/**
* Set request parameters
*
* @param array $paramFields
* @return void
*/
public function setRequestParams($paramFields)
{
if ($paramFields) {
foreach ($paramFields as $fieldName => $val) {
$element = $this->m_DataPanel->getByField($fieldName);
if ($element->m_AllowURLParam == 'Y') {
if ($this->getDataObj()->getField($fieldName)->checkValueType($val)) {
//$this->setFixSearchRule("[$fldName]='$val'");
$queryString = QueryStringParam::formatQueryString("[{$fieldName}]", "=", $val);
$this->setFixSearchRule($queryString, false);
$this->m_SearchRuleBindValues = QueryStringParam::getBindValues();
}
}
}
}
}
/**
* 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 . "'";
}
/**
* Build update sql
* UPDATE table SET col1=val1, col2=val2 ...
* WHERE idcol1='id1' AND idcol2='id2'
*
* @param BizDataObj $dataObj
* @return mixed
* @todo consider the record data on main table as well as join table, this function can return a sql array.
**/
public function buildUpdateSQL($dataObj)
{
// generate column value pairs. ignore those whose inputValue=fieldValue
$sqlFlds = $dataObj->m_BizRecord->getToSaveFields('UPDATE');
$colval_pairs = null;
foreach ($sqlFlds as $fldobj) {
$col = $fldobj->m_Column;
// ignore empty vallue for Date or Datetime
if ($fldobj->m_Value == "" && $fldobj->m_OldValue == "" && ($fldobj->m_Type == "Date" || $fldobj->m_Type == "Datetime")) {
continue;
}
if ($fldobj->m_ValueOnUpdate != "") {
// ignore ValueOnUpdate field first
continue;
}
if ($fldobj->isLobField()) {
// take care of blob/clob type later
continue;
}
// ignore the column where old value is same as new value; set the column only if new value is diff than the old value
if ($fldobj->m_OldValue == $fldobj->m_Value) {
continue;
}
$_val = $fldobj->getSqlValue();
$colval_pairs[$col] = $_val;
//($_val===null || $_val === '') ? "''" : $_val;
}
if ($colval_pairs == null) {
return false;
}
// take care value on update fields only
foreach ($sqlFlds as $fldobj) {
$col = $fldobj->m_Column;
if ($fldobj->m_ValueOnUpdate != "") {
$_val = $fldobj->getValueOnUpdate();
$colval_pairs[$col] = $_val;
//($_val===null || $_val === '') ? "''" : $_val;
}
}
$sql = "";
foreach ($colval_pairs as $col => $val) {
$queryString = QueryStringParam::formatQueryString($dataObj->getQuoted($col), "=", $val);
if ($sql != "") {
$sql .= ", {$queryString}";
} else {
$sql .= $queryString;
}
}
$sql = "UPDATE " . $dataObj->getQuoted($dataObj->m_MainTable) . " SET " . $sql;
$whereStr = $dataObj->m_BizRecord->getKeySearchRule(true, true);
// use old value and column name
$sql .= " WHERE " . $whereStr;
return $sql;
}
