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