# -*- encoding: utf8 -*-
import sys
import datetime
import pandas as pd
from sqlalchemy import create_engine
from dateutil.relativedelta import relativedelta
from itertools import product

reload(sys)
sys.setdefaultencoding("utf-8")
pd.options.mode.chained_assignment = None
max_limit = 800000
import os

path = u'E:/审计出表/人工控制/现金贷-还款表-1010版本/'
engine_xjd = create_engine('mysql+mysqldb://internal_r:ArbNgtvlJzZHXsEu@172.16.4.60:3306/?charset=utf8',
                           echo=False).connect()

'''
# 注意事项：非常重要 2017年4月份客户实还表中Loan_id = 52532760 的地方要特别地注意一下 ，1月份中loan_id = 39722219 也需要特别注意
'''

# 这四个资金方的还款计划来自于实际还资金方表
# group by 是由于还资金方计划中有0.01的情况
# sql_repay_frr_1 = '''
# SELECT
# 	frr.ref_id,
# 	frr.term_no '应还期数',
# 	date(frr.deadline) '应还资金方时间',
# 	sum(frr.principle) '应还资金方本金',
# 	sum(frr.interest) '应还资金方利息'
# FROM
# 	audit.funding_repayment_record frr
# 	join audit.loan_manifest lm on lm.ref_id = frr.ref_id
# WHERE
# 	lm.loan_paid_at>='%s' and lm.loan_paid_at<'%s'
# 	and frr.funding_code in (380,420,290,330)
# 	GROUP BY 1,2
# '''

# 这5个资金方的还款计划来自于用户还款计划
# group by 是由于还资金方计划中有0.01的情况
sql_repay_frr_2 = '''
select
    urp.ref_id,
    urp.term_no '应还期数',
    date(urp.deadline) '应还资金方时间',
    sum(urp.principle) '应还资金方本金',
    sum(urp.interest) '应还资金方利息'
from all_back_0930.user_repayment_plan urp
join all_back_0930.loan_manifest lm on lm.ref_id = urp.ref_id
where lm.loan_paid_at>='%s' and loan_paid_at<'%s'
and urp.fund_code in (210,270,310,320,410,430)
GROUP BY 1,2
'''

# test_shiyao.funding_repayment_plan中还资金方计划的数据是最全的
# group by 是由于还资金方计划中有0.01的情况
sql_repay_frr_3 = '''
select 
    frp.ref_id,
    frp.term_no '应还期数',
    date(frp.deadline) '应还资金方时间',
    sum(frp.principle) '应还资金方本金',
    sum(frp.interest) '应还资金方利息'
from audit.funding_repayment_plan frp
    join all_back_0930.loan_manifest lm on lm.ref_id = frp.ref_id
WHERE lm.loan_paid_at>='%s' and loan_paid_at<'%s'
and lm.funding_code not in (210,270,310,320,410,430)
GROUP BY  1,2
'''

# 在线还款部分
read_detail_online_sql = '''
SELECT t1.real_loan_id loan_id,date(lm.loan_paid_at) '放款时间',t1.ref_id,fc.name '资金方',t1.term_no '应还期数',
date(t6.bill_time) '实还时间',t2.ref_amount '实还总额',
t2.principle-t2.mitigate_principle '实还本金',t2.interest-t2.mitigate_interest '实还利息',
ifnull(t2.`service_fee`-t2.mitigate_service_fee,0) '实还服务费',
IFNULL(t2.`punish`-t2.mitigate_collection_relief, 0) '实还罚息',
t2.remain_income '其他收益',rch.approach_name '还款渠道',t6.order_id AS '流水号',
t6.amount '账单实还金额',t1.service_fee '应收服务费',t1.required_repayment '应还总额'
FROM all_back_0930.`xjd_repay_plan_repay_record_ref` t2
JOIN all_back_0930.`user_repayment_plan` t1 ON t1.id=t2.plan_id
JOIN all_back_0930.repay_record_online t6 ON t2.record_id=t6.id AND t2.repay_channel = t6.pay_approach
join basic.funding_corp fc on fc.funding_code = t1.fund_code
join all_back_0930.loan_manifest lm on lm.ref_id = t1.ref_id
join new_transaction.repay_channel rch on rch.id = t2.repay_channel
WHERE  lm.loan_paid_at >= '%s' AND lm.loan_paid_at < '%s'
'''

# 支付宝线下还款
read_detail_alipay_sql = '''
SELECT t1.real_loan_id loan_id,date(lm.loan_paid_at) '放款时间',t1.ref_id,fc.name '资金方',t1.term_no '应还期数',
date(t6.transfer_time) '实还时间',t2.ref_amount '实还总额',
t2.principle-t2.mitigate_principle '实还本金',t2.interest-t2.mitigate_interest '实还利息',
ifnull(t2.`service_fee`-t2.mitigate_service_fee,0) '实还服务费',
IFNULL(t2.`punish`-t2.mitigate_collection_relief, 0) '实还罚息',
t2.remain_income '其他收益',rch.approach_name '还款渠道',t6.business_flow '流水号',
t6.income '账单实还金额',t1.service_fee '应收服务费',t1.required_repayment '应还总额'
FROM all_back_0930.`xjd_repay_plan_repay_record_ref` t2
JOIN all_back_0930.`user_repayment_plan` t1 ON t1.id=t2.plan_id
JOIN all_back_0930.`offline_alipay_record` t6 ON t2.record_id=t6.id AND t2.repay_channel = 13
join basic.funding_corp fc on fc.funding_code = t1.fund_code
join all_back_0930.loan_manifest lm on lm.ref_id = t1.ref_id
join new_transaction.repay_channel rch on rch.id = t2.repay_channel
WHERE  lm.loan_paid_at >= '%s' AND  lm.loan_paid_at < '%s'
'''

# 银行线下还款部分，无对应流水号
read_detail_bank_sql = '''
SELECT 
    t1.real_loan_id loan_id,date(lm.loan_paid_at) '放款时间',t1.ref_id,fc.name '资金方',t1.term_no '应还期数',
    date(t6.transfer_time) '实还时间',t2.ref_amount '实还总额',
    t2.principle-t2.mitigate_principle '实还本金',t2.interest-t2.mitigate_interest '实还利息',
    ifnull(t2.`service_fee`-t2.mitigate_service_fee,0) '实还服务费',
    IFNULL(t2.`punish`-t2.mitigate_collection_relief, 0) '实还罚息',
    t2.remain_income '其他收益',rch.approach_name '还款渠道',t6.transfer_amount '账单实还金额',
    t1.service_fee '应收服务费',t1.required_repayment '应还总额'
FROM all_back_0930.`xjd_repay_plan_repay_record_ref` t2
JOIN all_back_0930.`user_repayment_plan` t1 ON t1.id=t2.plan_id
join all_back_0930.loan_manifest lm on lm.ref_id = t1.ref_id
JOIN all_back_0930.`offline_bank_repay_record` t6 ON t2.record_id=t6.id AND t2.repay_channel IN (14,15,16)
join basic.funding_corp fc on fc.funding_code = t1.fund_code
join new_transaction.repay_channel rch on rch.id = t2.repay_channel
WHERE  lm.loan_paid_at >=  '%s' AND  lm.loan_paid_at < '%s'
'''


# 获取还款数据
def handle_hk(year, month):
    # year, month = 2017, 1
    start_time = datetime.datetime(year, month, 1, 0, 0, 0)
    end_time = start_time + relativedelta(months=+1)
    # 获取全部还款计划
    # df_repay_frr_1 = pd.read_sql(sql_repay_frr_1 % (start_time, end_time), con=engine_xjd)
    df_repay_frr_2 = pd.read_sql(sql_repay_frr_2 % (start_time, end_time), con=engine_xjd)
    df_repay_frr_3 = pd.read_sql(sql_repay_frr_3 % (start_time, end_time), con=engine_xjd)
    df_repay_frr = pd.concat([df_repay_frr_2, df_repay_frr_3])
    df_repay_frr = df_repay_frr.drop_duplicates([u'ref_id', u'应还期数', u'应还资金方时间', u'应还资金方本金', u'应还资金方利息']).dropna()
    df_repay_frr['ref_id'] = df_repay_frr['ref_id'].astype('int64')
    df_repay_frr[u'应还期数'] = df_repay_frr[u'应还期数'].astype('int64')
    # 获取全部实还信息
    df_online_detail = pd.read_sql(read_detail_online_sql % (start_time, end_time), con=engine_xjd)
    df_alipay_detail = pd.read_sql(read_detail_alipay_sql % (start_time, end_time), con=engine_xjd)
    df_bank_detail = pd.read_sql(read_detail_bank_sql % (start_time, end_time), con=engine_xjd)
    df = pd.concat([df_online_detail, df_alipay_detail, df_bank_detail], axis=0)
    df[u'应还期数'] = df[u'应还期数'].astype('int64')
    df['ref_id'] = df['ref_id'].astype('int64')
    # 还款和实还进行合并
    df = pd.merge(df, df_repay_frr, on=['ref_id', u'应还期数'], how='left')
    df.loc[df[u'流水号'].duplicated(), u'账单实还金额'] = 0
    if len(df) > 0:
        df[u'唯一编号'] = df['loan_id'].astype('str') + df[u'应还期数'].fillna(0).astype('int').astype('str')
        df[u'实还总额-账单实还总额'] = df[u'实还总额'].fillna(0) - df[u'账单实还金额'].fillna(0)
        df[u'应还资金方本息-客户实还本息'] = df[u'应还资金方本金'].fillna(0) + df[u'应还资金方利息'].fillna(0) - df[u'实还本金'].fillna(0) - df[
            u'实还利息'].fillna(0)
        df[u'应收服务费-实还服务费'] = df[u'应收服务费'] - df[u'实还服务费']
        df = df.drop([u'ref_id'], axis=1)
        df = df.fillna(0)
        columns = [u'放款时间', u'唯一编号', u'资金方', 'loan_id', u'实还时间', u'实还总额', u'应还期数', u'实还本金', u'实还利息', u'实还服务费',
                   u'实还罚息', u'其他收益', u'流水号', u'还款渠道', u'账单实还金额', u'应还资金方时间', u'应还资金方本金', u'应还资金方利息',
                   u'应收服务费', u'实还总额-账单实还总额', u'应还资金方本息-客户实还本息', u'应收服务费-实还服务费']

        print(u'客户实还本息:' + str(sum(df[u'实还本金']) + sum(df[u'实还利息'])) + u' 应还资金方本金:' + str(
            sum(df[u'应还资金方本金'])) + u' 应还资金方利息' + str(sum(df[u'应还资金方利息'])))
        if len(df) > max_limit:
            for i in xrange(0, len(df), max_limit):
                df[i:i + max_limit].to_excel(os.path.join(path, u'人工控制-现金贷-客户实还表-(%s-%02d)-%d.xlsx' % (
                    year, month, i / max_limit + 1)), index=None, columns=columns)
        else:
            df.to_excel(path + u'人工控制-现金贷-客户实还表-(%d-%d).xlsx' % (year, month), index=None, columns=columns)


if __name__ == '__main__':
    year = [2017]
    month = range(9, 10, 1)

    for year, month in product(year, month):
        print '____%d__%d' % (year, month)
        handle_hk(year, month)
