Untitled
unknown
mysql
18 days ago
16 kB
8
Indexable
Never
/*==============================================================================================================*/ /*================================================== 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; /*==============================================================================================================*/ /*==============================================================================================================*/