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           'trxn_date' => array(
 203             'title' => ts('Transaction Date'),
 204             'default' => TRUE,
 205             'type' => CRM_Utils_Type::T_DATE,
 206           ),
 207           'trxn_id' => array(
 208             'title' => ts('Trans #'),
 209             'default' => TRUE,
 210           ),
 211         ),
 212         'filters' =>
 213         array(
 214           'payment_instrument_id' => array(
 215             'title' => ts('Payment Instrument'),
 216             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 217             'options' => CRM_Contribute_PseudoConstant::paymentInstrument(),
 218           ),
 219           'trxn_date' => array(
 220             'title' => ts('Transaction Date'),
 221             'operatorType' => CRM_Report_Form::OP_DATE,
 222             'type' => CRM_Utils_Type::T_DATE,
 223           ),
 224         ),
 225       ),
 226       'civicrm_entity_financial_trxn' => array(
 227         'dao' => 'CRM_Financial_DAO_EntityFinancialTrxn',
 228         'fields' => array(
 229           'amount' => array(
 230             'title' => ts('Amount'),
 231             'default' => TRUE,
 232           ),
 233         ),
 234         'filters' =>
 235         array(
 236           'amount' =>
 237           array('title' => ts('Amount')),
 238         ),
 239       ),
 240     );
 241     parent::__construct();
 242   }
 243
 244   function preProcess() {
 245     parent::preProcess();
 246   }
 247
 248   function select() {
 249     $select = array();
 250
 251     $this->_columnHeaders = array();
 252     foreach ($this->_columns as $tableName => $table) {
 253       if (array_key_exists('fields', $table)) {
 254         foreach ($table['fields'] as $fieldName => $field) {
 255           if (CRM_Utils_Array::value('required', $field) ||
 256             CRM_Utils_Array::value($fieldName, $this->_params['fields'])
 257           ) {
 258             switch ($fieldName) {
 259             case 'credit_accounting_code' :
 260               $select[] = " CASE
 261                             WHEN {$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id IS NOT NULL
 262                             THEN  {$this->_aliases['civicrm_financial_account']}_credit_1.accounting_code
 263                             ELSE  {$this->_aliases['civicrm_financial_account']}_credit_2.accounting_code
 264                             END AS civicrm_financial_account_credit_accounting_code ";
 265               break;
 266             case 'amount' :
 267               $select[] = " CASE
 268                             WHEN  {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id IS NOT NULL
 269                             THEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.amount
 270                             ELSE {$this->_aliases['civicrm_entity_financial_trxn']}.amount
 271                             END AS civicrm_entity_financial_trxn_amount ";
 272               break;
 273             case 'credit_name' :
 274               $select[] = " CASE
 275                             WHEN {$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id IS NOT NULL
 276                             THEN  {$this->_aliases['civicrm_financial_account']}_credit_1.name
 277                             ELSE  {$this->_aliases['civicrm_financial_account']}_credit_2.name
 278                             END AS civicrm_financial_account_credit_name ";
 279               break;
 280             default :
 281               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 282               break;
 283             }
 284             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 285             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 286           }
 287         }
 288       }
 289     }
 290
 291     $this->_select = "SELECT " . implode(', ', $select) . " ";
 292   }
 293
 294   function from() {
 295     $this->_from = NULL;
 296
 297     $this->_from = "FROM  civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}
 298               INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
 299                     ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND
 300                          {$this->_aliases['civicrm_contribution']}.is_test = 0
 301               LEFT JOIN civicrm_membership_payment payment
 302                     ON ( {$this->_aliases['civicrm_contribution']}.id = payment.contribution_id )
 303               LEFT JOIN civicrm_membership {$this->_aliases['civicrm_membership']}
 304                     ON payment.membership_id = {$this->_aliases['civicrm_membership']}.id
 305               LEFT JOIN civicrm_entity_financial_trxn {$this->_aliases['civicrm_entity_financial_trxn']}
 306                     ON ({$this->_aliases['civicrm_contribution']}.id = {$this->_aliases['civicrm_entity_financial_trxn']}.entity_id AND
 307                         {$this->_aliases['civicrm_entity_financial_trxn']}.entity_table = 'civicrm_contribution')
 308               LEFT JOIN civicrm_financial_trxn {$this->_aliases['civicrm_financial_trxn']}
 309                     ON {$this->_aliases['civicrm_financial_trxn']}.id = {$this->_aliases['civicrm_entity_financial_trxn']}.financial_trxn_id
 310               LEFT JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account']}_debit
 311                     ON {$this->_aliases['civicrm_financial_trxn']}.to_financial_account_id = {$this->_aliases['civicrm_financial_account']}_debit.id
 312               LEFT JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account']}_credit_1
 313                     ON {$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id = {$this->_aliases['civicrm_financial_account']}_credit_1.id
 314               LEFT JOIN civicrm_entity_financial_trxn {$this->_aliases['civicrm_entity_financial_trxn']}_item
 315                     ON ({$this->_aliases['civicrm_financial_trxn']}.id = {$this->_aliases['civicrm_entity_financial_trxn']}_item.financial_trxn_id AND
 316                         {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_table = 'civicrm_financial_item')
 317               LEFT JOIN civicrm_financial_item fitem
 318                     ON fitem.id = {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id
 319               LEFT JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account']}_credit_2
 320                     ON fitem.financial_account_id = {$this->_aliases['civicrm_financial_account']}_credit_2.id
 321               LEFT JOIN civicrm_line_item {$this->_aliases['civicrm_line_item']}
 322                     ON  fitem.entity_id = {$this->_aliases['civicrm_line_item']}.id AND fitem.entity_table = 'civicrm_line_item' ";
 323   }
 324
 325   function orderBy() {
 326           $this->_orderBy = " ORDER BY {$this->_aliases['civicrm_contact']}.sort_name, {$this->_aliases['civicrm_contribution']}.id, {$this->_aliases['civicrm_entity_financial_trxn']}.id ";
 327   }
 328
 329   function where() {
 330     foreach ($this->_columns as $tableName => $table) {
 331       if (array_key_exists('filters', $table)) {
 332         foreach ($table['filters'] as $fieldName => $field) {
 333           $clause = NULL;
 334           if ($fieldName == 'credit_accounting_code') {
 335             $field['dbAlias'] = "CASE
 336               WHEN financial_trxn_civireport.from_financial_account_id IS NOT NULL
 337               THEN  financial_account_civireport_credit_1.accounting_code
 338               ELSE  financial_account_civireport_credit_2.accounting_code
 339               END";
 340           }
 341           else if ($fieldName == 'credit_name') {
 342             $field['dbAlias'] = "CASE
 343               WHEN financial_trxn_civireport.from_financial_account_id IS NOT NULL
 344               THEN  financial_account_civireport_credit_1.id
 345               ELSE  financial_account_civireport_credit_2.id
 346               END";
 347           }
 348           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 349             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 350             $from     = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 351             $to       = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 352
 353             $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 354           }
 355           else {
 356             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 357             if ($op) {
 358               $clause = $this->whereClause($field,
 359                 $op,
 360                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 361                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 362                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 363               );
 364             }
 365           }
 366           if (!empty($clause)) {
 367             $clauses[] = $clause;
 368           }
 369         }
 370       }
 371     }
 372     if (empty($clauses)) {
 373       $this->_where = "WHERE ( 1 )";
 374     }
 375     else {
 376       $this->_where = "WHERE " . implode(' AND ', $clauses);
 377     }
 378   }
 379
 380   function postProcess() {
 381     // get the acl clauses built before we assemble the query
 382     $this->buildACLClause($this->_aliases['civicrm_contact']);
 383     parent::postProcess();
 384   }
 385
 386   function statistics(&$rows) {
 387     $statistics = parent::statistics($rows);
 388
 389     $select = " SELECT COUNT({$this->_aliases['civicrm_financial_trxn']}.id ) as count,
 390                 SUM(CASE
 391                   WHEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id IS NOT NULL
 392                   THEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.amount
 393                   ELSE {$this->_aliases['civicrm_entity_financial_trxn']}.amount
 394                 END) as amount ";
 395
 396     $sql = "{$select} {$this->_from} {$this->_where}";
 397     $dao = CRM_Core_DAO::executeQuery($sql);
 398     $dao->fetch();
 399     if ($dao->count) {
 400       $statistics['counts']['amount'] = array(
 401         'value' => $dao->amount,
 402         'title' => 'Total Amount',
 403         'type' => CRM_Utils_Type::T_MONEY,
 404       );
 405       $statistics['counts']['avg'] = array(
 406         'value' => round(($dao->amount / $dao->count), 2),
 407         'title' => 'Average',
 408         'type' => CRM_Utils_Type::T_MONEY,
 409       );
 410     }
 411
 412     return $statistics;
 413   }
 414
 415   function alterDisplay(&$rows) {
 416     $contributionTypes = CRM_Contribute_PseudoConstant::financialType();
 417     $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument();
 418     $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus();
 419     foreach ($rows as $rowNum => $row) {
 420       // convert display name to links
 421       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 422         CRM_Utils_Array::value('civicrm_contact_sort_name', $rows[$rowNum]) &&
 423         array_key_exists('civicrm_contact_id', $row)
 424       ) {
 425         $url = CRM_Utils_System::url("civicrm/contact/view",
 426           'reset=1&cid=' . $row['civicrm_contact_id'],
 427           $this->_absoluteUrl
 428         );
 429         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 430         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
 431       }
 432
 433       // handle contribution status id
 434       if ($value = CRM_Utils_Array::value('civicrm_contribution_contribution_status_id', $row)) {
 435         $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value];
 436       }
 437
 438       // handle payment instrument id
 439       if ($value = CRM_Utils_Array::value('civicrm_financial_trxn_payment_instrument_id', $row)) {
 440         $rows[$rowNum]['civicrm_financial_trxn_payment_instrument_id'] = $paymentInstruments[$value];
 441       }
 442
 443       // handle financial type id
 444       if ($value = CRM_Utils_Array::value('civicrm_line_item_financial_type_id', $row)) {
 445         $rows[$rowNum]['civicrm_line_item_financial_type_id'] = $contributionTypes[$value];
 446       }
 447     }
 448   }
 449 }
 450