---

with operations (id, ef_date, cred_mnemonics, deb_mnemonics, value, operation_type_id) as (
    select
    o.id,
    o.effective_date as ef_date,
    cat.mnemonics as cred_mnemonics,
    dat.mnemonics as deb_mnemonics,
    t.value,
    o.operation_type_id
from back_office.operation o
    join back_office.transaction t on t.operation_id = o.id
    left join back_office.account da on da.id = t.debit_account_id
    left join back_office.account ca on ca.id = t.credit_account_id
    left join back_office.account_type dat on dat.id = da.account_type_id
    left join back_office.account_type cat on cat.id = ca.account_type_id
where o.effective_date between cast($P{bDate} as date) and cast($P{eDate} as date)
        and da.owner_sharer_id = $P{sharerId}
        and da.owner_sharer_id <> coalesce(ca.owner_sharer_id, 0)
    
union all

select
    o.id,
    o.effective_date,
    cat.mnemonics,
    case when dat.mnemonics is null and ot.code = 'PDS_RETURN_NDFL_REDEMPTION'
         then cat.mnemonics
         else dat.mnemonics end,
    case when ot.code = 'PDS_RETURN_NDFL_REDEMPTION' then -t.value else t.value end,
    o.operation_type_id
from back_office.operation o
join back_office.operation_type ot
    on ot.id = o.operation_type_id
    join back_office.transaction t on t.operation_id = o.id
    join back_office.account ca on ca.id = t.credit_account_id
    left join back_office.account da on da.id = t.debit_account_id
    left join back_office.account_type dat on dat.id = da.account_type_id
    left join back_office.account_type cat on cat.id = ca.account_type_id
where o.effective_date between cast($P{bDate} as date) and cast($P{eDate} as date)
        and ca.owner_sharer_id = $P{sharerId}

    union all

-- Возврат ОПС
    select
    o.id,
    o.effective_date,
    cat.mnemonics,
    dat.mnemonics,
    t.value,
    o.operation_type_id
    from back_office.operation o
     join back_office.ops_payment_return r on r.id = o.ops_payment_return_id
     join back_office.ops_payment_register_decision rd on rd.id = r.ops_payment_register_decision_id
     join back_office.ops_payment_decision d on d.id = rd.ops_payment_decision_id
     join ourpension.ops_pension_assignment_application a on a.id = d.ops_pension_assignment_application_id    
    
    join back_office.transaction t on t.operation_id = o.id
    join back_office.account da on da.id = t.debit_account_id
    join back_office.account ca on ca.id = t.credit_account_id
    join back_office.account_type dat on dat.id = da.account_type_id
    join back_office.account_type cat on cat.id = ca.account_type_id
    
where o.effective_date between cast($P{bDate} as date) and cast($P{eDate} as date)
        and ca.owner_individual_id = rd.recipient_individual_id
        and a.sharer_id = $P{sharerId}
        
    union all
    
     -- Возврат НПО
    select
    o.id,
    o.effective_date,
    cat.mnemonics,
    dat.mnemonics,
    t.value,
    o.operation_type_id
    from back_office.operation o
     join back_office.payment_return pr on pr.id = o.payment_return_id
     join back_office.payment p on p.id = pr.payment_id
     join back_office.payment_register r on p.payment_register_id = r.id
    
    join back_office.transaction t on t.operation_id = o.id
    join back_office.account da on da.id = t.debit_account_id
    join back_office.account ca on ca.id = t.credit_account_id
    join back_office.account_type dat on dat.id = da.account_type_id
    join back_office.account_type cat on cat.id = ca.account_type_id
    
where o.effective_date between cast($P{bDate} as date) and cast($P{eDate} as date)
        and ca.owner_individual_id = p.recipient_individual_id
        and p.sharer_id = $P{sharerId}

    union all

-- Повторная выплата возврата
    select
    o.id,
    o.effective_date,
    cat.mnemonics,
    dat.mnemonics,
    t.value,
    o.operation_type_id
    from back_office.operation o
     join back_office.ops_payment_register_decision rd on rd.ops_payment_register_id = o.ops_payment_register_id
     join back_office.ops_payment_return r on r.id = rd.ops_payment_return_id
     join back_office.ops_payment_decision d on d.id = rd.ops_payment_decision_id
     join ourpension.ops_pension_assignment_application a on a.id = d.ops_pension_assignment_application_id
     join back_office.transaction t on t.operation_id = o.id
     join back_office.account da on da.id = t.debit_account_id
     join back_office.account ca on ca.id = t.credit_account_id
     join back_office.account_type dat on dat.id = da.account_type_id
     join back_office.account_type cat on cat.id = ca.account_type_id
where o.effective_date between cast($P{bDate} as date) and cast($P{eDate} as date)
        and ca.owner_individual_id = rd.recipient_individual_id
        and a.sharer_id = $P{sharerId}
)
select to_char(o.ef_date, 'DD.MM.YYYY') as effective_date, coalesce(ot.name_for_statement, ot.name) as name, sum(o.value) as value, o.ef_date
from operations o
join back_office.operation_type ot on ot.id = o.operation_type_id and ot.display_in_statement = true and ot.type_movement in (582, 583)
where o.deb_mnemonics is not null
group by o.ef_date, coalesce(ot.name_for_statement, ot.name)
order by o.ef_date