function MakeSQL($query, $strSqlWhere, $strSort, $bIncSites, $bStem)
{
global $USER;
$DB = CDatabase::GetModuleConnection('search');
$bDistinct = false;
$arSelect = array(
"ID" => "sc.ID",
"MODULE_ID" => "sc.MODULE_ID",
"ITEM_ID" => "sc.ITEM_ID",
"TITLE" => "sc.TITLE",
"TAGS" => "sc.TAGS",
"PARAM1" => "sc.PARAM1",
"PARAM2" => "sc.PARAM2",
"UPD" => "sc.UPD",
"DATE_FROM" => "sc.DATE_FROM",
"DATE_TO" => "sc.DATE_TO",
"URL" => "sc.URL",
"CUSTOM_RANK" => "sc.CUSTOM_RANK",
"FULL_DATE_CHANGE" => $DB->DateToCharFunction("sc.DATE_CHANGE")." as FULL_DATE_CHANGE",
"DATE_CHANGE" => $DB->DateToCharFunction("sc.DATE_CHANGE", "SHORT")." as DATE_CHANGE",
);
if (BX_SEARCH_VERSION > 1)
{
if ($this->Query->bText)
$arSelect["SEARCHABLE_CONTENT"] = "sct.SEARCHABLE_CONTENT";
$arSelect["USER_ID"] = "sc.USER_ID";
}
else
{
$arSelect["LID"] = "sc.LID";
$arSelect["SEARCHABLE_CONTENT"] = "sc.SEARCHABLE_CONTENT";
}
if (mb_strpos($strSort, "TITLE_RANK") !== false)
{
$strSelect = "";
if ($bStem)
{
foreach ($this->Query->m_stemmed_words as $stem)
{
if ($strSelect <> '')
$strSelect .= " + ";
$strSelect .= "if(locate('".$stem."', upper(sc.TITLE)) > 0, 1, 0)";
}
$arSelect["TITLE_RANK"] = $strSelect." as TITLE_RANK";
}
else
{
foreach ($this->Query->m_words as $word)
{
if ($strSelect <> '')
$strSelect .= " + ";
$strSelect .= "if(locate('".$DB->ForSql(ToUpper($word))."', upper(sc.TITLE)) > 0, 1, 0)";
}
$arSelect["TITLE_RANK"] = $strSelect." as TITLE_RANK";
}
}
$strStemList = '';
if ($bStem)
{
if (BX_SEARCH_VERSION > 1)
$strStemList = implode(", ", $this->Query->m_stemmed_words_id);
else
$strStemList = "'".implode("' ,'", $this->Query->m_stemmed_words)."'";
}
$bWordPos = BX_SEARCH_VERSION > 1 && COption::GetOptionString("search", "use_word_distance") == "Y";
if ($bIncSites && $bStem)
{
$arSelect["SITE_URL"] = "scsite.URL as SITE_URL";
$arSelect["SITE_ID"] = "scsite.SITE_ID";
if (!preg_match("/(sc|sct)./", $query))
{
$strSqlWhere = preg_replace('#AND\(st.TF >= [0-9.,]+\)#i', "", $strSqlWhere);
if (count($this->Query->m_stemmed_words) > 1)
$arSelect["RANK"] = "stt.RANK as `RANK`";
else
$arSelect["RANK"] = "stt.TF as `RANK`";
$strSql = "
FROM b_search_content sc
".($this->Query->bText? "INNER JOIN b_search_content_text sct ON sct.SEARCH_CONTENT_ID = sc.ID": "")."
INNER JOIN b_search_content_site scsite ON sc.ID=scsite.SEARCH_CONTENT_ID
".(count($this->Query->m_stemmed_words) > 1?
"INNER JOIN (
select search_content_id, max(st.TF) TF, ".($bWordPos? "if(STDDEV(st.PS)-".$this->normdev(count($this->Query->m_stemmed_words))." between -0.000001 and 1, 1/STDDEV(st.PS), 0) + ": "")."sum(st.TF/sf.FREQ) as `RANK`
from b_search_content_stem st, b_search_content_freq sf
where st.language_id = '".$this->Query->m_lang."'
and st.stem = sf.stem
and sf.language_id = st.language_id
and st.stem in (".$strStemList.")
".($this->tf_hwm > 0? "and st.TF >= ".number_format($this->tf_hwm, 2, ".", ""): "")."
".($this->tf_hwm_site_id <> ''? "and sf.SITE_ID = '".$DB->ForSQL($this->tf_hwm_site_id, 2)."'": "and sf.SITE_ID IS NULL")."
group by st.search_content_id
having (".$query.")
) stt ON sc.id = stt.search_content_id"
: "INNER JOIN b_search_content_stem stt ON sc.id = stt.search_content_id"
)."
WHERE
".CSearch::CheckPermissions("sc.ID")."
".(count($this->Query->m_stemmed_words) > 1? "": "
and stt.language_id = '".$this->Query->m_lang."'
and stt.stem in (".$strStemList.")
".($this->tf_hwm > 0? "and stt.TF >= ".number_format($this->tf_hwm, 2, ".", ""): "")."")."
".$strSqlWhere."
";
}
else
{
if (count($this->Query->m_stemmed_words) > 1)
{
if ($bWordPos)
$arSelect["RANK"] = "if(STDDEV(st.PS)-".$this->normdev(count($this->Query->m_stemmed_words))." between -0.000001 and 1, 1/STDDEV(st.PS), 0) + sum(st.TF/sf.FREQ) as `RANK`";
else
$arSelect["RANK"] = "sum(st.TF/sf.FREQ) as `RANK`";
}
else
{
$arSelect["RANK"] = "st.TF as `RANK`";
}
$strSql = "
FROM b_search_content sc
".($this->Query->bText? "INNER JOIN b_search_content_text sct ON sct.SEARCH_CONTENT_ID = sc.ID": "")."
INNER JOIN b_search_content_site scsite ON sc.ID=scsite.SEARCH_CONTENT_ID
INNER JOIN b_search_content_stem st ON sc.id = st.search_content_id+0
".(count($this->Query->m_stemmed_words) > 1?
"INNER JOIN b_search_content_freq sf ON
st.language_id = sf.language_id
and st.stem=sf.stem
".($this->tf_hwm_site_id <> ''?
"and sf.SITE_ID = '".$DB->ForSQL($this->tf_hwm_site_id, 2)."'":
"and sf.SITE_ID IS NULL"
):
""
)."
WHERE
".CSearch::CheckPermissions("sc.ID")."
AND st.STEM in (".$strStemList.")
".(count($this->Query->m_stemmed_words) > 1? "AND sf.STEM in (".$strStemList.")": "")."
AND st.language_id='".$this->Query->m_lang."'
".$strSqlWhere."
GROUP BY
sc.ID
,scsite.URL
,scsite.SITE_ID
HAVING
(".$query.")
";
}
}
elseif ($bIncSites && !$bStem)
{
$bDistinct = true;
$arSelect["SITE_URL"] = "scsite.URL as SITE_URL";
$arSelect["SITE_ID"] = "scsite.SITE_ID";
$arSelect["RANK"] = "1 as `RANK`";
if ($this->Query->bTagsSearch)
{
$strSql = "
FROM b_search_content sc
".($this->Query->bText? "INNER JOIN b_search_content_text sct ON sct.SEARCH_CONTENT_ID = sc.ID": "")."
INNER JOIN b_search_content_site scsite ON sc.ID=scsite.SEARCH_CONTENT_ID
INNER JOIN b_search_tags stags ON (sc.ID = stags.SEARCH_CONTENT_ID)
WHERE
".CSearch::CheckPermissions("sc.ID")."
".$strSqlWhere."
".(is_array($this->Query->m_tags_words) && count($this->Query->m_tags_words) > 0? "AND stags.NAME in ('".implode("','", $this->Query->m_tags_words)."')": "")."
GROUP BY
sc.ID
,scsite.URL
,scsite.SITE_ID
HAVING
".$query."
";
}
else
{
$strSql = "
FROM
".($this->Query->bText? "
b_search_content_text sct
INNER JOIN b_search_content sc ON sc.ID = sct.SEARCH_CONTENT_ID
INNER JOIN b_search_content_site scsite ON sc.ID = scsite.SEARCH_CONTENT_ID
": "
b_search_content sc
INNER JOIN b_search_content_site scsite ON sc.ID = scsite.SEARCH_CONTENT_ID
")."
WHERE
".CSearch::CheckPermissions("sc.ID")."
AND (".$query.")
".$strSqlWhere."
";
}
}
elseif (!$bIncSites && $bStem)
{
if (BX_SEARCH_VERSION <= 1)
$arSelect["SITE_ID"] = "sc.LID as SITE_ID";
if (count($this->Query->m_stemmed_words) > 1)
{
if ($bWordPos)
$arSelect["RANK"] = "if(STDDEV(st.PS)-".$this->normdev(count($this->Query->m_stemmed_words))." between -0.000001 and 1, 1/STDDEV(st.PS), 0) + sum(st.TF/sf.FREQ) as `RANK`";
else
$arSelect["RANK"] = "sum(st.TF/sf.FREQ) as `RANK`";
}
else
{
$arSelect["RANK"] = "st.TF as `RANK`";
}
$strSql = "
FROM b_search_content sc
".($this->Query->bText? "INNER JOIN b_search_content_text sct ON sct.SEARCH_CONTENT_ID = sc.ID": "")."
INNER JOIN b_search_content_stem st ON sc.id = st.search_content_id
".(count($this->Query->m_stemmed_words) > 1?
"INNER JOIN b_search_content_freq sf ON
st.language_id = sf.language_id
and st.stem=sf.stem
".($this->tf_hwm_site_id <> ''?
"and sf.SITE_ID = '".$DB->ForSQL($this->tf_hwm_site_id, 2)."'":
"and sf.SITE_ID IS NULL"
):
""
)."
WHERE
".CSearch::CheckPermissions("sc.ID")."
AND st.STEM in (".$strStemList.")
".(count($this->Query->m_stemmed_words) > 1? "AND sf.STEM in (".$strStemList.")": "")."
AND st.language_id='".$this->Query->m_lang."'
".$strSqlWhere."
".(count($this->Query->m_stemmed_words) > 1? "
GROUP BY
sc.ID
HAVING
(".$query.") ": "")."
";
}
else //if(!$bIncSites && !$bStem)
{
$bDistinct = true;
if (BX_SEARCH_VERSION <= 1)
$arSelect["SITE_ID"] = "sc.LID as SITE_ID";
$arSelect["RANK"] = "1 as `RANK`";
$strSql = "
FROM b_search_content sc
".($this->Query->bText? "INNER JOIN b_search_content_text sct ON sct.SEARCH_CONTENT_ID = sc.ID": "")."
".($this->Query->bTagsSearch? "INNER JOIN b_search_tags stags ON (sc.ID = stags.SEARCH_CONTENT_ID)
WHERE
".CSearch::CheckPermissions("sc.ID")."
".$strSqlWhere."
".(is_array($this->Query->m_tags_words) && count($this->Query->m_tags_words) > 0? "AND stags.NAME in ('".implode("','", $this->Query->m_tags_words)."')": "")."
GROUP BY
sc.ID
HAVING
(".$query.")":
" WHERE
(".$query.")
".$strSqlWhere."
")."
";
}
if ($this->offset === false)
{
$limit = $this->limit;
}
else
{
$limit = $this->offset.", ".$this->limit;
}
$strRatingJoin = "";
$RATING_MAX = 0;
$RATING_MIN = 0;
if (
($this->flagsUseRatingSort & 0x01)
&& COption::GetOptionString("search", "use_social_rating") == "Y"
&& BX_SEARCH_VERSION == 2
&& COption::GetOptionString("search", "dbnode_id") <= 0
)
{
$rsMinMax = $DB->Query("select max(TOTAL_VALUE) RATING_MAX, min(TOTAL_VALUE) RATING_MIN from b_rating_voting");
$arMinMax = $rsMinMax->Fetch();
if ($arMinMax)
{
$RATING_MAX = doubleval($arMinMax["RATING_MAX"]);
if ($RATING_MAX < 0)
$RATING_MAX = 0;
$RATING_MIN = doubleval($arMinMax["RATING_MIN"]);
if ($RATING_MIN > 0)
$RATING_MIN = 0;
}
if ($RATING_MAX != 0 || $RATING_MIN != 0)
{
$arSelectOuter = array();
$arSelectOuterFields = array(
"BODY",
);
foreach ($arSelectOuterFields as $outerField)
{
if (isset($arSelect[$outerField]))
$arSelectOuter[$outerField] = $arSelect[$outerField];
unset($arSelect[$outerField]);
}
$strSelectOuter = "SELECT sc0.*".($arSelectOuter? ", ".implode(", ", $arSelectOuter): "");
$strSelectInner = "SELECT ".($bDistinct? "DISTINCT": "")."n".implode("n,", $arSelect);
return "
".$strSelectOuter.", sc0.`RANK` +
if(rv.TOTAL_VALUE > 0, ".($RATING_MAX > 0? "rv.TOTAL_VALUE/".$RATING_MAX: "0").",
if(rv.TOTAL_VALUE < 0, ".($RATING_MIN < 0? "rv.TOTAL_VALUE/".abs($RATING_MIN): "0").",
0
)) SRANK
,".$DB->IsNull('rvv.VALUE', '0')." RATING_USER_VOTE_VALUE
,sc.ENTITY_TYPE_ID RATING_TYPE_ID
,sc.ENTITY_ID RATING_ENTITY_ID
,rv.TOTAL_VOTES RATING_TOTAL_VOTES
,rv.TOTAL_POSITIVE_VOTES RATING_TOTAL_POSITIVE_VOTES
,rv.TOTAL_NEGATIVE_VOTES RATING_TOTAL_NEGATIVE_VOTES
,rv.TOTAL_VALUE RATING_TOTAL_VALUE
FROM (
".$strSelectInner."
".$strSql.$strSort."nLIMIT ".$limit."
) sc0
INNER JOIN b_search_content sc ON sc.ID = sc0.ID
LEFT JOIN b_rating_voting rv ON rv.ENTITY_TYPE_ID = sc.ENTITY_TYPE_ID AND rv.ENTITY_ID = sc.ENTITY_ID
LEFT JOIN b_rating_vote rvv ON rvv.ENTITY_TYPE_ID = sc.ENTITY_TYPE_ID AND rvv.ENTITY_ID = sc.ENTITY_ID AND rvv.USER_ID = ".intval($USER->GetId())."
".str_replace(" `RANK`", " SRANK", $strSort);
}
}
$strSelect = "SELECT ".($bDistinct? "DISTINCT": "")."n".implode("n,", $arSelect);
return $strSelect."n".$strSql.$strSort."nLIMIT ".$limit;
}