Contact Trend Widget¶

<div class="box">
    <div class="box-title hidden-xs">
        <h5><a href="#">{{WidgetName}}</a></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 visible-xs-block">
            <h5>
                <i class="fa fa-chevron-circle-right"></i>&nbsp;&nbsp;{{WidgetName}}
            </h5>
        </div>
    </a>
    <div class="collapse in" id="{{WidgetId}}-section">
        <div class="box-content center">
            <div class="chart">
            </div>
        </div>
    </div>
</div>

 <script type="text/javascript">
      var {{WidgetId}} = function()  {
        var data = new  google.visualization.DataTable();
        data.addColumn("string", "Month");
        data.addColumn("number", "Card");
        data.addColumn("number", "Text");
        data.addColumn("number", "Phone");
        data.addColumn("number", "Email");
        data.addColumn("number", "Social");
        data.addColumn("number", "Visit");
        data.addColumn("number", "Letter");
        data.addColumn("number", "Other");

        data.addRows({{{rowdata}}});

        var options = {
            title: "Monthly Contact Trend",
            titlePosition: "none",
            legend: { position: "top", maxLines: 3 },
            bar: { groupWidth: "60%" },
            isStacked: true,
            chartArea: {width: "90%"}

        };

        var chart = new google.visualization.ColumnChart(document.querySelector("#{{WidgetId}}-section .chart"));

        chart.draw(data, options);
      }

    google.charts.load("current", {packages:["corechart"]});
    google.charts.setOnLoadCallback({{WidgetId}});
    WidgetCharts.{{WidgetId}} = {{WidgetId}};
</script>

declare @dt2 DATETIME = getdate()
declare @dt1 DATETIME  = DATEADD(MONTH, -12, @dt2)  --Months to look back.  Change the 6 if a different number of months is desired (e.g. -3 for 3 month span)

--These are options to filter for a specific Ministry, ContactType or Contact Reason.
--ID#s come from tables in Admin>Lookup Codes, set to 0 if you do not want to filter
declare @min INT = 0   --Ministry ID
declare @type INT = 0  --Contact Type ID
declare @reas INT = 0  --Contact Reason ID


--Prepopulate a list of months in case there is a month with zero contacts
;with months as (
select DATEFROMPARTS (Year(@dt2), Month(@dt2), 1) m UNION ALL
select DATEADD(month, -1, DATEFROMPARTS (Year(@dt2), Month(@dt2), 1)) m UNION ALL
select DATEADD(month, -2, DATEFROMPARTS (Year(@dt2), Month(@dt2), 1)) m UNION ALL
select DATEADD(month, -3, DATEFROMPARTS (Year(@dt2), Month(@dt2), 1)) m UNION ALL
select DATEADD(month, -4, DATEFROMPARTS (Year(@dt2), Month(@dt2), 1)) m UNION ALL
select DATEADD(month, -5, DATEFROMPARTS (Year(@dt2), Month(@dt2), 1)) m UNION ALL
select DATEADD(month, -6, DATEFROMPARTS (Year(@dt2), Month(@dt2), 1)) m UNION ALL
select DATEADD(month, -7, DATEFROMPARTS (Year(@dt2), Month(@dt2), 1)) m UNION ALL
select DATEADD(month, -8, DATEFROMPARTS (Year(@dt2), Month(@dt2), 1)) m UNION ALL
select DATEADD(month, -9, DATEFROMPARTS (Year(@dt2), Month(@dt2), 1)) m UNION ALL
select DATEADD(month, -10, DATEFROMPARTS (Year(@dt2), Month(@dt2), 1)) m UNION ALL
select DATEADD(month, -11, DATEFROMPARTS (Year(@dt2), Month(@dt2), 1)) m UNION ALL
select DATEADD(month, -12, DATEFROMPARTS (Year(@dt2), Month(@dt2), 1)) m
),

contacts as
(SELECT
	t.Description ContactType
	, CASE WHEN c.ContactDate IS NOT NULL THEN DATEFROMPARTS (Year(ContactDate), Month(ContactDate), 1) ELSE 'No Date' END ContactMonthYear
FROM dbo.Contact c
left JOIN lookup.ContactType t ON c.ContactTypeId = t.Id
left JOIN lookup.ContactReason r ON c.ContactReasonId = r.Id
LEFT JOIN dbo.Ministries m ON c.MinistryId = m.MinistryId
WHERE (@dt1 IS NULL OR c.ContactDate >= @dt1)
AND (@dt2 IS NULL OR c.ContactDate <= @dt2)

--These are options to filter for a specific Ministry, ContactType or Contact Reason.
AND (@min = 0 OR @min = c.MinistryId)
AND (@type = 0 OR @type = c.ContactTypeId)
AND (@reas = 0 OR @reas = c.ContactReasonId)
)

SELECT
    FORMAT(months.m, 'MMM yy')
    --	,FORMAT(contacts.ContactMonthYear, 'MMM yy')

    --The Text (e.g. "Card Sent") for ContactType matches should be identical to those in description column under Admin>Lookup Codes>ContactType
    --Note that you do not have to have all of the types listed... the rest will be counted in "other"
    , SUM(CASE WHEN contacts.ContactType='Card Sent' THEN 1 ELSE 0 END) Type_Card
    , SUM(CASE WHEN contacts.ContactType='Text Message' THEN 1 ELSE 0 END) Type_Text
    , SUM(CASE WHEN contacts.ContactType='Phone Call' THEN 1 ELSE 0 END) Type_Phone
    , SUM(CASE WHEN contacts.ContactType='Email Sent' THEN 1 ELSE 0 END) Type_Email
    , SUM(CASE WHEN contacts.ContactType='Social Media' THEN 1 ELSE 0 END) Type_Social
    , SUM(CASE WHEN contacts.ContactType='Personal Visit' THEN 1 ELSE 0 END) Type_Visit
    , SUM(CASE WHEN contacts.ContactType='Letter Sent' THEN 1 ELSE 0 END) Type_Letter

    --Now find any contacts that do not match the rows above.  Any types listed above should be included in the Comma separated list below.
    , SUM(CASE WHEN contacts.ContactType NOT IN ('Card Sent','Phone Call','Email Sent','Text Message','Social Media','Personal Visit', 'Letter Sent')  THEN 1 ELSE 0 END) Type_Other
FROM
months
full join
contacts on months.m = contacts.ContactMonthYear
GROUP BY months.m
ORDER BY months.m

import datetime

def Get():
    sql = Data.SQLContent
    template = Data.HTMLContent
    results = q.QuerySqlJsonArray(sql)

    Data.rowdata = model.FormatJson(results)

    print model.RenderTemplate(template)

Get()