// Obtain and populate textbox with data
private void generateReport()
{
// Obtain DataTable returned by getAppointments()
DataTable appointments = DataInterface.getAppointments();
// Create view to query
DataView view = new DataView(appointments);
// Create distinct table of consultants
DataTable consultants = view.ToTable(true, "createdBy");
// Iterate through consultants
foreach (DataRow consultant in consultants.Rows)
{
// Add Consultant Name to text box
reportTextBox.AppendText($"Consultant: {consultant["createdBy"].ToString()}\n\n");
// Create array of DataRows containing appointments for consultant being iterated
DataRow[] appointmentArray = appointments.Select($"createdBy = '{consultant["createdBy"].ToString()}'");
// Iterate through appointments associated with this consultant
foreach (DataRow row in appointmentArray)
{
// Append text detailing information about appointments
reportTextBox.AppendText($" ID: {row["appointmentId"]}");
reportTextBox.AppendText($" Title: {row["title"]}");
reportTextBox.AppendText($" CustomerID: {row["customerId"]}");
reportTextBox.AppendText($" Start: {row["start"]}");
reportTextBox.AppendText($" End: {row["end"]}\n\n");
}
}
}
// Month Report Button Click
private void button1_Click(object sender, EventArgs e)
{
// Open new MonthReportForm
DataInterface.DBClose();
monthReport = new MonthReportForm();
MonthReportForm.mainForm = this;
monthReport.Show();
}
// Constructor
public LoginForm()
{
InitializeComponent();
// Obtain user culture and translate appropriately
determineLanguage();
// Generate data into database --USE ONLY IF DATABASE HAS BEEN RESET--
DataInterface.generatePsuedoData();
}
// Cancel Button Click
private void appointmentCancelButton_Click(object sender, EventArgs e)
{
// Show MainForm again, close this form
DataInterface.DBClose();
MainForm.updateAppointmentForm = this;
mainForm.Show();
MainForm.updateAppointmentForm.Close();
}
// Customer Report Button Click
private void customerReportButton_Click(object sender, EventArgs e)
{
// Open new Customer Report
DataInterface.DBClose();
customerReport = new CustomerReportForm();
CustomerReportForm.mainForm = this;
customerReport.Show();
}
// Add Button Click
private void mainAddButton_Click(object sender, EventArgs e)
{
DataInterface.DBClose();
AddAppointmentForm appointmentForm = new AddAppointmentForm();
AddAppointmentForm.mainForm = this;
appointmentForm.Show();
}
// Cancel Button - Return to MainForm
private void appointmentCancelButton_Click(object sender, EventArgs e)
{
// Close database, set MainForms form to this form, show MainForm again, close this form
DataInterface.DBClose();
MainForm.addAppointmentForm = this;
mainForm.Show();
MainForm.addAppointmentForm.Close();
}
// Cancel Button click
private void customerCancelButton_Click(object sender, EventArgs e)
{
// Show CustomerMainForm, close this form
DataInterface.DBClose();
CustomerMainForm.editCustomer = this;
customerForm.Show();
CustomerMainForm.editCustomer.Close();
}
// Edit Button Click
private void customerEditButton_Click(object sender, EventArgs e)
{
// Open new edit customer form and set reference
DataInterface.DBClose();
editCustomer = new EditCustomerForm();
EditCustomerForm.customerForm = this;
editCustomer.Show();
}
// Add Button Click
private void customerAddButton_Click(object sender, EventArgs e)
{
// open add customer form
DataInterface.DBClose();
addCustomer = new AddCustomerForm();
AddCustomerForm.customerForm = this;
addCustomer.Show();
}
// Back Button Click
private void customerBackButton_Click(object sender, EventArgs e)
{
// Return to main form, close this form
DataInterface.DBClose();
MainForm.customerForm = this;
mainForm.Show();
MainForm.customerForm.Close();
}
// Consultant Report Button Click
private void consultantReportButton_Click(object sender, EventArgs e)
{
// Open new Consultant Report Form
DataInterface.DBClose();
consultantReport = new ConsultantReportForm();
ConsultantReportForm.mainForm = this;
consultantReport.Show();
}
// Function to obtain data and populate in DataGridView
private void displayCustomers()
{
DataInterface.DBOpen();
// Query to select desired information to display
String query = "SELECT c.customerId AS ID, c.customerName AS Name, c.active AS Active, a.address AS Address, a.address2 AS Address2, a.city AS City, a.postalCode AS 'Postal Code', a.country AS Country, a.phone AS Phone " +
"FROM customer AS c, (SELECT address.addressId, address.address, address.address2, address.postalCode, address.phone, city.city, country.country FROM address, city, country WHERE address.cityId = city.cityId AND city.countryId = country.countryId) AS a " +
"WHERE c.addressId = a.addressId";
// Pass query and desired DGV to function to obtain data and populate
DataInterface.displayDGV(query, customersDGV);
}
// Edit Button Click
private void mainEditButton_Click(object sender, EventArgs e)
{
// Attempt to selected row
try
{
DataInterface.DBClose();
updateAppointmentForm = new EditAppointmentForm();
EditAppointmentForm.mainForm = this;
updateAppointmentForm.Show();
}
// If unable to locate Appointment Data, display Exception message
catch (DataNotFoundException ex)
{
MessageBox.Show(ex.Message);
}
}
// Delete Button Click
private void mainDeleteButton_Click(object sender, EventArgs e)
{
// Check that selected row is valid
if (selectedAppointmentID != -1)
{
// Delete appointment
DataInterface.deleteAppoinment(selectedAppointmentID);
}
else
{
// If not valid, display message
MessageBox.Show("Please select a customer to delete and try again.");
}
// Refresh appointment data
displayAppointments();
}
// Populate form with existing data
private void setTextBoxText()
{
// Get Dictionary containing data for specific customer selected
selectedCustomer = DataInterface.getCustomerInfo(CustomerMainForm.selectedCustomerID);
// Set the values of each control to the corresponding data
customerIDTextBox.Text = selectedCustomer["ID"].ToString();
customerNameTextBox.Text = selectedCustomer["Name"].ToString();
customerAddressTextBox.Text = selectedCustomer["Address"].ToString();
customerAddress2TextBox.Text = selectedCustomer["Address2"].ToString();
customerZipCodeTextBox.Text = selectedCustomer["ZipCode"].ToString();
customerPhoneTextBox.Text = selectedCustomer["Phone"].ToString();
customerCityTextBox.Text = selectedCustomer["City"].ToString();
customerCountryTextBox.Text = selectedCustomer["Country"].ToString();
customerActiveCheckBox.Checked = (bool)selectedCustomer["Active"];
}
// Delect Button Click
private void customerDeleteButton_Click(object sender, EventArgs e)
{
// If selected ID is valid
if (selectedCustomerID != -1)
{
// Delete customer
DataInterface.deleteCustomer(selectedCustomerID);
}
else
{
// If not valid, display message
MessageBox.Show("Please select a customer to delete and try again.");
}
// Re-display data
customersDGV.Refresh();
displayCustomers();
}
// Valide inputs
private bool inputValidation()
{
// Bool to return indicating if inputs are valid
bool isValid = true;
// Obtain current DateTime for comparison
DateTime currentDateTime = DateTime.Parse(DataInterface.getCurrentDateTime());
// Check if required TextBoxes are empty
if (string.IsNullOrWhiteSpace(appointmentCustomerComboBox.Text))
{
// If empty, change background color to salmon, display message, and indicate inputs are not valid
appointmentCustomerComboBox.BackColor = Color.Salmon;
MessageBox.Show("Customer field cannot be empty. Please select a customer and try again.");
isValid = false;
}
if (string.IsNullOrWhiteSpace(appointmentTitleTextBox.Text))
{
appointmentTitleTextBox.BackColor = Color.Salmon;
MessageBox.Show("Title field cannot be empty. Please enter a title and try again.");
isValid = false;
}
if (string.IsNullOrWhiteSpace(appointmentDescriptionTextBox.Text))
{
appointmentDescriptionTextBox.BackColor = Color.Salmon;
MessageBox.Show("Description field cannot be empty. Please enter a description and try again.");
isValid = false;
}
// Check if appointment is before the current time
if (appointmentStartDate.Value < currentDateTime || appointmentEndDate.Value < currentDateTime)
{
// If appointment is before current time, display message and set inputs to invalid
MessageBox.Show("Start and end dates/times must occur after the current date/time.");
isValid = false;
}
if (appointmentEndDate.Value < appointmentStartDate.Value)
{
MessageBox.Show("End time must be after start time.");
isValid = false;
}
// Return whether inputs are valid or not -- True is valid, false is not valid
return(isValid);
}
// Populate form with existing data
private void populateData()
{
// Obtain dictionary of appointment information
selectedAppointment = DataInterface.getAppointmentInfo(MainForm.selectedAppointmentID);
// Populate customer ComboBox
DataInterface.populateComboBox(appointmentCustomerComboBox);
// Obtain data from form needed to update appointment
appointmentIDTextBox.Text = selectedAppointment["ID"];
appointmentCustomerComboBox.SelectedValue = Convert.ToInt32(selectedAppointment["customerID"]);
appointmentTitleTextBox.Text = selectedAppointment["Title"];
appointmentLocationTextBox.Text = selectedAppointment["Location"];
appointmentContactTextBox.Text = selectedAppointment["Contact"];
appointmentURLTextBox.Text = selectedAppointment["URL"];
appointmentStartDate.Value = DateTime.Parse(selectedAppointment["Start"]);
appointmentEndDate.Value = DateTime.Parse(selectedAppointment["End"]);
appointmentDescriptionTextBox.Text = selectedAppointment["Description"];
}
// Save Button click
private void appointmentSaveButton_Click(object sender, EventArgs e)
{
// If inputs are valid, continue
if (inputValidation())
{
// If appointment is within business hours or user clicked 'Yes', continue
if (checkBusinessHours(appointmentStartDate.Value, appointmentEndDate.Value))
{
// Store data within form
appointmentID = DataInterface.getNextID("appointmentId", "appointment", DataInterface.getAppointmentIDList());
customerID = selectedCustomerID;
title = appointmentTitleTextBox.Text;
description = appointmentDescriptionTextBox.Text;
location = appointmentLocationTextBox.Text;
contact = appointmentContactTextBox.Text;
url = appointmentURLTextBox.Text;
start = appointmentStartDate.Value.ToUniversalTime().ToString("u");
end = appointmentEndDate.Value.ToUniversalTime().ToString("u");
// Check if appointment being created overlaps an existing appointment
if (checkForOverlap(DateTime.Parse(start), DateTime.Parse(end)) == true)
{
// If there is overlap, prompt user and cancel save
MessageBox.Show("The appointment you are trying to add overlaps an existing appointment." +
"\n\nPlease correct and try again.");
return;
}
// If no overlap, continue with save
else
{
// Use DataInterface method to pass data to query for creating new appointment
DataInterface.createAppointment(customerID, title, description, location, contact, url, start, end, currentUser);
// Set MainForms form to this form, show mainForm again, close this form
MainForm.addAppointmentForm = this;
mainForm.Show();
MainForm.addAppointmentForm.Close();
}
}
}
// If inputs are not valid or user chose to not allow it outside business hours, cancel save
return;
}
// Populate DataGridView with appointment data
public void displayAppointments()
{
// Clear DataTable of any prior information
appointmentsDT.Clear();
String query = "";
// Obtain selected date from MonthCalendar
DateTime selectedDate = appointmentCalendar.SelectionRange.Start.ToUniversalTime();
// Determine sunday and saturday for week view, convert to universal time for accurate comparison to DB values
DateTime sunday = selectedDate.AddDays(-(int)selectedDate.DayOfWeek).ToUniversalTime();
DateTime saturday = selectedDate.AddDays(-(int)selectedDate.DayOfWeek + (int)DayOfWeek.Saturday).ToUniversalTime();
// Check which view is selected and query data accordingly
if (dgvViewMonthRadioButton.Checked)
{
query = $"SELECT a.appointmentId AS ID, c.customerName AS 'Customer Name', a.title AS Title, a.start AS Start, a.end AS End FROM appointment AS a, customer AS c WHERE c.customerId = a.customerId AND MONTH(a.start) = '{appointmentCalendar.SelectionStart.Month}' AND YEAR(a.start) = '{appointmentCalendar.SelectionStart.Year}' AND a.createdBy = '{DataInterface.getCurrentUserName()}' ORDER BY a.start";
}
else if (dgvViewWeekRadioButton.Checked)
{
query = $"SELECT a.appointmentId AS ID, c.customerName AS 'Customer Name', a.title AS Title, a.start AS Start, a.end AS End FROM appointment AS a, customer AS c WHERE c.customerId = a.customerId AND a.start >= '{sunday.ToString("yyyy-MM-dd hh:MM:ss")}' - INTERVAL 3 MINUTE AND a.start < '{saturday.AddHours(24).ToString("yyyy-MM-dd hh:MM:ss")}' - INTERVAL 3 MINUTE AND a.createdBy = '{DataInterface.getCurrentUserName()}' ORDER BY a.start";
}
else if (dgvViewDayRadioButton.Checked)
{
query = $"SELECT a.appointmentId AS ID, c.customerName AS 'Customer Name', a.title AS Title, a.start AS Start, a.end AS End FROM appointment AS a, customer AS c WHERE c.customerId = a.customerId AND a.start >= '{selectedDate.ToString("yyyy-MM-dd hh:MM:ss")}' - INTERVAL 3 MINUTE AND a.start < '{selectedDate.AddHours(24).ToString("yyyy-MM-dd hh:MM:ss")}' - INTERVAL 3 MINUTE AND a.createdBy = '{DataInterface.getCurrentUserName()}' ORDER BY a.start";
}
// Execute query and fill DataTable
DataInterface.DBOpen();
MySqlDataAdapter adp = new MySqlDataAdapter(query, DataInterface.conn);
MySqlCommandBuilder cmd = new MySqlCommandBuilder(adp);
adp.Fill(appointmentsDT);
// Convert start and end times to local time
DataInterface.convertToLocal(appointmentsDT, "Start");
DataInterface.convertToLocal(appointmentsDT, "End");
// Set DataSource for DataGridView to display data within DataTable
appointmentsDGV.DataSource = appointmentsDT;
appointmentsDGV.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
DataInterface.DBClose();
}
// Check if any appointments are within 15 minutes of occurring
public static void checkForReminders()
{
// Iterate through appointments in DataTable
foreach (DataRow row in appointmentsDT.Rows)
{
// Obtain start time for each appointment
DateTime startTime = DateTime.Parse(row["Start"].ToString());
// Obtain the current time
DateTime currentTime = DateTime.Parse(DataInterface.getCurrentDateTime());
// Set 15 minute mark for comparison
TimeSpan reminderMark = new TimeSpan(0, 15, 0);
TimeSpan appointmentPassed = new TimeSpan(0, 0, 0);
// Obtain difference between start time and the current time
TimeSpan difference = startTime.Subtract(currentTime);
// If difference is less than 15 minutes but has not passed the current time, display reminder
if (difference <= reminderMark && difference > appointmentPassed)
{
MessageBox.Show($"The event, {row["Title"].ToString()}, with customer, {row["Customer Name"].ToString()}, is starting soon.");
}
}
}
// Validate inputs before saving
private bool inputValidation()
{
// Variable determining if inputs are valid
bool isValid = true;
// Obtain current DateTime for comparing
DateTime currentDateTime = DateTime.Parse(DataInterface.getCurrentDateTime());
// Check if required fields contain text
if (string.IsNullOrWhiteSpace(appointmentCustomerComboBox.Text))
{
// If invalid, change BackColor, prompt user, and set isValid to false
appointmentCustomerComboBox.BackColor = Color.Salmon;
MessageBox.Show("Customer field cannot be empty. Please select a customer and try again.");
isValid = false;
}
if (string.IsNullOrWhiteSpace(appointmentTitleTextBox.Text))
{
appointmentTitleTextBox.BackColor = Color.Salmon;
MessageBox.Show("Title field cannot be empty. Please enter a title and try again.");
isValid = false;
}
if (string.IsNullOrWhiteSpace(appointmentDescriptionTextBox.Text))
{
appointmentDescriptionTextBox.BackColor = Color.Salmon;
MessageBox.Show("Description field cannot be empty. Please enter a description and try again.");
isValid = false;
}
if (appointmentStartDate.Value < currentDateTime || appointmentEndDate.Value < currentDateTime)
{
MessageBox.Show("Start and end dates/times must occur after the current date/time.");
isValid = false;
}
if (appointmentEndDate.Value < appointmentStartDate.Value)
{
MessageBox.Show("End time must be after start time.");
isValid = false;
}
// return bool to show validity -- true is valid, false is not valid
return(isValid);
}
// Save Button Click
private void appointmentSaveButton_Click(object sender, EventArgs e)
{
// Check if inputs are valid, if so, continue
if (inputValidation())
{
// Check if edited times are within business hours or approved by user, if so, continue
if (AddAppointmentForm.checkBusinessHours(appointmentStartDate.Value, appointmentEndDate.Value))
{
// Set data from form to variables
appointmentID = MainForm.selectedAppointmentID;
customerID = selectedCustomerID;
title = appointmentTitleTextBox.Text;
description = appointmentDescriptionTextBox.Text;
location = appointmentLocationTextBox.Text;
contact = appointmentContactTextBox.Text;
url = appointmentURLTextBox.Text;
start = appointmentStartDate.Value.ToUniversalTime().ToString("u");
end = appointmentEndDate.Value.ToUniversalTime().ToString("u");
// Check if there is overlap with times provided
if (AddAppointmentForm.checkForOverlap(DateTime.Parse(start), DateTime.Parse(end)) == true)
{
// If there is overlap, display message and cancel save
MessageBox.Show("The appointment you are trying to add overlaps an existing appointment." +
"\n\nPlease correct and try again.");
return;
}
else
{
// If no overlap, call update method to update appointment, pass variables obtained from form
DataInterface.updateAppointment(appointmentID, customerID, title, description, location, contact, url, start, end);
MainForm.updateAppointmentForm = this;
mainForm.Show();
MainForm.updateAppointmentForm.Close();
}
}
}
}
// Generate Log of Logins
private void recordLogin(string username)
{
string path = "C:/SchedulingApp/Logs";
string fileName = "log.txt";
string fullPath = path + fileName;
string logEntry = $"{username} logged in at {DataInterface.getCurrentDateTime()}" + Environment.NewLine;
DirectoryInfo dir = new DirectoryInfo(path);
try
{
// Check if folder exists, if not, create
if (!dir.Exists)
{
dir.Create();
}
// Append logEntry to file
File.AppendAllText(fullPath, logEntry);
}
catch (Exception e)
{
MessageBox.Show("An error occurred when generating log. " + $"\n\n{e.Message}" + $"\n\n{e.StackTrace}");
}
}
// Save Button click
private void customerSaveButton_Click(object sender, EventArgs e)
{
// Check if inputs are valid
if (validateInputs())
{
// If valid, obtain data from form controls
ID = DataInterface.getNextID("customerId", "customer", DataInterface.getCustomerIDList());
name = customerNameTextBox.Text;
address = customerAddressTextBox.Text;
address2 = customerAddress2TextBox.Text;
city = customerCityTextBox.Text;
zipCode = customerZipCodeTextBox.Text;
country = customerCountryTextBox.Text;
phone = customerPhoneTextBox.Text;
active = customerActiveCheckBox.Checked;
// Send data to DataInterface to create new customer
DataInterface.createCustomer(name, address, city, country, zipCode, phone, active, currentUser, address2);
// Set CustomerMainForm's reference to this form, show CustomerMainForm, close this form
CustomerMainForm.addCustomer = this;
customerForm.Show();
CustomerMainForm.addCustomer.Close();
}
}
// Close Button Click
private void reportCloseButton_Click(object sender, EventArgs e)
{
DataInterface.DBClose();
mainForm.Show();
MainForm.consultantReport.Close();
}
// Form constructor
public AddAppointmentForm()
{
InitializeComponent();
// Populate ComboBox with customers
DataInterface.populateComboBox(appointmentCustomerComboBox);
}
// Form Load
private void MainForm_Load(object sender, EventArgs e)
{
// Display welcome text
mainWelcomeLabel.Text = $"Welcome, {DataInterface.getCurrentUserName()}!";
}
// Login Button CLick
private void loginLoginButton_Click(object sender, EventArgs e)
{
// Obtain login information
string userName = loginUsernameTextBox.Text;
string password = loginPasswordTextBox.Text;
int userID;
// Check if username or password are empty
if (String.IsNullOrWhiteSpace(loginUsernameTextBox.Text) || String.IsNullOrWhiteSpace(loginPasswordTextBox.Text))
{
// Display appropriate message based on language
if (currentCulture == "fr-FR")
{
MessageBox.Show("Le nom d'utilisateur et le mot de passe ne peuvent pas être vides");
}
else
{
MessageBox.Show("Username and password cannot be empty");
}
return;
}
// Open database connection
DataInterface.DBOpen();
// Build Query
MySqlCommand cmd = new MySqlCommand($"SELECT userId FROM user WHERE userName = '******' AND password = '******'", DataInterface.conn);
MySqlDataReader reader = cmd.ExecuteReader();
// If matching data is present, set current user information and open MainForm
if (reader.HasRows)
{
// Read rows returned
reader.Read();
// Set userID based on row returned
userID = Convert.ToInt32(reader[0]);
// set current user information
DataInterface.setCurrentUserID(userID);
DataInterface.setCurrentUserName(userName);
// close database connection
reader.Close();
DataInterface.DBClose();
// Hide Login form and open MainForm
MainForm mainForm = new MainForm();
MainForm.loginForm = this;
this.Hide();
mainForm.Show();
loginUsernameTextBox.Text = "";
loginPasswordTextBox.Text = "";
recordLogin(DataInterface.getCurrentUserName());
}
// Username/Password do not match information in database
else
{
// Dipslay language appropriate error message
MessageBox.Show(errorMessage);
loginPasswordTextBox.Text = "";
}
}
