# -*- encoding: utf8 -*-
"""
白条坏账
"""
from __future__ import unicode_literals

from itertools import product
import pandas as pd
import numpy as np
from sqlalchemy import create_engine
import multiprocessing as mtp
import pyexcelerate as pe
from dateutil.relativedelta import relativedelta
import datetime

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

sql_plan = """select plan.id plan_id, loan.order_no 产品编号, 
 loan.real_loan_amount 放款金额, plan.term_no 当期应还期数,date(plan.deadline) deadline, plan.principle  
from new_transaction.user_bt_repayment_plan plan
join baitiao_audit.baitiao_order loan on loan.ref_id = plan.ref_id
where plan.deadline < date_sub('%s', interval 90 day) 
AND loan.funding_corp_id=1
and loan.is_active in(1,-2)"""
#之前（2017-10）给财务的坏账数据实还本金用的是ref表的principle，没有减去减免本金
sql_online = '''
select t1.plan_id,(t1.principle-IFNULL(t1.mitigate_principle,0)) real_principle 
from new_transaction.baitiao_repay_plan_repay_record_ref t1
join new_transaction.repay_record_online t2 on t1.record_id = t2.id and t1.repay_channel < 13 
JOIN (
select plan.id plan_id
from new_transaction.user_bt_repayment_plan plan
join baitiao_audit.baitiao_order loan on loan.ref_id = plan.ref_id AND loan.funding_corp_id=1
where plan.deadline < date_sub('%s', interval 90 day) 
and loan.is_active in(1,-2)
)t3 ON  t1.plan_id =t3.plan_id
WHERE t2.bill_time < '%s'
'''
sql_alipay = '''
select t1.plan_id,(t1.principle-IFNULL(t1.mitigate_principle,0)) real_principle 
from new_transaction.baitiao_repay_plan_repay_record_ref t1
join new_transaction.offline_alipay_record t2 on t1.record_id = t2.id and t1.repay_channel = 13 
JOIN (
select plan.id plan_id
from new_transaction.user_bt_repayment_plan plan
join baitiao_audit.baitiao_order loan on loan.ref_id = plan.ref_id AND loan.funding_corp_id=1
where plan.deadline < date_sub('%s', interval 90 day) 
and loan.is_active in(1,-2)
AND loan.funding_corp_id=1
)t3 ON  t1.plan_id =t3.plan_id
WHERE t2.transfer_time < '%s'
'''
sql_bank = '''
select t1.plan_id,(t1.principle-IFNULL(t1.mitigate_principle,0)) real_principle 
from new_transaction.baitiao_repay_plan_repay_record_ref t1
join new_transaction.offline_bank_repay_record t2 on t1.record_id = t2.id and t1.repay_channel > 13 
JOIN (
select plan.id plan_id
from new_transaction.user_bt_repayment_plan plan
join baitiao_audit.baitiao_order loan on loan.ref_id = plan.ref_id AND loan.funding_corp_id=1
where plan.deadline < date_sub('%s', interval 90 day) 
and loan.is_active in(1,-2)

)t3 ON  t1.plan_id =t3.plan_id
WHERE  t2.transfer_time < '%s'
'''
columns = ['plan_id', '白条商家', '产品编号', '放款日期', '总期数', '放款金额', '本金债权', '当期应还期数', '当期应还款日', '当期本金', '还款时间', '坏账统计时间']
years = (2017,)
months = (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
path = u'E:/数据汇总/白条/坏账/'


def bad_debt(year, month):
    end_time = datetime.date(year, month, 1) + relativedelta(months=+1)
    df_plan = pd.read_sql(sql_plan % (end_time), engine_audit)
    if len(df_plan) > 0:
        df_online = pd.read_sql(sql_online % (end_time, end_time), engine_audit)
        df_bank = pd.read_sql(sql_bank % (end_time, end_time), engine_audit)
        df_ali = pd.read_sql(sql_alipay % (end_time, end_time), engine_audit)
        df_real = pd.concat([df_online, df_bank, df_ali], ignore_index=True, axis=0)
        df_real = df_real.groupby('plan_id')['real_principle'].sum().reset_index()
        df_real.plan_id = df_real.plan_id.astype(int)
        df_plan.plan_id = df_plan.plan_id.astype(int)
        if len(df_real) > 0:
            df_plan = pd.merge(df_plan, df_real, on='plan_id', how='left')
        else:
            df_plan['real_principle'] = 0
        df_plan.fillna(0, inplace=True)
        df_plan['diff'] = np.round(df_plan['principle'] - df_plan['real_principle'], 2)
        df_plan = df_plan.ix[df_plan['diff'] != 0]
        df_plan[u'坏账统计时间'] = end_time
        df_plan.rename(
            columns={'principle': u'应还本金', 'real_principle': u'实还本金', 'diff': u'坏账金额'}, inplace=True)
        data_list = df_plan.values.tolist()
        data_list.insert(0, df_plan.columns.tolist())
        wb = pe.Workbook()
        wb.new_sheet(u'明细', data=data_list)
        wb.save(path + u'%s-%s_白条坏账.xlsx' % (year, month))
        # df_plan.to_excel(path + u'%s-%s_白条坏账.xlsx' % (year, month), index=None)


bad_debt(2017, 10)
# if __name__ == '__main__':
#     pool = mtp.Pool(processes=8)
#     for year, month in product(years, months):
#         print year, month
#         if year == 2015 and months < 6:
#             continue
#         elif year == 2017 and months > 8:
#             continue
#         pool.apply_async(bad_debt, (year, month))
#     pool.close()
#     pool.join()
