Recent Attendance Trends Widget¶

<div class="box">
    <div class="box-title hidden-xs">
        <h5>{{WidgetName}}</h5>
    </div>
    <a class="visible-xs-block" id="giving-fc-collapse" data-toggle="collapse" href="#{{WidgetId}}-section" aria-expanded="true" aria-controls="{{WidgetId}}-section">
        <div class="box-title">
            <h5>
                <i class="fa fa-chevron-circle-right"></i>&nbsp;&nbsp;{{title}}
            </h5>
        </div>
    </a>
    <div class="collapse in" id="{{WidgetId}}-section">
        <div class="box-content center">
            <h4 class="text-center">{{title}}</h4>
            <div class="chart">
            </div>
        </div>
    </div>
</div>
<script type="text/javascript">
    var {{WidgetId}} = function() {
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Sunday');
        {{{addcolumns}}}
        data.addRows({{{rowdata}}});
        
        var options = {
            hAxis: {
            title: 'Dates'
            },
            colors: ['#CC0300', '#5CA12B', '#345CAD', '#F9B710', '#9E4EBC', '#74746D'],
            hAxis: {
                slantedText: true,
                slantedTextAngle: 30,
                textStyle: {
                    fontSize: 8
                },
                viewWindow: {
                    max: {{{xAxisInterval}}}
                },
            },
            legend: {
                position: "top",
                textStyle: {fontSize: 8},
                alignment: 'center'
            },
            lineWidth: 2
        };
        
        var chart = new google.visualization.LineChart(document.querySelector('#{{WidgetId}}-section .chart'));
        chart.draw(data, options);
    }
    // load and register the chart
    google.charts.load("current", {packages:["corechart"]});
    google.charts.setOnLoadCallback({{WidgetId}});
    WidgetCharts.{{WidgetId}} = {{WidgetId}};
</script>

select
    MaxCount,
    dd.DivId DivisionId,
    iif(datepart(dw, m.MeetingDate) = 1, CAST(m.MeetingDate AS DATE), DATEADD(DAY, (DATEDIFF(DAY, ((1 + 5) % 7), m.MeetingDate) / 7) * 7 + 7, ((1 + 5) % 7))) ss
    into #meetingdata
from dbo.Meetings m
join dbo.Organizations o on o.OrganizationId = m.OrganizationId
join dbo.DivOrg dd on dd.orgid = o.OrganizationId
join dbo.Division d on d.Id = dd.DivId
where dd.DivId in (@divs)
and m.MeetingDate >= '@begindate';
--and MaxCount > 0;
declare @aux int = 1;
declare @auxDate datetime = iif(datepart(dw, '@begindate') = 1, CAST('@begindate' AS DATE), DATEADD(DAY, (DATEDIFF(DAY, ((1 + 5) % 7), '@begindate') / 7) * 7 + 7, ((1 + 5) % 7)));
declare @resultRows int = (select COUNT(*) from #meetingdata);
declare @auxDiv int = (select Top(1)Id from Division where Id in (@divs));
while (@aux < @resultRows or (select COUNT(*) from #meetingdata) <= @graphPoints)
begin
	if not exists(select 1 from #meetingdata where ss = @auxDate)
	begin
		insert into #meetingdata
		values(0,@auxDiv,@auxDate)
	end
	select @auxDate = dateadd(day, 7,@auxDate);
	select @aux = @aux + 1;
end;
with data as (
	select MaxCount, DivisionId, ss
	from #meetingdata
	where ss >= '@begindate'
)
select CONVERT(varchar, ss, 111) [Sunday], @rowcounts
from data dd
group by dd.ss
drop table #meetingdata

# Users can add divisions to include in the line chart for this widget inside this divisions array.
# This array is made up of objects. Each object contains a name and a division id number.
# The first item in the object is the name you wish to display in the widget for the selected division.
# The second item in the object is the division id you wish to display in the widget.
divisions = [
    ['Young Couples', 210],
    ['Young Marrieds', 211],
    ['Adults 1', 213]
]
# You can change begindate to the date you wish to start the chart for your widget.
begindate = '09/01/2020'
# You can change Interval to change the horizontal axis of your chart.
graphPoints = '15'


def GetData(divisions, begindate):
    sql = model.Content('WidgetRecentAttendanceTrendsSQL')
    divids = [row[1] for row in divisions]
    sql = sql.replace('@begindate', begindate)
    sql = sql.replace('@graphPoints', graphPoints)
    sql = sql.replace('@divs', str(divids)[1:-1])
    rowcountsql = 'isnull((select sum(MaxCount) from data d where d.DivisionId = {0} and d.ss = dd.ss group by d.ss), 0) d{0}'
    rlist = [rowcountsql.format(d) for d in divids]
    s = ',\n'.join(rlist)
    sql = sql.replace('@rowcounts', s)
    return q.QuerySqlJsonArray(sql)


def Get():
    sql = Data.SQLContent
    template = Data.HTMLContent
    Data.results = GetData(divisions, begindate)
    Data.rowdata = Data.results
    Data.xAxisInterval = graphPoints
    addcolumn = "data.addColumn('number', '{}');"
    alist = [addcolumn.format(s[0]) for s in divisions]
    Data.addcolumns = '\n'.join(alist)
    # for x in Data.rowdata:
    # print(x)
    print model.RenderTemplate(template)


Get()