/**
* 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").'";
