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

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

#此路径为服务器路径专用
path = u'E:/审计出表/11月份开始使用表数据/11月份现金贷出表/'
# path = u'E:/审计出表/人工控制/现金贷-还资金方表-1010版本/'
engine_xjd = create_engine('mysql+mysqldb://internal_r:ArbNgtvlJzZHXsEu@172.16.3.201:3306/?charset=utf8',
					   echo=False).connect()

max_limit = 800000


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


sql_lm = '''
select ref_id,loan_id,funding_code,date(loan_paid_at) 'loan_paid_at',contract_term 'contract_term'
from audit.loan_manifest lm
where loan_paid_at>='%s' and loan_paid_at <'%s'
and is_active in (1, -2)
'''

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


#获取还资金方计划，分为两部分，这5个资金方使用的是用户还款计划，user_repayment_plan
sql_repay_frr_2 = '''
	select
		urp.ref_id,
		urp.term_no 'term_no',
		date(urp.deadline) 'deadline',
		urp.principle+urp.interest  'prin_intere'
	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
'''

#获取还资金方计划2，其余部分从test_shiyao.funding_repayment_plan中获取，如果在获取过程中出现丢失，可以查看是否数据库缺失
sql_repay_frr_3 = '''
	select 
		frp.ref_id,
		frp.term_no 'term_no',
		date(frp.deadline) 'deadline',
		frp.interest+frp.principle 'prin_intere'

	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
'''

#获取实还资金方信息，目前从audit.funding_repayment_record获取，可看后续是否需要修改
sql_refund = '''
	SELECT
		frr.ref_id,
		date(frr.repaid_at) 'repaid_at',
		sum(frr.current_repayment) 'current_repayment',
		sum(frr.principle) 'principle',
		sum(frr.interest) 'interest',
		frr.flow_id 'flow_id',
		frr.term_no 'term_no'
	FROM
		audit.funding_repayment_record frr
	join audit.loan_manifest lm on lm.ref_id = frr.ref_id
	JOIN basic.funding_corp fc ON fc.funding_code = frr.funding_code
	WHERE lm.loan_paid_at>='%s' 
	  and lm.loan_paid_at <'%s'
	AND frr.is_active = 1
	GROUP BY 1,frr.term_no
'''

sql_flow_id_reamount = '''
select flow_id,ref_amount
from basic.lkb_to_fund_ref
'''
#df_flow_id.to_csv(u'E:/flow_id对应银行金额.csv',encoding='utf8')


def refund(year,month):
	#year,month = 2017,9

	df_flow_id_reamount = faster_sql_reader(sql_flow_id_reamount)

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

	df_all = faster_sql_reader(sql_refund %(start_time,end_time))
	df_all.rename(
		columns={'repaid_at': u'实还时间', 'current_repayment': u'实还总额', 'principle': u'实还本金', 'interest': u'实还利息', \
				 'flow_id': u'流水号', 'term_no': u'应还期数'}, inplace=True)

	#实还部分进行去重
	df_all  = df_all.drop_duplicates([u'ref_id', u'实还时间', u'实还本金', u'实还总额', u'实还利息', u'流水号',u'应还期数'])

	df_all['ref_id'] = df_all['ref_id'].astype('int64')
	df_all = df_all.drop_duplicates()

	#放款部分
	df_lm = faster_sql_reader(sql_lm %(start_time,end_time))
	df_lm.rename(columns={'loan_paid_at':u'放款时间','contract_term':u'期数'},inplace=True)

	df_lm['ref_id'] = df_lm['ref_id'].astype('int64')
	df_lm = df_lm.drop_duplicates()

	#实还资金方部分代码
	# 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'应还资金方时间', 'prin_intere': u'应还资金方本息'}, inplace=True)

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

	df_flow_id_reamount.rename(columns = {u'ref_amount':u'银行金额',u'flow_id':u'流水号'},inplace=True)

	df_repay_frr = df_repay_frr.drop_duplicates()

	#sql_lm是一段时间内放款计划，在merge的过程中，由于df_repay_frr中有未还款情况，因此需要先合并还款计划，再对还款记录进行合并
	df_res = pd.merge(df_lm,df_repay_frr,on='ref_id',how='left')
	df_res = pd.merge(df_res,df_all,on=['ref_id',u'应还期数'],how= 'left')
	df_res= pd.merge(df_res,df_fund_name,on='funding_code',how='left')

	df_res[u'流水号'] = df_res[u'流水号'].fillna(0).astype(str)
	df_flow_id_reamount[u'流水号'] = df_flow_id_reamount[u'流水号'].astype(str)

	df_res = pd.merge(df_res,df_flow_id_reamount,on=u'流水号',how='left')

	df_res = df_res[~df_res[u'实还总额'].isnull()]
	df_res = df_res[~df_res[u'流水号'].isnull()]

	df_res.fillna(0, inplace=True)
	df_res[u'银行金额'] = df_res[u'银行金额'].fillna(0)

	a = (df_res[u'银行金额']==0) & (df_res[u'资金方']==u'湖北消金')
	df_res.loc[a,u'银行总额'] = df_res.loc[a, u'实还本金']+df_res.loc[a, u'实还利息']

	df_res.loc[(df_res[u'银行金额']==0),u'银行总额'] = df_res[u'实还总额'].fillna(0)

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

	df = df_res.fillna(0)

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

	columns = [u'唯一编号',u'资金方',u'loan_id',u'放款时间',u'期数',u'实还时间',u'实还总额',u'实还本金',u'实还利息',
			   u'流水号',u'银行金额',u'应还期数',u'应还资金方时间',u'应还资金方本息',u'应还资金方本息-实还资金方本息']

	print(u'实还总额:' + str(sum(df[u'实还总额'])) + u'应还资金方本息:' + str(
		sum(df[u'应还资金方本息'])) + u'应还资金方本息-实还资金方本息' + str(
		sum(df[u'应还资金方本息-实还资金方本息'])))

	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 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'0927现金贷—还资金方表-(%s-%02d)-%d.xlsx' % (
	# 			year, month, i / max_limit + 1)), index=None, columns = columns)
	# else:
	# 	df.to_excel(path + u'人工控制-现金贷—还资金方表-(%d-%d)-V1.xlsx' % (year, month), index=None, columns = columns)

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

	for year, month in product(year, month):
		print('-----------%s-%s begins') %(year,month)
		refund(year, month)
		print('-----------%s-%s ends')%(year,month)
		print('----------------------')

print '---------mian-----------'