---

select
    l.id as load_snv_id,
    count(*) as rows_after_all_joins,
    count(distinct c.id) as contract_count,
    count(distinct sh.id) as sharer_count,
    count(distinct atc.id) as atc_count,
    count(distinct s.row_id) as selection_count
from ourpension.load_snv l
left join ourpension.contract c
    on l.contract_number = c.number
left join ourpension.sharer sh
    on sh.contract_id = c.id
left join ourpension.application_termination_contract atc
    on atc.sharer_id = sh.id
join tools.selection s
    on s.row_id = l.id
   and s.username = :username
   and s.code = :query_table_code
where l.file_id = :fileId
group by l.id
order by l.id;





select
    l.id as load_snv_id,
    l.year,
    count(*) as operation_rows,
    sum(t.amount) as sum_income
from ourpension.load_snv l
left join ourpension.contract c
    on l.contract_number = c.number
left join ourpension.sharer sh
    on sh.contract_id = c.id
join mgr.ft_dwh_get__pension_account_operation(
    cast(
        '{"p_sPensionAccountId":"' || sh.sharer_id_1c ||
        '", "p_sServiceType":"' || c.service_type || '"}'
        as jsonb
    )
) t on true
where l.file_id = :fileId
  and cast(date_part('year', t.period_date) as integer) = cast(l.year as integer)
  and case
        when c.service_type = 'NPO'
            then t.operation_type_name in ('Ч/з банк', 'От работодателя')
        when c.service_type = 'PDS'
            then t.operation_type_name = 'Сберегательные взносы'
        else false
      end
group by
    l.id,
    l.year
order by l.id;