CRM/Report/Form/Member/ContributionDetail.php

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