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

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

file_path=u'E:/量化派/白条减值/'
file_name = u'白条减值_%s.xlsx'
engine_qunaer = create_engine('mysql+mysqldb://internal_r:ArbNgtvlJzZHXsEu@172.16.3.201:3306/qunaer_new?charset=utf8',
                              echo=False).connect()

listen_time='2016-12-31'
user_plan_2016='repayment_plan_2016_201612'

# 去哪儿
sql_1='''
SELECT YEAR(t2.`loan_time`) 放款年,MONTH(t2.`loan_time`) 放款月,t1.`current_stage_no` 期数,YEAR(t1.`deadline`) 应还年,MONTH(t1.`deadline`) 应还月,SUM(t1.`principle`) 未到期
FROM %s t1
JOIN `pay_detail` t2 ON t1.`product_no` = t2.`product_no`
WHERE t1.`status` IN ( 1,2) AND date(t1.`deadline`) > '%s'
GROUP BY 1,2,3,4,5
''' % (user_plan_2016,listen_time)



df_q_1=pd.read_sql(sql_1,engine_qunaer)


sql_3='''
SELECT YEAR(t2.`loan_time`) 放款年,MONTH(t2.`loan_time`) 放款月,t1.`current_stage_no` 期数,YEAR(t1.`deadline`) 应还年,MONTH(t1.`deadline`) 应还月,SUM(t1.`principle`) 逾期30天内
 FROM %s t1
JOIN `pay_detail` t2 ON t1.`product_no` = t2.`product_no`
WHERE t1.`status` IN ( 1,2) AND date(t1.`deadline`) <= '%s'
AND DATEDIFF('%s',date(t1.`deadline`)) <= 30
GROUP BY 1,2,3,4,5 
''' % (user_plan_2016,listen_time,listen_time)


df_q_2=pd.read_sql(sql_3,engine_qunaer)

sql_5='''
SELECT YEAR(t2.`loan_time`) 放款年,MONTH(t2.`loan_time`) 放款月,t1.`current_stage_no` 期数,YEAR(t1.`deadline`) 应还年,MONTH(t1.`deadline`) 应还月,SUM(t1.`principle`) 逾期30天上
 FROM `repayment_plan_2016` t1
JOIN `pay_detail` t2 ON t1.`product_no` = t2.`product_no`
WHERE t1.`status` IN (1) AND date(t1.`deadline`) <= '%s'
AND DATEDIFF('%s',date(t1.`deadline`)) > 30
GROUP BY 1,2,3,4,5 
''' % (listen_time,listen_time)


df_q_3=pd.read_sql(sql_5,engine_qunaer)


sql_7='''
SELECT YEAR(t2.loan_paid_at) 放款年,MONTH(t2.loan_paid_at) 放款月, t1.term_no 期数,YEAR(t1.deadline) 应还年,MONTH(t1.deadline) 应还月,SUM(t1.principle) 未到期
FROM new_transaction.user_bt_repayment_plan t1
JOIN baitiao_audit.baitiao_order t2 ON t2.ref_id = t1.ref_id
WHERE 
 DATE(t1.deadline) > '%s'
AND date(t2.loan_paid_at) <= '%s' AND t2.is_active  = 1  and t2.funding_corp_id = 1
GROUP BY 1,2,3,4,5;
''' % (listen_time,listen_time)
sql_8='''
SELECT YEAR(t2.loan_paid_at) 放款年,MONTH(t2.loan_paid_at) 放款月, t1.term_no 期数,YEAR(t1.deadline) 应还年,MONTH(t1.deadline) 应还月,SUM(t1.principle) 未到期
FROM new_transaction.user_bt_repayment_plan t1
JOIN baitiao_audit.baitiao_order t2 ON t2.ref_id = t1.ref_id
WHERE 
 DATE(t1.deadline) > '%s'
AND date(t2.loan_paid_at) <= '%s' AND t2.is_active  = -2  and t2.funding_corp_id = 1
GROUP BY 1,2,3,4,5;
''' % (listen_time,listen_time)
df_7=pd.read_sql(sql_7,engine_qunaer)
df_8=pd.read_sql(sql_8,engine_qunaer)
df_bt_1=pd.concat([df_7,df_8],ignore_index=True)

sql_9='''
SELECT YEAR(t2.loan_paid_at) 放款年,MONTH(t2.loan_paid_at) 放款月, t1.term_no 期数,YEAR(t1.deadline) 应还年,MONTH(t1.deadline) 应还月,SUM(t1.principle) 逾期30天内
FROM new_transaction.user_bt_repayment_plan t1
JOIN baitiao_audit.baitiao_order t2 ON t2.ref_id = t1.ref_id
WHERE 
 date(t1.deadline) <= '%s'
AND DATEDIFF('%s',date(t1.deadline)) <= 30
AND date(t2.loan_paid_at) <= '%s' AND t2.is_active =1   and t2.funding_corp_id = 1
GROUP BY 1,2,3,4,5;
''' % (listen_time,listen_time,listen_time)

sql_10='''
SELECT YEAR(t2.loan_paid_at) 放款年,MONTH(t2.loan_paid_at) 放款月, t1.term_no 期数,YEAR(t1.deadline) 应还年,MONTH(t1.deadline) 应还月,SUM(t1.principle) 逾期30天内
FROM new_transaction.user_bt_repayment_plan t1
JOIN baitiao_audit.baitiao_order t2 ON t2.ref_id = t1.ref_id
WHERE 
date(t1.deadline) <= '%s'
AND date(t2.loan_paid_at) <= '%s' AND t2.is_active  = -2
AND DATEDIFF('%s',date(t1.`deadline`)) <=30   and t2.funding_corp_id = 1
GROUP BY 1,2,3,4,5;
''' % (listen_time,listen_time,listen_time)
df_9=pd.read_sql(sql_9,engine_qunaer)
df_10=pd.read_sql(sql_10,engine_qunaer)
df_bt_2=pd.concat([df_9,df_10],ignore_index=True)

sql_11='''
SELECT YEAR(t2.loan_paid_at) 放款年,MONTH(t2.loan_paid_at) 放款月, t1.term_no 期数,YEAR(t1.deadline) 应还年,MONTH(t1.deadline) 应还月,SUM(t1.principle) 逾期30天上
FROM new_transaction.user_bt_repayment_plan t1
JOIN baitiao_audit.baitiao_order t2 ON t2.ref_id = t1.ref_id
WHERE t1.real_principle+t1.`real_interest`+t1.`real_service_fee`+t1.`real_punish` = 0
AND date(t1.deadline) <= '%s'
AND DATEDIFF('%s',date(t1.deadline)) > 30
AND date(t2.loan_paid_at) <= '%s' AND t2.is_active = 1   and t2.funding_corp_id = 1
GROUP BY 1,2,3,4,5;
''' % (listen_time,listen_time,listen_time)

sql_12='''
SELECT YEAR(t2.loan_paid_at) 放款年,MONTH(t2.loan_paid_at) 放款月, t1.term_no 期数,YEAR(t1.deadline) 应还年,MONTH(t1.deadline) 应还月,SUM(t1.principle) 逾期30天上
FROM new_transaction.user_bt_repayment_plan t1
JOIN baitiao_audit.baitiao_order t2 ON t2.ref_id = t1.ref_id
WHERE t1.real_principle+t1.`real_interest`+t1.`real_service_fee`+t1.`real_punish` = 0 AND
 date(t1.deadline) <= '%s'
AND date(t2.loan_paid_at) <= '%s' AND t2.is_active  = -2  and t2.funding_corp_id = 1
AND DATEDIFF('%s',date(t1.`deadline`)) > 30  
GROUP BY 1,2,3,4,5;
''' % (listen_time,listen_time,listen_time)

df_11=pd.read_sql(sql_11,engine_qunaer)
df_12=pd.read_sql(sql_12,engine_qunaer)

df_bt_3=pd.concat([df_11,df_12],ignore_index=True)

df_a_1=pd.concat([df_q_1,df_bt_1],ignore_index=True)
df_a_2=pd.concat([df_q_2,df_bt_2],ignore_index=True)
df_a_3=pd.concat([df_q_3,df_bt_3],ignore_index=True)

df_a=pd.merge(df_a_1,df_a_2,on=[u'放款年',u'放款月',u'期数',u'应还年',u'应还月'],how='outer')
df_a=pd.merge(df_a,df_a_3,on=[u'放款年',u'放款月',u'期数',u'应还年',u'应还月'],how='outer')
df_a=df_a.groupby([u'放款年',u'放款月',u'期数',u'应还年',u'应还月'])[u'未到期',u'逾期30天内',u'逾期30天上'].sum().reset_index()
df_a.fillna(0,inplace=True)
df_a.to_excel(file_path+file_name % listen_time,encoding='UTF-8')