--´´½¨Êý¾Ý¿â
--create database WMS_V01

--Óû§¡¢½ÇÉ«¡¢½ÇɫȨÏÞ¡¢²Ëµ¥¡¢°ü×°¡¢   ²¿ÃÅ¡¢Êý¾Ý×ֵ䡢¹¦ÄÜÉ趨¡¢ÎïÁÏ¡¢
--²Ö¿â¡¢ÇøÓò¡¢´¢Î»¡¢ÏïµÀ¡¢Òì³£´¦Àí¡¢ÌõÂë¡¢ÍÐÅ̸ú×ÙÈÕÖ¾¡¢¿Í»§¡¢ÎïÁ÷ÐÅÏ¢¡¢²Ù×÷ÈÕÖ¾¡¢¼ÆÁ¿µ¥Î»¡¢
use WMS_V02
update 
--SysWareHouse (²Ö¿â±í) set identity_insert tableName ON  (ON/OFF)
create table SysWareHouse(
	Id int primary key identity not null,
	WareHouseNo varchar(20) not null,
	WareHouseName varchar(20) not null,
	Type varchar(3) not null,--0£ºÁ¢Ìå¿â  1£ºÃܼ¯¿â
	Temperature varchar(3) , --0: ³£Î¿â 1£ºµÍοâ 3:¸ÉÔïͨ·ç
	Row int ,
	[Column] int,
	Layer int,
	IsDel varchar(3) not null default(0),
	CreateTime dateTime default(getdate()) not null,
	CreateUser int not null,
	UpdateTime dateTime ,
	UpdateUser int 
)
--SysStorageArea (¿âÇøÓò±í) set identity_insert tableName ON  (ON/OFF)
create table SysStorageArea(
	Id int primary key identity not null,
	AreaNo varchar(20) not null,
	AreaName nvarchar(20) not null,
	WareHouseNo varchar(20) not null,
	RoadwayNo varchar(20) ,
	Status varchar(3) not null, --0£ºÆôÓà  1:Í£ÓÃ
	Priority int not null, --1 2 3 4  1ÓÅÏȼ¶×î¸ß
	Type varchar(3) not null, --1£º´æ´¢Çø 2£ºÖÜתÇø 3£º¼ìÑéÇø 4 ¼ð»õÇø µÈ  or 1 Ô­ÁÏÇø 2 °ü²ÄÇø 3 ³ÉÆ·Çø 4£º¿ÕÍÐÇø
	Temperature varchar(3) , --0: ³£Î¿â 1£ºµÍοâ 3:¸ÉÔïͨ·ç
	
	IsDel varchar(3) not null default(0),
	CreateTime dateTime default(getdate()) not null,
	CreateUser int not null,
	UpdateTime dateTime ,
	UpdateUser int 
)
--SysStorageLocat (´¢Î»±í) set identity_insert tableName ON  (ON/OFF)
create table SysStorageLocat(
	Id int primary key identity not null,
	LocatNo varchar(20) not null,
	WareHouseNo varchar(20) not null,
	RoadwayNo varchar(20),
	AreaNo varchar(20) not null,
	Status varchar(3) not null,
	Flag varchar(3) not null,
	Depth varchar(3) not null,
	Row int not null,
	[Column] int not null,
	Layer int not null,
	Height int,
	[Weight] int,
	Temperature varchar(3),
	
	IsDel varchar(3) not null default(0),
	CreateTime dateTime not null default(getdate()),
	CreateUser int not null,
	UpdateTime dateTime ,
	UpdateUser int 
)
--SysStorageRoadway (ÏïµÀ±í) set identity_insert tableName ON  (ON/OFF)
create table SysStorageRoadway(
	Id int primary key identity not null,
	RoadwayNo varchar(20) not null,
	WareHouseNo varchar(20) not null,
	AreaNo varchar(20),
	Status varchar(3) not null,
	Priority int not null,
	Type varchar(3),
	Temperature varchar(3),
	
	IsDel varchar(3) not null default(0),
	CreateTime dateTime not null default(getdate()),
	CreateUser int not null,
	UpdateTime dateTime ,
	UpdateUser int 
)
--SysException (Òì³£´¦Àí±í) set identity_insert tableName ON  (ON/OFF)
create table SysException(
	Id int primary key identity not null,
	ExceptionNo	varchar(20),
	Type varchar(3),
	PalletNo varchar(20),
	ExcLocatNo varchar(20),
	OrderNo  varchar(20),
	TaskNo varchar(20),
	Status varchar(3),
	Text nvarchar(100),

	IsDel varchar(3) not null default(0),
	CreateTime dateTime not null default(getdate()),
	CreateUser int not null,
	UpdateTime dateTime ,
	UpdateUser int 
)
--SysPallets (ÌõÂë¹ÜÀí) set identity_insert tableName ON  (ON/OFF)
create table SysPallets(
	Id int primary key identity not null,
	PalletNo varchar(20) not null,
	Type varchar(3),
	Status varchar(3),
	LastUse	dateTime,

	IsDel varchar(3) not null default(0),
	CreateTime dateTime not null default(getdate()),
	CreateUser int not null,
	UpdateTime dateTime ,
	UpdateUser int 
)
--LogPalletTrack  (ÍÐÅ̸ú×ÙÈÕÖ¾±í) set identity_insert tableName ON  (ON/OFF)
create table LogPalletTrack(
	Id int primary key identity not null,
	PalletNo varchar(20) not null,
	OrderNo	varchar(20) not null,
	Msg	nvarchar(100),
	
	IsDel varchar(3) not null default(0),
	CreateTime dateTime not null default(getdate()),
	CreateUser int not null,
	UpdateTime dateTime ,
	UpdateUser int 
)
--SysCustomer  (¿Í»§±í) set identity_insert tableName ON  (ON/OFF)
create table SysCustomer(
	Id int primary key identity not null,
	CustomerNo varchar(20) ,
	CustomerName varchar(50),
	Type int default(0),
	Address nvarchar(100),
	LinkMan nvarchar(20),
	Phone varchar(20),
	BankAccount varchar(30), --ÒøÐп¨ºÅ
	CreditRating varchar(3), --ÐÅÓõȼ¶

	Demo nvarchar(100),
	
	IsDel varchar(3) not null default(0),
	CreateTime dateTime not null default(getdate()),
	CreateUser int not null,
	UpdateTime dateTime ,
	UpdateUser int 
)
--SysLogisticsInfo  (ÎïÁ÷ÐÅÏ¢±í) set identity_insert tableName ON  (ON/OFF)
create table SysLogisticsInfo(
	Id int primary key identity not null,
	CarrierName varchar(20),
	Address	varchar(100) ,
	LinkMan	nvarchar(20),
	Phone varchar(20),
	BankAccount varchar(30), --ÒøÐп¨ºÅ
	CreditRating varchar(3), --ÐÅÓõȼ¶
	LicensePlate varchar(10),
	Type varchar(3),

	Demo varchar(100),
	IsDel varchar(3) not null default(0),
	CreateTime dateTime not null default(getdate()),
	CreateUser int not null,
	UpdateTime dateTime ,
	UpdateUser int 
)
--LogOperationSys  (²Ù×÷ÈÕÖ¾±í) set identity_insert tableName ON  (ON/OFF)
create table LogOperationSys(
	Id int primary key identity not null,
	ParentNo varchar(20) ,
	MenuNo varchar(20),
	MenuName varchar(20),
	FkNo varchar(20),
	Type varchar(3),
	Msg nvarchar(100),


	IsDel varchar(3) not null default(0),
	CreateTime dateTime not null default(getdate()),
	CreateUser int not null,
	UpdateTime dateTime ,
	UpdateUser int 
)
--------------------------------------------------------------------------------------------------
-- н¨SysUserInfor (Óû§±í)
create table SysUserInfor
(
	Id				int primary key identity,	-- Ö÷¼üID ×ÔÔö
	UserNo			varchar(20) not null,		-- Óû§ºÅ
	UserName		nvarchar(20) not null,		-- µÇ¼Ãû³Æ
	RealName		nvarchar(20) not null,		-- ÕæʵÐÕÃû
	PassWord		varchar(50) not null,		-- ÃÜÂë MD5¼ÓÃÜ
	JobNo			varchar(20),				-- Ö°¹¤ºÅ
	Status			varchar(3) default(0),		-- ״̬ 0£ºÕý³£ 1£ºÍ£Óà 2£ºÀëÖ°
	Sex				varchar(10),				-- ÐÔ±ð
	Nationality		nvarchar(20),				-- ¹ú¼®
	Paper			varchar(20),				-- Ö¤¼þºÅ
	Phone			varchar(11),				-- ÊÖ»úºÅ
	Email			varchar(50),				-- ÓÊÏä
	Addres			nvarchar(100),				-- µØÖ·
	LoginTime		dateTime,					-- ×îºóµÇ¼ʱ¼ä
	SetPasswordTime	dateTime,					-- ÃÜÂëÉèÖÃʱ¼ä ÓÃÒÔÉèÖÃ30Ì쳬ʱ    ¹¦ÄÜÉ趨
									 
	RoleNo	varchar(20),	-- ½ÇÉ«ºÅ
	DepartmentNo	varchar(20),	-- ²¿ÃźÅ
	Demo	nvarchar(100),	-- ±¸×¢
	IsDel	varchar(3) not null default(0),	-- ÊÇ·ñɾ³ý 0:Õý³£  1:ÒÑɾ³ý
	CreateTime	dateTime not null default(getdate()),	-- ´´½¨ÈÕÆÚ
	CreateUser	int not null,	-- ´´½¨ÈË
	UpdateTime	dateTime,	-- ¸üÐÂÈÕÆÚ
	UpdateUser	int	-- ¸üÐÂÈË
)

-- н¨SysRoles (½ÇÉ«±í)
create table SysRoles
(
	Id	int primary key identity,	-- Ö÷¼üID ×ÔÔö
	RoleNo	varchar(20) not null,	-- ½ÇÉ«ºÅ
	RoleName	nvarchar(20) not null,	-- ½ÇÉ«Ãû³Æ
		
	Demo	nvarchar(100),	-- ±¸×¢
	IsDel	varchar(3) not null default(0),	-- ÊÇ·ñɾ³ý 0:Õý³£  1:ÒÑɾ³ý
	CreateTime	dateTime not null default(getdate()),	-- ´´½¨ÈÕÆÚ
	CreateUser	int not null,	-- ´´½¨ÈË
	UpdateTime	dateTime,	-- ¸üÐÂÈÕÆÚ
	UpdateUser	int	-- ¸üÐÂÈË
)

-- н¨SysRoleRight (½ÇɫȨÏÞ±í)
create table SysRoleRight
(
	Id	int primary key identity,	-- Ö÷¼üID ×ÔÔö
	RoleNo	varchar(20) not null,	-- ½ÇÉ«ºÅ
	MenuNo	varchar(20) not null,	-- ²Ëµ¥ºÅ

	IsDel	varchar(3) not null default(0),	-- ÊÇ·ñɾ³ý 0:Õý³£  1:ÒÑɾ³ý
	CreateTime	dateTime not null default(getdate()),	-- ´´½¨ÈÕÆÚ
	CreateUser	int not null,	-- ´´½¨ÈË
	UpdateTime	dateTime,	-- ¸üÐÂÈÕÆÚ
	UpdateUser	int	-- ¸üÐÂÈË
)


-- н¨SysFunctionMenu (¹¦Äܲ˵¥±í)
create table SysFunctionMenu
(
	Id	int primary key identity,	-- Ö÷¼üID ×ÔÔö
	MenuNo	varchar(20) not null,	-- ²Ëµ¥ºÅ
	MenuName	nvarchar(20),	-- Ãû³Æ
	ParentNo	varchar(20),	-- ¸¸¼¶²Ëµ¥ºÅ
	Ord	varchar(5),	-- ÏÔʾ˳Ðò
	Url	varchar(100),	-- Ò³ÃæµØÖ·
	level	varchar(3) not null,	-- ²ã¼¶ 0£ºÄ£¿é 1£ºÒ³Ãæ 2£º°´Å¥
	IsEnable	varchar(3) not null default(0),	-- ÊÇ·ñ¿ÉÓà 0£º¿ÉÓà 1£º²»¿ÉÓã¨ÏîÄ¿¿´²»µ½µÄÒ³ÃæÒþ²Øµô£©
	
	Demo	nvarchar(100),	-- ±¸×¢
	IsDel	varchar(3) not null default(0),	-- ÊÇ·ñɾ³ý 0:Õý³£  1:ÒÑɾ³ý
	CreateTime	dateTime not null default(getdate()),	-- ´´½¨ÈÕÆÚ
	CreateUser	int not null,	-- ´´½¨ÈË
	UpdateTime	dateTime,	-- ¸üÐÂÈÕÆÚ
	UpdateUser	int	-- ¸üÐÂÈË
)

-- н¨SysDepartment (²¿Ãűí)
create table SysDepartment
(
	Id	int primary key identity,	--Ö÷¼üID ×ÔÔö
	DepartmentNo	varchar(20) not null,	--²¿ÃźÅ
	DepartmentName nvarchar(20) not null,	--²¿ÃÅÃû³Æ

	Demo	nvarchar(100),	--±¸×¢
	IsDel	varchar(3) not null default(0),	--ÊÇ·ñɾ³ý 0:Õý³£  1:ÒÑɾ³ý
	CreateTime	dateTime not null default(getdate()),	--´´½¨ÈÕÆÚ
	CreateUser	int not null,	--´´½¨ÈË
	UpdateTime	dateTime,	--¸üÐÂÈÕÆÚ
	UpdateUser	int	--¸üÐÂÈË
)

-- н¨SysFunSetting (¹¦ÄÜÉ趨±í)
create table SysFunSetting
(
	Id	int primary key identity,	--Ö÷¼üID ×ÔÔö
	FunSetNo	varchar(20) not null,	--¹¦ÄÜÉèÖñàºÅ
	FunSetName	nvarchar(20),	--¹¦ÄÜÃû³Æ
	SetValue	nvarchar(20),	--É趨ֵ
	FunText	nvarchar(100),	--¹¦ÄÜÃèÊö
	IsEnable	varchar(3) not null,	--ÊÇ·ñ¿ªÆô NO:¿ªÆô  OFF:¹Ø±Õ
	Ord	varchar(5),	--ÏÔʾ˳Ðò
	GroupNo	varchar(20),	--×éºÅ ͬһ×éÖ»¿É¿ªÆôÒ»¸ö¹¦ÄÜ¡£ ×éºÅµÈÓÚ×îСµÄ¹¦ÄܱàºÅ£ºÀý:Fun001 Fun002 Fun003 ×éºÅΪ:Fun001

	IsDel	varchar(3) not null default(0),	--ÊÇ·ñɾ³ý 0:Õý³£  1:ÒÑɾ³ý
	CreateTime	dateTime not null default(getdate()),	--´´½¨ÈÕÆÚ
	CreateUser	int not null,	--´´½¨ÈË
	UpdateTime	dateTime,	--¸üÐÂÈÕÆÚ
	UpdateUser	int	--¸üÐÂÈË
)

-- н¨SysDictionary (Êý¾Ý×Öµä±í)
create table SysDictionary
(
	Id	int primary key identity,	--Ö÷¼üID ×ÔÔö
	DictNo	varchar(20),	--񅧏
	DictName	nvarchar(20),	--×ÖµäÃû³Æ
	ParentNo	varchar(20),	--¸¸¼¶×ÖµäºÅ
	Ord	varchar(5),	--ÏÔʾ˳Ðò
	level	varchar(3) not null,	--²ã¼¶ 0£º¸ùĿ¼ 1£ºÏ¼¶Ä¿Â¼
	IsPublic	varchar(3),	--ÊÇ·ñ¹«¿ª ÊÇ·ñÈÃÓû§¿´µ½    0£º¹«¿ª 1£º²»¹«¿ª
	IsEdit	varchar(3),	--ÔÊÐí±à¼­ ÊÇ·ñÔÊÐíÓû§±à¼­  0£ºÔÊÐí 1£º²»ÔÊÐí
	IsAdd	varchar(3),	--ÔÊÐíÔö¼Ó ÊÇ·ñÔÊÐíÓû§ÐÂÔö  0£ºÔÊÐí 1£º²»ÔÊÐí

	IsDel	varchar(3) not null default(0),	--ÊÇ·ñɾ³ý 0:Õý³£  1:ÒÑɾ³ý
	CreateTime	dateTime not null default(getdate()),	--´´½¨ÈÕÆÚ
	CreateUser	int not null,	--´´½¨ÈË
	UpdateTime	dateTime,	--¸üÐÂÈÕÆÚ
	UpdateUser	int	--¸üÐÂÈË
)

-- н¨SysMaterials (ÎïÁϱàÂë±í) 
create table SysMaterials
(
	Id	int primary key identity,	--Ö÷¼üID ×ÔÔö
	SkuNo	varchar(20) not null,	--ÎïÁϺÅ
	SkuName	nvarchar(20) not null,	--ÎïÁÏÃû³Æ
	Standard	nvarchar(50),	--¹æ¸ñ
	AuditStatusNo	varchar(2),	--ÉóºË״̬ 0:´ýÉóºË  9:ÒÑͨ¹ý£»1-8Óû§¿É×Ô¶¨Òå 
	Type	varchar(20),	--ÀàÐÍ 0£ºÔ­ÁÏ 1£º°ü²Ä 2£º³ÉÆ· 3£ººÄ²Ä 4£º°ë³ÉÆ·
	IsControlled	varchar(3) default(1),	--ÊÇ·ñÊÜ¿Ø 0£ºÊÜ¿Ø 1£º·ÇÊÜ¿Ø
	IsInspect	varchar(3) default(0),	--ÊÇ·ñÃâ¼ì 0£º·ñ 1£ºÊÇ
	Origin	nvarchar(20),	--À´Ô´
	UnitNo	varchar(20),	--µ¥Î»
	PackagNo	varchar(20),	--°ü×°
	Environment	varchar(20),	--´æ´¢»·¾³ 0£º³£Î 1£ºµÍΠ3£º¸ÉÔïͨ·ç 
	Weight	decimal(6,1),	--ÀíÂÛÖØÁ¿
	Warranty	int,	--±£ÖÊÆÚ
	Price	decimal(8,2),	--ÀíÂÛµ¥¼Û

	Demo	nvarchar(100),	--±¸×¢
	IsDel	varchar(3) not null default(0),	--ÊÇ·ñɾ³ý 0:Õý³£  1:ÒÑɾ³ý
	CreateTime	dateTime not null default(getdate()),	--´´½¨ÈÕÆÚ
	CreateUser	int not null,	--´´½¨ÈË
	UpdateTime	dateTime,	--¸üÐÂÈÕÆÚ
	UpdateUser	int	--¸üÐÂÈË
)

-- н¨SysPackag (°ü×°±í)
create table SysPackag
(
	Id		int primary key identity,	--Ö÷¼üID ×ÔÔö
	PackagNo	varchar(20) not null,	-- °ü×°ºÅ
	PackagName	nvarchar(20),	-- °ü×°Ãû³Æ
	Level		int,	-- °ü×°¼¶Êý ·¶Î§ 2-5
	L1Num		int,	-- Ò»¼¶°ü×°-ÊýÁ¿
	L1Name		nvarchar(20),	--Ò»¼¶°ü×°-ÃèÊö
	L2Num		int,	--¶þ¼¶°ü×°-ÊýÁ¿
	L2Name		nvarchar(20),	--¶þ¼¶°ü×°-ÃèÊö
	L3Num		int,	--Èý¼¶°ü×°-ÊýÁ¿
	L3Name		nvarchar(20),	--Èý¼¶°ü×°-ÃèÊö
	L4Num		int,	--Ëļ¶°ü×°-ÊýÁ¿
	L4Name		nvarchar(20),	--Ëļ¶°ü×°-ÃèÊö
	L5Num		int,	--ÆäËû°ü×°-ÊýÁ¿
	L5Name		nvarchar(20),	--ÆäËû°ü×°-ÃèÊö


	IsDel	varchar(3) not null default(0),	--ÊÇ·ñɾ³ý 0:Õý³£  1:ÒÑɾ³ý
	CreateTime	dateTime not null default(getdate()),	--´´½¨ÈÕÆÚ
	CreateUser	int not null,	--´´½¨ÈË
	UpdateTime	dateTime,	--¸üÐÂÈÕÆÚ
	UpdateUser	int	--¸üÐÂÈË
)


-- н¨SysUnit (¼ÆÁ¿µ¥Î»)
create table SysUnit
(
	Id	int primary key identity,	--Ö÷¼üID ×ÔÔö
	UnitNo	varchar(20) not null,	--µ¥Î»±àºÅ
	UnitName	nvarchar(20),	--µ¥Î»Ãû³Æ
	Abbrev	varchar(3),	--Ëõд Kg t 
	

	IsDel	varchar(3) not null default(0),	--ÊÇ·ñɾ³ý 0:Õý³£  1:ÒÑɾ³ý
	CreateTime	dateTime not null default(getdate()),	--´´½¨ÈÕÆÚ
	CreateUser	int not null,	--´´½¨ÈË
	UpdateTime	dateTime,	--¸üÐÂÈÕÆÚ
	UpdateUser	int	--¸üÐÂÈË
)