CRM/Report/Form/Contribute/Repeat.php

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