Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
WooCommerce Downloadable Files: Grant Access for New Files to Previous Customers
/* create temporary table to hold values; ONLY RUN ONCE or you will lose temporary data */
DROP TEMPORARY TABLE IF EXISTS `temp_wp_woocommerce_downloadable_product_permissions`;
CREATE TEMPORARY TABLE IF NOT EXISTS `temp_wp_woocommerce_downloadable_product_permissions` (
`download_id` VARCHAR(32) NOT NULL,
`product_id` BIGINT(20) UNSIGNED NOT NULL,
`order_key` VARCHAR(200) NOT NULL,
`user_email` VARCHAR(200) NOT NULL,
`downloads_remaining` VARCHAR(9) DEFAULT NULL,
`access_granted` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
`access_expires` DATETIME DEFAULT NULL,
KEY `download_order_product` (`download_id`,`order_id`,`product_id`),
KEY `download_order_key_product` (`product_id`,`order_id`,`order_key`,`download_id`),
KEY `order_id` (`order_id`)
/* set product, date, and other variables here */
/* run all the queries in this file once for every product/variation/download file combination */
SET @product_id = 100; /* single product or variation ID integer to include */
SET @first_order_id = 100; /* integer order ID of the first order to include */
SET @last_order_id = 200; /* integer order ID of the last order to include */
SET @first_order_date = '2017-07-01'; /* first date to include in YYYY-MM-DD format */
SET @last_order_date = '2017-12-31'; /* last date to include in YYYY-MM-DD format */
SET @downloads_remaining = ''; /* set to integer to limit number of downloads or empty string for unlimited downloads */
SET @download_id = 'aaaaaaaa11111111aaaaaaaa11111111'; /* download hash for the new product */
/* add all eligible customers to temporary table */
INSERT INTO `temp_wp_woocommerce_downloadable_product_permissions`
(`download_id`, `product_id`, `order_id`, `order_key`, `user_email`, `user_id`, `downloads_remaining`, `access_granted`, `access_expires`)
SELECT @download_id AS download_id, @product_id AS product_id, posts.ID AS order_id, meta1.meta_value AS order_key, meta2.meta_value AS user_email, users.ID AS user_id, @downloads_remaining AS downloads_remaining, permissions.access_granted AS access_granted,
permissions.access_expires AS access_expires
FROM wp_posts posts
JOIN wp_postmeta meta1 ON posts.ID = meta1.post_id
JOIN wp_postmeta meta2 ON posts.ID = meta2.post_id
JOIN wp_users users ON meta2.meta_value = users.user_email
JOIN wp_woocommerce_order_items order_items ON order_items.order_id = posts.ID
JOIN wp_woocommerce_order_itemmeta order_item_meta ON order_item_meta.order_item_id = order_items.order_item_id
JOIN wp_woocommerce_downloadable_product_permissions permissions ON permissions.order_id = posts.ID
WHERE posts.post_type = 'shop_order'
AND posts.post_status = 'wc-completed'
AND meta1.meta_key = '_order_key'
AND meta2.meta_key = '_billing_email'
AND posts.ID >= @first_order_id
AND posts.ID <= @last_order_id
AND posts.post_date >= @first_order_date
AND posts.post_date <= @last_order_date
AND order_item_meta.meta_key IN ('_product_id','_variation_id')
AND order_item_meta.meta_value = @product_id
/* sanity check */
SELECT * FROM temp_wp_woocommerce_downloadable_product_permissions;
/* insert all users from temporary table into wp_woocommerce_downloadable_product_permissions table */
INSERT INTO wp_woocommerce_downloadable_product_permissions (download_id, product_id, order_id, order_key, user_email, user_id, downloads_remaining, access_granted, access_expires) (SELECT * FROM temp_wp_woocommerce_downloadable_product_permissions);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment