# -*- coding:utf-8 -*-
# Author: Perry_Zhu

import sys
import datetime
import itertools
import pandas as pd
from sqlalchemy import create_engine
import multiprocessing as mtp
import os

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

engine_new_transaction = create_engine(
    'mysql+mysqldb://yulong_rw:TouBStYwN8wkdxVt@172.16.3.201:3306/all_back_0630?charset=utf8',
    echo=True)

#分等级的赔付表

path = 'E:/peifu_loanpaid_At/'


'''
1、应还资金方本息
'''
sql_fund = '''
select id fund_code,name fund_name from new_transaction.funding_corp
'''

df_fund = pd.read_sql(sql_fund, con=engine_new_transaction)

# 还资金方时间-- 更改时间为还款时间
start_time = '2015-01-01 00:00:00'
end_time = '2017-07-01 00:00:00'

# 还资金方时间
start_time_deadline = '2017-01-01 00:00:00'
end_time_deadline = '2017-07-01 00:00:00'

start_loan_paid_at = '2015-01-01 00:00:00'
end_loan_paid_at = '2017-01-01 00:00:00'


# 应还资金方本息
sql_fund_hk = '''
SELECT DATE(t1.deadline) day_hk,t1.funding_code fund_code,SUM(t1.`principle`) plan_principle,SUM(t1.`interest`) plan_interest
FROM `funding_repayment_record` t1 
JOIN loan_manifest t2 ON  t1.ref_id=t2.ref_id WHERE  t1.is_active = 1 AND t1.funding_code=%s
and deadline >= '%s' AND deadline < '%s'
AND t2.loan_paid_at>='%s' AND t2.loan_paid_at<'%s'
GROUP BY DATE(deadline)
'''

# 客户还款计划
sql_plan_hk = '''
SELECT DATE(t1.deadline) day_hk,SUM(t1.principle+t1.interest) plan_all
FROM  `user_repayment_plan` t1 JOIN loan_manifest t2 ON  t1.ref_id=t2.ref_id
WHERE t1.fund_code=%s and deadline >= '%s' AND deadline < '%s'
AND t2.loan_paid_at>='%s' AND t2.loan_paid_at<'%s'
GROUP BY DATE(deadline)
'''

sql_290 = """
SELECT DATE(t1.repaid_at) day_hk,t1.funding_code fund_code,SUM(t1.`principle`) plan_principle,SUM(t1.`interest`) plan_interest
FROM `funding_repayment_record` t1 JOIN loan_manifest t2 ON  t1.ref_id=t2.ref_id
WHERE  t1.funding_code = %s and  repaid_at>= '%s' and repaid_at < '%s'
AND t2.loan_paid_at>='%s' AND t2.loan_paid_at<'%s'
GROUP BY 1
"""

# 用户实际还款--加入渠道
sql_hk_online = '''
SELECT DATE(t3.xyqb_repaid_at) repaid_at,DATE(t3.deadline) deadline ,t3.fund_code,
  t1.`principle` real_principle,t1.`mitigate_principle` mitigate_principle,t1.`interest` real_interest,t1.`mitigate_interest` mitigate_interest
FROM `xjd_repay_plan_repay_record_ref` t1
JOIN `repay_record_online` t2 ON t1.repay_channel < 13 AND t1.record_id = t2.id
JOIN `user_repayment_plan` t3 ON t1.plan_id = t3.id
JOIN loan_manifest t4 ON  t3.ref_id=t4.ref_id
WHERE t3.fund_code = %s and t3.xyqb_repaid_at >= '%s' and t3.xyqb_repaid_at < '%s'
AND t4.loan_paid_at>='%s' AND t4.loan_paid_at<'%s'
'''
sql_hk_huarong = """SELECT
	DATE(t3.xyqb_repaid_at) repaid_at,
	DATE(t4.deadline) deadline,
	t3.fund_code,
	t1.`principle` real_principle,
	t1.`mitigate_principle` mitigate_principle,
	t1.`interest` real_interest,
	t1.`mitigate_interest` mitigate_interest
FROM
	`xjd_repay_plan_repay_record_ref` t1
JOIN `repay_record_online` t2 ON t1.repay_channel IN (1,2,3,4,5,6,7,8,9,10,11,12,17)
AND t1.record_id = t2.id
JOIN `user_repayment_plan` t3 ON t1.plan_id = t3.id
JOIN funding_repayment_record t4 ON t4.ref_id = t3.ref_id AND t4.term_no = t3.term_no
JOIN loan_manifest t5 ON  t3.ref_id=t4.ref_id
WHERE t3.fund_code = %s  and t3.xyqb_repaid_at >= '%s' and t3.xyqb_repaid_at < '%s'
AND t5.loan_paid_at>='%s' AND t5.loan_paid_at<'%s'
	"""

sql_hk_alipay = '''
SELECT DATE(t2.transfer_time) repaid_at,DATE(t3.deadline) deadline ,t3.fund_code,
  t1.`principle` real_principle,t1.`mitigate_principle` mitigate_principle,t1.`interest` real_interest,
  t1.`mitigate_interest` mitigate_interest
FROM `xjd_repay_plan_repay_record_ref` t1
JOIN `offline_alipay_record` t2 ON t1.repay_channel = 13 AND t1.record_id = t2.id
JOIN `user_repayment_plan` t3 ON t1.plan_id = t3.id
JOIN loan_manifest t5 ON  t3.ref_id=t5.ref_id
WHERE t3.fund_code=%s and t2.transfer_time >= '%s' and  t2.transfer_time < '%s'
AND t5.loan_paid_at>='%s' AND t5.loan_paid_at<'%s'
'''

sql_hk_bank = '''
SELECT DATE(t2.transfer_time) repaid_at,DATE(t3.deadline) deadline ,t3.fund_code,t1.`principle` real_principle,t1.`mitigate_principle` mitigate_principle,t1.`interest` real_interest,t1.`mitigate_interest` mitigate_interest
FROM `xjd_repay_plan_repay_record_ref` t1
JOIN `offline_bank_repay_record` t2 ON t1.repay_channel in (14,15,16) AND t1.record_id = t2.id
JOIN `user_repayment_plan` t3 ON t1.plan_id = t3.id
JOIN loan_manifest t5 ON  t3.ref_id=t5.ref_id
WHERE t3.fund_code=%s and t2.transfer_time >= '%s' and  t2.transfer_time < '%s'
AND t5.loan_paid_at>='%s' AND t5.loan_paid_at<'%s'
'''

# ====== 应还时间使用 还资金方的deadline======================
sql_hk_fund_deadline_online = """SELECT
	DATE(t3.xyqb_repaid_at) repaid_at,
	DATE(t4.deadline) deadline,
	t3.fund_code,
	t1.`principle` real_principle,
	t1.`mitigate_principle` mitigate_principle,
	t1.`interest` real_interest,
	t1.`mitigate_interest` mitigate_interest
FROM
	`xjd_repay_plan_repay_record_ref` t1
JOIN `repay_record_online` t2 ON t1.repay_channel < 13 AND t1.record_id = t2.id
JOIN `user_repayment_plan` t3 ON t1.plan_id = t3.id
JOIN funding_repayment_record t4 ON t4.ref_id = t3.ref_id AND t4.term_no = t3.term_no
JOIN loan_manifest t5 ON  t3.ref_id=t5.ref_id
WHERE t3.fund_code =  %s  and t3.xyqb_repaid_at >= '%s' and t3.xyqb_repaid_at < '%s'
AND t5.loan_paid_at>='%s' AND t5.loan_paid_at<'%s'
	"""

sql_hk_fund_deadline_alipay = '''
SELECT DATE(t2.transfer_time) repaid_at,DATE(t4.deadline) deadline ,t3.fund_code,
t1.`principle` real_principle,t1.`mitigate_principle` mitigate_principle,t1.`interest` real_interest,
t1.`mitigate_interest` mitigate_interest
FROM `xjd_repay_plan_repay_record_ref` t1
JOIN `offline_alipay_record` t2 ON t1.repay_channel = 13 AND t1.record_id = t2.id
JOIN `user_repayment_plan` t3 ON t1.plan_id = t3.id
JOIN funding_repayment_record t4 ON t4.ref_id = t3.ref_id AND t4.term_no = t3.term_no
JOIN loan_manifest t5 ON  t3.ref_id=t5.ref_id
WHERE t3.fund_code=%s and t2.transfer_time >= '%s' and  t2.transfer_time < '%s'
AND t5.loan_paid_at>='%s' AND t5.loan_paid_at<'%s'
'''
sql_hk_fund_deadline_bank = '''
SELECT DATE(t2.transfer_time) repaid_at,DATE(t4.deadline) deadline ,t3.fund_code,
t1.`principle` real_principle,t1.`mitigate_principle` mitigate_principle,t1.`interest` real_interest,
t1.`mitigate_interest` mitigate_interest
FROM `xjd_repay_plan_repay_record_ref` t1
JOIN `offline_bank_repay_record` t2 ON t1.repay_channel in (14,15,16) AND t1.record_id = t2.id
JOIN `user_repayment_plan` t3 ON t1.plan_id = t3.id
JOIN funding_repayment_record t4 ON t4.ref_id = t3.ref_id AND t4.term_no = t3.term_no
JOIN loan_manifest t5 ON  t3.ref_id=t5.ref_id
WHERE t3.fund_code=%s and t2.transfer_time >= '%s' and  t2.transfer_time < '%s'
AND t5.loan_paid_at>='%s' AND t5.loan_paid_at<'%s'
'''

# =====委ACD 还资金方数据======
sql_A = '''
SELECT DATE(transfer_time) day_hk,transfer_amount plan_all FROM lkb_to_fund WHERE  fund_corp='张初阳'
'''
sql_C = '''
SELECT DATE(transfer_time) day_hk,transfer_amount plan_all FROM lkb_to_fund WHERE  fund_corp='李然'
'''
sql_D = '''
SELECT DATE(transfer_time) day_hk,transfer_amount plan_all FROM lkb_to_fund WHERE  fund_corp='曾思成'
'''
# 有一部分直投资金方 应还资金方本息 没有录入funding_repayment_record 直接使用 user_repayment_plan
sql_fund_hk_user_plan = '''
SELECT DATE(urp.deadline) day_hk,lm.funding_code fund_code,
  SUM(urp.`principle`) plan_principle,SUM(urp.`interest`) plan_interest
FROM `user_repayment_plan` urp
JOIN loan_manifest lm ON urp.ref_id=lm.ref_id
WHERE  lm.is_active in (1,-2) AND lm.funding_code=%s
and urp.deadline >= '%s' AND urp.deadline < '%s'
AND lm.loan_paid_at>='%s' AND lm.loan_paid_at<'%s'
GROUP BY DATE(urp.deadline)
'''




# 科易小贷，2017年的还款需要加入应还在 28-31 的提前还款
# 应还本息+ 提前还款（28号之前）- 提前应还款
def ky_table(fund_code, fund_name):
    # -------应还---------
    if fund_code in (290, 330):
        df_plan = pd.read_sql(sql_fund_hk_user_plan % (fund_code, start_time, end_time,start_loan_paid_at,end_loan_paid_at),
                              con=engine_new_transaction)
    else:
        df_plan = pd.read_sql(sql_fund_hk % (fund_code, start_time, end_time, start_loan_paid_at,end_loan_paid_at), con=engine_new_transaction)
    df_plan.fillna(0, inplace=True)
    df_plan['plan_all'] = df_plan['plan_interest'] + df_plan['plan_principle']
    df_plan.drop(['plan_principle', 'plan_interest'], inplace=True, axis=1)
    df_plan['day_hk'] = pd.to_datetime(df_plan['day_hk']).apply(lambda x: x.date())
    # ------实际还款-------

    df_online_hk = pd.read_sql(sql_hk_online % (fund_code, start_time, end_time, start_loan_paid_at,end_loan_paid_at),
                               con=engine_new_transaction)
    df_alipay_hk = pd.read_sql(sql_hk_alipay % (fund_code, start_time, end_time, start_loan_paid_at,end_loan_paid_at),
                               con=engine_new_transaction)
    df_hk = pd.concat([df_alipay_hk, df_online_hk], axis=0, ignore_index=True)
    df_hk['repaid_at'] = pd.to_datetime(df_hk['repaid_at']).apply(lambda x: x.date())
    df_hk['deadline'] = pd.to_datetime(df_hk['deadline']).apply(lambda x: x.date())
    df_hk.fillna(0, inplace=True)
    df_hk['real_principle'] = df_hk['real_principle'] - df_hk['mitigate_principle']
    df_hk['real_interest'] = df_hk['real_interest'] - df_hk['mitigate_interest']
    df_hk['real_principle'] = df_hk['real_principle'] + df_hk['real_interest']
    df_hk.drop(['mitigate_principle', 'mitigate_interest'], axis=1,
               inplace=True)

    # =============正常还款=========
    df_zc = df_hk.ix[df_hk['repaid_at'] == df_hk['deadline']]
    df_zc = df_zc.groupby(by=['repaid_at'])['real_principle'].sum()
    df_zc = df_zc.to_frame()
    df_zc.reset_index(inplace=True)
    df_zc.rename(columns={'repaid_at': 'day_hk', 'real_principle': 'zc_prin_inst_total'}, inplace=True)
    df_zc['day_hk'] = pd.to_datetime(df_zc['day_hk']).apply(lambda x: x.date())
    # ===========逾期还款==============
    df_yq = df_hk.ix[df_hk['deadline'] < df_hk['repaid_at']]
    df_yq = df_yq.groupby(by=['repaid_at'])['real_principle'].sum()
    df_yq = df_yq.to_frame()
    df_yq.reset_index(inplace=True)
    df_yq.rename(columns={'repaid_at': 'day_hk', 'real_principle': 'yq_prin_inst_total'}, inplace=True)
    df_yq['day_hk'] = pd.to_datetime(df_yq['day_hk']).apply(lambda x: x.date())
    # ============提前还款==============
    df_tq = df_hk.ix[df_hk['deadline'] > df_hk['repaid_at']]
    # =================== TODO 本次修改的内容 begin
    # 将2月1号到期的提前还款放到1月31号
    df_tq.ix[(df_tq.deadline == datetime.date(2016, 2, 1)) & (
        df_tq.repaid_at <= datetime.date(2016, 1, 31)), 'deadline'] = datetime.date(2016, 1, 31)
    # 将5月1号到期的提前还款放到4月30号
    df_tq.ix[(df_tq.deadline == datetime.date(2016, 5, 1)) & (
        df_tq.repaid_at <= datetime.date(2016, 4, 30)), 'deadline'] = datetime.date(2016, 4, 30)
    # 将7月1号至5号到期的提前还款放到6月30号
    df_tq.ix[(df_tq.deadline >= datetime.date(2016, 7, 1)) & (df_tq.deadline <= datetime.date(2016, 7, 5)) & (
        df_tq.repaid_at <= datetime.date(2016, 6, 30)), 'deadline'] = datetime.date(2016, 6, 30)
    # 将10月1号至15号到期的提前还款放到9月30号
    df_tq.ix[(df_tq.deadline >= datetime.date(2016, 10, 1)) & (df_tq.deadline <= datetime.date(2016, 10, 15)) & (
        df_tq.repaid_at <= datetime.date(2016, 9, 30)), 'deadline'] = datetime.date(2016, 9, 30)
    # =================== TODO 本次修改的内容 end
    # ---------手工处理---------------
    df_tq.ix[(df_tq.repaid_at >= datetime.date(2017, 1, 26)) & (df_tq.repaid_at < datetime.date(2017, 2, 6))
             & (df_tq.deadline > datetime.date(2017, 2, 27)), 'deadline'] = datetime.date(2017, 1, 26)
    df_tq.ix[(df_tq.repaid_at >= datetime.date(2017, 2, 28)) & (df_tq.repaid_at < datetime.date(2017, 3, 26))
             & (df_tq.deadline > datetime.date(2017, 3, 27)), 'deadline'] = datetime.date(2017, 2, 28)
    df_tq.ix[(df_tq.repaid_at >= datetime.date(2017, 3, 9)) & (df_tq.repaid_at < datetime.date(2017, 3, 16))
             & (df_tq.deadline > datetime.date(2017, 3, 17)), 'deadline'] = datetime.date(2017, 3, 9)
    # ----------end------------------

    # 增加一列 提前应还款金额
    df_tq_1 = df_tq.groupby(by=['deadline'])['real_principle'].sum()
    df_tq_1 = df_tq_1.to_frame()
    df_tq_1.reset_index(inplace=True)
    df_tq_1.rename(columns={'deadline': 'day_hk', 'real_principle': 'plan_tq_prin_inst_total'}, inplace=True)
    df_tq_1['day_hk'] = pd.to_datetime(df_tq_1['day_hk']).apply(lambda x: x.date())
    # 科易小贷在2016.12.28 号之后的提前还款，放到 2017年第一个周二
    if fund_code == 200:
        df_tq.ix[(df_tq.repaid_at > datetime.date(2016, 12, 26)), 'repaid_at'] = datetime.date(2017, 1, 3)
    df_tq = df_tq.groupby(by=['repaid_at'])['real_principle'].sum()
    df_tq = df_tq.to_frame()
    df_tq.reset_index(inplace=True)
    df_tq.rename(columns={'repaid_at': 'day_hk', 'real_principle': 'tq_prin_inst_total'}, inplace=True)
    df_tq['day_hk'] = pd.to_datetime(df_tq['day_hk']).apply(lambda x: x.date())

    # 合并
    df_hk_new = pd.merge(df_zc, df_yq, how='outer', on=['day_hk'])
    df_hk_new = pd.merge(df_hk_new, df_tq, how='outer', on=['day_hk'])
    df_hk_new = pd.merge(df_plan, df_hk_new, how='outer', on=['day_hk'])
    df_hk_new = pd.merge(df_hk_new, df_tq_1, how='outer', on=['day_hk'])
    df_hk_new.fillna(0, inplace=True)
    df_hk_new['real_all'] = df_hk_new['zc_prin_inst_total'] + df_hk_new['tq_prin_inst_total'] + df_hk_new[
        'yq_prin_inst_total']
    df_hk_new['plan_all'] = df_hk_new['plan_all'] + df_hk_new['tq_prin_inst_total'] - df_hk_new['plan_tq_prin_inst_total']
    df_hk_new['fund_name'] = fund_name
    df_hk_new = df_hk_new[
        ['fund_name', 'day_hk', 'plan_all', 'real_all', 'zc_prin_inst_total',
         'tq_prin_inst_total', 'yq_prin_inst_total']]
    df_hk_new.rename(columns={'day_hk': u'还款日期', 'plan_all': u'资金方应还本息', 'real_all': u'客户实还本息',
                              'zc_prin_inst_total': u'客户正常还款本息', 'yq_prin_inst_total': u'客户逾期还款本息',
                              'tq_prin_inst_total': u'客户提前还款本息'                              },
                     inplace=True)
    # df_hk_new.to_excel(excel_file, sheet_name=fund_name, columns=df_hk_new.columns, index=None, encoding='utf8')
    # excel_file=pd.ExcelWriter(path+str(fund_code)+'.xlsx')
    df_hk_new.to_excel(path + str(fund_code) +  '.xlsx', index=None, columns=df_hk_new.columns,
                       encoding='utf8')
    # excel_file.save()


# 华融，需要剔除 应还在 16年但还款时间在17年1-10号的数据
def hr_table(fund_code, fund_name):
    # -------应还---------
    df_plan = pd.read_sql(sql_fund_hk % (fund_code, start_time_deadline, end_time_deadline,start_loan_paid_at,end_loan_paid_at ),
                          con=engine_new_transaction)
    df_plan.fillna(0, inplace=True)
    df_plan['plan_all'] = df_plan['plan_interest'] + df_plan['plan_principle']
    df_plan.drop(['plan_principle', 'plan_interest'], inplace=True, axis=1)
    df_plan['day_hk'] = pd.to_datetime(df_plan['day_hk']).apply(lambda x: x.date())
    # ------实际还款-------
    df_online_hk = pd.read_sql(sql_hk_fund_deadline_online % (fund_code, start_time, end_time, start_loan_paid_at,end_loan_paid_at),
                               con=engine_new_transaction)
    df_alipay_hk = pd.read_sql(sql_hk_fund_deadline_alipay % (fund_code, start_time, end_time, start_loan_paid_at,end_loan_paid_at),
                               con=engine_new_transaction)
    df_hk = pd.concat([df_alipay_hk, df_online_hk], axis=0, ignore_index=True)
    df_hk['repaid_at'] = pd.to_datetime(df_hk['repaid_at']).apply(lambda x: x.date())
    df_hk['deadline'] = pd.to_datetime(df_hk['deadline']).apply(lambda x: x.date())
    df_hk['fund_code'] = df_hk['fund_code'].astype(int)
    df_hk['fund_name'] = fund_name
    df_hk.fillna(0, inplace=True)
    df_hk['real_principle'] = df_hk['real_principle'] - df_hk['mitigate_principle']
    df_hk['real_interest'] = df_hk['real_interest'] - df_hk['mitigate_interest']
    df_hk['real_principle'] = df_hk['real_principle'] + df_hk['real_interest']
    df_hk.drop(['mitigate_principle', 'mitigate_interest'], axis=1,
               inplace=True)
    # TODO ====逾期还款提前 2017.1-10 的数据 按照 deadline 进行统计即可
    df_hk.ix[(df_hk.deadline < datetime.date(2017, 1, 1)) & (df_hk.repaid_at >= datetime.date(2017, 1, 1)) & (
        df_hk.repaid_at < datetime.date(2017, 1, 11)), 'repaid_at'] = \
        df_hk.ix[(df_hk.deadline < datetime.date(2017, 1, 1)) & (df_hk.repaid_at >= datetime.date(2017, 1, 1)) & (
            df_hk.repaid_at < datetime.date(2017, 1, 11))].apply(lambda x: x['deadline'], axis=1)

    # =============正常还款=========
    df_hk.repaid_at=pd.to_datetime(df_hk.repaid_at).dt.date
    df_hk.deadline=pd.to_datetime(df_hk.deadline).dt.date
    df_zc = df_hk.ix[df_hk['repaid_at'] == df_hk['deadline']]
    df_zc = df_zc.groupby(by=['repaid_at'])['real_principle'].sum()
    df_zc = df_zc.to_frame()
    df_zc.reset_index(inplace=True)
    df_zc.rename(columns={'repaid_at': 'day_hk', 'real_principle': 'zc_prin_inst_total'}, inplace=True)
    df_zc['day_hk'] = pd.to_datetime(df_zc['day_hk']).apply(lambda x: x.date())
    # ===========逾期还款==============
    df_yq = df_hk.ix[df_hk['deadline'] < df_hk['repaid_at']]
    #将 2017.2.1-2017.2.10 提取到 2017.1---begin
    df_yq.ix[(df_yq.deadline >= datetime.date(2017,1,1)) & (df_yq.deadline < datetime.date(2017,2,1)) &
             (df_yq.repaid_at >= datetime.date(2017,2,1)) & (df_yq.repaid_at < datetime.date(2017,2,11)),'repaid_at']=\
        df_yq.ix[(df_yq.deadline >= datetime.date(2017, 1, 1)) & (df_yq.deadline < datetime.date(2017, 2, 1)) &
             (df_yq.repaid_at >= datetime.date(2017, 2, 1)) & (df_yq.repaid_at < datetime.date(2017, 2, 11))]['deadline']

    df_yq.ix[(df_yq.deadline >= datetime.date(2017, 1, 1)) & (df_yq.deadline < datetime.date(2017, 3, 1)) &
             (df_yq.repaid_at >= datetime.date(2017, 3, 1)) & (df_yq.repaid_at < datetime.date(2017, 3, 11)), 'repaid_at'] =\
        df_yq.ix[(df_yq.deadline >= datetime.date(2017, 1, 1)) & (df_yq.deadline < datetime.date(2017, 3, 1)) &
             (df_yq.repaid_at >= datetime.date(2017, 3, 1)) & (df_yq.repaid_at < datetime.date(2017, 3, 11))]['deadline']

    df_yq.ix[(df_yq.deadline >= datetime.date(2017, 1, 1)) & (df_yq.deadline < datetime.date(2017, 4, 1)) &
             (df_yq.repaid_at >= datetime.date(2017, 4, 1)) & (df_yq.repaid_at < datetime.date(2017, 4, 11)), 'repaid_at'] =\
        df_yq.ix[(df_yq.deadline >= datetime.date(2017, 1, 1)) & (df_yq.deadline < datetime.date(2017, 4, 1)) &
             (df_yq.repaid_at >= datetime.date(2017, 4, 1)) & (df_yq.repaid_at < datetime.date(2017, 4, 11))]['deadline']
    # 将 2017.2.1-2017.2.10 提取到 2017.1 -----end-----

    df_yq = df_yq.groupby(by=['repaid_at'])['real_principle'].sum()
    df_yq = df_yq.to_frame()
    df_yq.reset_index(inplace=True)
    df_yq.rename(columns={'repaid_at': 'day_hk', 'real_principle': 'yq_prin_inst_total'}, inplace=True)
    df_yq['day_hk'] = pd.to_datetime(df_yq['day_hk']).apply(lambda x: x.date())

    # ============提前还款==============
    df_tq = df_hk.ix[df_hk['deadline'] > df_hk['repaid_at']]
    # =================== TODO 本次修改的内容 begin
    # 将2月1号到期的提前还款放到1月31号
    df_tq.ix[(df_tq.deadline == datetime.date(2016, 2, 1)) & (
        df_tq.repaid_at <= datetime.date(2016, 1, 31)), 'deadline'] = datetime.date(2016, 1, 31)
    # 将5月1号到期的提前还款放到4月30号
    df_tq.ix[(df_tq.deadline == datetime.date(2016, 5, 1)) & (
        df_tq.repaid_at <= datetime.date(2016, 4, 30)), 'deadline'] = datetime.date(2016, 4, 30)
    # 将7月1号至5号到期的提前还款放到6月30号
    df_tq.ix[(df_tq.deadline >= datetime.date(2016, 7, 1)) & (df_tq.deadline <= datetime.date(2016, 7, 5)) & (
        df_tq.repaid_at <= datetime.date(2016, 6, 30)), 'deadline'] = datetime.date(2016, 6, 30)
    # 将10月1号至15号到期的提前还款放到9月30号
    df_tq.ix[(df_tq.deadline >= datetime.date(2016, 10, 1)) & (df_tq.deadline <= datetime.date(2016, 10, 15)) & (
        df_tq.repaid_at <= datetime.date(2016, 9, 30)), 'deadline'] = datetime.date(2016, 9, 30)
    # =================== TODO 本次修改的内容 end
    # ---------手工处理---------------
    df_tq.ix[(df_tq.repaid_at >= datetime.date(2017, 1, 26)) & (df_tq.repaid_at < datetime.date(2017, 2, 6))
             & (df_tq.deadline > datetime.date(2017, 2, 27)), 'deadline'] = datetime.date(2017, 1, 26)
    df_tq.ix[(df_tq.repaid_at >= datetime.date(2017, 2, 28)) & (df_tq.repaid_at < datetime.date(2017, 3, 26))
             & (df_tq.deadline > datetime.date(2017, 3, 27)), 'deadline'] = datetime.date(2017, 2, 28)
    df_tq.ix[(df_tq.repaid_at >= datetime.date(2017, 3, 9)) & (df_tq.repaid_at < datetime.date(2017, 3, 16))
             & (df_tq.deadline > datetime.date(2017, 3, 17)), 'deadline'] = datetime.date(2017, 3, 9)
    # ----------end------------------

    df_tq = df_tq.groupby(by=['deadline'])['real_principle'].sum()
    df_tq = df_tq.to_frame()
    df_tq.reset_index(inplace=True)
    df_tq['deadline'] = pd.to_datetime(df_tq['deadline']).apply(lambda x: x.date())
    df_tq.rename(columns={'deadline': 'day_hk', 'real_principle': 'tq_prin_inst_total'}, inplace=True)

    # 合并
    df_hk_new = pd.merge(df_zc, df_yq, how='outer', on=['day_hk'])
    df_hk_new = pd.merge(df_hk_new, df_tq, how='outer', on=['day_hk'])
    df_hk_new = pd.merge(df_plan, df_hk_new, how='outer', on=['day_hk'])
    df_hk_new.fillna(0, inplace=True)
    df_hk_new['real_all'] = df_hk_new['zc_prin_inst_total'] + df_hk_new['tq_prin_inst_total'] + df_hk_new[
        'yq_prin_inst_total']
    df_hk_new['fund_name'] = fund_name
    df_hk_new = df_hk_new.ix[df_hk_new.day_hk < datetime.date(2017, 5, 1)]
    df_hk_new = df_hk_new[
        ['fund_name', 'day_hk', 'plan_all', 'real_all', 'zc_prin_inst_total',
         'tq_prin_inst_total', 'yq_prin_inst_total']]
    df_hk_new.rename(
        columns={'day_hk': u'还款日期', 'plan_all': u'资金方应还本息', 'real_all': u'客户实还本息',
                 'zc_prin_inst_total': u'客户正常还款本息', 'yq_prin_inst_total': u'客户逾期还款本息',
                 'tq_prin_inst_total': u'客户提前还款本息'},
        inplace=True)
    df_hk_new.to_excel(path + str(fund_code) + '.xlsx', index=None, columns=df_hk_new.columns,
                       encoding='utf8')


def other_table(fund_code, fund_name):
    # -----应还资金方本息begin------
    if fund_code == 200:  # 科易小贷按照还款计划进行计算应还本息
        # 使用还款计划
        df_plan = pd.read_sql(sql_plan_hk % (fund_code, start_time_deadline, end_time_deadline, start_loan_paid_at,end_loan_paid_at),
                              con=engine_new_transaction)
    elif fund_code == 50:  # 应还本息使用客户实际还款，按照还款时间进行统计
        df_plan = pd.read_sql(sql_A, engine_new_transaction)
    elif fund_code == 80:
        df_plan = pd.read_sql(sql_C, engine_new_transaction)
    elif fund_code == 130:
        df_plan = pd.read_sql(sql_D, engine_new_transaction)
    elif fund_code in (270, 310, 320, 210):
        # 有一部分直投资金方 应还资金方本息 没有录入funding_repayment_record 直接使用 user_repayment_plan
        df_plan = pd.read_sql(sql_fund_hk_user_plan % (fund_code, start_time_deadline, end_time_deadline, start_loan_paid_at,end_loan_paid_at),
                              con=engine_new_transaction)
        df_plan.fillna(0, inplace=True)
        df_plan['plan_all'] = df_plan['plan_interest'] + df_plan['plan_principle']
        df_plan.drop(['plan_principle', 'plan_interest'], inplace=True, axis=1)
    else:
        df_plan = pd.read_sql(sql_fund_hk % (fund_code, start_time, end_time, start_loan_paid_at,end_loan_paid_at), con=engine_new_transaction)
        df_plan.fillna(0, inplace=True)
        df_plan['plan_all'] = df_plan['plan_interest'] + df_plan['plan_principle']
        df_plan.drop(['plan_principle', 'plan_interest'], inplace=True, axis=1)

    df_plan['day_hk'] = pd.to_datetime(df_plan['day_hk']).apply(lambda x: x.date())
    # -----应还资金方本息end------
    # ------实还------
    if fund_code in [2, 3, 4, 5]:  # 客户实还使用 还资金方deadline
        df_online_hk = pd.read_sql(sql_hk_fund_deadline_online % (fund_code, start_time, end_time, start_loan_paid_at,end_loan_paid_at),
                                   con=engine_new_transaction)
        df_alipay_hk = pd.read_sql(sql_hk_fund_deadline_alipay % (fund_code, start_time, end_time, start_loan_paid_at,end_loan_paid_at),
                                   con=engine_new_transaction)
        df_bank_hk = pd.read_sql(sql_hk_fund_deadline_bank % (fund_code, start_time, end_time, start_loan_paid_at,end_loan_paid_at),
                                 con=engine_new_transaction)
    else:
        df_online_hk = pd.read_sql(sql_hk_online % (fund_code, start_time, end_time, start_loan_paid_at,end_loan_paid_at),
                                   con=engine_new_transaction)
        df_alipay_hk = pd.read_sql(sql_hk_alipay % (fund_code, start_time, end_time, start_loan_paid_at,end_loan_paid_at),
                                   con=engine_new_transaction)
        df_bank_hk = pd.read_sql(sql_hk_bank % (fund_code, start_time, end_time, start_loan_paid_at,end_loan_paid_at),
                                 con=engine_new_transaction)

    df_hk = pd.concat([df_alipay_hk, df_online_hk, df_bank_hk], axis=0, ignore_index=True)
    df_hk['repaid_at'] = pd.to_datetime(df_hk['repaid_at']).apply(lambda x: x.date())
    df_hk['deadline'] = pd.to_datetime(df_hk['deadline']).apply(lambda x: x.date())
    df_hk.fillna(0, inplace=True)
    df_hk['real_principle'] = df_hk['real_principle'] - df_hk['mitigate_principle']
    df_hk['real_interest'] = df_hk['real_interest'] - df_hk['mitigate_interest']
    df_hk['real_principle'] = df_hk['real_principle'] + df_hk['real_interest']
    df_hk.drop(['mitigate_principle', 'mitigate_interest'], axis=1,
               inplace=True)
    if len(df_hk) < 1:
        return


    # =============正常还款=========
    df_zc = df_hk.ix[df_hk['repaid_at'] == df_hk['deadline']]
    df_zc = df_zc.groupby(by=['repaid_at'])['real_principle'].sum()
    df_zc = df_zc.to_frame()
    df_zc.reset_index(inplace=True)
    df_zc.rename(columns={'repaid_at': 'day_hk', 'real_principle': 'zc_prin_inst_total'}, inplace=True)
    df_zc['day_hk'] = pd.to_datetime(df_zc['day_hk']).apply(lambda x: x.date())
    # ===========逾期还款==============
    df_yq = df_hk.ix[df_hk['deadline'] < df_hk['repaid_at']]

    df_yq = df_yq.groupby(by=['repaid_at'])['real_principle'].sum()
    df_yq = df_yq.to_frame()
    df_yq.reset_index(inplace=True)
    df_yq.rename(columns={'repaid_at': 'day_hk', 'real_principle': 'yq_prin_inst_total'}, inplace=True)
    df_yq['day_hk'] = pd.to_datetime(df_yq['day_hk']).apply(lambda x: x.date())
    # ============提前还款==============
    df_tq = df_hk.ix[df_hk['deadline'] > df_hk['repaid_at']]
    # =================== TODO 本次修改的内容 begin
    # 将2月1号到期的提前还款放到1月31号
    df_tq.ix[(df_tq.deadline == datetime.date(2016, 2, 1)) & (
        df_tq.repaid_at <= datetime.date(2016, 1, 31)), 'deadline'] = datetime.date(2016, 1, 31)
    # 将5月1号到期的提前还款放到4月30号
    df_tq.ix[(df_tq.deadline == datetime.date(2016, 5, 1)) & (
        df_tq.repaid_at <= datetime.date(2016, 4, 30)), 'deadline'] = datetime.date(2016, 4, 30)
    # 将7月1号至5号到期的提前还款放到6月30号
    df_tq.ix[(df_tq.deadline >= datetime.date(2016, 7, 1)) & (df_tq.deadline <= datetime.date(2016, 7, 5)) & (
        df_tq.repaid_at <= datetime.date(2016, 6, 30)), 'deadline'] = datetime.date(2016, 6, 30)
    if fund_code == 170:  #
        df_tq.ix[(df_tq.repaid_at < datetime.date(2016, 10, 1)) & (
            df_tq.deadline >= datetime.date(2016, 10, 1)), 'deadline'] = datetime.date(2016, 9, 30)
    else:
        # 将10月1号至15号到期的提前还款放到9月30号
        df_tq.ix[(df_tq.deadline >= datetime.date(2016, 10, 1)) & (df_tq.deadline <= datetime.date(2016, 10, 15)) & (
            df_tq.repaid_at <= datetime.date(2016, 9, 30)), 'deadline'] = datetime.date(2016, 9, 30)

    # =================== TODO 本次修改的内容 end
    # ---------手工处理---------------
    df_tq.ix[(df_tq.repaid_at >= datetime.date(2017, 1, 26)) & (df_tq.repaid_at < datetime.date(2017, 2, 6))
                 & (df_tq.deadline > datetime.date(2017, 2, 27)), 'deadline'] = datetime.date(2017,1,26)
    df_tq.ix[(df_tq.repaid_at >= datetime.date(2017,2,28)) & (df_tq.repaid_at < datetime.date(2017,3,26))
                & (df_tq.deadline > datetime.date(2017,3,27)),'deadline'] = datetime.date(2017,2,28)
    df_tq.ix[(df_tq.repaid_at >= datetime.date(2017, 3, 9)) & (df_tq.repaid_at < datetime.date(2017, 3, 16))
             & (df_tq.deadline > datetime.date(2017, 3, 17)), 'deadline'] = datetime.date(2017, 3, 9)
    # ----------end------------------

    df_tq = df_tq.groupby(by=['deadline'])['real_principle'].sum()
    df_tq = df_tq.to_frame()
    df_tq.reset_index(inplace=True)
    df_tq['deadline'] = pd.to_datetime(df_tq['deadline']).apply(lambda x: x.date())
    df_tq.rename(columns={'deadline': 'day_hk', 'real_principle': 'tq_prin_inst_total'}, inplace=True)

    # 合并
    df_zc['day_hk']=pd.to_datetime(df_zc['day_hk']).dt.date
    df_yq['day_hk'] = pd.to_datetime(df_yq['day_hk']).dt.date
    df_tq['day_hk'] = pd.to_datetime(df_tq['day_hk']).dt.date
    df_plan['day_hk'] = pd.to_datetime(df_plan['day_hk']).dt.date

    df_hk_new = pd.merge(df_zc, df_yq, how='outer', on=['day_hk'])
    df_hk_new = pd.merge(df_hk_new, df_tq, how='outer', on=['day_hk'])
    df_hk_new = pd.merge(df_plan, df_hk_new, how='outer', on=['day_hk'])
    df_hk_new.fillna(0, inplace=True)
    df_hk_new['real_all'] = df_hk_new['zc_prin_inst_total'] + df_hk_new['tq_prin_inst_total'] + df_hk_new[
        'yq_prin_inst_total']
    df_hk_new['fund_name'] = fund_name
    df_hk_new = df_hk_new[
        ['fund_name', 'day_hk', 'plan_all', 'real_all', 'zc_prin_inst_total',
         'tq_prin_inst_total', 'yq_prin_inst_total']]
    df_hk_new.rename(columns={'day_hk': u'还款日期', 'plan_all': u'资金方应还本息', 'real_all': u'客户实还本息',
                              'zc_prin_inst_total': u'客户正常还款本息', 'yq_prin_inst_total': u'客户逾期还款本息',
                              'tq_prin_inst_total': u'客户提前还款本息'},
                     inplace=True)
    df_hk_new.to_excel(path + str(fund_code) + '.xlsx', index=None, columns=df_hk_new.columns,
                       encoding='utf8')


def xms_table(fund_code, fund_name):
    # 实还加入新马上
    read_ms_sql = '''
    SELECT '160' as fund_code,t1.`deadline`,t1.`repaid_at`,t1.`funding_principle`+t1.`funding_interest`+t1.`funding_service_fee` as prin_inst_total,t1.`qg_service_fee` real_service_fee ,t1.`qg_overdue_fee` real_punish
    FROM new_transaction.`new_ms_transaction_with_term` t1 JOIN loan_manifest t2 ON  cast(t1.loan_id as char) = t2.loan_id JOIN basic.loan_class t3 on t2.ref_id=t3.ref_id WHERE t3.class='%s'
    '''
    df_ms = pd.read_sql(read_ms_sql , con=engine_new_transaction)
    df_ms['repaid_at'].fillna('2016-01-01', inplace=True)
    df_ms['repaid_at'] = pd.to_datetime(df_ms['repaid_at']).apply(lambda x: x.date())
    df_ms['deadline'] = pd.to_datetime(df_ms['deadline']).apply(lambda x: x.date())
    # 正常还款，逾期还款，提前还款
    df_zc = df_ms.ix[df_ms['deadline'] == df_ms['repaid_at']]
    df_zc = df_zc.groupby(by=['repaid_at', 'fund_code'])['prin_inst_total'].sum()
    df_zc = df_zc.to_frame()
    df_zc.reset_index(inplace=True)
    df_zc.rename(columns={'repaid_at': 'day_hk', 'prin_inst_total': 'zc_prin_inst_total'}, inplace=True)
    df_zc['day_hk'] = pd.to_datetime(df_zc['day_hk']).apply(lambda x: x.date())
    df_zc['fund_code'] = df_zc['fund_code'].astype(int)
    # 提前还款
    df_tq = df_ms.ix[df_ms['deadline'] > df_ms['repaid_at']]
    # =================== TODO 本次修改的内容 begin
    # 将2月1号到期的提前还款放到1月31号
    df_tq.ix[(df_tq.deadline == datetime.date(2016, 2, 1)) & (
        df_tq.repaid_at <= datetime.date(2016, 1, 31)), 'deadline'] = datetime.date(2016, 1, 31)
    # 将5月1号到期的提前还款放到4月30号
    df_tq.ix[(df_tq.deadline == datetime.date(2016, 5, 1)) & (
        df_tq.repaid_at <= datetime.date(2016, 4, 30)), 'deadline'] = datetime.date(2016, 4, 30)
    # 将7月1号至5号到期的提前还款放到6月30号
    df_tq.ix[(df_tq.deadline >= datetime.date(2016, 7, 1)) & (df_tq.deadline <= datetime.date(2016, 7, 5)) & (
        df_tq.repaid_at <= datetime.date(2016, 6, 30)), 'deadline'] = datetime.date(2016, 6, 30)
    # 将10月1号至15号到期的提前还款放到9月30号
    df_tq.ix[(df_tq.deadline >= datetime.date(2016, 10, 1)) & (df_tq.deadline <= datetime.date(2016, 10, 15)) & (
        df_tq.repaid_at <= datetime.date(2016, 9, 30)), 'deadline'] = datetime.date(2016, 9, 30)
    # =================== TODO 本次修改的内容 end
    df_tq = df_tq.groupby(by=['deadline', 'fund_code'])['prin_inst_total'].sum()
    df_tq = df_tq.to_frame()
    df_tq.reset_index(inplace=True)
    df_tq['deadline'] = pd.to_datetime(df_tq['deadline']).apply(lambda x: x.date())
    df_tq.rename(columns={'deadline': 'day_hk', 'prin_inst_total': 'tq_prin_inst_total'}, inplace=True)
    df_tq['day_hk'] = pd.to_datetime(df_tq['day_hk']).apply(lambda x: x.date())
    df_tq['fund_code'] = df_tq['fund_code'].astype(int)
    # 逾期还款
    df_yq = df_ms.ix[df_ms['deadline'] < df_ms['repaid_at']]
    df_yq = df_yq.groupby(by=['repaid_at', 'fund_code'])['prin_inst_total'].sum()
    df_yq = df_yq.to_frame()
    df_yq.reset_index(inplace=True)
    df_yq.rename(columns={'repaid_at': 'day_hk', 'prin_inst_total': 'yq_prin_inst_total'}, inplace=True)
    df_yq['day_hk'] = pd.to_datetime(df_yq['day_hk']).apply(lambda x: x.date())
    df_yq['fund_code'] = df_yq['fund_code'].astype(int)
    # 时间+资金方生成
    list_date = pd.date_range('20160101', '20170710')
    df_date = pd.DataFrame(list_date)
    df_date.columns = ['day_hk']
    df_date.reset_index(inplace=True)
    df_date['day_hk'] = pd.to_datetime(df_date['day_hk']).apply(lambda x: x.date())
    fund_id = [160]
    df_date = pd.DataFrame(list(itertools.product(df_date['day_hk'].values.tolist(), fund_id)),
                           columns=['day_hk', 'fund_code'])
    df_date['day_hk'] = pd.to_datetime(df_date['day_hk']).apply(lambda x: x.date())
    df_date['fund_code'] = df_date['fund_code'].astype(int)
    # 合并实还本息
    # df_out = pd.DataFrame()
    df_out = pd.merge(df_date, df_zc, how='left', on=['day_hk', 'fund_code'])
    df_out = pd.merge(df_out, df_tq, how='left', on=['day_hk', 'fund_code'])
    df_out = pd.merge(df_out, df_yq, how='left', on=['day_hk', 'fund_code'])
    df_out.fillna(0, inplace=True)
    df_out['prin_inst_real'] = df_out['zc_prin_inst_total'] + df_out['yq_prin_inst_total'] + df_out[
        'tq_prin_inst_total']
    df_out.fillna(0, inplace=True)
    df_out['day_hk'] = pd.to_datetime(df_out['day_hk']).apply(lambda x: x.date())
    df_out['fund_code'] = df_out['fund_code'].astype(int)
    # 客户应还本息
    read_1_sql = '''
    SELECT DATE(deadline) day_hk,sum(`principle`+`interest_new`+`funding_service_fee`/4) prin_inst_plan FROM new_transaction.`new_ms_repayment_plan` nmrp  JOIN loan_manifest lm ON nmrp.loan_id = lm.loan_id JOIN basic.loan_class lc ON lm.ref_id = lc.ref_id WHERE lc.class='%s'  group by date(deadline)
    '''
    df_plan = pd.read_sql(read_1_sql , con=engine_new_transaction)
    df_plan.fillna(0, inplace=True)
    df_plan['day_hk'] = pd.to_datetime(df_plan['day_hk']).apply(lambda x: x.date())
    df_plan['day_hk'] = df_plan['day_hk'].apply(lambda x: x + datetime.timedelta(days=85))
    df_plan['fund_code'] = 160
    df_plan['prin_inst_plan'] = df_plan['prin_inst_plan'].astype(float)
    df_out['day_hk'] = pd.to_datetime(df_out['day_hk']).apply(lambda x: x.date())
    df_out['fund_code'] = df_out['fund_code'].astype(int)
    # 合并新马上
    df_out = pd.merge(df_out, df_plan, how='left', on=['day_hk', 'fund_code'])
    df_out.fillna(0, inplace=True)
    df_out['channel_name'] = '新马上'
    df_out.rename(columns={'day_hk': u'客户还款日期', 'zc_prin_inst_total': u'客户正常还款本息', 'tq_prin_inst_total': u'客户提前还款本息',
                           'yq_prin_inst_total': u'客户逾期还款本息',
                           'prin_inst_plan': u'客户应还本息',
                           'prin_inst_real': u'客户实还本息',
                           'fund_amount': u'资金方本息'}, inplace=True)
    df_out.to_excel(path + str(fund_code) +  '.xlsx', index=None, columns=df_out.columns,
                    encoding='utf8')
    # excel_file.save()
    # df_out.to_excel(excel_file, sheet_name=fund_name, columns=df_out.columns, index=None, encoding='utf8')

if __name__ == '__main__':
    # pool = mtp.Pool(processes=6)
    # for index, row in df_fund.iterrows():
    #     fund_code = int(row['fund_code'])
    #     fund_name = row['fund_name']
    #     # other_table(fund_code,fund_name)
    #     # hr_table(fund_code,fund_name)
    #     print '------begin---', fund_name
    # pool = mtp.Pool(processes=4)
    # for index, row in df_fund.iterrows():
    #     fund_code = int(row['fund_code'])
    #     fund_name = row['fund_name']
    #     print '------begin---', fund_name
    #     if fund_code in (200, 290, 330):
    #         pool.apply_async(ky_table, (fund_code, fund_name))
    #     elif fund_code == 150:
    #         pool.apply_async(hr_table, (fund_code, fund_name))
    #     elif fund_code == 160:
    #         # pool.apply_async(xms_table, (fund_code, fund_name))
    #         print '----------new_ma_shang-------------'
    #     else:
    #         # 其他资金方
    #         pool.apply_async(other_table, (fund_code, fund_name))
    # pool.close()
    # pool.join()

    print '=====================main done======================'

    # #============= 合并文件 begin=============
    excel_out = pd.ExcelWriter(path + 'all.xlsx')
    df_out = pd.DataFrame()
    df_tmp = pd.DataFrame()
    for file in os.listdir(path):
        tmp_file = file.replace('.xlsx', '')
        if tmp_file in ['160'] > 0:
           df_tmp = df_tmp.append(pd.read_excel(path + file))
        elif tmp_file == 'all':
           continue
        else:
           df_out = df_out.append(pd.read_excel(path + file))
    df_tmp.to_excel(excel_out, sheet_name='160', columns=df_tmp.columns, index=None, encoding='utf8')
    df_out.to_excel(excel_out, sheet_name='sheet1', columns=df_out.columns, index=None, encoding='utf8')
    excel_out.save()
    # ============= 合并文件 end=============
