<?php
class Relatorio_Model_Corretor extends Agro_Db_Table_Relatorios {

	protected $_schema = 'sinistro';
	protected $_name = 'avisos';
	
	public function getParcelasPropostas($id_proposta){
	    $sql = "select fl_pago, dt_pagamento, vl_total, vl_segurado, vl_subvencao_federal, vl_subvencao_estadual, to_char(dt_vencimento,'DD/MM/YYYY') as dt_vencimento from seguro.propostas_parcelas where id_proposta=".$id_proposta." order by nr_parcela asc";
	    return $this->_db->fetchAll($sql);
	}
	
    public function relatorioInicioColheita($id_safra="", $arProduto="", $id_usuario="", $tp_usuario=""){
		
		$filtro  = ($id_safra) ? "pd.id_safra=".$id_safra." and " : '';
				
		if(is_array($arProduto)){
			$filtro .= "pd.id in (".implode("," , $arProduto).") and ";
		}
		
		if($tp_usuario == 'C'){
			$filtro .= "pc.id_usuario = ".$id_usuario." and ";
		}
		if($tp_usuario == 'P'){
			$filtro .= "pr.id_usuario_criacao = ".$id_usuario." and ";
		}
	
		$sql = "
				select
					pr.id as proposta,
					pr.nr_apolice as apolice,
					c.ds_nome_cultura as cultura,
					pd.ds_nome_produto,
					pp.ds_nome_proponente,
					proc.id as processo,
					e.ds_nome_fantasia as empresa,
					iseg.nr_item_segurado as item,
					iseg.ds_item_segurado as quadra,
					to_char(ae.dt_inicio_colheita, 'dd/mm/yyyy') as data_inicio,
					to_char(ae.dt_criacao, 'dd/mm/yyyy') as data_envio_inicio,
					v.ds_nome_variedade,
				        CASE
					    WHEN u.tp_usuario = 'P' THEN u.ds_nome_usuario
					    ELSE ''
				        END as preposto,
				    u2.ds_nome_usuario as digitador
				from
					seguro.propostas pr,
					seguro.propostas_proponentes pp,
					produto.produtos pd,
					produto.produtos_geral pdg,
					produto.culturas c,
					seguro.propostas_corretores pc,
					sistema.corretores cor,
					sinistro.processos proc,
					sinistro.processos_empresas proce,
					sistema.empresas e,
					seguro.itens_segurados iseg,
					sinistro.avisos_inicio ae,
					sinistro.avisos_inicio_itens_segurados aeis,
					produto.variedades v,
					sistema.usuarios u,
					sistema.usuarios u2
				where
					".$filtro."
					pd.id = pr.id_produto and
					pp.id_proposta = pr.id and
					pdg.id = pd.id_produto_geral and
					c.id = pdg.id_cultura and
					pc.id_proposta = pr.id and
					cor.id_usuario = pc.id_usuario and
					proc.id_proposta = pr.id and
					proce.id_processo = proc.id and
					proce.fl_del = false and
					proce.fl_ativo = true and
					e.id = proce.id_empresa and
					iseg.id_proposta = pr.id and
					aeis.id_item_segurado = iseg.id and
					aeis.fl_del = false and
					aeis.fl_ativo = true and
					ae.id = aeis.id_aviso_inicio and
					v.id = iseg.id_variedade and
					u.id = pr.id_usuario_criacao and
					u2.id = ae.id_usuario_criacao
				order by
					pr.id,
					iseg.nr_item_segurado
				";
		return $this->fetchAll($sql);
	}
	
    public function relatorioEncerramentoColheita($id_safra="", $arProduto="", $id_usuario="", $tp_usuario=""){
		
		$filtro  = ($id_safra) ? "pd.id_safra=".$id_safra." and " : '';
				
		if(is_array($arProduto)){
			$filtro .= "pd.id in (".implode("," , $arProduto).") and ";
		}
		
		if($tp_usuario == 'C'){
			$filtro .= "pc.id_usuario = ".$id_usuario." and ";
		}
		if($tp_usuario == 'P'){
			$filtro .= "pr.id_usuario_criacao = ".$id_usuario." and ";
		}
	
		$sql = "
				select
					pr.id_proposta_mae as proposta,
					pr.nr_apolice as apolice,
					c.ds_nome_cultura as cultura,
					pd.ds_nome_produto,
					pp.ds_nome_proponente,
					proc.id as processo,
					e.ds_nome_fantasia as empresa,
					iseg.nr_item_segurado as item,
					iseg.ds_item_segurado as quadra,
					CASE
					    WHEN aeis.id is not null THEN to_char(ae.dt_encerramento_colheita, 'dd/mm/yyyy')
					    ELSE ''
					END as data_encerramento,
					CASE
					    WHEN aeis.id is not null THEN to_char(ae.dt_criacao, 'dd/mm/yyyy')
					    ELSE ''
					END as data_envio_encerramento,
					v.ds_nome_variedade,
					CASE
					    WHEN u.tp_usuario = 'P' THEN u.ds_nome_usuario
					    ELSE ''
					END as preposto,
				        CASE
					    WHEN aeis.id is not null THEN u2.ds_nome_usuario
					    ELSE ''
					END as digitador
				from
				    seguro.propostas_endosso pe,
					seguro.propostas pr
					inner join seguro.itens_segurados iseg on
					    iseg.id_proposta = pr.id
					left join sinistro.avisos_encerramento ae on
					    ae.id_proposta = pr.id
					left join sinistro.avisos_encerramento_itens_segurados aeis on
					    ae.id = aeis.id_aviso_encerramento and
					    aeis.id_item_segurado = iseg.id and
					    aeis.fl_del = false and
					    aeis.fl_ativo = true
					left join produto.variedades v on
					    v.id = iseg.id_variedade
					left join sistema.usuarios u2 on
					    u2.id = ae.id_usuario_criacao,
					seguro.propostas_proponentes pp,
					produto.produtos pd,
					produto.produtos_geral pdg,
					produto.culturas c,
					seguro.propostas_corretores pc,
					sistema.corretores cor,
					sinistro.processos proc,
					sinistro.processos_empresas proce,
					sistema.empresas e,
					sistema.usuarios u
				where
				    ".$filtro."
				    pe.id_proposta = pr.id and
				    pe.fl_proposta_vigente = true and
					pd.id = pr.id_produto and
					pp.id_proposta = pr.id and
					pdg.id = pd.id_produto_geral and
					c.id = pdg.id_cultura and
					pc.id_proposta = pr.id and
					cor.id_usuario = pc.id_usuario and
					proc.id_proposta = pr.id and
					proce.id_processo = proc.id and
					proce.fl_del = false and
					proce.fl_ativo = true and
					e.id = proce.id_empresa and
					u.id = pr.id_usuario_criacao
				order by
					pr.id,
					iseg.nr_item_segurado
				";
		return $this->fetchAll($sql);
	}
	
	public function relatorioParcelas($id_safra="", $arProduto="", $id_usuario="", $tp_usuario="", $fl_cancelados=false){
	
	    $filtro  = ($id_safra)   ? "pd.id_safra=".$id_safra." and " : '';
	    
	    if(is_array($arProduto)){
	        $filtro .= "pd.id in (".implode("," , $arProduto).") and ";
	    }
	    
	    if($tp_usuario == 'C'){
	        $filtro .= "pc.id_usuario = ".$id_usuario." and ";
	    }
	    
	    if($tp_usuario == 'P'){
	        $filtro .= "pr.id_usuario_criacao = ".$id_usuario." and ";
	    }
	
	    if($fl_cancelados == true){
	    	$status = "st.ds_chave in ('PROPOSTA_CANCELADA', 'ENDOSSO_CANCELADO', 'ENDOSSO_ANULADO', 'APOLICE_CANCELADA')";
	    } else {
	    	$status = "st.ds_chave not in ('PROPOSTA_INCOMPLETA' , 'NAO_ENVIADA' , 'DEVOLVIDA' , 'ORCAMENTO_ENDOSSO', 'ENDOSSO_INCOMPLETO', 'PROPOSTA_CANCELADA', 'APOLICE_CANCELADA', 'ENDOSSO_ANULADO', 'SOLICITACAO_CANCELADA')";
	    }
	    
	    $this->_db->query("
					CREATE temp TABLE tmp_propostas AS (
						SELECT
							pr.id,
							pr.id as id_proposta,
							pr.id_endosso,
							id_proposta_renovada,
							id_versao,
							seguro.nr_endosso(pr.id, pr.id_endosso) as nr_endosso,
							nr_apolice,
							pr.id_status,
				            pr.id_usuario_criacao,
							id_produto,
							to_char(seguro.dt_vigencia_inicio_original(pr.id),'DD/MM/YYYY') as dt_vigencia_inicio_original,
							pr.dt_vigencia_inicio,
							pr.id_proposta_mae
						FROM
							seguro.propostas pr,
							produto.produtos pd,
	    					seguro.propostas_corretores pc
	    				WHERE
	    					".$filtro."
	    					pd.id = pr.id_produto and 
	    		 			pc.id_proposta = pr.id
						)
				");
	    	    
	    $sql = "SELECT pr.id_proposta_mae AS id_proposta
					, pr.id_versao
					, pr.nr_endosso
					, pr.id_proposta_mae
					, pr.nr_apolice
					, to_char(pr.dt_vigencia_inicio, 'DD/MM/YYYY') as dt_vigencia_inicio
					, pr.id_status
					, c.ds_nome_cultura
					, pp.ds_nome_proponente
					, st.ds_status
					, st.cd_status
					, co.ds_nome_abreviado AS ds_nome_fantasia
					, u.tp_usuario
					, CASE WHEN u.tp_usuario='P' THEN
						u.ds_nome_usuario
					  ELSE
						''
					  END AS sublogin
					, pb.nr_parcela
					, pb.vl_total
					, pb.vl_segurado
					, pb.vl_subvencao_federal
					, pb.vl_subvencao_estadual
					, CASE WHEN bo.fl_pago=true THEN
						'Sim'
					  ELSE
						'Não'
					  END AS fl_pago
					, pc.vl_comissao
					, to_char(pb.dt_vencimento, 'DD/MM/YYYY') as dt_vencimento
					, tblprorrogacoes.dt_vencto_prorrogado
				FROM seguro.propostas_endosso pe
			INNER JOIN tmp_propostas pr
					ON pe.id_proposta  = pr.id
			INNER JOIN seguro.propostas_corretores pc
					ON pc.id_proposta  = pr.id
			INNER JOIN seguro.propostas_proponentes pp
					ON pp.id_proposta  = pr.id
			INNER JOIN produto.produtos pd
					ON pd.id = pr.id_produto
			INNER JOIN produto.produtos_geral pg
					ON pg.id = pd.id_produto_geral
			INNER JOIN produto.culturas c
					ON c.id = pg.id_cultura
			INNER JOIN sistema.status st
					ON st.id = pr.id_status
			INNER JOIN sistema.usuarios u
					ON pr.id_usuario_criacao = u.id
			INNER JOIN sistema.corretores co
					ON co.id_usuario = pc.id_usuario
			INNER JOIN seguro.propostas_parcelas pb
					ON pb.id_proposta = pr.id
			 LEFT JOIN seguro.proposta_boletos bo
					ON bo.id_proposta = pb.id_proposta
				   AND bo.nr_parcela = pb.nr_parcela
				   AND bo.id_versao = pr.id_versao
			 LEFT JOIN (SELECT id_proposta, nr_parcela, to_char(dt_vencimento, 'DD/MM/YYYY') as dt_vencto_prorrogado 
			              FROM seguro.propostas_boletos_prorrogacoes) as tblprorrogacoes ON tblprorrogacoes.id_proposta = bo.id_proposta 
			                                                                            AND tblprorrogacoes.nr_parcela = bo.nr_parcela
				 WHERE $filtro
					   $status
				   AND pc.id_usuario   <> 487 --(corretor teste)
				   AND pb.vl_segurado > 0
			  ORDER BY pr.id_proposta_mae ASC
					 , pr.id ASC
					 , pb.nr_parcela ASC;
				";

	    return $this->fetchAll($sql);
	}

	public function relatorioInadimplentes($id_safra="", $arProduto="", $id_usuario="", $tp_usuario=""){
	
		$filtro  = ($id_safra)   ? "pd.id_safra=".$id_safra." and " : '';
		 
		if(is_array($arProduto)){
			$filtro .= "pd.id in (".implode("," , $arProduto).") and ";
		}
		 
		if($tp_usuario == 'C'){
			$filtro .= "pc.id_usuario = ".$id_usuario." and ";
		}
		 
		if($tp_usuario == 'P'){
			$filtro .= "pr.id_usuario_criacao = ".$id_usuario." and ";
		}
	
		$this->_db->query("
					CREATE temp TABLE tmp_propostas AS (
						SELECT
							pr.id,
							pr.id as id_proposta,
							pr.id_endosso,
							id_proposta_renovada,
							id_versao,
							seguro.nr_endosso(pr.id, pr.id_endosso) as nr_endosso,
							nr_apolice,
							pr.id_status,
				            pr.id_usuario_criacao,
							id_produto,
							to_char(seguro.dt_vigencia_inicio_original(pr.id),'DD/MM/YYYY') as dt_vigencia_inicio_original,
							pr.dt_vigencia_inicio,
							pr.id_proposta_mae
						FROM
							seguro.propostas pr,
							produto.produtos pd,
	    					seguro.propostas_corretores pc
	    				WHERE
	    					".$filtro."
	    					pd.id = pr.id_produto and
	    		 			pc.id_proposta = pr.id
						)
				");

		$sql = "select
					 pr.id_proposta_mae as id_proposta
					,pr.id_versao
					,pr.nr_endosso
					,pr.id_proposta_mae
					,pr.nr_apolice
					,pr.dt_vigencia_inicio
					,pr.id_status
					,pr.id
					,c.ds_nome_cultura
					,pp.ds_nome_proponente
					,st.ds_status
					,co.ds_nome_abreviado as ds_nome_fantasia
					,u.tp_usuario
					,case when u.tp_usuario='P' then u.ds_nome_usuario else '' end as sublogin
					,tblvencimento.nr_parcela
					,tblvencimento.vl_total
					,tblvencimento.vl_segurado
					,tblvencimento.vl_subvencao_federal
					,tblvencimento.vl_subvencao_estadual
					,case when pb.fl_pago=true then 'Sim' else 'Não' end as fl_pago
					,pc.vl_comissao
					,tblvencimento.dt_vencimento
				from
					 seguro.propostas_endosso pe
					,tmp_propostas pr
					inner join (select dt_vencimento, id_proposta, nr_parcela, vl_total, vl_segurado, vl_subvencao_federal, vl_subvencao_estadual,fl_pago from seguro.propostas_parcelas) as tblvencimento on tblvencimento.id_proposta = pr.id		
					left join seguro.proposta_boletos pb on  pb.id_proposta = pr.id and pb.id_versao = pr.id_versao and tblvencimento.nr_parcela = pb.nr_parcela
					,seguro.propostas_corretores pc
					,seguro.propostas_proponentes pp
					,produto.produtos pd
					,produto.produtos_geral pg
					,produto.culturas c
					,sistema.status st
					,sistema.usuarios u
					,sistema.corretores co
				where
					".$filtro."
					st.ds_chave         not in ('PROPOSTA_INCOMPLETA' , 'NAO_ENVIADA' , 'DEVOLVIDA' , 'ORCAMENTO_ENDOSSO', 'ENDOSSO_INCOMPLETO', 'ENDOSSO_CANCELADO','PROPOSTA_CANCELADA', 'APOLICE_CANCELADA', 'ENDOSSO_ANULADO', 'SOLICITACAO_CANCELADA')
					and pe.id_proposta  = pr.id
					and pc.id_usuario   <> 487 --(corretor teste)
					and pd.id           = pr.id_produto
					and pc.id_proposta  = pr.id
					and pp.id_proposta  = pr.id
					and pg.id           = pd.id_produto_geral
					and c.id            = pg.id_cultura
					and st.id           = pr.id_status
					and pr.id_usuario_criacao = u.id
					and co.id_usuario   = pc.id_usuario
					and tblvencimento.vl_segurado > 0.01
					and tblvencimento.fl_pago  = false
					and (pb.fl_pago is null or pb.fl_pago is false)
					and to_char(tblvencimento.dt_vencimento, 'YYYY-MM-DD') <= to_char((now() - interval '3 day'), 'YYYY-MM-DD')
				order by
					pr.id_proposta_mae asc, pr.id asc, pb.nr_parcela asc
				";
	
		return $this->fetchAll($sql);
	}
	
	public function relatorioDocumentoFisico($id_safra="", $arProduto="", $id_usuario="", $tp_usuario="", $fl_pendente=false, $nr_caixa=""){
	
		// Se a listagem for por documentos pendentes, lista todas as pendencias independente da safra 
		if($fl_pendente == true){
			$filtro_status = "st2.id in (214, 217) and";
		} 
			
		$filtro = ($id_safra && $id_safra <> 'TODAS') ? "pd.id_safra=".$id_safra." and " : '';
		
		if(is_array($arProduto)){
			$filtro .= "pd.id in (".implode("," , $arProduto).") and ";
		}
			
		if($tp_usuario == 'C'){
		
			$filtro .= "pc.id_usuario = ".$id_usuario." and ";
		
		} elseif($tp_usuario == 'P'){
			
			$filtro .= "pr.id_usuario_criacao = ".$id_usuario." and ";
		
		} else {
			if(is_array($id_usuario)){
				$filtro .= "pc.id_usuario in (".implode("," , $id_usuario).") and ";
			}
		}
		
		if($nr_caixa){
			$filtro_caixa = "psd.ds_observacao = '".$nr_caixa."' and ";
		}
	
		$this->_db->query("
					CREATE temp TABLE tmp_propostas AS (
						SELECT
							pr.id,
							pr.id as id_proposta,
							pr.id_endosso,
							id_proposta_renovada,
							seguro.nr_endosso(pr.id, pr.id_endosso) as nr_endosso,
							dt_transmissao,
							nr_apolice,
							pr.id_status,
							pr.id_usuario_criacao,
							id_produto,
							to_char(seguro.dt_vigencia_inicio_original(pr.id),'DD/MM/YYYY') as dt_vigencia_inicio_original,
							dt_vigencia_inicio,
							pr.id_proposta_mae
						FROM
							seguro.propostas pr,
							produto.produtos pd,
							seguro.propostas_corretores pc
						WHERE
							".$filtro."
							pd.id = pr.id_produto and
							pc.id_proposta = pr.id
						)
				");

		$sql = "select
					distinct
					 pr.id_proposta_mae as id_proposta
					,'V1' AS id_versao
					,pr.nr_endosso
					,pr.id_proposta_mae
					,pr.nr_apolice
					,pr.dt_vigencia_inicio
                    ,case when pr.nr_endosso > 0 then to_char(ps_endosso.dt_criacao, 'DD/MM/YYYY') else to_char(pr.dt_transmissao, 'DD/MM/YYYY') end as dt_transmissao
                    ,case when pr.nr_endosso > 0 then current_date - ps_endosso.dt_criacao::date else current_date - pr.dt_transmissao::date end as dt_transmissao_dif_hoje
					,pr.id_status
					,pr.id
					,c.ds_nome_cultura
					,pp.ds_nome_proponente
					,st.ds_status
					,co.ds_nome_abreviado as ds_nome_fantasia
					,u.tp_usuario
					,case when u.tp_usuario='P' then u.ds_nome_usuario else '' end as sublogin
				    ,st2.ds_status as ds_status_doc_fisico
				    ,array_to_string(array_agg(pnd.ds_nome_pendencia::text) OVER (PARTITION BY pr.id), ', ') AS pendencias
				    ,psd.ds_observacao
					,regexp_replace(psd.ds_observacao, '[\n\r]+', '', 'g' ) as ds_observacao
				    ,sf.ds_nome_safra as id_safra
				    ,case when spb.vl_subvencao_federal > 0 then
				    	case when pr.nr_endosso > 0 then
				    		to_char(ps_endosso.dt_criacao::DATE + interval '45 day','DD/MM/YYYY')
				    	else
				    		to_char(pr.dt_transmissao + interval '45 day','DD/MM/YYYY')
				    	end
				     else
				     	case when pr.nr_endosso > 0 then
				     		to_char(ps_endosso.dt_criacao::DATE + interval '15 day','DD/MM/YYYY')
				     	else
				     		to_char(pr.dt_transmissao + interval '15 day','DD/MM/YYYY')
				     	end
				     end as dt_envio_fisico,
				     CASE WHEN st2.ds_chave <> 'DOC_PENDENTE_ENVIO' THEN
                         TO_CHAR(COALESCE(psd.dt_alteracao, psd.dt_criacao), 'DD/MM/YYYY')
                     ELSE
                         NULL
                     END as dt_ultima_alteracao
				from
					 seguro.propostas_endosso pe
					,tmp_propostas pr
					 inner join seguro.propostas_status_doc_fisico psd on psd.id_proposta = pr.id and psd.fl_ativo = true
					 inner join sistema.status st2 on st2.id = psd.id_status
					 left join seguro.propostas_status ps_endosso on ps_endosso.id_proposta = pr.id and ps_endosso.id_status = 8 -- busca as informações da apólice emitida
					 left join seguro.propostas_pendencias_documento_fisico ppdf on ppdf.id_proposta = pr.id and ppdf.id_proposta_status_doc_fisico = psd.id
					 left join atendimento.pendencias pnd on pnd.id = ppdf.id_pendencia
					 left join seguro.propostas_parcelas AS spb ON pr.id_proposta_mae = spb.id_proposta AND spb.nr_parcela=1 and spb.fl_ativo=true
					,seguro.propostas_corretores pc
					,seguro.propostas_proponentes pp
					,produto.produtos pd
				    ,produto.safras sf
					,produto.produtos_geral pg
					,produto.culturas c
					,sistema.status st
					,sistema.usuarios u
					,sistema.corretores co
				where
					".$filtro."
					".$filtro_status."
					".$filtro_caixa."							
					st.ds_chave         not in ('PROPOSTA_INCOMPLETA' , 'NAO_ENVIADA' , 'DEVOLVIDA' , 'ORCAMENTO_ENDOSSO', 'ENDOSSO_INCOMPLETO', 'SOLICITACAO_CANCELADA')
					and pe.id_proposta  = pr.id
					and pc.id_usuario   <> 487 --(corretor teste)
					and pd.id           = pr.id_produto
					and pc.id_proposta  = pr.id
					and pp.id_proposta  = pr.id
					and sf.id           = pd.id_safra
					and pg.id           = pd.id_produto_geral
					and c.id            = pg.id_cultura
					and st.id           = pr.id_status
					and pr.id_usuario_criacao = u.id
					and co.id_usuario   = pc.id_usuario
				group by
					 pr.id_proposta_mae
					,pr.nr_endosso
					,pr.id_proposta_mae
					,pr.nr_apolice
					,pr.dt_vigencia_inicio
					,pr.dt_transmissao
					,pr.id_status
					,pr.id
					,c.ds_nome_cultura
					,pp.ds_nome_proponente
					,st.ds_status
					,co.ds_nome_abreviado
					,u.tp_usuario
					,st2.ds_status	
					,u.ds_nome_usuario
					,pnd.ds_nome_pendencia
				    ,psd.ds_observacao
					,sf.ds_nome_safra
					,spb.vl_subvencao_federal
					,ps_endosso.dt_criacao
					,psd.dt_criacao
                    ,psd.dt_alteracao
                    ,st2.ds_chave
				order by
					pr.id_proposta_mae asc, pr.id asc				
				";

		return $this->fetchAll($sql);
	}

	public function relatorioDocumentoDigitalizado($id_safra="", $arProduto="", $id_usuario="", $tp_usuario="", $fl_pendente=false, $nr_caixa=""){
	
		// Se a listagem for por documentos pendentes, lista todas as pendencias independente da safra 
		if($fl_pendente == true){
			$idStatusPendente = $this->getIdStatus('DOC_PENDENTE_ENVIO', 'DOCUMENTO_DIGITALIZADO');
			$idStatusPendencia = $this->getIdStatus('DOC_PENDENCIA','DOCUMENTO_DIGITALIZADO');
			$filtro_status = " st2.id in ({$idStatusPendencia}, {$idStatusPendente}) and ";
		} 
			
		$filtro = ($id_safra && $id_safra <> 'TODAS') ? "pd.id_safra=".$id_safra." and " : '';
		
		if(is_array($arProduto)){
			$filtro .= "pd.id in (".implode("," , $arProduto).") and ";
		}
			
		if($tp_usuario == 'C'){
		
			$filtro .= "pc.id_usuario = ".$id_usuario." and ";
		
		} elseif($tp_usuario == 'P'){
			
			$filtro .= "pr.id_usuario_criacao = ".$id_usuario." and ";
		
		} else {
			if(is_array($id_usuario)){
				$filtro .= "pc.id_usuario in (".implode("," , $id_usuario).") and ";
			}
		}
		
		if($nr_caixa){
			$filtro_caixa = "psdd.ds_observacao = '".$nr_caixa."' and ";
		}

		$this->_db->query("
					CREATE temp TABLE tmp_propostas AS (
						SELECT
							pr.id,
							pr.id as id_proposta,
							pr.id_endosso,
							id_proposta_renovada,
							seguro.nr_endosso(pr.id, pr.id_endosso) as nr_endosso,
							dt_transmissao,
							nr_apolice,
							pr.id_status,
							pr.id_usuario_criacao,
							id_produto,
							to_char(seguro.dt_vigencia_inicio_original(pr.id),'DD/MM/YYYY') as dt_vigencia_inicio_original,
							dt_vigencia_inicio,
							pr.id_proposta_mae
						FROM
							seguro.propostas pr,
							produto.produtos pd,
							seguro.propostas_corretores pc
						WHERE
							".$filtro."
							pd.id = pr.id_produto and
							pc.id_proposta = pr.id
						)
				");

		$sql = "select
					distinct
					 pr.id_proposta_mae as id_proposta
					,'V1' AS id_versao
					,pr.nr_endosso
					,pr.id_proposta_mae
					,pr.nr_apolice
					,pr.dt_vigencia_inicio
                    ,case when pr.nr_endosso > 0 then to_char(ps_endosso.dt_criacao, 'DD/MM/YYYY') else to_char(pr.dt_transmissao, 'DD/MM/YYYY') end as dt_transmissao
                    ,case when pr.nr_endosso > 0 then current_date - ps_endosso.dt_criacao::date else current_date - pr.dt_transmissao::date end as dt_transmissao_dif_hoje
					,pr.id_status
					,pr.id
					,c.ds_nome_cultura
					,pp.ds_nome_proponente
					,st.ds_status
					,co.ds_nome_abreviado as ds_nome_fantasia
					,u.tp_usuario
					,case when u.tp_usuario='P' then u.ds_nome_usuario else '' end as sublogin
				    ,st2.ds_status as ds_status_doc_digitalizado
				    ,array_to_string(array_agg(pnd.ds_nome_pendencia::text) OVER (PARTITION BY pr.id), ', ') AS pendencias
				    ,psdd.ds_observacao
					,regexp_replace(psdd.ds_observacao, '[\n\r]+', '', 'g' ) as ds_observacao
				    ,sf.ds_nome_safra as id_safra
				    ,case when spb.vl_subvencao_federal > 0 then
				    	case when pr.nr_endosso > 0 then
				    		to_char(ps_endosso.dt_criacao::DATE + interval '45 day','DD/MM/YYYY')
				    	else
				    		to_char(pr.dt_transmissao + interval '45 day','DD/MM/YYYY')
				    	end
				     else
				     	case when pr.nr_endosso > 0 then
				     		to_char(ps_endosso.dt_criacao::DATE + interval '15 day','DD/MM/YYYY')
				     	else
				     		to_char(pr.dt_transmissao + interval '15 day','DD/MM/YYYY')
				     	end
				     end as dt_envio_fisico,
					 TO_CHAR(psdd.dt_criacao, 'DD/MM/YYYY') AS dt_ultima_alteracao
				from
					 seguro.propostas_endosso pe
					,tmp_propostas pr
					 INNER JOIN seguro.propostas_status_documento_digitalizado psdd ON psdd.id_proposta = pr.id AND psdd.fl_ativo = TRUE
					 inner join sistema.status st2 on st2.id = psdd.id_status
					 left join seguro.propostas_status ps_endosso on ps_endosso.id_proposta = pr.id and ps_endosso.id_status = 8
					 LEFT JOIN seguro.propostas_pendencias_documento_digitalizado ppdd ON ppdd.id_proposta_status_documento_digitalizado = psdd.id
					 left join atendimento.pendencias pnd on pnd.id = ppdd.id_pendencia
					 left join seguro.propostas_parcelas AS spb ON pr.id_proposta_mae = spb.id_proposta AND spb.nr_parcela=1 and spb.fl_ativo=true
					,seguro.propostas_corretores pc
					,seguro.propostas_proponentes pp
					,produto.produtos pd
				    ,produto.safras sf
					,produto.produtos_geral pg
					,produto.culturas c
					,sistema.status st
					,sistema.usuarios u
					,sistema.corretores co
				where
					".$filtro."
					".$filtro_status."
					".$filtro_caixa."							
					st.ds_chave         not in ('PROPOSTA_INCOMPLETA' , 'NAO_ENVIADA' , 'DEVOLVIDA' , 'ORCAMENTO_ENDOSSO', 'ENDOSSO_INCOMPLETO', 'SOLICITACAO_CANCELADA')
					and pe.id_proposta  = pr.id
					and pc.id_usuario   <> 487
					and pd.id           = pr.id_produto
					and pc.id_proposta  = pr.id
					and pp.id_proposta  = pr.id
					and sf.id           = pd.id_safra
					and pg.id           = pd.id_produto_geral
					and c.id            = pg.id_cultura
					and st.id           = pr.id_status
					and pr.id_usuario_criacao = u.id
					and co.id_usuario   = pc.id_usuario
				group by
					 pr.id_proposta_mae
					,pr.nr_endosso
					,pr.id_proposta_mae
					,pr.nr_apolice
					,pr.dt_vigencia_inicio
					,pr.dt_transmissao
					,pr.id_status
					,pr.id
					,c.ds_nome_cultura
					,pp.ds_nome_proponente
					,st.ds_status
					,co.ds_nome_abreviado
					,u.tp_usuario
					,st2.ds_status	
					,u.ds_nome_usuario
					,pnd.ds_nome_pendencia
				    ,psdd.ds_observacao
					,sf.ds_nome_safra
					,spb.vl_subvencao_federal
					,ps_endosso.dt_criacao
					,psdd.dt_criacao
                    ,psdd.dt_alteracao
                    ,st2.ds_chave
				order by
					pr.id_proposta_mae asc, pr.id asc				
				";

		return $this->fetchAll($sql);
	}

	public function relatorioAcompanhamentoDevolucaoPremio(
		int $idSafra,
		int $motivo,
		?int $idCorretor,
		?array $idsStatus,
		?array $idsProdutos
	) {
        $filtros = array();
        $filtros[] = "prod.id_safra = {$idSafra}";

        if (!empty($idsProdutos)) {
            $filtros[] = 'pdg.id IN (SELECT id_produto_geral FROM produto.produtos WHERE produtos.id IN (' . implode(',', $idsProdutos) . ')) ';
        }

        if (!empty($idCorretor)) {
            $filtros[] = "pc.id_usuario = {$idCorretor}";
        }

        $whereTemp = implode(" AND ", $filtros);

        $sqlDataStatusCanceladaTemp = "
			CREATE temp TABLE data_status_cancelada_temp AS (
				SELECT MAX(ps.dt_criacao) AS dt_criacao_max,
					pr.id AS id_proposta
					FROM seguro.propostas pr
					JOIN seguro.propostas_status ps ON ps.id_proposta = pr.id
					JOIN seguro.propostas_corretores pc ON pc.id_proposta = pr.id
					JOIN produto.produtos prod ON prod.id = pr.id_produto
					JOIN produto.produtos_geral pdg ON pdg.id = prod.id_produto_geral
					JOIN produto.safras sf ON sf.id = prod.id_safra
					WHERE {$whereTemp}
					AND ps.id_status = " . Seguro_Model_Propostas::ID_STATUS_CANCELADA . "
					GROUP BY pr.id
			);
		";

        $sqlTransmissaoTemp = "
			CREATE temp TABLE transmissao_temp AS (
				SELECT  MIN(pt.dt_criacao) AS dt_criacao_min
					,pt.id_proposta     AS id_proposta
				FROM log.propostas_transmissao pt
				JOIN seguro.propostas pr ON pt.id_proposta = pr.id
				JOIN seguro.propostas_corretores pc ON pc.id_proposta = pr.id
				JOIN produto.produtos prod ON prod.id = pr.id_produto
				JOIN produto.produtos_geral pdg ON pdg.id = prod.id_produto_geral
				JOIN produto.safras sf ON sf.id = prod.id_safra
				WHERE {$whereTemp}
				AND pt.fl_sucesso is TRUE
				AND pt.ds_tipo_transmissao = 'EMITIR_PROPOSTA (Endosso)'
				GROUP BY pt.id_proposta
			);
		";

        if ($motivo == 1) {
            $filtros[] = "seguro.nr_endosso(pr.id, pr.id_endosso) > 0";
        } else if ($motivo == 2) {
            $filtros[] = "seguro.nr_endosso(pr.id, pr.id_endosso) = 0";
        }

        if (!empty($idsStatus)) {
            $filtros[] = 'pds.id_status IN (' . implode(',', $idsStatus) . ')';
        }

        $where = implode(" AND ", $filtros);

        $sql = "
			SELECT
				pr.id_proposta_mae,
				pr.id as id_proposta,
				seguro.nr_endosso(pr.id, pr.id_endosso) AS nr_endosso,
				regexp_replace(seguro.id_proposta_mae_completo(pr.id), '[-.]', '', 'g') as proposta_completa,
				pr.nr_apolice AS nr_apolice,
				pr.ds_identificador_seguradora AS id_endosso,
				(SELECT array_to_string(array_agg(pb.nr_parcela || '-' || pb.nu_boleto ORDER BY pb.nr_parcela), ', ') FROM seguro.proposta_boletos pb WHERE pb.id_proposta = pr.id GROUP BY pb.id_proposta ) AS numero_titulo,
				pp.ds_nome_proponente,
				pp.cpf_cnpj AS cpf_cnpj_proponente,
				CASE WHEN pd.id_beneficiario is null THEN 'N' ELSE 'S' END AS termo,
				CASE WHEN seguro.nr_endosso(pr.id, pr.id_endosso) = 0 
					THEN (SELECT sum(vl_segurado) FROM seguro.propostas_parcelas pp WHERE pp.id_proposta = pd.id_proposta AND (fl_pago IS true OR fl_liberar_devolucao IS true))
					WHEN seguro.nr_endosso(pr.id, pr.id_endosso) > 0 
						THEN (SELECT sum(vl_segurado) * -1 FROM seguro.propostas_parcelas pp WHERE pp.id_proposta = pd.id_proposta)
					ELSE 0 
				END as valor,
				fp.ds_forma_pagamento AS tp_devolucao,
				b.ds_nome_banco AS instituicao_financeira,
				CASE WHEN ib.nu_digito_agencia is null THEN ib.ds_agencia ELSE ib.ds_agencia || '-' || ib.nu_digito_agencia END AS agencia,
				CASE WHEN ib.nu_digito_conta is null THEN ib.ds_conta ELSE ib.ds_conta || '-' || ib.nu_digito_conta END AS conta,
				CASE WHEN pd.fl_conta_conjunta is true THEN 'S' ELSE 'N' END as conta_conjunta,
				CASE WHEN ib.ds_tipo_conta is null THEN '' WHEN ib.ds_tipo_conta = 'C' THEN 'Corrente' ELSE 'Poupança' END AS tipo_conta,
				CASE WHEN data_status_cancelada_temp.dt_criacao_max is NULL THEN to_char(transmissao_temp.dt_criacao_min, 'DD/MM/YYYY') ELSE to_char(data_status_cancelada_temp.dt_criacao_max, 'DD/MM/YYYY') END AS dt_inicio,
				c.ds_nome_fantasia as corretor,
				status_devolucao.ds_status AS ds_status_devolucao,
				CASE WHEN (EXISTS(SELECT 1 FROM seguro.propostas_devolucoes_arquivos pda WHERE pda.id_proposta_devolucao = pd.id and pda.fl_ativo is true)) THEN 'Sim' ELSE 'Não' END AS documento_anexado,
				to_char(pd.dt_programada, 'DD/MM/YYYY') AS dt_programada,
				ds_nome_usuario AS digitador
			    , pds.ds_observacao as observacao
			FROM seguro.propostas_devolucoes pd
			JOIN seguro.propostas_devolucoes_status pds ON pds.id_proposta_devolucao = pd.id AND pds.fl_ativo is true
			JOIN sistema.status status_devolucao ON status_devolucao.id = pds.id_status
			JOIN seguro.propostas pr ON pr.id = pd.id_proposta
			JOIN seguro.propostas_proponentes pp ON pp.id_proposta = pr.id
			JOIN produto.produtos prod ON prod.id = pr.id_produto
			JOIN produto.safras sf ON sf.id = prod.id_safra
			JOIN seguro.propostas_corretores pc ON pc.id_proposta = pr.id
			JOIN sistema.corretores c ON c.id_usuario = pc.id_usuario
			JOIN sistema.usuarios u ON u.id = pr.id_usuario_criacao
            JOIN produto.produtos_geral pdg ON pdg.id = prod.id_produto_geral
			LEFT JOIN sistema.formas_pagamento fp ON fp.id = pd.id_forma_pagamento
			LEFT JOIN sistema.informacoes_bancarias ib ON ib.id = pd.id_informacao_bancaria
			LEFT JOIN sistema.bancos b ON b.id = ib.id_banco
			LEFT JOIN data_status_cancelada_temp ON data_status_cancelada_temp.id_proposta = pr.id
			LEFT JOIN transmissao_temp ON transmissao_temp.id_proposta = pr.id
		   WHERE {$where}
		";

        $sql .= " ORDER BY pr.id_proposta_mae desc, nr_endosso ";

        $this->_db->query($sqlDataStatusCanceladaTemp);
        $this->_db->query($sqlTransmissaoTemp);

        return $this->fetchAll($sql);
    }
}