def export_deposit_list_csv(cls, order_list, order_detail_dict, all_channels):
"""
导出充值订单列表
:param order_list:
:param order_detail_dict:
:param all_channels:
:return:
"""
# 用户id、系统订单号、商户订单号、创建时间、完成时间、商户、通道商户号、通道、发起金额、实际支付金额、优惠金额、手续费、
# 成本金额、收入金额、订单状态、充值类型
data = list()
for order in order_list:
channel = all_channels[order.channel_id]
order_detail = order_detail_dict[order.order_id]
data.append({
"用户ID": order.uid,
"系统订单号": order.sys_tx_id,
"商户订单号": order.mch_tx_id,
"创建时间": order.str_create_time,
"完成时间": order_detail.str_done_time,
"商户": order.merchant.name,
"通道商户号": channel.channel_enum.conf['mch_id'],
"通道": channel.channel_enum.desc,
"发起金额": str(order.amount),
"实际支付金额": str(order.tx_amount),
"优惠金额": str(order_detail.offer),
"手续费": str(order_detail.fee),
"成本金额": str(order_detail.cost),
"收入金额": str(order_detail.profit),
"订单状态": order.state.desc,
"充值类型": order.source.desc,
})
filename = 'epay_deposit_record_%s.csv' % DateTimeKit.datetime_to_str(
DateTimeKit.get_cur_datetime(),
DateTimeFormatEnum.TIGHT_DAY_FORMAT
)
return CsvKit.send_csv(data, filename=filename, fields=data[0].keys())
def export_withdraw_list_csv(cls, order_list, order_detail_dict, all_channels):
"""
导出提现订单列表
:param order_list:
:param order_detail_dict:
:param all_channels:
:return:
"""
# 用户id、系统订单号、商户订单号、创建时间、完成时间、商户、通道商户号、通道、提现金额、手续费、成本金额、收入金额、订单状态、出款类型、备注
data = list()
for order in order_list:
channel = all_channels.get(order.channel_id)
order_detail = order_detail_dict[order.order_id]
data.append({
"用户ID": order.uid,
"系统订单号": order.sys_tx_id,
"商户订单号": order.mch_tx_id,
"创建时间": order.str_create_time,
"完成时间": order_detail.str_done_time,
"商户": order.merchant.name,
"通道商户号": channel.channel_enum.conf['mch_id'] if channel else '',
"通道": channel.channel_enum.desc if channel else '',
"提现金额": str(order.amount),
"手续费": str(order_detail.fee),
"成本金额": str(order_detail.cost),
"收入金额": str(order_detail.profit),
"订单状态": order.state.desc,
"出款类型": order.source.desc,
"备注": order_detail.comment or '',
})
filename = 'epay_withdraw_record_%s.csv' % DateTimeKit.datetime_to_str(
DateTimeKit.get_cur_datetime(),
DateTimeFormatEnum.TIGHT_DAY_FORMAT
)
return CsvKit.send_csv(data, filename=filename, fields=data[0].keys())
def get(self):
"""
订单修改日志
:return:
"""
if not request.args:
return ResponseSuccess(
message=
"参数规则：?merchant=test&date=20190901&order_id=123&uid=123&ref_id=xxx&export=1，"
"必填参数：merchant，"
"可选参数：date，order_id，uid，ref_id，export，"
"当不填写date时，默认查询当天所有的数据").as_response()
try:
date = request.args.get('date')
if date:
date = DateTimeKit.str_to_datetime(
date, DateTimeFormatEnum.TIGHT_DAY_FORMAT, to_date=True)
else:
date = DateTimeKit.get_cur_date()
except:
return ResponseSuccess(
message="请输入有效的查询日期，格式为：20190901").as_response()
q_params = dict()
order_id = request.args.get('order_id')
try:
order_id = OrderUtils.parse_tx_id(order_id)
except:
pass
if order_id:
q_params['order_id'] = order_id
uid = request.args.get('uid')
if uid:
q_params['uid'] = uid
ref_id = request.args.get('ref_id')
if ref_id:
q_params['ref_id'] = ref_id
if not q_params:
return ResponseSuccess(
message="必须输入 order_id/uid/ref_id 其中一个或多个参数").as_response()
events = OrderEvent.query_model(query_fields=q_params, date=date)
rst = list()
for event in events:
rst.append(
dict(
create_time=event.create_time,
order_id=event.order_id,
uid=event.uid,
ref_id=event.ref_id,
data_before=event.data_before,
data_after=event.data_after,
))
rst = sorted(rst, key=lambda x: x['create_time'], reverse=True)
for x in rst:
x['create_time'] = DateTimeKit.datetime_to_str(x['create_time'])
if rst and request.args.get('export'):
filename = 'order_events_%s.csv' % DateTimeKit.datetime_to_str(
date, DateTimeFormatEnum.TIGHT_DAY_FORMAT)
return CsvKit.send_csv(rst,
filename=filename,
fields=rst[0].keys())
return ResponseSuccess(bs_data=rst).as_response()
def get(self):
"""
查询用户余额变更流水
:return:
"""
if not request.args:
return ResponseSuccess(
message=
"参数规则：?merchant=test&account=8618912341234&date=20190901&export=1"
).as_response()
try:
account = request.args.get('account')
if account:
account = '+' + account.strip('+').strip()
if not PhoneNumberParser.is_valid_number(account):
raise
except:
return ResponseSuccess(
message="请输入正确的用户手机号码，必须有完整区号，不填+号，如：8613812349999"
).as_response()
try:
merchant = MerchantEnum.from_name(request.args['merchant'])
except:
return ResponseSuccess(message="请输入正确的商户名称，有效的商户名称包括：%s" %
MerchantEnum.get_names()).as_response()
try:
date = request.args.get('date')
if date:
date = DateTimeKit.str_to_datetime(
date, DateTimeFormatEnum.TIGHT_DAY_FORMAT, to_date=True)
else:
date = DateTimeKit.get_cur_date()
except:
return ResponseSuccess(
message="请输入有效的查询日期，格式为：20190901").as_response()
rst = dict(
data=list(),
sum_value=0,
)
events = UserBalanceEvent.query_by_date(date,
merchant=merchant,
date=date)
if account:
user = User.query_user(merchant, account=account)
if not user:
return ResponseSuccess(message="用户不存在，请检查参数。商户：%s，手机号码：%s" %
(merchant.name, account))
user_balance = UserBalance.query_balance(user.uid,
merchant).first()
rst.update(user=dict(
account=user.account,
uid=user.uid,
user_balance=str(user_balance.real_balance),
))
events = events.filter_by(uid=user.uid)
rst['sql'] = str(events)
for event in events:
rst['sum_value'] += event.value_real
rst['data'].append(
dict(
create_time=event.create_time,
uid=event.uid,
ref_id=event.ref_id,
order_type=event.order_type.desc,
source=event.source.desc,
bl_type=event.bl_type.desc,
value=str(event.value_real),
ad_type=event.ad_type.desc,
tx_id=event.tx_id, # 大整数，转为字符串
comment=event.comment,
extra=event.raw_extra,
))
rst['data'] = sorted(rst['data'],
key=lambda x: x['create_time'],
reverse=True)
for x in rst['data']:
x['create_time'] = DateTimeKit.datetime_to_str(x['create_time'])
if rst['data'] and request.args.get('export'):
filename = 'user_balance_events_%s.csv' % DateTimeKit.datetime_to_str(
date, DateTimeFormatEnum.TIGHT_DAY_FORMAT)
return CsvKit.send_csv(rst['data'],
filename=filename,
fields=rst['data'][0].keys())
rst['sum_value'] = str(rst['sum_value'])
return ResponseSuccess(bs_data=rst).as_response()
def get(self):
"""
查询商户余额变更流水
:return:
"""
if not request.args:
return ResponseSuccess(
message="参数规则：?merchant=test&date=20190901&export=1"
).as_response()
try:
merchant = MerchantEnum.from_name(request.args['merchant'])
merchant_info = MerchantInfo.query_merchant(merchant)
if not merchant_info:
raise
except:
return ResponseSuccess(message="请输入正确的商户名称，有效的商户名称包括：%s" %
MerchantEnum.get_names()).as_response()
try:
date = request.args.get('date')
if date:
date = DateTimeKit.str_to_datetime(
date, DateTimeFormatEnum.TIGHT_DAY_FORMAT, to_date=True)
else:
date = DateTimeKit.get_cur_date()
except:
return ResponseSuccess(
message="请输入有效的查询日期，格式为：20190901").as_response()
balance = dict(
balance_total=str(merchant_info.balance_total),
balance_available=str(merchant_info.balance_available),
balance_income=str(merchant_info.balance_income),
balance_frozen=str(merchant_info.balance_frozen),
)
events = MerchantBalanceEvent.query_by_date(date,
merchant=merchant,
date=date)
rst = dict(
data=list(),
sum_value=0,
balance=balance,
)
rst['sql'] = str(events)
for event in events:
rst['sum_value'] += event.value_real
rst['data'].append(
dict(
create_time=event.create_time,
ref_id=event.ref_id,
order_type=event.order_type.desc,
source=event.source.desc,
bl_type=event.bl_type.desc,
value=str(event.value_real),
ad_type=event.ad_type.desc,
tx_id=event.tx_id,
comment=event.comment,
))
rst['data'] = sorted(rst['data'],
key=lambda x: x['create_time'],
reverse=True)
for x in rst['data']:
x['create_time'] = DateTimeKit.datetime_to_str(x['create_time'])
if rst['data'] and request.args.get('export'):
filename = 'merchant_balance_events_%s.csv' % DateTimeKit.datetime_to_str(
date, DateTimeFormatEnum.TIGHT_DAY_FORMAT)
return CsvKit.send_csv(rst['data'],
filename=filename,
fields=rst['data'][0].keys())
rst['sum_value'] = str(rst['sum_value'])
return ResponseSuccess(bs_data=rst).as_response()
def post(self):
"""
商户充值订单查询数据导出
:return:
"""
form, error = DepositOrderSelectForm().request_validate()
if error:
return error.as_response()
user = g.user
try:
order_list_query = OrderDeposit.query_by_create_time(
begin_time=form.start_datetime.data,
end_time=form.end_datetime.data,
merchant=MerchantEnum(user.mid))
except MultiMonthQueryException as e:
return MultiMonthQueryError().as_response()
kwargs = {}
if form.order_id.data:
kwargs["sys_tx_id"] = form.order_id.data
if form.state.data != "0":
kwargs["_state"] = form.state.data.value
entries = order_list_query.filter_by(**kwargs).all()
items = []
channel_lst = list(set([o.channel_id for o in entries]))
channel_items = {
Channel.id: Channel
for Channel in ChannelConfig.query.filter(
ChannelConfig.id.in_(channel_lst)).all()
}
kwargs = {}
if form.order_id.data and entries:
kwargs['id'] = entries[0].order_id
order_items = {
item.id: item
for item in OrderDetailDeposit.query_by_create_time(
begin_time=form.start_datetime.data,
end_time=form.end_datetime.data,
merchant=MerchantEnum(user.mid)).filter_by(**kwargs).all()
}
for order in entries:
item_channel = channel_items.get(order.channel_id, {})
detail = order_items.get(order.order_id, {})
if not item_channel or not detail:
continue
items.append(
dict(mch_tx_id=order.mch_tx_id,
sys_tx_id=order.sys_tx_id,
payment_type=item_channel.channel_enum.
conf['payment_type'].desc,
amount=detail.amount,
tx_amount=detail.tx_amount,
fee=detail.fee,
create_time=order.str_create_time,
done_time=order.update_time,
state=order.state.desc,
deliver=order.deliver.desc))
items = sorted(items, key=lambda item: item['create_time'])
if items:
data = list()
for item in items:
data.append({
"商户订单号": item['mch_tx_id'],
"支付方式": item['payment_type'],
"发起金额": str(item['amount']),
"实际支付金额": str(item['tx_amount']),
"手续费": str(item['fee']),
"创建时间": item['create_time'],
"完成时间": item['done_time'],
"订单状态": item['state'],
"通知状态": item['deliver']
})
filename = 'epay_deposit_record_%s.csv' % (
DateTimeKit.datetime_to_str(
DateTimeKit.get_cur_datetime(),
DateTimeFormatEnum.TIGHT_DAY_FORMAT))
return CsvKit.send_csv(data,
filename=filename,
fields=data[0].keys())
def post(self):
"""
商户提现订单查询
:return:
"""
form, error = WithdrawOrderSelectForm().request_validate()
if error:
return error.as_response()
user = g.user
try:
order_list_query = OrderWithdraw.query_by_create_time(
begin_time=form.start_datetime.data,
end_time=form.end_datetime.data,
merchant=MerchantEnum(user.mid))
except MultiMonthQueryException as e:
return MultiMonthQueryError().as_response()
kwargs = {}
if form.order_id.data:
kwargs["mch_tx_id"] = form.order_id.data
if form.state.data != "0":
kwargs["_state"] = form.state.data.value
entries = order_list_query.filter_by(**kwargs).all()
bank_lst = list(set([o.bank_id for o in entries]))
kwargs = {}
if form.order_id.data and entries:
kwargs['id'] = entries[0].order_id
if not MerchantEnum(user.mid).is_api_merchant:
bank_items = {
bank.id: bank
for bank in BankCard.query.filter(BankCard.id.in_(
bank_lst)).all()
}
order_items = {
item.id: item
for item in OrderDetailWithdraw.query_by_create_time(
begin_time=form.start_datetime.data,
end_time=form.end_datetime.data,
merchant=MerchantEnum(user.mid)).filter_by(**kwargs).all()
}
items = []
for order in entries:
if not MerchantEnum(user.mid).is_api_merchant:
bank = bank_items.get(order.bank_id, {})
else:
bank = order.get_bank_card()
detail = order_items.get(order.order_id, {})
if not bank or not detail:
continue
items.append(
dict(mch_tx_id=order.mch_tx_id,
sys_tx_id=order.sys_tx_id,
amount=detail.amount,
fee=detail.fee,
account_name=bank.account_name,
bank_name=bank.bank_name,
branch="{}{}{}".format(bank.province, bank.city,
bank.branch),
card_no=bank.card_no,
create_time=order.str_create_time,
done_time=order.update_time,
state=order.state.desc,
deliver=order.deliver.desc))
items = sorted(items, key=lambda item: item['create_time'])
if items:
data = list()
for item in items:
data.append({
"商户订单号": item['mch_tx_id'],
"提现金额": str(item['amount']),
"手续费": str(item['fee']),
"开户名": item['account_name'],
"开户银行": item['bank_name'],
"开户地址": item['branch'],
"银行卡号": item['card_no'],
"创建时间": item['create_time'],
"完成时间": item['done_time'],
"订单状态": item['state'],
"通知状态": item['deliver']
})
filename = 'epay_withdraw_record_%s.csv' % (
DateTimeKit.datetime_to_str(
DateTimeKit.get_cur_datetime(),
DateTimeFormatEnum.TIGHT_DAY_FORMAT))
return CsvKit.send_csv(data,
filename=filename,
fields=data[0].keys())