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