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