public bool InsertAula(AulaGrupo aula)
{
bool r = false;
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
string sqlCommand = "insert into Aula_Grupo (hora, dia, nome, lotaçao_Atual," +
" lotaçao_Max, duraçao, dificuldade, Instrutor_email, Espaço_Ginasio)" +
" values(" + aula.ToSql() + ")";
MySqlCommand command = new MySqlCommand(sqlCommand, connection);
aula.InitParam(command);
if (command.ExecuteNonQuery() > 0)
{
r = true;
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
connection.Close();
}
return(r);
}
public ActionResult <string> editarAula(dynamic json)
{
lock (_system)
{
JObject received = JObject.Parse(JsonSerializer.Serialize(json));
ActionResult <string> ret = Ok();
if (!_system.isUserOnline(received.GetValue("valueST").ToString()))
{
return(Unauthorized("Client Offline"));
}
JObject extract = (JObject)received.GetValue("aula");
AulaGrupo ag = new AulaGrupo(
extract.GetValue("id").ToObject <int>(),
TimeSpan.Parse(extract.GetValue("hora").ToString().Replace('h', ':')),
extract.GetValue("dia").ToString(),
extract.GetValue("nome").ToString(),
extract.GetValue("lotacao_atual").ToObject <int>(),
extract.GetValue("lotacao_max").ToObject <int>(),
extract.GetValue("duracao").ToString(),
extract.GetValue("dificuldade").ToString(),
_system.GetEmailFromI(extract.GetValue("instrutor_email").ToString()),
extract.GetValue("espaco_ginasio").ToString()
);
_system.EditarAula(ag, ag.id);
return(ret);
}
}
public List <AulaGrupo> GetAulasDiaEmail(string dia)
{
string sqlCommand = "select a.*, i.email from aula_grupo a , instrutor i where i.email = a.Instrutor_email and " +
"dia = @DIA order by hora";
MySqlCommand command = new MySqlCommand(sqlCommand, connection);
command.Parameters.Add(new MySqlParameter("@DIA", MySqlDbType.VarChar));
command.Parameters["@DIA"].Value = dia;
List <AulaGrupo> list = new List <AulaGrupo>();
MySqlDataReader reader = null;
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
reader = command.ExecuteReader();
while (reader.Read() && reader.HasRows)
{
AulaGrupo aula = new AulaGrupo(reader.GetInt32(0), reader.GetTimeSpan(1), reader.GetString(2),
reader.GetString(3), reader.GetInt16(4), reader.GetInt16(5), reader.GetString(6),
reader.GetString(7), reader.GetString(10), reader.GetString(9));
list.Add(aula);
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
reader.Close();
connection.Close();
}
return(list);
}
public List <AulaGrupo> GetAulas(MySqlCommand command)
{
List <AulaGrupo> list = new List <AulaGrupo>();
MySqlDataReader reader = null;
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
reader = command.ExecuteReader();
while (reader.Read() && reader.HasRows)
{
AulaGrupo aula = new AulaGrupo(reader.GetInt32(0), reader.GetTimeSpan(1), reader.GetString(2),
reader.GetString(3), reader.GetInt16(4), reader.GetInt16(5), reader.GetString(6),
reader.GetString(7), reader.GetString(8), reader.GetString(9));
list.Add(aula);
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
reader.Close();
connection.Close();
}
return(list);
}
public ActionResult <string> marcarAula(dynamic json)
{
lock (_system)
{
JObject received = JObject.Parse(JsonSerializer.Serialize(json));
ActionResult <string> ret = Ok();
var uMail = _system.getUserGivenToken(received.GetValue("valueST").ToString());
if (uMail == null)
{
return(Unauthorized("Client Offline"));
}
var tmp = received.GetValue("aula").ToObject <int>();
AulaGrupo ag = _system.GetAulaID(tmp);
ClienteAula ca = new ClienteAula(ag.id, ag.hora, ag.dia, uMail, ag.instrutor_email, ag.espaço_ginasio);
if (((ag.lotaçao_Max < ag.lotaçao_Atual + 1) || !(_system.MarcarAula(ca))))
{
ret = BadRequest("Lotaçao Maxima atingida");
}
return(ret);
}
}
public bool UpdateAulaGrupo(AulaGrupo aula, int id)
{
if (aula.dia.Equals("Sábado") || aula.dia.Equals("Domingo"))
{
return(false);
}
bool r = false;
string sqlCommand = "update Aula_Grupo set " + aula.ToSqlUpdate() + " where " +
"idAula_Grupo = @IDAULA_GRUPO";
MySqlCommand command = new MySqlCommand(sqlCommand, connection);
command.Parameters.Add(new MySqlParameter("@IDAULA_GRUPO", MySqlDbType.Int32));
command.Parameters["@IDAULA_GRUPO"].Value = id;
aula.InitParam(command);
string sqlCommandClienteAula =
"update Clientes_na_AulaGrupo set hora = @HORA, dia = @DIA, Instrutor_email = @INSTRUTOR_EMAIL, " +
"Espaço_Ginasio = @ESPAÇO_GINASIO " +
"where idAula_Grupo = @IDAULA_GRUPO";
MySqlCommand commandClienteAula = new MySqlCommand(sqlCommandClienteAula, connection);
commandClienteAula.Parameters.Add(new MySqlParameter("@IDAULA_GRUPO", MySqlDbType.Int32));
commandClienteAula.Parameters["@IDAULA_GRUPO"].Value = id;
commandClienteAula.Parameters.Add(new MySqlParameter("@INSTRUTOR_EMAIL", MySqlDbType.VarChar));
commandClienteAula.Parameters["@INSTRUTOR_EMAIL"].Value = aula.instrutor_email;
commandClienteAula.Parameters.Add(new MySqlParameter("@ESPAÇO_GINASIO", MySqlDbType.VarChar));
commandClienteAula.Parameters["@ESPAÇO_GINASIO"].Value = aula.espaço_ginasio;
commandClienteAula.Parameters.Add(new MySqlParameter("@HORA", MySqlDbType.Time));
commandClienteAula.Parameters["@HORA"].Value = aula.hora;
commandClienteAula.Parameters.Add(new MySqlParameter("@DIA", MySqlDbType.VarChar));
commandClienteAula.Parameters["@DIA"].Value = aula.dia;
try
{
// Abre a conexão à Base de Dados
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
if (command.ExecuteNonQuery() > 0 && commandClienteAula.ExecuteNonQuery() > 0)
{
r = true;
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
// Fecha a conexão à Base de Dados
connection.Close();
}
return(r);
}
