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