CRM/Report/Form/Contribute/Repeat.php

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