Cacher is the code snippet organizer for pro developers

# Cek pembiayaan baru - Status wc-on-hold
/*
'wc-pending'    => _x( 'Pending payment', 'Order status', 'woocommerce' ),
'wc-processing' => _x( 'Processing', 'Order status', 'woocommerce' ),
'wc-on-hold'    => _x( 'On hold', 'Order status', 'woocommerce' ),
'wc-completed'  => _x( 'Completed', 'Order status', 'woocommerce' ),
'wc-cancelled'  => _x( 'Cancelled', 'Order status', 'woocommerce' ),
'wc-refunded'   => _x( 'Refunded', 'Order status', 'woocommerce' ),
'wc-failed'     => _x( 'Failed', 'Order status', 'woocommerce' ),
 */

/*
1. Query daftar pengajuan Pembiayaan (Done)
2. Query total amount
3. Query user baru (Done)
*/

# Query Pengajuan Pembiayaan
SELECT
    p.ID as order_id,
    p.post_status,
    p.post_date,
    max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as email,
    max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as first_name,
    max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as last_name,
    max( CASE WHEN pm.meta_key = '_billing_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as address,
    max( CASE WHEN pm.meta_key = '_billing_city' and p.ID = pm.post_id THEN pm.meta_value END ) as kota,
    max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
    max( CASE WHEN pm.meta_key = '_paid_date' and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date,
    (select group_concat(order_item_name separator '|') from ohv5pu_woocommerce_order_items where order_id = p.ID) as order_items
FROM
    ohv5pu_posts p
    join ohv5pu_postmeta pm on p.ID = pm.post_id
    join ohv5pu_woocommerce_order_items oi on p.ID = oi.order_id
WHERE
    post_type = 'shop_order'
#     and post_status = 'wc-pending'
#     and post_status = 'wc-completed'
GROUP BY
    p.ID
 ORDER BY
    p.post_date DESC;


# Query Cek email
select max( CASE WHEN meta_key = '_billing_email' THEN meta_value END ) as email2 from ohv5pu_postmeta;

# Query total amount
SELECT
    p.ID as order_id,
    p.post_status,
    p.post_date,
    max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as email2,
    max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as first_name,
    max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as last_name,
    max( CASE WHEN pm.meta_key = '_billing_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as address,
    max( CASE WHEN pm.meta_key = '_billing_city' and p.ID = pm.post_id THEN pm.meta_value END ) as kota,
    max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
    max( CASE WHEN pm.meta_key = '_paid_date' and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date,
    (select group_concat(order_item_name separator '|') from ohv5pu_woocommerce_order_items where order_id = p.ID) as order_items
FROM
    ohv5pu_posts p
    join ohv5pu_postmeta pm on p.ID = pm.post_id
    join ohv5pu_woocommerce_order_items oi on p.ID = oi.order_id
WHERE
    post_type = 'shop_order'
    and post_status = 'wc-completed'
#     and pm.meta_key = '_billing_email' ='admin@ethisindonesia.com'
GROUP BY
    p.ID
 ORDER BY
    p.post_date DESC;


# Cek User dan Query user baru
SELECT * FROM ohv5pu_users ORDER BY user_registered DESC;
SELECT COUNT(ID) FROM ohv5pu_users ORDER BY user_registered DESC;
SELECT * FROM ohv5pu_users where user_email = 'admin@ethisindonesia.com';
SELECT `ID`,`user_login`,`user_pass`,`user_nicename`,`user_email`,`user_registered`,`display_name`
FROM `ohv5pu_users` ORDER BY `user_registered` DESC;

# Query Total
SELECT post_id AS order_number, meta_value AS order_total FROM ohv5pu_postmeta WHERE meta_key = '_order_total' ORDER BY post_id;

#
SELECT order_item_meta_2.meta_value as product_id,
SUM( order_item_meta.meta_value ) as line_total FROM ohv5pu_woocommerce_order_items as order_items
LEFT JOIN ohv5pu_woocommerce_order_itemmeta as order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id
LEFT JOIN ohv5pu_woocommerce_order_itemmeta as order_item_meta_2 ON order_items.order_item_id = order_item_meta_2.order_item_id
LEFT JOIN ohv5pu_posts AS posts ON order_items.order_id = posts.ID
LEFT JOIN ohv5pu_term_relationships AS rel ON posts.ID = rel.object_ID
LEFT JOIN ohv5pu_term_taxonomy AS tax USING( term_taxonomy_id )
LEFT JOIN ohv5pu_terms AS term USING( term_id )
WHERE
	    posts.post_type 	= 'shop_order'
	AND	posts.post_status 	= 'publish'
	AND	tax.taxonomy		= 'shop_order_status'
	AND	term.slug IN ('completed', 'processing', 'on-hold' )
	AND	order_items.order_item_type = 'line_item'
	AND	order_item_meta.meta_key = '_line_total'
	AND	order_item_meta_2.meta_key = '_product_id'
	GROUP BY
order_item_meta_2.meta_value;


# ===================================

select
#        xeys.*,
#        xval.*,
       xcom.*,
       xpmeta.*,
       xumeta.*
from
#     ohv5pu_metakey as xeys,
#     ohv5pu_usermeta as xval,
    ohv5pu_commentmeta as xcom,
    ohv5pu_postmeta as xpmeta,
    ohv5pu_usermeta as xumeta
;