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() {
55 'civicrm_contact' => [
56 'dao' => 'CRM_Contact_DAO_Contact',
59 'title' => ts("Last name, First name"),
64 'title' => ts('Contact Name'),
75 'dao' => 'CRM_Core_DAO_Email',
78 'title' => ts('Email'),
82 'grouping' => 'contact-fields',
85 'dao' => 'CRM_Core_DAO_Phone',
88 'title' => ts('Phone'),
92 'grouping' => 'contact-fields',
94 'civicrm_contribution' => [
95 'dao' => 'CRM_Contribute_DAO_Contribution',
102 'title' => ts('Amount Contributed to Date'),
104 'sum' => ts("Total Amount Contributed"),
109 'civicrm_financial_trxn' => [
110 'dao' => 'CRM_Financial_DAO_FinancialTrxn',
113 'title' => ts('Credit Card Type'),
114 'dbAlias' => 'GROUP_CONCAT(financial_trxn_civireport.card_type_id SEPARATOR ",")',
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' => [
128 'dao' => 'CRM_Contribute_DAO_ContributionRecur',
131 'no_display' => TRUE,
135 'title' => ts("Currency"),
137 'no_display' => TRUE,
139 'contribution_status_id' => [
140 'title' => ts('Contribution Status'),
142 'frequency_interval' => [
143 'title' => ts('Frequency interval'),
146 'frequency_unit' => [
147 'title' => ts('Frequency unit'),
151 'title' => ts('Installment Amount'),
155 'title' => ts('Installments'),
159 'title' => ts('Start Date'),
162 'title' => ts('Create Date'),
165 'title' => ts('Modified Date'),
168 'title' => ts('Cancel Date'),
171 'title' => ts('End Date'),
173 'next_sched_contribution_date' => [
174 'title' => ts('Next Scheduled Contribution Date'),
177 'title' => ts('Failure Count'),
179 'failure_retry_date' => [
180 'title' => ts('Failure Retry Date'),
182 'payment_processor_id' => [
183 'title' => ts('Payment Processor'),
187 'contribution_status_id' => [
188 'title' => ts('Contribution Status'),
189 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
190 'options' => CRM_Contribute_PseudoConstant
::contributionStatus(),
192 'type' => CRM_Utils_Type
::T_INT
,
195 'title' => ts('Currency'),
196 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
197 'options' => CRM_Core_OptionGroup
::values('currencies_enabled'),
199 'type' => CRM_Utils_Type
::T_STRING
,
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
,
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
,
213 'frequency_interval' => [
214 'title' => ts('Frequency Interval'),
215 'type' => CRM_Utils_Type
::T_INT
,
218 'title' => ts('Installment Amount'),
219 'type' => CRM_Utils_Type
::T_MONEY
,
222 'title' => ts('Installments'),
223 'type' => CRM_Utils_Type
::T_INT
,
226 'title' => ts('Start Date'),
227 'operatorType' => CRM_Report_Form
::OP_DATE
,
228 'type' => CRM_Utils_Type
::T_DATE + CRM_Utils_Type
::T_TIME
,
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
,
236 'title' => ts('End Date'),
237 'operatorType' => CRM_Report_Form
::OP_DATE
,
238 'type' => CRM_Utils_Type
::T_DATE + CRM_Utils_Type
::T_TIME
,
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
,
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,
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'),
256 'type' => CRM_Utils_Type
::T_INT
,
261 $this->_currencyColumn
= 'civicrm_contribution_recur_currency';
262 $this->_groupFilter
= TRUE;
263 parent
::__construct();
267 * Get template file name.
271 public function getTemplateName() {
272 return 'CRM/Report/Form.tpl';
276 * Generate FROM SQL clause.
278 public function 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";
284 LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
285 ON {$this->_aliases['civicrm_contribution_recur']}.id = {$this->_aliases['civicrm_contribution']}.contribution_recur_id";
287 $this->joinPhoneFromContact();
288 $this->joinEmailFromContact();
290 // for credit card type
291 $this->addFinancialTrxnFromClause();
294 public function groupBy() {
295 $this->_groupBy
= CRM_Contact_BAO_Query
::getGroupByFromSelectColumns($this->_selectClauses
, "{$this->_aliases['civicrm_contribution_recur']}.id");
298 public function 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) {
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
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'];
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);
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.
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";
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";
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";
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))
355 if (!empty($this->_params
['modified_date_' . $suffix])) {
356 $this->_where
.= " AND {$this->_aliases['civicrm_contribution_recur']}.contribution_status_id = 1";
359 if (!empty($isBreak)) {
367 * Alter display of rows.
369 * Iterate through the rows retrieved via SQL and make changes for display purposes,
370 * such as rendering contacts as links.
373 * Rows generated by SQL, with an array for each row.
375 public function alterDisplay(&$rows) {
376 $contributionStatus = CRM_Contribute_PseudoConstant
::contributionStatus();
377 foreach ($rows as $rowNum => $row) {
378 // convert display name to links
379 if (array_key_exists('civicrm_contact_sort_name', $row) &&
380 CRM_Utils_Array
::value('civicrm_contact_sort_name', $rows[$rowNum]) &&
381 array_key_exists('civicrm_contact_id', $row)
383 $url = CRM_Utils_System
::url('civicrm/contact/view',
384 'reset=1&cid=' . $row['civicrm_contact_id'],
387 $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
388 $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts('View Contact Summary for this Contact.');
391 // handle contribution status id
392 if ($value = CRM_Utils_Array
::value('civicrm_contribution_recur_contribution_status_id', $row)) {
393 $rows[$rowNum]['civicrm_contribution_recur_contribution_status_id'] = $contributionStatus[$value];
396 if ($value = CRM_Utils_Array
::value('civicrm_contribution_recur_amount', $row)) {
397 $rows[$rowNum]['civicrm_contribution_recur_amount'] = CRM_Utils_Money
::format($rows[$rowNum]['civicrm_contribution_recur_amount'], $rows[$rowNum]['civicrm_contribution_recur_currency']);
400 if (!empty($row['civicrm_financial_trxn_card_type_id'])) {
401 $rows[$rowNum]['civicrm_financial_trxn_card_type_id'] = $this->getLabels($row['civicrm_financial_trxn_card_type_id'], 'CRM_Financial_DAO_FinancialTrxn', 'card_type_id');
404 if (!empty($row['civicrm_contribution_recur_payment_processor_id'])) {
405 $rows[$rowNum]['civicrm_contribution_recur_payment_processor_id'] = $this->getLabels($row['civicrm_contribution_recur_payment_processor_id'], 'CRM_Contribute_BAO_ContributionRecur', 'payment_processor_id');