mescloud/db/mysql/mes_work_order.sql

-- 工单：维护和导入。不参与现场上线。
CREATE TABLE IF NOT EXISTS `mes_work_order` (
  `id` varchar(64) NOT NULL COMMENT '主键',
  `work_no` varchar(64) NOT NULL COMMENT '工单号，M开头',
  `project_code` varchar(64) DEFAULT NULL COMMENT '项目别',
  `material_sn` varchar(64) NOT NULL COMMENT '总成料号，1开头',
  `spec_name` varchar(255) DEFAULT NULL COMMENT '规格名称',
  `drawing_no` varchar(128) DEFAULT NULL COMMENT '图号',
  `plan_qty` int(11) NOT NULL COMMENT '需生产数量',
  `open_date` date NOT NULL COMMENT '开单日期',
  `produce_date` date NOT NULL COMMENT '生产日期',
  `sale_order_no` varchar(64) DEFAULT NULL COMMENT '订单编号',
  `line_sn` varchar(64) DEFAULT NULL COMMENT '产线编号，下达前填写',
  `cate_sn` varchar(64) DEFAULT NULL COMMENT '工件类型编号',
  `state` char(1) NOT NULL DEFAULT '0' COMMENT '0待下达 1已下达 2生产中 3完工 4暂停 5关闭',
  `done_qty` int(11) NOT NULL DEFAULT 0 COMMENT '已完工数量，本阶段固定为0',
  `status` char(1) NOT NULL DEFAULT '0' COMMENT '状态（0正常 1删除 2停用）',
  `create_by` varchar(64) NOT NULL COMMENT '创建者',
  `create_date` datetime NOT NULL COMMENT '创建时间',
  `update_by` varchar(64) NOT NULL COMMENT '更新者',
  `update_date` datetime NOT NULL COMMENT '更新时间',
  `remarks` varchar(500) DEFAULT NULL COMMENT '备注信息',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_mes_work_order_no` (`work_no`),
  KEY `idx_mes_work_order_produce` (`produce_date`),
  KEY `idx_mes_work_order_state` (`state`),
  KEY `idx_mes_work_order_material` (`material_sn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='工单';

-- 菜单挂在「生产线管理」下。执行后重新登录刷新菜单。
DELETE FROM `js_sys_role_menu` WHERE `menu_code` = '2099101000000000001';
DELETE FROM `js_sys_menu` WHERE `menu_code` = '2099101000000000001';

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
'2099101000000000001', p.`menu_code`, CONCAT(p.`parent_codes`, p.`menu_code`, ','),
180, CONCAT(p.`tree_sorts`, '0000000180,'), '1', p.`tree_level` + 1,
CONCAT(p.`tree_names`, '/工单管理'), '工单管理', '1',
'/mes/mesWorkOrder/list', '', 'fa fa-list-alt', '', '',
'mes:mesWorkOrder:view,mes:mesWorkOrder:edit', 40, '1', 'default', 'core', NULL, NULL, '0',
'system', NOW(), 'system', NOW(), '工单维护与导入，不影响现场生产'
FROM `js_sys_menu` p
WHERE p.`menu_code` = '1680784143702568960';

INSERT INTO `js_sys_role_menu` (`role_code`, `menu_code`)
SELECT DISTINCT rm.`role_code`, '2099101000000000001'
FROM `js_sys_role_menu` rm
INNER JOIN `js_sys_menu` m ON m.`menu_code` = rm.`menu_code`
WHERE m.`parent_code` = '1680784143702568960'
  AND NOT EXISTS (
    SELECT 1 FROM `js_sys_role_menu` x
    WHERE x.`role_code` = rm.`role_code`
      AND x.`menu_code` = '2099101000000000001'
  );

-- 上线工单校验开关。1 生效，0 关闭。在参数设置中修改，修改后重启应用。
INSERT INTO `js_sys_config` (`id`, `config_name`, `config_key`, `config_value`, `is_sys`, `create_by`, `create_date`, `update_by`, `update_date`, `remarks`)
SELECT '2099101000000000002', '生产工单-上线校验开关', 'mes.workOrder.check', '1', '0', 'system', NOW(), 'system', NOW(), '1：上线校验工单并回写完工数量；0：不校验、不回写'
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `js_sys_config` WHERE `config_key` = 'mes.workOrder.check'
);

-- 工单工件页。执行后重新登录刷新菜单。
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
'2099101000000000003', p.`menu_code`, CONCAT(p.`parent_codes`, p.`menu_code`, ','),
190, CONCAT(p.`tree_sorts`, '0000000190,'), '1', p.`tree_level` + 1,
CONCAT(p.`tree_names`, '/工单工件'), '工单工件', '1',
'/mes/mesWorkOrder/snList', '', 'fa fa-barcode', '', '',
'mes:mesWorkOrder:view', 40, '1', 'default', 'core', NULL, NULL, '0',
'system', NOW(), 'system', NOW(), '查看工单下的工件及状态'
FROM `js_sys_menu` p
WHERE p.`menu_code` = '1680784143702568960'
  AND NOT EXISTS (SELECT 1 FROM `js_sys_menu` WHERE `menu_code` = '2099101000000000003');

INSERT INTO `js_sys_role_menu` (`role_code`, `menu_code`)
SELECT DISTINCT rm.`role_code`, '2099101000000000003'
FROM `js_sys_role_menu` rm
WHERE rm.`menu_code` = '2099101000000000001'
  AND NOT EXISTS (
    SELECT 1 FROM `js_sys_role_menu` x
    WHERE x.`role_code` = rm.`role_code`
      AND x.`menu_code` = '2099101000000000003'
  );

-- 上线记录回写工单号。已有库执行一次即可。
ALTER TABLE `mes_product_record`
  ADD COLUMN `work_no` varchar(64) DEFAULT NULL COMMENT '工单号' AFTER `line_sn`,
  ADD INDEX `idx_mes_product_record_work_no` (`work_no`);