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