Setting up labeling reporting in 1C-Bitrix

Setting Up Labeling Reporting in 1C-Bitrix

Mandatory labeling creates not only an operational burden (retiring codes, processing returns) but also a need for reporting: how many codes were retired from circulation over a given period, what is the status of each document in Honest Sign, are there discrepancies between 1C-Bitrix data and Honest Sign. The standard 1C-Bitrix toolkit does not include labeling reports — they must be developed separately on top of the data accumulated during the integration with Honest Sign / EGAIS.

Data sources for reports

All labeling data is stored in custom tables created during the integration:

• local_marking_codes — labeling codes, their statuses, association with orders
• local_cz_documents — documents submitted to Honest Sign (retirements, returns)
• local_egais_documents — EGAIS documents (for alcohol)

Reports are built with SQL queries against these tables, joined to standard 1C-Bitrix tables (b_sale_order, b_catalog_iblock_element).

Implementing admin reports

In 1C-Bitrix, admin reports are added via the main.ui.grid module or custom pages under /local/php_interface/admin/. The latter gives full control over filtering and output.

// /local/php_interface/admin/marking_report.php
require_once $_SERVER['DOCUMENT_ROOT'] . '/bitrix/modules/main/include/prolog_admin_before.php';

$APPLICATION->SetTitle('Labeling Report');

$filter = [];
$dateFrom = $_REQUEST['date_from'] ?? date('Y-m-01');
$dateTo   = $_REQUEST['date_to']   ?? date('Y-m-d');

if ($dateFrom && $dateTo) {
    $filter['>=WITHDRAWAL_DATE'] = $dateFrom . ' 00:00:00';
    $filter['<=WITHDRAWAL_DATE'] = $dateTo   . ' 23:59:59';
}

// Aggregation by status
$stats = \Bitrix\Main\Application::getInstance()
    ->getConnection()
    ->query("
        SELECT
            mc.STATUS,
            COUNT(*) as cnt,
            COUNT(DISTINCT mc.ORDER_ID) as orders_cnt,
            COUNT(DISTINCT mc.PRODUCT_ID) as products_cnt
        FROM local_marking_codes mc
        WHERE mc.WITHDRAWAL_DATE BETWEEN ? AND ?
        GROUP BY mc.STATUS
    ", [$dateFrom . ' 00:00:00', $dateTo . ' 23:59:59'])
    ->fetchAll();

require $_SERVER['DOCUMENT_ROOT'] . '/bitrix/modules/main/include/prolog_admin_after.php';

Key reporting metrics

Operational report (daily):

• Number of codes retired from circulation during the day
• Number of codes returned to circulation
• Retirement errors (codes with an error response from Honest Sign)
• Documents in pending status (not processed by Honest Sign within 30 minutes)

Analytical report (weekly/monthly):

• Retirement dynamics by product category
• Return rate broken down by SKU
• Honest Sign document processing time (from submission to confirmation)

Reconciliation report:

-- Discrepancy between 1C-Bitrix stock and reserved/retired codes
SELECT
    ce.ID as PRODUCT_ID,
    ce.NAME as PRODUCT_NAME,
    cp.QUANTITY as STOCK_QUANTITY,
    COUNT(CASE WHEN mc.STATUS = 'in_stock' THEN 1 END) as CODES_AVAILABLE,
    cp.QUANTITY - COUNT(CASE WHEN mc.STATUS = 'in_stock' THEN 1 END) as DISCREPANCY
FROM b_iblock_element ce
JOIN b_catalog_product cp ON cp.ID = ce.ID
LEFT JOIN local_marking_codes mc ON mc.PRODUCT_ID = ce.ID
WHERE ce.IBLOCK_ID = 5  -- labeled products catalog
GROUP BY ce.ID, ce.NAME, cp.QUANTITY
HAVING DISCREPANCY != 0
ORDER BY ABS(DISCREPANCY) DESC

Discrepancies indicate lost codes or errors in the integration chain.

Excel export

For providing data to auditors or regulators — export via PHPSpreadsheet:

public function exportToXlsx(array $data, string $filename): void
{
    $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();

    $headers = ['Order', 'Product', 'Labeling Code', 'Status', 'Retirement Date', 'Honest Sign Document ID'];
    $sheet->fromArray($headers, null, 'A1');
    $sheet->fromArray($data, null, 'A2');

    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
    $writer->save($filename);
}

Notifications and alerts

Critical situations requiring immediate action:

• Code in pending status for more than 1 hour — Honest Sign has not confirmed the retirement
• Code retirement error (ERROR status from Honest Sign) — the code may have already been retired through another channel
• Stock discrepancy exceeding 5% — trigger a stock count

Notifications via CEventLog::Add() + email via CEvent::Send() to the responsible staff member.

Scope of work

• Development of admin pages with filters and data tables
• Aggregation SQL queries: operational and reconciliation reports
• Excel export
• Alert configuration for critical situations
• User documentation

Timeline: 2–3 weeks given a working integration with Honest Sign / EGAIS.