---

SELECT
    -- Атрибуты файла
    'SVEDVYCH2.1.219.2_' || f.inn || '_' 
        || to_char(now(), 'YYYYMMDD') || '_' 
        || replace(cast(gen_random_uuid() as text), '-', '') AS file_id,
    '1.01' AS vers_form,
    -- Атрибуты документа
    to_char(now(), 'DD.MM.YYYY') AS date_doc,
    to_char(now(), 'HH24:MI:SS') AS time_doc,
    cc.report_xml_filename AS nomer_sved,
    '1184070' AS knd,
    -- Фонд
    f.inn AS fund_inn,
    f.kpp AS fund_kpp,
    f.name_full AS fund_name,
    -- Налоговый период
    ri.tax_period_year AS period
FROM report.unv_registry_item ri
JOIN ourpension.contract c      ON c.id = ri.contract_id
JOIN ourpension.fund f          ON f.is_our_fund = true
LEFT JOIN ourpension.contribution_certificate cc 
    ON cc.contract_id = ri.contract_id
    AND cc.report_year = ri.tax_period_year
    AND cc.certificate_status = 'sent'
WHERE ri.id = :registry_item_id


SELECT
    to_char(i.birth_date, 'DD.MM.YYYY')    AS birth_date,
    i.inn                                   AS person_inn,
    i.last_name,
    i.first_name,
    i.middle_name,
    i.certificate_series || ' ' || i.certificate_number AS ser_nom_doc,
    i.certificate_code
FROM report.unv_registry_item ri
JOIN ourpension.individual i    ON i.id = ri.client_id
WHERE ri.id = :registry_item_id



SELECT
    c.number                                AS contract_number,
    to_char(c.date, 'DD.MM.YYYY')          AS contract_date,
    '1'                                     AS stat_ds,
    '1'                                     AS pr_lits
FROM report.unv_registry_item ri
JOIN ourpension.contract c      ON c.id = ri.contract_id
WHERE ri.id = :registry_item_id



SELECT
    to_char(io.date, 'DD.MM.YYYY')         AS pay_date,
    t.value                                 AS pay_sum
FROM report.unv_registry_item ri
JOIN back_office.incoming_order io          ON io.contract_id = ri.contract_id
JOIN back_office.incoming_order_operation ioo ON ioo.incoming_order_id = io.id
JOIN back_office.operation o                ON o.id = ioo.operation_id
JOIN back_office."transaction" t            ON t.operation_id = ioo.operation_id
JOIN back_office.account a                  ON a.id = t.credit_account_id
JOIN back_office.account_type at            ON at.id = a.account_type_id
WHERE ri.id = :registry_item_id
  AND at.mnemonics = 'ПДС СВ'
  AND io.payment_return = false
  AND CAST(EXTRACT(YEAR FROM io.date) AS int) = ri.tax_period_year
ORDER BY io.date



#set($h = $db.findOne($header))
SVEDVYCH2.1.219.2_${h.fund_inn}_${h.date_doc_raw}.xml



#set($h = $db.findOne($header))
#set($p = $db.findOne($person))
#set($c = $db.findOne($contract))
<?xml version="1.0" encoding="UTF-8"?>
<Файл ИдДок="$h.file_id" ВерсФорм="$h.vers_form">
    <Документ КНД="$h.knd" ДатаСвед="$h.date_doc" ВремяСвед="$h.time_doc" НомерСвед="$h.nomer_sved">
        <СвФонд НаимФонд="$h.fund_name" ИННФонд="$h.fund_inn" КППФонд="$h.fund_kpp"/>
        <СвНП ДатаРожд="$p.birth_date" ИННФЛ="$p.person_inn">
            <ФИО Фамилия="$p.last_name" Имя="$p.first_name" Отчество="$p.middle_name"/>
            <УдЛичнФЛ КодВидДок="$p.certificate_code" СерНомДок="$p.ser_nom_doc"/>
        </СвНП>
        <СвДог ДатаДог="$c.contract_date" НомДог="$c.contract_number" СтатДС="$c.stat_ds" ПрЛиц="$c.pr_lits">
            <СвРасх Период="$h.period">
                #foreach($pay in $db.find($payments))
                <ОбщСумРасх ДатаУпл="$pay.pay_date" СуммаДС="$pay.pay_sum"/>
                #end
            </СвРасх>
        </СвДог>
    </Документ>
</Файл>


console.log(registryItems.dataTable().api().rows().data().toArray());