antissoft / lvqianmeiye_ERP

#!/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()