You are here

Home » API reference » Ubercart 5 » uc_reports.module

function uc_reports_products in Ubercart 5

Same name and namespace in other branches
  1. 6.2 uc_reports/uc_reports.admin.inc \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
Implementation of hook_menu().

File

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

Code

function uc_reports_products() {
  $statistics = db_result(db_query("SELECT status FROM {system} WHERE name = 'statistics'"));
  $count_views = variable_get('statistics_count_content_views', FALSE);
  $page = !is_null($_GET['page']) ? intval($_GET['page']) : 0;
  $page_size = !is_null($_GET['nopage']) ? UC_REPORTS_MAX_RECORDS : variable_get('uc_reports_table_size', 30);
  $order_statuses = _uc_reports_order_statuses();
  $product_types = array(
    "'product'",
  );
  $types = db_query("SELECT DISTINCT(pcid) FROM {uc_product_classes}");
  $csv_rows = array();
  while ($type = db_fetch_object($types)) {
    $product_types[] = "'" . $type->pcid . "'";
  }
  if ($statistics && $count_views) {
    $header = array(
      array(
        'data' => t('#'),
      ),
      array(
        'data' => t('Product'),
        'field' => 'n.title',
      ),
      array(
        'data' => t('Views'),
        'field' => 'c.totalcount',
      ),
      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('Views'),
      t('Sold'),
      t('Revenue'),
      t('Gross'),
    );
    $sql = '';
    switch ($GLOBALS['db_type']) {
      case 'mysqli':
      case 'mysql':
        $sql = "SELECT n.nid, n.title, c.totalcount, (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.nid = n.nid) AS sold, (SELECT (SUM(p2.price * p2.qty)) FROM {uc_order_products} AS p2 LEFT JOIN {uc_orders} AS o2 ON p2.order_id = o2.order_id WHERE o2.order_status IN {$order_statuses} AND p2.nid = n.nid) AS revenue, (SELECT (SUM(p3.price * p3.qty) - SUM(p3.cost * p3.qty)) FROM {uc_order_products} AS p3 LEFT JOIN {uc_orders} AS o3 ON p3.order_id = o3.order_id WHERE o3.order_status IN {$order_statuses} AND p3.nid = n.nid) AS gross FROM {node} AS n LEFT JOIN {node_counter} AS c ON n.nid = c.nid WHERE type IN (" . implode(", ", $product_types) . ") GROUP BY n.nid DESC";
        break;
      case 'pgsql':
        $sql = "SELECT n.nid, n.title, c.totalcount, (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.nid = n.nid) AS sold, (SELECT (SUM(p2.price * p2.qty)) FROM {uc_order_products} AS p2 LEFT JOIN {uc_orders} AS o2 ON p2.order_id = o2.order_id WHERE o2.order_status IN {$order_statuses} AND p2.nid = n.nid) AS revenue, (SELECT (SUM(p3.price * p3.qty) - SUM(p3.cost * p3.qty)) FROM {uc_order_products} AS p3 LEFT JOIN {uc_orders} AS o3 ON p3.order_id = o3.order_id WHERE o3.order_status IN {$order_statuses} AND p3.nid = n.nid) AS gross FROM {node} AS n LEFT JOIN {node_counter} AS c ON n.nid = c.nid WHERE type IN (" . implode(", ", $product_types) . ") GROUP BY n.nid";
        break;
    }
  }
  else {
    $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'),
    );
    switch ($GLOBALS['db_type']) {
      case 'mysqli':
      case 'mysql':
        $sql = "SELECT n.nid, n.title, (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.nid = n.nid) AS sold, (SELECT (SUM(p2.price * p2.qty)) FROM {uc_order_products} AS p2 LEFT JOIN {uc_orders} AS o2 ON p2.order_id = o2.order_id WHERE o2.order_status IN {$order_statuses} AND p2.nid = n.nid) AS revenue, (SELECT (SUM(p3.price * p3.qty) - SUM(p3.cost * p3.qty)) FROM {uc_order_products} AS p3 LEFT JOIN {uc_orders} AS o3 ON p3.order_id = o3.order_id WHERE o3.order_status IN {$order_statuses} AND p3.nid = n.nid) AS gross FROM {node} AS n WHERE type IN (" . implode(', ', $product_types) . ') GROUP BY n.nid DESC';
        break;
      case 'pgsql':
        $sql = "SELECT n.nid, n.title, (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.nid = n.nid) AS sold, (SELECT (SUM(p2.price * p2.qty)) FROM {uc_order_products} AS p2 LEFT JOIN {uc_orders} AS o2 ON p2.order_id = o2.order_id WHERE o2.order_status IN {$order_statuses} AND p2.nid = n.nid) AS revenue, (SELECT (SUM(p3.price * p3.qty) - SUM(p3.cost * p3.qty)) FROM {uc_order_products} AS p3 LEFT JOIN {uc_orders} AS o3 ON p3.order_id = o3.order_id WHERE o3.order_status IN {$order_statuses} AND p3.nid = n.nid) AS gross FROM {node} AS n WHERE type IN (" . implode(', ', $product_types) . ') GROUP BY n.nid, n.title';
        break;
    }
  }
  $sql_count = "SELECT COUNT(nid) FROM {node} WHERE type IN (" . implode(", ", $product_types) . ")";
  $products = pager_query($sql . tablesort_sql($header), $page_size, 0, $sql_count);
  $row_cell = $page * variable_get('uc_reports_table_size', 30) + 1;
  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_cell = uc_currency_format(empty($product['revenue']) ? 0 : $product['revenue']);
    $revenue_csv = $revenue_cell;
    $gross_cell = uc_currency_format(empty($product['gross']) ? 0 : $product['gross']);
    $gross_csv = $gross_cell;

    // Primary product row.
    if ($statistics && $count_views) {
      $views = empty($product['totalcount']) ? 0 : $product['totalcount'];
      $rows[] = array(
        'data' => array(
          array(
            'data' => $row_cell,
          ),
          array(
            'data' => $product_cell,
          ),
          array(
            'data' => $views,
          ),
          array(
            'data' => "<strong>{$sold_cell}</strong>",
          ),
          array(
            'data' => "<strong>{$revenue_cell}</strong>",
            'nowrap' => 'nowrap',
          ),
          array(
            'data' => "<strong>{$gross_cell}</strong>",
            'nowrap' => 'nowrap',
          ),
        ),
        'primary' => TRUE,
      );
      $csv_rows[] = array(
        $row_cell,
        $product_csv,
        $views,
        $sold_csv,
        $revenue_csv,
        $gross_csv,
      );
    }
    else {
      $rows[] = array(
        'data' => array(
          array(
            'data' => $row_cell,
          ),
          array(
            'data' => $product_cell,
          ),
          array(
            'data' => "<strong>{$sold_cell}</strong>",
          ),
          array(
            'data' => "<strong>{$revenue_cell}</strong>",
            'nowrap' => 'nowrap',
          ),
          array(
            'data' => "<strong>{$gross_cell}</strong>",
            'nowrap' => 'nowrap',
          ),
        ),
        'primary' => TRUE,
      );
      $csv_rows[] = array(
        $row_cell,
        $product_csv,
        $sold_csv,
        $revenue_csv,
        $gross_csv,
      );
    }
    if (module_exists('uc_attribute')) {
      $product_models = db_query("SELECT model FROM {uc_product_adjustments} WHERE nid = %d", $product['nid']);
      $models = array(
        db_result(db_query("SELECT model FROM {uc_products} WHERE nid = %d", $product['nid'])),
      );
      unset($breakdown_product, $breakdown_sold, $breakdown_revenue, $breakdown_gross);
      while ($product_model = db_fetch_object($product_models)) {
        $models[] = $product_model->model;
      }
      foreach ($models as $model) {

        // Model breakdown rows.
        $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}";
        $breakdown_sold = !empty($sold) ? $sold : 0;
        $sold_csv = !empty($sold) ? $sold : 0;
        $breakdown_revenue = uc_currency_format(!empty($revenue) ? $revenue : 0);
        $revenue_csv = uc_currency_format(!empty($revenue) ? $revenue : 0);
        $breakdown_gross = uc_currency_format(!empty($gross) ? $gross : 0);
        $gross_csv = uc_currency_format(!empty($gross) ? $gross : 0);
        if ($statistics && $count_views) {
          $rows[] = array(
            array(
              'data' => '',
            ),
            array(
              'data' => $breakdown_product,
            ),
            array(
              'data' => '',
            ),
            array(
              'data' => $breakdown_sold,
            ),
            array(
              'data' => $breakdown_revenue,
              'nowrap' => 'nowrap',
            ),
            array(
              'data' => $breakdown_gross,
              'nowrap' => 'nowrap',
            ),
          );
          $csv_rows[] = array(
            '',
            $product_csv,
            '',
            $sold_csv,
            $revenue_csv,
            $gross_csv,
          );
        }
        else {
          $rows[] = array(
            'data' => array(
              array(
                'data' => '',
              ),
              array(
                'data' => $breakdown_product,
              ),
              array(
                'data' => $breakdown_sold,
              ),
              array(
                'data' => $breakdown_revenue,
                'nowrap' => 'nowrap',
              ),
              array(
                'data' => $breakdown_gross,
                'nowrap' => 'nowrap',
              ),
            ),
          );
          $csv_rows[] = array(
            '',
            $product_csv,
            $sold_csv,
            $revenue_csv,
            $gross_csv,
          );
        }
      }
    }
    $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;' . (!is_null($_GET['nopage']) ? l(t('Show paged records'), 'admin/store/reports/products') : l(t('Show all records'), 'admin/store/reports/products', array(), '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/logs/settings', 'destination=admin/store/reports/products'),
  )) . '</small>';
  return $output;
}