-- ============================================================
|
-- 生产核算（成品部门 / 白盒部门）数据库脚本
|
-- 目标库：product-inventory-management-new-pro（按实际环境替换）
|
-- 说明：
|
-- 第 1 部分：结构变更（补列 + 新建配置表）—— 必须执行，幂等可重复执行
|
-- 第 2 部分：演示数据 —— 可选，仅用于本地/演示环境，正式环境请勿执行或按需调整
|
-- 成品/白盒 的归属：在【工序类型配置】里直接把某个工序指定为 成品 或 白盒
|
-- （1=成品部门，2=白盒部门）
|
-- ============================================================
|
SET NAMES utf8mb4;
|
|
/* ============================================================
|
* 第 1 部分：结构变更（必须执行）
|
* ============================================================ */
|
|
-- 1.1 补齐 production_product_main 缺失列
|
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='production_product_main' AND COLUMN_NAME='report_type');
|
SET @s := IF(@c=0, 'ALTER TABLE `production_product_main` ADD COLUMN `report_type` int NULL COMMENT ''报工结果类型：0合格，1轻微返工，2严重返工，3报废'' AFTER `status`', 'DO 0');
|
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
|
|
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='production_product_main' AND COLUMN_NAME='audit_status');
|
SET @s := IF(@c=0, 'ALTER TABLE `production_product_main` ADD COLUMN `audit_status` int NULL DEFAULT 1 COMMENT ''审核状态：0待审核，1审核通过，2审核不通过'' AFTER `report_type`', 'DO 0');
|
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
|
|
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='production_product_main' AND COLUMN_NAME='audit_remark');
|
SET @s := IF(@c=0, 'ALTER TABLE `production_product_main` ADD COLUMN `audit_remark` varchar(255) NULL COMMENT ''审核意见'' AFTER `audit_status`', 'DO 0');
|
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
|
|
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='production_product_main' AND COLUMN_NAME='repair_work_hour');
|
SET @s := IF(@c=0, 'ALTER TABLE `production_product_main` ADD COLUMN `repair_work_hour` decimal(10,2) NULL DEFAULT 0 COMMENT ''补修理工时'' AFTER `work_hour`', 'DO 0');
|
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
|
|
-- 1.2 补齐 product 缺失列 standard_quantity
|
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='product' AND COLUMN_NAME='standard_quantity');
|
SET @s := IF(@c=0, 'ALTER TABLE `product` ADD COLUMN `standard_quantity` decimal(18,4) NULL COMMENT ''标准数量'' AFTER `product_name`', 'DO 0');
|
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
|
|
-- 1.3 已存在报工数据默认：审核通过、合格
|
UPDATE `production_product_main` SET `report_type` = 0 WHERE `report_type` IS NULL;
|
UPDATE `production_product_main` SET `audit_status` = 1 WHERE `audit_status` IS NULL;
|
|
-- 1.4 新建核算配置表
|
CREATE TABLE IF NOT EXISTS `production_costing_process` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
`process_id` bigint NULL COMMENT '工序ID(technology_operation.id)',
|
`dept_type` int NULL COMMENT '类型：1成品部门，2白盒部门',
|
`remark` varchar(255) NULL COMMENT '备注',
|
`create_user` bigint NULL COMMENT '创建人',
|
`create_time` datetime NULL COMMENT '创建时间',
|
`update_user` bigint NULL COMMENT '修改人',
|
`update_time` datetime NULL COMMENT '修改时间',
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uk_process` (`process_id`)
|
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '生产核算-工序类型配置';
|
|
CREATE TABLE IF NOT EXISTS `production_costing_standard` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
`dept_type` int NULL COMMENT '类型：1成品，2白盒',
|
`process_id` bigint NULL COMMENT '工序ID',
|
`product_model_id` bigint NULL COMMENT '型号ID，白盒必填',
|
`standard_quantity` decimal(18,4) NULL COMMENT '标准数量',
|
`unit` varchar(50) NULL COMMENT '单位',
|
`remark` varchar(255) NULL COMMENT '备注',
|
`create_user` bigint NULL COMMENT '创建人',
|
`create_time` datetime NULL COMMENT '创建时间',
|
`update_user` bigint NULL COMMENT '修改人',
|
`update_time` datetime NULL COMMENT '修改时间',
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uk_std` (`dept_type`, `process_id`, `product_model_id`)
|
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '生产核算-标准数量配置';
|
|
CREATE TABLE IF NOT EXISTS `production_costing_factor` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
`process_id` bigint NULL COMMENT '工序ID',
|
`staff_id` bigint NULL COMMENT '员工ID',
|
`staff_name` varchar(255) NULL COMMENT '员工姓名',
|
`violin_factor` decimal(18,4) NULL DEFAULT 1 COMMENT '小提琴折算率(基准)',
|
`viola_factor` decimal(18,4) NULL COMMENT '大提琴折算率',
|
`bass_factor` decimal(18,4) NULL COMMENT '贝斯折算率',
|
`remark` varchar(255) NULL COMMENT '备注',
|
`create_user` bigint NULL COMMENT '创建人',
|
`create_time` datetime NULL COMMENT '创建时间',
|
`update_user` bigint NULL COMMENT '修改人',
|
`update_time` datetime NULL COMMENT '修改时间',
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uk_factor` (`process_id`, `staff_id`)
|
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '生产核算-折算率配置(工序+员工)';
|
|
CREATE TABLE IF NOT EXISTS `production_costing_workday` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
`work_month` varchar(7) NULL COMMENT '年月 yyyy-MM',
|
`work_days` int NULL COMMENT '当月工作日天数',
|
`remark` varchar(255) NULL COMMENT '备注',
|
`create_user` bigint NULL COMMENT '创建人',
|
`create_time` datetime NULL COMMENT '创建时间',
|
`update_user` bigint NULL COMMENT '修改人',
|
`update_time` datetime NULL COMMENT '修改时间',
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uk_work_month` (`work_month`)
|
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '生产核算-月度工作日配置';
|
|
/* ============================================================
|
* 第 2 部分：演示数据（可选，仅本地/演示环境）
|
* ============================================================ */
|
|
-- 2.1 工序类型配置（成品：冷工艺27/硫化28；白盒：切割2/收线25）
|
INSERT INTO `production_costing_process` (`process_id`, `dept_type`, `remark`, `create_time`)
|
SELECT 27, 1, '冷工艺-成品', NOW() WHERE NOT EXISTS (SELECT 1 FROM `production_costing_process` WHERE `process_id`=27);
|
INSERT INTO `production_costing_process` (`process_id`, `dept_type`, `remark`, `create_time`)
|
SELECT 28, 1, '硫化-成品', NOW() WHERE NOT EXISTS (SELECT 1 FROM `production_costing_process` WHERE `process_id`=28);
|
INSERT INTO `production_costing_process` (`process_id`, `dept_type`, `remark`, `create_time`)
|
SELECT 2, 2, '切割-白盒', NOW() WHERE NOT EXISTS (SELECT 1 FROM `production_costing_process` WHERE `process_id`=2);
|
INSERT INTO `production_costing_process` (`process_id`, `dept_type`, `remark`, `create_time`)
|
SELECT 25, 2, '收线-白盒', NOW() WHERE NOT EXISTS (SELECT 1 FROM `production_costing_process` WHERE `process_id`=25);
|
|
-- 2.2 标准数量配置（成品：按工序；白盒：按工序+型号）
|
INSERT INTO `production_costing_standard` (`dept_type`, `process_id`, `product_model_id`, `standard_quantity`, `unit`, `remark`, `create_time`)
|
SELECT 1, 27, NULL, 10, 'm', '冷工艺标准数量', NOW() WHERE NOT EXISTS (SELECT 1 FROM `production_costing_standard` WHERE `dept_type`=1 AND `process_id`=27 AND `product_model_id` IS NULL);
|
INSERT INTO `production_costing_standard` (`dept_type`, `process_id`, `product_model_id`, `standard_quantity`, `unit`, `remark`, `create_time`)
|
SELECT 1, 28, NULL, 20, 'm', '硫化标准数量', NOW() WHERE NOT EXISTS (SELECT 1 FROM `production_costing_standard` WHERE `dept_type`=1 AND `process_id`=28 AND `product_model_id` IS NULL);
|
INSERT INTO `production_costing_standard` (`dept_type`, `process_id`, `product_model_id`, `standard_quantity`, `unit`, `remark`, `create_time`)
|
SELECT 2, 25, 251, 5, 'kg/km', '收线-铝合金线', NOW() WHERE NOT EXISTS (SELECT 1 FROM `production_costing_standard` WHERE `dept_type`=2 AND `process_id`=25 AND `product_model_id`=251);
|
INSERT INTO `production_costing_standard` (`dept_type`, `process_id`, `product_model_id`, `standard_quantity`, `unit`, `remark`, `create_time`)
|
SELECT 2, 25, 256, 2, 'm', '收线-铝合金绞线', NOW() WHERE NOT EXISTS (SELECT 1 FROM `production_costing_standard` WHERE `dept_type`=2 AND `process_id`=25 AND `product_model_id`=256);
|
INSERT INTO `production_costing_standard` (`dept_type`, `process_id`, `product_model_id`, `standard_quantity`, `unit`, `remark`, `create_time`)
|
SELECT 2, 2, 246, 4, 'kg/km', '切割-铝合金线', NOW() WHERE NOT EXISTS (SELECT 1 FROM `production_costing_standard` WHERE `dept_type`=2 AND `process_id`=2 AND `product_model_id`=246);
|
|
-- 2.3 折算率配置（工序+员工）
|
INSERT INTO `production_costing_factor` (`process_id`, `staff_id`, `staff_name`, `violin_factor`, `viola_factor`, `bass_factor`, `remark`, `create_time`)
|
SELECT 27, 1, '管理员账号', 1, 3, 6, '冷工艺-折算率', NOW() WHERE NOT EXISTS (SELECT 1 FROM `production_costing_factor` WHERE `process_id`=27 AND `staff_id`=1);
|
INSERT INTO `production_costing_factor` (`process_id`, `staff_id`, `staff_name`, `violin_factor`, `viola_factor`, `bass_factor`, `remark`, `create_time`)
|
SELECT 28, 1, '管理员账号', 1, 2, 4, '硫化-折算率', NOW() WHERE NOT EXISTS (SELECT 1 FROM `production_costing_factor` WHERE `process_id`=28 AND `staff_id`=1);
|
|
-- 2.4 乐器演示数据（小提琴/大提琴/贝斯）+ 报工链
|
INSERT INTO `product` (`id`, `parent_id`, `product_name`, `create_user`, `dept_id`)
|
SELECT 510, 276, '小提琴', 1, 100 WHERE NOT EXISTS (SELECT 1 FROM `product` WHERE `id` = 510);
|
INSERT INTO `product` (`id`, `parent_id`, `product_name`, `create_user`, `dept_id`)
|
SELECT 511, 276, '大提琴', 1, 100 WHERE NOT EXISTS (SELECT 1 FROM `product` WHERE `id` = 511);
|
INSERT INTO `product` (`id`, `parent_id`, `product_name`, `create_user`, `dept_id`)
|
SELECT 512, 276, '贝斯', 1, 100 WHERE NOT EXISTS (SELECT 1 FROM `product` WHERE `id` = 512);
|
|
INSERT INTO `product_model` (`id`, `product_id`, `model`, `unit`, `create_user`, `dept_id`)
|
SELECT 5001, 510, 'VIOLIN-STD', '把', 1, 100 WHERE NOT EXISTS (SELECT 1 FROM `product_model` WHERE `id` = 5001);
|
INSERT INTO `product_model` (`id`, `product_id`, `model`, `unit`, `create_user`, `dept_id`)
|
SELECT 5002, 511, 'CELLO-STD', '把', 1, 100 WHERE NOT EXISTS (SELECT 1 FROM `product_model` WHERE `id` = 5002);
|
INSERT INTO `product_model` (`id`, `product_id`, `model`, `unit`, `create_user`, `dept_id`)
|
SELECT 5003, 512, 'BASS-STD', '把', 1, 100 WHERE NOT EXISTS (SELECT 1 FROM `product_model` WHERE `id` = 5003);
|
|
INSERT INTO `production_order` (`id`, `product_model_id`, `technology_routing_id`, `nps_no`, `quantity`, `create_user`, `dept_id`)
|
SELECT 5000, 5001, 0, 'DEMO-ORDER-1', 100, 1, 100 WHERE NOT EXISTS (SELECT 1 FROM `production_order` WHERE `id` = 5000);
|
INSERT INTO `production_order_routing_operation` (`id`, `production_order_id`, `technology_routing_operation_id`, `technology_operation_id`, `operation_name`, `product_model_id`, `type`, `create_user`, `dept_id`)
|
SELECT 5000, 5000, 0, 27, '冷工艺', 5001, 1, 1, 100 WHERE NOT EXISTS (SELECT 1 FROM `production_order_routing_operation` WHERE `id` = 5000);
|
INSERT INTO `production_operation_task` (`id`, `production_order_routing_operation_id`, `technology_routing_operation_id`, `work_order_no`, `status`, `production_order_id`, `create_user`, `dept_id`)
|
SELECT 5000, 5000, 0, 'DEMO-WO-1', 4, 5000, 1, 100 WHERE NOT EXISTS (SELECT 1 FROM `production_operation_task` WHERE `id` = 5000);
|
INSERT INTO `production_product_main` (`id`, `product_no`, `user_id`, `user_name`, `production_operation_task_id`, `status`, `dept_id`, `create_time`, `create_user`, `report_type`, `audit_status`)
|
SELECT 5000, 'BG-DEMO-001', 1, '管理员账号', 5000, 4, 100, '2026-09-15 10:00:00', 1, 0, 1 WHERE NOT EXISTS (SELECT 1 FROM `production_product_main` WHERE `id` = 5000);
|
INSERT INTO `production_product_output` (`production_product_main_id`, `product_model_id`, `quantity`, `scrap_qty`, `create_user`, `dept_id`)
|
SELECT 5000, 5001, 20, 0, 1, 100 WHERE NOT EXISTS (SELECT 1 FROM `production_product_output` WHERE `production_product_main_id` = 5000 AND `product_model_id` = 5001);
|
INSERT INTO `production_product_output` (`production_product_main_id`, `product_model_id`, `quantity`, `scrap_qty`, `create_user`, `dept_id`)
|
SELECT 5000, 5002, 10, 0, 1, 100 WHERE NOT EXISTS (SELECT 1 FROM `production_product_output` WHERE `production_product_main_id` = 5000 AND `product_model_id` = 5002);
|
INSERT INTO `production_product_output` (`production_product_main_id`, `product_model_id`, `quantity`, `scrap_qty`, `create_user`, `dept_id`)
|
SELECT 5000, 5003, 5, 0, 1, 100 WHERE NOT EXISTS (SELECT 1 FROM `production_product_output` WHERE `production_product_main_id` = 5000 AND `product_model_id` = 5003);
|
|
SELECT '生产核算数据库脚本执行完成' AS result_message;
|
