function NavQuery($arNavParams, $arQuerySelect, $strTableName, $strQueryWhere, $arQueryOrder) { global $DB; if (IntVal($arNavParams["nTopCount"]) <= 0) { $strSql = "\n\t\t\t\tSELECT\n\t\t\t\t\tcount(1) C\n\t\t\t\tFROM\n\t\t\t\t\t" . $strTableName . " t\n\t\t\t"; if ($strQueryWhere) { $strSql .= "\n\t\t\t\t\tWHERE\n\t\t\t\t\t" . $strQueryWhere . "\n\t\t\t\t"; } $res_cnt = $DB->Query($strSql); $res_cnt = $res_cnt->Fetch(); $cnt = $res_cnt["C"]; $strSql = "\n\t\t\t\tSELECT\n\t\t\t\t" . implode(", ", $arQuerySelect) . "\n\t\t\t\tFROM\n\t\t\t\t\t" . $strTableName . " t\n\t\t\t"; if ($strQueryWhere) { $strSql .= "\n\t\t\t\t\tWHERE\n\t\t\t\t\t" . $strQueryWhere . "\n\t\t\t\t"; } if (count($arQueryOrder) > 0) { $strSql .= "\n\t\t\t\t\tORDER BY\n\t\t\t\t\t" . implode(", ", $arQueryOrder) . "\n\t\t\t\t"; } $res = new CDBResult(); $res->NavQuery($strSql, $cnt, $arNavParams); return $res; } else { $strSql = "\n\t\t\t\tSELECT\n\t\t\t\t" . implode(", ", $arQuerySelect) . "\n\t\t\t\tFROM\n\t\t\t\t\t" . $strTableName . " t\n\t\t\t"; if ($strQueryWhere) { $strSql .= "\n\t\t\t\t\tWHERE\n\t\t\t\t\t" . $strQueryWhere . "\n\t\t\t\t"; } if (count($arQueryOrder) > 0) { $strSql .= "\n\t\t\t\t\tORDER BY\n\t\t\t\t\t" . implode(", ", $arQueryOrder) . "\n\t\t\t\t"; } return $DB->Query($DB->TopSql($strSql, intval($arNavParams["nTopCount"]))); } }
public function GetNotifyList($arParams) { global $DB; $iNumPage = 1; if (isset($arParams['PAGE']) && intval($arParams['PAGE']) > 0) { $iNumPage = intval($arParams['PAGE']); } $bTimeZone = isset($arParams['USE_TIME_ZONE']) && $arParams['USE_TIME_ZONE'] == 'N' ? false : true; $sqlStr = "\n\t\t\tSELECT COUNT(M.ID) as CNT\n\t\t\tFROM b_im_relation R\n\t\t\tINNER JOIN b_im_message M ON M.NOTIFY_READ = 'Y' AND M.CHAT_ID = R.CHAT_ID\n\t\t\tWHERE R.USER_ID = " . $this->user_id . " AND R.MESSAGE_TYPE = '" . IM_MESSAGE_SYSTEM . "'\n\t\t"; $res_cnt = $DB->Query($sqlStr); $res_cnt = $res_cnt->Fetch(); $cnt = $res_cnt["CNT"]; $arNotify = array(); if ($cnt > 0) { if (!$bTimeZone) { CTimeZone::Disable(); } $strSql = "\n\t\t\t\tSELECT\n\t\t\t\t\tM.ID,\n\t\t\t\t\tM.CHAT_ID,\n\t\t\t\t\tM.MESSAGE,\n\t\t\t\t\tM.MESSAGE_OUT,\n\t\t\t\t\t" . $DB->DateToCharFunction('M.DATE_CREATE') . " DATE_CREATE,\n\t\t\t\t\tM.NOTIFY_TYPE,\n\t\t\t\t\tM.NOTIFY_MODULE,\n\t\t\t\t\tM.NOTIFY_TITLE,\n\t\t\t\t\tM.NOTIFY_BUTTONS,\n\t\t\t\t\tM.NOTIFY_TAG,\n\t\t\t\t\tM.NOTIFY_SUB_TAG,\n\t\t\t\t\tM.NOTIFY_READ,\n\t\t\t\t\tR.LAST_ID,\n\t\t\t\t\tR.USER_ID TO_USER_ID,\n\t\t\t\t\tM.AUTHOR_ID FROM_USER_ID\n\t\t\t\tFROM b_im_relation R\n\t\t\t\tINNER JOIN b_im_message M ON M.NOTIFY_READ = 'Y' AND M.CHAT_ID = R.CHAT_ID\n\t\t\t\tWHERE R.USER_ID = " . $this->user_id . " AND R.MESSAGE_TYPE = '" . IM_MESSAGE_SYSTEM . "'\n\t\t\t\tORDER BY M.DATE_CREATE DESC, ID DESC\n\t\t\t"; if (!$bTimeZone) { CTimeZone::Enable(); } $dbRes = new CDBResult(); $dbRes->NavQuery($strSql, $cnt, array('iNumPage' => $iNumPage, 'nPageSize' => 20)); while ($arRes = $dbRes->Fetch()) { if ($this->bHideLink) { $arRes['HIDE_LINK'] = 'Y'; } $arNotify[$arRes['ID']] = self::GetFormatNotify($arRes); } } return $arNotify; }
static function GetList($arOrder = array(), $arFilter = array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array()) { global $DB; $arFields = array("ID" => array("FIELD" => "CP.ID", "TYPE" => "int"), "PRODUCT_ID" => array("FIELD" => "CP.PRODUCT_ID", "TYPE" => "int"), "STORE_ID" => array("FIELD" => "CP.STORE_ID", "TYPE" => "int"), "AMOUNT" => array("FIELD" => "CP.AMOUNT", "TYPE" => "double"), "STORE_NAME" => array("FIELD" => "CS.TITLE", "TYPE" => "string", "FROM" => "RIGHT JOIN b_catalog_store CS ON (CS.ID = CP.STORE_ID)"), "STORE_ADDR" => array("FIELD" => "CS.ADDRESS", "TYPE" => "string", "FROM" => "RIGHT JOIN b_catalog_store CS ON (CS.ID = CP.STORE_ID)"), "STORE_DESCR" => array("FIELD" => "CS.DESCRIPTION", "TYPE" => "string", "FROM" => "RIGHT JOIN b_catalog_store CS ON (CS.ID = CP.STORE_ID)"), "STORE_GPS_N" => array("FIELD" => "CS.GPS_N", "TYPE" => "string", "FROM" => "RIGHT JOIN b_catalog_store CS ON (CS.ID = CP.STORE_ID)"), "STORE_GPS_S" => array("FIELD" => "CS.GPS_S", "TYPE" => "string", "FROM" => "RIGHT JOIN b_catalog_store CS ON (CS.ID = CP.STORE_ID)"), "STORE_IMAGE" => array("FIELD" => "CS.IMAGE_ID", "TYPE" => "int", "FROM" => "RIGHT JOIN b_catalog_store CS ON (CS.ID = CP.STORE_ID)"), "STORE_LOCATION" => array("FIELD" => "CS.LOCATION_ID", "TYPE" => "int", "FROM" => "RIGHT JOIN b_catalog_store CS ON (CS.ID = CP.STORE_ID)")); $arSqls = CCatalog::PrepareSql($arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields); $arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "", $arSqls["SELECT"]); if (is_array($arGroupBy) && count($arGroupBy) == 0) { $strSql = "SELECT " . $arSqls["SELECT"] . " " . "FROM b_catalog_store_product CP " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); if ($arRes = $dbRes->Fetch()) { return $arRes["CNT"]; } else { return false; } } $strSql = "SELECT " . $arSqls["SELECT"] . " " . "FROM b_catalog_store_product CP " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } if (strlen($arSqls["ORDERBY"]) > 0) { $strSql .= "ORDER BY " . $arSqls["ORDERBY"] . " "; } if (is_array($arNavStartParams) && intval($arNavStartParams["nTopCount"]) <= 0) { $strSql_tmp = "SELECT COUNT('x') as CNT " . "FROM b_catalog_store_product CP " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql_tmp .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql_tmp .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } $dbRes = $DB->Query($strSql_tmp, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); $cnt = 0; if (strlen($arSqls["GROUPBY"]) <= 0) { if ($arRes = $dbRes->Fetch()) { $cnt = $arRes["CNT"]; } } else { $cnt = $dbRes->SelectedRowsCount(); } $dbRes = new CDBResult(); $dbRes->NavQuery($strSql, $cnt, $arNavStartParams); } else { if (is_array($arNavStartParams) && intval($arNavStartParams["nTopCount"]) > 0) { $strSql .= "LIMIT " . intval($arNavStartParams["nTopCount"]); } $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); } return $dbRes; }
function GetList($arOrder = array(), $arFilter = array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array()) { global $DB; $arFields = array("ID" => array("FIELD" => "CPG.ID", "TYPE" => "int"), "PRODUCT_ID" => array("FIELD" => "CPG.PRODUCT_ID", "TYPE" => "int"), "GROUP_ID" => array("FIELD" => "CPG.GROUP_ID", "TYPE" => "int"), "ACCESS_LENGTH" => array("FIELD" => "CPG.ACCESS_LENGTH", "TYPE" => "int"), "ACCESS_LENGTH_TYPE" => array("FIELD" => "CPG.ACCESS_LENGTH_TYPE", "TYPE" => "char"), "GROUP_ACTIVE" => array("FIELD" => "G.ACTIVE", "TYPE" => "char", "FROM" => "INNER JOIN b_group G ON (CPG.GROUP_ID = G.ID)"), "GROUP_NAME" => array("FIELD" => "G.NAME", "TYPE" => "string", "FROM" => "INNER JOIN b_group G ON (CPG.GROUP_ID = G.ID)")); $arSqls = CCatalog::PrepareSql($arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields); $arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "", $arSqls["SELECT"]); if (is_array($arGroupBy) && empty($arGroupBy)) { $strSql = "SELECT " . $arSqls["SELECT"] . " " . "FROM b_catalog_product2group CPG " . "\t" . $arSqls["FROM"] . " "; if (!empty($arSqls["WHERE"])) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (!empty($arSqls["GROUPBY"])) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); if ($arRes = $dbRes->Fetch()) { return $arRes["CNT"]; } else { return False; } } $strSql = "SELECT " . $arSqls["SELECT"] . " " . "FROM b_catalog_product2group CPG " . "\t" . $arSqls["FROM"] . " "; if (!empty($arSqls["WHERE"])) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (!empty($arSqls["GROUPBY"])) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } if (!empty($arSqls["ORDERBY"])) { $strSql .= "ORDER BY " . $arSqls["ORDERBY"] . " "; } if (is_array($arNavStartParams) && intval($arNavStartParams["nTopCount"]) <= 0) { $strSql_tmp = "SELECT COUNT('x') as CNT " . "FROM b_catalog_product2group CPG " . "\t" . $arSqls["FROM"] . " "; if (!empty($arSqls["WHERE"])) { $strSql_tmp .= "WHERE " . $arSqls["WHERE"] . " "; } if (!empty($arSqls["GROUPBY"])) { $strSql_tmp .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } $dbRes = $DB->Query($strSql_tmp, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); $cnt = 0; if (empty($arSqls["GROUPBY"])) { if ($arRes = $dbRes->Fetch()) { $cnt = $arRes["CNT"]; } } else { $cnt = $dbRes->SelectedRowsCount(); } $dbRes = new CDBResult(); $dbRes->NavQuery($strSql, $cnt, $arNavStartParams); } else { if (is_array($arNavStartParams) && intval($arNavStartParams["nTopCount"]) > 0) { $strSql .= "LIMIT " . intval($arNavStartParams["nTopCount"]); } $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); } return $dbRes; }
static function getList($arOrder = array(), $arFilter = array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array()) { global $DB; $arFields = array("ID" => array("FIELD" => "DE.ID", "TYPE" => "int"), "DOC_ID" => array("FIELD" => "DE.DOC_ID", "TYPE" => "int"), "STORE_FROM" => array("FIELD" => "DE.STORE_FROM", "TYPE" => "int"), "STORE_TO" => array("FIELD" => "DE.STORE_TO", "TYPE" => "int"), "ELEMENT_ID" => array("FIELD" => "DE.ELEMENT_ID", "TYPE" => "int"), "AMOUNT" => array("FIELD" => "DE.AMOUNT", "TYPE" => "double"), "PURCHASING_PRICE" => array("FIELD" => "DE.PURCHASING_PRICE", "TYPE" => "double"), "IS_MULTIPLY_BARCODE" => array("FIELD" => "CP.BARCODE_MULTI", "TYPE" => "int", "FROM" => "INNER JOIN b_catalog_product CP ON (DE.ELEMENT_ID = CP.ID)")); $arSqls = CCatalog::PrepareSql($arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields); $arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "", $arSqls["SELECT"]); if (is_array($arGroupBy) && count($arGroupBy) == 0) { $strSql = "SELECT " . $arSqls["SELECT"] . " " . "FROM b_catalog_docs_element DE " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); if ($arRes = $dbRes->Fetch()) { return $arRes["CNT"]; } else { return false; } } $strSql = "SELECT " . $arSqls["SELECT"] . " " . "FROM b_catalog_docs_element DE " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } if (strlen($arSqls["ORDERBY"]) > 0) { $strSql .= "ORDER BY " . $arSqls["ORDERBY"] . " "; } if (is_array($arNavStartParams) && intval($arNavStartParams["nTopCount"]) <= 0) { $strSql_tmp = "SELECT COUNT('x') as CNT " . "FROM b_catalog_docs_element DE " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql_tmp .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql_tmp .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } $dbRes = $DB->Query($strSql_tmp, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); $cnt = 0; if (strlen($arSqls["GROUPBY"]) <= 0) { if ($arRes = $dbRes->Fetch()) { $cnt = $arRes["CNT"]; } } else { $cnt = $dbRes->SelectedRowsCount(); } $dbRes = new CDBResult(); $dbRes->NavQuery($strSql, $cnt, $arNavStartParams); } else { if (is_array($arNavStartParams) && intval($arNavStartParams["nTopCount"]) > 0) { $strSql .= "LIMIT " . intval($arNavStartParams["nTopCount"]); } $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); } return $dbRes; }
public static function GetList($arOrder = array("ID" => "ASC"), $arFilter = array(), $arAddParams = array()) { global $DB; $arFields = array("ID" => array("FIELD" => "BPP.ID", "TYPE" => "int"), "POST_ID" => array("FIELD" => "BPP.POST_ID", "TYPE" => "int"), "USER_ID" => array("FIELD" => "BPP.USER_ID", "TYPE" => "int"), "NAME" => array("FIELD" => "BPP.NAME", "TYPE" => "string"), "VALUE" => array("FIELD" => "BPP.VALUE", "TYPE" => "string"), "RANK" => $arOrder["OWNER_ID"] > 0 ? array("FIELD" => "RV0.RANK", "TYPE" => "int", "FROM" => "\n\tLEFT JOIN (\n\t\t" . "SELECT MAX(RV2.VOTE_WEIGHT) as VOTE_WEIGHT, RV2.ENTITY_ID \n\t\t" . "FROM b_rating_user RV2 \n\t\t" . "GROUP BY RV2.ENTITY_ID) RV ON (RV.ENTITY_ID = BPP.USER_ID)\n\t" . "LEFT JOIN (\n\t\t" . "SELECT RV1.OWNER_ID, SUM(case when RV1.ID is not null then 1 else 0 end) as RANK \n\t\t" . "FROM b_rating_vote RV1 \n\t\t" . "WHERE RV1.USER_ID = " . $arOrder["OWNER_ID"] . "\n\t\t" . "GROUP BY RV1.OWNER_ID) RV0 ON (RV0.OWNER_ID = BPP.USER_ID)") : array("FIELD" => "RV.RANK", "TYPE" => "string", "FROM" => "\n\tLEFT JOIN (" . "\n\t\tSELECT MAX(RV2.VOTE_WEIGHT) as VOTE_WEIGHT, RV2.ENTITY_ID, 0 as RANK " . "\n\t\tFROM b_rating_user RV2" . "\n\t\tGROUP BY RV2.ENTITY_ID) RV ON (RV.ENTITY_ID = BPP.USER_ID)"), "USER_ACTIVE" => array("FIELD" => "U.ACTIVE", "TYPE" => "string", "FROM" => "\n\tINNER JOIN b_user U ON (BPP.USER_ID = U.ID)"), "USER_NAME" => array("FIELD" => "U.NAME", "TYPE" => "string", "FROM" => "\n\tINNER JOIN b_user U ON (BPP.USER_ID = U.ID)"), "USER_LAST_NAME" => array("FIELD" => "U.LAST_NAME", "TYPE" => "string", "FROM" => "\n\tINNER JOIN b_user U ON (BPP.USER_ID = U.ID)"), "USER_SECOND_NAME" => array("FIELD" => "U.SECOND_NAME", "TYPE" => "string", "FROM" => "\n\tINNER JOIN b_user U ON (BPP.USER_ID = U.ID)"), "USER_LOGIN" => array("FIELD" => "U.LOGIN", "TYPE" => "string", "FROM" => "\n\tINNER JOIN b_user U ON (BPP.USER_ID = U.ID)"), "USER_PERSONAL_PHOTO" => array("FIELD" => "U.PERSONAL_PHOTO", "TYPE" => "string", "FROM" => "\n\tINNER JOIN b_user U ON (BPP.USER_ID = U.ID)")); $arSelect = array_diff(array_keys($arFields), array("RANK")); $arSelect = is_array($arAddParams["SELECT"]) && !empty($arAddParams["SELECT"]) ? array_intersect($arAddParams["SELECT"], $arSelect) : $arSelect; $arSql = CBlog::PrepareSql($arFields, array(), $arFilter, false, $arSelect); $arSql["SELECT"] = str_replace("%%_DISTINCT_%%", "", $arSql["SELECT"]); $iCnt = 0; if ($arAddParams["bCount"] || array_key_exists("bDescPageNumbering", $arAddParams)) { $strSql = "SELECT COUNT(BPP.ID) AS CNT \n" . "FROM b_blog_post_param BPP " . $arSql["FROM"] . "\n" . (empty($arSql["GROUPBY"]) ? "" : "GROUP BY " . $arSql["GROUPBY"] . "\n") . "WHERE " . (empty($arSql["WHERE"]) ? "1 = 1" : $arSql["WHERE"]); $db_res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); if ($arAddParams["bCount"]) { return $db_res; } $iCnt = $db_res && ($res = $db_res->Fetch()) ? intval($res["CNT"]) : 0; } // ORDER BY --> $arSqlOrder = array(); foreach ($arOrder as $by => $order) { $by = strtoupper($by); $order = strtoupper($order) != "ASC" ? "DESC" : "ASK"; if (array_key_exists($by, $arFields) && !array_key_exists($by, $arSqlOrder)) { if (strtoupper($DB->type) == "ORACLE") { $order .= $order == "ASC" ? " NULLS FIRST" : " NULLS LAST"; } if (isset($arFields[$by]["FROM"]) && !empty($arFields[$by]["FROM"]) && strpos($arSql["FROM"], $arFields[$by]["FROM"]) === false) { $arSql["FROM"] .= " " . $arFields[$by]["FROM"]; } if ($by == "RANK") { $arSql["SELECT"] .= ", " . $arFields["RANK"]["FIELD"]; $arSqlOrder[$by] = IsModuleInstalled("intranet") ? "RV.VOTE_WEIGHT " . $order . ", RANK " . $order : "RANK " . $order . ", RV.VOTE_WEIGHT " . $order; } else { $arSqlOrder[$by] = (array_key_exists("ORDER", $arFields[$by]) ? $arFields[$by]["ORDER"] : $arFields[$by]["FIELD"]) . " " . $order; } } } DelDuplicateSort($arSqlOrder); $arSql["ORDERBY"] = implode(", ", $arSqlOrder); // <-- ORDER BY $strSql = "SELECT " . $arSql["SELECT"] . "\n" . "FROM b_blog_post_param BPP" . $arSql["FROM"] . "\n" . "WHERE " . (empty($arSql["WHERE"]) ? "1 = 1" : $arSql["WHERE"]) . (empty($arSql["ORDERBY"]) ? "" : "\nORDER BY " . $arSql["ORDERBY"]); if (is_set($arAddParams, "bDescPageNumbering")) { $db_res = new CDBResult(); $db_res->NavQuery($strSql, $iCnt, $arAddParams); } else { $db_res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); } return $db_res; }
function GetMessagesUsers($userID, $arNavStartParams = false, $online_interval = 120) { global $DB; $userID = IntVal($userID); if ($userID <= 0) { return false; } $strSql = "SELECT U.ID, U.ACTIVE, U.LOGIN, U.NAME, U.LAST_NAME, U.SECOND_NAME, U.PERSONAL_PHOTO, U.PERSONAL_GENDER, COUNT(M.ID) as TOTAL, MAX(M.DATE_CREATE) as MAX_DATE, " . "\tIF(U.LAST_ACTIVITY_DATE > DATE_SUB(NOW(), INTERVAL " . intval($online_interval) . " SECOND), 'Y', 'N') IS_ONLINE, " . "\t" . $DB->DateToCharFunction("MAX(M.DATE_CREATE)", "FULL") . " as MAX_DATE_FORMAT, " . "\tSUM(CASE WHEN M.DATE_VIEW IS NULL AND M.TO_USER_ID = " . $userID . " THEN 1 ELSE 0 END) as UNREAD " . "FROM b_user U, b_sonet_messages M " . "WHERE " . "\t(M.IS_LOG IS NULL OR NOT M.IS_LOG = 'Y') " . "\tAND ( " . "\tM.TO_USER_ID = " . $userID . " " . "\tAND M.FROM_USER_ID = U.ID " . "\tAND M.TO_DELETED = 'N' " . "\tOR " . "\tM.FROM_USER_ID = " . $userID . " " . "\tAND M.TO_USER_ID = U.ID " . "\tAND M.FROM_DELETED = 'N' " . "\t) " . "GROUP BY U.ID, U.NAME, U.LAST_NAME, U.SECOND_NAME, U.PERSONAL_PHOTO, U.PERSONAL_GENDER " . "ORDER BY UNREAD DESC, MAX_DATE DESC "; if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) <= 0) { $strSql_tmp = "SELECT DISTINCT FROM_USER_ID " . "FROM b_sonet_messages M " . "WHERE " . "(M.IS_LOG IS NULL OR NOT M.IS_LOG = 'Y') " . "AND M.TO_USER_ID = " . $userID . " " . "AND M.TO_DELETED = 'N' " . "UNION DISTINCT " . "SELECT DISTINCT TO_USER_ID " . "FROM b_sonet_messages " . "WHERE " . "(IS_LOG IS NULL OR NOT IS_LOG = 'Y') " . "AND FROM_USER_ID = " . $userID . " " . "AND FROM_DELETED = 'N'"; $dbRes = $DB->Query($strSql_tmp, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); $cnt = 0; if ($dbRes) { $cnt = $dbRes->SelectedRowsCount(); } $dbRes = new CDBResult(); $dbRes->NavQuery($strSql, $cnt, $arNavStartParams); } else { if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) > 0) { $strSql .= "LIMIT " . IntVal($arNavStartParams["nTopCount"]); } $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); } return $dbRes; }
/** * <p>Функция возвращает результат выборки записей из заказов в соответствии со своими параметрами.</p> * * * * * @param array $arOrder = array() Массив, в соответствии с которым сортируются результирующие * записи. Массив имеет вид: <pre class="syntax">array( "название_поля1" => * "направление_сортировки1", "название_поля2" => * "направление_сортировки2", . . . )</pre> В качестве "название_поля<i>N</i>" * может стоять любое поле местоположения, а в качестве * "направление_сортировки<i>X</i>" могут быть значения "<i>ASC</i>" (по * возрастанию) и "<i>DESC</i>" (по убыванию).<br><br> Если массив сортировки * имеет несколько элементов, то результирующий набор сортируется * последовательно по каждому элементу (т.е. сначала сортируется по * первому элементу, потом результат сортируется по второму и * т.д.). <br><br> Значение по умолчанию - пустой массив array() - означает, * что результат отсортирован не будет. * * * * @param array $arFilter = array() Массив, в соответствии с которым фильтруются записи групп * свойств. Массив имеет вид: <pre class="syntax">array( * "[модификатор1][оператор1]название_поля1" => "значение1", * "[модификатор2][оператор2]название_поля2" => "значение2", . . . )</pre> * Удовлетворяющие фильтру записи возвращаются в результате, а * записи, которые не удовлетворяют условиям фильтра, * отбрасываются.<br><br> Допустимыми являются следующие модификаторы: * <ul> <li> <b> !</b> - отрицание;</li> <li> <b> +</b> - значения null, 0 и пустая строка * так же удовлетворяют условиям фильтра.</li> </ul> Допустимыми * являются следующие операторы: <ul> <li> <b>>=</b> - значение поля больше * или равно передаваемой в фильтр величины;</li> <li> <b>></b> - значение * поля строго больше передаваемой в фильтр величины;</li> <li><b> - * значение поля меньше или равно передаваемой в фильтр * величины;</b></li> <li><b> - значение поля строго меньше передаваемой в * фильтр величины;</b></li> <li> <b>@</b> - значение поля находится в * передаваемом в фильтр разделенном запятой списке значений;</li> <li> * <b>~</b> - значение поля проверяется на соответствие передаваемому в * фильтр шаблону;</li> <li> <b>%</b> - значение поля проверяется на * соответствие передаваемой в фильтр строке в соответствии с * языком запросов.</li> </ul> В качестве "название_поляX" может стоять * любое поле заказов.<br><br> Пример фильтра: <pre class="syntax">array("!PERSON_TYPE_ID" * => 1)</pre> Этот фильтр означает "выбрать все записи, в которых * значение в поле PERSON_TYPE_ID (код типа плательщика) не равно 1".<br><br> * Значение по умолчанию - пустой массив array() - означает, что * результат отфильтрован не будет. * * * * @param array $arGroupBy = false Массив полей, по которым группируются записи групп свойств. * Массив имеет вид: <pre class="syntax">array("название_поля1", * "группирующая_функция2" => "название_поля2", ...)</pre> В качестве * "название_поля<i>N</i>" может стоять любое поле групп свойств. В * качестве группирующей функции могут стоять: <ul> <li> <b> COUNT</b> - * подсчет количества;</li> <li> <b>AVG</b> - вычисление среднего значения;</li> * <li> <b>MIN</b> - вычисление минимального значения;</li> <li> <b> MAX</b> - * вычисление максимального значения;</li> <li> <b>SUM</b> - вычисление * суммы.</li> </ul> Если массив пустой, то функция вернет число записей, * удовлетворяющих фильтру.<br><br> Значение по умолчанию - <i>false</i> - * означает, что результат группироваться не будет. * * * * @param array $arNavStartParams = false Массив параметров выборки. Может содержать следующие ключи: <ul> * <li>"<b>nTopCount</b>" - количество возвращаемых функцией записей будет * ограничено сверху значением этого ключа;</li> <li> любой ключ, * принимаемый методом <b> CDBResult::NavQuery</b> в качестве третьего * параметра.</li> </ul> Значение по умолчанию - <i>false</i> - означает, что * параметров выборки нет. * * * * @param array $arSelectFields = array() Массив полей записей, которые будут возвращены функцией. Можно * указать только те поля, которые необходимы. Если в массиве * присутствует значение "*", то будут возвращены все доступные * поля.<br><br> Значение по умолчанию - пустой массив array() - означает, * что будут возвращены все поля основной таблицы запроса. * * * * @return CDBResult <p>Возвращается объект класса CDBResult, содержащий набор * ассоциативных массивов параметров групп свойств с ключами:</p> <table * class="tnormal" width="100%"> <tr> <th width="15%">Ключ</th> <th>Описание</th> </tr> <tr> <td>ID</td> * <td>Код группы заказов.</td> </tr> <tr> <td>PERSON_TYPE_ID</td> <td>Тип плательщика.</td> * </tr> <tr> <td>NAME</td> <td>Название группы.</td> </tr> <tr> <td>SORT</td> <td>Индекс * сортировки.</td> </tr> </table> <p>Если в качестве параметра arGroupBy * передается пустой массив, то функция вернет число записей, * удовлетворяющих фильтру.</p> <a name="examples"></a> * * * <h4>Example</h4> * <pre> * <? * // Выведем все группы свойств для плательщика с кодом $PERSON_TYPE * $db_propsGroup = CSaleOrderPropsGroup::GetList( * array("SORT" => "ASC"), * array("PERSON_TYPE_ID" => $PERSON_TYPE), * false, * false, * array() * ); * * while ($propsGroup = $db_propsGroup->Fetch()) * { * echo $propsGroup["NAME"]."<br>"; * } * ?> * </pre> * * * @static * @link http://dev.1c-bitrix.ru/api_help/sale/classes/csaleorderpropsgroup/csaleorderpropsgroup__getlist.7a3426ca.php * @author Bitrix */ public static function GetList($arOrder = array(), $arFilter = array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array()) { global $DB; // To call the old form if (!is_array($arOrder) && !is_array($arFilter)) { $arOrder = strval($arOrder); $arFilter = strval($arFilter); if (strlen($arOrder) > 0 && strlen($arFilter) > 0) { $arOrder = array($arOrder => $arFilter); } else { $arOrder = array(); } if (is_array($arGroupBy)) { $arFilter = $arGroupBy; } else { $arFilter = array(); } $arGroupBy = false; } // FIELDS --> $arFields = array("ID" => array("FIELD" => "PG.ID", "TYPE" => "int"), "PERSON_TYPE_ID" => array("FIELD" => "PG.PERSON_TYPE_ID", "TYPE" => "int"), "NAME" => array("FIELD" => "PG.NAME", "TYPE" => "string"), "SORT" => array("FIELD" => "PG.SORT", "TYPE" => "int")); // <-- FIELDS $arSqls = CSaleOrder::PrepareSql($arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields); $arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "DISTINCT", $arSqls["SELECT"]); if (is_array($arGroupBy) && count($arGroupBy) == 0) { $strSql = "SELECT " . $arSqls["SELECT"] . " " . "FROM b_sale_order_props_group PG " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } //echo "!1!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); if ($arRes = $dbRes->Fetch()) { return $arRes["CNT"]; } else { return False; } } $strSql = "SELECT " . $arSqls["SELECT"] . " " . "FROM b_sale_order_props_group PG " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } if (strlen($arSqls["ORDERBY"]) > 0) { $strSql .= "ORDER BY " . $arSqls["ORDERBY"] . " "; } if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) <= 0) { $strSql_tmp = "SELECT COUNT('x') as CNT " . "FROM b_sale_order_props_group PG " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql_tmp .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql_tmp .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } //echo "!2.1!=".htmlspecialcharsbx($strSql_tmp)."<br>"; $dbRes = $DB->Query($strSql_tmp, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); $cnt = 0; if (strlen($arSqls["GROUPBY"]) <= 0) { if ($arRes = $dbRes->Fetch()) { $cnt = $arRes["CNT"]; } } else { // FOR MYSQL!!! ANOTHER CODE FOR ORACLE $cnt = $dbRes->SelectedRowsCount(); } $dbRes = new CDBResult(); //echo "!2.2!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes->NavQuery($strSql, $cnt, $arNavStartParams); } else { if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) > 0) { $strSql .= "LIMIT " . IntVal($arNavStartParams["nTopCount"]); } //echo "!3!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); } return $dbRes; }
function GetList($arOrder = array(), $arFilter = array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array()) { global $DB; if (!is_array($arOrder) && !is_array($arFilter)) { $arOrder = strval($arOrder); $arFilter = strval($arFilter); if (strlen($arOrder) > 0 && strlen($arFilter) > 0) { $arOrder = array($arOrder => $arFilter); } else { $arOrder = array(); } if (is_array($arGroupBy)) { $arFilter = $arGroupBy; } else { $arFilter = array(); } $arGroupBy = false; $arSelectFields = array("ID", "PERSON_TYPE_ID", "NAME", "TYPE", "REQUIED", "DEFAULT_VALUE", "SORT", "USER_PROPS", "IS_LOCATION", "PROPS_GROUP_ID", "SIZE1", "SIZE2", "DESCRIPTION", "IS_EMAIL", "IS_PROFILE_NAME", "IS_PAYER", "IS_LOCATION4TAX", "IS_ZIP", "CODE", "IS_FILTERED", "ACTIVE", "UTIL", "INPUT_FIELD_LOCATION"); } if (count($arSelectFields) <= 0) { $arSelectFields = array("ID", "PERSON_TYPE_ID", "NAME", "TYPE", "REQUIED", "DEFAULT_VALUE", "SORT", "USER_PROPS", "IS_LOCATION", "PROPS_GROUP_ID", "SIZE1", "SIZE2", "DESCRIPTION", "IS_EMAIL", "IS_PROFILE_NAME", "IS_PAYER", "IS_LOCATION4TAX", "IS_ZIP", "CODE", "IS_FILTERED", "ACTIVE", "UTIL", "INPUT_FIELD_LOCATION"); } // FIELDS --> $arFields = array("ID" => array("FIELD" => "P.ID", "TYPE" => "int"), "PERSON_TYPE_ID" => array("FIELD" => "P.PERSON_TYPE_ID", "TYPE" => "int"), "NAME" => array("FIELD" => "P.NAME", "TYPE" => "string"), "TYPE" => array("FIELD" => "P.TYPE", "TYPE" => "string"), "REQUIED" => array("FIELD" => "P.REQUIED", "TYPE" => "char"), "REQUIRED" => array("FIELD" => "P.REQUIED", "TYPE" => "char"), "DEFAULT_VALUE" => array("FIELD" => "P.DEFAULT_VALUE", "TYPE" => "string"), "SORT" => array("FIELD" => "P.SORT", "TYPE" => "int"), "USER_PROPS" => array("FIELD" => "P.USER_PROPS", "TYPE" => "char"), "IS_LOCATION" => array("FIELD" => "P.IS_LOCATION", "TYPE" => "char"), "PROPS_GROUP_ID" => array("FIELD" => "P.PROPS_GROUP_ID", "TYPE" => "int"), "SIZE1" => array("FIELD" => "P.SIZE1", "TYPE" => "int"), "SIZE2" => array("FIELD" => "P.SIZE2", "TYPE" => "int"), "DESCRIPTION" => array("FIELD" => "P.DESCRIPTION", "TYPE" => "string"), "IS_EMAIL" => array("FIELD" => "P.IS_EMAIL", "TYPE" => "char"), "IS_PROFILE_NAME" => array("FIELD" => "P.IS_PROFILE_NAME", "TYPE" => "char"), "IS_PAYER" => array("FIELD" => "P.IS_PAYER", "TYPE" => "char"), "IS_LOCATION4TAX" => array("FIELD" => "P.IS_LOCATION4TAX", "TYPE" => "char"), "IS_FILTERED" => array("FIELD" => "P.IS_FILTERED", "TYPE" => "char"), "IS_ZIP" => array("FIELD" => "P.IS_ZIP", "TYPE" => "char"), "CODE" => array("FIELD" => "P.CODE", "TYPE" => "string"), "ACTIVE" => array("FIELD" => "P.ACTIVE", "TYPE" => "char"), "UTIL" => array("FIELD" => "P.UTIL", "TYPE" => "char"), "INPUT_FIELD_LOCATION" => array("FIELD" => "P.INPUT_FIELD_LOCATION", "TYPE" => "int"), "GROUP_ID" => array("FIELD" => "PG.ID", "TYPE" => "int", "FROM" => "LEFT JOIN b_sale_order_props_group PG ON (P.PROPS_GROUP_ID = PG.ID)"), "GROUP_PERSON_TYPE_ID" => array("FIELD" => "PG.PERSON_TYPE_ID", "TYPE" => "int", "FROM" => "LEFT JOIN b_sale_order_props_group PG ON (P.PROPS_GROUP_ID = PG.ID)"), "GROUP_NAME" => array("FIELD" => "PG.NAME", "TYPE" => "string", "FROM" => "LEFT JOIN b_sale_order_props_group PG ON (P.PROPS_GROUP_ID = PG.ID)"), "GROUP_SORT" => array("FIELD" => "PG.SORT", "TYPE" => "int", "FROM" => "LEFT JOIN b_sale_order_props_group PG ON (P.PROPS_GROUP_ID = PG.ID)"), "PERSON_TYPE_LID" => array("FIELD" => "SPT.LID", "TYPE" => "string", "FROM" => "LEFT JOIN b_sale_person_type SPT ON (P.PERSON_TYPE_ID = SPT.ID)"), "PERSON_TYPE_NAME" => array("FIELD" => "SPT.NAME", "TYPE" => "string", "FROM" => "LEFT JOIN b_sale_person_type SPT ON (P.PERSON_TYPE_ID = SPT.ID)"), "PERSON_TYPE_SORT" => array("FIELD" => "SPT.SORT", "TYPE" => "int", "FROM" => "LEFT JOIN b_sale_person_type SPT ON (P.PERSON_TYPE_ID = SPT.ID)"), "PERSON_TYPE_ACTIVE" => array("FIELD" => "SPT.ACTIVE", "TYPE" => "char", "FROM" => "LEFT JOIN b_sale_person_type SPT ON (P.PERSON_TYPE_ID = SPT.ID)")); // <-- FIELDS $arSqls = CSaleOrder::PrepareSql($arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields); $arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "DISTINCT", $arSqls["SELECT"]); if (is_array($arGroupBy) && count($arGroupBy) == 0) { $strSql = "SELECT " . $arSqls["SELECT"] . " " . "FROM b_sale_order_props P " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } //echo "!1!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); if ($arRes = $dbRes->Fetch()) { return $arRes["CNT"]; } else { return False; } } $strSql = "SELECT " . $arSqls["SELECT"] . " " . "FROM b_sale_order_props P " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } if (strlen($arSqls["ORDERBY"]) > 0) { $strSql .= "ORDER BY " . $arSqls["ORDERBY"] . " "; } if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) <= 0) { $strSql_tmp = "SELECT COUNT('x') as CNT " . "FROM b_sale_order_props P " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql_tmp .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql_tmp .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } //echo "!2.1!=".htmlspecialcharsbx($strSql_tmp)."<br>"; $dbRes = $DB->Query($strSql_tmp, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); $cnt = 0; if (strlen($arSqls["GROUPBY"]) <= 0) { if ($arRes = $dbRes->Fetch()) { $cnt = $arRes["CNT"]; } } else { // FOR MYSQL!!! ANOTHER CODE FOR ORACLE $cnt = $dbRes->SelectedRowsCount(); } $dbRes = new CDBResult(); //echo "!2.2!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes->NavQuery($strSql, $cnt, $arNavStartParams); } else { if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) > 0) { $strSql .= "LIMIT " . IntVal($arNavStartParams["nTopCount"]); } //echo "!3!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); } return $dbRes; }
public static function GetList($aSort = array(), $arFilter = array(), $arNavStartParams = false) { global $DB; $arSqlSearch = array(); $from1 = ""; if (is_array($arFilter)) { foreach ($arFilter as $key => $val) { if (!is_array($val)) { if (strlen($val) <= 0 || $val === "NOT_REF") { continue; } } switch (strtoupper($key)) { case "ID": $arSqlSearch[] = GetFilterQuery("S.ID", $val, "N"); break; case "EMAIL": $arSqlSearch[] = GetFilterQuery("S.EMAIL", $val, "Y", array("@", ".", "_")); break; case "UPDATE_1": $arSqlSearch[] = "S.DATE_UPDATE>=" . $DB->CharToDateFunction($val); break; case "UPDATE_2": $arSqlSearch[] = "S.DATE_UPDATE<=" . $DB->CharToDateFunction($val . " 23:59:59"); break; case "INSERT_1": $arSqlSearch[] = "S.DATE_INSERT>=" . $DB->CharToDateFunction($val); break; case "INSERT_2": $arSqlSearch[] = "S.DATE_INSERT<=" . $DB->CharToDateFunction($val . " 23:59:59"); break; case "USER": $arSqlSearch[] = GetFilterQuery("U.ID, U.LOGIN, U.NAME, U.LAST_NAME", $val); break; case "USER_ID": $arSqlSearch[] = GetFilterQuery("S.USER_ID", $val, "N"); break; case "ANONYMOUS": $arSqlSearch[] = $val == "Y" ? "(S.USER_ID = 0 or S.USER_ID is null)" : "(S.USER_ID > 0 and S.USER_ID is not null)"; break; case "CONFIRMED": $arSqlSearch[] = $val == "Y" ? "S.CONFIRMED='Y'" : "S.CONFIRMED='N'"; break; case "ACTIVE": $arSqlSearch[] = $val == "Y" ? "S.ACTIVE='Y'" : "S.ACTIVE='N'"; break; case "FORMAT": $arSqlSearch[] = $val == "text" ? "S.FORMAT='text'" : "S.FORMAT='html'"; break; case "RUBRIC": case "RUBRIC_MULTI": case "DISTRIBUTION": if (is_array($val)) { $val = implode(" | ", $val); } if (strlen($val) > 0) { $from1 = " INNER JOIN b_subscription_rubric SR ON (SR.SUBSCRIPTION_ID=S.ID) "; $arSqlSearch[] = GetFilterQuery("SR.LIST_RUBRIC_ID", $val, "N"); } else { $arSqlSearch[] = " 1=0 "; } break; } } } $strSqlSearch = GetFilterSqlSearch($arSqlSearch); $arOrder = array(); foreach ($aSort as $by => $ord) { $by = strtoupper($by); $ord = strtoupper($ord) != "ASC" ? "DESC" : "ASC"; switch ($by) { case "ID": $arOrder[$by] = "S.ID " . $ord; break; case "DATE_INSERT": $arOrder[$by] = "S.DATE_INSERT " . $ord; break; case "DATE_UPDATE": $arOrder[$by] = "S.DATE_UPDATE " . $ord; break; case "DATE_CONFIRM": $arOrder[$by] = "S.DATE_CONFIRM " . $ord; break; case "ACT": $arOrder[$by] = "S.ACTIVE " . $ord; break; case "CONF": $arOrder[$by] = "S.CONFIRMED " . $ord; break; case "EMAIL": $arOrder[$by] = "S.EMAIL " . $ord; break; case "FMT": $arOrder[$by] = "S.FORMAT " . $ord; break; case "USER": $arOrder[$by] = "S.USER_ID " . $ord; break; case "CONFIRM_CODE": $arOrder[$by] = "S.CONFIRM_CODE " . $ord; break; } } if (count($arOrder) <= 0) { $arOrder["ID"] = "S.ID DESC"; } if (is_array($arNavStartParams)) { $strSql = "\n\t\t\t\tSELECT count(" . ($from1 != "" ? "DISTINCT S.ID" : "'x'") . ") as C\n\t\t\t\tFROM\n\t\t\t\t\tb_subscription S\n\t\t\t\t\tLEFT JOIN b_user U ON (S.USER_ID=U.ID)\n\t\t\t\t\t{$from1}\n\t\t\t\tWHERE\n\t\t\t\t" . $strSqlSearch; $res_cnt = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); $res_cnt = $res_cnt->Fetch(); $cnt = $res_cnt["C"]; $strSql = "\n\t\t\t\tSELECT\n\t\t\t\t\tS.ID, S.USER_ID, S.ACTIVE, S.EMAIL, S.FORMAT, S.CONFIRM_CODE, S.CONFIRMED,\n\t\t\t\t\t" . $DB->DateToCharFunction("S.DATE_UPDATE") . " DATE_UPDATE,\n\t\t\t\t\t" . $DB->DateToCharFunction("S.DATE_INSERT") . " DATE_INSERT,\n\t\t\t\t\t" . $DB->DateToCharFunction("S.DATE_CONFIRM") . " DATE_CONFIRM,\n\t\t\t\t\tU.LOGIN USER_LOGIN,\n\t\t\t\t\tU.NAME USER_NAME,\n\t\t\t\t\tU.LAST_NAME USER_LAST_NAME\n\t\t\t\tFROM\n\t\t\t\t\tb_subscription S\n\t\t\t\t\tLEFT JOIN b_user U ON (S.USER_ID=U.ID)\n\t\t\t\t\t{$from1}\n\t\t\t\tWHERE\n\t\t\t\t{$strSqlSearch}\n\t\t\t\t" . ($from1 != "" ? "GROUP BY S.ID, S.USER_ID, S.ACTIVE, S.EMAIL, S.FORMAT, S.CONFIRM_CODE, S.CONFIRMED, S.DATE_CONFIRM, S.DATE_UPDATE, S.DATE_INSERT, U.LOGIN, U.NAME, U.LAST_NAME" : "") . "\n\t\t\t\tORDER BY " . implode(", ", $arOrder); $res = new CDBResult(); $res->NavQuery($strSql, $cnt, $arNavStartParams); $res->is_filtered = IsFiltered($strSqlSearch); return $res; } else { $strSql = "\n\t\t\t\tSELECT\n\t\t\t\t\tS.ID, S.USER_ID, S.ACTIVE, S.EMAIL, S.FORMAT, S.CONFIRM_CODE, S.CONFIRMED,\n\t\t\t\t\t" . $DB->DateToCharFunction("S.DATE_UPDATE") . " DATE_UPDATE,\n\t\t\t\t\t" . $DB->DateToCharFunction("S.DATE_INSERT") . " DATE_INSERT,\n\t\t\t\t\t" . $DB->DateToCharFunction("S.DATE_CONFIRM") . " DATE_CONFIRM,\n\t\t\t\t\tU.LOGIN USER_LOGIN,\n\t\t\t\t\tU.NAME USER_NAME,\n\t\t\t\t\tU.LAST_NAME USER_LAST_NAME\n\t\t\t\tFROM\n\t\t\t\t\tb_subscription S\n\t\t\t\t\tLEFT JOIN b_user U ON (S.USER_ID=U.ID)\n\t\t\t\t\t{$from1}\n\t\t\t\tWHERE\n\t\t\t\t{$strSqlSearch}\n\t\t\t\t" . ($from1 != "" ? "GROUP BY S.ID, S.USER_ID, S.ACTIVE, S.EMAIL, S.FORMAT, S.CONFIRM_CODE, S.CONFIRMED, S.DATE_CONFIRM, S.DATE_UPDATE, S.DATE_INSERT, U.LOGIN, U.NAME, U.LAST_NAME" : "") . "\n\t\t\t\tORDER BY " . implode(", ", $arOrder); $res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); $res->is_filtered = IsFiltered($strSqlSearch); return $res; } }
/** * The function select order history * * @param array $arOrder - array to sort * @param array $arFilter - array to filter * @param array $arGroupBy - array to group records * @param array $arNavStartParams - array to parameters * @param array $arSelectFields - array to selectes fields * @return object $dbRes - object result */ public function GetHistoryList($arOrder = array("ID" => "DESC"), $arFilter = array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array()) { global $DB; if (array_key_exists("H_DATE_INSERT_FROM", $arFilter)) { $val = $arFilter["H_DATE_INSERT_FROM"]; unset($arFilter["H_DATE_INSERT_FROM"]); $arFilter[">=H_DATE_INSERT"] = $val; } if (array_key_exists("H_DATE_INSERT_TO", $arFilter)) { $val = $arFilter["H_DATE_INSERT_TO"]; unset($arFilter["H_DATE_INSERT_TO"]); $arFilter["<=H_DATE_INSERT"] = $val; } if (!$arSelectFields || count($arSelectFields) <= 0 || in_array("*", $arSelectFields)) { $arSelectFields = array("ID", "H_USER_ID", "H_DATE_INSERT", "H_ORDER_ID", "H_CURRENCY", "PERSON_TYPE_ID", "PAYED", "DATE_PAYED", "EMP_PAYED_ID", "CANCELED", "DATE_CANCELED", "REASON_CANCELED", "MARKED", "DATE_MARKED", "REASON_MARKED", "DEDUCTED", "DATE_DEDUCTED", "REASON_UNDO_DEDUCTED", "STATUS_ID", "DATE_STATUS", "PRICE_DELIVERY", "ALLOW_DELIVERY", "DATE_ALLOW_DELIVERY", "PRICE", "CURRENCY", "DISCOUNT_VALUE", "USER_ID", "PAY_SYSTEM_ID", "DELIVERY_ID", "PS_STATUS", "PS_STATUS_CODE", "PS_STATUS_DESCRIPTION", "PS_STATUS_MESSAGE", "PS_SUM", "PS_CURRENCY", "PS_RESPONSE_DATE", "TAX_VALUE", "STAT_GID", "SUM_PAID", "PAY_VOUCHER_NUM", "PAY_VOUCHER_DATE", "AFFILIATE_ID", "DELIVERY_DOC_NUM", "DELIVERY_DOC_DATE"); } $arFields = array("ID" => array("FIELD" => "V.ID", "TYPE" => "int"), "H_ORDER_ID" => array("FIELD" => "V.H_ORDER_ID", "TYPE" => "int"), "H_USER_ID" => array("FIELD" => "V.H_USER_ID", "TYPE" => "int"), "H_DATE_INSERT" => array("FIELD" => "V.H_DATE_INSERT", "TYPE" => "datetime"), "H_CURRENCY" => array("FIELD" => "V.H_CURRENCY", "TYPE" => "string"), "PERSON_TYPE_ID" => array("FIELD" => "V.PERSON_TYPE_ID", "TYPE" => "int"), "PAYED" => array("FIELD" => "V.PAYED", "TYPE" => "char"), "DATE_PAYED" => array("FIELD" => "V.DATE_PAYED", "TYPE" => "datetime"), "EMP_PAYED_ID" => array("FIELD" => "V.EMP_PAYED_ID", "TYPE" => "int"), "CANCELED" => array("FIELD" => "V.CANCELED", "TYPE" => "char"), "DATE_CANCELED" => array("FIELD" => "V.DATE_CANCELED", "TYPE" => "datetime"), "REASON_CANCELED" => array("FIELD" => "V.REASON_CANCELED", "TYPE" => "string"), "MARKED" => array("FIELD" => "V.MARKED", "TYPE" => "char"), "DATE_MARKED" => array("FIELD" => "V.DATE_MARKED", "TYPE" => "datetime"), "REASON_MARKED" => array("FIELD" => "V.REASON_MARKED", "TYPE" => "string"), "DEDUCTED" => array("FIELD" => "V.DEDUCTED", "TYPE" => "char"), "DATE_DEDUCTED" => array("FIELD" => "V.DATE_DEDUCTED", "TYPE" => "datetime"), "REASON_DEDUCTED" => array("FIELD" => "V.REASON_UNDO_DEDUCTED", "TYPE" => "string"), "STATUS_ID" => array("FIELD" => "V.STATUS_ID", "TYPE" => "char"), "DATE_STATUS" => array("FIELD" => "V.DATE_STATUS", "TYPE" => "datetime"), "PAY_VOUCHER_NUM" => array("FIELD" => "V.PAY_VOUCHER_NUM", "TYPE" => "string"), "PAY_VOUCHER_DATE" => array("FIELD" => "V.PAY_VOUCHER_DATE", "TYPE" => "date"), "PRICE_DELIVERY" => array("FIELD" => "V.PRICE_DELIVERY", "TYPE" => "double"), "ALLOW_DELIVERY" => array("FIELD" => "V.ALLOW_DELIVERY", "TYPE" => "char"), "DATE_ALLOW_DELIVERY" => array("FIELD" => "V.DATE_ALLOW_DELIVERY", "TYPE" => "datetime"), "PRICE" => array("FIELD" => "V.PRICE", "TYPE" => "double"), "CURRENCY" => array("FIELD" => "V.CURRENCY", "TYPE" => "string"), "DISCOUNT_VALUE" => array("FIELD" => "V.DISCOUNT_VALUE", "TYPE" => "double"), "SUM_PAID" => array("FIELD" => "V.SUM_PAID", "TYPE" => "double"), "USER_ID" => array("FIELD" => "V.USER_ID", "TYPE" => "int"), "PAY_SYSTEM_ID" => array("FIELD" => "V.PAY_SYSTEM_ID", "TYPE" => "int"), "DELIVERY_ID" => array("FIELD" => "V.DELIVERY_ID", "TYPE" => "string"), "PS_STATUS" => array("FIELD" => "V.PS_STATUS", "TYPE" => "char"), "PS_STATUS_CODE" => array("FIELD" => "V.PS_STATUS_CODE", "TYPE" => "string"), "PS_STATUS_DESCRIPTION" => array("FIELD" => "V.PS_STATUS_DESCRIPTION", "TYPE" => "string"), "PS_STATUS_MESSAGE" => array("FIELD" => "V.PS_STATUS_MESSAGE", "TYPE" => "string"), "PS_SUM" => array("FIELD" => "V.PS_SUM", "TYPE" => "double"), "PS_CURRENCY" => array("FIELD" => "V.PS_CURRENCY", "TYPE" => "string"), "PS_RESPONSE_DATE" => array("FIELD" => "V.PS_RESPONSE_DATE", "TYPE" => "datetime"), "TAX_VALUE" => array("FIELD" => "V.TAX_VALUE", "TYPE" => "double"), "AFFILIATE_ID" => array("FIELD" => "V.AFFILIATE_ID", "TYPE" => "int"), "DELIVERY_DOC_NUM" => array("FIELD" => "V.DELIVERY_DOC_NUM", "TYPE" => "string"), "DELIVERY_DOC_DATE" => array("FIELD" => "V.DELIVERY_DOC_DATE", "TYPE" => "date")); $arSqls = CSaleOrder::PrepareSql($arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields); $arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "", $arSqls["SELECT"]); $strSql = "SELECT " . $arSqls["SELECT"] . " FROM b_sale_order_history V "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } if (strlen($arSqls["ORDERBY"]) > 0) { $strSql .= "ORDER BY " . $arSqls["ORDERBY"] . " "; } if (is_array($arGroupBy) && count($arGroupBy) == 0) { $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); if ($arRes = $dbRes->Fetch()) { return $arRes["CNT"]; } else { return false; } } if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) <= 0) { $strSql_tmp = "SELECT COUNT('x') as CNT FROM b_sale_order_history B "; if (strlen($arSqls["WHERE"]) > 0) { $strSql_tmp .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql_tmp .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } $dbRes = $DB->Query($strSql_tmp, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); $cnt = 0; if (strlen($arSqls["GROUPBY"]) <= 0) { if ($arRes = $dbRes->Fetch()) { $cnt = $arRes["CNT"]; } } else { $cnt = $dbRes->SelectedRowsCount(); } $dbRes = new CDBResult(); $dbRes->NavQuery($strSql, $cnt, $arNavStartParams); } else { $strSql = $DB->TopSql($strSql, $arNavStartParams["nTopCount"]); $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); } return $dbRes; }
public static function PrepareFromQueryData(array $arSql, $tableName, $tableAlias, $dbType, $arNavStartParams = false) { global $DB; $sql = 'SELECT ' . $arSql['SELECT'] . ' FROM ' . $tableName . ' ' . $tableAlias . ' ' . $arSql['FROM'] . ' GROUP BY ' . $arSql['GROUPBY'] . ' ORDER BY ' . $arSql['ORDERBY']; $enableNavigation = is_array($arNavStartParams); $top = $enableNavigation && isset($arNavStartParams['nTopCount']) ? (int) $arNavStartParams['nTopCount'] : 0; if ($enableNavigation && $top <= 0) { if (COption::GetOptionString('crm', 'enable_rough_row_count', 'Y') === 'Y') { $cnt = self::GetRoughRowCount($arSql, $tableName, $tableAlias, $dbType); } else { $cnt = CSqlUtil::GetRowCount($arSql, $tableName, $tableAlias, $dbType); } $dbResult = new CDBResult(); $dbResult->NavQuery($sql, $cnt, $arNavStartParams); return $dbResult; } if ($enableNavigation && $top > 0) { CSqlUtil::PrepareSelectTop($sql, $top, $dbType); } $dbResult = $DB->Query($sql, false, 'File: ' . __FILE__ . '<br/>Line: ' . __LINE__); return $dbResult; }
public static function GetList($arOrder = Array("ID" => "DESC"), $arFilter = Array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array()) { global $DB, $USER_FIELD_MANAGER; $obUserFieldsSql = new CUserTypeSQL; $obUserFieldsSql->SetEntity("BLOG_COMMENT", "C.ID"); $obUserFieldsSql->SetSelect($arSelectFields); $obUserFieldsSql->SetFilter($arFilter); $obUserFieldsSql->SetOrder($arOrder); if (count($arSelectFields) <= 0) $arSelectFields = array("ID", "BLOG_ID", "POST_ID", "PARENT_ID", "AUTHOR_ID", "AUTHOR_NAME", "AUTHOR_EMAIL", "AUTHOR_IP", "AUTHOR_IP1", "TITLE", "POST_TEXT"); if(in_array("*", $arSelectFields)) $arSelectFields = array("ID", "BLOG_ID", "POST_ID", "PARENT_ID", "AUTHOR_ID", "AUTHOR_NAME", "AUTHOR_EMAIL", "AUTHOR_IP", "AUTHOR_IP1", "TITLE", "POST_TEXT", "DATE_CREATE", "USER_LOGIN", "USER_NAME", "USER_LAST_NAME", "USER_SECOND_NAME", "USER_EMAIL", "USER", "BLOG_USER_ALIAS", "BLOG_USER_AVATAR", "BLOG_URL", "BLOG_OWNER_ID", "BLOG_SOCNET_GROUP_ID", "BLOG_ACTIVE", "BLOG_GROUP_ID", "BLOG_GROUP_SITE_ID", "BLOG_USE_SOCNET", "PERMS", "PUBLISH_STATUS"); if((array_key_exists("BLOG_GROUP_SITE_ID", $arFilter) || in_array("BLOG_GROUP_SITE_ID", $arSelectFields)) && !in_array("BLOG_URL", $arSelectFields)) $arSelectFields[] = "BLOG_URL"; // FIELDS --> $arFields = array( "ID" => array("FIELD" => "C.ID", "TYPE" => "int"), "BLOG_ID" => array("FIELD" => "C.BLOG_ID", "TYPE" => "int"), "POST_ID" => array("FIELD" => "C.POST_ID", "TYPE" => "int"), "PARENT_ID" => array("FIELD" => "C.PARENT_ID", "TYPE" => "int"), "AUTHOR_ID" => array("FIELD" => "C.AUTHOR_ID", "TYPE" => "int"), "AUTHOR_NAME" => array("FIELD" => "C.AUTHOR_NAME", "TYPE" => "string"), "AUTHOR_EMAIL" => array("FIELD" => "C.AUTHOR_EMAIL", "TYPE" => "string"), "AUTHOR_IP" => array("FIELD" => "C.AUTHOR_IP", "TYPE" => "string"), "AUTHOR_IP1" => array("FIELD" => "C.AUTHOR_IP1", "TYPE" => "string"), "TITLE" => array("FIELD" => "C.TITLE", "TYPE" => "string"), "POST_TEXT" => array("FIELD" => "C.POST_TEXT", "TYPE" => "string"), "DATE_CREATE" => array("FIELD" => "C.DATE_CREATE", "TYPE" => "datetime"), "DATE_CREATE_TS" => array("FIELD" => "UNIX_TIMESTAMP(C.DATE_CREATE)", "TYPE" => "int"), "PATH" => array("FIELD" => "C.PATH", "TYPE" => "string"), "PUBLISH_STATUS" => array("FIELD" => "C.PUBLISH_STATUS", "TYPE" => "string"), "HAS_PROPS" => array("FIELD" => "C.HAS_PROPS", "TYPE" => "string"), "SHARE_DEST" => array("FIELD" => "C.SHARE_DEST", "TYPE" => "string"), "USER_LOGIN" => array("FIELD" => "U.LOGIN", "TYPE" => "string", "FROM" => "LEFT JOIN b_user U ON (C.AUTHOR_ID = U.ID)"), "USER_NAME" => array("FIELD" => "U.NAME", "TYPE" => "string", "FROM" => "LEFT JOIN b_user U ON (C.AUTHOR_ID = U.ID)"), "USER_LAST_NAME" => array("FIELD" => "U.LAST_NAME", "TYPE" => "string", "FROM" => "LEFT JOIN b_user U ON (C.AUTHOR_ID = U.ID)"), "USER_SECOND_NAME" => array("FIELD" => "U.SECOND_NAME", "TYPE" => "string", "FROM" => "LEFT JOIN b_user U ON (C.AUTHOR_ID = U.ID)"), "USER_EMAIL" => array("FIELD" => "U.EMAIL", "TYPE" => "string", "FROM" => "LEFT JOIN b_user U ON (C.AUTHOR_ID = U.ID)"), "USER" => array("FIELD" => "U.LOGIN,U.NAME,U.LAST_NAME,U.EMAIL,U.ID", "WHERE_ONLY" => "Y", "TYPE" => "string", "FROM" => "LEFT JOIN b_user U ON (C.AUTHOR_ID = U.ID)"), "BLOG_USER_ALIAS" => array("FIELD" => "BU.ALIAS", "TYPE" => "string", "FROM" => "LEFT JOIN b_blog_user BU ON (C.AUTHOR_ID = BU.USER_ID)"), "BLOG_USER_AVATAR" => array("FIELD" => "BU.AVATAR", "TYPE" => "int", "FROM" => "LEFT JOIN b_blog_user BU ON (C.AUTHOR_ID = BU.USER_ID)"), "BLOG_URL" => array("FIELD" => "B.URL", "TYPE" => "string", "FROM" => "INNER JOIN b_blog B ON (C.BLOG_ID = B.ID)"), "BLOG_OWNER_ID" => array("FIELD" => "B.OWNER_ID", "TYPE" => "string", "FROM" => "INNER JOIN b_blog B ON (C.BLOG_ID = B.ID)"), "BLOG_SOCNET_GROUP_ID" => array("FIELD" => "B.SOCNET_GROUP_ID", "TYPE" => "string", "FROM" => "INNER JOIN b_blog B ON (C.BLOG_ID = B.ID)"), "BLOG_ACTIVE" => array("FIELD" => "B.ACTIVE", "TYPE" => "string", "FROM" => "INNER JOIN b_blog B ON (C.BLOG_ID = B.ID)"), "BLOG_GROUP_ID" => array("FIELD" => "B.GROUP_ID", "TYPE" => "int", "FROM" => "INNER JOIN b_blog B ON (C.BLOG_ID = B.ID)"), "BLOG_USE_SOCNET" => array("FIELD" => "B.USE_SOCNET", "TYPE" => "string", "FROM" => "INNER JOIN b_blog B ON (C.BLOG_ID = B.ID)"), "BLOG_NAME" => array("FIELD" => "B.NAME", "TYPE" => "string", "FROM" => "INNER JOIN b_blog B ON (C.BLOG_ID = B.ID)"), "BLOG_GROUP_SITE_ID" => array("FIELD" => "BG.SITE_ID", "TYPE" => "string", "FROM" => " INNER JOIN b_blog BGS ON (C.BLOG_ID = BGS.ID) INNER JOIN b_blog_group BG ON (BGS.GROUP_ID = BG.ID)"), "PERMS" => Array(), "SOCNET_BLOG_READ" => array("FIELD" => "BSR.BLOG_ID", "TYPE" => "int", "FROM" => "INNER JOIN b_blog_socnet BSR ON (C.BLOG_ID = BSR.BLOG_ID)"), "POST_CODE" => array("FIELD" => "BP.CODE", "TYPE" => "string", "FROM" => "INNER JOIN b_blog_post BP ON (C.POST_ID = BP.ID)"), "POST_TITLE" => array("FIELD" => "BP.TITLE", "TYPE" => "string", "FROM" => "INNER JOIN b_blog_post BP ON (C.POST_ID = BP.ID)"), "BLOG_POST_PUBLISH_STATUS" => array("FIELD" => "BP.PUBLISH_STATUS", "TYPE" => "string", "FROM" => "INNER JOIN b_blog_post BP ON (C.POST_ID = BP.ID)"), "BLOG_POST_MICRO" => array("FIELD" => "BP.MICRO", "TYPE" => "string", "FROM" => "INNER JOIN b_blog_post BP ON (C.POST_ID = BP.ID)"), ); if(isset($arFilter["GROUP_CHECK_PERMS"])) { if(is_array($arFilter["GROUP_CHECK_PERMS"])) { foreach($arFilter["GROUP_CHECK_PERMS"] as $val) { if(IntVal($val)>0) { $arFields["POST_PERM_".$val] = Array( "FIELD" => "BUGP".$val.".PERMS", "TYPE" => "string", "FROM" => "LEFT JOIN b_blog_user_group_perms BUGP".$val." ON (C.BLOG_ID = BUGP".$val.".BLOG_ID AND C.POST_ID = BUGP".$val.".POST_ID AND BUGP".$val.".USER_GROUP_ID = ".$val." AND BUGP".$val.".PERMS_TYPE = '".BLOG_PERMS_COMMENT."')" ); $arSelectFields[] = "POST_PERM_".$val; } } } else { if(IntVal($arFilter["GROUP_CHECK_PERMS"])>0) { $arFields["POST_PERM_".$arFilter["GROUP_CHECK_PERMS"]] = Array( "FIELD" => "BUGP.PERMS", "TYPE" => "string", "FROM" => "LEFT JOIN b_blog_user_group_perms BUGP ON (C.BLOG_ID = BUGP.BLOG_ID AND C.POST_ID = BUGP.POST_ID AND BUGP.USER_GROUP_ID = ".$arFilter["GROUP_CHECK_PERMS"]." AND BUGP.PERMS_TYPE = '".BLOG_PERMS_COMMENT."')" ); $arSelectFields[] = "POST_PERM_".$arFilter["GROUP_CHECK_PERMS"]; } } unset($arFilter["GROUP_CHECK_PERMS"]); } // rating variable if ( in_array("RATING_TOTAL_VOTES", $arSelectFields) || in_array("RATING_TOTAL_POSITIVE_VOTES", $arSelectFields) || in_array("RATING_TOTAL_NEGATIVE_VOTES", $arSelectFields) || array_key_exists("RATING_TOTAL_VALUE", $arOrder) || array_key_exists("RATING_TOTAL_VOTES", $arOrder) ) { $arFields["RATING_TOTAL_VALUE"] = array("FIELD" => $DB->IsNull('RV.TOTAL_VALUE', '0'), "TYPE" => "double", "FROM" => "LEFT JOIN b_rating_voting RV ON ( RV.ENTITY_TYPE_ID = 'BLOG_COMMENT' AND RV.ENTITY_ID = C.ID )"); $arFields["RATING_TOTAL_VOTES"] = array("FIELD" => $DB->IsNull('RV.TOTAL_VOTES', '0'), "TYPE" => "int", "FROM" => "LEFT JOIN b_rating_voting RV ON ( RV.ENTITY_TYPE_ID = 'BLOG_COMMENT' AND RV.ENTITY_ID = C.ID )"); $arFields["RATING_TOTAL_POSITIVE_VOTES"] = array("FIELD" => $DB->IsNull('RV.TOTAL_POSITIVE_VOTES', '0'), "TYPE" => "int", "FROM" => "LEFT JOIN b_rating_voting RV ON ( RV.ENTITY_TYPE_ID = 'BLOG_COMMENT' AND RV.ENTITY_ID = C.ID )"); $arFields["RATING_TOTAL_NEGATIVE_VOTES"] = array("FIELD" => $DB->IsNull('RV.TOTAL_NEGATIVE_VOTES', '0'), "TYPE" => "int", "FROM" => "LEFT JOIN b_rating_voting RV ON ( RV.ENTITY_TYPE_ID = 'BLOG_COMMENT' AND RV.ENTITY_ID = C.ID )"); } $bNeedDistinct = false; $blogModulePermissions = $GLOBALS["APPLICATION"]->GetGroupRight("blog"); if ($blogModulePermissions < "W") { $arUserGroups = CBlogUser::GetUserGroups(($GLOBALS["USER"]->IsAuthorized() ? $GLOBALS["USER"]->GetID() : 0), 0, "Y", BLOG_BY_USER_ID); $strUserGroups = "0"; foreach($arUserGroups as $v) $strUserGroups .= ",".IntVal($v); $arFields["PERMS"] = array("FIELD" => "UGP.PERMS", "TYPE" => "char", "FROM" => "INNER JOIN b_blog_user_group_perms UGP ON (C.POST_ID = UGP.POST_ID AND C.BLOG_ID = UGP.BLOG_ID AND UGP.USER_GROUP_ID IN (".$strUserGroups.") AND UGP.PERMS_TYPE = '".BLOG_PERMS_COMMENT."')"); $bNeedDistinct = true; } else { $arFields["PERMS"] = array("FIELD" => "'W'", "TYPE" => "string"); } $arSqls = CBlog::PrepareSql($arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields, $obUserFieldsSql); if(array_key_exists("FOR_USER", $arFilter)) { if(IntVal($arFilter["FOR_USER"]) > 0) //authorized user { $arSqls["FROM"] .= " INNER JOIN b_blog_socnet_rights SR ON (C.POST_ID = SR.POST_ID) " . " LEFT JOIN b_user_access UA ON (UA.ACCESS_CODE = SR.ENTITY AND UA.USER_ID = ".IntVal($arFilter["FOR_USER"]).") "; if(strlen($arSqls["WHERE"]) > 0) $arSqls["WHERE"] .= " AND "; $arSqls["WHERE"] .= " (UA.USER_ID is not NULL OR SR.ENTITY = 'AU') "; } else { $arSqls["FROM"] .= " INNER JOIN b_blog_socnet_rights SR ON (C.POST_ID = SR.POST_ID) ". " INNER JOIN b_user_access UA ON (UA.ACCESS_CODE = SR.ENTITY AND UA.USER_ID = 0)"; } $bNeedDistinct = true; } if($bNeedDistinct) $arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "DISTINCT", $arSqls["SELECT"]); else $arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "", $arSqls["SELECT"]); $r = $obUserFieldsSql->GetFilter(); if(strlen($r)>0) $strSqlUFFilter = " (".$r.") "; if (is_array($arGroupBy) && count($arGroupBy)==0) { $strSql = "SELECT ".$arSqls["SELECT"]." ". $obUserFieldsSql->GetSelect()." ". "FROM b_blog_comment C ". " ".$arSqls["FROM"]." ". $obUserFieldsSql->GetJoin("C.ID")." "; if (strlen($arSqls["WHERE"]) > 0) $strSql .= "WHERE ".$arSqls["WHERE"]." "; if(strlen($arSqls["WHERE"]) > 0 && strlen($strSqlUFFilter) > 0) $strSql .= " AND ".$strSqlUFFilter." "; elseif(strlen($arSqls["WHERE"]) <= 0 && strlen($strSqlUFFilter) > 0) $strSql .= " WHERE ".$strSqlUFFilter." "; if (strlen($arSqls["GROUPBY"]) > 0) $strSql .= "GROUP BY ".$arSqls["GROUPBY"]." "; //echo "!1!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes = $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__); if ($arRes = $dbRes->Fetch()) return $arRes["CNT"]; else return False; } $strSql = "SELECT ".$arSqls["SELECT"]." ". $obUserFieldsSql->GetSelect()." ". "FROM b_blog_comment C ". " ".$arSqls["FROM"]." ". $obUserFieldsSql->GetJoin("C.ID")." "; if (strlen($arSqls["WHERE"]) > 0) $strSql .= "WHERE ".$arSqls["WHERE"]." "; if(strlen($arSqls["WHERE"]) > 0 && strlen($strSqlUFFilter) > 0) $strSql .= " AND ".$strSqlUFFilter." "; elseif(strlen($arSqls["WHERE"]) <= 0 && strlen($strSqlUFFilter) > 0) $strSql .= " WHERE ".$strSqlUFFilter." "; if (strlen($arSqls["GROUPBY"]) > 0) $strSql .= "GROUP BY ".$arSqls["GROUPBY"]." "; if (strlen($arSqls["ORDERBY"]) > 0) $strSql .= "ORDER BY ".$arSqls["ORDERBY"]." "; if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"])<=0) { $strSql_tmp = "SELECT COUNT('x') as CNT ". $obUserFieldsSql->GetSelect()." ". "FROM b_blog_comment C ". " ".$arSqls["FROM"]." ". $obUserFieldsSql->GetJoin("C.ID")." "; if (strlen($arSqls["WHERE"]) > 0) $strSql_tmp .= "WHERE ".$arSqls["WHERE"]." "; if(strlen($arSqls["WHERE"]) > 0 && strlen($strSqlUFFilter) > 0) $strSql_tmp .= " AND ".$strSqlUFFilter." "; elseif(strlen($arSqls["WHERE"]) <= 0 && strlen($strSqlUFFilter) > 0) $strSql_tmp .= " WHERE ".$strSqlUFFilter." "; if (strlen($arSqls["GROUPBY"]) > 0) $strSql_tmp .= "GROUP BY ".$arSqls["GROUPBY"]." "; //echo "!2.1!=".htmlspecialcharsbx($strSql_tmp)."<br>"; $dbRes = $DB->Query($strSql_tmp, false, "File: ".__FILE__."<br>Line: ".__LINE__); $cnt = 0; if (strlen($arSqls["GROUPBY"]) <= 0) { if ($arRes = $dbRes->Fetch()) $cnt = $arRes["CNT"]; } else { $cnt = $dbRes->SelectedRowsCount(); } $dbRes = new CDBResult(); //echo "!2.2!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes->SetUserFields($USER_FIELD_MANAGER->GetUserFields("BLOG_POST")); $dbRes->NavQuery($strSql, $cnt, $arNavStartParams); } else { if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) > 0) $strSql .= "LIMIT ".IntVal($arNavStartParams["nTopCount"]); //echo "!3!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes = $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__); $dbRes->SetUserFields($USER_FIELD_MANAGER->GetUserFields("BLOG_POST")); } return $dbRes; }
function GetListEx($arOrder = array(), $arFilter = array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array()) { global $DB; if (empty($arSelectFields)) { $arSelectFields = array("ID", "NAME", "BASE", "SORT", "NAME_LANG", "XML_ID", "MODIFIED_BY", "CREATED_BY", "DATE_CREATE", "TIMESTAMP_X"); } $arFields = array("ID" => array("FIELD" => "CG.ID", "TYPE" => "int"), "NAME" => array("FIELD" => "CG.NAME", "TYPE" => "string"), "BASE" => array("FIELD" => "CG.BASE", "TYPE" => "char"), "SORT" => array("FIELD" => "CG.SORT", "TYPE" => "int"), "XML_ID" => array("FIELD" => "CG.XML_ID", "TYPE" => "string"), "TIMESTAMP_X" => array("FIELD" => "CG.TIMESTAMP_X", "TYPE" => "datetime"), "MODIFIED_BY" => array("FIELD" => "CG.MODIFIED_BY", "TYPE" => "int"), "DATE_CREATE" => array("FIELD" => "CG.DATE_CREATE", "TYPE" => "datetime"), "CREATED_BY" => array("FIELD" => "CG.CREATED_BY", "TYPE" => "int"), "GROUP_ID" => array("FIELD" => "CG2G.ID", "TYPE" => "int", "FROM" => "INNER JOIN b_catalog_group2group CG2G ON (CG.ID = CG2G.CATALOG_GROUP_ID)"), "GROUP_CATALOG_GROUP_ID" => array("FIELD" => "CG2G.CATALOG_GROUP_ID", "TYPE" => "int", "FROM" => "INNER JOIN b_catalog_group2group CG2G ON (CG.ID = CG2G.CATALOG_GROUP_ID)"), "GROUP_GROUP_ID" => array("FIELD" => "CG2G.GROUP_ID", "TYPE" => "int", "FROM" => "INNER JOIN b_catalog_group2group CG2G ON (CG.ID = CG2G.CATALOG_GROUP_ID)"), "GROUP_BUY" => array("FIELD" => "CG2G.BUY", "TYPE" => "char", "FROM" => "INNER JOIN b_catalog_group2group CG2G ON (CG.ID = CG2G.CATALOG_GROUP_ID)"), "NAME_LANG" => array("FIELD" => "CGL.NAME", "TYPE" => "string", "FROM" => "LEFT JOIN b_catalog_group_lang CGL ON (CG.ID = CGL.CATALOG_GROUP_ID AND CGL.LANG = '" . LANGUAGE_ID . "')")); $arSqls = CCatalog::PrepareSql($arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields); $arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "", $arSqls["SELECT"]); if (empty($arGroupBy) && is_array($arGroupBy)) { $strSql = "SELECT " . $arSqls["SELECT"] . " FROM b_catalog_group CG " . $arSqls["FROM"]; if (!empty($arSqls["WHERE"])) { $strSql .= " WHERE " . $arSqls["WHERE"]; } if (!empty($arSqls["GROUPBY"])) { $strSql .= " GROUP BY " . $arSqls["GROUPBY"]; } if (!empty($arSqls["HAVING"])) { $strSql .= " HAVING " . $arSqls["HAVING"]; } $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); if ($arRes = $dbRes->Fetch()) { return $arRes["CNT"]; } else { return false; } } $strSql = "SELECT " . $arSqls["SELECT"] . " FROM b_catalog_group CG " . $arSqls["FROM"]; if (!empty($arSqls["WHERE"])) { $strSql .= " WHERE " . $arSqls["WHERE"]; } if (!empty($arSqls["GROUPBY"])) { $strSql .= " GROUP BY " . $arSqls["GROUPBY"]; } if (!empty($arSqls["HAVING"])) { $strSql .= " HAVING " . $arSqls["HAVING"]; } if (!empty($arSqls["ORDERBY"])) { $strSql .= " ORDER BY " . $arSqls["ORDERBY"]; } $intTopCount = 0; $boolNavStartParams = !empty($arNavStartParams) && is_array($arNavStartParams); if ($boolNavStartParams && array_key_exists('nTopCount', $arNavStartParams)) { $intTopCount = intval($arNavStartParams["nTopCount"]); } if ($boolNavStartParams && 0 >= $intTopCount) { $strSql_tmp = "SELECT COUNT('x') as CNT FROM b_catalog_group CG " . $arSqls["FROM"]; if (!empty($arSqls["WHERE"])) { $strSql_tmp .= " WHERE " . $arSqls["WHERE"]; } if (!empty($arSqls["GROUPBY"])) { $strSql_tmp .= " GROUP BY " . $arSqls["GROUPBY"]; } if (!empty($arSqls["HAVING"])) { $strSql_tmp .= " HAVING " . $arSqls["HAVING"]; } $dbRes = $DB->Query($strSql_tmp, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); $cnt = 0; if (empty($arSqls["GROUPBY"])) { if ($arRes = $dbRes->Fetch()) { $cnt = $arRes["CNT"]; } } else { $cnt = $dbRes->SelectedRowsCount(); } $dbRes = new CDBResult(); $dbRes->NavQuery($strSql, $cnt, $arNavStartParams); } else { if ($boolNavStartParams && 0 < $intTopCount) { $strSql .= " LIMIT " . $intTopCount; } $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); } return $dbRes; }
/** * <p>Метод возвращает результат выборки записей счетов в соответствии со своими параметрами. Метод динамичный.</p> * * * @param array $arOrder = array() Массив, в соответствии с которым сортируются результирующие * записи. Массив имеет вид: <pre class="syntax">array( "название_поля1" => * "направление_сортировки1", "название_поля2" => * "направление_сортировки2", . . . )</pre> В качестве "название_поля<i>N</i>" * может стоять любое поле счетов, а в качестве * "направление_сортировки<i>X</i>" могут быть значения "<i>ASC</i>" (по * возрастанию) и "<i>DESC</i>" (по убыванию).<br><br> Если массив сортировки * имеет несколько элементов, то результирующий набор сортируется * последовательно по каждому элементу (т.е. сначала сортируется по * первому элементу, потом результат сортируется по второму и * т.д.). <br><br> Значение по умолчанию - пустой массив array() - означает, * что результат отсортирован не будет. * * @param array $arFilter = array() Массив, в соответствии с которым фильтруются записи счетов. * Массив имеет вид: <pre class="syntax">array( * "[модификатор1][оператор1]название_поля1" => "значение1", * "[модификатор2][оператор2]название_поля2" => "значение2", . . . )</pre> * Удовлетворяющие фильтру записи возвращаются в результате, а * записи, которые не удовлетворяют условиям фильтра, * отбрасываются.<br><br> Допустимыми являются следующие модификаторы: * <ul> <li> <b> !</b> - отрицание;</li> <li> <b> +</b> - значения null, 0 и пустая строка * так же удовлетворяют условиям фильтра.</li> </ul> Допустимыми * являются следующие операторы: <ul> <li> <b>>=</b> - значение поля больше * или равно передаваемой в фильтр величины;</li> <li> <b>></b> - значение * поля строго больше передаваемой в фильтр величины;</li> <li> <b><=</b> - * значение поля меньше или равно передаваемой в фильтр величины;</li> * <li> <b><</b> - значение поля строго меньше передаваемой в фильтр * величины;</li> <li> <b>@</b> - значение поля находится в передаваемом в * фильтр разделенном запятой списке значений;</li> <li> <b>~</b> - значение * поля проверяется на соответствие передаваемому в фильтр * шаблону;</li> <li> <b>%</b> - значение поля проверяется на соответствие * передаваемой в фильтр строке в соответствии с языком запросов.</li> * </ul> В качестве "название_поляX" может стоять любое поле * заказов.<br><br> Пример фильтра: <pre class="syntax">array("USER_ID" => 150)</pre> Этот * фильтр означает "выбрать все записи, в которых значение в поле * USER_ID (код пользователя) равно 150".<br><br> Значение по умолчанию - * пустой массив array() - означает, что результат отфильтрован не * будет. * * @param array $arGroupBy = false Массив полей, по которым группируются записи счетов. Массив имеет * вид: <pre class="syntax">array("название_поля1", "группирующая_функция2" => * "название_поля2", ...)</pre> В качестве "название_поля<i>N</i>" может стоять * любое поле счетов. В качестве группирующей функции могут стоять: * <ul> <li> <b> COUNT</b> - подсчет количества;</li> <li> <b>AVG</b> - вычисление * среднего значения;</li> <li> <b>MIN</b> - вычисление минимального * значения;</li> <li> <b> MAX</b> - вычисление максимального значения;</li> <li> * <b>SUM</b> - вычисление суммы.</li> </ul> Если массив пустой, то метод * вернет число записей, удовлетворяющих фильтру.<br><br> Значение по * умолчанию - <i>false</i> - означает, что результат группироваться не * будет. * * @param array $arNavStartParams = false Массив параметров выборки. Может содержать следующие ключи: <ul> * <li>"<b>nTopCount</b>" - количество возвращаемых методом записей будет * ограничено сверху значением этого ключа;</li> <li> любой ключ, * принимаемый методом <b> CDBResult::NavQuery</b> в качестве третьего * параметра.</li> </ul> Значение по умолчанию - <i>false</i> - означает, что * параметров выборки нет. * * @param array $arSelectFields = array() Массив полей записей, которые будут возвращены методом. Можно * указать только те поля, которые необходимы. Если в массиве * присутствует значение "*", то будут возвращены все доступные * поля.<br><br> Значение по умолчанию - пустой массив array() - означает, * что будут возвращены все поля основной таблицы запроса. * * @return CDBResult <p>Возвращается объект класса CDBResult, содержащий набор * ассоциативных массивов параметров счетов:</p> <ul> <li> <b>ID</b> - код * счета;</li> <li> <b>USER_ID</b> - код пользователя-владельца;</li> <li> * <b>CURRENT_BUDGET</b> - текущая сумма на счете;</li> <li> <b>CURRENCY</b> - валюта;</li> <li> * <b>NOTES</b> - текстовое описание;</li> <li> <b>LOCKED</b> - флаг * заблокированности счета;</li> <li> <b>TIMESTAMP_X</b> - дата последнего * изменения;</li> <li> <b>DATE_LOCKED</b> - дата блокировки счета Если в качестве * параметра arGroupBy передается пустой массив, то метод вернет число * записей, удовлетворяющих фильтру.</li> </ul> <a name="examples"></a> * * <h4>Example</h4> * <pre> * <? * // Выберем все счета (в разных валютах) пользователя с кодом 21 * $dbAccountCurrency = CSaleUserAccount::GetList( * array(), * array("USER_ID" => "21"), * false, * false, * array("CURRENT_BUDGET", "CURRENCY") * ); * while ($arAccountCurrency = $dbAccountCurrency->Fetch()) * { * echo "На счете ".$arAccountCurrency["CURRENCY"].": "; * echo SaleFormatCurrency($arAccountCurrency["CURRENT_BUDGET"], * $arAccountCurrency["CURRENCY"])."<br>"; * } * * // Выберем, сумму счетов покупателей (сколько должен магазин покупателям) * $dbAccountCurrency = CSaleUserAccount::GetList( * array("CURRENCY" => "ASC"), * array(), * array("CURRENCY", "SUM" => "CURRENT_BUDGET"), * false, * array("CURRENCY", "SUM" => "CURRENT_BUDGET") * ); * while ($arAccountCurrency = $dbAccountCurrency->Fetch()) * { * echo "В валюте ".$arAccountCurrency["CURRENCY"].": "; * echo SaleFormatCurrency($arAccountCurrency["CURRENT_BUDGET"], * $arAccountCurrency["CURRENCY"])."<br>"; * } * ?> * </pre> * * * @static * @link http://dev.1c-bitrix.ru/api_help/sale/classes/csaleuseraccount/csaleuseraccount.getlist.php * @author Bitrix */ public static function GetList($arOrder = array(), $arFilter = array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array()) { global $DB; if (count($arSelectFields) <= 0) { $arSelectFields = array("ID", "USER_ID", "CURRENT_BUDGET", "CURRENCY", "LOCKED", "NOTES", "TIMESTAMP_X", "DATE_LOCKED"); } // FIELDS --> $arFields = array("ID" => array("FIELD" => "UA.ID", "TYPE" => "int"), "USER_ID" => array("FIELD" => "UA.USER_ID", "TYPE" => "int"), "CURRENT_BUDGET" => array("FIELD" => "UA.CURRENT_BUDGET", "TYPE" => "double"), "CURRENCY" => array("FIELD" => "UA.CURRENCY", "TYPE" => "string"), "LOCKED" => array("FIELD" => "UA.LOCKED", "TYPE" => "char"), "NOTES" => array("FIELD" => "UA.NOTES", "TYPE" => "string"), "TIMESTAMP_X" => array("FIELD" => "UA.TIMESTAMP_X", "TYPE" => "datetime"), "DATE_LOCKED" => array("FIELD" => "UA.DATE_LOCKED", "TYPE" => "datetime"), "USER_LOGIN" => array("FIELD" => "U.LOGIN", "TYPE" => "string", "FROM" => "INNER JOIN b_user U ON (UA.USER_ID = U.ID)"), "USER_ACTIVE" => array("FIELD" => "U.ACTIVE", "TYPE" => "char", "FROM" => "INNER JOIN b_user U ON (UA.USER_ID = U.ID)"), "USER_NAME" => array("FIELD" => "U.NAME", "TYPE" => "string", "FROM" => "INNER JOIN b_user U ON (UA.USER_ID = U.ID)"), "USER_LAST_NAME" => array("FIELD" => "U.LAST_NAME", "TYPE" => "string", "FROM" => "INNER JOIN b_user U ON (UA.USER_ID = U.ID)"), "USER_EMAIL" => array("FIELD" => "U.EMAIL", "TYPE" => "string", "FROM" => "INNER JOIN b_user U ON (UA.USER_ID = U.ID)"), "USER_USER" => array("FIELD" => "U.LOGIN,U.NAME,U.LAST_NAME,U.EMAIL,U.ID", "WHERE_ONLY" => "Y", "TYPE" => "string", "FROM" => "INNER JOIN b_user U ON (UA.USER_ID = U.ID)")); // <-- FIELDS $arSqls = CSaleOrder::PrepareSql($arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields); $arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "", $arSqls["SELECT"]); if (is_array($arGroupBy) && count($arGroupBy) == 0) { $strSql = "SELECT " . $arSqls["SELECT"] . " " . "FROM b_sale_user_account UA " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } //echo "!1!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); if ($arRes = $dbRes->Fetch()) { return $arRes["CNT"]; } else { return False; } } $strSql = "SELECT " . $arSqls["SELECT"] . " " . "FROM b_sale_user_account UA " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } if (strlen($arSqls["ORDERBY"]) > 0) { $strSql .= "ORDER BY " . $arSqls["ORDERBY"] . " "; } if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) <= 0) { $strSql_tmp = "SELECT COUNT('x') as CNT " . "FROM b_sale_user_account UA " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql_tmp .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql_tmp .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } //echo "!2.1!=".htmlspecialcharsbx($strSql_tmp)."<br>"; $dbRes = $DB->Query($strSql_tmp, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); $cnt = 0; if (strlen($arSqls["GROUPBY"]) <= 0) { if ($arRes = $dbRes->Fetch()) { $cnt = $arRes["CNT"]; } } else { // FOR MYSQL!!! ANOTHER CODE FOR ORACLE $cnt = $dbRes->SelectedRowsCount(); } $dbRes = new CDBResult(); //echo "!2.2!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes->NavQuery($strSql, $cnt, $arNavStartParams); } else { if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) > 0) { $strSql .= "LIMIT " . IntVal($arNavStartParams["nTopCount"]); } //echo "!3!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); } return $dbRes; }
function GetList($arOrder = array("SORT" => "ASC"), $arFilter = array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array()) { /* Filter combinations: CHECK_PERMISSIONS="N" - check permissions of the current user to the infoblock MIN_PERMISSION="R" - when permissions check, then minimal access level SHOW_HISTORY="N" - add history items to list SHOW_NEW="N" - if not add history items, then add new, but not published elements */ global $DB, $USER, $APPLICATION; $MAX_LOCK = intval(COption::GetOptionString("workflow", "MAX_LOCK_TIME", "60")); $uid = is_object($USER) ? intval($USER->GetID()) : 0; $arIblockElementFields = array("ID" => "BE.ID", "TIMESTAMP_X" => $DB->DateToCharFunction("BE.TIMESTAMP_X"), "TIMESTAMP_X_UNIX" => 'UNIX_TIMESTAMP(BE.TIMESTAMP_X)', "MODIFIED_BY" => "BE.MODIFIED_BY", "DATE_CREATE" => $DB->DateToCharFunction("BE.DATE_CREATE"), "DATE_CREATE_UNIX" => 'UNIX_TIMESTAMP(BE.DATE_CREATE)', "CREATED_BY" => "BE.CREATED_BY", "IBLOCK_ID" => "BE.IBLOCK_ID", "IBLOCK_SECTION_ID" => "BE.IBLOCK_SECTION_ID", "ACTIVE" => "BE.ACTIVE", "ACTIVE_FROM" => CPageOption::GetOptionString("iblock", "FORMAT_ACTIVE_DATES", "-") != "-" ? $DB->DateToCharFunction("BE.ACTIVE_FROM", CPageOption::GetOptionString("iblock", "FORMAT_ACTIVE_DATES", "SHORT")) : "IF(EXTRACT(HOUR_SECOND FROM BE.ACTIVE_FROM)>0, " . $DB->DateToCharFunction("BE.ACTIVE_FROM", "FULL") . ", " . $DB->DateToCharFunction("BE.ACTIVE_FROM", "SHORT") . ")", "ACTIVE_TO" => CPageOption::GetOptionString("iblock", "FORMAT_ACTIVE_DATES", "-") != "-" ? $DB->DateToCharFunction("BE.ACTIVE_TO", CPageOption::GetOptionString("iblock", "FORMAT_ACTIVE_DATES", "SHORT")) : "IF(EXTRACT(HOUR_SECOND FROM BE.ACTIVE_TO)>0, " . $DB->DateToCharFunction("BE.ACTIVE_TO", "FULL") . ", " . $DB->DateToCharFunction("BE.ACTIVE_TO", "SHORT") . ")", "DATE_ACTIVE_FROM" => CPageOption::GetOptionString("iblock", "FORMAT_ACTIVE_DATES", "-") != "-" ? $DB->DateToCharFunction("BE.ACTIVE_FROM", CPageOption::GetOptionString("iblock", "FORMAT_ACTIVE_DATES", "SHORT")) : "IF(EXTRACT(HOUR_SECOND FROM BE.ACTIVE_FROM)>0, " . $DB->DateToCharFunction("BE.ACTIVE_FROM", "FULL") . ", " . $DB->DateToCharFunction("BE.ACTIVE_FROM", "SHORT") . ")", "DATE_ACTIVE_TO" => CPageOption::GetOptionString("iblock", "FORMAT_ACTIVE_DATES", "-") != "-" ? $DB->DateToCharFunction("BE.ACTIVE_TO", CPageOption::GetOptionString("iblock", "FORMAT_ACTIVE_DATES", "SHORT")) : "IF(EXTRACT(HOUR_SECOND FROM BE.ACTIVE_TO)>0, " . $DB->DateToCharFunction("BE.ACTIVE_TO", "FULL") . ", " . $DB->DateToCharFunction("BE.ACTIVE_TO", "SHORT") . ")", "SORT" => "BE.SORT", "NAME" => "BE.NAME", "PREVIEW_PICTURE" => "BE.PREVIEW_PICTURE", "PREVIEW_TEXT" => "BE.PREVIEW_TEXT", "PREVIEW_TEXT_TYPE" => "BE.PREVIEW_TEXT_TYPE", "DETAIL_PICTURE" => "BE.DETAIL_PICTURE", "DETAIL_TEXT" => "BE.DETAIL_TEXT", "DETAIL_TEXT_TYPE" => "BE.DETAIL_TEXT_TYPE", "SEARCHABLE_CONTENT" => "BE.SEARCHABLE_CONTENT", "WF_STATUS_ID" => "BE.WF_STATUS_ID", "WF_PARENT_ELEMENT_ID" => "BE.WF_PARENT_ELEMENT_ID", "WF_LAST_HISTORY_ID" => "BE.WF_LAST_HISTORY_ID", "WF_NEW" => "BE.WF_NEW", "LOCK_STATUS" => "if (BE.WF_DATE_LOCK is null, 'green', if(DATE_ADD(BE.WF_DATE_LOCK, interval " . $MAX_LOCK . " MINUTE)<now(), 'green', if(BE.WF_LOCKED_BY=" . $uid . ", 'yellow', 'red')))", "WF_LOCKED_BY" => "BE.WF_LOCKED_BY", "WF_DATE_LOCK" => $DB->DateToCharFunction("BE.WF_DATE_LOCK"), "WF_COMMENTS" => "BE.WF_COMMENTS", "IN_SECTIONS" => "BE.IN_SECTIONS", "SHOW_COUNTER" => "BE.SHOW_COUNTER", "SHOW_COUNTER_START" => "BE.SHOW_COUNTER_START", "CODE" => "BE.CODE", "TAGS" => "BE.TAGS", "XML_ID" => "BE.XML_ID", "EXTERNAL_ID" => "BE.XML_ID", "TMP_ID" => "BE.TMP_ID", "USER_NAME" => "concat('(',U.LOGIN,') ',ifnull(U.NAME,''),' ',ifnull(U.LAST_NAME,''))", "LOCKED_USER_NAME" => "concat('(',UL.LOGIN,') ',ifnull(UL.NAME,''),' ',ifnull(UL.LAST_NAME,''))", "CREATED_USER_NAME" => "concat('(',UC.LOGIN,') ',ifnull(UC.NAME,''),' ',ifnull(UC.LAST_NAME,''))", "LANG_DIR" => "L.DIR", "LID" => "B.LID", "IBLOCK_TYPE_ID" => "B.IBLOCK_TYPE_ID", "IBLOCK_CODE" => "B.CODE", "IBLOCK_NAME" => "B.NAME", "IBLOCK_EXTERNAL_ID" => "B.XML_ID", "DETAIL_PAGE_URL" => "B.DETAIL_PAGE_URL", "LIST_PAGE_URL" => "B.LIST_PAGE_URL", "CREATED_DATE" => $DB->DateFormatToDB("YYYY.MM.DD", "BE.DATE_CREATE"), "BP_PUBLISHED" => "if(BE.WF_STATUS_ID = 1, 'Y', 'N')"); $bDistinct = false; CIBlockElement::PrepareGetList($arIblockElementFields, $arJoinProps, $bOnlyCount, $bDistinct, $arSelectFields, $sSelect, $arAddSelectFields, $arFilter, $sWhere, $sSectionWhere, $arAddWhereFields, $arGroupBy, $sGroupBy, $arOrder, $arSqlOrder, $arAddOrderByFields, $arIBlockFilter, $arIBlockMultProps, $arIBlockConvProps, $arIBlockAllProps, $arIBlockNumProps, $arIBlockLongProps); $arFilterIBlocks = isset($arFilter["IBLOCK_ID"]) ? array($arFilter["IBLOCK_ID"]) : array(); //******************FROM PART******************************************** $sFrom = ""; foreach ($arJoinProps["FPS"] as $iblock_id => $iPropCnt) { $sFrom .= "\t\t\tINNER JOIN b_iblock_element_prop_s" . $iblock_id . " FPS" . $iPropCnt . " ON FPS" . $iPropCnt . ".IBLOCK_ELEMENT_ID = BE.ID\n"; $arFilterIBlocks[$iblock_id] = $iblock_id; } foreach ($arJoinProps["FP"] as $propID => $db_prop) { $i = $db_prop["CNT"]; if ($db_prop["bFullJoin"]) { $sFrom .= "\t\t\tINNER JOIN b_iblock_property FP" . $i . " ON FP" . $i . ".IBLOCK_ID = B.ID AND " . (IntVal($propID) > 0 ? " FP" . $i . ".ID=" . IntVal($propID) . "\n" : " FP" . $i . ".CODE='" . $DB->ForSQL($propID, 200) . "'\n"); } else { $sFrom .= "\t\t\tLEFT JOIN b_iblock_property FP" . $i . " ON FP" . $i . ".IBLOCK_ID = B.ID AND " . (IntVal($propID) > 0 ? " FP" . $i . ".ID=" . IntVal($propID) . "\n" : " FP" . $i . ".CODE='" . $DB->ForSQL($propID, 200) . "'\n"); } if ($db_prop["IBLOCK_ID"]) { $arFilterIBlocks[$db_prop["IBLOCK_ID"]] = $db_prop["IBLOCK_ID"]; } } foreach ($arJoinProps["FPV"] as $propID => $db_prop) { $i = $db_prop["CNT"]; if ($db_prop["MULTIPLE"] == "Y") { $bDistinct = true; } if ($db_prop["VERSION"] == 2) { $strTable = "b_iblock_element_prop_m" . $db_prop["IBLOCK_ID"]; } else { $strTable = "b_iblock_element_property"; } if ($db_prop["bFullJoin"]) { $sFrom .= "\t\t\tINNER JOIN " . $strTable . " FPV" . $i . " ON FPV" . $i . ".IBLOCK_PROPERTY_ID = FP" . $db_prop["JOIN"] . ".ID AND FPV" . $i . ".IBLOCK_ELEMENT_ID = BE.ID\n"; } else { $sFrom .= "\t\t\tLEFT JOIN " . $strTable . " FPV" . $i . " ON FPV" . $i . ".IBLOCK_PROPERTY_ID = FP" . $db_prop["JOIN"] . ".ID AND FPV" . $i . ".IBLOCK_ELEMENT_ID = BE.ID\n"; } if ($db_prop["IBLOCK_ID"]) { $arFilterIBlocks[$db_prop["IBLOCK_ID"]] = $db_prop["IBLOCK_ID"]; } } foreach ($arJoinProps["FPEN"] as $propID => $db_prop) { $i = $db_prop["CNT"]; if ($db_prop["VERSION"] == 2 && $db_prop["MULTIPLE"] == "N") { if ($db_prop["bFullJoin"]) { $sFrom .= "\t\t\tINNER JOIN b_iblock_property_enum FPEN" . $i . " ON FPEN" . $i . ".PROPERTY_ID = " . $db_prop["ORIG_ID"] . " AND FPS" . $db_prop["JOIN"] . ".PROPERTY_" . $db_prop["ORIG_ID"] . " = FPEN" . $i . ".ID\n"; } else { $sFrom .= "\t\t\tLEFT JOIN b_iblock_property_enum FPEN" . $i . " ON FPEN" . $i . ".PROPERTY_ID = " . $db_prop["ORIG_ID"] . " AND FPS" . $db_prop["JOIN"] . ".PROPERTY_" . $db_prop["ORIG_ID"] . " = FPEN" . $i . ".ID\n"; } } else { if ($db_prop["bFullJoin"]) { $sFrom .= "\t\t\tINNER JOIN b_iblock_property_enum FPEN" . $i . " ON FPEN" . $i . ".PROPERTY_ID = FPV" . $db_prop["JOIN"] . ".IBLOCK_PROPERTY_ID AND FPV" . $db_prop["JOIN"] . ".VALUE_ENUM = FPEN" . $i . ".ID\n"; } else { $sFrom .= "\t\t\tLEFT JOIN b_iblock_property_enum FPEN" . $i . " ON FPEN" . $i . ".PROPERTY_ID = FPV" . $db_prop["JOIN"] . ".IBLOCK_PROPERTY_ID AND FPV" . $db_prop["JOIN"] . ".VALUE_ENUM = FPEN" . $i . ".ID\n"; } } if ($db_prop["IBLOCK_ID"]) { $arFilterIBlocks[$db_prop["IBLOCK_ID"]] = $db_prop["IBLOCK_ID"]; } } foreach ($arJoinProps["BE"] as $propID => $db_prop) { $i = $db_prop["CNT"]; $sFrom .= "\t\t\tLEFT JOIN b_iblock_element BE" . $i . " ON BE" . $i . ".ID = " . ($db_prop["VERSION"] == 2 && $db_prop["MULTIPLE"] == "N" ? "FPS" . $db_prop["JOIN"] . ".PROPERTY_" . $db_prop["ORIG_ID"] : "FPV" . $db_prop["JOIN"] . ".VALUE_NUM") . ($arFilter["SHOW_HISTORY"] != "Y" ? " AND ((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)" . ($arFilter["SHOW_NEW"] == "Y" ? " OR BE.WF_NEW='Y'" : "") . ")" : "") . "\n"; if ($db_prop["bJoinIBlock"]) { $sFrom .= "\t\t\tLEFT JOIN b_iblock B" . $i . " ON B" . $i . ".ID = BE" . $i . ".IBLOCK_ID\n"; } if ($db_prop["bJoinSection"]) { $sFrom .= "\t\t\tLEFT JOIN b_iblock_section BS" . $i . " ON BS" . $i . ".ID = BE" . $i . ".IBLOCK_SECTION_ID\n"; } if ($db_prop["IBLOCK_ID"]) { $arFilterIBlocks[$db_prop["IBLOCK_ID"]] = $db_prop["IBLOCK_ID"]; } } foreach ($arJoinProps["BE_FPS"] as $iblock_id => $db_prop) { $sFrom .= "\t\t\tLEFT JOIN b_iblock_element_prop_s" . $iblock_id . " JFPS" . $db_prop["CNT"] . " ON JFPS" . $db_prop["CNT"] . ".IBLOCK_ELEMENT_ID = BE" . $db_prop["JOIN"] . ".ID\n"; if ($db_prop["IBLOCK_ID"]) { $arFilterIBlocks[$db_prop["IBLOCK_ID"]] = $db_prop["IBLOCK_ID"]; } } foreach ($arJoinProps["BE_FP"] as $propID => $db_prop) { $i = $db_prop["CNT"]; list($propID, $link) = explode("~", $propID, 2); if ($db_prop["bFullJoin"]) { $sFrom .= "\t\t\tINNER JOIN b_iblock_property JFP" . $i . " ON JFP" . $i . ".IBLOCK_ID = BE" . $db_prop["JOIN"] . ".IBLOCK_ID AND " . (IntVal($propID) > 0 ? " JFP" . $i . ".ID=" . IntVal($propID) . "\n" : " JFP" . $i . ".CODE='" . $DB->ForSQL($propID, 200) . "'\n"); } else { $sFrom .= "\t\t\tLEFT JOIN b_iblock_property JFP" . $i . " ON JFP" . $i . ".IBLOCK_ID = BE" . $db_prop["JOIN"] . ".IBLOCK_ID AND " . (IntVal($propID) > 0 ? " JFP" . $i . ".ID=" . IntVal($propID) . "\n" : " JFP" . $i . ".CODE='" . $DB->ForSQL($propID, 200) . "'\n"); } if ($db_prop["IBLOCK_ID"]) { $arFilterIBlocks[$db_prop["IBLOCK_ID"]] = $db_prop["IBLOCK_ID"]; } } foreach ($arJoinProps["BE_FPV"] as $propID => $db_prop) { $i = $db_prop["CNT"]; list($propID, $link) = explode("~", $propID, 2); if ($db_prop["MULTIPLE"] == "Y") { $bDistinct = true; } if ($db_prop["VERSION"] == 2) { $strTable = "b_iblock_element_prop_m" . $db_prop["IBLOCK_ID"]; } else { $strTable = "b_iblock_element_property"; } if ($db_prop["bFullJoin"]) { $sFrom .= "\t\t\tINNER JOIN " . $strTable . " JFPV" . $i . " ON JFPV" . $i . ".IBLOCK_PROPERTY_ID = JFP" . $db_prop["JOIN"] . ".ID AND JFPV" . $i . ".IBLOCK_ELEMENT_ID = BE" . $db_prop["BE_JOIN"] . ".ID\n"; } else { $sFrom .= "\t\t\tLEFT JOIN " . $strTable . " JFPV" . $i . " ON JFPV" . $i . ".IBLOCK_PROPERTY_ID = JFP" . $db_prop["JOIN"] . ".ID AND JFPV" . $i . ".IBLOCK_ELEMENT_ID = BE" . $db_prop["BE_JOIN"] . ".ID\n"; } if ($db_prop["IBLOCK_ID"]) { $arFilterIBlocks[$db_prop["IBLOCK_ID"]] = $db_prop["IBLOCK_ID"]; } } foreach ($arJoinProps["BE_FPEN"] as $propID => $db_prop) { $i = $db_prop["CNT"]; list($propID, $link) = explode("~", $propID, 2); if ($db_prop["VERSION"] == 2 && $db_prop["MULTIPLE"] == "N") { if ($db_prop["bFullJoin"]) { $sFrom .= "\t\t\tINNER JOIN b_iblock_property_enum JFPEN" . $i . " ON JFPEN" . $i . ".PROPERTY_ID = " . $db_prop["ORIG_ID"] . " AND JFPS" . $db_prop["JOIN"] . ".PROPERTY_" . $db_prop["ORIG_ID"] . " = JFPEN" . $i . ".ID\n"; } else { $sFrom .= "\t\t\tLEFT JOIN b_iblock_property_enum JFPEN" . $i . " ON JFPEN" . $i . ".PROPERTY_ID = " . $db_prop["ORIG_ID"] . " AND JFPS" . $db_prop["JOIN"] . ".PROPERTY_" . $db_prop["ORIG_ID"] . " = JFPEN" . $i . ".ID\n"; } } else { if ($db_prop["bFullJoin"]) { $sFrom .= "\t\t\tINNER JOIN b_iblock_property_enum JFPEN" . $i . " ON JFPEN" . $i . ".PROPERTY_ID = JFPV" . $db_prop["JOIN"] . ".IBLOCK_PROPERTY_ID AND JFPV" . $db_prop["JOIN"] . ".VALUE_ENUM = JFPEN" . $i . ".ID\n"; } else { $sFrom .= "\t\t\tLEFT JOIN b_iblock_property_enum JFPEN" . $i . " ON JFPEN" . $i . ".PROPERTY_ID = JFPV" . $db_prop["JOIN"] . ".IBLOCK_PROPERTY_ID AND JFPV" . $db_prop["JOIN"] . ".VALUE_ENUM = JFPEN" . $i . ".ID\n"; } } if ($db_prop["IBLOCK_ID"]) { $arFilterIBlocks[$db_prop["IBLOCK_ID"]] = $db_prop["IBLOCK_ID"]; } } if (strlen($arJoinProps["BES"])) { $sFrom .= "\t\t\t" . $arJoinProps["BES"] . "\n"; } if ($arJoinProps["RV"]) { $sFrom .= "\t\t\tLEFT JOIN b_rating_voting RV ON RV.ENTITY_TYPE_ID = 'IBLOCK_ELEMENT' AND RV.ENTITY_ID = BE.ID\n"; } if ($arJoinProps["RVU"]) { $sFrom .= "\t\t\tLEFT JOIN b_rating_vote RVU ON RVU.ENTITY_TYPE_ID = 'IBLOCK_ELEMENT' AND RVU.ENTITY_ID = BE.ID AND RVU.USER_ID = " . $uid . "\n"; } if ($arJoinProps["RVV"]) { $sFrom .= "\t\t\t" . ($arJoinProps["RVV"]["bFullJoin"] ? "INNER" : "LEFT") . " JOIN b_rating_vote RVV ON RVV.ENTITY_TYPE_ID = 'IBLOCK_ELEMENT' AND RVV.ENTITY_ID = BE.ID\n"; } //******************END OF FROM PART******************************************** $bCatalogSort = false; if (count($arAddSelectFields) > 0 || count($arAddWhereFields) > 0 || count($arAddOrderByFields) > 0) { if (CModule::IncludeModule("catalog")) { $res_catalog = CCatalogProduct::GetQueryBuildArrays($arAddOrderByFields, $arAddWhereFields, $arAddSelectFields); if ($sGroupBy == "" && !$bOnlyCount && !(is_object($this) && isset($this->strField))) { $sSelect .= $res_catalog["SELECT"] . " "; } $sFrom .= str_replace("LEFT JOIN", "\n\t\t\tLEFT JOIN", $res_catalog["FROM"]) . "\n"; //$sWhere .= $res_catalog["WHERE"]." "; moved to MkFilter if (is_array($res_catalog["ORDER"]) && count($res_catalog["ORDER"])) { $bCatalogSort = true; foreach ($res_catalog["ORDER"] as $i => $val) { $arSqlOrder[$i] = $val; } } } } $i = array_search("CREATED_BY_FORMATTED", $arSelectFields); if ($i !== false) { if ($sSelect && $sGroupBy == "" && !$bOnlyCount && !(is_object($this) && isset($this->strField))) { $sSelect .= ",UC.NAME UC_NAME, UC.LAST_NAME UC_LAST_NAME, UC.SECOND_NAME UC_SECOND_NAME, UC.EMAIL UC_EMAIL, UC.ID UC_ID, UC.LOGIN UC_LOGIN"; } else { unset($arSelectFields[$i]); } } $sOrderBy = ""; foreach ($arSqlOrder as $i => $val) { if (strlen($val)) { if ($sOrderBy == "") { $sOrderBy = " ORDER BY "; } else { $sOrderBy .= ","; } $sOrderBy .= $val . " "; } } if (strlen(trim($sSelect)) <= 0) { $sSelect = "0 as NOP "; } $bDistinct = $bDistinct || isset($arFilter["INCLUDE_SUBSECTIONS"]) && $arFilter["INCLUDE_SUBSECTIONS"] == "Y"; if ($bDistinct) { $sSelect = str_replace("%%_DISTINCT_%%", "DISTINCT", $sSelect); } else { $sSelect = str_replace("%%_DISTINCT_%%", "", $sSelect); } $sFrom = "\n\t\t\tb_iblock B\n\t\t\tINNER JOIN b_lang L ON B.LID=L.LID\n\t\t\tINNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID\n\t\t\t" . ltrim($sFrom, "\t\n") . (in_array("USER_NAME", $arSelectFields) ? "\t\t\tLEFT JOIN b_user U ON U.ID=BE.MODIFIED_BY\n" : "") . (in_array("LOCKED_USER_NAME", $arSelectFields) ? "\t\t\tLEFT JOIN b_user UL ON UL.ID=BE.WF_LOCKED_BY\n" : "") . (in_array("CREATED_USER_NAME", $arSelectFields) || in_array("CREATED_BY_FORMATTED", $arSelectFields) ? "\t\t\tLEFT JOIN b_user UC ON UC.ID=BE.CREATED_BY\n" : "") . "\n\t\t"; $strSql = "\n\t\t\tFROM " . $sFrom . "\n\t\t\tWHERE 1=1 " . $sWhere . "\n\t\t\t" . $sGroupBy . "\n\t\t"; if (isset($this) && is_object($this) && isset($this->strField)) { $this->sFrom = $sFrom; $this->sWhere = $sWhere; return "SELECT " . $sSelect . $strSql; } if ($bOnlyCount) { $res = $DB->Query("SELECT " . $sSelect . $strSql, false, "FILE: " . __FILE__ . "<br> LINE: " . __LINE__); $res = $res->Fetch(); return $res["CNT"]; } if (is_array($arNavStartParams)) { $nTopCount = intval($arNavStartParams["nTopCount"]); $nElementID = intval($arNavStartParams["nElementID"]); if ($nTopCount > 0) { $strSql = "SELECT " . $sSelect . $strSql . $sOrderBy . " LIMIT " . $nTopCount; $res = $DB->Query($strSql); } elseif ($nElementID > 0 && $sGroupBy == "" && $sOrderBy != "" && strpos($sSelect, "BE.ID") !== false && !$bCatalogSort) { $nPageSize = intval($arNavStartParams["nPageSize"]); if ($nPageSize > 0) { $DB->Query("SET @rank=0"); $DB->Query("\n\t\t\t\t\t\tSELECT @rank:=el1.rank\n\t\t\t\t\t\tFROM (\n\t\t\t\t\t\t\tSELECT @rank:=@rank+1 AS rank, el0.*\n\t\t\t\t\t\t\tFROM (\n\t\t\t\t\t\t\t\tSELECT " . $sSelect . $strSql . $sOrderBy . "\n\t\t\t\t\t\t\t) el0\n\t\t\t\t\t\t) el1\n\t\t\t\t\t\tWHERE el1.ID = " . $nElementID . "\n\t\t\t\t\t"); $DB->Query("SET @rank2=0"); $res = $DB->Query("\n\t\t\t\t\t\tSELECT *\n\t\t\t\t\t\tFROM (\n\t\t\t\t\t\t\tSELECT @rank2:=@rank2+1 AS RANK, el0.*\n\t\t\t\t\t\t\tFROM (\n\t\t\t\t\t\t\t\tSELECT " . $sSelect . $strSql . $sOrderBy . "\n\t\t\t\t\t\t\t) el0\n\t\t\t\t\t\t) el1\n\t\t\t\t\t\tWHERE el1.RANK between @rank-{$nPageSize} and @rank+{$nPageSize}\n\t\t\t\t\t"); } else { $DB->Query("SET @rank=0"); $res = $DB->Query("\n\t\t\t\t\t\tSELECT el1.*\n\t\t\t\t\t\tFROM (\n\t\t\t\t\t\t\tSELECT @rank:=@rank+1 AS RANK, el0.*\n\t\t\t\t\t\t\tFROM (\n\t\t\t\t\t\t\t\tSELECT " . $sSelect . $strSql . $sOrderBy . "\n\t\t\t\t\t\t\t) el0\n\t\t\t\t\t\t) el1\n\t\t\t\t\t\tWHERE el1.ID = " . $nElementID . "\n\t\t\t\t\t"); } } else { if ($sGroupBy == "") { $res_cnt = $DB->Query("SELECT COUNT(" . ($bDistinct ? "DISTINCT BE.ID" : "'x'") . ") as C " . $strSql); $res_cnt = $res_cnt->Fetch(); $cnt = $res_cnt["C"]; } else { $res_cnt = $DB->Query("SELECT 'x' " . $strSql); $cnt = $res_cnt->SelectedRowsCount(); } $strSql = "SELECT " . $sSelect . $strSql . $sOrderBy; $res = new CDBResult(); $res->NavQuery($strSql, $cnt, $arNavStartParams); } } else { $strSql = "SELECT " . $sSelect . $strSql . $sOrderBy; $res = $DB->Query($strSql, false, "FILE: " . __FILE__ . "<br> LINE: " . __LINE__); } $res = new CIBlockResult($res); $res->SetIBlockTag($arFilterIBlocks); $res->arIBlockMultProps = $arIBlockMultProps; $res->arIBlockConvProps = $arIBlockConvProps; $res->arIBlockAllProps = $arIBlockAllProps; $res->arIBlockNumProps = $arIBlockNumProps; $res->arIBlockLongProps = $arIBlockLongProps; return $res; }
protected static function GetListUni($arOrder = array(), $arFilter = array(), $arSelectFields = array(), $mode = self::GET_LIST_ALL, $lessonId = -1, $arNavParams = array()) { global $DB, $USER_FIELD_MANAGER; $obUserFieldsSql = new CUserTypeSQL(); $obUserFieldsSql->SetEntity('LEARNING_LESSONS', 'TL.ID'); $obUserFieldsSql->SetSelect($arSelectFields); $obUserFieldsSql->SetFilter($arFilter); $obUserFieldsSql->SetOrder($arOrder); $bReplaceCourseId = false; if (isset($arFilter['#REPLACE_COURSE_ID_TO_ID'])) { $bReplaceCourseId = true; unset($arFilter['#REPLACE_COURSE_ID_TO_ID']); } $oPermParser = new CLearnParsePermissionsFromFilter($arFilter); // For ordering $arMap = array('lesson_id' => 'TL.ID', 'site_id' => 'TL.ID', 'name' => 'TL.NAME', 'code' => 'TL.CODE', 'active' => 'TL.ACTIVE', 'created' => 'TL.DATE_CREATE', 'date_create' => 'TL.DATE_CREATE', 'created_by' => 'TL.CREATED_BY', 'timestamp_x' => 'TL.TIMESTAMP_X', 'course_id' => 'TC.ID', 'course_sort' => 'TC.SORT', 'active_from' => 'TC.ACTIVE_FROM', 'sort' => 'TC.SORT', 'linked_lesson_id' => 'TC.LINKED_LESSON_ID'); $allowedModes = array(self::GET_LIST_ALL, self::GET_LIST_IMMEDIATE_CHILDS_OF, self::GET_LIST_IMMEDIATE_PARENTS_OF, self::GET_LIST_IMMEDIATE_CHILDS_OF | self::GET_LIST_IMMEDIATE_PARENTS_OF); $argsCheck = is_array($arOrder) && is_array($arSelectFields) && in_array($mode, $allowedModes, true) && self::_EnsureArgsStrictlyCastableToIntegers($lessonId); if (!$argsCheck) { throw new LearnException('EA_PARAMS', LearnException::EXC_ERR_ALL_PARAMS); } $arFieldsMap = array('LESSON_ID' => 'TL.ID', 'SITE_ID' => 'CASE WHEN (1 > 0) THEN \'no site\' ELSE \'0\' END', 'WAS_CHAPTER_ID' => 'TL.WAS_CHAPTER_ID', 'KEYWORDS' => 'TL.KEYWORDS', 'CHILDS_CNT' => '(SELECT COUNT(*) FROM b_learn_lesson_edges TLES WHERE TLES.SOURCE_NODE = TL.ID)', 'IS_CHILDS' => 'CASE WHEN (SELECT COUNT(*) FROM b_learn_lesson_edges TLES WHERE TLES.SOURCE_NODE = TL.ID) > 0 THEN \'1\' ELSE \'0\' END', 'SORT' => 'TC.SORT', 'TIMESTAMP_X' => $DB->DateToCharFunction('TL.TIMESTAMP_X'), 'DATE_CREATE' => $DB->DateToCharFunction('TL.DATE_CREATE'), 'CREATED_USER_NAME' => $DB->Concat("'('", 'TU.LOGIN', "') '", 'TU.NAME', "' '", 'TU.LAST_NAME'), 'CREATED_BY' => 'TL.CREATED_BY', 'ACTIVE' => 'TL.ACTIVE', 'NAME' => 'TL.NAME', 'PREVIEW_PICTURE' => 'TL.PREVIEW_PICTURE', 'PREVIEW_TEXT' => 'TL.PREVIEW_TEXT', 'PREVIEW_TEXT_TYPE' => 'TL.PREVIEW_TEXT_TYPE', 'DETAIL_TEXT' => 'TL.DETAIL_TEXT', 'DETAIL_PICTURE' => 'TL.DETAIL_PICTURE', 'DETAIL_TEXT_TYPE' => 'TL.DETAIL_TEXT_TYPE', 'LAUNCH' => 'TL.LAUNCH', 'CODE' => 'TL.CODE', 'ACTIVE_FROM' => $DB->DateToCharFunction('TC.ACTIVE_FROM'), 'ACTIVE_TO' => $DB->DateToCharFunction('TC.ACTIVE_TO'), 'RATING' => 'TC.RATING', 'RATING_TYPE' => 'TC.RATING_TYPE', 'SCORM' => 'TC.SCORM', 'LINKED_LESSON_ID' => 'TC.LINKED_LESSON_ID', 'COURSE_ID' => 'TC.ID', 'COURSE_SORT' => 'TC.SORT'); // filter by TIMESTAMP_X by default if (count($arOrder) == 0) { $arOrder['TIMESTAMP_X'] = 'DESC'; } $arSqlSearch = self::GetFilter($arFilter, $mode); if (isset($arFilter['SITE_ID'])) { $arLID = array(); if (is_array($arFilter['SITE_ID'])) { $arLID = $arFilter['SITE_ID']; } else { if (strlen($arFilter['SITE_ID']) > 0) { $arLID[] = $arFilter['SITE_ID']; } } $SqlSearchLang = "''"; foreach ($arLID as $v) { $SqlSearchLang .= ", '" . $DB->ForSql($v) . "'"; } } $r = $obUserFieldsSql->GetFilter(); if (strlen($r) > 0) { $arSqlSearch[] = "(" . $r . ")"; } $sqlSearch = ''; foreach ($arSqlSearch as $value) { if (strlen($value) > 0) { $sqlSearch .= ' AND ' . $value; } } $modeSQL_join = $modeSQL_where = ''; $modeSQL_defaultSortField = "TC.SORT"; // as SORT // Prepare SQL's joins, if $mode need it if ($mode & self::GET_LIST_IMMEDIATE_PARENTS_OF) { $modeSQL_join .= "\nINNER JOIN b_learn_lesson_edges TLE \n\t\t\t\tON TLE.SOURCE_NODE = TL.ID\n"; $modeSQL_where .= "\nAND TLE.TARGET_NODE = " . ($lessonId + 0) . "\n"; $arFieldsMap['EDGE_SORT'] = 'TLE.SORT'; $arFieldsMap['SORT'] = 'TLE.SORT'; } if ($mode & self::GET_LIST_IMMEDIATE_CHILDS_OF) { /** * GROUP BY works for MySQL, MSSQL, Oracle * select a.id, a.NAME, count(b.USER_ID) as C * from b_group a, b_user_group b * where a.id = b.GROUP_ID * group by a.id, a.NAME * order by C */ $modeSQL_join .= "\nINNER JOIN b_learn_lesson_edges TLE \n\t\t\t\tON TLE.TARGET_NODE = TL.ID\n"; $modeSQL_where .= "\nAND TLE.SOURCE_NODE = " . ($lessonId + 0) . "\n"; $arMap['childs_cnt'] = 'CHILDS_CNT'; $arMap['is_childs'] = 'IS_CHILDS'; $arMap['edge_sort'] = 'TLE.SORT'; // Override default sort $arMap['sort'] = $arMap['edge_sort']; $modeSQL_defaultSortField = "TLE.SORT"; // as SORT $arFieldsMap['EDGE_SORT'] = 'TLE.SORT'; $arFieldsMap['SORT'] = 'TLE.SORT'; } if ($bReplaceCourseId) { $arFieldsMap['ID'] = $arFieldsMap['COURSE_ID']; } // Select all fields by default if (count($arSelectFields) == 0) { $arSelectFields = array_keys($arFieldsMap); } // Ensure that all order fields will be selected foreach ($arOrder as $by => $order) { $fieldName = strtoupper($by); if (!in_array($fieldName, $arSelectFields)) { $arSelectFields[] = $fieldName; } } // Build list of fields to be selected $strSqlSelect = ''; $bFirstPass = true; $bDefaultSortFieldSelected = false; foreach ($arSelectFields as $selectFieldName) { if (substr($selectFieldName, 0, 3) === 'UF_') { continue; } if (!$bFirstPass) { $strSqlSelect .= ', '; } else { $bFirstPass = false; } if (!isset($arFieldsMap[$selectFieldName])) { throw new LearnException('EA_OTHER: UNKNOWN FIELD: ' . $selectFieldName, LearnException::EXC_ERR_ALL_GIVEUP); } $strSqlSelect .= $arFieldsMap[$selectFieldName] . ' AS ' . $selectFieldName; if ($selectFieldName === 'SORT' && $arFieldsMap[$selectFieldName] === $modeSQL_defaultSortField) { $bDefaultSortFieldSelected = true; } } if (!$bDefaultSortFieldSelected) { if ($strSqlSelect !== '') { $strSqlSelect .= ', '; } $strSqlSelect .= $modeSQL_defaultSortField . ' AS SORT'; } $strSqlSelect .= $obUserFieldsSql->GetSelect(); $sqlLangConstraint = ''; if (strlen($SqlSearchLang) > 2) { $sqlLangConstraint = "\n\t\t\tAND\n\t\t\tEXISTS\n\t\t\t(\n\t\t\t\tSELECT 'x' FROM b_learn_course_site TCS\n\t\t\t\tWHERE TC.ID = TCS.COURSE_ID AND TCS.SITE_ID IN (" . $SqlSearchLang . ")\n\t\t\t)\n\t\t\t"; } $strSqlFrom = "FROM b_learn_lesson TL\n\t\t\tLEFT JOIN b_learn_course TC \n\t\t\t\tON TC.LINKED_LESSON_ID = TL.ID\n\t\t\tLEFT JOIN b_user TU \n\t\t\t\tON TU.ID = TL.CREATED_BY " . $modeSQL_join . $obUserFieldsSql->GetJoin("TL.ID") . " WHERE 1 = 1 " . $sqlLangConstraint . $modeSQL_where; // for getting only parents/childs, if need if ($oPermParser->IsNeedCheckPerm()) { $strSqlFrom .= " AND TL.ID IN (" . $oPermParser->SQLForAccessibleLessons() . ") "; } $strSqlFrom .= $sqlSearch; $sql = "SELECT " . $strSqlSelect . " " . $strSqlFrom; $arSqlOrder = array(); foreach ($arOrder as $by => $order) { $by = strtolower($by); $order = strtolower($order); if ($order !== 'asc') { $order = 'desc'; } if ($s = $obUserFieldsSql->getOrder(strtolower($by))) { $arSqlOrder[] = ' ' . $s . ' ' . $order . ' '; } if (substr($by, 0, 3) !== 'UF_') { if (!isset($arMap[$by])) { throw new LearnException('EA_PARAMS: unknown order by field: "' . $by . '"', LearnException::EXC_ERR_ALL_PARAMS); } } $arSqlOrder[] = ' ' . $arMap[$by] . ' ' . $order . ' '; } // on duplicate first occured FIELD will be used according to function description DelDuplicateSort($arSqlOrder); $sql .= ' ORDER BY ' . implode(', ', $arSqlOrder); if (is_array($arNavParams) && !empty($arNavParams)) { if (isset($arNavParams['nTopCount']) && (int) $arNavParams['nTopCount'] > 0) { $sql = $DB->TopSql($sql, (int) $arNavParams['nTopCount']); $res = $DB->Query($sql, true); } else { $res_cnt = $DB->Query("SELECT COUNT(TL.ID) as C " . $strSqlFrom); $res_cnt = $res_cnt->fetch(); $res = new CDBResult(); $rc = $res->NavQuery($sql, $res_cnt['C'], $arNavParams, true); if ($rc === false) { throw new LearnException('EA_SQLERROR', LearnException::EXC_ERR_ALL_GIVEUP); } } } else { $res = $DB->Query($sql, true); } if ($res === false) { throw new LearnException('EA_SQLERROR', LearnException::EXC_ERR_ALL_GIVEUP); } $res->SetUserFields($USER_FIELD_MANAGER->GetUserFields('LEARNING_LESSONS')); return $res; }
public static function GetList($arOrder = array(), $arFilter = array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array()) { global $DB; $TABLE = 'b_form_crm C'; $arFields = array("ID" => array("FIELD" => "C.ID", "TYPE" => "int"), "NAME" => array("FIELD" => "C.NAME", "TYPE" => "string"), "ACTIVE" => array("FIELD" => "C.ACTIVE", "TYPE" => "string"), "URL" => array("FIELD" => "C.URL", "TYPE" => "string"), "AUTH_HASH" => array("FIELD" => "C.AUTH_HASH", "TYPE" => "string")); if (count($arSelectFields) <= 0) { foreach ($arFields as $k => $v) { if (!isset($v['FROM'])) { $arSelectFields[] = $k; } } } elseif (in_array("*", $arSelectFields)) { $arSelectFields = array_keys($arFields); } $arSqls = self::PrepareSql($arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields); $arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "", $arSqls["SELECT"]); $strSql = "\nSELECT\n\t" . $arSqls["SELECT"] . " " . "\nFROM\n\t" . $TABLE . "\n\t" . $arSqls["FROM"] . " " . "\n"; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } if (is_array($arGroupBy) && count($arGroupBy) == 0) { $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); $arRes = $dbRes->Fetch(); if ($arRes) { return $arRes["CNT"]; } else { return false; } } if (strlen($arSqls["ORDERBY"]) > 0) { $strSql .= "ORDER BY " . $arSqls["ORDERBY"] . " "; } if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) <= 0) { $strSql_tmp = "\nSELECT COUNT(DISTINCT M.ID) as CNT\nFROM\n\t" . $TABLE . "\n\t" . $arSqls["FROM"] . "\n"; if (strlen($arSqls["WHERE"]) > 0) { $strSql_tmp .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql_tmp .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } $dbRes = $DB->Query($strSql_tmp, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); $cnt = 0; if (strlen($arSqls["GROUPBY"]) <= 0) { $arRes = $dbRes->Fetch(); if ($arRes) { $cnt = $arRes["CNT"]; } } else { $cnt = $dbRes->SelectedRowsCount(); } $dbRes = new CDBResult(); $dbRes->NavQuery($strSql, $cnt, $arNavStartParams); } else { if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) > 0) { $strSql .= "LIMIT " . intval($arNavStartParams["nTopCount"]); } $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); } return $dbRes; }
/** * <p>Функция возвращает результат выборки записей из типов плательщика в соответствии со своими параметрами.</p> * * * * * @param array $arOrder = array() Массив, в соответствии с которым сортируются результирующие * записи. Массив имеет вид: <pre class="syntax">array( "название_поля1" => * "направление_сортировки1", "название_поля2" => * "направление_сортировки2", . . . )</pre> В качестве "название_поля<i>N</i>" * может стоять любое поле типов плательщика, а в качестве * "направление_сортировки<i>X</i>" могут быть значения "<i>ASC</i>" (по * возрастанию) и "<i>DESC</i>" (по убыванию).<br><br> Если массив сортировки * имеет несколько элементов, то результирующий набор сортируется * последовательно по каждому элементу (т.е. сначала сортируется по * первому элементу, потом результат сортируется по второму и * т.д.). <br><br> Значение по умолчанию - пустой массив array() - означает, * что результат отсортирован не будет. * * * * @param array $arFilter = array() Массив, в соответствии с которым фильтруются записи типов * плательщика. Массив имеет вид: <pre class="syntax">array( * "[модификатор1][оператор1]название_поля1" => "значение1", * "[модификатор2][оператор2]название_поля2" => "значение2", . . . )</pre> * Удовлетворяющие фильтру записи возвращаются в результате, а * записи, которые не удовлетворяют условиям фильтра, * отбрасываются.<br><br> Допустимыми являются следующие модификаторы: * <ul> <li> <b> !</b> - отрицание;</li> <li> <b> +</b> - значения null, 0 и пустая строка * так же удовлетворяют условиям фильтра.</li> </ul> Допустимыми * являются следующие операторы: <ul> <li> <b>>=</b> - значение поля больше * или равно передаваемой в фильтр величины;</li> <li> <b>></b> - значение * поля строго больше передаваемой в фильтр величины;</li> <li> <b><=</b> - * значение поля меньше или равно передаваемой в фильтр величины;</li> * <li> <b><</b> - значение поля строго меньше передаваемой в фильтр * величины;</li> <li> <b>@</b> - значение поля находится в передаваемом в * фильтр разделенном запятой списке значений;</li> <li> <b>~</b> - значение * поля проверяется на соответствие передаваемому в фильтр * шаблону;</li> <li> <b>%</b> - значение поля проверяется на соответствие * передаваемой в фильтр строке в соответствии с языком запросов.</li> * </ul> В качестве "название_поляX" может стоять любое поле типов * плательщика.<br> Значение по умолчанию - пустой массив array() - * означает, что результат отфильтрован не будет. * * * * @param array $arGroupBy = false Массив полей, по которым группируются записи типов плательщика. * Массив имеет вид: <pre class="syntax">array("название_поля1", * "группирующая_функция2" => "название_поля2", ...)</pre> В качестве * "название_поля<i>N</i>" может стоять любое поле типов плательщика. В * качестве группирующей функции могут стоять: <ul> <li> <b> COUNT</b> - * подсчет количества;</li> <li> <b>AVG</b> - вычисление среднего значения;</li> * <li> <b>MIN</b> - вычисление минимального значения;</li> <li> <b> MAX</b> - * вычисление максимального значения;</li> <li> <b>SUM</b> - вычисление * суммы.</li> </ul> Этот фильтр означает "выбрать все записи, в которых * значение в поле LID (сайт системы) не равно en".<br><br> Значение по * умолчанию - <i>false</i> - означает, что результат группироваться не * будет. * * * * @param array $arNavStartParams = false Массив параметров выборки. Может содержать следующие ключи: <ul> * <li>"<b>nTopCount</b>" - количество возвращаемых функцией записей будет * ограничено сверху значением этого ключа;</li> <li> любой ключ, * принимаемый методом <b> CDBResult::NavQuery</b> в качестве третьего * параметра.</li> </ul> Значение по умолчанию - <i>false</i> - означает, что * параметров выборки нет. * * * * @param array $arSelectFields = array() Массив полей записей, которые будут возвращены функцией. Можно * указать только те поля, которые необходимы. Если в массиве * присутствует значение "*", то будут возвращены все доступные * поля.<br><br> Значение по умолчанию - пустой массив array() - означает, * что будут возвращены все поля основной таблицы запроса. * * * * @return CDBResult <p>Возвращается объект класса CDBResult, содержащий набор * ассоциативных массивов параметров типов плательщиков с * ключами:</p> <table class="tnormal" width="100%"> <tr> <th width="15%">Ключ</th> <th>Описание</th> * </tr> <tr> <td>ID</td> <td>Код типа плательщика.</td> </tr> <tr> <td>LID</td> <td>Код * сайта.</td> </tr> <tr> <td>LIDS</td> <td>Фильтрация/выборка всех сайтов, к * которым привязан тип плательщика.</td> </tr> <tr> <td>NAME</td> <td>Название * типа плательщика.</td> </tr> <tr> <td>SORT</td> <td>Индекс сортировки.</td> </tr> <tr> * <td>ACTIVE</td> <td>Флаг активности пользователя [Y|N].</td> </tr> </table> <p>Если в * качестве параметра arGroupBy передается пустой массив, то функция * вернет число записей, удовлетворяющих фильтру.</p> <a name="examples"></a> * * * <h4>Example</h4> * <pre> * <? * // Выведем переключатели для выбора типа плательщика для текущего сайта * $db_ptype = CSalePersonType::GetList(Array("SORT" => "ASC"), Array("LID"=>SITE_ID)); * $bFirst = True; * while ($ptype = $db_ptype->Fetch()) * { * ?><input type="radio" name="PERSON_TYPE" value="<?echo $ptype["ID"] ?>"<?if ($bFirst) echo " checked";?>><?echo $ptype["NAME"] ?><br><? * $bFirst = <i>false</i>; * } * ?> * </pre> * * * @static * @link http://dev.1c-bitrix.ru/api_help/sale/classes/csalepersontype/csalepersontype__getlist.2dca23fd.php * @author Bitrix */ public static function GetList($arOrder = array(), $arFilter = array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array()) { global $DB; if (!is_array($arOrder) && !is_array($arFilter)) { $arOrder = strval($arOrder); $arFilter = strval($arFilter); if (strlen($arOrder) > 0 && strlen($arFilter) > 0) { $arOrder = array($arOrder => $arFilter); } else { $arOrder = array(); } if (is_array($arGroupBy)) { $arFilter = $arGroupBy; } else { $arFilter = array(); } $arGroupBy = false; } if (empty($arSelectFields)) { $arSelectFields = array("ID", "LID", "NAME", "SORT", "ACTIVE"); } if (is_set($arFilter, "LID") && !empty($arFilter["LID"])) { $arFilter["LIDS"] = $arFilter["LID"]; unset($arFilter["LID"]); } // FIELDS --> $arFields = array("ID" => array("FIELD" => "PT.ID", "TYPE" => "int"), "LID" => array("FIELD" => "PT.LID", "TYPE" => "string"), "LIDS" => array("FIELD" => "PTS.SITE_ID", "TYPE" => "string", "FROM" => "LEFT JOIN b_sale_person_type_site PTS ON (PT.ID = PTS.PERSON_TYPE_ID)"), "NAME" => array("FIELD" => "PT.NAME", "TYPE" => "string"), "SORT" => array("FIELD" => "PT.SORT", "TYPE" => "int"), "ACTIVE" => array("FIELD" => "PT.ACTIVE", "TYPE" => "char")); // <-- FIELDS $arSqls = CSaleOrder::PrepareSql($arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields); $arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "DISTINCT", $arSqls["SELECT"]); if (is_array($arGroupBy) && count($arGroupBy) == 0) { $strSql = "SELECT " . $arSqls["SELECT"] . " " . "FROM b_sale_person_type PT " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } //echo "!1!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); if ($arRes = $dbRes->Fetch()) { return $arRes["CNT"]; } else { return False; } } $strSql = "SELECT " . $arSqls["SELECT"] . " " . "FROM b_sale_person_type PT " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } if (strlen($arSqls["ORDERBY"]) > 0) { $strSql .= "ORDER BY " . $arSqls["ORDERBY"] . " "; } if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) <= 0) { $strSql_tmp = "SELECT COUNT('x') as CNT " . "FROM b_sale_person_type PT " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql_tmp .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql_tmp .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } //echo "!2.1!=".htmlspecialcharsbx($strSql_tmp)."<br>"; $dbRes = $DB->Query($strSql_tmp, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); $cnt = 0; if (strlen($arSqls["GROUPBY"]) <= 0) { if ($arRes = $dbRes->Fetch()) { $cnt = $arRes["CNT"]; } } else { // FOR MYSQL!!! ANOTHER CODE FOR ORACLE $cnt = $dbRes->SelectedRowsCount(); } $dbRes = new CDBResult(); //echo "!2.2!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes->NavQuery($strSql, $cnt, $arNavStartParams); } else { if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) > 0) { $strSql .= "LIMIT " . IntVal($arNavStartParams["nTopCount"]); } //echo "!3!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); } $arPT = array(); $arResTmp = array(); while ($arRes = $dbRes->Fetch()) { if (IntVal($arRes["ID"]) > 0) { if (!in_array($arRes["ID"], $arPT)) { $arPT[] = $arRes["ID"]; } $arResTmp[] = $arRes; } } if (!empty($arPT) && is_array($arPT)) { $strSql = "SELECT * from b_sale_person_type_site WHERE PERSON_TYPE_ID IN (" . implode(",", $arPT) . ")"; $dbRes1 = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); while ($arRes1 = $dbRes1->Fetch()) { $arRes2[$arRes1["PERSON_TYPE_ID"]][] = $arRes1["SITE_ID"]; } } foreach ($arResTmp as $k => $v) { $arResTmp[$k]["LIDS"] = $arRes2[$v["ID"]]; } $dbRes = new CDBResult(); $dbRes->InitFromArray($arResTmp); return $dbRes; }
function GetList($arOrder = Array("ID" => "DESC"), $arFilter = Array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array()) { global $DB, $USER_FIELD_MANAGER, $USER; $obUserFieldsSql = new CUserTypeSQL; $obUserFieldsSql->SetEntity("BLOG_POST", "P.ID"); $obUserFieldsSql->SetSelect($arSelectFields); $obUserFieldsSql->SetFilter($arFilter); $obUserFieldsSql->SetOrder($arOrder); if (isset($arFilter["DATE_PUBLISH_DAY"]) && isset($arFilter["DATE_PUBLISH_MONTH"]) && isset($arFilter["DATE_PUBLISH_YEAR"])) { if (strlen($arFilter["DATE_PUBLISH_YEAR"]) == 2) $arFilter["DATE_PUBLISH_YEAR"] = "20".$arFilter["DATE_PUBLISH_YEAR"]; $date1 = mktime(0, 0, 0, $arFilter["DATE_PUBLISH_MONTH"], $arFilter["DATE_PUBLISH_DAY"], $arFilter["DATE_PUBLISH_YEAR"]); $date2 = mktime(0, 0, 0, $arFilter["DATE_PUBLISH_MONTH"], $arFilter["DATE_PUBLISH_DAY"] + 1, $arFilter["DATE_PUBLISH_YEAR"]); $arFilter[">=DATE_PUBLISH"] = ConvertTimeStamp($date1, "SHORT", SITE_ID); $arFilter["<DATE_PUBLISH"] = ConvertTimeStamp($date2, "SHORT", SITE_ID); unset($arFilter["DATE_PUBLISH_DAY"]); unset($arFilter["DATE_PUBLISH_MONTH"]); unset($arFilter["DATE_PUBLISH_YEAR"]); } elseif (isset($arFilter["DATE_PUBLISH_MONTH"]) && isset($arFilter["DATE_PUBLISH_YEAR"])) { if (strlen($arFilter["DATE_PUBLISH_YEAR"]) == 2) $arFilter["DATE_PUBLISH_YEAR"] = "20".$arFilter["DATE_PUBLISH_YEAR"]; $date1 = mktime(0, 0, 0, $arFilter["DATE_PUBLISH_MONTH"], 1, $arFilter["DATE_PUBLISH_YEAR"]); $date2 = mktime(0, 0, 0, $arFilter["DATE_PUBLISH_MONTH"] + 1, 1, $arFilter["DATE_PUBLISH_YEAR"]); $arFilter[">=DATE_PUBLISH"] = ConvertTimeStamp($date1, "SHORT", SITE_ID); $arFilter["<DATE_PUBLISH"] = ConvertTimeStamp($date2, "SHORT", SITE_ID); unset($arFilter["DATE_PUBLISH_MONTH"]); unset($arFilter["DATE_PUBLISH_YEAR"]); } elseif (isset($arFilter["DATE_PUBLISH_YEAR"])) { if (strlen($arFilter["DATE_PUBLISH_YEAR"]) == 2) $arFilter["DATE_PUBLISH_YEAR"] = "20".$arFilter["DATE_PUBLISH_YEAR"]; $date1 = mktime(0, 0, 0, 1, 1, $arFilter["DATE_PUBLISH_YEAR"]); $date2 = mktime(0, 0, 0, 1, 1, $arFilter["DATE_PUBLISH_YEAR"] + 1); $arFilter[">=DATE_PUBLISH"] = ConvertTimeStamp($date1, "SHORT", SITE_ID); $arFilter["<DATE_PUBLISH"] = ConvertTimeStamp($date2, "SHORT", SITE_ID); unset($arFilter["DATE_PUBLISH_YEAR"]); } if (count($arSelectFields) <= 0) $arSelectFields = array("ID", "TITLE", "BLOG_ID", "AUTHOR_ID", "PREVIEW_TEXT", "PREVIEW_TEXT_TYPE", "DETAIL_TEXT", "DETAIL_TEXT_TYPE", "DATE_CREATE", "DATE_PUBLISH", "KEYWORDS", "PUBLISH_STATUS", "ATRIBUTE", "ATTACH_IMG", "ENABLE_TRACKBACK", "ENABLE_COMMENTS", "VIEWS", "NUM_COMMENTS", "CODE", "MICRO"); if(in_array("*", $arSelectFields)) $arSelectFields = array("ID", "TITLE", "BLOG_ID", "AUTHOR_ID", "PREVIEW_TEXT", "PREVIEW_TEXT_TYPE", "DETAIL_TEXT", "DETAIL_TEXT_TYPE", "DATE_CREATE", "DATE_PUBLISH", "KEYWORDS", "PUBLISH_STATUS", "ATRIBUTE", "ATTACH_IMG", "ENABLE_TRACKBACK", "ENABLE_COMMENTS", "NUM_COMMENTS", "NUM_TRACKBACKS", "VIEWS", "FAVORITE_SORT", "CATEGORY_ID", "PERMS", "AUTHOR_LOGIN", "AUTHOR_NAME", "AUTHOR_LAST_NAME", "AUTHOR_SECOND_NAME", "AUTHOR_EMAIL", "AUTHOR", "BLOG_USER_ALIAS", "BLOG_USER_AVATAR", "BLOG_URL", "BLOG_OWNER_ID", "BLOG_ACTIVE", "BLOG_GROUP_ID", "BLOG_GROUP_SITE_ID", "BLOG_SOCNET_GROUP_ID", "BLOG_ENABLE_RSS", "BLOG_USE_SOCNET", "CODE", "MICRO"); if((array_key_exists("BLOG_GROUP_SITE_ID", $arFilter) || in_array("BLOG_GROUP_SITE_ID", $arSelectFields)) && !in_array("BLOG_URL", $arSelectFields)) $arSelectFields[] = "BLOG_URL"; // FIELDS --> $arFields = array( "ID" => array("FIELD" => "P.ID", "TYPE" => "int"), "TITLE" => array("FIELD" => "P.TITLE", "TYPE" => "string"), "CODE" => array("FIELD" => "P.CODE", "TYPE" => "string"), "BLOG_ID" => array("FIELD" => "P.BLOG_ID", "TYPE" => "int"), "AUTHOR_ID" => array("FIELD" => "P.AUTHOR_ID", "TYPE" => "int"), "PREVIEW_TEXT" => array("FIELD" => "P.PREVIEW_TEXT", "TYPE" => "string"), "PREVIEW_TEXT_TYPE" => array("FIELD" => "P.PREVIEW_TEXT_TYPE", "TYPE" => "string"), "DETAIL_TEXT" => array("FIELD" => "P.DETAIL_TEXT", "TYPE" => "string"), "DETAIL_TEXT_TYPE" => array("FIELD" => "P.DETAIL_TEXT_TYPE", "TYPE" => "string"), "DATE_CREATE" => array("FIELD" => "P.DATE_CREATE", "TYPE" => "datetime"), "DATE_PUBLISH" => array("FIELD" => "P.DATE_PUBLISH", "TYPE" => "datetime"), "KEYWORDS" => array("FIELD" => "P.KEYWORDS", "TYPE" => "string"), "PUBLISH_STATUS" => array("FIELD" => "P.PUBLISH_STATUS", "TYPE" => "string"), "ATRIBUTE" => array("FIELD" => "P.ATRIBUTE", "TYPE" => "string"), "ATTACH_IMG" => array("FIELD" => "P.ATTACH_IMG", "TYPE" => "int"), "ENABLE_TRACKBACK" => array("FIELD" => "P.ENABLE_TRACKBACK", "TYPE" => "string"), "ENABLE_COMMENTS" => array("FIELD" => "P.ENABLE_COMMENTS", "TYPE" => "string"), "NUM_COMMENTS" => array("FIELD" => "P.NUM_COMMENTS", "TYPE" => "int"), "NUM_TRACKBACKS" => array("FIELD" => "P.NUM_TRACKBACKS", "TYPE" => "int"), "VIEWS" => array("FIELD" => "P.VIEWS", "TYPE" => "int"), "FAVORITE_SORT" => array("FIELD" => "P.FAVORITE_SORT", "TYPE" => "int"), "CATEGORY_ID" => array("FIELD" => "P.CATEGORY_ID", "TYPE" => "string"), "PATH" => array("FIELD" => "P.PATH", "TYPE" => "string"), "MICRO" => array("FIELD" => "P.MICRO", "TYPE" => "string"), "HAS_IMAGES" => array("FIELD" => "P.HAS_IMAGES", "TYPE" => "string"), "HAS_PROPS" => array("FIELD" => "P.HAS_PROPS", "TYPE" => "string"), "HAS_TAGS" => array("FIELD" => "P.HAS_TAGS", "TYPE" => "string"), "HAS_COMMENT_IMAGES" => array("FIELD" => "P.HAS_COMMENT_IMAGES", "TYPE" => "string"), "HAS_SOCNET_ALL" => array("FIELD" => "P.HAS_SOCNET_ALL", "TYPE" => "string"), "SEO_TITLE" => array("FIELD" => "P.SEO_TITLE", "TYPE" => "string"), "SEO_TAGS" => array("FIELD" => "P.SEO_TAGS", "TYPE" => "string"), "SEO_DESCRIPTION" => array("FIELD" => "P.SEO_DESCRIPTION", "TYPE" => "string"), "PERMS" => array(), "AUTHOR_LOGIN" => array("FIELD" => "U.LOGIN", "TYPE" => "string", "FROM" => "LEFT JOIN b_user U ON (P.AUTHOR_ID = U.ID)"), "AUTHOR_NAME" => array("FIELD" => "U.NAME", "TYPE" => "string", "FROM" => "LEFT JOIN b_user U ON (P.AUTHOR_ID = U.ID)"), "AUTHOR_LAST_NAME" => array("FIELD" => "U.LAST_NAME", "TYPE" => "string", "FROM" => "LEFT JOIN b_user U ON (P.AUTHOR_ID = U.ID)"), "AUTHOR_SECOND_NAME" => array("FIELD" => "U.SECOND_NAME", "TYPE" => "string", "FROM" => "LEFT JOIN b_user U ON (P.AUTHOR_ID = U.ID)"), "AUTHOR_EMAIL" => array("FIELD" => "U.EMAIL", "TYPE" => "string", "FROM" => "LEFT JOIN b_user U ON (P.AUTHOR_ID = U.ID)"), "AUTHOR" => array("FIELD" => "U.LOGIN, U.NAME, U.LAST_NAME, U.EMAIL, U.ID", "WHERE_ONLY" => "Y", "TYPE" => "string", "FROM" => "LEFT JOIN b_user U ON (P.AUTHOR_ID = U.ID)"), "CATEGORY_NAME" => array("FIELD" => "PCN.NAME", "TYPE" => "string", "FROM" => "LEFT JOIN b_blog_category PCN ON (P.CATEGORY_ID = PCN.ID)"), "CATEGORY_ID_F" => array("FIELD" => "PC.CATEGORY_ID", "TYPE" => "int", "FROM" => "LEFT JOIN b_blog_post_category PC ON (PC.POST_ID = P.ID)"), "BLOG_USER_ALIAS" => array("FIELD" => "BU.ALIAS", "TYPE" => "string", "FROM" => "LEFT JOIN b_blog_user BU ON (P.AUTHOR_ID = BU.USER_ID)"), "BLOG_USER_AVATAR" => array("FIELD" => "BU.AVATAR", "TYPE" => "int", "FROM" => "LEFT JOIN b_blog_user BU ON (P.AUTHOR_ID = BU.USER_ID)"), "BLOG_URL" => array("FIELD" => "B.URL", "TYPE" => "string", "FROM" => "INNER JOIN b_blog B ON (P.BLOG_ID = B.ID)"), "BLOG_OWNER_ID" => array("FIELD" => "B.OWNER_ID", "TYPE" => "string", "FROM" => "INNER JOIN b_blog B ON (P.BLOG_ID = B.ID)"), "BLOG_ACTIVE" => array("FIELD" => "B.ACTIVE", "TYPE" => "string", "FROM" => "INNER JOIN b_blog B ON (P.BLOG_ID = B.ID)"), "BLOG_GROUP_ID" => array("FIELD" => "B.GROUP_ID", "TYPE" => "int", "FROM" => "INNER JOIN b_blog B ON (P.BLOG_ID = B.ID)"), "BLOG_ENABLE_RSS" => array("FIELD" => "B.ENABLE_RSS", "TYPE" => "string", "FROM" => "INNER JOIN b_blog B ON (P.BLOG_ID = B.ID)"), "BLOG_USE_SOCNET" => array("FIELD" => "B.USE_SOCNET", "TYPE" => "string", "FROM" => "INNER JOIN b_blog B ON (P.BLOG_ID = B.ID)"), "BLOG_NAME" => array("FIELD" => "B.NAME", "TYPE" => "string", "FROM" => "INNER JOIN b_blog B ON (P.BLOG_ID = B.ID)"), "BLOG_GROUP_SITE_ID" => array("FIELD" => "BG.SITE_ID", "TYPE" => "string", "FROM" => "INNER JOIN b_blog_group BG ON (B.GROUP_ID = BG.ID)"), "SOCNET_BLOG_READ" => array("FIELD" => "BSR.BLOG_ID", "TYPE" => "int", "FROM" => "INNER JOIN b_blog_socnet BSR ON (P.BLOG_ID = BSR.BLOG_ID)"), "BLOG_SOCNET_GROUP_ID" => array("FIELD" => "B.SOCNET_GROUP_ID", "TYPE" => "string", "FROM" => "INNER JOIN b_blog B ON (P.BLOG_ID = B.ID)"), "SOCNET_GROUP_ID" => array("FIELD" => "SR1.ENTITY_ID", "TYPE" => "string", "FROM" => "INNER JOIN b_blog_socnet_rights SR1 ON (P.ID = SR1.POST_ID AND SR1.ENTITY_TYPE = 'SG')"), "SOCNET_SITE_ID" => array("FIELD" => "SLS.SITE_ID", "TYPE" => "string", "FROM" => "INNER JOIN b_sonet_log BSL ON (BSL.EVENT_ID in ('blog_post', 'blog_post_micro', 'blog_post_important') AND BSL.SOURCE_ID = P.ID) ". "LEFT JOIN b_sonet_log_site SLS ON BSL.ID = SLS.LOG_ID"), "COMMENT_ID" => array("FIELD" => "PC.ID", "TYPE" => "string", "FROM" => "INNER JOIN b_blog_comment PC ON (P.ID = PC.POST_ID)"), ); foreach ($arFilter as $key => $val) { $key_res = CBlog::GetFilterOperation($key); $k = $key_res["FIELD"]; if (strpos($k, "POST_PARAM_") === 0) { $user_id = 0; $ii++; $pref = "BPP".$ii; if (is_array($val)) { $user_id = (isset($val["USER_ID"]) ? intval($val["USER_ID"]) : 0); $arFilter[$key] = $val["VALUE"]; } $arSelectFields[] = $k; $arFields[$k] = array("FIELD" => $pref.".VALUE", "TYPE" => "string", "FROM" => "LEFT JOIN b_blog_post_param ".$pref." ON (P.ID = ".$pref.".POST_ID AND ".$pref.".USER_ID". ($user_id <= 0 ? " IS NULL" : "=".$user_id)." AND ".$pref.".NAME='".$GLOBALS["DB"]->ForSql(substr($k, 11), 50)."')"); } } if(isset($arFilter["GROUP_CHECK_PERMS"])) { if(is_array($arFilter["GROUP_CHECK_PERMS"])) { foreach($arFilter["GROUP_CHECK_PERMS"] as $val) { if(IntVal($val)>0) { $arFields["POST_PERM_".$val] = Array( "FIELD" => "BUGP".$val.".PERMS", "TYPE" => "string", "FROM" => "LEFT JOIN b_blog_user_group_perms BUGP".$val." ON (P.BLOG_ID = BUGP".$val.".BLOG_ID AND P.ID = BUGP".$val.".POST_ID AND BUGP".$val.".USER_GROUP_ID = ".$val." AND BUGP".$val.".PERMS_TYPE = '".BLOG_PERMS_POST."')" ); $arSelectFields[] = "POST_PERM_".$val; } } } else { if(IntVal($arFilter["GROUP_CHECK_PERMS"])>0) { $arFields["POST_PERM_".$arFilter["GROUP_CHECK_PERMS"]] = Array( "FIELD" => "BUGP.PERMS", "TYPE" => "string", "FROM" => "LEFT JOIN b_blog_user_group_perms BUGP ON (P.BLOG_ID = BUGP.BLOG_ID AND P.ID = BUGP.POST_ID AND BUGP.USER_GROUP_ID = ".$arFilter["GROUP_CHECK_PERMS"]." AND BUGP.PERMS_TYPE = '".BLOG_PERMS_POST."')" ); $arSelectFields[] = "POST_PERM_".$arFilter["GROUP_CHECK_PERMS"]; } } unset($arFilter["GROUP_CHECK_PERMS"]); } // rating variable if ( in_array("RATING_TOTAL_VALUE", $arSelectFields) || in_array("RATING_TOTAL_VOTES", $arSelectFields) || in_array("RATING_TOTAL_POSITIVE_VOTES", $arSelectFields) || in_array("RATING_TOTAL_NEGATIVE_VOTES", $arSelectFields) || array_key_exists("RATING_TOTAL_VALUE", $arOrder) || array_key_exists("RATING_TOTAL_VOTES", $arOrder) || array_key_exists("RATING_TOTAL_POSITIVE_VOTES", $arOrder) || array_key_exists("RATING_TOTAL_NEGATIVE_VOTES", $arOrder) ) { $arSelectFields[] = 'RATING_TOTAL_VALUE'; $arSelectFields[] = 'RATING_TOTAL_VOTES'; $arSelectFields[] = 'RATING_TOTAL_POSITIVE_VOTES'; $arSelectFields[] = 'RATING_TOTAL_NEGATIVE_VOTES'; $arFields["RATING_TOTAL_VALUE"] = array("FIELD" => $DB->IsNull('RV.TOTAL_VALUE', '0'), "ORDER" => "RATING_TOTAL_VALUE", "TYPE" => "double", "FROM" => "LEFT JOIN b_rating_voting RV ON ( RV.ENTITY_TYPE_ID = 'BLOG_POST' AND RV.ENTITY_ID = P.ID )"); $arFields["RATING_TOTAL_VOTES"] = array("FIELD" => $DB->IsNull('RV.TOTAL_VOTES', '0'), "ORDER" => "RATING_TOTAL_VALUE", "TYPE" => "int", "FROM" => "LEFT JOIN b_rating_voting RV ON ( RV.ENTITY_TYPE_ID = 'BLOG_POST' AND RV.ENTITY_ID = P.ID )"); $arFields["RATING_TOTAL_POSITIVE_VOTES"] = array("FIELD" => $DB->IsNull('RV.TOTAL_POSITIVE_VOTES', '0'), "ORDER" => "RATING_TOTAL_POSITIVE_VOTES", "TYPE" => "int", "FROM" => "LEFT JOIN b_rating_voting RV ON ( RV.ENTITY_TYPE_ID = 'BLOG_POST' AND RV.ENTITY_ID = P.ID )"); $arFields["RATING_TOTAL_NEGATIVE_VOTES"] = array("FIELD" => $DB->IsNull('RV.TOTAL_NEGATIVE_VOTES', '0'), "ORDER" => "RATING_TOTAL_POSITIVE_VOTES", "TYPE" => "int", "FROM" => "LEFT JOIN b_rating_voting RV ON ( RV.ENTITY_TYPE_ID = 'BLOG_POST' AND RV.ENTITY_ID = P.ID )"); } if (in_array("RATING_USER_VOTE_VALUE", $arSelectFields)) { global $USER; if (isset($USER) && is_object($USER)) { $arSelectFields[] = 'RATING_USER_VOTE_VALUE'; $arFields["RATING_USER_VOTE_VALUE"] = Array("FIELD" => $DB->IsNull('RVV.VALUE', '0'), "ORDER" => "RATING_USER_VOTE_VALUE", "TYPE" => "double", "FROM" => "LEFT JOIN b_rating_vote RVV ON RVV.ENTITY_TYPE_ID = 'BLOG_POST' AND RVV.ENTITY_ID = P.ID AND RVV.USER_ID = ".intval($USER->GetId())); } } // <-- FIELDS $bNeedDistinct = false; $blogModulePermissions = $GLOBALS["APPLICATION"]->GetGroupRight("blog"); if ($blogModulePermissions < "W") { $user_id = 0; if(isset($USER) && is_object($USER) && $USER->IsAuthorized()) $user_id = $GLOBALS["USER"]->GetID(); if(!CBlog::IsBlogOwner($arFilter["BLOG_ID"], $user_id)) { $arUserGroups = CBlogUser::GetUserGroups($user_id, IntVal($arFilter["BLOG_ID"]), "Y", BLOG_BY_USER_ID); $strUserGroups = "0"; foreach($arUserGroups as $v) $strUserGroups .= ",".IntVal($v); $arFields["PERMS"] = array("FIELD" => "UGP.PERMS", "TYPE" => "string", "FROM" => "INNER JOIN b_blog_user_group_perms UGP ON (P.ID = UGP.POST_ID AND P.BLOG_ID = UGP.BLOG_ID AND UGP.USER_GROUP_ID IN (".$strUserGroups.") AND UGP.PERMS_TYPE = '".BLOG_PERMS_POST."')"); $bNeedDistinct = true; } else $arFields["PERMS"] = array("FIELD" => "'W'", "TYPE" => "string"); } else { $arFields["PERMS"] = array("FIELD" => "'W'", "TYPE" => "string"); } $arSqls = CBlog::PrepareSql($arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields, $obUserFieldsSql); if(array_key_exists("SOCNET_GROUP_ID", $arFilter) || array_key_exists("SOCNET_GROUP_ID", $arFilter)) $bNeedDistinct = true; if(array_key_exists("FOR_USER", $arFilter)) { if(IntVal($arFilter["FOR_USER"]) > 0) //authorized user { if($arFilter["FOR_USER_TYPE"] == "ALL") { $arSqls["FROM"] .= " INNER JOIN b_blog_socnet_rights SR ON (P.ID = SR.POST_ID) ". " LEFT JOIN b_user_access UA ON (UA.ACCESS_CODE = SR.ENTITY AND UA.USER_ID = ".IntVal($arFilter["FOR_USER"]).") "; if(strlen($arSqls["WHERE"]) > 0) $arSqls["WHERE"] .= " AND "; $arSqls["WHERE"] .= " (SR.ENTITY_TYPE != 'SG') AND ". " (SR.ENTITY = 'U".IntVal($arFilter["FOR_USER"])."' OR (UA.USER_ID is not NULL AND SR.ENTITY_TYPE = 'DR') OR P.AUTHOR_ID = '".IntVal($arFilter["FOR_USER"])."')"; } elseif($arFilter["FOR_USER_TYPE"] == "SELF") { $arSqls["FROM"] .= " INNER JOIN b_blog_socnet_rights SR ON (P.ID = SR.POST_ID) ". " LEFT JOIN b_user_access UA ON (UA.ACCESS_CODE = SR.ENTITY AND UA.USER_ID = ".IntVal($arFilter["FOR_USER"]).") "; if(strlen($arSqls["WHERE"]) > 0) $arSqls["WHERE"] .= " AND "; $arSqls["WHERE"] .= " (SR.ENTITY = 'U".IntVal($arFilter["FOR_USER"])."' OR (UA.USER_ID is not NULL AND SR.ENTITY_TYPE = 'DR')) "; } elseif($arFilter["FOR_USER_TYPE"] == "DR") { $arSqls["FROM"] .= " INNER JOIN b_blog_socnet_rights SR ON (P.ID = SR.POST_ID) " . " LEFT JOIN b_user_access UA ON (UA.ACCESS_CODE = SR.ENTITY AND UA.USER_ID = ".IntVal($arFilter["FOR_USER"]).") "; if(strlen($arSqls["WHERE"]) > 0) $arSqls["WHERE"] .= " AND "; $arSqls["WHERE"] .= " (UA.USER_ID is not NULL AND SR.ENTITY_TYPE = 'DR') "; } else { $arSqls["FROM"] .= " INNER JOIN b_blog_socnet_rights SR ON (P.ID = SR.POST_ID) " . " LEFT JOIN b_user_access UA ON (UA.ACCESS_CODE = SR.ENTITY AND UA.USER_ID = ".IntVal($arFilter["FOR_USER"]).") "; if(strlen($arSqls["WHERE"]) > 0) $arSqls["WHERE"] .= " AND "; $arSqls["WHERE"] .= " (UA.USER_ID is not NULL OR SR.ENTITY = 'AU') "; } } else { $arSqls["FROM"] .= " INNER JOIN b_blog_socnet_rights SR ON (P.ID = SR.POST_ID) ". " INNER JOIN b_user_access UA ON (UA.ACCESS_CODE = SR.ENTITY AND UA.USER_ID = 0)"; } $bNeedDistinct = true; } if($bNeedDistinct) $arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "DISTINCT", $arSqls["SELECT"]); else $arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "", $arSqls["SELECT"]); $r = $obUserFieldsSql->GetFilter(); if(strlen($r)>0) $strSqlUFFilter = " (".$r.") "; if (is_array($arGroupBy) && count($arGroupBy)==0) { $strSql = "SELECT ".$arSqls["SELECT"]." ". $obUserFieldsSql->GetSelect()." ". "FROM b_blog_post P ". " ".$arSqls["FROM"]." ". $obUserFieldsSql->GetJoin("P.ID")." "; if (strlen($arSqls["WHERE"]) > 0) $strSql .= "WHERE ".$arSqls["WHERE"]." "; if(strlen($arSqls["WHERE"]) > 0 && strlen($strSqlUFFilter) > 0) $strSql .= " AND ".$strSqlUFFilter." "; elseif(strlen($arSqls["WHERE"]) <= 0 && strlen($strSqlUFFilter) > 0) $strSql .= " WHERE ".$strSqlUFFilter." "; if (strlen($arSqls["GROUPBY"]) > 0) $strSql .= "GROUP BY ".$arSqls["GROUPBY"]." "; //echo "!1!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes = $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__); if ($arRes = $dbRes->Fetch()) return $arRes["CNT"]; else return False; } $strSql = "SELECT ".$arSqls["SELECT"]." ". $obUserFieldsSql->GetSelect()." ". "FROM b_blog_post P ". " ".$arSqls["FROM"]." ". $obUserFieldsSql->GetJoin("P.ID")." "; if (strlen($arSqls["WHERE"]) > 0) $strSql .= "WHERE ".$arSqls["WHERE"]." "; if(strlen($arSqls["WHERE"]) > 0 && strlen($strSqlUFFilter) > 0) $strSql .= " AND ".$strSqlUFFilter." "; elseif(strlen($arSqls["WHERE"]) <= 0 && strlen($strSqlUFFilter) > 0) $strSql .= " WHERE ".$strSqlUFFilter." "; if (strlen($arSqls["GROUPBY"]) > 0) $strSql .= "GROUP BY ".$arSqls["GROUPBY"]." "; if (strlen($arSqls["ORDERBY"]) > 0) $strSql .= "ORDER BY ".$arSqls["ORDERBY"]." "; if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"])<=0) { $strSql_tmp = "SELECT COUNT(DISTINCT P.ID) as CNT ". "FROM b_blog_post P ". " ".$arSqls["FROM"]." ". $obUserFieldsSql->GetJoin("P.ID")." "; if (strlen($arSqls["WHERE"]) > 0) $strSql_tmp .= "WHERE ".$arSqls["WHERE"]." "; if(strlen($arSqls["WHERE"]) > 0 && strlen($strSqlUFFilter) > 0) $strSql_tmp .= " AND ".$strSqlUFFilter." "; elseif(strlen($arSqls["WHERE"]) <= 0 && strlen($strSqlUFFilter) > 0) $strSql_tmp .= " WHERE ".$strSqlUFFilter." "; if (strlen($arSqls["GROUPBY"]) > 0) $strSql_tmp .= "GROUP BY ".$arSqls["GROUPBY"]." "; //echo "!2.1!=".htmlspecialcharsbx($strSql_tmp)."<br>"; $dbRes = $DB->Query($strSql_tmp, false, "File: ".__FILE__."<br>Line: ".__LINE__); $cnt = 0; if (strlen($arSqls["GROUPBY"]) <= 0) { if ($arRes = $dbRes->Fetch()) $cnt = $arRes["CNT"]; } else { $cnt = $dbRes->SelectedRowsCount(); } $dbRes = new CDBResult(); //echo "!2.2!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes->SetUserFields($USER_FIELD_MANAGER->GetUserFields("BLOG_POST")); $dbRes->NavQuery($strSql, $cnt, $arNavStartParams); } else { if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) > 0) $strSql .= "LIMIT ".IntVal($arNavStartParams["nTopCount"]); //echo "!3!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes = $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__); $dbRes->SetUserFields($USER_FIELD_MANAGER->GetUserFields("BLOG_POST")); } //echo "!4!=".htmlspecialcharsbx($strSql)."<br>"; return $dbRes; }
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("ID" => "DESC"), $arFilter = array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array()) { global $DB; if (count($arSelectFields) <= 0) { $arSelectFields = array("ID", "FIRST_USER_ID", "SECOND_USER_ID", "RELATION", "DATE_CREATE", "DATE_UPDATE", "MESSAGE", "INITIATED_BY"); } $online_interval = array_key_exists("ONLINE_INTERVAL", $arFilter) && intval($arFilter["ONLINE_INTERVAL"]) > 0 ? $arFilter["ONLINE_INTERVAL"] : 120; static $arFields = array("ID" => array("FIELD" => "UR.ID", "TYPE" => "int"), "FIRST_USER_ID" => array("FIELD" => "UR.FIRST_USER_ID", "TYPE" => "int"), "SECOND_USER_ID" => array("FIELD" => "UR.SECOND_USER_ID", "TYPE" => "int"), "RELATION" => array("FIELD" => "UR.RELATION", "TYPE" => "string"), "DATE_CREATE" => array("FIELD" => "UR.DATE_CREATE", "TYPE" => "datetime"), "DATE_UPDATE" => array("FIELD" => "UR.DATE_UPDATE", "TYPE" => "datetime"), "MESSAGE" => array("FIELD" => "UR.MESSAGE", "TYPE" => "string"), "INITIATED_BY" => array("FIELD" => "UR.INITIATED_BY", "TYPE" => "string"), "FIRST_USER_NAME" => array("FIELD" => "U.NAME", "TYPE" => "string", "FROM" => "INNER JOIN b_user U ON (UR.FIRST_USER_ID = U.ID)"), "FIRST_USER_LAST_NAME" => array("FIELD" => "U.LAST_NAME", "TYPE" => "string", "FROM" => "INNER JOIN b_user U ON (UR.FIRST_USER_ID = U.ID)"), "FIRST_USER_SECOND_NAME" => array("FIELD" => "U.SECOND_NAME", "TYPE" => "string", "FROM" => "INNER JOIN b_user U ON (UR.FIRST_USER_ID = U.ID)"), "FIRST_USER_LOGIN" => array("FIELD" => "U.LOGIN", "TYPE" => "string", "FROM" => "INNER JOIN b_user U ON (UR.FIRST_USER_ID = U.ID)"), "FIRST_USER_EMAIL" => array("FIELD" => "U.EMAIL", "TYPE" => "string", "FROM" => "INNER JOIN b_user U ON (UR.FIRST_USER_ID = U.ID)"), "FIRST_USER_PERSONAL_PHOTO" => array("FIELD" => "U.PERSONAL_PHOTO", "TYPE" => "int", "FROM" => "INNER JOIN b_user U ON (UR.FIRST_USER_ID = U.ID)"), "FIRST_USER_PERSONAL_GENDER" => array("FIELD" => "U.PERSONAL_GENDER", "TYPE" => "string", "FROM" => "INNER JOIN b_user U ON (UR.FIRST_USER_ID = U.ID)"), "FIRST_USER_LID" => array("FIELD" => "U.LID", "TYPE" => "string", "FROM" => "INNER JOIN b_user U ON (UR.FIRST_USER_ID = U.ID)"), "SECOND_USER_NAME" => array("FIELD" => "U1.NAME", "TYPE" => "string", "FROM" => "INNER JOIN b_user U1 ON (UR.SECOND_USER_ID = U1.ID)"), "SECOND_USER_LAST_NAME" => array("FIELD" => "U1.LAST_NAME", "TYPE" => "string", "FROM" => "INNER JOIN b_user U1 ON (UR.SECOND_USER_ID = U1.ID)"), "SECOND_USER_SECOND_NAME" => array("FIELD" => "U1.SECOND_NAME", "TYPE" => "string", "FROM" => "INNER JOIN b_user U1 ON (UR.SECOND_USER_ID = U1.ID)"), "SECOND_USER_LOGIN" => array("FIELD" => "U1.LOGIN", "TYPE" => "string", "FROM" => "INNER JOIN b_user U1 ON (UR.SECOND_USER_ID = U1.ID)"), "SECOND_USER_EMAIL" => array("FIELD" => "U1.EMAIL", "TYPE" => "string", "FROM" => "INNER JOIN b_user U1 ON (UR.SECOND_USER_ID = U1.ID)"), "SECOND_USER_PERSONAL_PHOTO" => array("FIELD" => "U1.PERSONAL_PHOTO", "TYPE" => "int", "FROM" => "INNER JOIN b_user U1 ON (UR.SECOND_USER_ID = U1.ID)"), "SECOND_USER_PERSONAL_GENDER" => array("FIELD" => "U1.PERSONAL_GENDER", "TYPE" => "string", "FROM" => "INNER JOIN b_user U1 ON (UR.SECOND_USER_ID = U1.ID)"), "SECOND_USER_LID" => array("FIELD" => "U1.LID", "TYPE" => "string", "FROM" => "INNER JOIN b_user U1 ON (UR.SECOND_USER_ID = U1.ID)"), "RAND" => array("FIELD" => "RAND()", "TYPE" => "string")); $arFields["FIRST_USER_IS_ONLINE"] = array("FIELD" => "IF(U.LAST_ACTIVITY_DATE > DATE_SUB(NOW(), INTERVAL " . $online_interval . " SECOND), 'Y', 'N')", "TYPE" => "string", "FROM" => "INNER JOIN b_user U ON (UR.FIRST_USER_ID = U.ID)"); $arFields["SECOND_USER_IS_ONLINE"] = array("FIELD" => "IF(U1.LAST_ACTIVITY_DATE > DATE_SUB(NOW(), INTERVAL " . $online_interval . " SECOND), 'Y', 'N')", "TYPE" => "string", "FROM" => "INNER JOIN b_user U1 ON (UR.SECOND_USER_ID = U1.ID)"); if (array_key_exists("ACTIVE_ONLY", $arFilter) && $arFilter["ACTIVE_ONLY"] == "Y") { $arFields["FIRST_USER_IS_ACTIVE"] = array("FIELD" => "U.ACTIVE", "TYPE" => "string", "FROM" => "INNER JOIN b_user U ON (UR.FIRST_USER_ID = U.ID)"); $arFields["SECOND_USER_IS_ACTIVE"] = array("FIELD" => "U1.ACTIVE", "TYPE" => "string", "FROM" => "INNER JOIN b_user U1 ON (UR.SECOND_USER_ID = U1.ID)"); $arFilter["FIRST_USER_IS_ACTIVE"] = "Y"; $arFilter["SECOND_USER_IS_ACTIVE"] = "Y"; } if (array_key_exists("USER_ID", $arFilter) && intval($arFilter["USER_ID"]) > 0 || array_key_exists("!USER_ID", $arFilter) && intval($arFilter["!USER_ID"]) > 0) { $key = array_key_exists("USER_ID", $arFilter) ? "USER_ID" : "!USER_ID"; $filterVal = $arFilter[$key]; unset($arFilter[$key]); $arFilter2 = $arFilter; if ($key == "USER_ID") { $arFilter2["SECOND_USER_ID"] = $filterVal; } else { $arFilter2["!SECOND_USER_ID"] = $filterVal; } $arSqls2 = CSocNetGroup::PrepareSql($arFields, $arOrder, $arFilter2, $arGroupBy, $arSelectFields); if ($key == "USER_ID") { $arFilter["FIRST_USER_ID"] = $filterVal; } else { $arFilter["!FIRST_USER_ID"] = $filterVal; } } $arSqls = CSocNetGroup::PrepareSql($arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields); $arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "", $arSqls["SELECT"]); if (is_array($arGroupBy) && count($arGroupBy) == 0) { if ($arSqls2 && strlen($arSqls2["WHERE"]) > 0) { $strSql = "SELECT COUNT(*) AS CNT FROM ("; $strSql .= "SELECT UR.ID " . "FROM b_sonet_user_relations UR " . "\t" . $arSqls["FROM"] . " " . "WHERE " . $arSqls["WHERE"] . " " . "UNION " . "SELECT UR.ID " . "FROM b_sonet_user_relations UR " . "\t" . $arSqls["FROM"] . " " . "WHERE " . $arSqls2["WHERE"] . " "; if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } $strSql .= ") AS RELS"; } else { $strSql = "SELECT " . $arSqls["SELECT"] . " " . "FROM b_sonet_user_relations UR " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } } //echo "!1!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); if ($arRes = $dbRes->Fetch()) { return $arRes["CNT"]; } else { return False; } } $strSql = "SELECT " . $arSqls["SELECT"] . " " . "FROM b_sonet_user_relations UR " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["ORDERBY"]) > 0) { $strSql .= "ORDER BY " . $arSqls["ORDERBY"] . " "; } if ($arSqls2 && strlen($arSqls2["WHERE"]) > 0) { $strSql = "(" . $strSql . ") "; $strSql .= "UNION (" . "SELECT " . $arSqls["SELECT"] . " " . "FROM b_sonet_user_relations UR " . "\t" . $arSqls["FROM"] . " " . "WHERE " . $arSqls2["WHERE"] . " "; if (strlen($arSqls2["ORDERBY"]) > 0) { $strSql .= "ORDER BY " . $arSqls2["ORDERBY"] . " "; } $strSql .= ") "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) <= 0) { $strSql_tmp = "SELECT COUNT('x') as CNT " . "FROM b_sonet_user_relations UR " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql_tmp .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql_tmp .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } //echo "!2.1!=".htmlspecialcharsbx($strSql_tmp)."<br>"; $dbRes = $DB->Query($strSql_tmp, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); $cnt = 0; if (strlen($arSqls["GROUPBY"]) <= 0) { if ($arRes = $dbRes->Fetch()) { $cnt = $arRes["CNT"]; } } else { // рнкэйн дкъ MYSQL!!! дкъ ORACLE дпсцни йнд $cnt = $dbRes->SelectedRowsCount(); } $dbRes = new CDBResult(); //echo "!2.2!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes->NavQuery($strSql, $cnt, $arNavStartParams); } else { if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) > 0) { $strSql .= "LIMIT " . IntVal($arNavStartParams["nTopCount"]); } //echo "!3!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); } return $dbRes; }
public static function GetListEx($arOrder = array("ID" => "DESC"), $arFilter = array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array()) { global $DB; if (count($arSelectFields) <= 0) { $arSelectFields = array("ID", "TIMESTAMP_X", "ACTIVE", "C_SORT", "ANONYMOUS", "NAME", "DESCRIPTION"); } // FIELDS --> $arFields = array("ID" => array("FIELD" => "G.ID", "TYPE" => "int"), "TIMESTAMP_X" => array("FIELD" => "G.TIMESTAMP_X", "TYPE" => "datetime"), "ACTIVE" => array("FIELD" => "G.ACTIVE", "TYPE" => "char"), "C_SORT" => array("FIELD" => "G.C_SORT", "TYPE" => "int"), "ANONYMOUS" => array("FIELD" => "G.ANONYMOUS", "TYPE" => "char"), "NAME" => array("FIELD" => "G.NAME", "TYPE" => "string"), "STRING_ID" => array("FIELD" => "G.STRING_ID", "TYPE" => "string"), "DESCRIPTION" => array("FIELD" => "G.DESCRIPTION", "TYPE" => "string"), "USER_USER_ID" => array("FIELD" => "UG.USER_ID", "TYPE" => "int", "FROM" => "INNER JOIN b_user_group UG ON (G.ID = UG.GROUP_ID)"), "USER_GROUP_ID" => array("FIELD" => "UG.GROUP_ID", "TYPE" => "string", "FROM" => "INNER JOIN b_user_group UG ON (G.ID = UG.GROUP_ID)"), "USER_DATE_ACTIVE_FROM" => array("FIELD" => "UG.DATE_ACTIVE_FROM", "TYPE" => "datetime", "FROM" => "INNER JOIN b_user_group UG ON (G.ID = UG.GROUP_ID)"), "USER_DATE_ACTIVE_TO" => array("FIELD" => "UG.DATE_ACTIVE_TO", "TYPE" => "datetime", "FROM" => "INNER JOIN b_user_group UG ON (G.ID = UG.GROUP_ID)")); // <-- FIELDS $arSqls = CGroup::PrepareSql($arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields); $arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "DISTINCT", $arSqls["SELECT"]); if (is_array($arGroupBy) && count($arGroupBy) == 0) { $strSql = "SELECT " . $arSqls["SELECT"] . " " . "FROM b_group G " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } $dbRes = $DB->Query($strSql); if ($arRes = $dbRes->Fetch()) { return $arRes["CNT"]; } else { return False; } } $strSql = "SELECT " . $arSqls["SELECT"] . " " . "FROM b_group G " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } if (strlen($arSqls["ORDERBY"]) > 0) { $strSql .= "ORDER BY " . $arSqls["ORDERBY"] . " "; } if (is_array($arNavStartParams) && intval($arNavStartParams["nTopCount"]) <= 0) { $strSql_tmp = "SELECT COUNT('x') as CNT " . "FROM b_group G " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql_tmp .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql_tmp .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } $dbRes = $DB->Query($strSql_tmp); $cnt = 0; if (strlen($arSqls["GROUPBY"]) <= 0) { if ($arRes = $dbRes->Fetch()) { $cnt = $arRes["CNT"]; } } else { // ТОЛЬКО ДЛЯ MYSQL!!! ДЛЯ ORACLE ДРУГОЙ КОД $cnt = $dbRes->SelectedRowsCount(); } $dbRes = new CDBResult(); $dbRes->NavQuery($strSql, $cnt, $arNavStartParams); } else { if (is_array($arNavStartParams) && intval($arNavStartParams["nTopCount"]) > 0) { $strSql .= "LIMIT " . intval($arNavStartParams["nTopCount"]); } $dbRes = $DB->Query($strSql); } return $dbRes; }
function GetList($arOrder = Array("ID" => "DESC"), $arFilter = Array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array()) { global $DB; if (count($arSelectFields) <= 0) $arSelectFields = array("ID", "BLOG_ID", "NAME"); // FIELDS --> $arFields = array( "ID" => array("FIELD" => "G.ID", "TYPE" => "int"), "BLOG_ID" => array("FIELD" => "G.BLOG_ID", "TYPE" => "int"), "NAME" => array("FIELD" => "G.NAME", "TYPE" => "string"), "USER2GROUP_ID" => array("FIELD" => "U2UG.ID", "TYPE" => "int", "FROM" => "INNER JOIN b_blog_user2user_group U2UG ON (G.ID = U2UG.USER_GROUP_ID AND G.BLOG_ID = U2UG.BLOG_ID)"), "USER2GROUP_USER_ID" => array("FIELD" => "U2UG.USER_ID", "TYPE" => "int", "FROM" => "INNER JOIN b_blog_user2user_group U2UG ON (G.ID = U2UG.USER_GROUP_ID AND G.BLOG_ID = U2UG.BLOG_ID)"), ); // <-- FIELDS $arSqls = CBlog::PrepareSql($arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields); $arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "", $arSqls["SELECT"]); if (is_array($arGroupBy) && count($arGroupBy)==0) { $strSql = "SELECT ".$arSqls["SELECT"]." ". "FROM b_blog_user_group G ". " ".$arSqls["FROM"]." "; if (strlen($arSqls["WHERE"]) > 0) $strSql .= "WHERE ".$arSqls["WHERE"]." "; if (strlen($arSqls["GROUPBY"]) > 0) $strSql .= "GROUP BY ".$arSqls["GROUPBY"]." "; //echo "!1!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes = $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__); if ($arRes = $dbRes->Fetch()) return $arRes["CNT"]; else return False; } $strSql = "SELECT ".$arSqls["SELECT"]." ". "FROM b_blog_user_group G ". " ".$arSqls["FROM"]." "; if (strlen($arSqls["WHERE"]) > 0) $strSql .= "WHERE ".$arSqls["WHERE"]." "; if (strlen($arSqls["GROUPBY"]) > 0) $strSql .= "GROUP BY ".$arSqls["GROUPBY"]." "; if (strlen($arSqls["ORDERBY"]) > 0) $strSql .= "ORDER BY ".$arSqls["ORDERBY"]." "; if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"])<=0) { $strSql_tmp = "SELECT COUNT('x') as CNT ". "FROM b_blog_user_group G ". " ".$arSqls["FROM"]." "; if (strlen($arSqls["WHERE"]) > 0) $strSql_tmp .= "WHERE ".$arSqls["WHERE"]." "; if (strlen($arSqls["GROUPBY"]) > 0) $strSql_tmp .= "GROUP BY ".$arSqls["GROUPBY"]." "; //echo "!2.1!=".htmlspecialcharsbx($strSql_tmp)."<br>"; $dbRes = $DB->Query($strSql_tmp, false, "File: ".__FILE__."<br>Line: ".__LINE__); $cnt = 0; if (strlen($arSqls["GROUPBY"]) <= 0) { if ($arRes = $dbRes->Fetch()) $cnt = $arRes["CNT"]; } else { // ТОЛЬКО ДЛЯ MYSQL!!! ДЛЯ ORACLE ДРУГОЙ КОД $cnt = $dbRes->SelectedRowsCount(); } $dbRes = new CDBResult(); //echo "!2.2!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes->NavQuery($strSql, $cnt, $arNavStartParams); } else { if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) > 0) $strSql .= "LIMIT ".IntVal($arNavStartParams["nTopCount"]); //echo "!3!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes = $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__); } return $dbRes; }
function GetList($arOrder = array("ID" => "DESC"), $arFilter = array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array(), $arOptions = array()) { global $DB, $USER_FIELD_MANAGER; if (!is_array($arOrder)) { $arOrder = array('ID' => 'DESC'); } if (!is_array($arFilter)) { $arFilter = array(); } if (!is_array($arSelectFields)) { $arSelectFields = array(); } $obUserFieldsSql = new CUserTypeSQL(); $obUserFieldsSql->SetEntity("ORDER", "O.ID"); $obUserFieldsSql->SetSelect($arSelectFields); $obUserFieldsSql->SetFilter($arFilter); $obUserFieldsSql->SetOrder($arOrder); if (array_key_exists("DATE_FROM", $arFilter)) { $val = $arFilter["DATE_FROM"]; unset($arFilter["DATE_FROM"]); $arFilter[">=DATE_INSERT"] = $val; } if (array_key_exists("DATE_TO", $arFilter)) { $val = $arFilter["DATE_TO"]; unset($arFilter["DATE_TO"]); $arFilter["<=DATE_INSERT"] = $val; } if (array_key_exists("DATE_INSERT_FROM", $arFilter)) { $val = $arFilter["DATE_INSERT_FROM"]; unset($arFilter["DATE_INSERT_FROM"]); $arFilter[">=DATE_INSERT"] = $val; } if (array_key_exists("DATE_INSERT_TO", $arFilter)) { $val = $arFilter["DATE_INSERT_TO"]; unset($arFilter["DATE_INSERT_TO"]); $arFilter["<=DATE_INSERT"] = $val; } if (array_key_exists("DATE_UPDATE_FROM", $arFilter)) { $val = $arFilter["DATE_UPDATE_FROM"]; unset($arFilter["DATE_UPDATE_FROM"]); $arFilter[">=DATE_UPDATE"] = $val; } if (array_key_exists("DATE_UPDATE_TO", $arFilter)) { $val = $arFilter["DATE_UPDATE_TO"]; unset($arFilter["DATE_UPDATE_TO"]); $arFilter["<=DATE_UPDATE"] = $val; } if (array_key_exists("DATE_STATUS_FROM", $arFilter)) { $val = $arFilter["DATE_STATUS_FROM"]; unset($arFilter["DATE_STATUS_FROM"]); $arFilter[">=DATE_STATUS"] = $val; } if (array_key_exists("DATE_STATUS_TO", $arFilter)) { $val = $arFilter["DATE_STATUS_TO"]; unset($arFilter["DATE_STATUS_TO"]); $arFilter["<=DATE_STATUS"] = $val; } if (array_key_exists("DATE_PAYED_FROM", $arFilter)) { $val = $arFilter["DATE_PAYED_FROM"]; unset($arFilter["DATE_PAYED_FROM"]); $arFilter[">=DATE_PAYED"] = $val; } if (array_key_exists("DATE_PAYED_TO", $arFilter)) { $val = $arFilter["DATE_PAYED_TO"]; unset($arFilter["DATE_PAYED_TO"]); $arFilter["<=DATE_PAYED"] = $val; } if (array_key_exists("DATE_ALLOW_DELIVERY_FROM", $arFilter)) { $val = $arFilter["DATE_ALLOW_DELIVERY_FROM"]; unset($arFilter["DATE_ALLOW_DELIVERY_FROM"]); $arFilter[">=DATE_ALLOW_DELIVERY"] = $val; } if (array_key_exists("DATE_ALLOW_DELIVERY_TO", $arFilter)) { $val = $arFilter["DATE_ALLOW_DELIVERY_TO"]; unset($arFilter["DATE_ALLOW_DELIVERY_TO"]); $arFilter["<=DATE_ALLOW_DELIVERY"] = $val; } if (array_key_exists("DATE_CANCELED_FROM", $arFilter)) { $val = $arFilter["DATE_CANCELED_FROM"]; unset($arFilter["DATE_CANCELED_FROM"]); $arFilter[">=DATE_CANCELED"] = $val; } if (array_key_exists("DATE_CANCELED_TO", $arFilter)) { $val = $arFilter["DATE_CANCELED_TO"]; unset($arFilter["DATE_CANCELED_TO"]); $arFilter["<=DATE_CANCELED"] = $val; } if (array_key_exists("DATE_DEDUCTED_FROM", $arFilter)) { $val = $arFilter["DATE_DEDUCTED_FROM"]; unset($arFilter["DATE_DEDUCTED_FROM"]); $arFilter[">=DATE_DEDUCTED"] = $val; } if (array_key_exists("DATE_DEDUCTED_TO", $arFilter)) { $val = $arFilter["DATE_DEDUCTED_TO"]; unset($arFilter["DATE_DEDUCTED_TO"]); $arFilter["<=DATE_DEDUCTED"] = $val; } if (array_key_exists("DATE_MARKED_FROM", $arFilter)) { $val = $arFilter["DATE_MARKED_FROM"]; unset($arFilter["DATE_MARKED_FROM"]); $arFilter[">=DATE_MARKED"] = $val; } if (array_key_exists("DATE_MARKED_TO", $arFilter)) { $val = $arFilter["DATE_MARKED_TO"]; unset($arFilter["DATE_MARKED_TO"]); $arFilter["<=DATE_MARKED"] = $val; } if (array_key_exists("DATE_PAY_BEFORE_FROM", $arFilter)) { $val = $arFilter["DATE_PAY_BEFORE_FROM"]; unset($arFilter["DATE_PAY_BEFORE_FROM"]); $arFilter[">=DATE_PAY_BEFORE"] = $val; } if (array_key_exists("DATE_PAY_BEFORE_TO", $arFilter)) { $val = $arFilter["DATE_PAY_BEFORE_TO"]; unset($arFilter["DATE_PAY_BEFORE_TO"]); $arFilter["<=DATE_PAY_BEFORE"] = $val; } if (array_key_exists("DELIVERY_REQUEST_SENT", $arFilter)) { if ($arFilter["DELIVERY_REQUEST_SENT"] == "Y") { $arFilter["!DELIVERY_DATE_REQUEST"] = ""; } else { $arFilter["+DELIVERY_DATE_REQUEST"] = ""; } unset($arFilter["DELIVERY_REQUEST_SENT"]); } $callback = false; if (array_key_exists("CUSTOM_SUBQUERY", $arFilter)) { $callback = $arFilter["CUSTOM_SUBQUERY"]; unset($arFilter["CUSTOM_SUBQUERY"]); } if (empty($arSelectFields)) { $arSelectFields = array("ID", "LID", "PERSON_TYPE_ID", "PAYED", "DATE_PAYED", "EMP_PAYED_ID", "CANCELED", "DATE_CANCELED", "EMP_CANCELED_ID", "REASON_CANCELED", "MARKED", "DATE_MARKED", "EMP_MARKED_ID", "REASON_MARKED", "STATUS_ID", "DATE_STATUS", "PAY_VOUCHER_NUM", "PAY_VOUCHER_DATE", "EMP_STATUS_ID", "PRICE_DELIVERY", "ALLOW_DELIVERY", "DATE_ALLOW_DELIVERY", "EMP_ALLOW_DELIVERY_ID", "DEDUCTED", "DATE_DEDUCTED", "EMP_DEDUCTED_ID", "REASON_UNDO_DEDUCTED", "RESERVED", "PRICE", "CURRENCY", "DISCOUNT_VALUE", "SUM_PAID", "USER_ID", "PAY_SYSTEM_ID", "DELIVERY_ID", "DATE_INSERT", "DATE_INSERT_FORMAT", "DATE_UPDATE", "USER_DESCRIPTION", "ADDITIONAL_INFO", "PS_STATUS", "PS_STATUS_CODE", "PS_STATUS_DESCRIPTION", "PS_STATUS_MESSAGE", "PS_SUM", "PS_CURRENCY", "PS_RESPONSE_DATE", "COMMENTS", "TAX_VALUE", "STAT_GID", "RECURRING_ID", "RECOUNT_FLAG", "USER_LOGIN", "USER_NAME", "USER_LAST_NAME", "USER_EMAIL", "DELIVERY_DOC_NUM", "DELIVERY_DOC_DATE", "DELIVERY_DATE_REQUEST", "STORE_ID", "ORDER_TOPIC", "RESPONSIBLE_ID", "RESPONSIBLE_LOGIN", "RESPONSIBLE_NAME", "RESPONSIBLE_LAST_NAME", "RESPONSIBLE_SECOND_NAME", "RESPONSIBLE_EMAIL", "RESPONSIBLE_WORK_POSITION", "RESPONSIBLE_PERSONAL_PHOTO", "RESPONSIBLE_GROUP_ID", "DATE_PAY_BEFORE", "DATE_BILL", "ACCOUNT_NUMBER", "TRACKING_NUMBER", "XML_ID"); } elseif (in_array("*", $arSelectFields)) { $arSelectFields = array("ID", "LID", "PERSON_TYPE_ID", "PAYED", "DATE_PAYED", "EMP_PAYED_ID", "CANCELED", "DATE_CANCELED", "EMP_CANCELED_ID", "REASON_CANCELED", "MARKED", "DATE_MARKED", "EMP_MARKED_ID", "REASON_MARKED", "STATUS_ID", "DATE_STATUS", "PAY_VOUCHER_NUM", "PAY_VOUCHER_DATE", "EMP_STATUS_ID", "PRICE_DELIVERY", "ALLOW_DELIVERY", "DATE_ALLOW_DELIVERY", "EMP_ALLOW_DELIVERY_ID", "DEDUCTED", "DATE_DEDUCTED", "EMP_DEDUCTED_ID", "REASON_UNDO_DEDUCTED", "RESERVED", "PRICE", "CURRENCY", "DISCOUNT_VALUE", "SUM_PAID", "USER_ID", "PAY_SYSTEM_ID", "DELIVERY_ID", "DATE_INSERT", "DATE_INSERT_FORMAT", "DATE_UPDATE", "USER_DESCRIPTION", "ADDITIONAL_INFO", "PS_STATUS", "PS_STATUS_CODE", "PS_STATUS_DESCRIPTION", "PS_STATUS_MESSAGE", "PS_SUM", "PS_CURRENCY", "PS_RESPONSE_DATE", "COMMENTS", "TAX_VALUE", "STAT_GID", "RECURRING_ID", "RECOUNT_FLAG", "USER_LOGIN", "USER_NAME", "USER_LAST_NAME", "USER_EMAIL", "DELIVERY_DOC_NUM", "DELIVERY_DOC_DATE", "DELIVERY_DATE_REQUEST", "STORE_ID", "ORDER_TOPIC", "RESPONSIBLE_ID", "RESPONSIBLE_LOGIN", "RESPONSIBLE_NAME", "RESPONSIBLE_LAST_NAME", "RESPONSIBLE_SECOND_NAME", "RESPONSIBLE_EMAIL", "RESPONSIBLE_WORK_POSITION", "RESPONSIBLE_PERSONAL_PHOTO", "RESPONSIBLE_GROUP_ID", "DATE_PAY_BEFORE", "DATE_BILL", "ACCOUNT_NUMBER", "TRACKING_NUMBER", "XML_ID"); } $maxLock = IntVal(COption::GetOptionString("sale", "MAX_LOCK_TIME", "60")); if (is_object($GLOBALS["USER"])) { $userID = IntVal($GLOBALS["USER"]->GetID()); } else { $userID = 0; } // FIELDS --> $arFields = array("ID" => array("FIELD" => "O.ID", "TYPE" => "int"), "LID" => array("FIELD" => "O.LID", "TYPE" => "string"), "PERSON_TYPE_ID" => array("FIELD" => "O.PERSON_TYPE_ID", "TYPE" => "int"), "PAYED" => array("FIELD" => "O.PAYED", "TYPE" => "char"), "DATE_PAYED" => array("FIELD" => "O.DATE_PAYED", "TYPE" => "datetime"), "EMP_PAYED_ID" => array("FIELD" => "O.EMP_PAYED_ID", "TYPE" => "int"), "CANCELED" => array("FIELD" => "O.CANCELED", "TYPE" => "char"), "DATE_CANCELED" => array("FIELD" => "O.DATE_CANCELED", "TYPE" => "datetime"), "EMP_CANCELED_ID" => array("FIELD" => "O.EMP_CANCELED_ID", "TYPE" => "int"), "REASON_CANCELED" => array("FIELD" => "O.REASON_CANCELED", "TYPE" => "string"), "STATUS_ID" => array("FIELD" => "O.STATUS_ID", "TYPE" => "char"), "DATE_STATUS" => array("FIELD" => "O.DATE_STATUS", "TYPE" => "datetime"), "PAY_VOUCHER_NUM" => array("FIELD" => "O.PAY_VOUCHER_NUM", "TYPE" => "string"), "PAY_VOUCHER_DATE" => array("FIELD" => "O.PAY_VOUCHER_DATE", "TYPE" => "date"), "EMP_STATUS_ID" => array("FIELD" => "O.EMP_STATUS_ID", "TYPE" => "int"), "PRICE_DELIVERY" => array("FIELD" => "O.PRICE_DELIVERY", "TYPE" => "double"), "ALLOW_DELIVERY" => array("FIELD" => "O.ALLOW_DELIVERY", "TYPE" => "char"), "DATE_ALLOW_DELIVERY" => array("FIELD" => "O.DATE_ALLOW_DELIVERY", "TYPE" => "datetime"), "EMP_ALLOW_DELIVERY_ID" => array("FIELD" => "O.EMP_ALLOW_DELIVERY_ID", "TYPE" => "int"), "DEDUCTED" => array("FIELD" => "O.DEDUCTED", "TYPE" => "char"), "DATE_DEDUCTED" => array("FIELD" => "O.DATE_DEDUCTED", "TYPE" => "datetime"), "EMP_DEDUCTED_ID" => array("FIELD" => "O.EMP_DEDUCTED_ID", "TYPE" => "int"), "REASON_UNDO_DEDUCTED" => array("FIELD" => "O.REASON_UNDO_DEDUCTED", "TYPE" => "string"), "RESERVED" => array("FIELD" => "O.RESERVED", "TYPE" => "char"), "MARKED" => array("FIELD" => "O.MARKED", "TYPE" => "char"), "DATE_MARKED" => array("FIELD" => "O.DATE_MARKED", "TYPE" => "datetime"), "EMP_MARKED_ID" => array("FIELD" => "O.EMP_MARKED_ID", "TYPE" => "int"), "REASON_MARKED" => array("FIELD" => "O.REASON_MARKED", "TYPE" => "string"), "PRICE" => array("FIELD" => "O.PRICE", "TYPE" => "double"), "CURRENCY" => array("FIELD" => "O.CURRENCY", "TYPE" => "string"), "DISCOUNT_VALUE" => array("FIELD" => "O.DISCOUNT_VALUE", "TYPE" => "double"), "SUM_PAID" => array("FIELD" => "O.SUM_PAID", "TYPE" => "double"), "USER_ID" => array("FIELD" => "O.USER_ID", "TYPE" => "int"), "PAY_SYSTEM_ID" => array("FIELD" => "O.PAY_SYSTEM_ID", "TYPE" => "int"), "DELIVERY_ID" => array("FIELD" => "O.DELIVERY_ID", "TYPE" => "string"), "DATE_INSERT" => array("FIELD" => "O.DATE_INSERT", "TYPE" => "datetime"), "DATE_INSERT_FORMAT" => array("FIELD" => "O.DATE_INSERT", "TYPE" => "datetime"), "DATE_UPDATE" => array("FIELD" => "O.DATE_UPDATE", "TYPE" => "datetime"), "USER_DESCRIPTION" => array("FIELD" => "O.USER_DESCRIPTION", "TYPE" => "string"), "ADDITIONAL_INFO" => array("FIELD" => "O.ADDITIONAL_INFO", "TYPE" => "string"), "PS_STATUS" => array("FIELD" => "O.PS_STATUS", "TYPE" => "char"), "PS_STATUS_CODE" => array("FIELD" => "O.PS_STATUS_CODE", "TYPE" => "string"), "PS_STATUS_DESCRIPTION" => array("FIELD" => "O.PS_STATUS_DESCRIPTION", "TYPE" => "string"), "PS_STATUS_MESSAGE" => array("FIELD" => "O.PS_STATUS_MESSAGE", "TYPE" => "string"), "PS_SUM" => array("FIELD" => "O.PS_SUM", "TYPE" => "double"), "PS_CURRENCY" => array("FIELD" => "O.PS_CURRENCY", "TYPE" => "string"), "PS_RESPONSE_DATE" => array("FIELD" => "O.PS_RESPONSE_DATE", "TYPE" => "datetime"), "COMMENTS" => array("FIELD" => "O.COMMENTS", "TYPE" => "string"), "TAX_VALUE" => array("FIELD" => "O.TAX_VALUE", "TYPE" => "double"), "STAT_GID" => array("FIELD" => "O.STAT_GID", "TYPE" => "string"), "RECURRING_ID" => array("FIELD" => "O.RECURRING_ID", "TYPE" => "int"), "RECOUNT_FLAG" => array("FIELD" => "O.RECOUNT_FLAG", "TYPE" => "char"), "AFFILIATE_ID" => array("FIELD" => "O.AFFILIATE_ID", "TYPE" => "int"), "LOCKED_BY" => array("FIELD" => "O.LOCKED_BY", "TYPE" => "int"), "LOCK_STATUS" => array("FIELD" => "if(DATE_LOCK is null, 'green', if(DATE_ADD(DATE_LOCK, interval " . $maxLock . " MINUTE)<now(), 'green', if(LOCKED_BY=" . $userID . ", 'yellow', 'red')))", "TYPE" => "string"), "LOCK_USER_NAME" => array("FIELD" => "concat('(',UL.LOGIN,') ',UL.NAME,' ',UL.LAST_NAME)", "FROM" => "LEFT JOIN b_user UL ON (O.LOCKED_BY = UL.ID)", "TYPE" => "string"), "DELIVERY_DOC_NUM" => array("FIELD" => "O.DELIVERY_DOC_NUM", "TYPE" => "string"), "DELIVERY_DOC_DATE" => array("FIELD" => "O.DELIVERY_DOC_DATE", "TYPE" => "date"), "UPDATED_1C" => array("FIELD" => "O.UPDATED_1C", "TYPE" => "string"), "STORE_ID" => array("FIELD" => "O.STORE_ID", "TYPE" => "int"), "ORDER_TOPIC" => array("FIELD" => "O.ORDER_TOPIC", "TYPE" => "string"), "RESPONSIBLE_ID" => array("FIELD" => "O.RESPONSIBLE_ID", "TYPE" => "int"), "DATE_PAY_BEFORE" => array("FIELD" => "O.DATE_PAY_BEFORE", "TYPE" => "date"), "DATE_BILL" => array("FIELD" => "O.DATE_BILL", "TYPE" => "date"), "ACCOUNT_NUMBER" => array("FIELD" => "O.ACCOUNT_NUMBER", "TYPE" => "string"), "TRACKING_NUMBER" => array("FIELD" => "O.TRACKING_NUMBER", "TYPE" => "string"), "XML_ID" => array("FIELD" => "O.XML_ID", "TYPE" => "string"), "ID_1C" => array("FIELD" => "O.ID_1C", "TYPE" => "string"), "VERSION_1C" => array("FIELD" => "O.VERSION_1C", "TYPE" => "string"), "VERSION" => array("FIELD" => "O.VERSION", "TYPE" => "int"), "EXTERNAL_ORDER" => array("FIELD" => "O.EXTERNAL_ORDER", "TYPE" => "string"), "NAME_SEARCH" => array("FIELD" => "U.NAME, U.LAST_NAME, U.SECOND_NAME, U.EMAIL, U.LOGIN, U.ID", "TYPE" => "string", "FROM" => "INNER JOIN b_user U ON (O.USER_ID = U.ID)"), "USER_LOGIN" => array("FIELD" => "U.LOGIN", "TYPE" => "string", "FROM" => "INNER JOIN b_user U ON (O.USER_ID = U.ID)"), "USER_NAME" => array("FIELD" => "U.NAME", "TYPE" => "string", "FROM" => "INNER JOIN b_user U ON (O.USER_ID = U.ID)"), "USER_LAST_NAME" => array("FIELD" => "U.LAST_NAME", "TYPE" => "string", "FROM" => "INNER JOIN b_user U ON (O.USER_ID = U.ID)"), "USER_EMAIL" => array("FIELD" => "U.EMAIL", "TYPE" => "string", "FROM" => "INNER JOIN b_user U ON (O.USER_ID = U.ID)"), "USER_GROUP_ID" => array("FIELD" => "UG.GROUP_ID", "TYPE" => "int", "FROM" => "LEFT JOIN b_user_group UG ON (UG.USER_ID = O.USER_ID)"), "RESPONSIBLE_LOGIN" => array("FIELD" => "UR.LOGIN", "TYPE" => "string", "FROM" => "LEFT JOIN b_user UR ON (O.RESPONSIBLE_ID = UR.ID)"), "RESPONSIBLE_NAME" => array("FIELD" => "UR.NAME", "TYPE" => "string", "FROM" => "LEFT JOIN b_user UR ON (O.RESPONSIBLE_ID = UR.ID)"), "RESPONSIBLE_LAST_NAME" => array("FIELD" => "UR.LAST_NAME", "TYPE" => "string", "FROM" => "LEFT JOIN b_user UR ON (O.RESPONSIBLE_ID = UR.ID)"), "RESPONSIBLE_SECOND_NAME" => array("FIELD" => "UR.SECOND_NAME", "TYPE" => "string", "FROM" => "LEFT JOIN b_user UR ON (O.RESPONSIBLE_ID = UR.ID)"), "RESPONSIBLE_EMAIL" => array("FIELD" => "UR.EMAIL", "TYPE" => "string", "FROM" => "LEFT JOIN b_user UR ON (O.RESPONSIBLE_ID = UR.ID)"), "RESPONSIBLE_WORK_POSITION" => array("FIELD" => "UR.WORK_POSITION", "TYPE" => "string", "FROM" => "LEFT JOIN b_user UR ON (O.RESPONSIBLE_ID = UR.ID)"), "RESPONSIBLE_PERSONAL_PHOTO" => array("FIELD" => "UR.PERSONAL_PHOTO", "TYPE" => "string", "FROM" => "LEFT JOIN b_user UR ON (O.RESPONSIBLE_ID = UR.ID)"), "BUYER" => array("FIELD" => "U.LOGIN,U.NAME,U.LAST_NAME,U.EMAIL,U.ID", "WHERE_ONLY" => "Y", "TYPE" => "string", "FROM" => "INNER JOIN b_user U ON (O.USER_ID = U.ID)"), "BASKET_ID" => array("FIELD" => "B.ID", "TYPE" => "int", "FROM" => "INNER JOIN b_sale_basket B ON (O.ID = B.ORDER_ID)"), "BASKET_PRODUCT_ID" => array("FIELD" => "B.PRODUCT_ID", "TYPE" => "int", "FROM" => "INNER JOIN b_sale_basket B ON (O.ID = B.ORDER_ID)"), "BASKET_PRODUCT_XML_ID" => array("FIELD" => "B.PRODUCT_XML_ID", "TYPE" => "string", "FROM" => "INNER JOIN b_sale_basket B ON (O.ID = B.ORDER_ID)"), "BASKET_MODULE" => array("FIELD" => "B.MODULE", "TYPE" => "string", "FROM" => "INNER JOIN b_sale_basket B ON (O.ID = B.ORDER_ID)"), "BASKET_NAME" => array("FIELD" => "B.NAME", "TYPE" => "string", "FROM" => "INNER JOIN b_sale_basket B ON (O.ID = B.ORDER_ID)"), "BASKET_QUANTITY" => array("FIELD" => "B.QUANTITY", "TYPE" => "int", "FROM" => "INNER JOIN b_sale_basket B ON (O.ID = B.ORDER_ID)"), "BASKET_PRICE" => array("FIELD" => "B.PRICE", "TYPE" => "double", "FROM" => "INNER JOIN b_sale_basket B ON (O.ID = B.ORDER_ID)"), "BASKET_CURRENCY" => array("FIELD" => "B.CURRENCY", "TYPE" => "string", "FROM" => "INNER JOIN b_sale_basket B ON (O.ID = B.ORDER_ID)"), "BASKET_DISCOUNT_PRICE" => array("FIELD" => "B.DISCOUNT_PRICE", "TYPE" => "double", "FROM" => "INNER JOIN b_sale_basket B ON (O.ID = B.ORDER_ID)"), "BASKET_DISCOUNT_NAME" => array("FIELD" => "B.DISCOUNT_NAME", "TYPE" => "string", "FROM" => "INNER JOIN b_sale_basket B ON (O.ID = B.ORDER_ID)"), "BASKET_DISCOUNT_VALUE" => array("FIELD" => "B.DISCOUNT_VALUE", "TYPE" => "string", "FROM" => "INNER JOIN b_sale_basket B ON (O.ID = B.ORDER_ID)"), "BASKET_DISCOUNT_COUPON" => array("FIELD" => "B.DISCOUNT_COUPON", "TYPE" => "string", "FROM" => "INNER JOIN b_sale_basket B ON (O.ID = B.ORDER_ID)"), "BASKET_VAT_RATE" => array("FIELD" => "B.VAT_RATE", "TYPE" => "string", "FROM" => "INNER JOIN b_sale_basket B ON (O.ID = B.ORDER_ID)"), "BASKET_RECOMMENDATION" => array("FIELD" => "B.RECOMMENDATION", "TYPE" => "string", "FROM" => "INNER JOIN b_sale_basket B ON (O.ID = B.ORDER_ID)"), "BASKET_PRICE_TOTAL" => array("FIELD" => "(B.PRICE * B.QUANTITY)", "TYPE" => "double", "FROM" => "INNER JOIN b_sale_basket B ON (O.ID = B.ORDER_ID)"), "STATUS_PERMS_GROUP_ID" => array("FIELD" => "SS2G.GROUP_ID", "TYPE" => "int", "FROM" => "INNER JOIN b_sale_status2group SS2G ON (O.STATUS_ID = SS2G.STATUS_ID)"), "STATUS_PERMS_PERM_VIEW" => array("FIELD" => "SS2G.PERM_VIEW", "TYPE" => "char", "FROM" => "INNER JOIN b_sale_status2group SS2G ON (O.STATUS_ID = SS2G.STATUS_ID)"), "STATUS_PERMS_PERM_CANCEL" => array("FIELD" => "SS2G.PERM_CANCEL", "TYPE" => "char", "FROM" => "INNER JOIN b_sale_status2group SS2G ON (O.STATUS_ID = SS2G.STATUS_ID)"), "STATUS_PERMS_PERM_MARK" => array("FIELD" => "SS2G.PERM_MARK", "TYPE" => "char", "FROM" => "INNER JOIN b_sale_status2group SS2G ON (O.STATUS_ID = SS2G.STATUS_ID)"), "STATUS_PERMS_PERM_DELIVERY" => array("FIELD" => "SS2G.PERM_DELIVERY", "TYPE" => "char", "FROM" => "INNER JOIN b_sale_status2group SS2G ON (O.STATUS_ID = SS2G.STATUS_ID)"), "STATUS_PERMS_PERM_DEDUCTION" => array("FIELD" => "SS2G.PERM_DEDUCTION", "TYPE" => "char", "FROM" => "INNER JOIN b_sale_status2group SS2G ON (O.STATUS_ID = SS2G.STATUS_ID)"), "STATUS_PERMS_PERM_PAYMENT" => array("FIELD" => "SS2G.PERM_PAYMENT", "TYPE" => "char", "FROM" => "INNER JOIN b_sale_status2group SS2G ON (O.STATUS_ID = SS2G.STATUS_ID)"), "STATUS_PERMS_PERM_STATUS" => array("FIELD" => "SS2G.PERM_STATUS", "TYPE" => "char", "FROM" => "INNER JOIN b_sale_status2group SS2G ON (O.STATUS_ID = SS2G.STATUS_ID)"), "STATUS_PERMS_PERM_STATUS_FROM" => array("FIELD" => "SS2G.PERM_STATUS_FROM", "TYPE" => "char", "FROM" => "INNER JOIN b_sale_status2group SS2G ON (O.STATUS_ID = SS2G.STATUS_ID)"), "STATUS_PERMS_PERM_UPDATE" => array("FIELD" => "SS2G.PERM_UPDATE", "TYPE" => "char", "FROM" => "INNER JOIN b_sale_status2group SS2G ON (O.STATUS_ID = SS2G.STATUS_ID)"), "STATUS_PERMS_PERM_DELETE" => array("FIELD" => "SS2G.PERM_DELETE", "TYPE" => "char", "FROM" => "INNER JOIN b_sale_status2group SS2G ON (O.STATUS_ID = SS2G.STATUS_ID)"), "PROPERTY_ID" => array("FIELD" => "SP.ID", "TYPE" => "int", "FROM" => "INNER JOIN b_sale_order_props_value SP ON (O.ID = SP.ORDER_ID)"), "PROPERTY_ORDER_PROPS_ID" => array("FIELD" => "SP.ORDER_PROPS_ID", "TYPE" => "int", "FROM" => "INNER JOIN b_sale_order_props_value SP ON (O.ID = SP.ORDER_ID)"), "PROPERTY_NAME" => array("FIELD" => "SP.NAME", "TYPE" => "string", "FROM" => "INNER JOIN b_sale_order_props_value SP ON (O.ID = SP.ORDER_ID)"), "PROPERTY_VALUE" => array("FIELD" => "SP.VALUE", "TYPE" => "string", "FROM" => "INNER JOIN b_sale_order_props_value SP ON (O.ID = SP.ORDER_ID)"), "PROPERTY_CODE" => array("FIELD" => "SP.CODE", "TYPE" => "string", "FROM" => "INNER JOIN b_sale_order_props_value SP ON (O.ID = SP.ORDER_ID)"), "PROPERTY_VAL_BY_CODE" => array("FIELD" => "SP.VALUE", "TYPE" => "string", "FROM" => "INNER JOIN b_sale_order_props_value SP ON (O.ID = SP.ORDER_ID)"), "COMPLETE_ORDERS" => array("WHERE" => array(self, "ProcessCompleteOrdersParam")), "DELIVERY_DATE_REQUEST" => array("FIELD" => "OD.DATE_REQUEST", "TYPE" => "datetime", "FROM" => "LEFT JOIN b_sale_order_delivery OD ON (O.ID = OD.ORDER_ID)")); // <-- FIELDS $arPropIDsTmp = array(); foreach ($arOrder as $key => $value) { CSaleOrder::PrepareGetListArray($key, $arFields, $arPropIDsTmp); } foreach ($arFilter as $key => $value) { $arKeyTmp = CSaleOrder::GetFilterOperation($key); $key = $arKeyTmp["FIELD"]; CSaleOrder::PrepareGetListArray($key, $arFields, $arPropIDsTmp); } if (is_array($arGroupBy)) { foreach ($arGroupBy as $key => $value) { CSaleOrder::PrepareGetListArray($key, $arFields, $arPropIDsTmp); } } foreach ($arSelectFields as $key => $value) { CSaleOrder::PrepareGetListArray($key, $arFields, $arPropIDsTmp); } $arSqls = CSaleOrder::PrepareSql($arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields, $obUserFieldsSql, $callback, $arOptions); $arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "", $arSqls["SELECT"]); $r = $obUserFieldsSql->GetFilter(); $strSqlUFFilter = ''; if (strlen($r) > 0) { $strSqlUFFilter = " (" . $r . ") "; } if (is_array($arGroupBy) && count($arGroupBy) == 0) { $strSql = "SELECT " . $arSqls["SELECT"] . " " . $obUserFieldsSql->GetSelect() . " " . "FROM b_sale_order O " . "\t" . $arSqls["FROM"] . " " . $obUserFieldsSql->GetJoin("O.ID") . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["WHERE"]) > 0 && strlen($strSqlUFFilter) > 0) { $strSql .= " AND " . $strSqlUFFilter . " "; } elseif (strlen($arSqls["WHERE"]) <= 0 && strlen($strSqlUFFilter) > 0) { $strSql .= " WHERE " . $strSqlUFFilter . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } //echo "!1!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); $dbRes->SetUserFields($USER_FIELD_MANAGER->GetUserFields("ORDER")); if ($arRes = $dbRes->Fetch()) { return $arRes["CNT"]; } else { return False; } } $strSql = "SELECT " . $arSqls["SELECT"] . " " . $obUserFieldsSql->GetSelect() . " " . "FROM b_sale_order O " . "\t" . $arSqls["FROM"] . " " . $obUserFieldsSql->GetJoin("O.ID") . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["WHERE"]) > 0 && strlen($strSqlUFFilter) > 0) { $strSql .= " AND " . $strSqlUFFilter . " "; } elseif (strlen($arSqls["WHERE"]) <= 0 && strlen($strSqlUFFilter) > 0) { $strSql .= " WHERE " . $strSqlUFFilter . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } if (strlen($arSqls["ORDERBY"]) > 0) { $strSql .= "ORDER BY " . $arSqls["ORDERBY"] . " "; } if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) <= 0) { $strSql_tmp = "SELECT COUNT('x') as CNT " . "FROM b_sale_order O " . "\t" . $arSqls["FROM"] . " " . $obUserFieldsSql->GetJoin("O.ID") . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql_tmp .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["WHERE"]) > 0 && strlen($strSqlUFFilter) > 0) { $strSql_tmp .= " AND " . $strSqlUFFilter . " "; } elseif (strlen($arSqls["WHERE"]) <= 0 && strlen($strSqlUFFilter) > 0) { $strSql_tmp .= " WHERE " . $strSqlUFFilter . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql_tmp .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } //echo "!2.1!=".htmlspecialcharsbx($strSql_tmp)."<br>"; $dbRes = $DB->Query($strSql_tmp, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); $cnt = 0; if (strlen($arSqls["GROUPBY"]) <= 0) { if ($arRes = $dbRes->Fetch()) { $cnt = $arRes["CNT"]; } } else { $cnt = $dbRes->SelectedRowsCount(); } $dbRes = new CDBResult(); //echo "!2.2!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes->SetUserFields($USER_FIELD_MANAGER->GetUserFields("ORDER")); $dbRes->NavQuery($strSql, $cnt, $arNavStartParams); } else { if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) > 0) { $strSql .= "LIMIT " . IntVal($arNavStartParams["nTopCount"]); } //echo "!3!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); $dbRes->SetUserFields($USER_FIELD_MANAGER->GetUserFields("ORDER")); } return $dbRes; }
function GetList($arOrder = array(), $arFilter = array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array()) { global $DB; if (!is_array($arOrder) && !is_array($arFilter)) { $arOrder = strval($arOrder); $arFilter = strval($arFilter); if (strlen($arOrder) > 0 && strlen($arFilter) > 0) { $arOrder = array($arOrder => $arFilter); } else { $arOrder = array(); } if (is_array($arGroupBy)) { $arFilter = $arGroupBy; } else { $arFilter = array(); } $arGroupBy = false; if (count($arSelectFields) <= 0) { $arSelectFields = array("ID", "USER_PROPS_ID", "ORDER_PROPS_ID", "USER_VALUE_NAME", "VALUE", "TYPE", "SORT", "VARIANT_NAME", "CODE"); } } if (count($arSelectFields) <= 0) { $arSelectFields = array("ID", "USER_PROPS_ID", "ORDER_PROPS_ID", "NAME", "VALUE", "PROP_ID", "PROP_PERSON_TYPE_ID", "PROP_NAME", "PROP_TYPE", "PROP_REQUIED", "PROP_DEFAULT_VALUE", "PROP_SORT", "PROP_USER_PROPS", "PROP_IS_LOCATION", "PROP_PROPS_GROUP_ID", "PROP_SIZE1", "PROP_SIZE2", "PROP_DESCRIPTION", "PROP_IS_EMAIL", "PROP_IS_PROFILE_NAME", "PROP_IS_PAYER", "PROP_IS_LOCATION4TAX", "PROP_IS_ZIP", "PROP_CODE", "VARIANT_ID", "VARIANT_ORDER_PROPS_ID", "VARIANT_NAME", "VARIANT_VALUE", "VARIANT_SORT", "VARIANT_DESCRIPTION"); } // TODO proper compatibility CAllSaleOrderUserPropsValue::getList15 $sale15converted = \Bitrix\Main\Config\Option::get('main', '~sale_converted_15', 'N') == 'Y'; if ($sale15converted && is_array($arSelectFields) && $arSelectFields) { if (($i = array_search('PROP_SIZE1', $arSelectFields)) !== false) { unset($arSelectFields[$i]); } if (($i = array_search('PROP_SIZE2', $arSelectFields)) !== false) { unset($arSelectFields[$i]); } } // FIELDS --> $arFields = array("ID" => array("FIELD" => "UP.ID", "TYPE" => "int"), "USER_PROPS_ID" => array("FIELD" => "UP.USER_PROPS_ID", "TYPE" => "int"), "ORDER_PROPS_ID" => array("FIELD" => "UP.ORDER_PROPS_ID", "TYPE" => "int"), "NAME" => array("FIELD" => "UP.NAME", "TYPE" => "string"), "PROP_ID" => array("FIELD" => "P.ID", "TYPE" => "int", "FROM" => "INNER JOIN b_sale_order_props P ON (UP.ORDER_PROPS_ID = P.ID)"), "PROP_PERSON_TYPE_ID" => array("FIELD" => "P.PERSON_TYPE_ID", "TYPE" => "int", "FROM" => "INNER JOIN b_sale_order_props P ON (UP.ORDER_PROPS_ID = P.ID)"), "PROP_NAME" => array("FIELD" => "P.NAME", "TYPE" => "string", "FROM" => "INNER JOIN b_sale_order_props P ON (UP.ORDER_PROPS_ID = P.ID)"), "PROP_TYPE" => array("FIELD" => "P.TYPE", "TYPE" => "string", "FROM" => "INNER JOIN b_sale_order_props P ON (UP.ORDER_PROPS_ID = P.ID)"), "PROP_REQUIED" => array("FIELD" => "P.REQUI" . ($sale15converted ? 'R' : '') . "ED", "TYPE" => "char", "FROM" => "INNER JOIN b_sale_order_props P ON (UP.ORDER_PROPS_ID = P.ID)"), "PROP_DEFAULT_VALUE" => array("FIELD" => "P.DEFAULT_VALUE", "TYPE" => "string", "FROM" => "INNER JOIN b_sale_order_props P ON (UP.ORDER_PROPS_ID = P.ID)"), "PROP_SORT" => array("FIELD" => "P.SORT", "TYPE" => "int", "FROM" => "INNER JOIN b_sale_order_props P ON (UP.ORDER_PROPS_ID = P.ID)"), "PROP_USER_PROPS" => array("FIELD" => "P.USER_PROPS", "TYPE" => "char", "FROM" => "INNER JOIN b_sale_order_props P ON (UP.ORDER_PROPS_ID = P.ID)"), "PROP_IS_LOCATION" => array("FIELD" => "P.IS_LOCATION", "TYPE" => "char", "FROM" => "INNER JOIN b_sale_order_props P ON (UP.ORDER_PROPS_ID = P.ID)"), "PROP_PROPS_GROUP_ID" => array("FIELD" => "P.PROPS_GROUP_ID", "TYPE" => "int", "FROM" => "INNER JOIN b_sale_order_props P ON (UP.ORDER_PROPS_ID = P.ID)"), "PROP_SIZE1" => array("FIELD" => "P.SIZE1", "TYPE" => "int", "FROM" => "INNER JOIN b_sale_order_props P ON (UP.ORDER_PROPS_ID = P.ID)"), "PROP_SIZE2" => array("FIELD" => "P.SIZE2", "TYPE" => "int", "FROM" => "INNER JOIN b_sale_order_props P ON (UP.ORDER_PROPS_ID = P.ID)"), "PROP_DESCRIPTION" => array("FIELD" => "P.DESCRIPTION", "TYPE" => "string", "FROM" => "INNER JOIN b_sale_order_props P ON (UP.ORDER_PROPS_ID = P.ID)"), "PROP_IS_EMAIL" => array("FIELD" => "P.IS_EMAIL", "TYPE" => "char", "FROM" => "INNER JOIN b_sale_order_props P ON (UP.ORDER_PROPS_ID = P.ID)"), "PROP_IS_PROFILE_NAME" => array("FIELD" => "P.IS_PROFILE_NAME", "TYPE" => "char", "FROM" => "INNER JOIN b_sale_order_props P ON (UP.ORDER_PROPS_ID = P.ID)"), "PROP_IS_PAYER" => array("FIELD" => "P.IS_PAYER", "TYPE" => "char", "FROM" => "INNER JOIN b_sale_order_props P ON (UP.ORDER_PROPS_ID = P.ID)"), "PROP_IS_LOCATION4TAX" => array("FIELD" => "P.IS_LOCATION4TAX", "TYPE" => "char", "FROM" => "INNER JOIN b_sale_order_props P ON (UP.ORDER_PROPS_ID = P.ID)"), "PROP_IS_ZIP" => array("FIELD" => "P.IS_ZIP", "TYPE" => "char", "FROM" => "INNER JOIN b_sale_order_props P ON (UP.ORDER_PROPS_ID = P.ID)"), "PROP_CODE" => array("FIELD" => "P.CODE", "TYPE" => "string", "FROM" => "INNER JOIN b_sale_order_props P ON (UP.ORDER_PROPS_ID = P.ID)"), "PROP_ACTIVE" => array("FIELD" => "P.ACTIVE", "TYPE" => "char", "FROM" => "INNER JOIN b_sale_order_props P ON (UP.ORDER_PROPS_ID = P.ID)"), "PROP_UTIL" => array("FIELD" => "P.UTIL", "TYPE" => "char", "FROM" => "INNER JOIN b_sale_order_props P ON (UP.ORDER_PROPS_ID = P.ID)"), "VARIANT_ID" => array("FIELD" => "PV.ID", "TYPE" => "int", "FROM" => "LEFT JOIN b_sale_order_props_variant PV ON (UP.ORDER_PROPS_ID = PV.ORDER_PROPS_ID AND UP.VALUE = PV.VALUE)"), "VARIANT_ORDER_PROPS_ID" => array("FIELD" => "PV.ORDER_PROPS_ID", "TYPE" => "int", "FROM" => "LEFT JOIN b_sale_order_props_variant PV ON (UP.ORDER_PROPS_ID = PV.ORDER_PROPS_ID AND UP.VALUE = PV.VALUE)"), "VARIANT_NAME" => array("FIELD" => "PV.NAME", "TYPE" => "string", "FROM" => "LEFT JOIN b_sale_order_props_variant PV ON (UP.ORDER_PROPS_ID = PV.ORDER_PROPS_ID AND UP.VALUE = PV.VALUE)"), "VARIANT_VALUE" => array("FIELD" => "PV.VALUE", "TYPE" => "string", "FROM" => "LEFT JOIN b_sale_order_props_variant PV ON (UP.ORDER_PROPS_ID = PV.ORDER_PROPS_ID AND UP.VALUE = PV.VALUE)"), "VARIANT_SORT" => array("FIELD" => "PV.SORT", "TYPE" => "int", "FROM" => "LEFT JOIN b_sale_order_props_variant PV ON (UP.ORDER_PROPS_ID = PV.ORDER_PROPS_ID AND UP.VALUE = PV.VALUE)"), "VARIANT_DESCRIPTION" => array("FIELD" => "PV.DESCRIPTION", "TYPE" => "string", "FROM" => "LEFT JOIN b_sale_order_props_variant PV ON (UP.ORDER_PROPS_ID = PV.ORDER_PROPS_ID AND UP.VALUE = PV.VALUE)"), "USER_VALUE_NAME" => array("FIELD" => "PV.NAME", "TYPE" => "string"), "TYPE" => array("FIELD" => "P.TYPE", "TYPE" => "string", "FROM" => "INNER JOIN b_sale_order_props P ON (UP.ORDER_PROPS_ID = P.ID)"), "SORT" => array("FIELD" => "P.SORT", "TYPE" => "int", "FROM" => "INNER JOIN b_sale_order_props P ON (UP.ORDER_PROPS_ID = P.ID)"), "CODE" => array("FIELD" => "P.CODE", "TYPE" => "string", "FROM" => "INNER JOIN b_sale_order_props P ON (UP.ORDER_PROPS_ID = P.ID)")); // <-- FIELDS self::addPropertyValueField('UP', $arFields, $arSelectFields); $arSqls = CSaleOrder::PrepareSql($arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields); $arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "DISTINCT", $arSqls["SELECT"]); if (is_array($arGroupBy) && count($arGroupBy) == 0) { $strSql = "SELECT " . $arSqls["SELECT"] . " " . "FROM b_sale_user_props_value UP " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } //echo "!1!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); if ($arRes = $dbRes->Fetch()) { return $arRes["CNT"]; } else { return False; } } $strSql = "SELECT " . $arSqls["SELECT"] . " " . "FROM b_sale_user_props_value UP " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } if (strlen($arSqls["ORDERBY"]) > 0) { $strSql .= "ORDER BY " . $arSqls["ORDERBY"] . " "; } if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) <= 0) { $strSql_tmp = "SELECT COUNT('x') as CNT " . "FROM b_sale_user_props_value UP " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql_tmp .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql_tmp .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } //echo "!2.1!=".htmlspecialcharsbx($strSql_tmp)."<br>"; $dbRes = $DB->Query($strSql_tmp, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); $cnt = 0; if (strlen($arSqls["GROUPBY"]) <= 0) { if ($arRes = $dbRes->Fetch()) { $cnt = $arRes["CNT"]; } } else { // рнкэйн дкъ MYSQL!!! дкъ ORACLE дпсцни йнд $cnt = $dbRes->SelectedRowsCount(); } $dbRes = new CDBResult(); //echo "!2.2!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes->NavQuery($strSql, $cnt, $arNavStartParams); } else { if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) > 0) { $strSql .= "LIMIT " . IntVal($arNavStartParams["nTopCount"]); } //echo "!3!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); } return $dbRes; }
function GetList($arOrder = array("ID" => "DESC"), $arFilter = array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array()) { global $DB; if (array_key_exists("DATE_FROM", $arFilter)) { $val = $arFilter["DATE_FROM"]; unset($arFilter["DATE_FROM"]); $arFilter[">=DATE_CREATE"] = $val; } if (array_key_exists("DATE_TO", $arFilter)) { $val = $arFilter["DATE_TO"]; unset($arFilter["DATE_TO"]); $arFilter["<=DATE_CREATE"] = $val; } if (count($arSelectFields) <= 0) { $arSelectFields = array("ID", "BASKET_ID", "BARCODE", "STORE_ID", "QUANTITY", "DATE_CREATE", "DATE_MODIFY", "CREATED_BY", "MODIFIED_BY"); } elseif (in_array("*", $arSelectFields)) { $arSelectFields = array("ID", "BASKET_ID", "BARCODE", "STORE_ID", "QUANTITY", "DATE_CREATE", "DATE_MODIFY", "CREATED_BY", "MODIFIED_BY"); } // FIELDS --> $arFields = array("ID" => array("FIELD" => "SB.ID", "TYPE" => "int"), "BASKET_ID" => array("FIELD" => "SB.BASKET_ID", "TYPE" => "int"), "BARCODE" => array("FIELD" => "SB.BARCODE", "TYPE" => "string"), "STORE_ID" => array("FIELD" => "SB.STORE_ID", "TYPE" => "int"), "QUANTITY" => array("FIELD" => "SB.QUANTITY", "TYPE" => "double"), "DATE_CREATE" => array("FIELD" => "SB.DATE_CREATE", "TYPE" => "datetime"), "CREATED_BY" => array("FIELD" => "SB.CREATED_BY", "TYPE" => "int"), "DATE_MODIFY" => array("FIELD" => "SB.DATE_MODIFY", "TYPE" => "datetime"), "MODIFIED_BY" => array("FIELD" => "SB.MODIFIED_BY", "TYPE" => "int")); // <-- FIELDS $arSqls = CSaleOrder::PrepareSql($arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields); $arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "", $arSqls["SELECT"]); if (is_array($arGroupBy) && count($arGroupBy) == 0) { $strSql = "SELECT " . $arSqls["SELECT"] . " " . "FROM b_sale_store_barcode SB " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } //echo "!1!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); if ($arRes = $dbRes->Fetch()) { return $arRes["CNT"]; } else { return False; } } $strSql = "SELECT " . $arSqls["SELECT"] . " " . "FROM b_sale_store_barcode SB " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } if (strlen($arSqls["ORDERBY"]) > 0) { $strSql .= "ORDER BY " . $arSqls["ORDERBY"] . " "; } if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) <= 0) { $strSql_tmp = "SELECT COUNT('x') as CNT " . "FROM b_sale_store_barcode SB " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql_tmp .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql_tmp .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } //echo "!2.1!=".htmlspecialcharsbx($strSql_tmp)."<br>"; $dbRes = $DB->Query($strSql_tmp, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); $cnt = 0; if (strlen($arSqls["GROUPBY"]) <= 0) { if ($arRes = $dbRes->Fetch()) { $cnt = $arRes["CNT"]; } } else { // FOR MYSQL!!! ANOTHER CODE FOR ORACLE $cnt = $dbRes->SelectedRowsCount(); } $dbRes = new CDBResult(); //echo "!2.2!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes->NavQuery($strSql, $cnt, $arNavStartParams); } else { if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) > 0) { $strSql .= "LIMIT " . IntVal($arNavStartParams["nTopCount"]); } //echo "!3!=".htmlspecialcharsbx($strSql)."<br>"; $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); } return $dbRes; }
protected function __GetDiscountEntityList($arOrder = array(), $arFilter = array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array()) { global $DB; $arFields = array("ID" => array("FIELD" => "DC.ID", "TYPE" => "int"), "DISCOUNT_ID" => array("FIELD" => "DC.DISCOUNT_ID", "TYPE" => "int"), "CATALOG_GROUP_ID" => array("FIELD" => "DC.PRICE_TYPE_ID", "TYPE" => "int"), "PRICE_TYPE_ID" => array("FIELD" => "DC.PRICE_TYPE_ID", "TYPE" => "int"), "USER_GROUP_ID" => array("FIELD" => "DC.USER_GROUP_ID", "TYPE" => "int"), "GROUP_ID" => array("FIELD" => "DC.USER_GROUP_ID", "TYPE" => "int")); $arSqls = CCatalog::PrepareSql($arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields); $arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "", $arSqls["SELECT"]); if (empty($arGroupBy) && is_array($arGroupBy)) { $strSql = "SELECT " . $arSqls["SELECT"] . " FROM b_catalog_discount_cond DC " . $arSqls["FROM"]; if (!empty($arSqls["WHERE"])) { $strSql .= " WHERE " . $arSqls["WHERE"]; } if (!empty($arSqls["GROUPBY"])) { $strSql .= " GROUP BY " . $arSqls["GROUPBY"]; } $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); if ($arRes = $dbRes->Fetch()) { return $arRes["CNT"]; } else { return false; } } $strSql = "SELECT " . $arSqls["SELECT"] . " FROM b_catalog_discount_cond DC " . $arSqls["FROM"]; if (!empty($arSqls["WHERE"])) { $strSql .= " WHERE " . $arSqls["WHERE"]; } if (!empty($arSqls["GROUPBY"])) { $strSql .= " GROUP BY " . $arSqls["GROUPBY"]; } if (!empty($arSqls["ORDERBY"])) { $strSql .= " ORDER BY " . $arSqls["ORDERBY"]; } $intTopCount = 0; $boolNavStartParams = !empty($arNavStartParams) && is_array($arNavStartParams); if ($boolNavStartParams && array_key_exists('nTopCount', $arNavStartParams)) { $intTopCount = intval($arNavStartParams["nTopCount"]); } if ($boolNavStartParams && 0 >= $intTopCount) { $strSql_tmp = "SELECT COUNT('x') as CNT FROM b_catalog_discount_cond DC " . $arSqls["FROM"]; if (!empty($arSqls["WHERE"])) { $strSql_tmp .= " WHERE " . $arSqls["WHERE"]; } if (!empty($arSqls["GROUPBY"])) { $strSql_tmp .= " GROUP BY " . $arSqls["GROUPBY"]; } $dbRes = $DB->Query($strSql_tmp, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); $cnt = 0; if (empty($arSqls["GROUPBY"])) { if ($arRes = $dbRes->Fetch()) { $cnt = $arRes["CNT"]; } } else { $cnt = $dbRes->SelectedRowsCount(); } $dbRes = new CDBResult(); $dbRes->NavQuery($strSql, $cnt, $arNavStartParams); } else { if ($boolNavStartParams && 0 < $intTopCount) { $strSql .= " LIMIT " . $intTopCount; } $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); } return $dbRes; }
/** * <p>Функция возвращает результат выборки записей товаров в соответствии со своими параметрами. </p> * * * * * @param array $arOrder = array() Массив, в соответствии с которым сортируются результирующие * записи. Массив имеет вид: <pre class="syntax">array( "название_поля1" => * "направление_сортировки1", "название_поля2" => * "направление_сортировки2", . . . )</pre> В качестве "название_поля<i>N</i>" * может стоять любое поле товара, а в качестве * "направление_сортировки<i>X</i>" могут быть значения "<i>ASC</i>" (по * возрастанию) и "<i>DESC</i>" (по убыванию).<br><br> Если массив сортировки * имеет несколько элементов, то результирующий набор сортируется * последовательно по каждому элементу (т.е. сначала сортируется по * первому элементу, потом результат сортируется по второму и * т.д.). <br><br> Значение по умолчанию - пустой массив array() - означает, * что результат отсортирован не будет. * * * * @param array $arFilter = array() Массив, в соответствии с которым фильтруются записи товара. * Массив имеет вид: <pre class="syntax">array( * "[модификатор1][оператор1]название_поля1" => "значение1", * "[модификатор2][оператор2]название_поля2" => "значение2", . . . )</pre> * Удовлетворяющие фильтру записи возвращаются в результате, а * записи, которые не удовлетворяют условиям фильтра, * отбрасываются.<br><br> Допустимыми являются следующие модификаторы: * <ul> <li> <b> !</b> - отрицание;</li> <li> <b> +</b> - значения null, 0 и пустая строка * так же удовлетворяют условиям фильтра.</li> </ul> Допустимыми * являются следующие операторы: <ul> <li> <b>>=</b> - значение поля больше * или равно передаваемой в фильтр величины;</li> <li> <b>></b> - значение * поля строго больше передаваемой в фильтр величины;</li> <li><b> - * значение поля меньше или равно передаваемой в фильтр * величины;</b></li> <li><b> - значение поля строго меньше передаваемой в * фильтр величины;</b></li> <li> <b>@</b> - значение поля находится в * передаваемом в фильтр разделенном запятой списке значений;</li> <li> * <b>~</b> - значение поля проверяется на соответствие передаваемому в * фильтр шаблону;</li> <li> <b>%</b> - значение поля проверяется на * соответствие передаваемой в фильтр строке в соответствии с * языком запросов.</li> </ul> В качестве "название_поляX" может стоять * любое поле товара.<br><br> Пример фильтра: <pre class="syntax">array("SUBSCRIPTION" => * "Y")</pre> Этот фильтр означает "выбрать все записи, в которых * значение в поле SUBSCRIPTION (флаг "Продажа контента") равно Y".<br><br> * Значение по умолчанию - пустой массив array() - означает, что * результат отфильтрован не будет. * * * * @param array $arGroupBy = false Массив полей, по которым группируются записи типов товара. Массив * имеет вид: <pre class="syntax">array("название_поля1", "название_поля2", . . .)</pre> В * качестве "название_поля<i>N</i>" может стоять любое поле типов * товара. <br><br> Если массив пустой, то функция вернет число записей, * удовлетворяющих фильтру.<br><br> Значение по умолчанию - <i>false</i> - * означает, что результат группироваться не будет. * * * * @param array $arNavStartParams = false Массив параметров выборки. Может содержать следующие ключи: <ul> * <li>"<b>nTopCount</b>" - количество возвращаемых функцией записей будет * ограничено сверху значением этого ключа;</li> <li> любой ключ, * принимаемый методом <b> CDBResult::NavQuery</b> в качестве третьего * параметра.</li> </ul> Значение по умолчанию - <i>false</i> - означает, что * параметров выборки нет. * * * * @param array $arSelectFields = array() Массив полей записей, которые будут возвращены функцией. Можно * указать только те поля, которые необходимы. Если в массиве * присутствует значение "*", то будут возвращены все доступные * поля.<br><br> Значение по умолчанию - пустой массив array() - означает, * что будут возвращены все поля основной таблицы запроса. * * * * @return CDBResult <p>Объект класса CDBResult, содержащий записи в виде ассоциативных * массивов параметров товара с ключами</p><table class="tnormal" width="100%"> <tr> <th * width="15%">Ключ</th> <th>Описание</th> </tr> <tr> <td>ID</td> <td>Код товара.</td> </tr> <tr> * <td>QUANTITY</td> <td>Количество на складе.</td> </tr> <tr> <td>QUANTITY_TRACE</td> <td>Флаг * (Y/N/D - значение берется из настроек модуля) "уменьшать количество * при оформлении заказа"</td> </tr> <tr> <td>WEIGHT</td> <td>Вес единицы товара.</td> * </tr> <tr> <td>PRICE_TYPE</td> <td>Тип цены (S - одноразовый платеж, R - регулярные * платежи, T - пробная подписка)</td> </tr> <tr> <td>RECUR_SCHEME_TYPE</td> <td>Тип * периода подписки ("H" - час, "D" - сутки, "W" - неделя, "M" - месяц, "Q" - * квартал, "S" - полугодие, "Y" - год)</td> </tr> <tr> <td>RECUR_SCHEME_LENGTH</td> <td>Длина * периода подписки.</td> </tr> <tr> <td>TRIAL_PRICE_ID</td> <td>Код товара, для * которого данный товар является пробным.</td> </tr> <tr> <td>WITHOUT_ORDER</td> * <td>Флаг "Продление подписки без оформления заказа"</td> </tr> <tr> * <td>VAT_ID</td> <td>Идентификатор ставки НДС.</td> </tr> <tr> <td>VAT_INCLUDED</td> * <td>Признак включённости НДС в цену (Y/N).</td> </tr> <tr> <td>PURCHASING_PRICE</td> * <td>Величина закупочной цены.</td> </tr> <tr> <td>PURCHASING_CURRENCY</td> <td>Валюта * закупочной цены.</td> </tr> <tr> <td>CAN_BUY_ZERO</td> <td>Флаг (Y/N/D - значение * берется из настроек модуля) "разрешить покупку при отсутствии * товара".</td> </tr> <tr> <td>NEGATIVE_AMOUNT_TRACE</td> <td>Флаг (Y/N/D - значение берется из * настроек модуля) "разрешить отрицательное количество товара".</td> * </tr> </table><a name="examples"></a> * * * <h4>Example</h4> * <pre> * <? * // Выведем коды 10 товаров с самым большим количеством на складе * // из тех, количество которых при заказе должно уменьшаться * $ind = 0; * $db_res = CCatalogProduct::GetList( * array("QUANTITY" => "DESC"), * array("QUANTITY_TRACE" => "Y"), * false, * array("nTopCount" => 10) * ); * while (($ar_res = $db_res->Fetch()) && ($ind < 10)) * { * echo $ar_res["ID"].", "; * $ind++; * } * ?> * </pre> * * * @static * @link http://dev.1c-bitrix.ru/api_help/catalog/classes/ccatalogproduct/ccatalogproduct__getlist.971a2b70.php * @author Bitrix */ public static function GetList($arOrder = array(), $arFilter = array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array()) { global $DB; if (!is_array($arOrder) && !is_array($arFilter)) { $arOrder = strval($arOrder); $arFilter = strval($arFilter); if ('' != $arOrder && '' != $arFilter) $arOrder = array($arOrder => $arFilter); else $arOrder = array(); if (is_array($arGroupBy)) $arFilter = $arGroupBy; else $arFilter = array(); $arGroupBy = false; } $arFields = array( "ID" => array("FIELD" => "CP.ID", "TYPE" => "int"), "QUANTITY" => array("FIELD" => "CP.QUANTITY", "TYPE" => "double"), "QUANTITY_RESERVED" => array("FIELD" => "CP.QUANTITY_RESERVED", "TYPE" => "double"), "QUANTITY_TRACE_ORIG" => array("FIELD" => "CP.QUANTITY_TRACE", "TYPE" => "char"), "CAN_BUY_ZERO_ORIG" => array("FIELD" => "CP.CAN_BUY_ZERO", "TYPE" => "char"), "NEGATIVE_AMOUNT_TRACE_ORIG" => array("FIELD" => "CP.NEGATIVE_AMOUNT_TRACE", "TYPE" => "char"), "QUANTITY_TRACE" => array("FIELD" => "IF (CP.QUANTITY_TRACE = 'D', '".$DB->ForSql(COption::GetOptionString('catalog','default_quantity_trace','N'))."', CP.QUANTITY_TRACE)", "TYPE" => "char"), "CAN_BUY_ZERO" => array("FIELD" => "IF (CP.CAN_BUY_ZERO = 'D', '".$DB->ForSql(COption::GetOptionString('catalog','default_can_buy_zero','N'))."', CP.CAN_BUY_ZERO)", "TYPE" => "char"), "NEGATIVE_AMOUNT_TRACE" => array("FIELD" => "IF (CP.NEGATIVE_AMOUNT_TRACE = 'D', '".$DB->ForSql(COption::GetOptionString('catalog','allow_negative_amount','N'))."', CP.NEGATIVE_AMOUNT_TRACE)", "TYPE" => "char"), "WEIGHT" => array("FIELD" => "CP.WEIGHT", "TYPE" => "double"), "TIMESTAMP_X" => array("FIELD" => "CP.TIMESTAMP_X", "TYPE" => "datetime"), "PRICE_TYPE" => array("FIELD" => "CP.PRICE_TYPE", "TYPE" => "char"), "RECUR_SCHEME_TYPE" => array("FIELD" => "CP.RECUR_SCHEME_TYPE", "TYPE" => "char"), "RECUR_SCHEME_LENGTH" => array("FIELD" => "CP.RECUR_SCHEME_LENGTH", "TYPE" => "int"), "TRIAL_PRICE_ID" => array("FIELD" => "CP.TRIAL_PRICE_ID", "TYPE" => "int"), "WITHOUT_ORDER" => array("FIELD" => "CP.WITHOUT_ORDER", "TYPE" => "char"), "SELECT_BEST_PRICE" => array("FIELD" => "CP.SELECT_BEST_PRICE", "TYPE" => "char"), "VAT_ID" => array("FIELD" => "CP.VAT_ID", "TYPE" => "int"), "VAT_INCLUDED" => array("FIELD" => "CP.VAT_INCLUDED", "TYPE" => "char"), "TMP_ID" => array("FIELD" => "CP.TMP_ID", "TYPE" => "char"), "PURCHASING_PRICE" => array("FIELD" => "CP.PURCHASING_PRICE", "TYPE" => "double"), "PURCHASING_CURRENCY" => array("FIELD" => "CP.PURCHASING_CURRENCY", "TYPE" => "string"), "BARCODE_MULTI" => array("FIELD" => "CP.BARCODE_MULTI", "TYPE" => "char"), "ELEMENT_IBLOCK_ID" => array("FIELD" => "I.IBLOCK_ID", "TYPE" => "int", "FROM" => "INNER JOIN b_iblock_element I ON (CP.ID = I.ID)"), "ELEMENT_XML_ID" => array("FIELD" => "I.XML_ID", "TYPE" => "string", "FROM" => "INNER JOIN b_iblock_element I ON (CP.ID = I.ID)"), "ELEMENT_NAME" => array("FIELD" => "I.NAME", "TYPE" => "string", "FROM" => "INNER JOIN b_iblock_element I ON (CP.ID = I.ID)") ); $arSqls = CCatalog::PrepareSql($arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields); $arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "", $arSqls["SELECT"]); if (empty($arGroupBy) && is_array($arGroupBy)) { $strSql = "SELECT ".$arSqls["SELECT"]." FROM b_catalog_product CP ".$arSqls["FROM"]; if (!empty($arSqls["WHERE"])) $strSql .= " WHERE ".$arSqls["WHERE"]; if (!empty($arSqls["GROUPBY"])) $strSql .= " GROUP BY ".$arSqls["GROUPBY"]; $dbRes = $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__); if ($arRes = $dbRes->Fetch()) return $arRes["CNT"]; else return false; } $strSql = "SELECT ".$arSqls["SELECT"]." FROM b_catalog_product CP ".$arSqls["FROM"]; if (!empty($arSqls["WHERE"])) $strSql .= " WHERE ".$arSqls["WHERE"]; if (!empty($arSqls["GROUPBY"])) $strSql .= " GROUP BY ".$arSqls["GROUPBY"]; if (!empty($arSqls["ORDERBY"])) $strSql .= " ORDER BY ".$arSqls["ORDERBY"]; $intTopCount = 0; $boolNavStartParams = (!empty($arNavStartParams) && is_array($arNavStartParams)); if ($boolNavStartParams && array_key_exists('nTopCount', $arNavStartParams)) { $intTopCount = intval($arNavStartParams["nTopCount"]); } if ($boolNavStartParams && 0 >= $intTopCount) { $strSql_tmp = "SELECT COUNT('x') as CNT FROM b_catalog_product CP ".$arSqls["FROM"]; if (!empty($arSqls["WHERE"])) $strSql_tmp .= " WHERE ".$arSqls["WHERE"]; if (!empty($arSqls["GROUPBY"])) $strSql_tmp .= " GROUP BY ".$arSqls["GROUPBY"]; $dbRes = $DB->Query($strSql_tmp, false, "File: ".__FILE__."<br>Line: ".__LINE__); $cnt = 0; if (empty($arSqls["GROUPBY"])) { if ($arRes = $dbRes->Fetch()) $cnt = $arRes["CNT"]; } else { $cnt = $dbRes->SelectedRowsCount(); } $dbRes = new CDBResult(); $dbRes->NavQuery($strSql, $cnt, $arNavStartParams); } else { if ($boolNavStartParams && 0 < $intTopCount) { $strSql .= " LIMIT ".$intTopCount; } $dbRes = $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__); } return $dbRes; }
function GetList($arOrder = array("ID" => "DESC"), $arFilter = array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array()) { global $DB; if (count($arSelectFields) <= 0) { $arSelectFields = array("ID", "BLOG_ID", "NAME"); } // FIELDS --> $arFields = array("ID" => array("FIELD" => "C.ID", "TYPE" => "int"), "BLOG_ID" => array("FIELD" => "C.BLOG_ID", "TYPE" => "int"), "NAME" => array("FIELD" => "C.NAME", "TYPE" => "string")); // <-- FIELDS $arSqls = CBlog::PrepareSql($arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields); $arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "", $arSqls["SELECT"]); if (is_array($arGroupBy) && count($arGroupBy) == 0) { $strSql = "SELECT " . $arSqls["SELECT"] . " " . "FROM b_blog_category C " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } //echo "!1!=".htmlspecialchars($strSql)."<br>"; $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); if ($arRes = $dbRes->Fetch()) { return $arRes["CNT"]; } else { return False; } } $strSql = "SELECT " . $arSqls["SELECT"] . " " . "FROM b_blog_category C " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } if (strlen($arSqls["ORDERBY"]) > 0) { $strSql .= "ORDER BY " . $arSqls["ORDERBY"] . " "; } if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) <= 0) { $strSql_tmp = "SELECT COUNT('x') as CNT " . "FROM b_blog_category C " . "\t" . $arSqls["FROM"] . " "; if (strlen($arSqls["WHERE"]) > 0) { $strSql_tmp .= "WHERE " . $arSqls["WHERE"] . " "; } if (strlen($arSqls["GROUPBY"]) > 0) { $strSql_tmp .= "GROUP BY " . $arSqls["GROUPBY"] . " "; } //echo "!2.1!=".htmlspecialchars($strSql_tmp)."<br>"; $dbRes = $DB->Query($strSql_tmp, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); $cnt = 0; if ($arRes = $dbRes->Fetch()) { $cnt = $arRes["CNT"]; } $dbRes = new CDBResult(); //echo "!2.2!=".htmlspecialchars($strSql)."<br>"; $dbRes->NavQuery($strSql, $cnt, $arNavStartParams); } else { if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) > 0) { $strSql = "SELECT * FROM (" . $strSql . ") WHERE ROWNUM<=" . $arNavStartParams["nTopCount"]; } //echo "!3!=".htmlspecialchars($strSql)."<br>"; $dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); } return $dbRes; }