# -*- encoding: utf8 -*-
import sys
import os
import datetime
import numpy as np
import pandas as pd
from sqlalchemy import create_engine
from dateutil.relativedelta import relativedelta
reload(sys)
sys.setdefaultencoding("utf-8")
pd.options.mode.chained_assignment = None
# 去哪儿源文件
path = u'E:/量化派/去哪儿/去哪儿账单文件/201708'

'''
@author linfang
哈银模式：只有发生退款，可考虑是否债转了。
优先对账，后扣款
1、针对 资金方还款表中提取退款的数据
2、更新 退款表中的资金方，更新还款表中的资金方
3、还款时间 > 退款时间的规则进行筛选
4、生成债转表中的放款表
5、生成债转表中的还款计划
'''
engine_qunaer = create_engine('mysql+mysqldb://linfang.wang:#jkl3453YUGuo99@172.16.3.201:3306/qunaer_new?charset=utf8', echo=False).connect()


# 提取退款数据
def step_1(year,month):
    start_time=datetime.date(year,month,1)
    end_time = start_time + relativedelta(months=+1)
    sql_1 = '''
    select product_no,max(repay_time) fund_repay_time from qunaer_new.fund_repay_detail 
    where repay_time >= '%s' and repay_time < '%s' and fund_code = 3 and type=7
    GROUP by 1
    ''' % (start_time,end_time)
    df=pd.read_sql(sql_1,engine_qunaer)
    df['fund_repay_time']=pd.to_datetime(df['fund_repay_time']).dt.date
    df.drop_duplicates(['product_no'],inplace=True)
    sql_pay='''
    select product_no ,loan_amount AS all_loan_amount from qunaer_new.pay_detail where status = 1 
    and fund_code = 3 and product_no in %s 
    ''' % str(tuple(df.product_no.astype(str).tolist()))
    df_pay=pd.read_sql(sql_pay,engine_qunaer)
    df=pd.merge(df,df_pay,on='product_no',how='left')
    return df

# 提取用户退款数据
def step_2(product_nos):
    if len(product_nos) == 0:
        return
    sql_1='''
    select id refund_id,fund_code src_fund_code,product_no,refund_principle,refund_time
    from qunaer_new.refund_detail where product_no in %s AND fund_code != 1
    ''' % str(tuple(product_nos))
    return pd.read_sql(sql_1,engine_qunaer)

# 提取用户还款数据
def step_3(product_nos):
    if len(product_nos) == 0:
        return
    sql_1='''
    select id repay_id,fund_code src_fund_code,product_no,repay_principle,repay_time
    from qunaer_new.repay_detail where product_no in %s  AND fund_code != 1
    ''' % str(tuple(product_nos))
    return pd.read_sql(sql_1,engine_qunaer)

# 更新还款表+退款表的资金方的ID，
def step_4(year,month):
    df_fund_repay=step_1(year,month)
    df_refund=step_2(df_fund_repay['product_no'].astype(str).tolist())
    df_refund['refund_time']=pd.to_datetime(df_refund['refund_time']).dt.date
    df_repay = step_3(df_fund_repay['product_no'].astype(str).tolist())
    df_repay['repay_time']=pd.to_datetime(df_repay['repay_time']).dt.date
    df_refund=pd.merge(df_refund,df_fund_repay[['product_no','fund_repay_time']],on=['product_no'],how='left')
    df_repay=pd.merge(df_repay,df_fund_repay[['product_no','fund_repay_time']],on=['product_no'],how='left')
    print '====有还款有退款====',df_fund_repay.ix[(df_fund_repay.product_no.isin(df_repay.product_no)) & (df_fund_repay.product_no.isin(df_refund.product_no))]
    #提取债转后的退款数据
    df_refund_after=df_refund.ix[df_refund.refund_time > df_refund.fund_repay_time]
    update_sql_1='''
    update refund_detail set fund_code = 1 where id = %s
    '''
    engine_qunaer.execute(update_sql_1,df_refund_after[['refund_id']].values.tolist())
    #提取债转后的还款数据
    df_repay_after=df_repay.ix[df_repay.repay_time > df_repay.fund_repay_time]
    update_sql_2 = '''
    update repay_detail set fund_code = 1 where id = %s
    '''
    engine_qunaer.execute(update_sql_2, df_repay_after[['repay_id']].values.tolist())
    # #债转前的退款数据
    # df_refund_before=df_refund.ix[df_refund.refund_time < df_refund.package_time]
    # df_repay_before=df_repay.ix[df_repay.repay_time < df_repay.package_time]
    # update_sql_3='''
    # update refund_detail set fund_code= %s where id= %s
    # '''
    # engine_qunaer.execute(update_sql_3, df_refund_before[['fund_code','refund_id']].values.tolist())
    # update_sql_4='''
    # update repay_detail set fund_code = %s where id = %s
    # '''
    # engine_qunaer.execute(update_sql_4, df_repay_before[['fund_code', 'repay_id']].values.tolist())

# 录入债转后的放款表----债转后的deadline 还是以 loan_time 计算deadline的
def step_5(year,month):
    df_fund_repay=step_1(year,month)
    sql_repay='''
    select product_no,sum(repay_principle) repay_principle from 
    qunaer_new.repay_detail where product_no in %s and fund_code=3 and repay_status=1
    group by 1
    ''' % str(tuple(df_fund_repay['product_no'].astype(str).tolist())).replace(',)',')')
    df_repay=pd.read_sql(sql_repay,engine_qunaer)
    sql_refund='''
    select product_no,-sum(refund_principle) repay_principle from qunaer_new.refund_detail
    WHERE product_no in %s  AND fund_code = 3 and refund_status=1
    GROUP by 1
    ''' % str(tuple(df_fund_repay['product_no'].astype(str).tolist())).replace(',)',')')
    df_refund=pd.read_sql(sql_refund,engine_qunaer)
    df_repay_all=pd.concat([df_repay,df_refund],ignore_index=True)
    df_repay_all=df_repay_all.groupby(['product_no']).agg({'repay_principle':'sum'}).reset_index()
    df_fund_repay=pd.merge(df_fund_repay,df_repay_all,on=['product_no'],how='left')
    print '====未找到的订单====',df_repay_all.ix[~df_repay_all.product_no.isin(df_fund_repay.product_no)]
    # diff---债转后的金额
    df_fund_repay['diff']=np.round(df_fund_repay['all_loan_amount']-df_fund_repay['repay_principle'],2)
    df_fund_repay=df_fund_repay.ix[df_fund_repay['diff'] != 0]
    sql_pay='''
    select product_no,user_no,loan_time,loan_amount,stages,is_ptf,status,rate
    from qunaer_new.pay_detail where product_no in %s 
    ''' % str(tuple(df_fund_repay['product_no'].astype(str).tolist())).replace(',)',')')
    df_pay=pd.read_sql(sql_pay,engine_qunaer)
    df_fund_repay=pd.merge(df_fund_repay,df_pay,on='product_no',how='left')
    df_fund_repay['fund_code'] = 1
    df_fund_repay['trans_time']=pd.to_datetime(df_fund_repay['fund_repay_time'])
    #针对已有的保理数据
    sql_baoli='''
    select product_no from qunaer_new.baoli_pay_detail where fund_code = 3
    '''
    df_baoli=pd.read_sql(sql_baoli,engine_qunaer)
    df_fund_repay_no=df_fund_repay.ix[~df_fund_repay.product_no.isin(df_baoli.product_no)]
    df_fund_repay_yes = df_fund_repay.ix[df_fund_repay.product_no.isin(df_baoli.product_no)]
    print '=====有问题数据====',len(df_fund_repay_yes)
    # stages==== 可以根据还款的次数进行剔除
    sql_repay='''
    select product_no,count(current_stage_no) left_stages from qunaer_new.repay_detail where fund_code=3 AND product_no in %s
    GROUP by 1
    ''' % str(tuple(df_fund_repay_no['product_no'].astype(str).tolist())).replace(',)',')')
    df_left_stages=pd.read_sql(sql_repay,engine_qunaer)
    df_fund_repay_no=pd.merge(df_fund_repay_no,df_left_stages,on='product_no',how='left')
    df_fund_repay_no['left_stages'].fillna(0,inplace=True)
    df_fund_repay_no.ix[df_fund_repay_no.stages==1,'left_stages'] = 0
    df_fund_repay_no['new_stages'] = df_fund_repay_no['stages'] - df_fund_repay_no['left_stages']
    #TODO 做这儿一定要注意，剩余期数这个概念
    df_fund_repay_no.to_csv('E:/ccc.csv')
    df_fund_repay_no=df_fund_repay_no[['fund_code','product_no','new_stages','stages','status','is_ptf','loan_time','diff','trans_time','user_no','rate']]
    df_fund_repay_no.rename(columns={'diff':'loan_amount'},inplace=True)
    df_fund_repay_no.to_sql('baoli_pay_detail',engine_qunaer,if_exists='append',index=None,chunksize=10000)


# # 债转后的保理放款表的还款计划更新
def step_6(year,month):
    start_time = datetime.date(year, month, 1)
    end_time = start_time + relativedelta(months=+1)
    sql_1='''
    select t1.product_no,t1.new_stages,t1.stages from qunaer_new.baoli_pay_detail t1
    JOIN qunaer_new.pay_detail t2 on t1.product_no = t2.product_no and t2.fund_code = 3
    where t1.trans_time >= '%s' and t1.trans_time < '%s'
    ''' % (start_time,end_time)
    df_1=pd.read_sql(sql_1,engine_qunaer)
    #还款计划
    sql_2='''
    select id plan_id,product_no,current_stage_no user_current_stage_no
    from qunaer_new.repayment_plan_2017 where product_no in %s
    ''' % str(tuple(df_1['product_no'].astype(str).tolist()))
    df_2=pd.read_sql(sql_2,engine_qunaer)
    df_2['fund_code']=1
    sql_3='''
    select product_no,current_stage_no user_current_stage_no,repay_no from qunaer_new.repay_detail
    where fund_code = 3 AND product_no in %s
    ''' %  str(tuple(df_1['product_no'].astype(str).tolist()))
    df_3=pd.read_sql(sql_3,engine_qunaer)
    df_2=pd.merge(df_2,df_3,on=['product_no','user_current_stage_no'],how='left')
    df_2=df_2.ix[df_2.repay_no.isnull()]
    df_2=pd.merge(df_2,df_1,on='product_no',how='left')
    update_sql='''
    update repayment_plan_2017 set fund_code=1 where fund_code = 3 and plan_id = %s
    '''
    engine_qunaer.execute(update_sql,df_2[['plan_id']].values.tolist())


# 债转校验--
def check_baoli(year,month):
    sql_fund='''
    select product_no,sum(repay_principle) fund_repay_principle from qunaer_new.fund_repay_detail 
    where fund_code = 3 and type = 7
    GROUP by 1
    '''
    sql_refund='''
    select product_no,-sum(refund_principle) repay_principle from qunaer_new.refund_detail 
    where fund_code = 3 and product_no in %s
    group by 1
    '''
    sql_repay='''
    select product_no,sum(repay_principle) repay_principle from qunaer_new.repay_detail 
    WHERE fund_code = 3 and product_no in %s
    GROUP BY 1
    '''
    sql_baoli='''
    select product_no,loan_amount ,trans_time from qunaer_new.baoli_pay_detail
    '''
    df_baoli=pd.read_sql(sql_baoli,engine_qunaer)
    df_fund=pd.read_sql(sql_fund,engine_qunaer)
    df_refund=pd.read_sql(sql_refund % str(tuple(df_fund.product_no.astype(str).tolist())),engine_qunaer)
    df_repay = pd.read_sql(sql_repay % str(tuple(df_fund.product_no.astype(str).tolist())), engine_qunaer)
    df_repay=pd.concat([df_refund,df_repay])
    df_repay=df_repay.groupby('product_no')['repay_principle'].sum().reset_index()
    df_diff=pd.merge(df_fund,df_repay,on='product_no',how='outer')
    df_diff=pd.merge(df_diff,df_baoli,on='product_no',how='outer')
    df_diff.fillna(0,inplace=True)
    df_diff['diff']=np.round(df_diff['fund_repay_principle']-df_diff['repay_principle'],2)
    df_diff.to_csv('E:/cccc.csv')


if __name__ == '__main__':
    year = 2017
    month = 10
    #===针对退款的情况进行债转处理
    #===到期日
    # step_4(year,month)
    # step_5(year,month)
    #更新，客户的还款计划中的资金方 ID
    # step_6(year,month)


    #--根据客户的还款计划更新保理的还款计划
    # 更新还款计划---哈银在9月上线，所以就无需更新了，但是之后10月必须更新的。
    # check_baoli(year,month)






