/** * 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]; }
/** * Get CSV data as string * * @return string formatted csv data */ public function getCSVData() { $sql = "SELECT hs_hr_employee.emp_number, employee_id, emp_lastname, emp_firstname, emp_middle_name, emp_street1, emp_street2," . "city_code,provin_code,emp_zipcode,emp_gender,emp_birthday,emp_ssn_num,emp_status,joined_date, " . "tax_federal_status, tax_federal_exceptions, tax_state, tax_state_status, tax_state_exceptions, " . "tax_unemp_state,tax_work_state,custom1,custom2,custom3,custom4,custom5,custom6,custom7,custom8,custom9,custom10, " . " pay.payperiod_code,sal.ebsal_basic_salary,loc.loc_name,comp.title " . " FROM hs_hr_employee " . " LEFT JOIN hs_hr_emp_us_tax tax on (tax.emp_number = hs_hr_employee.emp_number) " . " LEFT JOIN hs_hr_emp_basicsalary sal on (hs_hr_employee.emp_number = sal.emp_number) " . " LEFT JOIN hs_hr_payperiod pay on (sal.payperiod_code = pay.payperiod_code) " . " LEFT JOIN hs_hr_compstructtree comp on (hs_hr_employee.work_station = comp.id) " . " LEFT JOIN hs_hr_location loc on (comp.loc_code = loc.loc_code) "; if (KeyHandler::keyExists()) { $key = KeyHandler::readKey(); $sql = str_replace("emp_ssn_num", "IF(`emp_ssn_num` IS NOT NULL, AES_DECRYPT(emp_ssn_num, '{$key}'), '') AS `emp_ssn_num`", $sql); $sql = str_replace("sal.ebsal_basic_salary", "IF(`ebsal_basic_salary` IS NOT NULL, AES_DECRYPT(ebsal_basic_salary, '{$key}'), '') AS `ebsal_basic_salary`", $sql); } $conn = new DMLFunctions(); $result = $conn->executeQuery($sql); $csv = ""; if ($result === false) { throw new Exception("Error in query: " . $sql); } while ($row = mysql_fetch_assoc($result)) { $csv .= $this->_getCSVRow($row) . "\n"; } return $csv; }
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; } }
function assignData($index, $object) { switch ($index) { case 'EMPVIEW': $repgen = new ReportGenerator(); $repgen = $object; $sqlQ = $repgen->reportQueryBuilder(); $dbConnection = new DMLFunctions(); $message2 = $dbConnection->executeQuery($sqlQ); $i = 0; while ($line = mysql_fetch_array($message2, MYSQL_NUM)) { for ($c = 0; count($repgen->field) > $c; $c++) { $arrayDispList[$i][$c] = $line[$c]; } $i++; } $repgen->reportDisplay($arrayDispList); break; } }
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; }
function getUnAssCurrCodes($salGrd, $eno) { $sqlQString = "SELECT b.CURRENCY_NAME, a.* FROM HS_PR_SALARY_CURRENCY_DETAIL a, HS_HR_CURRENCY_TYPE b WHERE a.CURRENCY_ID NOT IN (SELECT CURRENCY_ID FROM hs_hr_emp_basicsalary WHERE SAL_GRD_CODE = '" . $salGrd . "' AND EMP_NUMBER = '" . $eno . "') AND a.CURRENCY_ID = b.CURRENCY_ID AND a.SAL_GRD_CODE = '" . $salGrd . "'"; $sqlQString = strtolower($sqlQString); //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; } }
/** * 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; }
/** * 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; } }
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 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 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; } }
/** * 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; }
/** * 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 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; } }
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; } }
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; }
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 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]; }
/** * 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; } }
function getAllCurrCodesForSalaryGrade($salGrd) { $sqlQString = "SELECT b.CURRENCY_NAME, a.* FROM HS_PR_SALARY_CURRENCY_DETAIL a, HS_HR_CURRENCY_TYPE b WHERE a.SAL_GRD_CODE = '" . $salGrd . "'"; $sqlQString = strtolower($sqlQString); //echo $sqlQString; $dbConnection = new DMLFunctions(); $message2 = $dbConnection->executeQuery($sqlQString); //Calling the addData() function while ($line = mysql_fetch_array($message2, MYSQL_NUM)) { $currency = $line[2]; $arrayDispList[$currency][1] = $line[1]; $arrayDispList[$currency][2] = $line[2]; $arrayDispList[$currency][3] = $line[3]; $arrayDispList[$currency][4] = $line[4]; $arrayDispList[$currency][5] = $line[5]; } if (isset($arrayDispList)) { return $arrayDispList; } else { $arrayDispList = ''; return $arrayDispList; } }
public function getAllEmpStats() { $tableName = 'hs_hr_empstat'; $arrFieldList[0] = 'ESTAT_CODE'; $arrFieldList[1] = 'ESTAT_NAME'; $sqlQString = 'SELECT ' . $arrFieldList[0] . ', ' . $arrFieldList[1] . ' FROM ' . $tableName; $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 { //Handle Exceptions //Create Logs } }
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 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; } }
/** * 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; }
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; }
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; } }
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; }
public function haveTimeItems($activityIds) { if (!empty($activityIds) && is_array($activityIds)) { $q = "SELECT * FROM `ohrm_timesheet_item` WHERE `activity_id` IN(" . implode(", ", $activityIds) . ")"; $dbConnection = new DMLFunctions(); $result = $dbConnection->executeQuery($q); if (mysql_num_rows($result) > 0) { return true; } return false; } return false; }