public static ArrayList gatherData(MySqlConnection con, String query)
{
try
{
ArrayList queue = new ArrayList();
MySqlDataReader reader = null;
MySqlCommand command = new MySqlCommand(query, con);
reader = command.ExecuteReader();
while (reader.Read())
{
QueueStat t = new QueueStat(reader.GetInt32(1), reader.GetInt32(2), reader.GetInt32(4));
queue.Add(t);
}
return(queue);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
finally
{
con.Close();
}
}
/* Main Buttons */
/* Call a Student Method. Type - insert into DB */
private async void callStudent_Click(object sender, EventArgs e)
{
using (MySqlConnection mysqlCon = new MySqlConnection(@"Server=" + ip + ";Database=osa_queuing;Uid=root;Pwd=;"))
{
string query = "SELECT * FROM osa_queuing.queue_stat WHERE date(timestamp) = date(now()) AND deact_flag = 0 UNION SELECT *, 0 FROM recalled_records WHERE date(timestamp) = date(now()) ORDER BY timestamp DESC Limit 1;";
try
{
callStudent.Enabled = false;
mysqlCon.Open();
l = QueueDB.gatherData(mysqlCon, query);
foreach (QueueStat cd in l)
{
currNumber = cd.getServingNumber() + 1;
if (currNumber == 71)
{
currNumber = 1;
}
//newNumber = currNumber.ToString();
}
//DateTime c = new DateTime(DateTimeOffset.Now.ToUnixTimeMilliseconds());
DateTime c = new DateTime(DateTime.Now.Ticks);
QueueStat qs = new QueueStat(Emp.empId, currNumber, c);
mysqlCon.Open();
QueueDB.addQueue(qs);
currentNumber.Text = QueueDB.getLastServed(Emp.empId.ToString(), mysqlCon);
FirstCheck();
await TaskEx.Delay(3000);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.Exit();
}
finally
{
callStudent.Enabled = true;
}
}
}
public static void addQueue(QueueStat rs)
{
using (MySqlConnection secondCon = new MySqlConnection(@"Server=" + ip + ";Database=osa_queuing;Uid=root;Pwd=;")) {
try
{
secondCon.Open();
String sql = "INSERT INTO queue_stat(cubicle_number, serving_number, timestamp) VALUES (@cubicle,@serving,@timestamp)";
MySqlCommand cmd = new MySqlCommand(sql, secondCon);
cmd.Parameters.AddWithValue("@cubicle", rs.getCubicleNumber());
cmd.Parameters.AddWithValue("@serving", rs.getServingNumber());
cmd.Parameters.AddWithValue("@timeStamp", rs.getTimeStamp());
cmd.ExecuteNonQuery();
} catch (Exception)
{
MessageBox.Show("Cannot insert!", "Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
secondCon.Close();
}
}
}
public static ArrayList gatherData(MySqlConnection con)
{
try
{
ArrayList queue = new ArrayList();
MySqlDataReader reader = null;
string query = "SELECT * FROM osa_queuing.queue_stat WHERE date(timestamp) = date(now()) AND deact_flag = 0 ORDER BY timestamp DESC Limit 1";
MySqlCommand command = new MySqlCommand(query, con);
reader = command.ExecuteReader();
if (!reader.HasRows)
{
QueueStat t = new QueueStat(0, 0);
queue.Add(t);
}
else
{
while (reader.Read())
{
QueueStat t = new QueueStat(reader.GetInt32(1), reader.GetInt32(2), reader.GetInt32(4));
queue.Add(t);
}
}
return(queue);
}catch (Exception)
{
MessageBox.Show("The connection to the database server has either terminated abruptly or it doesn't exist.", "Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
finally
{
con.Close();
}
}