Untitled

SELECT 
    k.transaction_date,
    COALESCE(k.merchant_name, '-') AS `merchant`,
    COALESCE(k.merchant_transaction_number, '-') AS `merchant_trx_no`,
    CASE 
        WHEN m.brand_name IS NOT NULL THEN m.brand_name 
        ELSE '' 
    END AS `brand_name`,
    k.user_name AS `customer_name`,
    COALESCE(k.user_phone, 0) AS `customer_id`,
    k.transaction_no AS `transaction_id`,
    k.rrn AS `ref_no`,
    k.status AS `status`,
    COALESCE(k.failed_reason, '-') AS `reason`,
    CASE
        WHEN k.transaction_type = 'MEMBERSHIP_PAYMENT' AND k.store_id = 'EDUCATION' THEN 'PAYMENT_IDN'
        WHEN k.transaction_type = 'PAYMENT_PPOB' THEN k.produk_ppob_type
        ELSE CASE
            WHEN k.transaction_type LIKE 'QR_TAP%' THEN 'QR_TAP'
            ELSE k.transaction_type
        END
    END AS `transaction_type`,
    COALESCE(k.nama_produk, '-') AS `product_name`,
    CASE
        WHEN k.transaction_type = 'TOPUP_TRANSFER_BANK' THEN REPLACE(k.store_name, 'BANK', '')
        WHEN k.transaction_type = 'TRANSFER_TO_BANK' THEN COALESCE(REPLACE(mb.nama_bank, 'BANK', ''), '-')
        WHEN k.card_no IS NOT NULL THEN 'Bank Not Found'
        ELSE '-'
    END AS `bank`,
    CASE
        WHEN k.payment_option_name = 'CREDIT_CARD' AND k.card_model IN ('CREDIT_CARD', 'DEBIT_CARD') THEN k.card_model
        ELSE k.payment_option_name
    END AS `payment_method`,
    CASE
        WHEN k.transaction_type LIKE '%DOUBLE%' THEN 'DOUBLE'
        WHEN k.transaction_type LIKE '%SINGLE%' THEN 'SINGLE'
        ELSE '-'
    END AS `tap_type`,
    CASE
        WHEN k.transaction_type REGEXP 'N[0-9]{3}' THEN 
            SUBSTRING(
                k.transaction_type,
                LENGTH(k.transaction_type) - LOCATE('N', REVERSE(k.transaction_type)) + 1, 
                4
            )
        ELSE '-'
    END AS `product_indicator`,
    CASE
        WHEN k.transaction_type REGEXP 'SINGLE' THEN 'ONLY PAYMENT'
        WHEN k.transaction_type REGEXP 'N003|N006' THEN 'TAP IN'
        WHEN k.transaction_type REGEXP 'N004|N007' THEN 'TAP OUT'
        WHEN k.transaction_type REGEXP 'N005' THEN 'WITHOUT TAPOUT'
        WHEN k.transaction_type LIKE '%RELEASE%' THEN 'RELEASE'
        ELSE '-'
    END AS `message`,
    k.ref2 AS `reff_number_2`,
    CASE 
        WHEN k.transaction_type REGEXP 'N003|N006' THEN k.total_amount
        ELSE 0
    END AS `hold_amount`,
    CASE
        WHEN k.transaction_type LIKE '%RELEASE%' THEN k.total_amount
        ELSE 0
    END AS `release`,
    CASE
        WHEN k.transaction_type LIKE 'QR_TAP%' THEN
            CASE
                WHEN k.transaction_type REGEXP 'N004|N007' THEN k.total_amount
                WHEN k.transaction_type LIKE '%SINGLE%' THEN k.total_amount
                ELSE 0
            END
        ELSE
            CASE
                WHEN k.status != 'PAID' AND k.transaction_type = 'TOPUP_TRANSFER_BANK' 
                    THEN COALESCE(k.total_amount, 0) + COALESCE(k.admin_fee, 0)
                WHEN k.status != 'PAID' AND k.transaction_type = 'TOPUP_BLU' 
                    THEN COALESCE(k.total_amount, 0) - COALESCE(k.admin_fee, 0)
                WHEN k.status != 'PAID' 
                    THEN COALESCE(k.total_amount, 0)
                WHEN k.merchant_name = 'PasarInd' AND k.transaction_type IN ('QR_CASHIER', 'QR_DYNAMIC') 
                    THEN COALESCE(k.total_amount, 0) - COALESCE(k.other_fee, 0)
                WHEN k.merchant_name = 'PasarInd' 
                    THEN COALESCE(k.total_amount, 0) - COALESCE(k.admin_fee, 0)
                WHEN k.transaction_type = 'TOPUP_TRANSFER_BANK' 
                    THEN COALESCE(k.total_amount, 0)
                WHEN k.transaction_type = 'TOPUP_BLU' 
                    THEN COALESCE(k.total_amount, 0) - COALESCE(k.admin_fee, 0)
                WHEN k.transaction_type = 'TOPUP_ONE_KLIK' 
                    THEN COALESCE(k.total_amount, 0)
                WHEN k.status = 'FAILED' 
                    THEN COALESCE(k.total_amount, 0) - COALESCE(k.admin_fee, 0)
                ELSE
                    CASE
                        WHEN COALESCE(k.produk_ppob_type, k.kode_produk) IN (
                            'PAKET_DATA', 'PBB', 'PDAM', 'PLNPASC', 'PLNPRA',
                            'PULSA', 'PULSA_PASC', 'RESIDENTIAL', 'TELKOM', 'TV KABEL'
                        ) THEN 
                            COALESCE(k.total_amount, 0) + COALESCE(k.discount, 0)
                            - COALESCE(k.bayarind_fee, 0) - COALESCE(k.other_fee, 0)
                        WHEN COALESCE(k.biller_fee, 0) > 0 THEN
                            CASE
                                WHEN COALESCE(k.admin_fee, 0) = 0 THEN 
                                    COALESCE(k.total_amount, 0) + COALESCE(k.discount, 0)
                                    - COALESCE(k.bayarind_fee, 0) - COALESCE(k.other_fee, 0)
                                    + COALESCE(k.biller_fee, 0)
                                ELSE 
                                    COALESCE(k.total_amount, 0) + COALESCE(k.discount, 0)
                                    - COALESCE(k.admin_fee, 0) + COALESCE(k.biller_fee, 0)
                            END
                        ELSE
                            CASE
                                WHEN COALESCE(k.admin_fee, 0) = 0 THEN 
                                    COALESCE(k.total_amount, 0) + COALESCE(k.discount, 0)
                                    - COALESCE(k.bayarind_fee, 0) - COALESCE(k.other_fee, 0)
                                ELSE 
                                    COALESCE(k.total_amount, 0) + COALESCE(k.discount, 0)
                                    - COALESCE(k.admin_fee, 0)
                            END
                    END
            END
    END AS `price`,
    k.bank_fee,
    CASE
        WHEN k.status != 'PAID' THEN 0
        WHEN k.merchant_name = 'PasarInd' THEN 
            CASE
                WHEN k.store_id = 'EDUCATION' THEN k.bayarind_fee
                WHEN k.transaction_type = 'ORDER' THEN k.bayarind_fee
                ELSE k.admin_fee
            END
        ELSE 
            CASE
                WHEN k.produk_ppob_type = 'GAME ONLINE'
                  OR k.transaction_type IN ('TOPUP_TRANSFER_BANK', 'TOPUP_ONE_KLIK') 
                    THEN k.admin_fee
                ELSE k.bayarind_fee
            END
    END AS `servicer_fee`,
    k.discount,
    CASE
        WHEN k.transaction_type != 'TOPUP_TRANSFER_BANK' THEN k.other_fee
        ELSE 0
    END AS `other_fee`,
    CASE
        WHEN k.status != 'PAID' THEN 0
        WHEN k.merchant_name = 'PasarInd' THEN
            CASE
                WHEN k.store_id = 'EDUCATION' THEN k.bayarind_fee
                WHEN k.transaction_type = 'ORDER' THEN k.bayarind_fee
                ELSE k.admin_fee
            END
        WHEN k.produk_ppob_type = 'GAME ONLINE'
            OR k.transaction_type IN ('TOPUP_TRANSFER_BANK', 'TOPUP_ONE_KLIK', 'TOPUP_BLU') THEN k.admin_fee
        ELSE k.bayarind_fee
    END AS `bayarind_fee`,
    CASE
        WHEN k.transaction_type = 'TOPUP_TRANSFER_BANK' THEN k.other_fee
        ELSE 0
    END AS `client_fee`,
    CASE
        WHEN k.status != 'PAID' THEN 
            CASE
                WHEN k.transaction_type = 'TOPUP_TRANSFER_BANK' THEN k.total_amount + k.admin_fee
                ELSE k.total_amount
            END
        WHEN k.transaction_type IN ('TOPUP_ONE_KLIK', 'TOPUP_TRANSFER_BANK') THEN k.total_amount + k.admin_fee
        ELSE k.total_amount
    END AS `total_amount`,
    CASE
        WHEN pd.delivery_vendor_type IS NULL THEN '-'
        ELSE pd.delivery_vendor_type
    END AS `courier_type`,
    COALESCE(pd.shipment_method, '-') AS `delivery_type`,
    COALESCE(pd.booking_id, '-') AS `delivery_code`,
    COALESCE(pd.amount, 0) AS `shipping_fee`,
    CASE
        WHEN pd.amount IS NOT NULL THEN 'On Procces'
        ELSE pd.status
    END AS `delivery_status`,
    CASE
        WHEN k.payment_option_name = 'QRIS' AND k.status = 'PAID' AND JSON_VALID(k.additional_data) 
            THEN CONVERT(JSON_UNQUOTE(JSON_EXTRACT(k.additional_data, '$.approvalCode')) USING utf8mb4) COLLATE utf8mb4_unicode_ci
        WHEN k.payment_option_name != 'QRIS' AND k.bank_auth_code IS NOT NULL 
            THEN CONVERT(k.bank_auth_code USING utf8mb4) COLLATE utf8mb4_unicode_ci
        ELSE '-' COLLATE utf8mb4_unicode_ci
    END AS `approval_code`,
    CASE
        WHEN k.payment_option_name = 'QRIS' AND k.status = 'PAID' AND JSON_VALID(k.additional_data) 
            THEN CONVERT(JSON_UNQUOTE(JSON_EXTRACT(k.additional_data, '$.merchantId')) USING utf8mb4) COLLATE utf8mb4_unicode_ci
        ELSE '' COLLATE utf8mb4_unicode_ci
    END AS `mid`,
    CASE
        WHEN k.transaction_type = 'TRANSFER_TO_BANK' THEN
            CASE
                WHEN (tba.ba_nama_rekening IS NULL OR tba.ba_nama_rekening = '') THEN '-'
                ELSE tba.ba_nama_rekening
            END
        ELSE ''
    END AS `transfer_to`,
    CASE
        WHEN k.transaction_type = 'TRANSFER_TO_BANK' THEN
            CASE
                WHEN (tba.ba_no_rekening IS NULL OR tba.ba_no_rekening = '') THEN '-'
                ELSE tba.ba_no_rekening
            END
        ELSE ''
    END AS `transfer_number`,
    CASE
        WHEN k.transaction_type IN ('QR_CASHIER', 'QR_DYNAMIC')
          AND JSON_VALID(k.additional_data)
          AND k.additional_data != ''
          AND JSON_UNQUOTE(JSON_EXTRACT(k.additional_data, '$.merchantCriteria')) IS NOT NULL
            THEN CONVERT(JSON_UNQUOTE(JSON_EXTRACT(k.additional_data, '$.merchantCriteria')) USING utf8mb4) COLLATE utf8mb4_unicode_ci
        ELSE '-' COLLATE utf8mb4_unicode_ci
    END AS `merchant_criteria`,
    CASE
        WHEN k.transaction_type LIKE 'QR_TAP%' AND JSON_VALID(k.additional_data) 
            THEN CONVERT(JSON_UNQUOTE(JSON_EXTRACT(k.additional_data, '$.mcc')) USING utf8mb4) COLLATE utf8mb4_unicode_ci
        ELSE '-' COLLATE utf8mb4_unicode_ci
    END AS `mcc`,
    CASE
        WHEN k.transaction_type = 'TRANSFER_TO_BANK' THEN
            CASE
                WHEN up.is_ewa_employee = 1 THEN 'EWA'
                ELSE ''
            END
        ELSE ''
    END AS `user_type`,
    k.produk_ppob_type,
    DATE_FORMAT(NOW(6), '%Y-%m-%d %H:%i:%s.%f') AS `created_at`,
    DATE_FORMAT(NOW(6), '%Y-%m-%d %H:%i:%s.%f') AS `updated_at`
FROM `sprint_money_trx_v2`.`t_payment` AS `k`
LEFT JOIN `sprint_money_account_v2`.`t_merchant` AS `m`
    ON `k`.`binding_merchant_id` = `m`.`id_`
LEFT JOIN `sprint_money_account_v2`.`t_bank_account` AS `tba`
    ON `k`.`additional_data` = `tba`.`ba_no_rekening`
LEFT JOIN `sprint_money_account_v2`.`m_bank` AS `mb`
    ON `mb`.`id_` = `tba`.`bank_id`
LEFT JOIN `sprint_money_trx_v2`.`t_payment_delivery` AS `pd`
    ON `pd`.`transaction_no` = `k`.`transaction_no`
LEFT JOIN `sprint_money_account_v2`.`t_user_profile` AS `up`
    ON `up`.`no_hp` = `k`.`user_phone`
ORDER BY `k`.`transaction_date` DESC
LIMIT 1;