# coding: utf-8
import sys
import datetime
import pandas as pd
from sqlalchemy import create_engine

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

# '''
# 1、2017-05 月份之后的华融赔付 增加了提前结算功能
# 华融加入了提前结清的规则。
# 提前结清：如果用户提前还款完成，则打款给资金方；
# 如果用户还有期数未还，则这笔不进行提前结清。
# 每周周四进行 上周四到本周三的提前结清。
# '''
engine_new_transaction = create_engine(
    'mysql+mysqldb://yulong:ohVU1nPITynSZwR2@172.16.3.201:3306/new_transaction?charset=utf8', echo=True)


start_loan_paid_at = "2017-01-01"
end_loan_paid_at = "2017-10-01"
# 还资金方----2017.5.4包含有历史提前结算
sql_fund_hk_hr = '''
SELECT DATE(t1.repaid_at) day_hk,SUM(t1.principle)+SUM(t1.interest) plan_all
FROM audit.`funding_repayment_record` t1
 JOIN  audit.loan_manifest t2 ON t1.ref_id = t2.ref_id
WHERE  t1.is_active = 1 and t1.funding_code = %s AND t1.deadline>="2017-05-01" AND (t1.deadline< '%s' OR t1.settle_status=1)
and t1.repaid_at >= '%s' AND t1.repaid_at < '%s'
AND t2.is_active in (1,-2) AND t2.loan_paid_at>='%s' AND t2.loan_paid_at<'%s'
GROUP BY DATE(t1.repaid_at)
'''

# 九月份实还中 应还包含8月31号的 这部分剔除因为已经在八月赔付包含
sql_fund_hk_jinshang = '''
SELECT DATE(t1.repaid_at) day_hk,SUM(t1.principle)+SUM(t1.interest) plan_all
FROM audit.`funding_repayment_record` t1
  JOIN  audit.loan_manifest t2 ON t1.ref_id = t2.ref_id
WHERE  t1.is_active = 1 and t1.funding_code = %s AND ((t1.deadline>="2017-09-01" AND t1.deadline< '%s') OR t1.settle_status=1)
and t1.repaid_at >= '%s' AND t1.repaid_at < '%s'
AND t2.is_active in (1,-2) AND t2.loan_paid_at>='%s' AND t2.loan_paid_at<'%s'
GROUP BY DATE(t1.repaid_at)
'''

# 客户正常还款+逾期还款，按照还款时间统计即可
sql_hk_online_zc = '''
SELECT DATE(t5.bill_time) day_hk ,sum(t1.`principle`+t1.`interest`-t1.`mitigate_principle`-t1.`mitigate_interest`) zc_real_all
FROM `xjd_repay_plan_repay_record_ref` t1
JOIN new_transaction.`user_repayment_plan` t3 ON t1.plan_id = t3.id and t3.fund_code = %s AND t1.repay_channel != 13
JOIN audit.funding_repayment_record t4 ON t4.ref_id = t3.ref_id AND t4.term_no = t3.term_no
JOIN new_transaction.repay_record_online t5 ON t1.record_id = t5.id AND  t1.repay_channel = t5.pay_approach and DATE(t5.bill_time) = DATE(t4.deadline)
AND t5.bill_time >= '%s' and t5.bill_time < '%s'
JOIN  audit.loan_manifest t6 ON t3.ref_id = t6.ref_id AND t6.is_active in (1,-2) AND t6.loan_paid_at>='%s' AND t6.loan_paid_at<'%s'
GROUP by 1
'''
# 线上还款-逾期还款
sql_hk_online_yq = '''
SELECT DATE(t5.bill_time) day_hk, sum(t1.`principle`+t1.`interest`-t1.`mitigate_principle`-t1.`mitigate_interest`) yq_real_all
FROM `xjd_repay_plan_repay_record_ref` t1
JOIN new_transaction.`user_repayment_plan` t3 ON t1.plan_id = t3.id and t3.fund_code = %s AND t1.repay_channel != 13
JOIN audit.funding_repayment_record t4 ON t4.ref_id = t3.ref_id AND t4.term_no = t3.term_no
JOIN new_transaction.repay_record_online t5 ON t1.record_id = t5.id AND  t1.repay_channel = t5.pay_approach and DATE(t5.bill_time) > DATE(t4.deadline)
AND t5.bill_time >= '%s' and t5.bill_time < '%s'
JOIN  audit.loan_manifest t6 ON t3.ref_id = t6.ref_id AND t6.is_active in (1,-2) AND t6.loan_paid_at>='%s' AND t6.loan_paid_at<'%s'
GROUP by 1
'''

sql_online_tq = '''
SELECT DATE(t4.deadline) deadline, sum(t1.`principle`+t1.`interest`-t1.`mitigate_principle`-t1.`mitigate_interest`) tq_amount
FROM `xjd_repay_plan_repay_record_ref` t1
JOIN new_transaction.`user_repayment_plan` t3 ON t1.plan_id = t3.id and t3.fund_code = %s AND t1.repay_channel != 13
JOIN audit.funding_repayment_record t4 ON t4.ref_id = t3.ref_id AND t4.term_no = t3.term_no   AND (t4.settle_status = 0 OR t4.repaid_at<"2017-05-01" )
join audit.loan_manifest t5 on t3.ref_id = t5.ref_id
JOIN new_transaction.repay_record_online t6 ON t1.record_id = t6.id AND  t1.repay_channel = t6.pay_approach AND  t6.bill_time < '%s' AND DATE(t6.bill_time) < DATE(t4.deadline)
WHERE t5.is_active in (1,-2) AND t5.loan_paid_at>='%s' AND t5.loan_paid_at<'%s'
GROUP BY 1
'''

sql_online_settle_tq = '''
SELECT DATE(t4.settle_date) repaid_at, SUM(t1.`principle`+t1.`interest`-t1.`mitigate_principle`-t1.`mitigate_interest`) settle_tq_amount
FROM `xjd_repay_plan_repay_record_ref` t1
JOIN new_transaction.`user_repayment_plan` t3 ON t1.plan_id = t3.id  and t3.fund_code = %s AND t1.repay_channel != 13
JOIN audit.funding_repayment_record t4 ON t4.ref_id = t3.ref_id AND t4.term_no = t3.term_no  AND t4.settle_status = 1
join audit.loan_manifest t5 on t3.ref_id = t5.ref_id
JOIN new_transaction.repay_record_online t6 ON t1.record_id = t6.id AND  t1.repay_channel = t6.pay_approach AND t6.bill_time < '%s'
WHERE t5.is_active in (1,-2) AND t5.loan_paid_at>='%s' AND t5.loan_paid_at<'%s'
GROUP BY 1
'''

# 支付宝正常还款
sql_hk_ali_zc = '''
SELECT DATE(t2.transfer_time) day_hk, sum(t1.`principle`+t1.`interest`-t1.`mitigate_principle`-t1.`mitigate_interest`) zc_real_all
FROM `xjd_repay_plan_repay_record_ref` t1
join new_transaction.offline_alipay_record t2 on t1.record_id = t2.id and t1.repay_channel = 13 and t2.transfer_time >= '%s' and t2.transfer_time < '%s'
JOIN new_transaction.`user_repayment_plan` t3 ON t1.plan_id = t3.id  and t3.fund_code = %s
JOIN audit.funding_repayment_record t4 ON t4.ref_id = t3.ref_id AND t4.term_no = t3.term_no and DATE(t2.transfer_time) = DATE(t4.deadline)
JOIN  audit.loan_manifest t5 ON t3.ref_id = t5.ref_id AND t5.is_active in (1,-2) AND t5.loan_paid_at>='%s' AND t5.loan_paid_at<'%s'
GROUP by 1
'''

# 支付宝还款--逾期还款
sql_hk_ali_yq = '''
SELECT DATE(t2.transfer_time) day_hk, sum(t1.`principle`+t1.`interest`-t1.`mitigate_principle`-t1.`mitigate_interest`) yq_real_all
FROM `xjd_repay_plan_repay_record_ref` t1
join new_transaction.offline_alipay_record t2 on t1.record_id = t2.id and t1.repay_channel = 13 and t2.transfer_time >= '%s' and t2.transfer_time < '%s'
JOIN new_transaction.`user_repayment_plan` t3 ON t1.plan_id = t3.id  and t3.fund_code = %s
JOIN audit.funding_repayment_record t4 ON t4.ref_id = t3.ref_id AND t4.term_no = t3.term_no and DATE(t2.transfer_time) > DATE(t4.deadline)
JOIN  audit.loan_manifest t5 ON t3.ref_id = t5.ref_id AND t5.is_active in (1,-2) AND t5.loan_paid_at>='%s' AND t5.loan_paid_at<'%s'
GROUP by 1
'''

# 提前还款特殊，需要提取明细

sql_ali_tq = '''
SELECT DATE (t4.deadline) deadline, SUM(t1.`principle`+t1.`interest`-t1.`mitigate_principle`-t1.`mitigate_interest`) tq_amount
FROM `xjd_repay_plan_repay_record_ref` t1
join new_transaction.offline_alipay_record t2 on t1.record_id = t2.id and t1.repay_channel = 13 and  t2.transfer_time < '%s'
JOIN new_transaction.`user_repayment_plan` t3 ON t1.plan_id = t3.id and t3.fund_code = %s
JOIN audit.funding_repayment_record t4 ON t4.ref_id = t3.ref_id AND t4.term_no = t3.term_no  AND (t4.settle_status = 0 OR t4.repaid_at<"2017-05-01" ) AND DATE(t2.transfer_time) < DATE(t4.deadline)
join audit.loan_manifest t5 on t3.ref_id = t5.ref_id AND t5.is_active in (1,-2) AND t5.loan_paid_at>='%s' AND t5.loan_paid_at<'%s'
GROUP BY 1
'''

sql_ali_settle_tq = '''
SELECT DATE(t4.settle_date) repaid_at, SUM(t1.`principle`+t1.`interest`-t1.`mitigate_principle`-t1.`mitigate_interest`) settle_tq_amount
FROM `xjd_repay_plan_repay_record_ref` t1
join new_transaction.offline_alipay_record t2 on t1.record_id = t2.id and t1.repay_channel = 13 and  t2.transfer_time < '%s'
JOIN new_transaction.`user_repayment_plan` t3 ON t1.plan_id = t3.id and t3.fund_code = %s
JOIN audit.funding_repayment_record t4 ON t4.ref_id = t3.ref_id AND t4.term_no = t3.term_no  AND t4.settle_status = 1
join audit.loan_manifest t5 on t3.ref_id = t5.ref_id AND t5.is_active in (1,-2) AND t5.loan_paid_at>='%s' AND t5.loan_paid_at<'%s'
GROUP BY 1
'''


def hr_table(fund_code, fund_name, start_time, end_time, truncate_start_time, truncate_end_time, deadline):
    df_online_zc = pd.read_sql(sql_hk_online_zc % (fund_code, start_time, end_time, start_loan_paid_at, end_loan_paid_at), engine_new_transaction)
    df_online_yq = pd.read_sql(sql_hk_online_yq % (fund_code, start_time, end_time, start_loan_paid_at, end_loan_paid_at), engine_new_transaction)
    df_ali_zc = pd.read_sql(sql_hk_ali_zc % (start_time, end_time, fund_code, start_loan_paid_at, end_loan_paid_at), engine_new_transaction)
    df_ali_yq = pd.read_sql(sql_hk_ali_yq % (start_time, end_time, fund_code, start_loan_paid_at, end_loan_paid_at), engine_new_transaction)
    if fund_code == 300:
        df_fund = pd.read_sql(sql_fund_hk_jinshang % (fund_code, deadline, start_time, end_time, start_loan_paid_at ,end_loan_paid_at),engine_new_transaction)
    else:
        df_fund = pd.read_sql(sql_fund_hk_hr % (fund_code, deadline, start_time, end_time, start_loan_paid_at ,end_loan_paid_at), engine_new_transaction)

    # 正常 逾期 提前
    df_zc = pd.concat([df_online_zc, df_ali_zc], axis=0, ignore_index=True)
    df_zc['day_hk'] = pd.to_datetime(df_zc['day_hk']).apply(lambda x: x.date())
    # if fund_code == 150:
    #     df_zc.ix[(df_zc.day_hk < datetime.date(2017, 5, 1)) & (
    #     df_zc.day_hk >= datetime.date(2017, 4, 29)), 'day_hk'] = datetime.date(2017, 5, 1)
        # df_zc.ix[(df_zc.day_hk < datetime.date(2017, 10, 5)) & (df_zc.day_hk >= datetime.date(2017, 10, 1)), 'day_hk'] = datetime.date(2017, 9, 30)
    # elif fund_code == 300:
        # df_zc.ix[(df_zc.day_hk < datetime.date(2017, 9, 1)) & (
        # df_zc.day_hk >= datetime.date(2017, 8, 31)), 'day_hk'] = datetime.date(2017, 9, 1)
        # df_zc.ix[(df_zc.day_hk < datetime.date(2017, 10, 5)) & (df_zc.day_hk >= datetime.date(2017, 10, 1)), 'day_hk'] = datetime.date(2017, 9, 30)
    df_zc = df_zc.groupby('day_hk')['zc_real_all'].sum().reset_index()

    df_yq = pd.concat([df_online_yq, df_ali_yq], axis=0, ignore_index=True)
    df_yq['day_hk'] = pd.to_datetime(df_yq['day_hk']).apply(lambda x: x.date())
    # if fund_code == 150:
    #     df_yq.ix[(df_yq.day_hk < datetime.date(2017, 5, 1)) & (
    #     df_yq.day_hk >= datetime.date(2017, 4, 29)), 'day_hk'] = datetime.date(2017, 5, 1)
        # df_yq.ix[(df_yq.day_hk < datetime.date(2017, 10, 5)) & (df_yq.day_hk >= datetime.date(2017, 10, 1)), 'day_hk'] = datetime.date(2017, 9, 30)
    # elif fund_code == 300:
    #     df_yq.ix[(df_yq.day_hk < datetime.date(2017, 9, 1)) & (
    #     df_yq.day_hk >= datetime.date(2017, 8, 31)), 'day_hk'] = datetime.date(2017, 9, 1)
        # df_yq.ix[(df_yq.day_hk < datetime.date(2017, 10, 5)) & (df_yq.day_hk >= datetime.date(2017, 10, 1)), 'day_hk'] = datetime.date(2017, 9, 30)

    df_yq = df_yq.groupby('day_hk')['yq_real_all'].sum().reset_index()

    # 提前还款 到期可用
    df_online_tq = pd.read_sql(sql_online_tq % (fund_code, end_time, start_loan_paid_at, end_loan_paid_at), engine_new_transaction)
    df_ali_tq = pd.read_sql(sql_ali_tq % (end_time, fund_code, start_loan_paid_at ,end_loan_paid_at), engine_new_transaction)
    df_tq = pd.concat([df_online_tq, df_ali_tq], axis=0, ignore_index=True)
    df_tq.rename(columns={"deadline": "day_hk"}, inplace=True)
    df_tq['day_hk'] = pd.to_datetime(df_tq['day_hk']).apply(lambda x: x.date())
    # if fund_code == 150:
    #     df_tq.ix[(df_tq.day_hk < datetime.date(2017, 5, 1)) & (
    #     df_tq.day_hk >= datetime.date(2017, 4, 29)), 'day_hk'] = datetime.date(2017, 5, 1)
        # df_tq.ix[(df_tq.day_hk < datetime.date(2017, 10, 5)) & (df_tq.day_hk >= datetime.date(2017, 10, 1)), 'day_hk'] = datetime.date(2017, 9, 30)
    # elif fund_code == 300:
    #     df_tq.ix[(df_tq.day_hk < datetime.date(2017, 9, 1)) & (
    #     df_tq.day_hk >= datetime.date(2017, 8, 31)), 'day_hk'] = datetime.date(2017, 9, 1)
        # df_tq.ix[(df_tq.day_hk < datetime.date(2017, 10, 5)) & (df_tq.day_hk >= datetime.date(2017, 10, 1)), 'day_hk'] = datetime.date(2017, 9, 30)

    df_tq = df_tq.groupby('day_hk')['tq_amount'].sum().reset_index()

    # 提前还款结清
    df_online_settle_tq = pd.read_sql(sql_online_settle_tq % (fund_code, end_time, start_loan_paid_at, end_loan_paid_at), engine_new_transaction)
    df_ali_settle_tq = pd.read_sql(sql_ali_settle_tq % (end_time, fund_code, start_loan_paid_at,end_loan_paid_at), engine_new_transaction)
    df_settle_tq = pd.concat([df_online_settle_tq, df_ali_settle_tq], axis=0, ignore_index=True)
    df_settle_tq.rename(columns={"repaid_at": "day_hk"}, inplace=True)
    df_settle_tq['day_hk'] = pd.to_datetime(df_settle_tq['day_hk']).apply(lambda x: x.date())
    df_settle_tq = df_settle_tq.groupby('day_hk')['settle_tq_amount'].sum().reset_index()

    # 合并所有的还款数据
    df_fund.day_hk = pd.to_datetime(df_fund.day_hk).dt.date
    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_settle_tq.day_hk = pd.to_datetime(df_settle_tq.day_hk).dt.date
    df = pd.merge(df_fund, df_zc, on='day_hk', how='outer')
    df = pd.merge(df, df_yq, on='day_hk', how='outer')
    df = pd.merge(df, df_tq, on='day_hk', how='outer')
    df = pd.merge(df, df_settle_tq, on='day_hk', how='outer')
    df.fillna(0, inplace=True)
    df.sort_values(['day_hk'], inplace=True)
    df = df.ix[(df.day_hk >= truncate_start_time) & (df.day_hk < truncate_end_time)]
    df['fund_name'] = fund_name
    # 包含提前还款到期可用 以及提前结清数据
    df['real_all'] = df['zc_real_all'] + df['yq_real_all'] + df['settle_tq_amount'] + df['tq_amount']
    df = df[
        ['fund_name', 'day_hk', 'plan_all', 'real_all', 'zc_real_all', 'yq_real_all', 'settle_tq_amount', 'tq_amount']]

    df['peifu'] = df['plan_all'] - df['real_all']
    df.rename(columns={'fund_name': '资金方', 'day_hk': '日期', 'plan_all': '应还资金方本息', 'real_all': '客户实还本息可用',
                       'zc_real_all': '客户正常还款本息', 'yq_real_all': '当天客户逾期还款本息',
                       'tq_amount': '客户提前还款本息当天到期可用', 'settle_tq_amount': '客戶提前結清本息', 'peifu': '当日不可用累计本息'},
              inplace=True)
    return df


# 华融5月份的还资金方 包含应还日deadline在 4月 29 30 号的数据
# 华融9月份的还资金方 包含应还日deadline在 10月 1 2 3 4 号的数据
# 晋商2【funding_code 300】 9月份的还资金方 包含应还日deadline在 8月31号 10月 1 2 3 4 号的数据
if __name__ == '__main__':
    fund_code_map = {
        # (150, u'华融'): {
        #     "start_time": datetime.date(2017, 5, 1)
        #     , "end_time": datetime.date(2017, 10, 1)
        #     , "deadline": datetime.date(2017, 10, 1)
        #     , "truncate_start_time": datetime.date(2017, 5, 1)
        #     , "truncate_end_time": datetime.date(2017, 10, 1)
        # },
        # (150, u'华融'): {
        #     "start_time": datetime.date(2017, 4, 29)
        #     , "end_time": datetime.date(2017, 10, 1)
        #     , "deadline": datetime.date(2017, 10, 1)
        #     , "truncate_start_time": datetime.date(2017, 5, 1)
        #     , "truncate_end_time": datetime.date(2017, 10, 1)
        # }
        # ,
        (300, u'晋商2'): {
            "start_time": datetime.date(2017, 9, 1)
            , "end_time": datetime.date(2017, 10, 1)
            , "deadline": datetime.date(2017, 10, 1)
            , "start_deadline": datetime.date(2017, 9, 1)
            , "truncate_start_time": datetime.date(2017, 9, 1)
            , "truncate_end_time": datetime.date(2017, 10, 1)
        }
    }

    try:
        for fund_tuple, time_list in fund_code_map.iteritems():
            print '------begin---', fund_tuple[1]
            df_total = hr_table(fund_tuple[0], fund_tuple[1], time_list["start_time"], time_list["end_time"],
                                time_list["truncate_start_time"], time_list["truncate_end_time"], time_list["deadline"])
            df_total.to_excel('./' + str(fund_tuple[0]) + '_settle_bill_time_loan_paid_at.xlsx', index=None, columns=df_total.columns,
                              encoding='utf8')
    except Exception, e:
        print e.message
        raise e

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