java/sql/升级更新脚本.sql

-- 孙一石 2025-05-08 生产工单中，bom和物料需求两个按钮
INSERT INTO `sys_menu`( `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('BOM组成导出', 2509, 10, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:pro:workorderbom:export', '#', 'admin', '2025-05-08 10:55:13', '', NULL, '');
INSERT INTO `sys_menu`(`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('物料需求导出', 2509, 11, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:pro:item:exportitem', '#', 'admin', '2025-05-08 10:55:13', '', NULL, '');


-- ----------------------------
-- 2025-05-06 复制BOM菜单，杨晓冬增加
-- ----------------------------
INSERT INTO `sys_menu` (`menu_id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2676, '复制BOM', 2100, 10, '', NULL, NULL, 1, 0, 'F', '0', '0', 'base:material:copybom', '#', 'admin', '2025-05-06 08:16:02', 'admin', '2025-05-06 08:16:22', '');
INSERT INTO `sys_menu` (`menu_id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2677, '多阶导出', 2100, 11, '', NULL, NULL, 1, 0, 'F', '0', '0', 'base:material:bommulti:export', '#', 'admin', '2025-05-06 08:25:07', '', NULL, '');

-- ----------------------------
-- 2025-04-26 工序流转菜单，杨晓冬增加
-- ----------------------------
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('工序流转', 2508, 6, 'procard', 'mes/pro/procard/index', NULL, 1, 0, 'C', '0', '0', 'mes:pro:procard', 'bug', 'admin', '2025-04-27 08:48:28', 'admin', '2025-04-27 08:52:12', '');
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('列表查询', 2672, 1, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:pro:procard:list', '#', 'admin', '2025-04-27 08:51:08', '', NULL, '');
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('流转卡查看', 2672, 2, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:pro:procard:query', '#', 'admin', '2025-04-27 08:51:31', '', NULL, '');

-- ----------------------------
-- 2025-04-23 SN流转-工序信息表，杨晓冬增加,增加SN流转-工序信息表
-- ----------------------------
DROP TABLE IF EXISTS `pro_sn_process`;
CREATE TABLE `pro_sn_process`  (
  `record_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '流水ID',
  `sn_id` bigint(20) NOT NULL COMMENT 'SNID',
  `sn_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'SN编号',
  `seq_num` int(11) NULL DEFAULT 1 COMMENT '序号',
  `process_id` bigint(20) NULL DEFAULT NULL COMMENT '工序ID',
  `process_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '工序编号',
  `process_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '工序名称',
  `input_time` datetime NULL DEFAULT NULL COMMENT '进入工序时间',
  `output_time` datetime NULL DEFAULT NULL COMMENT '出工序时间',
  `quantity_input` double(12, 2) NULL DEFAULT NULL COMMENT '投入数量',
  `quantity_output` double(12, 2) NULL DEFAULT NULL COMMENT '产出数量',
  `quantity_unquanlify` double(12, 2) NULL DEFAULT NULL COMMENT '不合格品数量',
  `workstation_id` bigint(20) NOT NULL COMMENT '工作站ID',
  `workstation_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '工作站编号',
  `workstation_name` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '工作站名称',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `user_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户名',
  `nick_name` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '名称',
  `ipqc_id` bigint(20) NULL DEFAULT NULL COMMENT '过程检验单ID',
  `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '备注',
  `attr1` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '预留字段1',
  `attr2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '预留字段2',
  `attr3` int(11) NULL DEFAULT 0 COMMENT '预留字段3',
  `attr4` int(11) NULL DEFAULT 0 COMMENT '预留字段4',
  `create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '创建者',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '更新者',
  `update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`record_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 206 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'SN流转-工序信息表' ROW_FORMAT = DYNAMIC;

SET FOREIGN_KEY_CHECKS = 1;

-- ----------------------------
-- 2025-04-23 工序流转卡-工序信息表，杨晓冬增加,增加工序流转卡-工序信息表
-- ----------------------------
DROP TABLE IF EXISTS `pro_card_process`;
CREATE TABLE `pro_card_process`  (
  `record_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '流水ID',
  `card_id` bigint(20) NOT NULL COMMENT '流转卡ID',
  `card_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '流转卡编号',
  `seq_num` int(11) NULL DEFAULT 1 COMMENT '序号',
  `process_id` bigint(20) NULL DEFAULT NULL COMMENT '工序ID',
  `process_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '工序编号',
  `process_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '工序名称',
  `input_time` datetime NULL DEFAULT NULL COMMENT '进入工序时间',
  `output_time` datetime NULL DEFAULT NULL COMMENT '出工序时间',
  `quantity_input` double(12, 2) NULL DEFAULT NULL COMMENT '投入数量',
  `quantity_output` double(12, 2) NULL DEFAULT NULL COMMENT '产出数量',
  `quantity_unquanlify` double(12, 2) NULL DEFAULT NULL COMMENT '不合格品数量',
  `workstation_id` bigint(20) NOT NULL COMMENT '工作站ID',
  `workstation_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '工作站编号',
  `workstation_name` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '工作站名称',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `user_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户名',
  `nick_name` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '名称',
  `ipqc_id` bigint(20) NULL DEFAULT NULL COMMENT '过程检验单ID',
  `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '备注',
  `attr1` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '预留字段1',
  `attr2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '预留字段2',
  `attr3` int(11) NULL DEFAULT 0 COMMENT '预留字段3',
  `attr4` int(11) NULL DEFAULT 0 COMMENT '预留字段4',
  `create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '创建者',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '更新者',
  `update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`record_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 203 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '工序流转卡-工序信息表' ROW_FORMAT = DYNAMIC;

SET FOREIGN_KEY_CHECKS = 1;

-- ----------------------------
-- 2025-04-23 流转卡表，杨晓冬增加,增加流转卡表
-- ----------------------------
DROP TABLE IF EXISTS `pro_card`;
CREATE TABLE `pro_card`  (
  `card_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '流转卡ID',
  `card_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '流转卡编号',
  `workorder_id` bigint(20) NULL DEFAULT NULL COMMENT '生产工单ID',
  `workorder_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '生产工单编号',
  `workorder_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '生产工单名称',
  `batch_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '批次号',
  `item_id` bigint(20) NULL DEFAULT NULL COMMENT '产品物料ID',
  `item_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '产品物料编码',
  `item_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '产品物料名称',
  `specification` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '规格型号',
  `unit_of_measure` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '单位',
  `unit_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '单位名称',
  `barcode_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '赋码地址',
  `quantity_transfered` double(12, 2) NULL DEFAULT NULL COMMENT '流转数量',
  `status` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '流转卡状态',
  `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '备注',
  `attr1` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '预留字段1',
  `attr2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '预留字段2',
  `attr3` int(11) NULL DEFAULT 0 COMMENT '预留字段3',
  `attr4` int(11) NULL DEFAULT 0 COMMENT '预留字段4',
  `create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '创建者',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '更新者',
  `update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`card_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 218 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '流转卡表' ROW_FORMAT = DYNAMIC;

SET FOREIGN_KEY_CHECKS = 1;

-- ----------------------------
-- 2025-04-08 杨晓冬 增加节假日设置和排班日历菜单权限
-- ----------------------------
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('节假日新增', 2663, 3, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:cal:holiday:add', '#', 'admin', '2025-04-08 08:28:29', '', NULL, '');
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('班组列表', 2667, 3, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:cal:team:list', '#', 'admin', '2025-04-08 08:49:40', '', NULL, '');
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('节假日设置列表', 2667, 4, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:cal:holiday:list', '#', 'admin', '2025-04-08 08:50:21', '', NULL, '');

-- ----------------------------
-- 2025-04-03 杨晓冬 增加设备资源、工装夹具资源、人力资源的菜单权限
-- ----------------------------
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (12376, '人力资源查询', 12295, 4, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:md:workstationworker:query', '#', 'admin', '2025-04-03 10:56:40', '', NULL, '');
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (12377, '工装夹具资源查询', 12297, 5, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:md:workstationtool:query', '#', 'admin', '2025-04-03 10:57:15', '', NULL, '');
UPDATE `sys_menu` SET `menu_name` = '设备资源删除', `parent_id` = 12299, `order_num` = 4, `path` = '', `component` = NULL, `query` = NULL, `is_frame` = 1, `is_cache` = 0, `menu_type` = 'F', `visible` = '0', `status` = '0', `perms` = 'mes:md:workstationmachine:remove', `icon` = '#', `create_by` = 'admin', `create_time` = '2025-03-29 16:44:39', `update_by` = 'admin', `update_time` = '2025-04-03 10:54:31', `remark` = '' WHERE `menu_id` = 12374;
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('维修单新增', 12205, 1, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:dv:repair:add', '#', 'admin', '2025-04-03 15:22:35', '', NULL, '');
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('班组导出', 12161, 6, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:cal:team:export', '#', 'admin', '2025-04-03 15:18:54', '', NULL, '');
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('班组删除', 12161, 5, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:cal:team:remove', '#', 'admin', '2025-04-03 15:18:34', '', NULL, '');
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('排班计划导出', 12168, 9, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:cal:plan:export', '#', 'admin', '2025-04-03 14:41:43', '', NULL, '');
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('计划班次查询', 12379, 4, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:cal:shift:query', '#', 'admin', '2025-04-03 13:56:01', '', NULL, '');
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('班组列表', 12380, 2, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:cal:team:list', '#', 'admin', '2025-04-03 13:53:37', '', NULL, '');
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('计划班组新增', 12380, 1, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:cal:planteam:add', '#', 'admin', '2025-04-03 13:49:18', 'admin', '2025-04-03 13:58:01', '');
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('计划班次删除', 12379, 3, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:cal:shift:remove', '#', 'admin', '2025-04-03 13:47:14', '', NULL, '');
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('计划班次修改', 12379, 2, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:cal:shift:edit', '#', 'admin', '2025-04-03 13:46:54', '', NULL, '');
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('计划班次新增', 12379, 1, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:cal:shift:add', '#', 'admin', '2025-04-03 13:46:30', '', NULL, '');
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('班组成员列表', 12168, 8, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:cal:teammember:list', '#', 'admin', '2025-04-03 13:43:36', '', NULL, '');
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('计划班组列表', 12168, 7, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:cal:planteam:list', '#', 'admin', '2025-04-03 13:42:37', '', NULL, '');
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('计划班次列表', 12168, 6, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:cal:shift:list', '#', 'admin', '2025-04-03 13:40:56', '', NULL, '');
UPDATE `sys_menu` SET `menu_name` = '生产任务查询', `parent_id` = 12368, `order_num` = 5, `path` = '', `component` = NULL, `query` = NULL, `is_frame` = 1, `is_cache` = 0, `menu_type` = 'F', `visible` = '0', `status` = '0', `perms` = 'mes:pro:protask:query', `icon` = '#', `create_by` = 'admin', `create_time` = '2025-03-29 16:15:18', `update_by` = 'admin', `update_time` = '2025-04-03 15:37:41', `remark` = '' WHERE `menu_id` = 12373;
UPDATE `sys_menu` SET `menu_name` = '计划班组新增', `parent_id` = 12380, `order_num` = 1, `path` = '', `component` = NULL, `query` = NULL, `is_frame` = 1, `is_cache` = 0, `menu_type` = 'F', `visible` = '0', `status` = '0', `perms` = 'mes:cal:planteam:add', `icon` = '#', `create_by` = 'admin', `create_time` = '2025-04-03 13:49:18', `update_by` = 'admin', `update_time` = '2025-04-03 13:58:01', `remark` = '' WHERE `menu_id` = 12385;
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('班组成员导出', 12394, 4, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:cal:teammember:export', '#', 'admin', '2025-04-03 16:45:43', '', NULL, '');
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('班组成员删除', 12394, 3, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:cal:teammember:remove', '#', 'admin', '2025-04-03 16:45:21', '', NULL, '');
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('班组成员新增', 12394, 2, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:cal:teammember:add', '#', 'admin', '2025-04-03 16:44:13', '', NULL, '');
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('班组成员查询', 12394, 1, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:cal:teammember:query', '#', 'admin', '2025-04-03 16:43:54', '', NULL, '');
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('班组成员列表', 12161, 7, '', NULL, NULL, 1, 0, 'F', '0', '0', 'mes:cal:teammember:list', '#', 'admin', '2025-04-03 16:40:39', '', NULL, '');


-- ----------------------------
-- 2025-03-28 在系统设置表中，增加设置xinli_interface_1和xinli_interface_2和K3CloudVersion，杨晓冬增加的
-- ----------------------------
INSERT IGNORE INTO `set_system_setting` (`setting_class`, `setting_code`, `setting_name`, `setting_values`, `setting_sort`, `setting_describe`) VALUES ('SysSetting', 'xinli_interface_1', '鑫利接口1', 'http://www.xinli.com_1', NULL, '生产退料单(车间材料仓=>主材仓) WMS同步至MES');
INSERT IGNORE INTO `set_system_setting` (`setting_class`, `setting_code`, `setting_name`, `setting_values`, `setting_sort`, `setting_describe`) VALUES ('SysSetting', 'xinli_interface_2', '鑫利接口2', 'http://www.xinli.com_2', NULL, '生产领料单(主材仓=>车间材料仓) WMS同步至MES');
INSERT IGNORE INTO `set_system_setting` (`setting_class`, `setting_code`, `setting_name`, `setting_values`, `setting_sort`, `setting_describe`) VALUES ('WmsSetting', 'K3CloudVersion', '金蝶版本', 'V90', NULL, 'V73、V90');


-- ----------------------------
-- 2025-03-24 sys 导入设备台账报错
-- ----------------------------
ALTER TABLE `dv_machinery`
MODIFY COLUMN `machinery_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '设备ID' FIRST,
MODIFY COLUMN `machinery_code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '设备编码' AFTER `machinery_id`,
MODIFY COLUMN `machinery_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '设备名称' AFTER `machinery_code`;


-- ----------------------------
-- 2025-03-21 sys 增加pad端质量模块拍照功能
-- ----------------------------

CREATE TABLE qc_defect_photo  (
  `photo_id` int(0) NOT NULL AUTO_INCREMENT COMMENT '照片主键',
  `photo_path` varchar(255) NOT NULL COMMENT '照片路径',
  `create_time` datetime(0) NOT NULL COMMENT '创建时间',
  `record_id` int(0) NOT NULL COMMENT '记录ID',
  PRIMARY KEY (`photo_id`)
);


-- ----------------------------
-- 2025-03-20 sys修改表字段类型
-- ----------------------------

ALTER TABLE `qc_ipqc`
MODIFY COLUMN `cr_quantity` int(12) NULL DEFAULT 0.0000 COMMENT '致命缺陷数量' AFTER `min_rate`,
MODIFY COLUMN `maj_quantity` int(12) NULL DEFAULT 0.0000 COMMENT '严重缺陷数量' AFTER `cr_quantity`,
MODIFY COLUMN `min_quantity` int(12) NULL DEFAULT 0.0000 COMMENT '轻微缺陷数量' AFTER `maj_quantity`;

-- ----------------------------
-- 2025-03-20 在系统设置表中，增加设置是否生成出入库任务单，杨晓冬增加的
-- ----------------------------
INSERT IGNORE INTO `set_system_setting` (`setting_class`, `setting_code`, `setting_name`, `setting_values`, `setting_sort`, `setting_describe`) VALUES ('MesSetting', 'IsCreateTaskInAndTaskOut', '生产工单是否生成出入库任务单', '0', NULL, '0生成、1不生成');

-- ----------------------------
-- 2025-03-20 sys质量检验先改成统一的long型了
-- ----------------------------

ALTER TABLE `qc_ipqc_line`
MODIFY COLUMN `cr_quantity` bigint(12) NULL DEFAULT 0.0000 COMMENT '致命缺陷数量' AFTER `threshold_min`,
MODIFY COLUMN `maj_quantity` bigint(12) NULL DEFAULT 0.0000 COMMENT '严重缺陷数量' AFTER `cr_quantity`,
MODIFY COLUMN `min_quantity` bigint(12) NULL DEFAULT 0.0000 COMMENT '轻微缺陷数量' AFTER `maj_quantity`;

-- ----------------------------
-- 2025-03-19 在生产工单表中，增加完成时间字段，杨晓冬增加的
-- ----------------------------
ALTER TABLE `pro_workorder`
ADD COLUMN `confirm_date` datetime NULL COMMENT '完成时间' AFTER `request_date`;


-- ----------------------------
-- 2025-03-19 在上下工记录表中，增加是哪个工单的记录，孙一石增加的
-- ----------------------------
ALTER TABLE `cath_mom_prod`.`pro_workrecord`
ADD COLUMN `task_id` bigint(20) NULL DEFAULT NULL COMMENT '生产任务ID' AFTER `update_time`,
ADD COLUMN `task_code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '生产任务编号' AFTER `task_id`;

-- ----------------------------
-- 2025-03-18 设备点检记录，陈志强增加,增加设备点检记录表
-- ----------------------------
DROP TABLE IF EXISTS `dv_check_record`;
CREATE TABLE `dv_check_record`  (
  `record_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '记录ID',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `user_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名',
  `nick_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称',
  `workstation_id` bigint(20) NOT NULL COMMENT '工作站ID',
  `workstation_code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '工作站编号',
  `workstation_name` varchar(125) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '工作站名称',
  `machinery_id` bigint(20) NOT NULL COMMENT '设备ID',
  `machinery_code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '设备编码',
  `machinery_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '设备名称',
  `plan_id` bigint(20) NOT NULL COMMENT '计划ID',
  `subject_id` bigint(20) NOT NULL COMMENT '项目ID',
  `subject_code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '项目编码',
  `subject_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '项目名称',
  `operation_flag` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '操作类型',
  `operation_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '操作名称',
  `operation_time` datetime(0) NULL DEFAULT NULL COMMENT '操作时间',
  `remark` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '备注',
  `attr1` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '预留字段1',
  `attr2` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '预留字段2',
  `attr3` int(11) NULL DEFAULT 0 COMMENT '预留字段3',
  `attr4` int(11) NULL DEFAULT 0 COMMENT '预留字段4',
  `create_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '创建者',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '更新者',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`record_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '设备点检记录' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;


-- ----------------------------
-- yxd  物料分类表和物料表添加字段item_or_product
-- ----------------------------
-- 物料分类
ALTER TABLE `base_material_class`
ADD COLUMN `item_or_product` varchar(20) NULL COMMENT '产品物料标识' AFTER `material_class_name`;
-- 物料
ALTER TABLE `base_material`
ADD COLUMN `item_or_product` varchar(20) NULL COMMENT '产品物料标识' AFTER `material_class_code`;


-- ----------------------------
-- 1508 yxd  入库任务表添加一个字段workorder_code      工单编码
-- ----------------------------
-- yxd
ALTER TABLE `wms_task_in`
ADD COLUMN workorder_code VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 2025-03-04 sys
ALTER TABLE `pro_workrecord`
MODIFY COLUMN `nick_name`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称' AFTER `user_name`;
ALTER TABLE `cath_mom_dev_mes_2`.`pro_user_workstation`
MODIFY COLUMN `nick_name` varchar(30) NULL DEFAULT NULL COMMENT '名称' AFTER `user_name`;
ALTER TABLE `cath_mom_dev_mes_2`.`pro_workrecord`
DROP COLUMN `operation_name`,
ADD COLUMN `operation_name` varchar(30) NULL COMMENT '操作名称' AFTER `operation_flag`;

-- 原有苦糖果有问题，默认的值不对，从NORMARL改成NORMAL
ALTER TABLE `cath_mom_dev_mes_2`.`pro_task`
MODIFY COLUMN `status` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT 'NORMAL' COMMENT '生产状态' AFTER `request_date`;

-- ----------------------------
-- 1417 表格设计-表增加字段；增加表设计角色表
-- ----------------------------

-- 如果没有下面两个个字段就增加
select * from set_table_info limit 10

ALTER TABLE set_table_info ADD COLUMN report_type INT NULL;
ALTER TABLE set_table_info ADD COLUMN dept_alias VARCHAR(50) NULL;
ALTER TABLE set_table_info ADD COLUMN user_alias VARCHAR(50) NULL;

-- 创建表设计角色表
SET NAMES utf8mb4;
CREATE TABLE `set_table_info_role`  (
  `table_info_id` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户ID',
  `role_id` bigint(20) NOT NULL COMMENT '角色ID',
  PRIMARY KEY (`table_info_id`, `role_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户和角色关联表' ROW_FORMAT = Dynamic;


-- ----------------------------
-- 1388 筛选去除多余字段，创建更新审核者加放大镜。执行后需清除缓存重启redis
-- ----------------------------
/* 移位任务筛选 不显示是否启用货位字段 */
update set_table_infos set query_field_name=''
where info_id in (select id from set_table_info where bill_code='taskMove')
	and query_field_name like '是否启用货位'

/* 入库、出库、退、移位、转换 接口状态 有两个 这个不要 */
update set_table_infos set query_field_name=''
where info_id in (select id from set_table_info where bill_code IN ('stockIn','backStockIn','stockOut','backStockOut','stockMove','stockChange'))
	and query_field_name like '接口状态' and query_field_alias='interfaceStatus'

/*入库、出库、退、移位、转换 接口状态 如果有客户用过，更新成唯一的那个*/
update set_table_filter_plans set query_field_alias='parameter_interfaceStatus',filter_type='是'
where plan_id in (select id from set_table_filter_plan where bill_code IN ('stockIn','backStockIn','stockOut','backStockOut','stockMove','stockChange'))
	and query_field_alias='interfaceStatus'

/*创建者、更新者、审核者 筛选加放大镜*/
update set_table_infos set basic_flag='user',basic_value='nickName'
where query_field_name like '创建者' or query_field_name like '更新者' or query_field_name like '审核者'


-- ----------------------------
-- 1375 app出库任务校验不好使
-- ----------------------------
--查询如果客户出库有taskOutsCode字段不要执行insert
select * from set_field_setting where bill_code='stockOut' and field='taskOutsCode'
--insert(鑫利)
INSERT INTO `set_field_setting`(`bill_code`, `field`, `field_name`, `required`, `required_condition`, `hidden`, `hidden_condition`, `setting_sort`, `setting_describe`, `flag`, `key_name`, `key_code`, `query_data`, `defaults`, `field_type`, `multiple`, `width`) VALUES ('stockOut', 'taskOutsCode', '出库任务编码|行号', 1, '[[\"billTypeCode\",\"^1$|^3$\"]]', 0, NULL, '11-1', '(app)生产领料1 销售出库3 必录任务', NULL, NULL, NULL, NULL, NULL, 'popupbox', 0, NULL);
--insert(其它)
INSERT INTO `set_field_setting`(`bill_code`, `field`, `field_name`, `required`, `required_condition`, `hidden`, `hidden_condition`, `setting_sort`, `setting_describe`, `flag`, `key_name`, `key_code`, `query_data`, `defaults`, `field_type`, `multiple`, `width`) VALUES ('stockOut', 'taskOutsCode', '出库任务编码|行号', 0, NULL, 0, NULL, '11-1', '(app)生产领料1 销售出库3 必录任务', NULL, NULL, NULL, NULL, NULL, 'popupbox', 0, NULL);

-- ----------------------------
-- 1353 2024-12-02 陈志强：编号规则子表，高级过滤时不能下拉选择字段，问题原因是在表格设计中，没有对应的中文字段
-- ----------------------------

-- 删除 编号规则明细子表 表格设计内容
DELETE FROM `set_table_infos` where info_id = '1009';

-- 增加 编号规则明细子表 表格设计内容
INSERT INTO `set_table_infos`( `info_id`, `query_table`, `query_field`, `query_field_alias`, `query_field_name`, `field_type`, `column_width`, `parameter`, `basic_flag`, `basic_value`, `basic_query_data`, `filter_status`, `is_date_control`, `status`) VALUES ('1009', NULL, 'id', 'id', '', '文本', 100, '', '', '', '', NULL, 1, 0);
INSERT INTO `set_table_infos`(`info_id`, `query_table`, `query_field`, `query_field_alias`, `query_field_name`, `field_type`, `column_width`, `parameter`, `basic_flag`, `basic_value`, `basic_query_data`, `filter_status`, `is_date_control`, `status`) VALUES ( '1009', NULL, 'code_rule_id', 'codeRuleId', '', '文本', 100, '', '', '', '', NULL, 1, 0);
INSERT INTO `set_table_infos`( `info_id`, `query_table`, `query_field`, `query_field_alias`, `query_field_name`, `field_type`, `column_width`, `parameter`, `basic_flag`, `basic_value`, `basic_query_data`, `filter_status`, `is_date_control`, `status`) VALUES ('1009', NULL, 'perfix_values', 'perfixValues', '前缀值', '文本', 100, '', '', '', '', NULL, 1, 0);
INSERT INTO `set_table_infos`(`info_id`, `query_table`, `query_field`, `query_field_alias`, `query_field_name`, `field_type`, `column_width`, `parameter`, `basic_flag`, `basic_value`, `basic_query_data`, `filter_status`, `is_date_control`, `status`) VALUES ('1009', NULL, 'entry_values', 'entryValues', '当前值', '文本', 100, '', '', '', '', NULL, 1, 0);

-- ----------------------------
-- 1342 增加版本日志功能
-- ----------------------------
-- 建表
DROP TABLE IF EXISTS `sys_version_log`;
CREATE TABLE `sys_version_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '标识',
  `version_number` int(11) DEFAULT NULL COMMENT '版本号',
  `version_describe` varchar(2000) DEFAULT NULL COMMENT '版本描述',
  `execute_sql` text COMMENT '执行脚本',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_versionLog_versionNumber` (`version_number`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COMMENT='版本信息日志';

SET FOREIGN_KEY_CHECKS = 1;


-- ----------------------------
-- 1342 增加版本日志功能
-- ----------------------------
-- 建表
DROP TABLE IF EXISTS `sys_version_log`;
CREATE TABLE `sys_version_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '标识',
  `version_number` int(11) DEFAULT NULL COMMENT '版本号',
  `version_describe` varchar(2000) DEFAULT NULL COMMENT '版本描述',
  `execute_sql` text COMMENT '执行脚本',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_versionLog_versionNumber` (`version_number`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COMMENT='版本信息日志';

SET FOREIGN_KEY_CHECKS = 1;