function PrepareResultFilter($WEB_FORM_ID, $arFilter) { return CFormResult::PrepareFilter($WEB_FORM_ID, $arFilter); }
function GetList($WEB_FORM_ID, &$by, &$order, $arFilter=Array(), &$is_filtered, $CHECK_RIGHTS="Y", $records_limit=false) { $err_mess = (CFormResult::err_mess())."<br>Function: GetList<br>Line: "; global $DB, $USER, $strError; $CHECK_RIGHTS = ($CHECK_RIGHTS=="Y") ? "Y" : "N"; $WEB_FORM_ID = intval($WEB_FORM_ID); $F_RIGHT = CForm::GetPermission($WEB_FORM_ID); $USER_ID = intval($USER->GetID()); $arSqlSearch = array(); $arr["FIELDS"] = array(); $strSqlSearch = ""; if (is_array($arFilter)) { $arFilter = CFormResult::PrepareFilter($WEB_FORM_ID, $arFilter); $z = CForm::GetByID($WEB_FORM_ID); $form = $z->Fetch(); /***********************/ $z = CFormField::GetList($WEB_FORM_ID, "", $v1, $v2, array(), $v3); while ($zr=$z->Fetch()) { $arPARAMETER_NAME = array("ANSWER_TEXT", "ANSWER_VALUE", "USER"); CFormField::GetFilterTypeList($arrUSER, $arrANSWER_TEXT, $arrANSWER_VALUE, $arrFIELD); foreach ($arPARAMETER_NAME as $PARAMETER_NAME) { switch ($PARAMETER_NAME) { case "ANSWER_TEXT": $arFILTER_TYPE = $arrANSWER_TEXT["reference_id"]; break; case "ANSWER_VALUE": $arFILTER_TYPE = $arrANSWER_VALUE["reference_id"]; break; case "USER": $arFILTER_TYPE = $arrUSER["reference_id"]; break; } foreach ($arFILTER_TYPE as $FILTER_TYPE) { $arrUF = array(); $arrUF["ID"] = $zr["ID"]; $arrUF["PARAMETER_NAME"] = $PARAMETER_NAME; $arrUF["FILTER_TYPE"] = $FILTER_TYPE; $FID = $form["SID"]."_".$zr["SID"]."_".$PARAMETER_NAME."_".$FILTER_TYPE; if ($FILTER_TYPE=="date" || $FILTER_TYPE=="integer") { $arrUF["SIDE"] = "1"; $arrFORM_FILTER[$FID."_1"] = $arrUF; $arrUF["SIDE"] = "2"; $arrFORM_FILTER[$FID."_2"] = $arrUF; $arrUF["SIDE"] = "0"; $arrFORM_FILTER[$FID."_0"] = $arrUF; } else $arrFORM_FILTER[$FID] = $arrUF; } } } if (is_array($arrFORM_FILTER)) $arrFORM_FILTER_KEYS = array_keys($arrFORM_FILTER); //echo "arFilter:<pre>"; print_r($arFilter); echo "</pre>"; //echo "arrFORM_FILTER:<pre>"; print_r($arrFORM_FILTER); echo "</pre>"; //echo "arrFORM_FILTER_KEYS:<pre>"; print_r($arrFORM_FILTER_KEYS); echo "</pre>"; $t = 0; $filter_keys = array_keys($arFilter); for ($i=0; $i<count($filter_keys); $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("R.ID", $val, $match); break; case "STATUS": $arSqlSearch[] = "R.STATUS_ID='".intval($val)."'"; break; case "STATUS_ID": $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $match_value_set) ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("R.STATUS_ID", $val, $match); break; case "TIMESTAMP_1": $arSqlSearch[] = "R.TIMESTAMP_X>=".$DB->CharToDateFunction($val, "SHORT"); break; case "TIMESTAMP_2": $arSqlSearch[] = "R.TIMESTAMP_X<".$DB->CharToDateFunction($val, "SHORT")." + INTERVAL 1 DAY"; break; case "DATE_CREATE_1": $arSqlSearch[] = "R.DATE_CREATE>=".$DB->CharToDateFunction($val, "SHORT"); break; case "DATE_CREATE_2": $arSqlSearch[] = "R.DATE_CREATE<".$DB->CharToDateFunction($val, "SHORT")." + INTERVAL 1 DAY"; break; case "TIME_CREATE_1": $arSqlSearch[] = "R.DATE_CREATE>=".$DB->CharToDateFunction($val, "FULL"); break; case "TIME_CREATE_2": $arSqlSearch[] = "R.DATE_CREATE<".$DB->CharToDateFunction($val, "FULL"); break; case "REGISTERED": $arSqlSearch[] = ($val=="Y") ? "R.USER_ID>0" : "(R.USER_ID<=0 or R.USER_ID is null)"; break; case "USER_AUTH": $arSqlSearch[] = ($val=="Y") ? "(R.USER_AUTH='Y' and R.USER_ID>0)" : "(R.USER_AUTH='N' and R.USER_ID>0)"; break; case "USER_ID": $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $match_value_set) ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("R.USER_ID", $val, $match); break; case "GUEST_ID": $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $match_value_set) ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("R.STAT_GUEST_ID", $val, $match); break; case "SESSION_ID": $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $match_value_set) ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("R.STAT_SESSION_ID", $val, $match); break; case "SENT_TO_CRM": $arSqlSearch[] = GetFilterQuery("R.SENT_TO_CRM", $val, "Y"); break; default: if (is_array($arrFORM_FILTER)) { $key = $filter_keys[$i]; if (in_array($key, $arrFORM_FILTER_KEYS)) { $arrF = $arrFORM_FILTER[$key]; if (is_array($arr["FIELDS"]) && !in_array($arrF["ID"],$arr["FIELDS"])) { $t++; $A = "A".$t; $arr["TABLES"][] = "b_form_result_answer ".$A; $arr["WHERE"][] = "(".$A.".RESULT_ID=R.ID and ".$A.".FIELD_ID='".$arrF["ID"]."')"; $arr["FIELDS"][] = $arrF["ID"]; } switch(strtoupper($arrF["FILTER_TYPE"])) { case "EXIST": if ($arrF["PARAMETER_NAME"]=="ANSWER_TEXT") $arSqlSearch[] = "length(".$A.".ANSWER_TEXT)+0>0"; elseif ($arrF["PARAMETER_NAME"]=="ANSWER_VALUE") $arSqlSearch[] = "length(".$A.".ANSWER_VALUE)+0>0"; elseif ($arrF["PARAMETER_NAME"]=="USER") $arSqlSearch[] = "length(".$A.".USER_TEXT)+0>0"; break; case "TEXT": $match = ($arFilter[$key."_exact_match"]=="Y") ? "N" : "Y"; $sql = ""; if ($arrF["PARAMETER_NAME"]=="ANSWER_TEXT") $sql = GetFilterQuery($A.".ANSWER_TEXT_SEARCH", ToUpper($val), $match); elseif ($arrF["PARAMETER_NAME"]=="ANSWER_VALUE") $sql = GetFilterQuery($A.".ANSWER_VALUE_SEARCH", ToUpper($val), $match); elseif ($arrF["PARAMETER_NAME"]=="USER") $sql = GetFilterQuery($A.".USER_TEXT_SEARCH", ToUpper($val), $match); if ($sql!=="0" && strlen(trim($sql))>0) $arSqlSearch[] = $sql; break; case "DROPDOWN": case "ANSWER_ID": $arSqlSearch[] = $A.".ANSWER_ID=".intval($val); break; case "DATE": if ($arrF["PARAMETER_NAME"]=="USER") { if (CheckDateTime($val)) { if ($arrF["SIDE"]=="1") $arSqlSearch[] = $A.".USER_DATE>=".$DB->CharToDateFunction($val, "SHORT"); elseif ($arrF["SIDE"]=="2") $arSqlSearch[] = $A.".USER_DATE<".$DB->CharToDateFunction($val, "SHORT")." + INTERVAL 1 DAY"; elseif ($arrF["SIDE"]=="0") $arSqlSearch[] = $A.".USER_DATE=".$DB->CharToDateFunction($val); } } break; case "INTEGER": if ($arrF["PARAMETER_NAME"]=="USER") { if ($arrF["SIDE"]=="1") $arSqlSearch[] = $A.".USER_TEXT+0>=".intval($val); elseif ($arrF["SIDE"]=="2") $arSqlSearch[] = $A.".USER_TEXT+0<=".intval($val); elseif ($arrF["SIDE"]=="0") $arSqlSearch[] = $A.".USER_TEXT='".intval($val)."'"; } elseif ($arrF["PARAMETER_NAME"]=="ANSWER_TEXT") { if ($arrF["SIDE"]=="1") $arSqlSearch[] = $A.".ANSWER_TEXT+0>=".intval($val); elseif ($arrF["SIDE"]=="2") $arSqlSearch[] = $A.".ANSWER_TEXT+0<=".intval($val); elseif ($arrF["SIDE"]=="0") $arSqlSearch[] = $A.".ANSWER_TEXT='".intval($val)."'"; } elseif ($arrF["PARAMETER_NAME"]=="ANSWER_VALUE") { if ($arrF["SIDE"]=="1") $arSqlSearch[] = $A.".ANSWER_VALUE+0>=".intval($val); elseif ($arrF["SIDE"]=="2") $arSqlSearch[] = $A.".ANSWER_VALUE+0<=".intval($val); elseif ($arrF["SIDE"]=="0") $arSqlSearch[] = $A.".ANSWER_VALUE='".intval($val)."'"; } break; } } } } } } if ($by == "s_id") $strSqlOrder = "ORDER BY R.ID"; elseif ($by == "s_date_create") $strSqlOrder = "ORDER BY R.DATE_CREATE"; elseif ($by == "s_timestamp") $strSqlOrder = "ORDER BY R.TIMESTAMP_X"; elseif ($by == "s_user_id") $strSqlOrder = "ORDER BY R.USER_ID"; elseif ($by == "s_guest_id") $strSqlOrder = "ORDER BY R.STAT_GUEST_ID"; elseif ($by == "s_session_id") $strSqlOrder = "ORDER BY R.STAT_SESSION_ID"; elseif ($by == "s_status") $strSqlOrder = "ORDER BY R.STATUS_ID"; elseif ($by == "s_sent_to_crm") $strSqlOrder = "ORDER BY R.SENT_TO_CRM"; else { $by = "s_timestamp"; $strSqlOrder = "ORDER BY R.TIMESTAMP_X"; } if ($order!="asc") { $strSqlOrder .= " desc "; $order="desc"; } $strSqlSearch = GetFilterSqlSearch($arSqlSearch); if (is_array($arr["TABLES"])) $str1 = implode(",\n ",$arr["TABLES"]); if (is_array($arr["WHERE"])) $str2 = implode("\n and ",$arr["WHERE"]); if (strlen($str1)>0) $str1 = ",\n ".$str1; if (strlen($str2)>0) $str2 = "\n and ".$str2; if ($records_limit===false) { $records_limit = "LIMIT ".intval(COption::GetOptionString("form","RECORDS_LIMIT")); } else { $records_limit = intval($records_limit); if ($records_limit>0) { $records_limit = "LIMIT ".$records_limit; } } //this hack is for mysql <3.23. we no longer support that dino. //$DB->Query("SET SQL_BIG_TABLES=1", false, $err_mess.__LINE__); if ($CHECK_RIGHTS!="Y" || $F_RIGHT >= 30 || CForm::IsAdmin()) { $strSql = " SELECT R.ID, R.USER_ID, R.USER_AUTH, R.STAT_GUEST_ID, R.STAT_SESSION_ID, R.STATUS_ID, R.SENT_TO_CRM, ".$DB->DateToCharFunction("R.DATE_CREATE")." DATE_CREATE, ".$DB->DateToCharFunction("R.TIMESTAMP_X")." TIMESTAMP_X, S.TITLE STATUS_TITLE, S.CSS STATUS_CSS FROM b_form_result R, b_form_status S $str1 WHERE $strSqlSearch $str2 and R.FORM_ID = '$WEB_FORM_ID' and S.ID = R.STATUS_ID GROUP BY R.ID, R.USER_ID, R.USER_AUTH, R.STAT_GUEST_ID, R.STAT_SESSION_ID, R.DATE_CREATE, R.STATUS_ID, R.SENT_TO_CRM $strSqlOrder $records_limit "; $res = $DB->Query($strSql, false, $err_mess.__LINE__); //echo '<pre>'.$strSql.'</pre>'; } elseif ($F_RIGHT>=15) { $arGroups = $USER->GetUserGroupArray(); if (!is_array($arGroups)) $arGroups[] = 2; if (is_array($arGroups) && count($arGroups)>0) $groups = implode(",",$arGroups); if ($F_RIGHT<20) $str3 = "and ifnull(R.USER_ID,0) = $USER_ID"; $strSql = " SELECT R.ID, R.USER_ID, R.USER_AUTH, R.STAT_GUEST_ID, R.STAT_SESSION_ID, R.STATUS_ID, R.SENT_TO_CRM, ".$DB->DateToCharFunction("R.DATE_CREATE")." DATE_CREATE, ".$DB->DateToCharFunction("R.TIMESTAMP_X")." TIMESTAMP_X, S.TITLE STATUS_TITLE, S.CSS STATUS_CSS FROM b_form_result R, b_form_status S, b_form_status_2_group G$str1 WHERE $strSqlSearch $str2 $str3 and R.FORM_ID = '$WEB_FORM_ID' and S.ID = R.STATUS_ID and G.STATUS_ID = S.ID and ( (G.GROUP_ID in ($groups)) or (G.GROUP_ID in ($groups,0) and ifnull(R.USER_ID,0) = $USER_ID and $USER_ID>0) ) and G.PERMISSION in ('VIEW', 'EDIT', 'DELETE') GROUP BY R.ID, R.USER_ID, R.USER_AUTH, R.STAT_GUEST_ID, R.STAT_SESSION_ID, R.SENT_TO_CRM, R.DATE_CREATE, R.STATUS_ID, R.SENT_TO_CRM $strSqlOrder $records_limit "; $res = $DB->Query($strSql, false, $err_mess.__LINE__); } else { $res = new CDBResult(); $res->InitFromArray(array()); } //echo "<pre>".$strSql."</pre>"; //echo "<pre>".$strSqlSearch."</pre>"; $is_filtered = (IsFiltered($strSqlSearch)); return $res; }
function GetList($WEB_FORM_ID, &$by, &$order, $arFilter = array(), &$is_filtered, $CHECK_RIGHTS = "Y", $records_limit = false) { $err_mess = CFormResult::err_mess() . "<br>Function: GetList<br>Line: "; global $DB, $USER, $strError; $CHECK_RIGHTS = $CHECK_RIGHTS == "Y" ? "Y" : "N"; $WEB_FORM_ID = intval($WEB_FORM_ID); $F_RIGHT = CForm::GetPermission($WEB_FORM_ID); $USER_ID = intval($USER->GetID()); $arSqlSearch = array(); $arrSEARCH = array(); $arrFIELDS = array(); $strSqlSearch = ""; if (is_array($arFilter)) { $arFilter = CFormResult::PrepareFilter($WEB_FORM_ID, $arFilter); $z = CForm::GetByID($WEB_FORM_ID); $form = $z->Fetch(); $z = CFormField::GetList($WEB_FORM_ID, "", $v1, $v2, array(), $v3); while ($zr = $z->Fetch()) { $arPARAMETER_NAME = array("ANSWER_TEXT", "ANSWER_VALUE", "USER"); CFormField::GetFilterTypeList($arrUSER, $arrANSWER_TEXT, $arrANSWER_VALUE, $arrFIELD); foreach ($arPARAMETER_NAME as $PARAMETER_NAME) { switch ($PARAMETER_NAME) { case "ANSWER_TEXT": $arFILTER_TYPE = $arrANSWER_TEXT["reference_id"]; break; case "ANSWER_VALUE": $arFILTER_TYPE = $arrANSWER_VALUE["reference_id"]; break; case "USER": $arFILTER_TYPE = $arrUSER["reference_id"]; break; } foreach ($arFILTER_TYPE as $FILTER_TYPE) { $arrUF = array(); $arrUF["ID"] = $zr["ID"]; $arrUF["PARAMETER_NAME"] = $PARAMETER_NAME; $arrUF["FILTER_TYPE"] = $FILTER_TYPE; $FID = $form["SID"] . "_" . $zr["SID"] . "_" . $PARAMETER_NAME . "_" . $FILTER_TYPE; if ($FILTER_TYPE == "date" || $FILTER_TYPE == "integer") { $arrUF["SIDE"] = "1"; $arrFORM_FILTER[$FID . "_1"] = $arrUF; $arrUF["SIDE"] = "2"; $arrFORM_FILTER[$FID . "_2"] = $arrUF; $arrUF["SIDE"] = "0"; $arrFORM_FILTER[$FID . "_0"] = $arrUF; } else { $arrFORM_FILTER[$FID] = $arrUF; } } } } if (is_array($arrFORM_FILTER)) { $arrFORM_FILTER_KEYS = array_keys($arrFORM_FILTER); } //echo "arFilter:<pre>"; print_r($arFilter); echo "</pre>"; //echo "arrFORM_FILTER:<pre>"; print_r($arrFORM_FILTER); echo "</pre>"; //echo "arrFORM_FILTER_KEYS:<pre>"; print_r($arrFORM_FILTER_KEYS); echo "</pre>"; $t = 0; $filter_keys = array_keys($arFilter); for ($i = 0; $i < count($filter_keys); $i++) { $key = $filter_keys[$i]; $val = $arFilter[$filter_keys[$i]]; if (strlen($val) <= 0 || "{$val}" == "NOT_REF") { continue; } if (is_array($val) && count($val) <= 0) { 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("R.ID", $val, $match); break; case "STATUS": $arSqlSearch[] = "R.STATUS_ID='" . intval($val) . "'"; break; case "STATUS_ID": $match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $match_value_set ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("R.STATUS_ID", $val, $match); break; case "TIMESTAMP_1": $arSqlSearch[] = "R.TIMESTAMP_X>=" . $DB->CharToDateFunction($val, "SHORT"); break; case "TIMESTAMP_2": $arSqlSearch[] = "R.TIMESTAMP_X<" . $DB->CharToDateFunction($val, "SHORT") . "+1"; break; case "DATE_CREATE_1": $arSqlSearch[] = "R.DATE_CREATE>=" . $DB->CharToDateFunction($val, "SHORT"); break; case "DATE_CREATE_2": $arSqlSearch[] = "R.DATE_CREATE<" . $DB->CharToDateFunction($val, "SHORT") . "+1"; break; case "TIME_CREATE_1": $arSqlSearch[] = "R.DATE_CREATE>=" . $DB->CharToDateFunction($val, "FULL"); break; case "TIME_CREATE_2": $arSqlSearch[] = "R.DATE_CREATE<" . $DB->CharToDateFunction($val, "FULL"); break; case "REGISTERED": $arSqlSearch[] = $val == "Y" ? "R.USER_ID>0" : "(R.USER_ID<=0 or R.USER_ID is null)"; break; case "USER_AUTH": $arSqlSearch[] = $val == "Y" ? "(R.USER_AUTH='Y' and R.USER_ID>0)" : "(R.USER_AUTH='N' and R.USER_ID>0)"; break; case "USER_ID": $match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $match_value_set ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("R.USER_ID", $val, $match); break; case "GUEST_ID": $match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $match_value_set ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("R.STAT_GUEST_ID", $val, $match); break; case "SESSION_ID": $match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $match_value_set ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("R.STAT_SESSION_ID", $val, $match); break; default: if (is_array($arrFORM_FILTER)) { $key = $filter_keys[$i]; if (in_array($key, $arrFORM_FILTER_KEYS)) { $arrF = $arrFORM_FILTER[$key]; if (!in_array($arrF["ID"], $arrFIELDS)) { $t++; $arSqlSearch_f = array(); $A = "A" . $t; $arrFIELDS[$t] = $arrF["ID"]; } switch (strtoupper($arrF["FILTER_TYPE"])) { case "EXIST": if ($arrF["PARAMETER_NAME"] == "ANSWER_TEXT") { $arSqlSearch_f[] = $A . ".ANSWER_TEXT is not null"; } elseif ($arrF["PARAMETER_NAME"] == "ANSWER_VALUE") { $arSqlSearch_f[] = $A . ".ANSWER_VALUE is not null"; } elseif ($arrF["PARAMETER_NAME"] == "USER") { $arSqlSearch_f[] = $A . ".USER_TEXT is not null"; } break; case "TEXT": $match = $arFilter[$key . "_exact_match"] == "Y" ? "N" : "Y"; $sql = ""; if ($arrF["PARAMETER_NAME"] == "ANSWER_TEXT") { $sql = GetFilterQuery($A . ".ANSWER_TEXT_SEARCH", ToUpper($val), $match, array(), "Y", "Y", "Y"); } elseif ($arrF["PARAMETER_NAME"] == "ANSWER_VALUE") { $sql = GetFilterQuery($A . ".ANSWER_VALUE_SEARCH", ToUpper($val), $match, array(), "Y", "Y", "Y"); } elseif ($arrF["PARAMETER_NAME"] == "USER") { $sql = GetFilterQuery($A . ".USER_TEXT_SEARCH", ToUpper($val), $match, array(), "Y", "Y", "Y"); } if ($sql !== "0" && strlen(trim($sql)) > 0) { $arSqlSearch_f[] = $sql; } break; case "DROPDOWN": case "ANSWER_ID": $arSqlSearch_f[] = $A . ".ANSWER_ID=" . intval($val); break; case "DATE": if ($arrF["PARAMETER_NAME"] == "USER") { if (CheckDateTime($val)) { if ($arrF["SIDE"] == "1") { $arSqlSearch_f[] = $A . ".USER_DATE>=" . $DB->CharToDateFunction($val, "SHORT"); } elseif ($arrF["SIDE"] == "2") { $arSqlSearch_f[] = $A . ".USER_DATE<" . $DB->CharToDateFunction($val, "SHORT") . "+1"; } elseif ($arrF["SIDE"] == "0") { $arSqlSearch_f[] = $A . ".USER_DATE=" . $DB->CharToDateFunction($val); } } } break; case "INTEGER": if ($arrF["PARAMETER_NAME"] == "USER") { if ($arrF["SIDE"] == "1") { $arSqlSearch_f[] = "\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tCONVERT_TO_NUMBER(\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tDBMS_LOB.SUBSTR(" . $A . ".USER_TEXT, \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tDBMS_LOB.GETLENGTH(" . $A . ".USER_TEXT),\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t1)\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t)>=" . intval($val); } elseif ($arrF["SIDE"] == "2") { $arSqlSearch_f[] = "\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tCONVERT_TO_NUMBER(\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tDBMS_LOB.SUBSTR(" . $A . ".USER_TEXT,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tDBMS_LOB.GETLENGTH(" . $A . ".USER_TEXT),\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t1)\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t)<=" . intval($val); } elseif ($arrF["SIDE"] == "0") { $arSqlSearch_f[] = "\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tCONVERT_TO_NUMBER(\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tDBMS_LOB.SUBSTR(" . $A . ".USER_TEXT,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tDBMS_LOB.GETLENGTH(" . $A . ".USER_TEXT),\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t1)\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t)=" . intval($val); } } elseif ($arrF["PARAMETER_NAME"] == "ANSWER_TEXT") { if ($arrF["SIDE"] == "1") { $arSqlSearch_f[] = "CONVERT_TO_NUMBER(" . $A . ".ANSWER_TEXT)>=" . intval($val); } elseif ($arrF["SIDE"] == "2") { $arSqlSearch_f[] = "CONVERT_TO_NUMBER(" . $A . ".ANSWER_TEXT)<=" . intval($val); } elseif ($arrF["SIDE"] == "0") { $arSqlSearch_f[] = "CONVERT_TO_NUMBER(" . $A . ".ANSWER_TEXT)=" . intval($val); } } elseif ($arrF["PARAMETER_NAME"] == "ANSWER_VALUE") { if ($arrF["SIDE"] == "1") { $arSqlSearch_f[] = "CONVERT_TO_NUMBER(" . $A . ".ANSWER_VALUE)>=" . intval($val); } elseif ($arrF["SIDE"] == "2") { $arSqlSearch_f[] = "CONVERT_TO_NUMBER(" . $A . ".ANSWER_VALUE)<=" . intval($val); } elseif ($arrF["SIDE"] == "0") { $arSqlSearch_f[] = "CONVERT_TO_NUMBER(" . $A . ".ANSWER_VALUE)=" . intval($val); } } break; } if (is_array($arSqlSearch_f) && count($arSqlSearch_f) > 0) { $arrSEARCH[$t] = $arSqlSearch_f; } } } } } } if ($by == "s_id") { $strSqlOrder = "ORDER BY R.ID"; } elseif ($by == "s_date_create") { $strSqlOrder = "ORDER BY R.DATE_CREATE"; } elseif ($by == "s_timestamp") { $strSqlOrder = "ORDER BY R.TIMESTAMP_X"; } elseif ($by == "s_user_id") { $strSqlOrder = "ORDER BY R.USER_ID"; } elseif ($by == "s_guest_id") { $strSqlOrder = "ORDER BY R.STAT_GUEST_ID"; } elseif ($by == "s_session_id") { $strSqlOrder = "ORDER BY R.STAT_SESSION_ID"; } elseif ($by == "s_valid") { $strSqlOrder = "ORDER BY R.VALID"; } else { $by = "s_timestamp"; $strSqlOrder = "ORDER BY R.TIMESTAMP_X"; } if ($order != "asc") { $strSqlOrder .= " desc "; $order = "desc"; } $strSqlSearch = GetFilterSqlSearch($arSqlSearch); $strSqlSearch_F = ""; if (is_array($arrSEARCH) && count($arrSEARCH) > 0) { reset($arrSEARCH); while (list($index, $arrS) = each($arrSEARCH)) { $field = intval($arrFIELDS[$index]); if ($field > 0) { $str = implode(" and ", $arrS); $strSqlSearch_F .= "\r\n\t\t\t\t\t\tand EXISTS (\r\n\t\t\t\t\t\t\tSELECT 'x' FROM b_form_result_answer A{$index} \r\n\t\t\t\t\t\t\tWHERE\r\n\t\t\t\t\t\t\t\tA{$index}.RESULT_ID=R.ID \r\n\t\t\t\t\t\t\tand A{$index}.FIELD_ID={$field}\r\n\t\t\t\t\t\t\tand {$str}\r\n\t\t\t\t\t\t)\r\n\t\t\t\t\t\t"; } } } $records_limit = $records_limit === false ? intval(COption::GetOptionString("form", "RECORDS_LIMIT")) : intval($records_limit); if ($CHECK_RIGHTS != "Y" || CForm::IsAdmin()) { $strSql = "\r\n\t\t\t\tSELECT \r\n\t\t\t\t\tR.ID, R.USER_ID, R.USER_AUTH, R.STAT_GUEST_ID, R.STAT_SESSION_ID, R.STATUS_ID,\r\n\t\t\t\t\t" . $DB->DateToCharFunction("R.DATE_CREATE") . "\tDATE_CREATE,\r\n\t\t\t\t\t" . $DB->DateToCharFunction("R.TIMESTAMP_X") . "\tTIMESTAMP_X,\r\n\t\t\t\t\tS.TITLE\t\t\t\tSTATUS_TITLE,\r\n\t\t\t\t\tS.CSS\t\t\t\tSTATUS_CSS\r\n\t\t\t\tFROM \r\n\t\t\t\t\tb_form_result R, \r\n\t\t\t\t\tb_form_status S\r\n\t\t\t\tWHERE \r\n\t\t\t\t{$strSqlSearch}\r\n\t\t\t\t{$strSqlSearch_F}\r\n\t\t\t\tand R.FORM_ID='{$WEB_FORM_ID}'\r\n\t\t\t\tand S.ID = R.STATUS_ID\r\n\t\t\t\tGROUP BY \r\n\t\t\t\t\tR.ID, R.USER_ID, R.USER_AUTH, R.STAT_GUEST_ID, R.STAT_SESSION_ID, R.DATE_CREATE, R.TIMESTAMP_X, R.STATUS_ID, S.ID, S.TITLE, S.CSS\r\n\t\t\t\t{$strSqlOrder}\r\n\t\t\t\t"; if ($records_limit > 0) { $strSql = "SELECT * FROM ({$strSql}) WHERE ROWNUM<=" . $records_limit; } $res = $DB->Query($strSql, false, $err_mess . __LINE__); } elseif ($F_RIGHT >= 15) { $arGroups = $USER->GetUserGroupArray(); if (!is_array($arGroups)) { $arGroups[] = 2; } if (is_array($arGroups) && count($arGroups) > 0) { $groups = implode(",", $arGroups); } if ($F_RIGHT < 20) { $str3 = "and nvl(R.USER_ID,0) = {$USER_ID}"; } $strSql = "\r\n\t\t\t\tSELECT \r\n\t\t\t\t\tR.ID, R.USER_ID, R.USER_AUTH, R.STAT_GUEST_ID, R.STAT_SESSION_ID, R.STATUS_ID,\r\n\t\t\t\t\t" . $DB->DateToCharFunction("R.DATE_CREATE") . "\tDATE_CREATE,\r\n\t\t\t\t\t" . $DB->DateToCharFunction("R.TIMESTAMP_X") . "\tTIMESTAMP_X,\r\n\t\t\t\t\tS.TITLE\t\t\t\tSTATUS_TITLE,\r\n\t\t\t\t\tS.CSS\t\t\t\tSTATUS_CSS\r\n\t\t\t\tFROM \r\n\t\t\t\t\tb_form_result R, \r\n\t\t\t\t\tb_form_status S, \r\n\t\t\t\t\tb_form_status_2_group G\r\n\t\t\t\tWHERE \r\n\t\t\t\t{$strSqlSearch}\r\n\t\t\t\t{$strSqlSearch_F}\r\n\t\t\t\tand R.FORM_ID='{$WEB_FORM_ID}'\r\n\t\t\t\tand S.ID = R.STATUS_ID\r\n\t\t\t\tand G.STATUS_ID = S.ID\r\n\t\t\t\tand (\r\n\t\t\t\t\t(G.GROUP_ID in ({$groups})) or\r\n\t\t\t\t\t(G.GROUP_ID in ({$groups},0) and nvl(R.USER_ID,0) = {$USER_ID} and {$USER_ID}>0)\r\n\t\t\t\t\t)\r\n\t\t\t\tand G.PERMISSION in ('VIEW', 'EDIT', 'DELETE')\r\n\t\t\t\tGROUP BY \r\n\t\t\t\t\tR.ID, R.USER_ID, R.USER_AUTH, R.STAT_GUEST_ID, R.STAT_SESSION_ID, R.DATE_CREATE, R.TIMESTAMP_X, R.STATUS_ID, S.ID, S.TITLE, S.CSS\r\n\t\t\t\t{$strSqlOrder}\r\n\t\t\t\t"; if ($records_limit > 0) { $strSql = "SELECT * FROM ({$strSql}) WHERE ROWNUM<=" . $records_limit; } $res = $DB->Query($strSql, false, $err_mess . __LINE__); } else { $res = new CDBResult(); $res->InitFromArray(array()); } //echo "<pre>".$strSqlSearch."</pre>"; //echo "<pre>".$strSql."</pre>"; $is_filtered = IsFiltered($strSqlSearch) || strlen($strSqlSearch_F) > 0; return $res; }