private async void button7_Click(object sender, EventArgs e)
{
Enabled = false;
var confirmResult = MessageBox.Show("Are you sure to restart the queuing system? ",
"Clean queue confirmation",
MessageBoxButtons.YesNo);
if (confirmResult == DialogResult.Yes)
{
SqlConnection con = new SqlConnection(connection_string);
con.Open();
SqlTransaction tran = con.BeginTransaction();
try
{
String getRating = "select * from Rating_Office";
String saveRating = "insert into Feedbacks (Servicing_Office,Score,Transaction_ID,Date_Of_Feedback,isStudent) values " +
" (@param_so,@param_score,@param_tt_ID,@param_date,@param_isStudent)";
SqlCommand cmd_getRating = new SqlCommand(getRating, con);
SqlCommand cmd_saveRating = new SqlCommand(saveRating, con);
cmd_getRating.Transaction = tran;
cmd_saveRating.Transaction = tran;
SqlDataReader rdr_rating;
List <_Rating_Office> evaluationForToday = new List <_Rating_Office>();
String query0 = "TRUNCATE TABLE Main_Queue; TRUNCATE TABLE Queue_Info; TRUNCATE TABLE Serving_Time; TRUNCATE TABLE Servicing_Terminal; TRUNCATE TABLE Rating_Office; TRUNCATE TABLE Serving_Info;";
// retrieve the evaluations first
rdr_rating = cmd_getRating.ExecuteReader();
while (rdr_rating.Read())
{
_Rating_Office a = new _Rating_Office
{
Customer_Queue_Number = (string)rdr_rating["Customer_Queue_Number"],
isStudent = (bool)rdr_rating["isStudent"],
score = (int)rdr_rating["Score"],
Servicing_OFfice = (int)rdr_rating["Servicing_Office"],
Transaction_ID = (int)rdr_rating["Transaction_ID"]
};
evaluationForToday.Add(a);
}
// save the evaluations
DateTime today = DateTime.Today;
progressBar1.Maximum = 2 + evaluationForToday.Count;
foreach (_Rating_Office b in evaluationForToday)
{
cmd_saveRating.Parameters.AddWithValue("@param_so", b.Servicing_OFfice);
cmd_saveRating.Parameters.AddWithValue("@param_score", b.score);
cmd_saveRating.Parameters.AddWithValue("@param_tt_ID", b.Transaction_ID);
cmd_saveRating.Parameters.AddWithValue("@param_date", today);
cmd_saveRating.Parameters.AddWithValue("@param_isStudent", b.isStudent);
cmd_saveRating.ExecuteNonQuery();
cmd_saveRating.Parameters.Clear();
progressBar1.Increment(1);
}
// clean all
SqlCommand cmd0 = new SqlCommand(query0, con);
cmd0.Transaction = tran;
cmd0.ExecuteNonQuery();
progressBar1.Increment(1);
// Doing the work on firebase too
firebase_Connection fcon = new firebase_Connection();
await fcon.Truncate_Firebase();
fcon.Controller_SetAllToInactive();
fcon.App_Delete_PreQueueAsyncNoCTS();
progressBar1.Increment(1);
tran.Commit();
Queue_Info_Update();
MessageBox.Show("All queue at the system and information about it have been cleared.", "Clean Success!");
progressBar1.Value = 0;
}
catch (FirebaseException exd)
{
try { tran.Rollback(); } catch (Exception exRollback) { MessageBox.Show("Error at -> " + exRollback.Message); }
Console.WriteLine("An error occured while connecting to firebase DB. Error ->" + exd.Message);
MessageBox.Show("Please check your internet connection.", "Could not connect online");
progressBar1.Value = 0;
}
catch (SqlException eb)
{
try { tran.Rollback(); } catch (Exception exRollback) { MessageBox.Show("Error at -> " + exRollback.Message); }
MessageBox.Show("An error occured while connecting to local DB. Error -> " + eb.Message, "Databse error");
progressBar1.Value = 0;
}
con.Close();
}
Enabled = true;
}
private async void btn_Import_Click(object sender, EventArgs e)
{
using (OpenFileDialog dialog = new OpenFileDialog()
{
Filter = "Excel Workbook|*.xlsx", ValidateNames = true
})
{
if (dialog.ShowDialog() == DialogResult.OK)
{
Enabled = false;
firebase_Connection fcon = new firebase_Connection();
SqlConnection con = new SqlConnection(connection_string);
string query = "insert into vw_es_students (Full_Name,Student_No,College,Course,Status,Year,Password) " +
" values (@param_fn,@param_sn,@param_cl,@param_cs,@param_s,@param_y,@param_p)";
string truncate_query = "TRUNCATE TABLE vw_es_students";
SqlCommand cmd = new SqlCommand(query, con);
SqlCommand truncate_cmd = new SqlCommand(truncate_query, con);
string path = dialog.FileName;
Excel excel = new Excel(path, 1);
try
{
List <_App_User> results = excel.ReadCell();
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
excel.cleanCOMobjects();
con.Open();
truncate_cmd.ExecuteNonQuery();
int max = excel._userCount * 4;
progressBar1.Maximum = max;
//drop queue_status and accounts first
await fcon.Controller_TruncateQueueStatus();
await fcon.Controller_DeleteAllAccounts();
foreach (_App_User b in results)
{
string password = Cryptography.Encrypt(b.password.ToString());
progressBar1.Increment(1);
//upload to the Firebase DB
try
{
await fcon.Controller_ImportUsers(b);
progressBar1.Increment(1);
await fcon.Controller_RegisterThisUser(b);
progressBar1.Increment(1);
await fcon.Controller_InsertQueueStatus(b.accountNumber);
progressBar1.Increment(1);
//upload to local db
cmd.Parameters.AddWithValue("@param_fn", b.lastName.ToUpper() + "," + b.firstName + " " + b.middleName);
cmd.Parameters.AddWithValue("@param_sn", b.accountNumber);
cmd.Parameters.AddWithValue("@param_cl", b.college);
cmd.Parameters.AddWithValue("@param_cs", b.course);
cmd.Parameters.AddWithValue("@param_s", b.status);
cmd.Parameters.AddWithValue("@param_y", b.year);
cmd.Parameters.AddWithValue("@param_p", password);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
catch (FirebaseAuthException exd) {
Console.WriteLine(exd.Message);
}
}
con.Close();
progressBar1.Value = max;
MessageBox.Show("Import finished!", "Success!");
progressBar1.Value = 0;
}
catch (FormatException)
{
progressBar1.Value = 0;
MessageBox.Show("Make sure all the ID contains numbers only.", "Format error");
}
catch (FirebaseAuthException exd)
{
//progressBar1.Value = 0;
//MessageBox.Show("Make sure all of the Student_No contains no spaces and special characters. Error Code: "+exd.Reason,"Online Database error!");
}
catch (FirebaseException)
{
progressBar1.Value = 0;
MessageBox.Show("Please check your internet connection. Error Code:", "Connection error");
}
Enabled = true;
}
}
}
