<?php
class Relatorio_Model_Comercial extends Agro_Db_Table_Relatorios {

	protected $_schema = 'seguro';
	protected $_name = 'propostas';

	public function getCoberturasAdicionais($id_proposta){
		$sql = "select id_cobertura from seguro.propostas_coberturas where fl_principal=false and id_proposta=".$id_proposta;
		return $this->_db->fetchAll($sql);
	}

	public function getParcelasPropostas($id_proposta){
		$sql = "select vl_total, vl_subvencao_federal, vl_subvencao_estadual, to_char(dt_vencimento,'DD/MM/YYYY') as dt_vencimento from seguro.propostas_parcelas where id_proposta=".$id_proposta." order by nr_parcela asc";
		return $this->_db->fetchAll($sql);
	}

	public function getPrepostoProposta($id_usuario){
		$sql = "select ds_nome_preposto from sistema.prepostos where id_usuario=".$id_usuario;
		return $this->_db->fetchOne($sql);
	}

	public function getComercialCorretor($id_usuario){
		$sql = "select id_corretor from sistema.corretores_comercial where id_comercial=".$id_usuario;
		return $this->_db->fetchAll($sql);
	}

    public function relatorioVendasEstado($id_safra="", $arProduto="", $arEstado="", $arCorretor="", $dt_inicio="", $dt_fim="", $fl_exibir_preposto=null, $arMunicipio=array(), $showMunicipios=false)
    {
        $this->_db->query("CREATE temp TABLE itens_segurados_temp AS (SELECT id_proposta,sum(vl_lmga) AS soma_lmga, sum(vl_area) AS soma_area FROM seguro.itens_segurados GROUP BY id_proposta)");
        $this->_db->query("CREATE temp TABLE parcelas_temp AS (SELECT id_proposta,sum(vl_total) AS valor_total, sum(vl_segurado) AS valor_segurado, sum(vl_subvencao_federal) AS valor_subvencao_federal, sum(vl_subvencao_estadual) AS valor_subvencao_estadual FROM seguro.propostas_parcelas GROUP BY id_proposta)");
        $this->_db->query("SELECT seguro.sumario_parcelas(0);");

        $filtro = [
            ($id_safra) ? "pd.id_safra = {$id_safra}" : ''
        ];

        $selectProduto = $selectProdutoSubQuery = $selectCorretor = $selectCorretorSubQuery = "";

        if (is_array($arProduto)) {
            $filtro[] = "pd.id in (" . implode(",", $arProduto) . ")";
            $selectProdutoSubQuery = ", pd.ds_nome_produto";
            $selectProduto = ", ds_nome_produto";
        }

        $this->_db->query("
					CREATE temp TABLE tmp_parcela_restituicao AS (
						SELECT
						    coalesce(sum(vl_segurado),0.0) as vl_restituido,
						    pe.id_endosso
						FROM
						    seguro.propostas pr
						    inner join seguro.propostas_endosso pe on pe.id_proposta = pr.id
						    inner join produto.produtos pd ON pd.id = pr.id_produto
						    inner join seguro.propostas_parcelas pp on pp.id_proposta = pr.id
						    inner join sistema.status st on st.id = pr.id_status and st.ds_chave not in ('PROPOSTA_INCOMPLETA' , 'NAO_ENVIADA' , 'DEVOLVIDA' , 'PROPOSTA_CANCELADA' , 'ORCAMENTO_ENDOSSO', 'ENDOSSO_INCOMPLETO', 'ENDOSSO_ANULADO', 'SOLICITACAO_CANCELADA')
						WHERE " . implode(' AND ', $filtro) . "
						  AND pp.vl_segurado < 0
						GROUP BY pe.id_endosso
					)
				");

        $this->_db->query("
					CREATE temp TABLE tmp_parcela_cobranca AS (
						SELECT
						    sum(vl_segurado) as vl_pago,
						    pe.id_endosso
						FROM
						    seguro.propostas pr
						    inner join seguro.propostas_endosso pe on pe.id_proposta = pr.id
						    inner join produto.produtos pd ON pd.id = pr.id_produto
						    inner join seguro.propostas_parcelas pp on pp.id_proposta = pr.id
						    inner join sistema.status st on st.id = pr.id_status and st.ds_chave not in ('PROPOSTA_INCOMPLETA' , 'NAO_ENVIADA' , 'DEVOLVIDA' , 'PROPOSTA_CANCELADA' , 'ORCAMENTO_ENDOSSO', 'ENDOSSO_INCOMPLETO', 'ENDOSSO_ANULADO', 'SOLICITACAO_CANCELADA')
						WHERE " . implode(' AND ', $filtro) . "
						  AND pp.vl_segurado > 0 and pp.fl_pago = true
						GROUP BY pe.id_endosso
					)
				");

        if (is_array($arCorretor)) {
            $filtro[] = "pc.id_usuario in (" . implode(",", $arCorretor) . ")";
            $selectCorretorSubQuery = ",c.ds_nome_abreviado";
            $selectCorretor = ", ds_nome_abreviado";
        } elseif (Zend_Auth::getInstance()->getIdentity()->ds_chave == 'COMERCIAL') {
            $selectCorretorSubQuery = ", c.ds_nome_abreviado";
            $selectCorretor = ", ds_nome_abreviado";
        }

        if (is_array($arEstado)) {
            $filtro[] = "e.id in (" . implode(",", $arEstado) . ")";
        }

        if (is_array($arMunicipio) && !empty($arMunicipio)) {
            $filtro[] = "m.id in (" . implode(",", $arMunicipio) . ")";
        }

        if ($fl_exibir_preposto && is_array($arCorretor)) {
            $selectPrepostoSubQuery = ", case when u.tp_usuario = 'P' then po.ds_nome_preposto else '-' end as ds_preposto";
            $selectPreposto = ", ds_preposto";
            $groupPreposto = ", ds_preposto";
        }

        $showMunicipiosFields = $showMunicipiosFieldsSubQuery = $groupShowMunicipios = "";
        if ($showMunicipios) {
            $showMunicipiosFields = ", ds_nome_municipio";
            $showMunicipiosFieldsSubQuery = ", m.ds_nome_municipio";
            $groupShowMunicipios = ", ds_nome_municipio";
        }

        if (!empty($dt_inicio)) {
            $filtro[] = "pr.dt_transmissao >= '" . Agro_Util::formatDate($dt_inicio, 'yyyy-MM-dd') . "'";
        }

        if (!empty($dt_fim)) {
            $filtro[] = "pr.dt_transmissao <= '" . Agro_Util::formatDate($dt_fim, 'yyyy-MM-dd') . "'";
        }

        $sql = "SELECT ds_sigla
                     {$selectProduto}
				     {$selectCorretor}
				     {$selectPreposto}
				     {$showMunicipiosFields}
                     , count(id)                     as count_propostas
                     , sum(soma_lmga)                AS soma_lmga
                     , sum(soma_area)                AS soma_area
                     , sum(valor_total)              AS soma_valor_total
                     , sum(valor_segurado)           AS soma_valor_segurado
                     , sum(valor_subvencao_federal)  AS soma_valor_sub_federal
                     , sum(valor_subvencao_estadual) AS soma_valor_sub_estadual
                     , sum(vl_custo_apolice)         as soma_custo_apolice
                     , sum(vl_retido)                as soma_valor_retido
                     , sum(proposta_valida_somatorio)   as soma_proposta_valida_somatorio
                FROM (SELECT e.ds_sigla
                           {$selectProdutoSubQuery}
                           {$selectCorretorSubQuery}
                           {$selectPrepostoSubQuery}
                           {$showMunicipiosFieldsSubQuery}
                           , pr.id
                           , CASE WHEN st.ds_chave <> 'APOLICE_CANCELADA' OR (st.ds_chave = 'APOLICE_CANCELADA' AND (coalesce(tmp_cobranca.vl_pago, 0) + coalesce(tmp_restituicao.vl_restituido, 0)) > 0) THEN its.soma_lmga ELSE 0 end as soma_lmga
                           , CASE WHEN st.ds_chave <> 'APOLICE_CANCELADA' OR (st.ds_chave = 'APOLICE_CANCELADA' AND (coalesce(tmp_cobranca.vl_pago, 0) + coalesce(tmp_restituicao.vl_restituido, 0)) > 0) THEN its.soma_area ELSE 0 end as soma_area
                           , CASE WHEN st.ds_chave = 'APOLICE_CANCELADA' THEN 0 ELSE pat.valor_total end as valor_total
                           , CASE WHEN st.ds_chave = 'APOLICE_CANCELADA' THEN 0 ELSE pat.valor_segurado end as valor_segurado
                           , CASE WHEN st.ds_chave = 'APOLICE_CANCELADA' THEN 0 ELSE pat.valor_subvencao_federal END AS valor_subvencao_federal
                           , CASE WHEN st.ds_chave = 'APOLICE_CANCELADA' THEN 0 ELSE pat.valor_subvencao_estadual END AS valor_subvencao_estadual
                           , CASE WHEN st.ds_chave = 'APOLICE_CANCELADA' THEN 0 ELSE pr.vl_custo_apolice END AS vl_custo_apolice
                           , CASE WHEN st.ds_chave = 'APOLICE_CANCELADA' THEN coalesce(tmp_cobranca.vl_pago, 0) + coalesce(tmp_restituicao.vl_restituido, 0) ELSE 0 end as vl_retido
                           , CASE WHEN st.ds_chave <> 'APOLICE_CANCELADA' OR (st.ds_chave = 'APOLICE_CANCELADA' AND (coalesce(tmp_cobranca.vl_pago, 0) + coalesce(tmp_restituicao.vl_restituido, 0)) > 0) THEN 1 ELSE 0 end as proposta_valida_somatorio
                      FROM seguro.propostas pr
                           INNER JOIN itens_segurados_temp its ON its.id_proposta = pr.id
                           INNER JOIN sumario_parcelas pat ON pat.id_endosso = pr.id_endosso
                           INNER JOIN seguro.propostas_endosso pe ON pe.id_proposta = pr.id AND pe.fl_proposta_vigente = true
                           INNER JOIN produto.produtos pd ON pd.id = pr.id_produto
                           INNER JOIN seguro.propostas_propriedades pp ON pp.id_proposta = pr.id
                           INNER JOIN sistema.municipios m ON m.id = pp.id_municipio
                           INNER JOIN seguro.propostas_corretores pc ON pc.id_proposta = pr.id
                           INNER JOIN sistema.estados e ON e.id = m.id_estado
                           INNER JOIN sistema.status st ON st.id = pr.id_status
                           INNER JOIN sistema.corretores c ON c.id_usuario = pc.id_usuario
                           INNER JOIN sistema.usuarios u ON u.id = pr.id_usuario_criacao
                           LEFT JOIN sistema.prepostos po ON po.id_usuario = u.id
                           LEFT JOIN tmp_parcela_restituicao as tmp_restituicao ON tmp_restituicao.id_endosso = pr.id_endosso
				           LEFT JOIN tmp_parcela_cobranca as tmp_cobranca ON tmp_cobranca.id_endosso = pr.id_endosso
                      where st.ds_chave in(
                            'APOLICE_EMITIDA',
                            'ENVIADA_AGUARDANDO_CONF',
                            'CONF_PENDENCIAS',
                            'APOLICE_CANCELADA',
                            'AGUARDANDO_ENVIO_SEG')
                        and " . implode(' AND ', $filtro) . "
                        and pc.id_usuario <> 487) AS result
                group by ds_sigla
                       {$selectProduto}
                       {$selectCorretor}
                       {$groupPreposto}
                       {$groupShowMunicipios}
                order by ds_sigla
                       {$selectCorretor}
                       {$selectProduto}
                       {$groupPreposto}
                       {$groupShowMunicipios}";

        return $this->fetchAll($sql);
    }
	
	public function relatorioParcelasSintetico($id_safra="",$arProduto=""){
	
	    $filtro  = ($id_safra)   ? "pd.id_safra=".$id_safra." and " : '';
	    if(is_array($arProduto)){
	        $filtro .= "pd.id in (".implode("," , $arProduto).") and ";
	    }
	    
	    $this->_db->query("CREATE temp TABLE parcelas_1 AS
                            (
                            select
                                (sum(pp.vl_segurado) - sum(pr.vl_custo_apolice)) as vl_segurado,
                                sum(pp.vl_subvencao_federal) as vl_subvencao_federal,
                                sum(pp.vl_subvencao_estadual) as vl_subvencao_estadual,
                                sum(pp.vl_total) as vl_total,
                                DATE_PART('YEAR', pp.dt_vencimento) as ano,
                                DATE_PART('MONTH', pp.dt_vencimento) as mes
                            from
                                seguro.propostas_parcelas pp,
                                seguro.propostas pr,
                                produto.produtos pd,
                                sistema.status st
                            where
                                    ".$filtro."
                            	pr.id = pp.id_proposta
                                and pd.id = pr.id_produto
                                and st.id = pr.id_status
                                and st.ds_chave not in ('PROPOSTA_INCOMPLETA' , 'NAO_ENVIADA' , 'DEVOLVIDA' , 'PROPOSTA_CANCELADA' , 'APOLICE_CANCELADA', 'ORCAMENTO_ENDOSSO', 'ENDOSSO_INCOMPLETO', 'SOLICITACAO_CANCELADA')
                                and pp.nr_parcela = 1
                            group by
                                DATE_PART('MONTH', pp.dt_vencimento),DATE_PART('YEAR', pp.dt_vencimento)
                            order by
                                DATE_PART('YEAR', pp.dt_vencimento), DATE_PART('MONTH', pp.dt_vencimento)
                            )");
	    
	    $this->_db->query("CREATE temp TABLE parcelas_2 AS
                            (
                            select
                                sum(pp.vl_segurado) as vl_segurado,
                                sum(pp.vl_subvencao_federal) as vl_subvencao_federal,
                                sum(pp.vl_subvencao_estadual) as vl_subvencao_estadual,
                                sum(pp.vl_total) as vl_total,
                                DATE_PART('YEAR', pp.dt_vencimento) as ano,
                                DATE_PART('MONTH', pp.dt_vencimento) as mes
                            from
                                seguro.propostas_parcelas pp,
                                seguro.propostas pr,
                                produto.produtos pd,
                                sistema.status st
                            where
                                    ".$filtro."
                            	pr.id = pp.id_proposta
                                and pd.id = pr.id_produto
                                and st.id = pr.id_status
                                and st.ds_chave not in ('PROPOSTA_INCOMPLETA' , 'NAO_ENVIADA' , 'DEVOLVIDA' , 'PROPOSTA_CANCELADA' , 'APOLICE_CANCELADA', 'ORCAMENTO_ENDOSSO', 'ENDOSSO_INCOMPLETO', 'SOLICITACAO_CANCELADA')
                                and pp.nr_parcela > 1
                            group by
                                DATE_PART('MONTH', pp.dt_vencimento),DATE_PART('YEAR', pp.dt_vencimento)
                            order by
                                DATE_PART('YEAR', pp.dt_vencimento), DATE_PART('MONTH', pp.dt_vencimento)
                            ) ");
	
	    $sql = "select 
                    sum(soma.vl_segurado) as vl_segurado,
                    sum(soma.vl_subvencao_federal) as vl_subvencao_federal,
                    sum(soma.vl_subvencao_estadual) as vl_subvencao_estadual, 
                    soma.ano,
                    soma.mes
                from 
                   (
                       (
                           select sum(vl_segurado) as vl_segurado, sum(vl_subvencao_federal) as vl_subvencao_federal , sum(vl_subvencao_estadual) as vl_subvencao_estadual, ano, mes from parcelas_1 group by ano,mes order by ano,mes
                       )
                       union 
                       (   
                           select sum(vl_segurado) as vl_segurado, sum(vl_subvencao_federal) as vl_subvencao_federal , sum(vl_subvencao_estadual) as vl_subvencao_estadual, ano, mes from parcelas_2 group by ano,mes order by ano,mes
                       ) 
                       order by ano, mes
                    ) as soma
                group by
                    soma.ano, soma.mes    
                order by
                    soma.ano, soma.mes    
				";
	    return $this->fetchAll($sql);
	}
	
}