---

SELECT
    CASE
        WHEN ri.contract_type = 'PDS' THEN 'SVEDVYCH2.1.219.2_'
        WHEN ri.contract_type = 'NPO' THEN 'SVEDVYCH1.1.219.2_'
    END || f.inn || '_'
        || to_char(now(), 'YYYYMMDD') || '_'
        || replace(cast(gen_random_uuid() as text), '-', '')   AS file_id,
    CASE
        WHEN ri.contract_type = 'PDS' THEN 'SVEDVYCH2.1.219.2_'
        WHEN ri.contract_type = 'NPO' THEN 'SVEDVYCH1.1.219.2_'
    END                                                         AS prefix,
    '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,
    replace(cast(gen_random_uuid() as text), '-', '')           AS guid,
    COALESCE(cc.report_xml_filename, CAST(ri.id AS varchar))   AS nomer_sved,
    CASE
        WHEN ri.contract_type = 'PDS' THEN '1184070'
        WHEN ri.contract_type = 'NPO' THEN '1184069'
    END                                                         AS knd,
    ri.contract_type                                            AS contract_type,
    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(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 (
      (ri.contract_type = 'PDS' AND at.mnemonics = 'ПДС СВ')
      OR
      (ri.contract_type = 'NPO' 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))
${h.prefix}${h.fund_inn}_${h.file_date}_${h.guid}.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" 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
        </СвНП>
        #if($h.contract_type == 'PDS')
        <СвДог ДатаДог="$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
            </СвРасх>
        </СвДог>
        #else
        <СвДог ДатаДог="$c.contract_date" НомДог="$c.contract_number" ПрВыгодопр="$c.pr_lits">
            <СвРасх Период="$h.period">
                #foreach($pay in $db.find($payments))
                <ОбщСумРасх ДатаУпл="$pay.pay_date" СуммаДС="$pay.pay_sum"/>
                #end
            </СвРасх>
        </СвДог>
        #end
    </Документ>
</Файл>