---

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



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,
    to_char(now(), 'YYYYMMDD')                                AS file_date,
    COALESCE(cc.report_xml_filename, CAST(ri.id AS varchar))  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.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))
#set($p = $db.findOne($person))
#set($c = $db.findOne($contract))
<?xml version="1.0" encoding="UTF-8"?>
<Файл ИдДок="$h.file_id" ВерсФорм="$h.vers_form" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="gnivс-500-03">
    <Документ КНД="$h.knd" ДатаСвед="$h.date_doc" ВремяСвед="$h.time_doc" НомерСвед="$h.nomer_sved">
        <СвФонд НаимФонд="$h.fund_name" ИННФонд="$h.fund_inn" КППФонд="$h.fund_kpp"/>
        <СвНП ДатаРожд="$p.birth_date"#if($p.person_inn && $p.person_inn != '') ИННФЛ="$p.person_inn"#end>
            <ФИО Фамилия="$p.last_name" Имя="$p.first_name"#if($p.middle_name && $p.middle_name != '') Отчество="$p.middle_name"#end/>
            #if(!$p.person_inn || $p.person_inn == '')
            <УдЛичнФЛ КодВидДок="$p.certificate_code" СерНомДок="$p.ser_nom_doc"/>
            #end
        </СвНП>
        <СвДог ДатаДог="$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
            </СвРасх>
        </СвДог>
    </Документ>
</Файл>