# withdrawal test
- 原本的版本 查ticket content
```sql=
-- 查ticket command
select dbo.andytest_payload_origin(), dbo.andytest_groupby_origin(),dbo.andytest_count_origin()
-- create function
CREATE or replace FUNCTION dbo.andytest_payload_origin(
begin timestamp(6) with time zone,
end timestamp(6) with time zone
) RETURNS SETOF record AS $$
SELECT t."TicketId", CASE
WHEN t."TicketType" = 4001 THEN w."PlayerId"
WHEN t."TicketType" = 5003 THEN a."PlayerId"
WHEN t."TicketType" = 4006 THEN c."PlayerId"
ELSE w0."FromPlayerId"
END AS "PlayerId", CASE
WHEN t."TicketType" = 4001 THEN w."DisplayId"
WHEN t."TicketType" = 5003 THEN a."DisplayId"
WHEN t."TicketType" = 4006 THEN c."DisplayId"
ELSE w0."DisplayId"
END AS "DisplayId", t."TicketType", CASE
WHEN t."TicketType" = 4001 THEN w."GameLoginId"
WHEN t."TicketType" = 5003 THEN a."PlayerGameLoginId"
WHEN t."TicketType" = 4006 THEN c."GameLoginId"
WHEN w0."TicketType" = 6000 THEN w0."FromAffiliateDisplayId"
ELSE w0."FromGameLoginId"
END AS "WalletId", CASE
WHEN t."TicketType" = 4001 THEN w."CreatedDate"
WHEN t."TicketType" = 5003 THEN a."CreatedDate"
WHEN t."TicketType" = 4006 THEN c."CreatedDate"
ELSE w0."CreatedDate"
END AS "CreatedDate", CASE
WHEN t."TicketType" = 4001 THEN w."RequestAmount"
WHEN t."TicketType" = 5003 THEN a."RequestAmount"
WHEN t."TicketType" = 4006 THEN c."RequestFiatAmount"
ELSE w0."RequestAmount"
END AS "RequestAmount", CASE
WHEN t."TicketType" = 4001 THEN w."ActualAmount"
WHEN t."TicketType" = 5003 THEN a."ActualAmount"
WHEN t."TicketType" = 4006 THEN c."ActualFiatAmount"
ELSE w0."ActualAmount"
END AS "ActualAmount", CASE
WHEN t."TicketType" = 4001 THEN w."Fee"
WHEN (a."AffiliateWithdrawalTransactionId" IS NOT NULL) THEN a."Fee"
ELSE 0.0
END AS "Fee", t0."TeamId" AS "CurrentStepTeamId", CASE
WHEN t."TicketType" = 4001 THEN w."Status"
WHEN t."TicketType" = 5003 THEN a."Status"
WHEN t."TicketType" = 4006 THEN c."Status"
ELSE w0."TicketStatus"
END AS "Status", CASE
WHEN t."TicketType" = 4001 THEN w."UpdatedDate"
WHEN t."TicketType" = 5003 THEN a."UpdatedDate"
WHEN t."TicketType" = 4006 THEN c."UpdatedDate"
ELSE w0."UpdatedDate"
END AS "UpdatedDate", CASE
WHEN t."TicketType" = 4001 THEN w."PendingHoldDate"
WHEN t."TicketType" = 5003 THEN a."PendingHoldDate"
WHEN (c."CryptoWithdrawalTransactionId" IS NOT NULL) THEN c."PendingHoldDate"
ELSE TIMESTAMPTZ '-infinity'
END AS "PendingHoldDate", CASE
WHEN (c."CryptoWithdrawalTransactionId" IS NOT NULL) THEN c."RequestCryptoAmount"
ELSE 0.0
END AS "RequestCryptoAmount", CASE
WHEN (c."CryptoWithdrawalTransactionId" IS NOT NULL) THEN c."RequestRate"
ELSE 0.0
END AS "RequestRate", CASE
WHEN t."TicketType" = 4001 THEN w."BankName"
WHEN (a."AffiliateWithdrawalTransactionId" IS NOT NULL) THEN a."BankName"
ELSE NULL
END AS "BankName", CASE
WHEN t."TicketType" = 4001 THEN w."AccountName"
WHEN (a."AffiliateWithdrawalTransactionId" IS NOT NULL) THEN a."AccountName"
ELSE NULL
END AS "AccountName", CASE
WHEN (c."CryptoWithdrawalTransactionId" IS NOT NULL) THEN c."CryptoCurrency"
ELSE 0
END AS "CryptoCurrency", CASE
WHEN (a0."Id" IS NOT NULL) THEN a0."CheckedDate"
ELSE TIMESTAMPTZ '-infinity'
END AS "SystemCheckedTime", CASE
WHEN t."TicketType" = 4001 THEN w."PaymentTokenId"
WHEN (a."AffiliateWithdrawalTransactionId" IS NOT NULL) THEN a."PaymentTokenId"
ELSE NULL
END AS "PaymentTokenId", CASE
WHEN t."TicketType" = 4001 THEN w."UpdatedUser"
WHEN t."TicketType" = 5003 THEN a."UpdatedUser"
WHEN (c."CryptoWithdrawalTransactionId" IS NOT NULL) THEN c."UpdatedUser"
ELSE NULL
END AS "UpdatedUser", CASE
WHEN t."TicketType" = 4001 THEN w."FraudAuditUser"
WHEN t."TicketType" = 5003 THEN ''
WHEN (c."CryptoWithdrawalTransactionId" IS NOT NULL) THEN c."FraudAuditUser"
ELSE NULL
END AS "FraudUpdatedUser", CASE
WHEN t."TicketType" = 4001 THEN w."FraudAuditDate"
WHEN t."TicketType" = 5003 THEN TIMESTAMPTZ '-infinity'
WHEN (c."CryptoWithdrawalTransactionId" IS NOT NULL) THEN c."FraudAuditDate"
ELSE TIMESTAMPTZ '-infinity'
END AS "FraudUpdatedDate", c."PlayerCryptoAddress", CASE
WHEN (w."WithdrawalTransactionId" IS NOT NULL) THEN w."WithdrawalProviderId"
ELSE 0
END AS "ProviderId", CASE
WHEN t."TicketType" = 4001 THEN t1."ProviderName"
WHEN t."TicketType" = 5003 THEN t2."ProviderName"
WHEN t."TicketType" = 4006 THEN t3."ProviderName"
ELSE NULL
END AS "ProviderName"
FROM dbo."Ticket" AS t
LEFT JOIN dbo."WithdrawalTransaction" AS w ON t."TicketId" = w."WithdrawalTransactionId"
LEFT JOIN dbo."AffiliateWithdrawalTransaction" AS a ON t."TicketId" = a."AffiliateWithdrawalTransactionId"
LEFT JOIN dbo."CryptoWithdrawalTransaction" AS c ON t."TicketId" = c."CryptoWithdrawalTransactionId"
LEFT JOIN dbo."WalletBalanceTransfer" AS w0 ON t."TicketId" = w0."WalletBalanceTransferId"
LEFT JOIN dbo."AutoAuditWithdrawal" AS a0 ON t."TicketId" = a0."WithdrawalTicketId"
INNER JOIN dbo."TicketFlowStep" AS t0 ON t."TicketId" = t0."TicketId"
LEFT JOIN (
SELECT DISTINCT w1."ProviderId", w1."ProviderName"
FROM dbo."WithdrawalMethod" AS w1
WHERE w1."CurrencyCode" = 'PHP'
) AS t1 ON w."WithdrawalProviderId" = t1."ProviderId"
LEFT JOIN (
SELECT DISTINCT w2."ProviderId", w2."ProviderName"
FROM dbo."WithdrawalMethod" AS w2
WHERE w2."CurrencyCode" = 'PHP'
) AS t2 ON a."WithdrawalProviderId" = t2."ProviderId"
LEFT JOIN (
SELECT DISTINCT w3."ProviderId", w3."ProviderName"
FROM dbo."WithdrawalMethod" AS w3
WHERE w3."CurrencyCode" = 'PHP'
) AS t3 ON c."WithdrawalProviderId" = t3."ProviderId"
WHERE ((((t."CurrencyCode" = 'PHP') AND t."TicketType" IN (4001, 4006, 6002, 6004)) AND (t."CurrentStep" = t0."Step")) AND (CASE
WHEN t."TicketType" = 4001 THEN w."CreatedDate"
WHEN t."TicketType" = 5003 THEN a."CreatedDate"
WHEN t."TicketType" = 4006 THEN c."CreatedDate"
ELSE w0."CreatedDate"
END >= v_bdgin)) AND (CASE
WHEN t."TicketType" = 4001 THEN w."CreatedDate"
WHEN t."TicketType" = 5003 THEN a."CreatedDate"
WHEN t."TicketType" = 4006 THEN c."CreatedDate"
ELSE w0."CreatedDate"
END < v_end)
ORDER BY CASE
WHEN t."TicketType" = 4001 THEN w."CreatedDate"
WHEN t."TicketType" = 5003 THEN a."CreatedDate"
WHEN t."TicketType" = 4006 THEN c."CreatedDate"
ELSE w0."CreatedDate"
END DESC
LIMIT 50 OFFSET 0
$$ LANGUAGE SQL STABLE;
```
- 原本的版本 查ticket count
```sql=
-- create function
CREATE or replace FUNCTION dbo.andytest_count_origin(
begin timestamp(6) with time zone,
end timestamp(6) with time zone) RETURNS SETOF record AS $$
SELECT COUNT(*)::INT
FROM dbo."Ticket" AS t
LEFT JOIN dbo."WithdrawalTransaction" AS w ON t."TicketId" = w."WithdrawalTransactionId"
LEFT JOIN dbo."AffiliateWithdrawalTransaction" AS a ON t."TicketId" = a."AffiliateWithdrawalTransactionId"
LEFT JOIN dbo."CryptoWithdrawalTransaction" AS c ON t."TicketId" = c."CryptoWithdrawalTransactionId"
LEFT JOIN dbo."WalletBalanceTransfer" AS w0 ON t."TicketId" = w0."WalletBalanceTransferId"
LEFT JOIN dbo."AutoAuditWithdrawal" AS a0 ON t."TicketId" = a0."WithdrawalTicketId"
INNER JOIN dbo."TicketFlowStep" AS t0 ON t."TicketId" = t0."TicketId"
LEFT JOIN (
SELECT DISTINCT w1."ProviderId", w1."ProviderName"
FROM dbo."WithdrawalMethod" AS w1
WHERE w1."CurrencyCode" = 'PHP'
) AS t1 ON w."WithdrawalProviderId" = t1."ProviderId"
LEFT JOIN (
SELECT DISTINCT w2."ProviderId", w2."ProviderName"
FROM dbo."WithdrawalMethod" AS w2
WHERE w2."CurrencyCode" = 'PHP'
) AS t2 ON a."WithdrawalProviderId" = t2."ProviderId"
LEFT JOIN (
SELECT DISTINCT w3."ProviderId", w3."ProviderName"
FROM dbo."WithdrawalMethod" AS w3
WHERE w3."CurrencyCode" = 'PHP'
) AS t3 ON c."WithdrawalProviderId" = t3."ProviderId"
WHERE ((((t."CurrencyCode" = 'PHP') AND t."TicketType" IN (4001, 4006, 6002, 6004)) AND (t."CurrentStep" = t0."Step")) AND (CASE
WHEN t."TicketType" = 4001 THEN w."CreatedDate"
WHEN t."TicketType" = 5003 THEN a."CreatedDate"
WHEN t."TicketType" = 4006 THEN c."CreatedDate"
ELSE w0."CreatedDate"
END >= v_bdgin)) AND (CASE
WHEN t."TicketType" = 4001 THEN w."CreatedDate"
WHEN t."TicketType" = 5003 THEN a."CreatedDate"
WHEN t."TicketType" = 4006 THEN c."CreatedDate"
ELSE w0."CreatedDate"
END < v_end)
$$ LANGUAGE SQL STABLE;
```
- 原版group by
```sql=
CREATE or replace FUNCTION dbo.andytest_groupby_origin(
begin timestamp(6) with time zone,
end timestamp(6) with time zone) RETURNS SETOF record AS $$
SELECT CASE
WHEN t."TicketType" = 4001 THEN w."Status"
WHEN t."TicketType" = 5003 THEN a."Status"
WHEN t."TicketType" = 4006 THEN c."Status"
ELSE w0."TicketStatus"
END AS "TicketStatus", COALESCE(SUM(CASE
WHEN t."TicketType" = 4001 THEN w."RequestAmount"
WHEN t."TicketType" = 5003 THEN a."RequestAmount"
WHEN t."TicketType" = 4006 THEN c."RequestFiatAmount"
ELSE w0."RequestAmount"
END), 0.0) AS "Amount"
FROM dbo."Ticket" AS t
LEFT JOIN dbo."WithdrawalTransaction" AS w ON t."TicketId" = w."WithdrawalTransactionId"
LEFT JOIN dbo."AffiliateWithdrawalTransaction" AS a ON t."TicketId" = a."AffiliateWithdrawalTransactionId"
LEFT JOIN dbo."CryptoWithdrawalTransaction" AS c ON t."TicketId" = c."CryptoWithdrawalTransactionId"
LEFT JOIN dbo."WalletBalanceTransfer" AS w0 ON t."TicketId" = w0."WalletBalanceTransferId"
LEFT JOIN dbo."AutoAuditWithdrawal" AS a0 ON t."TicketId" = a0."WithdrawalTicketId"
INNER JOIN dbo."TicketFlowStep" AS t0 ON t."TicketId" = t0."TicketId"
LEFT JOIN (
SELECT DISTINCT w1."ProviderId", w1."ProviderName"
FROM dbo."WithdrawalMethod" AS w1
WHERE w1."CurrencyCode" = 'PHP'
) AS t1 ON w."WithdrawalProviderId" = t1."ProviderId"
LEFT JOIN (
SELECT DISTINCT w2."ProviderId", w2."ProviderName"
FROM dbo."WithdrawalMethod" AS w2
WHERE w2."CurrencyCode" = 'PHP'
) AS t2 ON a."WithdrawalProviderId" = t2."ProviderId"
LEFT JOIN (
SELECT DISTINCT w3."ProviderId", w3."ProviderName"
FROM dbo."WithdrawalMethod" AS w3
WHERE w3."CurrencyCode" = 'PHP'
) AS t3 ON c."WithdrawalProviderId" = t3."ProviderId"
WHERE ((((t."CurrencyCode" = 'PHP') AND t."TicketType" IN (4001, 4006, 6002, 6004)) AND (t."CurrentStep" = t0."Step")) AND (CASE
WHEN t."TicketType" = 4001 THEN w."CreatedDate"
WHEN t."TicketType" = 5003 THEN a."CreatedDate"
WHEN t."TicketType" = 4006 THEN c."CreatedDate"
ELSE w0."CreatedDate"
END >= v_bdgin)) AND (CASE
WHEN t."TicketType" = 4001 THEN w."CreatedDate"
WHEN t."TicketType" = 5003 THEN a."CreatedDate"
WHEN t."TicketType" = 4006 THEN c."CreatedDate"
ELSE w0."CreatedDate"
END < v_end)
GROUP BY CASE
WHEN t."TicketType" = 4001 THEN w."Status"
WHEN t."TicketType" = 5003 THEN a."Status"
WHEN t."TicketType" = 4006 THEN c."Status"
ELSE w0."TicketStatus"
END
$$ LANGUAGE SQL STABLE;
```
- 新版的指令
```sql=
-- 執行查詢的指令
select "Totalcount", "Content" from dbo."usp_GetWithdrawalTransaction"(
null::uuid,
'PHP'::char(3),
null::varchar,
false::boolean,
null::varchar,
null::smallint[],
array[4001::smallint, 4006::smallint, 6002::smallint, 6004::smallint],
null::smallint[],
null::smallint[],
null::smallint,
null::timestamp(6) with time zone,
null::timestamp(6) with time zone,
1::integer,
50::integer
)
-- create function
CREATE OR REPLACE FUNCTION dbo."usp_GetWithdrawalTransaction"(
"playerId" uuid,
"currencyCode" char(3),
"displayId" varchar,
"isExport" boolean,
"providerName" varchar,
"autoAuditWithdrawalStatus" smallint[],
"ticketTypes" smallint[],
"ticketStatus" smallint[],
"providerIds" smallint[],
"cryptoCurrency" smallint,
"begin" timestamp(6) with time zone,
"end" timestamp(6) with time zone,
"page" integer,
"pageSize" integer,
"Content" out text,
"Totalcount" out integer,
"TotalPendingAmount" out decimal,
"TotalFloatingAmount" out decimal,
"TotalRejectAmount" out decimal,
"TotalApprovedAmount" out decimal
)LANGUAGE plpgsql AS $$
DECLARE
v_playerid uuid :="playerId" ;
v_currencycode char(3) :="currencyCode";
v_displayid varchar :="displayId";
v_isexport boolean :="isExport";
v_providername varchar:="providerName";
v_cryptocurrency smallint :="cryptoCurrency";
v_begin timestamp(6) with time zone :="begin";
v_end timestamp(6) with time zone :="end";
v_page integer:= "page";
v_pagesize integer:="pageSize";
v_test integer:=0;
BEGIN
WITH source AS (
select
t."TicketId"
,t."TicketType"
,tx.displayid as "DisplayId"
,tx.playerid as "PlayerId"
,tx.cryptocurrency as "CryptoCurrency"
,tx.providerid as "InternalProviderId"
,(case tx.createddate when '-infinity' then '0001-01-01 00:00:00.0000000 +00:00' else tx.createddate end) as "CreatedDate"
,tx.status as "Status"
,tx.walletId as "WalletId"
,tx.requestamount as "RequestAmount"
,tx.actualamount as "ActualAmount"
,tx.fee as "Fee"
,tx.paymentgroup as "PaymentGroup"
,(case tx.updateddate when '-infinity' then '0001-01-01 00:00:00.0000000 +00:00' else tx.updateddate end) as "UpdatedDate"
,(case tx.pendingholddate when '-infinity' then '0001-01-01 00:00:00.0000000 +00:00' else tx.pendingholddate end) as "PendingHoldDate"
,tx.requestcryptoamount as "RequestCryptoAmount"
,tx.requestrate as "RequestRate"
,tx.bankname as "BankName"
,tx.accountname as "AccountName"
,tx.systemcheckedtime as "SystemCheckedTime"
,tx.paymenttokenid as "PaymentTokenId"
,tx.updateduser as "UpdatedUser"
,tx.fraudupdateduser as "FraudUpdatedUser"
,(case tx.fraudupdateddate when '-infinity' then '0001-01-01 00:00:00.0000000 +00:00' else tx.fraudupdateddate end) as "FraudUpdatedDate"
,tx.playercryptoaddress as "PlayerCryptoAddress"
,tx.autoauditwithdrawalstatus as "AutoAuditWithdrawalStatus"
,(case when adw."Id" is null then '0001-01-01 00:00:00.0000000 +00:00' else adw."CheckedDate" end) as "CheckDate"
from dbo."Ticket" t
inner join (
select
wt."WithdrawalTransactionId" as ticketid
,wt."DisplayId" as displayid
,wt."PlayerId" as playerid
,0 as cryptocurrency
,wt."WithdrawalProviderId" as providerid
,wt."CreatedDate" as createddate
,wt."Status" as "status"
,wt."GameLoginId" as walletId
,wt."RequestAmount" as requestamount
,wt."ActualAmount" as actualamount
,wt."Fee" as fee
,wt."PaymentGroupName" as paymentgroup
,wt."UpdatedDate" as updateddate
,wt."PendingHoldDate" as pendingholddate
,0 as requestcryptoamount
,0 as requestrate
,wt."BankName" as bankname
,wt."AccountName" as accountname
,'0001-01-01 00:00:00.0000000 +00:00' as systemcheckedtime
,wt."PaymentTokenId" as paymenttokenid
,wt."UpdatedUser" as updateduser
,wt."FraudAuditUser" as fraudupdateduser
,wt."FraudAuditDate" as fraudupdateddate
,'' as playercryptoaddress
,wt."AutoAuditWithdrawalStatus" as autoauditwithdrawalstatus
from dbo."WithdrawalTransaction" as wt
where ("playerId" is null or wt."PlayerId" = v_playerid)
and wt."CurrencyCode" = v_currencycode
and ("displayId" is null or (wt."DisplayId" = v_displayid or wt."GameLoginId" = v_displayid or wt."PaymentTokenId" = v_displayid))
and ("ticketStatus" is null or wt."Status"=ANY("ticketStatus") )
and ("begin" is null or wt."CreatedDate" >= v_begin)
and ("end" is null or wt."CreatedDate" < v_end)
UNION ALL
select
awt."AffiliateWithdrawalTransactionId" as ticketid
,awt."DisplayId" as displayid
,awt."PlayerId" as playerid
,0 as cryptocurrency
,awt."WithdrawalProviderId" as providerid
,awt."CreatedDate" as createddate
,awt."Status" as "status"
,awt."PlayerGameLoginId" as walletId
,awt."RequestAmount" as requestamount
,awt."ActualAmount" as actualamount
,awt."Fee" as fee
,awt."PaymentGroupName" as paymentgroup
,awt."UpdatedDate" as updateddate
,awt."PendingHoldDate" as pendingholddate
,0 as requestcryptoamount
,0 as requestrate
,awt."BankName" as bankname
,awt."AccountName" as accountname
,'0001-01-01 00:00:00.0000000 +00:00' as systemcheckedtime
,awt."PaymentTokenId" as paymenttokenid
,awt."UpdatedUser" as updateduser
,'' as fraudupdateduser
,'0001-01-01 00:00:00.0000000 +00:00' as fraudupdateddate
,'' as playercryptoaddress
,0 as autoauditwithdrawalstatus
from dbo."AffiliateWithdrawalTransaction" as awt
where ("playerId" is null or awt."PlayerId" = v_playerid)
and awt."CurrencyCode" = v_currencycode
and ("displayId" is null or (awt."DisplayId" = v_displayid or awt."PlayerGameLoginId" = v_displayid or awt."PaymentTokenId" = v_displayid))
and ("ticketStatus" is null or awt."Status"=ANY("ticketStatus") )
and ("begin" is null or awt."CreatedDate" >= v_begin)
and ("end" is null or awt."CreatedDate" < v_end)
UNION ALL
select
wb."WalletBalanceTransferId" as ticketid
,wb."DisplayId" as displayid
,wb."FromPlayerId" as playerid
,0 as cryptocurrency
,0 as providerid
,wb."CreatedDate" as createddate
,wb."TicketStatus" as "status"
,wb."FromGameLoginId" as walletId
,wb."RequestAmount" as requestamount
,wb."ActualAmount" as actualamount
,0 as fee
,'' as paymentgroup
,wb."UpdatedDate" as updateddate
,'0001-01-01 00:00:00.0000000 +00:00' as pendingholddate
,0 as requestcryptoamount
,0 as requestrate
,'' as bankname
,'' as accountname
,'0001-01-01 00:00:00.0000000 +00:00' as systemcheckedtime
,'' as paymenttokenid
,wb."UpdatedUser" as updateduser
,'' as fraudupdateduser
,'0001-01-01 00:00:00.0000000 +00:00' as fraudupdateddate
,'' as playercryptoaddress
,wb."AutoAuditWithdrawalStatus" as autoauditwithdrawalstatus
from dbo."WalletBalanceTransfer" as wb
where ("playerId" is null or wb."FromPlayerId" = v_playerid)
and wb."CurrencyCode" = v_currencycode
and ("displayId" is null or (wb."DisplayId" = v_displayid or wb."FromGameLoginId" = v_displayid))
and ("ticketStatus" is null or wb."TicketStatus"=ANY("ticketStatus") )
and ("begin" is null or wb."CreatedDate" >= v_begin)
and ("end" is null or wb."CreatedDate" < v_end)
UNION ALL
select
crypto."CryptoWithdrawalTransactionId" as ticketid
,crypto."DisplayId" as displayid
,crypto."PlayerId" as playerid
,crypto."CryptoCurrency" as cryptocurrency
,crypto."WithdrawalProviderId" as providerid
,crypto."CreatedDate" as createddate
,crypto."Status" as "status"
,crypto."GameLoginId" as walletId
,crypto."RequestFiatAmount" as requestamount
,crypto."ActualFiatAmount" as actualamount
,0 as fee
,crypto."PaymentGroupName" as paymentgroup
,crypto."UpdatedDate" as updateddate
,crypto."PendingHoldDate" as pendingholddate
,crypto."RequestCryptoAmount" as requestcryptoamount
,crypto."RequestRate" as requestrate
,'' as bankname
,'' as accountname
,'0001-01-01 00:00:00.0000000 +00:00' as systemcheckedtime
,'' as paymenttokenid
,crypto."UpdatedUser" as updateduser
,crypto."FraudAuditUser" as fraudupdateduser
,crypto."FraudAuditDate" as fraudupdateddate
,crypto."PlayerCryptoAddress" as playercryptoaddress
,crypto."AutoAuditWithdrawalStatus" as autoauditwithdrawalstatus
from dbo."CryptoWithdrawalTransaction" as crypto
where ("playerId" is null or crypto."PlayerId" = v_playerid)
and crypto."CurrencyCode" = v_currencycode
and ("displayId" is null or (crypto."DisplayId" = v_displayid or crypto."GameLoginId" = v_displayid))
and ("ticketStatus" is null or crypto."Status"=ANY("ticketStatus") )
and ("begin" is null or crypto."CreatedDate" >= v_begin)
and ("end" is null or crypto."CreatedDate" < v_end)
) tx
on t."TicketId" = tx."ticketid"
inner join dbo."TicketFlowStep" tfs on t."TicketId" = tfs."TicketId"
left join dbo."AutoAuditWithdrawal" adw on t."TicketId" = adw."WithdrawalTicketId" and tx."ticketid" = adw."WithdrawalTicketId"
where ("autoAuditWithdrawalStatus" is null or (tx.autoauditwithdrawalstatus=ANY("autoAuditWithdrawalStatus")))
and t."CurrencyCode" = v_currencycode
and t."CurrentStep" = tfs."Step"
and ("begin" is null or t."CreatedDate" >= v_begin)
and ("end" is null or t."CreatedDate" < v_end)
and ("ticketTypes" is null or t."TicketType"=ANY("ticketTypes"))
)
select
(case when v_isexport is true then 0
else (
select count(*) from source t
left join ( select distinct "ProviderId", "ProviderName" from dbo."WithdrawalMethod" where "CurrencyCode" = v_currencycode) wm
on t."InternalProviderId" = wm."ProviderId"
where ("providerIds" is null or t."InternalProviderId"=ANY("providerIds"))
and ("providerName" is null or wm."ProviderName" =v_providername )
) end
)
,(
select json_agg(tb.*)
from (
select t.*, wm."ProviderName", (case t."TicketType" when 4001 then t."InternalProviderId" else 0 end) as "ProviderId"
from source t
left join ( select distinct "ProviderId", "ProviderName" from dbo."WithdrawalMethod" where "CurrencyCode" = v_currencycode) wm
on t."InternalProviderId" = wm."ProviderId"
where ("providerIds" is null or t."InternalProviderId"=ANY("providerIds"))
and ("providerName" is null or wm."ProviderName" =v_providername )
order by t."CreatedDate" desc
offset (CASE WHEN v_isexport is false THEN ((v_page-1) * v_pagesize) END)
limit (CASE WHEN v_isexport is false THEN v_pagesize END)
) tb
)
,(case when v_isexport is true then 0 else COALESCE((sum (ts."RequestAmount") FILTER(WHERE ts."Status"=10)), 0) end)
,(case when v_isexport is true then 0 else COALESCE((sum (ts."RequestAmount") FILTER(WHERE ts."Status"=20)), 0) end)
,(case when v_isexport is true then 0 else COALESCE((sum (ts."RequestAmount") FILTER(WHERE ts."Status"=32)), 0) end)
,(case when v_isexport is true then 0 else COALESCE((sum (ts."RequestAmount") FILTER(WHERE ts."Status"=31)), 0) end)
into "Totalcount", "Content", "TotalPendingAmount", "TotalFloatingAmount","TotalApprovedAmount","TotalRejectAmount"
from source ts;
END$$
```
Sign in with Wallet
Connect another wallet