# -*- coding: utf-8 -*-
"""
-------------------------------------------------
# @Project  : pythonProject
# @File     : test
# @Date     : 2024/1/19 18:55
# @Author   : 徐胜虎
# @Email    : 1813366784@qq.com
# @Software : PyCharm
-------------------------------------------------
pip install styleframe
"""

import re
import warnings

import pandas as pd
from pandas import DataFrame
from styleframe import StyleFrame, Styler, utils

from utils import covert_datetime


# styleframe自定义使用方式范围行号，有点麻烦
def highlight_en_shi(row: DataFrame):
    is_en_shi = False
    pattern = re.compile('.*恩施|咸丰|巴东|野三关|来凤|利川|宣恩|建始|鹤峰.*', re.S)
    for cell in row:
        if re.match(pattern, cell):
            is_en_shi = True
    return is_en_shi


def highlight_yi_chang(row: DataFrame):
    is_yi_chang = False
    pattern = re.compile('.*宜昌|松滋|当阳|远安|石首.*', re.S)
    for cell in row:
        if re.match(pattern, cell):
            is_yi_chang = True
    return is_yi_chang


warnings.filterwarnings('ignore')

if __name__ == '__main__':
    received_data = pd.read_excel('resources/temp/收货报表.xlsx', header=1, keep_default_na=False, sheet_name='收货报表',
                                  # dtype={'收货手机': str},
                                  skipfooter=1, converters={'关联单号': lambda x: pd.to_numeric(x),
                                                            '运单号码': lambda x: pd.to_numeric(x),
                                                            '开票日期': lambda x: covert_datetime(x[:19],
                                                                                                  patten="%Y-%m-%d")})
    received_data.sort_values(by=['开票日期', '到达部门'], inplace=True, ascending=False)
    received_data['运单路由'] = received_data[['到达部门', '转货地']].apply(
        lambda row: '—'.join([row['到达部门'], row['转货地']]) if row['转货地'] != '' else row['到达部门'],
        axis=1)
    received_data = received_data.reindex(
        columns=['运单号码', '关联单号', '开票日期', '开票部门', '到达部门', '转货地', '运单路由', '当前部门',
                 '运单状态', '收货人',
                 '收货手机',
                 '收货地址', '货物名称', '包装', '件数', '重量', '体积', '代收款', '费用合计', '运费结算方式', '送货费',
                 '回单费', '公司备注', '客户备注'])

    received_data = received_data.reset_index(drop=True)
    received_data_sf = StyleFrame(received_data,
                                  styler_obj=Styler(border_type='none', fill_pattern_type='none', font='宋体',
                                                    protection=True, date_format='YY/MM/DD',
                                                    shrink_to_fit=True
                                                    ))
    # -inf - 500 白色 500 - 800 红色 800 - max 深红
    # noinspection PyTypeChecker
    received_data_sf.add_color_scale_conditional_formatting(columns_range=['重量'],
                                                            start_type=utils.conditional_formatting_types.num,
                                                            start_value=500, start_color=utils.colors.white,
                                                            mid_type=utils.conditional_formatting_types.num,
                                                            mid_value=800, mid_color=utils.colors.red,
                                                            end_type=utils.conditional_formatting_types.max,
                                                            end_value=None, end_color=utils.colors.dark_red)

    received_data_sf.apply_column_style(cols_to_style='重量',
                                        styler_obj=Styler(number_format=utils.number_formats.thousands_comma_sep,
                                                          border_type=utils.borders.hair, fill_pattern_type='none'),
                                        overwrite_default_style=False)

    received_data_sf.apply_column_style(cols_to_style='收货手机',
                                        styler_obj=Styler(number_format='000-0000-0000'),
                                        overwrite_default_style=False)

    # 布尔索引也可以充当indexes_to_style
    en_shi_index = received_data[['收货地址', '公司备注', '客户备注']].apply(highlight_en_shi, axis=1).tolist()
    received_data_sf.apply_style_by_indexes(
        indexes_to_style=en_shi_index,
        cols_to_style=['到达部门', '运单路由'],
        styler_obj=Styler(bg_color=utils.colors.green)
    )

    yi_chang_index = received_data[['到达部门', '收货地址', '公司备注', '客户备注']].apply(highlight_yi_chang,
                                                                                           axis=1).tolist()
    received_data_sf.apply_style_by_indexes(
        indexes_to_style=yi_chang_index,
        cols_to_style=['到达部门', '运单路由'],
        styler_obj=Styler(bg_color=utils.colors.purple)
    )

    with pd.ExcelWriter('resources/demo.xlsx', engine='openpyxl') as writer:
        received_data_sf.P_FACTOR = 1.2
        received_data_sf.A_FACTOR = 2
        received_data_sf.to_excel(writer, sheet_name='demo', index=False, columns_and_rows_to_freeze='A2',
                                  columns_to_hide=['关联单号', '到达部门', '转货地', '代收款', '费用合计'],
                                  best_fit=list(received_data.columns), row_to_add_filters=0,
                                  )  # allow_protection=True