CRM/Report/Form/Member/ContributionDetail.php

   1 <?php
   2
   3 /*
   4   +--------------------------------------------------------------------+
   5   | CiviCRM version 4.3                                                |
   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_Member_ContributionDetail extends CRM_Report_Form {
  37   protected $_addressField = FALSE;
  38
  39   protected $_emailField = FALSE;
  40   protected $_emailFieldHonor = FALSE;
  41
  42   protected $_nameFieldHonor = FALSE;
  43
  44   protected $_summary = NULL;
  45
  46   protected $_customGroupExtends = array(
  47     'Contribution', 'Membership');
  48
  49   function __construct() {
  50     $config = CRM_Core_Config::singleton();
  51     $campaignEnabled = in_array('CiviCampaign', $config->enableComponents);
  52     if ($campaignEnabled) {
  53       $getCampaigns = CRM_Campaign_BAO_Campaign::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
  54       $this->activeCampaigns = $getCampaigns['campaigns'];
  55       asort($this->activeCampaigns);
  56     }
  57     $this->_columns = array(
  58       'civicrm_contact' =>
  59       array(
  60         'dao' => 'CRM_Contact_DAO_Contact',
  61         'fields' =>
  62         array(
  63           'sort_name' =>
  64           array('title' => ts('Donor Name'),
  65             'required' => TRUE,
  66             'no_repeat' => TRUE,
  67           ),
  68           'first_name' =>
  69           array('title' => ts('First Name'),
  70             'no_repeat' => TRUE,
  71           ),
  72           'last_name' =>
  73           array('title' => ts('Last Name'),
  74             'no_repeat' => TRUE,
  75           ),
  76           'contact_type' =>
  77           array('title' => ts('Contact Type'),
  78             'no_repeat' => TRUE,
  79           ),
  80           'do_not_email' =>
  81           array('title' => ts('Do Not Email'),
  82             'no_repeat' => TRUE,
  83           ),
  84           'is_opt_out' =>
  85           array('title' => ts('No Bulk Email(Is Opt Out)'),
  86             'no_repeat' => TRUE,
  87           ),
  88           'id' =>
  89           array(
  90             'no_display' => TRUE,
  91             'required' => TRUE,
  92             'csv_display' => TRUE,
  93             'title' => ts('Contact ID'),
  94           ),
  95         ),
  96         'filters' =>
  97         array(
  98           'sort_name' =>
  99           array('title' => ts('Donor Name'),
 100             'operator' => 'like',
 101           ),
 102           'id' =>
 103           array('title' => ts('Contact ID'),
 104             'no_display' => TRUE,
 105           ),
 106         ),
 107         'grouping' => 'contact-fields',
 108       ),
 109       'civicrm_email' =>
 110       array(
 111         'dao' => 'CRM_Core_DAO_Email',
 112         'fields' =>
 113         array(
 114           'email' =>
 115           array('title' => ts('Donor Email'),
 116             'default' => TRUE,
 117             'no_repeat' => TRUE,
 118           ),
 119         ),
 120         'grouping' => 'contact-fields',
 121       ),
 122       'civicrm_phone' =>
 123       array(
 124         'dao' => 'CRM_Core_DAO_Phone',
 125         'fields' =>
 126         array(
 127           'phone' =>
 128           array('title' => ts('Donor Phone'),
 129             'default' => TRUE,
 130             'no_repeat' => TRUE,
 131           ),
 132         ),
 133         'grouping' => 'contact-fields',
 134       ),
 135       'civicrm_contact_honor' =>
 136       array(
 137         'dao' => 'CRM_Contact_DAO_Contact',
 138         'fields' =>
 139         array(
 140           'sort_name_honor' =>
 141           array('title' => ts('Honoree Name'),
 142             'name' => 'sort_name',
 143             'alias' => 'contacthonor',
 144             'default' => FALSE,
 145             'no_repeat' => TRUE,
 146           ),
 147           'id_honor' =>
 148           array(
 149             'no_display' => TRUE,
 150             'title' => ts('Honoree ID'),
 151             'name' => 'id',
 152             'alias' => 'contacthonor',
 153             'required' => TRUE,
 154           ),
 155         ),
 156       ),
 157       'civicrm_email_honor' =>
 158       array(
 159         'dao' => 'CRM_Core_DAO_Email',
 160         'fields' =>
 161         array(
 162           'email_honor' =>
 163           array('title' => ts('Honoree Email'),
 164             'name' => 'email',
 165             'alias' => 'emailhonor',
 166             'default' => FALSE,
 167             'no_repeat' => TRUE,
 168           ),
 169         ),
 170         'grouping' => 'contact-fields',
 171       ),
 172       'first_donation' => array(
 173         'dao' => 'CRM_Contribute_DAO_Contribution',
 174         'fields' =>
 175         array(
 176           'first_donation_date' => array(
 177             'title' => ts('First Contribution Date'),
 178             'base_field' => 'receive_date',
 179             'no_repeat' => TRUE,
 180           ),
 181           'first_donation_amount' => array(
 182             'title' => ts('First Contribution Amount'),
 183             'base_field' => 'total_amount',
 184             'no_repeat' => TRUE,
 185           ),
 186         ),
 187       ),
 188       'civicrm_contribution' =>
 189       array(
 190         'dao' => 'CRM_Contribute_DAO_Contribution',
 191         'fields' =>
 192         array(
 193           'contribution_id' => array(
 194             'name' => 'id',
 195             'no_display' => TRUE,
 196             'required' => TRUE,
 197             'csv_display' => TRUE,
 198             'title' => ts('Contribution ID'),
 199           ),
 200           'financial_type_id' => array('title' => ts('Financial Type'),
 201             'default' => TRUE,
 202           ),
 203           'contribution_recur_id' => array('title' => ts('Recurring Contribution Id'),
 204             'name' => 'contribution_recur_id',
 205             'required' => TRUE,
 206             'no_display' => TRUE,
 207             'csv_display' => TRUE,
 208           ),
 209           'contribution_status_id' => array('title' => ts('Contribution Status'),
 210           ),
 211           'payment_instrument_id' => array('title' => ts('Payment Type'),
 212           ),
 213           'contribution_source' => array(
 214             'name' => 'source',
 215             'title' => ts('Contribution Source'),
 216           ),
 217           'currency' => array(
 218             'required' => TRUE,
 219             'no_display' => TRUE,
 220           ),
 221           'trxn_id' => NULL,
 222           'receive_date' => array('default' => TRUE),
 223           'receipt_date' => NULL,
 224           'honor_type_id' => array('title' => ts('Honor Type'),
 225                            'default' => FALSE,
 226           ),
 227           'fee_amount' => NULL,
 228           'net_amount' => NULL,
 229           'total_amount' => array('title' => ts('Amount'),
 230                           'required' => TRUE,
 231           ),
 232         ),
 233         'filters' =>
 234         array(
 235           'receive_date' =>
 236           array('operatorType' => CRM_Report_Form::OP_DATE),
 237           'financial_type_id' =>
 238           array('title' => ts('Financial Type'),
 239             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 240             'options' => CRM_Contribute_PseudoConstant::financialType(),
 241           ),
 242           'currency' =>
 243           array('title' => 'Currency',
 244             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 245             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 246             'default' => NULL,
 247             'type' => CRM_Utils_Type::T_STRING,
 248           ),
 249           'payment_instrument_id' =>
 250           array('title' => ts('Payment Type'),
 251             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 252             'options' => CRM_Contribute_PseudoConstant::paymentInstrument(),
 253           ),
 254           'contribution_status_id' =>
 255           array('title' => ts('Contribution Status'),
 256             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 257             'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 258             'default' => array(1),
 259           ),
 260           'total_amount' =>
 261           array('title' => ts('Contribution Amount')),
 262         ),
 263         'grouping' => 'contri-fields',
 264       ),
 265       'civicrm_product' =>
 266       array(
 267         'dao' => 'CRM_Contribute_DAO_Product',
 268         'fields' =>
 269         array(
 270           'product_name' => array(
 271             'name' => 'name',
 272             'title' => ts('Premium'),
 273           ),
 274         ),
 275       ),
 276       'civicrm_contribution_product' =>
 277       array(
 278         'dao' => 'CRM_Contribute_DAO_ContributionProduct',
 279         'fields' =>
 280         array(
 281           'product_id' => array(
 282             'no_display' => TRUE,
 283           ),
 284           'product_option' => array(
 285             'title' => ts('Premium Option'),
 286           ),
 287           'contribution_id' => array(
 288             'no_display' => TRUE,
 289           ),
 290         ),
 291       ),
 292       'civicrm_group' =>
 293       array(
 294         'dao' => 'CRM_Contact_DAO_GroupContact',
 295         'alias' => 'cgroup',
 296         'filters' =>
 297         array(
 298           'gid' =>
 299           array(
 300             'name' => 'group_id',
 301             'title' => ts('Group'),
 302             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 303             'group' => TRUE,
 304             'options' => CRM_Core_PseudoConstant::group(),
 305           ),
 306         ),
 307       ),
 308       'civicrm_contribution_ordinality' =>
 309       array(
 310         'dao' => 'CRM_Contribute_DAO_Contribution',
 311         'alias' => 'cordinality',
 312         'filters' =>
 313         array(
 314           'ordinality' =>
 315           array('title' => ts('Contribution Ordinality'),
 316             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 317             'options' => array(
 318               0 => 'First by Contributor',
 319               1 => 'Second or Later by Contributor',
 320             ),
 321           ),
 322         ),
 323       ),
 324       'civicrm_membership' =>
 325       array(
 326         'dao' => 'CRM_Member_DAO_Membership',
 327         'fields' =>
 328         array(
 329           'membership_type_id' => array('title' => ts('Membership Type'),
 330                                 'required' => TRUE,
 331                                 'no_repeat' => TRUE,
 332           ),
 333           'membership_start_date' => array('title' => ts('Start Date'),
 334                                    'default' => TRUE,
 335           ),
 336           'membership_end_date' => array('title' => ts('End Date'),
 337                                  'default' => TRUE,
 338           ),
 339           'join_date' => array('title' => ts('Join Date'),
 340                        'default' => TRUE,
 341           ),
 342           'source' => array('title' => ts('Membership Source')),
 343         ),
 344         'filters' => array(
 345           'join_date' =>
 346           array('operatorType' => CRM_Report_Form::OP_DATE),
 347           'membership_start_date' =>
 348           array('operatorType' => CRM_Report_Form::OP_DATE),
 349           'membership_end_date' =>
 350           array('operatorType' => CRM_Report_Form::OP_DATE),
 351           'owner_membership_id' =>
 352           array('title' => ts('Membership Owner ID'),
 353             'operatorType' => CRM_Report_Form::OP_INT,
 354           ),
 355           'tid' =>
 356           array(
 357             'name' => 'membership_type_id',
 358             'title' => ts('Membership Types'),
 359             'type' => CRM_Utils_Type::T_INT,
 360             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 361             'options' => CRM_Member_PseudoConstant::membershipType(),
 362           ),
 363         ),
 364         'grouping' => 'member-fields',
 365       ),
 366       'civicrm_membership_status' =>
 367       array(
 368         'dao' => 'CRM_Member_DAO_MembershipStatus',
 369         'alias' => 'mem_status',
 370         'fields' =>
 371         array(
 372           'membership_status_name' =>
 373           array(
 374             'name' => 'name',
 375             'title' => ts('Membership Status'),
 376             'default' => TRUE,
 377           ),
 378         ),
 379         'filters' => array(
 380           'sid' =>
 381           array(
 382             'name' => 'id',
 383             'title' => ts('Membership Status'),
 384             'type' => CRM_Utils_Type::T_INT,
 385             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 386             'options' => CRM_Member_PseudoConstant::membershipStatus(NULL, NULL, 'label'),
 387           ),
 388         ),
 389         'grouping' => 'member-fields',
 390       ),
 391       'civicrm_note' =>
 392       array(
 393         'dao' => 'CRM_Core_DAO_Note',
 394         'fields' =>
 395         array(
 396           'contribution_note' =>
 397           array(
 398             'name' => 'note',
 399             'title' => ts('Contribution Note'),
 400           ),
 401         ),
 402         'filters' =>
 403         array(
 404           'note' =>
 405           array(
 406             'name'  => 'note',
 407             'title' => ts('Contribution Note'),
 408             'operator' => 'like',
 409             'type'  => CRM_Utils_Type::T_STRING,
 410           ),
 411         ),
 412       ),
 413     ) + $this->addAddressFields(FALSE);
 414
 415     $this->_tagFilter = TRUE;
 416
 417     // Don't show Batch display column and filter unless batches are being used
 418     $this->_closedBatches = CRM_Batch_BAO_Batch::getBatches();
 419     if (!empty($this->_closedBatches)) {
 420       $this->_columns['civicrm_batch']['dao'] = 'CRM_Batch_DAO_Batch';
 421       $this->_columns['civicrm_batch']['fields']['batch_id'] = array(
 422         'name' => 'id',
 423         'title' => ts('Batch Name'),
 424       );
 425       $this->_columns['civicrm_batch']['filters']['bid'] = array(
 426         'name' => 'id',
 427         'title' => ts('Batch Name'),
 428         'type' => CRM_Utils_Type::T_INT,
 429         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 430         'options' => $this->_closedBatches,
 431       );
 432       $this->_columns['civicrm_entity_batch']['dao'] = 'CRM_Batch_DAO_EntityBatch';
 433       $this->_columns['civicrm_entity_batch']['fields']['entity_batch_id'] = array(
 434         'name' => 'batch_id',
 435         'default' => TRUE,
 436         'no_display' => TRUE,
 437       );
 438     }
 439
 440     if ($campaignEnabled && !empty($this->activeCampaigns)) {
 441       $this->_columns['civicrm_contribution']['fields']['campaign_id'] = array(
 442         'title' => ts('Campaign'),
 443         'default' => 'false',
 444       );
 445       $this->_columns['civicrm_contribution']['filters']['campaign_id'] = array('title' => ts('Campaign'),
 446                                                                           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 447                                                                           'options' => $this->activeCampaigns,
 448       );
 449       $this->_columns['civicrm_contribution']['order_bys']['campaign_id'] = array('title' => ts('Campaign'));
 450     }
 451
 452     $this->_currencyColumn = 'civicrm_contribution_currency';
 453     parent::__construct();
 454   }
 455
 456   function preProcess() {
 457     parent::preProcess();
 458   }
 459
 460   function select() {
 461     $select = array();
 462
 463     $this->_columnHeaders = array();
 464     foreach ($this->_columns as $tableName => $table) {
 465       if (array_key_exists('fields', $table)) {
 466         foreach ($table['fields'] as $fieldName => $field) {
 467           if (CRM_Utils_Array::value('required', $field) ||
 468             CRM_Utils_Array::value($fieldName, $this->_params['fields'])
 469           ) {
 470             if ($tableName == 'civicrm_address') {
 471               $this->_addressField = TRUE;
 472             }
 473             if ($tableName == 'civicrm_email') {
 474               $this->_emailField = TRUE;
 475             }
 476             elseif ($tableName == 'civicrm_email_honor') {
 477               $this->_emailFieldHonor = TRUE;
 478             }
 479
 480             if ($tableName == 'civicrm_contact_honor') {
 481               $this->_nameFieldHonor = TRUE;
 482             }
 483
 484             // only include statistics columns if set
 485             if (CRM_Utils_Array::value('statistics', $field)) {
 486               foreach ($field['statistics'] as $stat => $label) {
 487                 switch (strtolower($stat)) {
 488                   case 'sum':
 489                     $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 490                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 491                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
 492                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 493                     break;
 494
 495                   case 'count':
 496                     $select[] = "COUNT({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 497                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 498                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 499                     break;
 500
 501                   case 'avg':
 502                     $select[] = "ROUND(AVG({$field['dbAlias']}),2) as {$tableName}_{$fieldName}_{$stat}";
 503                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
 504                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 505                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 506                     break;
 507                 }
 508               }
 509             }
 510             elseif ($fieldName == 'first_donation_date' || $fieldName == 'first_donation_amount') {
 511               $baseField = CRM_Utils_Array::value('base_field', $field);
 512               $select[] = "{$this->_aliases['civicrm_contribution']}.{$baseField} as {$tableName}_{$fieldName}";
 513               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 514               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 515             }
 516             else {
 517               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 518               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 519               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 520             }
 521           }
 522         }
 523       }
 524     }
 525
 526     $this->_select = 'SELECT ' . implode(', ', $select) . ' ';
 527   }
 528
 529   function from() {
 530     $this->_from = "
 531               FROM civireport_membership_contribution_detail
 532               INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
 533                       ON (civireport_membership_contribution_detail.contribution_id = {$this->_aliases['civicrm_contribution']}.id)
 534               LEFT JOIN civicrm_membership {$this->_aliases['civicrm_membership']}
 535                       ON (civireport_membership_contribution_detail.membership_id = {$this->_aliases['civicrm_membership']}.id)
 536               INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 537                       ON (civireport_membership_contribution_detail.contact_id = {$this->_aliases['civicrm_contact']}.id)
 538               LEFT  JOIN civicrm_membership_status {$this->_aliases['civicrm_membership_status']}
 539                           ON {$this->_aliases['civicrm_membership_status']}.id =
 540                              {$this->_aliases['civicrm_membership']}.status_id
 541 ";
 542
 543     //for premiums
 544     if (CRM_Utils_Array::value('product_name', $this->_params['fields']) || CRM_Utils_Array::value('product_option', $this->_params['fields'])) {
 545       $this->_from .= "
 546                  LEFT JOIN  civicrm_contribution_product {$this->_aliases['civicrm_contribution_product']}
 547                         ON ({$this->_aliases['civicrm_contribution_product']}.contribution_id = {$this->_aliases['civicrm_contribution']}.id)
 548                  LEFT JOIN  civicrm_product {$this->_aliases['civicrm_product']} ON ({$this->_aliases['civicrm_product']}.id = {$this->_aliases['civicrm_contribution_product']}.product_id)";
 549     }
 550
 551     if (!empty($this->_params['ordinality_value'])) {
 552       $this->_from .= "
 553               INNER JOIN (SELECT c.id, IF(COUNT(oc.id) = 0, 0, 1) AS ordinality FROM civicrm_contribution c LEFT JOIN civicrm_contribution oc ON c.contact_id = oc.contact_id AND oc.receive_date < c.receive_date GROUP BY c.id) {$this->_aliases['civicrm_contribution_ordinality']}
 554                       ON {$this->_aliases['civicrm_contribution_ordinality']}.id = {$this->_aliases['civicrm_contribution']}.id";
 555     }
 556
 557     // include contribution note
 558     if (CRM_Utils_Array::value('contribution_note', $this->_params['fields']) || !empty($this->_params['note_value'])) {
 559       $this->_from.= "
 560             LEFT JOIN civicrm_note {$this->_aliases['civicrm_note']}
 561                       ON ( {$this->_aliases['civicrm_note']}.entity_table = 'civicrm_contribution' AND
 562                            {$this->_aliases['civicrm_contribution']}.id = {$this->_aliases['civicrm_note']}.entity_id )";
 563
 564     }
 565
 566     if (CRM_Utils_Array::value('phone', $this->_params['fields'])) {
 567       $this->_from .= "
 568                LEFT JOIN  civicrm_phone {$this->_aliases['civicrm_phone']}
 569                       ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
 570                          {$this->_aliases['civicrm_phone']}.is_primary = 1)";
 571     }
 572     //for contribution batches
 573     if ($this->_closedBatches &&
 574       (CRM_Utils_Array::value('batch_id', $this->_params['fields']) || !empty($this->_params['bid_value']))) {
 575       $this->_from .= "
 576                  LEFT JOIN  civicrm_entity_batch {$this->_aliases['civicrm_entity_batch']}
 577                         ON ({$this->_aliases['civicrm_entity_batch']}.entity_id = {$this->_aliases['civicrm_contribution']}.id AND
 578                         {$this->_aliases['civicrm_entity_batch']}.entity_table = 'civicrm_contribution')
 579                  LEFT JOIN civicrm_batch {$this->_aliases['civicrm_batch']}
 580                         ON {$this->_aliases['civicrm_batch']}.id = {$this->_aliases['civicrm_entity_batch']}.batch_id";
 581     }
 582
 583     if ($this->_addressField OR (!empty($this->_params['state_province_id_value']) OR !empty($this->_params['country_id_value']))) {
 584       $this->_from .= "
 585             LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
 586                    ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND
 587                       {$this->_aliases['civicrm_address']}.is_primary = 1\n";
 588     }
 589
 590     if ($this->_emailField) {
 591       $this->_from .= "
 592             LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
 593                    ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND
 594                       {$this->_aliases['civicrm_email']}.is_primary = 1\n";
 595     }
 596
 597     // include Honor name field
 598     if ($this->_nameFieldHonor) {
 599       $this->_from .= "
 600             LEFT JOIN civicrm_contact contacthonor
 601                       ON contacthonor.id = {$this->_aliases['civicrm_contribution']}.honor_contact_id";
 602     }
 603
 604     // include Honor email field
 605     if ($this->_emailFieldHonor) {
 606       $this->_from .= "
 607             LEFT JOIN civicrm_email emailhonor
 608                       ON emailhonor.contact_id = {$this->_aliases['civicrm_contribution']}.honor_contact_id
 609                       AND emailhonor.is_primary = 1\n";
 610     }
 611   }
 612
 613   function tempTable($applyLimit = TRUE) {
 614     // create temp table with contact ids,contribtuion id,membership id
 615     $dropTempTable = 'DROP TABLE IF EXISTS civireport_membership_contribution_detail';
 616     CRM_Core_DAO::executeQuery($dropTempTable);
 617
 618     $sql = 'CREATE TEMPORARY TABLE civireport_membership_contribution_detail
 619             (contribution_id int, contact_id int, membership_id int, payment_id int) ENGINE=HEAP';
 620     CRM_Core_DAO::executeQuery($sql);
 621
 622     $fillTemp = "
 623           INSERT INTO civireport_membership_contribution_detail (contribution_id, contact_id, membership_id)
 624           SELECT contribution.id, contact.id, m.id
 625           FROM civicrm_contribution contribution
 626           INNER JOIN civicrm_contact contact {$this->_aclFrom}
 627                 ON contact.id = contribution.contact_id AND contribution.is_test = 0
 628            LEFT JOIN civicrm_membership_payment mp
 629                 ON contribution.id = mp.contribution_id
 630            LEFT JOIN civicrm_membership m
 631                 ON mp.membership_id = m.id AND m.is_test = 0 ";
 632
 633     CRM_Core_DAO::executeQuery($fillTemp);
 634   }
 635
 636   function buildQuery($applyLimit = TRUE) {
 637     $this->select();
 638     //create temp table to be used as base table
 639     $this->tempTable();
 640     $this->from();
 641     $this->customDataFrom();
 642     $this->where();
 643     $this->groupBy();
 644     $this->orderBy();
 645
 646     // order_by columns not selected for display need to be included in SELECT
 647     $unselectedSectionColumns = $this->unselectedSectionColumns();
 648     foreach ($unselectedSectionColumns as $alias => $section) {
 649       $this->_select .= ", {$section['dbAlias']} as {$alias}";
 650     }
 651
 652     if ($applyLimit && !CRM_Utils_Array::value('charts', $this->_params)) {
 653       $this->limit();
 654     }
 655
 656     $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}";
 657     return $sql;
 658   }
 659
 660   function groupBy() {
 661     $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_contact']}.id, {$this->_aliases['civicrm_contribution']}.id ";
 662   }
 663
 664   function orderBy() {
 665     $this->_orderBy = " ORDER BY {$this->_aliases['civicrm_contact']}.sort_name, {$this->_aliases['civicrm_contact']}.id ";
 666     if (CRM_Utils_Array::value('first_donation_date', $this->_params['fields'])
 667       || CRM_Utils_Array::value('first_donation_amount', $this->_params['fields'])) {
 668       $this->_orderBy .= ", {$this->_aliases['civicrm_contribution']}.receive_date";
 669     }
 670   }
 671
 672   function statistics(&$rows) {
 673     $statistics = parent::statistics($rows);
 674
 675     $select = "                                                                                                                                                                                                     SELECT COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as count,                                                                                                                                   SUM( {$this->_aliases['civicrm_contribution']}.total_amount ) as amount,                                                                                                                                   ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as avg,                                                                                                                              {$this->_aliases['civicrm_contribution']}.currency as currency                                                                                                                                       ";
 676
 677     $group = "\nGROUP BY {$this->_aliases['civicrm_contribution']}.currency";
 678     $sql = "{$select} {$this->_from} {$this->_where} {$group}";
 679
 680     $dao = CRM_Core_DAO::executeQuery($sql);
 681     while ($dao->fetch()) {
 682       $totalAmount[] = CRM_Utils_Money::format($dao->amount, $dao->currency)."(".$dao->count.")";
 683       $average[] =   CRM_Utils_Money::format($dao->avg, $dao->currency);
 684     }
 685     $statistics['counts']['amount'] = array(
 686       'title' => ts('Total Amount'),
 687       'value' => implode(',  ', $totalAmount),
 688       'type' => CRM_Utils_Type::T_STRING,
 689     );
 690
 691     $statistics['counts']['avg'] = array(
 692       'title' => ts('Average'),
 693       'value' => implode(',  ', $average),
 694       'type' => CRM_Utils_Type::T_STRING,
 695     );
 696
 697     return $statistics;
 698   }
 699
 700   function postProcess() {
 701     // get the acl clauses built before we assemble the query
 702     $this->buildACLClause($this->_aliases['civicrm_contact']);
 703     parent::postProcess();
 704   }
 705
 706   function alterDisplay(&$rows) {
 707     // custom code to alter rows
 708     $checkList = array();
 709
 710     $entryFound         = FALSE;
 711     $contributionTypes  = CRM_Contribute_PseudoConstant::financialType();
 712     $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus();
 713     $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument();
 714     $honorTypes         = CRM_Core_OptionGroup::values('honor_type', FALSE, FALSE, FALSE, NULL, 'label');
 715
 716     //altering the csv display adding additional fields
 717     if ($this->_outputMode == 'csv') {
 718       foreach ($this->_columns as $tableName => $table) {
 719         if (array_key_exists('fields', $table)) {
 720           foreach ($table['fields'] as $fieldName => $field) {
 721             if (CRM_Utils_Array::value('csv_display', $field) && CRM_Utils_Array::value('no_display', $field)) {
 722               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 723               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 724             }
 725           }
 726         }
 727       }
 728     }
 729
 730     // allow repeat for first donation amount and date in csv
 731     $fAmt  = '';
 732     $fDate = '';
 733     foreach ($rows as $rowNum => $row) {
 734       if ($this->_outputMode == 'csv') {
 735         if (array_key_exists('civicrm_contact_id', $row)) {
 736           if ($contactId = $row['civicrm_contact_id']) {
 737             if ($rowNum == 0) {
 738               $pcid  = $contactId;
 739               $fAmt  = $row['first_donation_first_donation_amount'];
 740               $fDate = $row['first_donation_first_donation_date'];
 741             }
 742             else {
 743               if ($pcid == $contactId) {
 744                 $rows[$rowNum]['first_donation_first_donation_amount'] = $fAmt;
 745                 $rows[$rowNum]['first_donation_first_donation_date']   = $fDate;
 746                 $pcid = $contactId;
 747               }
 748               else {
 749                 $fAmt  = $row['first_donation_first_donation_amount'];
 750                 $fDate = $row['first_donation_first_donation_date'];
 751                 $pcid = $contactId;
 752               }
 753             }
 754           }
 755         }
 756       }
 757
 758       if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') {
 759         $repeatFound = FALSE;
 760
 761
 762         $display_flag = NULL;
 763         if (array_key_exists('civicrm_contact_id', $row)) {
 764           if ($cid = $row['civicrm_contact_id']) {
 765             if ($rowNum == 0) {
 766               $prev_cid = $cid;
 767             }
 768             else {
 769               if ($prev_cid == $cid) {
 770                 $display_flag = 1;
 771                 $prev_cid = $cid;
 772               }
 773               else {
 774                 $display_flag = 0;
 775                 $prev_cid = $cid;
 776               }
 777             }
 778
 779             if ($display_flag) {
 780               foreach ($row as $colName => $colVal) {
 781                 if (in_array($colName, $this->_noRepeats)) {
 782                   unset($rows[$rowNum][$colName]);
 783                 }
 784               }
 785             }
 786             $entryFound = TRUE;
 787           }
 788         }
 789       }
 790
 791
 792       if (array_key_exists('civicrm_membership_membership_type_id', $row)) {
 793         if ($value = $row['civicrm_membership_membership_type_id']) {
 794           $rows[$rowNum]['civicrm_membership_membership_type_id'] = CRM_Member_PseudoConstant::membershipType($value, FALSE);
 795         }
 796         $entryFound = TRUE;
 797       }
 798
 799       if (array_key_exists('civicrm_batch_batch_id', $row)) {
 800         if ($value = $row['civicrm_batch_batch_id']) {
 801           $rows[$rowNum]['civicrm_batch_batch_id'] = CRM_Core_DAO::getFieldValue('CRM_Batch_DAO_Batch', $value, 'title');
 802         }
 803         $entryFound = TRUE;
 804       }
 805
 806       if (array_key_exists('civicrm_address_state_province_id', $row)) {
 807         if ($value = $row['civicrm_address_state_province_id']) {
 808           $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
 809         }
 810         $entryFound = TRUE;
 811       }
 812
 813       if (array_key_exists('civicrm_address_country_id', $row)) {
 814         if ($value = $row['civicrm_address_country_id']) {
 815           $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE);
 816         }
 817         $entryFound = TRUE;
 818       }
 819
 820       // convert donor sort name to link
 821       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 822         CRM_Utils_Array::value('civicrm_contact_sort_name', $rows[$rowNum]) &&
 823         array_key_exists('civicrm_contact_id', $row)
 824       ) {
 825         $url = CRM_Utils_System::url('civicrm/contact/view',
 826           'reset=1&cid=' . $row['civicrm_contact_id'],
 827           $this->_absoluteUrl
 828         );
 829
 830         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 831         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts('View Contact Summary for this Contact.');
 832       }
 833
 834       // convert honoree sort name to link
 835       if (array_key_exists('civicrm_contact_honor_sort_name_honor', $row) &&
 836         CRM_Utils_Array::value('civicrm_contact_honor_sort_name_honor', $rows[$rowNum]) &&
 837         array_key_exists('civicrm_contact_honor_id_honor', $row)
 838       ) {
 839
 840         $url = CRM_Utils_System::url('civicrm/contact/view',
 841           'reset=1&cid=' . $row['civicrm_contact_honor_id_honor'],
 842           $this->_absoluteUrl
 843         );
 844         $rows[$rowNum]['civicrm_contact_honor_sort_name_honor_link'] = $url;
 845         $rows[$rowNum]['civicrm_contact_honor_sort_name_honor_hover'] = ts('View Contact Summary for Honoree.');
 846       }
 847
 848       if ($value = CRM_Utils_Array::value('civicrm_contribution_financial_type_id', $row)) {
 849         $rows[$rowNum]['civicrm_contribution_financial_type_id'] = $contributionTypes[$value];
 850         $entryFound = TRUE;
 851       }
 852       if ($value = CRM_Utils_Array::value('civicrm_contribution_contribution_status_id', $row)) {
 853         $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value];
 854         $entryFound = TRUE;
 855       }
 856       if ($value = CRM_Utils_Array::value('civicrm_contribution_payment_instrument_id', $row)) {
 857         $rows[$rowNum]['civicrm_contribution_payment_instrument_id'] = $paymentInstruments[$value];
 858         $entryFound = TRUE;
 859       }
 860       if ($value = CRM_Utils_Array::value('civicrm_contribution_honor_type_id', $row)) {
 861         $rows[$rowNum]['civicrm_contribution_honor_type_id'] = $honorTypes[$value];
 862         $entryFound = TRUE;
 863       }
 864       if (($value = CRM_Utils_Array::value('civicrm_contribution_total_amount_sum', $row)) &&
 865         CRM_Core_Permission::check('access CiviContribute')
 866       ) {
 867         $url = CRM_Utils_System::url('civicrm/contact/view/contribution',
 868           'reset=1&id=' . $row['civicrm_contribution_contribution_id'] . '&cid=' . $row['civicrm_contact_id'] . '&action=view&context=contribution&selectedChild=contribute',
 869           $this->_absoluteUrl
 870         );
 871         $rows[$rowNum]['civicrm_contribution_total_amount_sum_link'] = $url;
 872         $rows[$rowNum]['civicrm_contribution_total_amount_sum_hover'] = ts('View Details of this Contribution.');
 873         $entryFound = TRUE;
 874       }
 875
 876       // convert campaign_id to campaign title
 877       if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
 878         if ($value = $row['civicrm_contribution_campaign_id']) {
 879           $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->activeCampaigns[$value];
 880           $entryFound = TRUE;
 881         }
 882       }
 883
 884       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'member/contributionDetail', 'List all contribution(s) for this ') ? TRUE : $entryFound;
 885
 886       // skip looking further in rows, if first row itself doesn't
 887       // have the column we need
 888       if (!$entryFound) {
 889         break;
 890       }
 891       $lastKey = $rowNum;
 892     }
 893   }
 894 }
 895