CRM/Report/Form/Contribute/Repeat.php

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