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


path='E:/peifu/peifu_v2_2015/'


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

sql_pay='''
select t1.ref_id,t2.class,t1.loan_id,funding_code,contract_loan_amount loan_amount,date(loan_paid_at) loan_paid_at 
from all_back_0630.loan_manifest t1 
left JOIN temp.loan_class t2 on t1.ref_id = t2.ref_id
where funding_code = 160 AND is_active in (1,-2)
'''
df_pay=pd.read_sql(sql_pay,engine_new_transaction)
df_pay.loan_paid_at=pd.to_datetime(df_pay.loan_paid_at).dt.date
df_pay.loan_id=df_pay.loan_id.astype(int)
sql_plan='''
select real_loan_id,term_no,date(deadline) deadline,principle+interest fund_prin_inst from all_back_0630.user_repayment_plan 
where fund_code = 160 
'''
df_plan=pd.read_sql(sql_plan,engine_new_transaction)
df_plan.deadline=pd.to_datetime(df_plan.deadline).dt.date
df_plan['fund_code'] = 160
df_plan.real_loan_id=df_plan.real_loan_id.astype(int)
df_plan=pd.merge(df_plan,df_pay[['loan_id','class']],left_on='real_loan_id',right_on='loan_id',how='left')

sql_repay='''
select loan_id,term_no,date(repaid_at) repaid_at,repay_principle+repay_intrest real_prin_inst,
qg_service_fee real_service_fee,penalty_fee real_punish 
from test_8_2.new_ms_transaction_with_term_details
'''
df_repay=pd.read_sql(sql_repay,engine_new_transaction)
df_repay.repaid_at = pd.to_datetime(df_repay.repaid_at).dt.date
df_repay.loan_id=df_repay.loan_id.astype(int)
df_repay=pd.merge(df_repay,df_pay[['loan_id','class']],on='loan_id',how='left')
df_repay.loan_id=df_repay.loan_id.astype(int)
df_repay.term_no=df_repay.term_no.astype(int)
df_plan.real_loan_id=df_plan.real_loan_id.astype(int)
df_plan.term_no=df_plan.term_no.astype(int)
df_repay = pd.merge(df_repay,df_plan[['real_loan_id','term_no','deadline']],left_on=['loan_id','term_no'],right_on=['real_loan_id','term_no'],how='left')
df_repay['fund_code']  = 160

df_plan=df_plan.groupby('real_loan_id')['fund_prin_inst'].sum().reset_index().rename(columns={'real_loan_id':'loan_id'})
df_plan.loan_id=df_plan.loan_id.astype(int)
df_repay=df_repay.groupby('loan_id')['real_prin_inst','real_service_fee','real_punish'].sum().reset_index()
df_repay.loan_id=df_repay.loan_id.astype(int)

df=pd.merge(df_pay,df_plan,on='loan_id',how='left').merge(df_repay,on='loan_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['peifu-punish-service_fee'] = np.round(df.fund_prin_inst - df.real_prin_inst - df.real_punish - 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 = df.ix[df['peifu'] > 0]
df.ix[df['peifu-punish'] < 0,'peifu-punish'] = 0
df.ix[df['peifu-punish-service_fee'] < 0,'peifu-punish-service_fee'] = 0
df_class=pd.read_csv(path+'loan_class.csv')
df=pd.merge(df,df_class,on='ref_id',how='left')
df.to_csv(path+'new_ms_peifu.csv',index=None)