--前缀
--bas : 基础功能模块
--mts : 产码功能模块
--mes : 生产订单模块

--基础信息模块
--产品表：bas_product
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[bas_product]') AND type in (N'U')) DROP TABLE [bas_product];
CREATE TABLE [bas_product] (
	[id] int IDENTITY NOT NULL,
	[erpProdCode] VARCHAR(30) NOT NULL,                  --ERP产品代码
	[subTypeNo] varchar(20) default '' NOT NULL,         --产品大类编码
	[subTypeName] nvarchar(20) default '' NOT NULL,      --产品大类名称
	[prodCode] VARCHAR(30) NOT NULL,                     --产品代码
	[prodName] nvarchar(50) NOT NULL,                    --产品名称
	[packageSize] nvarchar(50) NOT NULL,                 --包装规格
	[packUnit] nvarchar(10)  NULL,                       --包装单位
	[produceNum] Decimal(18,3) NULL,                     --一锅的产量（配方）
	[ichangerate] Decimal(18,3) NULL,                    --产出率
	[operateTime] Decimal(18,2) NULL,                    --一锅炒制时间
	[pkgRatio] varchar(20)  NULL,                        --包装比例/新雅轩存入换算率ichangerate
	[boxLevel] tinyint default 0 NOT NULL,               --大箱层级
	[boxAmount] tinyint default 0 NOT NULL,              --整箱数量
	[formulaID] int NULL,                                --配方编码
	[validDate] int default 0 NOT NULL,                  --保质期/天
	[validunit] nvarchar(5) NULL,                        --保质期单位
	[memo] nvarchar(250) NOT NULL,                       --产品备注
	[approveBy] int default 0 NOT NULL,                  --修改--批准人
	[approveTime] dateTime NULL,                         --修改--批准时间
	[approveMark] nvarchar(250) NULL,                    --新增--批准备注
	[estate] tinyint default 1 NOT NULL,                 --状态 1正常/0禁用
	[uid] int default 0 NOT NULL,
	[createTime] datetime DEFAULT getdate() NOT NULL,
	[updateTime] datetime DEFAULT getdate() NOT NULL,
	PRIMARY KEY NONCLUSTERED ([prodCode])
);

--码资源表：bas_rescode
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[bas_rescode]') AND type in (N'U')) DROP TABLE [bas_rescode];
CREATE TABLE [bas_rescode] (
	[id] int IDENTITY NOT NULL,
	[prodCode] VARCHAR(30) NOT NULL,                     --产品代码
	[resCode] varchar(20) NOT NULL,                      --码资源药品就是前七位
	[codeLevel] tinyint default 0 NOT NULL,              --包装层级
	[amount] int default 0 NOT NULL,                     --对应最小包装数量
	[estate] tinyint default 1 NOT NULL,                 --状态 1正常/0禁用
	[uid] int default 0 NOT NULL,
	[createTime] datetime DEFAULT getdate() NOT NULL,
	[updateTime] datetime DEFAULT getdate() NOT NULL,
	PRIMARY KEY NONCLUSTERED ([resCode])
);

--物料表：bas_material
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[bas_material]') AND type in (N'U')) DROP TABLE [bas_material];
CREATE TABLE [bas_material] (
	[id] int IDENTITY NOT NULL,
	[materialCode] varchar(30) NOT NULL,                  --物料代码
	[materialName] nvarchar(30) NOT NULL,                 --物料名称
	[materialSize] nvarchar(50) NOT NULL,                 --物料规格
	[amount] decimal(18,3) NOT NULL,                      --物料数量
	[unit] nvarchar(10) NOT NULL,                         --物料单位
	[mValidDate] int default 0 NOT NULL,                  --保质期/天
	[mValidunit] nvarchar(5) NULL,                        --保质期单位
	[memo] nvarchar(250) NULL,                        --产品备注
	[estate] tinyint default 1 NOT NULL,                  --状态 1正常/0禁用
	[uid] int default 0 NOT NULL,
	[createTime] datetime DEFAULT getdate() NOT NULL,
	[updateTime] datetime DEFAULT getdate() NOT NULL,
	PRIMARY KEY NONCLUSTERED ([materialCode])
);

--配方表：bas_formula
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[bas_formula]') AND type in (N'U')) DROP TABLE [bas_formula];
CREATE TABLE [bas_formula] (
	[id] int IDENTITY NOT NULL,
	[prodCode] varchar(30) NOT NULL,                     --产品代码
	[formulaCode] varchar(20) NULL,                      --配方编码,预留字段
	[estate] tinyint default 1 NOT NULL,                 --状态 1正常/0禁用
	[uid] int default 0 NOT NULL,
	[createTime] datetime DEFAULT getdate() NOT NULL,
	[updateTime] datetime DEFAULT getdate() NOT NULL,
);

--配方详情表：bas_formula_detail
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[bas_formula_detail]') AND type in (N'U')) DROP TABLE [bas_formula_detail];
CREATE TABLE [bas_formula_detail] (
	[id] int IDENTITY NOT NULL,
	[prodCode] varchar(30) NOT NULL,                     --产品代码
	[formulaID] varchar(30) NOT NULL,                    --配方ID
	[materialCode] varchar(30) NOT NULL,                 --物料代码
	[unit] nvarchar(10) NOT NULL,                        --单位
	[amount] decimal(18,3)  NULL,                        --数量
	[memo] nvarchar(250) default '' NULL,                --产品备注
	[uid] int default 0 NOT NULL,
	[createTime] datetime DEFAULT getdate() NOT NULL,
	[updateTime] datetime DEFAULT getdate() NOT NULL,
);

--生产线表：bas_line
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[bas_line]') AND type in (N'U')) DROP TABLE [bas_line];
CREATE TABLE [bas_line] (
	[id] int IDENTITY NOT NULL,
	[wshopID] varchar(10) default 'WH01' NOT NULL,      --车间，默认字段，方便后期扩充
	[lineID] varchar(20) NOT NULL,                      --生产线ID ， L001
	[lineName] nvarchar(20) NOT NULL,                    --生产线名称
	[memo] nvarchar(50) NULL,
	[estate] tinyint default 1 NOT NULL,                 --状态 1正常/0禁用
	[uid] int default 0 NOT NULL,
	[createTime] datetime DEFAULT getdate() NOT NULL,
	[updateTime] datetime DEFAULT getdate() NOT NULL,
	PRIMARY KEY NONCLUSTERED ([lineID])
);

--工位表：bas_station
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[bas_station]') AND type in (N'U')) DROP TABLE [bas_station];
CREATE TABLE [bas_station] (
	[id] int IDENTITY NOT NULL,
	[lineID] varchar(20) NOT NULL,
	[stationID] varchar(20) NOT NULL,
	[stationName] nvarchar(20) NOT NULL,
	[stationIP] varchar(20) NOT NULL,
	[memo] nvarchar(50) NULL,
	[estate] tinyint default 1 NOT NULL,                 --状态 1正常/0禁用
	[uid] int default 0 NOT NULL,
	[createTime] datetime DEFAULT getdate() NOT NULL,
	[updateTime] datetime DEFAULT getdate() NOT NULL,
	PRIMARY KEY NONCLUSTERED ([stationID])
);

--经销商/供应商表：bas_customer
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[bas_customer]') AND type in (N'U')) DROP TABLE [bas_customer];
CREATE TABLE [bas_customer] (
	[id] int IDENTITY NOT NULL,
	[cusID] varchar(50) NOT NULL,
	[address] nvarchar(200) NOT NULL,
	[contact] varchar(200) NOT NULL,
	[cusName] nvarchar(20) NOT NULL,
	[cuslevel] varchar(20) NOT NULL,
	[cuslevName] nvarchar(20) NOT NULL,
	[cusConfig] nvarchar(100) NULL,
	[memo] nvarchar(50) NULL,
	[estate] tinyint default 1 NOT NULL,                --状态 1正常/0禁用
	[uid] int default 0 NOT NULL,
	[createTime] datetime DEFAULT getdate() NOT NULL,
	[updateTime] datetime DEFAULT getdate() NOT NULL,
	PRIMARY KEY NONCLUSTERED ([cusID])
);
