PHP DMLFunctions::executeQuery方法代码示例

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

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

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