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 }))