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