CRM/Report/Form/Contribute/Bookkeeping.php

   1 <?php
   2 // $Id$
   3
   4 /*
   5  +--------------------------------------------------------------------+
   6  | CiviCRM version 4.3                                                |
   7  +--------------------------------------------------------------------+
   8  | Copyright CiviCRM LLC (c) 2004-2013                                |
   9  +--------------------------------------------------------------------+
  10  | This file is a part of CiviCRM.                                    |
  11  |                                                                    |
  12  | CiviCRM is free software; you can copy, modify, and distribute it  |
  13  | under the terms of the GNU Affero General Public License           |
  14  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  15  |                                                                    |
  16  | CiviCRM is distributed in the hope that it will be useful, but     |
  17  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  18  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  19  | See the GNU Affero General Public License for more details.        |
  20  |                                                                    |
  21  | You should have received a copy of the GNU Affero General Public   |
  22  | License and the CiviCRM Licensing Exception along                  |
  23  | with this program; if not, contact CiviCRM LLC                     |
  24  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  25  | GNU Affero General Public License or the licensing of CiviCRM,     |
  26  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  27  +--------------------------------------------------------------------+
  28 */
  29
  30 /**
  31  *
  32  * @package CRM
  33  * @copyright CiviCRM LLC (c) 2004-2013
  34  * $Id$
  35  *
  36  */
  37 class CRM_Report_Form_Contribute_Bookkeeping extends CRM_Report_Form {
  38   protected $_addressField = FALSE;
  39
  40   protected $_emailField = FALSE;
  41
  42   protected $_summary = NULL;
  43
  44   protected $_customGroupExtends = array(
  45     'Membership');
  46
  47   function __construct() {
  48     $this->_columns = array(
  49       'civicrm_contact' =>
  50       array(
  51         'dao' => 'CRM_Contact_DAO_Contact',
  52         'fields' =>
  53         array(
  54           'sort_name' =>
  55           array('title' => ts('Contact Name'),
  56             'required' => TRUE,
  57             'no_repeat' => TRUE,
  58           ),
  59           'id' =>
  60           array(
  61             'no_display' => TRUE,
  62             'required' => TRUE,
  63           ),
  64         ),
  65         'filters' =>
  66         array(
  67           'sort_name' =>
  68           array('title' => ts('Contact Name'),
  69             'operator' => 'like',
  70           ),
  71           'id' =>
  72           array('title' => ts('Contact ID'),
  73             'no_display' => TRUE,
  74           ),
  75         ),
  76         'grouping' => 'contact-fields',
  77       ),
  78       'civicrm_membership' =>
  79       array(
  80         'dao' => 'CRM_Member_DAO_Membership',
  81         'fields' =>
  82         array(
  83           'id' =>
  84           array('title' => ts('Membership #'),
  85             'no_display' => TRUE,
  86             'required' => TRUE,
  87           ),
  88         ),
  89       ),
  90       'civicrm_contribution' =>
  91       array(
  92         'dao' => 'CRM_Contribute_DAO_Contribution',
  93         'fields' =>
  94         array(
  95           'receive_date' => array('default' => TRUE),
  96           'trxn_id' => array('title' => ts('Trans #'),
  97             'default' => TRUE,
  98           ),
  99           'invoice_id' => array('title' => ts('Invoice ID'),
 100             'default' => TRUE,
 101           ),
 102           'contribution_status_id' => array('title' => ts('Status'),
 103             'default' => TRUE,
 104           ),
 105           'id' => array('title' => ts('Contribution #'),
 106             'default' => TRUE,
 107           ),
 108         ),
 109         'filters' =>
 110         array(
 111           'receive_date' =>
 112           array('operatorType' => CRM_Report_Form::OP_DATE),
 113           'contribution_status_id' =>
 114           array('title' => ts('Contribution Status'),
 115             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 116             'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 117             'default' => array(1),
 118           ),
 119         ),
 120         'grouping' => 'contri-fields',
 121       ),
 122       'civicrm_line_item' => array(
 123         'dao' => 'CRM_Price_DAO_LineItem',
 124         'fields' => array(
 125           'financial_type_id' => array('title' => ts('Financial Type'),
 126             'default' => TRUE,
 127           ),
 128         ),
 129         'filters' => array(
 130           'financial_type_id' => array(
 131             'title' => ts('Financial Type'),
 132             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 133             'options' => CRM_Contribute_PseudoConstant::financialType(),
 134           ),
 135         ),
 136       ),
 137       'civicrm_financial_trxn' => array(
 138         'dao' => 'CRM_Financial_DAO_FinancialTrxn',
 139         'fields' => array(
 140           'check_number' => array(
 141             'title' => ts('Cheque #'),
 142             'default' => TRUE,
 143           ),
 144           'payment_instrument_id' => array('title' => ts('Payment Instrument'),
 145             'default' => TRUE,
 146           ),
 147         ),
 148         'filters' =>
 149         array(
 150           'payment_instrument_id' => array(
 151             'title' => ts('Paid By'),
 152             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 153             'options' => CRM_Contribute_PseudoConstant::paymentInstrument(),
 154           ),
 155         ),
 156       ),
 157       'civicrm_entity_financial_trxn' => array(
 158         'dao' => 'CRM_Financial_DAO_EntityFinancialTrxn',
 159         'fields' => array(
 160           'amount' => array(
 161             'title' => ts('Amount'),
 162             'default' => TRUE,
 163           ),
 164         ),
 165         'filters' =>
 166         array(
 167           'amount' =>
 168           array('title' => ts('Amount')),
 169         ),
 170       ),
 171       'civicrm_financial_account' => array(
 172         'dao' => 'CRM_Financial_DAO_FinancialAccount',
 173         'fields' => array(
 174           'debit_accounting_code' => array(
 175             'title' => ts('Financial Account Code- Debit'),
 176             'name'  => 'accounting_code',
 177             'alias' => 'financial_account_civireport_debit',
 178             'default' => TRUE,
 179           ),
 180           'credit_accounting_code' => array(
 181             'title' => ts('Financial Account Code- Credit'),
 182             'name'  => 'accounting_code',
 183             'alias' => 'financial_account_civireport_credit',
 184             'default' => TRUE,
 185           ),
 186         )
 187       ),
 188     );
 189     parent::__construct();
 190   }
 191
 192   function preProcess() {
 193     parent::preProcess();
 194   }
 195
 196   function select() {
 197     $select = array();
 198
 199     $this->_columnHeaders = array();
 200     foreach ($this->_columns as $tableName => $table) {
 201       if (array_key_exists('fields', $table)) {
 202         foreach ($table['fields'] as $fieldName => $field) {
 203           if (CRM_Utils_Array::value('required', $field) ||
 204             CRM_Utils_Array::value($fieldName, $this->_params['fields'])
 205           ) {
 206             if ($fieldName != 'credit_accounting_code' && $fieldName != 'amount') {
 207               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 208             }
 209             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 210             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 211           }
 212         }
 213       }
 214     }
 215
 216     $this->_select = "SELECT " . implode(', ', $select) . " ";
 217     $this->_select .= ", CASE
 218             WHEN {$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id IS NOT NULL
 219               THEN  {$this->_aliases['civicrm_financial_account']}_credit_1.accounting_code
 220               ELSE  {$this->_aliases['civicrm_financial_account']}_credit_2.accounting_code
 221             END AS civicrm_financial_account_credit_accounting_code, CASE
 222             WHEN  {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id IS NOT NULL
 223               THEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.amount
 224               ELSE {$this->_aliases['civicrm_entity_financial_trxn']}.amount
 225             END AS civicrm_entity_financial_trxn_amount ";
 226   }
 227
 228   function from() {
 229     $this->_from = NULL;
 230
 231     $this->_from = "FROM  civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}
 232               INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
 233                       ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND
 234                          {$this->_aliases['civicrm_contribution']}.is_test = 0
 235               LEFT JOIN civicrm_membership_payment payment
 236                         ON ( {$this->_aliases['civicrm_contribution']}.id = payment.contribution_id )
 237               LEFT JOIN civicrm_membership {$this->_aliases['civicrm_membership']}
 238                     ON payment.membership_id = {$this->_aliases['civicrm_membership']}.id
 239               LEFT JOIN civicrm_entity_financial_trxn {$this->_aliases['civicrm_entity_financial_trxn']}
 240                     ON ({$this->_aliases['civicrm_contribution']}.id = {$this->_aliases['civicrm_entity_financial_trxn']}.entity_id AND
 241                         {$this->_aliases['civicrm_entity_financial_trxn']}.entity_table = 'civicrm_contribution')
 242               LEFT JOIN civicrm_financial_trxn {$this->_aliases['civicrm_financial_trxn']}
 243                     ON {$this->_aliases['civicrm_financial_trxn']}.id = {$this->_aliases['civicrm_entity_financial_trxn']}.financial_trxn_id
 244               LEFT JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account']}_debit
 245                     ON {$this->_aliases['civicrm_financial_trxn']}.to_financial_account_id = {$this->_aliases['civicrm_financial_account']}_debit.id
 246               LEFT JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account']}_credit_1
 247                     ON {$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id = {$this->_aliases['civicrm_financial_account']}_credit_1.id
 248               LEFT JOIN civicrm_entity_financial_trxn {$this->_aliases['civicrm_entity_financial_trxn']}_item
 249                     ON ({$this->_aliases['civicrm_financial_trxn']}.id = {$this->_aliases['civicrm_entity_financial_trxn']}_item.financial_trxn_id AND
 250                         {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_table = 'civicrm_financial_item')
 251               LEFT JOIN civicrm_financial_item fitem
 252                     ON fitem.id = {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id
 253               LEFT JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account']}_credit_2
 254                     ON fitem.financial_account_id = {$this->_aliases['civicrm_financial_account']}_credit_2.id
 255               LEFT JOIN civicrm_line_item {$this->_aliases['civicrm_line_item']}
 256                     ON  fitem.entity_id = {$this->_aliases['civicrm_line_item']}.id AND fitem.entity_table = 'civicrm_line_item' ";
 257   }
 258
 259   function orderBy() {
 260           $this->_orderBy = " ORDER BY {$this->_aliases['civicrm_contact']}.sort_name, {$this->_aliases['civicrm_contribution']}.id, {$this->_aliases['civicrm_entity_financial_trxn']}.id ";
 261   }
 262
 263   function postProcess() {
 264     // get the acl clauses built before we assemble the query
 265     $this->buildACLClause($this->_aliases['civicrm_contact']);
 266     parent::postProcess();
 267   }
 268
 269   function statistics(&$rows) {
 270     $statistics = parent::statistics($rows);
 271
 272     $select = " SELECT COUNT({$this->_aliases['civicrm_financial_trxn']}.id ) as count,
 273                 SUM(CASE
 274                   WHEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id IS NOT NULL
 275                   THEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.amount
 276                   ELSE {$this->_aliases['civicrm_entity_financial_trxn']}.amount
 277                 END) as amount ";
 278
 279     $sql = "{$select} {$this->_from} {$this->_where}";
 280     $dao = CRM_Core_DAO::executeQuery($sql);
 281
 282     if ($dao->fetch()) {
 283       $statistics['counts']['amount'] = array(
 284         'value' => $dao->amount,
 285         'title' => 'Total Amount',
 286         'type' => CRM_Utils_Type::T_MONEY,
 287       );
 288       $statistics['counts']['avg'] = array(
 289         'value' => round(($dao->amount / $dao->count), 2),
 290         'title' => 'Average',
 291         'type' => CRM_Utils_Type::T_MONEY,
 292       );
 293     }
 294
 295     return $statistics;
 296   }
 297
 298   function alterDisplay(&$rows) {
 299     $contributionTypes = CRM_Contribute_PseudoConstant::financialType();
 300     $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument();
 301     $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus();
 302     foreach ($rows as $rowNum => $row) {
 303       // convert display name to links
 304       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 305         CRM_Utils_Array::value('civicrm_contact_sort_name', $rows[$rowNum]) &&
 306         array_key_exists('civicrm_contact_id', $row)
 307       ) {
 308         $url = CRM_Utils_System::url("civicrm/contact/view",
 309           'reset=1&cid=' . $row['civicrm_contact_id'],
 310           $this->_absoluteUrl
 311         );
 312         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 313         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
 314       }
 315
 316       // handle contribution status id
 317       if ($value = CRM_Utils_Array::value('civicrm_contribution_contribution_status_id', $row)) {
 318         $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value];
 319       }
 320
 321       // handle payment instrument id
 322       if ($value = CRM_Utils_Array::value('civicrm_financial_trxn_payment_instrument_id', $row)) {
 323         $rows[$rowNum]['civicrm_financial_trxn_payment_instrument_id'] = $paymentInstruments[$value];
 324       }
 325
 326       // handle financial type id
 327       if ($value = CRM_Utils_Array::value('civicrm_line_item_financial_type_id', $row)) {
 328         $rows[$rowNum]['civicrm_line_item_financial_type_id'] = $contributionTypes[$value];
 329       }
 330     }
 331   }
 332 }
 333