# -*- 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 = TRUNC(SYSDATE) 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_ylrjxsrw 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:
print(f"原始数据行数: {len(df)}")
print(f"原始数据列: {df.columns.tolist()}")
# 显示数据样本
print("\n原始数据样本:")
print(df.head(10).to_string())
# 检查数据完整性
print(f"\n数据统计:")
print(f"地市数量: {len(df['地市'].unique())}")
print(f"地区数量: {len(df['地区'].unique())}")
print(f"品类数量: {len(df['类别'].unique())}")
print(f"总任务额: {df['任务'].sum():.0f}")
print(f"总销售额: {df['销量/额'].sum():.0f}")
# 固定品类顺序
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"\n处理品类: {all_categories}")
result_data = []
# === 第一部分:地区数据(直接从原始数据获取)===
print("\n=== 处理地区数据 ===")
region_data = []
for region in sorted(df['地区'].unique()):
region_df = df[df['地区'] == region]
region_city = region_df['地市'].iloc[0] if len(region_df) > 0 else "未知"
row = {'区域': region, '类型': '地区', '排序键': 1}
region_total_sales = 0
region_total_task = 0
for category in all_categories:
cat_data = region_df[region_df['类别'] == category]
if len(cat_data) > 0:
sales = float(cat_data['销量/额'].iloc[0])
task = float(cat_data['任务'].iloc[0])
progress = float(cat_data['进度'].iloc[0])
else:
sales, task, progress = 0, 0, 0
row[f'{category}_销量'] = sales
row[f'{category}_进度'] = progress
region_total_sales += sales
region_total_task += task
row['总销售额'] = region_total_sales
row['总任务'] = region_total_task
region_data.append(row)
print(f"地区 {region}: 销售={region_total_sales:.0f}, 任务={region_total_task:.0f}")
# 按总销售额排序地区数据
region_data.sort(key=lambda x: x['总销售额'])
result_data.extend(region_data)
# === 第二部分:地市数据(重新聚合计算)===
print("\n=== 处理地市数据 ===")
city_data = []
for city in sorted(df['地市'].unique()):
city_df = df[df['地市'] == city]
row = {'区域': city, '类型': '地市', '排序键': 2}
city_total_sales = 0
city_total_task = 0
for category in all_categories:
# 获取该地市该品类的所有地区数据
cat_data = city_df[city_df['类别'] == category]
sales_sum = cat_data['销量/额'].sum()
task_sum = cat_data['任务'].sum()
# 重新计算进度:地市进度 = 地市销售合计 / 地市任务合计
progress = round(sales_sum * 100 / task_sum, 2) if task_sum > 0 else 0
row[f'{category}_销量'] = sales_sum
row[f'{category}_进度'] = progress
city_total_sales += sales_sum
city_total_task += task_sum
row['总销售额'] = city_total_sales
row['总任务'] = city_total_task
city_data.append(row)
print(
f"地市 {city}: 销售={city_total_sales:.0f}, 任务={city_total_task:.0f}, 进度={(city_total_sales / city_total_task * 100) if city_total_task > 0 else 0:.2f}%")
# 按总销售额排序地市数据
city_data.sort(key=lambda x: x['总销售额'])
result_data.extend(city_data)
# === 第三部分:总计数据 ===
print("\n=== 处理总计数据 ===")
total_row = {'区域': '总计', '类型': '总计', '排序键': 3}
total_sales = 0
total_task = 0
for category in all_categories:
cat_data = df[df['类别'] == category]
sales_total = cat_data['销量/额'].sum()
task_total = cat_data['任务'].sum()
# 重新计算总进度:总计进度 = 总销售 / 总任务
progress_total = round(sales_total * 100 / task_total, 2) if task_total > 0 else 0
total_row[f'{category}_销量'] = sales_total
total_row[f'{category}_进度'] = progress_total
total_sales += sales_total
total_task += task_total
total_row['总销售额'] = total_sales
total_row['总任务'] = total_task
result_data.append(total_row)
print(
f"总计: 销售={total_sales:.0f}, 任务={total_task:.0f}, 进度={(total_sales / total_task * 100) if total_task > 0 else 0:.2f}%")
# 构建最终DataFrame - 包含类型信息用于后续处理
final_columns = ['区域', '类型']
for cat in all_categories:
final_columns.extend([f'{cat}_销量', f'{cat}_进度'])
result_df = pd.DataFrame(result_data)[final_columns]
print(f"\n✅ 转换完成,最终表格形状: {result_df.shape}")
print("最终表格结构:")
print(result_df[['区域', '类型'] + [f'{all_categories[0]}_销量', f'{all_categories[0]}_进度']].head().to_string())
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, region_types=None):
"""
生成品种销售进度表图片 - 调试版本
"""
try:
print(f"\n=== 开始生成图片 ===")
print(f"数据形状: {data.shape}")
print(f"区域类型信息长度: {len(region_types) if region_types is not None else 'None'}")
# 获取品类列表
categories = [col[0] for col in data.columns if col[1] == '进度' and col[0] != '']
print(f"品类列表: {categories}")
# 详细打印每行的区域和类型信息
print("\n=== 详细区域信息 ===")
for idx in range(len(data)):
region_name = data.iloc[idx][('', '区域')]
region_type = region_types.iloc[idx] if region_types is not None and idx < len(region_types) else "未知"
print(f"行 {idx}: 区域='{region_name}', 类型='{region_type}'")
# 找出每个品类进度排名前三的地区
top3_styles = []
print("\n=== 计算各品类进度前三名 ===")
# 构建地区索引映射
region_indices = {}
for idx in range(len(data)):
region_name = data.iloc[idx][('', '区域')]
if region_types is not None and idx < len(region_types):
region_type = region_types.iloc[idx]
if region_type == '地区' and region_name != '总计':
region_indices[region_name] = idx
print(f"✅ 识别为地区: {region_name} (索引: {idx})")
else:
print(f"❌ 排除: {region_name} (类型: {region_type})")
print(f"最终参与排名的地区: {list(region_indices.keys())}")
# 为每个品类单独计算前三名
for category in categories:
progress_col = (category, '进度')
print(f"\n--- 处理品类: {category} ---")
# 收集该品类所有地区的进度数据
region_progress = []
for region_name, row_idx in region_indices.items():
progress_value = data.iloc[row_idx][progress_col]
if pd.notna(progress_value) and progress_value != 0:
region_progress.append((region_name, float(progress_value), row_idx))
print(f" {region_name}: {progress_value}%")
else:
print(f" {region_name}: {progress_value}% (无效数据)")
# 按进度值降序排列,取前三名
if region_progress:
region_progress.sort(key=lambda x: x[1], reverse=True)
top3_regions = region_progress[:3]
print(f"{category} 品类进度前三名:")
for i, (region_name, progress_value, row_idx) in enumerate(top3_regions, 1):
print(f" 第{i}名: {region_name} - {progress_value}%")
# 计算CSS选择器
col_idx = data.columns.get_loc(progress_col)
row_css_idx = row_idx + 2 # +2是因为第一行是表头
col_css_idx = col_idx + 1 # +1是因为第一列是区域列
selector = f'tr:nth-child({row_css_idx}) td:nth-child({col_css_idx})'
print(f" 应用样式: {selector}")
top3_styles.append({
'selector': selector,
'props': [
('background-color', '#FF0000 !important'),
('color', '#FFFFFF !important'),
('font-weight', 'bold !important')
]
})
else:
print(f"{category} 品类没有有效的进度数据")
print(f"\n总共生成了 {len(top3_styles)} 个前三名样式规则")
# 设置基础样式
base_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': 'tr:last-child td',
'props': [('background-color', '#FFFF00 !important'),
('font-weight', 'bold !important'),
('color', '#FF0000 !important')]}
]
# 应用样式
styled_data = (data.style
.hide(axis='index')
.set_caption(title)
.set_table_styles(base_styles)
.format(format_dict, na_rep='-'))
# 应用前三名样式
for style in top3_styles:
styled_data = styled_data.set_table_styles([style], overwrite=False)
# 导出图片
output_path = os.path.join(path, f'{file_mc}.png')
dfi.export(styled_data, output_path)
print(f"✅ 图片生成成功: {output_path}")
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日")
# 保存类型信息到临时变量 - 确保索引对齐
region_types = df['类型'].reset_index(drop=True)
# 创建多级表头(移除类型列)
df_for_display = df.drop('类型', axis=1).reset_index(drop=True)
multiindex_columns = create_multiindex_columns(categories)
df_for_display.columns = multiindex_columns
# 设置格式字典
format_dict = {}
# 为销量列设置数值格式,为进度列设置百分比格式
for category in categories:
format_dict[(category, '销量/额')] = '{:,.0f}'
format_dict[(category, '进度')] = '{:.2f}%'
# 需要渐变染色的列(只对进度列进行渐变染色)
subset_cols = [(category, '进度') for category in categories]
# 生成图片 - 修改标题为"截止X点X月X日地区各品种销售进度表"
success = img_create_variety_sales(
data=df_for_display,
title=f"截止{now.hour}点{title_date}地区各品种销售进度表",
format_dict=format_dict,
subset=subset_cols,
path=path,
file_mc=f'品种销售进度表_{date_str}',
region_types=region_types # 传递类型信息
)
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点到晚上22点,每2小时执行一次
schedule_times = [
"10:00","12:00", "14:00", "16:00", "18:00", "20:00", "22:00"
]
for time_str in schedule_times:
schedule.every().day.at(time_str).do(send_variety_sales_report, path=path)
print(f"已设置定时任务: 每天 {time_str} 执行")
# 立即测试一次
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("品种销售进度表程序已停止")
检查代码每个类别选出进度前三的地区填充红色背景白色字体,地市不参与填充