static function GetList($arOrder = array(), $arFilter = array(), $arSelect = array(), $arOptions = array(), $arNavParams = false)
{
global $DB, $USER_FIELD_MANAGER;
$bEmptySelect = !is_array($arSelect) || empty($arSelect);
if(is_array($arOrder) && is_array($arSelect))
foreach($arOrder as $k => $v)
$arSelect[] = $k;
$obUserFieldsSql = new CUserTypeSQL;
$obUserFieldsSql->SetEntity("CONTROLLER_MEMBER", "M.ID");
$obUserFieldsSql->SetSelect($arSelect);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder($arOrder);
static $arFields = array(
"ID" => array(
"FIELD_NAME" => "M.ID",
"FIELD_TYPE" => "int",
),
"MEMBER_ID" => array(
"FIELD_NAME" => "M.MEMBER_ID",
"FIELD_TYPE" => "string",
),
"SECRET_ID" => array(
"FIELD_NAME" => "M.SECRET_ID",
"FIELD_TYPE" => "string",
),
"NAME" => array(
"FIELD_NAME" => "M.NAME",
"FIELD_TYPE" => "string",
),
"EMAIL" => array(
"FIELD_NAME" => "M.EMAIL",
"FIELD_TYPE" => "string",
),
"CONTACT_PERSON" => array(
"FIELD_NAME" => "M.CONTACT_PERSON",
"FIELD_TYPE" => "string",
),
"URL" => array(
"FIELD_NAME" => "M.URL",
"FIELD_TYPE" => "string",
),
"CONTROLLER_GROUP_ID" => array(
"FIELD_NAME" => "M.CONTROLLER_GROUP_ID",
"FIELD_TYPE" => "int",
),
"CONTROLLER_GROUP_NAME" => array(
"FIELD_NAME" => "G.NAME",
"FIELD_TYPE" => "string",
"TABLE_ALIAS" => "G",
"JOIN" => "INNER JOIN b_controller_group G ON G.ID = M.CONTROLLER_GROUP_ID",
"LEFT_JOIN" => "LEFT JOIN b_controller_group G ON G.ID = M.CONTROLLER_GROUP_ID",
),
"IN_GROUP_FROM" => array(
"FIELD_NAME" => "M.IN_GROUP_FROM",
"FIELD_TYPE" => "datetime",
"FORMAT" => "FULL",
),
"SHARED_KERNEL" => array(
"FIELD_NAME" => "M.SHARED_KERNEL",
"FIELD_TYPE" => "string",
),
"ACTIVE" => array(
"FIELD_NAME" => "M.ACTIVE",
"FIELD_TYPE" => "string",
),
"SITE_ACTIVE" => array(
"FIELD_NAME" => "M.SITE_ACTIVE",
"FIELD_TYPE" => "string",
),
"DISCONNECTED" => array(
"FIELD_NAME" => "M.DISCONNECTED",
"FIELD_TYPE" => "string",
),
"DATE_ACTIVE_FROM" => array(
"FIELD_NAME" => "M.DATE_ACTIVE_FROM",
"FIELD_TYPE" => "datetime",
"FORMAT" => "SHORT",
),
"DATE_ACTIVE_TO" => array(
"FIELD_NAME" => "M.DATE_ACTIVE_TO",
"FIELD_TYPE" => "datetime",
"FORMAT" => "SHORT",
),
"TIMESTAMP_X" => array(
"FIELD_NAME" => "M.TIMESTAMP_X",
"FIELD_TYPE" => "datetime",
"FORMAT" => "FULL",
),
"MODIFIED_BY" => array(
"FIELD_NAME" => "M.MODIFIED_BY",
"FIELD_TYPE" => "int",
),
"MODIFIED_BY_USER" => array(
"FIELD_NAME" => "concat('(', UM.LOGIN, ') ', UM.NAME, ' ', UM.LAST_NAME)",
"FIELD_TYPE" => "string",
"TABLE_ALIAS" => "UM",
"JOIN" => "INNER JOIN b_user UM ON UM.ID = M.MODIFIED_BY",
"LEFT_JOIN" => "LEFT JOIN b_user UM ON UM.ID = M.MODIFIED_BY",
),
"DATE_CREATE" => array(
"FIELD_NAME" => "M.DATE_CREATE",
"FIELD_TYPE" => "datetime",
"FORMAT" => "FULL",
),
"CREATED_BY" => array(
"FIELD_NAME" => "M.CREATED_BY",
"FIELD_TYPE" => "int",
),
"CREATED_BY_USER" => array(
"FIELD_NAME" => "concat('(', UC.LOGIN, ') ', UC.NAME, ' ', UC.LAST_NAME)",
"FIELD_TYPE" => "string",
"TABLE_ALIAS" => "UC",
"JOIN" => "INNER JOIN b_user UC ON UC.ID = M.CREATED_BY",
"LEFT_JOIN" => "LEFT JOIN b_user UC ON UC.ID = M.CREATED_BY",
),
"COUNTER_FREE_SPACE" => array(
"FIELD_NAME" => "M.COUNTER_FREE_SPACE",
"FIELD_TYPE" => "int",
),
"COUNTER_SITES" => array(
"FIELD_NAME" => "M.COUNTER_SITES",
"FIELD_TYPE" => "int",
),
"COUNTER_USERS" => array(
"FIELD_NAME" => "M.COUNTER_USERS",
"FIELD_TYPE" => "int",
),
"COUNTER_LAST_AUTH" => array(
"FIELD_NAME" => "M.COUNTER_LAST_AUTH",
"FIELD_TYPE" => "datetime",
"FORMAT" => "FULL",
),
"COUNTERS_UPDATED" => array(
"FIELD_NAME" => "M.COUNTERS_UPDATED",
"FIELD_TYPE" => "datetime",
"FORMAT" => "FULL",
),
"NOTES" => array(
"FIELD_NAME" => "M.NOTES",
),
"HOSTNAME" => array(
"FIELD_NAME" => "M.HOSTNAME",
"FIELD_TYPE" => "string",
),
);
$rsCounters = CControllerCounter::GetList();
while($arCounter = $rsCounters->Fetch())
{
$arFields["COUNTER_".$arCounter["ID"]] = array(
"FIELD_NAME" => "CCV_".$arCounter["ID"].".".CControllerCounter::GetTypeColumn($arCounter["COUNTER_TYPE"]),
"FIELD_TYPE" => CControllerCounter::GetTypeUserType($arCounter["COUNTER_TYPE"]),
"TABLE_ALIAS" => "CCV_".$arCounter["ID"],
"JOIN" => "INNER JOIN b_controller_counter_value CCV_".$arCounter["ID"]." ON CCV_".$arCounter["ID"].".CONTROLLER_COUNTER_ID = ".$arCounter["ID"]." AND CCV_".$arCounter["ID"].".CONTROLLER_MEMBER_ID = M.ID",
"LEFT_JOIN" => "LEFT JOIN b_controller_counter_value CCV_".$arCounter["ID"]." ON CCV_".$arCounter["ID"].".CONTROLLER_COUNTER_ID = ".$arCounter["ID"]." AND CCV_".$arCounter["ID"].".CONTROLLER_MEMBER_ID = M.ID",
);
}
$obWhere = new CSQLWhere;
$obWhere->SetFields($arFields);
$arDateFields = array();
foreach($arFields as $code => $arField)
if($arField["FIELD_TYPE"] == "datetime")
$arDateFields[] = $code;
$date_field = "/(".implode("|", $arDateFields).")$/";
$arFilterNew = array();
if(is_array($arFilter))
{
foreach($arFilter as $k => $value)
{
if(is_array($value))
{
if(!empty($value))
$arFilterNew[$k] = $value;
}
elseif($value === false)
{
$arFilterNew[$k] = $value;
}
elseif((string)$value <> '')
{
if(array_key_exists("date_format", $arOptions) && preg_match($date_field, $k))
$arFilterNew[$k] = ConvertTimeStamp(MakeTimeStamp($value, $arOptions["date_format"]), "FULL");
else
$arFilterNew[$k] = $value;
}
}
}
$strWhere = "1 = 1";
$r = $obWhere->GetQuery($arFilterNew);
if($r <> '')
$strWhere .= " AND (".$r.") ";
$userFieldsWhere = $obUserFieldsSql->GetFilter();
if($userFieldsWhere)
$strWhere .= " AND (".$userFieldsWhere.") ";
if(is_array($arOrder))
{
foreach($arOrder as $key => $value)
{
$key = mb_strtoupper($key);
if(array_key_exists($key, $arFields) && isset($arFields[$key]["LEFT_JOIN"]))
$obWhere->c_joins[$key]++;
}
}
if($bEmptySelect)
{
$arSelectAdd = array(
"ID",
"MEMBER_ID",
"SECRET_ID",
"NAME",
"URL",
"HOSTNAME",
"EMAIL",
"CONTACT_PERSON",
"CONTROLLER_GROUP_ID",
"DISCONNECTED",
"SHARED_KERNEL",
"ACTIVE",
"DATE_ACTIVE_FROM",
"DATE_ACTIVE_TO",
"SITE_ACTIVE",
"TIMESTAMP_X",
"MODIFIED_BY",
"DATE_CREATE",
"CREATED_BY",
"IN_GROUP_FROM",
"NOTES",
"COUNTER_FREE_SPACE",
"COUNTER_SITES",
"COUNTER_USERS",
"COUNTER_LAST_AUTH",
"COUNTERS_UPDATED",
"MODIFIED_BY_USER",
"CREATED_BY_USER",
);
if(is_array($arSelect))
$arSelect = array_merge($arSelect, $arSelectAdd);
else
$arSelect = $arSelectAdd;
}
$duplicates = array("ID" => 1);
$strSelect = "M.ID AS IDn";
foreach($arSelect as $key)
{
$key = mb_strtoupper($key);
if(array_key_exists($key, $arFields) && !array_key_exists($key, $duplicates))
{
$duplicates[$key]++;
if(isset($arFields[$key]["LEFT_JOIN"]))
$obWhere->c_joins[$key]++;
if($arFields[$key]["FIELD_TYPE"] == "datetime")
{
if(array_key_exists("date_format", $arOptions))
$strSelect .= ",".$DB->DateFormatToDB($arOptions["date_format"], $arFields[$key]["FIELD_NAME"])." AS ".$key."n";
else
$strSelect .= ",".$arFields[$key]["FIELD_NAME"]." AS ".$key."_TMP,".$DB->DateToCharFunction($arFields[$key]["FIELD_NAME"], $arFields[$key]["FORMAT"])." AS ".$key."n";
}
else
$strSelect .= ",".$arFields[$key]["FIELD_NAME"]." AS ".$key."n";
}
}
$strSql = "
SELECT ".($obUserFieldsSql->GetDistinct()? "DISTINCT": "")." ".$strSelect.$obUserFieldsSql->GetSelect()."
FROM b_controller_member M
".$obWhere->GetJoins()."
".$obUserFieldsSql->GetJoin("M.ID")."
WHERE ".$strWhere."
".CControllerAgent::_OrderBy($arOrder, $arFields, $obUserFieldsSql);
if (!is_array($arNavParams))
{
$dbr = $DB->Query($strSql, false, "File: ".__FILE__."
Line: ".__LINE__);
}
elseif ($arNavParams["nTopCount"] > 0)
{
$strSql = $strSql."nLIMIT ".intval($arNavParams["nTopCount"]);
if ($arNavParams["nOffset"] > 0)
{
$strSql .= " OFFSET ".intval($arNavParams["nOffset"]);
}
$dbr = $DB->Query($strSql, false, "File: ".__FILE__."
Line: ".__LINE__);
}
else
{
$strSqlCnt = "
FROM b_controller_member M
".$obWhere->GetJoins()."
".($userFieldsWhere? $obUserFieldsSql->GetJoin("M.ID"): "")."
WHERE ".$strWhere."
";
$res_cnt = $DB->Query("SELECT count('x') CNT ".$strSqlCnt);
$ar_cnt = $res_cnt->Fetch();
if (isset($arNavParams["bOnlyCount"]) && $arNavParams["bOnlyCount"] === true)
{
return $ar_cnt["CNT"];
}
$dbr = new CDBResult();
$dbr->NavQuery($strSql, $ar_cnt["CNT"], $arNavParams);
}
$dbr->is_filtered = $strWhere != "1 = 1";
$dbr->SetUserFields($USER_FIELD_MANAGER->GetUserFields("CONTROLLER_MEMBER"));
return $dbr;
}