# 3.48 代理钱包
## 調整 2022/10/14
[[IMS] 佣金纪录](##佣金纪录)
* delete column:
* ul_agent_commission.currency
* ul_agent_commission_pending.currency
## 調整 2022/10/05
[[IMS] 代理取款銀行卡](##代理取款銀行卡)
* bank_address 欄位已棄用
* rename column:
* ul_agent_payments_info.payment_id => payments_info_id
* ul_agent_payments_info.create_by=>creator
* ul_agent_payments_info.update_by=>updater
[[IMS] 代理提款](##代理提款)
* add table: ul_agent_withdrawal_pending
* add table: ul_agent_withdrawal_sequence
* rename column: withdrawal_type => bank_payment_type
* ul_agent_withdrawal_pending.status integer => varchar
* rename column: ul_agent_withdrawal_pending.payment_id => payments_info_id
* rename column: ul_agent_withdrawal.payment_id => payments_info_id
[[IMS] 佣金纪录](##佣金纪录)
* add column: ul_agent_commission.commission_id
* add column: ul_agent_commission_pending.commission_pending_id
* add UNIQUE constraint on(settlement_id, ul_agent_id)
[實際返佣編輯歷史](###實際返佣編輯歷史)
* add column: ul_agent_commission_audit.ul_agent_id
## 調整 2022/10/04
[[IMS] 代理取款銀行卡](##代理取款銀行卡)
ul_agent_payments_info
rename column: pic3_id => pic_id
ul_agent_payments_info.status intger => varchar
[[IMS] 代理提款](##代理提款)
ul_agent_withdrawal
rename column: ec_remarks => agent_remarks
ul_agent_withdrawal.status intger => varchar
ul_agent_withdrawal_pending.status intger => varchar
[[IMS] 代理提款操作紀錄](##代理提款操作紀錄)
[[IMS] 佣金纪录](##佣金纪录)
rename table: ul_agent_settlement_pending => ul_agent_commission_pending
add table: ul_agent_commission
rename column: settlement_status => status
ul_agent_commission.status intger => varchar
[實際返佣編輯歷史](###實際返佣編輯歷史)
rename table: ul_agent_settlement_pending_audit => ul_agent_commission_audit
recolumn table: ul_agent_commission_audit.created_by => creator
## 代理取款銀行卡
```
-- VIP-1796 [BE][IMS][AGENT][CRUD] 編輯銀行卡
create table IF NOT EXISTS ul_agent_payments_info
(
payments_info_id uuid default public.uuid_generate_v4() not null primary key,
ul_agent_id integer not null,
bank_id uuid not null,
bank_branch varchar,
bank_account varchar not null,
bank_account_name varchar,
status varchar not null,
activation_province varchar,
activation_city varchar,
activation_mobile varchar,
activation_id varchar,
activation_email varchar,
create_time timestamp default clock_timestamp(),
create_by varchar not null,
update_time timestamp default clock_timestamp(),
update_by varchar,
pic_id uuid
);
COMMENT ON TABLE ul_agent_payments_info IS '代理取款銀行卡';
comment on column ul_agent_payments_info.payments_info_id is '支付編號';
comment on column ul_agent_payments_info.ul_agent_id is '代理編號';
comment on column ul_agent_payments_info.bank_id is '銀行編號';
comment on column ul_agent_payments_info.bank_branch is '銀行支行名稱';
comment on column ul_agent_payments_info.bank_account is '銀行帳戶';
comment on column ul_agent_payments_info.bank_account_name is '銀行卡姓名';
comment on column ul_agent_payments_info.status is '狀態(Enum:UlAgentPaymentsInfoStatus):激活 ACTIVE、停用BLOCKED';
comment on column ul_agent_payments_info.activation_province is '開戶省份';
comment on column ul_agent_payments_info.activation_city is '開戶城市';
comment on column ul_agent_payments_info.activation_mobile is '银行预留手机号码';
comment on column ul_agent_payments_info.activation_id is '銀行開戶證件號';
comment on column ul_agent_payments_info.activation_email is '付款人Email';
comment on column ul_agent_payments_info.create_time is '建立時間';
comment on column ul_agent_payments_info.create_by is '建立人員';
comment on column ul_agent_payments_info.update_time is '更新時間';
comment on column ul_agent_payments_info.update_by is '更新人員';
comment on column ul_agent_payments_info.pic_id is '銀行卡圖片';
create index IF NOT EXISTS idx_ul_agent_payments_info_ul_agent_id_status
on ul_agent_payments_info (ul_agent_id, status);
create index IF NOT EXISTS idx_ul_agent_payments_info_bank_id
on ul_agent_payments_info (bank_id);
```
## 代理提款
```
-- VIP-1807 [BE][AGENT][GET] 代理提款記錄
CREATE SEQUENCE ul_agent_withdrawal_id;
create table IF NOT EXISTS ul_agent_withdrawal_pending
(
withdrawal_id VARCHAR NOT NULL DEFAULT TO_CHAR(now(), 'YYMMDD') ||to_char(nextval('ul_agent_withdrawal_pending_id'), '0000000FM') not null primary key,
ul_agent_id integer not null,
ul_agent_account varchar not null,
ul_agent_level integer not null,
payments_info_id uuid,
withdrawal_amt numeric(23, 6) not null,
after_balance numeric(23, 6),
withdrawal_time timestamp not null,
audit_time timestamp,
auditor varchar,
remarks varchar,
status varchar not null,
bank_payment_type integer,
paid_amt numeric(23, 6),
external_id varchar unique,
caccount_id uuid,
bank_account_info jsonb,
bank_id uuid,
agent_remarks varchar,
ip varchar not null,
location varchar not null,
create_time timestamp default clock_timestamp(),
update_time timestamp default clock_timestamp()
);
COMMENT ON TABLE ul_agent_withdrawal_pending IS '代理提款審核';
COMMENT ON COLUMN ul_agent_withdrawal_pending.withdrawal_id IS '提款訂單號';
COMMENT ON COLUMN ul_agent_withdrawal_pending.ul_agent_id IS '代理編號';
COMMENT ON COLUMN ul_agent_withdrawal_pending.ul_agent_account IS '代理帳號';
COMMENT ON COLUMN ul_agent_withdrawal_pending.ul_agent_level IS '代理層級';
COMMENT ON COLUMN ul_agent_withdrawal_pending.payments_info_id IS '代理取款銀行卡編號';
COMMENT ON COLUMN ul_agent_withdrawal_pending.withdrawal_amt IS '出款金额';
COMMENT ON COLUMN ul_agent_withdrawal_pending.after_balance IS '帐户余额';
COMMENT ON COLUMN ul_agent_withdrawal_pending.withdrawal_time IS '提款申請時間';
COMMENT ON COLUMN ul_agent_withdrawal_pending.audit_time IS '審核時間';
COMMENT ON COLUMN ul_agent_withdrawal_pending.auditor IS '審核者';
COMMENT ON COLUMN ul_agent_withdrawal_pending.remarks IS '後台備註';
COMMENT ON COLUMN ul_agent_withdrawal_pending.status IS '狀態(UlAgentWithdrawalStatus):PENDING, PROCESSING';
COMMENT ON COLUMN ul_agent_withdrawal_pending.bank_payment_type IS '提款方式(Enum:BankPaymentType):BANK(1), GCASH(16)';
COMMENT ON COLUMN ul_agent_withdrawal_pending.paid_amt IS '實際支付';
COMMENT ON COLUMN ul_agent_withdrawal_pending.external_id IS '第三方订单号';
COMMENT ON COLUMN ul_agent_withdrawal_pending.caccount_id IS '公司出款帳戶';
COMMENT ON COLUMN ul_agent_withdrawal_pending.bank_account_info IS '銀行資料(含銀行姓名)';
COMMENT ON COLUMN ul_agent_withdrawal_pending.bank_id IS '銀行代碼';
COMMENT ON COLUMN ul_agent_withdrawal_pending.agent_remarks IS '代理備註';
COMMENT ON COLUMN ul_agent_withdrawal_pending.ip IS 'IP';
COMMENT ON COLUMN ul_agent_withdrawal_pending.location IS '國家/城市';
COMMENT ON COLUMN ul_agent_withdrawal_pending.create_time IS '建立時間';
COMMENT ON COLUMN ul_agent_withdrawal_pending.update_time IS '更新時間';
create table IF NOT EXISTS ul_agent_withdrawal_sequence
(
ul_agent_id integer not null primary key,
sequence integer not null,
create_time timestamp default clock_timestamp(),
update_time timestamp default clock_timestamp()
);
COMMENT ON TABLE ul_agent_withdrawal_sequence IS '代理提款序列號';
COMMENT ON COLUMN ul_agent_withdrawal_sequence.ul_agent_id IS '代理編號';
COMMENT ON COLUMN ul_agent_withdrawal_sequence.sequence IS '提款序號';
COMMENT ON COLUMN ul_agent_withdrawal_sequence.create_time IS '建立時間';
COMMENT ON COLUMN ul_agent_withdrawal_sequence.update_time IS '更新時間';
create table IF NOT EXISTS ul_agent_withdrawal
(
withdrawal_id VARCHAR NOT NULL primary key,
ul_agent_id integer not null,
ul_agent_account varchar not null,
ul_agent_level integer not null,
payments_info_id uuid,
withdrawal_amt numeric(23, 6) not null,
after_balance numeric(23, 6),
sequence integer,
withdrawal_time timestamp not null,
audit_time timestamp,
auditor varchar,
approve_reason varchar,
remarks varchar,
decline_reason varchar,
status varchar not null,
bank_payment_type integer,
paid_amt numeric(23, 6),
external_id varchar unique,
caccount_id uuid,
bank_account_info jsonb,
bank_id uuid,
agent_remarks varchar,
ip varchar not null,
location varchar not null,
create_time timestamp default clock_timestamp(),
update_time timestamp default clock_timestamp()
);
COMMENT ON TABLE ul_agent_withdrawal IS '代理提款記錄';
COMMENT ON COLUMN ul_agent_withdrawal.withdrawal_id IS '提款訂單號';
COMMENT ON COLUMN ul_agent_withdrawal.ul_agent_id IS '代理編號';
COMMENT ON COLUMN ul_agent_withdrawal.ul_agent_account IS '代理帳號';
COMMENT ON COLUMN ul_agent_withdrawal.ul_agent_level IS '代理層級';
COMMENT ON COLUMN ul_agent_withdrawal.payments_info_id IS '代理取款銀行卡編號';
COMMENT ON COLUMN ul_agent_withdrawal.withdrawal_amt IS '出款金额';
COMMENT ON COLUMN ul_agent_withdrawal.after_balance IS '帐户余额';
COMMENT ON COLUMN ul_agent_withdrawal.sequence IS '代理出款順序，如首次出款/二次出款';
COMMENT ON COLUMN ul_agent_withdrawal.withdrawal_time IS '提款申請時間';
COMMENT ON COLUMN ul_agent_withdrawal.audit_time IS '審核時間';
COMMENT ON COLUMN ul_agent_withdrawal.auditor IS '審核者';
COMMENT ON COLUMN ul_agent_withdrawal.approve_reason IS '批准理由';
COMMENT ON COLUMN ul_agent_withdrawal.remarks IS '後台備註';
COMMENT ON COLUMN ul_agent_withdrawal.decline_reason IS '拒絕理由';
COMMENT ON COLUMN ul_agent_withdrawal.status IS '狀態(UlAgentWithdrawalStatus):REJECTED, APPROVED';
COMMENT ON COLUMN ul_agent_withdrawal.bank_payment_type IS '提款方式(Enum:BankPaymentType):BANK(1), GCASH(16)';
COMMENT ON COLUMN ul_agent_withdrawal.paid_amt IS '實際支付';
COMMENT ON COLUMN ul_agent_withdrawal.external_id IS '第三方订单号';
COMMENT ON COLUMN ul_agent_withdrawal.caccount_id IS '公司出款帳戶';
COMMENT ON COLUMN ul_agent_withdrawal.bank_account_info IS '銀行資料(含銀行姓名)';
COMMENT ON COLUMN ul_agent_withdrawal.bank_id IS '銀行代碼';
COMMENT ON COLUMN ul_agent_withdrawal.agent_remarks IS '代理備註';
COMMENT ON COLUMN ul_agent_withdrawal.ip IS 'IP';
COMMENT ON COLUMN ul_agent_withdrawal.location IS '國家/城市';
COMMENT ON COLUMN ul_agent_withdrawal.create_time IS '建立時間';
COMMENT ON COLUMN ul_agent_withdrawal.update_time IS '更新時間';
create index IF NOT EXISTS idx_ul_agent_withdrawal_audit_time
on ul_agent_withdrawal (audit_time);
create index IF NOT EXISTS idx_ul_agent_withdrawal_status_withdrawal_time
on ul_agent_withdrawal (status, withdrawal_time);
create index IF NOT EXISTS idx_ul_agent_withdrawal_create_time
on ul_agent_withdrawal (create_time);
create index IF NOT EXISTS idx_ul_agent_withdrawal_ul_agent_account
on ul_agent_withdrawal (ul_agent_account);
```
## 代理提款操作紀錄
```
-- VIP-1800 [BE][IMS][GET] 提款操作記錄
create table IF NOT EXISTS ul_agent_withdrawal_action_log
(
log_id uuid default uuid_generate_v4() not null primary key,
withdrawal_id varchar not null,
withdrawal_status varchar not null,
withdrawal_action varchar not null,
action_detail jsonb,
creator varchar not null,
creator_ip varchar not null,
create_time timestamp default clock_timestamp()
);
COMMENT ON TABLE ul_agent_withdrawal_action_log IS '提款操作記錄';
COMMENT ON COLUMN ul_agent_withdrawal_action_log.log_id IS '操作紀錄單號';
COMMENT ON COLUMN ul_agent_withdrawal_action_log.ul_agent_withdrawal_id IS '提款訂單號';
COMMENT ON COLUMN ul_agent_withdrawal_action_log.ul_agent_withdrawal_status IS '狀態(Enum:UlAgentWithdrawalStatus)';
COMMENT ON COLUMN ul_agent_withdrawal_action_log.ul_agent_withdrawal_action IS '行為(Enum:UlAgentWithdrawalActionLogType)';
COMMENT ON COLUMN ul_agent_withdrawal_action_log.action_detail IS '操作細節';
COMMENT ON COLUMN ul_agent_withdrawal_action_log.creator IS '操作人員帳號';
COMMENT ON COLUMN ul_agent_withdrawal_action_log.creator_ip IS '操作人員IP';
COMMENT ON COLUMN ul_agent_withdrawal_action_log.create_time IS '建立時間';
create index IF NOT EXISTS idx_ul_agent_withdrawal_action_log_ul_agent_withdrawal_id
on ul_agent_withdrawal_action_log (ul_agent_withdrawal_id);
```
## 佣金纪录
```
-- VIP-1803 [BE][AGENT][GET] 佣金纪录
create table IF NOT EXISTS ul_agent_wallet
(
ul_agent_id integer not null primary key,
balance numeric(23, 6) default 0.000000 not null,
pending_balance numeric(23, 6) default 0.000000 not null
);
COMMENT ON TABLE ul_agent_wallet IS '代理錢包';
COMMENT ON COLUMN ul_agent_wallet.ul_agent_id IS '代理編號';
COMMENT ON COLUMN ul_agent_wallet.balance IS '總餘額';
COMMENT ON COLUMN ul_agent_wallet.pending_balance IS '待扣餘額，balance-pending_balance = available_balance';
create table ul_agent_commission
(
commission_id uuid default uuid_generate_v4() not null primary key,
settlement_id varchar not null,
ul_agent_id integer not null,
ul_agent_account varchar not null,
ul_agent_level integer not null,
start_time timestamp with time zone not null,
end_time timestamp with time zone not null,
agent_revenue numeric not null,
actual_rebate numeric not null,
status varchar not null,
settlement_type varchar not null,
create_time timestamp with time zone default now(),
update_time timestamp with time zone,
UNIQUE (settlement_id, ul_agent_id)
);
COMMENT ON TABLE ul_agent_commission IS '佣金紀錄';
COMMENT ON COLUMN ul_agent_commission.commission_id IS '佣金編號';
COMMENT ON COLUMN ul_agent_commission.settlement_id IS '返佣結算編號';
COMMENT ON COLUMN ul_agent_commission.ul_agent_id IS '代理編號';
COMMENT ON COLUMN ul_agent_commission.ul_agent_account IS '代理帳號';
COMMENT ON COLUMN ul_agent_commission.ul_agent_level IS '代理層級';
COMMENT ON COLUMN ul_agent_commission.start_time IS '結算開始時間';
COMMENT ON COLUMN ul_agent_commission.end_time IS '結算結束時間';
COMMENT ON COLUMN ul_agent_commission.agent_revenue IS '毛返佣';
COMMENT ON COLUMN ul_agent_commission.actual_rebate IS '實際反佣';
COMMENT ON COLUMN ul_agent_commission.status IS '反佣狀態(Enum:UlAgentSettlementStatus):APPROVAL, REJECTED';
COMMENT ON COLUMN ul_agent_commission.settlement_type IS '结算周期(Enum:UlAgentSettlementType):DAILY, WEEKLY, MONTHLY';
COMMENT ON COLUMN ul_agent_commission.create_time IS '建立時間';
COMMENT ON COLUMN ul_agent_commission.update_time IS '更新時間';
create table ul_agent_commission_pending
(
commission_pending_id uuid default uuid_generate_v4() not null primary key,
settlement_id varchar not null,
ul_agent_id integer not null,
ul_agent_account varchar not null,
ul_agent_level integer not null,
start_time timestamp with time zone not null,
end_time timestamp with time zone not null,
agent_revenue numeric not null,
actual_rebate numeric not null,
settlement_type varchar not null,
create_time timestamp with time zone default now(),
update_time timestamp with time zone,
UNIQUE (settlement_id, ul_agent_id)
);
COMMENT ON TABLE ul_agent_commission_pending IS '佣金待審核紀錄';
COMMENT ON COLUMN ul_agent_commission_pending.commission_pending_id IS '返佣待審核編號';
COMMENT ON COLUMN ul_agent_commission_pending.settlement_id IS '返佣結算編號';
COMMENT ON COLUMN ul_agent_commission_pending.ul_agent_id IS '代理編號';
COMMENT ON COLUMN ul_agent_commission_pending.ul_agent_account IS '代理帳號';
COMMENT ON COLUMN ul_agent_commission_pending.ul_agent_level IS '代理層級';
COMMENT ON COLUMN ul_agent_commission_pending.start_time IS '結算開始時間';
COMMENT ON COLUMN ul_agent_commission_pending.end_time IS '結算結束時間';
COMMENT ON COLUMN ul_agent_commission_pending.agent_revenue IS '毛返佣';
COMMENT ON COLUMN ul_agent_commission_pending.actual_rebate IS '實際反佣';
COMMENT ON COLUMN ul_agent_commission_pending.settlement_type IS '结算周期(Enum:UlAgentSettlementType):DAILY, WEEKLY, MONTHLY';
COMMENT ON COLUMN ul_agent_commission_pending.create_time IS '建立時間';
COMMENT ON COLUMN ul_agent_commission_pending.update_time IS '更新時間';
```
### 實際返佣編輯歷史
```
create table IF NOT EXISTS ul_agent_commission_audit
(
audit_id uuid default public.uuid_generate_v4() not null primary key,
settlement_id varchar not null,
ul_agent_id integer not null,
origin_amt numeric,
after_amt numeric,
remark varchar,
create_time timestamp default clock_timestamp(),
creator varchar not null
);
COMMENT ON TABLE ul_agent_commission_audit IS '實際返佣編輯歷史';
COMMENT ON COLUMN ul_agent_commission_audit.audit_id IS '編輯歷史編號';
COMMENT ON COLUMN ul_agent_commission_audit.settlement_id IS '返佣結算編號';
COMMENT ON COLUMN ul_agent_commission_audit.ul_agent_id IS '代理編號';
COMMENT ON COLUMN ul_agent_commission_audit.origin_amt IS '原本金額';
COMMENT ON COLUMN ul_agent_commission_audit.after_amt IS '修改後金額';
COMMENT ON COLUMN ul_agent_commission_audit.remark IS '備註';
COMMENT ON COLUMN ul_agent_commission_audit.create_time IS '建立時間';
COMMENT ON COLUMN ul_agent_commission_audit.creator IS '建立人員';
```
## [IMS] 角色權限設定
```
-- VIP-1794 [BE][IMS][GET][PUT] 角色權限設定
-- 开给 SuperAdmin 及 Root 的權限：读、写、汇出
UPDATE userrole
SET permissions = permissions || '
{
"UlAgent Withdrawal": 4,"UlAgent Withdrawal Pending": 4,"UlAgent Commission": 4
}',
updatedate = now(),
updateby = '_flyway'
WHERE rolename in ('Super Admin', 'Root');
-- 开给 AE_ADMIN 的權限：读、汇出
UPDATE userrole
SET permissions = permissions || '
{
"UlAgent Withdrawal": 3,"UlAgent Withdrawal Pending": 3,"UlAgent Commission": 3
}',
updatedate = now(),
updateby = '_flyway'
WHERE rolename = 'AE_ADMIN';
-- 开给 AE_OP 及 AE_SUPPORT 的權限：读
UPDATE userrole
SET permissions = permissions || '
{
"UlAgent Withdrawal": 1,"UlAgent Withdrawal Pending": 1,"UlAgent Commission": 1
}',
updatedate = now(),
updateby = '_flyway'
WHERE rolename in ('AE_OP', 'AE_SUPPORT');
-- Insert new funcname 'UlAgent Withdrawal' to funccategoryindex 'UlAgent Management'
INSERT INTO function (funcname, funccategory, funccategoryindex, status, createdate, createby, funcitemindex)
VALUES ('UlAgent Withdrawal', 'UlAgent Management', 12, 1, now(), '_flyway', 14),
('UlAgent Withdrawal Pending', 'UlAgent Management', 12, 1, now(), '_flyway', 15),
('UlAgent Commission', 'UlAgent Management', 12, 1, now(), '_flyway', 16)
ON CONFLICT DO NOTHING;
--代理錢包功能
WITH temp AS (
SELECT rolename,
unnest(array ['ULAGENT_BANKCARD_ADD','ULAGENT_BANKCARD_EDIT_INCLUDE_STATUS']) as authority
FROM userrole
WHERE permissions -> 'UlAgent Withdrawal' in ('2', '4')
)
INSERT INTO cms_user_role_authority (rolename, authority)
SELECT temp.rolename, temp.authority
From temp;
```
[AgentSite]
# 資金密碼
```
-- VIP-1805 [BE][AGENT][GET][PUT] 資金密碼
ALTER TABLE ul_agent ADD pin varchar;
COMMENT ON COLUMN ul_agent.pin IS '代理資金密碼';
ALTER TABLE ul_agent ADD pin_update_time timestamp;
COMMENT ON COLUMN ul_agent.pin_update_time IS 'pin修改時間';
ALTER TABLE ul_agent ADD default_bank_payment_type integer;
COMMENT ON COLUMN ul_agent.default_bank_payment_type IS '提款方式(Enum:BankPaymentType):BANK(1), GCASH(16)';
ALTER TABLE ul_agent ADD default_payment_id uuid;
COMMENT ON COLUMN ul_agent.default_payment_id IS '預設提款銀行卡、GCASH';
```
Sign in with Wallet
Connect another wallet