function uc_reports_products_custom in Ubercart 5
Same name and namespace in other branches
- 6.2 uc_reports/uc_reports.admin.inc \uc_reports_products_custom()
- 7.3 uc_reports/uc_reports.admin.inc \uc_reports_products_custom()
Display the product reports
1 string reference to 'uc_reports_products_custom'
- uc_reports_menu in uc_reports/
uc_reports.module - Implementation of hook_menu().
File
- uc_reports/
uc_reports.module, line 477 - Displays reports on sales, customers, and products to store admin
Code
function uc_reports_products_custom() {
$timezone = _uc_reports_timezone_offset();
$timezone_offset = time() + $timezone;
$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);
$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 . "'";
}
// 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(),
'status' => FALSE,
);
}
else {
$args = array(
'start_date' => arg(5),
'end_date' => arg(6),
'status' => explode(',', urldecode(arg(7))),
);
}
// 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']) . "')";
}
$time_condition = "o.created >= " . $args['start_date'] . " AND o.created <= " . $args['end_date'];
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 AND {$time_condition}) AS sold, (SELECT (SUM(p2.price * p2.qty)) FROM {uc_order_products} AS p2 LEFT JOIN {uc_orders} AS o ON p2.order_id = o.order_id WHERE o.order_status IN {$order_statuses} AND p2.nid = n.nid AND {$time_condition}) AS revenue, (SELECT (SUM(p3.price * p3.qty) - SUM(p3.cost * p3.qty)) FROM {uc_order_products} AS p3 LEFT JOIN {uc_orders} AS o ON p3.order_id = o.order_id WHERE o.order_status IN {$order_statuses} AND p3.nid = n.nid AND {$time_condition}) 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 AND {$time_condition}) AS sold, (SELECT (SUM(p2.price * p2.qty)) FROM {uc_order_products} AS p2 LEFT JOIN {uc_orders} AS o ON p2.order_id = o.order_id WHERE o.order_status IN {$order_statuses} AND p2.nid = n.nid AND {$time_condition}) AS revenue, (SELECT (SUM(p3.price * p3.qty) - SUM(p3.cost * p3.qty)) FROM {uc_order_products} AS p3 LEFT JOIN {uc_orders} AS o ON p3.order_id = o.order_id WHERE o.order_status IN {$order_statuses} AND p3.nid = n.nid AND {$time_condition}) 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 AND {$time_condition}) AS sold, (SELECT (SUM(p2.price * p2.qty)) FROM {uc_order_products} AS p2 LEFT JOIN {uc_orders} AS o ON p2.order_id = o.order_id WHERE o.order_status IN {$order_statuses} AND p2.nid = n.nid AND {$time_condition}) AS revenue, (SELECT (SUM(p3.price * p3.qty) - SUM(p3.cost * p3.qty)) FROM {uc_order_products} AS p3 LEFT JOIN {uc_orders} AS o ON p3.order_id = o.order_id WHERE o.order_status IN {$order_statuses} AND p3.nid = n.nid AND {$time_condition}) 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 AND {$time_condition}) AS sold, (SELECT (SUM(p2.price * p2.qty)) FROM {uc_order_products} AS p2 LEFT JOIN {uc_orders} AS o ON p2.order_id = o.order_id WHERE o.order_status IN {$order_statuses} AND p2.nid = n.nid AND {$time_condition}) AS revenue, (SELECT (SUM(p3.price * p3.qty) - SUM(p3.cost * p3.qty)) FROM {uc_order_products} AS p3 LEFT JOIN {uc_orders} AS o ON p3.order_id = o.order_id WHERE o.order_status IN {$order_statuses} AND p3.nid = n.nid AND {$time_condition}) 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);
while ($product = db_fetch_array($products)) {
$row_cell = $page * variable_get('uc_reports_table_size', 30) + count($rows) + 1;
$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;
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) {
$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 AND {$time_condition}", $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 AND {$time_condition}", $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 AND {$time_condition}", $model, $product['nid']));
$breakdown_product .= "<br /> {$model}";
$product_csv .= "\n {$model}";
$breakdown_sold .= "<br />" . (!empty($sold) ? $sold : 0);
$sold_csv .= "\n " . (!empty($sold) ? $sold : 0);
$breakdown_revenue .= "<br />" . uc_currency_format(!empty($revenue) ? $revenue : 0);
$revenue_csv .= "\n " . uc_currency_format(!empty($revenue) ? $revenue : 0);
$breakdown_gross .= "<br />" . uc_currency_format(!empty($gross) ? $gross : 0);
$gross_csv .= "\n " . uc_currency_format(!empty($gross) ? $gross : 0);
}
$product_cell = $product_cell . $breakdown_product;
$sold_cell = '<strong>' . $sold_cell . '</strong>' . $breakdown_sold;
$revenue_cell = '<strong>' . $revenue_cell . '</strong>' . $breakdown_revenue;
$gross_cell = '<strong>' . $gross_cell . '</strong>' . $breakdown_gross;
}
if ($statistics && $count_views) {
$views = empty($product['totalcount']) ? 0 : $product['totalcount'];
$rows[] = array(
array(
'data' => $row_cell,
),
array(
'data' => $product_cell,
),
array(
'data' => $views,
),
array(
'data' => $sold_cell,
),
array(
'data' => $revenue_cell,
'nowrap' => 'nowrap',
),
array(
'data' => $gross_cell,
'nowrap' => 'nowrap',
),
);
$csv_rows[] = array(
$row_cell,
$product_csv,
$views,
$sold_csv,
$revenue_csv,
$gross_csv,
);
}
else {
$rows[] = array(
array(
'data' => $row_cell,
),
array(
'data' => $product_cell,
),
array(
'data' => $sold_cell,
),
array(
'data' => $revenue_cell,
'nowrap' => 'nowrap',
),
array(
'data' => $gross_cell,
'nowrap' => 'nowrap',
),
);
$csv_rows[] = array(
$row_cell,
$product_csv,
$sold_csv,
$revenue_csv,
$gross_csv,
);
}
}
if (empty($rows)) {
$rows[] = array(
array(
'data' => t('No products found'),
'colspan' => count($header),
),
);
}
$csv_data = uc_reports_store_csv('uc_products', $csv_rows);
// Build the page output holding the form, table, and CSV export link.
$output = drupal_get_form('uc_reports_products_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>';
$output .= theme_pager(NULL, $page_size);
$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/custom'),
)) . '</small>';
return $output;
}