# coding: utf-8

# Author: Perry_Zhu
import numpy as np
import pandas as pd
import os
from sqlalchemy import create_engine
import sys

reload(sys)
sys.setdefaultencoding('utf-8')
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.3.201:3306/all_back_0930?charset=utf8', echo=False)
path_file = u'D:/人工控制/人工控制-放款表/'

# query_loan = """
#     SELECT
#         bo.ref_id,
#         bo.real_loan_amount 放款金额,
#         bo.contract_loan_amount 本金债权
#     FROM
#         all_back_0930.baitiao_order bo 
#     JOIN baitiao_audit.fund_corp fc ON fc.id = bo.funding_corp_id
#     JOIN all_back_0930.merchant m ON m.merchant_id = bo.merchantId
#     where bo.is_active =1
#     and bo.loan_paid_at>='2017-09-01'
#     and bo.loan_paid_at<'2017-08-01'
#     UNION ALL
#     SELECT
#         bo.ref_id,
#          bo.real_loan_amount 放款金额,
#         bo.contract_loan_amount 本金债权
#     FROM
#         all_back_0930.baitiao_order bo 
#     JOIN baitiao_audit.fund_corp fc ON fc.id = bo.funding_corp_id
#     JOIN all_back_0930.merchant m ON m.merchant_id = bo.merchantId
#     where bo.is_active =-2
#     and bo.train_cancel_at>='2017-09-01'
#     and bo.train_cancel_at<'2017-08-01'
# """
# df_loan_7 = pd.read_sql(query_loan, engine_new_transaction)

query_loan_09 = """
SELECT
    t1.ref_id,
	IFNULL(t1.funding_loan_amount, 0) 放款金额,
	IFNULL(t1.contract_loan_amount, 0) 本金债权
FROM
	all_back_0930.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 >= '2017-09-01'
AND t1.loan_paid_at < '2017-10-01'
UNION ALL
	SELECT
        bo.ref_id,
		IFNULL(bo.real_loan_amount, 0) 放款金额,
		IFNULL(bo.contract_loan_amount, 0) 本金债权
	FROM
		all_back_0930.baitiao_order bo
	WHERE
		bo.loan_paid_at >= '2017-09-01'
	AND bo.loan_paid_at < '2017-10-01'
	AND bo.is_active = 1
	AND bo.funding_corp_id = 1
	UNION ALL
		SELECT
            bo.ref_id,
			IFNULL(bo.real_loan_amount, 0) 放款金额,
			IFNULL(bo.contract_loan_amount, 0) 本金债权
		FROM
			all_back_0930.baitiao_order bo
		WHERE
			bo.train_cancel_at >= '2017-09-01'
		AND bo.train_cancel_at < '2017-10-01'
		AND bo.is_active =- 2
		AND bo.funding_corp_id = 1
"""
df_loan_9 = pd.read_sql(query_loan_09, engine_new_transaction)

# df_loan_7[u'放款金额'].sum()
df_loan=df_loan_9
# df_loan = pd.concat([df_loan_7, df_loan_8], ignore_index=True, axis=0)

query_plan_com = """
SELECT
    bo.order_no,
	bo.ref_id,
	ubrp.term_no ,
    fc.fund_name 资金方,
	m.merchant_name 商户名称,
    bo.loan_paid_at,
    bo.contract_term 贷款总期数,
	date(ubrp.deadline) 应还时间,
	ubrp.principle 应还本金,
	0.0 应还利息
FROM
	user_bt_repayment_plan ubrp
JOIN all_back_0930.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 >= '2017-09-01' AND bo.loan_paid_at < '2017-10-01'
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 商户名称,
    bo.train_cancel_at loan_paid_at,
    bo.contract_term 贷款总期数,
	date(ubrp.deadline) 应还时间,
	ubrp.principle 应还本金,
	0.0 应还利息
FROM
	all_back_0930.user_bt_repayment_plan ubrp
JOIN all_back_0930.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 >= '2017-09-01' AND bo.train_cancel_at < '2017-10-01'
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 商户名称,
    bo.loan_paid_at,
     bo.contract_term 贷款总期数,
	date(ubrp.deadline) 应还时间,
	bfrp.principal 应还本金,
	bfrp.interest 应还利息
FROM
	user_bt_repayment_plan ubrp
JOIN all_back_0930.bt_funding_repayment_plan bfrp ON ubrp.repayment_plan_id = bfrp.repay_plan_id AND ubrp.sys_tag = 'XYQB_BILL_BT'
JOIN all_back_0930.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 >= '2017-09-01'
AND bo.loan_paid_at < '2017-10-01'
"""
df_plan_com = pd.read_sql(query_plan_com, engine_new_transaction)

query_cash_plan = """
SELECT
    bo.order_no,
	bo.ref_id,
	1 term_no ,
    fc.fund_name 资金方,
	m.merchant_name 商户名称,
    bo.loan_paid_at,
    bo.contract_term 贷款总期数,
	'' 应还时间,
	bcr.real_order_amount 应还本金,
	0.0 应还利息
FROM
	new_transaction.baitiao_cash_repay bcr
JOIN all_back_0930.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 >= '2017-09-01' AND bo.loan_paid_at < '2017-10-01'
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,
	1 term_no ,
    fc.fund_name 资金方,
	m.merchant_name 商户名称,
    bo.train_cancel_at loan_paid_at,
    bo.contract_term 贷款总期数,
	'' 应还时间,
	bcr.real_order_amount 应还本金,
	0.0 应还利息
FROM
	new_transaction.baitiao_cash_repay bcr
JOIN all_back_0930.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 >= '2017-09-01' AND bo.train_cancel_at < '2017-10-01'
JOIN baitiao_audit.fund_corp fc ON fc.id = bo.funding_corp_id
JOIN baitiao_audit.merchant m ON bo.merchantId = m.merchant_id
"""
# df_cash_plan = pd.read_sql(sql=query_cash_plan, con=engine_new_transaction)

# In[21]:


# df_plan = pd.concat([df_plan_com, df_cash_plan], ignore_index=True, axis=0)
df_plan=df_plan_com
# In[22]:


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
	bo.ref_id,
	ubrp.term_no ,
	brprrr.principle 实还本金,
	ubrp.principle - brprrr.principle '应还本息 - 实还本息'
FROM
	all_back_0930.user_bt_repayment_plan ubrp
JOIN (
SELECT plan_id,sum(IFNULL(principle,0)-IFNULL(mitigate_principle,0))principle FROM
baitiao_repay_plan_repay_record_ref 
GROUP BY 1
)brprrr ON ubrp.id = brprrr.plan_id
JOIN 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 >= '2017-09-01' AND bo.loan_paid_at < '2017-10-01'


	UNION ALL
	SELECT
	bo.ref_id,
	ubrp.term_no,
	brprrr.principle 实还本金,
	ubrp.principle - brprrr.principle '应还本息 - 实还本息'
FROM
	all_back_0930.user_bt_repayment_plan ubrp
JOIN (
SELECT plan_id,sum(IFNULL(principle,0)-IFNULL(mitigate_principle,0))principle FROM
baitiao_repay_plan_repay_record_ref 
GROUP BY 1
)brprrr ON ubrp.id = brprrr.plan_id
JOIN all_back_0930.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 >= '2017-09-01' AND bo.train_cancel_at < '2017-10-01'
"""
df_com = pd.read_sql(query_repay_com, engine_new_transaction)

# In[13]:


query_repay_zt = """
SELECT
	bo.ref_id,
	ubrp.term_no,
	brprrr.principle 实还本金,
	bfrp.principal + bfrp.interest - brprrr.principle '应还本息 - 实还本息'
FROM
	all_back_0930.user_bt_repayment_plan ubrp
JOIN (
	SELECT plan_id, sum(IFNULL(principle, 0) - IFNULL(mitigate_principle, 0)) principle
	FROM
		all_back_0930.baitiao_repay_plan_repay_record_ref
	GROUP BY 1
) brprrr ON ubrp.id = brprrr.plan_id
JOIN bt_funding_repayment_plan bfrp ON ubrp.repayment_plan_id = bfrp.repay_plan_id
AND ubrp.sys_tag = 'XYQB_BILL_BT'
JOIN all_back_0930.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 >= '2017-09-01'
AND bo.loan_paid_at < '2017-10-01'
UNION ALL
	SELECT
		bo.ref_id,
		ubrp.term_no,
		brprrr.principle 实还本金,
		bfrp.principal + bfrp.interest - brprrr.principle '应还本息 - 实还本息'
	FROM
		all_back_0930.user_bt_repayment_plan ubrp
	JOIN (
		SELECT plan_id, sum(IFNULL(principle, 0) - IFNULL(mitigate_principle, 0)) principle
	FROM
		all_back_0930.baitiao_repay_plan_repay_record_ref
	GROUP BY 1
	) brprrr ON ubrp.id = brprrr.plan_id
	JOIN bt_funding_repayment_plan bfrp ON ubrp.repayment_plan_id = bfrp.repay_plan_id AND ubrp.sys_tag = 'XYQB_BILL_BT'
	JOIN 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 >= '2017-09-01' AND bo.train_cancel_at < '2017-10-01'
"""
df_zt = pd.read_sql(query_repay_zt, engine_new_transaction)



sql_cash_loan_repay = """
SELECT
    bo.ref_id,
	1 term_no,
	bcrr.ref_amount 实还本金,
	bo.real_loan_amount - bcrr.ref_amount '应还本息 - 实还本息'
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 all_back_0930.baitiao_order bo ON bo.ref_id = bcr.ref_id
AND bo.baitiao_type = 1
AND bo.loan_paid_at >= '2017-09-01'
AND bo.loan_paid_at < '2017-10-01'

UNION ALL
	SELECT
            bo.ref_id,
            1 term_no,
			bcrr.ref_amount 实还本金,
			bo.real_loan_amount - bcrr.ref_amount '应还本息 - 实还本息'
		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_order bo ON bo.ref_id = bcr.ref_id
		AND bo.is_active = -2
		AND bo.train_cancel_at >= '2017-09-01'
		AND bo.train_cancel_at < '2017-10-01'
"""
# df_cash_loan_repay = pd.read_sql(sql=sql_cash_loan_repay, con=engine_new_transaction)

df_repay = pd.concat([df_com, df_zt], 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.rename(columns={'loan_paid_at': u'放款时间', 'order_no': u'唯一编码', 'term_no': u'应还期数'}, inplace=True)
df.drop(['ref_id'], axis=1, inplace=True)
df.fillna(0, inplace=True)

date_range = pd.date_range(start='2017-09-01', end='2017-10-01', 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.to_excel(path_file + '0930非去哪放款表（放款时间%s-%02d）.xlsx' % (date_range[i].year, date_range[i].month), index=None,
                 encoding='gbk')




