3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.1 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2010 |
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-2010
36 require_once 'CRM/Report/Form.php';
37 require_once 'CRM/Contribute/PseudoConstant.php';
38 require_once 'CRM/Core/OptionGroup.php';
39 require_once 'CRM/Event/BAO/Participant.php';
40 require_once 'CRM/Contact/BAO/Contact.php';
41 class CRM_Report_Form_Contribute_Baykeeper
extends CRM_Report_Form
{
42 protected $_addressField = FALSE;
44 protected $_emailField = FALSE;
46 protected $_summary = NULL;
48 protected $_customGroupExtends = array('Contact', 'Contribution');
50 function __construct() {
51 $this->_columns
= array('civicrm_contact' =>
52 array('dao' => 'CRM_Contact_DAO_Contact',
54 array('display_name' =>
55 array('title' => ts('Contact Name'),
60 array('no_display' => FALSE,
61 'title' => ts('Contact ID'),
65 array('title' => ts('Contact ID'),
71 'addressee_display' => array('title' => ts('Addressee Name')),
72 'postal_greeting_display' => array('title' => ts('Greeting')),
73 'display_name_creditor' =>
74 array('title' => ts('Soft Credit Name'),
75 'name' => 'display_name',
76 'alias' => 'soft_credit',
80 array('title' => ts('Soft Credit Id'),
82 'alias' => 'soft_credit',
85 array('title' => ts('Employer Name'),
86 'name' => 'display_name',
87 'alias' => 'employer_company',
90 array('title' => ts('Employer Id'),
91 'name' => 'employer_id',
93 'do_not_mail' => array('title' => ts('Do Not Mail')),
97 array('title' => ts('Contact Name'),
101 array('title' => ts('Contact ID'),
102 'no_display' => TRUE,
105 'grouping' => 'contact-fields',
108 array('dao' => 'CRM_Core_DAO_Email',
111 array('title' => ts('Email'),
116 'grouping' => 'contact-fields',
119 array('dao' => 'CRM_Core_DAO_Phone',
122 array('title' => ts('Phone'),
127 'grouping' => 'contact-fields',
130 array('dao' => 'CRM_Core_DAO_Address',
132 array('street_address' => NULL,
133 'supplemental_address_1' => array('title' => ts('Sup Address 1'),
135 'supplemental_address_2' => array('title' => ts('Sup Address 2'),
138 'postal_code' => NULL,
139 'location_type_id' => array('title' => ts('Location Type ID'),
141 'state_province_id' =>
142 array('title' => ts('State/Province'),
145 array('title' => ts('Country'),
149 'grouping' => 'contact-fields',
151 array('country_id' =>
152 array('title' => ts('Country'),
153 'type' => CRM_Utils_Type
::T_INT
,
154 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
155 'options' => CRM_Core_PseudoConstant
::country(),
157 'state_province_id' =>
158 array('title' => ts('State/Province'),
159 'type' => CRM_Utils_Type
::T_INT
,
160 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
161 'options' => CRM_Core_PseudoConstant
::stateProvince(),
165 'civicrm_contribution' =>
166 array('dao' => 'CRM_Contribute_DAO_Contribution',
169 'contribution_id' => array(
171 'no_display' => TRUE,
174 'contribution_type_id' => array('title' => ts('Contribution Type'),
178 'receive_date' => array('default' => TRUE),
179 'receipt_date' => NULL,
180 'source' => array('title' => ts('Source')),
181 'fee_amount' => NULL,
182 'net_amount' => NULL,
183 'non_deductible_amount' => array('title' => ts('Non Deductible Amount')),
184 'total_amount' => array('title' => ts('Total Amount'),
187 'honor_contact_id' => array('title' => ts('Honor Contact ID'),
189 'honor_type_id' => array('title' => ts('Hon/Mem Type')),
192 array('receive_date' =>
193 array('operatorType' => CRM_Report_Form
::OP_DATE
),
194 'contribution_type_id' =>
195 array('title' => ts('Contribution Type'),
196 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
197 'options' => CRM_Contribute_PseudoConstant
::contributionType(),
199 'contribution_status_id' =>
200 array('title' => ts('Contribution Status'),
201 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
202 'options' => CRM_Contribute_PseudoConstant
::contributionStatus(),
205 array('title' => ts('Contribution Amount')),
207 'grouping' => 'contri-fields',
209 'civicrm_contact_hon_mem' =>
210 array('dao' => 'CRM_Contact_DAO_Contact',
211 'alias' => 'hon_mem',
213 array('id' => array('title' => ts('Hon/Mem Contact ID'),
215 'no_display' => TRUE,
217 'display_name' => array('title' => ts('In Honor Of'),
221 'grouping' => 'contri-fields',
224 array('dao' => 'CRM_Core_DAO_Note',
228 array('title' => ts('Note'),
230 'no_repeat' => FALSE,
233 'grouping' => 'contri-fields',
235 'civicrm_contribution_soft' =>
236 array('dao' => 'CRM_Contribute_DAO_ContributionSoft',
238 array('contribution_id' =>
239 array('title' => ts('Contribution ID'),
240 'no_display' => TRUE,
244 array('title' => ts('Contact ID'),
245 'no_display' => TRUE,
249 array('default' => TRUE,
250 'no_display' => TRUE,
253 'grouping' => 'softcredit-fields',
255 'civicrm_group_field' =>
256 array('dao' => 'CRM_Contact_DAO_Group',
259 array('title' => ts('Groups')),
263 array('dao' => 'CRM_Contact_DAO_GroupContact',
270 array('name' => 'group_id',
271 'title' => ts('Group'),
272 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
274 'options' => CRM_Core_PseudoConstant
::group(),
278 'civicrm_contribution_ordinality' =>
279 array('dao' => 'CRM_Contribute_DAO_Contribution',
280 'alias' => 'cordinality',
282 array('ordinality' =>
283 array('title' => ts('Contribution Ordinality'),
284 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
285 'options' => array(0 => 'First by Contributor',
286 1 => 'Second or Later by Contributor',
293 $this->_options
= array('first_contribution' => array('title' => ts('First Contribution'),
294 'type' => 'checkbox',
296 'last_contribution' => array('title' => ts('Last Contribution'),
297 'type' => 'checkbox',
299 'include_nondonors' => array('title' => ts('Include non-donors?'),
300 'type' => 'checkbox',
304 $this->_tagFilter
= TRUE;
305 parent
::__construct();
308 function preProcess() {
309 parent
::preProcess();
315 $this->_columnHeaders
= array();
316 foreach ($this->_columns
as $tableName => $table) {
317 if (array_key_exists('fields', $table)) {
318 foreach ($table['fields'] as $fieldName => $field) {
319 if (CRM_Utils_Array
::value('required', $field) ||
320 CRM_Utils_Array
::value($fieldName, $this->_params
['fields'])
322 if ($tableName == 'civicrm_address') {
323 $this->_addressField
= TRUE;
325 elseif ($tableName == 'civicrm_email') {
326 $this->_emailField
= TRUE;
329 if ( $tableName == 'civicrm_group_field' && $fieldName == 'title' ) {
330 $select[] = "GROUP_CONCAT(DISTINCT {$field['dbAlias']} ORDER BY {$field['dbAlias']} ) as {$tableName}_{$fieldName}";
332 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
337 // only include statistics columns if set
338 if (CRM_Utils_Array
::value('statistics', $field)) {
339 foreach ($field['statistics'] as $stat => $label) {
340 switch (strtolower($stat)) {
342 $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
343 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
344 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
345 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
349 $select[] = "COUNT({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
350 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
351 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
355 $select[] = "ROUND(AVG({$field['dbAlias']}),2) as {$tableName}_{$fieldName}_{$stat}";
356 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
357 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
358 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
363 elseif ($tableName == 'civicrm_group_field' && $fieldName == 'title') {
364 $select[] = " GROUP_CONCAT(DISTINCT {$field['dbAlias']} ORDER BY {$field['dbAlias']} SEPARATOR ' | <br>') as {$tableName}_{$fieldName} ";
365 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = $field['title'];
366 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array
::value('type', $field);
369 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
370 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = $field['title'];
371 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array
::value('type', $field);
378 // insert first and last contribution at end
379 if (CRM_Utils_Array
::value('first_contribution', $this->_params
['options'])) {
380 $select[] = " '' as first_contribution";
381 $this->_columnHeaders
['first_contribution']['title'] = ts('First Contribution');
384 if (CRM_Utils_Array
::value('last_contribution', $this->_params
['options'])) {
385 $select[] = " '' as last_contribution";
386 $this->_columnHeaders
['last_contribution']['title'] = ts('Last Contribution');
390 $this->_select
= "SELECT " . implode(', ', $select) . " ";
394 $alias_constituent = 'constituentname';
395 $alias_creditor = 'soft_credit';
396 $alias_employer = "employer_company";
400 $hackValue = CRM_Utils_Array
::value('include_nondonors', $this->_params
['options'], 0);
401 $contribJoin = $hackValue ?
"LEFT" : "INNER";
404 FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}
405 $contribJoin JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
406 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND {$this->_aliases['civicrm_contribution']}.is_test = 0
407 $contribJoin JOIN (SELECT c.id, IF(COUNT(oc.id) = 0, 0, 1) AS ordinality FROM civicrm_contribution c LEFT JOIN civicrm_contribution oc ON c.contact_id = oc.contact_id AND oc.receive_date < c.receive_date GROUP BY c.id) {$this->_aliases['civicrm_contribution_ordinality']}
408 ON {$this->_aliases['civicrm_contribution_ordinality']}.id = {$this->_aliases['civicrm_contribution']}.id
409 LEFT JOIN civicrm_note {$this->_aliases['civicrm_note']}
410 ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_note']}.contact_id AND
411 {$this->_aliases['civicrm_contribution']}.id = {$this->_aliases['civicrm_note']}.entity_id )
412 LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
413 ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
414 {$this->_aliases['civicrm_phone']}.is_primary = 1)
415 LEFT JOIN civicrm_contact {$alias_employer}
416 ON {$this->_aliases['civicrm_contact']}.employer_id =
418 LEFT JOIN civicrm_contribution_soft {$this->_aliases['civicrm_contribution_soft']}
419 ON {$this->_aliases['civicrm_contribution_soft']}.contribution_id =
420 {$this->_aliases['civicrm_contribution']}.id
421 LEFT JOIN civicrm_contact {$alias_creditor}
422 ON {$this->_aliases['civicrm_contribution_soft']}.contact_id =
424 LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact_hon_mem']}
425 ON {$this->_aliases['civicrm_contribution']}.honor_contact_id = {$this->_aliases['civicrm_contact_hon_mem']}.id
427 // add group - concatenated
428 $this->_from
.= " LEFT JOIN civicrm_group_contact gc ON {$this->_aliases['civicrm_contact']}.id = gc.contact_id AND gc.status = 'Added'
429 LEFT JOIN civicrm_group {$this->_aliases['civicrm_group_field']} ON {$this->_aliases['civicrm_group_field']}.id = gc.group_id ";
431 if ($this->_addressField
OR (!empty($this->_params
['state_province_id_value']) OR !empty($this->_params
['country_id_value']))) {
433 LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
434 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND
435 {$this->_aliases['civicrm_address']}.is_primary = 1\n";
438 if ($this->_emailField
) {
440 LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
441 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND
442 {$this->_aliases['civicrm_email']}.is_primary = 1\n";
447 $this->_groupBy
= " GROUP BY {$this->_aliases['civicrm_contact']}.id, {$this->_aliases['civicrm_contribution']}.id ";
451 $this->_orderBy
= " ORDER BY {$this->_aliases['civicrm_contact']}.id, {$this->_aliases['civicrm_contribution']}.receive_date ";
454 function statistics(&$rows) {
455 $statistics = parent
::statistics($rows);
456 // because the query returns groups, the amount is multiplied by the number of groups a contact is in
457 // that's why this is disabled
458 /* SUM( {$this->_aliases['civicrm_contribution']}.total_amount ) as amount, */
462 SELECT COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as count,
463 ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as avg
465 $sql = "{$select} {$this->_from} {$this->_where}";
466 $dao = CRM_Core_DAO
::executeQuery($sql);
469 // because the query returns groups, the amount is multiplied by the number of groups a contact is in
470 // that's why this is disabled
471 /* SUM( {$this->_aliases['civicrm_contribution']}.total_amount ) as amount, */
475 $statistics['counts']['amount'] = array( 'value' => $dao->amount,
476 'title' => 'Total Amount',
477 'type' => CRM_Utils_Type::T_MONEY );
478 $statistics['counts']['avg'] = array( 'value' => $dao->avg,
479 'title' => 'Average',
480 'type' => CRM_Utils_Type::T_MONEY );
487 function postProcess() {
488 // get the acl clauses built before we assemble the query
489 $this->buildACLClause($this->_aliases
['civicrm_contact']);
490 parent
::postProcess();
493 function alterDisplay(&$rows) {
495 require_once 'CRM/Contribute/BAO/Contribution/Utils.php';
496 require_once 'CRM/Utils/Money.php';
497 require_once 'CRM/Utils/Date.php';
499 $config = &CRM_Core_Config
::singleton();
501 // custom code to alter rows
502 $checkList = array();
504 $display_flag = $prev_cid = $cid = 0;
505 $contributionTypes = CRM_Contribute_PseudoConstant
::contributionType();
507 foreach ($rows as $rowNum => $row) {
508 if (!empty($this->_noRepeats
) &&
509 $this->_outputMode
!= 'csv'
511 // don't repeat contact details if its same as the previous row
512 if (array_key_exists('civicrm_contact_id', $row)) {
513 if ($cid = $row['civicrm_contact_id']) {
518 if ($prev_cid == $cid) {
529 foreach ($row as $colName => $colVal) {
530 if (in_array($colName, $this->_noRepeats
)) {
531 unset($rows[$rowNum][$colName]);
540 if (array_key_exists('first_contribution', $row) ||
541 array_key_exists('last_contribution', $row)
543 $details = CRM_Contribute_BAO_Contribution_Utils
::getFirstLastDetails($row['civicrm_contact_id']);
544 if ($details['first']) {
545 $rows[$rowNum]['first_contribution'] = CRM_Utils_Money
::format($details['first']['total_amount']) . ' - ' . CRM_Utils_Date
::customFormat($details['first']['receive_date'], $config->dateformatFull
);
547 if ($details['last']) {
548 $rows[$rowNum]['last_contribution'] = CRM_Utils_Money
::format($details['last']['total_amount']) . ' - ' . CRM_Utils_Date
::customFormat($details['last']['receive_date'], $config->dateformatFull
);
552 // handle state province
553 if (array_key_exists('civicrm_address_state_province_id', $row)) {
554 if ($value = $row['civicrm_address_state_province_id']) {
555 $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant
::stateProvince($value, FALSE);
557 $url = CRM_Report_Utils_Report
::getNextUrl('contribute/detail',
559 "state_province_id_op=in&state_province_id_value={$value}",
560 $this->_absoluteUrl
, $this->_id
562 $rows[$rowNum]['civicrm_address_state_province_id_link'] = $url;
563 $rows[$rowNum]['civicrm_address_state_province_id_hover'] = ts("List all contribution(s) for this State.");
569 if (array_key_exists('civicrm_address_country_id', $row)) {
570 if ($value = $row['civicrm_address_country_id']) {
571 $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant
::country($value, FALSE);
573 $url = CRM_Report_Utils_Report
::getNextUrl('contribute/detail',
575 "country_id_op=in&country_id_value={$value}",
576 $this->_absoluteUrl
, $this->_id
578 $rows[$rowNum]['civicrm_address_country_id_link'] = $url;
579 $rows[$rowNum]['civicrm_address_country_id_hover'] = ts("List all contribution(s) for this Country.");
585 // convert display name to links
586 if (array_key_exists('civicrm_contact_display_name', $row) &&
587 CRM_Utils_Array
::value('civicrm_contact_display_name', $rows[$rowNum]) &&
588 array_key_exists('civicrm_contact_id', $row)
590 $url = CRM_Utils_System
::url("civicrm/contact/view",
591 'reset=1&cid=' . $row['civicrm_contact_id'],
594 $rows[$rowNum]['civicrm_contact_display_name_link'] = $url;
595 $rows[$rowNum]['civicrm_contact_display_name_hover'] = ts("View Contact Summary for this Contact.");
598 // convert soft credit id to link
599 if (array_key_exists('soft_credit_display_name', $row) &&
600 CRM_Utils_Array
::value('soft_credit_display_name', $rows[$rowNum]) &&
601 array_key_exists('id', $row)
603 $url = CRM_Utils_System
::url("civicrm/contact/view",
604 'reset=1&cid=' . $row['id'],
607 $rows[$rowNum]['soft_credit_display_name_creditor_link'] = $url;
608 $rows[$rowNum]['soft_credit_display_name_creditor_hover'] = ts("View Contact Summary for this Soft Credit.");
611 // convert hon/mem contact to link
612 if (array_key_exists('civicrm_contact_hon_mem_display_name', $row) &&
613 CRM_Utils_Array
::value('civicrm_contribution_honor_contact_id', $rows[$rowNum]) &&
614 array_key_exists('civicrm_contribution_honor_contact_id', $row)
616 $url = CRM_Utils_System
::url("civicrm/contact/view",
617 'reset=1&cid=' . $row['civicrm_contribution_honor_contact_id'],
620 $rows[$rowNum]['civicrm_contact_hon_mem_display_name_link'] = $url;
621 $rows[$rowNum]['civicrm_contact_hon_mem_display_name_hover'] = ts("View Contact Summary for this Honor/Memory Contribution.");
624 // honor of/memory of type
625 if ($value = CRM_Utils_Array
::value('civicrm_contribution_honor_type_id', $row) &&
626 CRM_Utils_Array
::value('civicrm_contribution_honor_type_id', $rows[$rowNum])
628 // rather than do a join, just change the output here, since there these values are pretty static
631 if ($rows[$rowNum]['civicrm_contribution_honor_type_id'] == 1) {
632 $rows[$rowNum]['civicrm_contribution_honor_type_id'] = "In Honor Of";
634 elseif ($rows[$rowNum]['civicrm_contribution_honor_type_id'] == 2) {
635 $rows[$rowNum]['civicrm_contribution_honor_type_id'] = "In Memory Of";
638 $rows[$rowNum]['civicrm_contribution_honor_type_id'] = "n/a";
642 if ($value = CRM_Utils_Array
::value('civicrm_contribution_contribution_type_id', $row)) {
643 $rows[$rowNum]['civicrm_contribution_contribution_type_id'] = $contributionTypes[$value];
647 if (($value = CRM_Utils_Array
::value('civicrm_contribution_total_amount', $row)) &&
648 CRM_Core_Permission
::check('access CiviContribute')
650 $url = CRM_Utils_System
::url("civicrm/contact/view/contribution",
652 $row['civicrm_contribution_contribution_id'] .
654 $row['civicrm_contact_id'] .
655 "&action=view&context=contribution&selectedChild=contribute",
658 $rows[$rowNum]['civicrm_contribution_total_amount_link'] = $url;
659 $rows[$rowNum]['civicrm_contribution_total_amount_hover'] = ts("View Details of this Contribution.");
663 // skip looking further in rows, if first row itself doesn't
664 // have the column we need