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