在woocommerce中列出包含订单详细信息的优惠券

l0oc07j2  于 2021-06-18  发布在  Mysql
关注(0)|答案(1)|浏览(297)

我有一个有1000张优惠券的网站。所有的优惠券都有一个使用限制。我使用raunukgupta提供的代码直接从sql数据库导出优惠券。
如何在数据库中存储优惠券?
是否可以检索使用优惠券的用户的订单元?我想包括在报告中的用户名,电子邮件地址和可能的一些其他自定义字段。
谢谢您。
我们生成1000张优惠券并提供给一家公司(客户)。
每张优惠券的使用限制为1。
然后公司将优惠券分发给会员。
月底,我们需要向公司发送一份报告,告诉他们:
1000张优惠券中有多少张被使用了这可以通过上面链接提供的代码来完成)
然后从使用过的优惠券中,我们需要告诉他们谁使用了优惠券,换句话说,就是下订单的用户的姓名和电子邮件地址。
链接到示例excel导出-黄色字段来自优惠券-蓝色字段来自订单

fkaflof6

fkaflof61#

下面mysql查询将列出所有与订单相关联的优惠券。

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 `wp_posts` AS pc
INNER JOIN `wp_postmeta` AS pmc ON  pc.`ID` = pmc.`post_id`
INNER JOIN `wp_woocommerce_order_items` AS woi ON pc.post_title = woi.order_item_name
    AND woi.order_item_type = 'coupon'
INNER JOIN `wp_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') -- Added needed order status over here.
INNER JOIN `wp_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 -- modify it accordingly.

希望这有帮助!

相关问题