CRM/Report/Form/Contribute/TopDonor.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   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-2015
  32  * $Id$
  33  *
  34  */
  35 class CRM_Report_Form_Contribute_TopDonor extends CRM_Report_Form {
  36
  37   protected $_summary = NULL;
  38   protected $_customGroupExtends = array(
  39     'Contact',
  40     'Individual',
  41     'Contribution',
  42   );
  43
  44   public $_drilldownReport = array('contribute/detail' => 'Link to Detail Report');
  45
  46   protected $_charts = array(
  47     '' => 'Tabular',
  48     'barChart' => 'Bar Chart',
  49     'pieChart' => 'Pie Chart',
  50   );
  51
  52   /**
  53    */
  54   public function __construct() {
  55     $this->_autoIncludeIndexedFieldsAsOrderBys = 1;
  56     $this->_columns = array(
  57       'civicrm_contact' => array(
  58         'dao' => 'CRM_Contact_DAO_Contact',
  59         'fields' => array(
  60           'display_name' => array(
  61             'title' => ts('Contact Name'),
  62             'required' => TRUE,
  63             'no_repeat' => TRUE,
  64           ),
  65           'first_name' => array(
  66             'title' => ts('First Name'),
  67           ),
  68           'middle_name' => array(
  69             'title' => ts('Middle Name'),
  70           ),
  71           'last_name' => array(
  72             'title' => ts('Last Name'),
  73           ),
  74           'id' => array(
  75             'no_display' => TRUE,
  76             'required' => TRUE,
  77           ),
  78           'gender_id' => array(
  79             'title' => ts('Gender'),
  80           ),
  81           'birth_date' => array(
  82             'title' => ts('Birth Date'),
  83           ),
  84           'age' => array(
  85             'title' => ts('Age'),
  86             'dbAlias' => 'TIMESTAMPDIFF(YEAR, contact_civireport.birth_date, CURDATE())',
  87           ),
  88           'contact_type' => array(
  89             'title' => ts('Contact Type'),
  90           ),
  91           'contact_sub_type' => array(
  92             'title' => ts('Contact Subtype'),
  93           ),
  94         ),
  95         'filters' => array(
  96           'gender_id' => array(
  97             'title' => ts('Gender'),
  98             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
  99             'options' => CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'gender_id'),
 100           ),
 101           'contact_type' => array(
 102             'title' => ts('Contact Type'),
 103           ),
 104           'contact_sub_type' => array(
 105             'title' => ts('Contact Subtype'),
 106           ),
 107         ),
 108         'filters' => array(
 109           'gender_id' => array(
 110             'title' => ts('Gender'),
 111             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 112             'options' => CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'gender_id'),
 113           ),
 114         ),
 115       ),
 116       'civicrm_line_item' => array(
 117         'dao' => 'CRM_Price_DAO_LineItem',
 118       ),
 119     );
 120     $this->_columns += $this->getAddressColumns();
 121     $this->_columns += array(
 122       'civicrm_contribution' => array(
 123         'dao' => 'CRM_Contribute_DAO_Contribution',
 124         'fields' => array(
 125           'total_amount' => array(
 126             'title' => ts('Amount Statistics'),
 127             'required' => TRUE,
 128             'statistics' => array(
 129               'sum' => ts('Aggregate Amount'),
 130               'count' => ts('Donations'),
 131               'avg' => ts('Average'),
 132             ),
 133           ),
 134           'currency' => array(
 135             'required' => TRUE,
 136             'no_display' => TRUE,
 137           ),
 138         ),
 139         'filters' => array(
 140           'sort_name' => array(
 141             'title' => ts('Participant Name'),
 142             'type' => CRM_Utils_Type::T_STRING,
 143             'operator' => 'like',
 144           ),
 145           'id' => array(
 146             'title' => ts('Contact ID'),
 147             'type' => CRM_Utils_Type::T_INT,
 148             'no_display' => TRUE,
 149           ),
 150           'birth_date' => array(
 151             'title' => ts('Birth Date'),
 152             'operatorType' => CRM_Report_Form::OP_DATE,
 153           ),
 154           'contact_type' => array(
 155             'title' => ts('Contact Type'),
 156             'type' => CRM_Utils_Type::T_STRING,
 157           ),
 158           'contact_sub_type' => array(
 159             'title' => ts('Contact Subtype'),
 160             'type' => CRM_Utils_Type::T_STRING,
 161           ),
 162           'receive_date' => array(
 163             'default' => 'this.year',
 164             'operatorType' => CRM_Report_Form::OP_DATE,
 165           ),
 166           'currency' => array(
 167             'title' => 'Currency',
 168             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 169             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 170             'default' => NULL,
 171             'type' => CRM_Utils_Type::T_STRING,
 172           ),
 173           'total_range' => array(
 174             'title' => ts('Show no. of Top Donors'),
 175             'type' => CRM_Utils_Type::T_INT,
 176             'default_op' => 'eq',
 177           ),
 178           'financial_type_id' => array(
 179             'name' => 'financial_type_id',
 180             'title' => ts('Financial Type'),
 181             'type' => CRM_Utils_Type::T_INT,
 182             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 183             'options' => CRM_Financial_BAO_FinancialType::getAvailableFinancialTypes(),
 184           ),
 185           'contribution_status_id' => array(
 186             'title' => ts('Contribution Status'),
 187             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 188             'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 189             'default' => array(1),
 190           ),
 191         ),
 192       ),
 193       'civicrm_email' => array(
 194         'dao' => 'CRM_Core_DAO_Email',
 195         'fields' => array(
 196           'email' => array(
 197             'title' => ts('Email'),
 198             'default' => TRUE,
 199             'no_repeat' => TRUE,
 200           ),
 201         ),
 202         'grouping' => 'email-fields',
 203       ),
 204       'civicrm_phone' => array(
 205         'dao' => 'CRM_Core_DAO_Phone',
 206         'fields' => array(
 207           'phone' => array(
 208             'title' => ts('Phone'),
 209             'default' => TRUE,
 210             'no_repeat' => TRUE,
 211           ),
 212         ),
 213         'grouping' => 'phone-fields',
 214       ),
 215     );
 216
 217     $this->_groupFilter = TRUE;
 218     $this->_tagFilter = TRUE;
 219     $this->_currencyColumn = 'civicrm_contribution_currency';
 220     parent::__construct();
 221   }
 222
 223   public function preProcess() {
 224     parent::preProcess();
 225   }
 226
 227   public function select() {
 228     $select = array();
 229     $this->_columnHeaders = array();
 230     //Headers for Rank column
 231     $this->_columnHeaders["civicrm_donor_rank"]['title'] = ts('Rank');
 232     $this->_columnHeaders["civicrm_donor_rank"]['type'] = 1;
 233     //$select[] ="(@rank:=@rank+1)  as civicrm_donor_rank ";
 234
 235     foreach ($this->_columns as $tableName => $table) {
 236       if (array_key_exists('fields', $table)) {
 237         foreach ($table['fields'] as $fieldName => $field) {
 238           if (!empty($field['required']) ||
 239             !empty($this->_params['fields'][$fieldName])
 240           ) {
 241             // only include statistics columns if set
 242             if (!empty($field['statistics'])) {
 243               foreach ($field['statistics'] as $stat => $label) {
 244                 switch (strtolower($stat)) {
 245                   case 'sum':
 246                     $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 247                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 248                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
 249                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 250                     break;
 251
 252                   case 'count':
 253                     $select[] = "COUNT({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 254                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 255                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
 256                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 257                     break;
 258
 259                   case 'avg':
 260                     $select[] = "ROUND(AVG({$field['dbAlias']}),2) as {$tableName}_{$fieldName}_{$stat}";
 261                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
 262                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 263                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 264                     break;
 265                 }
 266               }
 267             }
 268             else {
 269               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 270               // $field['type'] is not always set. Use string type as default if not set.
 271               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = isset($field['type']) ? $field['type'] : 2;
 272               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 273             }
 274           }
 275         }
 276       }
 277     }
 278
 279     $this->_select = " SELECT * FROM ( SELECT " . implode(', ', $select) . " ";
 280   }
 281
 282   /**
 283    * @param $fields
 284    * @param $files
 285    * @param $self
 286    *
 287    * @return array
 288    */
 289   public static function formRule($fields, $files, $self) {
 290     $errors = array();
 291
 292     $op = CRM_Utils_Array::value('total_range_op', $fields);
 293     $val = CRM_Utils_Array::value('total_range_value', $fields);
 294
 295     if (!in_array($op, array(
 296       'eq',
 297       'lte',
 298     ))
 299     ) {
 300       $errors['total_range_op'] = ts("Please select 'Is equal to' OR 'Is Less than or equal to' operator");
 301     }
 302
 303     if ($val && !CRM_Utils_Rule::positiveInteger($val)) {
 304       $errors['total_range_value'] = ts("Please enter positive number");
 305     }
 306     return $errors;
 307   }
 308
 309   public function from() {
 310     $this->_from = "
 311         FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}
 312             INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
 313                 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND {$this->_aliases['civicrm_contribution']}.is_test = 0
 314              LEFT  JOIN civicrm_email  {$this->_aliases['civicrm_email']}
 315                          ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id
 316                          AND {$this->_aliases['civicrm_email']}.is_primary = 1
 317              LEFT  JOIN civicrm_phone  {$this->_aliases['civicrm_phone']}
 318                          ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
 319                             {$this->_aliases['civicrm_phone']}.is_primary = 1";
 320     $this->addAddressFromClause();
 321   }
 322
 323   public function where() {
 324     $clauses = array();
 325     $this->_tempClause = $this->_outerCluase = $this->_groupLimit = '';
 326     foreach ($this->_columns as $tableName => $table) {
 327       if (array_key_exists('filters', $table)) {
 328         foreach ($table['filters'] as $fieldName => $field) {
 329           $clause = NULL;
 330           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 331             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 332             $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 333             $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 334
 335             if ($relative || $from || $to) {
 336               $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 337             }
 338           }
 339           else {
 340             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 341             if ($op) {
 342               $clause = $this->whereClause($field,
 343                 $op,
 344                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 345                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 346                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 347               );
 348             }
 349           }
 350
 351           if (!empty($clause)) {
 352             if ($fieldName == 'total_range') {
 353               $value = CRM_Utils_Array::value("total_range_value", $this->_params);
 354               $this->_outerCluase = " WHERE (( @rows := @rows + 1) <= {$value}) ";
 355               $this->_groupLimit = " LIMIT {$value}";
 356             }
 357             else {
 358               $clauses[] = $clause;
 359             }
 360           }
 361         }
 362       }
 363     }
 364     if (empty($clauses)) {
 365       $this->_where = "WHERE ( 1 ) ";
 366     }
 367     else {
 368       $this->_where = "WHERE " . implode(' AND ', $clauses);
 369     }
 370
 371     if ($this->_aclWhere) {
 372       $this->_where .= " AND {$this->_aclWhere} ";
 373     }
 374   }
 375
 376   public function groupBy() {
 377     $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contact']}.id, {$this->_aliases['civicrm_contribution']}.currency";
 378   }
 379
 380   public function postProcess() {
 381
 382     $this->beginPostProcess();
 383
 384     // get the acl clauses built before we assemble the query
 385     $this->buildACLClause($this->_aliases['civicrm_contact']);
 386
 387     $this->select();
 388
 389     $this->from();
 390     $this->getPermissionedFTQuery($this);
 391
 392     $this->where();
 393
 394     $this->groupBy();
 395
 396     $this->limit();
 397
 398     //set the variable value rank, rows = 0
 399     $setVariable = " SET @rows:=0, @rank=0 ";
 400     CRM_Core_DAO::singleValueQuery($setVariable);
 401
 402     $sql = " {$this->_select} {$this->_from}  {$this->_where} {$this->_groupBy}
 403                      ORDER BY civicrm_contribution_total_amount_sum DESC
 404                  ) as abc {$this->_outerCluase} $this->_limit
 405                ";
 406
 407     $dao = CRM_Core_DAO::executeQuery($sql);
 408
 409     while ($dao->fetch()) {
 410       $row = array();
 411       foreach ($this->_columnHeaders as $key => $value) {
 412         if (property_exists($dao, $key)) {
 413           $row[$key] = $dao->$key;
 414         }
 415       }
 416       $rows[] = $row;
 417     }
 418     $this->formatDisplay($rows);
 419
 420     $this->doTemplateAssignment($rows);
 421
 422     $this->endPostProcess($rows);
 423   }
 424
 425   /**
 426    * @param int $groupID
 427    */
 428   public function add2group($groupID) {
 429     if (is_numeric($groupID)) {
 430
 431       $sql = "
 432 {$this->_select} {$this->_from}  {$this->_where} {$this->_groupBy}
 433 ORDER BY civicrm_contribution_total_amount_sum DESC
 434 ) as abc {$this->_groupLimit}";
 435       $dao = CRM_Core_DAO::executeQuery($sql);
 436
 437       $contact_ids = array();
 438       // Add resulting contacts to group
 439       while ($dao->fetch()) {
 440         $contact_ids[$dao->civicrm_contact_id] = $dao->civicrm_contact_id;
 441       }
 442
 443       CRM_Contact_BAO_GroupContact::addContactsToGroup($contact_ids, $groupID);
 444       CRM_Core_Session::setStatus(ts("Listed contact(s) have been added to the selected group."), ts('Contacts Added'), 'success');
 445     }
 446   }
 447
 448   /**
 449    * @param int $rowCount
 450    */
 451   public function limit($rowCount = CRM_Report_Form::ROW_COUNT_LIMIT) {
 452     // lets do the pager if in html mode
 453     $this->_limit = NULL;
 454
 455     // CRM-14115, over-ride row count if rowCount is specified in URL
 456     if ($this->_dashBoardRowCount) {
 457       $rowCount = $this->_dashBoardRowCount;
 458     }
 459     if ($this->_outputMode == 'html' || $this->_outputMode == 'group') {
 460       // Replace only first occurrence of SELECT.
 461       $this->_select = preg_replace('/SELECT/', 'SELECT SQL_CALC_FOUND_ROWS ', $this->_select, 1);
 462       $pageId = CRM_Utils_Request::retrieve('crmPID', 'Integer', CRM_Core_DAO::$_nullObject);
 463
 464       if (!$pageId && !empty($_POST) && isset($_POST['crmPID_B'])) {
 465         if (!isset($_POST['PagerBottomButton'])) {
 466           unset($_POST['crmPID_B']);
 467         }
 468         else {
 469           $pageId = max((int) @$_POST['crmPID_B'], 1);
 470         }
 471       }
 472
 473       $pageId = $pageId ? $pageId : 1;
 474       $this->set(CRM_Utils_Pager::PAGE_ID, $pageId);
 475       $offset = ($pageId - 1) * $rowCount;
 476
 477       $offset = CRM_Utils_Type::escape($offset, 'Int');
 478       $rowCount = CRM_Utils_Type::escape($rowCount, 'Int');
 479
 480       $this->_limit = " LIMIT $offset, " . $rowCount;
 481     }
 482   }
 483
 484   /**
 485    * Alter display of rows.
 486    *
 487    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 488    * such as rendering contacts as links.
 489    *
 490    * @param array $rows
 491    *   Rows generated by SQL, with an array for each row.
 492    */
 493   public function alterDisplay(&$rows) {
 494     $entryFound = FALSE;
 495     $rank = 1;
 496     if (!empty($rows)) {
 497       foreach ($rows as $rowNum => $row) {
 498
 499         $rows[$rowNum]['civicrm_donor_rank'] = $rank++;
 500         // convert display name to links
 501         if (array_key_exists('civicrm_contact_display_name', $row) &&
 502           array_key_exists('civicrm_contact_id', $row) &&
 503           !empty($row['civicrm_contribution_currency'])
 504         ) {
 505           $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
 506             'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'] .
 507             "&currency_value=" . $row['civicrm_contribution_currency'],
 508             $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 509           );
 510           $rows[$rowNum]['civicrm_contact_display_name_link'] = $url;
 511           $entryFound = TRUE;
 512         }
 513         $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'contribute/detail', 'List all contribution(s)') ? TRUE : $entryFound;
 514
 515         //handle gender
 516         if (array_key_exists('civicrm_contact_gender_id', $row)) {
 517           if ($value = $row['civicrm_contact_gender_id']) {
 518             $gender = CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'gender_id');
 519             $rows[$rowNum]['civicrm_contact_gender_id'] = $gender[$value];
 520           }
 521           $entryFound = TRUE;
 522         }
 523
 524         // display birthday in the configured custom format
 525         if (array_key_exists('civicrm_contact_birth_date', $row)) {
 526           $birthDate = $row['civicrm_contact_birth_date'];
 527           if ($birthDate) {
 528             $rows[$rowNum]['civicrm_contact_birth_date'] = CRM_Utils_Date::customFormat($birthDate, '%Y%m%d');
 529           }
 530           $entryFound = TRUE;
 531         }
 532
 533         // skip looking further in rows, if first row itself doesn't
 534         // have the column we need
 535         if (!$entryFound) {
 536           break;
 537         }
 538       }
 539     }
 540   }
 541
 542 }