DROP TABLE IF EXISTS `production_plan`;
|
|
CREATE TABLE production_plan
|
(
|
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
|
form_instance_id VARCHAR(64) COMMENT '表单实例ID',
|
serial_no VARCHAR(50) COMMENT '流水号',
|
apply_no VARCHAR(100) COMMENT '申请单编号',
|
customer_name VARCHAR(200) COMMENT '客户名称',
|
material_code VARCHAR(100) COMMENT '物料编码',
|
product_name VARCHAR(200) COMMENT '产品名称',
|
product_spec VARCHAR(200) COMMENT '产品规格',
|
length INT COMMENT '长',
|
width INT COMMENT '宽',
|
height INT COMMENT '高',
|
quantity INT COMMENT '块数',
|
total_count INT COMMENT '此次统计数量',
|
volume DECIMAL(10, 4) COMMENT '方数',
|
strength VARCHAR(50) COMMENT '强度',
|
start_date DATETIME COMMENT '开始日期',
|
end_date DATETIME COMMENT '结束日期',
|
submitter VARCHAR(100) COMMENT '提交人',
|
submit_org VARCHAR(200) COMMENT '提交人组织',
|
remark_one TEXT COMMENT '备注1',
|
remark_two TEXT COMMENT '备注2',
|
creator_name VARCHAR(100) COMMENT '创建人',
|
modifier_name VARCHAR(100) COMMENT '修改人',
|
form_created_time DATETIME COMMENT '表单创建时间',
|
form_modified_time DATETIME COMMENT '表单修改时间',
|
data_sync_type TINYINT DEFAULT 1 COMMENT '数据同步类型：1=手动 2=定时任务',
|
data_source_type TINYINT DEFAULT 1 COMMENT '数据来源类型：1=销售订单 2=销售预测',
|
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '数据库创建时间',
|
update_time DATETIME COMMENT '数据库更新时间',
|
|
INDEX idx_apply_no (apply_no),
|
INDEX idx_serial_no (serial_no),
|
INDEX idx_form_instance_id (form_instance_id),
|
INDEX idx_material_code (material_code)
|
) ENGINE = InnoDB
|
DEFAULT CHARSET = utf8mb4 COMMENT ='销售生产计划';
|
|
CREATE TABLE `product-inventory-management-zsjc`.`energy`
|
(
|
`id` int NOT NULL AUTO_INCREMENT,
|
`energy_tyep` varchar(255) NULL COMMENT '能源类型',
|
`energy_name` varchar(255) NULL COMMENT '能源名称',
|
`unit` varchar(255) NULL COMMENT '单位',
|
`unit_price` decimal(10, 2) NULL COMMENT '单价',
|
`remark` varchar(255) NULL COMMENT '备注',
|
`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
|
`update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
|
`create_user` bigint NULL DEFAULT NULL COMMENT '创建者',
|
`update_user` bigint NULL DEFAULT NULL COMMENT '更新者',
|
PRIMARY KEY (`id`)
|
) COMMENT = '能源类型';
|
|
CREATE TABLE `product-inventory-management-zsjc`.`energy_consumption_detail`
|
(
|
`id` int NOT NULL AUTO_INCREMENT,
|
`energy_id` int NULL COMMENT '外检关联能源类型id',
|
`type` varchar(255) NULL COMMENT '办公/生产',
|
`meter_reading_location` varchar(255) NULL COMMENT '抄表位置',
|
`meter_reading_date` date NULL COMMENT '抄表日期',
|
`start_code` decimal(10, 2) NULL COMMENT '起码',
|
`stop_code` decimal(10, 2) NULL COMMENT '止码',
|
`dosage` decimal(10, 2) NULL COMMENT '用量',
|
`remark` varchar(255) NULL COMMENT '备注',
|
`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
|
`update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
|
`create_user` bigint NULL DEFAULT NULL COMMENT '创建者',
|
`update_user` bigint NULL DEFAULT NULL COMMENT '更新者',
|
PRIMARY KEY (`id`)
|
) COMMENT = '能源类型-能耗抄表明细';
|
|
CREATE TABLE `product-inventory-management-zsjc`.`energy_consumption_detail_file`
|
(
|
`id` int NOT NULL AUTO_INCREMENT,
|
`energy_consumption_detail_id` int NULL COMMENT '关联外键能耗抄表id',
|
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
|
`url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
|
`file_size` double NULL DEFAULT NULL,
|
`create_time` timestamp(0) NULL DEFAULT NULL,
|
`create_user` int NULL DEFAULT NULL,
|
`update_time` timestamp(0) NULL DEFAULT NULL,
|
`update_user` int NULL DEFAULT NULL,
|
PRIMARY KEY (`id`)
|
) COMMENT = '能源类型-能耗抄表明细_附件';
|
|
alter table product_order
|
drop column sales_ledger_id,
|
drop column sale_ledger_product_id,
|
drop column product_model_id;
|
|
alter table production_plan
|
add assigned_quantity DECIMAL(10, 4) default 0 not null COMMENT '下发数量';
|
|
alter table product_order
|
add plan_complete_time date NULL DEFAULT NULL COMMENT '计划完成时间';
|
|
# 生产订单与生产计划关联表
|
drop table if exists product_order_plan;
|
create table product_order_plan
|
(
|
id bigint auto_increment primary key,
|
product_order_id bigint not null default 0 comment '生产订单id',
|
production_plan_id bigint not null default 0 comment '生产计划id',
|
create_time datetime null comment '录入时间',
|
update_time datetime null comment '更新时间',
|
assigned_quantity DECIMAL(10, 4) default 0 not null comment '下发数量',
|
index idx_product_order_id (product_order_id),
|
index idx_production_plan_id (production_plan_id),
|
unique idx_product_order_production_plan (product_order_id, production_plan_id)
|
);
|
|
CREATE TABLE `product_material`
|
(
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
`tenant_id` BIGINT DEFAULT NULL COMMENT '租户ID',
|
`material_type_id` INT DEFAULT NULL COMMENT '物料类型ID',
|
`inventory_category_id` INT DEFAULT NULL COMMENT '存货类别ID',
|
`material_name` VARCHAR(255) NOT NULL COMMENT '物料名称',
|
`base_unit` VARCHAR(50) DEFAULT NULL COMMENT '基本单位',
|
`remark` TEXT COMMENT '备注',
|
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
`update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`),
|
KEY `idx_type_id` (`material_type_id`),
|
KEY `idx_cat_id` (`inventory_category_id`),
|
UNIQUE KEY `uk_material_name` (`material_name`)
|
) ENGINE = INNODB
|
DEFAULT CHARSET = utf8mb4 COMMENT = '物料主表';
|
CREATE TABLE `product_material_sku`
|
(
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
`material_id` BIGINT NOT NULL COMMENT '物料ID',
|
`identifier_code` VARCHAR(100) DEFAULT NULL COMMENT '标识编码',
|
`material_code` VARCHAR(100) DEFAULT NULL COMMENT '物料编码',
|
`specification` VARCHAR(255) DEFAULT NULL COMMENT '规格型号',
|
`supply_type` VARCHAR(20) DEFAULT NULL COMMENT '供应方式',
|
`originator_name` VARCHAR(100) DEFAULT NULL COMMENT '提交人姓名',
|
`originator_org` VARCHAR(255) DEFAULT '宁夏中创绿能实业集团有限公司',
|
`form_instance_id` VARCHAR(100) DEFAULT NULL COMMENT '宜搭表单实例ID',
|
`form_modified_time` DATETIME DEFAULT NULL COMMENT '宜搭修改时间',
|
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
`update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`),
|
KEY `idx_material_id` (`material_id`),
|
UNIQUE KEY `uk_material_spec` (`material_id`, `specification`),
|
CONSTRAINT `fk_material` FOREIGN KEY (`material_id`) REFERENCES `product_material` (`id`) ON DELETE CASCADE
|
) ENGINE = INNODB
|
DEFAULT CHARSET = utf8mb4 COMMENT = '物料规格表';
|
|
ALTER TABLE product_material_sku
|
DROP FOREIGN KEY fk_material;
|
|
DROP TABLE IF EXISTS `product_material_config`;
|
CREATE TABLE `product_material_config`
|
(
|
`id` int NOT NULL AUTO_INCREMENT,
|
`config_type` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '区分类型: MATERIAL_TYPE 或 INVENTORY_CAT',
|
`config_name` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '显示的名称',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB
|
AUTO_INCREMENT = 39
|
CHARACTER SET = utf8mb3
|
COLLATE = utf8mb3_general_ci COMMENT = '物料信息表配置表'
|
ROW_FORMAT = Dynamic;
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
|
alter table production_plan
|
add status tinyint default 0 not null comment '状态 0未下发 1已下发';
|
|
|
ALTER TABLE `product-inventory-management-zsjc`.`production_plan`
|
DROP COLUMN `data_sync_type`,
|
MODIFY COLUMN `data_source_type` tinyint NULL DEFAULT 1 COMMENT '数据来源类型：1=钉钉同步 2=手动新增' AFTER `form_modified_time`;
|
|
-- 删除旧表
|
DROP TABLE IF EXISTS `base_param`;
|
DROP TABLE IF EXISTS `product_process_param`;
|
|
CREATE TABLE `base_param`
|
(
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
`param_key` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '参数唯一标识',
|
`param_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '参数名称',
|
`param_type` tinyint NOT NULL COMMENT '参数类型(1数字 2文本 3下拉选择 4时间)',
|
`param_format` varchar(255) DEFAULT NULL COMMENT '参数格式',
|
`value_mode` tinyint DEFAULT '1' COMMENT '值模式(1单值 2区间)',
|
`unit` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '单位',
|
`is_required` tinyint DEFAULT '0' COMMENT '是否必填',
|
`remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
|
`create_user` varchar(64) DEFAULT NULL COMMENT '创建人',
|
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`update_user` varchar(64) DEFAULT NULL COMMENT '修改人',
|
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
|
`tenant_id` bigint DEFAULT NULL COMMENT '租户ID',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB
|
AUTO_INCREMENT = 10
|
DEFAULT CHARSET = utf8mb4
|
COLLATE = utf8mb4_0900_ai_ci COMMENT ='基础参数定义表';
|
|
CREATE TABLE `product_process_param`
|
(
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
`process_id` bigint NOT NULL COMMENT '所属工序ID (product_process.id)',
|
`param_id` bigint NOT NULL COMMENT '关联基础参数ID (base_param.id)',
|
`standard_value` varchar(200) DEFAULT NULL COMMENT '在此工序设定的标准值(单值模式)',
|
`min_value` decimal(10, 2) DEFAULT NULL COMMENT '在此工序设定的标准最小值(区间模式)',
|
`max_value` decimal(10, 2) DEFAULT NULL COMMENT '在此工序设定的标准最大值(区间模式)',
|
`is_required` tinyint NOT NULL DEFAULT '0' COMMENT '在此工序中是否必填(0-否, 1-是)',
|
`sort` int NOT NULL DEFAULT '0' COMMENT '排序号',
|
`tenant_id` bigint DEFAULT NULL COMMENT '租户ID',
|
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
PRIMARY KEY (`id`) USING BTREE,
|
KEY `idx_process_id` (`process_id`) USING BTREE,
|
KEY `idx_param_id` (`param_id`) USING BTREE
|
) ENGINE = InnoDB
|
AUTO_INCREMENT = 1
|
DEFAULT CHARSET = utf8mb4
|
COLLATE = utf8mb4_0900_ai_ci COMMENT ='工序绑定参数表';
|
|
ALTER TABLE `product_process` MODIFY COLUMN `name` varchar(255) COMMENT '工序名称';
|
ALTER TABLE `product_process` MODIFY COLUMN `no` varchar(255) COMMENT '工序编号';
|
ALTER TABLE `product_process` ADD COLUMN `status` tinyint(1) DEFAULT '1' COMMENT '状态：0-停用，1-启用' AFTER `no`;
|
ALTER TABLE `product_process` MODIFY COLUMN `type` bigint COMMENT '类型：0-计时，1-计件';
|
ALTER TABLE `product_process` MODIFY COLUMN `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间';
|
ALTER TABLE `product_process` MODIFY COLUMN `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间';
|
|
ALTER TABLE `product-inventory-management-zsjc`.`customer`
|
ADD COLUMN `form_instance_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '宜搭表单实例ID' AFTER `customer_type`,
|
ADD COLUMN `form_modified_time` datetime(0) NULL DEFAULT NULL COMMENT '宜搭修改时间' AFTER `form_instance_id`;
|
