CodeImp.Gluon.AgendaItem.FromDataRow C# (CSharp) Method

        public static AgendaItem FromDataRow(DataTableRow r)
        {
            AgendaItem i = new AgendaItem();
            i.id = r.GetLong("id");
            i.color = (ColorIndex)r.GetInt("color");
            i.description = r.GetString("description");
            i.startdate = new DateTime(r.GetLong("startdate"));
            i.originstartdate = i.startdate;
            i.duration = new TimeSpan(r.GetLong("duration"));
            i.alarm = r.GetBool("alarm");
            i.alarmdate = new DateTime(r.GetLong("alarmdate"));
            i.recur = (AgendaItemRecur)r.GetInt("recur");
            i.recursions = 0;
            return i;
        }

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