# -*- 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


'''
只针对16年的放款的处理
这个只是导出数据，sql 更新，后续可以追加代码，前提是保证数据OK
'''

year=2017
month=10

start_time = datetime.datetime(year, month, 1, 0, 0, 0)
end_time = start_time + relativedelta(months=+1)

engine_qunaer = create_engine('mysql+mysqldb://linfang.wang:#jkl3453YUGuo99@172.16.3.201:3306/qunaer_new?charset=utf8', echo=False).connect()

sql_1='''
select t1.id,t1.product_no,t2.loan_amount,t1.current_stage_no,t1.repay_amount,t1.repay_principle,t1.repay_time
from repay_detail t1 
join pay_detail t2 on t1.product_no = t2.product_no and t2.trans_time < '2017-01-01' 
where t1.repay_time >= '%s' and t1.repay_time < '%s'
''' % (start_time,end_time)
sql_repay='''
select sum(repay_principle) repay_principle from repay_detail where repay_time < '%s' and product_no='%s'
'''
sql_refund='''
select sum(refund_principle) refund_principle from refund_detail where refund_time < '%s' and product_no = '%s'
'''

df_datas=pd.read_sql(sql_1,engine_qunaer)
df_datas.repay_time=pd.to_datetime(df_datas.repay_time)

list_out=[]
for index,row in df_datas.iterrows():
    left_amount=row['loan_amount']
    product_no=row['product_no']
    repay_time=row['repay_time']
    repay_principle=row['repay_principle']
    current_stage_no = row['current_stage_no']
    id=row['id']
    remain = 0
    df_repay=pd.read_sql(sql_repay % (repay_time,product_no),engine_qunaer)
    if df_repay.__len__() > 0:
        left_amount -=df_repay['repay_principle'].sum()
    df_refund=pd.read_sql(sql_refund % (repay_time,product_no),engine_qunaer)
    if df_refund.__len__() > 0:
        left_amount += df_refund['refund_principle'].sum()
    if left_amount > 0 :
        # 如果在贷 < 实还本金，那么 remain = 实还本金-在贷；实还本金=在贷
        if left_amount < repay_principle:
            remain = np.round(repay_principle-left_amount,2)
            repay_principle = left_amount
            list_out.append([id,product_no,current_stage_no,repay_principle,remain])
    elif left_amount < 0:
        print '------big problem-----',product_no


update_sql='''
update repay_detail set repay_principle=%s , remain=%s where id=%s
'''

df_out=pd.DataFrame(list_out,columns=['id','product_no','current_stage_no','repay_principle','remain'])
df_out=df_out.ix[df_out['remain'] > 0]
df_out.id=df_out.id.astype(int)
print len(df_out)
# df_out.to_csv('E:/remain.csv')
engine_qunaer.execute(update_sql,df_out[['repay_principle','remain','id']].values.tolist())








