PHP SQLQBuilder::simpleSelect方法代码示例

 /**
  * 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];
 }

 /**
  * Changes the leave status to taken if the date is before
  * or on today
  *
  * @access public
  */
 public function takeLeave()
 {
     $sqlBuilder = new SQLQBuilder();
     $selectFields[0] = '`leave_date`';
     $selectFields[1] = '`leave_status`';
     $selectFields[2] = '`leave_length_hours`';
     $selectFields[3] = '`leave_length_days`';
     $selectFields[4] = '`leave_comments`';
     $selectFields[5] = '`leave_id`';
     $selectFields[6] = '`employee_id`';
     $selectFields[7] = '`leave_type_id`';
     $selectTable = '`hs_hr_leave`';
     $selectConditions[] = "`leave_status` = " . $this->statusLeaveApproved;
     $selectConditions[] = "`leave_date` <= NOW()";
     $query = $sqlBuilder->simpleSelect($selectTable, $selectFields, $selectConditions);
     $dbConnection = new DMLFunctions();
     $result = $dbConnection->executeQuery($query);
     if (isset($result) && !empty($result)) {
         if (mysql_num_rows($result) > 0) {
             $leaveObjs = $this->_buildObjArr($result);
             foreach ($leaveObjs as $leaveObj) {
                 $leaveObj->setLeaveStatus(self::LEAVE_STATUS_LEAVE_TAKEN);
                 $leaveObj->changeLeaveToTaken();
                 $leaveObj->storeLeaveTaken();
             }
             return true;
         }
     }
     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;
     }
 }

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

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

 /**
  * 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 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 getSalGrades($fetchDetailedSalGradesOnly = false)
 {
     $sql_builder = new SQLQBuilder();
     $tableName = "`hs_pr_salary_grade`";
     $arrFieldList[0] = "`sal_grd_code`";
     $arrFieldList[1] = "`sal_grd_name`";
     $sql_builder->table_name = $tableName;
     $sql_builder->flg_select = 'true';
     $sql_builder->arr_select = $arrFieldList;
     if ($fetchDetailedSalGradesOnly) {
         $subQueryTable = "`hs_pr_salary_currency_detail`";
         $subQueryFields[0] = "DISTINCT(`sal_grd_code`)";
         $subQuery = $sql_builder->simpleSelect($subQueryTable, $subQueryFields);
         $selectConditions[0] = "{$arrFieldList[0]} IN ({$subQuery})";
         $sqlQString = $sql_builder->simpleSelect($tableName, $arrFieldList, $selectConditions);
     } else {
         $sqlQString = $sql_builder->passResultSetMessage();
     }
     $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 fetchTimesheetSubmissionPeriods()
 {
     $sql_builder = new SQLQBuilder();
     $selectTable = self::TIMESHEET_SUBMISSION_PERIOD_DB_TABLE_TIMESHEET_SUBMISSION_PERIOD . " a ";
     $selectFields[0] = "a.`" . self::TIMESHEET_SUBMISSION_PERIOD_DB_FIELD_TIMESHEET_PERIOD_ID . "`";
     $selectFields[1] = "a.`" . self::TIMESHEET_SUBMISSION_PERIOD_DB_FIELD_NAME . "`";
     $selectFields[2] = "a.`" . self::TIMESHEET_SUBMISSION_PERIOD_DB_FIELD_FREQUENCY . "`";
     $selectFields[3] = "a.`" . self::TIMESHEET_SUBMISSION_PERIOD_DB_FIELD_PERIOD . "`";
     $selectFields[4] = "a.`" . self::TIMESHEET_SUBMISSION_PERIOD_DB_FIELD_START_DAY . "`";
     $selectFields[5] = "a.`" . self::TIMESHEET_SUBMISSION_PERIOD_DB_FIELD_END_DAY . "`";
     $selectFields[6] = "a.`" . self::TIMESHEET_SUBMISSION_PERIOD_DB_FIELD_DESCRIPTION . "`";
     $selectConditions = null;
     if ($this->getTimesheetPeriodId() != null) {
         $selectConditions[] = "a.`" . self::TIMESHEET_SUBMISSION_PERIOD_DB_FIELD_TIMESHEET_PERIOD_ID . "` = {$this->getTimesheetPeriodId()}";
     }
     if ($this->getName() != null) {
         $selectConditions[] = "a.`" . self::TIMESHEET_SUBMISSION_PERIOD_DB_FIELD_NAME . "` = {$this->getName()}";
     }
     if ($this->getFrequency() != null) {
         $selectConditions[] = "a.`" . self::TIMESHEET_SUBMISSION_PERIOD_DB_FIELD_FREQUENCY . "` = {$this->getFrequency()}";
     }
     if ($this->getPeriod() != null) {
         $selectConditions[] = "a.`" . self::TIMESHEET_SUBMISSION_PERIOD_DB_FIELD_PERIOD . "` = {$this->getPeriod()}";
     }
     if ($this->getStartDay() != null) {
         $selectConditions[] = "a.`" . self::TIMESHEET_SUBMISSION_PERIOD_DB_FIELD_START_DAY . "` = {$this->getStartDay()}";
     }
     if ($this->getEndDay() != null) {
         $selectConditions[] = "a.`" . self::TIMESHEET_SUBMISSION_PERIOD_DB_FIELD_END_DAY . "` = {$this->getEndDay()}";
     }
     if ($this->getDescription() != null) {
         $selectConditions[] = "a.`" . self::TIMESHEET_SUBMISSION_PERIOD_DB_FIELD_DESCRIPTION . "` = {$this->getDescription()}";
     }
     $query = $sql_builder->simpleSelect($selectTable, $selectFields, $selectConditions, $selectFields[0], 'ASC');
     $dbConnection = new DMLFunctions();
     $result = $dbConnection->executeQuery($query);
     $objArr = $this->_buildObjArr($result);
     return $objArr;
 }

 public function filterExistingCustomers()
 {
     $sqlBuilder = new SQLQBuilder();
     $customerName = $sqlBuilder->quoteCorrectString($this->getCustomerName(), true, true);
     $selectFields[] = '`customer_id`';
     $selectFields[] = '`name`';
     $selectTable = self::TABLE_NAME;
     $selectConditions[] = "`name` = '{$customerName}'";
     $query = $sqlBuilder->simpleSelect($selectTable, $selectFields, $selectConditions);
     $dbConnection = new DMLFunctions();
     $result = $dbConnection->executeQuery($query);
     $cnt = 0;
     while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
         $existingCustomers[$cnt++] = $row;
     }
     if (isset($existingCustomers)) {
         return $existingCustomers;
     } else {
         $existingCustomers = '';
         return $existingCustomers;
     }
 }

 public function isSalaryGradeNameExists($salGradeName)
 {
     $selectTable = '`ohrm_pay_grade`';
     $selectFields[] = '`sal_grd_code`';
     $selectConditions[] = "`sal_grd_name` = '{$salGradeName}'";
     $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;
     }
 }

 public function fetchHoliday($holidayId)
 {
     $selectTable = "`" . self::HOLIDAYS_TABLE . "`";
     $arrFieldList[0] = "`" . self::HOLIDAYS_TABLE_HOLIDAY_ID . "`";
     $arrFieldList[1] = "`" . self::HOLIDAYS_TABLE_DESCRIPTION . "`";
     $arrFieldList[2] = "`" . self::HOLIDAYS_TABLE_DATE . "`";
     $arrFieldList[3] = "`" . self::HOLIDAYS_TABLE_LENGTH . "`";
     $arrFieldList[4] = "`" . self::HOLIDAYS_TABLE_RECURRING . "`";
     $arrSelectConditions[0] = "`" . self::HOLIDAYS_TABLE_HOLIDAY_ID . "` = {$holidayId}";
     $sqlBuilder = new SQLQBuilder();
     $query = $sqlBuilder->simpleSelect($selectTable, $arrFieldList, $arrSelectConditions, null, null, 1);
     $dbConnection = new DMLFunctions();
     $result = $dbConnection->executeQuery($query);
     return self::_buildObjArr($result);
 }

 public function filterExistingEducations()
 {
     $selectFields[] = '`edu_code`';
     $selectFields[] = '`edu_uni`';
     $selectFields[] = '`edu_deg`';
     $selectTable = $this->tableName;
     $selectConditions[] = "`edu_deg` = '" . $this->getEduDeg() . "'";
     $selectConditions[] = "`edu_uni` = '" . $this->getEduUni() . "'";
     $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)) {
         $existingEducations[$cnt++] = $row;
     }
     if (isset($existingEducations)) {
         return $existingEducations;
     } else {
         $existingEducations = '';
         return $existingEducations;
     }
 }

 public function fetchUserEmail($userId)
 {
     $sqlQBuilder = new SQLQBuilder();
     $arrFields[0] = '`email1`';
     $arrTable = "`hs_hr_users`";
     $selectConditions[1] = "`id` = '{$userId}'";
     $query = $sqlQBuilder->simpleSelect($arrTable, $arrFields, $selectConditions, $arrFields[0], 'ASC');
     $dbConnection = new DMLFunctions();
     $result = $dbConnection->executeQuery($query);
     $row = mysql_fetch_row($result);
     return $row[0];
 }

 /**
  * Check whether leave balance is zero 
  * @param $employeeId String Employee Id
  * @param $leaveTypeId String Leave Type Id
  * @return bool true if balnace is zero, false other wise  
  */
 public function isBalanceZero()
 {
     $selectTable = "`" . self::LEAVEQUOTA_DB_TABLE_EMPLOYEE_LEAVE_QUOTA . "`";
     $selectFields[0] = "`" . self::LEAVEQUOTA_DB_FIELD_NO_OF_DAYS_ALLOTED . "`";
     $selectFields[1] = "`" . self::LEAVEQUOTA_DB_FIELD_LEAVE_TAKEN . "`";
     $selectFields[2] = "`" . self::LEAVEQUOTA_DB_FIELD_LEAVE_BROUGHT_FORWARD . "`";
     $selectConditions[0] = "`" . self::LEAVEQUOTA_DB_FIELD_YEAR . "` = '" . $this->getYear() . "'";
     $selectConditions[1] = "`" . self::LEAVEQUOTA_DB_FIELD_LEAVE_TYPE_ID . "` = '" . $this->getLeaveTypeId() . "'";
     $selectConditions[2] = "`" . self::LEAVEQUOTA_DB_FIELD_EMPLOYEE_ID . "` = '" . $this->getEmployeeId() . "'";
     $sqlBuilder = new SQLQBuilder();
     $query = $sqlBuilder->simpleSelect($selectTable, $selectFields, $selectConditions, NULL, NULL, 1);
     $dbConnection = new DMLFunctions();
     $result = $dbConnection->executeQuery($query);
     $row = $dbConnection->dbObject->getArray($result);
     if ($row[self::LEAVEQUOTA_DB_FIELD_NO_OF_DAYS_ALLOTED] + $row[self::LEAVEQUOTA_DB_FIELD_LEAVE_BROUGHT_FORWARD] == $row[self::LEAVEQUOTA_DB_FIELD_LEAVE_TAKEN]) {
         return true;
     } else {
         return false;
     }
 }