You are here

Home » API reference » Ubercart 5 » uc_reports.module

function uc_reports_sales_custom in Ubercart 5

Same name and namespace in other branches
  1. 6.2 uc_reports/uc_reports.admin.inc \uc_reports_sales_custom()
  2. 7.3 uc_reports/uc_reports.admin.inc \uc_reports_sales_custom()
1 string reference to 'uc_reports_sales_custom'
uc_reports_menu in uc_reports/uc_reports.module
Implementation of hook_menu().

File

uc_reports/uc_reports.module, line 972
Displays reports on sales, customers, and products to store admin

Code

function uc_reports_sales_custom() {
  $timezone = _uc_reports_timezone_offset();
  $timezone_offset = time() + $timezone;
  $format = variable_get('uc_date_format_default', 'm/d/Y');

  // Use default report parameters if we don't detect values in the URL.
  if (arg(5) == '') {
    $args = array(
      'start_date' => gmmktime(0, 0, 0, gmdate('n', $timezone_offset), 1, gmdate('Y', $timezone_offset) - 1),
      'end_date' => time(),
      'length' => 'month',
      'status' => FALSE,
      'detail' => FALSE,
    );
  }
  else {
    $args = array(
      'start_date' => arg(5),
      'end_date' => arg(6),
      'length' => arg(7),
      'status' => explode(',', urldecode(arg(8))),
      'detail' => arg(9),
    );
  }

  // Pull the order statuses into a SQL friendly array.
  if ($args['status'] === FALSE) {
    $order_statuses = _uc_reports_order_statuses();
  }
  else {
    $order_statuses = "('" . implode("', '", $args['status']) . "')";
  }

  // Build the header for the report table.
  $header = array(
    t('Date'),
    t('Number of orders'),
    t('Products sold'),
    t('Total revenue'),
  );

  // Build the header to the CSV export.
  $csv_rows = array(
    array(
      t('Date'),
      t('Number of orders'),
      t('Products sold'),
      t('Total revenue'),
    ),
  );

  // Grab the subreports based on the date range and the report breakdown.
  $subreports = _uc_reports_subreport_intervals($args['start_date'], $args['end_date'], $args['length']);

  // Loop through the subreports and build the report table.
  foreach ($subreports as $subreport) {
    $product_data = '';
    $product_csv = '';
    $order_data = '';
    $order_csv = '';

    // Create the date title for the subreport.
    if ($args['length'] == 'day') {
      $date = format_date($subreport['start'], 'custom', $format . ' - D', 0);
    }
    else {
      $date = format_date($subreport['start'], 'custom', $format, 0) . ' - ' . format_date($subreport['end'], 'custom', $format, 0);
    }

    // Build the order data for the subreport.
    $result = db_query("SELECT COUNT(*) as count, title FROM {uc_orders} LEFT JOIN {uc_order_statuses} ON order_status_id = order_status WHERE %d <= created AND created <= %d AND order_status IN {$order_statuses} GROUP BY order_status, {uc_order_statuses}.title, {uc_order_statuses}.weight ORDER BY weight ASC", $subreport['start'], $subreport['end']);
    $statuses = array();

    // Put the order counts into an array by status.
    while ($status = db_fetch_object($result)) {
      $statuses[] = t('!count - @title', array(
        '!count' => $status->count,
        '@title' => $status->title,
      ));
    }
    $order_data = implode('<br />', $statuses);
    $order_csv = implode("\n", $statuses);

    // Build the product data for the subreport.
    if ($args['detail']) {

      // Grab the detailed product breakdown if selected.
      $result = db_query("SELECT SUM(op.qty) as count, n.title, n.nid FROM {uc_order_products} as op LEFT JOIN {uc_orders} as o ON o.order_id = op.order_id LEFT JOIN {node} as n ON n.nid = op.nid WHERE %d <= o.created AND o.created <= %d AND o.order_status IN {$order_statuses} GROUP BY n.nid ORDER BY count DESC, n.title ASC", $subreport['start'], $subreport['end']);
      while ($product_breakdown = db_fetch_object($result)) {
        $product_data .= $product_breakdown->count . ' x ' . l($product_breakdown->title, 'node/' . $product_breakdown->nid) . "<br />\n";
        $product_csv .= $product_breakdown->count . ' x ' . $product_breakdown->title . "\n";
      }
    }
    else {

      // Otherwise just display the total number of products sold.
      $product_data = db_result(db_query("SELECT SUM(qty) FROM {uc_orders} as o LEFT JOIN {uc_order_products} as op ON o.order_id = op.order_id WHERE %d <= created AND created <= %d AND order_status IN {$order_statuses}", $subreport['start'], $subreport['end']));
      $product_csv = $product_data;
    }

    // Tally up the revenue from the orders.
    $revenue_count = db_result(db_query("SELECT SUM(order_total) FROM {uc_orders} WHERE %d <= created AND created <= %d AND order_status IN {$order_statuses}", $subreport['start'], $subreport['end']));

    // Add the subreport's row to the report table.
    $rows[] = array(
      array(
        'data' => $date,
        'nowrap' => 'nowrap',
      ),
      empty($order_data) ? '0' : $order_data,
      empty($product_data) ? '0' : $product_data,
      uc_currency_format($revenue_count),
    );

    // Add the data to the CSV export.
    $csv_rows[] = array(
      $date,
      empty($order_csv) ? '0' : $order_csv,
      empty($product_csv) ? '0' : $product_csv,
      uc_currency_format($revenue_count),
    );
  }

  // Calculate the totals for the report.
  $order_total = db_result(db_query("SELECT COUNT(*) FROM {uc_orders} WHERE %d <= created AND created <= %d AND order_status IN {$order_statuses}", $args['start_date'], $args['end_date']));
  $product_total = db_result(db_query("SELECT SUM(qty) FROM {uc_orders} AS o LEFT JOIN {uc_order_products} AS op ON o.order_id = op.order_id WHERE %d <= created AND created <= %d AND order_status IN {$order_statuses}", $args['start_date'], $args['end_date']));
  $revenue_total = db_result(db_query("SELECT SUM(order_total) FROM {uc_orders} WHERE %d <= created AND created <= %d AND order_status IN {$order_statuses}", $args['start_date'], $args['end_date']));

  // Add the total row to the report table.
  $rows[] = array(
    t('Total'),
    $order_total,
    $product_total,
    uc_currency_format($revenue_total),
  );

  // Add the total data to the CSV export.
  $csv_rows[] = array(
    t('Total'),
    $order_total,
    $product_total,
    uc_currency_format($revenue_total),
  );

  // Cache the CSV export.
  $csv_data = uc_reports_store_csv('uc_sales_custom', $csv_rows);

  // Build the page output holding the form, table, and CSV export link.
  $output = drupal_get_form('uc_reports_sales_custom_form', $args, $args['status']) . theme('table', $header, $rows, array(
    'width' => '100%',
    'class' => 'uc-sales-table',
  )) . '<div class="uc-reports-links">' . l(t('Export to CSV file.'), 'admin/store/reports/getcsv/' . $csv_data['report'] . '/' . $csv_data['user']) . '</div>';
  return $output;
}