# -*- coding:utf-8 -*-
#Author: Perry_Zhu
import datetime
import multiprocessing as mtp
import sys
from itertools import product
import pandas as pd
from dateutil.relativedelta import relativedelta
from sqlalchemy import create_engine

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

path = u'E:/数据汇总/白条/汇总/'
path_detail = u'E:/数据汇总/白条/还款明细/'
# 注意修改数据源

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

read_merchant_sql = '''SELECT merchant_id,merchant_name FROM baitiao_audit.merchant'''
df_merchant = pd.read_sql(read_merchant_sql, con=engine_new_transaction)
df_merchant['merchant_id'] = df_merchant['merchant_id'].astype(int)
read_repay_sql = '''
SELECT id,`approach_name` FROM new_transaction.`repay_channel`
'''
df_approach = pd.read_sql(read_repay_sql, engine_new_transaction)
df_approach['id'] = df_approach['id'].astype(int)

read_fund_sql = '''
SELECT id,fund_name FROM baitiao_audit.`fund_corp`
'''
df_fund = pd.read_sql(read_fund_sql, engine_new_transaction)
df_fund['id'] = df_fund['id'].astype(int)

'''
白条还款汇总
'''


################################################
# 收客户还款汇总
def hk_hz():
    sql_alipay = """
    SELECT
        YEAR (t3.transfer_time) r_year,
        MONTH (t3.transfer_time) r_month,
        t8.fund_name,
        t6.merchant_name,
        t7.approach_name,
        SUM(ifnull(t1.ref_amount,0)) 总额,
        SUM(ifnull(t1.principle,0)) 本金,
        SUM(ifnull(t1.interest,0)) 利息,
        SUM(ifnull(t1.service_fee,0)) 服务费,
        SUM(ifnull(t1.collection_relief,0)) 总减免,
        SUM(ifnull(t1.mitigate_principle,0)) 减免本金,
        SUM(ifnull(t1.mitigate_interest,0)) 减免利息,
        SUM(ifnull(t1.mitigate_service_fee,0)) 减免服务费,
        SUM(
            ifnull(t1.mitigate_collection_relief,0)
        ) 减免罚息,
        SUM(ifnull(t1.red_package_fee,0)) 红包减免,
        SUM(ifnull(t1.punish,0)) 逾期罚款收益,
        SUM(ifnull(t1.remain_income,0)) 其他收益
    FROM
        baitiao_repay_plan_repay_record_ref t1
    JOIN user_bt_repayment_plan t2 ON t2.id = t1.plan_id
    JOIN offline_alipay_record t3 ON t1.repay_channel = 13 AND t3.id = t1.record_id  AND  t3.is_ref=1
    JOIN baitiao_audit.baitiao_order t5 ON t2.ref_id = t5.ref_id
    JOIN baitiao_audit.merchant t6 ON t6.`merchant_id` = t5.`merchantId`
    JOIN new_transaction.repay_channel t7 ON t1.repay_channel = t7.id
    JOIN baitiao_audit.fund_corp t8 ON  t5.funding_corp_id=t8.id
	GROUP BY 1,2,3,4,5"""
    df_alipay = pd.read_sql(sql=sql_alipay, con=engine_new_transaction)

    sql_bank = """
      SELECT
        YEAR (t3.transfer_time) r_year,
        MONTH (t3.transfer_time) r_month,
         t8.fund_name,
        t6.merchant_name,
        t7.approach_name,
        SUM(ifnull(t1.ref_amount,0)) 总额,
        SUM(ifnull(t1.principle,0)) 本金,
        SUM(ifnull(t1.interest,0)) 利息,
        SUM(ifnull(t1.service_fee,0)) 服务费,
        SUM(ifnull(t1.collection_relief,0)) 总减免,
        SUM(ifnull(t1.mitigate_principle,0)) 减免本金,
        SUM(ifnull(t1.mitigate_interest,0)) 减免利息,
        SUM(ifnull(t1.mitigate_service_fee,0)) 减免服务费,
        SUM(
            ifnull(t1.mitigate_collection_relief,0)
        ) 减免罚息,
        SUM(ifnull(t1.red_package_fee,0)) 红包减免,
        SUM(ifnull(t1.punish,0)) 逾期罚款收益,
        SUM(ifnull(t1.remain_income,0)) 其他收益
    FROM
        baitiao_repay_plan_repay_record_ref t1
    JOIN user_bt_repayment_plan t2 ON t2.id = t1.plan_id
    JOIN offline_bank_repay_record t3 ON t1.repay_channel IN (14, 15, 16)
    AND t3.id = t1.record_id
    JOIN baitiao_audit.baitiao_order t5 ON t2.ref_id = t5.ref_id
    JOIN baitiao_audit.merchant t6 ON t6.`merchant_id` = t5.`merchantId`
    JOIN new_transaction.repay_channel t7 ON t1.repay_channel = t7.id
    JOIN baitiao_audit.fund_corp t8 ON  t5.funding_corp_id=t8.id
	GROUP BY 1,2,3,4,5"""
    df_bank = pd.read_sql(sql=sql_bank, con=engine_new_transaction)

    sql_online = """
        SELECT
            YEAR (t3.bill_time) r_year,
            MONTH (t3.bill_time) r_month,
             t8.fund_name,
            t6.merchant_name,
            t7.approach_name,
            SUM(ifnull(t1.ref_amount,0)) 总额,
            SUM(ifnull(t1.principle,0)) 本金,
            SUM(ifnull(t1.interest,0)) 利息,
            SUM(ifnull(t1.service_fee,0)) 服务费,
            SUM(ifnull(t1.collection_relief,0)) 总减免,
            SUM(ifnull(t1.mitigate_principle,0)) 减免本金,
            SUM(ifnull(t1.mitigate_interest,0)) 减免利息,
            SUM(ifnull(t1.mitigate_service_fee,0)) 减免服务费,
            SUM(
                ifnull(t1.mitigate_collection_relief,0)
            ) 减免罚息,
            SUM(ifnull(t1.red_package_fee,0)) 红包减免,
            SUM(ifnull(t1.punish,0)) 逾期罚款收益,
            SUM(ifnull(t1.remain_income,0)) 其他收益
        FROM
            baitiao_repay_plan_repay_record_ref t1
        JOIN repay_record_online t3 ON t1.repay_channel in(1,2,3,4,5,6,7,8,9,10,11,12,22,23,24) AND t3.id = t1.record_id
        JOIN user_bt_repayment_plan t2 ON t2.id = t1.plan_id
        JOIN baitiao_audit.baitiao_order t5 ON t2.ref_id = t5.ref_id
        JOIN baitiao_audit.merchant t6 ON t6.`merchant_id` = t5.`merchantId`
        JOIN new_transaction.repay_channel t7 ON t1.repay_channel = t7.id
        JOIN baitiao_audit.fund_corp t8 ON  t5.funding_corp_id=t8.id
        WHERE t3.bill_time is not NULL 
        GROUP BY 1,2,3,4,5
	"""
    df_online = pd.read_sql(sql=sql_online, con=engine_new_transaction)
    sql_cash_online = """
    SELECT
        YEAR (t3.bill_time) r_year,
        MONTH (t3.bill_time) r_month,
        t8.fund_name,
        t6.merchant_name,
        '白条现金支付' approach_name,
        SUM(ifnull(t1.ref_amount,0)) 总额,
        SUM(ifnull(t5.real_loan_amount,0)) 本金,
        0.0 利息,
        0.0 服务费,
        0.0 总减免,
        0.0 减免本金,
        0.0 减免利息,
        0.0 减免服务费,
        0.0 减免罚息,
        0.0 红包减免,
        0.0 逾期罚款收益,
        0.0 其他收益
    FROM
        (SELECT repay_id,record_id,repay_channel,SUM(ifnull(ref_amount,0))ref_amount FROM 
				baitiao_cash_record_ref WHERE  repay_channel !=13 GROUP BY 1) t1
    JOIN baitiao_cash_repay t2 ON t2.id = t1.repay_id
    JOIN repay_record_online t3 ON t3.id = t1.record_id 
    JOIN baitiao_audit.baitiao_order t5 ON t2.order_no = t5.order_no AND t2.real_order_amount=t5.real_loan_amount
    JOIN baitiao_audit.merchant t6 ON t6.`merchant_id` = t5.`merchantId`
    JOIN baitiao_audit.fund_corp t8 ON  t5.funding_corp_id=t8.id
    WHERE t3.bill_time is not NULL 
    GROUP BY 1,2,3,4,5
    """
    sql_cash_alipay = """
        SELECT
            YEAR (t3.transfer_time) r_year,
            MONTH (t3.transfer_time) r_month,
            t8.fund_name,
            t6.merchant_name,
            t7.approach_name,
            SUM(ifnull(t1.ref_amount,0)) 总额,
            SUM(ifnull(t5.real_loan_amount,0)) 本金,
            0.0 利息,
            0.0 服务费,
            0.0 总减免,
            0.0 减免本金,
            0.0 减免利息,
            0.0 减免服务费,
            0.0 减免罚息,
            0.0 红包减免,
            0.0 逾期罚款收益,
            0.0 其他收益
        FROM
           (SELECT repay_id,record_id,repay_channel,SUM(ifnull(ref_amount,0))ref_amount 
              FROM 
			baitiao_cash_record_ref 
			GROUP BY 1) t1
        JOIN baitiao_cash_repay t2 ON t2.id = t1.repay_id
        JOIN offline_alipay_record t3 ON  t3.id = t1.record_id AND t1.repay_channel=13
        JOIN baitiao_audit.baitiao_order t5 ON t2.order_no = t5.order_no AND t2.real_order_amount=t5.real_loan_amount
        JOIN baitiao_audit.merchant t6 ON t6.`merchant_id` = t5.`merchantId`
         JOIN new_transaction.repay_channel t7 ON t1.repay_channel = t7.id
         JOIN baitiao_audit.fund_corp t8 ON  t5.funding_corp_id=t8.id
        GROUP BY 1,2,3,4,5
        """
    df_cash_onlie = pd.read_sql(sql=sql_cash_online, con=engine_new_transaction)
    df_cash_alipay = pd.read_sql(sql=sql_cash_alipay, con=engine_new_transaction)
    df = pd.concat([df_alipay, df_bank, df_online, df_cash_onlie, df_cash_alipay], ignore_index=True)
    df = df[
        ['r_year', 'r_month', 'fund_name', 'merchant_name', 'approach_name', u'总额', u'本金', u'利息', u'服务费', u'逾期罚款收益',
         u'其他收益', u'总减免',
         u'红包减免', u'减免本金', u'减免利息', u'减免服务费', u'减免罚息']]
    df.rename(
        columns={'r_year': '年', 'r_month': '月', 'fund_name': '资金方', 'merchant_name': '商户', 'approach_name': '收款账户'},
        inplace=True)
    df.to_excel(path + u'白条还款汇总表2017-09.xlsx', index=None)


################################################
# ====明细
#

read_detail_online_sql = '''
SELECT 
    t1.id plan_id,t3.funding_corp_id,t3.merchantId,
    t2.repay_channel,
    t7.approach_name,
    t1.order_no,
    t3.real_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 ,t6.order_id
FROM `baitiao_repay_plan_repay_record_ref` t2
JOIN `user_bt_repayment_plan` t1 ON t1.id=t2.plan_id
JOIN baitiao_audit.`baitiao_order` 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 new_transaction.repay_channel t7 on t2.repay_channel=t7.id
WHERE  t6.bill_time >= '%s' AND t6.bill_time < '%s'
'''

read_detail_alipay_sql = '''
SELECT t1.id plan_id,t3.funding_corp_id,t3.merchantId,
 t2.repay_channel,t7.approach_name,
 t1.order_no,t3.real_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,t6.business_flow order_id
FROM `baitiao_repay_plan_repay_record_ref` t2
JOIN `user_bt_repayment_plan` t1 ON t1.id=t2.plan_id
JOIN baitiao_audit.`baitiao_order` 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 new_transaction.repay_channel t7 on t2.repay_channel=t7.id
WHERE t6.transfer_time >= '%s' AND t6.transfer_time < '%s'
'''

read_detail_bank_sql = '''
SELECT t1.id plan_id,t3.funding_corp_id,t3.merchantId,
t2.repay_channel,t7.approach_name,
t1.order_no,t3.real_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, t6.id order_id
FROM `baitiao_repay_plan_repay_record_ref` t2
JOIN `user_bt_repayment_plan` t1 ON t1.id=t2.plan_id
JOIN baitiao_audit.`baitiao_order` 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 new_transaction.repay_channel t7 on t2.repay_channel=t7.id
WHERE  t6.transfer_time >= '%s' AND t6.transfer_time < '%s'
'''


def handle_detail(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, ignore_index=True)
    if len(df) > 0:
        df['repay_channel'] = df['repay_channel'].astype(int)
        df = pd.merge(df, df_approach, left_on=['repay_channel'], right_on=['id'], how='left')
        df = pd.merge(df, df_merchant, left_on=['merchantId'], right_on=['merchant_id'], how='left')
        df = pd.merge(df, df_fund, left_on=['funding_corp_id'], right_on=['id'], how='left')
        df.reset_index(inplace=True)
        df = df[['fund_name', 'merchant_name', 'approach_name', 'order_no', 'real_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', 'approach_name', 'order_id']]
        df.rename(columns={'fund_name': '资金方', 'merchant_name': '商户', 'approach_name': '收款账户',
                           'order_no': '订单号', 'real_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': '减免罚息', 'approach_name': '渠道名称', "order_id": "流水号"}, inplace=True)
        # excel_file_detail = pd.excel_writer(path + str(year) + '_' + str(month) + '.xlsx')
        df.to_excel(path_detail + str(year) + '_' + str(month) + '.xlsx', columns=df.columns, index=None,
                    encoding='utf8')


if __name__ == '__main__':
    # 还款汇总
    hk_hz()
    # 还款明细
    # pool = mtp.Pool(processes=1)
    # years = [2017, ]
    # months = [4, 5, 6]
    # for year, month in product(years, months):
    #     handle_detail(year, month)
    # pool.apply_async(handle_detail, (year, month))
    # pool.close()
    # pool.join()
print 'main done!!!'
