CRM/Report/Form/Contribute/RecurSummary.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2018                                |
   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-2018
  32  * $Id$
  33  *
  34  */
  35 class CRM_Report_Form_Contribute_RecurSummary extends CRM_Report_Form {
  36   /**
  37    */
  38   public function __construct() {
  39
  40     $this->_columns = array(
  41       'civicrm_contribution_recur' => array(
  42         'dao' => 'CRM_Contribute_DAO_ContributionRecur',
  43         'fields' => array(
  44           'id' => array(
  45             'no_display' => TRUE,
  46             'required' => TRUE,
  47           ),
  48           'payment_instrument_id' => array(
  49             'title' => ts('Payment Instrument'),
  50             'default' => TRUE,
  51             'required' => TRUE,
  52           ),
  53           'start_date' => array(
  54             'title' => ts('Started'),
  55             'default' => TRUE,
  56             'required' => TRUE,
  57           ),
  58           'cancel_date' => array(
  59             'title' => ts('Cancelled'),
  60             'default' => TRUE,
  61             'required' => TRUE,
  62           ),
  63           'contribution_status_id' => array(
  64             'title' => ts('Active'),
  65             'default' => TRUE,
  66             'required' => TRUE,
  67           ),
  68           'amount' => array(
  69             'title' => ts('Total Amount'),
  70             'default' => TRUE,
  71             'required' => TRUE,
  72           ),
  73         ),
  74         'filters' => array(
  75           'start_date' => array(
  76             'title' => ts('Start Date'),
  77             'operatorType' => CRM_Report_Form::OP_DATETIME,
  78             'type' => CRM_Utils_Type::T_TIME,
  79           ),
  80         ),
  81       ),
  82     );
  83     $this->_currencyColumn = 'civicrm_contribution_recur_currency';
  84     parent::__construct();
  85   }
  86
  87   /**
  88    * @param bool $freeze
  89    *
  90    * @return array
  91    */
  92   public function setDefaultValues($freeze = TRUE) {
  93     return parent::setDefaultValues($freeze);
  94   }
  95
  96   public function select() {
  97     // @todo remove & only adjust parent with selectWhere fn (if needed)
  98     $select = array();
  99     $this->_columnHeaders = array();
 100     foreach ($this->_columns as $tableName => $table) {
 101       if (array_key_exists('group_bys', $table)) {
 102         foreach ($table['group_bys'] as $fieldName => $field) {
 103           if (!empty($this->_params['group_bys'][$fieldName])) {
 104             switch (CRM_Utils_Array::value($fieldName, $this->_params['group_bys_freq'])) {
 105               case 'YEARWEEK':
 106                 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL WEEKDAY({$field['dbAlias']}) DAY) AS {$tableName}_{$fieldName}_start";
 107                 $select[] = "YEARWEEK({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 108                 $select[] = "WEEKOFYEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 109                 $field['title'] = 'Week';
 110                 break;
 111
 112               case 'YEAR':
 113                 $select[] = "MAKEDATE(YEAR({$field['dbAlias']}), 1)  AS {$tableName}_{$fieldName}_start";
 114                 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 115                 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 116                 $field['title'] = 'Year';
 117                 break;
 118
 119               case 'MONTH':
 120                 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL (DAYOFMONTH({$field['dbAlias']})-1) DAY) as {$tableName}_{$fieldName}_start";
 121                 $select[] = "MONTH({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 122                 $select[] = "MONTHNAME({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 123                 $field['title'] = 'Month';
 124                 break;
 125
 126               case 'QUARTER':
 127                 $select[] = "STR_TO_DATE(CONCAT( 3 * QUARTER( {$field['dbAlias']} ) -2 , '/', '1', '/', YEAR( {$field['dbAlias']} ) ), '%m/%d/%Y') AS {$tableName}_{$fieldName}_start";
 128                 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 129                 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 130                 $field['title'] = 'Quarter';
 131                 break;
 132             }
 133             if (!empty($this->_params['group_bys_freq'][$fieldName])) {
 134               $this->_interval = $field['title'];
 135               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['title'] = $field['title'] . ' Beginning';
 136               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['type'] = $field['type'];
 137               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['group_by'] = $this->_params['group_bys_freq'][$fieldName];
 138
 139               // just to make sure these values are transfered to rows.
 140               // since we need that for calculation purpose,
 141               // e.g making subtotals look nicer or graphs
 142               $this->_columnHeaders["{$tableName}_{$fieldName}_interval"] = array('no_display' => TRUE);
 143               $this->_columnHeaders["{$tableName}_{$fieldName}_subtotal"] = array('no_display' => TRUE);
 144             }
 145           }
 146         }
 147       }
 148
 149       if (array_key_exists('fields', $table)) {
 150         foreach ($table['fields'] as $fieldName => $field) {
 151           if (!empty($field['required']) ||
 152             !empty($this->_params['fields'][$fieldName])
 153           ) {
 154
 155             // only include statistics columns if set
 156             if (!empty($field['statistics'])) {
 157               foreach ($field['statistics'] as $stat => $label) {
 158                 switch (strtolower($stat)) {
 159                   case 'sum':
 160                     $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 161                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 162                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
 163                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 164                     break;
 165
 166                   case 'count':
 167                     $select[] = "COUNT({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 168                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
 169                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 170                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 171                     break;
 172
 173                   case 'avg':
 174                     $select[] = "ROUND(AVG({$field['dbAlias']}),2) as {$tableName}_{$fieldName}_{$stat}";
 175                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
 176                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 177                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 178                     break;
 179                 }
 180               }
 181             }
 182             else {
 183               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 184               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 185               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 186             }
 187           }
 188         }
 189       }
 190     }
 191     $this->_selectClauses = $select;
 192
 193     $this->_select = "SELECT " . implode(', ', $select) . " ";
 194   }
 195
 196   public function from() {
 197     $softCreditJoin = "LEFT";
 198     if (!empty($this->_params['fields']['soft_amount']) &&
 199       empty($this->_params['fields']['total_amount'])
 200     ) {
 201       // if its only soft credit stats, use inner join
 202       $softCreditJoin = "INNER";
 203     }
 204
 205     $this->_from = "
 206              FROM civicrm_contribution_recur   {$this->_aliases['civicrm_contribution_recur']}
 207     ";
 208   }
 209
 210   public function postProcess() {
 211     $this->beginPostProcess();
 212     $sql = $this->buildQuery(TRUE);
 213     $rows = array();
 214
 215     $this->buildRows($sql, $rows);
 216     $this->formatDisplay($rows);
 217     $this->doTemplateAssignment($rows);
 218     $this->endPostProcess($rows);
 219   }
 220
 221   public function groupBy() {
 222     $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_contribution_recur']}.payment_instrument_id");
 223   }
 224
 225   /**
 226    * Alter display of rows.
 227    *
 228    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 229    * such as rendering contacts as links.
 230    *
 231    * @param array $rows
 232    *   Rows generated by SQL, with an array for each row.
 233    */
 234   public function alterDisplay(&$rows) {
 235
 236     $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument();
 237
 238     $entryFound = FALSE;
 239
 240     $startDateFrom = CRM_Utils_Array::value("start_date_to", $this->_params);
 241     $startDateTo = CRM_Utils_Array::value("start_date_from", $this->_params);
 242     $startDateRelative = CRM_Utils_Array::value("start_date_relative", $this->_params);
 243
 244     $startedDateSql = $this->dateClause('start_date', $startDateRelative, $startDateFrom, $startDateTo);
 245     $startedDateSql = $startedDateSql ? $startedDateSql : " ( 1 ) ";
 246
 247     $cancelledDateSql = $this->dateClause('cancel_date', $startDateRelative, $startDateFrom, $startDateTo);
 248     $cancelledDateSql = $cancelledDateSql ? $cancelledDateSql : " ( cancel_date IS NOT NULL ) ";
 249
 250     $started = $cancelled = $active = $total = 0;
 251
 252     foreach ($rows as $rowNum => $row) {
 253
 254       $paymentInstrumentId = CRM_Utils_Array::value('civicrm_contribution_recur_payment_instrument_id', $row);
 255
 256       $rows[$rowNum]['civicrm_contribution_recur_start_date'] = 0;
 257       $rows[$rowNum]['civicrm_contribution_recur_cancel_date'] = 0;
 258       $rows[$rowNum]['civicrm_contribution_recur_contribution_status_id'] = 0;
 259
 260       $startedSql = "SELECT count(*) as count FROM civicrm_contribution_recur WHERE payment_instrument_id = $paymentInstrumentId AND $startedDateSql ";
 261
 262       $startedDao = CRM_Core_DAO::executeQuery($startedSql);
 263       $startedDao->fetch();
 264
 265       $rows[$rowNum]['civicrm_contribution_recur_start_date'] = $startedDao->count;
 266       $started = $started + $startedDao->count;
 267
 268       $cancelledSql = "SELECT count(*) as count FROM civicrm_contribution_recur WHERE payment_instrument_id = $paymentInstrumentId AND $cancelledDateSql ";
 269
 270       $cancelledDao = CRM_Core_DAO::executeQuery($cancelledSql);
 271       $cancelledDao->fetch();
 272
 273       $rows[$rowNum]['civicrm_contribution_recur_cancel_date'] = $cancelledDao->count;
 274
 275       $cancelled = $cancelled + $cancelledDao->count;
 276
 277       $activeSql = "SELECT count(*) as count FROM civicrm_contribution_recur WHERE payment_instrument_id = $paymentInstrumentId";
 278       list($from, $to) = $this->getFromTo($startDateRelative, $startDateFrom, $startDateTo);
 279       // To find active recurring contribution start date must be >= to start of selected date-range AND
 280       // end date or cancel date must be >= to end of selected date-range if NOT null OR end date is null
 281       if (!empty($from)) {
 282         $activeSql .= " AND start_date >= '{$from}'";
 283       }
 284       if (!empty($to)) {
 285         $activeSql .= " AND (
 286         ( end_date >= '{$to}' AND end_date IS NOT NULL ) OR
 287         ( cancel_date >= '{$to}' AND cancel_date IS NOT NULL ) OR
 288         end_date IS NULL )";
 289       }
 290
 291       $activeDao = CRM_Core_DAO::executeQuery($activeSql);
 292       $activeDao->fetch();
 293
 294       $rows[$rowNum]['civicrm_contribution_recur_contribution_status_id'] = $activeDao->count;
 295
 296       $active = $active + $activeDao->count;
 297
 298       $lineTotal = 0;
 299       $amountSql = "
 300   SELECT SUM(cc.total_amount) as amount FROM `civicrm_contribution` cc
 301   INNER JOIN civicrm_contribution_recur cr ON (cr.id = cc.contribution_recur_id AND cr.payment_instrument_id = {$paymentInstrumentId})
 302   WHERE cc.contribution_status_id = 1 AND cc.is_test = 0 AND ";
 303       $amountSql .= str_replace("start_date", "cc.`receive_date`", $startedDateSql);
 304       $amountDao = CRM_Core_DAO::executeQuery($amountSql);
 305       $amountDao->fetch();
 306       if ($amountDao->amount) {
 307         $lineTotal = $amountDao->amount;
 308       }
 309
 310       $rows[$rowNum]['civicrm_contribution_recur_amount'] = CRM_Utils_Money::format($lineTotal);
 311
 312       $total = $total + $amountDao->amount;
 313
 314       // handle payment instrument id
 315       if ($value = CRM_Utils_Array::value('civicrm_contribution_recur_payment_instrument_id', $row)) {
 316         $rows[$rowNum]['civicrm_contribution_recur_payment_instrument_id'] = $paymentInstruments[$value];
 317         $entryFound = TRUE;
 318       }
 319
 320       // skip looking further in rows, if first row itself doesn't
 321       // have the column we need
 322       if (!$entryFound) {
 323         break;
 324       }
 325     }
 326     // Add total line only if results are available
 327     if (count($rows) > 0) {
 328       $lastRow = array(
 329         'civicrm_contribution_recur_payment_instrument_id' => '',
 330         'civicrm_contribution_recur_start_date' => $started,
 331         'civicrm_contribution_recur_cancel_date' => $cancelled,
 332         'civicrm_contribution_recur_contribution_status_id' => $active,
 333         'civicrm_contribution_recur_amount' => CRM_Utils_Money::format($total),
 334       );
 335       $rows[] = $lastRow;
 336     }
 337   }
 338
 339 }