WO0000001455651.sql

/**
 * OBS:
 * 1 - EXECUTAR TODOS OS PASSOS PARA PREPARAÇÃO
 */

--DROP MATERIALIZED VIEW IF EXISTS par.vm_relatorio_pc_fluxo CASCADE;

CREATE MATERIALIZED VIEW par.vm_relatorio_pc_fluxo AS 
SELECT * FROM par.v_fluxo_prestacao_contas_v2 vfpc; -- EXEC 1

CREATE INDEX idx_processo ON par.vm_relatorio_pc_fluxo(processo); --EXEC 2
CREATE INDEX idx_terid ON par.vm_relatorio_pc_fluxo(terid); --EXEC 3
CREATE INDEX idx_dopid ON par.vm_relatorio_pc_fluxo(dopid); --EXEC 4
CREATE INDEX idx_dotid_par3 ON par.vm_relatorio_pc_fluxo(dotid_par3); --EXEC 5
CREATE INDEX idx_dotid_par4 ON par.vm_relatorio_pc_fluxo(dotid_par4); --EXEC 6
CREATE INDEX idx_proid_pac ON par.vm_relatorio_pc_fluxo(proid_pac); --EXEC 7
CREATE INDEX idx_proid_par ON par.vm_relatorio_pc_fluxo(proid_par); --EXEC 8
CREATE INDEX idx_prpid ON par.vm_relatorio_pc_fluxo(prpid); --EXEC 9
CREATE INDEX idx_proid_par3 ON par.vm_relatorio_pc_fluxo(proid_par3); --EXEC 10
CREATE INDEX idx_proid_par4 ON par.vm_relatorio_pc_fluxo(proid_par4); --EXEC 11

--DROP MATERIALIZED VIEW IF EXISTS par.vm_relatorio_pc_datas_limite CASCADE;

CREATE MATERIALIZED VIEW par.vm_relatorio_pc_datas_limite AS
SELECT
	vfpc.processo,
	vfpc.tipo,
	vfpc.modulo,
	par.data_limite_pc(vfpc.processo) AS prazo_pc,
	CASE
		WHEN vfpc.tipo = 'OBRA' OR vfpc.tipo = 'PAC'
			THEN par.retornadataenviopc(vfpc.processo)
		WHEN vfpc.tipo = 'PAR' AND vfpc.modulo = 'PAR2'
			THEN cpar.cpcdtenvio
		WHEN vfpc.tipo = 'PAR' AND vfpc.modulo = 'PAR3'
			THEN epc.epcdatavalidacao
		WHEN vfpc.tipo = 'PAR' AND vfpc.modulo = 'PAR4'
			THEN epc2.epcdatavalidacao
	END::date AS dt_envio_pc
FROM par.vm_relatorio_pc_fluxo vfpc
LEFT JOIN (
	SELECT 
		c.prpid,
		max(c.cpcdtenvio)::date AS cpcdtenvio
	FROM par.comprovanteprestacaocontas c
	WHERE c.cpcstatus = 'A'
	GROUP BY prpid
) cpar ON cpar.prpid = vfpc.prpid
LEFT JOIN (
	SELECT 
		epc.proid,
		max(epc.epcdatavalidacao)::date AS epcdatavalidacao
	FROM par3.execucao_prestacao_contas epc
	WHERE epc.epcstatus = 'A'
	GROUP BY proid
) AS epc ON epc.proid = vfpc.proid_par3
LEFT JOIN (
	SELECT 
		epc.proid,
		max(epc.epcdatavalidacao)::date AS epcdatavalidacao
	FROM par4.execucao_prestacao_contas epc
	WHERE epc.epcstatus = 'A'
	GROUP BY proid
) AS epc2 ON epc2.proid = vfpc.proid_par4; --EXEC 12


CREATE INDEX idx_processo_datas ON par.vm_relatorio_pc_datas_limite(processo); --EXEC 13

--DROP MATERIALIZED VIEW IF EXISTS par.vm_relatorio_pc_pagamento_processo_ob CASCADE;

CREATE MATERIALIZED VIEW par.vm_relatorio_pc_pagamento_processo_ob AS
SELECT 
	e.empnumeroprocesso AS processo,
	sum(p.pagvalorparcela) AS total_pag,
	MIN(p.pagnumeroob) AS primeiro_ob
FROM par.empenho e
JOIN par.pagamento p ON p.empid = e.empid
WHERE p.pagsituacaopagamento ILIKE '%EFETIVADO%'
GROUP BY empnumeroprocesso
UNION ALL
SELECT 
	e.empnumeroprocesso,
	sum(p.pagvalorparcela),
	MIN(p.pagnumeroob) AS primeiro_ob
FROM par3.empenho e
JOIN par3.pagamento p ON p.empid = e.empid
WHERE p.pagsituacaopagamento ILIKE '%EFETIVADO%'
GROUP BY e.empnumeroprocesso
UNION ALL
SELECT 
	e.empnumeroprocesso,
	sum(p.pagvalorparcela),
	MIN(p.pagnumeroob) AS primeiro_ob
FROM par4.empenho e
JOIN par4.pagamento p ON p.empid = e.empid
WHERE p.pagsituacaopagamento ILIKE '%EFETIVADO%'
GROUP BY e.empnumeroprocesso; --EXEC 14

CREATE INDEX idx_processo_pag_ob ON par.vm_relatorio_pc_pagamento_processo_ob(processo); --EXEC 15

--DROP MATERIALIZED VIEW IF EXISTS par.vm_relatorio_pc_me_situacao_fase CASCADE;

CREATE MATERIALIZED VIEW par.vm_relatorio_pc_me_situacao_fase AS
SELECT DISTINCT
	rs.rspid,
	s.smpcdesc,
	f.fmpcdesc,
	rs.proid_par,
	rs.proid_pac,
	rs.proid_par3,
	NULL AS proid_par4,
	rs.prpid
FROM par.registrosituacaopc rs
JOIN par.situacaomeprestacaocontas s ON rs.smpcid = s.smpcid --AND rs.smpcid IS NOT NULL
JOIN par.fasemeprestacaocontas f ON f.fmpcid = rs.fmpcid --AND rs.fmpcid IS NOT NULL
WHERE rs.rspstatus = 'A'; --EXEC 16

CREATE INDEX idx_proid_pac_sitme ON par.vm_relatorio_pc_me_situacao_fase(proid_pac); --EXEC 17
CREATE INDEX idx_proid_par_sitme ON par.vm_relatorio_pc_me_situacao_fase(proid_par); --EXEC 18
CREATE INDEX idx_prpid_sitme ON par.vm_relatorio_pc_me_situacao_fase(prpid); --EXEC 19
CREATE INDEX idx_proid_par3_sitme ON par.vm_relatorio_pc_me_situacao_fase(proid_par3); --EXEC 20
CREATE INDEX idx_proid_par4_sitme ON par.vm_relatorio_pc_me_situacao_fase(proid_par4); --EXEC 21


--DROP MATERIALIZED VIEW IF EXISTS par.vm_relatorio_pc_relatorio_xlsx CASCADE;

CREATE MATERIALIZED VIEW par.vm_relatorio_pc_relatorio_xlsx AS
SELECT * FROM (
	SELECT DISTINCT 
		vfpc.processo,
		COALESCE(t.ternumero, d.dopnumerodocumento, d_par3.dotnumero, d_par4.dotnumero) AS termo,
		CASE
			WHEN vfpc.tipo = 'PAC'
				THEN 'Programa PAC'
				ELSE COALESCE(m2.mdonome, m3.mdonome, m4.mdonome)
		END AS programa_projeto,
		COALESCE(mun.estuf, est.estuf) AS uf,
		COALESCE(mun.inudescricao, est.inudescricao) AS municipio,
		COALESCE(iemun.entcnpj, ieest.entcnpj) AS cnpj,
		COALESCE(iemun.entnome, ieest.entnome) AS entidade,
		
		TO_CHAR(
    COALESCE(
      COALESCE(t.terdataassinatura::date, t.terdatainclusao::date),
      par.formata_data_termo_inicio(d.dopdatainiciovigencia::text),
      d3.dotdatainiciovigencia::date,
      d4.dotdatainiciovigencia::date
    ), 
    'DD/MM/YYYY'
  ) AS inicio_vigencia,

  TO_CHAR(
    COALESCE(
      par.formata_data_termo_fim(t.terdatafimvigencia::text),
      par.formata_data_termo_fim(d.dopdatafimvigencia::text),
      d3.dotdatafimvigencia::date,
      d4.dotdatafimvigencia::date
    ), 
    'DD/MM/YYYY'
  ) AS fim_vigencia,

		to_char(v_dt.prazo_pc, 'DD/MM/YYYY') AS prazo_pc,
		CASE 
			WHEN v_dt.dt_envio_pc IS NULL AND t2.prpid IS NULL AND t3.proid_pac IS NULL AND t4.proid_par IS NULL AND v_dt.prazo_pc < now()::date
			THEN (now()::date - v_dt.prazo_pc)
			ELSE 0
		END AS dias_atraso,
		CASE
			WHEN t2.prpid IS NOT NULL OR t3.proid_pac IS NOT NULL OR t4.proid_par IS NOT NULL
				THEN 'Enviado via SIGPC'
			ELSE to_char(v_dt.dt_envio_pc, 'DD/MM/YYYY')
		END AS data_recibo,
		COALESCE(pag.total_pag, 0) AS vlr_repassado,
		-- Dados bancários
		COALESCE(propar.prpagencia, proobrapar.proagencia, propac.proagencia, pro3.proagencia, pro4.proagencia) AS agencia,
		COALESCE(propar.nu_conta_corrente, proobrapar.nu_conta_corrente, propac.nu_conta_corrente, pro3.nu_conta_corrente, pro4.nu_conta_corrente) AS conta,
		COALESCE(io.itrid, ip.itrid, ip4.itrid, ip5.itrid) as itrid,
		m.muncod,
		est.estuf,
		
		pag.primeiro_ob AS primeiro_ob,
		vfpc.fase_pc_esdsc AS fase_pc_atual,
		vfpc.situacao_pc_esddsc AS situcao_pc_atual,
		vfpc.situacao_opc_esddsc AS situacao_opc_atual,
		
		CASE
			WHEN COALESCE(efeitosuspensivo.efdstatus, efeitosuspensivo1.efdstatus, efeitosuspensivo2.efdstatus, efeitosuspensivo3.efdstatus) = 'A'
				THEN 'Vigente' 
				ELSE 'Não vigente' 
				END AS ef_suspensivo,				
		COALESCE(vm_s_f_me.fmpcdesc, vm_s_f_me1.fmpcdesc, vm_s_f_me2.fmpcdesc,vm_s_f_me3.fmpcdesc) AS fase_me,
		COALESCE(vm_s_f_me.smpcdesc, vm_s_f_me1.smpcdesc, vm_s_f_me2.smpcdesc, vm_s_f_me3.smpcdesc) AS situacao_me,
		vfpc.tipo AS tipo,
		vfpc.modulo
	FROM par.vm_relatorio_pc_fluxo vfpc
	JOIN par.vm_relatorio_pc_datas_limite AS v_dt ON v_dt.processo = vfpc.processo
	LEFT JOIN par.vm_relatorio_pc_pagamento_processo_ob AS pag ON pag.processo = vfpc.processo
	LEFT JOIN (
		SELECT 
			d.proid,
			d.dotnumero,
			max(dotid) AS dotid 
		FROM par3.documentotermo d
		WHERE d.dotstatus = 'A'
		GROUP BY proid, dotnumero
	) AS d_par3 ON d_par3.dotid = vfpc.dotid_par3
	LEFT JOIN (
		SELECT 
			d.proid,
			d.dotnumero,
			max(dotid) AS dotid 
		FROM par4.documentotermo d
		WHERE d.dotstatus = 'A'
		GROUP BY proid, dotnumero
	) AS d_par4 ON d_par4.dotid = vfpc.dotid_par4
	LEFT JOIN par3.documentotermo d3 ON d3.dotid = d_par3.dotid AND d3.dotstatus = 'A'
	--par3 banco
	LEFT JOIN par3.processo pro3 ON pro3.proid = d3.proid
	LEFT JOIN par4.documentotermo d4 ON d4.dotid = d_par4.dotid AND d4.dotstatus = 'A'
	--par4 banco 
	LEFT JOIN par4.processo pro4 ON pro4.proid = d4.proid
	LEFT JOIN par.vm_relatorio_pc_me_situacao_fase AS vm_s_f_me ON vm_s_f_me.prpid = vfpc.prpid
	LEFT JOIN par.vm_relatorio_pc_me_situacao_fase AS vm_s_f_me1 ON vm_s_f_me1.proid_pac = vfpc.proid_pac 
	LEFT JOIN par.vm_relatorio_pc_me_situacao_fase AS vm_s_f_me2 ON vm_s_f_me2.proid_par = vfpc.proid_par 
	LEFT JOIN par.vm_relatorio_pc_me_situacao_fase AS vm_s_f_me3 ON vm_s_f_me3.proid_par3 = vfpc.proid_par3
	-- efeito susp
	LEFT JOIN par.efeitosuspensivodocumento AS efeitosuspensivo ON efeitosuspensivo.prpid = vfpc.prpid
	LEFT JOIN par.efeitosuspensivodocumento AS efeitosuspensivo1 ON efeitosuspensivo1.proid_pac = vfpc.proid_pac 
	LEFT JOIN par.efeitosuspensivodocumento AS efeitosuspensivo2 ON efeitosuspensivo2.proid = vfpc.proid_par 
	LEFT JOIN par.efeitosuspensivodocumento AS efeitosuspensivo3 ON efeitosuspensivo3.proid_par3 = vfpc.proid_par3
	
	LEFT JOIN par.processoobra p ON p.proid = vfpc.proid_pac
	LEFT JOIN par.processoobraspar p2 ON p2.proid = vfpc.proid_par
	LEFT JOIN par.instrumentounidade io ON io.inuid = p2.inuid
	LEFT JOIN par.processopar p3 ON p3.prpid = vfpc.prpid
	LEFT JOIN par.instrumentounidade ip ON ip.inuid = p3.inuid
	LEFT JOIN par3.processo p4 ON p4.proid = vfpc.proid_par3
	LEFT JOIN par3.instrumentounidade ip4 ON ip4.inuid = p4.inuid
	LEFT JOIN par4.processo p5 ON p5.proid = vfpc.proid_par4
	LEFT JOIN par4.instrumentounidade ip5 ON ip5.inuid = p5.inuid
	
	LEFT JOIN par.termocompromissopac t ON t.terid = vfpc.terid 
	-- pac banco
	LEFT JOIN par.processoobra propac ON t.proid = propac.proid AND propac.prostatus = 'A'

	LEFT JOIN par.documentopar d ON d.dopid = vfpc.dopid AND d.dopid IN (
		SELECT dopid FROM (
				SELECT 
					d.prpid,
					d.dopnumerodocumento,
					max(dopid) AS dopid
				FROM par.documentopar d
				WHERE d.dopstatus  = 'A'
				GROUP BY prpid, d.dopnumerodocumento
		) lastdopid
	)
	-- banco obra par 
	LEFT JOIN par.processoobraspar proobrapar ON d.proid = proobrapar.proid AND proobrapar.prostatus = 'A'
	-- banco par
    LEFT JOIN par.processopar propar ON d.prpid = propar.prpid AND propar.prpstatus = 'A'
	LEFT JOIN territorios.municipio m ON m.muncod = COALESCE(t.muncod, ip.muncod, io.muncod, ip4.muncod, ip5.muncod)
	LEFT JOIN territorios.estado e ON e.estuf = COALESCE(t.estuf, ip.estuf, io.estuf, ip4.estuf, ip5.estuf)
	LEFT JOIN par4.instrumentounidade mun ON mun.muncod = m.muncod AND mun.itrid = 2
	LEFT JOIN par4.instrumentounidade est ON est.estuf = e.estuf AND est.itrid = 1
	LEFT JOIN par4.instrumentounidade_entidade iemun ON iemun.inuid = mun.inuid 
		AND iemun.tenid = 1 
		AND iemun.entstatus = 'A' 
		AND iemun.entrazaosocial IS NOT NULL
		AND iemun.entcnpj IN(p.procnpj, p2.procnpj, p3.prpcnpj, p4.procnpj, p5.procnpj)
	LEFT JOIN par4.instrumentounidade_entidade ieest ON ieest.inuid = est.inuid 
		AND ieest.tenid = 3 
		AND ieest.entstatus = 'A' 
		AND ieest.entrazaosocial IS NOT NULL
		AND ieest.entcnpj IN(p.procnpj, p2.procnpj, p3.prpcnpj, p4.procnpj, p5.procnpj)
	LEFT JOIN par.termossigpcpar t2 ON t2.prpid = vfpc.prpid
	LEFT JOIN par.termossigpcpar t3 ON t3.proid_pac = vfpc.proid_pac
	LEFT JOIN par.termossigpcpar t4 ON t4.proid_par = vfpc.proid_par
	LEFT JOIN par.modelosdocumentos m2 ON m2.mdoid = d.mdoid and m2.tpdcod in (16, 21, 102, 103)
	LEFT JOIN par3.modelodocumento m3 ON m3.mdoid = d3.mdoid
	LEFT JOIN par4.modelodocumento m4 ON m4.mdoid = d4.mdoid
	wHERE 1 = 1 
	--AND vfpc.tipo = 'OBRA' AND vfpc.modulo = 'PAR3' 
--	AND vfpc.processo = '23400004475201391'
	--ORDER BY vfpc.modulo, vfpc.tipo;
) AS foo WHERE termo IS NOT NULL AND programa_projeto IS NOT NULL; --EXEC 22


--query para trazer a planilha que joão batista pediu de Prestação de Contas
select distinct 
	to_char(processo::bigint, 'FM00000"."000000"/"0000"-"00') as "N° do processo",
	--termo as "Termo de Compromisso", 
	vrprx.uf as "UF", 
	municipio as "Município",
	'' as "Origem dos recursos",
	COALESCE(REPLACE(TO_CHAR(vlr_repassado, 'FM999999990.00'), '.', ','), '0,00') as "Valor total do recurso",
	prazo_pc as "Data limite prestação de contas",
	data_recibo as "Data efetiva prestação de contas",
	primeiro_ob as "Ordem Bancária",
	CASE 
	    WHEN vrprx.itrid = 2 
	    THEN 
	    	SUBSTRING(rrp.rrpcnpj FROM 1 FOR 2) || '.' ||
            SUBSTRING(rrp.rrpcnpj FROM 3 FOR 3) || '.' ||
            SUBSTRING(rrp.rrpcnpj FROM 6 FOR 3) || '/' ||
            SUBSTRING(rrp.rrpcnpj FROM 9 FOR 4) || '-' ||
            SUBSTRING(rrp.rrpcnpj FROM 13 FOR 2)
	    ELSE 
	    	SUBSTRING(rrs.rrscnpj FROM 1 FOR 2) || '.' ||
            SUBSTRING(rrs.rrscnpj FROM 3 FOR 3) || '.' ||
            SUBSTRING(rrs.rrscnpj FROM 6 FOR 3) || '/' ||
            SUBSTRING(rrs.rrscnpj FROM 9 FOR 4) || '-' ||
            SUBSTRING(rrs.rrscnpj FROM 13 FOR 2)
	END as "CPF/CNPJ do beneficiário",
	CASE 
        WHEN vrprx.itrid = 2 THEN (
            SELECT STRING_AGG(rpref.usucpf, ', ')
            FROM par.rollresponsaveisprefeitos rpref
            WHERE rpref.muncod = vrprx.muncod
            	limit 1
        )
        ELSE (
            SELECT STRING_AGG(rsec.usucpf, ', ')
            FROM par.rollresponsaveissecretarios rsec
            WHERE rsec.uf = vrprx.estuf
            	limit 1
        )
    END as "CPF/CNPJ dos Responsáveis",
	'' as "Estágio do Processo",
	substr(processo, 12, 4) AS"Ano do Processo",
	termo as "Identificação do Repasse",
	fase_pc_atual,
	situcao_pc_atual as "situacao_pc_atual",
	situacao_opc_atual,
	fase_me,
	situacao_me,
	CASE 	
		WHEN 
			(  
				SELECT COUNT(1)
				FROM par.termossigpcpar tsp
				LEFT JOIN par.processoobra po ON po.proid = tsp.proid_pac
				LEFT JOIN par.processoobraspar pop ON pop.proid = tsp.proid_par
				LEFT JOIN par.processopar pp ON pp.prpid = tsp.prpid
				WHERE COALESCE(po.pronumeroprocesso, pop.pronumeroprocesso, pp.prpnumeroprocesso) = processo
				LIMIT 1
			) > 0 
        THEN
			'Enviado via SIGPC'
        ELSE
        	data_recibo
        END as "Dt Recibo Envio"
from par.vm_relatorio_pc_relatorio_xlsx vrprx
left join par.rollresponsaveisprefeitos rrp on rrp.muncod = vrprx.muncod and vrprx.itrid = 2
left join par.rollresponsaveissecretarios rrs on rrs.uf = vrprx.estuf  and vrprx.itrid = 1;