static function GetList($by = "s_sort", $order = "desc", $arFilter = [], $is_filtered = null, $CHECK_RIGHTS="Y")
{
$err_mess = (CAdvContract::err_mess())."
Function: GetList
Line: ";
global $DB, $USER;
if ($CHECK_RIGHTS=="Y")
{
$USER_ID = intval($USER->GetID());
$isAdmin = CAdvContract::IsAdmin();
$isDemo = CAdvContract::IsDemo();
$isManager = CAdvContract::IsManager();
}
else
{
if (is_object($USER)) $USER_ID = intval($USER->GetID()); else $USER_ID = 0;
$isAdmin = true;
$isDemo = true;
$isManager = true;
}
$arSqlSearch = Array();
$lamp = "
if ((
(C.DATE_SHOW_FROM<=now() or C.DATE_SHOW_FROM is null or length(C.DATE_SHOW_FROM)<=0) and
(C.DATE_SHOW_TO>=now() or C.DATE_SHOW_TO is null or length(C.DATE_SHOW_TO)<=0) and
(ifnull(C.MAX_SHOW_COUNT,0)>ifnull(C.SHOW_COUNT,0) or ifnull(C.MAX_SHOW_COUNT,0)=0) and
(ifnull(C.MAX_CLICK_COUNT,0)>ifnull(C.CLICK_COUNT,0) or ifnull(C.MAX_CLICK_COUNT,0)=0) and
(ifnull(C.MAX_VISITOR_COUNT,0)>ifnull(C.VISITOR_COUNT,0) or ifnull(C.MAX_VISITOR_COUNT,0)=0) and
(C.ACTIVE='Y')
),
'green',
'red')
";
if (CAdvContract::CheckFilter($arFilter))
{
if (is_array($arFilter))
{
$filter_keys = array_keys($arFilter);
for ($i=0, $n = count($filter_keys); $i < $n; $i++)
{
$key = $filter_keys[$i];
$val = $arFilter[$filter_keys[$i]];
if ((string)$val == '' || "$val"=="NOT_REF") continue;
if (is_array($val) && count($val)<=0) continue;
$match_value_set = (in_array($key."_EXACT_MATCH", $filter_keys)) ? true : false;
$key = strtoupper($key);
switch($key)
{
case "ID":
$match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $match_value_set) ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("C.ID",$val,$match);
break;
case "SITE":
if (is_array($val)) $val = implode(" | ", $val);
$match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $match_value_set) ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("CS.SITE_ID", $val, $match);
$left_join = "LEFT JOIN b_adv_contract_2_site CS ON (C.ID = CS.CONTRACT_ID)";
break;
case "DATE_MODIFY_1":
$arSqlSearch[] = "C.DATE_MODIFY>=".$DB->CharToDateFunction($val, "SHORT");
break;
case "DATE_MODIFY_2":
$arSqlSearch[] = "C.DATE_MODIFY<".$DB->CharToDateFunction($val, "SHORT")." + INTERVAL 1 DAY";
break;
case "NAME":
case "DESCRIPTION":
case "ADMIN_COMMENTS":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("C.".$key, $val, $match);
break;
case "LAMP":
$arSqlSearch[] = " ".$lamp." = '".$DB->ForSQL($val)."'";
break;
case "OWNER":
$from = "
INNER JOIN b_user U ON (U.ID = CU.USER_ID)
";
$admin_from_1 = "
INNER JOIN b_adv_contract_2_user CU ON (CU.CONTRACT_ID=C.ID)
";
$admin_from_2 = "
INNER JOIN b_user U ON (U.ID = CU.USER_ID)
";
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("CU.USER_ID, U.LOGIN, U.NAME, U.LAST_NAME", $val, $match);
break;
case "BANNER_COUNT_1":
$arSqlSearch_h[] = "BANNER_COUNT>='".intval($val)."'";
break;
case "BANNER_COUNT_2":
$arSqlSearch_h[] = "BANNER_COUNT<='".intval($val)."'";
break;
case "SHOW_COUNT_1":
$arSqlSearch[] = "C.SHOW_COUNT>='".intval($val)."'";
break;
case "SHOW_COUNT_2":
$arSqlSearch[] = "C.SHOW_COUNT<='".intval($val)."'";
break;
case "EMAIL_COUNT_1":
$arSqlSearch[] = "C.EMAIL_COUNT>='".intval($val)."'";
break;
case "EMAIL_COUNT_2":
$arSqlSearch[] = "C.EMAIL_COUNT<='".intval($val)."'";
break;
case "VISITOR_COUNT_1":
$arSqlSearch[] = "C.VISITOR_COUNT>='".intval($val)."'";
break;
case "VISITOR_COUNT_2":
$arSqlSearch[] = "C.VISITOR_COUNT<='".intval($val)."'";
break;
case "CLICK_COUNT_1":
$arSqlSearch[] = "C.CLICK_COUNT>='".intval($val)."'";
break;
case "CLICK_COUNT_2":
$arSqlSearch[] = "C.CLICK_COUNT<='".intval($val)."'";
break;
case "CTR_1":
$arSqlSearch[] = "if(C.SHOW_COUNT<=0,0,round((C.CLICK_COUNT*100)/C.SHOW_COUNT,2))>='".DoubleVal(str_replace(',', '.', $val))."'";
break;
case "CTR_2":
$arSqlSearch[] = "if(C.SHOW_COUNT<=0,0,round((C.CLICK_COUNT*100)/C.SHOW_COUNT,2))<='".DoubleVal(str_replace(',', '.', $val))."'";
break;
case "USER_PERMISSIONS":
$admin_from_1 = " INNER JOIN b_adv_contract_2_user CU ON (CU.CONTRACT_ID=C.ID) ";
$arSqlSearch[] = GetFilterQuery("CU.PERMISSION", $val, "N");
break;
}
}
}
}
if ($by == "s_id") $strSqlOrder = "ORDER BY C.ID";
elseif ($by == "s_lamp") $strSqlOrder = "ORDER BY LAMP";
elseif ($by == "s_date_modify") $strSqlOrder = "ORDER BY C.DATE_MODIFY";
elseif ($by == "s_name") $strSqlOrder = "ORDER BY C.NAME";
elseif ($by == "s_description") $strSqlOrder = "ORDER BY C.DESCRIPTION";
elseif ($by == "s_modified_by") $strSqlOrder = "ORDER BY C.MODIFIED_BY";
elseif ($by == "s_active") $strSqlOrder = "ORDER BY C.ACTIVE";
elseif ($by == "s_weight") $strSqlOrder = "ORDER BY C.WEIGHT";
elseif ($by == "s_sort") $strSqlOrder = "ORDER BY ifnull(C.SORT,0)";
elseif ($by == "s_banner_count") $strSqlOrder = "ORDER BY BANNER_COUNT";
elseif ($by == "s_ctr") $strSqlOrder = "ORDER BY CTR";
elseif ($by == "s_show_count") $strSqlOrder = "ORDER BY C.SHOW_COUNT";
elseif ($by == "s_max_show_count") $strSqlOrder = "ORDER BY ifnull(C.MAX_SHOW_COUNT,0)";
elseif ($by == "s_click_count") $strSqlOrder = "ORDER BY C.CLICK_COUNT";
elseif ($by == "s_max_click_count") $strSqlOrder = "ORDER BY ifnull(C.MAX_CLICK_COUNT,0)";
elseif ($by == "s_visitor_count") $strSqlOrder = "ORDER BY C.VISITOR_COUNT";
elseif ($by == "s_max_visitor_count") $strSqlOrder = "ORDER BY ifnull(C.MAX_VISITOR_COUNT,0)";
else
{
$strSqlOrder = "ORDER BY ifnull(C.SORT,0)";
}
if ($order != "asc")
{
$strSqlOrder .= " desc ";
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$strSqlSearch_h = GetFilterSqlSearch($arSqlSearch_h);
if ($isAdmin || $isDemo || $isManager)
{
$strSql = "
SELECT
$lamp LAMP,
C.ID, C.ACTIVE, C.NAME, C.DESCRIPTION, C.ADMIN_COMMENTS, C.WEIGHT, C.SORT, C.MAX_SHOW_COUNT, C.SHOW_COUNT, C.EMAIL_COUNT, C.CREATED_BY, C.MODIFIED_BY, C.MAX_CLICK_COUNT, C.CLICK_COUNT, C.DEFAULT_STATUS_SID, C.MAX_VISITOR_COUNT, C.VISITOR_COUNT, C.KEYWORDS,
if(C.SHOW_COUNT<=0,0,round((C.CLICK_COUNT*100)/C.SHOW_COUNT,2)) CTR,
".$DB->DateToCharFunction("C.DATE_SHOW_FROM")." DATE_SHOW_FROM,
".$DB->DateToCharFunction("C.DATE_SHOW_TO")." DATE_SHOW_TO,
".$DB->DateToCharFunction("C.DATE_CREATE")." DATE_CREATE,
".$DB->DateToCharFunction("C.DATE_MODIFY")." DATE_MODIFY,
count(distinct B.ID) BANNER_COUNT
FROM
b_adv_contract C
LEFT JOIN b_adv_banner B ON (B.CONTRACT_ID=C.ID)
$left_join
$admin_from_1
$admin_from_2
WHERE
$strSqlSearch
GROUP BY
C.ID
HAVING
$strSqlSearch_h
$strSqlOrder
";
}
else
{
$strSql = "
SELECT
$lamp LAMP,
C.ID, C.ACTIVE, C.NAME, C.DESCRIPTION, C.ADMIN_COMMENTS, C.WEIGHT, C.SORT, C.MAX_SHOW_COUNT, C.SHOW_COUNT, C.MAX_CLICK_COUNT, C.CLICK_COUNT, C.EMAIL_COUNT, C.CREATED_BY, C.MODIFIED_BY, C.DEFAULT_STATUS_SID, C.MAX_VISITOR_COUNT, C.VISITOR_COUNT, C.KEYWORDS,
if(C.SHOW_COUNT<=0,0,round((C.CLICK_COUNT*100)/C.SHOW_COUNT,2)) CTR,
".$DB->DateToCharFunction("C.DATE_SHOW_FROM")." DATE_SHOW_FROM,
".$DB->DateToCharFunction("C.DATE_SHOW_TO")." DATE_SHOW_TO,
".$DB->DateToCharFunction("C.DATE_CREATE")." DATE_CREATE,
".$DB->DateToCharFunction("C.DATE_MODIFY")." DATE_MODIFY,
count(distinct B.ID) BANNER_COUNT
FROM
b_adv_contract C
LEFT JOIN b_adv_banner B ON (B.CONTRACT_ID=C.ID)
INNER JOIN b_adv_contract_2_user CU ON (CU.CONTRACT_ID=C.ID and CU.USER_ID=$USER_ID)
$left_join
$from
WHERE
$strSqlSearch
GROUP BY
C.ID
HAVING
$strSqlSearch_h
$strSqlOrder
";
}
$res = $DB->Query($strSql, false, $err_mess.__LINE__);
return $res;
}