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('status_id'); $sort_column_aliases = array('status' => 'status_id', 'type' => 'type_id', 'calculation' => 'calculation_id'); $order = $this->getColumnsFromAliases($order, $sort_column_aliases); if ($order == NULL) { $order = array('status_id' => 'asc', 'type_id' => 'asc', 'name' => 'asc'); $strict = FALSE; } else { //Always try to order by status first so INACTIVE employees go to the bottom. if (!isset($order['status_id'])) { $order = Misc::prependArray(array('status_id' => 'asc'), $order); } if (!isset($order['type_id'])) { $order = Misc::prependArray(array('type_id' => 'asc'), $order); } //Always sort by last name,first name after other columns if (!isset($order['name'])) { $order['name'] = 'asc'; } $strict = TRUE; } //Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10); //Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10); $uf = new UserFactory(); $ph = array('company_id' => $company_id); $query = ' select a.*, y.first_name as created_by_first_name, y.middle_name as created_by_middle_name, y.last_name as created_by_last_name, z.first_name as updated_by_first_name, z.middle_name as updated_by_middle_name, z.last_name as updated_by_last_name from ' . $this->getTable() . ' as a LEFT JOIN ' . $uf->getTable() . ' as y ON ( a.created_by = y.id AND y.deleted = 0 ) LEFT JOIN ' . $uf->getTable() . ' as z ON ( a.updated_by = z.id AND z.deleted = 0 ) where a.company_id = ? '; $query .= isset($filter_data['permission_children_ids']) ? $this->getWhereClauseSQL('a.created_by', $filter_data['permission_children_ids'], '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.id', $filter_data['exclude_id'], 'not_numeric_list', $ph) : NULL; if (isset($filter_data['include_user_id'])) { //Return rows that ONLY have this user assigned to them. $udf = new UserDeductionFactory(); $query .= ' AND a.id IN ( select company_deduction_id from ' . $udf->getTable() . ' as udf where udf.user_id in (' . $this->getListSQL($filter_data['include_user_id'], $ph) . ') AND udf.deleted = 0 )'; } if (isset($filter_data['exclude_user_id'])) { //Return rows that DO NOT have this user assigned to them. $udf = new UserDeductionFactory(); $query .= ' AND a.id NOT IN ( select company_deduction_id from ' . $udf->getTable() . ' as udf where udf.user_id in (' . $this->getListSQL($filter_data['exclude_user_id'], $ph) . ') AND udf.deleted = 0 )'; } 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('a.status_id', $filter_data['status_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['type_id']) ? $this->getWhereClauseSQL('a.type_id', $filter_data['type_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['calculation_id']) ? $this->getWhereClauseSQL('a.calculation_id', $filter_data['calculation_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['name']) ? $this->getWhereClauseSQL('a.name', $filter_data['name'], 'text', $ph) : NULL; $query .= isset($filter_data['country']) ? $this->getWhereClauseSQL('a.country', $filter_data['country'], 'upper_text_list', $ph) : NULL; $query .= isset($filter_data['province']) ? $this->getWhereClauseSQL('a.province', $filter_data['province'], 'upper_text_list', $ph) : NULL; $query .= isset($filter_data['created_by']) ? $this->getWhereClauseSQL(array('a.created_by', 'y.first_name', 'y.last_name'), $filter_data['created_by'], 'user_id_or_name', $ph) : NULL; $query .= isset($filter_data['updated_by']) ? $this->getWhereClauseSQL(array('a.updated_by', 'z.first_name', 'z.last_name'), $filter_data['updated_by'], 'user_id_or_name', $ph) : NULL; /* if ( isset($filter_data['permission_children_ids']) AND isset($filter_data['permission_children_ids'][0]) AND !in_array(-1, (array)$filter_data['permission_children_ids']) ) { $query .= ' AND a.created_by in ('. $this->getListSQL($filter_data['permission_children_ids'], $ph) .') '; } if ( isset($filter_data['id']) AND isset($filter_data['id'][0]) AND !in_array(-1, (array)$filter_data['id']) ) { $query .= ' AND a.id in ('. $this->getListSQL($filter_data['id'], $ph) .') '; } if ( isset($filter_data['exclude_id']) AND isset($filter_data['exclude_id'][0]) AND !in_array(-1, (array)$filter_data['exclude_id']) ) { $query .= ' AND a.id not in ('. $this->getListSQL($filter_data['exclude_id'], $ph) .') '; } if ( isset($filter_data['status_id']) AND isset($filter_data['status_id'][0]) AND !in_array(-1, (array)$filter_data['status_id']) ) { $query .= ' AND a.status_id in ('. $this->getListSQL($filter_data['status_id'], $ph) .') '; } if ( isset($filter_data['type_id']) AND isset($filter_data['type_id'][0]) AND !in_array(-1, (array)$filter_data['type_id']) ) { $query .= ' AND a.type_id in ('. $this->getListSQL($filter_data['type_id'], $ph) .') '; } if ( isset($filter_data['calculation_id']) AND isset($filter_data['calculation_id'][0]) AND !in_array(-1, (array)$filter_data['calculation_id']) ) { $query .= ' AND a.calculation_id in ('. $this->getListSQL($filter_data['calculation_id'], $ph) .') '; } if ( isset($filter_data['name']) AND trim($filter_data['name']) != '' ) { $ph[] = strtolower(trim($filter_data['name'])); $query .= ' AND lower(a.name) LIKE ?'; } if ( isset($filter_data['country']) AND isset($filter_data['country'][0]) AND !in_array(-1, (array)$filter_data['country']) ) { $query .= ' AND a.country in ('. $this->getListSQL($filter_data['country'], $ph) .') '; } if ( isset($filter_data['province']) AND isset($filter_data['province'][0]) AND !in_array( -1, (array)$filter_data['province']) AND !in_array( '00', (array)$filter_data['province']) ) { $query .= ' AND a.province in ('. $this->getListSQL($filter_data['province'], $ph) .') '; } if ( isset($filter_data['created_by']) AND isset($filter_data['created_by'][0]) AND !in_array(-1, (array)$filter_data['created_by']) ) { $query .= ' AND a.created_by in ('. $this->getListSQL($filter_data['created_by'], $ph) .') '; } if ( isset($filter_data['updated_by']) AND isset($filter_data['updated_by'][0]) AND !in_array(-1, (array)$filter_data['updated_by']) ) { $query .= ' AND a.updated_by in ('. $this->getListSQL($filter_data['updated_by'], $ph) .') '; } */ $query .= ' AND a.deleted = 0 '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict, $additional_order_fields); //Debug::Arr($ph, 'Query: '. $query, __FILE__, __LINE__, __METHOD__,10); $this->ExecuteSQL($query, $ph, $limit, $page); return $this; }