// 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); }
// 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 = ""; } }