3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.6 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2014 |
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-2014
35 class CRM_Report_Form_Contribute_Recur
extends CRM_Report_Form
{
37 public function __construct() {
38 $this->_columns
= array(
39 'civicrm_contact' => array(
40 'dao' => 'CRM_Contact_DAO_Contact',
43 'title' => ts("Last name, First name"),
48 'title' => ts('Contact Name'),
58 'civicrm_email' => array(
59 'dao' => 'CRM_Core_DAO_Email',
62 'title' => ts('Email'),
66 'grouping' => 'contact-fields',
68 'civicrm_phone' => array(
69 'dao' => 'CRM_Core_DAO_Phone',
72 'title' => ts('Phone'),
76 'grouping' => 'contact-fields',
78 'civicrm_contribution' => array(
79 'dao' => 'CRM_Contribute_DAO_Contribution',
85 'total_amount' => array(
86 'title' => ts('Amount Contributed to Date'),
87 'statistics' => array(
88 'sum' => ts("Total Amount Contributed")
93 'civicrm_contribution_recur' => array(
94 'dao' => 'CRM_Contribute_DAO_ContributionRecur',
101 'title' => ts("Currency"),
103 'no_display' => TRUE,
106 'title' => ts('Amount'),
109 'contribution_status_id' => array(
110 'title' => ts('Contribution Status'),
112 'frequency_interval' => array(
113 'title' => ts('Frequency interval'),
116 'frequency_unit' => array(
117 'title' => ts('Frequency unit'),
120 'installments' => array(
121 'title' => ts('Installments'),
124 'start_date' => array(
125 'title' => ts('Start Date'),
127 'create_date' => array(
128 'title' => ts('Create Date'),
130 'modified_date' => array(
131 'title' => ts('Modified Date'),
133 'cancel_date' => array(
134 'title' => ts('Cancel Date'),
137 'title' => ts('End Date'),
139 'next_sched_contribution_date' => array(
140 'title' => ts('Next Scheduled Contribution Date'),
142 'failure_count' => array(
143 'title' => ts('Failure Count'),
145 'failure_retry_date' => array(
146 'title' => ts('Failure Retry Date'),
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
,
158 'title' => 'Currency',
159 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
160 'options' => CRM_Core_OptionGroup
::values('currencies_enabled'),
162 'type' => CRM_Utils_Type
::T_STRING
,
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
,
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
,
176 'frequency_interval' => array(
177 'title' => ts('Frequency Interval'),
178 'type' => CRM_Utils_Type
::T_INT
,
180 'installments' => array(
181 'title' => ts('Installments'),
182 'type' => CRM_Utils_Type
::T_INT
,
184 'start_date' => array(
185 'title' => ts('Start Date'),
186 'operatorType' => CRM_Report_Form
::OP_DATE
,
187 'type' => CRM_Utils_Type
::T_TIME
,
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
,
195 'title' => ts('End Date'),
196 'operatorType' => CRM_Report_Form
::OP_DATE
,
197 'type' => CRM_Utils_Type
::T_TIME
,
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
208 $this->_currencyColumn
= 'civicrm_contribution_recur_currency';
209 parent
::__construct();
211 public function getTemplateName() {
212 return 'CRM/Report/Form.tpl' ;
215 public function from() {
217 FROM civicrm_contact {$this->_aliases['civicrm_contact']}
218 INNER JOIN civicrm_contribution_recur {$this->_aliases['civicrm_contribution_recur']}
219 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution_recur']}.contact_id";
221 LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
222 ON {$this->_aliases['civicrm_contribution_recur']}.id = {$this->_aliases['civicrm_contribution']}.contribution_recur_id";
224 LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
225 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id";
227 LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
228 ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
229 {$this->_aliases['civicrm_phone']}.is_primary = 1)";
232 public function groupBy() {
233 $this->_groupBy
= "GROUP BY " . $this->_aliases
['civicrm_contribution_recur'] . ".id";
236 public function where() {
238 // Handle calculated end date. This can come from one of two sources:
239 // Either there is a specified end date for the end_date field
240 // Or, we calculate the end date based on the start date +
241 // installments * intervals using the mysql date_add function, along
242 // with the interval unit (e.g. DATE_ADD(start_date, INTERVAL 12 * 1 MONTH)
243 $date_suffixes = array('relative', 'from', 'to');
244 while(list(,$suffix) = each($date_suffixes)) {
245 // Check to see if the user wants to search by calculated date.
246 if(!empty($this->_params
['calculated_end_date_' . $suffix])) {
247 // The calculated date field is in use - spring into action
249 $relative = CRM_Utils_Array
::value("calculated_end_date_relative", $this->_params
);
250 $from = CRM_Utils_Array
::value("calculated_end_date_from", $this->_params
);
251 $to = CRM_Utils_Array
::value("calculated_end_date_to", $this->_params
);
252 $end_date_db_alias = $this->_columns
['civicrm_contribution_recur']['filters']['end_date']['dbAlias'];
253 $end_date_type = $this->_columns
['civicrm_contribution_recur']['filters']['end_date']['type'];
254 $start_date_type = $this->_columns
['civicrm_contribution_recur']['filters']['start_date']['type'];
255 $frequency_unit_db_alias = $this->_columns
['civicrm_contribution_recur']['filters']['frequency_unit']['dbAlias'];
256 $frequency_interval_db_alias = $this->_columns
['civicrm_contribution_recur']['filters']['frequency_interval']['dbAlias'];
257 $installments_db_alias = $this->_columns
['civicrm_contribution_recur']['filters']['installments']['dbAlias'];
258 $start_date_db_alias = $this->_columns
['civicrm_contribution_recur']['filters']['start_date']['dbAlias'];
260 // The end date clause is simple to construct
261 $end_date_clause = $this->dateClause($end_date_db_alias, $relative, $from, $to, $end_date_type, NULL, NULL);
263 // NOTE: For the calculation based on installment, there doesn't
264 // seem to be a way to include the interval unit (e.g. month,
265 // date, etc) as a field name - so we have to build a complex
266 // OR statement instead.
268 $installment_clause = '(' .
269 $this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias,1) month)",
270 $relative, $from, $to, $start_date_type, NULL, NULL);
271 $installment_clause .= " AND $frequency_unit_db_alias = 'month' ) OR \n";
273 $installment_clause .= '(' .
274 $this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias,1) day)",
275 $relative, $from, $to, $start_date_type, NULL, NULL);
276 $installment_clause .= " AND $frequency_unit_db_alias = 'day' ) OR \n";
278 $installment_clause .= '(' .
279 $this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias, 1) week)",
280 $relative, $from, $to, $start_date_type, NULL, NULL);
281 $installment_clause .= " AND $frequency_unit_db_alias = 'week' ) OR \n";
283 $installment_clause .= '(' .
284 $this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias, 1) year)",
285 $relative, $from, $to, $start_date_type, NULL, NULL);
286 $installment_clause .= " AND $frequency_unit_db_alias = 'year' )\n";
288 $this->_where
.= " AND ";
289 $this->_where
.= "(";
290 $this->_where
.= "($end_date_db_alias IS NOT NULL AND $end_date_clause)\n";
291 $this->_where
.= " OR \n";
292 $this->_where
.= "($installments_db_alias IS NOT NULL AND ($installment_clause))\n";
293 $this->_where
.= ')';
300 public function alterDisplay(&$rows) {
301 $contributionStatus = CRM_Contribute_PseudoConstant
::contributionStatus();
302 foreach ($rows as $rowNum => $row) {
303 // convert display name to links
304 if (array_key_exists('civicrm_contact_sort_name', $row) &&
305 CRM_Utils_Array
::value('civicrm_contact_sort_name', $rows[$rowNum]) &&
306 array_key_exists('civicrm_contact_id', $row)
308 $url = CRM_Utils_System
::url('civicrm/contact/view',
309 'reset=1&cid=' . $row['civicrm_contact_id'],
312 $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
313 $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts('View Contact Summary for this Contact.');
316 // handle contribution status id
317 if ($value = CRM_Utils_Array
::value('civicrm_contribution_recur_contribution_status_id', $row)) {
318 $rows[$rowNum]['civicrm_contribution_recur_contribution_status_id'] = $contributionStatus[$value];
321 if ($value = CRM_Utils_Array
::value('civicrm_contribution_recur_amount', $row)) {
322 $rows[$rowNum]['civicrm_contribution_recur_amount'] = CRM_Utils_Money
::format($rows[$rowNum]['civicrm_contribution_recur_amount'],$rows[$rowNum]['civicrm_contribution_recur_currency']);