PHP SQLQBuilder::selectFromMultipleTable方法代码示例

 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
//.........这里部分代码省略.........

 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;
 }