extracao_par2_script.sql

select
		CASE WHEN iumuncod IS NULL THEN estestuf ELSE munestuf END AS "UF",
		foo.munmundescricao as "Entidade",
		--prpnumeroprocesso as "Processo",
		to_char(prpnumeroprocesso::bigint, 'FM00000"."000000"/"0000"-"00') as "Processo",
		foo.doc as "Documento",
		data_vigencia as "Vigência",
		contagem as "Validação",
		(SELECT tisub.tpsdescricao
            FROM par.propostasubacao pro
       		INNER JOIN par.propostatiposubacao prot on pro.ptsid = prot.ptsid AND prot.ptsstatus = 'A'
       		INNER JOIN par.tiposubacao tisub ON tisub.tpsid = prot.tpsid and tisub.tpsstatus = 'A'
            where pro.ptsid = sub.ptsid limit 1) as "Tipo",
		par.retornacodigosubacao(sd.sbaid) as "Código da Subação",
		sub.sbadsc as "Subação",
		sd.sbdano as "Ano",
		COALESCE(REPLACE(TO_CHAR(sd.sbdvaloraprovado, 'FM999999990.00'), '.', ','), '0,00') as "Valor da Subação",
		COALESCE(REPLACE(TO_CHAR(valorempenho, 'FM999999990.00'), '.', ','), '0,00') as "Valor Empenhado",
		COALESCE(REPLACE(TO_CHAR(valorpagamento, 'FM999999990.00'), '.', ','), '0,00') as "Valor Pago",
		case
			when
				pagsituacaopagamento = 'A'
             THEN
                'Sim'   
			else 
                'Não'
		end as "Situação Pagamento",
		CASE WHEN estestuf IS NULL THEN 'Municipal' ELSE 'Estadual' END as "Esfera",
		sub.sbaemenda as "Subação de Emenda",
		CASE 
		    WHEN sub.sbastatus = 'A' THEN 'Ativo'
		    WHEN sub.sbastatus = 'I' THEN 'Inativo'
		    ELSE 'Cópia'
		END AS "Status da Subação",
		(SELECT SUM(qtdtermo) as total_qtdtermo
			FROM (
			SELECT  distinct 
				si.icoid,
				(
                    CASE WHEN sbacronograma = 1
    				    THEN (
        					SELECT DISTINCT
    							CASE WHEN sic.icovalidatecnico = 'S' THEN sum(coalesce(sic.icoquantidadetecnico,0))  END as vlrsubacao
    						FROM par.subacaoitenscomposicao sic
    						WHERE
    							sic.sbaid = s.sbaid
    							AND sic.icoano = sd.sbdano
    							AND sic.icoid = si.icoid
    											AND sic.icostatus = 'A'
    						GROUP BY sic.sbaid, sic.icovalidatecnico
    					)
    					ELSE (
    						SELECT DISTINCT
    				            CASE WHEN (s.frmid = 2) OR ( s.frmid = 4 AND s.ptsid = 42 ) OR ( s.frmid = 12 AND s.ptsid = 46 )
    								THEN -- escolas sem itens
    										sum(coalesce(se.sesquantidadetecnico,0))
    								ELSE -- escolas com itens
    									CASE WHEN sic.icovalidatecnico = 'S' THEN -- validado (caso não o item não é contado)
    											sum(coalesce(ssi.seiqtdtecnico,0))
    									END
    							END as vlrsubacao
    						FROM entidade.entidade t
    						inner join entidade.funcaoentidade f on f.entid = t.entid
    						left join entidade.entidadedetalhe ed on t.entid = ed.entid
    						inner join entidade.endereco d on t.entid = d.entid
    						left join territorios.municipio m on m.muncod = d.muncod
    						left join par.escolas e on e.entid = t.entid
    						INNER JOIN par.subacaoescolas se ON se.escid = e.escid
    						INNER JOIN par.subacaoitenscomposicao sic on se.sbaid = sic.sbaid AND se.sesano = sic.icoano AND sic.icostatus = 'A'
    						LEFT JOIN  par.subescolas_subitenscomposicao ssi ON ssi.sesid = se.sesid AND ssi.icoid = sic.icoid
    						WHERE
    							sic.sbaid = s.sbaid AND sic.icoano = sd.sbdano
    							AND sic.icoid = si.icoid
    							AND (t.entescolanova = false OR t.entescolanova is null) AND t.entstatus = 'A' AND f.funid = 3
    						GROUP BY sic.sbaid, se.sesvalidatecnico, sic.icovalidatecnico 
                        )
                    END 
                ) as qtdtermo
			FROM par.subacao s
			/*Para recuperar o itrid - ver se é estadual ou municipal*/
			INNER JOIN par.acao 				aca on aca.aciid = s.aciid AND acistatus = 'A'
			INNER JOIN par.pontuacao 			pon on pon.ptoid = aca.ptoid AND ptostatus = 'A'
			INNER JOIN par.instrumentounidade 	inu on inu.inuid = pon.inuid
			/*FIM - Para recuperar o itrid*/
			INNER JOIN par.subacaodetalhe 					sd  ON sd.sbaid = s.sbaid
			LEFT JOIN par.documentoparreprogramacaosubacao 	dps
			INNER JOIN par.vm_documentopar_ativos 	dp  ON dp.dopid = dps.dopid ON dps.sbdid = sd.sbdid AND dps.dpsstatus = 'A'
			INNER JOIN par.termocomposicao 					tc  ON tc.sbdid = sd.sbdid
			INNER JOIN par.subacaoitenscomposicao si  ON si.sbaid = s.sbaid AND si.icoano = sd.sbdano AND si.icostatus = 'A'
			LEFT JOIN par.subacaoitenscomposicaocontratos con  ON con.icoid = si.icoid AND con.sccid IS NOT NULL
			INNER JOIN par.propostaitemcomposicao 			pic ON pic.picid = si.picid
			LEFT  JOIN par.empenhopregaoitensenviados 		emi ON emi.idsigarp = pic.idsigarp AND emi.sbaid = si.sbaid --AND emi.prpid = {$prpid}  
			AND emi.epistatus = 'A'
			WHERE sd.sbaid = sub.sbaid
			    AND icomonitoramentocancelado = false
				AND si.icovalidatecnico = 'S'
				) AS subquery)
		as "Quantidade de Itens Planejados", 
        (select sum(su.icoquantidaderecebida)
              FROM par.subacaoitenscomposicao su
              INNER JOIN par.subacao s ON s.sbaid = su.sbaid AND s.sbastatus = 'A' and su.icostatus = 'A'
              	where s.sbaid = sub.sbaid) 
        as "Quantidade de Itens Validados", 
		iuesituacaohabilita as "Habilitação",
		case
			when
				(SELECT count(1)
                    FROM par.subacao s
                    INNER JOIN par.subacaoitenscomposicao sic on s.sbaid = sic.sbaid AND s.sbastatus = 'A'
       				INNER JOIN par.subacaoitenscomposicaocontratos sicc ON sicc.icoid = sic.icoid
                    where s.sbaid = sub.sbaid) > 0
             THEN
                'Sim'   
			else 
                'Não'
		end
		as "Contrato Inserido",
		case
			when
				(SELECT count(1)
                    FROM par.subacao s
                    INNER JOIN par.subacaoitenscomposicao sic on s.sbaid = sic.sbaid AND s.sbastatus = 'A'
       				INNER JOIN par.subacaoitenscomposicaonotasfiscais sicnf ON sicnf.icoid = sic.icoid
                    where s.sbaid = sub.sbaid) > 0
             THEN
                'Sim'   
			else 
                'Não'
		end
		as "Nota Fiscal Inserida",
		(SELECT count(*)
               FROM par.subacao s
               INNER JOIN par.subacaoescolas se on se.sbaid = sd.sbaid AND s.sbastatus = 'A'
               where s.sbaid = sub.sbaid)
		as "Qtd de Escolas Atendidas",
		TO_CHAR(foopc.enviopc, 'DD/MM/YYYY') as "Data Envio PC",
		CASE WHEN EXISTS ( SELECT tspid FROM par.termossigpcpar where prpid = foo.prpid ) THEN
						'SIGPC'
                    WHEN
                        (SELECT d.esdid 
                        	FROM par.documentoparprestacaodecontas pc
                            --INNER JOIN par.comprovanteprestacaocontas cpc ON cpc.prpid = pc.prpid
                            INNER JOIN workflow.documento d ON pc.docid = d.docid WHERE pc.prpid = foo.prpid) <> 2005 THEN
                        'Enviada'
				      WHEN
                        (SELECT
                            count(1)
                            FROM par.termocomposicao tc
                            INNER JOIN par.subacaodetalhe sd ON sd.sbdid = tc.sbdid
                            INNER JOIN par.subacao s ON s.sbaid = sd.sbaid AND s.sbastatus = 'A'
                            where dopid = id
                            and EXISTS (
                            	SELECT 1 FROM par.programaformacao pf 
                            	WHERE pf.prgid = s.prgid 
    							AND pf.frmstatus = 'A')) > 0 
                       THEN
                        'Não Enviada'   
				    /* FIM Formação*/
                    ELSE
                        CASE  WHEN coalesce(valorpagamento, 0) <= 0 THEN
                            cast(estado_documentopc as text)
                        WHEN vigencia_termo_pc > '2018-07-02' THEN
                            CASE WHEN(vigencia_termo_pc + '60 day':: INTERVAL) :: DATE - current_date < 0 THEN
                              CASE WHEN id_opc = 2298 THEN
                                    cast(estado_documentopc as text) || ' - ' || cast(estado_opc as text)
                                ELSE
                                    cast(estado_documentopc as text)
                                END
                            ELSE
                                CASE WHEN(vigencia_termo_pc + '60 day':: INTERVAL) :: DATE - current_date < 60 THEN
                                  'Restam ' || cast(
                                      ((vigencia_termo_pc + '60 day':: INTERVAL):: DATE -
                                      current_date)  AS TEXT)
                                  || ' dias para envio da PC'
                                ELSE
                                  cast(estado_documentopc as text)
                                END
                            END
                        ELSE
                            CASE WHEN date '2018-07-02' + INTEGER '60' - current_date < 0 THEN
                                  CASE WHEN id_opc = 2298 THEN
                                    cast(estado_documentopc as text) || ' - ' || cast(estado_opc as text)
                                  ELSE
                                    cast(estado_documentopc as text)
                                  END
                            ELSE
                                CASE WHEN date '2018-07-02' + INTEGER '60' - current_date < 60 THEN
                                  'Restam ' || cast(
                                      (date '2018-07-02' + INTEGER '60' - current_date) AS TEXT)
                                  || ' dias para envio da PC'
                                ELSE
                                  cast(estado_documentopc as text)
                                END
                            END
                        END
                  END
                 as "Situação Envio PC",
                 foopc.fasepc as "Fase Análise PC",
                 (
                 	case                  		
                 		when foopc.valorrepassado > 0 then foopc.situacaopc
                 		else 'Sem valor a comprovar'                 		 
             		end
                 ) as "Situação Análise PC",
        foopc.paracercacs as "Conclusão PC",
        COALESCE(REPLACE(par.retornasaldoprocesso(prpnumeroprocesso), '.', ','), '0,00') as "Saldo CC"
                
		 FROM (
			SELECT
				dp.dopid as id,
                dp.prpid,
                dp.dopstatus,
				d.tpdcod as tipo_doc,
                (
                    SELECT
                        to_char(vigencia, 'MM/YYYY') AS vigencia
                    FROM (
                        SELECT
                            TO_date(dopdatafimvigencia, 'MM/YYYY') as vigencia,
                            CASE
                                WHEN (dpv.dpvid IS NOT NULL AND d.dopstatus = 'A') THEN 0
                                WHEN (dpv.dpvid IS NULL AND d.dopstatus = 'A' AND d.mdoid IN (69,82,81,41,80,68,42,67,65,76,79,74,44,78,56,62,52,71,66,73,75,77)) THEN 1
                                WHEN (dpv.dpvid IS NULL AND d.dopstatus = 'I' AND d.mdoid IN (69,82,81,41,80,68,42,67,65,76,79,74,44,78,56,62,52,71,66,73,75,77)) THEN 2
                                WHEN (dpv.dpvid IS NOT NULL AND d.dopstatus = 'I') THEN 3
                                ELSE 4
                            END AS prioridade
                        FROM par.documentopar d
                        LEFT JOIN par.documentoparvalidacao dpv ON d.dopid = dpv.dopid AND dpv.dpvstatus = 'A'
                        WHERE d.prpid = pp.prpid AND d.dopstatus IN ('A', 'I') AND dopdatafimvigencia IS NOT NULL
                        ORDER BY d.dopid DESC, prioridade
                    ) AS foo LIMIT 1
                ) as data_vigencia,
				mdonome as tipodocumento,
				iu.muncod as iumuncod,
				est.estuf as estestuf,
				mun.estuf as munestuf,
				mun.mundescricao as munmundescricao,
				dp.dopnumerodocumento as doc,
				pp.prpnumeroprocesso,
                (SELECT count(dopid) FROM par.documentoparvalidacao WHERE dopid = dp.dopid AND dpvstatus = 'A' ) as contagem,
				(select sum(vrlempenhocancelado) from par.v_vrlempenhocancelado where processo = pp.prpnumeroprocesso )
						as valorempenho,
				pm.valor_pagamento as valorpagamento,
				pm.pagstatus as pagsituacaopagamento,
				dp.dopdatafimvigencia,
				epc.esddsc as estado_documentopc,
                dopc.esdid as id_opc,
                eopc.esddsc as estado_opc,
                (date_trunc('month', TO_date(dp.dopdatafimvigencia, 'MM/YYYY')) + '1 month' - '1 day' :: INTERVAL) as vigencia_termo_pc
			FROM
				par.documentopar dp
			
			INNER JOIN par.modelosdocumentos   d ON d.mdoid = dp.mdoid 
			INNER JOIN par.processopar pp ON pp.prpid = dp.prpid
			LEFT JOIN par.situacaoprestacaocontas pc ON pp.prpid = pc.prpid
            LEFT JOIN workflow.documento dpc ON dpc.docid = pc.docid
            LEFT JOIN workflow.estadodocumento epc ON dpc.esdid = epc.esdid
            LEFT JOIN par.situacaoopc opc ON pp.prpid = opc.prpid
            LEFT JOIN workflow.documento dopc ON opc.docid = dopc.docid
            LEFT JOIN workflow.estadodocumento eopc ON dopc.esdid = eopc.esdid
			INNER JOIN par.instrumentounidade iu ON iu.inuid = pp.inuid
		 	LEFT JOIN territorios.municipio mun on mun.muncod = iu.muncod
         	LEFT JOIN territorios.estado est on est.estuf = iu.estuf
      	
			INNER JOIN ( select
							d.dopid,
							sum(vve.vrlempenhocancelado) + sum(coalesce(emr.vrlreforco,0)) as valor
						from
							par.documentopar d
						inner join par.processopar prp on prp.prpid = d.prpid
						inner join par.empenho emp on emp.empnumeroprocesso = prp.prpnumeroprocesso and empcodigoespecie not in ('03', '13', '02', '04') and empstatus = 'A'
						inner join par.v_vrlempenhocancelado vve on vve.empid = emp.empid
						left join (select empnumeroprocesso, empidpai, sum(empvalorempenho) as vrlreforco, empcodigoespecie
									from par.empenho
									where empcodigoespecie in ('02') and empstatus = 'A'
									group by
										empnumeroprocesso,
										empcodigoespecie,
										empidpai) as emr on emr.empidpai = emp.empid
						inner join par.empenhosubacao ems on ems.empid = emp.empid AND eobstatus = 'A' where d.dopstatus = 'A' group by d.dopid 
			) em ON em.dopid = dp.dopid
			LEFT JOIN(
				   Select em.empnumeroprocesso as empnumeroprocesso,
					  pagstatus,
					  sum(pg.pagvalorparcela) as valor_pagamento
				   From par.pagamento pg
				   join par.empenho em on em.empid = pg.empid and empstatus = 'A' AND pg.pagstatus = 'A'
				   where trim(pg.pagsituacaopagamento) ilike '%EFETIVADO%'
				   group by empnumeroprocesso, pagstatus
				) as pm on pm.empnumeroprocesso = pp.prpnumeroprocesso and pm.pagstatus='A'
		    LEFT JOIN (select
		                    d.dopid, sum( pobvalorpagamento ) as valor_pagamento
		                from
		                    par.documentopar d
		                inner join par.processopar 		prp on prp.prpid = d.prpid
		                inner join par.empenho 			emp on emp.empnumeroprocesso = prp.prpnumeroprocesso and empcodigoespecie not in ('03', '13', '02', '04') and empstatus = 'A'
		                inner join par.pagamento 		pag on pag.empid = emp.empid AND pag.pagstatus = 'A' AND pag.pagsituacaopagamento in ('8 - SOLICITAÇÃO APROVADA', 'SOLICITAÇÃO APROVADA', 'ENVIADO AO SIAFI', '0 - AUTORIZADO', 'AUTORIZADO', 'Enviado ao SIGEF')
		                inner join par.pagamentosubacao ps  on ps.pagid = pag.pagid and pobstatus = 'A'
                        where d.dopstatus = 'A' group by d.dopid, pagsituacaopagamento) pgs ON pgs.dopid = dp.dopid
		) as foo
		
		--- sub ação
            LEFT JOIN par.termocomposicao 	tc  ON foo.id = tc.dopid 
            LEFT JOIN par.subacaodetalhe 	sd  ON tc.sbdid = sd.sbdid 
            LEFT JOIN par.processoparcomposicao ppc ON ppc.sbdid = sd.sbdid 
            LEFT join par.subacao sub ON sd.sbaid = sub.sbaid 
      	---
		left join (
			select prpid,
                        prpnumeroprocesso as processo,
                        substr(prpnumeroprocesso, 12, 4) AS ano,
                        doc as termo,
                        tipodocumento,
                        iniciovigencia,
                        ((date_trunc('MONTH', TO_date(data_vigencia, 'MM/YYYY')) + INTERVAL '1 MONTH - 1 day') :: DATE) as fimvigencia,
                        valorpagamento as valorrepassado,
                        cpcdtenvio:: DATE as enviopc,
                        fasepc,
                        situacaopc,
                        muncod,
                        estuf,
                        paracercacs,
                        iuesituacaohabilita,
                        foobar.id as ddopid
                     FROM (
                        SELECT
                            dp.dopid as id,
                            dp.prpid,
                            dp.dopstatus,
                            d.tpdcod as tipo_doc,
                            (
                                SELECT
                                    to_char(vigencia, 'MM/YYYY') AS vigencia
                                FROM (
                                    SELECT
                                        TO_date(dopdatafimvigencia, 'MM/YYYY') as vigencia,
                                        CASE
                                            WHEN (dpv.dpvid IS NOT NULL AND d.dopstatus = 'A') THEN 0
                                            WHEN (dpv.dpvid IS NULL AND d.dopstatus = 'A' AND d.mdoid IN (69,82,81,41,80,68,42,67,65,76,79,74,44,78,56,62,52,71,66,73,75,77)) THEN 1
                                            WHEN (dpv.dpvid IS NULL AND d.dopstatus = 'I' AND d.mdoid IN (69,82,81,41,80,68,42,67,65,76,79,74,44,78,56,62,52,71,66,73,75,77)) THEN 2
                                            WHEN (dpv.dpvid IS NOT NULL AND d.dopstatus = 'I') THEN 3
                                            ELSE 4
                                        END AS prioridade
                                    FROM par.documentopar d
                                    LEFT JOIN par.documentoparvalidacao dpv ON d.dopid = dpv.dopid AND dpv.dpvstatus = 'A'
                                    WHERE d.prpid = pp.prpid AND d.dopstatus IN ('A', 'I')
                                    ORDER BY d.dopid DESC, prioridade
                                ) AS foo LIMIT 1
                            ) as data_vigencia,
                            mdonome as tipodocumento,
                            dp.dopnumerodocumento as doc,
                            pp.prpnumeroprocesso,
                            pm.valor_pagamento as valorpagamento,
                            dp.dopdatafimvigencia,
                            cpc.cpcdtenvio,
                            ((date_trunc('MONTH', TO_date(dp.dopdatainiciovigencia, 'MM/YYYY'))) :: DATE) as iniciovigencia,
                            fed.esddsc as fasepc,
                            sed.esddsc as situacaopc,
                            iu.muncod,
                            iu.estuf,
                            CASE WHEN tpc.tipid NOTNULL THEN
                              tpc.tipdescricao
                            ELSE
                              '-'
                            END paracercacs,
                            iue.iuesituacaohabilita as iuesituacaohabilita
                        FROM
                            par.documentopar dp
                        LEFT JOIN par.declaracaoinformacoesfinanceiras dif ON dp.prpid = dif.prpid AND dif.difstatus = 'A'
                        LEFT JOIN par.comprovanteprestacaocontas cpc ON dp.prpid = cpc.prpid AND cpc.cpcstatus = 'A'
                        INNER JOIN par.modelosdocumentos   d ON d.mdoid = dp.mdoid
                        INNER JOIN par.processopar pp ON pp.prpid = dp.prpid
                        INNER JOIN par.documentoparprestacaodecontas dpc ON dpc.prpid = pp.prpid
                        LEFT JOIN par.parecercacspc pc ON dpc.dppid = pc.dppid
                        LEFT JOIN par.tipoparecerpc tpc ON pc.tipid = tpc.tipid
                        INNER JOIN workflow.documento df ON df.docid = dpc.docid
                        INNER JOIN workflow.estadodocumento fed ON df.esdid = fed.esdid
                        INNER JOIN par.situacaoprestacaocontas spc ON spc.prpid = pp.prpid
                        INNER JOIN workflow.documento dspc ON dspc.docid = spc.docid
                        INNER JOIN workflow.estadodocumento sed ON dspc.esdid = sed.esdid
                        INNER JOIN par.instrumentounidade iu ON iu.inuid = pp.inuid
                        LEFT JOIN par.instrumentounidadeentidade iue ON iue.inuid = pp.inuid and iue.iuestatus = 'A' and iue.iuedefault = true
                        LEFT JOIN(
                               Select em.empnumeroprocesso as empnumeroprocesso,
                                  pagstatus,
                                  sum(pg.pagvalorparcela) as valor_pagamento
                               From par.pagamento pg
                               join par.empenho em on em.empid = pg.empid and empstatus = 'A' AND pg.pagstatus = 'A'
                               where trim(pg.pagsituacaopagamento) ilike '%EFETIVADO%'
                               group by empnumeroprocesso, pagstatus
                            ) as pm on pm.empnumeroprocesso = pp.prpnumeroprocesso and pm.pagstatus='A'
                    ) as foobar
                    where dopstatus = 'A'  AND id IS NOT NULL
                    AND tipo_doc in (102, 21, 16 )
		) as foopc on foopc.ddopid = foo.id
		                                  
		where dopstatus = 'A'             
		--AND iuinuid = 2561                
		AND id IS NOT NULL                
                                        
		--@todo confirmar com thiago      
		AND tipo_doc in (102, 21, 16,105 )
		--and foo.prpnumeroprocesso = '23400004847201460'
		--and iumuncod = 'CE'
		--limit 100;