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

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

'''
表头：
资金方
产品编号
放款时间
放款金额
用户退单日---------同一天内多个退款，最后一个退款日
退还给资金方日
退款本金
退款利息
流水号

注意：财务说无需出 用户提前还款的-我们提前打款给资金方的
'''
engine_qunaer = create_engine('mysql+mysqldb://yulong:ohVU1nPITynSZwR2@172.16.3.201:3306/qunaer_new?charset=utf8', echo=False).connect()
file_path=u'E:/量化派/'

def fund_tk(year,month):
    start_time=datetime.date(year,month,1)
    end_time=start_time+relativedelta(months=+1)
    sql_refund='''
    select t3.fund_name 资金方,t1.product_no 产品编号,date(t2.interest_time) 放款时间,t2.stages 期数 ,t2.loan_amount 放款金额,
    date(t1.repay_time) 退还给资金方日,t1.repay_amount 还款金额,t1.repay_principle 实还本金,
    t1.repay_interest 实还利息,t1.repay_fee_amount 实还资金方服务费,t1.flow_id 流水号,bf.trans_amount 银行金额,
    case t1.type WHEN 4 THEN '正常还款' WHEN 5 THEN '提前结清' WHEN 6 THEN '提前还款' WHEN 7 THEN '退款' END as '类型'
    from qunaer_new.fund_repay_detail t1
    JOIN qunaer_new.fund_pay_detail t2 on t1.product_no = t2.product_no
    JOIN qunaer_new.fund_corp t3 on t1.fund_code = t3.fund_code
    LEFT JOIN qunaer_new.bank_flow bf on bf.flow_id = t1.flow_id
    WHERE t1.repay_time >= '%s' and t1.repay_time < '%s'
    ''' % (start_time,end_time)
    #用户退款---用户退单日
    sql_user_refund='''
    select product_no 产品编号,max(date(refund_time)) 用户退单日 
    from qunaer_new.refund_detail where 
    fund_code != 1 AND 
    product_no in %s 
    group by 1
    '''
    # 提前还款
    sql_user_repay='''
    select product_no 产品编号,max(date(repay_time)) 用户退单日
    from qunaer_new.repay_detail where 
    fund_code != 1 AND 
    product_no in %s 
    group by 1
    '''
    df_refund=pd.read_sql(sql_refund,engine_qunaer)

    df_user_refund=pd.read_sql(sql_user_refund % str(tuple(df_refund[u'产品编号'].astype(str).tolist())),engine_qunaer)
    df_user_repay = pd.read_sql(sql_user_repay % str(tuple(df_refund[u'产品编号'].astype(str).tolist())), engine_qunaer)
    df_user_refund = pd.concat([df_user_refund,df_user_repay],ignore_index=True)
    df_user_refund=df_user_refund.groupby([u'产品编号'])[u'用户退单日'].max().reset_index()
    df_refund=pd.merge(df_refund,df_user_refund,on=[u'产品编号'],how='left')
    df_refund=df_refund[[u'资金方',u'产品编号',u'放款时间',u'期数',u'放款金额',u'退还给资金方日',u'还款金额',u'实还本金',u'实还利息',u'实还资金方服务费',u'流水号'
        , u'银行金额',u'类型',u'用户退单日']]
    df_refund.to_excel(file_path+u'还资金方表-%s_%s.xlsx' % (year,month),index=None,encoding='UTF-8')



if __name__ == '__main__':
    year=2017
    month=9
    fund_tk(year,month)