# -*- 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 loan_id,sum(principle+interest) fund_prin_inst from all_back_0630.user_repayment_plan
where fund_code = 160  GROUP BY real_loan_id
'''
df_plan=pd.read_sql(sql_plan,engine_new_transaction)
df_plan['fund_code'] = 160
df_plan.loan_id=df_plan.loan_id.astype(int)
df_plan=pd.merge(df_plan,df_pay[['loan_id','class']],on='loan_id',how='left')

sql_repay='''
SELECT loan_id,sum(repay_principle+repay_intrest) real_prin_inst,sum(service_fee) real_service_fee, sum(penalty_fee) real_punish FROM new_ms_transaction GROUP BY loan_id
'''
df_repay=pd.read_sql(sql_repay,engine_new_transaction)
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_plan.loan_id=df_plan.loan_id.astype(int)
df_repay['fund_code']  = 160

df=pd.merge(df_pay,df_plan[['loan_id','fund_prin_inst']],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_no_term_peifu.csv',index=None)