# -*- coding: utf-8 -*-
import sys
import numpy as np
import pandas as pd
from sqlalchemy import create_engine
reload(sys)
sys.setdefaultencoding('utf8')


'''
根据loan_id 展开赔付
注意：首先根据 导出数据_2016.sql 执行将数据导出到对应的文件中
涉及到的文件： loan_{year}.csv  fund_{year}.csv plan_{year}.csv 
repay_online_{year}.csv repay_offline_{year}.csv 
'''



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

path='E:/peifu/punish_2015/'


year=2015
time = '2016-01-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])]
df_plan=pd.read_csv(path+'plan_%d.csv' % year)
df_fund=pd.concat([df_fund,df_plan],ignore_index=True)
df_fund.fillna(0,inplace=True)
df_fund.ref_id=df_fund.ref_id.astype(int)
df_fund.term_no = df_fund.term_no.astype(int)
#计算资金方应还的金额,以loan_id 为基准
df_fund_plan=df_fund.ix[df_fund.fund_deadline < time]
df_fund_plan=df_fund_plan.groupby(['ref_id'])['fund_prin_inst'].sum().reset_index()

#客户还款
df_online_repay=pd.read_csv(path+'repay_online_%d.csv' % year)
df_online_repay.fillna(0,inplace=True)
df_offline_repay=pd.read_csv(path+'repay_offline_%d.csv' % year)
df_offline_repay.fillna(0,inplace=True)
df_repay=pd.concat([df_online_repay,df_offline_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=df_repay_other.ix[df_repay_other.deadline < time]
df_repay_other=df_repay_other.groupby('ref_id')['real_prin_inst','real_punish','real_service_fee'].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 < '2016-01-01']
df_repay_fund_deadline=df_repay_fund_deadline.groupby('ref_id')['real_prin_inst','real_punish','real_service_fee'].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['peifu-punish']=np.round(df.fund_prin_inst - df.real_prin_inst - df.real_punish,2)
df['peifu-service_fee']=np.round(df.fund_prin_inst - df.real_prin_inst - df.real_service_fee,2)
df=df.ix[(df['peifu'] != 0) | (df['peifu-punish'] !=0) | (df['peifu-service_fee'] != 0)]
df=df.ix[(df['peifu'] > 0.5) | (df['peifu'] < -0.5)]
df.to_csv(path+'peifu_punish_%d.csv' % year,index=None)
