save_the_day.php

<?php
/**
 * @file
 * Repairs the date mode dates.
 *
 * Requires the fundraiser_recurring table to be populated
 * and the uc_orders table to have at least order_id and created columns.
 *
 * Run with something like "drush php-script save_the_day.php > results.txt"
 */


// Count duplicates and gaps before fixing
fundraiser_sustainer_find_duplicates_and_gaps();
fundraiser_sustainer_find_gaps_from_october();

// one off hardcoded fix
db_query("UPDATE fundraiser_recurring SET next_charge = 1381813261 WHERE order_id IN (230094, 248077)");
drush_print('Moved charge date from 12/15 to 10/15 for orders 230094 and 248077');

// fix it!
fundraiser_sustainer_fix_date_mode();

// Count again
fundraiser_sustainer_find_duplicates_and_gaps();
fundraiser_sustainer_find_gaps_from_october();

drush_print('finished');

/**
 * Returns info on all orders in a sustainer series.
 *
 * The 0th element is the master order.
 *
 * @param integer $master_order_id
 * @return array
 */
function fundraiser_get_sustainer_series($master_order_id) {
  $return = array();

  $return[0] = db_fetch_array(
    db_query("SELECT order_id, created AS next_charge, modified
    FROM {uc_orders} WHERE order_id = %d", $master_order_id)
  );

  // Assume the master order succeeded.
  $return[0]['gateway_resp'] = 'success';
  $return[0]['attempts'] = 0;

  $results = db_query("SELECT order_id, next_charge, gateway_resp, attempts
    FROM {fundraiser_recurring} WHERE master_order_id = %d ORDER BY order_id ASC",
    $master_order_id
  );

  while ($row = db_fetch_array($results)) {
    $return[] = $row;
  }

  return $return;
}

/**
 * Returns an array of master order IDs for serieses that have future orders.
 *
 * @param integer $timestamp
 *
 * @return array
 */
function fundraiser_get_master_order_ids_after($timestamp) {
  $return = array();

  $results = db_query("SELECT DISTINCT master_order_id FROM {fundraiser_recurring}
    WHERE next_charge > %d
    ORDER BY master_order_id ASC, next_charge ASC",
    $timestamp
  );

  while ($row = db_fetch_array($results)) {
    $return[] = $row['master_order_id'];
  }

  return $return;
}

/**
 * Gets the array index and date of a baseline to calculate future orders from.
 *
 * Ignores the master order, past orders, canceled or failed orders.
 *
 * @param array $orders
 *
 * @return array|bool
 */
function fundraiser_sustainer_get_baseline_info($orders) {
  $now = strtotime('now');
  foreach ($orders as $i => $order) {
    // element 0 is the master order
    // element 1 is the first sustainer
    // skip the master order.
    // skip past orders
    // skip future failed and canceled orders.
    if ($i == 0 ||
      $order['next_charge'] < $now ||
      in_array($order['gateway_resp'], array('failed', 'canceled'))
    ) {
      continue;
    }

    // after passing that criteria, the first order in the sequence is the baseline.
    return array(
      'index' => $i,
      'date' => $order['next_charge'],
    );

  }
  return FALSE;
}

/**
 * Iterates through serieses and recalculates the charge dates on future orders.
 *
 * Should be run from drush (ex. drush eval "fundraiser_sustainer_fix_date_mode();"
 * Should be okay to run this multiple times.
 */
function fundraiser_sustainer_fix_date_mode() {
  $mode = variable_get('fundraiser_sustainer_set_date_mode', FALSE);

  if (!$mode) {
    drush_print('Date mode is not enabled.');
    return;
  }

  $count = 0;
  $past_records = 0;
  $future_processed_orders = 0;
  $baseline_rows = 0;
  $now = strtotime('now');

  $master_ids = fundraiser_get_master_order_ids_after($now);

  drush_print('Iterating through serieses to fix the dates.');

  // open a file to write all the SFIDs
  $file = fopen('order_sfids.csv', 'w');

  foreach ($master_ids as $master_id) {
    $orders = fundraiser_get_sustainer_series($master_id);
    $baseline_info = fundraiser_sustainer_get_baseline_info($orders);

    // if there's no legit baseline date, go to the next series.
    if ($baseline_info === FALSE) {
      continue;
    }

    // edit the sustainer after the baseline sustainer.
    $start_at = $baseline_info['index'] + 1;
    $baseline_date = $baseline_info['date'];
    $baseline_rows++;

    // offset number of months from the baseline date
    $increment = 0;
    for ($i = $start_at; $i < count($orders); $i++) {
      $order = $orders[$i];
      $increment++;

      // skip past orders
      // shouldn't happen because $start_at should already be past them.
      if ($order['next_charge'] < $now) {
        $past_records++;
        continue;
      }

      // skip future failed and canceled orders.
      if (in_array($order['gateway_resp'], array('failed', 'canceled'))) {
        $future_processed_orders++;
        continue;
      }

      $new_date = strtotime('+' . $increment . ' months', $baseline_date);

      // no change, skip it
      // don't care about the time, only the date
      if (date('Y-m-d', $order['next_charge']) == date('Y-m-d', $new_date)) {
        $no_change++;

        // we still want to to update the opportunity record because it never happened to begin with
        fundraiser_sustainer_write_csv_line($file, $order['order_id'], $new_date);

        continue;
      }

      db_query('UPDATE {fundraiser_recurring} ' .
        'SET next_charge = %d ' .
        'WHERE order_id = %d', $new_date, $order['order_id']);

      drush_print('Updated order '. $order['order_id'] .
        ' (master order ' . $master_id . ')' .
        ' from ' .
        date('Y-m-d H:i:s', $order['next_charge']) .
        ' to ' . date('Y-m-d H:i:s', $new_date));

      $replacements = array(
        '!from' => date('n/j/Y', $order['next_charge']),
        '!to' => date('n/j/Y', $new_date)
      );

      uc_order_comment_save($order['order_id'], 0, t('Payment charge date changed from !from to !to.', $replacements), 'admin');

      // write the new date to a file
      fundraiser_sustainer_write_csv_line($file, $order['order_id'], $new_date);

      $count++;
    }
  }

  fclose($file);

  drush_print($count . ' rows updated.');
  drush_print($past_records . ' past rows that were skipped.');
  drush_print($future_processed_orders . ' failed or canceled rows');
  drush_print($no_change . ' rows left unchanged');
  drush_print($baseline_rows . ' baseline date rows');

}

/**
 * Finds duplicates and gaps in future sustainers.
 *
 * Should be run from drush eval.
 */
function fundraiser_sustainer_find_duplicates_and_gaps() {
  $mode = variable_get('fundraiser_sustainer_set_date_mode', FALSE);

  if (!$mode) {
    drush_print('Date mode is not enabled.');
    return;
  }

  $count = 0;
  $gaps = 0;
  $now = strtotime('now');

  drush_print('Iterating through serieses to find duplicates and non-consecutive sustainers.');

  $master_ids = fundraiser_get_master_order_ids_after($now);

  foreach ($master_ids as $master_id) {
    $orders = fundraiser_get_sustainer_series($master_id);
    $charges = array();

    foreach ($orders as $order) {
      // only care about checking duplicates against future charges
      // and ones that haven't been canceled
      if ($order['next_charge'] > $now && $order['gateway_resp'] == NULL) {
        $charges[$order['next_charge']][] = $order['order_id'];
      }

    }

    $previous_charge_date = FALSE;
    foreach ($charges as $date => $dupes) {
      // See if the last charge date was more than 31 days ago.
      if ($previous_charge_date) {
        $difference = $date - $previous_charge_date;
        $difference_in_days = $difference / (3600 * 24);
        if ($difference_in_days > 31.5) {
          drush_print(
            'gap found (master ID ' . $master_id . ') between order ' .
            $charges[$previous_charge_date][0] . ' and order ' .
            $charges[$date][0] . ' - ' .
            $difference_in_days . ' days difference.');
          $gaps++;
        }
      }

      $previous_charge_date = $date;

      if (count($dupes) > 1) {
        drush_print('Master ID ' . $master_id . ' for ' . date('Y-m-d H:i:s', $date) . ' has multiple orders.');
        foreach ($dupes as $dupe) {

          // find the order
          foreach ($orders as $order) {
            if ($order['order_id'] == $dupe) {
              drush_print(
                $order['order_id'] . ' - ' .
                $order['next_charge'] . ' - ' .
                $order['gateway_resp'] . ' - ' .
                $order['attempts']
              );
              break;
            }
          } // end find the order

        }
        $count++;
      } // end duplicate check
    } // end foreach charges
  } // end foreach master id

  drush_print($count . ' sets of duplicates found.');
  drush_print($gaps . ' gaps found between orders.');
}

/**
 * Writes a new csv file containing the charge date and Salesforce Id
 * to be used to update Salesforce after the schedule has been fixed
 * in Drupal.
 */
function fundraiser_sustainer_write_csv_line($file_handle, $order_id, $new_date) {
  // get the salesforce id of the opportunity
  $sfid = fundraiser_sustainer_get_sfid($order_id);

  $line = array(
    $order_id,
    $sfid,
    date('c', $new_date),
  );
  fputcsv($file_handle, $line, ',');
}

/**
 * Returns the Salesforce ID of a given order.
 */
function fundraiser_sustainer_get_sfid($order_id) {
  $data = salesforce_management_api_id_load('donation', $order_id);
  if (!empty($data) && array_key_exists('sfid', $data)) {
    return $data['sfid'];
  }

  // donation has not been exported :-(
  return 'NOT_EXPORTED';
}

/**
 * Finds gaps between 10/15 and future sustainers based on counts.
 *
 * Should be run from drush php-script.
 */
function fundraiser_sustainer_find_gaps_from_october() {
  $mode = variable_get('fundraiser_sustainer_set_date_mode', FALSE);

  if (!$mode) {
    drush_print('Date mode is not enabled.');
    return;
  }

  $count = 0;
  $gaps = 0;
  $now = strtotime('now');

  drush_print('Iterating through serieses to compare sustainer count and number of months between 2013-10-15 and the last sustainer.');

  $master_ids = fundraiser_get_master_order_ids_after($now);

  foreach ($master_ids as $master_id) {
    $orders = fundraiser_get_sustainer_series($master_id);
    $charges = array();
    $failed = 0;
    $canceled = 0;

    $sustainer_count = 0;
    foreach ($orders as $order) {
      // count orders in October and later
      if (date('Y-m-d', $order['next_charge']) >= '2013-10-15') {
        $sustainer_count++;

        if ($order['gateway_resp'] == 'canceled') {
          $canceled++;
        }
        elseif ($order['gateway_resp'] == 'failed') {
          $failed++;
        }
      }

    }

    if (($canceled + $failed) == $sustainer_count) {
      // skip this series if all future orders are failed or canceled.
      continue;
    }

    $last_order = end($orders);
    // ignore time differences
    $october = new DateTime('2013-10-15 01:01:01');
    $end_date = new DateTime(date('Y-m-d 01:01:01', $last_order['next_charge']));

    $date_diff = $end_date->diff($october);
    $months_difference = ($date_diff->format('%y') * 12) + $date_diff->format('%m');

    // add a month when comparing number of sustainers to include the October charge.
    $months_difference++;

    if ($months_difference != $sustainer_count) {
      drush_print("$months_difference months between " .
        $october->format('Y-m-d H:i:s') . " and " .
        $end_date->format('Y-m-d H:i:s') .
        ". $sustainer_count sustainers in that range." .
        ' Master order ID ' . $master_id
      );

      $count++;
    }

  } // end foreach master id

  drush_print($count . ' gaps found through comparing months and sustainer counts.');
}