package br.com.centralit.citcorpore.integracao;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import br.com.centralit.citcorpore.bean.CaracteristicaDTO;
import br.com.centralit.citcorpore.bean.EventoEmpregadoDTO;
import br.com.centralit.citcorpore.bean.EventoItemConfigDTO;
import br.com.centralit.citcorpore.bean.ValorDTO;
import br.com.citframework.dto.IDto;
import br.com.citframework.excecao.PersistenceException;
import br.com.citframework.integracao.CrudDaoDefaultImpl;
import br.com.citframework.integracao.Field;
import br.com.citframework.util.Constantes;
@SuppressWarnings("unchecked")
public class EventoItemConfigDao extends CrudDaoDefaultImpl {
public EventoItemConfigDao() {
super(Constantes.getValue("DATABASE_ALIAS"), null);
}
public Class getBean() {
return EventoItemConfigDTO.class;
}
public Collection<Field> getFields() {
Collection<Field> listFields = new ArrayList<>();
listFields.add(new Field("IDEVENTO", "idEvento", true, true, false, false));
listFields.add(new Field("IDEMPRESA", "idEmpresa", false, false, false, false));
listFields.add(new Field("DESCRICAO", "descricao", false, false, false, false));
listFields.add(new Field("LIGARCASODESL", "ligarCasoDesl", false, false, false, false));
listFields.add(new Field("DATAINICIO", "dataInicio", false, false, false, false));
listFields.add(new Field("DATAFIM", "dataFim", false, false, false, false));
listFields.add(new Field("USUARIO", "usuario", false, false, false, false));
listFields.add(new Field("SENHA", "senha", false, false, false, false));
return listFields;
}
public String getTableName() {
return "EVENTO";
}
public Collection find(IDto obj) throws PersistenceException {
return null;
}
public Collection list() throws PersistenceException {
return null;
}
public ValorDTO pegarSistemaOperacionalEmpregado(Integer idEmpregado) throws PersistenceException {
String sql = "select v.valorstr from valor v join caracteristica c on c.idcaracteristica = v.idcaracteristica "
+ "join itemconfiguracao ic on ic.iditemconfiguracao = v.iditemconfiguracao "
+ "where c.tagcaracteristica = 'OSNAME' and ic.iditemconfiguracaopai in (select c.iditemconfiguracaopai from usuario a "
+ "join valor b on b.valorstr = a.login join itemconfiguracao c on c.iditemconfiguracao = b.iditemconfiguracao where a.idempregado = ?)";
List dado = this.execSQL(sql, new Object[] { idEmpregado });
List field = new ArrayList();
field.add("valorStr");
List result = listConvertion(ValorDTO.class, dado, field);
if (result != null && result.size() > 0) {
return (ValorDTO) result.get(0);
}
return null;
}
public ValorDTO pegarCaminhoItemConfig(String nomeBaseItemConfig) throws PersistenceException {
String sql = "SELECT V.VALORSTR FROM ITEMCONFIGURACAO IC JOIN TIPOITEMCONFIGURACAO TIC ON TIC.IDTIPOITEMCONFIGURACAO = IC.IDTIPOITEMCONFIGURACAO "
+ "JOIN TIPOITEMCFGCARACTERISTICA TICC ON TICC.IDTIPOITEMCONFIGURACAO = TIC.IDTIPOITEMCONFIGURACAO "
+ "JOIN CARACTERISTICA CA ON CA.IDCARACTERISTICA = TICC.IDCARACTERISTICA "
+ "JOIN VALOR V ON V.IDITEMCONFIGURACAO = IC.IDITEMCONFIGURACAO AND V.IDCARACTERISTICA = TICC.IDCARACTERISTICA "
+ "WHERE IC.DATAFIM IS NULL AND TIC.TAGTIPOITEMCONFIGURACAO = 'SOFTWARES' AND CA.TAGCARACTERISTICA = 'FOLDER' "
+ "AND IC.IDITEMCONFIGURACAO IN (SELECT A.IDITEMCONFIGURACAO FROM ITEMCONFIGURACAO A "
+ "JOIN TIPOITEMCONFIGURACAO B ON B.IDTIPOITEMCONFIGURACAO = A.IDTIPOITEMCONFIGURACAO "
+ "JOIN TIPOITEMCFGCARACTERISTICA C ON C.IDTIPOITEMCONFIGURACAO = B.IDTIPOITEMCONFIGURACAO JOIN CARACTERISTICA D ON D.IDCARACTERISTICA = C.IDCARACTERISTICA "
+ "JOIN VALOR V ON V.IDITEMCONFIGURACAO = A.IDITEMCONFIGURACAO AND V.IDCARACTERISTICA = C.IDCARACTERISTICA "
+ "WHERE A.DATAFIM IS NULL AND B.TAGTIPOITEMCONFIGURACAO = 'SOFTWARES' AND D.TAGCARACTERISTICA = 'NAME' AND V.VALORSTR = ?)";
List dado = this.execSQL(sql, new Object[] { nomeBaseItemConfig });
List field = new ArrayList();
field.add("valorStr");
List result = listConvertion(ValorDTO.class, dado, field);
if (result != null && result.size() > 0) {
return (ValorDTO) result.get(0);
}
return null;
}
/*public Collection<CaracteristicaDTO> pegarNetworksEmpregado(Integer idEmpregado) throws PersistenceException {
String sql = "select b.valorstr, a.tagcaracteristica from caracteristica a join valor b on a.idcaracteristica = b.idcaracteristica "
+ "join itemconfiguracao c on b.iditemconfiguracao = c.iditemconfiguracao where c.iditemconfiguracao in (select d.iditemconfiguracao "
+ "from (select b.valorstr from caracteristica a join valor b on a.idcaracteristica = b.idcaracteristica "
+ "join itemconfiguracao c on c.iditemconfiguracao = b.iditemconfiguracao where a.tagcaracteristica = 'IPADDR') a join "
+ "(select b.valorstr, b.iditemconfiguracao from caracteristica a join valor b on a.idcaracteristica = b.idcaracteristica "
+ "join itemconfiguracao c on c.iditemconfiguracao = b.iditemconfiguracao where a.tagcaracteristica = 'IPADDRESS') b on a.valorstr = b.valorstr "
+ "join itemconfiguracao d on d.iditemconfiguracao = b.iditemconfiguracao where d.iditemconfiguracaopai in (select c.iditemconfiguracaopai from usuario a "
+ "join valor b on b.valorstr = a.login " + "join itemconfiguracao c on c.iditemconfiguracao = b.iditemconfiguracao "
+ "where a.idempregado = ?)) and c.datafim is null order by c.datainicio desc, c.iditemconfiguracaopai desc";
List dados = this.execSQL(sql, new Object[] { idEmpregado });
List fields = new ArrayList();
fields.add("valorString");
fields.add("tag");
return this.listConvertion(CaracteristicaDTO.class, dados, fields);
}*/
public Integer getIdItemConfiguracaoPai(Integer idEmpregado) throws PersistenceException {
String sql = "select c.iditemconfiguracaopai from usuario a " + "join valor b on b.valorstr = a.login join itemconfiguracao c on c.iditemconfiguracao = b.iditemconfiguracao "
+ "where a.idempregado = ? and c.datafim is null order by c.datainicio desc, c.iditemconfiguracaopai desc";
List dados = this.execSQL(sql, new Object[] { idEmpregado });
List fields = new ArrayList();
fields.add("idItemConfiguracaoPai");
List result = this.listConvertion(EventoEmpregadoDTO.class, dados, fields);
if (result != null && result.size() > 0) {
return ((EventoEmpregadoDTO) result.get(0)).getIdItemConfiguracaoPai();
}
return null;
}
public Collection<CaracteristicaDTO> pegarNetworksItemConfiguracao(Integer idItemConfiguracao) throws PersistenceException {
String sql = "SELECT v.valorstr, c.tagcaracteristica FROM valor AS v " +
"INNER JOIN itemconfiguracao AS i ON i.iditemconfiguracao = v.iditemconfiguracao " +
"INNER JOIN caracteristica AS c ON c.idcaracteristica = v.idcaracteristica AND v.iditemconfiguracao = i.iditemconfiguracao " +
"INNER JOIN tipoitemconfiguracao AS t ON t.idtipoitemconfiguracao = i.idtipoitemconfiguracao " +
"AND (c.tagcaracteristica = 'MACADDR' " +
"OR c.tagcaracteristica = 'IPADDRESS' " +
"OR c.tagcaracteristica = 'IPMASK' ) " +
"AND t.tagtipoitemconfiguracao = 'NETWORKS' " +
"WHERE i.iditemconfiguracaopai = ? " +
"AND i.datafim IS NULL " +
"ORDER BY c.tagcaracteristica;";
List<?> dados = this.execSQL(sql, new Object[] { idItemConfiguracao });
List<String> fields = new ArrayList<String>();
fields.add("valorString");
fields.add("tag");
return this.listConvertion(CaracteristicaDTO.class, dados, fields);
}
/**
* M�todo que retorna o nome do Sistema operacional instalado no item de configura��o
*
* @param idItemConfiguracao
* @return String nome do SO
* @throws Exception
*/
public String pegarSistemaOperacionalItemConfiguracao(Integer idItemConfiguracao) throws PersistenceException {
String sql = "SELECT v.valorstr " +
"FROM valor v " +
"INNER JOIN caracteristica AS c " +
"ON c.idcaracteristica = v.idcaracteristica " +
"INNER JOIN itemconfiguracao AS ic " +
"ON ic.iditemconfiguracao = v.iditemconfiguracao " +
"INNER JOIN tipoitemconfiguracao AS tp " +
"ON tp.idtipoitemconfiguracao = ic.idtipoitemconfiguracao " +
"WHERE c.tagcaracteristica = 'OSNAME' " +
"AND tp.tagtipoitemconfiguracao = 'HARDWARE' " +
"AND ic.iditemconfiguracaopai = ?";
List<?> dado = this.execSQL(sql, new Object[] { idItemConfiguracao });
List<String> field = new ArrayList<String>();
field.add("valorStr");
List<?> result = listConvertion(ValorDTO.class, dado, field);
if (result != null && result.size() > 0) {
return ((ValorDTO) result.get(0)).getValorStr();
}
return null;
}
}