wcs-upgrade-fix.php

<?php
/**
 * Plugin Name: WCS 1.4 Upgrade Fix
 * Plugin URI: 
 * Description: Custom plugin to fix a database that fell out of sync with Subscriptions 1.4's database structure.
 * Author: Brent Shepherd
 * Author URI:
 * Version: 1.0
 */

if ( ! defined( 'WCS_DEBUG' ) ) {
	define( 'WCS_DEBUG', true );
}

function wcs_fix_upgrade_to_1_4() {
	global $wpdb;

	if ( ! isset( $_GET['wcs_fix_update'] ) ) {
		return;
	}

	echo '<div>';

	$subscriptions_meta_key = $wpdb->get_blog_prefix() . WC_Subscriptions_Manager::$users_meta_key;

	$order_items_table     = $wpdb->get_blog_prefix() . 'woocommerce_order_items';
	$order_item_meta_table = $wpdb->get_blog_prefix() . 'woocommerce_order_itemmeta';

	// Get the IDs of all users who have a subscription
	$users_to_upgrade = get_users( array(
		'meta_key' => $subscriptions_meta_key,
		'fields'   => 'ID',
		'orderby'  => 'ID',
		)
	);

	eg_htmldump( '$users_to_upgrade = ', $users_to_upgrade );

	$users_upgraded = get_users( array(
		'meta_key' => '_' . $subscriptions_meta_key,
		'fields'   => 'ID',
		'orderby'  => 'ID',
		)
	);

	eg_htmldump( '$users_upgraded = ', $users_upgraded );

	foreach ( $users_to_upgrade as $user_to_upgrade ) {

		// Can't use WC_Subscriptions_Manager::get_users_subscriptions() because it relies on the new structure
		$users_old_subscriptions = get_user_option( $subscriptions_meta_key, $user_to_upgrade );

		eg_htmldump( '$users_old_subscriptions = ', $users_old_subscriptions );

		// Has the user already been upgraded? If so, don't upgrade them, we'll need to merge them later
		$users_upgraded_subscriptions = get_user_option( '_' . $subscriptions_meta_key, $user_to_upgrade );

		eg_htmldump( '$users_upgraded_subscriptions = ', $users_upgraded_subscriptions );

		foreach ( $users_old_subscriptions as $subscription_key => $subscription ) {

			// Subscription was upgraded already, let's just make sure we have complete records of the payment dates
			if ( is_array( $users_upgraded_subscriptions ) && array_key_exists( $subscription_key, $users_upgraded_subscriptions ) ) {

				echo( '<div style="border: 1px solid #000; min-height: 3m; overflow: auto; margin: 0.5em 0.5em 0.5em 15em; padding: 0.5em; dislpay: block;">--- array_key_exists( ' . $subscription_key . ', $users_upgraded_subscriptions )</div>' );

				$current_subscription = WC_Subscriptions_Manager::get_subscription( $subscription_key );

				if ( isset( $subscription['completed_payments'] ) ) {

					foreach ( $subscription['completed_payments'] as $completed_payment_date ) {
						if ( ! in_array( $completed_payment_date, $current_subscription['completed_payments'] ) ) {
							$current_subscription['completed_payments'][] = $completed_payment_date;
						}
					}

					WC_Subscriptions_Manager::update_subscription( $subscription_key, $current_subscription );

				}

			} else { // Subscription has not been upgrade

				echo( '<div style="border: 1px solid #000; min-height: 3m; overflow: auto; margin: 0.5em 0.5em 0.5em 15em; padding: 0.5em; dislpay: block;">!!! upgrading subscription ' . $subscription_key . '</div>' );

				// Subscription created incorrectly with v1.1.2
				if ( ! isset( $subscription['order_id'] ) ) {
					continue;
				}

				$order_item_id = WC_Subscriptions_Order::get_item_id_by_subscription_key( $subscription_key );

				if ( empty( $order_item_id ) ) { // Subscription created incorrectly with v1.1.2
					continue;
				}

				if ( ! isset( $subscription['trial_expiry_date'] ) ) {
					$subscription['trial_expiry_date'] = '';
				}

				// Set defaults
				$failed_payments    = isset( $subscription['failed_payments'] ) ? $subscription['failed_payments'] : 0;
				$completed_payments = isset( $subscription['completed_payments'] ) ? $subscription['completed_payments'] : array();
				$suspension_count   = isset( $subscription['suspension_count'] ) ? $subscription['suspension_count'] : 0;
				$trial_expiry_date  = isset( $subscription['trial_expiry_date'] ) ? $subscription['trial_expiry_date'] : '';

				$order_item = WC_Subscriptions_Order::get_item_by_subscription_key( $subscription_key );

				// Don't duplicate status if it has changed
				if ( ! isset( $order_item['subscription_status'] ) ) {
					$wpdb->query(
						$wpdb->prepare(
							"INSERT INTO $order_item_meta_table (order_item_id, meta_key, meta_value)
							VALUES (%d,%s,%s)",
							$order_item_id, '_subscription_status', $subscription['status']
						)
					);
				}

				// Don't duplicate suspension count if it has changed
				if ( ! isset( $order_item['subscription_suspension_count'] ) ) {
					$wpdb->query(
						$wpdb->prepare(
							"INSERT INTO $order_item_meta_table (order_item_id, meta_key, meta_value)
							VALUES (%d,%s,%s)",
							$order_item_id, '_subscription_suspension_count', $subscription['suspension_count']
						)
					);
				}

				$wpdb->query(
					$wpdb->prepare(
						"INSERT INTO $order_item_meta_table (order_item_id, meta_key, meta_value)
						VALUES
						(%d,%s,%s),
						(%d,%s,%s),
						(%d,%s,%s),
						(%d,%s,%s),
						(%d,%s,%s),
						(%d,%s,%s)",
						$order_item_id, '_subscription_start_date', $subscription['start_date'],
						$order_item_id, '_subscription_expiry_date', $subscription['expiry_date'],
						$order_item_id, '_subscription_end_date', $subscription['end_date'],
						$order_item_id, '_subscription_trial_expiry_date', $trial_expiry_date,
						$order_item_id, '_subscription_failed_payments', $failed_payments,
						$order_item_id, '_subscription_completed_payments', serialize( $completed_payments )
					)
				);

			}
		}

		update_option( 'wcs_1_4_last_upgraded_user_id', $user_to_upgrade );

	}

	// Add an underscore prefix to usermeta key to deprecate, but not delete, subscriptions in user meta
	$wpdb->update(
		$wpdb->usermeta,
		array( 'meta_key' => '_' . $subscriptions_meta_key ),
		array( 'meta_key' => $subscriptions_meta_key )
	);

	eg_htmldump( 'For user meta key update, $wpdb->num_rows = ', $wpdb->num_rows );

	// Now set the recurring shipping & payment method on all subscription orders that do not have it already set
	$wpdb->query(
		"INSERT INTO $wpdb->postmeta (`post_id`, `meta_key`, `meta_value`)
		SELECT `post_id`, CONCAT('_recurring',`meta_key`), `meta_value`
		FROM $wpdb->postmeta
		WHERE `meta_key` IN ('_shipping_method','_shipping_method_title','_payment_method','_payment_method_title')
		AND `post_id` IN (
			SELECT `post_id` FROM $wpdb->postmeta WHERE `meta_key` = '_order_recurring_total'
		)
		AND `post_id` NOT IN (
			SELECT `post_id` FROM $wpdb->postmeta WHERE `meta_key` = '_recurring_payment_method'
		)"
	);

	eg_htmldump( 'For recurring shipping & payment method, $wpdb->num_rows = ', $wpdb->num_rows );

	// Set the recurring shipping total on all subscription orders that do not have it already set
	$wpdb->query(
		"INSERT INTO $wpdb->postmeta (`post_id`, `meta_key`, `meta_value`)
		SELECT `post_id`, '_order_recurring_shipping_total', `meta_value`
		FROM $wpdb->postmeta
		WHERE `meta_key` = '_order_shipping'
		AND `post_id` IN (
			SELECT `post_id` FROM $wpdb->postmeta WHERE `meta_key` = '_order_recurring_total'
		)
		AND `post_id` NOT IN (
			SELECT `post_id` FROM $wpdb->postmeta WHERE `meta_key` = '_order_recurring_shipping_total'
		)"
	);

	eg_htmldump( 'For recurring shipping total, $wpdb->num_rows = ', $wpdb->num_rows );

	// Get the ID of all orders for a subscription with a free trial and no sign-up fee
	$order_ids = $wpdb->get_col(
		"SELECT order_items.order_id FROM $order_items_table AS order_items
			LEFT JOIN $order_item_meta_table AS itemmeta USING (order_item_id)
			LEFT JOIN $order_item_meta_table AS itemmeta2 USING (order_item_id)
		WHERE itemmeta.meta_key = '_subscription_trial_length'
		AND itemmeta.meta_value > 0
		AND itemmeta2.meta_key = '_subscription_sign_up_fee'
		AND itemmeta2.meta_value > 0"
	);

	$order_ids = implode( ',', $order_ids );

	// Now set the order totals to $0 (can't use $wpdb->update as it only allows joining WHERE clauses with AND)
	if ( ! empty ( $order_ids ) ) {
		$wpdb->query(
				"UPDATE $wpdb->postmeta
				 SET `meta_value` = 0
				 WHERE `meta_key` IN ( '_order_total', '_order_tax', '_order_shipping_tax', '_order_shipping', '_order_discount', '_cart_discount' )
				 AND `post_id` IN ( $order_ids )"
		);

		// Now set the line totals to $0
		$wpdb->query(
			"UPDATE $order_item_meta_table
			 SET `meta_value` = 0
			 WHERE `meta_key` IN ( '_line_subtotal', '_line_subtotal_tax', '_line_total', '_line_tax', 'tax_amount', 'shipping_tax_amount' )
			 AND `order_item_id` IN (
				SELECT `order_item_id` FROM $order_items_table
				WHERE `order_item_type` IN ('tax','line_item')
				AND `order_id` IN ( $order_ids )
			)"
		);
	}

	eg_htmldump( '$order_ids = ', $order_ids );

	update_option( 'wcs_1_4_upgraded_order_ids', explode( ',', $order_ids ) );
	echo '</div>';
}
add_action( 'admin_footer', 'wcs_fix_upgrade_to_1_4' );

function eg_htmldump( $variable_name, $variable, $height = "9em" ) {
	echo "<pre style=\"border: 1px solid #000; min-height: {$height}; overflow: auto; margin: 0.5em 0.5em 0.5em 15em; padding: 0.5em; dislpay: block;\">";
	echo "$variable_name";
	var_dump( $variable );
	echo "</pre>\n";
}