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