SHARE
TWEET

Untitled

a guest Dec 7th, 2019 106 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2.  
  3.  
  4.  
  5.        
  6. with v as (
  7.     select distinct on (v.pkvenda) v.pkvenda
  8.     , v.fkindiceeconomico
  9.     , date_trunc('month', v.data) as datavenda
  10.     from venda v
  11.     where v.pkvenda  = 20311
  12. )
  13. , pag as (
  14.    
  15.         with t as (
  16.         select DISTINCT date_trunc('month', p.datapagamento) as datapagamento
  17.         , 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
  18.         , 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
  19.         from parcelavenda p
  20.         inner join v v on (v.pkvenda = p.fkvenda)
  21.         where p.datapagamento is not null
  22.         )
  23.        
  24.        
  25.     , pdata as (
  26.             select DISTINCT date_trunc('month', p.datavencimento) as datavencimento, p.fkvenda
  27.             from parcelavenda p
  28.             inner join v v on (v.pkvenda = p.fkvenda)
  29.             left outer join t t on (t.datapagamento = date_trunc('month', p.datavencimento))
  30.             where t is null
  31.         )
  32.        
  33.         select distinct date_trunc('month', p.datavencimento) as datapagamento
  34.         , sum(pa.vm) as v
  35.         , 0 as vm
  36.         from pdata p
  37.         inner join v v on (v.pkvenda = p.fkvenda)
  38.         left outer join t pa on (pa.datapagamento <= date_trunc('month', p.datavencimento))
  39.         where true
  40.         GROUP BY date_trunc('month', p.datavencimento)
  41.         union
  42.         select * from t t
  43. )
  44. , p as (
  45.     select
  46.     rank() over (PARTITION by p.fkvenda ORDER BY date_trunc('month', p.datavencimento), p.pkparcelavenda) as c
  47.     , rank() over (PARTITION by p.fkvenda, greatest(0, trunc(cast(((DATE_PART('year', date_trunc('month', p.datavencimento) ) - DATE_PART('year', v.datavenda)) * 12 +
  48.         ((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
  49.     , rank() over (PARTITION by p.fkvenda, date_trunc('month', p.datavencimento) ORDER BY p.pkparcelavenda) as cm
  50.     , sum(p.valor + p.valorservicos + p.valorcomissao) over (partition by p.fkvenda) as valorcontrato
  51.     , pag.v as totalpago
  52.     , pag.vm as totalpagomes
  53.     , date_trunc('month', p.datavencimento) as datavencimento
  54.     , p.pkparcelavenda
  55.     , p.valor
  56.     , p.status
  57.     , date_trunc('month', p.datapagamento) as datapagamento
  58.     , p.fkvenda
  59.     , (p.valor + p.valorservicos + p.valorcomissao) as v
  60.     , v.fkindiceeconomico
  61.     , v.datavenda
  62.     , greatest(0, trunc(cast(((DATE_PART('year', date_trunc('month', p.datavencimento) ) - DATE_PART('year', v.datavenda)) * 12 +
  63.         ((DATE_PART('month', date_trunc('month', p.datavencimento) ) - DATE_PART('month', v.datavenda)) )) / 12  as numeric),0) )  as anos
  64.   , cast(COALESCE(ie.valor, 0) as numeric) as ieporcentagem
  65.  
  66.     from parcelavenda p
  67.     inner join v v on (v.pkvenda = p.fkvenda)
  68.   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 ))
  69.   left outer join pag pag on (pag.datapagamento = date_trunc('month', p.datavencimento))
  70.  
  71.  
  72. )
  73.  
  74.  
  75. , f as(
  76.  
  77.     WITH RECURSIVE l AS (
  78.          
  79.             select
  80.             p.c as ordem
  81.             , p.datavencimento as datavencimento
  82.             , p.valorcontrato as saldoinicial
  83.             , cast(0 as numeric) as fator
  84.             , p.valorcontrato + (p.valorcontrato * 0) as saldocorrigido
  85.             , p.totalpagomes as abatimento
  86.             , (p.valorcontrato + (p.valorcontrato * 0)) - p.totalpagomes as saldofinal
  87.             , '|                                         |' as "-"
  88.             , p.v / p.valorcontrato as parcelaporcentagem
  89.             , cast(1 as DOUBLE PRECISION) as saldoDevedorAno
  90.             , cast(1 as DOUBLE PRECISION)  as valorParcelaFaltandoAno
  91.             , p.v as valorParcelaBase
  92.             , cast(0 as DOUBLE PRECISION) as correcao
  93.             , p.v as valorParcela
  94.             , '|                                         |' as "-2"
  95.             , p.*
  96.             from p p
  97.             where p.c = 1
  98.            
  99.          UNION ALL
  100.                 select
  101.             p.c as ordem
  102.             , p.datavencimento as datavencimento
  103.             , pa.saldofinal as saldoinicial
  104.             , p.ieporcentagem as fator
  105.             , pa.saldofinal + + case when p.cm = 1 then (pa.saldofinal * p.ieporcentagem / 100) else 0 end as saldocorrigido
  106.             , p.totalpagomes as abatimento
  107.             , pa.saldofinal + case when p.cm = 1 then (pa.saldofinal * p.ieporcentagem / 100) - p.totalpagomes else 0 end as saldofinal
  108.             , '|                                         |' as "-"
  109.             , p.v / p.valorcontrato as parcelaporcentagem
  110.             , (CASE WHEN p.ca = 1 THEN pa.saldofinal else pa.saldoDevedorAno end) as saldoDevedorAno
  111.             , (CASE WHEN p.ca = 1 THEN p.valorcontrato - pa.totalpago else pa.valorParcelaFaltandoAno end) as valorParcelaFaltandoAno
  112.             , p.v as valorParcelaBase
  113.             , (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
  114.             , (CASE WHEN p.ca = 1 THEN pa.saldofinal * (p.v / (p.valorcontrato - pa.totalpago)) else pa.saldoDevedorAno * (p.v / (pa.valorParcelaFaltandoAno)) end) as valorParcela
  115.             , '|                                         |' as "-2"
  116.             , p.*
  117.             from p p
  118.             INNER JOIN l pa ON (p.c = pa.c + 1)
  119.                  
  120.             )
  121.             SELECT
  122.          *
  123.     FROM l
  124.      
  125.      )
  126.      
  127.      select *
  128.      from f
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top