SELECT oitems.product_qty as qty, oitems.product_id, oitems.order_id, oitems.order_item_id as item_id,
itemmeta.type_cut, itemmeta.house, itemmeta.sex, itemmeta.size, itemmeta.is_alumni, itemmeta.grad_year, itemmeta.print_color, itemmeta.print_color2,
itemmeta.zipper, itemmeta.is_collected,
posts.post_status as order_status, posts.post_date as order_date,
pm.order_type, pm.order_total, pm.currency, pm.bfname, pm.blname, pm.bemail, pm.bphone, pm.txn_mode, pm.txn_number
FROM `wp_j1_wc_order_product_lookup` as oitems
left join `wp_j1_posts` as posts on posts.ID = oitems.order_id
left join (
select order_item_id ,
max(case when meta_key = 'type-of-cut' then meta_value end) `type_cut`,
max(case when meta_key = 'size' then meta_value end) `size`,
max(case when meta_key = 'Graduation Year' then meta_value end) grad_year,
max(case when meta_key = 'Alumni' then meta_value end) is_alumni,
max(case when meta_key = 'Print color' then meta_value end) print_color,
max(case when meta_key = 'print-color' then meta_value end) print_color2,
max(case when meta_key = 'Zipper' then meta_value end) zipper,
max(case when meta_key = 'House' then meta_value end) house,
max(case when meta_key = 'Sex' then meta_value end) sex,
max(case when meta_key = 'is_collected' then meta_value end) is_collected
from
wp_j1_woocommerce_order_itemmeta group by order_item_id
) as itemmeta on itemmeta.order_item_id = oitems.order_item_id
left join (
select post_id,
max(case when meta_key = '_payment_method' then meta_value end) order_type,
max(case when meta_key = '_order_total' then meta_value end) order_total,
max(case when meta_key = '_order_currency' then meta_value end) currency,
max(case when meta_key = '_billing_first_name' then meta_value end) bfname,
max(case when meta_key = '_billing_last_name' then meta_value end) blname,
max(case when meta_key = '_billing_email' then meta_value end) bemail,
max(case when meta_key = '_billing_phone' then meta_value end) bphone,
max(case when meta_key = '_ac_users_txn_mode' then meta_value end) txn_mode,
max(case when meta_key = '_ac_users_txn_number' then meta_value end) txn_number
from wp_j1_postmeta group by post_id
) as pm on pm.post_id = oitems.order_id
Where posts.post_status not in ( 'trash', 'wc-cancelled', 'wc-failed' ) order by pm.bfname, posts.ID desc, oitems.order_item_id asc
