function uc_reports_products_custom in Ubercart 6.2
Same name and namespace in other branches
- 5 uc_reports/uc_reports.module \uc_reports_products_custom()
- 7.3 uc_reports/uc_reports.admin.inc \uc_reports_products_custom()
Display the custom product report.
1 string reference to 'uc_reports_products_custom'
- uc_reports_menu in uc_reports/
uc_reports.module - Implements hook_menu().
File
- uc_reports/
uc_reports.admin.inc, line 303 - Reports administration menu items.
Code
function uc_reports_products_custom() {
$timezone = _uc_reports_timezone_offset();
$timezone_offset = time() + $timezone;
$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);
$product_types = array(
"'product'",
);
$csv_rows = array();
$types = db_query("SELECT DISTINCT(pcid) FROM {uc_product_classes}");
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 ($views_column) {
$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',
),
array(
'data' => t('Views'),
'field' => 'c.totalcount',
),
);
$csv_rows[] = array(
t('#'),
t('Product'),
t('Sold'),
t('Revenue'),
t('Gross'),
t('Views'),
);
$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);
$rows = array();
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 = empty($product['revenue']) ? 0 : $product['revenue'];
$gross_cell = uc_currency_format(empty($product['gross']) ? 0 : $product['gross']);
$gross_csv = empty($product['gross']) ? 0 : $product['gross'];
if (module_exists('uc_attribute')) {
$breakdown_product = $breakdown_sold = $breakdown_revenue = $breakdown_gross = '';
foreach (_uc_reports_product_get_skus($product['nid']) as $model) {
// was $models
$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']));
$sold = empty($sold) ? 0 : $sold;
$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']));
$revenue = empty($revenue) ? 0 : $revenue;
$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']));
$gross = empty($gross) ? 0 : $gross;
$breakdown_product .= "<br /> {$model}";
$product_csv .= "\n {$model}";
$breakdown_sold .= "<br />" . $sold;
$sold_csv .= "\n " . $sold;
$breakdown_revenue .= "<br />" . uc_currency_format($revenue);
$revenue_csv .= "\n " . $revenue;
$breakdown_gross .= "<br />" . uc_currency_format($gross);
$gross_csv .= "\n " . $gross;
}
$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 ($views_column) {
$views = empty($product['totalcount']) ? 0 : $product['totalcount'];
$rows[] = array(
array(
'data' => $row_cell,
),
array(
'data' => $product_cell,
),
array(
'data' => $sold_cell,
),
array(
'data' => $revenue_cell,
),
array(
'data' => $gross_cell,
),
array(
'data' => $views,
),
);
$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,
),
array(
'data' => $gross_cell,
),
);
$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']);
$output .= theme('table', $header, $rows, array(
'width' => '100%',
'class' => 'uc-sales-table',
));
$output .= '<div class="uc-reports-links">' . l(t('Export to CSV file.'), 'admin/store/reports/getcsv/' . $csv_data['report'] . '/' . $csv_data['user']) . ' ' . (isset($_GET['nopage']) ? l(t('Show paged records'), 'admin/store/reports/products/custom') : l(t('Show all records'), 'admin/store/reports/products/custom', array(
'query' => 'nopage=1',
))) . '</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/reports/settings', array(
'query' => 'destination=admin/store/reports/products/custom',
)),
)) . '</small>';
return $output;
}