# -*- encoding: utf8 -*-
import sys
import datetime
import calendar
import pandas as pd
from sqlalchemy import create_engine

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



'''
@author linfang
1、去哪儿坏账
'''

year=2017
month=10
path = u'E:/量化派/去哪儿/常规出账/'
a,b=calendar.monthrange(year,month)
#本月最后一天
last_day=datetime.date(year,month,b)
#17年放款的还款计划表
table_2017_name='repayment_plan_2017'
#16年放款的还款计划表
table_2016_name='repayment_plan_2016'

engine_qunaer = create_engine('mysql+mysqldb://internal_r:ArbNgtvlJzZHXsEu@172.16.3.201:3306/qunaer_new?charset=utf8',
							  echo=False).connect()
#未还款
sql_2017_unrepay='''
select product_no,current_stage_no,deadline,principle
from %s
where status = 1 and datediff('%s',date(deadline)) >= 90 AND fund_code = 1
''' % (table_2017_name,last_day)
#还款1半的
sql_2017_repay='''
select product_no,current_stage_no,deadline,(principle-repay_principle) principle
from %s
where status = 2 and repay_amount > 0 and principle > repay_principle and datediff('%s',date(deadline)) >= 90  AND fund_code = 1
''' % (table_2017_name,last_day)
#未还款
sql_2016_unrepay='''
select product_no,current_stage_no,deadline,principle
from %s
where status = 1 and principle > 0 and datediff('%s',date(deadline)) >= 90
''' % (table_2016_name,last_day)
#已还款
sql_2016_repay='''
select product_no,current_stage_no,deadline,(principle-repay_principle) principle
from %s
where status = 2 and repay_amount > 0 and principle > repay_principle and principle > 0 and datediff('%s',date(deadline)) >= 90
''' % (table_2016_name,last_day)

df_2017_unrepay=pd.read_sql(sql_2017_unrepay,engine_qunaer)
df_2017_repay=pd.read_sql(sql_2017_repay,engine_qunaer)
df_2016_unrepay=pd.read_sql(sql_2016_unrepay,engine_qunaer)
df_2016_repay=pd.read_sql(sql_2016_repay,engine_qunaer)
df=pd.concat([df_2017_unrepay,df_2017_repay,df_2016_unrepay,df_2016_repay],ignore_index=True,axis=0)
df.rename(columns={'product_no':'产品编号','current_stage_no':'期数','deadline':'应还日期','principle':'未还本金'},inplace=True)
df.to_excel(path+u'坏账_%s_%s.xlsx' %(year,month),columns=df.columns,index=None, encoding='utf8')

