# -*- coding:utf-8 -*-
# Author: WuKong

import sys
import datetime
import itertools
import pandas as pd
from sqlalchemy import create_engine
import multiprocessing as mtp
import os

reload(sys)
sys.setdefaultencoding('utf8')

engine_new_transaction = create_engine(
    'mysql+mysqldb://yulong:ohVU1nPITynSZwR2@172.16.3.201:3306/new_transaction?charset=utf8',
    echo=True)

engine_payment_center = create_engine(
    'mysql+mysqldb://bowang:in78u6ytgbhj56r@192.168.4.6:8066/payment_center?charset=utf8',
    echo=False)
sql_payment_center = """
SELECT id repay_order_id,order_id from repay_order WHERE id in %s
"""
# 20139595
# SELECT MAX(id) FROM repay_record_online;
# 0178000022434904
def gen_repay_order_id(hengfeng_id):
    hengfeng_id = hengfeng_id[4:]
    return int(hengfeng_id)
abd = "0178000022434904"
abd = abd[4:]
print int(abd)
path = u"""C:\\Users\\wangbo\\Desktop\\十月份对账\\还款数据\\MTF_20171001072103_6684\\恒丰还款数据.xlsx"""
df_hengfeng = pd.read_excel(path)
df_hengfeng = df_hengfeng[[u'订单号',u'交易金额',u'核心清算日期',u'交易账户',u'交易时间']]
df_hengfeng.rename(columns={u"订单号":"hengfeng_id",u"核心清算日期":"bill_time",u"交易账户":"third_order_id",u"交易金额":"amount",u"交易时间":"feiqi_transfer_time"},inplace=True)

df_hengfeng['pay_approach']=20
df_hengfeng['is_ref']=0
df_hengfeng['enable']=1
df_hengfeng['status']=3

df_hengfeng['repay_order_id'] = df_hengfeng['hengfeng_id'].astype(str).apply(lambda x: gen_repay_order_id(x))
df_hengfeng['repay_order_id']= df_hengfeng['repay_order_id'].astype(int)
df_repay_order = pd.read_sql(sql=sql_payment_center % str(tuple(df_hengfeng.repay_order_id.tolist())).replace(',)', ')'),con=engine_payment_center)
df_repay_order['repay_order_id'] = df_repay_order['repay_order_id'].astype(int)
df_hengfeng = pd.merge(df_hengfeng,df_repay_order,how='inner',on='repay_order_id')
del df_hengfeng['repay_order_id']
del df_hengfeng['hengfeng_id']
df_hengfeng.to_sql(name='repay_record_online', con=engine_new_transaction, if_exists='append', index=None,
                    chunksize=1000)