CRM/Financial/BAO/ExportFormat/CSV.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2014                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  10  | CiviCRM is free software; you can copy, modify, and distribute it  |
  11  | under the terms of the GNU Affero General Public License           |
  12  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13  |                                                                    |
  14  | CiviCRM is distributed in the hope that it will be useful, but     |
  15  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17  | See the GNU Affero General Public License for more details.        |
  18  |                                                                    |
  19  | You should have received a copy of the GNU Affero General Public   |
  20  | License and the CiviCRM Licensing Exception along                  |
  21  | with this program; if not, contact CiviCRM LLC                     |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2014
  32  * $Id$
  33  *
  34  */
  35
  36 /**
  37  * @link http://wiki.civicrm.org/confluence/display/CRM/CiviAccounts+Specifications+-++Batches#CiviAccountsSpecifications-Batches-%C2%A0Overviewofimplementation
  38  */
  39 class CRM_Financial_BAO_ExportFormat_CSV extends CRM_Financial_BAO_ExportFormat {
  40
  41   // 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.
  42   // Possibly in the future this could be selected by the user.
  43   public static $complementaryTables = array(
  44     'ACCNT',
  45     'CUST',
  46   );
  47
  48   /**
  49    * Class constructor
  50    */
  51   public function __construct() {
  52     parent::__construct();
  53   }
  54
  55   /**
  56    * @param array $exportParams
  57    */
  58   public function export($exportParams) {
  59     $export = parent::export($exportParams);
  60
  61     // Save the file in the public directory
  62     $fileName = self::putFile($export);
  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($fileName);
  73   }
  74
  75   /**
  76    * @param int $batchId
  77    *
  78    * @return Object
  79    */
  80   public function generateExportQuery($batchId) {
  81     $sql = "SELECT
  82       ft.id as financial_trxn_id,
  83       ft.trxn_date,
  84       fa_to.accounting_code AS to_account_code,
  85       fa_to.name AS to_account_name,
  86       fa_to.account_type_code AS to_account_type_code,
  87       ft.total_amount AS debit_total_amount,
  88       ft.trxn_id AS trxn_id,
  89       cov.label AS payment_instrument,
  90       ft.check_number,
  91       c.source AS source,
  92       ft.currency AS currency,
  93       cov_status.label AS status,
  94       CASE
  95         WHEN efti.entity_id IS NOT NULL
  96         THEN efti.amount
  97         ELSE eftc.amount
  98       END AS amount,
  99       fa_from.account_type_code AS credit_account_type_code,
 100       fa_from.accounting_code AS credit_account,
 101       fa_from.name AS credit_account_name,
 102       fac.account_type_code AS from_credit_account_type_code,
 103       fac.accounting_code AS from_credit_account,
 104       fac.name AS from_credit_account_name,
 105       fi.description AS item_description
 106       FROM civicrm_entity_batch eb
 107       LEFT JOIN civicrm_financial_trxn ft ON (eb.entity_id = ft.id AND eb.entity_table = 'civicrm_financial_trxn')
 108       LEFT JOIN civicrm_financial_account fa_to ON fa_to.id = ft.to_financial_account_id
 109       LEFT JOIN civicrm_financial_account fa_from ON fa_from.id = ft.from_financial_account_id
 110       LEFT JOIN civicrm_option_group cog ON cog.name = 'payment_instrument'
 111       LEFT JOIN civicrm_option_value cov ON (cov.value = ft.payment_instrument_id AND cov.option_group_id = cog.id)
 112       LEFT JOIN civicrm_entity_financial_trxn eftc ON (eftc.financial_trxn_id  = ft.id AND eftc.entity_table = 'civicrm_contribution')
 113       LEFT JOIN civicrm_contribution c ON c.id = eftc.entity_id
 114       LEFT JOIN civicrm_option_group cog_status ON cog_status.name = 'contribution_status'
 115       LEFT JOIN civicrm_option_value cov_status ON (cov_status.value = ft.status_id AND cov_status.option_group_id = cog_status.id)
 116       LEFT JOIN civicrm_entity_financial_trxn efti ON (efti.financial_trxn_id  = ft.id AND efti.entity_table = 'civicrm_financial_item')
 117       LEFT JOIN civicrm_financial_item fi ON fi.id = efti.entity_id
 118       LEFT JOIN civicrm_financial_account fac ON fac.id = fi.financial_account_id
 119       LEFT JOIN civicrm_financial_account fa ON fa.id = fi.financial_account_id
 120       WHERE eb.batch_id = ( %1 )";
 121
 122     $params = array(1 => array($batchId, 'String'));
 123     $dao = CRM_Core_DAO::executeQuery($sql, $params);
 124
 125     return $dao;
 126   }
 127
 128   /**
 129    * @param $export
 130    *
 131    * @return string
 132    */
 133   public function putFile($export) {
 134     $config = CRM_Core_Config::singleton();
 135     $fileName = $config->uploadDir . 'Financial_Transactions_' . $this->_batchIds . '_' . date('YmdHis') . '.' . $this->getFileExtension();
 136     $this->_downloadFile[] = $config->customFileUploadDir . CRM_Utils_File::cleanFileName(basename($fileName));
 137     $out = fopen($fileName, 'w');
 138     fputcsv($out, $export['headers']);
 139     unset($export['headers']);
 140     if (!empty($export)) {
 141       foreach ($export as $fields) {
 142         fputcsv($out, $fields);
 143       }
 144       fclose($out);
 145     }
 146     return $fileName;
 147   }
 148
 149   /**
 150    * Format table headers
 151    *
 152    * @param array $values
 153    * @return array
 154    */
 155   public function formatHeaders($values) {
 156     $arrayKeys = array_keys($values);
 157     $headers = '';
 158     if (!empty($arrayKeys)) {
 159       foreach ($values[$arrayKeys[0]] as $title => $value) {
 160         $headers[] = $title;
 161       }
 162     }
 163     return $headers;
 164   }
 165
 166   /**
 167    * Generate CSV array for export
 168    *
 169    * @param array $export
 170    */
 171   public function makeCSV($export) {
 172     foreach ($export as $batchId => $dao) {
 173       $financialItems = array();
 174       $this->_batchIds = $batchId;
 175       while ($dao->fetch()) {
 176         $creditAccountName = $creditAccountType = $creditAccount = NULL;
 177         if ($dao->credit_account) {
 178           $creditAccountName = $dao->credit_account_name;
 179           $creditAccountType = $dao->credit_account_type_code;
 180           $creditAccount = $dao->credit_account;
 181         }
 182         else {
 183           $creditAccountName = $dao->from_credit_account_name;
 184           $creditAccountType = $dao->from_credit_account_type_code;
 185           $creditAccount = $dao->from_credit_account;
 186         }
 187
 188         $financialItems[] = array(
 189           'Internal ID' => $dao->financial_trxn_id,
 190           'Transaction Date' => $dao->trxn_date,
 191           'Debit Account' => $dao->to_account_code,
 192           'Debit Account Name' => $dao->to_account_name,
 193           'Debit Account Type' => $dao->to_account_type_code,
 194           'Debit Account Amount (Unsplit)' => $dao->debit_total_amount,
 195           'Transaction ID (Unsplit)' => $dao->trxn_id,
 196           'Debit amount (Split)' => $dao->amount,
 197           'Payment Instrument' => $dao->payment_instrument,
 198           'Check Number' => $dao->check_number,
 199           'Source' => $dao->source,
 200           'Currency' => $dao->currency,
 201           'Transaction Status' => $dao->status,
 202           'Amount' => $dao->amount,
 203           'Credit Account' => $creditAccount,
 204           'Credit Account Name' => $creditAccountName,
 205           'Credit Account Type' => $creditAccountType,
 206           'Item Description' => $dao->item_description,
 207         );
 208       }
 209       $financialItems['headers'] = self::formatHeaders($financialItems);
 210       self::export($financialItems);
 211     }
 212     parent::initiateDownload();
 213   }
 214
 215   /**
 216    * @return string
 217    */
 218   public function getFileExtension() {
 219     return 'csv';
 220   }
 221
 222   public function exportACCNT() {
 223   }
 224
 225   public function exportCUST() {
 226   }
 227
 228   public function exportTRANS() {
 229   }
 230
 231 }