I hacked core and know I shouldn't (How do I use hook_civicrm_batchItems?)

Question

I need to adjust the CSV accounting batch export format for our accounting software. The CSV needs to be only four columns:

1. Envelope number (a custom field which corresponds to our accounting software)
2. Amount
3. Transaction date (formatted as yyyymmdd)
4. Fund # (Credit Account in Civi terms)

I know that there are two Civi hooks to alter accounting batches. There is an example for batchQuery, and I'm comfortable enough writing mySQL queries, so I'm okay there. But I'm newer at Civi development, so I'm not sure where to go with batchItems. The wiki does not have an example to work off of.

Not knowing where to go next, I found Sarah's blog post from Dec 2014 about changing the accounting batch export. Knowing it is not a long-term solution, more just trying to verify if the data could be retrieved, I setup a Drupal dev environment for 4.6.14 and changed some code in CRM/Finanical/BAO/ExportFormat/CSV.php. (I was going to use 4.7.3, but ran into a bug with the batch export function. It is slated to be fixed in 4.7.4).

I was able to achieve the results I needed. So, I know that the data can be retrieved. Here's the relevant code from CSV.php with my additions surrounded by ** (which are not really in my code); I commented out the lines in the $financialItems array for data I do not need.

Now the question is, how do I transition my temporary solution to a long term one using an extension with hook_civicrm_batchQuery and hook_civicrm_batchItems? I've got an extension framework set up with Civix, but don't know how to move from the adjusted code above to a solution that doesn't hack core.

Does anyone have an example of how to use hook_civicrm_batchItems?

~line 80

  public function generateExportQuery($batchId) {
    $sql = "SELECT
      ft.id as financial_trxn_id,
      ft.trxn_date,
      fa_to.accounting_code AS to_account_code,
      fa_to.name AS to_account_name,
      fa_to.account_type_code AS to_account_type_code,
      ft.total_amount AS debit_total_amount,
      ft.trxn_id AS trxn_id,
      cov.label AS payment_instrument,
      ft.check_number,
      c.source AS source,
      c.id AS contribution_id,
      c.contact_id AS contact_id,
      eb.batch_id AS batch_id,
      ft.currency AS currency,
      cov_status.label AS status,
      CASE
        WHEN efti.entity_id IS NOT NULL
        THEN efti.amount
        ELSE eftc.amount
      END AS amount,
      fa_from.account_type_code AS credit_account_type_code,
      fa_from.accounting_code AS credit_account,
      fa_from.name AS credit_account_name,
      fac.account_type_code AS from_credit_account_type_code,
      fac.accounting_code AS from_credit_account,
      fac.name AS from_credit_account_name,
      fi.description AS item_description,
      **pc.envelope_7 AS envelope_number**
      FROM civicrm_entity_batch eb
      LEFT JOIN civicrm_financial_trxn ft ON (eb.entity_id = ft.id AND eb.entity_table = 'civicrm_financial_trxn')
      LEFT JOIN civicrm_financial_account fa_to ON fa_to.id = ft.to_financial_account_id
      LEFT JOIN civicrm_financial_account fa_from ON fa_from.id = ft.from_financial_account_id
      LEFT JOIN civicrm_option_group cog ON cog.name = 'payment_instrument'
      LEFT JOIN civicrm_option_value cov ON (cov.value = ft.payment_instrument_id AND cov.option_group_id = cog.id)
      LEFT JOIN civicrm_entity_financial_trxn eftc ON (eftc.financial_trxn_id  = ft.id AND eftc.entity_table = 'civicrm_contribution')
      LEFT JOIN civicrm_contribution c ON c.id = eftc.entity_id
      LEFT JOIN civicrm_option_group cog_status ON cog_status.name = 'contribution_status'
      LEFT JOIN civicrm_option_value cov_status ON (cov_status.value = ft.status_id AND cov_status.option_group_id = cog_status.id)
      LEFT JOIN civicrm_entity_financial_trxn efti ON (efti.financial_trxn_id  = ft.id AND efti.entity_table = 'civicrm_financial_item')
      LEFT JOIN civicrm_financial_item fi ON fi.id = efti.entity_id
      LEFT JOIN civicrm_financial_account fac ON fac.id = fi.financial_account_id
      LEFT JOIN civicrm_financial_account fa ON fa.id = fi.financial_account_id
      **LEFT JOIN civicrm_value_power_church_info_4 pc ON pc.entity_id = c.contact_id**
      WHERE eb.batch_id = ( %1 )";

    CRM_Utils_Hook::batchQuery($sql);

    $params = array(1 => array($batchId, 'String'));
    $dao = CRM_Core_DAO::executeQuery($sql, $params);

    return $dao;
  }

~line 178

  public function makeCSV($export) {
    // getting data from admin page
    $prefixValue = CRM_Core_BAO_Setting::getItem(CRM_Core_BAO_Setting::CONTRIBUTE_PREFERENCES_NAME, 'contribution_invoice_settings');

    foreach ($export as $batchId => $dao) {
      $financialItems = array();
      $this->_batchIds = $batchId;

      $batchItems = array();
      $queryResults = array();

      while ($dao->fetch()) {
        $creditAccountName = $creditAccountType = $creditAccount = NULL;
        if ($dao->credit_account) {
          $creditAccountName = $dao->credit_account_name;
          $creditAccountType = $dao->credit_account_type_code;
          $creditAccount = $dao->credit_account;
        }
        else {
          $creditAccountName = $dao->from_credit_account_name;
          $creditAccountType = $dao->from_credit_account_type_code;
          $creditAccount = $dao->from_credit_account;
        }

        $invoiceNo = CRM_Utils_Array::value('invoice_prefix', $prefixValue) . "" . $dao->contribution_id;
        **$tdate = $dao->trxn_date;
        $mytdate = DateTime::createFromFormat('Y-m-d H:i:s', $tdate);
        $pcdate = $mytdate->format('Ymd');**

        $financialItems[] = array(
          //'Batch ID' => $dao->batch_id,
          //'Invoice No' => $invoiceNo,
          //'Contact ID' => $dao->contact_id,
          //'Financial Trxn ID/Internal ID' => $dao->financial_trxn_id,
          // Transaction date was here
          //'Debit Account' => $dao->to_account_code,
          //'Debit Account Name' => $dao->to_account_name,
          //'Debit Account Type' => $dao->to_account_type_code,
          //'Debit Account Amount (Unsplit)' => $dao->debit_total_amount,
          //'Transaction ID (Unsplit)' => $dao->trxn_id,
          //'Debit amount (Split)' => $dao->amount,
          //'Payment Instrument' => $dao->payment_instrument,
          //'Check Number' => $dao->check_number,
          //'Source' => $dao->source,
          //'Currency' => $dao->currency,
          //'Transaction Status' => $dao->status,
          // Amount was here
          // Credit Account was here
          //'Credit Account Name' => $creditAccountName,
          //'Credit Account Type' => $creditAccountType,
          //'Item Description' => $dao->item_description,
          'Envelope Number' => $dao->envelope_number,
          'Amount' => $dao->amount,
          **'Transaction Date' => $pcdate,
          'Credit Account' => $creditAccount,
        );

        end($financialItems);
        $batchItems[] = &$financialItems[key($financialItems)];
        $queryResults[] = get_object_vars($dao);
      }

      CRM_Utils_Hook::batchItems($queryResults, $batchItems);

      $financialItems['headers'] = self::formatHeaders($financialItems);
      self::export($financialItems);
    }
    parent::initiateDownload();
  }

Thanks!

Answer 1

You can create an extension that would export in your format. Here is an example of similar requirement.

https://github.com/JMAConsulting/biz.jmaconsulting.greatplains

You can use this extension to create a new export format through UI and copy paste CSV file and edit the columns code that you would not require in export.

Answer 2

I have an example for you. First I override the core query with my own one in the batchQuery hook:

function xchangewinbooks_civicrm_batchQuery(&$query) {
  CRM_Xchangewinbooks_Export::batchQuery($query);
}

The class method:

public static function batchQuery(&$query) {
    $query = "SELECT DISTINCT(d.invoice_id), d.creditnote_id, d.total_amount, d.net_amount, d.financial_type_id, d.receive_date, 
d.contact_id, g.name as description, i.accounting_code
FROM civicrm_entity_batch a
JOIN civicrm_financial_trxn b ON a.entity_id = b.id
JOIN civicrm_entity_financial_trxn c ON b.id = c.financial_trxn_id AND c.entity_table = 'civicrm_contribution'
LEFT JOIN civicrm_contribution d ON c.entity_id = d.id AND c.entity_table = 'civicrm_contribution'
LEFT JOIN civicrm_membership_payment e ON d.id = e.contribution_id
LEFT JOIN civicrm_membership f ON e.membership_id = f.id
LEFT JOIN civicrm_membership_type g ON f.membership_type_id = g.id
LEFT JOIN civicrm_entity_financial_account h ON h.entity_id = d.financial_type_id AND h.entity_table = 'civicrm_financial_type' 
  AND account_relationship = 1
LEFT JOIN civicrm_financial_account i ON h.financial_account_id = i.id
WHERE a.batch_id = %1 AND a.entity_table = 'civicrm_financial_trxn' AND d.invoice_id IS NOT NULL";
  }

And then for the items:

function xchangewinbooks_civicrm_batchItems(&$results, &$items) {
  CRM_Xchangewinbooks_Export::batchItems($results, $items);
}

with the class method:

 /**
   * Method to process the CiviCRM batchItems hook to generate new items
   *
   * @param $results
   * @param $items
   */
  public static function batchItems(&$results, &$items) {
    $items = array();
    foreach ($results as $result) {
      $result['due_date'] = self::calculateDueDate($result['receive_date']);
      // decide if this is a verkoopfactuur or a creditnota
      if (!empty($result['creditnote_id'])) {
        self::generateGrootboekLines('credit', $items, $result);
        self::generateAnalytischLine('credit', $items, $result);
      } else {
        self::generateGrootboekLines('factuur', $items, $result);
        self::generateAnalytischLine('factuur', $items, $result);
      }
    }
  }

In that class method I create a completely new area $items.