# -*- coding: utf-8 -*-
"""
Created on Sun Apr  4 21:24:41 2021

@author: qjwx
"""

import pandas as pd
import os

path = r'C:\Users\Administrator\Desktop\内部往来'
os.chdir(path)

name_dict = {
    '省水利水电公司本部':'云南省水利水电工程有限公司',
    '三公司机关':'云南省水利水电工程有限公司第三公司',
    '一公司机关':'云南省水利水电工程有限公司第一分公司',
    '大理分公司机关':'云南省水利水电工程有限公司大理分公司',
    '贵州分公司机关':'云南省水利水电工程有限公司贵州分公司',
    '路桥分公司机关':'云南省水利水电工程有限公司路桥分公司',
    '服务分公司机关':'云南省水利水电工程有限公司服务分公司',
    }

same_guest = {
        '易门县铜厂乡2021年以工代赈示范项目（里芬村进村道路路面硬化工程）':'省水利水电公司直属项目部-易门县铜厂乡2021年以工代赈示范项目(里芬村进村道路路面硬化工程)',
        '弥勒迎春（小黑洞、中哨、者圭等18座小型水库）':'中哨小黑洞项目',
        '普洱市西盟县农村供水保障专项行动项目投建联合（PC）招标':'云南省水利水电工程有限公司-普洱市西盟县农村供水保障专项行动员目投建联合(PC)招标',
        '陆良县永清河水库除险加固工程C1标：大坝防渗及坝面整修工程':'云南省水利水电工程有限公司-陆良县永清河水库除险加固工程C1标:大坝防渗及坝面整修工程',
        '宣威市2021年第一批巩固拓展脱贫攻坚成果项目抗旱应急工程':'云南省水利水电工程有限公司-宣威市2021年第一批巩固拓展脱贫攻坚成果项目抗旱应急工程',
        '临翔区南汀河水生态文明建设项目（一期）设计采购施工总承包（EPC)':'云南省水利水电工程有限公司-临翔区南汀河水生态文明建设项目（一期）设计采购施工总承包（EPC)',
        '宣威通南铺水库':'宣威通南铺水库项目',
        '施甸岚峰水库灌浆及隧洞工程':'施甸岚峰水库灌浆及隧洞',
        '云南省临沧市临翔区弯子水库枢纽工程':'云南省临沧市临翔区弯子水库水库枢纽工程',
        '文山州德厚水库灌浆平顶洞':'文山州德厚水库灌浆平洞工程',
        '易门县团结水库':'易门团结水库项目部',
        '易门县苗茂水库进库道路硬化及溢洪道、输水隧洞启闭机控制用电项目':'省水利水电公司直属项目部易门县苗茂水库进库道路硬化及溢洪道、输水隧洞启闭机控制用电项目',
        '临翔区大营铺水库工程':'省水利水电公司直属项目部-临翔区大营铺水库工程',
        '易门县大谷厂水库除险加固工程-三标段（施工招标）':'云南省水利水电工程有限公司-易门县大谷厂水库除险加固工程—三标段(施工招标)',
        '富源县迤启水库':'省水利水电公司直属项目部-富源县迤启水库',
        '云南省曲靖市陆良县河道综合整治工程PPP项目':'省水利水电公司直属项目部-云南省曲靖市陆良县河道综合整治工程PPP项目',
        '临翔区第四净水处理厂及配套管网工程设计施工总承包':'省水利水电公司直属项目部-临翔区第四净水处理厂及配套管网工程设计施工总承包',
        '省水利水电公司直属项目部-临翔区第四净水处理厂及配套管网工程设计施工总承包（新）':'省水利水电公司直属项目部-临翔区第四净水处理厂及配套管网工程设计施工总承包',
        '通海县琉璃河水库扩建工程施工':'省水利水电公司直属项目部-通海县琉璃河水库扩建工程施工',
        '临翔区昔归小镇引水入湾工程设计施工总承包':'云南省水利水电工程有限公司-临翔区昔归小镇引水入湾工程设计施工总承包',
        '蒙自市蚂蚱冲水库工程第一标段(枢纽工程)':'蒙自市蚂蚱冲水库工程项目经理部',
        '屏边县红旗水库除险加固工程':'省水利水电公司直属项目部-屏边县红旗水库除险加固工程',
        '新平县洋发城水库扩建工程一标枢纽主体工程施工':'省水利水电公司直属项目部-新平县洋发城水库扩建工程一标枢纽主体工程施工',
        '宣威市偏桥水库除险加固工程':'云南省水利水电工程有限公司-宣威市偏桥水库除险加固工程',
        '云南省玉溪市易门县岔河水库连通工程PPP项目':'省水利水电公司直属项目部云南省玉溪市易门县岔河水库连通工程PPP项目',
        '屏边县城观音河文化桥段综合治理工程':'省水利水电公司直属项目部-屏边县城观音河文化桥段综合治理工程',
        '富源县阿汪水库工程第五标段：输水兼导流隧洞工程':'省水利水电公司直属项目部富源县阿汪水库工程第五标段:输水兼导流隧洞工程',
        '临翔区大桥坡水库工程施工总承包':'省水利水电公司直属项目部-临翔区大桥坡水库工程施工总承包',
        '宣威市新屯水库至庙山水厂抗旱应急调水工程':'云南省水利水电工程有限公司宣威市新屯水库至庙山水厂抗旱应急调水工程',
        '屏边县城区抗旱应急供水工程':'省水利水电公司直属项目部-屏边县城区抗旱应急供水工程',
        '临翔区曼豪箐水库工程':'省水利水电公司直属项目部-临翔区曼豪箐水库工程',
        '曲靖市麒麟区大黑箐水库帷幕灌浆工程':'省水利水电公司直属项目部-曲靖市麒麟区大黑箐水库帷幕灌浆工程',
        '曲靖市麒麟区大黑箐水库勘探检测工程':'省水利水电公司直属项目部-曲靖市麒麟区大黑箐水库勘探检测工程',
        '芒市等嘎':'芒市等噶项目部',
        '屏边滴水苗城（苗族）特色小镇牧羊河生态修复综合整治（宝镜湖坝体、碧玉湖坝体、清溪湖坝体、双龙湖坝体、月牙湖坝体）建设项目设计-采购-施工总承包(EPC新)':'牧羊河水生态治理项目',
        '屏边县新华水库工程':'云南省水利水电工程有限公司-屏边县新华水库工程',
        '会泽县白马箐水库第一标段：拦河坝、溢洪道工程':'会泽县白马箐水库第一标段:拦河坝、溢洪道工程',
        '云南省水利水电工程有限公司-易门县大谷厂水库除险加固工程—三标段(施工招标)':'云南省水利水电工程有限公司-易门县大谷厂水库除险加固工程-三标段（施工招标）',
        '云南省水利水电工程有限公司宣威市新屯水库至庙山水厂抗旱应急调水工程':'云南省水利水电工程有限公司-宣威市新屯水库至庙山水厂抗旱应急调水工程',
        '云南省水利水电工程有限公司-易门县大谷厂水库除险加固工程—三标段(施工招标)':'云南省水利水电工程有限公司-易门县大谷厂水库除险加固工程-三标段（施工招标）',
        '施甸县五座水库PPP项目栗树水库':'省水利水电公司直属项目部施甸县栗树水库工程',
        '云南省水利水电工程有限公司-临翔区南汀河水生态文明建设项目（一期）设计采购施工总承包（EPC）':'云南省水利水电工程有限公司-临翔区南汀河生态文明建设项目（一期）设计采购施工总承包（EPC）',
        '云南省水利水电工程有限公司宣威市新屯水库至庙山水厂抗旱应急调水工程':'云南省水利水电工程有限公司-宣威市新屯水库至庙山水厂抗旱应急调水工程',
        '云南省水利水电工程有限公司-临翔区南汀河水生态文明建设项目（一期）设计采购施工总承包（EPC）':'云南省水利水电工程有限公司-临翔区南汀河生态文明建设项目（一期）设计采购施工总承包（EPC）',
        }

diff_guest = {
    '云南省水利水电工程有限公司宣威市新屯水库至庙山水厂抗旱应急调水工程':'云南省水利水电工程有限公司-宣威市新屯水库至庙山水厂抗旱应急调水工程',
    }


diff_project = {
    '腾冲市向阳水库工程第三标段：工程设备采购及施工':'屏边县大绿树水库工程第三标段：水库枢纽工程施工标',
    '新平(戛洒)至元江(红光)段高速公路八分部专业分包':'富源县岔河水库进场公路工程项目部',
    '省水利水电公司直属项目部-临翔区曼豪箐水库工程(不用)':'省水利水电公司直属项目部-临翔区曼豪箐水库工程',
    '云县晓街河水库工程灌区渠道隧洞工程（11标）':'云县晓街河水库工程灌区渠道隧洞工程(11标)',
    '省水利水电公司直属项目部-临翔区第四净水处理厂及配套管网工程设计施工总承包（不用）':'省水利水电公司直属项目部-临翔区第四净水处理厂及配套管网工程设计施工总承包',
    '滇中引水工程大理州鲁地拉水电站水资源综合利用配套二期工程（第八直属项目经理部）':'云南省水利水电工程有限公司-滇中引水大理州鲁地拉水电站水资源综合利用二期工程（第八直属部项目经理部）',
    '省水利水电公司直属项目部-临翔区大营铺水库工程（不用）':'省水利水电公司直属项目部-临翔区大营铺水库工程',
    '省水利水电公司直属项目部-临翔区大桥坡水库工程施工总承包（不用）':'省水利水电公司直属项目部-临翔区大桥坡水库工程施工总承包',
    '望谟县桑郎水库工程（枢纽工程SG1）解除施工合同未完成部分施工工程':'望谟县桑郎水库工程(枢纽工程SG1)解除施工合同未完成部分施工工程',
    '务坪工地':'务坪工地项目部',
    '宣威市红石岩水库新增渗流观测孔工程':'云南省水利水电工程有限公司-宣威市红石岩水库新增渗流观测孔工程',
    '永胜县水系连通及农村水系综合整治试点项目施工招标':'省水利水电公司直属项目部-永胜县水系连通及农村水系综合整治试点项目施工招标',
    '芒市等薄水库工程PPP项目':'省水利水电公司直属项目部-芒市等薄水库工程PPP项目',
    '云南省保山市施甸县三块石水库连通工程一标段：施工招标（二次招标）':'云南省水利水电工程有限公司-云南省保山市施甸县三块石水库连通工程一标段:施工招标',
    '墨江县小勐连水库工程（第7标段：大坝及溢洪道）':'省水利水电公司直属项目部-墨江县小勐连水库工程(第7标段:大坝及溢洪道)',
    '陆良县中三路改扩建工程PPP项目':'省水利水电公司直属项目部-陆良县中三路改扩建工程PPP项目',
    '屏边县城乡供水一体化项目':'云南省水利水电工程有限公司-屏边县城乡供水一体化项目',
    }

# 内部往来输入表格必须包含以下五个字段：
# 摘要    项目辅助核算名称    客商辅助核算名称    方向    期末余额本币

# 打开相关客商名单
df_guest = pd.read_excel('项目清单.xls')

for key, value in name_dict.items():
    df_guest['项目'] = df_guest['项目'].map(lambda x:x.replace(key, value))
for key, value in same_guest.items():
    df_guest['项目'] = df_guest['项目'].map(lambda x:x.replace(key, value))

guest_list = list(df_guest['项目'])

# 打开原始数据
df_source =pd.read_excel('内部往来最终.xls',names=['A', 'B', 'C', 'D', 'E'])
df_source = df_source[~(df_source['B'].isnull())]
df_source = df_source[~(df_source['A']=='主体帐簿累计')]
start = []
end = []
subject = []
for ind,item in zip(df_source.index,df_source['A']):
    if '对象：' == item:
        start.append(ind)
        subject.append(df_source.loc[ind,'B'].split('\\')[-1].replace('】',''))

    elif '核算单位：' == item:
        end.append(ind)

df_tmp_list = []
for i,j,k in zip(start, end, subject):
    df_tmp = pd.DataFrame(columns = ['科目', '项目辅助核算名称', '客商辅助核算名称', '方向', '金额'])
    df_tmp['项目辅助核算名称'] = list(df_source.loc[i+3:j-1,'B'])
    df_tmp['客商辅助核算名称'] = list(df_source.loc[i+3:j-1,'C'])
    df_tmp['方向'] = list(df_source.loc[i+3:j-1,'D'])
    df_tmp['金额'] = list(df_source.loc[i+3:j-1,'E'])
    df_tmp['科目'] = k
    df_tmp_list.append(df_tmp)

df_res = pd.concat(df_tmp_list, axis = 0)
for key, value in name_dict.items():
    df_res['项目辅助核算名称'] = df_res['项目辅助核算名称'].map(lambda x:x.replace(key, value))
    df_res['客商辅助核算名称'] = df_res['客商辅助核算名称'].map(lambda x:x.replace(key, value))

for key, value in same_guest.items():
    df_res['项目辅助核算名称'] = df_res['项目辅助核算名称'].map(lambda x:x.replace(key, value))
for key, value in diff_guest.items():
    df_res['客商辅助核算名称'] = df_res['客商辅助核算名称'].map(lambda x:x.replace(key, value))


df_res['客商辅助核算名称'] = df_res['客商辅助核算名称'].map(lambda x:x.replace(' ',''))
df_res['项目辅助核算名称'] = df_res['项目辅助核算名称'].map(lambda x:x.replace(':','：'))
df_res['客商辅助核算名称'] = df_res['客商辅助核算名称'].map(lambda x:x.replace(':','：'))
df_res['项目辅助核算名称'] = df_res['项目辅助核算名称'].map(lambda x:x.replace('(','（'))
df_res['客商辅助核算名称'] = df_res['客商辅助核算名称'].map(lambda x:x.replace('(','（'))
df_res['项目辅助核算名称'] = df_res['项目辅助核算名称'].map(lambda x:x.replace(')','）'))
df_res['客商辅助核算名称'] = df_res['客商辅助核算名称'].map(lambda x:x.replace(')','）'))

df_res = df_res[df_res['方向']!= '平']


df_res = df_res[~df_res['客商辅助核算名称'].isnull()]

def sort_names(a,b,c,d):  # 定义计算函数
    li = sorted([b,c])
    return a + '_' + li[0] + '_' + li[1] + '_' + str(d)
df_res['key'] = df_res.apply(lambda x:sort_names(x.科目, x.项目辅助核算名称 , x.客商辅助核算名称, x.金额), axis = 1)

df_res.sort_values(by = ['科目','方向', '金额'],ascending = [True,True,True],inplace=True)

df_res['guest'] = df_res['项目辅助核算名称'] + '#' + df_res['客商辅助核算名称']
df_res['guest'] = df_res['guest'].map(lambda x:sorted(x.split('#'))[0]+'#'+sorted(x.split('#'))[1])
df_res = df_res[~df_res['金额'].isnull()]
df_res.sort_values(by = ['科目','金额','guest','方向'],ascending = [True,True,True,True],inplace=True)
df_res = df_res[df_res['项目辅助核算名称'].isin(guest_list)|df_res['客商辅助核算名称'].isin(guest_list)]

for key, value in diff_project.items():
    df_res['项目辅助核算名称'] = df_res['项目辅助核算名称'].map(lambda x:x.replace(key, value))


def check_if_balance(x,y):
    if x == y:
        return '平'
    else:
        return '不平'
df_res['校验结果'] = ''

df_res.reset_index(inplace =True)
df_res.drop('index',axis =1,inplace =True)
for i in range(1,len(df_res)):
    if i <= len(df_res)-2:
        if df_res.loc[i,'guest'] == df_res.loc[i+1,'guest'] and df_res.loc[i,'方向'] == '借' and df_res.loc[i+1,'方向'] == '贷' and df_res.loc[i,'金额']== df_res.loc[i+1,'金额']:
            df_res.loc[i,'校验结果'] = '平'
        elif df_res.loc[i,'guest'] == df_res.loc[i-1,'guest'] and df_res.loc[i,'方向'] == '贷' and df_res.loc[i-1,'方向'] == '借' and df_res.loc[i,'金额']== df_res.loc[i-1,'金额']:
            df_res.loc[i,'校验结果'] = '平'
        else:
            df_res.loc[i,'校验结果'] = '不平'
    else:
        if df_res.loc[i,'guest'] == df_res.loc[i-1,'guest'] and df_res.loc[i,'方向'] == '借' and df_res.loc[i-1,'方向'] == '贷' and df_res.loc[i,'金额']== df_res.loc[i-1,'金额']:
            df_res.loc[i,'校验结果'] = '平'
        elif df_res.loc[i,'guest'] == df_res.loc[i-1,'guest'] and df_res.loc[i,'方向'] == '贷' and df_res.loc[i-1,'方向'] == '借' and df_res.loc[i,'金额']== df_res.loc[i-1,'金额']:
            df_res.loc[i,'校验结果'] = '平'
        else:
            df_res.loc[i,'校验结果'] = '不平'

#df_res = df_res[['科目', '项目辅助核算名称', '客商辅助核算名称', '方向', '金额', 'guest', '校验结果']]

with pd.ExcelWriter('内部往来核对结果.xlsx') as f:
    df_res.to_excel(f, index = False)