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 } } }