-- 钉钉消息推送配置（类型预置，页面只允许启停、按产线配人、测试发送）
-- 挂载位置：系统管理 / 系统设置 / 钉钉消息推送

CREATE TABLE IF NOT EXISTS `mes_ding_msg_config` (
  `id` varchar(64) NOT NULL COMMENT '主键',
  `msg_code` varchar(64) NOT NULL COMMENT '消息编码（代码写死）',
  `msg_name` varchar(100) NOT NULL COMMENT '消息名称',
  `module` varchar(20) NOT NULL COMMENT '模块：DEVICE/PROD/QUALITY',
  `trigger_desc` varchar(500) DEFAULT '' COMMENT '触发说明（只读展示）',
  `msg_content` text COMMENT '发送内容（页面可编辑）',
  `is_enabled` char(1) NOT NULL DEFAULT '0' COMMENT '是否启用：1是 0否',
  `sort_order` int DEFAULT 0 COMMENT '排序',
  `status` char(1) NOT NULL DEFAULT '0' COMMENT '状态',
  `create_by` varchar(64) DEFAULT NULL COMMENT '创建者',
  `create_date` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT NULL COMMENT '更新者',
  `update_date` datetime DEFAULT NULL COMMENT '更新时间',
  `remarks` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_mes_ding_msg_code` (`msg_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='钉钉消息推送配置';

CREATE TABLE IF NOT EXISTS `mes_ding_msg_line` (
  `id` varchar(64) NOT NULL COMMENT '主键',
  `config_id` varchar(64) NOT NULL COMMENT '消息配置ID',
  `line_sn` varchar(64) NOT NULL COMMENT '产线编号',
  `line_title` varchar(100) DEFAULT '' COMMENT '产线名称',
  `user_codes` varchar(2000) DEFAULT '' COMMENT '接收人用户编码，逗号分隔',
  `user_names` varchar(2000) DEFAULT '' COMMENT '接收人姓名，逗号分隔',
  `mobiles` varchar(2000) DEFAULT '' COMMENT '接收人手机号，逗号分隔',
  `status` char(1) NOT NULL DEFAULT '0' COMMENT '状态',
  `create_by` varchar(64) DEFAULT NULL COMMENT '创建者',
  `create_date` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT NULL COMMENT '更新者',
  `update_date` datetime DEFAULT NULL COMMENT '更新时间',
  `remarks` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_mes_ding_msg_line_cfg` (`config_id`, `line_sn`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='钉钉消息产线接收人';

INSERT INTO `js_sys_menu` (
  `menu_code`, `parent_code`, `parent_codes`, `tree_sort`, `tree_sorts`, `tree_leaf`, `tree_level`,
  `tree_names`, `menu_name`, `menu_type`, `menu_href`, `menu_target`, `menu_icon`, `menu_color`,
  `menu_title`, `permission`, `weight`, `is_show`, `sys_code`, `module_codes`, `component`, `params`,
  `status`, `create_by`, `create_date`, `update_by`, `update_date`, `remarks`
)
SELECT
  'mesDingMsgConfig',
  '1582259828007895040',
  '0,1582259822626603008,1582259828007895040,',
  180,
  '0000009000,0000000500,0000000180,',
  '1',
  2,
  '系统管理/系统设置/钉钉消息推送',
  '钉钉消息推送',
  '1',
  '/mes/mesDingMsgConfig/list',
  '',
  'icon-speech',
  '',
  '',
  'mes:mesDingMsgConfig:view',
  40,
  '1',
  'default',
  'core',
  NULL,
  NULL,
  '0',
  'system',
  NOW(),
  'system',
  NOW(),
  ''
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `js_sys_menu` WHERE `menu_code` = 'mesDingMsgConfig'
);

INSERT INTO `js_sys_menu` (
  `menu_code`, `parent_code`, `parent_codes`, `tree_sort`, `tree_sorts`, `tree_leaf`, `tree_level`,
  `tree_names`, `menu_name`, `menu_type`, `menu_href`, `menu_target`, `menu_icon`, `menu_color`,
  `menu_title`, `permission`, `weight`, `is_show`, `sys_code`, `module_codes`, `component`, `params`,
  `status`, `create_by`, `create_date`, `update_by`, `update_date`, `remarks`
)
SELECT
  'mesDingMsgConfigEdit',
  'mesDingMsgConfig',
  '0,1582259822626603008,1582259828007895040,mesDingMsgConfig,',
  30,
  '0000009000,0000000500,0000000180,0000000030,',
  '1',
  3,
  '系统管理/系统设置/钉钉消息推送/编辑',
  '编辑',
  '2',
  '',
  '',
  '',
  '',
  '',
  'mes:mesDingMsgConfig:edit',
  40,
  '0',
  'default',
  'core',
  NULL,
  NULL,
  '0',
  'system',
  NOW(),
  'system',
  NOW(),
  ''
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `js_sys_menu` WHERE `menu_code` = 'mesDingMsgConfigEdit'
);

INSERT INTO `mes_ding_msg_config` (`id`,`msg_code`,`msg_name`,`module`,`trigger_desc`,`is_enabled`,`sort_order`,`status`,`create_by`,`create_date`,`update_by`,`update_date`)
SELECT 'DEVICE_PREVENT_TASK','DEVICE_PREVENT_TASK','预防性任务提示','DEVICE','每天中午12点进行定时任务：查询预防性维护计划，在开始前3天或者已经开始（未结束），但是没有完成的任务，进行提醒','0',10,'0','system',NOW(),'system',NOW() FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `mes_ding_msg_config` WHERE `msg_code`='DEVICE_PREVENT_TASK');
INSERT INTO `mes_ding_msg_config` (`id`,`msg_code`,`msg_name`,`module`,`trigger_desc`,`is_enabled`,`sort_order`,`status`,`create_by`,`create_date`,`update_by`,`update_date`)
SELECT 'DEVICE_MAINTAIN_15','DEVICE_MAINTAIN_15','设备保养三级维护（提前15天）','DEVICE','每天中午12点进行定时任务：查询预防性维护计划，未完成且距离开始时间正好提前15天的任务进行提醒','0',11,'0','system',NOW(),'system',NOW() FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `mes_ding_msg_config` WHERE `msg_code`='DEVICE_MAINTAIN_15');
INSERT INTO `mes_ding_msg_config` (`id`,`msg_code`,`msg_name`,`module`,`trigger_desc`,`is_enabled`,`sort_order`,`status`,`create_by`,`create_date`,`update_by`,`update_date`)
SELECT 'DEVICE_MAINTAIN_7','DEVICE_MAINTAIN_7','设备保养三级维护（提前7天）','DEVICE','每天中午12点进行定时任务：查询预防性维护计划，未完成且距离开始时间正好提前7天的任务进行提醒','0',12,'0','system',NOW(),'system',NOW() FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `mes_ding_msg_config` WHERE `msg_code`='DEVICE_MAINTAIN_7');
INSERT INTO `mes_ding_msg_config` (`id`,`msg_code`,`msg_name`,`module`,`trigger_desc`,`is_enabled`,`sort_order`,`status`,`create_by`,`create_date`,`update_by`,`update_date`)
SELECT 'DEVICE_MAINTAIN_3','DEVICE_MAINTAIN_3','设备保养三级维护（提前3天）','DEVICE','每天中午12点进行定时任务：查询预防性维护计划，未完成且距离开始时间正好提前3天的任务进行提醒','0',13,'0','system',NOW(),'system',NOW() FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `mes_ding_msg_config` WHERE `msg_code`='DEVICE_MAINTAIN_3');
INSERT INTO `mes_ding_msg_config` (`id`,`msg_code`,`msg_name`,`module`,`trigger_desc`,`is_enabled`,`sort_order`,`status`,`create_by`,`create_date`,`update_by`,`update_date`)
SELECT 'PROD_PLAN_RATE','PROD_PLAN_RATE','生产计划完成率低于97%','PROD','每天中午12点进行定时任务：排产计划中的最后一天中午12:00，如果完成率低于97%，进行提醒','0',20,'0','system',NOW(),'system',NOW() FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `mes_ding_msg_config` WHERE `msg_code`='PROD_PLAN_RATE');
INSERT INTO `mes_ding_msg_config` (`id`,`msg_code`,`msg_name`,`module`,`trigger_desc`,`is_enabled`,`sort_order`,`status`,`create_by`,`create_date`,`update_by`,`update_date`)
SELECT 'PROD_MATERIAL_3DAY','PROD_MATERIAL_3DAY','低于3天计划量的物料','PROD','每天中午12点进行定时任务：查询当前日期的计划数据，计算平均每天需要做工数量，结合产线每个工位需要的物料进行计算，如果库存中物料少于三天的需要物料进行提醒','0',30,'0','system',NOW(),'system',NOW() FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `mes_ding_msg_config` WHERE `msg_code`='PROD_MATERIAL_3DAY');
INSERT INTO `mes_ding_msg_config` (`id`,`msg_code`,`msg_name`,`module`,`trigger_desc`,`is_enabled`,`sort_order`,`status`,`create_by`,`create_date`,`update_by`,`update_date`)
SELECT 'PROD_OEE_85','PROD_OEE_85','设备OEE低于85%','PROD','每1小时进行定时任务：看设备综合效率管理大屏中的平均开动率，如果低于85%，则发送数据','0',40,'0','system',NOW(),'system',NOW() FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `mes_ding_msg_config` WHERE `msg_code`='PROD_OEE_85');
INSERT INTO `mes_ding_msg_config` (`id`,`msg_code`,`msg_name`,`module`,`trigger_desc`,`is_enabled`,`sort_order`,`status`,`create_by`,`create_date`,`update_by`,`update_date`)
SELECT 'PROD_STOCK_500','PROD_STOCK_500','产品成品库存低于500件','PROD','每天中午12点进行定时任务：查询批次码记录表中仓库中的批次码，计算数量，如果少于500，进行提醒','0',50,'0','system',NOW(),'system',NOW() FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `mes_ding_msg_config` WHERE `msg_code`='PROD_STOCK_500');
INSERT INTO `mes_ding_msg_config` (`id`,`msg_code`,`msg_name`,`module`,`trigger_desc`,`is_enabled`,`sort_order`,`status`,`create_by`,`create_date`,`update_by`,`update_date`)
SELECT 'QM_FIRST_NG','QM_FIRST_NG','首件NG时进行消息推送','QUALITY','触发任务：使用pda抽检模块提交首件检查为NG时触发','0',60,'0','system',NOW(),'system',NOW() FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `mes_ding_msg_config` WHERE `msg_code`='QM_FIRST_NG');
INSERT INTO `mes_ding_msg_config` (`id`,`msg_code`,`msg_name`,`module`,`trigger_desc`,`is_enabled`,`sort_order`,`status`,`create_by`,`create_date`,`update_by`,`update_date`)
SELECT 'QM_SPOT_NG','QM_SPOT_NG','抽检NG时进行消息推送','QUALITY','触发任务：使用pda抽检模块提交抽检为NG时触发','0',70,'0','system',NOW(),'system',NOW() FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `mes_ding_msg_config` WHERE `msg_code`='QM_SPOT_NG');
INSERT INTO `mes_ding_msg_config` (`id`,`msg_code`,`msg_name`,`module`,`trigger_desc`,`is_enabled`,`sort_order`,`status`,`create_by`,`create_date`,`update_by`,`update_date`)
SELECT 'QM_LAST_NG','QM_LAST_NG','末件NG时进行消息推送','QUALITY','触发任务：使用pda抽检模块提交末件检查为NG时触发','0',80,'0','system',NOW(),'system',NOW() FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `mes_ding_msg_config` WHERE `msg_code`='QM_LAST_NG');
INSERT INTO `mes_ding_msg_config` (`id`,`msg_code`,`msg_name`,`module`,`trigger_desc`,`is_enabled`,`sort_order`,`status`,`create_by`,`create_date`,`update_by`,`update_date`)
SELECT 'QM_REPAIR_ONLINE','QM_REPAIR_ONLINE','返修机完成重新上线','QUALITY','触发任务：返修检查OK时','0',90,'0','system',NOW(),'system',NOW() FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `mes_ding_msg_config` WHERE `msg_code`='QM_REPAIR_ONLINE');
INSERT INTO `mes_ding_msg_config` (`id`,`msg_code`,`msg_name`,`module`,`trigger_desc`,`is_enabled`,`sort_order`,`status`,`create_by`,`create_date`,`update_by`,`update_date`)
SELECT 'QM_SCRAP','QM_SCRAP','报废','QUALITY','触发任务：当调用报废接口提交时触发','0',100,'0','system',NOW(),'system',NOW() FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `mes_ding_msg_config` WHERE `msg_code`='QM_SCRAP');
INSERT INTO `mes_ding_msg_config` (`id`,`msg_code`,`msg_name`,`module`,`trigger_desc`,`is_enabled`,`sort_order`,`status`,`create_by`,`create_date`,`update_by`,`update_date`)
SELECT 'QM_NG_3','QM_NG_3','当班同产品不良数超过3','QUALITY','触发任务：加工提交结果为NG时，查询当班NG数量，当NG数量为4时触发','0',110,'0','system',NOW(),'system',NOW() FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `mes_ding_msg_config` WHERE `msg_code`='QM_NG_3');
INSERT INTO `mes_ding_msg_config` (`id`,`msg_code`,`msg_name`,`module`,`trigger_desc`,`is_enabled`,`sort_order`,`status`,`create_by`,`create_date`,`update_by`,`update_date`)
SELECT 'QM_NG_10','QM_NG_10','当班同产品不良数超过10','QUALITY','触发任务：加工提交结果为NG时，查询当班NG数量，当NG数量为11时触发','0',120,'0','system',NOW(),'system',NOW() FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `mes_ding_msg_config` WHERE `msg_code`='QM_NG_10');
INSERT INTO `mes_ding_msg_config` (`id`,`msg_code`,`msg_name`,`module`,`trigger_desc`,`is_enabled`,`sort_order`,`status`,`create_by`,`create_date`,`update_by`,`update_date`)
SELECT 'QM_NG_50','QM_NG_50','当班同产品不良数超过50','QUALITY','触发任务：加工提交结果为NG时，查询当班NG数量，当NG数量为51时触发','0',130,'0','system',NOW(),'system',NOW() FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `mes_ding_msg_config` WHERE `msg_code`='QM_NG_50');

INSERT INTO `js_sys_role_menu` (`role_code`, `menu_code`)
SELECT 'corpAdmin', 'mesDingMsgConfig' FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `js_sys_role_menu` WHERE `role_code`='corpAdmin' AND `menu_code`='mesDingMsgConfig');

INSERT INTO `js_sys_role_menu` (`role_code`, `menu_code`)
SELECT 'corpAdmin', 'mesDingMsgConfigEdit' FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `js_sys_role_menu` WHERE `role_code`='corpAdmin' AND `menu_code`='mesDingMsgConfigEdit');

-- 已有库补加发送内容字段
SET @col_exists := (
  SELECT COUNT(1) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'mes_ding_msg_config' AND COLUMN_NAME = 'msg_content'
);
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE mes_ding_msg_config ADD COLUMN msg_content text COMMENT ''发送内容'' AFTER trigger_desc',
  'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

ALTER TABLE mes_ding_msg_config MODIFY COLUMN trigger_desc varchar(500) DEFAULT '' COMMENT '触发说明';

DELETE FROM mes_ding_msg_line WHERE config_id IN (
  SELECT id FROM (
    SELECT id FROM mes_ding_msg_config WHERE msg_code NOT IN (
      'DEVICE_PREVENT_TASK','DEVICE_MAINTAIN_15','DEVICE_MAINTAIN_7','DEVICE_MAINTAIN_3',
      'PROD_PLAN_RATE','PROD_MATERIAL_3DAY','PROD_OEE_85','PROD_STOCK_500',
      'QM_FIRST_NG','QM_SPOT_NG','QM_LAST_NG','QM_REPAIR_ONLINE','QM_SCRAP','QM_NG_3','QM_NG_10','QM_NG_50'
    )
  ) t
);
DELETE FROM mes_ding_msg_config WHERE msg_code NOT IN (
  'DEVICE_PREVENT_TASK','DEVICE_MAINTAIN_15','DEVICE_MAINTAIN_7','DEVICE_MAINTAIN_3',
  'PROD_PLAN_RATE','PROD_MATERIAL_3DAY','PROD_OEE_85','PROD_STOCK_500',
  'QM_FIRST_NG','QM_SPOT_NG','QM_LAST_NG','QM_REPAIR_ONLINE','QM_SCRAP','QM_NG_3','QM_NG_10','QM_NG_50'
);