public static function GetList($arOrder = Array(), $arFilter = Array())
{
global $DB;
static $arFields = array(
"ID" => Array("FIELD_NAME" => "G.ID", "FIELD_TYPE" => "int"),
"NAME" => Array("FIELD_NAME" => "G.NAME", "FIELD_TYPE" => "string"),
"TIMESTAMP_X" => Array("FIELD_NAME" => "G.TIMESTAMP_X", "FIELD_TYPE" => "datetime"),
"MODIFIED_BY" => Array("FIELD_NAME" => "G.MODIFIED_BY", "FIELD_TYPE" => "int"),
"UPDATE_PERIOD" => Array("FIELD_NAME" => "G.UPDATE_PERIOD", "FIELD_TYPE" => "int"),
"MODIFIED_BY_USER" => Array("FIELD_TYPE" => "string"),
"DATE_CREATE" => Array("FIELD_NAME" => "G.DATE_CREATE", "FIELD_TYPE" => "datetime"),
"CREATED_BY" => Array("FIELD_NAME" => "G.CREATED_BY", "FIELD_TYPE" => "int"),
"CREATED_BY_USER" => Array("FIELD_TYPE" => "string"),
"TRIAL_PERIOD" => Array("FIELD_NAME" => "G.TRIAL_PERIOD", "FIELD_TYPE" => "int"),
"COUNTER_UPDATE_PERIOD" => Array("FIELD_NAME" => "G.COUNTER_UPDATE_PERIOD", "FIELD_TYPE" => "int"),
"CHECK_COUNTER_FREE_SPACE" => Array("FIELD_NAME" => "G.CHECK_COUNTER_FREE_SPACE", "FIELD_TYPE" => "string"),
"CHECK_COUNTER_SITES" => Array("FIELD_NAME" => "G.CHECK_COUNTER_SITES", "FIELD_TYPE" => "string"),
"CHECK_COUNTER_USERS" => Array("FIELD_NAME" => "G.CHECK_COUNTER_USERS", "FIELD_TYPE" => "string"),
"CHECK_COUNTER_LAST_AUTH" => Array("FIELD_NAME" => "G.CHECK_COUNTER_LAST_AUTH", "FIELD_TYPE" => "string"),
);
$arFields["MODIFIED_BY_USER"]["FIELD_NAME"] = $DB->Concat("UM.LOGIN", "UM.NAME", "UM.LAST_NAME");
$arFields["CREATED_BY_USER"]["FIELD_NAME"] = $DB->Concat("UC.LOGIN", "UC.NAME", "UC.LAST_NAME");
$obWhere = new CSQLWhere;
$obWhere->SetFields($arFields);
$arFilterNew = Array();
foreach($arFilter as $k=>$value)
if(strlen($value)>0 || $value === false)
$arFilterNew[$k]=$value;
$strWhere = $obWhere->GetQuery($arFilterNew);
$strSql =
"SELECT G.*, ".
" UC.LOGIN as CREATED_BY_LOGIN, UC.NAME as CREATED_BY_NAME, UC.LAST_NAME as CREATED_BY_LAST_NAME, ".
" UM.LOGIN as MODIFIED_BY_LOGIN, UM.NAME as MODIFIED_BY_NAME, UM.LAST_NAME as MODIFIED_BY_LAST_NAME, ".
" ".$DB->DateToCharFunction("G.TIMESTAMP_X")." as TIMESTAMP_X, ".
" ".$DB->DateToCharFunction("G.DATE_CREATE")." as DATE_CREATE ".
"FROM b_controller_group G ".
" LEFT JOIN b_user UC ON UC.ID=G.CREATED_BY ".
" LEFT JOIN b_user UM ON UM.ID=G.MODIFIED_BY ".
(strlen($strWhere)<=0 ? "" : "WHERE ".$strWhere)." ".
CControllerAgent::_OrderBy($arOrder, $arFields);
$dbr = $DB->Query($strSql);
$dbr->is_filtered = (strlen($strWhere)>0);
return $dbr;
}
public static function GetList($arOrder = array(), $arFilter = array(), $arNavParams = false)
{
/** @global CDatabase $DB */
global $DB;
static $arFields = array("ID" => array("FIELD_NAME" => "L.ID", "FIELD_TYPE" => "int"), "CONTROLLER_MEMBER_ID" => array("FIELD_NAME" => "L.CONTROLLER_MEMBER_ID", "FIELD_TYPE" => "int"), "NAME" => array("FIELD_NAME" => "L.NAME", "FIELD_TYPE" => "string"), "TIMESTAMP_X" => array("FIELD_NAME" => "L.TIMESTAMP_X", "FIELD_TYPE" => "datetime"), "TASK_ID" => array("FIELD_NAME" => "L.TASK_ID", "FIELD_TYPE" => "int"), "TASK_NAME" => array("FIELD_NAME" => "T.TASK_ID", "FIELD_TYPE" => "string", "JOIN" => "INNER JOIN b_controller_task T ON T.ID = L.TASK_ID", "LEFT_JOIN" => "LEFT JOIN b_controller_task T ON T.ID = L.TASK_ID"), "USER_ID" => array("FIELD_NAME" => "L.USER_ID", "FIELD_TYPE" => "int"), "USER_NAME" => array("FIELD_NAME" => "L.USER_ID", "FIELD_TYPE" => "string"), "USER_LAST_NAME" => array("FIELD_NAME" => "U.LAST_NAME", "FIELD_TYPE" => "string", "JOIN" => "INNER JOIN b_user U ON U.ID = L.USER_ID", "LEFT_JOIN" => "LEFT JOIN b_user U ON U.ID = L.USER_ID"), "USER_LOGIN" => array("FIELD_NAME" => "U.LOGIN", "FIELD_TYPE" => "string", "JOIN" => "INNER JOIN b_user U ON U.ID = L.USER_ID", "LEFT_JOIN" => "LEFT JOIN b_user U ON U.ID = L.USER_ID"), "STATUS" => array("FIELD_NAME" => "L.STATUS", "FIELD_TYPE" => "string"), "CONTROLLER_MEMBER_NAME" => array("FIELD_NAME" => "M.NAME", "FIELD_TYPE" => "string", "JOIN" => "INNER JOIN b_controller_member M ON M.ID = L.CONTROLLER_MEMBER_ID", "LEFT_JOIN" => "LEFT JOIN b_controller_member M ON M.ID = L.CONTROLLER_MEMBER_ID"), "CONTROLLER_MEMBER_URL" => array("FIELD_NAME" => "M.URL", "FIELD_TYPE" => "string", "JOIN" => "INNER JOIN b_controller_member M ON M.ID = L.CONTROLLER_MEMBER_ID", "LEFT_JOIN" => "LEFT JOIN b_controller_member M ON M.ID = L.CONTROLLER_MEMBER_ID"));
$obWhere = new CSQLWhere();
$obWhere->SetFields($arFields);
$arFilterNew = array();
foreach ($arFilter as $k => $value) {
if (strlen($value) > 0 || $value === false) {
$arFilterNew[$k] = $value;
}
}
$strWhere = $obWhere->GetQuery($arFilterNew);
$strSelect = "\n\t\t\tSELECT L.*\n\t\t\t\t,M.NAME as CONTROLLER_MEMBER_NAME\n\t\t\t\t,M.URL as CONTROLLER_MEMBER_URL\n\t\t\t\t,U.NAME as USER_NAME\n\t\t\t\t,U.LAST_NAME as USER_LAST_NAME\n\t\t\t\t,U.LOGIN as USER_LOGIN\n\t\t\t\t,T.TASK_ID as TASK_NAME\n\t\t\t\t," . $DB->DateToCharFunction("L.TIMESTAMP_X") . " as TIMESTAMP_X\n\t\t";
$strSql = "\n\t\t\tFROM b_controller_log L\n\t\t\tLEFT JOIN b_controller_member M ON L.CONTROLLER_MEMBER_ID=M.ID\n\t\t\tLEFT JOIN b_controller_task T ON T.ID = L.TASK_ID\n\t\t\tLEFT JOIN b_user U ON U.ID = L.USER_ID\n\t\t\t" . (strlen($strWhere) <= 0 ? "" : "WHERE " . $strWhere) . "\n\t\t";
$strOrder = CControllerAgent::_OrderBy($arOrder, $arFields);
if (is_array($arNavParams) && $arNavParams["nTopCount"] > 0) {
$strSql = $DB->TopSQL($strSelect . $strSql . $strOrder, $arNavParams["nTopCount"]);
$dbr = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
} elseif (is_array($arNavParams)) {
$res_cnt = $DB->Query("\n\t\t\t\tSELECT count('x') CNT\n\t\t\t\tFROM b_controller_log L\n\t\t\t\t" . $obWhere->GetJoins() . "\n\t\t\t\t" . (strlen($strWhere) <= 0 ? "" : "WHERE " . $strWhere) . "\n\t\t\t");
$ar_cnt = $res_cnt->Fetch();
$dbr = new CDBResult();
$dbr->NavQuery($strSelect . $strSql . $strOrder, $ar_cnt["CNT"], $arNavParams);
} else {
$dbr = $DB->Query($strSelect . $strSql . $strOrder, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
$dbr->is_filtered = strlen($strWhere) > 0;
return $dbr;
}
function GetList($arOrder = array(), $arFilter = array())
{
global $DB;
static $arFields = array("ID" => array("FIELD_NAME" => "L.ID", "FIELD_TYPE" => "int"), "CONTROLLER_MEMBER_ID" => array("FIELD_NAME" => "L.CONTROLLER_MEMBER_ID", "FIELD_TYPE" => "int"), "NAME" => array("FIELD_NAME" => "L.NAME", "FIELD_TYPE" => "string"), "TIMESTAMP_X" => array("FIELD_NAME" => "L.TIMESTAMP_X", "FIELD_TYPE" => "datetime"), "TASK_ID" => array("FIELD_NAME" => "L.TASK_ID", "FIELD_TYPE" => "int"), "TASK_NAME" => array("FIELD_NAME" => "T.TASK_ID", "FIELD_TYPE" => "string"), "USER_ID" => array("FIELD_NAME" => "L.USER_ID", "FIELD_TYPE" => "int"), "USER_NAME" => array("FIELD_NAME" => "L.USER_ID", "FIELD_TYPE" => "string"), "USER_LAST_NAME" => array("FIELD_NAME" => "U.LAST_NAME", "FIELD_TYPE" => "string"), "USER_LOGIN" => array("FIELD_NAME" => "U.LOGIN", "FIELD_TYPE" => "string"), "STATUS" => array("FIELD_NAME" => "L.STATUS", "FIELD_TYPE" => "string"), "CONTROLLER_MEMBER_NAME" => array("FIELD_NAME" => "M.NAME", "FIELD_TYPE" => "string"), "CONTROLLER_MEMBER_URL" => array("FIELD_NAME" => "M.URL", "FIELD_TYPE" => "string"));
$obWhere = new CSQLWhere();
$obWhere->SetFields($arFields);
$arFilterNew = array();
foreach ($arFilter as $k => $value) {
if (strlen($value) > 0 || $value === false) {
$arFilterNew[$k] = $value;
}
}
$strWhere = $obWhere->GetQuery($arFilterNew);
$strSql = "SELECT L.*, " . "\tM.NAME as CONTROLLER_MEMBER_NAME, M.URL as CONTROLLER_MEMBER_URL, " . "\tU.NAME as USER_NAME, U.LAST_NAME as USER_LAST_NAME, U.LOGIN as USER_LOGIN, " . "\tT.TASK_ID as TASK_NAME, " . "\t" . $DB->DateToCharFunction("L.TIMESTAMP_X") . "\tas TIMESTAMP_X " . "FROM b_controller_log L " . "\tLEFT JOIN b_controller_member M ON L.CONTROLLER_MEMBER_ID=M.ID " . "\tLEFT JOIN b_controller_task T ON T.ID=L.TASK_ID " . "\tLEFT JOIN b_user U ON U.ID=L.USER_ID " . (strlen($strWhere) <= 0 ? "" : "WHERE " . $strWhere) . " " . CControllerAgent::_OrderBy($arOrder, $arFields);
$dbr = $DB->Query($strSql);
$dbr->is_filtered = strlen($strWhere) > 0;
return $dbr;
}
function GetList($arOrder = array(), $arFilter = array(), $arSelect = array(), $arOptions = array())
{
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_TYPE" => "string"), "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_TYPE" => "string"), "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"));
$arFields["MODIFIED_BY_USER"]["FIELD_NAME"] = $DB->Concat("'('", "UM.LOGIN", "') '", "UM.NAME", "' '", "UM.LAST_NAME");
$arFields["CREATED_BY_USER"]["FIELD_NAME"] = $DB->Concat("'('", "UC.LOGIN", "') '", "UC.NAME", "' '", "UC.LAST_NAME");
$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 (strlen($value) > 0) {
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 (strlen($r) > 0) {
$strWhere .= " AND (" . $r . ") ";
}
$r = $obUserFieldsSql->GetFilter();
if (strlen($r) > 0) {
$strWhere .= " AND (" . $r . ") ";
}
if (is_array($arOrder)) {
foreach ($arOrder as $key => $value) {
$key = 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", "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 ID\n";
foreach ($arSelect as $key) {
$key = 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";
}
}
}
$bUseSubQuery = false;
if ($DB->type == "ORACLE" && $obUserFieldsSql->GetDistinct()) {
$bUseSubQuery = true;
}
if ($bUseSubQuery) {
$ob = new CUserTypeSQL();
$ob->SetEntity("CONTROLLER_MEMBER", "M.ID");
$ob->SetSelect($arSelect);
$ob->SetOrder($arOrder);
$strSql = "\n\t\t\t\tSELECT " . $strSelect . $ob->GetSelect() . "\n\t\t\t\tFROM b_controller_member M\n\t\t\t\t\tLEFT JOIN b_user UC ON UC.ID = M.CREATED_BY\n\t\t\t\t\tLEFT JOIN b_user UM ON UM.ID = M.MODIFIED_BY\n\t\t\t\t\t" . $obWhere->GetJoins() . "\n\t\t\t\t\t" . $ob->GetJoin("M.ID") . "\n\t\t\t\tWHERE M.ID IN (\n\t\t\t\t\tSELECT M.ID\n\t\t\t\t\tFROM b_controller_member M\n\t\t\t\t\t" . $obWhere->GetJoins() . "\n\t\t\t\t\t" . $obUserFieldsSql->GetJoin("M.ID") . "\n\t\t\t\t\tWHERE " . $strWhere . "\n\t\t\t\t)\n\t\t\t\t" . CControllerAgent::_OrderBy($arOrder, $arFields, $ob) . "\n\t\t\t";
} else {
$strSql = "\n\t\t\t\tSELECT " . ($obUserFieldsSql->GetDistinct() ? "DISTINCT" : "") . " " . $strSelect . $obUserFieldsSql->GetSelect() . "\n\t\t\t\tFROM b_controller_member M\n\t\t\t\t\tLEFT JOIN b_user UC ON UC.ID = M.CREATED_BY\n\t\t\t\t\tLEFT JOIN b_user UM ON UM.ID = M.MODIFIED_BY\n\t\t\t\t\t" . $obWhere->GetJoins() . "\n\t\t\t\t\t" . $obUserFieldsSql->GetJoin("M.ID") . "\n\t\t\t\tWHERE " . $strWhere . "\n\t\t\t\t" . CControllerAgent::_OrderBy($arOrder, $arFields, $obUserFieldsSql) . "\n\t\t\t";
}
$dbr = $DB->Query($strSql);
$dbr->is_filtered = $strWhere != "1 = 1";
$dbr->SetUserFields($USER_FIELD_MANAGER->GetUserFields("CONTROLLER_MEMBER"));
return $dbr;
}
function GetList($arOrder = array(), $arFilter = array(), $bCnt = false)
{
global $DB;
static $arFields = array("ID" => array("FIELD_NAME" => "T.ID", "FIELD_TYPE" => "int"), "TIMESTAMP_X" => array("FIELD_NAME" => "T.TIMESTAMP_X", "FIELD_TYPE" => "datetime"), "DATE_CREATE" => array("FIELD_NAME" => "T.DATE_CREATE", "FIELD_TYPE" => "datetime"), "TASK_ID" => array("FIELD_NAME" => "T.TASK_ID", "FIELD_TYPE" => "string"), "CONTROLLER_MEMBER_ID" => array("FIELD_NAME" => "T.CONTROLLER_MEMBER_ID", "FIELD_TYPE" => "int"), "CONTROLLER_MEMBER_NAME" => array("FIELD_NAME" => "M.NAME", "FIELD_TYPE" => "string"), "CONTROLLER_MEMBER_URL" => array("FIELD_NAME" => "M.URL", "FIELD_TYPE" => "string"), "STATUS" => array("FIELD_NAME" => "T.STATUS", "FIELD_TYPE" => "string"), "DATE_EXECUTE" => array("FIELD_NAME" => "T.DATE_EXECUTE", "FIELD_TYPE" => "datetime"));
$obWhere = new CSQLWhere();
$obWhere->SetFields($arFields);
$arFilterNew = array();
foreach ($arFilter as $k => $value) {
if (is_array($value) || strlen($value) > 0 || $value === false) {
$arFilterNew[$k] = $value;
}
}
$strWhere = $obWhere->GetQuery($arFilterNew);
if ($bCnt) {
$strSql = "SELECT COUNT('x') as C, MIN(T.ID) as MIN_ID, MAX(T.ID) as MAX_ID ";
} else {
$strSql = "SELECT T.*, " . "\tM.NAME as CONTROLLER_MEMBER_NAME, M.URL as CONTROLLER_MEMBER_URL, " . "\t" . $DB->DateToCharFunction("T.TIMESTAMP_X") . "\tas TIMESTAMP_X, " . "\t" . $DB->DateToCharFunction("T.DATE_EXECUTE") . "\tas DATE_EXECUTE, " . "\t" . $DB->DateToCharFunction("T.DATE_CREATE") . "\tas DATE_CREATE ";
}
$strSql .= "FROM b_controller_task T " . "\tINNER JOIN b_controller_member M ON T.CONTROLLER_MEMBER_ID=M.ID " . (strlen($strWhere) <= 0 ? "" : "WHERE " . $strWhere) . " " . CControllerAgent::_OrderBy($arOrder, $arFields);
$dbr = $DB->Query($strSql);
$dbr->is_filtered = strlen($strWhere) > 0;
return $dbr;
}
public static function GetList($arOrder = array(), $arFilter = array(), $arSelect = array())
{
global $DB, $USER_FIELD_MANAGER;
$obUserFieldsSql = new CUserTypeSQL();
$obUserFieldsSql->SetEntity("CONTROLLER_GROUP", "G.ID");
$obUserFieldsSql->SetSelect($arSelect);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder($arOrder);
static $arFields = array("ID" => array("FIELD_NAME" => "G.ID", "FIELD_TYPE" => "int"), "NAME" => array("FIELD_NAME" => "G.NAME", "FIELD_TYPE" => "string"), "TIMESTAMP_X" => array("FIELD_NAME" => "G.TIMESTAMP_X", "FIELD_TYPE" => "datetime"), "MODIFIED_BY" => array("FIELD_NAME" => "G.MODIFIED_BY", "FIELD_TYPE" => "int"), "UPDATE_PERIOD" => array("FIELD_NAME" => "G.UPDATE_PERIOD", "FIELD_TYPE" => "int"), "MODIFIED_BY_USER" => array("FIELD_TYPE" => "string"), "DATE_CREATE" => array("FIELD_NAME" => "G.DATE_CREATE", "FIELD_TYPE" => "datetime"), "CREATED_BY" => array("FIELD_NAME" => "G.CREATED_BY", "FIELD_TYPE" => "int"), "CREATED_BY_USER" => array("FIELD_TYPE" => "string"), "TRIAL_PERIOD" => array("FIELD_NAME" => "G.TRIAL_PERIOD", "FIELD_TYPE" => "int"), "COUNTER_UPDATE_PERIOD" => array("FIELD_NAME" => "G.COUNTER_UPDATE_PERIOD", "FIELD_TYPE" => "int"), "CHECK_COUNTER_FREE_SPACE" => array("FIELD_NAME" => "G.CHECK_COUNTER_FREE_SPACE", "FIELD_TYPE" => "string"), "CHECK_COUNTER_SITES" => array("FIELD_NAME" => "G.CHECK_COUNTER_SITES", "FIELD_TYPE" => "string"), "CHECK_COUNTER_USERS" => array("FIELD_NAME" => "G.CHECK_COUNTER_USERS", "FIELD_TYPE" => "string"), "CHECK_COUNTER_LAST_AUTH" => array("FIELD_NAME" => "G.CHECK_COUNTER_LAST_AUTH", "FIELD_TYPE" => "string"));
$arFields["MODIFIED_BY_USER"]["FIELD_NAME"] = $DB->Concat("UM.LOGIN", "UM.NAME", "UM.LAST_NAME");
$arFields["CREATED_BY_USER"]["FIELD_NAME"] = $DB->Concat("UC.LOGIN", "UC.NAME", "UC.LAST_NAME");
$obWhere = new CSQLWhere();
$obWhere->SetFields($arFields);
$arFilterNew = array();
foreach ($arFilter as $k => $value) {
if (strlen($value) > 0 || $value === false) {
$arFilterNew[$k] = $value;
}
}
$strWhere = "1 = 1";
$r = $obWhere->GetQuery($arFilterNew);
if (strlen($r) > 0) {
$strWhere .= " AND (" . $r . ") ";
}
$r = $obUserFieldsSql->GetFilter();
if (strlen($r) > 0) {
$strWhere .= " AND (" . $r . ") ";
}
$strSql = "\n\t\t\tSELECT " . ($obUserFieldsSql->GetDistinct() ? "DISTINCT" : "") . " G.*\n\t\t\t\t,UC.LOGIN as CREATED_BY_LOGIN\n\t\t\t\t,UC.NAME as CREATED_BY_NAME\n\t\t\t\t,UC.LAST_NAME as CREATED_BY_LAST_NAME\n\t\t\t\t,UM.LOGIN as MODIFIED_BY_LOGIN\n\t\t\t\t,UM.NAME as MODIFIED_BY_NAME\n\t\t\t\t,UM.LAST_NAME as MODIFIED_BY_LAST_NAME\n\t\t\t\t," . $DB->DateToCharFunction("G.TIMESTAMP_X") . " as TIMESTAMP_X\n\t\t\t\t," . $DB->DateToCharFunction("G.DATE_CREATE") . " as DATE_CREATE\n\t\t\t\t" . $obUserFieldsSql->GetSelect() . "\n\t\t\tFROM b_controller_group G\n\t\t\t\tLEFT JOIN b_user UC ON UC.ID=G.CREATED_BY\n\t\t\t\tLEFT JOIN b_user UM ON UM.ID=G.MODIFIED_BY\n\t\t\t\t" . $obWhere->GetJoins() . "\n\t\t\t\t" . $obUserFieldsSql->GetJoin("G.ID") . "\n\t\t\tWHERE " . $strWhere . "\n\t\t\t" . CControllerAgent::_OrderBy($arOrder, $arFields, $obUserFieldsSql) . "\n\t\t";
$dbr = $DB->Query($strSql);
$dbr->is_filtered = strlen($strWhere) > 0;
return $dbr;
}
public static function GetList($arOrder = array(), $arFilter = array(), $bCnt = false, $arNavParams = false)
{
global $DB;
static $arFields = array("ID" => array("FIELD_NAME" => "T.ID", "FIELD_TYPE" => "int"), "TIMESTAMP_X" => array("FIELD_NAME" => "T.TIMESTAMP_X", "FIELD_TYPE" => "datetime"), "DATE_CREATE" => array("FIELD_NAME" => "T.DATE_CREATE", "FIELD_TYPE" => "datetime"), "TASK_ID" => array("FIELD_NAME" => "T.TASK_ID", "FIELD_TYPE" => "string"), "CONTROLLER_MEMBER_ID" => array("FIELD_NAME" => "T.CONTROLLER_MEMBER_ID", "FIELD_TYPE" => "int"), "CONTROLLER_MEMBER_NAME" => array("FIELD_NAME" => "M.NAME", "FIELD_TYPE" => "string"), "CONTROLLER_MEMBER_URL" => array("FIELD_NAME" => "M.URL", "FIELD_TYPE" => "string"), "STATUS" => array("FIELD_NAME" => "T.STATUS", "FIELD_TYPE" => "string"), "DATE_EXECUTE" => array("FIELD_NAME" => "T.DATE_EXECUTE", "FIELD_TYPE" => "datetime"));
$obWhere = new CSQLWhere();
$obWhere->SetFields($arFields);
$arFilterNew = array();
foreach ($arFilter as $k => $value) {
if (is_array($value) || strlen($value) > 0 || $value === false) {
$arFilterNew[$k] = $value;
}
}
$strWhere = $obWhere->GetQuery($arFilterNew);
if ($bCnt) {
$strSelect = "\n\t\t\t\tSELECT\n\t\t\t\t\tCOUNT('x') as C\n\t\t\t\t\t,MIN(T.ID) as MIN_ID\n\t\t\t\t\t,MAX(T.ID) as MAX_ID\n\t\t\t";
} else {
$strSelect = "\n\t\t\t\tSELECT T.*\n\t\t\t\t\t,M.NAME as CONTROLLER_MEMBER_NAME, M.URL as CONTROLLER_MEMBER_URL\n\t\t\t\t\t," . $DB->DateToCharFunction("T.TIMESTAMP_X") . "\tas TIMESTAMP_X\n\t\t\t\t\t," . $DB->DateToCharFunction("T.DATE_EXECUTE") . "\tas DATE_EXECUTE\n\t\t\t\t\t," . $DB->DateToCharFunction("T.DATE_CREATE") . "\tas DATE_CREATE\n\t\t\t";
}
$strSql = "\n\t\t\tFROM b_controller_task T\n\t\t\tINNER JOIN b_controller_member M ON T.CONTROLLER_MEMBER_ID = M.ID\n\t\t\t" . (strlen($strWhere) <= 0 ? "" : "WHERE " . $strWhere) . "\n\t\t";
$strOrder = CControllerAgent::_OrderBy($arOrder, $arFields);
if (is_array($arNavParams) && $arNavParams["nTopCount"] > 0) {
$strSql = $DB->TopSQL($strSelect . $strSql . $strOrder, $arNavParams["nTopCount"]);
$dbr = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
} elseif (is_array($arNavParams)) {
$res_cnt = $DB->Query("SELECT count('x') CNT " . $strSql);
$ar_cnt = $res_cnt->Fetch();
$dbr = new CDBResult();
$dbr->NavQuery($strSelect . $strSql . $strOrder, $ar_cnt["CNT"], $arNavParams);
} else {
$dbr = $DB->Query($strSelect . $strSql . $strOrder, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
$dbr->is_filtered = strlen($strWhere) > 0;
return $dbr;
}
