public static function fetchOptions($table, $valueField, $labelField, $descField, $filterKey, $joinTable = null, $joinCondition = null, $compareMethod = self::COMPARE_LEFT, $caseSensitive = false) { $selecteFields[] = $valueField; $selecteFields[] = $labelField; $selecteFields[] = $descField; $selectTables[] = $table; $selectTables[] = $joinTable; $joinConditions[1] = $joinCondition; if (!$caseSensitive) { $labelField = "LOWER({$labelField})"; $filterKey = strtolower($filterKey); } switch ($compareMethod) { case self::COMPARE_LEFT: $selectConditions[] = "{$labelField} LIKE '{$filterKey}%'"; break; case self::COMPARE_RIGHT: $selectConditions[] = "{$labelField} LIKE '%{$filterKey}'"; break; case self::COMPARE_MID: $selectConditions[] = "{$labelField} LIKE '%{$filterKey}%'"; break; } $orderCondition = $labelField; $sqlBuilder = new SQLQBuilder(); $query = $sqlBuilder->selectFromMultipleTable($selecteFields, $selectTables, $joinConditions, $selectConditions, null, $orderCondition); $query = self::_formatQuery($query); $dbConnection = new DMLFunctions(); $result = $dbConnection->executeQuery($query); if (mysql_error()) { echo mysql_error() + "\n" + $query; die; } $result = $dbConnection->executeQuery($query); while ($row = mysql_fetch_array($result, MYSQL_NUM)) { $value = trim($row[0]); $label = trim($row[1]); $description = $row[2] == '' ? ' ' : trim($row[2]); echo "{$value},{$label},{$description}\n"; } }
/** * Leave summary of all employees */ public function fetchAllEmployeeLeaveSummary($employeeId, $year, $leaveTypeId = self::LEAVESUMMARY_CRITERIA_ALL, $searchBy = "employee", $sortField = null, $sortOrder = null, $hideDeleted = false, $pageNO = 1, $itemPerPage = 0, $leaveCount = FALSE) { $selectFields[0] = "a.`emp_number` as emp_number"; $selectFields[1] = "CONCAT(a.`emp_firstname`, ' ', a.`emp_lastname`) as employee_name"; $selectFields[2] = "c.`leave_type_name` as leave_type_name"; $selectFields[3] = "COALESCE(b.`no_of_days_allotted`, 0) as no_of_days_allotted"; $selectFields[4] = "COALESCE(b.`leave_taken`, 0) as leave_taken"; $sumOfApproved = "SUM( IF( d.`leave_status` = " . Leave::LEAVE_STATUS_LEAVE_APPROVED . ", ABS(COALESCE(d.`leave_length_days`, 0)), 0) )"; $selectFields[5] = "{$sumOfApproved} as leave_scheduled"; $selectFields[6] = "COALESCE(b.`no_of_days_allotted`, 0) + COALESCE(b.`leave_brought_forward`, 0) - COALESCE(b.`leave_taken`, 0) - {$sumOfApproved} as leave_available"; $selectFields[7] = "c.`leave_type_id` as leave_type_id"; $selectFields[8] = "c.`available_flag` as available_flag"; $arrTables[0] = '(`hs_hr_employee` a, `hs_hr_leavetype` c)'; $arrTables[1] = '`hs_hr_employee_leave_quota` b'; $arrTables[2] = '`hs_hr_leave` d'; $joinConditions[1] = "a.`emp_number` = b.`employee_id` AND c.`leave_type_id` = b.`leave_type_id` AND b.`year` = '{$year}'"; $joinConditions[2] = "d.`employee_id` = a.`emp_number` AND c.`leave_type_id` = d.`leave_type_id` AND (d.`leave_status` = " . Leave::LEAVE_STATUS_LEAVE_TAKEN . " OR d.`leave_status` = " . Leave::LEAVE_STATUS_LEAVE_APPROVED . ") AND d.`leave_date` BETWEEN DATE('" . $year . "-01-01') AND DATE('" . $year . "-12-31')"; $groupBy = "emp_number, employee_name, leave_type_id, leave_type_name, no_of_days_allotted, available_flag"; $selectConditions = null; if (($searchBy == "employee" || $searchBy == "both") && !empty($employeeId) && $employeeId != self::LEAVESUMMARY_CRITERIA_ALL) { $selectConditions[] = "a.`emp_number` = {$employeeId}"; } $selectConditions[] = "(a.`emp_status` IS NULL OR a.`emp_status` != 'EST000')"; $selectConditions[] = "c.`available_flag` = 1"; if ($sortField == null) { $sortField = 0; } if ($sortOrder == null) { $sortOrder = "ASC"; } /* Get the alias name (the last word) in the field definition */ $tmpFieldDefWords = explode(" ", $selectFields[$sortField]); $orderBy = array_pop($tmpFieldDefWords); $sqlBuilder = new SQLQBuilder(); $query = $sqlBuilder->selectFromMultipleTable($selectFields, $arrTables, $joinConditions, $selectConditions, null, $orderBy, $sortOrder, null, $groupBy); $objLeaveType = new LeaveType(); if ($leaveCount) { if (($searchBy == "leaveType" || $searchBy == "both") && !empty($leaveTypeId) && $leaveTypeId != self::LEAVESUMMARY_CRITERIA_ALL) { $query = "SELECT COUNT(*) AS leaveCount FROM ( {$query} ) subsel WHERE leave_type_id = '{$leaveTypeId}' AND available_flag = {$objLeaveType->availableStatusFlag}"; } else { $query = "SELECT COUNT(*) AS leaveCount FROM ( {$query} ) subsel WHERE available_flag = {$objLeaveType->availableStatusFlag}"; } } else { if (($searchBy == "leaveType" || $searchBy == "both") && !empty($leaveTypeId) && $leaveTypeId != self::LEAVESUMMARY_CRITERIA_ALL) { $query = "SELECT * FROM ( {$query} ) subsel WHERE leave_type_id = '{$leaveTypeId}' AND available_flag = {$objLeaveType->availableStatusFlag}"; } else { $query = "SELECT * FROM ( {$query} ) subsel WHERE available_flag = {$objLeaveType->availableStatusFlag}"; } } if (!$hideDeleted) { $query = $query . " OR leave_taken > 0 OR leave_scheduled > 0"; } /* Setting limit */ if (!$leaveCount) { $limit = '0, 50'; if ($pageNO > 0) { $pageNO--; $pageNO *= $itemPerPage; $limit = "{$pageNO}, {$itemPerPage}"; } $query = $query . " LIMIT " . $limit; } $dbConnection = new DMLFunctions(); $result = $dbConnection->executeQuery($query); $resultArr = null; while ($row = mysql_fetch_assoc($result)) { $resultArr[] = $row; } return $resultArr; }
/** * Queries the hs_hr_project_admin and hs_hr_project tables with the given conditions * * @param int $empNumber The employee number to filter by * @param int $projectId The project ID to filter by * @param bool $includeDeleted Whether deleted projects should be included * * @return resource Resource returned from the database. */ private function _getProjects($empNumber, $projectId = null, $includeDeleted = false) { $fields[0] = "b.`" . Projects::PROJECT_DB_FIELD_PROJECT_ID . "`"; $fields[1] = "b.`" . Projects::PROJECT_DB_FIELD_CUSTOMER_ID . "`"; $fields[2] = "b.`" . Projects::PROJECT_DB_FIELD_NAME . "`"; $fields[3] = "b.`" . Projects::PROJECT_DB_FIELD_DESCRIPTION . "`"; $fields[4] = "b.`" . Projects::PROJECT_DB_FIELD_DELETED . "`"; $tables[0] = "`" . self::TABLE_NAME . "` a "; $tables[1] = "`" . Projects::PROJECT_DB_TABLE . "` b "; $selectConditions[] = "a.`" . self::PROJECT_ADMIN_FIELD_EMP_NUMBER . "`= {$empNumber} "; if (!$includeDeleted) { $selectConditions[] = "b.`" . Projects::PROJECT_DB_FIELD_DELETED . "`= " . Projects::PROJECT_NOT_DELETED; } if (!empty($projectId)) { $selectConditions[] = "a.`" . self::PROJECT_ADMIN_FIELD_PROJECT_ID . "`= {$projectId} "; } $joinConditions[1] = "a.`" . self::PROJECT_ADMIN_FIELD_PROJECT_ID . "` = b.`" . Projects::PROJECT_DB_FIELD_PROJECT_ID . "`"; $sqlBuilder = new SQLQBuilder(); $sql = $sqlBuilder->selectFromMultipleTable($fields, $tables, $joinConditions, $selectConditions); $conn = new DMLFunctions(); $results = $conn->executeQuery($sql); return $results; }
function getListofEmployee($pageNO = 0, $schStr = '', $mode = -1, $sortField = 4, $sortOrder = 'ASC', $supervisorId = null) { //$tableName = 'HS_HR_EMPLOYEE'; $arrFieldList[0] = "a.`employee_id`"; $arrFieldList[1] = "a.`emp_firstname`"; $arrFieldList[2] = "a.`emp_lastname`"; $arrFieldList[3] = "a.`emp_middle_name`"; $arrFieldList[4] = "LPAD(a.`emp_number`, " . $this->employeeIdLength . ", 0)"; $arrFieldList[5] = "a.`work_station`"; $arrFieldList[6] = "c.`jobtit_name`"; $arrFieldList[7] = "CONCAT(a.`emp_firstname`, ' ', a.`emp_middle_name`, ' ', a.`emp_lastname`)"; $arrFieldList[8] = "d.`title`"; $arrFieldList[9] = "e.`estat_name`"; /* First show direct supervisors then indirect supervisors */ $arrFieldList[10] = "GROUP_CONCAT(g.`emp_firstname`, ' ', g.`emp_lastname` ORDER BY erep_reporting_mode ) AS Supervisor"; $sql_builder = new SQLQBuilder(); $arrTables[0] = "`hs_hr_employee` a"; $arrTables[1] = "`hs_hr_job_title` c"; $arrTables[2] = "`hs_hr_compstructtree` d"; $arrTables[3] = "`hs_hr_empstat` e"; $arrTables[4] = "`hs_hr_emp_reportto` f"; $arrTables[5] = "`hs_hr_employee` g"; $joinConditions[1] = "a.`job_title_code` = c.`jobtit_code`"; $joinConditions[2] = "a.`work_station` = d.`id`"; $joinConditions[3] = "a.`emp_status` = e.`estat_code`"; $joinConditions[4] = "a.`emp_number` = f.`erep_sub_emp_number`"; $joinConditions[5] = "f.`erep_sup_emp_number` = g.`emp_number`"; $selectConditions = null; $filteredSearch = mysql_real_escape_string($schStr); /* * Skip setting select conditions if no search string set, no search mode set * or if searching by supservisor (mode = 8) * * If searching by supervisor, the conditions are set in the outer SELECT statement. */ if ($mode != -1 && $mode != 8 && !empty($schStr)) { if ($mode == 7) { // Special handling for search by subdivision. // Get list of workstations with matches in the title or matches higher in the hierachy $subdivisionIds = $this->_getMatchingSubdivisionIds($schStr); // Create select condition for employees with workstation set to any of the // subdivisions if (isset($subdivisionIds) && !empty($subdivisionIds)) { $selectConditions[] = "a.`work_station` IN (" . $subdivisionIds . ") "; } else { // No subdivisions matches found. return ''; } } else { $selectConditions[] = "{$arrFieldList[$mode]} LIKE '" . $filteredSearch . "%'"; } } /** * Check whether searching for the employement status, * if not add this to not to show 'Terminated' employees */ if ($mode != 9) { $selectConditions[] = "(a.`emp_status` != 'EST000' OR a.`emp_status` IS NULL)"; } /* If supervisor ID is set, filter by that supervisor */ if (!empty($supervisorId)) { $selectConditions[] = "(f.`erep_sup_emp_number` = '{$supervisorId}')"; } $sysConst = new sysConf(); $limit = null; if ($pageNO > 0) { $pageNO--; $pageNO *= $sysConst->itemsPerPage; $limit = "{$pageNO}, {$sysConst->itemsPerPage}"; } /* We need to group to get the concatenated list of supervisor names */ $groupBy = "a.`emp_number` "; /* Don't order if searching by supervisor. The order by has * to be added to the outer SELECT. */ if ($sortField == 10) { $selectOrder = null; $selectOrderBy = null; } else { $selectOrder = $sortOrder; $selectOrderBy = $arrFieldList[$sortField]; } $sqlQString = $sql_builder->selectFromMultipleTable($arrFieldList, $arrTables, $joinConditions, $selectConditions, null, $selectOrderBy, $selectOrder, null, $groupBy); /* Add the outer SELECT */ $sqlQString = "SELECT * FROM ( {$sqlQString} ) AS subsel "; /* If searching by supervisor add the condition now */ if ($mode == 8 && !empty($schStr)) { $sqlQString .= " WHERE Supervisor LIKE '%{$filteredSearch}%' "; } /* If sorting by supervisor add the order by condition */ if ($sortField == 10) { $sqlQString .= " ORDER BY Supervisor {$sortOrder}"; } /* Add the search limit */ if (isset($limit)) { $sqlQString .= " LIMIT {$limit}"; } $dbConnection = new DMLFunctions(); $message2 = $dbConnection->executeQuery($sqlQString); //Calling the addData() function $i = 0; while ($line = mysql_fetch_array($message2, MYSQL_NUM)) { $arrayDispList[$i][0] = $line[0]; $arrayDispList[$i][1] = $line[1] . ' ' . $line[3] . ' ' . $line[2]; $arrayDispList[$i][2] = $line[4]; $arrayDispList[$i][3] = $line[5]; $arrayDispList[$i][4] = $line[6]; $arrayDispList[$i][6] = $line[9]; $arrayDispList[$i][5] = $line[10]; $i++; } if (isset($arrayDispList)) { return $arrayDispList; } else { $arrayDispList = ''; return $arrayDispList; } }
private static function _getEmployeeSearchList() { $employeeSearchList = array(); $selecteFields[] = 'CONCAT(em.`emp_firstname`, \' \', em.`emp_lastname`)'; $selecteFields[] = 'jt.`jobtit_name`'; $selecteFields[] = 'em.`emp_number`'; $selectTables[] = '`hs_hr_employee` AS em'; $selectTables[] = '`hs_hr_job_title` AS jt'; $joinConditions[1] = 'jt.`jobtit_code` = em.`job_title_code`'; $orderCondition = $selecteFields[1]; $sqlBuilder = new SQLQBuilder(); $query = $sqlBuilder->selectFromMultipleTable($selecteFields, $selectTables, $joinConditions, null, null, $orderCondition); $query = preg_replace("/\\\\'/", "'", $query); $dbConnection = new DMLFunctions(); $result = $dbConnection->executeQuery($query); $result = $dbConnection->executeQuery($query); while ($row = mysql_fetch_array($result, MYSQL_NUM)) { $row[0] = addslashes($row[0]); $employeeSearchList[] = $row; } return $employeeSearchList; }
public function timeReport($startDate, $endDate) { $selectFields[0] = "a.`" . self::TIME_EVENT_DB_FIELD_PROJECT_ID . "`"; $selectFields[1] = "a.`" . self::TIME_EVENT_DB_FIELD_ACTIVITY_ID . "`"; $selectFields[2] = "a.`" . self::TIME_EVENT_DB_FIELD_DURATION . "`"; $selectFields[3] = "a.`" . self::TIME_EVENT_DB_FIELD_START_TIME . "`"; $selectFields[4] = "a.`" . self::TIME_EVENT_DB_FIELD_END_TIME . "`"; $selectFields[5] = "a.`" . self::TIME_EVENT_DB_FIELD_REPORTED_DATE . "`"; $tables[0] = "`" . self::TIME_EVENT_DB_TABLE_TIME_EVENT . "` a "; $tables[1] = "`" . Timesheet::TIMESHEET_DB_TABLE_TIMESHEET . "` b "; $joinConditions[1] = "a.`" . self::TIME_EVENT_DB_FIELD_TIMESHEET_ID . "` = b.`" . Timesheet::TIMESHEET_DB_FIELD_TIMESHEET_ID . "`"; $selectConditions[0] = "((a.`" . self::TIME_EVENT_DB_FIELD_START_TIME . "` >= '{$startDate} 00:00:00' AND " . "a.`" . self::TIME_EVENT_DB_FIELD_END_TIME . "` <= '{$endDate} 23:59:59')" . " OR (a.`" . self::TIME_EVENT_DB_FIELD_REPORTED_DATE . "` >= '{$startDate} 00:00:00' AND " . "a.`" . self::TIME_EVENT_DB_FIELD_REPORTED_DATE . "` <= '{$endDate} 23:59:59}'))"; $selectConditions[1] = "b.`" . Timesheet::TIMESHEET_DB_FIELD_STATUS . "` =20"; if ($this->getProjectId() != null) { $selectConditions[] = "a.`" . self::TIME_EVENT_DB_FIELD_PROJECT_ID . "` = {$this->getProjectId()}"; } if ($this->getActivityId() != null) { $selectConditions[] = "a.`" . self::TIME_EVENT_DB_FIELD_ACTIVITY_ID . "` = {$this->getActivityId()}"; } if ($this->getEmployeeId() != null) { $selectConditions[] = "a.`" . self::TIME_EVENT_DB_FIELD_EMPLOYEE_ID . "` = {$this->getEmployeeId()}"; } $sqlBuilder = new SQLQBuilder(); $query = $sqlBuilder->selectFromMultipleTable($selectFields, $tables, $joinConditions, $selectConditions); $dbConnection = new DMLFunctions(); $result = $dbConnection->executeQuery($query); $arrData = null; $searchStartDateTimestap = strtotime(date("Y-m-d", strtotime($startDate))); $searchEndDateTimestap = strtotime(date("Y-m-d", strtotime($endDate))); while ($row = mysql_fetch_assoc($result)) { $add = FALSE; $eventStartTime = strtotime(date("Y-m-d", strtotime($row[self::TIME_EVENT_DB_FIELD_START_TIME]))); $eventEndTime = strtotime(date("Y-m-d", strtotime($row[self::TIME_EVENT_DB_FIELD_END_TIME]))); $eventReportedDate = strtotime(date("Y-m-d", strtotime($row[self::TIME_EVENT_DB_FIELD_REPORTED_DATE]))); if ($eventStartTime < 0 && $eventReportedDate) { $add = TRUE; } elseif ($eventStartTime > 0 && $eventReportedDate) { if ($eventStartTime >= $searchStartDateTimestap && $eventEndTime <= $searchEndDateTimestap) { $add = TRUE; } } elseif ($eventReportedDate >= $searchStartDateTimestap && $eventReportedDate <= $searchEndDateTimestap) { $add = TRUE; } if ($add) { if (isset($arrData[$row[self::TIME_EVENT_DB_FIELD_PROJECT_ID]][$row[self::TIME_EVENT_DB_FIELD_ACTIVITY_ID]])) { $arrData[$row[self::TIME_EVENT_DB_FIELD_PROJECT_ID]][$row[self::TIME_EVENT_DB_FIELD_ACTIVITY_ID]] = $arrData[$row[self::TIME_EVENT_DB_FIELD_PROJECT_ID]][$row[self::TIME_EVENT_DB_FIELD_ACTIVITY_ID]] + $row[self::TIME_EVENT_DB_FIELD_DURATION]; } else { $arrData[$row[self::TIME_EVENT_DB_FIELD_PROJECT_ID]][$row[self::TIME_EVENT_DB_FIELD_ACTIVITY_ID]] = $row[self::TIME_EVENT_DB_FIELD_DURATION]; } } } return $arrData; }
/** * Fetch all projects with paging */ public function getListOfProjectsStr($pageNO, $schStr, $schField, $sortField = 0, $sortOrder = 'ASC') { $arrFieldList[0] = "a.`" . self::PROJECT_DB_FIELD_PROJECT_ID . "`"; $arrFieldList[1] = "a.`" . self::PROJECT_DB_FIELD_NAME . "`"; $arrFieldList[2] = "b.`" . Customer::CUSTOMER_DB_FIELDS_NAME . "`"; $arrFieldList[3] = "a.`" . self::PROJECT_DB_FIELD_DESCRIPTION . "`"; $arrFieldList[4] = "a.`" . self::PROJECT_DB_FIELD_DELETED . "`"; $tableNames[0] = "`" . Customer::TABLE_NAME . "` b "; $tableNames[1] = "`" . self::PROJECT_DB_TABLE . "` a "; $joinConditions[1] = "b.`" . Customer::CUSTOMER_DB_FIELDS_ID . "` = a.`" . self::PROJECT_DB_FIELD_CUSTOMER_ID . "`"; $sql_builder = new SQLQBuilder(); $arrSelectConditions[0] = "a.`" . self::PROJECT_DB_FIELD_DELETED . "`= " . self::PROJECT_NOT_DELETED . ""; $arrSelectConditions[1] = "a.`" . self::PROJECT_DB_FIELD_PROJECT_ID . "` != 0"; if ($schField != -1) { $arrSelectConditions[2] = "" . $arrFieldList[$schField] . " LIKE '%" . $schStr . "%'"; } $limitStr = null; if ($pageNO > 0) { $sysConfObj = new sysConf(); $page = ($pageNO - 1) * $sysConfObj->itemsPerPage; $limit = $sysConfObj->itemsPerPage; $limitStr = "{$page},{$limit}"; } $sqlQString = $sql_builder->selectFromMultipleTable($arrFieldList, $tableNames, $joinConditions, $arrSelectConditions, null, $arrFieldList[$sortField], $sortOrder, $limitStr); $dbConnection = new DMLFunctions(); $message2 = $dbConnection->executeQuery($sqlQString); //Calling the addData() function $arrDispArr = null; $i = 0; while ($row = mysql_fetch_row($message2)) { $arrDispArr[$i] = $row; $i++; } return $arrDispArr; }
/** * Get a list of jobs applications with the given conditions. * * @param array $selectCondition Array of select conditions to use. * @param String $filterForManagerId Filter by the given manager * @return array Array of JobApplication objects. Returns an empty (length zero) array if none found. */ private static function _getList($selectCondition = null, $filterForManagerId = null) { $fields[0] = 'a.' . self::DB_FIELD_ID; $fields[1] = 'a.' . self::DB_FIELD_VACANCY_ID; $fields[2] = 'a.' . self::DB_FIELD_FIRSTNAME; $fields[3] = 'a.' . self::DB_FIELD_MIDDLENAME; $fields[4] = 'a.' . self::DB_FIELD_LASTNAME; $fields[5] = 'a.' . self::DB_FIELD_STREET1; $fields[6] = 'a.' . self::DB_FIELD_STREET2; $fields[7] = 'a.' . self::DB_FIELD_CITY; $fields[8] = 'a.' . self::DB_FIELD_COUNTRY_CODE; $fields[9] = 'a.' . self::DB_FIELD_PROVINCE; $fields[10] = 'a.' . self::DB_FIELD_ZIP; $fields[11] = 'a.' . self::DB_FIELD_PHONE; $fields[12] = 'a.' . self::DB_FIELD_MOBILE; $fields[13] = 'a.' . self::DB_FIELD_EMAIL; $fields[14] = 'a.' . self::DB_FIELD_QUALIFICATIONS; $fields[15] = 'a.' . self::DB_FIELD_STATUS; $fields[16] = 'a.' . self::DB_FIELD_APPLIED_DATETIME; $fields[17] = 'a.' . self::DB_FIELD_EMP_NUMBER; $fields[18] = 'c.jobtit_name AS ' . self::JOB_TITLE_NAME; $fields[19] = "CONCAT(d.`emp_firstname`, ' ', d.`emp_lastname`) AS " . self::HIRING_MANAGER_NAME; $tables[0] = self::TABLE_NAME . ' a'; $tables[1] = JobVacancy::TABLE_NAME . ' b'; $tables[2] = 'hs_hr_job_title c'; $tables[3] = 'hs_hr_employee d'; $joinConditions[1] = 'a.' . self::DB_FIELD_VACANCY_ID . ' = b.' . JobVacancy::DB_FIELD_VACANCY_ID; $joinConditions[2] = 'b.jobtit_code = c.jobtit_code'; $joinConditions[3] = 'b.' . JobVacancy::DB_FIELD_MANAGER_ID . ' = d.emp_number'; $groupBy = null; if (!empty($filterForManagerId)) { $tables[4] = JobApplicationEvent::TABLE_NAME . ' e'; $joinConditions[4] = 'a.' . self::DB_FIELD_ID . ' = e.' . JobApplicationEvent::DB_FIELD_APPLICATION_ID; $selectCondition[] = '((b.' . JobVacancy::DB_FIELD_MANAGER_ID . ' = ' . $filterForManagerId . ') OR ' . '(e.' . JobApplicationEvent::DB_FIELD_OWNER . ' = ' . $filterForManagerId . '))'; $groupBy = 'a.' . self::DB_FIELD_ID; } $sqlBuilder = new SQLQBuilder(); $sql = $sqlBuilder->selectFromMultipleTable($fields, $tables, $joinConditions, $selectCondition, null, null, null, null, $groupBy); $actList = array(); $conn = new DMLFunctions(); $result = $conn->executeQuery($sql); while ($result && ($row = mysql_fetch_assoc($result))) { $actList[] = self::_createFromRow($row); } return $actList; }
/** * Return list of employees who are assigned to this workshift * @return array Array of employees */ public function getAssignedEmployees() { if (!CommonFunctions::isValidId($this->workshiftId)) { throw new WorkshiftException("No valid workshift id defined", WorkshiftException::INVALID_ID); } $fields[0] = "b.`" . EmpInfo::EMPLOYEE_FIELD_EMP_NUMBER . "`"; $fields[1] = "b.`" . EmpInfo::EMPLOYEE_FIELD_EMP_ID . "`"; $fields[2] = "b.`" . EmpInfo::EMPLOYEE_FIELD_FIRST_NAME . "`"; $fields[3] = "b.`" . EmpInfo::EMPLOYEE_FIELD_MIDDLE_NAME . "`"; $fields[4] = "b.`" . EmpInfo::EMPLOYEE_FIELD_LAST_NAME . "`"; $tables[0] = "`" . self::EMPLOYEE_WORKSHIFT_TABLE . "` a "; $tables[1] = "`" . EmpInfo::EMPLOYEE_TABLE_NAME . "` b "; $joinConditions[1] = "a." . self::DB_FIELD_EMP_NUMBER . " = b." . EmpInfo::EMPLOYEE_FIELD_EMP_NUMBER; $selectConditions[0] = " a." . self::DB_FIELD_WORKSHIFT_ID . " = " . $this->workshiftId; $sqlBuilder = new SQLQBuilder(); $sql = $sqlBuilder->selectFromMultipleTable($fields, $tables, $joinConditions, $selectConditions); $conn = new DMLFunctions(); $results = $conn->executeQuery($sql); if ($results === false) { throw new WorkshiftException("Error in db query:" . $sql, WorkshiftException::ERROR_IN_DB_QUERY); } return self::_getEmployeesFromResults($results); }
/** * This retrives alreadyt taken leaves. */ public function retriveLeaveTaken() { $sqlBuilder = new SQLQBuilder(); $arrFields[0] = 'a.`leave_id`'; $arrFields[1] = 'a.`leave_date`'; $arrFields[2] = 'b.`emp_firstname`'; $arrFields[3] = 'b.`emp_lastname`'; $arrFields[4] = 'a.`leave_length_hours`'; $arrFields[5] = 'a.`leave_comments`'; $arrFields[6] = 'a.`leave_type_id`'; $arrFields[7] = 'a.`employee_id`'; $arrTables[0] = "`hs_hr_leave` a"; $arrTables[1] = "`hs_hr_employee` b"; $joinConditions[1] = "a.`employee_id` = b.`employee_id`"; $selectConditions[1] = "a.`leave_status` = '3'"; $query = $sqlBuilder->selectFromMultipleTable($arrFields, $arrTables, $joinConditions, $selectConditions); $dbConnection = new DMLFunctions(); $result = $dbConnection->executeQuery($query); $leaveArr = $this->_buildObjArr($result, true); return $leaveArr; }
/** * Get a list of Employee locations with the given conditions. * * @param array $selectCondition Array of select conditions to use. * @return array Array of EmpLocation objects. Returns an empty (length zero) array if none found. */ private static function _getList($selectCondition = null) { $fields[0] = "a." . self::DB_FIELD_EMP_NUMBER; $fields[1] = "a." . self::DB_FIELD_LOC_CODE; $fields[2] = "b.loc_name AS " . self::FIELD_LOCATION_NAME; $tables[0] = self::TABLE_NAME . ' a'; $tables[1] = 'hs_hr_location b'; $joinConditions[1] = 'a.' . self::DB_FIELD_LOC_CODE . ' = b.loc_code'; $sqlBuilder = new SQLQBuilder(); $sql = $sqlBuilder->selectFromMultipleTable($fields, $tables, $joinConditions, $selectCondition); $actList = array(); $conn = new DMLFunctions(); $result = $conn->executeQuery($sql); while ($result && ($row = mysql_fetch_assoc($result))) { $actList[] = self::_createFromRow($row); } return $actList; }
/** * Get list of subordinates for supervisors with a name that matches the search term. * The supervisors first, middle and lastnames are searched and the subordinates of * matching supervisors are returned. * * @param searchTerm The search term to match with the supervisor names * @return Array of matching employee ID's */ public function getSubordinatesOfSupervisorWithName($searchTerm) { $sqlBuilder = new SQLQBuilder(); $arrFields[0] = 'DISTINCT r.`erep_sub_emp_number`'; $arrTables[0] = '`hs_hr_emp_reportto` r'; $arrTables[1] = '`hs_hr_employee` e'; $joinConditions[1] = "r.`erep_sup_emp_number` = e.`emp_number`"; $filteredSearchTerm = mysql_real_escape_string($searchTerm); $selectConditions[1] = "e.`emp_firstname` LIKE '" . $filteredSearchTerm . "%' OR " . "e.`emp_lastname` LIKE '" . $filteredSearchTerm . "%' OR " . "e.`emp_middle_name` LIKE '" . $filteredSearchTerm . "%'"; $query = $sqlBuilder->selectFromMultipleTable($arrFields, $arrTables, $joinConditions, $selectConditions, null, null, null); $dbConnection = new DMLFunctions(); $result = $dbConnection->executeQuery($query); $subordinateIds = null; $rowNum = 0; while ($line = mysql_fetch_array($result, MYSQL_NUM)) { $subordinateIds[$rowNum] = $line[0]; $rowNum++; } return $subordinateIds; }
/** * Get a list of jobs vacancies with the given conditions. * * @param array $selectCondition Array of select conditions to use. * @return array Array of JobVacancy objects. Returns an empty (length zero) array if none found. */ private static function _getList($selectCondition = null) { $fields[0] = "a. " . self::DB_FIELD_VACANCY_ID; $fields[1] = "a. " . self::DB_FIELD_JOBTITLE_CODE; $fields[2] = "c.jobtit_name AS " . self::FIELD_JOB_TITLE_NAME; $fields[3] = "a. " . self::DB_FIELD_MANAGER_ID; $fields[4] = "CONCAT(b.`emp_firstname`, ' ', b.`emp_lastname`) AS " . self::FIELD_MANAGER_NAME; $fields[5] = "a. " . self::DB_FIELD_ACTIVE; $fields[6] = "a. " . self::DB_FIELD_DESCRIPTION; $tables[0] = self::TABLE_NAME . ' a'; $tables[1] = 'hs_hr_employee b'; $tables[2] = 'hs_hr_job_title c'; $joinConditions[1] = 'a.' . self::DB_FIELD_MANAGER_ID . ' = b.emp_number'; $joinConditions[2] = 'a.jobtit_code = c.jobtit_code'; $sqlBuilder = new SQLQBuilder(); $sql = $sqlBuilder->selectFromMultipleTable($fields, $tables, $joinConditions, $selectCondition); $actList = array(); $conn = new DMLFunctions(); $result = $conn->executeQuery($sql); while ($result && ($row = mysql_fetch_assoc($result))) { $actList[] = self::_createFromRow($row); } return $actList; }
/** * Retrieves Leave Quota Details of all Leave Quota * available to the employee. * * @param String $employeeId * @return LeaveQuota[][] * @access public */ public function fetchLeaveQuota($employeeId) { $sqlBuilder = new SQLQBuilder(); $arrFields[0] = "a.`" . self::LEAVEQUOTA_DB_FIELD_YEAR . "`"; $arrFields[1] = "a.`" . self::LEAVEQUOTA_DB_FIELD_LEAVE_TYPE_ID . "`"; $arrFields[2] = "b.`leave_type_name`"; $arrFields[3] = "a.`" . self::LEAVEQUOTA_DB_FIELD_NO_OF_DAYS_ALLOTED . "`"; $arrFields[4] = "a.`" . self::LEAVEQUOTA_DB_FIELD_EMPLOYEE_ID . "`"; $arrTables[0] = "`" . self::LEAVEQUOTA_DB_TABLE_EMPLOYEE_LEAVE_QUOTA . "` a"; $arrTables[1] = "`hs_hr_leavetype` b"; $joinConditions[1] = "a.`" . self::LEAVEQUOTA_DB_FIELD_LEAVE_TYPE_ID . "` = b.`leave_type_id`"; $selectConditions = null; $selectOrderBy = $arrFields[4]; if ($this->getYear() != null) { $selectConditions[] = "a.`" . self::LEAVEQUOTA_DB_FIELD_YEAR . "` = '{$this->getYear()}'"; } if ($employeeId != 0) { $selectConditions[] = "a.`" . self::LEAVEQUOTA_DB_FIELD_EMPLOYEE_ID . "` = '" . $employeeId . "'"; $selectOrderBy = $arrFields[1]; } $selectConditions[] = "a.`" . self::LEAVEQUOTA_DB_FIELD_NO_OF_DAYS_ALLOTED . "` > 0"; $joinTypes[1] = "LEFT"; $selectOrderBy = "{$arrFields['0']}, {$selectOrderBy}"; $selectOrder = "ASC"; $query = $sqlBuilder->selectFromMultipleTable($arrFields, $arrTables, $joinConditions, $selectConditions, $joinTypes, $selectOrderBy, $selectOrder); $dbConnection = new DMLFunctions(); $result = $dbConnection->executeQuery($query); $leaveTypeArr = $this->_buildObjArr($result); return $leaveTypeArr; }
/** * Retrieves leave for the given employee between the given two dates. * If the given dates are the same and startTime and endTime are given * looks for leave on that date between the given times. * * */ public function retrieveDuplicateLeave($employeeNum, $fromDate, $toDate) { $sqlBuilder = new SQLQBuilder(); $arrFields[0] = 'a.`leave_id`'; $arrFields[1] = 'a.`leave_date`'; $arrFields[2] = 'a.`leave_length_hours`'; $arrFields[3] = 'a.`leave_length_days`'; $arrFields[4] = 'a.`leave_status`'; $arrFields[5] = 'a.`leave_comments`'; $arrFields[6] = 'a.`leave_request_id`'; $arrFields[7] = 'a.`leave_type_id`'; $arrFields[8] = 'a.`employee_id`'; $arrFields[9] = 'a.`start_time`'; $arrFields[10] = 'a.`end_time`'; $arrFields[11] = 'b.`leave_type_name`'; $arrFields[12] = 'c.`emp_firstname`'; $arrFields[13] = 'c.`emp_lastname`'; $arrTable = "`hs_hr_leave`"; $arrTables[0] = "`hs_hr_leave` a"; $arrTables[1] = "`hs_hr_leavetype` b"; $arrTables[2] = "`hs_hr_employee` c"; $joinConditions[1] = "a.`leave_type_id` = b.`leave_type_id`"; $joinConditions[2] = "a.`employee_id` = c.`emp_number`"; $dbConnection = new DMLFunctions(); $selectConditions[1] = "a.`employee_id` = '" . mysql_real_escape_string($employeeNum) . "'"; $selectConditions[2] = "a.`leave_date` >='" . mysql_real_escape_string($fromDate) . "'"; $selectConditions[3] = "a.`leave_date` <='" . mysql_real_escape_string($toDate) . "'"; $selectConditions[4] = "a.`leave_status` <>'" . self::LEAVE_STATUS_LEAVE_CANCELLED . "'"; $query = $sqlBuilder->selectFromMultipleTable($arrFields, $arrTables, $joinConditions, $selectConditions); $result = $dbConnection->executeQuery($query); $leaveArr = $this->_buildObjArr($result, true); return $leaveArr; }
/** * Get a list of jobs applications with the given conditions. * * @param array $selectCondition Array of select conditions to use. * @return array Array of JobApplicationEvent objects. Returns an empty (length zero) array if none found. */ private static function _getList($selectCondition = null) { $fields[0] = 'a.' . self::DB_FIELD_ID; $fields[1] = 'a.' . self::DB_FIELD_APPLICATION_ID; $fields[2] = 'a.' . self::DB_FIELD_CREATED_TIME; $fields[3] = 'a.' . self::DB_FIELD_CREATED_BY; $fields[4] = 'a.' . self::DB_FIELD_OWNER; $fields[5] = 'a.' . self::DB_FIELD_EVENT_TIME; $fields[6] = 'a.' . self::DB_FIELD_EVENT_TYPE; $fields[7] = 'a.' . self::DB_FIELD_STATUS; $fields[8] = 'a.' . self::DB_FIELD_NOTES; $fields[9] = "CONCAT(b.`emp_firstname`, ' ', b.`emp_lastname`) AS " . self::OWNER_NAME; $tables[0] = self::TABLE_NAME . ' a'; $tables[1] = 'hs_hr_employee b'; $joinConditions[1] = 'a.' . self::DB_FIELD_OWNER . ' = b.emp_number'; $orderBy = self::DB_FIELD_CREATED_TIME; $order = 'ASC'; $sqlBuilder = new SQLQBuilder(); $sql = $sqlBuilder->selectFromMultipleTable($fields, $tables, $joinConditions, $selectCondition, null, $orderBy, $order); $actList = array(); $conn = new DMLFunctions(); $result = $conn->executeQuery($sql); while ($result && ($row = mysql_fetch_assoc($result))) { $actList[] = self::_createFromRow($row); } return $actList; }