---
# System prepended metadata

title: 3.48 代理钱包

---

# 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

## 代理取款銀行卡
![](https://i.imgur.com/582KR6B.png)

```
-- 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);
    
```

## 代理提款
![](https://i.imgur.com/xsZFuMA.png)

```
-- 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);
    


```
## 代理提款操作紀錄
![](https://i.imgur.com/GpZPL73.png)
```
-- 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);
```

## 佣金纪录
![](https://i.imgur.com/dAiY8Fd.png)
```
-- 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 '更新時間';
```
### 實際返佣編輯歷史
![](https://i.imgur.com/m0xnEV5.png)
```
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] 角色權限設定
![](https://i.imgur.com/3jx9qg8.png)
```
-- 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] 
# 資金密碼

![](https://i.imgur.com/WlYd6ej.png)

```
-- 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';

```