Payment
unknown
mysql
a month ago
7.0 kB
5
Indexable
Never
/*==============================================================================================================*/ /*============================================= 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;
Leave a Comment