function GetList(&$by, &$order, $arFilter = array(), &$is_filtered, $get_user = "******")
{
$err_mess = CAllVoteEvent::err_mess() . "<br>Function: GetList<br>Line: ";
global $DB;
$arSqlSearch = array();
$strSqlSearch = "";
if (is_array($arFilter)) {
$filter_keys = array_keys($arFilter);
$count = count($filter_keys);
for ($i = 0; $i < $count; $i++) {
$key = $filter_keys[$i];
$val = $arFilter[$filter_keys[$i]];
if (is_array($val)) {
if (count($val) <= 0) {
continue;
}
} else {
if (strlen($val) <= 0 || $val === "NOT_REF") {
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("E.ID", $val, $match);
break;
case "VALID":
$arSqlSearch[] = $val == "Y" ? "E.VALID='Y'" : "E.VALID='N'";
break;
case "DATE_1":
$arSqlSearch[] = "E.DATE_VOTE>=" . $DB->CharToDateFunction($val, "SHORT");
break;
case "DATE_2":
$arSqlSearch[] = "E.DATE_VOTE<=" . $DB->CharToDateFunction($val . " 23:59:59", "FULL");
break;
case "VOTE_USER":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $match_value_set ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("E.VOTE_USER_ID", $val, $match);
break;
case "USER_ID":
if ($get_user == "Y") {
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $match_value_set ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("U.AUTH_USER_ID", $val, $match);
}
break;
case "SESSION":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("E.STAT_SESSION_ID", $val, $match);
break;
case "IP":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("E.IP", $val, $match, array("."));
break;
case "VOTE":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("E.VOTE_ID, V.TITLE", $val, $match);
break;
case "VOTE_ID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $match_value_set ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("E.VOTE_ID", $val, $match);
break;
}
}
}
if ($by == "s_id") {
$strSqlOrder = "ORDER BY E.ID";
} elseif ($by == "s_valid") {
$strSqlOrder = "ORDER BY E.VALID";
} elseif ($by == "s_date") {
$strSqlOrder = "ORDER BY E.DATE_VOTE";
} elseif ($by == "s_session") {
$strSqlOrder = "ORDER BY E.STAT_SESSION_ID";
} elseif ($by == "s_vote_user") {
$strSqlOrder = "ORDER BY E.VOTE_USER_ID";
} elseif ($by == "s_vote") {
$strSqlOrder = "ORDER BY E.VOTE_ID";
} elseif ($by == "s_ip") {
$strSqlOrder = "ORDER BY E.IP";
} else {
$by = "s_id";
$strSqlOrder = "ORDER BY E.ID";
}
if ($order != "asc") {
$strSqlOrder .= " desc ";
$order = "desc";
}
if ($get_user == "Y") {
$select = " ,\n\t\t\t\tU.AUTH_USER_ID, U.STAT_GUEST_ID,\n\t\t\t\tA.LOGIN,\n\t\t\t\t" . $DB->Concat("A.LAST_NAME", "' '", "A.NAME") . "\tAUTH_USER_NAME\n\t\t\t";
$from = "\n\t\t\tLEFT JOIN b_vote_user U ON (U.ID = E.VOTE_USER_ID)\n\t\t\tLEFT JOIN b_user A ON (A.ID = U.AUTH_USER_ID)\n\t\t\t";
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$strSql = "\n\t\t\tSELECT \n\t\t\t\tE.*,\n\t\t\t\t" . $DB->DateToCharFunction("E.DATE_VOTE") . "\tDATE_VOTE,\n\t\t\t\tV.TITLE, V.DESCRIPTION, V.DESCRIPTION_TYPE\n\t\t\t\t{$select}\n\t\t\tFROM\n\t\t\t\tb_vote_event E\n\t\t\tINNER JOIN b_vote V ON (V.ID=E.VOTE_ID)\n\t\t\t{$from}\n\t\t\tWHERE\n\t\t\t{$strSqlSearch}\n\t\t\t{$strSqlOrder}\n\t\t\t";
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
$is_filtered = IsFiltered($strSqlSearch);
return $res;
}
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;
}
