# -*- 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月份）
# 表字段：
#	资金方	放款id	实还款日	实还款	马上本金	马上利息	马上服务费	量化派服务费	逾期费	滞纳金
# 数据库：
#	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/new_mashang?charset=utf8',
	echo=True)

sql_query_detail = '''
SELECT
	'新马上' 资金方,
	t1.loan_id '放款id',
	t1.repaid_at '实还款日',
	t1.repay_amt '实还款',
	t1.repay_principle '马上本金',
	t1.repay_intrest '马上利息',
	t1.fund_service_fee '马上服务费',
	t1.qg_service_fee '量化派服务费',
	t1.late_fee '逾期费',
	t1.penalty_fee '滞纳金'
FROM
	new_ms_transaction_with_term_details t1
WHERE 
	repaid_at>="%s" AND repaid_at<"%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.to_excel(path + u'新马上还款明细'+str(year) + '_' + str(month) + '.xlsx',index=None)


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)