在WooCommerce中列出带有订单详细信息的优惠券 [英] List Coupon with order details in WooCommerce

问题描述

我有一个包含1000张优惠券的网站。所有优惠券的使用限制均为一。我使用Raunuk Gupta提供的代码直接从SQL数据库导出优惠券。

I have a site with 1000 coupons. All the coupons have a usage limit of one. Im using the code provided by Raunuk Gupta to export coupons directly from the SQL database.

WooCommerce优惠券如何存储在数据库中？

是否可以检索使用优惠券的用户的订单元？我想在报告中包括用户名，电子邮件地址，以及其他可能的自定义字段。

Is it possible to retrieve order meta of the user that used the coupon? I would like to include in the report the user's name, email address and possibly some other custom fields.

谢谢。

• 我们生成1000张优惠券并将其提供给公司（客户）。


• 每个优惠券的使用限制为1。


• 公司然后在其成员之间分发优惠券。


• 在月底，我们需要向公司发送报告告知他们：
  
  

• We generate 1000 coupons and provide it to a company ( client ).
• Each coupon has a usage limit of 1.
• The company then distribute the coupons among their members.
• At the end of the month, we need to send a report to the company telling them:

1. 使用了1000张优惠券中的多少张。 （可以使用上面的链接提供的代码来完成此操作）。


2. 然后从已使用的优惠券中，告诉他们使用的优惠券，换句话说，下订单的用户。

链接到示例卓越出口
-黄色字段来自优惠券
-蓝色字段来自ORDER

LINK TO SAMPLE EXCEL EXPORT -The Yellow fields are from the COUPONS -The Blue fields are from the ORDER

推荐答案

下面的MySQL查询将列出与Order关联的所有优惠券。

Below MySQL query will list all your coupon associated with Order.

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.

希望有帮助！

这篇关于在WooCommerce中列出带有订单详细信息的优惠券的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！