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