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
/* | |
Written by: patpawlowski | |
Created On: Oct 26, 2015 at 4:51:52 PM | |
Description: This is a rough attempt to create a funciton to strip the markup from | |
the Act TBL_NOTE.NOTETEXT field. It appears to work for what I need | |
but could probably use some work with the escaped characters. | |
It's not particularly fast but it is faster than other solutions I've come | |
across. It takes about 4 seconds to parse 2700 records. |
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
drop function IF exists count_business_days; | |
create or replace function count_business_days(start_date date, end_date date) | |
returns bigint | |
as $fbd$ | |
select count(d::date) as d | |
from generate_series(start_date, end_date, '1 day'::interval) d | |
where extract('dow' from d) not in (0, 6) and | |
not exists ( select 1 from public.feriado where dia::date = d::date ) | |
$fbd$ language sql; |
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 public.configuracao SET | |
arisp=cast(a.depois->'arisp' as jsonb), | |
atendimento=cast(a.depois->'atendimento' as jsonb), | |
cabecalho_site=cast(a.depois->'cabecalho_site' as jsonb), | |
calculadora_valor_venal=cast(a.depois->'calculadora_valor_venal' as jsonb), | |
chat=cast(a.depois->'chat' as jsonb), | |
cidade=cast(a.depois->'cidade' as text), | |
corisc=cast(a.depois->'corisc' as jsonb), | |
debito=cast(a.depois->'debito' as jsonb), | |
detalhes_cartorio=cast(a.depois->'detalhes_cartorio' as jsonb), |
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
delete from atribuicao where usuario_id in (select id from usuario where ativo is false); | |
delete from atribuicao where etapa_id in (select id from etapa where ativo is false); | |
delete from atribuicao where tipo_servico_id in (select id from tipo_servico where ativo is false); | |
delete from checklist_tipo_documento where tipo_documento_id in (select id from tipo_documento where ativo is false); | |
delete from checklist_tipo_documento where checklist_id in (select id from checklist where ativo is false); | |
delete from checklist_tipo_servico where tipo_servico_id in (select id from tipo_servico where ativo is false); | |
delete from checklist_tipo_servico where checklist_id in (select id from checklist where ativo is false); |
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
select substring(nome from '\d+'), nome, * from arquivo_morto am where pasta = 'titulos' and substring(nome from '[a-zA-Z\s]+') is null | |
update arquivo_morto set nome = replace(replace(nome, 'TIT', ''), '.pdf', '') where pasta = 'titulos' and substring(nome from '[a-zA-Z\s]+') is null; | |
update arquivo_morto set nome = substring(nome from '\d+')::numeric::text where pasta = 'titulos' and substring(nome from '[a-zA-Z\s]+') is null; | |
-- Títulos: | |
with arquivos as ( | |
select | |
p.id referencia |
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
/* DTO ato ------------------------------------------- */ | |
{ | |
"valoresBaseCalculo":{ | |
"alienacao":10000, | |
"alienacaoAtualizado":12427.14, | |
"valorAvaliacao":10000, |
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
-- http://tatiyants.com/pev/ | |
EXPLAIN (COSTS OFF) | |
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) | |
-- realizar a analise da query lenta | |
explain (analyze,buffers,timing) DELETE FROM indicador_pessoal_versao where id = 'c5df0553-612c-4a89-9d48-131890efff92' | |
-- https://www.cybertec-postgresql.com/en/avoid-or-for-better-performance/ |
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
ALTER TABLE indicador_pessoal_versao DROP CONSTRAINT uk_2s3c25we0a91ejyy5moa8623j; | |
update indicador_pessoal_versao | |
set hashcode = md5(CONCAT_WS(';', | |
coalesce(indicador_Pessoal_Id::varchar, '') | |
, coalesce(nome, '') | |
, coalesce(documento, '') | |
, coalesce(documento_Desconhecido::varchar, '') | |
, coalesce(denominacao, '') |
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 |
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
select i.documento, TO_CHAR(p.registro, 'MM-YYYY') mes, sum(pf.valor) total, count(p.id) protocolos | |
from protocolo p | |
join protocolo_financeiro pf on p.id = pf.protocolo_id | |
join indicador_pessoal_versao i on i.id = p.tomador_id or i.id = p.interessado_id or i.id = p.solicitante_id | |
where p.registro::date between '2021-01-01' and '2021-02-28' and i.documento is not null | |
and pf.forma_pagamento = 'DINHEIRO' | |
group by 1,2 | |
having sum(pf.valor) >= 30000 | |
order by 1,2 |
NewerOlder