package br.com.centralit.citcorpore.integracao;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import br.com.centralit.citcorpore.bean.ScriptsDTO;
import br.com.centralit.citcorpore.util.CITCorporeUtil;
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.integracao.Order;
import br.com.citframework.util.Constantes;
import br.com.citframework.util.SQLConfig;
public class ScriptsDao extends CrudDaoDefaultImpl {
public ScriptsDao() {
super(Constantes.getValue("DATABASE_ALIAS"), null);
}
public ScriptsDTO buscaScriptPorId(final Integer id) throws PersistenceException {
final ScriptsDTO scriptsDTO = new ScriptsDTO();
scriptsDTO.setIdScript(id);
final List col = (List) super.find(scriptsDTO, null);
if (col == null || col.size() == 0) {
return null;
}
return (ScriptsDTO) col.get(0);
}
public List<ScriptsDTO> buscaScriptsPorIdVersao(final Integer idVersao) throws PersistenceException {
final ScriptsDTO scriptsDTO = new ScriptsDTO();
scriptsDTO.setIdVersao(idVersao);
return (List) super.find(scriptsDTO, null);
}
public ScriptsDTO consultarScript(final String nomeScript) throws PersistenceException {
final ScriptsDTO scriptsDTO = new ScriptsDTO();
scriptsDTO.setNome(nomeScript);
final List col = (List) super.find(scriptsDTO, null);
if (col != null && col.size() != 0) {
return (ScriptsDTO) col.get(0);
}
return null;
}
@Override
public Collection<ScriptsDTO> find(final IDto obj) throws PersistenceException {
return null;
}
@Override
public Class<ScriptsDTO> getBean() {
return ScriptsDTO.class;
}
@Override
public Collection<Field> getFields() {
final Collection<Field> listFields = new ArrayList<>();
listFields.add(new Field("IDSCRIPT", "idScript", true, true, false, false));
listFields.add(new Field("IDVERSAO", "idVersao", false, false, false, false));
listFields.add(new Field("NOME", "nome", false, false, false, false));
listFields.add(new Field("DESCRICAO", "descricao", false, false, false, false));
listFields.add(new Field("SQLQUERY", "sqlQuery", false, false, false, false));
listFields.add(new Field("HISTORICO", "historico", 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("TIPO", "tipo", false, false, false, false));
return listFields;
}
@Override
public String getTableName() {
return "SCRIPTS";
}
public boolean haScriptDeVersaoComErro() throws PersistenceException {
final String sql = "select * from " + getTableName() + " where idversao is not null and descricao like 'ERRO%'";
final List list = this.execSQL(sql, null);
return list != null && !list.isEmpty();
}
@Override
public Collection list() throws PersistenceException {
final List list = new ArrayList();
list.add(new Order("dataInicio"));
return super.list(list);
}
public void marcaErrosScriptsComoCorrigidos() throws PersistenceException {
if (CITCorporeUtil.SGBD_PRINCIPAL.equalsIgnoreCase(SQLConfig.SQLSERVER)) {
execUpdate("update " + getTableName() + " set descricao = 'CORRIGIDO ' + convert(varchar,descricao) where idversao is not null and descricao like 'ERRO%' and idscript > 0 ", null);
} else {
execUpdate("update " + getTableName() + " set descricao = concat('CORRIGIDO ', descricao) where idversao is not null and descricao like 'ERRO%' and idscript > 0 ", null);
}
}
public boolean temScriptsAtivos(final ScriptsDTO script) throws PersistenceException {
final List parametro = new ArrayList();
List list;
String sql = "select idscript From " + getTableName() + " where ";
if (script != null && script.getNome() != null && !script.getNome().isEmpty()) {
sql += " nome = ? and";
parametro.add(script.getNome());
}
sql += " dataFim is null ";
if (script != null && script.getIdScript() != null) {
sql += " and idscript <> " + script.getIdScript();
}
list = this.execSQL(sql, parametro.toArray());
return list != null && !list.isEmpty();
}
public String testaPermissaoCriacaoTabela() {
String retorno = "sucesso";
try {
final StringBuilder sql = new StringBuilder();
if (CITCorporeUtil.SGBD_PRINCIPAL.equalsIgnoreCase(SQLConfig.MYSQL)) {
sql.append("CREATE TABLE tabelatestepermissao ( ");
sql.append(" idtabelatestepermissao INT(11) NOT NULL PRIMARY KEY, ");
sql.append(" colunatexto VARCHAR(256) ");
sql.append(") ");
sql.append("engine = innodb");
} else if (CITCorporeUtil.SGBD_PRINCIPAL.equalsIgnoreCase(SQLConfig.POSTGRESQL)) {
sql.append("CREATE TABLE tabelatestepermissao ( ");
sql.append(" idtabelatestepermissao INTEGER NOT NULL, ");
sql.append(" colunatexto VARCHAR(256) NULL, ");
sql.append(" PRIMARY KEY (idtabelatestepermissao) ");
sql.append(") ");
} else if (CITCorporeUtil.SGBD_PRINCIPAL.equalsIgnoreCase(SQLConfig.ORACLE)) {
sql.append("CREATE TABLE tabelatestepermissao ( ");
sql.append(" idtabelatestepermissao NUMBER(10, 0) NOT NULL, ");
sql.append(" colunatexto VARCHAR2(256) NULL ");
sql.append(") ");
} else if (CITCorporeUtil.SGBD_PRINCIPAL.equalsIgnoreCase(SQLConfig.SQLSERVER)) {
sql.append("CREATE TABLE tabelatestepermissao ( ");
sql.append(" idtabelatestepermissao INTEGER NOT NULL PRIMARY KEY, ");
sql.append(" colunatexto VARCHAR(256) ");
sql.append(")");
}
execUpdate(sql.toString(), null);
} catch (final PersistenceException e) {
retorno = e.getMessage();
}
return retorno;
}
public String testaPermissaoInsercaoRegistroTabela() {
String retorno = "sucesso";
try {
execUpdate("INSERT INTO tabelatestepermissao (idtabelatestepermissao, colunatexto) VALUES (1, 'conteudo')", null);
} catch (final PersistenceException e) {
retorno = e.getMessage();
}
return retorno;
}
public String testaPermissaoConsultaTabela() {
String retorno = "sucesso";
try {
execSQL("SELECT * FROM parametrocorpore", null);
} catch (final PersistenceException e) {
retorno = e.getMessage();
}
return retorno;
}
public String testaPermissaoExclusaoRegistroTabela() {
String retorno = "sucesso";
try {
execUpdate("DELETE FROM tabelatestepermissao WHERE idtabelatestepermissao = 1", null);
} catch (final PersistenceException e) {
retorno = e.getMessage();
}
return retorno;
}
public String testaPermissaoCriacaoColuna() {
String retorno = "sucesso";
try {
if (CITCorporeUtil.SGBD_PRINCIPAL.equalsIgnoreCase(SQLConfig.MYSQL) || CITCorporeUtil.SGBD_PRINCIPAL.equalsIgnoreCase(SQLConfig.POSTGRESQL)) {
execUpdate("ALTER TABLE tabelatestepermissao ADD COLUMN colunaadicionada VARCHAR(256) NULL", null);
} else if (CITCorporeUtil.SGBD_PRINCIPAL.equalsIgnoreCase(SQLConfig.ORACLE)) {
execUpdate("ALTER TABLE tabelatestepermissao ADD (colunaadicionada VARCHAR2(256) NULL)", null);
} else if (CITCorporeUtil.SGBD_PRINCIPAL.equalsIgnoreCase(SQLConfig.SQLSERVER)) {
execUpdate("ALTER TABLE tabelatestepermissao ADD colunaadicionada VARCHAR(256) NULL", null);
}
} catch (final PersistenceException e) {
retorno = e.getMessage();
}
return retorno;
}
public String testaPermissaoAlteracaoColuna() {
String retorno = "sucesso";
try {
if (CITCorporeUtil.SGBD_PRINCIPAL.equalsIgnoreCase(SQLConfig.MYSQL)) {
execUpdate("ALTER TABLE tabelatestepermissao CHANGE COLUMN colunaadicionada colunaadicionadaalterada VARCHAR(256) NULL", null);
} else if (CITCorporeUtil.SGBD_PRINCIPAL.equalsIgnoreCase(SQLConfig.POSTGRESQL) || CITCorporeUtil.SGBD_PRINCIPAL.equalsIgnoreCase(SQLConfig.ORACLE)
|| CITCorporeUtil.SGBD_PRINCIPAL.equalsIgnoreCase(SQLConfig.SQLSERVER)) {
execUpdate("ALTER TABLE tabelatestepermissao RENAME COLUMN colunaadicionada TO colunaadicionadaalterada", null);
}
} catch (final PersistenceException e) {
retorno = e.getMessage();
}
return retorno;
}
public String testaPermissaoExclusaoColuna() {
String retorno = "sucesso";
try {
execUpdate("ALTER TABLE tabelatestepermissao DROP COLUMN colunatexto", null);
} catch (final PersistenceException e) {
retorno = e.getMessage();
}
return retorno;
}
public String testaPermissaoExclusaoTabela() {
String retorno = "sucesso";
try {
execUpdate("DROP TABLE tabelatestepermissao", null);
} catch (final PersistenceException e) {
retorno = e.getMessage();
}
return retorno;
}
public List<ScriptsDTO> pesquisaScriptsComFaltaPermissao() throws PersistenceException {
String descricao = null;
if (CITCorporeUtil.SGBD_PRINCIPAL.equalsIgnoreCase(SQLConfig.SQLSERVER)) {
// PARA O SQLSERVER, O QUE DEFINE O CASE INSENSITIVE DA COLUNA � O COLLATE SQL_Latin1_General_CP1_CI_AS
descricao = "descricao COLLATE SQL_Latin1_General_CP1_CI_AS";
} else {
descricao = "UPPER(descricao)";
}
final StringBuilder sql = new StringBuilder();
sql.append("SELECT descricao, ");
sql.append(" sqlquery ");
sql.append("FROM scripts ");
sql.append("WHERE idversao IS NOT NULL ");
sql.append(" AND ").append(descricao).append(" LIKE 'ERRO%' ");
sql.append(" AND ( ").append(descricao).append(" LIKE '%COMMAND DENIED%' ");
sql.append(" OR ").append(descricao).append(" LIKE '%PERMISSION DENIED%' ");
sql.append(" OR ").append(descricao).append(" LIKE '%PERMISS�O NEGADA%' ");
sql.append(" OR ").append(descricao).append(" LIKE '%MUST BE OWNER%' ");
sql.append(" OR ").append(descricao).append(" LIKE '%DEVE SER O DONO%' ");
sql.append(" OR ").append(descricao).append(" LIKE '%INSUFFICIENT PRIVILEGES%' ");
sql.append(" OR ").append(descricao).append(" LIKE '%NO PRIVILEGES%' )");
final List list = this.execSQL(sql.toString(), null);
final List fields = new ArrayList();
fields.add("descricao");
fields.add("sqlQuery");
List<ScriptsDTO> listaRetorno = new ArrayList<ScriptsDTO>();
if (list != null && !list.isEmpty()) {
listaRetorno = this.listConvertion(getBean(), list, fields);
}
return listaRetorno;
}
public boolean verificaExistenciaTabela(final String tabela) {
boolean tabelaExiste = false;
try {
execSQL("SELECT * FROM " + tabela, null);
tabelaExiste = true;
} catch (final PersistenceException e) {
e.printStackTrace();
}
return tabelaExiste;
}
public boolean verificaExistenciaColuna(final String tabela, final String coluna) {
boolean colunaExiste = false;
try {
execSQL("SELECT " + coluna + " FROM " + tabela, null);
colunaExiste = true;
} catch (final PersistenceException e) {
e.printStackTrace();
}
return colunaExiste;
}
/**
* Retorna todos os scripts da tabela de script
*
* @author thyen.chang
* @since 03/02/2015 - OPERA��O USAIN BOLT
* @return Lista com todos os scripts
* @throws PersistenceException
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
public Collection<ScriptsDTO> listaTodosScripts() throws PersistenceException{
List retorno = execSQL("SELECT nome FROM SCRIPTS", null);
List fields = new ArrayList();
fields.add("nome");
return listConvertion(getBean(), retorno, fields);
}
}