_db->fetchAll($sql); } /** * Retorna os dados do aviso incluindo o data de envio do aviso e os nomes dos eventos * Para utilizar esse método é necessário criar a tabela temporária nomes_eventos_temp antes. * Não foi criado dentro desse método pois ele é chamado dentro de um looping * * @param int $idProposta */ public function getAvisoProcessoCompleto(int $idProposta) { $sql = " SELECT av.id, "; $sql .= " to_char(av.dt_sinistro, 'DD/MM/YYYY HH24:MI') as dt_sinistro, "; $sql .= " to_char(av.dt_criacao, 'DD/MM/YYYY HH24:MI') as dt_envio_aviso, "; $sql .= " aep.ds_eventos "; $sql .= " FROM sinistro.avisos av"; $sql .= " JOIN nomes_eventos_temp aep ON aep.id_aviso = av.id"; $sql .= " WHERE av.id_status in (28, 30) "; $sql .= " AND av.id_proposta = {$idProposta}"; return $this->_db->fetchAll($sql); } public function getPrevisoesPagamento($id_liquidacao) { $sql = "select fl_subvencao_descontada,vl_pagamento,to_char(dt_pagamento, 'DD/MM/YYYY') as dt_pagamento,to_char(dt_confirmacao_pagamento, 'DD/MM/YYYY') as dt_confirmacao_pagamento from sinistro.liquidacoes_previsao_pagamento where id_liquidacao=".$id_liquidacao; return $this->_db->fetchAll($sql); } public function getVistoriador($id_laudo_final_item_segurado) { $sql = "select u.ds_nome_usuario from sinistro.laudos_finais_itens_segurados_funcionarios lf, sistema.funcionarios f, sistema.usuarios u where f.id = lf.id_funcionario and u.id = f.id_usuario and lf.id_laudo_final_item_segurado=".$id_laudo_final_item_segurado; return $this->_db->fetchAll($sql); } public function getVistoriadorLF($id_laudo_final) { $sql = "select u.ds_nome_usuario from sinistro.laudos_finais_funcionarios lf, sistema.funcionarios f, sistema.usuarios u where f.id = lf.id_funcionario and u.id = f.id_usuario and lf.id_laudo_final=".$id_laudo_final; return $this->_db->fetchAll($sql); } public function getPrePosFloracao($id_item_segurado,$id_safra) { if($id_safra == 12) { $sql = "select fl_pre_floracao, fl_pos_floracao from safra12.laudos_finais_uva_granizo_itens_segurados where id_laudo_final_item_segurado=".$id_item_segurado; } if($id_safra == 13) { $sql = "select fl_pre_floracao, fl_pos_floracao from safra13.laudos_finais_uva_granizo_itens_segurados where id_laudo_final_item_segurado=".$id_item_segurado; } if($id_safra == 14) { $sql = "select fl_floracao from safra14.lf005_itens_segurados where id_laudo_final_item_segurado=".$id_item_segurado; } if($id_safra == 15) { $sql = "select fl_floracao from safra15.lf005_itens_segurados where id_laudo_final_item_segurado=".$id_item_segurado; } if($id_safra == 16) { $sql = "select fl_floracao from safra16.lf005_itens_segurados where id_laudo_final_item_segurado=".$id_item_segurado; } if($id_safra == 17) { try{ $sql = "select fl_floracao from safra17.lf005_itens_segurados where id_laudo_final_item_segurado=".$id_item_segurado; }catch (Exception $e){ die($e); } } if($id_safra > 17) { try{ $sql = "select fl_floracao from regulacao.lf005_itens_segurados where id_laudo_final_item_segurado=".$id_item_segurado; }catch (Exception $e){ die($e); } } return $this->_db->fetchAll($sql); } public function getLaudosItensSegurados($nr_item_segurado, $id_endosso) { $sql = <<_db->fetchAll($sql); } public function getLaudosNaoSinistrados($id_processo) { $sql = "select lf.id , lf.dt_vistoria , lf.dt_criacao , co.ds_nome_cobertura from sinistro.processos sp inner join sinistro.laudos_finais lf on lf.id_processo = sp.id inner join produto.coberturas co on co.id = lf.id_cobertura where (lf.fl_evento_nao_constatado = true OR lf.fl_dt_evento_em_cobertura IS FALSE) AND sp.id = {$id_processo} ORDER BY lf.dt_vistoria"; return $this->_db->fetchAll($sql); } public function getLaudosLiquidadosItensSeguradosUvaVinho($id_item_segurado,$id_cobertura) { $sql = " select lf.id ,lfis.id as id_laudo_final_item_segurado ,lfis.vl_percent_perdas ,lfis.dt_vistoria ,lfis.dt_criacao ,lfis.vl_percent_colhido ,lfis.vl_area_sinistrada ,lfis.vl_percent_area_afetada ,(( select sum(vl_percent_perdas) from sinistro.liquidacoes_itens_segurados_coberturas_lf_dependentes where id_liquidacoes_itens_segurados_coberturas_lf = lislf.id ) + lislf.vl_percent_dano ) as vl_percent_dano from sinistro.laudos_finais lf ,sinistro.laudos_finais_itens_segurados lfis ,sinistro.liquidacoes_itens_segurados_coberturas_lf lislf where lfis.id_laudo_final = lf.id and lf.id_cobertura = ".$id_cobertura." and lfis.id_item_segurado = ".$id_item_segurado." and lislf.id_laudo_final_item_segurado = lfis.id group by lf.id ,lfis.id ,lfis.vl_percent_perdas ,lfis.dt_vistoria ,lfis.dt_criacao ,lfis.vl_percent_colhido ,lfis.vl_area_sinistrada ,lfis.vl_percent_area_afetada ,lislf.id ,lislf.vl_percent_dano order by lfis.dt_vistoria"; return $this->_db->fetchAll($sql); } public function getLaudosPreliminaresItensSegurados($id_item_segurado,$id_safra) { if($id_safra < 14) { $sql = " select lp.id, lp.dt_vistoria as dt_criacao, case when i1.ds_intensidade is not null then SUBSTRING(i1.ds_intensidade,1,1) when i2.ds_intensidade is not null then SUBSTRING(i2.ds_intensidade,1,1) when i3.ds_intensidade is not null then SUBSTRING(i3.ds_intensidade,1,1) else '0.00' end as intensidade, lp.fl_dt_evento_em_cobertura, lp.fl_evento_nao_constatado, lp.id_status from sinistro.laudos_preliminares lp left join safra13.laudos_preliminares_frutas_hortalicas_granizo lpfh on lpfh.id_laudo_preliminar = lp.id left join sinistro.intensidades i1 on i1.id = lpfh.id_intensidade left join safra13.laudos_preliminares_uva_geada lpug on lpug.id_laudo_preliminar = lp.id left join sinistro.intensidades i2 on i2.id = lpug.id_intensidade, sinistro.laudos_preliminares_itens_segurados lpis left join safra13.laudos_preliminares_graos_itens_segurados lpgis on lpgis.id_laudo_preliminar_item_segurado = lpis.id left join sinistro.intensidades i3 on i3.id = lpgis.id_intensidade where lpis.id_laudo_preliminar = lp.id and lp.id_cobertura = ".$id_cobertura." and lpis.id_item_segurado = ".$id_item_segurado; } else { $sql = " SELECT CASE WHEN i.ds_intensidade IS NOT NULL THEN SUBSTRING(i.ds_intensidade,1,1)||' ' ELSE '0' END AS intensidade, lp.dt_vistoria AS dt_criacao, co.ds_nome_cobertura, vlp.id_vistoria, ef.ds_nome_estadio_fenologico, CASE WHEN lis2.vl_tamanho_planta is not null THEN lis2.vl_tamanho_planta WHEN lis3.vl_tamanho_planta is not null THEN lis3.vl_tamanho_planta WHEN lis4.vl_tamanho_planta is not null THEN lis4.vl_tamanho_planta ELSE null END as tamanho_planta, string_agg(ds_nome_evento, ', ') as eventos, lp.fl_dt_evento_em_cobertura, lp.fl_evento_nao_constatado, lp.id_status, lp.id_cobertura FROM sinistro.laudos_preliminares lp INNER JOIN produto.coberturas co ON co.id = lp.id_cobertura INNER JOIN sinistro.laudos_preliminares_itens_segurados li ON li.id_laudo_preliminar = lp.id INNER JOIN sistema.vistorias_laudos_preliminares vlp ON vlp.id_laudo_preliminar = lp.id LEFT JOIN sinistro.vistorias_avisos va ON va.id_vistoria = vlp.id_vistoria LEFT JOIN sinistro.avisos_eventos ae ON ae.id_aviso = va.id_aviso LEFT JOIN produto.eventos e ON e.id = ae.id_evento LEFT JOIN sinistro.intensidades i ON i.id = li.id_intensidade LEFT JOIN produto.estadios_fenologicos ef ON ef.id = li.id_estadio_fenologico LEFT JOIN regulacao.lp002_itens_segurados lis2 ON lis2.id_laudo_preliminar_item_segurado = li.id LEFT JOIN regulacao.lp003_itens_segurados lis3 ON lis3.id_laudo_preliminar_item_segurado = li.id LEFT JOIN regulacao.lp004_itens_segurados lis4 ON lis4.id_laudo_preliminar_item_segurado = li.id WHERE li.id_item_segurado = {$id_item_segurado} AND lp.id_status NOT IN ( ". Sistema_Model_Status::LAUDOS_PRELIMINARES_CANCELADO .", ". Sistema_Model_Status::LAUDOS_PRELIMINARES_CANCELADO_CULPA_SEGURADOR ." ) GROUP BY i.ds_intensidade, lp.dt_vistoria, co.ds_nome_cobertura, vlp.id_vistoria, ef.ds_nome_estadio_fenologico, lis2.vl_tamanho_planta, lis3.vl_tamanho_planta, lis4.vl_tamanho_planta, lp.fl_dt_evento_em_cobertura, lp.fl_evento_nao_constatado, lp.id_status, lp.id_cobertura ORDER BY lp.dt_vistoria"; } return $this->_db->fetchAll($sql); } public function getLaudosPreliminaresNaoSinistrado($id_processo) { $sql = " select lp.dt_vistoria as dt_criacao, co.ds_nome_cobertura, vlp.id_vistoria, ef.ds_nome_estadio_fenologico, li.id_item_segurado, CASE WHEN lis2.vl_tamanho_planta is not null THEN lis2.vl_tamanho_planta WHEN lis3.vl_tamanho_planta is not null THEN lis3.vl_tamanho_planta WHEN lis4.vl_tamanho_planta is not null THEN lis4.vl_tamanho_planta ELSE null END as tamanho_planta, lp.fl_dt_evento_em_cobertura, lp.fl_evento_nao_constatado, lp.id_status, lp.id_cobertura from sinistro.laudos_preliminares lp INNER JOIN produto.coberturas co ON co.id = lp.id_cobertura LEFT JOIN sinistro.laudos_preliminares_itens_segurados li ON li.id_laudo_preliminar = lp.id INNER JOIN sistema.vistorias_laudos_preliminares vlp ON vlp.id_laudo_preliminar = lp.id LEFT JOIN produto.estadios_fenologicos ef ON ef.id = li.id_estadio_fenologico LEFT JOIN regulacao.lp002_itens_segurados lis2 ON lis2.id_laudo_preliminar_item_segurado = li.id LEFT JOIN regulacao.lp003_itens_segurados lis3 ON lis3.id_laudo_preliminar_item_segurado = li.id LEFT JOIN regulacao.lp004_itens_segurados lis4 ON lis4.id_laudo_preliminar_item_segurado = li.id where lp.id_status NOT IN ( ". Sistema_Model_Status::LAUDOS_PRELIMINARES_CANCELADO .", ". Sistema_Model_Status::LAUDOS_PRELIMINARES_CANCELADO_CULPA_SEGURADOR ." ) AND (lp.fl_evento_nao_constatado = true or lp.fl_dt_evento_em_cobertura = false) and lp.id_processo = ".$id_processo; return $this->_db->fetchAll($sql); } public function getLaudosPreliminares($id_proposta,$id_cobertura,$id_safra) { if($id_safra < 14) { $sql = " select lp.id, lp.dt_criacao, case when i1.ds_intensidade is not null then SUBSTRING(i1.ds_intensidade,1,1) when i2.ds_intensidade is not null then SUBSTRING(i2.ds_intensidade,1,1) when i3.ds_intensidade is not null then SUBSTRING(i3.ds_intensidade,1,1) else ' - ' end as intensidade from sinistro.laudos_preliminares lp left join safra13.laudos_preliminares_frutas_hortalicas_granizo lpfh on lpfh.id_laudo_preliminar = lp.id left join sinistro.intensidades i1 on i1.id = lpfh.id_intensidade left join safra13.laudos_preliminares_uva_geada lpug on lpug.id_laudo_preliminar = lp.id left join sinistro.intensidades i2 on i2.id = lpug.id_intensidade, sinistro.laudos_preliminares_itens_segurados lpis left join safra13.laudos_preliminares_graos_itens_segurados lpgis on lpgis.id_laudo_preliminar_item_segurado = lpis.id left join sinistro.intensidades i3 on i3.id = lpgis.id_intensidade where lpis.id_laudo_preliminar = lp.id and lp.id_cobertura = ".$id_cobertura." and lpis.id_item_segurado = ".$id_item_segurado; } else { $sql = " select SUBSTRING(i.ds_intensidade,1,1) as intensidade, lp.dt_criacao from sinistro.laudos_preliminares lp, sinistro.laudos_preliminares_itens_segurados li, sinistro.intensidades i where li.id_laudo_preliminar = lp.id and i.id = li.id_intensidade and lp.id_cobertura = ".$id_cobertura." and li.id_item_segurado = ".$id_item_segurado; } return $this->_db->fetchAll($sql); } public function relatorioProcessos($id_safra="",$arProduto="") { $filtro = ($id_safra) ? "pd.id_safra=".$id_safra." and " : ''; if(is_array($arProduto)) { $filtro .= "pd.id in (".implode("," , $arProduto).") and "; } $sql = " select sp.id as processo, pr.id_proposta_mae as proposta, pr.id as id_proposta_original, pr.nr_apolice as apolice, sp.id_processo_seguradora, sprop.id as id_proponente, sp.dt_criacao as data_abertura, pd.ds_nome_produto as produto, s1.cd_status||' - '||s1.ds_chave as status_proposta, s2.cd_status||' - '||s2.ds_chave as status_processo, e.ds_nome_fantasia as empresa, u.ds_nome_usuario as ds_nome_usuario_responsavel_liquidacao from sinistro.processos sp left join sistema.usuarios u on u.id = sp.id_usuario_responsavel_liquidacao, sinistro.processos_empresas pe, sistema.empresas e, seguro.propostas pr, seguro.propostas_endosso pre, produto.produtos pd, seguro.propostas_proponentes pp, seguro.proponentes sprop, sistema.status s1, sistema.status s2 where ".$filtro." pe.fl_ativo = true and sp.id_proposta = pr.id and pre.id_proposta = pr.id and pre.fl_proposta_vigente = true and pe.id_processo = sp.id and e.id = pe.id_empresa and pd.id = pr.id_produto and pp.id_proposta = pr.id and sprop.cpf_cnpj = pp.cpf_cnpj and s1.id = pr.id_status and s2.id = sp.id_status order by sp.dt_criacao "; return $this->fetchAll($sql); } public function relatorioAgendamentosPendentes($id_safra="",$arProduto="") { $filtro = ($id_safra) ? "pd.id_safra=".$id_safra." and " : ''; if(is_array($arProduto)) { $filtro .= "pd.id in (".implode("," , $arProduto).") and "; } $sql = " SELECT sa.id as id_aviso ,pr.id_proposta_mae as id_proposta ,pr.nr_apolice ,pc.ds_nome_cobertura ,pd.ds_nome_produto ,sprop.id as id_proponente ,sm.ds_nome_municipio ,se.ds_sigla ,to_char(sa.dt_aceitacao, 'dd/mm/yyyy') as dt_aceitacao ,to_char(sa.dt_sinistro, 'dd/mm/yyyy') as dt_sinistro ,sem.ds_nome_fantasia ,( SELECT to_char(a.dt_agendamento, 'dd/mm/yyyy') as dt_agendamento FROM sinistro.processos sp JOIN sinistro.vistorias_processos vp ON vp.id_processo = sp.id JOIN sistema.vistorias_agendamentos va ON va.id_vistoria = vp.id_vistoria JOIN sistema.agendamentos a ON a.id = va.id_agendamento AND a.dt_agendamento > sa.dt_criacao::date JOIN sistema.vistorias v ON v.id = va.id_vistoria JOIN sistema.vistorias_coberturas vc ON vc.id_vistoria = v.id LEFT JOIN sinistro.vistorias_avisos sva ON sva.id_vistoria = v.id AND sva.id_aviso = sa.id JOIN sistema.tipos_vistoria tv ON tv.id = v.id_tipo AND tv.ds_chave_tipos_vistoria = 'PRELIMINAR' WHERE sp.id_proposta = pr.id AND vc.id_cobertura = pc.id LIMIT 1 ) as dt_agendamento_preliminar ,( SELECT to_char(lp.dt_vistoria, 'dd/mm/yyyy') as dt_vistoria FROM sinistro.processos sp JOIN sinistro.vistorias_processos vp ON vp.id_processo = sp.id JOIN sistema.vistorias_agendamentos va ON va.id_vistoria = vp.id_vistoria JOIN sistema.agendamentos a ON a.id = va.id_agendamento AND a.dt_agendamento > sa.dt_criacao::date JOIN sistema.vistorias v ON v.id = va.id_vistoria JOIN sistema.vistorias_coberturas vc ON vc.id_vistoria = v.id JOIN sinistro.laudos_preliminares lp ON lp.id_processo = sp.id AND lp.id_cobertura = pc.id JOIN sistema.tipos_vistoria tv ON tv.id = v.id_tipo AND tv.ds_chave_tipos_vistoria = 'PRELIMINAR' JOIN sinistro.vistorias_avisos sva ON sva.id_vistoria = v.id AND sva.id_aviso = sa.id JOIN sistema.vistorias_laudos_preliminares vlp ON vlp.id_vistoria = v.id AND vlp.id_laudo_preliminar = lp.id WHERE sp.id_proposta = pr.id AND vc.id_cobertura = pc.id LIMIT 1 ) as dt_vistoria_preliminar , CASE WHEN sa.fl_dispensa_preliminar = true THEN 'Sim' ELSE 'Não' END AS fl_dispensa_preliminar ,( SELECT to_char(a.dt_agendamento, 'dd/mm/yyyy') as dt_agendamento FROM sinistro.processos sp JOIN sinistro.vistorias_processos vp ON vp.id_processo = sp.id JOIN sistema.vistorias_agendamentos va ON va.id_vistoria = vp.id_vistoria JOIN sistema.agendamentos a ON a.id = va.id_agendamento AND a.dt_agendamento >= sa.dt_criacao::date JOIN sistema.vistorias v ON v.id = va.id_vistoria JOIN sistema.vistorias_coberturas vc ON vc.id_vistoria = v.id JOIN sistema.tipos_vistoria tv ON tv.id = v.id_tipo AND tv.ds_chave_tipos_vistoria = 'FINAL' JOIN sinistro.avisos ON avisos.id_proposta = sp.id_proposta AND avisos.id = sa.id WHERE sp.id_proposta = pr.id AND vc.id_cobertura = pc.id LIMIT 1 ) as dt_agendamento_final ,( SELECT to_char(lf.dt_vistoria, 'dd/mm/yyyy') as dt_vistoria FROM sinistro.processos sp JOIN sinistro.vistorias_processos vp ON vp.id_processo = sp.id JOIN sistema.vistorias_agendamentos va ON va.id_vistoria = vp.id_vistoria JOIN sistema.agendamentos a ON a.id = va.id_agendamento AND a.dt_agendamento >= sa.dt_criacao::date JOIN sistema.vistorias v ON v.id = va.id_vistoria JOIN sistema.vistorias_coberturas vc ON vc.id_vistoria = v.id JOIN sinistro.laudos_finais lf ON lf.id_processo = sp.id AND lf.id_cobertura = pc.id JOIN sistema.tipos_vistoria tv ON tv.id = v.id_tipo AND tv.ds_chave_tipos_vistoria = 'FINAL' JOIN sinistro.vistorias_avisos sva ON sva.id_vistoria = v.id AND sva.id_aviso = sa.id JOIN sistema.vistorias_laudos_finais vlf ON vlf.id_vistoria = v.id AND vlf.id_laudo_final = lf.id WHERE sp.id_proposta = pr.id AND vc.id_cobertura = pc.id LIMIT 1 ) as dt_vistoria_final ,sinistro.fn_get_eventos_aviso_sinistro(sa.id) as eventos ,ss.ds_status FROM seguro.propostas AS pr JOIN sinistro.avisos AS sa ON pr.id = sa.id_proposta JOIN sinistro.avisos_eventos AS sae ON sa.id = sae.id_aviso JOIN produto.eventos AS pe ON pe.id = sae.id_evento JOIN produto.coberturas_eventos AS pce ON pce.id_evento = pe.id JOIN produto.coberturas AS pc ON pc.id = pce.id_cobertura JOIN produto.produtos AS pd ON pd.id = pr.id_produto JOIN produto.produtos_coberturas AS ppc ON ppc.id_produto = pd.id AND ppc.id_cobertura = pc.id JOIN seguro.propostas_proponentes as pp ON pp.id_proposta = pr.id JOIN seguro.proponentes as sprop ON sprop.cpf_cnpj = pp.cpf_cnpj JOIN seguro.propostas_propriedades as ppr ON ppr.id_proposta = pr.id JOIN sistema.municipios as sm ON sm.id = ppr.id_municipio JOIN sistema.estados se ON se.id = sm.id_estado JOIN sistema.status as ss ON ss.id = sa.id_status JOIN seguro.propostas_endosso spe ON spe.id_proposta = pr.id AND spe.fl_proposta_vigente = true JOIN sinistro.processos as sp ON sp.id_proposta = pr.id JOIN sinistro.processos_empresas pem ON pem.id_processo = sp.id AND pem.fl_ativo = true JOIN sistema.empresas sem ON sem.id = pem.id_empresa LEFT JOIN sinistro.vistorias_avisos as sva ON sa.id = sva.id_aviso LEFT JOIN sistema.vistorias as sv ON sv.id = sva.id_vistoria WHERE ".$filtro." pr.fl_ativo = true AND sa.id_status <> 168 AND ppc.fl_exibir_regulacao_perdas = true AND pr.fl_del = false AND pr.fl_ativo AND pc.id not in (9,12,16,23,25,26) GROUP BY sa.id , pr.id_proposta_mae , pr.id , pr.nr_apolice , sa.dt_criacao , ds_nome_cobertura , pc.id , pd.ds_nome_produto , pp.ds_nome_proponente , sm.ds_nome_municipio , se.ds_sigla , sa.dt_aceitacao , sa.dt_sinistro , sem.ds_nome_fantasia , ss.ds_status , sa.fl_dispensa_preliminar , sprop.id ORDER BY sa.id "; return $this->fetchAll($sql); } public function relatorioLaudosFinaisPendentes($id_safra="",$arProduto="") { $filtro = ($id_safra) ? "pd.id_safra=".$id_safra." and " : ''; if(is_array($arProduto)) { $filtro .= "pd.id in (".implode("," , $arProduto).") and "; } $sql = " select pr.id_proposta_mae as id_proposta ,sp.id as id_processo ,av.id as id_aviso ,to_char(av.dt_sinistro,'DD/MM/YYYY') as dt_sinistro ,to_char(av.dt_aceitacao,'DD/MM/YYYY') as dt_aceitacao ,sprop.id as id_proponente ,m.ds_nome_municipio ,es.ds_sigla ,e.ds_nome_fantasia ,pd.ds_nome_produto ,its.nr_item_segurado ,vr.ds_nome_variedade ,va.id_vistoria ,to_char(ag.dt_agendamento,'DD/MM/YYYY') as dt_agendamento ,co.ds_nome_cobertura ,case when pc.fl_vistoria_preliminar = false then ( select case when lf_sub.id > 0 then 'Sim' else 'Não' end from sinistro.laudos_finais lf_sub ,sinistro.laudos_finais_itens_segurados lfis_sub ,sistema.vistorias_laudos_finais vlf_sub ,sinistro.vistorias_avisos va_sub where lfis_sub.id_laudo_final = lf_sub.id and vlf_sub.id_laudo_final = lf_sub.id and va_sub.id_vistoria = vlf_sub.id_vistoria and lfis_sub.id_item_segurado = its.id and lf_sub.id_cobertura = vc.id_cobertura and va_sub.id_aviso = av.id limit 1 ) else ( select case when lpis.id_laudo_preliminar > 0 then 'Sim' else 'Não' end from sinistro.laudos_preliminares lp ,sinistro.laudos_preliminares_itens_segurados lpis ,sistema.vistorias_laudos_preliminares vlp ,sinistro.vistorias_avisos via where lpis.id_laudo_preliminar = lp.id and vlp.id_laudo_preliminar = lp.id and via.id_vistoria = vlp.id_vistoria and lp.id_processo = sp.id and lp.id_cobertura = vc.id_cobertura and lpis.id_item_segurado = its.id and via.id_aviso = av.id limit 1 ) end as id_laudo_final , (select case when lf_sub.id > 0 and lfis_sub.vl_percent_perdas is not null then 'Sim' when lf_sub.id > 0 and co.id = 11 then 'Sim' when lfis_sub.id_motivo_quadra_nao_vistoriada is not null then 'Sim' else 'Não' end from sinistro.laudos_finais lf_sub ,sinistro.laudos_finais_itens_segurados lfis_sub ,sistema.vistorias_laudos_finais vlf_sub ,sinistro.vistorias_avisos va_sub where lfis_sub.id_laudo_final = lf_sub.id and vlf_sub.id_laudo_final = lf_sub.id and va_sub.id_vistoria = vlf_sub.id_vistoria and lfis_sub.id_item_segurado = its.id and lf_sub.id_cobertura = vc.id_cobertura and va_sub.id_aviso = av.id limit 1 ) as digitado , (select case when lf_sub.id > 0 and lfis_sub.vl_percent_perdas is not null then lfis_sub.dt_vistoria when lf_sub.id > 0 and co.id = 11 then lfis_sub.dt_vistoria when lfis_sub.id_motivo_quadra_nao_vistoriada is not null then lfis_sub.dt_vistoria else null end from sinistro.laudos_finais lf_sub ,sinistro.laudos_finais_itens_segurados lfis_sub ,sistema.vistorias_laudos_finais vlf_sub ,sinistro.vistorias_avisos va_sub where lfis_sub.id_laudo_final = lf_sub.id and vlf_sub.id_laudo_final = lf_sub.id and va_sub.id_vistoria = vlf_sub.id_vistoria and lfis_sub.id_item_segurado = its.id and lf_sub.id_cobertura = vc.id_cobertura and va_sub.id_aviso = av.id limit 1 ) as dt_vistoria from seguro.propostas pr inner join seguro.propostas_endosso pre on pre.id_proposta = pr.id and pre.fl_proposta_vigente = true ,seguro.itens_segurados its ,produto.variedades vr ,seguro.propostas_proponentes pp ,seguro.proponentes sprop ,seguro.propostas_propriedades po ,sistema.municipios m ,sistema.estados es ,produto.produtos pd ,sinistro.avisos av ,sinistro.vistorias_avisos va ,sistema.vistorias v ,sistema.tipos_vistoria tv ,sistema.vistorias_agendamentos vg ,sistema.vistorias_coberturas vc ,produto.produtos_coberturas pc ,produto.coberturas co ,sistema.agendamentos ag ,sinistro.processos sp ,sinistro.processos_empresas pe ,sistema.empresas e where ".$filtro." its.id_proposta = pr.id and its.id_variedade = vr.id and pd.id = pr.id_produto and pp.id_proposta = pr.id and sprop.cpf_cnpj = pp.cpf_cnpj and po.id_proposta = pr.id and m.id = po.id_municipio and es.id = m.id_estado and sp.id_proposta = pr.id and av.id_proposta = pr.id and va.id_aviso = av.id and v.id = va.id_vistoria and v.id_tipo = tv.id and vc.id_vistoria = va.id_vistoria and co.id = vc.id_cobertura and pc.id_produto = pd.id and pc.id_cobertura = co.id and vg.id_vistoria = va.id_vistoria and ag.id = vg.id_agendamento and pe.id_processo = sp.id and e.id = pe.id_empresa and tv.ds_tipo = 'Final' and pe.fl_ativo = true and sp.id_status <> 35 order by pr.id, va.id_vistoria, its.nr_item_segurado "; return $this->fetchAll($sql); } public function relatorioAvisos($id_safra="", $arProduto="", array $estados = [], array $municipios = [], int $idEmpresa = null) { $User = Zend_Auth::getInstance()->getIdentity(); $Select = $this->select()->setIntegrityCheck(false); $Select->from(array('sa' => 'sinistro.avisos'), array('id as id_aviso', "sinistro.fn_get_eventos_aviso_sinistro(sa.id) as ds_eventos", "to_char(dt_aceitacao, 'dd/mm/yyyy HH24:MI:SS') as dt_aceitacao_hora", "case when sa.id_status = 29 then to_char(ar.dt_criacao, 'dd/mm/yyyy') when sa.id_status = 168 then to_char(sa.dt_alteracao, 'dd/mm/yyyy') else to_char(dt_aceitacao, 'dd/mm/yyyy') end as dt_aceitacao", "to_char(dt_sinistro, 'dd/mm/yyyy') as dt_sinistro", "to_char(sa.dt_criacao, 'dd/mm/yyyy') as dt_criacao", "to_char(sa.dt_criacao, 'dd/mm/yyyy HH24:MI:SS') as dt_criacao_hora", "fl_transmissao_seguradora", "fl_ocorrencia_replantio", "fl_ocorrencia_queda_parreiral")); $Select->joinInner(array('pr' => 'seguro.propostas'), 'sa.id_proposta = pr.id', array('pr.id_proposta_mae as id_proposta','nr_apolice')); $Select->joinInner(array('pre' => 'seguro.propostas_endosso'), 'pre.id_proposta = pr.id and pre.fl_proposta_vigente=true', false); $Select->joinLeft(array('pvp' => 'seguro.propostas_vistorias_previas'), 'pvp.id_proposta = pr.id', false); $Select->joinLeft(array('ve' => 'sistema.vistorias_empresas'), 've.id_vistoria = pvp.id_vistoria and ve.fl_ativo=true', false); $Select->joinLeft(array('emp' => 'sistema.empresas'), 'emp.id = ve.id_empresa', 'emp.ds_nome_fantasia as empresa_previa'); $Select->joinInner(array('pd' => 'produto.produtos'), 'pd.id = pr.id_produto', 'ds_nome_produto'); $Select->joinInner(array('sf' => 'produto.safras'), 'sf.id = pd.id_safra', 'ds_nome_safra'); $Select->joinInner(array('pc' => 'seguro.propostas_corretores'), 'pc.id_proposta = pr.id', false); $Select->joinInner(array('co' => 'sistema.corretores'), 'co.id_usuario = pc.id_usuario', 'ds_nome_abreviado'); $Select->joinInner(array('ist' => 'sumario.itens_segurados'), 'ist.id_proposta = pr.id', array('vl_area as soma_area', 'vl_lmga as soma_lmga')); $Select->joinInner(array('pp' => 'seguro.propostas_propriedades'), 'pp.id_proposta = pr.id', false); $Select->joinInner(array('ppo' => 'seguro.propostas_proponentes'), 'ppo.id_proposta = pr.id', 'ds_nome_proponente'); $Select->joinInner(array('m1' => 'sistema.municipios'), 'm1.id = pp.id_municipio', 'ds_nome_municipio as municipio_propriedade'); $Select->joinInner(array('e1' => 'sistema.estados'), 'e1.id = m1.id_estado', 'ds_sigla as uf_propriedade'); $Select->joinInner(array('s' => 'sistema.status'), 's.id = sa.id_status', 'ds_chave'); $Select->joinInner(array('u' => 'sistema.usuarios'), 'u.id = sa.id_usuario_criacao', 'ds_nome_usuario'); $Select->joinInner(array('u2' => 'sistema.usuarios'), 'u2.id = pr.id_usuario_criacao', array('tp_usuario', 'ds_nome_usuario as ds_nome_preposto')); $Select->joinLeft(array('sp' => 'sinistro.processos'), 'sp.id_proposta = pr.id', array('id as id_processo', 'id_processo_seguradora')); $Select->joinLeft(array('pe' => 'sinistro.processos_empresas'), 'pe.id_processo = sp.id and pe.fl_ativo=true', false); $Select->joinLeft(array('vp' => 'sinistro.vistorias_processos'), 'vp.id_processo = sp.id', false); $Select->joinLeft(array('s2' => 'sistema.status'), 's2.id = sp.id_status', array('ds_chave as status_processo')); $Select->joinLeft(array('e' => 'sistema.empresas'), 'e.id = pe.id_empresa', 'ds_nome_fantasia'); $Select->joinLeft(array('m2' => 'sistema.municipios'), 'm2.id = e.id_municipio', 'ds_nome_municipio as municipio_empresa'); $Select->joinLeft(array('e2' => 'sistema.estados'), 'e2.id = m2.id_estado', 'ds_sigla as uf_empresa'); $Select->joinLeft(array('ar' => 'sinistro.avisos_recusas'), 'ar.id_aviso = sa.id', 'ds_motivo_recusa'); $Select->joinLeft(array('tr' => 'atendimento.tipo_recusa_sinistro'), 'tr.id = ar.id_tipo_recusa', 'ds_tipo_recusa'); $Select->joinLeft(array('lg' => 'sinistro.avisos_log_mudanca_status'), 'lg.id_aviso = sa.id and lg.id_status_novo=168', 'ds_motivo as ds_motivo_cancelamento'); $Select->joinLeft(array('va' => 'sinistro.vistorias_avisos'), 'va.id_aviso = sa.id', 'id_vistoria'); $Select->joinLeft(array('sv' => 'sistema.vistorias'), 'sv.id = vp.id_vistoria', array('id as id_sistema_vistorias', "to_char(sv.dt_inicio, 'dd/mm/yyyy') as dt_vistoria_preliminar_realizada")); $Select->joinLeft(array('sva' => 'sistema.vistorias_agendamentos'), 'sva.id_vistoria = sv.id', 'id_agendamento'); $Select->joinLeft(array('sag' => 'sistema.agendamentos'), 'sag.id = sva.id_agendamento', "to_char(dt_agendamento, 'dd/mm/yyyy') as dt_agendamento"); $Select->joinLeft(array('slf' => 'sinistro.laudos_finais'), 'slf.id_processo = sp.id', "to_char(slf.dt_vistoria, 'dd/mm/yyyy') as dt_vistoria_final_realizada"); $Select->joinLeft(array('u3' => 'sistema.usuarios'), 'u3.id = sa.id_usuario_alteracao', array('ds_nome_usuario as ds_usuario_alteracao')); $Select->order(array('sa.dt_aceitacao desc', 'pr.id')); if ($id_safra) { $Select->where('pd.id_safra = ?', $id_safra); } if (is_array($arProduto)) { $Select->where('pd.id in ('.implode("," , $arProduto).')'); } if ($User->tp_usuario == 'C') { $Select->where('co.id_usuario = ?', $User->id); } if ($User->tp_usuario == 'P') { $Select->joinInner(array('prep' => 'sistema.prepostos'), 'co.id = prep.id_corretor', false); $Select->where('pr.id_usuario_criacao = ?', $User->id); $Select->where('prep.id_usuario = ?', $User->id); } if ($User->tp_usuario == 'V') { $Select->joinInner(array('func' => 'sistema.funcionarios'), 'pe.id_empresa = func.id_empresa', false); $Select->where('func.id_usuario = ?', $User->id); $Select->where("s.ds_chave <> 'RECUSADO'"); } if (!empty($estados)) { $Select->where('e1.id in ('.implode("," , $estados).')'); } if (!empty($municipios)) { $Select->where('m1.id in ('.implode("," , $municipios).')'); } if (!empty($idEmpresa)) { $Select->where("e.id = {$idEmpresa}"); } return $this->fetchAll($Select); } public function relatorioAvisosAutomatico($id_safra="", $arProduto="", array $estados = [], array $municipios = [], int $idEmpresa = null) { $User = Zend_Auth::getInstance()->getIdentity(); $Select = $this->select()->setIntegrityCheck(false); $Select->from(array('sa' => 'sinistro.avisos'), array('id as id_aviso',"to_char(dt_aceitacao, 'dd/mm/yyyy HH24:MI:SS') as dt_aceitacao_hora", "sinistro.fn_get_eventos_aviso_sinistro(sa.id) as ds_eventos", "case when sa.id_status = 29 then to_char(ar.dt_criacao, 'dd/mm/yyyy') when sa.id_status = 168 then to_char(sa.dt_alteracao, 'dd/mm/yyyy') else to_char(dt_aceitacao, 'dd/mm/yyyy') end as dt_aceitacao", "to_char(dt_sinistro, 'dd/mm/yyyy') as dt_sinistro", "to_char(sa.dt_criacao, 'dd/mm/yyyy') as dt_criacao", "to_char(sa.dt_criacao, 'dd/mm/yyyy HH24:MI:SS') as dt_criacao_hora", "fl_transmissao_seguradora", "fl_ocorrencia_replantio", "fl_ocorrencia_queda_parreiral")); $Select->joinInner(array('pr' => 'seguro.propostas'), 'sa.id_proposta = pr.id', array('pr.id_proposta_mae as id_proposta','nr_apolice')); $Select->joinInner(array('pre' => 'seguro.propostas_endosso'), 'pre.id_proposta = pr.id and pre.fl_proposta_vigente=true', false); $Select->joinLeft(array('pvp' => 'seguro.propostas_vistorias_previas'), 'pvp.id_proposta = pr.id', false); $Select->joinLeft(array('ve' => 'sistema.vistorias_empresas'), 've.id_vistoria = pvp.id_vistoria and ve.fl_ativo=true', false); $Select->joinLeft(array('emp' => 'sistema.empresas'), 'emp.id = ve.id_empresa', 'emp.ds_nome_fantasia as empresa_previa'); $Select->joinInner(array('pd' => 'produto.produtos'), 'pd.id = pr.id_produto', 'ds_nome_produto'); $Select->joinInner(array('sf' => 'produto.safras'), 'sf.id = pd.id_safra', 'ds_nome_safra'); $Select->joinInner(array('pc' => 'seguro.propostas_corretores'), 'pc.id_proposta = pr.id', false); $Select->joinInner(array('co' => 'sistema.corretores'), 'co.id_usuario = pc.id_usuario', 'ds_nome_abreviado'); $Select->joinInner(array('ist' => 'sumario.itens_segurados'), 'ist.id_proposta = pr.id', array('vl_area as soma_area', 'vl_lmga as soma_lmga')); $Select->joinInner(array('pp' => 'seguro.propostas_propriedades'), 'pp.id_proposta = pr.id', false); $Select->joinInner(array('ppo' => 'seguro.propostas_proponentes'), 'ppo.id_proposta = pr.id', 'ds_nome_proponente'); $Select->joinInner(array('m1' => 'sistema.municipios'), 'm1.id = pp.id_municipio', 'ds_nome_municipio as municipio_propriedade'); $Select->joinInner(array('e1' => 'sistema.estados'), 'e1.id = m1.id_estado', 'ds_sigla as uf_propriedade'); $Select->joinInner(array('s' => 'sistema.status'), 's.id = sa.id_status', 'ds_chave'); $Select->joinInner(array('u' => 'sistema.usuarios'), 'u.id = sa.id_usuario_criacao', 'ds_nome_usuario'); $Select->joinInner(array('u2' => 'sistema.usuarios'), 'u2.id = pr.id_usuario_criacao', array('tp_usuario', 'ds_nome_usuario as ds_nome_preposto')); $Select->joinLeft(array('sp' => 'sinistro.processos'), 'sp.id_proposta = pr.id', array('id as id_processo', 'id_processo_seguradora')); $Select->joinLeft(array('pe' => 'sinistro.processos_empresas'), 'pe.id_processo = sp.id and pe.fl_ativo=true', false); $Select->joinLeft(array('vp' => 'sinistro.vistorias_processos'), 'vp.id_processo = sp.id', false); $Select->joinLeft(array('s2' => 'sistema.status'), 's2.id = sp.id_status', array('ds_chave as status_processo')); $Select->joinLeft(array('e' => 'sistema.empresas'), 'e.id = pe.id_empresa', 'ds_nome_fantasia'); $Select->joinLeft(array('m2' => 'sistema.municipios'), 'm2.id = e.id_municipio', 'ds_nome_municipio as municipio_empresa'); $Select->joinLeft(array('e2' => 'sistema.estados'), 'e2.id = m2.id_estado', 'ds_sigla as uf_empresa'); $Select->joinLeft(array('ar' => 'sinistro.avisos_recusas'), 'ar.id_aviso = sa.id', 'ds_motivo_recusa'); $Select->joinLeft(array('tr' => 'atendimento.tipo_recusa_sinistro'), 'tr.id = ar.id_tipo_recusa', 'ds_tipo_recusa'); $Select->joinLeft(array('lg' => 'sinistro.avisos_log_mudanca_status'), 'lg.id_aviso = sa.id and lg.id_status_novo=168', 'ds_motivo as ds_motivo_cancelamento'); $Select->joinLeft(array('va' => 'sinistro.vistorias_avisos'), 'va.id_aviso = sa.id', 'id_vistoria'); $Select->joinLeft(array('sv' => 'sistema.vistorias'), 'sv.id = vp.id_vistoria', array('id as id_sistema_vistorias', "to_char(sv.dt_inicio, 'dd/mm/yyyy') as dt_vistoria_preliminar_realizada")); $Select->joinLeft(array('sva' => 'sistema.vistorias_agendamentos'), 'sva.id_vistoria = sv.id', 'id_agendamento'); $Select->joinLeft(array('sag' => 'sistema.agendamentos'), 'sag.id = sva.id_agendamento', "to_char(dt_agendamento, 'dd/mm/yyyy') as dt_agendamento"); $Select->joinLeft(array('slf' => 'sinistro.laudos_finais'), 'slf.id_processo = sp.id', "to_char(slf.dt_vistoria, 'dd/mm/yyyy') as dt_vistoria_final_realizada"); $Select->joinLeft(array('u3' => 'sistema.usuarios'), 'u3.id = sa.id_usuario_alteracao', array('ds_nome_usuario as ds_usuario_alteracao')); $Select->joinLeft(array('spp'=>'seguro.proponentes'), 'spp.ds_nome_proponente = ppo.ds_nome_proponente', 'id as id_preposto'); $Select->order(array('sa.dt_aceitacao desc', 'pr.id')); if ($id_safra) { $Select->where('pd.id_safra = ?', $id_safra); } if (is_array($arProduto)) { $Select->where('pd.id in ('.implode("," , $arProduto).')'); } if ($User->tp_usuario == 'C') { $Select->where('co.id_usuario = ?', $User->id); } if ($User->tp_usuario == 'P') { $Select->joinInner(array('prep' => 'sistema.prepostos'), 'co.id = prep.id_corretor', false); $Select->where('pr.id_usuario_criacao = ?', $User->id); $Select->where('prep.id_usuario = ?', $User->id); } if ($User->tp_usuario == 'V') { $Select->joinInner(array('func' => 'sistema.funcionarios'), 'pe.id_empresa = func.id_empresa', false); $Select->where('func.id_usuario = ?', $User->id); $Select->where("s.ds_chave <> 'RECUSADO'"); } if (!empty($estados)) { $Select->where('e1.id in ('.implode("," , $estados).')'); } if (!empty($municipios)) { $Select->where('m1.id in ('.implode("," , $municipios).')'); } if (!empty($idEmpresa)) { $Select->where("e.id = {$idEmpresa}"); } return $this->fetchAll($Select); } public function relatorioAvisosNobre($id_safra="",$arProduto="") { $Select = $this->select()->setIntegrityCheck(false); $Select->from(array('sa' => 'sinistro.avisos'), array('id as id_aviso', "sinistro.fn_get_eventos_aviso_sinistro(sa.id) as ds_eventos", "to_char(dt_aceitacao, 'dd/mm/yyyy HH24:MI:SS') as dt_aceitacao_hora", "to_char(dt_aceitacao, 'dd/mm/yyyy') as dt_aceitacao", "to_char(dt_sinistro, 'dd/mm/yyyy') as dt_sinistro", "to_char(sa.dt_criacao, 'dd/mm/yyyy') as dt_criacao", "to_char(sa.dt_criacao, 'dd/mm/yyyy HH24:MI:SS') as dt_criacao_hora", "fl_transmissao_seguradora")); $Select->joinInner(array('pr' => 'seguro.propostas'), 'sa.id_proposta = pr.id', array('nr_apolice', 'pr.id_proposta_mae as id_proposta')); $Select->joinInner(array('pre' => 'seguro.propostas_endosso'), 'pre.id_proposta = pr.id and pre.fl_proposta_vigente=true', false); $Select->joinInner(array('pd' => 'produto.produtos'), 'pd.id = pr.id_produto', 'ds_nome_produto'); $Select->joinInner(array('pg' => 'produto.produtos_geral'), 'pd.id_produto_geral = pg.id', false); $Select->joinInner(array('cu' => 'produto.culturas'), 'cu.id = pg.id_cultura', 'ds_nome_cultura'); $Select->joinInner(array('ist' => 'seguro.itens_segurados'), 'ist.id_proposta = pr.id', array('nr_item_segurado')); $Select->joinInner(array('pc' => 'seguro.propostas_coberturas'), 'pc.id_proposta = pr.id', false); $Select->joinInner(array('co' => 'produto.coberturas'), 'co.id = pc.id_cobertura', 'ds_nome_cobertura'); $Select->order(array('pr.id', 'sa.id', 'pc.id_cobertura', 'ist.nr_item_segurado')); $Select->where('sa.id_status in (28,30)'); if ($id_safra) { $Select->where('pd.id_safra = ?', $id_safra); } if (is_array($arProduto)) { $Select->where('pd.id in ('.implode("," , $arProduto).')'); } return $this->fetchAll($Select); } public function relatorioDocumentosSinistro($id_safra="",$arProduto="") { $filtro = ($id_safra) ? "pd.id_safra=".$id_safra." and " : ''; if(is_array($arProduto)) { $filtro .= "pd.id in (".implode("," , $arProduto).") and "; } $sql = " SELECT sp.id as id_processo , pr.id_proposta_mae as id_proposta , ta.ds_tipo_arquivo , TO_CHAR(pa.dt_criacao, 'dd/mm/yyyy HH24:mi:ss') as dt_criacao , CASE WHEN pa.fl_conferido = true THEN 'Sim' ELSE 'Não' END as fl_conferido , c.ds_nome_abreviado , s.ds_status , CASE WHEN u.tp_usuario = 'P' THEN p.ds_nome_preposto END as ds_nome_preposto , sp.id_status_arquivos , f.ds_nome_funcionario , TO_CHAR(sp.dt_revisao_arquivos, 'dd/mm/yyyy HH24:mi:ss') AS dt_revisao_arquivos , CASE WHEN pa.dt_criacao IS NOT NULL THEN TO_CHAR(pax.dt_criacao, 'dd/mm/yyyy HH24:mi:ss') END AS dt_ultimo_documento FROM sinistro.processos sp INNER JOIN seguro.propostas pr ON pr.id = sp.id_proposta INNER JOIN seguro.propostas_endosso pre ON pre.id_proposta = pr.id AND pre.fl_proposta_vigente = true INNER JOIN produto.produtos pd ON pd.id = pr.id_produto INNER JOIN seguro.propostas_corretores pc ON pc.id_proposta = pr.id INNER JOIN sistema.corretores c ON c.id_usuario = pc.id_usuario INNER JOIN sistema.status s ON s.id = sp.id_status_arquivos INNER JOIN sistema.usuarios u ON u.id = pr.id_usuario_criacao LEFT JOIN sinistro.processos_arquivos pa ON pa.fl_del = false AND pa.id_processo = sp.id LEFT JOIN sistema.tipo_arquivo_processo ta ON ta.id = pa.id_tipo_arquivo LEFT JOIN sistema.funcionarios f ON f.id_usuario = sp.id_revisor_arquivos LEFT JOIN sistema.prepostos p ON p.id_usuario = u.id LEFT JOIN (SELECT MAX(dt_criacao) as dt_criacao , id_processo FROM sinistro.processos_arquivos GROUP BY id_processo) as pax ON sp.id = pax.id_processo WHERE ".substr($filtro, 0, -4)." ORDER BY sp.id , ta.ds_tipo_arquivo"; return $this->fetchAll($sql); } public function relatorioInicioColheita($id_safra="",$arProduto="") { $Sql = $this->select()->setIntegrityCheck(false); $Sql->from(array('pr' => 'seguro.propostas'), 'pr.id_proposta_mae as id_proposta'); $Sql->join(array('pre' => 'seguro.propostas_endosso'), 'pre.id_proposta = pr.id and pre.fl_proposta_vigente = true', null); $Sql->join(array('pd' => 'produto.produtos'), 'pd.id = pr.id_produto', null); $Sql->join(array('sa' => 'produto.safras'), 'sa.id = pd.id_safra', 'ds_nome_safra'); $Sql->join(array('pg' => 'produto.produtos_geral'), 'pg.id = pd.id_produto_geral', null); $Sql->join(array('cu' => 'produto.culturas'), 'cu.id = pg.id_cultura', 'ds_nome_cultura'); $Sql->join(array('it' => 'seguro.itens_segurados'), 'pr.id = it.id_proposta', array('nr_item_segurado', 'ds_item_segurado', 'id as id_item_segurado')); $Sql->join(array('va' => 'produto.variedades'), 'va.id = it.id_variedade', 'ds_nome_variedade'); $Sql->join(array('pp' => 'seguro.propostas_proponentes'), 'pr.id = pp.id_proposta', 'ds_nome_proponente'); $Sql->join(array('ps' => 'seguro.propostas_propriedades'), 'pr.id = ps.id_proposta', null); $Sql->join(array('mu' => 'sistema.municipios'), 'mu.id = ps.id_municipio', 'ds_nome_municipio'); $Sql->join(array('es' => 'sistema.estados'), 'es.id = mu.id_estado', 'ds_sigla'); $Sql->join(array('pc' => 'sinistro.processos'), 'pr.id = pc.id_proposta and pc.id_status <> 35', 'id as id_processo'); $Sql->join(array('pe' => 'sinistro.processos_empresas'), 'pc.id = pe.id_processo', null); $Sql->join(array('em' => 'sistema.empresas'), 'em.id = pe.id_empresa', 'ds_nome_fantasia'); $Sql->join(array('ai' => 'sinistro.avisos_inicio'), 'pr.id = ai.id_proposta', array('id as id_aviso', "to_char(ai.dt_inicio_colheita, 'dd/mm/yyyy') as dt_inicio_colheita", "to_char(ai.dt_criacao, 'dd/mm/yyyy') as dt_criacao")); $Sql->join(array('at' => 'sinistro.avisos_inicio_itens_segurados'), 'it.id = at.id_item_segurado and ai.id = at.id_aviso_inicio', null); $Sql->order(array('pr.id', 'it.nr_item_segurado')); $Sql->where('pr.fl_ativo is true'); $Sql->where('pr.fl_del is false'); $Sql->where('it.fl_ativo is true'); $Sql->where('it.fl_del is false'); // Caso seja um vistoriador $User = Zend_Auth::getInstance()->getIdentity(); if ($User->tp_usuario == 'V') { $Empresas = new Sistema_Model_Empresas(); $Sql->where('em.id = ?', $Empresas->getIdEmpresaUsuarioCorrente()); } // Caso a safra for informada if ($id_safra) { $Sql->where('pd.id_safra = ?', $id_safra); } // Caso o usuario selecione algum produto if (is_array($arProduto)) { $Sql->where('pd.id in ('.implode("," , $arProduto).')'); } return $this->fetchAll($Sql); } public function relatorioIncioColheitaPendente($id_safra="", $arProduto="") { $user = Zend_Auth::getInstance()->getIdentity(); $filtro = ($id_safra) ? 'pd.id_safra='.$id_safra.' and ' : ''; if(is_array($arProduto)) { $filtro .= 'pd.id in ('.implode(',' , $arProduto).') and '; } if ($user->tp_usuario == 'C') { $filtro .= 'cor.id_usuario = '.$user->id.' and '; } if ($user->tp_usuario == 'P') { $filtro .= 'pr.id_usuario_criacao = '.$user->id.' and '; } $sql = " SELECT pr.id_proposta_mae as id_proposta, pr.id, pp.ds_nome_proponente as segurado, c.ds_nome_cultura as cultura, cor.ds_nome_fantasia as corretor, proc.id as processo, m.ds_nome_municipio, e.ds_sigla, itens.nr_itens, CASE WHEN u.tp_usuario = 'P' THEN u.ds_nome_usuario ELSE '' END as sublogin FROM seguro.propostas pr JOIN seguro.propostas_endosso pre ON pre.id_proposta = pr.id AND pre.fl_proposta_vigente = true JOIN produto.produtos pd ON pd.id = pr.id_produto JOIN produto.produtos_geral pdg ON pdg.id = pd.id_produto_geral JOIN produto.culturas c ON c.id = pdg.id_cultura JOIN seguro.propostas_corretores pc ON pc.id_proposta = pr.id JOIN sistema.corretores cor ON cor.id_usuario = pc.id_usuario JOIN sistema.usuarios u ON u.id = cor.id_usuario JOIN sinistro.processos proc ON proc.id_proposta = pr.id JOIN seguro.propostas_proponentes pp ON pp.id_proposta = pr.id JOIN seguro.propostas_propriedades prp ON prp.id_proposta = pr.id JOIN sistema.municipios m ON m.id = prp.id_municipio JOIN sistema.estados e ON e.id = m.id_estado JOIN ( SELECT sis.id_proposta, count(sis.id) as nr_itens FROM seguro.itens_segurados sis WHERE NOT EXISTS ( SELECT * FROM sinistro.avisos_inicio_itens_segurados ais WHERE ais.id_item_segurado = sis.id ) AND vl_lmga > 0 GROUP BY sis.id_proposta ORDER BY sis.id_proposta ) AS itens ON itens.id_proposta = pr.id WHERE ".$filtro." EXISTS ( SELECT * FROM sinistro.avisos sa WHERE sa.id_proposta = pr.id AND sa.id_status = 28) ORDER BY pr.id DESC "; return $this->fetchAll($sql); } public function relatorioPendenciaAvisoEncerramento($id_safra="",$arProduto="") { $user = Zend_Auth::getInstance()->getIdentity(); $filtro = ($id_safra) ? 'pd.id_safra='.$id_safra.' and ' : ''; if(is_array($arProduto)) { $filtro .= 'pd.id in ('.implode(',' , $arProduto).') and '; } if ($user->tp_usuario == 'C') { $filtro .= 'cor.id_usuario = '.$user->id.' and '; } if ($user->tp_usuario == 'P') { $filtro .= 'pr.id_usuario_criacao = '.$user->id.' and '; } $sql = " SELECT pr.id_proposta_mae as id_proposta, pp.ds_nome_proponente as segurado, c.ds_nome_cultura as cultura, cor.ds_nome_fantasia as corretor, proc.id as processo, m.ds_nome_municipio, e.ds_sigla, itens.nr_itens, CASE WHEN u.tp_usuario = 'P' THEN u.ds_nome_usuario ELSE '' END as sublogin, to_char(pr.dt_vigencia_fim, 'DD/MM/YYYY') as dt_vigencia_fim FROM seguro.propostas pr JOIN seguro.propostas_endosso pre ON pre.id_proposta = pr.id AND pre.fl_proposta_vigente = true JOIN produto.produtos pd ON pd.id = pr.id_produto JOIN produto.produtos_geral pdg ON pdg.id = pd.id_produto_geral JOIN produto.culturas c ON c.id = pdg.id_cultura JOIN seguro.propostas_corretores pc ON pc.id_proposta = pr.id JOIN sistema.corretores cor ON cor.id_usuario = pc.id_usuario JOIN sistema.usuarios u ON u.id = cor.id_usuario JOIN sinistro.processos proc ON proc.id_proposta = pr.id JOIN seguro.propostas_proponentes pp ON pp.id_proposta = pr.id JOIN seguro.propostas_propriedades prp ON prp.id_proposta = pr.id JOIN sistema.municipios m ON m.id = prp.id_municipio JOIN sistema.estados e ON e.id = m.id_estado JOIN ( SELECT sis.id_proposta, count(sis.id) as nr_itens FROM seguro.itens_segurados sis WHERE NOT EXISTS ( SELECT * FROM sinistro.avisos_encerramento_itens_segurados ais WHERE ais.id_item_segurado = sis.id ) AND vl_lmga > 0 GROUP BY sis.id_proposta ORDER BY sis.id_proposta ) AS itens ON itens.id_proposta = pr.id JOIN sistema.status s ON s.id = pr.id_status AND s.ds_chave NOT IN ('PROPOSTA_CANCELADA', 'APOLICE_CANCELADA') JOIN sistema.status sp ON sp.id = proc.id_status AND sp.ds_chave <> 'CANCELADO' WHERE ". $filtro ." EXISTS ( SELECT * FROM sinistro.avisos sa WHERE sa.id_proposta = pr.id AND sa.id_status = 28 ) ORDER BY pr.id DESC "; return $this->fetchAll($sql); } public function relatorioFinalReplantio($id_safra='', $arProduto='') { $user = Zend_Auth::getInstance()->getIdentity(); $filtro = ($id_safra) ? 'pd.id_safra = ' . $id_safra . ' and ' : ''; if (is_array($arProduto)) { $filtro .= 'pd.id in (' . implode(',', $arProduto) . ') and '; } if ($user->tp_usuario == 'C') { $filtro .= 'cor.id_usuario = '.$user->id.' and '; } if ($user->tp_usuario == 'P') { $filtro .= 'pr.id_usuario_criacao = '.$user->id.' and '; } $sql = ' SELECT pr.id_proposta_mae AS proposta, pr.nr_apolice AS apolice, c.ds_nome_cultura AS cultura, cor.ds_nome_fantasia AS corretor, sub_processo.id AS processo, sub_processo.ds_nome_fantasia AS empresa, iseg.nr_item_segurado AS item, iseg.ds_item_segurado AS quadra, v.ds_nome_variedade, to_char(afr.dt_final_replantio, \'DD/MM/YYYY\') AS dt_final_replantio, to_char(afr.dt_criacao, \'DD/MM/YYYY\') AS dt_envio_replantio FROM seguro.propostas pr JOIN seguro.propostas_endosso pre ON pre.id_proposta = pr.id JOIN seguro.itens_segurados iseg ON iseg.id_proposta = pr.id LEFT JOIN sinistro.avisos_final_replantio_itens_segurados aeis ON aeis.id_item_segurado = iseg.id AND aeis.fl_del IS FALSE AND aeis.fl_ativo IS TRUE LEFT JOIN sinistro.avisos_final_replantio afr ON afr.id = aeis.id_aviso_final_replantio LEFT JOIN seguro.propostas_beneficiarios pb ON pr.id = pb.id_proposta LEFT JOIN sistema.bancos b ON b.id = pb.id_banco JOIN produto.produtos pd ON pd.id = pr.id_produto JOIN produto.produtos_geral pdg ON pdg.id = pd.id_produto_geral JOIN produto.culturas c ON c.id = pdg.id_cultura JOIN seguro.propostas_corretores pc ON pc.id_proposta = pr.id JOIN sistema.corretores cor ON cor.id_usuario = pc.id_usuario LEFT JOIN sinistro.processos proc ON proc.id_proposta = pr.id JOIN produto.variedades v ON v.id = iseg.id_variedade inner join (SELECT processos.id, empresas.ds_nome_fantasia, propostas.id_proposta_mae from seguro.propostas inner JOIN seguro.propostas_endosso ON propostas_endosso.id_proposta = propostas.id and propostas_endosso.fl_proposta_vigente is true inner JOIN sinistro.processos ON processos.id_proposta = propostas.id inner JOIN sinistro.processos_empresas ON processos_empresas.id_processo = processos.id inner JOIN sistema.empresas ON empresas.id = processos_empresas.id_empresa) sub_processo on sub_processo.id_proposta_mae = pr.id_proposta_mae WHERE ' . $filtro . ' iseg.vl_lmga > 0 AND (proc.id_status is null OR proc.id_status <> 35) AND afr.id IS NOT NULL ORDER BY pr.id, iseg.nr_item_segurado '; return $this->fetchAll($sql); } public function relatorioEncerramentoColheita($id_safra="",$arProduto="") { $filtro = ($id_safra) ? "pd.id_safra=".$id_safra." and " : ''; if(is_array($arProduto)) { $filtro .= "pd.id in (".implode("," , $arProduto).") and "; } $sql = " select pr.id_proposta_mae as proposta, pr.nr_apolice as apolice, pr.dt_vigencia_fim, c.ds_nome_cultura as cultura, cor.ds_nome_fantasia as corretor, proc.id as processo, e.ds_nome_fantasia as empresa, iseg.nr_item_segurado as item, iseg.ds_item_segurado as quadra, iseg.vl_lmga as lmga, v.ds_nome_variedade, (select to_char(dt_encerramento_colheita, 'dd/mm/yyyy') from sinistro.avisos_encerramento where id = aeis.id_aviso_encerramento) as data_encerramento, (select to_char(dt_criacao, 'dd/mm/yyyy') from sinistro.avisos_encerramento where id = aeis.id_aviso_encerramento) as data_envio_encerramento /* , CASE WHEN (select count(*) from sinistro.liquidacoes_itens_segurados_coberturas isc, produto.produtos_coberturas pc where pc.id_produto = pr.id_produto and pc.id_cobertura = isc.id_cobertura and pc.fl_cobertura_principal = true and isc.id_item_segurado=iseg.id) > 0 THEN 'Sim' ELSE 'Não' END as count_indenizacoes, CASE WHEN (select count(*) from sinistro.liquidacoes_itens_segurados_coberturas isc, produto.produtos_coberturas pc where pc.id_produto = pr.id_produto and pc.id_cobertura = isc.id_cobertura and pc.id_cobertura = 16 and isc.id_item_segurado=iseg.id) > 0 THEN 'Sim' ELSE 'Não' END as count_indenizacoes_fito, CASE WHEN (select count(*) from sinistro.liquidacoes_itens_segurados_coberturas isc, produto.produtos_coberturas pc where pc.id_produto = pr.id_produto and pc.id_cobertura = isc.id_cobertura and pc.id_cobertura = 15 and isc.id_item_segurado=iseg.id) > 0 THEN 'Sim' ELSE 'Não' END as count_indenizacoes_prod_minima, CASE WHEN (select count(*) from sinistro.liquidacoes_itens_segurados_coberturas isc, produto.produtos_coberturas pc where pc.id_produto = pr.id_produto and pc.id_cobertura = isc.id_cobertura and isc.id_item_segurado=iseg.id and pc.id_cobertura not in (15,16) and pc.fl_cobertura_principal = false) > 0 THEN 'Sim' ELSE 'Não' END as count_indenizacoes_adicionais*/ from seguro.propostas pr inner join seguro.propostas_endosso pre on pre.id_proposta = pr.id and pre.fl_proposta_vigente = true inner join seguro.itens_segurados iseg on iseg.id_proposta = pr.id left join sinistro.avisos_encerramento_itens_segurados aeis on aeis.id_item_segurado = iseg.id and aeis.fl_del = false and aeis.fl_ativo = true, 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, produto.variedades v where ".$filtro." pd.id = pr.id_produto 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.vl_lmga > 0 and v.id = iseg.id_variedade and proc.id_status <> 35 order by pr.id, iseg.nr_item_segurado "; return $this->fetchAll($sql); } public function relatorioLaudosDigitados($id_safra="",$arProduto="") { $filtro = ($id_safra) ? "pd.id_safra=".$id_safra." and " : ''; if(is_array($arProduto)) { $filtro .= "pd.id in (".implode("," , $arProduto).") and "; } $sql = " (select sp.id as id_processo, pr.id_proposta_mae as id_proposta, av.id as id_aviso, to_char(av.dt_aceitacao,'DD/MM/YYYY') as dt_aceitacao, 'FINAL' as tp_laudo, its.nr_item_segurado, to_char(ag.dt_agendamento,'DD/MM/YYYY') as dt_agendamento, e.ds_nome_fantasia, to_char(lfis.dt_criacao,'DD/MM/YYYY') as dt_digitacao, to_char(lfis.dt_vistoria,'DD/MM/YYYY') as dt_vistoria, pd.ds_nome_produto, m.ds_nome_municipio, es.ds_sigla, CASE WHEN (lf.fl_transmitido_tablet IS TRUE) THEN 'Digital' WHEN (lf.fl_transmitido_tablet IS NULL) THEN '-' ELSE 'Manual' END AS fl_transmitido_tablet from sinistro.processos sp inner join sinistro.laudos_finais lf on lf.id_processo = sp.id inner join sistema.status s on s.id = lf.id_status inner join sinistro.laudos_finais_itens_segurados lfis on lfis.id_laudo_final = lf.id inner join seguro.itens_segurados its on its.id = lfis.id_item_segurado, sinistro.avisos av, seguro.propostas pr inner join seguro.propostas_endosso pre on pre.id_proposta = pr.id and pre.fl_proposta_vigente = true, seguro.propostas_propriedades pp, sistema.municipios m, sistema.estados es, produto.produtos pd, sinistro.processos_empresas pe, sistema.empresas e, sinistro.vistorias_avisos va, sistema.vistorias v, sistema.vistorias_laudos_finais vlf, sistema.tipos_vistoria tv, sistema.vistorias_agendamentos vag, sistema.agendamentos ag where ".$filtro." tv.ds_tipo = 'Final' and av.id_proposta = sp.id_proposta and pr.id = sp.id_proposta and pp.id_proposta = pr.id and m.id = pp.id_municipio and es.id = m.id_estado and pd.id = pr.id_produto and pe.id_processo = sp.id and pe.fl_ativo = true and v.id = vlf.id_vistoria and vlf.id_laudo_final = lf.id and e.id = pe.id_empresa and va.id_aviso = av.id and v.id = va.id_vistoria and tv.id = v.id_tipo and vag.id_vistoria = v.id and ag.id = vag.id_agendamento order by sp.id,av.id,its.nr_item_segurado ) union ( select sp.id as id_processo, pr.id_proposta_mae as id_proposta, av.id as id_aviso, to_char(av.dt_aceitacao,'DD/MM/YYYY') as dt_aceitacao, 'PRELIMINAR' as tp_laudo, its.nr_item_segurado, to_char(ag.dt_agendamento,'DD/MM/YYYY') as dt_agendamento, e.ds_nome_fantasia, to_char(lfis.dt_criacao,'DD/MM/YYYY') as dt_digitacao, case when to_char(v.dt_inicio,'DD/MM/YYYY') is not null then to_char(v.dt_inicio,'DD/MM/YYYY') else to_char(lp.dt_vistoria,'DD/MM/YYYY') end as dt_vistoria, pd.ds_nome_produto, m.ds_nome_municipio, es.ds_sigla, 'Não' as fl_transmitido_tablet from sinistro.processos sp inner join sinistro.laudos_preliminares lp on lp.id_processo = sp.id inner join sistema.status s on s.id = lp.id_status inner join sinistro.laudos_preliminares_itens_segurados lfis on lfis.id_laudo_preliminar = lp.id inner join seguro.itens_segurados its on its.id = lfis.id_item_segurado, sinistro.avisos av, seguro.propostas pr inner join seguro.propostas_endosso pre on pre.id_proposta = pr.id and pre.fl_proposta_vigente = true, seguro.propostas_propriedades pp, sistema.municipios m, sistema.estados es, produto.produtos pd, sinistro.processos_empresas pe, sistema.empresas e, sinistro.vistorias_avisos va, sistema.vistorias v, sistema.tipos_vistoria tv, sistema.vistorias_agendamentos vag, sistema.agendamentos ag where ".$filtro." tv.ds_tipo = 'Preliminar' and av.id_proposta = sp.id_proposta and pr.id = sp.id_proposta and pp.id_proposta = pr.id and m.id = pp.id_municipio and es.id = m.id_estado and pd.id = pr.id_produto and pe.id_processo = sp.id and e.id = pe.id_empresa and va.id_aviso = av.id and v.id = va.id_vistoria and tv.id = v.id_tipo and vag.id_vistoria = v.id and ag.id = vag.id_agendamento order by sp.id,av.id,its.nr_item_segurado ) "; return $this->fetchAll($sql); } public function relatorioLaudosFinais($id_safra="",$arProduto="") { $this->_db->query("CREATE temp TABLE vistorias_temp AS (SELECT id, id_tipo FROM sistema.vistorias where id_tipo=3)"); $this->_db->query("CREATE temp table vistorias_avisos_temp as (select id_aviso,id_vistoria from sinistro.vistorias_avisos va inner join vistorias_temp vt on vt.id = va.id_vistoria)"); $filtro = ($id_safra) ? "pd.id_safra=".$id_safra." and " : ''; if(is_array($arProduto)) { $filtro .= "pd.id in (".implode("," , $arProduto).") and "; } $sql = " select pr.id_proposta_mae as id_proposta ,sp.id as id_processo ,av.id as id_aviso ,to_char(av.dt_sinistro,'DD/MM/YYYY') as dt_sinistro ,to_char(av.dt_aceitacao,'DD/MM/YYYY') as dt_aceitacao ,sprop.id as id_proponente ,m.ds_nome_municipio ,es.ds_sigla ,e.ds_nome_fantasia ,sf.ds_nome_safra ,pd.ds_nome_produto ,co.ds_nome_cobertura ,lf.id as id_laudo_final ,st.ds_chave ,case when (select count(*) from seguro.propostas_observacoes where fl_ocultar_itens_segurados=true and fl_observacao_endosso=true and id_proposta=pr.id) > 0 then 'Sim' else 'Não' end as fl_endosso ,sinistro.fn_get_eventos_aviso_sinistro(av.id) as ds_eventos ,tv.ds_tipo ,ag.dt_agendamento ,CASE WHEN (lf.fl_transmitido_tablet IS TRUE) THEN 'Digital' WHEN (lf.fl_transmitido_tablet IS NULL) THEN '-' ELSE 'Manual' END AS fl_transmitido_tablet ,agronomo_responsavel.agronomo_responsavel ,corretores.ds_nome_fantasia as corretor ,lf_funcionario.perito ,TO_CHAR(lf.dt_vistoria, 'DD/MM/YYYY') as dt_vistoria from seguro.propostas pr inner join seguro.propostas_endosso pre on pre.id_proposta = pr.id and pre.fl_proposta_vigente = true inner join seguro.propostas_proponentes pp on pp.id_proposta = pr.id inner join seguro.proponentes sprop on sprop.cpf_cnpj = pp.cpf_cnpj inner join seguro.propostas_propriedades po on po.id_proposta = pr.id inner join sistema.municipios m on m.id = po.id_municipio inner join sistema.estados es on es.id = m.id_estado inner join produto.produtos pd on pd.id = pr.id_produto inner join produto.produtos_geral ppgeral on ppgeral.id = pd.id_produto_geral inner join produto.safras sf on sf.id = pd.id_safra inner join sinistro.processos sp on sp.id_proposta = pr.id inner join sinistro.processos_empresas pe on pe.id_processo = sp.id and pe.fl_ativo = true inner join sistema.empresas e on e.id = pe.id_empresa inner join sinistro.avisos av on av.id_proposta = pr.id left join vistorias_avisos_temp va on va.id_aviso = av.id left join vistorias_temp v on v.id = va.id_vistoria left join sistema.tipos_vistoria tv on v.id_tipo = tv.id left join sistema.vistorias_laudos_finais vlf on vlf.id_vistoria = v.id left join sinistro.laudos_finais lf on lf.id = vlf.id_laudo_final left join sistema.status st on st.id = lf.id_status left join produto.coberturas co on co.id = lf.id_cobertura left join sistema.vistorias_agendamentos vag on vag.id_vistoria = v.id left join sistema.agendamentos ag on ag.id = vag.id_agendamento left join ( select par.id_estado, par_cultura.id_cultura, string_agg(ds_nome_funcionario::text, ', ') AS agronomo_responsavel from sinistro.parametros_agronomos_responsaveis par inner join sinistro.parametros_agronomos_responsaveis_culturas par_cultura on par_cultura.id_parametro_agronomo_responsavel = par.id inner join sistema.funcionarios on funcionarios.id = par.id_funcionario group by par.id_estado, par_cultura.id_cultura ) agronomo_responsavel ON agronomo_responsavel.id_estado = es.id AND agronomo_responsavel.id_cultura = ppgeral.id_cultura left join seguro.propostas_corretores on propostas_corretores.id_proposta = pr.id left join sistema.usuarios on usuarios.id = propostas_corretores.id_usuario left join sistema.corretores on corretores.id_usuario = usuarios.id left join ( select string_agg(ds_nome_funcionario::text, ', ') AS perito, id_laudo_final from sinistro.laudos_finais_funcionarios inner join sistema.funcionarios on funcionarios.id = laudos_finais_funcionarios.id_funcionario group by id_laudo_final ) as lf_funcionario on lf_funcionario.id_laudo_final = lf.id where ".$filtro." av.id_status in (30,28) order by pr.id, av.id "; return $this->fetchAll($sql); } public function relatorioLiquidacoesNobre($id_safra="",$arProduto="") { $filtro = ($id_safra) ? "pd.id_safra=".$id_safra." and " : ''; if(is_array($arProduto)) { $filtro .= "pd.id in (".implode("," , $arProduto).") and "; } $sql = " select sp.id as id_processo, sp.id_processo_seguradora, sp.id_proposta, pr.nr_apolice, sl.id as id_liquidacao, pp.ds_nome_proponente, (select sum(vl_indenizacao) from sinistro.liquidacoes_itens_segurados_coberturas where id_liquidacao=sl.id) as vl_indenizacao, st.ds_status, case when st.ds_chave='ENVIADA_SEGURADORA' then (select max(dt_criacao) from sinistro.liquidacoes_status ls where id_liquidacao=sl.id and id_status=112) end as dt_envio_seguradora, ( select distinct(e.ds_nome_fantasia) from seguro.propostas_vistorias_previas pvp left join sistema.vistorias_empresas ve ON ve.id_vistoria = pvp.id_vistoria and ve.fl_ativo=true left join sistema.empresas e ON e.id = ve.id_empresa where pvp.id_proposta = pr.id and pvp.fl_ativo = true and pvp.id_proposta = pr.id limit 1 ) as ds_nome_fantasia from sinistro.processos sp, seguro.propostas pr, produto.produtos pd, sinistro.liquidacoes sl, sinistro.liquidacoes_status ls, sistema.status st, seguro.propostas_proponentes pp where ".$filtro." ls.fl_ativo = true and pr.id = sp.id_proposta and pd.id = pr.id_produto and sl.id_processo = sp.id and ls.id_liquidacao = sl.id and st.id = ls.id_status and pp.id_proposta = pr.id and st.ds_chave in ('ENVIADA_SEGURADORA','INDENIZACAO_PAGA') and pd.id_safra < 16 order by pr.id "; return $this->fetchAll($sql); } public function relatorioLiquidacoesEssor($id_safra="",$arProduto="") { $filtro = ($id_safra) ? "pd.id_safra=".$id_safra." and " : ''; if(is_array($arProduto)) { $filtro .= "pd.id in (".implode("," , $arProduto).") and "; } $sql = " select sp.id as id_processo, sp.id_processo_seguradora, sp.id_proposta, pr.nr_apolice, sl.id as id_liquidacao, pp.ds_nome_proponente, (select sum(vl_indenizacao) from sinistro.liquidacoes_itens_segurados_coberturas where id_liquidacao=sl.id) as vl_indenizacao, st.ds_status, case when (st.ds_chave='ENVIADA_SEGURADORA' OR st.ds_chave='ENVIADA_SEGURADORA_EXCEL') then (select max(dt_criacao) from sinistro.liquidacoes_status ls where id_liquidacao=sl.id and id_status in (112,187)) end as dt_envio_seguradora, ( select distinct(e.ds_nome_fantasia) from seguro.propostas_vistorias_previas pvp left join sistema.vistorias_empresas ve ON ve.id_vistoria = pvp.id_vistoria and ve.fl_ativo=true left join sistema.empresas e ON e.id = ve.id_empresa where pvp.id_proposta = pr.id and pvp.fl_ativo = true and pvp.id_proposta = pr.id limit 1 ) as ds_nome_fantasia from sinistro.processos sp, seguro.propostas pr, produto.produtos pd, sinistro.liquidacoes sl, sinistro.liquidacoes_status ls, sistema.status st, seguro.propostas_proponentes pp where ".$filtro." ls.fl_ativo = true and pr.id = sp.id_proposta and pd.id = pr.id_produto and sl.id_processo = sp.id and ls.id_liquidacao = sl.id and st.id = ls.id_status and pp.id_proposta = pr.id and st.ds_chave in ('ENVIADA_SEGURADORA','INDENIZACAO_PAGA','ENVIADA_SEGURADORA_EXCEL') and pd.id_safra >= 16 order by pr.id "; return $this->fetchAll($sql); } public function relatorioLiquidacoesItem($id_safra="",$arProduto="") { $filtro = ($id_safra) ? "pd.id_safra=".$id_safra." and " : ''; if(is_array($arProduto)) { $filtro .= "pd.id in (".implode("," , $arProduto).") and "; } $sql = " select pd.id, pd.ds_nome_produto, sp.id as id_processo, sp.id_processo_seguradora, pr.id_proposta_mae as id_proposta, pr.nr_apolice, sl.id as id_liquidacao, sprop.id as id_proponente, iseg.nr_item_segurado, iseg.vl_lmga, iseg.vl_area, sl.id as id_liquidacao, lisc.vl_prejuizo_total as vl_prejuizo, case when lisc.vl_indenizacao >= 0 then lisc.vl_indenizacao else 0 end as vl_indenizacao, st.ds_status, case when st.ds_chave='ENVIADA_SEGURADORA' then ( select max(dt_criacao) from sinistro.liquidacoes_status ls where id_liquidacao=sl.id and id_status=112 ) end as dt_envio_seguradora, case when (select pc.fl_principal from seguro.propostas_coberturas pc where pc.id_cobertura = lisc.id_cobertura and pc.id_proposta = pr.id ) is false then 'Não' else 'Sim' end as principal, (select ds_nome_cobertura from produto.coberturas where id = lisc.id_cobertura) as ds_nome_cobertura, (select id_cobertura from produto.coberturas where id = lisc.id_cobertura) as id_cobertura, (select vl_franquia from seguro.propostas_coberturas where id_proposta = pr.id and id_cobertura = lisc.id_cobertura) as vl_franquia, case when tl.fl_liquidacao_manual = true then 'Sim' else 'Não' end as liquidacao_manual, u.ds_nome_usuario as ds_nome_usuario_responsavel_liquidacao, to_char(sl.dt_criacao, 'DD/MM/YYYY HH24:MI:ss') as dt_liquidacao_hora from sinistro.processos sp left join sistema.usuarios u on u.id = sp.id_usuario_responsavel_liquidacao, seguro.propostas pr inner join seguro.propostas_endosso pre on pre.id_proposta = pr.id and pre.fl_proposta_vigente = true, produto.produtos pd, seguro.itens_segurados iseg, sinistro.liquidacoes sl, sinistro.tipo_liquidacao tl, sinistro.liquidacoes_status ls, sistema.status st, seguro.propostas_proponentes pp, seguro.proponentes sprop, sinistro.liquidacoes_itens_segurados_coberturas lisc where ".$filtro." ls.fl_ativo = true and pr.id = sp.id_proposta and pd.id = pr.id_produto and iseg.id_proposta = pr.id and sl.id_processo = sp.id and tl.id = sl.id_tipo_liquidacao and ls.id_liquidacao = sl.id and st.id = ls.id_status and pp.id_proposta = pr.id and sprop.cpf_cnpj = pp.cpf_cnpj and (select nr_item_segurado from seguro.itens_segurados where id=lisc.id_item_segurado) = iseg.nr_item_segurado and lisc.id_liquidacao = sl.id order by pr.id, iseg.nr_item_segurado "; return $this->fetchAll($sql); } public function relatorioLiquidacoes($id_safra="",$arProduto="", $id_tipo_liquidacao=false, $fl_seguradora=false) { $filtro = array(); if($fl_seguradora){ $filtro[] = "pd.id_safra >= 16"; } elseif ($id_safra) { $filtro[] = "pd.id_safra=$id_safra"; } if(is_array($arProduto)) { $filtro[] = "pd.id in (".implode("," , $arProduto).")"; } if ($id_tipo_liquidacao) { $filtro[] = "sl.id_tipo_liquidacao = $id_tipo_liquidacao"; } $sql = " SELECT distinct sl.ID AS id_liquidacao, sl.tp_liquidacao, sp.ID AS id_processo, pr.id_proposta_mae as id_proposta, sp.id_processo_seguradora, pr.nr_apolice, sl. ID AS id_liquidacao, pp.ds_nome_proponente, pp.cpf_cnpj, ( SELECT array_to_string( array( SELECT case when (b.ds_nome_banco is not null) then b.ds_nome_banco || ' ' || pb.ds_agencia ||' ('||pb.nr_cpf_cnpj||')' else pb.ds_nome_beneficiarios ||' ('||pb.nr_cpf_cnpj||')' end FROM seguro.propostas_beneficiarios pb LEFT JOIN sistema.bancos b ON b.id = pb.id_banco WHERE pb.id_proposta = pr.id ), ' / ') ) as ds_nome_beneficiario, CASE WHEN( SELECT SUM(vl_indenizacao) FROM sinistro.liquidacoes_itens_segurados_coberturas WHERE id_liquidacao = sl. ID and vl_indenizacao >= 0 )< 0 THEN 0 ELSE ( SELECT SUM(vl_indenizacao) FROM sinistro.liquidacoes_itens_segurados_coberturas WHERE id_liquidacao = sl. ID and vl_indenizacao >= 0 ) END AS vl_indenizacao, st.ds_status, st2.ds_status as ds_status_arquivos, pd.ds_nome_produto, m.ds_nome_municipio, e.ds_sigla, to_char(sl.dt_criacao, 'DD/MM/YYYY') as dt_liquidacao, case when tl.fl_liquidacao_avulsa is true then 'Não' else 'Sim' end as principal, c.ds_nome_abreviado, ( select vl_pagamento from sinistro.liquidacoes_previsao_pagamento where id_liquidacao=sl.id limit 1 ) as vl_pagamento, ( select to_char(dt_confirmacao_pagamento, 'dd/MM/yyyy') from sinistro.liquidacoes_previsao_pagamento where id_liquidacao=sl.id limit 1 ) as dt_pagamento, ( select to_char(dt_pagamento, 'dd/MM/yyyy') from sinistro.liquidacoes_previsao_pagamento where id_liquidacao=sl.id limit 1 ) as dt_estimada_pagamento, case when us.tp_usuario = 'P' then us.ds_nome_usuario else '' end as ds_nome_preposto, u.ds_nome_usuario as ds_nome_usuario_responsavel_liquidacao ,to_char(( SELECT liquidacoes_status.dt_criacao FROM sinistro.liquidacoes_status WHERE liquidacoes_status.id_liquidacao=sl.id and liquidacoes_status.id_status = 184 order by dt_criacao desc limit 1),'DD/MM/YYYY HH24:MI') as dt_responsavel FROM sinistro.processos sp left join sistema.usuarios u on u.id = sp.id_usuario_responsavel_liquidacao left join sistema.status st2 on st2.id = sp.id_status_arquivos inner join seguro.propostas pr on pr.id = sp.id_proposta inner join seguro.propostas_endosso pre on pre.id_proposta = pr.id and pre.fl_proposta_vigente = true inner join seguro.propostas_propriedades po on po.id_proposta = pr.id inner join seguro.propostas_proponentes pp on pp.id_proposta = pr.id inner join seguro.propostas_corretores co on co.id_proposta = pr.id inner join sistema.corretores c on c.id_usuario = co.id_usuario inner join sistema.municipios m on m.id = po.id_municipio inner join sistema.estados e on e.id = m.id_estado inner join produto.produtos pd on pd.id = pr.id_produto inner join sinistro.liquidacoes sl on sl.id_processo = sp.id INNER JOIN sinistro.tipo_liquidacao tl on tl.id = sl.id_tipo_liquidacao inner join sinistro.liquidacoes_status ls on ls.id_liquidacao = sl.id and ls.fl_ativo = true inner join sistema.status st on st.id = ls.id_status inner join sistema.usuarios us on us.id = pr.id_usuario_criacao left join (select max(sls.id), sls.dt_criacao, sls.id_liquidacao from sinistro.liquidacoes_status sls inner join sistema.status sst on sst.id=sls.id_status and (sst.ds_chave = 'ENVIADA_SEGURADORA' or sst.ds_chave = 'ENVIADA_SEGURADORA_EXCEL') group by sls.dt_criacao, sls.id_liquidacao limit 1) as env_seguradora on env_seguradora.id_liquidacao = sl.id where sl.id in (select id_liquidacao from sinistro.liquidacoes_itens_segurados_coberturas) and ".implode(" and ", $filtro)." order by id_proposta, sl.ID desc "; return $this->fetchAll($sql); } public function beneficiariosRelatorioLiquidacoesSeguradora($idSafra, $flSeguradora = false){ $subSelect = $this->select()->setIntegrityCheck(false); $subSelect->from(array('sba' => 'seguro.beneficiarios_arquivos'), array('id_beneficiario', 'max(dt_criacao) as dt_criacao')); $subSelect->where('sba.fl_ativo = true'); $subSelect->group('id_beneficiario'); $subSelect->order('dt_criacao DESC'); $Select = $this->select()->setIntegrityCheck(false); $Select->from(array('p' => 'seguro.propostas_beneficiarios')); $Select->join(array('pr' => 'seguro.propostas'), 'p.id_proposta = pr.id', null); $Select->join(array('prod' => 'produto.produtos'), 'pr.id_produto = prod.id', null); $Select->join(array('bf' => 'seguro.beneficiarios'), 'bf.cpf_cnpj = p.nr_cpf_cnpj', null); $Select->joinLeft(array('st' => 'sistema.status'), 'st.id = bf.id_status_arquivos', 'ds_status'); $Select->joinLeft(array('b' => 'sistema.bancos'), 'p.id_banco = b.id', 'ds_nome_banco'); //$Select->joinLeft(array('ib' => 'seguro.informacoes_bancarias'), 'ib.id_proposta = '.$this->id.' and ib.cpf_cnpj = p.nr_cpf_cnpj', array('nu_conta_corrente','nu_digito_conta_corrente','nu_agencia','nu_digito_agencia','id_forma_pagamento')); $Select->joinLeft(array('ib' => 'seguro.informacoes_bancarias'), 'ib.cpf_cnpj = p.nr_cpf_cnpj and ib.id_proposta = p.id_proposta', array('nu_conta_corrente','nu_digito_conta_corrente','nu_agencia','nu_digito_agencia','id_forma_pagamento')); $Select->joinLeft(array('bc' => 'sistema.bancos'), 'bc.id = ib.id_banco', 'nr_banco'); $Select->joinLeft(array('ba' => $subSelect), 'ba.id_beneficiario = bf.id', "to_char(ba.dt_criacao, 'DD/MM/YYYY') as dt_ultimo_arquivo_beneficiario"); if ($flSeguradora) { $Select->where("prod.id_safra >= 16"); } elseif ($idSafra) { $Select->where("prod.id_safra = ?", $idSafra); } $Select->where("p.fl_del = ?", "false"); $Select->order('id asc'); $Beneficiarios = $this->fetchAll($Select); if (!is_array($Beneficiarios)) { $Beneficiarios = $Beneficiarios->toArray(); } $result = array(); if ($Beneficiarios) { foreach ($Beneficiarios as $beneficiario) { if (strlen($beneficiario['nr_cpf_cnpj']) > 14){ $beneficiario['tp_pessoa'] = 'J'; } else { $beneficiario['tp_pessoa'] = 'F'; } if (!$result[$beneficiario['id_proposta']]) { $result[$beneficiario['id_proposta']] = array(); } $result[$beneficiario['id_proposta']][] = $beneficiario; } } return $result; } public function importacaoIndenizacaoAgro( array $idsLiquidacoes, string $dataBaseInicio, string $dataBaseFim, bool $isRegerarRelatorioIndenizacaoAgro = false ) { $filtro = []; $idStatusLiquidacaoEnviadaSeguradora = (new Sistema_Model_Status())->getIdStatus('ENVIADA_SEGURADORA', 'LIQUIDACOES'); $idStatusLiquidacaoAguardandoPagamento = ''; if ($isRegerarRelatorioIndenizacaoAgro) { $idStatusLiquidacaoAguardandoPagamento = ', ' . (new Sistema_Model_Status())->getIdStatus('AGUARDANDO_PAGAMENTO', 'LIQUIDACOES'); } /** * Traz liquidações com status indenização paga somente quando estas tiverem pagamento adicional * no status PENDENTE PAGAMENTO ou AGUARDANDO PAGAMENTO (quando regera relatório) */ $filtro[] = 'sl.id IN ('. implode(',', $idsLiquidacoes) .')'; $filtro[] = "ls.id_status IN ({$idStatusLiquidacaoEnviadaSeguradora} {$idStatusLiquidacaoAguardandoPagamento})"; $filtro[] = "pd.id_safra >= 16"; return $this->fetchAll( $this->getConsultaLiquidacoesSeguradora( $filtro, $dataBaseInicio, $dataBaseFim, $isRegerarRelatorioIndenizacaoAgro ) ); } private function getConsultaLiquidacoesSeguradora( array $filtro, string $dataBaseInicioPagamentoAdicional = '', string $dataBaseFimPagamentoAdicional = '', $isRegerarRelatorioIndenizacaoAgro = false ): string { $sqlAvisosEncerramentoTemp = " CREATE temp TABLE avisos_encerramento_temp AS ( select l.id as id_liquidacao, tmp_encerramento.dt_criacao as dt_encerramento_colheita from sinistro.processos sp, sinistro.liquidacoes l, sinistro.liquidacoes_itens_segurados_coberturas lisc inner join seguro.itens_segurados isg1 on isg1.id = lisc.id_item_segurado, ( select ae.id_proposta, ae.id, ae.dt_encerramento_colheita, ae.dt_criacao, isg2.nr_item_segurado from seguro.propostas pr inner join seguro.propostas_endosso pre on pre.id_proposta = pr.id and pre.fl_proposta_vigente = true inner join sinistro.avisos_encerramento ae on ae.id_proposta = pr.id inner join sinistro.avisos_encerramento_itens_segurados aeis on aeis.id_aviso_encerramento = ae.id inner join seguro.itens_segurados isg2 on isg2.id = aeis.id_item_segurado ) as tmp_encerramento where sp.id = l.id_processo and lisc.id_liquidacao = l.id and l.dt_criacao >= tmp_encerramento.dt_criacao and tmp_encerramento.id_proposta = sp.id_proposta and tmp_encerramento.nr_item_segurado = isg1.nr_item_segurado group by l.id,tmp_encerramento.dt_criacao ); "; $this->_db->query($sqlAvisosEncerramentoTemp); $sqlAvisosFinalReplantioTemp = " CREATE temp TABLE avisos_final_replantio_temp AS ( select l.id as id_liquidacao, tmp_replantio.dt_criacao as dt_aviso_final_replantio from sinistro.processos sp INNER JOIN sinistro.liquidacoes l ON l.id_processo = sp.id INNER JOIN seguro.propostas_endosso pre ON pre.id_proposta = sp.id_proposta INNER JOIN seguro.propostas_endosso pre2 ON pre2.id_endosso = pre.id_endosso INNER JOIN seguro.propostas pr2 ON pr2.id = pre2.id_proposta INNER JOIN sinistro.liquidacoes_itens_segurados_coberturas lisc ON lisc.id_liquidacao = l.id INNER JOIN seguro.itens_segurados isg1 ON isg1.id = lisc.id_item_segurado, ( select ae.id_proposta, ae.id, ae.dt_final_replantio, ae.dt_criacao, isg2.nr_item_segurado from seguro.propostas pr INNER JOIN seguro.propostas_endosso pre on pre.id_proposta = pr.id INNER JOIN seguro.propostas_endosso pre2 on pre2.id_endosso = pre.id_endosso INNER JOIN seguro.propostas pr2 on pr2.id = pre2.id_proposta INNER JOIN sinistro.avisos_final_replantio ae on ae.id_proposta = pr.id INNER JOIN sinistro.avisos_final_replantio_itens_segurados aeis on aeis.id_aviso_final_replantio = ae.id INNER JOIN seguro.itens_segurados isg2 on isg2.id = aeis.id_item_segurado ) as tmp_replantio where l.dt_criacao >= tmp_replantio.dt_criacao and tmp_replantio.id_proposta = pre2.id_proposta and tmp_replantio.nr_item_segurado = isg1.nr_item_segurado group by l.id,tmp_replantio.dt_criacao ); "; $this->_db->query($sqlAvisosFinalReplantioTemp); $sqlDataArquivosTemp = " CREATE temp TABLE data_arquivos_temp AS ( SELECT max(pa.dt_criacao) dt_arquivo, p.id_proposta FROM sinistro.processos_arquivos AS pa INNER JOIN sinistro.processos AS p ON p.id = pa.id_processo INNER JOIN seguro.propostas AS pr ON pr.id = p.id_proposta INNER JOIN seguro.propostas_endosso AS pe ON pe.id_proposta = pr.id WHERE (pa.fl_del = false) AND (pa.fl_ativo = true) AND (pe.fl_proposta_vigente = true) AND pa.id_tipo_arquivo not in (18,20) group by p.id_proposta ); "; $this->_db->query($sqlDataArquivosTemp); $sqlSomaLiquidacoesTemp = " CREATE temp TABLE soma_liquidacoes_temp AS ( SELECT string_agg(DISTINCT ds_nome_cobertura, ',') as ds_nome_cobertura, case when SUM(vl_indenizacao) > 0 then SUM(vl_indenizacao) else 0.0 end as vl_indenizacao,id_liquidacao FROM sinistro.liquidacoes_itens_segurados_coberturas lisc JOIN produto.coberturas pc ON lisc.id_cobertura = pc.id GROUP BY id_liquidacao ); "; $this->_db->query($sqlSomaLiquidacoesTemp); $sqlAvisosTemp = " CREATE temp TABLE avisos_temp AS ( SELECT min(cd_identificador_seguradora) as cd_identificador_seguradora, av.id_proposta FROM sinistro.avisos av inner join sinistro.avisos_transmissao_seguradora ats on ats.id_aviso = av.id WHERE av.id_status=28 GROUP BY av.id_proposta ); "; $this->_db->query($sqlAvisosTemp); $sqlLiquidacoesStatusTemp = " CREATE temp TABLE liquidacoes_status_temp AS ( SELECT lss.id, lss.id_liquidacao, u.ds_nome_usuario, to_char(lss.dt_criacao, 'DD/MM/YYYY') as dt_envio_seguradora FROM sinistro.liquidacoes_status lss, sistema.status s, sistema.usuarios u WHERE s.id = lss.id_status and lss.id_usuario_criacao = u.id and s.ds_chave in ('ENVIADA_SEGURADORA','ENVIADA_SEGURADORA_EXCEL') ); "; $this->_db->query($sqlLiquidacoesStatusTemp); $sqlLiquidacoesCoberturasTemp = " CREATE TEMP TABLE liquidacoes_coberturas_temp AS ( SELECT min(id_cobertura) AS id_cobertura, id_liquidacao FROM sinistro.liquidacoes_itens_segurados_coberturas GROUP BY id_liquidacao ); "; $this->_db->query($sqlLiquidacoesCoberturasTemp); $sqlProdutoEventosTemp = " CREATE TEMP TABLE produto_eventos_temp AS ( SELECT string_agg(ds_nome_evento::text, ', ') AS ds_nome_evento, id_proposta FROM produto.eventos INNER JOIN sinistro.avisos_eventos ON avisos_eventos.id_evento = eventos.id INNER JOIN sinistro.avisos ON avisos.id = avisos_eventos.id_aviso GROUP BY id_proposta ); "; $this->_db->query($sqlProdutoEventosTemp); $sqlAvisosIdTemp = " CREATE TEMP TABLE avisos_id_temp AS ( SELECT string_agg(id::text, ', ') AS id_aviso, id_proposta FROM sinistro.avisos GROUP BY id_proposta ); "; $this->_db->query($sqlAvisosIdTemp); $sqlLiquidacoesMotivosTemp = " CREATE TEMP TABLE liquidacoes_motivos_temp AS ( SELECT string_agg(ds_motivo::TEXT, ', ') AS motivo, liq_mot.id_liquidacao FROM sinistro.liquidacoes_motivos_liquidacao_sem_indenizacao liq_mot JOIN sinistro.motivos_liquidacao_sem_indenizacao mot ON liq_mot.id_motivos_liquidacao_sem_indenizacao = mot.id GROUP BY liq_mot.id_liquidacao ); "; $this->_db->query($sqlLiquidacoesMotivosTemp); $sqlLiquidacoesDadosEnvioTemp = " CREATE TEMP TABLE liquidacoes_dados_envio_temp AS ( SELECT dt_envio_seguradora, ds_nome_usuario, lst.id_liquidacao FROM liquidacoes_status_temp lst JOIN (SELECT max(id) AS max_id, id_liquidacao FROM liquidacoes_status_temp GROUP BY id_liquidacao) lst2 ON lst2.id_liquidacao = lst.id_liquidacao AND lst2.max_id = lst.id ); "; $this->_db->query($sqlLiquidacoesDadosEnvioTemp); $sqlLiquidacoesPrevisaoPagamentoTemp = " CREATE TEMP TABLE liquidacoes_previsao_pagamento_temp AS ( SELECT id_liquidacao, vl_pagamento, dt_pagamento, dt_confirmacao_pagamento FROM sinistro.liquidacoes_previsao_pagamento ); "; $this->_db->query($sqlLiquidacoesPrevisaoPagamentoTemp); $sqlEncerramentoColheitaTemp = " CREATE TEMP TABLE encerramento_colheita_temp AS ( SELECT max(dt_encerramento_colheita) dt_encerramento_colheita, id_liquidacao FROM avisos_encerramento_temp GROUP BY id_liquidacao ); "; $this->_db->query($sqlEncerramentoColheitaTemp); $sqlFinalReplantioTemp = " CREATE TEMP TABLE final_replantio_temp AS ( SELECT max(dt_aviso_final_replantio) dt_aviso_final_replantio, id_liquidacao FROM avisos_final_replantio_temp GROUP BY id_liquidacao ); "; $this->_db->query($sqlFinalReplantioTemp); $sqlInformacoesBancariasTemp = " CREATE TEMP TABLE informacoes_bancarias_temp AS ( SELECT MAX(id) as id, cpf_cnpj FROM seguro.informacoes_bancarias GROUP BY cpf_cnpj ); "; $this->_db->query($sqlInformacoesBancariasTemp); $condicoes = ''; if (!empty($filtro)) { $condicoes = ' and ' . implode(" and ", $filtro); } $sql = " SELECT distinct sl.id AS id_liquidacao, sl.tp_liquidacao, to_char(sl.dt_base_pagamento, 'DD/MM/YYYY') as dt_base_pagamento, case when fl_relatorio_importacao_seguradora = true then 'S' else 'N' end as fl_relatorio_importacao_seguradora, sp.id AS id_processo, pr.id as id_proposta_original, pr.id_proposta_mae as id_proposta, sp.id_processo_seguradora, pr.nr_apolice, pp.ds_nome_proponente, pp.cpf_cnpj, slt.vl_indenizacao, slt.ds_nome_cobertura, st.ds_status as ds_status, st.ds_chave, st2.ds_status as ds_status_arquivos, lst.dt_envio_seguradora, lst.ds_nome_usuario, pd.ds_nome_produto, sf.ds_nome_safra, m.ds_nome_municipio, e.ds_sigla, to_char(sl.dt_criacao, 'DD/MM/YYYY') as dt_liquidacao, case when tl.fl_liquidacao_avulsa is true then 'Não' else 'Sim' end as principal, c.ds_nome_abreviado, slpagamento.vl_pagamento, slpagamento.dt_pagamento, slpagamento.dt_confirmacao_pagamento, case when us.tp_usuario = 'P' then us.ds_nome_usuario else ''end as ds_nome_preposto, u.ds_nome_usuario as ds_nome_usuario_responsavel_liquidacao, to_char(aet.dt_encerramento_colheita, 'DD/MM/YYYY') as dt_encerramento_colheita, to_char(afr.dt_aviso_final_replantio, 'DD/MM/YYYY') as dt_aviso_final_replantio, to_char(dat.dt_arquivo, 'DD/MM/YYYY') as dt_arquivo, cob.id_cobertura, CASE WHEN ib_proposta.id is not null THEN ib_proposta.nu_conta_corrente ELSE ib.nu_conta_corrente END AS nu_conta_corrente, CASE WHEN ib_proposta.id is not null THEN ib_proposta.nu_digito_conta_corrente ELSE ib.nu_digito_conta_corrente END AS nu_digito_conta_corrente, CASE WHEN ib_proposta.id is not null THEN ib_proposta.nu_agencia ELSE ib.nu_agencia END AS nu_agencia, CASE WHEN ib_proposta.id is not null THEN ib_proposta.nu_digito_agencia ELSE ib.nu_digito_agencia END AS nu_digito_agencia, CASE WHEN ib_proposta.id is not null THEN ib_proposta.id_forma_pagamento ELSE ib.id_forma_pagamento END AS id_forma_pagamento, (CASE WHEN ib_proposta.ds_tipo_conta = 'C' THEN 'Conta Corrente' WHEN ib_proposta.ds_tipo_conta = 'P' THEN 'Conta Poupança' WHEN ib_proposta.ds_tipo_conta = 'O' THEN 'Ordem de Pagamento' ELSE '' END) AS ds_tipo_conta, (CASE WHEN ib_proposta.id IS NULL THEN '' WHEN ib_proposta.fl_conta_conjunta IS TRUE THEN 'Sim' ELSE 'Não' END) AS fl_conta_conjunta, CASE WHEN bc_proposta.id is not null THEN bc_proposta.nr_banco ELSE bc.nr_banco END AS nr_banco, av.cd_identificador_seguradora, motivo_indenizacao.motivo AS motivos_sem_indenizacao, sav_agg.id_aviso, pe.ds_nome_evento, tl.ds_chave as tipo_liquidacao_chave, sce.ds_endereco_rua, sce.ds_endereco_nro, sce.ds_endereco_bairro, sce.ds_endereco_cep, sce.ds_endereco_complemento, sm_corretor.ds_nome_municipio as municipio_endereco_corretor, se_corretor.ds_sigla as sigla_endereco_corretor, ls.id_status as id_status_liquidacao, ls.dt_criacao as data_criacao_liquidacao_status %CAMPOS_PAGAMENTO_ADICIONAL% FROM sinistro.processos sp INNER JOIN sistema.status st2 ON st2.id = sp.id_status_arquivos INNER JOIN seguro.propostas pr ON pr.id = sp.id_proposta INNER JOIN seguro.propostas_endosso pre ON pre.id_proposta = pr.id and pre.fl_proposta_vigente = true INNER JOIN seguro.propostas_propriedades po ON po.id_proposta = pr.id INNER JOIN seguro.propostas_proponentes pp ON pp.id_proposta = pr.id INNER JOIN sistema.usuarios us ON us.id = pr.id_usuario_criacao INNER JOIN seguro.propostas_corretores co ON co.id_proposta = pr.id INNER JOIN sistema.corretores c ON c.id_usuario = co.id_usuario INNER JOIN sistema.municipios m ON m.id = po.id_municipio INNER JOIN sistema.estados e ON e.id = m.id_estado INNER JOIN produto.produtos pd ON pd.id = pr.id_produto INNER JOIN produto.safras sf ON sf.id = pd.id_safra INNER JOIN sinistro.liquidacoes sl ON sl.id_processo = sp.id INNER JOIN sinistro.tipo_liquidacao tl ON tl.id = sl.id_tipo_liquidacao INNER JOIN soma_liquidacoes_temp slt ON slt.id_liquidacao = sl.id INNER JOIN sinistro.liquidacoes_status ls ON ls.id_liquidacao = sl.id and ls.fl_ativo = true INNER JOIN avisos_id_temp sav_agg ON sav_agg.id_proposta = sp.id_proposta INNER JOIN produto_eventos_temp pe ON pe.id_proposta = sp.id_proposta %JOIN_PAGAMENTO_ADICIONAL% LEFT JOIN sistema.usuarios u ON u.id = sp.id_usuario_responsavel_liquidacao LEFT JOIN data_arquivos_temp dat ON dat.id_proposta = sp.id_proposta LEFT JOIN liquidacoes_coberturas_temp cob ON cob.id_liquidacao = sl.id LEFT JOIN liquidacoes_previsao_pagamento_temp slpagamento ON slpagamento.id_liquidacao = sl.id LEFT JOIN sistema.status st ON st.id = %JOIN_ON_STATUS% LEFT JOIN encerramento_colheita_temp aet ON aet.id_liquidacao = sl.id LEFT JOIN final_replantio_temp afr ON afr.id_liquidacao = sl.id LEFT JOIN liquidacoes_dados_envio_temp lst ON lst.id_liquidacao = sl.id LEFT JOIN seguro.informacoes_bancarias AS ib_proposta ON ib_proposta.cpf_cnpj = pp.cpf_cnpj AND ib_proposta.id_proposta = pp.id_proposta LEFT JOIN informacoes_bancarias_temp ib_temp ON ib_temp.cpf_cnpj = pp.cpf_cnpj LEFT JOIN seguro.informacoes_bancarias AS ib ON ib.id = ib_temp.id LEFT JOIN sistema.bancos bc_proposta ON bc_proposta.id = ib_proposta.id_banco LEFT JOIN sistema.bancos bc ON bc.id = ib.id_banco LEFT JOIN avisos_temp av ON av.id_proposta = sp.id_proposta LEFT JOIN liquidacoes_motivos_temp motivo_indenizacao ON motivo_indenizacao.id_liquidacao = sl.id LEFT JOIN sistema.corretores_enderecos sce ON sce.id_corretor = c.id LEFT JOIN sistema.municipios sm_corretor ON sm_corretor.id = sce.id_municipio LEFT JOIN sistema.estados se_corretor ON se_corretor.id = sm_corretor.id_estado WHERE sl.id IN (select id_liquidacao from sinistro.liquidacoes_itens_segurados_coberturas) "; if (!empty($dataBaseInicioPagamentoAdicional) || !empty($dataBaseFimPagamentoAdicional)) { $sqlPagamentoAdicional = str_replace( ['%CAMPOS_PAGAMENTO_ADICIONAL%', '%JOIN_PAGAMENTO_ADICIONAL%', '%JOIN_ON_STATUS%'], [ ',liquidacao_pagamento_adicional.dt_criacao AS dt_criacao_pagamento_adicional, liquidacao_pagamento_adicional.vl_total_corrigido, liquidacao_pagamento_adicional.vl_indenizacao_paga', 'left join sinistro.liquidacao_pagamento_adicional ON liquidacao_pagamento_adicional.id_liquidacao = sl.id AND liquidacao_pagamento_adicional.fl_ativo IS TRUE', 'liquidacao_pagamento_adicional.id_status' ], $sql ); $idStatusLiquidacaoIndenizacaoPaga = (new Sistema_Model_Status())->getIdStatus('INDENIZACAO_PAGA', 'LIQUIDACOES'); $idStatusPendentePagamentoPagamentoAdicional = (new Sistema_Model_Status())->getIdStatus('PENDENTE_PAGAMENTO', 'LIQUIDACAO_PAGAMENTO_ADICIONAL'); $idStatusAguardandoPagamentoPagamentoAdicional = ''; if ($isRegerarRelatorioIndenizacaoAgro) { $idStatusAguardandoPagamentoPagamentoAdicional = ',' . (new Sistema_Model_Status())->getIdStatus('AGUARDANDO_PAGAMENTO', 'LIQUIDACAO_PAGAMENTO_ADICIONAL'); } $sqlPagamentoAdicional .= " AND pd.id_safra >= 16 AND ls.id_status IN ({$idStatusLiquidacaoIndenizacaoPaga} ) AND liquidacao_pagamento_adicional.id_status IN ({$idStatusPendentePagamentoPagamentoAdicional} {$idStatusAguardandoPagamentoPagamentoAdicional}) AND TO_CHAR(liquidacao_pagamento_adicional.dt_criacao, 'YYYY-MM-DD') >= '{$dataBaseInicioPagamentoAdicional}' AND TO_CHAR(liquidacao_pagamento_adicional.dt_criacao, 'YYYY-MM-DD') <= '{$dataBaseFimPagamentoAdicional}' "; $sql = str_replace( ['%CAMPOS_PAGAMENTO_ADICIONAL%', '%JOIN_PAGAMENTO_ADICIONAL%', '%JOIN_ON_STATUS%'], [ ",NULL::timestamp with time zone as dt_criacao_pagamento_adicional, 0 as vl_total_corrigido, 0 as vl_indenizacao_paga", '', 'ls.id_status' ], $sql ); $sql .= " {$condicoes}"; $sql = $sql . ' UNION ' . $sqlPagamentoAdicional; } else { $sql = str_replace( ['%CAMPOS_PAGAMENTO_ADICIONAL%', '%JOIN_PAGAMENTO_ADICIONAL%', '%JOIN_ON_STATUS%'], ['', '', 'ls.id_status'], $sql ); $sql .= " {$condicoes}"; } $sql .= ' order by id_proposta, id_liquidacao asc'; return $sql; } public function getLiquidacoesDeAntecipacoes($antecipacoes) { // Extrai os valores de id_liquidacao $ids = array_column($antecipacoes, 'id_liquidacao'); // Monta a cláusula IN $inClause = 'AND sl.id IN (' . implode(',', $ids) . ')'; return $this->fetchAll($this->getConsultaLiquidacoesAntecipacoes($inClause)); } public function getConsultaLiquidacoesAntecipacoes($inClause) { return " SELECT distinct sl.id AS id_liquidacao, sl.tp_liquidacao, to_char(sl.dt_base_pagamento, 'DD/MM/YYYY') as dt_base_pagamento, case when fl_relatorio_importacao_seguradora = true then 'S' else 'N' end as fl_relatorio_importacao_seguradora, sp.id AS id_processo, pr.id as id_proposta_original, pr.id_proposta_mae as id_proposta, sp.id_processo_seguradora, pr.nr_apolice, sl.id AS id_liquidacao, pp.ds_nome_proponente, pp.cpf_cnpj, slt.vl_indenizacao, slt.ds_nome_cobertura, st.ds_status as ds_status, st.ds_chave, st2.ds_status as ds_status_arquivos, lst.dt_envio_seguradora, lst.ds_nome_usuario, pd.ds_nome_produto, sf.ds_nome_safra, m.ds_nome_municipio, e.ds_sigla, to_char(sl.dt_criacao, 'DD/MM/YYYY') as dt_liquidacao, case when tl.fl_liquidacao_avulsa is true then 'Não' else 'Sim' end as principal, c.ds_nome_abreviado, slpagamento.vl_pagamento, slpagamento.dt_pagamento, slpagamento.dt_confirmacao_pagamento, case when us.tp_usuario = 'P' then us.ds_nome_usuario else ''end as ds_nome_preposto, u.ds_nome_usuario as ds_nome_usuario_responsavel_liquidacao, to_char(aet.dt_encerramento_colheita, 'DD/MM/YYYY') as dt_encerramento_colheita, to_char(afr.dt_aviso_final_replantio, 'DD/MM/YYYY') as dt_aviso_final_replantio, to_char(dat.dt_arquivo, 'DD/MM/YYYY') as dt_arquivo, cob.id_cobertura, CASE WHEN ib_proposta.id is not null THEN ib_proposta.nu_conta_corrente ELSE ib.nu_conta_corrente END AS nu_conta_corrente, CASE WHEN ib_proposta.id is not null THEN ib_proposta.nu_digito_conta_corrente ELSE ib.nu_digito_conta_corrente END AS nu_digito_conta_corrente, CASE WHEN ib_proposta.id is not null THEN ib_proposta.nu_agencia ELSE ib.nu_agencia END AS nu_agencia, CASE WHEN ib_proposta.id is not null THEN ib_proposta.nu_digito_agencia ELSE ib.nu_digito_agencia END AS nu_digito_agencia, CASE WHEN ib_proposta.id is not null THEN ib_proposta.id_forma_pagamento ELSE ib.id_forma_pagamento END AS id_forma_pagamento, (CASE WHEN ib_proposta.ds_tipo_conta = 'C' THEN 'Conta Corrente' WHEN ib_proposta.ds_tipo_conta = 'P' THEN 'Conta Poupança' WHEN ib_proposta.ds_tipo_conta = 'O' THEN 'Ordem de Pagamento' ELSE '' END) AS ds_tipo_conta, (CASE WHEN ib_proposta.id IS NULL THEN '' WHEN ib_proposta.fl_conta_conjunta IS TRUE THEN 'Sim' ELSE 'Não' END) AS fl_conta_conjunta, CASE WHEN bc_proposta.id is not null THEN bc_proposta.nr_banco ELSE bc.nr_banco END AS nr_banco, av.cd_identificador_seguradora, motivo_indenizacao.motivo AS motivos_sem_indenizacao, sav_agg.id_aviso, pe.ds_nome_evento, tl.ds_chave as tipo_liquidacao_chave, sce.ds_endereco_rua, sce.ds_endereco_nro, sce.ds_endereco_bairro, sce.ds_endereco_cep, sce.ds_endereco_complemento, sm_corretor.ds_nome_municipio as municipio_endereco_corretor, se_corretor.ds_sigla as sigla_endereco_corretor, ls.id_status as id_status_liquidacao, ls.dt_criacao as data_criacao_liquidacao_status FROM sinistro.processos sp inner join sistema.status st2 on st2.id = sp.id_status_arquivos inner join seguro.propostas pr on pr.id = sp.id_proposta inner join seguro.propostas_endosso pre on pre.id_proposta = pr.id and pre.fl_proposta_vigente = true inner join seguro.propostas_propriedades po on po.id_proposta = pr.id inner join seguro.propostas_proponentes pp on pp.id_proposta = pr.id inner join sistema.usuarios us on us.id = pr.id_usuario_criacao inner join seguro.propostas_corretores co on co.id_proposta = pr.id inner join sistema.corretores c on c.id_usuario = co.id_usuario inner join sistema.municipios m on m.id = po.id_municipio inner join sistema.estados e on e.id = m.id_estado inner join produto.produtos pd on pd.id = pr.id_produto inner join produto.safras sf on sf.id = pd.id_safra inner join sinistro.liquidacoes sl on sl.id_processo = sp.id inner join sinistro.tipo_liquidacao tl on tl.id = sl.id_tipo_liquidacao inner join soma_liquidacoes_temp slt on slt.id_liquidacao = sl.id inner join sinistro.liquidacoes_status ls on ls.id_liquidacao = sl.id and ls.fl_ativo = true inner join avisos_id_temp sav_agg on sav_agg.id_proposta = sp.id_proposta inner join produto_eventos_temp pe on pe.id_proposta = sp.id_proposta left join sistema.usuarios u on u.id = sp.id_usuario_responsavel_liquidacao left join data_arquivos_temp dat on dat.id_proposta = sp.id_proposta left join liquidacoes_coberturas_temp cob on cob.id_liquidacao = sl.id left join liquidacoes_previsao_pagamento_temp slpagamento ON slpagamento.id_liquidacao = sl.id left join sistema.status st on st.id = ls.id_status left join encerramento_colheita_temp aet ON aet.id_liquidacao = sl.id left join final_replantio_temp afr ON afr.id_liquidacao = sl.id left join liquidacoes_dados_envio_temp lst ON lst.id_liquidacao = sl.id LEFT JOIN seguro.informacoes_bancarias AS ib_proposta ON ib_proposta.cpf_cnpj = pp.cpf_cnpj AND ib_proposta.id_proposta = pp.id_proposta left join informacoes_bancarias_temp ib_temp on ib_temp.cpf_cnpj = pp.cpf_cnpj LEFT JOIN seguro.informacoes_bancarias AS ib ON ib.id = ib_temp.id left join sistema.bancos bc_proposta on bc_proposta.id = ib_proposta.id_banco left join sistema.bancos bc on bc.id = ib.id_banco left join avisos_temp av on av.id_proposta = sp.id_proposta left join liquidacoes_motivos_temp motivo_indenizacao ON motivo_indenizacao.id_liquidacao = sl.id left join sistema.corretores_enderecos sce ON sce.id_corretor = c.id left join sistema.municipios sm_corretor ON sm_corretor.id = sce.id_municipio left join sistema.estados se_corretor ON se_corretor.id = sm_corretor.id_estado join sinistro.solicitacao_antecipacao_pagamento sap on sl.id = sap.id_liquidacao where sl.id in (select id_liquidacao from sinistro.liquidacoes_itens_segurados_coberturas) {$inClause} order by id_proposta, sl.ID asc "; } public function relatorioLiquidacoesSeguradora($id_safra="", $arProduto="", $id_tipo_liquidacao=false, $id_status=false, $fl_seguradora=false, $fl_pagas=false) { $filtro = array(); if($fl_seguradora){ $filtro[] = "pd.id_safra >= 16"; } elseif ($id_safra) { $filtro[] = "pd.id_safra = ".($id_safra); } if($fl_pagas){ $filtro[] = "st.ds_chave in ('INDENIZACAO_PAGA')"; } if($fl_seguradora && !$fl_pagas){ $filtro[] = "st.ds_chave in ('SEM_INDENIZACAO', 'ENVIADA_SEGURADORA','ENVIADA_SEGURADORA_EXCEL', 'INDENIZACAO_PAGA')"; } if(is_array($arProduto) && !$fl_seguradora) { $filtro[] = "pd.id in (".implode("," , $arProduto).")"; } if ($id_tipo_liquidacao) { $filtro[] = "sl.id_tipo_liquidacao = $id_tipo_liquidacao"; } if ($id_status) { $filtro[] = "ls.id_status = $id_status"; } $sql = $this->getConsultaLiquidacoesSeguradora($filtro); return $this->fetchAll($sql); } public function getConferenteLiquidacao($id_liquidacao){ $sql = "SELECT MAX(ls.id), u.ds_nome_usuario, ls.dt_criacao FROM sinistro.liquidacoes_status ls inner join sistema.usuarios u ON u.id = ls.id_usuario_criacao WHERE ls.id_liquidacao = ".$id_liquidacao." and ls.id_status = 185 GROUP BY u.ds_nome_usuario, ls.dt_criacao"; return $this->_db->fetchRow($sql); } public function getLiberadorLiquidacao($id_liquidacao){ $sql = "SELECT MAX(ls.id), u.ds_nome_usuario, ls.dt_criacao FROM sinistro.liquidacoes_status ls inner join sistema.usuarios u ON u.id = ls.id_usuario_criacao WHERE ls.id_liquidacao = ".$id_liquidacao." and ls.id_status = 187 GROUP BY u.ds_nome_usuario, ls.dt_criacao"; return $this->_db->fetchRow($sql); } public function getResponsavelLiquidacao($id_liquidacao){ $sql = "SELECT MAX(ls.id), u.ds_nome_usuario, ls.dt_criacao FROM sinistro.liquidacoes_status ls inner join sistema.usuarios u ON u.id = ls.id_usuario_criacao WHERE ls.id_liquidacao = ".$id_liquidacao." and ls.id_status = 184 GROUP BY u.ds_nome_usuario, ls.dt_criacao"; return $this->_db->fetchRow($sql); } public function relatorioLaudosHortalicas(array $params) { $filtro = array(); if ($params["id_cultura"]) { if (is_array($params["id_cultura"])) { $filtro[] = "c.id in (".implode(",", $params["id_cultura"]).")"; } else { $filtro[] = "c.id = {$params["id_proposta"]}"; } } if (count($filtro)>0) { $filtro = implode(", ", $filtro); } else { $filtro="1=1"; } $sql = " select c.ds_nome_cultura, pr.id_proposta_mae as id_proposta, lfis.id as id_item_segurado, lfacis.nr_amostra as nr_amostra, lfacis.ds_estadio as ds_estadio, lfacis.vl_percent_desfolhamento as vl_percent_desfolhamento, lfacis.vl_percent_dano_bruto as vl_percent_dano_bruto from seguro.propostas p inner join seguro.propostas_endosso pre on pre.id_proposta = p.id and pre.fl_proposta_vigente = true inner join sinistro.processos pr on pr.id_proposta = p.id inner join sinistro.laudos_finais ls on ls.id_processo = pr.id inner join sinistro.laudos_finais_itens_segurados lfis on lfis.id_laudo_final = ls.id inner join {$params["safra"]}.laudos_finais_alho_cebola_granizo_itens_segurados_amostras lfacis on lfacis.id_laudo_final_item_segurado = lfis.id inner join produto.produtos prod on prod.id = p.id_produto inner join produto.produtos_geral prodg on prodg.id = prod.id_produto_geral inner join produto.culturas c on c.id = prodg.id_cultura where $filtro order by p.id "; return $this->fetchAll($sql); } public function relatorioSinistralidade($id_safra = "", $arProduto = "") { $filtro = ""; $whereTemp = ["lfis.fl_nao_vistoriada = false"]; if (!empty($id_safra)) { $filtro = "pd.id_safra = {$id_safra} AND "; $whereTemp[] = "pp.id_safra = {$id_safra}"; } if(!empty($arProduto) && is_array($arProduto)) { $filtro .= "pd.id in (" . implode("," , $arProduto) . ") and "; $whereTemp[] = "pp.id in (" . implode("," , $arProduto) . ")"; } $this->_db->query("CREATE temp TABLE itens_segurados_vistorias AS ( SELECT distinct(its.nr_item_segurado) , pr.id_endosso FROM sinistro.laudos_finais_itens_segurados lfis INNER JOIN seguro.itens_segurados its ON its.id = lfis.id_item_segurado INNER JOIN seguro.propostas pr ON pr.id = its.id_proposta INNER JOIN produto.produtos pp ON pp.id = pr.id_produto WHERE " . implode(' AND ', $whereTemp) . ")"); $sql = "select pr.id_proposta_mae as id_proposta , pr.id_endosso , sp.id as id_processo , pp.ds_nome_proponente , m.ds_nome_municipio , m.nr_ibge , es.ds_sigla , e.ds_nome_fantasia , pd.ds_nome_produto , pd.id_safra , its.id as id_item_segurado , its.nr_item_segurado , its.ds_item_segurado , its.vl_area , its.vl_lmga , v.ds_nome_variedade , pg.id_cultura , cu.ds_nome_cultura from seguro.propostas pr inner join seguro.propostas_endosso pre on pre.id_proposta = pr.id and pre.fl_proposta_vigente = true inner join seguro.itens_segurados its on its.id_proposta = pr.id left join itens_segurados_vistorias itstemp on itstemp.nr_item_segurado = its.nr_item_segurado and itstemp.id_endosso = pr.id_endosso ,seguro.propostas_proponentes pp ,seguro.propostas_propriedades po ,sistema.municipios m ,sistema.estados es ,produto.produtos pd ,produto.produtos_geral pg ,produto.culturas cu ,produto.variedades v ,sinistro.processos sp ,sinistro.processos_empresas pe ,sistema.empresas e where ".$filtro." pe.fl_ativo = true and pd.id = pr.id_produto and pg.id = pd.id_produto_geral and cu.id = pg.id_cultura and pp.id_proposta = pr.id and po.id_proposta = pr.id and m.id = po.id_municipio and es.id = m.id_estado and sp.id_proposta = pr.id and its.id_variedade = v.id and pe.id_processo = sp.id and e.id = pe.id_empresa and pg.id_tipo_produto <> 3 order by pr.id, its.nr_item_segurado"; return $this->fetchAll($sql); } public function relatorioSinistralidadePG($idSafra = "", $arProduto = "") { $where = [ "pg.id_tipo_produto = 3", "its.vl_lmga > 0", "(lfis.fl_nao_vistoriada = false OR (lfis.fl_nao_vistoriada = true and lfis.id_motivo_quadra_nao_vistoriada in (" . Sinistro2_Model_MotivosQuadraNaoVistoriada::SEGURADO_DISPENSA_VISTORIA .", ". Sinistro2_Model_MotivosQuadraNaoVistoriada::AREA_COLHIDA.")))", ]; if (!empty($idSafra)) { $where[] = "pd.id_safra = {$idSafra}"; } if(is_array($arProduto)) { $where[] = "pd.id in (".implode("," , $arProduto).")"; } $where = implode(" AND ", $where); $sql = " SELECT pr.id_proposta_mae ,pr.id AS id_proposta ,pr.id_endosso ,sp.id AS id_processo ,pp.ds_nome_proponente ,m.ds_nome_municipio ,m.nr_ibge ,es.ds_sigla ,e.ds_nome_fantasia ,pd.ds_nome_produto ,pd.id_safra ,its.id AS id_item_segurado ,its.nr_item_segurado ,its.ds_item_segurado ,its.vl_area ,its.vl_lmga ,its.vl_rendimento_tonelada ,v.ds_nome_variedade ,co.ds_nome_cobertura ,co.id AS id_cobertura ,pc.vl_taxa ,pc.vl_franquia ,pg.id_cultura ,cu.ds_nome_cultura ,itsc.vl_producao_garantida ,cu.id AS id_cultura ,string_agg(CASE WHEN (lf.fl_transmitido_tablet IS TRUE) THEN lf.id || ' - Digital' WHEN (lf.fl_transmitido_tablet IS NULL) THEN lf.id || '(-)' ELSE lf.id || ' - Manual' END, ', ') AS fl_transmitido_tablet FROM seguro.propostas pr JOIN seguro.propostas_endosso pre ON pre.id_proposta = pr.id AND pre.fl_proposta_vigente = true JOIN seguro.itens_segurados its ON its.id_proposta = pr.id JOIN seguro.itens_segurados_complemento itsc ON itsc.id_item_segurado = its.id JOIN seguro.propostas_proponentes pp ON pp.id_proposta = pr.id JOIN seguro.propostas_propriedades po ON po.id_proposta = pr.id JOIN seguro.propostas_coberturas pc ON pc.id_proposta = pr.id AND pc.fl_principal = true JOIN produto.coberturas co ON co.id = pc.id_cobertura JOIN sistema.municipios m ON m.id = po.id_municipio JOIN sistema.estados es ON es.id = m.id_estado JOIN produto.produtos pd ON pd.id = pr.id_produto JOIN produto.produtos_geral pg ON pg.id = pd.id_produto_geral JOIN produto.culturas cu ON cu.id = pg.id_cultura JOIN produto.variedades v ON v.id = its.id_variedade JOIN sinistro.processos sp ON sp.id_proposta = pr.id JOIN sinistro.processos_empresas pe ON pe.id_processo = sp.id AND pe.fl_ativo = true JOIN sistema.empresas e ON e.id = pe.id_empresa JOIN sinistro.laudos_finais_itens_segurados lfis ON lfis.id_item_segurado = its.id JOIN sinistro.laudos_finais lf ON lf.id = lfis.id_laudo_final AND lf.id_cobertura = pc.id_cobertura WHERE {$where} GROUP BY pr.id_proposta_mae ,pr.id ,pr.id_endosso ,sp.id ,pp.ds_nome_proponente ,m.ds_nome_municipio ,m.nr_ibge ,es.ds_sigla ,e.ds_nome_fantasia ,pd.ds_nome_produto ,pd.id_safra ,its.id ,its.nr_item_segurado ,its.ds_item_segurado ,its.vl_area ,its.vl_lmga ,its.vl_rendimento_tonelada ,v.ds_nome_variedade ,co.ds_nome_cobertura ,co.id ,pc.vl_taxa ,pc.vl_franquia ,pg.id_cultura ,cu.ds_nome_cultura ,itsc.vl_producao_garantida ,cu.id ,pc.id_cobertura ORDER BY pr.id, pc.id_cobertura, its.nr_item_segurado "; return $this->fetchAll($sql); } public function relatorioVistoriasPreliminares($id_safra="",$arProduto="") { $filtro = []; if (!empty($id_safra)) { $filtro[] = "pd.id_safra = {$id_safra}"; } if(is_array($arProduto)) { $filtro[] = "pd.id in (".implode("," , $arProduto).")"; } $sql = " select pr.id_proposta_mae as id_proposta ,sp.id as id_processo ,pp.ds_nome_proponente ,m.ds_nome_municipio ,m.nr_ibge ,es.ds_sigla ,e.ds_nome_fantasia ,pd.ds_nome_produto ,pd.id_safra ,its.id as id_item_segurado ,its.nr_item_segurado ,its.ds_item_segurado ,its.vl_area ,its.vl_lmga ,v.ds_nome_variedade ,pg.id_cultura ,cu.ds_nome_cultura ,pd.id_safra from seguro.propostas pr inner join seguro.propostas_endosso pre on pre.id_proposta = pr.id and pre.fl_proposta_vigente = true inner join ( select pr.id as id_proposta from sinistro.laudos_preliminares lp, sinistro.processos sp, seguro.propostas pr where lp.id_processo = sp.id and sp.id_proposta = pr.id AND lp.id_status NOT IN ( ". Sistema_Model_Status::LAUDOS_PRELIMINARES_CANCELADO .", ". Sistema_Model_Status::LAUDOS_PRELIMINARES_CANCELADO_CULPA_SEGURADOR ." ) group by pr.id ) as pr_pre on pr_pre.id_proposta = pr.id inner join seguro.itens_segurados its ON its.id_proposta = pr.id inner join seguro.propostas_proponentes pp ON pp.id_proposta = pr.id inner join seguro.propostas_propriedades po ON po.id_proposta = pr.id inner join sistema.municipios m ON m.id = po.id_municipio inner join sistema.estados es ON es.id = m.id_estado 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 cu ON cu.id = pg.id_cultura inner join produto.variedades v ON v.id = its.id_variedade inner join sinistro.processos sp ON sp.id_proposta = pr.id inner join sinistro.processos_empresas pe ON pe.id_processo = sp.id and pe.fl_ativo = true inner join sistema.empresas e ON e.id = pe.id_empresa where ".implode(' AND ', $filtro)." order by pr.id, its.nr_item_segurado "; return $this->fetchAll($sql); } public function relatorioVistoriasPreliminares_backup($id_safra="",$arProduto="") { $filtro = ($id_safra) ? "pd.id_safra=".$id_safra." and " : ''; if(is_array($arProduto)) { $filtro .= "pd.id in (".implode("," , $arProduto).") and "; } $sql = " select pr.id_proposta_mae as id_proposta ,sp.id as id_processo ,pp.ds_nome_proponente ,m.ds_nome_municipio ,m.nr_ibge ,es.ds_sigla ,e.ds_nome_fantasia ,pd.ds_nome_produto ,pd.id_safra ,its.id as id_item_segurado ,its.nr_item_segurado ,its.ds_item_segurado ,its.vl_area ,its.vl_lmga ,v.ds_nome_variedade ,co.ds_nome_cobertura ,co.id as id_cobertura ,pc.vl_taxa ,pc.vl_franquia ,pg.id_cultura ,cu.ds_nome_cultura ,pd.id_safra from seguro.propostas pr inner join seguro.propostas_endosso pre on pre.id_proposta = pr.id and pre.fl_proposta_vigente = true ,seguro.itens_segurados its ,seguro.propostas_proponentes pp ,seguro.propostas_propriedades po ,seguro.propostas_coberturas pc ,produto.coberturas co ,sistema.municipios m ,sistema.estados es ,produto.produtos pd ,produto.produtos_geral pg ,produto.culturas cu ,produto.variedades v ,sinistro.processos sp ,sinistro.processos_empresas pe ,sistema.empresas e where ".$filtro." its.id_proposta = pr.id and pd.id = pr.id_produto and pg.id = pd.id_produto_geral and cu.id = pg.id_cultura and pp.id_proposta = pr.id and po.id_proposta = pr.id and pc.id_proposta = pr.id and co.id = pc.id_cobertura and m.id = po.id_municipio and es.id = m.id_estado and sp.id_proposta = pr.id and its.id_variedade = v.id and pe.id_processo = sp.id and e.id = pe.id_empresa and sp.id in (select id_processo from sinistro.laudos_preliminares where id_cobertura=pc.id_cobertura) order by pr.id, pc.id_cobertura, its.nr_item_segurado "; return $this->fetchAll($sql); } public function relatorioIndenizacaoCobertura($id_safra="",$arProduto="") { //$this->_db->query("CREATE temp TABLE itens_segurados_temp AS (SELECT id_proposta,sum(vl_lmga) AS lmga FROM seguro.itens_segurados GROUP BY id_proposta)"); $select = $this->select()->setIntegrityCheck(false); $select->from(array('pr'=>'seguro.propostas'), array('pr.id_proposta_mae as id_proposta', 'nr_apolice')); $select->join(array('pre'=>'seguro.propostas_endosso'), 'pre.id_proposta = pr.id and pre.fl_proposta_vigente=true', null); $select->join(array('iseg'=>'sumario.itens_segurados'), 'iseg.id_proposta = pr.id', 'vl_lmga as lmga'); $select->join(array('pd'=>'produto.produtos'), 'pd.id = pr.id_produto', 'ds_nome_produto'); $select->join(array('pdg'=>'produto.produtos_geral'), 'pdg.id = pd.id_produto_geral', null); $select->join(array('c'=>'produto.culturas'), 'c.id = pdg.id_cultura', 'ds_nome_cultura'); $select->join(array('pc'=>'sinistro.processos'), 'pc.id_proposta = pr.id', 'id as id_processo'); $select->join(array('li'=>'sinistro.liquidacoes'), 'li.id_processo = pc.id', null); $select->join(array('lisc'=>'sinistro.liquidacoes_itens_segurados_coberturas'), 'lisc.id_liquidacao = li.id', array('li.id as id_liquidacao','sum(lisc.vl_prejuizo_total) as vl_prejuizo','case when sum(lisc.vl_indenizacao) > 0 then sum(lisc.vl_indenizacao) else 0.00 end as indenizacao', 'sum(lisc.vl_franquia) as franquia')); $select->join(array('pco'=>'seguro.propostas_coberturas'), 'pco.id_cobertura = lisc.id_cobertura and pco.id_proposta = pr.id', 'vl_franquia'); $select->join(array('co'=>'produto.coberturas'), 'co.id = lisc.id_cobertura', 'ds_nome_cobertura'); $select->join(array('tl'=>'sinistro.tipo_liquidacao'), 'tl.id = li.id_tipo_liquidacao', 'tl.ds_nome_tipo_liquidacao'); $select->join(array('liqst' => 'sistema.status'), 'li.id_status = liqst.id', 'ds_status as ds_status_liquidacao'); $select->join(array('prco' => 'seguro.propostas_corretores'), 'pr.id = prco.id_proposta', null); $select->join(array('cor' => 'sistema.corretores'), 'prco.id_usuario = cor.id_usuario', 'ds_nome_fantasia as ds_nome_corretor'); $select->join(array('prpro' => 'seguro.propostas_propriedades'), 'pr.id = prpro.id_proposta', null); $select->join(array('mun' => 'sistema.municipios'), 'prpro.id_municipio = mun.id', null); $select->join(array('est' => 'sistema.estados'), 'mun.id_estado = est.id', 'ds_sigla'); $select->order(array('pr.id')); // Caso a safra for informada if ($id_safra) { $select->where('pd.id_safra = ?', $id_safra); } // Caso o usuario selecione algum produto if (is_array($arProduto)) { $select->where('pd.id in ('.implode("," , $arProduto).')'); } $select->group(array( 'pr.id', 'pr.nr_apolice', 'iseg.vl_lmga', 'pd.ds_nome_produto', 'c.ds_nome_cultura', 'pc.id', 'lisc.id_cobertura', 'pco.vl_franquia', 'co.ds_nome_cobertura', 'tl.ds_nome_tipo_liquidacao', 'cor.ds_nome_fantasia', 'est.ds_sigla', 'liqst.ds_status', 'li.id' )); return $this->fetchAll($select); } public function relatorioIndenizacaoCoberturaItem($id_safra="",$arProduto="") { $filtro = ($id_safra) ? "pd.id_safra=".$id_safra." and " : ''; if(is_array($arProduto)) { $filtro .= "pd.id in (".implode("," , $arProduto).") and "; } $sql = " SELECT pr.id_proposta_mae as id_proposta, pr.nr_apolice, iseg.nr_item_segurado, iseg.ds_item_segurado, iseg.vl_lmga, iseg.vl_area, va.ds_nome_variedade, pd.ds_nome_produto, c.ds_nome_cultura, pc.id AS id_processo, li.id as id_liquidacao, lisc.vl_prejuizo_total AS vl_prejuizo, lisc.vl_indenizacao AS indenizacao, lisc.vl_franquia AS franquia, pco.vl_franquia, co.ds_nome_cobertura, tl.ds_nome_tipo_liquidacao, pco.vl_taxa, iseg.vl_lmga FROM seguro.propostas AS pr INNER JOIN seguro.propostas_endosso pre on pre.id_proposta = pr.id and pre.fl_proposta_vigente = true INNER JOIN produto.produtos AS pd ON pd.id = pr.id_produto INNER JOIN produto.produtos_geral AS pdg ON pdg.id = pd.id_produto_geral INNER JOIN produto.culturas AS c ON c.id = pdg.id_cultura INNER JOIN sinistro.processos AS pc ON pc.id_proposta = pr.id INNER JOIN sinistro.liquidacoes AS li ON li.id_processo = pc.id INNER JOIN sinistro.liquidacoes_itens_segurados_coberturas AS lisc ON lisc.id_liquidacao = li.id INNER JOIN seguro.itens_segurados iseg ON iseg.id_proposta = pr.id and lisc.id_item_segurado = iseg.id INNER JOIN produto.variedades va ON va.id = iseg.id_variedade INNER JOIN seguro.propostas_coberturas AS pco ON pco.id_cobertura = lisc.id_cobertura and pco.id_proposta = pr.id INNER JOIN produto.coberturas AS co ON co.id = lisc.id_cobertura INNER JOIN sinistro.tipo_liquidacao AS tl ON tl.id = li.id_tipo_liquidacao WHERE ".$filtro." (1 = 1) ORDER BY pr.id, li.id, iseg.nr_item_segurado ASC "; return $this->fetchAll($sql); } public function processosPendentesLiquidacao($id_safra="", $arProduto="") { $filtroTmp = $filtro = array(); $filtro[] = "(qtd_is_encerrados - qtd_is_liquidados) > 0"; if ($id_safra) { $filtroTmp[] = $filtro[] = "id_safra = $id_safra"; } if(is_array($arProduto)) { $filtroTmp[] = $filtro[] = "id_produto in (".implode("," , $arProduto).")"; } $this->_db->query(" create temp table tmp_processos as ( ( select p.id as id_processo, p.id_proposta, p.fl_pasta_aberta, ( select count(avis.id) as contador FROM sinistro.avisos_encerramento_itens_segurados avis inner join seguro.itens_segurados its on its.id = avis.id_item_segurado and its.vl_lmga > 0 inner join sinistro.avisos_encerramento ave on ave.id = avis.id_aviso_encerramento WHERE ave.id_proposta = p.id_proposta and ave.fl_del = false ) as qtd_is_encerrados, ( SELECT count(*) as contador FROM sinistro.liquidacoes_itens_segurados_coberturas lisc inner join sinistro.liquidacoes l on l.id = lisc.id_liquidacao where l.id_processo = p.id AND lisc.id_cobertura not in (5,16,30,31) ) as qtd_is_liquidados, av.dt_criacao as dt_envio_encerramento, av.dt_encerramento_colheita, u.ds_nome_usuario as ds_nome_usuario_responsavel_liquidacao FROM sinistro.processos p left join sistema.usuarios u on u.id = p.id_usuario_responsavel_liquidacao inner join sinistro.avisos_encerramento av on av.id_proposta = p.id_proposta inner join seguro.propostas pr on pr.id = p.id_proposta inner join produto.produtos pd on pd.id = pr.id_produto WHERE av.fl_del = false and av.fl_ativo = true and ".implode(" and ", $filtroTmp)." ) ) "); $sql = " select distinct processos.id_processo, p.id_proposta_mae as id_proposta, processos.ds_nome_usuario_responsavel_liquidacao, p.nr_apolice, (qtd_is_encerrados - qtd_is_liquidados) as qtd_quadras_pendentes, p.id_produto, prod.ds_nome_produto, prod.id_safra, s.ds_chave, s.ds_nome_safra, case when processos.fl_pasta_aberta then 'Sim' else 'Não' end fl_pasta_aberta, CASE when c.id is not NULL then c.ds_nome_corretor else pre.ds_nome_preposto ||'(Preposto)' end ds_nome_corretor, cu.ds_nome_cultura, to_char(processos.dt_encerramento_colheita, 'DD/MM/YYYY') as dt_encerramento_colheita, to_char(processos.dt_envio_encerramento, 'DD/MM/YYYY') as dt_envio_encerramento, sp.id as id_proponente, m.ds_nome_municipio, e.ds_sigla from tmp_processos as processos inner join seguro.propostas p on p.id = processos.id_proposta inner join seguro.propostas_endosso pe on pe.id_proposta = p.id and pe.fl_proposta_vigente = true inner join seguro.propostas_status ps on ps.id_proposta = p.id inner join sistema.status st on st.id = ps.id_status and st.ds_chave not in ('PROPOSTA_INCOMPLETA' , 'NAO_ENVIADA' , 'DEVOLVIDA' , 'PROPOSTA_CANCELADA' , 'APOLICE_CANCELADA', 'ORCAMENTO_ENDOSSO', 'ENDOSSO_INCOMPLETO', 'SOLICITACAO_CANCELADA') inner join produto.produtos prod on prod.id = p.id_produto inner join produto.produtos_geral pg on pg.id = prod.id_produto_geral inner join produto.culturas cu on cu.id = pg.id_cultura inner join produto.safras s on s.id = prod.id_safra inner join seguro.propostas_corretores pc on pc.id_proposta = p.id left join sistema.corretores c on c.id_usuario = pc.id_usuario left join sistema.prepostos pre on pre.id_usuario = pc.id_usuario inner join seguro.propostas_proponentes proponente on proponente.id_proposta = p.id inner join seguro.proponentes sp on sp.cpf_cnpj = proponente.cpf_cnpj inner join seguro.propostas_propriedades po on po.id_proposta = p.id inner join sistema.municipios m on m.id = po.id_municipio inner join sistema.estados e on e.id = m.id_estado where ".implode(" and ", $filtro)." "; return $this->fetchAll($sql); } public function processosComEncerramentoColheita($id_safra="",$arProduto="", $dataLimite="") { $filtro = ($id_safra) ? "s.id=".$id_safra." and " : ''; if(is_array($arProduto)) { $filtro .= "prod.id in (".implode("," , $arProduto).") and "; } if ($dataLimite) { $filtro .= "ave.dt_encerramento_colheita <= '$dataLimite' and "; } $sql = " SELECT proc.id as id_processo, p.id_proposta_mae as id_proposta, p.nr_apolice, sp.id as id_proponente, emp.ds_nome_fantasia as vistoriador, c.ds_nome_abreviado, cu.ds_nome_cultura, s.ds_nome_safra as safra, prod.ds_nome_abreviado as produto, to_char(ave.dt_encerramento_colheita, 'dd/mm/yyyy') as data_encerramento_colheita, to_char(ave.dt_criacao, 'dd/mm/yyyy') as data_envio, case when proc.fl_pasta_aberta then 'Sim' else '-' end fl_pasta_aberta, case when liq.id is not null then 'Sim' else '-' end liquidacao from sinistro.processos proc inner join sinistro.avisos_encerramento ave on ave.id_proposta = proc.id_proposta inner join seguro.propostas p on p.id = proc.id_proposta inner join seguro.propostas_endosso pre on pre.id_proposta = p.id and pre.fl_proposta_vigente = true inner JOIN produto.produtos prod on prod.id = p.id_produto inner JOIN produto.produtos_geral prodg on prodg.id = prod.id_produto_geral inner join produto.culturas cu ON cu.id = prodg.id_cultura inner join seguro.propostas_proponentes pp on pp.id_proposta = p.id inner join seguro.proponentes sp on sp.cpf_cnpj = pp.cpf_cnpj inner join seguro.propostas_corretores pc on pc.id_proposta = p.id inner join sistema.corretores c on c.id_usuario = pc.id_usuario inner join sinistro.processos_empresas procemp on procemp.id_processo = proc.id and procemp.fl_ativo = true inner join sistema.empresas emp on emp.id = procemp.id_empresa inner join produto.safras s on s.id = prod.id_safra left join sinistro.liquidacoes liq on liq.id_processo = proc.id WHERE ".$filtro." proc.id_status <> ".Agro_Util::status("CANCELADO", "SP")." ORDER BY prod.ds_nome_abreviado, p.id, proc.id "; return $this->fetchAll($sql); } public function relatorioLaudosMaca() { $sql = " (select pr.id_proposta_mae as id_proposta, its.nr_item_segurado, its.vl_lmga, its.vl_area, pc.vl_franquia, l2.id_laudo_final_item_segurado, sf.ds_nome_safra, SUM(l2.nr_depreciacao_1_1) as cat1_cat1, SUM(l2.nr_depreciacao_1_2) as cat1_cat2, SUM(l2.nr_depreciacao_1_3) as cat1_cat3, SUM(l2.nr_depreciacao_1_ind) as cat1_industrial, SUM(l2.nr_depreciacao_2_2) as cat2_cat2, SUM(l2.nr_depreciacao_2_3) as cat2_cat3, SUM(l2.nr_depreciacao_2_ind) as cat2_industrial, SUM(l2.nr_depreciacao_3_3) as cat3_cat3, SUM(l2.nr_depreciacao_3_ind) as cat3_industrial, SUM(l2.nr_depreciacao_ind_ind) as industrial_industrial FROM sinistro.processos p, produto.produtos pd, produto.safras sf, seguro.propostas pr inner join seguro.propostas_endosso pre on pre.id_proposta = pr.id and pre.fl_proposta_vigente = true, seguro.propostas_coberturas pc, sinistro.processos_empresas ep, sistema.empresas e, sinistro.laudos_finais lf, sinistro.laudos_finais_itens_segurados lfis, seguro.itens_segurados its, safra13.laudos_finais_maca_granizo_itens_segurados_amostras l2 where pr.id = p.id_proposta and pc.id_proposta = pr.id and pc.fl_principal = true and pd.id = pr.id_produto and pd.id_safra = sf.id and ep.id_processo = p.id and e.id = ep.id_empresa and lf.id_processo = p.id and lfis.id_laudo_final = lf.id and its.id = lfis.id_item_segurado and l2.id_laudo_final_item_segurado = lfis.id GROUP BY l2.id_laudo_final_item_segurado, pr.id, its.nr_item_segurado, its.vl_area, its.vl_lmga, pc.vl_franquia, sf.ds_nome_safra ) UNION ( select pr.id_proposta_mae as id_proposta, its.nr_item_segurado, its.vl_lmga, its.vl_area, pc.vl_franquia, l.id_laudo_final_item_segurado, sf.ds_nome_safra, SUM(l.cat1_cat1) as cat1_cat1, SUM(l.cat1_cat2) as cat1_cat2, SUM(l.cat1_cat3) as cat1_cat3, SUM(l.cat1_industrial) as cat1_industrial, SUM(l.cat2_cat2) as cat2_cat2, SUM(l.cat2_cat3) as cat2_cat3, SUM(l.cat2_industrial) as cat2_industrial, SUM(l.cat3_cat3) as cat3_cat3, SUM(l.cat3_industrial) as cat3_industrial, SUM(l.industrial_industrial) as industrial_industrial FROM sinistro.processos p, produto.produtos pd, produto.safras sf, seguro.propostas pr inner join seguro.propostas_endosso pre on pre.id_proposta = pr.id and pre.fl_proposta_vigente = true, seguro.propostas_coberturas pc, sinistro.processos_empresas ep, sistema.empresas e, sinistro.laudos_finais lf, sinistro.laudos_finais_itens_segurados lfis, seguro.itens_segurados its, safra14.lf013_itens_segurados_amostras l where pr.id = p.id_proposta and pc.id_proposta = pr.id and pc.fl_principal = true and pd.id = pr.id_produto and pd.id_safra = sf.id and ep.id_processo = p.id and e.id = ep.id_empresa and lf.id_processo = p.id and lfis.id_laudo_final = lf.id and its.id = lfis.id_item_segurado and l.id_laudo_final_item_segurado = lfis.id GROUP BY l.id_laudo_final_item_segurado, pr.id, its.nr_item_segurado, its.vl_area, its.vl_lmga, pc.vl_franquia, sf.ds_nome_safra ) UNION ( select pr.id_proposta_mae as id_proposta, its.nr_item_segurado, its.vl_lmga, its.vl_area, pc.vl_franquia, l.id_laudo_final_item_segurado, sf.ds_nome_safra, SUM(l.cat1_cat1) as cat1_cat1, SUM(l.cat1_cat2) as cat1_cat2, SUM(l.cat1_cat3) as cat1_cat3, SUM(l.cat1_industrial) as cat1_industrial, SUM(l.cat2_cat2) as cat2_cat2, SUM(l.cat2_cat3) as cat2_cat3, SUM(l.cat2_industrial) as cat2_industrial, SUM(l.cat3_cat3) as cat3_cat3, SUM(l.cat3_industrial) as cat3_industrial, SUM(l.industrial_industrial) as industrial_industrial FROM sinistro.processos p, produto.produtos pd, produto.safras sf, seguro.propostas pr inner join seguro.propostas_endosso pre on pre.id_proposta = pr.id and pre.fl_proposta_vigente = true, seguro.propostas_coberturas pc, sinistro.processos_empresas ep, sistema.empresas e, sinistro.laudos_finais lf, sinistro.laudos_finais_itens_segurados lfis, seguro.itens_segurados its, safra15.lf013_itens_segurados_amostras l where pr.id = p.id_proposta and pc.id_proposta = pr.id and pc.fl_principal = true and pd.id = pr.id_produto and pd.id_safra = sf.id and ep.id_processo = p.id and e.id = ep.id_empresa and lf.id_processo = p.id and lfis.id_laudo_final = lf.id and its.id = lfis.id_item_segurado and l.id_laudo_final_item_segurado = lfis.id GROUP BY l.id_laudo_final_item_segurado, pr.id, its.nr_item_segurado, its.vl_area, its.vl_lmga, pc.vl_franquia, sf.ds_nome_safra ) UNION ( select pr.id_proposta_mae as id_proposta, its.nr_item_segurado, its.vl_lmga, its.vl_area, pc.vl_franquia, l.id_laudo_final_item_segurado, sf.ds_nome_safra, SUM(l.cat1_cat1) as cat1_cat1, SUM(l.cat1_cat2) as cat1_cat2, SUM(l.cat1_cat3) as cat1_cat3, SUM(l.cat1_industrial) as cat1_industrial, SUM(l.cat2_cat2) as cat2_cat2, SUM(l.cat2_cat3) as cat2_cat3, SUM(l.cat2_industrial) as cat2_industrial, SUM(l.cat3_cat3) as cat3_cat3, SUM(l.cat3_industrial) as cat3_industrial, SUM(l.industrial_industrial) as industrial_industrial FROM sinistro.processos p, produto.produtos pd, produto.safras sf, seguro.propostas pr inner join seguro.propostas_endosso pre on pre.id_proposta = pr.id and pre.fl_proposta_vigente = true, seguro.propostas_coberturas pc, sinistro.processos_empresas ep, sistema.empresas e, sinistro.laudos_finais lf, sinistro.laudos_finais_itens_segurados lfis, seguro.itens_segurados its, safra16.lf013_itens_segurados_amostras l where pr.id = p.id_proposta and pc.id_proposta = pr.id and pc.fl_principal = true and pd.id = pr.id_produto and pd.id_safra = sf.id and ep.id_processo = p.id and e.id = ep.id_empresa and lf.id_processo = p.id and lfis.id_laudo_final = lf.id and its.id = lfis.id_item_segurado and l.id_laudo_final_item_segurado = lfis.id GROUP BY l.id_laudo_final_item_segurado, pr.id, its.nr_item_segurado, its.vl_area, its.vl_lmga, pc.vl_franquia, sf.ds_nome_safra ) UNION ( select pr.id_proposta_mae as id_proposta, its.nr_item_segurado, its.vl_lmga, its.vl_area, pc.vl_franquia, l.id_laudo_final_item_segurado, sf.ds_nome_safra, SUM(l.cat1_cat1) as cat1_cat1, SUM(l.cat1_cat2) as cat1_cat2, SUM(l.cat1_cat3) as cat1_cat3, SUM(l.cat1_industrial) as cat1_industrial, SUM(l.cat2_cat2) as cat2_cat2, SUM(l.cat2_cat3) as cat2_cat3, SUM(l.cat2_industrial) as cat2_industrial, SUM(l.cat3_cat3) as cat3_cat3, SUM(l.cat3_industrial) as cat3_industrial, SUM(l.industrial_industrial) as industrial_industrial FROM sinistro.processos p, produto.produtos pd, produto.safras sf, seguro.propostas pr inner join seguro.propostas_endosso pre on pre.id_proposta = pr.id and pre.fl_proposta_vigente = true, seguro.propostas_coberturas pc, sinistro.processos_empresas ep, sistema.empresas e, sinistro.laudos_finais lf, sinistro.laudos_finais_itens_segurados lfis, seguro.itens_segurados its, safra17.lf013_itens_segurados_amostras l where pr.id = p.id_proposta and pc.id_proposta = pr.id and pc.fl_principal = true and pd.id = pr.id_produto and pd.id_safra = sf.id and ep.id_processo = p.id and e.id = ep.id_empresa and lf.id_processo = p.id and lfis.id_laudo_final = lf.id and its.id = lfis.id_item_segurado and l.id_laudo_final_item_segurado = lfis.id GROUP BY l.id_laudo_final_item_segurado, pr.id, its.nr_item_segurado, its.vl_area, its.vl_lmga, pc.vl_franquia, sf.ds_nome_safra ) UNION ( select pr.id_proposta_mae as id_proposta, its.nr_item_segurado, its.vl_lmga, its.vl_area, pc.vl_franquia, l.id_laudo_final_item_segurado, sf.ds_nome_safra, SUM(l.cat1_cat1) as cat1_cat1, SUM(l.cat1_cat2) as cat1_cat2, SUM(l.cat1_cat3) as cat1_cat3, SUM(l.cat1_industrial) as cat1_industrial, SUM(l.cat2_cat2) as cat2_cat2, SUM(l.cat2_cat3) as cat2_cat3, SUM(l.cat2_industrial) as cat2_industrial, SUM(l.cat3_cat3) as cat3_cat3, SUM(l.cat3_industrial) as cat3_industrial, SUM(l.industrial_industrial) as industrial_industrial FROM sinistro.processos p, produto.produtos pd, produto.safras sf, seguro.propostas pr inner join seguro.propostas_endosso pre on pre.id_proposta = pr.id and pre.fl_proposta_vigente = true, seguro.propostas_coberturas pc, sinistro.processos_empresas ep, sistema.empresas e, sinistro.laudos_finais lf, sinistro.laudos_finais_itens_segurados lfis, seguro.itens_segurados its, regulacao.lf031_itens_segurados_amostras l where pr.id = p.id_proposta and pc.id_proposta = pr.id and pc.fl_principal = true and pd.id = pr.id_produto and pd.id_safra = sf.id and ep.id_processo = p.id and e.id = ep.id_empresa and lf.id_processo = p.id and lfis.id_laudo_final = lf.id and its.id = lfis.id_item_segurado and l.id_laudo_final_item_segurado = lfis.id GROUP BY l.id_laudo_final_item_segurado, pr.id, its.nr_item_segurado, its.vl_area, its.vl_lmga, pc.vl_franquia, sf.ds_nome_safra ) "; return $this->fetchAll($sql); } public function relatorioNotasFiscais($id_safra="",$arProduto="") { $filtro = ($id_safra) ? "pd.id_safra=".$id_safra." and " : ''; if(is_array($arProduto)) { $filtro .= "pd.id in (".implode("," , $arProduto).") and "; } $sql = " select cr.id as id_relatorio, cr.nr_nota_fiscal, st.ds_chave as ds_status, pr.id_proposta_mae as id_proposta, sp.id as id_processo, u.ds_nome_usuario, e.ds_nome_fantasia, pd.ds_nome_produto, crs.dt_criacao from sinistro.cobranca_relatorio cr, sinistro.cobranca_relatorio_vistorias crv, sinistro.cobranca_relatorio_status crs, sistema.vistorias v, sinistro.vistorias_processos vp, sistema.status st, sinistro.processos sp, seguro.propostas pr inner join seguro.propostas_endosso pre on pre.id_proposta = pr.id and pre.fl_proposta_vigente = true, sistema.usuarios u, sinistro.processos_empresas pe, sistema.empresas e, produto.produtos pd where ".$filtro." cr.nr_nota_fiscal is not null and cr.nr_nota_fiscal <> '' and cr.fl_ativo = true and pe.fl_ativo = true and crs.fl_ativo = true and crv.id_cobranca_relatorio = cr.id and crs.id_cobranca_relatorio = cr.id and v.id = crv.id_vistoria and vp.id_vistoria = v.id and st.id = cr.id_status and sp.id = vp.id_processo and pr.id = sp.id_proposta and u.id = cr.id_usuario_criacao and pe.id_processo = sp.id and e.id = pe.id_empresa and pd.id = pr.id_produto order by cr.nr_nota_fiscal, pr.id "; return $this->fetchAll($sql); } public function sinistrosLiquidacoes($id_safra="",$arProduto="") { $filtro = ($id_safra) ? "pd.id_safra=".$id_safra." and " : ''; if(is_array($arProduto)) { $filtro .= "pd.id in (".implode("," , $arProduto).") and "; } $sql = " select s.ds_nome_safra, pd.ds_nome_produto, ( select distinct count(p1.id) from sinistro.processos p1 inner JOIN seguro.propostas prop1 on prop1.id = p1.id_proposta and prop1.id_produto = pd.id ) as total_processos, ( select distinct count(p3.id) from sinistro.processos p3 inner JOIN seguro.propostas prop3 on prop3.id = p3.id_proposta AND prop3.id_produto = pd.id WHERE p3.id_proposta in (select ae3.id_proposta from sinistro.avisos_encerramento ae3) AND p3.id NOT in (select l.id_processo from sinistro.liquidacoes l) ) as total_processos_aguardando_liquidacao, ( select distinct count(p2.id) from sinistro.processos p2 inner JOIN seguro.propostas prop2 on prop2.id = p2.id_proposta AND prop2.id_produto = pd.id WHERE p2.id in (select l1.id_processo from sinistro.liquidacoes l1 where id_status=111) ) as total_processos_liquidados, ( select distinct count(p4.id) from sinistro.processos p4 inner JOIN seguro.propostas prop4 on prop4.id = p4.id_proposta AND prop4.id_produto = pd.id WHERE p4.id in (select l2.id_processo from sinistro.liquidacoes l2 where id_status=112) ) as total_processos_liquidados_enviados, ( select distinct count(p5.id) from sinistro.processos p5 inner JOIN seguro.propostas prop5 on prop5.id = p5.id_proposta AND prop5.id_produto = pd.id WHERE p5.id in (select l3.id_processo from sinistro.liquidacoes l3 where id_status=113) ) as total_processos_liquidados_pagos FROM produto.safras s, produto.produtos pd WHERE ".$filtro." pd.id_safra = s.id order by s.id desc, pd.ds_nome_produto asc "; return $this->fetchAll($sql); } public function relatorioDocumentosBeneficiarios($id_safra="", $arProduto="", $sinistro=false) { $User = Zend_Auth::getInstance()->getIdentity(); $filtro = ($id_safra) ? "pd.id_safra=".$id_safra : ''; if(is_array($arProduto)) { if($id_safra != '') { $filtro .= " and "; } $filtro .= "pd.id in (".implode("," , $arProduto).")"; } $SubSelect = $this->select()->setIntegrityCheck(false); $SubSelect->from(array('bp' => 'seguro.beneficiarios_pendencias'), array( "to_char(bp.dt_criacao,'dd/mm/yyyy') as dt_criacao_pendencia", 'bp.ds_descricao as ds_descricao_pendencia', 'bp.id_beneficiario')); $SubSelect->joinInner(array('us' => 'sistema.usuarios'), 'us.id = bp.id_usuario_criacao', 'ds_nome_usuario as usuario_criacao_pendencia'); $SubSelect->order('bp.dt_criacao desc'); //$SubSelect->limit(1); $sub = new Zend_Db_Expr('('.$SubSelect.')'); if ( $User->tp_usuario == 'P' || $User->tp_usuario == 'C') { $wh = (($User->tp_usuario == 'P') ? " where pc.id_usuario_criacao = $User->id" : "where pc.id_usuario = $User->id"); $this->_db->query("CREATE temp TABLE beneficiarios AS (SELECT b.id_proposta,case when b.ds_nome_beneficiarios <> '' then b.ds_nome_beneficiarios else ba.ds_nome_banco end as ds_nome_beneficiarios, b.nr_cpf_cnpj from seguro.propostas_beneficiarios b left join sistema.bancos ba on b.id_banco=ba.id inner join seguro.propostas_corretores as pc on pc.id_proposta = b.id_proposta $wh)"); } else { $this->_db->query("CREATE temp TABLE beneficiarios AS (SELECT b.id_proposta,case when b.ds_nome_beneficiarios <> '' then b.ds_nome_beneficiarios else ba.ds_nome_banco end as ds_nome_beneficiarios, b.nr_cpf_cnpj from seguro.propostas_beneficiarios b left join sistema.bancos ba on b.id_banco=ba.id)"); } $Select = $this->select()->setIntegrityCheck(false); $Select->from(array('pb' => 'beneficiarios'), array('ds_nome_beneficiarios', 'pb.nr_cpf_cnpj')); $Select->joinInner(array('b' => 'seguro.beneficiarios'), 'b.cpf_cnpj = pb.nr_cpf_cnpj', false); $Select->joinLeft(array('pendencias' => $sub), 'pendencias.id_beneficiario = b.id', array( 'dt_criacao_pendencia','usuario_criacao_pendencia')); $Select->joinLeft(array('u' => 'sistema.usuarios'), 'u.id = b.id_usuario_alteracao', 'u.ds_nome_usuario'); $Select->joinInner(array('s' => 'sistema.status'), 's.id = b.id_status_arquivos', 's.ds_status'); $Select->joinInner(array('pr' => 'seguro.propostas'), 'pr.id = pb.id_proposta', false); $Select->joinInner(array('pre' => 'seguro.propostas_endosso'), 'pr.id = pre.id_proposta and pre.fl_proposta_vigente=true', false); $Select->joinInner(array('pd' => 'produto.produtos'), 'pd.id = pr.id_produto', false); $Select->where($filtro); $Select->group(array('pb.ds_nome_beneficiarios', 'pb.nr_cpf_cnpj', 'ds_status', 'u.ds_nome_usuario','dt_criacao_pendencia','ds_descricao_pendencia','usuario_criacao_pendencia')); $Select->order(array('pb.ds_nome_beneficiarios')); $dados = $this->fetchAll($Select); if($sinistro){ foreach ($dados as $key => $value){ if($value['nr_cpf_cnpj'] && $id_safra){ $propostas = $this->getPropostasByCpfCnpj($value['nr_cpf_cnpj'], $id_safra); foreach($propostas as $chave => $proposta){ if($chave % 4 == 0 && $chave != 0){ $dados[$key]['propostas'] .= '
'; } $dados[$key]['propostas'] .= " ".$proposta['id_proposta']. ","; } } $dados[$key]['propostas'] = substr_replace($dados[$key]['propostas'], '', -1); if($dados[$key]['propostas'] == ''){ unset($dados[$key]); } } $dados['sinistrados'] = true; } return $dados; } public function getPropostasByCpfCnpj($cpf_cnpj, $idSafra){ $User = Zend_Auth::getInstance()->getIdentity(); try{ $Select = $this->select()->setIntegrityCheck(false); $Select->from(array('pb' => 'seguro.propostas_beneficiarios'), false); $Select->joinInner(array('p' => 'sinistro.processos'), 'p.id_proposta = pb.id_proposta', false); $Select->joinInner(array('pr' => 'seguro.propostas'), 'pr.id = pb.id_proposta', 'id_proposta_mae as id_proposta'); $Select->joinInner(array('pd' => 'produto.produtos'), 'pd.id = pr.id_produto', false); if ($User->tp_usuario == 'P' || $User->tp_usuario == 'C') { $Select->joinInner(array('pc' => 'seguro.propostas_corretores'), 'pc.id_proposta = pb.id_proposta', false); if ($User->tp_usuario == 'P') { $Select->where('pc.id_usuario_criacao = (?)', $User->id); } if ($User->tp_usuario == 'C') { $Select->where('pc.id_usuario = (?)', $User->id ); } } $Select->where('pd.id_safra = (?)', $idSafra); $Select->where('pb.nr_cpf_cnpj = (?)', $cpf_cnpj); $Select->group(array('id_proposta_mae')); return $this->_db->fetchAll($Select); }catch (Exception $e){ die($e); } } public function ajustesReservas($id_safra="", $arProduto="") { $where = []; if (!empty($id_safra)) { $where[] = "pd.id_safra = {$id_safra}"; } if(is_array($arProduto)) { $idsProdutos = implode(", " , $arProduto); $where[] = "pd.id IN ({$idsProdutos})"; } $where = !empty($where) ? "WHERE " . implode("AND ", $where) : ''; $sqlTmpCobertura = " CREATE TEMP TABLE tmp_cobertura AS ( SELECT avisos_transmissao_seguradora.id_aviso , avisos.id_proposta , propostas_coberturas.id_cobertura AS cobertura_principal_contratada , avisos_transmissao_seguradora.id_cobertura AS cobertura_aviso , CASE WHEN (xpath('//Cobertura/text()', propostas_transmissao.ds_xml_transmissao::XML))[1]::TEXT = '240' THEN 1 WHEN (xpath('//Cobertura/text()', propostas_transmissao.ds_xml_transmissao::XML))[1]::TEXT = '2291' THEN 1 WHEN (xpath('//Cobertura/text()', propostas_transmissao.ds_xml_transmissao::XML))[1]::TEXT = '239' THEN 3 WHEN (xpath('//Cobertura/text()', propostas_transmissao.ds_xml_transmissao::XML))[1]::TEXT = '2290' THEN 3 WHEN (xpath('//Cobertura/text()', propostas_transmissao.ds_xml_transmissao::XML))[1]::TEXT = '252' THEN 11 WHEN (xpath('//Cobertura/text()', propostas_transmissao.ds_xml_transmissao::XML))[1]::TEXT = '2303' THEN 11 WHEN (xpath('//Cobertura/text()', propostas_transmissao.ds_xml_transmissao::XML))[1]::TEXT = '244' THEN 18 WHEN (xpath('//Cobertura/text()', propostas_transmissao.ds_xml_transmissao::XML))[1]::TEXT = '2295' THEN 18 WHEN (xpath('//Cobertura/text()', propostas_transmissao.ds_xml_transmissao::XML))[1]::TEXT = '340' THEN 32 WHEN (xpath('//Cobertura/text()', propostas_transmissao.ds_xml_transmissao::XML))[1]::TEXT = '2282' THEN 32 END AS cobertura_xml FROM sinistro.avisos_transmissao_seguradora JOIN sinistro.avisos ON avisos.id = avisos_transmissao_seguradora.id_aviso JOIN seguro.propostas ON propostas.id = avisos.id_proposta JOIN seguro.propostas_endosso ON propostas_endosso.id_proposta = propostas.id JOIN seguro.propostas_endosso AS endossos ON endossos.id_endosso = propostas_endosso.id_endosso JOIN log.propostas_transmissao ON propostas_transmissao.id_proposta = endossos.id_proposta AND propostas_transmissao.fl_sucesso = TRUE AND propostas_transmissao.ds_tipo_transmissao = 'InserirAvisoSinistro' AND propostas_transmissao.ds_xml_transmissao != 'Array' AND propostas_transmissao.ds_retorno ILIKE '%'||avisos.id::TEXT||'%' JOIN seguro.propostas_coberturas ON propostas_coberturas.id_proposta = propostas.id JOIN produto.produtos_coberturas ON produtos_coberturas.id_produto = propostas.id_produto AND produtos_coberturas.id_cobertura = propostas_coberturas.id_cobertura AND produtos_coberturas.fl_cobertura_principal = TRUE ); "; $this->_db->query($sqlTmpCobertura); $sql = " SELECT seguro.id_proposta_mae(pr.id) as id_proposta_original, pr.id as id_proposta_endosso, pr.nr_apolice, pd.ds_nome_produto, co.ds_nome_cobertura, co.id as id_cobertura, ats.id_aviso, ats.cd_identificador_seguradora as id_aviso_seguradora, ats.vl_reserva, ats.vl_indenizacao, st.ds_chave FROM sinistro.avisos_transmissao_seguradora ats JOIN sinistro.avisos av ON av.id = ats.id_aviso JOIN seguro.propostas pr ON pr.id = av.id_proposta JOIN produto.produtos pd ON pd.id = pr.id_produto JOIN tmp_cobertura ON tmp_cobertura.id_aviso = av.id JOIN produto.coberturas co ON co.id = tmp_cobertura.cobertura_xml JOIN sistema.status st ON st.id = av.id_status {$where} GROUP BY pr.id , pr.nr_apolice , pd.ds_nome_produto , co.ds_nome_cobertura , co.id , ats.id_aviso , ats.cd_identificador_seguradora , ats.vl_reserva , ats.vl_indenizacao , st.ds_chave "; return $this->fetchAll($sql); } public function relatorioCobranca($id_safra="",$arProduto="") { $filtro = ($id_safra) ? "pd.id_safra=".$id_safra." and " : ''; if(is_array($arProduto)) { $filtro .= "pd.id in (".implode("," , $arProduto).") and "; } $sql = "select * from (select pr.id_proposta_mae as id_proposta, seguro.id_proposta_mae_completo(pr.id) as id_proposta_mae_completo, pr.nr_apolice, pd.ds_nome_produto, sf.ds_nome_safra, e.ds_nome_fantasia as empresa, cr.id as id_relatorio_cobranca, cr.nr_nota_fiscal, st.ds_chave, crv.vl_honorarios, crv.vl_quilometragem as km_rodado, crv.vl_custo_quilometragem as custo_km_rodado, crv.vl_pedagio, crv.vl_hotel, crv.vl_alimentacao, sum(crv.vl_alimentacao + crv.vl_hotel + crv.vl_pedagio + crv.vl_custo_quilometragem) as vl_total_despesas, crv.id_vistoria, tv.ds_tipo, array_to_string(array_agg(f.ds_nome_funcionario::text) OVER (PARTITION BY v.id), ', ') AS vistoriadores, to_char(lf.dt_vistoria, 'YYYY-MM-DD HH24:MI:SS'::text) AS dt_vistoria, CASE WHEN (lf.fl_transmitido_tablet IS TRUE) THEN 'Digital' WHEN (lf.fl_transmitido_tablet IS NULL) THEN '-' ELSE 'Manual' END AS fl_transmitido_tablet from sinistro.cobranca_relatorio_vistorias crv inner join sinistro.cobranca_relatorio cr on cr.id = crv.id_cobranca_relatorio inner join sistema.status st on st.id = cr.id_status inner join sistema.vistorias v on v.id = crv.id_vistoria inner join sistema.tipos_vistoria tv on tv.id = v.id_tipo inner join sinistro.vistorias_processos vp on vp.id_vistoria = v.id inner join sinistro.processos p on p.id = vp.id_processo inner join seguro.propostas pr on pr.id = p.id_proposta inner join produto.produtos pd on pd.id = pr.id_produto inner join produto.safras sf on sf.id = pd.id_safra inner join sistema.vistorias_empresas ve on ve.id_vistoria = v.id inner join sistema.empresas e on e.id = ve.id_empresa inner join sinistro.laudos_finais as lf on lf.id_processo = vp.id_processo inner join sistema.vistorias_laudos_finais vlf on vlf.id_vistoria = v.id and vlf.id_laudo_final = lf.id left join sinistro.laudos_finais_funcionarios lff on lff.id_laudo_final = vlf.id_laudo_final left join sistema.funcionarios f on f.id = lff.id_funcionario left join sistema.usuarios u on u.id = f.id_usuario where ".$filtro." st.ds_chave = 'CONFERIDO' and tv.ds_tipo = 'Final' and u.tp_usuario = '". Sistema_Model_Usuarios::TIPO_USUARIO_VISTORIADOR ."' group by pr.id, pr.nr_apolice, pd.ds_nome_produto, sf.ds_nome_safra, cr.id, e.ds_nome_fantasia, cr.nr_nota_fiscal, st.ds_chave, crv.vl_honorarios, crv.vl_quilometragem, crv.vl_custo_quilometragem , crv.vl_pedagio, crv.vl_hotel, crv.vl_alimentacao, crv.id_vistoria, tv.ds_tipo, f.ds_nome_funcionario, v.id, lf.dt_vistoria, fl_transmitido_tablet UNION select pr.id_proposta_mae as id_proposta, seguro.id_proposta_mae_completo(pr.id) as id_proposta_mae_completo, pr.nr_apolice, pd.ds_nome_produto, sf.ds_nome_safra, e.ds_nome_fantasia as empresa, cr.id as id_relatorio_cobranca, cr.nr_nota_fiscal, st.ds_chave, crv.vl_honorarios, crv.vl_quilometragem as km_rodado, crv.vl_custo_quilometragem as custo_km_rodado, crv.vl_pedagio, crv.vl_hotel, crv.vl_alimentacao, sum(crv.vl_alimentacao + crv.vl_hotel + crv.vl_pedagio + crv.vl_custo_quilometragem) as vl_total_despesas, crv.id_vistoria, tv.ds_tipo, array_to_string(array_agg(f.ds_nome_funcionario::text) OVER (PARTITION BY v.id), ', ') AS vistoriadores, to_char(lp.dt_vistoria, 'YYYY-MM-DD HH24:MI:SS'::text) AS dt_vistoria, 'Não' AS fl_transmitido_tablet from sinistro.cobranca_relatorio_vistorias crv inner join sinistro.cobranca_relatorio cr on cr.id = crv.id_cobranca_relatorio inner join sistema.status st on st.id = cr.id_status inner join sistema.vistorias v on v.id = crv.id_vistoria inner join sistema.tipos_vistoria tv on tv.id = v.id_tipo inner join sinistro.vistorias_processos vp on vp.id_vistoria = v.id inner join sinistro.processos p on p.id = vp.id_processo inner join seguro.propostas pr on pr.id = p.id_proposta inner join produto.produtos pd on pd.id = pr.id_produto inner join produto.safras sf on sf.id = pd.id_safra inner join sistema.vistorias_empresas ve on ve.id_vistoria = v.id inner join sistema.empresas e on e.id = ve.id_empresa inner join sistema.vistorias_laudos_preliminares vlp on vlp.id_vistoria = v.id inner join sinistro.laudos_preliminares lp on lp.id = vlp.id_laudo_preliminar left join sistema.vistorias_funcionarios vf on vf.id_vistoria = v.id left join sistema.funcionarios f on f.id = vf.id_funcionario left join sistema.usuarios u on u.id = f.id_usuario where ".$filtro." st.ds_chave = 'CONFERIDO' and tv.ds_tipo = 'Preliminar' and u.tp_usuario = '". Sistema_Model_Usuarios::TIPO_USUARIO_VISTORIADOR ."' group by pr.id, pr.nr_apolice, pd.ds_nome_produto, sf.ds_nome_safra, cr.id, e.ds_nome_fantasia, cr.nr_nota_fiscal, st.ds_chave, crv.vl_honorarios, crv.vl_quilometragem, crv.vl_custo_quilometragem , crv.vl_pedagio, crv.vl_hotel, crv.vl_alimentacao, crv.id_vistoria, tv.ds_tipo, f.ds_nome_funcionario, v.id, lp.dt_vistoria, fl_transmitido_tablet UNION select pr.id_proposta_mae as id_proposta, seguro.id_proposta_mae_completo(pr.id) as id_proposta_mae_completo, pr.nr_apolice, pd.ds_nome_produto, sf.ds_nome_safra, e.ds_nome_fantasia as empresa, cr.id as id_relatorio_cobranca, cr.nr_nota_fiscal, st.ds_chave, crv.vl_honorarios, crv.vl_quilometragem as km_rodado, crv.vl_custo_quilometragem as custo_km_rodado, crv.vl_pedagio, crv.vl_hotel, crv.vl_alimentacao, sum(crv.vl_alimentacao + crv.vl_hotel + crv.vl_pedagio + crv.vl_custo_quilometragem) as vl_total_despesas, crv.id_vistoria, tv.ds_tipo, array_to_string(array_agg(f.ds_nome_funcionario::text) OVER (PARTITION BY v.id), ', ') AS vistoriadores, concat(to_char(lvp1.dt_laudo, 'YYYY-MM-DD'::text), ' ', lvp1.hr_laudo) AS dt_vistoria, CASE WHEN (pvp.fl_transmitido_tablet IS TRUE) THEN 'Digital' WHEN (pvp.fl_transmitido_tablet IS NULL) THEN '-' ELSE 'Manual' END AS fl_transmitido_tablet from sinistro.cobranca_relatorio_vistorias crv inner join sinistro.cobranca_relatorio cr on cr.id = crv.id_cobranca_relatorio inner join sistema.status st on st.id = cr.id_status inner join sistema.vistorias v on v.id = crv.id_vistoria inner join sistema.tipos_vistoria tv on tv.id = v.id_tipo inner join seguro.propostas_vistorias_previas pvp on pvp.id_vistoria = v.id inner join seguro.propostas pr on pr.id = pvp.id_proposta inner join produto.produtos pd on pd.id = pr.id_produto inner join produto.safras sf on sf.id = pd.id_safra inner join sistema.vistorias_empresas ve on ve.id_vistoria = v.id inner join sistema.empresas e on e.id = ve.id_empresa inner join regulacao.lvp001 lvp1 on lvp1.id_proposta_vistoria = pvp.id left join sistema.vistorias_funcionarios vf on vf.id_vistoria = v.id left join sistema.funcionarios f on f.id = vf.id_funcionario left join sistema.usuarios u on u.id = f.id_usuario where ".$filtro." st.ds_chave = 'CONFERIDO' and tv.ds_tipo = 'Prévia' and u.tp_usuario = '". Sistema_Model_Usuarios::TIPO_USUARIO_VISTORIADOR ."' group by pr.id, pr.nr_apolice, pd.ds_nome_produto, sf.ds_nome_safra, cr.id, e.ds_nome_fantasia, cr.nr_nota_fiscal, st.ds_chave, crv.vl_honorarios, crv.vl_quilometragem, crv.vl_custo_quilometragem , crv.vl_pedagio, crv.vl_hotel, crv.vl_alimentacao, crv.id_vistoria, tv.ds_tipo, f.ds_nome_funcionario, v.id, lvp1.dt_laudo, lvp1.hr_laudo, fl_transmitido_tablet ) cobranca order by cobranca.id_relatorio_cobranca "; return $this->fetchAll($sql); } public function processosPendentesLiquidacaoNovo($id_safra="", $arProduto="", string $tipoRelatorio = 'BASICO') { $filtroTmp = $filtro = array(); $filtro[] = "(qtd_is_encerrados - qtd_is_liquidados) > 0"; if ($id_safra) { $filtroTmp[] = $filtro[] = "id_safra = $id_safra"; } if (is_array($arProduto)) { $filtroTmp[] = $filtro[] = "pd.id in (" . implode(",", $arProduto) . ")"; } $sqlTmpProcessos = " create temp table tmp_processos as (( SELECT DISTINCT p.id AS id_processo , p.id_proposta , p.fl_pasta_aberta , COALESCE(qtd_is_encerrados.contador, 0) AS qtd_is_encerrados , COALESCE(qtd_is_liquidados.contador, 0) AS qtd_is_liquidados , av.dt_criacao AS dt_envio_encerramento , av.dt_encerramento_colheita AS dt_encerramento_colheita , u.ds_nome_usuario AS ds_nome_usuario_responsavel_liquidacao , liqis.id AS id_liqis , ss.id AS ds_id_status_documentacao_processo , ss.ds_status AS ds_status_documentacao_processo , CASE WHEN st.ds_chave is NULL THEN '' WHEN st.ds_chave = 'ATIVO' THEN 'Ativo' ELSE 'Inativo' END AS ds_status_cronometro , observacao_cronometro.ds_observacao AS ds_observacao_cronometro , cr.id AS id_cronometro , cr.dt_inicio AS dt_inicio_cronometro , cr.dt_fim AS dt_fim_cronometro , cr.nr_dias_suspenso AS nr_dias_suspenso_cronometro , cr.nr_dias_regulado AS prazo_cronometro , sl.dt_base_pagamento AS dt_previsao_pagamento , av.id AS id_aviso_encerramento FROM sinistro.processos p INNER JOIN sinistro.avisos_encerramento av ON av.id_proposta = p.id_proposta AND av.fl_ativo = TRUE AND av.fl_del = FALSE INNER JOIN seguro.propostas pr ON pr.id = p.id_proposta INNER JOIN produto.produtos pd ON pd.id = pr.id_produto INNER JOIN seguro.propostas_coberturas pdc ON pr.id = pdc.id_proposta INNER JOIN produto.coberturas pc ON pc.id = pdc.id_cobertura AND pc.id NOT IN (5,16,30,31) INNER JOIN sinistro.avisos_encerramento_itens_segurados avis ON avis.id_aviso_encerramento = av.id INNER JOIN sistema.status ss ON ss.id = p.id_status_arquivos LEFT JOIN sinistro.liquidacoes_itens_segurados_coberturas liqis ON liqis.id_cobertura = pc.id AND liqis.id_item_segurado = avis.id_item_segurado AND liqis.fl_del = FALSE -- AND liqis.id IS NULL LEFT JOIN sinistro.liquidacoes sl ON sl.id_processo = p.id LEFT JOIN sistema.usuarios u ON u.id = p.id_usuario_responsavel_liquidacao LEFT JOIN ( SELECT COUNT(lisc.id) AS contador , id_processo FROM sinistro.liquidacoes_itens_segurados_coberturas lisc INNER JOIN sinistro.liquidacoes l ON l.id = lisc.id_liquidacao WHERE lisc.id_cobertura NOT IN (5,16,30,31) GROUP BY id_processo ) AS qtd_is_liquidados ON qtd_is_liquidados.id_processo = p.id LEFT JOIN ( SELECT COUNT(avis.id) AS contador , ave.id_proposta FROM sinistro.avisos_encerramento_itens_segurados avis INNER JOIN seguro.itens_segurados its ON its.id = avis.id_item_segurado AND its.vl_lmga > 0 INNER JOIN sinistro.avisos_encerramento ave ON ave.id = avis.id_aviso_encerramento WHERE ave.fl_del = FALSE GROUP BY ave.id_proposta ) AS qtd_is_encerrados ON qtd_is_encerrados.id_proposta = pr.id LEFT JOIN sinistro.cronometro_regulacao cr ON cr.id_aviso_encerramento = av.id LEFT JOIN sistema.status st ON st.id = cr.id_status LEFT JOIN LATERAL ( SELECT ds_observacao FROM log.cronometro_regulacao_historico crh WHERE crh.id_cronometro = cr.id ORDER BY id DESC LIMIT 1 ) AS observacao_cronometro ON true WHERE p.id_status <> " . Agro_Util::status("CANCELADO", "SP") . " and " . implode(" and ", $filtroTmp) . " ORDER BY p.id , p.id_proposta , p.fl_pasta_aberta , av.dt_criacao , av.dt_encerramento_colheita ) ); "; $this->_db->query($sqlTmpProcessos); $camposAdicionaisCompleto = ''; $joinsAdicionaisCompleto = ''; if ($tipoRelatorio === 'COMPLETO') { $safra = !empty($id_safra) ? " AND pp.id_safra = {$id_safra}" : ""; $this->_db->query(" CREATE TEMP TABLE tmp_status_arquivos_beneficiarios as ( SELECT array_to_string(array_agg(ss.ds_status), ' / ') as ds_status_beneficiarios, array_to_string(array_agg(ss.id), ' / ') as ds_id_status_beneficiarios, spb.id_proposta FROM seguro.propostas_beneficiarios spb INNER JOIN seguro.beneficiarios sb ON sb.cpf_cnpj = spb.nr_cpf_cnpj INNER JOIN sistema.status ss ON ss.id = sb.id_status_arquivos INNER JOIN seguro.propostas sprop ON sprop.id = spb.id_proposta INNER JOIN sinistro.processos sproc ON sproc.id_proposta = sprop.id INNER JOIN produto.produtos pp ON pp.id = sprop.id_produto WHERE spb.fl_del is false {$safra} GROUP BY spb.id_proposta ) "); $this->_db->query(" CREATE TEMP TABLE tmp_ultimos_arquivos_beneficiarios as ( SELECT array_to_string(array_agg(data_criacao_maior), ' / ') as data_ulimo_arquivo, id_proposta FROM ( SELECT to_char(MAX(sba.dt_criacao), 'DD/MM/YYYY') as data_criacao_maior, spb.id_proposta as id_proposta, sb.id FROM seguro.propostas_beneficiarios spb INNER JOIN seguro.beneficiarios sb ON sb.cpf_cnpj = spb.nr_cpf_cnpj INNER JOIN sistema.status ss ON ss.id = sb.id_status_arquivos INNER JOIN seguro.propostas sprop ON sprop.id = spb.id_proposta INNER JOIN sinistro.processos sproc ON sproc.id_proposta = sprop.id INNER JOIN produto.produtos pp ON pp.id = sprop.id_produto INNER JOIN seguro.beneficiarios_arquivos sba ON sba.id_beneficiario = sb.id WHERE spb.fl_del is false {$safra} GROUP BY spb.id_proposta, sb.id) as x GROUP BY id_proposta ) "); $this->_db->query(" CREATE TEMP TABLE tmp_data_maior_arquivo_beneficiario as ( SELECT to_char(MAX(sba.dt_criacao), 'DD/MM/YYYY') as data_criacao_maior, MAX(sba.dt_criacao) as maior_data_arquivos_beneficiario, spb.id_proposta as id_proposta FROM seguro.propostas_beneficiarios spb INNER JOIN seguro.beneficiarios sb ON sb.cpf_cnpj = spb.nr_cpf_cnpj INNER JOIN sistema.status ss ON ss.id = sb.id_status_arquivos INNER JOIN seguro.propostas sprop ON sprop.id = spb.id_proposta INNER JOIN sinistro.processos sproc ON sproc.id_proposta = sprop.id INNER JOIN produto.produtos pp ON pp.id = sprop.id_produto INNER JOIN seguro.beneficiarios_arquivos sba ON sba.id_beneficiario = sb.id WHERE spb.fl_del is false {$safra} GROUP BY spb.id_proposta ) "); $this->_db->query(" CREATE TEMP TABLE tmp_processos_observacoes AS ( SELECT id_processo, ARRAY_TO_STRING(ARRAY_AGG(tl.ds_chave || ' - ' || pcp.ds_observacao), '
') AS ds_observacao FROM sinistro.processos_coberturas_pendencias pcp JOIN produto.produtos_coberturas AS pc ON pc.id_produto = pcp.id_produto AND pc.id_cobertura = pcp.id_cobertura JOIN sinistro.tipo_liquidacao AS tl ON tl.id = pc.id_tipo_liquidacao WHERE pcp.id IN ( SELECT max(pcp.id) FROM sinistro.processos_coberturas_pendencias pcp JOIN produto.produtos pd ON pd.id = pcp.id_produto WHERE ". implode(" AND ", $filtroTmp) ." GROUP BY id_processo, id_cobertura ) GROUP BY id_processo ); "); $camposAdicionaisCompleto = [ "se.ds_nome_fantasia as ds_empresa_vistoria", "c.ds_nome_fantasia as ds_corretora", "to_char(now() - processos.dt_envio_encerramento, 'DD') as dias_aguardando_liquidacao", "processos.ds_status_documentacao_processo", "processos.ds_id_status_documentacao_processo", "to_char(spa.dt_criacao, 'DD/MM/YYYY') as ultimo_documento_enviado", "tsab.ds_status_beneficiarios as ds_status_documentacao_beneficiario", "tsab.ds_id_status_beneficiarios as ds_id_status_documentacao_beneficiario", "tuab.data_ulimo_arquivo as ultimo_documento_beneficiario", "processos.dt_envio_encerramento as data_envio_encerramento_sem_formatacao", "spa.dt_criacao as maior_data_arquivos_processo", "tdmab.maior_data_arquivos_beneficiario", "tpo.ds_observacao", ]; $joinsAdicionaisCompleto = [ "inner join sinistro.processos_empresas spe ON spe.id_processo = processos.id_processo AND spe.fl_ativo is true", "inner join sistema.empresas se ON se.id = spe.id_empresa", "left join (SELECT MAX(spa_max.dt_criacao) as dt_criacao, id_processo FROM sinistro.processos_arquivos spa_max INNER JOIN sistema.tipo_arquivo_processo stap ON stap.id = spa_max.id_tipo_arquivo WHERE stap.ds_chave_tipo_arquivo <> 'LIQUIDACAO' GROUP BY id_processo) as spa ON spa.id_processo = processos.id_processo", "left join tmp_status_arquivos_beneficiarios tsab ON tsab.id_proposta = processos.id_proposta", "left join tmp_ultimos_arquivos_beneficiarios tuab ON tuab.id_proposta = processos.id_proposta", "left join tmp_data_maior_arquivo_beneficiario tdmab ON tdmab.id_proposta = processos.id_proposta", "left join tmp_processos_observacoes tpo ON tpo.id_processo = processos.id_processo", ]; $camposAdicionaisCompleto = ', ' . implode(', ', $camposAdicionaisCompleto); $joinsAdicionaisCompleto = implode(' ', $joinsAdicionaisCompleto); } $sql = "select distinct processos.id_processo, "; $sql .= " p.id_proposta_mae as id_proposta, "; $sql .= " processos.ds_nome_usuario_responsavel_liquidacao, "; $sql .= " p.nr_apolice, "; $sql .= " (qtd_is_encerrados - qtd_is_liquidados) as qtd_quadras_pendentes, "; $sql .= " p.id_produto, "; $sql .= " pd.ds_nome_produto, "; $sql .= " pd.id_safra, "; $sql .= " s.ds_chave, "; $sql .= " s.ds_nome_safra, "; $sql .= " case "; $sql .= " when processos.fl_pasta_aberta then 'Sim' "; $sql .= " else 'Não' "; $sql .= " end fl_pasta_aberta, "; $sql .= " CASE "; $sql .= " when c.id is not NULL then c.ds_nome_corretor "; $sql .= " else pre.ds_nome_preposto ||'(Preposto)' "; $sql .= " end ds_nome_corretor, "; $sql .= " cu.ds_nome_cultura, "; $sql .= " to_char(processos.dt_encerramento_colheita, 'DD/MM/YYYY') as dt_encerramento_colheita, "; $sql .= " to_char(processos.dt_envio_encerramento, 'DD/MM/YYYY') as dt_envio_encerramento, "; $sql .= " ( "; $sql .= " SELECT "; $sql .= " array_to_string( "; $sql .= " ARRAY( "; $sql .= " SELECT "; $sql .= " CASE "; $sql .= " WHEN pb.ds_tipo_conta = 'n' AND pb.tp_pessoa = 'F' "; $sql .= " THEN substring(trim(pb.ds_nome_beneficiarios) from '[A-z]{0,}') || ' (' || overlay(pb.nr_cpf_cnpj placing '.___.___' from 4 for 8) || ')' "; $sql .= " WHEN pb.ds_tipo_conta = 'n' AND pb.tp_pessoa = 'J' "; $sql .= " THEN pb.ds_nome_beneficiarios || ' (' || pb.nr_cpf_cnpj || ')' "; $sql .= " ELSE sb.ds_nome_banco || ' (' || pb.nr_cpf_cnpj || ')' "; $sql .= " END AS beneficiario "; $sql .= " FROM seguro.propostas_beneficiarios pb "; $sql .= " LEFT JOIN sistema.bancos sb ON sb.id = pb.id_banco "; $sql .= " WHERE "; $sql .= " pb.id_proposta = P.id "; $sql .= " ), "; $sql .= " ' / ' "; $sql .= " ) "; $sql .= " ) AS ds_beneficiarios, "; $sql .= " sp.id as id_proponente, "; $sql .= " m.ds_nome_municipio, "; $sql .= " e.ds_sigla, "; $sql .= " processos.id_cronometro, "; $sql .= " processos.ds_status_cronometro, "; $sql .= " processos.ds_observacao_cronometro, "; $sql .= " processos.dt_inicio_cronometro, "; $sql .= " processos.dt_fim_cronometro, "; $sql .= " processos.nr_dias_suspenso_cronometro, "; $sql .= " processos.prazo_cronometro, "; $sql .= " to_char(processos.dt_previsao_pagamento, 'DD/MM/YYYY') as dt_previsao_pagamento, "; $sql .= " processos.id_aviso_encerramento "; $sql .= " {$camposAdicionaisCompleto} "; $sql .= " from tmp_processos as processos "; $sql .= " inner join seguro.propostas p on p.id = processos.id_proposta "; $sql .= " inner join seguro.propostas_endosso pe on pe.id_proposta = p.id "; $sql .= " and pe.fl_proposta_vigente = true "; $sql .= " inner join seguro.propostas_status ps on ps.id_proposta = p.id "; $sql .= " inner join sistema.status st on st.id = ps.id_status "; $sql .= " and st.ds_chave not in ('PROPOSTA_INCOMPLETA' , 'NAO_ENVIADA' , 'DEVOLVIDA' , 'PROPOSTA_CANCELADA' , 'APOLICE_CANCELADA', 'ORCAMENTO_ENDOSSO', 'ENDOSSO_INCOMPLETO', 'SOLICITACAO_CANCELADA') "; $sql .= " inner join produto.produtos pd on pd.id = p.id_produto "; $sql .= " inner join produto.produtos_geral pg on pg.id = pd.id_produto_geral "; $sql .= " inner join produto.culturas cu on cu.id = pg.id_cultura "; $sql .= " inner join produto.safras s on s.id = pd.id_safra "; $sql .= " inner join seguro.propostas_corretores pc on pc.id_proposta = p.id "; $sql .= " left join sistema.corretores c on c.id_usuario = pc.id_usuario "; $sql .= " left join sistema.prepostos pre on pre.id_usuario = pc.id_usuario "; $sql .= " inner join seguro.propostas_proponentes proponente on proponente.id_proposta = p.id "; $sql .= " inner join seguro.proponentes sp on sp.cpf_cnpj = proponente.cpf_cnpj "; $sql .= " inner join seguro.propostas_propriedades po on po.id_proposta = p.id "; $sql .= " inner join sistema.municipios m on m.id = po.id_municipio "; $sql .= " inner join sistema.estados e on e.id = m.id_estado "; $sql .= " {$joinsAdicionaisCompleto} "; $sql .= "where processos.id_liqis IS NULL AND " . implode(" and ", $filtro); return $this->fetchAll($sql); } public function cobrancaGeral(array $params) { $filtro = array(); $filtroPreliminarFinal = array(); $filtroPrevia = array(); $dataVistoriaInicial = $dataVistoriaFinal = ''; if (!empty($params['dt_vistoria_ini'])) { $aux = explode('/', $params['dt_vistoria_ini']); $dataVistoriaInicial = "{$aux[2]}-{$aux[1]}-{$aux[0]}"; array_push($filtroPreliminarFinal, "laudos.dt_vistoria >= '{$dataVistoriaInicial}'"); array_push($filtroPrevia, "pvp.dt_vistoria >= '{$dataVistoriaInicial}'"); } if (!empty($params['dt_vistoria_fim'])) { $aux = explode('/', $params['dt_vistoria_fim']); $dataVistoriaFinal = "{$aux[2]}-{$aux[1]}-{$aux[0]}"; array_push($filtroPreliminarFinal, "laudos.dt_vistoria <= '{$dataVistoriaFinal}'"); array_push($filtroPrevia, "pvp.dt_vistoria <= '{$dataVistoriaFinal}'"); } if ($params['id_status'] > 0) { array_push($filtro, 'cr.id_status = '. $params['id_status'].' '); } if ($params['tipo_cobranca'] > 0) { switch ($params['tipo_cobranca']) { case 1: array_push($filtro, "tp_vistoria::text = 'Previa'"); break; case 2: array_push($filtro, "tp_vistoria::text != 'Previa'"); break; } } if (empty($dataVistoriaInicial) && empty($dataVistoriaInicial)) { $idsSafras = implode(", ", $params['id_safra']); array_push($filtro, "id_safra IN ({$idsSafras})"); } $filtroPrevia = array_merge($filtro, $filtroPrevia); $filtroPreliminarFinal = array_merge($filtro, $filtroPreliminarFinal); //Consulta do relatório Resumido (Padrão) if($params['modelo_relatorio'] == 'R'){ $sqlPreliminarFinal = " SELECT vlcpf.id_proposta_mae, vlcpf.ds_nome_fantasia AS nome_empresa, cr.id AS id_relatorio, cr.nr_nota_fiscal, vlcpf.ds_nome_proponente AS nome_segurado, vlcpf.ds_nome_municipio AS municipio_empresa, vlcpf.ds_sigla AS uf_empresa, to_char(vlcpf.dt_vistoria, 'DD/MM/YYYY') AS data_vistoria, to_char(vlcpf.dt_vistoria, 'HH24:MI'::text) AS hora_vistoria, crv.vl_quilometragem AS qtde_quilometragem, crv.vl_hotel AS custo_hotel, crv.vl_alimentacao, vlcpf.id_vistoria, vlcpf.perito AS nome_perito, vlcpf.municipio_perito, vlcpf.id_perito, coalesce(vlcpf.vl_area_vistoriada, 0) as vl_area_vistoriada, vlcpf.auxiliar, vlcpf.nr_apolice, CASE WHEN (vlcpf.fl_transmitido_tablet IS TRUE) THEN 'Digital' WHEN (vlcpf.fl_transmitido_tablet IS NULL) THEN '-' ELSE 'Manual' END AS fl_transmitido_tablet FROM sinistro.cobranca_relatorio AS cr JOIN sinistro.cobranca_relatorio_vistorias AS crv ON crv.id_cobranca_relatorio = cr.id JOIN sistema.vistorias AS v ON v.id = crv.id_vistoria JOIN sinistro.v_laudos_cobranca_preliminar_final AS vlcpf ON vlcpf.id_vistoria = v.id INNER JOIN ( SELECT vlf.id_vistoria, lf1.dt_vistoria FROM sistema.vistorias_laudos_finais vlf INNER JOIN sinistro.laudos_finais lf1 ON lf1.id = vlf.id_laudo_final UNION SELECT vlp.id_vistoria, lp1.dt_vistoria FROM sistema.vistorias_laudos_preliminares vlp INNER JOIN sinistro.laudos_preliminares lp1 ON lp1.id = vlp.id_laudo_preliminar ) laudos ON laudos.id_vistoria = vlcpf.id_vistoria JOIN produto.produtos pd ON pd.id = vlcpf.id_produto JOIN sistema.status ss ON ss.id = cr.id_status JOIN produto.safras sf ON sf.id = pd.id_safra WHERE ".implode(' and ', $filtroPreliminarFinal)." "; $sqlPrevia = " SELECT vlcp.id_proposta_mae, vlcp.ds_nome_fantasia AS nome_empresa, cr.id AS id_relatorio, cr.nr_nota_fiscal, vlcp.ds_nome_proponente AS nome_segurado, vlcp.ds_nome_municipio AS municipio_empresa, vlcp.ds_sigla AS uf_empresa, to_char(vlcp.dt_vistoria::date,'DD/MM/YYYY') AS data_vistoria, to_char(pvp.hr_laudo, 'HH24:MI'::text) AS hora_vistoria, crv.vl_quilometragem AS qtde_quilometragem, crv.vl_hotel AS custo_hotel, crv.vl_alimentacao, vlcp.id_vistoria, vlcp.perito AS nome_perito, vlcp.municipio_perito, vlcp.id_perito AS id_perito, coalesce(pvp.vl_area_vistoriada, 0) as vl_area_vistoriada, '' as auxiliar, vlcp.nr_apolice, CASE WHEN (vlcp.fl_transmitido_tablet IS TRUE) THEN 'Digital' WHEN (vlcp.fl_transmitido_tablet IS NULL) THEN '-' ELSE 'Manual' END AS fl_transmitido_tablet FROM sinistro.cobranca_relatorio AS cr JOIN sinistro.cobranca_relatorio_vistorias AS crv ON crv.id_cobranca_relatorio = cr.id JOIN sistema.vistorias AS v ON v.id = crv.id_vistoria JOIN sinistro.v_laudos_cobranca_previa AS vlcp ON vlcp.id_vistoria = crv.id_vistoria JOIN sistema.status ss ON ss.id = cr.id_status JOIN (SELECT DISTINCT string_agg(lvpis.nr_quadra, ', ') as itens, pvp.id_vistoria, lvp.hr_laudo, pvp.dt_laudo as dt_vistoria, sum(lvpis.vl_area_vistoriada) as vl_area_vistoriada FROM regulacao.lvp001_itens_segurados AS lvpis JOIN regulacao.lvp001 AS lvp ON lvpis.id_laudo_previa = lvp.id JOIN seguro.propostas_vistorias_previas AS pvp ON lvp.id_proposta_vistoria = pvp.id GROUP BY pvp.id, pvp.dt_laudo, lvp.hr_laudo) as pvp ON pvp.id_vistoria = vlcp.id_vistoria WHERE ".implode(' and ', $filtroPrevia)." "; } else { //Consulta do relatório Completo $sqlPreliminarFinal = ''; $sqlPrevia = ''; $sqlPrevia = " SELECT DISTINCT cr.id as id_relatorio , cr.id_status , cr.dt_criacao , cr.nr_nota_fiscal , crv.id_vistoria , crv.vl_alimentacao , crv.vl_custo_quilometragem , crv.vl_honorarios , crv.vl_hotel , crv.vl_pedagio , crv.vl_quilometragem , (crv.vl_custo_quilometragem + crv.vl_hotel + crv.vl_pedagio + crv.vl_alimentacao) as vl_despesas , vlcp.ds_nome_fantasia , vlcp.cpf_cnpj_empresa , vlcp.ds_nome_municipio , vlcp.ds_sigla , to_char(vlcp.dt_vistoria::date,'YYYY-MM-DD'::text) as dt_vistoria , vlcp.id_proposta_mae , vlcp.id_tipo_produto ,pvp.itens ,coalesce(area_vistoriada.vl_area_vistoriada, 0) as vl_area_vistoriada ,vlcp.tp_vistoria::text ,vlcp.perito ,vlcp.ds_nome_abreviado ,ss.ds_status ,itens_proposta.area_total_proposta ,to_char(pvp.hr_laudo, 'HH24:MI'::text) as hr_vistoria ,vlcp.id_vistoria ,vlcp.id_safra ,vlcp.ds_nome_safra ,info_bancaria.ds_agencia ,info_bancaria.nu_digito_agencia ,info_bancaria.ds_conta ,info_bancaria.nu_digito_conta ,info_bancaria.nr_banco ,vlcp.ds_nome_municipio_empresa ,vlcp.ds_sigla_uf_empresa ,vlcp.municipio_perito ,e.ds_razao_social ,crs.dt_criacao::TIMESTAMP as dt_fechamento ,CASE WHEN info_bancaria.ds_tipo_conta IS NULL THEN '' WHEN info_bancaria.ds_tipo_conta = 'C' THEN 'Corrente' ELSE 'Poupança' END AS tipo_conta ,vlcp.nr_apolice ,CASE WHEN (vlcp.fl_transmitido_tablet IS TRUE) THEN 'Digital' WHEN (vlcp.fl_transmitido_tablet IS NULL) THEN '-' ELSE 'Manual' END AS fl_transmitido_tablet FROM sinistro.cobranca_relatorio AS cr INNER JOIN sinistro.cobranca_relatorio_vistorias AS crv ON crv.id_cobranca_relatorio = cr.id INNER JOIN sistema.vistorias AS v ON v.id = crv.id_vistoria INNER JOIN sistema.vistorias_empresas AS ve ON ve.id_vistoria = v.id AND ve.id_vistoria = crv.id_vistoria AND ve.fl_ativo IS TRUE INNER JOIN sistema.empresas e ON e.id = ve.id_empresa INNER JOIN sinistro.v_laudos_cobranca_previa vlcp ON vlcp.id_vistoria = crv.id_vistoria AND vlcp.id_vistoria = v.id INNER JOIN sinistro.cobranca_relatorio_status AS crs ON crs.id_cobranca_relatorio = crv.id_cobranca_relatorio AND crs.fl_ativo IS TRUE INNER JOIN sistema.status ss ON ss.id = cr.id_status INNER JOIN (SELECT DISTINCT string_agg(lvpis.nr_quadra, ', ') as itens, pvp.id_vistoria, pvp.dt_laudo as dt_vistoria, lvp.hr_laudo FROM regulacao.lvp001_itens_segurados AS lvpis INNER JOIN regulacao.lvp001 AS lvp ON lvpis.id_laudo_previa = lvp.id INNER JOIN seguro.propostas_vistorias_previas AS pvp ON lvp.id_proposta_vistoria = pvp.id GROUP BY pvp.id, pvp.dt_laudo, lvp.hr_laudo) as pvp ON pvp.id_vistoria = vlcp.id_vistoria INNER JOIN (select sum(lvpis.vl_area_vistoriada) as vl_area_vistoriada, pvp.id_vistoria from regulacao.lvp001_itens_segurados lvpis, regulacao.lvp001 lvp, seguro.propostas_vistorias_previas pvp where lvpis.id_laudo_previa = lvp.id and lvp.id_proposta_vistoria = pvp.id GROUP BY pvp.id_vistoria) as area_vistoriada ON area_vistoriada.id_vistoria = vlcp.id_vistoria INNER JOIN seguro.proposta_area_item_segurado as itens_proposta ON itens_proposta.id_proposta = vlcp.id_proposta LEFT JOIN ( SELECT ds_agencia ,nu_digito_agencia ,ds_conta ,nu_digito_conta ,nr_banco ,x.id_empresa ,sib.ds_tipo_conta FROM ( SELECT max(sibe.id_informacoes_bancarias) as id_info_bancaria ,sibe.id_empresa FROM sistema.informacoes_bancarias_empresas sibe GROUP BY sibe.id_empresa ) as x LEFT JOIN sistema.informacoes_bancarias sib ON sib.id = x.id_info_bancaria LEFT JOIN sistema.bancos sb ON sb.id = sib.id_banco ) AS info_bancaria ON info_bancaria.id_empresa = vlcp.id_empresa WHERE ".implode(' and ', $filtroPrevia); $sqlPreliminarFinal = " select cr.id as id_relatorio , cr.id_status , cr.dt_criacao , cr.nr_nota_fiscal , crv.id_vistoria , crv.vl_alimentacao , crv.vl_custo_quilometragem , crv.vl_honorarios , crv.vl_hotel , crv.vl_pedagio , crv.vl_quilometragem , (crv.vl_custo_quilometragem + crv.vl_hotel + crv.vl_pedagio + crv.vl_alimentacao) as vl_despesas , vlcpf.ds_nome_fantasia , vlcpf.cpf_cnpj_empresa , vlcpf.ds_nome_municipio , vlcpf.ds_sigla , to_char(laudos.dt_vistoria, 'YYYY-MM-DD'::text) as dt_vistoria , vlcpf.id_proposta_mae , vlcpf.id_tipo_produto , vlcpf.itens , coalesce(vlcpf.vl_area_vistoriada, 0) as vl_area_vistoriada , vlcpf.tp_vistoria , vlcpf.perito , pd.ds_nome_abreviado , ss.ds_status ,itens_proposta.area_total_proposta , to_char(laudos.dt_vistoria, 'HH24:MI'::text) as hr_vistoria , vlcpf.id_vistoria , pd.id_safra , sf.ds_nome_safra , info_bancaria.ds_agencia , info_bancaria.nu_digito_agencia , info_bancaria.ds_conta , info_bancaria.nu_digito_conta , info_bancaria.nr_banco ,vlcpf.ds_nome_municipio_empresa ,vlcpf.ds_sigla_uf_empresa ,vlcpf.municipio_perito ,e.ds_razao_social ,crs.dt_criacao::TIMESTAMP as dt_fechamento ,CASE WHEN info_bancaria.ds_tipo_conta IS NULL THEN '' WHEN info_bancaria.ds_tipo_conta = 'C' THEN 'Corrente' ELSE 'Poupança' END AS tipo_conta ,vlcpf.nr_apolice ,CASE WHEN (vlcpf.fl_transmitido_tablet IS TRUE) THEN 'Digital' WHEN (vlcpf.fl_transmitido_tablet IS NULL) THEN '-' ELSE 'Manual' END AS fl_transmitido_tablet FROM sinistro.cobranca_relatorio AS cr INNER JOIN sinistro.cobranca_relatorio_vistorias AS crv ON crv.id_cobranca_relatorio = cr.id INNER JOIN sistema.vistorias AS v ON v.id = crv.id_vistoria INNER JOIN sistema.vistorias_empresas AS ve ON ve.id_vistoria = v.id AND ve.id_vistoria = crv.id_vistoria AND ve.fl_ativo IS TRUE INNER JOIN sistema.empresas e ON e.id = ve.id_empresa INNER JOIN sinistro.v_laudos_cobranca_preliminar_final vlcpf ON vlcpf.id_vistoria = crv.id_vistoria AND vlcpf.id_vistoria = v.id INNER JOIN ( SELECT vlf.id_vistoria, lf1.dt_vistoria FROM sistema.vistorias_laudos_finais vlf INNER JOIN sinistro.laudos_finais lf1 ON lf1.id = vlf.id_laudo_final UNION SELECT vlp.id_vistoria, lp1.dt_vistoria FROM sistema.vistorias_laudos_preliminares vlp INNER JOIN sinistro.laudos_preliminares lp1 ON lp1.id = vlp.id_laudo_preliminar ) laudos ON laudos.id_vistoria = vlcpf.id_vistoria INNER JOIN sinistro.cobranca_relatorio_status AS crs ON crs.id_cobranca_relatorio = crv.id_cobranca_relatorio AND crs.fl_ativo IS TRUE INNER JOIN produto.produtos pd ON pd.id = vlcpf.id_produto INNER JOIN sistema.status ss ON ss.id = cr.id_status INNER JOIN seguro.proposta_area_item_segurado as itens_proposta ON itens_proposta.id_proposta = vlcpf.id_proposta INNER JOIN produto.safras sf ON sf.id = pd.id_safra LEFT JOIN ( SELECT ds_agencia ,nu_digito_agencia ,ds_conta ,nu_digito_conta ,nr_banco ,x.id_empresa ,sib.ds_tipo_conta FROM ( SELECT max(sibe.id_informacoes_bancarias) as id_info_bancaria ,sibe.id_empresa FROM sistema.informacoes_bancarias_empresas sibe GROUP BY sibe.id_empresa ) as x LEFT JOIN sistema.informacoes_bancarias sib ON sib.id = x.id_info_bancaria LEFT JOIN sistema.bancos sb ON sb.id = sib.id_banco ) AS info_bancaria ON info_bancaria.id_empresa = vlcpf.id_empresa WHERE ".implode(' and ', $filtroPreliminarFinal)." "; } if ($params['tipo_cobranca'] == 1) { $sql = $sqlPrevia; } elseif ($params['tipo_cobranca'] == 2) { $sql = $sqlPreliminarFinal; } else { $sql = "{$sqlPrevia} union {$sqlPreliminarFinal}"; } if($params['modelo_relatorio'] == 'R') { $sql .= 'ORDER BY id_proposta_mae desc, nome_perito ASC'; } else { $sql .= ' ORDER BY id_proposta_mae desc, perito ASC'; } return $this->fetchAll($sql); } public function relatorioSinistroImportacaoEssor($id_safra="",$arProduto="") { $this->_db->query("CREATE temp TABLE tmp_processo_lmga AS ( SELECT inter.id_processo , SUM(inter.lmga) as vl_lmga_processo FROM ( SELECT l.id_processo , isc.id_item_segurado , it.vl_lmga AS lmga FROM sinistro.liquidacoes_itens_segurados_coberturas isc inner join sinistro.liquidacoes l on l.id = isc.id_liquidacao inner join seguro.itens_segurados it on it.id = isc.id_item_segurado WHERE id_cobertura not in (18,29,32) GROUP BY l.id_processo , isc.id_item_segurado , it.vl_lmga ) AS inter GROUP BY inter.id_processo );" ); $this->_db->query("CREATE temp TABLE soma_liquidacoes_temp AS ( SELECT case when SUM(vl_indenizacao) > 0 then SUM(vl_indenizacao) else 0.0 end as vl_indenizacao ,case when SUM(vl_prejuizo_total) > 0 then SUM(vl_prejuizo_total) else 0.0 end as vl_prejuizo , (select sum(vl_lmga) from seguro.itens_segurados where id in (select distinct(id_item_segurado) from sinistro.liquidacoes_itens_segurados_coberturas where id_liquidacao=lisc.id_liquidacao)) as vl_lmga ,case when SUM(vl_area) > 0 then SUM(vl_area) else 0.0 end as vl_area ,id_liquidacao ,id_cobertura ,case when lisc.id_cobertura in (18,32) then round(sum(iseg.vl_area * isc.vl_producao_garantida)/sum(vl_area) - (sum(iseg.vl_area * isc.vl_producao_garantida)/sum(vl_area) * (sum(vl_indenizacao) / sum(vl_lmga))), 3) end as po_media FROM sinistro.liquidacoes_itens_segurados_coberturas lisc inner join seguro.itens_segurados iseg on iseg.id = lisc.id_item_segurado inner join seguro.itens_segurados_complemento isc on isc.id_item_segurado = iseg.id inner join produto.coberturas co on co.id = lisc.id_cobertura GROUP BY id_liquidacao,lisc.id_cobertura); " ); $this->_db->query("CREATE temp TABLE avisos_temp AS ( SELECT min(cd_identificador_seguradora) as cd_identificador_seguradora, min(av.dt_criacao) as dt_criacao_aviso, av.id_proposta FROM sinistro.avisos av inner join sinistro.avisos_transmissao_seguradora ats on ats.id_aviso = av.id WHERE av.id_status=28 GROUP BY av.id_proposta); " ); $this->_db->query("CREATE temp TABLE laudos_temp AS ( SELECT least(tbl_dt_vistoria.dt_vistoria_preliminar,tbl_dt_vistoria.dt_vistoria) as min_dt_vistoria, id_processo FROM ( SELECT min(l.dt_vistoria) as dt_vistoria, min(lp.dt_vistoria) as dt_vistoria_preliminar, l.id_processo FROM sinistro.laudos_finais l left join sinistro.laudos_preliminares lp on lp.id_processo = l.id_processo and lp.id_status = 76 WHERE l.id_status=110 GROUP BY l.id_processo ) AS tbl_dt_vistoria ); " ); $this->_db->query("CREATE temp TABLE liquidacoes_sumarizadas_temp AS ( SELECT distinct sl.id_processo, sum(vl_area) as vl_area, sum(slt.vl_indenizacao) as vl_indenizacao, sum(slt.vl_prejuizo) as vl_prejuizo, case when slt.id_cobertura not in (18,32) then round((sum(vl_prejuizo) / (vl_lmga_processo)*100), 2) end as vl_perda_media, po_media as vl_po_media FROM sinistro.liquidacoes sl inner join sinistro.tipo_liquidacao tl on tl.id = sl.id_tipo_liquidacao inner join soma_liquidacoes_temp slt on slt.id_liquidacao = sl.id inner join sinistro.liquidacoes_status ls on ls.id_liquidacao = sl.id and ls.fl_ativo = true inner join sistema.status st on st.id = ls.id_status left join tmp_processo_lmga tpl on tpl.id_processo = sl.id_processo WHERE sl.id in (select id_liquidacao from sinistro.liquidacoes_itens_segurados_coberturas) and st.ds_chave in ('INDENIZACAO_PAGA') group by sl.id_processo, po_media, vl_lmga_processo, slt.id_cobertura ); " ); $this->_db->query("CREATE temp TABLE liquidacoes_dt_base_tmp AS ( SELECT distinct sl.id_processo, max(ls.dt_criacao) as dt_base FROM sinistro.liquidacoes sl inner join sinistro.liquidacoes_status ls on ls.id_liquidacao = sl.id and ls.fl_ativo = true group by sl.id_processo );" ); $sql = " SELECT distinct sf.ds_nome_safra, sp.id AS id_processo, pr.id_proposta_mae as id_proposta, pr.nr_apolice, dtb.dt_base, av.cd_identificador_seguradora, em.cpf_cnpj, em.ds_nome_fantasia, case when lt.min_dt_vistoria is null then date(dt_criacao_aviso) + interval '15' day else lt.min_dt_vistoria end as min_dt_vistoria, its.vl_area, sum(lst.vl_indenizacao) as vl_indenizacao, case when sum(lst.vl_perda_media) > 0 then sum(lst.vl_perda_media) else 0 end as vl_perda_media, case when sum(lst.vl_po_media) > 0 then sum(lst.vl_po_media) else 0 end as vl_po_media FROM sinistro.processos sp inner join sinistro.processos_empresas pe on pe.id_processo = sp.id and pe.fl_ativo=true inner join sistema.empresas em on em.id = pe.id_empresa inner join seguro.propostas pr on pr.id = sp.id_proposta inner join seguro.propostas_endosso pre on pre.id_proposta = pr.id and pre.fl_proposta_vigente = true inner join seguro.propostas pr_original on pr_original.id = pr.id_proposta_mae inner join produto.produtos pd on pd.id = pr.id_produto inner join produto.produtos_geral pg on pd.id_produto_geral = pg.id inner join produto.safras sf on sf.id = pd.id_safra inner join liquidacoes_sumarizadas_temp lst on lst.id_processo = sp.id inner join sumario.itens_segurados its on its.id_proposta = sp.id_proposta inner join liquidacoes_dt_base_tmp dtb on dtb.id_processo = sp.id left join avisos_temp av on av.id_proposta = sp.id_proposta left join laudos_temp lt on lt.id_processo = sp.id WHERE pr_original.dt_transmissao >= '2018-11-01' GROUP BY sp.id, pr.id_proposta_mae , av.cd_identificador_seguradora, pr.nr_apolice, em.cpf_cnpj, em.ds_nome_fantasia, sf.ds_nome_safra, lt.min_dt_vistoria, its.vl_area, pg.id_tipo_produto, dtb.dt_base, dt_criacao_aviso ORDER BY id_proposta"; return $this->fetchAll($sql); } public function relatorioSinistroImportacaoEssorSemIndenizacao($id_safra="",$arProduto="") { $this->_db->query("CREATE temp TABLE tmp_processo_lmga AS ( SELECT inter.id_processo , SUM(inter.lmga) as vl_lmga_processo FROM ( SELECT l.id_processo , isc.id_item_segurado , it.vl_lmga AS lmga FROM sinistro.liquidacoes_itens_segurados_coberturas isc inner join sinistro.liquidacoes l on l.id = isc.id_liquidacao inner join seguro.itens_segurados it on it.id = isc.id_item_segurado WHERE id_cobertura not in (18,29,32) GROUP BY l.id_processo , isc.id_item_segurado , it.vl_lmga ) AS inter GROUP BY inter.id_processo );" ); $this->_db->query("CREATE temp TABLE avisos_temp AS ( SELECT min(cd_identificador_seguradora) as cd_identificador_seguradora, min(av.dt_criacao) as dt_criacao_aviso, av.id_proposta FROM sinistro.avisos av inner join sinistro.avisos_transmissao_seguradora ats on ats.id_aviso = av.id WHERE av.id_status=28 GROUP BY av.id_proposta); " ); $this->_db->query("CREATE temp TABLE laudos_temp AS ( SELECT least(tbl_dt_vistoria.dt_vistoria_preliminar,tbl_dt_vistoria.dt_vistoria) as min_dt_vistoria, id_processo FROM ( SELECT min(l.dt_vistoria) as dt_vistoria, min(lp.dt_vistoria) as dt_vistoria_preliminar, l.id_processo FROM sinistro.laudos_finais l left join sinistro.laudos_preliminares lp on lp.id_processo = l.id_processo and lp.id_status = 76 WHERE l.id_status=110 GROUP BY l.id_processo ) AS tbl_dt_vistoria ); " ); $sql = " SELECT distinct sf.ds_nome_safra, sp.id AS id_processo, pr.id_proposta_mae as id_proposta, pr.nr_apolice, '' as dt_base, av.cd_identificador_seguradora, em.cpf_cnpj, em.ds_nome_fantasia, case when lt.min_dt_vistoria is null then date(dt_criacao_aviso) + interval '15' day else lt.min_dt_vistoria end as min_dt_vistoria, its.vl_area, 0 as vl_indenizacao, '' as vl_perda_media, '' as vl_po_media FROM sinistro.processos sp inner join sinistro.processos_empresas pe on pe.id_processo = sp.id and pe.fl_ativo=true inner join sistema.empresas em on em.id = pe.id_empresa inner join seguro.propostas pr on pr.id = sp.id_proposta inner join seguro.propostas_endosso pre on pre.id_proposta = pr.id and pre.fl_proposta_vigente = true inner join seguro.propostas pr_original on pr_original.id = pr.id_proposta_mae inner join produto.produtos pd on pd.id = pr.id_produto inner join produto.produtos_geral pg on pd.id_produto_geral = pg.id inner join produto.safras sf on sf.id = pd.id_safra inner join sumario.itens_segurados its on its.id_proposta = sp.id_proposta inner join avisos_temp av on av.id_proposta = sp.id_proposta left join laudos_temp lt on lt.id_processo = sp.id WHERE pr_original.dt_transmissao >= '2018-11-01' AND sp.id NOT IN ( SELECT sl.id_processo FROM sinistro.liquidacoes sl inner join sinistro.liquidacoes_status ls on ls.id_liquidacao = sl.id and ls.fl_ativo = true inner join sistema.status st on st.id = ls.id_status WHERE st.ds_chave in ('INDENIZACAO_PAGA') ) GROUP BY sp.id, pr.id_proposta_mae , av.cd_identificador_seguradora, pr.nr_apolice, em.cpf_cnpj, em.ds_nome_fantasia, sf.ds_nome_safra, lt.min_dt_vistoria, its.vl_area, pg.id_tipo_produto, dt_criacao_aviso ORDER BY id_proposta"; return $this->fetchAll($sql); } public function relatorioAcompanhamentoVistoriasSinistro(int $idSafra, array $produtos = [], array $tiposVistoria = []) { $filtros = [ "prod.id_safra = {$idSafra}", "avi.id_status IN (" . Sinistro2_Model_AvisosSinistro::ID_STATUS_SA_ACEITO . "," . Sinistro2_Model_AvisosSinistro::ID_STATUS_SA_PENDENTE . ")" ]; if (!empty($produtos)) { $produtos = implode(', ', $produtos); $filtros[] = "prod.id in({$produtos})"; } if (!empty($tiposVistoria)) { $tiposVistoria = implode(', ', $tiposVistoria); $filtros[] = "v.id_tipo in({$tiposVistoria})"; } else { $tiposVistoria = implode(', ', [ Sistema_Model_TipoVistoria::PRELIMINAR, Sistema_Model_TipoVistoria::FINAL ]); $filtros[] = "v.id_tipo in({$tiposVistoria})"; } $sql = "SELECT prop.id_proposta_mae as id_proposta "; $sql .= " , prod.ds_nome_produto as produto "; $sql .= " , cu.ds_nome_cultura as cultura "; $sql .= " , e.ds_sigla as uf "; $sql .= " , prop.id as id_endosso "; $sql .= " , proc.id as id_processo "; $sql .= " , v.id as id_vistoria "; $sql .= " , tv.ds_tipo as tipo_vistoria "; $sql .= " , sa.ds_status as status_agendamento "; $sql .= " , CASE WHEN vlp.id_laudo_preliminar is not null "; $sql .= " THEN slp.ds_status "; $sql .= " ELSE slf.ds_status "; $sql .= " END as status_vistoria "; $sql .= " , ARRAY_TO_STRING(ARRAY_AGG(avi.id), ', ', '') as avisos "; $sql .= " FROM seguro.propostas prop "; $sql .= " INNER JOIN produto.produtos prod ON prod.id = prop.id_produto "; $sql .= " INNER JOIN produto.produtos_geral pg ON pg.id = prod.id_produto_geral "; $sql .= " INNER JOIN produto.culturas cu ON cu.id = pg.id_cultura "; $sql .= " INNER JOIN seguro.propostas_propriedades pp ON pp.id_proposta = prop.id "; $sql .= " INNER JOIN sistema.municipios m ON m.id = pp.id_municipio "; $sql .= " INNER JOIN sistema.estados e ON e.id = m.id_estado "; $sql .= " INNER JOIN sinistro.processos proc ON proc.id_proposta = prop.id "; $sql .= " INNER JOIN sinistro.vistorias_processos vp ON vp.id_processo = proc.id "; $sql .= " INNER JOIN sistema.vistorias v ON v.id = vp.id_vistoria "; $sql .= " INNER JOIN sistema.tipos_vistoria tv ON tv.id = v.id_tipo "; $sql .= " INNER JOIN sistema.vistorias_agendamentos vagend ON vagend.id_vistoria = v.id "; $sql .= " INNER JOIN sistema.agendamentos a ON a.id = vagend.id_agendamento "; $sql .= " INNER JOIN sistema.status sa ON sa.id = a.id_status "; $sql .= " INNER JOIN sinistro.avisos avi ON avi.id_proposta = prop.id "; $sql .= " LEFT JOIN sistema.vistorias_laudos_preliminares vlp ON vlp.id_vistoria = v.id "; $sql .= " LEFT JOIN sinistro.laudos_preliminares lp ON lp.id = vlp.id_laudo_preliminar "; $sql .= " LEFT JOIN sistema.status slp ON slp.id = lp.id_status "; $sql .= " LEFT JOIN sistema.vistorias_laudos_finais vlf ON vlf.id_vistoria = v.id "; $sql .= " LEFT JOIN sinistro.laudos_finais lf ON lf.id = vlf.id_laudo_final "; $sql .= " LEFT JOIN sistema.status slf ON slf.id = lf.id_status "; $sql .= " WHERE " . implode(' AND ', $filtros); $sql .= " GROUP BY prop.id_proposta_mae, prod.ds_nome_produto, cu.ds_nome_cultura, e.ds_sigla, prop.id, proc.id, v.id, tv.ds_tipo, sa.ds_status, vlp.id_laudo_preliminar, slp.ds_status, slf.ds_status "; $sql .= " ORDER BY prop.id_proposta_mae desc, prop.id desc"; return $this->fetchAll($sql); } /** * * @param int $idSafra * @param array $idProdutos * @return array */ public function conferenciaDocumentos(int $idSafra = null, ?array $idProdutos = null): array { $filtros = []; if (!empty($idSafra)) { $filtros[] = "pd.id_safra = {$idSafra}"; } if (!empty($idProdutos)) { $filtros[] = "pd.id in (".implode("," , $idProdutos).")"; } $sql = " SELECT pap.id_processo, "; $sql .= " p.id_proposta, "; $sql .= " CASE WHEN nr_tipo = " . Sinistro2_Model_ProcessosArquivosPendencias::TIPO_DECLARAÇÃO_PRELIMINAR; $sql .= " THEN '" . Sinistro2_Model_ProcessosArquivosPendencias::getDescricaoByTipo(Sinistro2_Model_ProcessosArquivosPendencias::TIPO_DECLARAÇÃO_PRELIMINAR) . "'"; $sql .= " WHEN nr_tipo = " . Sinistro2_Model_ProcessosArquivosPendencias::TIPO_DECLARAÇÃO_FINAL; $sql .= " THEN '" . Sinistro2_Model_ProcessosArquivosPendencias::getDescricaoByTipo(Sinistro2_Model_ProcessosArquivosPendencias::TIPO_DECLARAÇÃO_FINAL) . "'"; $sql .= " WHEN nr_tipo = " . Sinistro2_Model_ProcessosArquivosPendencias::TIPO_AUTORIZAÇÃO_FINAL; $sql .= " THEN '" . Sinistro2_Model_ProcessosArquivosPendencias::getDescricaoByTipo(Sinistro2_Model_ProcessosArquivosPendencias::TIPO_AUTORIZAÇÃO_FINAL) . "'"; $sql .= " WHEN nr_tipo = " . Sinistro2_Model_ProcessosArquivosPendencias::TIPO_AUTORIZAÇÃO_PRELIMINAR; $sql .= " THEN '" . Sinistro2_Model_ProcessosArquivosPendencias::getDescricaoByTipo(Sinistro2_Model_ProcessosArquivosPendencias::TIPO_AUTORIZAÇÃO_PRELIMINAR) . "'"; $sql .= " WHEN nr_tipo = " . Sinistro2_Model_ProcessosArquivosPendencias::TIPO_LAUDO_PARCIAL_FINAL; $sql .= " THEN '" . Sinistro2_Model_ProcessosArquivosPendencias::getDescricaoByTipo(Sinistro2_Model_ProcessosArquivosPendencias::TIPO_LAUDO_PARCIAL_FINAL) . "'"; $sql .= " WHEN nr_tipo = " . Sinistro2_Model_ProcessosArquivosPendencias::TIPO_LAUDO_FINAL; $sql .= " THEN '" . Sinistro2_Model_ProcessosArquivosPendencias::getDescricaoByTipo(Sinistro2_Model_ProcessosArquivosPendencias::TIPO_LAUDO_FINAL) . "'"; $sql .= " WHEN nr_tipo = " . Sinistro2_Model_ProcessosArquivosPendencias::TIPO_LAUDO_PRELIMINAR; $sql .= " THEN '" . Sinistro2_Model_ProcessosArquivosPendencias::getDescricaoByTipo(Sinistro2_Model_ProcessosArquivosPendencias::TIPO_LAUDO_PRELIMINAR) . "'"; $sql .= " ELSE 'TIPO DESCONHECIDO' "; $sql .= " END as tipo, "; $sql .= " TO_CHAR(pap.dt_criacao, 'dd/mm/yyyy HH24:mi:ss') as dt_envio, "; $sql .= " e.ds_nome_fantasia, "; $sql .= " s.ds_status "; $sql .= " FROM sinistro.processos_arquivos_pendencias pap "; $sql .= " JOIN sistema.empresas e ON e.id = pap.id_empresa "; $sql .= " JOIN sinistro.processos p ON p.id = pap.id_processo "; $sql .= " JOIN sistema.status s ON s.id = pap.id_status "; $sql .= " JOIN seguro.propostas pr ON pr.id = p.id_proposta "; $sql .= " JOIN produto.produtos pd ON pd.id = pr.id_produto "; if (!empty($filtros)) { $sql .= " WHERE " . implode(" AND ", $filtros); } $sql .= " ORDER BY pap.id_processo desc, pap.id"; return $this->fetchAll($sql); } public function vistoriasPrevias(array $filtros) : array { $campos = [ 'sp.id_proposta_mae as id_proposta', "to_char(spvp.dt_laudo, 'DD/MM/YYYY') as data_vistoria", 'spvp.id_vistoria', 'sf.ds_nome_funcionario as nome_perito', 'se.ds_nome_fantasia as nome_empresa', ]; $select = $this->select()->setIntegrityCheck(false); $select->from(['spvp' => 'seguro.propostas_vistorias_previas'], $campos); $select->join(['sp' => 'seguro.propostas'], 'sp.id = spvp.id_proposta', false); $select->join(['pp' => 'produto.produtos'], 'pp.id = sp.id_produto', false); $select->join(['sve' => 'sistema.vistorias_empresas'], 'sve.id_vistoria = spvp.id_vistoria', false); $select->join(['se' => 'sistema.empresas'], 'se.id = sve.id_empresa', false); $select->joinLeft(['svf' => 'sistema.vistorias_funcionarios'], 'svf.id_vistoria = spvp.id_vistoria', false); $select->joinLeft(['sf' => 'sistema.funcionarios'], 'sf.id = svf.id_funcionario', false); if (!empty($filtros['id_safra'])) { $select->where('pp.id_safra = ?', $filtros['id_safra']); } if (!empty($filtros['dataVistoria'])) { $select->where('spvp.dt_laudo = ?', Agro_Util::formatDate($filtros['dataVistoria'], Zend_Date::W3C)); } if (array_key_exists('id_produto', $filtros)) { $produtos = implode(', ', $filtros['id_produto']); $select->where("pp.id in ({$produtos})"); } if (array_key_exists('id_empresa', $filtros)) { $empresas = implode(', ', $filtros['id_empresa']); $select->where("se.id in ({$empresas})"); } if (array_key_exists('fl_nova_vistoria_custo_perito', $filtros)) { $select->where('spvp.fl_nova_vistoria_custo_perito is true'); } return $this->fetchAll($select); } /** * @param string $dataInicio * @param string $dataFim * @return mixed */ public function getVistoriaresNaoConferidosByData(string $dataInicio = '', string $dataFim = '') { $dataInicio = !empty($dataInicio) ? Agro_Util::formatDate($dataInicio, 'YYYY-MM-dd') : ''; $dataFim = !empty($dataFim) ? Agro_Util::formatDate($dataFim, 'YYYY-MM-dd') : ''; $sql = "SELECT sf.id "; $sql .= " ,sf.ds_nome_funcionario "; $sql .= " ,sf.cpf_cnpj "; $sql .= " ,to_char(sf.dt_nascimento, 'DD/MM/YYYY') as dt_nascimento "; $sql .= " ,se.ds_nome_fantasia as nome_empresa "; $sql .= " ,sd.ds_nome_departamento "; $sql .= " ,CASE WHEN sf.id_tipo_carteira_profissional = 1 THEN 'CREA' ELSE 'CFTA' END AS ds_tipo_carteira_profisional "; $sql .= " ,sf.ds_crea "; $sql .= " ,sr.ds_nome_role "; $sql .= " FROM sistema.funcionarios sf "; $sql .= " INNER JOIN sistema.usuarios su ON su.id = sf.id_usuario "; $sql .= " INNER JOIN sistema.empresas se ON se.id = sf.id_empresa "; $sql .= " INNER JOIN sistema.departamentos sd ON sd.id_empresa = se.id AND sd.id = sf.id_departamento "; $sql .= " INNER JOIN sistema.roles sr ON sr.id = su.id_role "; $sql .= " INNER JOIN (SELECT max(id) as id, svs.id_funcionario "; $sql .= " FROM sistema.vistoriadores_status svs "; $sql .= " GROUP BY id_funcionario) as svs_id ON svs_id.id_funcionario = sf.id "; $sql .= " INNER JOIN sistema.vistoriadores_status svs ON svs.id = svs_id.id "; $sql .= " INNER JOIN sistema.status ss ON ss.id = svs.id_status AND ss.ds_chave = 'NAO_CONFERIDO' "; $sql .= "WHERE su.tp_usuario = 'V' "; $sql .= " AND su.id_role in(" . Sistema_Model_Roles::ID_VISTORIADOR . ", " . Sistema_Model_Roles::ID_VISTORIADOR_RESP . ") "; if (!empty($dataInicio) && !empty($dataFim)) { $sql .= " AND to_char(svs.dt_criacao, 'YYYY-MM-DD') BETWEEN '{$dataInicio}' AND '{$dataFim}' "; } $sql .= " AND sf.fl_ativo is true "; $sql .= " AND svs.id_status <> " . Sistema_Model_VistoriadoresStatus::ID_STATUS_CONFERIDO; $sql .= "ORDER BY sf.ds_nome_funcionario ASC "; return $this->fetchAll($sql); } public function getVistoriadorComMultiplosRegistrosStatus() { $sql = "SELECT count(*), id_funcionario "; $sql .= " FROM sistema.vistoriadores_status "; $sql .= "GROUP BY id_funcionario "; $sql .= "HAVING count(*) > 1"; return $this->fetchAll($sql); } public function getInformacoesEmpresas(string $dataInicio, string $dataFim) { $dataInicio = Agro_Util::formatDate($dataInicio, 'YYYY-MM-dd'); $dataFim = Agro_Util::formatDate($dataFim, 'YYYY-MM-dd'); $campos = [ 'se.ds_razao_social as nome_empresa', "regexp_replace(se.cpf_cnpj, '[^0-9]*', '', 'g') AS cnpj_empresa", "CASE WHEN sf.id_tipo_carteira_profissional = 1 THEN 'CREA' ELSE 'CFTA' END as tipo_carteira", "sf.ds_crea as crea_cfta", "se.id_codigo_ibge as codigo_ibge", "CASE WHEN se.ds_telefone <> '' THEN regexp_replace(regexp_replace(se.ds_telefone, '[^0-9]*', '', 'g'), '(\d{2})', '\\1-') ELSE se.ds_telefone END AS telefone_empresa", "se.ds_email as email_empresa", 'sf.ds_nome_funcionario as nome_responsavel', "regexp_replace(sf.cpf_cnpj, '[^0-9]*', '', 'g') AS cpf_cnpj", "CASE WHEN sf.id_tipo_carteira_profissional = 1 THEN 'CREA' ELSE 'CFTA' END as tipo_carteira2", "sf.ds_crea as crea_cfta2", "CASE WHEN sf.fl_ativo is true THEN 1 ELSE 2 END AS status", ]; $select = $this->select()->setIntegrityCheck(false); $select->from(['se' => 'sistema.empresas'], $campos); $select->join(['sf' => 'sistema.funcionarios'], 'sf.id_empresa = se.id AND sf.fl_responsavel_empresa is true', false); $select->where("to_char(se.dt_criacao, 'YYYY-MM-DD') BETWEEN '{$dataInicio}' AND '{$dataFim}'"); $select->order('se.ds_razao_social'); return $this->fetchAll($select); } public function getInformacoesFuncionarios(string $dataInicio, string $dataFim) { $dataInicio = Agro_Util::formatDate($dataInicio, 'YYYY-MM-dd'); $dataFim = Agro_Util::formatDate($dataFim, 'YYYY-MM-dd'); $campos = [ 'distinct sf.id as id_funcionario', 'sf.ds_nome_funcionario as nome_funcionario', "regexp_replace(sf.cpf_cnpj, '[^0-9]*', '', 'g') AS cpf_cnpj", "CASE WHEN sf.id_tipo_carteira_profissional = 1 THEN 'CREA' ELSE 'CFTA' END as tipo_carteira", "sf.ds_crea as crea_cfta", "CASE WHEN sf.id_tipo_carteira_profissional = 1 THEN 1 ELSE 2 END as formacao", "se.id_codigo_ibge as codigo_ibge", "CASE WHEN se.ds_telefone <> '' THEN regexp_replace(regexp_replace(se.ds_telefone, '[^0-9]*', '', 'g'), '(\d{2})', '\\1-') ELSE se.ds_telefone END AS telefone_empresa", "se.ds_email as email_empresa", "sf.dt_nascimento as data_nascimento", new Zend_Db_Expr("'' as sexo"), "CASE WHEN sf.fl_ativo is true THEN 1 ELSE 2 END AS status", "regexp_replace(se.cpf_cnpj, '[^0-9]*', '', 'g') AS cnpj_empresa", ]; $subquery = "( WITH historico_funcionario AS ( SELECT id, id_funcionario, id_status, LAG(id_status) OVER (PARTITION BY id_funcionario ORDER BY dt_criacao) AS status_anterior, dt_criacao FROM sistema.vistoriadores_status ) SELECT id_funcionario FROM historico_funcionario hf WHERE to_char(dt_criacao, 'YYYY-MM-DD') >= '{$dataInicio}' AND to_char(dt_criacao, 'YYYY-MM-DD') <= '{$dataFim}' AND id_status = 278 AND status_anterior IS DISTINCT FROM 278 ) "; $select1 = $this->select()->setIntegrityCheck(false); $select1->from(['sf' => 'sistema.funcionarios'], $campos); $select1->join(['su' => 'sistema.usuarios'], 'su.id = sf.id_usuario', false); $select1->join(['se' => 'sistema.empresas'], 'se.id = sf.id_empresa', false); $select1->where('su.id_role in (' . implode(', ', [Sistema_Model_Roles::ID_VISTORIADOR, Sistema_Model_Roles::ID_VISTORIADOR_RESP]) . ')'); $select1->where("sf.ds_crea <> '' AND sf.ds_crea IS NOT NULL"); $select1->where("su.tp_usuario = 'V'"); $select2 = clone $select1; $select1->join(['svs' => $subquery], 'svs.id_funcionario = sf.id', false); $select1->where('sf.fl_ativo IS TRUE'); $select2->where('sf.fl_ativo IS FALSE'); $select2->where("to_char(sf.dt_alteracao, 'YYYY-MM-DD') >= '{$dataInicio}' AND to_char(sf.dt_alteracao, 'YYYY-MM-DD') <= '{$dataFim}'"); $select = $this->select()->setIntegrityCheck(false); $select->union([$select1, $select2])->order('nome_funcionario'); return $this->fetchAll($select); } public function digitacaoAmostrasPG($parametros) { $this->_db->query(" CREATE temp TABLE dados_laudos_itens_segurados AS ( ( SELECT lfis003.id_laudo_final_item_segurado, lfis003.fl_amostragem_maquina, lfisa003.vl_largura_plataforma, lfisa003.vl_distancia_percorrida, lfisa003.vl_area_colhida, lfisa003.vl_producao_colhida_amostra, lfisa003.vl_perda_colheita, lfisa003.vl_umidade_graos, lfisa003.vl_desconto_umidade_graos, lfisa003.vl_impurezas, lfis003.vl_graos_podres, lfis003.vl_graos_brotados, lfis003.vl_pragas_doencas_outros, lfisa003.vl_espacamento_entre_linhas, lfisa003.vl_metros_linhas_colhidos, lfisa003.vl_producao_liquida, lfisa003.nr_amostra FROM regulacao.lf003_itens_segurados lfis003 JOIN regulacao.lf003_itens_segurados_amostras lfisa003 ON lfisa003.id_laudo_final_item_segurado = lfis003.id_laudo_final_item_segurado ) UNION ( SELECT lfis034.id_laudo_final_item_segurado, lfis034.fl_amostragem_maquina, lfisa034.vl_largura_plataforma, lfisa034.vl_distancia_percorrida, lfisa034.vl_area_colhida, lfisa034.vl_producao_colhida_amostra, lfisa034.vl_perda_colheita, lfisa034.vl_umidade_graos, lfisa034.vl_desconto_umidade_graos, lfisa034.vl_impurezas, lfis034.vl_graos_podres, lfis034.vl_graos_brotados, lfis034.vl_pragas_doencas_outros, lfisa034.vl_espacamento_entre_linhas, lfisa034.vl_metros_linhas_colhidos, lfisa034.vl_producao_liquida, lfisa034.nr_amostra FROM regulacao.lf034_itens_segurados lfis034 JOIN regulacao.lf034_itens_segurados_amostras lfisa034 ON lfisa034.id_laudo_final_item_segurado = lfis034.id_laudo_final_item_segurado ) ); "); $select = $this->select()->setIntegrityCheck(false); $select->from(['pg' => 'produto.produtos_geral'], false); $select->join(['prod' => 'produto.produtos'], 'prod.id_produto_geral = pg.id', false); $select->join(['pc' => 'produto.produtos_coberturas'], 'pc.id_produto = prod.id', ['id_cobertura']); $select->join(['cob' => 'produto.coberturas'], 'cob.id = pc.id_cobertura', ['cob.ds_nome_cobertura']); $select->join(['tlf' => 'sinistro.tipo_laudo_final'], 'tlf.id = pc.id_tipo_laudo_final', false); $select->join(['pr' => 'seguro.propostas'], 'pr.id_produto = prod.id', ['id_proposta_mae']); $select->join(['proc' => 'sinistro.processos'], 'proc.id_proposta = pr.id', false); $select->join(['vp' => 'sinistro.vistorias_processos'], 'vp.id_processo = proc.id', ['vp.id_vistoria']); $select->join(['vc' => 'sistema.vistorias_coberturas'], 'vc.id_cobertura = cob.id AND vc.id_vistoria = vp.id_vistoria', false); $select->join(['va' => 'sistema.vistorias_agendamentos'], 'va.id_vistoria = vp.id_vistoria', false); $select->join(['a' => 'sistema.agendamentos'], 'a.id = va.id_agendamento', ["to_char(a.dt_agendamento, 'DD/MM/YYYY') as dt_agendamento"]); $select->join(['vlf' => 'sistema.vistorias_laudos_finais'], 'vlf.id_vistoria = vp.id_vistoria', false); $select->join(['lf' => 'sinistro.laudos_finais'], 'lf.id = vlf.id_laudo_final', ["to_char(lf.dt_vistoria, 'DD/MM/YYYY HH24:MI'::text) as dt_vistoria, CASE WHEN (lf.fl_transmitido_tablet IS TRUE) THEN 'Digital' WHEN (lf.fl_transmitido_tablet IS NULL) THEN '-' ELSE 'Manual' END AS fl_transmitido_tablet"]); $select->join(['slf' => 'sistema.status'], 'slf.id = lf.id_status', ['slf.ds_status']); $select->join(['ve' => 'sistema.vistorias_empresas'], 've.id_vistoria = vp.id_vistoria AND ve.fl_ativo is true', false); $select->join(['e' => 'sistema.empresas'], 'e.id = ve.id_empresa', ['ds_nome_fantasia']); $select->join(['vavisos' => 'sinistro.vistorias_avisos'], 'vavisos.id_vistoria = vp.id_vistoria', false); $select->join(['av' => 'sinistro.avisos'], 'av.id = vavisos.id_aviso', ["ARRAY_TO_STRING(ARRAY_AGG(av.id), ', ', '') as avisos"]); $select->join(['lfis' => 'sinistro.laudos_finais_itens_segurados'], 'lfis.id_laudo_final = lf.id', ['vl_rendimento_final_lavoura', 'ds_observacao', 'id_item_segurado']); $select->join(['dlis' => 'dados_laudos_itens_segurados'], 'dlis.id_laudo_final_item_segurado = lfis.id', ['fl_amostragem_maquina', 'vl_largura_plataforma', 'vl_distancia_percorrida', 'vl_area_colhida', 'vl_producao_colhida_amostra', 'vl_perda_colheita', 'vl_umidade_graos', 'vl_desconto_umidade_graos', 'vl_impurezas', 'vl_graos_podres', 'vl_graos_brotados', 'vl_pragas_doencas_outros', 'vl_espacamento_entre_linhas', 'vl_metros_linhas_colhidos', 'vl_producao_liquida', 'nr_amostra']); $select->where('pg.id_tipo_produto = 3'); $select->where('tlf.id_grupamento_cultura = 5'); if (!empty($parametros['id_safra'])) { $select->where("prod.id_safra in ({$parametros['id_safra']})"); } if (!empty($parametros['id_produto'])) { $idProdutos = implode(', ', $parametros['id_produto']); $select->where("prod.id in ({$idProdutos})"); } $select->group(['cob.ds_nome_cobertura', 'vp.id_vistoria', 'slf.ds_status', 'e.ds_nome_fantasia', 'a.dt_agendamento', 'lf.dt_vistoria', 'lfis.vl_rendimento_final_lavoura', 'dlis.fl_amostragem_maquina', 'dlis.vl_largura_plataforma', 'dlis.vl_distancia_percorrida', 'dlis.vl_area_colhida', 'dlis.vl_producao_colhida_amostra', 'dlis.vl_perda_colheita', 'dlis.vl_umidade_graos', 'dlis.vl_desconto_umidade_graos', 'dlis.vl_impurezas', 'dlis.vl_graos_podres', 'dlis.vl_graos_brotados', 'dlis.vl_pragas_doencas_outros', 'lfis.ds_observacao', 'dlis.vl_espacamento_entre_linhas', 'dlis.vl_metros_linhas_colhidos', 'dlis.vl_producao_liquida', 'dlis.nr_amostra', 'proc.id', 'lfis.id_item_segurado', 'pc.id_cobertura', 'pr.id_proposta_mae', 'lf.fl_transmitido_tablet' ]); $select->order('proc.id DESC, pc.id_cobertura, lfis.id_item_segurado, dlis.nr_amostra'); return $this->fetchAll($select); } public function getLaudosItensSeguradosBySafra(int $idSafra) { $sql = <<_db->fetchAll($sql); } /** * @param int $idSafra * @param array|null $produtos * @return array */ public function getLaudosItensSeguradosNovo(int $idSafra, ?array $produtos = []) : array { $whereLaudosItensSegurados = ["pp.id_safra = {$idSafra}", "its.vl_lmga > 0"]; if (!empty($produtos)) { $whereLaudosItensSegurados[] = "pp.id in (" . implode(', ', $produtos) . ")"; } $sqlLaudosItensSegurados = "SELECT lf.id , lfis.id as id_laudo_final_item_segurado , lfis.vl_percent_perdas , lfis.dt_vistoria , lfis.dt_criacao , lfis.vl_percent_colhido , lfis.vl_area_sinistrada , lfis.vl_percent_area_afetada , lfis.vl_rendimento_final_lavoura , lfis.vl_percent_perdas_chocho , lfis.vl_percent_colhido_chocho , lfis.id_motivo_quadra_nao_vistoriada , co.ds_nome_cobertura , CASE WHEN lf003.vl_graos_podres IS NOT NULL THEN lf003.vl_graos_podres ELSE lf034.vl_graos_podres END as vl_graos_podres , CASE WHEN lf003.vl_graos_brotados IS NOT NULL THEN lf003.vl_graos_brotados ELSE lf034.vl_graos_brotados END as vl_graos_brotados , CASE WHEN lf003.vl_pragas_doencas_outros IS NOT NULL THEN lf003.vl_pragas_doencas_outros ELSE lf034.vl_pragas_doencas_outros END as vl_pragas_doencas_outros , its.nr_item_segurado , pr.id_endosso , co.id as id_cobertura , lfis.vl_ha_area_replantada , ppc.fl_cobertura_principal as cobertura_principal FROM sinistro.laudos_finais lf INNER JOIN sinistro.laudos_finais_itens_segurados lfis ON lfis.id_laudo_final = lf.id INNER JOIN seguro.itens_segurados its ON its.id = lfis.id_item_segurado INNER JOIN produto.coberturas co ON co.id = lf.id_cobertura INNER JOIN seguro.propostas pr ON pr.id = its.id_proposta INNER JOIN produto.produtos pp ON pp.id = pr.id_produto INNER JOIN produto.produtos_coberturas ppc ON ppc.id_produto = pp.id AND ppc.id_cobertura = co.id LEFT JOIN regulacao.lf003_itens_segurados lf003 ON lf003.id_laudo_final_item_segurado = lfis.id LEFT JOIN regulacao.lf034_itens_segurados lf034 ON lf034.id_laudo_final_item_segurado = lfis.id WHERE " . implode(' AND ', $whereLaudosItensSegurados) . " ORDER BY lfis.dt_vistoria"; return Agro_Db_Table_Abstract::getDefaultAdapter()->fetchAll($sqlLaudosItensSegurados); } /** * @var string|array $cnpj */ public function relatorioIdentificacaoCredito( array $documentos, int $idSafra, array $idsStatus, ?DateTime $dataPagamentoInicio = null, ?DateTime $dataPagamentoFim = null, ?int $idUsuarioCorretor = null, bool $considerarSafraAnterior = false ): array { $stringIdsStatus = implode(', ', $idsStatus); $stringDataPagamentoInicio = !empty($dataPagamentoInicio) ? $dataPagamentoInicio->format('Y-m-d') : null; $stringDataPagamentoFim = !empty($dataPagamentoFim) ? $dataPagamentoFim->format('Y-m-d') : null; $usuarioLogado = Zend_Auth::getInstance()->getIdentity(); if ($idUsuarioCorretor == null) { $usuario = $usuarioLogado; } else { $usuario = (new Sistema_Model_Usuarios())->find($idUsuarioCorretor)->current(); if (empty($documentos) && $usuarioLogado->tp_usuario != 'F') { return []; } } $campoCorretor = 'ds_nome_corretor'; $whereCorretor = ''; if ($usuario->tp_usuario == 'C') { $campoCorretor = "CASE WHEN sc.id_usuario <> {$usuario->id} THEN '' ELSE ds_nome_corretor END AS ds_nome_corretor"; $whereCorretor = "AND spc.id_usuario = {$usuario->id}"; } $docsProponentes = ''; $docsBeneficiarios = ''; foreach ($documentos as $doc) { $docsProponentes .= empty($docsProponentes) ? "'{$doc}'" : ", '{$doc}'"; $docsBeneficiarios .= empty($docsBeneficiarios) ? "'{$doc}'" : ", '{$doc}'"; } if (!empty($docsProponentes) && !empty($docsBeneficiarios)) { $whereDocumento = " AND (spp.cpf_cnpj IN ($docsProponentes) OR spb.nr_cpf_cnpj IN ($docsBeneficiarios))"; } elseif (!empty($docsProponentes)) { $whereDocumento = " AND spp.cpf_cnpj IN ($docsProponentes)"; } elseif (!empty($docsBeneficiarios)) { $whereDocumento = " AND spb.nr_cpf_cnpj IN ($docsBeneficiarios)"; } $idsSafras = "{$idSafra}"; if ($considerarSafraAnterior) { $idsSafras += ', '. ($idSafra - 1); } $sql = ' SELECT sl.id AS id_liquidacao, spr.id_proposta_mae, spr.nr_apolice, pp.ds_nome_produto, spp.ds_nome_proponente, spp.cpf_cnpj AS cpf_cnpj_proponente, spb.ds_nome_beneficiarios AS ds_nome_beneficiario, spb.nr_cpf_cnpj AS cpf_cnpj_beneficiario, ss.ds_status, CASE WHEN ss.ds_chave = \'AGUARDANDO_PAGAMENTO\' THEN liq_coberturas.vl_indenizacao ELSE slpp.vl_pagamento END AS vl_indenizacao, '. $campoCorretor .', TO_CHAR(slpp.dt_confirmacao_pagamento, \'DD/MM/YYYY\') AS dt_pagamento, TO_CHAR(slpp.dt_pagamento, \'DD/MM/YYYY\') AS dt_agendamento_pagamento, COALESCE((xpath( format(\'/soap:Envelope/soap:Body/ns:EnviarPropostaSeguroAgricola/ns:propostaSeguroAgricola/ns:beneficiarios/ns:beneficiario[ns:detalhe/ns:%s/text()="%s"]/ns:percentualParticipacao/text()\', CASE WHEN length(spb.nr_cpf_cnpj) = 14 THEN \'cpf\' ELSE \'cnpj\' END, regexp_replace(spb.nr_cpf_cnpj::text, \'[^A-Za-z0-9]\', \'\', \'g\') ), ds_xml_transmissao::xml, ARRAY[ ARRAY[\'soap\', \'http://schemas.xmlsoap.org/soap/envelope/\'], ARRAY[\'ns\', \'http://www.essor.com.br/\'] ] ))[1]::text, 100::text) AS percentual_participacao_beneficiario FROM sinistro.liquidacoes sl INNER JOIN sinistro.processos sp ON sp.id = sl.id_processo INNER JOIN seguro.propostas spr ON spr.id = sp.id_proposta INNER JOIN seguro.propostas_endosso spe ON spe.id_proposta = spr.id AND spe.fl_proposta_vigente IS TRUE INNER JOIN produto.produtos pp ON pp.id = spr.id_produto LEFT JOIN seguro.propostas_proponentes spp ON spp.id_proposta = spr.id LEFT JOIN seguro.propostas_beneficiarios spb ON spb.id_proposta = spr.id AND spb.fl_del IS FALSE INNER JOIN seguro.propostas_corretores spc ON spc.id_proposta = spr.id INNER JOIN sistema.corretores sc ON sc.id_usuario = spc.id_usuario LEFT JOIN ( SELECT DISTINCT ON (id_liquidacao) id_liquidacao, dt_criacao FROM sinistro.liquidacoes_status WHERE fl_ativo IS TRUE ) liquidacoes_status ON liquidacoes_status.id_liquidacao = sl.id LEFT JOIN sinistro.liquidacoes_previsao_pagamento slpp ON slpp.id_liquidacao = sl.id LEFT JOIN ( SELECT DISTINCT ON (propostas_transmissao.id_proposta) propostas_transmissao.id_proposta, ds_xml_transmissao FROM log.propostas_transmissao INNER JOIN seguro.propostas ON propostas.id = propostas_transmissao.id_proposta INNER JOIN seguro.propostas_endosso ON propostas_endosso.id_proposta = propostas_transmissao.id_proposta AND fl_proposta_vigente IS TRUE INNER JOIN produto.produtos ON produtos.id = propostas.id_produto WHERE fl_sucesso IS TRUE AND ds_xml_transmissao <> \'\' AND ds_xml_transmissao IS NOT NULL AND ( ds_tipo_transmissao = \'EMITIR_PROPOSTA (Endosso)\' OR ds_tipo_transmissao = \'EMITIR_PROPOSTA\' ) ORDER BY propostas_transmissao.id_proposta, propostas_transmissao.dt_criacao DESC ) propostas_transmissao ON propostas_transmissao.id_proposta = spr.id LEFT JOIN ( SELECT id_liquidacao, SUM(vl_indenizacao) AS vl_indenizacao FROM sinistro.liquidacoes_itens_segurados_coberturas GROUP BY id_liquidacao ) liq_coberturas ON liq_coberturas.id_liquidacao = sl.id INNER JOIN sistema.status ss ON ss.id = sl.id_status WHERE pp.id_safra IN ('. $idsSafras .') AND sl.id_status IN ('. $stringIdsStatus .') AND sl.fl_ativo IS TRUE '. $whereDocumento .' '. $whereCorretor .' AND sc.fl_ativo IS TRUE AND sl.id_tipo_liquidacao <> 8 '; if (!empty($dataPagamentoInicio) && !empty($dataPagamentoFim)) { $sql .= " AND CASE WHEN ss.ds_chave = 'AGUARDANDO_PAGAMENTO' THEN slpp.dt_pagamento >= '{$stringDataPagamentoInicio}' AND slpp.dt_pagamento <= '{$stringDataPagamentoFim}' WHEN ss.ds_chave = 'SEM_INDENIZACAO' THEN liquidacoes_status.dt_criacao::date >= '{$stringDataPagamentoInicio}' AND liquidacoes_status.dt_criacao::date <= '{$stringDataPagamentoFim}' ELSE slpp.dt_confirmacao_pagamento >= '{$stringDataPagamentoInicio}' AND slpp.dt_confirmacao_pagamento <= '{$stringDataPagamentoFim}' END "; } elseif (!empty($dataPagamentoInicio)) { $sql .= " AND CASE WHEN ss.ds_chave = 'AGUARDANDO_PAGAMENTO' THEN slpp.dt_pagamento >= '{$stringDataPagamentoInicio}' WHEN ss.ds_chave = 'SEM_INDENIZACAO' THEN liquidacoes_status.dt_criacao::date >= '{$stringDataPagamentoInicio}' ELSE slpp.dt_confirmacao_pagamento >= '{$stringDataPagamentoInicio}' END "; } elseif (!empty($dataPagamentoFim)) { $sql .= " AND CASE WHEN ss.ds_chave = 'AGUARDANDO_PAGAMENTO' THEN slpp.dt_pagamento <= '{$stringDataPagamentoFim}' WHEN ss.ds_chave = 'SEM_INDENIZACAO' THEN liquidacoes_status.dt_criacao::date <= '{$stringDataPagamentoFim}' ELSE slpp.dt_confirmacao_pagamento <= '{$stringDataPagamentoFim}' END "; } $sql .= 'ORDER BY spr.id'; return $this->_db->fetchAll($sql); } }