<%
Server.ScriptTimeout = 12000
'id0=left(session("username"),6)
id1=request.querystring("ID1")
id2=request.querystring("ID2")
id3=request.querystring("ID3")
id4=request.querystring("ID4")
id5=request.querystring("ID5")
id6=request.querystring("ID6")
com_name = left(request.querystring("com_name"),6)
daohuo = request.querystring("daohuo")
PP=REQUEST.querystring("PP")
DL=REQUEST.querystring("DL")
XPL=REQUEST.querystring("XPL")
LB = REQUEST.querystring("LB")
session("id1")=id1
session("md")=id1
session("id2")=id2
session("id3")=id3
session("id4")=id4
session("id5")=id5
SESSION("PP")=PP
SESSION("DL")=DL
SESSION("XPL")=XPL
session("id6")=id6
session("daohuo") = daohuo
session("com_name") = com_name
SESSION("LB") = LB
response.Write "商店：" & id1 & " "
%>
<%
'判断店铺代码与计算机名
'response.Write("判断店铺代码与计算机名0") & " "
'response.Write left(com_name,6) & " "
'response.Write left(id1,6) & " "
if trim(left(com_name,6))<>"395001" and trim(left(id1,6))="395001" then
'response.Write("判断店铺代码与计算机名")
'"
end if
%>
<%
if(session("id1")="") then
'response.Write("")
response.write(sqlq)
response.End()
end if
end if
%>
<%
'选择否 不输出网页内容
if id6="否" then
response.End()
end if
set rs=server.CreateObject("adodb.Recordset")
response.Write(daohuo)
if daohuo="全部" then
sql="SELECT sp.khdm khdm,sp.rq tjrq,ph3.RQ scrq,ph3a.RQ zjrq,concat(sp.spdm,kc.gg1dm) TM,sp.je6 je6,sp.byzd12 byzd12,sp.spdm spdm,sp.spmc spmc,KC.gg1dm gg1dm,g1.ggmc gg1mc, (case when isnull(jia,'')='' then bz else concat(JIA,'-',CENG,'-',GE,bz) end) WZ,sum(KC.s_sl) s_sl FROM ( select KHDM,SHANGPIN.spdm spdm,SHANGPIN.spmc spmc,rq,je6, (case when isnull(TJ.byzd12,0)=0 then SHANGPIN.sj1 else TJ.byzd12 end ) as byzd12 from shangpin left join ( SELECT N,djbh,rq,spdm,je6,byzd12,KHDM FROM (SELECT ROW_NUMBER() OVER(PARTITION BY QDTJDMH.KHDM,QDTJDMP.SPDM ORDER BY QDTJD.RQ DESC) AS N, QDTJD.DJBH,QDTJD.RQ, QDTJDMP.SPDM,QDTJDMH.KHDM, (case when isnull(QDTJDMP.JE5,0)>isnull(QDTJDMP.JE6,0) then QDTJDMP.JE5 when isnull(QDTJDMP.JE5,0)=isnull(QDTJDMP.JE6,0) then QDTJDMP.JE5 else QDTJDMP.JE5 END) je6, (case when QDTJD.LXDJ='001001' then QDTJDMP.BYZD12 when QDTJD.lxdj='011000' then QDTJDMP.je6 when QDTJD.LXDJ='001000' then qdtjdmp.je5 when QDTJD.LXDJ='001000' then qdtjdmp.je6 when QDTJD.lxdj='010000' then QDTJDMP.JE5 ELSE QDTJDMP.BYZD12 END) byzd12 FROM QDTJDMP,QDTJD,QDTJDMH WHERE QDTJDMP.DJBH=QDTJD.DJBH AND QDTJDMH.DJBH=QDTJDMP.DJBH AND QDTJDMH.KHDM='"&id1&"' AND QDTJDMP.SPDM like '%"&id5&"' and QDTJD.jz=1 and QDTJD.sp=0 and convert(varchar(10),rq_2,120)>=convert(varchar(10),getdate(),120) and convert(varchar(10),qdtjd.rq,120) >=convert(varchar(10),getdate()-"&id4&",120) ) AS AA WHERE N=1 ) as TJ on SHANGPIN.spdm=TJ.spdm where SHANGPIN.spdm=TJ.SPDM and BYZD3 IN ("&PP&") AND BYZD5 IN ("&id3&") AND BYZD8 IN ("&id2&") and FJSX2 in("&XPL&") and byzd4 in("&DL&") ) AS SP inner JOIN ( select ckdm,spdm ,gg1dm,sum(SPKCB.sl-isnull(XP.ssl,0)) as s_sl from spkcb left join ( SELECT sg.vshop,sgs.vstyle,sgs.vcolor,sgs.vsize,sum(sgs.fquantity) ssl FROM SG_Gathering SG,SG_Gatherings SGS where SG.VMBILLID=SGS.VMBILLID AND SG.VSHOP='"&id1&"' AND CONVERT(VARCHAR(10),DTDATE,120)=CONVERT(VARCHAR(10),GETDATE(),120) and sg.bcancel=0 and SG.btotal=0 group by sg.vshop,sgs.vstyle,sgs.vcolor,sgs.vsize HAVING sum(sgs.fquantity)<>0 ) as XP on SPKCB.spdm=XP.vstyle and SPKCB.ckdm=XP.vshop and XP.vcolor=SPKCB.gg1dm and XP.vsize=SPKCB.gg2dm group by ckdm,spdm ,gg1dm,gg2dm having sum(SPKCB.sl-isnull(XP.ssl,0))>0 ) as KC on KC.spdm=sp.spdm and sp.khdm=KC.ckdm left join ( select RQ,MD,SP,JIA,CENG,GE,bz from ( select ROW_NUMBER() OVER(PARTITION BY md,sp ORDER BY RQ,uprq DESC) AS N, RQ,md,sp,jia,ceng,ge,bz from al_spwz ) KW where KW.N=1 ) KW1 ON KW1.MD=KC.CKDM AND KW1.SP=KC.SPDM left join guige1 g1 on g1.ggdm=kc.GG1DM left join ( SELECT DM1,SPDM,RQ FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY DM1,SPDM ORDER BY RQ ASC) AS N1,DM1,SPDM,RQ FROM ( SELECT DM1,SPDM,RQ FROM (SELECT ROW_NUMBER() OVER(PARTITION BY DM1,SPDM ORDER BY RQ DESC) AS N,DM1,SPDM,RQ FROM (SELECT DISTINCT DM1,SPDM,RQ FROM VW_SDPHD) ph WHERE DM1='"&id1&"' AND SPDM like '%"&id5&"' UNION ALL SELECT ROW_NUMBER() OVER(PARTITION BY DM1,SPDM ORDER BY RQ ASC) AS N,DM1,SPDM,RQ FROM (SELECT DISTINCT DM1,SPDM,RQ FROM VW_SDPHD) ph WHERE DM1='"&id1&"' AND SPDM like '%"&id5&"' )ph1 WHERE N=1 )ph2 ) phh WHERE N1=2 ) as PH3 on PH3.spdm=kc.spdm and ph3.dm1=kc.CKDM left join ( SELECT DM1,SPDM,RQ FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY DM1,SPDM ORDER BY RQ ASC) AS N1,DM1,SPDM,RQ FROM ( SELECT DM1,SPDM,RQ FROM (SELECT ROW_NUMBER() OVER(PARTITION BY DM1,SPDM ORDER BY RQ DESC) AS N,DM1,SPDM,RQ FROM (SELECT DISTINCT DM1,SPDM,RQ FROM VW_SDPHD) ph WHERE DM1='"&id1&"' AND SPDM like '%"&id5&"' UNION ALL SELECT ROW_NUMBER() OVER(PARTITION BY DM1,SPDM ORDER BY RQ ASC) AS N,DM1,SPDM,RQ FROM (SELECT DISTINCT DM1,SPDM,RQ FROM VW_SDPHD) ph WHERE DM1='"&id1&"' AND SPDM like '%"&id5&"' )ph1a WHERE N=1 )ph2a ) phh WHERE N1=1 ) as PH3a on PH3a.spdm=kc.spdm and ph3a.dm1=kc.CKDM group by sp.khdm ,sp.rq ,ph3.RQ ,ph3.RQ ,ph3a.RQ ,sp.je6,sp.byzd12,sp.spdm,sp.spmc,KC.gg1dm,g1.ggmc,jia,ceng,ge,bz order by sp.khdm,sp.spdm,KC.gg1dm"
else
sql="SELECT sp.khdm khdm,sp.rq tjrq,ph3.RQ scrq,ph3a.RQ zjrq,concat(sp.spdm,kc.gg1dm) TM,sp.je6 je6,sp.byzd12 byzd12,sp.spdm spdm,sp.spmc spmc,KC.gg1dm gg1dm,g1.ggmc gg1mc, (case when isnull(jia,'')='' then bz else concat(JIA,'-',CENG,'-',GE,bz) end) WZ,sum(KC.s_sl) s_sl FROM ( select KHDM,SHANGPIN.spdm spdm,SHANGPIN.spmc spmc,rq,je6, (case when isnull(TJ.byzd12,0)=0 then SHANGPIN.sj1 else TJ.byzd12 end ) as byzd12 from shangpin left join ( SELECT N,djbh,rq,spdm,je6,byzd12,KHDM FROM (SELECT ROW_NUMBER() OVER(PARTITION BY QDTJDMH.KHDM,QDTJDMP.SPDM ORDER BY QDTJD.RQ DESC) AS N, QDTJD.DJBH,QDTJD.RQ, QDTJDMP.SPDM,QDTJDMH.KHDM, (case when isnull(QDTJDMP.JE5,0)>isnull(QDTJDMP.JE6,0) then QDTJDMP.JE5 when isnull(QDTJDMP.JE5,0)=isnull(QDTJDMP.JE6,0) then QDTJDMP.JE5 else QDTJDMP.JE5 END) je6, (case when QDTJD.LXDJ='001001' then QDTJDMP.BYZD12 when QDTJD.lxdj='011000' then QDTJDMP.je6 when QDTJD.LXDJ='001000' then qdtjdmp.je5 when QDTJD.LXDJ='001000' then qdtjdmp.je6 when QDTJD.lxdj='010000' then QDTJDMP.JE5 ELSE QDTJDMP.BYZD12 END) byzd12 FROM QDTJDMP,QDTJD,QDTJDMH WHERE QDTJDMP.DJBH=QDTJD.DJBH AND QDTJDMH.DJBH=QDTJDMP.DJBH AND QDTJDMH.KHDM='"&id1&"' AND QDTJDMP.SPDM like '%"&id5&"' and QDTJD.jz=1 and QDTJD.sp=0 and convert(varchar(10),rq_2,120)>=convert(varchar(10),getdate(),120) and convert(varchar(10),qdtjd.rq,120) >=convert(varchar(10),getdate()-"&id4&",120) ) AS AA WHERE N=1 ) as TJ on SHANGPIN.spdm=TJ.spdm where SHANGPIN.spdm=TJ.SPDM and BYZD3 IN ("&PP&") AND BYZD5 IN ("&id3&") AND BYZD8 IN ("&id2&") and FJSX2 in("&XPL&") and byzd4 in("&DL&") ) AS SP inner JOIN ( select ckdm,spdm ,gg1dm,sum(SPKCB.sl-isnull(XP.ssl,0)) as s_sl from spkcb left join ( SELECT sg.vshop,sgs.vstyle,sgs.vcolor,sgs.vsize,sum(sgs.fquantity) ssl FROM SG_Gathering SG,SG_Gatherings SGS where SG.VMBILLID=SGS.VMBILLID AND SG.VSHOP='"&id1&"' AND CONVERT(VARCHAR(10),DTDATE,120)=CONVERT(VARCHAR(10),GETDATE(),120) and sg.bcancel=0 and SG.btotal=0 group by sg.vshop,sgs.vstyle,sgs.vcolor,sgs.vsize HAVING sum(sgs.fquantity)<>0 ) as XP on SPKCB.spdm=XP.vstyle and SPKCB.ckdm=XP.vshop and XP.vcolor=SPKCB.gg1dm and XP.vsize=SPKCB.gg2dm group by ckdm,spdm ,gg1dm,gg2dm having sum(SPKCB.sl-isnull(XP.ssl,0))>0 ) as KC on KC.spdm=sp.spdm and sp.khdm=KC.ckdm left join ( select RQ,MD,SP,JIA,CENG,GE,bz from ( select ROW_NUMBER() OVER(PARTITION BY md,sp ORDER BY RQ,uprq DESC) AS N, RQ,md,sp,jia,ceng,ge,bz from al_spwz ) KW where KW.N=1 ) KW1 ON KW1.MD=KC.CKDM AND KW1.SP=KC.SPDM left join guige1 g1 on g1.ggdm=kc.GG1DM left join ( SELECT DM1,SPDM,RQ FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY DM1,SPDM ORDER BY RQ ASC) AS N1,DM1,SPDM,RQ FROM ( SELECT DM1,SPDM,RQ FROM (SELECT ROW_NUMBER() OVER(PARTITION BY DM1,SPDM ORDER BY RQ DESC) AS N,DM1,SPDM,RQ FROM (SELECT DISTINCT DM1,SPDM,RQ FROM VW_SDPHD) ph WHERE DM1='"&id1&"' AND SPDM like '%"&id5&"' UNION ALL SELECT ROW_NUMBER() OVER(PARTITION BY DM1,SPDM ORDER BY RQ ASC) AS N,DM1,SPDM,RQ FROM (SELECT DISTINCT DM1,SPDM,RQ FROM VW_SDPHD) ph WHERE DM1='"&id1&"' AND SPDM like '%"&id5&"' )ph1 WHERE N=1 )ph2 ) phh WHERE N1=2 ) as PH3 on PH3.spdm=kc.spdm and ph3.dm1=kc.CKDM inner join ( SELECT DM1,SPDM,RQ FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY DM1,SPDM ORDER BY RQ ASC) AS N1,DM1,SPDM,RQ FROM ( SELECT DM1,SPDM,RQ FROM (SELECT ROW_NUMBER() OVER(PARTITION BY DM1,SPDM ORDER BY RQ DESC) AS N,DM1,SPDM,RQ FROM (SELECT DISTINCT DM1,SPDM,RQ FROM VW_SDPHD) ph WHERE DM1='"&id1&"' AND SPDM like '%"&id5&"' UNION ALL SELECT ROW_NUMBER() OVER(PARTITION BY DM1,SPDM ORDER BY RQ ASC) AS N,DM1,SPDM,RQ FROM (SELECT DISTINCT DM1,SPDM,RQ FROM VW_SDPHD) ph WHERE DM1='"&id1&"' AND SPDM like '%"&id5&"' )ph1a WHERE N=1 )ph2a ) phh WHERE N1=1 ) as PH3a on PH3a.spdm=kc.spdm and ph3a.dm1=kc.CKDM and ph3a.RQ>=convert(varchar(10),getdate()-"&daohuo&",120) group by sp.khdm ,sp.rq ,ph3.RQ ,ph3.RQ ,ph3a.RQ ,sp.je6,sp.byzd12,sp.spdm,sp.spmc,KC.gg1dm,g1.ggmc,jia,ceng,ge,bz order by sp.khdm,sp.spdm,KC.gg1dm"
end if
'response.write sql
rs.open sql,conn,1,1
if rs.eof then %>
<% response.write("
")
set rs0=server.CreateObject("adodb.Recordset")
sql0="select ckdm,spdm ,gg1dm,g2.ggmc gg2mc,sum(SPKCB.sl-isnull(XP.ssl,0)) as sl from spkcb left join ( SELECT sg.vshop,sgs.vstyle,sgs.vcolor,sgs.vsize,sum(sgs.fquantity) ssl FROM SG_Gathering SG,SG_Gatherings SGS where SG.VMBILLID=SGS.VMBILLID AND SG.VSHOP='"&rs("khdm")&"' AND CONVERT(VARCHAR(10),DTDATE,120)=CONVERT(VARCHAR(10),GETDATE(),120) and sg.bcancel=0 and SG.btotal=0 group by sg.vshop,sgs.vstyle,sgs.vcolor,sgs.vsize HAVING sum(sgs.fquantity)<>0 ) as XP on SPKCB.spdm=XP.vstyle and SPKCB.ckdm=XP.vshop and XP.vcolor=SPKCB.gg1dm and XP.vsize=SPKCB.gg2dm and SPKCB.ckdm = '"&rs("khdm")&"' AND SPKCB.spdm = '"&rs("spdm")&"' AND gg1dm = '"&rs("gg1dm")&"' LEFT JOIN guige2 G2 on g2.ggdm=SPKCB.gg2dm where spkcb.spdm='"&rs("spdm")&"' and spkcb.ckdm='"&rs("khdm")&"' and spkcb.gg1dm='"&rs("gg1dm")&"' group by ckdm,spdm ,gg1dm,g2.ggmc having sum(SPKCB.sl-isnull(XP.ssl,0))>0 order by g2.ggmc "
'response.write sql0
rs0.open sql0,conn,1,1
do while not rs0.eof
response.write(""&rs0("gg2mc")&":"&rs0("sl")&" ")
rs0.movenext
loop
response.Write("
")
rs.movenext
loop
response.Write("
")
rs0.close
rs.close
%>
<%
set rs=nothing
set rs0=nothing
end if
%>
带图普通导出本页内容导出Excel文件
![](http://61.52.47.251:8808/temp/DengLu/product/"&rs("spdm")&".jpg)