SELECT p.post_date AS DATE, 
p.id AS invoice_id, 
(
  CASE
    WHEN m5.meta_value THEN 'subscription'
    WHEN m4.meta_value > 0 THEN 'renewal'
        ELSE 'new_order'
  END
) AS type_invoice, 
ifnull(plm.license_key, '-') AS license, 
ifnull(concat(m5.meta_value, ' ', m6.meta_value), (
  ifnull((SELECT GROUP_CONCAT(DISTINCT meta_value SEPARATOR ' ') AS masa_aktif FROM wp_postmeta WHERE post_id IN (SELECT meta_value FROM wp_postmeta WHERE post_id=p.ID AND meta_key='_subscription_renewal' ) AND (meta_key='_billing_interval' OR meta_key='_billing_period') LIMIT 1),
    ifnull((SELECT GROUP_CONCAT(DISTINCT meta_value SEPARATOR ' ') AS masa_aktif FROM wp_postmeta WHERE post_id IN (SELECT meta_value FROM wp_postmeta WHERE post_id=p.ID AND meta_key='_subscription_switch' ) AND (meta_key='_billing_interval' OR meta_key='_billing_period') LIMIT 1),
      ifnull((SELECT GROUP_CONCAT(DISTINCT meta_value SEPARATOR ' ') AS masa_aktif FROM wp_postmeta WHERE post_id IN (SELECT id FROM wp_posts WHERE post_parent=p.ID AND post_type = 'shop_subscription' ) AND (meta_key='_billing_interval' OR meta_key='_billing_period') LIMIT 1),'-')
        )
    )
)) AS masa_aktif,
REPLACE(p.post_status, 'wc-', '') AS STATUS, 
ifnull(m2.meta_value, '-') AS currency, 
ifnull(m3.meta_value, '-') AS total
 
FROM wp_posts p
JOIN wp_postmeta m ON p.id=m.post_id
LEFT JOIN wp_postmeta m2 ON p.id=m2.post_id AND m2.meta_key='_order_currency' 
LEFT JOIN wp_postmeta m3 ON p.id=m3.post_id AND m3.meta_key='_order_total'
LEFT JOIN wp_postmeta m4 ON p.id=m4.post_id AND m4.meta_key='_subscription_renewal' 
LEFT JOIN wp_postmeta m5 ON p.id=m5.post_id AND m5.meta_key='_billing_interval'
LEFT JOIN wp_postmeta m6 ON p.id=m6.post_id AND m6.meta_key='_billing_period'
LEFT JOIN wp_plm_manager_keys plm ON p.id=plm.txn_id
WHERE  m.meta_key='_customer_user' AND m.meta_value=(SELECT ID FROM wp_users WHERE user_email = ? LIMIT 1) ORDER BY p.id DESC;