67 lines
1.2 KiB
PL/PgSQL
67 lines
1.2 KiB
PL/PgSQL
BEGIN;
|
|
|
|
-- 锁定代理主钱包，确认余额和冻结余额
|
|
SELECT *
|
|
FROM tb_agent_wallet
|
|
WHERE shop_id = :shop_id
|
|
AND wallet_type = 'main'
|
|
AND deleted_at IS NULL
|
|
FOR UPDATE;
|
|
|
|
-- 确认 frozen_balance = 0 且 balance > 0 后执行
|
|
INSERT INTO tb_agent_wallet_transaction (
|
|
agent_wallet_id,
|
|
shop_id,
|
|
user_id,
|
|
transaction_type,
|
|
amount,
|
|
balance_before,
|
|
balance_after,
|
|
status,
|
|
reference_type,
|
|
reference_id,
|
|
remark,
|
|
metadata,
|
|
creator,
|
|
shop_id_tag,
|
|
enterprise_id_tag,
|
|
created_at,
|
|
updated_at
|
|
)
|
|
SELECT
|
|
id,
|
|
shop_id,
|
|
:operator_user_id,
|
|
'deduct',
|
|
-balance,
|
|
balance,
|
|
0,
|
|
1,
|
|
NULL,
|
|
NULL,
|
|
'平台清理代理预充值剩余余额，历史消费流水保留',
|
|
jsonb_build_object('reason', 'clear_agent_main_wallet_balance'),
|
|
:operator_user_id,
|
|
shop_id_tag,
|
|
enterprise_id_tag,
|
|
NOW(),
|
|
NOW()
|
|
FROM tb_agent_wallet
|
|
WHERE shop_id = :shop_id
|
|
AND wallet_type = 'main'
|
|
AND deleted_at IS NULL
|
|
AND balance > 0
|
|
AND frozen_balance = 0;
|
|
|
|
UPDATE tb_agent_wallet
|
|
SET balance = 0,
|
|
version = version + 1,
|
|
updated_at = NOW()
|
|
WHERE shop_id = :shop_id
|
|
AND wallet_type = 'main'
|
|
AND deleted_at IS NULL
|
|
AND balance > 0
|
|
AND frozen_balance = 0;
|
|
|
|
COMMIT;
|