uc_csv.module in Ubercart CSV 6.2

File

uc_csv.module

<?php

/**
 * implementation of hook_menu();
 */
function uc_csv_menu() {
  $items = array();
  $items['admin/store/export'] = array(
    'title' => 'CSV Export',
    'description' => t('Export all orders as a CSV file'),
    'page callback' => 'uc_csv_report_grid',
    'access callback' => 'uc_csv_both_perms',
    'access arguments' => array(
      '',
    ),
    'type' => MENU_NORMAL_ITEM,
  );
  $items['admin/store/export/index'] = array(
    'title' => 'Export Report Settings',
    'description' => t('Set up the reports you want to export.'),
    'page callback' => 'uc_csv_report_grid',
    'access callback' => 'user_access',
    'access arguments' => array(
      'administer ubercart csv export settings',
    ),
    'weight' => -25,
    'type' => MENU_DEFAULT_LOCAL_TASK,
  );
  $items['admin/store/export/create'] = array(
    'title' => 'Create New Report',
    'description' => t('Add a new export report.'),
    'page callback' => 'drupal_get_form',
    'page arguments' => array(
      'uc_csv_add_new_report',
    ),
    'access callback' => 'user_access',
    'access arguments' => array(
      'administer ubercart csv export settings',
    ),
    'weight' => -20,
    'type' => MENU_LOCAL_TASK,
  );
  $items['admin/store/export/execute'] = array(
    'title' => 'Export Report',
    'description' => t('Select report to export.'),
    'page callback' => 'drupal_get_form',
    'page arguments' => array(
      'uc_csv_select_report_to_export',
    ),
    'access callback' => 'uc_csv_both_perms',
    'access arguments' => array(
      '',
    ),
    'weight' => -15,
    'type' => MENU_LOCAL_TASK,
  );
  $items['admin/store/export/reports/update/%uc_csv_report_id'] = array(
    'title' => 'Update Report',
    'description' => t('Update an existing report'),
    'page callback' => 'drupal_get_form',
    'page arguments' => array(
      'uc_csv_add_new_report',
      5,
    ),
    'access callback' => 'user_access',
    'access arguments' => array(
      'administer ubercart csv export settings',
    ),
    'type' => MENU_CALLBACK,
  );
  $items['admin/store/export/reports/delete/%uc_csv_report_id'] = array(
    'title' => 'Delete Report',
    'description' => t('Delete an existing export report.'),
    'page callback' => 'drupal_get_form',
    'page arguments' => array(
      'uc_csv_delete_report',
      5,
    ),
    'access callback' => 'user_access',
    'access arguments' => array(
      'administer ubercart csv export settings',
    ),
    'type' => MENU_CALLBACK,
  );
  return $items;
}

/**
 * uc_csv_both_perms() 
 * allow access if one of either the permissions are checked for a user
 */
function uc_csv_both_perms() {
  return user_access('administer ubercart csv export settings') || user_access('export ubercart reports as csv files');
}

/**
 * implementation of hook_perm();
 */
function uc_csv_perm() {
  return array(
    'administer ubercart csv export settings',
    'export ubercart reports as csv files',
  );
}

/**
 * implementation of hook_load()
 */
function uc_csv_report_id_load($id) {
  if (is_numeric($id)) {
    $result = db_query("SELECT * FROM {uc_csv_reports} WHERE rid='%d';", $id);
    if ($result == TRUE) {
      $report = db_fetch_object($result);
    }
    else {
      drupal_set_message('Unable to load the requested report. Please report this error with a snapshot of your uc_csv_reports table', 'error');
      return FALSE;
    }
    return $report;
  }
}
function uc_csv_report_grid() {
  $i = 0;
  $rows = array();
  $header = array(
    array(
      'data' => t('Export Name'),
    ),
    array(
      'data' => t('Last Exported'),
      'style' => 'text-align: center;',
    ),
    array(
      'data' => t('Last Order ID'),
      'style' => 'text-align: center;',
    ),
    array(
      'data' => t('Actions'),
      'style' => 'text-align: center;',
    ),
  );
  $result = db_query('SELECT * FROM {uc_csv_reports} ORDER BY report_name ASC');
  while ($data = db_fetch_object($result)) {

    /* if this is an untrackable report, then it is not necessary for list the last exported */

    /* number since it will always be zero */
    if ($data->track < 1) {
      $data->last_order_id = 'N/A';
    }
    $row = array();
    $row[] = $data->report_name;
    $row[] = array(
      'data' => $data->last_exported,
      'style' => 'text-align: center;',
    );
    $row[] = array(
      'data' => $data->last_order_id,
      'style' => 'text-align: center;',
    );
    $row[] = array(
      'data' => l(t('Edit'), 'admin/store/export/reports/update/' . $data->rid) . ' | ' . l(t('Delete'), 'admin/store/export/reports/delete/' . $data->rid),
      'style' => 'text-align: center;',
    );
    $rows[] = $row;
  }
  if (count($rows) == 0) {
    $rows = array(
      array(
        'data' => array(
          array(
            'align' => 'center',
            'colspan' => 4,
            'data' => t('THERE ARE CURRENTLY NO CONFIGURED EXPORT REPORTS'),
          ),
        ),
      ),
    );
  }
  $output = theme('table', $header, $rows);
  return $output;
}
function uc_csv_select_report_to_export_submit($form, $form_state) {
  $result = db_query("SELECT * FROM {uc_csv_reports} WHERE rid='%d';", $form_state['values']['rid']);
  $report = db_fetch_object($result);
  $statuses = array();
  foreach (unserialize($report->statuses) as $key => $value) {
    if ($value != '0') {
      $statuses[] = $value;
    }
  }
  $selects = array();
  $headers = array(
    'order_id',
    'last_name',
    'first_name',
    'email_address',
    'order_total',
    'product_count',
  );

  /* cache the list of countries and states in memory so we don't have to do repeated lookups */
  $result = db_query("SELECT country_id,country_name FROM {uc_countries}");
  while ($sdata = db_fetch_object($result)) {
    $country[$sdata->country_id] = $sdata->country_name;
  }
  $result = db_query("SELECT zone_id,zone_code FROM {uc_zones}");
  while ($sdata = db_fetch_object($result)) {
    $zone[$sdata->zone_id] = $sdata->zone_code;
  }
  if ($report->shipping_address == 1) {
    $selects[] = 'o.delivery_first_name,o.delivery_last_name,o.delivery_phone,o.delivery_company,o.delivery_street1,o.delivery_street2,o.delivery_city,o.delivery_zone,o.delivery_postal_code,o.delivery_country';
    $headers = array_merge($headers, array(
      'shipping_first_name',
      'shipping_last_name',
      'shipping_phone',
      'shipping_company',
      'shipping_street1',
      'shipping_street2',
      'shipping_city',
      'shipping_state',
      'shipping_zipcode',
      'shipping_country',
    ));
  }
  if ($report->billing_address == 1) {
    $selects[] = 'o.billing_phone,o.billing_company,o.billing_street1,o.billing_street2,o.billing_city,o.billing_zone,o.billing_postal_code,o.billing_country';
    $headers = array_merge($headers, array(
      'billing_phone',
      'billing_company',
      'billing_street1',
      'billing_street2',
      'billing_city',
      'billing_state',
      'billing_zipcode',
      'billing_country',
    ));
  }
  if ($report->products == 1) {
    $headers = array_merge($headers, array(
      'products',
      'tax',
      'shipping',
      'weight',
    ));
  }
  if (module_exists('uc_coupon')) {
    $headers = array_merge($headers, array(
      'coupons',
    ));
  }
  if ($report->orderby == 'orderid') {
    $orderby = 'o.order_id ASC';
  }
  elseif ($report->orderby == 'last_name') {
    $orderby = 'o.billing_last_name ASC';
  }
  else {
    $orderby = 'o.order_id ASC, o.billing_last_name ASC';
  }
  if (count($selects) > 0) {
    $sel = ',' . implode(',', $selects);
  }
  else {
    $sel = NULL;
  }

  /* reports may have been trackable at one time and then switched to non-trackable. as such we */

  /* need to track the state of the report. if it is not trackable, then set last_order_id to */

  /* 0 so that we get all the orders */
  if ($report->track < 1) {
    $report->last_order_id = 0;
  }
  $complete_order = array();
  $result = db_query("SELECT o.order_id, o.billing_last_name, o.billing_first_name, o.primary_email,o.order_total, o.product_count" . $sel . "\n                      FROM {uc_orders} o\n                      WHERE o.order_id > '" . $report->last_order_id . "' && o.order_status IN ('" . join("','", $statuses) . "')\n                      ORDER BY " . $orderby);
  while ($order = db_fetch_object($result)) {
    $order->delivery_zone = $zone[$order->delivery_zone];
    $order->delivery_country = $country[$order->delivery_country];
    $order->billing_zone = $zone[$order->billing_zone];
    $order->billing_country = $country[$order->billing_country];
    if ($report->products == 1) {
      $product = NULL;
      $calc_attribute = array();
      $total_weight = 0;
      $presults = db_query("SELECT model, title, qty, price, data, weight\n                            FROM {uc_order_products} \n                            WHERE order_id='%d'", $order->order_id);
      while ($pdata = db_fetch_object($presults)) {
        $total_weight += $pdata->weight;
        $data = unserialize($pdata->data);
        if (is_array($data['attributes'])) {
          foreach ($data['attributes'] as $key => $attribute) {
            foreach ($attribute as $display_attribute) {
              $calc_attribute[] = $key . ": " . $display_attribute;
            }
          }
        }
        if (count($calc_attribute) > 0) {
          $title = $pdata->title . " - " . join(',', $calc_attribute);
        }
        else {
          $title = $pdata->title;
        }
        $product .= $pdata->qty . ' - ' . $title . ' (' . $pdata->model . '): $' . number_format($pdata->price, 2) . 'ea.';
      }
      $order->products = $product;

      /* now we need to get the shipping and sales tax data for this order */

      /* we will do this in two queries for now - first we do tax */
      $stResult = db_query("SELECT amount AS sales_tax\n                            FROM uc_order_line_items\n                            WHERE order_id = '%d'\n                              AND type='tax'", $order->order_id);
      $stdata = db_fetch_object($stResult);
      if (isset($stdata->sales_tax)) {
        $order->sales_tax = $stdata->sales_tax;
      }
      else {
        $order->sales_tax = "N/A";
      }

      /* use the same sort of query to get the shipping amount */
      $stResult = db_query("SELECT amount AS shipping\n                            FROM uc_order_line_items\n                            WHERE order_id = '%d'\n                              AND type='shipping'", $order->order_id);
      $stdata = db_fetch_object($stResult);
      if (isset($stdata->shipping)) {
        $order->shipping = $stdata->shipping;
      }
      else {
        $order->shipping = "N/A";
      }

      // Assign our total weight
      $order->weight = $total_weight;

      // Check for coupons if we have uc_coupon enabled
      if (module_exists('uc_coupon')) {
        $coupons = array();
        $cresult = db_query("SELECT code FROM {uc_coupons_orders}\n                             WHERE oid = '%d'", $order->order_id);
        while ($cdata = db_fetch_object($cresult)) {
          $coupons[] = $cdata->code;
        }
        if (count($coupons) > 0) {
          $order->coupons = implode(',', $coupons);
        }
        else {
          $order->coupons = "No Coupon";
        }
      }
    }
    $complete_order[] = (array) $order;
  }

  /* update the table with our latest export time and order_id so we don't re-export things on */

  /* the next go-around - but only do this if it is a trackable report */
  if ($report->track > 0) {

    /* get the max order id for storage */
    $result = db_query('SELECT max(order_id) AS order_id FROM {uc_orders}');
    $odata = db_fetch_object($result);
    $max_order = $odata->order_id;

    /* insert the max id into our report table */
    $result = db_query("UPDATE {uc_csv_reports} \n                        SET last_order_id='%d', last_exported='%s'\n                        WHERE rid='%d';", $max_order, date('Y-m-d H:i:s', time()), $form_state['values']['rid']);
  }

  /* name the report file name after the given name for this report, just make it filename friendly */
  $file_name = uc_csv_clean_filename($report->report_name);

  /* if we are reporting as a csv, then use standard csv export. to do multi-byte reports, then use */

  /* the Microsoft excel XML export method */
  if ($report->file_type == 'csv') {

    /* output the file so we download */
    header('Content-Type: text/x-csv');
    header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');
    header('Content-Disposition: inline; filename="' . $file_name . '.csv"');
    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
    header('Pragma: public');
    echo implode(",", $headers) . "\n";
    foreach ($complete_order as $order) {
      $csv_array = array();
      foreach ($order as $cell) {
        $csv_array[] = $cell;
      }

      // We need to make sure the lines are properly escaped.
      // from comments at: http://php.net/manual/en/function.fputcsv.php
      $csv = fopen('php://temp/maxmemory:' . 1 * 1024 * 1024, 'r+');
      fputcsv($csv, $csv_array);
      rewind($csv);
      $source = stream_get_contents($csv);
      fclose($csv);
      $source .= "\n";
    }
    echo $source;
    exit;
  }
  elseif ($report->file_type == 'xls') {
    module_load_include('inc', 'uc_csv', 'inc/excel-export');

    // generate file (constructor parameters are optional)
    $xls = new Excel_XML('UTF-8', false, $report->report_name);
    $data = array(
      $headers,
    );
    foreach ($complete_order as $line) {
      $set = array();
      foreach ($line as $key => $value) {
        $set[] = $value;
      }
      $data[] = $set;
    }
    $xls
      ->addArray($data);
    $xls
      ->generateXML($file_name);
    exit;
  }
  else {
    drupal_goto('admin/store/export');
  }
}
function uc_csv_add_new_report($form_state, $report = NULL) {

  // if no report is passed, then create the class so we do not get warnings
  if ($report == NULL) {
    $report = new stdClass();
  }

  // set up our form of properties for the report being sought.
  $form = array();
  $form['rid'] = array(
    '#type' => 'hidden',
    '#default_value' => $report->rid,
  );
  $form['export_options'] = array(
    '#type' => 'fieldset',
    '#title' => t('Export Options'),
    '#weight' => 2,
    '#collapsible' => TRUE,
  );
  $form['status_options'] = array(
    '#type' => 'fieldset',
    '#title' => t('Order Statuses to Export'),
    '#weight' => 3,
    '#collapsible' => TRUE,
  );
  $form['other'] = array(
    '#type' => 'fieldset',
    '#title' => t('Other Options'),
    '#weight' => 4,
    '#collapsible' => TRUE,
  );
  $form['report_name'] = array(
    '#type' => 'textfield',
    '#title' => t('Report Name'),
    '#default_value' => $report->report_name,
    '#max_length' => 96,
    '#required' => TRUE,
    '#weight' => 1,
  );
  $default_options = array();
  if ($report->shipping_address) {
    $default_options[] = 'shipping';
  }
  if ($report->billing_address) {
    $default_options[] = 'billing';
  }
  if ($report->products) {
    $default_options[] = 'products';
  }
  $form['export_options']['options'] = array(
    '#type' => 'checkboxes',
    '#title' => t('Export Options'),
    '#default_value' => $default_options,
    '#options' => array(
      'shipping' => t('Shipping Address'),
      'billing' => t('Billing Address'),
      'products' => t('Products'),
    ),
    '#description' => t('Select the information you want exported in your report'),
  );

  /* get a list of the order statuses available/created */
  $statuses = array();
  if (isset($report->statuses) && $report->statuses) {
    $default_statuses = unserialize($report->statuses);
  }
  else {
    $default_statuses = array();
  }
  $result = db_query("SELECT order_status_id,title,state\n                      FROM {uc_order_statuses}\n                      ORDER BY weight ASC");
  while ($sdata = db_fetch_object($result)) {
    $statuses[$sdata->order_status_id] = $sdata->title;
  }
  $form['status_options']['statuses'] = array(
    '#type' => 'checkboxes',
    '#title' => t('Order Statuses to Export'),
    '#default_value' => $default_statuses,
    '#options' => $statuses,
    '#description' => t('Select the order statuses to be exported in this report.'),
  );
  $form['other']['orderby'] = array(
    '#type' => 'select',
    '#title' => t('Order By'),
    '#default_value' => $report->orderby,
    '#options' => array(
      'order_id' => 'Order ID',
      'last_name' => 'Customer Last Name',
    ),
    '#description' => t('How you would like your report sorted.'),
  );
  $form['other']['track'] = array(
    '#type' => 'select',
    '#title' => t('Track Last Exported Orders'),
    '#default_value' => $report->track,
    '#options' => array(
      '0' => 'No',
      '1' => 'Yes',
    ),
    '#description' => t('Select Yes if you would like for each export to be progressive instead of all inclusive.'),
  );
  $form['other']['file_type'] = array(
    '#type' => 'select',
    '#title' => t('Type of File To Export'),
    '#default_value' => $report->file_type,
    '#options' => array(
      'csv' => 'CSV (Comma Separated Values)',
      'xls' => 'Microsoft Excel XML Export',
    ),
    '#description' => t('The type of file to be exported. Use Excel if trying to export data with special characters (foreign language) in it.'),
  );
  $form['submit'] = array(
    '#type' => 'submit',
    '#value' => t('Update Report Options'),
    '#weight' => 5,
  );
  $form['#redirect'] = array(
    'admin/store/export/reports',
  );
  return $form;
}
function uc_csv_add_new_report_submit($form, $form_state) {
  $shipping = $form_state['values']['options']['shipping'] ? 1 : 0;
  $billing = $form_state['values']['options']['billing'] ? 1 : 0;
  $products = $form_state['values']['options']['products'] ? 1 : 0;
  if ($form_state['values']['rid'] > 0) {
    $array = array(
      'rid' => $form_state['values']['rid'],
      'report_name' => $form_state['values']['report_name'],
      'shipping_address' => $shipping,
      'billing_address' => $billing,
      'products' => $products,
      'orderby' => $form_state['values']['orderby'],
      'file_type' => $form_state['values']['file_type'],
      'statuses' => serialize($form_state['values']['statuses']),
      'track' => $form_state['values']['track'],
    );
    $result = drupal_write_record('uc_csv_reports', $array, 'rid');
  }
  else {
    $array = array(
      'report_name' => $form_state['values']['report_name'],
      'last_exported' => 0,
      'last_order_id' => 0,
      'shipping_address' => $shipping,
      'billing_address' => $billing,
      'products' => $products,
      'orderby' => $form_state['values']['orderby'],
      'file_type' => $form_state['values']['file_type'],
      'statuses' => serialize($form_state['values']['statuses']),
      'track' => $form_state['values']['track'],
    );
    $result = drupal_write_record('uc_csv_reports', $array);
  }
  if ($result == TRUE) {
    drupal_set_message("Report successfully saved");
  }
  else {
    drupal_set_message("Your report could not be saved. Please submit a bug report", 'error');
  }
  return;
}
function uc_csv_select_report_to_export($form_state) {
  $result = db_query('SELECT * FROM {uc_csv_reports} ORDER BY report_name ASC;');
  if ($result == TRUE) {
    $options = array();
    while ($ldata = db_fetch_object($result)) {
      $options[$ldata->rid] = $ldata->report_name;
    }
    $form = array();
    $form['rid'] = array(
      '#type' => 'select',
      '#title' => 'Select the report that you would like export',
      '#options' => $options,
    );
    $form['submit'] = array(
      '#type' => 'submit',
      '#value' => t('Export Selected Report'),
    );
    $form['#redirect'] = array(
      'admin/store/export/reports',
    );
    return $form;
  }
  else {
    drupal_set_message(t('Unable to get list of available reports.'));
    return;
  }
}
function uc_csv_delete_report($form_state, $report) {
  $form = array();
  $form['rid'] = array(
    '#type' => 'hidden',
    '#value' => $report->rid,
  );
  $form['report_name'] = array(
    '#type' => 'hidden',
    '#value' => $report->report_name,
  );
  $form['#redirect'] = array(
    'admin/store/export/index',
  );
  return confirm_form($form, t('Are you sure you wish to delete the %report export report?', array(
    '%report' => $report->report_name,
  )), 'admin/store/export/index', t('This action cannot be undone.'), t('Delete'), t('Cancel'));
}
function uc_csv_delete_report_submit($form_state, $form) {
  db_query("DELETE FROM {uc_csv_reports} WHERE rid='%d'", (int) $form['values']['rid']);
  drupal_set_message(t('%report report successfully deleted', array(
    '%report' => $form['values']['report_name'],
  )));
}
function uc_csv_clean_filename($str, $replace = array(), $delimiter = '-') {
  setlocale(LC_ALL, 'en_US.UTF8');
  if (!empty($replace)) {
    $str = str_replace((array) $replace, ' ', $str);
  }
  $clean = iconv('UTF-8', 'ASCII//TRANSLIT', $str);
  $clean = preg_replace("/[^a-zA-Z0-9\\/_|+ -]/", '', $clean);
  $clean = strtolower(trim($clean, '-'));
  $clean = preg_replace("/[\\/_|+ -]+/", $delimiter, $clean);
  return $clean;
}

Functions

Name	Description
uc_csv_add_new_report
uc_csv_add_new_report_submit
uc_csv_both_perms	uc_csv_both_perms() allow access if one of either the permissions are checked for a user
uc_csv_clean_filename
uc_csv_delete_report
uc_csv_delete_report_submit
uc_csv_menu	implementation of hook_menu();
uc_csv_perm	implementation of hook_perm();
uc_csv_report_grid
uc_csv_report_id_load	implementation of hook_load()
uc_csv_select_report_to_export
uc_csv_select_report_to_export_submit