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

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

path = u'E:/审计出表/11月份开始使用表数据/11月份现金贷出表/'
engine_xjd = create_engine('mysql+mysqldb://internal_r:ArbNgtvlJzZHXsEu@172.16.3.201:3306/?charset=utf8',
						   echo=False).connect()

def faster_sql_reader(sql):
	cmdstr = 'mysql -h172.16.3.201 -uinternal_r -pArbNgtvlJzZHXsEu -P3306 -e "%s" > %s'
	file_name = 'test.txt'
	path_file = os.path.join(os.getcwd(), file_name)
	exe_sql = cmdstr % (sql.replace('`',''), path_file)
	exe_sql = exe_sql.replace('\t','').replace('\n',' ')
	os.system(exe_sql)
	try:
		df = pd.read_csv(path_file, sep='\t',encoding='utf-8')
	except:
		return pd.DataFrame()
	return df
'''
# 注意事项：非常重要 2017年4月份客户实还表中Loan_id = 52532760 的地方要特别地注意一下 ，1月份中loan_id = 39722219 也需要特别注意
'''



# 这5个资金方的还款计划来自于用户还款计划
# group by 是由于还资金方计划中有0.01的情况
sql_repay_frr_2 = '''
	SELECT
		urp.ref_id,
		urp.term_no 'term_no',
		date(urp.deadline) 'deadline',
		sum(urp.principle) 'principle',
		sum(urp.interest) 'interest'

	FROM new_transaction.user_repayment_plan urp
		JOIN audit.loan_manifest lm ON lm.ref_id = urp.ref_id
	WHERE lm.loan_paid_at>='%s' AND loan_paid_at<'%s'
	AND urp.fund_code IN (210,270,310,320,410,430)
	GROUP BY 1,2
'''

# test_shiyao.funding_repayment_plan中还资金方计划的数据是最全的
# group by 是由于还资金方计划中有0.01的情况
sql_repay_frr_3 = '''
	SELECT 
		frp.ref_id,
		frp.term_no 'term_no',
		date(frp.deadline) 'deadline',
		sum(frp.principle) 'principle',
		sum(frp.interest) 'interest'

	FROM audit.funding_repayment_plan frp
		JOIN audit.loan_manifest lm ON lm.ref_id = frp.ref_id
	WHERE lm.loan_paid_at>='%s' AND loan_paid_at<'%s'
	AND lm.funding_code NOT IN (210,270,310,320,410,430)
	GROUP BY  1,2
'''

# 在线还款部分
read_detail_online_sql = '''
SELECT t1.real_loan_id loan_id,date(lm.loan_paid_at) 'loan_paid_at',t1.ref_id,t1.fund_code funding_code,t1.term_no 'term_no',date(t6.bill_time) 'bill_time',t2.ref_amount 'ref_amount',
t2.principle-t2.mitigate_principle 'repay_prin',t2.interest-t2.mitigate_interest 'repay_intes',
ifnull(t2.`service_fee`-t2.mitigate_service_fee,0) 'repay_fee',IFNULL(t2.`punish`-t2.mitigate_collection_relief, 0) 'repay_due',
t2.remain_income 'remain',t2.repay_channel,t6.order_id AS 'flow_no',t6.amount 'bank_amount',t1.service_fee 'service_fee',t1.required_repayment 'required_repayment'
FROM new_transaction.xjd_repay_plan_repay_record_ref t2
JOIN new_transaction.user_repayment_plan t1 ON t1.id=t2.plan_id
JOIN new_transaction.repay_record_online t6 ON t2.record_id=t6.id AND t2.repay_channel = t6.pay_approach
JOIN audit.loan_manifest lm ON lm.ref_id = t1.ref_id
WHERE  lm.loan_paid_at >= '%s' AND lm.loan_paid_at < '%s'
'''

# 支付宝线下还款
read_detail_alipay_sql = '''
SELECT t1.real_loan_id loan_id,date(lm.loan_paid_at) 'loan_paid_at',t1.ref_id,t1.fund_code funding_code,t1.term_no 'term_no',date(t6.transfer_time) 'bill_time',t2.ref_amount 'ref_amount',
t2.principle-t2.mitigate_principle 'repay_prin',t2.interest-t2.mitigate_interest 'repay_intes',
ifnull(t2.`service_fee`-t2.mitigate_service_fee,0) 'repay_fee',IFNULL(t2.`punish`-t2.mitigate_collection_relief, 0) 'repay_due',
t2.remain_income 'remain',t2.repay_channel,t6.business_flow 'flow_no',t6.income 'bank_amount',t1.service_fee 'service_fee',t1.required_repayment 'required_repayment'
FROM new_transaction.xjd_repay_plan_repay_record_ref t2
JOIN new_transaction.`user_repayment_plan` t1 ON t1.id=t2.plan_id
JOIN new_transaction.`offline_alipay_record` t6 ON t2.record_id=t6.id AND t2.repay_channel = 13
JOIN audit.loan_manifest lm ON lm.ref_id = t1.ref_id
WHERE  lm.loan_paid_at >= '%s' AND  lm.loan_paid_at < '%s'
'''

# 银行线下还款部分，无对应流水号
read_detail_bank_sql = '''
SELECT t1.real_loan_id loan_id,date(lm.loan_paid_at) 'loan_paid_at',t1.ref_id,t1.fund_code funding_code,t1.term_no 'term_no',date(t6.transfer_time) 'bill_time',t2.ref_amount 'ref_amount',
t2.principle-t2.mitigate_principle 'repay_prin',t2.interest-t2.mitigate_interest 'repay_intes',
ifnull(t2.`service_fee`-t2.mitigate_service_fee,0) 'repay_fee',IFNULL(t2.`punish`-t2.mitigate_collection_relief, 0) 'repay_due',
t2.remain_income 'remain',t2.repay_channel,t6.transfer_amount 'bank_amount',t1.service_fee 'service_fee',t1.required_repayment 'required_repayment'
FROM new_transaction.xjd_repay_plan_repay_record_ref t2
JOIN new_transaction.`user_repayment_plan` t1 ON t1.id=t2.plan_id
JOIN audit.loan_manifest lm ON lm.ref_id = t1.ref_id
JOIN new_transaction.`offline_bank_repay_record` t6 ON t2.record_id=t6.id AND t2.repay_channel IN (14,15,16)
WHERE  lm.loan_paid_at >=  '%s' AND  lm.loan_paid_at < '%s'
'''

sql_fund_name = '''
select funding_code,name '资金方' 
from basic.funding_corp
'''
df_fund_name = pd.read_sql(sql_fund_name,engine_xjd)

sql_repay_channel = '''
select id 'repay_channel',approach_name '还款渠道'
from new_transaction.repay_channel
'''
df_approach_name = pd.read_sql(sql_repay_channel,engine_xjd)

# 获取还款数据
def handle_hk(year, month):
	# year, month = 2017, 1
	start_time = datetime.datetime(year, month, 1, 0, 0, 0)
	end_time = start_time + relativedelta(months=+1)

	# 获取全部还款计划
	# df_repay_frr_1 = pd.read_sql(sql_repay_frr_1 % (start_time, end_time), con=engine_xjd)
	df_repay_frr_2 = faster_sql_reader(sql_repay_frr_2 % (start_time, end_time))
	df_repay_frr_3 = faster_sql_reader(sql_repay_frr_3 % (start_time, end_time))

	df_repay_frr = pd.concat([df_repay_frr_2, df_repay_frr_3])
	df_repay_frr.rename(columns={'term_no': u'应还期数', 'deadline': u'应还资金方时间', 'principle': u'应还资金方本金', 'interest': u'应还资金方利息'},
			  inplace=True)

	df_repay_frr = df_repay_frr.drop_duplicates([u'ref_id', u'应还期数', u'应还资金方时间', u'应还资金方本金', u'应还资金方利息']).dropna()

	df_repay_frr['ref_id'] = df_repay_frr['ref_id'].astype('int64')
	df_repay_frr[u'应还期数'] = df_repay_frr[u'应还期数'].astype('int64')

	# 获取全部实还信息
	df_online_detail = faster_sql_reader(read_detail_online_sql % (start_time, end_time))
	df_alipay_detail = faster_sql_reader(read_detail_alipay_sql % (start_time, end_time))
	df_bank_detail = faster_sql_reader(read_detail_bank_sql % (start_time, end_time))

	df = pd.concat([df_online_detail, df_alipay_detail, df_bank_detail], axis=0)
	df.rename(columns={'loan_paid_at': u'放款时间', 'term_no': u'应还期数', 'bill_time': u'实还时间', 'ref_amount': u'实还总额', \
						  'repay_prin': u'实还本金', 'repay_intes': u'实还利息', 'repay_fee': u'实还服务费', 'repay_due': u'实还罚息', \
						  'remain': u'其他收益', 'flow_no': u'流水号', 'bank_amount': u'账单实还金额', 'service_fee': u'应收服务费',
						  'required_repayment': u'应还总额'}, inplace=True)

	df[u'应还期数'] = df[u'应还期数'].astype('int64')
	df['ref_id'] = df['ref_id'].astype('int64')

	# 还款和实还进行合并
	df = pd.merge(df, df_repay_frr, on=['ref_id', u'应还期数'], how='left')
	df.loc[df[u'流水号'].duplicated(), u'账单实还金额'] = 0
	df = pd.merge(df,df_fund_name,on='funding_code',how='left')
	df = pd.merge(df,df_approach_name,on='repay_channel',how='left')

	if len(df) > 0:

		df[u'唯一编号'] = df['loan_id'].astype('str') + df[u'应还期数'].fillna(0).astype('int').astype('str')

		df[u'实还总额-账单实还总额'] = df[u'实还总额'].fillna(0) - df[u'账单实还金额'].fillna(0)

		df[u'应还资金方本息-客户实还本息'] = df[u'应还资金方本金'].fillna(0) + df[u'应还资金方利息'].fillna(0) - df[u'实还本金'].fillna(0) - df[
			u'实还利息'].fillna(0)

		df[u'应收服务费-实还服务费'] = df[u'应收服务费'] - df[u'实还服务费']

		df = df.drop([u'ref_id'], axis=1)
		df = df.fillna(0)

		columns = [u'放款时间', u'唯一编号', u'资金方', 'loan_id', u'实还时间', u'实还总额', u'应还期数', u'实还本金', u'实还利息', u'实还服务费',
				   u'实还罚息', u'其他收益', u'流水号', u'还款渠道', u'账单实还金额', u'应还资金方时间', u'应还资金方本金', u'应还资金方利息',
				   u'应收服务费', u'实还总额-账单实还总额', u'应还资金方本息-客户实还本息', u'应收服务费-实还服务费']

		print(u'客户实还本息:' + str(sum(df[u'实还本金']) + sum(df[u'实还利息'])) + u' 应还资金方本金:' + str(
			sum(df[u'应还资金方本金'])) + u' 应还资金方利息' + str(sum(df[u'应还资金方利息'])))

		# if len(df) > max_limit:
		# 	for i in xrange(0, len(df), max_limit):
		# 		df[i:i + max_limit].to_excel(os.path.join(path, u'人工控制-现金贷-客户实还表-(%s-%02d)-%d.xlsx' % (
		# 			year, month, i / max_limit + 1)), index=None, columns=columns)
		# else:
		# 	df.to_excel(path + u'人工控制-现金贷-客户实还表-(%d-%d).xlsx' % (year, month), index=None, columns=columns)

		all_wb = pyexcelerate.Workbook()
		for i in xrange(0, len(df), max_limit):
			temp = df[i:i + max_limit][columns]
			temp_list = temp.values.tolist()
			temp_list.insert(0, temp.columns.tolist())
			all_wb.new_sheet(str(int(i / max_limit + 1)), data=temp_list)
		all_wb.save(path + u'人工控制-现金贷-客户实还表(%d-%02d).xlsx' % (start_time.year, start_time.month))


if __name__ == '__main__':
	year = [2017]
	month = range(10, 11, 1)

	for year, month in product(year, month):
		print '____%d__%d' % (year, month)
		handle_hk(year, month)
