def generate_weekly_sales_table(self):
sql = f"""
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 上周医保会员消费频次
from (select * from gl_custom where tjbh='{self.store_id}')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- 1, 'IW') AND TRUNC(SYSDATE- 1) and a.subbh ='{self.store_id}' 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- 1, 'IW') - 7 AND TRUNC(SYSDATE- 1) - 7 and a.subbh ='{self.store_id}' group by a.subbh)a2 on a2.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- 1, 'IW') AND TRUNC(SYSDATE- 1) and a.subbh ='{self.store_id}' 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- 1, 'IW') - 7 AND TRUNC(SYSDATE- 1)- 7 and a.subbh ='{self.store_id}' 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.yhkh) hys,count(distinct a.lsh) kds from subfhd a
where a.kdrq BETWEEN TRUNC(SYSDATE- 1, 'IW') AND TRUNC(SYSDATE- 1) and a.subbh ='{self.store_id}' 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- 1, 'IW') - 7 AND TRUNC(SYSDATE- 1) - 7 and a.subbh ='{self.store_id}' 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,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- 1, 'IW') AND TRUNC(SYSDATE- 1) and fkfsname like '%医保%')b on b.lsh=a.lsh
where a.subbh ='{self.store_id}' 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- 1, 'IW') - 7 AND TRUNC(SYSDATE- 1)- 7 and fkfsname like '%医保%')b on b.lsh=a.lsh
where a.subbh ='{self.store_id}' 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 ---医保会员
"""
df = self.fetch_data(sql)
if df.empty:
return None
# Process data and create table
table_data = []
indicators = [
('总销售', '本周总销售', '上周总销售', False),
('综合毛利率', '本周综合毛利率', '上周综合毛利率', False),
('总客单', '本周总客单', '上周总客单', False),
('客单价', '本周客单价', '上周客单价', False),
('线上销售', '本周线上销售', '上周线上销售', False),
('线上客单', '本周线上客单', '上周线上客单', False),
('线下销售', '本周线下销售', '上周线下销售', False),
('线下客单', '本周线下客单', '上周线下客单', False),
('DTP销售', '本周dtp销售', '上周dtp销售', True),
('排除DTP销售', '本周排除dtp销售', '上周排除dtp销售', False),
('排除DTP综合毛利率', '本周排除dtp综合毛利率', '上周排除dtp综合毛利率', False),
('排除DTP客单', '本周排除dtp客单', '上周排除dtp客单', False),
('排除DTP客单价', '本周排除dtp客单价', '上周排除dtp客单价', False),
('中药销售', '本周中药销售', '上周中药销售', True),
('内方销售', '本周内方销售', '上周内方销售', True),
('膏方销售', '本周膏方销售', '上周膏方销售', True),
('会员销售', '本周会员销售', '上周会员销售', True),
('会员客单', '本周会员客单', '上周会员客单', False),
('会员数', '本周会员数', '上周会员数', False),
('会员客单价', '本周会员客单价', '上周会员客单价', False),
('会员人均消费', '本周会员人均消费', '上周会员人均消费', False),
('会员消费频次', '本周会员消费频次', '上周会员消费频次', False),
('医保会员销售', '本周医保会员销售', '上周医保会员销售', True),
('医保会员客单', '本周医保会员客单', '上周医保会员客单', False),
('医保会员数', '本周医保会员数', '上周医保会员数', False),
('医保会员客单价', '本周医保会员客单价', '上周医保会员客单价', False),
('医保会员人均消费', '本周医保会员人均消费', '上周医保会员人均消费', False),
('医保会员消费频次', '本周医保会员消费频次', '上周医保会员消费频次', False)
]
for indicator in indicators:
name, curr_col, prev_col, has_ratio = indicator
curr_value = df.iloc[0][curr_col]
prev_value = df.iloc[0][prev_col]
# 处理占比列
if has_ratio:
curr_ratio_col = curr_col.replace('本周', '本周') + '占比'
prev_ratio_col = curr_col.replace('本周', '上周') + '占比'
curr_ratio_value = df.iloc[0][curr_ratio_col] if curr_ratio_col in df.columns else '0%'
prev_ratio_value = df.iloc[0][prev_ratio_col] if prev_ratio_col in df.columns else '0%'
else:
curr_ratio_value = ''
prev_ratio_value = ''
# Calculate week-over-week change
try:
if isinstance(curr_value, str) and '%' in curr_value:
curr_num = float(curr_value.replace('%', ''))
prev_num = float(prev_value.replace('%', ''))
mom = f"{curr_num - prev_num:.2f}%"
else:
curr_num = float(curr_value) if curr_value else 0
prev_num = float(prev_value) if prev_value else 0
mom = curr_num - prev_num
if name in ['综合毛利率', '排除DTP综合毛利率']:
mom = f"{mom:.2f}%"
else:
mom = f"{mom:.2f}"
except:
mom = '0'
table_data.append([
name,
str(curr_value),
str(curr_ratio_value),
str(prev_value),
str(prev_ratio_value),
str(mom)
])
return table_data
def generate_yearly_sales_table(self):
sql = f"""
SELECT e.c_mdfq 大区,
e.c_dq 地市,
e.c_mdfq1 地区,
e.tjbh 编码,
e.mc 门店,
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='{self.store_id}') 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, 'YEAR') AND TRUNC(SYSDATE- 1)
AND a.subbh = '{self.store_id}'
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 = '{self.store_id}'
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, 'YEAR') AND TRUNC(SYSDATE- 1)
AND a.subbh = '{self.store_id}'
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
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 = '{self.store_id}'
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
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 = '{self.store_id}'
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 = '{self.store_id}'
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, 'YEAR') AND TRUNC(SYSDATE- 1)
AND fkfsname LIKE '%医保%'
) b ON b.lsh=a.lsh
WHERE a.subbh = '{self.store_id}'
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 = '{self.store_id}'
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
"""
df = self.fetch_data(sql)
if df.empty:
return None
# 构造年销售同比数据表格
table_data = []
indicators = [
('总销售(万)', '今年总销售(万)', '去年总销售(万)', False),
('综合毛利率', '今年综合毛利率', '去年综合毛利率', False),
('总客单', '今年总客单', '去年总客单', False),
('客单价', '今年客单价', '去年客单价', False),
('线上销售(万)', '今年线上销售(万)', '去年线上销售(万)', False),
('线上客单', '今年线上客单', '去年线上客单', False),
('线下销售(万)', '今年线下销售(万)', '去年线下销售(万)', False),
('线下客单', '今年线下客单', '去年线下客单', False),
('DTP销售(万)', '今年DTP销售(万)', '去年DTP销售(万)', True),
('排除DTP销售(万)', '今年排除DTP销售(万)', '去年排除DTP销售(万)', False),
('排除DTP综合毛利率', '今年排除DTP综合毛利率', '去年排除DTP综合毛利率', False),
('排除DTP客单', '今年排除DTP客单', '去年排除DTP客单', False),
('排除DTP客单价', '今年排除DTP客单价', '去年排除DTP客单价', False),
('中药销售(万)', '今年中药销售(万)', '去年中药销售(万)', True),
('内方销售(万)', '今年内方销售(万)', '去年内方销售(万)', True),
('膏方销售(万)', '今年膏方销售(万)', '去年膏方销售(万)', True),
('会员销售(万)', '今年会员销售(万)', '去年会员销售(万)', True),
('会员客单', '今年会员客单', '去年会员客单', False),
('会员数', '今年会员数', '去年会员数', False),
('会员客单价', '今年会员客单价', '去年会员客单价', False),
('会员人均消费', '今年会员人均消费', '去年会员人均消费', False),
('会员消费频次', '今年会员消费频次', '去年会员消费频次', False),
('医保会员销售(万)', '今年医保会员销售(万)', '去年医保会员销售(万)', True),
('医保会员客单', '今年医保会员客单', '去年医保会员客单', False),
('医保会员数', '今年医保会员数', '去年医保会员数', False),
('医保会员客单价', '今年医保会员客单价', '去年医保会员客单价', False),
('医保会员人均消费', '今年医保会员人均消费', '去年医保会员人均消费', False),
('医保会员消费频次', '今年医保会员消费频次', '去年医保会员消费频次', False)
]
for indicator in indicators:
name, curr_col, prev_col, has_ratio = indicator
curr_value = df.iloc[0][curr_col] if curr_col in df.columns else 0
prev_value = df.iloc[0][prev_col] if prev_col in df.columns else 0
ratio_curr = ''
ratio_prev = ''
if has_ratio:
# 修改字段拼接逻辑，使用你提供的规则：替换 "(万)" 和 "今年" -> "去年"
ratio_col = curr_col.replace('(万)', '').replace('今年', '去年') + '占比'
ratio_prev_col = prev_col.replace('(万)', '').replace('去年', '今年') + '占比'
# 如果字段不存在，则设置默认值
ratio_curr = df.iloc[0][ratio_col] if ratio_col in df.columns else '0%'
ratio_prev = df.iloc[0][ratio_prev_col] if ratio_prev_col in df.columns else '0%'
try:
if isinstance(curr_value, str) and '%' in curr_value:
curr_num = float(curr_value.replace('%', ''))
prev_num = float(prev_value.replace('%', '')) if isinstance(prev_value,
str) and '%' in prev_value else 0
yoy = f"{curr_num - prev_num:.2f}%"
else:
curr_num = float(curr_value) if isinstance(curr_value, (int, float)) else 0
prev_num = float(prev_value) if isinstance(prev_value, (int, float)) else 0
yoy = curr_num - prev_num
if name in ['综合毛利率', '排除DTP综合毛利率']:
yoy = f"{yoy:.2f}%"
else:
yoy = f"{yoy:.2f}"
except Exception as e:
yoy = '0'
table_data.append([
name,
str(curr_value),
str(ratio_curr),
str(prev_value),
str(ratio_prev),
str(yoy)
])
return table_data
年度数据参数传的不对，检查代码，模仿周数据的代码，修正年度数据内容
扫一扫
1241
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
