# -*- encoding: utf8 -*-

"""
目标：
    收入确认表-以loan_id为维度比较风险金及客户实还服务费
数据库：

字段名：
    loan_id，资金方，风险等级，本金债权，放款日期，计提风险金比例，计提风险金，实还服务费（减免后，含一次性服务费），Asset余额，收入确认金额
注意：
    加入一次性服务费，计提风险金比例
    每个时间点用对应时间点的备份库，eg：17年6月30号之前的用630数据库，。。。
"""

import os

import datetime
import numpy as np
import pandas as pd

from db_con.db_connect import sql_engine

# ----------------------------------------------
step = 10000
max_limit = 900000
pd.options.mode.chained_assignment = None
pd.set_option('display.float_format', lambda x: '%.5f' % x)

end_date = datetime.date(2017, 11, 1)
file_path = u'E:/审计出表/现金贷/1107'

if not os.path.exists(file_path):
    os.makedirs(file_path)
mysql = sql_engine('audit', 'new_transaction').get_engine()

sql_loan_plan = """
SELECT
  lm.loan_id,
  lm.ref_id,
  lc.class                风险等级,
  lm.funding_code,
  fc.name                 资金方,
  lm.is_active,
  lm.contract_loan_amount 本金债权,
  lm.qg_one_time_service_fee,
  date(lm.loan_paid_at)   放款日期,
  year(lm.loan_paid_at)   年,
  month(lm.loan_paid_at)  月
FROM audit.loan_manifest lm
  JOIN test_mzh.loan_class_contract lc ON lc.ref_id = lm.ref_id
  JOIN basic.funding_corp fc ON fc.funding_code = lm.funding_code
WHERE lm.loan_paid_at < '%s';
"""

sql_ref = """
SELECT
  urp.ref_id,
  xrprrr.ref_amount                                 实还总额,
  xrprrr.principle - xrprrr.mitigate_principle      实还本金,
  xrprrr.interest - xrprrr.mitigate_interest        实还利息,
  xrprrr.service_fee - xrprrr.mitigate_service_fee  `实还服务费（减免后）`,
  xrprrr.punish - xrprrr.mitigate_collection_relief 实还罚息,
  xrprrr.remain_income                              其他收益,
  DATE(rra.repaid_at)                          `repaid_at`
FROM new_transaction.xjd_repay_plan_repay_record_ref xrprrr
  JOIN test_mzh.repay_record_amount rra ON rra.record_id = xrprrr.record_id and rra.repay_channel = xrprrr.repay_channel
  JOIN new_transaction.user_repayment_plan urp ON urp.id = xrprrr.plan_id
WHERE rra.repaid_at < '%s';
"""
sql_ms = """
SELECT
  t.loan_id,
  t.repay_amt                实还总额,
  t.repay_principle          实还本金,
  t.repay_intrest            实还利息,
  t.service_fee              `实还服务费（减免后）`,
  t.penalty_fee + t.late_fee 滞纳金罚息,
  0                          其他收益,
  date(t.repay_date)         `repaid_at`
FROM new_transaction.new_ms_transaction t;
"""
sql_yghs = """
SELECT
  t.ref_id,
  t.actual_repay_amount                                         实还总额,
  t.actual_principle                                            实还本金,
  t.actual_interest                                             实还利息,
  t.actual_qg_service_fee                                       `实还服务费（减免后）`,
  t.actual_qg_overdue_interest + t.actual_fund_overdue_interest 实还罚息,
  t.actual_compound_interest                                    实还复利,
  date(t.repay_date)                                            repaid_at
FROM new_transaction.yg_huishang_repay_term t;"""

sql_dunjiao = """
SELECT 
  xdp.ref_id,
  xdpr.ref_amount 趸交服务费,
  date(rro.bill_time) repaid_at 
FROM
  new_transaction.xjd_dunjiao_plan_ref xdpr 
  JOIN new_transaction.xjd_dunjiao_plan xdp 
    ON xdp.id = xdpr.plan_id 
  JOIN new_transaction.repay_record_online rro 
    ON rro.id = xdpr.record_id 
    AND rro.pay_approach = xdpr.repay_channel
"""


df_gen = pd.read_sql(sql=sql_loan_plan % (end_date,), con=mysql, chunksize=step)
res = []
for tmp in df_gen:
    res.append(tmp)
df_loan = pd.concat(res)
df_loan['qg_one_time_service_fee'].fillna(0, inplace=True)
df_loan.to_csv(os.path.join(file_path, 'all_loan_%s.csv' % end_date), encoding='utf8', index=None)

df_gen = pd.read_sql(sql=sql_ref % (end_date,), con=mysql, chunksize=step)
res = []
for tmp in df_gen:
    res.append(tmp)
df_ref = pd.concat(res)
df_ref.to_csv(os.path.join(file_path, 'all_ref_%s.csv' % end_date), encoding='utf8', index=None)

df_ms = pd.read_sql(sql=sql_ms, con=mysql)
df_ms.to_csv(os.path.join(file_path, 'ms.csv'), encoding='utf8', index=None)
df_yghs = pd.read_sql(sql=sql_yghs, con=mysql)
df_yghs.to_csv(os.path.join(file_path, 'yghs.csv'), encoding='utf8', index=None)
df_dunjiao = pd.read_sql(sql=sql_dunjiao, con=mysql)
df_dunjiao.to_csv(os.path.join(file_path, 'dunjiao.csv'), encoding='utf8', index=None)
