Last active
February 24, 2021 18:00
-
-
Save jonathanalves/fd7fe4e38086a45b3cbbd852daa328d2 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
update livro_protocolo set ordem = null where "data" >= '2020-12-05'; | |
with maximo as ( | |
select max(ordem) as maximo from livro_protocolo lp | |
), | |
ordens as ( | |
select lp.id, lp."data", lp.tipo, m.maximo + row_number() over(order by lp."data" asc, p.codigo asc, lp.tipo asc) ordem | |
from livro_protocolo lp | |
join protocolo p on p.id = lp.protocolo_id | |
join maximo m on 1=1 | |
where lp."data" >= '2020-12-05' | |
order by lp."data" asc, p.codigo asc, lp.tipo asc | |
) | |
update livro_protocolo set ordem = o.ordem from ordens o where livro_protocolo.id = o.id; | |
with anteriores as ( | |
select lp.id, string_agg(CAST(replace(TRIM(to_char(la.ordem, 'G999G999G999G990')),',','.') AS TEXT), ', ' ORDER by la.ordem asc) anteriores | |
from livro_protocolo lp | |
join livro_protocolo la on lp.ordem is not null and la.ordem is not null and la.ordem < lp.ordem and la.protocolo_id = lp.protocolo_id | |
where lp."data" >= '2020-12-05' | |
group by lp.id | |
) update livro_protocolo set anteriores = anteriores.anteriores from anteriores where livro_protocolo.id = anteriores.id; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
SELECT p.id protocolo_id, pp."data"::date "data", c.natureza natureza, c.solicitante solicitante, 'REINGRESSO' tipo, pp."data" as ordem, e.nome etapa
FROM protocolo_atividade pp
join etapa e on pp.etapa_id = e.id
join protocolo p on p.id = pp.protocolo_id
left join livro_protocolo c on c.protocolo_id = p.id and c.tipo = 'CADASTRO'
where p.dominio = 'PROTOCOLO_RI'
and (pp.etapa_id = '85f94e3f-1a5a-4405-84d0-70ff4298def0' or (pp.etapa_id = '023e9849-5262-49c9-bcbc-550ba1158324' and pp.destino_id != '85f94e3f-1a5a-4405-84d0-70ff4298def0') )
and pp.tipo = 'AVANCAR' and retornada is false
--and p.codigo = 728296
and pp."data" >= '2020-04-20' AND c.solicitante IS NOT null
union all
SELECT p.id protocolo_id, pp."data"::date "data", c.natureza natureza, c.solicitante solicitante, 'EXIGENCIA' tipo, pp."data" as ordem, e.nome etapa
FROM protocolo_atividade pp
join etapa e on pp.etapa_id = e.id
join protocolo p on p.id = pp.protocolo_id
left join livro_protocolo c on c.protocolo_id = p.id and c.tipo = 'CADASTRO'
where p.dominio = 'PROTOCOLO_RI'
and pp.destino_id = '023e9849-5262-49c9-bcbc-550ba1158324'
and pp.tipo = 'AVANCAR' and retornada is false
--and p.codigo = 728296
and pp."data" >= '2020-04-20' AND c.solicitante IS NOT null
order by ordem asc
--limit 10