public static bool Update(DataTable dataSource, string table)
{
using (MySqlConnection conexaoMySQL = MySQLDao.getInstancia().getConexao())
{
try
{
conexaoMySQL.Open();
string mSQL = $"Select * from {table}";
MySqlDataAdapter da = new MySqlDataAdapter(mSQL, conexaoMySQL);
MySqlCommandBuilder cmd = new MySqlCommandBuilder(da);
da.Update(dataSource);
return(true);
}
catch (MySqlException msqle)
{
MessageBox.Show("Erro de acesso ao MySQL : " + msqle.Message);
}
finally
{
conexaoMySQL.Close();
}
return(false);
}
}
public static bool UpdateCodigoSenha(string codigo, string id)
{
using (MySqlConnection conexaoMySQL = MySQLDao.getInstancia().getConexao())
{
try
{
conexaoMySQL.Open();
string sqlupd = $@"UPDATE usuario SET codigo_recupera_senha = '{codigo}' where id = {id}";
MySqlDataAdapter da = new MySqlDataAdapter();
da.UpdateCommand = new MySqlCommand(sqlupd, conexaoMySQL);
da.UpdateCommand.ExecuteNonQuery();
return(true);
}
catch (MySqlException msqle)
{
MessageBox.Show("Erro de acesso ao MySQL : " + msqle.Message);
}
finally
{
conexaoMySQL.Close();
}
return(false);
}
}
public static DataTable GetRegistros(string table, string select = "*", string where = "")
{
using (MySqlConnection conexaoMySQL = MySQLDao.getInstancia().getConexao())
{
try
{
conexaoMySQL.Open();
string mSQL = $"Select {select} from {table} {where}";
MySqlCommand cmd = new MySqlCommand(mSQL, conexaoMySQL);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dtUsuario = new DataTable();
da.Fill(dtUsuario);
return(dtUsuario);
}
catch (MySqlException msqle)
{
MessageBox.Show("Erro de acesso ao MySQL : " + msqle.Message);
}
finally
{
conexaoMySQL.Close();
}
return(null);
}
}
public static int GeraId(string table)
{
try
{
return(Convert.ToInt32(MySQLDao.GetRegistros(table, " max(id) as id ").Rows[0]["id"]) + 1);
}
catch
{
return(1);
}
}
public static bool Update(DataTable dataSource)
{
return(MySQLDao.Update(dataSource, "curso"));
}
public static int GeraId()
{
return(MySQLDao.GeraId("curso"));
}
public static DataTable GetCursos(string select = "*", string where = "")
{
return(MySQLDao.GetRegistros("curso", select, where));
}
public static bool Update(DataTable dataSource)
{
return(MySQLDao.Update(dataSource, "instituicao"));
}
public static int GeraId()
{
return(MySQLDao.GeraId("instituicao"));
}
public static DataTable GetInstituicoes(string where = "", string select = "*")
{
return(MySQLDao.GetRegistros("instituicao", select, where));
}
public static bool UpdateAuditoria(DataTable dataSource, DataTable dataSourceAntigo, string nome_registro, int qtdd_colunas)
{
int addedrows = 0;
using (MySqlConnection conexaoMySQL = MySQLDao.getInstancia().getConexao())
{
try
{
conexaoMySQL.Open();
List <auditoria_operacao> listaAuditoria = new List <auditoria_operacao>();
foreach (DataRow rowNova in dataSource.Rows)
{
dataSourceAntigo.PrimaryKey = new DataColumn[] { dataSourceAntigo.Columns["id"] };
DataRow rowAntiga = dataSourceAntigo.Rows.Find(rowNova["id"]);
if (rowAntiga == null)
{
//insert
auditoria_operacao auditoria = new auditoria_operacao(MySQLDao.GeraId("auditoria_operacao") + addedrows,
Convert.ToString(LoginSession.usuario["login"]),
"I",
DateTime.Now,
null,
null,
Convert.ToInt32(rowNova["id"]),
Convert.ToString(rowNova[nome_registro]),
dataSource.TableName);
listaAuditoria.Add(auditoria);
addedrows++;
}
else //update
{
for (int i = 0; i < qtdd_colunas; i++)
{
if (!rowAntiga[i].Equals(rowNova[i]))
{
auditoria_operacao auditoria = new auditoria_operacao(MySQLDao.GeraId("auditoria_operacao") + addedrows,
Convert.ToString(LoginSession.usuario["login"]),
"U",
DateTime.Now,
Convert.ToString(rowAntiga[i]),
Convert.ToString(rowNova[i]),
Convert.ToInt32(rowNova["id"]),
Convert.ToString(rowNova[nome_registro]),
dataSource.TableName + "_" + dataSource.Columns[i].ColumnName);
listaAuditoria.Add(auditoria);
addedrows++;
}
}
}
}
foreach (DataRow rowAntiga in dataSourceAntigo.Rows)
{
dataSource.PrimaryKey = new DataColumn[] { dataSource.Columns["id"] };
DataRow rowNova = dataSource.Rows.Find(rowAntiga["id"]);
if (rowNova == null)
{
//delete
auditoria_operacao auditoria = new auditoria_operacao(MySQLDao.GeraId("auditoria_operacao") + addedrows,
Convert.ToString(LoginSession.usuario["login"]),
"D",
DateTime.Now,
null,
null,
Convert.ToInt32(rowAntiga["id"]),
Convert.ToString(rowAntiga[nome_registro]),
dataSource.TableName);
listaAuditoria.Add(auditoria);
addedrows++;
}
}
string sqlupd = @"INSERT INTO auditoria_operacao VALUES ";
foreach (auditoria_operacao _auditoria in listaAuditoria)
{
string data_operacao = _auditoria.data_operacao.Year.ToString() + "/" + _auditoria.data_operacao.Month.ToString() + "/" + _auditoria.data_operacao.Day.ToString();
sqlupd += $@"({_auditoria.id}, '{_auditoria.login_usuario}', '{_auditoria.operacao}', '{data_operacao}', '{_auditoria.valor_antigo}', '{_auditoria.valor_novo}', {_auditoria.id_registro_alterado}, '{_auditoria.nome_registro_alterado}', '{_auditoria.coluna_alterada}'),";
}
sqlupd = sqlupd.Substring(0, sqlupd.Length - 1);
MySqlDataAdapter da = new MySqlDataAdapter();
da.UpdateCommand = new MySqlCommand(sqlupd, conexaoMySQL);
da.UpdateCommand.ExecuteNonQuery();
return(true);
}
catch (MySqlException msqle)
{
MessageBox.Show("Erro de acesso ao MySQL : " + msqle.Message);
}
finally
{
conexaoMySQL.Close();
}
return(false);
}
}
public static DataTable GetAuditorias(string where = "")
{
return(MySQLDao.GetRegistros("auditoria_operacao", "*", " where not (operacao = 'U' and coluna_alterada = 'diploma_validado' and ((date_sub({fn now()}, interval 40 year)) >= data_operacao))"));
}
public static int GeraId()
{
return(MySQLDao.GeraId("diploma"));
}
public static DataTable GetDiplomas(string select = "*", string where = "")
{
return(MySQLDao.GetRegistros("diploma", select, where));
}
