# -*- coding: utf-8 -*-
import os
import cx_Oracle as cx
import pandas as pd
import datetime as dt
import schedule
import time
import requests
from threading import Thread
import dataframe_image as dfi
import base64
import hashlib
class Wechat:
def __init__(self, secret):
self.secret = secret
def access_token(self):
"""获取企业微信access_token"""
url = f'https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid=wwed5e7f3fd1a3a553&corpsecret={self.secret}'
response = requests.get(url).json()
return response['access_token']
def oracle_connect(sql):
"""Oracle数据库连接查询"""
con = cx.connect("admin/xxb20140415@192.168.3.16:1521/zzjdata")
cursor = con.cursor()
cursor.execute(sql)
column_s = [col[0] for col in cursor.description]
data = cursor.fetchall()
cursor.close()
con.close()
return data, column_s
def generate_variety_sales_sql():
"""生成地区各品种销售进度表SQL"""
sql = """
with mdxs as (select a.subbh,b.lb,sum(case when nvl(b.bz,0)<>0 then a.sl*b.bz else a.jshj end) jshj from subfhd a
left join c_ylgcjxsrw b on a.hh=b.hh
where a.kdrq BETWEEN TRUNC(SYSDATE-1,'MONTH') AND TRUNC(SYSDATE-1) and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' and b.hh is not null
group by a.subbh,b.lb)
select distinct c.c_dq 地市,a.c_mdfq1 地区,a.lb 类别,round(a.xsrw,0) 任务,nvl(b.jshj,0) "销量/额",case when nvl(a.xsrw,0)<>0 then round(nvl(b.jshj,0)*100/a.xsrw,4) else 0 end 进度
from c_ylxsrw a
left join (select b.c_mdfq1,a.lb,sum(a.jshj) jshj from mdxs a left join gl_custom b on a.subbh=b.tjbh group by b.c_mdfq1,a.lb) b on a.c_mdfq1=b.c_mdfq1 and a.lb=b.lb
left join (select distinct c_dq,c_mdfq1 from gl_custom where c_dq<>' ') c on a.c_mdfq1=c.c_mdfq1
"""
return sql
def transform_data_to_pivot(df):
"""将数据转换为透视表格式 - 修正版"""
try:
# 筛选有效数据
df = df[(df['进度'] > 0) | (df['销量/额'] > 0)].copy()
# 固定品类顺序
fixed_categories = [
'智禾', '红曲', '黄精', '三鞭补酒', '达因D', '云南白药血膏剂',
'云南白药小气', '杭州远大', '来益叶黄素', 'VC系列', '京润珍珠',
'地喹氯铵含片', '扬子江'
]
# 从数据中筛选出实际存在的品类
existing_categories = [cat for cat in fixed_categories if cat in df['类别'].unique()]
# 如果有数据中存在但不在固定列表中的品类，可以添加到后面（可选）
other_categories = [cat for cat in sorted(df['类别'].unique()) if cat not in fixed_categories]
all_categories = existing_categories + other_categories
print(f"固定品类顺序: {all_categories}")
# 创建层级结构的数据
result_data = []
# 第一部分：数据查询中的地区（小的区域）- 直接从数据查询得到
print("处理地区数据...")
region_data = []
for region in sorted(df['地区'].unique()):
region_df = df[df['地区'] == region]
# 添加地区行
region_row = {'区域': region, '类型': '地区', '排序键': 1}
total_sales = 0 # 初始化总销售额
for category in all_categories:
category_df = region_df[region_df['类别'] == category]
# 计算地区各品类的销量/额和进度 - 直接从数据查询得到
if len(category_df) > 0:
sales_amount = float(category_df['销量/额'].iloc[0]) # 转换为数值
progress = float(category_df['进度'].iloc[0]) # 转换为数值
else:
sales_amount = 0
progress = 0
region_row[f'{category}_销量'] = sales_amount
region_row[f'{category}_进度'] = progress
total_sales += sales_amount # 累加总销售额
# 设置总销售额
region_row['总销售额'] = total_sales
region_data.append(region_row)
print(f" 添加地区: {region}, 总销售额: {total_sales:,.0f}")
# 按总销售额升序排序地区
region_data.sort(key=lambda x: x['总销售额'])
# 第二部分：数据查询中的地市（大的地区）- 重新计算（修正版）
print("处理地市数据...")
city_data = []
for city in sorted(df['地市'].unique()):
city_df = df[df['地市'] == city]
# 计算地市各品类的总销量和总任务
city_row = {'区域': city, '类型': '地市', '排序键': 2}
total_sales = 0 # 初始化总销售额
total_task = 0 # 初始化总任务
for category in all_categories:
category_df = city_df[city_df['类别'] == category]
# 地市总销量 = 该地市下所有地区该品类销量的合计
total_sales_amount = category_df['销量/额'].sum()
# 地市总任务 = 该地市下所有地区该品类任务的合计
total_task_amount = category_df['任务'].sum()
# 修正：重新计算地市进度 = 地市总销量 / 地市总任务
if total_task_amount > 0:
city_progress = round(total_sales_amount * 100 / total_task_amount, 2)
else:
city_progress = 0
city_row[f'{category}_销量'] = total_sales_amount
city_row[f'{category}_进度'] = city_progress
total_sales += total_sales_amount # 累加总销售额
total_task += total_task_amount # 累加总任务
# 设置总销售额
city_row['总销售额'] = total_sales
city_row['总任务'] = total_task
city_data.append(city_row)
print(
f" 添加地市: {city}, 总销售额: {total_sales:,.0f}, 总任务: {total_task:,.0f}, 总进度: {total_sales / total_task * 100 if total_task > 0 else 0:.2f}%")
# 按总销售额升序排序地市
city_data.sort(key=lambda x: x['总销售额'])
# 第三部分：总计 - 重新计算（修正版）
print("计算总计...")
total_row = {'区域': '总计', '类型': '总计', '排序键': 3}
total_sales = 0 # 初始化总销售额
total_task = 0 # 初始化总任务
for category in all_categories:
category_df = df[df['类别'] == category]
total_sales_amount = category_df['销量/额'].sum()
total_task_amount = category_df['任务'].sum()
# 修正：重新计算总计进度 = 所有地区总销量 / 所有地区总任务
if total_task_amount > 0:
total_progress = round(total_sales_amount * 100 / total_task_amount, 2)
else:
total_progress = 0
total_row[f'{category}_销量'] = total_sales_amount
total_row[f'{category}_进度'] = total_progress
total_sales += total_sales_amount # 累加总销售额
total_task += total_task_amount # 累加总任务
# 设置总销售额和总任务
total_row['总销售额'] = total_sales
total_row['总任务'] = total_task
# 合并数据：地区（升序） + 地市（升序） + 总计
result_data.extend(region_data)
result_data.extend(city_data)
result_data.append(total_row)
# 转换为DataFrame
result_df = pd.DataFrame(result_data)
# 重新排列列顺序：区域 + (每个品类的销量+进度)
final_columns = ['区域']
for category in all_categories:
final_columns.extend([f'{category}_销量', f'{category}_进度'])
result_df = result_df[final_columns]
print(f"数据转换完成，最终表格形状: {result_df.shape}")
# 数据验证
print("\n=== 数据验证 ===")
print(f"地区数量: {len(region_data)}")
print(f"地市数量: {len(city_data)}")
print(f"总计行: 1")
# 验证地市计算是否正确
print("\n=== 地市计算验证 ===")
for city in city_data:
city_name = city['区域']
city_df_sub = df[df['地市'] == city_name]
# 验证每个品类的计算
for category in all_categories[:3]: # 只验证前3个品类避免输出过多
category_df = city_df_sub[city_df_sub['类别'] == category]
actual_sales = category_df['销量/额'].sum()
actual_task = category_df['任务'].sum()
calculated_progress = city[f'{category}_进度']
if actual_task > 0:
expected_progress = round(actual_sales * 100 / actual_task, 2)
else:
expected_progress = 0
print(
f" {city_name}-{category}: 销量={actual_sales:.0f}, 任务={actual_task:.0f}, 进度={calculated_progress}% (预期: {expected_progress}%)")
# 验证总计计算
print("\n=== 总计计算验证 ===")
total_sales_actual = df['销量/额'].sum()
total_task_actual = df['任务'].sum()
total_progress_actual = round(total_sales_actual * 100 / total_task_actual, 2) if total_task_actual > 0 else 0
print(
f"总计验证: 总销量={total_sales_actual:.0f}, 总任务={total_task_actual:.0f}, 总进度={total_progress_actual}%")
return result_df, all_categories
except Exception as e:
print(f"数据转换失败: {str(e)}")
import traceback
traceback.print_exc()
return None, None
def create_multiindex_columns(categories):
"""创建多级表头"""
columns = [('', '区域')]
for category in categories:
columns.extend([(category, '销量/额'), (category, '进度')])
return pd.MultiIndex.from_tuples(columns)
def img_create_variety_sales(data, title, format_dict, subset, path, file_mc):
"""
生成品种销售进度表图片
:param data: 传入的数据体
:param title: 表头标题
:param format_dict: 数据格式字典
:param subset: 需要渐变染色的列名列表
:param path: 文件保存路径
:param file_mc: 文件名
"""
try:
# 设置样式 - 使用多级索引访问
styled_data = data.style.hide(axis='index').set_caption(title).set_table_styles([
{'selector': 'caption', # 标题
'props': [('font-weight', 'bold'), ('font-family', 'Microsoft Yahei'), ('color', '#000000'),
('background-color', '#F4B084'), ('font-size', '16px'), ('border-top', '2px solid #000000'),
('border-left', '2px solid #000000'), ('border-right', '2px solid #000000'),
('text-align', 'center'), ('padding', '6px')]},
{'selector': 'th.level0', # 一级表头
'props': [('font-family', 'Microsoft Yahei'), ('border', '1px solid #000000'),
('color', '#000000'), ('background-color', '#F4B084'), ('font-size', '10px'),
('vertical-align', 'center'), ('text-align', 'center'), ('white-space', 'nowrap'),
('padding', '4px')]},
{'selector': 'th.level1', # 二级表头
'props': [('font-family', 'Microsoft Yahei'), ('border', '1px solid #000000'),
('color', '#000000'), ('background-color', '#F4B084'), ('font-size', '9px'),
('vertical-align', 'center'), ('text-align', 'center'), ('white-space', 'nowrap'),
('padding', '3px')]},
{'selector': 'td.data', # 数据
'props': [('font-family', 'Microsoft Yahei'), ('border', '1px solid #000000'), ('font-size', '8px'),
('vertical-align', 'center'), ('text-align', 'center'), ('white-space', 'nowrap'),
('padding', '2px')]},
# 为地市行设置特殊样式
{
'selector': 'td.col0:contains("信阳地区"), td.col0:contains("南阳地区"), td.col0:contains("周口地区"), td.col0:contains("平顶山地区"), td.col0:contains("漯河地区"), td.col0:contains("许昌地区"), td.col0:contains("驻马店地区")',
'props': [('background-color', '#D1EEEE'), ('font-weight', 'bold')]},
# 为总计行设置特殊样式 - 黄色填充，红色字体
{'selector': 'tr:last-child td',
'props': [('background-color', '#FFFF00'), ('font-weight', 'bold'), ('color', '#FF0000')]}
]).format(format_dict, na_rep='-').background_gradient(subset=subset, cmap='RdYlGn_r')
# 设置表格整体样式
styled_data.set_table_styles([{
'selector': 'table',
'props': [('width', 'auto'), ('margin-left', 'auto'), ('margin-right', 'auto')]
}], overwrite=False)
# 导出图片
dfi.export(styled_data, os.path.join(path, f'{file_mc}.png'))
return True
except Exception as e:
print(f"生成图片失败: {str(e)}")
import traceback
traceback.print_exc()
return False
def create_variety_sales_report(path, df, categories):
"""创建品种销售进度报表图片"""
try:
# 获取当前日期
now = dt.datetime.now()
date_str = now.strftime("%Y%m%d")
title_date = now.strftime("%Y年%m月%d日")
# 创建多级表头
multiindex_columns = create_multiindex_columns(categories)
df.columns = multiindex_columns
# 设置格式字典
format_dict = {}
# 为销量列设置数值格式，为进度列设置百分比格式
for category in categories:
format_dict[(category, '销量/额')] = '{:,.0f}'
format_dict[(category, '进度')] = '{:.2f}%'
# 需要渐变染色的列（只对进度列进行渐变染色）
subset_cols = [(category, '进度') for category in categories]
# 生成图片
success = img_create_variety_sales(
data=df,
title=f"截至{title_date} 本月地区各品种销售进度表",
format_dict=format_dict,
subset=subset_cols,
path=path,
file_mc=f'品种销售进度表_{date_str}'
)
if success:
image_path = os.path.join(path, f'品种销售进度表_{date_str}.png')
print(f"图片生成成功: {image_path}")
return [image_path]
else:
return None
except Exception as e:
print(f"生成品种销售进度报表失败: {str(e)}")
return None
def send_robot_message(image_path, webhook_key):
"""通过企业微信群机器人发送图片消息"""
try:
# 读取图片内容
with open(image_path, 'rb') as f:
image_data = f.read()
# 将图片内容转换为base64
image_base64 = base64.b64encode(image_data).decode('utf-8')
# 计算MD5
md5 = hashlib.md5(image_data).hexdigest()
# 发送图片消息
send_url = f'https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key={webhook_key}'
payload = {
"msgtype": "image",
"image": {
"base64": image_base64,
"md5": md5
}
}
response = requests.post(send_url, json=payload).json()
return response.get('errcode') == 0
except Exception as e:
print(f"群机器人消息发送失败: {str(e)}")
return False
def application_push_image(access_token, image_path, user_id="037565", agent_id="1000077"):
"""推送图片到企业微信（个人）"""
try:
# 上传图片获取media_id
with open(image_path, 'rb') as f:
files = {'media': f}
upload_url = f'https://qyapi.weixin.qq.com/cgi-bin/media/upload?access_token={access_token}&type=image'
upload_response = requests.post(upload_url, files=files).json()
if 'media_id' not in upload_response:
print(f"图片上传失败: {upload_response}")
return None
media_id = upload_response['media_id']
# 发送图片消息
send_url = f'https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token={access_token}'
payload = {
"touser": user_id,
"msgtype": "image",
"agentid": agent_id,
"image": {"media_id": media_id},
"safe": 0
}
response = requests.post(send_url, json=payload).json()
return response
except Exception as e:
print(f"图片发送失败: {str(e)}")
return None
def send_variety_sales_report(path):
"""发送品种销售进度表"""
try:
# 初始化微信
wechat = Wechat('tXNMrgcTgeV3IAqJhWB7mOe_bcKe9EtdCDze_75mGeY')
access_token = wechat.access_token()
# 获取原始数据
sql = generate_variety_sales_sql()
data, columns = oracle_connect(sql)
# 转换为DataFrame
df = pd.DataFrame(data, columns=columns)
print(f"获取到 {len(df)} 条原始数据")
# 数据验证
print("\n数据验证:")
print(f"地市列表: {sorted(df['地市'].unique())}")
print(f"地区列表: {sorted(df['地区'].unique())}")
print(f"品类列表: {sorted(df['类别'].unique())}")
# 显示样本数据
print("\n样本数据:")
print(df.head(10).to_string())
# 转换数据为透视表格式
pivot_df, categories = transform_data_to_pivot(df)
if pivot_df is None or len(pivot_df) == 0:
raise Exception("数据转换后为空")
print(f"转换后数据形状: {pivot_df.shape}")
print("最终表格结构:")
print(pivot_df[['区域']].to_string(index=False))
# 生成报表图片
image_paths = create_variety_sales_report(path, pivot_df, categories)
if not image_paths:
raise Exception("报表图片生成失败")
# 群机器人webhook key
webhook_key = "3cb06b7e-3658-4fbd-9676-54fc25fea470"
# 推送报表图片
for image_path in image_paths:
if os.path.exists(image_path):
# 发送给群机器人
robot_result = send_robot_message(image_path, webhook_key)
if robot_result:
print(f"{dt.datetime.now()} - 品种销售进度表 群机器人推送成功")
else:
print(f"{dt.datetime.now()} - 品种销售进度表 群机器人推送失败")
# 发送给个人
result = application_push_image(access_token, image_path, "037565")
if result and result.get('errcode') == 0:
print(f"{dt.datetime.now()} - 品种销售进度表 个人推送成功")
else:
print(f"{dt.datetime.now()} - 品种销售进度表 个人推送失败")
# 删除临时文件
os.remove(image_path)
return True
except Exception as e:
print(f"{dt.datetime.now()} - 品种销售进度表推送失败: {str(e)}")
return False
def run_variety_sales_scheduler(path):
"""运行品种销售进度表定时任务"""
# 每天10:00执行
schedule.every().day.at("10:00").do(send_variety_sales_report, path=path)
# 立即测试一次
print(f"{dt.datetime.now()} - 正在执行品种销售进度表首次测试...")
send_variety_sales_report(path)
while True:
schedule.run_pending()
time.sleep(60)
if __name__ == '__main__':
print(f'品种销售进度表程序启动时间: {dt.datetime.now()}')
output_path = r'D:\品种销售数据报表'
if not os.path.exists(output_path):
os.makedirs(output_path)
# 启动定时任务线程
scheduler_thread = Thread(target=run_variety_sales_scheduler, args=(output_path,))
scheduler_thread.daemon = True
scheduler_thread.start()
try:
while True:
time.sleep(60)
except KeyboardInterrupt:
print("品种销售进度表程序已停止")
检查代码，要求区域的销售和进度从数据查询中得到，地市的进度=地市的地区的销售的合计/地市的地区的任务的合计，总计的的进度=所有地区的销售的合计/所有地区的任务
最新发布
本文介绍如何使用Oracle SQL进行分组计算，包括各部门的自查问题数量和公司检查问题数量，并计算出自查比率。通过使用grouping和group by rollup子句，可以得到详细的统计结果并进行总计，最后将比率转换为百分比形式。
扫一扫
493
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
