data_analysis/data_processor/daily_zones_exporter.py

import mysql.connector
import pandas as pd
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.utils import get_column_letter
from openpyxl.styles import Alignment


def create_row(row_data, document_date='', shop_name=''):
    new_row_temp = {
        '单据日期': document_date,
        '区域': '',
        '组长': row_data['组长'],
        '999及以内单数': row_data['999及以内单数'].sum(),
        '1000-1999单数': row_data['1000-1999单数'].sum(),
        '2000-2899单数': row_data['2000-2899单数'].sum(),
        '2900-3999单数': row_data['2900-3999单数'].sum(),
        '4000-4999单数': row_data['4000-4999单数'].sum(),
        '5000-7999单数': row_data['5000-7999单数'].sum(),
        '8000以上单数': row_data['8000以上单数'].sum(),
        '满足奖励条件大单数': row_data['满足奖励条件大单数'].sum(),
        '最大单件数': row_data['最大单件数'].max(),
        '最大单金额': row_data['最大单金额'].max(),
        '总单数': row_data['总单数'].sum(),
        '2000+单数': row_data['2000+单数'].sum(),
        '2000+单数占比': row_data['2000+单数'].sum() / row_data['总单数'].sum() if row_data['总单数'].sum() != 0 else 0
    }

    if shop_name == '总计':
        new_row_temp['组长'] = '总计'
    else:
        new_row_temp['组长'] = f'{shop_name}汇总'

    return new_row_temp


class DailyZonesSalesExporter:
    def __init__(self, db_config):
        self.db_config = db_config
        self.query = """
SELECT *, IF(temp.`总单数` = 0, 0, temp.`2000+单数` / temp.`总单数`) AS `2000+单数占比` 
FROM (
    SELECT
        t1.document_date AS `单据日期`,
        t1.channel_type AS `店铺类型`,
        t3.division AS `区域`,
        t3.group_leader AS `组长`,
        SUM(CASE WHEN t1.remaining_amount_after_return <= 999 THEN 1 ELSE 0 END) AS `999及以内单数`,
        SUM(CASE WHEN t1.remaining_amount_after_return > 999 AND t1.remaining_amount_after_return < 2000 THEN 1 ELSE 0 END) AS `1000-1999单数`,
        SUM(CASE WHEN t1.remaining_amount_after_return >= 2000 AND t1.remaining_amount_after_return <= 2899 THEN 1 ELSE 0 END) AS `2000-2899单数`,
        SUM(CASE WHEN t1.remaining_amount_after_return > 2899 AND t1.remaining_amount_after_return <= 3999 THEN 1 ELSE 0 END) AS `2900-3999单数`,
        SUM(CASE WHEN t1.remaining_amount_after_return > 3999 AND t1.remaining_amount_after_return <= 4999 THEN 1 ELSE 0 END) AS `4000-4999单数`,
        SUM(CASE WHEN t1.remaining_amount_after_return > 4999 AND t1.remaining_amount_after_return <= 7999 THEN 1 ELSE 0 END) AS `5000-7999单数`,
        SUM(CASE WHEN t1.remaining_amount_after_return > 7999 THEN 1 ELSE 0 END) AS `8000以上单数`,
        SUM(CASE WHEN t2.refund_meets_big_order != '不符合' THEN 1 ELSE 0 END) AS `满足奖励条件大单数`,
        MAX(t2.big_order_items) AS `最大单件数`,
        MAX(t2.big_order_amount) AS `最大单金额`,
        COUNT(1) AS `总单数`,
        SUM(CASE WHEN t1.remaining_amount_after_return >= 2000 THEN 1 ELSE 0 END) AS `2000+单数`
    FROM sd_sales_order AS t1
    LEFT JOIN sd_big_sales_order AS t2 ON t1.sale_id = t2.sale_id
    LEFT JOIN sd_store_info AS t3 ON t3.f360_code = t1.store_code
    WHERE t3.group_leader IS NOT NULL
    GROUP BY t1.document_date, t1.channel_type, t3.group_leader, t3.division
    ORDER BY `单据日期`, `店铺类型`
) AS temp;
"""

    def export(self, output_file='output_by_shop_type_with_summary_and_total.xlsx'):
        global cursor, connection
        try:
            connection = mysql.connector.connect(**self.db_config)
            cursor = connection.cursor()
            cursor.execute(self.query)
            rows = cursor.fetchall()
            columns = [desc[0] for desc in cursor.description]
        finally:
            cursor.close()
            connection.close()

        df = pd.DataFrame(rows, columns=columns)

        for col in df.columns:
            if col not in ['单据日期', '店铺类型', '区域', '组长']:
                df[col] = pd.to_numeric(df[col], errors='coerce')

        grouped_by_date = df.groupby('单据日期', sort=False)
        new_data = []

        for date, date_df in grouped_by_date:
            grouped_by_shop = date_df.groupby('店铺类型', sort=False)

            for shop, shop_df in grouped_by_shop:
                for _, row in shop_df.iterrows():
                    new_row = {
                        '单据日期': date,
                        '区域': row['区域'],
                        '组长': row['组长'],
                        '999及以内单数': row['999及以内单数'],
                        '1000-1999单数': row['1000-1999单数'],
                        '2000-2899单数': row['2000-2899单数'],
                        '2900-3999单数': row['2900-3999单数'],
                        '4000-4999单数': row['4000-4999单数'],
                        '5000-7999单数': row['5000-7999单数'],
                        '8000以上单数': row['8000以上单数'],
                        '满足奖励条件大单数': row['满足奖励条件大单数'],
                        '最大单件数': row['最大单件数'],
                        '最大单金额': row['最大单金额'],
                        '总单数': row['总单数'],
                        '2000+单数': row['2000+单数'],
                        '2000+单数占比': row['2000+单数占比'],
                    }
                    new_data.append(new_row)

                summary_row = create_row(shop_df, date, shop)
                new_data.append(summary_row)

            day_summary_row = create_row(date_df, date, '总计')
            new_data.append(day_summary_row)

        final_df = pd.DataFrame(new_data)

        wb = Workbook()
        ws = wb.active

        for r in dataframe_to_rows(final_df, index=False, header=True):
            ws.append(r)

        current_date = None
        start_row = None

        for row in range(2, ws.max_row + 1):
            date_cell = ws.cell(row=row, column=1)
            date_value = date_cell.value

            if date_value != current_date:
                if start_row is not None and row > start_row + 1:
                    ws.merge_cells(start_row=start_row, start_column=1, end_row=row - 1, end_column=1)
                    merged_cell = ws.cell(row=start_row, column=1)
                    merged_cell.alignment = Alignment(horizontal='center', vertical='center')

                current_date = date_value
                start_row = row

        if start_row is not None and start_row < ws.max_row:
            ws.merge_cells(start_row=start_row, start_column=1, end_row=ws.max_row, end_column=1)
            merged_cell = ws.cell(row=start_row, column=1)
            merged_cell.alignment = Alignment(horizontal='center', vertical='center')

        header = {cell.value: cell.column for cell in ws[1]}
        numeric_columns = [
            '999及以内单数', '1000-1999单数', '2000-2899单数', '2900-3999单数',
            '4000-4999单数', '5000-7999单数', '8000以上单数', '满足奖励条件大单数',
            '最大单件数', '最大单金额', '总单数', '2000+单数'
        ]

        for col_name in numeric_columns:
            if col_name in header:
                col_letter = get_column_letter(header[col_name])
                for cell in ws[col_letter]:
                    if cell.row == 1:
                        continue
                    cell.number_format = '0'

        if '2000+单数占比' in header:
            col_letter = get_column_letter(header['2000+单数占比'])
            for cell in ws[col_letter]:
                if cell.row == 1:
                    continue
                cell.number_format = '0.00%'

        wb.save(output_file)
        print(f"✅ 数据已成功导出到 {output_file}")

    def export_to_dataframe(self):
        connection = mysql.connector.connect(**self.db_config)
        cursor = connection.cursor()
        try:
            cursor.execute(self.query)
            rows = cursor.fetchall()
            columns = [desc[0] for desc in cursor.description]
        finally:
            cursor.close()
            connection.close()

        df = pd.DataFrame(rows, columns=columns)

        for col in df.columns:
            if col not in ['单据日期', '店铺类型', '区域', '组长']:
                df[col] = pd.to_numeric(df[col], errors='coerce')

        grouped_by_date = df.groupby('单据日期', sort=False)
        new_data = []

        for date, date_df in grouped_by_date:
            grouped_by_shop = date_df.groupby('店铺类型', sort=False)

            for shop, shop_df in grouped_by_shop:
                for _, row in shop_df.iterrows():
                    new_row = {
                        '单据日期': date,
                        '区域': row['区域'],
                        '组长': row['组长'],
                        '999及以内单数': row['999及以内单数'],
                        '1000-1999单数': row['1000-1999单数'],
                        '2000-2899单数': row['2000-2899单数'],
                        '2900-3999单数': row['2900-3999单数'],
                        '4000-4999单数': row['4000-4999单数'],
                        '5000-7999单数': row['5000-7999单数'],
                        '8000以上单数': row['8000以上单数'],
                        '满足奖励条件大单数': row['满足奖励条件大单数'],
                        '最大单件数': row['最大单件数'],
                        '最大单金额': row['最大单金额'],
                        '总单数': row['总单数'],
                        '2000+单数': row['2000+单数'],
                        '2000+单数占比': row['2000+单数占比'],
                    }
                    new_data.append(new_row)

                summary_row = create_row(shop_df, date, shop)
                new_data.append(summary_row)

            day_summary_row = create_row(date_df, date, '总计')
            new_data.append(day_summary_row)

        final_df = pd.DataFrame(new_data)
        return final_df