package br.com.centralit.citcorpore.integracao;

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

import br.com.centralit.citcorpore.bean.ModeloEmailDTO;
import br.com.centralit.citcorpore.util.CITCorporeUtil;
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.util.Constantes;
import br.com.citframework.util.SQLConfig;

@SuppressWarnings({"unchecked", "rawtypes"})
public class ModeloEmailDao extends CrudDaoDefaultImpl {

    public ModeloEmailDao() {
		super(Constantes.getValue("DATABASE_ALIAS"), null);
	}
    
	@Override
	public Collection find(IDto obj) throws PersistenceException {
		return null;
	}

	@Override
	public Collection<Field> getFields() {
		Collection<Field> listFields = new ArrayList<>();
		
		listFields.add(new Field("idModeloEmail" ,"idModeloEmail", true, true, false, false) );
		listFields.add(new Field("titulo" ,"titulo", false, false, false, false) );
		listFields.add(new Field("texto" ,"texto", false, false, false, false) );
		listFields.add(new Field("situacao" ,"situacao", false, false, false, false) );
		listFields.add(new Field("identificador" ,"identificador", false, false, false, false) );
		listFields.add(new Field("idprocesso", "idProcesso", false, false, false, false));
		
		return listFields;
	}

	@Override
	public String getTableName() {
		return "ModelosEmails";
	}

	@Override
	public Collection list() throws PersistenceException {
		return null;
	}

	@Override
	public Class getBean() {
		return ModeloEmailDTO.class;
	}
	
	public Collection getAtivos() throws PersistenceException {
		List lstOrder = new ArrayList();
		List lstCondicao = new ArrayList();
		
		lstCondicao.add(new Condition("situacao", "=", "A") );
		
		lstOrder.add(new Order("titulo") );
		
		return super.findByCondition(lstCondicao, lstOrder);
	}
	
	public ModeloEmailDTO findByIdentificador(String identificador) throws PersistenceException {
		List lstOrder = null;
		List lstCondicao = null;
		List result = null;
		
		if (identificador != null && !identificador.trim().equals("") ) {
			lstOrder = new ArrayList();
			lstCondicao = new ArrayList();
			
			lstCondicao.add(new Condition("identificador", "=", identificador) );
			lstOrder.add(new Order("titulo") );
			
			result = (List) super.findByCondition(lstCondicao, lstOrder);
		}
		
		if (result != null && !result.isEmpty() )
			return (ModeloEmailDTO) result.get(0);
		else
			return null;
	}

	/**
	 * Consulta o título dos modelos de email com data fim diferente de zero com
	 * limit de 10 registros
	 *
	 * @param tituloModeloEmail
	 * @return Collection<ModeloEmailDTO> Modelos de email com data fim
	 *         diferente de zero
	 * @throws PersistenceException
	 * 
	 * @author gilmar.junior
	 * @since 05.02.2016
	 */
	public Collection<ModeloEmailDTO> consultarModelosEmailPorTitulo(String tituloModeloEmail)
			throws PersistenceException {
		if (tituloModeloEmail == null) {
			tituloModeloEmail = "";
		}

		String texto = Normalizer.normalize(tituloModeloEmail, Normalizer.Form.NFD);
		texto = texto.replaceAll("[^\\p{ASCII}]", "");
		texto = texto.replaceAll("áàãâéêíóôõúüçÁÀÃÂÉÊÍÓÔÕÚÜÇ´`^''-+=", "aaaaeeiooouucAAAAEEIOOOUUC ");
		texto = "%" + texto.toUpperCase() + "%";

		final Object[] objs = new Object[] { texto };
		final StringBuilder sql = new StringBuilder("select ");

		if (CITCorporeUtil.SGBD_PRINCIPAL.trim().toUpperCase().equalsIgnoreCase(SQLConfig.SQLSERVER)) {
			sql.append("TOP 10 ");
		}
		sql.append("idmodeloemail, titulo from modelosemails where ");

		if (CITCorporeUtil.SGBD_PRINCIPAL.trim().toUpperCase().equalsIgnoreCase(SQLConfig.ORACLE)) {
			sql.append("ROWNUM <= 10 and ");
		}

		sql.append("upper(titulo) like upper(?) and situacao <> 'I' order by titulo");

		if (CITCorporeUtil.SGBD_PRINCIPAL.trim().toUpperCase().equalsIgnoreCase(SQLConfig.POSTGRESQL)
				|| CITCorporeUtil.SGBD_PRINCIPAL.trim().toUpperCase().equalsIgnoreCase(SQLConfig.MYSQL)) {
			sql.append(" LIMIT 10");
		}

		final List<?> list = this.execSQL(sql.toString(), objs);
		final List<String> listaRetorno = new ArrayList();
		listaRetorno.add("idModeloEmail");
		listaRetorno.add("titulo");

		return engine.listConvertion(this.getBean(), list, listaRetorno);
	}
}



