You are here

function uc_reports_customers in Ubercart 5

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

File

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

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 = !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']) . '&nbsp;&nbsp;&nbsp;' . (!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;
}