Контроль целостности торгового каталога Битрикс

bitrix_catalog_integrity_check.sql

-- Контроль целостности торгового каталога Битрикса: найти отсутствующие сущности "продукт"

SELECT E.IBLOCK_ID AS IblockId,
       E.ID AS ElementId,
       E.NAME AS ElementName,
       E.ACTIVE AS ElementActive,
       CP.ID,
       CP.TYPE
FROM b_catalog_iblock AS CI
INNER JOIN b_iblock_element AS E ON E.IBLOCK_ID = CI.IBLOCK_ID
LEFT JOIN b_catalog_product AS CP ON CP.ID = E.ID
WHERE CP.ID IS NULL;

-- Контроль целостности торгового каталога Битрикса: найти отсутствующие сущности "цена"
-- TYPE: 1 — "простой", 4 — "предложение"

SELECT E.IBLOCK_ID AS IblockId,
       E.ID AS ElementId,
       E.NAME AS ElementName,
       E.ACTIVE AS ElementActive,
       CP.TYPE AS ProductType
FROM b_catalog_iblock AS CI
INNER JOIN b_iblock_element AS E ON E.IBLOCK_ID = CI.IBLOCK_ID
INNER JOIN b_catalog_product AS CP ON CP.ID = E.ID
LEFT JOIN b_catalog_price AS CPR ON CPR.PRODUCT_ID = E.ID
WHERE CPR.ID IS NULL
  AND CP.TYPE in(1, 4);