Check-In Activity (Chart)¶

This script will create a line chart showing the activity (number of check-ins) per 20-minute segments on Sunday morning. The purpose is to let you see when your check-in stations are the busiest so you know when you need the most volunteers.

https://i.tpsdb.com/2017-03-28_11-30-36.png

Sample Check-In Chart

As you see in the sample chart, the busiest time at this church is between 9:10 and 9:30 AM.

Create Check-In Activity Script¶

Step 1: Go to Administration > Setup > Special Content and select the Python Scripts tab.
Step 2: Click the green + New Python Script File button and enter the name of the file as CheckinActivity and press Submit.
Step 3: Copy all of the code below and then paste it into the new script file and press the blue Save Python Script button.

model.Header = model.SundayForDate(model.DateTime).ToString("D")

sql = '''
    DECLARE @p1 DATETIME = dbo.SundayForDate(GETDATE())
	DECLARE @tzoffset INT = ISNULL((SELECT Setting FROM dbo.Setting WHERE Id = 'TZOffset'), 0)

    ;WITH activity AS (
        SELECT DATEADD(HOUR, @tzoffset, a.ActivityDate) ActivityDate
        FROM dbo.ActivityLog a
        WHERE (Activity LIKE 'checkin%, attend' OR Activity LIKE 'Check-In Record Attend%Attended:True')
        AND ActivityDate >= @p1
        AND ActivityDate < DATEADD(hh, 12, @p1)
	),
	intervals AS
    (
        SELECT DATEDIFF(MINUTE, 0, CONVERT(TIME, MIN(ActivityDate))) / 10 * 10 [minutes], COUNT(*) AS requests
        FROM activity
        GROUP BY DATEDIFF (ss ,'19700101' ,ActivityDate )/600
    )
    SELECT SUBSTRING(CONVERT(VARCHAR, DATEADD(MINUTE, nn.n10, 0), 108), 1, 5) Name,
        ISNULL(requests, 0) Cnt
    FROM intervals i
    RIGHT JOIN
    (
        SELECT Number * 10 n10
        FROM dbo.Numbers
        WHERE Number * 10 >= (SELECT MIN(minutes) FROM intervals)
        AND Number * 10 <= (SELECT MAX(minutes) FROM intervals)
    ) nn ON nn.n10 = i.[minutes]
    ORDER BY nn.n10
'''

data = q.SqlNameCountArray("Sunday", sql)

model.Script = '''
    <script type='text/javascript' src='https://www.google.com/jsapi'></script>
    <script type='text/javascript'>
      google.load('visualization', '1', {packages:['corechart']});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
        var data = google.visualization.arrayToDataTable(@data);

        var options = {
          title: 'Sunday Morning Checkin Activity',
          legend: 'none',
          pieSliceText: 'label',
          slices: {  4: {offset: 0.2},
                    12: {offset: 0.3},
                    14: {offset: 0.4},
                    15: {offset: 0.5}
          },
        };

        var chart = new google.visualization.LineChart(document.getElementById('chart'));
        chart.draw(data, options);
      }
    </script>
'''.replace("@data", data)

print "<div id='chart' style='width: 900px; height: 500px;'></div>"

Step 4: Click the Run Script button at the top of the form. Then, if you want the report available on the Reports Menu, click the link at the bottom of the report - Add Report to Menu.