Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT *
- FROM lc_woocommerce_order_items
- LEFT JOIN lc_woocommerce_order_itemmeta ON lc_woocommerce_order_items.order_item_id = lc_woocommerce_order_itemmeta.order_item_id
- WHERE lc_woocommerce_order_items.order_id = 10737
- AND (meta_key IN ('_line_total', '_qty'))
- SELECT pc.post_title AS coupon_name,
- pc.post_excerpt AS coupon_description,
- Max(CASE WHEN pmc.meta_key = 'discount_type' AND pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS discount_type,
- Max(CASE WHEN pmc.meta_key = 'coupon_amount' AND pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS coupon_amount,
- Max(CASE WHEN pmc.meta_key = 'product_ids' AND pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS product_ids,
- Max(CASE WHEN pmc.meta_key = 'product_categories' AND pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS product_categories,
- Max(CASE WHEN pmc.meta_key = 'customer_email' AND pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS customer_email,
- Max(CASE WHEN pmc.meta_key = 'usage_limit' AND pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS usage_limit,
- Max(CASE WHEN pmc.meta_key = 'usage_count' AND pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS total_usaged,
- po.ID AS order_id,
- MAX(CASE WHEN pmo.meta_key = '_billing_email' AND po.ID = pmo.post_id THEN pmo.meta_value END) AS billing_email,
- MAX(CASE WHEN pmo.meta_key = '_billing_first_name' AND po.ID = pmo.post_id THEN pmo.meta_value END) AS billing_first_name,
- MAX(CASE WHEN pmo.meta_key = '_billing_last_name' AND po.ID = pmo.post_id THEN pmo.meta_value END) AS billing_last_name,
- MAX(CASE WHEN pmo.meta_key = '_order_total' AND po.ID = pmo.post_id THEN pmo.meta_value END) AS order_total
- FROM `lc_posts` AS pc
- INNER JOIN `lc_postmeta` AS pmc ON pc.`ID` = pmc.`post_id`
- INNER JOIN `lc_woocommerce_order_items` AS woi ON pc.post_title = woi.order_item_name
- AND woi.order_item_type = 'coupon'
- INNER JOIN `lc_posts` AS po ON woi.order_id = po.ID
- AND po.post_type = 'shop_order'
- AND po.post_status IN ('wc-completed', 'wc-processing', 'wc-refunded')
- INNER JOIN `lc_postmeta` AS pmo ON po.ID = pmo.post_id
- WHERE pc.post_type = 'shop_coupon'
- GROUP BY po.ID
- ORDER BY pc.ID DESC,po.ID DESC
- LIMIT 0, 10
- SELECT p.`ID`,
- p.`post_title` AS coupon_code,
- p.`post_excerpt` AS coupon_description,
- Max(CASE WHEN pm.meta_key = 'discount_type' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS discount_type, -- Discount type
- Max(CASE WHEN pm.meta_key = 'coupon_amount' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS coupon_amount, -- Coupon amount
- Max(CASE WHEN pm.meta_key = 'free_shipping' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS free_shipping, -- Allow free shipping
- Max(CASE WHEN pm.meta_key = 'expiry_date' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS expiry_date, -- Coupon expiry date
- Max(CASE WHEN pm.meta_key = 'minimum_amount' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS minimum_amount, -- Minimum spend
- Max(CASE WHEN pm.meta_key = 'maximum_amount' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS maximum_amount, -- Maximum spend
- Max(CASE WHEN pm.meta_key = 'individual_use' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS individual_use, -- Individual use only
- Max(CASE WHEN pm.meta_key = 'exclude_sale_items' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS exclude_sale_items, -- Exclude sale items
- Max(CASE WHEN pm.meta_key = 'product_ids' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS product_ids, -- Products
- Max(CASE WHEN pm.meta_key = 'exclude_product_ids'AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS exclude_product_ids, -- Exclude products
- Max(CASE WHEN pm.meta_key = 'product_categories' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS product_categories, -- Product categories
- Max(CASE WHEN pm.meta_key = 'exclude_product_categories' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS exclude_product_categories,-- Exclude Product categories
- Max(CASE WHEN pm.meta_key = 'customer_email' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS customer_email, -- Email restrictions
- Max(CASE WHEN pm.meta_key = 'usage_limit' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS usage_limit, -- Usage limit per coupon
- Max(CASE WHEN pm.meta_key = 'usage_limit_per_user' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS usage_limit_per_user, -- Usage limit per user
- Max(CASE WHEN pm.meta_key = 'usage_count' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS total_usaged -- Usage count
- FROM `lc_posts` AS p INNER JOIN `lc_postmeta` AS pm ON p.`ID` = pm.`post_id`
- WHERE p.`post_type` = 'shop_coupon'
- AND p.`post_status` = 'publish'
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement