{"title":"\u0412\u0437\u0430\u0438\u043c\u043e\u0440\u0430\u0441\u0447\u0435\u0442\u044b \u043f\u043e \u043f\u043e\u0441\u0442\u0430\u0432\u0449\u0438\u043a\u0430\u043c \u2014 \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0439","query_pattern":"\/*\n *\n * {WHERE} \u043e\u0436\u0438\u0434\u0430\u0435\u0442\u0441\u044f \u043a\u0430\u043a:\n * 1. \u0421\u0442\u0430\u0442\u0443\u0441 \u0434\u043e\u043a\u0443\u043c\u0435\u043d\u0442\u0430: exec,save (\u043f\u043e\u0441\u0442\u043e\u044f\u043d\u043d\u044b\u0439 IN).\n * 2. \u041f\u043e\u0441\u0442\u0430\u0432\u0449\u0438\u043a: \u043e\u0434\u0438\u043d \u0438\u043b\u0438 \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u043e ID (IN), \u043d\u0435\u043e\u0431\u044f\u0437\u0430\u0442\u0435\u043b\u044c\u043d\u044b\u0439.\n *    \u041f\u0440\u0438 \u043e\u0442\u0441\u0443\u0442\u0441\u0442\u0432\u0438\u0438 \u0444\u0438\u043b\u044c\u0442\u0440\u0430 \u2014 \u0432\u0441\u0435 \u043f\u043e\u0441\u0442\u0430\u0432\u0449\u0438\u043a\u0438.\n * 3. \u0414\u0430\u0442\u0430 \u043f\u0440\u0438\u0445\u043e\u0434\u043d\u043e\u0439 \u043d\u0430\u043a\u043b\u0430\u0434\u043d\u043e\u0439 \u043e\u0442 (>=).\n * 4. \u0414\u0430\u0442\u0430 \u043f\u0440\u0438\u0445\u043e\u0434\u043d\u043e\u0439 \u043d\u0430\u043a\u043b\u0430\u0434\u043d\u043e\u0439 \u0434\u043e (<=, \u0432\u043a\u043b\u044e\u0447\u0438\u0442\u0435\u043b\u044c\u043d\u043e).\n * 5. ID \u043a\u043b\u0438\u043d\u0438\u043a\u0438 (=).\n * \u0417\u043d\u0430\u0447\u0435\u043d\u0438\u044f \u0438\u0437\u0432\u043b\u0435\u043a\u0430\u044e\u0442\u0441\u044f \u043f\u043e \u0438\u043c\u0435\u043d\u0430\u043c \u043f\u043e\u043b\u0435\u0439; \u0434\u0430\u0442\u044b \u2014 \u043f\u043e \u0434\u0432\u0443\u043c \u043b\u0438\u0442\u0435\u0440\u0430\u043b\u0430\u043c ISO.\n * \u0414\u0430\u0442\u0430 \u043f\u0435\u0440\u0438\u043e\u0434\u0430 \u0438 \u0432\u0438\u0434\u0438\u043c\u0430\u044f \u0434\u0430\u0442\u0430: \u0440\u0443\u0447\u043d\u0430\u044f \u0434\u0430\u0442\u0430 \u043d\u0430\u043a\u043b\u0430\u0434\u043d\u043e\u0439, \u0438\u043d\u0430\u0447\u0435 \u0434\u0430\u0442\u0430 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f,\n * \u0438\u043d\u0430\u0447\u0435 \u0430\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0447\u0435\u0441\u043a\u0430\u044f \u0434\u0430\u0442\u0430 \u0441\u043e\u0437\u0434\u0430\u043d\u0438\u044f (\u0434\u043b\u044f \u0441\u043e\u0445\u0440\u0430\u043d\u0451\u043d\u043d\u044b\u0445 \u0431\u0435\u0437 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f).\n * \u041e\u0434\u043d\u0430 \u0441\u0442\u0440\u043e\u043a\u0430 \u2014 \u043e\u0434\u043d\u0430 \u043f\u0440\u0438\u0445\u043e\u0434\u043d\u0430\u044f \u043d\u0430\u043a\u043b\u0430\u0434\u043d\u0430\u044f \u0441 \u0430\u043a\u0442\u0443\u0430\u043b\u044c\u043d\u044b\u043c\u0438 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u043d\u044b\u043c\u0438 \u043e\u043f\u043b\u0430\u0442\u0430\u043c\u0438.\n *\/\nWITH raw_params AS (\n    SELECT \"{WHERE}\" AS where_text\n), extracted_params AS (\n    SELECT\n        REGEXP_SUBSTR(where_text, '[0-9]{4}-[0-9]{2}-[0-9]{2}', 1, 1) AS date_from_text,\n        REGEXP_SUBSTR(where_text, '[0-9]{4}-[0-9]{2}-[0-9]{2}', 1, 2) AS date_to_text,\n        REGEXP_SUBSTR(where_text, 'supplier_id`?[[:space:]]+IN[[:space:]]*[(][^)]*[)]', 1, 1, 'i') AS supplier_clause,\n        REGEXP_SUBSTR(where_text, 'document_status`?[[:space:]]+IN[[:space:]]*[(][^)]*[)]', 1, 1, 'i') AS status_clause,\n        REGEXP_SUBSTR(where_text, 'clinic_id`?[[:space:]]*=[[:space:]]*[^ )]+', 1, 1, 'i') AS clinic_clause\n    FROM raw_params\n), params AS (\n    SELECT\n        CAST(date_from_text AS DATE) AS date_from,\n        CAST(date_to_text AS DATE) AS date_to,\n        REGEXP_REPLACE(REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(supplier_clause, '(', -1), ')', 1), \"'\", ''), '`', ''), '[[:space:]]', '') AS supplier_ids,\n        REGEXP_REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(status_clause, '(', -1), ')', 1), \"'\", ''), '[[:space:]]', '') AS statuses,\n        CAST(REPLACE(TRIM(SUBSTRING_INDEX(clinic_clause, '=', -1)), \"'\", '') AS UNSIGNED) AS clinic_id\n    FROM extracted_params\n), dated_documents AS (\n    SELECT d.id, d.document_type, d.status, d.store_id, d.supplier_id,\n           d.prihod_date, d.prihod_number, d.exec_dt, d.total,\n           CASE\n               WHEN YEAR(d.prihod_date) > 0 THEN d.prihod_date\n               WHEN YEAR(d.exec_dt) > 0 THEN DATE(d.exec_dt)\n               WHEN YEAR(d.add_dt) > 0 THEN DATE(d.add_dt)\n               ELSE NULL\n           END AS report_date\n    FROM store_document AS d\n    WHERE d.document_type = 'prihod'\n), selected_documents AS (\n    SELECT d.id, d.status, d.store_id, d.supplier_id, d.prihod_date,\n           d.prihod_number, d.exec_dt, d.total, d.report_date, s.clinic_id, s.title AS store_name\n    FROM dated_documents AS d\n    JOIN stores AS s ON s.id = d.store_id\n    CROSS JOIN params AS p\n    WHERE d.document_type = 'prihod'\n      AND FIND_IN_SET(d.status, p.statuses) > 0\n      AND (p.supplier_ids IS NULL OR FIND_IN_SET(d.supplier_id, p.supplier_ids) > 0)\n      AND d.report_date >= p.date_from\n      AND d.report_date <= p.date_to\n      AND s.clinic_id = p.clinic_id\n), receipt_lines AS (\n    SELECT o.document_id,\n           MIN(o.party_account_id) AS party_id,\n           SUM(o.cost) AS line_total\n    FROM selected_documents AS d\n    JOIN store_document_operation_current AS o ON o.document_id = d.id AND o.document_type = 'prihod'\n    GROUP BY o.document_id\n), receipt_numbers AS (\n    SELECT n.document_id,\n           GROUP_CONCAT(DISTINCT n.document_number ORDER BY n.document_number SEPARATOR ', ') AS receipt_number\n    FROM selected_documents AS d\n    JOIN document_number AS n ON n.document_id = d.id AND n.document_type = 'prihod'\n        AND (n.clinic_id = d.clinic_id OR n.clinic_id = 0)\n    GROUP BY n.document_id\n), expense_numbers AS (\n    SELECT n.doc_id, GROUP_CONCAT(DISTINCT n.number ORDER BY n.number SEPARATOR ', ') AS expense_number\n    FROM selected_documents AS d\n    JOIN cassarashod AS c ON c.prihod_id = d.id AND c.status = 'exec'\n    JOIN cassa_doc_numbers AS n ON n.doc_id = c.id AND n.doc_type = 'cassarashod'\n    GROUP BY n.doc_id\n), payments AS (\n    SELECT c.prihod_id AS document_id,\n           GROUP_CONCAT(DISTINCT c.id ORDER BY c.id SEPARATOR ', ') AS expense_ids,\n           GROUP_CONCAT(DISTINCT n.expense_number ORDER BY n.expense_number SEPARATOR ', ') AS expense_numbers,\n           GROUP_CONCAT(DISTINCT c.date ORDER BY c.date SEPARATOR ', ') AS payment_dates,\n           SUM(IFNULL(c.summa, 0) + IFNULL(c.summa_cashless, 0)) AS paid_total\n    FROM selected_documents AS d\n    JOIN cassarashod AS c ON c.prihod_id = d.id AND c.status = 'exec'\n    LEFT JOIN expense_numbers AS n ON n.doc_id = c.id\n    GROUP BY c.prihod_id\n), amounts AS (\n    SELECT d.id, l.party_id, n.receipt_number, d.report_date, d.exec_dt,\n           sp.company_name AS supplier_name, d.store_name, d.status,\n           CASE WHEN d.total IS NULL OR ROUND(d.total, 2) = 0 THEN l.line_total ELSE d.total END AS receipt_total,\n           p.expense_ids, p.expense_numbers, p.payment_dates, IFNULL(p.paid_total, 0) AS paid_total\n    FROM selected_documents AS d\n    JOIN receipt_lines AS l ON l.document_id = d.id\n    LEFT JOIN suppliers AS sp ON sp.id = d.supplier_id\n    LEFT JOIN receipt_numbers AS n ON n.document_id = d.id\n    LEFT JOIN payments AS p ON p.document_id = d.id\n), results AS (\n    SELECT id AS document_id, party_id, receipt_number, report_date AS receipt_date,\n           exec_dt AS execution_date, supplier_name, store_name,\n           CASE status WHEN 'exec' THEN '\u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d' WHEN 'save' THEN '\u0441\u043e\u0445\u0440\u0430\u043d\u0435\u043d' ELSE status END AS document_status,\n           ROUND(receipt_total, 2) AS receipt_total, expense_ids, expense_numbers, payment_dates,\n           ROUND(paid_total, 2) AS paid_total,\n           ROUND(receipt_total - paid_total, 2) AS debt_total\n    FROM amounts\n)\nSELECT\n    {FIELDS}\nFROM results AS r\nORDER BY r.party_id, r.document_id","fields":[{"field_title":"\u041f\u0430\u0440\u0442\u0438\u044f","table_title":"\u041f\u0440\u0438\u0445\u043e\u0434","module_name":"stores","grouping_fn_title":null,"is_grouping":0,"grouping_fn_id":0,"sort_order":0,"visible":1,"title":"\u041f\u0430\u0440\u0442\u0438\u044f","formula":"r.party_id","old_field_id":552},{"field_title":"ID \u0417\u0430\u0433\u043e\u043b\u043e\u0432\u043a\u0430","table_title":"\u041f\u0440\u0438\u0445\u043e\u0434","module_name":"stores","grouping_fn_title":null,"is_grouping":0,"grouping_fn_id":0,"sort_order":0,"visible":0,"title":"ID \u0417\u0430\u0433\u043e\u043b\u043e\u0432\u043a\u0430","formula":"r.document_id","old_field_id":539},{"field_title":"ID \u0441\u0442\u0440\u043e\u043a\u0438","table_title":"\u041f\u0440\u0438\u0445\u043e\u0434","module_name":"stores","grouping_fn_title":null,"is_grouping":0,"grouping_fn_id":0,"sort_order":0,"visible":1,"title":"\u2116 \u043f\u0440\u0438\u0445\u043e\u0434\u0430","formula":"r.receipt_number","old_field_id":546},{"field_title":"\u0414\u0430\u0442\u0430 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f \u0434\u043e\u043a\u0443\u043c\u0435\u043d\u0442\u0430","table_title":"\u041f\u0440\u0438\u0445\u043e\u0434","module_name":"stores","grouping_fn_title":null,"is_grouping":0,"grouping_fn_id":0,"sort_order":0,"visible":0,"title":"\u0414\u0430\u0442\u0430 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f \u0434\u043e\u043a\u0443\u043c\u0435\u043d\u0442\u0430","formula":"r.execution_date","old_field_id":545},{"field_title":"\u0414\u0430\u0442\u0430 \u043f\u0440\u0438\u0445\u043e\u0434\u043d\u043e\u0439 \u043d\u0430\u043a\u043b\u0430\u0434\u043d\u043e\u0439","table_title":"\u041f\u0440\u0438\u0445\u043e\u0434","module_name":"stores","grouping_fn_title":null,"is_grouping":0,"grouping_fn_id":0,"sort_order":0,"visible":1,"title":"\u0414\u0430\u0442\u0430 \u043f\u0440\u0438\u0445\u043e\u0434\u043d\u043e\u0439 \u043d\u0430\u043a\u043b\u0430\u0434\u043d\u043e\u0439","formula":"DATE_FORMAT(r.receipt_date,'%d.%m.%Y')","old_field_id":756},{"field_title":"\u041f\u043e\u0441\u0442\u0430\u0432\u0449\u0438\u043a","table_title":"\u041f\u0440\u0438\u0445\u043e\u0434","module_name":"stores","grouping_fn_title":null,"is_grouping":0,"grouping_fn_id":0,"sort_order":0,"visible":1,"title":"\u041f\u043e\u0441\u0442\u0430\u0432\u0449\u0438\u043a","formula":"r.supplier_name","old_field_id":542},{"field_title":"\u0421\u043a\u043b\u0430\u0434","table_title":"\u041f\u0440\u0438\u0445\u043e\u0434","module_name":"stores","grouping_fn_title":null,"is_grouping":0,"grouping_fn_id":0,"sort_order":0,"visible":0,"title":"\u0421\u043a\u043b\u0430\u0434","formula":"r.store_name","old_field_id":541},{"field_title":"\u0421\u0442\u0430\u0442\u0443\u0441 \u0434\u043e\u043a\u0443\u043c\u0435\u043d\u0442\u0430","table_title":"\u041f\u0440\u0438\u0445\u043e\u0434","module_name":"stores","grouping_fn_title":null,"is_grouping":0,"grouping_fn_id":0,"sort_order":0,"visible":1,"title":"\u0421\u0442\u0430\u0442\u0443\u0441 \u0434\u043e\u043a\u0443\u043c\u0435\u043d\u0442\u0430","formula":"r.document_status","old_field_id":540},{"field_title":"\u0426\u0435\u043d\u0430","table_title":"\u041f\u0440\u0438\u0445\u043e\u0434","module_name":"stores","grouping_fn_title":null,"is_grouping":0,"grouping_fn_id":0,"sort_order":0,"visible":1,"title":"\u0421\u0443\u043c\u043c\u0430 \u043f\u0440\u0438\u0445\u043e\u0434\u0430","formula":"r.receipt_total","old_field_id":551},{"field_title":"ID \u0440\u0430\u0441\u0445\u043e\u0434\u0430","table_title":"\u0420\u0430\u0441\u0445\u043e\u0434","module_name":"cassa","grouping_fn_title":null,"is_grouping":0,"grouping_fn_id":0,"sort_order":0,"visible":0,"title":"ID \u0440\u0430\u0441\u0445\u043e\u0434\u0430","formula":"r.expense_ids","old_field_id":274},{"field_title":"\u041d\u043e\u043c\u0435\u0440 \u0434\u043e\u043a\u0443\u043c\u0435\u043d\u0442\u0430","table_title":"\u0420\u0430\u0441\u0445\u043e\u0434","module_name":"cassa","grouping_fn_title":null,"is_grouping":0,"grouping_fn_id":0,"sort_order":0,"visible":1,"title":"\u041d\u043e\u043c\u0435\u0440 \u0440\u0430\u0441\u0445\u043e\u0434\u0430","formula":"r.expense_numbers","old_field_id":903},{"field_title":"\u0414\u0430\u0442\u0430","table_title":"\u0420\u0430\u0441\u0445\u043e\u0434","module_name":"cassa","grouping_fn_title":null,"is_grouping":0,"grouping_fn_id":0,"sort_order":0,"visible":1,"title":"\u0414\u0430\u0442\u0430 \u043e\u043f\u043b\u0430\u0442\u044b","formula":"DATE_FORMAT(r.payment_dates,'%d.%m.%Y')","old_field_id":275},{"field_title":"\u0421\u0443\u043c\u043c\u0430","table_title":"\u0420\u0430\u0441\u0445\u043e\u0434","module_name":"cassa","grouping_fn_title":null,"is_grouping":0,"grouping_fn_id":0,"sort_order":0,"visible":1,"title":"\u0421\u0443\u043c\u043c\u0430 \u043e\u043f\u043b\u0430\u0442","formula":"r.paid_total","old_field_id":282},{"field_title":"\u0421\u0443\u043c\u043c\u0430","table_title":"\u0420\u0430\u0441\u0445\u043e\u0434","module_name":"cassa","grouping_fn_title":null,"is_grouping":0,"grouping_fn_id":0,"sort_order":0,"visible":1,"title":"\u0414\u043e\u043b\u0433 \u043f\u043e \u043f\u0440\u0438\u0445\u043e\u0434\u0443","formula":"r.debt_total","old_field_id":282}],"nesting":[],"filter":[{"pattern":"(((1 and 2) and 3) and 4)","number":1,"field_title":"\u0421\u0442\u0430\u0442\u0443\u0441 \u0434\u043e\u043a\u0443\u043c\u0435\u043d\u0442\u0430","table_title":"\u041f\u0440\u0438\u0445\u043e\u0434","module_name":"stores","operator_name":"enters_in","condition":"","value":"exec,save","is_dynamic":0},{"pattern":"(((1 and 2) and 3) and 4)","number":2,"field_title":"\u0414\u0430\u0442\u0430 \u043f\u0440\u0438\u0445\u043e\u0434\u043d\u043e\u0439 \u043d\u0430\u043a\u043b\u0430\u0434\u043d\u043e\u0439","table_title":"\u041f\u0440\u0438\u0445\u043e\u0434","module_name":"stores","operator_name":"more_and_equals","condition":"and","value":"2026-01-01T00:00:00","is_dynamic":1},{"pattern":"(((1 and 2) and 3) and 4)","number":3,"field_title":"\u0414\u0430\u0442\u0430 \u043f\u0440\u0438\u0445\u043e\u0434\u043d\u043e\u0439 \u043d\u0430\u043a\u043b\u0430\u0434\u043d\u043e\u0439","table_title":"\u041f\u0440\u0438\u0445\u043e\u0434","module_name":"stores","operator_name":"less_and_equals","condition":"and","value":"2026-10-05T00:00:00","is_dynamic":1},{"pattern":"(((1 and 2) and 3) and 4)","number":4,"field_title":"\u041a\u043b\u0438\u043d\u0438\u043a\u0430","table_title":"\u0421\u043a\u043b\u0430\u0434\u044b","module_name":"stores","operator_name":"equal","condition":"and","value":"1","is_dynamic":1}],"program_version":"0.546.11"}