private void buttonBack_Click(object sender, EventArgs e)
{
// Return to Home screen
this.Visible = false;
Home form = new Home();
form.Show();
this.Dispose();
}
// Submits new check out data once the data has been checked for errors
private void buttonCheckOut_Click(object sender, EventArgs e)
{
// Code for converting necessary PID and Date fields so the database doesn't have problems
int PIDparse;
String checkOutDate = dateTimeCheckOut.Value.ToString("yyyy/MM/dd hh:mm:ss tt");
String dueDate = dateTimeDueDate.Value.ToString("yyyy/MM/dd");
// Verify that the Student is not already registered in the database
SQLiteConnection connectStudent = new SQLiteConnection(Login.connection);
connectStudent.Open();
String queryStudent = "SELECT PID FROM Students WHERE PID = '" + textBoxPID.Text + "';";
SQLiteCommand cmdCheckStudent = new SQLiteCommand(queryStudent, connectStudent);
object checkStudent = cmdCheckStudent.ExecuteScalar();
connectStudent.Close();
if (checkStudent != null)
{
MessageBox.Show("This student is already registered in the database. Please use the \"Check Out\" form instead of \"New Check Out\"");
}
// Check for any blank fields
else if (string.IsNullOrWhiteSpace(textBoxDevice.Text))
{
MessageBox.Show("Please scan the serial number and device name will populate itself.");
}
else if (string.IsNullOrWhiteSpace(textBoxSerial.Text))
{
MessageBox.Show("Please use the scanner or manually input the device serial number.");
}
// Verify that the PID is not blank, approprite in length, and type (6 numbers)
else if (textBoxPID.Text.Length != 6 || string.IsNullOrWhiteSpace(textBoxPID.Text) || !int.TryParse(textBoxPID.Text, out PIDparse))
{
MessageBox.Show("The PID entered was not valid. Please enter a valid PID (6 numbers long)");
}
// Check for a blank first or last name
else if (string.IsNullOrWhiteSpace(textBoxFirstName.Text))
{
MessageBox.Show("Please enter the student's first name.");
}
else if (string.IsNullOrWhiteSpace(textBoxLastName.Text))
{
MessageBox.Show("Please enter the student's last name.");
}
else
{
// Prompts the user for any last changes
if (MessageBox.Show("Are you sure you want to check this device out? (Make sure to verify the check out date)", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
// Pull device info for the one specified
SQLiteConnection connectAvailable = new SQLiteConnection(Login.connection);
connectAvailable.Open();
String queryAvailable = "SELECT Device FROM CheckOut WHERE Device = '" + textBoxDevice.Text + "';";
SQLiteCommand cmdCheck = new SQLiteCommand(queryAvailable, connectAvailable);
object check = cmdCheck.ExecuteScalar();
// Check if the device is available
if (check == null)
{
SQLiteConnection connect = new SQLiteConnection(Login.connection);
try
{
// If the device is available, update the database, add the new student info, and swith the device to unavailable
String[] queries = new String[4];
queries[0] = "INSERT INTO CheckOut (Device, SerialNumber, PID, CheckOutDate, DueDate, Comments, Assets) VALUES (@Device, @SerialNumber, @PID, @CheckOutDate, @DueDate, @Comments, @Assets)";
queries[1] = "INSERT INTO History (Device, SerialNumber, PID, CheckOutDate, DueDate, Comments, Assets) VALUES (@Device, @SerialNumber, @PID, @CheckOutDate, @DueDate, @Comments, @Assets)";
queries[2] = "INSERT INTO Students (PID, FirstName, LastName) VALUES (@PID, @FirstName, @LastName)";
queries[3] = "UPDATE Device SET CheckOut = 'True' WHERE SerialNumber = @SerialNumber AND Device = @Device";
foreach (String query in queries)
{
SQLiteCommand cmd = new SQLiteCommand(query, connect);
connect.Open();
cmd.Parameters.AddWithValue("@Device", textBoxDevice.Text);
cmd.Parameters.AddWithValue("@SerialNumber", textBoxSerial.Text);
cmd.Parameters.AddWithValue("@PID", textBoxPID.Text);
cmd.Parameters.AddWithValue("@FirstName", textBoxFirstName.Text);
cmd.Parameters.AddWithValue("@LastName", textBoxLastName.Text);
cmd.Parameters.AddWithValue("@CheckOutDate", checkOutDate);
cmd.Parameters.AddWithValue("@DueDate", dueDate);
cmd.Parameters.AddWithValue("@Comments", textBoxComments.Text);
cmd.Parameters.AddWithValue("@Assets", comboBoxAsset.Text);
cmd.ExecuteNonQuery();
connect.Close();
}
}
catch (SQLiteException exception)
{
MessageBox.Show(exception.Message.ToString());
}
finally
{
if (connect.State == ConnectionState.Open)
{
connect.Close();
}
}
// Refresh back to Home after New Check Out is completed
this.Visible = false;
Home form = new Home();
form.Show();
this.Dispose();
}
else
{
MessageBox.Show("This device is already checked out. Please verify the device name and serial number on the label.");
}
}
else
{
// Discard changes
}
}
}
private void buttonCheckIn_Click(object sender, EventArgs e)
{
// Code for converting necessary PID and Date fields so the database doesn't have problems
int PIDparse;
dateTimeSelect.MinDate = DateTime.Now;
String date = dateTimeSelect.Value.ToString("yyyy/MM/dd hh:mm:ss tt");
if (string.IsNullOrWhiteSpace(textBoxPID.Text))
{
MessageBox.Show("Please enter the student's PID.");
}
else
{
int pid = Int32.Parse(textBoxPID.Text);
}
// Check for any blank fields
if (string.IsNullOrWhiteSpace(textBoxDevice.Text))
{
MessageBox.Show("Please scan the serial number and device name will populate itself.");
}
// Verify that the PID is approprite in length and type (6 numbers)
else if (textBoxPID.Text.Length != 6 || !int.TryParse(textBoxPID.Text, out PIDparse))
{
MessageBox.Show("The PID entered was not valid. Please enter a valid PID (6 numbers long)");
}
else
{
// Prompts the user for any last changes
if (MessageBox.Show("Are you sure you want to check this device in?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
SQLiteConnection connectAvailable = new SQLiteConnection(Login.connection);
connectAvailable.Open();
String queryAvailable = "SELECT Device FROM CheckOut WHERE Device = '" + textBoxDevice.Text + "';";
SQLiteCommand cmdCheck = new SQLiteCommand(queryAvailable, connectAvailable);
object check = cmdCheck.ExecuteScalar();
SQLiteConnection connectPID = new SQLiteConnection(Login.connection);
connectPID.Open();
String queryPID = "SELECT PID FROM Students WHERE PID = '" + textBoxPID.Text + "';";
SQLiteCommand cmdCheckPID = new SQLiteCommand(queryPID, connectPID);
object checkPID = cmdCheckPID.ExecuteScalar();
// Ensures the student has checked out a device previously
if (checkPID == null)
{
MessageBox.Show("The student is not registered. Please verify that the Student's PID is correct.");
}
// Ensures the device has not already been checked in
else if (check != null)
{
SQLiteConnection connect = new SQLiteConnection(Login.connection);
try
{
// If the student is in the database and the device is not already checked in, update the database and check in the device
String[] queries = new String[3];
queries[0] = "UPDATE History SET CheckInDate = @CheckInDate, ReturnComments = @ReturnComments WHERE SerialNumber = @SerialNumber AND PID = @PID";
queries[1] = "UPDATE Device SET CheckOut = 'False' WHERE SerialNumber = @SerialNumber AND Device = @Device";
queries[2] = "DELETE FROM CheckOut WHERE SerialNumber = @SerialNumber";
foreach (String query in queries)
{
SQLiteCommand cmd = new SQLiteCommand(query, connect);
connect.Open();
cmd.Parameters.AddWithValue("@CheckInDate", date);
cmd.Parameters.AddWithValue("@ReturnComments", textBoxComments.Text);
cmd.Parameters.AddWithValue("@PID", textBoxPID.Text);
cmd.Parameters.AddWithValue("@Device", textBoxDevice.Text);
cmd.Parameters.AddWithValue("@SerialNumber", textBoxSerial.Text);
cmd.ExecuteNonQuery();
connect.Close();
}
}
catch (SQLiteException exception)
{
MessageBox.Show(exception.Message.ToString());
}
finally
{
connectAvailable.Close();
if (connect.State == ConnectionState.Open)
{
connect.Close();
}
}
// Refresh back to Home after New Check Out is completed
this.Visible = false;
Home form = new Home();
form.Show();
this.Dispose();
}
else
{
MessageBox.Show("This device is not checked out. Please verify the device name and serial number on the label.");
}
}
else
{
// Discard changes
}
}
}