function uc_reports_products in Ubercart 6.2

Same name and namespace in other branches

1. 5 uc_reports/uc_reports.module \uc_reports_products()
2. 7.3 uc_reports/uc_reports.admin.inc \uc_reports_products()

Display the product reports

1 string reference to 'uc_reports_products'

uc_reports_menu in uc_reports/uc_reports.module

Implements hook_menu().

File

uc_reports/uc_reports.admin.inc, line 119

Reports administration menu items.

Code

function uc_reports_products() {
  $views_column = module_exists('statistics') && variable_get('statistics_count_content_views', FALSE);
  $page = isset($_GET['page']) ? intval($_GET['page']) : 0;
  $page_size = isset($_GET['nopage']) ? UC_REPORTS_MAX_RECORDS : variable_get('uc_reports_table_size', 30);
  $order_statuses = _uc_reports_order_statuses();
  $row_cell = $page * variable_get('uc_reports_table_size', 30) + 1;
  $csv_rows = array();

  // Hard code the ignore of the product kit for this report.
  $ignored_types = array(
    'product_kit',
  );

  // Build an array of valid product types to include on the report.
  $product_types = array();
  foreach (uc_product_types() as $type) {

    // Pass over any ignored types.
    if (!in_array($type, $ignored_types)) {
      $product_types[] = "'" . $type . "'";
    }
  }
  $product_types = '(' . implode(', ', $product_types) . ')';
  $header = array(
    array(
      'data' => t('#'),
    ),
    array(
      'data' => t('Product'),
      'field' => 'n.title',
    ),
    array(
      'data' => t('Sold'),
      'field' => 'sold',
    ),
    array(
      'data' => t('Revenue'),
      'field' => 'revenue',
      'sort' => 'desc',
    ),
    array(
      'data' => t('Gross'),
      'field' => 'gross',
    ),
  );
  $csv_rows[] = array(
    t('#'),
    t('Product'),
    t('Sold'),
    t('Revenue'),
    t('Gross'),
  );
  if ($views_column) {
    $header[] = array(
      'data' => t('Views'),
      'field' => 'nc.totalcount',
    );
    $csv_rows[0][] = t('Views');
  }
  $var_tables = "{uc_order_products} AS uop LEFT JOIN {uc_orders} AS uo ON uop.order_id = uo.order_id WHERE uo.order_status IN {$order_statuses} AND uop.nid = n.nid";
  $sql_vars = array(
    "n.nid",
    "n.title",
    "(SELECT SUM(uop.qty) FROM {$var_tables}) AS sold",
    "(SELECT (SUM(uop.price * uop.qty) - SUM(uop.cost * uop.qty)) FROM {$var_tables}) AS gross",
    "(SELECT (SUM(uop.price * uop.qty)) FROM {$var_tables}) AS revenue",
  );
  if ($views_column) {
    $sql_vars[] = "nc.totalcount";
  }
  $sql_vars = implode(", ", $sql_vars);
  $sql_tables = "{node} as n";
  if ($views_column) {
    $sql_tables .= " LEFT JOIN {node_counter} AS nc ON n.nid = nc.nid";
  }
  $sql_conditions = "n.type IN {$product_types}";
  $sql_count = "SELECT COUNT(n.nid) FROM {$sql_tables} WHERE {$sql_conditions}";
  $context = array(
    'revision' => 'formatted-original',
    'type' => 'amount',
  );
  $products = pager_query("SELECT {$sql_vars} FROM {$sql_tables} WHERE {$sql_conditions} GROUP BY n.nid, n.title" . tablesort_sql($header), $page_size, 0, $sql_count);
  while ($product = db_fetch_array($products)) {
    $product_cell = l($product['title'], 'node/' . $product['nid']);
    $product_csv = $product['title'];
    $sold_cell = empty($product['sold']) ? 0 : $product['sold'];
    $sold_csv = $sold_cell;
    $revenue_csv = empty($product['revenue']) ? 0 : $product['revenue'];
    $revenue_cell = uc_price($revenue_csv, $context);
    $gross_csv = empty($product['gross']) ? 0 : $product['gross'];
    $gross_cell = uc_price($gross_csv, $context);
    $row = array(
      'data' => array(
        $row_cell,
        $product_cell,
        "<strong>{$sold_cell}</strong>",
        "<strong>{$revenue_cell}</strong>",
        "<strong>{$gross_cell}</strong>",
      ),
      'primary' => TRUE,
    );
    $csv_row = array(
      $row_cell,
      $product_csv,
      $sold_csv,
      $revenue_csv,
      $gross_csv,
    );
    if ($views_column) {
      $views = isset($product['totalcount']) ? $product['totalcount'] : 0;
      $row['data'][] = $views;
      $csv_row[] = $views;
    }
    $rows[] = $row;
    $csv_rows[] = $csv_row;
    if (module_exists('uc_attribute')) {

      // Get the SKUs from this product.
      $models = _uc_reports_product_get_skus($product['nid']);

      // Add the product breakdown rows
      foreach ($models as $model) {
        $sold = db_result(db_query("SELECT SUM(qty) FROM {uc_order_products} AS p LEFT JOIN {uc_orders} AS o ON p.order_id = o.order_id WHERE o.order_status IN {$order_statuses} AND p.model = '%s' AND p.nid = %d", $model, $product['nid']));
        $revenue = db_result(db_query("SELECT SUM(p.price * p.qty) FROM {uc_order_products} AS p LEFT JOIN {uc_orders} AS o ON p.order_id = o.order_id WHERE o.order_status IN {$order_statuses} AND p.model = '%s' AND p.nid = %d", $model, $product['nid']));
        $gross = db_result(db_query("SELECT (SUM(p.price * p.qty) - SUM(p.cost * p.qty)) FROM {uc_order_products} AS p LEFT JOIN {uc_orders} AS o ON p.order_id = o.order_id WHERE o.order_status IN {$order_statuses} AND p.model = '%s' AND p.nid = %d", $model, $product['nid']));
        $breakdown_product = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{$model}";
        $product_csv = "     {$model}";
        $sold_csv = !empty($sold) ? $sold : 0;
        $breakdown_sold = $sold_csv;
        $context['revision'] = 'themed';
        $revenue_csv = !empty($revenue) ? $revenue : 0;
        $breakdown_revenue = uc_price($revenue_csv, $context);
        $gross_csv = !empty($gross) ? $gross : 0;
        $breakdown_gross = uc_price($gross_csv, $context);
        $context['revision'] = 'formatted';
        $row = array(
          'data' => array(
            '',
            $breakdown_product,
            $breakdown_sold,
            $breakdown_revenue,
            $breakdown_gross,
          ),
        );
        $csv_row = array(
          '',
          $product_csv,
          $sold_csv,
          $revenue_csv,
          $gross_csv,
        );
        if ($views_column) {
          $row['data'][] = '';
          $csv_row[] = '';
        }
        $rows[] = $row;
        $csv_rows[] = $csv_row;
      }
    }
    $row_cell++;
  }
  if (empty($rows)) {
    $rows[] = array(
      array(
        'data' => t('No products found'),
        'colspan' => count($header),
      ),
    );
  }
  $csv_data = uc_reports_store_csv('uc_products', $csv_rows);
  $output = theme('uc_reports_product_table', $header, $rows, array(
    'width' => '100%',
    'class' => 'uc-sales-table',
  ));
  $output .= theme('pager', NULL, $page_size);
  $output .= '<div class="uc-reports-links">' . l(t('Export to CSV file.'), 'admin/store/reports/getcsv/' . $csv_data['report'] . '/' . $csv_data['user']) . '&nbsp;&nbsp;&nbsp;' . (isset($_GET['nopage']) ? l(t('Show paged records'), 'admin/store/reports/products') : l(t('Show all records'), 'admin/store/reports/products', array(
    'query' => 'nopage=1',
  ))) . '</div>';
  $output .= '<small>*' . t('Make sure %setting_name is set to %state in the <a href="!url">access log settings page</a> to enable views column.', array(
    '%setting_name' => 'count content views',
    '%state' => 'enabled',
    '!url' => url('admin/reports/settings', array(
      'query' => 'destination=admin/store/reports/products',
    )),
  )) . '</small>';
  return $output;
}