-- ============================================================ -- 工时报工:标准工时(考核对比)+ 辅助工时 + 工时考核报表 -- 适用:MySQL 8.4(ADD COLUMN 不支持 IF NOT EXISTS,统一用 information_schema 判断) -- 可重复执行 -- ============================================================ -- 1. 工序设置:标准工时计算方式与参数绑定 -- std_time_mode:0-未启用 1-转速×节距 2-线速度直取 3-满速百分比×节距 SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_process' AND column_name = 'std_time_mode'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_pro_process` ADD COLUMN `std_time_mode` TINYINT NOT NULL DEFAULT 0 COMMENT ''标准工时计算方式:0-未启用 1-转速×节距 2-线速度直取 3-满速百分比×节距'' AFTER `wage_rate`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_process' AND column_name = 'std_time_factor'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_pro_process` ADD COLUMN `std_time_factor` DECIMAL(18,4) NOT NULL DEFAULT 1 COMMENT ''标准工时折算系数(束丝 1.8,其余 1)'' AFTER `std_time_mode`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_process' AND column_name = 'max_rpm'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_pro_process` ADD COLUMN `max_rpm` DECIMAL(18,4) NOT NULL DEFAULT 0 COMMENT ''机台满转速(r/min),方式3使用'' AFTER `std_time_factor`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_process' AND column_name = 'std_rpm_param_code'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_pro_process` ADD COLUMN `std_rpm_param_code` VARCHAR(64) NULL COMMENT ''转速参数编码'' AFTER `max_rpm`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_process' AND column_name = 'std_pitch_param_code'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_pro_process` ADD COLUMN `std_pitch_param_code` VARCHAR(64) NULL COMMENT ''节距参数编码'' AFTER `std_rpm_param_code`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_process' AND column_name = 'std_speed_param_code'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_pro_process` ADD COLUMN `std_speed_param_code` VARCHAR(64) NULL COMMENT ''线速度(米/分)参数编码'' AFTER `std_pitch_param_code`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_process' AND column_name = 'std_percent_param_code'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_pro_process` ADD COLUMN `std_percent_param_code` VARCHAR(64) NULL COMMENT ''转速百分比参数编码,方式3使用'' AFTER `std_speed_param_code`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- 2. 生产报工:生产长度 + 标准工时(只用于考核对比,不参与工资) SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_feedback' AND column_name = 'production_length'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_pro_feedback` ADD COLUMN `production_length` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT ''本次生产长度(米),仅用于计算标准工时'' AFTER `wage_amount`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_feedback' AND column_name = 'standard_work_hours'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_pro_feedback` ADD COLUMN `standard_work_hours` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT ''标准工时(小时),只用于考核对比'' AFTER `production_length`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_feedback' AND column_name = 'standard_primary_hours'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_pro_feedback` ADD COLUMN `standard_primary_hours` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT ''标准工时-主工时(小时)'' AFTER `standard_work_hours`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_feedback' AND column_name = 'standard_aux_hours'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_pro_feedback` ADD COLUMN `standard_aux_hours` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT ''标准工时-辅助工时(小时)'' AFTER `standard_primary_hours`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_feedback' AND column_name = 'std_calc_note'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_pro_feedback` ADD COLUMN `std_calc_note` VARCHAR(500) NULL COMMENT ''标准工时计算依据快照'' AFTER `standard_aux_hours`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- 3. 辅助工时配置表 CREATE TABLE IF NOT EXISTS `mes_pro_worktime_aux` ( `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '编号', `process_id` BIGINT NOT NULL COMMENT '工序编号', `workstation_id` BIGINT NULL COMMENT '工作站(机台)编号,空=不限机台', `item_code` VARCHAR(64) NOT NULL COMMENT '辅助项目编码', `item_name` VARCHAR(100) NOT NULL COMMENT '辅助项目名称', `measure_type` TINYINT NOT NULL DEFAULT 1 COMMENT '计量方式:1-按次 2-按个 3-按盘 4-按米折算', `duration_minutes` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '单次/单个/单盘分钟数(方式4不用)', `speed_m_per_min` DECIMAL(18,4) NOT NULL DEFAULT 0 COMMENT '折算速度(米/分),方式4用:时间=米数÷速度', `material_from` VARCHAR(100) NULL COMMENT '换色/换料起始材料', `material_to` VARCHAR(100) NULL COMMENT '换色/换料目标材料', `status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态:0-启用 1-禁用', `sort` INT NOT NULL DEFAULT 0 COMMENT '排序', `remark` VARCHAR(250) NULL COMMENT '备注', `creator` VARCHAR(64) NULL DEFAULT '', `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updater` VARCHAR(64) NULL DEFAULT '', `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `deleted` BIT(1) NOT NULL DEFAULT b'0', PRIMARY KEY (`id`), KEY `idx_worktime_aux_process` (`process_id`, `workstation_id`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = 'MES 辅助工时配置'; -- 4. 报工辅助工时明细(报工时录入的次数快照) CREATE TABLE IF NOT EXISTS `mes_pro_feedback_aux` ( `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '编号', `feedback_id` BIGINT NOT NULL COMMENT '报工编号', `aux_item_id` BIGINT NOT NULL COMMENT '辅助工时项目编号', `item_name` VARCHAR(100) NOT NULL COMMENT '辅助项目名称快照', `measure_type` TINYINT NOT NULL DEFAULT 1 COMMENT '计量方式:1-按次 2-按个 3-按盘 4-按米折算', `quantity` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '次数/个数/盘数/米数', `material_from` VARCHAR(100) NULL COMMENT '换色起始材料', `material_to` VARCHAR(100) NULL COMMENT '换色目标材料', `minutes` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '折算分钟数', `remark` VARCHAR(250) NULL COMMENT '备注', `creator` VARCHAR(64) NULL DEFAULT '', `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updater` VARCHAR(64) NULL DEFAULT '', `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `deleted` BIT(1) NOT NULL DEFAULT b'0', PRIMARY KEY (`id`), KEY `idx_feedback_aux_feedback` (`feedback_id`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = 'MES 报工辅助工时明细'; -- 5. 菜单:辅助工时配置、工时考核报表 INSERT IGNORE INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (1075650, '辅助工时配置', 'mes:pro-worktime-aux:query', 2, 8, 5700, 'worktime-aux', 'ep:timer', 'mes/pro/worktime-aux/index', 'MesProWorktimeAux', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1075651, '工时考核报表', '', 2, 9, 5700, 'worktime-report', 'ep:data-analysis', 'mes/pro/worktime-report/index', 'MesProWorktimeReport', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1075652, '辅助工时新增', 'mes:pro-worktime-aux:create', 3, 1, 1075650, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1075653, '辅助工时修改', 'mes:pro-worktime-aux:update', 3, 2, 1075650, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1075654, '辅助工时删除', 'mes:pro-worktime-aux:delete', 3, 3, 1075650, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1075655, '工时考核查询', 'mes:pro-worktime-report:query', 3, 1, 1075651, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0');