function getCtrlSearchTypeOptions($fName, $selOpt, $not)
{
$options = parent::getCtrlSearchTypeOptions($fName, $selOpt, $not);
if (strlen($fName)) {
$fType = GetEditFormat($fName, $this->tName);
} else {
$fType = EDIT_FORMAT_TEXT_FIELD;
}
if ($fType == EDIT_FORMAT_DATE || $fType == EDIT_FORMAT_TIME) {
$options .= "<option value=\"NOT Equals\" " . ($selOpt == "Equals" && $not ? "selected" : "") . ">" . mlang_message("SEARCH_NOT_EQUALS") . "</option>";
$options .= "<option value=\"NOT More than\" " . ($selOpt == "More than" && $not ? "selected" : "") . ">" . mlang_message("SEARCH_NOT_MORE_THAN") . "</option>";
$options .= "<option value=\"NOT Less than\" " . ($selOpt == "Less than" && $not ? "selected" : "") . ">" . mlang_message("SEARCH_NOT_LESS_THAN") . "</option>";
$options .= "<option value=\"NOT Between\" " . ($selOpt == "Between" && $not ? "selected" : "") . ">" . mlang_message("SEARCH_NOT_BETWEEN") . "</option>";
$options .= "<option value=\"NOT Empty\" " . ($selOpt == "Empty" && $not ? "selected" : "") . ">" . mlang_message("SEARCH_NOT_EMPTY") . "</option>";
} elseif ($fType == EDIT_FORMAT_LOOKUP_WIZARD) {
if (Multiselect($fName, $this->tName)) {
$options .= "<OPTION value=\"NOT Contains\" " . ($selOpt == "Contains" && $not ? "selected" : "") . ">" . mlang_message("SEARCH_NOT_CONTAINS") . "</option>";
} else {
$options .= "<OPTION value=\"NOT Equals\" " . ($selOpt == "Equals" && $not ? "selected" : "") . ">" . mlang_message("SEARCH_NOT_EQUALS") . "</option>";
}
} elseif ($fType == EDIT_FORMAT_TEXT_FIELD || $fType == EDIT_FORMAT_TEXT_AREA || $fType == EDIT_FORMAT_PASSWORD || $fType == EDIT_FORMAT_HIDDEN || $fType == EDIT_FORMAT_READONLY) {
$options .= "<option value=\"NOT Contains\" " . ($selOpt == "Contains" && $not ? "selected" : "") . ">" . mlang_message("SEARCH_NOT_CONTAINS") . "</option>";
$options .= "<option value=\"NOT Equals\" " . ($selOpt == "Equals" && $not ? "selected" : "") . ">" . mlang_message("SEARCH_NOT_EQUALS") . "</option>";
$options .= "<option value=\"NOT Starts with\" " . ($selOpt == "Starts with" && $not ? "selected" : "") . ">" . mlang_message("SEARCH_NOT_STARTS_WITH") . "</option>";
$options .= "<option value=\"NOT More than\" " . ($selOpt == "More than" && $not ? "selected" : "") . ">" . mlang_message("SEARCH_NOT_MORE_THAN") . "</option>";
$options .= "<option value=\"NOT Less than\" " . ($selOpt == "Less than" && $not ? "selected" : "") . ">" . mlang_message("SEARCH_NOT_LESS_THAN") . "</option>";
$options .= "<option value=\"NOT Between\" " . ($selOpt == "Between" && $not ? "selected" : "") . ">" . mlang_message("SEARCH_NOT_BETWEEN") . "</option>";
$options .= "<option value=\"NOT Empty\" " . ($selOpt == "Empty" && $not ? "selected" : "") . ">" . mlang_message("SEARCH_NOT_EMPTY") . "</option>";
} else {
$options .= "<option value=\"NOT Equals\" " . ($selOpt == "Equals" && $not ? "selected" : "") . ">" . mlang_message("SEARCH_NOT_EQUALS") . "</option>";
}
return $options;
}
function StrWhereAdv($strField, $SearchFor, $strSearchOption, $SearchFor2, $etype, $isSuggest = false)
{
global $strTableName;
$type = GetFieldType($strField);
$isOracle = false;
$ismssql = true;
$isdb2 = false;
$btexttype = IsTextType($type);
$isMysql = false;
if (IsBinaryType($type)) {
return "";
}
if ($btexttype && $strSearchOption != "Contains" && $strSearchOption != "Starts with") {
return "";
}
if ($strSearchOption == 'Empty') {
if (IsCharType($type) && (!$ismssql || !$btexttype) && !$isOracle) {
return "(" . GetFullFieldName($strField) . " is null or " . GetFullFieldName($strField) . "='')";
} elseif ($ismssql && $btexttype) {
return "(" . GetFullFieldName($strField) . " is null or " . GetFullFieldName($strField) . " LIKE '')";
} else {
return GetFullFieldName($strField) . " is null";
}
}
$like = "like";
if (GetEditFormat($strField) == EDIT_FORMAT_LOOKUP_WIZARD) {
if (Multiselect($strField)) {
$SearchFor = splitvalues($SearchFor);
} else {
$SearchFor = array($SearchFor);
}
$ret = "";
foreach ($SearchFor as $value) {
if (!($value == "null" || $value == "Null" || $value == "")) {
if (strlen($ret)) {
$ret .= " or ";
}
if ($strSearchOption == "Equals") {
$value = make_db_value($strField, $value);
if (!($value == "null" || $value == "Null")) {
$ret .= GetFullFieldName($strField) . '=' . $value;
}
} elseif ($isSuggest) {
$ret .= " " . GetFullFieldName($strField) . " " . $like . " '%" . $value . "%'";
} else {
if (strpos($value, ",") !== false || strpos($value, '"') !== false) {
$value = '"' . str_replace('"', '""', $value) . '"';
}
if ($isMysql) {
$value = str_replace('\\\\', '\\\\\\\\', $value);
}
//for search by multiply Lookup wizard field
$ret .= GetFullFieldName($strField) . " = " . db_prepare_string($value);
$ret .= " or " . GetFullFieldName($strField) . " " . $like . " " . db_prepare_string("%," . $value . ",%");
$ret .= " or " . GetFullFieldName($strField) . " " . $like . " " . db_prepare_string("%," . $value);
$ret .= " or " . GetFullFieldName($strField) . " " . $like . " " . db_prepare_string($value . ",%");
}
}
}
if (strlen($ret)) {
$ret = "(" . $ret . ")";
}
return $ret;
}
if (GetEditFormat($strField) == EDIT_FORMAT_CHECKBOX) {
if ($SearchFor == "none") {
return "";
}
if (NeedQuotes($type)) {
$isOracle = false;
if ($SearchFor == "on") {
$whereStr = "(" . GetFullFieldName($strField) . "<>'0' ";
if (!$isOracle) {
$whereStr .= " and " . GetFullFieldName($strField) . "<>'' ";
}
$whereStr .= " and " . GetFullFieldName($strField) . " is not null)";
return $whereStr;
} elseif ($SearchFor == "off") {
$whereStr = "(" . GetFullFieldName($strField) . "='0' ";
if (!$isOracle) {
$whereStr .= " or " . GetFullFieldName($strField) . "='' ";
}
$whereStr .= " or " . GetFullFieldName($strField) . " is null)";
}
} else {
if ($SearchFor == "on") {
return "(" . GetFullFieldName($strField) . "<>0 and " . GetFullFieldName($strField) . " is not null)";
} elseif ($SearchFor == "off") {
return "(" . GetFullFieldName($strField) . "=0 or " . GetFullFieldName($strField) . " is null)";
}
}
}
$value1 = make_db_value($strField, $SearchFor, $etype);
$value2 = false;
$cleanvalue2 = false;
if ($strSearchOption == "Between") {
$cleanvalue2 = prepare_for_db($strField, $SearchFor2, $etype);
$value2 = make_db_value($strField, $SearchFor2, $etype);
}
if ($strSearchOption != "Contains" && $strSearchOption != "Starts with" && ($value1 === "null" || $value2 === "null")) {
return "";
}
if (IsCharType($type) && !$btexttype) {
$value1 = isEnableUpper($value1);
$value2 = isEnableUpper($value2);
$gstrField = isEnableUpper(GetFullFieldName($strField));
} elseif ($strSearchOption == "Contains" || $strSearchOption == "Starts with") {
$gstrField = db_field2char(GetFullFieldName($strField), $type);
} elseif (ViewFormat($strField) == FORMAT_TIME) {
$gstrField = db_field2time(GetFullFieldName($strField), $type);
} else {
$gstrField = GetFullFieldName($strField);
}
/*
elseif ($ismssql && !$btexttype && ($strSearchOption=="Contains" || $strSearchOption=="Starts with"))
$gstrField="convert(varchar,".GetFullFieldName($strField).")";
elseif ($isdb2 && !$btexttype && ($strSearchOption=="Contains" || $strSearchOption=="Starts with"))
$gstrField="char(".GetFullFieldName($strField).")";
else
$gstrField=GetFullFieldName($strField);
*/
$ret = "";
if ($strSearchOption == "Contains") {
if ($isMysql) {
$SearchFor = str_replace('\\\\', '\\\\\\\\', $SearchFor);
}
if (IsCharType($type) && !$btexttype) {
return $gstrField . " " . $like . " " . isEnableUpper(db_prepare_string("%" . $SearchFor . "%"));
} else {
return $gstrField . " " . $like . " " . db_prepare_string("%" . $SearchFor . "%");
}
} else {
if ($strSearchOption == "Equals") {
return $gstrField . "=" . $value1;
} else {
if ($strSearchOption == "Starts with") {
if ($isMysql) {
$SearchFor = str_replace('\\\\', '\\\\\\\\', $SearchFor);
}
if (IsCharType($type) && !$btexttype) {
return $gstrField . " " . $like . " " . isEnableUpper(db_prepare_string($SearchFor . "%"));
} else {
return $gstrField . " " . $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($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($strField, $value2, $strTableName);
$ret .= $gstrField . "<" . $value2;
} else {
$ret .= $gstrField . "<=" . $value2;
}
} else {
$ret .= $gstrField . "<=" . $value2;
}
return $ret;
}
}
}
}
}
}
}
}
return "";
}