# coding: utf-8
# Author: Perry_Zhu

import datetime
import sys

import pandas as pd
import pyexcelerate as pe
from sqlalchemy import create_engine
"""
注意：ref_id=5403743，order_no='1709161050021394'线上放款重复，
导致2017年10月给财务的质保金应还本息比实际多了100，逾期实还比实际多了150，赔付比实际值少了50
"""
reload(sys)
sys.setdefaultencoding('utf8')

engine_new_transaction = create_engine(
    'mysql+mysqldb://internal_r:ArbNgtvlJzZHXsEu@172.16.4.60:3306/new_transaction?charset=utf8', echo=True)
engine_xyqb = create_engine('mysql+mysqldb://syzhu_data_r:ulk0989742097y43@192.168.4.6:8066/xyqb?charset=utf8',
                            echo=True)

start_time = '2017-08-01'
end_time = '2017-11-01'
end_y = 2017
end_m = 11
end_date = datetime.date(end_y, end_m, 1)

path = u'E:/数据汇总/白条/每日赔付/2017-10/'

loan_info = """
    SELECT r.order_no,r.term_no,date(o.loan_paid_at) loan_paid_at
    FROM baitiao_audit.baitiao_order o
    JOIN new_transaction.bt_funding_repayment_record r ON o.ref_id=r.ref_id
"""

loan_plan_hk = '''
  SELECT t.order_no,t.term_no,t.day_hk,SUM(t.plan_all) plan_all from(
    SELECT plan.order_no,plan.term_no, DATE(plan.deadline) day_hk,SUM(plan.principal+plan.interest) plan_all 
    FROM  bt_funding_repayment_plan plan
    JOIN user_bt_repayment_plan t1 on plan.repay_plan_id=t1.repayment_plan_id AND t1.sys_tag='XYQB_BILL_BT'
    JOIN baitiao_audit.baitiao_order t2 ON t1.ref_id=t2.ref_id
    WHERE 
      plan.refund_status!=3
      and plan.deadline >= '%s' AND plan.deadline < '%s'  
    GROUP BY 1,2,3
    UNION ALL 
    SELECT plan.order_no,plan.term_no, DATE(plan.deadline) day_hk,SUM(plan.interest) plan_all 
    FROM  bt_funding_repayment_plan plan
    JOIN user_bt_repayment_plan t1 on plan.repay_plan_id=t1.repayment_plan_id AND t1.sys_tag='XYQB_BILL_BT'
    JOIN baitiao_audit.baitiao_order t2 ON t1.ref_id=t2.ref_id
    WHERE 
      plan.refund_status=3
      and plan.deadline >= '%s' AND plan.deadline < '%s'  
    GROUP BY 1,2,3
    )t 
   GROUP BY 1,2,3
'''
loan_plan_amount = """
 SELECT 
      t.order_no,t.term_no,t.day_hk,SUM(t.principle+t.interest) plan_all
    FROM(
        SELECT plan.order_no,plan.term_no,DATE(plan.deadline) day_hk,
          sum(ifnull(plan.principal,0))principle,0.0 interest
        FROM  bt_funding_repayment_record plan
        JOIN user_bt_repayment_plan t1 on plan.repay_plan_id=t1.repayment_plan_id AND t1.sys_tag='XYQB_BILL_BT' 
            AND plan.principal_account=%s AND plan.refund_status!=3
        JOIN baitiao_audit.baitiao_order t2 ON t1.ref_id=t2.ref_id
        WHERE 
          plan.deadline >= '%s' AND plan.deadline < '%s'  
        GROUP BY 1,2,3

         UNION ALL 
          SELECT plan.order_no,plan.term_no,DATE(plan.deadline) day_hk,
           0.0 principle,SUM(ifnull(plan.interest,0)) interest
        FROM  bt_funding_repayment_record plan
        JOIN user_bt_repayment_plan t1 on plan.repay_plan_id=t1.repayment_plan_id AND t1.sys_tag='XYQB_BILL_BT'  
            AND plan.interest_account=%s AND plan.refund_status!=3
        JOIN baitiao_audit.baitiao_order t2 ON t1.ref_id=t2.ref_id
        WHERE 
          plan.deadline >= '%s' AND plan.deadline < '%s'  
        GROUP BY 1,2,3
    )t 
    GROUP BY 1,2,3
"""

loan_hk_online = '''
    SELECT plan.order_no,plan.term_no,DATE(t2.bill_time) repaid_at ,DATE(plan.deadline) deadline ,
      sum(ifnull(t1.principle,0)-ifnull(t1.mitigate_principle,0)) real_principle
    FROM baitiao_repay_plan_repay_record_ref t1
    JOIN repay_record_online t2 ON t1.record_id = t2.id AND t1.repay_channel in(22,23,24)
    JOIN user_bt_repayment_plan t3 ON t1.plan_id = t3.id AND t3.sys_tag='XYQB_BILL_BT'
    JOIN bt_funding_repayment_record plan on plan.repay_plan_id=t3.repayment_plan_id  
      AND plan.refund_status!=3 AND plan.principal_account=%s
    JOIN baitiao_audit.baitiao_order t4 ON t4.ref_id=t3.ref_id AND t4.is_active = 1
    WHERE   
    (ifnull(t1.principle,0)-ifnull(t1.mitigate_principle,0))>0
    and t2.bill_time >= '%s' 
    and t2.bill_time < '%s'
    GROUP BY  1,2 ,3,4
'''

loan_hk_ali = '''
    SELECT plan.order_no,plan.term_no,DATE(t2.transfer_time) repaid_at,DATE(plan.deadline) deadline ,
      sum(ifnull(t1.principle,0)-ifnull(t1.mitigate_principle,0)) real_principle
    FROM baitiao_repay_plan_repay_record_ref t1
    JOIN offline_alipay_record t2 ON  t1.record_id = t2.id AND t1.repay_channel = 13
    JOIN user_bt_repayment_plan t3 ON t1.plan_id = t3.id AND t3.sys_tag='XYQB_BILL_BT'
    JOIN bt_funding_repayment_record plan on plan.repay_plan_id=t3.repayment_plan_id  
        AND plan.refund_status!=3 AND plan.principal_account=%s
    JOIN baitiao_audit.baitiao_order t4 ON t4.ref_id=t3.ref_id AND t4.is_active = 1
    WHERE  
      t2.transfer_time >= '%s' 
    and  t2.transfer_time < '%s'
    GROUP BY  1,2,3,4
'''

repay_online = '''
SELECT plan.order_no,plan.term_no,DATE(t2.bill_time) repaid_at,DATE(plan.deadline) deadline ,
      sum(ifnull(t1.principle,0)-ifnull(t1.mitigate_principle,0)) real_principle,
      sum(ifnull(t1.interest,0)-ifnull(t1.mitigate_interest,0)) real_interest
FROM baitiao_repay_plan_repay_record_ref t1
JOIN repay_record_online t2 ON t1.record_id = t2.id AND t2.pay_approach in(22,23,24)
JOIN user_bt_repayment_plan t3 ON t1.plan_id = t3.id AND t3.sys_tag='XYQB_BILL_BT' 
JOIN bt_funding_repayment_plan plan on plan.repay_plan_id=t3.repayment_plan_id  AND plan.refund_status!=3
JOIN baitiao_audit.baitiao_order t4 ON t4.ref_id=t3.ref_id AND t4.is_active = 1
WHERE   
 t2.bill_time >= '%s' 
and t2.bill_time < '%s'
GROUP BY 1,2,3,4;
'''
repay_alipay = '''
SELECT plan.order_no,plan.term_no,DATE(t2.transfer_time) repaid_at,DATE(plan.deadline) deadline ,
      sum(ifnull(t1.principle,0)-ifnull(t1.mitigate_principle,0)) real_principle,
      sum(ifnull(t1.interest,0)-ifnull(t1.mitigate_interest,0)) real_interest
FROM baitiao_repay_plan_repay_record_ref t1
JOIN offline_alipay_record t2 ON  t1.record_id = t2.id AND t1.repay_channel = 13 
JOIN user_bt_repayment_plan t3 ON t1.plan_id = t3.id AND t3.sys_tag='XYQB_BILL_BT'
JOIN bt_funding_repayment_plan plan on plan.repay_plan_id=t3.repayment_plan_id  AND plan.refund_status!=3
JOIN baitiao_audit.baitiao_order t4 ON t4.ref_id=t3.ref_id AND t4.is_active = 1
WHERE 
 t2.transfer_time >= '%s' 
and  t2.transfer_time < '%s'
GROUP BY 1,2,3,4
'''

tx_amount = 1
sr_amount = 2
zbj_amount = 3


def split_repay(df_repay, df_plan):
    df_plan.order_no = df_plan.order_no.astype(str)
    df_plan.term_no = df_plan.term_no.astype(int)
    df_repay.order_no = df_repay.order_no.astype(str)
    df_repay.term_no = df_repay.term_no.astype(int)
    df_repay['repaid_at'] = pd.to_datetime(df_repay['repaid_at']).apply(lambda x: x.date())
    df_repay['deadline'] = pd.to_datetime(df_repay['deadline']).apply(lambda x: x.date())
    # 正常还款
    df_zc = df_repay.ix[df_repay['repaid_at'] == df_repay['deadline']]
    df_zc = df_zc.groupby(by=['order_no', 'term_no', 'repaid_at'])['real_principle'].sum()
    df_zc = df_zc.to_frame()
    df_zc.reset_index(inplace=True)
    df_zc.rename(columns={'repaid_at': 'day_hk', 'real_principle': 'zc_prin_inst_total'}, inplace=True)
    # ===========逾期还款==============
    df_yq = df_repay.ix[df_repay['deadline'] < df_repay['repaid_at']]
    df_yq = df_yq.groupby(by=['order_no', 'term_no', 'repaid_at'])['real_principle'].sum()
    df_yq = df_yq.to_frame()
    df_yq.reset_index(inplace=True)
    df_yq.rename(columns={'repaid_at': 'day_hk', 'real_principle': 'yq_prin_inst_total'}, inplace=True)
    # ============提前还款==============
    df_tq = df_repay.ix[df_repay['deadline'] > df_repay['repaid_at']]
    # 实还可用筛选
    df_tq = df_tq.ix[df_tq['deadline'] < end_date]
    # 提前还款的可用日期变更
    df_tq = df_tq.groupby(by=['order_no', 'term_no', 'deadline'])['real_principle'].sum()
    df_tq = df_tq.to_frame()
    df_tq.reset_index(inplace=True)
    df_tq.rename(columns={'deadline': 'day_hk', 'real_principle': 'tq_prin_inst_total'}, inplace=True)
    # 合并
    if len(df_repay) > 0:
        df_hk_new = pd.merge(df_zc, df_yq, how='outer', on=['order_no', 'term_no', 'day_hk'])
        df_hk_new = pd.merge(df_hk_new, df_tq, how='outer', on=['order_no', 'term_no', 'day_hk'])
        df_hk_new.fillna(0, inplace=True)
        df_h = df_hk_new.groupby(by=['order_no', 'term_no', 'day_hk'])[
            'zc_prin_inst_total', 'yq_prin_inst_total', 'tq_prin_inst_total'].sum().reset_index()
        # df_plan.drop('day_hk', axis=1, inplace=True)
        df_hk_new = pd.merge(df_plan, df_h, how='outer', on=['order_no', 'term_no','day_hk'])
    else:
        df_hk_new = df_plan
        df_hk_new['zc_prin_inst_total'] = 0
        df_hk_new['yq_prin_inst_total'] = 0
        df_hk_new['tq_prin_inst_total'] = 0
    return df_hk_new


def spread_loan_id():
    df_plan = pd.read_sql(loan_plan_hk % (start_time, end_time, start_time, end_time), engine_new_transaction)
    df_tx = pd.read_sql(loan_plan_amount % (tx_amount, start_time, end_time, tx_amount, start_time, end_time),
                        engine_new_transaction)
    df_tx.rename(columns={'plan_all': 'tx_plan_all'}, inplace=True)
    df_sr = pd.read_sql(loan_plan_amount % (sr_amount, start_time, end_time, sr_amount, start_time, end_time),
                        engine_new_transaction)
    df_sr.rename(columns={'plan_all': 'sr_plan_all'}, inplace=True)
    df_zbj = pd.read_sql(loan_plan_amount % (zbj_amount, start_time, end_time, zbj_amount, start_time, end_time),
                         engine_new_transaction)
    df_zbj.rename(columns={'plan_all': 'zbj_plan_all'}, inplace=True)
    df_plan.fillna(0, inplace=True)
    df_online = pd.read_sql(repay_online % (start_time, end_time), engine_new_transaction)
    df_ali = pd.read_sql(repay_alipay % (start_time, end_time), engine_new_transaction)
    df_repay = pd.concat([df_online, df_ali], axis=0, ignore_index=True)
    df_repayment = split_repay(df_repay, df_plan)
    df_online_tx = pd.read_sql(loan_hk_online % (tx_amount, start_time, end_time), engine_new_transaction)
    df_ali_tx = pd.read_sql(loan_hk_ali % (tx_amount, start_time, end_time), engine_new_transaction)
    df_repay_tx = pd.concat([df_online_tx, df_ali_tx], axis=0, ignore_index=True)
    df_repay_tx = split_repay(df_repay_tx, df_tx)
    df_repay_tx.rename(columns={'zc_prin_inst_total': 'tx_zc_principle', 'yq_prin_inst_total': 'tx_yq_principle',
                                'tq_prin_inst_total': 'tx_tq_principle'}, inplace=True)
    df_online_sr = pd.read_sql(loan_hk_online % (sr_amount, start_time, end_time), engine_new_transaction)
    df_ali_sr = pd.read_sql(loan_hk_ali % (sr_amount, start_time, end_time), engine_new_transaction)
    df_repay_sr = pd.concat([df_online_sr, df_ali_sr], axis=0, ignore_index=True)
    df_repay_sr = split_repay(df_repay_sr, df_sr)
    df_repay_sr.rename(columns={'zc_prin_inst_total': 'sr_zc_principle', 'yq_prin_inst_total': 'sr_yq_principle',
                                'tq_prin_inst_total': 'sr_tq_principle'}, inplace=True)
    df_online_zbj = pd.read_sql(loan_hk_online % (zbj_amount, start_time, end_time), engine_new_transaction)
    df_ali_zbj = pd.read_sql(loan_hk_ali % (zbj_amount, start_time, end_time), engine_new_transaction)
    df_repay_zbj = pd.concat([df_online_zbj, df_ali_zbj], axis=0, ignore_index=True)
    df_repay_zbj = split_repay(df_repay_zbj, df_zbj)
    df_repay_zbj.rename(columns={'zc_prin_inst_total': 'zbj_zc_principle', 'yq_prin_inst_total': 'zbj_yq_principle',
                                 'tq_prin_inst_total': 'zbj_tq_principle'}, inplace=True)
    # 合并
    df_hk_new = pd.merge(df_repay_tx, df_repay_sr, how='outer', on=['order_no', 'term_no', 'day_hk'])
    df_hk_new = pd.merge(df_hk_new, df_repay_zbj, how='outer', on=['order_no', 'term_no', 'day_hk'])
    df_hk_new = pd.merge(df_repayment, df_hk_new, how='outer', on=['order_no', 'term_no', 'day_hk'])
    df_hk_new.fillna(0, inplace=True)
    df_hk_new['real_all'] = df_hk_new['zc_prin_inst_total'] + df_hk_new['yq_prin_inst_total'] + df_hk_new[
        'tq_prin_inst_total']
    df_info = pd.read_sql(loan_info, engine_new_transaction)
    df_info.order_no = df_info.order_no.astype(str)
    df_info.term_no = df_info.term_no.astype(int)
    df_hk_new.order_no = df_hk_new.order_no.astype(str)
    df_hk_new.term_no = df_hk_new.term_no.astype(int)
    df_hk_new = pd.merge(df_hk_new, df_info, on=['order_no', 'term_no'], how='left')
    df_hk_new = df_hk_new[
        ['order_no', 'term_no', 'loan_paid_at','day_hk', 'plan_all', 'zc_prin_inst_total', 'yq_prin_inst_total',
         'tq_prin_inst_total', 'tx_plan_all', 'tx_zc_principle', 'tx_yq_principle', 'tx_tq_principle',
         'sr_plan_all', 'sr_zc_principle', 'sr_yq_principle', 'sr_tq_principle', 'zbj_plan_all', 'zbj_zc_principle',
         'zbj_yq_principle', 'zbj_tq_principle', 'real_all']]

    df_hk_new.rename(columns={'day_hk': '还款日期', 'plan_all': '应还资金方本息', 'real_all': '客户实还可用',
                              'zc_prin_total': '客户正常还款本金', 'yq_prin_total': '客户逾期还款本金',
                              'zc_prin_inst_total': '客户正常还款本金', 'yq_prin_inst_total': '客户逾期还款本金',
                              'tq_prin_inst_total': '客户提前可用还款',
                              'tq_prin_total': '客户提前可用本金', 'tx_zc_principle': '提现账户客户正常还款本金',
                              'tx_yq_principle': '提现账户客户逾期还款本金', 'tx_tq_principle': '提现账户客户提前可用本金',
                              'sr_zc_principle': '收入账户客户正常还款本金', 'sr_yq_principle': '收入账户客户逾期还款本金',
                              'sr_tq_principle': '收入账户客户提前可用本金', 'zbj_zc_principle': '质保金账户客户正常还款本金',
                              'tx_plan_all': '提现账户应还资方本息', 'sr_plan_all': '收入账户应还资方本息',
                              'zbj_plan_all': '质保金账户应还资方本息', 'zbj_yq_principle': '质保金账户客户逾期还款本金',
                              'zbj_tq_principle': '质保金账户客户提前可用本金'}, inplace=True)
    # data_list = df_hk_new.values.tolist()
    # data_list.insert(0, df_hk_new.columns.tolist())
    # wb = pe.Workbook()
    # wb.new_sheet(u'明细', data=data_list)
    # wb.save(path + u'5_loan_id维度展开2017-10_2.xlsx')
    df_hk_new.to_excel(path + u'5_loan_id维度展开2017-10_3.xlsx', index=None, columns=df_hk_new.columns, encoding='utf8')


spread_loan_id()
