---

select
    cast(o.ef_year as varchar(4)) as year_name,
    concat(ot.name, ' ', sh.number) as name,

    sum(
        case
            when o.operation_type_id = 125
                then -o.value
            when o.operation_type_id in (118, 130)
                then null
            else o.value
        end
    ) as sum_value,

    sum(
        case
            when o.operation_type_id = 118
                then o.value
            when o.operation_type_id = 130
                then -o.value
            else n.value
        end
    ) as sum_ndfl

from operations o

join back_office.operation_type ot
    on ot.id = o.operation_type_id

left join operations n
    on o.id = n.id
    and n.cred_mnemonics like '%НДФЛ%'
    and o.operation_type_id not in (112, 118, 125, 130, 173)

left join ourpension.sharer sh
    on o.owner_sharer_id = sh.id

where
    o.operation_type_id in (112, 118, 125, 130, 173)

    or (
        o.deb_mnemonics is not null
        and o.deb_mnemonics not like '%СВ%'
        and o.deb_mnemonics not like '%ПП%'
        and ot.name not like '%Выплата%'
        and o.operation_type_id not in (106, 103, 101, 111, 104)
        and coalesce(o.cred_mnemonics, '') not like '%НДФЛ%'
    )

group by
    o.ef_year,
    ot.name,
    sh.number

order by
    o.ef_year,
    ot.name,
    sh.number;