API tools faq
paste
Advertisement
Gistrec

Битрикс? Может его просто сжечь накуй?

Gistrec
Oct 5th, 2018
323
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 10.01 KB | None | 0 0
raw download clone embed print report
  1. # Стандартный SQL запрос битрикса
  2. # Выполняется около 0.2 сек
  3.  
  4. SELECT DISTINCT BE.ID AS ID,
  5.                 DATE_FORMAT(DATE_ADD(BE.TIMESTAMP_X, INTERVAL 18000 SECOND), '%d.%m.%Y %H:%i:%s') AS TIMESTAMP_X,
  6.                 UNIX_TIMESTAMP(BE.TIMESTAMP_X) AS TIMESTAMP_X_UNIX,
  7.                 BE.MODIFIED_BY AS MODIFIED_BY,
  8.                 DATE_FORMAT(DATE_ADD(BE.DATE_CREATE, INTERVAL 18000 SECOND), '%d.%m.%Y %H:%i:%s') AS DATE_CREATE,
  9.                 UNIX_TIMESTAMP(BE.DATE_CREATE) AS DATE_CREATE_UNIX,
  10.                 BE.CREATED_BY AS CREATED_BY,
  11.                 BE.IBLOCK_ID AS IBLOCK_ID,
  12.                 BE.IBLOCK_SECTION_ID AS IBLOCK_SECTION_ID,
  13.                 BE.ACTIVE AS ACTIVE,
  14.                 IF(EXTRACT(HOUR_SECOND
  15.                            FROM BE.ACTIVE_FROM)>0, DATE_FORMAT(DATE_ADD(BE.ACTIVE_FROM, INTERVAL 18000 SECOND), '%d.%m.%Y %H:%i:%s'), DATE_FORMAT(BE.ACTIVE_FROM, '%d.%m.%Y')) AS ACTIVE_FROM,
  16.                 IF(EXTRACT(HOUR_SECOND
  17.                            FROM BE.ACTIVE_TO)>0, DATE_FORMAT(DATE_ADD(BE.ACTIVE_TO, INTERVAL 18000 SECOND), '%d.%m.%Y %H:%i:%s'), DATE_FORMAT(BE.ACTIVE_TO, '%d.%m.%Y')) AS ACTIVE_TO,
  18.                 IF(EXTRACT(HOUR_SECOND
  19.                            FROM BE.ACTIVE_FROM)>0, DATE_FORMAT(DATE_ADD(BE.ACTIVE_FROM, INTERVAL 18000 SECOND), '%d.%m.%Y %H:%i:%s'), DATE_FORMAT(BE.ACTIVE_FROM, '%d.%m.%Y')) AS DATE_ACTIVE_FROM,
  20.                 IF(EXTRACT(HOUR_SECOND
  21.                            FROM BE.ACTIVE_TO)>0, DATE_FORMAT(DATE_ADD(BE.ACTIVE_TO, INTERVAL 18000 SECOND), '%d.%m.%Y %H:%i:%s'), DATE_FORMAT(BE.ACTIVE_TO, '%d.%m.%Y')) AS DATE_ACTIVE_TO,
  22.                 BE.SORT AS
  23. SORT,
  24.                 BE.NAME AS NAME,
  25.                 BE.PREVIEW_PICTURE AS PREVIEW_PICTURE,
  26.                 BE.PREVIEW_TEXT AS PREVIEW_TEXT,
  27.                 BE.PREVIEW_TEXT_TYPE AS PREVIEW_TEXT_TYPE,
  28.                 BE.DETAIL_PICTURE AS DETAIL_PICTURE,
  29.                 BE.DETAIL_TEXT AS DETAIL_TEXT,
  30.                 BE.DETAIL_TEXT_TYPE AS DETAIL_TEXT_TYPE,
  31.                 BE.SEARCHABLE_CONTENT AS SEARCHABLE_CONTENT,
  32.                 BE.WF_STATUS_ID AS WF_STATUS_ID,
  33.                 BE.WF_PARENT_ELEMENT_ID AS WF_PARENT_ELEMENT_ID,
  34.                 BE.WF_LAST_HISTORY_ID AS WF_LAST_HISTORY_ID,
  35.                 BE.WF_NEW AS WF_NEW,
  36.                 IF (BE.WF_DATE_LOCK IS NULL,
  37.                                        'green',
  38.                                        IF(DATE_ADD(BE.WF_DATE_LOCK, INTERVAL 60 MINUTE)<now(), 'green', IF(BE.WF_LOCKED_BY=3, 'yellow', 'red'))) AS LOCK_STATUS,
  39.                    BE.WF_LOCKED_BY AS WF_LOCKED_BY,
  40.                    DATE_FORMAT(DATE_ADD(BE.WF_DATE_LOCK, INTERVAL 18000 SECOND), '%d.%m.%Y %H:%i:%s') AS WF_DATE_LOCK,
  41.                    BE.WF_COMMENTS AS WF_COMMENTS,
  42.                    BE.IN_SECTIONS AS IN_SECTIONS,
  43.                    BE.SHOW_COUNTER AS SHOW_COUNTER,
  44.                    DATE_FORMAT(DATE_ADD(BE.SHOW_COUNTER_START, INTERVAL 18000 SECOND), '%d.%m.%Y %H:%i:%s') AS SHOW_COUNTER_START,
  45.                    BE.CODE AS CODE,
  46.                    BE.TAGS AS TAGS,
  47.                    BE.XML_ID AS XML_ID,
  48.                    BE.XML_ID AS EXTERNAL_ID,
  49.                    BE.TMP_ID AS TMP_ID,
  50.                    concat('(', U.LOGIN, ') ', ifnull(U.NAME, ''), ' ', ifnull(U.LAST_NAME, '')) AS USER_NAME,
  51.                    concat('(', UL.LOGIN, ') ', ifnull(UL.NAME, ''), ' ', ifnull(UL.LAST_NAME, '')) AS LOCKED_USER_NAME,
  52.                    concat('(', UC.LOGIN, ') ', ifnull(UC.NAME, ''), ' ', ifnull(UC.LAST_NAME, '')) AS CREATED_USER_NAME,
  53.                    L.DIR AS LANG_DIR,
  54.                    B.LID AS LID,
  55.                    B.IBLOCK_TYPE_ID AS IBLOCK_TYPE_ID,
  56.                    B.CODE AS IBLOCK_CODE,
  57.                    B.NAME AS IBLOCK_NAME,
  58.                    B.XML_ID AS IBLOCK_EXTERNAL_ID,
  59.                    B.DETAIL_PAGE_URL AS DETAIL_PAGE_URL,
  60.                    B.LIST_PAGE_URL AS LIST_PAGE_URL,
  61.                    B.CANONICAL_PAGE_URL AS CANONICAL_PAGE_URL,
  62.                    DATE_FORMAT(BE.DATE_CREATE, '%Y.%m.%d') AS CREATED_DATE,
  63.                    IF(BE.WF_STATUS_ID = 1, 'Y', 'N') AS BP_PUBLISHED,
  64.                    CAT_P1.ID AS CATALOG_PRICE_ID_1,
  65.                    CAT_P1.CATALOG_GROUP_ID AS CATALOG_GROUP_ID_1,
  66.                    CAT_P1.PRICE AS CATALOG_PRICE_1,
  67.                    CAT_P1.CURRENCY AS CATALOG_CURRENCY_1,
  68.                    CAT_P1.QUANTITY_FROM AS CATALOG_QUANTITY_FROM_1,
  69.                    CAT_P1.QUANTITY_TO AS CATALOG_QUANTITY_TO_1,
  70.                    'Цена' AS CATALOG_GROUP_NAME_1,
  71.                    'Y' AS CATALOG_CAN_ACCESS_1,
  72.                    'Y' AS CATALOG_CAN_BUY_1,
  73.                    CAT_P1.EXTRA_ID AS CATALOG_EXTRA_ID_1,
  74.                    CAT_PR.QUANTITY AS CATALOG_QUANTITY,
  75.                    CAT_PR.QUANTITY_RESERVED AS CATALOG_QUANTITY_RESERVED,
  76.                    IF (CAT_PR.QUANTITY_TRACE = 'D',
  77.                        'Y',
  78.                        CAT_PR.QUANTITY_TRACE) AS CATALOG_QUANTITY_TRACE,
  79.                       CAT_PR.QUANTITY_TRACE AS CATALOG_QUANTITY_TRACE_ORIG,
  80.                       IF (CAT_PR.CAN_BUY_ZERO = 'D',
  81.                           'N',
  82.                           CAT_PR.CAN_BUY_ZERO) AS CATALOG_CAN_BUY_ZERO,
  83.                          CAT_PR.CAN_BUY_ZERO AS CATALOG_CAN_BUY_ZERO_ORIG,
  84.                          IF (CAT_PR.NEGATIVE_AMOUNT_TRACE = 'D',
  85.                              'Y',
  86.                              CAT_PR.NEGATIVE_AMOUNT_TRACE) AS CATALOG_NEGATIVE_AMOUNT_TRACE,
  87.                             CAT_PR.NEGATIVE_AMOUNT_TRACE AS CATALOG_NEGATIVE_AMOUNT_ORIG,
  88.                             IF (CAT_PR.SUBSCRIBE = 'D',
  89.                                 'Y',
  90.                                 CAT_PR.SUBSCRIBE) AS CATALOG_SUBSCRIBE,
  91.                                CAT_PR.SUBSCRIBE AS CATALOG_SUBSCRIBE_ORIG,
  92.                                IF (CAT_PR.QUANTITY > 0
  93.                                    OR IF (CAT_PR.QUANTITY_TRACE = 'D',
  94.                                           'Y',
  95.                                           CAT_PR.QUANTITY_TRACE) = 'N'
  96.                                    OR IF (CAT_PR.CAN_BUY_ZERO = 'D',
  97.                                           'N',
  98.                                           CAT_PR.CAN_BUY_ZERO) = 'Y',
  99.                                          'Y',
  100.                                          'N') AS CATALOG_AVAILABLE,
  101.                                   CAT_PR.WEIGHT AS CATALOG_WEIGHT,
  102.                                   CAT_PR.WIDTH AS CATALOG_WIDTH,
  103.                                   CAT_PR.LENGTH AS CATALOG_LENGTH,
  104.                                   CAT_PR.HEIGHT AS CATALOG_HEIGHT,
  105.                                   CAT_PR.MEASURE AS CATALOG_MEASURE,
  106.                                   CAT_VAT.RATE AS CATALOG_VAT,
  107.                                   CAT_PR.VAT_INCLUDED AS CATALOG_VAT_INCLUDED,
  108.                                   CAT_PR.PRICE_TYPE AS CATALOG_PRICE_TYPE,
  109.                                   CAT_PR.RECUR_SCHEME_TYPE AS CATALOG_RECUR_SCHEME_TYPE,
  110.                                   CAT_PR.RECUR_SCHEME_LENGTH AS CATALOG_RECUR_SCHEME_LENGTH,
  111.                                   CAT_PR.TRIAL_PRICE_ID AS CATALOG_TRIAL_PRICE_ID,
  112.                                   CAT_PR.WITHOUT_ORDER AS CATALOG_WITHOUT_ORDER,
  113.                                   CAT_PR.SELECT_BEST_PRICE AS CATALOG_SELECT_BEST_PRICE,
  114.                                   CAT_PR.PURCHASING_PRICE AS CATALOG_PURCHASING_PRICE,
  115.                                   CAT_PR.PURCHASING_CURRENCY AS CATALOG_PURCHASING_CURRENCY,
  116.                                   CAT_PR.TYPE AS CATALOG_TYPE
  117. FROM b_iblock B
  118. INNER JOIN b_lang L ON B.LID=L.LID
  119. INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
  120. INNER JOIN b_iblock_property FP0 ON FP0.IBLOCK_ID = B.ID
  121. AND FP0.CODE='YEAR'
  122. INNER JOIN b_iblock_property FP1 ON FP1.IBLOCK_ID = B.ID
  123. AND FP1.CODE='MODEL'
  124. INNER JOIN b_iblock_element_prop_m2 FPV0 ON FPV0.IBLOCK_PROPERTY_ID = FP0.ID
  125. AND FPV0.IBLOCK_ELEMENT_ID = BE.ID
  126. INNER JOIN b_iblock_element_prop_m2 FPV1 ON FPV1.IBLOCK_PROPERTY_ID = FP1.ID
  127. AND FPV1.IBLOCK_ELEMENT_ID = BE.ID
  128. INNER JOIN b_iblock_2_index FC ON FC.ELEMENT_ID = BE.ID
  129. LEFT JOIN b_catalog_price CAT_P1 ON (CAT_P1.PRODUCT_ID = BE.ID
  130.                                      AND CAT_P1.CATALOG_GROUP_ID = 1)
  131. AND ((CAT_P1.QUANTITY_FROM <= 1
  132.       OR CAT_P1.QUANTITY_FROM IS NULL)
  133.      AND (CAT_P1.QUANTITY_TO >= 1
  134.           OR CAT_P1.QUANTITY_TO IS NULL))
  135. LEFT JOIN b_catalog_product CAT_PR ON (CAT_PR.ID = BE.ID)
  136. LEFT JOIN b_catalog_iblock CAT_IB ON ((CAT_PR.VAT_ID IS NULL
  137.                                        OR CAT_PR.VAT_ID = 0)
  138.                                       AND CAT_IB.IBLOCK_ID = BE.IBLOCK_ID)
  139. LEFT JOIN b_catalog_vat CAT_VAT ON (CAT_VAT.ID = IF((CAT_PR.VAT_ID IS NULL
  140.                                                      OR CAT_PR.VAT_ID = 0), CAT_IB.VAT_ID, CAT_PR.VAT_ID))
  141. LEFT JOIN b_user U ON U.ID=BE.MODIFIED_BY
  142. LEFT JOIN b_user UL ON UL.ID=BE.WF_LOCKED_BY
  143. LEFT JOIN b_user UC ON UC.ID=BE.CREATED_BY
  144. WHERE 1=1
  145.   AND ((FC.SECTION_ID = 8
  146.         AND FC.FACET_ID = 1
  147.         AND FC.VALUE_NUM = 0
  148.         AND FC.VALUE IN (0))
  149.        AND ((((BE.IBLOCK_ID = '2'))))
  150.        AND (EXISTS
  151.               (SELECT IBLOCK_ID
  152.                FROM b_iblock_site
  153.                WHERE IBLOCK_ID = B.ID
  154.                  AND (((SITE_ID='s1'))) ))
  155.        AND ((((B.ACTIVE='Y'))))
  156.        AND (((BE.ACTIVE_TO >= now()
  157.               OR BE.ACTIVE_TO IS NULL)
  158.              AND (BE.ACTIVE_FROM <= now()
  159.                   OR BE.ACTIVE_FROM IS NULL)))
  160.        AND ((((BE.ACTIVE='Y'))))
  161.        AND ((IF (CAT_PR.QUANTITY > 0
  162.                  OR IF (CAT_PR.QUANTITY_TRACE = 'D',
  163.                         'Y',
  164.                         CAT_PR.QUANTITY_TRACE) = 'N'
  165.                  OR IF (CAT_PR.CAN_BUY_ZERO = 'D',
  166.                         'N',
  167.                         CAT_PR.CAN_BUY_ZERO) = 'Y',
  168.                        'Y',
  169.                        'N') = 'Y'))
  170.        AND (1=1)
  171.        AND ((((FPV0.VALUE LIKE '1991'))))
  172.        AND ((((FPV1.VALUE_NUM = '695')))))
  173.   AND (((BE.WF_STATUS_ID=1
  174.          AND BE.WF_PARENT_ELEMENT_ID IS NULL)))
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!