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', 'frequency' => 'frequency_id'); $order = $this->getColumnsFromAliases($order, $sort_column_aliases); if ($order == NULL) { $order = array('status_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); } //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(); $pseaf = new PayStubEntryAccountFactory(); $ph = array('company_id' => $company_id); $query = ' select a.*, pseaf.name as pay_stub_entry_name, 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 ' . $pseaf->getTable() . ' as pseaf ON ( a.pay_stub_entry_name_id = pseaf.id AND pseaf.deleted = 0 ) 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['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; 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('a.type_id', $filter_data['type_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['frequency_id']) ? $this->getWhereClauseSQL('a.frequency_id', $filter_data['frequency_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['name']) ? $this->getWhereClauseSQL('a.name', $filter_data['name'], 'text', $ph) : NULL; $query .= isset($filter_data['description']) ? $this->getWhereClauseSQL('a.description', $filter_data['description'], 'text', $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['frequency_id']) AND isset($filter_data['frequency_id'][0]) AND !in_array(-1, (array)$filter_data['frequency_id']) ) { $query .= ' AND a.frequency_id in ('. $this->getListSQL($filter_data['frequency_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['description']) AND trim($filter_data['description']) != '' ) { $ph[] = strtolower(trim($filter_data['description'])); $query .= ' AND lower(a.description) LIKE ?'; } $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; */ $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; }
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('pay_stub_entry_name', 'user_status_id', 'last_name', 'first_name', 'default_branch', 'default_department', 'user_group', 'title'); $sort_column_aliases = array('user_status' => 'user_status_id', 'status' => 'status_id', 'type' => 'type_id'); $order = $this->getColumnsFromAliases($order, $sort_column_aliases); if ($order == NULL) { $order = array('effective_date' => 'desc', 'last_name' => 'asc'); $strict = FALSE; } else { //Always sort by effective_date,last name after other columns if (!isset($order['effective_date'])) { $order['effective_date'] = 'desc'; } if (!isset($order['last_name'])) { $order['last_name'] = 'asc'; } $strict = TRUE; } //Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10); //Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10); if (strncmp($this->db->databaseType, 'mysql', 5) == 0) { $to_timestamp_sql = 'from_unixtime(a.effective_date)'; } else { $to_timestamp_sql = 'to_timestamp(a.effective_date)'; } $uf = new UserFactory(); $bf = new BranchFactory(); $df = new DepartmentFactory(); $ugf = new UserGroupFactory(); $utf = new UserTitleFactory(); $pseaf = new PayStubEntryAccountFactory(); $ppf = new PayPeriodFactory(); $ppsf = new PayPeriodScheduleFactory(); $ppsuf = new PayPeriodScheduleUserFactory(); $ph = array('company_id' => $company_id); $query = ' select a.*, b.first_name as first_name, b.last_name as last_name, b.status_id as user_status_id, b.default_branch_id as default_branch_id, bf.name as default_branch, b.default_department_id as default_department_id, df.name as default_department, b.group_id as group_id, ugf.name as user_group, b.title_id as title_id, utf.name as title, pseaf.name as pay_stub_entry_name, 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 b ON ( a.user_id = b.id AND b.deleted = 0 ) LEFT JOIN ' . $bf->getTable() . ' as bf ON ( b.default_branch_id = bf.id AND bf.deleted = 0) LEFT JOIN ' . $df->getTable() . ' as df ON ( b.default_department_id = df.id AND df.deleted = 0) LEFT JOIN ' . $ugf->getTable() . ' as ugf ON ( b.group_id = ugf.id AND ugf.deleted = 0 ) LEFT JOIN ' . $utf->getTable() . ' as utf ON ( b.title_id = utf.id AND utf.deleted = 0 ) LEFT JOIN ' . $pseaf->getTable() . ' as pseaf ON ( a.pay_stub_entry_name_id = pseaf.id AND pseaf.deleted = 0 ) LEFT JOIN ' . $ppsuf->getTable() . ' as ppsuf ON ( a.user_id = ppsuf.user_id ) LEFT JOIN ' . $ppsf->getTable() . ' as ppsf ON ( ppsuf.pay_period_schedule_id = ppsf.id AND ppsf.deleted = 0 ) LEFT JOIN ' . $ppf->getTable() . ' as ppf ON ( ppsuf.pay_period_schedule_id = ppf.pay_period_schedule_id AND ' . $to_timestamp_sql . ' >= ppf.start_date AND ' . $to_timestamp_sql . ' <= ppf.end_date AND ppf.deleted = 0 ) 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 b.company_id = ? '; 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 b.id in (' . $this->getListSQL($filter_data['permission_children_ids'], $ph) . ') '; } if (isset($filter_data['user_id']) and isset($filter_data['user_id'][0]) and !in_array(-1, (array) $filter_data['user_id'])) { $query .= ' AND b.id in (' . $this->getListSQL($filter_data['user_id'], $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['user_id']) and isset($filter_data['user_id'][0]) and !in_array(-1, (array) $filter_data['user_id'])) { $query .= ' AND a.user_id in (' . $this->getListSQL($filter_data['user_id'], $ph) . ') '; } if (isset($filter_data['pay_period_id']) and isset($filter_data['pay_period_id'][0]) and !in_array(-1, (array) $filter_data['pay_period_id'])) { $query .= ' AND ppf.id in (' . $this->getListSQL($filter_data['pay_period_id'], $ph) . ') '; } if (isset($filter_data['group_id']) and isset($filter_data['group_id'][0]) and !in_array(-1, (array) $filter_data['group_id'])) { if (isset($filter_data['include_subgroups']) and (bool) $filter_data['include_subgroups'] == TRUE) { $uglf = new UserGroupListFactory(); $filter_data['group_id'] = $uglf->getByCompanyIdAndGroupIdAndSubGroupsArray($company_id, $filter_data['group_id'], TRUE); } $query .= ' AND b.group_id in (' . $this->getListSQL($filter_data['group_id'], $ph) . ') '; } if (isset($filter_data['default_branch_id']) and isset($filter_data['default_branch_id'][0]) and !in_array(-1, (array) $filter_data['default_branch_id'])) { $query .= ' AND b.default_branch_id in (' . $this->getListSQL($filter_data['default_branch_id'], $ph) . ') '; } if (isset($filter_data['default_department_id']) and isset($filter_data['default_department_id'][0]) and !in_array(-1, (array) $filter_data['default_department_id'])) { $query .= ' AND b.default_department_id in (' . $this->getListSQL($filter_data['default_department_id'], $ph) . ') '; } if (isset($filter_data['title_id']) and isset($filter_data['title_id'][0]) and !in_array(-1, (array) $filter_data['title_id'])) { $query .= ' AND b.title_id in (' . $this->getListSQL($filter_data['title_id'], $ph) . ') '; } if (isset($filter_data['recurring_ps_amendment_id']) and isset($filter_data['recurring_ps_amendment_id'][0]) and !in_array(-1, (array) $filter_data['recurring_ps_amendment_id'])) { $query .= ' AND a.recurring_ps_amendment_id in (' . $this->getListSQL($filter_data['recurring_ps_amendment_id'], $ph) . ') '; } if (isset($filter_data['start_date']) and trim($filter_data['start_date']) != '') { $ph[] = strtolower(trim($filter_data['start_date'])); $query .= ' AND a.effective_date >= ?'; } if (isset($filter_data['end_date']) and trim($filter_data['end_date']) != '') { $ph[] = strtolower(trim($filter_data['end_date'])); $query .= ' AND a.effective_date <= ?'; } if (isset($filter_data['effective_date']) and trim($filter_data['effective_date']) != '') { $ph[] = strtolower(trim($filter_data['effective_date'])); $query .= ' AND a.effective_date = ?'; } $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; //Need to account for employees being assigned to deleted pay period schedules. $query .= ' AND ( ppsuf.id IS NULL OR ppsf.id IS NOT NULL ) AND a.deleted = 0 '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict, $additional_order_fields); //Debug::Arr($query,'Query: ', __FILE__, __LINE__, __METHOD__,10); $this->ExecuteSQL($query, $ph, $limit, $page); return $this; }