數據導入 · 天力ERP實施手冊

[TOC] # 一、財貿通數據導入導入前，請先瀏覽學習本軟件數據結構----[開發----基礎](http://www.hmoore.net/book/ylyscx/tlerp_imple/preview/%E5%9F%BA%E7%A1%80.md)，對本軟件的數據有個大致的了解后，再進行導入工作。財貿通數據導入腳本是以本軟件的備注寫入財貿通的表ID進行關聯的，如果導入發現有問題，可用ID和備注關聯進行相應的修改。 ## 1、初始化導入 ### 基本資料導入 PS：導入前，請替換相應的財貿通數據庫名稱 cmt402ls --------部門資料 ~~~ select*from base_department insert into base_department (id,org_id,mcheng,pyma,ztai,bzhu) select case when ID = '_qt1' then 'BM1000000000' else 'BM'+ RIGHT('000000000000'+CAST(ID AS VARCHAR), 12) end , 'org_id',mcheng,pyin,'10',ID from cmt402ls..BaseDepartment where deleted <> '8' and SonNum = '0' select max(id) from base_department where id <> 'BM1000000000' ~~~ --------職員資料 ~~~ select * from base_employee insert into base_employee (id,bma,org_id,d_id,mcheng,pyma,ztai,bzhu,csrqi,byrqi,e_shren,shsjian) select case when id = '_cjyh1' then 'ZY100000000000' else 'ZY'+ right ('00000000000' + CAST (id as varchar),12 ) end ,bma,'',e_d_id,mcheng,pyin, '10' ,id,convert(char(10),csrqi,20),convert(char(10),byrqi,20),shren_id,shsjian from cmt402ls..BaseEmployee where SonNum = '0' and deleted <> '8' update base_employee set d_id = d.mcheng from base_employee e ,base_department d where e.d_id = d. bzhu ---修改職員部門 select convert(char(10),'2013-02-06 10:00:00.000',20) ~~~ --------倉庫資料 ~~~ select * from base_storage select * from cmt402ls..basestorage insert into base_storage (id,mcheng,pyma,lxing,mji,zyrshu,e_bgyuan,e_fzren,dis_id,cctjian,azlpdian,zllxing,wklxing,ztai,bzhu ) select 'CK' + RIGHT('000000000000'+CAST(ID AS VARCHAR), 12) ,mcheng,pyin,lxing,mji,ygrshu,s_e_bgyuan_id,s_e_id, '',cctjian,azlpd,zlhklxing,wklxing,'10',id from cmt402ls..BaseStorage where sysrow = '0' and SonNum = '0' and deleted <> '8' ~~~ --------貨位資料 ~~~ select * from dbo.base_location select * from cmt402ls..baselocation ----------------alter table base_location add s_id varchar(255) insert into base_location (id,mcheng,pyma,ztai,bzhu,s_id) select 'HW' + RIGHT('000000000000'+CAST(l.ID AS VARCHAR), 12), l.mcheng,pyin,'10' ,l.id, s.id from cmt402ls..BaseLocation l left join base_storage s on s.bzhu = l.l_s_id ~~~ --------往來單位 ~~~ select * from base_client select * from cmt402ls..baseclient insert into base_client (id,v_id,bma,mcheng,pyma,lxing,jming,dqu,qyfzren,qyfzrdhua,lxren,lxrdhua,dwczhen,xyedu,yjedu,khyhang,khyhzhao, swdjhao,dzhi,ysjjia,yssjia,jjlv,cwdma,ybian,wzhan,qq,ztai,skqxfshi,skqxtian,skqxyue,skqxri,qtzliao,qtzliao2, bzhu,zdyi1,zdyi2,zdyi3,zdyi4,ysfshi,ysxlu,xlbhao,xlglshu,shrxming,shrdhua,shrdzhi,qylxing,rzlxing,rzbhao,qyxkzhao, shren,shsjian,e_cjren,cjsjian,e_xgren,xgsjian) select 'DW' + RIGHT('000000000000'+CAST(c.ID AS VARCHAR), 12), '',c.bma,c.mcheng,c.pyin,c.lxing,c.jming,a.mcheng,'','',lxren,dhua,czhen,xyedu,yjedu,zhao,zhao, shao,dzhi,ysjjia,yssjia,jjlv,'',yzbma,www,QQ,c.'10',skqxfshi,skqxian,skqxjgyue,skqxri,'','', c.id,BZHU1,BZHU2,BZHU3,BZHU4,psfshi,isnull(r.mcheng,''),psxlbhao,psxljli,shren,shdhua,shdzhi,qylxing,rzlxing,zshao,xkzhao, shren_id ,shsjian,C_CE_ID,CreateDate,C_CE_ID,c.ModifyDate from cmt402ls..BaseClient c left join cmt402ls..basezone a on a.id = c.c_z_id left join cmt402ls..BaseTranRoad r on r.id = c.c_tr_id where c.kid <> '1' and c.SonNum = '0' and c.deleted <> '8' update base_client set e_shren = e.mcheng from base_client p ,base_employee e where p.e_shren = e.bzhu update base_client set e_cjren = e.mcheng from base_client p ,base_employee e where p.e_cjren = e.bzhu update base_client set e_xgren = e.mcheng from base_client p ,base_employee e where p.e_xgren = e.bzhu ~~~ --------往來單位授權人員 ~~~ select * from dbo.base_c_employee select * from cmt402ls..BaseClientEmployee insert into base_c_employee (id,c_id,bma,mcheng,pyma,jming,lxdhua,dzhi,xbie,zjhma,csrqi,rzrqi,xueli,zcheng,zhwu,sqsjqi,sqsjzhi ,ywfwei,qtzliao,bzhu1,bzhu2,bzhu3,bzhu4,ztai,zdyi1,zdyi2,zdyi3,zdyi4,e_cjren,cjsjian,e_xgren,xgsjian) select 'CE' + RIGHT('000000000000'+CAST(ce.ID AS VARCHAR), 12), c.id,ce.bma,ce.mcheng,ce.pyin,ce.jming,ce.dhua,ce.dzhi,ce.xbie,ce.sfzhao,csrqi,rzrqi,xli,zcheng,zwu,sqksrqi,sqjsrqi, ywfwei,qtzliao,ce.id,BZHU1,BZHU2,BZHU3,deleted,'','','','','admin',ModifyDate,'admin',ModifyDate from cmt402ls..BaseClientEmployee ce left join base_client c on c.bzhu = ce.ce_c_id ~~~ --------商品資料 ~~~ select * from base_product select * from cmt402ls..baseproduct insert into base_product ([id],[bma],[mcheng],[pyma],[tyming],[gge],[cdi],[jxing],[xhao],[bzgge] ,[pzwhao],[bzqyue],[bzqtian],[jbdwei],[jbdwtma],[cctjian],[yhfshi],[yhtshu],[sccshang],[s_id] ,[cbhsfa],[slv],[ztai],[mdxliang],[mtxliang],[dzjgtzma] ,[bzhu] ,[sfcxiao],[sfjfen],[sfdzhe] ,[sfglkcun],[sfcyjscxje],[at_id],[clzma],[clbli],[asltcheng1] ,[asltcheng2],[ajetcheng1],[ajetcheng2],[amltcheng1] ,[amltcheng2],[zxxsdwei],[zxcgdwei],[zxcgliang],[zhdwei],[wklxing],[glxshou],[e_shren],[shsjian],[gspsxing] ,[cwdma],[ybdma],[qtzliao],[zdyi1],[zdyi2],[zdyi3],[zdyi4],[zdyi5],[zdyi6],[zdyi7] ,[zdyi8],[e_cjren],[e_xgren],[cjsjian],[xgsjian]) select 'SP'+ RIGHT('000000000000'+CAST(ID AS VARCHAR), 12), bma,mcheng,pyin,jming,gge,cdi,lxing,xhao,bzgge ,pzwhao,xqyue,xqtian,dwei,tma,cctjian,yhfshi,yhtshu,sccshang,p_s_id ,cbhsfa,slv,deleted,'0.0' ,'0.0',dzjguan,id, (case when dzhe in (0,1) then '0' when dzhe in (2,3) then '1' end ) e , ----2，3 不允許,0，1 允許促銷 jfen, ---1 允許積分 0 不允許積分 (case when dzhe in (1,3) then '0' when dzhe in (0,2) then '1' end) b ---0,2 不允許打折，1，3允許 ,spxzhi,'0','',cl_p_id,clbli,'0.0','0.0','0.0','0.0','0.0' ,'0.0',mrdwei,cgmrdwei,zxcgliang,zhdwei,ckwklxing,'',shren_id,shsjian,GSPsxing ,'','','',P_K_ID1,P_K_ID2,P_K_ID3,P_K_ID4,P_K_ID5,P_K_ID6,P_K_ID7 ,P_K_ID8,P_CE_ID,P_CE_ID,CreateDate,ModifyDate from cmt402ls..baseproduct where SonNum = '0' and KID <> '1' update base_product set e_shren = e.mcheng from base_product p ,base_employee e where p.e_shren = e.bzhu update base_product set e_cjren = e.mcheng from base_product p ,base_employee e where p.e_cjren = e.bzhu update base_product set e_xgren = e.mcheng from base_product p ,base_employee e where p.e_xgren = e.bzhu ~~~ --------價格資料 ~~~ select * from base_packprice select * from cmt402ls..baseprice insert into base_packprice (id,p_id,dwei,dwei_idx,hsbli,lsjia,zjjjia,zdxjia1,zdxjia2,ysjia1,ysjia2,ysjia3,ysjia4,ysjia5,ysjia6,ygmli,ygmllv,chang,kuan,gao, zliang,tji,sfjbdwei,dwtma,ztai,qtzliao,bzhu,zdyi1,zdyi2,zdyi3,zdyi4,e_cjren,e_xgren,cjsjian,xgsjian) select b.id,b.id,p.dwei , case when dwei_idx = '1' then '0' when dwei_idx = '2' then '1' when dwei_idx = '3' then '2' end , case when dwei_idx = '1' then '1' when dwei_idx = '2' then p.hsbli2 when dwei_idx = '3' then p.hsbli3 end , a.jge9,a.jjia,a.zdjia,a.zdjia2,jge1,jge2,jge3,jge4,jge5,jge6,'0.0','0.0','0.0','0.0','0.0', '0.0','0.0',case when dwei_idx = '1' then '1' else '0' end , case when dwei_idx = '1' then p.tma when dwei_idx = '2' then p.tma2 when dwei_idx = '3' then p.tma3 end , '0','','','','','','','admin','admin',a.zjrqi,a.ModifyDate from cmt402ls..BasePrice a left join base_product b on b.bzhu = a.ID left join cmt402ls..BaseProduct p on p.ID = a.p_id where a.p_id in (select bzhu from base_product ) and b.id is not null ~~~ --------會員卡類型 ~~~ select * from dbo.vip_v_type select * from cmt402ls..basevipcardtype insert into vip_v_type (id,mcheng,pyma,yxqi,jfxshu,jfljfshi,zkou,csczhi,csjfen,xddmhwei,hyqyi,sfjfen,sfczhi,sfcxiao, zdzkou,jfzczjsjfen,jfzczzjczhi,hysrjfbslxing,hysrjfbshu, cxspsfjfen,qyxfjesji,qyxfcssji,qyhyjfsji,xfjesjszhi,xfslsjszhi,hyjfsjszhi,xfje_mt_id,xfcs_mt_id,hyjf_mt_id,dzspsfjfen,jfsjhkcjfshu, ztai,jglxing,bzhu,zdyi1,zdyi2,zdyi3,zdyi4,e_cjren,e_xgren,cjsjian,xgsjian) select 'VT'+ RIGHT('000000000000'+CAST(ID AS VARCHAR), 12),mcheng,pyin,'',jfje,jffshi,mrzkou,'0.0','0.0','','',sfjfen,sfyxczhi,sfsycxiao, '1',jfzczjsjfen,jfzczzjje,hysrjfbslxing,hysrjfbshu, dzspsfjfen,qyxfjesj,qyxfcshusj,qyjfensj,sjxfje,sjxfcshu,sjjfen,xfje_vt_id,xfcshu_vt_id,jfen_vt_id,dzspsfjfen,sjkcjfen, deleted,'',id,'','','','','admin','admin',ModifyDate,ModifyDate from cmt402ls..BaseVipCardType ~~~ --------會員卡資料 ~~~ select * from vip_vipinfo select * from cmt402ls..BaseVipCard insert into vip_vipinfo ([id],[vt_id],[hykhao],[mcheng],[xbie],[tjrkhao],[tjrxming],[zjlxing],[zjhma] ,[sri] ,[srlxing] ,[sjhma],[gddhua],[skje],[fkfshi],[e_tcren],[sheng],[shi],[qu],[jdao],[ybian] ,[dzyjian],[bzhu],[zddyxpiao],[org_id],[pyma],[e_fkren],[fkrqi],[yxqi],[e_zbfwryuan],[e_mdfwryuan] ,[mma],[bzhu1],[bzhu2] ,[bzhu3],[bzhu4],[bzhu5],[qcjfen] ,[qcczye],[qcxfje],[ztai] ,[grtzheng],[hyjbie],[zkyhui],[zkou],[zdyi1],[zdyi2],[zdyi3],[zdyi4],[qtzliao],[cjsjian] ,[e_cjren],[xgsjian],[e_xgren]) select 'HY'+ RIGHT('000000000000'+CAST(v.ID AS VARCHAR), 12), vt.id,bma,v.mcheng,xbie,'','',zjlxing,zjhma,csrqi,srlxing, lxdhua,'','0.0','','','','','','','', '',v.id,'',shoptag,PYIN,v_e_id,isnull(CreateDate,GETDATE()),yxqi,'admin',V_E_ID2, '',v.bzhu,[bzhu1],[bzhu2] ,[bzhu3],[bzhu4],inijfen,iniczje,inixfje,deleted, '','','0.0',v.zkou,'','','','',qtxxi,isnull(CreateDate,GETDATE()), isnull(v_ce_id,'admin'),ModifyDate,'admin' from cmt402ls..BaseVipCard v left join vip_v_type vt on vt.bzhu = v.v_vt_id where v.KID <> '1' ~~~ --------更新會員卡發卡人 `update vip_vipinfo set e_fkren = e.id from vip_vipinfo v ,base_employee e where v.e_fkren = e.bzhu ` --------會員卡余額表 ~~~ select * from vip_balance select *from cmt402ls..basevipcard insert into vip_balance (v_id,jfen,czje,ztai,bzhu,zdyi1,zdyi2,zdyi3,zdyi4,xfje,xfcshu) select vp.id ,v.jfen,v.czje,v.deleted,v.ID,v.BZHU1,v.BZHU2,v.BZHU3,v.BZHU4,v.xfje,v.xfcshu from cmt402ls..BaseVipCard v left join vip_vipinfo vp on vp.bzhu = v.ID where v.KID <> '1' ~~~ --------資料分類處理及資料最大編碼處理 ~~~ select * from sys_datasn select * from dbo.base_type_p_relation insert into base_type_p_relation ---商品資料 (zl_id,zllxing,fl_id,e_cjren,e_xgren,cjsjian,xgsjian) select ID,'SP','','admin','admin','2016-04-19 16:06:45.100','2016-04-19 16:06:45.100' from base_product insert into base_type_p_relation ----往來單位 (zl_id,zllxing,fl_id,e_cjren,e_xgren,cjsjian,xgsjian) select ID,'DW','','admin','admin','2016-04-19 16:06:45.100','2016-04-19 16:06:45.100' from base_client insert into base_type_p_relation ---職員 (zl_id,zllxing,fl_id,e_cjren,e_xgren,cjsjian,xgsjian) select ID,'ZY','','admin','admin','2016-04-19 16:06:45.100','2016-04-19 16:06:45.100' from base_employee insert into base_type_p_relation ---部門 (zl_id,zllxing,fl_id,e_cjren,e_xgren,cjsjian,xgsjian) select ID,'BM','','admin','admin','2016-04-19 16:06:45.100','2016-04-19 16:06:45.100' from base_department insert into base_type_p_relation ---倉庫 (zl_id,zllxing,fl_id,e_cjren,e_xgren,cjsjian,xgsjian) select ID,'CK','','admin','admin','2016-04-19 16:06:45.100','2016-04-19 16:06:45.100' from base_storage insert into base_type_p_relation ---機構 (zl_id,zllxing,fl_id,e_cjren,e_xgren,cjsjian,xgsjian) select ID,'ORG','','admin','admin','2016-04-19 16:06:45.100','2016-04-19 16:06:45.100' from base_organization insert into base_type_p_relation ---會員卡類型 (zl_id,zllxing,fl_id,e_cjren,e_xgren,cjsjian,xgsjian) select ID,'VT','','admin','admin','2016-04-19 16:06:45.100','2016-04-19 16:06:45.100' from vip_v_type insert into base_type_p_relation ---會員卡 (zl_id,zllxing,fl_id,e_cjren,e_xgren,cjsjian,xgsjian) select ID,'HY','','admin','admin','2016-04-19 16:06:45.100','2016-04-19 16:06:45.100' from vip_vipinfo ~~~ ### 庫存導入 --------庫存余量明細 ~~~ select * from dbo.store_storedetail select * from cmt402ls..storeremains insert into store_storedetail (kclshao,s_id,org_id,p_id,l_id,gyshang_id,dju_id,dju_lxing,phao,mjphao,scrqi,yxqi, rksjian,sliang,cbjia,fycbjia,fycbje,cbje,pcxjia1,pcxjia2,ztai,bzhu,zdyi1,zdyi2, zdyi3,zdyi4) select 'KC'+RIGHT('000000000000'+CAST(s.ID AS VARCHAR), 12), s1.id,'',p.id,isnull(l.id,'') ,isnull(c.id,''), '','CGRK',phao,'',convert(char(10),scrqi,20),convert(char(10),yxqi,20), rksjian,sliang,cbjia,'0.0','0.0',cbje,pcxjia1,pcxjia2,tsbzhi,s.id,pctma,pcbzhu, pcfjsming,inorder from cmt402ls..StoreRemains s left join base_storage s1 on s1.bzhu = s.s_id left join base_product p on p.bzhu = s.p_id left join base_p_location l on l.bzhu = s.l_id left join base_client c on c.bzhu = s.c_id2 ~~~ --------庫存匯總 ~~~ select * from dbo.store_storesum select * from dbo.store_storedetail insert into store_storesum (s_id,org_id,p_id,sliang,cbjia,cbje,ztai) select s_id,org_id,p_id,SUM(sliang) sliang ,cbjia,cbje,ztai from store_storedetail group by s_id,org_id,p_id,cbjia,cbje,ztai ~~~ ### 單據導入 --------采購單據主表 ~~~ select * from buy_idx select * from cmt402ls..idxbill where billtype in ('10','11','122') insert into buy_idx (id,ydju_lxing,ydju_id,bhao,dju_lxing,oc_id,dis_id,org_id,au_id,rqi, gzrqi,dyrqi,jsrqi,e_jsren,e_gzren,e_shren,a_id,c_id,ce_id, d_id,v_id,s_id,s_id2,slv,sliang,hjje,cbje,hjyhje,lsje, ysje,ssje,ztai,sfjsuan,jsfshi,jfen,dycshu,cxdan_lxing,cxdan_id,ywfpiao, fplxing,fphma,csztai,lxrmcheng,lxdhua,lxdzhi,fyong,bzhu,zdyi1,zdyi2, zdyi3,zdyi4,e_cjren,cjsjian,e_xgren,xgsjian,e_fhren1,e_fhren2,e_fhren,psfshi, lhjshu,zhjshu,shrdhua,shrmcheng,zkou,shrdzhi) select (case when i.billtype = '10' then 'CGRK' when i.billtype = '11' then 'CGTH' when i.billtype = '122' then 'CGDD' end )+ RIGHT('000000000000'+CAST(i.ID AS VARCHAR), 12), '','',bhao,case when i.billtype = '10' then 'CGRK' when i.billtype = '11' then 'CGTH' when i.billtype = '122' then 'CGDD' end,'','',i.org_ID,'',rqi, gzrqi,dyrqi,jsrqi,e1.id,e2.id,e3.id ,a_id,c1.id,isnull(ce.id,''), d1.id,isnull(v1.id,''),s1.id,isnull(s2.id,''),slv,sliang,hjje,hjje-yhui,yhui,hjlsje, ysje,ssje,states,jsflag,jsfshi,i.jfen,dycshu,cxdlxing,i.cxorg_id,fpflag, FPLXing,fphma,TranStates,idxlxren,i.lxdhua1,idxdzhi,'0.0',i.bzhu,i.id,i.bzhu2, i.bzhu3,i.bzhu4,'admin',gzrqi,'admin',gzrqi,isnull(e6.id,''),'',isnull(e4.id,''),psfshi, lhjshu,zhjshu,shrdhua,isnull(e5.id,''),isnull(zkou,'100.0'),shrdzhi from cmt402ls..IDXBill i left join base_employee e1 on e1.bzhu = i.e_id left join base_employee e2 on e2.bzhu = i.e_input_id left join base_employee e3 on e3.bzhu = i.e_settle_id left join base_client c1 on c1.bzhu = i.c_id left join base_c_employee ce on ce.bzhu1 = i.ce_id left join base_department d1 on d1.bzhu = i.d_id left join v_vip_vipinfo v1 on v1.bzhu = i.v_id left join base_storage s1 on s1.bzhu = i.s_id left join base_storage s2 on s2.bzhu = i.s_id2 left join base_employee e4 on e4.bzhu = i.e_fhuo_id left join base_employee e5 on e5.bzhu = i.shren left join base_employee e6 on e6.bzhu = i.e_goodscheck_id where i.billtype in ('10','11','122') and i.states = '0' ~~~ --------采購單據明細注：id寫行備注hbzhu ~~~ select * from dbo.buy_detail select *from cmt402ls..billbuy insert into buy_detail ([id],[dju_id],[ydju_lxing],[ydjmxi_id],[dju_lxing],[crkbzhi],[hhao],[kclshao],[oc_id],[dis_id] ,[org_id],[au_id],[a_id],[s_id],[e_jsren],[p_id],[dwei_idx],[ztai],[zhsliang],[lhsliang] ,[sliang],[sliang2],[cbjia],[cbje],[jge],[je],[zkou],[zhjia],[zhje],[slv] ,[hsjia],[hsje],[se],[lsjia],[lsje],[pcxjia1],[pcxjia2],[phao],[mjphao],[yxqi] ,[scrqi],[rksjian],[thsliang],[thsjian],[jfen],[cbhsfa],[csztai],[l_id],[cxdan_lxing],[cxdmxi_id] ,[zlzkuang],[pcbzhu1],[pcbzhu2],[gyshang_id],[hbzhu],[e_fhren1],[e_fhren2],[fhztai],[fhsliang],[zdyi1] ,[bzhu],[zdyi2],[zdyi3],[zdyi4],[e_cjren],[cjsjian],[e_xgren],[xgsjian],[e_ysyuan1],[e_ysyuan2] ) select (case when b.billtype = '10' then 'CGRK' when b.billtype = '11' then 'CGTH' when b.billtype = '122' then 'CGDD' end )+ RIGHT('000000000000'+CAST(b.ID AS VARCHAR), 12), i.id,'','',case when b.billtype = '10' then 'CGRK' when b.billtype = '11' then 'CGTH' when b.billtype = '122' then 'CGDD' end, case when b.billtype = '10' then '1' when b.billtype = '11' then '-1' when b.billtype = '122' then '0' end,'','','','', '','','',s1.id,i.e_shren,p.id,'0',i.ztai,'0.0','0.0', b.sliang,sliang2,cbjia,b.cbje,jge,je,b.zkou,zhjia,zhje ,b.slv, hsjia,hsje,se,lsjia,b.lsje,pcxjia1,pcxjia2,phao,'',convert(char(10),yxqi,20), convert(char(10),scrqi,20),rksjian,thsliang,thrqi,jfen,b.cbhsfa,i.csztai,isnull(pl.id,''),cxdan_lxing,cxdan_id, zlzkuang,pcbzhu,'',isnull(c1.id,''),b.id,i.e_fhren1,i.e_fhren2,i.fhztai,'0.0','', bzhu1,BZhu2,BZhu3,BZhu4,i.e_cjren,i.cjsjian,i.e_xgren,i.xgsjian,'',isnull(e.id,'') from cmt402ls..BillBuy b left join buy_idx i on i.zdyi1 = b.bill_id left join base_storage s1 on s1.bzhu = b.s_id left join base_product p on p.bzhu = b.p_id left join base_client c1 on c1.bzhu = b.c_id2 left join base_employee e on e.bzhu = b.e_ysyuan_id2 left join base_p_location pl on pl.bzhu = b.l_id where i.zdyi1 is not null ~~~ --------零售主表 ~~~ select * from dbo.retail_idx select *from cmt402ls..idxretail insert into retail_idx (id,ydju_lxing,ydju_id,bhao,dju_lxing,lshao,oc_id,dis_id,org_id,au_id, rqi,gzrqi,dyrqi,jsrqi,e_jsren,e_gzren,e_shren,a_id,c_id,ce_id, d_id,v_id,s_id,s_id2,slv,sliang,hjje,cbje,hjyhje,lsje, ysje,ssje,ztai,sfjsuan,jsfshi,jfen,dycshu,cxdan_lxing,cxdan_id,csztai, ysztai,gmrxxi,e_zyyshi,zjlxing,zjhma,bzhu,zdyi1,zdyi2,zdyi3,zdyi4, e_cjren,cjsjian,e_xgren,xgsjian) select (case when billtype = '41' then 'LS' when billtype = '42' then 'LST' end )+ RIGHT('000000000000'+CAST(i.ID AS VARCHAR), 12), '','',bhao,case when billtype = '41' then 'LS' when billtype = '42' then 'LST' end ,'','','','','', rqi,rqi,rqi,rqi,e1.id,e2.id,e2.id,'',c1.id,'', d1.id,isnull(v.id,''),s1.id,'',slv,sliang,hjje,r.cbje,yhui,hjlsje , ysje,ssje,states,'','',isnull(vd.jfen,''),dycshu,cxdlxing,cxorg_id,'0', '',gmrxxi,isnull(e3.id,''),i.zjlxing,i.zjhma,i.id,i.bzhu,zyao,'','', '','','','' from cmt402ls..idxretail i left join base_employee e1 on e1.bzhu = i.e_id left join base_employee e2 on e2.bzhu = i.e_input_id left join base_client c1 on c1.bzhu = i.c_id left join base_department d1 on d1.bzhu = i.d_id left join vip_vipinfo v on v.bzhu = i.v_id left join base_storage s1 on s1.bzhu = i.s_id left join (select bill_id ,sum(sliang*cbjia) cbje from cmt402ls..BillRetail group by bill_id )r on r.bill_id = i.id left join (select bill_id,jfen from cmt402ls..idxvipdetail where billtype = '41' or billtype = '42') vd on vd.bill_id = i.id left join base_employee e3 on e3.bzhu = i.e_yshi_id ~~~ --------零售明細 ~~~ select * from retail_detail select * from cmt402ls..billretail insert into retail_detail ([id],[dju_id],[ydju_lxing],[ydjmxi_id],[kclshao],[gyshang_id],[dju_lxing],[oc_id],[dis_id] ,[org_id],[au_id],[a_id],[s_id],[e_jsren],[p_id],[dwei_idx],[hhao],[crkbzhi],[ztai],[zhsliang] ,[lhsliang],[sliang],[sliang2],[cbjia],[cbje],[jge],[je],[zkou],[zhjia],[zhje] ,[slv],[hsjia],[hsje],[se],[lsjia],[lsje],[pcxjia1],[pcxjia2],[phao],[mjphao],[yxqi] ,[scrqi],[rksjian],[thsliang],[thsjian],[jfen],[cbhsfa],[csztai],[l_id],[cxdan_lxing],[cxdmxi_id] ,[zlzkuang],[pcbzhu1],[pcbzhu2],[hbzhu],[e_fhren1],[e_fhren2],[fhztai],[fhsliang],[zdyi1] ,[bzhu],[zdyi2],[zdyi3],[zdyi4],[e_cjren],[cjsjian],[e_xgren],[xgsjian],[e_ysyuan1],[e_ysyuan2] ) select (case when billtype = '41' then 'LS' when billtype = '42' then 'LST' end )+ RIGHT('000000000000'+CAST(i.ID AS VARCHAR), 12), ri.id ,'','','',isnull(c1.id,''),case when billtype = '41' then 'LS' when billtype = '42' then 'LST' end ,'','', '','','',s1.id,e1.id,p.id,dwei_idx,'',case when billtype = '41' then '1' when billtype = '42' then '-1' end ,'0','0.0', '0.0',i.sliang,i.sliang,cbjia,cbje,jge,je,zkou,zhjia,zhje, i.slv,hsjia,hsje ,se,lsjia,i.lsje,pcxjia1,pcxjia2,phao,'',convert(char(10),yxqi,20), convert(char(10),scrqi,20),rksjian,thsliang,'',jfen,i.cbhsfa,'0',isnull(pl.id,''),cxdan_lxing,cxdan_id, zlzkuang,pcbzhu,'',i.id,'','','','0.0',i.bzhu1, i.bzhu,i.bzhu2,i.bzhu3,i.bzhu4,'','','','','','' from cmt402ls..BillRetail i left join retail_idx ri on ri.bzhu = i.bill_id left join base_client c1 on c1.bzhu = i .c_id2 left join base_storage s1 on s1.bzhu = i.s_id left join base_employee e1 on e1.bzhu = i.bill_e_id left join base_product p on p.bzhu = i.p_id left join base_p_location pl on pl.bzhu = i.l_id ~~~ --------其他出入庫（報損，報溢，盤點，調撥，配送，拆零） ~~~ select * from dbo.store_idx select * from cmt402ls..idxbill where billtype in (60,61,62,63,64,65,66,67,70,71,72,73,80,81) insert into store_idx (id,ydju_lxing,ydju_id,bhao,dju_lxing,oc_id,dis_id,org_id,au_id,rqi, gzrqi,dyrqi,jsrqi,e_jsren,e_gzren,e_shren,a_id,c_id,ce_id, d_id,v_id,s_id,s_id2,slv,sliang,hjje,cbje,hjyhje,lsje, ysje,ssje,ztai,sfjsuan,jsfshi,jfen,dycshu,cxdan_lxing,cxdan_id,ywfpiao, fplxing,fphma,csztai,lxrmcheng,lxdhua,lxdzhi,fyong,bzhu,zdyi1,fhztai,zdyi2, zdyi3,zdyi4,e_cjren,cjsjian,e_xgren,xgsjian,e_fhren1,e_fhren2,e_fhren,psfshi, lhjshu,zhjshu,shrdhua,shrmcheng,zkou,shrdzhi ) select (case when i.billtype = '60' then 'BYD' when i.billtype = '61' then 'BSD' when i.billtype = '66' or i.billtype = '67' then 'DBD' when i.billtype = '71' then 'ZZCLD' when i.billtype = '72' or i.billtype = '73' then 'KCCBPCTZD' when i.billtype = '80' or i.billtype = '81' then 'KCPDD' end )+ RIGHT('000000000000'+CAST(i.ID AS VARCHAR), 12),'','',bhao, case when i.billtype = '60' then 'BYD' when i.billtype = '61' then 'BSD' when i.billtype = '66' or i.billtype = '67' then 'DBD' when i.billtype = '71' then 'ZZCLD' when i.billtype = '72' or i.billtype = '73' then 'KCCBPCTZD' when i.billtype = '80' or i.billtype = '81' then 'KCPDD' end ,'','',i.org_ID,'',rqi, gzrqi,dyrqi,jsrqi,e1.id,e2.id,e3.id ,a_id,isnull(c1.id,''),isnull(ce.id,''), d1.id,isnull(v1.id,''),isnull(s1.id,''),isnull(s2.id,''),slv,sliang,hjje,hjje-yhui,yhui,hjlsje, ysje,ssje,states,jsflag,jsfshi,i.jfen,dycshu,cxdlxing,i.cxorg_id,fpflag, FPLXing,fphma,TranStates,idxlxren,i.lxdhua1,idxdzhi,'0.0',i.id,i.bzhu,'',i.bzhu2, i.bzhu3,i.bzhu4,'admin',gzrqi,'admin',gzrqi,isnull(e6.id,''),'',isnull(e4.id,''),psfshi, lhjshu,zhjshu,isnull(shrdhua,''),isnull(e5.id,''),isnull(zkou,'100.0'),isnull(shrdzhi,'') from cmt402ls..IDXBill i left join base_employee e1 on e1.bzhu = i.e_id left join base_employee e2 on e2.bzhu = i.e_input_id left join base_employee e3 on e3.bzhu = i.e_settle_id left join base_client c1 on c1.bzhu = i.c_id left join base_c_employee ce on ce.bzhu1 = i.ce_id left join base_department d1 on d1.bzhu = i.d_id left join v_vip_vipinfo v1 on v1.bzhu = i.v_id left join base_storage s1 on s1.bzhu = i.s_id left join base_storage s2 on s2.bzhu = i.s_id2 left join base_employee e4 on e4.bzhu = i.e_fhuo_id left join base_employee e5 on e5.bzhu = i.shren left join base_employee e6 on e6.bzhu = i.e_goodscheck_id where i.billtype in (60,61,62,63,64,65,66,67,70,71,72,73,80,81) and i.states = '0' ~~~ --------其他出入庫明細 ~~~ select * from dbo.store_detail select * from cmt402ls..billstore where billtype in (60,61,62,63,64,65,66,67,70,71,72,73,80,81) insert into store_detail ([id],[dju_id],[ydju_lxing],[ydjmxi_id],[kclshao],[gyshang_id],[dju_lxing],[oc_id],[dis_id],[org_id], [au_id],[a_id],[s_id],[e_jsren],[p_id],[dwei_idx],[crkbzhi],[hhao],[ztai], [zhsliang],[lhsliang],[sliang],[cbjia],[cbje],[jge],[je],[lsjia],[lsje],[pcxjia1], zhjia,zhje,hsjia,hsje,slv,se, [pcxjia2],[phao],[mjphao],[yxqi],[scrqi],[rksjian],[kcsliang],[cbhsfa],[csztai],[l_id], [cxdan_lxing],[cxdmxi_id],[pcbzhu1],[pcbzhu2],[hbzhu],[e_fhren1],[e_fhren2],[fhztai],[fhsliang],[zdyi1], [bzhu],[zdyi2],[zdyi3],[zdyi4],[e_cjren],[cjsjian],[e_xgren],[xgsjian],[e_ysyuan1],[e_ysyuan2] ) select (case when b.billtype = '60' then 'BYD' when b.billtype = '61' then 'BSD' when b.billtype = '66' or b.billtype = '67' then 'DBD' when b.billtype = '71' then 'ZZCLD' when b.billtype = '72' or b.billtype = '73' then 'KCCBPCTZD' when b.billtype = '80' or b.billtype = '81' then 'KCPDD' end )+ RIGHT('000000000000'+CAST(b.ID AS VARCHAR), 12), i.id,'','','',c1.id,i.dju_lxing,'','','', '','',s1.id,i.e_jsren,p.id,'0',case when b.sliang > '0' then '1' when b.sliang < '0' then '-1' end ,'',i.ztai, '0.0','0.0',abs(b.sliang),cbjia,abs(b.cbje),jge,abs(je),lsjia,abs(b.lsje),pcxjia1, jge,abs(je),jge,abs(je),'0.0','0.0', pcxjia2,phao,'',convert(char(10),yxqi,20),convert(char(10),scrqi,20),rksjian,'0.0',b.cbhsfa,i.csztai,isnull(pl.id,''), cxdan_lxing,cxdan_id,pcbzhu,'',b.id,i.e_fhren1,i.e_fhren2,i.fhztai,'0.0','', bzhu1,BZhu2,BZhu3,BZhu4,i.e_cjren,i.cjsjian,i.e_xgren,i.xgsjian,'','' from cmt402ls..billstore b left join store_idx i on i.bzhu = b.bill_id left join base_storage s1 on s1.bzhu = b.s_id left join base_product p on p.bzhu = b.p_id left join base_client c1 on c1.bzhu = b.c_id2 left join base_p_location pl on pl.bzhu = b.l_id where billtype in (60,61,62,63,64,65,66,67,70,71,72,73,80,81) ~~~ --------商品流水(庫存出入) ~~~ select * from dbo.store_productdetial select * from buy_detail insert into store_productdetial ([dju_id] ,dju_lxing ,djmxi_id , [kclshao] ,[oc_id] ,[dis_id] , [org_id] ,[au_id] ,[a_id] ,[s_id] ,[e_jsren] ,[p_id] ,[dwei_idx] ,[sliang] ,[cbjia] ,[ztai] ,[cbje] ,[jge] , [je] ,[zkou] ,[zhjia] ,[zhje] ,[slv] ,[hsjia] ,[hsje] ,[se] ,[lsjia] ,[lsje] ,[pcxjia1] ,[pcxjia2] ,[phao] , [mjphao] ,[yxqi] ,[scrqi] ,[rksjian] ,[cbhsfa] ,[csztai] ,[l_id] , [bzhu] , [e_cjren] ,[cjsjian] ,[e_xgren] ,[xgsjian] ,[sliang2] ,cxztai) select dju_id,dju_lxing,id,kclshao,oc_id,dis_id, org_id,au_id ,[a_id] ,[s_id] ,[e_jsren] ,[p_id] ,[dwei_idx] ,[sliang]*crkbzhi ,[cbjia] ,[ztai] ,[cbje]*crkbzhi ,[jge] , [je]*crkbzhi ,[zkou] ,[zhjia] ,[zhje]*crkbzhi ,[slv] ,[hsjia] ,[hsje]*crkbzhi ,[se] ,[lsjia] ,[lsje]*crkbzhi ,[pcxjia1] ,[pcxjia2] ,[phao] , [mjphao] ,[yxqi] ,[scrqi] ,[rksjian] ,[cbhsfa] ,[csztai] ,[l_id] , [bzhu] , [e_cjren] ,[cjsjian] ,[e_xgren] ,[xgsjian] ,[sliang2] ,'' from buy_detail where dju_lxing in ('cgrk','cgth') union all select dju_id,dju_lxing,id,kclshao,oc_id,dis_id, org_id,au_id ,[a_id] ,[s_id] ,[e_jsren] ,[p_id] ,[dwei_idx] ,-[sliang]*crkbzhi ,[cbjia] ,[ztai] ,-[cbje]*crkbzhi ,[jge] , -[je]*crkbzhi ,[zkou] ,[zhjia] ,-[zhje]*crkbzhi ,[slv] ,[hsjia] ,-[hsje]*crkbzhi ,[se] ,[lsjia] ,-[lsje]*crkbzhi ,[pcxjia1] ,[pcxjia2] ,[phao] , [mjphao] ,[yxqi] ,[scrqi] ,[rksjian] ,[cbhsfa] ,[csztai] ,[l_id] , [bzhu] , [e_cjren] ,[cjsjian] ,[e_xgren] ,[xgsjian] ,[sliang2] ,'' from retail_detail union all select dju_id,dju_lxing,id,kclshao,oc_id,dis_id, org_id,au_id ,[a_id] ,[s_id] ,[e_jsren] ,[p_id] ,[dwei_idx] ,[sliang]*crkbzhi ,[cbjia] ,[ztai] ,[cbje]*crkbzhi ,[jge] , [je]*crkbzhi ,[zkou] ,[zhjia] ,[zhje]*crkbzhi ,[slv] ,[hsjia] ,[hsje]*crkbzhi ,[se] ,[lsjia] ,[lsje]*crkbzhi ,[pcxjia1] ,[pcxjia2] ,[phao] , [mjphao] ,[yxqi] ,[scrqi] ,[rksjian] ,[cbhsfa] ,[csztai] ,[l_id] , [bzhu] , [e_cjren] ,[cjsjian] ,[e_xgren] ,[xgsjian] ,[sliang2] ,'' from store_detail ~~~ ## 2、gsp檔案導入使用導入工具直接導入，gsp導入存儲過程為sp_leadincmtgsp；注：一定要在基礎資料和單據導入以后進行。因為導入同樣和備注上的ID有關。注意：如果修改品種，導入的記錄只能取當前時間點的品種的信息。歷史修改記錄無法展示。 ~~~ USE [TLZY] GO /****** Object: StoredProcedure [dbo].[sp_leadincmtgsp] Script Date: 06/07/2018 09:36:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -----要根據tlzy_cmtGSPReport 的orgbilltype區分來源。本腳本取標準GSP流程。 -----主要區別在收貨記錄，驗收記錄上面，來源分idxbill和idxgsp，需對應修改。 -----可能有未完善的記錄，請找開發進行添加，并在腳本里進行相應的添加。 -----缺2713腳本退回驗收。 ALTER PROC [dbo].[sp_leadincmtgsp] ( @tlzy_guid VARCHAR(255), @lxing int , @sjk VARCHAR(255) ) AS BEGIN IF @lxing = '2763' ---庫存藥品養護檢查記錄 kcypyhjcjlu BEGIN INSERT INTO gsp_basedata ([id],[dju_lxing],[gzrqi],[rqi],[org_id],[bhao],[e_shren],[e_xgren],[p_id],[bzhu],[e_cjren],[xgsjian],[cjsjian],[ztai]) SELECT c.id,'KCYPYHJCJL',rqi,rqi,'ORG_ID',bhao,e2.mcheng,e1.mcheng,'',c.bzhu,e2.mcheng,rqi,rqi,'0' FROM tlzy_cmtGSPReport c LEFT JOIN base_employee e1 ON e1.bzhu = c.e_id LEFT JOIN base_employee e2 ON e2.bzhu = c.shren_id WHERE c.gsptype = '2763' INSERT INTO dbo.gsp_basedatadetail ( id ,dju_id ,dju_lxing ,Column1 ,Column2 ,Column3 ,Column4 ,Column5 ,Column6 ,Column7 ,Column8 ,Column9 ,Column10 , Column11 ,Column12 ,Column13 ,Column14 ,Column15 ,Column16 ,Column17 ,Column18 ,Column19 ,Column20 , Column21 ,Column22 ,Column23 ,Column24 ,Column25 ,Column26 ,Column27 ,Column28 ,Column29 ) SELECT SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255)+SN,SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255),'KCYPYHJCJL', [Rqi],[P_mcheng],[p_jming],[p_bma],[p_lxing],[P_gge],'',[P_sccshang],[p_cdi],[P_pzwhao], [p_cctjian],[scrqi],[yxqi],[phao],[p_dwei],[Sliang],[s_mcheng],[l_mcheng],[Jcxmu],[Zlztai], [yhyuan],[Yhffa],[Cljguo],[rksjian],a.[Bzhu],'',t.yhfshi,t.yhtshu,t.id FROM [tlzy2].[dbo].[tlzygsp_kcypyhjcjlu_tmp] a LEFT JOIN base_product t ON a.bzhu = a.p_id END else IF @lxing = '2894' --庫存藥品養護檢查計劃 kcypyhjcjhua BEGIN INSERT INTO gsp_basedata ([id],[dju_lxing],[gzrqi],[rqi],[org_id],[bhao],[e_shren],[e_xgren],[p_id],[bzhu],[e_cjren],[xgsjian],[cjsjian],[ztai] ) SELECT c.id,'KCYPYHJCJH',rqi,rqi,'ORG_ID',bhao,e2.mcheng,e1.mcheng,'',c.bzhu,e2.mcheng,rqi,rqi,'0' FROM tlzy_cmtGSPReport c LEFT JOIN base_employee e1 ON e1.bzhu = c.e_id LEFT JOIN base_employee e2 ON e2.bzhu = c.shren_id WHERE c.gsptype = '2894' INSERT INTO dbo.gsp_basedatadetail ( id ,dju_id ,dju_lxing ,Column1 ,Column2 ,Column3 ,Column4 ,Column5 ,Column6 ,Column7 ,Column8 ,Column9 ,Column10 , Column11 ,Column12 ,Column13 ,Column14 ,Column15 ,Column16 ,Column17 ,Column18 ,Column19 ,Column20 , Column21 ,Column22 ,Column23 ,Column24 ,Column25 ,Column26 ,Column27 ,Column28 ,Column29 ) SELECT SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255)+SN,SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255),'KCYPYHJCJH', [Rqi],[P_mcheng],[p_jming],[p_bma],[p_lxing],[P_gge],'',[P_sccshang],[p_cdi],[P_pzwhao], [p_cctjian],[scrqi],[yxqi],[phao],[p_dwei],[Sliang],[s_mcheng],[l_mcheng],[Jcxmu],[Zlztai], [yhyuan],[Yhffa],[Cljguo],[rksjian],a.[Bzhu],'',t.yhfshi,t.yhtshu,t.id FROM [tlzy2].[dbo].[tlzygsp_kcypyhjcjhua_tmp] a LEFT JOIN base_product t ON a.bzhu = a.p_id END else IF @lxing = '2774' ---陳列藥品養護檢查記錄 clypzljcjlu BEGIN INSERT INTO gsp_basedata ([id],[dju_lxing],[gzrqi],[rqi],[org_id],[bhao],[e_shren],[e_xgren],[p_id],[bzhu],[e_cjren],[xgsjian],[cjsjian],[ztai] ) SELECT c.id,'CLYPZLJCJL',rqi,rqi,'ORG_ID',bhao,e2.mcheng,e1.mcheng,'',c.bzhu,e2.mcheng,rqi,rqi,'0' FROM tlzy_cmtGSPReport c LEFT JOIN base_employee e1 ON e1.bzhu = c.e_id LEFT JOIN base_employee e2 ON e2.bzhu = c.shren_id WHERE c.gsptype = '2774' INSERT INTO dbo.gsp_basedatadetail ( id ,dju_id ,dju_lxing ,Column1 ,Column2 ,Column3 ,Column4 ,Column5 ,Column6 ,Column7 ,Column8 ,Column9 ,Column10 , Column11 ,Column12 ,Column13 ,Column14 ,Column15 ,Column16 ,Column17 ,Column18 ,Column19 ,Column20 , Column21 ,Column22 ,Column23 ,Column24 ,Column25 ,Column26 ,Column27 ,Column28 ,Column29 ) SELECT SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255)+SN,SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255),'CLYPZLJCJL', [Rqi],[P_mcheng],[p_jming],[p_bma],[p_lxing],[P_gge],'',[P_sccshang],[p_cdi],[P_pzwhao], [p_cctjian],[scrqi],[yxqi],[phao],[p_dwei],[Sliang],[s_mcheng],[l_mcheng],[Jcxmu],[Zlztai], [yhyuan],[Yhffa],[Cljguo],[rksjian],a.[Bzhu],'',t.yhfshi,t.yhtshu,t.id FROM [tlzy2].[dbo].[tlzygsp_clypzljcjlu_tmp] a LEFT JOIN base_product t ON a.bzhu = a.p_id END ELSE IF @lxing = '2706' ---藥品采購記錄 dbo.tlzygsp_ypgjjlu_tmp BEGIN DECLARE @sql nvarchar(4000) SET @sql = ' INSERT INTO gsp_idx(id,dju_lxing,bhao,org_id,rqi,gzrqi,e_jsren,e_shren,c_id,d_id,v_id,s_id,s_id2,slv,sliang,hjje, cbje,hjyhje,lsje,ysje,yjje,ssje,gsplcheng,ztai,jfen,bzhu,e_cjren,cjsjian,e_xgren,xgsjian, cgywyuan,gmrxxi,e_zyyshi,zjlxing,zjhma,djlcheng,cflyuan,cfkjsjian,e_tpren,e_hdren,ysxming) SELECT c.id,''YPCGJL'',c.bhao,''ORG_ID'',c.rqi,c.rqi,e1.mcheng,e2.mcheng,a.c_id,'''','''',a.s_id,'''',0.0,0.0,0.0, 0.0,0.0,0.0,0.0,0.0,0.0,''1'',''0'',0.0,c.bzhu,e1.mcheng,c.rqi,e1.mcheng,c.rqi, '''','''','''','''','''',''2'','''',c.rqi,'''','''','''' FROM tlzy_cmtGSPReport c LEFT JOIN '+@sjk+'..idxbill a ON a.id = c.orgbillid LEFT JOIN base_employee e1 ON e1.bzhu = c.e_id LEFT JOIN base_employee e2 ON e2.bzhu = c.shren_id WHERE c.gsptype = ''2706''' EXEC (@sql) INSERT INTO gsp_detail ( [id],[dju_id],[ydju_lxing],[ydjmxi_id],[dju_lxing],[crkbzhi],[hhao],[kclshao],[oc_id],[dis_id],[org_id],[au_id], [a_id],[s_id],[e_jsren],[p_id],[dwei_idx],[ztai],[zhsliang],[lhsliang],[sliang],[sliang2],[cbjia],[cbje],[jge], [je],[zkou],[zhjia],[zhje],[slv],[hsjia],[hsje],[lsjia],[lsje],[pcxjia1],[pcxjia2],[phao],[mjphao],[yxqi],[scrqi], [rksjian],[thsliang],[thsjian],[jfen],[cbhsfa],[csztai],[l_id],[cxdan_lxing],[cxdmxi_id],[zlzkuang],[pcbzhu1], [pcbzhu2],[gyshang_id],[hbzhu],[e_fhren1],[e_fhren2],[fhztai],[shsliang],[hgsliang],[bhgsliang],[cysliang],[yssliang], [jssliang],[fhsliang],[ysddian],[bhgclcshi],[bhgyyin],[jsyyin],[shren],[yssjian],[shsjian],[zdyi1],[bzhu],[zdyi2], [zdyi3],[zdyi4],[e_cjren],[cjsjian],[e_xgren],[qysjian],[dhsjian],[xgsjian],[ysjlun],[qywsdu],[dhwsdu],[cytjian], [ysgjjcqkuang],[ysgju],[e_ysyuan1],[e_ysyuan2],[ddsliang],[fyddian],[wkfshi],[gsplcheng],[tlzy_transztai],[tlzy_transguid]) SELECT SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255)+SN,SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255), '','','YPCGJL','1',sn,'','','','ORG_ID','','','',e1.id,p.id,'0','0',0.0,0.0,sliang,sliang,jge,je,jge, je,100.00,jge,je,0.0,jge,je,jge,je,0.0,0.0,phao,'',yxqi,scrqi, rqi,0.0,'1900-01-01 00:00:00.000',0.0,'0','0','','','','','', '',c.id,'','','','0',sliang,sliang,0.0,0.0,sliang, 0.0,0.0,'','','','','','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','','','', '','',e1.id,rqi,e1.id,'1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','合格','','','', '','','','',sliang,'','','0','0',SUBSTRING(tlzy_guid,0,CHARINDEX('|',tlzy_guid)) FROM tlzygsp_ypgjjlu_tmp y LEFT JOIN base_employee e1 ON e1.bzhu = y.e_id LEFT JOIN base_product p ON p.bzhu = y.p_id LEFT JOIN base_client c ON c.bzhu = y.c_id UPDATE gsp_idx SET c_id = t.id FROM gsp_idx i ,base_client t WHERE i.dju_lxing = 'YPCGJL' AND i.id = t.bzhu UPDATE gsp_idx SET s_id = t.id FROM gsp_idx i ,base_storage t WHERE i.dju_lxing = 'YPCGJL' AND i.id = t.bzhu END ELSE IF @lxing = '2870' ---醫療器械采購記錄 tlzygsp_ylqxgjjlu_tmp BEGIN DECLARE @sql1 nvarchar(4000) SET @sql1 = ' INSERT INTO gsp_idx(id,dju_lxing,bhao,org_id,rqi,gzrqi,e_jsren,e_shren,c_id,d_id,v_id,s_id,s_id2,slv,sliang,hjje, cbje,hjyhje,lsje,ysje,yjje,ssje,gsplcheng,ztai,jfen,bzhu,e_cjren,cjsjian,e_xgren,xgsjian, cgywyuan,gmrxxi,e_zyyshi,zjlxing,zjhma,djlcheng,cflyuan,cfkjsjian,e_tpren,e_hdren,ysxming) SELECT c.id,''YLQXCGJL'',c.bhao,''ORG_ID'',c.rqi,c.rqi,e1.mcheng,e2.mcheng,a.c_id,'''','''',a.s_id,'''',0.0,0.0,0.0, 0.0,0.0,0.0,0.0,0.0,0.0,''1'',''0'',0.0,c.bzhu,e1.mcheng,c.rqi,e1.mcheng,c.rqi, '''','''','''','''','''',''2'','''',c.rqi,'''','''','''' FROM tlzy_cmtGSPReport c LEFT JOIN '+@sjk+'..idxbill a ON a.id = c.orgbillid LEFT JOIN base_employee e1 ON e1.bzhu = c.e_id LEFT JOIN base_employee e2 ON e2.bzhu = c.shren_id WHERE c.gsptype = ''2870''' EXEC (@sql1) INSERT INTO gsp_detail ( [id],[dju_id],[ydju_lxing],[ydjmxi_id],[dju_lxing],[crkbzhi],[hhao],[kclshao],[oc_id],[dis_id],[org_id],[au_id], [a_id],[s_id],[e_jsren],[p_id],[dwei_idx],[ztai],[zhsliang],[lhsliang],[sliang],[sliang2],[cbjia],[cbje],[jge], [je],[zkou],[zhjia],[zhje],[slv],[hsjia],[hsje],[lsjia],[lsje],[pcxjia1],[pcxjia2],[phao],[mjphao],[yxqi],[scrqi], [rksjian],[thsliang],[thsjian],[jfen],[cbhsfa],[csztai],[l_id],[cxdan_lxing],[cxdmxi_id],[zlzkuang],[pcbzhu1], [pcbzhu2],[gyshang_id],[hbzhu],[e_fhren1],[e_fhren2],[fhztai],[shsliang],[hgsliang],[bhgsliang],[cysliang],[yssliang], [jssliang],[fhsliang],[ysddian],[bhgclcshi],[bhgyyin],[jsyyin],[shren],[yssjian],[shsjian],[zdyi1],[bzhu],[zdyi2], [zdyi3],[zdyi4],[e_cjren],[cjsjian],[e_xgren],[qysjian],[dhsjian],[xgsjian],[ysjlun],[qywsdu],[dhwsdu],[cytjian], [ysgjjcqkuang],[ysgju],[e_ysyuan1],[e_ysyuan2],[ddsliang],[fyddian],[wkfshi],[gsplcheng],[tlzy_transztai],[tlzy_transguid]) SELECT SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255)+SN,SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255), '','','YLQXCGJL','1',sn,'','','','ORG_ID','','','',e1.id,p.id,'0','0',0.0,0.0,sliang,sliang,jge,je,jge, je,100.00,jge,je,0.0,jge,je,jge,je,0.0,0.0,phao,'',yxqi,scrqi, rqi,0.0,'1900-01-01 00:00:00.000',0.0,'0','0','','','','','', '',c.id,'','','','0',sliang,sliang,0.0,0.0,sliang, 0.0,0.0,'','','','','','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','','','', '','',e1.id,rqi,e1.id,'1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','合格','','','', '','','','',sliang,'','','0','0',SUBSTRING(tlzy_guid,0,CHARINDEX('|',tlzy_guid)) FROM tlzygsp_ylqxgjjlu_tmp y LEFT JOIN base_employee e1 ON e1.bzhu = y.e_id LEFT JOIN base_product p ON p.bzhu = y.p_id LEFT JOIN base_client c ON c.bzhu = y.c_id UPDATE gsp_idx SET c_id = t.id FROM gsp_idx i ,base_client t WHERE i.dju_lxing = 'YLQXCGJL' AND i.id = t.bzhu UPDATE gsp_idx SET s_id = t.id FROM gsp_idx i ,base_storage t WHERE i.dju_lxing = 'YLQXCGJL' AND i.id = t.bzhu END ELSE IF @lxing = '2871' ---中藥飲片采購記錄 tlzygsp_zyczyypgjjlu_tmp BEGIN DECLARE @sql2 nvarchar(4000) SET @sql2 = ' INSERT INTO gsp_idx(id,dju_lxing,bhao,org_id,rqi,gzrqi,e_jsren,e_shren,c_id,d_id,v_id,s_id,s_id2,slv,sliang,hjje, cbje,hjyhje,lsje,ysje,yjje,ssje,gsplcheng,ztai,jfen,bzhu,e_cjren,cjsjian,e_xgren,xgsjian, cgywyuan,gmrxxi,e_zyyshi,zjlxing,zjhma,djlcheng,cflyuan,cfkjsjian,e_tpren,e_hdren,ysxming) SELECT c.id,''ZYYPCGJL'',c.bhao,''ORG_ID'',c.rqi,c.rqi,e1.mcheng,e2.mcheng,a.c_id,'''','''',a.s_id,'''',0.0,0.0,0.0, 0.0,0.0,0.0,0.0,0.0,0.0,''1'',''0'',0.0,c.bzhu,e1.mcheng,c.rqi,e1.mcheng,c.rqi, '''','''','''','''','''',''2'','''',c.rqi,'''','''','''' FROM tlzy_cmtGSPReport c LEFT JOIN '+@sjk+'..idxbill a ON a.id = c.orgbillid LEFT JOIN base_employee e1 ON e1.bzhu = c.e_id LEFT JOIN base_employee e2 ON e2.bzhu = c.shren_id WHERE c.gsptype = ''2871''' EXEC (@sql2) INSERT INTO gsp_detail ( [id],[dju_id],[ydju_lxing],[ydjmxi_id],[dju_lxing],[crkbzhi],[hhao],[kclshao],[oc_id],[dis_id],[org_id],[au_id], [a_id],[s_id],[e_jsren],[p_id],[dwei_idx],[ztai],[zhsliang],[lhsliang],[sliang],[sliang2],[cbjia],[cbje],[jge], [je],[zkou],[zhjia],[zhje],[slv],[hsjia],[hsje],[lsjia],[lsje],[pcxjia1],[pcxjia2],[phao],[mjphao],[yxqi],[scrqi], [rksjian],[thsliang],[thsjian],[jfen],[cbhsfa],[csztai],[l_id],[cxdan_lxing],[cxdmxi_id],[zlzkuang],[pcbzhu1], [pcbzhu2],[gyshang_id],[hbzhu],[e_fhren1],[e_fhren2],[fhztai],[shsliang],[hgsliang],[bhgsliang],[cysliang],[yssliang], [jssliang],[fhsliang],[ysddian],[bhgclcshi],[bhgyyin],[jsyyin],[shren],[yssjian],[shsjian],[zdyi1],[bzhu],[zdyi2], [zdyi3],[zdyi4],[e_cjren],[cjsjian],[e_xgren],[qysjian],[dhsjian],[xgsjian],[ysjlun],[qywsdu],[dhwsdu],[cytjian], [ysgjjcqkuang],[ysgju],[e_ysyuan1],[e_ysyuan2],[ddsliang],[fyddian],[wkfshi],[gsplcheng],[tlzy_transztai],[tlzy_transguid]) SELECT SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255)+SN,SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255), '','','ZYYPCGJL','1',sn,'','','','ORG_ID','','','',e1.id,p.id,'0','0',0.0,0.0,sliang,sliang,jge,je,jge, je,100.00,jge,je,0.0,jge,je,jge,je,0.0,0.0,phao,'',yxqi,scrqi, rqi,0.0,'1900-01-01 00:00:00.000',0.0,'0','0','','','','','', '',c.id,'','','','0',sliang,sliang,0.0,0.0,sliang, 0.0,0.0,'','','','','','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','','','', '','',e1.id,rqi,e1.id,'1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','合格','','','', '','','','',sliang,'','','0','0',SUBSTRING(tlzy_guid,0,CHARINDEX('|',tlzy_guid)) FROM tlzygsp_zyczyypgjjlu_tmp y LEFT JOIN base_employee e1 ON e1.bzhu = y.e_id LEFT JOIN base_product p ON p.bzhu = y.p_id LEFT JOIN base_client c ON c.bzhu = y.c_id UPDATE gsp_idx SET c_id = t.id FROM gsp_idx i ,base_client t WHERE i.dju_lxing = 'ZYYPCGJL' AND i.id = t.bzhu UPDATE gsp_idx SET s_id = t.id FROM gsp_idx i ,base_storage t WHERE i.dju_lxing = 'ZYYPCGJL' AND i.id = t.bzhu END ELSE IF @lxing = '2850' ---生物制品采購記錄 tlzygsp_swzpgjjlu_tmp BEGIN DECLARE @sql3 NVARCHAR(4000) SET @sql3 = ' INSERT INTO gsp_idx(id,dju_lxing,bhao,org_id,rqi,gzrqi,e_jsren,e_shren,c_id,d_id,v_id,s_id,s_id2,slv,sliang,hjje, cbje,hjyhje,lsje,ysje,yjje,ssje,gsplcheng,ztai,jfen,bzhu,e_cjren,cjsjian,e_xgren,xgsjian, cgywyuan,gmrxxi,e_zyyshi,zjlxing,zjhma,djlcheng,cflyuan,cfkjsjian,e_tpren,e_hdren,ysxming) SELECT c.id,''SWZPCGJL'',c.bhao,''ORG_ID'',c.rqi,c.rqi,e1.mcheng,e2.mcheng,a.c_id,'''','''',a.s_id,'''',0.0,0.0,0.0, 0.0,0.0,0.0,0.0,0.0,0.0,''1'',''0'',0.0,c.bzhu,e1.mcheng,c.rqi,e1.mcheng,c.rqi, '''','''','''','''','''',''2'','''',c.rqi,'''','''','''' FROM tlzy_cmtGSPReport c LEFT JOIN '+@sjk+'..idxbill a ON a.id = c.orgbillid LEFT JOIN base_employee e1 ON e1.bzhu = c.e_id LEFT JOIN base_employee e2 ON e2.bzhu = c.shren_id WHERE c.gsptype = ''2850''' EXEC (@sql3) INSERT INTO gsp_detail ( [id],[dju_id],[ydju_lxing],[ydjmxi_id],[dju_lxing],[crkbzhi],[hhao],[kclshao],[oc_id],[dis_id],[org_id],[au_id], [a_id],[s_id],[e_jsren],[p_id],[dwei_idx],[ztai],[zhsliang],[lhsliang],[sliang],[sliang2],[cbjia],[cbje],[jge], [je],[zkou],[zhjia],[zhje],[slv],[hsjia],[hsje],[lsjia],[lsje],[pcxjia1],[pcxjia2],[phao],[mjphao],[yxqi],[scrqi], [rksjian],[thsliang],[thsjian],[jfen],[cbhsfa],[csztai],[l_id],[cxdan_lxing],[cxdmxi_id],[zlzkuang],[pcbzhu1], [pcbzhu2],[gyshang_id],[hbzhu],[e_fhren1],[e_fhren2],[fhztai],[shsliang],[hgsliang],[bhgsliang],[cysliang],[yssliang], [jssliang],[fhsliang],[ysddian],[bhgclcshi],[bhgyyin],[jsyyin],[shren],[yssjian],[shsjian],[zdyi1],[bzhu],[zdyi2], [zdyi3],[zdyi4],[e_cjren],[cjsjian],[e_xgren],[qysjian],[dhsjian],[xgsjian],[ysjlun],[qywsdu],[dhwsdu],[cytjian], [ysgjjcqkuang],[ysgju],[e_ysyuan1],[e_ysyuan2],[ddsliang],[fyddian],[wkfshi],[gsplcheng],[tlzy_transztai],[tlzy_transguid]) SELECT SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255)+SN,SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255), '','','SWZPCGJL','1',sn,'','','','ORG_ID','','','',e1.id,p.id,'0','0',0.0,0.0,sliang,sliang,jge,je,jge, je,100.00,jge,je,0.0,jge,je,jge,je,0.0,0.0,phao,'',yxqi,scrqi, rqi,0.0,'1900-01-01 00:00:00.000',0.0,'0','0','','','','','', '',c.id,'','','','0',sliang,sliang,0.0,0.0,sliang, 0.0,0.0,'','','','','','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','','','', '','',e1.id,rqi,e1.id,'1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','合格','','','', '','','','',sliang,'','','0','0',SUBSTRING(tlzy_guid,0,CHARINDEX('|',tlzy_guid)) FROM tlzygsp_swzpgjjlu_tmp y LEFT JOIN base_employee e1 ON e1.bzhu = y.e_id LEFT JOIN base_product p ON p.bzhu = y.p_id LEFT JOIN base_client c ON c.bzhu = y.c_id UPDATE gsp_idx SET c_id = t.id FROM gsp_idx i ,base_client t WHERE i.dju_lxing = 'SWZPCGJL' AND i.id = t.bzhu UPDATE gsp_idx SET s_id = t.id FROM gsp_idx i ,base_storage t WHERE i.dju_lxing = 'SWZPCGJL' AND i.id = t.bzhu END ELSE IF @lxing = '2732' ---購進藥品退出記錄 tlzygsp_gjyptcjlu_tmp BEGIN DECLARE @sql4 NVARCHAR(4000) SET @sql4 = ' INSERT INTO gsp_idx(id,dju_lxing,bhao,org_id,rqi,gzrqi,e_jsren,e_shren,c_id,d_id,v_id,s_id,s_id2,slv,sliang,hjje, cbje,hjyhje,lsje,ysje,yjje,ssje,gsplcheng,ztai,jfen,bzhu,e_cjren,cjsjian,e_xgren,xgsjian, cgywyuan,gmrxxi,e_zyyshi,zjlxing,zjhma,djlcheng,cflyuan,cfkjsjian,e_tpren,e_hdren,ysxming) SELECT c.id,''GJYPTCJL'',c.bhao,''ORG_ID'',c.rqi,c.rqi,e1.mcheng,e2.mcheng,a.c_id,'''','''',a.s_id,'''',0.0,0.0,0.0, 0.0,0.0,0.0,0.0,0.0,0.0,''1'',''0'',0.0,c.bzhu,e1.mcheng,c.rqi,e1.mcheng,c.rqi, '''','''','''','''','''',''2'','''',c.rqi,'''','''','''' FROM tlzy_cmtGSPReport c LEFT JOIN '+@sjk+'..idxbill a ON a.id = c.orgbillid LEFT JOIN base_employee e1 ON e1.bzhu = c.e_id LEFT JOIN base_employee e2 ON e2.bzhu = c.shren_id WHERE c.gsptype = ''2732''' EXEC (@sql4) INSERT INTO gsp_detail -----s_id 問題 ( [id],[dju_id],[ydju_lxing],[ydjmxi_id],[dju_lxing],[crkbzhi],[hhao],[kclshao],[oc_id],[dis_id],[org_id],[au_id], [a_id],[s_id],[e_jsren],[p_id],[dwei_idx],[ztai],[zhsliang],[lhsliang],[sliang],[sliang2],[cbjia],[cbje],[jge], [je],[zkou],[zhjia],[zhje],[slv],[hsjia],[hsje],[lsjia],[lsje],[pcxjia1],[pcxjia2],[phao],[mjphao],[yxqi],[scrqi], [rksjian],[thsliang],[thsjian],[jfen],[cbhsfa],[csztai],[l_id],[cxdan_lxing],[cxdmxi_id],[zlzkuang],[pcbzhu1], [pcbzhu2],[gyshang_id],[hbzhu],[e_fhren1],[e_fhren2],[fhztai],[shsliang],[hgsliang],[bhgsliang],[cysliang],[yssliang], [jssliang],[fhsliang],[ysddian],[bhgclcshi],[bhgyyin],[jsyyin],[shren],[yssjian],[shsjian],[zdyi1],[bzhu],[zdyi2], [zdyi3],[zdyi4],[e_cjren],[cjsjian],[e_xgren],[qysjian],[dhsjian],[xgsjian],[ysjlun],[qywsdu],[dhwsdu],[cytjian], [ysgjjcqkuang],[ysgju],[e_ysyuan1],[e_ysyuan2],[ddsliang],[fyddian],[wkfshi],[gsplcheng],[tlzy_transztai],[tlzy_transguid]) SELECT SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255)+SN,SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255), '','','GJYPTCJL','1',sn,'','','','ORG_ID','','','',e1.id,p.id,'0','0',0.0,0.0,sliang,sliang,0.0,0.0,0.0, 0.0,100.00,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,phao,'',yxqi,scrqi, rqi,0.0,'1900-01-01 00:00:00.000',0.0,'0','0','','','','','', '',c.id,'','','','0',sliang,sliang,0.0,0.0,sliang, 0.0,0.0,'','','','','','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','','','', '','',e1.id,rqi,e1.id,'1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','合格','','','', '','','','',sliang,'','','0','0',SUBSTRING(tlzy_guid,0,CHARINDEX('|',tlzy_guid)) FROM tlzygsp_gjyptcjlu_tmp y LEFT JOIN base_employee e1 ON e1.bzhu = y.e_id LEFT JOIN base_product p ON p.bzhu = y.p_id LEFT JOIN base_client c ON c.bzhu = y.c_id UPDATE gsp_idx SET c_id = t.id FROM gsp_idx i ,base_client t WHERE i.dju_lxing = 'GJYPTCJL' AND i.id = t.bzhu UPDATE gsp_idx SET s_id = t.id FROM gsp_idx i ,base_storage t WHERE i.dju_lxing = 'GJYPTCJL' AND i.id = t.bzhu END ELSE IF @lxing = '2898' ---藥品收貨記錄 tlzygsp_ypshjlu_tmp BEGIN DECLARE @sql5 NVARCHAR(4000) SET @sql5 = ' INSERT INTO gsp_idx(id,dju_lxing,bhao,org_id,rqi,gzrqi,e_jsren,e_shren,c_id,d_id,v_id,s_id,s_id2,slv,sliang,hjje, cbje,hjyhje,lsje,ysje,yjje,ssje,gsplcheng,ztai,jfen,bzhu,e_cjren,cjsjian,e_xgren,xgsjian, cgywyuan,gmrxxi,e_zyyshi,zjlxing,zjhma,djlcheng,cflyuan,cfkjsjian,e_tpren,e_hdren,ysxming) SELECT c.id,''YPSHJL'',c.bhao,''ORG_ID'',c.rqi,c.rqi,e1.mcheng,e2.mcheng,a.c_id,'''','''',a.s_id,'''',0.0,0.0,0.0, 0.0,0.0,0.0,0.0,0.0,0.0,''1'',''0'',0.0,c.bzhu,e1.mcheng,c.rqi,e1.mcheng,c.rqi, '''','''','''','''','''',''2'','''',c.rqi,'''','''','''' FROM tlzy_cmtGSPReport c LEFT JOIN '+@sjk+'..idxgsp a ON a.id = c.orgbillid LEFT JOIN base_employee e1 ON e1.bzhu = c.e_id LEFT JOIN base_employee e2 ON e2.bzhu = c.shren_id WHERE c.gsptype = ''2898''' EXEC (@sql5) INSERT INTO gsp_detail ( [id],[dju_id],[ydju_lxing],[ydjmxi_id],[dju_lxing],[crkbzhi],[hhao],[kclshao],[oc_id],[dis_id],[org_id],[au_id], [a_id],[s_id],[e_jsren],[p_id],[dwei_idx],[ztai],[zhsliang],[lhsliang],[sliang],[sliang2],[cbjia],[cbje],[jge], [je],[zkou],[zhjia],[zhje],[slv],[hsjia],[hsje],[lsjia],[lsje],[pcxjia1],[pcxjia2],[phao],[mjphao],[yxqi],[scrqi], [rksjian],[thsliang],[thsjian],[jfen],[cbhsfa],[csztai],[l_id],[cxdan_lxing],[cxdmxi_id],[zlzkuang],[pcbzhu1], [pcbzhu2],[gyshang_id],[hbzhu],[e_fhren1],[e_fhren2],[fhztai],[shsliang],[hgsliang],[bhgsliang],[cysliang],[yssliang], [jssliang],[fhsliang],[ysddian],[bhgclcshi],[bhgyyin],[jsyyin],[shren],[yssjian],[shsjian],[zdyi1],[bzhu],[zdyi2], [zdyi3],[zdyi4],[e_cjren],[cjsjian],[e_xgren],[qysjian],[dhsjian],[xgsjian],[ysjlun],[qywsdu],[dhwsdu],[cytjian], [ysgjjcqkuang],[ysgju],[e_ysyuan1],[e_ysyuan2],[ddsliang],[fyddian],[wkfshi],[gsplcheng],[tlzy_transztai],[tlzy_transguid]) SELECT SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255)+SN,SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255), '','','YPSHJL','1',sn,'','','','ORG_ID','','','',e1.id,p.id,'0','0',0.0,0.0,sliang,sliang,jge,je,jge, je,100.00,jge,je,0.0,jge,je,jge,je,0.0,0.0,phao,'',yxqi,scrqi, rqi,0.0,'1900-01-01 00:00:00.000',0.0,'0','0','','','','','', '',c.id,'','','','0',sliang,sliang,0.0,0.0,sliang, 0.0,0.0,'','','','','','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','','','', '','',e1.id,rqi,e1.id,qyrqi,y.shsjian,'1900-01-01 00:00:00.000','合格','',y.dhswdu,cytjian, '合格',ysgju,'','',sliang,'',cytjian,'0','0',SUBSTRING(tlzy_guid,0,CHARINDEX('|',tlzy_guid)) FROM tlzygsp_ypshjlu_tmp y LEFT JOIN base_employee e1 ON e1.bzhu = y.e_id LEFT JOIN base_product p ON p.bzhu = y.p_id LEFT JOIN base_client c ON c.bzhu = y.c_id UPDATE gsp_idx SET c_id = t.id FROM gsp_idx i ,base_client t WHERE i.dju_lxing = 'YPSHJL' AND i.id = t.bzhu UPDATE gsp_idx SET s_id = t.id FROM gsp_idx i ,base_storage t WHERE i.dju_lxing = 'YPSHJL' AND i.id = t.bzhu END ELSE IF @lxing = '2712' ---藥品驗收抽樣記錄 tlzygsp_ypyscyjlu_tmp BEGIN DECLARE @sql6 NVARCHAR(4000) SET @sql6 = ' INSERT INTO gsp_idx(id,dju_lxing,bhao,org_id,rqi,gzrqi,e_jsren,e_shren,c_id,d_id,v_id,s_id,s_id2,slv,sliang,hjje, cbje,hjyhje,lsje,ysje,yjje,ssje,gsplcheng,ztai,jfen,bzhu,e_cjren,cjsjian,e_xgren,xgsjian, cgywyuan,gmrxxi,e_zyyshi,zjlxing,zjhma,djlcheng,cflyuan,cfkjsjian,e_tpren,e_hdren,ysxming) SELECT c.id,''YPYSCYJL'',c.bhao,''ORG_ID'',c.rqi,c.rqi,e1.mcheng,e2.mcheng,a.c_id,'''','''',a.s_id,'''',0.0,0.0,0.0, 0.0,0.0,0.0,0.0,0.0,0.0,''1'',''0'',0.0,c.bzhu,e1.mcheng,c.rqi,e1.mcheng,c.rqi, '''','''','''','''','''',''2'','''',c.rqi,'''','''','''' FROM tlzy_cmtGSPReport c LEFT JOIN '+@sjk+'..idxgsp a ON a.id = c.orgbillid LEFT JOIN base_employee e1 ON e1.bzhu = c.e_id LEFT JOIN base_employee e2 ON e2.bzhu = c.shren_id WHERE c.gsptype = ''2712''' EXEC (@sql6) INSERT INTO gsp_detail -----s_id 問題 ( [id],[dju_id],[ydju_lxing],[ydjmxi_id],[dju_lxing],[crkbzhi],[hhao],[kclshao],[oc_id],[dis_id],[org_id],[au_id], [a_id],[s_id],[e_jsren],[p_id],[dwei_idx],[ztai],[zhsliang],[lhsliang],[sliang],[sliang2],[cbjia],[cbje],[jge], [je],[zkou],[zhjia],[zhje],[slv],[hsjia],[hsje],[lsjia],[lsje],[pcxjia1],[pcxjia2],[phao],[mjphao],[yxqi],[scrqi], [rksjian],[thsliang],[thsjian],[jfen],[cbhsfa],[csztai],[l_id],[cxdan_lxing],[cxdmxi_id],[zlzkuang],[pcbzhu1], [pcbzhu2],[gyshang_id],[hbzhu],[e_fhren1],[e_fhren2],[fhztai],[shsliang],[hgsliang],[bhgsliang],[cysliang],[yssliang], [jssliang],[fhsliang],[ysddian],[bhgclcshi],[bhgyyin],[jsyyin],[shren],[yssjian],[shsjian],[zdyi1],[bzhu],[zdyi2], [zdyi3],[zdyi4],[e_cjren],[cjsjian],[e_xgren],[qysjian],[dhsjian],[xgsjian],[ysjlun],[qywsdu],[dhwsdu],[cytjian], [ysgjjcqkuang],[ysgju],[e_ysyuan1],[e_ysyuan2],[ddsliang],[fyddian],[wkfshi],[gsplcheng],[tlzy_transztai],[tlzy_transguid]) SELECT SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255)+SN,SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255), '','','YPYSCYJL','1',sn,'','','','ORG_ID','','','',e1.id,p.id,'0','0',0.0,0.0,sliang,sliang,0.0,0.0,0.0, 0.0,100.00,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,phao,'',yxqi,scrqi, rqi,0.0,'1900-01-01 00:00:00.000',0.0,'0','0','','','','','', '','','','','','0',sliang,sliang,0.0,gpzjs,sliang, 0.0,0.0,'','','','','','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','','','', '','',e1.id,rqi,e1.id,'','','1900-01-01 00:00:00.000','合格','','','', '合格','','','',sliang,'','','0','0',SUBSTRING(tlzy_guid,0,CHARINDEX('|',tlzy_guid)) FROM tlzygsp_ypyscyjlu_tmp y LEFT JOIN base_employee e1 ON e1.bzhu = y.e_id LEFT JOIN base_product p ON p.bzhu = y.p_id UPDATE gsp_idx SET c_id = t.id FROM gsp_idx i ,base_client t WHERE i.dju_lxing = 'YPYSCYJL' AND i.id = t.bzhu UPDATE gsp_idx SET s_id = t.id FROM gsp_idx i ,base_storage t WHERE i.dju_lxing = 'YPYSCYJL' AND i.id = t.bzhu END ELSE IF @lxing = '2741' ---藥品驗收記錄 tlzygsp_gjypysjlu_tmp begin DECLARE @sql7 NVARCHAR(4000) SET @sql7 = ' INSERT INTO gsp_idx(id,dju_lxing,bhao,org_id,rqi,gzrqi,e_jsren,e_shren,c_id,d_id,v_id,s_id,s_id2,slv,sliang,hjje, cbje,hjyhje,lsje,ysje,yjje,ssje,gsplcheng,ztai,jfen,bzhu,e_cjren,cjsjian,e_xgren,xgsjian, cgywyuan,gmrxxi,e_zyyshi,zjlxing,zjhma,djlcheng,cflyuan,cfkjsjian,e_tpren,e_hdren,ysxming) SELECT c.id,''YPYSJL'',c.bhao,''ORG_ID'',c.rqi,c.rqi,e1.mcheng,e2.mcheng,a.c_id,'''','''',a.s_id,'''',0.0,0.0,0.0, 0.0,0.0,0.0,0.0,0.0,0.0,''1'',''0'',0.0,c.bzhu,e1.mcheng,c.rqi,e1.mcheng,c.rqi, '''','''','''','''','''',''2'','''',c.rqi,'''','''','''' FROM tlzy_cmtGSPReport c LEFT JOIN '+@sjk+'..idxgsp a ON a.id = c.orgbillid LEFT JOIN base_employee e1 ON e1.bzhu = c.e_id LEFT JOIN base_employee e2 ON e2.bzhu = c.shren_id WHERE c.gsptype = ''2741''' EXEC (@sql7) INSERT INTO gsp_detail -----s_id 問題 ( [id],[dju_id],[ydju_lxing],[ydjmxi_id],[dju_lxing],[crkbzhi],[hhao],[kclshao],[oc_id],[dis_id],[org_id],[au_id], [a_id],[s_id],[e_jsren],[p_id],[dwei_idx],[ztai],[zhsliang],[lhsliang],[sliang],[sliang2],[cbjia],[cbje],[jge], [je],[zkou],[zhjia],[zhje],[slv],[hsjia],[hsje],[lsjia],[lsje],[pcxjia1],[pcxjia2],[phao],[mjphao],[yxqi],[scrqi], [rksjian],[thsliang],[thsjian],[jfen],[cbhsfa],[csztai],[l_id],[cxdan_lxing],[cxdmxi_id],[zlzkuang],[pcbzhu1], [pcbzhu2],[gyshang_id],[hbzhu],[e_fhren1],[e_fhren2],[fhztai],[shsliang],[hgsliang],[bhgsliang],[cysliang],[yssliang], [jssliang],[fhsliang],[ysddian],[bhgclcshi],[bhgyyin],[jsyyin],[shren],[yssjian],[shsjian],[zdyi1],[bzhu],[zdyi2], [zdyi3],[zdyi4],[e_cjren],[cjsjian],[e_xgren],[qysjian],[dhsjian],[xgsjian],[ysjlun],[qywsdu],[dhwsdu],[cytjian], [ysgjjcqkuang],[ysgju],[e_ysyuan1],[e_ysyuan2],[ddsliang],[fyddian],[wkfshi],[gsplcheng],[tlzy_transztai],[tlzy_transguid]) SELECT SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255)+SN,SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255), '','','YPYSJL','1',sn,'','','','ORG_ID','','','','admin',p.id,'0','0',0.0,0.0,sliang,sliang,jge,je,jge, je,100.00,jge,je,0.0,jge,je,jge,je,0.0,0.0,phao,'',yxqi,scrqi, rqi,0.0,'1900-01-01 00:00:00.000',0.0,'0','0','','','','','', '',c.id,'','','','0',sliang,sliang,0.0,0.0,sliang, 0.0,0.0,'','','','','','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','','','', '','','admin',rqi,'admin','','','1900-01-01 00:00:00.000',ysjlun,'','','', '合格','',ysyuan,ysyuan2,sliang,'','','0','0',SUBSTRING(tlzy_guid,0,CHARINDEX('|',tlzy_guid)) FROM tlzygsp_gjypysjlu_tmp y LEFT JOIN base_product p ON p.bzhu = y.p_id LEFT JOIN base_client c ON c.bzhu = y.c_id UPDATE gsp_idx SET c_id = t.id FROM gsp_idx i ,base_client t WHERE i.dju_lxing = 'YPYSJL' AND i.id = t.bzhu UPDATE gsp_idx SET s_id = t.id FROM gsp_idx i ,base_storage t WHERE i.dju_lxing = 'YPYSJL' AND i.id = t.bzhu END ELSE IF @lxing = '2743' ---中藥飲片驗收記錄 tlzygsp_zyczyypysjlu_tmp begin DECLARE @sql8 NVARCHAR(4000) SET @sql8 = ' INSERT INTO gsp_idx(id,dju_lxing,bhao,org_id,rqi,gzrqi,e_jsren,e_shren,c_id,d_id,v_id,s_id,s_id2,slv,sliang,hjje, cbje,hjyhje,lsje,ysje,yjje,ssje,gsplcheng,ztai,jfen,bzhu,e_cjren,cjsjian,e_xgren,xgsjian, cgywyuan,gmrxxi,e_zyyshi,zjlxing,zjhma,djlcheng,cflyuan,cfkjsjian,e_tpren,e_hdren,ysxming) SELECT c.id,''ZYYPYSJL'',c.bhao,''ORG_ID'',c.rqi,c.rqi,e1.mcheng,e2.mcheng,a.c_id,'''','''',a.s_id,'''',0.0,0.0,0.0, 0.0,0.0,0.0,0.0,0.0,0.0,''1'',''0'',0.0,c.bzhu,e1.mcheng,c.rqi,e1.mcheng,c.rqi, '''','''','''','''','''',''2'','''',c.rqi,'''','''','''' FROM tlzy_cmtGSPReport c LEFT JOIN '+@sjk+'..idxgsp a ON a.id = c.orgbillid LEFT JOIN base_employee e1 ON e1.bzhu = c.e_id LEFT JOIN base_employee e2 ON e2.bzhu = c.shren_id WHERE c.gsptype = ''2743''' EXEC (@sql8) INSERT INTO gsp_detail -----s_id 問題 ( [id],[dju_id],[ydju_lxing],[ydjmxi_id],[dju_lxing],[crkbzhi],[hhao],[kclshao],[oc_id],[dis_id],[org_id],[au_id], [a_id],[s_id],[e_jsren],[p_id],[dwei_idx],[ztai],[zhsliang],[lhsliang],[sliang],[sliang2],[cbjia],[cbje],[jge], [je],[zkou],[zhjia],[zhje],[slv],[hsjia],[hsje],[lsjia],[lsje],[pcxjia1],[pcxjia2],[phao],[mjphao],[yxqi],[scrqi], [rksjian],[thsliang],[thsjian],[jfen],[cbhsfa],[csztai],[l_id],[cxdan_lxing],[cxdmxi_id],[zlzkuang],[pcbzhu1], [pcbzhu2],[gyshang_id],[hbzhu],[e_fhren1],[e_fhren2],[fhztai],[shsliang],[hgsliang],[bhgsliang],[cysliang],[yssliang], [jssliang],[fhsliang],[ysddian],[bhgclcshi],[bhgyyin],[jsyyin],[shren],[yssjian],[shsjian],[zdyi1],[bzhu],[zdyi2], [zdyi3],[zdyi4],[e_cjren],[cjsjian],[e_xgren],[qysjian],[dhsjian],[xgsjian],[ysjlun],[qywsdu],[dhwsdu],[cytjian], [ysgjjcqkuang],[ysgju],[e_ysyuan1],[e_ysyuan2],[ddsliang],[fyddian],[wkfshi],[gsplcheng],[tlzy_transztai],[tlzy_transguid]) SELECT SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255)+SN,SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255), '','','ZYYPYSJL','1',sn,'','','','ORG_ID','','','','admin',p.id,'0','0',0.0,0.0,sliang,sliang,jge,je,jge, je,100.00,jge,je,0.0,jge,je,jge,je,0.0,0.0,phao,'',yxqi,scrqi, rqi,0.0,'1900-01-01 00:00:00.000',0.0,'0','0','','','','','', '',c.id,'','','','0',sliang,hgsliang,0.0,0.0,sliang, 0.0,0.0,'','','','','','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','','','', '','','admin',rqi,'admin','','','1900-01-01 00:00:00.000',ysjlun,'','','', '合格','',ysyuan,ysyuan2,sliang,'','','0','0',SUBSTRING(tlzy_guid,0,CHARINDEX('|',tlzy_guid)) FROM tlzygsp_zyczyypysjlu_tmp y LEFT JOIN base_product p ON p.bzhu = y.p_id LEFT JOIN base_client c ON c.bzhu = y.c_id UPDATE gsp_idx SET c_id = t.id FROM gsp_idx i ,base_client t WHERE i.dju_lxing = 'ZYYPYSJL' AND i.id = t.bzhu UPDATE gsp_idx SET s_id = t.id FROM gsp_idx i ,base_storage t WHERE i.dju_lxing = 'ZYYPYSJL' AND i.id = t.bzhu END ELSE IF @lxing = '2863' ---醫療器械驗收記錄 tlzygsp_ylqxypysjl_tmp begin DECLARE @sql9 NVARCHAR(4000) SET @sql9 = ' INSERT INTO gsp_idx(id,dju_lxing,bhao,org_id,rqi,gzrqi,e_jsren,e_shren,c_id,d_id,v_id,s_id,s_id2,slv,sliang,hjje, cbje,hjyhje,lsje,ysje,yjje,ssje,gsplcheng,ztai,jfen,bzhu,e_cjren,cjsjian,e_xgren,xgsjian, cgywyuan,gmrxxi,e_zyyshi,zjlxing,zjhma,djlcheng,cflyuan,cfkjsjian,e_tpren,e_hdren,ysxming) SELECT c.id,''YLQXYSJL'',c.bhao,''ORG_ID'',c.rqi,c.rqi,e1.mcheng,e2.mcheng,a.c_id,'''','''',a.s_id,'''',0.0,0.0,0.0, 0.0,0.0,0.0,0.0,0.0,0.0,''1'',''0'',0.0,c.bzhu,e1.mcheng,c.rqi,e1.mcheng,c.rqi, '''','''','''','''','''',''2'','''',c.rqi,'''','''','''' FROM tlzy_cmtGSPReport c LEFT JOIN '+@sjk+'..idxgsp a ON a.id = c.orgbillid LEFT JOIN base_employee e1 ON e1.bzhu = c.e_id LEFT JOIN base_employee e2 ON e2.bzhu = c.shren_id WHERE c.gsptype = ''2863''' EXEC (@sql9) INSERT INTO gsp_detail -----s_id 問題 ( [id],[dju_id],[ydju_lxing],[ydjmxi_id],[dju_lxing],[crkbzhi],[hhao],[kclshao],[oc_id],[dis_id],[org_id],[au_id], [a_id],[s_id],[e_jsren],[p_id],[dwei_idx],[ztai],[zhsliang],[lhsliang],[sliang],[sliang2],[cbjia],[cbje],[jge], [je],[zkou],[zhjia],[zhje],[slv],[hsjia],[hsje],[lsjia],[lsje],[pcxjia1],[pcxjia2],[phao],[mjphao],[yxqi],[scrqi], [rksjian],[thsliang],[thsjian],[jfen],[cbhsfa],[csztai],[l_id],[cxdan_lxing],[cxdmxi_id],[zlzkuang],[pcbzhu1], [pcbzhu2],[gyshang_id],[hbzhu],[e_fhren1],[e_fhren2],[fhztai],[shsliang],[hgsliang],[bhgsliang],[cysliang],[yssliang], [jssliang],[fhsliang],[ysddian],[bhgclcshi],[bhgyyin],[jsyyin],[shren],[yssjian],[shsjian],[zdyi1],[bzhu],[zdyi2], [zdyi3],[zdyi4],[e_cjren],[cjsjian],[e_xgren],[qysjian],[dhsjian],[xgsjian],[ysjlun],[qywsdu],[dhwsdu],[cytjian], [ysgjjcqkuang],[ysgju],[e_ysyuan1],[e_ysyuan2],[ddsliang],[fyddian],[wkfshi],[gsplcheng],[tlzy_transztai],[tlzy_transguid]) SELECT SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255)+SN,SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255), '','','YLQXYSJL','1',sn,'','','','ORG_ID','','','','admin',p.id,'0','0',0.0,0.0,sliang,sliang,jge,je,jge, je,100.00,jge,je,0.0,jge,je,jge,je,0.0,0.0,phao,'',yxqi,scrqi, rqi,0.0,'1900-01-01 00:00:00.000',0.0,'0','0','','','','','', '',c.id,'','','','0',sliang,hgsliang,0.0,0.0,sliang, 0.0,0.0,'','','','','','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','','','', '','','admin',rqi,'admin','','','1900-01-01 00:00:00.000',ysjlun,'','','', '合格','',ysyuan,ysyuan2,sliang,'','','0','0',SUBSTRING(tlzy_guid,0,CHARINDEX('|',tlzy_guid)) FROM tlzygsp_ylqxypysjl_tmp y LEFT JOIN base_product p ON p.bzhu = y.p_id LEFT JOIN base_client c ON c.bzhu = y.c_id UPDATE gsp_idx SET c_id = t.id FROM gsp_idx i ,base_client t WHERE i.dju_lxing = 'YLQXYSJL' AND i.id = t.bzhu UPDATE gsp_idx SET s_id = t.id FROM gsp_idx i ,base_storage t WHERE i.dju_lxing = 'YLQXYSJL' AND i.id = t.bzhu END ELSE IF @lxing = '2728' ---驗收入庫通知單 tlzygsp_ysrktzdan_tmp begin DECLARE @sql10 NVARCHAR(4000) SET @sql10 = ' INSERT INTO gsp_idx(id,dju_lxing,bhao,org_id,rqi,gzrqi,e_jsren,e_shren,c_id,d_id,v_id,s_id,s_id2,slv,sliang,hjje, cbje,hjyhje,lsje,ysje,yjje,ssje,gsplcheng,ztai,jfen,bzhu,e_cjren,cjsjian,e_xgren,xgsjian, cgywyuan,gmrxxi,e_zyyshi,zjlxing,zjhma,djlcheng,cflyuan,cfkjsjian,e_tpren,e_hdren,ysxming) SELECT c.id,''YSRKTZD'',c.bhao,''ORG_ID'',c.rqi,c.rqi,e1.mcheng,e2.mcheng,a.c_id,'''','''',a.s_id,'''',0.0,0.0,0.0, 0.0,0.0,0.0,0.0,0.0,0.0,''1'',''0'',0.0,c.bzhu,e1.mcheng,c.rqi,e1.mcheng,c.rqi, '''','''','''','''','''',''2'','''',c.rqi,'''','''','''' FROM tlzy_cmtGSPReport c LEFT JOIN '+@sjk+'..idxgsp a ON a.id = c.orgbillid LEFT JOIN base_employee e1 ON e1.bzhu = c.e_id LEFT JOIN base_employee e2 ON e2.bzhu = c.shren_id WHERE c.gsptype = ''2728''' EXEC (@sql10) INSERT INTO gsp_detail -----s_id 問題 ( [id],[dju_id],[ydju_lxing],[ydjmxi_id],[dju_lxing],[crkbzhi],[hhao],[kclshao],[oc_id],[dis_id],[org_id],[au_id], [a_id],[s_id],[e_jsren],[p_id],[dwei_idx],[ztai],[zhsliang],[lhsliang],[sliang],[sliang2],[cbjia],[cbje],[jge], [je],[zkou],[zhjia],[zhje],[slv],[hsjia],[hsje],[lsjia],[lsje],[pcxjia1],[pcxjia2],[phao],[mjphao],[yxqi],[scrqi], [rksjian],[thsliang],[thsjian],[jfen],[cbhsfa],[csztai],[l_id],[cxdan_lxing],[cxdmxi_id],[zlzkuang],[pcbzhu1], [pcbzhu2],[gyshang_id],[hbzhu],[e_fhren1],[e_fhren2],[fhztai],[shsliang],[hgsliang],[bhgsliang],[cysliang],[yssliang], [jssliang],[fhsliang],[ysddian],[bhgclcshi],[bhgyyin],[jsyyin],[shren],[yssjian],[shsjian],[zdyi1],[bzhu],[zdyi2], [zdyi3],[zdyi4],[e_cjren],[cjsjian],[e_xgren],[qysjian],[dhsjian],[xgsjian],[ysjlun],[qywsdu],[dhwsdu],[cytjian], [ysgjjcqkuang],[ysgju],[e_ysyuan1],[e_ysyuan2],[ddsliang],[fyddian],[wkfshi],[gsplcheng],[tlzy_transztai],[tlzy_transguid]) SELECT SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255)+SN,SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255), '','','YSRKTZD','1',sn,'','','','ORG_ID','','','','admin',p.id,'0','0',0.0,0.0,sliang,sliang,0.0,0.0,0.0, 0.0,100.00,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,phao,'',yxqi,scrqi, rqi,0.0,'1900-01-01 00:00:00.000',0.0,'0','0','','','','','', '',c.id,'','','','0',sliang,0.0,0.0,0.0,sliang, 0.0,0.0,'','','','','','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','','','', '','','admin',rqi,'admin','','','1900-01-01 00:00:00.000','','','','', '合格','','','',sliang,'','','0','0',SUBSTRING(tlzy_guid,0,CHARINDEX('|',tlzy_guid)) FROM tlzygsp_ysrktzdan_tmp y LEFT JOIN base_product p ON p.bzhu = y.p_id LEFT JOIN base_client c ON c.bzhu = y.c_id UPDATE gsp_idx SET c_id = t.id FROM gsp_idx i ,base_client t WHERE i.dju_lxing = 'YSRKTZD' AND i.id = t.bzhu UPDATE gsp_idx SET s_id = t.id FROM gsp_idx i ,base_storage t WHERE i.dju_lxing = 'YSRKTZD' AND i.id = t.bzhu END ELSE IF @lxing = '2731' ---藥品入庫記錄 tlzygsp_ysrktzdan_tmp begin DECLARE @sql11 NVARCHAR(4000) SET @sql11 = ' INSERT INTO gsp_idx(id,dju_lxing,bhao,org_id,rqi,gzrqi,e_jsren,e_shren,c_id,d_id,v_id,s_id,s_id2,slv,sliang,hjje, cbje,hjyhje,lsje,ysje,yjje,ssje,gsplcheng,ztai,jfen,bzhu,e_cjren,cjsjian,e_xgren,xgsjian, cgywyuan,gmrxxi,e_zyyshi,zjlxing,zjhma,djlcheng,cflyuan,cfkjsjian,e_tpren,e_hdren,ysxming) SELECT c.id,''YPRKJLTZ'',c.bhao,''ORG_ID'',c.rqi,c.rqi,e1.mcheng,e2.mcheng,a.c_id,'''','''',a.s_id,'''',0.0,0.0,0.0, 0.0,0.0,0.0,0.0,0.0,0.0,''1'',''0'',0.0,c.bzhu,e1.mcheng,c.rqi,e1.mcheng,c.rqi, '''','''','''','''','''',''2'','''',c.rqi,'''','''','''' FROM tlzy_cmtGSPReport c LEFT JOIN '+@sjk+'..idxgsp a ON a.id = c.orgbillid LEFT JOIN base_employee e1 ON e1.bzhu = c.e_id LEFT JOIN base_employee e2 ON e2.bzhu = c.shren_id WHERE c.gsptype = ''2731''' EXEC (@sql11) INSERT INTO gsp_detail -----s_id 問題 ( [id],[dju_id],[ydju_lxing],[ydjmxi_id],[dju_lxing],[crkbzhi],[hhao],[kclshao],[oc_id],[dis_id],[org_id],[au_id], [a_id],[s_id],[e_jsren],[p_id],[dwei_idx],[ztai],[zhsliang],[lhsliang],[sliang],[sliang2],[cbjia],[cbje],[jge], [je],[zkou],[zhjia],[zhje],[slv],[hsjia],[hsje],[lsjia],[lsje],[pcxjia1],[pcxjia2],[phao],[mjphao],[yxqi],[scrqi], [rksjian],[thsliang],[thsjian],[jfen],[cbhsfa],[csztai],[l_id],[cxdan_lxing],[cxdmxi_id],[zlzkuang],[pcbzhu1], [pcbzhu2],[gyshang_id],[hbzhu],[e_fhren1],[e_fhren2],[fhztai],[shsliang],[hgsliang],[bhgsliang],[cysliang],[yssliang], [jssliang],[fhsliang],[ysddian],[bhgclcshi],[bhgyyin],[jsyyin],[shren],[yssjian],[shsjian],[zdyi1],[bzhu],[zdyi2], [zdyi3],[zdyi4],[e_cjren],[cjsjian],[e_xgren],[qysjian],[dhsjian],[xgsjian],[ysjlun],[qywsdu],[dhwsdu],[cytjian], [ysgjjcqkuang],[ysgju],[e_ysyuan1],[e_ysyuan2],[ddsliang],[fyddian],[wkfshi],[gsplcheng],[tlzy_transztai],[tlzy_transguid]) SELECT SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255)+SN,SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255), '','','YPRKJLTZ','1',sn,'','','','ORG_ID','','','',e1.id,p.id,'0','0',0.0,0.0,sliang,sliang,0.0,0.0,0.0, 0.0,100.00,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,phao,'',yxqi,scrqi, rqi,0.0,'1900-01-01 00:00:00.000',0.0,'0','0','','','','','', '',c.id,'','','','0',sliang,sliang,0.0,0.0,sliang, 0.0,sliang,'','','','','','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','','','', '','',e1.id,rqi,e1.id,'','','1900-01-01 00:00:00.000',ysjlun,'','','', '合格','','','',sliang,'','','0','0',SUBSTRING(tlzy_guid,0,CHARINDEX('|',tlzy_guid)) FROM tlzygsp_yprkjlu_tmp y LEFT JOIN base_employee e1 ON e1.bzhu = y.e_id LEFT JOIN base_product p ON p.bzhu = y.p_id LEFT JOIN base_client c ON c.bzhu = y.c_id UPDATE gsp_idx SET c_id = t.id FROM gsp_idx i ,base_client t WHERE i.dju_lxing = 'YPRKJLTZ' AND i.id = t.bzhu UPDATE gsp_idx SET s_id = t.id FROM gsp_idx i ,base_storage t WHERE i.dju_lxing = 'YPRKJLTZ' AND i.id = t.bzhu END ELSE IF @lxing = '2867' ---醫療器械入庫記錄 tlzygsp_ylqxrkjlu_tmp begin DECLARE @sql12 NVARCHAR(4000) SET @sql12 = ' INSERT INTO gsp_idx(id,dju_lxing,bhao,org_id,rqi,gzrqi,e_jsren,e_shren,c_id,d_id,v_id,s_id,s_id2,slv,sliang,hjje, cbje,hjyhje,lsje,ysje,yjje,ssje,gsplcheng,ztai,jfen,bzhu,e_cjren,cjsjian,e_xgren,xgsjian, cgywyuan,gmrxxi,e_zyyshi,zjlxing,zjhma,djlcheng,cflyuan,cfkjsjian,e_tpren,e_hdren,ysxming) SELECT c.id,''YLQXRKJL'',c.bhao,''ORG_ID'',c.rqi,c.rqi,e1.mcheng,e2.mcheng,a.c_id,'''','''',a.s_id,'''',0.0,0.0,0.0, 0.0,0.0,0.0,0.0,0.0,0.0,''1'',''0'',0.0,c.bzhu,e1.mcheng,c.rqi,e1.mcheng,c.rqi, '''','''','''','''','''',''2'','''',c.rqi,'''','''','''' FROM tlzy_cmtGSPReport c LEFT JOIN '+@sjk+'..idxgsp a ON a.id = c.orgbillid LEFT JOIN base_employee e1 ON e1.bzhu = c.e_id LEFT JOIN base_employee e2 ON e2.bzhu = c.shren_id WHERE c.gsptype = ''2867''' EXEC (@sql12) INSERT INTO gsp_detail -----s_id 問題 ( [id],[dju_id],[ydju_lxing],[ydjmxi_id],[dju_lxing],[crkbzhi],[hhao],[kclshao],[oc_id],[dis_id],[org_id],[au_id], [a_id],[s_id],[e_jsren],[p_id],[dwei_idx],[ztai],[zhsliang],[lhsliang],[sliang],[sliang2],[cbjia],[cbje],[jge], [je],[zkou],[zhjia],[zhje],[slv],[hsjia],[hsje],[lsjia],[lsje],[pcxjia1],[pcxjia2],[phao],[mjphao],[yxqi],[scrqi], [rksjian],[thsliang],[thsjian],[jfen],[cbhsfa],[csztai],[l_id],[cxdan_lxing],[cxdmxi_id],[zlzkuang],[pcbzhu1], [pcbzhu2],[gyshang_id],[hbzhu],[e_fhren1],[e_fhren2],[fhztai],[shsliang],[hgsliang],[bhgsliang],[cysliang],[yssliang], [jssliang],[fhsliang],[ysddian],[bhgclcshi],[bhgyyin],[jsyyin],[shren],[yssjian],[shsjian],[zdyi1],[bzhu],[zdyi2], [zdyi3],[zdyi4],[e_cjren],[cjsjian],[e_xgren],[qysjian],[dhsjian],[xgsjian],[ysjlun],[qywsdu],[dhwsdu],[cytjian], [ysgjjcqkuang],[ysgju],[e_ysyuan1],[e_ysyuan2],[ddsliang],[fyddian],[wkfshi],[gsplcheng],[tlzy_transztai],[tlzy_transguid]) SELECT SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255)+SN,SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255), '','','YLQXRKJL','1',sn,'','','','ORG_ID','','','',e1.id,p.id,'0','0',0.0,0.0,sliang,sliang,0.0,0.0,0.0, 0.0,100.00,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,phao,'',yxqi,scrqi, rqi,0.0,'1900-01-01 00:00:00.000',0.0,'0','0','','','','','', '',c.id,'','','','0',sliang,sliang,0.0,0.0,sliang, 0.0,sliang,'','','','','','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','','','', '','',e1.id,rqi,e1.id,'','','1900-01-01 00:00:00.000',ysjlun,'','','', '合格','','','',sliang,'','','0','0',SUBSTRING(tlzy_guid,0,CHARINDEX('|',tlzy_guid)) FROM tlzygsp_ylqxrkjlu_tmp y LEFT JOIN base_employee e1 ON e1.bzhu = y.e_id LEFT JOIN base_product p ON p.bzhu = y.p_id LEFT JOIN base_client c ON c.bzhu = y.c_id UPDATE gsp_idx SET c_id = t.id FROM gsp_idx i ,base_client t WHERE i.dju_lxing = 'YLQXRKJL' AND i.id = t.bzhu UPDATE gsp_idx SET s_id = t.id FROM gsp_idx i ,base_storage t WHERE i.dju_lxing = 'YLQXRKJL' AND i.id = t.bzhu END ELSE IF @lxing = '2868' ---中藥飲片入庫記錄 tlzygsp_zyczyyprkjlu_tmp BEGIN DECLARE @sql13 NVARCHAR(4000) SET @sql13 = ' INSERT INTO gsp_idx(id,dju_lxing,bhao,org_id,rqi,gzrqi,e_jsren,e_shren,c_id,d_id,v_id,s_id,s_id2,slv,sliang,hjje, cbje,hjyhje,lsje,ysje,yjje,ssje,gsplcheng,ztai,jfen,bzhu,e_cjren,cjsjian,e_xgren,xgsjian, cgywyuan,gmrxxi,e_zyyshi,zjlxing,zjhma,djlcheng,cflyuan,cfkjsjian,e_tpren,e_hdren,ysxming) SELECT c.id,''ZYYPRKJL'',c.bhao,''ORG_ID'',c.rqi,c.rqi,e1.mcheng,e2.mcheng,a.c_id,'''','''',a.s_id,'''',0.0,0.0,0.0, 0.0,0.0,0.0,0.0,0.0,0.0,''1'',''0'',0.0,c.bzhu,e1.mcheng,c.rqi,e1.mcheng,c.rqi, '''','''','''','''','''',''2'','''',c.rqi,'''','''','''' FROM tlzy_cmtGSPReport c LEFT JOIN '+@sjk+'..idxgsp a ON a.id = c.orgbillid LEFT JOIN base_employee e1 ON e1.bzhu = c.e_id LEFT JOIN base_employee e2 ON e2.bzhu = c.shren_id WHERE c.gsptype = ''2868''' EXEC (@sql13) INSERT INTO gsp_detail -----s_id 問題 ( [id],[dju_id],[ydju_lxing],[ydjmxi_id],[dju_lxing],[crkbzhi],[hhao],[kclshao],[oc_id],[dis_id],[org_id],[au_id], [a_id],[s_id],[e_jsren],[p_id],[dwei_idx],[ztai],[zhsliang],[lhsliang],[sliang],[sliang2],[cbjia],[cbje],[jge], [je],[zkou],[zhjia],[zhje],[slv],[hsjia],[hsje],[lsjia],[lsje],[pcxjia1],[pcxjia2],[phao],[mjphao],[yxqi],[scrqi], [rksjian],[thsliang],[thsjian],[jfen],[cbhsfa],[csztai],[l_id],[cxdan_lxing],[cxdmxi_id],[zlzkuang],[pcbzhu1], [pcbzhu2],[gyshang_id],[hbzhu],[e_fhren1],[e_fhren2],[fhztai],[shsliang],[hgsliang],[bhgsliang],[cysliang],[yssliang], [jssliang],[fhsliang],[ysddian],[bhgclcshi],[bhgyyin],[jsyyin],[shren],[yssjian],[shsjian],[zdyi1],[bzhu],[zdyi2], [zdyi3],[zdyi4],[e_cjren],[cjsjian],[e_xgren],[qysjian],[dhsjian],[xgsjian],[ysjlun],[qywsdu],[dhwsdu],[cytjian], [ysgjjcqkuang],[ysgju],[e_ysyuan1],[e_ysyuan2],[ddsliang],[fyddian],[wkfshi],[gsplcheng],[tlzy_transztai],[tlzy_transguid]) SELECT SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255)+SN,SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255), '','','ZYYPRKJL','1',sn,'','','','ORG_ID','','','',e1.id,p.id,'0','0',0.0,0.0,sliang,sliang,0.0,0.0,0.0, 0.0,100.00,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,phao,'',yxqi,scrqi, rqi,0.0,'1900-01-01 00:00:00.000',0.0,'0','0','','','','','', '',c.id,'','','','0',sliang,sliang,0.0,0.0,sliang, 0.0,sliang,'','','','','','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','','','', '','',e1.id,rqi,e1.id,'','','1900-01-01 00:00:00.000',ysjlun,'','','', '合格','','','',sliang,'','','0','0',SUBSTRING(tlzy_guid,0,CHARINDEX('|',tlzy_guid)) FROM tlzygsp_zyczyyprkjlu_tmp y LEFT JOIN base_employee e1 ON e1.bzhu = y.e_id LEFT JOIN base_product p ON p.bzhu = y.p_id LEFT JOIN base_client c ON c.bzhu = y.c_id UPDATE gsp_idx SET c_id = t.id FROM gsp_idx i ,base_client t WHERE i.dju_lxing = 'ZYYPRKJL' AND i.id = t.bzhu UPDATE gsp_idx SET s_id = t.id FROM gsp_idx i ,base_storage t WHERE i.dju_lxing = 'ZYYPRKJL' AND i.id = t.bzhu END ELSE IF @lxing = '2749' ---藥品銷售記錄 tlzygsp_ypxsjlu_tmp BEGIN DECLARE @sql14 NVARCHAR(4000) SET @sql14 = ' INSERT INTO gsp_idx(id,dju_lxing,bhao,org_id,rqi,gzrqi,e_jsren,e_shren,c_id,d_id,v_id,s_id,s_id2,slv,sliang,hjje, cbje,hjyhje,lsje,ysje,yjje,ssje,gsplcheng,ztai,jfen,bzhu,e_cjren,cjsjian,e_xgren,xgsjian, cgywyuan,gmrxxi,e_zyyshi,zjlxing,zjhma,djlcheng,cflyuan,cfkjsjian,e_tpren,e_hdren,ysxming) SELECT c.id,''YPXSJL'',c.bhao,''ORG_ID'',c.rqi,c.rqi,e1.mcheng,e2.mcheng,a.c_id,'''','''',a.s_id,'''',0.0,0.0,0.0, 0.0,0.0,0.0,0.0,0.0,0.0,''1'',''0'',0.0,c.bzhu,e1.mcheng,c.rqi,e1.mcheng,c.rqi, '''','''','''','''','''',''2'','''',c.rqi,'''','''','''' FROM tlzy_cmtGSPReport c LEFT JOIN '+@sjk+'..idxgsp a ON a.id = c.orgbillid LEFT JOIN base_employee e1 ON e1.bzhu = c.e_id LEFT JOIN base_employee e2 ON e2.bzhu = c.shren_id WHERE c.gsptype = ''2749''' EXEC (@sql14) INSERT INTO gsp_detail -----s_id 問題 ( [id],[dju_id],[ydju_lxing],[ydjmxi_id],[dju_lxing],[crkbzhi],[hhao],[kclshao],[oc_id],[dis_id],[org_id],[au_id], [a_id],[s_id],[e_jsren],[p_id],[dwei_idx],[ztai],[zhsliang],[lhsliang],[sliang],[sliang2],[cbjia],[cbje],[jge], [je],[zkou],[zhjia],[zhje],[slv],[hsjia],[hsje],[lsjia],[lsje],[pcxjia1],[pcxjia2],[phao],[mjphao],[yxqi],[scrqi], [rksjian],[thsliang],[thsjian],[jfen],[cbhsfa],[csztai],[l_id],[cxdan_lxing],[cxdmxi_id],[zlzkuang],[pcbzhu1], [pcbzhu2],[gyshang_id],[hbzhu],[e_fhren1],[e_fhren2],[fhztai],[shsliang],[hgsliang],[bhgsliang],[cysliang],[yssliang], [jssliang],[fhsliang],[ysddian],[bhgclcshi],[bhgyyin],[jsyyin],[shren],[yssjian],[shsjian],[zdyi1],[bzhu],[zdyi2], [zdyi3],[zdyi4],[e_cjren],[cjsjian],[e_xgren],[qysjian],[dhsjian],[xgsjian],[ysjlun],[qywsdu],[dhwsdu],[cytjian], [ysgjjcqkuang],[ysgju],[e_ysyuan1],[e_ysyuan2],[ddsliang],[fyddian],[wkfshi],[gsplcheng],[tlzy_transztai],[tlzy_transguid]) SELECT SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255)+SN,SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255), '','','YPXSJL','1',sn,'','','','ORG_ID','','','',e1.id,p.id,'0','0',0.0,0.0,sliang,sliang,jge,je,jge, je,100.00,jge,je,0.0,jge,je,0.0,0.0,0.0,0.0,phao,'',yxqi,scrqi, rqi,0.0,'1900-01-01 00:00:00.000',0.0,'0','0','','','','','', '',c.id,'','','','0',sliang,sliang,0.0,0.0,sliang, 0.0,sliang,'','','','','','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','','','', '','',e1.id,rqi,e1.id,'','','1900-01-01 00:00:00.000','','','','', '合格','','','',sliang,'','','0',' ~~~0',SUBSTRING(tlzy_guid,0,CHARINDEX('|',tlzy_guid)) FROM tlzygsp_ypxsjlu_tmp y LEFT JOIN base_employee e1 ON e1.bzhu = y.e_id LEFT JOIN base_product p ON p.bzhu = y.p_id LEFT JOIN base_client c ON c.bzhu = y.c_id UPDATE gsp_idx SET c_id = t.id FROM gsp_idx i ,base_client t WHERE i.dju_lxing = 'YPXSJL' AND i.id = t.bzhu UPDATE gsp_idx SET s_id = t.id FROM gsp_idx i ,base_storage t WHERE i.dju_lxing = 'YPXSJL' AND i.id = t.bzhu END ELSE IF @lxing = '2860' ---醫療器械銷售記錄 tlzygsp_ylqxxsjlu_tmp BEGIN DECLARE @sql15 NVARCHAR(4000) SET @sql15 = ' INSERT INTO gsp_idx(id,dju_lxing,bhao,org_id,rqi,gzrqi,e_jsren,e_shren,c_id,d_id,v_id,s_id,s_id2,slv,sliang,hjje, cbje,hjyhje,lsje,ysje,yjje,ssje,gsplcheng,ztai,jfen,bzhu,e_cjren,cjsjian,e_xgren,xgsjian, cgywyuan,gmrxxi,e_zyyshi,zjlxing,zjhma,djlcheng,cflyuan,cfkjsjian,e_tpren,e_hdren,ysxming) SELECT c.id,''YLQXXSJL'',c.bhao,''ORG_ID'',c.rqi,c.rqi,e1.mcheng,e2.mcheng,a.c_id,'''','''',a.s_id,'''',0.0,0.0,0.0, 0.0,0.0,0.0,0.0,0.0,0.0,''1'',''0'',0.0,c.bzhu,e1.mcheng,c.rqi,e1.mcheng,c.rqi, '''','''','''','''','''',''2'','''',c.rqi,'''','''','''' FROM tlzy_cmtGSPReport c LEFT JOIN '+@sjk+'..idxgsp a ON a.id = c.orgbillid LEFT JOIN base_employee e1 ON e1.bzhu = c.e_id LEFT JOIN base_employee e2 ON e2.bzhu = c.shren_id WHERE c.gsptype = ''2860''' EXEC (@sql15) INSERT INTO gsp_detail -----s_id 問題 ( [id],[dju_id],[ydju_lxing],[ydjmxi_id],[dju_lxing],[crkbzhi],[hhao],[kclshao],[oc_id],[dis_id],[org_id],[au_id], [a_id],[s_id],[e_jsren],[p_id],[dwei_idx],[ztai],[zhsliang],[lhsliang],[sliang],[sliang2],[cbjia],[cbje],[jge], [je],[zkou],[zhjia],[zhje],[slv],[hsjia],[hsje],[lsjia],[lsje],[pcxjia1],[pcxjia2],[phao],[mjphao],[yxqi],[scrqi], [rksjian],[thsliang],[thsjian],[jfen],[cbhsfa],[csztai],[l_id],[cxdan_lxing],[cxdmxi_id],[zlzkuang],[pcbzhu1], [pcbzhu2],[gyshang_id],[hbzhu],[e_fhren1],[e_fhren2],[fhztai],[shsliang],[hgsliang],[bhgsliang],[cysliang],[yssliang], [jssliang],[fhsliang],[ysddian],[bhgclcshi],[bhgyyin],[jsyyin],[shren],[yssjian],[shsjian],[zdyi1],[bzhu],[zdyi2], [zdyi3],[zdyi4],[e_cjren],[cjsjian],[e_xgren],[qysjian],[dhsjian],[xgsjian],[ysjlun],[qywsdu],[dhwsdu],[cytjian], [ysgjjcqkuang],[ysgju],[e_ysyuan1],[e_ysyuan2],[ddsliang],[fyddian],[wkfshi],[gsplcheng],[tlzy_transztai],[tlzy_transguid]) SELECT SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255)+SN,SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255), '','','YLQXXSJL','1',sn,'','','','ORG_ID','','','',e1.id,p.id,'0','0',0.0,0.0,sliang,sliang,jge,je,jge, je,100.00,jge,je,0.0,jge,je,0.0,0.0,0.0,0.0,phao,'',yxqi,scrqi, rqi,0.0,'1900-01-01 00:00:00.000',0.0,'0','0','','','','','', '',c.id,'','','','0',sliang,sliang,0.0,0.0,sliang, 0.0,sliang,'','','','','','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','','','', '','',e1.id,rqi,e1.id,'','','1900-01-01 00:00:00.000','','','','', '合格','','','',sliang,'','','0','0',SUBSTRING(tlzy_guid,0,CHARINDEX('|',tlzy_guid)) FROM tlzygsp_ylqxxsjlu_tmp y LEFT JOIN base_employee e1 ON e1.bzhu = y.e_id LEFT JOIN base_product p ON p.bzhu = y.p_id LEFT JOIN base_client c ON c.bzhu = y.c_id UPDATE gsp_idx SET c_id = t.id FROM gsp_idx i ,base_client t WHERE i.dju_lxing = 'YLQXXSJL' AND i.id = t.bzhu UPDATE gsp_idx SET s_id = t.id FROM gsp_idx i ,base_storage t WHERE i.dju_lxing = 'YLQXXSJL' AND i.id = t.bzhu END ELSE IF @lxing = '2873' ---中藥飲片銷售記錄 dbo.tlzygsp_zyczyypxsjlu_tmp BEGIN DECLARE @sql16 NVARCHAR(4000) SET @sql16 = ' INSERT INTO gsp_idx(id,dju_lxing,bhao,org_id,rqi,gzrqi,e_jsren,e_shren,c_id,d_id,v_id,s_id,s_id2,slv,sliang,hjje, cbje,hjyhje,lsje,ysje,yjje,ssje,gsplcheng,ztai,jfen,bzhu,e_cjren,cjsjian,e_xgren,xgsjian, cgywyuan,gmrxxi,e_zyyshi,zjlxing,zjhma,djlcheng,cflyuan,cfkjsjian,e_tpren,e_hdren,ysxming) SELECT c.id,''ZYYPXSJL'',c.bhao,''ORG_ID'',c.rqi,c.rqi,e1.mcheng,e2.mcheng,a.c_id,'''','''',a.s_id,'''',0.0,0.0,0.0, 0.0,0.0,0.0,0.0,0.0,0.0,''1'',''0'',0.0,c.bzhu,e1.mcheng,c.rqi,e1.mcheng,c.rqi, '''','''','''','''','''',''2'','''',c.rqi,'''','''','''' FROM tlzy_cmtGSPReport c LEFT JOIN '+@sjk+'..idxgsp a ON a.id = c.orgbillid LEFT JOIN base_employee e1 ON e1.bzhu = c.e_id LEFT JOIN base_employee e2 ON e2.bzhu = c.shren_id WHERE c.gsptype = ''2873''' EXEC (@sql16) INSERT INTO gsp_detail -----s_id 問題 ( [id],[dju_id],[ydju_lxing],[ydjmxi_id],[dju_lxing],[crkbzhi],[hhao],[kclshao],[oc_id],[dis_id],[org_id],[au_id], [a_id],[s_id],[e_jsren],[p_id],[dwei_idx],[ztai],[zhsliang],[lhsliang],[sliang],[sliang2],[cbjia],[cbje],[jge], [je],[zkou],[zhjia],[zhje],[slv],[hsjia],[hsje],[lsjia],[lsje],[pcxjia1],[pcxjia2],[phao],[mjphao],[yxqi],[scrqi], [rksjian],[thsliang],[thsjian],[jfen],[cbhsfa],[csztai],[l_id],[cxdan_lxing],[cxdmxi_id],[zlzkuang],[pcbzhu1], [pcbzhu2],[gyshang_id],[hbzhu],[e_fhren1],[e_fhren2],[fhztai],[shsliang],[hgsliang],[bhgsliang],[cysliang],[yssliang], [jssliang],[fhsliang],[ysddian],[bhgclcshi],[bhgyyin],[jsyyin],[shren],[yssjian],[shsjian],[zdyi1],[bzhu],[zdyi2], [zdyi3],[zdyi4],[e_cjren],[cjsjian],[e_xgren],[qysjian],[dhsjian],[xgsjian],[ysjlun],[qywsdu],[dhwsdu],[cytjian], [ysgjjcqkuang],[ysgju],[e_ysyuan1],[e_ysyuan2],[ddsliang],[fyddian],[wkfshi],[gsplcheng],[tlzy_transztai],[tlzy_transguid]) SELECT SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255)+SN,SUBSTRING(tlzy_guid,CHARINDEX('|',tlzy_guid)+1,255), '','','ZYYPXSJL','1',sn,'','','','ORG_ID','','','',e1.id,p.id,'0','0',0.0,0.0,sliang,sliang,jge,je,jge, je,100.00,jge,je,0.0,jge,je,0.0,0.0,0.0,0.0,phao,'',yxqi,scrqi, rqi,0.0,'1900-01-01 00:00:00.000',0.0,'0','0','','','','','', '',c.id,'','','','0',sliang,sliang,0.0,0.0,sliang, 0.0,sliang,'','','','','','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','','','', '','',e1.id,rqi,e1.id,'','','1900-01-01 00:00:00.000','','','','', '合格','','','',sliang,'','','0','0',SUBSTRING(tlzy_guid,0,CHARINDEX('|',tlzy_guid)) FROM tlzygsp_zyczyypxsjlu_tmp y LEFT JOIN base_employee e1 ON e1.bzhu = y.e_id LEFT JOIN base_product p ON p.bzhu = y.p_id LEFT JOIN base_client c ON c.bzhu = y.c_id UPDATE gsp_idx SET c_id = t.id FROM gsp_idx i ,base_client t WHERE i.dju_lxing = 'ZYYPXSJL' AND i.id = t.bzhu UPDATE gsp_idx SET s_id = t.id FROM gsp_idx i ,base_storage t WHERE i.dju_lxing = 'ZYYPXSJL' AND i.id = t.bzhu END end ~~~ # 二、EXCEL導入 ## 導入說明注：關于財貿通資料或其他軟件導入execl文檔有相關模板 1.導商品資料 ![](https://box.kancloud.cn/e4538e6f841180aea45ff4b75eb665ae_1315x74.png) ![](https://box.kancloud.cn/cf43af9415b132f4e4c116c8e93d5fcd_1305x92.png) ![](https://box.kancloud.cn/8ff24565a2d8a32bb11f2b0921549356_1310x82.png) [商品資料模板下載地址](https://pan.baidu.com/disk/home?#/all?vmode=list&path=%2F%E5%A4%A9%E5%8A%9BERP%E5%AE%9E%E6%96%BD%E5%8C%85%2F%E5%9F%BA%E6%9C%AC%E8%B5%84%E6%96%99%E5%AF%BC%E5%85%A5%E6%A8%A1%E6%9D%BF) 注：商品資料里的處方藥品導入erp是處方藥替換為1，非處方藥替換為0 2.導往來單位 ![](https://box.kancloud.cn/d27ba40e4768422aa22ba3d227a49143_1310x77.png) ![](https://box.kancloud.cn/ab5a055dcdbd701b85a7ed72cde310f0_1303x90.png) ![](https://box.kancloud.cn/406c2b6e254d508210f473ca7685a02a_887x62.png) [往來單位模板下載地址](https://pan.baidu.com/disk/home?#/all?vmode=list&path=%2F%E5%A4%A9%E5%8A%9BERP%E5%AE%9E%E6%96%BD%E5%8C%85%2F%E5%9F%BA%E6%9C%AC%E8%B5%84%E6%96%99%E5%AF%BC%E5%85%A5%E6%A8%A1%E6%9D%BF) 3.導職員資料 ![](https://box.kancloud.cn/2f5884fcb6e0d167271c4d3ef59cf5b5_1294x66.png) [職員資料模板下載地址](https://pan.baidu.com/disk/home?#/all?vmode=list&path=%2F%E5%A4%A9%E5%8A%9BERP%E5%AE%9E%E6%96%BD%E5%8C%85%2F%E5%9F%BA%E6%9C%AC%E8%B5%84%E6%96%99%E5%AF%BC%E5%85%A5%E6%A8%A1%E6%9D%BF) 4.導會員卡資料 ![](https://box.kancloud.cn/b9890d0750f9a4438f0d8e640224a639_1314x62.png) ![](https://box.kancloud.cn/07041c631c73815837375422eb08785f_841x67.png) [會員卡資料模板下載地址](https://pan.baidu.com/disk/home?#/all?vmode=list&path=%2F%E5%A4%A9%E5%8A%9BERP%E5%AE%9E%E6%96%BD%E5%8C%85%2F%E5%9F%BA%E6%9C%AC%E8%B5%84%E6%96%99%E5%AF%BC%E5%85%A5%E6%A8%A1%E6%9D%BF) 注：財貿通當前積分導入erp為期初積分財貿通當前儲值余額導入erp為期初儲值金額財貿通當前消費次數導入erp為期初消費次數財貿通當前消費金額導入erp為期初消費金額 PS：財貿通導出的數據需要提前在execl分列的需提前分好，防止導入后發現格式不正確再重新導就比較浪費時間（例：會員卡資料中的證件號碼/手機號碼/單位條碼等）