// This sets up the days for the given week public void SetupWeek(DateTime week) { GregorianCalendar calendar = new GregorianCalendar(); weekstart = new DateTime(week.Year, week.Month, week.Day); weekstart = weekstart.AddDays(-Tools.DayOfWeekInt(week.DayOfWeek)); weekend = new DateTime(weekstart.Year, weekstart.Month, weekstart.Day); weekend = weekend.AddDays(7); weekend = weekend.AddTicks(-1); // Setup interface yearlabel.Text = weekstart.Year.ToString(); monthlabel.Text = weekstart.ToString("MMMM"); weeklabel.Text = calendar.GetWeekOfYear(weekstart, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday).ToString(); // Fetch the agenda items for this week List<AgendaItem> items = General.Agenda.GetItems(weekstart, weekend); // Go for all days in the week for(int wd = 0; wd < 7; wd++) { // Determine begin and end date for this day DateTime dstart = new DateTime(weekstart.Year, weekstart.Month, weekstart.Day); dstart = dstart.AddDays(wd); DateTime dend = new DateTime(dstart.Year, dstart.Month, dstart.Day); dend = dend.AddDays(1); dend = dend.AddTicks(-1); // Find the items for this day List<AgendaItem> dayitems = new List<AgendaItem>(); AgendaItemSorter sorter = new AgendaItemSorter(); foreach(AgendaItem i in items) if(((i.startdate + i.duration) >= dstart) && (i.startdate <= dend)) dayitems.Add(i); dayitems.Sort(sorter); // Setup control daydisplays[wd].SetupDate(dstart); daydisplays[wd].SetupItems(dayitems, dstart); daydisplays[wd].Visible = false; } showtimer.Start(); }
// This returns all agenda items in the given range // Returns null on database error public List <AgendaItem> GetItems(DateTime from, DateTime to) { TimeSpan span = to - from; General.DB.ConnectSafe(); // Make the WHERE clause for weekly recurring items string ww = "(`recur` = '" + (int)AgendaItemRecur.Weekly + "')"; if (span.Days < 7) { // Add limitations for the days of the weeks between 'from' and 'to' if (to.DayOfWeek >= from.DayOfWeek) { // Range within a single week ww += " AND (`dayofweek` >= '" + from.DayOfWeek + "' AND `dayofweek` <= '" + to.DayOfWeek + "')"; } else { // Range crossing into the next week ww += " AND (`dayofweek` >= '" + to.DayOfWeek + "' OR `dayofweek` <= '" + from.DayOfWeek + "')"; } } // Make the WHERE clause for monthly recurring items string wm = "(`recur` = '" + (int)AgendaItemRecur.Monthly + "')"; if (span.Days < 31) { // Add limitations for the days of the month between 'from' and 'to' if (to.Day >= from.Day) { // Range within a single month wm += " AND (`dayofmonth` >= '" + from.Day + "' AND `dayofmonth` <= '" + to.Day + "')"; } else { // Range crossing into the next month wm += " AND (`dayofmonth` >= '" + to.Day + "' OR `dayofmonth` <= '" + from.Day + "')"; } } // Make the WHERE clause for annually recurring items string wa = "(`recur` = '" + (int)AgendaItemRecur.Annually + "')"; if (span.Days < 366) { // Add limitations for the months of the year between 'from' and 'to' if (to.Month >= from.Month) { // Range within a single year wa += " AND (`month` >= '" + from.Month + "' AND `month` <= '" + to.Month + "')"; } else { // Range crossing into the next year wa += " AND (`month` >= '" + to.Month + "' OR `month` <= '" + from.Month + "')"; } // Shorter than a month? Add limitations for the day of the month. if (span.Days < 31) { // Add limitations for the days of the month between 'from' and 'to' if (to.Day >= from.Day) { // Range within a single month wa += " AND (`dayofmonth` >= '" + from.Day + "' AND `dayofmonth` <= '" + to.Day + "')"; } else { // Range crossing into the next month wa += " AND (`dayofmonth` >= '" + to.Day + "' OR `dayofmonth` <= '" + from.Day + "')"; } } } string q = "SELECT * FROM `agenda` WHERE " + "((`startdate` + `duration`) >= '" + from.Ticks + "') AND " + "(`startdate` <= '" + to.Ticks + "') AND ((`recur` = '0') OR (" + ww + ") OR (" + wm + ") OR (" + wa + "));"; DataTable t = General.DB.Query(q); if (t != null) { List <AgendaItem> list = new List <AgendaItem>(t.Count); // Make the full list (expand recurring items) foreach (DataTableRow r in t) { AgendaItem item = AgendaItem.FromDataRow(r); if (item.recur != AgendaItemRecur.None) { AddRecurringItem(list, item, from, to); } else { list.Add(item); } } // Sort by startdate AgendaItemSorter sorter = new AgendaItemSorter(); list.Sort(sorter); General.DB.Disconnect(); return(list); } else { // Failed! General.DB.Disconnect(); return(null); } }