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