CRM/Financial/BAO/ExportFormat/IIF.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.5                                                |
   6  +--------------------------------------------------------------------+
   7  | Copyright CiviCRM LLC (c) 2004-2014                                |
   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-2014
  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   /**
  62    * @param array $exportParams
  63    */
  64   function export( $exportParams ) {
  65     parent::export( $exportParams );
  66
  67     foreach( self::$complementaryTables as $rct ) {
  68       $func = "export{$rct}";
  69       $this->$func();
  70     }
  71
  72     // now do general journal entries
  73     $this->exportTRANS();
  74
  75     $this->output();
  76   }
  77
  78   /**
  79    * @param $out
  80    *
  81    * @return string
  82    */
  83   function putFile($out) {
  84     $config = CRM_Core_Config::singleton();
  85     $fileName = $config->uploadDir.'Financial_Transactions_'.$this->_batchIds.'_'.date('YmdHis').'.'.$this->getFileExtension();
  86     $this->_downloadFile[] = $config->customFileUploadDir.CRM_Utils_File::cleanFileName(basename($fileName));
  87     $buffer = fopen($fileName, 'w');
  88     fwrite($buffer, $out);
  89     fclose($buffer);
  90     return $fileName;
  91   }
  92
  93   /**
  94    * @param int $batchId
  95    *
  96    * @return Object
  97    */
  98   function generateExportQuery($batchId) {
  99
 100     $sql = "SELECT
 101       ft.id as financial_trxn_id,
 102       ft.trxn_date,
 103       ft.total_amount AS debit_total_amount,
 104       ft.currency AS currency,
 105       ft.trxn_id AS trxn_id,
 106       cov.label AS payment_instrument,
 107       ft.check_number,
 108       fa_from.id AS from_account_id,
 109       fa_from.name AS from_account_name,
 110       fa_from.accounting_code AS from_account_code,
 111       fa_from.financial_account_type_id AS from_account_type_id,
 112       fa_from.description AS from_account_description,
 113       fa_from.account_type_code AS from_account_type_code,
 114       fa_to.id AS to_account_id,
 115       fa_to.name AS to_account_name,
 116       fa_to.accounting_code AS to_account_code,
 117       fa_to.financial_account_type_id AS to_account_type_id,
 118       fa_to.account_type_code AS to_account_type_code,
 119       fa_to.description AS to_account_description,
 120       fi.description AS item_description,
 121       contact_from.id AS contact_from_id,
 122       contact_from.display_name AS contact_from_name,
 123       contact_from.first_name AS contact_from_first_name,
 124       contact_from.last_name AS contact_from_last_name,
 125       contact_to.id AS contact_to_id,
 126       contact_to.display_name AS contact_to_name,
 127       contact_to.first_name AS contact_to_first_name,
 128       contact_to.last_name AS contact_to_last_name
 129       FROM civicrm_entity_batch eb
 130       LEFT JOIN civicrm_financial_trxn ft ON (eb.entity_id = ft.id AND eb.entity_table = 'civicrm_financial_trxn')
 131       LEFT JOIN civicrm_financial_account fa_from ON fa_from.id = ft.from_financial_account_id
 132       LEFT JOIN civicrm_financial_account fa_to ON fa_to.id = ft.to_financial_account_id
 133       LEFT JOIN civicrm_option_group cog ON cog.name = 'payment_instrument'
 134       LEFT JOIN civicrm_option_value cov ON (cov.value = ft.payment_instrument_id AND cov.option_group_id = cog.id)
 135       LEFT JOIN civicrm_contact contact_from ON contact_from.id = fa_from.contact_id
 136       LEFT JOIN civicrm_contact contact_to ON contact_to.id = fa_to.contact_id
 137       LEFT JOIN civicrm_entity_financial_trxn efti ON (efti.financial_trxn_id  = ft.id AND efti.entity_table = 'civicrm_financial_item')
 138       LEFT JOIN civicrm_financial_item fi ON fi.id = efti.entity_id
 139       WHERE eb.batch_id = ( %1 )";
 140
 141     $params = array(1 => array($batchId, 'String'));
 142     $dao = CRM_Core_DAO::executeQuery( $sql, $params );
 143
 144     return $dao;
 145   }
 146
 147   /**
 148    * @param $export
 149    */
 150   function makeIIF($export) {
 151     // Keep running list of accounts and contacts used in this batch, since we need to
 152     // include those in the output. Only want to include ones used in the batch, not everything in the db,
 153     // since would increase the chance of messing up user's existing Quickbooks entries.
 154     foreach ($export as $batchId => $dao) {
 155       $accounts = $contacts = $journalEntries = $exportParams = array();
 156       $this->_batchIds = $batchId;
 157       while ($dao->fetch()) {
 158         // add to running list of accounts
 159         if (!empty($dao->from_account_id) && !isset($accounts[$dao->from_account_id])) {
 160           $accounts[$dao->from_account_id] = array(
 161             'name' => $this->format($dao->from_account_name),
 162             'account_code' => $this->format($dao->from_account_code),
 163             'description' => $this->format($dao->from_account_description),
 164             'type' => $this->format($dao->from_account_type_code),
 165           );
 166         }
 167         if (!empty($dao->to_account_id) && !isset($accounts[$dao->to_account_id])) {
 168           $accounts[$dao->to_account_id] = array(
 169             'name' => $this->format($dao->to_account_name),
 170             'account_code' => $this->format($dao->to_account_code),
 171             'description' => $this->format($dao->to_account_description),
 172             'type' => $this->format($dao->to_account_type_code),
 173           );
 174         }
 175
 176         // add to running list of contacts
 177         if (!empty($dao->contact_from_id) && !isset($contacts[$dao->contact_from_id])) {
 178           $contacts[$dao->contact_from_id] = array(
 179             'name' => $this->format($dao->contact_from_name),
 180             'first_name' => $this->format($dao->contact_from_first_name),
 181             'last_name' => $this->format($dao->contact_from_last_name),
 182           );
 183         }
 184
 185         if (!empty($dao->contact_to_id) && !isset($contacts[$dao->contact_to_id])) {
 186           $contacts[$dao->contact_to_id] = array(
 187             'name' => $this->format($dao->contact_to_name),
 188             'first_name' => $this->format($dao->contact_to_first_name),
 189             'last_name' => $this->format($dao->contact_to_last_name),
 190           );
 191         }
 192
 193         // set up the journal entries for this financial trxn
 194         $journalEntries[$dao->financial_trxn_id] = array(
 195           'to_account' => array(
 196             'trxn_date' => $this->format($dao->trxn_date, 'date'),
 197             'trxn_id' =>  $this->format($dao->trxn_id),
 198             'account_name' => $this->format($dao->to_account_name),
 199             'amount' => $this->format($dao->debit_total_amount, 'money'),
 200             'contact_name' => $this->format($dao->contact_to_name),
 201             'payment_instrument' => $this->format($dao->payment_instrument),
 202             'check_number' => $this->format($dao->check_number),
 203           ),
 204           'splits' => array(),
 205         );
 206
 207         /*
 208          * splits has two possibilities depending on FROM account
 209          */
 210         if (empty($dao->from_account_id)) {
 211           // In this case, split records need to use the individual financial_item account for each item in the trxn
 212           $item_sql = "SELECT
 213             fa.id AS account_id,
 214             fa.name AS account_name,
 215             fa.accounting_code AS account_code,
 216             fa.description AS account_description,
 217             fi.description AS description,
 218             fi.id AS financial_item_id,
 219             fi.currency AS currency,
 220             cov.label AS payment_instrument,
 221             ft.check_number AS check_number,
 222             fi.transaction_date AS transaction_date,
 223             fi.amount AS amount,
 224             fa.account_type_code AS account_type_code,
 225             contact.id AS contact_id,
 226             contact.display_name AS contact_name,
 227             contact.first_name AS contact_first_name,
 228             contact.last_name AS contact_last_name
 229             FROM civicrm_entity_financial_trxn eft
 230             LEFT JOIN civicrm_financial_item fi ON eft.entity_id = fi.id
 231             LEFT JOIN civicrm_financial_trxn ft ON ft.id = eft.financial_trxn_id
 232             LEFT JOIN civicrm_option_group cog ON cog.name = 'payment_instrument'
 233             LEFT JOIN civicrm_option_value cov ON (cov.value = ft.payment_instrument_id AND cov.option_group_id = cog.id)
 234             LEFT JOIN civicrm_financial_account fa ON fa.id = fi.financial_account_id
 235             LEFT JOIN civicrm_contact contact ON contact.id = fi.contact_id
 236             WHERE eft.entity_table = 'civicrm_financial_item'
 237             AND eft.financial_trxn_id = %1";
 238
 239           $itemParams = array( 1 => array( $dao->financial_trxn_id, 'Integer' ) );
 240
 241           $itemDAO = CRM_Core_DAO::executeQuery( $item_sql, $itemParams );
 242           while ($itemDAO->fetch()) {
 243             // add to running list of accounts
 244             if (!empty($itemDAO->account_id) && !isset($accounts[$itemDAO->account_id])) {
 245               $accounts[$itemDAO->account_id] = array(
 246                 'name' => $this->format($itemDAO->account_name),
 247                 'account_code' => $this->format($itemDAO->account_code),
 248                 'description' => $this->format($itemDAO->account_description),
 249                 'type' => $this->format($itemDAO->account_type_code),
 250               );
 251             }
 252
 253             if (!empty($itemDAO->contact_id) && !isset($contacts[$itemDAO->contact_id])) {
 254               $contacts[$itemDAO->contact_id] = array(
 255                 'name' => $this->format($itemDAO->contact_name),
 256                 'first_name' => $this->format($itemDAO->contact_first_name),
 257                 'last_name' => $this->format($itemDAO->contact_last_name),
 258               );
 259             }
 260
 261             // add split line for this item
 262             $journalEntries[$dao->financial_trxn_id]['splits'][$itemDAO->financial_item_id] = array(
 263               'trxn_date' => $this->format($itemDAO->transaction_date, 'date'),
 264               'spl_id' => $this->format($itemDAO->financial_item_id),
 265               'account_name' => $this->format($itemDAO->account_name),
 266               'amount' => '-' .$this->format($itemDAO->amount, 'money'),
 267               'contact_name' => $this->format($itemDAO->contact_name),
 268               'payment_instrument' => $this->format($itemDAO->payment_instrument),
 269               'description' => $this->format($itemDAO->description),
 270               'check_number' => $this->format($itemDAO->check_number),
 271               'currency' => $this->format($itemDAO->currency),
 272             );
 273           } // end items loop
 274           $itemDAO->free();
 275         }
 276         else {
 277           // In this case, split record just uses the FROM account from the trxn, and there's only one record here
 278           $journalEntries[$dao->financial_trxn_id]['splits'][] = array(
 279             'trxn_date' => $this->format($dao->trxn_date, 'date'),
 280             'spl_id' => $this->format($dao->financial_trxn_id),
 281             'account_name' => $this->format($dao->from_account_name),
 282             'amount' => '-' . $this->format($dao->debit_total_amount, 'money'),
 283             'contact_name' => $this->format($dao->contact_from_name),
 284             'description' => $this->format($dao->item_description),
 285             'payment_instrument' => $this->format($dao->payment_instrument),
 286             'check_number' => $this->format($dao->check_number),
 287             'currency' => $this->format($dao->currency),
 288           );
 289         }
 290       }
 291       $exportParams = array(
 292         'accounts' => $accounts,
 293         'contacts' => $contacts,
 294         'journalEntries' => $journalEntries,
 295       );
 296       self::export($exportParams);
 297     }
 298     parent::initiateDownload();
 299   }
 300
 301   function exportACCNT() {
 302     self::assign( 'accounts', $this->_exportParams['accounts'] );
 303   }
 304
 305   function exportCUST() {
 306     self::assign( 'contacts', $this->_exportParams['contacts'] );
 307   }
 308
 309   function exportTRANS() {
 310     self::assign( 'journalEntries', $this->_exportParams['journalEntries'] );
 311   }
 312
 313   /**
 314    * @return string
 315    */
 316   function getMimeType() {
 317     return 'application/octet-stream';
 318   }
 319
 320   /**
 321    * @return string
 322    */
 323   function getFileExtension() {
 324     return 'iif';
 325   }
 326
 327   /**
 328    * @return string
 329    */
 330   function getHookedTemplateFileName() {
 331     return 'CRM/Financial/ExportFormat/IIF.tpl';
 332   }
 333
 334   /*
 335    * $s the input string
 336    * $type can be string, date, or notepad
 337    */
 338   /**
 339    * @param $s
 340    * @param string $type
 341    *
 342    * @return bool|mixed|string
 343    */
 344   static function format($s, $type = 'string') {
 345     // If I remember right there's a couple things:
 346     // 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.
 347     // 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.
 348     // In all cases need to do something with tabs in the input.
 349
 350     $s1 = str_replace( self::$SEPARATOR, '\t', $s );
 351     switch( $type ) {
 352       case 'date':
 353         $sout = date( 'Y/m/d', strtotime( $s1 ) );
 354         break;
 355       case 'money':
 356         $sout = CRM_Utils_Money::format($s, null, null, true);
 357         break;
 358       case 'string':
 359       case 'notepad':
 360         $s2 = str_replace( "\n", '\n', $s1 );
 361         $s3 = str_replace( "\r", '', $s2 );
 362         $s4 = str_replace( '"', "'", $s3 );
 363         if ($type == 'notepad') {
 364           $sout = '"' . $s4 . '"';
 365         }
 366         else {
 367           $sout = $s4;
 368         }
 369         break;
 370     }
 371
 372     return $sout;
 373   }
 374 }