-- ============================================================ -- 工序计费类型/工资定额 → 生产报工工资 → 薪酬核算报工工资 -- 适用:MySQL 8.4(ADD COLUMN 不支持 IF NOT EXISTS,统一用 information_schema 判断) -- 可重复执行 -- ============================================================ -- 1. 工序设置:计费类型(1-计时 2-计件)、工资定额(计时=元/小时,计件=元/件) SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_process' AND column_name = 'billing_type'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_pro_process` ADD COLUMN `billing_type` TINYINT NOT NULL DEFAULT 1 COMMENT ''计费类型:1-计时 2-计件'' AFTER `attention`', '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 = 'wage_rate'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_pro_process` ADD COLUMN `wage_rate` DECIMAL(18,4) NOT NULL DEFAULT 0 COMMENT ''工资定额:计时为元/小时,计件为元/件'' AFTER `billing_type`', '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 = 'billing_type'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_pro_feedback` ADD COLUMN `billing_type` TINYINT NOT NULL DEFAULT 1 COMMENT ''计费类型快照:1-计时 2-计件'' AFTER `feedback_user_id`', '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 = 'wage_rate'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_pro_feedback` ADD COLUMN `wage_rate` DECIMAL(18,4) NOT NULL DEFAULT 0 COMMENT ''工资定额快照:计时为元/小时,计件为元/件'' AFTER `billing_type`', '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 = 'work_hours'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_pro_feedback` ADD COLUMN `work_hours` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT ''本次报工工时(小时),计时计薪必填'' 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_feedback' AND column_name = 'wage_amount'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_pro_feedback` ADD COLUMN `wage_amount` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT ''本次报工工资 = 计时 工时×定额 / 计件 报工数量×定额'' AFTER `work_hours`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- 3. 薪酬核算:报工工资 SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hrm_salary_calculation' AND column_name = 'work_wage'); SET @sql = IF(@exists = 0, 'ALTER TABLE `hrm_salary_calculation` ADD COLUMN `work_wage` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT ''报工工资(核算时可按人调整)'' AFTER `other_allowance`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- 4. 汇总索引用:按报工人+报工时间取月度报工工资 SET @exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'mes_pro_feedback' AND index_name = 'idx_feedback_user_time'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_pro_feedback` ADD INDEX `idx_feedback_user_time` (`feedback_user_id`, `feedback_time`)', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;