function filterGenInfo()
{
$tableName = 'HS_HR_GENINFO';
$arrFieldList[0] = 'CODE';
$arrFieldList[1] = 'GENINFO_KEYS';
$arrFieldList[2] = 'GENINFO_VALUES';
$sql_builder = new SQLQBuilder();
$sql_builder->table_name = $tableName;
$sql_builder->flg_select = 'true';
$sql_builder->arr_select = $arrFieldList;
$sqlQString = $sql_builder->selectOneRecordFiltered('001');
//echo $sqlQString;
$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[1];
$arrayDispList[$i][1] = $line[2];
$i++;
}
if (isset($arrayDispList)) {
return $arrayDispList;
} else {
$arrayDispList = '';
return $arrayDispList;
}
}
/**
* Outputs the 'value' corresponding to 'key'
* @param string $key 'key' field where corresponding 'value' is needed
*/
private static function _selectValue($key)
{
$selectTable = "`" . self::DB_TABLE_CONFIG . "`";
$selectFields[0] = "`" . self::DB_FIELD_VALUE . "`";
$selectConditions[0] = "`" . self::DB_FIELD_KEY . "` = '" . $key . "'";
$sqlBuilder = new SQLQBuilder();
$query = $sqlBuilder->simpleSelect($selectTable, $selectFields, $selectConditions);
$dbConnection = new DMLFunctions();
$result = $dbConnection->executeQuery($query);
if ($dbConnection->dbObject->numberOfRows($result) != 1) {
throw new Exception("Value corresponding to {$key} could not be selected");
}
$resultArray = $dbConnection->dbObject->getArray($result);
return $resultArray[0];
}
function filterUser($userName)
{
$sql_builder = new SQLQBuilder();
$dbConnection = new DMLFunctions();
$this->username = mysql_real_escape_string($userName);
$tableName = 'HS_HR_USERS a LEFT JOIN HS_HR_EMPLOYEE b ON (a.EMP_NUMBER = b.EMP_NUMBER)';
$arrFieldList[0] = 'a.USER_NAME';
$arrFieldList[1] = 'a.USER_PASSWORD';
$arrFieldList[2] = 'IFNULL(b.EMP_FIRSTNAME, a.USER_NAME)';
$arrFieldList[3] = 'a.ID';
$arrFieldList[4] = 'a.USERG_ID';
$arrFieldList[5] = 'a.STATUS';
$arrFieldList[6] = 'LPAD(a.`EMP_NUMBER`, ' . $this->employeeIdLength . ', 0)';
$arrFieldList[7] = 'a.IS_ADMIN';
$arrFieldList[8] = 'b.EMP_STATUS';
$arrFieldList[9] = 'a.EMP_NUMBER';
$sql_builder->table_name = $tableName;
$sql_builder->flg_select = 'true';
$sql_builder->arr_select = $arrFieldList;
$sqlQString = $sql_builder->selectOneRecordFiltered($this->username);
//echo $sqlQString;
$message2 = $dbConnection->executeQuery($sqlQString);
//Calling the addData() function
if ($message2 && mysql_num_rows($message2) != 0) {
$i = 0;
while ($line = mysql_fetch_array($message2, MYSQL_NUM)) {
$arrayDispList[$i][0] = $line[0];
$arrayDispList[$i][1] = $line[1];
$arrayDispList[$i][2] = $line[2];
$arrayDispList[$i][3] = $line[3];
$arrayDispList[$i][4] = $line[4];
$arrayDispList[$i][5] = $line[5];
$arrayDispList[$i][6] = $line[6];
$arrayDispList[$i][7] = $line[7];
$arrayDispList[$i][8] = $line[8];
$arrayDispList[$i][9] = $line[9];
$i++;
}
return $arrayDispList;
} else {
return NULL;
}
}
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";
}
}
/**
* Get list of pay periods defined in the system
* @return array Array of all pay periods defined in the system
*/
public static function getPayPeriodList()
{
$fields[0] = self::DB_FIELD_PAYPERIOD_NAME;
$fields[1] = self::DB_FIELD_PAYPERIOD_CODE;
$sql_builder = new SQLQBuilder();
$sql_builder->table_name = self::TABLE_NAME;
$sql_builder->flg_select = 'true';
$sql_builder->arr_select = $fields;
$sql = $sql_builder->queryAllInformation();
$dbConnection = new DMLFunctions();
$result = $dbConnection->executeQuery($sql);
$periods = array();
if ($result && mysql_num_rows($result) > 0) {
while ($line = mysql_fetch_assoc($result)) {
$period = new PayPeriod();
$period->setCode($line[self::DB_FIELD_PAYPERIOD_CODE]);
$period->setName($line[self::DB_FIELD_PAYPERIOD_NAME]);
$periods[$period->getCode()] = $period;
}
}
return $periods;
}
/**
* Used to check whether the employee's status is terminated
*/
private static function _isEmployeeTerminated($employeeId)
{
$selectTable = "`hs_hr_employee` a";
$selectFields[0] = "a.`emp_status`";
$selectConditions[0] = "a.`employee_id`= {$employeeId}";
$sqlBuilder = new SQLQBuilder();
$query = $sqlBuilder->simpleSelect($selectTable, $selectFields, $selectConditions);
$dbConnection = new DMLFunctions();
$result = $dbConnection->executeQuery($query);
$employeeStatus = mysql_fetch_array($result, MYSQL_NUM);
return $employeeStatus[0] === EmploymentStatus::EMPLOYMENT_STATUS_ID_TERMINATED;
}
function getAssEmpBasSal($getID)
{
$this->getID = $getID;
$tableName = 'hs_hr_emp_basicsalary';
$arrFieldList[0] = 'EMP_NUMBER';
$arrFieldList[1] = 'SAL_GRD_CODE';
$arrFieldList[2] = 'CURRENCY_ID';
$arrFieldList[3] = 'EBSAL_BASIC_SALARY';
$arrFieldList[4] = 'PAYPERIOD_CODE';
$sql_builder = new SQLQBuilder();
$sql_builder->table_name = $tableName;
$sql_builder->flg_select = 'true';
$sql_builder->arr_select = $arrFieldList;
$sqlQString = $sql_builder->selectOneRecordFiltered($this->getID);
//echo $sqlQString;
$dbConnection = new DMLFunctions();
$message2 = $dbConnection->executeQuery($sqlQString);
//Calling the addData() function
$i = 0;
while ($line = mysql_fetch_array($message2, MYSQL_NUM)) {
for ($c = 0; count($arrFieldList) > $c; $c++) {
$arrayDispList[$i][$c] = $line[$c];
}
$i++;
}
if (isset($arrayDispList)) {
return $arrayDispList;
} else {
$arrayDispList = '';
return $arrayDispList;
}
}
/**
* Retrieves Project Id of a given Activity Id.
* @param integer $activityId
* @return integer Returns Project Id on success, Null on failiure
*/
public function retrieveActivityProjectId($activityId)
{
$selectTable = "`" . self::TABLE_NAME . "`";
$selectFields[0] = "`" . self::DB_FIELD_PROJECT_ID . "`";
$selectConditions[0] = "`" . self::DB_FIELD_ACTIVITY_ID . "` = {$activityId}";
$sqlBuilder = new SQLQBuilder();
$query = $sqlBuilder->simpleSelect($selectTable, $selectFields, $selectConditions);
$dbConnection = new DMLFunctions();
$result = $dbConnection->executeQuery($query);
$row = $dbConnection->dbObject->getArray($result);
if (isset($row[0])) {
return $row[0];
} else {
return null;
}
}
function filterJobTitles($getID)
{
$arrFieldList[0] = 'JOBTIT_CODE';
$arrFieldList[1] = 'JOBTIT_NAME';
$arrFieldList[2] = 'JOBTIT_DESC';
$arrFieldList[3] = 'JOBTIT_COMM';
$arrFieldList[4] = 'SAL_GRD_CODE';
$arrFieldList[5] = self::DB_FIELD_JOBSPEC_ID;
$tableName = 'HS_HR_JOB_TITLE';
$sql_builder = new SQLQBuilder();
$sql_builder->table_name = $tableName;
$sql_builder->flg_select = 'true';
$sql_builder->arr_select = $arrFieldList;
$sqlQString = $sql_builder->selectOneRecordFiltered($getID);
//echo $sqlQString;
$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];
$arrayDispList[$i][2] = $line[2];
$arrayDispList[$i][3] = $line[3];
$arrayDispList[$i][4] = $line[4];
$arrayDispList[$i][5] = $line[5];
$i++;
}
if (isset($arrayDispList)) {
return $arrayDispList;
} else {
$arrayDispList = '';
return $arrayDispList;
}
}
function getSalGrdCodes()
{
$tableName = 'HS_PR_SALARY_GRADE';
$arrFieldList[0] = 'SAL_GRD_CODE';
$arrFieldList[1] = 'SAL_GRD_NAME';
$sql_builder = new SQLQBuilder();
$sql_builder->table_name = $tableName;
$sql_builder->flg_select = 'true';
$sql_builder->arr_select = $arrFieldList;
$sqlQString = $sql_builder->passResultSetMessage();
//echo $sqlQString;
$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];
$i++;
}
if (isset($arrayDispList)) {
return $arrayDispList;
} else {
$arrayDispList = '';
return $arrayDispList;
}
}
public function filterExistingProjects()
{
$selectFields[] = "`" . self::PROJECT_DB_FIELD_PROJECT_ID . "`";
$selectFields[] = "`" . self::PROJECT_DB_FIELD_CUSTOMER_ID . "`";
$selectFields[] = "`" . self::PROJECT_DB_FIELD_NAME . "`";
$selectTable = self::TABLE_NAME;
$selectConditions[] = "`" . self::PROJECT_DB_FIELD_CUSTOMER_ID . "`= '" . $this->getCustomerId() . "'";
$selectConditions[] = "`" . self::PROJECT_DB_FIELD_NAME . "`= '" . $this->getProjectName() . "'";
$sqlBuilder = new SQLQBuilder();
$query = $sqlBuilder->simpleSelect($selectTable, $selectFields, $selectConditions);
$dbConnection = new DMLFunctions();
$result = $dbConnection->executeQuery($query);
$cnt = 0;
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$existingProjects[$cnt++] = $row;
}
if (isset($existingProjects)) {
return $existingProjects;
} else {
$existingProjects = '';
return $existingProjects;
}
}
function getProvinceCodes($getID)
{
$this->getID = $getID;
$tableName = 'HS_HR_PROVINCE';
$arrFieldList[0] = 'COU_CODE';
$arrFieldList[1] = 'PROVINCE_CODE';
$arrFieldList[2] = 'PROVINCE_NAME';
$sql_builder = new SQLQBuilder();
$sql_builder->table_name = $tableName;
$sql_builder->flg_select = 'true';
$sql_builder->arr_select = $arrFieldList;
$sqlQString = $sql_builder->selectOneRecordFiltered($this->getID);
//echo $sqlQString;
$dbConnection = new DMLFunctions();
$message2 = $dbConnection->executeQuery($sqlQString);
//Calling the addData() function
$i = 0;
while ($line = mysql_fetch_array($message2, MYSQL_NUM)) {
for ($c = 0; count($arrFieldList) > $c; $c++) {
$arrayDispList[$i][$c] = $line[$c];
}
$i++;
}
if (isset($arrayDispList)) {
return $arrayDispList;
} else {
$arrayDispList = '';
return $arrayDispList;
}
}
/**
* Check whether the given date is a weekend.
* @param date $date
* @return bool true on success and false on failiure
*/
public static function isWeekend($date)
{
$dayNumber = date('N', strtotime($date));
$selectTable = "`" . self::WEEKENDS_TABLE . "`";
$selectFields[0] = "`" . self::WEEKENDS_TABLE_LENGTH . "`";
$selectConditions[0] = "`" . self::WEEKENDS_TABLE_DAY . "` = {$dayNumber}";
$sqlBuilder = new SQLQBuilder();
$query = $sqlBuilder->simpleSelect($selectTable, $selectFields, $selectConditions);
$dbConnection = new DMLFunctions();
$result = $dbConnection->executeQuery($query);
$row = $dbConnection->dbObject->getArray($result);
if ($row[0] == self::WEEKENDS_LENGTH_WEEKEND) {
return true;
} else {
return false;
}
}
function getEducation($getID)
{
$this->getID = $getID;
$tableName = 'HS_HR_EDUCATION';
$arrFieldList[0] = 'EDU_CODE';
$arrFieldList[1] = 'EDU_UNI';
$arrFieldList[2] = 'EDU_DEG';
$sql_builder = new SQLQBuilder();
$sql_builder->table_name = $tableName;
$sql_builder->flg_select = 'true';
$sql_builder->arr_select = $arrFieldList;
$sqlQString = $sql_builder->selectOneRecordFiltered($this->getID);
//echo $sqlQString;
$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];
$arrayDispList[$i][2] = $line[2];
$i++;
}
if (isset($arrayDispList)) {
return $arrayDispList;
} else {
$arrayDispList = '';
return $arrayDispList;
}
}
public function getJobStatusFromTitle($getID)
{
$this->getID = $getID;
$dbConnection = new DMLFunctions();
if ($this->getID == '0') {
// if clicked on select
$selectTable = "`hs_hr_empstat` a";
$arrFieldList[0] = "a.`estat_code`";
$arrFieldList[1] = "a.`estat_code`";
$arrFieldList[2] = "a.`estat_name`";
$selectConditions = null;
} else {
$selectTable = "`hs_hr_jobtit_empstat` a,`hs_hr_empstat` b";
$arrFieldList[0] = "a.`jobtit_code`";
$arrFieldList[1] = "a.`estat_code`";
$arrFieldList[2] = "b.`estat_name`";
// ID should not have quotes, so it's safe to escape without looking at magic quotes setting
$selectConditions[] = "a.`jobtit_code`='" . mysql_real_escape_string($this->getID) . "'";
$selectConditions[] = "a.`estat_code`=b.`estat_code`";
}
$sql_builder = new SQLQBuilder();
$sqlQString = $sql_builder->simpleSelect($selectTable, $arrFieldList, $selectConditions, $arrFieldList[2], 'ASC');
$result = $dbConnection->executeQuery($sqlQString);
$i = 0;
$fieldNum = count($arrFieldList);
while ($line = $dbConnection->dbObject->getArray($result)) {
for ($c = 0; $fieldNum > $c; $c++) {
$arrayDispList[$i][$c] = $line[$c];
}
$i++;
}
if (isset($arrayDispList)) {
return $arrayDispList;
} else {
$arrayDispList = '';
return $arrayDispList;
}
return $arrayDispList;
}
public static function isUnfinishedTimesheet($timesheetId)
{
$selectTable = "`" . self::TIME_EVENT_DB_TABLE_TIME_EVENT . "`";
$selectFields[0] = "`" . self::TIME_EVENT_DB_FIELD_TIME_EVENT_ID . "`";
$selectConditions[0] = "`" . self::TIME_EVENT_DB_FIELD_DURATION . "` IS NULL";
$selectConditions[1] = "`" . self::TIME_EVENT_DB_FIELD_TIMESHEET_ID . "` = {$timesheetId}";
$sqlBuilder = new SQLQBuilder();
$query = $sqlBuilder->simpleSelect($selectTable, $selectFields, $selectConditions);
$dbConnection = new DMLFunctions();
$result = $dbConnection->executeQuery($query);
if ($dbConnection->dbObject->numberOfRows($result) > 0) {
return true;
} else {
return false;
}
}
function countUserGroups($schStr, $mode)
{
$arrFieldList[0] = 'userg_id';
$arrFieldList[1] = 'userg_name';
$sql_builder = new SQLQBuilder();
$sql_builder->table_name = $this->tableName;
$sql_builder->flg_select = 'true';
$sql_builder->arr_select = $arrFieldList;
$sqlQString = $sql_builder->countResultset($schStr, $mode);
//echo $sqlQString;
$dbConnection = new DMLFunctions();
$message2 = $dbConnection->executeQuery($sqlQString);
//Calling the addData() function
$line = mysql_fetch_array($message2, MYSQL_NUM);
return $line[0];
}
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;
}
function getAssEmpSkill($getID)
{
$this->getID = $getID;
$tableName = 'HS_HR_EMP_SKILL';
$arrFieldList[0] = 'EMP_NUMBER';
$arrFieldList[1] = 'SKILL_CODE';
$arrFieldList[2] = 'YEARS_OF_EXP';
$arrFieldList[3] = 'COMMENTS';
$sql_builder = new SQLQBuilder();
$sql_builder->table_name = $tableName;
$sql_builder->flg_select = 'true';
$sql_builder->arr_select = $arrFieldList;
$sqlQString = $sql_builder->selectOneRecordFiltered($this->getID);
//echo $sqlQString;
$dbConnection = new DMLFunctions();
$message2 = $dbConnection->executeQuery($sqlQString);
//Calling the addData() function
$i = 0;
while ($line = mysql_fetch_array($message2, MYSQL_NUM)) {
for ($c = 0; count($arrFieldList) > $c; $c++) {
$arrayDispList[$i][$c] = $line[$c];
}
$i++;
}
if (isset($arrayDispList)) {
return $arrayDispList;
} else {
$arrayDispList = '';
return $arrayDispList;
}
}
function getLastRecord($str)
{
$sql_builder = new SQLQBuilder();
$tableName = 'HS_HR_EMP_CONTRACT_EXTEND';
$arrFieldList[0] = 'ECON_EXTEND_ID';
$arrFieldList[1] = 'EMP_NUMBER';
$sql_builder->table_name = $tableName;
$sql_builder->flg_select = 'true';
$sql_builder->arr_select = $arrFieldList;
$arrSel[0] = $str;
$sqlQString = $sql_builder->selectOneRecordOnly(1, $arrSel);
$dbConnection = new DMLFunctions();
$message2 = $dbConnection->executeQuery($sqlQString);
//Calling the addData() function
$common_func = new CommonFunctions();
if (isset($message2)) {
$i = 0;
while ($line = mysql_fetch_array($message2, MYSQL_ASSOC)) {
foreach ($line as $col_value) {
$this->singleField = $col_value;
}
}
$lastrec = (int) $this->singleField + 1;
return $lastrec;
}
}
/**
* Returns an array containing employee first name, last name and employee id.
*/
public static function getEmployeeMainDetails()
{
$selectTable = "`" . self::EMPLOYEE_TABLE_NAME . "`";
$selectFields[0] = "`" . self::EMPLOYEE_FIELD_EMP_NUMBER . "`";
$selectFields[1] = "`" . self::EMPLOYEE_FIELD_FIRST_NAME . "`";
$selectFields[2] = "`" . self::EMPLOYEE_FIELD_LAST_NAME . "`";
$sqlBuilder = new SQLQBuilder();
$query = $sqlBuilder->simpleSelect($selectTable, $selectFields);
$dbConnection = new DMLFunctions();
$result = $dbConnection->executeQuery($query);
$rowCount = $dbConnection->dbObject->numberOfRows($result);
if ($rowCount > 0) {
$i = 0;
for ($i; $i < $rowCount; $i++) {
$row = $dbConnection->dbObject->getArray($result);
$employee[$i][0] = $row[0];
$employee[$i][1] = $row[1];
$employee[$i][2] = $row[2];
}
return $employee;
} else {
return null;
}
}
/**
* 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 getAssEmpStat($jobtit)
{
$sql_builder = new SQLQBuilder();
$sqlQString = $sql_builder->getAssEmpStat($jobtit);
$dbConnection = new DMLFunctions();
$message2 = $dbConnection->executeQuery($sqlQString);
//Calling the addData() function
$common_func = new CommonFunctions();
$i = 0;
while ($line = mysql_fetch_array($message2, MYSQL_NUM)) {
$arrayDispList[$i][0] = $line[0];
$arrayDispList[$i][1] = $line[1];
$i++;
}
if (isset($arrayDispList)) {
return $arrayDispList;
} else {
//Handle Exceptions
//Create Logs
}
}
/**
* Check for given date in approved timesheet for given employee
* @param string $date
* @param string $empId
* @return bool Returns true if record found, false other wise
*/
public static function checkDateInApprovedTimesheet($date, $empId)
{
$sqlBuilder = new SQLQBuilder();
$selectTable = "`" . self::TIMESHEET_DB_TABLE_TIMESHEET . "` a ";
$selectFields[0] = "a.`" . self::TIMESHEET_DB_FIELD_TIMESHEET_ID . "`";
$selectConditions[0] = "a.`" . self::TIMESHEET_DB_FIELD_START_DATE . "` <= '{$date}'";
$selectConditions[1] = "a.`" . self::TIMESHEET_DB_FIELD_END_DATE . "` >= '{$date}'";
$selectConditions[2] = "a.`" . self::TIMESHEET_DB_FIELD_STATUS . "` = '" . self::TIMESHEET_STATUS_APPROVED . "'";
$selectConditions[3] = "a.`" . self::TIMESHEET_DB_FIELD_EMPLOYEE_ID . "` = {$empId}";
$query = $sqlBuilder->simpleSelect($selectTable, $selectFields, $selectConditions, NULL, NULL, 1);
$dbConnection = new DMLFunctions();
$result = $dbConnection->executeQuery($query);
if (mysql_num_rows($result) > 0) {
return true;
} else {
return false;
}
}
public function filterExistingMembershipInfo()
{
$tableName = 'hs_hr_membership';
$selectFields[] = '`membship_code`';
$selectFields[] = '`membtype_code`';
$selectFields[] = '`membship_name`';
$selectTable = $tableName;
$selectConditions[] = "`membtype_code` = '" . $this->getMembershipTypeId() . "'";
$selectConditions[] = "`membship_name` = '" . $this->getMembershipInfoDesc() . "'";
$sqlBuilder = new SQLQBuilder();
$query = $sqlBuilder->simpleSelect($selectTable, $selectFields, $selectConditions);
$dbConnection = new DMLFunctions();
$result = $dbConnection->executeQuery($query);
$cnt = 0;
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$existingMembershipInfo[$cnt++] = $row;
}
if (isset($existingMembershipInfo)) {
return $existingMembershipInfo;
} else {
$existingMembershipInfo = '';
return $existingMembershipInfo;
}
}
public function fetchLeaveTypes($all = false)
{
$sql_builder = new SQLQBuilder();
$selectTable = "`hs_hr_leavetype` ";
$selectFields[0] = '`leave_type_id`';
$selectFields[1] = '`leave_type_name`';
if (!$all) {
$selectConditions[0] = "`available_flag` = '" . $this->availableStatusFlag . "'";
} else {
$selectConditions = null;
$selectFields[2] = '`available_flag`';
}
$selectOrder = "ASC";
$selectOrderBy = $selectFields[1];
$query = $sql_builder->simpleSelect($selectTable, $selectFields, $selectConditions, $selectOrderBy, $selectOrder, null);
//echo $query."\n";
$dbConnection = new DMLFunctions();
$result = $dbConnection->executeQuery($query);
$leaveTypeArr = $this->_buildObjArr($result);
return $leaveTypeArr;
}
public function deleteProperties()
{
$arrFieldList[0] = 'prop_id';
$sql_builder = new SQLQBuilder();
$sql_builder->table_name = $this->TABLE_NAME;
$sql_builder->flg_delete = 'true';
$sql_builder->arr_delete = $arrFieldList;
$dbConnection = new DMLFunctions();
$sqlQString = $sql_builder->deleteRecord(array($this->deleteList));
$message2 = $dbConnection->executeQuery($sqlQString);
return $message2;
}
/**
* Get a list of custom export objects with the given conditions.
*
* @param array $selectCondition Array of select conditions to use.
* @return array Array of CustomExport objects. Returns an empty (length zero) array if none found.
*/
private static function _getList($selectCondition = null)
{
$fields[0] = self::DB_FIELDS_ID;
$fields[1] = self::DB_FIELDS_NAME;
$fields[2] = self::DB_FIELDS_FIELDS;
$fields[3] = self::DB_FIELDS_HEADINGS;
$sqlBuilder = new SQLQBuilder();
$sql = $sqlBuilder->simpleSelect(self::TABLE_NAME, $fields, $selectCondition, $fields[1], "ASC");
$actList = array();
$conn = new DMLFunctions();
$result = $conn->executeQuery($sql);
while ($result && ($row = mysql_fetch_assoc($result))) {
$actList[] = self::_createFromRow($row);
}
return $actList;
}
private function _fetchEmployeesAndLeaveTypes($searchBy = "employee")
{
$selectFields[0] = "c.`emp_number` as emp_number";
$selectFields[1] = "CONCAT(c.`emp_firstname`, ' ', c.`emp_lastname`) as employee_name";
$selectFields[2] = "d.`leave_type_id` as leave_type_id";
$selectFields[3] = "d.`leave_type_name` as leave_type_name";
$selectFields[4] = "d.`available_flag` as available_flag";
$selectTable = "`hs_hr_employee` c, `hs_hr_leavetype` d ";
$selectConditions = null;
$employeeId = $this->getEmployeeId();
if (!empty($employeeId) && $this->getEmployeeId() != self::LEAVESUMMARY_CRITERIA_ALL) {
$selectConditions[] = "c.`emp_number` = {$this->getEmployeeId()}";
}
$leaveTypeId = $this->getLeaveTypeId();
if (!empty($leaveTypeId) && $this->getLeaveTypeId() != self::LEAVESUMMARY_CRITERIA_ALL) {
$selectConditions[] = "d.`leave_type_id` = '{$this->getLeaveTypeId()}'";
}
$sqlBuilder = new SQLQBuilder();
$query = $sqlBuilder->simpleSelect($selectTable, $selectFields, $selectConditions);
$dbConnection = new DMLFunctions();
$result = $dbConnection->executeQuery($query);
$resultArr = null;
while ($row = mysql_fetch_assoc($result)) {
$row['no_of_days_allotted'] = 0;
$row['leave_taken'] = 0;
$row['leave_available'] = 0;
if ($searchBy == "leaveType") {
$resultArr[$row['leave_type_id']][$row['emp_number']] = $row;
} else {
$resultArr[$row['emp_number']][$row['leave_type_id']] = $row;
}
}
return $resultArr;
}
function getAssEmpDep($getID)
{
$this->getID = $getID;
$tableName = 'HS_HR_EMP_DEPENDENTS';
$arrFieldList[0] = 'EMP_NUMBER';
$arrFieldList[1] = 'ED_SEQNO';
$arrFieldList[2] = 'ED_NAME';
$arrFieldList[3] = 'ED_RELATIONSHIP';
$sql_builder = new SQLQBuilder();
$sql_builder->table_name = $tableName;
$sql_builder->flg_select = 'true';
$sql_builder->arr_select = $arrFieldList;
$sqlQString = $sql_builder->selectOneRecordFiltered($this->getID);
/*$exception_handler = new ExceptionHandler();
$exception_handler->logW($sqlQString);*/
//$sqlQString="SELECT EMP_NUMBER, EP_SEQNO, EP_PASSPORT_NUM, EP_PASSPORTISSUEDDATE, EP_PASSPORTEXPIREDATE, EP_COMMENTS, EP_PASSPORT_TYPE_FLG, EP_I9_STATUS, EP_I9_REVIEW_DATE, COU_CODE FROM HS_HR_EMP_DEPENDENTS WHERE EMP_NUMBER='EMP010'";
//echo $sqlQString;
$dbConnection = new DMLFunctions();
$message2 = $dbConnection->executeQuery($sqlQString);
//Calling the addData() function
$i = 0;
while ($line = mysql_fetch_array($message2, MYSQL_NUM)) {
for ($c = 0; count($arrFieldList) > $c; $c++) {
$arrayDispList[$i][$c] = $line[$c];
}
$i++;
}
if (isset($arrayDispList)) {
return $arrayDispList;
} else {
$arrayDispList = '';
return $arrayDispList;
}
}
