def purge(pattern):
cfg = yaml.load(open('config.yaml', 'r'))
sentinels = [(c['ip'], c['port']) for c in cfg['cache']]
sentinel = Sentinel(sentinels,
socket_timeout=0.1,
decode_responses=True,
db=6)
master = sentinel.master_for('machado')
engine = create_engine(cfg['database']['machado'],
pool_size=2,
echo=True,
echo_pool=True,
pool_recycle=3600)
session = sessionmaker(bind=engine)()
try:
q = session.query(Box).filter(Box.filename.like(pattern + '%'))
for box in q.all():
print(box)
part = box.partition
card_part = get_card_shard(part)
q2 = session.query(card_part).filter(card_part.box_id == box.id)
for card in q2.all():
price = card.price
card_id = card.card_id
print(
'REMOVE CARD %s %d' %
(card_id, master.lrem('list:card:%d' % price, 0, card_id)))
master.delete('card:%s' % card_id)
session.delete(card)
master.delete('box:%d' % box.id)
session.delete(box)
session.commit()
finally:
session.close()
def loading(self):
engine = self.application.engine['machado']
session = sessionmaker(bind=engine)()
try:
for partition in range(1, 12):
card_cls = get_card_shard(partition)
current = -1
p = -1
while True:
q = session.query(
card_cls.id,
card_cls.card_id).filter(card_cls.id > current).filter(
card_cls.status != 'removed').order_by(
card_cls.id).limit(1000)
for xid, card_id in q.all():
p = xid
cid = int(card_id)
if cid in self.card_set:
print('INVALID %s' % cid)
else:
self.card_set.add(cid)
if p == current:
break
else:
current = p
yield gen.Task(IOLoop.instance().add_timeout, 1)
self.loaded = True
print('Full loaded, size=%dm.' %
(getsizeof(self.card_set) / 1024 / 1024))
finally:
session.close()
def update_rollback_card(self, card_list):
for card_id in card_list:
request_log.error('ROLLBACK CARD[{0}]'.format(card_id))
box_id, price, status = self.master.hmget('card:%s' % card_id,
'box', 'price', 'status')
box_key = 'box:{0}'.format(box_id)
redis_box_info = self.master.hgetall(box_key)
if not redis_box_info:
request_log.error(
'ROLLBACK CARD[{0}] ERROR!!!'.format(card_id))
self.master.sadd('set:card:rollback:unknown', card_id)
continue
#修改卡盒里面的计数信息
self.master.hincrby('box:{0}'.format(box_id), 'inuse', -1)
self.master.hincrby('box:{0}'.format(box_id), 'ready', 1)
# 根据冻结状态设置数据
if redis_box_info.get('status') != 'frozen':
card_list_key = 'list:card:{0}:{1}:{2}'.format(
redis_box_info['card_type'], redis_box_info['pool'],
redis_box_info['price'])
self.master.rpush(card_list_key, card_id)
else:
engine = self.application.engine['machado']
db_session = sessionmaker(bind=engine)()
card_part = get_card_shard(redis_box_info['part'])
try:
db_session.query(card_part).filter(
and_(card_part.card_id == card_id,
card_part.box_id == box_id)).update(
{'status': 'frozen'})
db_session.commit()
except:
request_log.exception(
'ROLLBACK CARD[{0}] EXCEPTION!!!'.format(card_id))
finally:
db_session.close()
def post(self, path):
if 'card_query' not in self.current_user['roles']:
return self.finish()
self.user_id = self.current_user['user_id']
body = self.request.body.decode()
args = json.loads(body)
if not {'box_id', 'card_id', 'password', 'no_modify'} <= set(args):
return self.finish(json.dumps({'status': 'fail', 'msg': '输入异常'}))
box_id = args['box_id']
card_id = args['card_id']
password = args['password']
no_modify = args['no_modify']
session = None
try:
session = self.session('machado')
#数据库中查询卡盒信息
db_box_info = None
query = session.query(Box).filter(Box.id == box_id)
if 'admin' not in self.current_user['role']:
query = query.filter(Box.user_id == self.user_id)
db_box_info = query.one_or_none()
if db_box_info == None:
return self.finish(
json.dumps({
'status': 'fail',
'msg': '卡盒信息出错'
}))
#redis查询单张卡的信息
partition = self.slave.hget('card:%s' % card_id, 'part')
if partition is None:
return self.finish(
json.dumps({
'status': 'fail',
'msg': '卡信息错 %s' % partition
}))
card_class = get_card_shard(partition)
query = session.query(card_class).filter(
card_class.card_id == card_id, card_class.box_id == box_id)
if 'admin' not in self.current_user['role']:
query = query.filter(card_class.user_id == self.user_id)
card = query.one_or_none()
if card is None:
return self.finish(
json.dumps({
'status': 'fail',
'msg': '无效的卡(%s)' % card_id
}))
if card.status != 'error':
return self.finish(
json.dumps({
'status': 'fail',
'msg': '无效的状态(%s)' % card.status
}))
card_pass = None
if not no_modify:
user_id = card.user_id
user = self.application.config.get('user').get(user_id)
aes_pass = user['aes_pass']
aes_iv = user['aes_iv']
card_pass = aes_encrypt(password, aes_pass, aes_iv)
card.password = card_pass
card.status = db_box_info.status
session.add(card)
session.commit()
# update box
self.master.hincrby('box:%s' % box_id, 'error', -1)
self.master.hincrby('box:%s' % box_id, 'ready', 1)
# update Caching
request_log.info(
'USER[{0}] MODIFE CARD_PASS BOX[{1}] CARD[{2}] OLD_CARD_PASS[{3}] >> NEW_CARD_PASS[{4}]'
.format(self.current_user['id'], box_id, card_id,
self.master.hget('card:%s' % card_id, 'password'),
card_pass))
if card_pass:
self.master.hset('card:%s' % card_id, 'password', card_pass)
# 判断是否需要挪到可用列表
if card.status == 'ready':
card_type, pool = self.master.hmget('box:{0}'.format(box_id),
'card_type', 'pool')
card_list_key = 'list:card:{0}:{1}:{2}'.format(
card_type, pool, card.price)
self.master.rpush(card_list_key, card.card_id)
self.finish(json.dumps({'status': 'ok', 'msg': '成功'}))
except Exception as e:
request_log.exception('UPDATE CARD ERROR')
self.finish(json.dumps({'status': 'fail', 'msg': '未知异常'}))
finally:
if session:
session.close()
def post_commit(self):
user_id = self.current_user['user_id']
status = 'fail'
msg = ''
master = self.master
engine = self.application.engine['machado']
session = sessionmaker(bind=engine)()
number_check = self.application.number_check
try:
if self.master.exists('flag:task'):
log_request.info('STOP FLAG FOUND!')
raise RuntimeError('系统临时维护，请与技术人员联系。')
if not number_check.is_loaded():
raise RuntimeError('重复卡号检查器未加载完毕，请稍后再试。')
# checking
price0 = None
card_set = set()
pass_set = set()
count = 0
card_lines = self.master.lrange('list:easy:card:' + user_id, 0, -1)
if len(card_lines) == 0:
raise RuntimeError('你还没有输入可提交的卡密')
for card in card_lines:
card_id, price, card_pass = card.split(';')
if price0 is None:
price0 = price
elif price0 != price:
raise RuntimeError('价格不一致')
if card_id in card_set or master.exists(
'card:%s' % card_id) or number_check.exists(card_id):
raise RuntimeError('系统中有重复卡号：%s' % card_id)
if card_pass in pass_set:
raise RuntimeError('系统中有重复密码：%s' % card_id)
if sinopec_pattern_card.match(card_id) is None:
raise RuntimeError('卡号格式不正确(16位)：%s' % card_id)
if sinopec_pattern_pass.match(card_pass) is None:
raise RuntimeError('卡密码不正确(20位) %s' % card_id)
card_set.add(card_id)
pass_set.add(card_pass)
count += 1
########
# CREATE BOX
tsp = dt.datetime.now()
part = tsp.strftime('%m')
uid = master.incr('xid:easy')
file_name = 'easy_%s_%d.txt' % (tsp.strftime('%Y%m%d'), uid)
box = Box()
box.card_type = 'SINOPEC'
box.user_id = user_id # for multi-user
box.partition = part
box.price = price0
box.count = count
box.ready = count
box.inuse = 0
box.used = 0
box.error = 0
box.invalid = 0
box.status = 'ready'
box.filename = file_name
box.package = str(uid)
box.create_time = tsp
# box.target_user = target_user
box.source = None
box.buy_price = price0
box.sell_price = price0
box.notes = None
session.add(box)
session.commit()
master.hmset(
'box:%d' % box.id, {
'count': count,
'ready': count,
'inuse': 0,
'used': 0,
'error': 0,
'invalid': 0,
'user_id': user_id,
'price': price0,
'part': part,
'card_type': 'SINOPEC',
})
'''
INSERT FILE
'''
card_part = get_card_shard(part)
user = self.application.config.get('user').get(user_id)
aes_pass = user['aes_pass']
aes_iv = user['aes_iv']
# two parse
card_list = []
for card in card_lines:
card_id, price, card_pass = card.split(';')
card_pass = aes_encrypt(card_pass, aes_pass, aes_iv)
master.hmset(
'card:%s' % card_id, {
'price': price,
'password': card_pass,
'part': part,
'box': box.id,
'user_id': user_id,
'package': box.package,
'create_time': tsp,
'file_name': file_name,
})
card_list.append(card_id)
card = card_part()
card.user_id = user_id
card.card_id = card_id
card.box_id = box.id
card.password = card_pass
card.price = price
card.status = 'ready'
card.create_time = tsp
session.add(card)
session.commit()
number_check.update(card_set)
# 将上的卡存储到默认上卡卡池
up_pool = user_id
master.hmset('box:%d' % box.id, {'pool': up_pool})
master.sadd(
'set:card_pool:box_list:{0}:{1}'.format('SINOPEC', up_pool),
box.id)
for card_id in card_list:
master.lpush(
'list:card:{0}:{1}:{2}'.format('SINOPEC', up_pool, price0),
card_id)
# ret = {'status': 'ok', 'box_id': box.id, 'count': count, 'price': price0}
income = price0 * count
log_request.info('ADD FUND FOR %sx%s', price0, count)
for _try in range(5):
r = yield self.post_fund('deposit', '', user_id, income)
log_request.info('ADD FUND RESULT %s', r)
if r == 'ok':
break
tornado.gen.sleep(5)
self.master.delete('set:easy:card:' + user_id)
self.master.delete('list:easy:card:' + user_id)
status = 'ok'
msg = '导入成功'
except Exception as e:
log_request.exception('EASY IMPORT FAIL')
status = 'fail'
msg = str(e)
finally:
session.close()
self.finish(json.dumps({'status': status, 'msg': msg}))
def query_box(self, args):
page = int(args['page'])
size = int(args['size'])
status = args['status']
price = args['price']
package = args['package']
card_id = args.get('card_id')
card_type = args['card_type']
session = self.session('machado')
result = []
q = session.query(Box)
if status != '':
q = q.filter(Box.status == status)
if price != '':
q = q.filter(Box.price == price)
if package != '':
q = q.filter(Box.package == package)
if 'start' in args and 'end' in args and args['start'] and args['end']:
start = time.strptime(args['start'], '%Y/%m/%d %H:%M:%S')
end = time.strptime(args['end'], '%Y/%m/%d %H:%M:%S')
q = q.filter(Box.create_time >= start).filter(
Box.create_time < end)
# filter by card_id
if card_id:
box_id = -1
box_id_set = set()
for part in range(1, 13):
card_class = get_card_shard(part)
card = session.query(card_class).filter(
card_class.card_id == card_id).first()
if card:
box_id_set.add(card.box_id)
q = q.filter(Box.id.in_(box_id_set))
q = q.filter(Box.card_type == card_type)
if 'admin' not in self.current_user['roles']:
q = q.filter(Box.user_id == self.current_user['user_id'])
request_log.info('FILTER USER %s', self.current_user['user_id'])
count = q.count()
max_page = int(math.ceil(count / int(args['size'])))
q = q.order_by(desc(Box.id)).offset((page - 1) * size).limit(size)
for box in q:
if not box.update_time:
box.update_time = box.create_time
o = {
'pachage_id': box.package, # 箱号
'box_id': box.id, # 盒号
'price': box.price, # 面值
'filename': box.filename, # 文件名
'create_time': str(box.create_time), # 上卡时间
'count': box.count, # 总数
'ready': box.ready, # 可用
'inuse': box.inuse, # 在用
'used': box.used, # 已用
'error': box.error, # 错卡
'invalid': box.invalid, # 废卡
'frozen': 0, # 冻结的卡
'removed': 0, # 删除的卡
'status': box.status, # 状态
'status_time': str(box.update_time), # 状态时间
}
if box.status in ['ready', 'frozen']:
box_info = self.slave.hmget(
'box:%d' % box.id,
['count', 'ready', 'inuse', 'used', 'error'])
o['count'] = box_info[0] # count
o['ready'] = box_info[1] # ready
o['inuse'] = box_info[2] # inuse
o['used'] = box_info[3] # used
o['error'] = box_info[4] # error
result.append(o)
session.close()
self.finish(
json.dumps({
'data': result,
'max': max_page,
'page': page,
'size': size
}))
def query_card(self, args):
box_id = int(args['box'])
page = int(args['page'])
size = int(args['size'])
requ_type = args.get('requ_type', 'query')
status = None
if 'status' in args:
status = args['status']
else:
status = 'all'
session = self.session('machado')
result = []
box = session.query(Box).filter(Box.id == box_id).first()
if box is None:
return self.finish(json.dumps({'data': {}}))
card_class = get_card_shard(box.partition)
q = session.query(card_class).filter(card_class.box_id == box.id)
if status and status != 'all':
q = q.filter(card_class.status == status)
if requ_type == 'query':
count = q.count()
# print(count)
max_page = int(math.ceil(count / int(args['size'])))
q = q.order_by(card_class.card_id).offset(
(page - 1) * size).limit(size)
# 订单编号 手机号 产品名称 运营商 面值 采购金额 开始时间 状态时间 批次号 订单状态 备注
for card in q:
if not card.update_time:
card.update_time = card.create_time
o = {
'id': card.id,
'card_id': card.card_id,
'box_id': card.box_id,
'price': card.price,
# 'pass': card.password,
'status': card.status,
'site': card.site,
'create_time': str(card.create_time),
'status_time': str(card.update_time),
}
result.append(o)
session.close()
self.finish(
json.dumps({
'data': result,
'max': max_page,
'page': page,
'size': size,
'count': count,
}))
elif requ_type == 'export':
q = q.order_by(card_class.card_id)
status_map = {
'all': '全部',
'ready': '可用',
'inuse': '在用',
'error': '错卡',
}
path = 'exports/card_{0}_{1}.xlsx'.format(box.id, status)
workbook = xlsxwriter.Workbook(path)
worksheet = workbook.add_worksheet()
#写文件头
worksheet.write(0, 0, '箱号')
worksheet.write(0, 1, '盒号')
worksheet.write(0, 2, '文件名')
worksheet.write(0, 3, '面值')
worksheet.write(0, 4, '上卡时间')
worksheet.write(0, 5, '状态')
worksheet.write(0, 6, '卡号')
worksheet.write(0, 7, '新密码')
row = 1
for card_info in q:
worksheet.write(row, 0, box.package)
worksheet.write(row, 1, box.id)
worksheet.write(row, 2, box.filename)
worksheet.write(row, 3, box.price)
worksheet.write(row, 4, str(box.create_time))
worksheet.write(
row, 5, status_map.get(card_info.status, card_info.status))
worksheet.write(row, 6, card_info.card_id)
row += 1
workbook.close()
self.finish(json.dumps({'path': '/' + path}))
def move_box_list(self):
source_pool = self.argu_list['source_pool']
dest_pool = self.argu_list['dest_pool']
move_box_list = self.argu_list['move_box_list']
# 检查所有的盒号是否存在指定的set中
source_box_id_list_key = 'set:card_pool:box_list:{0}:{1}'.format(self.card_type, source_pool)
for box_id in move_box_list:
if not self.slave.sismember(source_box_id_list_key, box_id):
log_request.error('CHECK BOX {0} FAIL,NOT IN POOL {1}'.format(box_id, source_pool))
return self.resp_result('fail', '盒号{0}检查失败!!!'.format(box_id))
# 移卡
engine = self.application.engine['machado']
session = sessionmaker(bind=engine)()
move_box_info_list = {}
try:
dest_box_id_list_key = 'set:card_pool:box_list:{0}:{1}'.format(self.card_type, dest_pool)
for box_id in move_box_list:
box = self.slave.hgetall('box:{0}'.format(box_id))
move_box_info_list[box_id] = box
# 移动每张卡
card_part = get_card_shard(box['part'])
query = session.query(card_part).filter(card_part.box_id == box_id, card_part.status == 'ready').all()
source_card_list_key = 'list:card:{0}:{1}:{2}'.format(box['card_type'], source_pool, box['price'])
dest_card_list_key = 'list:card:{0}:{1}:{2}'.format(box['card_type'], dest_pool, box['price'])
for card in query:
result = self.master.lrem(source_card_list_key, 0, card.card_id)
if not result:
log_request.error(
'move box card error lrem card_id:{0} result:{1}'.format(card.card_id, result))
else:
result = self.master.lpush(dest_card_list_key, card.card_id)
log_request.debug('move box card lrem card_id:{0} result:{1}'.format(card.card_id, result))
yield tornado.gen.moment
# 移动单盒卡
self.master.hset('box:{0}'.format(box_id), 'pool', dest_pool)
if self.master.srem(source_box_id_list_key, box_id) == 1:
log_request.info('REMOVE BOX {0} FROM {1}'.format(box_id, source_pool))
else:
log_request.warn('REMOVE BOX {0} FROM {1} ERROR!!!!!!!!!!!!'.format(box_id, source_pool))
if self.master.sadd(dest_box_id_list_key, box_id) == 1:
log_request.info('ADD BOX {0} TO {1}'.format(box_id, dest_pool))
else:
log_request.warn('ADD BOX {0} TO {1} ERROR!!!!!!!!!!!!'.format(box_id, dest_pool))
except:
log_request.exception("MOVE CARD_POOL {0} BOXES TO {1} EXCEPTION".format(source_pool, dest_pool))
# 记录操作历史
value_info = self.value_info(move_box_info_list)
log_request.info('CARD_POOL HISTORY {0}'.format(value_info))
source_history_list_key = 'list:history:{0}:{1}'.format(self.card_type, source_pool)
self.master.lpush(source_history_list_key,
"{0} 移出到 >>{1}<< {2} ".format(datetime.now().strftime("%Y-%m-%d %H:%M:%S"), dest_pool,
value_info))
dest_history_list_key = 'list:history:{0}:{1}'.format(self.card_type, dest_pool)
self.master.lpush(dest_history_list_key,
"{0} 接收自 >>{1}<< {2}".format(datetime.now().strftime("%Y-%m-%d %H:%M:%S"), source_pool,
value_info))
return self.resp_result('ok', '成功')
def freeze_by_box_id(self, box_id):
#redis中查找盒号信息并获取卡所在表
box_key = 'box:{0}'.format(box_id)
redis_box_info = self.master.hgetall(box_key)
if not redis_box_info:
return self.resp_json_result('fail', '找不到该盒卡的信息')
card_part = get_card_shard(redis_box_info['part'])
#1.修改数据库中的盒状态
#2.修改数据库中的卡状态
#3.删除redis中的盒数据和卡数据
db_session = self.session('machado')
try:
#查询数据库数据
db_box_info = db_session.query(Box).filter(
Box.id == box_id).one_or_none()
if db_box_info == None:
return self.resp_json_result('fail', '无法查询到本盒卡的数据')
#判断用户ID是否匹配
if db_box_info.user_id != self.user_id and 'admin' not in self.current_user[
'roles']:
return self.write_error(403)
#判断本盒状态是否合法
if db_box_info.status != 'ready':
return self.resp_json_result('fail', '非法的修改状态')
#记录操作日志
operation_log = OperationLog()
operation_log.operator_id = self.current_user['id']
operation_log.operation = 'freeze'
operation_log.object_id = box_id
operation_log.object_type = 'box'
operation_log.create_date = datetime.now()
db_session.add(operation_log)
#修改每张卡的状态
db_session.query(card_part).filter(
and_(card_part.box_id == box_id,
card_part.status == 'ready')).update({
'status':
'frozen',
'update_time':
datetime.now()
})
#修改盒状态
db_box_info.update_time = datetime.now()
db_box_info.status = 'frozen'
db_session.add(db_box_info)
db_session.commit()
#redis修改 移出可用表里面的卡号
freeze_count = 0
card_list_key = 'list:card:{0}:{1}:{2}'.format(
redis_box_info['card_type'], redis_box_info['pool'],
redis_box_info['price'])
ready_card_list = db_session.query(card_part.card_id).filter(
and_(card_part.box_id == box_id,
card_part.status == 'frozen')).all()
for card in ready_card_list:
result = self.master.lrem(card_list_key, 0, card.card_id)
if result == 0:
log.warning(
'freeze_by_box_id LREM ERROR BOX {0} CARD_ID {1} !!!'.
format(box_id, card.card_id))
else:
log.info(
'freeze_by_box_id LREM SUCCESS BOX {0} CARD_ID {1}'.
format(box_id, card.card_id))
freeze_count += 1
if freeze_count != int(redis_box_info['ready']):
log.warning(
'freeze_by_box_id BOX {0} READY_COUNT {1} != FREEZE_COUNT {2} !!!'
.format(box_id, redis_box_info['ready'], freeze_count))
self.master.hset(box_key, 'status', 'frozen')
return self.resp_json_result('ok', '冻结成功')
except:
log.exception('freeze_by_box_id {0} EXCEPTION!!!'.format(box_id))
return self.resp_json_result('fail', '冻结异常')
finally:
db_session.close()
def remove_by_box_id(self, box_id):
#redis中查找盒号信息并获取卡所在表
box_key = 'box:{0}'.format(box_id)
redis_box_info = self.master.hgetall(box_key)
if not redis_box_info:
return self.resp_json_result('fail', '找不到该盒卡的信息')
card_part = get_card_shard(redis_box_info['part'])
#1.修改数据库中的盒状态
#2.修改数据库中的卡状态
#3.将卡数据添加到redis中
db_session = self.session('machado')
try:
#查询数据库数据
db_box_info = db_session.query(Box).filter(
Box.id == box_id).one_or_none()
if db_box_info == None:
return self.resp_json_result('fail', '无法查询到本盒卡的数据')
#判断用户ID是否匹配
if db_box_info.user_id != self.user_id and 'admin' not in self.current_user[
'roles']:
return self.write_error(403)
#判断本盒状态是否合法
if db_box_info.status != 'frozen':
return self.resp_json_result('fail', '非法的修改状态')
#判断卡的数量是否符合规则
if redis_box_info['count'] != redis_box_info['ready']:
return self.resp_json_result('fail', '暂时不支持删除被使用过的卡盒')
#记录操作日志
operation_log = OperationLog()
operation_log.operator_id = self.current_user['id']
operation_log.operation = 'remove'
operation_log.object_id = box_id
operation_log.object_type = 'box'
operation_log.create_date = datetime.now()
db_session.add(operation_log)
#修改每张卡的状态
db_session.query(card_part).filter(
and_(card_part.box_id == box_id,
card_part.status == 'frozen')).update({
'status':
'removed',
'update_time':
datetime.now()
})
#修改盒状态
db_box_info.update_time = datetime.now()
db_box_info.status = 'removed'
db_box_info.count = redis_box_info['count']
db_box_info.ready = redis_box_info['ready']
db_box_info.inuse = redis_box_info['inuse']
db_box_info.used = redis_box_info['used']
db_box_info.error = redis_box_info['error']
db_session.add(db_box_info)
db_session.commit()
#删除redis中存储的每张卡的数据
remove_count = 0
ready_card_list = db_session.query(card_part).filter(
and_(card_part.box_id == box_id,
card_part.status == 'removed')).all()
for card in ready_card_list:
if self.master.delete('card:{0}'.format(card.card_id)) == 1:
remove_count += 1
log.info(
'remove_by_box_id DELETE CARD SUCCESS BOX {0} CARD_ID {1}'
.format(box_id, card.card_id))
else:
log.warning(
'remove_by_box_id DELETE CARD ERROR BOX {0} CARD_ID {1} !!!'
.format(box_id, card.card_id))
#删除重复卡号加载器中的数据
self.application.number_check.remove(card.card_id)
#删除redis中存储的卡盒数据
if self.master.delete('box:{0}'.format(box_id)) != 1:
log.warning(
'remove_by_box_id DELETE BOX ERROR BOX {0}!!!'.format(
box_id))
if remove_count != int(redis_box_info['ready']):
log.warning(
'remove_by_box_id BOX {0} READY_COUNT {1} != REMOVE_COUNT {2} !!!'
.format(box_id, redis_box_info['ready'], remove_count))
return self.resp_json_result('ok', '删除成功')
except:
log.exception('remove_by_box_id {0} EXCEPTION!!!'.format(box_id))
return self.resp_json_result(
'fail', '删除本盒卡出现异常，请联系管理员， ID={0}'.format(box_id))
finally:
db_session.close()
def post(self):
if 'card_query' not in self.current_user['roles']:
return self.send_error(403)
err_msg = []
resp_result = {'status': 'fail', 'msg': err_msg}
self.user_id = self.current_user['user_id']
self.card_type = self.get_argument('card_type')
box_id = self.get_argument('box_id')
pattern_card = None
pattern_pass = None
if self.card_type == 'CMCC_FEE':
pattern_card = self.cmccfee_pattern_card
pattern_pass = self.cmccfee_pattern_pass
elif self.card_type == 'SINOPEC':
pattern_card = self.sinopec_pattern_card
pattern_pass = self.sinopec_pattern_pass
else:
return self.send_error(403)
file = self.request.files['correction_cards'][0]
file_name = file['filename']
card_id_set = set()
card_pass_set = set()
card_list = {}
#检测盒号信息
redis_box_info = self.master.hgetall('box:{0}'.format(box_id))
if not redis_box_info:
err_msg.append('找不到该盒卡的数据')
return self.finish(json.dumps(resp_result))
try:
self.db_session = self.session('machado')
query = self.db_session.query(Box).filter(Box.id == box_id)
if 'admin' not in self.current_user['role']:
query = query.filter(Box.user_id == self.user_id)
db_box_info = query.one_or_none()
if not db_box_info:
err_msg.append('找不到盒号 {0} 的信息'.format(box_id))
return self.finish(json.dumps(resp_result))
finally:
self.db_session.close()
#检测卡号和卡密
try:
with xlrd.open_workbook(file_contents=file['body']) as book:
sheet1 = book.sheet_by_index(0)
for i in range(1, sheet1.nrows):
line_num = i + 1
card_id = sheet1.cell(i, 6).value
card_pass = sheet1.cell(i, 7).value
if isinstance(card_id, float):
card_id = '%.0f' % card_id
if isinstance(card_pass, float):
card_pass = '******' % card_pass
if card_id in card_id_set:
card_id_set.add(card_id)
err_msg.append('第{0}行存在重复卡号 {1}'.format(
line_num, card_id))
if pattern_card.match(card_id) is None:
err_msg.append('第{0}行卡号{1}格式不正确'.format(
line_num, card_pass))
if card_pass in card_pass_set:
card_pass_set.add(card_pass_set)
err_msg.append('第{0}行存在重复卡密 {1}'.format(
line_num, card_pass))
if pattern_pass.match(card_pass) is None:
err_msg.append('第{0}行卡密{1}格式不正确'.format(
line_num, card_pass))
card_list[card_id] = card_pass
except Exception:
log.exception('CorrectionBoxFileHandler read file exception')
err_msg.append('数据异常')
return self.finish(json.dumps(resp_result))
if len(err_msg):
return self.finish(json.dumps(resp_result))
#根据盒号信息检测卡号信息是否存在
db_err_card_list = {}
card_class = get_card_shard(db_box_info.partition)
try:
self.db_session = self.session('machado')
q = self.db_session.query(card_class).filter(
card_class.box_id == db_box_info.id,
card_class.status == 'error')
for card_info in q:
db_err_card_list[card_info.card_id] = None
finally:
self.db_session.close()
#判断错卡状态是否正确
for card_id in card_list:
if card_id not in db_err_card_list:
err_msg.append('卡号 {0} 状态非法,或者不属于本盒卡'.format(card_id))
else:
db_err_card_list[card_id] = card_list[card_id]
#更新错卡卡密
user_id = db_box_info.user_id
user = self.application.config.get('user').get(user_id)
aes_pass = user['aes_pass']
aes_iv = user['aes_iv']
try:
self.db_session = self.session('machado')
for card_id in db_err_card_list:
card_pass = db_err_card_list[card_id]
if not card_pass:
continue
card_pass = aes_encrypt(card_pass, aes_pass, aes_iv)
#更新卡的状态
card = self.db_session.query(card_class).filter(
card_class.box_id == db_box_info.id,
card_class.card_id == card_id).one()
card.password = card_pass
card.status = db_box_info.status
self.db_session.add(card)
self.db_session.commit()
self.master.hincrby('box:%s' % box_id, 'error', -1)
self.master.hincrby('box:%s' % box_id, 'ready', 1)
log.info(
'USER[{0}] MODIFE CARD_PASS BOX[{1}] CARD[{2}] OLD_CARD_PASS[3] >> NEW_CARD_PASS[{4}]'
.format(self.current_user['id'], box_id, card_id,
self.master.hget('card:%s' % card_id, 'password'),
card_pass))
self.master.hmset('card:%s' % card_id, {'password': card_pass})
if card.status == 'ready':
card_list_key = 'list:card:{0}:{1}:{2}'.format(
redis_box_info['card_type'],
redis_box_info['pool'],
redis_box_info['price'],
)
self.master.rpush(card_list_key, card.card_id)
finally:
self.db_session.close()
resp_result['status'] = 'ok'
return self.finish(json.dumps(resp_result))
def commit_file(self, file_info, session):
# checking only
card_set = set()
pass_set = set()
price = int(file_info['price'])
file_name = file_info['filename']
file_path = os.path.join(file_info['path'], file_name)
package_no = file_info['package_no']
# target_user = file_info['target_user']
user_id = file_info['user_id']
source_id = file_info['source_id']
buy_price = file_info['buy_price']
sell_price = file_info['sell_price']
notes = file_info['notes']
card_type = file_info['card_type']
master = self.master
status = 'ok'
msg = ''
number_check = self.application.number_check
if not number_check.is_loaded():
status = 'fail'
msg = '重复卡号检查器未加载完毕，请稍后再试。'
return status, msg
try:
tsp = dt.datetime.now()
part = tsp.strftime('%m')
with xlrd.open_workbook(file_path) as book:
sheet1 = book.sheet_by_index(0)
# FAST CHECK
count = 0
for n in range(sheet1.nrows):
count += 1
# read a cell
card_id = str(sheet1.cell(
n, self.CARD_ID_COL_NUM).value).strip()
card_pass = str(
sheet1.cell(n, self.CARD_PASS_COL_NUM).value).strip()
p = sheet1.cell(n, self.CARD_PRICE_NUM).value
if isinstance(p, str) and '元' in p:
p = p[:-1]
price0 = int(p)
if price != price0:
status = 'fail'
msg = '价格不一致：%s' % card_id
break
if master.exists('card:%s' %
card_id) or number_check.exists(card_id):
status = 'fail'
msg = '系统中有重复卡号：%s' % card_id
break
if card_id in card_set:
status = 'fail'
msg = '文件中有重复卡号：%s' % card_id
break
if card_type == 'SINOPEC':
if sinopec_pattern_card.match(card_id) is None:
status = 'fail'
msg = '卡号格式不正确(16位)：%s' % card_id
break
if sinopec_pattern_pass.match(card_pass) is None:
status = 'fail'
msg = '卡密码不正确(20位) %s' % card_id
break
elif card_type == 'CMCC_FEE':
if pattern_card.match(card_id) is None:
status = 'fail'
msg = '卡号格式不正确(17位，6,7位为10,16,19,20)：%s' % card_id
break
if pattern_pass.match(card_pass) is None:
status = 'fail'
msg = '卡密码不正确(19位) %s' % card_id
break
if card_pass in pass_set:
status = 'fail'
msg = '第%d行：文件中卡密码重复 %s' % (count, card_id)
break
card_set.add(card_id)
pass_set.add(card_pass)
# SLOW CHECK
# INPUT
if status == 'fail':
return status, msg
'''
CREATE BOX
'''
box = Box()
box.card_type = card_type
box.user_id = user_id # for multi-user
box.partition = part
box.price = price
box.count = count
box.ready = count
box.inuse = 0
box.used = 0
box.error = 0
box.invalid = 0
box.status = 'ready'
box.filename = file_name
box.package = package_no
box.create_time = tsp
# box.target_user = target_user
box.source = source_id
box.buy_price = buy_price
box.sell_price = sell_price
box.notes = notes
session.add(box)
session.commit()
master.hmset(
'box:%d' % box.id, {
'count': count,
'ready': count,
'inuse': 0,
'used': 0,
'error': 0,
'invalid': 0,
'user_id': user_id,
'price': price,
'part': part,
'card_type': card_type,
})
master.lpush(
'list:pool:type:{pool_id}:{price}'.format(pool_id=user_id,
price=price),
box.id)
'''
INSERT FILE
'''
card_part = get_card_shard(part)
user = self.application.config.get('user').get(user_id)
aes_pass = user['aes_pass']
aes_iv = user['aes_iv']
# two parse
card_list = []
for n in range(sheet1.nrows):
# read a cell
card_id = str(sheet1.cell(
n, self.CARD_ID_COL_NUM).value).strip()
card_pass = str(
sheet1.cell(n, self.CARD_PASS_COL_NUM).value).strip()
if isinstance(card_id, float):
card_id = '%.0f' % card_id
if isinstance(card_pass, float):
card_pass = '******' % card_pass
card_pass = aes_encrypt(card_pass, aes_pass, aes_iv)
master.hmset(
'card:%s' % card_id, {
'price': price,
'password': card_pass,
'part': part,
'box': box.id,
'user_id': user_id,
'package': box.package,
'create_time': tsp,
'file_name': file_name,
})
master.lpush(
'list:ready:box:{box_id}'.format(box_id=box.id),
card_id)
card_list.append(card_id)
card = card_part()
card.user_id = user_id
card.card_id = card_id
card.box_id = box.id
card.password = card_pass
card.price = price
card.status = 'ready'
card.create_time = tsp
session.add(card)
session.commit()
number_check.update(card_set)
# 存储到指定卡池之中
up_pool = file_info['card_pool']
master.hmset('box:%d' % box.id, {'pool': up_pool})
master.sadd(
'set:card_pool:box_list:{0}:{1}'.format(
card_type, up_pool), box.id)
for card_id in card_list:
master.lpush(
'list:card:{0}:{1}:{2}'.format(card_type, up_pool,
price), card_id)
ret = {
'status': 'ok',
'box_id': box.id,
'count': count,
'price': price
}
# when file closed
if status == 'ok':
self.encrypt_file_by_user(file_path)
except Exception as e:
log_request.exception('ERROR')
status = 'fail'
msg = repr(e)
return status, msg
def update_card_status(self, status, card_list):
if len(card_list) <= 0:
return
request_log.info("UPDATE_CARD_STATUS {0} {1}".format(
status, card_list))
session = None
try:
# 更新redis中的卡状态
for card in card_list:
if status == 'used':
card_id = card['id']
else:
card_id = card
request_log.info('CACHE CARD {0} {1}'.format(status, card_id))
self.master.hset('card:{0}'.format(card_id), 'state', status)
box_id = self.master.hget('card:{0}'.format(card_id), 'box')
self.master.hincrby('box:{0}'.format(box_id), status, 1)
if status == 'inuse':
self.master.hincrby('box:{0}'.format(box_id), 'ready', -1)
elif status in ['used', 'error']:
self.master.hincrby('box:{0}'.format(box_id), 'inuse', -1)
# 更新数据库中卡的状态
engine = self.application.engine['machado']
session = sessionmaker(bind=engine)()
for card_obj in card_list:
if status == 'used':
card_id = card_obj['id']
else:
card_id = card_obj
card_info = self.master.hmget('card:{0}'.format(card_id),
['part', 'site', 'state', 'box'])
part = card_info[0]
box_id = int(card_info[3])
if part is None:
continue
card_part = get_card_shard(part)
card = session.query(card_part).filter(
card_part.card_id == card_id).filter(
card_part.box_id == box_id).first()
if card is None:
continue
card.site = card_info[1]
card.status = card_info[2]
card.update_time = dt.now()
if status == 'used':
card.order_id = card_obj.get('order_id')
card.target_user = card_obj.get('user_id')
session.add(card)
session.commit()
# 删掉用过的卡
if status == 'used':
for card_id in card_list:
request_log.info('CLEAN CACHE {0}'.format(card_id))
self.master.delete('card:{0}'.format(card_id))
except Exception as e:
request_log.exception('UPDATE_CARD_STATUS EXCEPTION')
finally:
if session: session.close()
def post(self, product):
if product not in PRODUCT_LIST:
return self.finish()
args = self.json_args
page = int(args['page'])
size = int(args['size'])
if 'admin' in self.current_user['roles'] and 'user_id' in args:
user_id = args['user_id']
else:
user_id = self.current_user['partner_id']
session = self.session('madeira')
result = []
if 'master' in self.application.config['downstream'][user_id]:
master_id = self.application.config['downstream'][user_id][
'master']
else:
master_id = user_id
card_cls = get_card_shard(1)
q = session.query(card_cls).filter(card_cls.status == 'error')
count = q.count()
max_page = int(math.ceil(count / int(args['size'])))
q = q.order_by(desc(card_cls.req_time)) \
.offset((page - 1) * size) \
.limit(size)
# 订单编号 手机号 产品名称 运营商 面值 采购金额 开始时间 状态时间 批次号 订单状态 备注
for order in q:
carrier = ''
carrier_name = ''
area = ''
if order.area and ':' in order.area:
carrier, area = order.area.split(':')
o = {
'id':
order.order_id,
'sp_id':
order.sp_order_id,
'phone':
order.mobile,
'price':
'%d' % int(order.price),
'value':
(order.value is not None and '%.3f' % (order.value / 10000))
or '-',
'create':
str(order.req_time),
'update':
order.back_time and str(order.back_time),
'result':
self.decode_status(order),
'balance': (order.balance is not None and '%0.03f' %
(order.balance / 10000)) or '-',
}
result.append(o)
session.close()
self.write(
json.dumps({
'data': result,
'max': max_page,
'page': page,
'size': size
}))
