# -*- coding: utf-8 -*-
import os
import sys
import datetime
from itertools import product

import pandas as pd
from dateutil.relativedelta import relativedelta

from sqlalchemy import create_engine
import multiprocessing as mtp

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

#
# 表名称：
#	现金贷-核算-还款表明细（1-6月份）
# 表字段：
#	资金方	loanID	放款期数	当前期数	实还款日	实还金额	本金	利息	服务费	实还资金方逾期费	实还量化派逾期费	实还复利
# 数据库：
#	yg_huishang_repay_term，loan_manifest
# 注意事项：
#	目前阳光徽商的还款数据从6月份开始
#

path = u'E:/审计出表/现金贷-还款表（1月份-6月份）/'

engine_new_transaction = create_engine(
	'mysql+mysqldb://internal_r:ArbNgtvlJzZHXsEu@172.16.4.60:3306/?charset=utf8',
	echo=True)

sql_query_detail = '''
SELECT
	'阳光徽商' fund_name,
	t2.loan_id,
	t2.contract_term,
	t1.term_no,
	t1.repay_date,
	t1.actual_repay_amount,
	t1.actual_principle,
	t1.actual_interest,
	t1.actual_qg_service_fee,
	t1.actual_fund_overdue_interest ,
	t1.actual_qg_overdue_interest ,
	t1.actual_compound_interest
FROM 
	new_transaction.yg_huishang_repay_term t1
JOIN audit.loan_manifest t2 ON t1.ref_id = t2.ref_id WHERE repay_date>="%s" AND repay_date<"%s"
'''


def repay(year, month):

	start_time = datetime.datetime(year, month, 1, 0, 0, 0)
	end_time = start_time + relativedelta(months=+1)

	df = pd.read_sql(sql_query_detail % (start_time, end_time), engine_new_transaction)

	df.rename(columns={'repay_date': u'实还款日',
					   'actual_principle': u'实还本金', 'actual_interest': u'实还利息', 'actual_qg_service_fee': u'实还服务费',
					   'actual_qg_overdue_interest': u'实还量化派逾期费',
					   'actual_fund_overdue_interest': u'实还资金方逾期费', 'actual_compound_interest': u'实还复利',
					   'fund_name': u'资金方', 'actual_repay_amount': u'实还金额', 'contract_term': '放款期数', 'term_no': '当期期数'},
			  inplace=True)

	df.to_excel(path + u'阳光徽商还款明细'+str(year) + '_' + str(month) + '.xlsx', columns=df.columns, index=None, encoding='utf8')


if __name__ == '__main__':
	years = [2017]
	months = range(7, 10, 1)
	for year, month in product(years, months):
		print '______%s_%s' % (year, month)
		repay(year, month)