CRM/Financial/BAO/ExportFormat/IIF.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.4                                                |
   6  +--------------------------------------------------------------------+
   7  | Copyright CiviCRM LLC (c) 2004-2013                                |
   8  +--------------------------------------------------------------------+
   9  | This file is a part of CiviCRM.                                    |
  10  |                                                                    |
  11  | CiviCRM is free software; you can copy, modify, and distribute it  |
  12  | under the terms of the GNU Affero General Public License           |
  13  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  14  |                                                                    |
  15  | CiviCRM is distributed in the hope that it will be useful, but     |
  16  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  17  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  18  | See the GNU Affero General Public License for more details.        |
  19  |                                                                    |
  20  | You should have received a copy of the GNU Affero General Public   |
  21  | License and the CiviCRM Licensing Exception along                  |
  22  | with this program; if not, contact CiviCRM LLC                     |
  23  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  24  | GNU Affero General Public License or the licensing of CiviCRM,     |
  25  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  26  +--------------------------------------------------------------------+
  27 */
  28
  29 /**
  30  *
  31  * @package CRM
  32  * @copyright CiviCRM LLC (c) 2004-2013
  33  * $Id$
  34  *
  35  */
  36
  37 /*
  38  * @see http://wiki.civicrm.org/confluence/display/CRM/CiviAccounts+Specifications+-++Batches#CiviAccountsSpecifications-Batches-%C2%A0Overviewofimplementation
  39  */
  40 class CRM_Financial_BAO_ExportFormat_IIF extends CRM_Financial_BAO_ExportFormat {
  41
  42   // Tab character. Some people's editors replace tabs with spaces so I'm scared to use actual tabs.
  43   // Can't set it here using chr() because static. Same thing if a const. So it's set in constructor.
  44   static $SEPARATOR;
  45
  46   // For this phase, we always output these records too so that there isn't data referenced in the journal entries that isn't defined anywhere.
  47   // Possibly in the future this could be selected by the user.
  48   public static $complementaryTables = array(
  49     'ACCNT',
  50     'CUST',
  51   );
  52
  53   /**
  54    * class constructor
  55    */
  56   function __construct() {
  57     parent::__construct();
  58     self::$SEPARATOR = chr(9);
  59   }
  60
  61   function export( $exportParams ) {
  62     parent::export( $exportParams );
  63
  64     foreach( self::$complementaryTables as $rct ) {
  65       $func = "export{$rct}";
  66       $this->$func();
  67     }
  68
  69     // now do general journal entries
  70     $this->exportTRANS();
  71
  72     $this->output();
  73   }
  74
  75   function putFile($out) {
  76     $config = CRM_Core_Config::singleton();
  77     $fileName = $config->uploadDir.'Financial_Transactions_'.$this->_batchIds.'_'.date('YmdHis').'.'.$this->getFileExtension();
  78     $this->_downloadFile[] = $config->customFileUploadDir.CRM_Utils_File::cleanFileName(basename($fileName));
  79     $buffer = fopen($fileName, 'w');
  80     fwrite($buffer, $out);
  81     fclose($buffer);
  82     return $fileName;
  83   }
  84
  85   function generateExportQuery($batchId) {
  86
  87     $sql = "SELECT
  88       ft.id as financial_trxn_id,
  89       ft.trxn_date,
  90       ft.total_amount AS debit_total_amount,
  91       ft.currency AS currency,
  92       ft.trxn_id AS trxn_id,
  93       cov.label AS payment_instrument,
  94       ft.check_number,
  95       fa_from.id AS from_account_id,
  96       fa_from.name AS from_account_name,
  97       fa_from.accounting_code AS from_account_code,
  98       fa_from.financial_account_type_id AS from_account_type_id,
  99       fa_from.description AS from_account_description,
 100       fa_from.account_type_code AS from_account_type_code,
 101       fa_to.id AS to_account_id,
 102       fa_to.name AS to_account_name,
 103       fa_to.accounting_code AS to_account_code,
 104       fa_to.financial_account_type_id AS to_account_type_id,
 105       fa_to.account_type_code AS to_account_type_code,
 106       fa_to.description AS to_account_description,
 107       fi.description AS item_description,
 108       contact_from.id AS contact_from_id,
 109       contact_from.display_name AS contact_from_name,
 110       contact_from.first_name AS contact_from_first_name,
 111       contact_from.last_name AS contact_from_last_name,
 112       contact_to.id AS contact_to_id,
 113       contact_to.display_name AS contact_to_name,
 114       contact_to.first_name AS contact_to_first_name,
 115       contact_to.last_name AS contact_to_last_name
 116       FROM civicrm_entity_batch eb
 117       LEFT JOIN civicrm_financial_trxn ft ON (eb.entity_id = ft.id AND eb.entity_table = 'civicrm_financial_trxn')
 118       LEFT JOIN civicrm_financial_account fa_from ON fa_from.id = ft.from_financial_account_id
 119       LEFT JOIN civicrm_financial_account fa_to ON fa_to.id = ft.to_financial_account_id
 120       LEFT JOIN civicrm_option_group cog ON cog.name = 'payment_instrument'
 121       LEFT JOIN civicrm_option_value cov ON (cov.value = ft.payment_instrument_id AND cov.option_group_id = cog.id)
 122       LEFT JOIN civicrm_contact contact_from ON contact_from.id = fa_from.contact_id
 123       LEFT JOIN civicrm_contact contact_to ON contact_to.id = fa_to.contact_id
 124       LEFT JOIN civicrm_entity_financial_trxn efti ON (efti.financial_trxn_id  = ft.id AND efti.entity_table = 'civicrm_financial_item')
 125       LEFT JOIN civicrm_financial_item fi ON fi.id = efti.entity_id
 126       WHERE eb.batch_id = ( %1 )";
 127
 128     $params = array(1 => array($batchId, 'String'));
 129     $dao = CRM_Core_DAO::executeQuery( $sql, $params );
 130
 131     return $dao;
 132   }
 133
 134   function makeIIF($export) {
 135     // Keep running list of accounts and contacts used in this batch, since we need to
 136     // include those in the output. Only want to include ones used in the batch, not everything in the db,
 137     // since would increase the chance of messing up user's existing Quickbooks entries.
 138     foreach ($export as $batchId => $dao) {
 139       $accounts = $contacts = $journalEntries = $exportParams = array();
 140       $this->_batchIds = $batchId;
 141       while ($dao->fetch()) {
 142         // add to running list of accounts
 143         if (!empty($dao->from_account_id) && !isset($accounts[$dao->from_account_id])) {
 144           $accounts[$dao->from_account_id] = array(
 145             'name' => $this->format($dao->from_account_name),
 146             'account_code' => $this->format($dao->from_account_code),
 147             'description' => $this->format($dao->from_account_description),
 148             'type' => $this->format($dao->from_account_type_code),
 149           );
 150         }
 151         if (!empty($dao->to_account_id) && !isset($accounts[$dao->to_account_id])) {
 152           $accounts[$dao->to_account_id] = array(
 153             'name' => $this->format($dao->to_account_name),
 154             'account_code' => $this->format($dao->to_account_code),
 155             'description' => $this->format($dao->to_account_description),
 156             'type' => $this->format($dao->to_account_type_code),
 157           );
 158         }
 159
 160         // add to running list of contacts
 161         if (!empty($dao->contact_from_id) && !isset($contacts[$dao->contact_from_id])) {
 162           $contacts[$dao->contact_from_id] = array(
 163             'name' => $this->format($dao->contact_from_name),
 164             'first_name' => $this->format($dao->contact_from_first_name),
 165             'last_name' => $this->format($dao->contact_from_last_name),
 166           );
 167         }
 168
 169         if (!empty($dao->contact_to_id) && !isset($contacts[$dao->contact_to_id])) {
 170           $contacts[$dao->contact_to_id] = array(
 171             'name' => $this->format($dao->contact_to_name),
 172             'first_name' => $this->format($dao->contact_to_first_name),
 173             'last_name' => $this->format($dao->contact_to_last_name),
 174           );
 175         }
 176
 177         // set up the journal entries for this financial trxn
 178         $journalEntries[$dao->financial_trxn_id] = array(
 179           'to_account' => array(
 180             'trxn_date' => $this->format($dao->trxn_date, 'date'),
 181             'trxn_id' =>  $this->format($dao->trxn_id),
 182             'account_name' => $this->format($dao->to_account_name),
 183             'amount' => $this->format($dao->debit_total_amount, 'money'),
 184             'contact_name' => $this->format($dao->contact_to_name),
 185             'payment_instrument' => $this->format($dao->payment_instrument),
 186             'check_number' => $this->format($dao->check_number),
 187           ),
 188           'splits' => array(),
 189         );
 190
 191         /*
 192          * splits has two possibilities depending on FROM account
 193          */
 194         if (empty($dao->from_account_id)) {
 195           // In this case, split records need to use the individual financial_item account for each item in the trxn
 196           $item_sql = "SELECT
 197             fa.id AS account_id,
 198             fa.name AS account_name,
 199             fa.accounting_code AS account_code,
 200             fa.description AS account_description,
 201             fi.description AS description,
 202             fi.id AS financial_item_id,
 203             fi.currency AS currency,
 204             cov.label AS payment_instrument,
 205             ft.check_number AS check_number,
 206             fi.transaction_date AS transaction_date,
 207             fi.amount AS amount,
 208             fa.account_type_code AS account_type_code,
 209             contact.id AS contact_id,
 210             contact.display_name AS contact_name,
 211             contact.first_name AS contact_first_name,
 212             contact.last_name AS contact_last_name
 213             FROM civicrm_entity_financial_trxn eft
 214             LEFT JOIN civicrm_financial_item fi ON eft.entity_id = fi.id
 215             LEFT JOIN civicrm_financial_trxn ft ON ft.id = eft.financial_trxn_id
 216             LEFT JOIN civicrm_option_group cog ON cog.name = 'payment_instrument'
 217             LEFT JOIN civicrm_option_value cov ON (cov.value = ft.payment_instrument_id AND cov.option_group_id = cog.id)
 218             LEFT JOIN civicrm_financial_account fa ON fa.id = fi.financial_account_id
 219             LEFT JOIN civicrm_contact contact ON contact.id = fi.contact_id
 220             WHERE eft.entity_table = 'civicrm_financial_item'
 221             AND eft.financial_trxn_id = %1";
 222
 223           $itemParams = array( 1 => array( $dao->financial_trxn_id, 'Integer' ) );
 224
 225           $itemDAO = CRM_Core_DAO::executeQuery( $item_sql, $itemParams );
 226           while ($itemDAO->fetch()) {
 227             // add to running list of accounts
 228             if (!empty($itemDAO->account_id) && !isset($accounts[$itemDAO->account_id])) {
 229               $accounts[$itemDAO->account_id] = array(
 230                 'name' => $this->format($itemDAO->account_name),
 231                 'account_code' => $this->format($itemDAO->account_code),
 232                 'description' => $this->format($itemDAO->account_description),
 233                 'type' => $this->format($itemDAO->account_type_code),
 234               );
 235             }
 236
 237             if (!empty($itemDAO->contact_id) && !isset($contacts[$itemDAO->contact_id])) {
 238               $contacts[$itemDAO->contact_id] = array(
 239                 'name' => $this->format($itemDAO->contact_name),
 240                 'first_name' => $this->format($itemDAO->contact_first_name),
 241                 'last_name' => $this->format($itemDAO->contact_last_name),
 242               );
 243             }
 244
 245             // add split line for this item
 246             $journalEntries[$dao->financial_trxn_id]['splits'][$itemDAO->financial_item_id] = array(
 247               'trxn_date' => $this->format($itemDAO->transaction_date, 'date'),
 248               'spl_id' => $this->format($itemDAO->financial_item_id),
 249               'account_name' => $this->format($itemDAO->account_name),
 250               'amount' => '-' .$this->format($itemDAO->amount, 'money'),
 251               'contact_name' => $this->format($itemDAO->contact_name),
 252               'payment_instrument' => $this->format($itemDAO->payment_instrument),
 253               'description' => $this->format($itemDAO->description),
 254               'check_number' => $this->format($itemDAO->check_number),
 255               'currency' => $this->format($itemDAO->currency),
 256             );
 257           } // end items loop
 258           $itemDAO->free();
 259         }
 260         else {
 261           // In this case, split record just uses the FROM account from the trxn, and there's only one record here
 262           $journalEntries[$dao->financial_trxn_id]['splits'][] = array(
 263             'trxn_date' => $this->format($dao->trxn_date, 'date'),
 264             'spl_id' => $this->format($dao->financial_trxn_id),
 265             'account_name' => $this->format($dao->from_account_name),
 266             'amount' => '-' . $this->format($dao->debit_total_amount, 'money'),
 267             'contact_name' => $this->format($dao->contact_from_name),
 268             'description' => $this->format($dao->item_description),
 269             'payment_instrument' => $this->format($dao->payment_instrument),
 270             'check_number' => $this->format($dao->check_number),
 271             'currency' => $this->format($dao->currency),
 272           );
 273         }
 274       }
 275       $exportParams = array(
 276         'accounts' => $accounts,
 277         'contacts' => $contacts,
 278         'journalEntries' => $journalEntries,
 279       );
 280       self::export($exportParams);
 281     }
 282     parent::initiateDownload();
 283   }
 284
 285   function exportACCNT() {
 286     self::assign( 'accounts', $this->_exportParams['accounts'] );
 287   }
 288
 289   function exportCUST() {
 290     self::assign( 'contacts', $this->_exportParams['contacts'] );
 291   }
 292
 293   function exportTRANS() {
 294     self::assign( 'journalEntries', $this->_exportParams['journalEntries'] );
 295   }
 296
 297   function getMimeType() {
 298     return 'application/octet-stream';
 299   }
 300
 301   function getFileExtension() {
 302     return 'iif';
 303   }
 304
 305   function getHookedTemplateFileName() {
 306     return 'CRM/Financial/ExportFormat/IIF.tpl';
 307   }
 308
 309   /*
 310    * $s the input string
 311    * $type can be string, date, or notepad
 312    */
 313   static function format($s, $type = 'string') {
 314     // If I remember right there's a couple things:
 315     // NOTEPAD field needs to be surrounded by quotes and then get rid of double quotes inside, also newlines should be literal \n, and ditch any ascii 0x0d's.
 316     // Date handling has changed over the years. It used to only understand mm/dd/yy but I think now it might depend on your OS settings. Sometimes mm/dd/yyyy works but sometimes it wants yyyy/mm/dd, at least where I had used it.
 317     // In all cases need to do something with tabs in the input.
 318
 319     $s1 = str_replace( self::$SEPARATOR, '\t', $s );
 320     switch( $type ) {
 321       case 'date':
 322         $sout = date( 'Y/m/d', strtotime( $s1 ) );
 323         break;
 324       case 'money':
 325         $sout = CRM_Utils_Money::format($s, null, null, true);
 326         break;
 327       case 'string':
 328       case 'notepad':
 329         $s2 = str_replace( "\n", '\n', $s1 );
 330         $s3 = str_replace( "\r", '', $s2 );
 331         $s4 = str_replace( '"', "'", $s3 );
 332         if ($type == 'notepad') {
 333           $sout = '"' . $s4 . '"';
 334         }
 335         else {
 336           $sout = $s4;
 337         }
 338         break;
 339     }
 340
 341     return $sout;
 342   }
 343 }