Incorrect data is being inserted into the ec_recurring_expiration table

hey phil. i've emailed you this, but thought i would share on here..

Here's a mod I came up with a while back for a music shop I was working on.

It overrides the ec_recurring.module renewal settings for file.products and uses a combination of the transaction payment date (when the payment was completed) and an expiry duration for files, which can be set by site admins on the ADMIN -> ECOMMERCE CONFIGURATION -> FILE settings page.

The music shop is still a work in progress, but, I have tested these mods on a test shop.

Notes:
--------

1. These changes override the ec_recurring.module renewal settings for file products.

2. The expiry date is calculated using the transaction payment_date field..i.e. when the order payment was completed and an expiry duration determined by site admins on the ecommerce file module settings page

3. The expiry duration options include 30 minutes, 1 hour, 6 hours, 12 hours, 24 hours, 2 days or 3 days.
(30 minutes is just useful for testing purposes)

3. The expiry duration is a site-wide setting...at the moment it cannot be specified for individual files.

4. No database changes are required.

5. The myfiles page now combines both expired and active file downloads on the same page.

6. Tested using Drupal eCommerce version 5.x-3-dev.

7. Unlikely to be updated for eCommerce version 5.x-4 because that is a complete rewrite of the Drupal
eCommerce suite of modules.

8. Attached is a patched version of the file.module (v 1.37.2.7.2.1.2.16 2007/08/03 02:48:35 gordon), just rename from file.module.txt to file.module and upload to your ecommerce/file folder on your server and then
go to ADMIN -> ECOMMERCE CONFIGURATION -> FILE

Patch 1
replace the entire function called file_ec_settings() (line numbers 244-271 in the file.module) with the following mod.

/**
 * Implementation of hook_settings().
 */
function file_ec_settings() {
  drupal_set_title(t('Ecommerce file download settings'));

  if (!isset($_POST['op'])) {
    $file_path = variable_get('ec_file_directory_path', 'files');
    file_check_directory($file_path, 0, 'ec_file_directory_path');
  }
  $form['ec_file_directory_path'] = array(
    '#type' => 'textfield',
    '#title' => t('File path for products'),
    '#default_value' => variable_get('ec_file_directory_path', 'files'),
    '#size' => 70,
    '#maxlength' => 255,
    '#description' => t('A file system path where the product files are stored. This directory has to exist and be writable by Drupal. This directory should not be accessible over the web. Changing this location after the site has been in use will cause problems so only change this setting on an existing site if you know what you are doing.'),
    '#validate' => array('file_directory_validate' => array()),
  );
  // override the ec_recurring module renewals settings form if a file expiry date has been set.
  $cutofftime = variable_get('file_expiry_setting','');
  if (!$cutofftime) {
    $slist = array_merge(array(0 => '--'), ec_recurring_get_list(FALSE));
    // TODO Implement the code to use this feature. 
    $form['ec_file_default_schedule'] = array(
      '#type' => 'select',
      '#title' => t('Default renewal schedule'),
      '#default_value' => variable_get('ec_file_default_schedule', 0),
      '#description' => t('Select a default renewal schedule to be used for files. Note: This is not used yet.'),
      '#options' => $slist,
      '#weight' => -1
    );
  }

  $form['f_settings']['file_expiry_setting'] = array(
	  '#type' => 'select', 
	  '#title' => t('Make purchased downloads available for'), 
	  '#default_value' => variable_get('file_expiry_setting',''), 
	  '#options' => array(
	    '1800' =>t('30 minutes') ,
                 '3600' =>t('1 hour') ,
	    '21600' =>t('6 hours') , 
	    '43200' =>t('12 hours'), 
	    '86400' => t('24 hours'),
	    '172800' => t('2 days'),
	    '187200' => t('3 days'),), 
	  '#description' => t('<p>The length of time downloads will be available to download for, from the time payment and the order workflow has been set to completed. This option overrides the renewal schedule setting.<p> '),
  	);

  return system_settings_form($form);
}

Patch 2
Replace the entire function called file_my_overview with the following mod. Note that this should really use the views.module to display the files, but, it works.

/**
 * Display a list of files for the user to download.
 *
 * Note:
 * The expiration date is set in the ecommerce file admin settings page  
 * and is linked to the payment date for the transaction.
 *
 * Expired and active downloads are listed on the same page.
 * 
 */
function file_my_overview($uid = FALSE, $expired = FALSE) {
  
  // construct the page title
  $name = db_result(db_query('SELECT name FROM {users} WHERE uid = %d', $uid));
  $title = t('%username\'s files', array('%username' => $name));
  drupal_set_title($title);
  
  // define the table columns and default sort
  $header = array(
    array('data' => t('title'), 'field' => 't.txnid', 'sort' => 'desc'),
    array('data' => t('size'), 'field' => 'epf.size'),
    t('expires'), 
    t('download link')
  );

  // Load all file transactions that are set as workflow completed
  $result = db_query('SELECT etp.title, epf.size, epf.fpath, t.workflow, t.payment_date
     FROM {ec_transaction} t
       JOIN {ec_transaction_product} etp ON etp.txnid = t.txnid
       JOIN {ec_product_file} epf ON epf.nid = etp.nid
     WHERE t.uid = %d AND t.workflow = %d '. tablesort_sql($header), $uid, EC_WORKFLOW_COMPLETED);
    
  // load the cutoff time which is set under the ecommerce file admin settings page.
  $cutofftime = variable_get('file_expiry_setting','');

  // remove download link and replace with the text expired if the file download has expired 
  while ($data = db_fetch_object($result)) {
    if (($data->workflow == EC_WORKFLOW_COMPLETED) && ((time() - $data->payment_date) <= $cutofftime)) { // if the workflow is set to completed and the expiry time is less than the current time 
      $dload_link = t('<a href="!file-download-uri">download</a>', array('!file-download-uri' => ec_file_create_url($data->fpath)));
    }
    if (($data->workflow == EC_WORKFLOW_COMPLETED) && ((time() - $data->payment_date) > $cutofftime)) {// if the workflow is set to completed and the expiry time is greater than the current time
     $dload_link = t('Expired');
    }
    
    // define some variables   
    //$paid_date = date('M-D-Y (h:m)',$data->payment_date);
    $expiry_date =  ($data->payment_date + $cutofftime);
    $due_date = format_interval($expiry_date - time());

    // change the expires string in the table to be time ago if the file has expired
    if (time() - $data->payment_date >= $cutofftime) {
        $due_date =  t('%interval ago', array('%interval' => format_interval(time() - ($data->payment_date + $cutofftime))));
    }
  
    // construct the table row for this file 
    $rows[] = array(theme('placeholder', $data->title), format_size($data->size),  $due_date, $dload_link);  
  
  }
  
  // build the table
  $output .= ($rows) ? theme('table', $header, $rows) : t('You have no files to download.');

  return $output;
}

Patch 3
Replace the entire function called ec_file_may_download with the following mod.

function ec_file_may_download($file, $uid) {

  $data = db_fetch_object(db_query("SELECT st.txnid, stp.vid, st.payment_date FROM {ec_transaction} AS st
      JOIN {ec_transaction_product} AS stp ON stp.txnid = st.txnid
      JOIN {ec_product_file} AS pf ON pf.nid = stp.nid
    WHERE st.uid = %d
      AND st.workflow = %d
      AND pf.fpath = '%s'", $uid, EC_WORKFLOW_COMPLETED, $file));

// ec_recurring override
  if (!empty($data)) {
      // return false if the cutofftime has expired. 
      $cutofftime = variable_get('file_expiry_setting','');
      if (time() - $data->payment_date > $cutofftime) {
        return false;
      }
      else {
        return true;
      }
     }
  else {
    return false;
  }
}

hope that helps others

the patch includes an override in the may download function....i.e it runs a quick check against the payment date and expiration time to see if the file has expired or not.

dub

There are several versions of ec mentioned here. I am not sure which one it applies to. Please update with the version that this issue applies to.

Thanks
DaveA

Hi Phillip,

Unfortunately, the ec_recurring_expiration entries above don't paint a complete picture of what is wrong. The top two show a status of 2 (meaning they've been renewed). There are meant to be other entries in that table with a status of 0 (zero: meaning they are active/unexpired).

Please paste all entries, in ec_recurring_expiration relating to those product vids (1, 2 and 3).

Thanks.