# -*- 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

#
# 表名称：
#	现金贷-每月到期本金利息
#表字段：
#	资金方	放款年	放款月	到期年	到期月	客户应还本金	客户应还利息
# 数据库：
#	new_transaction，loan_manifest，fund_corp,loan_class_contract_1002
# 注意事项：
#	出来的表为汇总表
#

file_path = u'E:/审计出表/去哪儿/'
engine_xjd = create_engine('mysql+mysqldb://internal_r:ArbNgtvlJzZHXsEu@172.16.4.60:3306/audit?charset=utf8',
							  echo=False).connect()

def xjd_principle_interest():
	sql_quaer_2016 = '''
	SELECT
		fc.fund_name 资金方,
		YEAR (t2.loan_time) 放款年,
		MONTH (t2.loan_time) 放款月,
		YEAR (t1.deadline) 到期年,
		MONTH (t1.deadline) 到期月,
		sum(t1.principle) 客户应还本金,
		sum(t1.fee_amount) 客户应还利息
	FROM
		qunaer_new.repayment_plan_2016 t1
	JOIN qunaer_new.pay_detail t2 ON t1.product_no = t2.product_no
	JOIN qunaer_new.fund_corp fc ON fc.fund_code = t2.fund_code
	where t1.status in (1,2)
	GROUP BY
		1,2,3,4,5;
	'''

	sql_quaer_2017 = '''
	SELECT
		fc.fund_name 资金方,
		YEAR (t2.loan_time) 放款年,
		MONTH (t2.loan_time) 放款月,
		YEAR (t1.deadline) 到期年,
		MONTH (t1.deadline) 到期月,
		sum(t1.principle) 客户应还本金,
		sum(t1.fee_amount) 客户应还利息
	FROM
		qunaer_new.repayment_plan_2017 t1
	JOIN qunaer_new.pay_detail t2 ON t1.product_no = t2.product_no
	JOIN qunaer_new.fund_corp fc ON fc.fund_code = t2.fund_code
	where t1.status in (1,2) and t2.status = 1
	GROUP BY
		1,2,3,4,5;	
	'''

	sql_quaer_baoli = '''
	SELECT
		'保理' 资金方,
		YEAR (t2.loan_time) 放款年,
		MONTH (t2.loan_time) 放款月,
		YEAR (t1.deadline) 到期年,
		MONTH (t1.deadline) 到期月,
		sum(t1.principle) 客户应还本金,
		sum(t1.fee_amount) 客户应还利息
	FROM
		qunaer_new.repayment_plan_2017 t1
	JOIN qunaer_new.baoli_pay_detail t2 ON t1.product_no = t2.product_no and t1.fund_code = 1
	JOIN qunaer_new.fund_corp fc ON fc.fund_code = t2.fund_code
	WHERE t1.status IN (1,2) AND t2.status = 1 
	GROUP BY
		1,2,3,4,5;
	'''

	df_quaer_2016 = pd.read_sql(sql_quaer_2016,engine_xjd)
	df_quaer_2017 = pd.read_sql(sql_quaer_2017,engine_xjd)
	df_quaer_baoli = pd.read_sql(sql_quaer_baoli,engine_xjd)
	df_quaer_all = pd.concat([df_quaer_2016,df_quaer_baoli,df_quaer_2017],ignore_index=True)

	df_quaer_all.loc[df_quaer_all[u'资金方']==u'保理',u'客户应还利息'] = 0

	df_quaer_all.to_excel(file_path+u'去哪儿-每月到期本金利息-11月份V2.xlsx',index=False)

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