jonathanalves

/*
	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. 

jonathanalves

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;

jonathanalves

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),

jonathanalves

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);

jonathanalves

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

jonathanalves

/* DTO ato ------------------------------------------- */

{

  "valoresBaseCalculo":{

    "alienacao":10000,
    "alienacaoAtualizado":12427.14,

    "valorAvaliacao":10000,

jonathanalves

-- 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/

jonathanalves

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, '')

jonathanalves

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

jonathanalves

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