1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
| -- н¨Êý¾Ý¿â
| 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 --¸üÐÂÈË
|
| )
|
|
