import cx_Oracle
import pandas as pd
from reportlab.lib import colors
from reportlab.lib.pagesizes import landscape, letter
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, PageBreak, Spacer
from reportlab.lib.units import inch
import matplotlib.pyplot as plt
from io import BytesIO
import numpy as np
from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
def fetch_store_data(store_id):
"""从数据库获取门店数据"""
engine = create_engine('oracle+cx_oracle://admin:xxb20140415@192.168.3.16:1521/?service_name=zzjdata')
# 执行四个SQL查询
QUERIES = [
# 整体销售数据
"""
------整体销售数据
select e.c_mdfq 大区,e.c_dq 地市,e.c_mdfq1 地区,e.tjbh 编码,e.mc 门店,
nvl(a1.jshj,0) 本周总销售,case when nvl(a1.jshj,0)<>0 then round(nvl(a1.zhml,0)*100/nvl(a1.jshj,0),2) else 0 end || '%' 本周综合毛利率,nvl(a1.kds,0) 本周总客单,case when nvl(a1.kds,0)=0 then 0 else round(nvl(a1.jshj,0)/nvl(a1.kds,0),2) end 本周客单价,
nvl(a1.dsxse,0) 本周线上销售,nvl(a1.dskds,0) 本周线上客单,nvl(a1.jshj,0)-nvl(a1.dsxse,0) 本周线下销售,nvl(a1.kds,0)-nvl(a1.dskds,0) 本周线下客单,
nvl(a1.dtp,0) 本周DTP销售,case when nvl(a1.jshj,0)<>0 then round(nvl(a1.dtp,0)*100/nvl(a1.jshj,0),2) else 0 end || '%' 本周DTP销售占比,
nvl(b1.jshj,0) 本周排除DTP销售,case when nvl(b1.jshj,0)<>0 then round(nvl(b1.ml,0)*100/nvl(b1.jshj,0),2) else 0 end || '%' 本周排除DTP综合毛利率,nvl(b1.kds,0) 本周排除DTP客单,case when nvl(b1.kds,0)=0 then 0 else round(nvl(b1.jshj,0)/nvl(b1.kds,0),2) end 本周排除DTP客单价,
nvl(a1.cyxse,0) 本周中药销售,case when nvl(a1.jshj,0)<>0 then round(nvl(a1.cyxse,0)*100/nvl(a1.jshj,0),2) else 0 end || '%' 本周中药销售占比,
nvl(a1.nf,0) 本周内方销售,case when nvl(a1.jshj,0)<>0 then round(nvl(a1.nf,0)*100/nvl(a1.jshj,0),2) else 0 end || '%' 本周内方销售占比,
nvl(a1.gf,0) 本周膏方销售,case when nvl(a1.jshj,0)<>0 then round(nvl(a1.gf,0)*100/nvl(a1.jshj,0),2) else 0 end || '%' 本周膏方销售占比,
nvl(c1.jshj,0) 本周会员销售,case when nvl(a1.jshj,0)<>0 then round(nvl(c1.jshj,0)*100/nvl(a1.jshj,0),2) else 0 end || '%' 本周会员销售占比,
nvl(c1.kds,0) 本周会员客单,nvl(c1.hys,0) 本周会员数,case when nvl(c1.kds,0)=0 then 0 else round(nvl(c1.jshj,0)/nvl(c1.kds,0),2) end 本周会员客单价,
case when nvl(c1.hys,0)=0 then 0 else round(nvl(c1.jshj,0)/nvl(c1.hys,0),2) end 本周会员人均消费,case when nvl(c1.hys,0)=0 then 0 else round(nvl(c1.kds,0)/nvl(c1.hys,0),2) end 本周会员消费频次,
nvl(d1.jshj,0) 本周医保会员销售,case when nvl(c1.jshj,0)<>0 then round(nvl(d1.jshj,0)*100/nvl(c1.jshj,0),2) else 0 end || '%' 本周医保会员销售占比,
nvl(d1.kds,0) 本周医保会员客单,nvl(d1.hys,0) 本周医保会员数,case when nvl(d1.kds,0)=0 then 0 else round(nvl(d1.jshj,0)/nvl(d1.kds,0),2) end 本周医保会员客单价,
case when nvl(d1.hys,0)=0 then 0 else round(nvl(d1.jshj,0)/nvl(d1.hys,0),2) end 本周医保会员人均消费,case when nvl(d1.hys,0)=0 then 0 else round(nvl(d1.kds,0)/nvl(d1.hys,0),2) end 本周医保会员消费频次,
nvl(a2.jshj,0) 上周总销售,case when nvl(a2.jshj,0)<>0 then round(nvl(a2.zhml,0)*100/nvl(a2.jshj,0),2) else 0 end || '%' 上周综合毛利率,nvl(a2.kds,0) 上周总客单,case when nvl(a2.kds,0)=0 then 0 else round(nvl(a2.jshj,0)/nvl(a2.kds,0),2) end 上周客单价,
nvl(a2.dsxse,0) 上周线上销售,nvl(a2.dskds,0) 上周线上客单,nvl(a2.jshj,0)-nvl(a2.dsxse,0) 上周线下销售,nvl(a2.kds,0)-nvl(a2.dskds,0) 上周线下客单,
nvl(a2.dtp,0) 上周DTP销售,case when nvl(a2.jshj,0)<>0 then round(nvl(a2.dtp,0)*100/nvl(a2.jshj,0),2) else 0 end || '%' 上周DTP销售占比,
nvl(b2.jshj,0) 上周排除DTP销售,case when nvl(b2.jshj,0)<>0 then round(nvl(b2.ml,0)*100/nvl(b2.jshj,0),2) else 0 end || '%' 上周排除DTP综合毛利率,nvl(b2.kds,0) 上周排除DTP客单,case when nvl(b2.kds,0)=0 then 0 else round(nvl(b2.jshj,0)/nvl(b2.kds,0),2) end 上周排除DTP客单价,
nvl(a2.cyxse,0) 上周中药销售,case when nvl(a2.jshj,0)<>0 then round(nvl(a2.cyxse,0)*100/nvl(a2.jshj,0),2) else 0 end || '%' 上周中药销售占比,
nvl(a2.nf,0) 上周内方销售,case when nvl(a2.jshj,0)<>0 then round(nvl(a2.nf,0)*100/nvl(a2.jshj,0),2) else 0 end || '%' 上周内方销售占比,
nvl(a2.gf,0) 上周膏方销售,case when nvl(a2.jshj,0)<>0 then round(nvl(a2.gf,0)*100/nvl(a2.jshj,0),2) else 0 end || '%' 上周膏方销售占比,
nvl(c2.jshj,0) 上周会员销售,case when nvl(a2.jshj,0)<>0 then round(nvl(c2.jshj,0)*100/nvl(a2.jshj,0),2) else 0 end || '%' 上周会员销售占比,
nvl(c2.kds,0) 上周会员客单,nvl(c2.hys,0) 上周会员数,case when nvl(c2.kds,0)=0 then 0 else round(nvl(c2.jshj,0)/nvl(c2.kds,0),2) end 上周会员客单价,
case when nvl(c2.hys,0)=0 then 0 else round(nvl(c2.jshj,0)/nvl(c2.hys,0),2) end 上周会员人均消费,case when nvl(c2.hys,0)=0 then 0 else round(nvl(c2.kds,0)/nvl(c2.hys,0),2) end 上周会员消费频次,
nvl(d2.jshj,0) 上周医保会员销售,case when nvl(c2.jshj,0)<>0 then round(nvl(d2.jshj,0)*100/nvl(c2.jshj,0),2) else 0 end || '%' 上周医保会员销售占比,
nvl(d2.kds,0) 上周医保会员客单,nvl(d2.hys,0) 上周医保会员数,case when nvl(d2.kds,0)=0 then 0 else round(nvl(d2.jshj,0)/nvl(d2.kds,0),2) end 上周医保会员客单价,
case when nvl(d2.hys,0)=0 then 0 else round(nvl(d2.jshj,0)/nvl(d2.hys,0),2) end 上周医保会员人均消费,case when nvl(d2.hys,0)=0 then 0 else round(nvl(d2.kds,0)/nvl(d2.hys,0),2) end 上周医保会员消费频次,
round(nvl(a3.jshj,0)/10000 ,2) 今年总销售,case when nvl(a3.jshj,0)<>0 then round(nvl(a3.zhml,0)*100/nvl(a3.jshj,0),2) else 0 end || '%' 今年综合毛利率,nvl(a3.kds,0) 今年总客单,case when nvl(a3.kds,0)=0 then 0 else round(nvl(a3.jshj,0)/nvl(a3.kds,0),2) end 今年客单价,
round(nvl(a3.dsxse,0)/10000 ,2) 今年线上销售,nvl(a3.dskds,0) 今年线上客单,round((nvl(a3.jshj,0)-nvl(a3.dsxse,0))/10000 ,2) 今年线下销售,nvl(a3.kds,0)-nvl(a3.dskds,0) 今年线下客单,
round(nvl(a3.dtp,0)/10000 ,2) 今年DTP销售,case when nvl(a3.jshj,0)<>0 then round(nvl(a3.dtp,0)*100/nvl(a3.jshj,0),2) else 0 end || '%' 今年DTP销售占比,
round(nvl(b3.jshj,0)/10000 ,2) 今年排除DTP销售,case when nvl(b3.jshj,0)<>0 then round(nvl(b3.ml,0)*100/nvl(b3.jshj,0),2) else 0 end || '%' 今年排除DTP综合毛利率,nvl(b3.kds,0) 今年排除DTP客单,case when nvl(b3.kds,0)=0 then 0 else round(nvl(b3.jshj,0)/nvl(b3.kds,0),2) end 今年排除DTP客单价,
round(nvl(a3.cyxse,0)/10000 ,2) 今年中药销售,case when nvl(a3.jshj,0)<>0 then round(nvl(a3.cyxse,0)*100/nvl(a3.jshj,0),2) else 0 end || '%' 今年中药销售占比,
round(nvl(a3.nf,0)/10000 ,2) 今年内方销售,case when nvl(a3.jshj,0)<>0 then round(nvl(a3.nf,0)*100/nvl(a3.jshj,0),2) else 0 end || '%' 今年内方销售占比,
round(nvl(a3.gf,0)/10000 ,2) 今年膏方销售,case when nvl(a3.jshj,0)<>0 then round(nvl(a3.gf,0)*100/nvl(a3.jshj,0),2) else 0 end || '%' 今年膏方销售占比,
round(nvl(c3.jshj,0)/10000 ,2) 今年会员销售,case when nvl(a3.jshj,0)<>0 then round(nvl(c3.jshj,0)*100/nvl(a3.jshj,0),2) else 0 end || '%' 今年会员销售占比,
nvl(c3.kds,0) 今年会员客单,nvl(c3.hys,0) 今年会员数,case when nvl(c3.kds,0)=0 then 0 else round(nvl(c3.jshj,0)/nvl(c3.kds,0),2) end 今年会员客单价,
case when nvl(c3.hys,0)=0 then 0 else round(nvl(c3.jshj,0)/nvl(c3.hys,0),2) end 今年会员人均消费,case when nvl(c3.hys,0)=0 then 0 else round(nvl(c3.kds,0)/nvl(c3.hys,0),2) end 今年会员消费频次,
round(nvl(d3.jshj,0)/10000 ,2) 今年医保会员销售,case when nvl(c3.jshj,0)<>0 then round(nvl(d3.jshj,0)*100/nvl(c3.jshj,0),2) else 0 end || '%' 今年医保会员销售占比,
nvl(d3.kds,0) 今年医保会员客单,nvl(d3.hys,0) 今年医保会员数,case when nvl(d3.kds,0)=0 then 0 else round(nvl(d3.jshj,0)/nvl(d3.kds,0),2) end 今年医保会员客单价,
case when nvl(d3.hys,0)=0 then 0 else round(nvl(d3.jshj,0)/nvl(d3.hys,0),2) end 今年医保会员人均消费,case when nvl(d3.hys,0)=0 then 0 else round(nvl(d3.kds,0)/nvl(d3.hys,0),2) end 今年医保会员消费频次,
round(nvl(a4.jshj,0)/10000 ,2) 去年总销售,case when nvl(a4.jshj,0)<>0 then round(nvl(a4.zhml,0)*100/nvl(a4.jshj,0),2) else 0 end || '%' 去年综合毛利率,nvl(a4.kds,0) 去年总客单,case when nvl(a4.kds,0)=0 then 0 else round(nvl(a4.jshj,0)/nvl(a4.kds,0),2) end 去年客单价,
round(nvl(a4.dsxse,0)/10000 ,2) 去年线上销售,nvl(a4.dskds,0) 去年线上客单,round((nvl(a4.jshj,0)-nvl(a4.dsxse,0))/10000 ,2) 去年线下销售,nvl(a4.kds,0)-nvl(a4.dskds,0) 去年线下客单,
round(nvl(a4.dtp,0)/10000 ,2) 去年DTP销售,case when nvl(a4.jshj,0)<>0 then round(nvl(a4.dtp,0)*100/nvl(a4.jshj,0),2) else 0 end || '%' 去年DTP销售占比,
round(nvl(b4.jshj,0)/10000 ,2) 去年排除DTP销售,case when nvl(b4.jshj,0)<>0 then round(nvl(b4.ml,0)*100/nvl(b4.jshj,0),2) else 0 end || '%' 去年排除DTP综合毛利率,nvl(b4.kds,0) 去年排除DTP客单,case when nvl(b4.kds,0)=0 then 0 else round(nvl(b4.jshj,0)/nvl(b4.kds,0),2) end 去年排除DTP客单价,
round(nvl(a4.cyxse,0)/10000 ,2) 去年中药销售,case when nvl(a4.jshj,0)<>0 then round(nvl(a4.cyxse,0)*100/nvl(a4.jshj,0),2) else 0 end || '%' 去年中药销售占比,
round(nvl(a4.nf,0)/10000 ,2) 去年内方销售,case when nvl(a4.jshj,0)<>0 then round(nvl(a4.nf,0)*100/nvl(a4.jshj,0),2) else 0 end || '%' 去年内方销售占比,
round(nvl(a4.gf,0)/10000 ,2) 去年膏方销售,case when nvl(a4.jshj,0)<>0 then round(nvl(a4.gf,0)*100/nvl(a4.jshj,0),2) else 0 end || '%' 去年膏方销售占比,
round(nvl(c4.jshj,0)/10000 ,2) 去年会员销售,case when nvl(a4.jshj,0)<>0 then round(nvl(c4.jshj,0)*100/nvl(a4.jshj,0),2) else 0 end || '%' 去年会员销售占比,
nvl(c4.kds,0) 去年会员客单,nvl(c4.hys,0) 去年会员数,case when nvl(c4.kds,0)=0 then 0 else round(nvl(c4.jshj,0)/nvl(c4.kds,0),2) end 去年会员客单价,
case when nvl(c4.hys,0)=0 then 0 else round(nvl(c4.jshj,0)/nvl(c4.hys,0),2) end 去年会员人均消费,case when nvl(c4.hys,0)=0 then 0 else round(nvl(c4.kds,0)/nvl(c4.hys,0),2) end 去年会员消费频次,
round(nvl(d4.jshj,0)/10000 ,2) 去年医保会员销售,case when nvl(c4.jshj,0)<>0 then round(nvl(d4.jshj,0)*100/nvl(c4.jshj,0),2) else 0 end || '%' 去年医保会员销售占比,
nvl(d4.kds,0) 去年医保会员客单,nvl(d4.hys,0) 去年医保会员数,case when nvl(d4.kds,0)=0 then 0 else round(nvl(d4.jshj,0)/nvl(d4.kds,0),2) end 去年医保会员客单价,
case when nvl(d4.hys,0)=0 then 0 else round(nvl(d4.jshj,0)/nvl(d4.hys,0),2) end 去年医保会员人均消费,case when nvl(d4.hys,0)=0 then 0 else round(nvl(d4.kds,0)/nvl(d4.hys,0),2) end 去年医保会员消费频次
from (select * from gl_custom where tjbh='03711033')e
left join (select a.subbh,sum(a.jshj) jshj,sum(a.zhml) zhml,sum(a.kds) kds,sum(a.dsxse) dsxse,sum(a.dskds) dskds,sum(a.cyxse) cyxse,sum(a.nfxs) nf,sum(nvl(a.gfxs,0)+nvl(a.spgf,0)+nvl(a.gfxsa,0)) gf,sum(a.dtp) dtp,sum(a.dtpzhml) dtpzhml
from c_zkmdxshzb a where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') AND TRUNC(SYSDATE-1) and a.subbh ='03711033' group by a.subbh)a1 on a1.subbh= e.tjbh----整体
left join (select a.subbh,sum(a.jshj) jshj,sum(a.zhml) zhml,sum(a.kds) kds,sum(a.dsxse) dsxse,sum(a.dskds) dskds,sum(a.cyxse) cyxse,sum(a.nfxs) nf,sum(nvl(a.gfxs,0)+nvl(a.spgf,0)+nvl(a.gfxsa,0)) gf,sum(a.dtp) dtp,sum(a.dtpzhml) dtpzhml
from c_zkmdxshzb a where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh ='03711033' group by a.subbh)a2 on a2.subbh= e.tjbh----整体
left join (select a.subbh,sum(a.jshj) jshj,sum(a.zhml) zhml,sum(a.kds) kds,sum(a.dsxse) dsxse,sum(a.dskds) dskds,sum(a.cyxse) cyxse,sum(a.nfxs) nf,sum(nvl(a.gfxs,0)+nvl(a.spgf,0)+nvl(a.gfxsa,0)) gf,sum(a.dtp) dtp,sum(a.dtpzhml) dtpzhml
from c_zkmdxshzb a where a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and a.subbh ='03711033' group by a.subbh)a3 on a3.subbh= e.tjbh----整体
left join (select a.subbh,sum(a.jshj) jshj,sum(a.zhml) zhml,sum(a.kds) kds,sum(a.dsxse) dsxse,sum(a.dskds) dskds,sum(a.cyxse) cyxse,sum(a.nfxs) nf,sum(nvl(a.gfxs,0)+nvl(a.spgf,0)+nvl(a.gfxsa,0)) gf,sum(a.dtp) dtp,sum(a.dtpzhml) dtpzhml
from c_zkmdxshzb a where a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and a.subbh ='03711033' group by a.subbh)a4 on a4.subbh= e.tjbh----整体
left join (select a.subbh,sum(a.jshj) jshj,count(distinct a.lsh) kds,sum(a.je-a.sl*nvl(b.dyfjj,0)) ml from subfhd a
left join (select subbh,hh,ph,dh,jhdj,c_dyfjj,(case when c_dyfjj is not null then c_dyfjj else jhdj end) dyfjj from subphk) b on a.subbh=b.subbh and a.hedgehh=b.hh and a.ph1=b.ph and a.pcdh=b.dh
left join yw_kck d on a.hh=d.hh left join (select * from spjxlbz where lbz='1' ) e on e.hh=d.hh
where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') AND TRUNC(SYSDATE-1) and a.subbh ='03711033' and nvl(e.sx,' ') <> 'D' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh)b1 on b1.subbh=e.tjbh ---非dtp
left join (select a.subbh,sum(a.jshj) jshj,count(distinct a.lsh) kds,sum(a.je-a.sl*nvl(b.dyfjj,0)) ml from subfhd a
left join (select subbh,hh,ph,dh,jhdj,c_dyfjj,(case when c_dyfjj is not null then c_dyfjj else jhdj end) dyfjj from subphk) b on a.subbh=b.subbh and a.hedgehh=b.hh and a.ph1=b.ph and a.pcdh=b.dh
left join yw_kck d on a.hh=d.hh left join (select * from spjxlbz where lbz='1' ) e on e.hh=d.hh
where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh ='03711033' and nvl(e.sx,' ') <> 'D' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh)b2 on b2.subbh=e.tjbh ---非dtp
left join (select a.subbh,sum(a.jshj) jshj,count(distinct a.lsh) kds,sum(a.je-a.sl*nvl(b.dyfjj,0)) ml from subfhd a
left join (select subbh,hh,ph,dh,jhdj,c_dyfjj,(case when c_dyfjj is not null then c_dyfjj else jhdj end) dyfjj from subphk) b on a.subbh=b.subbh and a.hedgehh=b.hh and a.ph1=b.ph and a.pcdh=b.dh
left join yw_kck d on a.hh=d.hh left join (select * from spjxlbz where lbz='1' ) e on e.hh=d.hh
where a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and a.subbh ='03711033' and nvl(e.sx,' ') <> 'D' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh)b3 on b3.subbh=e.tjbh ---非dtp
left join (select a.subbh,sum(a.jshj) jshj,count(distinct a.lsh) kds,sum(a.je-a.sl*nvl(b.dyfjj,0)) ml from subfhd a
left join (select subbh,hh,ph,dh,jhdj,c_dyfjj,(case when c_dyfjj is not null then c_dyfjj else jhdj end) dyfjj from subphk) b on a.subbh=b.subbh and a.hedgehh=b.hh and a.ph1=b.ph and a.pcdh=b.dh
left join yw_kck d on a.hh=d.hh left join (select * from spjxlbz where lbz='1' ) e on e.hh=d.hh
where a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and a.subbh ='03711033' and nvl(e.sx,' ') <> 'D' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh)b4 on b4.subbh=e.tjbh ---非dtp
left join (select a.subbh,sum(a.jshj) jshj,count(distinct a.yhkh) hys,count(distinct a.lsh) kds from subfhd a
where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') AND TRUNC(SYSDATE-1) and a.subbh ='03711033' and nvl(a.yhkh,' ')<>' 'and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh)c1 on c1.subbh=e.tjbh ---会员
left join (select a.subbh,sum(a.jshj) jshj,count(distinct a.yhkh) hys,count(distinct a.lsh) kds from subfhd a
where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh ='03711033' and nvl(a.yhkh,' ')<>' 'and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh)c2 on c2.subbh=e.tjbh ---会员
left join (select a.subbh,sum(a.jshj) jshj,count(distinct a.yhkh) hys,count(distinct a.lsh) kds from subfhd a
where a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and a.subbh ='03711033' and nvl(a.yhkh,' ')<>' 'and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh)c3 on c3.subbh=e.tjbh ---会员
left join (select a.subbh,sum(a.jshj) jshj,count(distinct a.yhkh) hys,count(distinct a.lsh) kds from subfhd a
where a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and a.subbh ='03711033' and nvl(a.yhkh,' ')<>' 'and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh)c4 on c4.subbh=e.tjbh ---会员
left join (select a.subbh,count(distinct a.yhkh) hys,count(distinct a.lsh) kds,sum(a.jshj) jshj from subfhd a
inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, 'IW') AND TRUNC(SYSDATE-1) and fkfsname like '%医保%')b on b.lsh=a.lsh
where a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh)d1 on d1.subbh=e.tjbh ---医保会员
left join (select a.subbh,count(distinct a.yhkh) hys,count(distinct a.lsh) kds,sum(a.jshj) jshj from subfhd a
inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and fkfsname like '%医保%')b on b.lsh=a.lsh
where a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh)d2 on d2.subbh=e.tjbh ---医保会员
left join (select a.subbh,count(distinct a.yhkh) hys,count(distinct a.lsh) kds,sum(a.jshj) jshj from subfhd a
inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and fkfsname like '%医保%')b on b.lsh=a.lsh
where a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh)d3 on d3.subbh=e.tjbh ---医保会员
left join (select a.subbh,count(distinct a.yhkh) hys,count(distinct a.lsh) kds,sum(a.jshj) jshj from subfhd a
inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and fkfsname like '%医保%')b on b.lsh=a.lsh
where a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh)d4 on d4.subbh=e.tjbh ---医保会员
""",
# 医保付款数据
"""
-----二、医保付款数据
select e.c_mdfq 大区,e.c_dq 地市,e.c_mdfq1 地区,e.tjbh 编码,e.mc 门店,
fk.fkfsname 付款方式,nvl(a1.je,0) 本周付款金额,nvl(a2.je,0) 上周付款金额,nvl(a3.je,0) 今年付款金额,nvl(a4.je,0) 去年付款金额
from (select * from gl_custom where tjbh='03711033')e
cross join (select distinct a.fkfsname from subdfkfs a where a.fkfsname like '%医保%'and a.subbh ='03711033' and ((a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 ) or (a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1))) ) fk
left join (select a.subbh,a.fkfsname ,sum(a.fkje) je from subdfkfs a
where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') AND TRUNC(SYSDATE-1) and a.fkfsname like '%医保%' and a.subbh ='03711033' group by a.subbh,a.fkfsname)a1 on a1.subbh= e.tjbh and a1.fkfsname = fk.fkfsname
left join (select a.subbh,a.fkfsname ,sum(a.fkje) je from subdfkfs a
where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.fkfsname like '%医保%' and a.subbh ='03711033' group by a.subbh,a.fkfsname)a2 on a2.subbh= e.tjbh and a2.fkfsname = fk.fkfsname
left join (select a.subbh,a.fkfsname ,sum(a.fkje) je from subdfkfs a
where a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and a.fkfsname like '%医保%' and a.subbh ='03711033' group by a.subbh,a.fkfsname)a3 on a3.subbh= e.tjbh and a3.fkfsname = fk.fkfsname
left join (select a.subbh,a.fkfsname ,sum(a.fkje) je from subdfkfs a
where a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and a.fkfsname like '%医保%' and a.subbh ='03711033' group by a.subbh,a.fkfsname)a4 on a4.subbh= e.tjbh and a4.fkfsname = fk.fkfsname
""",
# 品类数据
"""
-------品类数据
select a.c_mdfq 大区,a.c_dq 地市,a.c_mdfq1 地区,a.tjbh 编码,a.mc 门店,a.name 类别,
nvl(b.jshj,0) 本周销售,nvl(b.jshj,0)-nvl(c.jshj,0) 周销售环比,nvl(b.kds,0) 本周客流,nvl(b.kds,0)-nvl(c.kds,0) 周客流环比,
case when nvl(b.jshj,0)<>0 and nvl(c.jshj,0)<>0 then round(nvl(b.ml,0)*100/nvl(b.jshj,0),2)- round(nvl(c.ml,0)*100/nvl(c.jshj,0),2) else 0 end || '%' 周毛利率环比,
nvl(d1.jshj,0)-nvl(d2.jshj,0) 医保会员销售周销售环比,nvl(d3.jshj,0)-nvl(d4.jshj,0) 非医保会员周销售环比,nvl(d5.jshj,0)-nvl(d6.jshj,0) 非会员周销售环比,
nvl(b1.jshj,0) 今年销售,nvl(b1.jshj,0)-nvl(c1.jshj,0) 年销售同比,nvl(b1.kds,0) 今年客流,nvl(b1.kds,0)-nvl(c1.kds,0) 年客流同比,
case when nvl(b1.jshj,0)<>0 and nvl(c1.jshj,0)<>0 then round(nvl(b1.ml,0)*100/nvl(b1.jshj,0),2)- round(nvl(c1.ml,0)*100/nvl(c1.jshj,0),2) else 0 end || '%' 年毛利率同比,
nvl(e1.jshj,0)-nvl(e2.jshj,0) 医保会员销售年销售同比,nvl(e3.jshj,0)-nvl(e4.jshj,0) 非医保会员年销售同比,nvl(e5.jshj,0)-nvl(e6.jshj,0) 非会员年销售同比
from (select a.c_mdfq,a.c_dq,a.c_mdfq1,a.tjbh,a.mc,b.name from gl_custom a cross join (select distinct name from dataitem where dataitemtypeguid='313') b where a.tjbh='03711033') a
left join (select a.subbh,nvl(c.name,'未分类') name,sum(a.jshj) jshj,count(distinct a.lsh) kds,sum(a.je-a.sl*nvl(d.dyfjj,0)) ml
from subfhd a left join (select subbh,hh,ph,dh,jhdj,c_dyfjj,(case when c_dyfjj is not null then c_dyfjj else jhdj end) dyfjj from subphk) d on a.subbh=d.subbh and a.hedgehh=d.hh and a.ph1=d.ph and a.pcdh=d.dh
left join yw_kck b on a.hedgehh=b.hh left join (select * from dataitem where dataitemtypeguid='313') c on b.fzflsx2=c.code
where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') AND TRUNC(SYSDATE-1) and a.subbh ='03711033' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(c.name,'未分类')) b on a.tjbh=b.subbh and a.name=b.name
left join (select a.subbh,nvl(c.name,'未分类') name,sum(a.jshj) jshj,count(distinct a.lsh) kds,sum(a.je-a.sl*nvl(d.dyfjj,0)) ml
from subfhd a left join (select subbh,hh,ph,dh,jhdj,c_dyfjj,(case when c_dyfjj is not null then c_dyfjj else jhdj end) dyfjj from subphk) d on a.subbh=d.subbh and a.hedgehh=d.hh and a.ph1=d.ph and a.pcdh=d.dh
left join yw_kck b on a.hedgehh=b.hh left join (select * from dataitem where dataitemtypeguid='313') c on b.fzflsx2=c.code
where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh ='03711033' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(c.name,'未分类')) c on a.tjbh=c.subbh and a.name=c.name
left join (select a.subbh,nvl(c.name,'未分类') name,sum(a.jshj) jshj,count(distinct a.lsh) kds,sum(a.je-a.sl*nvl(d.dyfjj,0)) ml
from subfhd a left join (select subbh,hh,ph,dh,jhdj,c_dyfjj,(case when c_dyfjj is not null then c_dyfjj else jhdj end) dyfjj from subphk) d on a.subbh=d.subbh and a.hedgehh=d.hh and a.ph1=d.ph and a.pcdh=d.dh
left join yw_kck b on a.hedgehh=b.hh left join (select * from dataitem where dataitemtypeguid='313') c on b.fzflsx2=c.code
where a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and a.subbh ='03711033' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(c.name,'未分类')) b1 on a.tjbh=b1.subbh and a.name=b1.name
left join (select a.subbh,nvl(c.name,'未分类') name,sum(a.jshj) jshj,count(distinct a.lsh) kds,sum(a.je-a.sl*nvl(d.dyfjj,0)) ml
from subfhd a left join (select subbh,hh,ph,dh,jhdj,c_dyfjj,(case when c_dyfjj is not null then c_dyfjj else jhdj end) dyfjj from subphk) d on a.subbh=d.subbh and a.hedgehh=d.hh and a.ph1=d.ph and a.pcdh=d.dh
left join yw_kck b on a.hedgehh=b.hh left join (select * from dataitem where dataitemtypeguid='313') c on b.fzflsx2=c.code
where a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and a.subbh ='03711033' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(c.name,'未分类')) c1 on a.tjbh=c1.subbh and a.name=c1.name
left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj
from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, 'IW') AND TRUNC(SYSDATE-1) and fkfsname like '%医保%')d on d.lsh=a.lsh
left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code
where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') AND TRUNC(SYSDATE-1) and a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(e.name,'未分类'))d1 on a.tjbh=d1.subbh and a.name=d1.name
left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj
from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and fkfsname like '%医保%')d on d.lsh=a.lsh
left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code
where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(e.name,'未分类'))d2 on a.tjbh=d2.subbh and a.name=d2.name
left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj
from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, 'IW') AND TRUNC(SYSDATE-1) and fkfsname not like '%医保%')d on d.lsh=a.lsh
left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code
where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') AND TRUNC(SYSDATE-1) and a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(e.name,'未分类'))d3 on a.tjbh=d3.subbh and a.name=d3.name
left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj
from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and fkfsname not like '%医保%')d on d.lsh=a.lsh
left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code
where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(e.name,'未分类'))d4 on a.tjbh=d4.subbh and a.name=d4.name
left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj
from subfhd a left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code
where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') AND TRUNC(SYSDATE-1) and a.subbh ='03711033' and nvl(a.yhkh,' ')=' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(e.name,'未分类'))d5 on a.tjbh=d5.subbh and a.name=d5.name
left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj
from subfhd a left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code
where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh ='03711033' and nvl(a.yhkh,' ')=' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(e.name,'未分类'))d6 on a.tjbh=d6.subbh and a.name=d6.name
left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj
from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and fkfsname like '%医保%')d on d.lsh=a.lsh
left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code
where a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(e.name,'未分类'))e1 on a.tjbh=e1.subbh and a.name=e1.name
left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj
from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and fkfsname like '%医保%')d on d.lsh=a.lsh
left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code
where a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(e.name,'未分类'))e2 on a.tjbh=e2.subbh and a.name=e2.name
left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj
from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and fkfsname not like '%医保%')d on d.lsh=a.lsh
left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code
where a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(e.name,'未分类'))e3 on a.tjbh=e3.subbh and a.name=e3.name
left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj
from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and fkfsname not like '%医保%')d on d.lsh=a.lsh
left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code
where a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(e.name,'未分类'))e4 on a.tjbh=e4.subbh and a.name=e4.name
left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj
from subfhd a left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code
where a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and a.subbh ='03711033' and nvl(a.yhkh,' ')=' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(e.name,'未分类'))e5 on a.tjbh=e5.subbh and a.name=e5.name
left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj
from subfhd a left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code
where a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and a.subbh ='03711033' and nvl(a.yhkh,' ')=' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(e.name,'未分类'))e6 on a.tjbh=e6.subbh and a.name=e6.name
order by 周销售环比
""",
# 销售能力
"""
------销售能力
with subfhdls as (select a.subbh,a.lsh,a.hedgehh,a.jshj,a.xsr,a.sl from subfhd a left join yw_kck b on b.hh=a.hedgehh
left join gl_custom d on a.subbh=d.tjbh
where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') AND TRUNC(SYSDATE-1) and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换' and nvl(a.posguid,' ')<>'10000'
and b.fzflsx1<>'14'and b.fzflsx2<>'08031' and a.subbh ='03711033'
and b.fzflsx2<>'08032' and b.fzflsx2<>'08034' and b.fzflsx2<>'08035' and b.fzflsx2<>'08038' and b.fzflsx2<>'08039' and b.fzflsx2<>'08040' and b.fzflsx2<>'08041' ),
a1 as (select a.subbh,nvl(a.jshj,0) jshj,nvl(a.pzs,0) zkds,nvl(b.lsh,0) dpkds,case when nvl(a.pzs,0)<>0 then round(b.lsh/a.pzs,4) else 0 end dpl,c.kps kps,d.kp kpc
from (select a.subbh,count(distinct a.lsh) pzs,sum(a.jshj) jshj from subfhdls a group by a.subbh) a
left join (select subbh,count(distinct lsh) lsh from (select a.subbh,a.lsh,count(a.hedgehh) pzs from
subfhdls a group by a.subbh,a.lsh having count(a.hedgehh)=1) group by subbh) b on b.subbh=a.subbh
left join (select subbh,round(sum(sl)/count(distinct lsh),2) kps from subfhdls group by subbh) c on c.subbh=a.subbh
left join (select subbh,round(sum(kpc)/count(distinct lsh),2) kp from (select a.subbh,a.lsh,count(distinct a.hedgehh) kpc from subfhdls a group by a.subbh,a.lsh)
group by subbh) d on d.subbh=a.subbh),
subfhdls1 as (select a.subbh,a.lsh,a.hedgehh,a.jshj,a.xsr,a.sl from subfhd a left join yw_kck b on b.hh=a.hedgehh
left join gl_custom d on a.subbh=d.tjbh
where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换' and nvl(a.posguid,' ')<>'10000'
and b.fzflsx1<>'14'and b.fzflsx2<>'08031' and a.subbh ='03711033'
and b.fzflsx2<>'08032' and b.fzflsx2<>'08034' and b.fzflsx2<>'08035' and b.fzflsx2<>'08038' and b.fzflsx2<>'08039' and b.fzflsx2<>'08040' and b.fzflsx2<>'08041' ),
a2 as (select a.subbh,nvl(a.jshj,0) jshj,nvl(a.pzs,0) zkds,nvl(b.lsh,0) dpkds,case when nvl(a.pzs,0)<>0 then round(b.lsh/a.pzs,4) else 0 end dpl,c.kps kps,d.kp kpc
from (select a.subbh,count(distinct a.lsh) pzs,sum(a.jshj) jshj from subfhdls1 a group by a.subbh) a
left join (select subbh,count(distinct lsh) lsh from (select a.subbh,a.lsh,count(a.hedgehh) pzs from
subfhdls1 a group by a.subbh,a.lsh having count(a.hedgehh)=1) group by subbh) b on b.subbh=a.subbh
left join (select subbh,round(sum(sl)/count(distinct lsh),2) kps from subfhdls1 group by subbh) c on c.subbh=a.subbh
left join (select subbh,round(sum(kpc)/count(distinct lsh),2) kp from (select a.subbh,a.lsh,count(distinct a.hedgehh) kpc from subfhdls1 a group by a.subbh,a.lsh)
group by subbh) d on d.subbh=a.subbh),
subfhdls2 as (select a.subbh,a.lsh,a.hedgehh,a.jshj,a.xsr,a.sl from subfhd a left join yw_kck b on b.hh=a.hedgehh
left join gl_custom d on a.subbh=d.tjbh
where a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换' and nvl(a.posguid,' ')<>'10000'
and b.fzflsx1<>'14'and b.fzflsx2<>'08031' and a.subbh ='03711033'
and b.fzflsx2<>'08032' and b.fzflsx2<>'08034' and b.fzflsx2<>'08035' and b.fzflsx2<>'08038' and b.fzflsx2<>'08039' and b.fzflsx2<>'08040' and b.fzflsx2<>'08041' ),
a3 as (select a.subbh,nvl(a.jshj,0) jshj,nvl(a.pzs,0) zkds,nvl(b.lsh,0) dpkds,case when nvl(a.pzs,0)<>0 then round(b.lsh/a.pzs,4) else 0 end dpl,c.kps kps,d.kp kpc
from (select a.subbh,count(distinct a.lsh) pzs,sum(a.jshj) jshj from subfhdls1 a group by a.subbh) a
left join (select subbh,count(distinct lsh) lsh from (select a.subbh,a.lsh,count(a.hedgehh) pzs from
subfhdls2 a group by a.subbh,a.lsh having count(a.hedgehh)=1) group by subbh) b on b.subbh=a.subbh
left join (select subbh,round(sum(sl)/count(distinct lsh),2) kps from subfhdls1 group by subbh) c on c.subbh=a.subbh
left join (select subbh,round(sum(kpc)/count(distinct lsh),2) kp from (select a.subbh,a.lsh,count(distinct a.hedgehh) kpc from subfhdls1 a group by a.subbh,a.lsh)
group by subbh) d on d.subbh=a.subbh),
subfhdls3 as (select a.subbh,a.lsh,a.hedgehh,a.jshj,a.xsr,a.sl from subfhd a left join yw_kck b on b.hh=a.hedgehh
left join gl_custom d on a.subbh=d.tjbh
where a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换' and nvl(a.posguid,' ')<>'10000'
and b.fzflsx1<>'14'and b.fzflsx2<>'08031' and a.subbh ='03711033'
and b.fzflsx2<>'08032' and b.fzflsx2<>'08034' and b.fzflsx2<>'08035' and b.fzflsx2<>'08038' and b.fzflsx2<>'08039' and b.fzflsx2<>'08040' and b.fzflsx2<>'08041' ),
a4 as (select a.subbh,nvl(a.jshj,0) jshj,nvl(a.pzs,0) zkds,nvl(b.lsh,0) dpkds,case when nvl(a.pzs,0)<>0 then round(b.lsh/a.pzs,4) else 0 end dpl,c.kps kps,d.kp kpc
from (select a.subbh,count(distinct a.lsh) pzs,sum(a.jshj) jshj from subfhdls1 a group by a.subbh) a
left join (select subbh,count(distinct lsh) lsh from (select a.subbh,a.lsh,count(a.hedgehh) pzs from
subfhdls3 a group by a.subbh,a.lsh having count(a.hedgehh)=1) group by subbh) b on b.subbh=a.subbh
left join (select subbh,round(sum(sl)/count(distinct lsh),2) kps from subfhdls1 group by subbh) c on c.subbh=a.subbh
left join (select subbh,round(sum(kpc)/count(distinct lsh),2) kp from (select a.subbh,a.lsh,count(distinct a.hedgehh) kpc from subfhdls1 a group by a.subbh,a.lsh)
group by subbh) d on d.subbh=a.subbh)
select a.subbh 编码,e.mc 门店,a.dpl "本周一单一品率",a.kps "本周客品数",a.kpc "本周客品次",b.dpl "上周一单一品率",b.kps "上周客品数",b.kpc "上周客品次",
c.dpl "今年一单一品率",c.kps "今年客品数",c.kpc "今年客品次",d.dpl "去年一单一品率",d.kps "去年客品数",d.kpc "去年客品次"
from a1 a left join a2 b on a.subbh=b.subbh left join a3 c on a.subbh=c.subbh left join a4 d on a.subbh=d.subbh
left join gl_custom e on a.subbh=e.tjbh
"""
]
results = []
for query in QUERIES:
formatted_query = query.replace("'03711033'", f"'{store_id}'")
with engine.connect() as conn:
df = pd.read_sql(formatted_query, conn)
results.append(df)
return results
def create_pdf_report(store_id, store_name, data, filename):
"""创建PDF报告"""
doc = SimpleDocTemplate(
filename,
pagesize=landscape(letter),
rightMargin=30,
leftMargin=30,
topMargin=30,
bottomMargin=30
)
styles = getSampleStyleSheet()
elements = []
# 标题
title_style = ParagraphStyle(
'Title',
parent=styles['Heading1'],
alignment=1,
spaceAfter=12
)
elements.append(Paragraph(f"{store_name}销售分析报告", title_style))
# 表格1: 整体周销售数据
df1 = data[0]
weekly_columns = ['指标', '本周', '本周占比', '上周', '上周占比', '环比']
weekly_data = [
['总销售', df1['本周总销售'][0], '', df1['上周总销售'][0], '', df1['本周总销售'][0] - df1['上周总销售'][0]],
['综合毛利率', df1['本周综合毛利率'][0], '', df1['上周综合毛利率'][0], '', ''],
['总客单', df1['本周总客单'][0], '', df1['上周总客单'][0], '', df1['本周总客单'][0] - df1['上周总客单'][0]],
['客单价', df1['本周客单价'][0], '', df1['上周客单价'][0], '', ''],
['线上销售', df1['本周线上销售'][0], '', df1['上周线上销售'][0], '',
df1['本周线上销售'][0] - df1['上周线上销售'][0]],
['线上客单', df1['本周线上客单'][0], '', df1['上周线上客单'][0], '', ''],
['线下销售', df1['本周线下销售'][0], '', df1['上周线下销售'][0], '',
df1['本周线下销售'][0] - df1['上周线下销售'][0]],
['线下客单', df1['本周线下客单'][0], '', df1['上周线下客单'][0], '', ''],
['DTP销售', df1['本周DTP销售'][0], df1['本周DTP销售占比'][0], df1['上周DTP销售'][0], df1['上周DTP销售占比'][0],
df1['本周DTP销售'][0] - df1['上周DTP销售'][0]],
['排除DTP销售', df1['本周排除DTP销售'][0], '', df1['上周排除DTP销售'][0], '',
df1['本周排除DTP销售'][0] - df1['上周排除DTP销售'][0]],
['排除DTP综合毛利率', df1['本周排除DTP综合毛利率'][0], '', df1['上周排除DTP综合毛利率'][0], '', ''],
['排除DTP客单', df1['本周排除DTP客单'][0], '', df1['上周排除DTP客单'][0], '',
df1['本周排除DTP客单'][0] - df1['上周排除DTP客单'][0]],
['排除DTP客单价', df1['本周排除DTP客单价'][0], '', df1['上周排除DTP客单价'][0], '', ''],
['中药销售', df1['本周中药销售'][0], df1['本周中药销售占比'][0], df1['上周中药销售'][0],
df1['上周中药销售占比'][0], df1['本周中药销售'][0] - df1['上周中药销售'][0]],
['内方销售', df1['本周内方销售'][0], df1['本周内方销售占比'][0], df1['上周内方销售'][0],
df1['上周内方销售占比'][0], df1['本周内方销售'][0] - df1['上周内方销售'][0]],
['膏方销售', df1['本周膏方销售'][0], df1['本周膏方销售占比'][0], df1['上周膏方销售'][0],
df1['上周膏方销售占比'][0], df1['本周膏方销售'][0] - df1['上周膏方销售'][0]],
['会员销售', df1['本周会员销售'][0], df1['本周会员销售占比'][0], df1['上周会员销售'][0],
df1['上周会员销售占比'][0], df1['本周会员销售'][0] - df1['上周会员销售'][0]],
['会员客单', df1['本周会员客单'][0], '', df1['上周会员客单'][0], '',
df1['本周会员客单'][0] - df1['上周会员客单'][0]],
['会员数', df1['本周会员数'][0], '', df1['上周会员数'][0], '', df1['本周会员数'][0] - df1['上周会员数'][0]],
['会员客单价', df1['本周会员客单价'][0], '', df1['上周会员客单价'][0], '', ''],
['会员人均消费', df1['本周会员人均消费'][0], '', df1['上周会员人均消费'][0], '', ''],
['会员消费频次', df1['本周会员消费频次'][0], '', df1['上周会员消费频次'][0], '', ''],
['医保会员销售', df1['本周医保会员销售'][0], df1['本周医保会员销售占比'][0], df1['上周医保会员销售'][0],
df1['上周医保会员销售占比'][0], df1['本周医保会员销售'][0] - df1['上周医保会员销售'][0]],
['医保会员客单', df1['本周医保会员客单'][0], '', df1['上周医保会员客单'][0], '',
df1['本周医保会员客单'][0] - df1['上周医保会员客单'][0]],
['医保会员数', df1['本周医保会员数'][0], '', df1['上周医保会员数'][0], '',
df1['本周医保会员数'][0] - df1['上周医保会员数'][0]],
['医保会员客单价', df1['本周医保会员客单价'][0], '', df1['上周医保会员客单价'][0], '', ''],
['医保会员人均消费', df1['本周医保会员人均消费'][0], '', df1['上周医保会员人均消费'][0], '', ''],
['医保会员消费频次', df1['本周医保会员消费频次'][0], '', df1['上周医保会员消费频次'][0], '', ''],
]
# 创建表格1
weekly_table = Table([weekly_columns] + weekly_data)
weekly_table.setStyle(TableStyle([
('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#4F81BD')),
('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
('ALIGN', (0, 0), (-1, -1), 'CENTER'),
('FONTSIZE', (0, 0), (-1, 0), 10),
('BOTTOMPADDING', (0, 0), (-1, 0), 12),
('BACKGROUND', (0, 1), (-1, -1), colors.HexColor('#DCE6F1')),
('GRID', (0, 0), (-1, -1), 1, colors.black),
('VALIGN', (0, 0), (-1, -1), 'MIDDLE')
]))
elements.append(Paragraph("<b>表1: 整体周销售数据</b>", styles['Heading2']))
elements.append(weekly_table)
elements.append(Spacer(1, 0.2 * inch))
# 表格2: 医保付款周数据
df2 = data[1]
payment_columns = ['付款方式', '本周付款金额', '上周付款金额', '环比']
payment_data = []
for _, row in df2.iterrows():
payment_data.append([
row['付款方式'],
row['本周付款金额'],
row['上周付款金额'],
row['本周付款金额'] - row['上周付款金额']
])
payment_table = Table([payment_columns] + payment_data)
payment_table.setStyle(TableStyle([
('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#C0504D')),
('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
('ALIGN', (0, 0), (-1, -1), 'CENTER'),
('FONTSIZE', (0, 0), (-1, 0), 10),
('BOTTOMPADDING', (0, 0), (-1, 0), 12),
('BACKGROUND', (0, 1), (-1, -1), colors.HexColor('#F2DCDB')),
('GRID', (0, 0), (-1, -1), 1, colors.black)
]))
elements.append(Paragraph("<b>表2: 医保付款周数据</b>", styles['Heading2']))
elements.append(payment_table)
elements.append(Spacer(1, 0.2 * inch))
# 表格3: 品类周数据
df3 = data[2]
category_columns = ['类别', '本周销售', '本周占比', '周销售环比', '本周客流', '周客流环比',
'医保会员销售周销售环比', '非医保会员周销售环比', '非会员周销售环比']
category_data = []
for _, row in df3.iterrows():
total_sales = df1['本周总销售'][0]
category_data.append([
row['类别'],
row['本周销售'],
f"{row['本周销售'] / total_sales:.2%}" if total_sales > 0 else "0.00%",
row['周销售环比'],
row['本周客流'],
row['周客流环比'],
row['医保会员销售周销售环比'],
row['非医保会员周销售环比'],
row['非会员周销售环比']
])
category_table = Table([category_columns] + category_data)
category_table.setStyle(TableStyle([
('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#9BBB59')),
('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
('ALIGN', (0, 0), (-1, -1), 'CENTER'),
('FONTSIZE', (0, 0), (-1, 0), 9),
('BOTTOMPADDING', (0, 0), (-1, 0), 10),
('BACKGROUND', (0, 1), (-1, -1), colors.HexColor('#EBF1DE')),
('GRID', (0, 0), (-1, -1), 1, colors.black)
]))
elements.append(Paragraph("<b>表3: 品类周数据</b>", styles['Heading2']))
elements.append(category_table)
elements.append(Spacer(1, 0.2 * inch))
# 表格4: 销售能力环比
df4 = data[3]
capability_columns = ['时间', '一单一品率', '客品数', '客品次']
capability_data = [
['本周', df4['本周一单一品率'][0], df4['本周客品数'][0], df4['本周客品次'][0]],
['上周', df4['上周一单一品率'][0], df4['上周客品数'][0], df4['上周客品次'][0]],
['环比',
df4['本周一单一品率'][0] - df4['上周一单一品率'][0],
df4['本周客品数'][0] - df4['上周客品数'][0],
df4['本周客品次'][0] - df4['上周客品次'][0]]
]
capability_table = Table([capability_columns] + capability_data)
capability_table.setStyle(TableStyle([
('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#8064A2')),
('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
('ALIGN', (0, 0), (-1, -1), 'CENTER'),
('FONTSIZE', (0, 0), (-1, 0), 10),
('BOTTOMPADDING', (0, 0), (-1, 0), 12),
('BACKGROUND', (0, 1), (-1, -1), colors.HexColor('#E5E0EC')),
('GRID', (0, 0), (-1, -1), 1, colors.black)
]))
elements.append(Paragraph("<b>表4: 销售能力环比</b>", styles['Heading2']))
elements.append(capability_table)
elements.append(PageBreak())
# 表格5: 整体销售数据(年度)
annual_columns = ['指标', '今年', '今年占比', '去年', '去年占比', '同比']
annual_data = [
['总销售', df1['今年总销售'][0], '', df1['去年总销售'][0], '', df1['今年总销售'][0] - df1['去年总销售'][0]],
['综合毛利率', df1['今年综合毛利率'][0], '', df1['去年综合毛利率'][0], '', ''],
['总客单', df1['今年总客单'][0], '', df1['去年总客单'][0], '', df1['今年总客单'][0] - df1['去年总客单'][0]],
# 其他行...(类似表格1的结构)
]
annual_table = Table([annual_columns] + annual_data)
annual_table.setStyle(TableStyle([
('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#4F81BD')),
('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
('ALIGN', (0, 0), (-1, -1), 'CENTER'),
('FONTSIZE', (0, 0), (-1, 0), 10),
('BOTTOMPADDING', (0, 0), (-1, 0), 12),
('BACKGROUND', (0, 1), (-1, -1), colors.HexColor('#DCE6F1')),
('GRID', (0, 0), (-1, -1), 1, colors.black)
]))
elements.append(Paragraph("<b>表5: 整体销售数据(年度)</b>", styles['Heading2']))
elements.append(annual_table)
elements.append(Spacer(1, 0.2 * inch))
# 表格6: 医保付款数据(年度)
payment_annual_columns = ['付款方式', '今年付款金额', '去年付款金额', '同比']
payment_annual_data = []
for _, row in df2.iterrows():
payment_annual_data.append([
row['付款方式'],
row['今年付款金额'],
row['去年付款金额'],
row['今年付款金额'] - row['去年付款金额']
])
payment_annual_table = Table([payment_annual_columns] + payment_annual_data)
payment_annual_table.setStyle(TableStyle([
('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#C0504D')),
('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
('ALIGN', (0, 0), (-1, -1), 'CENTER'),
('FONTSIZE', (0, 0), (-1, 0), 10),
('BOTTOMPADDING', (0, 0), (-1, 0), 12),
('BACKGROUND', (0, 1), (-1, -1), colors.HexColor('#F2DCDB')),
('GRID', (0, 0), (-1, -1), 1, colors.black)
]))
elements.append(Paragraph("<b>表6: 医保付款数据(年度)</b>", styles['Heading2']))
elements.append(payment_annual_table)
elements.append(Spacer(1, 0.2 * inch))
# 表格7: 品类数据(年度)
category_annual_columns = ['类别', '今年销售', '今年占比', '年销售同比', '今年客流', '年客流同比',
'医保会员销售年销售同比', '非医保会员年销售同比', '非会员年销售同比']
category_annual_data = []
for _, row in df3.iterrows():
total_annual_sales = df1['今年总销售'][0]
category_annual_data.append([
row['类别'],
row['今年销售'],
f"{row['今年销售'] / total_annual_sales:.2%}" if total_annual_sales > 0 else "0.00%",
row['年销售同比'],
row['今年客流'],
row['年客流同比'],
row['医保会员销售年销售同比'],
row['非医保会员年销售同比'],
row['非会员年销售同比']
])
category_annual_table = Table([category_annual_columns] + category_annual_data)
category_annual_table.setStyle(TableStyle([
('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#9BBB59')),
('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
('ALIGN', (0, 0), (-1, -1), 'CENTER'),
('FONTSIZE', (0, 0), (-1, 0), 9),
('BOTTOMPADDING', (0, 0), (-1, 0), 10),
('BACKGROUND', (0, 1), (-1, -1), colors.HexColor('#EBF1DE')),
('GRID', (0, 0), (-1, -1), 1, colors.black)
]))
elements.append(Paragraph("<b>表7: 品类数据(年度)</b>", styles['Heading2']))
elements.append(category_annual_table)
elements.append(Spacer(1, 0.2 * inch))
# 表格8: 销售能力同比
capability_annual_columns = ['时间', '一单一品率', '客品数', '客品次']
capability_annual_data = [
['今年', df4['今年一单一品率'][0], df4['今年客品数'][0], df4['今年客品次'][0]],
['去年', df4['去年一单一品率'][0], df4['去年客品数'][0], df4['去年客品次'][0]],
['同比',
df4['今年一单一品率'][0] - df4['去年一单一品率'][0],
df4['今年客品数'][0] - df4['去年客品数'][0],
df4['今年客品次'][0] - df4['去年客品次'][0]]
]
capability_annual_table = Table([capability_annual_columns] + capability_annual_data)
capability_annual_table.setStyle(TableStyle([
('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#8064A2')),
('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
('ALIGN', (0, 0), (-1, -1), 'CENTER'),
('FONTSIZE', (0, 0), (-1, 0), 10),
('BOTTOMPADDING', (0, 0), (-1, 0), 12),
('BACKGROUND', (0, 1), (-1, -1), colors.HexColor('#E5E0EC')),
('GRID', (0, 0), (-1, -1), 1, colors.black)
]))
elements.append(Paragraph("<b>表8: 销售能力同比</b>", styles['Heading2']))
elements.append(capability_annual_table)
# 分析页
elements.append(PageBreak())
analysis_title = Paragraph("销售数据分析报告", styles['Heading1'])
elements.append(analysis_title)
# 添加分析内容
analysis_content = [
"1. <b>总体销售趋势</b>: 本周总销售额为¥{:.2f},环比{:.2%}。今年累计销售额¥{:.2f},同比{:.2%}。".format(
df1['本周总销售'][0],
(df1['本周总销售'][0] - df1['上周总销售'][0]) / df1['上周总销售'][0] if df1['上周总销售'][0] > 0 else 0,
df1['今年总销售'][0],
(df1['今年总销售'][0] - df1['去年总销售'][0]) / df1['去年总销售'][0] if df1['去年总销售'][0] > 0 else 0
),
"2. <b>会员分析</b>: 会员销售占比{},较上周{}。医保会员贡献率{},人均消费¥{:.2f}。".format(
df1['本周会员销售占比'][0],
"上升" if float(df1['本周会员销售占比'][0].strip('%')) > float(
df1['上周会员销售占比'][0].strip('%')) else "下降",
df1['本周医保会员销售占比'][0],
df1['本周医保会员人均消费'][0]
),
"3. <b>渠道表现</b>: 线上销售占比{:.2%},线下销售占比{:.2%}。DTP渠道占比{},环比{}。".format(
df1['本周线上销售'][0] / df1['本周总销售'][0] if df1['本周总销售'][0] > 0 else 0,
(df1['本周总销售'][0] - df1['本周线上销售'][0]) / df1['本周总销售'][0] if df1['本周总销售'][0] > 0 else 0,
df1['本周DTP销售占比'][0],
"上升" if float(df1['本周DTP销售占比'][0].strip('%')) > float(
df1['上周DTP销售占比'][0].strip('%')) else "下降"
),
"4. <b>品类表现</b>: 销售最好的品类是{},贡献率{:.2%}。增长最快的品类是{},环比增长{:.2%}。".format(
df3.loc[df3['本周销售'].idxmax()]['类别'],
df3['本周销售'].max() / df1['本周总销售'][0] if df1['本周总销售'][0] > 0 else 0,
df3.loc[df3['周销售环比'].idxmax()]['类别'],
df3['周销售环比'].max() / df3['本周销售'].max() if df3['本周销售'].max() > 0 else 0
),
"5. <b>销售能力</b>: 客品数{}件/单,客品次{}次/单。一单一品率{:.2%},较上周{}。".format(
df4['本周客品数'][0],
df4['本周客品次'][0],
df4['本周一单一品率'][0],
"上升" if df4['本周一单一品率'][0] > df4['上周一单一品率'][0] else "下降"
)
]
for content in analysis_content:
elements.append(Paragraph(content, styles['Normal']))
elements.append(Spacer(1, 0.1 * inch))
# 生成图表
fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(12, 5))
# 销售渠道占比图
channels = ['线上', '线下', 'DTP', '其他']
sales = [
df1['本周线上销售'][0],
df1['本周线下销售'][0],
df1['本周DTP销售'][0],
df1['本周总销售'][0] - df1['本周线上销售'][0] - df1['本周DTP销售'][0] - df1['本周线下销售'][0]
]
ax1.pie(sales, labels=channels, autopct='%1.1f%%', startangle=90)
ax1.set_title('销售渠道占比')
# 品类销售TOP5
top_categories = df3.nlargest(5, '本周销售')
ax2.bar(top_categories['类别'], top_categories['本周销售'], color='skyblue')
ax2.set_title('品类销售TOP5')
ax2.tick_params(axis='x', rotation=45)
plt.tight_layout()
chart_buffer = BytesIO()
plt.savefig(chart_buffer, format='png', dpi=150)
plt.close()
# 将图表添加到PDF
from reportlab.platypus import Image
elements.append(Image(chart_buffer, width=10 * inch, height=5 * inch))
# 生成PDF
doc.build(elements)
def main():
engine = create_engine('oracle+cx_oracle://admin:xxb20140415@192.168.3.16:1521/?service_name=zzjdata')
with engine.connect() as conn:
store_df = pd.read_sql("SELECT tjbh, mc FROM gl_custom WHERE tjbh = '03711033'", conn)
for _, row in store_df.iterrows():
store_id = row['tjbh']
store_name = row['mc']
print(f"正在处理门店: {store_name} ({store_id})")
try:
data = fetch_store_data(store_id)
filename = f"{store_name}_销售分析报告.pdf"
create_pdf_report(store_id, store_name, data, filename)
print(f"已生成: {filename}")
except Exception as e:
print(f"处理门店 {store_name} 时出错: {str(e)}")
if __name__ == "__main__":
main()
E:\Anaconda3-2024.02.1\python.exe E:\Pycharm-2023.3.4\project\pythonProject\venv\门店周数据3.py
正在处理门店: 郑州康复店G (03711033)
处理门店 郑州康复店G 时出错: Wedge sizes 'x' must be non negative values
Process finished with exit code 0
最新发布