function GetUserAnswerStat($arSort = array(), $arFilter = array(), $arParams = array())
{
global $DB;
$err_mess = CAllVoteEvent::err_mess() . "<br>Function: GetUserAnswerStat<br>Line: ";
$arFilter = is_array($arFilter) ? $arFilter : array();
if (!is_array($arSort) && $arSort > 0) {
$arFilter["VOTE_ID"] = $arSort;
$arFilter["VALID"] = "Y";
$arSort = array();
}
$arFilter["bGetMemoStat"] = $arFilter["bGetMemoStat"] == "N" ? "N" : "Y";
$arSqlSelect = $arSqlSearch = $arSqlGroup = array();
$strSqlSelect = $strSqlSearch = $strSqlGroup = "";
foreach ($arFilter as $key => $val) {
$key_res = VoteGetFilterOperation($key);
$strNegative = $key_res["NEGATIVE"];
$strOperation = $key_res["OPERATION"];
$key = strtoupper($key_res["FIELD"]);
switch ($key) {
case "ID":
case "VOTE_ID":
case "QUESTION_ID":
case "ANSWER_ID":
case "USER_ID":
case "AUTH_USER_ID":
switch ($key) {
case "ID":
case "VOTE_ID":
$key = "VE." . $key;
break;
case "QUESTION_ID":
$key = "VEQ." . $key;
break;
case "ANSWER_ID":
$key = "VEA." . $key;
break;
case "USER_ID":
case "AUTH_USER_ID":
$key = "VU.AUTH_USER_ID";
break;
}
$str = ($strNegative == "Y" ? "NOT" : "") . "(" . $key . " IS NULL OR " . $key . "<=0)";
if (!empty($val)) {
$str = ($strNegative == "Y" ? " " . $key . " IS NULL OR NOT " : "") . "(" . $key . " " . $strOperation . " " . intVal($val) . ")";
if ($strOperation == "IN") {
$val = array_unique(is_array($val) ? $val : explode(",", $val), SORT_NUMERIC);
$str = ($strNegative == "Y" ? " NOT " : "") . "(" . $key . " IN (" . $DB->ForSql(implode(",", $val)) . "))";
}
}
$arSqlSearch[] = $str;
break;
case "VALID":
if (empty($val)) {
$arSqlSearch[] = ($strNegative == "Y" ? "NOT" : "") . "(VE." . $key . " IS NULL OR LENGTH(VE." . $key . ")<=0)";
} else {
$arSqlSearch[] = ($strNegative == "Y" ? " VE." . $key . " IS NULL OR NOT " : "") . "(VE." . $key . " " . $strOperation . " '" . $DB->ForSql($val) . "' )";
}
break;
case "BGETMEMOSTAT":
if ($val == "Y") {
$arSqlGroup[] = $arSqlSelect[] = "VEA.MESSAGE";
$arSqlSearch[] = "VEA.MESSAGE != ' '";
}
break;
case "BGETVOTERS":
if ($val == "Y" || $val > 0) {
$arSqlGroup[] = $arSqlSelect[] = "VU.AUTH_USER_ID";
$arSqlSearch[] = "VU.AUTH_USER_ID > 0";
$arFilter["bGetVoters"] = intval($val == "Y" ? $GLOBALS["USER"]->GetID() : $val);
}
break;
case "BGETEVENTRESULTS":
$arFilter["bGetEventResults"] = intval($arFilter["bGetEventResults"]);
if ($arFilter["bGetEventResults"] > 0) {
$arSqlSelect[] = "MAX(CASE WHEN VE.ID=" . $arFilter["bGetEventResults"] . " THEN VEA.ANSWER_ID ELSE NULL END) AS RESTORED_ANSWER_ID";
}
break;
}
}
if (!empty($arSqlSearch)) {
$strSqlSearch = " AND (" . implode(") AND (", $arSqlSearch) . ") ";
}
if (!empty($arSqlSelect)) {
$strSqlSelect = ", " . implode(", ", $arSqlSelect);
}
if (!empty($arSqlGroup)) {
$strSqlGroup = ", " . implode(", ", $arSqlGroup);
}
$strSql = "SELECT VEQ.QUESTION_ID, VEA.ANSWER_ID, COUNT(VEA.ID) as COUNTER, " . "MIN(TIMESTAMPDIFF(SECOND, VE.DATE_VOTE, NOW())) AS LAST_VOTE" . $strSqlSelect . " FROM b_vote_event VE " . " INNER JOIN b_vote_event_question VEQ ON (VEQ.EVENT_ID = VE.ID) " . " INNER JOIN b_vote_event_answer VEA ON (VEA.EVENT_QUESTION_ID = VEQ.ID) " . " LEFT JOIN b_vote_user VU ON (VU.ID = VE.VOTE_USER_ID)" . " WHERE 1=1 " . $strSqlSearch . " GROUP BY VEQ.QUESTION_ID, VEA.ANSWER_ID" . $strSqlGroup . " ORDER BY COUNTER DESC";
if (isset($arFilter["bGetVoters"])) {
$strSql = "SELECT COUNT(VEG.COUNTER) AS CNT FROM (" . "SELECT 'x' AS COUNTER " . " FROM b_vote_event VE " . " INNER JOIN b_vote_event_question VEQ ON (VEQ.EVENT_ID = VE.ID) " . " INNER JOIN b_vote_event_answer VEA ON (VEA.EVENT_QUESTION_ID = VEQ.ID) " . " LEFT JOIN b_vote_user VU ON (VU.ID = VE.VOTE_USER_ID)" . " WHERE 1=1 " . $strSqlSearch . " GROUP BY VEQ.QUESTION_ID, VEA.ANSWER_ID" . $strSqlGroup . ") VEG";
$db_res = $GLOBALS["DB"]->Query($strSql);
if ($db_res && ($res = $db_res->Fetch())) {
$strSql = "SELECT VEQ.QUESTION_ID, VEA.ANSWER_ID, COUNT(VEA.ID) as COUNTER, " . " MIN(TIMESTAMPDIFF(SECOND, VE.DATE_VOTE, NOW())) AS LAST_VOTE, " . ($arFilter["bGetVoters"] > 0 ? " SUM(case when RV0.ID is not null then 1 else 0 end) RANK, " : " 0 as RANK, ") . " MAX(RV.VALUE) RV_VALUE, MAX(RV.ID) RV_ID" . $strSqlSelect . " FROM b_vote_event VE " . " INNER JOIN b_vote_event_question VEQ ON (VEQ.EVENT_ID = VE.ID) " . " INNER JOIN b_vote_event_answer VEA ON (VEA.EVENT_QUESTION_ID = VEQ.ID) " . " LEFT JOIN b_vote_user VU ON (VU.ID = VE.VOTE_USER_ID)" . " LEFT JOIN b_rating_vote RV ON (RV.USER_ID = VU.AUTH_USER_ID)" . ($arFilter["bGetVoters"] > 0 ? " LEFT JOIN b_rating_vote RV0 ON (RV0.USER_ID = " . intval($arFilter["bGetVoters"]) . " AND RV0.OWNER_ID = RV.USER_ID) " : "") . " WHERE 1=1 " . $strSqlSearch . " GROUP BY VEQ.QUESTION_ID, VEA.ANSWER_ID" . $strSqlGroup . " ORDER BY RANK DESC, RV_VALUE DESC, RV_ID DESC";
$db_res = new CDBResult();
$db_res->NavQuery($strSql, $res["CNT"], $arParams);
}
} else {
$db_res = $DB->Query($strSql, false, $err_mess . __LINE__);
}
return $db_res;
}
function GetListEx($arOrder = array("ID" => "ASC"), $arFilter = array())
{
global $DB;
$arSqlSearch = array();
$strSqlSearch = "";
$arSqlOrder = array();
$strSqlOrder = "";
$arFilter = is_array($arFilter) ? $arFilter : array();
foreach ($arFilter as $key => $val) {
if ($val === "NOT_REF") {
continue;
}
$key_res = VoteGetFilterOperation($key);
$strNegative = $key_res["NEGATIVE"];
$strOperation = $key_res["OPERATION"];
$key = strtoupper($key_res["FIELD"]);
switch ($key) {
case "ID":
case "VOTE_ID":
$str = ($strNegative == "Y" ? "NOT" : "") . "(VQ." . $key . " IS NULL OR VQ." . $key . "<=0)";
if (!empty($val)) {
$str = ($strNegative == "Y" ? " VQ." . $key . " IS NULL OR NOT " : "") . "(VQ." . $key . " " . $strOperation . " " . intVal($val) . ")";
if ($strOperation == "IN") {
$val = array_unique(array_map("intval", is_array($val) ? $val : explode(",", $val)), SORT_NUMERIC);
if (!empty($val)) {
$str = ($strNegative == "Y" ? " NOT " : "") . "(VQ." . $key . " IN (" . implode(",", $val) . "))";
}
}
}
$arSqlSearch[] = $str;
break;
case "CHANNEL_ID":
if (strlen($val) <= 0) {
$arSqlSearch[] = ($strNegative == "Y" ? "NOT" : "") . "(V." . $key . " IS NULL OR V." . $key . "<=0)";
} else {
$arSqlSearch[] = ($strNegative == "Y" ? " V." . $key . " IS NULL OR NOT " : "") . "(V." . $key . " " . $strOperation . " " . intVal($val) . ")";
}
break;
case "ACTIVE":
if (empty($val)) {
$arSqlSearch[] = ($strNegative == "Y" ? "NOT" : "") . "(VQ." . $key . " IS NULL OR " . ($DB->type == "MSSQL" ? "LEN" : "LENGTH") . "(VQ." . $key . ")<=0)";
} else {
$arSqlSearch[] = ($strNegative == "Y" ? " VQ." . $key . " IS NULL OR NOT " : "") . "(VQ." . $key . " " . $strOperation . " '" . $DB->ForSql($val) . "')";
}
break;
}
}
if (count($arSqlSearch) > 0) {
$strSqlSearch = " AND (" . implode(") AND (", $arSqlSearch) . ") ";
}
foreach ($arOrder as $by => $order) {
$by = strtoupper($by);
$order = strtoupper($order);
$by = $by == "ACTIVE" ? $by : "ID";
if ($order != "ASC") {
$order = "DESC";
}
if ($by == "ACTIVE") {
$arSqlOrder[] = " VQ.ACTIVE " . $order . " ";
} else {
$arSqlOrder[] = " VQ.ID " . $order . " ";
}
}
DelDuplicateSort($arSqlOrder);
if (count($arSqlOrder) > 0) {
$strSqlOrder = " ORDER BY " . implode(", ", $arSqlOrder);
}
$strSql = "\n\t\t\tSELECT VQ.*\n\t\t\tFROM\n\t\t\t\tb_vote_question VQ, b_vote V\n\t\t\tWHERE VQ.VOTE_ID = V.ID " . $strSqlSearch . "\n\t\t\t" . $strSqlOrder;
return $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
public static function GetListEx($arOrder = array("ID" => "ASC"), $arFilter=array(), $arAddParams = array())
{
$err_mess = (self::err_mess())."<br>Function: GetListEx<br>Line: ";
global $DB;
$arSqlSearch = Array();
$strSqlSearch = "";
$arSqlOrder = Array();
$strSqlOrder = "";
$arFilter = (is_array($arFilter) ? $arFilter : array());
$arAddParams = (is_array($arAddParams) ? $arAddParams : array());
foreach ($arFilter as $key => $val)
{
if ($val === "NOT_REF")
continue;
$key_res = VoteGetFilterOperation($key);
$strNegative = $key_res["NEGATIVE"];
$strOperation = $key_res["OPERATION"];
$key = strtoupper($key_res["FIELD"]);
switch($key)
{
case "ID":
case "QUESTION_ID":
$str = ($strNegative=="Y"?"NOT":"")."(VA.".$key." IS NULL OR VA.".$key."<=0)";
if (!empty($val))
{
$str = ($strNegative=="Y"?" VA.".$key." IS NULL OR NOT ":"")."(VA.".$key." ".$strOperation." ".intVal($val).")";
if ($strOperation == "IN")
{
$val = array_unique((is_array($val) ? $val : explode(",", $val)), SORT_NUMERIC);
$str = ($strNegative=="Y"?" NOT ":"")."(VA.".$key." IN (".$DB->ForSql(implode(",", $val))."))";
}
}
$arSqlSearch[] = $str;
break;
case "VOTE_ID":
$str = ($strNegative=="Y"?"NOT":"")."(VQ.".$key." IS NULL OR VQ.".$key."<=0)";
if (!empty($val))
{
$str = ($strNegative=="Y"?" VQ.".$key." IS NULL OR NOT ":"")."(VQ.".$key." ".$strOperation." ".intVal($val).")";
if ($strOperation == "IN")
{
$val = array_unique((is_array($val) ? $val : explode(",", $val)), SORT_NUMERIC);
$str = ($strNegative=="Y"?" NOT ":"")."(VQ.".$key." IN (".$DB->ForSql(implode(",", $val))."))";
}
}
$arSqlSearch[] = $str;
break;
case "CHANNEL_ID":
$str = ($strNegative=="Y"?"NOT":"")."(V.".$key." IS NULL OR V.".$key."<=0)";
if (!empty($val))
{
$str = ($strNegative=="Y"?" V.".$key." IS NULL OR NOT ":"")."(V.".$key." ".$strOperation." ".intVal($val).")";
if ($strOperation == "IN")
{
$val = array_unique((is_array($val) ? $val : explode(",", $val)), SORT_NUMERIC);
$str = ($strNegative=="Y"?" NOT ":"")."(V.".$key." IN (".$DB->ForSql(implode(",", $val))."))";
}
}
$arSqlSearch[] = $str;
break;
case "ACTIVE":
if (empty($val))
$arSqlSearch[] = ($strNegative=="Y"?"NOT":"")."(VA.".$key." IS NULL OR ".($DB->type == "MSSQL" ? "LEN" : "LENGTH")."(VA.".$key.")<=0)";
else
$arSqlSearch[] = ($strNegative=="Y"?" VA.".$key." IS NULL OR NOT ":"")."(VA.".$key." ".$strOperation." '".$DB->ForSql($val)."')";
break;
}
}
if (count($arSqlSearch) > 0)
$strSqlSearch = " AND (".implode(") AND (", $arSqlSearch).") ";
foreach ($arOrder as $by => $order)
{
$by = strtoupper($by); $order = strtoupper($order);
if ($order!="ASC") $order = "DESC";
if ($by == "ID") $arSqlOrder[] = " VA.ID ".$order." ";
elseif ($by == "ACTIVE") $arSqlOrder[] = " VA.ACTIVE ".$order." ";
else
{
$arSqlOrder[] = " VA.ID ".$order." ";
$by = "ID";
}
}
DelDuplicateSort($arSqlOrder);
if (count($arSqlOrder) > 0)
$strSqlOrder = " ORDER BY ".implode(", ", $arSqlOrder);
$strSql = "
SELECT V.CHANNEL_ID, VQ.VOTE_ID, VA.*
FROM b_vote_answer VA
INNER JOIN b_vote_question VQ ON (VA.QUESTION_ID = VQ.ID)
INNER JOIN b_vote V ON (VQ.VOTE_ID = V.ID)
WHERE 1=1 ".$strSqlSearch." ".$strSqlOrder;
return $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__);
}