CRM/Report/Form/Contribute/Recur.php

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