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

import pandas as pd
import numpy as np
from dateutil.relativedelta import relativedelta

from sqlalchemy import create_engine
import multiprocessing as mtp

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

'''
现金贷还款表
1、现金贷还款汇总
2、还款时间使用账单时间
3、特殊 新马上
'''

read_fund_sql = '''
select id fund_corp_id,name fund_name from new_transaction.funding_corp where id  NOT in (160, 340)
'''
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/all_back_0430?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 = './'

# fundcodes = [
#     1, 0, 3, 2, 5, 29, 4, 141, 50, 70, 80, 90, 100, 120, 110, 130, 150, 170, 180, 140, 190, 200, 210, 135, 230,
#     240,
#     250, 251, 260, 300, 290, 270, 330, 310, 320, 350, 340]

fundcodes = [160]


################################################
# 收客户还款汇总
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) 减免罚息
     from xjd_repay_plan_repay_record_ref t1
    join user_repayment_plan t2 on t2.id = t1.plan_id
    join new_transaction.funding_corp t6 on t2.fund_code=t6.id
    join new_transaction.repay_channel t7 on t1.repay_channel = t7.id
    join loan_manifest t8 on t8.ref_id = t2.ref_id
    join offline_alipay_record t3 on t1.repay_channel =13 and t3.id = t1.record_id
    JOIN temp.loan_uuid_class t9 ON  t8.ref_id = t9.ref_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) 减免罚息
     from xjd_repay_plan_repay_record_ref t1
    join user_repayment_plan t2 on t2.id = t1.plan_id
    join new_transaction.funding_corp t6 on t2.fund_code=t6.id
    join new_transaction.repay_channel t7 on t1.repay_channel = t7.id
    join loan_manifest t8 on t8.ref_id = t2.ref_id
    join offline_bank_repay_record t3 on t1.repay_channel in (14,15,16) and t3.id = t1.record_id
    JOIN temp.loan_uuid_class t9 ON  t8.ref_id = t9.ref_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) 减免罚息
    from xjd_repay_plan_repay_record_ref t1
    join user_repayment_plan t2 on t2.id = t1.plan_id
    join new_transaction.funding_corp t6 on t2.fund_code=t6.id
    join new_transaction.repay_channel t7 on t1.repay_channel = t7.id
    join loan_manifest t8 on t8.ref_id = t2.ref_id
    join repay_record_online t3 on t1.repay_channel < 13 and t3.id = t1.record_id
    JOIN temp.loan_uuid_class t9 ON  t8.ref_id = t9.ref_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 new_transaction.repay_record_online t4 ON t1.record_id = t4.id JOIN new_transaction.funding_corp t5 ON t5.id = 150 JOIN new_transaction.repay_channel t6 ON t6.id=2 AND t4.pay_approach = 2  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.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'现金贷还款表汇总_430.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_term,t1.term_no,t3.loan_paid_at,t3.user_biz_id,
t6.bill_time repaid_at,t2.ref_amount,t2.principle-t2.mitigate_principle real_principle,t2.interest-t2.mitigate_interest real_interest,
t2.`service_fee`-t2.mitigate_service_fee real_service_fee,t2.`punish`-t2.mitigate_collection_relief real_punish,t2.remain_income, t6.order_id,t7.uuid
FROM `xjd_repay_plan_repay_record_ref` t2
JOIN `user_repayment_plan` t1 ON t1.id=t2.plan_id
JOIN `loan_manifest` t3 ON t1.ref_id=t3.ref_id
JOIN repay_record_online t6 ON t2.record_id=t6.id AND t2.repay_channel < 13
JOIN temp.loan_uuid_class t7 ON  t3.ref_id = t7.ref_id
WHERE  t6.bill_time >= '%s' and t6.bill_time < '%s' AND t3.funding_code=%s
'''

read_detail_alipay_sql = '''
SELECT t1.id plan_id,t1.fund_code,t2.repay_channel,t1.real_loan_id loan_id,t3.contract_term,t1.term_no,t3.loan_paid_at,t3.user_biz_id,
t6.transfer_time repaid_at,t2.ref_amount,t2.principle-t2.mitigate_principle real_principle,t2.interest-t2.mitigate_interest real_interest,
t2.`service_fee`-t2.mitigate_service_fee real_service_fee,t2.`punish`-t2.mitigate_collection_relief real_punish,t2.remain_income, t6.business_flow order_id, t7.uuid
FROM `xjd_repay_plan_repay_record_ref` t2
JOIN `user_repayment_plan` t1 ON t1.id=t2.plan_id
JOIN `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
JOIN temp.loan_uuid_class t7 ON  t3.ref_id = t7.ref_id
WHERE  t6.transfer_time >= '%s' AND  t6.transfer_time < '%s' AND t3.funding_code=%s
'''

read_detail_bank_sql = '''
SELECT t1.id plan_id,t1.fund_code,t2.repay_channel,t1.real_loan_id loan_id,t3.contract_term,t1.term_no,t3.loan_paid_at,t3.user_biz_id,
t6.transfer_time repaid_at,t2.ref_amount,t2.principle-t2.mitigate_principle real_principle,t2.interest-t2.mitigate_interest real_interest,
t2.`service_fee`-t2.mitigate_service_fee real_service_fee,t2.`punish`-t2.mitigate_collection_relief real_punish,t2.remain_income,'' order_id, t7.uuid
FROM `xjd_repay_plan_repay_record_ref` t2
JOIN `user_repayment_plan` t1 ON t1.id=t2.plan_id
JOIN `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)
JOIN temp.loan_uuid_class t7 ON  t3.ref_id = t7.ref_id
WHERE  t6.transfer_time >=  '%s' AND  t6.transfer_time <  '%s' AND t3.funding_code=%s
'''

chunksize = 10000
# 还款明细
def handle_hk(fund_code):
    start_time = datetime.datetime(2015, 1, 1, 0, 0, 0)
    end_time = datetime.datetime(2017, 1, 1, 0, 0, 0)
    df_online_detail_gen = pd.read_sql(read_detail_online_sql % (start_time, end_time, fund_code),
                                   con=engine_new_transaction, chunksize=chunksize)
    df_online_detail = pd.DataFrame()
    for tmp in df_online_detail_gen:
        df_online_detail = df_online_detail.append(tmp, ignore_index=True)

    print read_detail_online_sql % (start_time, end_time, fund_code)
    df_alipay_detail_gen = pd.read_sql(read_detail_alipay_sql % (start_time, end_time, fund_code),
                                   con=engine_new_transaction,chunksize=chunksize)

    df_alipay_detail = pd.DataFrame()
    for tmp in df_alipay_detail_gen:
        df_alipay_detail = df_alipay_detail.append(tmp, ignore_index=True)

    print read_detail_alipay_sql % (start_time, end_time, fund_code)

    df_bank_detail_gen = pd.read_sql(read_detail_bank_sql % (start_time, end_time, fund_code), con=engine_new_transaction,chunksize=chunksize)
    df_bank_detail = pd.DataFrame()
    for tmp in df_bank_detail_gen:
        df_bank_detail = df_bank_detail.append(tmp, ignore_index=True)

    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.sort_values(by=['loan_id', 'term_no'], inplace=True)
        df = df[['fund_name','loan_id', 'term_no', 'repaid_at', 'ref_amount',
                 'real_principle', 'real_interest',
                 'real_service_fee', 'real_punish', 'remain_income', 'approach_name','uuid']]

        df['repaid_at'] = df['repaid_at'].apply(lambda x: pd.datetime.strftime(x, '%Y-%m-%d %H:%M'))

        df.rename(
            columns={'fund_name': u'资金方',
                     'loan_id': u'放款id',
                     'term_no': u'期数',
                     'repaid_at': u'实还款日',
                     'ref_amount': u'实还金额',
                     'real_principle': u'本金',
                     'real_interest': u'利息',
                     'real_service_fee': u'服务费',
                     'real_punish': u'罚息',
                     'remain_income': u'其他收益',
                     'approach_name': u'还款渠道',
                     'uuid':u'借款人id'
                     }, inplace=True)

        all_columns = [u'资金方', u'放款id', u'期数', u'实还款日', u'实还金额', u'本金', u'利息',u'服务费', u'罚息', u'其他收益', u'还款渠道',u'借款人id']
        header_line = ','.join(all_columns)
        df = df[all_columns]
        df.to_csv(path + 'xjd_repay_2015-2016_' +str(fund_code)+ '.txt', sep='|', index=None, encoding='utf8')

        #  资金方,期数,实还款日,实还金额,本金,利息,服务费,罚息,其他收益,还款渠道
        # np.savetxt(path + 'xjd_repay_2015-2016_' +str(fundcode)+ '.txt', X=df.values,fmt='%s|%s|%d|%s|%.2f|%.2f|%.2f|%.2f|%.2f|%.2f|%s|', delimiter='|')


if __name__ == '__main__':
    # 还款汇总--如果不需要可注释
    # hk_hz()
    # 还款明细
    for fundcode in fundcodes:
        handle_hk(fundcode)
        # pool.apply_async(handle_hk, (year, month))
        # pool.close()
        # pool.join()

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