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