CRM/Financial/BAO/ExportFormat/CSV.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   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-2015
  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       c.id AS contribution_id,
  93       c.contact_id AS contact_id,
  94       eb.batch_id AS batch_id,
  95       ft.currency AS currency,
  96       cov_status.label AS status,
  97       CASE
  98         WHEN efti.entity_id IS NOT NULL
  99         THEN efti.amount
 100         ELSE eftc.amount
 101       END AS amount,
 102       fa_from.account_type_code AS credit_account_type_code,
 103       fa_from.accounting_code AS credit_account,
 104       fa_from.name AS credit_account_name,
 105       fac.account_type_code AS from_credit_account_type_code,
 106       fac.accounting_code AS from_credit_account,
 107       fac.name AS from_credit_account_name,
 108       fi.description AS item_description
 109       FROM civicrm_entity_batch eb
 110       LEFT JOIN civicrm_financial_trxn ft ON (eb.entity_id = ft.id AND eb.entity_table = 'civicrm_financial_trxn')
 111       LEFT JOIN civicrm_financial_account fa_to ON fa_to.id = ft.to_financial_account_id
 112       LEFT JOIN civicrm_financial_account fa_from ON fa_from.id = ft.from_financial_account_id
 113       LEFT JOIN civicrm_option_group cog ON cog.name = 'payment_instrument'
 114       LEFT JOIN civicrm_option_value cov ON (cov.value = ft.payment_instrument_id AND cov.option_group_id = cog.id)
 115       LEFT JOIN civicrm_entity_financial_trxn eftc ON (eftc.financial_trxn_id  = ft.id AND eftc.entity_table = 'civicrm_contribution')
 116       LEFT JOIN civicrm_contribution c ON c.id = eftc.entity_id
 117       LEFT JOIN civicrm_option_group cog_status ON cog_status.name = 'contribution_status'
 118       LEFT JOIN civicrm_option_value cov_status ON (cov_status.value = ft.status_id AND cov_status.option_group_id = cog_status.id)
 119       LEFT JOIN civicrm_entity_financial_trxn efti ON (efti.financial_trxn_id  = ft.id AND efti.entity_table = 'civicrm_financial_item')
 120       LEFT JOIN civicrm_financial_item fi ON fi.id = efti.entity_id
 121       LEFT JOIN civicrm_financial_account fac ON fac.id = fi.financial_account_id
 122       LEFT JOIN civicrm_financial_account fa ON fa.id = fi.financial_account_id
 123       WHERE eb.batch_id = ( %1 )";
 124
 125     CRM_Utils_Hook::batchQuery($sql);
 126
 127     $params = array(1 => array($batchId, 'String'));
 128     $dao = CRM_Core_DAO::executeQuery($sql, $params);
 129
 130     return $dao;
 131   }
 132
 133   /**
 134    * @param $export
 135    *
 136    * @return string
 137    */
 138   public function putFile($export) {
 139     $config = CRM_Core_Config::singleton();
 140     $fileName = $config->uploadDir . 'Financial_Transactions_' . $this->_batchIds . '_' . date('YmdHis') . '.' . $this->getFileExtension();
 141     $this->_downloadFile[] = $config->customFileUploadDir . CRM_Utils_File::cleanFileName(basename($fileName));
 142     $out = fopen($fileName, 'w');
 143     fputcsv($out, $export['headers']);
 144     unset($export['headers']);
 145     if (!empty($export)) {
 146       foreach ($export as $fields) {
 147         fputcsv($out, $fields);
 148       }
 149       fclose($out);
 150     }
 151     return $fileName;
 152   }
 153
 154   /**
 155    * Format table headers.
 156    *
 157    * @param array $values
 158    * @return array
 159    */
 160   public function formatHeaders($values) {
 161     $arrayKeys = array_keys($values);
 162     $headers = '';
 163     if (!empty($arrayKeys)) {
 164       foreach ($values[$arrayKeys[0]] as $title => $value) {
 165         $headers[] = $title;
 166       }
 167     }
 168     return $headers;
 169   }
 170
 171   /**
 172    * Generate CSV array for export.
 173    *
 174    * @param array $export
 175    */
 176   public function makeCSV($export) {
 177     // getting data from admin page
 178     $prefixValue = CRM_Core_BAO_Setting::getItem(CRM_Core_BAO_Setting::CONTRIBUTE_PREFERENCES_NAME, 'contribution_invoice_settings');
 179
 180     foreach ($export as $batchId => $dao) {
 181       $financialItems = array();
 182       $this->_batchIds = $batchId;
 183
 184       $batchItems = array();
 185       $queryResults = array();
 186
 187       while ($dao->fetch()) {
 188         $creditAccountName = $creditAccountType = $creditAccount = NULL;
 189         if ($dao->credit_account) {
 190           $creditAccountName = $dao->credit_account_name;
 191           $creditAccountType = $dao->credit_account_type_code;
 192           $creditAccount = $dao->credit_account;
 193         }
 194         else {
 195           $creditAccountName = $dao->from_credit_account_name;
 196           $creditAccountType = $dao->from_credit_account_type_code;
 197           $creditAccount = $dao->from_credit_account;
 198         }
 199
 200         $invoiceNo = CRM_Utils_Array::value('invoice_prefix', $prefixValue) . "" . $dao->contribution_id;
 201
 202         $financialItems[] = array(
 203           'Batch ID' => $dao->batch_id,
 204           'Invoice No' => $invoiceNo,
 205           'Contact ID' => $dao->contact_id,
 206           'Financial Trxn ID/Internal ID' => $dao->financial_trxn_id,
 207           'Transaction Date' => $dao->trxn_date,
 208           'Debit Account' => $dao->to_account_code,
 209           'Debit Account Name' => $dao->to_account_name,
 210           'Debit Account Type' => $dao->to_account_type_code,
 211           'Debit Account Amount (Unsplit)' => $dao->debit_total_amount,
 212           'Transaction ID (Unsplit)' => $dao->trxn_id,
 213           'Debit amount (Split)' => $dao->amount,
 214           'Payment Instrument' => $dao->payment_instrument,
 215           'Check Number' => $dao->check_number,
 216           'Source' => $dao->source,
 217           'Currency' => $dao->currency,
 218           'Transaction Status' => $dao->status,
 219           'Amount' => $dao->amount,
 220           'Credit Account' => $creditAccount,
 221           'Credit Account Name' => $creditAccountName,
 222           'Credit Account Type' => $creditAccountType,
 223           'Item Description' => $dao->item_description,
 224         );
 225
 226         end($financialItems);
 227         $batchItems[] = &$financialItems[key($financialItems)];
 228         $queryResults[] = get_object_vars($dao);
 229       }
 230
 231       CRM_Utils_Hook::batchItems($queryResults, $batchItems);
 232
 233       $financialItems['headers'] = self::formatHeaders($financialItems);
 234       self::export($financialItems);
 235     }
 236     parent::initiateDownload();
 237   }
 238
 239   /**
 240    * @return string
 241    */
 242   public function getFileExtension() {
 243     return 'csv';
 244   }
 245
 246   public function exportACCNT() {
 247   }
 248
 249   public function exportCUST() {
 250   }
 251
 252   public function exportTRANS() {
 253   }
 254
 255 }