# -*- encoding: utf8 -*-
import sys
import datetime
import numpy as np
import math
import pandas as pd
from dateutil.relativedelta import relativedelta
from sqlalchemy import create_engine

reload(sys)
sys.setdefaultencoding("utf-8")
pd.options.mode.chained_assignment = None


'''
@author linfang
去哪儿还款计划拆分---适合 trans_time >= 2017 年的
'''

engine_qunaer = create_engine('mysql+mysqldb://linfang.wang:#jkl3453YUGuo99@172.16.3.201:3306/qunaer_new?charset=utf8', echo=False).connect()

engine_djy = create_engine('mysql+mysqldb://wanglinfang_data_r:uh90888n7y8ijk8886@192.168.4.6:8066/dujiangyan?charset=utf8', echo=False).connect()
# 取小数点 前两位
def get_2_flo(num):
    begin = str(num).find('.')
    if begin == -1:
        return round(begin, 2)
    return float(str(num)[:begin + 3])

# 小数向上取数
def get_2_ceil(num):
    begin = str(num).find('.')
    if begin == -1 :
        return round(begin, 2)
    else:
        if str(num).__len__() - begin > 3:
            return math.ceil(num*100) / 100
        else:
            return float(str(num)[:begin + 3])

#四舍五入
def get_2_round(num):
    begin = str(num).find('.')
    if begin == -1:
        return round(begin, 2)
    else:
        if str(num).__len__() - begin > 3:
            dot_3=int(str(num)[begin+3])
            if dot_3 >= 5:
                return get_2_flo(num)+0.01
            else:
                return get_2_flo(num)
        else:
            return float(str(num)[:begin + 3])

#步骤1  首先 放款订单生成还款计划
# product_no , term,deadline,principle status = 0(init)
# 生成还款计划表 repayment_plan_2017
def step_1(year,month,product_no='',insert_db=False):
    start_time = datetime.datetime(year, month, 1, 0, 0, 0)
    end_time = start_time + relativedelta(months=+1)
    sql_pay = '''
        select fund_code,product_no,rate,loan_amount,stages,loan_time from qunaer_new.pay_detail
        where status = 1 and trans_time >= '%s' and trans_time < '%s'
    ''' % (start_time, end_time)

    if product_no.__len__() > 0 :
        sql_pay = '''
        select fund_code,product_no,rate,loan_amount,stages,loan_time from qunaer_new.pay_detail
        where product_no='%s' 
        ''' % product_no
    df_pay = pd.read_sql(sql_pay,engine_qunaer,chunksize=5000)
    for df in df_pay:
        df.loan_time = pd.to_datetime(df.loan_time).dt.date
        list_tmp=[]
        # 等本等息
        for index,row in df.iterrows():
            fund_code=int(row['fund_code'])
            stages=int(row['stages'])
            avg_principle = get_2_flo(row['loan_amount'] / stages)
            first_principle = round(row['loan_amount'] - avg_principle *(stages - 1),2)
            # 不使用账单文件中的应还服务费，这个是去哪儿进行了处理的数据
            # 初始化还款计划
            fee_amount = stages * row['loan_amount'] * row['rate']
            avg_fee_amount = get_2_flo(row['loan_amount'] * row['rate'])
            first_fee_amount = round(get_2_ceil(fee_amount) - avg_fee_amount * (stages - 1),2)
            for i in range(1,stages+1,1):
                if stages == 1:
                    #不分期订单 30天免息
                    deadline = row['loan_time'] + relativedelta(days=+30)
                else:
                    #分期订单，自然月
                    deadline = row['loan_time'] + relativedelta(months=+i)
                if i == 1:
                    list_tmp.append([fund_code,row['product_no'],row['rate'],i,deadline,first_principle,first_fee_amount,1])
                else:
                    list_tmp.append([fund_code,row['product_no'], row['rate'], i, deadline, avg_principle, avg_fee_amount, 1])
        df_tmp = pd.DataFrame(list_tmp,columns=['fund_code','product_no','rate','current_stage_no','deadline','principle','fee_amount','status'])
        if insert_db:
            df_tmp.to_sql('repayment_plan_2017',engine_qunaer,index=None,chunksize=10000,if_exists='append')


#步骤1.1 针对步骤1 中的 还款计划中的deadline 进行同步都江堰
def step_1_1(year,month,product_no='',insert_db = False):
    start_time = datetime.datetime(year, month, 1, 0, 0, 0)
    end_time = start_time + relativedelta(months=+1)
    sql_pay='''
       select product_no from qunaer_new.pay_detail where trans_time >= '%s' and trans_time < '%s' and status=1
    ''' % (start_time,end_time)

    if product_no.__len__() > 0:
        sql_pay = '''
        select product_no from qunaer_new.pay_detail where product_no = '%s' and status=1
        ''' % product_no
    df_pay=pd.read_sql(sql_pay,engine_qunaer)

    #提取还款计划
    sql_plan='''
    select id,product_no,current_stage_no,deadline
    from repayment_plan_2017
    where product_no in %s
    '''
    sql_djy_plan='''
    select loan_no product_no,term_no current_stage_no,due_date from qunar_repayment_plan
    where loan_no IN %s
    '''
    update_sql='''
    update repayment_plan_2017 set deadline=%s where id=%s
    '''
    df_plan=pd.DataFrame()
    df_djy = pd.DataFrame()
    #数据太多，分开查询
    for i in range(0,len(df_pay),50000):
        product_nos = df_pay[i:i+50000].product_no.astype(str).tolist()
        df_plan=df_plan.append(pd.read_sql(sql_plan % str(tuple(product_nos)).replace(',)',')'),engine_qunaer))
        df_djy = df_djy.append(pd.read_sql(sql_djy_plan  % str(tuple(product_nos)).replace(',)',')'),engine_djy))
    df_plan.product_no=df_plan.product_no.astype(str)
    df_plan.current_stage_no = df_plan.current_stage_no.astype(int)
    df_djy.product_no = df_djy.product_no.astype(str)
    df_djy.current_stage_no = df_djy.current_stage_no.astype(int)
    df_djy.drop_duplicates(['product_no','current_stage_no'],inplace=True)
    df=pd.merge(df_plan,df_djy,on=['product_no','current_stage_no'],how='left')
    df.due_date = pd.to_datetime(df.due_date).dt.date
    df.deadline = pd.to_datetime(df.deadline).dt.date
    df['days']=df.apply(lambda x:(x.deadline - x.due_date).days,axis=1)
    df=df.ix[df['days'] != 0]
    print '=======不一样的deadline============',len(df)
    if insert_db & (len(df) > 0):
        df['id'] = df['id'].map('{:.0f}'.format)
        for i in range(0,len(df),5000):
            print '----------更新deadline-----------------',i
            up_vals = df[i:i+5000][['due_date', 'id']].values.tolist()
            engine_qunaer.execute(update_sql, up_vals)
    return df


# 单单只有退款服务费的时候，也需要更新下。
# 有退款的订单，更新还款计划-- 更新应还本金，利息
# 还款计划表 repayment_plan_2017
def step_2(year,month,product_no='',insert_db=False):
    start_time = datetime.datetime(year, month, 1, 0, 0, 0)
    end_time = start_time + relativedelta(months=+1)
    if product_no.__len__() > 0 :
        sql_refund = '''
            select product_no,refund_amount,refund_principle,refund_fee_amount,refund_due_amount,refund_time from qunaer_new.refund_detail
            where  product_no = '%s' AND refund_time >= '%s' and refund_time < '%s' and refund_status=1
            ORDER by refund_time asc
        ''' % (product_no,start_time,end_time)
    else:
        sql_refund = '''
            select product_no,refund_amount,refund_principle,refund_fee_amount,refund_due_amount,refund_time from qunaer_new.refund_detail
            where refund_status = 1 and refund_time >= '%s' and refund_time < '%s' and refund_status=1
            ORDER by refund_time asc
        ''' % (start_time,end_time)


    sql_pay='''
    select product_no,rate,stages from qunaer_new.pay_detail where product_no in %s 
    '''
    sql_plan='''
    select id,product_no,current_stage_no,deadline,principle,fee_amount,due_amount,status
    from repayment_plan_2017
    where product_no = '%s' ORDER by current_stage_no asc
    '''
    sql_repay='''
    select product_no,current_stage_no,repay_amount,repay_principle,repay_fee_amount,repay_due_amount
    from qunaer_new.repay_detail
    where repay_status = 1 and product_no = '%s' and repay_time < '%s'
    '''
    update_sql='''
    update repayment_plan_2017 set principle=%s ,fee_amount = %s ,due_amount=%s,status=%s where id=%s
    '''
    # 逾期退款--本期全额退款
    update_sql_2='''
    update repayment_plan_2017 set principle=0 ,fee_amount = 0,status = 2 where id = %s
    '''
    df_refund = pd.read_sql(sql_refund,engine_qunaer,chunksize=1000)
    i=0
    for df in df_refund:
        print '-------------update-------------',i
        i+=1000
        df.product_no = df.product_no.astype(str)
        df_pay = pd.read_sql(sql_pay % str(tuple(df.product_no.tolist())).replace(',)',')'),engine_qunaer)
        df=pd.merge(df,df_pay,on='product_no',how='inner')
        # 这块只针对退款本金和退款服务费进行了处理
        # TODO 后续需要对，如果没有退款本金，有退款罚息的如何处理进行研究下
        # TODO 进行应还罚息的时候，需要考虑 退款罚息和实还时间，这块在 step_5中进行处理
        df=df.ix[(df.refund_principle < 0) | (df.refund_fee_amount < 0)]
        df.refund_time=pd.to_datetime(df.refund_time)
        for index,row in df.iterrows():
            list1=[]
            product_no = row['product_no']
            refund_time = row['refund_time']
            df_plan = pd.read_sql(sql_plan % product_no, engine_qunaer)
            #还款计划一定存在，如果未找到，那么这笔放款在 16年，所以 repayment_plan_2017 表中不存在 2016年的还款计划
            if len(df_plan) == 0:
                continue
            #还款在退款前
            df_repay = pd.read_sql(sql_repay % (product_no,refund_time), engine_qunaer)
            df_plan.deadline=pd.to_datetime(df_plan.deadline).dt.date
            # 有退款本金和退款服务费的方可更新退款
            refund_principle = row['refund_principle']
            refund_time = row['refund_time']
            rate = float(row['rate'])
            if len(df_repay) == 0: #客户无还款情况
                status = 1
                #如果有逾期的，则进行全额匹配逾期
                df_yq=df_plan.ix[df_plan.deadline < refund_time.date()]
                if len(df_yq) > 0:
                    print '---------逾期退款-----------',df_yq
                    for index_yq, row_yq in df_yq.iterrows():
                        yq_id = int(row_yq['id'])
                        yq_principle=float(row_yq['principle'])
                        refund_principle = np.round(refund_principle + yq_principle,2)
                        if refund_principle <= 0 : #已还款
                            engine_qunaer.execute(update_sql_2, [yq_id])
                        else:
                            print '======逾期还款退款一半=======',yq_id
                            fee_amount = 0
                            engine_qunaer.execute(update_sql, [refund_principle,fee_amount,0,1,yq_id])
                            break
                #剔除逾期还款后的
                df_plan=df_plan.ix[df_plan.deadline >= refund_time.date()]
                cur_loan_amount = df_plan['principle'].sum()
                if refund_principle > 0 :
                    continue
                left_principle=np.round(cur_loan_amount+refund_principle,2)
                if left_principle == 0:
                    status = 2
                # 分期服务费 =  本金 * 利率 * 期数
                # 金额第三位小数向上取值
                stages=len(df_plan)
                all_fee_amount = get_2_ceil(left_principle * rate * stages)
                avg_principle = get_2_flo(left_principle/stages)
                first_principle = left_principle - avg_principle * (stages -1)
                avg_fee_amount = get_2_flo(left_principle * rate)
                first_fee_amount = all_fee_amount - avg_fee_amount * (stages -1)
                #TODO 如果only 1期，那么是 30天免息的
                if int(row['stages']) == 1:
                    all_fee_amount=0
                    avg_fee_amount = 0
                    first_fee_amount = 0
                min_term_no = df_plan['current_stage_no'].min()
                for jindex,jrow in df_plan.iterrows():
                    if int(jrow['current_stage_no']) == min_term_no:
                        list1.append([first_principle,first_fee_amount,0,status,int(jrow['id'])])
                    else:
                        list1.append([avg_principle, avg_fee_amount, 0, status, int(jrow['id'])])
                if insert_db & (len(list1) > 0):
                    engine_qunaer.execute(update_sql,list1)
            else: # 客户有还款
                # 如果 剩余应还 >= 退款本金，那么基于剩下的进行本金进行拆分
                sum_repay=df_repay['repay_principle'].sum()
                test = df_plan.ix[df_plan.current_stage_no.isin(df_repay.current_stage_no)]['principle'].sum()
                if np.round(test - sum_repay,2) != 0:
                    print '-----------客户应还-客户实还---------',product_no
                    continue
                #剔除还款状态为2的
                left_principle = np.round(df_plan.principle.sum() - sum_repay,2)
                if left_principle >= 0: # 剩余未还本金 > 退款情况
                    #剔除已还款的还款计划
                    df_plan = df_plan.ix[~df_plan.current_stage_no.isin(df_repay.current_stage_no)]
                    #未还是否有逾期
                    df_yq=df_plan.ix[df_plan.deadline < refund_time.date()]
                    if len(df_yq) > 0:
                        print '---------逾期退款-----------', df_yq
                        for index_yq, row_yq in df_yq.iterrows():
                            yq_id = int(row_yq['id'])
                            yq_principle = float(row_yq['principle'])
                            refund_principle = np.round(refund_principle + yq_principle, 2)
                            if refund_principle <= 0:  # 已还款
                                engine_qunaer.execute(update_sql_2, [yq_id])
                            else:
                                print '======逾期还款退款一半=======', yq_id
                                fee_amount = 0
                                engine_qunaer.execute(update_sql,[refund_principle, fee_amount, 0, 1, yq_id])
                                break
                    #剔除逾期后的
                    df_plan = df_plan.ix[df_plan.deadline >= refund_time.date()]
                    if refund_principle > 0:
                        continue
                    # 分期服务费 =  本金 * 利率 * 期数
                    # 金额第三位小数向上取值
                    left_principle = np.round(df_plan['principle'].sum()+refund_principle,2)
                    stages = len(df_plan)
                    all_fee_amount = get_2_ceil(left_principle * rate * stages)
                    avg_principle = get_2_flo(left_principle / stages)
                    first_principle = left_principle - avg_principle * (stages - 1)
                    avg_fee_amount = get_2_flo(left_principle * rate)
                    first_fee_amount = all_fee_amount - avg_fee_amount * (stages - 1)
                    status = 1
                    if left_principle == 0:
                        status = 2
                    min_term_no=df_plan['current_stage_no'].min()
                    for jindex, jrow in df_plan.iterrows():
                        if int(jrow['current_stage_no']) == int(min_term_no):
                            list1.append([first_principle, first_fee_amount, 0, status, int(jrow['id'])])
                        else:
                            list1.append([avg_principle, avg_fee_amount, 0, status, int(jrow['id'])])
                    if insert_db & (len(list1) > 0):
                        engine_qunaer.execute(update_sql, list1)
                else: # 剩余未还本金 《 退款金额
                    print '----------剩余未还金额<退款金额------',product_no




# 步骤2 钩稽还款计划 还款记录--
# 注意多笔还一期的情况，这种还款时间取最后，未做处理
# 使用还款计划表repayment_plan_2017
def step_3(year,month,insert_db = False,repay_nos=[]):
    start_time = datetime.datetime(year, month, 1, 0, 0, 0)
    end_time = start_time + relativedelta(months=+1)
    if repay_nos.__len__() > 0: # 指定了 还款流水
        sql_repay = '''
            SELECT product_no,repay_time src_repay_time,current_stage_no,repay_amount src_repay_amount,repay_principle src_repay_principle,
            repay_fee_amount src_repay_fee_amount,repay_due_amount src_repay_due_amount FROM qunaer_new.repay_detail
            WHERE repay_status = 1 
            and repay_no in %s
        ''' % str(tuple(repay_nos)).replace(',)',')')
    else:
        sql_repay='''
        SELECT product_no,repay_time src_repay_time,current_stage_no,repay_amount src_repay_amount,repay_principle src_repay_principle,
        repay_fee_amount src_repay_fee_amount,repay_due_amount src_repay_due_amount FROM qunaer_new.repay_detail
        WHERE repay_status = 1 AND repay_time >= '%s' AND repay_time < '%s'
        '''  % (start_time,end_time)


    sql_plan='''
    SELECT id plan_id,product_no,current_stage_no,repay_principle,repay_fee_amount,repay_due_amount,repay_time
    FROM repayment_plan_2017
    WHERE product_no IN %s
    '''
    update_plan='''
    update repayment_plan_2017
    set repay_time = %s , repay_amount = %s , repay_principle=%s,repay_fee_amount = %s ,repay_due_amount = %s,status = 2 where id = %s
    '''
    df_repay=pd.read_sql(sql_repay,engine_qunaer,chunksize=5000)
    for df in df_repay:
        #实还数据
        df = df.groupby(['product_no','current_stage_no']).agg({'src_repay_amount':'sum','src_repay_principle':'sum','src_repay_fee_amount':'sum',
                                                                'src_repay_due_amount':'sum','src_repay_time':'max'}).reset_index()
        df.current_stage_no = df.current_stage_no.astype(int)
        df.product_no = df.product_no.astype(str)
        # 还款计划中的数据
        product_nos = df.product_no.astype(str).tolist()
        df_plan = pd.read_sql(sql_plan % str(tuple(product_nos)).replace(',)',')'),engine_qunaer)
        df_plan.current_stage_no=df_plan.current_stage_no.astype(int)
        df_plan.product_no = df_plan.product_no.astype(str)

        #存在还款计划的钩稽关联
        df_new = pd.merge(df,df_plan,on=['product_no','current_stage_no'],how='inner')
        df_new.fillna(0, inplace=True)
        df_new['al'] = df_new['repay_principle'] + df_new['repay_fee_amount'] + df_new['repay_due_amount']
        print '-----------------未找到还款计划--------------', df_new.ix[(df_new.plan_id == 0)]
        #提取有还款计划的
        df_new.plan_id = df_new.plan_id.astype(int)
        df_new = df_new.ix[(df_new.plan_id != 0)]
        #更新最新还款时间---注意精确到秒
        df_new['repay_time']=pd.to_datetime(df_new['repay_time'])
        df_new['repay_time'] = df_new.apply(lambda x:max(x.src_repay_time,x.repay_time),axis=1)
        #如果是已经还款，那么 分别对应相加
        df_new['src_repay_amount'] = df_new['src_repay_amount'] + df_new['al']
        df_new['src_repay_principle'] =df_new['src_repay_principle']+df_new['repay_principle']
        df_new['src_repay_fee_amount'] = df_new['src_repay_fee_amount'] + df_new['repay_fee_amount']
        df_new['src_repay_due_amount'] = df_new['src_repay_due_amount'] + df_new['repay_due_amount']
        up_vals=df_new[['repay_time','src_repay_amount','src_repay_principle','src_repay_fee_amount','src_repay_due_amount','plan_id']].values.tolist()
        if insert_db & (len(df_new) > 0):
            print '-----update df_good len ===',len(df_new)
            engine_qunaer.execute(update_plan,up_vals)




# 转分期订单，更新还款计划状态为转分期态
def step_4(year,month,insert_db=False):
    start_time = datetime.datetime(year, month, 1, 0, 0, 0)
    end_time = start_time + relativedelta(months=+1)
    sql_pay='''
    select product_no,trans_time,loan_time from qunaer_new.pay_detail where is_ptf = 1 and status = 1 and trans_time >= '%s' and trans_time < '%s'
    ''' % (start_time,end_time)
    sql_trans_ref='''
    select trans_no,product_no old_product_no from qunaer_new.trans_ref where trans_no in %s
    '''
    sql_plan='''
    select id,product_no old_product_no,deadline,current_stage_no,require_fee,repay_amount,status from repayment_plan_2017 where product_no in %s
    '''
    update_sql='''
    update repayment_plan_2017 set status=%s  where id = %s and status = 1
    '''
    df_pay=pd.read_sql(sql_pay,engine_qunaer)
    trans_no = df_pay.product_no.astype(str).tolist()
    if len(df_pay) == 0:
        return
    df_ref=pd.read_sql(sql_trans_ref % str(tuple(trans_no)).replace(',)',')'),engine_qunaer)
    df_pay=pd.merge(df_pay,df_ref,left_on='product_no',right_on='trans_no',how='inner')
    df_pay.drop(['product_no'],inplace=True,axis = 1)
    old_product_nos=df_ref.old_product_no.astype(str).tolist()
    df_plan=pd.read_sql(sql_plan % str(tuple(old_product_nos)).replace(',)',')'),engine_qunaer)
    df=pd.merge(df_pay,df_plan,on='old_product_no',how='inner')
    df.fillna(0,inplace=True)
    #剔除已经还款的数据
    df=df.ix[df.repay_amount == 0]
    #剔除 require_fee == 0的数据
    df = df.ix[df.require_fee > 0]
    print '---------转分期订单中有状态为FINISH------',df.ix[df.status == 2]
    df=df.ix[df.status == 1]
    # 比较 deadline 和 trans_time 如果deadline > trans_time 更新
    df.trans_time=pd.to_datetime(df.trans_time).dt.date
    df.deadline=pd.to_datetime(df.deadline).dt.date
    print '-----------还款计划早于转分期----需要处理----',df.ix[df.trans_time > df.deadline]
    print '--------------期数-----------',df.current_stage_no.unique()
    df['status'] = 3
    if insert_db:
        df['id'] = df['id'].map('{:.0f}'.format)
        up_vals = df[['status', 'id']].values.tolist()
        engine_qunaer.execute(update_sql, up_vals)
    return df



# 拆分的还款计划同都江堰的比较
def check_plan_vs_djy(year,month):
    df_out=pd.DataFrame()
    start_time = datetime.datetime(year, month, 1, 0, 0, 0)
    end_time = start_time + relativedelta(months=+1)
    sql_pay ='''
    select product_no from qunaer_new.pay_detail where trans_time >= '%s' AND trans_time < '%s' AND status = 1
    ''' % (start_time,end_time)
    sql_plan='''
    select product_no,current_stage_no,principle,fee_amount from repayment_plan_2017
    where product_no in %s
    '''
    sql_djy='''
    select loan_no product_no,term_no current_stage_no,capital_amount,fee_amount djy_fee_amount from qunar_repayment_plan
    where loan_no in %s
    '''
    df_pay=pd.read_sql(sql_pay,engine_qunaer,chunksize=5000)
    for df in df_pay:
        product_nos=df.product_no.astype(str).tolist()
        df_plan=pd.read_sql(sql_plan % str(tuple(product_nos)).replace(',)',')'),engine_qunaer)
        df_djy = pd.read_sql(sql_djy % str(tuple(product_nos)).replace(',)',')'),engine_djy)
        df=pd.merge(df_plan,df_djy,on=['product_no','current_stage_no'],how='outer')
        df.fillna(0,inplace=True)
        df['diff1']=np.round(df['capital_amount'] - df['principle'],2)
        df['diff2'] = np.round(df['djy_fee_amount'] - df['fee_amount'],2)
        df_out=df_out.append(df.ix[(df['diff1'] != 0) | (df['diff2'] != 0)])
    return df_out


if __name__ == '__main__':
    year = 2017
    month = 10
    #TODO 首先校验放款利率，如果正常，放款进行录入放款计划
    #TODO 利率 如果出现 0.5 0.65 等这种数据是有问题的，利率太大了。
    #TODO 使用sql 查询，如果出现 0.2 以上的利率，这个要注意

    #=========step_1 BEGIN================
    # 录入还款计划
    # step_1(year,month,insert_db=True)
    # deadline 同步都江堰
    # df=step_1_1(year,month,insert_db=True)
    # =========step_1 END================

    # =========step_2 BEGIN================
    #根据退款，进行更新还款计划
    # step_2(year,month,insert_db=True)
    # 校验还款计划更新的对否
    # df=check_plan_vs_djy(year,month)
    # df.to_csv('E:/diff_2017_plan.csv')
    # =========step_2 END================

    # =========step_3 BEGIN================
    # 钩稽还款计划+还款记录
    # step_3(year,month,insert_db=True,repay_nos=[])
    # =========step_3 END================

    # =========step_4 BEGIN================
    # 还款计划的状态更新---针对该订单发生转分期后，后续的还款计划转为转分期状态
    # df=step_4(year,month,insert_db=True)
    # =========step_4 END================





