function prepareSql($arSelectFields=array(), $arFilter=array(), $arGroupBy=false, $arOrder=array("SORT"=>"ASC"))
{
global $DB;
$connection = BitrixMainApplication::getConnection();
$helper = $connection->getSqlHelper();
$MAX_LOCK = intval(COption::GetOptionString("workflow","MAX_LOCK_TIME","60"));
$uid = $this->userId;
$formatActiveDates = CPageOption::GetOptionString("iblock", "FORMAT_ACTIVE_DATES", "-") != "-";
$shortFormatActiveDates = CPageOption::GetOptionString("iblock", "FORMAT_ACTIVE_DATES", "SHORT");
$arIblockElementFields = array(
"ID"=>"BE.ID",
"TIMESTAMP_X"=>$DB->DateToCharFunction("BE.TIMESTAMP_X"),
"TIMESTAMP_X_UNIX"=>'UNIX_TIMESTAMP(BE.TIMESTAMP_X)',
"MODIFIED_BY"=>"BE.MODIFIED_BY",
"DATE_CREATE"=>$DB->DateToCharFunction("BE.DATE_CREATE"),
"DATE_CREATE_UNIX"=>'UNIX_TIMESTAMP(BE.DATE_CREATE)',
"CREATED_BY"=>"BE.CREATED_BY",
"IBLOCK_ID"=>"BE.IBLOCK_ID",
"IBLOCK_SECTION_ID"=>"BE.IBLOCK_SECTION_ID",
"ACTIVE"=>"BE.ACTIVE",
"ACTIVE_FROM"=>(
$formatActiveDates
?
$DB->DateToCharFunction("BE.ACTIVE_FROM", $shortFormatActiveDates)
:
"BE.ACTIVE_FROM as ACTIVE_FROM_X, case when EXTRACT(HOUR FROM BE.ACTIVE_FROM) > 0 OR EXTRACT(MINUTE FROM BE.ACTIVE_FROM) > 0 OR EXTRACT(SECOND FROM BE.ACTIVE_FROM) > 0 then ".$DB->DateToCharFunction("BE.ACTIVE_FROM", "FULL")." else ".$DB->DateToCharFunction("BE.ACTIVE_FROM", "SHORT")." end"
),
"ACTIVE_TO"=>(
$formatActiveDates
?
$DB->DateToCharFunction("BE.ACTIVE_TO", $shortFormatActiveDates)
:
"case when EXTRACT(HOUR FROM BE.ACTIVE_TO) > 0 OR EXTRACT(MINUTE FROM BE.ACTIVE_TO) > 0 OR EXTRACT(SECOND FROM BE.ACTIVE_TO) > 0 then ".$DB->DateToCharFunction("BE.ACTIVE_TO", "FULL")." else ".$DB->DateToCharFunction("BE.ACTIVE_TO", "SHORT")." end"
),
"DATE_ACTIVE_FROM"=>(
$formatActiveDates
?
$DB->DateToCharFunction("BE.ACTIVE_FROM", $shortFormatActiveDates)
:
"case when EXTRACT(HOUR FROM BE.ACTIVE_FROM) > 0 OR EXTRACT(MINUTE FROM BE.ACTIVE_FROM) > 0 OR EXTRACT(SECOND FROM BE.ACTIVE_FROM) > 0 then ".$DB->DateToCharFunction("BE.ACTIVE_FROM", "FULL")." else ".$DB->DateToCharFunction("BE.ACTIVE_FROM", "SHORT")." end"
),
"DATE_ACTIVE_TO"=>(
$formatActiveDates
?
$DB->DateToCharFunction("BE.ACTIVE_TO", $shortFormatActiveDates)
:
"case when EXTRACT(HOUR FROM BE.ACTIVE_TO) > 0 OR EXTRACT(MINUTE FROM BE.ACTIVE_TO) > 0 OR EXTRACT(SECOND FROM BE.ACTIVE_TO) > 0 then ".$DB->DateToCharFunction("BE.ACTIVE_TO", "FULL")." else ".$DB->DateToCharFunction("BE.ACTIVE_TO", "SHORT")." end"
),
"SORT"=>"BE.SORT",
"NAME"=>"BE.NAME",
"PREVIEW_PICTURE"=>"BE.PREVIEW_PICTURE",
"PREVIEW_TEXT"=>"BE.PREVIEW_TEXT",
"PREVIEW_TEXT_TYPE"=>"BE.PREVIEW_TEXT_TYPE",
"DETAIL_PICTURE"=>"BE.DETAIL_PICTURE",
"DETAIL_TEXT"=>"BE.DETAIL_TEXT",
"DETAIL_TEXT_TYPE"=>"BE.DETAIL_TEXT_TYPE",
"SEARCHABLE_CONTENT"=>"BE.SEARCHABLE_CONTENT",
"WF_STATUS_ID"=>"BE.WF_STATUS_ID",
"WF_PARENT_ELEMENT_ID"=>"BE.WF_PARENT_ELEMENT_ID",
"WF_LAST_HISTORY_ID"=>"BE.WF_LAST_HISTORY_ID",
"WF_NEW"=>"BE.WF_NEW",
"LOCK_STATUS"=>"case when BE.WF_DATE_LOCK is null then 'green' when " . $helper->addSecondsToDateTime($MAX_LOCK * 60, 'BE.WF_DATE_LOCK') . " < " . $helper->getCurrentDateTimeFunction() . " then 'green' when BE.WF_LOCKED_BY = " . $uid . " then 'yellow' else 'red' end",
"WF_LOCKED_BY"=>"BE.WF_LOCKED_BY",
"WF_DATE_LOCK"=>$DB->DateToCharFunction("BE.WF_DATE_LOCK"),
"WF_COMMENTS"=>"BE.WF_COMMENTS",
"IN_SECTIONS"=>"BE.IN_SECTIONS",
"SHOW_COUNTER"=>"BE.SHOW_COUNTER",
"SHOW_COUNTER_START"=>$DB->DateToCharFunction("BE.SHOW_COUNTER_START"),
"SHOW_COUNTER_START_X"=>"BE.SHOW_COUNTER_START",
"CODE"=>"BE.CODE",
"TAGS"=>"BE.TAGS",
"XML_ID"=>"BE.XML_ID",
"EXTERNAL_ID"=>"BE.XML_ID",
"TMP_ID"=>"BE.TMP_ID",
"USER_NAME"=>"concat('(',U.LOGIN,') ',coalesce(U.NAME,''),' ',coalesce(U.LAST_NAME,''))",
"LOCKED_USER_NAME"=>"concat('(',UL.LOGIN,') ',coalesce(UL.NAME,''),' ',coalesce(UL.LAST_NAME,''))",
"CREATED_USER_NAME"=>"concat('(',UC.LOGIN,') ',coalesce(UC.NAME,''),' ',coalesce(UC.LAST_NAME,''))",
"LANG_DIR"=>"L.DIR",
"LID"=>"B.LID",
"IBLOCK_TYPE_ID"=>"B.IBLOCK_TYPE_ID",
"IBLOCK_CODE"=>"B.CODE",
"IBLOCK_NAME"=>"B.NAME",
"IBLOCK_EXTERNAL_ID"=>"B.XML_ID",
"DETAIL_PAGE_URL"=>"B.DETAIL_PAGE_URL",
"LIST_PAGE_URL"=>"B.LIST_PAGE_URL",
"CANONICAL_PAGE_URL"=>"B.CANONICAL_PAGE_URL",
"CREATED_DATE"=>$DB->DateFormatToDB("YYYY.MM.DD", "BE.DATE_CREATE"),
"BP_PUBLISHED"=>"case when BE.WF_STATUS_ID = 1 then 'Y' else 'N' end",
);
unset($shortFormatActiveDates);
unset($formatActiveDates);
$this->bDistinct = false;
$this->PrepareGetList(
$arIblockElementFields,
$arJoinProps,
$arSelectFields,
$sSelect,
$arAddSelectFields,
$arFilter,
$sWhere,
$sSectionWhere,
$arAddWhereFields,
$arGroupBy,
$sGroupBy,
$arOrder,
$arSqlOrder,
$arAddOrderByFields
);
$this->arFilterIBlocks = isset($arFilter["IBLOCK_ID"])? array($arFilter["IBLOCK_ID"]): array();
//******************FROM PART********************************************
$sFrom = "";
foreach($arJoinProps["FPS"] as $iblock_id => $iPropCnt)
{
$sFrom .= "tttINNER JOIN b_iblock_element_prop_s".$iblock_id." FPS".$iPropCnt." ON FPS".$iPropCnt.".IBLOCK_ELEMENT_ID = BE.IDn";
$this->arFilterIBlocks[$iblock_id] = $iblock_id;
}
foreach($arJoinProps["FP"] as $propID => $db_prop)
{
$i = $db_prop["CNT"];
if($db_prop["bFullJoin"])
$sFrom .= "tttINNER JOIN b_iblock_property FP".$i." ON FP".$i.".IBLOCK_ID = B.ID AND ".
(
intval($propID)>0?
" FP".$i.".ID=".intval($propID)."n":
" FP".$i.".CODE='".$DB->ForSQL($propID, 200)."'n"
);
else
$sFrom .= "tttLEFT JOIN b_iblock_property FP".$i." ON FP".$i.".IBLOCK_ID = B.ID AND ".
(
intval($propID)>0?
" FP".$i.".ID=".intval($propID)."n":
" FP".$i.".CODE='".$DB->ForSQL($propID, 200)."'n"
);
if (isset($db_prop["IBLOCK_ID"]) && $db_prop["IBLOCK_ID"])
{
$this->arFilterIBlocks[$db_prop["IBLOCK_ID"]] = $db_prop["IBLOCK_ID"];
}
}
foreach($arJoinProps["FPV"] as $propID => $db_prop)
{
$i = $db_prop["CNT"];
if($db_prop["MULTIPLE"]=="Y")
$this->bDistinct = true;
if($db_prop["VERSION"]==2)
$strTable = "b_iblock_element_prop_m".$db_prop["IBLOCK_ID"];
else
$strTable = "b_iblock_element_property";
if($db_prop["bFullJoin"])
$sFrom .= "tttINNER JOIN ".$strTable." FPV".$i." ON FPV".$i.".IBLOCK_PROPERTY_ID = FP".$db_prop["JOIN"].".ID AND FPV".$i.".IBLOCK_ELEMENT_ID = BE.IDn";
else
$sFrom .= "tttLEFT JOIN ".$strTable." FPV".$i." ON FPV".$i.".IBLOCK_PROPERTY_ID = FP".$db_prop["JOIN"].".ID AND FPV".$i.".IBLOCK_ELEMENT_ID = BE.IDn";
if (isset($db_prop["IBLOCK_ID"]) && $db_prop["IBLOCK_ID"])
{
$this->arFilterIBlocks[$db_prop["IBLOCK_ID"]] = $db_prop["IBLOCK_ID"];
}
}
foreach($arJoinProps["FPEN"] as $propID => $db_prop)
{
$i = $db_prop["CNT"];
if($db_prop["VERSION"] == 2 && $db_prop["MULTIPLE"] == "N")
{
if($db_prop["bFullJoin"])
$sFrom .= "tttINNER JOIN b_iblock_property_enum FPEN".$i." ON FPEN".$i.".PROPERTY_ID = ".$db_prop["ORIG_ID"]." AND FPS".$db_prop["JOIN"].".PROPERTY_".$db_prop["ORIG_ID"]." = FPEN".$i.".IDn";
else
$sFrom .= "tttLEFT JOIN b_iblock_property_enum FPEN".$i." ON FPEN".$i.".PROPERTY_ID = ".$db_prop["ORIG_ID"]." AND FPS".$db_prop["JOIN"].".PROPERTY_".$db_prop["ORIG_ID"]." = FPEN".$i.".IDn";
}
else
{
if($db_prop["bFullJoin"])
$sFrom .= "tttINNER JOIN b_iblock_property_enum FPEN".$i." ON FPEN".$i.".PROPERTY_ID = FPV".$db_prop["JOIN"].".IBLOCK_PROPERTY_ID AND FPV".$db_prop["JOIN"].".VALUE_ENUM = FPEN".$i.".IDn";
else
$sFrom .= "tttLEFT JOIN b_iblock_property_enum FPEN".$i." ON FPEN".$i.".PROPERTY_ID = FPV".$db_prop["JOIN"].".IBLOCK_PROPERTY_ID AND FPV".$db_prop["JOIN"].".VALUE_ENUM = FPEN".$i.".IDn";
}
if (isset($db_prop["IBLOCK_ID"]) && $db_prop["IBLOCK_ID"])
{
$this->arFilterIBlocks[$db_prop["IBLOCK_ID"]] = $db_prop["IBLOCK_ID"];
}
}
$showHistory = isset($arFilter["SHOW_HISTORY"]) && $arFilter["SHOW_HISTORY"] === 'Y';
$showNew = isset($arFilter["SHOW_NEW"]) && $arFilter["SHOW_NEW"] === 'Y';
foreach($arJoinProps["BE"] as $propID => $db_prop)
{
$i = $db_prop["CNT"];
$sFrom .= "tttLEFT JOIN b_iblock_element BE".$i." ON BE".$i.".ID = ".
(
$db_prop["VERSION"]==2 && $db_prop["MULTIPLE"]=="N"?
"FPS".$db_prop["JOIN"].".PROPERTY_".$db_prop["ORIG_ID"]
:"FPV".$db_prop["JOIN"].".VALUE_NUM"
).
(
!$showHistory ?
" AND ((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)".($showNew ? " OR BE.WF_NEW='Y'": "").")":
""
)."n";
if($db_prop["bJoinIBlock"])
$sFrom .= "tttLEFT JOIN b_iblock B".$i." ON B".$i.".ID = BE".$i.".IBLOCK_IDn";
if($db_prop["bJoinSection"])
$sFrom .= "tttLEFT JOIN b_iblock_section BS".$i." ON BS".$i.".ID = BE".$i.".IBLOCK_SECTION_IDn";
if (isset($db_prop["IBLOCK_ID"]) && $db_prop["IBLOCK_ID"])
{
$this->arFilterIBlocks[$db_prop["IBLOCK_ID"]] = $db_prop["IBLOCK_ID"];
}
}
foreach($arJoinProps["BE_FPS"] as $iblock_id => $db_prop)
{
[$iblock_id, $link] = explode("~", $iblock_id, 2);
$sFrom .= "tttLEFT JOIN b_iblock_element_prop_s".$iblock_id." JFPS".$db_prop["CNT"]." ON JFPS".$db_prop["CNT"].".IBLOCK_ELEMENT_ID = BE".$db_prop["JOIN"].".IDn";
if (isset($db_prop["IBLOCK_ID"]) && $db_prop["IBLOCK_ID"])
{
$this->arFilterIBlocks[$db_prop["IBLOCK_ID"]] = $db_prop["IBLOCK_ID"];
}
}
foreach($arJoinProps["BE_FP"] as $propID => $db_prop)
{
$i = $db_prop["CNT"];
[$propID, $link] = explode("~", $propID, 2);
if($db_prop["bFullJoin"])
$sFrom .= "tttINNER JOIN b_iblock_property JFP".$i." ON JFP".$i.".IBLOCK_ID = BE".$db_prop["JOIN"].".IBLOCK_ID AND ".
(
intval($propID)>0?
" JFP".$i.".ID=".intval($propID)."n":
" JFP".$i.".CODE='".$DB->ForSQL($propID, 200)."'n"
);
else
$sFrom .= "tttLEFT JOIN b_iblock_property JFP".$i." ON JFP".$i.".IBLOCK_ID = BE".$db_prop["JOIN"].".IBLOCK_ID AND ".
(
intval($propID)>0?
" JFP".$i.".ID=".intval($propID)."n":
" JFP".$i.".CODE='".$DB->ForSQL($propID, 200)."'n"
);
if (isset($db_prop["IBLOCK_ID"]) && $db_prop["IBLOCK_ID"])
{
$this->arFilterIBlocks[$db_prop["IBLOCK_ID"]] = $db_prop["IBLOCK_ID"];
}
}
foreach($arJoinProps["BE_FPV"] as $propID => $db_prop)
{
$i = $db_prop["CNT"];
[$propID, $link] = explode("~", $propID, 2);
if($db_prop["MULTIPLE"]=="Y")
$this->bDistinct = true;
if($db_prop["VERSION"]==2)
$strTable = "b_iblock_element_prop_m".$db_prop["IBLOCK_ID"];
else
$strTable = "b_iblock_element_property";
if($db_prop["bFullJoin"])
$sFrom .= "tttINNER JOIN ".$strTable." JFPV".$i." ON JFPV".$i.".IBLOCK_PROPERTY_ID = JFP".$db_prop["JOIN"].".ID AND JFPV".$i.".IBLOCK_ELEMENT_ID = BE".$db_prop["BE_JOIN"].".IDn";
else
$sFrom .= "tttLEFT JOIN ".$strTable." JFPV".$i." ON JFPV".$i.".IBLOCK_PROPERTY_ID = JFP".$db_prop["JOIN"].".ID AND JFPV".$i.".IBLOCK_ELEMENT_ID = BE".$db_prop["BE_JOIN"].".IDn";
if (isset($db_prop["IBLOCK_ID"]) && $db_prop["IBLOCK_ID"])
{
$this->arFilterIBlocks[$db_prop["IBLOCK_ID"]] = $db_prop["IBLOCK_ID"];
}
}
foreach($arJoinProps["BE_FPEN"] as $propID => $db_prop)
{
$i = $db_prop["CNT"];
[$propID, $link] = explode("~", $propID, 2);
if($db_prop["VERSION"] == 2 && $db_prop["MULTIPLE"] == "N")
{
if($db_prop["bFullJoin"])
$sFrom .= "tttINNER JOIN b_iblock_property_enum JFPEN".$i." ON JFPEN".$i.".PROPERTY_ID = ".$db_prop["ORIG_ID"]." AND JFPS".$db_prop["JOIN"].".PROPERTY_".$db_prop["ORIG_ID"]." = JFPEN".$i.".IDn";
else
$sFrom .= "tttLEFT JOIN b_iblock_property_enum JFPEN".$i." ON JFPEN".$i.".PROPERTY_ID = ".$db_prop["ORIG_ID"]." AND JFPS".$db_prop["JOIN"].".PROPERTY_".$db_prop["ORIG_ID"]." = JFPEN".$i.".IDn";
}
else
{
if($db_prop["bFullJoin"])
$sFrom .= "tttINNER JOIN b_iblock_property_enum JFPEN".$i." ON JFPEN".$i.".PROPERTY_ID = JFPV".$db_prop["JOIN"].".IBLOCK_PROPERTY_ID AND JFPV".$db_prop["JOIN"].".VALUE_ENUM = JFPEN".$i.".IDn";
else
$sFrom .= "tttLEFT JOIN b_iblock_property_enum JFPEN".$i." ON JFPEN".$i.".PROPERTY_ID = JFPV".$db_prop["JOIN"].".IBLOCK_PROPERTY_ID AND JFPV".$db_prop["JOIN"].".VALUE_ENUM = JFPEN".$i.".IDn";
}
if (isset($db_prop["IBLOCK_ID"]) && $db_prop["IBLOCK_ID"])
{
$this->arFilterIBlocks[$db_prop["IBLOCK_ID"]] = $db_prop["IBLOCK_ID"];
}
}
if($arJoinProps["BES"] <> '')
{
$sFrom .= "ttt".$arJoinProps["BES"]."n";
}
if($arJoinProps["FC"] <> '')
{
$sFrom .= "ttt".$arJoinProps["FC"]."n";
$this->bDistinct = $this->bDistinct || (isset($arJoinProps["FC_DISTINCT"]) && $arJoinProps["FC_DISTINCT"] == "Y");
}
if($arJoinProps["RV"])
$sFrom .= "tttLEFT JOIN b_rating_voting RV ON RV.ENTITY_TYPE_ID = 'IBLOCK_ELEMENT' AND RV.ENTITY_ID = BE.IDn";
if($arJoinProps["RVU"])
$sFrom .= "tttLEFT JOIN b_rating_vote RVU ON RVU.ENTITY_TYPE_ID = 'IBLOCK_ELEMENT' AND RVU.ENTITY_ID = BE.ID AND RVU.USER_ID = ".$uid."n";
if($arJoinProps["RVV"])
$sFrom .= "ttt".($arJoinProps["RVV"]["bFullJoin"]? "INNER": "LEFT")." JOIN b_rating_vote RVV ON RVV.ENTITY_TYPE_ID = 'IBLOCK_ELEMENT' AND RVV.ENTITY_ID = BE.IDn";
//******************END OF FROM PART********************************************
$this->bCatalogSort = false;
if(!empty($arAddSelectFields) || !empty($arAddWhereFields) || !empty($arAddOrderByFields))
{
if (Loader::includeModule("catalog"))
{
$catalogQueryResult = CProductQueryBuilder::makeQuery(array(
'select' => $arAddSelectFields,
'filter' => $arAddWhereFields,
'order' => $arAddOrderByFields
));
if (!empty($catalogQueryResult))
{
if (
!empty($catalogQueryResult['select'])
&& $sGroupBy==""
&& !$this->bOnlyCount
&& !isset($this->strField)
)
{
$sSelect .= ', '.implode(', ', $catalogQueryResult['select']).' ';
}
// filter set in CIBlockElement::MkFilter
if (!empty($catalogQueryResult['order']))
{
$this->bCatalogSort = true;
foreach ($catalogQueryResult['order'] as $index => $field)
$arSqlOrder[$index] = $field;
unset($field);
unset($index);
}
if (!empty($catalogQueryResult['join']))
{
$sFrom .= "nttt".implode("nttt", $catalogQueryResult['join'])."n";
}
}
unset($catalogQueryResult);
}
}
$i = array_search("CREATED_BY_FORMATTED", $arSelectFields);
if ($i !== false)
{
if (
$sSelect
&& $sGroupBy==""
&& !$this->bOnlyCount
&& !isset($this->strField)
)
{
$sSelect .= ",UC.NAME UC_NAME, UC.LAST_NAME UC_LAST_NAME, UC.SECOND_NAME UC_SECOND_NAME, UC.EMAIL UC_EMAIL, UC.ID UC_ID, UC.LOGIN UC_LOGIN";
}
else
{
unset($arSelectFields[$i]);
}
}
$sOrderBy = "";
foreach($arSqlOrder as $i=>$val)
{
if($val <> '')
{
if($sOrderBy == "")
{
$sOrderBy = " ORDER BY ";
}
else
{
$sOrderBy .= ",";
}
$sOrderBy .= $val." ";
}
}
$sSelect = trim($sSelect, ", tnr");
if($sSelect == '')
$sSelect = "0 as NOP ";
$this->bDistinct = $this->bDistinct || (isset($arFilter["INCLUDE_SUBSECTIONS"]) && $arFilter["INCLUDE_SUBSECTIONS"] == "Y");
if($this->bDistinct)
$sSelect = str_replace("%%_DISTINCT_%%", "DISTINCT", $sSelect);
else
$sSelect = str_replace("%%_DISTINCT_%%", "", $sSelect);
$sFrom = "
b_iblock B
INNER JOIN b_lang L ON B.LID=L.LID
INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
".ltrim($sFrom, "tn")
.(in_array("USER_NAME", $arSelectFields)? "tttLEFT JOIN b_user U ON U.ID=BE.MODIFIED_BYn": "")
.(in_array("LOCKED_USER_NAME", $arSelectFields)? "tttLEFT JOIN b_user UL ON UL.ID=BE.WF_LOCKED_BYn": "")
.(in_array("CREATED_USER_NAME", $arSelectFields) || in_array("CREATED_BY_FORMATTED", $arSelectFields)? "tttLEFT JOIN b_user UC ON UC.ID=BE.CREATED_BYn": "")."
";
$this->sSelect = $sSelect;
$this->sFrom = $sFrom;
$this->sWhere = $sWhere;
$this->sGroupBy = $sGroupBy;
$this->sOrderBy = $sOrderBy;
}