3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2019 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2019
35 class CRM_Report_Form_Contribute_Recur
extends CRM_Report_Form
{
38 * This report has not been optimised for group filtering.
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.
48 protected $groupFilterNotOptimised = TRUE;
53 public function __construct() {
54 $this->_columns
= array(
55 'civicrm_contact' => array(
56 'dao' => 'CRM_Contact_DAO_Contact',
59 'title' => ts("Last name, First name"),
64 'title' => ts('Contact Name'),
74 'civicrm_email' => array(
75 'dao' => 'CRM_Core_DAO_Email',
78 'title' => ts('Email'),
82 'grouping' => 'contact-fields',
84 'civicrm_phone' => array(
85 'dao' => 'CRM_Core_DAO_Phone',
88 'title' => ts('Phone'),
92 'grouping' => 'contact-fields',
94 'civicrm_contribution' => array(
95 'dao' => 'CRM_Contribute_DAO_Contribution',
101 'total_amount' => array(
102 'title' => ts('Amount Contributed to Date'),
103 'statistics' => array(
104 'sum' => ts("Total Amount Contributed"),
109 'civicrm_financial_trxn' => array(
110 'dao' => 'CRM_Financial_DAO_FinancialTrxn',
112 'card_type_id' => array(
113 'title' => ts('Credit Card Type'),
114 'dbAlias' => 'GROUP_CONCAT(financial_trxn_civireport.card_type_id SEPARATOR ",")',
118 'card_type_id' => array(
119 'title' => ts('Credit Card Type'),
120 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
121 'options' => CRM_Financial_DAO_FinancialTrxn
::buildOptions('card_type_id'),
123 'type' => CRM_Utils_Type
::T_STRING
,
127 'civicrm_contribution_recur' => array(
128 'dao' => 'CRM_Contribute_DAO_ContributionRecur',
131 'no_display' => TRUE,
135 'title' => ts("Currency"),
137 'no_display' => TRUE,
139 'contribution_status_id' => array(
140 'title' => ts('Contribution Status'),
142 'frequency_interval' => array(
143 'title' => ts('Frequency interval'),
146 'frequency_unit' => array(
147 'title' => ts('Frequency unit'),
151 'title' => ts('Installment Amount'),
154 'installments' => array(
155 'title' => ts('Installments'),
158 'start_date' => array(
159 'title' => ts('Start Date'),
161 'create_date' => array(
162 'title' => ts('Create Date'),
164 'modified_date' => array(
165 'title' => ts('Modified Date'),
167 'cancel_date' => array(
168 'title' => ts('Cancel Date'),
171 'title' => ts('End Date'),
173 'next_sched_contribution_date' => array(
174 'title' => ts('Next Scheduled Contribution Date'),
176 'failure_count' => array(
177 'title' => ts('Failure Count'),
179 'failure_retry_date' => array(
180 'title' => ts('Failure Retry Date'),
184 'contribution_status_id' => array(
185 'title' => ts('Contribution Status'),
186 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
187 'options' => CRM_Contribute_PseudoConstant
::contributionStatus(),
188 'default' => array(5),
189 'type' => CRM_Utils_Type
::T_INT
,
192 'title' => ts('Currency'),
193 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
194 'options' => CRM_Core_OptionGroup
::values('currencies_enabled'),
196 'type' => CRM_Utils_Type
::T_STRING
,
198 'financial_type_id' => array(
199 'title' => ts('Financial Type'),
200 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
201 'options' => CRM_Financial_BAO_FinancialType
::getAvailableFinancialTypes(),
202 'type' => CRM_Utils_Type
::T_INT
,
204 'frequency_unit' => array(
205 'title' => ts('Frequency Unit'),
206 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
207 'options' => CRM_Core_OptionGroup
::values('recur_frequency_units'),
208 'type' => CRM_Utils_Type
::T_STRING
,
210 'frequency_interval' => array(
211 'title' => ts('Frequency Interval'),
212 'type' => CRM_Utils_Type
::T_INT
,
215 'title' => ts('Installment Amount'),
216 'type' => CRM_Utils_Type
::T_MONEY
,
218 'installments' => array(
219 'title' => ts('Installments'),
220 'type' => CRM_Utils_Type
::T_INT
,
222 'start_date' => array(
223 'title' => ts('Start Date'),
224 'operatorType' => CRM_Report_Form
::OP_DATE
,
225 'type' => CRM_Utils_Type
::T_DATE + CRM_Utils_Type
::T_TIME
,
227 'next_sched_contribution_date' => array(
228 'title' => ts('Next Scheduled Contribution Date'),
229 'operatorType' => CRM_Report_Form
::OP_DATE
,
230 'type' => CRM_Utils_Type
::T_DATE + CRM_Utils_Type
::T_TIME
,
233 'title' => ts('End Date'),
234 'operatorType' => CRM_Report_Form
::OP_DATE
,
235 'type' => CRM_Utils_Type
::T_DATE + CRM_Utils_Type
::T_TIME
,
237 'modified_date' => array(
238 'title' => ts('Last Contribution Processed'),
239 'operatorType' => CRM_Report_Form
::OP_DATE
,
240 'type' => CRM_Utils_Type
::T_DATE + CRM_Utils_Type
::T_TIME
,
242 'calculated_end_date' => array(
243 'title' => ts('Calculated end date (either end date or date all installments will be made)'),
244 'description' => "does this work?",
245 'operatorType' => CRM_Report_Form
::OP_DATE
,
246 'pseudofield' => TRUE,
251 $this->_currencyColumn
= 'civicrm_contribution_recur_currency';
252 $this->_groupFilter
= TRUE;
253 parent
::__construct();
257 * Get template file name.
261 public function getTemplateName() {
262 return 'CRM/Report/Form.tpl';
266 * Generate FROM SQL clause.
268 public function from() {
270 FROM civicrm_contact {$this->_aliases['civicrm_contact']}
271 INNER JOIN civicrm_contribution_recur {$this->_aliases['civicrm_contribution_recur']}
272 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution_recur']}.contact_id";
274 LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
275 ON {$this->_aliases['civicrm_contribution_recur']}.id = {$this->_aliases['civicrm_contribution']}.contribution_recur_id";
277 $this->joinPhoneFromContact();
278 $this->joinEmailFromContact();
280 // for credit card type
281 $this->addFinancialTrxnFromClause();
284 public function groupBy() {
285 $this->_groupBy
= CRM_Contact_BAO_Query
::getGroupByFromSelectColumns($this->_selectClauses
, "{$this->_aliases['civicrm_contribution_recur']}.id");
288 public function where() {
290 // Handle calculated end date. This can come from one of two sources:
291 // Either there is a specified end date for the end_date field
292 // Or, we calculate the end date based on the start date +
293 // installments * intervals using the mysql date_add function, along
294 // with the interval unit (e.g. DATE_ADD(start_date, INTERVAL 12 * 1 MONTH)
295 $date_suffixes = array('relative', 'from', 'to');
296 foreach ($date_suffixes as $suffix) {
298 // Check to see if the user wants to search by calculated date.
299 if (!empty($this->_params
['calculated_end_date_' . $suffix])) {
300 // The calculated date field is in use - spring into action
302 $relative = CRM_Utils_Array
::value("calculated_end_date_relative", $this->_params
);
303 $from = CRM_Utils_Array
::value("calculated_end_date_from", $this->_params
);
304 $to = CRM_Utils_Array
::value("calculated_end_date_to", $this->_params
);
305 $end_date_db_alias = $this->_columns
['civicrm_contribution_recur']['filters']['end_date']['dbAlias'];
306 $end_date_type = $this->_columns
['civicrm_contribution_recur']['filters']['end_date']['type'];
307 $start_date_type = $this->_columns
['civicrm_contribution_recur']['filters']['start_date']['type'];
308 $frequency_unit_db_alias = $this->_columns
['civicrm_contribution_recur']['filters']['frequency_unit']['dbAlias'];
309 $frequency_interval_db_alias = $this->_columns
['civicrm_contribution_recur']['filters']['frequency_interval']['dbAlias'];
310 $installments_db_alias = $this->_columns
['civicrm_contribution_recur']['filters']['installments']['dbAlias'];
311 $start_date_db_alias = $this->_columns
['civicrm_contribution_recur']['filters']['start_date']['dbAlias'];
313 // The end date clause is simple to construct
314 $end_date_clause = $this->dateClause($end_date_db_alias, $relative, $from, $to, $end_date_type, NULL, NULL);
316 // NOTE: For the calculation based on installment, there doesn't
317 // seem to be a way to include the interval unit (e.g. month,
318 // date, etc) as a field name - so we have to build a complex
319 // OR statement instead.
321 $this->_where
.= 'AND (' .
322 $this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias,1) month)",
323 $relative, $from, $to, $start_date_type, NULL, NULL);
324 $this->_where
.= " AND $frequency_unit_db_alias = 'month' ) OR \n";
326 $this->_where
.= '(' .
327 $this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias,1) day)",
328 $relative, $from, $to, $start_date_type, NULL, NULL);
329 $this->_where
.= " AND $frequency_unit_db_alias = 'day' ) OR \n";
331 $this->_where
.= '(' .
332 $this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias, 1) week)",
333 $relative, $from, $to, $start_date_type, NULL, NULL);
334 $this->_where
.= " AND $frequency_unit_db_alias = 'week' ) OR \n";
336 $this->_where
.= '(' .
337 $this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias, 1) year)",
338 $relative, $from, $to, $start_date_type, NULL, NULL);
339 $this->_where
.= " AND $frequency_unit_db_alias = 'year' )
340 AND (($end_date_db_alias IS NOT NULL AND $end_date_clause)
341 OR ($installments_db_alias IS NOT NULL))
345 if (!empty($this->_params
['modified_date_' . $suffix])) {
346 $this->_where
.= " AND {$this->_aliases['civicrm_contribution_recur']}.contribution_status_id = 1";
349 if (!empty($isBreak)) {
357 * Alter display of rows.
359 * Iterate through the rows retrieved via SQL and make changes for display purposes,
360 * such as rendering contacts as links.
363 * Rows generated by SQL, with an array for each row.
365 public function alterDisplay(&$rows) {
366 $contributionStatus = CRM_Contribute_PseudoConstant
::contributionStatus();
367 foreach ($rows as $rowNum => $row) {
368 // convert display name to links
369 if (array_key_exists('civicrm_contact_sort_name', $row) &&
370 CRM_Utils_Array
::value('civicrm_contact_sort_name', $rows[$rowNum]) &&
371 array_key_exists('civicrm_contact_id', $row)
373 $url = CRM_Utils_System
::url('civicrm/contact/view',
374 'reset=1&cid=' . $row['civicrm_contact_id'],
377 $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
378 $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts('View Contact Summary for this Contact.');
381 // handle contribution status id
382 if ($value = CRM_Utils_Array
::value('civicrm_contribution_recur_contribution_status_id', $row)) {
383 $rows[$rowNum]['civicrm_contribution_recur_contribution_status_id'] = $contributionStatus[$value];
386 if ($value = CRM_Utils_Array
::value('civicrm_contribution_recur_amount', $row)) {
387 $rows[$rowNum]['civicrm_contribution_recur_amount'] = CRM_Utils_Money
::format($rows[$rowNum]['civicrm_contribution_recur_amount'], $rows[$rowNum]['civicrm_contribution_recur_currency']);
390 if (!empty($row['civicrm_financial_trxn_card_type_id'])) {
391 $rows[$rowNum]['civicrm_financial_trxn_card_type_id'] = $this->getLabels($row['civicrm_financial_trxn_card_type_id'], 'CRM_Financial_DAO_FinancialTrxn', 'card_type_id');