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

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

#
# 表名称：
#	白条-每月到期本金利息
#表字段：
#	资金方	放款年	放款月	到期年	到期月	客户应还本金	客户应还利息
# 数据库：
#	user_bt_repayment_plan，baitiao_order，fund_corp
# 注意事项：
#	出来的表为汇总表
# 笑脸直投应还本金会有140的差距，是由于下单后使用红包金额导致。查询代码：
# SELECT SUM(funding_loan_amount),SUM(real_loan_amount) FROM baitiao_order WHERE funding_corp_id=5 AND funding_loan_amount!=real_loan_amount;

file_path = u'E:/审计出表/非去哪儿白条-放款表（1月份-6月份）/'
engine_bt = create_engine('mysql+mysqldb://internal_r:ArbNgtvlJzZHXsEu@172.16.4.60:3306/audit?charset=utf8',
							  echo=False).connect()

def bt_principle_interest():
	sql_bt = '''
	SELECT
		fc.fund_name 资金方,
		YEAR (bo.loan_paid_at) 放款年,
		MONTH (bo.loan_paid_at) 放款月,
		YEAR (plan.deadline) 到期年,
		MONTH (plan.deadline) 到期月,
		sum(plan.principle) 客户应还本金,
		0 客户应还利息
	FROM
		new_transaction.user_bt_repayment_plan plan
	JOIN baitiao_audit.baitiao_order bo ON bo.ref_id = plan.ref_id
	JOIN baitiao_audit.fund_corp fc ON fc.id = bo.funding_corp_id
	WHERE
		bo.is_active IN (1)
	GROUP BY
		1,2,3,4,5;	
	'''

	sql_bt_refund = '''
	SELECT
		fc.fund_name 资金方,
		YEAR (bo.loan_paid_at) 放款年,
		MONTH (bo.loan_paid_at) 放款月,
		YEAR (plan.deadline) 到期年,
		MONTH (plan.deadline) 到期月,
		sum(plan.principle) 客户应还本金,
		0 客户应还利息
	FROM
		new_transaction.user_bt_repayment_plan plan
	JOIN baitiao_audit.baitiao_order bo ON bo.ref_id = plan.ref_id
	JOIN baitiao_audit.fund_corp fc ON fc.id = bo.funding_corp_id
	WHERE
		bo.is_active IN (-2) AND bo.funding_corp_id in (1,5) AND bo.train_cancel_at<'2017-11-01'
	GROUP BY
		1,2,3,4,5;
	'''

	sql_cash_bt = '''
	select 
			fc.fund_name 资金方,
			YEAR (t1.loan_paid_at) 放款年,
			MONTH (t1.loan_paid_at) 放款月,
			YEAR (t1.loan_paid_at) 到期年,
			MONTH (t1.loan_paid_at) 到期月,
			sum(t1.real_loan_amount) 客户应还本金,
			0 客户应还利息
	from baitiao_audit.baitiao_order t1
	join baitiao_audit.fund_corp fc ON fc.id = t1.funding_corp_id
	where t1.baitiao_type=1
	GROUP BY 1,2,3,4,5;
	'''

	df_bt = pd.read_sql(sql_bt,engine_bt)
	df_bt_refund = pd.read_sql(sql_bt_refund,engine_bt)
	df_cash = pd.read_sql(sql_cash_bt,engine_bt)

	df = pd.concat([df_bt,df_bt_refund,df_cash],ignore_index=True)

	df.fillna(0,inplace=True)
	result = df.groupby([u'资金方',u'放款年',u'放款月',u'到期年',u'到期月'])[u'客户应还本金',u'客户应还利息'].sum().reset_index()
	result.to_excel(file_path+u'非去哪儿白条每月到期本金利息-V7.xlsx',index=False)
	df.to_excel(file_path+u'非去哪儿白条每月到期本金利息明细版本-V7.xlsx',index=False)

if __name__ == '__main__':
	print '--------main begin'
	bt_principle_interest()
	print '________main done'