a1

SELECT
	a.log_reference_id as 'Order Id',
	a.log_pg_name as 'Payment',
	SUBSTR(
        a.log_response,
        LOCATE ( 'status', a.log_response ) + 9,
        LOCATE( 'authorized_amount', a.log_response ) - LOCATE ( 'status', a.log_response ) - 12
    )
 AS 'Response',
	 SUBSTR(
	        a.log_response,
	        LOCATE ( 'card_type', a.log_response ) + 12,
	        LOCATE( 'xid', a.log_response ) - LOCATE ( 'card_type', a.log_response ) - 15
	    )
AS 'Type',
	a.log_timestamp 
FROM
	log__credit_card a
WHERE 
	a.log_timestamp BETWEEN '2024-06-09 00:00:01' AND '2024-06-09 23:59:59'
 	AND a.log_service_name = 'create_charge'
	AND a.log_response LIKE '%CAPTURED%'
	AND a.log_pg_name = 'xendit'
	AND NOT EXISTS ( SELECT * FROM log__payment_notification lpn WHERE lpn.reference_id = a.log_reference_id);