CRM/Report/Form/Contribute/Repeat.php

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