5 +--------------------------------------------------------------------+
6 | CiviCRM version 4.3 |
7 +--------------------------------------------------------------------+
8 | Copyright CiviCRM LLC (c) 2004-2013 |
9 +--------------------------------------------------------------------+
10 | This file is a part of CiviCRM. |
12 | CiviCRM is free software; you can copy, modify, and distribute it |
13 | under the terms of the GNU Affero General Public License |
14 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
16 | CiviCRM is distributed in the hope that it will be useful, but |
17 | WITHOUT ANY WARRANTY; without even the implied warranty of |
18 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
19 | See the GNU Affero General Public License for more details. |
21 | You should have received a copy of the GNU Affero General Public |
22 | License and the CiviCRM Licensing Exception along |
23 | with this program; if not, contact CiviCRM LLC |
24 | at info[AT]civicrm[DOT]org. If you have questions about the |
25 | GNU Affero General Public License or the licensing of CiviCRM, |
26 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
27 +--------------------------------------------------------------------+
33 * @copyright CiviCRM LLC (c) 2004-2013
37 class CRM_Report_Form_Contribute_Repeat
extends CRM_Report_Form
{
38 protected $_amountClauseWithAND = NULL;
40 public $_drilldownReport = array('contribute/detail' => 'Link to Detail Report');
42 function __construct() {
43 $this->_columns
= array(
46 'dao' => 'CRM_Contact_DAO_Contact',
47 'grouping' => 'contact-fields',
52 'title' => ts('Contact Name'),
58 'title' => ts('Display Name'),
61 'addressee_display' =>
63 'title' => ts('Addressee Name'),
74 'percentage_change' =>
76 'title' => ts('Percentage Change'),
77 'type' => CRM_Utils_Type
::T_INT
,
78 'operatorType' => CRM_Report_Form
::OP_INT
,
79 'name' => 'percentage_change',
80 'dbAlias' => '( ( contribution_civireport2.total_amount_sum - contribution_civireport1.total_amount_sum ) * 100 / contribution_civireport1.total_amount_sum )',
87 'title' => ts('Contact'),
94 'dao' => 'CRM_Core_DAO_Email',
99 'title' => ts('Email'),
103 'grouping' => 'contact-fields',
107 'dao' => 'CRM_Core_DAO_Phone',
112 'title' => ts('Phone'),
116 'grouping' => 'contact-fields',
120 'dao' => 'CRM_Core_DAO_Address',
121 'grouping' => 'contact-fields',
124 'street_address' => array('title' => ts('Street Address')),
125 'supplemental_address_1' => array('title' => ts('Additional Address')),
126 'city' => array('title' => ts('City')),
127 'country_id' => array('title' => ts('Country')),
128 'state_province_id' => array('title' => ts('State/Province')),
129 'postal_code' => array('title' => ts('Postal Code')),
133 'country_id' => array('title' => ts('Country')),
134 'state_province_id' => array('title' => ts('State/Province'),
138 'civicrm_financial_type' =>
140 'dao' => 'CRM_Financial_DAO_FinancialType',
141 'fields' => array('financial_type' => array('title' => ts('Financial Type'))),
142 'grouping' => 'contri-fields',
144 array('financial_type' =>
147 'title' => ts('Financial Type'),
151 'civicrm_contribution' =>
153 'dao' => 'CRM_Contribute_DAO_Contribution',
156 'contribution_source' => NULL,
159 'name' => 'total_amount',
160 'alias' => 'contribution1',
161 'title' => ts('Range One Stat'),
162 'type' => CRM_Utils_Type
::T_MONEY
,
166 contribution_civireport1.total_amount_count as contribution1_total_amount_count,
167 contribution_civireport1.total_amount_sum as contribution1_total_amount_sum',
171 'name' => 'total_amount',
172 'alias' => 'contribution2',
173 'title' => ts('Range Two Stat'),
174 'type' => CRM_Utils_Type
::T_MONEY
,
178 contribution_civireport2.total_amount_count as contribution2_total_amount_count,
179 contribution_civireport2.total_amount_sum as contribution2_total_amount_sum',
182 'grouping' => 'contri-fields',
187 'title' => ts('Initial Date Range'),
188 'default' => 'previous.year',
189 'type' => CRM_Utils_Type
::T_DATE
,
190 'operatorType' => CRM_Report_Form
::OP_DATE
,
191 'name' => 'receive_date',
195 'title' => ts('Second Date Range'),
196 'default' => 'this.year',
197 'type' => CRM_Utils_Type
::T_DATE
,
198 'operatorType' => CRM_Report_Form
::OP_DATE
,
199 'name' => 'receive_date',
203 'title' => ts('Range One Amount'),
204 'type' => CRM_Utils_Type
::T_INT
,
205 'operatorType' => CRM_Report_Form
::OP_INT
,
206 'name' => 'total_amount',
210 'title' => ts('Range Two Amount'),
211 'type' => CRM_Utils_Type
::T_INT
,
212 'operatorType' => CRM_Report_Form
::OP_INT
,
213 'name' => 'total_amount',
215 'financial_type_id' =>
217 'title' => ts('Financial Type'),
218 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
219 'options' => CRM_Contribute_PseudoConstant
::financialType(),
221 'contribution_status_id' =>
223 'title' => ts('Contribution Status'),
224 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
225 'options' => CRM_Contribute_PseudoConstant
::contributionStatus(),
226 'default' => array('1'),
229 'group_bys' => array('contribution_source' => NULL),
233 'dao' => 'CRM_Contact_DAO_GroupContact',
239 'name' => 'group_id',
240 'title' => ts('Group'),
241 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
243 'options' => CRM_Core_PseudoConstant
::group(),
249 $this->_tagFilter
= TRUE;
251 parent
::__construct();
254 function preProcess() {
255 parent
::preProcess();
258 function setDefaultValues($freeze = TRUE) {
259 return parent
::setDefaultValues($freeze);
265 // since contact fields not related to financial type
266 if (array_key_exists('financial_type', $this->_params
['group_bys']) ||
267 array_key_exists('contribution_source', $this->_params
['group_bys'])
269 unset($this->_columns
['civicrm_contact']['fields']['id']);
272 foreach ($this->_columns
as $tableName => $table) {
273 if (array_key_exists('fields', $table)) {
274 foreach ($table['fields'] as $fieldName => $field) {
275 if (CRM_Utils_Array
::value('required', $field) ||
276 CRM_Utils_Array
::value($fieldName, $this->_params
['fields'])
278 if (isset($field['clause'])) {
279 $select[] = $field['clause'];
281 // FIXME: dirty hack for setting columnHeaders
282 $this->_columnHeaders
["{$field['alias']}_{$field['name']}_sum"]['type'] = CRM_Utils_Array
::value('type', $field);
283 $this->_columnHeaders
["{$field['alias']}_{$field['name']}_sum"]['title'] = $field['title'];
284 $this->_columnHeaders
["{$field['alias']}_{$field['name']}_count"]['type'] = CRM_Utils_Array
::value('type', $field);
285 $this->_columnHeaders
["{$field['alias']}_{$field['name']}_count"]['title'] = $field['title'];
289 // only include statistics columns if set
290 $select[] = "{$field['dbAlias']} as {$field['alias']}_{$field['name']}";
291 $this->_columnHeaders
["{$field['alias']}_{$field['name']}"]['type'] = CRM_Utils_Array
::value('type', $field);
292 $this->_columnHeaders
["{$field['alias']}_{$field['name']}"]['title'] = CRM_Utils_Array
::value('title', $field);
293 if (CRM_Utils_Array
::value('no_display', $field)) {
294 $this->_columnHeaders
["{$field['alias']}_{$field['name']}"]['no_display'] = TRUE;
301 $this->_select
= "SELECT " . implode(', ', $select) . " ";
304 function groupBy($tableCol = FALSE) {
305 $this->_groupBy
= "";
306 if (!empty($this->_params
['group_bys']) && is_array($this->_params
['group_bys'])) {
307 foreach ($this->_columns
as $tableName => $table) {
308 if (array_key_exists('group_bys', $table)) {
309 foreach ($table['group_bys'] as $fieldName => $field) {
310 if (CRM_Utils_Array
::value($fieldName, $this->_params
['group_bys'])) {
312 return array($tableName, $field['alias'], $field['name']);
315 $this->_groupBy
[] = "{$field['dbAlias']}";
322 $this->_groupBy
= "GROUP BY " . implode(', ', $this->_groupBy
);
327 list($fromTable, $fromAlias, $fromCol) = $this->groupBy(TRUE);
328 $from = "$fromTable $fromAlias";
330 if ($fromTable == 'civicrm_contact') {
331 $contriCol = "contact_id";
333 LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']} ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id
334 LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
335 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND {$this->_aliases['civicrm_email']}.is_primary = 1
336 LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
337 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND {$this->_aliases['civicrm_phone']}.is_primary = 1";
340 else if ($fromTable == 'civicrm_financial_type') {
341 $contriCol = "financial_type_id";
343 elseif ($fromTable == 'civicrm_contribution') {
344 $contriCol = $fromCol;
346 elseif ($fromTable == 'civicrm_address') {
348 INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']} ON {$this->_aliases['civicrm_address']}.contact_id = {$this->_aliases['civicrm_contact']}.id";
349 $fromAlias = $this->_aliases
['civicrm_contact'];
351 $contriCol = "contact_id";
356 LEFT JOIN civicrm_temp_civireport_repeat1 {$this->_aliases['civicrm_contribution']}1
357 ON $fromAlias.$fromCol = {$this->_aliases['civicrm_contribution']}1.$contriCol
358 LEFT JOIN civicrm_temp_civireport_repeat2 {$this->_aliases['civicrm_contribution']}2
359 ON $fromAlias.$fromCol = {$this->_aliases['civicrm_contribution']}2.$contriCol";
362 function whereContribution($replaceAliasWith = 'contribution1') {
363 $clauses = array("is_test" => "{$this->_aliases['civicrm_contribution']}.is_test = 0");
365 foreach ($this->_columns
['civicrm_contribution']['filters'] as $fieldName => $field) {
367 if (CRM_Utils_Array
::value('type', $field) & CRM_Utils_Type
::T_DATE
) {
368 $relative = CRM_Utils_Array
::value("{$fieldName}_relative", $this->_params
);
369 $from = CRM_Utils_Array
::value("{$fieldName}_from", $this->_params
);
370 $to = CRM_Utils_Array
::value("{$fieldName}_to", $this->_params
);
372 $clause = $this->dateClause($field['dbAlias'], $relative, $from, $to, $field['type']);
375 $op = CRM_Utils_Array
::value("{$fieldName}_op", $this->_params
);
377 $clause = $this->whereClause($field,
379 CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
),
380 CRM_Utils_Array
::value("{$fieldName}_min", $this->_params
),
381 CRM_Utils_Array
::value("{$fieldName}_max", $this->_params
)
385 if (!empty($clause)) {
386 $clauses[$fieldName] = $clause;
390 if (!$this->_amountClauseWithAND
) {
391 $amountClauseWithAND = array();
392 if (CRM_Utils_Array
::value('total_amount1', $clauses)) {
393 $amountClauseWithAND[] = str_replace("{$this->_aliases['civicrm_contribution']}.total_amount",
394 "{$this->_aliases['civicrm_contribution']}1.total_amount_sum", $clauses['total_amount1']);
396 if (CRM_Utils_Array
::value('total_amount2', $clauses)) {
397 $amountClauseWithAND[] = str_replace("{$this->_aliases['civicrm_contribution']}.total_amount",
398 "{$this->_aliases['civicrm_contribution']}2.total_amount_sum", $clauses['total_amount2']);
400 $this->_amountClauseWithAND
= !empty($amountClauseWithAND) ?
implode(' AND ', $amountClauseWithAND) : NULL;
403 if ($replaceAliasWith == 'contribution1') {
404 unset($clauses['receive_date2'], $clauses['total_amount2']);
407 unset($clauses['receive_date1'], $clauses['total_amount1']);
410 $whereClause = !empty($clauses) ?
"WHERE " . implode(' AND ', $clauses) : '';
412 if ($replaceAliasWith) {
413 $whereClause = str_replace($this->_aliases
['civicrm_contribution'], $replaceAliasWith, $whereClause);
420 if (!$this->_amountClauseWithAND
) {
421 $this->_amountClauseWithAND
=
422 "!({$this->_aliases['civicrm_contribution']}1.total_amount_count IS NULL AND {$this->_aliases['civicrm_contribution']}2.total_amount_count IS NULL)";
424 $clauses = array("atleast_one_amount" => $this->_amountClauseWithAND
);
426 foreach ($this->_columns
as $tableName => $table) {
427 if (array_key_exists('filters', $table) && $tableName != 'civicrm_contribution') {
428 foreach ($table['filters'] as $fieldName => $field) {
430 $op = CRM_Utils_Array
::value("{$fieldName}_op", $this->_params
);
432 $clause = $this->whereClause($field,
434 CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
),
435 CRM_Utils_Array
::value("{$fieldName}_min", $this->_params
),
436 CRM_Utils_Array
::value("{$fieldName}_max", $this->_params
)
439 if (!empty($clause)) {
440 $clauses[$fieldName] = $clause;
446 $this->_where
= !empty($clauses) ?
"WHERE " . implode(' AND ', $clauses) : '';
449 function formRule($fields, $files, $self) {
451 $errors = $checkDate = $errorCount = array();
464 'supplemental_address_1',
467 'country_id' => array('country_id'),
468 'state_province_id' => array('country_id', 'state_province_id'),
469 'contribution_source' => array('contribution_source'),
470 'financial_type' => array('financial_type'),
474 'id' => ts('Contact'),
475 'country_id' => ts('Country'),
476 'state_province_id' => ts('State/Province'),
477 'contribution_source' => ts('Contribution Source'),
478 'financial_type' => ts('Financial Type'),
479 'sort_name' => ts('Contact Name'),
480 'email' => ts('Email'),
481 'phone' => ts('Phone'),
484 if (empty($fields['group_bys'])) {
485 $errors['fields'] = ts('Please select at least one Group by field.');
487 elseif ((array_key_exists('contribution_source', $fields['group_bys']) ||
488 array_key_exists('contribution_type', $fields['group_bys'])
490 (count($fields['group_bys']) > 1)
492 $errors['fields'] = ts('You can not use other Group by with Financial type or Contribution source.');
495 foreach ($fields['fields'] as $fld_id => $value) {
496 if (!($fld_id == 'total_amount1') && !($fld_id == 'total_amount2')) {
498 $invlidGroups = array();
499 foreach ($fields['group_bys'] as $grp_id => $val) {
500 $validFields = $rules[$grp_id];
501 if (in_array($fld_id, $validFields)) {
505 $invlidGroups[] = $idMapping[$grp_id];
509 $erorrGrps = implode(',', $invlidGroups);
510 $tempErrors[] = ts("Do not select field %1 with Group by %2.", array(1 => $idMapping[$fld_id], 2 => $erorrGrps));
514 if (!empty($tempErrors)) {
515 $errors['fields'] = implode("<br>", $tempErrors);
519 if (!empty($fields['gid_value']) && CRM_Utils_Array
::value('group_bys', $fields)) {
520 if (!array_key_exists('id', $fields['group_bys'])) {
521 $errors['gid_value'] = ts("Filter with Group only allow with group by Contact");
525 if ($fields['receive_date1_relative'] == '0') {
526 $checkDate['receive_date1']['receive_date1_from'] = $fields['receive_date1_from'];
527 $checkDate['receive_date1']['receive_date1_to'] = $fields['receive_date1_to'];
530 if ($fields['receive_date2_relative'] == '0') {
531 $checkDate['receive_date2']['receive_date2_from'] = $fields['receive_date2_from'];
532 $checkDate['receive_date2']['receive_date2_to'] = $fields['receive_date2_to'];
535 foreach ($checkDate as $date_range => $range_data) {
536 foreach ($range_data as $key => $value) {
537 if (CRM_Utils_Date
::isDate($value)) {
538 $errorCount[$date_range][$key]['valid'] = 'true';
539 $errorCount[$date_range][$key]['is_empty'] = 'false';
542 $errorCount[$date_range][$key]['valid'] = 'false';
543 $errorCount[$date_range][$key]['is_empty'] = 'true';
544 if (is_array($value)) {
545 foreach ($value as $v) {
547 $errorCount[$date_range][$key]['is_empty'] = 'false';
551 elseif (!isset($value)) {
552 $errorCount[$date_range][$key]['is_empty'] = 'false';
558 $errorText = ts("Select valid date range");
559 foreach ($errorCount as $date_range => $error_data) {
561 if (($error_data[$date_range . '_from']['valid'] == 'false') &&
562 ($error_data[$date_range . '_to']['valid'] == 'false')
565 if (($error_data[$date_range . '_from']['is_empty'] == 'true') &&
566 ($error_data[$date_range . '_to']['is_empty'] == 'true')
568 $errors[$date_range . '_relative'] = $errorText;
571 if ($error_data[$date_range . '_from']['is_empty'] == 'false') {
572 $errors[$date_range . '_from'] = $errorText;
575 if ($error_data[$date_range . '_to']['is_empty'] == 'false') {
576 $errors[$date_range . '_to'] = $errorText;
579 elseif (($error_data[$date_range . '_from']['valid'] == 'true') &&
580 ($error_data[$date_range . '_to']['valid'] == 'false')
582 if ($error_data[$date_range . '_to']['is_empty'] == 'false') {
583 $errors[$date_range . '_to'] = $errorText;
586 elseif (($error_data[$date_range . '_from']['valid'] == 'false') &&
587 ($error_data[$date_range . '_to']['valid'] == 'true')
589 if ($error_data[$date_range . '_from']['is_empty'] == 'false') {
590 $errors[$date_range . '_from'] = $errorText;
598 function statistics(&$rows) {
599 $statistics = parent
::statistics($rows);
601 //fetch contributions for both date ranges from pre-existing temp tables
603 CREATE TEMPORARY TABLE civicrm_temp_civireport_repeat3
604 SELECT contact_id FROM civicrm_temp_civireport_repeat1 UNION SELECT contact_id FROM civicrm_temp_civireport_repeat2;";
605 $dao = CRM_Core_DAO
::executeQuery($sql);
608 SELECT civicrm_temp_civireport_repeat3.contact_id,
609 civicrm_temp_civireport_repeat1.total_amount_sum as contribution1_total_amount_sum,
610 civicrm_temp_civireport_repeat2.total_amount_sum as contribution2_total_amount_sum
611 FROM civicrm_temp_civireport_repeat3
612 LEFT JOIN civicrm_temp_civireport_repeat1
613 ON civicrm_temp_civireport_repeat3.contact_id = civicrm_temp_civireport_repeat1.contact_id
614 LEFT JOIN civicrm_temp_civireport_repeat2
615 ON civicrm_temp_civireport_repeat3.contact_id = civicrm_temp_civireport_repeat2.contact_id";
617 $dao = CRM_Core_DAO
::executeQuery($sql);
619 //store contributions in array 'contact_sums' for comparison
620 $contact_sums = array();
621 while ($dao->fetch()) {
622 $contact_sums[$dao->contact_id
] =
624 'contribution1_total_amount_sum' => $dao->contribution1_total_amount_sum
,
625 'contribution2_total_amount_sum' => $dao->contribution2_total_amount_sum
629 $total_distinct_contacts = count($contact_sums);
630 $number_maintained = 0;
631 $number_upgraded = 0;
632 $number_downgraded = 0;
636 foreach ($contact_sums as $uid => $row) {
637 if ($row['contribution1_total_amount_sum'] && $row['contribution2_total_amount_sum']) {
638 $change = ($row['contribution1_total_amount_sum'] - $row['contribution2_total_amount_sum']);
640 $number_maintained +
= 1;
642 $number_upgraded +
= 1;
644 $number_downgraded +
= 1;
646 elseif ($row['contribution1_total_amount_sum']) {
649 elseif ($row['contribution2_total_amount_sum']) {
654 //calculate percentages from numbers
655 $percent_maintained = ($number_maintained / $total_distinct_contacts) * 100;
656 $percent_upgraded = ($number_upgraded / $total_distinct_contacts) * 100;
657 $percent_downgraded = ($number_downgraded / $total_distinct_contacts) * 100;
658 $percent_new = ($number_new / $total_distinct_contacts) * 100;
659 $percent_lapsed = ($number_lapsed / $total_distinct_contacts) * 100;
661 //display percentages for new, lapsed, upgraded, downgraded, and maintained contributors
662 $statistics['counts']['count_new'] = array(
663 'value' => $percent_new,
664 'title' => '% New Donors',
666 $statistics['counts']['count_lapsed'] = array(
667 'value' => $percent_lapsed,
668 'title' => '% Lapsed Donors',
670 $statistics['counts']['count_upgraded'] = array(
671 'value' => $percent_upgraded,
672 'title' => '% Upgraded Donors',
674 $statistics['counts']['count_downgraded'] = array(
675 'value' => $percent_downgraded,
676 'title' => '% Downgraded Donors',
678 $statistics['counts']['count_maintained'] = array(
679 'value' => $percent_maintained,
680 'title' => '% Maintained Donors',
684 SELECT COUNT({$this->_aliases['civicrm_contribution']}1.total_amount_count ) as count,
685 SUM({$this->_aliases['civicrm_contribution']}1.total_amount_sum ) as amount,
686 ROUND(AVG({$this->_aliases['civicrm_contribution']}1.total_amount_sum), 2) as avg,
687 COUNT({$this->_aliases['civicrm_contribution']}2.total_amount_count ) as count2,
688 SUM({$this->_aliases['civicrm_contribution']}2.total_amount_sum ) as amount2,
689 ROUND(AVG({$this->_aliases['civicrm_contribution']}2.total_amount_sum), 2) as avg2";
690 $sql = "{$select} {$this->_from} {$this->_where}";
691 $dao = CRM_Core_DAO
::executeQuery($sql);
694 $statistics['counts']['range_one_title'] = array('title' => 'Initial Date Range:');
695 $statistics['counts']['amount'] = array(
696 'value' => $dao->amount
,
697 'title' => 'Total Amount',
698 'type' => CRM_Utils_Type
::T_MONEY
,
700 $statistics['counts']['count '] = array(
701 'value' => $dao->count
,
702 'title' => 'Total Donations',
704 $statistics['counts']['avg '] = array(
705 'value' => $dao->avg
,
706 'title' => 'Average',
707 'type' => CRM_Utils_Type
::T_MONEY
,
709 $statistics['counts']['range_two_title'] = array(
710 'title' => 'Second Date Range:',
712 $statistics['counts']['amount2'] = array(
713 'value' => $dao->amount2
,
714 'title' => 'Total Amount',
715 'type' => CRM_Utils_Type
::T_MONEY
,
717 $statistics['counts']['count2 '] = array(
718 'value' => $dao->count2
,
719 'title' => 'Total Donations',
721 $statistics['counts']['avg2 '] = array(
722 'value' => $dao->avg2
,
723 'title' => 'Average',
724 'type' => CRM_Utils_Type
::T_MONEY
,
731 function postProcess() {
732 $this->beginPostProcess();
733 $create = $subSelect1 = $subSelect2 = NULL;
734 list($fromTable, $fromAlias, $fromCol) = $this->groupBy(TRUE);
735 if ($fromTable == 'civicrm_contact') {
736 $contriCol = "contact_id";
738 elseif ($fromTable == 'civicrm_contribution_type') {
739 $contriCol = "contribution_type_id";
741 elseif ($fromTable == 'civicrm_contribution') {
742 $contriCol = $fromCol;
744 elseif ($fromTable == 'civicrm_address') {
745 $contriCol = "contact_id";
747 elseif ($fromTable == 'civicrm_financial_type') {
748 $contriCol = 'financial_type_id';
749 $subSelect1 = 'contribution1.contact_id,';
750 $subSelect2 = 'contribution2.contact_id,';
751 $create = 'contact_id int unsigned,';
754 $subWhere = $this->whereContribution();
755 $subContributionQuery1 = "
756 SELECT {$subSelect1} contribution1.{$contriCol},
757 sum( contribution1.total_amount ) AS total_amount_sum,
758 count( * ) AS total_amount_count
759 FROM civicrm_contribution contribution1
761 GROUP BY contribution1.{$contriCol}";
763 $subWhere = $this->whereContribution('contribution2');
764 $subContributionQuery2 = "
765 SELECT {$subSelect2} contribution2.{$contriCol},
766 sum( contribution2.total_amount ) AS total_amount_sum,
767 count( * ) AS total_amount_count
768 FROM civicrm_contribution contribution2
770 GROUP BY contribution2.{$contriCol}";
773 CREATE TEMPORARY TABLE civicrm_temp_civireport_repeat1 (
775 {$contriCol} int unsigned,
776 total_amount_sum int,
777 total_amount_count int
778 ) ENGINE=HEAP DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
779 CRM_Core_DAO
::executeQuery($sql);
780 $sql = "INSERT INTO civicrm_temp_civireport_repeat1 {$subContributionQuery1}";
781 CRM_Core_DAO
::executeQuery($sql);
784 CREATE TEMPORARY TABLE civicrm_temp_civireport_repeat2 (
786 {$contriCol} int unsigned,
787 total_amount_sum int,
788 total_amount_count int
789 ) ENGINE=HEAP DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
790 CRM_Core_DAO
::executeQuery($sql);
791 $sql = "INSERT INTO civicrm_temp_civireport_repeat2 {$subContributionQuery2}";
792 CRM_Core_DAO
::executeQuery($sql);
801 $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_limit}";
802 $dao = CRM_Core_DAO
::executeQuery($sql);
804 while ($dao->fetch()) {
805 foreach ($this->_columnHeaders
as $key => $value) {
806 $rows[$count][$key] = $dao->$key;
811 // FIXME: calculate % using query
812 foreach ($rows as $uid => $row) {
813 if ($row['contribution1_total_amount_sum'] && $row['contribution2_total_amount_sum']) {
814 $rows[$uid]['change'] = number_format((($row['contribution2_total_amount_sum'] -
815 $row['contribution1_total_amount_sum']
817 ($row['contribution1_total_amount_sum']), 2
820 elseif ($row['contribution1_total_amount_sum']) {
821 $rows[$uid]['change'] = ts('Skipped Donation');
823 elseif ($row['contribution2_total_amount_sum']) {
824 $rows[$uid]['change'] = ts('New Donor');
826 if ($row['contribution1_total_amount_count']) {
827 $rows[$uid]['contribution1_total_amount_sum'] = $row['contribution1_total_amount_sum'] . " ({$row['contribution1_total_amount_count']})";
829 if ($row['contribution2_total_amount_count']) {
830 $rows[$uid]['contribution2_total_amount_sum'] = $row['contribution2_total_amount_sum'] . " ({$row['contribution2_total_amount_count']})";
833 $this->_columnHeaders
['change'] = array(
834 'title' => '% Change',
835 'type' => CRM_Utils_Type
::T_INT
,
839 list($from1, $to1) = $this->getFromTo(CRM_Utils_Array
::value("receive_date1_relative", $this->_params
),
840 CRM_Utils_Array
::value("receive_date1_from", $this->_params
),
841 CRM_Utils_Array
::value("receive_date1_to", $this->_params
)
843 $from1 = CRM_Utils_Date
::customFormat($from1, NULL, array('d'));
844 $to1 = CRM_Utils_Date
::customFormat($to1, NULL, array('d'));
846 list($from2, $to2) = $this->getFromTo(CRM_Utils_Array
::value("receive_date2_relative", $this->_params
),
847 CRM_Utils_Array
::value("receive_date2_from", $this->_params
),
848 CRM_Utils_Array
::value("receive_date2_to", $this->_params
)
850 $from2 = CRM_Utils_Date
::customFormat($from2, NULL, array('d'));
851 $to2 = CRM_Utils_Date
::customFormat($to2, NULL, array('d'));
853 $this->_columnHeaders
['contribution1_total_amount_sum']['title'] = "$from1 -<br/> $to1";
854 $this->_columnHeaders
['contribution2_total_amount_sum']['title'] = "$from2 -<br/> $to2";
855 unset($this->_columnHeaders
['contribution1_total_amount_count'],
856 $this->_columnHeaders
['contribution2_total_amount_count']
859 $this->formatDisplay($rows);
861 // assign variables to templates
862 $this->doTemplateAssignment($rows);
864 $this->endPostProcess($rows);
867 function alterDisplay(&$rows) {
868 // custom code to alter rows
869 list($from1, $to1) = $this->getFromTo(CRM_Utils_Array
::value("receive_date1_relative", $this->_params
),
870 CRM_Utils_Array
::value("receive_date1_from", $this->_params
),
871 CRM_Utils_Array
::value("receive_date1_to", $this->_params
)
873 list($from2, $to2) = $this->getFromTo(CRM_Utils_Array
::value("receive_date2_relative", $this->_params
),
874 CRM_Utils_Array
::value("receive_date2_from", $this->_params
),
875 CRM_Utils_Array
::value("receive_date2_to", $this->_params
)
880 $dateUrl .= "receive_date1_from={$from1}&";
883 $dateUrl .= "receive_date1_to={$to1}&";
886 $dateUrl .= "receive_date2_from={$from2}&";
889 $dateUrl .= "receive_date2_to={$to2}&";
892 foreach ($rows as $rowNum => $row) {
894 if (array_key_exists('address_civireport_country_id', $row)) {
895 if ($value = $row['address_civireport_country_id']) {
896 $rows[$rowNum]['address_civireport_country_id'] = CRM_Core_PseudoConstant
::country($value, FALSE);
898 $url = CRM_Report_Utils_Report
::getNextUrl('contribute/detail',
900 "country_id_op=in&country_id_value={$value}&" .
902 $this->_absoluteUrl
, $this->_id
, $this->_drilldownReport
905 $rows[$rowNum]['address_civireport_country_id_link'] = $url;
906 $rows[$rowNum]['address_civireport_country_id_hover'] = ts("View contributions for this Country.");
910 // handle state province
911 if (array_key_exists('address_civireport_state_province_id', $row)) {
912 if ($value = $row['address_civireport_state_province_id']) {
913 $rows[$rowNum]['address_civireport_state_province_id'] = CRM_Core_PseudoConstant
::stateProvince($value, FALSE);
915 $url = CRM_Report_Utils_Report
::getNextUrl('contribute/detail',
917 "state_province_id_op=in&state_province_id_value={$value}&" .
919 $this->_absoluteUrl
, $this->_id
, $this->_drilldownReport
922 $rows[$rowNum]['address_civireport_state_province_id_link'] = $url;
923 $rows[$rowNum]['address_civireport_state_province_id_hover'] = ts("View repeatDetails for this state.");
927 // convert display name to links
928 if (array_key_exists('contact_civireport_sort_name', $row) &&
929 array_key_exists('contact_civireport_id', $row)
931 $url = CRM_Report_Utils_Report
::getNextUrl('contribute/detail',
932 'reset=1&force=1&id_op=eq&id_value=' . $row['contact_civireport_id'],
933 $this->_absoluteUrl
, $this->_id
, $this->_drilldownReport
936 $rows[$rowNum]['contact_civireport_sort_name_link'] = $url;
937 $rows[$rowNum]['contact_civireport_sort_name_hover'] = ts("View Contribution details for this contact");