From 0f655372a618539baa95a5b366ebeb4d11ab2f20 Mon Sep 17 00:00:00 2001 From: chen.llin <1442464845@qq.comm> Date: 星期三, 24 十二月 2025 08:26:51 +0800 Subject: [PATCH] 月结功能对应-sql --- src/main/java/monthly_settle.sql | 90 +++++++++++++++++++++++++++++++++++++++++++++ 1 files changed, 90 insertions(+), 0 deletions(-) diff --git a/src/main/java/monthly_settle.sql b/src/main/java/monthly_settle.sql new file mode 100644 index 0000000..f9372ff --- /dev/null +++ b/src/main/java/monthly_settle.sql @@ -0,0 +1,90 @@ +CREATE TABLE [man_monthly_settle] ( + [id] bigint NOT NULL IDENTITY(1,1), + [settle_no] varchar(255) NOT NULL, + [start_date] datetime NOT NULL, + [end_date] datetime NOT NULL, + [status] int NOT NULL DEFAULT 0, + [total_in_qty] decimal(24,9) DEFAULT 0.00, + [total_out_qty] decimal(24,9) DEFAULT 0.00, + [total_materials] int DEFAULT 0, + [memo] varchar(255) DEFAULT NULL, + [create_by] bigint DEFAULT NULL, + [create_time] datetime DEFAULT NULL, + [update_by] bigint DEFAULT NULL, + [update_time] datetime DEFAULT NULL, + [is_deleted] int NOT NULL DEFAULT 0, + PRIMARY KEY ([id]), + CONSTRAINT [uk_settle_no] UNIQUE ([settle_no]) +); +CREATE INDEX [idx_start_date] ON [man_monthly_settle] ([start_date]); +CREATE INDEX [idx_end_date] ON [man_monthly_settle] ([end_date]); +CREATE INDEX [idx_status] ON [man_monthly_settle] ([status]); + +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鏈堢粨涓昏〃', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'ID', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'id'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鏈堢粨缂栧彿', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'settle_no'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'璧峰鏃ユ湡', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'start_date'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'缁撴潫鏃ユ湡', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'end_date'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鐘舵�� 0:寰呮湀缁� 1:宸叉湀缁�', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'status'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鎬诲叆搴撴暟閲�', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'total_in_qty'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鎬诲嚭搴撴暟閲�', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'total_out_qty'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鐗╂枡绉嶇被鏁�', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'total_materials'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'澶囨敞', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'memo'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鍒涘缓浜哄憳', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'create_by'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鍒涘缓鏃堕棿', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'create_time'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'淇敼浜哄憳', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'update_by'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'淇敼鏃堕棿', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'update_time'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鍒犻櫎鏍囪 0:鏈垹闄� 1:宸插垹闄�', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'is_deleted'; + +CREATE TABLE [man_monthly_settle_detail] ( + [id] bigint NOT NULL IDENTITY(1,1), + [settle_id] bigint NOT NULL, + [settle_no] varchar(255) NOT NULL, + [matnr] varchar(255) NOT NULL, + [batch] varchar(255) DEFAULT NULL, + [maktx] varchar(255) DEFAULT NULL, + [brand] varchar(255) DEFAULT NULL, + [beginning_qty] decimal(24,9) NOT NULL, + [in_qty] decimal(24,9) NOT NULL, + [out_qty] decimal(24,9) NOT NULL, + [ending_qty] decimal(24,9) NOT NULL, + [stock_qty] decimal(24,9) NOT NULL, + [diff_qty] decimal(24,9) NOT NULL, + [create_time] datetime DEFAULT NULL, + [is_deleted] int NOT NULL DEFAULT 0, + PRIMARY KEY ([id]) +); +CREATE INDEX [idx_settle_id] ON [man_monthly_settle_detail] ([settle_id]); +CREATE INDEX [idx_settle_no] ON [man_monthly_settle_detail] ([settle_no]); +CREATE INDEX [idx_matnr] ON [man_monthly_settle_detail] ([matnr]); + +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鏈堢粨鏄庣粏琛�', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'ID', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'id'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鏈堢粨涓昏〃ID', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'settle_id'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鏈堢粨缂栧彿', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'settle_no'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鐗╂枡缂栫爜', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'matnr'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鎵规锛堣鍗曟槑缁嗘壒娆★級', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'batch'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鐗╂枡鍚嶇О锛堟湀缁撴椂浠庤鍗曟槑缁嗚〃鑾峰彇锛�', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'maktx'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鍝佺墝锛堟湀缁撴椂浠庤鍗曟槑缁嗚〃鑾峰彇锛�', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'brand'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鏈熷垵搴撳瓨锛堜笂鏈熺粨浣欙級', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'beginning_qty'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鏈湡鍏ュ簱鏁伴噺', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'in_qty'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鏈湡鍑哄簱鏁伴噺', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'out_qty'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鏈熸湯搴撳瓨锛堟湡鍒�+鍏ュ簱-鍑哄簱锛�', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'ending_qty'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'褰撳墠瀹為檯搴撳瓨鏁伴噺', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'stock_qty'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'宸紓鏁伴噺锛堝疄闄呭簱瀛�-鏈熸湯搴撳瓨锛�', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'diff_qty'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鍒涘缓鏃堕棿', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'create_time'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鍒犻櫎鏍囪 0:鏈垹闄� 1:宸插垹闄�', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'is_deleted'; + +ALTER TABLE [man_order_log_pakin] ADD [monthly_settle_id] bigint DEFAULT NULL; +ALTER TABLE [man_order_log_pakin] ADD [monthly_settle_no] varchar(255) DEFAULT NULL; +CREATE INDEX [idx_monthly_settle_id] ON [man_order_log_pakin] ([monthly_settle_id]); + +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鏈堢粨ID', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_order_log_pakin', @level2type = N'COLUMN', @level2name = N'monthly_settle_id'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鏈堢粨缂栧彿', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_order_log_pakin', @level2type = N'COLUMN', @level2name = N'monthly_settle_no'; + +ALTER TABLE [man_order_log_pakout] ADD [monthly_settle_id] bigint DEFAULT NULL; +ALTER TABLE [man_order_log_pakout] ADD [monthly_settle_no] varchar(255) DEFAULT NULL; +CREATE INDEX [idx_monthly_settle_id] ON [man_order_log_pakout] ([monthly_settle_id]); + +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鏈堢粨ID', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_order_log_pakout', @level2type = N'COLUMN', @level2name = N'monthly_settle_id'; +EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'鏈堢粨缂栧彿', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_order_log_pakout', @level2type = N'COLUMN', @level2name = N'monthly_settle_no'; -- Gitblit v1.9.1