CRM/Report/Form/Contribute/Bookkeeping.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.4                                                |
   6  +--------------------------------------------------------------------+
   7  | Copyright CiviCRM LLC (c) 2004-2013                                |
   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-2013
  33  * $Id$
  34  *
  35  */
  36 class CRM_Report_Form_Contribute_Bookkeeping extends CRM_Report_Form {
  37   protected $_addressField = FALSE;
  38
  39   protected $_emailField = FALSE;
  40
  41   protected $_summary = NULL;
  42
  43   protected $_customGroupExtends = array(
  44     'Membership');
  45
  46   function __construct() {
  47     $this->_columns = array(
  48       'civicrm_contact' =>
  49       array(
  50         'dao' => 'CRM_Contact_DAO_Contact',
  51         'fields' =>
  52         array(
  53           'sort_name' =>
  54           array('title' => ts('Contact Name'),
  55             'required' => TRUE,
  56             'no_repeat' => TRUE,
  57           ),
  58           'id' =>
  59           array(
  60             'no_display' => TRUE,
  61             'required' => TRUE,
  62           ),
  63         ),
  64         'filters' =>
  65         array(
  66           'sort_name' =>
  67           array('title' => ts('Contact Name'),
  68             'operator' => 'like',
  69           ),
  70           'id' =>
  71           array('title' => ts('Contact ID'),
  72             'no_display' => TRUE,
  73           ),
  74         ),
  75         'grouping' => 'contact-fields',
  76       ),
  77       'civicrm_membership' =>
  78       array(
  79         'dao' => 'CRM_Member_DAO_Membership',
  80         'fields' =>
  81         array(
  82           'id' =>
  83           array('title' => ts('Membership #'),
  84             'no_display' => TRUE,
  85             'required' => TRUE,
  86           ),
  87         ),
  88       ),
  89       'civicrm_financial_account' => array(
  90         'dao' => 'CRM_Financial_DAO_FinancialAccount',
  91         'fields' => array(
  92           'debit_accounting_code' => array(
  93             'title' => ts('Financial Account Code - Debit'),
  94             'name'  => 'accounting_code',
  95             'alias' => 'financial_account_civireport_debit',
  96             'default' => TRUE,
  97           ),
  98           'credit_accounting_code' => array(
  99             'title' => ts('Financial Account Code - Credit'),
 100             'name'  => 'accounting_code',
 101             'alias' => 'financial_account_civireport_credit',
 102             'default' => TRUE,
 103           ),
 104           'debit_name' => array(
 105             'title' => ts('Financial Account Name - Debit'),
 106             'name'  => 'name',
 107             'alias' => 'financial_account_civireport_debit',
 108             'default' => TRUE,
 109           ),
 110           'credit_name' => array(
 111             'title' => ts('Financial Account Name - Credit'),
 112             'name'  => 'name',
 113             'alias' => 'financial_account_civireport_credit',
 114             'default' => TRUE,
 115           ),
 116         ),
 117         'filters' => array(
 118           'debit_accounting_code' => array(
 119             'title' => ts('Financial Account Code - Debit'),
 120             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 121             'options' => CRM_Contribute_PseudoConstant::financialAccount(NULL, NULL, 'accounting_code', 'accounting_code'),
 122             'name'  => 'accounting_code',
 123             'alias' => 'financial_account_civireport_debit',
 124           ),
 125           'credit_accounting_code' => array(
 126             'title' => ts('Financial Account Code - Credit'),
 127             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 128             'options' => CRM_Contribute_PseudoConstant::financialAccount(NULL, NULL, 'accounting_code', 'accounting_code'),
 129           ),
 130           'debit_name' => array(
 131             'title' => ts('Financial Account Name - Debit'),
 132             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 133             'options' => CRM_Contribute_PseudoConstant::financialAccount(),
 134             'name'  => 'id',
 135             'alias' => 'financial_account_civireport_debit',
 136           ),
 137           'credit_name' => array(
 138             'title' => ts('Financial Account Name - Credit'),
 139             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 140             'options' => CRM_Contribute_PseudoConstant::financialAccount(),
 141           ),
 142         ),
 143       ),
 144       'civicrm_line_item' => array(
 145         'dao' => 'CRM_Price_DAO_LineItem',
 146         'fields' => array(
 147           'financial_type_id' => array('title' => ts('Financial Type'),
 148             'default' => TRUE,
 149           ),
 150         ),
 151         'filters' => array(
 152           'financial_type_id' => array(
 153             'title' => ts('Financial Type'),
 154             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 155             'options' => CRM_Contribute_PseudoConstant::financialType(),
 156           ),
 157         ),
 158       ),
 159       'civicrm_contribution' =>
 160       array(
 161         'dao' => 'CRM_Contribute_DAO_Contribution',
 162         'fields' =>
 163         array(
 164           'receive_date' => array(
 165             'default' => TRUE
 166           ),
 167           'invoice_id' => array(
 168             'title' => ts('Invoice ID'),
 169             'default' => TRUE,
 170           ),
 171           'contribution_status_id' => array('title' => ts('Contribution Status'),
 172             'default' => TRUE,
 173           ),
 174           'id' => array('title' => ts('Contribution #'),
 175             'default' => TRUE,
 176           ),
 177         ),
 178         'filters' =>
 179         array(
 180           'receive_date' =>
 181           array('operatorType' => CRM_Report_Form::OP_DATE),
 182           'contribution_status_id' =>
 183           array('title' => ts('Contribution Status'),
 184             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 185             'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 186             'default' => array(1),
 187           ),
 188         ),
 189         'grouping' => 'contri-fields',
 190       ),
 191       'civicrm_financial_trxn' => array(
 192         'dao' => 'CRM_Financial_DAO_FinancialTrxn',
 193         'fields' => array(
 194           'check_number' => array(
 195             'title' => ts('Cheque #'),
 196             'default' => TRUE,
 197           ),
 198           'payment_instrument_id' => array('title' => ts('Payment Instrument'),
 199             'default' => TRUE,
 200           ),
 201           'currency' => array(
 202              'required' => TRUE,
 203              'no_display' => TRUE,
 204           ),
 205           'trxn_date' => array(
 206             'title' => ts('Transaction Date'),
 207             'default' => TRUE,
 208             'type' => CRM_Utils_Type::T_DATE,
 209           ),
 210           'trxn_id' => array(
 211             'title' => ts('Trans #'),
 212             'default' => TRUE,
 213           ),
 214         ),
 215         'filters' =>
 216         array(
 217           'payment_instrument_id' => array(
 218             'title' => ts('Payment Instrument'),
 219             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 220             'options' => CRM_Contribute_PseudoConstant::paymentInstrument(),
 221           ),
 222           'currency' => array(
 223              'title' => 'Currency',
 224              'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 225              'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 226              'default' => NULL,
 227              'type' => CRM_Utils_Type::T_STRING,
 228           ),
 229           'trxn_date' => array(
 230             'title' => ts('Transaction Date'),
 231             'operatorType' => CRM_Report_Form::OP_DATE,
 232             'type' => CRM_Utils_Type::T_DATE,
 233           ),
 234         ),
 235       ),
 236       'civicrm_entity_financial_trxn' => array(
 237         'dao' => 'CRM_Financial_DAO_EntityFinancialTrxn',
 238         'fields' => array(
 239           'amount' => array(
 240             'title' => ts('Amount'),
 241             'default' => TRUE,
 242             'type' => CRM_Utils_Type::T_STRING,
 243           ),
 244         ),
 245         'filters' =>
 246         array(
 247           'amount' =>
 248           array('title' => ts('Amount')),
 249         ),
 250       ),
 251     );
 252     parent::__construct();
 253   }
 254
 255   function preProcess() {
 256     parent::preProcess();
 257   }
 258
 259   function select() {
 260     $select = array();
 261
 262     $this->_columnHeaders = array();
 263     foreach ($this->_columns as $tableName => $table) {
 264       if (array_key_exists('fields', $table)) {
 265         foreach ($table['fields'] as $fieldName => $field) {
 266           if (CRM_Utils_Array::value('required', $field) ||
 267             CRM_Utils_Array::value($fieldName, $this->_params['fields'])
 268           ) {
 269             switch ($fieldName) {
 270             case 'credit_accounting_code' :
 271               $select[] = " CASE
 272                             WHEN {$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id IS NOT NULL
 273                             THEN  {$this->_aliases['civicrm_financial_account']}_credit_1.accounting_code
 274                             ELSE  {$this->_aliases['civicrm_financial_account']}_credit_2.accounting_code
 275                             END AS civicrm_financial_account_credit_accounting_code ";
 276               break;
 277             case 'amount' :
 278               $select[] = " CASE
 279                             WHEN  {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id IS NOT NULL
 280                             THEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.amount
 281                             ELSE {$this->_aliases['civicrm_entity_financial_trxn']}.amount
 282                             END AS civicrm_entity_financial_trxn_amount ";
 283               break;
 284             case 'credit_name' :
 285               $select[] = " CASE
 286                             WHEN {$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id IS NOT NULL
 287                             THEN  {$this->_aliases['civicrm_financial_account']}_credit_1.name
 288                             ELSE  {$this->_aliases['civicrm_financial_account']}_credit_2.name
 289                             END AS civicrm_financial_account_credit_name ";
 290               break;
 291             default :
 292               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 293               break;
 294             }
 295             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 296             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 297           }
 298         }
 299       }
 300     }
 301
 302     $this->_select = 'SELECT ' . implode(', ', $select) . ' ';
 303   }
 304
 305   function from() {
 306     $this->_from = NULL;
 307
 308     $this->_from = "FROM  civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}
 309               INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
 310                     ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND
 311                          {$this->_aliases['civicrm_contribution']}.is_test = 0
 312               LEFT JOIN civicrm_membership_payment payment
 313                     ON ( {$this->_aliases['civicrm_contribution']}.id = payment.contribution_id )
 314               LEFT JOIN civicrm_membership {$this->_aliases['civicrm_membership']}
 315                     ON payment.membership_id = {$this->_aliases['civicrm_membership']}.id
 316               LEFT JOIN civicrm_entity_financial_trxn {$this->_aliases['civicrm_entity_financial_trxn']}
 317                     ON ({$this->_aliases['civicrm_contribution']}.id = {$this->_aliases['civicrm_entity_financial_trxn']}.entity_id AND
 318                         {$this->_aliases['civicrm_entity_financial_trxn']}.entity_table = 'civicrm_contribution')
 319               LEFT JOIN civicrm_financial_trxn {$this->_aliases['civicrm_financial_trxn']}
 320                     ON {$this->_aliases['civicrm_financial_trxn']}.id = {$this->_aliases['civicrm_entity_financial_trxn']}.financial_trxn_id
 321               LEFT JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account']}_debit
 322                     ON {$this->_aliases['civicrm_financial_trxn']}.to_financial_account_id = {$this->_aliases['civicrm_financial_account']}_debit.id
 323               LEFT JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account']}_credit_1
 324                     ON {$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id = {$this->_aliases['civicrm_financial_account']}_credit_1.id
 325               LEFT JOIN civicrm_entity_financial_trxn {$this->_aliases['civicrm_entity_financial_trxn']}_item
 326                     ON ({$this->_aliases['civicrm_financial_trxn']}.id = {$this->_aliases['civicrm_entity_financial_trxn']}_item.financial_trxn_id AND
 327                         {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_table = 'civicrm_financial_item')
 328               LEFT JOIN civicrm_financial_item fitem
 329                     ON fitem.id = {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id
 330               LEFT JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account']}_credit_2
 331                     ON fitem.financial_account_id = {$this->_aliases['civicrm_financial_account']}_credit_2.id
 332               LEFT JOIN civicrm_line_item {$this->_aliases['civicrm_line_item']}
 333                     ON  fitem.entity_id = {$this->_aliases['civicrm_line_item']}.id AND fitem.entity_table = 'civicrm_line_item' ";
 334   }
 335
 336   function orderBy() {
 337           $this->_orderBy = " ORDER BY {$this->_aliases['civicrm_contact']}.sort_name, {$this->_aliases['civicrm_contribution']}.id, {$this->_aliases['civicrm_entity_financial_trxn']}.id ";
 338   }
 339
 340   function where() {
 341     foreach ($this->_columns as $tableName => $table) {
 342       if (array_key_exists('filters', $table)) {
 343         foreach ($table['filters'] as $fieldName => $field) {
 344           $clause = NULL;
 345           if ($fieldName == 'credit_accounting_code') {
 346             $field['dbAlias'] = "CASE
 347               WHEN financial_trxn_civireport.from_financial_account_id IS NOT NULL
 348               THEN  financial_account_civireport_credit_1.accounting_code
 349               ELSE  financial_account_civireport_credit_2.accounting_code
 350               END";
 351           }
 352           else if ($fieldName == 'credit_name') {
 353             $field['dbAlias'] = "CASE
 354               WHEN financial_trxn_civireport.from_financial_account_id IS NOT NULL
 355               THEN  financial_account_civireport_credit_1.id
 356               ELSE  financial_account_civireport_credit_2.id
 357               END";
 358           }
 359           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 360             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 361             $from     = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 362             $to       = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 363
 364             $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 365           }
 366           else {
 367             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 368             if ($op) {
 369               $clause = $this->whereClause($field,
 370                 $op,
 371                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 372                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 373                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 374               );
 375             }
 376           }
 377           if (!empty($clause)) {
 378             $clauses[] = $clause;
 379           }
 380         }
 381       }
 382     }
 383     if (empty($clauses)) {
 384       $this->_where = 'WHERE ( 1 )';
 385     }
 386     else {
 387       $this->_where = 'WHERE ' . implode(' AND ', $clauses);
 388     }
 389   }
 390
 391   function postProcess() {
 392     // get the acl clauses built before we assemble the query
 393     $this->buildACLClause($this->_aliases['civicrm_contact']);
 394     parent::postProcess();
 395   }
 396
 397   function statistics(&$rows) {
 398     $statistics = parent::statistics($rows);
 399
 400     $select = " SELECT COUNT({$this->_aliases['civicrm_financial_trxn']}.id ) as count,
 401                 {$this->_aliases['civicrm_contribution']}.currency,
 402                 SUM(CASE
 403                   WHEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id IS NOT NULL
 404                   THEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.amount
 405                   ELSE {$this->_aliases['civicrm_entity_financial_trxn']}.amount
 406                 END) as amount
 407 ";
 408
 409     $sql = "{$select} {$this->_from} {$this->_where}
 410             GROUP BY {$this->_aliases['civicrm_contribution']}.currency
 411 ";
 412
 413     $dao = CRM_Core_DAO::executeQuery($sql);
 414     while ($dao->fetch()) {
 415       $amount[] = CRM_Utils_Money::format($dao->amount, $dao->currency);
 416       $avg[] =  CRM_Utils_Money::format(round(($dao->amount / $dao->count), 2), $dao->currency);
 417     }
 418
 419     $statistics['counts']['amount'] = array(
 420        'value' => implode(', ', $amount),
 421        'title' => 'Total Amount',
 422        'type' => CRM_Utils_Type::T_STRING,
 423     );
 424     $statistics['counts']['avg'] = array(
 425       'value' => implode(', ', $avg),
 426       'title' => 'Average',
 427       'type' => CRM_Utils_Type::T_STRING,
 428     );
 429     return $statistics;
 430   }
 431
 432   function alterDisplay(&$rows) {
 433     $contributionTypes = CRM_Contribute_PseudoConstant::financialType();
 434     $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument();
 435     $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus();
 436     foreach ($rows as $rowNum => $row) {
 437       // convert display name to links
 438       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 439         CRM_Utils_Array::value('civicrm_contact_sort_name', $rows[$rowNum]) &&
 440         array_key_exists('civicrm_contact_id', $row)
 441       ) {
 442         $url = CRM_Utils_System::url('civicrm/contact/view',
 443           'reset=1&cid=' . $row['civicrm_contact_id'],
 444           $this->_absoluteUrl
 445         );
 446         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 447         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts('View Contact Summary for this Contact.');
 448       }
 449
 450       // handle contribution status id
 451       if ($value = CRM_Utils_Array::value('civicrm_contribution_contribution_status_id', $row)) {
 452         $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value];
 453       }
 454
 455       // handle payment instrument id
 456       if ($value = CRM_Utils_Array::value('civicrm_financial_trxn_payment_instrument_id', $row)) {
 457         $rows[$rowNum]['civicrm_financial_trxn_payment_instrument_id'] = $paymentInstruments[$value];
 458       }
 459
 460       // handle financial type id
 461       if ($value = CRM_Utils_Array::value('civicrm_line_item_financial_type_id', $row)) {
 462         $rows[$rowNum]['civicrm_line_item_financial_type_id'] = $contributionTypes[$value];
 463       }
 464       if ($value = CRM_Utils_Array::value('civicrm_entity_financial_trxn_amount', $row)) {
 465         $rows[$rowNum]['civicrm_entity_financial_trxn_amount'] = CRM_Utils_Money::format($rows[$rowNum]['civicrm_entity_financial_trxn_amount'],$rows[$rowNum]['civicrm_financial_trxn_currency']);
 466       }
 467     }
 468   }
 469 }
 470