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