CRM/Report/Form/Member/ContributionDetail.php

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