def parse_hb_detail(self, hb_info: dict):
"""
解析红包 并格式化sql语句
:return:
"""
# print(hb_info)
# 发送者昵称
sender_nick = hb_info.get('sendNick', '')
# 发送者微信号
sender_wxid = hb_info.get('sendUserName', '')
# 发送编号
send_id = hb_info.get('sendId', '')
# 红包发出的总金额
total_amount = hb_info.get('totalAmount', '')
total_amount = format_fee(total_amount)
# 红包发出的总个数
total_num = hb_info.get('totalNum', '')
# 被领取的总金额
recv_amount = hb_info.get('recAmount', '')
recv_amount = format_fee(recv_amount)
# 被领取的红包总数
recv_num = hb_info.get('recNum', '')
status_mess = hb_info.get('statusMess', '')
# 接收人编号
receiver_id = hb_info.get('receiveId', '')
# 祝福语
wishing = hb_info.get('wishing', '')
# 当前用户是否是发送者
is_sender = hb_info.get('isSender', '')
# 红包头标题
head_title = hb_info.get('headTitle', '')
# 零钱去向
change_words = hb_info.get('changeWording', '')
# 领取状态
receive_status = hb_info.get('receiveStatus', '')
# 红包状态
hbstatus = hb_info.get('hbStatus', '')
# 红包类型 ：群发红包 / 一对一红包
hbtype = hb_info.get('hbType', '')
hbkind = ''
can_share = ''
sql = '''INSERT INTO "TBL_PRCD_WECHAT_HBDETAIL_INFO"
("sendUserNick", "sendUserWxid", "sendId", "recNum", "totalNum", "totalAmount", "amount", "wishing", "isSender", "receiveId",
"hbType", "hbStatus", "receiveStatus", "statusMess", "headTitle", "canShare", "hbKind",
"recAmount", "changeWording")
VALUES({}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {})'''
params = (sender_nick, sender_wxid, send_id, recv_num, total_num,
total_amount, recv_amount, wishing, is_sender, receiver_id,
hbtype, hbstatus, receive_status, status_mess, head_title,
can_share, hbkind, recv_amount, change_words)
return sql_format(sql, params)
def parse_hb_receiver(self, hb_info: dict):
"""
解析红包接收人信息， 并格式化sql语句
:return:
"""
receiver_list = []
sql_list = []
receiver_list = hb_info.get('receiven', [])
send_id = hb_info.get('sendId', '')
for receiver in receiver_list:
# 接收者id
receiver_id = receiver.get('recvId', '')
# 接收者昵称
receiver_nick = receiver.get('receivenNick', '')
# 回复语
answer = receiver.get('answer', '')
# 小贴士
game_tips = receiver.get('gameTips', '')
# 接收金额
recv_amount = receiver.get('receiveAmount', '')
recv_amount = format_fee(recv_amount)
# 接收时间
recv_time = datetime_format(receiver.get('receiveTime', ''))
# 微信id
username = receiver.get('userName', '')
sql = '''INSERT INTO "TBL_PRCD_WECHAT_HBRECEIVER_INFO" ("sendId", "receiveAmount", "receiveTime", "answer",
"receiveId", "receiveNick", "state", "gameTips","receiveOpenId", "userName") VALUES({},{},{},{},{},{},{},{},{},{})'''
params = (send_id, recv_amount, recv_time, answer, receiver_id,
receiver_nick, '', game_tips, '', username)
sql_list.append(sql_format(sql, params))
return sql_list
def parse_api_bill_detail(self, bill: dict, detail_result: dict):
"""
解析api版本接口 账单详情结果
:param title: 标题
:param bill_id: 账单id
:param trans_id: 交易id
:param timestamp: 时间戳
:param classify: 分类
:param detail_result: 账单详情结果集
:return: sql: str
"""
title = bill.get('title', '')
bill_id = bill.get('bill_id', '')
bill_type = bill.get('bill_type', '')
trans_id = bill.get('trans_id', '')
timestamp = bill.get('timestamp', '')
classify_type = bill.get('classify_type', '')
if not isinstance(detail_result, dict):
return ''
user_roll_detail = detail_result.get('user_roll_detail', {})
# 费用类型
fee_type = user_roll_detail.get('fee_type', '')
# 支付人微信id
payer_wxid = user_roll_detail.get('payer_wxid', '')
# 支付金额
pay_fee = user_roll_detail.get('pay_fee', 0)
pay_fee = self.format_fee(pay_fee)
# 商品名称
goods_name = user_roll_detail.get('goods_name', '')
# 商户简称
mch_name = user_roll_detail.get('mch_name', '')
# 当前状态 支付状态
current_state = user_roll_detail.get('current_state', '')
# 支付时间
pay_time = user_roll_detail.get('pay_time', '')
pay_time = datetime_format(pay_time)
# 支付方式 零钱/银行名称
pay_bank_name = user_roll_detail.get('pay_bank_name', '')
# 交易单号
out_trade_no = user_roll_detail.get('out_trade_no', '')
# 转账单号
trans_id = user_roll_detail.get('trans_id', '')
# 费用单位
fee_unit = user_roll_detail.get('fee_unit', '')
# 商户全称
mch_full_name = user_roll_detail.get('mch_full_name', '')
# 接收人姓名
receiver_name = user_roll_detail.get('receiver_name', '')
# 转账时间
transfer_pay_time = user_roll_detail.get('transfer_pay_time', '')
transfer_pay_time = datetime_format(transfer_pay_time)
# 转账单号
transfer_trans_id = user_roll_detail.get('transfer_trans_id', '')
# 收款方备注
receiver_remark = user_roll_detail.get('receiver_remark', '')
# 收钱时间
transfer_recv_time = user_roll_detail.get('transfer_recv_time', '')
transfer_recv_time = datetime_format(transfer_recv_time)
# 接收方微信id
receiver_wxid = user_roll_detail.get('receiver_wxid', '')
# 转账说明
transfer_detail = user_roll_detail.get('transfer_detail', '')
withdraw_fee = user_roll_detail.get('withdraw_fee', 0)
withdraw_fee = self.format_fee(withdraw_fee)
# 提现申请时间
withdraw_apply_time = user_roll_detail.get('withdraw_apply_time', '')
withdraw_apply_time = datetime_format(withdraw_apply_time)
# 提现到账时间
withdraw_arrive_time = user_roll_detail.get('withdraw_arrive_time', '')
withdraw_arrive_time = datetime_format(withdraw_arrive_time)
# 提现银行名称
withdraw_bank_name = user_roll_detail.get('withdraw_bank_name', '')
# 提现单号
withdraw_trans_id = user_roll_detail.get('withdraw_trans_id', '')
# 实际提现金额
real_withdraw_fee = user_roll_detail.get('real_withdraw_fee', 0)
real_withdraw_fee = self.format_fee(real_withdraw_fee)
# 提现费用
real_withdraw_charge = user_roll_detail.get('real_withdraw_charge', 0)
real_withdraw_charge = self.format_fee(real_withdraw_charge)
# 银行卡尾号
card_tail = user_roll_detail.get('card_tail', '')
# 退款金额
refund_flow_fee = user_roll_detail.get('refund_flow_fee', 0)
refund_flow_fee = self.format_fee(refund_flow_fee)
# 退款状态
refund_flow_status = user_roll_detail.get('refund_flow_status', '')
# 退款时间
refund_flow_time = user_roll_detail.get('refund_flow_time', '')
refund_flow_time = datetime_format(refund_flow_time)
# 退款银行
refund_flow_bank_name = user_roll_detail.get('refund_flow_bank_name',
'')
# 退款id
refund_flow_refund_id = user_roll_detail.get('refund_flow_refund_id',
'')
# 退款交易单号
refund_flow_out_trade_no = user_roll_detail.get(
'refund_flow_out_trade_no', '')
# 原生单号
jump_refund_flow_origin_id = user_roll_detail.get(
'jump_refund_flow_origin_id', '')
# 充值时间
charge_time = user_roll_detail.get('charge_time', '')
charge_time = datetime_format(charge_time)
charge_fee = user_roll_detail.get('charge_fee', 0)
charge_fee = self.format_fee(charge_fee)
recv_fee = user_roll_detail.get('recv_fee', 0)
recv_fee = self.format_fee(recv_fee)
# 收款时间
recv_time = user_roll_detail.get('recv_time', '')
recv_time = datetime_format(recv_time)
# 交易单号
out_trade_no = user_roll_detail.get('out_trade_no', '')
payer_name = self.match_payer_name(title, bill_type, classify_type)
receiver_name = self.match_receiver_name(title, bill_type,
classify_type)
sql = '''INSERT INTO "TBL_PRCD_WECHAT_BILL_RECORDS" ("title", "bill_id","current_state", "pay_fee","withdraw_fee",
"charge_fee","recv_fee","classify_type","bill_type","payer_name","receiver_name", "payer_wxid","receiver_wxid","transfer_detail","receiver_remark",
"goods_name","mch_name","mch_full_name", "pay_time","recv_time","transfer_pay_time", "transfer_recv_time","withdraw_apply_time",
"withdraw_arrive_time", "charge_time", "pay_bank_name", "withdraw_bank_name","out_trade_no", "trans_id","transfer_trans_id",
"withdraw_trans_id","real_withdraw_fee","real_withdraw_charge","card_tail","refund_flow_fee", "refund_flow_status",
"refund_flow_time","refund_flow_bank_name","refund_flow_refund_id","refund_flow_out_trade_no","source_acct") VALUES
({}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {},{}, {}, {}, {}, {}, {},{}, {}, {}, {}, {}, {}, {}, {}, {}, {},
{}, {}, {}, {}, {}, {}, {}, {}, {}, {})'''
params = (title, bill_id, current_state, pay_fee, withdraw_fee,
charge_fee, recv_fee, classify_type, bill_type, payer_name,
receiver_name, payer_wxid, receiver_wxid, transfer_detail,
receiver_remark, goods_name, mch_name, mch_full_name,
pay_time, recv_time, transfer_pay_time, transfer_recv_time,
withdraw_apply_time, withdraw_arrive_time, charge_time,
pay_bank_name, withdraw_bank_name, out_trade_no, trans_id,
transfer_trans_id, withdraw_trans_id, real_withdraw_fee,
real_withdraw_charge, card_tail, refund_flow_fee,
refund_flow_status, refund_flow_time, refund_flow_bank_name,
refund_flow_refund_id, refund_flow_out_trade_no,
self.wechat.account)
return sql_format(sql, params)
def parse_web_bill_detail(self, bill: dict, detail_result: dict):
"""
解析网页版账单详情
:param title: 账单抬头
:param bill_id: 账单id
:param trans_id: 交易id
:param timestamp: 时间戳
:param classify_type: 分类
:param bill_type: 账单类型
:param bill_result: 账单结果集
:return: sql:str
"""
def make_key_pair(detail_result: dict):
bill_dict = {}
preview = detail_result.get('preview', [])
if not preview:
return bill_dict
for part in preview:
label = part.get('label', {})
if not label:
continue
label_name = label.get('name', '')
if not label_name:
continue
value = part.get('value', [])
if not value:
continue
label_value = ''
try:
label_value = value[0].get('name', '')
except:
pass
bill_dict[label_name] = label_value
return bill_dict
title = bill.get('title', '')
bill_id = bill.get('bill_id', '')
bill_type = bill.get('bill_type', '')
trans_id = bill.get('trans_id', '')
timestamp = bill.get('timestamp', '')
classify_type = bill.get('classify_type', '')
payer_wxid = bill.get('payer_wxid', '')
out_trade_no = bill.get('out_trade_no', '')
receiver_wxid = bill.get('receiver_wxid', '')
header = detail_result.get('header', {})
payer_name = self.match_payer_name(title, bill_type, classify_type)
receiver_name = self.match_receiver_name(title, bill_type,
classify_type)
fee = header.get('fee', 0)
bill_dict = make_key_pair(detail_result)
current_state = bill_dict.get('当前状态', '')
pay_time = bill_dict.get('支付时间', '')
pay_time = datetime_format(pay_time)
recv_time = bill_dict.get('收款时间', '')
recv_time = datetime_format(recv_time)
pay_bank_name = bill_dict.get('支付方式', '')
out_trade_no = bill_dict.get('交易单号', '')
mch_id = bill_dict.get('商户单号', '')
hongbao_detail = bill_dict.get('红包详情', '')
receiver_remark = bill_dict.get('收款方备注', '')
transfer_pay_time = bill_dict.get('转账时间', '')
transfer_pay_time = datetime_format(transfer_pay_time)
transfer_trans_id = bill_dict.get('转账单号', '')
goods_name = bill_dict.get('商品', '')
charge_time = bill_dict.get('充值时间', '')
charge_time = datetime_format(charge_time)
mch_full_name = bill_dict.get('商户全称', '')
good_price = bill_dict.get('原价', 0)
discounts = bill_dict.get('优惠', '')
refund_flow_status = bill_dict.get('退款状态', '')
refund_flow_time = bill_dict.get('退款时间', '')
refund_flow_time = datetime_format(refund_flow_time)
refund_flow_bank_name = bill_dict.get('退款方式', '')
refund_flow_out_trade_no = bill_dict.get('退款单号', '')
withdraw_fee = bill_dict.get('提现金额', 0)
service_fee = bill_dict.get('手续费', 0)
withdraw_apply_time = bill_dict.get('申请时间', '')
withdraw_apply_time = datetime_format(withdraw_apply_time)
withdraw_arrive_time = bill_dict.get('到账时间', '')
withdraw_arrive_time = datetime_format(withdraw_arrive_time)
withdraw_bank_name = bill_dict.get('提现银行', '')
withdraw_trans_id = bill_dict.get('提现单号', '')
pay_fee = 0
recv_fee = 0
withdraw_fee = 0
charge_fee = 0
refund_flow_fee = 0
# 转账 扫码支付 群收款 信用卡还款
if bill_type == 1:
pay_fee = fee
# 微信红包 收取好友红包
if (bill_type == 6 and classify_type == 2) or \
(bill_type == 4 and classify_type == 1) or \
(bill_type == 20 and classify_type == 11) or \
(bill_type == 4 and classify_type == 3) or \
(bill_type == 20 and classify_type == 99):
recv_fee = fee
# 零钱提现
if bill_type == 7 and classify_type == 53:
withdraw_fee = fee
# 充值
if bill_type == 2 and classify_type == 97:
charge_fee = fee
# 退款（红包退款、商家退款）
if bill_type == 11 and classify_type == 31:
refund_flow_fee = fee
current_state = refund_flow_status
sql = '''INSERT INTO "TBL_PRCD_WECHAT_BILL_RECORDS" ("title", "bill_id", "current_state", "pay_fee", "withdraw_fee",
"charge_fee", "recv_fee", "classify_type", "bill_type","payer_name", "payer_wxid", "receiver_name", "receiver_wxid",
"receiver_remark", "goods_name", "mch_full_name", "pay_time","recv_time","transfer_pay_time", "withdraw_apply_time",
"withdraw_arrive_time", "charge_time", "pay_bank_name", "withdraw_bank_name", "out_trade_no","trans_id",
"transfer_trans_id","withdraw_trans_id", "refund_flow_fee", "refund_flow_status","refund_flow_time",
"refund_flow_bank_name", "refund_flow_out_trade_no", "source_acct") VALUES({}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {},
{}, {}, {}, {}, {}, {}, {},{}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {},{})'''
params = (title, bill_id, current_state, pay_fee, withdraw_fee,
charge_fee, recv_fee, classify_type, bill_type, payer_name,
payer_wxid, receiver_name, receiver_wxid, receiver_remark,
goods_name, mch_full_name, pay_time, recv_time,
transfer_pay_time, withdraw_apply_time, withdraw_arrive_time,
charge_time, pay_bank_name, withdraw_bank_name, out_trade_no,
trans_id, transfer_trans_id, withdraw_trans_id,
refund_flow_fee, refund_flow_status, refund_flow_time,
refund_flow_bank_name, refund_flow_out_trade_no,
self.wechat.account)
return sql_format(sql, params)
def parse_simple_bill(self, bill: dict):
"""
解析简要账单信息
:param bill:
:return:
"""
if not isinstance(bill, dict):
return ''
# 账单ID
bill_id = bill.get('bill_id', '')
# 交易ID
trans_id = bill.get('trans_id', '')
# 交易抬头
title = bill.get('title', '')
# 时间戳
timestamp = bill.get('timestamp', '')
trade_time = datetime_format(timestamp)
# 交易金额
fee = bill.get('fee', 0)
fee = self.format_fee(fee)
# 金额类型
fee_type = bill.get('fee_type', '')
# 收入/支出
fee_attr = bill.get('fee_attr', '')
# 当前状态
current_state = bill.get('current_state', '')
# 当前状态类型
current_state_type = bill.get('current_state_type', '')
# 账单类型
bill_type = bill.get('bill_type', '')
# 交易单号
out_trade_no = bill.get('out_trade_no', '')
# 总退还费用
total_refund_fee = bill.get('total_refund_fee', '')
# 交易分类
classify_type = bill.get('classify_type', '')
# 支付银行/零钱
pay_bank_name = bill.get('pay_bank_name', '')
# 备注
remark = bill.get('remark', '')
# 商品数据
business_data = bill.get('business_data', '')
# 充值金额
charge_fee = bill.get('charge_fee', 0)
# 付款人备注
payer_remark = bill.get('payer_remark', '')
#
payer_uin = bill.get('payer_uin', '')
# 付款人微信ID
payer_name = bill.get('payer_wxid', '')
# 是否是朋友
is_friend = bill.get('is_friend', '')
pay_fee = 0
pay_time = ''
# 转账、二维码收付款、微信红包、信用卡还款、扫码支付、向群收款支付
if bill_type == 1:
pay_fee = fee
pay_time = trade_time
recv_fee = 0
recv_time = ''
receiver_name = ''
# 微信红包 收取好友红包
if bill_type == 6 and classify_type == 2:
recv_fee = fee
recv_time = trade_time
# 微信转账 收到转账
if bill_type == 4 and classify_type == 1:
recv_fee = fee
recv_time = trade_time
# 提款
if bill_type == 20 and classify_type == 11:
recv_fee = fee
recv_time = trade_time
# 提现金额
withdraw_fee = 0
withdraw_apply_time = ''
# 零钱提现
if bill_type == 7 and classify_type == 53:
withdraw_fee = fee
withdraw_apply_time = trade_time
# 充值金额
charge_fee = 0
charge_time = ''
# 充值
if bill_type == 2 and classify_type == 97:
charge_fee = fee
charge_time = trade_time
# 退款金额
refund_flow_fee = 0
refund_flow_time = ''
# 退款（红包退款、商家退款）
if bill_type == 11 and classify_type == 31:
refund_flow_fee = fee
refund_flow_time = trade_time
sql = '''INSERT INTO "TBL_PRCD_WECHAT_BILL_RECORDS" ("title", "bill_id","current_state","pay_fee",
"withdraw_fee","charge_fee","recv_fee","classify_type","bill_type","payer_name",
"pay_time","recv_time","withdraw_apply_time",
"charge_time", "pay_bank_name", "out_trade_no","trans_id","refund_flow_fee", "refund_flow_time",
"source_acct") VALUES({}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {},{})'''
params = (title, bill_id, current_state, pay_fee, withdraw_fee,
charge_fee, recv_fee, classify_type, bill_type, payer_name,
pay_time, recv_time, withdraw_apply_time, charge_time,
pay_bank_name, out_trade_no, trans_id, refund_flow_fee,
refund_flow_time, self.wechat.account)
return sql_format(sql, params)