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