# -*- coding: utf-8 -*-
import os
import sys
import datetime
import threading
from itertools import product
from threading import Thread

import pandas as pd
from dateutil.relativedelta import relativedelta

from sqlalchemy import create_engine
import multiprocessing as mtp

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

'''
现金贷还款表
1、现金贷还款汇总
2、还款时间使用账单时间
'''

read_fund_sql = '''
select funding_code fund_corp_id,name fund_name from basic.funding_corp where funding_code not in (0,160,40,60)
'''
read_repay_sql = '''
SELECT id,`approach_name` FROM new_transaction.`repay_channel`
'''

engine_new_transaction = engine_new_transaction = create_engine(
    'mysql+mysqldb://internal_r:ArbNgtvlJzZHXsEu@172.16.3.201:3306/new_transaction?charset=utf8',
    echo=True)

df_fund = pd.read_sql(read_fund_sql, con=engine_new_transaction)
df_approach = pd.read_sql(read_repay_sql, engine_new_transaction)
df_approach['id'] = df_approach['id'].astype(int)

path = './'
# AND t1.id NOT in (17978225,17978223,17978227,17978229,17978231,17978233,17978235,17978237,17978239,17978241,17978243,17978245,17978247,17978249,17978251,17978253,17978255,17978257,17978259,17978267,17978265,17978263,17978261,17978269,17978271,17978273,17978275,17978277,17978279,17978281,17978283,17978285,17978287,17978289,17978291,17978293,17978297,17978295,17978299,17978301,17978303,17978305,17978307,17978309,17978311,17978313,17978315,17978317,17978319,17978321,17978323,17978325,17978327,17978329,17978331,17978333,17978335,17978337,17978339,17978341,17978343,17978345,17978347,17978349,17978351,17978353,17978355,17978357,17978359,17978361,17978363,17978365,17978367,17978369,17978371,17978373,17978375,17978377,17978381,17978379,17978383,17978385,17978387,17978389,17978391,17978393,17978395,17978397,17978405,17978399,17978401,17978403,17978407,17978409,17978413,17978411,17978415,17978417,17978419,17978421,17978423,17978425,17978427,17978429,17978431,17978433,17978435,17978437,17978439,17978441,17978443,17978445,17978447,17978453,17978449,17978451,17978455,17978457,17978459,17978461,17978463,17978465,17978467,17978469,17978471,17978473,17978475,17978477,17978479,17978481,17978483,17978485,17978487,17978489,17978491,17978493,17978495,17978497,17978499,17978501,17978503,17978505,17978507,17978509,17688805,17978511,17978513,17978515,17978517,17978519,17978521,17978523,17978525,17978527,17978529,17978531,17688803,17978533,17978535,17978537,17978539,17978543,17978541,17978545,17978547,17978549,17978553,17978551,17978555,17978557,17978559,17978561,17978563,17978565,17978573,17978567,17978569,17978571,17978575,17978577,17978579,17978581,17978585,17978587,17978583,17978589,17978591,17978593,17978595,17978597,17978599,17978601,17978605,17978603,17978607,17978609,17978611,17978613,17978615,17978617,17978619,17978621,17978623,17978625,17978627,17978629,17978633,17978631,17978635,17978637,17978639,17978641,17978643,17978645,17978647,17978649,17978651,17978653,17978655,17978657,17978659,17978661,17978663,17978665,17978667,17978669,17978671,17978673,17978675,17978677,17978679,17978681,17978683,17978685,17978687,17978689,17978691,17978693,17978209,17978695,17948017,17978205,17978781,17978783,17978697,17978699,17978701,17978703,17978793,17978789,17978791,17978705,17978707,17978709,17978711,17978713,17978715,17978717,17978719,17978721,17978723,17978725,17978727,17978729,17978731,17978733,17978735,17978737,17978739,17978741,17978743,17978745,17978747,17978749,17978751,17978753,17978755,17978757,17978759,17978761,17978763,17978765,17978767,17978769,17978771,17978773,17978775,17978777,17978779,19732901,19732903,19732905,19732909,19732907,19732915,19732913,19732917,19732911,19732919,19732921,19732923,19732925,19732927,19732929,19732931,19732933,19732935,19732937,19733029,19732939,19732941,19732949,19732943,19732945,19732947,19732951,19732959,19732955,19732963,19732957,19732961,19732953,19732967,19732965,19732969,19732971,19732973,19733025,19733027,19732975,19732977,19732979,19732981,19732983,19732985,19732987,19732989,19732991,19732993,19732995,19732997,19732999,19733001,19733003,19733005,19733007,19733009,19733031,19733033,19733035,19733011,19733013,19733015,19733017,19733021,19733019,19733023,21613351,21613339,21613341,21613343,21613393,21613359,21613361)

################################################
# 收客户还款汇总
def hk_hz():
    sql_alipay = """select year(t8.loan_paid_at) l_year,month(t8.loan_paid_at) l_month, year(t3.transfer_time) r_year, month(t3.transfer_time) r_month, t6.name funding_name,  t7.approach_name, sum(t1.ref_amount) 总额, sum(t1.principle) 本金, sum(t1.interest) 利息, sum(t1.service_fee) 服务费,
    sum(t1.collection_relief) 催收减免, -- sum(t1.red_package_fee) 实还红包减免,
    sum(t1.punish) 逾期罚款收益, sum(t1.remain_income) 其他收益, sum(mitigate_principle) 减免本金, sum(mitigate_interest) 减免利息, sum(mitigate_service_fee) 减免服务费, sum(mitigate_collection_relief) 减免罚息, SUM(t1.residual_service_fee) 剩余未还服务费
     from xjd_repay_plan_repay_record_ref t1
    join user_repayment_plan t2 on t2.id = t1.plan_id
    join basic.funding_corp t6 on t2.fund_code=t6.funding_code
    join new_transaction.repay_channel t7 on t1.repay_channel = t7.id
    join audit.loan_manifest t8 on t8.ref_id = t2.ref_id
    join new_transaction.offline_alipay_record t3 on t1.repay_channel =13 and t3.id = t1.record_id
    group by 1,2,3,4,5,6"""

    df_alipay = pd.read_sql(sql=sql_alipay, con=engine_new_transaction)
    sql_bank = """select  year(t8.loan_paid_at) l_year,month(t8.loan_paid_at) l_month,year(t3.transfer_time) r_year, month(t3.transfer_time) r_month, t6.name funding_name, t7.approach_name, sum(t1.ref_amount) 总额, sum(t1.principle) 本金, sum(t1.interest) 利息, sum(t1.service_fee) 服务费,
    sum(t1.collection_relief) 催收减免, -- sum(t1.red_package_fee) 实还红包减免,
    sum(t1.punish) 逾期罚款收益, sum(t1.remain_income) 其他收益, sum(mitigate_principle) 减免本金, sum(mitigate_interest) 减免利息, sum(mitigate_service_fee) 减免服务费, sum(mitigate_collection_relief) 减免罚息, SUM(t1.residual_service_fee) 剩余未还服务费
     from xjd_repay_plan_repay_record_ref t1
    join user_repayment_plan t2 on t2.id = t1.plan_id
    join basic.funding_corp t6 on t2.fund_code=t6.funding_code
    join new_transaction.repay_channel t7 on t1.repay_channel = t7.id
    join audit.loan_manifest t8 on t8.ref_id = t2.ref_id
    join new_transaction.offline_bank_repay_record t3 on t1.repay_channel in (14,15,16) and t3.id = t1.record_id
    group by 1,2,3,4,5,6"""
    df_bank = pd.read_sql(sql=sql_bank, con=engine_new_transaction)
    sql_online = """select year(t8.loan_paid_at) l_year,month(t8.loan_paid_at) l_month,year(t3.bill_time) r_year, month(t3.bill_time) r_month, t6.name funding_name, t7.approach_name, sum(t1.ref_amount) 总额, sum(t1.principle) 本金, sum(t1.interest) 利息, sum(t1.service_fee) 服务费,
    sum(t1.collection_relief) 催收减免, -- sum(t1.red_package_fee) 实还红包减免,
    sum(t1.punish) 逾期罚款收益, sum(t1.remain_income) 其他收益, sum(mitigate_principle) 减免本金, sum(mitigate_interest) 减免利息, sum(mitigate_service_fee) 减免服务费, sum(mitigate_collection_relief) 减免罚息, SUM(t1.residual_service_fee) 剩余未还服务费
    from xjd_repay_plan_repay_record_ref t1
    join user_repayment_plan t2 on t2.id = t1.plan_id
    join basic.funding_corp t6 on t2.fund_code=t6.funding_code
    join new_transaction.repay_channel t7 on t1.repay_channel = t7.id
    join audit.loan_manifest t8 on t8.ref_id = t2.ref_id
    join repay_record_online t3 on t1.repay_channel = t3.pay_approach and t3.id = t1.record_id
    group by 1,2,3,4,5,6"""
    df_online = pd.read_sql(sql=sql_online, con=engine_new_transaction)

    sql_dunjiao_serivce_fee = """
    SELECT
	YEAR (t3.loan_paid_at) l_year,
	MONTH (t3.loan_paid_at) l_month,
	YEAR (t4.bill_time) r_year,
	MONTH (t4.bill_time) r_month,
	t5. NAME funding_name,
	t6.approach_name,
	sum(t1.ref_amount) "实还趸收服务费"
FROM
	new_transaction.xjd_dunjiao_plan_ref t1
JOIN new_transaction.xjd_dunjiao_plan t2 ON t1.plan_id = t2.id
JOIN audit.loan_manifest t3 ON t2.ref_id = t3.ref_id
JOIN repay_record_online t4 ON t1.record_id = t4.id
JOIN  basic.funding_corp t5 ON t5.funding_code = 150
JOIN new_transaction.repay_channel t6 ON t6.id = t1.repay_channel
AND t4.pay_approach = t1.repay_channel
GROUP BY
	1,
	2,
	3,
	4,
	5,
	6
    """
    df_dunjiao = pd.read_sql(sql=sql_dunjiao_serivce_fee, con=engine_new_transaction)

    df = pd.concat([df_alipay, df_bank, df_online], ignore_index=True)
    # df = pd.concat([df_alipay], ignore_index=True)
    df = pd.merge(df, df_dunjiao, how='left', on=['l_year', 'l_month', 'r_year', 'r_month', 'funding_name', 'approach_name'])
    df[u'实还趸收服务费'].fillna(value=0, inplace=True)
    df[u'总额'] = df[u'总额']+df[u'实还趸收服务费']
    df.sort_values(['l_year', 'l_month', 'r_year', 'r_month', 'funding_name', 'approach_name'], inplace=True)
    df.rename(columns={'l_year': '放款年', 'l_month': '放款月', 'r_year': '还款年', 'r_month': '还款月', 'funding_name': '资金方',
                       'approach_name': '收款账户'}, inplace=True)
    excel_file = pd.ExcelWriter(path + u'现金贷还款表汇总_new.xlsx')
    df.to_excel(excel_file, 'Sheet1',
                columns=['放款年', '放款月', '还款年', '还款月', '资金方', '收款账户', '总额', '本金', '利息', '服务费', '催收减免', '逾期罚款收益', '其他收益',
                         '减免本金', '减免利息', '减免服务费', '减免罚息','剩余未还服务费', '实还趸收服务费'], index=None)
    excel_file.save()


################################################
# 明细
read_detail_online_sql = '''
SELECT t1.id plan_id,t1.fund_code,t2.repay_channel,t1.real_loan_id loan_id,t3.contract_loan_amount,t3.contract_term,t3.loan_paid_at,t1.term_no,
t1.deadline,t6.bill_time repaid_at,t1.required_repayment,t1.principle,t1.interest,t1.service_fee,t1.should_punish,t2.ref_amount,t2.principle real_principle,t2.interest real_interest,
t2.`service_fee` real_service_fee,t2.`punish` real_punish,t2.collection_relief,
t2.remain_income,t2.`mitigate_principle`,t2.`mitigate_interest`,t2.`mitigate_service_fee`,t2.`mitigate_collection_relief` mitigate_punish
FROM `xjd_repay_plan_repay_record_ref` t2
JOIN `user_repayment_plan` t1 ON t1.id=t2.plan_id
JOIN audit.`loan_manifest` t3 ON t1.ref_id=t3.ref_id
JOIN repay_record_online t6 ON t2.record_id=t6.id AND t2.pay_approach = t2.repay_channel
WHERE  t6.bill_time >= '%s' and t6.bill_time < '%s'
'''

read_detail_alipay_sql = '''
SELECT t1.id plan_id,t1.fund_code,t2.repay_channel,t1.real_loan_id loan_id,t3.contract_loan_amount,t3.contract_term,t3.loan_paid_at,t1.term_no,
t1.deadline,t6.transfer_time repaid_at,t1.required_repayment,t1.principle,t1.interest,t1.service_fee,t1.should_punish,t2.ref_amount,t2.principle real_principle,t2.interest real_interest,
t2.`service_fee` real_service_fee,t2.`punish` real_punish,t2.collection_relief,
t2.remain_income,t2.`mitigate_principle`,t2.`mitigate_interest`,t2.`mitigate_service_fee`,t2.`mitigate_collection_relief` mitigate_punish
FROM `xjd_repay_plan_repay_record_ref` t2
JOIN `user_repayment_plan` t1 ON t1.id=t2.plan_id
JOIN audit.`loan_manifest` t3 ON t1.ref_id=t3.ref_id
JOIN `offline_alipay_record` t6 ON t2.record_id=t6.id and t2.repay_channel = 13
WHERE  t6.transfer_time >= '%s' AND  t6.transfer_time < '%s'
'''

read_detail_bank_sql = '''
SELECT t1.id plan_id,t1.fund_code,t2.repay_channel,t1.real_loan_id loan_id,t3.contract_loan_amount,t3.contract_term,t3.loan_paid_at,t1.term_no,
t1.deadline,t6.transfer_time repaid_at,t1.required_repayment,t1.principle,t1.interest,t1.service_fee,t1.should_punish,t2.ref_amount,t2.principle real_principle,t2.interest real_interest,
t2.`service_fee` real_service_fee,t2.`punish` real_punish,t2.collection_relief,
t2.remain_income,t2.`mitigate_principle`,t2.`mitigate_interest`,t2.`mitigate_service_fee`,t2.`mitigate_collection_relief` mitigate_punish
FROM `xjd_repay_plan_repay_record_ref` t2
JOIN `user_repayment_plan` t1 ON t1.id=t2.plan_id
JOIN audit.`loan_manifest` t3 ON t1.ref_id=t3.ref_id
JOIN `offline_bank_repay_record` t6 ON t2.record_id=t6.id and t2.repay_channel in (14,15,16)
WHERE  t6.transfer_time >=  '%s' AND  t6.transfer_time <  '%s'
'''


# 还款明细
def handle_hk(year, month):
    start_time = datetime.datetime(year, month, 1, 0, 0, 0)
    end_time = start_time + relativedelta(months=+1)
    df_online_detail = pd.read_sql(read_detail_online_sql % (start_time, end_time), con=engine_new_transaction)
    df_alipay_detail = pd.read_sql(read_detail_alipay_sql % (start_time, end_time), con=engine_new_transaction)
    df_bank_detail = pd.read_sql(read_detail_bank_sql % (start_time, end_time), con=engine_new_transaction)
    df = pd.concat([df_online_detail, df_alipay_detail, df_bank_detail], axis=0)
    if len(df) > 0:
        df['repay_channel'] = df['repay_channel'].astype(int)
        df['fund_code'] = df['fund_code'].astype(int)
        df = pd.merge(df, df_approach, left_on=['repay_channel'], right_on=['id'], how='left')
        df = pd.merge(df, df_fund, left_on=['fund_code'], right_on=['fund_corp_id'], how='left')
        df.reset_index(inplace=True)
        df = df[['fund_name', 'approach_name', 'loan_id', 'contract_loan_amount', 'contract_term', 'loan_paid_at',
                 'term_no', 'deadline', 'repaid_at',
                 'required_repayment', 'principle', 'interest', 'service_fee', 'should_punish', 'ref_amount',
                 'real_principle', 'real_interest',
                 'real_service_fee', 'real_punish', 'remain_income', 'collection_relief', 'mitigate_principle',
                 'mitigate_interest', 'mitigate_service_fee',
                 'mitigate_punish']]
        df.sort_values(by=['loan_id', 'term_no'], inplace=True)
        df.rename(
            columns={'fund_name': '资金方', 'approach_name': '收款账户',
                     'loan_id': '放款id', 'contract_loan_amount': '放款金额',
                     'contract_term': '放款期数', 'loan_paid_at': '放款时间', 'term_no': '期数',
                     'deadline': '应还款日',
                     'repaid_at': '实还款日', 'required_repayment': '应还款金额', 'principle': '应还本金',
                     'interest': '应还利息',
                     'service_fee': '应还服务费', 'should_punish': '应还罚息', 'ref_amount': '实还金额',
                     'real_principle': '实还本金', 'real_interest': '实还利息', 'real_service_fee': '实还服务费',
                     'real_punish': '实还罚息', 'collection_relief': '减免金额', 'remain_income': '其他收益',
                     'mitigate_principle': '减免本金',
                     'mitigate_interest': '减免利息', 'mitigate_service_fee': '减免服务费',
                     'mitigate_punish': '减免罚息'}, inplace=True)
        df.to_excel(path + str(year) + '_' + str(month) + '.xlsx', columns=df.columns, index=None, encoding='utf8')


if __name__ == '__main__':
    # 还款汇总--如果不需要可注释
    hk_hz()
    # 还款明细
    # pool = mtp.Pool(processes=6)
    # years = [ 2017, ]
    # months = [5, ]
    # for year, month in product(years, months):
    #     pool.apply_async(handle_hk, (year, month))
    # pool.close()
    # pool.join()

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