# encoding: utf8
import pandas as pd
from sqlalchemy import create_engine
engine_audit = create_engine('mysql+mysqldb://internal_r:ArbNgtvlJzZHXsEu@172.16.3.201:3306/new_transaction?charset=utf8', echo=True)



df_fund_corp = pd.read_sql(sql="select funding_code, name '资金方' from basic.funding_corp", con=engine_audit)

sql = """
select t1.funding_code, t3.class '风险等级', year(t1.loan_paid_at) '放款年',  month(t1.loan_paid_at) '放款月',
year(t2.deadline) '到期年',month(t2.deadline) '到期月',sum(t2.principle) '客户应还本金',sum(t2.interest) '客户应还利息'
 from audit.loan_manifest t1
inner join user_repayment_plan t2
on t1.ref_id = t2.ref_id and t1.is_active in (1,-2) and t1.loan_paid_at>='2017-01-01' and t1.loan_paid_at<'2017-10-01'
inner join test_mzh.loan_class_contract_1002  t3
on t1.ref_id = t3.ref_id
group by 1,2,3,4,5,6
"""
path_out = u'./'
excel_file = pd.ExcelWriter(u'现金贷应收本金利息汇总.xlsx')
df = pd.read_sql(sql=sql, con=engine_audit)
df['funding_code'] = df['funding_code'].astype(int)
df = df.merge(df_fund_corp, on='funding_code', how='left')
df.drop(['funding_code'], axis=1, inplace=True)
df.to_excel(excel_file, columns=[u'资金方', u'风险等级', u'放款年', u'放款月', u'到期年', u'到期月',u'客户应还本金', u'客户应还利息'], index=None)
excel_file.save()