def export_task(request):
redis = request.redis
partner_id = request.current_user['partner_id']
args = request.json_args
print(args)
page = int(args['page'])
size = int(args['size'])
user_id = args['user_id']
partner_id = request.current_user['partner_id']
session = request.session()
result = []
order_cls = get_order_shard(user_id)
q = session.query(order_cls).filter(order_cls.partner_id == partner_id)
# filter
if args['number']:
q = q.filter(order_cls.number == args['number'])
if 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(order_cls.create_time >= start) \
.filter(order_cls.create_time <= end)
if args['status']:
q = q.filter(order_cls.status == args['status'])
if args['batch']:
q = q.filter(order_cls.batch_id == args['batch'])
q = q.order_by(desc(order_cls.order_id)).limit(1000)
filename = ''.join(
random.sample(string.ascii_uppercase + string.digits * 6, 6))
path = 'exports/%s.xlsx' % filename
workbook = xlsxwriter.Workbook(path)
worksheet = workbook.add_worksheet()
# 0 1 2 3 4 5 6
# 订单编号 手机号 产品名称 运营商 面值 采购金额 开始时间 状态时间 批次号 订单状态 备注
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, '状态时间')
worksheet.write(0, 8, '批次号')
worksheet.write(0, 9, '订单状态')
worksheet.write(0, 10, '备注')
worksheet.set_column(0, 0, 25)
worksheet.set_column('B:J', 10)
date_format = workbook.add_format({'num_format': 'yyyy/mm/dd hh:mm:ss'})
row = 1
for order in q:
k = 'product/%s/%s' % (partner_id, order.offer_id)
offer_name = redis.hget(k, 'name') or order.offer_id
worksheet.write(row, 0, order.order_id)
worksheet.write(row, 1, order.number)
worksheet.write(row, 2, offer_name)
worksheet.write(row, 3, '')
worksheet.write(row, 4, '%.2f' % (order.face_value / 1000))
worksheet.write(row, 5, '%.2f' % (order.price / 1000))
worksheet.write(row, 6, order.create_time, date_format)
worksheet.write(row, 7, order.update_time, date_format)
worksheet.write(row, 8, order.batch_id)
worksheet.write(row, 9, utils.escape_status(order.status))
worksheet.write(row, 10, order.notes)
# worksheet.write,
row += 1
workbook.close()
return path
def post_query(self):
try:
user_id = self.current_user['partner_id']
batch_id = self.json_args['batch']
page = int(self.json_args['page'])
size = int(self.json_args['size'])
status = self.json_args['status']
offset = (page - 1) * size
session = self.session('purus')
session_made = self.session('madeira')
batch_info = session.query(BatchInfo).filter(
BatchInfo.batch_id == batch_id).filter(
BatchInfo.user_id == user_id).first()
if batch_info is None:
raise RuntimeError('')
q = session.query(BatchDetail).filter(
BatchDetail.batch_id == batch_id)
if status and status != '':
q = q.filter(BatchDetail.status == status)
count = q.count()
max_page = int(math.ceil(count / size))
result = []
if count > 0:
details = q.order_by(BatchDetail.id).offset(offset).limit(size)
for detail in details.all():
if detail.status == 'call':
order_cls = get_order_shard(user_id)
order_id = detail.order_id.split(',')[-1].strip()
order = session_made.query(order_cls).filter(
order_cls.sp_order_id == order_id).first()
if order and order.back_result:
if order.back_result == '00000':
detail.status = 'success'
session.add(detail)
elif order.back_result in ['10010', '10058']:
detail.status = 'fail'
session.add(detail)
result.append({
'mobile': detail.mobile,
'price': detail.price,
'status': detail.status,
'order_id': detail.order_id,
})
session.commit()
self.finish(
json.dumps({
'page': page,
'max': max_page,
'data': result
}))
except Exception as e:
self.finish(json.dumps({'data': []}))
finally:
session.close()
session_made.close()
def post(self, product):
#### SAFTY ######
safety = self.application.config.get('safety')
if safety is None:
request_log.error('CONFIG FAIL (NO SAFETY)')
return self.send_error(500)
# verify ip in white list
if self.direct is None and self.request.remote_ip not in safety[
'white_list']:
request_log.error("CONFIG FAIL ('%s'NOT IN WHITELIST)",
self.request.remote_ip)
return self.send_error(500)
#### SAFTY ######
if product not in PRODUCT_LIST:
return self.finish()
product_cache = dict()
args = self.json_args
page = int(args['page'])
size = int(args['size'])
user_id = args['user_id']
### RELOAD ###
if user_id not in self.application.config['downstream']:
cfg = yaml.load(open('downstream.yaml', 'r', encoding='utf8'))
self.application.config['downstream'] = cfg['downstream']
if user_id not in self.application.config['downstream']:
return self.send_error(500)
session = self.session('madeira')
try:
result = []
if 'master' in self.application.config['downstream'][user_id]:
master_id = self.application.config['downstream'][user_id][
'master']
else:
master_id = user_id
if 'shard_id' in self.application.config['downstream'][user_id]:
shard_id = self.application.config['downstream'][user_id][
'shard_id']
else:
shard_id = master_id
order_cls = get_order_shard(shard_id)
q = session.query(order_cls).filter(
order_cls.product == product).filter(
order_cls.user_id == user_id)
f = False
# filter
if 'number' in args and args['number']:
q = q.filter(order_cls.mobile == args['number'])
f = True
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(order_cls.req_time >= start).filter(
order_cls.req_time < end)
f = True
if 'result' in args and args['result']:
if product == 'fee' or product == 'sinopec':
if args['result'] == '-1':
q = q.filter(
and_(order_cls.result == '0',
order_cls.back_result == None))
else:
q = q.filter(
or_(order_cls.back_result == args['result'],
order_cls.result == args['result']))
elif product == 'data':
if args['result'] == 'finish':
q = q.filter(
or_(order_cls.back_result == '00000',
order_cls.back_result == '1'))
elif args['result'] == 'processing':
q = q.filter(
and_(order_cls.result == '00000',
order_cls.back_result == None))
elif args['result'] == 'fail':
q = q.filter(
or_(
and_(order_cls.back_result == None,
order_cls.result != '00000'),
and_(order_cls.back_result != None,
order_cls.back_result != '00000',
order_cls.back_result != '1')))
f = True
if 'id' in args and args['id']:
q = q.filter(order_cls.order_id == args['id'])
f = True
if 'sp_id' in args and args['sp_id']:
q = q.filter(order_cls.sp_order_id == args['sp_id'])
f = True
if 'carrier' in args and args['carrier']:
if 'area' in args and args['area']:
q = q.filter(order_cls.area == '%s:%s' %
(args['carrier'], args['area']))
else:
q = q.filter(order_cls.area.like(args['carrier'] + ':%'))
f = True
else:
if 'area' in args and args['area']:
q = q.filter(order_cls.area.like('%:' + args['area']))
f = True
if not f and not (page == 1 and size <= 10):
return self.write(
json.dumps({
'status': 'fail',
'msg': '您未选择任何过滤条件，请至少输入一个'
}))
count = q.count()
# print(count)
max_page = int(math.ceil(count / int(args['size'])))
q = q.order_by(desc(order_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(':')
carrier_name = escape_carrier(carrier)
area_name = escape_area(area)
o = {
'id':
order.order_id,
'sp_id':
order.sp_order_id,
'phone':
order.mobile,
'price':
str(order.price or '-'),
'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':
decode_status(order),
'name':
decode_name(self.slave, order, carrier, carrier_name, area,
area_name, master_id, product_cache),
'carrier':
area_name + carrier_name,
'balance': (order.balance is not None and '%0.03f' %
(order.balance / 10000)) or '-',
}
result.append(o)
finally:
session.close()
self.write(
json.dumps({
'data': result,
'max': max_page,
'page': page,
'size': size
}))
def post(self, product):
#### SAFTY ######
safety = self.application.config.get('safety')
if safety is None:
request_log.error('CONFIG FAIL (NO SAFETY)')
return self.send_error(500)
# verify ip in white list
if self.request.remote_ip not in safety['white_list']:
request_log.error("CONFIG FAIL ('%s' NOT IN WHITELIST)",
self.request.remote_ip)
return self.send_error(500)
#### SAFTY ######
if product not in PRODUCT_LIST:
return self.finish()
product_cache = dict()
args = self.json_args
user_id = args['user_id']
if 'master' in self.application.config['downstream'][user_id]:
master_id = self.application.config['downstream'][user_id][
'master']
else:
master_id = user_id
if 'shard_id' in self.application.config['downstream'][user_id]:
shard_id = self.application.config['downstream'][user_id][
'shard_id']
else:
shard_id = master_id
session = self.session('madeira')
order_cls = get_order_shard(shard_id)
q = session.query(order_cls).filter(
order_cls.product == product).filter(order_cls.user_id == user_id)
f = False
# filter
if 'number' in args and args['number']:
q = q.filter(order_cls.mobile == args['number'])
f = True
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(order_cls.req_time >= start).filter(
order_cls.req_time < end)
f = True
if 'result' in args and args['result']:
if product == 'fee':
if args['result'] == '-1':
q = q.filter(
and_(order_cls.result == '0',
order_cls.back_result == None))
else:
q = q.filter(
or_(order_cls.back_result == args['result'],
order_cls.result == args['result']))
elif product == 'data':
if args['result'] == 'finish':
q = q.filter(
or_(order_cls.back_result == '00000',
order_cls.back_result == '1'))
elif args['result'] == 'processing':
q = q.filter(
and_(order_cls.result == '00000',
order_cls.back_result == None))
elif args['result'] == 'fail':
q = q.filter(
or_(
and_(order_cls.back_result == None,
order_cls.result != '00000'),
and_(order_cls.back_result != None,
order_cls.back_result != '00000',
order_cls.back_result != '1')))
f = True
if 'id' in args and args['id']:
q = q.filter(order_cls.order_id == args['id'])
f = True
if 'sp_id' in args and args['sp_id']:
q = q.filter(order_cls.sp_order_id == args['sp_id'])
f = True
if not f:
return self.write(
json.dumps({
'status': 'fail',
'msg': '您未选择任何过滤条件，请至少输入一个'
}))
q = q.order_by(desc(order_cls.req_time)).limit(100000)
path = 'exports/export_%s.xlsx' % user_id
workbook = xlsxwriter.Workbook(path, {'constant_memory': True})
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, '开始时间')
worksheet.write(0, 8, '订单状态')
worksheet.write(0, 9, '状态时间')
worksheet.write(0, 10, '余额')
row = 1
# 订单编号 手机号 产品名称 运营商 面值 采购金额 开始时间 状态时间 批次号 订单状态 备注
for order in q:
carrier = ''
carrier_name = ''
area = ''
if order.area and ':' in order.area:
carrier, area = order.area.split(':')
carrier_name = escape_carrier(carrier)
area_name = escape_area(area)
worksheet.write(row, 0, order.order_id)
worksheet.write(row, 1, order.sp_order_id)
worksheet.write(row, 2, order.mobile)
worksheet.write(
row, 3,
decode_name(self.slave, order, carrier, carrier_name, area,
area_name, master_id, product_cache))
worksheet.write(row, 4, area_name + carrier_name)
worksheet.write(row, 5, int(order.price))
worksheet.write(row, 6,
(order.value is not None and (order.value / 10000))
or '-')
worksheet.write(row, 7, str(order.req_time))
worksheet.write(row, 8, decode_status(order))
worksheet.write(row, 9, order.back_time and str(order.back_time)
or '')
worksheet.write(row, 10, (order.balance is not None and
(order.balance / 10000)) or '-')
row += 1
if row % 1000 == 0:
yield gen.moment
workbook.close()
session.close()
self.write(json.dumps({'status': 'ok', 'path': path}))
def prepare_query(self):
if 'master' in self.application.config['downstream'][self.user_id]:
master_id = self.application.config['downstream'][
self.user_id]['master']
else:
master_id = self.user_id
if 'shard_id' in self.application.config['downstream'][self.user_id]:
shard_id = self.application.config['downstream'][
self.user_id]['shard_id']
else:
shard_id = master_id
self.db_session = self.session('madeira')
self.order_cls = get_order_shard(shard_id)
#判断是否需要通过任务ID过滤
self.db_query = self.db_session.query(
self.order_cls.user_id,
self.order_cls.order_id,
self.order_cls.mobile,
self.order_cls.price,
self.order_cls.req_time,
self.order_cls.back_time,
self.order_cls.back_result,
self.order_cls.result,
SinopecForrestalOrder.card_id,
SinopecForrestalOrder.account_price,
SinopecForrestalOrder.bot_account,
SinopecForrestalOrder.site_msg,
SinopecForrestalOrder.status,
).outerjoin(
(SinopecForrestalOrder,
SinopecForrestalOrder.order_id == self.order_cls.order_id))
#根据任务ID查询
if 'task_id' in self.argu_list and self.argu_list['task_id']:
self.db_query = self.db_query.filter(
and_(FuelCardOrder.task_id == self.argu_list['task_id'],
self.order_cls.sp_order_id == FuelCardOrder.order_id))
#通过用户ID过滤
if 'admin' not in self.current_user['roles']:
self.argu_list['user_id'] = self.user_id
if 'user_id' in self.argu_list and self.argu_list['user_id']:
self.db_query = self.db_query.filter(
self.order_cls.user_id == self.argu_list['user_id'])
#通过订单编号过滤
if 'order_id' in self.argu_list and self.argu_list['order_id']:
self.db_query = self.db_query.filter(
self.order_cls.order_id == self.argu_list['order_id'])
#通过加油卡账号过滤
if 'account' in self.argu_list and self.argu_list['account']:
self.db_query = self.db_query.filter(
self.order_cls.mobile == self.argu_list['account'])
#通过充值卡卡号过滤
if 'card_id' in self.argu_list and self.argu_list['card_id']:
self.db_query = self.db_query.filter(
SinopecForrestalOrder.card_id == self.argu_list['card_id'])
#通过面值过滤
if 'price' in self.argu_list and self.argu_list['price']:
self.db_query = self.db_query.filter(
self.order_cls.price == self.argu_list['price'])
#通过订单状态过滤
if 'result' in self.argu_list and self.argu_list['result']:
order_type = self.argu_list['result']
if self.argu_list['result'] == '1': #读取成功的订单
self.db_query = self.db_query.filter(
self.order_cls.back_result == '1')
elif self.argu_list['result'] == '0': #读取充值中的订单
self.db_query = self.db_query.filter(
and_(self.order_cls.result == '0',
self.order_cls.back_result == None))
elif self.argu_list['result'] == '9': #读失败的订单
self.db_query = self.db_query.filter(
or_(self.order_cls.result != '0',
self.order_cls.back_result == '9'))
elif self.argu_list['result'] == '-1': #卡在forrestal的订单
self.db_query = self.db_query.filter(
and_(self.order_cls.result == '0',
self.order_cls.back_result == None))
self.db_query = self.db_query.filter(
SinopecForrestalOrder.status == 'unknown')
#通过时间过滤
if 'start' in self.argu_list and 'end' in self.argu_list and self.argu_list[
'start'] and self.argu_list['end']:
start = time.strptime(self.argu_list['start'], '%Y/%m/%d %H:%M:%S')
end = time.strptime(self.argu_list['end'], '%Y/%m/%d %H:%M:%S')
self.db_query = self.db_query.filter(
self.order_cls.req_time >= start).filter(
self.order_cls.req_time < end)