package br.com.centralit.citcorpore.integracao;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import br.com.centralit.citcorpore.bean.SmartDecisionsDTO;
import br.com.centralit.citcorpore.util.CITCorporeUtil;
import br.com.citframework.excecao.ServiceException;
import br.com.citframework.integracao.CrudDaoDefaultImpl;
import br.com.citframework.integracao.Field;
import br.com.citframework.util.Constantes;

public class SmartDecisionsDAO extends CrudDaoDefaultImpl {

	public SmartDecisionsDAO() {
		super(Constantes.getValue("DATABASE_ALIAS"), null);
	}

	@Override
	public Collection<Field> getFields() {
		final Collection<Field> listFields = new ArrayList<>();
		return listFields;
	}

	@Override
	public String getTableName() {
		return null;
	}

	@Override
	public Class<SmartDecisionsDTO> getBean() {
		return SmartDecisionsDTO.class;
	}

	@SuppressWarnings("unchecked")
	public Map<String, HashMap<Integer, Integer>> dashboardProcessos(String month, String year) throws ServiceException, Exception {
		Map<String, HashMap<Integer, Integer>> data = new HashMap<String, HashMap<Integer, Integer>>();
		List<Object[]> list = new ArrayList<>();

		StringBuilder sb = new StringBuilder();

		/*
		 * Carrega dados de incidentes/requisições
		 */

		if (CITCorporeUtil.SGBD_PRINCIPAL.equalsIgnoreCase("SQLSERVER")) {
			sb.append("SELECT DAY(datahorasolicitacao) dia, count(*) quantidade FROM solicitacaoservico WHERE convert(VARCHAR(7),datahorasolicitacao, 120) = '" + year + "-" + month + "' group by DAY(datahorasolicitacao)");
		} else {
			sb.append("SELECT to_char(datahorasolicitacao, 'DD') dia, count(*) quantidade FROM solicitacaoservico WHERE to_char(datahorasolicitacao, 'MM/YYYY') = '" + month + "/" + year + "' GROUP BY to_char(datahorasolicitacao, 'DD')");
		}

		list = this.execSQL(sb.toString(), null);

		data.put("citcorpore.comum.incidenteRequisicao", new HashMap<Integer, Integer>());

		if (list != null) {
			for (Object[] object : list) {
				try {
					Integer quantidade = data.get("citcorpore.comum.incidenteRequisicao").get(Integer.parseInt(object[0] + "")) == null ? 0 : data.get("citcorpore.comum.incidenteRequisicao").get(Integer.parseInt(object[0] + ""));
					data.get("citcorpore.comum.incidenteRequisicao").put(Integer.parseInt(object[0] + ""), quantidade + ((Long) object[1]).intValue());
				} catch (Exception e) {
					data.get("citcorpore.comum.incidenteRequisicao").put(Integer.parseInt(object[0] + ""), 0);
				}
			}
		}

		/*
		 * Carrega dados de problemas
		 */

		sb.setLength(0);

		if (CITCorporeUtil.SGBD_PRINCIPAL.equalsIgnoreCase("SQLSERVER")) {
			sb.append("SELECT DAY(datahorainicio) dia, count(*) quantidade FROM problema WHERE convert(VARCHAR(7),datahorainicio, 120) = '" + year + "-" + month + "' group by DAY(datahorainicio)");
		} else {
			sb.append("SELECT to_char(datahorainicio, 'DD') dia, count(*) quantidade FROM problema WHERE to_char(datahorainicio, 'MM/YYYY') = '" + month + "/" + year + "' GROUP BY to_char(datahorainicio, 'DD')");
		}

		list = this.execSQL(sb.toString(), null);

		data.put("citcorpore.comum.problema", new HashMap<Integer, Integer>());

		if (list != null) {
			for (Object[] object : list) {
				try {
					Integer quantidade = data.get("citcorpore.comum.problema").get(Integer.parseInt(object[0] + "")) == null ? 0 : data.get("citcorpore.comum.problema").get(Integer.parseInt(object[0] + ""));
					data.get("citcorpore.comum.problema").put(Integer.parseInt(object[0] + ""), quantidade + ((Long) object[1]).intValue());
				} catch (Exception e) {
					data.get("citcorpore.comum.problema").put(Integer.parseInt(object[0] + ""), 0);
				}
			}
		}

		/*
		 * Carrega dados de mudanças
		 */

		sb.setLength(0);

		if (CITCorporeUtil.SGBD_PRINCIPAL.equalsIgnoreCase("SQLSERVER")) {
			sb.append("SELECT DAY(datahorainicio) dia, count(*) quantidade FROM requisicaomudanca WHERE convert(VARCHAR(7),datahorainicio, 120) = '" + year + "-" + month + "' group by DAY(datahorainicio)");
		} else {
			sb.append("SELECT to_char(datahorainicio, 'DD') dia, count(*) quantidade FROM requisicaomudanca WHERE to_char(datahorainicio, 'MM/YYYY') = '" + month + "/" + year + "' GROUP BY to_char(datahorainicio, 'DD')");
		}

		list = this.execSQL(sb.toString(), null);

		data.put("citcorpore.comum.mudanca", new HashMap<Integer, Integer>());

		if (list != null) {
			for (Object[] object : list) {
				try {
					Integer quantidade = data.get("citcorpore.comum.mudanca").get(Integer.parseInt(object[0] + "")) == null ? 0 : data.get("citcorpore.comum.mudanca").get(Integer.parseInt(object[0] + ""));
					data.get("citcorpore.comum.mudanca").put(Integer.parseInt(object[0] + ""), quantidade + ((Long) object[1]).intValue());
				} catch (Exception e) {
					data.get("citcorpore.comum.mudanca").put(Integer.parseInt(object[0] + ""), 0);
				}
			}
		}

		/*
		 * Carrega dados de liberações
		 */

		sb.setLength(0);

		if (CITCorporeUtil.SGBD_PRINCIPAL.equalsIgnoreCase("SQLSERVER")) {
			sb.append("SELECT DAY(datainicial) dia, count(*) quantidade FROM liberacao WHERE convert(VARCHAR(7),datainicial, 120) = '" + year + "-" + month + "' group by DAY(datainicial)");
		} else {
			sb.append("SELECT to_char(datainicial, 'DD') dia, count(*) quantidade FROM liberacao WHERE to_char(datainicial, 'MM/YYYY') = '" + month + "/" + year + "' GROUP BY to_char(datainicial, 'DD')");
		}

		list = this.execSQL(sb.toString(), null);

		data.put("citcorpore.comum.liberacao", new HashMap<Integer, Integer>());

		if (list != null) {
			for (Object[] object : list) {
				try {
					Integer quantidade = data.get("citcorpore.comum.liberacao").get(Integer.parseInt(object[0] + "")) == null ? 0 : data.get("citcorpore.comum.liberacao").get(Integer.parseInt(object[0] + ""));
					data.get("citcorpore.comum.liberacao").put(Integer.parseInt(object[0] + ""), quantidade + ((Long) object[1]).intValue());
				} catch (Exception e) {
					data.get("citcorpore.comum.liberacao").put(Integer.parseInt(object[0] + ""), 0);
				}
			}
		}

		return data;
	}

	@SuppressWarnings("unchecked")
	public List<Object[]> solicitacoesPorServico(Date dataInicio, Date dataFim, Integer idServicoNegocio) throws ServiceException, Exception {
		List<Object> parametro = new ArrayList<>();

		StringBuilder sb = new StringBuilder();

		sb.append("SELECT ");

		if (idServicoNegocio == null) {
			sb.append("DISTINCT servico.idservico, coalesce(servico.nomeservico, 'N/A'), count(solicitacaoservico.idsolicitacaoservico) ");
		} else {
			sb.append("DISTINCT atividade.idservico, coalesce(atividade.nomeservico, 'N/A'), count(solicitacaoservico.idsolicitacaoservico) ");
		}

		sb.append("FROM ");
		sb.append("solicitacaoservico ");
		sb.append("JOIN servicocontrato on solicitacaoservico.idservicocontrato = servicocontrato.idservicocontrato ");
		sb.append("JOIN servico atividade on servicocontrato.idservico = atividade.idservico ");
		sb.append("LEFT JOIN servicoautorelacionamento rel on atividade.idservico = rel.idservico ");
		sb.append("LEFT JOIN servico servico on rel.idservicorelacionado = servico.idservico ");
		sb.append("WHERE ");
		sb.append("datahorasolicitacao BETWEEN ? and ? ");

		parametro.add(dataInicio);
		parametro.add(dataFim);

		if (idServicoNegocio != null) {
			if (idServicoNegocio == 0) {
				sb.append("AND servico.idservico is NULL ");
			} else {
				sb.append("AND servico.idservico = ? ");
				parametro.add(idServicoNegocio);
			}
		}

		sb.append("GROUP BY ");

		if (idServicoNegocio == null) {
			sb.append("servico.idservico, servico.nomeservico ");
		} else {
			sb.append("atividade.idservico, atividade.nomeservico ");
		}

		return this.execSQL(sb.toString(), parametro.toArray());
	}

	@SuppressWarnings("unchecked")
	public List<Object[]> itensConfiguracaoSoftwares(String periodo, String palavraChave) throws ServiceException, Exception {
		StringBuilder sb = new StringBuilder();

		sb.append("select distinct ");
		sb.append("	count(*), itemconfiguracao.identificacao, itemconfiguracao.datainicio ");
		sb.append("from");
		sb.append("	itemconfiguracao JOIN tipoitemconfiguracao ON itemconfiguracao.idtipoitemconfiguracao = tipoitemconfiguracao.idtipoitemconfiguracao ");
		sb.append("where ");
		sb.append("	itemconfiguracao.datafim is null ");

		if (palavraChave != null && !palavraChave.isEmpty()) {
			sb.append("and upper(itemconfiguracao.identificacao) like upper('%" + palavraChave + "%') ");
		}

		if (periodo != null && !periodo.isEmpty()) {
			String[] p = periodo.split("/");

			if (CITCorporeUtil.SGBD_PRINCIPAL.equalsIgnoreCase("SQLSERVER")) {
				sb.append("and convert(VARCHAR(7),itemconfiguracao.datainicio, 120) = '" + p[1] + "-" + p[0] + "' ");
			} else {
				sb.append("and to_char(itemconfiguracao.datainicio, 'MM/YYYY') = '" + p[0] + "/" + p[1] + "' ");
			}
		}

		sb.append("	and tipoitemconfiguracao.nometipoitemconfiguracao = 'SOFTWARES' ");
		sb.append("group by ");
		sb.append("	itemconfiguracao.identificacao, itemconfiguracao.datainicio ");

		return this.execSQL(sb.toString(), null);
	}
}
