/*
drop table System_Member;
drop table System_Admin;
drop table System_Auth;
drop table System_Role;
drop table System_Member_Auth;
drop table System_Role_Auth;
drop table System_Member_Role;
drop table System_ModuleFormat;
drop table System_Module;
drop table Base_Department;
drop table Base_Warehouse;
drop table Base_Employee;
drop table Base_InventoryClass;
drop table Base_CustomerClass;
drop table Base_Customer;

*/
--系统版本表
if object_id('System_Version') is null
begin
	CREATE TABLE System_Version(
		version_no varchar(20),--版本号
		version_name nvarchar(50) --版本名称
	);
end
/**/GO

--系统配置表
if object_id('System_Config') is null
begin
	CREATE TABLE System_Config(
		cf_id int IDENTITY(1,1) NOT NULL,
		cf_code nvarchar(50) PRIMARY KEY, --参数编码
		cf_name nvarchar(50), --参数名称
		cf_value nvarchar(50), --参数值
		category nvarchar(50), --参数名称
		cf_type nvarchar(50),--控件类型
		value_list nvarchar(50),--控件类型
		hidden int,--是否隐藏参数
		cf_intro nvarchar(500) --参数说明
	);
end
/**/GO

--用户表
if object_id('System_Member') is null
CREATE TABLE System_Member(
	[uid] [int] IDENTITY(1,1) PRIMARY KEY,
	[username] [nvarchar](50) UNIQUE,
	[nickname] [nvarchar](50),
	[password] [nvarchar](64),
	[status] [nvarchar](10)
);
/**/GO

--管理员表
if object_id('System_Admin') is null
begin
	CREATE TABLE System_Admin(
		[adm_id] [int] IDENTITY(1,1),
		[uid] int
	);
	--与用户外键约束
	ALTER TABLE [dbo].[System_Admin]  WITH CHECK ADD  CONSTRAINT [FK_System_Admin_System_Member] FOREIGN KEY([uid])
	REFERENCES [dbo].[System_Member] ([uid]);
end
/**/GO

--初始化管理员默认账号
if (select count(1) from System_Member where username='manager')=0
begin
	insert System_Member(username,nickname,password,status)
	select 'manager' as username,'系统管理员' as nickname,
	'1A8565A9DC72048BA03B4156BE3E569F22771F23' as password,1 as status;
	insert System_Admin(uid) select uid from System_Member where username='manager';
end
/**/GO

--权限表
if object_id('System_Auth') is null
CREATE TABLE System_Auth(
	[auth_code] nvarchar(50) PRIMARY KEY,
	[auth_name] nvarchar(50) NOT NULL
);
/**/GO

--角色表
if object_id('System_Role') is null
CREATE TABLE System_Role(
  [id] [int] IDENTITY(1,1) PRIMARY KEY,
	[role_name] [nvarchar](50) UNIQUE NOT NULL,
	[status] [nvarchar](10)
);
/**/GO

--用户权限表
if object_id('System_Member_Auth') is null
begin
	CREATE TABLE System_Member_Auth(
		[id] [int] IDENTITY(1,1) PRIMARY KEY,
		[uid] int NOT NULL,
		[auth_code] [nvarchar](50) NOT NULL
	);
	--与用户外键约束
	ALTER TABLE [dbo].[System_Member_Auth]  WITH CHECK ADD  CONSTRAINT [FK_System_Member_Auth_System_Member] FOREIGN KEY([uid])
	REFERENCES [dbo].[System_Member] ([uid]);
	--与权限表外键约束
	ALTER TABLE [dbo].[System_Member_Auth]  WITH CHECK ADD  CONSTRAINT [FK_System_Member_Auth_System_Auth] FOREIGN KEY([auth_code])
	REFERENCES [dbo].[System_Auth] ([auth_code]);
end
/**/GO

--角色权限表
if object_id('System_Role_Auth') is null
begin
	CREATE TABLE System_Role_Auth(
		[id] [int] IDENTITY(1,1) PRIMARY KEY,
		[role_id] int NOT NULL,
		[auth_code] [nvarchar](50) UNIQUE NOT NULL
	);
	--与角色外键约束
	ALTER TABLE [dbo].[System_Role_Auth]  WITH CHECK ADD  CONSTRAINT [FK_System_Role_Auth_System_Role] FOREIGN KEY([role_id])
	REFERENCES [dbo].[System_Role] ([id]);
	--与权限表外键约束
	ALTER TABLE [dbo].[System_Role_Auth]  WITH CHECK ADD  CONSTRAINT [FK_System_Role_Auth_System_Auth] FOREIGN KEY([auth_code])
	REFERENCES [dbo].[System_Auth] ([auth_code]);
end
/**/GO

--用户对应角色表
if object_id('System_Member_Role') is null
begin
	CREATE TABLE System_Member_Role(
		[id] [int] IDENTITY(1,1) PRIMARY KEY,
		[user_id] int NOT NULL,
		[role_id] int NOT NULL
	);
	--与角色外键约束
	ALTER TABLE [dbo].[System_Member_Role]  WITH CHECK ADD  CONSTRAINT [FK_System_Member_Role_System_Role] FOREIGN KEY([role_id])
	REFERENCES [dbo].[System_Role] ([id]);
	--与用户外键约束
	ALTER TABLE [dbo].[System_Member_Role]  WITH CHECK ADD  CONSTRAINT [FK_System_Member_Role_System_Member] FOREIGN KEY([user_id])
	REFERENCES [dbo].[System_Member] ([uid]);
end
/**/GO

--在线用户表
if object_id('System_OnlineUsers') is null
begin
	CREATE TABLE System_OnlineUsers(
		ou_id int IDENTITY(1,1) NOT NULL,--主键自增ID
		u_id int,--用户ID
		login_date datetime,--登录日期
		ip nvarchar(32)--IP地址
	);
end
/**/GO

--模块格式表by yr 2013-05-17
if object_id('System_ModuleFormat') is null
create table System_ModuleFormat(
  id int identity(1,1) PRIMARY KEY,
  module nvarchar(50),
  field nvarchar(50),
  field_name nvarchar(50),
  title nvarchar(50),
  width int,
  sort int,
  show int,
  hidden int,
  auth_view nvarchar(50),
  auth_edit nvarchar(50),
  sum_field int,
  type nvarchar(50),--控件类型
  can_select int,--是否可以选择
  color nvarchar(30),--颜色
  can_edit int,--是否可编辑
  default_value nvarchar(100)--默认值
)
/**/GO

--模块表
if object_id('System_Module') is null
CREATE TABLE [dbo].[System_Module](
	[id] [int] IDENTITY(1,1) PRIMARY KEY,
	[category] [nvarchar](30),
	[module_code] [nvarchar](50),
	[module_name] [nvarchar](30),
	[sort] [int] NULL,
	[auth_code] [nvarchar](50),
	[node_img] [nvarchar](50),
	[url] [nvarchar](255),
	[flow_url] [nvarchar](255)--流程图地址
);
/**/GO

--自定义报表
if object_id('System_XReport') is null
begin
	CREATE TABLE System_XReport(
		xr_id int IDENTITY(1,1) NOT NULL,--主键ID
		module_code nvarchar(50) PRIMARY KEY, --模块编码
		module_name nvarchar(30), --报表名称
		column_names nvarchar(1000), --列标题
		keywords nvarchar(1000), --查询关键字
		date1 nvarchar(100), --日期控件1
		date2 nvarchar(100), --日期控件2
		textbox1 nvarchar(100), --文本控件1
		textbox2 nvarchar(100), --文本控件2
		order_field nvarchar(100), --排序字段
		filters nvarchar(500),--过滤条件
		html nvarchar(max),--HTML脚本
		temp_sql nvarchar(max),--临时表SQL
		report_sql nvarchar(max)--报表SQL
	);
end
/**/GO
--自定义报表_过滤条件
if object_id('System_XReportFilter') is null
begin
	CREATE TABLE System_XReportFilter(
		xf_id int IDENTITY(1,1) NOT NULL,
		xf_code nvarchar(50) PRIMARY KEY, --过滤条件编码
		xf_name nvarchar(50), --过滤条件名称
		xf_sql nvarchar(max)--过滤条件脚本
	);
end
/**/GO

--自定义项档案表
if object_id('Base_DefineItem') is null
begin
	CREATE TABLE Base_DefineItem(
		di_id int IDENTITY(1,1) NOT NULL,--主键自增ID
		di_code nvarchar(50),--所属自定义项
		di_value nvarchar(150)--数据
	);
end
/**/GO

--模块脚本表
if object_id('System_ModuleScript') is null
begin
	CREATE TABLE System_ModuleScript(
		ms_id int IDENTITY(1,1) NOT NULL,--主键自增ID
		ms_code nvarchar(50),--脚本编码
		ms_name nvarchar(50),--脚本名称
		ms_type nvarchar(50),--脚本类型
		remark nvarchar(1000),--备注
		script ntext--脚本
	);
end
/**/GO

--部门表
if object_id('Base_Department') is null
CREATE TABLE Base_Department(
	dep_id int IDENTITY(1,1) PRIMARY KEY, --部门id
	dep_code nvarchar(50) UNIQUE, --部门编码
	dep_name nvarchar(50), --部门名称
	parent_id int, --上级部门id
	layer int,--层次
	is_workshop int,--是否生产车间
	is_stop int,--是否停用
	sdefine1 nvarchar(300),--字符自定义项1
	sdefine2 nvarchar(300),--字符自定义项2
	sdefine3 nvarchar(300),--字符自定义项3
	sdefine4 nvarchar(300),--字符自定义项4
	sdefine5 nvarchar(300),--字符自定义项5
	ndefine1 float,--数值自定义项1
	ndefine2 float,--数值自定义项2
	ndefine3 float,--数值自定义项3
	ndefine4 float,--数值自定义项4
	ndefine5 float --数值自定义项5
)
/**/GO

--部门增加仓库字段
if not exists(select * from syscolumns where id=object_id('Base_Department') and name='dep_wh_id')
begin
	alter table Base_Department add
		dep_wh_id int --仓库ID
end
/**/GO

--月结表
if object_id('System_CloseMonth') is null
CREATE TABLE System_CloseMonth(
	cm_id int IDENTITY(1,1) PRIMARY KEY,
	cm_year int NOT NULL,
	cm_month int NOT NULL
);
/**/GO

--仓库表
if object_id('Base_Warehouse') is null
CREATE TABLE Base_Warehouse(
	[wh_id] int IDENTITY(1,1) PRIMARY KEY,
	[wh_code] nvarchar(50) UNIQUE NOT NULL,
	[wh_name] nvarchar(50) NOT NULL,
	[is_stop] int,--是否停用
	[is_control_stock] int --禁止负库存
);
/**/GO
 
--操作日志表 
if object_id('System_UserLog') is null
CREATE TABLE System_UserLog(
	[log_id] int IDENTITY(1,1) PRIMARY KEY,
	[module] nvarchar(30),--模块
	[op_type] nvarchar(30),--操作类型
	[order_id] int,--单据id
	[order_number] nvarchar(50),--单据号
	[uid] int,--用户id
	[nickname] nvarchar(50),--用户姓名
	[login_date] datetime,--登录日期
	[system_date] datetime,--系统日期
	[ip_addr] nvarchar(64)--ip地址
);
/**/GO

--计价方式表
if object_id('Base_ValuationMethod') is null
CREATE TABLE Base_ValuationMethod(
	[vm_id] int PRIMARY KEY,
	[vm_name] nvarchar(50) UNIQUE
);
/**/GO
--员工表
if object_id('Base_Employee') is null
begin
	CREATE TABLE Base_Employee(
		ele_id int IDENTITY(1,1) PRIMARY KEY,
		ele_code nvarchar(50) UNIQUE NOT NULL,--员工编码
		ele_name nvarchar(50) NOT NULL,--员工姓名
		dep_id int,--所属部门ID
		[uid] int NULL,--绑定登录用户
		entry_date datetime,--入职日期
		exit_date datetime,--离职日期
		position nvarchar(20),--职务
		birthday datetime,--出生日期
		sex int,--性别
		native_place nvarchar(20),--籍贯
		degree nvarchar(20),--学历
		id_card_no nvarchar(18),--身份证
		office_phone nvarchar(20),--办公电话
		cellphone nvarchar(20),--手机
		home_phone nvarchar(20),--家庭电话
		email nvarchar(50),--Email
		qq nvarchar(20),--QQ
		[address] nvarchar(100),--地址
		sdefine1 nvarchar(300),--字符自定义项1
		sdefine2 nvarchar(300),--字符自定义项2
		sdefine3 nvarchar(300),--字符自定义项3
		sdefine4 nvarchar(300),--字符自定义项4
		sdefine5 nvarchar(300),--字符自定义项5
		ndefine1 float,--数值自定义项1
		ndefine2 float,--数值自定义项2
		ndefine3 float,--数值自定义项3
		ndefine4 float,--数值自定义项4
		ndefine5 float --数值自定义项5  
	);
	--与部门外键约束
	ALTER TABLE [dbo].[Base_Employee]  WITH CHECK ADD  CONSTRAINT [FK_Base_Employee_Base_Department] FOREIGN KEY([dep_id])
	REFERENCES [dbo].[Base_Department] ([dep_id]);
end
/**/GO

--增加业务员折扣率字段
if not exists(select * from syscolumns where id=object_id('Base_Employee') and name='kl')
begin
	alter table Base_Employee add
		kl float--折扣率
end
/**/GO

--下级员工表
if object_id('Base_Employee_Child') is null
begin
	CREATE TABLE Base_Employee_Child(
		ec_id int IDENTITY(1,1) PRIMARY KEY,--自增主键
		parent_ele_id int,--上级员工ID
		child_ele_id int--下级员工ID
	);
	--与员工表外键约束
	ALTER TABLE Base_Employee_Child WITH CHECK ADD  CONSTRAINT FK_Base_Employee_Child_Base_Employee_P FOREIGN KEY(parent_ele_id)
	REFERENCES Base_Employee (ele_id);
	ALTER TABLE Base_Employee_Child WITH CHECK ADD  CONSTRAINT FK_Base_Employee_Child_Base_Employee_C FOREIGN KEY(child_ele_id)
	REFERENCES Base_Employee (ele_id);
end
/**/GO

--存货分类表
if object_id('Base_InventoryClass') is null
CREATE TABLE Base_InventoryClass(
	ic_id int IDENTITY(1,1) PRIMARY KEY, --分类id
	ic_code nvarchar(50) UNIQUE NOT NULL, --分类编码
	ic_name nvarchar(50) NOT NULL, --分类名称
	parent_id int, --上级分类id
	layer int--层次
);
/**/GO

--客户分类表
if object_id('Base_CustomerClass') is null
CREATE TABLE Base_CustomerClass(
	cc_id int IDENTITY(1,1) PRIMARY KEY, --分类id
	cc_code nvarchar(50) UNIQUE NOT NULL, --分类编码
	cc_name nvarchar(50) NOT NULL, --分类名称
	parent_id int, --上级分类id
	layer int--层次
);
/**/GO

--客户表
if object_id('Base_Customer') is null
begin
	CREATE TABLE Base_Customer(
		cus_id int identity(1,1) PRIMARY KEY,--客户ID
		cus_code nvarchar(50),--客户编码
		cc_id int not null,--客户分类id
		cus_name nvarchar(50) UNIQUE not null,--客户简称
		cus_full_name nvarchar(50),--客户全称
		is_stop int,--是否停用
		ele_id int,--专管业务员
		address nvarchar(50),--公司地址
		ship_address nvarchar(100),--发货地址
		contact nvarchar(50),--联系人
		legal_person nvarchar(50),--法人
		phone nvarchar(50),--电话
		cellphone nvarchar(50),--手机
		fax nvarchar(50),--传真
		email nvarchar(50),--电子邮件
		qq nvarchar(20),--QQ号
		bank_of_deposit nvarchar(50),--开户银行
		banking_account nvarchar(50),--银号账号
		sdefine1 nvarchar(300),--字符自定义项1
		sdefine2 nvarchar(300),--字符自定义项2
		sdefine3 nvarchar(300),--字符自定义项3
		sdefine4 nvarchar(300),--字符自定义项4
		sdefine5 nvarchar(300),--字符自定义项5
		ndefine1 float,--数值自定义项1
		ndefine2 float,--数值自定义项2
		ndefine3 float,--数值自定义项3
		ndefine4 float,--数值自定义项4
		ndefine5 float --数值自定义项5
	);
	--与客户分类外键约束
	ALTER TABLE [dbo].[Base_Customer]  WITH CHECK ADD  CONSTRAINT [FK_Base_Customer_Base_CustomerClass] FOREIGN KEY([cc_id])
	REFERENCES [dbo].[Base_CustomerClass] ([cc_id]);
end
/**/GO

--客户档案增加字段
if not exists(select * from syscolumns where id=object_id('Base_Customer') and name='bao_bei')
begin
	alter table Base_Customer add 
	bao_bei int --仅报备
end
/**/GO

--客户跟进记录表
--drop table Base_Customer_FollowUp
if object_id('Base_Customer_FollowUp') is null
begin
	CREATE TABLE Base_Customer_FollowUp(
		cfu_id int identity(1,1) PRIMARY KEY,--主键ID
		cus_id int,--客户ID
		fu_date datetime,--跟进日期
		fu_uid int,--跟进人ID
		fu_user nvarchar(50),--跟进人姓名
		remark nvarchar(1000)--跟进内容
	);
	--与客户外键约束
	ALTER TABLE [dbo].[Base_Customer_FollowUp]  WITH CHECK ADD  CONSTRAINT [FK_Base_Customer_FollowUp_Base_Customer] FOREIGN KEY([cus_id])
	REFERENCES [dbo].[Base_Customer] ([cus_id]);
	--与用户外键约束
	ALTER TABLE [dbo].[Base_Customer_FollowUp]  WITH CHECK ADD  CONSTRAINT [FK_Base_Customer_FollowUp_System_Member] FOREIGN KEY([fu_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
end
/**/GO

--供应商分类表
if object_id('Base_VendorClass') is null
CREATE TABLE Base_VendorClass(
	vc_id int IDENTITY(1,1) PRIMARY KEY, --分类id
	vc_code nvarchar(50) UNIQUE NOT NULL, --分类编码
	vc_name nvarchar(50) NOT NULL, --分类名称
	parent_id int, --上级分类id
	layer int--层次
);
/**/GO
--供应商表
if object_id('Base_Vendor') is null
begin
	CREATE TABLE Base_Vendor(
		vd_id int identity(1,1) PRIMARY KEY,--供应商ID
		vd_code nvarchar(50) UNIQUE NOT NULL,--供应商编码
		vc_id int not null,--供应商分类id
		vd_name nvarchar(50) UNIQUE not null,--供应商简称
		vd_full_name nvarchar(50),--供应商全称
		is_stop int,--是否停用
		ele_id int,--专管业务员
		address nvarchar(50),--公司地址
		arrive_address nvarchar(100),--到货地址
		contact nvarchar(50),--联系人
		legal_person nvarchar(50),--法人
		phone nvarchar(50),--电话
		cellphone nvarchar(50),--手机
		fax nvarchar(50),--传真
		email nvarchar(50),--电子邮件
		qq nvarchar(20),--QQ号
		bank_of_deposit nvarchar(50),--开户银行
		banking_account nvarchar(50),--银号账号
		sdefine1 nvarchar(300),--字符自定义项1
		sdefine2 nvarchar(300),--字符自定义项2
		sdefine3 nvarchar(300),--字符自定义项3
		sdefine4 nvarchar(300),--字符自定义项4
		sdefine5 nvarchar(300),--字符自定义项5
		ndefine1 float,--数值自定义项1
		ndefine2 float,--数值自定义项2
		ndefine3 float,--数值自定义项3
		ndefine4 float,--数值自定义项4
		ndefine5 float --数值自定义项5
	);
	--与供应商分类外键约束
	ALTER TABLE [dbo].[Base_Vendor]  WITH CHECK ADD  CONSTRAINT [FK_Base_Vendor_Base_VendorClass] FOREIGN KEY([vc_id])
	REFERENCES [dbo].[Base_VendorClass] ([vc_id]);
end
/**/GO

--币种表
if object_id('Base_Currency') is null
CREATE TABLE Base_Currency(
	crc_id int IDENTITY(1,1) PRIMARY KEY, --币种id
	crc_code nvarchar(50) UNIQUE NOT NULL, --币种编码
	crc_name nvarchar(50) UNIQUE, --币种名称
	is_standard int --是否本位币
);
/**/GO
--币种汇率表
if object_id('Base_CurrencyRate') is null
CREATE TABLE Base_CurrencyRate(
	rate_id int IDENTITY(1,1) PRIMARY KEY, --币种id
	crc_id int NOT NULL, --币种id
	crc_year int NOT NULL, --年份
	crc_month int NOT NULL, --月
	exchange_rate float --当前汇率
);
/**/GO
--销售类型表
if object_id('Base_SaleType') is null
CREATE TABLE Base_SaleType(
	st_id int IDENTITY(1,1) PRIMARY KEY, --id
	st_code nvarchar(50) UNIQUE NOT NULL, --编码
	st_name nvarchar(50), --名称
	is_default int --是否默认值
);
/**/GO
--采购类型表
if object_id('Base_PurchaseType') is null
CREATE TABLE Base_PurchaseType(
	pt_id int IDENTITY(1,1) PRIMARY KEY, --id
	pt_code nvarchar(50) UNIQUE NOT NULL, --编码
	pt_name nvarchar(50), --名称
	is_default int --是否默认值
);
/**/GO
--收发类型
if object_id('Base_IOType') is null
CREATE TABLE Base_IOType(
	iot_id int IDENTITY(1,1) PRIMARY KEY, --收发类型id
	iot_code nvarchar(50) UNIQUE NOT NULL, --编码
	iot_name nvarchar(50), --名称
	iot_flag int,--收发标志
	parent_id int, --上级id
	layer int--层次
)
/**/GO
--单据类型
if object_id('Base_OrderType') is null
CREATE TABLE Base_OrderType(
	ot_code nvarchar(50) PRIMARY KEY, --编码
	ot_name nvarchar(50) --名称
)
/**/GO

--销售类型表
if object_id('Base_ShipWay') is null
CREATE TABLE Base_ShipWay(
	sw_id int IDENTITY(1,1) PRIMARY KEY, --id
	sw_code nvarchar(50) UNIQUE NOT NULL, --编码
	sw_name nvarchar(50), --名称
	is_default int --是否默认值
);
/**/GO

--结算方式表
if object_id('Base_SettleStyle') is null
CREATE TABLE Base_SettleStyle(
	ss_id int IDENTITY(1,1) PRIMARY KEY, --id
	ss_code nvarchar(50), --编码
	ss_name nvarchar(50), --名称
	is_stop int,--是否停用
	is_default int --是否默认值
);
/**/GO

--结算账户表
if object_id('Base_Account') is null
CREATE TABLE Base_Account(
	ac_id int IDENTITY(1,1) PRIMARY KEY, --id
	ac_code nvarchar(50), --账户名称
	ac_name nvarchar(50), --账户名称
	bank_name nvarchar(50), --开户银行
	bank_no nvarchar(50), --银行账号
	balance float,--余额
	is_stop int--是否停用
);
/**/GO
--流水号规则表
if object_id('Base_RunningNumberRule') is null
CREATE TABLE Base_RunningNumberRule(
	module_code nvarchar(50),--模块编码
	rule1 nvarchar(50),--规则1
	rule1_value nvarchar(50),--规则1的值
	rule2 nvarchar(50),--规则2
	rule2_value nvarchar(50),--规则2的值
	rule3 nvarchar(50),--规则3
	rule3_value nvarchar(50),--规则3的值
	length int--流水号位数
);
/**/GO

--当前流水号表
if object_id('Base_RunningNumber') is null
CREATE TABLE Base_RunningNumber(
	rn_id int identity(1,1) PRIMARY KEY,--主键自增id
	module_code nvarchar(50),--模块编码
	seed nvarchar(50),--流水依据
	current_number int--当前流水
);
/**/GO

--流水号前缀表
if object_id('Base_RunningNumberPrefix') is null
CREATE TABLE Base_RunningNumberPrefix(
	rnp_id int identity(1,1) PRIMARY KEY,--主键自增id
	module_code nvarchar(50),--模块编码
	prefix_code nvarchar(50),--前缀编码(有时作为字段名称)
	prefix_name nvarchar(50),--前缀名称
	link_table nvarchar(50)--对应数据库表
);
/**/GO
/*
--费用表
if object_id('Base_Cost') is null
CREATE TABLE Base_Cost(
	cst_id int IDENTITY(1,1) PRIMARY KEY, --费用id
	cst_code nvarchar(50), --费用编码
	cst_name nvarchar(50), --费用名称
	parent_id int, --上级费用id
	is_stop int,--是否停用
	sdefine1 nvarchar(300),--字符自定义项1
	sdefine2 nvarchar(300),--字符自定义项2
	sdefine3 nvarchar(300),--字符自定义项3
	sdefine4 nvarchar(300),--字符自定义项4
	sdefine5 nvarchar(300),--字符自定义项5
	ndefine1 float,--数值自定义项1
	ndefine2 float,--数值自定义项2
	ndefine3 float,--数值自定义项3
	ndefine4 float,--数值自定义项4
	ndefine5 float --数值自定义项5
)
*/
/**/GO

--打印模板表
if object_id('System_PrintTemplate') is null
CREATE TABLE System_PrintTemplate(
	prt_id int identity(1,1) PRIMARY KEY,--主键自增id
	module nvarchar(50),--模块编码
	template_name nvarchar(50),--模板名称
	is_system int,--是否系统自带模板
	grf_file nvarchar(250),--模板文件
	sql_where nvarchar(1000),--SQL报表条件
	is_delete int--是否已假删除
);
/**/GO

--存货表
if object_id('Base_Inventory') is null
begin
	CREATE TABLE Base_Inventory(
		i_id int IDENTITY(1,1) PRIMARY KEY, --存货id
		i_code nvarchar(50) UNIQUE NOT NULL,--存货编码
		i_name nvarchar(50) NOT NULL,--存货名称
		ic_id int NOT NULL,--存货分类id
		standard nvarchar(50),--规格型号
		munit nvarchar(50),--主计量单位 
		aunit nvarchar(50),--辅计量单位
		convert_rate float,--换算率
		valuation nvarchar(50), --计价方式(1,全月平均法 2,移动平均法)
		add_date datetime,--新增日期
		update_date datetime,--修改日期
		stop_date datetime,--停用日期
		updater_id int,--修改人id
		on_free1 int,--启用自由项1
		on_free2 int,--启用自由项2    
		on_batch int,--启用批次
		is_sale int,--是否销售
		is_purchase int,--是否外购
		is_self int,--是否自制
		is_outsource int,--是否委外
		is_service int,--是否劳务费
		safety_stock float,--安全库存
		min_stock float,--最低库存
		max_stock float,--最高库存
		purchase_batch float,--采购批量
		new_tax_unit_price float,--最新采购价(原币含税)
		new_nat_unit_price float,--最新成本(本币无税)
		vd_id int,--默认供应商id
		wh_id int,--默认仓库id
		sdefine1 nvarchar(300),--字符自定义项1
		sdefine2 nvarchar(300),--字符自定义项2
		sdefine3 nvarchar(300),--字符自定义项3
		sdefine4 nvarchar(300),--字符自定义项4
		sdefine5 nvarchar(300),--字符自定义项5
		ndefine1 float,--数值自定义项1
		ndefine2 float,--数值自定义项2
		ndefine3 float,--数值自定义项3
		ndefine4 float,--数值自定义项4
		ndefine5 float --数值自定义项5
	);
	--与存货分类外键约束
	ALTER TABLE [dbo].[Base_Inventory]  WITH CHECK ADD  CONSTRAINT [FK_Base_Inventory_Base_InventoryClass] FOREIGN KEY([ic_id])
	REFERENCES [dbo].[Base_InventoryClass] ([ic_id]);
end
/**/GO

--存货图片表
if object_id('Base_InventoryPhoto') is null
begin
	CREATE TABLE Base_InventoryPhoto(
		ip_id int IDENTITY(1,1) PRIMARY KEY, --主键id
		i_id int NOT NULL, --存货id
		pic_url nvarchar(500),
		small_pic_url nvarchar(500)
	);
	--与存货分类外键约束
	ALTER TABLE [dbo].[Base_InventoryPhoto]  WITH CHECK ADD  CONSTRAINT [FK_Base_InventoryPhoto_Base_Inventory] FOREIGN KEY([i_id])
	REFERENCES [dbo].[Base_Inventory] ([i_id]);
end
/**/GO

--销售订单主表
if object_id('Sale_SalesOrder') is null
begin
	CREATE TABLE Sale_SalesOrder(
		so_id int identity(1,1) PRIMARY KEY,--订单主表ID
		order_number nvarchar(50) UNIQUE,--单据号
		new_uid int,--新增人ID
		new_user nvarchar(50),--新增人名称
		new_date datetime,--新增日期
		update_uid int,--修改人ID
		update_user nvarchar(50),--修改人名称
		update_date datetime,--修改日期
		verify_uid int,--审核人ID
		verify_user nvarchar(50),--审核人名称
		cus_id int,--客户ID
		st_id int,--销售类型ID
		dep_id int,--销售部门ID
		ele_id int,--员工ID
		sw_id int,--发运方式ID
		ship_address nvarchar(100),--发货地址
		contact nvarchar(50),--联系人
		phone nvarchar(50),--电话
		crc_id int, --币种ID
		exchange_rate float, --汇率
		tax_rate float,--税率
		remark nvarchar(500),--备注
		sdefine1 nvarchar(300),--字符自定义项1
		sdefine2 nvarchar(300),--字符自定义项2
		sdefine3 nvarchar(300),--字符自定义项3
		sdefine4 nvarchar(300),--字符自定义项4
		sdefine5 nvarchar(300),--字符自定义项5
		ndefine1 float,--数值自定义项1
		ndefine2 float,--数值自定义项2
		ndefine3 float,--数值自定义项3
		ndefine4 float,--数值自定义项4
		ndefine5 float --数值自定义项5
	);
	--与客户外键约束
	ALTER TABLE [dbo].[Sale_SalesOrder]  WITH CHECK ADD  CONSTRAINT [FK_Sale_SalesOrder_Base_Customer] FOREIGN KEY([cus_id])
	REFERENCES [dbo].[Base_Customer] ([cus_id]);
	--与部门外键约束
	ALTER TABLE [dbo].[Sale_SalesOrder]  WITH CHECK ADD  CONSTRAINT [FK_Sale_SalesOrder_Base_Department] FOREIGN KEY([dep_id])
	REFERENCES [dbo].[Base_Department] ([dep_id]);
	--与员工外键约束
	ALTER TABLE [dbo].[Sale_SalesOrder]  WITH CHECK ADD  CONSTRAINT [FK_Sale_SalesOrder_Base_Employee] FOREIGN KEY([ele_id])
	REFERENCES [dbo].[Base_Employee] ([ele_id]); 
	--与销售类型外键约束
	ALTER TABLE [dbo].[Sale_SalesOrder]  WITH CHECK ADD  CONSTRAINT [FK_Sale_SalesOrder_Base_SaleType] FOREIGN KEY([st_id])
	REFERENCES [dbo].[Base_SaleType] ([st_id]);
	--与币种外键约束
	ALTER TABLE [dbo].[Sale_SalesOrder]  WITH CHECK ADD  CONSTRAINT [FK_Sale_SalesOrder_Base_Currency] FOREIGN KEY([crc_id])
	REFERENCES [dbo].[Base_Currency] ([crc_id]);
	--与运输方式外键约束
	ALTER TABLE [dbo].[Sale_SalesOrder]  WITH CHECK ADD  CONSTRAINT [FK_Sale_SalesOrder_Base_ShipWay] FOREIGN KEY([sw_id])
	REFERENCES [dbo].[Base_ShipWay] ([sw_id]);
	--与新增用户外键约束
	ALTER TABLE [dbo].[Sale_SalesOrder]  WITH CHECK ADD  CONSTRAINT [FK_Sale_SalesOrder_System_Member_New] FOREIGN KEY([new_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
	--与修改用户外键约束
	ALTER TABLE [dbo].[Sale_SalesOrder]  WITH CHECK ADD  CONSTRAINT [FK_Sale_SalesOrder_System_Member_Update] FOREIGN KEY([update_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
end
/**/GO


--drop TABLE Sale_SalesOrders
--销售订单子表
if object_id('Sale_SalesOrders') is null
begin
	CREATE TABLE Sale_SalesOrders(
		sos_id int identity(1,1) PRIMARY KEY,--订单子表ID
		so_id int,--订单主表ID
		i_id int,--存货ID
		free1 nvarchar(100),--自由项1
		free2 nvarchar(100),--自由项2
		unit_price float,--无税单价
		tax_unit_price float, --含税单价
		quantity float, --数量
		num float, --件数
		acc_fh_quantity float,--累计发货数量
		acc_fh_num float,--累计发货件数
		convert_rate float,--换算率
		total_price float, --无税金额
		tax_total_price float,--含税金额
		tax float,--税额
		nat_unit_price float,--本币单价
		nat_total_price float,--本币金额
		nat_tax_unit_price float,--本币含税单价
		nat_tax_total_price float,--本币含税金额
		nat_tax float,--本币税额
		d_tax_rate float,--税率
		delivery_date datetime,--预交货日期
		d_remark nvarchar(300),--备注
		d_sdefine1 nvarchar(300),--字符自定义项1
		d_sdefine2 nvarchar(300),--字符自定义项2
		d_sdefine3 nvarchar(300),--字符自定义项3
		d_sdefine4 nvarchar(300),--字符自定义项4
		d_sdefine5 nvarchar(300),--字符自定义项5
		d_ndefine1 float,--数值自定义项1
		d_ndefine2 float,--数值自定义项2
		d_ndefine3 float,--数值自定义项3
		d_ndefine4 float,--数值自定义项4
		d_ndefine5 float --数值自定义项5
	);
	--与主表外键约束
	ALTER TABLE [dbo].[Sale_SalesOrders]  WITH CHECK ADD  CONSTRAINT [FK_Sale_SalesOrders_Sale_SalesOrder] FOREIGN KEY([so_id])
	REFERENCES [dbo].[Sale_SalesOrder] ([so_id]);
	--与存货档案外键约束
	ALTER TABLE [dbo].[Sale_SalesOrders]  WITH CHECK ADD  CONSTRAINT [FK_Sale_SalesOrders_Base_Inventory] FOREIGN KEY([i_id])
	REFERENCES [dbo].[Base_Inventory] ([i_id]);
end
/**/GO

--增加折扣相关字段
if not exists(select * from syscolumns where id=object_id('Sale_SalesOrders') and name='quoted_price')
begin
	alter table Sale_SalesOrders add
		quoted_price float, --报价
		kl float,--扣率
		zk float--折扣额
end
/**/GO

--采购订单主表
if object_id('Purchase_PurchaseOrder') is null
begin
	CREATE TABLE Purchase_PurchaseOrder(
		po_id int identity(1,1) PRIMARY KEY,--订单主表ID
		order_number nvarchar(50) UNIQUE,--单据号
		new_uid int,--新增人ID
		new_user nvarchar(50),--新增人名称
		new_date datetime,--新增日期
		update_uid int,--修改人ID
		update_user nvarchar(50),--修改人名称
		update_date datetime,--修改日期
		verify_uid int,--审核人ID
		verify_user nvarchar(50),--审核人名称
		vd_id int,--供应商ID
		pt_id int,--采购类型ID
		dep_id int,--采购部门ID
		ele_id int,--员工ID
		crc_id int, --币种ID
		exchange_rate float, --汇率
		tax_rate float,--税率
		sw_id int,--运输方式ID
		ship_address nvarchar(100),--发货地址
		contact nvarchar(50),--联系人
		phone nvarchar(50),--电话
		remark nvarchar(500),--备注
		sdefine1 nvarchar(300),--字符自定义项1
		sdefine2 nvarchar(300),--字符自定义项2
		sdefine3 nvarchar(300),--字符自定义项3
		sdefine4 nvarchar(300),--字符自定义项4
		sdefine5 nvarchar(300),--字符自定义项5
		ndefine1 float,--数值自定义项1
		ndefine2 float,--数值自定义项2
		ndefine3 float,--数值自定义项3
		ndefine4 float,--数值自定义项4
		ndefine5 float --数值自定义项5
	);
	--与供应商外键约束
	ALTER TABLE [dbo].[Purchase_PurchaseOrder]  WITH CHECK ADD  CONSTRAINT [FK_Purchase_PurchaseOrder_Base_Vendor] FOREIGN KEY([vd_id])
	REFERENCES [dbo].[Base_Vendor] ([vd_id]);
	--与部门外键约束
	ALTER TABLE [dbo].[Purchase_PurchaseOrder]  WITH CHECK ADD  CONSTRAINT [FK_Purchase_PurchaseOrder_Base_Department] FOREIGN KEY([dep_id])
	REFERENCES [dbo].[Base_Department] ([dep_id]);
	--与员工外键约束
	ALTER TABLE [dbo].[Purchase_PurchaseOrder]  WITH CHECK ADD  CONSTRAINT [FK_Purchase_PurchaseOrder_Base_Employee] FOREIGN KEY([ele_id])
	REFERENCES [dbo].[Base_Employee] ([ele_id]);
	--与采购类型外键约束
	ALTER TABLE [dbo].[Purchase_PurchaseOrder]  WITH CHECK ADD  CONSTRAINT [FK_Purchase_PurchaseOrder_Base_PurchaseType] FOREIGN KEY([pt_id])
	REFERENCES [dbo].[Base_PurchaseType] ([pt_id]);
	--与币种外键约束
	ALTER TABLE [dbo].[Purchase_PurchaseOrder]  WITH CHECK ADD  CONSTRAINT [FK_Purchase_PurchaseOrder_Base_Currency] FOREIGN KEY([crc_id])
	REFERENCES [dbo].[Base_Currency] ([crc_id]);
	--与运输方式外键约束
	ALTER TABLE [dbo].[Purchase_PurchaseOrder]  WITH CHECK ADD  CONSTRAINT [FK_Purchase_PurchaseOrder_Base_ShipWay] FOREIGN KEY([sw_id])
	REFERENCES [dbo].[Base_ShipWay] ([sw_id]);
	--与新增用户外键约束
	ALTER TABLE [dbo].[Purchase_PurchaseOrder]  WITH CHECK ADD  CONSTRAINT [FK_Purchase_PurchaseOrder_System_Member_New] FOREIGN KEY([new_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
	--与修改用户外键约束
	ALTER TABLE [dbo].[Purchase_PurchaseOrder]  WITH CHECK ADD  CONSTRAINT [FK_Purchase_PurchaseOrder_System_Member_Update] FOREIGN KEY([update_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
end
/**/GO
--drop TABLE Purchase_PurchaseOrders
--采购订单子表
if object_id('Purchase_PurchaseOrders') is null
begin
	CREATE TABLE Purchase_PurchaseOrders(
		pos_id int identity(1,1) PRIMARY KEY,--订单子表ID
		po_id int,--订单主表ID
		i_id int,--存货ID
		free1 nvarchar(100),--自由项1
		free2 nvarchar(100),--自由项2
		unit_price float,--无税单价
		tax_unit_price float, --含税单价
		quantity float, --数量
		acc_in_quantity float, --累计入库数量
		acc_in_num float,--累计入库件数
		num float, --件数
		convert_rate float,--换算率
		total_price float, --无税金额
		tax_total_price float,--含税金额
		acc_pay_money float, --累计付款金额
		tax float,--税额
		nat_unit_price float,--本币单价
		nat_total_price float,--本币金额
		nat_tax_unit_price float,--本币含税单价
		nat_tax_total_price float,--本币含税金额
		nat_tax float,--本币税额
		d_tax_rate float,--税率
		arrive_date datetime,--预到货日期
		d_remark nvarchar(300),--备注
		d_sdefine1 nvarchar(300),--字符自定义项1
		d_sdefine2 nvarchar(300),--字符自定义项2
		d_sdefine3 nvarchar(300),--字符自定义项3
		d_sdefine4 nvarchar(300),--字符自定义项4
		d_sdefine5 nvarchar(300),--字符自定义项5
		d_ndefine1 float,--数值自定义项1
		d_ndefine2 float,--数值自定义项2
		d_ndefine3 float,--数值自定义项3
		d_ndefine4 float,--数值自定义项4
		d_ndefine5 float --数值自定义项5
	);
	--与主表外键约束
	ALTER TABLE [dbo].[Purchase_PurchaseOrders]  WITH CHECK ADD  CONSTRAINT [FK_Purchase_PurchaseOrders_Purchase_PurchaseOrder] FOREIGN KEY([po_id])
	REFERENCES [dbo].[Purchase_PurchaseOrder] ([po_id]);
	--与存货档案外键约束
	ALTER TABLE [dbo].[Purchase_PurchaseOrders]  WITH CHECK ADD  CONSTRAINT [FK_Purchase_PurchaseOrders_Base_Inventory] FOREIGN KEY([i_id])
	REFERENCES [dbo].[Base_Inventory] ([i_id]);
end
/**/GO


--现存量表
if object_id('Stock_OnHand') is null
begin
	CREATE TABLE Stock_OnHand(
		oh_id int identity(1,1) PRIMARY KEY,--主键ID
		wh_id int,--仓库id
		i_id int,--存货id
		free1 nvarchar(100),--自由项1
		free2 nvarchar(100),--自由项2
		batch nvarchar(100),--批次
		quantity float,--结存数量
		num float--结存件数
	)
	--与仓库外键约束
	ALTER TABLE [dbo].[Stock_OnHand]  WITH CHECK ADD  CONSTRAINT [FK_Stock_OnHand_Base_Warehouse] FOREIGN KEY([wh_id])
	REFERENCES [dbo].[Base_Warehouse] ([wh_id]);
	--与存货外键约束
	ALTER TABLE [dbo].[Stock_OnHand]  WITH CHECK ADD  CONSTRAINT [FK_Stock_OnHand_Base_Inventory] FOREIGN KEY([i_id])
	REFERENCES [dbo].[Base_Inventory] ([i_id]);
end
/**/GO

--发货单主表
if object_id('Sale_DeliveryOrder') is null
begin
	CREATE TABLE Sale_DeliveryOrder(
		do_id int identity(1,1) PRIMARY KEY,--发货单主表ID
		order_number nvarchar(50) UNIQUE,--单据号
		new_uid int,--新增人ID
		new_user nvarchar(50),--新增人名称
		new_date datetime,--新增日期
		update_uid int,--修改人ID
		update_user nvarchar(50),--修改人名称
		update_date datetime,--修改日期
		verify_uid int,--审核人ID
		verify_user nvarchar(50),--审核人名称
		cus_id int,--客户ID
		st_id int,--销售类型ID
		iot_id int,--收发类型ID
		wh_id int,--仓库ID
		dep_id int,--销售部门ID
		ele_id int,--员工ID
		sw_id int,--发运方式ID
		ship_address nvarchar(100),--发货地址
		contact nvarchar(50),--联系人
		phone nvarchar(50),--电话
		crc_id int, --币种ID
		exchange_rate float, --汇率
		tax_rate float,--税率
		remark nvarchar(500),--备注
		sdefine1 nvarchar(300),--字符自定义项1
		sdefine2 nvarchar(300),--字符自定义项2
		sdefine3 nvarchar(300),--字符自定义项3
		sdefine4 nvarchar(300),--字符自定义项4
		sdefine5 nvarchar(300),--字符自定义项5
		ndefine1 float,--数值自定义项1
		ndefine2 float,--数值自定义项2
		ndefine3 float,--数值自定义项3
		ndefine4 float,--数值自定义项4
		ndefine5 float --数值自定义项5
	);
	--与客户外键约束
	ALTER TABLE [dbo].[Sale_DeliveryOrder]  WITH CHECK ADD  CONSTRAINT [FK_Sale_DeliveryOrder_Base_Customer] FOREIGN KEY([cus_id])
	REFERENCES [dbo].[Base_Customer] ([cus_id]);
	--与部门外键约束
	ALTER TABLE [dbo].[Sale_DeliveryOrder]  WITH CHECK ADD  CONSTRAINT [FK_Sale_DeliveryOrder_Base_Department] FOREIGN KEY([dep_id])
	REFERENCES [dbo].[Base_Department] ([dep_id]);
	--与销售类型外键约束
	ALTER TABLE [dbo].[Sale_DeliveryOrder]  WITH CHECK ADD  CONSTRAINT [FK_Sale_DeliveryOrder_Base_SaleType] FOREIGN KEY([st_id])
	REFERENCES [dbo].[Base_SaleType] ([st_id]);
	--与币种外键约束
	ALTER TABLE [dbo].[Sale_DeliveryOrder]  WITH CHECK ADD  CONSTRAINT [FK_Sale_DeliveryOrder_Base_Currency] FOREIGN KEY([crc_id])
	REFERENCES [dbo].[Base_Currency] ([crc_id]);
	--与新增用户外键约束
	ALTER TABLE [dbo].[Sale_DeliveryOrder]  WITH CHECK ADD  CONSTRAINT [FK_Sale_DeliveryOrder_System_Member_New] FOREIGN KEY([new_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
	--与修改用户外键约束
	ALTER TABLE [dbo].[Sale_DeliveryOrder]  WITH CHECK ADD  CONSTRAINT [FK_Sale_DeliveryOrder_System_Member_Update] FOREIGN KEY([update_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
end
/**/GO

--发货单表头增加整单折扣率字段
if not exists(select * from syscolumns where id=object_id('Sale_DeliveryOrder') and name='zdkl')
begin
	alter table Sale_DeliveryOrder add
		zdkl float--整单折扣率
end
/**/GO

--drop TABLE Sale_DeliveryOrders
--销售发货单子表
if object_id('Sale_DeliveryOrders') is null
begin
	CREATE TABLE Sale_DeliveryOrders(
		dos_id int identity(1,1) PRIMARY KEY,--发货单子表ID
		do_id int,--发货单主表ID
		sos_id int,--销售订单子表ID
		d_wh_id int,--仓库ID
		i_id int,--存货ID
		free1 nvarchar(100),--自由项1
		free2 nvarchar(100),--自由项2
		batch nvarchar(100),--批次
		unit_price float,--无税单价
		tax_unit_price float, --含税单价
		quantity float, --数量
		num float, --件数
		acc_out_quantity float,--累计出库数量
		acc_out_num float,--累计出库件数
		convert_rate float,--换算率
		total_price float, --无税金额
		tax_total_price float,--含税金额
		tax float,--税额
		nat_unit_price float,--本币单价
		nat_total_price float,--本币金额
		nat_tax_unit_price float,--本币含税单价
		nat_tax_total_price float,--本币含税金额
		nat_tax float,--本币税额
		d_tax_rate float,--税率
		delivery_date datetime,--预发货日期
		d_remark nvarchar(300),--备注
		d_sdefine1 nvarchar(300),--字符自定义项1
		d_sdefine2 nvarchar(300),--字符自定义项2
		d_sdefine3 nvarchar(300),--字符自定义项3
		d_sdefine4 nvarchar(300),--字符自定义项4
		d_sdefine5 nvarchar(300),--字符自定义项5
		d_ndefine1 float,--数值自定义项1
		d_ndefine2 float,--数值自定义项2
		d_ndefine3 float,--数值自定义项3
		d_ndefine4 float,--数值自定义项4
		d_ndefine5 float --数值自定义项5
	);
	--与主表外键约束
	ALTER TABLE [dbo].[Sale_DeliveryOrders]  WITH CHECK ADD  CONSTRAINT [FK_Sale_DeliveryOrders_Sale_SalesOrder] FOREIGN KEY([do_id])
	REFERENCES [dbo].[Sale_DeliveryOrder] ([do_id]);
	--与存货档案外键约束
	ALTER TABLE [dbo].[Sale_DeliveryOrders]  WITH CHECK ADD  CONSTRAINT [FK_Sale_DeliveryOrders_Base_Inventory] FOREIGN KEY([i_id])
	REFERENCES [dbo].[Base_Inventory] ([i_id]);
	--与订单子表外键约束
	ALTER TABLE [dbo].[Sale_DeliveryOrders]  WITH CHECK ADD  CONSTRAINT [FK_Sale_DeliveryOrders_Sale_SalesOrders] FOREIGN KEY([sos_id])
	REFERENCES [dbo].[Sale_SalesOrders] ([sos_id]);
end
/**/GO

--增加折扣相关字段
if not exists(select * from syscolumns where id=object_id('Sale_DeliveryOrders') and name='quoted_price')
begin
	alter table Sale_DeliveryOrders add
		quoted_price float, --报价
		kl float,--折扣率
		zk float--折扣金额
end
/**/GO

--物料清单主表
--drop table Base_BOM
/*
if object_id('Base_BOM') is null
begin
	CREATE TABLE Base_BOM(
		bo_id int identity(1,1) PRIMARY KEY,--BOM主表ID
		p_i_id int,--成品存货ID
		p_free1 nvarchar(100),--成品自由项1
		p_free2 nvarchar(100),--成品自由项2
		new_uid int,--新增人ID
		new_user nvarchar(50),--新增人名称
		new_date datetime,--新增日期
		update_uid int,--修改人ID
		update_user nvarchar(50),--修改人名称
		update_date datetime,--修改日期
		verify_uid int,--审核人ID
		verify_user nvarchar(50),--审核人名称
		wh_id int,--仓库ID
		dep_id int,--部门ID
		remark nvarchar(500),--备注
		sdefine1 nvarchar(300),--字符自定义项1
		sdefine2 nvarchar(300),--字符自定义项2
		sdefine3 nvarchar(300),--字符自定义项3
		sdefine4 nvarchar(300),--字符自定义项4
		sdefine5 nvarchar(300),--字符自定义项5
		ndefine1 float,--数值自定义项1
		ndefine2 float,--数值自定义项2
		ndefine3 float,--数值自定义项3
		ndefine4 float,--数值自定义项4
		ndefine5 float --数值自定义项5
	);
	--与部门外键约束
	ALTER TABLE [dbo].[Base_BOM]  WITH CHECK ADD  CONSTRAINT [FK_Base_BOM_Base_Department] FOREIGN KEY([dep_id])
	REFERENCES [dbo].[Base_Department] ([dep_id]);
	--与仓库外键约束
	ALTER TABLE [dbo].[Base_BOM]  WITH CHECK ADD  CONSTRAINT [FK_Base_BOM_Base_Warehouse] FOREIGN KEY([wh_id])
	REFERENCES [dbo].[Base_Warehouse] ([wh_id]);
	--与存货档案外键约束
	ALTER TABLE [dbo].[Base_BOM]  WITH CHECK ADD  CONSTRAINT [FK_Base_BOM_Base_Inventory] FOREIGN KEY([p_i_id])
	REFERENCES [dbo].[Base_Inventory] ([i_id]);
	--与新增用户外键约束
	ALTER TABLE [dbo].[Base_BOM]  WITH CHECK ADD  CONSTRAINT [FK_Base_BOM_System_Member_New] FOREIGN KEY([new_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
	--与修改用户外键约束
	ALTER TABLE [dbo].[Base_BOM]  WITH CHECK ADD  CONSTRAINT [FK_Base_BOM_System_Member_Update] FOREIGN KEY([update_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
	--与审核用户外键约束
	ALTER TABLE [dbo].[Base_BOM]  WITH CHECK ADD  CONSTRAINT [FK_Base_BOM_System_Member_Verify] FOREIGN KEY([verify_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
end
*/
/**/GO

/*
--drop TABLE Base_BOMs
--物料清单子表
if object_id('Base_BOMs') is null
begin
	CREATE TABLE Base_BOMs(
		bos_id int identity(1,1) PRIMARY KEY,--BOM子表ID
		bo_id int,--主表ID
		d_wh_id int,--仓库ID
		d_dep_id int,--部门ID
		i_id int,--存货ID
		free1 nvarchar(100),--自由项1
		free2 nvarchar(100),--自由项2
		bo_quantity float, --单件用量
		bo_num float, --件数
		convert_rate float,--换算率
		att_rate float,--损耗率
		d_remark nvarchar(300),--备注
		d_sdefine1 nvarchar(300),--字符自定义项1
		d_sdefine2 nvarchar(300),--字符自定义项2
		d_sdefine3 nvarchar(300),--字符自定义项3
		d_sdefine4 nvarchar(300),--字符自定义项4
		d_sdefine5 nvarchar(300),--字符自定义项5
		d_ndefine1 float,--数值自定义项1
		d_ndefine2 float,--数值自定义项2
		d_ndefine3 float,--数值自定义项3
		d_ndefine4 float,--数值自定义项4
		d_ndefine5 float --数值自定义项5
	);
	--与主表外键约束
	ALTER TABLE [dbo].[Base_BOMs]  WITH CHECK ADD  CONSTRAINT [FK_Base_BOMs_Base_BOM] FOREIGN KEY([bo_id])
	REFERENCES [dbo].[Base_BOM] ([bo_id]);
	--与存货档案外键约束
	ALTER TABLE [dbo].[Base_BOMs]  WITH CHECK ADD  CONSTRAINT [FK_Base_BOMs_Base_Inventory] FOREIGN KEY([i_id])
	REFERENCES [dbo].[Base_Inventory] ([i_id]);
	--与部门外键约束
	ALTER TABLE [dbo].[Base_BOMs]  WITH CHECK ADD  CONSTRAINT [FK_Base_BOMs_Base_Department] FOREIGN KEY([d_dep_id])
	REFERENCES [dbo].[Base_Department] ([dep_id]);
	--与仓库外键约束
	ALTER TABLE [dbo].[Base_BOMs]  WITH CHECK ADD  CONSTRAINT [FK_Base_BOMs_Base_Warehouse] FOREIGN KEY([d_wh_id])
	REFERENCES [dbo].[Base_Warehouse] ([wh_id]);
end
*/
/**/GO

 

/*
--物料清单主表
--drop table Plan_PMC
if object_id('Plan_PMC') is null
begin
	CREATE TABLE Plan_PMC(
		order_number nvarchar(50),--单据号
		pmc_id int identity(1,1) PRIMARY KEY,--主表ID
		p_i_id int,--成品存货ID
		p_free1 nvarchar(100),--成品自由项1
		p_free2 nvarchar(100),--成品自由项2
		sos_id int,--订单子表id
		pmc_quantity float,--产量
		acc_in_quantity float,--累计入库数量
		acc_in_num float,--累计入库件数
		plan_finish_date datetime,--预完工日
		act_finish_date datetime,--完工日期
		new_uid int,--新增人ID
		new_user nvarchar(50),--新增人名称
		new_date datetime,--新增日期
		update_uid int,--修改人ID
		update_user nvarchar(50),--修改人名称
		update_date datetime,--修改日期
		verify_uid int,--审核人ID
		verify_user nvarchar(50),--审核人名称
		finish_uid int,--完工人ID
		finish_user nvarchar(50),--完工人名称
		wh_id int,--仓库ID
		dep_id int,--部门ID
		remark nvarchar(500),--备注
		sdefine1 nvarchar(300),--字符自定义项1
		sdefine2 nvarchar(300),--字符自定义项2
		sdefine3 nvarchar(300),--字符自定义项3
		sdefine4 nvarchar(300),--字符自定义项4
		sdefine5 nvarchar(300),--字符自定义项5
		ndefine1 float,--数值自定义项1
		ndefine2 float,--数值自定义项2
		ndefine3 float,--数值自定义项3
		ndefine4 float,--数值自定义项4
		ndefine5 float --数值自定义项5
	);
	--与订单子表外键约束
	ALTER TABLE [dbo].[Plan_PMC]  WITH CHECK ADD  CONSTRAINT [FK_Plan_PMC_Sale_SalesOrders] FOREIGN KEY([sos_id])
	REFERENCES [dbo].[Sale_SalesOrders] ([sos_id]);
	--与部门外键约束
	ALTER TABLE [dbo].[Plan_PMC]  WITH CHECK ADD  CONSTRAINT [FK_Plan_PMC_Base_Department] FOREIGN KEY([dep_id])
	REFERENCES [dbo].[Base_Department] ([dep_id]);
	--与仓库外键约束
	ALTER TABLE [dbo].[Plan_PMC]  WITH CHECK ADD  CONSTRAINT [FK_Plan_PMC_Base_Warehouse] FOREIGN KEY([wh_id])
	REFERENCES [dbo].[Base_Warehouse] ([wh_id]);
	--与存货档案外键约束
	ALTER TABLE [dbo].[Plan_PMC]  WITH CHECK ADD  CONSTRAINT [FK_Plan_PMC_Base_Inventory] FOREIGN KEY([p_i_id])
	REFERENCES [dbo].[Base_Inventory] ([i_id]);
	--与新增用户外键约束
	ALTER TABLE [dbo].[Plan_PMC]  WITH CHECK ADD  CONSTRAINT [FK_Plan_PMC_System_Member_New] FOREIGN KEY([new_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
	--与修改用户外键约束
	ALTER TABLE [dbo].[Plan_PMC]  WITH CHECK ADD  CONSTRAINT [FK_Plan_PMC_System_Member_Update] FOREIGN KEY([update_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
	--与审核用户外键约束
	ALTER TABLE [dbo].[Plan_PMC]  WITH CHECK ADD  CONSTRAINT [FK_Plan_PMC_System_Member_Verify] FOREIGN KEY([verify_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
end
*/
/**/GO
/*
--drop TABLE Plan_PMCs
--生产加工单子表
if object_id('Plan_PMCs') is null
begin
	CREATE TABLE Plan_PMCs(
		pmcs_id int identity(1,1) PRIMARY KEY,--BOM子表ID
		pmc_id int,--主表ID
		d_wh_id int,--仓库ID
		d_dep_id int,--部门ID
		i_id int,--存货ID
		free1 nvarchar(100),--自由项1
		free2 nvarchar(100),--自由项2
		convert_rate float,--换算率
		num float, --件数
		quantity float, --产量
		bo_quantity float, --单件用量
		att_rate float,--损耗率
		plan_quantity float, --计划数量
		d_remark nvarchar(300),--备注
		d_sdefine1 nvarchar(300),--字符自定义项1
		d_sdefine2 nvarchar(300),--字符自定义项2
		d_sdefine3 nvarchar(300),--字符自定义项3
		d_sdefine4 nvarchar(300),--字符自定义项4
		d_sdefine5 nvarchar(300),--字符自定义项5
		d_ndefine1 float,--数值自定义项1
		d_ndefine2 float,--数值自定义项2
		d_ndefine3 float,--数值自定义项3
		d_ndefine4 float,--数值自定义项4
		d_ndefine5 float --数值自定义项5
	);
	--与主表外键约束
	ALTER TABLE [dbo].[Plan_PMCs]  WITH CHECK ADD  CONSTRAINT [FK_Plan_PMCs_Plan_PMC] FOREIGN KEY([pmc_id])
	REFERENCES [dbo].[Plan_PMC] ([pmc_id]);
	--与存货档案外键约束
	ALTER TABLE [dbo].[Plan_PMCs]  WITH CHECK ADD  CONSTRAINT [FK_Plan_PMCs_Base_Inventory] FOREIGN KEY([i_id])
	REFERENCES [dbo].[Base_Inventory] ([i_id]);
	--与部门外键约束
	ALTER TABLE [dbo].[Plan_PMCs]  WITH CHECK ADD  CONSTRAINT [FK_Plan_PMCs_Base_Department] FOREIGN KEY([d_dep_id])
	REFERENCES [dbo].[Base_Department] ([dep_id]);
	--与仓库外键约束
	ALTER TABLE [dbo].[Plan_PMCs]  WITH CHECK ADD  CONSTRAINT [FK_Plan_PMCs_Base_Warehouse] FOREIGN KEY([d_wh_id])
	REFERENCES [dbo].[Base_Warehouse] ([wh_id]);
end
*/
/**/GO

--调拨单主表
if object_id('Stock_Transfer') is null
begin
	CREATE TABLE Stock_Transfer(
		ts_id int identity(1,1) PRIMARY KEY,--单据主表ID
		order_number nvarchar(50) UNIQUE,--单据号
		new_uid int,--新增人ID
		new_user nvarchar(50),--新增人名称
		new_date datetime,--新增日期
		update_uid int,--修改人ID
		update_user nvarchar(50),--修改人名称
		update_date datetime,--修改日期
		verify_uid int,--审核人ID
		verify_user nvarchar(50),--审核人名称
		wh_id_in int,--转入仓库ID
		wh_id_out int,--转出仓库ID
		iot_id_in int,--入库类型ID
		iot_id_out int,--出库类型ID
		dep_id_in int,--转出部门ID
		dep_id_out int,--转入部门ID
		remark nvarchar(500),--备注
		sdefine1 nvarchar(300),--字符自定义项1
		sdefine2 nvarchar(300),--字符自定义项2
		sdefine3 nvarchar(300),--字符自定义项3
		sdefine4 nvarchar(300),--字符自定义项4
		sdefine5 nvarchar(300),--字符自定义项5
		ndefine1 float,--数值自定义项1
		ndefine2 float,--数值自定义项2
		ndefine3 float,--数值自定义项3
		ndefine4 float,--数值自定义项4
		ndefine5 float --数值自定义项5
	);
	--与入库类型外键约束
	ALTER TABLE [dbo].[Stock_Transfer]  WITH CHECK ADD  CONSTRAINT [FK_Stock_Transfer_Base_IOType_In] FOREIGN KEY([iot_id_in])
	REFERENCES [dbo].[Base_IOType] ([iot_id]);
	--与转出类型外键约束
	ALTER TABLE [dbo].[Stock_Transfer]  WITH CHECK ADD  CONSTRAINT [FK_Stock_Transfer_Base_IOType_Out] FOREIGN KEY([iot_id_out])
	REFERENCES [dbo].[Base_IOType] ([iot_id]);
	--与转入仓库外键约束
	ALTER TABLE [dbo].[Stock_Transfer]  WITH CHECK ADD  CONSTRAINT [FK_Stock_Transfer_Base_Warehouse_In] FOREIGN KEY([wh_id_in])
	REFERENCES [dbo].[Base_Warehouse] ([wh_id]);
	--与转出仓库外键约束
	ALTER TABLE [dbo].[Stock_Transfer]  WITH CHECK ADD  CONSTRAINT [FK_Stock_Transfer_Base_Warehouse_Out] FOREIGN KEY([wh_id_out])
	REFERENCES [dbo].[Base_Warehouse] ([wh_id]);
	--与转入部门外键约束
	ALTER TABLE [dbo].[Stock_Transfer]  WITH CHECK ADD  CONSTRAINT [FK_Stock_Transfer_Base_Department_In] FOREIGN KEY([dep_id_in])
	REFERENCES [dbo].[Base_Department] ([dep_id]);
	--与转出部门外键约束
	ALTER TABLE [dbo].[Stock_Transfer]  WITH CHECK ADD  CONSTRAINT [FK_Stock_Transfer_Base_Department_Out] FOREIGN KEY([dep_id_out])
	REFERENCES [dbo].[Base_Department] ([dep_id]);
	--与新增用户外键约束
	ALTER TABLE [dbo].[Stock_Transfer]  WITH CHECK ADD  CONSTRAINT [FK_Stock_Transfer_System_Member_New] FOREIGN KEY([new_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
	--与修改用户外键约束
	ALTER TABLE [dbo].[Stock_Transfer]  WITH CHECK ADD  CONSTRAINT [FK_Stock_Transfer_System_Member_Update] FOREIGN KEY([update_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
	--与审核用户外键约束
	ALTER TABLE [dbo].[Stock_Transfer]  WITH CHECK ADD  CONSTRAINT [FK_Stock_Transfer_System_Member_Verify] FOREIGN KEY([verify_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
end
/**/GO

--调拨单子表
if object_id('Stock_Transfers') is null
begin
	CREATE TABLE Stock_Transfers(
		tss_id int identity(1,1) PRIMARY KEY,--子表ID
		ts_id int,--主表ID
		i_id int,--存货ID
		free1 nvarchar(100),--自由项1
		free2 nvarchar(100),--自由项2
		batch nvarchar(100),--批次
		quantity float, --数量
		num float, --件数
		convert_rate float,--换算率
		unit_price float,--无税单价
		total_price float, --无税金额
		pmcs_id int,--加工单子表ID
		d_remark nvarchar(300),--备注
		d_sdefine1 nvarchar(300),--字符自定义项1
		d_sdefine2 nvarchar(300),--字符自定义项2
		d_sdefine3 nvarchar(300),--字符自定义项3
		d_sdefine4 nvarchar(300),--字符自定义项4
		d_sdefine5 nvarchar(300),--字符自定义项5
		d_ndefine1 float,--数值自定义项1
		d_ndefine2 float,--数值自定义项2
		d_ndefine3 float,--数值自定义项3
		d_ndefine4 float,--数值自定义项4
		d_ndefine5 float --数值自定义项5
	);
	--与主表外键约束
	ALTER TABLE [dbo].[Stock_Transfers]  WITH CHECK ADD  CONSTRAINT [FK_Stock_Transfers_Stock_Transfer] FOREIGN KEY([ts_id])
	REFERENCES [dbo].[Stock_Transfer] ([ts_id]);
	--与存货档案外键约束
	ALTER TABLE [dbo].[Stock_Transfers]  WITH CHECK ADD  CONSTRAINT [FK_Stock_Transfers_Base_Inventory] FOREIGN KEY([i_id])
	REFERENCES [dbo].[Base_Inventory] ([i_id]);
end
/**/GO

--drop table Stock_StockCounts;drop table Stock_StockCount;
if object_id('Stock_StockCount') is null
begin
	CREATE TABLE Stock_StockCount(
		sc_id int identity(1,1) PRIMARY KEY,--单据主表ID
		order_number nvarchar(50) UNIQUE,--单据号
		new_uid int,--新增人ID
		new_user nvarchar(50),--新增人名称
		new_date datetime,--新增日期
		update_uid int,--修改人ID
		update_user nvarchar(50),--修改人名称
		update_date datetime,--修改日期
		verify_uid int,--审核人ID
		verify_user nvarchar(50),--审核人名称
		wh_id int,--仓库ID
		dep_id int,--部门ID
		ele_id int,--经手人ID
		iot_id_in int,--入库类型ID
		iot_id_out int,--出库类型ID
		remark nvarchar(500),--备注
		sdefine1 nvarchar(300),--字符自定义项1
		sdefine2 nvarchar(300),--字符自定义项2
		sdefine3 nvarchar(300),--字符自定义项3
		sdefine4 nvarchar(300),--字符自定义项4
		sdefine5 nvarchar(300),--字符自定义项5
		ndefine1 float,--数值自定义项1
		ndefine2 float,--数值自定义项2
		ndefine3 float,--数值自定义项3
		ndefine4 float,--数值自定义项4
		ndefine5 float --数值自定义项5
	);
	--与仓库外键约束
	ALTER TABLE [dbo].[Stock_StockCount]  WITH CHECK ADD  CONSTRAINT [FK_Stock_StockCount_Base_Warehouse] FOREIGN KEY([wh_id])
	REFERENCES [dbo].[Base_Warehouse] ([wh_id]);
	--与入库类型外键约束
	ALTER TABLE [dbo].[Stock_StockCount]  WITH CHECK ADD  CONSTRAINT [FK_Stock_StockCount_Base_IOType_In] FOREIGN KEY([iot_id_in])
	REFERENCES [dbo].[Base_IOType] ([iot_id]);
	--与出库类型外键约束
	ALTER TABLE [dbo].[Stock_StockCount]  WITH CHECK ADD  CONSTRAINT [FK_Stock_StockCount_Base_IOType_Out] FOREIGN KEY([iot_id_out])
	REFERENCES [dbo].[Base_IOType] ([iot_id]);
	--与部门外键约束
	ALTER TABLE [dbo].[Stock_StockCount]  WITH CHECK ADD  CONSTRAINT [FK_Stock_StockCount_Base_Department] FOREIGN KEY([dep_id])
	REFERENCES [dbo].[Base_Department] ([dep_id]);
	--与员工外键约束
	ALTER TABLE [dbo].[Stock_StockCount]  WITH CHECK ADD  CONSTRAINT [FK_Stock_StockCount_Base_Employee] FOREIGN KEY([ele_id])
	REFERENCES [dbo].[Base_Employee] ([ele_id]); 
	--与新增用户外键约束
	ALTER TABLE [dbo].[Stock_StockCount]  WITH CHECK ADD  CONSTRAINT [FK_Stock_StockCount_System_Member_New] FOREIGN KEY([new_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
	--与修改用户外键约束
	ALTER TABLE [dbo].[Stock_StockCount]  WITH CHECK ADD  CONSTRAINT [FK_Stock_StockCount_System_Member_Update] FOREIGN KEY([update_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
	--与审核用户外键约束
	ALTER TABLE [dbo].[Stock_StockCount]  WITH CHECK ADD  CONSTRAINT [FK_Stock_StockCount_System_Member_Verify] FOREIGN KEY([verify_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
end
/**/GO

--drop table Stock_StockCounts
--调拨单子表
if object_id('Stock_StockCounts') is null
begin
	CREATE TABLE Stock_StockCounts(
		scs_id int identity(1,1) PRIMARY KEY,--子表ID
		sc_id int,--主表ID
		i_id int,--存货ID
		free1 nvarchar(100),--自由项1
		free2 nvarchar(100),--自由项2
		batch nvarchar(100),--批次
		quantity_zm float, --账面数量
		num_zm float, --账面件数
		quantity float, --盘点数量
		num float, --盘点件数
		convert_rate float,--换算率
		d_remark nvarchar(300),--备注
		d_sdefine1 nvarchar(300),--字符自定义项1
		d_sdefine2 nvarchar(300),--字符自定义项2
		d_sdefine3 nvarchar(300),--字符自定义项3
		d_sdefine4 nvarchar(300),--字符自定义项4
		d_sdefine5 nvarchar(300),--字符自定义项5
		d_ndefine1 float,--数值自定义项1
		d_ndefine2 float,--数值自定义项2
		d_ndefine3 float,--数值自定义项3
		d_ndefine4 float,--数值自定义项4
		d_ndefine5 float --数值自定义项5
	);
	--与主表外键约束
	ALTER TABLE [dbo].[Stock_StockCounts]  WITH CHECK ADD  CONSTRAINT [FK_Stock_StockCounsc_Stock_StockCount] FOREIGN KEY([sc_id])
	REFERENCES [dbo].[Stock_StockCount] ([sc_id]);
	--与存货档案外键约束
	ALTER TABLE [dbo].[Stock_StockCounts]  WITH CHECK ADD  CONSTRAINT [FK_Stock_StockCounsc_Base_Inventory] FOREIGN KEY([i_id])
	REFERENCES [dbo].[Base_Inventory] ([i_id]);
end
/**/GO

--drop table Stock_InOuts;drop table Stock_InOut;
--出入库单主表
if object_id('Stock_InOut') is null
begin
	CREATE TABLE Stock_InOut(
		io_id int identity(1,1) PRIMARY KEY,--单据主表ID
		io_flag int,--进出标志
		order_type int,--单据类型
		order_number nvarchar(50) UNIQUE,--单据号
		new_uid int,--新增人ID
		new_user nvarchar(50),--新增人名称
		new_date datetime,--新增日期
		update_uid int,--修改人ID
		update_user nvarchar(50),--修改人名称
		update_date datetime,--修改日期
		verify_uid int,--审核人ID
		verify_user nvarchar(50),--审核人名称
		wh_id int,--仓库ID
		iot_id int,--收发类型ID
		cus_id int,--客户ID
		st_id int,--销售类型ID
		vd_id int,--供应商ID
		pt_id int,--采购类型ID
		dep_id int,--部门ID
		ele_id int,--员工ID
    tax_rate float,--税率
		sw_id int,--发运方式
		ship_address nvarchar(100),--发货地址
		contact nvarchar(50),--联系人
		phone nvarchar(50),--电话
		remark nvarchar(500),--备注
		from_id int,--来源单据ID
		from_type varchar(50),--单据来源
		bus_type varchar(50),--业务类型
		sdefine1 nvarchar(300),--字符自定义项1
		sdefine2 nvarchar(300),--字符自定义项2
		sdefine3 nvarchar(300),--字符自定义项3
		sdefine4 nvarchar(300),--字符自定义项4
		sdefine5 nvarchar(300),--字符自定义项5
		ndefine1 float,--数值自定义项1
		ndefine2 float,--数值自定义项2
		ndefine3 float,--数值自定义项3
		ndefine4 float,--数值自定义项4
		ndefine5 float --数值自定义项5
	);
	--与进出类型外键约束
	ALTER TABLE [dbo].[Stock_InOut]  WITH CHECK ADD  CONSTRAINT [FK_Stock_InOut_Base_IOType] FOREIGN KEY([iot_id])
	REFERENCES [dbo].[Base_IOType] ([iot_id]);
 	--与销售类型外键约束
	ALTER TABLE [dbo].[Stock_InOut]  WITH CHECK ADD  CONSTRAINT [FK_Stock_InOut_Base_SaleType] FOREIGN KEY([st_id])
	REFERENCES [dbo].[Base_SaleType] ([st_id]);
	--与采购类型外键约束
	ALTER TABLE [dbo].[Stock_InOut]  WITH CHECK ADD  CONSTRAINT [FK_Stock_InOut_Base_PurchaseType] FOREIGN KEY([pt_id])
	REFERENCES [dbo].[Base_PurchaseType] ([pt_id]);
	--与客户外键约束
	ALTER TABLE [dbo].[Stock_InOut]  WITH CHECK ADD  CONSTRAINT [FK_Stock_InOut_Base_Customer] FOREIGN KEY([cus_id])
	REFERENCES [dbo].[Base_Customer] ([cus_id]);
	--与供应商外键约束
	ALTER TABLE [dbo].[Stock_InOut]  WITH CHECK ADD  CONSTRAINT [FK_Stock_InOut_Base_Vendor] FOREIGN KEY([vd_id])
	REFERENCES [dbo].[Base_Vendor] ([vd_id]);
	--与仓库外键约束
	ALTER TABLE [dbo].[Stock_InOut]  WITH CHECK ADD  CONSTRAINT [FK_Stock_InOut_Base_Warehouse] FOREIGN KEY([wh_id])
	REFERENCES [dbo].[Base_Warehouse] ([wh_id]);
	--与部门外键约束
	ALTER TABLE [dbo].[Stock_InOut]  WITH CHECK ADD  CONSTRAINT [FK_Stock_InOut_Base_Department] FOREIGN KEY([dep_id])
	REFERENCES [dbo].[Base_Department] ([dep_id]);
	--与员工外键约束
	ALTER TABLE [dbo].[Stock_InOut]  WITH CHECK ADD  CONSTRAINT [FK_Stock_InOut_Base_Employee] FOREIGN KEY([ele_id])
	REFERENCES [dbo].[Base_Employee] ([ele_id]); 
	--与新增用户外键约束
	ALTER TABLE [dbo].[Stock_InOut]  WITH CHECK ADD  CONSTRAINT [FK_Stock_InOut_System_Member_New] FOREIGN KEY([new_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
	--与修改用户外键约束
	ALTER TABLE [dbo].[Stock_InOut]  WITH CHECK ADD  CONSTRAINT [FK_Stock_InOut_System_Member_Update] FOREIGN KEY([update_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
	--与审核用户外键约束
	ALTER TABLE [dbo].[Stock_InOut]  WITH CHECK ADD  CONSTRAINT [FK_Stock_InOut_System_Member_Verify] FOREIGN KEY([verify_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
end
/**/GO

--进出单子表
if object_id('Stock_InOuts') is null
begin
	CREATE TABLE Stock_InOuts(
		ios_id int identity(1,1) PRIMARY KEY,--子表ID
		io_id int,--主表ID
		d_wh_id int,--仓库ID
		d_dep_id int,--部门ID
		i_id int,--存货ID
		free1 nvarchar(100),--自由项1
		free2 nvarchar(100),--自由项2
		batch nvarchar(100),--批次
		unit_price float,--无税单价
		tax_unit_price float, --含税单价
		quantity float, --数量
		num float, --件数
		convert_rate float,--换算率
		total_price float, --无税金额
		tax_total_price float,--含税金额
		tax float,--税额
		d_tax_rate float,--税率
		dz_quantity float, --累计对账数量
		dz_num float, --累计对账件数
		pmcs_id int,--加工单子表ID
		mos_id int,--领料单子表ID
		dos_id int,--发货单子表ID
		aos_id int,--到货单子表ID
		pos_id int,--采购订单子表ID
		tss_id int,--调拨单子表ID
		scs_id int,--盘点单子表ID
		d_remark nvarchar(300),--备注
		d_sdefine1 nvarchar(300),--字符自定义项1
		d_sdefine2 nvarchar(300),--字符自定义项2
		d_sdefine3 nvarchar(300),--字符自定义项3
		d_sdefine4 nvarchar(300),--字符自定义项4
		d_sdefine5 nvarchar(300),--字符自定义项5
		d_ndefine1 float,--数值自定义项1
		d_ndefine2 float,--数值自定义项2
		d_ndefine3 float,--数值自定义项3
		d_ndefine4 float,--数值自定义项4
		d_ndefine5 float --数值自定义项5
	);
	--与主表外键约束
	ALTER TABLE [dbo].[Stock_InOuts]  WITH CHECK ADD  CONSTRAINT [FK_Stock_InOuts_Stock_InOut] FOREIGN KEY([io_id])
	REFERENCES [dbo].[Stock_InOut] ([io_id]);
	--与仓库外键约束
	ALTER TABLE [dbo].[Stock_InOuts]  WITH CHECK ADD  CONSTRAINT [FK_Stock_InOuts_Base_Warehouse] FOREIGN KEY([d_wh_id])
	REFERENCES [dbo].[Base_Warehouse] ([wh_id]);
	--与部门外键约束
	ALTER TABLE [dbo].[Stock_InOuts]  WITH CHECK ADD  CONSTRAINT [FK_Stock_InOuts_Base_Department] FOREIGN KEY([d_dep_id])
	REFERENCES [dbo].[Base_Department] ([dep_id]);
	--与存货档案外键约束
	ALTER TABLE [dbo].[Stock_InOuts]  WITH CHECK ADD  CONSTRAINT [FK_Stock_InOuts_Base_Inventory] FOREIGN KEY([i_id])
	REFERENCES [dbo].[Base_Inventory] ([i_id]);
	--与采购订单子表外键约束
	ALTER TABLE [dbo].[Stock_InOuts]  WITH CHECK ADD  CONSTRAINT [FK_Stock_InOuts_Purchase_PurchaseOrders] FOREIGN KEY([pos_id])
	REFERENCES [dbo].[Purchase_PurchaseOrders] ([pos_id]);
	--与发货单子表外键约束
	ALTER TABLE [dbo].[Stock_InOuts]  WITH CHECK ADD  CONSTRAINT [FK_Stock_InOuts_Sale_DeliveryOrders] FOREIGN KEY([dos_id])
	REFERENCES [dbo].[Sale_DeliveryOrders] ([dos_id]);
	--与盘点单子表外键约束
	ALTER TABLE [dbo].[Stock_InOuts]  WITH CHECK ADD  CONSTRAINT [FK_Stock_InOuts_Stock_StockCounts] FOREIGN KEY([scs_id])
	REFERENCES [dbo].[Stock_StockCounts] ([scs_id]);
	--与调拨单子表外键约束
	ALTER TABLE [dbo].[Stock_InOuts]  WITH CHECK ADD  CONSTRAINT [FK_Stock_InOuts_Stock_Transfers] FOREIGN KEY([tss_id])
	REFERENCES [dbo].[Stock_Transfers] ([tss_id]);
end
/**/GO

--对账单主表
if object_id('ARAP_CheckAccount') is null
begin
	CREATE TABLE ARAP_CheckAccount(
		ca_id int identity(1,1) PRIMARY KEY,--对账单主表ID
		rp_flag nvarchar(2),--收付标志:AR应收、AP应付
		order_number nvarchar(50) UNIQUE,--单据号
		invoice_no nvarchar(50),--发票号
		new_uid int,--新增人ID
		new_user nvarchar(50),--新增人名称
		new_date datetime,--新增日期
		update_uid int,--修改人ID
		update_user nvarchar(50),--修改人名称
		update_date datetime,--修改日期
		verify_uid int,--审核人ID
		verify_user nvarchar(50),--审核人名称
		cus_id int,--客户ID
		vd_id int,--供应商ID
		dep_id int,--部门ID
		ele_id int,--业务员ID
		crc_id int, --币种ID
		wh_id int,--仓库ID
		exchange_rate float, --汇率
		tax_rate float,--税率
		from_id int,--来源单据ID
		from_type varchar(50),--单据来源
		remark nvarchar(500),--备注
		sdefine1 nvarchar(300),--字符自定义项1
		sdefine2 nvarchar(300),--字符自定义项2
		sdefine3 nvarchar(300),--字符自定义项3
		sdefine4 nvarchar(300),--字符自定义项4
		sdefine5 nvarchar(300),--字符自定义项5
		ndefine1 float,--数值自定义项1
		ndefine2 float,--数值自定义项2
		ndefine3 float,--数值自定义项3
		ndefine4 float,--数值自定义项4
		ndefine5 float --数值自定义项5
	);
	--与仓库外键约束
	ALTER TABLE [dbo].[ARAP_CheckAccount]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_CheckAccount_Base_Warehouse] FOREIGN KEY([wh_id])
	REFERENCES [dbo].[Base_Warehouse] ([wh_id]);
	--与客户外键约束
	ALTER TABLE [dbo].[ARAP_CheckAccount]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_CheckAccount_Base_Customer] FOREIGN KEY([cus_id])
	REFERENCES [dbo].[Base_Customer] ([cus_id]);
	--与供应商外键约束
	ALTER TABLE [dbo].[ARAP_CheckAccount]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_CheckAccount_Base_Vendor] FOREIGN KEY([vd_id])
	REFERENCES [dbo].[Base_Vendor] ([vd_id]);
	--与部门外键约束
	ALTER TABLE [dbo].[ARAP_CheckAccount]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_CheckAccount_Base_Department] FOREIGN KEY([dep_id])
	REFERENCES [dbo].[Base_Department] ([dep_id]);
	--与员工外键约束
	ALTER TABLE [dbo].[ARAP_CheckAccount]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_CheckAccount_Base_Employee] FOREIGN KEY([ele_id])
	REFERENCES [dbo].[Base_Employee] ([ele_id]); 
	--与币种外键约束
	ALTER TABLE [dbo].[ARAP_CheckAccount]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_CheckAccount_Base_Currency] FOREIGN KEY([crc_id])
	REFERENCES [dbo].[Base_Currency] ([crc_id]);
	--与新增用户外键约束
	ALTER TABLE [dbo].[ARAP_CheckAccount]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_CheckAccount_System_Member_New] FOREIGN KEY([new_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
	--与修改用户外键约束
	ALTER TABLE [dbo].[ARAP_CheckAccount]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_CheckAccount_System_Member_Update] FOREIGN KEY([update_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
end
/**/GO

--drop TABLE ARAP_CheckAccounts
--对账单子表
if object_id('ARAP_CheckAccounts') is null
begin
	CREATE TABLE ARAP_CheckAccounts(
		cas_id int identity(1,1) PRIMARY KEY,--对账单子表ID
		ca_id int,--对账单主表ID
		d_wh_id int,--仓库ID
		i_id int,--存货ID
		free1 nvarchar(100),--自由项1
		free2 nvarchar(100),--自由项2
		batch nvarchar(100),--批次
		ios_id int,--进出单子表id
		unit_price float,--无税单价
		tax_unit_price float, --含税单价
		quantity float, --数量
		num float, --件数
		convert_rate float,--换算率
		total_price float, --无税金额
		tax_total_price float,--含税金额
		tax float,--税额
		nat_unit_price float,--本币单价
		nat_total_price float,--本币金额
		nat_tax_unit_price float,--本币含税单价
		nat_tax_total_price float,--本币含税金额
		nat_tax float,--本币税额
		d_tax_rate float,--税率
		d_remark nvarchar(300),--备注
		d_sdefine1 nvarchar(300),--字符自定义项1
		d_sdefine2 nvarchar(300),--字符自定义项2
		d_sdefine3 nvarchar(300),--字符自定义项3
		d_sdefine4 nvarchar(300),--字符自定义项4
		d_sdefine5 nvarchar(300),--字符自定义项5
		d_ndefine1 float,--数值自定义项1
		d_ndefine2 float,--数值自定义项2
		d_ndefine3 float,--数值自定义项3
		d_ndefine4 float,--数值自定义项4
		d_ndefine5 float --数值自定义项5
	);
	--与主表外键约束
	ALTER TABLE [dbo].[ARAP_CheckAccounts]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_CheckAccounts_ARAP_CheckAccount] FOREIGN KEY([ca_id])
	REFERENCES [dbo].[ARAP_CheckAccount] ([ca_id]);
	--与存货档案外键约束
	ALTER TABLE [dbo].[ARAP_CheckAccounts]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_CheckAccounts_Base_Inventory] FOREIGN KEY([i_id])
	REFERENCES [dbo].[Base_Inventory] ([i_id]);
	--与进出单子表外键约束
	ALTER TABLE [dbo].[ARAP_CheckAccounts]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_CheckAccounts_Stock_InOuts] FOREIGN KEY([ios_id])
	REFERENCES [dbo].[Stock_InOuts] ([ios_id]);
	--与仓库外键约束
	ALTER TABLE [dbo].[ARAP_CheckAccounts]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_CheckAccounts_Base_Warehouse] FOREIGN KEY([d_wh_id])
	REFERENCES [dbo].[Base_Warehouse] ([wh_id]);
end
/**/GO

--drop TABLE ARAP_APDetail
--应收应付明细表
if object_id('ARAP_APDetail') is null
begin
	CREATE TABLE ARAP_APDetail(
		apd_id int identity(1,1) PRIMARY KEY,--应收应付明细ID
		new_date datetime,--新增日期
		rp_flag nvarchar(2),--收付标志:AR应收、AP应付
		invoice_no nvarchar(50),--发票号
		i_id int,--存货ID
		free1 nvarchar(100),--自由项1
		free2 nvarchar(100),--自由项2
		batch nvarchar(100),--批次
		unit_price float,--无税单价
		tax_unit_price float, --含税单价
		quantity float, --数量
		num float, --件数
		convert_rate float, --换算率
		total_price float, --无税金额
		tax_total_price float,--含税金额
		nat_unit_price float,--本币单价
		nat_total_price float,--本币金额
		nat_tax_unit_price float,--本币含税单价
		nat_tax_total_price float,--本币含税金额
		tax_rate float,--税率
		crc_id int, --币种ID
		exchange_rate float, --汇率
		acc_hx_amount float,--累计核销金额
		acc_hx_nat_amount float,--本币累计核销金额
		cas_id int,--对账单子表id
		orp_id int,--其它应收应付单id
		order_type int,--进出单据类型
		cus_id int,--客户ID
		vd_id int,--供应商ID
		dep_id int,--部门ID
		ele_id int,--业务员ID
		from_id int,--来源单据ID
		from_order_number nvarchar(50),--来源单据号
		from_type nvarchar(50),--单据来源
		bus_order_number nvarchar(50),--业务订单号
		bus_type nvarchar(50),--业务类型
		remark nvarchar(300),--备注
		new_uid int,--新增人ID
		new_user nvarchar(50),--新增人名称
		update_uid int,--修改人ID
		update_user nvarchar(50),--修改人名称
		update_date datetime,--修改日期
		verify_uid int,--审核人ID
		verify_user nvarchar(50),--审核人名称
		d_sdefine1 nvarchar(300),--字符自定义项1
		d_sdefine2 nvarchar(300),--字符自定义项2
		d_sdefine3 nvarchar(300),--字符自定义项3
		d_sdefine4 nvarchar(300),--字符自定义项4
		d_sdefine5 nvarchar(300),--字符自定义项5
		d_ndefine1 float,--数值自定义项1
		d_ndefine2 float,--数值自定义项2
		d_ndefine3 float,--数值自定义项3
		d_ndefine4 float,--数值自定义项4
		d_ndefine5 float --数值自定义项5
	);
	--与存货档案外键约束
	ALTER TABLE [dbo].[ARAP_APDetail]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_APDetail_Base_Inventory] FOREIGN KEY([i_id])
	REFERENCES [dbo].[Base_Inventory] ([i_id]);
	--与对账单子表外键约束
	ALTER TABLE [dbo].[ARAP_APDetail]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_APDetail_ARAP_CheckAccounts] FOREIGN KEY([cas_id])
	REFERENCES [dbo].[ARAP_CheckAccounts] ([cas_id]);
	--与其它应收应付单主表外键约束
	ALTER TABLE [dbo].[ARAP_APDetail]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_APDetail_ARAP_OtherRP] FOREIGN KEY([orp_id])
	REFERENCES [dbo].[ARAP_OtherRP] ([orp_id]);
	--与部门外键约束
	ALTER TABLE [dbo].[ARAP_APDetail]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_APDetail_Base_Department] FOREIGN KEY([dep_id])
	REFERENCES [dbo].[Base_Department] ([dep_id]);
	--与员工外键约束
	ALTER TABLE [dbo].[ARAP_APDetail]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_APDetail_Base_Employee] FOREIGN KEY([ele_id])
	REFERENCES [dbo].[Base_Employee] ([ele_id]); 
	--与客户外键约束
	ALTER TABLE [dbo].[ARAP_APDetail]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_APDetail_Base_Customer] FOREIGN KEY([cus_id])
	REFERENCES [dbo].[Base_Customer] ([cus_id]);
	--与供应商外键约束
	ALTER TABLE [dbo].[ARAP_APDetail]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_APDetail_Base_Vendor] FOREIGN KEY([vd_id])
	REFERENCES [dbo].[Base_Vendor] ([vd_id]);
	--与币种外键约束
	ALTER TABLE [dbo].[ARAP_APDetail]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_APDetail_Base_Currency] FOREIGN KEY([crc_id])
	REFERENCES [dbo].[Base_Currency] ([crc_id]);
end
/**/GO

--核销单类型表
--drop TABLE ARAP_CancelType
if object_id('ARAP_CancelType') is null
begin
	CREATE TABLE ARAP_CancelType(
		cl_type int PRIMARY KEY,--核销类型编码
		cl_type_name nvarchar(20)--核销类型名称
	);
end
/**/GO

--drop TABLE ARAP_SFKuan
--收付款单主表
if object_id('ARAP_SFKuan') is null
begin
	CREATE TABLE ARAP_SFKuan(
		sfk_id int identity(1,1) PRIMARY KEY,--收付款单主表ID
		rp_flag nvarchar(2),--收付标志:AR收款、AP付款
		order_number nvarchar(50) UNIQUE,--单据号
		new_uid int,--新增人ID
		new_user nvarchar(50),--新增人名称
		new_date datetime,--新增日期
		update_uid int,--修改人ID
		update_user nvarchar(50),--修改人名称
		update_date datetime,--修改日期
		verify_uid int,--审核人ID
		verify_user nvarchar(50),--审核人名称
		ss_id int,--结算方式ID
		invoice_no nvarchar(50),--发票号
		ac_id int,--结算账户ID
		amount float,--金额
		nat_amount float,--本币金额
		acc_hx_amount float,--累计核销金额
		acc_hx_nat_amount float,--本币累计核销金额
		cus_id int,--客户ID
		vd_id int,--供应商ID
		dep_id int,--部门ID
		ele_id int,--业务员ID
		crc_id int, --币种ID
		exchange_rate float, --汇率
		remark nvarchar(500),--备注
		sdefine1 nvarchar(300),--字符自定义项1
		sdefine2 nvarchar(300),--字符自定义项2
		sdefine3 nvarchar(300),--字符自定义项3
		sdefine4 nvarchar(300),--字符自定义项4
		sdefine5 nvarchar(300),--字符自定义项5
		ndefine1 float,--数值自定义项1
		ndefine2 float,--数值自定义项2
		ndefine3 float,--数值自定义项3
		ndefine4 float,--数值自定义项4
		ndefine5 float --数值自定义项5
	);
	--与客户外键约束
	ALTER TABLE [dbo].[ARAP_SFKuan]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_SFKuan_Base_Customer] FOREIGN KEY([cus_id])
	REFERENCES [dbo].[Base_Customer] ([cus_id]);
	--与供应商外键约束
	ALTER TABLE [dbo].[ARAP_SFKuan]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_SFKuan_Base_Vendor] FOREIGN KEY([vd_id])
	REFERENCES [dbo].[Base_Vendor] ([vd_id]);
	--与部门外键约束
	ALTER TABLE [dbo].[ARAP_SFKuan]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_SFKuan_Base_Department] FOREIGN KEY([dep_id])
	REFERENCES [dbo].[Base_Department] ([dep_id]);
	--与员工外键约束
	ALTER TABLE [dbo].[ARAP_SFKuan]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_SFKuan_Base_Employee] FOREIGN KEY([ele_id])
	REFERENCES [dbo].[Base_Employee] ([ele_id]); 
	--与币种外键约束
	ALTER TABLE [dbo].[ARAP_SFKuan]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_SFKuan_Base_Currency] FOREIGN KEY([crc_id])
	REFERENCES [dbo].[Base_Currency] ([crc_id]);
	--与新增用户外键约束
	ALTER TABLE [dbo].[ARAP_SFKuan]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_SFKuan_System_Member_New] FOREIGN KEY([new_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
	--与修改用户外键约束
	ALTER TABLE [dbo].[ARAP_SFKuan]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_SFKuan_System_Member_Update] FOREIGN KEY([update_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
end
/**/GO

--收付款核销表
--drop TABLE ARAP_APCancel
if object_id('ARAP_APCancel') is null
begin
	CREATE TABLE ARAP_APCancel(
		acl_id int identity(1,1) PRIMARY KEY,--核销ID
		cl_type int,--核销类型 1,收款 2, 付款 3,预收冲应收 4,预付冲应付
		sfk_id int,--收付款单主表ID
		apd_id int,--应收应付账ID
		co_id int,--核销单主表ID
		d_exchange_rate float, --汇率
		hx_amount float,--本次核销金额
		nat_hx_amount float,--本次核销本币金额
		d_remark nvarchar(500)--备注
	);
	--与收付款单外键约束
	ALTER TABLE [dbo].[ARAP_APCancel]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_APCancel_ARAP_SFKuan] FOREIGN KEY([sfk_id])
	REFERENCES [dbo].[ARAP_SFKuan] ([sfk_id]);
	--与应收应付账外键约束
	ALTER TABLE [dbo].[ARAP_APCancel]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_APCancel_ARAP_APDetail] FOREIGN KEY([apd_id])
	REFERENCES [dbo].[ARAP_APDetail] ([apd_id]);
	--与核销单外键约束
	ALTER TABLE [dbo].[ARAP_APCancel]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_APCancel_ARAP_CancelOrder] FOREIGN KEY([co_id])
	REFERENCES [dbo].[ARAP_CancelOrder] ([co_id]);
end
/**/GO

--核销单主表
--drop TABLE ARAP_CancelOrder
if object_id('ARAP_CancelOrder') is null
begin
	CREATE TABLE ARAP_CancelOrder(
		co_id int identity(1,1) PRIMARY KEY,--核销单主表ID
		order_number nvarchar(50) UNIQUE,--单据号
		cl_type int,--核销类型 3,预收冲应收 4,预付冲应付
		cus_id int,--客户ID
		vd_id int,--供应商ID
		crc_id int, --币种ID
		exchange_rate float, --汇率
		remark nvarchar(500),--备注
		new_uid int,--新增人ID
		new_user nvarchar(50),--新增人名称
		new_date datetime,--新增日期
		update_uid int,--修改人ID
		update_user nvarchar(50),--修改人名称
		update_date datetime,--修改日期
		verify_uid int,--审核人ID
		verify_user nvarchar(50)--审核人名称
	);
	--与客户外键约束
	ALTER TABLE [dbo].[ARAP_CancelOrder]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_CancelOrder_Base_Customer] FOREIGN KEY([cus_id])
	REFERENCES [dbo].[Base_Customer] ([cus_id]);
	--与供应商外键约束
	ALTER TABLE [dbo].[ARAP_CancelOrder]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_CancelOrder_Base_Vendor] FOREIGN KEY([vd_id])
	REFERENCES [dbo].[Base_Vendor] ([vd_id]);
	--与币种外键约束
	ALTER TABLE [dbo].[ARAP_CancelOrder]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_CancelOrder_Base_Currency] FOREIGN KEY([crc_id])
	REFERENCES [dbo].[Base_Currency] ([crc_id]);
	--与新增用户外键约束
	ALTER TABLE [dbo].[ARAP_CancelOrder]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_CancelOrder_System_Member_New] FOREIGN KEY([new_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
	--与修改用户外键约束
	ALTER TABLE [dbo].[ARAP_CancelOrder]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_CancelOrder_System_Member_Update] FOREIGN KEY([update_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
end
/**/GO

--其它应收应付单主表
if object_id('ARAP_OtherRP') is null
begin
	CREATE TABLE ARAP_OtherRP(
		orp_id int identity(1,1) PRIMARY KEY,--应收应付单主表ID
		rp_flag nvarchar(2),--收付标志:AR应收、AP应付
		order_number nvarchar(50) UNIQUE,--单据号
		new_uid int,--新增人ID
		new_user nvarchar(50),--新增人名称
		new_date datetime,--新增日期
		update_uid int,--修改人ID
		update_user nvarchar(50),--修改人名称
		update_date datetime,--修改日期
		verify_uid int,--审核人ID
		verify_user nvarchar(50),--审核人名称
		cus_id int,--客户ID
		vd_id int,--供应商ID
		dep_id int,--部门ID
		ele_id int,--业务员ID
		crc_id int, --币种ID
		exchange_rate float, --汇率
		tax_rate float,--税率
		total_price float, --无税金额
		tax_total_price float,--含税金额
		nat_total_price float,--本币金额
		nat_tax_total_price float,--本币含税金额
		from_id int,--来源单据ID
		from_type varchar(50),--单据来源
		remark nvarchar(500),--备注
		sdefine1 nvarchar(300),--字符自定义项1
		sdefine2 nvarchar(300),--字符自定义项2
		sdefine3 nvarchar(300),--字符自定义项3
		sdefine4 nvarchar(300),--字符自定义项4
		sdefine5 nvarchar(300),--字符自定义项5
		ndefine1 float,--数值自定义项1
		ndefine2 float,--数值自定义项2
		ndefine3 float,--数值自定义项3
		ndefine4 float,--数值自定义项4
		ndefine5 float --数值自定义项5
	);
	--与客户外键约束
	ALTER TABLE [dbo].[ARAP_OtherRP]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_OtherRP_Base_Customer] FOREIGN KEY([cus_id])
	REFERENCES [dbo].[Base_Customer] ([cus_id]);
	--与供应商外键约束
	ALTER TABLE [dbo].[ARAP_OtherRP]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_OtherRP_Base_Vendor] FOREIGN KEY([vd_id])
	REFERENCES [dbo].[Base_Vendor] ([vd_id]);
	--与部门外键约束
	ALTER TABLE [dbo].[ARAP_OtherRP]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_OtherRP_Base_Department] FOREIGN KEY([dep_id])
	REFERENCES [dbo].[Base_Department] ([dep_id]);
	--与员工外键约束
	ALTER TABLE [dbo].[ARAP_OtherRP]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_OtherRP_Base_Employee] FOREIGN KEY([ele_id])
	REFERENCES [dbo].[Base_Employee] ([ele_id]); 
	--与币种外键约束
	ALTER TABLE [dbo].[ARAP_OtherRP]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_OtherRP_Base_Currency] FOREIGN KEY([crc_id])
	REFERENCES [dbo].[Base_Currency] ([crc_id]);
	--与新增用户外键约束
	ALTER TABLE [dbo].[ARAP_OtherRP]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_OtherRP_System_Member_New] FOREIGN KEY([new_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
	--与修改用户外键约束
	ALTER TABLE [dbo].[ARAP_OtherRP]  WITH CHECK ADD  CONSTRAINT [FK_ARAP_OtherRP_System_Member_Update] FOREIGN KEY([update_uid])
	REFERENCES [dbo].[System_Member] ([uid]);
end
/**/GO

--drop TABLE Sale_Cart
--购物车表
if object_id('Sale_Cart') is null
begin
	CREATE TABLE Sale_Cart(
		cart_id int identity(1,1) PRIMARY KEY,--主键ID
		uid int,--用户ID		
		i_id int,--存货id
		free1 nvarchar(100),--自由项1
		free2 nvarchar(100),--自由项2
		tax_unit_price float,--单价
		quantity float,--数量
		num float,--件数
		convert_rate float--换算率
	)
	--与存货外键约束
	ALTER TABLE [dbo].[Sale_Cart]  WITH CHECK ADD  CONSTRAINT [FK_Sale_Cart_Base_Inventory] FOREIGN KEY([i_id])
	REFERENCES [dbo].[Base_Inventory] ([i_id]);
	--与修改用户外键约束
	ALTER TABLE [dbo].[Sale_Cart]  WITH CHECK ADD  CONSTRAINT [FK_Sale_Cart_System_Member] FOREIGN KEY([uid])
	REFERENCES [dbo].[System_Member] ([uid]);
end
/**/GO

--客户产品对照表
if object_id('Base_CusInv') is null
begin
	CREATE TABLE Base_CusInv(
		ci_id int identity(1,1) PRIMARY KEY,--主键ID
		cus_id int not null,--客户ID
		i_id int not null,--存货id
		free1 nvarchar(100),--自由项1
		free2 nvarchar(100),--自由项2
		cus_i_name nvarchar(50),--存货别名
		cus_price float,--客户报价
		crc_id int,--币种
		tax_rate float,--税率
		sdefine1 nvarchar(300),--字符自定义项1
		sdefine2 nvarchar(300),--字符自定义项2
		sdefine3 nvarchar(300),--字符自定义项3
		sdefine4 nvarchar(300),--字符自定义项4
		sdefine5 nvarchar(300),--字符自定义项5
		ndefine1 float,--数值自定义项1
		ndefine2 float,--数值自定义项2
		ndefine3 float,--数值自定义项3
		ndefine4 float,--数值自定义项4
		ndefine5 float --数值自定义项5
	);
	--与客户外键约束
	ALTER TABLE [dbo].[Base_CusInv]  WITH CHECK ADD  CONSTRAINT [FK_Base_CusInv_Base_Customer] FOREIGN KEY([cus_id])
	REFERENCES [dbo].[Base_Customer] ([cus_id]);
	--与存货外键约束
	ALTER TABLE [dbo].[Base_Inventory]  WITH CHECK ADD  CONSTRAINT [FK_Base_CusInv_Base_Inventory] FOREIGN KEY([i_id])
	REFERENCES [dbo].[Base_Inventory] ([i_id]);
end
/**/GO

--部门权限表
if object_id('System_DepAuth') is null
begin
	CREATE TABLE System_DepAuth(
		da_id int IDENTITY(1,1) PRIMARY KEY, --主键id
		uid int NOT NULL,--用户ID
		dep_id int NOT NULL--部门ID
	);
	--与用户外键约束
	ALTER TABLE [dbo].[System_DepAuth]  WITH CHECK ADD  CONSTRAINT [FK_System_DepAuth_System_Member] FOREIGN KEY([uid])
	REFERENCES [dbo].[System_Member] ([uid]);
	--与部门外键约束
	ALTER TABLE [dbo].[System_DepAuth]  WITH CHECK ADD  CONSTRAINT [FK_System_DepAuth_Base_Department] FOREIGN KEY([dep_id])
	REFERENCES [dbo].[Base_Department] ([dep_id]);
	end
/**/GO

--仓库权限表
if object_id('System_WhAuth') is null
begin
	CREATE TABLE System_WhAuth(
		wa_id int IDENTITY(1,1) PRIMARY KEY, --主键id
		uid int NOT NULL,--用户ID
		wh_id int NOT NULL--仓库ID
	);
	--与用户外键约束
	ALTER TABLE [dbo].[System_WhAuth]  WITH CHECK ADD  CONSTRAINT [FK_System_WhAuth_System_Member] FOREIGN KEY([uid])
	REFERENCES [dbo].[System_Member] ([uid]);
	--与仓库外键约束
	ALTER TABLE [dbo].[System_WhAuth]  WITH CHECK ADD  CONSTRAINT [FK_System_WhAuth_Base_Warehouse] FOREIGN KEY([wh_id])
	REFERENCES [dbo].[Base_Warehouse] ([wh_id]);
	end
/**/GO

--促销套餐主表
if object_id('Sale_GoodsCombo') is null
begin
	CREATE TABLE Sale_GoodsCombo(
		gc_id int identity(1,1) PRIMARY KEY,--套餐主表ID
		order_number nvarchar(50) UNIQUE,--套餐编号
		gc_name nvarchar(50),--套餐名称
		begin_date datetime,--开始日期
		end_date datetime,--结束日期
		wh_id int,--仓库id
		is_stop int,--是否停用
		verify_uid int,--审核人ID
		verify_user nvarchar(50),--审核人名称
		info nvarchar(1000),--套餐摘要
		remark nvarchar(500)--备注
	);
end
/**/GO

--套餐子表
if object_id('Sale_GoodsCombos') is null
begin
	CREATE TABLE Sale_GoodsCombos(
		gcs_id int identity(1,1) PRIMARY KEY,--子表ID
		gc_id int not null,--主表ID
		i_id int not null,--存货id
		free1 nvarchar(100),--自由项1
		free2 nvarchar(100),--自由项2
		tax_unit_price float, --含税单价
		tax_total_price float,--含税金额
		quantity float,--数量
		num float,--件数
		convert_rate float,--换算率
		d_remark nvarchar(300),--备注
		is_main int--主商品
	);
	--与存货档案外键约束
	ALTER TABLE [dbo].[Sale_GoodsCombos]  WITH CHECK ADD  CONSTRAINT [FK_Sale_GoodsCombos_Base_Inventory] FOREIGN KEY([i_id])
	REFERENCES [dbo].[Base_Inventory] ([i_id]);
	--与主表外键约束
	ALTER TABLE [dbo].[Sale_GoodsCombos]  WITH CHECK ADD  CONSTRAINT [FK_Sale_GoodsCombos_Sale_GoodsCombo] FOREIGN KEY([gc_id])
	REFERENCES [dbo].[Sale_GoodsCombo] ([gc_id]);
end
/**/GO

--发货单表体增加促销套餐子表id字段
if not exists(select * from syscolumns where id=object_id('Sale_DeliveryOrders') and name='gcs_id')
begin
	alter table Sale_DeliveryOrders add gcs_id int;
	--与套餐子表外键约束
	ALTER TABLE Sale_DeliveryOrders  WITH CHECK ADD  CONSTRAINT FK_Sale_DeliveryOrders_Sale_GoodsCombos FOREIGN KEY(gcs_id)
	REFERENCES Sale_GoodsCombos (gcs_id);
end
/**/GO

