# -*- coding: utf-8 -*-
import sys
import numpy as np
import pandas as pd
import datetime
from sqlalchemy import create_engine

reload(sys)
sys.setdefaultencoding('utf8')

'''
根据loan_id 展开赔付
'''

engine_new_transaction = create_engine(
    'mysql+mysqldb://yulong_rw:TouBStYwN8wkdxVt@172.16.3.201:3306/all_back_0630?charset=utf8',
    echo=True)

path = 'E:/linfang_17/'

year = 2017
time = '2017-07-01'

df_pay = pd.read_csv(path + 'loan_%d.csv' % year)
df_fund = pd.read_csv(path + 'fund_%d.csv' % year)
df_fund = df_fund.ix[~df_fund.funding_code.isin([0,1,2,3,4,5,210,270,310,320])]
if year == 2017:
    df_plan = pd.read_csv(path + 'plan_%d.csv' % year)
    df_acd = pd.read_csv(path + 'ACD_%d.csv' % year)
    # df_kaola = pd.read_csv(path + 'fund_kaola_%d.csv' % year)
    # # T+1处理
    # df_kaola.ix[df_kaola.fund_deadline >= time, 'fund_deadline'] = datetime.date(2016, 12, 31)
    # # T+2处理
    # df_kjxm = pd.read_csv(path + 'fund_kjmx_%d.csv' % year)
    # df_kjxm.ix[df_kjxm.fund_deadline >= time, 'fund_deadline'] = datetime.date(2016, 12, 31)
    df_fund = pd.concat([df_fund, df_plan, df_acd], ignore_index=True)
    # df_fund = pd.concat([df_fund, df_plan, df_acd, df_kaola, df_kjxm], ignore_index=True)
df_fund.fillna(0, inplace=True)
df_fund.ref_id = df_fund.ref_id.apply(lambda x: str(x).replace('.0', ''))
df_fund.ref_id = df_fund.ref_id.astype(int)
df_fund.funding_code = df_fund.funding_code.astype(int)
df_fund.term_no = df_fund.term_no.astype(int)
# 计算资金方应还的金额
df_fund.fund_deadline = pd.to_datetime(df_fund.fund_deadline).dt.date
df_fund_plan = df_fund.ix[df_fund.fund_deadline < datetime.date(2017, 7, 1)]
# 晋商 2016.12.30-12.31 还资金方为0
df_fund_plan.ix[(df_fund_plan.funding_code == 180) & (
df_fund_plan.fund_deadline >= datetime.date(2016, 12, 30)), 'fund_prin_inst'] = 0
df_fund_plan = df_fund_plan.groupby(['ref_id'])['fund_prin_inst'].sum().reset_index()

# 客户还款--还款时间2016年
df_online_repay = pd.read_csv(path + 'repay_online_%d.csv' % year)
df_offline_repay = pd.read_csv(path + 'repay_offline_%d.csv' % year)
df_repay = pd.concat([df_online_repay, df_offline_repay], ignore_index=True)
df_repay = df_repay.ix[df_repay.fund_code !=160]
# # 考拉
# df_repay.ix[(df_repay.fund_code == 170) & (df_repay.deadline >= '2017-01-02'), 'real_prin_inst'] = 0
# df_repay.ix[(df_repay.fund_code == 170) & (df_repay.deadline == '2017-01-01'), 'deadline'] = '2016-12-31'
# 橘子小樱米庄
# df_repay.ix[(df_repay.fund_code.isin([230, 240, 250, 260])) & (df_repay.deadline >= '2017-01-03'), 'real_prin_inst'] = 0
# df_repay.ix[
#     (df_repay.fund_code.isin([230, 240, 250, 260])) & (df_repay.deadline < '2017-01-03'), 'deadline'] = '2016-12-31'
# # 其他资金方--除去还资金方
# df_repay.ix[(df_repay.fund_code != 150) & (df_repay.deadline >= '2017-01-01'), 'real_prin_inst'] = 0
# 华融---需要使用还资金方时间

# 华融逾期还款
# df_hr_online_repay = pd.read_csv(path + 'hr_repay_online_%d.csv' % year)
# df_hr_offline_repay = pd.read_csv(path + 'hr_repay_offline_%d.csv' % year)
# # 考拉橘子小樱米庄 2017.1.1-2017.1.2
# df_kjxm_online_repay = pd.read_csv(path + 'kjmx_repay_online_2016.csv')
# df_kjxm_offline_repay = pd.read_csv(path + 'kjmx_repay_offline_2016.csv')
# df_kjxm_repay = pd.concat([df_kjxm_online_repay, df_kjxm_offline_repay], ignore_index=True)
# # 考拉
# df_kjxm_repay.ix[(df_kjxm_repay.fund_code == 170) & (df_kjxm_repay.repaid_at >= '2017-01-02'), 'real_prin_inst'] = 0
# df_kjxm_repay.ix[(df_kjxm_repay.fund_code == 170) & (df_kjxm_repay.deadline > '2017-01-02'), 'real_prin_inst'] = 0
# # 小嬴橘子米庄口袋理财T+2
# df_kjxm_repay.ix[(df_kjxm_repay.fund_code.isin([230, 240, 250, 260])) & (
# df_kjxm_repay.deadline >= '2017-01-03'), 'real_prin_inst'] = 0

# df_repay = pd.concat([df_repay], ignore_index=True)
# df_repay = pd.concat([df_repay, df_hr_online_repay, df_hr_offline_repay, df_kjxm_repay], ignore_index=True)
df_repay.term_no = df_repay.term_no.astype(int)
df_repay.ref_id = df_repay.ref_id.astype(int)
df_repay_other = df_repay.ix[~df_repay.fund_code.isin([150, 29, 50, 80, 130])]
df_repay_other.deadline = pd.to_datetime(df_repay_other.deadline).dt.date
df_repay_other.repaid_at = pd.to_datetime(df_repay_other.repaid_at).dt.date

print df_repay_other.ix[(df_repay_other.fund_code == 170)]['real_prin_inst'].sum()

df_repay_other = df_repay_other.groupby('ref_id')['real_prin_inst'].sum().reset_index()
# 使用资金方的deadline
df_repay_fund_deadline = df_repay.ix[df_repay.fund_code.isin([150, 29, 50, 80, 130])]
df_repay_fund_deadline = pd.merge(df_fund, df_repay_fund_deadline, on=['ref_id', 'term_no'], how='inner')
df_repay_fund_deadline = df_repay_fund_deadline.ix[df_repay_fund_deadline.fund_deadline < datetime.date(2017, 7, 1)]
df_repay_fund_deadline = df_repay_fund_deadline.groupby('ref_id')['real_prin_inst'].sum().reset_index()
df_repay_real = pd.concat([df_repay_other, df_repay_fund_deadline], ignore_index=True)

# 比对数据
df = pd.merge(df_pay, df_fund_plan, on='ref_id', how='left')
df = pd.merge(df, df_repay_real, on='ref_id', how='left')

df.fillna(0, inplace=True)
df['peifu'] = np.round(df.fund_prin_inst - df.real_prin_inst, 2)

df = df.ix[df['peifu'] != 0]
df=df.ix[(df['peifu'] > 0.5) | (df['peifu'] < -0.5)]
df.to_csv(path + 'peifu_%d.csv' % year, index=None)
