CRM/Report/Form/Contribute/Recur.php

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