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


'''
@author linfang
表头：风险等级 Loan id 资金方 放款时间 应还款日 放款金额 应还资金方本息 客户实还可用本息
'''


path='E:/peifu/peifu_v9/'

# #放款
# loan_start_time=datetime.date(2016,1,1)
# loan_end_time=datetime.date(2016,4,1)
# #deadline 时间
# deadline_start_time=datetime.date(2016,4,1)
# deadline_end_time=datetime.date(2017,7,1)
# #还款时间--期后赔付，包括 deadline 在 2016.4.1 之前的
# repay_start_time=datetime.date(2016,1,1)
# repay_end_time=datetime.date(2017,8,1)
# filename=u'peifu_季后_Q1_%d.csv'


# listen_time=[[datetime.date(2016,1,1),datetime.date(2016,4,1),datetime.date(2016,4,1),datetime.date(2017,8,1),datetime.date(2016,1,1),datetime.date(2017,8,1),u'peifu_2016季后_Q1_%d.csv'],
#              [datetime.date(2016, 4, 1), datetime.date(2016, 7, 1), datetime.date(2016,7, 1),datetime.date(2017,8,1), datetime.date(2016, 4, 1), datetime.date(2016, 7, 1), u'peifu_2016季后_Q2_%d.csv'],
#              [datetime.date(2016, 7, 1), datetime.date(2016, 10, 1), datetime.date(2016, 10, 1),datetime.date(2017,8,1), datetime.date(2016, 7, 1), datetime.date(2016, 10, 1), u'peifu_2016季后_Q3_%d.csv'],
#              [datetime.date(2016, 10, 1), datetime.date(2017, 1, 1), datetime.date(2017, 1, 1),datetime.date(2017,8,1), datetime.date(2016, 10, 1), datetime.date(2017, 1, 1), u'peifu_2016季后_Q4_%d.csv'],
#              [datetime.date(2017, 1, 1), datetime.date(2017, 4, 1), datetime.date(2017, 4, 1),datetime.date(2017,8,1), datetime.date(2017, 1, 1), datetime.date(2017, 4, 1), u'peifu_2017季后_Q1_%d.csv'],
#              [datetime.date(2017, 4, 1), datetime.date(2017, 7, 1), datetime.date(2017, 7, 1),datetime.date(2017,8,1), datetime.date(2017, 4, 1), datetime.date(2017, 7, 1), u'peifu_2017季后_Q2_%d.csv']]

listen_time=[[datetime.date(2016,1,1),datetime.date(2016,4,1),datetime.date(2016,4,1),datetime.date(2017,8,1),datetime.date(2016,1,1),datetime.date(2017,8,1),u'peifu_2016季后_Q1_%d.csv'],
             [datetime.date(2016, 4, 1), datetime.date(2016, 7, 1), datetime.date(2016,7, 1),datetime.date(2017,8,1), datetime.date(2016, 4, 1), datetime.date(2016, 7, 1), u'peifu_2016季后_Q2_%d.csv'],
             [datetime.date(2016, 7, 1), datetime.date(2016, 10, 1), datetime.date(2016, 10, 1),datetime.date(2017,8,1), datetime.date(2016, 7, 1), datetime.date(2016, 10, 1), u'peifu_2016季后_Q3_%d.csv'],
             [datetime.date(2016, 10, 1), datetime.date(2017, 1, 1), datetime.date(2017, 1, 1),datetime.date(2017,8,1), datetime.date(2016, 10, 1), datetime.date(2017, 1, 1), u'peifu_2016季后_Q4_%d.csv']
            ]

fund_sum = 0
repay_sum = 0
peifu_sum = 0
def handle(loan_start_time,loan_end_time,deadline_start_time,deadline_end_time,repay_start_time,repay_end_time,filename):
    global fund_sum
    global repay_sum
    global peifu_sum
    #放款
    df_pay=pd.read_csv(path+'loan.csv' )
    #扣除新马上,渤海
    df_pay=df_pay.ix[~df_pay.funding_code.isin([160,270,310,320])]
    #TODO 为了测试某一资金方
    # df_pay=df_pay.ix[df_pay.funding_code == 140]
    df_pay.loan_paid_at=pd.to_datetime(df_pay.loan_paid_at).dt.date
    df_pay.ref_id = df_pay.ref_id.astype(int)
    df_loan_class=pd.read_csv(path+'loan_class.csv')
    df_loan_class.ref_id=df_loan_class.ref_id.astype(int)
    df_pay=pd.merge(df_pay,df_loan_class,on='ref_id',how='left')
    df_pay.ref_id=df_pay.ref_id.astype(int)
    df_pay_sub=df_pay.ix[(df_pay.loan_paid_at >= loan_start_time) & (df_pay.loan_paid_at < loan_end_time)]
    # #填坑。。。 之前出的表，是 15+16年的放款，为了填补 11万的差距，把 15年放款的老马上加入，财务手工处理 17-11 = 6万的数据
    # df_pay=df_pay.ix[((df_pay.loan_year == loan_year) & (df_pay.funding_code != 29)) | (df_pay.funding_code == 29)]

    #还资金方
    df_fund=pd.read_csv(path+'fund.csv')
    df_fund.ref_id=df_fund.ref_id.astype(int)
    df_fund=df_fund.ix[df_fund.ref_id.isin(df_pay.ref_id)]
    df_fund=pd.merge(df_fund,df_pay[['ref_id','funding_code']],on='ref_id',how='left')
    df_fund.funding_code=df_fund.funding_code.astype(int)
    df_fund=df_fund.ix[~df_fund.funding_code.isin([1, 2, 3, 4, 5,210,270,310,320,50,80,130,70,135,141])]

    df_plan=pd.read_csv(path+'plan.csv')
    df_plan.ref_id=df_plan.ref_id.astype(int)
    df_plan = df_plan.ix[df_plan.ref_id.isin(df_pay.ref_id)]
    df_plan=pd.merge(df_plan,df_pay[['ref_id','funding_code']],on='ref_id',how='left')
    df_plan.funding_code=df_plan.funding_code.astype(int)
    df_plan=df_plan.ix[df_plan.funding_code.isin([1, 2, 3, 4, 5,210,270,310,320,70,135,141])]

    df_acd=pd.read_csv(path+'ACD.csv')
    df_acd.ref_id=df_acd.ref_id.astype(int)
    df_acd = df_acd.ix[df_acd.ref_id.isin(df_pay.ref_id)]
    df_acd=pd.merge(df_acd,df_pay[['ref_id','funding_code']],on='ref_id',how='left')
    df_acd.funding_code=df_acd.funding_code.astype(int)
    df_acd=df_acd.ix[df_acd.funding_code.isin([50,80,130])]

    df_fund_all=pd.concat([df_fund,df_plan,df_acd],ignore_index=True)
    df_fund_all.ref_id=df_fund_all.ref_id.astype(int)
    df_fund_all.term_no=df_fund_all.term_no.astype(int)
    df_fund_all.fund_deadline=pd.to_datetime(df_fund_all.fund_deadline).dt.date
    #指定放款时间段内的
    df_fund_all = df_fund_all.ix[df_fund_all.ref_id.isin(df_pay_sub.ref_id)]
    # #应还时间在 2017.1.1-2017.1.2 的 洋钱罐，口袋理财，小樱理财，橘子理---2016.12.31
    # df_fund_all.ix[(df_fund_all.funding_code.isin([140,230,240,250])) & (df_fund_all.fund_deadline <= datetime.date(2017,1,2)) & (df_fund_all.fund_deadline >= datetime.date(2017,1,1)),'fund_deadline'] = datetime.date(2016,12,31)
    # #考拉 2017.1.1---2016.12.31
    # df_fund_all.ix[(df_fund_all.funding_code.isin([170])) & (df_fund_all.fund_deadline == datetime.date(2017,1,1)),'fund_deadline'] = datetime.date(2016,12,31)
    #季后应还为0
    df_fund_all.ix[df_fund_all.fund_deadline < deadline_start_time,'fund_prin_inst'] = 0
    # 这样做的处理是因为 湖北销金有重复的问题
    df_fund_gp=df_fund_all.groupby(['ref_id','term_no'])['fund_prin_inst'].sum().reset_index()
    df_fund_all.drop_duplicates(['ref_id','term_no'],inplace=True)
    df_fund_all = pd.merge(df_fund_all[['ref_id','term_no','fund_deadline','fund_repaid_at']],df_fund_gp,on=['ref_id','term_no'],how='right')


    print '-----------1----'

    #客户实还
    df_repay_online=pd.read_csv(path+'repay_online.csv')
    df_repay_ali = pd.read_csv(path + 'repay_ali.csv')
    df_repay_bank = pd.read_csv(path + 'repay_bank.csv')
    df_repay = pd.concat([df_repay_online, df_repay_ali, df_repay_bank], ignore_index=True)
    # df_repay_offline = pd.read_csv(path + 'repay_offline.csv')
    # df_repay = pd.concat([df_repay_online, df_repay_offline], 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=df_repay.ix[df_repay.ref_id.isin(df_pay_sub.ref_id)]
    df_repay.repaid_at=pd.to_datetime(df_repay.repaid_at).dt.date
    df_repay=pd.merge(df_repay,df_pay[['ref_id','funding_code']],on='ref_id',how='left')
    df_repay=pd.merge(df_repay,df_fund_all[['ref_id','term_no','fund_deadline']],on=['ref_id','term_no'],how='left')
    df_repay.fund_deadline=pd.to_datetime(df_repay.fund_deadline).dt.date
    df_repay.funding_code=df_repay.funding_code.astype(int)
    # #应还时间在 2017.1.1-2017.1.2 的 洋钱罐，口袋理财，小樱理财，橘子理财
    # df_repay.ix[(df_repay.funding_code.isin([140,230,240,250]))& (df_repay.repaid_at >= datetime.date(2017,1,1)) & (df_repay.repaid_at <= datetime.date(2017,1,2)),'repaid_at'] = datetime.date(2016,12,31)
    # #考拉 2017.1.1
    # df_repay.ix[(df_repay.funding_code.isin([170]))& (df_repay.repaid_at == datetime.date(2017,1,1)),'repaid_at'] = datetime.date(2016,12,31)
    # #华融 2017.1.1-2017.1.10 逾期还款 为0
    # df_repay.ix[(df_repay.funding_code == 150) & (df_repay.fund_deadline < datetime.date(2017,1,1)) & (df_repay.repaid_at >= datetime.date(2017,1,1)) & (df_repay.repaid_at < datetime.date(2017,1,11)),'repaid_at'] = datetime.date(2016,12,31)
    #提取放款时间--季后赔付，注意逾期还款
    df_repay.ix[(df_repay.fund_deadline < deadline_start_time) & (df_repay.repaid_at < loan_end_time),'real_prin_inst'] = 0
    df_repay=df_repay.groupby(['ref_id','term_no'])['real_prin_inst'].sum().reset_index()
    print '-----------------2---------'

    #比对数据
    df=pd.merge(df_pay_sub[['ref_id','class','funding_code','loan_id','loan_paid_at','loan_amount']],df_fund_all[['ref_id','term_no','fund_deadline','fund_prin_inst']],on='ref_id',how='right')
    df=pd.merge(df,df_repay,on=['ref_id','term_no'],how='left')
    df.fillna(0,inplace=True)
    fund_sum = df.fund_prin_inst.sum() + fund_sum
    repay_sum = df.real_prin_inst.sum() + repay_sum
    df = df.ix[(df.fund_prin_inst > 0) | (df.real_prin_inst > 0)]
    df['peifu']=np.round(df.fund_prin_inst - df.real_prin_inst,2)
    peifu_sum = df.peifu.sum() + peifu_sum
    # 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)]

    for i in range(0,len(df),500000):
        df[i:i+500000].to_csv(path + filename % (i), index=None)


for listen_t in listen_time:
    loan_start_time = listen_t[0]
    loan_end_time = listen_t[1]
    deadline_start_time = listen_t[2]
    deadline_end_time = listen_t[3]
    repay_start_time = listen_t[4]
    repay_end_time = listen_t[5]
    filename =  listen_t[6]
    handle(loan_start_time,loan_end_time,deadline_start_time,deadline_end_time,repay_start_time,repay_end_time,filename)

print "资金方应还总和 %s" % fund_sum
print "客户实还总和  %s" % repay_sum
print "赔付  %s" % peifu_sum