Calendar View from Dynamic Data and Content Channels

  • By Daniel Hazelbaker One Year Ago

Overview

Like many churches, we have what we call the "Pastor Of the Day" (or POD). This is the pastor that deals with walk-ins and such so others can focus on their other duties.

But that's not really important to this demo. The point is we use a Content Channel to "schedule" these PODs and then a small block to display the next 10 days' worth of PODs on the staff dashboard. But we wanted more... of course we did. We wanted a way to view and print a month-up calendar view of the schedule. So, we built something. It uses a SQL query to return the data (3 columns worth) to be displayed in the month and then some Lava to format the data into something that resembles a calendar.

SQL

Okay so first up, the query to get the data from the database. There are actually two queries happening below. The first query is what returns the actual data to be displayed. The second query returns some useful data to the Lava for determing start of the month, end of month, etc. I discovered that I couldn't find a way to get these values from pure Lava, or if there was I couldn't find it.

DECLARE @Now DATE = {% if PageParameter.Date and PageParameter.Date != empty %}'{{ PageParameter.Date }}'{% else %}GETDATE(){% endif %}

SELECT
	[CCI].[StartDateTime] AS [Date],
	[Pastor].[NickName] + ' ' + [Pastor].[LastName] AS [Title],
	[CCI].[Content] AS [Details]
	FROM [ContentChannelItem] AS [CCI]
	LEFT JOIN [AttributeValue] AS [PastorGuid] ON [PastorGuid].[EntityId] = [CCI].[Id] AND [PastorGuid].[AttributeId] = 5555
	LEFT JOIN [PersonAlias] AS [PastorAlias] ON [PastorAlias].[Guid] = TRY_CAST([PastorGuid].[Value] AS UNIQUEIDENTIFIER)
	LEFT JOIN [Person] AS [Pastor] ON [Pastor].[Id] = [PastorAlias].[PersonId]
	WHERE [CCI].[ContentChannelId] = 14
	  AND YEAR([CCI].[StartDateTime]) = YEAR(@Now)
	  AND MONTH([CCI].[StartDateTime]) = MONTH(@Now)
	ORDER BY [StartDateTime]
;
SELECT
    DATEPART(dw, DATEFROMPARTS(YEAR(@Now), MONTH(@Now), 1)) AS [Day1OfWeek],
    DATEPART(d, DATEADD( DAY, -1, DATEADD( MONTH, 1, DATEFROMPARTS(YEAR(@Now), MONTH(@Now), 1) ) ) ) AS [LastDay],
    DATEADD(MONTH, -1, @Now) AS [PreviousMonth],
    @Now AS [ThisMonth],
    DATEADD(MONTH, 1, @Now) AS [NextMonth]

So if you notice the data returned just needs three columns.

  1. Date - The day of the month this content should display on.
  2. Title - The larger text title for this content.
  3. Details - The smaller text that will be displayed for the content, if any.

One important thing to note is that order is important, so make sure you order your results by the Date column. Also, if you notice the very first line is doing some Lava too. This takes the URL parameter Date and uses that for determining the month of data to be displayed, otherwise it uses the current date. This will become important later.

Lava

Now we get to the fun (read: ugly) stuff. Configurating the Dynamic Data block is pretty simple. Nothing really matters. Most of those configuration options are for use in the Grid, which we are not using. So jumping in you're going to copy and paste this Lava code into the Formatted Output. We are going to start by looking at various sections of the code and then at the end you can click the Show Full Code button to see it all together for copy & paste.

{% comment %} ## Assign initial values to friendly variable names ## {% endcomment %}
{% assign padInitialDays = table2.rows[0].Day1OfWeek | Minus:1 %}
{% assign lastDay = table2.rows[0].LastDay | Minus:1 %}
{% assign previousMonth = table2.rows[0].PreviousMonth %}
{% assign thisMonth = table2.rows[0].ThisMonth %}
{% assign nextMonth = table2.rows[0].NextMonth %}
{% assign dayOfWeek = 1 %}
{% assign currentDay = 1 %}
{% assign today = 'Now' | Date:'yyyyMMdd' %}

This first section takes data from the second SELECT query and stores it into friendly variable names. We also setup a few counter variables that we will use.

  • padInitialDays - This tells us how many days we need to pad the calendar until we get to the first day of the month. For example if the 1st is on a Wednesday then we need to pad 3 days.
  • lastDay - Contains the last day number of the month (minus 1 because of the way Lava handles loops).
  • previousMonth - The date that represents the previous month for the clickable button.
  • thisMonth - The date that represents this month for display at the top of the calendar.
  • nextMonth - The date that represents the next month for the clickable button.
  • dayOfWeek - Counter used for knowing what day of the week we are building and if a new row (week) is needed.
  • currentDay - Counter to know what day of the month we are building.
  • today - String to compare against later to determine if the cell being built is 'today'.

<style>
tr.calendar-header {
    background-color: #edeae6;
}
tr.calendar-header > th {
    text-align: center;
    padding: 8px 0px;
    border-bottom: 1px solid #d8d1c8;
}
tr.calendar-header > th.calendar-previous {
    text-align: left;
    padding-left: 20px;
}
tr.calendar-header > th.calendar-current {
    font-size: 1.25em;
}
tr.calendar-header > th.calendar-next {
    text-align: right;
    padding-right: 20px;
}
tr.calendar-header > th.calendar-previous > a,
tr.calendar-header > th.calendar-next > a {
    font-weight: initial;
}

.calendar-day {
    border: 1px solid #d8d1c8;
    vertical-align: top;
    width: 14.2857%;
}
.calendar-day.today {
    background-color: #ee7624;
    color: white;
}
.calendar-day > div {
    min-height: 90px;
    padding: 6px;
}
.calendar-dom {
    font-size: 120%;
    font-weight: bold;
    margin-bottom: 4px;
}

.calendar-item {
}
.calendar-details {
    font-size: 80%;
    display: block;
    padding-left: 8px;
}
</style>

There isn't much to say here. This is the CSS styles we use to display the calendar. If you know CSS and want to customize you can read it over the content and should be able to figure out what to change. A few common ones you may want to customize:

  • .calendar-day - Styles a single "day" of the calendar view.
  • .calendar-day.today - Overrides the styles for "today" to make it pop on the calendar.
  • .calendar-item - Styles the Title column of the results.
  • .calendar-details - Styles the Details column of the results.
<div class="panel panel-block">
    <div class="panel-heading">
        <h3 class="panel-title"><i class="fa fa-calendar-o"></i> POD Schedule</h3>
    </div>

    <table style="width: 100%;">

Setup the Bootstrap panel with a calendar icon and the text POD Schedule. You will probably want to change this text to match whatever calendar you are building.


        {% comment %} ## Put in the links to the previous and next month along with the name of this month. ## {% endcomment %}
        <tr class="calendar-header">
            <th colspan="2" class="calendar-previous">
                <a href="?Date={{ previousMonth | Date:'yyyy-MM' }}-01"><i class="fa fa-chevron-left"></i> Previous</a>
            </th>
            <th colspan="3" class="calendar-current">
                {{ thisMonth | Date:'MMMM yyyy' }}
            </th>
            <th colspan="2" class="calendar-next">
                <a href="?Date={{ nextMonth | Date:'yyyy-MM' }}-01">Next <i class="fa fa-chevron-right"></i></a>
            </th>
        </tr>

This is the first header row that displays the Previous and Next links as well as the current month and year being viewed. But hold on. You heard right. This whole setup lets you click buttons to move around between the months you are viewing. Remember earlier we talked about the URL parameter of `Date`? That is where this comes into play. Using the results from the second SQL query for the previous and next month dates, we build a dynamic URL with the proper date to allow the user to simply click through the months.


        {% comment %} ## Put in weekday names. ## {% endcomment %}
        <tr class="calendar-header">
            <th>Sunday</th>
            <th>Monday</th>
            <th>Tuesday</th>
            <th>Wednesday</th>
            <th>Thursday</th>
            <th>Friday</th>
            <th>Saturday</th>
        </tr>

This is pretty straight forward. A second header row that simplay contains the weekday names. No you can't simply change the order of the columns if you want your calendar to start on Monday. That's a more intense code change and you can play around to figure that out for yourself. Hint, it will require some changes to the SQL which determines the day of the week that the 1st starts on.


        <tr>
            {% comment %} ## Add a single colspan cell to cover the days not in this month yet. ## {% endcomment %}
            {% if padInitialDays > 0 %}
                <td class="calendar-day" colspan="{{ padInitialDays }}"></td>
                {% assign dayOfWeek = dayOfWeek | Plus:padInitialDays %}
            {% endif %}

            {% comment %} ## Start with day 1. ## {% endcomment %}
            {% capture dayText %}{{ thisMonth | Date:'yyyyMM' }}01{% endcapture %}
            <td class="calendar-day {% if dayText == today %}today{% endif %}"><div>
                <div class="calendar-dom">{{ currentDay }}</div>

Now we are starting to get into some interesting stuff. Let's call this the vegetables of the stew. We create a single table cell to handle all the "days that aren't part of this month". Remember we talked about the month starting on Wednesday and needing to pad 3 days? Well this is the pad. We create a single cell that spans those 3 days. Then we build the real "day 1" cell and prepare for our meat.

Notice that <code>if</code> statement in the <code>td</code> element? If the first of the month is today then we add
an extra CSS class called <code>today</code> so that it can be styled differently to make it pop out to the user more.


                {% for row in table1.rows %}
                    {% assign day = row.Date | Date:'d' %}

                    {% comment %} ## Calculate and then add any "empty" days that were skipped in the result set. ## {% endcomment %}
                    {% assign padDays = day | Minus:currentDay %}
                    {% for d in (1..padDays) %}
                        </div>
                        </td>
                        {% if dayOfWeek == 7 %}
                            </tr><tr>
                            {% assign dayOfWeek = 0 %}
                        {% endif %}

                        {% assign dayOfWeek = dayOfWeek | Plus:1 %}
                        {% assign currentDay = currentDay | Plus:1 %}

                        {% capture dayText %}{{ thisMonth | Date:'yyyyMM' }}{% if currentDay < 10 %}0{% endif %}{{ currentDay }}{% endcapture %}
                        <td class="calendar-day {% if dayText == today %}today{% endif %}"><div>
                        <div>
                        <div class="calendar-dom">{{ currentDay }}</div>
                    {% endfor %}

                    <div class="calendar-item">
                        {{ row.Title }}
                        {% if row.Details != empty %}<span class="calendar-details">{{ row.Details }}</div>{% endif %}
                    </div>
                {% endfor %}

Alright, I told you the meat was coming. This might take some extra chewing so get those molars ready 'cuz we got some grinding to do. This section handles all the real data returned by your first query. We loop through each item and process what needs to be displayed. It looks ominous, but there is actually very little going on.

Notice the inner for loop that is happening? That is more padding. We may get multiple rows for the same day or we may skip days entirely. This inner loop handles the case of skipping days (or even just moving from one day to the next). So when we need to skip days we close out the calendar-day div and the td cell. Then if we are currently on the last day of the week we also close and and start a new tr row for the next week.

Next we increment our counters and then build the new calendar-day cell. Again checking if this cell is for "today".

Finally in this little block, once we are done with the inner for loop we put the contents of the SQL result row into the cell, wrapped inside calendar-item and calendar-details classes. That is all there is to really be chewed up.


                {% comment %} ## Add any empty days to make up the rest of the month. ## {% endcomment %}
                {% for d in (currentDay..lastDay) %}
                    </div>
                    </td>
                    {% if dayOfWeek == 7 %}
                        </tr><tr>
                        {% assign dayOfWeek = 0 %}
                    {% endif %}

                    {% assign dayOfWeek = dayOfWeek | Plus:1 %}
                    {% assign currentDay = currentDay | Plus:1 %}

                    {% capture dayText %}{{ thisMonth | Date:'yyyyMM' }}{% if currentDay < 10 %}0{% endif %}{{ currentDay }}{% endcapture %}
                    <td class="calendar-day {% if dayText == today %}today{% endif %}">
                    <div>
                    <div class="calendar-dom">{{ currentDay }}</div>
                {% endfor %}

                </div>
            </td>

Now that we are done with the data we have for days of the month, we need to fill in any days at the end of the month that we did not having any data for. Does this code look familiar? It should. This is pretty much an exact copy of that inner for loop you saw above. We are simply doing the same thing but looping until we hit the end of the month.


            {% comment %} ## Add in a colspan cell to fill out the rest of the week at the end of the month. ## {% endcomment %}
            {% if dayOfWeek < 7 %}
                {% assign count = 7 | Minus:dayOfWeek %}
                <td class="calendar-day" colspan="{{ count }}"></td>
            {% endif %}
        </tr>
    </table>
</div>

Home stretch. Time to pull the dessert out of the fridge and get ready to eat. We do another table cell that spans multiple cells to deal with filling in the rest of the week for a month that doesn't end on Saturday. That's all there is to it.


{% comment %} ## Assign initial values to friendly variable names ## {% endcomment %}
{% assign padInitialDays = table2.rows[0].Day1OfWeek | Minus:1 %}
{% assign lastDay = table2.rows[0].LastDay | Minus:1 %}
{% assign previousMonth = table2.rows[0].PreviousMonth %}
{% assign thisMonth = table2.rows[0].ThisMonth %}
{% assign nextMonth = table2.rows[0].NextMonth %}
{% assign dayOfWeek = 1 %}
{% assign currentDay = 1 %}
{% assign today = 'Now' | Date:'yyyyMMdd' %}

<style>
tr.calendar-header {
    background-color: #edeae6;
}
tr.calendar-header > th {
    text-align: center;
    padding: 8px 0px;
    border-bottom: 1px solid #d8d1c8;
}
tr.calendar-header > th.calendar-previous {
    text-align: left;
    padding-left: 20px;
}
tr.calendar-header > th.calendar-current {
    font-size: 1.25em;
}
tr.calendar-header > th.calendar-next {
    text-align: right;
    padding-right: 20px;
}
tr.calendar-header > th.calendar-previous > a,
tr.calendar-header > th.calendar-next > a {
    font-weight: initial;
}

.calendar-day {
    border: 1px solid #d8d1c8;
    vertical-align: top;
    width: 14.2857%;
}
.calendar-day.today {
    background-color: #ee7624;
    color: white;
}
.calendar-day > div {
    min-height: 90px;
    padding: 6px;
}
.calendar-dom {
    font-size: 120%;
    font-weight: bold;
    margin-bottom: 4px;
}

.calendar-item {
}
.calendar-details {
    font-size: 80%;
    display: block;
    padding-left: 8px;
}
</style>

<div class="panel panel-block">
    <div class="panel-heading">
        <h3 class="panel-title"><i class="fa fa-calendar-o"></i> POD Schedule</h3>
    </div>

    <table style="width: 100%;">
        {% comment %} ## Put in the links to the previous and next month along with the name of this month. ## {% endcomment %}
        <tr class="calendar-header">
            <th colspan="2" class="calendar-previous">
                <a href="?Date={{ previousMonth | Date:'yyyy-MM' }}-01"><i class="fa fa-chevron-left"></i> Previous</a>
            </th>
            <th colspan="3" class="calendar-current">
                {{ thisMonth | Date:'MMMM yyyy' }}
            </th>
            <th colspan="2" class="calendar-next">
                <a href="?Date={{ nextMonth | Date:'yyyy-MM' }}-01">Next <i class="fa fa-chevron-right"></i></a>
            </th>
        </tr>

        {% comment %} ## Put in weekday names. ## {% endcomment %}
        <tr class="calendar-header">
            <th>Sunday</th>
            <th>Monday</th>
            <th>Tuesday</th>
            <th>Wednesday</th>
            <th>Thursday</th>
            <th>Friday</th>
            <th>Saturday</th>
        </tr>

        <tr>
            {% comment %} ## Add a single colspan cell to cover the days not in this month yet. ## {% endcomment %}
            {% if padInitialDays > 0 %}
                <td class="calendar-day" colspan="{{ padInitialDays }}"></td>
                {% assign dayOfWeek = dayOfWeek | Plus:padInitialDays %}
            {% endif %}

            {% comment %} ## Start with day 1. ## {% endcomment %}
            {% capture dayText %}{{ thisMonth | Date:'yyyyMM' }}01{% endcapture %}
            <td class="calendar-day {% if dayText == today %}today{% endif %}"><div>
                <div class="calendar-dom">{{ currentDay }}</div>

                {% for row in table1.rows %}
                    {% assign day = row.Date | Date:'d' %}

                    {% comment %} ## Calculate and then add any "empty" days that were skipped in the result set. ## {% endcomment %}
                    {% assign padDays = day | Minus:currentDay %}
                    {% for d in (1..padDays) %}
                        </div>
                        </td>
                        {% if dayOfWeek == 7 %}
                            </tr><tr>
                            {% assign dayOfWeek = 0 %}
                        {% endif %}

                        {% assign dayOfWeek = dayOfWeek | Plus:1 %}
                        {% assign currentDay = currentDay | Plus:1 %}

                        {% capture dayText %}{{ thisMonth | Date:'yyyyMM' }}{% if currentDay < 10 %}0{% endif %}{{ currentDay }}{% endcapture %}
                        <td class="calendar-day {% if dayText == today %}today{% endif %}">
                        <div>
                        <div class="calendar-dom">{{ currentDay }}</div>
                    {% endfor %}

                    <div class="calendar-item">
                        {{ row.Title }}
                        {% if row.Details != empty %}<span class="calendar-details">{{ row.Details }}</div>{% endif %}
                    </div>
                {% endfor %}

                {% comment %} ## Add any empty days to make up the rest of the month. ## {% endcomment %}
                {% for d in (currentDay..lastDay) %}
                    </div>
                    </td>
                    {% if dayOfWeek == 7 %}
                        </tr><tr>
                        {% assign dayOfWeek = 0 %}
                    {% endif %}

                    {% assign dayOfWeek = dayOfWeek | Plus:1 %}
                    {% assign currentDay = currentDay | Plus:1 %}

                    {% capture dayText %}{{ thisMonth | Date:'yyyyMM' }}{% if currentDay < 10 %}0{% endif %}{{ currentDay }}{% endcapture %}
                    <td class="calendar-day {% if dayText == today %}today{% endif %}">
                    <div>
                    <div class="calendar-dom">{{ currentDay }}</div>
                {% endfor %}

                </div>
            </td>

            {% comment %} ## Add in a colspan cell to fill out the rest of the week at the end of the month. ## {% endcomment %}
            {% if dayOfWeek < 7 %}
                {% assign count = 7 | Minus:dayOfWeek %}
                <td class="calendar-day" colspan="{{ count }}"></td>
            {% endif %}
        </tr>
    </table>
</div>

Preview

We all like pretty images right? Okay well, too bad. You get an image anyway. This is how our styled calendar comes out looking with the CSS that is mentioned above. Doesn't take much tweaking to the CSS to make things look how you want, unless it already looks exactly how you want. In which case, you're welcome.


@danielhazelbaker
Shepherd Church

Prolific coding monkey and avid Overwatch player. If there is a "right way" to do something you can be sure Daniel's approach will be a "near miss".