search envelope-o feed check
Home Unanswered Active Tags New Question
user comment-o

Retrieve an Event (Possibly Recurrent) given Date

Asked by John
7 years ago.

Hi there,

First of all... great work on this component. I saw it on CodeProject a few years back and really wanted to use it but at the time it didn't have drag & drop and resize. Now of course it does and you've done an amazing job getting it where it is now! :)

I'm evaluating the Day Pilot Pro and really only going to use the "MonthView" at this stage. I want to use DayPilot for it's ability to manipulate recurrent events. Our application is for a Digital Signage system. We allow users to select one thing to play for a particular day, so we only want to have one event per day. In other words each event will only ever run from 00:00 -> '24:00'. All that is fine and working properly thanks.

What happens now is each sign will contact the server with it's current date and ask "What event should I play today?". I can see with a non-recurrent event I can do a simple sql query on 'eventstart' and 'eventend'. But when I get into recurrent events (using your special varchar field)... how will I query it?

The call from the signs is answered by a web service, so I figured I'd do a database query and return the event id. But is this possible? Or should I really be loading the MonthView (or other) class up and get it to parse the database and return the result?

Many thanks for your time and well done again on the DayPilot!

John

Answer posted by John
7 years ago.

Hi again,

I'm not sure if this is the correct way of doing it as it seems like quite a bit of coding to get at the answer! I've probably missed something very obvious along the way but at least this code works. I will post it here in case anyone else may need it.

Remember for my application I only ever let the user have one event per day and the event runs all day. So the following code will allow you to find one event per day. For example if you were looking to find the event for '09-Sep-2011' this will return the first event it finds on that day.

public int GetEventIdByDate(DateTime StartDate)
{
// Default Result
int result = 0;

// Create the End Dates (I'm only ever looking for one particular day)
DateTime EndDate = StartDate.AddDays(1);
DateTime EndDatePlusOneDay = EndDate.AddDays(1);

// Grab the events (including recurring) for the date specified in StartDate
string Sql = "SELECT e.[id], [name], e.[eventstart], e.[eventend], recurrence FROM [Events] e WHERE NOT ((e.[eventend] <= '" + StartDate.ToLongDateString() + "') OR (e.[eventstart] >= '" + EndDatePlusOneDay.ToLongDateString() + "')) or (e.[recurrence] is not null and e.[eventstart] < '" + StartDate.ToLongDateString() + "')";

// The RunSQL bit is just a utility function to execute SQL and return a DataSet
DataTable dt = this.RunSQL(Sql).Tables[0];

// Create a DayPilotMonth Control. I guess it should really be a calendar control where you can restrict the days to just 1, but it works for my purposes
DayPilot.Web.Ui.DayPilotMonth dm = new DayPilot.Web.Ui.DayPilotMonth();
dm.DataEndField = "eventend";
dm.DataStartField = "eventstart";
dm.DataTextField = "name";
dm.DataValueField = "id";
dm.DataTagFields = "id, name";
dm.DataSource = this.RunSQL(Sql).Tables[0];
dm.StartDate = new DateTime(StartDate.Year, StartDate.Month, 1);

// Set the Recurrent Properties
dm.DataRecurrenceField = "recurrence";
dm.RecurrentEventImage = "~/images/recur10x9.png";
dm.RecurrentEventExceptionImage = "~/images/recurex10x9.png";

// Set a BeforeEventRender so we can collect the events into a list
dm.BeforeEventRender += this.DayPilot_BeforeEventRender;
dm.DataBind();

// We're going to render the Control so we can get the events (including recurring)
StringBuilder sb = new StringBuilder();
StringWriter tw = new StringWriter(sb);
HtmlTextWriter hw = new HtmlTextWriter(tw);

// We don't want to render the control on our actual page, so create a placeholder one
Page pg = new Page();
pg.Controls.Add(dm);
gEvents.Clear();
dm.RenderControl(hw);

// Now we've created our list in function DayPilot_BeforeEventRender, we can get rid of the page and control
pg.Controls.Clear();
dm.Dispose();
pg.Dispose();

// Let's see if we've found an event for the day in question. For my purposes there's only ever one event per day.
if (gEvents.Count > 0)
{
for (int i=0; i < gEvents.Count; i++)
{
// Does this event match the dat we're looking for?
if (gEvents[i].Start == StartDate)
{
// Grab the Id so we can return it.
result = Convert.ToInt32(gEvents[i].Tag["id"]);
break;
}
}
}

// Returns 0 or the Event Id if it found it.
return result;
}

protected void DayPilot_BeforeEventRender(object sender, DayPilot.Web.Ui.Events.Month.BeforeEventRenderEventArgs e)
{
// Add each event to a globally defined list collection... the declaration is below..
// List<DayPilot.Web.Ui.Events.Month.BeforeEventRenderEventArgs> gEvents = new List<DayPilot.Web.Ui.Events.Month.BeforeEventRenderEventArgs>();
gEvents.Add(e);
}

Answer posted by Dan Letecky [DayPilot]
7 years ago.

There is also a special class for expanding the recurrent events from a DataTable:

DataTable output = RecurrenceExpander.Expand(source, "recurrence", "start", "end", "id",
"master", DayPilotMonth1.VisibleStart, DayPilotMonth1.VisibleEnd);

The "source" parameter is a DataTable with raw data. You need to supply the key field mappings (corresponding to DataRecurrenceField, DataStartField, DataEndField, DataValueField), a name of a new field that will hold the id of the recurrent master event for the expanded events - "master", and the time range.

See also:
http://www.daypilot.org/daypilot-pro-6-4.html
(RecurrenceExpander section)

This question is more than 3 months old and has been closed. Please create a new question if you have anything to add.