示例#1
0
        // 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();
        }