CiviCRM Community Forums (archive)

We have had a couple of requests now for 'turf' reports - reports to be used by phone canvassing staff (offline - the staff don't have computers on their desks) that contain quite a bit of information about the person to be called. The first time around I used tokens & the pdf letter functionality. However, that struggled to cope with 150 at a time & this time around we wanted 3500 at a time so I customised a CiviReport.

The code I wrote has quite a bit of hard-coding in it (custom fields, layout in the tpl) but I'm sharing it here in case it's useful to someone else.

https://svn.fuzion.co.nz/repos/fuzion-code/trunk/civicrm/unpackaged_extenstions/turf_report

Requirements
1) print 2 contacts to a page. (ie. landscape page in 2 halfs)
2) Each contact to hold information about
 - contact basic details
 - details of last 5 pledges (as a table) including amount paid
 - all phone numbers of contact (not just primary)
 - name, gender, nick name of related contact
 - last 5 pledges made by related contact (pledges don't offer soft_credits so we expose related contact pledges on pledge tab & here)
 3) Add a highlighed banner  to pages for contacts who paid their most recent pledge by 'Charge' (held in a custom field)
 4) order by various custom fields including by custom fields relating  to most recent pledge.
 5) Copes with 3500 + contacts being returned
 6) database of approx 70,000 contacts & 270,000 pledges
 

I had a few challenges and had to re-track & try different approaches a few times but the version I have now takes about a minute to render the 3200 pages and then I use the print driver's functionality to print two pages to one landscape page (end result 1600 pages). I found I had to hardcode the full site css url in the report instance to get the page breaks.

The main difficulty was getting information about the last 5 pledges in the report AND grabbing specific information (a custom field) about the very latest one. I tried just doing it with a LEFT join & sorting it out in php but that scaled to 1000 or so records before php ran out of memory.

So, I figured out I could compile the list of 5 pledges in an SQL temp table using group_concat (I'd already tried it out on the phone number field). Despite the number of pledges I found I got good responsiveness by creating temp table which held an entry for every contact with the last 5 pledges concatenated into a single cell in html format. A bonus of this was that I was also able to do other aggregate functions - I didn't need any sums but I did include max('id') which meant I could join against the latest pledge in the main Report Query via my temp table. This means the fields & filters selected through the report UI automatically apply to the contact's latest pledges.

        $this->_from .= " LEFT JOIN " .  $this->_create_temp_pledges_table() . " as ....
        $this->_from .= " LEFT JOIN civicrm_pledge as 
                                   {$this->_aliases['civicrm_pledge']} on ....;
The 3 temp tables I created held 70,000 , 70,000 and 270,000 records which intuitively seems like I should have filtered before creating them but the performance seemed really good - and perhaps it's not so different to what we do all the time (behind the scenes ) in all those Distinct / Group By queries we have.

    /*
     * Create temporary table of concatenated pledges against contact. Return Table name
     */
    function _create_temp_pledges_table(){
      static $tempTable;
      if(!empty($tempTable)){
        return $tempTable;
      }
      // TODO shouldn't be hard-coded
       $option_group_id = 11;
       $tempTable = 'tmp_table_pledges' . rand(10,99);
       $tempPayments = CRM_Core_DAO::executeQuery(
       "CREATE TEMPORARY  TABLE $tempTable
     SELECT  contact_id, max(pledge.id) as latest_pledge_id,
     SUBSTRING_INDEX(group_concat(
                          concat('<td>',amount , '</td><td>', create_date, '</td><td>',
                             amount_paid,'</td><td>',ct.name,'</td><td>',
                     ov.name, '</td>', 
                            installments, '</td><td>')
                            ORDER BY create_date DESC                   
                            SEPARATOR  '<tr></tr>' )
                    ,'</tr>',5)
       as pledges ,
       FROM (SELECT * FROM civicrm_pledge ORDER BY create_date DESC ) as pledge
       LEFT JOIN civicrm_contribution_type ct on ct.id = contribution_type_id
       LEFT JOIN civicrm_option_value ov ON (option_group_id = $option_group_id
                                      AND ov.value = contribution_type_id)
       LEFT JOIN  " .$this->_create_temp_payments_table() . " pp on pp.pp_id =  pledge.id 
       
   GROUP BY pledge.contact_id
   ORDER BY NULL
   
   ");(note ORDER BY NULL prevents it sorting by pledge.contact_id which is not required)
 

Hi Eileen,

Any chance you would still have the custom report linked to here

https://svn.fuzion.co.nz/repos/fuzion-code/trunk/civicrm/unpackaged_extenstions/turf_report

The link doesnt seem to work.

I am struggliing with building reports pulling custom data and it would definitely be of immsense help if I could have a look at your afore-referenced work.

Thanks

Thank you. Will report back on my progress.