Snippet: Weekly Report of First-Time Contributors

  • By Michael Garrison 3 Years Ago

The Accounting team at our church asked me to generate a report to output families that has had any member contribute to the giving fund (AccountId=1) for the first time between two arbitrary dates -- they want to be able to send a "thank you" note with some suggestions for the next steps to take to simplify/automate giving.

To make the request a little more complex, I needed to exclude families where any member is also a member of a custom general group (GroupId=6240) containing all the people who ever contributed to the general fund prior to our adoption of Rock.

OK. Let's have the report show up under the Financial menu in the sidebar, so start off by going to the Rock Internal Homepage and clicking on the Child Pages button on the Admin bar. Click on Finance, then pull up the Child Pages dialogue again. Let's have our custom reports appear under a separate header, so click the + button and call the page Custom Reports, with the Full Width layout. Click OK, then click the name to go to the new page. Once more, pull up the Child Pages dialogue, and click + to add another Full-Width page called First-time givers and go to that page. At this point you should be able to see your new page under your custom heading when you click the Financial button in Rock's left gutter menu.

Now click the Page Zones button on the Admin bar, and click the Zone Blocks button on the Main Zone.

First things first; we're going to need an HTML element so that our users can specify a date range to limit the first-time contributions to. So click the + button to add a block of type HTML Content. Give it the title of Date Range to keep things clearer for you later.

Let's walk through everything we're going to do in this HTML block, one step at a time.

First, we want a nice date control, which Rock doesn't provide out of the box. So we start with some stylizing to make each of our two dates look like a single input: white background, black border, and most importantly, transparent backgrounds on any inputs therein.

<style>  
span.DateControl {  
    border:1px solid black;
    background-color:#fff;
}
span.DateControl input {  
    border:0px;
    background:transparent;
    text-align:center;
}
</style>  

Then we create the six inputs (Month, Day and Year for the beginning and ending dates) with some onKeyUp triggers to auto-advance the text input from one field to the next when an appropriate number has been filled in. I experimented a little bit to find the correct time delay for auto-advancing the cursor, (I arrived at 125ms) but feel free to adjust this if necessary. You'll also notice that I'm telling the browser that when the user clicks on a number to place their cursor, it should highlight the whole number so they can type right over it.

<div style="display:inline-block;padding-right:4em;">Beginning date: (MM/DD/YYYY)<br />  
    <span class="DateControl">
        <input type="number" id="iMMstart" maxlength="2" style="width:2em;" onFocus="this.select()" onKeyUp="if (this.value.length==2) {setTimeout(function(){ document.getElementById('iDDstart').focus();},125)}" ></input>/<input type="number" id="iDDstart" maxlength="2" style="width:2em;" onFocus="this.select()" onKeyUp="if (this.value.length==2) {setTimeout(function(){ document.getElementById('iYYYYstart').focus();},125)}" ></input>/<input type="number" id="iYYYYstart" maxlength="4" style="width:3em;" onFocus="this.select()" onKeyUp="if (this.value.length==4) {setTimeout(function(){ document.getElementById('iMMend').focus();},125)}"></input>
    </span>
</div>  
<div style="display:inline-block;padding-right:4em;">Ending date: (MM/DD/YYYY)<br />  
    <span class="DateControl">
        <input type="number" id="iMMend" maxlength="2" style="width:2em;" onFocus="this.select()" onKeyUp="if (this.value.length==2) {setTimeout(function(){ document.getElementById('iDDend').focus();},125)}" ></input>/<input type="number" id="iDDend" maxlength="2" style="width:2em;" onFocus="this.select()" onKeyUp="if (this.value.length==2) {setTimeout(function(){ document.getElementById('iYYYYend').focus();},125)}" ></input>/<input type="number" id="iYYYYend" maxlength="4" style="width:3em;" onFocus="this.select()"></input>
    </span>
</div>  

Now we'll create a Submit button that simply appends the dates provided in our inputs to the end of the current URL as ?start=YYYYMMDD&end=YYYYMMDD

<div style="display:inline-block;"><input type="submit" class="btn btn-primary" value="View" onClick="window.location.href=window.location.pathname + '?start=' +  
                                                                                        document.getElementById('iYYYYstart').value+
                                                                                        document.getElementById('iMMstart').value+
                                                                                        document.getElementById('iDDstart').value+
                                                                                                                '&end=' +
                                                                                        document.getElementById('iYYYYend').value+
                                                                                        document.getElementById('iMMend').value+
                                                                                        document.getElementById('iDDend').value;"></input>
</div>  

OK, Date control created.

Now we're going to jump into some JavaScript

<script>  

...to parse the URL so we will be able to check and see if dates have been provided.

var params = {};  
if (location.search) {  
    var parts = location.search.substring(1).split('&');

    for (var i = 0; i < parts.length; i++) {
        var nv = parts[i].split('=');
        if (!nv[0]) continue;
        params[nv[0]] = nv[1] || true;
    }
}

If start and end have been specified in the URL, let's fill in the appropriate numbers into our date input controls so that they match what's been requested.

if (params.start && params.end) {  
    document.getElementById('iDDstart').value=params.start.substring(6,8);
    document.getElementById('iMMstart').value=params.start.substring(4,6);
    document.getElementById('iYYYYstart').value=params.start.substring(0,4);
    document.getElementById('iDDend').value=params.end.substring(6,8);
    document.getElementById('iMMend').value=params.end.substring(4,6);
    document.getElementById('iYYYYend').value=params.end.substring(0,4);
    document.getElementById('iMMstart').focus();
}

If start and end have NOT been provided however, let's calculate appropriate default dates- here, I'm using the dates of the most recent complete Monday-Sunday week.

else {  
    var thissun=new Date();
    var lastmon=new Date();
    thissun.setDate(thissun.getDate()-parseInt(thissun.getDay()));
    lastmon.setDate(lastmon.getDate()-6-parseInt(lastmon.getDay()));

Once that's calculated (we're still in the "dates have not yet been specified" clause), let's auto-refresh the page with those days as the parameters in the URL - that way the page is always loaded with good starting dates and current data and the users won't have to 'start from scratch'.

    window.location.href=window.location.pathname + '?start=' +
                                        lastmon.getFullYear().toString() +
                                        ((lastmon.getMonth()+1) < 10 ? '0' + (lastmon.getMonth()+1) : (lastmon.getMonth()+1)).toString() +
                                        ((lastmon.getDate()) < 10 ? '0' + (lastmon.getDate()) : (lastmon.getDate())).toString() +
                                                    '&end=' +
                                        (thissun.getFullYear()).toString() +
                                        ((thissun.getMonth()+1) < 10 ? '0' + (thissun.getMonth()+1) : (thissun.getMonth()+1)).toString() +
                                        ((thissun.getDate()) < 10 ? '0' + (thissun.getDate()) : (thissun.getDate())).toString();
} // <-end of the else clause

And end our JavaScript section

</script>  

Let's put all of the HTML Content block together:

<style>  
span.DateControl {  
    border:1px solid black;
    background-color:#fff;
}
span.DateControl input {  
    border:0px;
    background:transparent;
    text-align:center;
}
</style>  
<div style="display:inline-block;padding-right:4em;">Beginning date: (MM/DD/YYYY)<br />  
    <span class="DateControl">
        <input type="number" id="iMMstart" maxlength="2" style="width:2em;" onFocus="this.select()" onKeyUp="if (this.value.length==2) {setTimeout(function(){ document.getElementById('iDDstart').focus();},125)}" ></input>/<input type="number" id="iDDstart" maxlength="2" style="width:2em;" onFocus="this.select()" onKeyUp="if (this.value.length==2) {setTimeout(function(){ document.getElementById('iYYYYstart').focus();},125)}" ></input>/<input type="number" id="iYYYYstart" maxlength="4" style="width:3em;" onFocus="this.select()" onKeyUp="if (this.value.length==4) {setTimeout(function(){ document.getElementById('iMMend').focus();},125)}"></input>
    </span>
</div>  
<div style="display:inline-block;padding-right:4em;">Ending date: (MM/DD/YYYY)<br />  
    <span class="DateControl">
        <input type="number" id="iMMend" maxlength="2" style="width:2em;" onFocus="this.select()" onKeyUp="if (this.value.length==2) {setTimeout(function(){ document.getElementById('iDDend').focus();},125)}" ></input>/<input type="number" id="iDDend" maxlength="2" style="width:2em;" onFocus="this.select()" onKeyUp="if (this.value.length==2) {setTimeout(function(){ document.getElementById('iYYYYend').focus();},125)}" ></input>/<input type="number" id="iYYYYend" maxlength="4" style="width:3em;" onFocus="this.select()"></input>
    </span>
</div>  
<div style="display:inline-block;"><input type="submit" class="btn btn-primary" value="View" onClick="window.location.href=window.location.pathname + '?start=' +  
                                                                                        document.getElementById('iYYYYstart').value+
                                                                                        document.getElementById('iMMstart').value+
                                                                                        document.getElementById('iDDstart').value+
                                                                                                                '&end=' +
                                                                                        document.getElementById('iYYYYend').value+
                                                                                        document.getElementById('iMMend').value+
                                                                                        document.getElementById('iDDend').value;"></input>
</div>  
<script>  
var params = {};  
if (location.search) {  
    var parts = location.search.substring(1).split('&');

    for (var i = 0; i < parts.length; i++) {
        var nv = parts[i].split('=');
        if (!nv[0]) continue;
        params[nv[0]] = nv[1] || true;
    }
}
if (params.start && params.end) {  
    document.getElementById('iDDstart').value=params.start.substring(6,8);
    document.getElementById('iMMstart').value=params.start.substring(4,6);
    document.getElementById('iYYYYstart').value=params.start.substring(0,4);
    document.getElementById('iDDend').value=params.end.substring(6,8);
    document.getElementById('iMMend').value=params.end.substring(4,6);
    document.getElementById('iYYYYend').value=params.end.substring(0,4);
    document.getElementById('iMMstart').focus();
}
else {  
    var thissun=new Date();
    var lastmon=new Date();
    thissun.setDate(thissun.getDate()-parseInt(thissun.getDay()));
    lastmon.setDate(lastmon.getDate()-6-parseInt(lastmon.getDay()));
    window.location.href=window.location.pathname + '?start=' +
                                        lastmon.getFullYear().toString() +
                                        ((lastmon.getMonth()+1) < 10 ? '0' + (lastmon.getMonth()+1) : (lastmon.getMonth()+1)).toString() +
                                        ((lastmon.getDate()) < 10 ? '0' + (lastmon.getDate()) : (lastmon.getDate())).toString() +
                                                    '&end=' +
                                        (thissun.getFullYear()).toString() +
                                        ((thissun.getMonth()+1) < 10 ? '0' + (thissun.getMonth()+1) : (thissun.getMonth()+1)).toString() +
                                        ((thissun.getDate()) < 10 ? '0' + (thissun.getDate()) : (thissun.getDate())).toString();
}
</script>  

Whew, one more block to go

Now we need the block that will actually give us the output. Click the Page Zones button on the Admin bar, and click the Zone Blocks button on the Main Zone to add a new Dynamic Data block below the Date Range block.

Call it First-time givers, and use this query:

SELECT d.[GivingGroupId], f.[Name] AS FamilyName, d.[TransactionDateTime] AS FirstTransactionDate FROM  
    (
    Select FamilyFirst.[GivingGroupId], FamilyFirst.[TransactionDateTime] FROM
        (
        SELECT PersonFirst.[GivingGroupId], MIN(PersonFirst.[TransactionDateTime]) AS TransactionDateTime FROM
            (
            SELECT P.[Id], P.[GivingGroupId], MIN(g.[TransactionDateTime]) AS TransactionDateTime
                From [Person] P
                LEFT JOIN [FinancialTransaction] g
                    ON P.[Id]=g.[AuthorizedPersonAliasId]
                LEFT JOIN [FinancialTransactionDetail] a
                    ON g.[Id]=a.[TransactionId]
                WHERE g.[TransactionDateTime] IS NOT NULL
                    AND a.[AccountId]=1
                GROUP BY P.[Id], P.[GivingGroupId]
            ) personFirst
            GROUP BY PersonFirst.[GivingGroupId]
        ) FamilyFirst
        WHERE FamilyFirst.[TransactionDateTime] > @start
            AND FamilyFirst.[TransactionDateTime] <= @end
    ) d
    LEFT JOIN [Group] f ON d.[GivingGroupId]=f.[Id]
    WHERE d.[GivingGroupId] NOT IN
        (
            SELECT DISTINCT p.[GivingGroupId] FROM [Person] p
                LEFT JOIN [GroupMember] g
                    ON g.[PersonId] = p.[Id]
                WHERE g.[GroupId]=6240
                    AND p.[GivingGroupId] IS NOT NULL
        )

This query basically starts by getting each person's date of first giving to AccountId=1, then groups all the people by ContributionGroupId to get each family's first date of giving. Then it filters the list to only families whose first contribution date is within the specified range (@start and @end) and then further excludes any results which also show up in a query to return all the ContributionGroupIds for each person who is a member of our general group (GroupId=6240).

Obviously if you're trying create this report for yourself, your 'prior giving' group is going to have a different ID- replace it as appropriate. Or if you don't have such a group, you can exclude the entire WHERE clause at the end.

Now as I mentioned, we only wanted to see first-time contributors to the General Fund (default is AccountId=1). That way an otherwise disconnected-from-church parent paying for summer camp isn't included in the report. If your desires are different, you can add to that clause or remove it entirely.

Now, in the Dynamic Data block properties, let's Hide GivingGroupId, set Parameters to start=0;end=0 and set your Selection Url to ~/page/113?GroupId={GivingGroupId} - that way when you click on a result, you'll be taken to the Group Viewer page to view that family. If you have another group viewer page - a roster page, for instance, you can point to that page instead.

Done!

Finished Report



@mikejed
Spark Development Network
Flagstaff, AZ

Michael Garrison recently left his job in Architecture to become one of the "new guys" at Spark (the "Core Team"), but he's still helping out Christ's Church of Flagstaff and other non-profits with tech needs in his off-hours, trying to make computers do what computers do best, so that people are freed to do what we do best: relate with people!