#!/usr/bin/env python3 # -*- coding: utf-8 -*- """ 导出所有用户的剩余权益数据Excel文件 包含字段：客户档案号、姓名、手机号、注册时间、归属门店、来源、品项名称、单价、总购买次数、已消费次数、已退卡次数、已扣除次数、剩余次数、剩余价值等 """ import pymysql import pandas as pd from datetime import datetime import os # 数据库配置 DB_CONFIG = { 'host': 'rm-2vccze142rc9a8f58bo.mysql.cn-chengdu.rds.aliyuncs.com', 'port': 3306, 'user': 'lvqiansql', 'password': 'LvQ1@n!20251211', 'database': 'lqerp_dev', 'charset': 'utf8mb4', 'connect_timeout': 60, 'read_timeout': 300, 'write_timeout': 300 } def query_data(): """查询所有用户的剩余权益数据（分批查询优化）""" connection = None try: connection = pymysql.connect(**DB_CONFIG) cursor = connection.cursor() print("步骤1/4: 查询基础开单品项数据...") # 先查询基础数据 base_sql = """ SELECT pxmx.F_Id AS 开单品项明细ID, pxmx.F_MemberId AS 会员ID, pxmx.glkdbh AS 开单编号, pxmx.px AS 品项ID, pxmx.pxmc AS 品项名称, pxmx.F_ItemCategory AS 品项分类, pxmx.F_BeautyType AS 科美类型, pxmx.F_PerformanceType AS 业绩类型, pxmx.F_SourceType AS 来源类型, pxmx.pxjg AS 单价, pxmx.F_ProjectNumber AS 总购买次数, pxmx.F_TotalPrice AS 总金额, pxmx.F_ActualPrice AS 实付金额, pxmx.yjsj AS 业绩时间, pxmx.F_Remark AS 备注 FROM lq_kd_pxmx pxmx WHERE pxmx.F_IsEffective = 1 """ base_df = pd.read_sql(base_sql, connection) print(f" 查询到 {len(base_df)} 条开单品项记录") if base_df.empty: return pd.DataFrame() print("步骤2/4: 查询客户信息...") # 查询客户信息 member_ids = base_df['会员ID'].unique().tolist() if not member_ids: return pd.DataFrame() # 查询客户信息（包括已失效的客户，因为可能还有剩余权益） member_sql = f""" SELECT kh.F_Id AS 会员ID, kh.F_Id AS 客户编号, COALESCE(kh.dah, '') AS 客户档案号, COALESCE(kh.khmc, '') AS 客户姓名, COALESCE(kh.sjh, '') AS 手机号, kh.zcsj AS 注册时间, kh.gsmd AS 门店ID, COALESCE(md.dm, '') AS 归属门店 FROM lq_khxx kh LEFT JOIN lq_mdxx md ON kh.gsmd = md.F_Id WHERE kh.F_Id IN ({','.join(['%s'] * len(member_ids))}) """ member_df = pd.read_sql(member_sql, connection, params=member_ids) print(f" 查询到 {len(member_df)} 个客户信息") print("步骤3/4: 查询开单信息和消费/退卡/扣除数据...") # 查询开单信息 billing_ids = base_df['开单编号'].dropna().unique().tolist() if billing_ids: billing_sql = f""" SELECT kd.F_Id AS 开单编号, COALESCE(kd.khly, '') AS 客户来源, kd.kdrq AS 开单日期 FROM lq_kd_kdjlb kd WHERE kd.F_Id IN ({','.join(['%s'] * len(billing_ids))}) """ billing_df = pd.read_sql(billing_sql, connection, params=billing_ids) else: billing_df = pd.DataFrame(columns=['开单编号', '客户来源', '开单日期']) print(f" 查询到 {len(billing_df)} 条开单信息") # 查询已消费数据 item_ids = base_df['开单品项明细ID'].unique().tolist() if item_ids: consumed_sql = f""" SELECT F_BillingItemId AS 开单品项明细ID, SUM(F_OriginalProjectNumber) AS 已消费次数 FROM lq_xh_pxmx WHERE F_IsEffective = 1 AND F_BillingItemId IN ({','.join(['%s'] * len(item_ids))}) GROUP BY F_BillingItemId """ consumed_df = pd.read_sql(consumed_sql, connection, params=item_ids) else: consumed_df = pd.DataFrame(columns=['开单品项明细ID', '已消费次数']) print(f" 查询到 {len(consumed_df)} 条消费记录") # 查询已退卡数据 if item_ids: refunded_sql = f""" SELECT F_BillingItemId AS 开单品项明细ID, SUM(F_ProjectNumber) AS 已退卡次数 FROM lq_hytk_mx WHERE F_IsEffective = 1 AND F_BillingItemId IN ({','.join(['%s'] * len(item_ids))}) GROUP BY F_BillingItemId """ refunded_df = pd.read_sql(refunded_sql, connection, params=item_ids) else: refunded_df = pd.DataFrame(columns=['开单品项明细ID', '已退卡次数']) print(f" 查询到 {len(refunded_df)} 条退卡记录") # 查询已扣除数据 if item_ids: deducted_sql = f""" SELECT F_DeductId AS 开单品项明细ID, SUM(F_ProjectNumber) AS 已扣除次数 FROM lq_kd_deductinfo WHERE F_IsEffective = 1 AND F_DeductId IN ({','.join(['%s'] * len(item_ids))}) GROUP BY F_DeductId """ deducted_df = pd.read_sql(deducted_sql, connection, params=item_ids) else: deducted_df = pd.DataFrame(columns=['开单品项明细ID', '已扣除次数']) print(f" 查询到 {len(deducted_df)} 条扣除记录") print("步骤4/4: 合并数据并计算剩余权益...") # 合并数据 df = base_df.merge(member_df, on='会员ID', how='left') df = df.merge(billing_df, on='开单编号', how='left') df = df.merge(consumed_df, on='开单品项明细ID', how='left') df = df.merge(refunded_df, on='开单品项明细ID', how='left') df = df.merge(deducted_df, on='开单品项明细ID', how='left') # 填充客户信息空值（如果客户信息缺失，至少显示会员ID） df['客户编号'] = df['客户编号'].fillna(df['会员ID']) df['客户档案号'] = df['客户档案号'].fillna('') df['客户姓名'] = df['客户姓名'].fillna('') df['手机号'] = df['手机号'].fillna('') df['归属门店'] = df['归属门店'].fillna('') df['客户来源'] = df['客户来源'].fillna('') # 填充空值 df['已消费次数'] = df['已消费次数'].fillna(0) df['已退卡次数'] = df['已退卡次数'].fillna(0) df['已扣除次数'] = df['已扣除次数'].fillna(0) # 计算剩余次数和剩余价值 df['剩余次数'] = df['总购买次数'] - df['已消费次数'] - df['已退卡次数'] - df['已扣除次数'] df['剩余价值'] = df['单价'] * df['剩余次数'] # 过滤剩余次数大于0的记录 df = df[df['剩余次数'] > 0].copy() # 重命名列 df = df.rename(columns={ '客户档案号': '客户档案号', '客户姓名': '客户姓名', '手机号': '手机号', '注册时间': '注册时间', '归属门店': '归属门店', '客户来源': '客户来源', '品项名称': '品项名称', '品项ID': '品项ID', '品项分类': '品项分类', '科美类型': '科美类型', '业绩类型': '业绩类型', '来源类型': '来源类型', '单价': '单价', '总购买次数': '总购买次数', '已消费次数': '已消费次数', '已退卡次数': '已退卡次数', '已扣除次数': '已扣除次数', '剩余次数': '剩余次数', '剩余价值': '剩余价值', '总金额': '总金额', '实付金额': '实付金额', '开单日期': '开单日期', '业绩时间': '业绩时间', '备注': '备注' }) # 选择需要的列（客户编号放在最前面） df = df[[ '客户编号', '客户档案号', '客户姓名', '手机号', '注册时间', '归属门店', '客户来源', '品项名称', '品项ID', '品项分类', '科美类型', '业绩类型', '来源类型', '单价', '总购买次数', '已消费次数', '已退卡次数', '已扣除次数', '剩余次数', '剩余价值', '总金额', '实付金额', '开单日期', '业绩时间', '备注' ]] # 排序 df = df.sort_values(['客户档案号', '品项名称', '来源类型']) cursor.close() # 格式化时间字段 if '注册时间' in df.columns: df['注册时间'] = pd.to_datetime(df['注册时间']).dt.strftime('%Y-%m-%d %H:%M:%S') df['注册时间'] = df['注册时间'].replace('NaT', '') if '开单日期' in df.columns: df['开单日期'] = pd.to_datetime(df['开单日期']).dt.strftime('%Y-%m-%d %H:%M:%S') df['开单日期'] = df['开单日期'].replace('NaT', '') if '业绩时间' in df.columns: df['业绩时间'] = pd.to_datetime(df['业绩时间']).dt.strftime('%Y-%m-%d %H:%M:%S') df['业绩时间'] = df['业绩时间'].replace('NaT', '') # 确保数值字段为数值类型 numeric_columns = ['单价', '总购买次数', '已消费次数', '已退卡次数', '已扣除次数', '剩余次数', '剩余价值', '总金额', '实付金额'] for col in numeric_columns: if col in df.columns: df[col] = pd.to_numeric(df[col], errors='coerce').fillna(0) return df except Exception as e: print(f"查询数据时发生错误: {e}") raise finally: if connection: connection.close() def generate_excel(df, output_file='所有用户剩余权益数据.xlsx'): """生成Excel文件""" try: # 创建Excel写入器 with pd.ExcelWriter(output_file, engine='openpyxl') as writer: df.to_excel(writer, sheet_name='剩余权益明细', index=False) # 获取工作表对象进行格式设置 worksheet = writer.sheets['剩余权益明细'] # 设置列宽 column_widths = { 'A': 20, # 客户编号 'B': 20, # 客户档案号 'C': 15, # 客户姓名 'D': 15, # 手机号 'E': 20, # 注册时间 'F': 20, # 归属门店 'G': 15, # 客户来源 'H': 25, # 品项名称 'I': 20, # 品项ID 'J': 15, # 品项分类 'K': 12, # 科美类型 'L': 12, # 业绩类型 'M': 12, # 来源类型 'N': 12, # 单价 'O': 12, # 总购买次数 'P': 12, # 已消费次数 'Q': 12, # 已退卡次数 'R': 12, # 已扣除次数 'S': 12, # 剩余次数 'T': 15, # 剩余价值 'U': 15, # 总金额 'V': 15, # 实付金额 'W': 20, # 开单日期 'X': 20, # 业绩时间 'Y': 30 # 备注 } for col, width in column_widths.items(): worksheet.column_dimensions[col].width = width # 设置表头样式 from openpyxl.styles import Font, Alignment, PatternFill header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid') header_font = Font(bold=True, color='FFFFFF', size=11) for cell in worksheet[1]: cell.fill = header_fill cell.font = header_font cell.alignment = Alignment(horizontal='center', vertical='center') # 设置数据对齐方式 for row in worksheet.iter_rows(min_row=2, max_row=worksheet.max_row): for cell in row: col_letter = cell.column_letter # 数值列右对齐 if col_letter in ['N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V']: cell.alignment = Alignment(horizontal='right', vertical='center') else: cell.alignment = Alignment(horizontal='left', vertical='center') print(f"Excel文件已生成: {output_file}") print(f"共 {len(df)} 条记录") return output_file except Exception as e: print(f"生成Excel文件时发生错误: {e}") raise def main(): """主函数""" print("开始查询所有用户的剩余权益数据...") df = query_data() if df.empty: print("未查询到数据") return print(f"查询到 {len(df)} 条记录") print("\n前5条数据预览:") print(df.head()) # 统计信息 print(f"\n统计信息:") print(f"- 总客户数: {df['客户档案号'].nunique()}") print(f"- 总品项数: {df['品项名称'].nunique()}") print(f"- 剩余权益总价值: {df['剩余价值'].sum():,.2f} 元") print(f"- 剩余权益总次数: {df['剩余次数'].sum():,.0f} 次") # 生成Excel文件 output_file = f"所有用户剩余权益数据_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx" generate_excel(df, output_file) print(f"\n文件保存路径: {os.path.abspath(output_file)}") if __name__ == '__main__': main()