package br.com.centralit.citcorpore.integracao;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import br.com.centralit.citcorpore.bean.VinculoServicoProprietariosDTO;
import br.com.centralit.citcorpore.util.Util;
import br.com.citframework.integracao.CrudDaoDefaultImpl;
import br.com.citframework.integracao.Field;
import br.com.citframework.util.Constantes;

/**
 * @since 20.01.2016
 * @author gustavo.gomes
 */
@SuppressWarnings({ "rawtypes", "unchecked" })
public class VinculoServicoProprietariosDao extends CrudDaoDefaultImpl {

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

	public VinculoServicoProprietariosDao(String databaseAlias) {
		super(databaseAlias, null);
	}

	@Override
	public String getTableName() {
		return this.getOwner() + "VinculoServicoProprietarios";
	}

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

		listFields.add(new Field("idVinculoServicoProprietarios", "idVinculoServicoProprietarios", true, true, false, false));
		listFields.add(new Field("idServico", "idServico", false, false, false, false));
		listFields.add(new Field("idEmpregado", "idEmpregado", false, false, false, false));
		listFields.add(new Field("idGrupo", "idGrupo", false, false, false, false));
		listFields.add(new Field("tipo", "tipo", false, false, false, false));

		return listFields;
	}

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

	public void deletaVinculo(Integer idVinculo) throws Exception {
		StringBuilder sql = new StringBuilder();
		List parametros = new ArrayList();

		sql.append("DELETE FROM VINCULOSERVICOPROPRIETARIOS WHERE IDVINCULOSERVICOPROPRIETARIOS = ?");
		parametros.add(idVinculo);

		try {
			this.execUpdate(sql.toString(), parametros.toArray());
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e);
		}
	}

	public boolean existeVinculo(Integer idServico, Integer idVinculado, Integer tipo) throws Exception {
		StringBuilder sql = new StringBuilder();
		List parametros = new ArrayList();
		List result;
		sql.append("SELECT * ");
		sql.append("FROM VINCULOSERVICOPROPRIETARIOS ");

		if (tipo.equals(VinculoServicoProprietariosDTO.TIPO_GRUPO))
			sql.append("WHERE IDSERVICO = ? AND IDGRUPO = ?");
		else
			sql.append("WHERE IDSERVICO = ? AND idEmpregado = ?");

		parametros.add(idServico);
		parametros.add(idVinculado);

		try {
			result = this.execSQL(sql.toString(), parametros.toArray());
			if (result == null || result.isEmpty())
				return false;
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e);
		}

	}

	/**
	 * @since 20.01.2016
	 * @author gustavo.gomes
	 *         Executa a busca dos vínculos com o filtro
	 */
	public Collection<VinculoServicoProprietariosDTO> findProprietariosByServico(Integer idServico, String like) throws Exception {
		try {
			StringBuilder sql = new StringBuilder();
			List parametros = new ArrayList();
			List result = new ArrayList<VinculoServicoProprietariosDTO>();
			Collection<VinculoServicoProprietariosDTO> listaVinculo;

			List<String> listRetorno = new ArrayList<String>();
			listRetorno.add("idVinculoServicoProprietarios");
			listRetorno.add("idServico");
			listRetorno.add("idEmpregado");
			listRetorno.add("idGrupo");
			listRetorno.add("tipo");

			if (like == null || like.isEmpty()) {
				sql.append("SELECT * ");
				sql.append("FROM VINCULOSERVICOPROPRIETARIOS ");
				sql.append("WHERE IDSERVICO = ?");
				parametros.add(idServico);

				result = this.execSQL(sql.toString(), parametros.toArray());
				listaVinculo = (List<VinculoServicoProprietariosDTO>) this.engine.listConvertion(getBean(), result, listRetorno);
				return listaVinculo;
			} else {
				parametros.add(idServico);

				sql.append("SELECT V.IDVINCULOSERVICOPROPRIETARIOS, V.IDSERVICO, V.IDEMPREGADO, V.IDGRUPO, V.TIPO ");
				sql.append("FROM VINCULOSERVICOPROPRIETARIOS V INNER JOIN EMPREGADOS E ON V.IDEMPREGADO = E.IDEMPREGADO ");
				sql.append("WHERE IDSERVICO = ? AND ").append(Util.getQueryLikeWithCaseInsensitiveAndAccentInsensitive(MAIN_SGBD, "E.NOME", like));
				result = this.execSQL(sql.toString(), parametros.toArray());
				listaVinculo = (List<VinculoServicoProprietariosDTO>) this.engine.listConvertion(getBean(), result, listRetorno);

				sql.delete(0, sql.length());

				sql.append("SELECT V.IDVINCULOSERVICOPROPRIETARIOS, V.IDSERVICO, V.IDEMPREGADO, V.IDGRUPO, V.TIPO ");
				sql.append("FROM VINCULOSERVICOPROPRIETARIOS V INNER JOIN GRUPO G ON V.IDGRUPO = G.IDGRUPO ");
				sql.append("WHERE IDSERVICO = ? AND ").append(Util.getQueryLikeWithCaseInsensitiveAndAccentInsensitive(MAIN_SGBD, "G.NOME", like));
				result = this.execSQL(sql.toString(), parametros.toArray());
				listaVinculo.addAll((List<VinculoServicoProprietariosDTO>) this.engine.listConvertion(getBean(), result, listRetorno));

				return listaVinculo;
			}


		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e);
		}
	}

}