Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- with v as (
- select distinct on (v.pkvenda) v.pkvenda
- , v.fkindiceeconomico
- , date_trunc('month', v.data) as datavenda
- from venda v
- where v.pkvenda = 20311
- )
- , pag as (
- with t as (
- select DISTINCT date_trunc('month', p.datapagamento) as datapagamento
- , sum(case when p.datapagamento is not null then p.valor + p.valorcomissao + p.valorservicos else 0 end) OVER (PARTITION BY v.pkvenda ORDER BY date_trunc('month', p.datapagamento)) as v
- , sum(case when p.datapagamento is not null then p.valor + p.valorcomissao + p.valorservicos else 0 end) OVER (PARTITION BY v.pkvenda, date_trunc('month', p.datapagamento)) as vm
- from parcelavenda p
- inner join v v on (v.pkvenda = p.fkvenda)
- where p.datapagamento is not null
- )
- , pdata as (
- select DISTINCT date_trunc('month', p.datavencimento) as datavencimento, p.fkvenda
- from parcelavenda p
- inner join v v on (v.pkvenda = p.fkvenda)
- left outer join t t on (t.datapagamento = date_trunc('month', p.datavencimento))
- where t is null
- )
- select distinct date_trunc('month', p.datavencimento) as datapagamento
- , sum(pa.vm) as v
- , 0 as vm
- from pdata p
- inner join v v on (v.pkvenda = p.fkvenda)
- left outer join t pa on (pa.datapagamento <= date_trunc('month', p.datavencimento))
- where true
- GROUP BY date_trunc('month', p.datavencimento)
- union
- select * from t t
- )
- , p as (
- select
- rank() over (PARTITION by p.fkvenda ORDER BY date_trunc('month', p.datavencimento), p.pkparcelavenda) as c
- , rank() over (PARTITION by p.fkvenda, greatest(0, trunc(cast(((DATE_PART('year', date_trunc('month', p.datavencimento) ) - DATE_PART('year', v.datavenda)) * 12 +
- ((DATE_PART('month', date_trunc('month', p.datavencimento) ) - DATE_PART('month', v.datavenda)) )) / 12 as numeric),0) ) ORDER BY date_trunc('month', p.datavencimento), p.pkparcelavenda) as ca
- , rank() over (PARTITION by p.fkvenda, date_trunc('month', p.datavencimento) ORDER BY p.pkparcelavenda) as cm
- , sum(p.valor + p.valorservicos + p.valorcomissao) over (partition by p.fkvenda) as valorcontrato
- , pag.v as totalpago
- , pag.vm as totalpagomes
- , date_trunc('month', p.datavencimento) as datavencimento
- , p.pkparcelavenda
- , p.valor
- , p.status
- , date_trunc('month', p.datapagamento) as datapagamento
- , p.fkvenda
- , (p.valor + p.valorservicos + p.valorcomissao) as v
- , v.fkindiceeconomico
- , v.datavenda
- , greatest(0, trunc(cast(((DATE_PART('year', date_trunc('month', p.datavencimento) ) - DATE_PART('year', v.datavenda)) * 12 +
- ((DATE_PART('month', date_trunc('month', p.datavencimento) ) - DATE_PART('month', v.datavenda)) )) / 12 as numeric),0) ) as anos
- , cast(COALESCE(ie.valor, 0) as numeric) as ieporcentagem
- from parcelavenda p
- inner join v v on (v.pkvenda = p.fkvenda)
- left outer join lancamentoindiceeconomico ie on (ie.fkindiceeconomico = v.fkindiceeconomico and ie.status = 0 and date_trunc('month', ie.data) = date_trunc('month', p.datavencimento - interval '1' month ))
- left outer join pag pag on (pag.datapagamento = date_trunc('month', p.datavencimento))
- )
- , f as(
- WITH RECURSIVE l AS (
- select
- p.c as ordem
- , p.datavencimento as datavencimento
- , p.valorcontrato as saldoinicial
- , cast(0 as numeric) as fator
- , p.valorcontrato + (p.valorcontrato * 0) as saldocorrigido
- , p.totalpagomes as abatimento
- , (p.valorcontrato + (p.valorcontrato * 0)) - p.totalpagomes as saldofinal
- , '| |' as "-"
- , p.v / p.valorcontrato as parcelaporcentagem
- , cast(1 as DOUBLE PRECISION) as saldoDevedorAno
- , cast(1 as DOUBLE PRECISION) as valorParcelaFaltandoAno
- , p.v as valorParcelaBase
- , cast(0 as DOUBLE PRECISION) as correcao
- , p.v as valorParcela
- , '| |' as "-2"
- , p.*
- from p p
- where p.c = 1
- UNION ALL
- select
- p.c as ordem
- , p.datavencimento as datavencimento
- , pa.saldofinal as saldoinicial
- , p.ieporcentagem as fator
- , pa.saldofinal + + case when p.cm = 1 then (pa.saldofinal * p.ieporcentagem / 100) else 0 end as saldocorrigido
- , p.totalpagomes as abatimento
- , pa.saldofinal + case when p.cm = 1 then (pa.saldofinal * p.ieporcentagem / 100) - p.totalpagomes else 0 end as saldofinal
- , '| |' as "-"
- , p.v / p.valorcontrato as parcelaporcentagem
- , (CASE WHEN p.ca = 1 THEN pa.saldofinal else pa.saldoDevedorAno end) as saldoDevedorAno
- , (CASE WHEN p.ca = 1 THEN p.valorcontrato - pa.totalpago else pa.valorParcelaFaltandoAno end) as valorParcelaFaltandoAno
- , p.v as valorParcelaBase
- , (CASE WHEN p.ca = 1 THEN pa.saldofinal * (p.v / (p.valorcontrato - pa.totalpago)) - p.v else pa.saldoDevedorAno * (p.v / (pa.valorParcelaFaltandoAno)) - p.v end) as correcao
- , (CASE WHEN p.ca = 1 THEN pa.saldofinal * (p.v / (p.valorcontrato - pa.totalpago)) else pa.saldoDevedorAno * (p.v / (pa.valorParcelaFaltandoAno)) end) as valorParcela
- , '| |' as "-2"
- , p.*
- from p p
- INNER JOIN l pa ON (p.c = pa.c + 1)
- )
- SELECT
- *
- FROM l
- )
- select *
- from f
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement