-- 创建销售记录表
|
CREATE TABLE sales_record
|
(
|
id BIGSERIAL PRIMARY KEY, -- 主键ID
|
sale_date DATE NOT NULL, -- 销售日期
|
customer_id BIGINT, -- 客户id
|
customer VARCHAR(255) NOT NULL, -- 客户
|
coal_id VARCHAR(255) NOT NULL, -- 煤种 id
|
coal VARCHAR(255) NOT NULL, -- 煤种
|
price_including_tax DECIMAL(10, 2) NOT NULL, -- 单价（含税）
|
inventory_quantity DECIMAL(10, 0) NOT NULL, -- 库存数量
|
unit VARCHAR(100) NOT NULL, -- 单位
|
sale_quantity DECIMAL(10, 2) NOT NULL, -- 销售数量
|
sale_price DECIMAL(10, 2) NOT NULL, -- 销售单价 (含税)
|
total_amount DECIMAL(10, 2) NOT NULL, -- 销售总价 (含税)
|
freight DECIMAL(10, 2) NOT NULL, -- 运费
|
tax_coal VARCHAR(36), -- 购销煤税率13%
|
tax_trans VARCHAR(36), -- 运输税率9%
|
gross_profit DECIMAL(10, 2), -- 毛利润
|
net_profit DECIMAL(10, 2), -- 净利润
|
registrant_id BIGINT, -- 登记人id
|
registrant VARCHAR(255) NOT NULL, -- 登记人
|
registration_date DATE NOT NULL, -- 登记日期
|
|
create_time TIMESTAMP WITHOUT TIME ZONE, -- 上传时间，默认当前时间
|
update_time TIMESTAMP WITHOUT TIME ZONE, -- 最后更新时间，默认当前时间
|
create_by VARCHAR(255), -- 上传人用户名
|
update_by VARCHAR(255), -- 最后修改人用户名
|
deleted INT NOT NULL DEFAULT 0 -- 软删除标志：0=未删除，1=已删除
|
);
|
|
-- 表注释
|
COMMENT ON TABLE sales_record IS '销售记录表';
|
|
-- 添加字段注释
|
COMMENT ON COLUMN sales_record.id IS '主键ID';
|
COMMENT ON COLUMN sales_record.sale_date IS '销售日期';
|
COMMENT ON COLUMN sales_record.customer IS '客户';
|
COMMENT ON COLUMN sales_record.customer_id IS '客户id';
|
COMMENT ON COLUMN sales_record.coal IS '煤种';
|
COMMENT ON COLUMN sales_record.coal_id IS '煤种id';
|
COMMENT ON COLUMN sales_record.unit IS '单位';
|
COMMENT ON COLUMN sales_record.price_including_tax IS '单价（含税）';
|
COMMENT ON COLUMN sales_record.inventory_quantity IS '库存数量';
|
COMMENT ON COLUMN sales_record.sale_quantity IS '销售数量';
|
COMMENT ON COLUMN sales_record.sale_price IS '销售单价 (含税)';
|
COMMENT ON COLUMN sales_record.total_amount IS '销售总价 (含税)';
|
COMMENT ON COLUMN sales_record.tax_coal IS '购销煤税率13%';
|
COMMENT ON COLUMN sales_record.tax_trans IS '运输税率9%';
|
COMMENT ON COLUMN sales_record.gross_profit IS '毛利润';
|
COMMENT ON COLUMN sales_record.net_profit IS '净利润';
|
COMMENT ON COLUMN sales_record.registrant IS '登记人';
|
COMMENT ON COLUMN sales_record.registrant_id IS '登记人id';
|
COMMENT ON COLUMN sales_record.registration_date IS '登记日期';
|
|
COMMENT ON COLUMN sales_record.deleted IS '软删除标志，0=未删除，1=已删除';
|
COMMENT ON COLUMN sales_record.create_by IS '创建该记录的用户';
|
COMMENT ON COLUMN sales_record.create_time IS '记录创建时间';
|
COMMENT ON COLUMN sales_record.update_by IS '最后修改该记录的用户';
|
COMMENT ON COLUMN sales_record.update_time IS '记录最后更新时间';
|
