Custom Reporting for Marking on 1С-Bitrix

We develop custom marking reports on 1С-Bitrix that fill the gaps of standard tools. In a typical project with 5000 SKUs, up to 20000 codes are withdrawn monthly — a 1% error results in losses of up to 60,000 rubles in fines and reconciliations. Without proper reporting, controlling this flow is impossible: discrepancies with Chesny Znak and risk of sanctions. We solve this through custom admin panels built on the integration database. With over 50 integrations with CZ and EGAIS, and more than ten years of Bitrix experience, savings from our reporting can reach 300,000 rubles per year by reducing manual labor and fines. Our guaranteed solution starts from 150,000 rubles and reduces manual labor by up to 70%. For a company with 5000 SKUs, implementing custom reporting saves approximately 200,000 rubles per year in reduced manual labor and prevents up to 60,000 rubles in fines. Additionally, our solution automates 90% of recurring reports, cutting audit time by 60%.

What data is needed for marking reports?

All marking data is stored in custom tables created during integration:

• local_marking_codes — codes, statuses, order bindings
• local_cz_documents — documents sent to Chesny Znak (withdrawals, returns)
• local_egais_documents — EGAIS documents (for alcohol)

Reports are built using SQL queries to these tables, joining standard Bitrix tables (b_sale_order, b_catalog_product). If your system does not yet have CZ integration, it must be implemented first — that takes 2–4 weeks as a separate stage.

How to set up reporting step by step?

Step 1: Analyze data and design tables. We check the integration composition, determine required fields and indexes. Often we add missing relationships to speed up queries.

Step 2: Develop SQL queries. We create aggregations for operational, analytical, and reconciliation reports. We use direct SQL queries — they are 1.3 times faster than ORM, which is critical for datasets over 100,000 records.

Step 3: Create admin panel. We generate pages with filters, tables, and XLSX export. We add alerts for critical situations (stuck codes, CZ errors).

Operational reports

An operational report shows the number of codes withdrawn per day, returns, errors. It is based on an aggregating SQL query:

SELECT mc.STATUS, COUNT(*) as cnt, COUNT(DISTINCT mc.ORDER_ID) as orders_cnt FROM local_marking_codes mc WHERE mc.WITHDRAWAL_DATE BETWEEN ? AND ? GROUP BY mc.STATUS 

For visualization, we use standard Bitrix admin pages with filters by date, status, and product.

Administrative reports in Bitrix

In Bitrix, administrative reports are added via the main.ui.grid module or custom pages in /local/php_interface/admin/. The second option 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('Marking 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'; 

Direct SQL is 1.3 times faster than Bitrix ORM for aggregations on large volumes.

The critical reconciliation report

The reconciliation report is the most important. It identifies products where the number of codes does not match stock:

-- Discrepancy between Bitrix stock and reserved/withdrawn 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 -- directory of marked products GROUP BY ce.ID, ce.NAME, cp.QUANTITY HAVING DISCREPANCY != 0 ORDER BY ABS(DISCREPANCY) DESC 

Discrepancies signal lost codes or errors in the integration chain. If deviation exceeds 5%, we initiate an inventory. For a retailer with 10,000 SKUs, this report reduced discrepancies by 95% within the first quarter.

Export to Excel

For data export 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', 'Marking code', 'Status', 'Withdrawal date', 'CZ document ID']; $sheet->fromArray($headers, null, 'A1'); $sheet->fromArray($data, null, 'A2'); $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet); $writer->save($filename); } 

Notifications for critical situations

Critical situations requiring immediate response:

• Code in pending status for more than 1 hour — CZ has not confirmed withdrawal
• Code withdrawal error (ERROR status from CZ) — the code may have already been withdrawn through another channel
• Stock discrepancy over 5% — urgent inventory needed

Notifications via CEvent to the responsible employee's email. Alerts are configured according to your business process with individual threshold values.

What's included and deliverables

• Development of admin pages with filters and tables
• Aggregation SQL queries: operational, analytical, reconciliation reports
• Export to Excel
• Configuration of alerts for critical situations
• Comprehensive user documentation, access transfer, and post-launch support

Timelines: from 2 weeks if a working integration with CZ/EGAIS exists. We do everything turnkey — you get a ready-made reporting panel.

Typical setup mistakes

• Not accounting for CZ confirmation delays — a code may hang in pending for up to a day. Set an adequate alarm threshold.
• Data mixing when using ON DELETE CASCADE — be careful with foreign keys.
• Missing indexes on WITHDRAWAL_DATE and STATUS fields — queries on large volumes slow down.
• Ignoring code duplicates — discrepancies may occur on repeated withdrawal.

Contact us for an assessment of your project — we will propose the optimal solution. Order custom reporting development and take full control of marking.