---

WITH operation(operation_id, credit_mnemonics, debit_mnemonics, value) as (

  -- Дебет
  select
    o.id,
    cat.mnemonics,
    dat.mnemonics,
    t.value
  from back_office.operation o
		join ourpension.sharer s on s.id = :sharerId
    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 <= :effectiveDate
		and da.owner_sharer_id = s.id
		and da.owner_sharer_id <> coalesce(ca.owner_sharer_id, -1)

	union all

  -- Кредит
  select
    o.id,
    cat.mnemonics,
    dat.mnemonics,
    t.value
  from back_office.operation o
		join ourpension.sharer s on s.id = :sharerId
    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 <= :effectiveDate
		and ca.owner_sharer_id = s.id

	union all

  -- Возврат ОПС
	select
    o.id,
    cat.mnemonics,
    dat.mnemonics,
    t.value
	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 <= :effectiveDate
		and ca.owner_individual_id = rd.recipient_individual_id
		and a.sharer_id = :sharerId
		
	union all
	
	  -- Возврат НПО
	select
    o.id,
    cat.mnemonics,
    dat.mnemonics,
    t.value
	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 <= :effectiveDate
		and ca.owner_individual_id = p.recipient_individual_id
		and p.sharer_id = :sharerId

	union all

  -- Повторная выплата возврата
	select
    o.id,
    cat.mnemonics,
    dat.mnemonics,
    t.value
	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 <= :effectiveDate
		and ca.owner_individual_id = rd.recipient_individual_id
		and a.sharer_id = :sharerId

), operation_sorter as (
SELECT
  op.id as id,
  op.effective_date as effectiveDate,
  op.id as operation_id,
  m.value as value,
  op.approved as approved,
  op.period_date as periodDate,
  m.credit_mnemonics as creditMnemonics,
  m.debit_mnemonics as debitMnemonics,
  op.payment_register_id as paymentRegisterId,
  op.ops_payment_register_id as opsPaymentRegisterId,
  op.operation_type_id as operationTypeId 
FROM back_office.operation op
	join operation m on m.operation_id = op.id
  join back_office.operation_type ot on ot.id = op.operation_type_id
  left join back_office.account_type atc on atc.mnemonics = m.credit_mnemonics 
  left join back_office.account_type atd on atd.mnemonics = m.debit_mnemonics
  
WHERE 	
	case
	when :acc_filter_date <>'' then
		cast(:acc_filter_date as date) = cast(op.effective_date as date)
	else true
	end
	and
	case
	when :acc_filter_period <>'' then
		cast(:acc_filter_period as date) = cast(op.period_date as date)
	else true
	end
	and
	case
	when :acc_filter_oper_type <>'' then
		cast(:acc_filter_oper_type as integer) = op.operation_type_id
	else true
	end
	and
	case
	when :acc_filter_sum <>'' then
		cast(m.value as text) like '%'||:acc_filter_sum||'%'
	else true
	end
	and
	case
	when :acc_filter_in <>'' then
		cast(:acc_filter_in as integer) = atc.id
	else true
	end
	and
	case
	when :acc_filter_out <>'' then
		cast(:acc_filter_out as integer) = atd.id
	else true
	end
	ORDER BY op.id DESC
)
SELECT
  o.id as id,
  to_char(os.effectiveDate, 'DD.MM.YYYY') as effectiveDate,
  '<a target="_blank" href="view?viewCode=9CNmoQeC&id='||o.id||'"target="_blank">'||ot.short_name||'</a>' as operationType,
  REPLACE(trim(to_char(coalesce(os.value, 0), '99 999 999 999 999 990D99')), '.', ',') as value,
  to_char(os.approved, 'DD.MM.YYYY') as approved,
  to_char(os.periodDate, 'DD.MM.YYYY') as periodDate,
  '<span class="label label-success">' || os.creditMnemonics || '</span>' as creditMnemonics,
  '<span class="label label-primary">' || os.debitMnemonics || '</span>' as debitMnemonics,
  os.paymentRegisterId as paymentRegisterId,
  os.opsPaymentRegisterId as opsPaymentRegisterId,
  os.operationTypeId as operationTypeId 
FROM operation_sorter os
join back_office.operation o on o.id = os.operation_id
join back_office.operation_type ot on ot.id = o.operation_type_id