CiviReport - Add days to Transaction Date for Estimated Deposit Date

Question

Our Treasurer has asked for a report that will help him reconcile the monthly bank statement more efficiently. As with most financial services vendors, our vendor (iATS) has a delay between the transaction date (or receive date in Civi) and the day the money is actually deposited into our organization's bank account. This creates an extra step for our treasurer as he has to line up the transaction dates in Civi to the deposit dates on the bank statement.

iATS has the added wrinkle that credit/debit card transactions are deposited on a different schedule than ACH/EFT transactions. How can I create a column in the CiviContribute Bookkeeping Transactions report that will give an estimated deposit date for each donation based on what type of payment instrument was used and what day of the week the transaction took place?

For reference, iATS deposits transactions on this schedule:

• Credit/Debit cards - 1-2 business days, rolling; to be safe, the default on the report will be 2 business days unless the transaction happens on a Friday-Sunday; then it will be the following Monday.
• ACH/EFT - each Sunday through Saturday is deposited on the following Wednesday.

Civi 4.6.20, Drupal 7

Answer 1

This is not an elegant solution by any means. But it does work. So there's that.

To start, I copied the Bookkeeping transactions report from here (sites\all\modules\contrib\civicrm\CRM\Report\Form\Contribute) and renamed it Reconcile.php and placed the new file in my custom PHP folder so I wouldn't be adjusting the original report.

I renamed the class on line 35 to CRM_Report_Form_Contribute_Reconcile so the class name and file name match.

My report does not require both the Receive Date and Transaction Date columns, so I re-purposed the Transaction Date column. (Truthfully, I would rather have been able to add a column for this, instead of re-purposing an existing one... but alas, I didn't know how). In the function __construct(), under 'civicrm_financial_trxn' 'fields', I changed the 'title' of 'trxn_date' to 'Est Deposit Date'. Approx line 299:

'trxn_date' => array(
  'title' => ts('Est Deposit Date'),
  'default' => TRUE,
  'type' => CRM_Utils_Type::T_DATE,
),

I also changed the title in the 'filters' section so that the UI would match.

Then in 'function select()', I added another case to the switch ($fieldName) (which begins approx line 371). Our payment instrument 1 is credit/debit cards. Our payment instrument 2 is ACH/EFT.

//adjust Transaction Date column to yield adjusted dates for Estimated Deposit Date
case 'trxn_date':
  $select[] = " CASE 
     WHEN {$this->_aliases['civicrm_financial_trxn']}.payment_instrument_id = 1 AND DAYOFWEEK({$this->_aliases['civicrm_financial_trxn']}.trxn_date) = 1
     THEN DATE_ADD({$this->_aliases['civicrm_financial_trxn']}.trxn_date, INTERVAL 1 DAY)
     WHEN {$this->_aliases['civicrm_financial_trxn']}.payment_instrument_id = 1 AND DAYOFWEEK({$this->_aliases['civicrm_financial_trxn']}.trxn_date) = 6
     THEN DATE_ADD({$this->_aliases['civicrm_financial_trxn']}.trxn_date, INTERVAL 3 DAY)

     WHEN {$this->_aliases['civicrm_financial_trxn']}.payment_instrument_id = 2 AND DAYOFWEEK({$this->_aliases['civicrm_financial_trxn']}.trxn_date) = 1
     THEN DATE_ADD({$this->_aliases['civicrm_financial_trxn']}.trxn_date, INTERVAL 10 DAY)
     WHEN {$this->_aliases['civicrm_financial_trxn']}.payment_instrument_id = 2 AND DAYOFWEEK({$this->_aliases['civicrm_financial_trxn']}.trxn_date) = 2
     THEN DATE_ADD({$this->_aliases['civicrm_financial_trxn']}.trxn_date, INTERVAL 9 DAY)
     WHEN {$this->_aliases['civicrm_financial_trxn']}.payment_instrument_id = 2 AND DAYOFWEEK({$this->_aliases['civicrm_financial_trxn']}.trxn_date) = 3
     THEN DATE_ADD({$this->_aliases['civicrm_financial_trxn']}.trxn_date, INTERVAL 8 DAY)
     WHEN {$this->_aliases['civicrm_financial_trxn']}.payment_instrument_id = 2 AND DAYOFWEEK({$this->_aliases['civicrm_financial_trxn']}.trxn_date) = 4
     THEN DATE_ADD({$this->_aliases['civicrm_financial_trxn']}.trxn_date, INTERVAL 7 DAY)
     WHEN {$this->_aliases['civicrm_financial_trxn']}.payment_instrument_id = 2 AND DAYOFWEEK({$this->_aliases['civicrm_financial_trxn']}.trxn_date) = 5
     THEN DATE_ADD({$this->_aliases['civicrm_financial_trxn']}.trxn_date, INTERVAL 6 DAY)
     WHEN {$this->_aliases['civicrm_financial_trxn']}.payment_instrument_id = 2 AND DAYOFWEEK({$this->_aliases['civicrm_financial_trxn']}.trxn_date) = 6
     THEN DATE_ADD({$this->_aliases['civicrm_financial_trxn']}.trxn_date, INTERVAL 5 DAY)
     WHEN {$this->_aliases['civicrm_financial_trxn']}.payment_instrument_id = 2 AND DAYOFWEEK({$this->_aliases['civicrm_financial_trxn']}.trxn_date) = 7
     THEN DATE_ADD({$this->_aliases['civicrm_financial_trxn']}.trxn_date, INTERVAL 4 DAY)

     ELSE DATE_ADD({$this->_aliases['civicrm_financial_trxn']}.trxn_date, INTERVAL 2 DAY)
     END AS civicrm_financial_trxn_trxn_date ";
break;

I'd be curious to hear and learn how others might have tackled this. I know I could have done this as an extension, but that would have been more work for me at this point. Maybe if I learned the extension system better it wouldn't be more work in the future. :-) I also initially tried finding a solution in Drupal Views, but I couldn't sort out how to add days to a date within the Views ecosystem.