def do_fix():
config = yaml.load(open('../config.yaml', 'r'))
engine = create_engine(
config['database']['url'],
pool_size=2, echo=debug, echo_pool=debug, pool_recycle=3600)
session_maker = sessionmaker(bind=engine)
try:
session = session_maker()
classifier = MobileClassifier()
order_cls = get_order_shard(FIX_USER)
trans_cls = get_trans_shard(FIX_USER)
# Start from batch_order
orders = session.query(order_cls).filter(order_cls.area.is_(None)).order_by(
order_cls.mobile).limit(500)
i = 1
cache = {'header': None, 'area': None}
for order in orders.all():
if order.mobile[0:7] == cache['header']:
order.area = cache['area']
else:
head = order.mobile[0:7]
o, a = classifier.search(int(head))
if o and a:
cache['header'] = head
cache['area'] = '%d:%s' % (o, a)
order.area = cache['area']
else:
print('INVALID HEADER %s' % head)
continue
session.add(order)
i += 1
all_trans = session.query(trans_cls).filter(trans_cls.order_id == order.order_id)
for trans in all_trans.all():
trans.name = '{prod}:{area}:{price}'.format(prod=order.product, area=order.area, price=order.price)
session.add(trans)
if i % 100 == 0:
session.commit()
session.commit()
finally:
session.close()
def from_db(self, user_id, sp_order_id):
info = ''
engine = create_engine(
self.application.config['database']['url'], pool_size=1, echo=True, echo_pool=True, pool_recycle=3600)
session = None
try:
session = sessionmaker(bind=engine)()
order_cls = get_order_shard(user_id)
o = session.query(order_cls).filter(order_cls.sp_order_id == sp_order_id).first()
if o:
order = ET.Element('order')
ET.SubElement(order, 'orderid').text = o.order_id
ET.SubElement(order, 'num').text = '1'
ET.SubElement(order, 'ordercash').text = str((o.value or 0) / 10000)
ET.SubElement(order, 'sporderid').text = o.sp_order_id
ET.SubElement(order, 'account').text = o.mobile
ET.SubElement(order, 'resultno').text = o.back_result or o.result
info = ET.tostring(order, encoding='gbk')
request_log.info("FEE QUERY FROM DB %s", info, extra={'orderid': o.order_id})
else:
order = ET.Element('order')
ET.SubElement(order, 'orderid').text = ''
ET.SubElement(order, 'sporderid').text = sp_order_id
ET.SubElement(order, 'resultno').text = '5007'
info = ET.tostring(order, encoding='gbk')
request_log.info("FEE QUERY FROM DB %s", info, extra={'orderid': 'UNKNOWN'})
except Exception:
request_log.exception("QUERY FAIL", extra={'orderid': 'UNKNOWN'})
finally:
if session:
session.close()
return info
def accounting_user_day(user_id, day, session, last_account):
task_log.info('VERIFY %s', day)
verified = True
# VERIFY
master_id = USER_MAPPING[user_id]
order_cls = get_order_shard(master_id)
trans_cls = get_trans_shard(master_id)
account_cls = get_account_shard()
begin = datetime.datetime(day.year, day.month, day.day)
end = begin + datetime.timedelta(1)
# order -> trans
orders = session.query(order_cls).filter(
order_cls.user_id == user_id).filter(
order_cls.req_time >= begin).filter(
order_cls.req_time < end).order_by(order_cls.req_time)
for order in orders.all():
if order.result == '0':
trans = session.query(trans_cls).filter(
trans_cls.order_id == order.order_id).filter(
trans_cls.type == 'debit').first()
if trans is None:
task_log.error('Order %s without debit', order.order_id)
verified = False
continue
if trans.outcome != order.value:
task_log.error('Order %s without debit', order.order_id)
verified = False
continue
# trans -> order
trans_all = session.query(trans_cls).filter(
trans_cls.user_id == user_id).filter(
trans_cls.create_time >= begin).filter(
trans_cls.create_time < end).order_by(
trans_cls.create_time, trans_cls.trans_id)
balance_in_order = -1
if last_account:
last_id = last_account.id
balance = last_balance = last_account.balance
else:
last_id = 0
balance = last_balance = 0
total = {
'deposit': 0,
'debit': 0,
'debit-manual': 0,
'refund': 0,
'refund-manual': 0,
}
for trans in trans_all.all():
if trans.type != 'deposit':
order = session.query(order_cls).filter(
order_cls.order_id == trans.order_id).first()
if order is None:
task_log.error('Trans %s without debit', trans.order_id)
verified = False
continue
total[trans.type] += trans.income + trans.outcome
balance = balance + trans.income - trans.outcome
balance_in_order = trans.balance
# task_log.debug('balance={balance}, final={final}'.format(balance=balance, final=balance_in_order))
# task_log.debug(total)
if balance_in_order != -1 and balance != balance_in_order:
task_log.debug('balance=%.3f,in_order=%.3f' %
(balance / 10000, balance_in_order / 10000))
verified = False
if verified:
account = account_cls()
account.user_id = user_id
account.last_balance = last_balance
account.last_id = last_id
account.debit = total['debit']
account.debit_manual = total['debit-manual']
account.refund = total['refund']
account.refund_manual = total['refund-manual']
account.deposit = total['deposit']
account.balance = balance
account.account_date = begin
session.add(account)
session.commit()
return account
else:
task_log.info('VERIFY result=%s', verified)
return None
def persist_single(self, order_id, session, master, mode):
task_log.info('PROCESS %s', order_id)
try:
order_key = 'order:%s' % order_id
order_info = master.hgetall(order_key)
key_id = get_i(order_info, 'id')
user_id = get_s(order_info, 'user_id')
if user_id not in MASTER_MAPPING:
task_log.info("RELOAD CONFIG FOR %s", user_id)
load_mapping(True)
master_id = MASTER_MAPPING[user_id]
order_cls = get_order_shard(master_id)
order = None
if key_id:
order = session.query(order_cls).filter(order_cls.id == key_id).one()
if order is None:
order = order_cls()
sp_order_id = get_s(order_info, 'sp_order_id')
price = get_i(order_info, 'price')
order.order_id = order_id
order.user_id = user_id
order.price = price
order.mobile = get_s(order_info, 'mobile')
order.sp_order_id = sp_order_id
order.req_time = get_t(order_info, 'req_time')
order.resp_time = get_t(order_info, 'resp_time')
order.back_time = get_t(order_info, 'back_time')
order.result = get_s(order_info, 'result') or 'None'
order.back_result = get_s(order_info, 'back_result')
order.value = get_i(order_info, 'value')
order.balance = get_i(order_info, 'balance')
order.area = get_s(order_info, 'area')
order.product = get_s(order_info, 'product')
order.scope = get_s(order_info, 'scope')
if order.scope == 'None':
order.scope = None
session.add(order)
session.commit()
if mode == MODE_UNFINISHED:
master.hset(order_key, 'id', order.id) # set id
# --------------UP--------------------
stage = get_i(order_info, 'stage')
if stage:
for i in range(1, stage + 1):
up_req_time = get_t(order_info, 'up_req_time/%d' % i)
if up_req_time is None:
continue
up_order_cls = get_up_order_shard(master_id)
up_order = None
up_key_id = get_i(order_info, 'id/%d' % i)
if up_key_id:
up_order = session.query(up_order_cls).filter(up_order_cls.id == up_key_id).one()
if up_order is None:
up_order = up_order_cls()
up_order.stage = i
up_order.order_id = order_id
up_order.up_order_id = get_s(order_info, 'up_order_id/%d' % i)
up_order.route = get_s(order_info, 'route/%d' % i)
up_order.price = price
up_order.cost = get_i(order_info, 'cost/%d' % i)
up_order.up_cost = get_s(order_info, 'up_cost/%d' % i)
up_order.up_req_time = up_req_time
up_order.up_resp_time = get_t(order_info, 'up_resp_time/%d' % i)
up_order.up_back_time = get_t(order_info, 'up_back_time/%d' % i)
up_order.up_result = get_s(order_info, 'up_result/%d' % i)
up_order.up_back_result = get_s(order_info, 'up_back_result/%d' % i)
session.add(up_order)
session.commit()
if mode == MODE_UNFINISHED:
master.hset(order_key, 'id/%d' % i, up_order.id) # set id
if mode == MODE_FINISHED:
master.expire(order_key, 8 * 3600)
master.expire('map:%s:%s' % (user_id, sp_order_id), 24 * 3600)
return 0
except Exception as e:
task_log.exception('PROCESS %s FAIL(MODE=%s)', order_id, mode)
session.rollback()
return -1
def do_fix():
config = yaml.load(open('../config.yaml', 'r'))
engine = create_engine(config['database']['url'],
pool_size=2,
echo=debug,
echo_pool=debug,
pool_recycle=3600)
session_maker = sessionmaker(bind=engine)
try:
session = session_maker()
up_order_cls = get_up_order_shard(FIX_USER)
order_cls = get_order_shard(FIX_USER)
trans_cls = get_trans_shard(FIX_USER)
i = 1
for line in FIX_RECORD.split('\n'):
if len(line) < 10:
continue
print(line)
tsp = dt.datetime.strptime(line[0:19], '%Y-%m-%d %H:%M:%S')
sp_order_id = line[20:34]
order_id = line[35:58]
cost = line[59:]
up_order = session.query(up_order_cls).filter(
up_order_cls.up_order_id == sp_order_id).first()
# Verify UpOrder
if up_order:
if up_order.order_id != order_id:
print("INVALID " + sp_order_id)
break
if up_order.up_cost != cost:
print("INVALID " + sp_order_id)
break
order = session.query(order_cls).filter(
order_cls.order_id == order_id).first()
if order is None:
print("INVALID " + order_id)
break
print('price=%d, cost=%s' % (order.value, cost))
t = tsp.strftime('%Y%m%d%H%M%S')
fix_trans = trans_cls(trans_id='F%s%08d' % (t, i),
type='refund-manual',
income=order.value,
outcome=0,
balance=0,
order_id=order.order_id,
user_id=FIX_USER,
account=order.mobile,
create_time=tsp)
i += 1
session.add(fix_trans)
session.commit()
finally:
session.close()
def persist_single(self, order_id, session, master):
try:
key = 'order:%s' % order_id
order_info = master.hgetall(key)
# key_id = get_i(order_info, 'id')
user_id = get_s(order_info, 'user_id')
master_id = get_s(order_info, 'master_id')
if master_id is None:
master_id = user_id
price = get_i(order_info, 'price')
value = get_i(order_info, 'value')
sp_order_id = get_s(order_info, 'sp_order_id')
result = get_s(order_info, 'result')
order_cls = get_order_shard(master_id)
order = session.query(order_cls).filter(
order_cls.order_id == order_id).first()
print('FIX ORDER %s; need_fix=%s / %s' %
(order_id, order is None, user_id))
if result == '10007' and value is None:
print('JUST SKIP THE ORDER')
elif order is None:
"""
1) mobile 长度超长
2) result 为null （异常退出）
3) 表不存在
"""
order = order_cls()
order.order_id = order_id
order.user_id = user_id
order.price = price
order.mobile = get_s(order_info, 'mobile')
order.sp_order_id = sp_order_id
order.req_time = get_t(order_info, 'req_time')
order.resp_time = get_t(order_info, 'resp_time')
order.back_time = get_t(order_info, 'back_time')
order.result = result
order.back_result = get_s(order_info, 'back_result')
order.value = value
order.balance = get_i(order_info, 'balance')
order.area = get_s(order_info, 'area')
order.product = get_s(order_info, 'product')
if len(order.mobile) > 11:
print("\tFIX1: MOBILE INVALID")
order.mobile = order.mobile[0:11]
elif order.result is None:
print("\tFIX2: RESULT IS NULL")
order.result = '5003'
else:
print("\tFIX?: UNKNOWN")
if not self.dry_run:
session.add(order)
session.commit()
# --------------UP--------------------
stage = get_i(order_info, 'stage')
if stage:
for i in range(1, stage + 1):
up_req_time = get_t(order_info, 'up_req_time/%d' % i)
if up_req_time:
# check
up_order_cls = get_up_order_shard(master_id)
up_order = session.query(up_order_cls).filter(
up_order_cls.order_id == order_id).filter(
up_order_cls.stage == stage).first()
print('\tFIX UP_ORDER %s(%d) need_fix=%s' %
(order_id, i, up_order is None))
if up_order is None:
up_order = up_order_cls()
up_order.stage = i
up_order.order_id = order_id
up_order.up_order_id = get_s(
order_info, 'up_order_id/%d' % i)
up_order.route = get_s(order_info, 'route/%d' % i)
up_order.price = price
up_order.cost = get_i(order_info, 'cost/%d' % i)
up_order.up_cost = get_s(order_info,
'up_cost/%d' % i)
up_order.up_req_time = up_req_time
up_order.up_resp_time = get_t(
order_info, 'up_resp_time/%d' % i)
up_order.up_back_time = get_t(
order_info, 'up_back_time/%d' % i)
up_order.up_result = get_s(order_info,
'up_result/%d' % i)
up_order.up_back_result = get_s(
order_info, 'up_back_result/%d' % i)
if not self.dry_run:
session.add(up_order)
session.commit()
print("EXPIRE %s" % key)
if not self.dry_run:
master.expire(key, 1800)
master.expire(('map:%s' % sp_order_id), 1800)
return 0
except Exception as e:
print('PROCESS %s FAIL: %s' % (order_id, e))
return -1
def do_fix():
config = yaml.load(open('../config.yaml', 'r'))
engine_jaguar = create_engine(
'mysql+mysqlconnector://madeira:[email protected]/jaguar',
pool_size=2,
echo=debug,
echo_pool=debug,
pool_recycle=3600)
engine_madeira = create_engine(config['database']['url'],
pool_size=2,
echo=debug,
echo_pool=debug,
pool_recycle=3600)
session_maker_jaguar = sessionmaker(bind=engine_jaguar)
session_maker_madeira = sessionmaker(bind=engine_madeira)
try:
session_jaguar = session_maker_jaguar()
session_madeira = session_maker_madeira()
up_order_cls = get_up_order_shard(FIX_USER)
order_cls = get_order_shard(FIX_USER)
trans_cls = get_trans_shard(FIX_USER)
# Start from batch_order
jag_batch_orders = session_jaguar.query(JagBatchOrder).filter(
JagBatchOrder.user_id == 'tb711').order_by(
JagBatchOrder.create_time)
i = 1
t = 1
for jag_batch_order in jag_batch_orders.all():
jag_ct_order = session_jaguar.query(JagCtOrder).filter(
JagCtOrder.request_no == jag_batch_order.request).first()
if jag_ct_order:
resp_time = jag_ct_order.update_time
back_time = jag_ct_order.callback_time
result = '00000'
back_result = jag_ct_order.result
else:
resp_time = jag_batch_order.update_time
back_time = None
result = jag_batch_order.result
back_result = None
# Q2014103014243610000013
# P2014103014243600000018
# F2014110111074200000015
# Create order
tsp = jag_batch_order.create_time.strftime('%Y%m%d%H%M%S')
order_id = 'Q%s1%07d' % (tsp, i)
sp_order_id = 'P%s%08d' % (tsp, i)
price = int(int(jag_batch_order.face_value) / 1000)
cost = price * 9600
value = price * 9700
i += 1
order = order_cls(order_id=order_id,
user_id=FIX_USER,
price=price,
mobile=jag_batch_order.number,
sp_order_id=sp_order_id,
req_time=jag_batch_order.create_time,
resp_time=resp_time,
back_time=back_time,
result=result,
value=value,
back_result=back_result,
product='data')
session_madeira.add(order)
# Create up_order if exist
if jag_ct_order:
up_order = up_order_cls(
stage=1,
order_id=order_id,
up_order_id=order_id,
route='21cn',
price=price,
cost=cost,
up_cost=cost,
up_req_time=jag_batch_order.create_time,
up_resp_time=resp_time,
up_back_time=back_time,
up_result=int(result),
up_back_result=back_result)
session_madeira.add(up_order)
# Create transaction
if result == '00000':
trans = trans_cls(trans_id='F%s%08d' % (tsp, t),
type='debit',
income=0,
outcome=value,
balance=0,
order_id=order_id,
user_id=FIX_USER,
account=jag_batch_order.number,
create_time=jag_batch_order.create_time)
t += 1
session_madeira.add(trans)
if back_result and back_result != '00000':
trans = trans_cls(trans_id='F%s%08d' % (tsp, t),
type='refund',
income=value,
outcome=0,
balance=0,
order_id=order_id,
user_id=FIX_USER,
account=jag_batch_order.number,
create_time=back_time)
t += 1
session_madeira.add(trans)
session_madeira.commit()
finally:
session_jaguar.close()
session_madeira.close()
def do_fix():
config = yaml.load(open('../config.yaml', 'r'))
engine = create_engine(config['database']['url'],
pool_size=2,
echo=debug,
echo_pool=debug,
pool_recycle=3600)
session_maker = sessionmaker(bind=engine)
try:
session = session_maker()
order_cls = get_order_shard(FIX_USER)
trans_cls = get_trans_shard(FIX_USER)
# Fix trans
orders = session.query(order_cls).filter(
order_cls.req_time >= FIX_BEGIN).filter(
order_cls.req_time < FIX_END).order_by(order_cls.req_time)
i = 0
for order in orders.all():
# DEBIT
if order.result == '0':
trans = session.query(trans_cls).filter(
trans_cls.order_id == order.order_id).filter(
trans_cls.type == 'debit').first()
if trans is None:
tsp = order.req_time.strftime('%Y%m%d%H%M%S')
fix_trans = trans_cls(trans_id='F%s%08d' % (tsp, i),
type='debit',
income=0,
outcome=order.value,
balance=0,
order_id=order.order_id,
user_id=FIX_USER,
account=order.mobile,
create_time=order.req_time)
i += 1
session.add(fix_trans)
session.commit()
print('Fix DEBIT %s' % fix_trans)
# REFUND
if order.back_result == '9':
trans = session.query(trans_cls).filter(
trans_cls.order_id == order.order_id).filter(
trans_cls.type == 'refund').first()
if trans is None:
tsp = order.req_time.strftime('%Y%m%d%H%M%S')
fix_trans = trans_cls(trans_id='F%s%08d' % (tsp, i),
type='refund',
income=order.value,
outcome=0,
balance=0,
order_id=order.order_id,
user_id=FIX_USER,
account=order.mobile,
create_time=order.req_time)
i += 1
session.add(fix_trans)
session.commit()
print('Fix REFUND %s' % fix_trans)
# Fix balance (Generic)
all_trans = session.query(trans_cls).order_by(trans_cls.create_time,
trans_cls.trans_id)
balance = 0
i = 0
for trans in all_trans:
balance = balance + trans.income - trans.outcome
if balance != trans.balance:
print('Update trans trans_id=%s, time=%s' %
(trans.trans_id, trans.create_time))
trans.balance = balance
session.add(trans)
i += 1
if i % 100 == 0:
session.commit()
session.commit()
finally:
session.close()