-- ÐÂ½¨Êý¾Ý¿â
create database WMS_V01
go
use WMS_V01
delete from SysRoleRight where RoleNo  = '1000'
select * from SysMaterials where mate.IsDel = '0' and Id = 6
select * from SysRoleRight where Isdel = '0' and RoleNo = (select RoleNo from SysRoles where IsDel = '0' and Id = 7)
-- ÐÂ½¨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	-- ¸üÐÂÈË
);
select * from SysStorageLocat where IsDel = '0' and Layer = 1 and WareHouseNo = 'W01' and Row >= 1 and Row <= 10 and Column >= 1 and  Column <= 10
-- ÐÂ½¨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	-- ¸üÐÂÈË
)

select * from SysStorageLocat where IsDel = '0' and row = '1' and WareHouseNo = 'w01' order by [Layer],[column]
select * from SysStorageLocat where IsDel = '0' and row = '1' and WareHouseNo = 'w03' order by [Layer],[Column]

select Depth from SysStorageLocat where IsDel = '0' and WareHouseNo = 'w03' group by Depth order by Depth

select TOP 1 WareHouseNo from SysWareHouse


-- ÐÂ½¨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	-- ¸üÐÂÈË
)
select * from SysFunctionMenu where IsDel = '0' or ParentNo = ''
select * from SysRoleRight


select userinfo.UserNo,userinfo.UserName,userinfo.RealName,userinfo.Status,userinfo.Sex,userinfo.Nationality,userinfo.Paper,userinfo.Phone,userinfo.Email,userinfo.Addres,userinfo.LoginTime,userinfo.SetPasswordTime,role.RoleNo,role.RoleName,menu.MenuNo,menu.MenuName,menu.ParentNo,menu.Ord,menu.Url,menu.level from SysUserInfor userinfo join SysRoles role on userinfo.RoleNo = role.RoleNo join SysRoleRight roleright on roleright.RoleNo = role.RoleNo join SysFunctionMenu menu on roleright.MenuNo = menu.MenuNo where role.Id = 7
select * from SysFunctionMenu where IsDel = '0' and ParentNo in (2100,2200,2300,2400,2500,2600)

select * from SysRoleRight WHERE  RoleNo = '1000'
select * from SysRoleRight roleright left join SysRoles role on roleright.RoleNo = role.RoleNo where role.Id = 7
@RoleNo, @MenuNo, @IsDel, @CreateTime, @CreateUser, null, null
insert into SysRoleRight values('admin','1000','0','',1,'',0)
insert into SysRoleRight values('admin','1100','0','',1,'',0)
insert into SysRoleRight values('admin','1200','0','',1,'',0)
insert into SysRoleRight values('admin','1300','0','',1,'',0)
insert into SysRoleRight values('admin','1400','0','',1,'',0)

insert into SysRoleRight values('admin','2000','0','',1,'',0)
insert into SysRoleRight values('admin','2100','0','',1,'',0)
	insert into SysRoleRight values('admin','2101','0','',1,'',0)
insert into SysRoleRight values('admin','2200','0','',1,'',0)
insert into SysRoleRight values('admin','2300','0','',1,'',0)
insert into SysRoleRight values('admin','2400','0','',1,'',0)
insert into SysRoleRight values('admin','2500','0','',1,'',0)
insert into SysRoleRight values('admin','2600','0','',1,'',0)
select* from SysRoleRight
insert into SysRoleRight values('admin','2301','0','',1,'',0)
insert into SysRoleRight values('admin','2302','0','',1,'',0)
select * from SysFunctionMenu where IsDel = '0'and MenuName like '%ÉèÖÃ%' or ParentNo like '%20%' or MenuNo like '20%'
select * from SysFunctionMenu where IsDel = '0' or ParentNo like '30%' or MenuNo like '30%'
-- ÐÂ½¨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	-- ¸üÐÂÈË
)
select * from SysFunctionMenu where IsDel = '3' and IsEnable = '0'

select * from SysFunctionMenu where IsDel = '0'
@menuno,@menuname,@parentno,@ord,@url,@level,@isenable,@demo,@isdel,@createtime,@createuser,@updatetime,@updateuser
 MenuName = @menuname, ParentNo = @parentno, Ord = @ord, Url = @url, Level = @level, IsEnable = @isenable, Demo = @demo, UpdateTime = @updatetime, UpdateUser = @updateuser
insert into SysFunctionMenu values('1000','Éè±¸¹ÜÀí','','','','0','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('1100','²Ö¿â¹ÜÀí','1000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('1200','ÇøÓò¹ÜÀí','1000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('1300','ÈÎÎñÒì³£¹ÜÀí','1000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('1400','µ¥»úÍ¬²½','1000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)

insert into SysFunctionMenu values('2000','ÏµÍ³ÉèÖÃ','','','','0','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('2100','²Ëµ¥¹ÜÀí','2000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)
	insert into SysFunctionMenu values('2101','Ìí¼Ó²Ëµ¥','2100','','','2','0','²âÊÔÊý¾Ý','0','',1,'',0)

insert into SysFunctionMenu values('2200','ÓÃ»§¹ÜÀí','2000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('2300','ÌõÂë¹ÜÀí','2000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('2400','½ÇÉ«¹ÜÀí','2000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('2500','Êý¾Ý×Öµä','2000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('2600','²Ù×÷ÈÕÖ¾','2000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)

insert into SysFunctionMenu values('3000','²Ö¿âÉèÖÃ','','','','0','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('3100','²Ö¿â¹ÜÀí','3000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('3200','ÇøÓò¹ÜÀí','3000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('3300','ÏïµÀ¹ÜÀí','3000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('3400','´¢Î»¹ÜÀí','3000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('3500','Òì³£´¦Àí','3000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('3600','ÌõÂë¹ÜÀí','3000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('3700','´¢Î»Í¼Àý','3000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('3800','²Ù×÷ÈÕÖ¾','3000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)

insert into SysFunctionMenu values('4000','»ù´¡ÐÅÏ¢','','','','0','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('4100','ÎïÁÏ¹ÜÀí','4000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('4200','°ü×°¹ÜÀí','4000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('4300','¼ÆÁ¿µ¥Î»','4000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('4400','¿Í»§¹ÜÀí','4000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('4500','ÎïÁ÷¹ÜÀí','4000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('4600','²Ù×÷ÈÕÖ¾','4000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)


insert into SysFunctionMenu values('5000','ÎïÁ÷¹ÜÀí','','','','0','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('5600','²Ù×÷ÈÕÖ¾','5000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('5500','ÎïÁ÷¹ÜÀí','5000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)
insert into SysFunctionMenu values('5600','²Ù×÷ÈÕÖ¾','5000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0)

update SysFunctionMenu set MenuName = '½ÇÉ«È¨ÏÞ' where MenuNo = '2300'
update SysFunctionMenu set MenuName = '¹¦ÄÜÉè¶¨' where MenuNo = '2400'
select * from SysFunctionMenu where IsDel = '0' 
delete from SysFunctionMenu where id in (58)
update SysFunctionMenu set Url = 'SystemSettings/Menumanagement.html' where MenuNo = '2100'
update SysFunctionMenu set Url = 'SystemSettings/UserManage.html',upda
select * from SysRoleRight where Isdel = '0'

-- ÐÂ½¨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(Now())),	--´´½¨ÈÕÆÚ
	CreateUser	int not null,	--´´½¨ÈË
	UpdateTime	dateTime,	--¸üÐÂÈÕÆÚ
	UpdateUser	int	--¸üÐÂÈË
)


select dep.*,user1.UserName CreateUserName from SysDepartment dep left join SysUserInfor user1 on dep.CreateUser = user1.id where dep.IsDel = '0'

select * from SysDepartment where IsDel = '0'
select * from SysDepartment
update SysDepartment set DepartmentNo = '@departmentno',DepartmentName='@departmentname',Demo = '@demo', IsDel = '@isdel',UpdateTime = '@updatetime',UpdateUser = updateuser where id = @id
insert into SysDepartment values('DepartmentNo','DepartmentName','Demo','IsDel','CreateTime',CreateUser,'UpdateTime',UpdateUser)
Fun001
-- ÐÂ½¨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	--¸üÐÂÈË
)

select * from SysFunctionMenu  order by MenuNo
-- ÐÂ½¨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	--¸üÐÂÈË
)

update SysDictionary set IsDel = @de,UpdateTime = @upttime,UpdateUser = @uptuser where id = @dicid
,@de,@upttime datetime,@uptuser int,@dicid int',@de='1',@upttime='2023-03-31 11:26:48.373,@uptuser=1,@dicid=6

select * from SysDictionary
insert into SysDictionary values('1000','²âÊÔ','','','0','0','0','0','0',getdate(),1,null,null)
insert into SysDictionary values('@dictno','@DictName','@parentno','@ord','@level','@ispublic','@isedit','@isadd','@isdel','@createtime',@createuser,'@updatetime',@updateuser)
update SysDictionary set IsDel = '0',UpdateTime = '',UpdateUser = 1 where id = 11
update SysDictionary set DictNo = '@dictno',DictName='@dictname',ParentNo='@parentno',Ord='@ord',Level='@level',IsPublic='@ispublic',IsEdit='@isedit',IsAdd='@isadd',IsDel = '@isdel',UpdateTime = '@updatetime',UpdateUser = updateuser where id = @id

select dic.*,user1.UserName CreateName from SysDictionary dic left join SysUserInfor user1 on dic.CreateUser = user1.Id
select dic.*,user1.UserName CreateName from SysDictionary dic left join SysUserInfor user1 on dic.CreateUser = user1.Id where dic.IsDel = '0'
select * from SysDictionary
-- ÐÂ½¨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	--¸üÐÂÈË
)
select * from SysPackag
update SysPackag set PackagName = 'ºÐ' where id = 2
update SysMaterials set Origin= 'ERP' where id = 1
select mate.*,user1.UserName CreateUserName from SysMaterials mate left join SysUserInfor user1 on mate.CreateUser = user1.Id
insert into SysMaterials values('1000','²âÊÔÏÔÊ¾','0.0','0','0','1','0','text','text','text','0',1.0,30,20.00,'²âÊÔÊý¾Ý','0',GETDATE(),1,'',0)
update SysMaterials set IsDel = @isdel, UpdateTime = @updatetime, UpdateUser = @updateuser where Id = @id

select menu1.Id,menu1.MenuNo,menu1.MenuName,menu2.MenuName ParentName,menu1.ParentNo,menu1.Ord,menu1.Url,menu1.level,menu1.IsEnable,menu1.Demo,menu1.IsDel,menu1.CreateTime,menu1.CreateUser,menu1.UpdateTime,menu1.UpdateUser,user1.UserName CreateUserName from SysFunctionMenu menu1 left join sysfunctionmenu menu2 on menu1.parentNo = menu2.MenuNo join SysUserInfor user1 on menu1.CreateUser = user1.id  where menu1.IsDel = '0' and menu1.IsEnable = '0'  and menu1.MenuName like '%É¾³ý%' and menu1.ParentNo = '2000' or menu1.ParentNo in (select MenuNo from SysFunctionMenu where ParentNo = '2000') and menu1.MenuName like '%É¾³ý%'

select * from SysFunctionMenu where isdel = '0'


-- ÐÂ½¨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	--¸üÐÂÈË
)
select mate.*,user1.RealName CreateUserName,pack.PackagName PackagName,unit.UnitName UnitName from SysMaterials mate left join SysUserInfor user1 on mate.CreateUser = user1.Id left join SysPackag pack on mate.PackagNo = pack.PackagNo left join SysUnit unit on mate.UnitNo = unit.UnitNo where mate.IsDel = '0'
select * from SysPackag
-- ÐÂ½¨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	--¸üÐÂÈË
)

create table TableName
(
	Id				int primary key identity not null,		--Ö÷¼üID ×ÔÔö
	ExceptionNo		varchar(20),							--Òì³£ºÅ
	Type			varchar(3),								--Òì³£ÀàÐÍ
	PalletNo		varchar(20),							-- ÍÐÅÌºÅ
	ExcLocatNo		varchar(20),							--Òì³£´¢Î»
	OrderNo			varchar(20),							--¹ØÁªµ¥ºÅ
	TaskNo			varchar(20),							--¹ØÁªÈÎÎñºÅ
	Status			varchar(3),								--×´Ì¬
	Text			nvarchar(100),							--´¦ÀíÃèÊö

	Demo			nvarchar(100),							--±¸×¢
	IsDel			varchar(3) not null default('0'),		--ÊÇ·ñÉ¾³ý
	CreateTime		dateTime not null default(GETDATE()),	--´´½¨ÈÕÆÚ
	CreateUser		int not null,							--´´½¨ÈË
	UpdateTime		dateTime,								--¸üÐÂÈÕÆÚ
	UpdateUser		int										--¸üÐÂÈË

)































































































































