function uc_reports_customers in Ubercart 6.2
Same name and namespace in other branches
- 5 uc_reports/uc_reports.module \uc_reports_customers()
- 7.3 uc_reports/uc_reports.admin.inc \uc_reports_customers()
Display the customer report
1 string reference to 'uc_reports_customers'
- uc_reports_menu in uc_reports/
uc_reports.module - Implements hook_menu().
File
- uc_reports/
uc_reports.admin.inc, line 41 - Reports administration menu items.
Code
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 = isset($_GET['page']) ? intval($_GET['page']) : 0;
$page_size = isset($_GET['nopage']) ? UC_REPORTS_MAX_RECORDS : variable_get('uc_reports_table_size', 30);
$order_statuses = _uc_reports_order_statuses();
$rows = array();
$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;
}
$context = array(
'revision' => 'formatted-original',
'type' => 'amount',
);
$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_price($customer['total'], $context);
$average_revenue = uc_price($customer['average'], $context);
$rows[] = array(
array(
'data' => $customer_number,
),
array(
'data' => $name,
),
array(
'data' => l($customer_name, 'user/' . $customer['uid']),
),
array(
'data' => $orders,
),
array(
'data' => $products,
),
array(
'data' => $total_revenue,
),
array(
'data' => $average_revenue,
),
);
$csv_rows[] = array(
$customer_number,
$customer_order_name,
$customer_name,
$orders,
$products,
$customer['total'],
$customer['average'],
);
}
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']) . ' ' . (isset($_GET['nopage']) ? l(t('Show paged records'), 'admin/store/reports/customers') : l(t('Show all records'), 'admin/store/reports/customers', array(
'query' => 'nopage=1',
))) . '</div>';
return $output;
}