---
# System prepended metadata

title: 代理錢包 SQL INDEX 討論

---

# 代理錢包 SQL INDEX 討論

## 2022-10-20 會議討論後調整內容:
### 1. 無調整
* ul_agent_payments_info 
* ul_agent_withdrawal_action_log
* ul_agent_commission_audit
* ul_agent_wallet 
### 2. ul_agent_withdrawal
index 建議: ul_agent_id, withdrawal_time
index 建議: bank_id

create index IF NOT EXISTS idx_ul_agent_withdrawal_ul_agent_id_withdrawal_time
    on ul_agent_withdrawal (ul_agent_id, withdrawal_time);

create index IF NOT EXISTS idx_ul_agent_withdrawal_bank_id
    on ul_agent_withdrawal (bank_id);

create index IF NOT EXISTS idx_ul_agent_withdrawal_audit_time
    on ul_agent_withdrawal (status, 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_ul_agent_account
    on ul_agent_withdrawal (ul_agent_account);

### 3. ul_agent_withdrawal_pending
index建議: bank_id
-- index建議: withdrawal_time: 預期此表資料量較小，暫不添加
-- index建議: audit_time: 預期此表資料量較小，暫不添加

create index IF NOT EXISTS idx_ul_agent_withdrawal_pending_bank_id
    on ul_agent_withdrawal_pending (bank_id);

### 4. ul_agent_commission_pending
index 建議: (start_time, end_time)
index 建議: (settlement_id,settlement_type)
index 建議: UNIQUE (settlement_id, ul_agent_id) 改成  UNIQUE INDEX (ul_agent_id,settlement_id)

create index IF NOT EXISTS idx_ul_agent_commission_pending_start_time_end_time
	on ul_agent_commission_pending (start_time, end_time);

create index IF NOT EXISTS idx_ul_agent_commission_pending_settlement_id_settlement_type
	on ul_agent_commission_pending (settlement_id, settlement_type);

CREATE UNIQUE INDEX IF NOT EXISTS idx_ul_agent_commission_pending_ul_agent_id_settlement_id
    ON ul_agent_commission_pending (ul_agent_id, settlement_id);


### 5. ul_agent_commission
index 建議: (start_time, end_time)
index 建議: settlement_type
index 建議: UNIQUE (settlement_id, ul_agent_id) 改成  UNIQUE INDEX (ul_agent_id,settlement_id)

create index IF NOT EXISTS idx_ul_agent_commission_start_time_end_time
	on ul_agent_commission (start_time, end_time);

create index IF NOT EXISTS idx_ul_agent_commission_settlement_id_settlement_type
	on ul_agent_commission (settlement_id, settlement_type);

CREATE UNIQUE INDEX IF NOT EXISTS idx_ul_agent_commission_ul_agent_id_settlement_id
    ON ul_agent_commission (ul_agent_id, settlement_id);

### 6.  其他:
* balance 統一限制小數點位數 numeric(23, 6)
* create_time timestamp with time zone default now(), 改成我們統一的 default clock_timestamp()
* ul_agent_commission_pending, ul_agent_commission => 兩表相關連，因此欄位相同維持一致性
* where 條件優先置入 union all 之中


## 編輯銀行卡 ul_agent_payments_info
* table
```=sql=
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(),
    creator           varchar                             not null,
    update_time         timestamp                              default clock_timestamp(),
    updater           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.creator is '建立人員';
comment on column ul_agent_payments_info.update_time is '更新時間';
comment on column ul_agent_payments_info.updater 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);
```
* dml
```=sql=
<entry key="ulagent_exist_by_ulagentid">
    SELECT count(1)
    FROM ul_agent
    WHERE id = :ulagentId
  </entry>
  <entry key="ulagentpaymentsinfo_find_count_by_paymentsinfoid_and_bankaccount">
    SELECT count(1)
    FROM ul_agent_payments_info
    WHERE (:paymentsInfoIdIsNull = true OR payments_info_id != :paymentsInfoId)
            AND (:bankIdIsNull = true OR bank_id = :bankId)
            AND ltrim(bank_account, '0') = ltrim(:bankAccount, '0')
            AND status != :deleteStatus
  </entry>
  <entry key="ulagentpaymentsinfo_find_count_by_ulagentid">
    SELECT count(1)
    FROM ul_agent_payments_info p
    WHERE p.ul_agent_id = :ulagentId
        AND p.status != :deleteStatus
  </entry>
  <entry key="ulagentpaymentsinfo_find_detail_by_ulagentid">
        SELECT * FROM (
            SELECT
                p.payments_info_id AS paymentsInfoId,
                p.ul_agent_id AS ulagentId,
                p.bank_id AS bankId,
                p.bank_branch AS bankBranch,
                p.bank_account AS bankAccount,
                p.bank_account_name AS bankAccountName,
                p.status AS status,
                p.activation_province AS activationProvince,
                p.activation_city AS activationCity,
                p.activation_mobile AS activationMobile,
                p.activation_id AS activationId,
                p.activation_email AS activationEmail,
                p.create_time AS createTime,
                p.creator AS creator,
                p.update_time AS updateTime,
                p.updater AS updater,
                p.pic_id AS picId,
                b.currency AS currency,
                CASE WHEN :language = :enLanguage THEN b.banknameen WHEN :language = :scLanguage THEN b.banknamesc ELSE b.banknametc END AS bankName
            FROM ul_agent_payments_info p INNER JOIN bank b ON p.bank_id = b.bankid
            WHERE p.ul_agent_id = :ulagentId
                AND p.status != :deleteStatus
        ) paymentjoin
        ORDER BY
        (case when :sort='DESC' and :sortColumn='BANK_NAME' then bankName end) DESC,
        (case when :sort='DESC' and :sortColumn='BANK_BRANCH' then bankBranch end) DESC,
        (case when :sort='DESC' and :sortColumn='BANK_ACCOUNT' then bankAccount end) DESC,
        (case when :sort='DESC' and :sortColumn='BANK_ACCOUNT_NAME' then bankAccountName end) DESC,
        (case when :sort='DESC' and :sortColumn='ACTIVATION_PROVINCE' then activationProvince end) DESC,
        (case when :sort='DESC' and :sortColumn='ACTIVATION_MOBILE' then activationMobile end) DESC,
        (case when :sort='DESC' and :sortColumn='ACTIVATION_ID' then activationId end) DESC,
        (case when :sort='DESC' and :sortColumn='CREATE_TIME' then createTime end) DESC,
        (case when :sort='DESC' and :sortColumn='UPDATE_TIME' then updateTime end) DESC,
        (case when :sort='DESC' and :sortColumn='UPDATER' then updater end) DESC,
        (case when :sort='ASC' and :sortColumn='BANK_NAME' then bankName end) ASC,
        (case when :sort='ASC' and :sortColumn='BANK_BRANCH' then bankBranch end) ASC,
        (case when :sort='ASC' and :sortColumn='BANK_ACCOUNT' then bankAccount end) ASC,
        (case when :sort='ASC' and :sortColumn='BANK_ACCOUNT_NAME' then bankAccountName end) ASC,
        (case when :sort='ASC' and :sortColumn='ACTIVATION_PROVINCE' then activationProvince end) ASC,
        (case when :sort='ASC' and :sortColumn='ACTIVATION_MOBILE' then activationMobile end) ASC,
        (case when :sort='ASC' and :sortColumn='ACTIVATION_ID' then activationId end) ASC,
        (case when :sort='ASC' and :sortColumn='CREATE_TIME' then createTime end) ASC,
        (case when :sort='ASC' and :sortColumn='UPDATE_TIME' then updateTime end) ASC,
        (case when :sort='ASC' and :sortColumn='UPDATER' then  updater end) ASC,
        status ASC, updateTime ASC
        OFFSET :offset LIMIT :limit
  </entry>
    <entry key="ulagentpaymentsinfo_find_by_paymentsinfoid">
        SELECT payments_info_id AS paymentsInfoId,
            ul_agent_id AS ulagentId,
            bank_id AS bankId,
            bank_branch AS bankBranch,
            bank_account AS bankAccount,
            bank_account_name AS bankAccountName,
            status AS status,
            activation_province AS activationProvince,
            activation_city AS activationCity,
            activation_mobile AS activationMobile,
            activation_id AS activationId,
            activation_email AS activationEmail,
            create_time AS createTime,
            creator AS creator,
            update_time AS updateTime,
            updater AS updater,
            pic_id AS picId
        FROM ul_agent_payments_info
        WHERE payments_info_id = :paymentsInfoId
    </entry>
    <entry key="ulagentpaymentsinfo_save">
    INSERT INTO ul_agent_payments_info (ul_agent_id, bank_id, bank_branch, bank_account, bank_account_name, status, create_time, creator, update_time, updater, activation_province, activation_city, activation_mobile, activation_email, activation_id, pic_id)
        VALUES (:ulagentId, :bankId, :bankBranch, :bankAccount, :bankAccountName, :status, clock_timestamp(), :creator,  clock_timestamp(), :updater, :activationProvince, :activationCity, :activationMobile, :activationEmail, :activationId, :picId)
  </entry>
    <entry key="ulagentpaymentsinfo_update_by_paymentsinfoid">
        UPDATE ul_agent_payments_info
        SET bank_id = :bankId, bank_branch = :bankBranch, bank_account = :bankAccount, bank_account_name = :bankAccountName, activation_province = :activationProvince, activation_city = :activationCity, activation_mobile = :activationMobile, activation_email = :activationEmail, activation_id = :activationId, pic_id = :picId, update_time = clock_timestamp(), updater = :updater
        WHERE payments_info_id =:paymentsInfoId
    </entry>
    <entry key="ulagentpaymentsinfo_update_status_by_paymentsinfoid">
    UPDATE ul_agent_payments_info
    SET status = :status, update_time = clock_timestamp(), updater = :updater
        WHERE payments_info_id =:paymentsInfoId
  </entry>
     <entry key="ul_agent_payments_info_find_one">
        SELECT uapi.bank_account AS account,uapi.bank_account_name AS accountName, status AS paymentStatusEnum
        FROM ul_agent_payments_info uapi
        WHERE uapi.ul_agent_id =:ulAgentId
        ORDER BY status,update_time DESC
        LIMIT 1
    </entry>

    <entry key="ul_agent_payments_info_find_all">
        SELECT uapi.bank_account AS account,uapi.bank_account_name AS accountName,status AS paymentStatusEnum
        FROM ul_agent_payments_info uapi
        WHERE uapi.ul_agent_id =:ulAgentId
        ORDER BY status,update_time DESC
    </entry>
```

## 提款審核 ul_agent_withdrawal_pending,  ul_agent_withdrawal_sequence

* table

```=sql=
-- VIP-1807 [BE][AGENT][GET] 代理提款記錄
CREATE SEQUENCE if not exists ul_agent_withdrawal_id;

-- initializing of withdraw serial id
SELECT setval('ul_agent_withdrawal_id', (TO_NUMBER(TO_CHAR(current_date, 'YYMMDD')||'00000000','9999999999999FM'))::bigint);

create table IF NOT EXISTS ul_agent_withdrawal_pending
(
    withdrawal_id           VARCHAR NOT NULL DEFAULT
        CASE WHEN TO_NUMBER(TO_CHAR(current_date + interval '1 d', 'YYMMDD')||'00000000','9999999999999FM') = CURRVAL('ul_agent_withdrawal_id') + 1 -- overflow
            THEN NULL
        WHEN TO_NUMBER(TO_CHAR(current_date, 'YYMMDD')||'0000001','9999999999999FM') > CURRVAL('ul_agent_withdrawal_id') -- 跨日更新
            THEN SETVAL('ul_agent_withdrawal_id', TO_NUMBER(TO_CHAR(current_date, 'YYMMDD')||'0000001','9999999999999FM')::bigint)
        ELSE
            NEXTVAL('ul_agent_withdrawal_id')
        END
    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)';
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 '更新時間';


```
* dml
```=sql=
 <entry key="ul_agent_withdrawal_pending_data_move_to_ul_agent_withdrawal_by_withdrawal_id">
        WITH del AS (
            DELETE FROM ul_agent_withdrawal_pending
            WHERE withdrawal_id = :withdrawalId
              AND status = :ulAgentWithdrawalProcessingStatus
              AND auditor = :auditor
            RETURNING *
        )
        INSERT INTO ul_agent_withdrawal(withdrawal_id, ul_agent_id, ul_agent_account, ul_agent_level, payments_info_id, withdrawal_amt, after_balance, sequence, withdrawal_time,
                                        audit_time, auditor, approve_reason, remarks, decline_reason, status, bank_payment_type, paid_amt, external_id, caccount_id,
                                        bank_account_info, bank_id, agent_remarks, ip, location, create_time, update_time)
        SELECT withdrawal_id, ul_agent_id, ul_agent_account, ul_agent_level, payments_info_id, withdrawal_amt, after_balance, :sequence, withdrawal_time,
               CLOCK_TIMESTAMP(), :auditor, :approveReason, :remarks, :declineReason, :status, bank_payment_type, :paidAmt, external_id, :caccountId,
               bank_account_info, bank_id, agent_remarks, ip, location, create_time, CLOCK_TIMESTAMP()
        FROM del
    </entry>
 <entry key="ul_agent_withdrawal_pending_status_update_by_withdrawal_id">
        UPDATE ul_agent_withdrawal_pending
        SET status = :status, auditor = :auditor, update_time = CLOCK_TIMESTAMP()
        WHERE withdrawal_id = :withdrawalId
          AND status = CASE WHEN :status = :ulAgentWithdrawalPendingStatus THEN :ulAgentWithdrawalProcessingStatus ELSE :ulAgentWithdrawalPendingStatus END
          AND CASE WHEN :status = :ulAgentWithdrawalPendingStatus THEN auditor = :updateBy ELSE auditor IS NULL END
    </entry>
    <entry key="ul_agent_withdrawal_sequence_upsert_by_ul_agent_id">
        INSERT INTO ul_agent_withdrawal_sequence as us (ul_agent_id, sequence)
        VALUES(:ulAgentId, 1)
        ON CONFLICT (ul_agent_id) DO UPDATE
            SET sequence = us.sequence + 1, update_time = CLOCK_TIMESTAMP()
        RETURNING sequence;
    </entry>
    <entry key="ul_agent_wallet_query_by_ul_agent_id">
        SELECT ul_agent_id as ulAgentId, balance, pending_balance as pendingBalance
        FROM ul_agent_wallet
        WHERE ul_agent_id = :ulAgentId;
    </entry>
    <entry key="ul_agent_withdrawal_pending_query_by_withdrawal_id">
        SELECT withdrawal_id as withdrawalId, ul_agent_id as ulAgentId, ul_agent_account as ulAgentAccount, ul_agent_level as ulAgentLevel, payments_info_id as paymentsInfoId,
               withdrawal_amt as withdrawalAmt, after_balance as afterBalance, withdrawal_time as withdrawalTime, audit_time as auditTime, auditor, remarks, status,
               bank_payment_type as bankPaymentType, paid_amt as paidAmt, external_id as externalId, caccount_id as caccountId, bank_account_info as bankAccountInfo, bank_id as bankId,
               agent_remarks as agentRemarks, ip, location, create_time as createTime, update_time as updateTime
        FROM ul_agent_withdrawal_pending
        WHERE withdrawal_id = :withdrawalId;
    </entry>
    <entry key="ul_agent_wallet_update_by_approve_by_ul_agent_id">
        UPDATE ul_agent_wallet uw
        SET balance = balance - uw.pending_balance, pending_balance = pending_balance - uw.pending_balance
        WHERE ul_agent_id = :ulAgentId
          AND balance = :balance
          AND pending_balance = :pendingBalance;
    </entry>
    <entry key="ul_agent_wallet_update_by_reject_by_ul_agent_id">
        UPDATE ul_agent_wallet uw
        SET pending_balance = pending_balance - uw.pending_balance
        WHERE ul_agent_id = :ulAgentId
          AND balance = :balance
          AND pending_balance = :pendingBalance;
    </entry>

     <entry key="ul_agent_withdrawal_pending_agent_remarks_update_by_withdrawal_id">
        UPDATE ul_agent_withdrawal_pending
        SET agent_remarks = :agentRemarks
        WHERE withdrawal_id = :withdrawalId
    </entry>
     <entry key="ul_agent_withdrawal_pending_insert">
        INSERT INTO public.ul_agent_withdrawal_pending
        (ul_agent_id, ul_agent_account, ul_agent_level, payments_info_id, withdrawal_amt, withdrawal_time, status, bank_account_info, bank_id, ip, "location")
        VALUES(:ulAgentId, :ulAgentAccount, :ulAgentLevel, :paymentsInfoId, :withdrawalAmt, CLOCK_TIMESTAMP(), :status, CAST(:bankAccountInfo AS jsonb), :bankId, :ip, :location);
    </entry>
     
```
```=sql=
    <entry key="ul_agent_withdrawal_pending_find">
        SELECT w.withdrawal_id AS withdrawalId,
            w.ul_agent_id AS ulAgentId,
            w.ul_agent_account AS ulAgentAccount,
            w.ul_agent_level AS ulAgentLevel,
            w.payments_info_id AS paymentsInfoId,
            w.withdrawal_amt AS withdrawalAmt,
            w.after_balance AS afterBalance,
            w.withdrawal_time AS withdrawalTime,
            w.audit_time AS auditTime,
            w.auditor AS auditor,
            w.remarks AS remarks,
            w.status AS status,
            w.bank_payment_type AS bankPaymentType,
            w.paid_amt AS paidAmt,
            w.external_id AS externalId,
            w.caccount_id AS caccountId,
            w.bank_account_info AS bankAccountInfo,
            w.bank_account_info->>'bankAccountName' AS bankAccountName,
            w.bank_id AS bankId,
            w.agent_remarks AS agentRemarks,
            w.ip AS ip,
            w.location AS location,
            w.create_time AS createTime,
            w.update_time AS updateTime,
            CASE WHEN :lang = :sclanguage THEN b.banknamesc WHEN :lang = :tclanguage THEN b.banknametc ELSE b.banknameen END AS bankname
        FROM ul_agent_withdrawal_pending w
        LEFT JOIN bank b ON w.bank_id = b.bankid
        WHERE (CAST(:searchField AS varchar) IS NULL OR CAST(:searchValue AS varchar) IS NULL OR
            (CASE WHEN :searchField = 'UL_AGENT_ACCOUNT' THEN w.ul_agent_account = :searchValue
            WHEN :searchField = 'AUDITOR' THEN w.auditor = :searchValue
            WHEN :searchField = 'BANK_NAME' THEN (CASE WHEN :lang = :sclanguage THEN b.banknamesc WHEN :lang = :tclanguage THEN b.banknametc ELSE b.banknameen END) = :searchValue
            WHEN :searchField = 'WITHDRAWAL_ID' THEN w.withdrawal_id = :searchValue
            WHEN :searchField = 'EXTERNAL_ID' THEN w.external_id = :searchValue END))
        AND (CASE WHEN :timeFilter = 'WITHDRAWAL_TIME' THEN w.withdrawal_time >= :startTime AND :endTime >= w.withdrawal_time
            WHEN :timeFilter = 'AUDIT_TIME' THEN w.audit_time >= :startTime AND :endTime >= w.audit_time END)
        AND (CAST(:status AS varchar) IS NULL OR w.status = :status)
        ORDER BY
            (CASE WHEN :sort='DESC' AND :sortColumn='WITHDRAWAL_TIME' THEN w.withdrawal_time END) DESC,
            (CASE WHEN :sort='ASC' AND :sortColumn='WITHDRAWAL_TIME' THEN w.withdrawal_time END) ASC
        OFFSET :offset LIMIT :limit
    </entry>
    <entry key="ul_agent_withdrawal_pending_count">
        SELECT COUNT(1)
        FROM ul_agent_withdrawal_pending w
        LEFT JOIN bank b ON w.bank_id = b.bankid
        WHERE (CAST(:searchField AS varchar) IS NULL OR CAST(:searchValue AS varchar) IS NULL OR
            (CASE WHEN :searchField = 'UL_AGENT_ACCOUNT' THEN w.ul_agent_account = :searchValue
            WHEN :searchField = 'AUDITOR' THEN w.auditor = :searchValue
            WHEN :searchField = 'BANK_NAME' THEN
            (CASE WHEN :lang = :sclanguage THEN b.banknamesc WHEN :lang = :tclanguage THEN b.banknametc ELSE b.banknameen END) = :searchValue
            WHEN :searchField = 'WITHDRAWAL_ID' THEN w.withdrawal_id = :searchValue
            WHEN :searchField = 'EXTERNAL_ID' THEN w.external_id = :searchValue END))
        AND (CASE WHEN :timeFilter = 'WITHDRAWAL_TIME' THEN w.withdrawal_time >= :startTime AND :endTime >= w.withdrawal_time
            WHEN :timeFilter = 'AUDIT_TIME' THEN w.audit_time >= :startTime AND :endTime >= w.audit_time END)
        AND (CAST(:status AS varchar) IS NULL OR w.status = :status)
    </entry>
```
## 提款紀錄 ul_agent_withdrawal
* table
```=sql=
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)';
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);
```

* dml
```=sql=
      <entry key="ul_agent_withdrawal_agent_remarks_update_by_withdrawal_id">
        UPDATE ul_agent_withdrawal
        SET agent_remarks = :agentRemarks
        WHERE withdrawal_id = :withdrawalId
    </entry>
```
## 提款紀錄查詢
```=sql=
    <entry key="ul_agent_wallet_find">
        SELECT balance-pending_balance AS balance,point,pin,pin_update_time AS pinUpdateTime
        FROM ul_agent_wallet uaw
        JOIN ul_agent ua ON uaw.ul_agent_id =ua.id
        WHERE ul_agent_id =:ulAgentId
    </entry>
 

    <entry key="ul_agent_find_agentId_and_pin">
        SELECT * FROM ul_agent WHERE id=:ulAgentId AND pin=:pin
    </entry>

    <entry key="ul_agent_withdrawal_pending_exist">
        SELECT EXISTS(SELECT 1 FROM ul_agent_withdrawal_pending WHERE ul_agent_id=:ulAgentId)
    </entry>
<entry key="ul_agentSite_withdrawal_find">
        SELECT * FROM(
            SELECT  w.withdrawal_id AS withdrawalId,
                    w.withdrawal_amt AS withdrawalAmt,
                    w.withdrawal_time AS withdrawalTime,
                    w.audit_time AS auditTime,
                    w.remarks AS remarks,
                    w.status AS status
            FROM 	ul_agent_withdrawal w
            WHERE 	ul_agent_id = :ulAgentId
                    AND w.withdrawal_time >= :start
                    AND :end > w.withdrawal_time
        UNION ALL
            SELECT  wp.withdrawal_id AS withdrawalId,
                    wp.withdrawal_amt AS withdrawalAmt,
                    wp.withdrawal_time AS withdrawalTime,
                    wp.audit_time AS auditTime,
                    wp.remarks AS remarks,
                    wp.status AS status
            FROM 	ul_agent_withdrawal_pending wp
            WHERE 	ul_agent_id = :ulAgentId
                    AND wp.withdrawal_time >= :start
                    AND :end > wp.withdrawal_time
        ) wwp
        ORDER BY status = 'PENDING' desc,withdrawalTime DESC
        OFFSET :offset LIMIT :limit;
    </entry>
    <entry key="ul_agentSite_withdrawal_count">
        SELECT COUNT(1)
        FROM  (
            SELECT  w.withdrawal_id AS withdrawalId
            FROM 	ul_agent_withdrawal w
            WHERE 	ul_agent_id = :ulAgentId
                    AND w.withdrawal_time >= :start
                    AND :end > w.withdrawal_time
        UNION ALL
            SELECT  wp.withdrawal_id AS withdrawalId
            FROM 	ul_agent_withdrawal_pending wp
            WHERE 	ul_agent_id = :ulAgentId
                    AND wp.withdrawal_time >= :start
                    AND :end > wp.withdrawal_time
        ) wwp;
    </entry>
```

```=sql=
 <entry key="ul_agent_withdrawal_find">
        SELECT w.withdrawal_id AS withdrawalId,
            w.ul_agent_id AS ulAgentId,
            w.ul_agent_account AS ulAgentAccount,
            w.ul_agent_level AS ulAgentLevel,
            w.payments_info_id AS paymentsInfoId,
            w.withdrawal_amt AS withdrawalAmt,
            w.after_balance AS afterBalance,
            w.sequence AS sequence,
            w.withdrawal_time AS withdrawalTime,
            w.audit_time AS auditTime,
            w.auditor AS auditor,
            w.approve_reason AS approveReason,
            w.remarks AS remarks,
            w.decline_reason AS declineReason,
            w.status AS status,
            w.bank_payment_type AS bankPaymentType,
            w.paid_amt AS paidAmt,
            w.external_id AS externalId,
            w.caccount_id AS caccountId,
            w.bank_account_info AS bankAccountInfo,
            w.bank_id AS bankId,
            w.agent_remarks AS agentRemarks,
            w.ip AS ip,
            w.location AS location,
            w.create_time AS createTime,
            w.update_time AS updateTime,
            CASE WHEN :lang = :sclanguage THEN b.banknamesc WHEN :lang = :tclanguage THEN b.banknametc ELSE b.banknameen END AS bankname
        FROM ul_agent_withdrawal w
        LEFT JOIN bank b ON w.bank_id = b.bankid
        WHERE (CAST(:searchField AS varchar) IS NULL OR CAST(:searchValue AS varchar) IS NULL OR
            (CASE WHEN :searchField = 'UL_AGENT_ACCOUNT' THEN w.ul_agent_account = :searchValue
            WHEN :searchField = 'AUDITOR' THEN w.auditor = :searchValue
            WHEN :searchField = 'BANK_NAME' THEN (CASE WHEN :lang = :sclanguage THEN b.banknamesc WHEN :lang = :tclanguage THEN b.banknametc ELSE b.banknameen END) = :searchValue
            WHEN :searchField = 'WITHDRAWAL_ID' THEN w.withdrawal_id = :searchValue
            WHEN :searchField = 'EXTERNAL_ID' THEN w.external_id = :searchValue END))
        AND (CASE WHEN :timeFilter = 'WITHDRAWAL_TIME' THEN w.withdrawal_time >= :startTime AND :endTime >= w.withdrawal_time
            WHEN :timeFilter = 'AUDIT_TIME' THEN w.audit_time >= :startTime AND :endTime >= audit_time END)
        AND (CAST(:status AS varchar) IS NULL OR w.status = :status)
        AND (:sequence = 0 OR sequence = :sequence)
        ORDER BY
            (CASE WHEN :sort='DESC' AND :sortColumn='WITHDRAWAL_TIME' THEN w.withdrawal_time END) DESC,
            (CASE WHEN :sort='ASC' AND :sortColumn='WITHDRAWAL_TIME' THEN w.withdrawal_time END) ASC
        OFFSET :offset LIMIT :limit
    </entry>
    <entry key="ul_agent_withdrawal_count">
        SELECT COUNT(1)
        FROM ul_agent_withdrawal w
        LEFT JOIN bank b ON w.bank_id = b.bankid
        WHERE (CAST(:searchField AS varchar) IS NULL OR CAST(:searchValue AS varchar) IS NULL OR
            (CASE WHEN :searchField = 'UL_AGENT_ACCOUNT' THEN w.ul_agent_account = :searchValue
            WHEN :searchField = 'AUDITOR' THEN w.auditor = :searchValue
            WHEN :searchField = 'BANK_NAME' THEN (CASE WHEN :lang = :sclanguage THEN b.banknamesc WHEN :lang = :tclanguage THEN b.banknametc ELSE b.banknameen END) = :searchValue
            WHEN :searchField = 'WITHDRAWAL_ID' THEN w.withdrawal_id = :searchValue
            WHEN :searchField = 'EXTERNAL_ID' THEN w.external_id = :searchValue END))
        AND (CASE WHEN :timeFilter = 'WITHDRAWAL_TIME' THEN w.withdrawal_time >= :startTime AND :endTime >= w.withdrawal_time
            WHEN :timeFilter = 'AUDIT_TIME' THEN w.audit_time >= :startTime AND :endTime >= audit_time END)
        AND (CAST(:status AS varchar) IS NULL OR w.status = :status)
        AND (:sequence = 0 OR sequence = :sequence)
    </entry>

```
## 提款操作紀錄ul_agent_withdrawal_action_log
* table
```=sql=
-- 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.withdrawal_id IS '提款訂單號';
COMMENT ON COLUMN ul_agent_withdrawal_action_log.withdrawal_status IS '狀態(Enum:UlAgentWithdrawalStatus)';
COMMENT ON COLUMN ul_agent_withdrawal_action_log.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 (withdrawal_id);
```
* dml
```=sql=
<entry key="ul_agent_withdrawal_action_log_save">
        INSERT INTO ul_agent_withdrawal_action_log
            (withdrawal_id, withdrawal_status, withdrawal_action, action_detail, creator, creator_ip)
        VALUES
            (:withdrawalId, :withdrawalStatus, :withdrawalAction, CAST(:actionDetail AS JSONB), :creator, :creatorIp)
    </entry>
    <entry key="ul_agent_withdrawal_actionlog_find">
        SELECT log_id AS logId,
            withdrawal_id AS withdrawalId,
            withdrawal_status AS withdrawalStatus,
            withdrawal_action AS withdrawalAction,
            action_detail AS actionDetail,
            creator AS creator,
            creator_ip AS creatorIp,
            create_time AS createTime
        FROM ul_agent_withdrawal_action_log
        WHERE withdrawal_id = :withdrawalId
        ORDER BY
            (CASE WHEN :sort='DESC' AND :sortColumn='CREATE_TIME' THEN create_time END) DESC,
            (CASE WHEN :sort='ASC' AND :sortColumn='CREATE_TIME' THEN create_time END) ASC
    </entry>
```


## 佣金紀錄 ul_agent_commission, ul_agent_commission_pending
* table
```=sql=
-- 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 if not exists 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 INDEX (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 if not exists 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 INDEX (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 '建立人員';
```
* dml
```=sql=
 <entry key="ul_agent_commission_pending_delete">
        DELETE FROM ul_agent_commission_pending
        WHERE settlement_id = :settlement_id AND settlement_type = :settlementType
    </entry>
    <entry key="ul_agent_commission_pending_insert">
        INSERT INTO public.ul_agent_commission_pending
            (settlement_id, ul_agent_id, ul_agent_account, ul_agent_level, start_time,
            end_time, agent_revenue, actual_rebate, settlement_type, create_time, update_time)
        VALUES
            (:settlementId, :ulAgentId, :ulAgentAccount, :ulAgentLevel, :startTime,
            :endTime, :agentRevenue, :actualRebate, :settlementType, CLOCK_TIMESTAMP(), CLOCK_TIMESTAMP());
    </entry>
    <entry key="ul_agent_commission_pending_find_by_ul_agent_id">
        SELECT commission_pending_id AS commissionPendingId, settlement_id AS settlementId, ul_agent_id AS ulAgentId, ul_agent_account AS ulAgentAccount,
            ul_agent_level AS ulAgentLevel, start_time AS startTime, end_time AS endTime, agent_revenue AS agentRevenue, actual_rebate AS actualRebate,
            settlement_type AS settlementType, create_time AS createTime, update_time AS updateTime
        FROM ul_agent_commission_pending
        WHERE ul_agent_id = :agentId
        ORDER BY settlement_id DESC
    </entry>
    <entry key="ul_agent_commission_pending_find_by_ul_agent_id_find_one">
        SELECT commission_pending_id AS commissionPendingId, settlement_id AS settlementId, ul_agent_id AS ulAgentId, ul_agent_account AS ulAgentAccount,
            ul_agent_level AS ulAgentLevel, start_time AS startTime, end_time AS endTime, agent_revenue AS agentRevenue, actual_rebate AS actualRebate,
            settlement_type AS settlementType, create_time AS createTime, update_time AS update_time
        FROM ul_agent_commission_pending
        WHERE ul_agent_id = :agentId
        ORDER BY settlement_id DESC
        LIMIT 1
    </entry>
    <entry key="ul_agent_commission_find_by_start_time_end_time_settlement_type">
        SELECT COUNT(1) from ul_agent_commission
        WHERE end_time > :startTime AND :endTime > start_time
            AND settlement_type = :type
    </entry>
    <entry key="ul_agent_commission_pending_delete_by_commission_pending_id">
        DELETE FROM ul_agent_commission_pending
        WHERE commission_pending_id = ANY (:commissionPendingId)
    </entry>
    <entry key="ul_agent_commission_insert">
        INSERT INTO public.ul_agent_commission
            (commission_id, settlement_id, ul_agent_id, ul_agent_account, ul_agent_level,
            start_time, end_time, agent_revenue, actual_rebate, status, settlement_type, create_time, update_time)
        VALUES
            (:commissionId, :settlementId, :ulAgentId, :ulAgentAccount, :ulAgentLevel,
            :startTime, :endTime, :agentRevenue, :actualRebate, :status, :settlementType, :createTime, CLOCK_TIMESTAMP())
    </entry>
    <entry key="ul_agent_wallet_upsert_by_ul_agent_id">
        INSERT INTO public.ul_agent_wallet
            (ul_agent_id, balance, pending_balance)
        VALUES
            (:ulAgentId, :balance, :pendingBalance)
        ON CONFLICT (ul_agent_id)
            DO UPDATE SET balance = ul_agent_wallet.balance + :balance
    </entry>
    <entry key="ul_agent_commission_audit_select_by_ul_agent_id_settlement_id">
        SELECT audit_id AS auditId, settlement_id AS settlementId, ul_agent_id AS ulAgentId, origin_amt AS originAmt,
            after_amt AS afterAmt, remark AS remark, create_time AS createTime, creator AS creator
        FROM ul_agent_commission_audit
        WHERE ul_agent_id = :ulAgentId
            AND settlement_id = :settlementId
        ORDER BY create_time DESC
    </entry>
    <entry key="ul_agent_commission_pending_update_by_commission_pending_id">
        UPDATE ul_agent_commission_pending
        SET actual_rebate = :actualRebate, update_time = CLOCK_TIMESTAMP()
        WHERE commission_pending_id = :commissionPendingId
    </entry>
    <entry key="ul_agent_commission_audit_insert">
        INSERT INTO public.ul_agent_commission_audit
            (settlement_id, ul_agent_id, origin_amt, after_amt, remark, create_time, creator)
        VALUES
            (:settlementId, :ulAgentId, :originAmt, :afterAmt, :remark, CLOCK_TIMESTAMP(), :creator)
    </entry>
```

## 佣金記錄查詢類

```=sql=
 <entry key="ul_agent_commission_pending_find_lastest">
        SELECT ul_agent_account AS account,ul_agent_level AS level,start_time AS start,end_time AS end,agent_revenue AS revenue,actual_rebate AS rebate,'PENDING' AS status,ul_agent_id AS ulAgentId,settlement_id AS settlementId
        FROM ul_agent_commission_pending
        WHERE ul_agent_id=:ulAgentId
        ORDER BY settlement_id DESC
        LIMIT 1
    </entry>

    <entry key="ul_agent_commission_find">
        SELECT * FROM
        (SELECT ul_agent_account AS account,ul_agent_level AS level,start_time AS start,end_time AS end,
            agent_revenue AS revenue,actual_rebate AS rebate,status,ul_agent_id AS ulAgentId,settlement_id AS settlementId,
            settlement_type as settlementType, ul_agent_level as ulAgentLevel, ul_agent_account as ulAgentAccount
        FROM ul_agent_commission
        WHERE end_time > :start AND :end > start_time
        UNION ALL
        SELECT ul_agent_account AS account,ul_agent_level AS level,start_time AS start,end_time AS end,
            agent_revenue AS revenue,actual_rebate AS rebate,'PENDING',ul_agent_id AS ulAgentId,settlement_id AS settlementId,
            settlement_type as settlementType, ul_agent_level as ulAgentLevel, ul_agent_account as ulAgentAccount
        FROM ul_agent_commission_pending
        WHERE end_time > :start AND :end > start_time
        ) foo
        WHERE (CAST (:status AS VARCHAR) IS NULL OR status = :status)
            AND settlementType = :settlementType
            AND (CAST (:agentLevel AS INTEGER) = 0 OR ulAgentLevel = :agentLevel)
            AND (CAST (:agentAccount AS VARCHAR) IS NULL OR ulAgentAccount = :agentAccount)
        ORDER BY ulAgentId,array_position(array['PENDING','APPROVAL','REJECTED'], ''||status),settlementId DESC
        OFFSET :offset
        LIMIT :limit
    </entry>
    <entry key="ul_agent_commission_find_all">
        SELECT * FROM
        (SELECT ul_agent_account AS account,ul_agent_level AS level,start_time AS start,end_time AS end,
            agent_revenue AS revenue,actual_rebate AS rebate,status,ul_agent_id AS ulAgentId,settlement_id AS settlementId,
            settlement_type as settlementType, ul_agent_level as ulAgentLevel, ul_agent_account as ulAgentAccount
        FROM ul_agent_commission
        WHERE end_time > :start AND :end > start_time
        UNION ALL
        SELECT ul_agent_account AS account,ul_agent_level AS level,start_time AS start,end_time AS end,
            agent_revenue AS revenue,actual_rebate AS rebate,'PENDING',ul_agent_id AS ulAgentId,settlement_id AS settlementId,
            settlement_type as settlementType, ul_agent_level as ulAgentLevel, ul_agent_account as ulAgentAccount
        FROM ul_agent_commission_pending
        WHERE end_time > :start AND :end > start_time
        ) foo
        WHERE (CAST (:status AS VARCHAR) IS NULL OR status = :status)
            AND settlementType = :settlementType
            AND (CAST (:agentLevel AS INTEGER) = 0 OR ulAgentLevel = :agentLevel)
            AND (CAST (:agentAccount AS VARCHAR) IS NULL OR ulAgentAccount = :agentAccount)
        ORDER BY ulAgentId,array_position(array['PENDING','APPROVAL','REJECTED'], ''||status),settlementId DESC
    </entry>
    <entry key="ul_agent_commission_count">
        SELECT COUNT(1) FROM
            (SELECT ul_agent_account,ul_agent_level,start_time,end_time,agent_revenue,
                actual_rebate,status,ul_agent_id,settlement_id,create_time,settlement_type
            FROM ul_agent_commission
            WHERE end_time > :start AND :end > start_time
            UNION ALL
            SELECT ul_agent_account,ul_agent_level,start_time,end_time,agent_revenue,
                actual_rebate,'PENDING',ul_agent_id,settlement_id,create_time,settlement_type
            FROM ul_agent_commission_pending
            WHERE end_time > :start AND :end > start_time
            ) foo
        WHERE (CAST (:status AS VARCHAR) IS NULL OR status = :status)
            AND settlement_type = :settlementType
            AND (CAST (:agentLevel AS INTEGER) = 0 OR ul_agent_level = :agentLevel)
            AND (CAST (:agentAccount AS VARCHAR) IS NULL OR ul_agent_account = :agentAccount)
    </entry>

    <entry key="ul_agent_commission_agentview_find">
        SELECT * FROM
            (SELECT start_time AS start,end_time AS end,actual_rebate AS rebate,status,ul_agent_id AS ulAgentId,settlement_id AS settlementId FROM ul_agent_commission
            WHERE (end_time > :start AND :end > start_time) AND ul_agent_id=:agentId
            UNION ALL
            SELECT start_time AS start,end_time AS end,actual_rebate AS rebate,'PENDING',ul_agent_id AS ulAgentId,settlement_id AS settlementId FROM ul_agent_commission_pending
            WHERE (end_time > :start AND :end > start_time) AND ul_agent_id=:agentId
            ) foo
        ORDER BY array_position(array['PENDING','APPROVAL','REJECTED'], ''||status),settlementId DESC
        OFFSET :offset
        LIMIT :limit
    </entry>

    <entry key="ul_agent_commission_agentview_count">
        SELECT COUNT(1) FROM
            (SELECT start_time,end_time,actual_rebate AS rebate,status,ul_agent_id AS ulAgentId,settlement_id AS settlementId FROM ul_agent_commission
            WHERE (end_time > :start AND :end > start_time) AND ul_agent_id=:agentId
            UNION ALL
            SELECT start_time,end_time,actual_rebate AS rebate,'PENDING',ul_agent_id AS ulAgentId,settlement_id AS settlementId FROM ul_agent_commission_pending
            WHERE (end_time > :start AND :end > start_time) AND ul_agent_id=:agentId
            ) foo
    </entry>
```