Compare-Checkout-and-Processout-Payments

Get Checkout Payments

We get all information from CkoEvent table from database prod-paymentCheckout-use2-sqlDb

declare @beginDate datetime;
declare @endDate datetime;
select @beginDate ='2022-12-15';
select @endDate ='2022-12-16';

with CkoEvt as
(
 select 
    JSON_VALUE(CkoEventData, '$.data.id') as CkoPaymentId
    ,JSON_VALUE(CkoEventData, '$.data.amount') as CkoAmount
    ,JSON_VALUE(CkoEventData, '$.data.response_code') as ResponseCode
    ,JSON_VALUE(CkoEventData, '$.data.response_summary') as ResponseSummary
    ,JSON_VALUE(CkoEventData, '$.created_on') as CreatedOn
 from dbo.CkoEvent
)
,CkoEventRN as
(
  select 
      *,
      ROW_NUMBER() OVER(PARTITION BY CkoPaymentId ORDER BY CreatedOn DESC) AS row_number
   from CkoEvt
)
select 
    p.UserId,
    p.CardId,
    p.CkoPaymentId,
    Convert(decimal(10,2),CONVERT(decimal,rn.CkoAmount)/100.0) as CkoAmount,
    rn.ResponseCode,
    rn.ResponseSummary,
    p.CreatedOn
from dbo.Payment p
join CkoEventRN rn
    on p.CkoPaymentId = rn.CkoPaymentId 
        and rn.row_number=1 
        and rn.ResponseCode>20000 
        and p.CreatedOn between @beginDate and @endDate
order by p.CreatedOn desc

Import Checkout payment info to Psp Db

Previous step get all payment failed payment information from checkout. To compare it with payment via processout, We need to import this information into prod-pspFunding-use2-sqlDb database. We are not allowed to create table in database so we need a table variable

1. export output of previews sql to csv file.
2. open csv file in vscode and convert it to sql which create a table variable and insert all results into it. The result should be something like this. number of rows below values should be less than 1000. It is a restriction of Sql Server. If there are more than 1000 payments. You need to break it down to multiple insert statements.

declare @cko_payments table (
      UserId uniqueidentifier, 
      CardId uniqueidentifier, 
      CkoPaymentId varchar(100), 
      CkoAmount decimal(10,2),
      CkoResponseCode varchar(10), 
      CkoResponseSummary varchar(100), 
      CkoCreatedOn datetime);

insert into @cko_payments(UserId, CardId, CkoPaymentId, CkoAmount, CKoResponseCode,CkoResponseSummary,CkoCreatedOn)
values
('89EF6969-EC08-4821-988B-227F164FF205','3EBD54D9-B738-4EC8-9564-F4E7F6E6D254','pay_4rslp7o2ntduvm3vpagykklx7y','5.99','20051','Insufficient Funds','2022-12-15 13:24:08.550'),
-- more rows here
('65C87ED6-DEAC-47A6-A075-E6858F6D2A17','6A60105D-EB71-45A1-AD42-55B669B92041','pay_23ak6fz2ua5ujbi5gf6ihr3xla','20.00','20051','Insufficient Funds','2022-12-12 13:43:54.723');

Compare with Processout Orders and Create report

This sql uses variable created in previous sql. So they must be in the same session

with psp_po_tx
as(
select  
    c.UserId,
    c.CardId,
    o.DepositedAmount as OrderAmount, 
    o.OrderStateId,
    case when o.OrderStateId =3 then 'Succeed' else 'Failed' end as OrderState,
    o.Created as OrderCreatedOn
 from @cko_payments p
 join dbo.ExternalCards c 
    on p.UserId = c.UserId and p.CardId=c.CardId
 join dbo.SaveFundingAccountOrder a 
    on c.ExternalCardGatewayId= 1 and c.CardId=a.ExternalCardId
 join dbo.Orders o
    on o.AccountId = a.AccountId
),
psp_po_tx_rn
as(
select 
    *,
    ROW_NUMBER() over(partition by UserId, CardId order by OrderCreatedOn desc ) as RN
from psp_po_tx
where OrderStateId !=3
),
psp_po_tx_3
as (
 select 
     *,
     ROW_NUMBER() over(partition by UserId, CardId order by OrderCreatedOn desc ) as RN
 from psp_po_tx
 where OrderStateId=3
)
select p.*,
rn.OrderAmount as LastFailOrderAmount,
rn.OrderCreatedOn as LastFailOrderCreatedOn,
rn3.OrderAmount as LastSuccessOrderAmount, 
rn3.OrderCreatedOn as LastSuccessOrderCreatedOn,
DATEDIFF(DAY, rn.OrderCreatedOn,p.CkoCreatedOn) as DaysSinceLastFailOrder,
DATEDIFF(DAY, rn3.OrderCreatedOn, p.CkoCreatedOn) as DaysSinceLastSuccess
from @cko_payments p
left join psp_po_tx_rn rn
    on p.UserId=rn.UserId and p.CardId = rn.CardId and rn.RN=1
left join psp_po_tx_3 rn3
    on p.UserId=rn3.UserId and p.CardId =rn3.CardId and rn3.RN=1