uc_reports.module in Ubercart 5
Same filename and directory in other branches
Displays reports on sales, customers, and products to store admin
The reports module creates a few table-based reports for an ubercart store. Store admins can view reports for sales between different dates, customers by the products purchased and money spent, and products by their views, number sold, and revenue produced.
Development sponsored by the Ubercart project. http://www.ubercart.org
File
uc_reports/uc_reports.moduleView source
<?php
/**
* @file
* Displays reports on sales, customers, and products to store admin
*
* The reports module creates a few table-based reports for an ubercart store.
* Store admins can view reports for sales between different dates, customers
* by the products purchased and money spent, and products by their views,
* number sold, and revenue produced.
*
* Development sponsored by the Ubercart project. http://www.ubercart.org
*/
define('UC_REPORTS_MAX_RECORDS', PHP_INT_MAX);
/* ************************************************************************* *
* Hook Functions (Drupal) *
* ************************************************************************* */
/**
* Implementation of hook_help().
*/
function uc_reports_help($section) {
if (!strncmp($section, 'admin/store/reports/', 20)) {
$statuses = array();
foreach (variable_get('uc_reports_reported_statuses', array(
'completed',
)) as $status) {
$statuses[] = db_result(db_query("SELECT title FROM {uc_order_statuses} WHERE order_status_id = '%s'", $status));
}
$order_statuses = t('<b>Order statuses used:</b> @statuses', array(
'@statuses' => implode(', ', $statuses),
));
switch ($section) {
case 'admin/store/reports/customers':
return '<p>' . t("The following are total orders, products, sales, and average order totals for each store customer. Clicking on the header links will toggle a descending or ascending order for that column. Clicking on a customer's name will take you to a detailed list of orders that customer has made. Clicking on a customers username will take you to their account page.") . '</p><p>' . $order_statuses . '</p>';
case 'admin/store/reports/products':
return '<p>' . t('The table lists each product listed in the store, its amount sold, how many times it has been viewed, revenue it has produced, and gross profit it has generated. If you do not see the number of views you must enable the Statistics module on the <a href="!url">module administration page</a>.', array(
'!url' => url('admin/build/modules'),
)) . '</p><p>' . $order_statuses . '</p>';
case 'admin/store/reports/products/custom':
return t('Expand the fieldset below to customize the date range of this report, and the statuses of orders displayed.');
case 'admin/store/reports/sales':
return '<p>' . t('These are the sales for the last two days, average sales for the month, and the projected sales for the rest of the month. Further down the list you will see other sales statistics.') . '</p><p>' . $order_statuses . '</p>';
case 'admin/store/reports/sales/custom':
return t('Expand the fieldset below to customize the date range of this report, the statuses of orders displayed, and product display options.');
}
if (strpos($section, 'admin/store/reports/sales/year') === 0) {
$year = $arg[5] ? $arg[5] : format_date(time(), 'custom', "Y");
return '<p>' . t('This is the monthly break down of sales for the year @year. Clicking on each link will take you to a list of orders during that month.', array(
'@year' => $year,
)) . '</p><p>' . $order_statuses . '</p>';
}
}
}
/**
* Implementation of hook_menu().
*/
function uc_reports_menu($may_cache) {
global $user;
$items = array();
if ($may_cache) {
$items[] = array(
'path' => 'admin/store/settings/reports',
'title' => t('Report settings'),
'description' => t('View the report settings.'),
'callback' => 'drupal_get_form',
'callback arguments' => array(
'uc_reports_settings_overview',
),
'access' => user_access('administer store'),
'type' => MENU_NORMAL_ITEM,
);
$items[] = array(
'path' => 'admin/store/reports/customers',
'title' => t('Customer reports'),
'description' => t('View reports for store customers'),
'callback' => 'uc_reports_customers',
'access' => user_access('view reports'),
'type' => MENU_NORMAL_ITEM,
);
$items[] = array(
'path' => 'admin/store/reports/products',
'title' => t('Product reports'),
'description' => t('View product reports'),
'callback' => 'uc_reports_products',
'access' => user_access('view reports'),
'type' => MENU_NORMAL_ITEM,
);
$items[] = array(
'path' => 'admin/store/reports/products/summary',
'title' => t('Product report'),
'description' => t('View reports for store products'),
'access' => user_access('view reports'),
'type' => MENU_DEFAULT_LOCAL_TASK,
'weight' => -10,
);
$items[] = array(
'path' => 'admin/store/reports/products/custom',
'title' => t('Custom product report'),
'description' => t('View a customized product report'),
'callback' => 'uc_reports_products_custom',
'access' => user_access('view reports'),
'type' => MENU_LOCAL_TASK,
'weight' => -5,
);
$items[] = array(
'path' => 'admin/store/reports/sales',
'title' => t('Sales reports'),
'description' => t('View reports for store sales'),
'callback' => 'uc_reports_sales_summary',
'access' => user_access('view reports'),
'type' => MENU_NORMAL_ITEM,
);
$items[] = array(
'path' => 'admin/store/reports/sales/summary',
'title' => t('Sales summary'),
'description' => t('View summary of all store sales'),
'access' => user_access('view reports'),
'type' => MENU_DEFAULT_LOCAL_TASK,
'weight' => -10,
);
$items[] = array(
'path' => 'admin/store/reports/sales/year',
'title' => t('Sales per year'),
'description' => t('View store sales for a particular year'),
'callback' => 'uc_reports_sales_year',
'access' => user_access('view reports'),
'type' => MENU_LOCAL_TASK,
'weight' => -7,
);
$items[] = array(
'path' => 'admin/store/reports/sales/custom',
'title' => t('Custom sales summary'),
'description' => t('View a customized sales summary'),
'callback' => 'uc_reports_sales_custom',
'access' => user_access('view reports'),
'type' => MENU_LOCAL_TASK,
'weight' => -1,
);
}
else {
$items[] = array(
'path' => 'admin/store/reports/getcsv/' . arg(4) . '/' . arg(5),
'callback' => '_uc_reports_get_csv',
'callback arguments' => array(
arg(4),
arg(5),
),
'access' => user_access('view reports'),
'type' => MENU_CALLBACK,
);
drupal_add_css(drupal_get_path('module', 'uc_reports') . '/uc_reports.css');
}
return $items;
}
/**
* Implementation of hook_perm
*/
function uc_reports_perm() {
return array(
'view reports',
);
}
/* ************************************************************************* *
* Callback Functions, Forms, and Tables *
* ************************************************************************* */
/**
* Display the customer report
*/
function uc_reports_customers() {
$address_preference = variable_get('uc_customer_list_address', 'billing');
$first_name = $address_preference == 'billing' ? 'billing_first_name' : 'delivery_first_name';
$last_name = $address_preference == 'billing' ? 'billing_last_name' : 'delivery_last_name';
$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();
$csv_rows = array();
$header = array(
array(
'data' => t('#'),
),
array(
'data' => t('Customer'),
'field' => "ou.{$last_name}",
),
array(
'data' => t('Username'),
'field' => "u.name",
),
array(
'data' => t('Orders'),
'field' => 'orders',
),
array(
'data' => t('Products'),
'field' => 'products',
),
array(
'data' => t('Total'),
'field' => 'total',
'sort' => 'desc',
),
array(
'data' => t('Average'),
'field' => 'average',
),
);
$csv_rows[] = array(
t('#'),
t('Customer'),
t('Username'),
t('Orders'),
t('Products'),
t('Total'),
t('Average'),
);
$sql = '';
$sql_count = '';
switch ($GLOBALS['db_type']) {
case 'mysqli':
case 'mysql':
$sql = "SELECT u.uid, u.name, ou.{$first_name}, ou.{$last_name}, (SELECT COUNT(DISTINCT(order_id)) FROM {uc_orders} as o WHERE o.uid = u.uid AND o.order_status IN {$order_statuses}) as orders, (SELECT SUM(qty) FROM {uc_order_products} as ps LEFT JOIN {uc_orders} as os ON ps.order_id = os.order_id WHERE os.order_status IN {$order_statuses} AND os.uid = u.uid) as products, (SELECT SUM(ot.order_total) FROM {uc_orders} as ot WHERE ot.uid = u.uid AND ot.order_status IN {$order_statuses}) as total, ROUND((SELECT SUM(ot.order_total) FROM {uc_orders} as ot WHERE ot.uid = u.uid AND ot.order_status IN {$order_statuses})/(SELECT COUNT(DISTINCT(order_id)) FROM {uc_orders} as o WHERE o.uid = u.uid AND o.order_status IN {$order_statuses}), 2) as average FROM {users} as u LEFT JOIN {uc_orders} as ou ON u.uid = ou.uid WHERE u.uid > 0 GROUP BY u.uid";
$sql_count = "SELECT COUNT(DISTINCT(u.uid)) FROM {users} as u LEFT JOIN {uc_orders} as ou ON u.uid = ou.uid WHERE u.uid > 0";
break;
case 'pgsql':
$sql = "SELECT u.uid, u.name, ou.{$first_name}, ou.{$last_name}, (SELECT COUNT(DISTINCT(order_id)) FROM {uc_orders} as o WHERE o.uid = u.uid AND o.order_status IN {$order_statuses}) as orders, (SELECT SUM(qty) FROM {uc_order_products} as ps LEFT JOIN {uc_orders} as os ON ps.order_id = os.order_id WHERE os.order_status IN {$order_statuses} AND os.uid = u.uid) as products, (SELECT SUM(ot.order_total) FROM {uc_orders} as ot WHERE ot.uid = u.uid AND ot.order_status IN {$order_statuses}) as total, ROUND((SELECT SUM(ot.order_total) FROM {uc_orders} as ot WHERE ot.uid = u.uid AND ot.order_status IN {$order_statuses})/(SELECT COUNT(DISTINCT(order_id)) FROM {uc_orders} as o WHERE o.uid = u.uid AND o.order_status IN {$order_statuses}), 2) as average FROM {users} as u LEFT JOIN {uc_orders} as ou ON u.uid = ou.uid WHERE u.uid > 0 GROUP BY u.uid, u.name, ou.{$first_name}, ou.{$last_name}";
$sql_count = "SELECT COUNT(DISTINCT(u.uid)) FROM {users} as u LEFT JOIN {uc_orders} as ou ON u.uid = ou.uid WHERE u.uid > 0";
break;
}
$customers = pager_query($sql . tablesort_sql($header), $page_size, 0, $sql_count);
while ($customer = db_fetch_array($customers)) {
$name = !empty($customer[$last_name]) || !empty($customer[$first_name]) ? l($customer[$last_name] . ', ' . $customer[$first_name], 'admin/store/customers/orders/' . $customer['uid']) : l($customer['name'], 'admin/store/customers/orders/' . $customer['uid']);
$customer_number = $page * variable_get('uc_reports_table_size', 30) + (count($rows) + 1);
$customer_order_name = !empty($customer[$last_name]) || !empty($customer[$first_name]) ? $customer[$last_name] . ', ' . $customer[$first_name] : $customer['name'];
$customer_name = $customer['name'];
$orders = !empty($customer['orders']) ? $customer['orders'] : 0;
$products = !empty($customer['products']) ? $customer['products'] : 0;
$total_revenue = uc_currency_format($customer['total']);
$average_revenue = uc_currency_format($customer['average']);
$rows[] = array(
array(
'data' => $customer_number,
),
array(
'data' => $name,
'nowrap' => 'nowrap',
),
array(
'data' => l($customer_name, 'user/' . $customer['uid']),
'nowrap' => 'nowrap',
),
array(
'data' => $orders,
),
array(
'data' => $products,
),
array(
'data' => $total_revenue,
'nowrap' => 'nowrap',
),
array(
'data' => $average_revenue,
'nowrap' => 'nowrap',
),
);
$csv_rows[] = array(
$customer_number,
$customer_order_name,
$customer_name,
$orders,
$products,
$total_revenue,
$average_revenue,
);
}
if (empty($rows)) {
$rows[] = array(
array(
'data' => t('No customers found'),
'colspan' => count($header),
),
);
}
$csv_data = uc_reports_store_csv('uc_customers', $csv_rows);
$output = theme('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']) . ' ' . (!is_null($_GET['nopage']) ? l(t('Show paged records'), 'admin/store/reports/customers') : l(t('Show all records'), 'admin/store/reports/customers', array(), 'nopage=1')) . '</div>';
return $output;
}
/**
* Display the product reports
*/
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 = " {$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']) . ' ' . (!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;
}
/**
* Return a themed table for product reports.
*
* Straight duplication of theme_table, but our row handling is different.
*/
function theme_uc_reports_product_table($header, $rows, $attributes = array(), $caption = NULL) {
$output = '<table' . drupal_attributes($attributes) . ">\n";
if (isset($caption)) {
$output .= '<caption>' . $caption . "</caption>\n";
}
// Format the table header:
if (count($header)) {
$ts = tablesort_init($header);
// HTML requires that the thead tag has tr tags in it follwed by tbody
// tags. Using ternary operator to check and see if we have any rows.
$output .= count($rows) ? ' <thead><tr>' : ' <tr>';
foreach ($header as $cell) {
$cell = tablesort_header($cell, $header, $ts);
$output .= _theme_table_cell($cell, TRUE);
}
// Using ternary operator to close the tags based on whether or not there are rows
$output .= count($rows) ? " </tr></thead>\n" : "</tr>\n";
}
// Format the table rows:
if (count($rows)) {
$output .= "<tbody>\n";
$flip = array(
'even' => 'odd',
'odd' => 'even',
);
$class = 'even';
foreach ($rows as $number => $row) {
$attributes = array();
// Check if we're dealing with a simple or complex row
if (isset($row['data'])) {
foreach ($row as $key => $value) {
if ($key == 'data') {
$cells = $value;
}
elseif ($key == 'primary') {
$class = $flip[$class];
}
else {
$attributes[$key] = $value;
}
}
}
else {
$cells = $row;
}
// Add odd/even class
if (isset($attributes['class'])) {
$attributes['class'] .= ' ' . $class;
}
else {
$attributes['class'] = $class;
}
// Build row
$output .= ' <tr' . drupal_attributes($attributes) . '>';
$i = 0;
foreach ($cells as $cell) {
$cell = tablesort_cell($cell, $header, $ts, $i++);
$output .= _theme_table_cell($cell);
}
$output .= " </tr>\n";
}
$output .= "</tbody>\n";
}
$output .= "</table>\n";
return $output;
}
/**
* Display the product reports
*/
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;
}
// Form builder for the custom sales report.
function uc_reports_products_custom_form($values, $statuses) {
$form['search'] = array(
'#type' => 'fieldset',
'#title' => t('Customize product report parameters'),
'#description' => t('Adjust these values and update the report to build your custom product report. Once submitted, the report may be bookmarked for easy reference in the future.'),
'#collapsible' => TRUE,
'#collapsed' => TRUE,
);
$form['search']['start_date'] = array(
'#type' => 'date',
'#title' => t('Start date'),
'#default_value' => array(
'month' => format_date($values['start_date'], 'custom', 'n'),
'day' => format_date($values['start_date'], 'custom', 'j'),
'year' => format_date($values['start_date'], 'custom', 'Y'),
),
);
$form['search']['end_date'] = array(
'#type' => 'date',
'#title' => t('End date'),
'#default_value' => array(
'month' => format_date($values['end_date'], 'custom', 'n'),
'day' => format_date($values['end_date'], 'custom', 'j'),
'year' => format_date($values['end_date'], 'custom', 'Y'),
),
);
$options = array();
foreach (uc_order_status_list() as $status) {
$options[$status['id']] = $status['title'];
}
if ($statuses === FALSE) {
$statuses = variable_get('uc_reports_reported_statuses', array(
'completed',
));
}
$form['search']['status'] = array(
'#type' => 'select',
'#title' => t('Order statuses'),
'#description' => t('Only orders with selected statuses will be included in the report.') . '<br />' . t('Hold Ctrl + click to select multiple statuses.'),
'#options' => $options,
'#default_value' => $statuses,
'#multiple' => TRUE,
'#size' => 5,
);
$form['search']['submit'] = array(
'#type' => 'submit',
'#value' => t('Update report'),
);
return $form;
}
function uc_reports_products_custom_form_validate($form_id, $form_values) {
if (empty($form_values['status'])) {
form_set_error('status', t('You must select at least one order status.'));
}
}
function uc_reports_products_custom_form_submit($form_id, $form_values) {
$timezone_offset = _uc_reports_timezone_offset();
// Build the start and end dates from the form.
$start_date = gmmktime(0, 0, 0, $form_values['start_date']['month'], $form_values['start_date']['day'], $form_values['start_date']['year']);
$end_date = gmmktime(23, 59, 59, $form_values['end_date']['month'], $form_values['end_date']['day'], $form_values['end_date']['year']);
$args = array(
$start_date,
$end_date,
urlencode(implode(',', array_keys($form_values['status']))),
);
drupal_goto('admin/store/reports/products/custom/' . implode('/', $args));
}
// Displays the sales summary report.
function uc_reports_sales_summary() {
$timezone_offset = time() + _uc_reports_timezone_offset();
$order_statuses = _uc_reports_order_statuses();
$format = variable_get('uc_date_format_default', 'm/d/Y');
$date_month = gmdate('n', $timezone_offset);
$date_year = gmdate('Y', $timezone_offset);
$date_day_of_month = gmdate('j', $timezone_offset);
$date_days_in_month = gmdate('t', $timezone_offset);
$month_start = gmmktime(0, 0, 0, $date_month, 1, $date_year);
$month_end = gmmktime(23, 59, 59, $date_month, $date_days_in_month, $date_year);
$today_start = gmmktime(0, 0, 0, $date_month, $date_day_of_month, $date_year);
$today_end = gmmktime(23, 59, 59, $date_month, $date_day_of_month, $date_year);
// Build the report table header.
$header = array(
t('Sales data'),
t('Number of orders'),
t('Total revenue'),
t('Average order'),
);
// Calculate and add today's sales summary to the report table.
$today = _uc_reports_get_sales($today_start);
$rows[] = array(
l(t('Today, !date', array(
'!date' => format_date($today_start, 'custom', $format, 0),
)), 'admin/store/orders/search/results/0/0/0/0/0/0/' . $today_start . '/' . $today_end),
$today['total'],
uc_currency_format($today['income']),
uc_currency_format($today['average']),
);
// Calculate and add yesterday's sales summary to the report table.
$yesterday = _uc_reports_get_sales($today_start - 86400);
$rows[] = array(
l(t('Yesterday, !date', array(
'!date' => format_date($today_start - 86400, 'custom', $format, 0),
)), 'admin/store/orders/search/results/0/0/0/0/0/0/' . ($today_start - 86400) . '/' . ($today_end - 86400)),
$yesterday['total'],
uc_currency_format($yesterday['income']),
uc_currency_format($yesterday['average']),
);
// Get the sales report for the month.
$month = _uc_reports_get_sales($month_start, 'month');
$month_title = format_date($month_start, 'custom', 'M Y');
// Add the month-to-date details to the report table.
$rows[] = array(
l(t('Month-to-date, @month', array(
'@month' => $month_title,
)), 'admin/store/orders/search/results/0/0/0/0/0/0/' . $month_start . '/' . $month_end),
$month['total'],
uc_currency_format($month['income']),
uc_currency_format($month['average']),
);
// Calculate the daily averages for the month.
$daily_orders = round($month['total'] / $date_day_of_month, 2);
$daily_revenue = round($month['income'] / $date_day_of_month, 2);
if ($daily_orders > 0) {
$daily_average = round($daily_revenue / $daily_orders, 2);
}
else {
$daily_average = 0;
}
// Add the daily averages for the month to the report table.
$rows[] = array(
t('Daily average for @month', array(
'@month' => $month_title,
)),
$daily_orders,
uc_currency_format($daily_revenue),
'',
);
// Store the number of days remaining in the month.
$remaining_days = $date_days_in_month - $date_day_of_month;
// Add the projected totals for the month to the report table.
$rows[] = array(
t('Projected totals for @date', array(
'@date' => $month_title,
)),
round($month['total'] + $daily_orders * $remaining_days, 2),
uc_currency_format(round($month['income'] + $daily_revenue * $remaining_days, 2)),
'',
);
// Add the sales data report table to the output.
$output = theme('table', $header, $rows, array(
'class' => 'uc-sales-table',
));
// Build the header statistics table header.
$header = array(
array(
'data' => t('Statistics'),
'width' => '50%',
),
'',
);
$rows = array(
array(
array(
'data' => t('Grand total sales'),
),
array(
'data' => uc_currency_format(db_result(db_query("SELECT SUM(order_total) FROM {uc_orders} WHERE order_status IN {$order_statuses}"))),
),
),
array(
array(
'data' => t('Customers total'),
),
array(
'data' => db_result(db_query("SELECT COUNT(DISTINCT uid) FROM {uc_orders} WHERE order_status IN {$order_statuses}")),
),
),
array(
array(
'data' => t('New customers today'),
),
array(
'data' => db_result(db_query("SELECT COUNT(DISTINCT uid) FROM {uc_orders} WHERE order_status IN {$order_statuses} AND %d >= created AND created >= %d", $today_end, $today_start)),
),
),
array(
array(
'data' => t('Online customers'),
),
array(
'data' => db_result(db_query("SELECT COUNT(DISTINCT s.uid) FROM {sessions} as s LEFT JOIN {uc_orders} as o ON s.uid = o.uid WHERE s.uid > 0 AND o.order_status IN {$order_statuses}")),
),
),
);
// Add the statistics table to the output.
$output .= theme('table', $header, $rows, array(
'width' => '100%',
'class' => 'uc-sales-table',
));
// Build the total orders by status table header.
$header = array(
array(
'data' => t('Total orders by status'),
'width' => '50%',
),
'',
);
$rows = array();
$unknown = 0;
// Loop through the order statuses with their total number of orders.
$result = db_query("SELECT s.order_status_id, order_status, s.title, s.weight, COUNT(o.order_status) as order_count FROM {uc_orders} as o LEFT JOIN {uc_order_statuses} as s ON s.order_status_id = o.order_status GROUP BY s.order_status_id, order_status, s.title, s.weight ORDER BY s.weight DESC");
while ($status = db_fetch_array($result)) {
if (!empty($status['title'])) {
// Add the total number of orders with this status to the table.
$rows[] = array(
l($status['title'], 'admin/store/orders/sort/' . $status['order_status_id']),
$status['order_count'],
);
}
else {
// Keep track of the count of orders with an unknown status.
$unknown += $status['order_count'];
}
}
// Add the unknown status count to the table.
if ($unknown > 0) {
$rows[] = array(
t('Unknown status'),
$unknown,
);
}
// Add the total orders by status table to the output.
$output .= theme('table', $header, $rows, array(
'class' => 'uc-sales-table',
));
return $output;
}
// Displays the yearly sales report form and table.
function uc_reports_sales_year() {
$timezone_offset = time() + _uc_reports_timezone_offset();
$order_statuses = _uc_reports_order_statuses();
// Get the year for the report from the URL.
if (intval(arg(5)) == 0) {
$year = format_date($timezone_offset, 'custom', 'Y', 0);
}
else {
$year = arg(5);
}
// Build the header for the report table.
$header = array(
t('Month'),
t('Number of orders'),
t('Total revenue'),
t('Average order'),
);
// Build the header to the CSV export.
$csv_rows = array(
array(
t('Month'),
t('Number of orders'),
t('Total revenue'),
t('Average order'),
),
);
// For each month of the year...
for ($i = 1; $i <= 12; $i++) {
// Calculate the start and end timestamps for the month in local time.
$month_start = gmmktime(0, 0, 0, $i, 1, $year);
$month_end = gmmktime(23, 59, 59, $i + 1, 0, $year);
// Get the sales report for the month.
$month_sales = _uc_reports_get_sales($month_start, 'month');
// Calculate the average order total for the month.
if ($month_sales['total'] != 0) {
$month_average = round($month_sales['income'] / $month_sales['total'], 2);
}
else {
$month_average = 0;
}
// Add the month's row to the report table.
$rows[] = array(
l(gmdate('M Y', $month_start), 'admin/store/orders/search/results/0/0/0/0/0/0/' . $month_start . '/' . $month_end),
$month_sales['total'],
uc_currency_format($month_sales['income']),
uc_currency_format($month_average),
);
// Add the data to the CSV export.
$csv_rows[] = array(
gmdate('M Y', $month_start),
$month_sales['total'],
uc_currency_format($month_sales['income']),
uc_currency_format($month_average),
);
}
// Calculate the start and end timestamps for the year in local time.
$year_start = gmmktime(0, 0, 0, 1, 1, $year);
$year_end = gmmktime(23, 59, 59, 1, 0, $year + 1);
// Get the sales report for the year.
$year_sales = _uc_reports_get_sales($year_start, 'year');
// Calculate the average order total for the year.
if ($year_sales['total'] != 0) {
$year_average = round($year_sales['income'] / $year_sales['total'], 2);
}
else {
$year_average = 0;
}
// Add the total row to the report table.
$rows[] = array(
l(t('Total @year', array(
'@year' => $year,
)), 'admin/store/orders/search/results/0/0/0/0/0/0/' . $year_start . '/' . $year_end),
$year_sales['total'],
uc_currency_format($year_sales['income']),
uc_currency_format($year_average),
);
// Add the total data to the CSV export.
$csv_rows[] = array(
t('Total @year', array(
'@year' => $year,
)),
$year_sales['total'],
uc_currency_format($year_sales['income']),
uc_currency_format($year_average),
);
// Cache the CSV export.
$csv_data = uc_reports_store_csv('uc_sales_yearly', $csv_rows);
// Build the page output holding the form, table, and CSV export link.
$output = drupal_get_form('uc_reports_sales_year_form', $year) . 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;
}
// Form to specify a year for the yearly sales report.
function uc_reports_sales_year_form($year) {
$form['year'] = array(
'#type' => 'textfield',
'#title' => t('Sales year'),
'#default_value' => $year,
'#maxlength' => 4,
'#size' => 4,
'#prefix' => '<div class="sales-year">',
'#suffix' => '</div>',
);
$form['submit'] = array(
'#type' => 'submit',
'#value' => t('View'),
'#prefix' => '<div class="sales-year">',
'#suffix' => '</div>',
);
return $form;
}
function uc_reports_sales_year_form_submit($form_id, $form_values) {
drupal_goto('admin/store/reports/sales/year/' . $form_values['year']);
}
// Displays the custom sales report form and table.
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;
}
// Form builder for the custom sales report.
function uc_reports_sales_custom_form($values, $statuses) {
$form['search'] = array(
'#type' => 'fieldset',
'#title' => t('Customize sales report parameters'),
'#description' => t('Adjust these values and update the report to build your custom sales summary. Once submitted, the report may be bookmarked for easy reference in the future.'),
'#collapsible' => TRUE,
'#collapsed' => TRUE,
);
$form['search']['start_date'] = array(
'#type' => 'date',
'#title' => t('Start date'),
'#default_value' => array(
'month' => format_date($values['start_date'], 'custom', 'n', 0),
'day' => format_date($values['start_date'], 'custom', 'j', 0),
'year' => format_date($values['start_date'], 'custom', 'Y', 0),
),
);
$form['search']['end_date'] = array(
'#type' => 'date',
'#title' => t('End date'),
'#default_value' => array(
'month' => format_date($values['end_date'], 'custom', 'n', 0),
'day' => format_date($values['end_date'], 'custom', 'j', 0),
'year' => format_date($values['end_date'], 'custom', 'Y', 0),
),
);
$form['search']['length'] = array(
'#type' => 'select',
'#title' => t('Results breakdown'),
'#description' => t('Large daily reports may take a long time to display.'),
'#options' => array(
'day' => t('daily'),
'week' => t('weekly'),
'month' => t('monthly'),
'year' => t('yearly'),
),
'#default_value' => $values['length'],
);
$options = array();
foreach (uc_order_status_list() as $status) {
$options[$status['id']] = $status['title'];
}
if ($statuses === FALSE) {
$statuses = variable_get('uc_reports_reported_statuses', array(
'completed',
));
}
$form['search']['status'] = array(
'#type' => 'select',
'#title' => t('Order statuses'),
'#description' => t('Only orders with selected statuses will be included in the report.') . '<br />' . t('Hold Ctrl + click to select multiple statuses.'),
'#options' => $options,
'#default_value' => $statuses,
'#multiple' => TRUE,
'#size' => 5,
);
$form['search']['detail'] = array(
'#type' => 'checkbox',
'#title' => t('Show a detailed list of products ordered.'),
'#default_value' => $values['detail'],
);
$form['search']['submit'] = array(
'#type' => 'submit',
'#value' => t('Update report'),
);
return $form;
}
function uc_reports_sales_custom_form_validate($form_id, $form_values) {
if (empty($form_values['status'])) {
form_set_error('status', t('You must select at least one order status.'));
}
}
function uc_reports_sales_custom_form_submit($form_id, $form_values) {
$timezone_offset = _uc_reports_timezone_offset();
// Build the start and end dates from the form.
$start_date = gmmktime(0, 0, 0, $form_values['start_date']['month'], $form_values['start_date']['day'], $form_values['start_date']['year']);
$end_date = gmmktime(23, 59, 59, $form_values['end_date']['month'], $form_values['end_date']['day'], $form_values['end_date']['year']);
$args = array(
$start_date,
$end_date,
$form_values['length'],
urlencode(implode(',', array_keys($form_values['status']))),
$form_values['detail'],
);
drupal_goto('admin/store/reports/sales/custom/' . implode('/', $args));
}
// Form builder for the admin settings.
function uc_reports_settings_overview() {
$form['uc_reports_table_size'] = array(
'#type' => 'textfield',
'#title' => t('Paged table size'),
'#description' => t('The maximum number of rows displayed on one page for a report table.'),
'#default_value' => variable_get('uc_reports_table_size', 30),
);
$options = array();
foreach (uc_order_status_list() as $status) {
$options[$status['id']] = $status['title'];
}
$form['uc_reports_reported_statuses'] = array(
'#type' => 'select',
'#title' => t('Reported statuses'),
'#description' => t('Only orders with selected statuses will be included in reports.'),
'#options' => $options,
'#default_value' => variable_get('uc_reports_reported_statuses', array(
'completed',
)),
'#multiple' => TRUE,
);
return system_settings_form($form);
}
/******************************************************************************
* Module and Helper Functions *
******************************************************************************/
/**
* Store a CSV file for a report in Drupal's cache to be retrieved later
* @param $report_id
* A unique string that identifies the report of the CSV file
* @param $rows
* The rows (table header included) that make CSV file
* @return:
* An array containing the values need to build URL that return the CSV file of
* the report and the CSV data itself
*/
function uc_reports_store_csv($report_id, $rows) {
global $user;
$user_id = empty($user->uid) ? session_id() : $user->uid;
foreach ($rows as $row) {
foreach ($row as $index => $column) {
$row[$index] = '"' . str_replace('"', '""', $column) . '"';
}
$csv_output .= implode(',', $row) . "\n";
}
cache_set('uc_reports_' . $report_id . '_' . $user_id, 'cache', $csv_output, time() + 86400);
return array(
'user' => $user_id,
'report' => $report_id,
'csv' => $csv_output,
);
}
/**
* Retrieve a cached CSV report & send its data
*
* @param $report_id
* A unique string that identifies the specific report CSV to retrieve
* @param $user_id
* The user id to who's retrieving the report
* - (Equals uid for authenticated users)
* - (Equals session_id for anonymous users)
*/
function _uc_reports_get_csv($report_id, $user_id) {
global $user;
$user_check = empty($user->uid) ? session_id() : $user->uid;
$csv_data = cache_get('uc_reports_' . $report_id . '_' . $user_id, 'cache');
if (!$csv_data || $user_id != $user_check) {
drupal_set_message(t("The CSV data could not be retrieved. It's possible the data might have expired. Refresh the report page and try to retrieve the CSV file again."), 'error');
drupal_not_found();
exit;
}
else {
ob_end_clean();
$http_headers = array(
'Pragma: private',
'Expires: 0',
'Cache-Control: private, must-revalidate',
'Content-Transfer-Encoding: binary',
'Content-Length:' . strlen($csv_data->data),
'Content-Disposition: attachment; filename="' . $report_id . '.csv"',
'Content-Type: text/csv',
);
foreach ($http_headers as $header) {
$header = preg_replace('/\\r?\\n(?!\\t| )/', '', $header);
drupal_set_header($header);
}
print $csv_data->data;
exit;
}
}
/**
* Given a timestamp and time period function returns sales that occurred in
* that time period
*
* @param $time
* A UNIX time stamp representing the time in which to get sales data
* @param $period
* The amount of time over which to count sales (e.g. [1] day, month, year)
* @return:
* An associative array containing information about sales:
* - "date" => A string representing the day counting was started
* - "income" => The total revenue that occurred during the time period
* - "total" => The total number of orders completed during the time period
* - "average" => The average revenue produced for each order
*/
function _uc_reports_get_sales($time, $period = 'day') {
$timezone = _uc_reports_timezone_offset();
// Get the current date markers.
$date = array(
'day' => format_date($time, 'custom', 'j', 0),
'month' => format_date($time, 'custom', 'n', 0),
'year' => format_date($time, 'custom', 'Y', 0),
);
// Add one to the granularity chosen, and use it to calc the new time.
$date[$period] += 1;
$new_time = gmmktime(0, 0, 0, $date['month'], $date['day'], $date['year']);
// Set up the default SQL for getting orders with the proper status
// within this period.
$order_statuses = _uc_reports_order_statuses();
$sql_frag = " FROM {uc_orders} as o WHERE o.order_status IN {$order_statuses} AND created >= {$time} and created < {$new_time}";
// Get the total value of the orders.
$output = array(
'income' => 0,
);
$orders = db_query("SELECT o.order_total " . $sql_frag);
while ($order = db_fetch_object($orders)) {
$output['income'] += $order->order_total;
}
// Get the total amount of orders.
$count = db_result(db_query("SELECT COUNT(o.order_total) " . $sql_frag));
$output['total'] = $count;
// Average for this period.
$output['average'] = $count != 0 ? round($output['income'] / $count, 2) : 0;
return $output;
}
// Returns a SQL friendly array of order statuses for orders used in reports.
function _uc_reports_order_statuses() {
$statuses = variable_get('uc_reports_reported_statuses', array(
'completed',
));
return "('" . implode("', '", $statuses) . "')";
}
// Returns the sitewide timezone offset for use in reports.
function _uc_reports_timezone_offset() {
return variable_get('date_default_timezone', 0);
}
/**
* With a given time span for a report and specified interval for subreports,
* return a list of timespans for subreports over that report's time span.
*
* @param $start
* A UNIX time stamp representing the time to start the report.
* @param $end
* A UNIX time stamp representing the time to end the report.
* @param $granularity
* Text representing the amount of time for the subreport (e.g. 'day', 'week')
* @return:
* An array of keyed arrays with the following values:
* - "start": The starting point of the sub report
* - "end": The ending point of the sub report
*/
function _uc_reports_subreport_intervals($report_start, $report_end, $interval) {
$subreports = array();
for ($start = $report_start, $end = _uc_reports_end_interval($report_start, $interval); $start < $report_end; $start = $end + 1, $end = _uc_reports_end_interval($start, $interval)) {
$subreports[] = array(
'start' => $start,
'end' => $end > $report_end ? $report_end : $end,
);
}
return $subreports;
}
/**
* Given a timestamp and a length of time, return the offset timestamp
* @param $time
* A UNIX timestamp
* @param $interval
* The text representing the amount of time (e.g. 'day', 'week')
* @return:
* The offset UNIX timestamp
*/
function _uc_reports_end_interval($time, $interval = 'month') {
if (empty($time)) {
$time = time();
}
$temp = strtotime('+1 ' . $interval, $time) - 1;
switch ($interval) {
case 'day':
$temp = gmmktime(0, 0, -1, gmdate('n', $time), gmdate('j', $time) + 1, gmdate('Y', $time));
break;
case 'week':
$temp = gmmktime(0, 0, -1, gmdate('n', $time), gmdate('j', $time) + 7, gmdate('Y', $time));
break;
case 'month':
$temp = gmmktime(23, 59, 59, gmdate('n', $time) + 1, 0, gmdate('Y', $time));
break;
case 'year':
$temp = gmmktime(23, 59, 59, gmdate('n', $time), 0, gmdate('Y', $time) + 1);
break;
}
return $temp;
}Functions
Constants
Name |
Description |
|---|---|
| UC_REPORTS_MAX_RECORDS | @file Displays reports on sales, customers, and products to store admin |