# -*- encoding: utf8 -*-
import sys
import datetime
import pandas as pd
from sqlalchemy import create_engine
from dateutil.relativedelta import relativedelta
from itertools import product

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

'''
基于数据库查询出表
'''



path='E:/hk/qunaer/'
engine_qunaer = create_engine('mysql+mysqldb://yulong:ohVU1nPITynSZwR2@172.16.3.201:3306/qunaer_new?charset=utf8', echo=False).connect()


def pay(year,month):
    start_time = datetime.datetime(year, month, 1, 0, 0, 0)
    end_time = start_time + relativedelta(months=+1)
    sql_pay='''
    select product_no,trans_time,loan_amount,stages,
    CASE is_ptf WHEN 0 THEN 'FALSE' WHEN 1 THEN 'TRUE' END as is_ptf
    ,0 'refund_principle',0 'refund_fee_amount',0 'refund_due_amount'
    from pay_detail 
    where trans_time >='%s' and trans_time < '%s'
    ''' % (start_time,end_time)
    sql_refund='''
    select t1.product_no,t1.refund_time trans_time,t1.refund_amount loan_amount,t2.stages,
    FALSE as is_ptf,
    t1.refund_principle,t1.refund_fee_amount,t1.refund_due_amount
    from refund_detail t1 
    join pay_detail t2 on t1.product_no = t2.product_no
    where t1.refund_time >= '%s' and t1.refund_time < '%s'
    ''' % (start_time,end_time)
    df_pay=pd.read_sql(sql_pay,engine_qunaer)
    df_refund=pd.read_sql(sql_refund,engine_qunaer)
    df=pd.concat([df_pay,df_refund],ignore_index=True)
    df.rename(columns={
        'product_no': '产品编号','trans_time':'放款时间','loan_amount':'放款金额','stages':'期数','is_ptf':'是否转分期订单',
        'refund_principle':'退款本金','refund_fee_amount':'退款服务费','refund_due_amount':'退款罚息'
    },inplace=True)
    df.to_csv(path+'qunaer_pay_%d_%d.csv' % (year,month),index=None)

if __name__ == '__main__':
    # 还款明细
    years = [2017]
    months = [7]
    for year, month in product(years, months):
        print '---------------',year,month
        pay(year,month)
print '-----------------------main done---------------------------------------'