Developing reports on inventory balances in 1C-Bitrix

Developing Stock Level Reports for 1C-Bitrix

The task of "show what's running low in the warehouse" in 1C-Bitrix is solved differently depending on the accounting structure: single or multiple warehouses, 1C synchronization or standalone accounting, product offers (SKUs) or simple products. The standard 1C-Bitrix tools provide only basic filters in the admin panel. Operational work requires specialized reports.

Stock Storage Structure

Stock levels in 1C-Bitrix are stored in several tables depending on the accounting mode:

• b_catalog_product — the QUANTITY field (total stock), QUANTITY_RESERVED (reserved)
• b_catalog_store_product — per-warehouse stock (store_id, product_id, amount, quantity_reserved)

When working with product offers (SKUs): the base product (b_iblock_element of product type) has no stock — stock is set at the SKU level (b_catalog_product.product_id = sku_element_id).

Key Queries for Reports

Report: products with critical stock levels:

SELECT
    ie.id,
    ie.name,
    prop_art.value    AS article,
    sect.name         AS section,
    cp.quantity       AS stock,
    cp.quantity_reserved AS reserved,
    cp.quantity - cp.quantity_reserved AS available
FROM b_catalog_product cp
JOIN b_iblock_element ie ON ie.id = cp.id
LEFT JOIN b_iblock_element_property prop_art
    ON prop_art.iblock_element_id = ie.id
    AND prop_art.iblock_property_id = :article_prop_id
LEFT JOIN b_iblock_section sect ON sect.id = ie.iblock_section_id
WHERE ie.iblock_id = :iblock_id
  AND ie.active = 'Y'
  AND (cp.quantity - cp.quantity_reserved) <= :min_stock_threshold
ORDER BY (cp.quantity - cp.quantity_reserved) ASC;

Per-warehouse stock with detail (for multi-warehouse accounting):

SELECT
    ie.name             AS product_name,
    prop_art.value      AS article,
    cs.title            AS store_name,
    cs.address          AS store_address,
    csp.amount          AS store_amount,
    csp.quantity_reserved AS store_reserved
FROM b_catalog_store_product csp
JOIN b_catalog_store cs ON cs.id = csp.store_id AND cs.active = 'Y'
JOIN b_iblock_element ie ON ie.id = csp.product_id
LEFT JOIN b_iblock_element_property prop_art
    ON prop_art.iblock_element_id = ie.id
    AND prop_art.iblock_property_id = :article_prop_id
WHERE ie.iblock_id = :iblock_id
  AND csp.amount > 0
ORDER BY ie.name, cs.sort;

Report by SKU (product offers):

SELECT
    parent.name     AS product_name,
    sku.name        AS sku_name,
    prop_color.value AS color,
    prop_size.value  AS size,
    cp.quantity      AS stock
FROM b_iblock_element sku
JOIN b_iblock_element parent ON parent.id = sku.wf_parent_id -- for the legacy API
-- Or via b_catalog_product_offer for D7:
JOIN b_catalog_product_offer cpo ON cpo.id = sku.id
JOIN b_iblock_element parent ON parent.id = cpo.owner_id
JOIN b_catalog_product cp ON cp.id = sku.id
LEFT JOIN b_iblock_element_property prop_color
    ON prop_color.iblock_element_id = sku.id AND prop_color.iblock_property_id = :color_prop_id
LEFT JOIN b_iblock_element_property prop_size
    ON prop_size.iblock_element_id = sku.id AND prop_size.iblock_property_id = :size_prop_id
WHERE sku.iblock_id = :sku_iblock_id AND sku.active = 'Y'
ORDER BY parent.name, sku.name;

Case Study: Buyer Report for a Fashion E-Commerce Store

A clothing store with 3,000 SKUs (product × color × size), 2 warehouses (Moscow and St. Petersburg), and hourly 1C synchronization. The buyer checked stock levels in a spreadsheet every morning — the file was updated manually once a day.

Task: an automated "critical stock" report delivered by email at 8:00 AM.

Implementation:

1. SQL query against b_catalog_store_product + b_iblock_element_property (color, size)
2. XLSX generation via PhpSpreadsheet with conditional formatting: red for stock 0–1, yellow for 2–5
3. A 1C-Bitrix agent running once daily at 7:45 AM generates the file and saves it to /upload/reports/
4. Email delivery via \Bitrix\Main\Mail\Event::send() with attachment to the buyer and the director

// Report generation agent
function GenerateLowStockReport(): string
{
    $generator = new StockReportGenerator();
    $file = $generator->generateLowStock(threshold: 5);

    $savedPath = '/upload/reports/low_stock_' . date('Y-m-d') . '.xlsx';
    copy($file, $_SERVER['DOCUMENT_ROOT'] . $savedPath);

    \Bitrix\Main\Mail\Event::send([
        'EVENT_NAME' => 'LOW_STOCK_REPORT',
        'LID'        => 's1',
        'C_FIELDS'   => [
            'REPORT_DATE' => date('d.m.Y'),
            'FILE_PATH'   => $savedPath,
        ],
    ]);

    unlink($file);
    return __FUNCTION__ . '();';
}

The report reduced the buyer's data preparation time from 30 minutes to zero — the file is waiting in their inbox.

Interface Integration

For managers without /bitrix/admin/ access, the report is available through a page in a restricted section with filter controls (date range, warehouse, catalog section, minimum stock level) and an "Export to Excel" button.

Timeline