<%
set rs=server.CreateObject("adodb.Recordset")
'---------------程梦鸽区域---------------------
sql=" SELECT DYXS.khsx1mc,DYXS.KHMC,JRXS.JRSL,JRXS.jrje,DYXS.yxsl,DYXS.yxje,QNYXS.qnyxsl,QNYXS.qnyxje, "
sql=sql+ " cast((CASE when QNYXS.qnyxsl=0 then 0 else round(CONVERT(FLOAT,(DYXS.yxsl-QNYXS.qnyxsl)/QNYXS.qnyxsl),4)*100 end) as varchar)+'%' as sszz, "
sql=sql+ " cast((CASE when QNYXS.qnyxje=0 then 0 else round(convert(FLOAT,(DYXS.yxje-QNYXS.qnyxje)/QNYXS.qnyxje),4)*100 end) as varchar)+'%' as jezz, "
sql=sql+ " ZB.zbje,cast((CASE when ZB.zbje=0 then 0 else round(JRXS.jrje/ZB.zbje,4) END)*100 as varchar)+'%' as dcl, "
sql=sql+ " cast(round(convert(float,day(getdate()-1))/CONVERT(float,Day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)))),4)*100 as varchar)+'%' as sjjd "
sql=sql+ " FROM ( "
'sql=sql+ " --今年当月销 "
sql=sql+ " SELECT vw_cust_kehu.khsx1mc,vw_cust_kehu.khmc,sum(VW_LSXHMX.sl) yxsl,sum(VW_LSXHMX.JE) yxje FROM VW_LSXHMX "
sql=sql+ " left join vw_cust_kehu on VW_CUST_KEHU.khdm=VW_LSXHMX.DM1 "
sql=sql+ " WHERE DM1 IN( "
sql=sql+ " SELECT KHDM FROM VW_CUST_KEHU WHERE KHSX1MC='程梦鸽' and (KHSX3MC='重点考核店铺' OR KHSX3MC='已安装') AND KHMC NOT LIKE '%停%' "
sql=sql+ " ) AND RQ>=CONVERT(VARCHAR(10),dateadd(d,-day(getdate())+1,getdate()),23) "
sql=sql+ " GROUP BY vw_cust_kehu.khsx1mc,vw_cust_kehu.khmc "
sql=sql+ " ) AS DYXS "
sql=sql+ " LEFT JOIN ( "
'sql=sql+ " --今日销售 "
sql=sql+ " select khsx1mc,khmc,sum(jrsl) jrsl,sum(jrje) jrje from ( "
sql=sql+ " SELECT vw_cust_kehu.khsx1mc,vw_cust_kehu.khmc,sum(VW_LSXHMX.sl) jrsl,sum(VW_LSXHMX.JE) jrje FROM VW_LSXHMX "
sql=sql+ " left join vw_cust_kehu on VW_CUST_KEHU.khdm=VW_LSXHMX.DM1 "
sql=sql+ " WHERE DM1 IN( "
sql=sql+ " SELECT KHDM FROM VW_CUST_KEHU WHERE KHSX1MC='程梦鸽' and (KHSX3MC='重点考核店铺' OR KHSX3MC='已安装') AND KHMC NOT LIKE '%停%' "
sql=sql+ " ) AND RQ>=CONVERT(VARCHAR(10),getdate(),23) "
sql=sql+ " GROUP BY vw_cust_kehu.khsx1mc,vw_cust_kehu.khmc "
sql=sql+ " union ALL "
sql=sql+ " SELECT vw_cust_kehu.khsx1mc,vw_cust_kehu.khmc,sum(SG_Gathering.fQuantity) jrsl,sum(SG_Gathering.fRealMoney) jrje from SG_Gathering "
sql=sql+ " left join VW_CUST_KEHU on VW_CUST_KEHU.khdm=SG_Gathering.vShop "
sql=sql+ " where vshop in( "
sql=sql+ " SELECT KHDM FROM VW_CUST_KEHU WHERE KHSX1MC='程梦鸽' and (KHSX3MC='重点考核店铺' OR KHSX3MC='已安装') AND KHMC NOT LIKE '%停%' "
sql=sql+ " ) and CONVERT(VARCHAR(10),dtdate,23)=CONVERT(VARCHAR(10),GETDATE(),23) and btotal=0 and bcancel=0 and bputup=0 "
sql=sql+ " and not EXISTS (select 1 from VW_LSXHMX where VW_LSXHMX.dm1=SG_Gathering.vShop and VW_LSXHMX.rq=CONVERT(VARCHAR(10),GETDATE(),23)) "
sql=sql+ " GROUP BY vw_cust_kehu.khsx1mc,vw_cust_kehu.khmc) as jrxs "
sql=sql+ " GROUP BY khsx1mc,khmc "
sql=sql+ " ) AS JRXS ON JRXS.khmc=DYXS.khmc "
sql=sql+ " LEFT JOIN ( "
'sql=sql+ " --去年当月 "
sql=sql+ " SELECT vw_cust_kehu.khsx1mc,vw_cust_kehu.khmc,sum(VW_LSXHMX.sl) qnyxsl,sum(VW_LSXHMX.JE) qnyxje FROM VW_LSXHMX "
sql=sql+ " left join vw_cust_kehu on VW_CUST_KEHU.khdm=VW_LSXHMX.DM1 "
sql=sql+ " WHERE DM1 IN( "
sql=sql+ " SELECT KHDM FROM VW_CUST_KEHU WHERE KHSX1MC='程梦鸽' and (KHSX3MC='重点考核店铺' OR KHSX3MC='已安装') AND KHMC NOT LIKE '%停%' "
sql=sql+ " ) AND RQ >= Convert(varchar(10), DateAdd(dd, -1 * (Day(Convert(varchar(10), DateAdd(yy, -1, getdate()), 120)) - 1), Convert(varchar(10), DateAdd(yy, -1, getdate()), 120)), 120) "
sql=sql+ " and RQ<=CONVERT(varchar(10),dateAdd(yy,-1,getdate()),120) "
sql=sql+ " GROUP BY vw_cust_kehu.khsx1mc,vw_cust_kehu.khmc "
sql=sql+ " ) AS QNYXS ON QNYXS.KHMC=DYXS.KHMC "
sql=sql+ " left join ( "
sql=sql+ " select khmc,zbje from IPOS月度指标 where nd=YEAR(GETDATE()) and mn=month(GETDATE()) "
sql=sql+ " ) as ZB on ZB.khmc=DYXS.KHMC "
'RESPONSE.WRITE sql
rs.open sql,conn,1,1
if rs.eof then %>
<%else
'response.write(""&rs("SPDM")&""&rs("spmc")&""&rs("bzsj")&"![](../product/"&rs("spdm")&".jpg)
")
'response.Write ("| 门店 | 商品代码 | 商品名称 | 批发价 | 零售价 | 会员价 | 颜色 | 发货 | 累销 | 14天销 | 库存数量 | 图片 | 库存明细 | ")
%>
| align="center">程梦鸽 |
<%
do while not rs.eof
response.Write ("")
response.Write("| "&rs("khmc")&" | "&rs("JRSL")&" | "&rs("JRJE")&" | "&rs("yxsl")&" | "&rs("yxje")&" | "&rs("qnyxsl")&" | "&rs("qnyxje")&" | "&rs("sszz")&" | "&rs("jezz")&" | "&rs("zbje")&" | "&rs("dcl")&" | "&rs("sjjd")&" | ")
response.Write("
")
rs.movenext
loop
rs.close
'set rs=nothing
'程梦鸽区域合计
sql = " SELECT khsx1mc,sum(jrsl) jrsl,sum(jrje) jrje,sum(yxsl) yxsl,sum(yxje) yxje,sum(qnyxsl) qnyxsl,sum(qnyxje) qnyxje, "
sql = sql + " cast((CASE when sum(qnyxsl)=0 then 0 else round(CONVERT(float,(sum(yxsl)-sum(qnyxsl))/sum(qnyxsl)),4) end)*100 as varchar)+'%' as sszz, "
sql = sql + " cast((CASE when sum(qnyxje)=0 then 0 else round(convert(float,((sum(yxje)-sum(qnyxje))/sum(qnyxje))),4) end)*100 as VARCHAR)+'%' as jezz, "
sql = sql + " sum(zbje) zbje,cast((CASE when sum(zbje)=0 then 0 else round(sum(jrje)/sum(zbje),4) END)*100 as varchar)+'%' as dcl, "
sql = sql + " cast(round(convert(float,day(getdate()-1))/CONVERT(float,Day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)))),4)*100 as varchar)+'%' as sjjd "
sql = sql + " FROM ( "
sql = sql + " SELECT DYXS.khsx1mc,JRXS.JRSL,JRXS.jrje,DYXS.yxsl,DYXS.yxje,QNYXS.qnyxsl,QNYXS.qnyxje, "
sql = sql + " (CASE when QNYXS.qnyxsl=0 then 0 else (DYXS.yxsl-QNYXS.qnyxsl)/QNYXS.qnyxsl end) as sszz, "
sql = sql + " (CASE when QNYXS.qnyxje=0 then 0 else (DYXS.yxje-QNYXS.qnyxje)/QNYXS.qnyxje end) as jezz, "
sql = sql + " ZB.zbje zbje,(CASE when ZB.zbje=0 then 0 else round(JRXS.jrje/ZB.zbje,4) END) as dcl, "
sql = sql + " round(convert(float,day(getdate()-1))/CONVERT(float,Day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)))),4) as sjjd "
sql = sql + " FROM ( "
' sql = sql + " --今年当月销 "
sql = sql + " SELECT vw_cust_kehu.khsx1mc,vw_cust_kehu.khmc,sum(VW_LSXHMX.sl) yxsl,sum(VW_LSXHMX.JE) yxje FROM VW_LSXHMX "
sql = sql + " left join vw_cust_kehu on VW_CUST_KEHU.khdm=VW_LSXHMX.DM1 "
sql = sql + " WHERE DM1 IN( "
sql = sql + " SELECT KHDM FROM VW_CUST_KEHU WHERE KHSX1MC='程梦鸽' and (KHSX3MC='重点考核店铺' OR KHSX3MC='已安装') AND KHMC NOT LIKE '%停%' "
sql = sql + " ) AND RQ>=CONVERT(VARCHAR(10),dateadd(d,-day(getdate())+1,getdate()),23) "
sql = sql + " GROUP BY vw_cust_kehu.khsx1mc,vw_cust_kehu.khmc "
sql = sql + " ) AS DYXS "
sql = sql + " LEFT JOIN ( "
' sql = sql + " --今日销售 "
sql = sql + " select khsx1mc,khmc,sum(jrsl) jrsl,sum(jrje) jrje from ( "
sql = sql + " SELECT vw_cust_kehu.khsx1mc,vw_cust_kehu.khmc,sum(VW_LSXHMX.sl) jrsl,sum(VW_LSXHMX.JE) jrje FROM VW_LSXHMX "
sql = sql + " left join vw_cust_kehu on VW_CUST_KEHU.khdm=VW_LSXHMX.DM1 "
sql = sql + " WHERE DM1 IN( "
sql = sql + " SELECT KHDM FROM VW_CUST_KEHU WHERE KHSX1MC='程梦鸽' and (KHSX3MC='重点考核店铺' OR KHSX3MC='已安装') AND KHMC NOT LIKE '%停%' "
sql = sql + " ) AND RQ>=CONVERT(VARCHAR(10),getdate(),23) "
sql = sql + " GROUP BY vw_cust_kehu.khsx1mc,vw_cust_kehu.khmc "
sql = sql + " union ALL "
sql = sql + " SELECT vw_cust_kehu.khsx1mc,vw_cust_kehu.khmc,sum(SG_Gathering.fQuantity) jrsl,sum(SG_Gathering.fRealMoney) jrje from SG_Gathering "
sql = sql + " left join VW_CUST_KEHU on VW_CUST_KEHU.khdm=SG_Gathering.vShop "
sql = sql + " where vshop in( "
sql = sql + " SELECT KHDM FROM VW_CUST_KEHU WHERE KHSX1MC='程梦鸽' and (KHSX3MC='重点考核店铺' OR KHSX3MC='已安装') AND KHMC NOT LIKE '%停%' "
sql = sql + " ) and CONVERT(VARCHAR(10),dtdate,23)=CONVERT(VARCHAR(10),GETDATE(),23) and btotal=0 and bcancel=0 and bputup=0 "
sql = sql + " and not EXISTS (select 1 from VW_LSXHMX where VW_LSXHMX.dm1=SG_Gathering.vShop and VW_LSXHMX.rq=CONVERT(VARCHAR(10),GETDATE(),23)) "
sql = sql + " GROUP BY vw_cust_kehu.khsx1mc,vw_cust_kehu.khmc) as jrxs "
sql = sql + " GROUP BY khsx1mc,khmc "
sql = sql + " ) AS JRXS ON JRXS.khmc=DYXS.khmc "
sql = sql + " LEFT JOIN ( "
'sql = sql + " --去年当月 "
sql = sql + " SELECT vw_cust_kehu.khsx1mc,vw_cust_kehu.khmc,sum(VW_LSXHMX.sl) qnyxsl,sum(VW_LSXHMX.JE) qnyxje FROM VW_LSXHMX "
sql = sql + " left join vw_cust_kehu on VW_CUST_KEHU.khdm=VW_LSXHMX.DM1 "
sql = sql + " WHERE DM1 IN( "
sql = sql + " SELECT KHDM FROM VW_CUST_KEHU WHERE KHSX1MC='程梦鸽' and (KHSX3MC='重点考核店铺' OR KHSX3MC='已安装') AND KHMC NOT LIKE '%停%' "
sql = sql + " ) AND RQ >= Convert(varchar(10), DateAdd(dd, -1 * (Day(Convert(varchar(10), DateAdd(yy, -1, getdate()), 120)) - 1), Convert(varchar(10), DateAdd(yy, -1, getdate()), 120)), 120) "
sql = sql + " and RQ<=CONVERT(varchar(10),dateAdd(yy,-1,getdate()),120) "
sql = sql + " GROUP BY vw_cust_kehu.khsx1mc,vw_cust_kehu.khmc "
sql = sql + " ) AS QNYXS ON QNYXS.KHMC=DYXS.KHMC "
sql = sql + " left join ( "
sql = sql + " select khmc,zbje from IPOS月度指标 where nd=YEAR(GETDATE()) and mn=month(GETDATE()) "
sql = sql + " ) as ZB on ZB.khmc=DYXS.KHMC "
sql = sql + " ) AS hz1 "
sql = sql + " GROUP BY khsx1mc "
'RESPONSE.WRITE sql
rs.open sql,conn,1,1
if rs.eof then %>
<%else
'response.write(""&rs("SPDM")&""&rs("spmc")&""&rs("bzsj")&"")
'response.Write ("| 门店 | 商品代码 | 商品名称 | 批发价 | 零售价 | 会员价 | 颜色 | 发货 | 累销 | 14天销 | 库存数量 | 图片 | 库存明细 | ")
%>
<%
do while not rs.eof
response.Write ("
")
response.Write("| 片区小计 | "&rs("JRSL")&" | "&rs("JRJE")&" | "&rs("yxsl")&" | "&rs("yxje")&" | "&rs("qnyxsl")&" | "&rs("qnyxje")&" | "&rs("sszz")&" | "&rs("jezz")&" | "&rs("zbje")&" | "&rs("dcl")&" | "&rs("sjjd")&" | ")
response.Write("
")
rs.movenext
loop
rs.close
response.Write("
")