PHP SQLQBuilder::selectOneRecordFiltered方法代码示例

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

 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 getAssEmpAtt($getID)
 {
     $this->getID = $getID;
     $tableName = 'HS_HR_EMP_ATTACHMENT';
     $arrFieldList[0] = 'EMP_NUMBER';
     $arrFieldList[1] = 'EATTACH_ID';
     $arrFieldList[2] = 'EATTACH_DESC';
     $arrFieldList[3] = 'EATTACH_FILENAME';
     $arrFieldList[4] = 'EATTACH_SIZE';
     $arrFieldList[5] = 'EATTACH_ATTACHMENT';
     $arrFieldList[6] = 'EATTACH_TYPE';
     $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 getAssEmpWorkExp($getID)
 {
     $this->getID = $getID;
     $tableName = 'HS_HR_EMP_WORK_EXPERIENCE';
     $arrFieldList[0] = 'EMP_NUMBER';
     $arrFieldList[1] = 'EEXP_SEQNO';
     $arrFieldList[2] = 'EEXP_EMPLOYER';
     $arrFieldList[3] = 'EEXP_JOBTIT';
     $arrFieldList[4] = 'EEXP_FROM_DATE';
     $arrFieldList[5] = 'EEXP_TO_DATE';
     $arrFieldList[6] = 'EEXP_COMMENTS';
     $arrFieldList[7] = 'EEXP_INTERNAL';
     $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, null, 4, 'DESC');
     //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 filterLocation($getID)
 {
     $this->getID = $getID;
     $tableName = 'HS_HR_LOCATION';
     $arrFieldList[0] = 'LOC_CODE';
     $arrFieldList[1] = 'LOC_NAME';
     $arrFieldList[2] = 'LOC_COUNTRY';
     $arrFieldList[3] = 'LOC_STATE';
     $arrFieldList[4] = 'LOC_CITY';
     $arrFieldList[5] = 'LOC_ADD';
     $arrFieldList[6] = 'LOC_ZIP';
     $arrFieldList[7] = 'LOC_PHONE';
     $arrFieldList[8] = 'LOC_FAX';
     $arrFieldList[9] = 'LOC_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 getDistrictCodes($getID)
 {
     $sql_builder = new SQLQBuilder();
     $this->getID = $sql_builder->quoteCorrectString($getID, false);
     $tableName = 'HS_HR_DISTRICT';
     $arrFieldList[0] = 'PROVINCE_CODE';
     $arrFieldList[1] = 'DISTRICT_CODE';
     $arrFieldList[2] = 'DISTRICT_NAME';
     $sql_builder->table_name = $tableName;
     $sql_builder->flg_select = 'true';
     $sql_builder->arr_select = $arrFieldList;
     $sqlQString = $sql_builder->selectOneRecordFiltered($this->getID);
     $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 getMembershipCodes($getID)
 {
     $this->getID = $getID;
     $tableName = 'HS_HR_MEMBERSHIP';
     $arrFieldList[0] = 'MEMBTYPE_CODE';
     $arrFieldList[1] = 'MEMBSHIP_CODE';
     $arrFieldList[2] = 'MEMBSHIP_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 filterEmpContact($getID)
 {
     $this->getID = $getID;
     $tableName = 'HS_HR_EMPLOYEE';
     $arrFieldList[0] = 'EMP_NUMBER';
     $arrFieldList[1] = 'EMP_STREET1';
     $arrFieldList[2] = 'EMP_STREET2';
     $arrFieldList[3] = 'CITY_CODE';
     $arrFieldList[4] = 'COUN_CODE';
     $arrFieldList[5] = 'PROVIN_CODE';
     $arrFieldList[6] = 'EMP_ZIPCODE';
     $arrFieldList[7] = 'EMP_HM_TELEPHONE';
     $arrFieldList[8] = 'EMP_MOBILE';
     $arrFieldList[9] = 'EMP_WORK_TELEPHONE';
     $arrFieldList[10] = 'EMP_WORK_EMAIL';
     $arrFieldList[11] = 'EMP_OTH_EMAIL';
     $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 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 filterGetEthnicRaceInfo($getID)
 {
     $this->getID = $getID;
     $tableName = 'HS_HR_ETHNIC_RACE';
     $arrFieldList[0] = 'ETHNIC_RACE_CODE';
     $arrFieldList[1] = 'ETHNIC_RACE_DESC';
     $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);
     $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];
         // Province Code
         $arrayDispList[$i][1] = $line[1];
         // Provicne Name
         $i++;
     }
     if (isset($arrayDispList)) {
         return $arrayDispList;
     } else {
         $arrayDispList = '';
         return $arrayDispList;
     }
 }

 /**
  * Gets the tax information for the given employee
  *
  * @param string $empNumber The employee number
  * @return array array containing employee tax information
  */
 function getEmployeeTaxInfo($empNumber)
 {
     $this->empNumber = $empNumber;
     $arrFieldList[0] = self::EMP_TAX_TABLE_EMP_NUMBER;
     $arrFieldList[1] = self::EMP_TAX_FEDERAL_STATUS;
     $arrFieldList[2] = self::EMP_TAX_FEDERAL_EXCEPTIONS;
     $arrFieldList[3] = self::EMP_TAX_STATE;
     $arrFieldList[4] = self::EMP_TAX_STATE_STATUS;
     $arrFieldList[5] = self::EMP_TAX_STATE_EXCEPTIONS;
     $arrFieldList[6] = self::EMP_TAX_UNEMP_STATE;
     $arrFieldList[7] = self::EMP_TAX_WORK_STATE;
     $sql_builder = new SQLQBuilder();
     $sql_builder->table_name = self::EMP_TAX_TABLE_NAME;
     $sql_builder->flg_select = 'true';
     $sql_builder->arr_select = $arrFieldList;
     $sqlQString = $sql_builder->selectOneRecordFiltered($this->empNumber);
     $dbConnection = new DMLFunctions();
     $results = $dbConnection->executeQuery($sqlQString);
     if (mysql_num_rows($results) === 1) {
         $taxInfo = mysql_fetch_array($results, MYSQL_ASSOC);
     } else {
         $taxInfo = array(self::EMP_TAX_TABLE_EMP_NUMBER => $empNumber, self::EMP_TAX_FEDERAL_STATUS => null, self::EMP_TAX_FEDERAL_EXCEPTIONS => null, self::EMP_TAX_STATE => null, self::EMP_TAX_STATE_STATUS => null, self::EMP_TAX_STATE_EXCEPTIONS => null, self::EMP_TAX_UNEMP_STATE => null, self::EMP_TAX_WORK_STATE => null);
     }
     return $taxInfo;
 }

 function getAssEmpQual($getID)
 {
     $this->getID = $getID;
     $tableName = 'HS_HR_EMP_QUALIFICATION';
     $arrFieldList[0] = 'EMP_NUMBER';
     $arrFieldList[1] = 'QUALIFI_CODE';
     $arrFieldList[2] = 'EQUALIFI_INSTITUTE';
     $arrFieldList[3] = 'EQUALIFI_YEAR';
     $arrFieldList[4] = 'EQUALIFI_STATUS';
     $arrFieldList[5] = 'EQUALIFI_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 filterReport($getID)
 {
     $tableName = 'HS_HR_EMPREPORT';
     $arrFieldList[0] = 'REP_CODE';
     $arrFieldList[1] = 'REP_NAME';
     $arrFieldList[2] = 'REP_CRIDEF_STR';
     $arrFieldList[3] = 'REP_FLDDEF_STR';
     $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];
         $i++;
     }
     if (isset($arrayDispList)) {
         return $arrayDispList;
     } else {
         $arrayDispList = '';
         return $arrayDispList;
     }
 }

 function filterSalCurDet($getID)
 {
     $this->getID = $getID;
     $tableName = 'HS_PR_SALARY_CURRENCY_DETAIL';
     $arrFieldList[0] = 'SAL_GRD_CODE';
     $arrFieldList[1] = 'CURRENCY_ID';
     $arrFieldList[2] = 'SALCURR_DTL_MINSALARY';
     $arrFieldList[3] = 'SALCURR_DTL_MAXSALARY';
     $arrFieldList[4] = 'SALCURR_DTL_STEPSALARY';
     $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, 1);
     $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];
         }
         $arrayDispList[$i][5] = $this->getMinSalPg($getID);
         $arrayDispList[$i][6] = $this->getMaxSalPg($getID);
         $i++;
     }
     if (isset($arrayDispList)) {
         return $arrayDispList;
     } else {
         $arrayDispList = '';
         return $arrayDispList;
     }
 }

 function filterSalaryGrades($getID)
 {
     $this->getID = $getID;
     $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->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];
         $i++;
     }
     if (isset($arrayDispList)) {
         return $arrayDispList;
     } else {
         $arrayDispList = '';
         return $arrayDispList;
     }
 }