public static function GetList($arOrder = Array(), $arFilter = Array(), $bCnt = false)
{
global $DB;
if(strtoupper($DB->type)=="MYSQL")
$sum = "IF(NEW_MESSAGE='Y', 1, 0)";
else
$sum = "case when NEW_MESSAGE='Y' then 1 else 0 end";
$strSql =
"SELECT ".
($bCnt?
"COUNT('x') as CNT, SUM(".$sum.") as CNT_NEW, COUNT('x')-SUM(".$sum.") as CNT_OLD "
:
"MS.*, MB.NAME as MAILBOX_NAME, MB.LID, ".
" ".$DB->DateToCharFunction("MS.DATE_INSERT")." as DATE_INSERT, ".
" ".$DB->DateToCharFunction("MS.FIELD_DATE")." as FIELD_DATE "
).
"FROM b_mail_message MS ".
($bCnt? "":" INNER JOIN b_mail_mailbox MB ON MS.MAILBOX_ID=MB.ID ");
$arSqlSearch = Array();
$filter_keys = array_keys($arFilter);
for($i = 0, $n = count($filter_keys); $i < $n; $i++)
{
$key = $filter_keys[$i];
$val = $arFilter[$key];
$res = CMailUtil::MkOperationFilter($key);
$key = strtoupper($res["FIELD"]);
$cOperationType = $res["OPERATION"];
if($cOperationType == "?")
{
if (strlen($val)<=0) continue;
switch($key)
{
case "ID":
case "MAILBOX_ID":
case "MSGUID":
$arSqlSearch[] = GetFilterQuery("MS.".$key, $val, "N");
break;
case "FIELD_FROM":
case "FIELD_TO":
case "FIELD_CC":
case "FIELD_BCC":
$arSqlSearch[] = GetFilterQuery("MS.".$key, $val, "Y", Array("@", "_", ".", "-"));
break;
case "NEW_MESSAGE":
case "SUBJECT":
case "HEADER":
case "MSG_ID":
case "IN_REPLY_TO":
case "BODY":
$arSqlSearch[] = GetFilterQuery("MS.".$key, $val);
break;
case "SENDER":
$arSqlSearch[] = GetFilterQuery("MS.FIELD_FROM", $val, "Y", array("@","_",".","-"));
break;
case "RECIPIENT":
$arSqlSearch[] = GetFilterQuery("MS.FIELD_TO, MS.FIELD_CC, MS.FIELD_BCC", $val, "Y", array("@","_",".","-"));
break;
case "SPAM_RATING":
CMailFilter::RecalcSpamRating();
$arSqlSearch[] = GetFilterQuery("MS.SPAM_RATING", $val, "N");
break;
case "SPAM":
$arSqlSearch[] = GetFilterQuery("MS.SPAM", $val, "Y", array("?"));
break;
case "ALL":
$arSqlSearch[] = GetFilterQuery("MS.HEADER, MS.BODY", $val);
break;
}
}
else
{
switch($key)
{
case "SPAM":
case "NEW_MESSAGE":
$arSqlSearch[] = CMailUtil::FilterCreate("MS.".$key, $val, "string_equal", $cOperationType);
break;
case "ID":
case "MAILBOX_ID":
$arSqlSearch[] = CMailUtil::FilterCreate("MS.".$key, $val, "number", $cOperationType);
break;
case "SUBJECT":
case "HEADER":
case "BODY":
case "MSGUID":
case "FIELD_FROM":
case "FIELD_TO":
case "FIELD_CC":
case "MSG_ID":
case "IN_REPLY_TO":
case "FIELD_BCC":
$arSqlSearch[] = CMailUtil::FilterCreate("MS.".$key, $val, "string", $cOperationType);
break;
case "SPAM_RATING":
$arSqlSearch[] = CMailUtil::FilterCreate("MS.".$key, $val, "number", $cOperationType);
CMailFilter::RecalcSpamRating();
break;
/*
case "TIMESTAMP_X":
$arSqlSearch[] = CIBlock::FilterCreate("BE.TIMESTAMP_X", $val, "date", $cOperationType);
break;
*/
}
}
}
$is_filtered = false;
$strSqlSearch = "";
for($i = 0, $n = count($arSqlSearch); $i < $n; $i++)
{
if(strlen($arSqlSearch[$i])>0)
{
$strSqlSearch .= " AND (".$arSqlSearch[$i].") ";
$is_filtered = true;
}
}
$arSqlOrder = Array();
foreach($arOrder as $by=>$order)
{
$by = strtolower($by);
$order = strtolower($order);
if ($order!="asc")
$order = "desc".(strtoupper($DB->type)=="ORACLE"?" NULLS LAST":"");
else
$order = "asc".(strtoupper($DB->type)=="ORACLE"?" NULLS FIRST":"");
if ($by == "field_date") $arSqlOrder[] = " MS.FIELD_DATE ".$order." ";
elseif ($by == "field_from") $arSqlOrder[] = " MS.FIELD_FROM ".$order." ";
elseif ($by == "field_reply_to")$arSqlOrder[] = " MS.FIELD_REPLY_TO ".$order." ";
elseif ($by == "field_to") $arSqlOrder[] = " MS.FIELD_TO ".$order." ";
elseif ($by == "field_cc") $arSqlOrder[] = " MS.FIELD_CC ".$order." ";
elseif ($by == "field_bcc") $arSqlOrder[] = " MS.FIELD_BCC ".$order." ";
elseif ($by == "subject") $arSqlOrder[] = " MS.SUBJECT ".$order." ";
elseif ($by == "attachments") $arSqlOrder[] = " MS.ATTACHMENTS ".$order." ";
elseif ($by == "date_insert") $arSqlOrder[] = " MS.DATE_INSERT ".$order." ";
elseif ($by == "msguid") $arSqlOrder[] = " MS.MSGUID ".$order." ";
elseif ($by == "mailbox_id") $arSqlOrder[] = " MS.MAILBOX_ID ".$order." ";
elseif ($by == "new_message") $arSqlOrder[] = " MS.NEW_MESSAGE ".$order." ";
elseif ($by == "mailbox_name" && !$bCnt) $arSqlOrder[] = " MB.NAME ".$order." ";
elseif ($by == "spam_rating") {$arSqlOrder[] = " MS.SPAM_RATING ".$order." "; CMailFilter::RecalcSpamRating();}
else $arSqlOrder[] = " MS.ID ".$order." ";
}
$strSqlOrder = "";
$arSqlOrder = array_unique($arSqlOrder);
DelDuplicateSort($arSqlOrder);
for ($i = 0, $n = count($arSqlOrder); $i < $n; $i++)
{
if($i==0)
$strSqlOrder = " ORDER BY ";
else
$strSqlOrder .= ",";
$strSqlOrder .= $arSqlOrder[$i];
}
$strSql .= " WHERE 1=1 ".$strSqlSearch.$strSqlOrder;
$dbr = $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__);
$dbr->is_filtered = $is_filtered;
return $dbr;
}
