function SQLWhere($SearchFor, $strSearchOption, $SearchFor2, $etype, $isSuggest)
{
$baseResult = $this->baseSQLWhere($strSearchOption);
if ($baseResult === false) {
return "";
}
if ($baseResult != "") {
return $baseResult;
}
if (IsCharType($this->type)) {
if (!$this->pageObject->cipherer->isFieldPHPEncrypted($this->field)) {
$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);
} else {
$gstrField = GetFullFieldName($this->field, "", false);
}
$ret = "";
if ($this->isMysql) {
$SearchFor = str_replace('\\\\', '\\\\\\\\', $SearchFor);
}
if ($strSearchOption == "Contains") {
$SearchFor = "%" . $SearchFor . "%";
} else {
if ($strSearchOption == "Starts with") {
$SearchFor = $SearchFor . "%";
}
}
if ($strSearchOption == "Contains" || $strSearchOption == "Starts with" || $strSearchOption == "Equals") {
return $this->buildWhere($gstrField, $SearchFor, $strSearchOption == "Equals");
}
return "";
}
function StrWhereAdv($strField, $SearchFor, $strSearchOption, $SearchFor2, $etype, $isSuggest)
{
global $strTableName;
$pSet = new ProjectSettings($strTableName, PAGE_SEARCH);
$cipherer = new RunnerCipherer($strTableName);
$type = $pSet->getFieldType($strField);
$isOracle = false;
$ismssql = false;
$isdb2 = false;
$btexttype = IsTextType($type);
$isMysql = false;
if (IsBinaryType($type)) {
return "";
}
if ($strSearchOption == 'Empty') {
if (IsCharType($type) && (!$ismssql || !$btexttype) && !$isOracle) {
return "(" . GetFullFieldNameForInsert($pSet, $strField) . " is null or " . GetFullFieldNameForInsert($pSet, $strField) . "='')";
} elseif ($ismssql && $btexttype) {
return "(" . GetFullFieldNameForInsert($pSet, $strField) . " is null or " . GetFullFieldNameForInsert($pSet, $strField) . " LIKE '')";
} else {
return GetFullFieldNameForInsert($pSet, $strField) . " is null";
}
}
$like = "ilike";
if ($pSet->getEditFormat($strField) == EDIT_FORMAT_LOOKUP_WIZARD) {
if ($pSet->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, "", false) . '=' . $value;
}
} elseif ($isSuggest) {
$ret .= " " . GetFullFieldName($strField, "", false) . " " . $like . " " . db_prepare_string('%' . $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, "", false) . " = " . db_prepare_string($value);
$ret .= " or " . GetFullFieldName($strField, "", false) . " " . $like . " " . db_prepare_string("%," . $value . ",%");
$ret .= " or " . GetFullFieldName($strField, "", false) . " " . $like . " " . db_prepare_string("%," . $value);
$ret .= " or " . GetFullFieldName($strField, "", false) . " " . $like . " " . db_prepare_string($value . ",%");
}
}
}
if (strlen($ret)) {
$ret = "(" . $ret . ")";
}
return $ret;
}
if ($pSet->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 = $cipherer->MakeDBValue($strField, $SearchFor, $etype, "", true);
$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") && !$cipherer->isFieldPHPEncrypted($strField)) {
return "";
}
if (IsCharType($type) && !$btexttype) {
if (!$cipherer->isFieldPHPEncrypted($strField)) {
$value1 = $pSet->isEnableUpper($value1);
$value2 = $pSet->isEnableUpper($value2);
$gstrField = $pSet->isEnableUpper(GetFullFieldName($strField, "", false));
} else {
$gstrField = GetFullFieldName($strField, "", false);
}
} elseif ($strSearchOption == "Contains" || $strSearchOption == "Starts with") {
$gstrField = db_field2char(GetFullFieldName($strField, "", false), $type);
} elseif ($pSet->getViewFormat($strField) == FORMAT_TIME) {
$gstrField = db_field2time(GetFullFieldName($strField, "", false), $type);
} else {
$gstrField = GetFullFieldName($strField, "", false);
}
$ret = "";
if ($strSearchOption == "Contains") {
if ($isMysql) {
$SearchFor = str_replace('\\\\', '\\\\\\\\', $SearchFor);
}
if ($cipherer->isFieldPHPEncrypted($strField)) {
return $gstrField . "=" . $cipherer->MakeDBValue($strField, $SearchFor);
}
if (IsCharType($type) && !$btexttype) {
return $gstrField . " " . $like . " " . $pSet->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 . " " . $pSet->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 "";
}
/**
* 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
*/
function SQLWhere($SearchFor, $strSearchOption, $SearchFor2, $etype, $isSuggest)
{
$baseResult = $this->baseSQLWhere($strSearchOption);
if($baseResult === false)
return "";
if($baseResult != "")
return $baseResult;
if( !strlen($SearchFor) )
return "";
$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(($strSearchOption == "Contains" || $strSearchOption == "Starts with") && !$this->isStringValidForLike($SearchFor))
{
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")
{
if($this->pageObject->cipherer->isFieldPHPEncrypted($this->field))
return $gstrField."=".$this->pageObject->cipherer->MakeDBValue($this->field, $SearchFor);
$SearchFor = db_escape_likepattern($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 = db_escape_likepattern($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 "";
}
