# -*- coding: utf-8 -*-
import datetime
import sys

import pandas as pd
from sqlalchemy import create_engine


reload(sys)
sys.setdefaultencoding('utf8')

# 注意修改数据源
engine_new_transaction0630 = create_engine(
    'mysql+mysqldb://internal_r:ArbNgtvlJzZHXsEu@172.16.3.201:3306/all_back_0630?charset=utf8',
    echo=True)
engine_new_transaction0731 = create_engine(
    'mysql+mysqldb://internal_r:ArbNgtvlJzZHXsEu@172.16.3.201:3306/all_back_0731?charset=utf8',
    echo=True)

for i in['2017-4-1','2017-7-1']:
    read_sql_2017='''
    SELECT ref_id,order_no,loan_paid_at,sum(loan) FROM
    (SELECT b.ref_id,b.order_no,b.loan_paid_at,sum(u.principle) loan
    FROM `baitiao_order` b
    JOIN user_bt_repayment_plan u
    ON b.ref_id=u.ref_id
    WHERE DATE(b.loan_paid_at)<'%s' and b.is_active in(1)
    GROUP BY 1
    UNION ALL
    SELECT b.ref_id,b.order_no,b.loan_paid_at,sum(u.principle) loan
    FROM `baitiao_order` b
    JOIN user_bt_repayment_plan u
    ON b.ref_id=u.ref_id
    WHERE DATE(b.train_cancel_at)<'%s' and b.is_active =-2
    GROUP BY 1
    UNION ALL
    SELECT t4.ref_id,t4.order_no,t4.loan_paid_at,-sum(t1.principle - t1.mitigate_principle) loan
    FROM  baitiao_repay_plan_repay_record_ref t1
    JOIN repay_record_online t2 on t1.record_id = t2.id and t1.repay_channel < 13 and DATE(t2.bill_time) < '%s'
    join user_bt_repayment_plan t3 on t3.id = t1.plan_id
    join baitiao_order t4 on t4.ref_id = t3.ref_id and t4.is_active in (1) and DATE(t4.loan_paid_at) < '%s'
    GROUP BY 1
    UNION ALL
    SELECT t4.ref_id,t4.order_no,t4.loan_paid_at,-sum(t1.principle - t1.mitigate_principle) loan
    FROM  baitiao_repay_plan_repay_record_ref t1
    JOIN repay_record_online t2 on t1.record_id = t2.id and t1.repay_channel < 13 and DATE(t2.bill_time) < '%s'
    join user_bt_repayment_plan t3 on t3.id = t1.plan_id
    join baitiao_order t4 on t4.ref_id = t3.ref_id and t4.is_active in (-2) and DATE(t4.train_cancel_at) < '%s'
    GROUP BY 1
    UNION ALL
    SELECT t4.ref_id,t4.order_no,t4.loan_paid_at,-sum(t1.principle - t1.mitigate_principle) loan
    FROM baitiao_repay_plan_repay_record_ref t1
    JOIN offline_alipay_record t2 on t1.record_id = t2.id and t1.repay_channel = 13 and DATE(t2.transfer_time) <'%s'
    join user_bt_repayment_plan t3 on t3.id = t1.plan_id
    join baitiao_order t4 on t4.ref_id = t3.ref_id and t4.is_active in (1) and DATE(t4.loan_paid_at) < '%s'
    GROUP BY 1
    UNION ALL
    SELECT t4.ref_id,t4.order_no,t4.loan_paid_at,-sum(t1.principle - t1.mitigate_principle) loan
    FROM baitiao_repay_plan_repay_record_ref t1
    JOIN offline_alipay_record t2 on t1.record_id = t2.id and t1.repay_channel = 13 and DATE(t2.transfer_time) <'%s'
    join user_bt_repayment_plan t3 on t3.id = t1.plan_id
    join baitiao_order t4 on t4.ref_id = t3.ref_id and t4.is_active in (-2) and DATE(t4.train_cancel_at) < '%s'
    GROUP BY 1
    UNION ALL
    SELECT t4.ref_id,t4.order_no,t4.loan_paid_at,-sum(t1.principle - t1.mitigate_principle) loan
    FROM baitiao_repay_plan_repay_record_ref t1
    JOIN offline_bank_repay_record t2 on t1.record_id = t2.id and t1.repay_channel in (14,15,16) and DATE(t2.transfer_time) < '%s'
    join user_bt_repayment_plan t3 on t3.id = t1.plan_id
    join baitiao_order t4 on t4.ref_id = t3.ref_id and t4.is_active in (1) and DATE(t4.loan_paid_at) <'%s'
    GROUP BY 1) as a
    GROUP BY 1'''%(i,i,i,i,i,i,i,i,i,i,i,i)

    df1=pd.read_sql(read_sql_2017,con=engine_new_transaction0630)

    read_sql_repay='''SELECT ref_id,SUM(repay) FROM 
    (SELECT t4.ref_id ,
    SUM(t1.principle-t1.mitigate_principle) as repay
    FROM baitiao_repay_plan_repay_record_ref t1
    JOIN offline_alipay_record t2 on t1.record_id = t2.id 
    and t1.repay_channel = 13  AND t2.transfer_time<'2017-08-01' AND  t2.transfer_time>='%s'
    join user_bt_repayment_plan t3 on t3.id = t1.plan_id 
    join baitiao_order t4 on t4.ref_id = t3.ref_id and t4.is_active in (1,-2) and t4.loan_paid_at <'%s'
    JOIN merchant m ON m.merchant_id=t4.merchantId
    GROUP BY 1
    UNION
    SELECT  t4.ref_id ,
    SUM(t1.principle-t1.mitigate_principle) as repay
    FROM  baitiao_repay_plan_repay_record_ref t1 
    JOIN repay_record_online t2 on t1.record_id = t2.id 
    and t1.repay_channel < 13  AND t2.bill_time<'2017-08-01' AND  t2.bill_time>='%s'
    join user_bt_repayment_plan t3 on t3.id = t1.plan_id 
    join baitiao_order t4 on t4.ref_id = t3.ref_id and t4.is_active in (1,-2) and t4.loan_paid_at < '%s'
    JOIN merchant m ON m.merchant_id=t4.merchantId
    GROUP BY 1) as a 
    GROUP BY 1
    '''%(i,i,i,i)

    df2 = pd.read_sql(read_sql_repay, con=engine_new_transaction0731)
    df_201703 = pd.merge(df1, df2, how='left', on='ref_id')
    for n in range(0, len(df_201703), 500000):
        df_201703[n:n + 500000].to_csv(u'D:\\data\\非去哪白条'+i+'在贷贷后表现%d.csv' % n, index=None)
