4 +--------------------------------------------------------------------+
5 | CiviCRM version 4.5 |
6 +--------------------------------------------------------------------+
7 | Copyright CiviCRM LLC (c) 2004-2014 |
8 +--------------------------------------------------------------------+
9 | This file is a part of CiviCRM. |
11 | CiviCRM is free software; you can copy, modify, and distribute it |
12 | under the terms of the GNU Affero General Public License |
13 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
15 | CiviCRM is distributed in the hope that it will be useful, but |
16 | WITHOUT ANY WARRANTY; without even the implied warranty of |
17 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
18 | See the GNU Affero General Public License for more details. |
20 | You should have received a copy of the GNU Affero General Public |
21 | License and the CiviCRM Licensing Exception along |
22 | with this program; if not, contact CiviCRM LLC |
23 | at info[AT]civicrm[DOT]org. If you have questions about the |
24 | GNU Affero General Public License or the licensing of CiviCRM, |
25 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
26 +--------------------------------------------------------------------+
32 * @copyright CiviCRM LLC (c) 2004-2014
36 class CRM_Report_Form_Contribute_Repeat
extends CRM_Report_Form
{
37 protected $_amountClauseWithAND = NULL;
39 public $_drilldownReport = array('contribute/detail' => 'Link to Detail Report');
41 function __construct() {
42 $this->_columns
= array(
45 'dao' => 'CRM_Contact_DAO_Contact',
46 'grouping' => 'contact-fields',
51 'title' => ts('Contact Name'),
57 'title' => ts('Display Name'),
60 'addressee_display' =>
62 'title' => ts('Addressee Name'),
72 'title' => ts('Contact Type'),
77 'title' => ts('Contact SubType'),
83 'percentage_change' =>
85 'title' => ts('Percentage Change'),
86 'type' => CRM_Utils_Type
::T_INT
,
87 'operatorType' => CRM_Report_Form
::OP_INT
,
88 'name' => 'percentage_change',
89 'dbAlias' => '( ( contribution_civireport2.total_amount_sum - contribution_civireport1.total_amount_sum ) * 100 / contribution_civireport1.total_amount_sum )',
96 'title' => ts('Contact'),
103 'dao' => 'CRM_Core_DAO_Email',
108 'title' => ts('Email'),
112 'grouping' => 'contact-fields',
116 'dao' => 'CRM_Core_DAO_Phone',
121 'title' => ts('Phone'),
125 'grouping' => 'contact-fields',
129 'dao' => 'CRM_Core_DAO_Address',
130 'grouping' => 'contact-fields',
133 'street_address' => array('title' => ts('Street Address')),
134 'supplemental_address_1' => array('title' => ts('Supplemental Address 1')),
135 'city' => array('title' => ts('City')),
136 'country_id' => array('title' => ts('Country')),
137 'state_province_id' => array('title' => ts('State/Province')),
138 'postal_code' => array('title' => ts('Postal Code')),
142 'country_id' => array('title' => ts('Country')),
143 'state_province_id' => array('title' => ts('State/Province'),
147 'civicrm_financial_type' =>
149 'dao' => 'CRM_Financial_DAO_FinancialType',
150 'fields' => array('financial_type' => array('title' => ts('Financial Type'))),
151 'grouping' => 'contri-fields',
153 array('financial_type' =>
156 'title' => ts('Financial Type'),
160 'civicrm_contribution' =>
162 'dao' => 'CRM_Contribute_DAO_Contribution',
165 'contribution_source' => NULL,
168 'name' => 'total_amount',
169 'alias' => 'contribution1',
170 'title' => ts('Range One Stat'),
171 'type' => CRM_Utils_Type
::T_MONEY
,
175 contribution_civireport1.total_amount_count as contribution1_total_amount_count,
176 contribution_civireport1.total_amount_sum as contribution1_total_amount_sum',
180 'name' => 'total_amount',
181 'alias' => 'contribution2',
182 'title' => ts('Range Two Stat'),
183 'type' => CRM_Utils_Type
::T_MONEY
,
187 contribution_civireport2.total_amount_count as contribution2_total_amount_count,
188 contribution_civireport2.total_amount_sum as contribution2_total_amount_sum',
191 'grouping' => 'contri-fields',
196 'title' => ts('Initial Date Range'),
197 'default' => 'previous.year',
198 'type' => CRM_Utils_Type
::T_DATE
,
199 'operatorType' => CRM_Report_Form
::OP_DATE
,
200 'name' => 'receive_date',
204 'title' => ts('Second Date Range'),
205 'default' => 'this.year',
206 'type' => CRM_Utils_Type
::T_DATE
,
207 'operatorType' => CRM_Report_Form
::OP_DATE
,
208 'name' => 'receive_date',
212 'title' => ts('Range One Amount'),
213 'type' => CRM_Utils_Type
::T_INT
,
214 'operatorType' => CRM_Report_Form
::OP_INT
,
215 'name' => 'total_amount',
219 'title' => ts('Range Two Amount'),
220 'type' => CRM_Utils_Type
::T_INT
,
221 'operatorType' => CRM_Report_Form
::OP_INT
,
222 'name' => 'total_amount',
224 'financial_type_id' =>
226 'title' => ts('Financial Type'),
227 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
228 'options' => CRM_Contribute_PseudoConstant
::financialType(),
230 'contribution_status_id' =>
232 'title' => ts('Contribution Status'),
233 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
234 'options' => CRM_Contribute_PseudoConstant
::contributionStatus(),
235 'default' => array('1'),
238 'group_bys' => array('contribution_source' => NULL),
242 'dao' => 'CRM_Contact_DAO_GroupContact',
248 'name' => 'group_id',
249 'title' => ts('Group'),
250 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
252 'options' => CRM_Core_PseudoConstant
::group(),
258 $this->_tagFilter
= TRUE;
260 parent
::__construct();
263 function preProcess() {
264 parent
::preProcess();
267 function setDefaultValues($freeze = TRUE) {
268 return parent
::setDefaultValues($freeze);
274 // since contact fields not related to financial type
275 if (array_key_exists('financial_type', $this->_params
['group_bys']) ||
276 array_key_exists('contribution_source', $this->_params
['group_bys'])
278 unset($this->_columns
['civicrm_contact']['fields']['id']);
281 foreach ($this->_columns
as $tableName => $table) {
282 if (array_key_exists('fields', $table)) {
283 foreach ($table['fields'] as $fieldName => $field) {
284 if (!empty($field['required']) ||
!empty($this->_params
['fields'][$fieldName])) {
285 if (isset($field['clause'])) {
286 $select[] = $field['clause'];
288 // FIXME: dirty hack for setting columnHeaders
289 $this->_columnHeaders
["{$field['alias']}_{$field['name']}_sum"]['type'] = CRM_Utils_Array
::value('type', $field);
290 $this->_columnHeaders
["{$field['alias']}_{$field['name']}_sum"]['title'] = $field['title'];
291 $this->_columnHeaders
["{$field['alias']}_{$field['name']}_count"]['type'] = CRM_Utils_Array
::value('type', $field);
292 $this->_columnHeaders
["{$field['alias']}_{$field['name']}_count"]['title'] = $field['title'];
296 // only include statistics columns if set
297 $select[] = "{$field['dbAlias']} as {$field['alias']}_{$field['name']}";
298 $this->_columnHeaders
["{$field['alias']}_{$field['name']}"]['type'] = CRM_Utils_Array
::value('type', $field);
299 $this->_columnHeaders
["{$field['alias']}_{$field['name']}"]['title'] = CRM_Utils_Array
::value('title', $field);
300 if (!empty($field['no_display'])) {
301 $this->_columnHeaders
["{$field['alias']}_{$field['name']}"]['no_display'] = TRUE;
308 $this->_select
= "SELECT " . implode(', ', $select) . " ";
311 function groupBy($tableCol = FALSE) {
312 $this->_groupBy
= "";
313 if (!empty($this->_params
['group_bys']) && is_array($this->_params
['group_bys'])) {
314 foreach ($this->_columns
as $tableName => $table) {
315 if (array_key_exists('group_bys', $table)) {
316 foreach ($table['group_bys'] as $fieldName => $field) {
317 if (!empty($this->_params
['group_bys'][$fieldName])) {
319 return array($tableName, $field['alias'], $field['name']);
322 $this->_groupBy
[] = "{$field['dbAlias']}";
329 $this->_groupBy
= "GROUP BY " . implode(', ', $this->_groupBy
);
334 list($fromTable, $fromAlias, $fromCol) = $this->groupBy(TRUE);
335 $from = "$fromTable $fromAlias";
337 if ($fromTable == 'civicrm_contact') {
338 $contriCol = "contact_id";
340 LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']} ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id
341 LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
342 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND {$this->_aliases['civicrm_email']}.is_primary = 1
343 LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
344 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND {$this->_aliases['civicrm_phone']}.is_primary = 1";
347 else if ($fromTable == 'civicrm_financial_type') {
348 $contriCol = "financial_type_id";
350 elseif ($fromTable == 'civicrm_contribution') {
351 $contriCol = $fromCol;
353 elseif ($fromTable == 'civicrm_address') {
355 INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']} ON {$this->_aliases['civicrm_address']}.contact_id = {$this->_aliases['civicrm_contact']}.id";
356 $fromAlias = $this->_aliases
['civicrm_contact'];
358 $contriCol = "contact_id";
363 LEFT JOIN civicrm_temp_civireport_repeat1 {$this->_aliases['civicrm_contribution']}1
364 ON $fromAlias.$fromCol = {$this->_aliases['civicrm_contribution']}1.$contriCol
365 LEFT JOIN civicrm_temp_civireport_repeat2 {$this->_aliases['civicrm_contribution']}2
366 ON $fromAlias.$fromCol = {$this->_aliases['civicrm_contribution']}2.$contriCol";
369 function whereContribution($replaceAliasWith = 'contribution1') {
370 $clauses = array("is_test" => "{$this->_aliases['civicrm_contribution']}.is_test = 0");
372 foreach ($this->_columns
['civicrm_contribution']['filters'] as $fieldName => $field) {
374 if (CRM_Utils_Array
::value('type', $field) & CRM_Utils_Type
::T_DATE
) {
375 $relative = CRM_Utils_Array
::value("{$fieldName}_relative", $this->_params
);
376 $from = CRM_Utils_Array
::value("{$fieldName}_from", $this->_params
);
377 $to = CRM_Utils_Array
::value("{$fieldName}_to", $this->_params
);
379 $clause = $this->dateClause($field['dbAlias'], $relative, $from, $to, $field['type']);
382 $op = CRM_Utils_Array
::value("{$fieldName}_op", $this->_params
);
384 $clause = $this->whereClause($field,
386 CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
),
387 CRM_Utils_Array
::value("{$fieldName}_min", $this->_params
),
388 CRM_Utils_Array
::value("{$fieldName}_max", $this->_params
)
392 if (!empty($clause)) {
393 $clauses[$fieldName] = $clause;
397 if (!$this->_amountClauseWithAND
) {
398 $amountClauseWithAND = array();
399 if (!empty($clauses['total_amount1'])) {
400 $amountClauseWithAND[] = str_replace("{$this->_aliases['civicrm_contribution']}.total_amount",
401 "{$this->_aliases['civicrm_contribution']}1.total_amount_sum", $clauses['total_amount1']);
403 if (!empty($clauses['total_amount2'])) {
404 $amountClauseWithAND[] = str_replace("{$this->_aliases['civicrm_contribution']}.total_amount",
405 "{$this->_aliases['civicrm_contribution']}2.total_amount_sum", $clauses['total_amount2']);
407 $this->_amountClauseWithAND
= !empty($amountClauseWithAND) ?
implode(' AND ', $amountClauseWithAND) : NULL;
410 if ($replaceAliasWith == 'contribution1') {
411 unset($clauses['receive_date2'], $clauses['total_amount2']);
414 unset($clauses['receive_date1'], $clauses['total_amount1']);
417 $whereClause = !empty($clauses) ?
"WHERE " . implode(' AND ', $clauses) : '';
419 if ($replaceAliasWith) {
420 $whereClause = str_replace($this->_aliases
['civicrm_contribution'], $replaceAliasWith, $whereClause);
427 if (!$this->_amountClauseWithAND
) {
428 $this->_amountClauseWithAND
=
429 "!({$this->_aliases['civicrm_contribution']}1.total_amount_count IS NULL AND {$this->_aliases['civicrm_contribution']}2.total_amount_count IS NULL)";
431 $clauses = array("atleast_one_amount" => $this->_amountClauseWithAND
);
433 foreach ($this->_columns
as $tableName => $table) {
434 if (array_key_exists('filters', $table) && $tableName != 'civicrm_contribution') {
435 foreach ($table['filters'] as $fieldName => $field) {
437 $op = CRM_Utils_Array
::value("{$fieldName}_op", $this->_params
);
439 $clause = $this->whereClause($field,
441 CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
),
442 CRM_Utils_Array
::value("{$fieldName}_min", $this->_params
),
443 CRM_Utils_Array
::value("{$fieldName}_max", $this->_params
)
446 if (!empty($clause)) {
447 $clauses[$fieldName] = $clause;
453 $this->_where
= !empty($clauses) ?
"WHERE " . implode(' AND ', $clauses) : '';
456 function formRule($fields, $files, $self) {
458 $errors = $checkDate = $errorCount = array();
473 'supplemental_address_1',
476 'country_id' => array('country_id'),
477 'state_province_id' => array('country_id', 'state_province_id'),
478 'contribution_source' => array('contribution_source'),
479 'financial_type' => array('financial_type'),
483 'id' => ts('Contact'),
484 'country_id' => ts('Country'),
485 'state_province_id' => ts('State/Province'),
486 'contribution_source' => ts('Contribution Source'),
487 'financial_type' => ts('Financial Type'),
488 'sort_name' => ts('Contact Name'),
489 'email' => ts('Email'),
490 'phone' => ts('Phone'),
493 if (empty($fields['group_bys'])) {
494 $errors['fields'] = ts('Please select at least one Group by field.');
496 elseif ((array_key_exists('contribution_source', $fields['group_bys']) ||
497 array_key_exists('contribution_type', $fields['group_bys'])
499 (count($fields['group_bys']) > 1)
501 $errors['fields'] = ts('You can not use other Group by with Financial type or Contribution source.');
504 foreach ($fields['fields'] as $fld_id => $value) {
505 if (!($fld_id == 'total_amount1') && !($fld_id == 'total_amount2')) {
507 $invlidGroups = array();
508 foreach ($fields['group_bys'] as $grp_id => $val) {
509 $validFields = $rules[$grp_id];
510 if (in_array($fld_id, $validFields)) {
514 $invlidGroups[] = $idMapping[$grp_id];
518 $erorrGrps = implode(',', $invlidGroups);
519 $tempErrors[] = ts("Do not select field %1 with Group by %2.", array(1 => $idMapping[$fld_id], 2 => $erorrGrps));
523 if (!empty($tempErrors)) {
524 $errors['fields'] = implode("<br>", $tempErrors);
528 if (!empty($fields['gid_value']) && !empty($fields['group_bys'])) {
529 if (!array_key_exists('id', $fields['group_bys'])) {
530 $errors['gid_value'] = ts("Filter with Group only allow with group by Contact");
534 if ($fields['receive_date1_relative'] == '0') {
535 $checkDate['receive_date1']['receive_date1_from'] = $fields['receive_date1_from'];
536 $checkDate['receive_date1']['receive_date1_to'] = $fields['receive_date1_to'];
539 if ($fields['receive_date2_relative'] == '0') {
540 $checkDate['receive_date2']['receive_date2_from'] = $fields['receive_date2_from'];
541 $checkDate['receive_date2']['receive_date2_to'] = $fields['receive_date2_to'];
544 foreach ($checkDate as $date_range => $range_data) {
545 foreach ($range_data as $key => $value) {
546 if (CRM_Utils_Date
::isDate($value)) {
547 $errorCount[$date_range][$key]['valid'] = 'true';
548 $errorCount[$date_range][$key]['is_empty'] = 'false';
551 $errorCount[$date_range][$key]['valid'] = 'false';
552 $errorCount[$date_range][$key]['is_empty'] = 'true';
553 if (is_array($value)) {
554 foreach ($value as $v) {
556 $errorCount[$date_range][$key]['is_empty'] = 'false';
560 elseif (!isset($value)) {
561 $errorCount[$date_range][$key]['is_empty'] = 'false';
567 $errorText = ts("Select valid date range");
568 foreach ($errorCount as $date_range => $error_data) {
570 if (($error_data[$date_range . '_from']['valid'] == 'false') &&
571 ($error_data[$date_range . '_to']['valid'] == 'false')
574 if (($error_data[$date_range . '_from']['is_empty'] == 'true') &&
575 ($error_data[$date_range . '_to']['is_empty'] == 'true')
577 $errors[$date_range . '_relative'] = $errorText;
580 if ($error_data[$date_range . '_from']['is_empty'] == 'false') {
581 $errors[$date_range . '_from'] = $errorText;
584 if ($error_data[$date_range . '_to']['is_empty'] == 'false') {
585 $errors[$date_range . '_to'] = $errorText;
588 elseif (($error_data[$date_range . '_from']['valid'] == 'true') &&
589 ($error_data[$date_range . '_to']['valid'] == 'false')
591 if ($error_data[$date_range . '_to']['is_empty'] == 'false') {
592 $errors[$date_range . '_to'] = $errorText;
595 elseif (($error_data[$date_range . '_from']['valid'] == 'false') &&
596 ($error_data[$date_range . '_to']['valid'] == 'true')
598 if ($error_data[$date_range . '_from']['is_empty'] == 'false') {
599 $errors[$date_range . '_from'] = $errorText;
607 function statistics(&$rows) {
608 $statistics = parent
::statistics($rows);
610 //fetch contributions for both date ranges from pre-existing temp tables
612 CREATE TEMPORARY TABLE civicrm_temp_civireport_repeat3
613 SELECT contact_id FROM civicrm_temp_civireport_repeat1 UNION SELECT contact_id FROM civicrm_temp_civireport_repeat2;";
614 $dao = CRM_Core_DAO
::executeQuery($sql);
617 SELECT civicrm_temp_civireport_repeat3.contact_id,
618 civicrm_temp_civireport_repeat1.total_amount_sum as contribution1_total_amount_sum,
619 civicrm_temp_civireport_repeat2.total_amount_sum as contribution2_total_amount_sum
620 FROM civicrm_temp_civireport_repeat3
621 LEFT JOIN civicrm_temp_civireport_repeat1
622 ON civicrm_temp_civireport_repeat3.contact_id = civicrm_temp_civireport_repeat1.contact_id
623 LEFT JOIN civicrm_temp_civireport_repeat2
624 ON civicrm_temp_civireport_repeat3.contact_id = civicrm_temp_civireport_repeat2.contact_id";
625 $dao = CRM_Core_DAO
::executeQuery($sql);
627 //store contributions in array 'contact_sums' for comparison
628 $contact_sums = array();
629 while ($dao->fetch()) {
630 $contact_sums[$dao->contact_id
] =
632 'contribution1_total_amount_sum' => $dao->contribution1_total_amount_sum
,
633 'contribution2_total_amount_sum' => $dao->contribution2_total_amount_sum
637 $total_distinct_contacts = count($contact_sums);
638 $number_maintained = 0;
639 $number_upgraded = 0;
640 $number_downgraded = 0;
644 foreach ($contact_sums as $uid => $row) {
645 if ($row['contribution1_total_amount_sum'] && $row['contribution2_total_amount_sum']) {
646 $change = ($row['contribution1_total_amount_sum'] - $row['contribution2_total_amount_sum']);
648 $number_maintained +
= 1;
650 $number_upgraded +
= 1;
652 $number_downgraded +
= 1;
654 elseif ($row['contribution1_total_amount_sum']) {
657 elseif ($row['contribution2_total_amount_sum']) {
662 //calculate percentages from numbers
663 $percent_maintained = ($number_maintained / $total_distinct_contacts) * 100;
664 $percent_upgraded = ($number_upgraded / $total_distinct_contacts) * 100;
665 $percent_downgraded = ($number_downgraded / $total_distinct_contacts) * 100;
666 $percent_new = ($number_new / $total_distinct_contacts) * 100;
667 $percent_lapsed = ($number_lapsed / $total_distinct_contacts) * 100;
669 //display percentages for new, lapsed, upgraded, downgraded, and maintained contributors
670 $statistics['counts']['count_new'] = array(
671 'value' => $percent_new,
672 'title' => '% New Donors',
674 $statistics['counts']['count_lapsed'] = array(
675 'value' => $percent_lapsed,
676 'title' => '% Lapsed Donors',
678 $statistics['counts']['count_upgraded'] = array(
679 'value' => $percent_upgraded,
680 'title' => '% Upgraded Donors',
682 $statistics['counts']['count_downgraded'] = array(
683 'value' => $percent_downgraded,
684 'title' => '% Downgraded Donors',
686 $statistics['counts']['count_maintained'] = array(
687 'value' => $percent_maintained,
688 'title' => '% Maintained Donors',
692 SELECT COUNT({$this->_aliases['civicrm_contribution']}1.total_amount_count ) as count,
693 SUM({$this->_aliases['civicrm_contribution']}1.total_amount_sum ) as amount,
694 ROUND(AVG({$this->_aliases['civicrm_contribution']}1.total_amount_sum), 2) as avg,
695 COUNT({$this->_aliases['civicrm_contribution']}2.total_amount_count ) as count2,
696 SUM({$this->_aliases['civicrm_contribution']}2.total_amount_sum ) as amount2,
697 ROUND(AVG({$this->_aliases['civicrm_contribution']}2.total_amount_sum), 2) as avg2,
699 $sql = "{$select} {$this->_from} {$this->_where}
702 $dao = CRM_Core_DAO
::executeQuery($sql);
704 $amount = $average = $amount2 = $average2 = array();
705 $count = $count2 = 0;
706 while ($dao->fetch()) {
708 $amount[] = CRM_Utils_Money
::format($dao->amount
, $dao->currency
)."(".$dao->count
.")";
709 $average[] = CRM_Utils_Money
::format($dao->avg
, $dao->currency
);
712 $count +
= $dao->count
;
714 $amount2[] = CRM_Utils_Money
::format($dao->amount2
, $dao->currency
)."(".$dao->count
.")";
715 $average2[] = CRM_Utils_Money
::format($dao->avg2
, $dao->currency
);
717 $count2 +
= $dao->count2
;
720 $statistics['counts']['range_one_title'] = array('title' => 'Initial Date Range:');
721 $statistics['counts']['amount'] = array(
722 'value' => implode(', ', $amount),
723 'title' => 'Total Amount',
724 'type' => CRM_Utils_Type
::T_STRING
,
726 $statistics['counts']['count'] = array(
728 'title' => 'Total Donations',
730 $statistics['counts']['avg'] = array(
731 'value' => implode(', ', $average),
732 'title' => 'Average',
733 'type' => CRM_Utils_Type
::T_STRING
,
735 $statistics['counts']['range_two_title'] = array(
736 'title' => 'Second Date Range:',
738 $statistics['counts']['amount2'] = array(
739 'value' => implode(', ', $amount2),
740 'title' => 'Total Amount',
741 'type' => CRM_Utils_Type
::T_STRING
,
743 $statistics['counts']['count2'] = array(
745 'title' => 'Total Donations',
747 $statistics['counts']['avg2'] = array(
748 'value' => implode(', ', $average2),
749 'title' => 'Average',
750 'type' => CRM_Utils_Type
::T_STRING
,
756 function postProcess() {
757 $this->beginPostProcess();
758 $create = $subSelect1 = $subSelect2 = NULL;
759 list($fromTable, $fromAlias, $fromCol) = $this->groupBy(TRUE);
760 if ($fromTable == 'civicrm_contact') {
761 $contriCol = "contact_id";
763 elseif ($fromTable == 'civicrm_contribution_type') {
764 $contriCol = "contribution_type_id";
766 elseif ($fromTable == 'civicrm_contribution') {
767 $contriCol = $fromCol;
769 elseif ($fromTable == 'civicrm_address') {
770 $contriCol = "contact_id";
772 elseif ($fromTable == 'civicrm_financial_type') {
773 $contriCol = 'financial_type_id';
774 $subSelect1 = 'contribution1.contact_id,';
775 $subSelect2 = 'contribution2.contact_id,';
776 $create = 'contact_id int unsigned,';
779 $subWhere = $this->whereContribution();
780 $subContributionQuery1 = "
781 SELECT {$subSelect1} contribution1.{$contriCol},
782 sum( contribution1.total_amount ) AS total_amount_sum,
783 count( * ) AS total_amount_count
784 FROM civicrm_contribution contribution1
786 GROUP BY contribution1.{$contriCol}";
788 $subWhere = $this->whereContribution('contribution2');
789 $subContributionQuery2 = "
790 SELECT {$subSelect2} contribution2.{$contriCol},
791 sum( contribution2.total_amount ) AS total_amount_sum,
792 count( * ) AS total_amount_count,
794 FROM civicrm_contribution contribution2
796 GROUP BY contribution2.{$contriCol}";
799 CREATE TEMPORARY TABLE civicrm_temp_civireport_repeat1 (
801 {$contriCol} int unsigned,
802 total_amount_sum int,
803 total_amount_count int
804 ) ENGINE=HEAP DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
805 CRM_Core_DAO
::executeQuery($sql);
806 $sql = "INSERT INTO civicrm_temp_civireport_repeat1 {$subContributionQuery1}";
807 CRM_Core_DAO
::executeQuery($sql);
810 CREATE TEMPORARY TABLE civicrm_temp_civireport_repeat2 (
812 {$contriCol} int unsigned,
813 total_amount_sum int,
814 total_amount_count int,
816 ) ENGINE=HEAP DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
817 CRM_Core_DAO
::executeQuery($sql);
818 $sql = "INSERT INTO civicrm_temp_civireport_repeat2 {$subContributionQuery2}";
819 CRM_Core_DAO
::executeQuery($sql);
828 $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_limit}";
829 $dao = CRM_Core_DAO
::executeQuery($sql);
831 while ($dao->fetch()) {
832 foreach ($this->_columnHeaders
as $key => $value) {
833 $rows[$count][$key] = $dao->$key;
838 // FIXME: calculate % using query
839 foreach ($rows as $uid => $row) {
840 if ($row['contribution1_total_amount_sum'] && $row['contribution2_total_amount_sum']) {
841 $rows[$uid]['change'] = number_format((($row['contribution2_total_amount_sum'] -
842 $row['contribution1_total_amount_sum']
844 ($row['contribution1_total_amount_sum']), 2
847 elseif ($row['contribution1_total_amount_sum']) {
848 $rows[$uid]['change'] = ts('Skipped Donation');
850 elseif ($row['contribution2_total_amount_sum']) {
851 $rows[$uid]['change'] = ts('New Donor');
853 if ($row['contribution1_total_amount_count']) {
854 $rows[$uid]['contribution1_total_amount_sum'] = $row['contribution1_total_amount_sum'] . " ({$row['contribution1_total_amount_count']})";
856 if ($row['contribution2_total_amount_count']) {
857 $rows[$uid]['contribution2_total_amount_sum'] = $row['contribution2_total_amount_sum'] . " ({$row['contribution2_total_amount_count']})";
860 $this->_columnHeaders
['change'] = array(
861 'title' => '% Change',
862 'type' => CRM_Utils_Type
::T_INT
,
866 list($from1, $to1) = $this->getFromTo(CRM_Utils_Array
::value("receive_date1_relative", $this->_params
),
867 CRM_Utils_Array
::value("receive_date1_from", $this->_params
),
868 CRM_Utils_Array
::value("receive_date1_to", $this->_params
)
870 $from1 = CRM_Utils_Date
::customFormat($from1, NULL, array('d'));
871 $to1 = CRM_Utils_Date
::customFormat($to1, NULL, array('d'));
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
)
877 $from2 = CRM_Utils_Date
::customFormat($from2, NULL, array('d'));
878 $to2 = CRM_Utils_Date
::customFormat($to2, NULL, array('d'));
880 $this->_columnHeaders
['contribution1_total_amount_sum']['title'] = "$from1 -<br/> $to1";
881 $this->_columnHeaders
['contribution2_total_amount_sum']['title'] = "$from2 -<br/> $to2";
882 unset($this->_columnHeaders
['contribution1_total_amount_count'],
883 $this->_columnHeaders
['contribution2_total_amount_count']
886 $this->formatDisplay($rows);
888 // assign variables to templates
889 $this->doTemplateAssignment($rows);
891 $this->endPostProcess($rows);
894 function alterDisplay(&$rows) {
895 // custom code to alter rows
896 list($from1, $to1) = $this->getFromTo(CRM_Utils_Array
::value("receive_date1_relative", $this->_params
),
897 CRM_Utils_Array
::value("receive_date1_from", $this->_params
),
898 CRM_Utils_Array
::value("receive_date1_to", $this->_params
)
900 list($from2, $to2) = $this->getFromTo(CRM_Utils_Array
::value("receive_date2_relative", $this->_params
),
901 CRM_Utils_Array
::value("receive_date2_from", $this->_params
),
902 CRM_Utils_Array
::value("receive_date2_to", $this->_params
)
907 $dateUrl .= "receive_date1_from={$from1}&";
910 $dateUrl .= "receive_date1_to={$to1}&";
913 $dateUrl .= "receive_date2_from={$from2}&";
916 $dateUrl .= "receive_date2_to={$to2}&";
919 foreach ($rows as $rowNum => $row) {
921 if (array_key_exists('address_civireport_country_id', $row)) {
922 if ($value = $row['address_civireport_country_id']) {
923 $rows[$rowNum]['address_civireport_country_id'] = CRM_Core_PseudoConstant
::country($value, FALSE);
925 $url = CRM_Report_Utils_Report
::getNextUrl('contribute/detail',
927 "country_id_op=in&country_id_value={$value}&" .
929 $this->_absoluteUrl
, $this->_id
, $this->_drilldownReport
932 $rows[$rowNum]['address_civireport_country_id_link'] = $url;
933 $rows[$rowNum]['address_civireport_country_id_hover'] = ts("View contributions for this Country.");
937 // handle state province
938 if (array_key_exists('address_civireport_state_province_id', $row)) {
939 if ($value = $row['address_civireport_state_province_id']) {
940 $rows[$rowNum]['address_civireport_state_province_id'] = CRM_Core_PseudoConstant
::stateProvince($value, FALSE);
942 $url = CRM_Report_Utils_Report
::getNextUrl('contribute/detail',
944 "state_province_id_op=in&state_province_id_value={$value}&" .
946 $this->_absoluteUrl
, $this->_id
, $this->_drilldownReport
949 $rows[$rowNum]['address_civireport_state_province_id_link'] = $url;
950 $rows[$rowNum]['address_civireport_state_province_id_hover'] = ts("View repeatDetails for this state.");
954 // convert display name to links
955 if (array_key_exists('contact_civireport_sort_name', $row) &&
956 array_key_exists('contact_civireport_id', $row)
958 $url = CRM_Report_Utils_Report
::getNextUrl('contribute/detail',
959 'reset=1&force=1&id_op=eq&id_value=' . $row['contact_civireport_id'],
960 $this->_absoluteUrl
, $this->_id
, $this->_drilldownReport
963 $rows[$rowNum]['contact_civireport_sort_name_link'] = $url;
964 $rows[$rowNum]['contact_civireport_sort_name_hover'] = ts("View Contribution details for this contact");