3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.7 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2016 |
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-2016
33 class CRM_Report_Form_Contribute_Repeat
extends CRM_Report_Form
{
34 protected $_amountClauseWithAND = NULL;
36 protected $_customGroupExtends = array(
41 public $_drilldownReport = array('contribute/detail' => 'Link to Detail Report');
44 * Temp table for first time frame.
48 protected $tempTableRepeat1 = NULL;
51 * Temp table for second time frame.
55 protected $tempTableRepeat2 = NULL;
58 * The table the report is being grouped by.
62 protected $groupByTable;
65 * The field the report is being grouped by.
69 protected $groupByFieldName;
72 * The alias of the table the report is being grouped by.
76 protected $groupByTableAlias;
79 * The column in the contribution table that joins to the temp tables.
83 protected $contributionJoinTableColumn;
86 * This report has been optimised for group filtering.
92 protected $groupFilterNotOptimised = FALSE;
97 public function __construct() {
98 $this->_columns
= array(
99 'civicrm_contact' => array(
100 'dao' => 'CRM_Contact_DAO_Contact',
101 'grouping' => 'contact-fields',
103 'sort_name' => array(
104 'title' => ts('Contact Name'),
108 'display_name' => array(
109 'title' => ts('Display Name'),
112 'addressee_display' => array(
113 'title' => ts('Addressee Name'),
117 'no_display' => TRUE,
120 'contact_type' => array(
121 'title' => ts('Contact Type'),
124 'contact_sub_type' => array(
125 'title' => ts('Contact Subtype'),
130 'percentage_change' => array(
131 'title' => ts('Percentage Change'),
132 'type' => CRM_Utils_Type
::T_INT
,
133 'operatorType' => CRM_Report_Form
::OP_INT
,
134 'name' => 'percentage_change',
135 'dbAlias' => '( ( contribution_civireport2.total_amount_sum - contribution_civireport1.total_amount_sum ) * 100 / contribution_civireport1.total_amount_sum )',
138 'group_bys' => array(
140 'title' => ts('Contact'),
144 'order_bys' => array(
145 'sort_name' => array(
146 'title' => ts('Last Name, First Name'),
148 'default_weight' => '0',
149 'default_order' => 'ASC',
151 'first_name' => array(
152 'title' => ts('First Name'),
154 'gender_id' => array(
155 'name' => 'gender_id',
156 'title' => ts('Gender'),
158 'birth_date' => array(
159 'name' => 'birth_date',
160 'title' => ts('Birth Date'),
162 'contact_type' => array(
163 'title' => ts('Contact Type'),
165 'contact_sub_type' => array(
166 'title' => ts('Contact Subtype'),
170 'civicrm_email' => array(
171 'dao' => 'CRM_Core_DAO_Email',
174 'title' => ts('Email'),
178 'grouping' => 'contact-fields',
180 'civicrm_phone' => array(
181 'dao' => 'CRM_Core_DAO_Phone',
184 'title' => ts('Phone'),
188 'grouping' => 'contact-fields',
190 'civicrm_address' => array(
191 'dao' => 'CRM_Core_DAO_Address',
192 'grouping' => 'contact-fields',
194 'street_address' => array('title' => ts('Street Address')),
195 'supplemental_address_1' => array('title' => ts('Supplemental Address 1')),
196 'city' => array('title' => ts('City')),
197 'country_id' => array('title' => ts('Country')),
198 'state_province_id' => array('title' => ts('State/Province')),
199 'postal_code' => array('title' => ts('Postal Code')),
201 'group_bys' => array(
202 'country_id' => array('title' => ts('Country')),
203 'state_province_id' => array(
204 'title' => ts('State/Province'),
208 'civicrm_financial_type' => array(
209 'dao' => 'CRM_Financial_DAO_FinancialType',
210 'fields' => array('financial_type' => array('title' => ts('Financial Type'))),
211 'grouping' => 'contri-fields',
212 'group_bys' => array(
213 'financial_type' => array(
215 'title' => ts('Financial Type'),
219 'civicrm_contribution' => array(
220 'dao' => 'CRM_Contribute_DAO_Contribution',
222 'contribution_source' => NULL,
223 'total_amount1' => array(
224 'name' => 'total_amount',
225 'alias' => 'contribution1',
226 'title' => ts('Range One Stat'),
227 'type' => CRM_Utils_Type
::T_MONEY
,
230 'clause' => 'contribution_civireport1.total_amount_count as contribution1_total_amount_count, contribution_civireport1.total_amount_sum as contribution1_total_amount_sum',
232 'total_amount2' => array(
233 'name' => 'total_amount',
234 'alias' => 'contribution2',
235 'title' => ts('Range Two Stat'),
236 'type' => CRM_Utils_Type
::T_MONEY
,
239 'clause' => 'contribution_civireport2.total_amount_count as contribution2_total_amount_count, contribution_civireport2.total_amount_sum as contribution2_total_amount_sum',
242 'grouping' => 'contri-fields',
244 'receive_date1' => array(
245 'title' => ts('Initial Date Range'),
246 'default' => 'previous.year',
247 'operatorType' => CRM_Report_Form
::OP_DATE
,
248 'name' => 'receive_date',
250 'receive_date2' => array(
251 'title' => ts('Second Date Range'),
252 'default' => 'this.year',
253 'operatorType' => CRM_Report_Form
::OP_DATE
,
254 'name' => 'receive_date',
256 'total_amount1' => array(
257 'title' => ts('Range One Amount'),
258 'type' => CRM_Utils_Type
::T_INT
,
259 'operatorType' => CRM_Report_Form
::OP_INT
,
260 'name' => 'total_amount',
262 'total_amount2' => array(
263 'title' => ts('Range Two Amount'),
264 'type' => CRM_Utils_Type
::T_INT
,
265 'operatorType' => CRM_Report_Form
::OP_INT
,
266 'name' => 'total_amount',
268 'financial_type_id' => array(
269 'title' => ts('Financial Type'),
270 'type' => CRM_Utils_Type
::T_INT
,
271 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
272 'options' => CRM_Financial_BAO_FinancialType
::getAvailableFinancialTypes(),
274 'contribution_status_id' => array(
275 'title' => ts('Contribution Status'),
276 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
277 'options' => CRM_Contribute_PseudoConstant
::contributionStatus(),
278 'default' => array('1'),
281 'group_bys' => array('contribution_source' => NULL),
285 $this->_groupFilter
= TRUE;
286 $this->_tagFilter
= TRUE;
288 parent
::__construct();
292 * Override parent select for reasons someone will someday make sense of & document.
294 public function select() {
297 // since contact fields not related to financial type
298 if (array_key_exists('financial_type', $this->_params
['group_bys']) ||
299 array_key_exists('contribution_source', $this->_params
['group_bys'])
301 unset($this->_columns
['civicrm_contact']['fields']['id']);
304 foreach ($this->_columns
as $tableName => $table) {
305 if (array_key_exists('fields', $table)) {
306 foreach ($table['fields'] as $fieldName => $field) {
307 if (!empty($field['required']) ||
308 !empty($this->_params
['fields'][$fieldName])
310 if (isset($field['clause'])) {
311 $select[] = $field['clause'];
313 // FIXME: dirty hack for setting columnHeaders
314 $this->_columnHeaders
["{$field['alias']}_{$field['name']}_sum"]['type'] = CRM_Utils_Array
::value('type', $field);
315 $this->_columnHeaders
["{$field['alias']}_{$field['name']}_sum"]['title'] = $field['title'];
316 $this->_columnHeaders
["{$field['alias']}_{$field['name']}_count"]['type'] = CRM_Utils_Array
::value('type', $field);
317 $this->_columnHeaders
["{$field['alias']}_{$field['name']}_count"]['title'] = $field['title'];
321 // only include statistics columns if set
322 $select[] = "{$field['dbAlias']} as {$field['alias']}_{$field['name']}";
323 $this->_columnHeaders
["{$field['alias']}_{$field['name']}"]['type'] = CRM_Utils_Array
::value('type', $field);
324 $this->_columnHeaders
["{$field['alias']}_{$field['name']}"]['title'] = CRM_Utils_Array
::value('title', $field);
325 if (!empty($field['no_display'])) {
326 $this->_columnHeaders
["{$field['alias']}_{$field['name']}"]['no_display'] = TRUE;
332 $this->_selectClauses
= $select;
333 $this->_select
= "SELECT " . implode(', ', $select) . " ";
337 * Inspect the group by params to determine group by information.
339 public function setGroupByInformation() {
340 if (!empty($this->_params
['group_bys']) &&
341 is_array($this->_params
['group_bys'])
343 foreach ($this->_columns
as $tableName => $table) {
344 if (array_key_exists('group_bys', $table)) {
345 foreach ($table['group_bys'] as $fieldName => $field) {
346 if (!empty($this->_params
['group_bys'][$fieldName])) {
347 $this->groupByTable
= $tableName;
348 $this->groupByTableAlias
= $field['alias'];
349 $this->groupByFieldName
= $field['name'];
350 if ($this->groupByTable
== 'civicrm_contact') {
351 $this->contributionJoinTableColumn
= "contact_id";
353 elseif ($this->groupByTable
== 'civicrm_contribution_type') {
354 $this->contributionJoinTableColumn
= "contribution_type_id";
356 elseif ($this->groupByTable
== 'civicrm_contribution') {
357 $this->contributionJoinTableColumn
= $this->groupByFieldName
;
359 elseif ($this->groupByTable
== 'civicrm_address') {
360 $this->contributionJoinTableColumn
= "contact_id";
362 elseif ($this->groupByTable
== 'civicrm_financial_type') {
363 $this->contributionJoinTableColumn
= 'financial_type_id';
374 public function from() {
375 $this->buildTempTables();
376 $fromCol = $this->groupByFieldName
;
378 $from = "$this->groupByTable $this->groupByTableAlias";
380 if ($this->groupByTable
== 'civicrm_contact') {
382 LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']} ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id
383 LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
384 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND {$this->_aliases['civicrm_email']}.is_primary = 1
385 LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
386 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND {$this->_aliases['civicrm_phone']}.is_primary = 1";
389 elseif ($this->groupByTable
== 'civicrm_address') {
391 INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']} ON {$this->_aliases['civicrm_address']}.contact_id = {$this->_aliases['civicrm_contact']}.id";
392 $this->groupByTableAlias
= $this->_aliases
['civicrm_contact'];
398 LEFT JOIN $this->tempTableRepeat1 {$this->_aliases['civicrm_contribution']}1
399 ON {$this->groupByTableAlias}.$fromCol = {$this->_aliases['civicrm_contribution']}1
400 .{$this->contributionJoinTableColumn}
401 LEFT JOIN $this->tempTableRepeat2 {$this->_aliases['civicrm_contribution']}2
402 ON {$this->groupByTableAlias}.$fromCol = {$this->_aliases['civicrm_contribution']}2.{$this->contributionJoinTableColumn}";
406 * @param string $replaceAliasWith
408 * @return mixed|string
410 public function fromContribution($replaceAliasWith = 'contribution1') {
411 $this->setFromBase('civicrm_contribution', 'contact_id', $replaceAliasWith);
413 $temp = $this->_aliases
['civicrm_contribution'];
414 $this->_aliases
['civicrm_contribution'] = $replaceAliasWith;
415 $from = $this->_from
;
416 $from .= (string) $this->getPermissionedFTQuery($this, 'civicrm_line_item_report', TRUE);
417 $this->_aliases
['civicrm_contribution'] = $temp;
423 * @param string $replaceAliasWith
425 * @return mixed|string
427 public function whereContribution($replaceAliasWith = 'contribution1') {
428 $clauses = array("is_test" => "{$this->_aliases['civicrm_contribution']}.is_test = 0");
430 foreach ($this->_columns
['civicrm_contribution']['filters'] as $fieldName => $field) {
432 if (CRM_Utils_Array
::value('type', $field) & CRM_Utils_Type
::T_DATE
) {
433 $relative = CRM_Utils_Array
::value("{$fieldName}_relative", $this->_params
);
434 $from = CRM_Utils_Array
::value("{$fieldName}_from", $this->_params
);
435 $to = CRM_Utils_Array
::value("{$fieldName}_to", $this->_params
);
437 $clause = $this->dateClause($field['dbAlias'], $relative, $from, $to, $field['type']);
440 $op = CRM_Utils_Array
::value("{$fieldName}_op", $this->_params
);
442 $clause = $this->whereClause($field,
444 CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
),
445 CRM_Utils_Array
::value("{$fieldName}_min", $this->_params
),
446 CRM_Utils_Array
::value("{$fieldName}_max", $this->_params
)
450 if (!empty($clause)) {
451 $clauses[$fieldName] = $clause;
455 if (!$this->_amountClauseWithAND
) {
456 $amountClauseWithAND = array();
457 if (!empty($clauses['total_amount1'])) {
458 $amountClauseWithAND[] = str_replace("{$this->_aliases['civicrm_contribution']}.total_amount",
459 "{$this->_aliases['civicrm_contribution']}1.total_amount_sum", $clauses['total_amount1']);
461 if (!empty($clauses['total_amount2'])) {
462 $amountClauseWithAND[] = str_replace("{$this->_aliases['civicrm_contribution']}.total_amount",
463 "{$this->_aliases['civicrm_contribution']}2.total_amount_sum", $clauses['total_amount2']);
465 $this->_amountClauseWithAND
= !empty($amountClauseWithAND) ?
implode(' AND ', $amountClauseWithAND) : NULL;
468 if ($replaceAliasWith == 'contribution1') {
469 unset($clauses['receive_date2'], $clauses['total_amount2']);
472 unset($clauses['receive_date1'], $clauses['total_amount1']);
475 $whereClause = !empty($clauses) ?
"WHERE " . implode(' AND ', $clauses) : '';
477 if ($replaceAliasWith) {
478 $whereClause = str_replace($this->_aliases
['civicrm_contribution'], $replaceAliasWith, $whereClause);
484 public function where() {
485 if (!$this->_amountClauseWithAND
) {
486 $this->_amountClauseWithAND
487 = "!({$this->_aliases['civicrm_contribution']}1.total_amount_count IS NULL AND {$this->_aliases['civicrm_contribution']}2.total_amount_count IS NULL)";
489 $clauses = array("atleast_one_amount" => $this->_amountClauseWithAND
);
491 foreach ($this->_columns
as $tableName => $table) {
492 if (array_key_exists('filters', $table) &&
493 $tableName != 'civicrm_contribution'
495 foreach ($table['filters'] as $fieldName => $field) {
497 $op = CRM_Utils_Array
::value("{$fieldName}_op", $this->_params
);
499 $clause = $this->whereClause($field,
501 CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
),
502 CRM_Utils_Array
::value("{$fieldName}_min", $this->_params
),
503 CRM_Utils_Array
::value("{$fieldName}_max", $this->_params
)
506 if (!empty($clause)) {
507 $clauses[$fieldName] = $clause;
513 $this->_where
= !empty($clauses) ?
"WHERE " . implode(' AND ', $clauses) : '';
523 public static function formRule($fields, $files, $self) {
525 $errors = $checkDate = $errorCount = array();
540 'supplemental_address_1',
543 'country_id' => array('country_id'),
544 'state_province_id' => array('country_id', 'state_province_id'),
545 'contribution_source' => array('contribution_source'),
546 'financial_type' => array('financial_type'),
550 'id' => ts('Contact'),
551 'exposed_id' => ts('Contact'),
552 'country_id' => ts('Country'),
553 'state_province_id' => ts('State/Province'),
554 'contribution_source' => ts('Contribution Source'),
555 'financial_type' => ts('Financial Type'),
556 'sort_name' => ts('Contact Name'),
557 'email' => ts('Email'),
558 'phone' => ts('Phone'),
561 if (empty($fields['group_bys'])) {
562 $errors['fields'] = ts('Please select at least one Group by field.');
564 elseif ((array_key_exists('contribution_source', $fields['group_bys']) ||
565 array_key_exists('contribution_type', $fields['group_bys'])
567 (count($fields['group_bys']) > 1)
569 $errors['fields'] = ts('You can not use other Group by with Financial type or Contribution source.');
572 foreach ($fields['fields'] as $fld_id => $value) {
573 if (!($fld_id == 'total_amount1') && !($fld_id == 'total_amount2')) {
575 $invlidGroups = array();
576 foreach ($fields['group_bys'] as $grp_id => $val) {
577 $validFields = $rules[$grp_id];
578 if (in_array($fld_id, $validFields)) {
582 $invlidGroups[] = $idMapping[$grp_id];
586 $erorrGrps = implode(',', $invlidGroups);
587 $tempErrors[] = ts("Do not select field %1 with Group by %2.", array(
588 1 => $idMapping[$fld_id],
594 if (!empty($tempErrors)) {
595 $errors['fields'] = implode("<br/>", $tempErrors);
599 if (!empty($fields['gid_value']) && !empty($fields['group_bys'])) {
600 if (!array_key_exists('id', $fields['group_bys'])) {
601 $errors['gid_value'] = ts("Filter with Group only allow with group by Contact");
605 if ($fields['receive_date1_relative'] == '0') {
606 $checkDate['receive_date1']['receive_date1_from'] = $fields['receive_date1_from'];
607 $checkDate['receive_date1']['receive_date1_to'] = $fields['receive_date1_to'];
610 if ($fields['receive_date2_relative'] == '0') {
611 $checkDate['receive_date2']['receive_date2_from'] = $fields['receive_date2_from'];
612 $checkDate['receive_date2']['receive_date2_to'] = $fields['receive_date2_to'];
615 foreach ($checkDate as $date_range => $range_data) {
616 foreach ($range_data as $key => $value) {
617 if (CRM_Utils_Date
::isDate($value)) {
618 $errorCount[$date_range][$key]['valid'] = 'true';
619 $errorCount[$date_range][$key]['is_empty'] = 'false';
622 $errorCount[$date_range][$key]['valid'] = 'false';
623 $errorCount[$date_range][$key]['is_empty'] = 'true';
624 if (is_array($value)) {
625 foreach ($value as $v) {
627 $errorCount[$date_range][$key]['is_empty'] = 'false';
631 elseif (!isset($value)) {
632 $errorCount[$date_range][$key]['is_empty'] = 'false';
638 $errorText = ts("Select valid date range");
639 foreach ($errorCount as $date_range => $error_data) {
641 if (($error_data[$date_range . '_from']['valid'] == 'false') &&
642 ($error_data[$date_range . '_to']['valid'] == 'false')
645 if (($error_data[$date_range . '_from']['is_empty'] == 'true') &&
646 ($error_data[$date_range . '_to']['is_empty'] == 'true')
648 $errors[$date_range . '_relative'] = $errorText;
651 if ($error_data[$date_range . '_from']['is_empty'] == 'false') {
652 $errors[$date_range . '_from'] = $errorText;
655 if ($error_data[$date_range . '_to']['is_empty'] == 'false') {
656 $errors[$date_range . '_to'] = $errorText;
659 elseif (($error_data[$date_range . '_from']['valid'] == 'true') &&
660 ($error_data[$date_range . '_to']['valid'] == 'false')
662 if ($error_data[$date_range . '_to']['is_empty'] == 'false') {
663 $errors[$date_range . '_to'] = $errorText;
666 elseif (($error_data[$date_range . '_from']['valid'] == 'false') &&
667 ($error_data[$date_range . '_to']['valid'] == 'true')
669 if ($error_data[$date_range . '_from']['is_empty'] == 'false') {
670 $errors[$date_range . '_from'] = $errorText;
683 public function statistics(&$rows) {
684 $statistics = parent
::statistics($rows);
685 $sql = "{$this->_select} {$this->_from} {$this->_where}";
686 $dao = CRM_Core_DAO
::executeQuery($sql);
687 //store contributions in array 'contact_sums' for comparison
688 $contact_sums = array();
689 while ($dao->fetch()) {
690 $contact_sums[$dao->contact_civireport_id
] = array(
691 'contribution1_total_amount_sum' => $dao->contribution1_total_amount_sum
,
692 'contribution2_total_amount_sum' => $dao->contribution2_total_amount_sum
,
696 $total_distinct_contacts = count($contact_sums);
703 foreach ($contact_sums as $uid => $row) {
704 if ($row['contribution1_total_amount_sum'] &&
705 $row['contribution2_total_amount_sum']
707 $change = ($row['contribution1_total_amount_sum'] -
708 $row['contribution2_total_amount_sum']);
712 elseif ($change > 0) {
715 elseif ($change < 0) {
719 elseif ($row['contribution1_total_amount_sum']) {
722 elseif ($row['contribution2_total_amount_sum']) {
727 //calculate percentages from numbers
728 if (!empty($total_distinct_contacts)) {
729 $maintained = ($maintained / $total_distinct_contacts) * 100;
730 $upgraded = ($upgraded / $total_distinct_contacts) * 100;
731 $downgraded = ($downgraded / $total_distinct_contacts) * 100;
732 $new = ($new / $total_distinct_contacts) * 100;
733 $lapsed = ($lapsed / $total_distinct_contacts) * 100;
735 //display percentages for new, lapsed, upgraded, downgraded, and maintained contributors
736 $statistics['counts']['count_new'] = array(
738 'title' => ts('% New Donors'),
740 $statistics['counts']['count_lapsed'] = array(
742 'title' => ts('% Lapsed Donors'),
744 $statistics['counts']['count_upgraded'] = array(
745 'value' => $upgraded,
746 'title' => ts('% Upgraded Donors'),
748 $statistics['counts']['count_downgraded'] = array(
749 'value' => $downgraded,
750 'title' => ts('% Downgraded Donors'),
752 $statistics['counts']['count_maintained'] = array(
753 'value' => $maintained,
754 'title' => ts('% Maintained Donors'),
758 SELECT COUNT({$this->_aliases['civicrm_contribution']}1.total_amount_count ) as count,
759 SUM({$this->_aliases['civicrm_contribution']}1.total_amount_sum ) as amount,
760 ROUND(AVG({$this->_aliases['civicrm_contribution']}1.total_amount_sum), 2) as avg,
761 COUNT({$this->_aliases['civicrm_contribution']}2.total_amount_count ) as count2,
762 SUM({$this->_aliases['civicrm_contribution']}2.total_amount_sum ) as amount2,
763 ROUND(AVG({$this->_aliases['civicrm_contribution']}2.total_amount_sum), 2) as avg2,
765 $sql = "{$select} {$this->_from} {$this->_where}
768 $dao = CRM_Core_DAO
::executeQuery($sql);
770 $amount = $average = $amount2 = $average2 = array();
771 $count = $count2 = 0;
772 while ($dao->fetch()) {
775 = CRM_Utils_Money
::format($dao->amount
, $dao->currency
) . "(" .
777 $average[] = CRM_Utils_Money
::format($dao->avg
, $dao->currency
);
780 $count +
= $dao->count
;
783 = CRM_Utils_Money
::format($dao->amount2
, $dao->currency
) . "(" .
785 $average2[] = CRM_Utils_Money
::format($dao->avg2
, $dao->currency
);
787 $count2 +
= $dao->count2
;
790 $statistics['counts']['range_one_title'] = array('title' => ts('Initial Date Range:'));
791 $statistics['counts']['amount'] = array(
792 'value' => implode(', ', $amount),
793 'title' => ts('Total Amount'),
794 'type' => CRM_Utils_Type
::T_STRING
,
796 $statistics['counts']['count'] = array(
798 'title' => ts('Total Donations'),
800 $statistics['counts']['avg'] = array(
801 'value' => implode(', ', $average),
802 'title' => ts('Average'),
803 'type' => CRM_Utils_Type
::T_STRING
,
805 $statistics['counts']['range_two_title'] = array(
806 'title' => ts('Second Date Range:'),
808 $statistics['counts']['amount2'] = array(
809 'value' => implode(', ', $amount2),
810 'title' => ts('Total Amount'),
811 'type' => CRM_Utils_Type
::T_STRING
,
813 $statistics['counts']['count2'] = array(
815 'title' => ts('Total Donations'),
817 $statistics['counts']['avg2'] = array(
818 'value' => implode(', ', $average2),
819 'title' => ts('Average'),
820 'type' => CRM_Utils_Type
::T_STRING
,
826 public function postProcess() {
827 $this->beginPostProcess();
836 $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_limit}";
837 $dao = CRM_Core_DAO
::executeQuery($sql);
839 while ($dao->fetch()) {
840 foreach ($this->_columnHeaders
as $key => $value) {
841 $rows[$count][$key] = $dao->$key;
846 // FIXME: calculate % using query
847 foreach ($rows as $uid => $row) {
848 if ($row['contribution1_total_amount_sum'] &&
849 $row['contribution2_total_amount_sum']
851 $rows[$uid]['change'] = number_format((($row['contribution2_total_amount_sum'] -
852 $row['contribution1_total_amount_sum']
854 ($row['contribution1_total_amount_sum']), 2
857 elseif ($row['contribution1_total_amount_sum']) {
858 $rows[$uid]['change'] = ts('Skipped Donation');
860 elseif ($row['contribution2_total_amount_sum']) {
861 $rows[$uid]['change'] = ts('New Donor');
863 if ($row['contribution1_total_amount_count']) {
864 $rows[$uid]['contribution1_total_amount_sum']
865 = $row['contribution1_total_amount_sum'] .
866 " ({$row['contribution1_total_amount_count']})";
868 if ($row['contribution2_total_amount_count']) {
869 $rows[$uid]['contribution2_total_amount_sum']
870 = $row['contribution2_total_amount_sum'] .
871 " ({$row['contribution2_total_amount_count']})";
874 $this->_columnHeaders
['change'] = array(
875 'title' => ts('% Change'),
876 'type' => CRM_Utils_Type
::T_INT
,
880 list($from1, $to1) = $this->getFromTo(CRM_Utils_Array
::value("receive_date1_relative", $this->_params
),
881 CRM_Utils_Array
::value("receive_date1_from", $this->_params
),
882 CRM_Utils_Array
::value("receive_date1_to", $this->_params
)
884 $from1 = CRM_Utils_Date
::customFormat($from1, NULL, array('d'));
885 $to1 = CRM_Utils_Date
::customFormat($to1, NULL, array('d'));
887 list($from2, $to2) = $this->getFromTo(CRM_Utils_Array
::value("receive_date2_relative", $this->_params
),
888 CRM_Utils_Array
::value("receive_date2_from", $this->_params
),
889 CRM_Utils_Array
::value("receive_date2_to", $this->_params
)
891 $from2 = CRM_Utils_Date
::customFormat($from2, NULL, array('d'));
892 $to2 = CRM_Utils_Date
::customFormat($to2, NULL, array('d'));
894 $this->_columnHeaders
['contribution1_total_amount_sum']['title'] = "$from1 -<br/> $to1";
895 $this->_columnHeaders
['contribution2_total_amount_sum']['title'] = "$from2 -<br/> $to2";
896 unset($this->_columnHeaders
['contribution1_total_amount_count'],
897 $this->_columnHeaders
['contribution2_total_amount_count']
900 $this->formatDisplay($rows);
902 // assign variables to templates
903 $this->doTemplateAssignment($rows);
905 $this->endPostProcess($rows);
909 * Alter display of rows.
911 * Iterate through the rows retrieved via SQL and make changes for display purposes,
912 * such as rendering contacts as links.
915 * Rows generated by SQL, with an array for each row.
917 public function alterDisplay(&$rows) {
918 list($from1, $to1) = $this->getFromTo(CRM_Utils_Array
::value("receive_date1_relative", $this->_params
),
919 CRM_Utils_Array
::value("receive_date1_from", $this->_params
),
920 CRM_Utils_Array
::value("receive_date1_to", $this->_params
)
922 list($from2, $to2) = $this->getFromTo(CRM_Utils_Array
::value("receive_date2_relative", $this->_params
),
923 CRM_Utils_Array
::value("receive_date2_from", $this->_params
),
924 CRM_Utils_Array
::value("receive_date2_to", $this->_params
)
929 $dateUrl .= "receive_date1_from={$from1}&";
932 $dateUrl .= "receive_date1_to={$to1}&";
935 $dateUrl .= "receive_date2_from={$from2}&";
938 $dateUrl .= "receive_date2_to={$to2}&";
941 foreach ($rows as $rowNum => $row) {
943 if (array_key_exists('address_civireport_country_id', $row)) {
944 if ($value = $row['address_civireport_country_id']) {
945 $rows[$rowNum]['address_civireport_country_id'] = CRM_Core_PseudoConstant
::country($value, FALSE);
947 $url = CRM_Report_Utils_Report
::getNextUrl('contribute/detail',
949 "country_id_op=in&country_id_value={$value}&" .
951 $this->_absoluteUrl
, $this->_id
, $this->_drilldownReport
954 $rows[$rowNum]['address_civireport_country_id_link'] = $url;
955 $rows[$rowNum]['address_civireport_country_id_hover'] = ts("View contributions for this Country.");
959 // handle state province
960 if (array_key_exists('address_civireport_state_province_id', $row)) {
961 if ($value = $row['address_civireport_state_province_id']) {
962 $rows[$rowNum]['address_civireport_state_province_id'] = CRM_Core_PseudoConstant
::stateProvince($value, FALSE);
964 $url = CRM_Report_Utils_Report
::getNextUrl('contribute/detail',
966 "state_province_id_op=in&state_province_id_value={$value}&" .
968 $this->_absoluteUrl
, $this->_id
, $this->_drilldownReport
971 $rows[$rowNum]['address_civireport_state_province_id_link'] = $url;
972 $rows[$rowNum]['address_civireport_state_province_id_hover'] = ts("View repeatDetails for this state.");
976 // convert display name to links
977 if (array_key_exists('contact_civireport_sort_name', $row) &&
978 array_key_exists('contact_civireport_id', $row)
980 $url = CRM_Report_Utils_Report
::getNextUrl('contribute/detail',
981 'reset=1&force=1&id_op=eq&id_value=' . $row['contact_civireport_id'],
982 $this->_absoluteUrl
, $this->_id
, $this->_drilldownReport
985 $rows[$rowNum]['contact_civireport_sort_name_link'] = $url;
986 $rows[$rowNum]['contact_civireport_sort_name_hover'] = ts("View Contribution details for this contact");
993 * Build the temp tables for comparison.
995 protected function buildTempTables() {
996 $this->setGroupByInformation();
997 $create = $subSelect1 = $subSelect2 = NULL;
998 if ($this->tempTableRepeat1
) {
1002 if ($this->groupByTable
== 'civicrm_financial_type') {
1003 $subSelect1 = 'contribution1.contact_id,';
1004 $subSelect2 = 'contribution2.contact_id,';
1005 $create = 'contact_id int unsigned,';
1008 $subWhere = $this->whereContribution();
1009 $from = $this->fromContribution();
1010 $subContributionQuery1 = "
1011 SELECT {$subSelect1} contribution1.{$this->contributionJoinTableColumn},
1012 sum( contribution1.total_amount ) AS total_amount_sum,
1013 count( * ) AS total_amount_count
1016 GROUP BY contribution1.{$this->contributionJoinTableColumn}";
1018 $subWhere = $this->whereContribution('contribution2');
1019 $from = $this->fromContribution('contribution2');
1020 $subContributionQuery2 = "
1021 SELECT {$subSelect2} contribution2.{$this->contributionJoinTableColumn},
1022 sum( contribution2.total_amount ) AS total_amount_sum,
1023 count( * ) AS total_amount_count,
1027 GROUP BY contribution2.{$this->contributionJoinTableColumn}, currency";
1028 $this->tempTableRepeat1
= 'civicrm_temp_civireport_repeat1' . uniqid();
1030 CREATE TEMPORARY TABLE $this->tempTableRepeat1 (
1032 {$this->contributionJoinTableColumn} int unsigned,
1033 total_amount_sum int,
1034 total_amount_count int
1035 ) ENGINE=HEAP DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1036 CRM_Core_DAO
::executeQuery($sql);
1037 CRM_Core_DAO
::executeQuery("INSERT INTO $this->tempTableRepeat1 {$subContributionQuery1}");
1039 CRM_Core_DAO
::executeQuery("
1040 ALTER TABLE $this->tempTableRepeat1 ADD INDEX ({$this->contributionJoinTableColumn})
1043 $this->tempTableRepeat2
= 'civicrm_temp_civireport_repeat2' . uniqid();
1045 CREATE TEMPORARY TABLE $this->tempTableRepeat2 (
1047 {$this->contributionJoinTableColumn} int unsigned,
1048 total_amount_sum int,
1049 total_amount_count int,
1051 ) ENGINE=HEAP DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1052 CRM_Core_DAO
::executeQuery($sql);
1053 $sql = "INSERT INTO $this->tempTableRepeat2 {$subContributionQuery2}";
1054 CRM_Core_DAO
::executeQuery($sql);
1056 CRM_Core_DAO
::executeQuery("
1057 ALTER TABLE $this->tempTableRepeat2 ADD INDEX ({$this->contributionJoinTableColumn})