CRM/Report/Form/Contribute/Recur.php

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