Building a custom exporter for WooSubscriptions involves creating a tailored tool that allows you to export subscription-related data from WooCommerce Subscriptions into a specific format, such as CSV, Excel, or XML. This can be particularly useful if the default export options provided by WooCommerce Subscriptions don’t meet your needs, or if you require a specific format for integration with other systems, reporting, or analysis.
Here’s a step-by-step guide on how to build a custom exporter for WooSubscriptions:
1. Understand the Data Requirements
- Identify the specific data you need to export. This could include subscription details like subscription ID, customer information, product details, subscription status, start and end dates, renewal dates, payment methods, and any custom meta fields associated with subscriptions.
- Determine the format in which the data should be exported (e.g., CSV, Excel, XML) and any specific structure required by the end user or system that will receive the exported data.
2. Set Up the Development Environment
- Ensure you have a development environment where you can safely test your custom exporter. This could be a local WordPress installation with WooCommerce and WooCommerce Subscriptions installed.
- Use a child theme or a custom plugin to add your code, ensuring that your modifications won’t be overwritten by theme or plugin updates.
3. Create the Export Functionality
- Hook into WooCommerce Admin: Use WooCommerce hooks and filters to create a custom admin page or add an export button to the WooCommerce Subscriptions admin page. This button will trigger the export process.
- Retrieve Subscription Data: Use WooCommerce’s built-in functions to query and retrieve the necessary subscription data. The wcs_get_subscriptions function can be used to fetch subscription objects, which you can then iterate over to extract the required details.
- Format the Data: Once you have the data, format it according to the chosen export format. For example, if exporting to CSV, you might use PHP functions like fputcsv to write the data to a file. For Excel, you could use a library like PHPExcel or PhpSpreadsheet to generate the file.
4. Handle Large Data Exports
- If you’re exporting a large number of subscriptions, consider implementing a batch process to avoid timeouts and performance issues. You can use WP Cron jobs or AJAX to handle large datasets in smaller chunks.
- Implement proper error handling to ensure that if the export process fails, it can either retry or notify the admin of the issue.
5. Provide Download or Storage Options
- After the export file is generated, offer options to the admin user. They might download the file directly, receive it via email, or save it to a specific directory on the server.
- Consider adding options for automated exports on a schedule, allowing for regular backups or data transfers without manual intervention.
6. Test the Exporter
- Test your custom exporter thoroughly with various scenarios, including different subscription statuses, large data sets, and any custom fields or data types you might be including.
- Validate the exported data to ensure it meets the format and content requirements, and that no data is missing or incorrectly formatted.
7. Optimize and Refine
- Optimize your code to ensure it runs efficiently, especially when dealing with large numbers of subscriptions.
- Add any necessary user interface elements in the WooCommerce admin to allow users to configure the export options, such as selecting specific fields to include or filtering the subscriptions based on criteria like date ranges or statuses.
8. Documentation and User Instructions
- Provide clear documentation or inline help within the WooCommerce admin panel to guide users on how to use the custom exporter.
- Include details on how to access, configure, and troubleshoot the export tool.
Example Code Snippet
<?php
/*
Plugin Name: WooCommerce Subscription Export
Plugin URI: https://emoxie.com
Description: Custom plugin to export shipping data for Magazine Subscribers
Version: 1.2.0
Author: E-Moxie
Author URI: https://emoxie.com
*/
defined( 'ABSPATH' ) or exit;
if ( ! class_exists( 'WC_Export_Subscriptions' ) ) {
class WC_Export_Subscriptions {
public static $product_ids = '7164,7163,7157,1416,581,119,1666,1623,119,581,5004,1416';
public static $mailing_list_product_ids = '7157,7164,1666,1623,581,119';
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>Use the button below to export subscriptions that are Active and Pending Cancellation. We default the fields to the Shipping information, if Shipping information is not filled in we use the Billing information.</p>
<label>What type of report to export?
<select name="type">
<option value="printer">Printer Export</option>
<option value="full">Full Export</option>
<option value="mailing">Mailing List Export</option>
</select>
</label>
<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['type'] ) {
case "printer":
$sql = self::full_export_query();
$columns = self::printer_export_columns();
break;
case "normal":
$sql = self::export_query();
$columns = self::export_columns();
break;
case "full":
$sql = self::full_export_query();
$columns = self::full_export_columns();
break;
case "mailing":
$sql = self::mailing_export_query();
$columns = self::printer_export_columns();
break;
}
header( 'Content-Type: text/csv; charset=utf-8' );
header( 'Content-Disposition: attachment; filename=' . $_POST['type'] . '-bookpage_' . date( 'm-d-Y' ) . '-' . time() . '.csv' );
$output = fopen( "php://output", "w" );
fputcsv( $output, $columns );
$results = $wpdb->get_results( $sql, ARRAY_A );
if ( $_POST['type'] === 'printer' ) {
$new_results = [];
foreach ( $results as $subscription ) {
$first_name = $subscription['shipping_first_name'];
$last_name = $subscription['shipping_last_name'];
$email = $subscription['shipping_email'];
$company = $subscription['shipping_company'];
$address_1 = $subscription['shipping_address_1'];
$address_2 = $subscription['shipping_address_2'];
$city = $subscription['shipping_city'];
$state = $subscription['shipping_state'];
$postal_code = $subscription['shipping_postal_code'];
$country = $subscription['shipping_country'];
//If there is NOT a shipping_address_1 set then we use _billing info
if ( empty( $address_1 ) ) {
$first_name = $subscription['billing_first_name'];
$last_name = $subscription['billing_last_name'];
$email = $subscription['billing_email'];
$company = $subscription['billing_company'];
$address_1 = $subscription['billing_address_1'];
$address_2 = $subscription['billing_address_2'];
$city = $subscription['billing_city'];
$state = $subscription['billing_state'];
$postal_code = $subscription['billing_postal_code'];
$country = $subscription['billing_country'];
}
$new_results[] = [
'Ship to 1' => $first_name . ' ' . $last_name,
'Job Title' => '',
'Other 3' => $company,
'Ship To Street1' => $address_1,
'Ship To Street2' => $address_2,
'Ship To City' => $city,
'Ship To State' => $state,
'Ship To Zip' => $postal_code,
'Country' => $country,
];
}
$results = $new_results;
}
foreach ( $results as $row ) {
fputcsv( $output, $row );
}
fclose( $output );
die();
}
}
}
private static function mailing_export_query() {
global $wpdb;
return "SELECT
CONCAT(IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_first_name' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_first_name' AND `post_id` = p.ID LIMIT 1)),' ',IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_last_name' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_last_name' AND `post_id` = p.ID LIMIT 1))) as 'Ship to 1',
'' as 'Job Title',
IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_company' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_company' AND `post_id` = p.ID LIMIT 1)) as 'Other 3',
IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_address_1' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_address_1' AND `post_id` = p.ID LIMIT 1)) as 'Ship To Street1',
IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_address_2' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_address_2' AND `post_id` = p.ID LIMIT 1)) as 'Ship To Street2',
IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_city' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_city' AND `post_id` = p.ID LIMIT 1)) as 'Ship To City',
IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_state' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_state' AND `post_id` = p.ID LIMIT 1)) as 'Ship To State',
IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_postcode' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_postcode' AND `post_id` = p.ID LIMIT 1)) as 'Ship To Zip',
IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_country' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_country' AND `post_id` = p.ID LIMIT 1)) as 'Country'
FROM " . $wpdb->prefix . "posts p
WHERE
post_type='shop_subscription'
AND (post_status = 'wc-active' || post_status = 'wc-pending-cancellation')
AND (SELECT `meta_value` FROM " . $wpdb->prefix . "woocommerce_order_itemmeta WHERE `order_item_id` = (SELECT `order_item_id` FROM " . $wpdb->prefix . "woocommerce_order_items WHERE `order_id` = p.ID AND `order_item_type` = 'line_item' LIMIT 1) AND `meta_key` = '_product_id') IN (" . self::$mailing_list_product_ids . ")";
}
private static function printer_export_query() {
global $wpdb;
return "SELECT
CONCAT(IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_first_name' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_first_name' AND `post_id` = p.ID LIMIT 1)),' ',IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_last_name' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_last_name' AND `post_id` = p.ID LIMIT 1))) as 'Ship to 1',
'' as 'Job Title',
IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_company' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_company' AND `post_id` = p.ID LIMIT 1)) as 'Other 3',
IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_address_1' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_address_1' AND `post_id` = p.ID LIMIT 1)) as 'Ship To Street1',
IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_address_2' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_address_2' AND `post_id` = p.ID LIMIT 1)) as 'Ship To Street2',
IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_city' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_city' AND `post_id` = p.ID LIMIT 1)) as 'Ship To City',
IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_state' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_state' AND `post_id` = p.ID LIMIT 1)) as 'Ship To State',
IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_postcode' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_postcode' AND `post_id` = p.ID LIMIT 1)) as 'Ship To Zip',
IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_country' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_country' AND `post_id` = p.ID LIMIT 1)) as 'Country'
FROM " . $wpdb->prefix . "posts p
WHERE
post_type='shop_subscription'
AND (post_status = 'wc-active' || post_status = 'wc-pending-cancellation')
AND (SELECT `meta_value` FROM " . $wpdb->prefix . "woocommerce_order_itemmeta WHERE `order_item_id` = (SELECT `order_item_id` FROM " . $wpdb->prefix . "woocommerce_order_items WHERE `order_id` = p.ID AND `order_item_type` = 'line_item' LIMIT 1) AND `meta_key` = '_product_id') IN (" . self::$product_ids . ")";
}
private static function export_query() {
global $wpdb;
return "SELECT
p.ID as 'order_id',
IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_first_name' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_first_name' AND `post_id` = p.ID LIMIT 1)) as 'first_name',
IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_last_name' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_last_name' AND `post_id` = p.ID LIMIT 1)) as 'last_name',
IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_company' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_company' AND `post_id` = p.ID LIMIT 1)) as 'company',
IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_address_1' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_address_1' AND `post_id` = p.ID LIMIT 1)) as 'address_1',
IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_address_2' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_address_2' AND `post_id` = p.ID LIMIT 1)) as 'address_2',
IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_city' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_city' AND `post_id` = p.ID LIMIT 1)) as 'city',
IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_state' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_state' AND `post_id` = p.ID LIMIT 1)) as 'state',
IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_postcode' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_postcode' AND `post_id` = p.ID LIMIT 1)) as 'postal_code',
IFNULL((SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_country' AND `post_id` = p.ID LIMIT 1), (SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_country' AND `post_id` = p.ID LIMIT 1)) as 'country',
(SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_schedule_next_payment' AND `post_id` = p.ID LIMIT 1) as 'next_payment_date',
(SELECT `order_item_name` FROM " . $wpdb->prefix . "woocommerce_order_items WHERE `order_id` = p.ID AND `order_item_type` = 'line_item' LIMIT 1) as 'product',
(SELECT `meta_value` FROM " . $wpdb->prefix . "woocommerce_order_itemmeta WHERE `order_item_id` = (SELECT `order_item_id` FROM " . $wpdb->prefix . "woocommerce_order_items WHERE `order_id` = p.ID AND `order_item_type` = 'line_item' LIMIT 1) AND `meta_key` = '_product_id' LIMIT 1) as 'product_id',
(SELECT `sku` FROM " . $wpdb->prefix . "wc_product_meta_lookup WHERE `product_id` = (SELECT `meta_value` FROM " . $wpdb->prefix . "woocommerce_order_itemmeta WHERE `order_item_id` = (SELECT `order_item_id` FROM " . $wpdb->prefix . "woocommerce_order_items WHERE `order_id` = p.ID AND `order_item_type` = 'line_item' LIMIT 1) AND `meta_key` = '_product_id' LIMIT 1)) as 'sku'
FROM " . $wpdb->prefix . "posts p
WHERE
post_type='shop_subscription'
AND (post_status = 'wc-active' || post_status = 'wc-pending-cancellation')
AND (SELECT `meta_value` FROM " . $wpdb->prefix . "woocommerce_order_itemmeta WHERE `order_item_id` = (SELECT `order_item_id` FROM " . $wpdb->prefix . "woocommerce_order_items WHERE `order_id` = p.ID AND `order_item_type` = 'line_item' LIMIT 1) AND `meta_key` = '_product_id' LIMIT 1) IN (" . self::$product_ids . ")";
}
private static function full_export_query() {
global $wpdb;
return "SELECT
p.ID as 'order_id',
(SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_first_name' AND `post_id` = p.ID LIMIT 1) as 'shipping_first_name',
(SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_last_name' AND `post_id` = p.ID LIMIT 1) as 'shipping_last_name',
(SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_email' AND `post_id` = p.ID LIMIT 1) as 'shipping_email',
(SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_company' AND `post_id` = p.ID LIMIT 1) as 'shipping_company',
(SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_address_1' AND `post_id` = p.ID LIMIT 1) as 'shipping_address_1',
(SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_address_2' AND `post_id` = p.ID LIMIT 1) as 'shipping_address_2',
(SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_city' AND `post_id` = p.ID LIMIT 1) as 'shipping_city',
(SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_state' AND `post_id` = p.ID LIMIT 1) as 'shipping_state',
(SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_postcode' AND `post_id` = p.ID LIMIT 1) as 'shipping_postal_code',
(SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_country' AND `post_id` = p.ID LIMIT 1) as 'shipping_country',
(SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_shipping_phone' AND `post_id` = p.ID LIMIT 1) as 'shipping_phone',
(SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_first_name' AND `post_id` = p.ID LIMIT 1) as 'billing_first_name',
(SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_last_name' AND `post_id` = p.ID LIMIT 1) as 'billing_last_name',
(SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_email' AND `post_id` = p.ID LIMIT 1) as 'billing_email',
(SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_company' AND `post_id` = p.ID LIMIT 1) as 'billing_company',
(SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_address_1' AND `post_id` = p.ID LIMIT 1) as 'billing_address_1',
(SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_address_2' AND `post_id` = p.ID LIMIT 1) as 'billing_address_2',
(SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_city' AND `post_id` = p.ID LIMIT 1) as 'billing_city',
(SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_state' AND `post_id` = p.ID LIMIT 1) as 'billing_state',
(SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_postcode' AND `post_id` = p.ID LIMIT 1) as 'billing_postal_code',
(SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_country' AND `post_id` = p.ID LIMIT 1) as 'billing_country',
(SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_billing_phone' AND `post_id` = p.ID LIMIT 1) as 'billing_phone',
(SELECT `meta_value` FROM " . $wpdb->prefix . "postmeta WHERE `meta_key` = '_schedule_next_payment' AND `post_id` = p.ID LIMIT 1) as 'next_payment_date',
(SELECT `order_item_name` FROM " . $wpdb->prefix . "woocommerce_order_items WHERE `order_id` = p.ID AND `order_item_type` = 'line_item' LIMIT 1) as 'product',
(SELECT `meta_value` FROM " . $wpdb->prefix . "woocommerce_order_itemmeta WHERE `order_item_id` = (SELECT `order_item_id` FROM " . $wpdb->prefix . "woocommerce_order_items WHERE `order_id` = p.ID AND `order_item_type` = 'line_item' LIMIT 1) AND `meta_key` = '_product_id' LIMIT 1) as 'product_id',
(SELECT `sku` FROM " . $wpdb->prefix . "wc_product_meta_lookup WHERE `product_id` = (SELECT `meta_value` FROM " . $wpdb->prefix . "woocommerce_order_itemmeta WHERE `order_item_id` = (SELECT `order_item_id` FROM " . $wpdb->prefix . "woocommerce_order_items WHERE `order_id` = p.ID AND `order_item_type` = 'line_item' LIMIT 1) AND `meta_key` = '_product_id' LIMIT 1)) as 'sku'
FROM " . $wpdb->prefix . "posts p
WHERE
post_type='shop_subscription'
AND (post_status = 'wc-active' || post_status = 'wc-pending-cancellation')
AND (SELECT `meta_value` FROM " . $wpdb->prefix . "woocommerce_order_itemmeta WHERE `order_item_id` = (SELECT `order_item_id` FROM " . $wpdb->prefix . "woocommerce_order_items WHERE `order_id` = p.ID AND `order_item_type` = 'line_item' LIMIT 1) AND `meta_key` = '_product_id' LIMIT 1) IN (" . self::$product_ids . ")";
}
private static function printer_export_columns() {
return [
'Ship to 1',
'Job Title',
'Other 3',
'Ship To Street1',
'Ship To Street2',
'Ship To City',
'Ship To State',
'Ship To Zip',
'Country',
];
}
private static function export_columns() {
return [
'id',
'first_name',
'last_name',
'company',
'address_1',
'address_2',
'city',
'state',
'postal_code',
'country',
'next_payment_date',
'product_name',
'product_id',
'sku',
];
}
private static function full_export_columns() {
return [
'id',
'shipping_first_name',
'shipping_last_name',
'shipping_email',
'shipping_company',
'shipping_address_1',
'shipping_address_2',
'shipping_city',
'shipping_state',
'shipping_postal_code',
'shipping_country',
'shipping_phone',
'billing_first_name',
'billing_last_name',
'billing_email',
'billing_company',
'billing_address_1',
'billing_address_2',
'billing_city',
'billing_state',
'billing_postal_code',
'billing_country',
'billing_phone',
'next_payment_date',
'product_name',
'product_id',
'sku',
];
}
}
}
WC_Export_Subscriptions::init();
