# -*- encoding: utf8 -*-
from __future__ import unicode_literals
import pandas as pd
from sqlalchemy import create_engine
import sys
reload(sys)
sys.setdefaultencoding('utf8')

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


df_fund_corp = pd.read_sql(sql="select funding_code, name '资金方' from basic.funding_corp", con=engine_audit)
df_loan_account = pd.read_sql(sql="select id loan_account_id, accountName '放款账户' from audit.loan_account", con=engine_audit)
df_repay_channel = pd.read_sql(sql="select id repay_channel_id, approach_name '收款账户' from new_transaction.repay_channel", con=engine_audit)

excel_file = pd.ExcelWriter('放款数据汇总加等级表.xlsx')
################################################
# 放款表汇总
sql = """
select year(t1.loan_paid_at) '放款年', month(t1.loan_paid_at) '放款月', t1.funding_code ,  loan_account_id ,lc.class '风险等级（ABCD）', sum(t1.real_loan_amount) '放款金额', sum(t1.contract_loan_amount) '本金债权',
sum(t1.qg_one_time_service_fee) '一次性扣除手续费QG', sum(t1.funding_one_time_service_fee) ' 一次性扣除手续费点滴',SUM(t2.qg_one_time_service_fee) '应扣趸收服务费'
from loan_manifest t1 JOIN test_mzh.loan_class_contract_1002 lc ON t1.ref_id = lc.ref_id LEFT JOIN new_transaction.xjd_dunjiao_plan t2 ON t1.ref_id = t2.ref_id
where t1.is_active in (1,-2) AND t1.loan_paid_at>="2017-01-01" AND t1.loan_paid_at<"2017-10-01"
group by 1,2,3,4,5;
"""
df = pd.read_sql(sql=sql, con=engine_audit)
df['funding_code'] = df['funding_code'].astype(int)
df['loan_account_id'] = df['loan_account_id'].astype(int)
df = df.merge(df_fund_corp, on='funding_code', how='left').merge(df_loan_account, on='loan_account_id', how='left')
df.drop(['funding_code', 'loan_account_id'], axis=1, inplace=True)
# df.to_excel(u'放款表汇总.xlsx', index=None)
df.to_excel(excel_file, '放款表汇总', columns=['放款年', '放款月', '资金方', '放款账户', '放款金额', '本金债权', '一次性扣除手续费QG', '一次性扣除手续费点滴','风险等级（ABCD）','应扣趸收服务费'], index=None)
excel_file.save()