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

#
# 表名称：
#	白条-非去哪儿-放款表（1-6月份）
#表字段：
#	资金方	产品编号	放款日期	总期数	白条商家	放款金额	本金债权
# 数据库：
#	baitiao_order，fund_corp，merchant
# 注意事项：
#	baitiao_order.is_active = 1、资金方统一为“保理”
#	8月份分为内保和直投模式，内保模式可以直接用,直投模式直接统计放款即可

file_path = u'E:/审计出表/非去哪儿白条-放款表（1月份-6月份）/'
engine_bt = create_engine('mysql+mysqldb://internal_r:ArbNgtvlJzZHXsEu@172.16.3.201:3306/?charset=utf8',
							  echo=False).connect()


def baitiao_pay(year, month):
	#year,month = 2017,8
	start_time = datetime.datetime(year, month, 1, 0, 0, 0)
	end_time = start_time + relativedelta(months=+1)

	#收款表，获取数据
	sql_bt_neibao_pay = '''
	SELECT
		bo.order_no as '产品编号',
		date(bo.loan_paid_at) as '放款日期',
		bo.contract_term as '总期数',
		mc.merchant_name as '白条商家',
		bo.real_loan_amount as '放款金额',
		bo.contract_loan_amount as '本金债权'
	FROM
		all_back_0930.baitiao_order bo
	JOIN baitiao_audit.merchant mc ON mc.merchant_id = bo.merchantId
	WHERE
  		bo.loan_paid_at >= '%s'
	AND bo.loan_paid_at < '%s'
	AND bo.is_active = 1
	AND bo.funding_corp_id = 1
	''' %(start_time,end_time)

	sql_bt_neibao_refund = '''
	SELECT
		bo.order_no as '产品编号',
		date(bo.train_cancel_at) as '放款日期',
		bo.contract_term as '总期数',
		mc.merchant_name as '白条商家',
		bo.real_loan_amount as '放款金额',
		bo.contract_loan_amount as '本金债权'
	FROM
		all_back_0930.baitiao_order bo
	JOIN baitiao_audit.merchant mc ON mc.merchant_id = bo.merchantId
	WHERE
		bo.train_cancel_at >= '%s'
	AND bo.train_cancel_at < '%s'
	AND bo.is_active = -2
	AND bo.funding_corp_id = 1
	''' %(start_time,end_time)

	df_bt_pay = pd.read_sql(sql_bt_neibao_pay,engine_bt)
	df_bt_refund = pd.read_sql(sql_bt_neibao_refund,engine_bt)

	df = pd.concat([df_bt_pay,df_bt_refund])

	# 收款表，获取数据
	sql_bt_zhitou_pay = '''
		SELECT
			bo.order_no as '产品编号',
			date(bo.loan_paid_at) as '放款日期',
			bo.contract_term as '总期数',
			mc.merchant_name as '白条商家',
			bo.funding_loan_amount as '放款金额',
			bo.contract_loan_amount as '本金债权',
			bfr.serial_number as '流水号-直投'
		FROM
			all_back_0930.baitiao_order bo
		JOIN baitiao_audit.merchant mc ON mc.merchant_id = bo.merchantId
		join all_back_0930.baitiao_finance_record bfr on bfr.batch_no = bo.batch_no_id
		WHERE
	  		bo.loan_paid_at >= '%s'
		AND bo.loan_paid_at < '%s'
		AND bo.is_active = 1
		AND bo.funding_corp_id = 5
		''' % (start_time, end_time)

	df_bt_zhitou_pay = pd.read_sql(sql_bt_zhitou_pay, engine_bt)

	print('----%s-%s'%(year,month))
	print(sum(df[u'放款金额']))
	df[u'资金方'] = u'保理'
	df_bt_zhitou_pay[u'资金方'] = u'笑脸'

	writer = pd.ExcelWriter(file_path+u'白条放款表_%s_%s_.xlsx' %(year,month))
	df.to_excel(writer,sheet_name = u'保理放款表',index=False)
	df_bt_zhitou_pay.to_excel(writer,sheet_name = u'直投放款表',index=False)
	writer.save()

	#df.to_csv(file_path + u'白条放款表_%s_%s_0925补录.csv' %(year,month), encoding='gb18030', index=False)

if __name__ == '__main__':

	# 还款明细
	years = [2017]
	months = range(9,10,1)
	for year, month in product(years, months):
		print('-----%s--%s') %(year,month)
		baitiao_pay(year, month)

print '-----------------------main done---------------------------------------'