Untitled

/*==============================================================================================================*/
/*================================================== 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;

/*==============================================================================================================*/
/*==============================================================================================================*/