langjiu-project/FineReport/生产/二郎厂/填报/成本系统数据维护.sql

244 lines
9.6 KiB
MySQL
Raw Permalink Normal View History

2025-06-18 17:09:16 +08:00
-- *****************************
-- 创建人员: 杨坤安
-- 创建时间: 2025年4月28日
-- 功能描述: 成本系统数据维护
-- *****************************
DROP TABLE IF EXISTS dm.input_cost_sys_data;
CREATE TABLE dm.input_cost_sys_data (
id varchar(200) PRIMARY KEY NOT NULL
, create_user varchar(100)
,create_date timestamp
,table1 varchar(200)
,table2 varchar(200)
,table3 varchar(200)
,table4 varchar(200)
,table5 varchar(200)
,table6 varchar(200)
,list_date varchar(100)
,dept_code varchar(100)
,dept_name varchar(200)
,class_code varchar(100)
,class_name varchar(200)
,code varchar(100)
,num bigint
,amt decimal(32,8)
,price decimal(16,8)
,remark1 varchar(200)
,remark2 varchar(200)
,remark3 varchar(200)
,remark4 varchar(200)
,remark5 varchar(200)
,remark6 varchar(200)
,remark7 varchar(200)
,update_date timestamp
,update_by varchar(50)
);
-- 为表添加注释
COMMENT ON TABLE dm.input_cost_sys_data IS '成本系统数据维护';
-- 部门 班组 编码 数量 金额 单价 备注1 备注2 备注3 备注4 备注5 备注6 备注7
-- 为字段添加注释
COMMENT ON COLUMN dm.input_cost_sys_data.create_user IS '制单人';
COMMENT ON COLUMN dm.input_cost_sys_data.id IS '主键ID';
COMMENT ON COLUMN dm.input_cost_sys_data.create_date IS '制单日期';
COMMENT ON COLUMN dm.input_cost_sys_data.table1 IS '主表1';
COMMENT ON COLUMN dm.input_cost_sys_data.table2 IS '主表2';
COMMENT ON COLUMN dm.input_cost_sys_data.table3 IS '主表3';
COMMENT ON COLUMN dm.input_cost_sys_data.table4 IS '主表4';
COMMENT ON COLUMN dm.input_cost_sys_data.table5 IS '主表5';
COMMENT ON COLUMN dm.input_cost_sys_data.table6 IS '主表6';
COMMENT ON COLUMN dm.input_cost_sys_data.list_date IS '明细表日期';
COMMENT ON COLUMN dm.input_cost_sys_data.dept_code IS '部门编码';
COMMENT ON COLUMN dm.input_cost_sys_data.dept_name IS '部门名称';
COMMENT ON COLUMN dm.input_cost_sys_data.class_code IS '班组编码';
COMMENT ON COLUMN dm.input_cost_sys_data.class_name IS '班组名称';
COMMENT ON COLUMN dm.input_cost_sys_data.code IS '编码';
COMMENT ON COLUMN dm.input_cost_sys_data.num IS '数量';
COMMENT ON COLUMN dm.input_cost_sys_data.amt IS '金额';
COMMENT ON COLUMN dm.input_cost_sys_data.price IS '单价';
COMMENT ON COLUMN dm.input_cost_sys_data.remark1 IS '备注1';
COMMENT ON COLUMN dm.input_cost_sys_data.remark2 IS '备注2';
COMMENT ON COLUMN dm.input_cost_sys_data.remark3 IS '备注3';
COMMENT ON COLUMN dm.input_cost_sys_data.remark4 IS '备注4';
COMMENT ON COLUMN dm.input_cost_sys_data.remark5 IS '备注5';
COMMENT ON COLUMN dm.input_cost_sys_data.remark6 IS '备注6';
COMMENT ON COLUMN dm.input_cost_sys_data.remark7 IS '备注7';
COMMENT ON COLUMN dm.input_cost_sys_data.update_date IS '更新日期';
COMMENT ON COLUMN dm.input_cost_sys_data.update_by IS '更新人';
DROP TABLE IF EXISTS dm.input_cost_sys_data_list;
CREATE TABLE dm.input_cost_sys_data_list (
id varchar(200) PRIMARY KEY NOT NULL
,cost_id varchar(200)
,create_user varchar(100)
,create_date timestamp
,table1 varchar(200)
,table2 varchar(200)
,table3 varchar(200)
,table4 varchar(200)
,table5 varchar(200)
,table6 varchar(200)
,list_date varchar(100)
,dept_code varchar(100)
,dept_name varchar(200)
,class_code varchar(100)
,class_name varchar(200)
,code varchar(100)
,num bigint
,amt decimal(32,8)
,price decimal(16,8)
,remark1 varchar(200)
,remark2 varchar(200)
,remark3 varchar(200)
,remark4 varchar(200)
,remark5 varchar(200)
,remark6 varchar(200)
,remark7 varchar(200)
,update_date timestamp
,update_by varchar(50)
);
-- 为表添加注释
COMMENT ON TABLE dm.input_cost_sys_data_list IS '成本系统数据维护';
-- 部门 班组 编码 数量 金额 单价 备注1 备注2 备注3 备注4 备注5 备注6 备注7
-- 为字段添加注释
COMMENT ON COLUMN dm.input_cost_sys_data_list.create_user IS '制单人';
COMMENT ON COLUMN dm.input_cost_sys_data_list.id IS '主键ID';
COMMENT ON COLUMN dm.input_cost_sys_data_list.cost_id IS '表ID';
COMMENT ON COLUMN dm.input_cost_sys_data_list.create_date IS '制单日期';
COMMENT ON COLUMN dm.input_cost_sys_data_list.table1 IS '主表1';
COMMENT ON COLUMN dm.input_cost_sys_data_list.table2 IS '主表2';
COMMENT ON COLUMN dm.input_cost_sys_data_list.table3 IS '主表3';
COMMENT ON COLUMN dm.input_cost_sys_data_list.table4 IS '主表4';
COMMENT ON COLUMN dm.input_cost_sys_data_list.table5 IS '主表5';
COMMENT ON COLUMN dm.input_cost_sys_data_list.table6 IS '主表6';
COMMENT ON COLUMN dm.input_cost_sys_data_list.list_date IS '明细表日期';
COMMENT ON COLUMN dm.input_cost_sys_data_list.dept_code IS '部门编码';
COMMENT ON COLUMN dm.input_cost_sys_data_list.dept_name IS '部门名称';
COMMENT ON COLUMN dm.input_cost_sys_data_list.class_code IS '班组编码';
COMMENT ON COLUMN dm.input_cost_sys_data_list.class_name IS '班组名称';
COMMENT ON COLUMN dm.input_cost_sys_data_list.code IS '编码';
COMMENT ON COLUMN dm.input_cost_sys_data_list.num IS '数量';
COMMENT ON COLUMN dm.input_cost_sys_data_list.amt IS '金额';
COMMENT ON COLUMN dm.input_cost_sys_data_list.price IS '单价';
COMMENT ON COLUMN dm.input_cost_sys_data_list.remark1 IS '备注1';
COMMENT ON COLUMN dm.input_cost_sys_data_list.remark2 IS '备注2';
COMMENT ON COLUMN dm.input_cost_sys_data_list.remark3 IS '备注3';
COMMENT ON COLUMN dm.input_cost_sys_data_list.remark4 IS '备注4';
COMMENT ON COLUMN dm.input_cost_sys_data_list.remark5 IS '备注5';
COMMENT ON COLUMN dm.input_cost_sys_data_list.remark6 IS '备注6';
COMMENT ON COLUMN dm.input_cost_sys_data_list.remark7 IS '备注7';
COMMENT ON COLUMN dm.input_cost_sys_data_list.update_date IS '更新日期';
COMMENT ON COLUMN dm.input_cost_sys_data_list.update_by IS '更新人';
SELECT
A.ID
,B.emp_name AS create_user -- 制单人
,A.create_date AS create_date -- 制单日期
,A.table1 AS table1 -- 主表1
,A.table2 AS table2 -- 主表2
,A.table3 AS table3 -- 主表3
,A.table4 AS table4 -- 主表4
,A.table5 AS table5 -- 主表5
,A.table6 AS table6 -- 主表6
FROM
(
SELECT
distinct
COST_id AS ID
,create_user -- 制单人
,create_date -- 制单日期
,table1 -- 主表1
,table2 -- 主表2
,table3 -- 主表3
,table4 -- 主表4
,table5 -- 主表5
,table6 -- 主表6
FROM dm.input_cost_sys_data_list
WHERE 1=1
${if(len(sTb1)=0,"","AND table1 IN ('"+sTb1+"') ")}
${if(len(sTb2)=0,"","AND table2 IN ('"+sTb2+"') ")}
${if(len(sTb3)=0,"","AND table3 IN ('"+sTb3+"') ")}
)A
LEFT JOIN
(
SELECT
emp_code -- 员工编号
,emp_name -- 员工名称
from dwr.dim_employee
)B
ON A.create_user = B.emp_code
ORDER BY A.create_date desc,A.table1,A.table2,A.table3
;
INSERT INTO dm.input_cost_sys_data_list ( id
,cost_id
,create_user
,create_date
,table1
,table2
,table3
,table4
,table5
,table6
,list_date
,dept_code
,dept_name
,class_code
,class_name
,code
,num
,amt
,price
,remark1
,remark2
,remark3
,remark4
,remark5
,remark6
,remark7
,update_date
,update_by)
SELECT
s.id
,s.cost_id
,s.create_user
,s.create_date
,s.table1
,s.table2
,s.table3
,s.table4
,s.table5
,s.table6
,s.list_date
,s.dept_code
,s.dept_name
,s.class_code
,s.class_name
,s.code
,s.num
,s.amt
,s.price
,s.remark1
,s.remark2
,s.remark3
,s.remark4
,s.remark5
,s.remark6
,s.remark7
,s.update_date
,s.update_by
FROM dm.input_cost_sys_data_list_1 s
LEFT JOIN dm.input_cost_sys_data_list t
ON s.id = t.id
WHERE t.id IS NULL;