mescloud/db/mysql/add_mes_device_fault_menu.sql

-- 设备故障菜单及表：从 mes-MX11-bj 迁入，挂在设备管理 / 设备故障 下

CREATE TABLE IF NOT EXISTS `mes_device_fault_cate` (
  `id` varchar(64) NOT NULL,
  `title` varchar(255) DEFAULT NULL COMMENT '名称',
  `remark` varchar(255) DEFAULT '' COMMENT '备注',
  `create_date` datetime DEFAULT NULL COMMENT '创建',
  `update_date` datetime DEFAULT NULL COMMENT '更新时间',
  `create_by` varchar(64) DEFAULT '',
  `update_by` varchar(64) DEFAULT '',
  `status` char(1) DEFAULT '0' COMMENT '状态',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='设备故障分类';

CREATE TABLE IF NOT EXISTS `mes_device_fault_maintain` (
  `id` varchar(64) NOT NULL,
  `title` varchar(255) DEFAULT NULL COMMENT '名称',
  `remark` varchar(255) DEFAULT '' COMMENT '备注',
  `create_date` datetime DEFAULT NULL COMMENT '创建',
  `update_date` datetime DEFAULT NULL COMMENT '更新时间',
  `create_by` varchar(64) DEFAULT '',
  `update_by` varchar(64) DEFAULT '',
  `status` char(1) DEFAULT '0' COMMENT '状态',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='设备故障工序';

CREATE TABLE IF NOT EXISTS `mes_device_fault` (
  `id` varchar(64) NOT NULL,
  `level` char(100) DEFAULT NULL COMMENT '停台级别',
  `cate_id` varchar(64) DEFAULT NULL COMMENT '故障分类',
  `maintain_id` varchar(64) DEFAULT NULL COMMENT '故障工序',
  `fault_phenomenon` varchar(1000) DEFAULT NULL COMMENT '故障现象',
  `problem_description` varchar(1000) DEFAULT NULL COMMENT '问题描述',
  `analysis` varchar(1000) DEFAULT NULL COMMENT '根因分析',
  `temporary_measures` varchar(1000) DEFAULT NULL COMMENT '临时措施',
  `eradication_measures` varchar(1000) DEFAULT NULL COMMENT '根除措施',
  `handle_by` varchar(255) DEFAULT NULL COMMENT '处理人',
  `start_date` datetime DEFAULT NULL COMMENT '开始时间',
  `end_date` datetime DEFAULT NULL COMMENT '结束时间',
  `duration` int(10) unsigned DEFAULT '0' COMMENT '时长（分钟）',
  `state` char(1) DEFAULT NULL COMMENT '状态 1=整改中 2=已完成',
  `create_date` datetime DEFAULT NULL COMMENT '创建',
  `update_date` datetime DEFAULT NULL COMMENT '更新时间',
  `create_by` varchar(64) DEFAULT '',
  `update_by` varchar(64) DEFAULT '',
  `status` char(1) DEFAULT '0' COMMENT '状态',
  `oprno` varchar(255) DEFAULT NULL COMMENT '工序',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='设备故障记录';

CREATE TABLE IF NOT EXISTS `mes_device_oee` (
  `id` varchar(64) NOT NULL,
  `cate_id` varchar(64) DEFAULT NULL COMMENT '阶段',
  `date` date DEFAULT NULL COMMENT '日期',
  `plan_num` int(10) DEFAULT NULL COMMENT '计划产量',
  `today_num` int(10) DEFAULT NULL COMMENT '今日产量',
  `create_by` varchar(64) DEFAULT '',
  `create_date` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '',
  `update_date` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='OEE';

CREATE TABLE IF NOT EXISTS `mes_device_oee_cate` (
  `id` varchar(64) NOT NULL DEFAULT '',
  `title` varchar(255) DEFAULT NULL COMMENT '名称',
  `create_by` varchar(64) DEFAULT '',
  `create_date` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '',
  `update_date` datetime DEFAULT NULL COMMENT '更新时间',
  `status` char(1) DEFAULT '0' COMMENT '状态',
  `type` char(1) DEFAULT NULL COMMENT '类型',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='OEE工序';

INSERT INTO `js_sys_dict_type` (
  `id`, `dict_name`, `dict_type`, `is_sys`, `status`,
  `create_by`, `create_date`, `update_by`, `update_date`, `remarks`
)
SELECT 'mesDeviceFaultLevel', '设备故障停台级别', 'mes_device_fault_level', '1', '0',
  'system', NOW(), 'system', NOW(), '1一级停台 2二级停台 3三级停台'
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `js_sys_dict_type` WHERE `dict_type` = 'mes_device_fault_level'
);

INSERT INTO `js_sys_dict_data` (
  `dict_code`, `parent_code`, `parent_codes`, `tree_sort`, `tree_sorts`,
  `tree_leaf`, `tree_level`, `tree_names`, `dict_label`, `dict_value`,
  `dict_icon`, `dict_type`, `is_sys`, `description`, `css_style`, `css_class`,
  `status`, `create_by`, `create_date`, `update_by`, `update_date`, `remarks`,
  `corp_code`, `corp_name`
)
SELECT 'mesDeviceFaultLevel1', '0', '0,', 10, '0000000010,',
  '1', 0, '一级停台', '一级停台', '1',
  '', 'mes_device_fault_level', '1', '', '', '',
  '0', 'system', NOW(), 'system', NOW(), '',
  '0', 'JeeSite'
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `js_sys_dict_data` WHERE `dict_type` = 'mes_device_fault_level' AND `dict_value` = '1'
);

INSERT INTO `js_sys_dict_data` (
  `dict_code`, `parent_code`, `parent_codes`, `tree_sort`, `tree_sorts`,
  `tree_leaf`, `tree_level`, `tree_names`, `dict_label`, `dict_value`,
  `dict_icon`, `dict_type`, `is_sys`, `description`, `css_style`, `css_class`,
  `status`, `create_by`, `create_date`, `update_by`, `update_date`, `remarks`,
  `corp_code`, `corp_name`
)
SELECT 'mesDeviceFaultLevel2', '0', '0,', 20, '0000000020,',
  '1', 0, '二级停台', '二级停台', '2',
  '', 'mes_device_fault_level', '1', '', '', '',
  '0', 'system', NOW(), 'system', NOW(), '',
  '0', 'JeeSite'
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `js_sys_dict_data` WHERE `dict_type` = 'mes_device_fault_level' AND `dict_value` = '2'
);

INSERT INTO `js_sys_dict_data` (
  `dict_code`, `parent_code`, `parent_codes`, `tree_sort`, `tree_sorts`,
  `tree_leaf`, `tree_level`, `tree_names`, `dict_label`, `dict_value`,
  `dict_icon`, `dict_type`, `is_sys`, `description`, `css_style`, `css_class`,
  `status`, `create_by`, `create_date`, `update_by`, `update_date`, `remarks`,
  `corp_code`, `corp_name`
)
SELECT 'mesDeviceFaultLevel3', '0', '0,', 30, '0000000030,',
  '1', 0, '三级停台', '三级停台', '3',
  '', 'mes_device_fault_level', '1', '', '', '',
  '0', 'system', NOW(), 'system', NOW(), '',
  '0', 'JeeSite'
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `js_sys_dict_data` WHERE `dict_type` = 'mes_device_fault_level' AND `dict_value` = '3'
);

INSERT INTO `js_sys_dict_type` (
  `id`, `dict_name`, `dict_type`, `is_sys`, `status`,
  `create_by`, `create_date`, `update_by`, `update_date`, `remarks`
)
SELECT 'mesDeviceFaultState', '设备故障记录状态', 'mes_device_fault_state', '1', '0',
  'system', NOW(), 'system', NOW(), '1整改中 2已完成'
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `js_sys_dict_type` WHERE `dict_type` = 'mes_device_fault_state'
);

INSERT INTO `js_sys_dict_data` (
  `dict_code`, `parent_code`, `parent_codes`, `tree_sort`, `tree_sorts`,
  `tree_leaf`, `tree_level`, `tree_names`, `dict_label`, `dict_value`,
  `dict_icon`, `dict_type`, `is_sys`, `description`, `css_style`, `css_class`,
  `status`, `create_by`, `create_date`, `update_by`, `update_date`, `remarks`,
  `corp_code`, `corp_name`
)
SELECT 'mesDeviceFaultState1', '0', '0,', 10, '0000000010,',
  '1', 0, '整改中', '整改中', '1',
  '', 'mes_device_fault_state', '1', '', '', '',
  '0', 'system', NOW(), 'system', NOW(), '',
  '0', 'JeeSite'
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `js_sys_dict_data` WHERE `dict_type` = 'mes_device_fault_state' AND `dict_value` = '1'
);

INSERT INTO `js_sys_dict_data` (
  `dict_code`, `parent_code`, `parent_codes`, `tree_sort`, `tree_sorts`,
  `tree_leaf`, `tree_level`, `tree_names`, `dict_label`, `dict_value`,
  `dict_icon`, `dict_type`, `is_sys`, `description`, `css_style`, `css_class`,
  `status`, `create_by`, `create_date`, `update_by`, `update_date`, `remarks`,
  `corp_code`, `corp_name`
)
SELECT 'mesDeviceFaultState2', '0', '0,', 20, '0000000020,',
  '1', 0, '已完成', '已完成', '2',
  '', 'mes_device_fault_state', '1', '', '', '',
  '0', 'system', NOW(), 'system', NOW(), '',
  '0', 'JeeSite'
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `js_sys_dict_data` WHERE `dict_type` = 'mes_device_fault_state' AND `dict_value` = '2'
);

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
  'mesDeviceFaultRoot', '1582259848270577664', '0,1582259848270577664,', 445, '0000009170,0000000445,',
  '0', 1, '设备管理/设备故障', '设备故障', '1', '', '', 'icon-notebook', '', '', '',
  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` = 'mesDeviceFaultRoot');

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
  'mesDeviceFaultCate', 'mesDeviceFaultRoot', '0,1582259848270577664,mesDeviceFaultRoot,', 10, '0000009170,0000000445,0000000010,',
  '1', 2, '设备管理/设备故障/设备故障分类', '设备故障分类', '1', '/mes/mesDeviceFaultCate/list', '', '', '', '',
  'mes:mesDeviceFaultCate:view,mes:mesDeviceFaultCate:edit',
  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` = 'mesDeviceFaultCate');

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
  'mesDeviceFaultMaintain', 'mesDeviceFaultRoot', '0,1582259848270577664,mesDeviceFaultRoot,', 20, '0000009170,0000000445,0000000020,',
  '1', 2, '设备管理/设备故障/设备故障工序', '设备故障工序', '1', '/mes/mesDeviceFaultMaintain/list', '', '', '', '',
  'mes:mesDeviceFaultMaintain:view,mes:mesDeviceFaultMaintain:edit',
  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` = 'mesDeviceFaultMaintain');

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
  'mesDeviceFaultRecord', 'mesDeviceFaultRoot', '0,1582259848270577664,mesDeviceFaultRoot,', 30, '0000009170,0000000445,0000000030,',
  '1', 2, '设备管理/设备故障/设备故障记录', '设备故障记录', '1', '/mes/mesDeviceFault/list', '', '', '', '',
  'mes:mesDeviceFault:view,mes:mesDeviceFault:edit',
  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` = 'mesDeviceFaultRecord');

UPDATE `js_sys_menu`
SET `parent_code` = 'mesDeviceFaultRoot',
    `parent_codes` = '0,1582259848270577664,mesDeviceFaultRoot,',
    `tree_sort` = 40,
    `tree_sorts` = '0000009170,0000000445,0000000040,',
    `tree_leaf` = '1',
    `tree_level` = 2,
    `tree_names` = '设备管理/设备故障/设备修改记录',
    `status` = '0',
    `is_show` = '1',
    `update_by` = 'system',
    `update_date` = NOW()
WHERE `menu_code` = '1775363810180796416';

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
  'mesDeviceOee', 'mesDeviceFaultRoot', '0,1582259848270577664,mesDeviceFaultRoot,', 50, '0000009170,0000000445,0000000050,',
  '1', 2, '设备管理/设备故障/OEE管理', 'OEE管理', '1', '/mes/mesDeviceOee/list', '', '', '', '',
  'mes:mesDeviceOee:view,mes:mesDeviceOee:edit',
  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` = 'mesDeviceOee');

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
  'mesDeviceOeeCate', 'mesDeviceFaultRoot', '0,1582259848270577664,mesDeviceFaultRoot,', 60, '0000009170,0000000445,0000000060,',
  '1', 2, '设备管理/设备故障/OEE工序管理', 'OEE工序管理', '1', '/mes/mesDeviceOeeCate/list', '', '', '', '',
  'mes:mesDeviceOeeCate:view,mes:mesDeviceOeeCate:edit',
  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` = 'mesDeviceOeeCate');

CREATE TABLE IF NOT EXISTS `mes_device_vulnerable_list` (
  `id` varchar(64) NOT NULL,
  `oprno` varchar(20) DEFAULT NULL COMMENT '工位编号',
  `material_id` varchar(64) DEFAULT NULL COMMENT '物料编号',
  `oprno_name` varchar(100) DEFAULT NULL COMMENT '工位名称',
  `device_name` varchar(255) DEFAULT NULL COMMENT '设备名称',
  `model` varchar(255) DEFAULT NULL COMMENT '型号',
  `brand` varchar(100) DEFAULT NULL COMMENT '品牌',
  `min_inventory` bigint(20) DEFAULT NULL COMMENT '最小库存',
  `max_inventory` bigint(20) DEFAULT NULL COMMENT '最大库存',
  `unit` varchar(20) DEFAULT NULL COMMENT '单位',
  `purchase_cycle` varchar(64) DEFAULT NULL COMMENT '采购周期',
  `use_cycle` varchar(100) DEFAULT NULL COMMENT '使用周期',
  `price` varchar(64) DEFAULT NULL COMMENT '单价',
  PRIMARY KEY (`id`) 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
  'mesDeviceVulnerable', 'mesDeviceFaultRoot', '0,1582259848270577664,mesDeviceFaultRoot,', 70, '0000009170,0000000445,0000000070,',
  '1', 2, '设备管理/设备故障/产线设备易损件清单', '产线设备易损件清单', '1', '/mes/mesDeviceVulnerable/list', '', '', '', '',
  'mes:mesDeviceVulnerable:view,mes:mesDeviceVulnerable:edit',
  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` = 'mesDeviceVulnerable');

INSERT INTO `js_sys_role_menu` (`role_code`, `menu_code`)
SELECT r.role_code, m.menu_code
FROM `js_sys_role_menu` r
JOIN (
  SELECT 'mesDeviceFaultRoot' AS menu_code
  UNION ALL SELECT 'mesDeviceFaultCate'
  UNION ALL SELECT 'mesDeviceFaultMaintain'
  UNION ALL SELECT 'mesDeviceFaultRecord'
  UNION ALL SELECT '1775363810180796416'
  UNION ALL SELECT 'mesDeviceOee'
  UNION ALL SELECT 'mesDeviceOeeCate'
  UNION ALL SELECT 'mesDeviceVulnerable'
) m
WHERE r.menu_code = '1582259848270577664'
AND NOT EXISTS (
  SELECT 1 FROM `js_sys_role_menu` x
  WHERE x.role_code = r.role_code AND x.menu_code = m.menu_code
);