CRM/Financial/BAO/ExportFormat/CSV.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.6                                                |
   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  * @link http://wiki.civicrm.org/confluence/display/CRM/CiviAccounts+Specifications+-++Batches#CiviAccountsSpecifications-Batches-%C2%A0Overviewofimplementation
  39  */
  40 class CRM_Financial_BAO_ExportFormat_CSV extends CRM_Financial_BAO_ExportFormat {
  41
  42   // 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.
  43   // Possibly in the future this could be selected by the user.
  44   public static $complementaryTables = array(
  45     'ACCNT',
  46     'CUST',
  47   );
  48
  49   /**
  50    * Class constructor
  51    */
  52   public function __construct() {
  53     parent::__construct();
  54   }
  55
  56   /**
  57    * @param array $exportParams
  58    */
  59   public function export($exportParams) {
  60     $export = parent::export($exportParams);
  61
  62     // Save the file in the public directory
  63     $fileName = self::putFile($export);
  64
  65     foreach (self::$complementaryTables as $rct) {
  66       $func = "export{$rct}";
  67       $this->$func();
  68     }
  69
  70     // now do general journal entries
  71     $this->exportTRANS();
  72
  73     $this->output($fileName);
  74   }
  75
  76   /**
  77    * @param int $batchId
  78    *
  79    * @return Object
  80    */
  81   public function generateExportQuery($batchId) {
  82     $sql = "SELECT
  83       ft.id as financial_trxn_id,
  84       ft.trxn_date,
  85       fa_to.accounting_code AS to_account_code,
  86       fa_to.name AS to_account_name,
  87       fa_to.account_type_code AS to_account_type_code,
  88       ft.total_amount AS debit_total_amount,
  89       ft.trxn_id AS trxn_id,
  90       cov.label AS payment_instrument,
  91       ft.check_number,
  92       c.source AS source,
  93       ft.currency AS currency,
  94       cov_status.label AS status,
  95       CASE
  96         WHEN efti.entity_id IS NOT NULL
  97         THEN efti.amount
  98         ELSE eftc.amount
  99       END AS amount,
 100       fa_from.account_type_code AS credit_account_type_code,
 101       fa_from.accounting_code AS credit_account,
 102       fa_from.name AS credit_account_name,
 103       fac.account_type_code AS from_credit_account_type_code,
 104       fac.accounting_code AS from_credit_account,
 105       fac.name AS from_credit_account_name,
 106       fi.description AS item_description
 107       FROM civicrm_entity_batch eb
 108       LEFT JOIN civicrm_financial_trxn ft ON (eb.entity_id = ft.id AND eb.entity_table = 'civicrm_financial_trxn')
 109       LEFT JOIN civicrm_financial_account fa_to ON fa_to.id = ft.to_financial_account_id
 110       LEFT JOIN civicrm_financial_account fa_from ON fa_from.id = ft.from_financial_account_id
 111       LEFT JOIN civicrm_option_group cog ON cog.name = 'payment_instrument'
 112       LEFT JOIN civicrm_option_value cov ON (cov.value = ft.payment_instrument_id AND cov.option_group_id = cog.id)
 113       LEFT JOIN civicrm_entity_financial_trxn eftc ON (eftc.financial_trxn_id  = ft.id AND eftc.entity_table = 'civicrm_contribution')
 114       LEFT JOIN civicrm_contribution c ON c.id = eftc.entity_id
 115       LEFT JOIN civicrm_option_group cog_status ON cog_status.name = 'contribution_status'
 116       LEFT JOIN civicrm_option_value cov_status ON (cov_status.value = ft.status_id AND cov_status.option_group_id = cog_status.id)
 117       LEFT JOIN civicrm_entity_financial_trxn efti ON (efti.financial_trxn_id  = ft.id AND efti.entity_table = 'civicrm_financial_item')
 118       LEFT JOIN civicrm_financial_item fi ON fi.id = efti.entity_id
 119       LEFT JOIN civicrm_financial_account fac ON fac.id = fi.financial_account_id
 120       LEFT JOIN civicrm_financial_account fa ON fa.id = fi.financial_account_id
 121       WHERE eb.batch_id = ( %1 )";
 122
 123     $params = array(1 => array($batchId, 'String'));
 124     $dao = CRM_Core_DAO::executeQuery($sql, $params);
 125
 126     return $dao;
 127   }
 128
 129   /**
 130    * @param $export
 131    *
 132    * @return string
 133    */
 134   public function putFile($export) {
 135     $config = CRM_Core_Config::singleton();
 136     $fileName = $config->uploadDir . 'Financial_Transactions_' . $this->_batchIds . '_' . date('YmdHis') . '.' . $this->getFileExtension();
 137     $this->_downloadFile[] = $config->customFileUploadDir . CRM_Utils_File::cleanFileName(basename($fileName));
 138     $out = fopen($fileName, 'w');
 139     fputcsv($out, $export['headers']);
 140     unset($export['headers']);
 141     if (!empty($export)) {
 142       foreach ($export as $fields) {
 143         fputcsv($out, $fields);
 144       }
 145       fclose($out);
 146     }
 147     return $fileName;
 148   }
 149
 150   /**
 151    * Format table headers
 152    *
 153    * @param array $values
 154    * @return array
 155    */
 156   public function formatHeaders($values) {
 157     $arrayKeys = array_keys($values);
 158     $headers = '';
 159     if (!empty($arrayKeys)) {
 160       foreach ($values[$arrayKeys[0]] as $title => $value) {
 161         $headers[] = $title;
 162       }
 163     }
 164     return $headers;
 165   }
 166
 167   /**
 168    * Generate CSV array for export
 169    *
 170    * @param array $export
 171    */
 172   public function makeCSV($export) {
 173     foreach ($export as $batchId => $dao) {
 174       $financialItems = array();
 175       $this->_batchIds = $batchId;
 176       while ($dao->fetch()) {
 177         $creditAccountName = $creditAccountType =
 178         $creditAccount = NULL;
 179         if ($dao->credit_account) {
 180           $creditAccountName = $dao->credit_account_name;
 181           $creditAccountType = $dao->credit_account_type_code;
 182           $creditAccount = $dao->credit_account;
 183         }
 184         else {
 185           $creditAccountName = $dao->from_credit_account_name;
 186           $creditAccountType = $dao->from_credit_account_type_code;
 187           $creditAccount = $dao->from_credit_account;
 188         }
 189
 190         $financialItems[] = array(
 191           'Internal ID' => $dao->financial_trxn_id,
 192           'Transaction Date' => $dao->trxn_date,
 193           'Debit Account' => $dao->to_account_code,
 194           'Debit Account Name' => $dao->to_account_name,
 195           'Debit Account Type' => $dao->to_account_type_code,
 196           'Debit Account Amount (Unsplit)' => $dao->debit_total_amount,
 197           'Transaction ID (Unsplit)' => $dao->trxn_id,
 198           'Debit amount (Split)' => $dao->amount,
 199           'Payment Instrument' => $dao->payment_instrument,
 200           'Check Number' => $dao->check_number,
 201           'Source' => $dao->source,
 202           'Currency' => $dao->currency,
 203           'Transaction Status' => $dao->status,
 204           'Amount' => $dao->amount,
 205           'Credit Account' => $creditAccount,
 206           'Credit Account Name' => $creditAccountName,
 207           'Credit Account Type' => $creditAccountType,
 208           'Item Description' => $dao->item_description,
 209         );
 210       }
 211       $financialItems['headers'] = self::formatHeaders($financialItems);
 212       self::export($financialItems);
 213     }
 214     parent::initiateDownload();
 215   }
 216
 217   /**
 218    * @return string
 219    */
 220   public function getFileExtension() {
 221     return 'csv';
 222   }
 223
 224   public function exportACCNT() {
 225   }
 226
 227   public function exportCUST() {
 228   }
 229
 230   public function exportTRANS() {
 231   }
 232 }