Export WooCommerce Subscriptions Shipping Information

woocommerce-subscription-export.php

<?php
	/*
	  Plugin Name: WooCommerce Subscription Export
	  Plugin URI: https://emoxie.com
	  Description: Custom plugin to export shipping data for Magazine Subscribers
	  Version: 1.0.0
	  Author: Matt Pramschufer
	  Author URI:
	*/
	defined( 'ABSPATH' ) or exit;

	if ( ! class_exists( 'WC_Export_Subscriptions' ) ) {

		class WC_Export_Subscriptions {

			public static $error_message = '';

			public static function init() {
				if ( is_admin() && ( ! defined( 'DOING_AJAX' ) || ! DOING_AJAX ) ) {
					add_action( 'admin_menu', __CLASS__ . '::add_sub_menu', 10 );
					add_action( 'admin_init', __CLASS__ . '::export_handler' );

				}
			}

			public static function add_sub_menu() {
				add_submenu_page( 'woocommerce', __( 'Export Subscriptions', 'wc-export-subscriptions' ), __( 'Export Subscriptions', 'wc-export-subscriptions' ), 'manage_woocommerce', 'wc_export_subscriptions', __CLASS__ . '::export_page' );
			}

			public static function export_page() {
				?>

				<div class="wrap woocommerce">
				<h2>Export Subscriptions</h2>
				<?php if ( ! empty( self::$error_message ) ) : ?>
					<div id="message" class="error">
						<p><?php echo esc_html( self::$error_message ); ?></p>
					</div>
				<?php endif; ?>
				<form class="wc-export-subscriptions" method="POST" action="<?php echo esc_attr( add_query_arg( 'step', 'download' ) ); ?>">
					<p>Select which magazine you want to download active subscriptions for.  <strong>Please note this can take up to 5 minutes to download</strong>.</p>
					<select name="subscription_type">
						<option value="gm">Green Magazine</option>
						<option value="lg">Little Green Magazine</option>
						<option value="cft">Classic Farm & Tractor</option>
					</select>
					<p class="note">
						<strong>Green Magazine queries the table</strong> <code>subscriptions</code> for products that are titled <code>Green Magazine Subscription%</code> where the <code>%</code> is a wildcard.  So this includes all legacy products.</strong>
						<br>
						<strong>Little Green Magazine</strong> queries the table <code>subscriptions</code> for products that are titled <code>“Little Green, The Green Magazine for Kids” Subscription%</code> where the <code>%</code> is a wildcard.  So this includes all legacy products.</strong>
						<br>
						<strong>Classic Farm & Tractor</strong> queries the table <code>subscriptions</code> for products that are titled <code>Classic Farm and Tractor Subscription%</code> where the <code>%</code> is a wildcard.  So this includes all legacy products.</strong>
					</p>
				<p class="submit">
				<input type="submit" class="button" value="Export Subscriptions" />
				</p>
				<?php wp_nonce_field( 'wc-export-subscriptions', 'ec-export-subscriptions_wpnonce' ); ?>
				</form>

				<?php
			}


			/**
			 * Check params sent through as POST and start the export
			 *
			 * @since 1.0
			 */
			public static function export_handler() {
				global $wpdb;

				if ( isset( $_GET['page'] ) && 'wc_export_subscriptions' == $_GET['page'] ) {
					if ( isset( $_GET['step'] ) && 'download' == $_GET['step'] ) {

						check_admin_referer( 'wc-export-subscriptions', 'ec-export-subscriptions_wpnonce' );

						switch($_POST['subscription_type']){
							default:
							case "gm":
								$like = 'Green Magazine Subscription%';
								break;
							case "lg":
								$like = 'Little Green - The Green Magazine for Kids Subscription%';
								break;
							case "cft":
								$like = 'Classic Farm and Tractor Subscription%';
								break;
						}

						$sql = self::gm_query($like);



						header('Content-Type: text/csv; charset=utf-8');
						header('Content-Disposition: attachment; filename=' . $_POST['subscription_type'] . '_' . time() . '.csv');
						$output = fopen("php://output", "w");
						$columns = [
							'first_name',
							'last_name',
							'company',
							'address_1',
							'address_2',
							'city',
							'state',
							'postal_code',
							'country',
							'next_payment_date'
						];
						fputcsv($output, $columns);
						$results = $wpdb->get_results( $sql, ARRAY_A );
						foreach($results as $row){
							fputcsv($output, $row);
						}
						fclose($output);
                        die();

					}
				}
			}


			private static function gm_query($like){
			    //If you add in JOINS to pull the product_id, it is about 200x slower
                return "SELECT
                              pm1.meta_value              AS 'first_name',
                              pm2.meta_value              AS 'last_name',
                              pm3.meta_value              AS 'company',
                              pm4.meta_value              AS 'address_1',
                              pm5.meta_value              AS 'address_2',
                              pm6.meta_value              AS 'city',
                              pm7.meta_value              AS 'state',
                              pm8.meta_value              AS 'postal_code',
                              pm9.meta_value              AS 'country',
                              order_items.order_item_name AS 'product',
                              pm10.meta_value             AS 'next_payment_date'
                              #,order_item_meta.meta_value  AS 'product_id'
                            
                            FROM h7Wp01vs5_posts p
                              INNER JOIN h7Wp01vs5_postmeta pm1 ON pm1.post_id = p.ID
                              INNER JOIN h7Wp01vs5_postmeta pm2 ON pm2.post_id = p.ID
                              INNER JOIN h7Wp01vs5_postmeta pm3 ON pm3.post_id = p.ID
                              INNER JOIN h7Wp01vs5_postmeta pm4 ON pm4.post_id = p.ID
                              INNER JOIN h7Wp01vs5_postmeta pm5 ON pm5.post_id = p.ID
                              INNER JOIN h7Wp01vs5_postmeta pm6 ON pm6.post_id = p.ID
                              INNER JOIN h7Wp01vs5_postmeta pm7 ON pm7.post_id = p.ID
                              INNER JOIN h7Wp01vs5_postmeta pm8 ON pm8.post_id = p.ID
                              INNER JOIN h7Wp01vs5_postmeta pm9 ON pm9.post_id = p.ID
                              INNER JOIN h7Wp01vs5_postmeta pm10 ON pm10.post_id = p.ID
                              INNER JOIN h7Wp01vs5_woocommerce_order_items order_items ON order_items.order_id = p.ID
                              #INNER JOIN h7Wp01vs5_woocommerce_order_itemmeta order_item_meta ON order_items.order_id = order_item_meta.order_item_id
                            WHERE
                              post_type = 'shop_subscription'
                              AND post_status = 'wc-active'
                              AND pm1.meta_key = '_shipping_first_name'
                              AND pm2.meta_key = '_shipping_last_name'
                              AND pm3.meta_key = '_shipping_company'
                              AND pm4.meta_key = '_shipping_address_1'
                              AND pm5.meta_key = '_shipping_address_2'
                              AND pm6.meta_key = '_shipping_city'
                              AND pm7.meta_key = '_shipping_state'
                              AND pm8.meta_key = '_shipping_postcode'
                              AND pm9.meta_key = '_shipping_country'
                              AND pm10.meta_key = '_schedule_next_payment'
                              AND order_items.order_item_type = 'line_item'
                              #AND order_item_meta.meta_key = '_product_id'
                              AND order_items.order_item_name LIKE '{$like}'
                            GROUP BY p.ID";
			}
			
		}
	}

	WC_Export_Subscriptions::init();