// Check if times overlap any existing appointments
public static bool checkForOverlap(DateTime start, DateTime end)
{
// Bool returned to show if overlap exists
bool overlap = false;
// Create DataTable containing appointments
DataTable appointments = DataInterface.getAppointments(DataInterface.getCurrentUserName());
// Iterate through DataTable
foreach (DataRow row in appointments.Rows)
{
// Obtain start and end times
DateTime appointmentStart = DateTime.Parse(row["start"].ToString()).ToLocalTime();
DateTime appointmentEnd = DateTime.Parse(row["end"].ToString()).ToLocalTime();
// Lambda expression to check if provided time is within start and end times of appointment being evaluated
overlaps = (time) => { return(time <= appointmentEnd && time >= appointmentStart); };
// Use lambda to check if the provided start and end times in the form overlap appointment being evaluated
if (overlaps(start) || overlaps(end))
{
// if either start or end are within an existing appointment, set overlap to true to indicate overlap
overlap = true;
}
}
// return state of overlap -- true indicates overlap, false indicates no overlap
return(overlap);
}
// 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();
}
// 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 = "";
}
}
// Form Load
private void MainForm_Load(object sender, EventArgs e)
{
// Display welcome text
mainWelcomeLabel.Text = $"Welcome, {DataInterface.getCurrentUserName()}!";
}