# 代理錢包 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>
```
Sign in with Wallet
Connect another wallet