PHP SQLQBuilder类代码示例

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