# coding: utf-8
# Author: Perry_Zhu
import numpy as np
import pandas as pd
from sqlalchemy import create_engine
import pyexcelerate

max_limit = 800000
pd.options.mode.chained_assignment = None
pd.set_option('display.float_format', lambda x: '%.5f' % x)
engine_new_transaction = create_engine(
    'mysql+mysqldb://internal_r:ArbNgtvlJzZHXsEu@172.16.4.60:3306/new_transaction?charset=utf8', echo=False)

start_time = '2017-10-01'
end_time = '2017-11-01'
out_file_path = u'E:/审计出表/11月份开始使用表数据/11月份白条出表/'

loan_baoli = """
    select tt.ref_id,sum(tt.real_loan_amount) 放款金额,sum(contract_loan_amount)本金债权 
    from(
        SELECT
            bo.ref_id,
            IFNULL(bo.real_loan_amount, 0) real_loan_amount,
            IFNULL(bo.contract_loan_amount, 0) contract_loan_amount
        FROM
            baitiao_audit.baitiao_order bo
        WHERE
            bo.loan_paid_at >= '%s'
        AND bo.loan_paid_at < '%s'
        AND bo.is_active = 1
        AND bo.funding_corp_id = 1
        UNION ALL
            SELECT
                bo.ref_id,
                IFNULL(bo.real_loan_amount, 0) real_loan_amount,
                IFNULL(bo.contract_loan_amount, 0) contract_loan_amount
            FROM
                baitiao_audit.baitiao_order bo
            WHERE
                bo.train_cancel_at >= '%s'
            AND bo.train_cancel_at < '%s'
            AND bo.is_active =- 2
            AND bo.funding_corp_id = 1
    )tt
    group by 1
"""

loan_zhitou = """
    SELECT
        t1.ref_id,
        IFNULL(t1.funding_loan_amount, 0) real_loan_amount,
        IFNULL(t1.contract_loan_amount, 0) contract_loan_amount
    FROM
        baitiao_audit.baitiao_order t1
    
    WHERE
        t1.batch_no_id is not null 
    AND funding_corp_id = 5
    AND t1.is_active = 1
    AND t1.loan_paid_at >= '%s'
    AND t1.loan_paid_at < '%s'
    UNION ALL
    SELECT
        t1.ref_id,
        IFNULL(t1.funding_loan_amount, 0) real_loan_amount,
        IFNULL(t1.contract_loan_amount, 0) contract_loan_amount
    FROM
        baitiao_audit.baitiao_order t1
    
    WHERE
        t1.batch_no_id is not null 
    AND funding_corp_id = 5
    AND t1.is_active = -2
    AND t1.train_cancel_at >= '%s'
    AND t1.train_cancel_at < '%s'
"""

df_bl = pd.read_sql(loan_baoli % (start_time, end_time, start_time, end_time), engine_new_transaction)
df_zt = pd.read_sql(loan_zhitou % (start_time, end_time, start_time, end_time), engine_new_transaction)
df_loan = pd.concat([df_bl, df_zt], ignore_index=True)
query_plan_com = """
    SELECT tt.order_no,tt.ref_id,tt.term_no,tt.fund_name 资金方,tt.merchant_name 商户名称,
          min(tt.loan_paid_at)loan_paid_at,min(tt.contract_term)贷款总期数,
        min(tt.deadline)应还时间,SUM(tt.principle)应还本金,SUM(tt.interest) 应还利息
    FROM(
    SELECT
        bo.order_no,
        bo.ref_id,
        ubrp.term_no ,
        fc.fund_name,
        m.merchant_name,
        date(bo.loan_paid_at)loan_paid_at,
        bo.contract_term ,
        date(ubrp.deadline) deadline,
        ubrp.principle ,
        0.0 interest
    FROM
        user_bt_repayment_plan ubrp
    JOIN baitiao_audit.baitiao_order bo ON bo.ref_id = ubrp.ref_id AND bo.is_active =1 AND bo.funding_corp_id =1 AND bo.loan_paid_at >= '%s' AND bo.loan_paid_at < '%s'
    JOIN baitiao_audit.fund_corp fc ON fc.id = bo.funding_corp_id
    JOIN baitiao_audit.merchant m ON bo.merchantId = m.merchant_id
    
    union all
    
    SELECT
        bo.order_no,
        bo.ref_id,
        ubrp.term_no ,
        fc.fund_name,
        m.merchant_name,
        date(bo.train_cancel_at) loan_paid_at,
        bo.contract_term ,
        date(ubrp.deadline) ,
        ubrp.principle ,
        0.0 interest
    FROM
        user_bt_repayment_plan ubrp
    JOIN baitiao_audit.baitiao_order bo ON bo.ref_id = ubrp.ref_id AND bo.is_active =-2 AND bo.funding_corp_id =1 AND bo.train_cancel_at >= '%s' AND bo.train_cancel_at < '%s'
    JOIN baitiao_audit.fund_corp fc ON fc.id = bo.funding_corp_id
    JOIN baitiao_audit.merchant m ON bo.merchantId = m.merchant_id
    
    UNION ALL
        
    
    SELECT
        bo.order_no,
        bo.ref_id,
        ubrp.term_no,
        fc.fund_name ,
        m.merchant_name ,
        date(bo.loan_paid_at)loan_paid_at,
         bo.contract_term ,
        date(ubrp.deadline),
        bfrp.principal,
        bfrp.interest interest
    FROM
        user_bt_repayment_plan ubrp
    JOIN bt_funding_repayment_plan bfrp ON ubrp.repayment_plan_id = bfrp.repay_plan_id AND ubrp.sys_tag = 'XYQB_BILL_BT'
    JOIN baitiao_audit.baitiao_order bo ON bo.ref_id = ubrp.ref_id
    JOIN baitiao_audit.fund_corp fc ON fc.id = bo.funding_corp_id
    JOIN baitiao_audit.merchant m ON bo.merchantId = m.merchant_id AND bo.is_active = 1 AND bo.funding_corp_id = 5
        AND bo.loan_paid_at >= '%s' AND bo.loan_paid_at < '%s'
    )tt
    GROUP BY 1,2,3
"""
df_plan_com = pd.read_sql(query_plan_com % (start_time, end_time, start_time, end_time, start_time, end_time),
                          engine_new_transaction)

query_cash_plan = """
    SELECT tt.order_no,tt.ref_id,1 term_no,tt.fund_name 资金方,tt.merchant_name 商户名称,min(tt.loan_paid_at)loan_paid_at,min(tt.contract_term)贷款总期数,
        '' 应还时间,SUM(tt.principle)应还本金,0.0 应还利息 
    FROM (
    SELECT
        bo.order_no,
        bo.ref_id,
        fc.fund_name ,
        m.merchant_name ,
        date(bo.loan_paid_at)loan_paid_at,
        bo.contract_term ,
        bcr.real_order_amount principle
    FROM
        baitiao_cash_repay bcr
    JOIN baitiao_audit.baitiao_order bo ON bo.ref_id = bcr.ref_id AND bo.is_active=1 AND bo.funding_corp_id= 1 AND bo.loan_paid_at >= '%s' AND bo.loan_paid_at < '%s'
    JOIN baitiao_audit.fund_corp fc ON fc.id = bo.funding_corp_id
    JOIN baitiao_audit.merchant m ON bo.merchantId = m.merchant_id
    UNION ALL
    SELECT
        bo.order_no,
        bo.ref_id,
        fc.fund_name,
        m.merchant_name,
        date(bo.train_cancel_at) loan_paid_at,
        bo.contract_term ,
        bcr.real_order_amount principle
    FROM
        baitiao_cash_repay bcr
    JOIN baitiao_audit.baitiao_order bo ON bo.ref_id = bcr.ref_id AND bo.is_active=-2 AND bo.funding_corp_id =1 AND bo.train_cancel_at >= '%s' AND bo.train_cancel_at < '%s'
    JOIN baitiao_audit.fund_corp fc ON fc.id = bo.funding_corp_id
    JOIN baitiao_audit.merchant m ON bo.merchantId = m.merchant_id
    )tt
    GROUP BY 1,2,3
"""
df_cash_plan = pd.read_sql(sql=query_cash_plan % (start_time, end_time, start_time, end_time),
                           con=engine_new_transaction)

df_plan = pd.concat([df_plan_com, df_cash_plan], ignore_index=True, axis=0)
df_loan['term_no'] = 1
df_plan.ref_id = df_plan.ref_id.astype(int)
df_plan.term_no = df_plan.term_no.astype(int)
df_loan.ref_id = df_loan.ref_id.astype(int)
df_loan.term_no = df_loan.term_no.astype(int)
df_loan_all = pd.merge(df_loan, df_plan, on=['ref_id', 'term_no'], how='right')
df_loan_all.fillna(0, inplace=True)
query_repay_com = """
    SELECT tt.ref_id,tt.term_no,sum(tt.principle) 实还本金,0.00 实还利息,sum(plan_repay) '应还本息 - 实还本息' from(
    SELECT
        bo.ref_id,
        ubrp.term_no ,
        (brprrr.principle- ifnull(brprrr.mitigate_principle,0)) principle,
        (ubrp.principle - brprrr.principle) plan_repay
         
    FROM
        baitiao_repay_plan_repay_record_ref brprrr
    JOIN user_bt_repayment_plan ubrp ON ubrp.id = brprrr.plan_id
    JOIN baitiao_audit.baitiao_order bo ON bo.ref_id = ubrp.ref_id AND bo.is_active =1  AND bo.funding_corp_id =1 AND bo.loan_paid_at >= '%s' AND bo.loan_paid_at < '%s'
    
    
        UNION ALL
        SELECT
        bo.ref_id,
        ubrp.term_no,
        (brprrr.principle- ifnull(brprrr.mitigate_principle,0)) principle,
        (ubrp.principle - brprrr.principle) plan_repay
    FROM
        baitiao_repay_plan_repay_record_ref brprrr
    JOIN user_bt_repayment_plan ubrp ON ubrp.id = brprrr.plan_id
    JOIN baitiao_audit.baitiao_order bo ON bo.ref_id = ubrp.ref_id AND bo.is_active =-2 AND bo.funding_corp_id =1 AND bo.train_cancel_at >= '%s' AND bo.train_cancel_at < '%s'
    )tt
    GROUP BY 1,2
"""
df_repay_com = pd.read_sql(query_repay_com % (start_time, end_time, start_time, end_time), engine_new_transaction)
query_repay_zt = """
    SELECT tt.ref_id,tt.term_no,sum(tt.principle) 实还本金,sum(plan_repay) '应还本息 - 实还本息' from(
    SELECT
        bo.ref_id,
        ubrp.term_no ,
        (brprrr.principle- ifnull(brprrr.mitigate_principle,0)) principle,
        (ubrp.principle - brprrr.principle) plan_repay
         
    FROM
        baitiao_repay_plan_repay_record_ref brprrr
    JOIN user_bt_repayment_plan ubrp ON ubrp.id = brprrr.plan_id
    JOIN baitiao_audit.baitiao_order bo ON bo.ref_id = ubrp.ref_id AND bo.is_active =1  AND bo.funding_corp_id =5 AND bo.loan_paid_at >= '%s' AND bo.loan_paid_at < '%s'
    
    
        UNION ALL
        SELECT
        bo.ref_id,
        ubrp.term_no,
        (brprrr.principle- ifnull(brprrr.mitigate_principle,0)) principle,
        (ubrp.principle - brprrr.principle) plan_repay
    FROM
        baitiao_repay_plan_repay_record_ref brprrr
    JOIN user_bt_repayment_plan ubrp ON ubrp.id = brprrr.plan_id
    JOIN baitiao_audit.baitiao_order bo ON bo.ref_id = ubrp.ref_id AND bo.is_active =-2 AND bo.funding_corp_id =5 AND bo.train_cancel_at >= '%s' AND bo.train_cancel_at < '%s'
    )tt
    GROUP BY 1,2
"""
df_repay_zt = pd.read_sql(query_repay_zt % (start_time, end_time, start_time, end_time), engine_new_transaction)
sql_cash_loan_repay = """
    SELECT tt.ref_id,1 term_no,sum(tt.principle) 实还本金,sum(plan_repay) '应还本息 - 实还本息' FROM(
    SELECT
        bo.ref_id,
        bcrr.ref_amount principle,
        (bo.real_loan_amount - bcrr.ref_amount) plan_repay
    FROM
        new_transaction.baitiao_cash_repay bcr
    JOIN (
        SELECT
            repay_id,
            sum(ref_amount) ref_amount
        FROM
            new_transaction.baitiao_cash_record_ref
        where repay_channel in(18,24)
        GROUP BY
            1
    ) bcrr ON bcrr.repay_id = bcr.id
    JOIN baitiao_audit.baitiao_order bo ON bo.ref_id = bcr.ref_id AND bo.baitiao_type = 1 AND bo.loan_paid_at >= '%s' AND bo.loan_paid_at < '%s'
    
    UNION ALL
        SELECT
                bo.ref_id,
                bcrr.ref_amount principle,
                (bo.real_loan_amount - bcrr.ref_amount) plan_repay
            FROM
                baitiao_cash_repay bcr
            JOIN (
                SELECT
                    repay_id,
                    sum(ref_amount) ref_amount
                FROM
                    baitiao_cash_record_ref
                 where repay_channel =13
                GROUP BY 1
            ) bcrr ON bcrr.repay_id = bcr.id
            JOIN baitiao_audit.baitiao_order bo ON bo.ref_id = bcr.ref_id
            AND bo.is_active = -2 AND bo.train_cancel_at >= '%s' AND bo.train_cancel_at < '%s'
    )tt
    GROUP BY 1,2
"""
df_cash_loan_repay = pd.read_sql(sql=sql_cash_loan_repay % (start_time, end_time, start_time, end_time),
                                 con=engine_new_transaction)
df_repay = pd.concat([df_repay_com, df_repay_zt, df_cash_loan_repay], ignore_index=True, axis=0)
# df_repay.ref_id = df_repay.ref_id.astype(int)
df_repay.term_no = df_repay.term_no.astype(int)
# df_loan_all.ref_id = df_loan_all.ref_id.astype(int)
df_loan_all.term_no = df_loan_all.term_no.astype(int)
df = pd.merge(df_loan_all, df_repay, on=['ref_id', 'term_no'], how='left')
df.loc[df['ref_id'].duplicated(), [u'放款金额', u'本金债权']] = 0
# df.loc[tmp[['ref_id', 'term_no',u'放款金额']].duplicated(), [u'放款金额']] = 0
# df.loc[tmp[['ref_id', 'term_no',u'本金债权']].duplicated(), [u'本金债权']] = 0
df.rename(columns={'loan_paid_at': u'放款时间', 'order_no': u'唯一编码', 'term_no': u'应还期数'}, inplace=True)
df.drop(['ref_id'], axis=1, inplace=True)
df[u'实还本金'].fillna(0, inplace=True)
df[u'应还本息 - 实还本息'].fillna(0, inplace=True)
date_range = pd.date_range(start=start_time, end=end_time, freq='MS')
cnt = 0
for i in xrange(0, len(date_range) - 1):
    tmp = df.loc[(df[u'放款时间'] >= date_range[i].date()) & (df[u'放款时间'] < date_range[i + 1].date())]
    tmp[u'本金债权'] = tmp[u'本金债权'].apply(lambda x: np.round(x, 2))
    tmp[u'实还本金'] = tmp[u'实还本金'].apply(lambda x: np.round(x, 2))
    tmp = tmp[
        [u'唯一编码', u'商户名称', u'资金方', u'放款时间', u'放款金额', u'本金债权', u'贷款总期数', u'应还期数', u'应还时间', u'应还本金', u'应还利息', u'实还本金',
         u'实还利息', u'应还本息 - 实还本息']]
    tmp[u'实还利息'] = 0.00

    tmp.to_csv(out_file_path + u'人工控制-非去哪放款表（放款时间%s-%02d）V2.csv' % (date_range[i].year, date_range[i].month),
               index=None, encoding='GBK')

    all_wb = pyexcelerate.Workbook()
    for i in xrange(0, len(tmp), max_limit):
        temp = tmp[i:i + max_limit]
        temp_list = temp.values.tolist()
        temp_list.insert(0, temp.columns.tolist())
        all_wb.new_sheet(str(int(i / max_limit + 1)), data=temp_list)
    all_wb.save(out_file_path + u'人工控制-普通白条-放款表(%s-%02d).xlsx' % (date_range[i].year, date_range[i].month))
