Payment

/*==============================================================================================================*/
/*============================================= QUERY CC  & ORDER ==============================================*/

SELECT
    a.log_reference_id,
--     a.log_pg_name,
    c.`status`,
    CONCAT_WS(" ", "Dapet notif", SUBSTR(
        c.response_data,
        LOCATE( 'message', c.response_data ) + 9,
        LOCATE( 'timestamp', c.response_data ) - LOCATE ( 'message', c.response_data ) - 11 
    ), "dari order")
AS 'Reason' ,
    b.log_change_payment,
    CASE
    WHEN b.log_change_payment >=1 THEN 'Customer fault back to shopping cart'
    ELSE
        'Kendala intermittent, dana sudah masuk tapi tidak diteruskan ke proses selanjutnya'
    END AS 'Payment',
    b.log_timestamp
FROM
    log__credit_card a
    JOIN log__payment_activity b ON b.log_reference_id = a.log_reference_id 
    LEFT JOIN log__payment_notification c ON c.reference_id = a.log_reference_id
WHERE
    1 
    -- ## XENDIT
  	AND a.log_pg_name = 'xendit' 
 	AND a.log_service_name = 'create_charge'
	AND a.log_response LIKE '%CAPTURED%'
  	AND a.log_reference_id IN ()
    -- ## 2C2P
    OR a.log_pg_name = '2c2p'
 	AND a.log_service_name = '2c2p_payment_response'
	AND a.log_response LIKE '%Approved%'
  	AND a.log_reference_id IN ()
  	-- ## STRIPE
 	AND a.log_service_name = 'payment_notification'
	AND a.log_response = 'SUCCESS'
	AND a.`log_pg_name` = 'stripe'
	AND a.log_reference_id IN ()
    -- ## Nicepay
    OR a.log_pg_name = 'nicepay'
    AND a.log_service_name = 'payment_notification'
	AND a.log_response LIKE '%SUCCESS%'
    AND a.log_reference_id IN () GROUP BY a.log_reference_id, Payment ORDER BY b.log_timestamp ASC;
/*==============================================================================================================*/
/*=============================================== BANK TRANSFER  ===============================================*/
   
SELECT
 a.log_reference_id,
 CONCAT_WS(" ", "Dapet notif", SUBSTR(
        b.response_data,
        LOCATE('message', b.response_data) + 9,
        LOCATE('timestamp', b.response_data) - LOCATE ( 'message', b.response_data ) - 11 
    ), "dari order")
AS 'Reason',
    SUBSTR(b.request_data,
    LOCATE ('paymentSource',b.request_data)+15,
  	LOCATE('customAccount',b.request_data)-LOCATE ('paymentSource',b.request_data)-17) 
AS 'payment source',
  a.log_change_payment,
    CASE    
    WHEN a.log_change_payment >=1 THEN 'Change Payment'
    ELSE
        'Tidak ada balikan dari '
    END AS 'Payment',
    a.log_timestamp
FROM
    log__payment_activity a
    LEFT JOIN log__payment_notification b ON b.reference_id = a.log_reference_id
WHERE 1 
    AND a.log_reference_id IN ()
    AND NOT EXISTS ( SELECT * FROM log__abtc c WHERE c.log_reference_id = a.log_reference_id) GROUP BY a.log_reference_id;
/*==============================================================================================================*/
/*================================================= PAYLATER ===================================================*/
   
SELECT
	lpl.log_reference_id as 'Order Id',
	CONCAT_WS(" ", "Dapet notif", SUBSTR(
        lpn.response_data,
        LOCATE ( 'message', lpn.response_data ) + 9,
        LOCATE( 'timestamp', lpn.response_data ) - LOCATE ( 'message', lpn.response_data ) - 11 
    ), "dari order") AS 'Reason',
    SUBSTR(lpn.request_data,
    LOCATE ('paymentSource',lpn.request_data)+15,
  	LOCATE('customAccount',lpn.request_data)-LOCATE ('paymentSource',lpn.request_data)-17) 
	AS 'payment source',
	lpa.log_change_payment,
CASE
    WHEN lpa.log_change_payment >=1 THEN 'Customer fault back to shopping cart'
  	WHEN lpa.log_change_payment < 1 AND lpl.log_type <> 'payment_notification' AND lpl.log_response <> 'true' THEN'Tidak Ada Balikan Dari PAYLATER'
    END AS 'Payment',
	lpl.log_timestamp 
FROM
	log__pay_later lpl
JOIN log__payment_activity lpa ON lpa.log_reference_id = lpl.log_reference_id
LEFT JOIN  log__payment_notification lpn ON lpn.reference_id = lpl.log_reference_id 
WHERE 1
	AND lpl.log_reference_id IN () GROUP BY lpl.log_reference_id ;
	
/*=================================================================================================================*/
/*============================================== LOG OUTBOND  PAYMENT =============================================*/
    
SELECT
	a.log_reference_id,
 	CONCAT_WS(" ", "Dapet notif", SUBSTR(
        c.response_data,
        LOCATE ( 'message', c.response_data ) + 9,
        LOCATE( 'timestamp', c.response_data ) - LOCATE ( 'message', c.response_data ) - 11 
    ), "dari order")
AS 'Reason',
    SUBSTR(c.request_data,
    LOCATE ('paymentSource',c.request_data)+15,
  	LOCATE('customAccount',c.request_data)-LOCATE ('paymentSource',c.request_data)-17) 
AS 'payment source',
  	b.log_change_payment,
CASE
--     WHEN b.log_change_payment >=1 THEN 'Customer fault back to shopping cart'
  	WHEN b.log_change_payment < 1 AND a.log_payment_channel = "ATM_TRANSFER" THEN'Tidak Ada Balikan Dari JATIS'
  	WHEN b.log_change_payment < 1 AND a.log_type = 'nicepay_payment_notification' AND a.log_reason_code = 14 THEN 'Kendala internal, sudah di rollback per tanggal 17 oktober 2023'
    WHEN b.log_change_payment < 1 AND a.log_payment_gateway IN ("E2PAY") THEN 'Tidak Ada Balikan Dari E2PAY'
    WHEN b.log_change_payment < 1 AND a.log_payment_channel IN ("H2H") THEN 'Tidak Ada Balikan Dari BCA'
    WHEN b.log_change_payment < 1 AND a.log_payment_gateway IN ("MIDTRANS") THEN 'Tidak Ada Balikan Dari MIDTRANS' -- SHOPEE_PAY
    WHEN b.log_change_payment < 1 AND a.log_payment_channel IN ("SHOPEE_PAY") THEN 'Tidak Ada Balikan Dari SHOPEE PAY'
    WHEN b.log_change_payment < 1 AND a.log_payment_channel IN ("AKULAKU") THEN 'Tidak Ada Balikan Dari AKULAKU'
    ELSE
        CONCAT_WS(" ", 'Tidak ada balikan dari', a.log_payment_channel)
    END AS 'Payment',
        b.log_timestamp
FROM
 	log__outbound_payment a
JOIN log__payment_activity b ON b.log_reference_id = a.log_reference_id
LEFT JOIN log__payment_notification c ON c.reference_id = a.log_reference_id
  	WHERE 1 
    AND a.log_reference_id IN () GROUP BY a.log_reference_id, Payment ORDER BY a.log_reference_id;
    
/*=================================================================================================================*/
/*=========================================== QUERY PAYMENT NOTIF ORDER ===========================================*/

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',
a.log_timestamp 
	FROM
    	log__payment_notification a 
	WHERE   1
	AND a.reference_id IN () GROUP BY a.reference_id;