truncate table sysmenu;
INSERT INTO [sysmenu] ([mid],[msort],[pid],[name],[icon],[type],[url],[status],[memo]) VALUES (N'101',N'1',N'0', N'basic',N'icon-cog',N'0',N'',N'0',N'系统信息维护');
INSERT INTO [sysmenu] ([mid],[msort],[pid],[name],[icon],[type],[url],[status],[memo]) VALUES (N'102',N'2',N'0', N'code',N'icon-th', N'0', N'',N'0',N'监管码管理');
INSERT INTO [sysmenu] ([mid],[msort],[pid],[name],[icon],[type],[url],[status],[memo]) VALUES (N'103',N'3',N'0', N'produce',N'icon-list-alt', N'0', N'',N'0',N'生产赋码管理');
INSERT INTO [sysmenu] ([mid],[msort],[pid],[name],[icon],[type],[url],[status],[memo]) VALUES (N'104',N'4',N'0', N'ware',N'icon-folder-close', N'0', N'',N'0',N'出入库管理');
INSERT INTO [sysmenu] ([mid],[msort],[pid],[name],[icon],[type],[url],[status],[memo]) VALUES (N'105',N'5',N'0', N'member',N'icon-user', N'0', N'',N'0',N'系统用户管理');
INSERT INTO [sysmenu] ([mid],[msort],[pid],[name],[icon],[type],[url],[status],[memo]) VALUES (N'106',N'6',N'0', N'log',N'icon-book', N'0', N'',N'0',N'系统日志管理');
INSERT INTO [sysmenu] ([mid],[msort],[pid],[name],[icon],[type],[url],[status],[memo]) VALUES (N'107',N'7',N'0', N'track',N'icon-book', N'0', N'',N'2',N'历史数据查询');
INSERT INTO [sysmenu] ([mid],[msort],[pid],[name],[icon],[type],[url],[status],[memo]) VALUES (N'108',N'7',N'0', N'check1',N'icon-book', N'0', N'',N'2',N'合格证校验');
INSERT INTO [sysmenu] ([mid],[msort],[pid],[name],[icon],[type],[url],[status],[memo]) VALUES (N'109',N'7',N'0', N'check2',N'icon-book', N'0', N'',N'2',N'合格证QA审核');



INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10100',N'101',N'basic_prod',N'1',N'basic/prod',N'2',N'产品信息管理');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1010001',N'10100',N'basic_prod_edit',N'2',N'',N'2',N'编辑');	
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1010002',N'10100',N'basic_prod_related',N'2',N'',N'0',N'关联');	
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1010003',N'10100',N'basic_prod_rescode',N'2',N'',N'0',N'详情');
	
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10101',N'101',N'basic_products',N'1',N'basic/products',N'0',N'药品信息管理');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1010101',N'10101',N'basic_products_import',N'2',N'',N'0',N'导入');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1010102',N'10101',N'basic_products_modify',N'2',N'',N'0',N'编辑');	
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1010103',N'10101',N'basic_products_related',N'2',N'',N'0',N'关联');	
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1010104',N'10101',N'basic_products_rescode',N'2',N'',N'0',N'详情');

INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10107',N'101',N'basic_fproducts',N'1',N'basic/fproducts',N'2',N'药品信息管理');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1010701',N'10107',N'basic_fproducts_import',N'2',N'',N'0',N'导入');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1010702',N'10107',N'basic_fproducts_modify',N'2',N'',N'0',N'编辑');	
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1010703',N'10107',N'basic_fproducts_related',N'2',N'',N'0',N'关联');	
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1010704',N'10107',N'basic_fproducts_rescode',N'2',N'',N'0',N'详情');

INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10102',N'101',N'basic_lines',N'1',N'basic/lines',N'0',N'车间生产线管理');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1010201',N'10102',N'basic_lines_add',N'2',N'',N'0',N'新增');	
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1010202',N'10102',N'basic_lines_modify',N'2',N'',N'0',N'编辑');	
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1010203',N'10102',N'basic_lines_del',N'2',N'',N'0',N'删除');	

INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10103',N'101',N'basic_clients',N'1',N'basic/clients',N'0',N'赋码控制端管理');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1010301',N'10103',N'basic_clients_add',N'2',N'',N'0',N'新增');	
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1010302',N'10103',N'basic_clients_modify',N'2',N'',N'0',N'编辑');	
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1010303',N'10103',N'basic_clients_del',N'2',N'',N'0',N'删除');	

INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10104',N'101',N'basic_stores',N'1',N'basic/stores',N'0',N'仓库信息管理');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1010401',N'10104',N'basic_stores_add',N'2',N'',N'0',N'新增');	
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1010402',N'10104',N'basic_stores_modify',N'2',N'',N'0',N'编辑');	
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1010403',N'10104',N'basic_stores_del',N'2',N'',N'0',N'删除');

INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10105',N'101',N'basic_customers',N'1',N'basic/customers',N'0',N'客户信息管理');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1010501',N'10105',N'basic_customers_import',N'2',N'',N'0',N'导入');	

INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10106',N'101',N'basic_actors',N'1',N'basic/actors',N'0',N'操作员管理');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1010601',N'10106',N'basic_actors_import',N'2',N'',N'0',N'导入');	

INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10200',N'102',N'code_prodfile',N'1',N'code/prodfile',N'2',N'码文件管理');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1020001',N'10200',N'code_prodfile_import',N'2',N'',N'0',N'导入');	

INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10201',N'102',N'code_files',N'1',N'code/files',N'0',N'码文件管理');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1020101',N'10201',N'code_files_import',N'2',N'',N'0',N'导入');	
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1020102',N'10201',N'code_files_download',N'2',N'',N'0',N'下载');	

INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10204',N'102',N'code_foodfile',N'1',N'code/foodfile',N'2',N'码文件管理');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1020401',N'10204',N'code_foodfile_import',N'2',N'',N'0',N'导入');	
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1020402',N'10204',N'code_foodfile_download',N'2',N'',N'0',N'下载');	
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1020403',N'10204',N'code_record_insert',N'2',N'',N'0',N'导入处理');	



INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10202',N'102',N'code_status',N'1',N'code/status',N'0',N'码状态查询');

INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10203',N'102',N'code_filesimport',N'1',N'code/filesimport',N'2',N'码文件导入');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1020301',N'10203',N'code_filesimport_import',N'2',N'',N'0',N'导入');	
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1020302',N'10203',N'code_filesimport_compare',N'2',N'',N'0',N'分配');	

INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10301',N'103',N'produce_order',N'1',N'produce/order',N'0',N'生产任务管理');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1030101',N'10301',N'produce_order_add',N'2',N'',N'0',N'新增');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1030102',N'10301',N'produce_order_modify',N'2',N'',N'0',N'修改');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1030103',N'10301',N'produce_order_del',N'2',N'',N'0',N'删除');

INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10302',N'103',N'produce_close',N'1',N'produce/close',N'0',N'生产结批管理');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1030201',N'10302',N'produce_close_detail',N'2',N'',N'0',N'详情');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1030202',N'10302',N'produce_close_end',N'2',N'',N'0',N'结批');

INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10303',N'103',N'produce_release',N'1',N'produce/release',N'0',N'生产方形管理');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1030301',N'10303',N'produce_release_end',N'2',N'',N'0',N'放行');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1030302',N'10303',N'produce_release_detail',N'2',N'',N'0',N'详情');

INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10304',N'103',N'produce_valid',N'1',N'produce/valid',N'0',N'关联关系验证');

INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10305',N'103',N'produce_export',N'1',N'produce/export',N'0',N'关联关系导出');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1030501',N'10305',N'produce_export_dump',N'2',N'',N'0',N'导出');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1030502',N'10305',N'produce_export_detail',N'2',N'',N'0',N'详情');

INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10306',N'103',N'produce_tracing',N'1',N'produce/tracing',N'0',N'生产批次追踪');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10307',N'103',N'produce_replace',N'1',N'produce/replace',N'2',N'关联关系替换');


INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10401',N'104',N'ware_order',N'1',N'ware/order',N'0',N'单据管理');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1040101',N'10401',N'ware_order_import',N'2',N'',N'0',N'导入');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1040102',N'10401',N'ware_order_modify',N'2',N'',N'0',N'编辑');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1040103',N'10401',N'ware_order_export',N'2',N'',N'0',N'导出');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1040104',N'10401',N'ware_order_batch',N'2',N'',N'0',N'整批出库');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1040105',N'10401',N'ware_order_del',N'2',N'',N'0',N'删除');

INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10501',N'105',N'member_users',N'1',N'member/users',N'0',N'登录用户管理');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1050101',N'10501',N'member_users_add',N'2',N'',N'0',N'新增');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1050102',N'10501',N'member_users_modify',N'2',N'',N'0',N'编辑');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1050103',N'10501',N'member_users_del',N'2',N'',N'0',N'删除');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1050104',N'10501',N'member_users_reset',N'2',N'',N'0',N'重置密码');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1050105',N'10501',N'member_users_status',N'2',N'',N'0',N'状态');

INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10502',N'105',N'member_groups',N'1',N'member/groups',N'1',N'用户角色管理');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1050201',N'10502',N'member_groups_add',N'2',N'',N'0',N'新增');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1050202',N'10502',N'member_groups_modify',N'2',N'',N'0',N'编辑');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1050203',N'10502',N'member_groups_del',N'2',N'',N'2',N'删除');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1050204',N'10502',N'member_groups_status',N'2',N'',N'0',N'状态');

INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10601',N'106',N'log_logs',N'1',N'log/logs',N'0',N'日志管理');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'1060101',N'10601',N'log_logs_export',N'2',N'',N'0',N'导出');

INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10701',N'107',N'track_code',N'1',N'track/code',N'0',N'监管码追踪');
INSERT INTO [sysmenu] ([mid],[pid],[name],[type],[url],[status],[memo]) VALUES (N'10702',N'107',N'track_batch',N'1',N'track/batch',N'0',N'批次追踪');

CREATE INDEX idx_bardef on barcodedef(defid);
INSERT INTO [barcodedef](defid,bclen,bcreslen,bcdatalen,bcvallen,bcchklen,bcstruc) values('1',20,8,8,0,4,'8-20-P1..P5-N1..N8-无-X1..X4');
INSERT INTO [barcodedef](defid,bclen,bcreslen,bcdatalen,bcvallen,bcchklen,bcstruc) values('2',20,7,9,0,4,'8-00~19,21~89-P1..P4-N1..N9-无-X1..X4');
INSERT INTO [barcodedef](defid,bclen,bcreslen,bcdatalen,bcvallen,bcchklen,bcstruc) values('3',20,8,7,1,4,'8-9-P1..P6-N1..N7-0/1-X1..X4');
INSERT INTO [barcodedef](defid,bclen,bcreslen,bcdatalen,bcvallen,bcchklen,bcstruc) values('4',20,3,12,1,4,'8-9-0,N1..N12-2-X1..X4');

CREATE INDEX idx_drugtype on drugtypedef(drugtype);
INSERT INTO [drugtypedef](drugtype,reslen,idnolen, typedetail) values('1',6,7,'麻醉药品原料药');
INSERT INTO [drugtypedef](drugtype,reslen,idnolen, typedetail) values('2',6,7,'第一类精神药品原料药');
INSERT INTO [drugtypedef](drugtype,reslen,idnolen, typedetail) values('3',6,7,'第二类精神药品原料药');
INSERT INTO [drugtypedef](drugtype,reslen,idnolen, typedetail) values('4',6,7,'麻醉药品');
INSERT INTO [drugtypedef](drugtype,reslen,idnolen, typedetail) values('5',6,7,'第一类精神药品');
INSERT INTO [drugtypedef](drugtype,reslen,idnolen, typedetail) values('6',6,7,'第二类精神药品');
INSERT INTO [drugtypedef](drugtype,reslen,idnolen, typedetail) values('7',5,9,'中药注射剂');
INSERT INTO [drugtypedef](drugtype,reslen,idnolen, typedetail) values('8',5,9,'疫苗');
INSERT INTO [drugtypedef](drugtype,reslen,idnolen, typedetail) values('9',5,9,'血液制品');
INSERT INTO [drugtypedef](drugtype,reslen,idnolen, typedetail) values('10',5,9,'国家基本药品目录入网药品');
INSERT INTO [drugtypedef](drugtype,reslen,idnolen, typedetail) values('11',5,9,'非基本药物');
INSERT INTO [drugtypedef](drugtype,reslen,idnolen, typedetail) values('12',5,9,'各省增补基本药物');
INSERT INTO [drugtypedef](drugtype,reslen,idnolen, typedetail) values('13',5,9,'特殊药品复方制剂');

CREATE INDEX idx_product on allproduct(productCode,subTypeNo, resprodcode, pkgRatio);
CREATE INDEX idx_productres on productrescode(productCode, resprodcode,codeversion, rescode, codeLevel);
CREATE INDEX idx_bcfile on barcodefile(subTypeNo,resprodcode);
CREATE INDEX idx_bcchk on barcodefile(rescode,startno,endno,verno, detailtype);
CREATE INDEX idx_bcfn on barcodefile(bcfilename);
CREATE INDEX idx_fileopt on fileoperation(fileid);
CREATE clustered index idx_status on bardata000(fileid);
CREATE index idx_bcdata on bardata000(resprodcode,codelv,status, updatepc, lasttime);
CREATE clustered index idx_drugrtllinepc on linepcdrug(productCode,subtypeno, lineid, pcid);
CREATE index idx_prodordtask on productordertask(productCode,subTypeNo,batchno, resprodcode);
CREATE clustered index idx_prodtaskdetail on productorderdetail(taskid,lineid,pcid);
CREATE index idx_allbox on productallbox(taskid, boxcode, lineid,pcid,palletcode,status);
CREATE index idx_boxflag on productallbox(taskid, boxflag);
CREATE index idx_tskbox on productallbox(taskid, boxcode, maintskid);
CREATE index idx_customer on customers(customerid,customername,shortname);
CREATE index idx_paname on sysparams(paraname);
CREATE clustered index idx_sysparas on sysparams(paraid,parasubid);
CREATE index IX_warehouseticket on warehouseticket(ticketid, subtckid);
CREATE index IX_warehouseticket_0 on warehouseticket(resprodcode,batchno,ticktype );
CREATE index IX_warehouseticket_1 on warehouseticket(customerid);
CREATE index IX_warehouseticket_2 on warehouseticket(pdaid);
CREATE clustered index IX_warehousecode on warehouseticketdetail (ticketid, subtckid);
CREATE index IX_warehousecode_1 on warehouseticketdetail (code20);
CREATE index IX_warehousecode_2 on warehouseticketdetail (smidcode, lmidcode);
CREATE index IX_warehousecode_3 on warehouseticketdetail (boxcode, palletcode);
CREATE index idx_sysfunc on sysfunction(funcid,funcgroup,funcdefine,funcname);
CREATE index idx_groupfunc on sysgroupfunc(id, groupid, funcid);
CREATE index idx_sysuser on sysuserlist(username,userpwd,usergroup, userstatus);
CREATE CLUSTERED INDEX [IX_boxdata000_batch] ON [dbo].[boxdata000] 
(
	[taskid] ASC,
	[boxcode] ASC,
	[codelv] DESC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY];      

INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A','A00','系统','N30','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A','A01','药监码管理','N1','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A','A02','生产赋码管理','N5','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A','A03','出入库信息管理','N12','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A','A04','系统信息维护','N19','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A','A05','系统用户管理','N16','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A00','B01','切换用户','N32','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A01','C01','码状态查询','N2,SpeedButton6','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A01','C02','码文件管理','N4,SpeedButton1','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('C02','CA01','导入产品码文件数据','SpeedButton6','BarFileF','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('C02','CA02','导出码文件清单','SpeedButton1','BarFileF','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('C02','CA03','管理码文件','SpeedButton2','BarFileF','',1,1);

INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A02','D01','生产任务管理','N6,SpeedButton2','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('D01','DA01','新建生产任务','SpeedButton6','TaskForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('D01','DA02','导出任务清单','SpeedButton1','TaskForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('D01','DA03','任务单信息','SpeedButton2','TaskForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('D01','DA04','删除任务单','SpeedButton4','TaskForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('D01','DA05','任务单结批','SpeedButton3','TaskForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('D01','DA06','单据反结批','SpeedButton7','TaskForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('D01','DA07','任务单放行','SpeedButton9','TaskForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('D01','DA08','任务单生产详情','SpeedButton5','TaskForm','',1,1);

INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A02','D02','关联关系查验','N7,SpeedButton3','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A02','D03','异常替换处理','N8','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A02','D04','关联关系导出','N9,SpeedButton7','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('D04','EA01','任务单据信息','SpeedButton2','BathOutF','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('D04','EA02','任务单生产详情','SpeedButton5','BathOutF','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('D04','EA03','导出关联关系','SpeedButton3','BathOutF','',1,1);

INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A03','E01','手持终端管理','N14','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A03','E02','手持终端单据导入','N13,SpeedButton5','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A03','E03','出入库单据管理','N15,SpeedButton4','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A04','F01','药品信息管理','N20','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('F01','LA01','导入产品信息','SpeedButton6','DrugForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('F01','LA02','导出产品目录','SpeedButton1','DrugForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('F01','LA03','产品详细信息','SpeedButton2','DrugForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('F01','LA04','产线关联信息','SpeedButton3','DrugForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('F01','LA05','编辑附加信息','SpeedButton7','DrugForm','',1,1);

INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A04','F02','车间生产线信息管理','N21','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('F02','MA01','登记车间生产线','SpeedButton6','DevLineF','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('F02','MA02','导出生产线信息','SpeedButton1','DevLineF','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('F02','MA03','编辑生产线信息','SpeedButton2','DevLineF','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('F02','MA04','删除生产线信息','SpeedButton3','DevLineF','',1,1);


INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A04','F03','赋码控制端信息管理','N22','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('F03','NA01','登记赋码控制端','SpeedButton6','DevIPCsF','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('F03','NA02','导出赋码控制端信息','SpeedButton1','DevIPCsF','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('F03','NA03','编辑赋码控制端信息','SpeedButton2','DevIPCsF','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('F03','NA04','删除赋码控制端信息','SpeedButton3','DevIPCsF','',1,1);

INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A04','F04','产品仓库信息管理','N23','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('F04','PA01','登记仓库信息','SpeedButton6','DrugStoF','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('F04','PA02','导出仓库信息','SpeedButton1','DrugStoF','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('F04','PA03','编辑仓库信息','SpeedButton2','DrugStoF','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('F04','PA04','删除仓库信息','SpeedButton3','DrugStoF','',1,1);

INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A04','F05','往来单位信息管理','N24','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('F05','QA01','导入往来单位信息','SpeedButton6','DrugCusF','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('F05','QA02','导出往来单位信息','SpeedButton1','DrugCusF','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('F05','QA03','编辑附加信息','SpeedButton7','DrugCusF','',1,1);

INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A04','F06','仓库操作员信息管理','N25','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('F06','RA01','仓库操作员信息管理','SpeedButton6','StoOperF','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('F06','RA02','导出仓库操作员信息','SpeedButton1','StoOperF','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('F06','RA03','查看操作员密码','SpeedButton7','StoOperF','',1,1);

INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A05','G01','用户组管理','N17','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('G01','SA01','新建用户组','SpeedButton1','UserGrpF','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('G01','SA02','修改用户组','SpeedButton2','UserGrpF','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('G01','SA03','删除用户组','SpeedButton3','UserGrpF','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('G01','SA04','用户组权限','SpeedButton4','UserGrpF','',1,1);

INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A05','G02','用户信息管理','N18','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('G02','TA01','用户信息管理','SpeedButton6','UserForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('G02','TA02','编辑用户信息','SpeedButton4','UserForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('G02','TA03','删除用户信息','SpeedButton3','UserForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('G02','TA04','重置用户密码','SpeedButton1','UserForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('G02','TA05','用户权限管理','SpeedButton2','UserForm','',1,1);

INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('A05','G03','系统信息基础信息维护','N26','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('G03','H01','系统参数维护','N29','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('G03','H02','码规则定义','N27','MainForm','',1,1);
INSERT INTO sysfunction(funcgroup,funcdefine,funcname,comname,formname, comment,functype,sysuser) values('G03','H03','系统功能定义','N28','MainForm','',1,1);

INSERT INTO sysparams([paraname],[paravalue],[paraid],[paraedit]) values('producttray','1','101','0');
INSERT INTO sysparams([paraname],[paravalue],[paraid],[paraedit]) values('setbarminnum','100000','101','0');
INSERT INTO sysparams([paraname],[paravalue],[paraid],[paraedit]) values('setothersyscode','1','101','1');
INSERT INTO sysparams([paraname],[paravalue],[paraid],[paraedit]) values('barcodefile','bardata000','101','1');
INSERT INTO sysparams([paraname],[paravalue],[paraid],[paraedit]) values('boxdatafile','boxdata000','101','1');
INSERT INTO sysparams([paraname],[paravalue],[paraid],[paraedit]) values('tablerecordnum','20000000','101','1');
INSERT INTO sysparams([paraname],[paravalue],[paraid],[paraedit]) values('barcodelen','20','101','0');

CREATE PROCEDURE [userlogin] @username varchar(30),@userpwd varchar(100),@lineID varchar(5),@clientID varchar(5)
as 
   if(@lineID = '') and (@lineID<>'DLAA')
   begin 
	  select '' as uid, '' as uname, 1 as statusflag , '请输入产线ID' as error
	  return
   end 
   
   if(@clientID = '') and (@clientID<>'PCAA')
   begin 
	  select  '' as uid, '' as uname, 1 as statusflag , '请输入控制端ID' as error
	  return
   end
   
   set @userpwd = SUBSTRING(sys.fn_VarBinToHexStr(hashbytes('MD5', @userpwd)),3,35);
   declare @uid int 
   declare @limitime datetime
   declare @userstatus int 
   declare @linerecord varchar(5)
   declare @clientrecord varchar(5)
   
   select @uid=id,@limitime =limitime,@userstatus=userstatus,@linerecord=lineid,@clientrecord=pcid from sysuserlist where username=@username and userpwd=@userpwd  
   if(@uid is null)
    begin 
        select  '' as uid, @username as uname, 1 as statusflag , '登录失败' as error
        return
    end 
    
    if(@limitime<GETDATE())
    begin
        update sysuserlist set userstatus=2 where id = @uid 
		select @uid as uid, @username as uname,  1 as statusflag , '用户已过期' as error
		return
    end 
    
    if(@linerecord is not null and @lineID != @linerecord)
    begin 
		select @uid as uid, @username as uname, 1 as statusflag , '生产线错误' as error
		return
    end 
    
    if(@clientID is not null and @clientID != @clientrecord)
    begin 
		select @uid as uid, @username as uname, 1 as statusflag , '控制端错误'  as error
		return
    end 
    
    if(@userstatus = 1)
    begin 
		select @uid as uid, @username as uname, 1 as statusflag,'用户已锁定' as error
		return 
    end 
    
    if(@userstatus = 9)
    begin 
		select @uid as uid, @username as uname, 9 as statusflag,'修改密码' as error
		return 
    end 
    
    update sysuserlist set lastlogtime=GETDATE(),updatetime=GETDATE() where id = @uid 
    select @uid as uid, @username as uname, 0 as statusflag,'' as error;
    
    
CREATE PROCEDURE getdatatablename 
  @tabletype tinyint,
  @nowtablename nvarchar(20) , @tablerecordnum integer,
  @newtablename varchar(20) output
  as 

declare
  @recnum integer,
  @newcreate tinyint,
  @sqlstr nvarchar(2000)

begin
  
  set @sqlstr='select @rcn=isnull(max(id),0) from '+@nowtablename
  exec sp_executesql @sqlstr,N'@rcn as integer output',@recnum output
  if @recnum>=@tablerecordnum begin
    set @newtablename=left(@nowtablename,7)+right(convert(integer,right(@nowtablename,3))+1001,3)
    set @newcreate=1
    set @sqlstr=''
  end else begin
    set @newtablename=@nowtablename
    set @newcreate=0
    set @sqlstr=''
  end

  
  if @newcreate=1 begin
    
    if @tabletype=1 begin
      set @sqlstr='create table '+@newtablename
        +'('
        +'id      integer IDENTITY  not null, '
        +'code20    char(20) not null UNIQUE,'
        +'prodsubtype char(10) not null,'
        +'resprodcode varchar(13) not null,' 
        +'codelv    tinyint not null ,'
        +'fileid    integer not null ,'
        +'status    tinyint not null default 0,'
        +'updatepc   varchar(10), '
        +'vbatch     varchar(20), '
        +'firsttime  datetime default getdate() not null, '
        +'lasttime   datetime default getdate(),'
        +')'
        +'create clustered index idx_status on '+@newtablename+'(fileid, status)'
        +'create index idx_code20 on '+@newtablename+'(code20,  fileid, status, lasttime)'
        +'create index idx_bcdata on '+@newtablename+'(resprodcode,codelv,status, updatepc, lasttime)'
    end else begin
      set @sqlstr='create table  '+@newtablename
        +'('
        +'id  integer  identity  not null,'
        +'prodsubtype char(10) not null, '
        +'resprodcode varchar(13) not null,'
        +'code20 char(20) not null UNIQUE,'
        +'parentcode char(20),'
        +'codelv  tinyint not null,'
        +'lv4code tinyint not null,'
        +'lv3code tinyint not null,'
        +'lv2code tinyint not null,'
        +'lv1code tinyint not null,'
        +'smidcode char(20),  '
        +'lmidcode char(20),  '
        +'boxcode char(20) not null,'
        +'codeflag tinyint not null,  '
        +'lineid char(4) not null, '
        +'taskid integer not null , '
        +'status  tinyint not null default 0, '
        +'sysuserid integer default -1,'
        +'savetime  datetime default getdate() not null, '
        +'updateuser integer,'
        +'updatetime datetime ,'
        +')'
        +'create index idx_id on '+@newtablename+'(id,resprodcode,boxcode,taskid)'
        +'create index idx_c20 on '+@newtablename+'(code20,parentcode, smidcode,lmidcode)'
    end  
  end
  exec sp_executesql @sqlstr
    
  select @newtablename as newtablename end;




