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; }