WooCommerce Downloadable Files: Grant Access for New Files to Previous Customers

0 - readme.md

See blog post at https://andrewrminion.com/2017/12/woocommerce-downloadable-products-granting-permissions-to-previous-customers/

1 - CREATE temporary table.sql

/* 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_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
  `order_key` VARCHAR(200) NOT NULL,
  `user_email` VARCHAR(200) NOT NULL,
  `user_id` BIGINT(20) UNSIGNED DEFAULT 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`)
);

2 - INSERT into temporary table.sql

/* 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;

3 - INSERT into wp_woocommerce_downloadable_product_permissions.sql

/* 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);