public function searchSipTrace($id = null) { include_once OPENBIZ_BIN . "/easy/SearchHelper.php"; $searchRule = ""; foreach ($this->m_DataPanel as $element) { if (!$element->m_FieldName) { continue; } $value = BizSystem::clientProxy()->getFormInputs($element->m_Name); if ($element->m_FuzzySearch == "Y") { $value = "*{$value}*"; } if ($value) { $searchStr = inputValToRule($element->m_FieldName, $value, $this); if ($searchRule == "") { $searchRule .= $searchStr; } else { $searchRule .= " AND " . $searchStr; } } } $searchRuleBindValues = QueryStringParam::getBindValues(); $listFormObj = BizSystem::getObject($this->localListForm); $listFormObj->setSearchRule($searchRule, $searchRuleBindValues); $listFormObj->rerender(); }
public function fetchData() { if ($this->m_ActiveRecord != null) { return $this->m_ActiveRecord; } $dataObj = $this->getDataObj(); if ($dataObj == null) { return; } QueryStringParam::setBindValues($this->m_SearchRuleBindValues); if ($this->m_RefreshData) { $dataObj->resetRules(); } else { $dataObj->clearSearchRule(); } if ($this->m_FixSearchRule) { if ($this->m_SearchRule) { $searchRule = $this->m_SearchRule . " AND " . $this->m_FixSearchRule; } else { $searchRule = $this->m_FixSearchRule; } } $dataObj->setSearchRule($searchRule); QueryStringParam::setBindValues($this->m_SearchRuleBindValues); $resultRecords = $dataObj->fetch(); foreach ($resultRecords as $record) { $settingRecord["_" . $record['name']] = $record["value"]; } $this->m_RecordId = $resultRecords[0]['Id']; $this->setActiveRecord($settingRecord); QueryStringParam::ReSet(); return $settingRecord; }
public function SwitchSearchFieldForm($switchForm, $fieldName, $fieldValue) { include_once OPENBIZ_BIN . "/easy/SearchHelper.php"; $this->switchForm($switchForm); $listFormObj = BizSystem::getObject($switchForm); QueryStringParam::reset(); $searchRule = inputValToRule($fieldName, $fieldValue, $this); $searchRuleBindValues = QueryStringParam::getBindValues(); $listFormObj->setSearchRule($searchRule, $searchRuleBindValues); $listFormObj->rerender(); }
public function fetchDataSet() { $dataObj = $this->getDataObj(); if (!$dataObj) { return null; } QueryStringParam::setBindValues($this->m_SearchRuleBindValues); if ($this->m_RefreshData) { $dataObj->resetRules(); } else { $dataObj->clearSearchRule(); } if ($this->m_FixSearchRule) { if ($this->m_SearchRule) { $searchRule = $this->m_SearchRule . " AND " . $this->m_FixSearchRule; } else { $searchRule = $this->m_FixSearchRule; } } else { $searchRule = $this->m_SearchRule; } $dataObj->setSearchRule($searchRule); if ($this->m_StartItem > 1) { $dataObj->setLimit($this->m_Range, $this->m_StartItem); } else { $dataObj->setLimit($this->m_Range, ($this->m_CurrentPage - 1) * $this->m_Range); } //$resultRecords = $dataObj->fetch(); $resultRecordTree = $dataObj->fetchTree($this->m_RootSearchRule, $this->m_TreeDepth); if (is_array($resultRecordTree)) { foreach ($resultRecordTree as $resultRecordTreeNode) { $this->tree2array($resultRecordTreeNode, $resultRecords); } } $this->m_TotalRecords = $dataObj->count(); if ($this->m_Range && $this->m_Range > 0) { $this->m_TotalPages = ceil($this->m_TotalRecords / $this->m_Range); } $selectedIndex = 0; $this->getDataObj()->setActiveRecord($resultRecords[$selectedIndex]); QueryStringParam::ReSet(); return $resultRecords; }
/** * Insert record using given input record array * * @param array $recArr - associated array whose keys are field names of this BizDataObj * @return boolean - if return false, the caller can call getErrorMessage to get the error. **/ public function insertRecord($recArr) { if ($this->_isNeedGenerateId($recArr)) { $recArr["Id"] = $this->generateId(); } // for certain cases, id is generated before insert $this->m_BizRecord->setInputRecord($recArr); if (!$this->validateInput()) { return false; } $db = $this->getDBConnection(); try { $sql = $this->getSQLHelper()->buildInsertSQL($this, $joinValues); if ($sql) { $bindValues = QueryStringParam::getBindValues(); $bindValueString = QueryStringParam::getBindValueString(); BizSystem::log(LOG_DEBUG, "DATAOBJ", "Insert Sql = {$sql}" . "; BIND: {$bindValueString}"); QueryStringParam::reset(); $db->query($sql, $bindValues); } //$mainId = $db->lastInsertId(); if ($this->_isNeedGenerateId($recArr)) { $mainId = $this->generateId(false); $recArr["Id"] = $mainId; } BizSystem::log(LOG_DEBUG, "DATAOBJ", "New record Id is " . $recArr["Id"]); } catch (Exception $e) { BizSystem::log(LOG_ERR, "DATAOBJ", "Query Error : " . $e->getMessage()); $this->m_ErrorMessage = $this->getMessage("DATA_ERROR_QUERY") . ": " . $sql . ". " . $e->getMessage(); throw new BDOException($this->m_ErrorMessage); return null; } $this->m_BizRecord->setInputRecord($recArr); if ($this->_postUpdateLobFields($recArr) === false) { $this->m_ErrorMessage = $db->ErrorMsg(); return false; } $this->cleanCache(); $this->m_RecordId = $recArr["Id"]; $this->m_CurrentRecord = null; $this->_postInsertRecord($recArr); return true; }
/** * Convert the user input on a given fieldcontrol in query mode to search rule * * @param string $fieldName - fieldcontrol name * @param string $inputVal - use input text * @param EasyForm $formObj * @return string - searchRule */ function inputValToRule($fieldName, $inputVal, $formObj) { // todo: should check single quote for nonoperators clauses // find locations for all sql key words // search for starting ' and closing ' pair, check if sql key word in the pair $val = strtoupper(trim($inputVal)); // check " AND ", " OR " if (($pos = strpos($val, " AND ")) !== false) { $inputArr = explode(" AND ", $val); $retStr = null; foreach ($inputArr as $v) { $retStr .= $retStr ? " AND " . inputValToRule($fieldName, $v, $formObj) : inputValToRule($fieldName, $v, $formObj); } return $retStr; } else { if (($pos = strpos($val, " OR ")) !== false) { $inputArr = explode(" OR ", $val); $retStr = null; foreach ($inputArr as $v) { $retStr .= $retStr ? " OR " . inputValToRule($fieldName, $v, $formObj) : inputValToRule($fieldName, $v, $formObj); } return "(" . $retStr . ")"; } } // check >=, >, <=, <, = if (($pos = strpos($val, "<>")) !== false || ($pos = strpos($val, "!=")) !== false) { $opr = "<>"; $oprlen = 2; } else { if (($pos = strpos($val, ">=")) !== false) { $opr = ">="; $oprlen = 2; } else { if (($pos = strpos($val, ">")) !== false) { $opr = ">"; $oprlen = 1; } else { if (($pos = strpos($val, "<=")) !== false) { $opr = "<="; $oprlen = 2; } else { if (($pos = strpos($val, "<")) !== false) { $opr = "<"; $oprlen = 1; } else { if (($pos = strpos($val, "=")) !== false) { $opr = "="; $oprlen = 1; } } } } } } if ($opr) { $val = trim(substr($val, $pos + $oprlen)); } if (strpos($val, "*") !== false) { $opr = "LIKE"; $val = str_replace("*", "%", $val); } //if (strpos($val, "'") !== false) { // not needed since addslashes() is called before // $val = str_replace("'", "\\'", $val); //} if (!$opr) { $opr = "="; } // unformat value to real value data if ($formObj->getDataObj()) { $bizField = $formObj->getDataObj()->getField($fieldName); $realValue = BizSystem::typeManager()->formattedStringToValue($bizField->m_Type, $bizField->m_Format, $val); } else { $realValue = $val; } // set the query param $queryString = QueryStringParam::formatQueryString("[{$fieldName}]", $opr, $realValue); return $queryString; //return "[" . $field . "] " . $opr . " '" . $realVal . "'"; }
/** * Reset query string param * * @return void */ public static function reset() { QueryStringParam::$_counter = 1; QueryStringParam::$params = array(); }
protected function getDOFromList($selectFrom) { $pos0 = strpos($selectFrom, "["); $pos1 = strpos($selectFrom, "]"); if ($pos0 > 0 && $pos1 > $pos0) { // select from bizObj // support BizObjName[BizFieldName] or // BizObjName[BizFieldName4Text:BizFieldName4Value] or // BizObjName[BizFieldName4Text:BizFieldName4Value:BizFieldName4Pic] $bizObjName = substr($selectFrom, 0, $pos0); $pos3 = strpos($selectFrom, ":"); if ($pos3 > $pos0 && $pos3 < $pos1) { $fieldName = substr($selectFrom, $pos0 + 1, $pos3 - $pos0 - 1); $fieldName_v = substr($selectFrom, $pos3 + 1, $pos1 - $pos3 - 1); } else { $fieldName = substr($selectFrom, $pos0 + 1, $pos1 - $pos0 - 1); $fieldName_v = $fieldName; } $pos4 = strpos($fieldName_v, ":"); if ($pos4) { $fieldName_v_mixed = $fieldName_v; $fieldName_v = substr($fieldName_v_mixed, 0, $pos4); $fieldName_p = substr($fieldName_v_mixed, $pos4 + 1, strlen($fieldName_v_mixed) - $pos4 - 1); unset($fieldName_v_mixed); } $commaPos = strpos($selectFrom, ",", $pos1); if ($commaPos > $pos1) { $searchRule = trim(substr($selectFrom, $commaPos + 1)); } /* @var $bizObj BizDataObj */ $bizObj = BizSystem::getObject($bizObjName); if (!$bizObj) { return false; } $recList = array(); $oldAssoc = $bizObj->m_Association; $bizObj->m_Association = null; QueryStringParam::reset(); $recList = $bizObj->directFetch($searchRule); $bizObj->m_Association = $oldAssoc; foreach ($recList as $rec) { $list[$i]['val'] = $rec[$fieldName_v]; $list[$i]['txt'] = $rec[$fieldName]; $list[$i]['pic'] = $rec[$fieldName_p]; $i++; } return $list; } return false; }
public function fetchDataGroup() { //get group list first $dataObj = $this->getDataObj(); if (!$dataObj) { return null; } if ($this->m_RefreshData) { $dataObj->resetRules(); } else { $dataObj->clearSearchRule(); } if (strpos($this->m_GroupBy, ":")) { preg_match("/\\[(.*?):(.*?)\\]/si", $this->m_GroupBy, $match); $GroupFieldName = $match[1]; $GroupField = $match[2]; } else { $GroupField = str_replace("[", "", $this->m_GroupBy); $GroupField = str_replace("]", "", $GroupField); } $GroupSQLRule = "GROUP BY [{$GroupField}]"; $dataObj->setOtherSQLRule($GroupSQLRule); //within each group, search records like before QueryStringParam::setBindValues($this->m_SearchRuleBindValues); if ($this->m_FixSearchRule) { if ($this->m_SearchRule) { $searchRule = $this->m_SearchRule . " AND " . $this->m_FixSearchRule; } else { $searchRule = $this->m_FixSearchRule; } } else { $searchRule = $this->m_SearchRule; } $dataObj->setSearchRule($searchRule); $resultRecords = $dataObj->fetch(); $this->m_TotalRecords = $dataObj->count(); if ($this->m_Range && $this->m_Range > 0) { $this->m_TotalPages = ceil($this->m_TotalRecords / $this->m_Range); } QueryStringParam::ReSet(); //looping $i = 0; $results = array(); foreach ($resultRecords as $record) { if ($this->m_RefreshData) { $dataObj->resetRules(); } else { $dataObj->clearSearchRule(); } QueryStringParam::setBindValues($this->m_SearchRuleBindValues); $group_val = $record[$GroupField]; if ($this->m_FixSearchRule) { if ($this->m_SearchRule) { $searchRule = $this->m_SearchRule . " AND " . $this->m_FixSearchRule; } else { $searchRule = $this->m_FixSearchRule; } } else { $searchRule = $this->m_SearchRule; } if ($group_val) { if ($searchRule != "") { $searchRule = $searchRule . " AND [{$GroupField}]='{$group_val}'"; } else { $searchRule = " [{$GroupField}]='{$group_val}'"; } } else { if ($searchRule != "") { $searchRule = $searchRule . " AND [{$GroupField}] is NULL"; } else { $searchRule = " [{$GroupField}] is NULL"; } } $dataObj->setOtherSQLRule(""); $dataObj->setLimit(0, 0); $dataObj->setSearchRule($searchRule); $resultRecords_grouped = $dataObj->fetch(); //renderTable $resultRecords_grouped_table = $this->m_DataPanel->renderTable($resultRecords_grouped); if ($record[$GroupField]) { if ($GroupFieldName) { $results[$record[$GroupFieldName]] = $resultRecords_grouped_table; } else { $results[$record[$GroupField]] = $resultRecords_grouped_table; } } else { $results["Empty"] = $resultRecords_grouped_table; } $i++; QueryStringParam::ReSet(); } //set active records $selectedIndex = 0; $this->getDataObj()->setActiveRecord($resultRecords[$selectedIndex]); return $results; }
/** * Get the number of records according the Select SQL * * @param object $db database connection * @param string $sql SQL string * @return int number of records */ private function _getNumberRecords($db, $sql) { if (preg_match("/^\\s*SELECT\\s+DISTINCT/is", $sql) || preg_match('/\\s+GROUP\\s+BY\\s+/is', $sql)) { // ok, has SELECT DISTINCT or GROUP BY so see if we can use a table alias $rewritesql = preg_replace('/(\\sORDER\\s+BY\\s.*)/is', '', $sql); $rewritesql = "SELECT COUNT(*) FROM ({$rewritesql}) _TABLE_ALIAS_"; } else { // now replace SELECT ... FROM with SELECT COUNT(*) FROM $rewritesql = preg_replace('/^\\s*SELECT\\s.*\\s+FROM\\s/Uis', 'SELECT COUNT(*) FROM ', $sql); // Because count(*) and 'order by' fails with mssql, access and postgresql. // Also a good speedup optimization - skips sorting! $rewritesql = preg_replace('/(\\sORDER\\s+BY\\s.*)/is', '', $rewritesql); } try { $bindValues = QueryStringParam::getBindValues(); $bindValueString = QueryStringParam::getBindValueString(); if ($this->m_CacheLifeTime > 0) { $cache_id = md5($this->m_Name . $rewritesql . serialize($bindValues)); //try to process cache service. $cacheSvc = BizSystem::getService(CACHE_SERVICE); $cacheSvc->init($this->m_Name, $this->m_CacheLifeTime); if ($cacheSvc->test($cache_id)) { //BizSystem::log(LOG_DEBUG, "DATAOBJ", ". Query Sql = ".$rewritesql." BIND: $bindValueString"); $resultArray = $cacheSvc->load($cache_id); } else { BizSystem::log(LOG_DEBUG, "DATAOBJ", "Query Sql = " . $rewritesql . " BIND: {$bindValueString}"); $result = $db->query($rewritesql, $bindValues); $resultArray = $result->fetch(); $cacheSvc->save($resultArray, $cache_id); } } else { $resultSet = $db->query($rewritesql, $bindValues); $resultArray = $resultSet->fetch(); } } catch (Exception $e) { BizSystem::log(LOG_ERR, "DATAOBJ", "Query Error: " . $e->getMessage()); $this->m_ErrorMessage = $this->getMessage("DATA_ERROR_QUERY") . ": " . $sql . ". " . $e->getMessage(); throw new BDOException($this->m_ErrorMessage); return 0; } return $resultArray[0]; }
/** * Run Search * * @return void */ public function runSearch() { include_once OPENBIZ_BIN . "/easy/SearchHelper.php"; $searchRule = ""; foreach ($this->m_SearchPanel as $element) { if (!$element->m_FieldName) { continue; } $value = BizSystem::clientProxy()->getFormInputs($element->m_Name); if ($element->m_FuzzySearch == "Y") { $value = "*{$value}*"; } if ($value) { $searchStr = inputValToRule($element->m_FieldName, $value, $this); if ($searchRule == "") { $searchRule .= $searchStr; } else { $searchRule .= " AND " . $searchStr; } } } $this->m_SearchRule = $searchRule; $this->m_SearchRuleBindValues = QueryStringParam::getBindValues(); $this->m_RefreshData = true; $this->m_CurrentPage = 1; BizSystem::log(LOG_DEBUG, "FORMOBJ", $this->m_Name . "::runSearch(), SearchRule=" . $this->m_SearchRule); $this->runEventLog(); $this->rerender(); }
/** * Build insert-sql * INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....) * * @param BizDataObj $dataObj * @param array $joinValues array of join values * @return string Insert-SQL statement */ public function buildInsertSQL($dataObj, $joinValues = null) { // generate column value pairs. $sqlFlds = $dataObj->m_BizRecord->getToSaveFields('CREATE'); $dbInfo = BizSystem::configuration()->getDatabaseInfo($dataObj->m_Database); $dbType = $dbInfo["Driver"]; $sql_col = ""; $sql_val = ""; foreach ($sqlFlds as $fldobj) { $col = $fldobj->m_Column; // if Field Id has null value and Id is an identity type, remove the Id's column from the array if ($fldobj->m_Name == "Id" && $dataObj->m_IdGeneration == "Identity") { continue; } if ($fldobj->isLobField()) { // special value for blob/clob type $_val = $fldobj->getInsertLobValue($dbType); } else { if ($fldobj->m_ValueOnCreate != "") { $_val = $fldobj->getValueOnCreate(); } else { $_val = $fldobj->getSqlValue(); } } if (!$_val || $_val == '') { continue; } $sql_col .= $dataObj->getQuoted($col) . ", "; //$sql_val .= $_val. ", "; $sql_val .= QueryStringParam::formatQueryValue($_val) . ", "; } // if joinValues is given then add join values in to the main table InsertSQL. if (is_array($joinValues)) { foreach ($joinValues as $joinColumn => $joinValue) { if (!$joinValue || $joinValue == '') { continue; } $sql_col .= $dataObj->getQuoted($joinColumn) . ", "; $sql_val .= "'" . $joinValue . "', "; } } $sql_col = substr($sql_col, 0, -2); $sql_val = substr($sql_val, 0, -2); $sql = "INSERT INTO " . $dataObj->getQuoted($dataObj->m_MainTable) . " (" . $sql_col . ") VALUES (" . $sql_val . ")"; return $sql; }