CodeImp.Gluon.AgendaManager.GetItems C# (CSharp) Method

        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;
            }
        }