Untitled
unknown
mysql
5 months ago
16 kB
8
Indexable
/*==============================================================================================================*/
/*================================================== QUERY PAID ================================================*/
SELECT DISTINCT(lpn.status) FROM log__payment_notification lpn;
-- status
-- "00"
-- "78"
-- "14"
-- "13"
-- "76"
-- XX
-- Nul
SELECT
a.`reference_id`,
SUBSTR(a.request_data,
LOCATE ('paymentSource',a.request_data)+15,
LOCATE('customAccount',a.request_data)-LOCATE ('paymentSource',a.request_data)-17)
AS 'payment source',
a.`status`,
CONCAT_WS(" ", "Dapet notif", SUBSTR(
a.response_data,
LOCATE ( 'message', a.response_data ) + 9,
LOCATE( 'timestamp', a.response_data ) - LOCATE ( 'message', a.response_data ) - 11 ), "dari Order")
AS 'Reason'
FROM
log__payment_notification a
WHERE 1
AND a.log_timestamp BETWEEN '2024-03-16 03:00:01' AND '2024-03-16 07:09:00'
AND a.status IN (00) GROUP BY a.reference_id ASC;
#== ALFAMART
SELECT
-- COUNT(DISTINCT(lop.log_reference_id))
DISTINCT lop.log_reference_id,
lop.log_timestamp
FROM
log__outbound_payment lop
JOIN log__payment_notification lpn ON lpn.reference_id = lop.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-06-09 00:00:01' AND '2024-06-09 23:59:59'
AND lpn.response_data LIKE '%Success - paid%'
AND lop.log_payment_channel = 'ALFAMART'
AND lop.log_type = 'faspay_payment_notification'
AND lop.log_reason_code = '00'
AND lop.log_response LIKE '%Sukses%'
AND lpn.request_data LIKE '%ALFAMART%'
AND lpn.status = 00 GROUP BY lop.log_reference_id ASC;
#== BCA TRANSFER
SELECT
COUNT(DISTINCT la.log_reference_id)
FROM
log__abtc la
JOIN log__payment_notification lpn ON lpn.reference_id = la.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-02-24 18:00:00' AND '2024-02-24 20:00:00'
AND la.log_payment_source = 'bca'
AND la.log_notes = 'Data has been insert/update'
AND lpn.response_data LIKE '%Success - paid%'
AND lpn.request_data LIKE '%BCA_TRANSFER%'
AND lpn.status = 00;
#== CIMB_CLICKS
SELECT
COUNT(DISTINCT lop.log_reference_id)
FROM
log__outbound_payment lop
JOIN log__payment_notification lpn ON lpn.reference_id = lop.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-02-24 22:00:00' AND '2024-02-24 23:00:00'
AND lop.log_payment_channel = 'CIMB_CLICKS'
AND lop.log_type = 'payment_response_backend'
AND lop.log_reason_code = 'OK'
AND lop.log_response LIKE '%OK%'
AND lpn.response_data LIKE '%Success - paid%'
AND lpn.request_data LIKE '%CIMB_CLICKS%'
AND lpn.status = 00;
#== DEBIT ONLINE 2C2P
SELECT
COUNT(DISTINCT lcc.log_reference_id)
FROM
log__credit_card lcc
JOIN log__payment_notification lpn ON lpn.reference_id = lcc.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-02-21 10:00:00' AND '2024-02-24 23:00:00'
AND lpn.response_data LIKE '%Success - paid%'
AND lpn.request_data LIKE '%DEBIT_ONLINE%'
AND lcc.log_pg_name = '2c2p'
AND lcc.log_service_name = '2c2p_payment_response'
AND lcc.log_response LIKE '%Approved%'
AND lpn.status = 00;
#== CC 2C2P
SELECT
COUNT(DISTINCT lcc.log_reference_id)
FROM
log__credit_card lcc
JOIN log__payment_notification lpn ON lpn.reference_id = lcc.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-02-24 21:00:00' AND '2024-02-24 23:00:00'
AND lpn.response_data LIKE '%Success - paid%'
AND lpn.request_data LIKE '%CREDITCARD%'
AND lcc.log_pg_name = '2c2p'
AND lcc.log_service_name = '2c2p_payment_response'
AND lcc.log_response LIKE '%Approved%'
AND lpn.status = 00;
#== CC NICEPAY
SELECT
COUNT(DISTINCT lcc.log_reference_id)
-- lcc.log_reference_id
FROM
log__credit_card lcc
JOIN log__payment_notification lpn ON lpn.reference_id = lcc.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-04-12 19:00:00' AND '2024-04-12 19:20:00'
AND lpn.response_data LIKE '%Success - paid%'
AND lpn.request_data LIKE '%CREDITCARD%'
AND lcc.log_pg_name = 'NICEPAY'
AND lcc.log_service_name = 'payment_notification'
AND lcc.log_response LIKE '%SUCCESS%'
AND lpn.status = 00;
#== DEBIT ONLINE NICEPAY
SELECT
COUNT(DISTINCT lcc.log_reference_id)
-- lcc.log_reference_id
FROM
log__credit_card lcc
JOIN log__payment_notification lpn ON lpn.reference_id = lcc.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-02-24 21:00:00' AND '2024-02-24 23:00:00'
AND lpn.response_data LIKE '%Success - paid%'
AND lpn.request_data LIKE '%DEBIT_ONLINE%'
AND lcc.log_pg_name = 'NICEPAY'
AND lcc.log_service_name = 'payment_notification'
AND lcc.log_response LIKE '%SUCCESS%'
AND lpn.status = 00;
#== DEBIT ONLINE XENDIT
SELECT
COUNT(DISTINCT lcc.log_reference_id)
-- lcc.log_reference_id
FROM
log__credit_card lcc
JOIN log__payment_notification lpn ON lpn.reference_id = lcc.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-06-27 00:00:01' AND '2024-06-27 00:50:00'
AND lpn.response_data LIKE '%Success - paid%'
AND lpn.request_data LIKE '%DEBIT_ONLINE%'
AND lcc.log_pg_name = 'xendit'
AND lcc.log_service_name = 'create_charge'
AND lcc.log_response LIKE '%CAPTURED%'
AND lpn.status = 00;
#== CC XENDIT
SELECT
COUNT(DISTINCT lcc.log_reference_id)
-- lcc.log_reference_id
FROM
log__credit_card lcc
JOIN log__payment_notification lpn ON lpn.reference_id = lcc.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-06-27 00:00:01' AND '2024-06-27 00:50:00'
AND lpn.response_data LIKE '%Success - paid%'
AND lpn.request_data LIKE '%CREDITCARD%'
AND lcc.log_pg_name = 'xendit'
AND lcc.log_service_name = 'create_charge'
AND lcc.log_response LIKE '%CAPTURED%'
AND lpn.status = 00;
#== DEBIT ONLINE STRIPE
SELECT
COUNT(DISTINCT lcc.log_reference_id)
-- lcc.log_reference_id
FROM
log__credit_card lcc
JOIN log__payment_notification lpn ON lpn.reference_id = lcc.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-02-29 11:00:00' AND '2024-02-29 12:00:00'
AND lpn.response_data LIKE '%Success - paid%'
AND lpn.request_data LIKE '%DEBIT_ONLINE%'
AND lcc.log_pg_name = 'stripe'
AND lcc.log_service_name = 'payment_notification'
AND lcc.log_response = 'SUCCESS'
AND lpn.status = 00;
#== CC STRIPE
SELECT
COUNT(DISTINCT lcc.log_reference_id)
-- lcc.log_reference_id
FROM
log__credit_card lcc
JOIN log__payment_notification lpn ON lpn.reference_id = lcc.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-02-24 21:00:00' AND '2024-02-24 23:00:00'
AND lpn.response_data LIKE '%Success - paid%'
AND lpn.request_data LIKE '%CREDITCARD%'
AND lcc.log_pg_name = 'stripe'
AND lcc.log_service_name = 'payment_notification'
AND lcc.log_response = 'SUCCESS'
AND lpn.status = 00;
#== GOPAY
SELECT
-- COUNT(DISTINCT lop.log_reference_id)
DISTINCT lop.log_reference_id,
lop.log_timestamp
FROM
log__outbound_payment lop
JOIN log__payment_notification lpn ON lpn.reference_id = lop.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-06-09 00:00:01' AND '2024-06-09 23:59:59'
AND lpn.response_data LIKE '%Success - paid%'
AND lop.log_payment_channel = 'GOPAY'
AND lop.log_type = 'midtrans_payment_notification'
AND lop.log_reason_code = '200'
AND lop.log_response LIKE '%Transaction has been paid%'
AND lpn.request_data LIKE '%GOPAY%'
AND lpn.status = 00 GROUP BY lop.log_reference_id ASC;
#== INDOMARET MIDTRANS
SELECT
-- COUNT(DISTINCT lop.log_reference_id)
DISTINCT lop.log_reference_id,
lop.log_timestamp
FROM
log__outbound_payment lop
JOIN log__payment_notification lpn ON lpn.reference_id = lop.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-06-09 00:00:01' AND '2024-06-09 23:59:59'
AND lpn.response_data LIKE '%Success - paid%'
AND lop.log_payment_channel = 'INDOMARET'
AND lpn.request_data LIKE '%INDOMARET_MIDTRANS%'
AND lpn.status = 00 GROUP BY lop.log_reference_id ASC;
#== KLIKBCA
SELECT
COUNT(DISTINCT lop.log_reference_id)
-- lop.log_reference_id, lop.log_timestamp
FROM
log__outbound_payment lop
JOIN log__payment_notification lpn ON lpn.reference_id = lop.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-06-18 23:00:00' AND '2024-06-18 23:34:01'
AND lpn.response_data LIKE '%Success - paid%'
AND lpn.request_data LIKE '%KLIKBCA%'
AND lpn.status = 00;
#== KREDIVO
SELECT
-- COUNT(DISTINCT lk.log_reference_id)
distinct lk.log_reference_id
FROM
log__kredivo lk
JOIN log__payment_notification lpn ON lpn.reference_id = lk.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-05-19 18:00:00' AND '2024-05-19 20:20:00'
AND lk.log_type = 'kredivo_update_status'
AND lk.log_response LIKE '%OK%'
AND lpn.response_data LIKE '%Success - paid%'
AND lpn.request_data LIKE '%KREDIVO%'
AND lpn.status = 00 GROUP BY lk.log_reference_id ASC;
#== LINKAJA
SELECT
-- COUNT(DISTINCT lop.log_reference_id)
DISTINCT lop.log_reference_id,
lop.log_timestamp
FROM
log__outbound_payment lop
JOIN log__payment_notification lpn ON lpn.reference_id = lop.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-06-09 00:00:01' AND '2024-06-09 23:59:59'
AND lpn.response_data LIKE '%Success - paid%'
AND lop.log_payment_channel = 'LINKAJA'
AND lop.log_reason_code = 'Success'
AND lop.log_response LIKE '%Success%'
AND lpn.request_data LIKE '%LINKAJA%'
AND lpn.status = 00 GROUP BY lop.log_reference_id ASC;
#== MANDIRI TRANSFER
SELECT
COUNT(DISTINCT la.log_reference_id)
-- la.log_reference_id
FROM
log__abtc la
JOIN log__payment_notification lpn ON lpn.reference_id = la.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-02-24 18:00:00' AND '2024-02-24 20:00:00'
AND lpn.response_data LIKE '%Success - paid%'
AND lpn.request_data LIKE '%MANDIRI_TRANSFER%'
AND lpn.status = 00;
#== OVO
SELECT
COUNT(DISTINCT lop.log_reference_id)
-- lop.log_reference_id
FROM
log__outbound_payment lop
JOIN log__payment_notification lpn ON lpn.reference_id = lop.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-02-24 21:00:00' AND '2024-02-24 23:00:00'
AND lpn.response_data LIKE '%Success - paid%'
AND lop.log_payment_channel = 'OVO'
AND lop.log_reason_code = 'SUCCESS'
AND lop.log_response LIKE '%SUCCESS%'
AND lpn.request_data LIKE '%OVO%'
AND lpn.status = 00;
#== PAYLATER
SELECT
COUNT(DISTINCT lpl.log_reference_id)
-- lpl.log_reference_id
FROM
log__pay_later lpl
JOIN log__payment_notification lpn ON lpn.reference_id = lpl.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-08-06 00:00:01' AND '2024-08-06 23:59:59'
AND lpn.response_data LIKE '%Success - paid%'
AND lpl.log_type = 'payment_notification'
AND lpl.log_response LIKE '%200%'
AND lpn.request_data LIKE '%PAY_LATER%'
AND lpn.status = 00;
#== SAKUKU
SELECT
-- COUNT(DISTINCT lop.log_reference_id)
lop.log_reference_id,
lop.log_timestamp
FROM
log__outbound_payment lop
JOIN log__payment_notification lpn ON lpn.reference_id = lop.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-03-01 00:00:01' AND '2024-03-16 23:00:01'
AND lpn.response_data LIKE '%Success - paid%'
AND lop.log_payment_channel = 'SAKUKU'
AND lop.log_reason_code = '00'
AND lop.log_response LIKE '%SUCCESS%'
AND lpn.request_data LIKE '%SAKUKU%'
AND lpn.status = 00 GROUP BY lop.log_reference_id;
#== SHOPEEPAY
SELECT
COUNT(DISTINCT lop.log_reference_id)
-- lop.log_reference_id
FROM
log__outbound_payment lop
JOIN log__payment_notification lpn ON lpn.reference_id = lop.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-02-24 21:00:00' AND '2024-02-24 23:00:00'
AND lpn.response_data LIKE '%Success - paid%'
AND lop.log_payment_channel = 'SHOPEE_PAY'
AND lop.log_type = 'payment_notification'
AND lop.log_response LIKE '%Success%'
AND lpn.request_data LIKE '%SHOPEE_PAY%'
AND lpn.status = 00;
#== VA BCA
SELECT
-- COUNT(DISTINCT lop.log_reference_id)
Distinct lop.log_reference_id,
lop.log_timestamp
FROM
log__outbound_payment lop
JOIN log__payment_notification lpn ON lpn.reference_id = lop.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-07-12 10:00:01' AND '2024-07-12 14:00:01'
AND lop.log_payment_channel = 'H2H'
AND lop.log_type = 'bca_notification'
AND lop.log_reason_code = '00'
AND lpn.request_data LIKE '%VIRTUAL_ACCOUNT_NICEPAY%'
AND lpn.status = 00 GROUP BY lop.log_reference_id ASC;
#== VA BNI
SELECT
-- COUNT(DISTINCT lop.log_reference_id)
DISTINCT lop.log_reference_id,
lop.log_timestamp
FROM
log__outbound_payment lop
JOIN log__payment_notification lpn ON lpn.reference_id = lop.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-06-09 00:00:01' AND '2024-06-09 23:59:59'
AND lpn.response_data LIKE '%Success - paid%'
AND lop.log_payment_channel = 'VA_BNI'
AND lop.log_type = 'nicepay_payment_notification'
AND lop.log_response LIKE '%Payment Success%'
AND lpn.request_data LIKE '%VIRTUAL_ACCOUNT_NICEPAY%'
AND lpn.status = 00 GROUP BY lop.log_reference_id ASC;
#== VA BRI
SELECT
COUNT(DISTINCT lop.log_reference_id)
-- lop.log_reference_id
FROM
log__outbound_payment lop
JOIN log__payment_notification lpn ON lpn.reference_id = lop.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-02-24 21:00:00' AND '2024-02-24 23:00:00'
AND lpn.response_data LIKE '%Success - paid%'
AND lop.log_payment_channel = 'VA_BRI'
AND lop.log_type = 'nicepay_payment_notification'
AND lop.log_response LIKE '%Payment Success%'
AND lpn.request_data LIKE '%VIRTUAL_ACCOUNT_NICEPAY%'
AND lpn.status = 00;
#==VA MANDIRI
SELECT
COUNT(DISTINCT lop.log_reference_id)
-- lop.log_reference_id
FROM
log__outbound_payment lop
JOIN log__payment_notification lpn ON lpn.reference_id = lop.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-03-02 17:00:00' AND '2024-03-02 17:15:00'
AND lpn.response_data LIKE '%Success - paid%'
AND lop.log_payment_channel = 'VA_MANDIRI'
AND lop.log_type = 'nicepay_payment_notification'
AND lop.log_response LIKE '%Payment Success%'
AND lpn.request_data LIKE '%VIRTUAL_ACCOUNT_NICEPAY%'
AND lpn.status = 00;
/*============================================== TIDAK AKTIF ===================================================*/
/*==============================================================================================================*/
#== JATIS
SELECT
-- COUNT(DISTINCT lop.log_reference_id)
DISTINCT lop.log_reference_id,
lop.log_timestamp
FROM
log__outbound_payment lop
JOIN log__payment_notification lpn ON lpn.reference_id = lop.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-06-09 00:00:01' AND '2024-06-09 23:59:59'
AND lpn.response_data LIKE '%Success - paid%'
AND lop.log_payment_channel = 'ATM_TRANSFER'
-- AND lop.log_type = 'settlement'
AND lop.log_response LIKE '%Success%'
AND lpn.request_data LIKE '%JATIS%'
AND lpn.status = 00 GROUP BY lop.log_reference_id ASC;
#==AKULAKU
SELECT
COUNT(DISTINCT lop.log_reference_id)
-- lop.log_reference_id
FROM
log__outbound_payment lop
JOIN log__payment_notification lpn ON lpn.reference_id = lop.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-02-19 01:00:00' AND '2024-02-19 23:00:00'
AND lpn.response_data LIKE '%Success - paid%'
AND lop.log_payment_gateway = 'AKULAKU'
AND lop.log_type = 'akulaku_confirm_receipt'
AND lop.log_reason_code = 'TRUE'
AND lpn.request_data LIKE '%AKULAKU%'
AND lpn.status = 00;
#== KLIKPAY
SELECT
COUNT(DISTINCT lop.log_reference_id)
-- lop.log_reference_id
FROM
log__outbound_payment lop
JOIN log__payment_notification lpn ON lpn.reference_id = lop.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-02-19 01:00:00' AND '2024-02-19 23:00:00'
AND lpn.response_data LIKE '%Success - paid%'
AND lop.log_payment_channel = 'KLIKPAY'
AND lop.log_type = 'payment'
AND lop.log_reason_code = '00'
AND lop.log_response LIKE '%Sukses%'
AND lpn.request_data LIKE '%KLIKPAY%'
AND lpn.status = 00;
#== ONEKLIK
SELECT
COUNT(DISTINCT lo.log_reference_id)
-- lo.log_reference_id
FROM
log__oneklik_202402 lo
JOIN log__payment_notification_202402 lpn ON lpn.reference_id = lo.log_reference_id
WHERE
1
AND lpn.log_timestamp BETWEEN '2024-07-12 10:00:01' AND '2024-07-12 14:00:01'
AND lpn.response_data LIKE '%Success - paid%'
AND lo.log_type = 'oneklik_payment'
AND lo.log_response LIKE '%SUCCESS%'
AND lpn.request_data LIKE '%ONEKLIK%'
AND lpn.status = 00;
/*==============================================================================================================*/
/*==============================================================================================================*/