public function fetchDataSet()
{
$dataObj = $this->getDataObj();
if (!$dataObj) {
return null;
}
QueryStringParam::setBindValues($this->searchRuleBindValues);
if ($this->isRefreshData) {
$dataObj->resetRules();
} else {
$dataObj->clearSearchRule();
}
if ($this->fixSearchRule) {
if ($this->searchRule) {
$searchRule = $this->searchRule . " AND " . $this->fixSearchRule;
} else {
$searchRule = $this->fixSearchRule;
}
} else {
$searchRule = $this->searchRule;
}
$dataObj->setSearchRule($searchRule);
if ($this->startItem > 1) {
$dataObj->setLimit($this->range, $this->startItem);
} else {
$dataObj->setLimit($this->range, ($this->currentPage - 1) * $this->range);
}
//$resultRecords = $dataObj->fetch();
$resultRecordTree = $dataObj->fetchTree($this->rootSearchRule, $this->treeDepth);
if (is_array($resultRecordTree)) {
foreach ($resultRecordTree as $resultRecordTreeNode) {
$this->tree2array($resultRecordTreeNode, $resultRecords);
}
}
$this->totalRecords = $dataObj->count();
if ($this->range && $this->range > 0) {
$this->totalPages = ceil($this->totalRecords / $this->range);
}
$selectedIndex = 0;
$this->getDataObj()->setActiveRecord($resultRecords[$selectedIndex]);
QueryStringParam::ReSet();
return $resultRecords;
}
public function fetchDataGroup()
{
//get group list first
$dataObj = $this->getDataObj();
if (!$dataObj) {
return null;
}
if ($this->isRefreshData) {
$dataObj->resetRules();
} else {
$dataObj->clearSearchRule();
}
if (strpos($this->groupBy, ":")) {
preg_match("/\\[(.*?):(.*?)\\]/si", $this->groupBy, $match);
$GroupFieldName = $match[1];
$GroupField = $match[2];
} else {
$GroupField = str_replace("[", "", $this->groupBy);
$GroupField = str_replace("]", "", $GroupField);
}
$GroupSQLRule = "GROUP BY [{$GroupField}]";
$dataObj->setOtherSQLRule($GroupSQLRule);
//within each group, search records like before
QueryStringParam::setBindValues($this->searchRuleBindValues);
if ($this->fixSearchRule) {
if ($this->searchRule) {
$searchRule = $this->searchRule . " AND " . $this->fixSearchRule;
} else {
$searchRule = $this->fixSearchRule;
}
} else {
$searchRule = $this->searchRule;
}
$dataObj->setSearchRule($searchRule);
$resultRecords = $dataObj->fetch();
$this->totalRecords = $dataObj->count();
if ($this->range && $this->range > 0) {
$this->totalPages = ceil($this->totalRecords / $this->range);
}
$this->totalPagesBak = $this->totalPages;
QueryStringParam::ReSet();
//looping
$i = 0;
$results = array();
foreach ($resultRecords as $record) {
if ($this->isRefreshData) {
$dataObj->resetRules();
} else {
$dataObj->clearSearchRule();
}
QueryStringParam::setBindValues($this->searchRuleBindValues);
$group_val = $record[$GroupField];
if ($this->fixSearchRule) {
if ($this->searchRule) {
$searchRule = $this->searchRule . " AND " . $this->fixSearchRule;
} else {
$searchRule = $this->fixSearchRule;
}
} else {
$searchRule = $this->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->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;
}
protected function getDOFromList(&$list, $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 = Openbizx::getObject($bizObjName);
if (!$bizObj) {
return false;
}
$recList = array();
$oldAssoc = $bizObj->association;
$bizObj->association = null;
QueryStringParam::reset();
$recList = $bizObj->directFetch($searchRule);
$bizObj->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 true;
}
return false;
}
/**
* 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 = Openbizx::$app->getTypeManager()->formattedStringToValue($bizField->type, $bizField->format, $val);
} else {
$realValue = $val;
}
// set the query param
$queryString = QueryStringParam::formatQueryString("[{$fieldName}]", $opr, $realValue);
return $queryString;
//return "[" . $field . "] " . $opr . " '" . $realVal . "'";
}
