CRM/Report/Form/Contribute/Recur.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  * $Id$
  17  *
  18  */
  19 class CRM_Report_Form_Contribute_Recur extends CRM_Report_Form {
  20
  21   /**
  22    * This report has not been optimised for group filtering.
  23    *
  24    * The functionality for group filtering has been improved but not
  25    * all reports have been adjusted to take care of it. This report has not
  26    * and will run an inefficient query until fixed.
  27    *
  28    * CRM-19170
  29    *
  30    * @var bool
  31    */
  32   protected $groupFilterNotOptimised = TRUE;
  33
  34   /**
  35    * Class constructor.
  36    */
  37   public function __construct() {
  38     $this->_columns = [
  39       'civicrm_contact' => [
  40         'dao' => 'CRM_Contact_DAO_Contact',
  41         'order_bys' => [
  42           'sort_name' => [
  43             'title' => ts("Last name, First name"),
  44           ],
  45         ],
  46         'fields' => [
  47           'sort_name' => [
  48             'title' => ts('Contact Name'),
  49             'no_repeat' => TRUE,
  50             'default' => TRUE,
  51           ],
  52           'id' => [
  53             'no_display' => TRUE,
  54             'required' => TRUE,
  55           ],
  56         ],
  57       ],
  58       'civicrm_email' => [
  59         'dao' => 'CRM_Core_DAO_Email',
  60         'fields' => [
  61           'email' => [
  62             'title' => ts('Email'),
  63             'no_repeat' => TRUE,
  64           ],
  65         ],
  66         'grouping' => 'contact-fields',
  67       ],
  68       'civicrm_phone' => [
  69         'dao' => 'CRM_Core_DAO_Phone',
  70         'fields' => [
  71           'phone' => [
  72             'title' => ts('Phone'),
  73             'no_repeat' => TRUE,
  74           ],
  75         ],
  76         'grouping' => 'contact-fields',
  77       ],
  78       'civicrm_contribution' => [
  79         'dao' => 'CRM_Contribute_DAO_Contribution',
  80         'fields' => [
  81           'id' => [
  82             'no_display' => TRUE,
  83             'required' => TRUE,
  84           ],
  85           'total_amount' => [
  86             'title' => ts('Amount Contributed to Date'),
  87             'statistics' => [
  88               'sum' => ts("Total Amount Contributed"),
  89             ],
  90           ],
  91           'source' => [
  92             'title' => ts('Contribution Source'),
  93           ],
  94         ],
  95       ],
  96       'civicrm_financial_trxn' => [
  97         'dao' => 'CRM_Financial_DAO_FinancialTrxn',
  98         'fields' => [
  99           'card_type_id' => [
 100             'title' => ts('Credit Card Type'),
 101             'dbAlias' => 'GROUP_CONCAT(financial_trxn_civireport.card_type_id SEPARATOR ",")',
 102           ],
 103         ],
 104         'filters' => [
 105           'card_type_id' => [
 106             'title' => ts('Credit Card Type'),
 107             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 108             'options' => CRM_Financial_DAO_FinancialTrxn::buildOptions('card_type_id'),
 109             'default' => NULL,
 110             'type' => CRM_Utils_Type::T_STRING,
 111           ],
 112         ],
 113       ],
 114       'civicrm_contribution_recur' => [
 115         'dao' => 'CRM_Contribute_DAO_ContributionRecur',
 116         'fields' => [
 117           'id' => [
 118             'no_display' => TRUE,
 119             'required' => TRUE,
 120           ],
 121           'currency' => [
 122             'title' => ts("Currency"),
 123             'required' => TRUE,
 124             'no_display' => TRUE,
 125           ],
 126           'contribution_status_id' => [
 127             'title' => ts('Contribution Status'),
 128           ],
 129           'frequency_interval' => [
 130             'title' => ts('Frequency interval'),
 131             'default' => TRUE,
 132           ],
 133           'frequency_unit' => [
 134             'title' => ts('Frequency unit'),
 135             'default' => TRUE,
 136           ],
 137           'amount' => [
 138             'title' => ts('Installment Amount'),
 139             'default' => TRUE,
 140           ],
 141           'installments' => [
 142             'title' => ts('Installments'),
 143             'default' => TRUE,
 144           ],
 145           'start_date' => [
 146             'title' => ts('Start Date'),
 147           ],
 148           'create_date' => [
 149             'title' => ts('Create Date'),
 150           ],
 151           'modified_date' => [
 152             'title' => ts('Modified Date'),
 153           ],
 154           'cancel_date' => [
 155             'title' => ts('Cancel Date'),
 156           ],
 157           'end_date' => [
 158             'title' => ts('End Date'),
 159           ],
 160           'next_sched_contribution_date' => [
 161             'title' => ts('Next Scheduled Contribution Date'),
 162           ],
 163           'failure_count' => [
 164             'title' => ts('Failure Count'),
 165           ],
 166           'failure_retry_date' => [
 167             'title' => ts('Failure Retry Date'),
 168           ],
 169           'payment_processor_id' => [
 170             'title' => ts('Payment Processor'),
 171           ],
 172         ],
 173         'filters' => [
 174           'contribution_status_id' => [
 175             'title' => ts('Contribution Status'),
 176             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 177             'options' => CRM_Contribute_BAO_Contribution::buildOptions('contribution_status_id', 'search'),
 178             'default' => [5],
 179             'type' => CRM_Utils_Type::T_INT,
 180           ],
 181           'currency' => [
 182             'title' => ts('Currency'),
 183             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 184             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 185             'default' => NULL,
 186             'type' => CRM_Utils_Type::T_STRING,
 187           ],
 188           'financial_type_id' => [
 189             'title' => ts('Financial Type'),
 190             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 191             'options' => CRM_Financial_BAO_FinancialType::getAvailableFinancialTypes(),
 192             'type' => CRM_Utils_Type::T_INT,
 193           ],
 194           'frequency_unit' => [
 195             'title' => ts('Frequency Unit'),
 196             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 197             'options' => CRM_Core_OptionGroup::values('recur_frequency_units'),
 198             'type' => CRM_Utils_Type::T_STRING,
 199           ],
 200           'frequency_interval' => [
 201             'title' => ts('Frequency Interval'),
 202             'type' => CRM_Utils_Type::T_INT,
 203           ],
 204           'amount' => [
 205             'title' => ts('Installment Amount'),
 206             'type' => CRM_Utils_Type::T_MONEY,
 207           ],
 208           'installments' => [
 209             'title' => ts('Installments'),
 210             'type' => CRM_Utils_Type::T_INT,
 211           ],
 212           'start_date' => [
 213             'title' => ts('Start Date'),
 214             'operatorType' => CRM_Report_Form::OP_DATE,
 215             'type' => CRM_Utils_Type::T_DATE + CRM_Utils_Type::T_TIME,
 216           ],
 217           'next_sched_contribution_date' => [
 218             'title' => ts('Next Scheduled Contribution Date'),
 219             'operatorType' => CRM_Report_Form::OP_DATE,
 220             'type' => CRM_Utils_Type::T_DATE + CRM_Utils_Type::T_TIME,
 221           ],
 222           'end_date' => [
 223             'title' => ts('End Date'),
 224             'operatorType' => CRM_Report_Form::OP_DATE,
 225             'type' => CRM_Utils_Type::T_DATE + CRM_Utils_Type::T_TIME,
 226           ],
 227           'modified_date' => [
 228             'title' => ts('Last Contribution Processed'),
 229             'operatorType' => CRM_Report_Form::OP_DATE,
 230             'type' => CRM_Utils_Type::T_DATE + CRM_Utils_Type::T_TIME,
 231           ],
 232           'calculated_end_date' => [
 233             'title' => ts('Calculated end date (either end date or date all installments will be made)'),
 234             'description' => "does this work?",
 235             'operatorType' => CRM_Report_Form::OP_DATE,
 236             'pseudofield' => TRUE,
 237           ],
 238           'payment_processor_id' => [
 239             'title' => ts('Payment Processor'),
 240             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 241             'options' => CRM_Contribute_BAO_ContributionRecur::buildOptions('payment_processor_id', 'get'),
 242             'default' => NULL,
 243             'type' => CRM_Utils_Type::T_INT,
 244           ],
 245         ],
 246       ],
 247     ];
 248     $this->_currencyColumn = 'civicrm_contribution_recur_currency';
 249     $this->_groupFilter = TRUE;
 250     parent::__construct();
 251   }
 252
 253   /**
 254    * Get template file name.
 255    *
 256    * @return string
 257    */
 258   public function getTemplateName() {
 259     return 'CRM/Report/Form.tpl';
 260   }
 261
 262   /**
 263    * Generate FROM SQL clause.
 264    */
 265   public function from() {
 266     $this->_from = "
 267       FROM civicrm_contact  {$this->_aliases['civicrm_contact']}
 268         INNER JOIN civicrm_contribution_recur   {$this->_aliases['civicrm_contribution_recur']}
 269           ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution_recur']}.contact_id";
 270     $this->_from .= "
 271       LEFT JOIN civicrm_contribution  {$this->_aliases['civicrm_contribution']}
 272         ON {$this->_aliases['civicrm_contribution_recur']}.id = {$this->_aliases['civicrm_contribution']}.contribution_recur_id";
 273
 274     $this->joinPhoneFromContact();
 275     $this->joinEmailFromContact();
 276
 277     // for credit card type
 278     $this->addFinancialTrxnFromClause();
 279   }
 280
 281   public function groupBy() {
 282     $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_contribution_recur']}.id");
 283   }
 284
 285   public function where() {
 286     parent::where();
 287     // Handle calculated end date. This can come from one of two sources:
 288     // Either there is a specified end date for the end_date field
 289     // Or, we calculate the end date based on the start date +
 290     // installments * intervals using the mysql date_add function, along
 291     // with the interval unit (e.g. DATE_ADD(start_date, INTERVAL 12 * 1 MONTH)
 292     $date_suffixes = ['relative', 'from', 'to'];
 293     foreach ($date_suffixes as $suffix) {
 294       $isBreak = FALSE;
 295       // Check to see if the user wants to search by calculated date.
 296       if (!empty($this->_params['calculated_end_date_' . $suffix])) {
 297         // The calculated date field is in use - spring into action
 298         // Gather values
 299         $relative = $this->_params["calculated_end_date_relative"] ?? NULL;
 300         $from = $this->_params["calculated_end_date_from"] ?? NULL;
 301         $to = $this->_params["calculated_end_date_to"] ?? NULL;
 302         $end_date_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['end_date']['dbAlias'];
 303         $end_date_type = $this->_columns['civicrm_contribution_recur']['filters']['end_date']['type'];
 304         $start_date_type = $this->_columns['civicrm_contribution_recur']['filters']['start_date']['type'];
 305         $frequency_unit_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['frequency_unit']['dbAlias'];
 306         $frequency_interval_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['frequency_interval']['dbAlias'];
 307         $installments_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['installments']['dbAlias'];
 308         $start_date_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['start_date']['dbAlias'];
 309
 310         // The end date clause is simple to construct
 311         $end_date_clause = $this->dateClause($end_date_db_alias, $relative, $from, $to, $end_date_type, NULL, NULL);
 312
 313         // NOTE: For the calculation based on installment, there doesn't
 314         // seem to be a way to include the interval unit (e.g. month,
 315         // date, etc) as a field name - so we have to build a complex
 316         // OR statement instead.
 317
 318         $this->_where .= 'AND (' .
 319           $this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias,1) month)",
 320             $relative, $from, $to, $start_date_type, NULL, NULL);
 321         $this->_where .= " AND $frequency_unit_db_alias = 'month' ) OR \n";
 322
 323         $this->_where .= '(' .
 324           $this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias,1) day)",
 325             $relative, $from, $to, $start_date_type, NULL, NULL);
 326         $this->_where .= " AND $frequency_unit_db_alias = 'day' ) OR \n";
 327
 328         $this->_where .= '(' .
 329           $this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias, 1) week)",
 330             $relative, $from, $to, $start_date_type, NULL, NULL);
 331         $this->_where .= " AND $frequency_unit_db_alias = 'week' ) OR \n";
 332
 333         $this->_where .= '(' .
 334           $this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias, 1) year)",
 335             $relative, $from, $to, $start_date_type, NULL, NULL);
 336         $this->_where .= " AND $frequency_unit_db_alias = 'year' )
 337    AND (($end_date_db_alias IS NOT NULL AND $end_date_clause)
 338     OR ($installments_db_alias IS NOT NULL))
 339 ";
 340         $isBreak = TRUE;
 341       }
 342       if (!empty($this->_params['modified_date_' . $suffix])) {
 343         $this->_where .= " AND {$this->_aliases['civicrm_contribution_recur']}.contribution_status_id = 1";
 344         $isBreak = TRUE;
 345       }
 346       if (!empty($isBreak)) {
 347         break;
 348       }
 349     }
 350   }
 351
 352   /**
 353    * Alter display of rows.
 354    *
 355    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 356    * such as rendering contacts as links.
 357    *
 358    * @param array $rows
 359    *   Rows generated by SQL, with an array for each row.
 360    */
 361   public function alterDisplay(&$rows) {
 362     $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'label');
 363     foreach ($rows as $rowNum => $row) {
 364       // convert display name to links
 365       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 366         CRM_Utils_Array::value('civicrm_contact_sort_name', $rows[$rowNum]) &&
 367         array_key_exists('civicrm_contact_id', $row)
 368       ) {
 369         $url = CRM_Utils_System::url('civicrm/contact/view',
 370           'reset=1&cid=' . $row['civicrm_contact_id'],
 371           $this->_absoluteUrl
 372         );
 373         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 374         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts('View Contact Summary for this Contact.');
 375       }
 376
 377       // handle contribution status id
 378       if ($value = CRM_Utils_Array::value('civicrm_contribution_recur_contribution_status_id', $row)) {
 379         $rows[$rowNum]['civicrm_contribution_recur_contribution_status_id'] = $contributionStatus[$value];
 380       }
 381
 382       if ($value = CRM_Utils_Array::value('civicrm_contribution_recur_amount', $row)) {
 383         $rows[$rowNum]['civicrm_contribution_recur_amount'] = CRM_Utils_Money::format($rows[$rowNum]['civicrm_contribution_recur_amount'], $rows[$rowNum]['civicrm_contribution_recur_currency']);
 384       }
 385
 386       if (!empty($row['civicrm_financial_trxn_card_type_id'])) {
 387         $rows[$rowNum]['civicrm_financial_trxn_card_type_id'] = $this->getLabels($row['civicrm_financial_trxn_card_type_id'], 'CRM_Financial_DAO_FinancialTrxn', 'card_type_id');
 388       }
 389
 390       if (!empty($row['civicrm_contribution_recur_payment_processor_id'])) {
 391         $rows[$rowNum]['civicrm_contribution_recur_payment_processor_id'] = $this->getLabels($row['civicrm_contribution_recur_payment_processor_id'], 'CRM_Contribute_BAO_ContributionRecur', 'payment_processor_id');
 392       }
 393     }
 394   }
 395
 396 }