1 begin
2
3 --通过销货单与明细，生成安装项目及明细，及判断明细是否拆分生成多条
4 --delete from sazxm
5 --delete from ssbazrw
6 --获取未生成项目的销货单号
7 select rowid = identity(int,1,1),a.id xmid
8 into #sazxmlist
9 from sales a where a.id not in (select id from sazxm) and
10 convert(char(20),a.ddjrq,112) >= convert(char(20),'2018-12-01',112)
11 --根据销货单 单据日期 生成 当天安装项目,公司安装的直接到待安装状态
12 insert into sazxm(id,scode,idxhdh,sazxmdb,emazlx,idxsht,idxsdd,idhtqykh,emsfwsby,emsfwsaz,iddep,
13 idowner,emshzt,dfhrq,dbjfzje,emlife,idzdkh,swlgs,dsfzdsc,created,createdby,djrq,bqbqr,idazgcs,
14 idfpr,dfpsj,sshdz,idxsjh,contacts,sdh,idtsfpr)
15 select xhd.id,replace(convert(char(20),xhd.ddjrq,112)+right(xhd.id,3),' ',''),xhd.id,'2F01',
16 'f8d6171e3fcdaed39f0919a9ec5f51ae',xhd.idxmh,xhd.idso,xhd.idkh,ht.emcarry,ht.install,xhd.iddep,xhd.idowner,
17 '2cdd6816b789e9dc5312a21e37232b46',xhd.dxhrq,xhd.dbmoney,ht.emlife,xhd.idkh,xhd.sfyfs,1,getdate(),
18 '12979218489686204728368524150460',convert(date,xhd.ddjrq),0,ht.idfwgcs,'12979218489686204728368524150460',
19 getdate(),xhd.sshdz,xhd.idxsjh,xhd.contacts,xhd.slxdh,'9eda31d9ea905fa4df78f3d8b9b642c0'
20 from sales xhd left join [contract] ht on ht.id = xhd.idxmh left join org_employee emp
21 on emp.id = ht.idfwgcs where xhd.id not in (select id from sazxm) and
22 convert(char(20),xhd.ddjrq,112) >= convert(char(20),'2018-12-01',112)
23 and xhd.sname in ('2301','2303','2388','2399') and ht.install = '9eda31d9ea905fa4df78f3d8b9b642c0'
24 --根据销货单 单据日期 生成 当天安装项目,客户安装的直接到待客服审核状态
25 insert into sazxm(id,scode,idxhdh,sazxmdb,emazlx,idxsht,idxsdd,idhtqykh,emsfwsby,emsfwsaz,iddep,
26 idowner,emshzt,dfhrq,dbjfzje,emlife,idzdkh,swlgs,dsfzdsc,created,createdby,djrq,bqbqr,
27 sshdz,idxsjh,contacts,sdh)
28 select xhd.id,replace(convert(char(20),xhd.ddjrq,112)+right(xhd.id,3),' ',''),xhd.id,'2F01',
29 'f8d6171e3fcdaed39f0919a9ec5f51ae',xhd.idxmh,xhd.idso,xhd.idkh,ht.emcarry,ht.install,xhd.iddep,xhd.idowner,
30 'c03cc4622209d4adfa082a96e73f8221',xhd.dxhrq,xhd.dbmoney,ht.emlife,xhd.idkh,xhd.sfyfs,1,getdate(),
31 '12979218489686204728368524150460',convert(date,xhd.ddjrq),0,xhd.sshdz,xhd.idxsjh,xhd.contacts,xhd.slxdh
32 from sales xhd left join [contract] ht on ht.id = xhd.idxmh left join org_employee emp
33 on emp.id = ht.idfwgcs where xhd.id not in (select id from sazxm) and
34 convert(char(20),xhd.ddjrq,112) >= convert(char(20),'2018-12-01',112)
35 and xhd.sname in ('2301','2303','2388','2399') and ht.install = 'f908b77150a27c74415912c1c3a13656'
36 --无合同的 销货单 单据日期 生成 当天安装项目
37 insert into sazxm(id,scode,idxhdh,sazxmdb,emazlx,idxsdd,idhtqykh,iddep,
38 idowner,emshzt,dfhrq,dbjfzje,idzdkh,swlgs,dsfzdsc,created,createdby,djrq,bqbqr,
39 sshdz,idxsjh,contacts,sdh)
40 select xhd.id,replace(convert(char(20),xhd.ddjrq,112)+right(xhd.id,3),' ',''),xhd.id,'2F01',
41 'f8d6171e3fcdaed39f0919a9ec5f51ae',xhd.idso,xhd.idkh,xhd.iddep,xhd.idowner,
42 '20658d2abc23904dd1d9c0db20f04d71',xhd.dxhrq,xhd.dbmoney,xhd.idkh,xhd.sfyfs,1,getdate(),
43 '12979218489686204728368524150460',convert(date,xhd.ddjrq),0,xhd.sshdz,xhd.idxsjh,xhd.contacts,xhd.slxdh
44 from sales xhd where xhd.id not in (select id from sazxm) and
45 convert(char(20),xhd.ddjrq,112) >= convert(char(20),'2018-12-01',112)
46 and xhd.sname in ('2301','2303','2388','2399') and xhd.idxmh is null
47
48 --创建临时表，表中有3列：行号，id，数量，是否展开数量
49 select rowid = identity(int,1,1),mx.id,mx.dbnumber,mx.sfscazrw
50 into #amxtemplist
51 from #sazxmlist a join salemx mx on a.xmid = mx.idxhdh
52
53 --DROP TABLE #amxtemplist
54 --select * from #amxtemplist
55 --声明变量：@numrows 当前行号，@maxnumrows 最大行号，@id 当前id，@maxdbsl 最大数量，@dbsl 初始数量，@sfcf 是否展开数量
56 declare @numrows int,@maxnumrows int,@id char(100),@maxdbsl int ,@dbsl int,@sfcf int
57
58 select @numrows = MIN(RowID),@maxnumrows=MAX(RowID)
59 FROM #amxtemplist
60 print @numrows print @maxnumrows
61
62 while @numrows <= @maxnumrows
63 begin
64 select @id = id,@maxdbsl = dbnumber
65 from #amxtemplist where rowid=@numrows
66
67 select @id = (select id from salemx where id = @id)
68 set @maxdbsl = (select dbnumber from sales xhd join salemx xhmx on xhd.id = xhmx.idxhdh where xhmx.id = @id)
69 set @sfcf = (select sfscazrw from sales xhd join salemx xhmx on xhd.id = xhmx.idxhdh where xhmx.id = @id)
70 set @dbsl = 1
71 if (@dbsl = @maxdbsl)
72 begin
73 --生成单个设备安装任务
74 insert into ssbazrw (id,idazxm,sazxmdb,syspos,idcpxx,idxhmxbh,swlmc,idpp,szjys,
75 sptys,sdw,iddep,idowner,idhtqykh,idzdkh,dbsl,sph,scw,sfqdb,idfqdh,idxhddh,sxhddb,
76 dbprice,dbmoney,swlgg,emazlx,created,createdby,szk,rwscode,emzt,idazgcs,idfpr,
77 dfpsj,idazbm,idht,emsfyxlh,ddjrq,bzdscsj)
78 select xhmx.id,xhmx.idxhdh,left(xhmx.idxhdh,4),xhmx.sxh,xhmx.idcp,
79 xhmx.id,xhmx.sname,xhmx.idpp,xhmx.szjys,xhmx.sptys,xhmx.sdw,xhmx.iddep,
80 xhmx.idowner,xhmx.idkh,xhmx.idkh,xhmx.dbnumber,xhmx.sph,xhmx.scw,xhmx.sfqdb,
81 xhmx.idfqdh,xhmx.idxhdh,left(xhmx.idxhdh,4),xhmx.dbprice,xhmx.dbmoney,xhmx.sgg,
82 'f8d6171e3fcdaed39f0919a9ec5f51ae',getdate(),'12979218489686204728368524150460',
83 xhmx.discount,xhd.scode+'-'+xhmx.sxh,'7c78c2e5fb906560a1ff52ea68a50b6d',xm.idazgcs,
84 xm.idfpr,xm.dfpsj,emp.iddep,xm.idxsht,'f3406c8577eb9b62f9e5cf8a1895e8c2',xhd.ddjrq,1
85 from sales xhd join salemx xhmx on xhd.id = xhmx.idxhdh join sazxm xm on xm.id = xhd.id
86 left join org_employee emp on emp.id = xm.idazgcs
87 where xhmx.id not in (select id from ssbazrw)
88 and xhd.id in (select id from sazxm) and xhmx.dbnumber = 1 and xhmx.sfscazrw = 1
89 end
90 else
91 begin
92 --数量大于1时,创建多条设备安装任务
93 while (@dbsl <= @maxdbsl)
94 begin
95 if(@sfcf = 0)
96 begin
97 --生成无需展开的安装任务
98 insert into ssbazrw (id,idazxm,sazxmdb,syspos,idcpxx,idxhmxbh,swlmc,idpp,szjys,
99 sptys,sdw,iddep,idowner,idhtqykh,idzdkh,dbsl,sph,scw,sfqdb,idfqdh,idxhddh,sxhddb,
100 dbprice,dbmoney,swlgg,emazlx,created,createdby,szk,rwscode,emzt,idazgcs,idfpr,
101 dfpsj,idazbm,idht,emsfyxlh,ddjrq,bzdscsj)
102 select xhmx.id,xhmx.idxhdh,left(xhmx.idxhdh,4),xhmx.sxh,xhmx.idcp,
103 xhmx.id,xhmx.sname,xhmx.idpp,xhmx.szjys,xhmx.sptys,xhmx.sdw,xhmx.iddep,
104 xhmx.idowner,xhmx.idkh,xhmx.idkh,xhmx.dbnumber,xhmx.sph,xhmx.scw,xhmx.sfqdb,
105 xhmx.idfqdh,xhmx.idxhdh,left(xhmx.idxhdh,4),xhmx.dbprice,xhmx.dbmoney,xhmx.sgg,
106 'f8d6171e3fcdaed39f0919a9ec5f51ae',getdate(),'12979218489686204728368524150460',
107 xhmx.discount,xhd.scode+'-'+xhmx.sxh,'7c78c2e5fb906560a1ff52ea68a50b6d',xm.idazgcs,
108 xm.idfpr,xm.dfpsj,emp.iddep,xm.idxsht,'403fd62411e6c38ee1e7999ff53db78e',xhd.ddjrq,1
109 from sales xhd join salemx xhmx on xhd.id = xhmx.idxhdh join sazxm xm on xm.id = xhd.id
110 left join org_employee emp on emp.id = xm.idazgcs
111 where xhmx.id not in (select id from ssbazrw)
112 and xhd.id in (select id from sazxm) and xhmx.sfscazrw = 0
113 break
114 end else begin
115 insert into ssbazrw (id,idazxm,sazxmdb,syspos,idcpxx,idxhmxbh,swlmc,idpp,szjys,sptys,
116 sdw,iddep,idowner,idhtqykh,idzdkh,dbsl,sph,scw,sfqdb,idfqdh,idxhddh,sxhddb,dbprice,dbmoney,
117 swlgg,emazlx,created,createdby,szk,rwscode,emzt,idazgcs,idfpr,dfpsj,idazbm,idht,emsfyxlh,ddjrq,bzdscsj)
118 select xhmx.id+'-'+convert(varchar(100),@dbsl),xhmx.idxhdh,left(xhmx.idxhdh,4),xhmx.sxh,
119 xhmx.idcp,xhmx.id,xhmx.sname,xhmx.idpp,xhmx.szjys,xhmx.sptys,xhmx.sdw,xhmx.iddep,
120 xhmx.idowner,xhmx.idkh,xhmx.idkh,1,xhmx.sph,xhmx.scw,xhmx.sfqdb,idfqdh,xhmx.idxhdh,
121 left(xhmx.idxhdh,4),xhmx.dbprice,nullif(xhmx.discount,0) / nullif(xhmx.dbprice,0) * 1,xhmx.sgg,
122 'f8d6171e3fcdaed39f0919a9ec5f51ae',getdate(),'12979218489686204728368524150460',xhmx.discount,
123 xhd.scode+'-'+xhmx.sxh+'-'+convert(varchar(100),@dbsl),'7c78c2e5fb906560a1ff52ea68a50b6d',
124 xm.idazgcs,xm.idfpr,xm.dfpsj,emp.iddep,xm.idxsht,'f3406c8577eb9b62f9e5cf8a1895e8c2',xhd.ddjrq,1
125 from sales xhd join salemx xhmx on xhd.id = xhmx.idxhdh join sazxm xm on xm.id = xhd.id
126 left join org_employee emp on emp.id = xm.idazgcs
127 where xhmx.id+'-'+convert(varchar(100),@dbsl) not in (select id from ssbazrw) and xhmx.id = @id
128 and xhd.id in (select id from sazxm) and xhmx.sfscazrw = 1
129 set @dbsl = @dbsl + 1
130 if @dbsl > @maxdbsl begin break end
131 end
132 end
133 end
134 set @numrows = @numrows + 1
135 if @numrows > @maxnumrows begin break end
136 end
137 DROP TABLE #sazxmlist
138 DROP TABLE #amxtemplist
139
140 --安装任务明细总条数
141 update xm set xm.dbmxts = mx.tl from sazxm xm join
142 (select COUNT(*) tl,rw.idazxm id from ssbazrw rw group by rw.idazxm) mx
143 on mx.id = xm.id
144
145 --生成搬运任务
146 insert into sbyrw(id,scode,idazxm,idxsht,idhtqykh,idzdkh,idowner,iddep,idxhdh,dbmoney,idywy,idywbm,bzdscsj)
147 select xhd.id,'BY'+replace(convert(char(20),xhd.ddjrq,112)+right(xhd.id,3),' ',''),xhd.id,
148 xhd.idxmh,xhd.idkh,xhd.idkh,ht.idfwgcs,emp.iddep,xhd.id,xhd.dbmoney,ht.idowner,ht.iddep,1
149 from sales xhd join [contract] ht on ht.id = xhd.idxmh left join org_employee emp
150 on emp.id = ht.idfwgcs join sazxm xm on xm.id = xhd.id where xhd.id not in (select id from sbyrw) and
151 --datediff(dd,xhd.ddjrq,getdate()) = 0 and
152 ht.emcarry = 'bbe251eb0f1d867ed89eea05523a72f4'
153 end
154
