CBPHelper::PrepareSql: Bitrix method

      
<?php 
//  CBPHelper :: PrepareSql()
//  /dist/bitrix/modules/bizproc/classes/general/helper.php:449

    
public static function PrepareSql(&$arFields$arOrder$arFilter$arGroupBy$arSelectFields)
    {
        global 
$DB;

        
$strSqlSelect "";
        
$strSqlFrom "";
        
$strSqlWhere "";
        
$strSqlGroupBy "";
        
$strSqlOrderBy "";

        
$arOrder array_change_key_case($arOrderCASE_UPPER);

        
$arGroupByFunct = array("COUNT""AVG""MIN""MAX""SUM");

        
$arAlreadyJoined = [];

        
// GROUP BY -->
        
if (is_array($arGroupBy) && count($arGroupBy)>0)
        {
            
$arSelectFields $arGroupBy;
            foreach (
$arGroupBy as $key => $val)
            {
                
$val strtoupper($val);
                
$key strtoupper($key);
                if (
array_key_exists($val$arFields) && !in_array($key$arGroupByFunct))
                {
                    if (
strlen($strSqlGroupBy) > 0)
                        
$strSqlGroupBy .= ", ";
                    
$strSqlGroupBy .= $arFields[$val]["FIELD"];

                    if (!empty(
$arFields[$val]["FROM"]))
                    {
                        
$toJoin = (array)$arFields[$val]["FROM"];
                        foreach (
$toJoin as $join)
                        {
                            if (
in_array($join$arAlreadyJoined))
                            {
                                continue;
                            }
                            if (
strlen($strSqlFrom) > 0)
                            {
                                
$strSqlFrom .= " ";
                            }
                            
$strSqlFrom .= $join;
                            
$arAlreadyJoined[] = $join;
                        }
                    }
                }
            }
        }
        
// <-- GROUP BY

        // SELECT -->
        
$arFieldsKeys array_keys($arFields);

        if (
is_array($arGroupBy) && count($arGroupBy)==0)
        {
            
$strSqlSelect "COUNT(%%_DISTINCT_%% ".$arFields[$arFieldsKeys[0]]["FIELD"].") as CNT ";
        }
        else
        {
            if (isset(
$arSelectFields) && !is_array($arSelectFields) && is_string($arSelectFields) && strlen($arSelectFields)>&& array_key_exists($arSelectFields$arFields))
                
$arSelectFields = array($arSelectFields);

            if (!isset(
$arSelectFields)
                || !
is_array($arSelectFields)
                || 
count($arSelectFields)<=0
                
|| in_array("*"$arSelectFields))
            {
                for (
$i 0$cnt count($arFieldsKeys); $i $cnt$i++)
                {
                    if (isset(
$arFields[$arFieldsKeys[$i]]["WHERE_ONLY"])
                        && 
$arFields[$arFieldsKeys[$i]]["WHERE_ONLY"] == "Y")
                    {
                        continue;
                    }

                    if (
strlen($strSqlSelect) > 0)
                        
$strSqlSelect .= ", ";

                    if (
$arFields[$arFieldsKeys[$i]]["TYPE"] == "datetime")
                    {
                        if (
array_key_exists($arFieldsKeys[$i], $arOrder))
                            
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"]." as ".$arFieldsKeys[$i]."_X1, ";

                        
$strSqlSelect .= $DB->DateToCharFunction($arFields[$arFieldsKeys[$i]]["FIELD"], "FULL")." as ".$arFieldsKeys[$i];
                    }
                    elseif (
$arFields[$arFieldsKeys[$i]]["TYPE"] == "date")
                    {
                        if (
array_key_exists($arFieldsKeys[$i], $arOrder))
                            
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"]." as ".$arFieldsKeys[$i]."_X1, ";

                        
$strSqlSelect .= $DB->DateToCharFunction($arFields[$arFieldsKeys[$i]]["FIELD"], "SHORT")." as ".$arFieldsKeys[$i];
                    }
                    else
                        
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"]." as ".$arFieldsKeys[$i];

                    if (!empty(
$arFields[$arFieldsKeys[$i]]["FROM"]))
                    {
                        
$toJoin = (array)$arFields[$arFieldsKeys[$i]]["FROM"];
                        foreach (
$toJoin as $join)
                        {
                            if (
in_array($join$arAlreadyJoined))
                                continue;
                            if (
strlen($strSqlFrom) > 0)
                                
$strSqlFrom .= " ";
                            
$strSqlFrom .= $join;
                            
$arAlreadyJoined[] = $join;
                        }
                    }
                }
            }
            else
            {
                foreach (
$arOrder as $by => $order)
                {
                    if (
                        isset(
$arFields[$by])
                        && !
in_array($by$arSelectFields)
                        && (
$arFields[$by]["TYPE"] == "date" || $arFields[$by]["TYPE"] == "datetime")
                    )
                        
$arSelectFields[] = $by;
                }

                foreach (
$arSelectFields as $key => $val)
                {
                    
$val strtoupper($val);
                    
$key strtoupper($key);
                    if (
array_key_exists($val$arFields))
                    {
                        if (
strlen($strSqlSelect) > 0)
                            
$strSqlSelect .= ", ";

                        if (
in_array($key$arGroupByFunct))
                        {
                            
$strSqlSelect .= $key."(".$arFields[$val]["FIELD"].") as ".$val;
                        }
                        else
                        {
                            if (
$arFields[$val]["TYPE"] == "datetime")
                            {
                                if (
array_key_exists($val$arOrder))
                                    
$strSqlSelect .= $arFields[$val]["FIELD"]." as ".$val."_X1, ";

                                
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "FULL")." as ".$val;
                            }
                            elseif (
$arFields[$val]["TYPE"] == "date")
                            {
                                if (
array_key_exists($val$arOrder))
                                    
$strSqlSelect .= $arFields[$val]["FIELD"]." as ".$val."_X1, ";

                                
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "SHORT")." as ".$val;
                            }
                            else
                                
$strSqlSelect .= $arFields[$val]["FIELD"]." as ".$val;
                        }
                        if (!empty(
$arFields[$val]["FROM"]))
                        {
                            
$toJoin = (array)$arFields[$val]["FROM"];
                            foreach (
$toJoin as $join)
                            {
                                if (
in_array($join$arAlreadyJoined))
                                    continue;
                                if (
strlen($strSqlFrom) > 0)
                                    
$strSqlFrom .= " ";
                                
$strSqlFrom .= $join;
                                
$arAlreadyJoined[] = $join;
                            }
                        }
                    }
                }
            }

            if (
strlen($strSqlGroupBy) > 0)
            {
                if (
strlen($strSqlSelect) > 0)
                    
$strSqlSelect .= ", ";
                
$strSqlSelect .= "COUNT(%%_DISTINCT_%% ".$arFields[$arFieldsKeys[0]]["FIELD"].") as CNT";
            }
            else
                
$strSqlSelect "%%_DISTINCT_%% ".$strSqlSelect;
        }
        
// <-- SELECT

        // WHERE -->
        
$arSqlSearch = [];

        if (!
is_array($arFilter))
            
$filter_keys = [];
        else
            
$filter_keys array_keys($arFilter);

        for (
$i 0$cnt count($filter_keys); $i $cnt$i++)
        {
            
$vals $arFilter[$filter_keys[$i]];
            if (!
is_array($vals))
                
$vals = array($vals);

            
$key $filter_keys[$i];
            
$key_res CBPHelper::GetFilterOperation($key);
            
$key $key_res["FIELD"];
            
$strNegative $key_res["NEGATIVE"];
            
$strOperation $key_res["OPERATION"];
            
$strOrNull $key_res["OR_NULL"];

            if (
array_key_exists($key$arFields))
            {
                
$arSqlSearch_tmp = array();
                for (
$j 0$cntj count($vals); $j $cntj$j++)
                {
                    
$val $vals[$j];

                    if (isset(
$arFields[$key]["WHERE"]))
                    {
                        
$arSqlSearch_tmp1 call_user_func_array(
                                
$arFields[$key]["WHERE"],
                                array(
$val$key$strOperation$strNegative$arFields[$key]["FIELD"], $arFields$arFilter)
                            );
                        if (
$arSqlSearch_tmp1 !== false)
                            
$arSqlSearch_tmp[] = $arSqlSearch_tmp1;
                    }
                    else
                    {
                        if (
$arFields[$key]["TYPE"] == "int")
                        {
                            if ((
IntVal($val) == 0) && (strpos($strOperation"=") !== False))
                                
$arSqlSearch_tmp[] = "(".$arFields[$key]["FIELD"]." IS ".(($strNegative == "Y") ? "NOT " "")."NULL) ".(($strNegative == "Y") ? "AND" "OR")." ".(($strNegative == "Y") ? "NOT " "")."(".$arFields[$key]["FIELD"]." ".$strOperation." 0)";
                            else
                                
$arSqlSearch_tmp[] = (($strNegative == "Y") ? " ".$arFields[$key]["FIELD"]." IS NULL OR NOT " "")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".IntVal($val)." )";
                        }
                        elseif (
$arFields[$key]["TYPE"] == "double")
                        {
                            
$val str_replace(",""."$val);

                            if ((
DoubleVal($val) == 0) && (strpos($strOperation"=") !== False))
                                
$arSqlSearch_tmp[] = "(".$arFields[$key]["FIELD"]." IS ".(($strNegative == "Y") ? "NOT " "")."NULL) ".(($strNegative == "Y") ? "AND" "OR")." ".(($strNegative == "Y") ? "NOT " "")."(".$arFields[$key]["FIELD"]." ".$strOperation." 0)";
                            else
                                
$arSqlSearch_tmp[] = (($strNegative == "Y") ? " ".$arFields[$key]["FIELD"]." IS NULL OR NOT " "")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".DoubleVal($val)." )";
                        }
                        elseif (
$arFields[$key]["TYPE"] == "string" || $arFields[$key]["TYPE"] == "char")
                        {
                            if (
$strOperation == "QUERY")
                            {
                                
$arSqlSearch_tmp[] = GetFilterQuery($arFields[$key]["FIELD"], $val"Y");
                            }
                            else
                            {
                                if ((
strlen($val) == 0) && (strpos($strOperation"=") !== False))
                                    
$arSqlSearch_tmp[] = "(".$arFields[$key]["FIELD"]." IS ".(($strNegative == "Y") ? "NOT " "")."NULL) ".(($strNegative == "Y") ? "AND NOT" "OR")." (".$DB->Length($arFields[$key]["FIELD"])." <= 0) ".(($strNegative == "Y") ? "AND NOT" "OR")." (".$arFields[$key]["FIELD"]." ".$strOperation." '".$DB->ForSql($val)."' )";
                                else
                                    
$arSqlSearch_tmp[] = (($strNegative == "Y") ? " ".$arFields[$key]["FIELD"]." IS NULL OR NOT " "")."(".$arFields[$key]["FIELD"]." ".$strOperation." '".$DB->ForSql($val)."' )";
                            }
                        }
                        elseif (
$arFields[$key]["TYPE"] == "datetime")
                        {
                            if (
strlen($val) <= 0)
                                
$arSqlSearch_tmp[] = ($strNegative=="Y"?"NOT":"")."(".$arFields[$key]["FIELD"]." IS NULL)";
                            else
                                
$arSqlSearch_tmp[] = ($strNegative=="Y"?" ".$arFields[$key]["FIELD"]." IS NULL OR NOT ":"")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".$DB->CharToDateFunction($DB->ForSql($val), "FULL").")";
                        }
                        elseif (
$arFields[$key]["TYPE"] == "date")
                        {
                            if (
strlen($val) <= 0)
                                
$arSqlSearch_tmp[] = ($strNegative=="Y"?"NOT":"")."(".$arFields[$key]["FIELD"]." IS NULL)";
                            else
                                
$arSqlSearch_tmp[] = ($strNegative=="Y"?" ".$arFields[$key]["FIELD"]." IS NULL OR NOT ":"")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".$DB->CharToDateFunction($DB->ForSql($val), "SHORT").")";
                        }
                    }
                }

                if (!empty(
$arFields[$key]["FROM"]))
                {
                    
$toJoin = (array)$arFields[$key]["FROM"];
                    foreach (
$toJoin as $join)
                    {
                        if (
in_array($join$arAlreadyJoined))
                            continue;
                        if (
strlen($strSqlFrom) > 0)
                            
$strSqlFrom .= " ";
                        
$strSqlFrom .= $join;
                        
$arAlreadyJoined[] = $join;
                    }
                }

                
$strSqlSearch_tmp "";
                for (
$j 0$cntj count($arSqlSearch_tmp); $j $cntj$j++)
                {
                    if (
$j 0)
                        
$strSqlSearch_tmp .= ($strNegative=="Y" " AND " " OR ");
                    
$strSqlSearch_tmp .= "(".$arSqlSearch_tmp[$j].")";
                }
                if (
$strOrNull == "Y")
                {
                    if (
strlen($strSqlSearch_tmp) > 0)
                        
$strSqlSearch_tmp .= ($strNegative=="Y" " AND " " OR ");
                    
$strSqlSearch_tmp .= "(".$arFields[$key]["FIELD"]." IS ".($strNegative=="Y" "NOT " "")."NULL)";

                    if (
strlen($strSqlSearch_tmp) > 0)
                        
$strSqlSearch_tmp .= ($strNegative=="Y" " AND " " OR ");
                    if (
$arFields[$key]["TYPE"] == "int" || $arFields[$key]["TYPE"] == "double")
                        
$strSqlSearch_tmp .= "(".$arFields[$key]["FIELD"]." ".($strNegative=="Y" "<>" "=")." 0)";
                    elseif (
$arFields[$key]["TYPE"] == "string" || $arFields[$key]["TYPE"] == "char")
                        
$strSqlSearch_tmp .= "(".$arFields[$key]["FIELD"]." ".($strNegative=="Y" "<>" "=")." '')";
                }

                if (
$strSqlSearch_tmp != "")
                    
$arSqlSearch[] = "(".$strSqlSearch_tmp.")";
            }
        }

        for (
$i 0$cnt count($arSqlSearch); $i $cnt$i++)
        {
            if (
strlen($strSqlWhere) > 0)
                
$strSqlWhere .= " AND ";
            
$strSqlWhere .= "(".$arSqlSearch[$i].")";
        }
        
// <-- WHERE

        // ORDER BY -->
        
$arSqlOrder = Array();
        foreach (
$arOrder as $by => $order)
        {
            
$by strtoupper($by);
            
$order strtoupper($order);

            if (
$order != "ASC")
                
$order "DESC";
            else
                
$order "ASC";

            if (
array_key_exists($by$arFields))
            {
                if (
$arFields[$by]["TYPE"] == "datetime" || $arFields[$by]["TYPE"] == "date")
                    
$arSqlOrder[] = " ".$by."_X1 ".$order." ";
                else
                    
$arSqlOrder[] = " ".$arFields[$by]["FIELD"]." ".$order." ";

                if (!empty(
$arFields[$by]["FROM"]))
                {
                    
$toJoin = (array)$arFields[$by]["FROM"];
                    foreach (
$toJoin as $join)
                    {
                        if (
in_array($join$arAlreadyJoined))
                            continue;
                        if (
strlen($strSqlFrom) > 0)
                            
$strSqlFrom .= " ";
                        
$strSqlFrom .= $join;
                        
$arAlreadyJoined[] = $join;
                    }
                }
            }
        }

        
$strSqlOrderBy "";
        
DelDuplicateSort($arSqlOrder);
        for (
$i 0$cnt count($arSqlOrder); $i $cnt$i++)
        {
            if (
strlen($strSqlOrderBy) > 0)
                
$strSqlOrderBy .= ", ";

            
$strSqlOrderBy .= $arSqlOrder[$i];
        }
        
// <-- ORDER BY

        
return array(
            
"SELECT" => $strSqlSelect,
            
"FROM" => $strSqlFrom,
            
"WHERE" => $strSqlWhere,
            
"GROUPBY" => $strSqlGroupBy,
            
"ORDERBY" => $strSqlOrderBy
        
);
    }