{"title":"ABC-\u0430\u043d\u0430\u043b\u0438\u0437 \u0442\u043e\u0432\u0430\u0440\u043e\u0432 \u043f\u043e \u0434\u043e\u043b\u0435 \u0432\u044b\u0440\u0443\u0447\u043a\u0438","query_pattern":"\/*\n * \u041e\u0442\u0447\u0451\u0442 \u0441\u0434\u0435\u043b\u0430\u043b \u041a\u0438\u0440\u0438\u043b\u043b\n *\n * {WHERE} \u043e\u0436\u0438\u0434\u0430\u0435\u0442\u0441\u044f \u043a\u0430\u043a:\n * 1. \u0414\u0430\u0442\u0430 \u043e\u0442\n * 2. \u0414\u0430\u0442\u0430 \u0434\u043e\n *\n * \u041e\u0442\u0447\u0451\u0442: ABC-\u0430\u043d\u0430\u043b\u0438\u0437 \u0442\u043e\u0432\u0430\u0440\u043e\u0432 \u043f\u043e \u0432\u044b\u0440\u0443\u0447\u043a\u0435.\n * \u0417\u0435\u0440\u043d\u043e: \u043e\u0434\u043d\u0430 \u0441\u0442\u0440\u043e\u043a\u0430 \u2014 \u043e\u0434\u0438\u043d \u0442\u043e\u0432\u0430\u0440.\n *\n * \u0412\u044b\u0440\u0443\u0447\u043a\u0430 \u2014 \u0441\u0442\u043e\u0438\u043c\u043e\u0441\u0442\u044c \u0442\u043e\u0432\u0430\u0440\u043e\u0432 \u0432 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u043d\u044b\u0445 \u0441\u0447\u0435\u0442\u0430\u0445 \u043f\u043e\u0441\u043b\u0435 \u043e\u0431\u0449\u0435\u0439 \u0441\u043a\u0438\u0434\u043a\u0438\n * \u0438\u043b\u0438 \u043d\u0430\u0446\u0435\u043d\u043a\u0438 \u043d\u0430 \u0441\u0447\u0451\u0442. \u042d\u0442\u043e \u043d\u0430\u0447\u0438\u0441\u043b\u0435\u043d\u043d\u0430\u044f \u043f\u0440\u043e\u0434\u0430\u0436\u0430, \u0430 \u043d\u0435 \u0444\u0430\u043a\u0442\u0438\u0447\u0435\u0441\u043a\u0438 \u043f\u043e\u043b\u0443\u0447\u0435\u043d\u043d\u0430\u044f\n * \u043e\u043f\u043b\u0430\u0442\u0430. \u041e\u0431\u0449\u0430\u044f \u0441\u043a\u0438\u0434\u043a\u0430\/\u043d\u0430\u0446\u0435\u043d\u043a\u0430 \u0440\u0430\u0441\u043f\u0440\u0435\u0434\u0435\u043b\u044f\u0435\u0442\u0441\u044f \u043f\u043e \u043f\u043e\u0437\u0438\u0446\u0438\u044f\u043c \u043f\u0440\u043e\u043f\u043e\u0440\u0446\u0438\u043e\u043d\u0430\u043b\u044c\u043d\u043e\n * \u0438\u0445 \u0441\u0442\u043e\u0438\u043c\u043e\u0441\u0442\u0438.\n *\/\nWITH raw_params AS (\n    SELECT\n        \"{WHERE}\" AS where_text,\n        LENGTH(\"{WHERE}\") - LENGTH(REPLACE(\"{WHERE}\", \"'\", \"\")) AS quote_count\n),\nparams AS (\n    SELECT\n        CASE\n            WHEN quote_count >= 2\n                THEN DATE(\n                    SUBSTRING_INDEX(\n                        SUBSTRING_INDEX(where_text, \"'\", 2),\n                        \"'\",\n                        -1\n                    )\n                )\n            ELSE DATE('1000-01-01')\n        END AS date_from,\n        CASE\n            WHEN quote_count >= 4\n                THEN DATE(\n                    SUBSTRING_INDEX(\n                        SUBSTRING_INDEX(where_text, \"'\", 4),\n                        \"'\",\n                        -1\n                    )\n                )\n            WHEN quote_count >= 2\n                THEN DATE(\n                    SUBSTRING_INDEX(\n                        SUBSTRING_INDEX(where_text, \"'\", 2),\n                        \"'\",\n                        -1\n                    )\n                )\n            ELSE DATE('9999-12-30')\n        END AS date_to\n    FROM raw_params\n),\nfiltered_invoices AS (\n    SELECT\n        i.id,\n        i.amount\n    FROM invoice AS i\n    CROSS JOIN params AS prm\n    WHERE i.status = 'exec'\n      AND i.invoice_date >= prm.date_from\n      AND i.invoice_date < DATE_ADD(prm.date_to, INTERVAL 1 DAY)\n),\ninvoice_totals AS (\n    SELECT\n        fi.id AS invoice_id,\n        fi.amount AS invoice_amount,\n        SUM(idoc.price) AS invoice_lines_amount\n    FROM filtered_invoices AS fi\n    INNER JOIN invoice_document AS idoc\n        ON idoc.document_id = fi.id\n    GROUP BY\n        fi.id,\n        fi.amount\n),\ngoods_revenue AS (\n    SELECT\n        g.id AS good_id,\n        g.title AS good_title,\n        SUM(idoc.quantity) AS sold_quantity,\n        SUM(\n            CASE\n                WHEN it.invoice_lines_amount <> 0\n                    THEN idoc.price\n                         * it.invoice_amount\n                         \/ it.invoice_lines_amount\n                ELSE 0\n            END\n        ) AS revenue\n    FROM invoice_totals AS it\n    INNER JOIN invoice_document AS idoc\n        ON idoc.document_id = it.invoice_id\n    INNER JOIN good AS g\n        ON g.id = idoc.good_id\n    INNER JOIN good_group AS gg\n        ON gg.id = g.group_id\n    WHERE gg.is_service = 0\n    GROUP BY\n        g.id,\n        g.title\n    HAVING ABS(revenue) >= 0.005\n),\nranked_goods AS (\n    SELECT\n        gr.good_id,\n        gr.good_title,\n        gr.sold_quantity,\n        gr.revenue,\n        SUM(gr.revenue) OVER () AS total_revenue,\n        SUM(gr.revenue) OVER (\n            ORDER BY\n                gr.revenue DESC,\n                gr.good_id ASC\n            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW\n        ) AS cumulative_revenue,\n        COALESCE(\n            SUM(gr.revenue) OVER (\n                ORDER BY\n                    gr.revenue DESC,\n                    gr.good_id ASC\n                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING\n            ),\n            0\n        ) AS previous_revenue\n    FROM goods_revenue AS gr\n),\nresults AS (\n    SELECT\n        rg.good_id AS sort_id,\n        rg.good_title,\n        ROUND(rg.sold_quantity, 3) AS sold_quantity,\n        ROUND(rg.revenue, 2) AS revenue,\n        ROUND(\n            CASE\n                WHEN rg.total_revenue <> 0\n                    THEN rg.revenue \/ rg.total_revenue * 100\n                ELSE 0\n            END,\n            2\n        ) AS revenue_share,\n        ROUND(\n            CASE\n                WHEN rg.total_revenue <> 0\n                    THEN rg.cumulative_revenue \/ rg.total_revenue * 100\n                ELSE 0\n            END,\n            2\n        ) AS cumulative_share,\n        CASE\n            WHEN rg.total_revenue <= 0 THEN '\u041d\u0435 \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u0430'\n            WHEN rg.previous_revenue \/ rg.total_revenue < 0.80 THEN 'A'\n            WHEN rg.previous_revenue \/ rg.total_revenue < 0.95 THEN 'B'\n            ELSE 'C'\n        END AS abc_group\n    FROM ranked_goods AS rg\n)\nSELECT\n    {FIELDS}\nFROM results AS r\nORDER BY\n    r.revenue DESC,\n    r.sort_id ASC","fields":[{"field_title":"\u0424\u0430\u043c\u0438\u043b\u0438\u044f ","table_title":"\u041a\u043b\u0438\u0435\u043d\u0442\u044b","module_name":"clients","grouping_fn_title":null,"is_grouping":0,"grouping_fn_id":0,"sort_order":0,"visible":1,"title":"\u0422\u043e\u0432\u0430\u0440","formula":"good_title","old_field_id":18},{"field_title":"\u0424\u0430\u043c\u0438\u043b\u0438\u044f ","table_title":"\u041a\u043b\u0438\u0435\u043d\u0442\u044b","module_name":"clients","grouping_fn_title":null,"is_grouping":0,"grouping_fn_id":0,"sort_order":0,"visible":1,"title":"\u041f\u0440\u043e\u0434\u0430\u043d\u043e","formula":"sold_quantity","old_field_id":18},{"field_title":"\u0424\u0430\u043c\u0438\u043b\u0438\u044f ","table_title":"\u041a\u043b\u0438\u0435\u043d\u0442\u044b","module_name":"clients","grouping_fn_title":null,"is_grouping":0,"grouping_fn_id":0,"sort_order":0,"visible":0,"title":"\u0412\u044b\u0440\u0443\u0447\u043a\u0430","formula":"revenue","old_field_id":18},{"field_title":"\u0421\u0443\u043c\u043c\u0430","table_title":"\u0421\u0447\u0435\u0442\u0430","module_name":"invoice","grouping_fn_title":null,"is_grouping":0,"grouping_fn_id":0,"sort_order":0,"visible":1,"title":"\u0412\u044b\u0440\u0443\u0447\u043a\u0430","formula":"revenue","old_field_id":96},{"field_title":"\u0424\u0430\u043c\u0438\u043b\u0438\u044f ","table_title":"\u041a\u043b\u0438\u0435\u043d\u0442\u044b","module_name":"clients","grouping_fn_title":null,"is_grouping":0,"grouping_fn_id":0,"sort_order":0,"visible":0,"title":"\u0414\u043e\u043b\u044f \u0432 \u0432\u044b\u0440\u0443\u0447\u043a\u0435, %","formula":"revenue_share","old_field_id":18},{"field_title":"\u0421\u0443\u043c\u043c\u0430","table_title":"\u0421\u0447\u0435\u0442\u0430","module_name":"invoice","grouping_fn_title":null,"is_grouping":0,"grouping_fn_id":0,"sort_order":0,"visible":1,"title":"\u0414\u043e\u043b\u044f \u0432 \u0432\u044b\u0440\u0443\u0447\u043a\u0435, %","formula":"revenue_share","old_field_id":96},{"field_title":"\u0424\u0430\u043c\u0438\u043b\u0438\u044f ","table_title":"\u041a\u043b\u0438\u0435\u043d\u0442\u044b","module_name":"clients","grouping_fn_title":null,"is_grouping":0,"grouping_fn_id":0,"sort_order":0,"visible":0,"title":"\u041d\u0430\u043a\u043e\u043f\u0438\u0442\u0435\u043b\u044c\u043d\u0430\u044f \u0434\u043e\u043b\u044f, %","formula":"cumulative_share","old_field_id":18},{"field_title":"\u0421\u0443\u043c\u043c\u0430","table_title":"\u0421\u0447\u0435\u0442\u0430","module_name":"invoice","grouping_fn_title":null,"is_grouping":0,"grouping_fn_id":0,"sort_order":0,"visible":1,"title":"\u041d\u0430\u043a\u043e\u043f\u0438\u0442\u0435\u043b\u044c\u043d\u0430\u044f \u0434\u043e\u043b\u044f, %","formula":"cumulative_share","old_field_id":96},{"field_title":"\u0424\u0430\u043c\u0438\u043b\u0438\u044f ","table_title":"\u041a\u043b\u0438\u0435\u043d\u0442\u044b","module_name":"clients","grouping_fn_title":null,"is_grouping":0,"grouping_fn_id":0,"sort_order":0,"visible":1,"title":"\u0413\u0440\u0443\u043f\u043f\u0430 ABC","formula":"abc_group","old_field_id":18}],"nesting":[],"filter":[{"pattern":"(1 and 2)","number":1,"field_title":"\u0414\u0430\u0442\u0430","table_title":"\u0421\u0447\u0435\u0442\u0430","module_name":"invoice","operator_name":"equal","condition":"","value":"2026-01-01T00:00:00","is_dynamic":1},{"pattern":"(1 and 2)","number":2,"field_title":"\u0414\u0430\u0442\u0430","table_title":"\u0421\u0447\u0435\u0442\u0430","module_name":"invoice","operator_name":"equal","condition":"and","value":"2026-03-31T00:00:00","is_dynamic":1}],"program_version":"0.536.2"}