CRM/Report/Form/Contribute/Repeat.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.5                                                |
   6  +--------------------------------------------------------------------+
   7  | Copyright CiviCRM LLC (c) 2004-2014                                |
   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-2014
  33  * $Id$
  34  *
  35  */
  36 class CRM_Report_Form_Contribute_Repeat extends CRM_Report_Form {
  37   protected $_amountClauseWithAND = NULL;
  38
  39   public $_drilldownReport = array('contribute/detail' => 'Link to Detail Report');
  40
  41   /**
  42    *
  43    */
  44   /**
  45    *
  46    */
  47   function __construct() {
  48     $this->_columns = array(
  49       'civicrm_contact' =>
  50       array(
  51         'dao' => 'CRM_Contact_DAO_Contact',
  52         'grouping' => 'contact-fields',
  53         'fields' =>
  54         array(
  55           'sort_name' =>
  56           array(
  57             'title' => ts('Contact Name'),
  58             'no_repeat' => TRUE,
  59             'default' => TRUE,
  60           ),
  61           'display_name' =>
  62           array(
  63             'title' => ts('Display Name'),
  64             'no_repeat' => TRUE,
  65           ),
  66           'addressee_display' =>
  67           array(
  68             'title' => ts('Addressee Name'),
  69             'no_repeat' => TRUE,
  70           ),
  71           'id' =>
  72           array(
  73             'no_display' => TRUE,
  74             'required' => TRUE,
  75           ),
  76           'contact_type' =>
  77           array(
  78             '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         ),
  87         'filters' =>
  88         array(
  89           'percentage_change' =>
  90           array(
  91             'title' => ts('Percentage Change'),
  92             'type' => CRM_Utils_Type::T_INT,
  93             'operatorType' => CRM_Report_Form::OP_INT,
  94             'name' => 'percentage_change',
  95             'dbAlias' => '( ( contribution_civireport2.total_amount_sum - contribution_civireport1.total_amount_sum ) * 100 / contribution_civireport1.total_amount_sum )',
  96           ),
  97         ),
  98         'group_bys' =>
  99         array(
 100           'id' =>
 101           array(
 102             'title' => ts('Contact'),
 103             'default' => TRUE,
 104           ),
 105         ),
 106       ),
 107       'civicrm_email' =>
 108       array(
 109         'dao' => 'CRM_Core_DAO_Email',
 110         'fields' =>
 111         array(
 112           'email' =>
 113           array(
 114             'title' => ts('Email'),
 115             'no_repeat' => TRUE,
 116           ),
 117         ),
 118         'grouping' => 'contact-fields',
 119       ),
 120       'civicrm_phone' =>
 121       array(
 122         'dao' => 'CRM_Core_DAO_Phone',
 123         'fields' =>
 124         array(
 125           'phone' =>
 126           array(
 127             'title' => ts('Phone'),
 128             'no_repeat' => TRUE,
 129           ),
 130         ),
 131         'grouping' => 'contact-fields',
 132       ),
 133       'civicrm_address' =>
 134       array(
 135         'dao' => 'CRM_Core_DAO_Address',
 136         'grouping' => 'contact-fields',
 137         'fields' =>
 138         array(
 139           'street_address' => array('title' => ts('Street Address')),
 140           'supplemental_address_1' => array('title' => ts('Supplemental Address 1')),
 141           'city' => array('title' => ts('City')),
 142           'country_id' => array('title' => ts('Country')),
 143           'state_province_id' => array('title' => ts('State/Province')),
 144           'postal_code' => array('title' => ts('Postal Code')),
 145         ),
 146         'group_bys' =>
 147         array(
 148           'country_id' => array('title' => ts('Country')),
 149           'state_province_id' => array('title' => ts('State/Province'),
 150           ),
 151         ),
 152       ),
 153       'civicrm_financial_type' =>
 154       array(
 155         'dao' => 'CRM_Financial_DAO_FinancialType',
 156         'fields' => array('financial_type' => array('title' => ts('Financial Type'))),
 157         'grouping'  => 'contri-fields',
 158         'group_bys' =>
 159         array('financial_type' =>
 160         array(
 161           'name' => 'id',
 162           'title' => ts('Financial Type'),
 163         ),
 164         ),
 165       ),
 166       'civicrm_contribution' =>
 167       array(
 168         'dao' => 'CRM_Contribute_DAO_Contribution',
 169         'fields' =>
 170         array(
 171           'contribution_source' => NULL,
 172           'total_amount1' =>
 173           array(
 174             'name' => 'total_amount',
 175             'alias' => 'contribution1',
 176             'title' => ts('Range One Stat'),
 177             'type' => CRM_Utils_Type::T_MONEY,
 178             'default' => TRUE,
 179             'required' => TRUE,
 180             'clause' => '
 181 contribution_civireport1.total_amount_count as contribution1_total_amount_count,
 182 contribution_civireport1.total_amount_sum as contribution1_total_amount_sum',
 183           ),
 184           'total_amount2' =>
 185           array(
 186             'name' => 'total_amount',
 187             'alias' => 'contribution2',
 188             'title' => ts('Range Two Stat'),
 189             'type' => CRM_Utils_Type::T_MONEY,
 190             'default' => TRUE,
 191             'required' => TRUE,
 192             'clause' => '
 193 contribution_civireport2.total_amount_count as contribution2_total_amount_count,
 194 contribution_civireport2.total_amount_sum as contribution2_total_amount_sum',
 195           ),
 196         ),
 197         'grouping' => 'contri-fields',
 198         'filters' =>
 199         array(
 200           'receive_date1' =>
 201           array(
 202             'title' => ts('Initial Date Range'),
 203             'default' => 'previous.year',
 204             'type' => CRM_Utils_Type::T_DATE,
 205             'operatorType' => CRM_Report_Form::OP_DATE,
 206             'name' => 'receive_date',
 207           ),
 208           'receive_date2' =>
 209           array(
 210             'title' => ts('Second Date Range'),
 211             'default' => 'this.year',
 212             'type' => CRM_Utils_Type::T_DATE,
 213             'operatorType' => CRM_Report_Form::OP_DATE,
 214             'name' => 'receive_date',
 215           ),
 216           'total_amount1' =>
 217           array(
 218             'title' => ts('Range One Amount'),
 219             'type' => CRM_Utils_Type::T_INT,
 220             'operatorType' => CRM_Report_Form::OP_INT,
 221             'name' => 'total_amount',
 222           ),
 223           'total_amount2' =>
 224           array(
 225             'title' => ts('Range Two Amount'),
 226             'type' => CRM_Utils_Type::T_INT,
 227             'operatorType' => CRM_Report_Form::OP_INT,
 228             'name' => 'total_amount',
 229           ),
 230           'financial_type_id' =>
 231           array(
 232             'title' => ts('Financial Type'),
 233             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 234             'options' => CRM_Contribute_PseudoConstant::financialType(),
 235           ),
 236           'contribution_status_id' =>
 237           array(
 238             'title' => ts('Contribution Status'),
 239             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 240             'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 241             'default' => array('1'),
 242           ),
 243         ),
 244         'group_bys' => array('contribution_source' => NULL),
 245       ),
 246     );
 247
 248     $this->_groupFilter = TRUE;
 249     $this->_tagFilter = TRUE;
 250
 251     parent::__construct();
 252   }
 253
 254   function preProcess() {
 255     parent::preProcess();
 256   }
 257
 258   /**
 259    * @param bool $freeze
 260    *
 261    * @return array
 262    */
 263   function setDefaultValues($freeze = TRUE) {
 264     return parent::setDefaultValues($freeze);
 265   }
 266
 267   function select() {
 268     $select = array();
 269     $append = NULL;
 270     // since contact fields not related to financial type
 271     if (array_key_exists('financial_type', $this->_params['group_bys']) ||
 272       array_key_exists('contribution_source', $this->_params['group_bys'])
 273     ) {
 274       unset($this->_columns['civicrm_contact']['fields']['id']);
 275     }
 276
 277     foreach ($this->_columns as $tableName => $table) {
 278       if (array_key_exists('fields', $table)) {
 279         foreach ($table['fields'] as $fieldName => $field) {
 280           if (!empty($field['required']) || !empty($this->_params['fields'][$fieldName])) {
 281             if (isset($field['clause'])) {
 282               $select[] = $field['clause'];
 283
 284               // FIXME: dirty hack for setting columnHeaders
 285               $this->_columnHeaders["{$field['alias']}_{$field['name']}_sum"]['type'] = CRM_Utils_Array::value('type', $field);
 286               $this->_columnHeaders["{$field['alias']}_{$field['name']}_sum"]['title'] = $field['title'];
 287               $this->_columnHeaders["{$field['alias']}_{$field['name']}_count"]['type'] = CRM_Utils_Array::value('type', $field);
 288               $this->_columnHeaders["{$field['alias']}_{$field['name']}_count"]['title'] = $field['title'];
 289               continue;
 290             }
 291
 292             // only include statistics columns if set
 293             $select[] = "{$field['dbAlias']} as {$field['alias']}_{$field['name']}";
 294             $this->_columnHeaders["{$field['alias']}_{$field['name']}"]['type'] = CRM_Utils_Array::value('type', $field);
 295             $this->_columnHeaders["{$field['alias']}_{$field['name']}"]['title'] = CRM_Utils_Array::value('title', $field);
 296             if (!empty($field['no_display'])) {
 297               $this->_columnHeaders["{$field['alias']}_{$field['name']}"]['no_display'] = TRUE;
 298             }
 299           }
 300         }
 301       }
 302     }
 303
 304     $this->_select = "SELECT " . implode(', ', $select) . " ";
 305   }
 306
 307   /**
 308    * @param bool $tableCol
 309    */
 310   function groupBy($tableCol = FALSE) {
 311     $this->_groupBy = "";
 312     if (!empty($this->_params['group_bys']) && is_array($this->_params['group_bys'])) {
 313       foreach ($this->_columns as $tableName => $table) {
 314         if (array_key_exists('group_bys', $table)) {
 315           foreach ($table['group_bys'] as $fieldName => $field) {
 316             if (!empty($this->_params['group_bys'][$fieldName])) {
 317               if ($tableCol) {
 318                 return array($tableName, $field['alias'], $field['name']);
 319               }
 320               else {
 321                 $this->_groupBy[] = "{$field['dbAlias']}";
 322               }
 323             }
 324           }
 325         }
 326       }
 327
 328       $this->_groupBy = "GROUP BY " . implode(', ', $this->_groupBy);
 329     }
 330   }
 331
 332   function from() {
 333     list($fromTable, $fromAlias, $fromCol) = $this->groupBy(TRUE);
 334     $from = "$fromTable $fromAlias";
 335
 336     if ($fromTable == 'civicrm_contact') {
 337       $contriCol = "contact_id";
 338       $from .= "
 339 LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']} ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id
 340 LEFT JOIN civicrm_email   {$this->_aliases['civicrm_email']}
 341        ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND {$this->_aliases['civicrm_email']}.is_primary = 1
 342 LEFT JOIN civicrm_phone   {$this->_aliases['civicrm_phone']}
 343        ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND {$this->_aliases['civicrm_phone']}.is_primary = 1";
 344
 345     }
 346     else if ($fromTable == 'civicrm_financial_type') {
 347       $contriCol = "financial_type_id";
 348     }
 349     elseif ($fromTable == 'civicrm_contribution') {
 350       $contriCol = $fromCol;
 351     }
 352     elseif ($fromTable == 'civicrm_address') {
 353       $from .= "
 354 INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']} ON {$this->_aliases['civicrm_address']}.contact_id = {$this->_aliases['civicrm_contact']}.id";
 355       $fromAlias = $this->_aliases['civicrm_contact'];
 356       $fromCol   = "id";
 357       $contriCol = "contact_id";
 358     }
 359
 360     $this->_from = "
 361 FROM $from
 362 LEFT JOIN civicrm_temp_civireport_repeat1 {$this->_aliases['civicrm_contribution']}1
 363        ON $fromAlias.$fromCol = {$this->_aliases['civicrm_contribution']}1.$contriCol
 364 LEFT JOIN civicrm_temp_civireport_repeat2 {$this->_aliases['civicrm_contribution']}2
 365        ON $fromAlias.$fromCol = {$this->_aliases['civicrm_contribution']}2.$contriCol";
 366   }
 367
 368   /**
 369    * @param string $replaceAliasWith
 370    *
 371    * @return mixed|string
 372    */
 373   function whereContribution($replaceAliasWith = 'contribution1') {
 374     $clauses = array("is_test" => "{$this->_aliases['civicrm_contribution']}.is_test = 0");
 375
 376     foreach ($this->_columns['civicrm_contribution']['filters'] as $fieldName => $field) {
 377       $clause = NULL;
 378       if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 379         $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 380         $from     = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 381         $to       = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 382
 383         $clause = $this->dateClause($field['dbAlias'], $relative, $from, $to, $field['type']);
 384       }
 385       else {
 386         $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 387         if ($op) {
 388           $clause = $this->whereClause($field,
 389             $op,
 390             CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 391             CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 392             CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 393           );
 394         }
 395       }
 396       if (!empty($clause)) {
 397         $clauses[$fieldName] = $clause;
 398       }
 399     }
 400
 401     if (!$this->_amountClauseWithAND) {
 402       $amountClauseWithAND = array();
 403       if (!empty($clauses['total_amount1'])) {
 404         $amountClauseWithAND[] = str_replace("{$this->_aliases['civicrm_contribution']}.total_amount",
 405           "{$this->_aliases['civicrm_contribution']}1.total_amount_sum", $clauses['total_amount1']);
 406       }
 407       if (!empty($clauses['total_amount2'])) {
 408         $amountClauseWithAND[] = str_replace("{$this->_aliases['civicrm_contribution']}.total_amount",
 409           "{$this->_aliases['civicrm_contribution']}2.total_amount_sum", $clauses['total_amount2']);
 410       }
 411       $this->_amountClauseWithAND = !empty($amountClauseWithAND) ? implode(' AND ', $amountClauseWithAND) : NULL;
 412     }
 413
 414     if ($replaceAliasWith == 'contribution1') {
 415       unset($clauses['receive_date2'], $clauses['total_amount2']);
 416     }
 417     else {
 418       unset($clauses['receive_date1'], $clauses['total_amount1']);
 419     }
 420
 421     $whereClause = !empty($clauses) ? "WHERE " . implode(' AND ', $clauses) : '';
 422
 423     if ($replaceAliasWith) {
 424       $whereClause = str_replace($this->_aliases['civicrm_contribution'], $replaceAliasWith, $whereClause);
 425     }
 426
 427     return $whereClause;
 428   }
 429
 430   function where() {
 431     if (!$this->_amountClauseWithAND) {
 432       $this->_amountClauseWithAND =
 433         "!({$this->_aliases['civicrm_contribution']}1.total_amount_count IS NULL AND {$this->_aliases['civicrm_contribution']}2.total_amount_count IS NULL)";
 434     }
 435     $clauses = array("atleast_one_amount" => $this->_amountClauseWithAND);
 436
 437     foreach ($this->_columns as $tableName => $table) {
 438       if (array_key_exists('filters', $table) && $tableName != 'civicrm_contribution') {
 439         foreach ($table['filters'] as $fieldName => $field) {
 440           $clause = NULL;
 441           $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 442           if ($op) {
 443             $clause = $this->whereClause($field,
 444               $op,
 445               CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 446               CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 447               CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 448             );
 449           }
 450           if (!empty($clause)) {
 451             $clauses[$fieldName] = $clause;
 452           }
 453         }
 454       }
 455     }
 456
 457     $this->_where = !empty($clauses) ? "WHERE " . implode(' AND ', $clauses) : '';
 458   }
 459
 460   /**
 461    * @param $fields
 462    * @param $files
 463    * @param $self
 464    *
 465    * @return array
 466    */
 467   static function formRule($fields, $files, $self) {
 468
 469     $errors = $checkDate = $errorCount = array();
 470
 471     $rules = array(
 472       'id' => array(
 473         'sort_name',
 474         'display_name',
 475         'addressee_display',
 476         'contact_type',
 477         'contact_sub_type',
 478         'email',
 479         'phone',
 480         'state_province_id',
 481         'country_id',
 482         'city',
 483         'street_address',
 484         'supplemental_address_1',
 485         'postal_code',
 486       ),
 487       'country_id' => array('country_id'),
 488       'state_province_id' => array('country_id', 'state_province_id'),
 489       'contribution_source' => array('contribution_source'),
 490       'financial_type' => array('financial_type'),
 491     );
 492
 493     $idMapping = array(
 494       'id' => ts('Contact'),
 495       'exposed_id' => ts('Contact'),
 496       'country_id' => ts('Country'),
 497       'state_province_id' => ts('State/Province'),
 498       'contribution_source' => ts('Contribution Source'),
 499       'financial_type' => ts('Financial Type'),
 500       'sort_name' => ts('Contact Name'),
 501       'email' => ts('Email'),
 502       'phone' => ts('Phone'),
 503     );
 504
 505     if (empty($fields['group_bys'])) {
 506       $errors['fields'] = ts('Please select at least one Group by field.');
 507     }
 508     elseif ((array_key_exists('contribution_source', $fields['group_bys']) ||
 509       array_key_exists('contribution_type', $fields['group_bys'])
 510     ) &&
 511       (count($fields['group_bys']) > 1)
 512     ) {
 513       $errors['fields'] = ts('You can not use other Group by with Financial type or Contribution source.');
 514     }
 515     else {
 516       foreach ($fields['fields'] as $fld_id => $value) {
 517         if (!($fld_id == 'total_amount1') && !($fld_id == 'total_amount2')) {
 518           $found = FALSE;
 519           $invlidGroups = array();
 520           foreach ($fields['group_bys'] as $grp_id => $val) {
 521             $validFields = $rules[$grp_id];
 522             if (in_array($fld_id, $validFields)) {
 523               $found = TRUE;
 524             }
 525             else {
 526               $invlidGroups[] = $idMapping[$grp_id];
 527             }
 528           }
 529           if (!$found) {
 530             $erorrGrps = implode(',', $invlidGroups);
 531             $tempErrors[] = ts("Do not select field %1 with Group by %2.", array(1 => $idMapping[$fld_id], 2 => $erorrGrps));
 532           }
 533         }
 534       }
 535       if (!empty($tempErrors)) {
 536         $errors['fields'] = implode("<br>", $tempErrors);
 537       }
 538     }
 539
 540     if (!empty($fields['gid_value']) && !empty($fields['group_bys'])) {
 541       if (!array_key_exists('id', $fields['group_bys'])) {
 542         $errors['gid_value'] = ts("Filter with Group only allow with group by Contact");
 543       }
 544     }
 545
 546     if ($fields['receive_date1_relative'] == '0') {
 547       $checkDate['receive_date1']['receive_date1_from'] = $fields['receive_date1_from'];
 548       $checkDate['receive_date1']['receive_date1_to'] = $fields['receive_date1_to'];
 549     }
 550
 551     if ($fields['receive_date2_relative'] == '0') {
 552       $checkDate['receive_date2']['receive_date2_from'] = $fields['receive_date2_from'];
 553       $checkDate['receive_date2']['receive_date2_to'] = $fields['receive_date2_to'];
 554     }
 555
 556     foreach ($checkDate as $date_range => $range_data) {
 557       foreach ($range_data as $key => $value) {
 558         if (CRM_Utils_Date::isDate($value)) {
 559           $errorCount[$date_range][$key]['valid'] = 'true';
 560           $errorCount[$date_range][$key]['is_empty'] = 'false';
 561         }
 562         else {
 563           $errorCount[$date_range][$key]['valid'] = 'false';
 564           $errorCount[$date_range][$key]['is_empty'] = 'true';
 565           if (is_array($value)) {
 566             foreach ($value as $v) {
 567               if ($v) {
 568                 $errorCount[$date_range][$key]['is_empty'] = 'false';
 569               }
 570             }
 571           }
 572           elseif (!isset($value)) {
 573             $errorCount[$date_range][$key]['is_empty'] = 'false';
 574           }
 575         }
 576       }
 577     }
 578
 579     $errorText = ts("Select valid date range");
 580     foreach ($errorCount as $date_range => $error_data) {
 581
 582       if (($error_data[$date_range . '_from']['valid'] == 'false') &&
 583         ($error_data[$date_range . '_to']['valid'] == 'false')
 584       ) {
 585
 586         if (($error_data[$date_range . '_from']['is_empty'] == 'true') &&
 587           ($error_data[$date_range . '_to']['is_empty'] == 'true')
 588         ) {
 589           $errors[$date_range . '_relative'] = $errorText;
 590         }
 591
 592         if ($error_data[$date_range . '_from']['is_empty'] == 'false') {
 593           $errors[$date_range . '_from'] = $errorText;
 594         }
 595
 596         if ($error_data[$date_range . '_to']['is_empty'] == 'false') {
 597           $errors[$date_range . '_to'] = $errorText;
 598         }
 599       }
 600       elseif (($error_data[$date_range . '_from']['valid'] == 'true') &&
 601         ($error_data[$date_range . '_to']['valid'] == 'false')
 602       ) {
 603         if ($error_data[$date_range . '_to']['is_empty'] == 'false') {
 604           $errors[$date_range . '_to'] = $errorText;
 605         }
 606       }
 607       elseif (($error_data[$date_range . '_from']['valid'] == 'false') &&
 608         ($error_data[$date_range . '_to']['valid'] == 'true')
 609       ) {
 610         if ($error_data[$date_range . '_from']['is_empty'] == 'false') {
 611           $errors[$date_range . '_from'] = $errorText;
 612         }
 613       }
 614     }
 615
 616     return $errors;
 617   }
 618
 619   /**
 620    * @param $rows
 621    *
 622    * @return array
 623    */
 624   function statistics(&$rows) {
 625     $statistics = parent::statistics($rows);
 626
 627     //fetch contributions for both date ranges from pre-existing temp tables
 628     $sql = "
 629 CREATE TEMPORARY TABLE civicrm_temp_civireport_repeat3
 630 SELECT contact_id FROM civicrm_temp_civireport_repeat1 UNION SELECT contact_id FROM civicrm_temp_civireport_repeat2;";
 631     $dao = CRM_Core_DAO::executeQuery($sql);
 632
 633     $sql = "
 634 SELECT civicrm_temp_civireport_repeat3.contact_id,
 635        civicrm_temp_civireport_repeat1.total_amount_sum as contribution1_total_amount_sum,
 636        civicrm_temp_civireport_repeat2.total_amount_sum as contribution2_total_amount_sum
 637 FROM civicrm_temp_civireport_repeat3
 638 LEFT JOIN civicrm_temp_civireport_repeat1
 639        ON civicrm_temp_civireport_repeat3.contact_id = civicrm_temp_civireport_repeat1.contact_id
 640 LEFT JOIN civicrm_temp_civireport_repeat2
 641        ON civicrm_temp_civireport_repeat3.contact_id = civicrm_temp_civireport_repeat2.contact_id";
 642     $dao = CRM_Core_DAO::executeQuery($sql);
 643
 644     //store contributions in array 'contact_sums' for comparison
 645     $contact_sums = array();
 646     while ($dao->fetch()) {
 647       $contact_sums[$dao->contact_id] =
 648         array(
 649           'contribution1_total_amount_sum' => $dao->contribution1_total_amount_sum,
 650           'contribution2_total_amount_sum' => $dao->contribution2_total_amount_sum
 651         );
 652     }
 653
 654     $total_distinct_contacts = count($contact_sums);
 655     $number_maintained = 0;
 656     $number_upgraded = 0;
 657     $number_downgraded = 0;
 658     $number_new = 0;
 659     $number_lapsed = 0;
 660
 661     foreach ($contact_sums as $uid => $row) {
 662       if ($row['contribution1_total_amount_sum'] && $row['contribution2_total_amount_sum']) {
 663         $change = ($row['contribution1_total_amount_sum'] - $row['contribution2_total_amount_sum']);
 664         if($change == 0)
 665           $number_maintained += 1;
 666         elseif($change > 0)
 667           $number_upgraded += 1;
 668         elseif($change < 0)
 669           $number_downgraded += 1;
 670       }
 671       elseif ($row['contribution1_total_amount_sum']) {
 672         $number_new +=1;
 673       }
 674       elseif ($row['contribution2_total_amount_sum']) {
 675         $number_lapsed +=1;
 676       }
 677     }
 678
 679     //calculate percentages from numbers
 680     $percent_maintained = ($number_maintained / $total_distinct_contacts) * 100;
 681     $percent_upgraded = ($number_upgraded / $total_distinct_contacts) * 100;
 682     $percent_downgraded = ($number_downgraded / $total_distinct_contacts) * 100;
 683     $percent_new = ($number_new / $total_distinct_contacts) * 100;
 684     $percent_lapsed = ($number_lapsed / $total_distinct_contacts) * 100;
 685
 686     //display percentages for new, lapsed, upgraded, downgraded, and maintained contributors
 687     $statistics['counts']['count_new'] = array(
 688       'value' => $percent_new,
 689       'title' => '% New Donors',
 690     );
 691     $statistics['counts']['count_lapsed'] = array(
 692       'value' => $percent_lapsed,
 693       'title' => '% Lapsed Donors',
 694     );
 695     $statistics['counts']['count_upgraded'] = array(
 696       'value' => $percent_upgraded,
 697       'title' => '% Upgraded Donors',
 698     );
 699     $statistics['counts']['count_downgraded'] = array(
 700       'value' => $percent_downgraded,
 701       'title' => '% Downgraded Donors',
 702     );
 703     $statistics['counts']['count_maintained'] = array(
 704       'value' => $percent_maintained,
 705       'title' => '% Maintained Donors',
 706     );
 707
 708     $select = "
 709 SELECT COUNT({$this->_aliases['civicrm_contribution']}1.total_amount_count )       as count,
 710        SUM({$this->_aliases['civicrm_contribution']}1.total_amount_sum )           as amount,
 711        ROUND(AVG({$this->_aliases['civicrm_contribution']}1.total_amount_sum), 2)  as avg,
 712        COUNT({$this->_aliases['civicrm_contribution']}2.total_amount_count )       as count2,
 713        SUM({$this->_aliases['civicrm_contribution']}2.total_amount_sum )           as amount2,
 714        ROUND(AVG({$this->_aliases['civicrm_contribution']}2.total_amount_sum), 2)  as avg2,
 715        currency";
 716     $sql = "{$select} {$this->_from} {$this->_where}
 717 GROUP BY    currency
 718 ";
 719     $dao = CRM_Core_DAO::executeQuery($sql);
 720
 721     $amount = $average = $amount2 = $average2 = array();
 722     $count = $count2 = 0;
 723     while ($dao->fetch()) {
 724       if ($dao->amount) {
 725         $amount[] = CRM_Utils_Money::format($dao->amount, $dao->currency)."(".$dao->count.")";
 726         $average[] =   CRM_Utils_Money::format($dao->avg, $dao->currency);
 727       }
 728
 729       $count += $dao->count;
 730       if ($dao->amount2) {
 731         $amount2[] = CRM_Utils_Money::format($dao->amount2, $dao->currency)."(".$dao->count.")";
 732         $average2[] =   CRM_Utils_Money::format($dao->avg2, $dao->currency);
 733       }
 734       $count2 += $dao->count2;
 735     }
 736
 737     $statistics['counts']['range_one_title'] = array('title' => 'Initial Date Range:');
 738     $statistics['counts']['amount'] = array(
 739       'value' => implode(',  ', $amount),
 740       'title' => 'Total Amount',
 741       'type' => CRM_Utils_Type::T_STRING,
 742     );
 743     $statistics['counts']['count'] = array(
 744       'value' => $count,
 745       'title' => 'Total Donations',
 746     );
 747     $statistics['counts']['avg'] = array(
 748       'value' => implode(',  ', $average),
 749       'title' => 'Average',
 750       'type' => CRM_Utils_Type::T_STRING,
 751     );
 752     $statistics['counts']['range_two_title'] = array(
 753       'title' => 'Second Date Range:',
 754     );
 755     $statistics['counts']['amount2'] = array(
 756       'value' => implode(',  ', $amount2),
 757       'title' => 'Total Amount',
 758       'type' => CRM_Utils_Type::T_STRING,
 759     );
 760     $statistics['counts']['count2'] = array(
 761       'value' => $count2,
 762       'title' => 'Total Donations',
 763     );
 764     $statistics['counts']['avg2'] = array(
 765       'value' => implode(',  ', $average2),
 766       'title' => 'Average',
 767       'type' => CRM_Utils_Type::T_STRING,
 768     );
 769
 770     return $statistics;
 771   }
 772
 773   function postProcess() {
 774     $this->beginPostProcess();
 775     $create = $subSelect1 = $subSelect2 = NULL;
 776     list($fromTable, $fromAlias, $fromCol) = $this->groupBy(TRUE);
 777     if ($fromTable == 'civicrm_contact') {
 778       $contriCol = "contact_id";
 779     }
 780     elseif ($fromTable == 'civicrm_contribution_type') {
 781       $contriCol = "contribution_type_id";
 782     }
 783     elseif ($fromTable == 'civicrm_contribution') {
 784       $contriCol = $fromCol;
 785     }
 786     elseif ($fromTable == 'civicrm_address') {
 787       $contriCol = "contact_id";
 788     }
 789     elseif ($fromTable == 'civicrm_financial_type') {
 790       $contriCol = 'financial_type_id';
 791       $subSelect1 = 'contribution1.contact_id,';
 792       $subSelect2 = 'contribution2.contact_id,';
 793       $create = 'contact_id int unsigned,';
 794     }
 795
 796     $subWhere = $this->whereContribution();
 797     $subContributionQuery1 = "
 798 SELECT {$subSelect1} contribution1.{$contriCol},
 799        sum( contribution1.total_amount ) AS total_amount_sum,
 800        count( * ) AS total_amount_count
 801 FROM   civicrm_contribution contribution1
 802 {$subWhere}
 803 GROUP BY contribution1.{$contriCol}";
 804
 805     $subWhere = $this->whereContribution('contribution2');
 806     $subContributionQuery2 = "
 807 SELECT {$subSelect2} contribution2.{$contriCol},
 808        sum( contribution2.total_amount ) AS total_amount_sum,
 809        count( * ) AS total_amount_count,
 810        currency
 811 FROM   civicrm_contribution contribution2
 812 {$subWhere}
 813 GROUP BY contribution2.{$contriCol}";
 814
 815     $sql = "
 816 CREATE TEMPORARY TABLE civicrm_temp_civireport_repeat1 (
 817 {$create}
 818 {$contriCol} int unsigned,
 819 total_amount_sum int,
 820 total_amount_count int
 821 ) ENGINE=HEAP DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
 822     CRM_Core_DAO::executeQuery($sql);
 823     $sql = "INSERT INTO civicrm_temp_civireport_repeat1 {$subContributionQuery1}";
 824     CRM_Core_DAO::executeQuery($sql);
 825
 826     $sql = "
 827 CREATE TEMPORARY TABLE civicrm_temp_civireport_repeat2 (
 828 {$create}
 829 {$contriCol} int unsigned,
 830 total_amount_sum int,
 831 total_amount_count int,
 832 currency varchar(3)
 833 ) ENGINE=HEAP DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
 834     CRM_Core_DAO::executeQuery($sql);
 835     $sql = "INSERT INTO civicrm_temp_civireport_repeat2 {$subContributionQuery2}";
 836     CRM_Core_DAO::executeQuery($sql);
 837
 838     $this->select();
 839     $this->from();
 840     $this->where();
 841     $this->groupBy();
 842     $this->limit();
 843
 844     $count = 0;
 845     $sql   = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_limit}";
 846     $dao   = CRM_Core_DAO::executeQuery($sql);
 847     $rows  = array();
 848     while ($dao->fetch()) {
 849       foreach ($this->_columnHeaders as $key => $value) {
 850         $rows[$count][$key] = $dao->$key;
 851       }
 852       $count++;
 853     }
 854
 855     // FIXME: calculate % using query
 856     foreach ($rows as $uid => $row) {
 857       if ($row['contribution1_total_amount_sum'] && $row['contribution2_total_amount_sum']) {
 858         $rows[$uid]['change'] = number_format((($row['contribution2_total_amount_sum'] -
 859             $row['contribution1_total_amount_sum']
 860           ) * 100) /
 861             ($row['contribution1_total_amount_sum']), 2
 862         );
 863       }
 864       elseif ($row['contribution1_total_amount_sum']) {
 865         $rows[$uid]['change'] = ts('Skipped Donation');
 866       }
 867       elseif ($row['contribution2_total_amount_sum']) {
 868         $rows[$uid]['change'] = ts('New Donor');
 869       }
 870       if ($row['contribution1_total_amount_count']) {
 871         $rows[$uid]['contribution1_total_amount_sum'] = $row['contribution1_total_amount_sum'] . " ({$row['contribution1_total_amount_count']})";
 872       }
 873       if ($row['contribution2_total_amount_count']) {
 874         $rows[$uid]['contribution2_total_amount_sum'] = $row['contribution2_total_amount_sum'] . " ({$row['contribution2_total_amount_count']})";
 875       }
 876     }
 877     $this->_columnHeaders['change'] = array(
 878       'title' => '% Change',
 879       'type' => CRM_Utils_Type::T_INT,
 880     );
 881
 882     // hack to fix title
 883     list($from1, $to1) = $this->getFromTo(CRM_Utils_Array::value("receive_date1_relative", $this->_params),
 884       CRM_Utils_Array::value("receive_date1_from", $this->_params),
 885       CRM_Utils_Array::value("receive_date1_to", $this->_params)
 886     );
 887     $from1 = CRM_Utils_Date::customFormat($from1, NULL, array('d'));
 888     $to1 = CRM_Utils_Date::customFormat($to1, NULL, array('d'));
 889
 890     list($from2, $to2) = $this->getFromTo(CRM_Utils_Array::value("receive_date2_relative", $this->_params),
 891       CRM_Utils_Array::value("receive_date2_from", $this->_params),
 892       CRM_Utils_Array::value("receive_date2_to", $this->_params)
 893     );
 894     $from2 = CRM_Utils_Date::customFormat($from2, NULL, array('d'));
 895     $to2 = CRM_Utils_Date::customFormat($to2, NULL, array('d'));
 896
 897     $this->_columnHeaders['contribution1_total_amount_sum']['title'] = "$from1 -<br/> $to1";
 898     $this->_columnHeaders['contribution2_total_amount_sum']['title'] = "$from2 -<br/> $to2";
 899     unset($this->_columnHeaders['contribution1_total_amount_count'],
 900     $this->_columnHeaders['contribution2_total_amount_count']
 901     );
 902
 903     $this->formatDisplay($rows);
 904
 905     // assign variables to templates
 906     $this->doTemplateAssignment($rows);
 907
 908     $this->endPostProcess($rows);
 909   }
 910
 911   /**
 912    * @param $rows
 913    */
 914   function alterDisplay(&$rows) {
 915     // custom code to alter rows
 916     list($from1, $to1) = $this->getFromTo(CRM_Utils_Array::value("receive_date1_relative", $this->_params),
 917       CRM_Utils_Array::value("receive_date1_from", $this->_params),
 918       CRM_Utils_Array::value("receive_date1_to", $this->_params)
 919     );
 920     list($from2, $to2) = $this->getFromTo(CRM_Utils_Array::value("receive_date2_relative", $this->_params),
 921       CRM_Utils_Array::value("receive_date2_from", $this->_params),
 922       CRM_Utils_Array::value("receive_date2_to", $this->_params)
 923     );
 924
 925     $dateUrl = "";
 926     if ($from1) {
 927       $dateUrl .= "receive_date1_from={$from1}&";
 928     }
 929     if ($to1) {
 930       $dateUrl .= "receive_date1_to={$to1}&";
 931     }
 932     if ($from2) {
 933       $dateUrl .= "receive_date2_from={$from2}&";
 934     }
 935     if ($to2) {
 936       $dateUrl .= "receive_date2_to={$to2}&";
 937     }
 938
 939     foreach ($rows as $rowNum => $row) {
 940       // handle country
 941       if (array_key_exists('address_civireport_country_id', $row)) {
 942         if ($value = $row['address_civireport_country_id']) {
 943           $rows[$rowNum]['address_civireport_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE);
 944
 945           $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
 946             "reset=1&force=1&" .
 947               "country_id_op=in&country_id_value={$value}&" .
 948               "$dateUrl",
 949             $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 950           );
 951
 952           $rows[$rowNum]['address_civireport_country_id_link'] = $url;
 953           $rows[$rowNum]['address_civireport_country_id_hover'] = ts("View contributions for this Country.");
 954         }
 955       }
 956
 957       // handle state province
 958       if (array_key_exists('address_civireport_state_province_id', $row)) {
 959         if ($value = $row['address_civireport_state_province_id']) {
 960           $rows[$rowNum]['address_civireport_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
 961
 962           $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
 963             "reset=1&force=1&" .
 964               "state_province_id_op=in&state_province_id_value={$value}&" .
 965               "$dateUrl",
 966             $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 967           );
 968
 969           $rows[$rowNum]['address_civireport_state_province_id_link'] = $url;
 970           $rows[$rowNum]['address_civireport_state_province_id_hover'] = ts("View repeatDetails for this state.");
 971         }
 972       }
 973
 974       // convert display name to links
 975       if (array_key_exists('contact_civireport_sort_name', $row) &&
 976         array_key_exists('contact_civireport_id', $row)
 977       ) {
 978         $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
 979           'reset=1&force=1&id_op=eq&id_value=' . $row['contact_civireport_id'],
 980           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 981         );
 982
 983         $rows[$rowNum]['contact_civireport_sort_name_link'] = $url;
 984         $rows[$rowNum]['contact_civireport_sort_name_hover'] = ts("View Contribution details for this contact");
 985       }
 986     }
 987     // foreach ends
 988   }
 989 }
 990