package br.com.centralit.citcorpore.integracao;

import java.math.BigDecimal;
import java.sql.Date;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;

import br.com.centralit.citcorpore.bean.ContratoDTO;
import br.com.centralit.citcorpore.bean.ServicoDTO;
import br.com.centralit.citcorpore.util.ArvoreServico;
import br.com.centralit.citcorpore.util.CITCorporeUtil;
import br.com.centralit.citcorpore.util.Enumerados.CategoriaPortfolio;
import br.com.centralit.citcorpore.util.Enumerados.ServiceRequestStatus;
import br.com.centralit.citcorpore.util.Enumerados.SimNao;
import br.com.centralit.citcorpore.util.Enumerados.StatusServico;
import br.com.centralit.citcorpore.util.Enumerados.TipoServico;
import br.com.citframework.dto.IDto;
import br.com.citframework.excecao.PersistenceException;
import br.com.citframework.integracao.Condition;
import br.com.citframework.integracao.CrudDaoDefaultImpl;
import br.com.citframework.integracao.Field;
import br.com.citframework.integracao.Order;
import br.com.citframework.integracao.core.DataBase;
import br.com.citframework.integracao.core.Page;
import br.com.citframework.integracao.core.PageImpl;
import br.com.citframework.integracao.core.Pageable;
import br.com.citframework.integracao.core.PagingQueryUtil;
import br.com.citframework.util.Constantes;
import br.com.citframework.util.SQLConfig;
import br.com.citframework.util.UtilDatas;
import br.com.citframework.util.UtilNumbersAndDecimals;
import br.com.citframework.util.UtilStrings;

@SuppressWarnings({ "rawtypes", "unchecked" })
public class ServicoDao extends CrudDaoDefaultImpl {
	public ServicoDao() {
		super(Constantes.getValue("DATABASE_ALIAS"), null);
	}

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

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

		listFields.add(new Field("idServico", "idServico", true, true, false, false));
		listFields.add(new Field("idCategoriaServico", "idCategoriaServico", false, false, false, false));
		listFields.add(new Field("idSituacaoServico", "idSituacaoServico", false, false, false, false));
		listFields.add(new Field("idTipoServico", "idTipoServico", false, false, false, false));
		listFields.add(new Field("idImportanciaNegocio", "idImportanciaNegocio", false, false, false, false));
		listFields.add(new Field("idEmpresa", "idEmpresa", false, false, false, false));
		listFields.add(new Field("idTipoEventoServico", "idTipoEventoServico", false, false, false, false));
		listFields.add(new Field("idTipoDemandaServico", "idTipoDemandaServico", false, false, false, false));
		listFields.add(new Field("idLocalExecucaoServico", "idLocalExecucaoServico", false, false, false, false));
		listFields.add(new Field("nomeServico", "nomeServico", false, false, false, false));
		listFields.add(new Field("detalheServico", "detalheServico", false, false, false, false));
		listFields.add(new Field("objetivo", "objetivo", false, false, false, false));
		listFields.add(new Field("passosServico", "passosServico", false, false, false, false));
		listFields.add(new Field("dataInicio", "dataInicio", false, false, false, false));
		listFields.add(new Field("linkProcesso", "linkProcesso", false, false, false, false));
		listFields.add(new Field("descricaoProcesso", "descricaoProcesso", false, false, false, false));
		listFields.add(new Field("tipoDescProcess", "tipoDescProcess", false, false, false, false));
		listFields.add(new Field("dispPortal", "dispPortal", false, false, false, false));
		listFields.add(new Field("siglaAbrev", "siglaAbrev", false, false, false, false));
		listFields.add(new Field("deleted", "deleted", false, false, false, false));
		listFields.add(new Field("idBaseconhecimento", "idBaseconhecimento", false, false, false, false));
		listFields.add(new Field("idTemplateSolicitacao", "idTemplateSolicitacao", false, false, false, false));
		listFields.add(new Field("idTemplateAcompanhamento", "idTemplateAcompanhamento", false, false, false, false));
		listFields.add(new Field("tipoServico", "tipoServico", false, false, false, false));
		listFields.add(new Field("idPortfolioServico", "idPortfolioServico", false, false, false, false));
		listFields.add(new Field("faseServico", "faseServico", false, false, false, false));
		listFields.add(new Field("statusServico", "statusServico", false, false, false, false));
		listFields.add(new Field("processoDeIniciacao", "processoDeIniciacao", false, false, false, false));
		listFields.add(new Field("tipoDeInvestimento", "tipoDeInvestimento", false, false, false, false));
		listFields.add(new Field("valoresDoServico", "valoresDoServico", false, false, false, false));
		listFields.add(new Field("idTemplateServico", "idTemplateServico", false, false, false, false));
		listFields.add(new Field("criticidadeservico", "criticidadeServico", false, false, false, false));
		listFields.add(new Field("demandaservico", "demandaServico", false, false, false, false));
		listFields.add(new Field("retornofinanceiroservico", "retornoFinanceiroServico", false, false, false, false));
		listFields.add(new Field("incidentecritico", "incidenteCritico", false, false, false, false));

		listFields.add(new Field("nomeportal", "nomePortal", false, false, false, false));
		listFields.add(new Field("descricaoportal", "descricaoPortal", false, false, false, false));
		listFields.add(new Field("imagemportal", "imagemPortal", false, false, false, false));

		return listFields;
	}

	private Timestamp transformaHoraFinal(Date data) throws ParseException {
		String dataHora = data + " 23:59:59";
		String pattern = "yyyy-MM-dd hh:mm:ss";
		SimpleDateFormat sdf = new SimpleDateFormat(pattern);
		java.util.Date d = sdf.parse(dataHora);
		java.sql.Timestamp sqlDate = new java.sql.Timestamp(d.getTime());
		return sqlDate;
	}
	/**
	 * Executando em: Oracle, sqlServer, mySql e POstgreSQL
	 **/
	public Collection<ServicoDTO> listaQuantidadeServicoAnalitico(ServicoDTO servicoDTO)
			throws Exception {
		List listRetorno = new ArrayList();
		List parametro = new ArrayList();
		List listaQuantidadeServicoAnalitico = new ArrayList<ServicoDTO>();

		/**
		 * Checa se é necessário aplicar limite
		 *
		 * @author thyen.chang
		 */
		boolean seLimita = servicoDTO.getTopList() != 0;

		/*
		 * Desenvolvedor: Rodrigo Pecci - Data: 31/10/2013 - Horário: 15h35min -
		 * ID Citsmart: 120770 Motivo/Comentário: Novas cláusulas foram
		 * adicionadas no where para garantir a consistência do relatório.
		 */

		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		/**
		 * Adiciona Limite para SqlServer
		 *
		 * @author thyen.chang
		 */
		if(seLimita && CITCorporeUtil.SGBD_PRINCIPAL.trim().toUpperCase().equalsIgnoreCase(SQLConfig.SQLSERVER)) {
			sql.append("TOP " + servicoDTO.getTopList().toString() + " ");
		}
		sql.append("solicitacaoservico.idsolicitacaoservico, ");
		sql.append("       tipocomplexidade.desctipocomplexidade, ");
		sql.append("       servico.nomeservico, ");
		sql.append("       tipodemandaservico.nometipodemandaservico, ");
		sql.append("       atividadesservicocontrato.custoatividade, ");
		sql.append("       usuario.nome, ");
		sql.append(" ( CASE  ");
		sql.append("				WHEN solicitacaoservico.idStatus = ? THEN ' '  ");
		parametro.add(ServiceRequestStatus.CANCELED.getId());
		sql.append(" 	WHEN solicitacaoservico.prazomm = 0 and solicitacaoservico.prazohh = 0  THEN  'sim'  ");
		sql.append(" 	ELSE ");
		sql.append(" 		( CASE ");
		sql.append(" 			WHEN solicitacaoservico.datahoralimite IS NULL THEN 'sim' ");
		sql.append(" 			WHEN ");
		sql.append(" 			( CASE   ");
		sql.append(" 				WHEN solicitacaoservico.situacaosla = 'S' and solicitacaoservico.idStatus = ? THEN solicitacaoservico.datahorasuspensaosla  ");
		parametro.add(ServiceRequestStatus.CLOSED.getId());
		sql.append("				WHEN solicitacaoservico.situacaosla = 'S' and solicitacaoservico.idStatus = ? THEN solicitacaoservico.datahorasuspensaosla  ");
		parametro.add(ServiceRequestStatus.SOLVED.getId());
		sql.append("				WHEN solicitacaoservico.idStatus= ? THEN solicitacaoservico.datahorafim  ");
		parametro.add(ServiceRequestStatus.CLOSED.getId());
		sql.append("				WHEN solicitacaoservico.datahorafim IS NOT NULL THEN solicitacaoservico.datahorafim  ");
		sql.append("			ELSE CURRENT_TIMESTAMP ");
		sql.append("	 		END) <= solicitacaoservico.datahoralimite THEN 'sim' ");
		sql.append("	 	ELSE 'nao' ");
		sql.append("	 END) ");
		sql.append(" END ) ");
		sql.append("      as  slaatendida, ");
		sql.append("       solicitacaoservico.tempoatendimentohh, ");
		sql.append("       solicitacaoservico.tempoatendimentomm, ");
		sql.append("       solicitacaoservico.idStatus ");
		sql.append("FROM   solicitacaoservico solicitacaoservico ");
		sql.append("       INNER JOIN tipodemandaservico tipodemandaservico ");
		sql.append("               ON tipodemandaservico.idtipodemandaservico = solicitacaoservico.idtipodemandaservico ");
		sql.append("       INNER JOIN servicocontrato servicocontrato ");
		sql.append("               ON servicocontrato.idservicocontrato = solicitacaoservico.idservicocontrato ");
		sql.append("       INNER JOIN contratos ");
		sql.append("               ON servicocontrato.idcontrato = contratos.idcontrato ");
		sql.append("              AND (upper(contratos.deleted)  = 'N' or contratos.deleted is null) ");
		sql.append("       INNER JOIN servico servico ");
		sql.append("               ON servico.idservico = servicocontrato.idservico ");
		sql.append("       LEFT JOIN bpm_itemtrabalhofluxo bpm_itemtrabalhofluxo ");
		sql.append("               ON solicitacaoservico.idtarefaencerramento = bpm_itemtrabalhofluxo.iditemtrabalho ");
		sql.append("       LEFT JOIN atividadesservicocontrato atividadesservicocontrato ");
		sql.append("              ON atividadesservicocontrato.idServicoContrato = servicocontrato.idservicocontrato ");
		sql.append("                 AND (atividadesservicocontrato.deleted is null OR atividadesservicocontrato.deleted <> 'Y' )");
		sql.append("       LEFT JOIN tipocomplexidade tipocomplexidade ");
		sql.append("              ON tipocomplexidade.complexidade = atividadesservicocontrato.complexidade ");
		sql.append("       LEFT JOIN usuario usuario ");
		sql.append("              ON usuario.idusuario = bpm_itemtrabalhofluxo.idresponsavelatual ");

		if (CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.ORACLE)) {
			sql.append("WHERE ");
			/**
			 * Adiciona Limite para Oracle
			 *
			 * @author thyen.chang
			 */
			if(seLimita){
				sql.append(" ROWNUM <= ? AND ");
				parametro.add(servicoDTO.getTopList());
			}
			sql.append("to_char(solicitacaoservico.datahorafim, 'YYYY-MM-DD') BETWEEN ? AND ?  ");
			SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
			parametro.add(formatter.format(servicoDTO.getDataInicio()));
			parametro.add(formatter.format(servicoDTO.getDataFim()));
		} else {
			sql.append("WHERE  solicitacaoservico.datahorafim BETWEEN ? AND ? ");
			parametro.add(servicoDTO.getDataInicio());
			parametro.add(transformaHoraFinal(servicoDTO.getDataFim()));
		}
		if (servicoDTO.getIdContrato() != null) {
			sql.append("AND servicocontrato.idcontrato = ? ");
			parametro.add(servicoDTO.getIdContrato());
		}
		if (servicoDTO.getIdTipoDemandaServico() != null) {
			sql.append("AND tipodemandaservico.idtipodemandaservico = ? ");
			parametro.add(servicoDTO.getIdTipoDemandaServico());
		}


		sql.append(" AND (upper(contratos.deleted) = 'N' or contratos.deleted is null) ");
		sql.append(" AND solicitacaoservico.idtipodemandaservico is not null ");
		sql.append(" AND solicitacaoservico.idStatus in (?, ?, ?) ");
		parametro.add(ServiceRequestStatus.CLOSED.getId());
		parametro.add(ServiceRequestStatus.CANCELED.getId());
		parametro.add(ServiceRequestStatus.SOLVED.getId());

		/*
		 * sql.append("GROUP BY solicitacaoservico.idsolicitacaoservico ");
		 * sql.append("        ,tipocomplexidade.desctipocomplexidade ");
		 * sql.append("        ,servico.nomeservico ");
		 * sql.append("        ,tipodemandaservico.nometipodemandaservico ");
		 * sql.append("        ,usuario.nome ");
		 * sql.append(" ,( CASE  ");
		 * sql.append("				WHEN solicitacaoservico.idStatus = ? THEN ' '  ");
		 * parametro.add(ServiceRequestStatus.CANCELED.getId());
		 * sql.append(" 	WHEN solicitacaoservico.prazomm = 0 and solicitacaoservico.prazohh = 0  THEN  'sim'  ");
		 * sql.append(" 	ELSE ");
		 * sql.append(" 		( CASE ");
		 * sql.append(" 			WHEN solicitacaoservico.datahoralimite IS NULL THEN 'sim' ");
		 * sql.append(" 			WHEN ");
		 * sql.append(" 			( CASE   ");
		 * sql.append(" 				WHEN solicitacaoservico.situacaosla = 'S' and solicitacaoservico.idStatus = ? THEN solicitacaoservico.datahorasuspensaosla  ");
		 * parametro.add(ServiceRequestStatus.CLOSED.getId());
		 * sql.append("				WHEN solicitacaoservico.situacaosla = 'S' and solicitacaoservico.idStatus = ? THEN solicitacaoservico.datahorasuspensaosla  ");
		 * parametro.add(ServiceRequestStatus.SOLVED.getId());
		 * sql.append("				WHEN solicitacaoservico.idStatus= ? THEN solicitacaoservico.datahorafim  ");
		 * parametro.add(ServiceRequestStatus.CLOSED.getId());
		 * sql.append("				WHEN solicitacaoservico.datahorafim IS NOT NULL THEN solicitacaoservico.datahorafim  ");
		 * sql.append("			ELSE CURRENT_TIMESTAMP ");
		 * sql.append("	 		END) <= solicitacaoservico.datahoralimite THEN 'sim' ");
		 * sql.append("	 	ELSE 'nao' ");
		 * sql.append("	 END) ");
		 * sql.append(" END ) ");
		 * sql.append("        ,atividadesservicocontrato.custoatividade ");
		 * sql.append("        ,solicitacaoservico.datahorafim ");
		 * sql.append("        ,solicitacaoservico.datahoralimite ");
		 * sql.append("        ,solicitacaoservico.tempoatendimentohh ");
		 * sql.append("        ,solicitacaoservico.tempoatendimentomm ");
		 * sql.append("        ,solicitacaoservico.idStatus ");
		 */


		sql.append("ORDER BY solicitacaoservico.idsolicitacaoservico ");
		sql.append("        ,tipocomplexidade.desctipocomplexidade ");
		sql.append("        ,servico.nomeservico ");
		sql.append("        ,tipodemandaservico.nometipodemandaservico ");
		sql.append("        ,usuario.nome ");
		sql.append("        ,atividadesservicocontrato.custoatividade ");
		sql.append("        ,solicitacaoservico.datahorafim ");
		sql.append("        ,solicitacaoservico.datahoralimite ");
		sql.append("        ,solicitacaoservico.tempoatendimentohh ");
		sql.append("        ,solicitacaoservico.tempoatendimentomm ");
		sql.append("        ,solicitacaoservico.idStatus ");

		/**
		 * Adiciona limite para Postgre, MySQL
		 *
		 * @author thyen.chang
		 */
		if(seLimita && (CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.MYSQL) || CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.POSTGRESQL))){
			sql.append("LIMIT ?");
			parametro.add(servicoDTO.getTopList());
		}


		List lista = new ArrayList();
		lista = this.execSQL(sql.toString(), parametro.toArray());
		listRetorno.add("idSolicitacao");
		listRetorno.add("complexidade");
		listRetorno.add("nomeServico");
		listRetorno.add("nomeTipoDemandaServico");
		listRetorno.add("custoAtividade");
		listRetorno.add("nomeSolucionador");
		listRetorno.add("slaAtendido");
		listRetorno.add("tempoAtendimentoHH");
		listRetorno.add("tempoAtendimentoMM");
		listRetorno.add("idStatus");

		if (lista != null && !lista.isEmpty()) {
			listaQuantidadeServicoAnalitico = this.engine.listConvertion(ServicoDTO.class, lista, listRetorno);
			return listaQuantidadeServicoAnalitico;
		}
		return Collections.emptyList();
	}

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

	@Override
	public Collection list() throws PersistenceException {
		List ordenacao = new ArrayList();
		ordenacao.add(new Order("idServico"));
		return super.list(ordenacao);
	}

	@Override
	public Class getBean() {
		return ServicoDTO.class;
	}

	@Override
	public Collection find(IDto arg0) throws PersistenceException {
		List ordenacao = new ArrayList();
		ordenacao.add(new Order("idServico"));
		return super.find(arg0, ordenacao);
	}

	/**
	 * Verifica se o ServicoDTO informado existe.
	 *
	 * @param ServicoDTO
	 * @return boolean
	 * @throws Exception
	 * @author rodrigo.guilherme
	 */
	public boolean verificarSeServicoExiste(final ServicoDTO servicoDto) throws Exception {

		Collection<ServicoDTO> listServicoDto = new ArrayList<>();

		final List<Condition> condicao = new ArrayList<>();

		if (servicoDto.getNomeServico() != null) {
			condicao.add(new Condition("nomeServico", servicoDto.getNomeServico()));
		}

		if (servicoDto.getIdPortfolioServico() != null) {
			condicao.add(new Condition("idPortfolioServico", servicoDto.getIdPortfolioServico()));
		}

		if (servicoDto.getIdServico() != null) {
			condicao.add(new Condition("idServico", Condition.NOT_EQUALS, servicoDto.getIdServico()));
		}


		condicao.add(new Condition("deleted", "is", null));

		listServicoDto = this.findByCondition(condicao, null);

		if (listServicoDto != null && !listServicoDto.isEmpty()) {
			return true;
		}

		return false;
	}

	public Collection findByIdCategoriaServico(Integer parm) throws Exception {
		List condicao = new ArrayList();
		List ordenacao = new ArrayList();
		condicao.add(new Condition("idCategoriaServico", "=", parm));
		ordenacao.add(new Order("idServicoContrato"));
		return super.findByCondition(condicao, ordenacao);
	}

	public void deleteByIdCategoriaServico(Integer parm) throws Exception {
		List condicao = new ArrayList();
		condicao.add(new Condition("idCategoriaServico", "=", parm));
		super.deleteByCondition(condicao);
	}

	public Collection findByIdSituacaoServico(Integer parm) throws Exception {
		List condicao = new ArrayList();
		List ordenacao = new ArrayList();
		condicao.add(new Condition("idSituacaoServico", "=", parm));
		condicao.add(new Condition("deleted", "<>", "Y"));
		condicao.add(new Condition(Condition.OR, "deleted", "is", null));
		ordenacao.add(new Order("idServico"));
		return super.findByCondition(condicao, ordenacao);
	}

	public void deleteByIdSituacaoServico(Integer parm) throws Exception {
		List condicao = new ArrayList();
		condicao.add(new Condition("idSituacaoServico", "=", parm));
		super.deleteByCondition(condicao);
	}

	public Collection findByIdTipoDemandaAndIdCategoria(Integer idTipoDemanda, Integer idCategoria) throws Exception {
		List condicao = new ArrayList();
		List ordenacao = new ArrayList();
		condicao.add(new Condition("idTipoDemandaServico", "=", idTipoDemanda));
		if (idCategoria != null) {
			condicao.add(new Condition("idCategoriaServico", "=", idCategoria));
		}
		ordenacao.add(new Order("nomeServico"));
		return super.findByCondition(condicao, ordenacao);
	}
	/**
	 * Executando em: Oracle, sqlServer, mySql e POstgreSQL
	 **/
	public Collection findByIdTipoDemandaAndIdContrato(Integer idTipoDemanda, Integer idContrato, Integer idCategoria) throws Exception {
		Collection fields = getFields();
		List listaNomes = new ArrayList();

		Date dataAtual = UtilDatas.getDataAtual();
		Object[] objs = new Object[] {dataAtual};

		String sql = "SELECT ";
		int i = 0;
		for (Iterator it = fields.iterator(); it.hasNext();) {
			Field field = (Field) it.next();
			if (i > 0) {
				sql += ",";
			}

			field.setFieldDB("servico." + field.getFieldDB());

			sql += field.getFieldDB();
			listaNomes.add(field.getFieldClass());
			i++;
		}
		sql += " FROM " + this.getTableName();
		sql += " INNER JOIN situacaoservico ss on ss.idsituacaoservico = servico.idsituacaoservico ";
		sql += " WHERE idTipoDemandaServico = " + idTipoDemanda + " AND ";
		sql += " (ss.ativo = " + SimNao.SIM.getValorUmDois() + ") AND ";
		if (idCategoria != null) {
			sql += " idCategoriaServico = " + idCategoria + " AND ";
		}
		sql += " idServico IN (SELECT idservico FROM servicocontrato WHERE idcontrato = " + idContrato + " AND (datafim is null OR datafim > ?) AND ((deleted IS NULL) OR (UPPER(deleted) = 'N'))) AND";

		if (CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.POSTGRESQL)) {
			sql += "(UPPER(deleted) IS NULL OR UPPER(deleted) = 'N') ";
		} else {
			sql += "(deleted IS NULL OR deleted = 'N' OR deleted = 'n') ";
		}

		sql += " ORDER BY nomeServico";

		List listaR = this.execSQL(sql, objs);
		return this.listConvertion(ServicoDTO.class, listaR, listaNomes);
	}

	public Collection<ServicoDTO> findAutocompleteServico(Integer idTipoDemanda, Integer idContrato, Integer idCategoria, String queryConsulta, Integer idServicoNegocioTecnico) throws Exception {
		Date dataAtual = UtilDatas.getDataAtual();

		if(queryConsulta == null) {
			queryConsulta = "";
		}

		queryConsulta = tratarQueryConsulta(queryConsulta);

		List parametros = new ArrayList();
		StringBuilder sql = new StringBuilder();

		sql.append("SELECT DISTINCT s.idServico, s.nomeServico ");

		if (idContrato!=null&&idContrato>0){
			sql.append("FROM servicocontrato as servcont JOIN servico as s on ");

			if (CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.POSTGRESQL)) {
				sql.append("(remove_acento(s.nomeServico) ilike remove_acento(?)) AND ");
			} else if (CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.SQLSERVER)) {
				sql.append("UPPER(dbo.remove_acento(s.nomeServico)) like UPPER(dbo.remove_acento(?)) AND ");
			} else {
				sql.append("UPPER(remove_acento(s.nomeServico)) like UPPER(remove_acento(?)) AND ");
			}
			parametros.add(queryConsulta);

			if (idTipoDemanda!=null&&idTipoDemanda>0){
				sql.append("s.idTipoDemandaServico = ? AND ");
				parametros.add(idTipoDemanda);
			}

			if (idCategoria != null&&idCategoria>0) {
				sql.append("idCategoriaServico = ? AND ");
				parametros.add(idCategoria);
			}

			sql.append("((s.deleted IS NULL) OR (UPPER(s.deleted) = 'N')) AND ");
			sql.append("s.idservico = servcont.idservico AND ");
			sql.append("servcont.idcontrato = ? AND (servcont.datafim is null OR servcont.datafim > ?) AND ((servcont.deleted IS NULL) OR (UPPER(servcont.deleted) = 'N')) ");
			parametros.add(idContrato);
			parametros.add(dataAtual);
			sql.append("JOIN situacaoservico as ss on (ss.ativo = ").append(SimNao.SIM.getValorUmDois()).append(") AND ss.idsituacaoservico = s.idsituacaoservico ");
			sql.append("LEFT JOIN servicoautorelacionamento as auto on s.idservico = auto.idservico ");
			sql.append("LEFT JOIN servico as servicoauto on auto.idservicorelacionado = servicoauto.idservico ");
			sql.append("WHERE (servicoauto.statusServico = 'PRD' OR servicoauto.statusServico is null) ");

			if (idServicoNegocioTecnico != null && idServicoNegocioTecnico > 0) {
				sql.append("and auto.idServicoRelacionado = ? ");
				parametros.add(idServicoNegocioTecnico);
				sql.append("and auto.dataFim is null ");
			}
		} else {
			sql.append("FROM servico as s ");
			sql.append("JOIN situacaoservico as ss on ");

			if (CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.POSTGRESQL)) {
				sql.append("(UPPER(remove_acento(s.nomeServico)) ilike UPPER(remove_acento(?))) AND ");
			} else {
				sql.append("UPPER(s.nomeServico) like UPPER(?) AND ");
			}
			parametros.add(queryConsulta);

			if (idTipoDemanda!=null&&idTipoDemanda>0){
				sql.append("s.idTipoDemandaServico = ? AND ");
				parametros.add(idTipoDemanda);
			}

			if (idCategoria != null&&idCategoria>0) {
				sql.append("idCategoriaServico = ? AND ");
				parametros.add(idCategoria);
			}

			sql.append("((s.deleted IS NULL) OR (UPPER(s.deleted) = 'N')) AND ");
			sql.append("(ss.ativo = ").append(SimNao.SIM.getValorUmDois()).append(") AND ss.idsituacaoservico = s.idsituacaoservico ");
			sql.append("LEFT JOIN servicoautorelacionamento as auto on s.idservico = auto.idservico ");
			sql.append("LEFT JOIN servico as servicoauto on auto.idservicorelacionado = servicoauto.idservico ");
			sql.append("WHERE (servicoauto.statusServico = 'PRD' OR servicoauto.statusServico is null) ");

			if (idServicoNegocioTecnico != null && idServicoNegocioTecnico > 0) {
				sql.append("AND auto.idServicoRelacionado = ? ");
				parametros.add(idServicoNegocioTecnico);
			}
		}

		sql.append("ORDER BY s.nomeServico");

		List lista = this.execSQL(sql.toString(), parametros.toArray());

		List listaRetorno = new ArrayList();
		listaRetorno.add("idServico");
		listaRetorno.add("nomeServico");

		return this.listConvertion(ServicoDTO.class, lista, listaRetorno);
	}

	public Collection<ServicoDTO> findAutocompleteServicoNegocioTecnico(Integer idContrato, String tipoServicoNegocioTecnico, String queryConsulta) throws Exception {
		return findAutocompleteServicoNegocioTecnico(idContrato, tipoServicoNegocioTecnico, queryConsulta, null);
	}

	public Collection<ServicoDTO> findAutocompleteServicoNegocioTecnico(Integer idContrato, String tipoServicoNegocioTecnico, String queryConsulta, Integer idCategoriaServicoNegocio) throws Exception {
		Date dataAtual = UtilDatas.getDataAtual();

		if(queryConsulta == null) {
			queryConsulta = "";
		}

		queryConsulta = tratarQueryConsulta(queryConsulta);

		List parametros = new ArrayList();
		StringBuilder sql = new StringBuilder();

		sql.append("SELECT ");
		sql.append("	s.idServico, ");
		sql.append("	s.nomeServico ");
		sql.append("FROM ");
		sql.append("	servicocontrato as servcont ");
		sql.append("	INNER JOIN servico as s on servcont.idservico = s.idservico ");
		sql.append("WHERE ");

		if (CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.POSTGRESQL)) {
			sql.append("	(remove_acento(s.nomeServico) ilike remove_acento(?)) ");
		} else if (CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.SQLSERVER)) {
			sql.append("	UPPER(dbo.remove_acento(s.nomeServico)) like UPPER(dbo.remove_acento(?)) ");
		} else {
			sql.append("	UPPER(remove_acento(s.nomeServico)) like UPPER(remove_acento(?)) ");
		}
		parametros.add(queryConsulta);

		sql.append("	and s.tipoServico = ? ");
		parametros.add(tipoServicoNegocioTecnico);

		sql.append("	and s.statusServico = ? ");
		parametros.add(StatusServico.PRODUCAO.getIdentificador());

		sql.append("	and ((s.deleted IS NULL) OR (UPPER(s.deleted) = 'N')) ");
		sql.append("	and servcont.idcontrato = ? ");
		parametros.add(idContrato);
		sql.append("	and (servcont.datafim is null or servcont.datafim > ?) ");
		parametros.add(dataAtual);
		sql.append("	and ((servcont.deleted is null) or (upper(servcont.deleted) = 'N')) ");

		if(idCategoriaServicoNegocio != null){
			sql.append("and idcategoriaservico = ?");
			parametros.add(idCategoriaServicoNegocio);
		}

		sql.append(" ORDER BY ");
		sql.append("	s.nomeServico");

		List lista = this.execSQL(sql.toString(), parametros.toArray());

		List<String> listaRetorno = new ArrayList();
		listaRetorno.add("idServico");
		listaRetorno.add("nomeServico");

		return this.listConvertion(ServicoDTO.class, lista, listaRetorno);
	}

	/**
	 * Retorna sigla do servico pelo idOs.
	 *
	 * @param idOs
	 * @return
	 * @throws Exception
	 */
	public String retornaSiglaPorIdOs(Integer idOs) throws Exception {
		List lstParametros = new ArrayList();
		String sql = "SELECT servico.siglaabrev FROM servicocontrato";
		sql = sql + " INNER JOIN servico ON servico.idservico = servicocontrato.idservico";
		sql = sql + " INNER JOIN os ON os.idservicocontrato = servicocontrato.idservicocontrato";
		sql = sql + " WHERE os.idos = ? ";
		lstParametros.add(idOs);

		Object[] parametros = lstParametros.toArray();

		List lstDados = super.execSQL(sql, parametros);

		List fields = new ArrayList();
		fields.add("siglaAbrev");

		Collection<ServicoDTO> listDeFaturas = super.listConvertion(ServicoDTO.class, lstDados, fields);

		if (listDeFaturas != null && !listDeFaturas.isEmpty()) {
			for (ServicoDTO servico : listDeFaturas) {
				if (servico.getSiglaAbrev() != null && !servico.getSiglaAbrev().trim().equals("")) {
					return servico.getSiglaAbrev();
				} else {
					return "           -";
				}
			}
			return null;
		} else {
			return null;
		}

	}

	/**
	 * Retorna lista Serviço por nome.
	 *
	 * @return Collection
	 * @throws Exception
	 */
	public Collection findByNome(ServicoDTO servicoDTO) throws Exception {
		List condicao = new ArrayList();
		List ordenacao = new ArrayList();

		condicao.add(new Condition("nomeServico", "=", servicoDTO.getNomeServico()));
		ordenacao.add(new Order("nomeServico"));
		return super.findByCondition(condicao, ordenacao);
	}

	/**
	 * Método para retornar apenas os serviços referente a unidade do
	 * solicitante
	 *
	 * @author rodrigo.oliveira
	 * @param idServico
	 * @param idTipoDemanda
	 * @param idCategoria
	 * @return
	 * @throws Exception
	 */
	public Collection findByIdServicoAndIdTipoDemandaAndIdCategoria(Integer idServico, Integer idTipoDemanda, Integer idCategoria) throws Exception {
		List condicao = new ArrayList();
		List ordenacao = new ArrayList();
		condicao.add(new Condition("idTipoDemandaServico", "=", idTipoDemanda));
		if (idCategoria != null) {
			condicao.add(new Condition("idCategoriaServico", "=", idCategoria));
		}
		condicao.add(new Condition("idServico", "=", idServico));
		ordenacao.add(new Order("nomeServico"));
		return super.findByCondition(condicao, ordenacao);
	}

	public ServicoDTO findByIdServico(Integer idServico) throws Exception {

		List parametro = new ArrayList();
		List fields = new ArrayList();
		List list = new ArrayList();
		String sql = "select nomeservico, nomecategoriaservico, idServico, dispportal, nomeportal, descricaoportal, imagemportal from servico "
				+ "inner join categoriaservico on servico.idcategoriaservico = categoriaservico.idcategoriaservico "
				+ "where servico.deleted is null and servico.idServico = ? order by nomecategoriaservico";
		parametro.add(idServico);
		list = this.execSQL(sql, parametro.toArray());
		fields.add("nomeServico");
		fields.add("nomeCategoriaServico");
		fields.add("idServico");
		fields.add("dispPortal");
		fields.add("nomePortal");
		fields.add("descricaoPortal");
		fields.add("imagemPortal");

		if (list != null && !list.isEmpty()) {
			return (ServicoDTO) this.listConvertion(getBean(), list, fields).get(0);
		} else {
			return null;
		}
	}

	public Collection<ServicoDTO> findByServico(Integer idServico) throws Exception {
		List parametro = new ArrayList();
		List fields = new ArrayList();
		List list = new ArrayList();
		String sql = "   select nometiposervico, nomeservico, nomecategoriaservico, idServico from servico " + "inner join tiposervico  on servico.idtiposervico = tiposervico.idtiposervico "
				+ "inner join categoriaservico on servico.idcategoriaservico = categoriaservico.idcategoriaservico "
				+ "where servico.deleted is null and situacao = 'A' and servico.idServico = ? and (dispportal = 'Y' or dispportal = 'S') order by nomecategoriaservico";
		parametro.add(idServico);
		list = this.execSQL(sql, parametro.toArray());
		fields.add("nomeTipoServico");
		fields.add("nomeServico");
		fields.add("nomeCategoriaServico");
		fields.add("idServico");
		if (list != null && !list.isEmpty()) {
			return this.listConvertion(getBean(), list, fields);
		} else {
			return null;
		}
	}

	public Collection<ServicoDTO> findByServico(Integer idServico, String nome) throws Exception {
		List parametro = new ArrayList();
		List fields = new ArrayList();
		List list = new ArrayList();
		String sql = "   select nometiposervico, nomeservico, nomecategoriaservico, idServico from servico " + "inner join tiposervico  on servico.idtiposervico = tiposervico.idtiposervico "
				+ "inner join categoriaservico on servico.idcategoriaservico = categoriaservico.idcategoriaservico "
				+ "where servico.deleted is null and situacao = 'A' and servico.idServico = ? and (dispportal = 'Y' or dispportal = 'S') and servico.nomeservico like '%" + nome
				+ "%' order by nomecategoriaservico";
		parametro.add(idServico);
		list = this.execSQL(sql, parametro.toArray());
		fields.add("nomeTipoServico");
		fields.add("nomeServico");
		fields.add("nomeCategoriaServico");
		fields.add("idServico");
		if (list != null && !list.isEmpty()) {
			return this.listConvertion(getBean(), list, fields);
		} else {
			return null;
		}

	}
	public Collection listAtivos() throws Exception {
		List condicao = new ArrayList();
		List ordenacao = new ArrayList();
		condicao.add(new Condition("deleted", "IS", null));
		ordenacao.add(new Order("nomeServico"));
		return super.findByCondition(condicao, ordenacao);
	}

	public Collection<ServicoDTO> findByIdTemplate(Integer idTemplate) throws Exception {
		Collection<ServicoDTO> resultado = new ArrayList<ServicoDTO>();
		if (idTemplate != null) {
			List condicao = new ArrayList();
			List ordenacao = new ArrayList();

			condicao.add(new Condition("idTemplateSolicitacao", " = ", idTemplate));
			condicao.add(new Condition(Condition.OR, "idTemplateAcompanhamento", " = ", idTemplate));
			resultado = super.findByCondition(condicao, ordenacao);
		}
		return resultado;
	}

	public ServicoDTO findById(Integer parm) throws Exception {
		List condicao = new ArrayList();
		List ordenacao = new ArrayList();
		condicao.add(new Condition("idServico", "=", parm));
		ArrayList<ServicoDTO> lista = (ArrayList<ServicoDTO>) super.findByCondition(condicao, ordenacao);
		return lista==null||lista.size()<=0?new ServicoDTO():lista.get(0);
	}

	/**
	 * Retorna uma lista de servicos ativos que ainda não foram adicionados a
	 * este contrato
	 *
	 * @param servicoDto
	 * @return
	 * @throws Exception
	 * @author thays.araujo
	 */
	public Collection<ServicoDTO> listAtivosDiferenteContrato(ServicoDTO servicoDto) throws Exception{

		StringBuilder sql = new StringBuilder();
		List parametro = new ArrayList();
		List listRetorno = new ArrayList();

		sql.append("SELECT  servico.idservico,servico.nomeServico,servico.detalheServico FROM  servico ");

		if(servicoDto.getIdContrato()!=null){
			sql.append("WHERE servico.idservico not in(SELECT idservico FROM servicocontrato  WHERE (servicocontrato.idcontrato = ? or servicocontrato.idcontrato is null)) AND (servico.deleted IS NULL or servico.deleted = 'N') ");
			parametro.add(servicoDto.getIdContrato());
		}else{
			sql.append(" WHERE servico.deleted IS NULL or servico.deleted = 'N' ");
		}
		sql.append(" ORDER BY servico.nomeServico");
		listRetorno.add("idServico");
		listRetorno.add("nomeServico");
		listRetorno.add("detalheServico");
		List list = execSQL(sql.toString(), parametro.toArray());
		if (list != null && !list.isEmpty()) {
			Collection<ServicoDTO> listAtivosDiferenteContrato = this.listConvertion(ServicoDTO.class, list, listRetorno);
			return listAtivosDiferenteContrato;
		}
		return null;
	}

	public Integer calculaTotalPaginas(Integer itensPorPagina) throws PersistenceException {
		List parametro = new ArrayList();
		StringBuilder sql = new StringBuilder();

        sql.append(" select COUNT(*) ");
        sql.append(" from servico ");

        List lista = new ArrayList();
        lista = this.execSQL(sql.toString(), parametro.toArray());

        Long totalLinhaLong = 0l;
        Long totalPagina = 0l;
        Integer total = 0;
        BigDecimal totalLinhaBigDecimal;
        Integer totalLinhaInteger;
        int intLimite = itensPorPagina;
        if(lista != null){
        	Object[] totalLinha = (Object[]) lista.get(0);
        	if(totalLinha != null && totalLinha.length > 0){
        		if (CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.POSTGRESQL) || CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.MYSQL)) {
        			totalLinhaLong = (Long) totalLinha[0];
        		}
        		if (CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.ORACLE)) {
        			totalLinhaBigDecimal = (BigDecimal) totalLinha[0];
        			totalLinhaLong = totalLinhaBigDecimal.longValue();
        		}
        		if (CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.SQLSERVER)) {
        			totalLinhaInteger = (Integer) totalLinha[0];
        			totalLinhaLong = Long.valueOf(totalLinhaInteger);
        		}
        	}
        }

        if (totalLinhaLong > 0) {
        	totalPagina = totalLinhaLong / intLimite;
        	if(totalLinhaLong % intLimite != 0){
        		totalPagina = totalPagina + 1;
        	}
        }
        total = Integer.valueOf(totalPagina.toString());

        return total;
	}

	public Collection<ServicoDTO> paginacaoServico(Integer paginaAtual, Integer itensPorPagina) throws PersistenceException {

		List parametro = new ArrayList();
		List listRetorno = new ArrayList();
		StringBuilder sql = new StringBuilder();

		if (CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.SQLSERVER)) {
			sql.append(" ;WITH TabelaTemporaria AS ( ");
		}

		sql.append(" SELECT ");
		sql.append("	idServico, ");
		sql.append("	idTipoServico, ");
		sql.append("	dataInicio, ");
		sql.append("	nomeServico ");

        if (CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.SQLSERVER)) {
        	sql.append(" , ROW_NUMBER() OVER (ORDER BY servico.nomeServico) AS Row ");
    	}
        sql.append(" FROM servico ");


        if (CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.POSTGRESQL)) {
        	sql.append(" ORDER BY servico.nomeServico");
        	Integer pgTotal = paginaAtual * itensPorPagina;
        	paginaAtual = pgTotal - itensPorPagina;
        	sql.append(" LIMIT " + itensPorPagina + " OFFSET " +paginaAtual);
        }

        if (CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.MYSQL)){
        	sql.append(" ORDER BY servico.nomeServico");
        	Integer pgTotal = paginaAtual * itensPorPagina;
        	paginaAtual = pgTotal - itensPorPagina;
        	sql.append(" LIMIT " +paginaAtual+ ", "+itensPorPagina);
        }

        if (CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.SQLSERVER)){
        	Integer quantidadePaginator2 = new Integer(0);
        	if (paginaAtual > 0) {
        		quantidadePaginator2 = itensPorPagina * paginaAtual;
        		paginaAtual = paginaAtual * itensPorPagina - itensPorPagina;
        	}else{
        		quantidadePaginator2 = itensPorPagina;
        		paginaAtual = 0;
        	}
        	sql.append(" ) SELECT * FROM TabelaTemporaria WHERE Row> "+paginaAtual+" and Row<"+(quantidadePaginator2+1)+" ");
        }

        String sqlOracle = "";
        if (CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.ORACLE)){
        	Integer quantidadePaginator2 = new Integer(0);
        	if (paginaAtual > 1) {
        		quantidadePaginator2 = itensPorPagina * paginaAtual;
        		paginaAtual = paginaAtual * itensPorPagina - itensPorPagina;
        		paginaAtual = paginaAtual + 1;
        	}else{
        		quantidadePaginator2 = itensPorPagina;
        		paginaAtual = 0;
        	}
        	int intInicio = paginaAtual;
        	int intLimite = quantidadePaginator2;
        	sqlOracle = paginacaoOracle(sql.toString(), intInicio, intLimite);
        }

        List lista = new ArrayList();

        if (CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.ORACLE)){
        	lista = this.execSQL(sqlOracle, parametro.toArray());
    	}else{
			/*
			 * Desenvolvedor: Euler Data: 28/10/2013 Horário: 10h57min ID
			 * Citsmart: 120393 Motivo/Comentário: Loop infinito ao selecionar
			 * um grupo sem empregados
			 */
    		lista = this.execSQL(sql.toString(), parametro.toArray());
    	}

        listRetorno.add("idServico");
        listRetorno.add("idTipoServico");
        listRetorno.add("dataInicio");
        listRetorno.add("nomeServico");

        if (lista != null && !lista.isEmpty()) {
			Collection<ServicoDTO> listServico = this.listConvertion(ServicoDTO.class, lista, listRetorno);
			return listServico;
		}

		return null;
	}

	public String paginacaoOracle(String strSQL, int intInicio, int intLimite) {
		String paginacaoOracle =
				"SELECT * FROM (SELECT PAGING.*, ROWNUM PAGING_RN FROM" +
				" (" + strSQL + " order by servico.nomeServico) PAGING WHERE (ROWNUM <= " + intLimite + "))" +
				" WHERE (PAGING_RN >= " + intInicio + ") ";
		return paginacaoOracle;
	}

	public Collection<ServicoDTO> findByIdPortfolioServico(Integer idPortfolioServico) throws Exception {
		StringBuilder sb = new StringBuilder("SELECT ");
		sb.append(getNamesFieldsStr("serv") + " ");
		sb.append("FROM ");
		sb.append("	servico serv ");
		sb.append("WHERE ");
		sb.append("	(upper(serv.deleted)  = 'N' or serv.deleted is null) ");
		sb.append("	and serv.idportfolioservico = " + idPortfolioServico + " ");
		sb.append("ORDER BY ");
		sb.append("	serv.idPortfolioServico");

		List lista = this.execSQL(sb.toString(), null);
		List<String> listRetorno = this.getListNamesFieldClass();
		List<ServicoDTO> result = this.engine.listConvertion(getBean(), lista, listRetorno);

		if (result == null || result.isEmpty()) {
			return null;
	}

		return result;
	}

	public List<ServicoDTO> listServicosDeNegocioPortal(Integer idPortfolio, Collection<ContratoDTO> contratosDTO, String categoriaPortfolio, String filtroServico, String ordenarPor, String ascDesc) throws PersistenceException {
		StringBuilder selectQueryPiece = new StringBuilder("SELECT DISTINCT ");
		selectQueryPiece.append("	servico.idServico, ");
		selectQueryPiece.append("	servico.nomeServico, ");
		selectQueryPiece.append("	servico.faseServico, ");
		selectQueryPiece.append("	servico.statusServico, ");
		selectQueryPiece.append("	servico.criticidadeServico, ");
		selectQueryPiece.append("	servico.dispportal, ");
		selectQueryPiece.append("	servico.nomeportal, ");
		if (CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.SQLSERVER)) {
			selectQueryPiece.append("	convert(varchar(max),servico.descricaoportal), ");
		}else{
			selectQueryPiece.append("	servico.descricaoportal, ");
		}
		selectQueryPiece.append("	servico.imagemportal ");

		StringBuilder orderBy = new StringBuilder("ORDER BY ");

		StringBuilder fromWhereQueryPiece = new StringBuilder("FROM ");
		fromWhereQueryPiece.append("	servico JOIN servicocontrato ON servico.idservico = servicocontrato.idservico ");
		fromWhereQueryPiece.append("WHERE ");
		fromWhereQueryPiece.append(getFiltroPorCategoriaPortfolio(categoriaPortfolio));
		fromWhereQueryPiece.append(" and (upper(servico.deleted)  = 'N' or servico.deleted is null) ");
		fromWhereQueryPiece.append(" and servico.idPortfolioServico = " + idPortfolio + " ");
		fromWhereQueryPiece.append(" and servico.tipoServico = '" + TipoServico.SERVICO_NEGOCIO.getIdentificador() + "' ");
		fromWhereQueryPiece.append(" and servico.dispportal = 'S' ");

		if (contratosDTO != null && !contratosDTO.isEmpty()) {
			fromWhereQueryPiece.append(" and servicocontrato.idcontrato in (");

			int count = 0;
			for (ContratoDTO contratoDTO : contratosDTO) {
				fromWhereQueryPiece.append((count == 0 ? "" : ", ") + contratoDTO.getIdContrato());
				count++;
			}

			fromWhereQueryPiece.append(") ");
		}

		if (UtilStrings.isNotVazio(filtroServico)) {
			try {
				Integer idCatalogoServico = Integer.parseInt(filtroServico);
				fromWhereQueryPiece.append(" and (servico.idServico = " + idCatalogoServico + " ");
				fromWhereQueryPiece.append(" or upper(servico.nomeportal) like upper('%" + filtroServico + "%')) ");
			} catch (Exception e) {
				fromWhereQueryPiece.append(" and upper(servico.nomeportal) like upper('%" + filtroServico + "%') ");
			}
		}

		if (UtilStrings.isNotVazio(ordenarPor)) {
			orderBy.append(ordenarPor).append(" ").append(ascDesc);
		} else {
			orderBy.append("nomeServico");
		}

		List lista = this.execSQL(selectQueryPiece.append(fromWhereQueryPiece).append(orderBy).toString(), null);

		List<String> listRetorno = new ArrayList<String>();
		listRetorno.add("idServico");
		listRetorno.add("nomeServico");
		listRetorno.add("faseServico");
		listRetorno.add("statusServico");
		listRetorno.add("criticidadeServico");
		listRetorno.add("dispPortal");
		listRetorno.add("nomePortal");
		listRetorno.add("descricaoPortal");
		listRetorno.add("imagemPortal");

		List<ServicoDTO> result = this.engine.listConvertion(getBean(), lista, listRetorno);

		return result;
	}

	public Page<ServicoDTO> listServicosDeNegocio(Integer idPortfolio, String categoriaPortfolio, String filtroServico, String ordenarPor, String ascDesc, Pageable pageable, Boolean isTotalizacao) throws PersistenceException {
		Page<ServicoDTO> taskPage;

		StringBuilder selectQueryPiece = new StringBuilder("SELECT ");
		selectQueryPiece.append("	idServico, ");
		selectQueryPiece.append("	nomeServico, ");
		selectQueryPiece.append("	faseServico, ");
		selectQueryPiece.append("	statusServico, ");
		selectQueryPiece.append("	criticidadeServico ");

		StringBuilder orderBy = new StringBuilder("ORDER BY ");

		StringBuilder fromWhereQueryPiece = new StringBuilder("FROM ");
		fromWhereQueryPiece.append("	servico ");
		fromWhereQueryPiece.append("WHERE ");
		fromWhereQueryPiece.append(getFiltroPorCategoriaPortfolio(categoriaPortfolio));
		fromWhereQueryPiece.append(" and (upper(deleted)  = 'N' or deleted is null) ");
		fromWhereQueryPiece.append(" and idPortfolioServico = " + idPortfolio);
		fromWhereQueryPiece.append(" and tipoServico = '" + TipoServico.SERVICO_NEGOCIO.getIdentificador() + "'");

		if (UtilStrings.isNotVazio(filtroServico)) {
			try {
				Integer idCatalogoServico = Integer.parseInt(filtroServico);
				fromWhereQueryPiece.append(" and (idServico = " + idCatalogoServico + " ");
				fromWhereQueryPiece.append(" or upper(nomeServico) like upper('%" + filtroServico + "%')) ");
			} catch(Exception e) {
				fromWhereQueryPiece.append(" and upper(nomeServico) like upper('%" + filtroServico + "%') ");
			}
		}

		if (UtilStrings.isNotVazio(ordenarPor)) {
			orderBy.append(ordenarPor).append(" ").append(ascDesc);
		} else {
			orderBy.append("nomeServico");
		}

		String sql = "";
		List lista = new ArrayList();
		if (isTotalizacao) {
			final StringBuilder sqlCount = this.countQueryPiece(fromWhereQueryPiece);
			final Long totalElements = this.countElements(sqlCount.toString(), null);
			final List<ServicoDTO> result = new ArrayList<ServicoDTO>();
			taskPage = this.makePage(result, pageable, totalElements);

		} else {
			if (MAIN_SGBD.equals(DataBase.MSSQLSERVER)) {
				sql = PagingQueryUtil.constructsSQLServerPagingPiece(pageable, selectQueryPiece.toString(), orderBy.toString(), fromWhereQueryPiece.toString());
			} else {
				selectQueryPiece.append(fromWhereQueryPiece);
				selectQueryPiece.append(orderBy);
				sql = PagingQueryUtil.concatPagingPieceOnQuery(pageable, selectQueryPiece.toString(), MAIN_SGBD);
			}

			lista = this.execSQL(sql, null);

			List<String> listRetorno = new ArrayList<String>();
			listRetorno.add("idServico");
			listRetorno.add("nomeServico");
			listRetorno.add("faseServico");
			listRetorno.add("statusServico");
			listRetorno.add("criticidadeServico");

			List<ServicoDTO> result = this.engine.listConvertion(getBean(), lista, listRetorno);
			taskPage = new PageImpl<ServicoDTO>(result, pageable, 1L);
		}

		return taskPage;
	}

	public Page<ServicoDTO> listServicosRequisicaoIncidente(String tipoServico, Integer idPortfolio, Integer idServicoRelacionado, String filtroServico, Pageable pageable, Boolean isTotalizacao) throws PersistenceException {
		Page<ServicoDTO> taskPage;

		StringBuilder selectQueryPiece = new StringBuilder("SELECT ");
		selectQueryPiece.append("	serv.idServico, ");
		selectQueryPiece.append("	serv.nomeServico ");

		StringBuilder orderBy = new StringBuilder("ORDER BY serv.nomeServico ");

		StringBuilder fromWhereQueryPiece = new StringBuilder("FROM ");
		fromWhereQueryPiece.append("	servico serv ");
		fromWhereQueryPiece.append("	inner join servicoautorelacionamento auto on serv.idservico = auto.idservico ");
		fromWhereQueryPiece.append("WHERE ");
		fromWhereQueryPiece.append(" (upper(serv.deleted)  = 'N' or serv.deleted is null) ");
		fromWhereQueryPiece.append(" and serv.idPortfolioServico = " + idPortfolio);
		fromWhereQueryPiece.append(" and serv.tipoServico = '" + tipoServico + "' ");
		fromWhereQueryPiece.append(" and auto.idServicoRelacionado = " + idServicoRelacionado);
		fromWhereQueryPiece.append(" and auto.dataFim is null ");

		if (UtilStrings.isNotVazio(filtroServico)) {
			try {
				Integer idServico = Integer.parseInt(filtroServico);
				fromWhereQueryPiece.append(" and (serv.idServico = " + idServico + " ");
				fromWhereQueryPiece.append(" or upper(serv.nomeServico) like upper('%" + filtroServico + "%')) ");
			} catch(Exception e) {
				fromWhereQueryPiece.append(" and upper(serv.nomeServico) like upper('%" + filtroServico + "%') ");
			}
		}

		String sql = "";
		List lista = new ArrayList();
		if (isTotalizacao) {
			final StringBuilder sqlCount = this.countQueryPiece(fromWhereQueryPiece);
			final Long totalElements = this.countElements(sqlCount.toString(), null);
			final List<ServicoDTO> result = new ArrayList<ServicoDTO>();
			taskPage = this.makePage(result, pageable, totalElements);

		} else {
			if (MAIN_SGBD.equals(DataBase.MSSQLSERVER)) {
				sql = PagingQueryUtil.constructsSQLServerPagingPiece(pageable, selectQueryPiece.toString(), orderBy.toString(), fromWhereQueryPiece.toString());
			} else {
				selectQueryPiece.append(fromWhereQueryPiece);
				selectQueryPiece.append(orderBy);
				sql = PagingQueryUtil.concatPagingPieceOnQuery(pageable, selectQueryPiece.toString(), MAIN_SGBD);
			}

			lista = this.execSQL(sql, null);

			List<String> listRetorno = new ArrayList<String>();
			listRetorno.add("idServico");
			listRetorno.add("nomeServico");

			List<ServicoDTO> result = this.engine.listConvertion(getBean(), lista, listRetorno);
			taskPage = new PageImpl<ServicoDTO>(result, pageable, 1L);
		}

		return taskPage;
	}

	public List<ServicoDTO> listServicosVinculadosAoContratoPortal(List<TipoServico> tiposServico, Integer idPortfolio, Integer idServicoRelacionado, Collection<ContratoDTO> contratosDTO, String filtroServico) throws PersistenceException {
		StringBuilder selectQueryPiece = new StringBuilder("SELECT DISTINCT ");
		selectQueryPiece.append("	serv.idServico, ");
		selectQueryPiece.append("	serv.nomeServico, ");
		selectQueryPiece.append("	serv.faseServico, ");
		selectQueryPiece.append("	serv.dispportal, ");
		selectQueryPiece.append("	serv.nomeportal, ");
		if (CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.SQLSERVER)) {
			selectQueryPiece.append("	convert(varchar(max),serv.descricaoportal), ");
		}else{
			selectQueryPiece.append("	serv.descricaoportal, ");
		}
		selectQueryPiece.append("	serv.imagemportal ");

		StringBuilder orderBy = new StringBuilder("ORDER BY serv.nomeServico ");

		StringBuilder fromWhereQueryPiece = new StringBuilder("FROM ");
		fromWhereQueryPiece.append("	servico serv ");
		fromWhereQueryPiece.append("	inner join servicoautorelacionamento auto on serv.idservico = auto.idservico ");
		fromWhereQueryPiece.append("	inner join servicocontrato servcontrato on serv.idservico = servcontrato.idservico ");
		fromWhereQueryPiece.append("WHERE ");
		fromWhereQueryPiece.append(" (upper(serv.deleted)  = 'N' or serv.deleted is null) ");
		fromWhereQueryPiece.append(" and (upper(servcontrato.deleted)  = 'N' or servcontrato.deleted is null) ");
		fromWhereQueryPiece.append(" and auto.idservicorelacionado = " + idServicoRelacionado);
		fromWhereQueryPiece.append(" and (serv.idportfolioservico = " + idPortfolio + " OR serv.idportfolioservico is null) ");
		fromWhereQueryPiece.append(" and auto.dataFim is null ");
		fromWhereQueryPiece.append(" and (serv.dispportal = 'Y' or serv.dispportal = 'S') ");

		if (tiposServico != null && !tiposServico.isEmpty()) {
			fromWhereQueryPiece.append(" and tipoServico in (");

			int count = 0;
			for (TipoServico tipoServico : tiposServico) {
				fromWhereQueryPiece.append((count == 0 ? "'" : ", '") + tipoServico.getIdentificador() + "'");
				count++;
			}

			fromWhereQueryPiece.append(") ");
		}

		if (contratosDTO != null && !contratosDTO.isEmpty()) {
			fromWhereQueryPiece.append(" and servcontrato.idcontrato in (");

			int count = 0;
			for (ContratoDTO contratoDTO : contratosDTO) {
				fromWhereQueryPiece.append((count == 0 ? "" : ", ") + contratoDTO.getIdContrato());
				count++;
			}

			fromWhereQueryPiece.append(") ");
		}

		if (UtilStrings.isNotVazio(filtroServico)) {
			try {
				Integer idServico = Integer.parseInt(filtroServico);
				fromWhereQueryPiece.append(" and (serv.idServico = " + idServico + " ");
				fromWhereQueryPiece.append(" or upper(serv.nomeportal) like upper('%" + filtroServico + "%')) ");
			} catch (Exception e) {
				fromWhereQueryPiece.append(" and upper(serv.nomeportal) like upper('%" + filtroServico + "%') ");
			}
		}

		List lista = this.execSQL(selectQueryPiece.append(fromWhereQueryPiece).append(orderBy).toString(), null);

		List<String> listRetorno = new ArrayList<String>();
		listRetorno.add("idServico");
		listRetorno.add("nomeServico");
		listRetorno.add("faseServico");
		listRetorno.add("dispPortal");
		listRetorno.add("nomePortal");
		listRetorno.add("descricaoPortal");
		listRetorno.add("imagemPortal");

		List<ServicoDTO> result = this.engine.listConvertion(getBean(), lista, listRetorno);

		return result;
	}

	public Page<ServicoDTO> listServicosVinculadosAoContrato(String tipoServico, Integer idPortfolio, Integer idServicoRelacionado, Integer idContrato, String filtroServico, Pageable pageable, Boolean isTotalizacao) throws PersistenceException {
		Page<ServicoDTO> taskPage;

		StringBuilder selectQueryPiece = new StringBuilder("SELECT ");
		selectQueryPiece.append("	serv.idServico, ");
		selectQueryPiece.append("	serv.nomeServico, ");
		selectQueryPiece.append("	serv.faseServico ");

		StringBuilder orderBy = new StringBuilder("ORDER BY serv.nomeServico ");

		StringBuilder fromWhereQueryPiece = new StringBuilder("FROM ");
		fromWhereQueryPiece.append("	servico serv ");
		fromWhereQueryPiece.append("	inner join servicoautorelacionamento auto on serv.idservico = auto.idservico ");
		fromWhereQueryPiece.append("	inner join servicocontrato servcontrato on serv.idservico = servcontrato.idservico ");
		fromWhereQueryPiece.append("WHERE ");
		fromWhereQueryPiece.append(" (upper(serv.deleted)  = 'N' or serv.deleted is null) ");
		fromWhereQueryPiece.append(" and (upper(servcontrato.deleted)  = 'N' or servcontrato.deleted is null) ");
		fromWhereQueryPiece.append(" and auto.idservicorelacionado = " + idServicoRelacionado);
		fromWhereQueryPiece.append(" and servcontrato.idcontrato = " + idContrato);
		fromWhereQueryPiece.append(" and (serv.idportfolioservico = " + idPortfolio + " OR serv.idportfolioservico is null) ");
		fromWhereQueryPiece.append(" and tipoServico = '" + tipoServico + "' ");
		fromWhereQueryPiece.append(" and auto.dataFim is null ");

		if (UtilStrings.isNotVazio(filtroServico)) {
			try {
				Integer idServico = Integer.parseInt(filtroServico);
				fromWhereQueryPiece.append(" and (serv.idServico = " + idServico + " ");
				fromWhereQueryPiece.append(" or upper(nomeServico) like upper('%" + filtroServico + "%')) ");
			} catch(Exception e) {
				fromWhereQueryPiece.append(" and upper(nomeServico) like upper('%" + filtroServico + "%') ");
			}
		}

		String sql = "";
		List lista = new ArrayList();
		if (isTotalizacao) {
			final StringBuilder sqlCount = this.countQueryPiece(fromWhereQueryPiece);
			final Long totalElements = this.countElements(sqlCount.toString(), null);
			final List<ServicoDTO> result = new ArrayList<ServicoDTO>();
			taskPage = this.makePage(result, pageable, totalElements);

		} else {
			if (pageable != null) {
				if (MAIN_SGBD.equals(DataBase.MSSQLSERVER)) {
					sql = PagingQueryUtil.constructsSQLServerPagingPiece(pageable, selectQueryPiece.toString(), orderBy.toString(), fromWhereQueryPiece.toString());
				} else {
					selectQueryPiece.append(fromWhereQueryPiece);
					selectQueryPiece.append(orderBy);
					sql = PagingQueryUtil.concatPagingPieceOnQuery(pageable, selectQueryPiece.toString(), MAIN_SGBD);
				}
			} else {
				sql = selectQueryPiece.toString() + fromWhereQueryPiece.toString() + orderBy.toString();
			}

			lista = this.execSQL(sql, null);

			List<String> listRetorno = new ArrayList<String>();
			listRetorno.add("idServico");
			listRetorno.add("nomeServico");
			listRetorno.add("faseServico");

			List<ServicoDTO> result = this.engine.listConvertion(getBean(), lista, listRetorno);
			taskPage = new PageImpl<ServicoDTO>(result, pageable, 1L);
		}

		return taskPage;
	}

	public Page<ServicoDTO> listServicosDeApoio(Integer idPortfolio, Integer idServicoRelacionado, String filtroServico, Pageable pageable, Boolean isTotalizacao) throws PersistenceException {
		Page<ServicoDTO> taskPage;

		StringBuilder selectQueryPiece = new StringBuilder("SELECT ");
		selectQueryPiece.append("	serv.idServico, ");
		selectQueryPiece.append("	serv.nomeServico, ");
		selectQueryPiece.append("	serv.idPortfolioServico, ");
		selectQueryPiece.append("	serv.faseServico, ");
		selectQueryPiece.append("	serv.statusServico ");

		StringBuilder orderBy = new StringBuilder("ORDER BY serv.nomeServico ");

		StringBuilder fromWhereQueryPiece = new StringBuilder("FROM ");
		fromWhereQueryPiece.append("	servico serv ");
		fromWhereQueryPiece.append("	inner join servicoautorelacionamento auto on serv.idservico = auto.idservico ");
		fromWhereQueryPiece.append("WHERE ");
		fromWhereQueryPiece.append(" (upper(serv.deleted)  = 'N' or serv.deleted is null) ");
		fromWhereQueryPiece.append(" and serv.idPortfolioServico = " + idPortfolio);
		fromWhereQueryPiece.append(" and serv.tipoServico = '" + TipoServico.SERVICO_APOIO.getIdentificador() + "' ");
		fromWhereQueryPiece.append(" and auto.idServicoRelacionado = " + idServicoRelacionado);
		fromWhereQueryPiece.append(" and auto.dataFim is null ");

		if (UtilStrings.isNotVazio(filtroServico)) {
			try {
				Integer idServico = Integer.parseInt(filtroServico);
				fromWhereQueryPiece.append(" and (serv.idServico = " + idServico + " ");
				fromWhereQueryPiece.append(" or upper(serv.nomeServico) like upper('%" + filtroServico + "%')) ");
			} catch(Exception e) {
				fromWhereQueryPiece.append(" and upper(serv.nomeServico) like upper('%" + filtroServico + "%') ");
			}
		}

		String sql = "";
		List lista = new ArrayList();
		if (isTotalizacao) {
			final StringBuilder sqlCount = this.countQueryPiece(fromWhereQueryPiece);
			final Long totalElements = this.countElements(sqlCount.toString(), null);
			final List<ServicoDTO> result = new ArrayList<ServicoDTO>();
			taskPage = this.makePage(result, pageable, totalElements);

		} else {
			if (pageable != null) {
				if (MAIN_SGBD.equals(DataBase.MSSQLSERVER)) {
					sql = PagingQueryUtil.constructsSQLServerPagingPiece(pageable, selectQueryPiece.toString(), orderBy.toString(), fromWhereQueryPiece.toString());
				} else {
					selectQueryPiece.append(fromWhereQueryPiece);
					selectQueryPiece.append(orderBy);
					sql = PagingQueryUtil.concatPagingPieceOnQuery(pageable, selectQueryPiece.toString(), MAIN_SGBD);
				}
			} else {
				sql = selectQueryPiece.toString() + fromWhereQueryPiece.toString() + orderBy.toString();
			}

			lista = this.execSQL(sql, null);

			List<String> listRetorno = new ArrayList<String>();
			listRetorno.add("idServico");
			listRetorno.add("nomeServico");
			listRetorno.add("idPortfolioServico");
			listRetorno.add("faseServico");
			listRetorno.add("statusServico");

			List<ServicoDTO> result = this.engine.listConvertion(getBean(), lista, listRetorno);
			taskPage = new PageImpl<ServicoDTO>(result, pageable, 1L);
		}

		return taskPage;
	}

	public Collection<ServicoDTO> listServicosPorImagemItemConfiguracao(Integer idItemConfiguracao) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select distinct serv.idservico, serv.nomeservico from imagemitemconfiguracao img inner join servico serv on img.idservico = serv.idservico where iditemconfiguracao = ?");

		List parametro = new ArrayList();
		parametro.add(idItemConfiguracao);

		List listRetorno = new ArrayList();
		listRetorno.add("idServico");
		listRetorno.add("nomeServico");

		List list = execSQL(sql.toString(), parametro.toArray());
		if (list != null && !list.isEmpty()) {
			return this.listConvertion(ServicoDTO.class, list, listRetorno);
		}

		return null;
	}

	public Long getTotalRegistrosPorCategoriaPortfolio(Integer idPortfolio, String categoriaPortfolio) throws PersistenceException {
		StringBuilder fromWhereQueryPiece = new StringBuilder("FROM ");
		fromWhereQueryPiece.append("	servico ");
		fromWhereQueryPiece.append("WHERE ");
		fromWhereQueryPiece.append(getFiltroPorCategoriaPortfolio(categoriaPortfolio));
		fromWhereQueryPiece.append(" and (upper(deleted)  = 'N' or deleted is null) ");
		fromWhereQueryPiece.append(" and idPortfolioServico = " + idPortfolio);
		fromWhereQueryPiece.append(" and tipoServico = '" + TipoServico.SERVICO_NEGOCIO.getIdentificador() + "'");

		final StringBuilder sqlCount = this.countQueryPiece(fromWhereQueryPiece);
		return this.countElements(sqlCount.toString(), null);
	}

	private String getFiltroPorCategoriaPortfolio(String categoriaPortfolio) {
		return getFiltroPorCategoriaPortfolio(categoriaPortfolio, "");
	}

	private String getFiltroPorCategoriaPortfolio(String categoriaPortfolio, String prefixoCampo) {
		final StringBuilder filtro = new StringBuilder();

		if (UtilStrings.isNotVazio(prefixoCampo)) {
			filtro.append(prefixoCampo);
			filtro.append(".");
		}

		filtro.append("statusServico in (");

		if (CategoriaPortfolio.FUNIL_SERVICOS.getIdentificador().equalsIgnoreCase(categoriaPortfolio)) {
			filtro.append("'").append(StatusServico.REQUISITOS.getIdentificador()).append("',");
			filtro.append("'").append(StatusServico.DEFINICAO.getIdentificador()).append("',");
			filtro.append("'").append(StatusServico.ANALISE.getIdentificador()).append("',");
			filtro.append("'").append(StatusServico.APROVADO.getIdentificador()).append("',");
			filtro.append("'").append(StatusServico.TERMO_DE_ABERTURA.getIdentificador()).append("',");
			filtro.append("'").append(StatusServico.DESENHO.getIdentificador()).append("',");
			filtro.append("'").append(StatusServico.DESENVOLVIMENTO.getIdentificador()).append("',");
			filtro.append("'").append(StatusServico.CONSTRUCAO.getIdentificador()).append("',");
			filtro.append("'").append(StatusServico.TESTE.getIdentificador()).append("'");
		} else if (CategoriaPortfolio.CATALOGO_SERVICOS.getIdentificador().equalsIgnoreCase(categoriaPortfolio)) {
			filtro.append("'").append(StatusServico.LIBERACAO.getIdentificador()).append("',");
			filtro.append("'").append(StatusServico.PRODUCAO.getIdentificador()).append("'");
		} else if (CategoriaPortfolio.SERVICOS_OBSOLETOS.getIdentificador().equalsIgnoreCase(categoriaPortfolio)) {
			filtro.append("'").append(StatusServico.APOSENTANDO.getIdentificador()).append("',");
			filtro.append("'").append(StatusServico.APOSENTADO.getIdentificador()).append("'");
		}

		filtro.append(")");

		return filtro.toString();
	}

	public ServicoDTO recuperaServicoRelacionado(final Integer idContrato, final Integer idServico) throws PersistenceException {

		Date dataAtual = UtilDatas.getDataAtual();

		List parametros = new ArrayList();
		StringBuilder sql = new StringBuilder();

		sql.append("select ");
		sql.append("	servrelacionado.idServico, ");
		sql.append("	servrelacionado.nomeServico, ");
		sql.append("	servrelacionado.tipoServico ");
		sql.append("from ");
		sql.append("	servicocontrato as servcont ");
		sql.append("	inner join servico as serv on servcont.idservico = serv.idservico ");
		sql.append("	inner join servicoautorelacionamento auto on serv.idservico = auto.idservico ");
		sql.append("	inner join servico servrelacionado on auto.idservicorelacionado = servrelacionado.idservico ");
		sql.append("where ");
		sql.append("	((serv.deleted is null) or (upper(serv.deleted) = upper('n'))) ");
		sql.append("	and servcont.idcontrato = ? ");
		parametros.add(idContrato);
		sql.append("	and servcont.idservico = ? ");
		parametros.add(idServico);
		sql.append("	and (servcont.datafim is null or servcont.datafim > ?) ");
		parametros.add(dataAtual);
		sql.append("	and ((servcont.deleted is null) or (upper(servcont.deleted) = upper('n'))) ");
		sql.append("	and servrelacionado.statusServico = ? ");
		parametros.add(StatusServico.PRODUCAO.getIdentificador());
		sql.append("	and auto.datafim is null ");

		sql.append("order by ");
		sql.append("	serv.nomeServico");

		List lista = this.execSQL(sql.toString(), parametros.toArray());

		List<String> listaRetorno = new ArrayList();
		listaRetorno.add("idServico");
		listaRetorno.add("nomeServico");
		listaRetorno.add("tipoServico");

		Collection<ServicoDTO> colServico = this.listConvertion(ServicoDTO.class, lista, listaRetorno);

		if (colServico != null && !colServico.isEmpty()) {
			return colServico.iterator().next();
		}

		return new ServicoDTO();
	}

	public Collection findByNomeAndContratoAndTipoDemandaAndCategoria(Integer idTipoDemanda, Integer idContrato, Integer idCategoria, String nome) throws Exception {

		List fields = new ArrayList();
		Date dataAtual = UtilDatas.getDataAtual();
		if(nome == null) {
			nome = "";
		}

		// Incidente 193336: Removido trecho que remove caracteres especias do campo deixando que ele pesquise com caracteres especiais
		// No PostgreSQL existe uma funcao
		if (CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.POSTGRESQL)) {
			nome = UtilStrings.normalizaERemoveCaracteresEspeciais(nome);
		}
		nome = "%" + nome + "%";

		List parametros = new ArrayList();

		StringBuilder sql = new StringBuilder();
		sql.append("SELECT s.idServico, s.nomeServico ");
		if (idContrato!=null&&idContrato>0){
			sql.append("FROM servicocontrato as sc JOIN servico as s on ");
			if (CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.POSTGRESQL)) {
				sql.append("(UPPER(remove_acento(s.nomeServico)) ilike UPPER(remove_acento(?))) AND ");
			} else {
				sql.append("UPPER(s.nomeServico) like UPPER(?) AND ");
			}
			parametros.add(nome);
			if (idTipoDemanda!=null&&idTipoDemanda>0){
				sql.append("s.idTipoDemandaServico = ? AND ");
				parametros.add(idTipoDemanda);
			}
			if (idCategoria != null&&idCategoria>0) {
				sql.append("idCategoriaServico = ? AND ");
				parametros.add(idCategoria);
			}
			sql.append("((s.deleted IS NULL) OR (UPPER(s.deleted) = 'N')) AND ");
			sql.append("s.idservico = sc.idservico AND ");
			sql.append("sc.idcontrato = ? AND (sc.datafim is null OR sc.datafim > ?) AND ((sc.deleted IS NULL) OR (UPPER(sc.deleted) = 'N')) ");
			parametros.add(idContrato);
			parametros.add(dataAtual);
			sql.append("JOIN situacaoservico as ss on (ss.ativo = ").append(SimNao.SIM.getValorUmDois()).append(") AND ss.idsituacaoservico = s.idsituacaoservico ");
		} else {
			sql.append("FROM servico as s ");
			sql.append("JOIN situacaoservico as ss on ");
			if (CITCorporeUtil.SGBD_PRINCIPAL.toUpperCase().equals(SQLConfig.POSTGRESQL)) {
				sql.append("(UPPER(remove_acento(s.nomeServico)) ilike UPPER(remove_acento(?))) AND ");
			} else {
				sql.append("UPPER(s.nomeServico) like UPPER(?) AND ");
			}
			parametros.add(nome);
			if (idTipoDemanda!=null&&idTipoDemanda>0){
				sql.append("s.idTipoDemandaServico = ? AND ");
				parametros.add(idTipoDemanda);
			}
			if (idCategoria != null&&idCategoria>0) {
				sql.append("idCategoriaServico = ? AND ");
				parametros.add(idCategoria);
			}
			sql.append("((s.deleted IS NULL) OR (UPPER(s.deleted) = 'N')) AND ");

			sql.append("(ss.ativo = ").append(SimNao.SIM.getValorUmDois()).append(") AND ss.idsituacaoservico = s.idsituacaoservico ");
		}
		sql.append("ORDER BY s.nomeServico");

		List listaR = this.execSQL(sql.toString(), parametros.toArray());

		fields.add("idServico");
		fields.add("nomeServico");

		return this.listConvertion(ServicoDTO.class, listaR, fields);
	}

	public List<ServicoDTO> listServicoRelacionadoByItemConfiguracao(Integer idItemConfiguracao) throws Exception{
		StringBuilder sb = new StringBuilder();
		List parametros = new ArrayList();

		sb.append("SELECT DISTINCT ").append(getNamesFieldsStr("ser"));
		sb.append(" FROM mapanegocioitem mni ");
		sb.append(" LEFT JOIN servico ser ON mni.idserviconegocio = ser.idservico AND mni.iditemconfiguracao = ? AND ser.deleted IS NULL ");
		parametros.add(idItemConfiguracao);
		sb.append(" UNION " );
		sb.append("SELECT DISTINCT ").append(getNamesFieldsStr("ser"));
		sb.append(" FROM ImagemItemConfiguracao iic ");
		sb.append(" LEFT JOIN servico ser ON iic.idservico = ser.idservico AND iic.iditemconfiguracao = ? AND ser.deleted IS NULL ");
		parametros.add(idItemConfiguracao);

		List lista = this.execSQL(sb.toString(), parametros.toArray());

		List fields = new ArrayList<>(getFields());

		return this.listConvertion(getBean(), lista, fields);
	}

	public Collection<ServicoDTO> listServicosCombo(ServicoDTO dto) throws PersistenceException {
		StringBuilder sb = new StringBuilder();
		List parametros = new ArrayList();

		sb.append("SELECT ");
		sb.append(getNamesFieldsStr()).append(" ");
		sb.append("FROM ");
		sb.append("	servico ");
		sb.append("WHERE ");
		sb.append("	deleted is null ");

		if (!UtilNumbersAndDecimals.isNullOrZeroOrEmpty(dto.getIdPortfolioServico())) {
			sb.append("	and idPortfolioServico = ? ");
			parametros.add(dto.getIdPortfolioServico());
		}

		if (UtilStrings.isNotVazio(dto.getTipoServico())) {
			sb.append("	and tipoServico = ? ");
			parametros.add(dto.getTipoServico());
		} else {
			sb.append("	and tipoServico in (?, ?) ");
			parametros.add(TipoServico.SERVICO_APOIO.getIdentificador());
			parametros.add(TipoServico.SERVICO_NEGOCIO.getIdentificador());
		}

		sb.append("ORDER BY ");
		sb.append("	nomeServico ");

		List lista = this.execSQL(sb.toString(), parametros.toArray());

		return this.listConvertion(getBean(), lista, getListNamesFieldClass());
	}

	public Collection<ServicoDTO> listServicosPorFuncaoVital(Integer idFuncaoVital) throws PersistenceException {
		StringBuilder sb = new StringBuilder();
		List<Integer> parametros = new ArrayList<Integer>();
		List<String> retorno = new ArrayList<String>();

		sb.append("select distinct ");
		sb.append("	s.idServico, ");
		sb.append("	s.nomeServico ");
		sb.append("from ");
		sb.append("	servico s ");
		sb.append("	inner join servicoprocessonegocio spn on  s.idservico = spn.idservico ");
		sb.append("	inner join funcoesvitais fv on spn.idprocessonegocio = fv.idprocessonegocio ");
		sb.append("WHERE ");
		sb.append("	(fv.dataFim is null or fv.dataFim > {DATAATUAL}) ");
		sb.append("	and ((s.deleted IS NULL) OR (UPPER(s.deleted) = 'N')) ");
		sb.append("	and fv.idFuncaoVital = ?");

		parametros.add(idFuncaoVital);

		retorno.add("idServico");
		retorno.add("nomeServico");

		List<?> lista = this.execSQL(sb.toString(), parametros.toArray());

		return this.listConvertion(getBean(), lista, retorno);
	}

	public List getIdsServicosRelacionadosServicoContrato(String idsServicoContrato) throws PersistenceException {
		StringBuilder sql = new StringBuilder();
		List<Object> fields = new ArrayList<>();

		sql.append(" SELECT DISTINCT");
		sql.append("	s.idservico, ");
		sql.append("	s.nomeservico, ");
		sql.append("	s.tiposervico, ");
		sql.append("	sc.idcontrato ");
		sql.append(" FROM ");
		sql.append("	servicocontrato sc ");
		sql.append("	INNER JOIN servico s ON s.idservico = sc.idservico ");
		sql.append("	INNER JOIN servicoautorelacionamento auto ON sc.idservico = auto.idservicorelacionado ");
		sql.append("	INNER JOIN servicocontrato scr ON scr.idservico = auto.idservico ");
		sql.append(" WHERE scr.idservicocontrato  in (".concat(idsServicoContrato).concat(")"));

		List result = this.execSQL(sql.toString(), null);

		fields.add("idServico");
		fields.add("nomeServico");
		fields.add("tipoServico");
		fields.add("idContrato");

		return this.engine.listConvertion(getBean(), result, fields);
	}

	public ArvoreServico getHieraquiaServicoApoioImpactado(Integer idServicoApoio) throws Exception {
		ArvoreServico arvServicos = new ArvoreServico();

		ServicoDTO servico = new ServicoDTO();
		servico.setIdServico(idServicoApoio);
		servico = (ServicoDTO) this.restore(servico);

		arvServicos.adicionaNo(servico.getIdServico(), servico.getNomeServico(), servico.getTipoServico(), null);
		this.montaArvoreHierarquiaServicoApoioImpactado(servico.getIdServico(), arvServicos);

		return arvServicos;
	}

	private void montaArvoreHierarquiaServicoApoioImpactado(Integer idServico, ArvoreServico arvServicos) throws PersistenceException {
		List<ServicoDTO> listServicosAux = this.getServicoAndServicoRelacionado(idServico);

		for (ServicoDTO servicoAux : listServicosAux) {
			if (servicoAux.getIdServicoRelacionado() != null) {
				arvServicos.adicionaNo(servicoAux.getIdServicoRelacionado(), servicoAux.getNomeServicoRelacionado(), servicoAux.getTipoServicoRelacionado(), servicoAux.getIdServico());
				this.montaArvoreHierarquiaServicoApoioImpactado(servicoAux.getIdServicoRelacionado(), arvServicos);
			}
		}
	}


	public Collection<ServicoDTO> getServicosImpactadosByIdItemConfiguracao(Integer idItemConfiguracao) throws PersistenceException {
		StringBuilder sb = new StringBuilder();
		List<Integer> parametros = new ArrayList<>();
		List<String> retorno = new ArrayList<>();

		sb.append("	SELECT DISTINCT");
		sb.append("		servico.idServico,");
		sb.append("		servico.nomeServico,");
		sb.append("		servico.tipoServico");
		sb.append("	FROM");
		sb.append("		servico servico");
		sb.append("		LEFT JOIN imagemItemConfiguracao imagemItemConfiguracao");
		sb.append("			ON servico.idServico = imagemItemConfiguracao.idServico");
		sb.append("		LEFT JOIN mapaNegocioItem mapaNegocioItem");
		sb.append("			ON servico.idServico = mapaNegocioItem.idServicoNegocio");
		sb.append("	WHERE ");
		sb.append("		(imagemItemConfiguracao.idItemConfiguracao = ? or mapaNegocioItem.idItemConfiguracao = ?)");
		sb.append("		AND servico.tipoServico in ('A', 'N')");
		sb.append("	ORDER BY servico.tipoServico DESC");

		parametros.add(idItemConfiguracao);
		parametros.add(idItemConfiguracao);

		retorno.add("idServico");
		retorno.add("nomeServico");
		retorno.add("tipoServico");

		List<?> lista = this.execSQL(sb.toString(), parametros.toArray());

		if (lista != null && !lista.isEmpty()) {
			return this.listConvertion(getBean(), lista, retorno);
		}

		return Collections.emptyList();
	}

	private List<ServicoDTO> getServicoAndServicoRelacionado(Integer idServico) throws PersistenceException {
		StringBuilder sb = new StringBuilder();
		List<Integer> parametros = new ArrayList<>();
		List<String> retorno = new ArrayList<>();

		sb.append("	SELECT DISTINCT");
		sb.append("		servico.idServico,");
		sb.append("		servico.nomeServico,");
		sb.append("		servico.tipoServico,");
		sb.append("		servicoRelacionado.idServico,");
		sb.append("		servicoRelacionado.nomeServico,");
		sb.append("		servicoRelacionado.tipoServico");
		sb.append("	FROM");
		sb.append("		servico servico");
		sb.append("		LEFT JOIN servicoAutoRelacionamento servicoAutoRelacionamento");
		sb.append("			ON servico.idServico = servicoAutoRelacionamento.idServico");
		sb.append("		LEFT JOIN servico servicoRelacionado");
		sb.append("			ON servicoRelacionado.idServico = servicoAutoRelacionamento.idServicoRelacionado");
		sb.append("	WHERE ");
		sb.append("		servico.idServico = ?");

		parametros.add(idServico);

		retorno.add("idServico");

		List<?> lista = this.execSQL(sb.toString(), parametros.toArray());

		List<ServicoDTO> listServico = this.engine.listConvertion(ServicoDTO.class, lista, retorno);
		if (listServico != null && !listServico.isEmpty()) {
			return listServico;
		}

		return Collections.emptyList();
	}

	public boolean isServicoEmProducao(Integer idServico) throws PersistenceException{
		List<Integer> parametros = new ArrayList<>();
		StringBuilder sql = new StringBuilder();

		sql.append("SELECT serv.idservico, serv.idcategoriaservico, serv.idsituacaoservico, serv.idtiposervico, serv.idimportancianegocio, serv.idempresa, ");
		sql.append(" serv.idtipoeventoservico, serv.idtipodemandaservico, serv.idlocalexecucaoservico, serv.nomeservico, serv.detalheservico, serv.objetivo, ");
		sql.append(" serv.passosservico, serv.datainicio, serv.linkprocesso, serv.descricaoprocesso, serv.tipodescprocess, serv.dispportal, ");
		sql.append(" serv.quadroorientportal, serv.deleted, serv.detalhesServico, serv.siglaAbrev, serv.idbaseconhecimento, serv.idtemplatesolicitacao, ");
		sql.append(" serv.idtemplateacompanhamento, serv.idportfolioservico, serv.tiposervico, serv.faseservico, serv.statusservico, serv.processodeiniciacao, ");
		sql.append(" serv.tipodeinvestimento, serv.valoresdoservico, serv.idtemplateservico, serv.criticidadeservico, serv.demandaservico, serv.retornofinanceiroservico, ");
		sql.append(" serv.incidentecritico ");
		sql.append(" FROM servico s");
		sql.append(" INNER JOIN servicoautorelacionamento sa ON s.idservico = sa.idservico ");
		sql.append(" INNER JOIN servico serv ON sa.idservicorelacionado = serv.idservico ");
		sql.append(" WHERE s.idservico = ? ");
		sql.append(" AND serv.statusservico = 'PRD' ");

		if(idServico != null){
			parametros.add(idServico);
		}else{
			parametros.add(0);
		}

		List<?> lista = this.execSQL(sql.toString(), parametros.toArray());

		if (lista != null && !lista.isEmpty()) {
			return true;
		}

		return false;
	}

	public boolean existeQuestionarioServico(final Integer idServico) throws PersistenceException {
		try {
			Object[] param = new Object[] { idServico };

			String query = "SELECT count(*) FROM " +
			" servico s" +
			" INNER JOIN" +
			" servicocontrato sc ON sc.idservico = s.idservico" +
			" INNER JOIN" +
			" templatesolicitacaoservico t ON t.idtemplate = s.idtemplatesolicitacao" +
			" INNER JOIN" +
			" questionario q ON q.idquestionarioorigem = t.idquestionario" +
			" WHERE" +
			" s.idservico = ? and q.ativo = 'S'";

			List lista = this.execSQL(query, param);

			Object[] row = (Object[]) lista.get(0);

			return Integer.parseInt(row[0].toString()) > 0;

		} catch (NumberFormatException ex) {
			return Boolean.FALSE;
		}
	}

	public Integer obterIdQuestionarioServico(Integer idServico) {
		Object[] param = new Object[] { idServico };

		StringBuilder query = new StringBuilder("select q.idquestionario from servico s ").append(" INNER JOIN templatesolicitacaoservico t ON t.idtemplate = s.idtemplatesolicitacao ").append(" INNER JOIN questionario q ON q.idquestionarioorigem =  t.idquestionario ").append(" WHERE s.idservico= ? and q.ativo = 'S' ");

		List lista;

		try {
			lista = this.execSQL(query.toString(), param);

			if (lista != null && lista.size() > 0) {
				Object[] row = (Object[]) lista.get(0);

				if (row[0] != null) {
					return Integer.parseInt(row[0].toString());
				}
			}

		} catch (PersistenceException e) {
			e.printStackTrace();
		}

		return null;
	}

}