<?php

class Agro_ReportsTecnica extends Agro_Reports
{
	const ID_TOMATE_INDUSTRIA = 41;
	const GRAOS = 2;
	const MULTIRRISCO = 3;

	private function getCoberturasAdicionais($id_proposta)
	{
		$sql =
			"select pc.id_cobertura, pc.vl_taxa from seguro.propostas_coberturas pc
                INNER JOIN
                (
                   select id_cobertura from produto.produtos_coberturas pco, seguro.propostas pr
                   where pco.id_produto = pr.id_produto and pr.id=" .
			$id_proposta .
			" and pco.fl_contratacao_automatica=false and pco.fl_cobertura_principal=false
                ) as pco ON pc.id_cobertura = pco.id_cobertura and pc.id_proposta=" .
			$id_proposta .
			" and pc.fl_del=false order by pc.id_cobertura";
		return $this->getDb()->fetchAll($sql);
	}

	private function relatorioVendas(int $idSafra)
	{
		$this->setDb();
		return $this->getDb()->fetchAll(
			"select * from sumario.relatorio_vendas where id_safra = {$idSafra}"
		);
	}

	public function vendasSinistroItemCobertura($descricaoSafra, $codigoCultura) {
		$this->setDb();
		$descricaoSafra = str_replace('\'', '', $descricaoSafra);
		
		$idSafra = $this->getDb()->fetchOne("SELECT id FROM produto.safras WHERE ds_nome_safra = '{$descricaoSafra}';");
		if (empty($idSafra)) {
			throw new InvalidArgumentException('Safra inválida.');
		}

		if (!filter_var($codigoCultura, FILTER_VALIDATE_INT)) {
			throw new InvalidArgumentException('Código da cultura deve ser um inteiro.');
		}

		$idCultura = null;
		if (!empty($codigoCultura)) {
			$idCultura = $this->getDb()->fetchOne("SELECT id FROM produto.culturas WHERE id = '{$codigoCultura}';");

			if (empty($idCultura)) {
				throw new Exception('Cultura inválida.');
			}

			$filtroCultura = "AND mv1.id_cultura = {$idCultura}";
		}
		
		$sql = "
            SELECT
				proposta,
				item,
				cobertura,
				status,
				ibge,
				corretor,
				sublogin,
				produto,
				cultura,
				variedade,
				data_transmissao,
				safra,
				area,
				franquia,
				lmga_cobertura,
				vl_is,
				premio,
				produtividade_esperada,
				produtividade_garantida,
				produtividade_obtida,
				status_apolice,
				cobertura_sinistrada,
				evento,
				data_sinistro,
				data_aviso_sinistro,
				data_vistoria_final,
				empresa_vistoria,
				vistoriador,
				status_indenizacao,
				prejuizo,
				indenizacao,
				latitude_centroide,
				longitude_centroide,
				poligono.geom AS poligono
			FROM seguro.mv_vendas_sinistros_item_cobertura mv1
			LEFT JOIN (
				SELECT
					id,
					-- ST_GeomFromText(
					--     'POLYGON((' || string_agg(lon || ' ' || lat, ', ' ORDER BY ordem) || '))',
					--     4326
					-- ) AS geom
					'POLYGON((' || string_agg(LEFT(lon, 10) || ' ' || LEFT(lat,10), ', ' ORDER BY ordem) || '))' as geom
					FROM seguro.mv_poligono_fechado
				GROUP BY id
			) poligono ON poligono.id = mv1.id_croqui_talhao
			WHERE
				mv1.id_safra = {$idSafra}
				{$filtroCultura}
			ORDER BY proposta DESC, item
        ";
		
		try {
			return $this->getDb()->fetchAll($sql);
		} catch (Exception $e) {
			$message = $_SERVER['APPLICATION_ENV'] != 'production' ? $e->getMessage() : 'Erro na consulta.';

			return [
				'error' => $message
			];
		}
    }

	public function vendas(string $idSafra) : array {
		$Rs = $this->relatorioVendas($idSafra);
		$arr = [];
		$count = 0;

		foreach ($Rs as $Row) {
			$tp_proposta = $Row["id_proposta_renovada"] > 0 ? "R" : "P";

			$ds_classe = "NO";
			if (@$Row["ds_classe"]) {
				$ds_classe = $Row["ds_classe"];
			}

			$vl_premio_liquido = $Row["valor_total"] - $Row["vl_custo_apolice"];
			$vl_premio_tarifario = $vl_premio_liquido;

			$ds_coordenadas = preg_replace("/\s/", " ", $Row["ds_coordenadas"]);
			$ds_coordenadas = str_replace(";", " - ", $ds_coordenadas);

			$arr[$count]["Proposta do Endosso"] = $Row["id_proposta"];
			//$arr[$Row['id_proposta']]['id_versao']                      = $Row['id_versao'];
			$arr[$count]["Seção"] = $Row["id_secao"];
			$arr[$count]["Endosso"] = $Row["nr_endosso"];
			$arr[$count]["Proposta"] = $Row["id_proposta_mae"];
			//$arr[$Row['id_proposta']]['id_proposta_mae_completo'] = $Row['id_proposta_mae_completo'];
			$arr[$count]["Proposta Completo"] = $Row["id_proposta_composto"];
			$arr[$count]["Tipo Proposta"] = $tp_proposta;
			$arr[$count]["Nro Apólice"] = $Row["nr_apolice"];
			$arr[$count]["ID Segurado"] = $Row["id_proponente"];
			$arr[$count]["Grupo"] = $Row["tipo_produto"];
			$arr[$count]["Preposto"] = $Row["ds_nome_preposto"];
			$arr[$count]["Franquia"] = $Row["valor_franquia"];
			$arr[$count]["Status"] = $Row["ds_status"];
			$arr[$count]["Município"] = $Row["municipio_propriedade"];
			$arr[$count]["Cod. IBGE"] = $Row["nr_ibge"];
			$arr[$count]["UF"] = $Row["estado_propriedade"];
			$arr[$count]["Corretor"] = $Row["ds_nome_fantasia"];
			$arr[$count]["Comissão corretor"] = $Row["vl_comissao"];
			$arr[$count]["Produto"] = $Row["ds_nome_produto"];
			$arr[$count]["Cultura"] = $Row["ds_nome_cultura"];
			$arr[$count]["Área"] = $Row["total_area"];
			$arr[$count]["LMGA"] = $Row["total_lmga"];
			$arr[$count]["Prêmio Tarifario"] = $Row["premio_principal"];
			$arr[$count]["Prêmio adicional"] = $Row["premio_adicional"];
			$arr[$count]["Prêmio Total"] = $Row["valor_total"];
			$arr[$count]["Prêmio Líquido"] = (string) $vl_premio_liquido;
			$arr[$count]["Prêmio Segurado"] = $Row["valor_segurado"];
			$arr[$count]["Prêmio Ganho do Segurado"] = $Row["premio_pago_segurado"];
			$arr[$count]["Custo de Apólice"] = $Row["vl_custo_apolice"];
			$arr[$count]["Subsídio Federal"] = $Row["valor_subvencao_federal"];
			$arr[$count]["Subsídio Estadual"] = $Row["valor_subvencao_estadual"];
			$arr[$count]["Início Vigência Original"] =
				$Row["dt_vigencia_inicio_original"];
			$arr[$count]["Início Vigência"] = $Row["dt_vigencia_inicio"];
			$arr[$count]["Final Vigência"] = $Row["dt_vigencia_fim"];
			$arr[$count]["Dt. Transmissão"] = $Row["dt_transmissao"];
			$arr[$count]["Coordenada"] = $ds_coordenadas;
			$arr[$count]["ST"] = $ds_classe;
			$arr[$count]["Nro. Boleto"] = $Row["nu_boleto"];
			$arr[$count]["Proposta Renovada"] = $Row["id_proposta_renovada"];
			//$arr[$Row['id_proposta']]['fl_pago']                  = $Row['fl_pago'];
			$arr[$count]["Vencimento 1ª Parcela"] = $Row["vencto_primeira_parcela"];
			$arr[$count]["Valor 1ª Parcela"] = $Row["valor_primeira_parcela"];
			$arr[$count]["Pronamp"] = $Row["ds_nome_safra"];
			$arr[$count]["Orgânico"] = $Row["ds_nome_contrato"];
			$arr[$count]["Sub. Fed. Concedida (endosso 0)"] =
				$Row["subvencao_concedida"];
			$arr[$count]["Status Boleto"] = $Row["fl_boleto_pago"];
			$arr[$count]["Sub. Est. Concedida (endosso 0)"] =
				$Row["quest_sub_estadual"];

			$Rs_cob = $this->getCoberturasAdicionais($Row["id_proposta"]);
			$i = 0;
			foreach ($Rs_cob as $row2) {
				$arr[$count]["Cob. Adicional " . $i] = $row2["id_cobertura"];
				$i++;
			}

			$count++;
		}

		return $arr;
	}

	public function propostasCanceladas(string $idSafra)
	{
		$Rs = $this->relatorioPropostasCanceladas($idSafra);
		$arr = [];
		$oldProposta = 0;
		$count = -1;

		foreach ($Rs as $Row) {
			if ($oldProposta != $Row["id_proposta"]) {
				$count++;
			}

			if ($Row["tp_usuario"] == "P") {
				$ds_nome_preposto = $this->getPrepostoProposta(
					$Row["id_usuario_criacao"],
				);
			} else {
				$ds_nome_preposto = "";
			}

			$vl_premio_liquido = $Row["valor_total"] - $Row["vl_custo_apolice"];
			//$vl_premio_tarifario = $vl_premio_liquido;

			$ds_coordenadas = preg_replace("/\s/", " ", $Row["ds_coordenadas"]);
			$ds_coordenadas = str_replace(";", " - ", $ds_coordenadas);

			$ds_observacao = str_replace("\n", "", $Row["ds_observacao"]);
			$ds_observacao = str_replace("\r", "", $ds_observacao);

			$arr[$count]["Proposta"] = $Row["id_proposta_mae"];
			$arr[$count]["Endosso"] = $Row["nr_endosso"];
			$arr[$count]["Proposta Completo"] = $Row["id_proposta_mae_completo"];
			$arr[$count]["Tipo Proposta"] =
				$Row["id_proposta_renovada"] > 0 ? "R" : "P";
			$arr[$count]["Nro Apólice"] = $Row["nr_apolice"];
			$arr[$count]["ID Segurado"] = $Row["id_proponente"];
			$arr[$count]["Grupo"] = $Row["tipo_produto"];
			$arr[$count]["Sublogin"] = $ds_nome_preposto;
			$arr[$count]["Franquia"] = $Row["valor_franquia"];
			$arr[$count]["Status"] = $Row["ds_status"];
			$arr[$count]["Município"] = $Row["municipio_propriedade"];
			$arr[$count]["Cod. IBGE"] = $Row["nr_ibge"];
			$arr[$count]["UF"] = $Row["estado_propriedade"];
			$arr[$count]["Corretor"] = $Row["ds_nome_fantasia"];
			$arr[$count]["Comissão corretor"] = $Row["vl_comissao"];
			$arr[$count]["Produto"] = $Row["ds_nome_produto"];
			$arr[$count]["Cultura"] = $Row["ds_nome_cultura"];
			$arr[$count]["Área"] = $Row["total_area"];
			$arr[$count]["LMGA"] = $Row["total_lmga"];
			$arr[$count]["Prêmio Tarifario"] = $Row["premio_principal"];
			$arr[$count]["Prêmio adicional"] = $Row["premio_adicional"];
			$arr[$count]["Prêmio Total"] = $Row["valor_total"];
			$arr[$count]["Prêmio Líquido"] = (string) $vl_premio_liquido;
			$arr[$count]["Prêmio Segurado"] = $Row["valor_segurado"];
			$arr[$count]["Custo de Apólice"] = $Row["vl_custo_apolice"];
			$arr[$count]["Subsídio Federal"] = $Row["valor_subvencao_federal"];
			$arr[$count]["Subvenção Concedida?"] = $Row["subvencao_concedida"];
			$arr[$count]["Subsídio Estadual"] = $Row["valor_subvencao_estadual"];
			$arr[$count]["Início Vigência"] = $Row["dt_vigencia_inicio"];
			$arr[$count]["Final Vigência"] = $Row["dt_vigencia_fim"];
			$arr[$count]["Valor 1ª Parcela"] = $Row["valor_primeira_parcela"];
			$arr[$count]["Vencimento 1ª Parcela"] = $Row["vencto_primeira_parcela"];
			$arr[$count]["Nro. Boleto"] = $Row["nu_boleto"];
			$arr[$count]["Seção"] = $Row["id_secao"];
			$arr[$count]["Coordenada"] = $ds_coordenadas;
			$arr[$count]["ST"] = "NO";
			$arr[$count]["Proposta Renovada"] = $Row["id_proposta_renovada"];
			$arr[$count]["Retido"] = $Row["vl_retido"] > 0 ? $Row["vl_retido"] : "0";
			$arr[$count]["Contrato de Resseguro"] = $Row["contrato_resseguro"];

			if ($Row["motivo_cancelamento"]) {
				$arr[$count]["Motivo Cancelamento"] = $Row["motivo_cancelamento"];
			} else {
				$arr[$count]["Motivo Cancelamento"] = $ds_observacao;
			}

			$arr[$count]["Data Cancelamento"] = $this->formatDate(
				$Row["dt_cancelamento"],
				"dd/MM/Y H:m",
			);

			$Rs_cob = $this->getCoberturasAdicionais($Row["id_proposta"]);
			$i = 0;
			foreach ($Rs_cob as $row2) {
				$arr[$count]["Cob. Adicional " . $i] = $row2["id_cobertura"];
				$i++;
			}

			$oldProposta = $Row["id_proposta"];
		}

		return $arr;
	}

	public function relatorioPropostasCanceladas(int $idSafra)
	{
		$this->setDb();
		$this->getDb()->query("SELECT seguro.sumario_parcelas_canceladas(0);");

		return $this->getDb()->fetchAll(
			"select * from sumario.relatorio_propostas_canceladas where id_safra = {$idSafra}",
		);
	}

	public function getPrepostoProposta(int $idUsuario)
	{
		$sql =
			"select ds_nome_preposto from sistema.prepostos where id_usuario=" .
			$idUsuario;
		return $this->getDb()->fetchOne($sql);
	}

	function formatDate($dt, $format = "")
	{
		if (!$format) {
			$format = Zend_Date::DATES;
		}

		if ($dt) {
			$Date = new Zend_Date(1234567890, false, "pt_BR");
			$Date->set($dt);

			return $Date->toString($format);
		} else {
			return null;
		}
	}

	/**
	 * Retorna os dados bancários das devoluções com o status 'EM_PROCESSAMENTO_DADOS_BANCARIOS'
	 * e altera o status para ''
	 *
	 * @return array
	 */
	public function informacoesBancarias($dtAtualizacao = null): array
	{
		$sql = "SELECT coalesce(sb.cpf_cnpj, sp.cpf_cnpj) AS cpf_cnpj
					  ,pd.id_forma_pagamento
			          ,fp.ds_forma_pagamento
			          ,b.ds_nome_banco
			          ,b.nr_banco
			          ,ib.ds_agencia
			          ,ib.nu_digito_agencia
			          ,ib.ds_conta
			          ,ib.nu_digito_conta
			          ,ib.ds_tipo_conta
			          ,pd.fl_conta_conjunta
					  ,pds.id_proposta_devolucao
					  ,pp.cpf_cnpj as cpf_cnpj_segurado
				  FROM seguro.propostas_devolucoes pd
				  JOIN seguro.propostas_devolucoes_status pds ON pds.id_proposta_devolucao = pd.id
				  JOIN sistema.status st ON st.id = pds.id_status
				  JOIN sistema.informacoes_bancarias ib ON ib.id = pd.id_informacao_bancaria
				  JOIN sistema.bancos b ON b.id = ib.id_banco
				  JOIN seguro.propostas_proponentes pp ON pp.id_proposta = pd.id_proposta
				  LEFT JOIN sistema.formas_pagamento fp ON fp.id = pd.id_forma_pagamento
				  LEFT JOIN seguro.proponentes sp ON sp.id = pd.id_proponente
				  LEFT JOIN seguro.beneficiarios sb ON sb.id = pd.id_beneficiario
		";

		$where = [
			"st.ds_chave = 'EM_PROCESSAMENTO_DADOS_BANCARIOS'",
			"st.id_tipo_status = 33",
		];

		if (!empty($dtAtualizacao)) {
			$dtAtualizacao = $this->formatDate($dtAtualizacao, "Y-MM-dd");

			$where[] = "pds.fl_ativo is false";
			$where[] = "pds.dt_alteracao::date = '{$dtAtualizacao}'";
			$where[] = "pds.id_usuario_alteracao = 2068";
		} else {
			$where[] = "pds.fl_ativo is true";
		}

		$where = implode(" AND ", $where);
		$sql .= "WHERE {$where} ;";

		$rs = $this->getDb()->fetchAll($sql);

		if (!empty($dtAtualizacao)) {
			return $rs;
		}

		foreach ($rs as &$row) {
			$rsUpdate = $this->getDb()->query("
				UPDATE seguro.propostas_devolucoes_status
				   SET fl_ativo = false,
				       id_usuario_alteracao = 2068,
					   dt_alteracao = now()
				 WHERE fl_ativo = true
				   AND id_proposta_devolucao = {$row["id_proposta_devolucao"]}
			");

			if (!$rsUpdate) {
				throw new Exception(
					"Erro ao desabilitar o status da devolução {$row["id_proposta_devolucao"]}.",
				);
			}

			$rsInsert = $this->getDb()->query("
				INSERT INTO seguro.propostas_devolucoes_status (id_usuario_criacao, id_proposta_devolucao, id_status, fl_observacao_automatica)
					VALUES (
						2068,
						{$row["id_proposta_devolucao"]},
						(SELECT id
						   FROM sistema.status
						  WHERE ds_chave = 'EM_PROCESSAMENTO'
						    AND id_tipo_status = 33),
						true
					)
			");

			if (!$rsInsert) {
				throw new Exception(
					"Erro ao desabilitar o status da devolução {$row["id_proposta_devolucao"]}.",
				);
			}

			unset($row["id_proposta_devolucao"]);
		}

		return $rs;
	}

	private function relatorioParcelas(int $idSafra)
	{
		$this->setDb();
		return $this->getDb()->fetchAll(
			"select * from sumario.relatorio_parcelas where id_safra = {$idSafra}",
		);
	}
	public function parcelas(string $idSafra): array
	{
		$Rs = $this->relatorioParcelas($idSafra);
		$arr = [];
		$count = 0;

		foreach ($Rs as $Row) {
			$arr[$count]["Proposta"] = $Row["id_proposta"];
			$arr[$count]["Endosso"] = $Row["nr_endosso"];
			$arr[$count]["Segurado"] = $Row["ds_nome_proponente"];
			$arr[$count]["Cultura"] = $Row["ds_nome_cultura"];
			$arr[$count]["Corretor"] = $Row["ds_nome_fantasia"];
			$arr[$count]["Sublogin"] = $Row["sublogin"];
			$arr[$count]["Início de Vigência"] = $Row["dt_vigencia_inicio"];
			$arr[$count]["N° Parcela"] = $Row["nr_parcela"];
			$arr[$count]["Valor Segurado"] = $Row["vl_segurado"];
			$arr[$count]["Vencimento"] = $Row["dt_vencimento"];
			$arr[$count]["Status Pagamento"] = $Row["fl_pago"];
			$arr[$count]["Vencimento Prorrogado"] = $Row["dt_vencto_prorrogado"];
			$count++;
		}

		return $arr;
	}

	/**
	 * Retornam os itens e seus CAR pelo id proposta do ERP (nr_identificador_seguradora) informado
	 *
	 * @param string $id_proposta_erp
	 * @return array 
	 */
	public function getItensCarByIdPropostaERP(string $id_proposta_erp): array
	{
		try {
			$this->setDb();
			$sql = "
				SELECT pr.id as id_proposta
				      ,pr.nr_apolice
					  ,ds_identificador_seguradora as id_proposta_erp
					  ,sis.nr_item_segurado
					  ,sis.ds_item_segurado
					  ,sisc.ds_car
					  ,sisc.ds_status_imovel
				  FROM seguro.propostas pr
				  JOIN seguro.itens_segurados sis ON sis.id_proposta = pr.id
				  JOIN seguro.itens_segurados_car sisc ON sisc.id_item_segurado = sis.id AND sisc.fl_selecionado = true
				  JOIN seguro.propostas_endosso pe ON pe.id_proposta = pr.id AND pe.fl_proposta_vigente = true
				 WHERE pr.ds_identificador_seguradora = '{$id_proposta_erp}';
			";
			$itens = $this->getDb()->fetchAll($sql);

			$result = [];
			foreach ($itens as $item) {
				$result["nr_apolice"] = $item["nr_apolice"];
				$result["id_proposta_erp"] = $item["id_proposta_erp"];
				$result["id_proposta"] = $item["id_proposta"];
				$nrItem = $item["nr_item_segurado"];
				$result["itens"][$nrItem]["nr_item_segurado"] = $nrItem;
				$result["itens"][$nrItem]["ds_item_segurado"] = $item["ds_item_segurado"];
				$result["itens"][$nrItem]["ds_car"] = $item["ds_car"];
				$result["itens"][$nrItem]["ds_status_imovel"] = $item["ds_status_imovel"];
			}
		} catch (Exception $e) {
			$result = $e->getMessage();
		}

		return $result;
	}
}