我有一个有 1000 张优惠券的网站。所有优惠券的使用限额均为一张。我使用 Raunuk Gupta 提供的代码直接从 SQL 数据库导出优惠券。
WooCommerce 优惠券如何存储在数据库中 ?
是否可以检索使用优惠券的用户的订单元?我想在报告中包含用户的姓名、电子邮件地址以及可能的其他一些自定义字段。
谢谢。
- 我们生成 1000 张优惠券并将其提供给公司(客户)。
- 每张优惠券的使用限额为 1 次。
- 然后该公司向其会员分发优惠券。
- At the end of the month, we need to send a report to the company telling them:
- 1000张优惠券用了多少张。 (这可以使用上面链接中提供的代码来完成)
- 然后,从已使用的优惠券中,我们需要告诉他们谁使用了它,换句话说,就是下订单的用户的姓名和电子邮件地址。
Excel 导出示例的链接- 黄色字段来自优惠券
- 蓝色字段来自订单
下面的 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.
希望这可以帮助!
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)