function getAPISearchByCompanyIdAndArrayCriteria($company_id, $filter_data, $limit = NULL, $page = NULL, $where = NULL, $order = NULL) { if ($company_id == '') { return FALSE; } if (!is_array($order)) { //Use Filter Data ordering if its set. if (isset($filter_data['sort_column']) and $filter_data['sort_order']) { $order = array(Misc::trimSortPrefix($filter_data['sort_column']) => $filter_data['sort_order']); } } $additional_order_fields = array('accrual_policy', 'accrual_policy_type_id', 'first_name', 'last_name', 'name', 'default_branch', 'default_department'); $sort_column_aliases = array('accrual_policy_type' => 'accrual_policy_type_id', 'group' => 'e.name'); $order = $this->getColumnsFromAliases($order, $sort_column_aliases); if ($order == NULL) { $order = array('last_name' => 'asc', 'accrual_policy_id' => 'asc'); $strict = FALSE; } else { //Always sort by last name,first name after other columns /* if ( !isset($order['effective_date']) ) { $order['effective_date'] = 'desc'; } */ $strict = TRUE; } //Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10); //Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10); $uf = new UserFactory(); $bf = new BranchFactory(); $df = new DepartmentFactory(); $ugf = new UserGroupFactory(); $utf = new UserTitleFactory(); $apf = new AccrualPolicyFactory(); $af = new AccrualFactory(); $ph = array('company_id' => $company_id); $query = ' select a.*, ab.name as accrual_policy, ab.type_id as accrual_policy_type_id, b.first_name as first_name, b.last_name as last_name, b.country as country, b.province as province, c.id as default_branch_id, c.name as default_branch, d.id as default_department_id, d.name as default_department, e.id as group_id, e.name as "group", f.id as title_id, f.name as title from ' . $this->getTable() . ' as a LEFT JOIN ' . $apf->getTable() . ' as ab ON ( a.accrual_policy_id = ab.id AND ab.deleted = 0 ) LEFT JOIN ' . $uf->getTable() . ' as b ON ( a.user_id = b.id AND b.deleted = 0 ) LEFT JOIN ' . $bf->getTable() . ' as c ON ( b.default_branch_id = c.id AND c.deleted = 0) LEFT JOIN ' . $df->getTable() . ' as d ON ( b.default_department_id = d.id AND d.deleted = 0) LEFT JOIN ' . $ugf->getTable() . ' as e ON ( b.group_id = e.id AND e.deleted = 0 ) LEFT JOIN ' . $utf->getTable() . ' as f ON ( b.title_id = f.id AND f.deleted = 0 ) where b.company_id = ? AND EXISTS ( select 1 from ' . $af->getTable() . ' as af WHERE af.accrual_policy_id = a.accrual_policy_id AND a.user_id = af.user_id AND af.deleted = 0 ) '; $query .= isset($filter_data['permission_children_ids']) ? $this->getWhereClauseSQL('a.user_id', $filter_data['permission_children_ids'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['user_id']) ? $this->getWhereClauseSQL('a.user_id', $filter_data['user_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['id']) ? $this->getWhereClauseSQL('a.id', $filter_data['id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['exclude_id']) ? $this->getWhereClauseSQL('a.user_id', $filter_data['exclude_id'], 'not_numeric_list', $ph) : NULL; if (isset($filter_data['type']) and trim($filter_data['type']) != '' and !isset($filter_data['type_id'])) { $filter_data['type_id'] = Option::getByFuzzyValue($filter_data['type'], $this->getOptions('type')); } $query .= isset($filter_data['type_id']) ? $this->getWhereClauseSQL('ab.type_id', $filter_data['type_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['accrual_policy_id']) ? $this->getWhereClauseSQL('a.accrual_policy_id', $filter_data['accrual_policy_id'], 'numeric_list', $ph) : NULL; if (isset($filter_data['status']) and trim($filter_data['status']) != '' and !isset($filter_data['status_id'])) { $filter_data['status_id'] = Option::getByFuzzyValue($filter_data['status'], $this->getOptions('status')); } $query .= isset($filter_data['status_id']) ? $this->getWhereClauseSQL('b.status_id', $filter_data['status_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['group_id']) ? $this->getWhereClauseSQL('b.group_id', $filter_data['group_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['group']) ? $this->getWhereClauseSQL('e.name', $filter_data['group'], 'text', $ph) : NULL; $query .= isset($filter_data['default_branch_id']) ? $this->getWhereClauseSQL('b.default_branch_id', $filter_data['default_branch_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['default_branch']) ? $this->getWhereClauseSQL('c.name', $filter_data['default_branch'], 'text', $ph) : NULL; $query .= isset($filter_data['default_department_id']) ? $this->getWhereClauseSQL('b.default_department_id', $filter_data['default_department_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['default_department']) ? $this->getWhereClauseSQL('d.name', $filter_data['default_department'], 'text', $ph) : NULL; $query .= isset($filter_data['title_id']) ? $this->getWhereClauseSQL('a.title_id', $filter_data['title_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['title']) ? $this->getWhereClauseSQL('e.name', $filter_data['title'], 'text', $ph) : NULL; $query .= isset($filter_data['first_name']) ? $this->getWhereClauseSQL('b.first_name', $filter_data['first_name'], 'text_metaphone', $ph) : NULL; $query .= isset($filter_data['last_name']) ? $this->getWhereClauseSQL('b.last_name', $filter_data['last_name'], 'text_metaphone', $ph) : NULL; $query .= isset($filter_data['country']) ? $this->getWhereClauseSQL('b.country', $filter_data['country'], 'upper_text_list', $ph) : NULL; $query .= isset($filter_data['province']) ? $this->getWhereClauseSQL('b.province', $filter_data['province'], 'upper_text_list', $ph) : NULL; $query .= ' AND a.deleted = 0 '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict, $additional_order_fields); $this->ExecuteSQL($query, $ph, $limit, $page); return $this; }