public static function get_customers($search = array(), $return_options = false)
{
$cache_key_args = func_get_args();
$cache_key = self::_customer_cache_key('all', $cache_key_args);
$cache_timeout = module_config::c('cache_objects', 60);
if ($cached_item = module_cache::get('customer', $cache_key)) {
return $cached_item;
}
// work out what customers this user can access?
$customer_access = self::get_customer_data_access();
// build up a custom search sql query based on the provided search fields
$sql = "SELECT ";
if (is_array($return_options) && isset($return_options['columns'])) {
$sql .= $return_options['columns'];
} else {
$sql .= " c.*, c.customer_id AS id, u.user_id, u.name, u.last_name, u.phone ";
$sql .= " , pu.user_id, pu.name AS primary_user_name, pu.last_name AS primary_user_last_name, pu.phone AS primary_user_phone, pu.email AS primary_user_email";
$sql .= " , pu.fax AS primary_user_fax, pu.mobile AS primary_user_mobile, pu.language AS primary_user_language";
$sql .= " , a.line_1, a.line_2, a.suburb, a.state, a.region, a.country, a.post_code ";
if (!count($search)) {
// we're pulling all available customers into an array.
//echo "all customers! ";
}
if (isset($_REQUEST['import_export_go']) && $_REQUEST['import_export_go'] == 'yes') {
// doing the export, pull in the staff names as well.
$sql .= ', GROUP_CONCAT( DISTINCT st.name, \' \', st.last_name SEPARATOR \', \' ) AS `customer_staff` ';
}
}
$sql .= " FROM `" . _DB_PREFIX . "customer` c ";
$where = "";
if (defined('_SYSTEM_ID')) {
$sql .= " AND c.system_id = '" . _SYSTEM_ID . "' ";
}
$group_order = '';
$sql .= ' LEFT JOIN `' . _DB_PREFIX . "user` u ON c.customer_id = u.customer_id";
//c.primary_user_id = u.user_id AND
$sql .= ' LEFT JOIN `' . _DB_PREFIX . "user` pu ON c.primary_user_id = pu.user_id";
if (isset($_REQUEST['import_export_go']) && $_REQUEST['import_export_go'] == 'yes') {
// doing the export, pull in the staff names as well.
$sql .= " LEFT JOIN `" . _DB_PREFIX . "customer_user_rel` cur ON (c.customer_id = cur.customer_id)";
$sql .= ' LEFT JOIN `' . _DB_PREFIX . "user` st ON cur.user_id = st.user_id";
}
$sql .= ' LEFT JOIN `' . _DB_PREFIX . "address` a ON c.customer_id = a.owner_id AND a.owner_table = 'customer' AND a.address_type = 'physical'";
if (isset($search['generic']) && trim($search['generic'])) {
$str = mysql_real_escape_string(trim($search['generic']));
// search the customer name, contact name, cusomter phone, contact phone, contact email.
//$where .= 'AND u.customer_id IS NOT NULL AND ( ';
$where .= " AND ( ";
$where .= "c.customer_name LIKE '%{$str}%' OR ";
// $where .= "c.phone LIKE '%$str%' OR "; // search company phone number too.
$where .= "u.name LIKE '%{$str}%' OR u.email LIKE '%{$str}%' OR ";
$where .= "u.last_name LIKE '%{$str}%' OR ";
$where .= "u.phone LIKE '%{$str}%' OR u.fax LIKE '%{$str}%' ";
$where .= ') ';
}
if (isset($search['customer_id']) && (int) $search['customer_id'] > 0) {
$where .= " AND c.customer_id = " . (int) $search['customer_id'];
}
if (isset($search['customer_type_id'])) {
$where .= " AND c.customer_type_id = " . (int) $search['customer_type_id'];
}
if (isset($search['address']) && trim($search['address'])) {
$str = mysql_real_escape_string(trim($search['address']));
// search all the customer site addresses.
$where .= " AND ( ";
$where .= " a.line_1 LIKE '%{$str}%' OR ";
$where .= " a.line_2 LIKE '%{$str}%' OR ";
$where .= " a.suburb LIKE '%{$str}%' OR ";
$where .= " a.state LIKE '%{$str}%' OR ";
$where .= " a.region LIKE '%{$str}%' OR ";
$where .= " a.country LIKE '%{$str}%' OR ";
$where .= " a.post_code LIKE '%{$str}%' ";
$where .= " ) ";
}
if (isset($search['state_id']) && trim($search['state_id'])) {
$str = (int) $search['state_id'];
// search all the customer site addresses.
$sql .= " LEFT JOIN `" . _DB_PREFIX . "address` a ON (a.owner_id = c.customer_id)";
// swap join around? meh.
$where .= " AND (a.state_id = '{$str}' AND a.owner_table = 'customer')";
}
if (isset($search['staff_id']) && trim($search['staff_id'])) {
$str = (int) $search['staff_id'];
// search all the customer site addresses.
$sql .= " LEFT JOIN `" . _DB_PREFIX . "customer_user_rel` cur ON (c.customer_id = cur.customer_id)";
$where .= " AND (cur.user_id = '{$str}')";
}
if (isset($search['company_id']) && trim($search['company_id'])) {
$str = (int) $search['company_id'];
// search all the customer site addresses.
$sql .= " LEFT JOIN `" . _DB_PREFIX . "company_customer` ccr ON (c.customer_id = ccr.customer_id)";
$where .= " AND (ccr.company_id = '{$str}')";
}
if (isset($search['group_id']) && trim($search['group_id'])) {
$str = (int) $search['group_id'];
$sql .= " LEFT JOIN `" . _DB_PREFIX . "group_member` gm ON (c.customer_id = gm.owner_id)";
$where .= " AND (gm.group_id = '{$str}' AND gm.owner_table = 'customer')";
}
if (isset($search['extra_fields']) && is_array($search['extra_fields']) && class_exists('module_extra', false)) {
$extra_fields = array();
foreach ($search['extra_fields'] as $key => $val) {
if (strlen(trim($val))) {
$extra_fields[$key] = trim($val);
}
}
if (count($extra_fields)) {
$sql .= " LEFT JOIN `" . _DB_PREFIX . "extra` ext ON (ext.owner_id = c.customer_id)";
//AND ext.owner_table = 'customer'
$where .= " AND (ext.owner_table = 'customer' AND ( ";
foreach ($extra_fields as $key => $val) {
$val = mysql_real_escape_string($val);
$key = mysql_real_escape_string($key);
$where .= "( ext.`extra` LIKE '%{$val}%' AND ext.`extra_key` = '{$key}') OR ";
}
$where = rtrim($where, ' OR');
$where .= ' ) )';
}
}
switch ($customer_access) {
case _CUSTOMER_ACCESS_ALL:
break;
case _CUSTOMER_ACCESS_ALL_COMPANY:
if (class_exists('module_company', false) && module_company::is_enabled()) {
$companys = module_company::get_companys_access_restrictions();
if (count($companys)) {
$sql .= " LEFT JOIN `" . _DB_PREFIX . "company_customer` cc ON c.customer_id = cc.customer_id";
$where .= " AND ( ";
if (module_config::c('customer_show_unassigned_company', 0)) {
$where .= 'cc.company_id IS NULL OR ';
}
$where .= "cc.company_id IN ( ";
$where .= mysql_real_escape_string(implode(', ', $companys));
$where .= " ) ) ";
}
}
break;
case _CUSTOMER_ACCESS_CONTACTS:
// we only want customers that are directly linked with the currently logged in user contact.
//$sql .= " LEFT JOIN `"._DB_PREFIX."user` u ON c.customer_id = u.customer_id "; // done above.
$sql .= " LEFT JOIN `" . _DB_PREFIX . "user_customer_rel` ucr ON c.customer_id = ucr.customer_id ";
$where .= " AND (";
$where .= "u.user_id = " . (int) module_security::get_loggedin_id();
$where .= " OR ( ucr.customer_id = c.customer_id AND ucr.user_id = " . (int) module_security::get_loggedin_id() . " AND ucr.primary = u.user_id )";
$where .= " OR ( ucr.customer_id = c.customer_id AND ucr.primary = " . (int) module_security::get_loggedin_id() . " AND ucr.user_id = u.user_id )";
$where .= ' )';
/*
// if(isset($_SESSION['_restrict_customer_id']) && (int)$_SESSION['_restrict_customer_id']> 0){
// this session variable is set upon login, it holds their customer id.
// todo - share a user account between multiple customers!
//$where .= " AND c.customer_id IN (SELECT customer_id FROM )";
if(isset($res['linked_parent_user_id']) && $res['linked_parent_user_id'] == $res['user_id']){
// this user is a primary user.
$_SESSION['_restrict_customer_id'] = array();
$_SESSION['_restrict_customer_id'][$res['customer_id']] = $res['customer_id'];
foreach(module_user::get_contact_customer_links($res['user_id']) as $linked){
$_SESSION['_restrict_customer_id'][$linked['customer_id']] = $linked['customer_id'];
}
}else{
// oldschool permissions.
$_SESSION['_restrict_customer_id'] = $res['customer_id'];
}*/
/*$valid_customer_ids = module_security::get_customer_restrictions();
if(count($valid_customer_ids)){
$where .= " AND ( ";
foreach($valid_customer_ids as $valid_customer_id){
$where .= " c.customer_id = '".(int)$valid_customer_id."' OR ";
}
$where = rtrim($where,'OR ');
$where .= " )";
}*/
// }
break;
case _CUSTOMER_ACCESS_TASKS:
// only customers who have linked jobs that I am assigned to.
$sql .= " LEFT JOIN `" . _DB_PREFIX . "job` j ON c.customer_id = j.customer_id ";
$sql .= " LEFT JOIN `" . _DB_PREFIX . "task` t ON j.job_id = t.job_id ";
$where .= " AND (j.user_id = " . (int) module_security::get_loggedin_id() . " OR t.user_id = " . (int) module_security::get_loggedin_id() . ")";
break;
case _CUSTOMER_ACCESS_STAFF:
// only customers who have linked staff entries
$sql .= " LEFT JOIN `" . _DB_PREFIX . "customer_user_rel` cur ON c.customer_id = cur.customer_id ";
$where .= " AND (cur.user_id = " . (int) module_security::get_loggedin_id() . ")";
break;
}
$group_order = ' GROUP BY c.customer_id ORDER BY c.customer_name ASC';
// stop when multiple company sites have same region
$sql = $sql . (strlen($where) > 0 ? ' WHERE 1' . $where : '') . $group_order;
if (!is_array($return_options) && $return_options === true || is_array($return_options) && isset($return_options['as_resource']) && $return_options['as_resource']) {
return query($sql);
}
$result = qa($sql);
/*if(!function_exists('sort_customers')){
function sort_customers($a,$b){
return strnatcasecmp($a['customer_name'],$b['customer_name']);
}
}
uasort($result,'sort_customers');*/
// we are filtering in the SQL code now..
//module_security::filter_data_set("customer",$result);
module_cache::put('customer', $cache_key, $result, $cache_timeout);
return $result;
//return get_multiple("customer",$search,"customer_id","fuzzy","name");
}
