/** * It's called for Contains and Starts with searches * @param Mixed value * @param Number type (oprional) * @return String */ public function field2char($value, $type = 3) { if (IsCharType($type)) { return $value; } return "char(" . $value . ")"; }
function db_field2char($value, $type) { // is called for Contains and Starts with searches if (IsCharType($type)) { return $value; } return "''||(" . $value . ")"; }
/** * Form the SQL query string to get then the filter's data */ protected function buildSQL() { $dbfName = $this->getDbFieldName($this->fName); $sqlHead = "SELECT MIN(" . $dbfName . ") as " . $this->connection->addFieldWrappers("sliderMin") . ", MAX(" . $dbfName . ") as " . $this->connection->addFieldWrappers("sliderMax"); $whereComponents = $this->whereComponents; $gQuery = $this->pSet->getSQLQuery(); $sqlFrom = $gQuery->FromToSql() . $whereComponents["joinFromPart"]; $sqlWhere = $this->getCombinedFilterWhere(); $sqlGroupBy = "GROUP BY " . $dbfName; $sqlHaving = $this->getCombinedFilterHaving(); $notNullWhere = $dbfName . " is not NULL"; if ($this->connection->dbType != nDATABASE_Oracle) { if (IsCharType($this->fieldType)) { $notNullWhere = $dbfName . "<>'' and " . $notNullWhere; } } $sqlWhere = whereAdd($sqlWhere, $notNullWhere); $searchCombineType = $whereComponents["searchUnionRequired"] ? "or" : "and"; $this->strSQL = SQLQuery::gSQLWhere_having($sqlHead, $sqlFrom, $sqlWhere, "", "", $whereComponents["searchWhere"], $whereComponents["searchHaving"], $strSearchCriteria); }
/** * @param Mixed rs * @param Number nPageSize */ protected function WriteTableData($rs, $nPageSize) { $exportFields = $this->pSet->getExportFields(); $totalFieldsData = $this->pSet->getTotalsFields(); if ($this->eventsObject->exists("ListFetchArray")) { $row = $this->eventsObject->ListFetchArray($rs, $this); } else { $row = $this->cipherer->DecryptFetchedArray($this->connection->fetch_array($rs)); } // write header echo "<tr>"; if ($_REQUEST["type"] == "excel") { foreach ($exportFields as $field) { echo '<td style="width: 100" x:str>' . PrepareForExcel($this->pSet->label($field)) . '</td>'; } } else { foreach ($exportFields as $field) { echo "<td>" . $this->pSet->label($field) . "</td>"; } } echo "</tr>"; $totals = array(); $totalsFields = array(); foreach ($totalFieldsData as $data) { if (!in_array($data["fName"], $exportFields)) { continue; } $totals[$data["fName"]] = array("value" => 0, "numRows" => 0); $totalsFields[] = array('fName' => $data["fName"], 'totalsType' => $data["totalsType"], 'viewFormat' => $this->pSet->getViewFormat($data["fName"])); } // write data rows $iNumberOfRows = 0; $this->viewControls->forExport = "export"; while ((!$nPageSize || $iNumberOfRows < $nPageSize) && $row) { countTotals($totals, $totalsFields, $row); $values = array(); foreach ($exportFields as $field) { $fType = $this->pSet->getFieldType($field); if (IsBinaryType($fType)) { $values[$field] = "código binario demasiado grande – no puede ser desplegado"; } else { $values[$field] = $this->getViewControl($field)->getExportValue($row, ""); } } $eventRes = true; if ($this->eventsObject->exists('BeforeOut')) { $eventRes = $this->eventsObject->BeforeOut($row, $values, $this); } if ($eventRes) { $iNumberOfRows++; echo "<tr>"; foreach ($exportFields as $field) { $fType = $this->pSet->getFieldType($field); if (IsCharType($fType)) { if ($_REQUEST["type"] == "excel") { echo '<td x:str>'; } else { echo '<td>'; } } else { echo '<td>'; } $editFormat = $this->pSet->getEditFormat($field); if ($editFormat == EDIT_FORMAT_LOOKUP_WIZARD) { if ($this->pSet->NeedEncode($field)) { if ($_REQUEST["type"] == "excel") { echo PrepareForExcel($values[$field]); } else { echo $values[$field]; } } else { echo $values[$field]; } } elseif (IsBinaryType($fType)) { echo $values[$field]; } else { if ($editFormat == FORMAT_CUSTOM || $this->pSet->isUseRTE($field)) { echo $values[$field]; } elseif (NeedQuotes($field)) { if ($_REQUEST["type"] == "excel") { echo PrepareForExcel($values[$field]); } else { echo $values[$field]; } } else { echo $values[$field]; } } echo '</td>'; } echo "</tr>"; } if ($this->eventsObject->exists("ListFetchArray")) { $row = $this->eventsObject->ListFetchArray($rs, $this); } else { $row = $this->cipherer->DecryptFetchedArray($this->connection->fetch_array($rs)); } } if (count($totalFieldsData)) { echo "<tr>"; foreach ($totalFieldsData as $data) { if (!in_array($data["fName"], $exportFields)) { continue; } echo "<td>"; if (strlen($data["totalsType"])) { if ($data["totalsType"] == "COUNT") { echo "Contar" . ": "; } elseif ($data["totalsType"] == "TOTAL") { echo "Total" . ": "; } elseif ($data["totalsType"] == "AVERAGE") { echo "Promedio" . ": "; } echo runner_htmlspecialchars(GetTotals($data["fName"], $totals[$data["fName"]]["value"], $data["totalsType"], $totals[$data["fName"]]["numRows"], $this->pSet->getViewFormat($data["fName"]), PAGE_EXPORT)); } echo "</td>"; } echo "</tr>"; } }
function db_field2char($value,$type = 3) { // is called for Contains and Starts with searches if(IsCharType($type)) return $value; if(!IsDateFieldType($type)) return "convert(varchar(250),".$value.")"; return "convert(varchar(50),".$value.", 120)"; }
/** * Get axes displyed values * @param Number index * @param String value * @return String */ protected function getAxisDisplayValue($index, $value) { global $locale_info; if ($value == "" || is_null($value)) { return ""; } $groupFieldsData = $this->xml_array["group_fields"]; $field = $groupFieldsData[$index]["name"]; $int_type = $groupFieldsData[$index]["int_type"]; if ($this->fromWizard) { $control = $this->viewControls->getControl($field); } if ($int_type == 0) { // The 'Normal' interval is set if ($this->fromWizard) { $data = array($field => $value); return $control->showDBValue($data, ""); } if ($this->table_type != "db") { $fieldIdentifier = $this->xml_array["tables"][0] . "_" . $field; } else { $fieldIdentifier = $this->CrossGoodFieldName($field); } if ($this->xml_array['totals'][$fieldIdentifier]['curr'] == true) { return str_format_currency($value); } return xmlencode($value); } $ftype = $this->getFieldType($field); if (IsNumberType($ftype)) { $start = $value - $value % $int_type; $end = $start + $int_type; if ($this->fromWizard) { $dataStart = array($field => $start); $dataEnd = array($field => $end); return $control->showDBValue($dataStart, "") . " - " . $control->showDBValue($dataEnd, ""); } if ($this->table_type != "db") { $fieldIdentifier = $this->xml_array["tables"][0] . "_" . $field; } else { $fieldIdentifier = $this->CrossGoodFieldName($field); } if ($this->xml_array['totals'][$fieldIdentifier]['curr'] == true) { return str_format_currency($start) . " - " . str_format_currency($end); } return $start . " - " . $end; } if (IsCharType($ftype)) { return xmlencode(substr($value, 0, $int_type)); } if (IsDateFieldType($ftype)) { $dvalue = substr($value, 0, 4) . '-' . substr($value, 4, 2) . '-' . substr($value, 6, 2); if (strlen($value) == 10) { $dvalue .= " " . substr($value, 8, 2) . "00:00"; } elseif (strlen($value) == 12) { $dvalue .= " " . substr($value, 8, 2) . ":" . substr($value, 10, 2) . ":00"; } $tm = db2time($dvalue); if (!count($tm)) { return ""; } switch ($int_type) { case 1: // DATE_INTERVAL_YEAR return $tm[0]; case 2: // DATE_INTERVAL_QUARTER return $tm[0] . "/Q" . $tm[1]; case 3: // DATE_INTERVAL_MONTH return @$locale_info["LOCALE_SABBREVMONTHNAME" . $tm[1]] . " " . $tm[0]; case 4: // DATE_INTERVAL_WEEK $dates = $this->getDatesByWeek($tm[1] + 1, $tm[0]); return format_shortdate(db2time($dates[0])) . ' - ' . format_shortdate(db2time($dates[1])); case 5: // DATE_INTERVAL_DAY return format_shortdate($tm); case 6: // DATE_INTERVAL_HOUR $tm[4] = 0; $tm[5] = 0; return str_format_datetime($tm); case 7: // DATE_INTERVAL_MINUTE $tm[5] = 0; return str_format_datetime($tm); default: return str_format_datetime($tm); } } return ""; }
/** * It's called for Contains and Starts with searches * @param Mixed value * @param Number type (optional) * @return String */ public function field2char($value, $type = 3) { if (IsCharType($type)) { return $value; } if (!IsDateFieldType($type)) { return "convert(varchar(250)," . $value . ")"; } return "convert(varchar(50)," . $value . ", 120)"; }
function buildWhere($gstrField, $value, $equals = false) { $likeVal = $this->connection->prepareString('%searchStr":"' . $value . ':sStrEnd"%'); $notLikeVal = $this->connection->prepareString($value); if (IsCharType($this->type) && $this->pageObject->pSetEdit->getNCSearch()) { // search is case-insensitive $likeVal = $this->connection->upper($likeVal); $notLikeVal = $this->connection->upper($notLikeVal); } if ($this->connection->dbType == nDATABASE_Access) { $testSymbols = "'_{%}_'"; } else { $testSymbols = "'[{%'"; } return "((" . $gstrField . " " . $this->like . " " . $testSymbols . " and " . $gstrField . " " . $this->like . " " . $likeVal . ") or (" . $gstrField . " not " . $this->like . " " . $testSymbols . " and " . $gstrField . " " . ($equals ? "=" : $this->like) . " " . $notLikeVal . "))"; }
function WRprepare_for_db($field, $value, $table = "") { $type = WRGetFieldType($table . "." . $field); if (is_array($value)) { $value = combinevalues($value); } if (($value === "" || $value === FALSE) && !IsCharType($type)) { return ""; } if (IsDateFieldType($type)) { $value = localdatetime2db($value); } return $value; }
function prepare_for_db($field, $value, $controltype = "", $postfilename = "", $table = "") { global $strTableName; if ($table == "") { $table = $strTableName; } $pSet = new ProjectSettings($table); $filename = ""; $type = $pSet->getFieldType($field); if (!$controltype || $controltype == "multiselect") { if (is_array($value)) { $value = combinevalues($value); } if (($value === "" || $value === FALSE) && !IsCharType($type)) { return ""; } if (IsGuid($type)) { if (!IsGuidString($value)) { return ""; } } return $value; } else { if ($controltype == "time") { if (!strlen($value)) { return ""; } $time = localtime2db($value); if (IsDateFieldType($pSet->getFieldType($field))) { $time = "2000-01-01 " . $time; } return $time; } else { if (substr($controltype, 0, 4) == "date") { $dformat = substr($controltype, 4); if ($dformat == EDIT_DATE_SIMPLE || $dformat == EDIT_DATE_SIMPLE_DP) { $time = localdatetime2db($value); if ($time == "null") { return ""; } return $time; } else { if ($dformat == EDIT_DATE_DD || $dformat == EDIT_DATE_DD_DP) { $a = explode("-", $value); if (count($a) < 3) { return ""; } else { $y = $a[0]; $m = $a[1]; $d = $a[2]; } if ($y < 100) { if ($y < 70) { $y += 2000; } else { $y += 1900; } } return mysprintf("%04d-%02d-%02d", array($y, $m, $d)); } else { return ""; } } } else { if (substr($controltype, 0, 8) == "checkbox") { if ($value == "on") { $ret = 1; } else { if ($value == "none") { return ""; } else { $ret = 0; } } return $ret; } else { return false; } } } } }
/** * Get the WHERE clause conditions string for the search or suggest SQL query * @param String SearchFor * @param String strSearchOption * @param String SearchFor2 * @param String etype * @param Boolean isSuggest * @return String */ function SQLWhere($SearchFor, $strSearchOption, $SearchFor2, $etype, $isSuggest) { if ($this->lookupType == LT_LISTOFVALUES) { return parent::SQLWhere($SearchFor, $strSearchOption, $SearchFor2, $etype, $isSuggest); } $baseResult = $this->baseSQLWhere($strSearchOption); if ($baseResult === false) { return ""; } if ($baseResult !== "") { return $baseResult; } if ($this->connection->dbType != nDATABASE_MySQL) { $this->btexttype = IsTextType($this->type); } if ($this->multiselect && $strSearchOption != "Equals") { $SearchFor = splitvalues($SearchFor); } else { $SearchFor = array($SearchFor); } $gstrField = $this->getFieldSQLDecrypt(); $gstrField = $this->getFieldSQLDecrypt(); if (($strSearchOption == "Starts with" || $strSearchOption == "Contains") && (!IsCharType($this->type) || $this->btexttype)) { $gstrField = $this->connection->field2char($gstrField, $this->type); } $ret = ""; foreach ($SearchFor as $searchItem) { $value = $searchItem; if ($value == "null" || $value == "Null" || $value == "") { continue; } if (strlen(trim($ret))) { $ret .= " or "; } if (($strSearchOption == "Starts with" || $strSearchOption == "Contains") && !$this->multiselect) { $value = $this->connection->escapeLIKEpattern($value); if ($strSearchOption == "Starts with") { $value .= '%'; } if ($strSearchOption == "Contains") { $value = '%' . $value . '%'; } } if ($strSearchOption != "Starts with" && $strSearchOption != "Contains") { $value = make_db_value($this->field, $value); } $searchIsCaseInsensitive = $this->pageObject->pSetEdit->getNCSearch(); if ($strSearchOption == "Equals" && !($value == "null" || $value == "Null")) { $condition = $gstrField . '=' . $value; } else { if (($strSearchOption == "Starts with" || $strSearchOption == "Contains") && !$this->multiselect) { $condition = $gstrField . " " . $this->like . " " . $this->connection->prepareString($value); } else { if ($strSearchOption == "More than") { $condition = $gstrField . " > " . $value; } else { if ($strSearchOption == "Less than") { $condition = $gstrField . "<" . $value; } else { if ($strSearchOption == "Equal or more than") { $condition = $gstrField . ">=" . $value1; } else { if ($strSearchOption == "Equal or less than") { $condition = $gstrField . "<=" . $value1; } else { if ($strSearchOption == "Between") { $value2 = $this->connection->prepareString($SearchFor2); if ($this->lookupType == LT_QUERY && IsCharType($this->type) && !$this->btexttype && $searchIsCaseInsensitive) { $value2 = $this->connection->upper($value2); } $condition = $gstrField . ">=" . $value . " and "; if (IsDateFieldType($this->type)) { $timeArr = db2time($SearchFor2); // for dates without time, add one day if ($timeArr[3] == 0 && $timeArr[4] == 0 && $timeArr[5] == 0) { $timeArr = adddays($timeArr, 1); $SearchFor2 = $timeArr[0] . "-" . $timeArr[1] . "-" . $timeArr[2]; $SearchFor2 = add_db_quotes($this->field, $SearchFor2, $this->tName); $condition .= $gstrField . "<" . $SearchFor2; } else { $condition .= $gstrField . "<=" . $value2; } } else { $condition .= $gstrField . "<=" . $value2; } } else { if ($this->multiselect) { if (strpos($value, ",") !== false || strpos($value, '"') !== false) { $value = '"' . str_replace('"', '""', $value) . '"'; } $fullFieldName = $this->getFieldSQLDecrypt(); $value = $this->connection->escapeLIKEpattern($value); //for search by multiply Lookup wizard field $ret .= $fullFieldName . " = " . $this->connection->prepareString($value); $ret .= " or " . $fullFieldName . " " . $this->like . " " . $this->connection->prepareString("%," . $value . ",%"); $ret .= " or " . $fullFieldName . " " . $this->like . " " . $this->connection->prepareString("%," . $value); $ret .= " or " . $fullFieldName . " " . $this->like . " " . $this->connection->prepareString($value . ",%"); } } } } } } } } if ($condition != "" && ($isSuggest || $strSearchOption == "Contains" || $strSearchOption == "Equals" || $strSearchOption == "Starts with" || $strSearchOption == "More than" || $strSearchOption == "Less than" || $strSearchOption == "Equal or more than" || $strSearchOption == "Equal or less than" || $strSearchOption == "Between")) { if ($this->linkAndDisplaySame || $strSearchOption != "Contains" && $strSearchOption != "Starts with") { $ret .= " " . $condition; } else { return ""; } } } $ret = trim($ret); if (strlen($ret)) { $ret = "(" . $ret . ")"; } return $ret; }
/** * Set the sorting params */ protected function setSortingParams() { $this->sortingType = $this->pSet->getFilterSortValueType($this->fName); $this->isDescendingSortOrder = $this->pSet->isFilterSortOrderDescending($this->fName); $this->useFormatedValueInSorting = $this->sortingType == SORT_BY_DISP_VALUE || IsCharType($this->fieldType) || $this->pSet->getEditFormat($this->fName) == EDIT_FORMAT_LOOKUP_WIZARD; }
function SQLWhere($SearchFor, $strSearchOption, $SearchFor2, $etype, $isSuggest) { $baseResult = $this->baseSQLWhere($strSearchOption); if ($baseResult === false) { return ""; } if ($baseResult != "") { return $baseResult; } $value1 = $this->pageObject->cipherer->MakeDBValue($this->field, $SearchFor, $etype, "", true); $value2 = false; $cleanvalue2 = false; if ($strSearchOption == "Between") { $cleanvalue2 = prepare_for_db($this->field, $SearchFor2, $etype); $value2 = make_db_value($this->field, $SearchFor2, $etype); } if ($strSearchOption != "Contains" && $strSearchOption != "Starts with" && ($value1 === "null" || $value2 === "null") && !$this->pageObject->cipherer->isFieldPHPEncrypted($this->field)) { return ""; } if (IsCharType($this->type) && !$this->btexttype) { if (!$this->pageObject->cipherer->isFieldPHPEncrypted($this->field)) { $value1 = $this->pageObject->pSetEdit->isEnableUpper($value1); $value2 = $this->pageObject->pSetEdit->isEnableUpper($value2); $gstrField = $this->pageObject->pSetEdit->isEnableUpper(GetFullFieldName($this->field, "", false)); } else { $gstrField = GetFullFieldName($this->field, "", false); } } elseif ($strSearchOption == "Contains" || $strSearchOption == "Starts with") { $gstrField = db_field2char(GetFullFieldName($this->field, "", false), $this->type); } elseif ($this->pageObject->pSetEdit->getViewFormat($this->field) == FORMAT_TIME) { $gstrField = db_field2time(GetFullFieldName($this->field, "", false), $this->type); } else { $gstrField = GetFullFieldName($this->field, "", false); } $ret = ""; if ($strSearchOption == "Contains") { $SearchFor = $this->escapeSearchValForMySQL($SearchFor); if ($this->pageObject->cipherer->isFieldPHPEncrypted($this->field)) { return $gstrField . "=" . $this->pageObject->cipherer->MakeDBValue($this->field, $SearchFor); } if (IsCharType($this->type) && !$this->btexttype) { return $gstrField . " " . $this->like . " " . $this->pageObject->pSetEdit->isEnableUpper(db_prepare_string("%" . $SearchFor . "%")); } else { return $gstrField . " " . $this->like . " " . db_prepare_string("%" . $SearchFor . "%"); } } else { if ($strSearchOption == "Equals") { return $gstrField . "=" . $value1; } else { if ($strSearchOption == "Starts with") { $SearchFor = $this->escapeSearchValForMySQL($SearchFor); if (IsCharType($this->type) && !$this->btexttype) { return $gstrField . " " . $this->like . " " . $this->pageObject->pSetEdit->isEnableUpper(db_prepare_string($SearchFor . "%")); } else { return $gstrField . " " . $this->like . " " . db_prepare_string($SearchFor . "%"); } } else { if ($strSearchOption == "More than") { return $gstrField . ">" . $value1; } else { if ($strSearchOption == "Less than") { return $gstrField . "<" . $value1; } else { if ($strSearchOption == "Equal or more than") { return $gstrField . ">=" . $value1; } else { if ($strSearchOption == "Equal or less than") { return $gstrField . "<=" . $value1; } else { if ($strSearchOption == "Between") { $ret = $gstrField . ">=" . $value1 . " and "; if (IsDateFieldType($this->type)) { $timeArr = db2time($cleanvalue2); // for dates without time, add one day if ($timeArr[3] == 0 && $timeArr[4] == 0 && $timeArr[5] == 0) { $timeArr = adddays($timeArr, 1); $value2 = $timeArr[0] . "-" . $timeArr[1] . "-" . $timeArr[2]; $value2 = add_db_quotes($this->field, $value2, $this->pageObject->tName); $ret .= $gstrField . "<" . $value2; } else { $ret .= $gstrField . "<=" . $value2; } } else { $ret .= $gstrField . "<=" . $value2; } return $ret; } } } } } } } } return ""; }
function SQLWhere($SearchFor, $strSearchOption, $SearchFor2, $etype, $isSuggest) { if ($this->lookupType == LT_LISTOFVALUES) { return parent::SQLWhere($SearchFor, $strSearchOption, $SearchFor2, $etype, $isSuggest); } $baseResult = $this->baseSQLWhere($strSearchOption); if ($baseResult === false) { return ""; } if ($baseResult != "") { return $baseResult; } $displayFieldType = $this->type; if ($this->lookupType == LT_QUERY) { $displayFieldType = $this->lookupPSet->getFieldType($this->field); $this->btexttype = IsTextType($displayFieldType); } if ($this->multiselect) { $SearchFor = splitvalues($SearchFor); } else { $SearchFor = array($SearchFor); } $ret = ""; if ($this->linkAndDisplaySame) { $gstrField = GetFullFieldName($this->field, "", false); } else { $gstrField = GetFullFieldName($this->displayFieldName, $this->lookupTable, false); } if ($this->customDisplay) { $gstrField = $this->lwDisplayFieldWrapped; } else { if (!$this->linkAndDisplaySame && $this->lookupType == LT_QUERY && IsCharType($displayFieldType) && !$this->btexttype && !$this->ciphererDisplay->isFieldPHPEncrypted($this->displayFieldName)) { $gstrField = $this->lookupPSet->isEnableUpper(GetFullFieldName($this->displayFieldName, $this->lookupTable, false)); } } foreach ($SearchFor as $value) { if (!($value == "null" || $value == "Null" || $value == "")) { if (strlen(trim($ret))) { $ret .= " or "; } if (!$this->multiselect) { if ($strSearchOption == "Starts with") { $value .= '%'; } if ($isSuggest || $strSearchOption == "Contains") { $value = '%' . $value . '%'; } if ($isSuggest || $strSearchOption == "Contains" || $strSearchOption == "Starts with" || $strSearchOption == "More than" || $strSearchOption == "Less than" || $strSearchOption == "Equal or more than" || $strSearchOption == "Equal or less than" || $strSearchOption == "Between" || $strSearchOption == "Equals" && $this->LCType == LCT_AJAX && !$this->linkAndDisplaySame) { $value = $this->escapeSearchValForMySQL($value); if ($this->lookupType == LT_QUERY && IsCharType($displayFieldType) && !$this->btexttype) { $value = $this->lookupPSet->isEnableUpper(db_prepare_string($value)); } else { $value = db_prepare_string($value); } } else { if ($strSearchOption == "Equals") { $value = make_db_value($this->field, $value); } } } if ($strSearchOption == "Equals") { if (!($value == "null" || $value == "Null")) { if ($this->LCType == LCT_AJAX && !$this->linkAndDisplaySame) { $condition = $gstrField . '=' . $value; } else { $condition = GetFullFieldName($this->field, "", false) . '=' . $value; } } } else { if ($strSearchOption == "Starts with" || $strSearchOption == "Contains" && !$this->multiselect) { $condition = $gstrField . " " . $this->like . " " . $value; } else { if ($strSearchOption == "More than") { $condition = $gstrField . " > " . $value; } else { if ($strSearchOption == "Less than") { $condition = $gstrField . "<" . $value; } else { if ($strSearchOption == "Equal or more than") { $condition = $gstrField . ">=" . $value1; } else { if ($strSearchOption == "Equal or less than") { $condition = $gstrField . "<=" . $value1; } else { if ($strSearchOption == "Between") { if ($this->lookupType == LT_QUERY && IsCharType($displayFieldType) && !$this->btexttype) { $value2 = $this->lookupPSet->isEnableUpper(db_prepare_string($SearchFor2)); } else { $value2 = db_prepare_string($SearchFor2); } $condition = $gstrField . ">=" . $value . " and "; if (IsDateFieldType($this->type)) { $timeArr = db2time($SearchFor2); // for dates without time, add one day if ($timeArr[3] == 0 && $timeArr[4] == 0 && $timeArr[5] == 0) { $timeArr = adddays($timeArr, 1); $SearchFor2 = $timeArr[0] . "-" . $timeArr[1] . "-" . $timeArr[2]; $SearchFor2 = add_db_quotes($this->field, $SearchFor2, $this->pageObject->tName); $condition .= $gstrField . "<" . $SearchFor2; } else { $condition .= $gstrField . "<=" . $value2; } } else { $condition .= $gstrField . "<=" . $value2; } } else { if (strpos($value, ",") !== false || strpos($value, '"') !== false) { $value = '"' . str_replace('"', '""', $value) . '"'; } $value = $this->escapeSearchValForMySQL($value); //for search by multiply Lookup wizard field $ret .= GetFullFieldName($this->field, "", false) . " = " . db_prepare_string($value); $ret .= " or " . GetFullFieldName($this->field, "", false) . " " . $this->like . " " . db_prepare_string("%," . $value . ",%"); $ret .= " or " . GetFullFieldName($this->field, "", false) . " " . $this->like . " " . db_prepare_string("%," . $value); $ret .= " or " . GetFullFieldName($this->field, "", false) . " " . $this->like . " " . db_prepare_string($value . ",%"); } } } } } } } if ($condition != "" && ($isSuggest || $strSearchOption == "Contains" || $strSearchOption == "Equals" || $strSearchOption == "Starts with" || $strSearchOption == "More than" || $strSearchOption == "Less than" || $strSearchOption == "Equal or more than" || $strSearchOption == "Equal or less than" || $strSearchOption == "Between")) { if ($this->linkAndDisplaySame || $strSearchOption == "Equals" && $this->LCType != LCT_AJAX) { $ret .= " " . $condition; } else { if ($this->lookupType == LT_QUERY) { $lookupQueryObj = $this->lookupPSet->getSQLQuery(); $ret .= " EXISTS (" . $lookupQueryObj->toSql($condition . " and " . GetFullFieldName($this->linkFieldName, $this->lookupTable, false) . " = " . AddTableWrappers($this->pageObject->pSetEdit->getStrOriginalTableName()) . "." . AddFieldWrappers($this->field), '', null, false) . ")"; } else { $ret .= " EXISTS (SELECT 1 as fld from " . AddTableWrappers($this->lookupTable) . " where " . $condition . " and " . $this->lwLinkField . " = " . AddTableWrappers($this->pageObject->pSetEdit->getStrOriginalTableName()) . "." . AddFieldWrappers($this->field) . ")"; } } } } } if (strlen(trim($ret))) { $ret = "(" . $ret . ")"; } else { $ret = trim($ret); } return $ret; }
function SQLStatement($sql, $order, $pagesize, $connection, &$searchClauseObj, &$params) { // copy properties to object RunnerApply($this, $params); $this->searchClauseObj = $searchClauseObj; $this->pSet = new ProjectSettings($this->tName, PAGE_REPORT); if (!is_array($sql)) { die('Invalid sql parameter'); } global $reportCaseSensitiveGroupFields; $this->_originalSql = $sql; $start = 0; $fields = array(); for ($i = 0; $i < count($this->repGroupFields); $i++) { for ($j = 0; $j < count($this->fieldsArr); $j++) { if ($this->repGroupFields[$i]['strGroupField'] == $this->fieldsArr[$j]['name']) { $add = array(); $add['name'] = $this->fieldsArr[$j]['name']; if (IsNumberType($this->pSet->getFieldType($this->fieldsArr[$j]['name']))) { $add['type'] = 'numeric'; } elseif (IsCharType($this->pSet->getFieldType($this->fieldsArr[$j]['name']))) { $add['type'] = 'char'; $add['case_sensitive'] = $reportCaseSensitiveGroupFields; } elseif (IsDateFieldType($this->pSet->getFieldType($this->fieldsArr[$j]['name']))) { $add['type'] = 'date'; } else { $add['type'] = 'char'; } $add['interval'] = $this->repGroupFields[$i]['groupInterval']; $add['viewformat'] = $this->fieldsArr[$j]['viewFormat']; $add['rowsinsummary'] = 1; if ($this->fieldsArr[$j]['totalMax'] || $this->fieldsArr[$j]['totalMin'] || $this->fieldsArr[$j]['totalAvg'] || $this->fieldsArr[$j]['totalSum']) { $add['rowsinsummary']++; } if ($this->repLayout == REPORT_STEPPED) { $add['rowsinheader'] = 1; } elseif ($this->repLayout == REPORT_BLOCK) { $add['rowsinheader'] = 0; } elseif ($this->repLayout == REPORT_OUTLINE || $this->repLayout == REPORT_ALIGN) { if ($j == count($this->fieldsArr) - 1) { $add['rowsinheader'] = 2; } else { $add['rowsinheader'] = 1; } } elseif ($this->repLayout == REPORT_TABULAR) { $add['rowsinheader'] = 0; } $fields[] = $add; } } } $this->_hasGroups = count($fields) > 0; foreach ($fields as $field) { $f = create_reportfield($field['name'], $field['type'], $field['interval'], 'grp', $this->tName); $start = $f->setStart($start); if (isset($field['case_sensitive'])) { $f->setCaseSensitive($field['case_sensitive']); } if (isset($field['rowsinsummary'])) { $f->_rowsInSummary = $field['rowsinsummary']; } if (isset($field['rowsinheader'])) { $f->_rowsInHeader = $field['rowsinheader']; } $f->_viewFormat = $field['viewformat']; $this->_fields[] = $f; } // order if ($order) { $order_in = array(); $order_out = array(); $order_old = array(); foreach ($order as $o) { $order_in[] = $o[2] . ' as ' . cached_ffn('originalorder' . $o[0]); $order_out[] = cached_ffn('originalorder' . $o[0]) . ' ' . $o[1]; $groupField = false; for ($i = 0; $i < count($this->repGroupFields); $i++) { for ($j = 0; $j < count($this->fieldsArr); $j++) { if ($this->repGroupFields[$i]['strGroupField'] == $this->fieldsArr[$j]['name']) { $fieldIndex = $this->pSet->GetFieldIndex($this->repGroupFields[$i]['strGroupField']); if ($fieldIndex == $o[0]) { $n = $this->repGroupFields[$i]['groupOrder'] - 1; $this->_fields[$n]->_orderBy = $o[1]; $groupField = true; } } } } // don't add group fields to the $order_old if (!$groupField) { $order_old[] = $o[2] . ' ' . $o[1]; } } $this->_order_in = join(', ', $order_in); $this->_order_out = join(', ', $order_out); $this->_order_old = join(', ', $order_old); } for ($i = 0; $i < count($this->fieldsArr); $i++) { if ($this->fieldsArr[$i]['totalMax']) { $this->_aggregates[] = 'MAX(' . cached_ffn($this->fieldsArr[$i]['name'], true) . ') as ' . cached_ffn($this->fieldsArr[$i]['name'] . "MAX"); } if ($this->fieldsArr[$i]['totalMin']) { $this->_aggregates[] = 'MIN(' . cached_ffn($this->fieldsArr[$i]['name'], true) . ') as ' . cached_ffn($this->fieldsArr[$i]['name'] . "MIN"); } if ($this->fieldsArr[$i]['totalAvg']) { if (!IsDateFieldType($this->pSet->getFieldType($this->fieldsArr[$i]['name']))) { $this->_aggregates[] = 'AVG(' . cached_ffn($this->fieldsArr[$i]['name'], true) . ') as ' . cached_ffn($this->fieldsArr[$i]['name'] . "AVG"); $this->_aggregates[] = 'COUNT(' . cached_ffn($this->fieldsArr[$i]['name'], true) . ') as ' . cached_ffn($this->fieldsArr[$i]['name'] . "NAVG"); } } if ($this->fieldsArr[$i]['totalSum']) { if (!IsDateFieldType($this->pSet->getFieldType($this->fieldsArr[$i]['name']))) { $this->_aggregates[] = 'SUM(' . cached_ffn($this->fieldsArr[$i]['name'], true) . ') as ' . cached_ffn($this->fieldsArr[$i]['name'] . "SUM"); } } } $this->_reportSummary = $this->repPageSummary || $this->repGlobalSummary; $this->_pagesize = $pagesize; }
// array of vals $response = array(); if (postvalue("start")) { $suggestAllContent = false; } $searchFor = postvalue('searchFor'); $searchField = GoodFieldName(postvalue('searchField')); $strSecuritySql = SecuritySQL("Search", $strTableName); if ($searchField == "") { $allSearchFields = GetTableData($strTableName, '.googleLikeFields', array()); } // proccess fields and create sql foreach ($allSearchFields as $f) { $fType = GetFieldType($f, $strTableName); // filter fields by type if (!IsCharType($fType) && !IsNumberType($fType) && !IsGuid($fType) || IsTextType($fType)) { continue; } // get suggest for field if (($searchField == '' || $searchField == GoodFieldName($f)) && CheckFieldPermissions($f)) { $where = ""; $having = ""; if (!$gQuery->IsAggrFuncField(GetFieldIndex($f) - 1)) { $where = $searchClauseObj->getSuggestWhere($f, $fType, $suggestAllContent, $searchFor); } elseif ($gQuery->IsAggrFuncField(GetFieldIndex($f) - 1)) { $having = $searchClauseObj->getSuggestWhere($f, $fType, $suggestAllContent, $searchFor); } // prepare common vals $sqlHead = "SELECT DISTINCT " . GetFullFieldName($f) . " "; $oHaving = $gQuery->Having(); $sqlHaving = $oHaving->toSql($gQuery);
function buildWhere($gstrField, $value, $equals = false) { $likeVal = db_prepare_string('%searchStr":"' . $value . ':sStrEnd"%'); $notLikeVal = db_prepare_string($value); if (IsCharType($this->type)) { $likeVal = $this->pageObject->pSetEdit->isEnableUpper($likeVal); $notLikeVal = $this->pageObject->pSetEdit->isEnableUpper($notLikeVal); } $testSymbols = "'[{%'"; return "((" . $gstrField . " " . $this->like . " " . $testSymbols . " and " . $gstrField . " " . $this->like . " " . $likeVal . ") or (" . $gstrField . " not " . $this->like . " " . $testSymbols . " and " . $gstrField . " " . ($equals ? "=" : $this->like) . " " . $notLikeVal . "))"; }
/** * Is the search string valid for LIKE search */ function isStringValidForLike($str) { if (!IsCharType($this->type) && hasNonAsciiSymbols($str)) { return false; } return true; }
function getDisplayValue($index, $value) { global $locale_info; $field = $this->xml_array["group_fields"][$index]["name"]; if ($value == "" || is_null($value)) { return ""; } $ftype = $this->getFieldType($field); $arr = array(); $arr = $this->xml_array["group_fields"]; for ($i = 0; $i < count($arr) - 1; $i++) { if ($field == $arr[$i]["name"] && $index == $i) { $int_type = $arr[$i]["int_type"]; break; } } if ($int_type == 0) { $prefix = ""; if ($this->table_type != "db") { if (!$this->fromWizard) { $prefix = $this->xml_array["tables"][0] . "_"; } } else { $field = $this->CrossGoodFieldName($field); } if ($this->xml_array['totals'][$prefix . $field]['curr'] == true) { return str_format_currency($value); } else { return $value; } } elseif (IsNumberType($ftype)) { $start = $value - $value % $int_type; $end = $start + $int_type; $prefix = ""; if ($this->table_type != "db") { if (!$this->fromWizard) { $prefix = $this->xml_array["tables"][0] . "_"; } } else { $field = $this->CrossGoodFieldName($field); } if ($this->xml_array['totals'][$prefix . $field]['curr'] == true) { return str_format_currency($start) . " - " . str_format_currency($end); } else { return $start . " - " . $end; } } elseif (IsCharType($ftype)) { return substr($value, 0, $int_type); } elseif (IsDateFieldType($ftype)) { $dvalue = substr($value, 0, 4) . '-' . substr($value, 4, 2) . '-' . substr($value, 6, 2); if (strlen($value) == 10) { $dvalue .= " " . substr($value, 8, 2) . "00:00"; } elseif (strlen($value) == 12) { $dvalue .= " " . substr($value, 8, 2) . ":" . substr($value, 10, 2) . ":00"; } $tm = db2time($dvalue); if (!count($tm)) { return ""; } if ($int_type == 1) { // DATE_INTERVAL_YEAR return $tm[0]; } elseif ($int_type == 2) { // DATE_INTERVAL_QUARTER return $tm[0] . "/Q" . $tm[1]; } elseif ($int_type == 3) { // DATE_INTERVAL_MONTH return @$locale_info["LOCALE_SABBREVMONTHNAME" . $tm[1]] . " " . $tm[0]; } elseif ($int_type == 4) { // DATE_INTERVAL_WEEK $dates = $this->getDatesByWeek($tm[1] + 1, $tm[0]); return format_shortdate(db2time($dates[0])) . ' - ' . format_shortdate(db2time($dates[1])); } elseif ($int_type == 5) { // DATE_INTERVAL_DAY return format_shortdate($tm); } elseif ($int_type == 6) { // DATE_INTERVAL_HOUR $tm[4] = 0; $tm[5] = 0; return str_format_datetime($tm); } elseif ($int_type == 7) { // DATE_INTERVAL_MINUTE $tm[5] = 0; return str_format_datetime($tm); } else { return str_format_datetime($tm); } } }
function GetGroupDisplay($field, $value) { global $locale_info, $rpt_array, $tbl, $fields_type; for ($i = 0; $i < count($rpt_array['group_fields']) - 1; $i++){ $arr = $rpt_array['group_fields'][$i]; foreach ($rpt_array['totals'] as $fld){ if ($arr['name'] == fldname($fld)) { if (!is_wr_custom()) $ftype = WRGetFieldType($fld['table'] . "." . $fld['name']); else $ftype = $fields_type[$fld['name']]; if ($field == $arr['name']) { if ($arr['int_type'] == 0) { $prefix = ""; if (!is_wr_db()) $prefix = $rpt_array["tables"][0] . "_"; else $field = GoodFieldName($field); if ($rpt_array['totals'][$prefix . $field]['curr'] == "true") return str_format_currency($value); else return $value; } elseif (IsNumberType($ftype)) { $start = $value - ($value % $arr['int_type']); $end = $start + $arr['int_type']; $prefix = ""; if (!is_wr_db()) $prefix = $rpt_array["tables"][0] . "_"; else $field = GoodFieldName($field); if ($rpt_array['totals'][$prefix . $field]['curr'] == "true") return str_format_currency($start) . " - " . str_format_currency($end); else return $start . " - " . $end; } elseif (IsCharType($ftype)) { return substr($value, 0, $arr['int_type']); } elseif (IsDateFieldType($ftype)) { $tm = db2time($value); if (!count($tm)) return ""; if ($arr['int_type'] == 1) { // DATE_INTERVAL_YEAR return $tm[0]; } elseif ($arr['int_type'] == 2) { // DATE_INTERVAL_QUARTER return $tm[0] . "/Q" . floor(($tm[1] - 1) / 4 + 1); } elseif ($arr['int_type'] == 3) { // DATE_INTERVAL_MONTH return @$locale_info["LOCALE_SABBREVMONTHNAME" . $tm[1]] . " " . $tm[0]; } elseif ($arr['int_type'] == 4) { // DATE_INTERVAL_WEEK $start = getweekstart($tm); $end = adddays($start, 6); return format_shortdate($start) . " - " . format_shortdate($end); } elseif ($arr['int_type'] == 5) { // DATE_INTERVAL_DAY return format_shortdate($tm); } elseif ($arr['int_type'] == 6) { // DATE_INTERVAL_HOUR $tm[4] = 0; $tm[5] = 0; return str_format_datetime($tm); } elseif ($arr['int_type'] == 7) { // DATE_INTERVAL_MINUTE $tm[5] = 0; return str_format_datetime($tm); } else { return str_format_datetime($tm); } } } } } } }
/** * @param String field * @param Mixed value * @param String controltype * @param String postfilename * @param String table The datasource table name * @intellisense */ function prepare_for_db($field, $value, $controltype = "", $postfilename = "", $table = "") { global $strTableName, $cman; if ($table == "") { $table = $strTableName; } $pSet = new ProjectSettings($table); $connection = $cman->byTable($table); $filename = ""; $type = $pSet->getFieldType($field); if ((!$controltype || $controltype == "multiselect") && !IsTimeType($type)) { if (is_array($value)) { $value = combinevalues($value); } if (($value === "" || $value === FALSE) && !IsCharType($type)) { return ""; } if (IsGuid($type)) { if (!IsGuidString($value)) { return ""; } } if (IsFloatType($type)) { return makeFloat($value); } if (IsNumberType($type) && !is_int($value)) { $value = trim($value); if (!is_numeric(str_replace(",", ".", $value))) { $value = ""; } } return $value; } else { if ($controltype == "time" || IsTimeType($type)) { if (!strlen($value)) { return ""; } $time = localtime2db($value); if ($connection->dbType == nDATABASE_PostgreSQL) { $timeArr = explode(":", $time); if ($timeArr[0] > 24 || $timeArr[1] > 59 || $timeArr[2] > 59) { return ""; } } if (IsDateFieldType($type)) { $time = "2000-01-01 " . $time; } return $time; } else { if (substr($controltype, 0, 4) == "date") { $dformat = substr($controltype, 4); if ($dformat == EDIT_DATE_SIMPLE || $dformat == EDIT_DATE_SIMPLE_INLINE || $dformat == EDIT_DATE_SIMPLE_DP) { $time = localdatetime2db($value); if ($time == "null") { return ""; } return $time; } else { if ($dformat == EDIT_DATE_DD || $dformat == EDIT_DATE_DD_INLINE || $dformat == EDIT_DATE_DD_DP) { $a = explode("-", $value); if (count($a) < 3) { return ""; } else { $y = $a[0]; $m = $a[1]; $d = $a[2]; } if ($y < 100) { if ($y < 70) { $y += 2000; } else { $y += 1900; } } return mysprintf("%04d-%02d-%02d", array($y, $m, $d)); } else { return ""; } } } else { if (substr($controltype, 0, 8) == "checkbox") { if ($value == "on") { $ret = 1; } else { if ($value == "none") { return ""; } else { $ret = 0; } } return $ret; } else { return false; } } } } }
} if ($masterTablesInfoArr[$i]['dispInfo']) { $detailKeys = $masterTablesInfoArr[$i]['detailKeys']; for ($j = 0; $j < count($detailKeys); $j++) { $masterWhere .= " and " . $controls->getControl($detailKeys[$j])->getSuggestWhere('Equals', @$_SESSION[$strTableName . "_masterkey" . ($j + 1)]); } } break; } } $result = array(); // traversing searchable fields foreach ($allSearchFields as $f) { // filter fields by type $fType = $pSet->getFieldType($f); if (!IsCharType($fType) && !IsNumberType($fType) && !IsGuid($fType) || in_array($f, $detailKeys)) { continue; } if ($_connection->dbType == nDATABASE_Oracle && IsTextType($fType)) { continue; } if ($searchField != '' && $searchField != GoodFieldName($f) || !$pSet->checkFieldPermissions($f)) { continue; } $fieldControl = $controls->getControl($f); $isAggregateField = $pSet->isAggregateField($f); $where = $fieldControl->getSuggestWhere($searchOpt, $searchFor, $isAggregateField); $having = $fieldControl->getSuggestHaving($searchOpt, $searchFor, $isAggregateField); if (!strlen($where) && !strlen($having)) { continue; }
if(is_wr_custom()) { $fields_type=array(); $fields_type=WRGetAllCustomFieldType(); } foreach ($group_fields as $fld) { if(!is_wr_custom()) $type = WRGetFieldType($fld); else $type = $fields_type[$fld]; if ( IsNumberType( $type ) ) { $b_includes .= "fld_types['" . jsreplace($fld) . "'] = \"number\";"."\r\n"; } elseif ( IsCharType( $type ) ) { $b_includes .= "fld_types['" . jsreplace($fld) . "'] = \"string\";"."\r\n"; } elseif ( IsDateFieldType( $type ) ) { $b_includes .= "fld_types['" . jsreplace($fld) . "'] = \"date\";"."\r\n"; } } if (is_wr_db()) { $b_includes .= ' var NEXT_PAGE_URL = "'.GetTableLink("webreport4").'", PREV_PAGE_URL = "'.GetTableLink("webreport2").'"; '."\r\n"; } else { $b_includes .= ' var NEXT_PAGE_URL = "'.GetTableLink("webreport4").'", PREV_PAGE_URL = "'.GetTableLink("webreport0").'";