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