apache-tomcat 8.5.73 版本

2018-03-23 13:55:41 | 15.303|15.303 | select bcode,bgcode,salordcode,salshipcode,date2,bgdate,date1,trademode,ywlx,ciffcy,yffcy,bxfcy,fobfcy,tsdate,c1,bz,c2,(case when ( select count(*) from ivoucher where acode = '6001010100' and icode = u_bg.salshipcode)>0 then '凭证已做' else '' end) as flag from u_bg where (1=1) and (1=1) and bcode like '101%' order by 2 2018-03-23 13:56:31 | 08.907|08.907 | select * from ( select a.salordcode,( select stuff(( select ',' + outordercode from salshiporder where salordcode=a.salordcode for xml path('')),1,1,'') as field from salshiporder where salordcode=a.salordcode group by salordcode) as outordercode, a.ccode,a.ccodecor,a.odate,a.financefinis,a.bcode, (case when yfp.rmb is not null then yfp.rmb else 0 end ) as yfzs, (case when yfrzp.rmb is not null then yfrzp.rmb else 0 end ) as yfrzzs, (case when bhrzp.rmb is not null then bhrzp.rmb else 0 end ) as bhrzzs, (case when fyp.rmb is not null then fyp.rmb else 0 end ) as fyzs, (case when yingfp.rmb is not null then yingfp.rmb else 0 end ) as yingfzs, (case when ys.rmb is not null then ys.rmb else 0 end )as ysrmb,(case when s.rmb is not null then s.rmb else 0 end ) as rmb, (case when ts.backtax is not null then ts.backtax else 0 end ) as backtax, (case when ssts.backtax is not null then ssts.backtax else 0 end ) as ssts, (case when yf.rmb is not null then yf.rmb else 0 end )as yf,(case when yfrz.rmb is not null then yfrz.rmb else 0 end ) as yfrz ,(case when bhrz.rmb is not null then bhrz.rmb else 0 end )as bhrz, (case when fy.rmb is not null then fy.rmb else 0 end ) as fy,(case when yingf.rmb is not null then yingf.rmb else 0 end ) as yingfu, (case when zjzc.rmb is not null then zjzc.rmb else 0 end ) as zjzc,(case when zjzr.rmb is not null then zjzr.rmb else 0 end ) zjzr, (case when ht.zslx is not null then ht.zslx else 0 end ) lx, (case when kf.rmb is not null then kf.rmb else 0 end ) yhfy, (case when tsfy.rmb is not null then tsfy.rmb else 0 end ) tsfy, (case when bxf.rmb is not null then bxf.rmb else 0 end ) bxf, (case when cgkp.rmb is not null then cgkp.rmb else 0 end ) cgkp, round(((case when ys.rmb is not null then ys.rmb else 0 end )+(case when s.rmb is not null then s.rmb else 0 end )+(case when ssts.backtax is not null and ssts.ynrz=5 then ssts.backtax else 0 end )+ (case when ts.backtax is not null then ts.backtax else 0 end )-(case when yf.rmb is not null then yf.rmb else 0 end ) -(case when yfrz.rmb is not null then yfrz.rmb else 0 end )-(case when bhrz.rmb is not null then bhrz.rmb else 0 end )-(case when fy.rmb is not null then fy.rmb else 0 end ) -(case when yingf.rmb is not null then yingf.rmb else 0 end )-(case when zjzc.rmb is not null then zjzc.rmb else 0 end )+(case when zjzr.rmb is not null then zjzr.rmb else 0 end )-(case when ht.zslx is not null then ht.zslx else 0 end ) -(case when kf.rmb is not null then kf.rmb else 0 end )-(case when tsfy.rmb is not null then tsfy.rmb else 0 end )-(case when bxf.rmb is not null then bxf.rmb else 0 end ) ),2) as zjye, round(((case when ys.rmb is not null then ys.rmb else 0 end )+(case when s.rmb is not null then s.rmb else 0 end )+(case when ts.backtax is not null then ts.backtax else 0 end ) +(case when ssts.backtax is not null and ssts.ynrz=5 then ssts.backtax else 0 end ) -(case when yfp.rmb is not null then yfp.rmb else 0 end )-(case when yfrzp.rmb is not null then yfrzp.rmb else 0 end )-(case when bhrzp.rmb is not null then bhrzp.rmb else 0 end )- (case when fyp.rmb is not null then fyp.rmb else 0 end )-(case when yingfp.rmb is not null then yingfp.rmb else 0 end )-(case when zjzc.rmb is not null then zjzc.rmb else 0 end )+(case when zjzr.rmb is not null then zjzr.rmb else 0 end ) -(case when ht.zslx is not null then ht.zslx else 0 end ) -(case when bxf.rmb is not null then bxf.rmb else 0 end ) -(case when kf.rmb is not null then kf.rmb else 0 end )-(case when tsfy.rmb is not null then tsfy.rmb else 0 end )),2) as zszjye from ( select a.salordcode,a.ccode,a.ccodecor,a.odate,b.financefinis,a.bcode from aabb a join salorder b on a.salordcode=b.salordcode and b.status='70' and isnull(ywlx,0)<>'5' ) a left join /*预收*/ ( select g.salordcode,sum(g.rmb) rmb from invoice m,invoiceg g where m.invicode=g.invicode and confirmflag=1 and m.status='70' and m.sheetcode='1218002' and g.invtype in ('358','356') and g.salordcode is not null group by g.salordcode) ys on a.salordcode=ys.salordcode left join /*应收*/ ( select g.salordcode,g.ccodecor,sum(g.rmb) rmb from invoice m,invoiceg g where m.invicode=g.invicode and confirmflag=1 and m.status='70' and m.sheetcode='1218002' and g.invtype='359' and g.mdc=1 and g.salordcode is not null group by g.salordcode,g.ccodecor) s on a.salordcode=s.salordcode and a.ccodecor=s.ccodecor /*退税融资款投保或者全额收汇退税款进资金池*/ left join ( select s.salordcode, round(sum(c.backtax),2) backtax from stockcost c,storeslistg g,salshiporder s where c.islcode = g.islcode and c.idx = g.idx and c.stockgid = g.stockgid and c.stockgcostid = g.stockgcostid and c.dc=1 and c.costtype=10 and s.redflags not in ('1') and s.ynrz not in ('5','6') and c.invkind in ('02','03') and s.salshipcode=g.slcodex and ( isnull(s.shqk,0)=1 or s.creditinscode is not null) group by s.salordcode) ts on a.salordcode=ts.salordcode /*纯综合服务实收退税*/ left join ( select salordcode,sum(backtax)backtax,ynrz from ( select g.salordcode,(case when ((isnull((s.shqk),0)=1 or (creditinscode) is not null)) and datediff(d,(t.odate),getdate())>=(case when (s.ccodecor)='000193' then 45 else 90 end) then (g.fcy) else 0 end ) backtax,ynrz from invoice m,invoiceg g ,salshiporder s,(/*成本认定日期*/ select max(odate) odate,g.slcodex from stockcost c,storeslistg g where c.islcode = g.islcode and c.idx = g.idx and c.stockgid = g.stockgid and c.stockgcostid = g.stockgcostid and c.dc=1 and c.costtype=10 and c.invkind in ('02','03') group by slcodex )t where m.invicode=g.invicode and s.salordcode=g.salordcode and m.sheetcode='1218016' and m.status = '70' and t.slcodex=s.salshipcode and g.salshipcodex=s.salshipcode ) tax group by salordcode,ynrz ) ssts on a.salordcode=ssts.salordcode left join /*退税手续费*/ ( select s.salordcode, ( case when isnull(max(s.dlf),0)>0 then round(max(s.usd)*max(s.dlf),2) else round(sum(c.backtax)*(case when isnull(max(tszyll),0)=0 then 0.04 else max(tszyll) end),2) end )as rmb from stockcost c,storeslistg g,salshiporder s where c.islcode = g.islcode and c.idx = g.idx and c.stockgid = g.stockgid and c.stockgcostid = g.stockgcostid and s.redflags not in ('1') and s.ynrz not in('5','6') and c.dc=1 and c.costtype=10 and c.invkind=02 and s.salshipcode=g.slcodex group by s.salordcode) tsfy on a.salordcode=tsfy.salordcode left join /*预付货款*/ ( select g.salordcode,sum(g.rmb) rmb from invoice m,invoiceg g where m.invicode=g.invicode and m.status='70' and m.sheetcode='1218104' and g.mdc=1 and g.invtype='1800' group by g.salordcode) yf on a.salordcode=yf.salordcode left join /*预付货款实付*/ ( select g.salordcode,sum(g.rmb) rmb from invoice m,invoiceg g where m.invicode=g.invicode and m.status='70' and m.sheetcode='1218104' and g.mdc=1 and g.invtype='1800' and m.fcypaied is not null group by g.salordcode) yfp on a.salordcode=yfp.salordcode left join /*付融资款*/ ( select g.salordcode,sum(g.rmb) rmb from invoice m,invoiceg g where m.invicode=g.invicode and m.status='70' and m.sheetcode='1218104' and g.mdc=1 and g.invtype='1100' group by g.salordcode) yfrz on a.salordcode=yfrz.salordcode left join /*付融资款实付*/ ( select g.salordcode,sum(g.rmb) rmb from invoice m,invoiceg g where m.invicode=g.invicode and m.status='70' and m.sheetcode='1218104' and g.mdc=1 and g.invtype='1100' and m.fcypaied is not null group by g.salordcode) yfrzp on a.salordcode=yfrzp.salordcode left join /*备货资款*/ ( select g.salordcode,sum(g.rmb) rmb from invoice m,invoiceg g where m.invicode=g.invicode and m.status='70' and m.sheetcode='1218104' and g.mdc=1 and g.invtype='1105' group by g.salordcode) bhrz on a.salordcode=bhrz.salordcode lef