# -*- coding: utf-8 -*-
"""
-------------------------------------------------
# @Project  : pythonProject
# @File     : demo3
# @Date     : 2024/3/5 16:00
# @Author   : 徐胜虎
# @Email    : 1813366784@qq.com
# @Software : PyCharm
-------------------------------------------------
"""
##########################验算运单号码
import pandas as pd

data = pd.read_excel('resources/template/账单登记表.xlsx', sheet_name='临时表', dtype={'货物运单号码': str})
# 代收+提付  净运费 代收款 提付合计
data['货物运单号码'].str.split('|').explode()

##########################计算到到付总金额
import numpy as np
import pandas as pd
from pandas import DataFrame

data = pd.read_excel('resources/template/账单登记表.xlsx',
                     sheet_name='临时表')
# 代收+提付  净运费 代收款 提付合计
data['货物运单号码'] = data['货物运单号码'].str.split('|')

data = data.explode('货物运单号码').dropna(axis=0, subset=['货物运单号码'])
data['货物运单号码'] = data['货物运单号码'].astype(np.int64)

data_2 = pd.read_excel('resources/temp/收货报表.xlsx', sheet_name='收货报表', header=1, skipfooter=1,
                       converters={'运单号码': lambda x: pd.to_numeric(x)})
data_2['到付合计'] = data_2['提付合计'] + data_2['代收款']

all_data = pd.merge(data, data_2, left_on='货物运单号码', right_on='运单号码', how='left')
all_data.info()
# all_data['开票净运费']
#
# all_data['提付合计'] + all_data['代收款']
#
# all_data[['运单号码', '到付合计']]


temp = DataFrame()
temp['开票净运费'] = all_data.groupby(['序号'])['净运费'].agg('sum')
temp['到付总金额'] = all_data.groupby(['序号'])['到付合计'].agg('sum')
# 7月份每个用户每个时间点领取优惠券次数

# all_data.to_excel()

with pd.ExcelWriter('resources/template/账单登记表.xlsx', mode='a', if_sheet_exists='replace',
                    engine='openpyxl') as writer:
    temp.to_excel(writer, index=True, header=True,
                  sheet_name='test')

##########################计算到分摊金额
# 带记忆的apply函数用法
cumsum_ = []


def cal_allocated_expense(row):
    global cumsum_
    upper = row['调车费用'] - sum(cumsum_) - row['每次调车序号']
    lower = 1
    if row['每次调车序号'] == 0:
        re_2 = int(row['调车费用'] - sum(cumsum_))
        re_2 = re_2 if re_2 <= upper else re_2 - 1
        re_2 = re_2 if re_2 >= lower else re_2 + 1
        cumsum_.clear()
        return re_2
    else:
        re_1 = int(row['调车费用'] * row['分摊比例'])
        re_1 = re_1 if re_1 <= upper else re_1 - 1
        re_1 = re_1 if re_1 >= lower else re_1 + 1
        cumsum_.append(re_1)
        return re_1


import pandas as pd

temp = pd.read_excel('resources/template/账单登记表.xlsx', sheet_name='调车送货明细总表', header=0)
temp['序号'].ffill(inplace=True)
temp['调车费用'].ffill(inplace=True)
temp['调车费用'] = temp['调车费用'].astype(int)
temp['开票费用每次调车汇总'] = temp.groupby(by='序号')['开票费用'].transform('sum')
temp['分摊比例'] = temp['开票费用'] / temp['开票费用每次调车汇总']
temp['每次调车序号'] = temp.groupby(by='序号', as_index=False).cumcount(ascending=False)
temp['分摊实际金额'] = temp[['调车费用', '每次调车序号', '分摊比例']].apply(cal_allocated_expense, axis=1)
with pd.ExcelWriter('resources/template/账单登记表.xlsx', mode='a', if_sheet_exists='overlay',
                    engine='openpyxl') as writer:
    temp.to_excel(writer, startcol=15, startrow=1, index=False,
                  header=False, columns=['分摊实际金额'], sheet_name='调车送货明细总表')

temp = pd.read_excel('resources/template/账单登记表.xlsx', sheet_name='调车提货明细总表', header=0)
temp['序号'].ffill(inplace=True)
temp['调车费用'].ffill(inplace=True)
temp['调车费用'] = temp['调车费用'].astype(int)
temp['开票费用每次调车汇总'] = temp.groupby(by='序号')['开票费用'].transform('sum')
temp['分摊比例'] = temp['开票费用'] / temp['开票费用每次调车汇总']
temp['每次调车序号'] = temp.groupby(by='序号', as_index=False).cumcount(ascending=False)
temp['分摊实际金额'] = temp[['调车费用', '每次调车序号', '分摊比例']].apply(cal_allocated_expense, axis=1)

with pd.ExcelWriter('resources/template/账单登记表.xlsx', mode='a', if_sheet_exists='overlay',
                    engine='openpyxl') as writer:
    temp.to_excel(writer, startcol=15, startrow=1, index=False,
                  header=False, columns=['分摊实际金额'], sheet_name='调车提货明细总表')

#############批量按照映射关系修改文件名
import shutil
import pandas as pd
import os

temp = pd.read_excel('resources/template/账单登记表.xlsx', sheet_name='Sheet5', header=0, usecols=(0, 1), na_values='',
                     dtype={'原序号': str, '新序号': str})
base_path = os.path.join(os.getcwd(), r'resources\test')
temp.fillna('', inplace=True)

for _, j in temp[['原序号', '新序号']].iterrows():
    old_file_name = j['原序号']
    if old_file_name == '':
        continue
    new_file_name = j['新序号']
    old_name = os.path.join(base_path, f'{old_file_name}.jpg')
    new_name = os.path.join(base_path, 'temporary', f'{new_file_name}.jpg')
    print(old_name, '============>', new_name)

    try:
        os.renames(old_name, new_name)  # 用os模块中的rename方法对文件改名
    # except ExceptionGroup:
    # except Exception:
    except(FileNotFoundError, FileExistsError):
        print('异常')
shutil.move(os.path.join(base_path, 'temporary'), os.path.join(os.getcwd(), r'resources\temporary'))