CRM/Report/Form/Contribute/SoftCredit.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.4                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2013                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  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.   |
  13  |                                                                    |
  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.        |
  18  |                                                                    |
  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  +--------------------------------------------------------------------+
  26 */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2013
  32  * $Id$
  33  *
  34  */
  35 class CRM_Report_Form_Contribute_SoftCredit extends CRM_Report_Form {
  36
  37   protected $_emailField = FALSE;
  38   protected $_emailFieldCredit = FALSE;
  39   protected $_phoneField = FALSE;
  40   protected $_phoneFieldCredit = FALSE;
  41   protected $_charts = array(
  42     '' => 'Tabular',
  43     'barChart' => 'Bar Chart',
  44     'pieChart' => 'Pie Chart',
  45   );
  46   public $_drilldownReport = array('contribute/detail' => 'Link to Detail Report');
  47
  48   function __construct() {
  49
  50     // Check if CiviCampaign is a) enabled and b) has active campaigns
  51     $config = CRM_Core_Config::singleton();
  52     $campaignEnabled = in_array("CiviCampaign", $config->enableComponents);
  53     if ($campaignEnabled) {
  54       $getCampaigns = CRM_Campaign_BAO_Campaign::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
  55       $this->activeCampaigns = $getCampaigns['campaigns'];
  56       asort($this->activeCampaigns);
  57     }
  58
  59     $this->_columns = array(
  60       'civicrm_contact' =>
  61       array(
  62         'dao' => 'CRM_Contact_DAO_Contact',
  63         'fields' =>
  64         array(
  65           'display_name_creditor' =>
  66           array('title' => ts('Soft Credit Name'),
  67             'name' => 'sort_name',
  68             'alias' => 'contact_civireport',
  69             'required' => TRUE,
  70             'no_repeat' => TRUE,
  71           ),
  72           'id_creditor' =>
  73           array('title' => ts('Soft Credit Id'),
  74             'name' => 'id',
  75             'alias' => 'contact_civireport',
  76             'no_display' => TRUE,
  77             'required' => TRUE,
  78           ),
  79           'display_name_constituent' =>
  80           array('title' => ts('Contributor Name'),
  81             'name' => 'sort_name',
  82             'alias' => 'constituentname',
  83             'required' => TRUE,
  84           ),
  85           'id_constituent' =>
  86           array('title' => ts('Const Id'),
  87             'name' => 'id',
  88             'alias' => 'constituentname',
  89             'no_display' => TRUE,
  90             'required' => TRUE,
  91           ),
  92         ),
  93         'filters' =>
  94         array(
  95           'sort_name' =>
  96           array(
  97             'name' => 'sort_name',
  98             'title' => ts('Soft Credit Name')
  99           ),
 100         ),
 101         'grouping' => 'contact-fields',
 102       ),
 103       'civicrm_email' =>
 104       array(
 105         'dao' => 'CRM_Core_DAO_Email',
 106         'fields' =>
 107         array(
 108           'email_creditor' =>
 109           array('title' => ts('Soft Credit Email'),
 110             'name' => 'email',
 111             'alias' => 'emailcredit',
 112             'default' => TRUE,
 113             'no_repeat' => TRUE,
 114           ),
 115           'email_constituent' =>
 116           array('title' => ts('Contributor\'s Email'),
 117             'name' => 'email',
 118             'alias' => 'emailconst',
 119           ),
 120         ),
 121         'grouping' => 'contact-fields',
 122       ),
 123       'civicrm_phone' =>
 124       array(
 125         'dao' => 'CRM_Core_DAO_Phone',
 126         'fields' =>
 127         array(
 128           'phone_creditor' =>
 129           array('title' => ts('Soft Credit Phone'),
 130             'name' => 'phone',
 131             'alias' => 'pcredit',
 132             'default' => TRUE,
 133           ),
 134           'phone_constituent' =>
 135           array('title' => ts('Contributor\'s Phone'),
 136             'name' => 'phone',
 137             'alias' => 'pconst',
 138             'no_repeat' => TRUE,
 139           ),
 140         ),
 141         'grouping' => 'contact-fields',
 142       ),
 143       'civicrm_financial_type' =>
 144       array('dao' => 'CRM_Financial_DAO_FinancialType',
 145         'fields' => array('financial_type' => null,),
 146         'filters' =>
 147         array(
 148           'id' =>
 149           array(
 150             'name' => 'id',
 151             'title' => ts('Financial Type'),
 152             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 153             'options' => CRM_Contribute_PseudoConstant::financialType()
 154           ),
 155         ),
 156         'grouping' => 'softcredit-fields',
 157       ),
 158       'civicrm_contribution' =>
 159       array(
 160         'dao' => 'CRM_Contribute_DAO_Contribution',
 161         'fields' =>
 162         array(
 163           'contribution_source' => NULL,
 164           'currency' => array(
 165             'required' => TRUE,
 166             'no_display' => TRUE,
 167           ),
 168           'total_amount' =>
 169           array('title' => ts('Amount Statistics'),
 170             'default' => TRUE,
 171             'statistics' =>
 172             array('sum' => ts('Aggregate Amount'),
 173               'count' => ts('Donations'),
 174               'avg' => ts('Average'),
 175             ),
 176           ),
 177         ),
 178         'grouping' => 'softcredit-fields',
 179         'filters' =>
 180         array(
 181           'receive_date' =>
 182           array('operatorType' => CRM_Report_Form::OP_DATE),
 183           'currency' =>
 184           array('title' => 'Currency',
 185             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 186             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 187             'default' => NULL,
 188             'type' => CRM_Utils_Type::T_STRING,
 189           ),
 190           'contribution_status_id' =>
 191           array('title' => ts('Donation Status'),
 192             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 193             'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 194             'default' => array(1),
 195           ),
 196           'total_amount' =>
 197           array('title' => ts('Donation Amount'),
 198           ),
 199         ),
 200       ),
 201       'civicrm_contribution_soft' =>
 202       array(
 203         'dao' => 'CRM_Contribute_DAO_ContributionSoft',
 204         'fields' =>
 205         array(
 206           'contribution_id' =>
 207           array('title' => ts('Contribution ID'),
 208             'no_display' => TRUE,
 209             'default' => TRUE,
 210           ),
 211           'id' =>
 212           array(
 213             'default' => TRUE,
 214             'no_display' => TRUE,
 215           ),
 216         ),
 217         'grouping' => 'softcredit-fields',
 218       ),
 219       'civicrm_group' =>
 220       array(
 221         'dao' => 'CRM_Contact_DAO_GroupContact',
 222         'alias' => 'cgroup',
 223         'filters' =>
 224         array(
 225           'gid' =>
 226           array(
 227             'name' => 'group_id',
 228             'title' => ts('Soft Credit Group'),
 229             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 230             'group' => TRUE,
 231             'options' => CRM_Core_PseudoConstant::group(),
 232           ),
 233         ),
 234       ),
 235     );
 236
 237     // If we have a campaign, build out the relevant elements
 238     if ($campaignEnabled && !empty($this->activeCampaigns)) {
 239       $this->_columns['civicrm_contribution']['fields']['campaign_id'] = array(
 240         'title' => ts('Campaign'),
 241         'default' => 'false',
 242       );
 243       $this->_columns['civicrm_contribution']['filters']['campaign_id'] = array('title' => ts('Campaign'),
 244         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 245         'options' => $this->activeCampaigns,
 246       );
 247     }
 248
 249     $this->_tagFilter = TRUE;
 250
 251     $this->_currencyColumn = 'civicrm_contribution_currency';
 252     parent::__construct();
 253   }
 254
 255   function preProcess() {
 256     parent::preProcess();
 257   }
 258
 259   function select() {
 260     $select = array();
 261     $this->_columnHeaders = array();
 262     foreach ($this->_columns as $tableName => $table) {
 263       if (array_key_exists('fields', $table)) {
 264         foreach ($table['fields'] as $fieldName => $field) {
 265           if (CRM_Utils_Array::value('required', $field) ||
 266             CRM_Utils_Array::value($fieldName, $this->_params['fields'])
 267           ) {
 268
 269             // include email column if set
 270             if ($tableName == 'civicrm_email') {
 271               $this->_emailField = TRUE;
 272               $this->_emailFieldCredit = TRUE;
 273             }
 274             elseif ($tableName == 'civicrm_email_creditor') {
 275               $this->_emailFieldCredit = TRUE;
 276             }
 277
 278             // include phone columns if set
 279             if ($tableName == 'civicrm_phone') {
 280               $this->_phoneField = TRUE;
 281               $this->_phoneFieldCredit = TRUE;
 282             }
 283             elseif ($tableName == 'civicrm_phone_creditor') {
 284               $this->_phoneFieldCredit = TRUE;
 285             }
 286
 287             // only include statistics columns if set
 288             if (CRM_Utils_Array::value('statistics', $field)) {
 289               foreach ($field['statistics'] as $stat => $label) {
 290                 switch (strtolower($stat)) {
 291                   case 'sum':
 292                     $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 293                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 294                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
 295                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 296                     break;
 297
 298                   case 'count':
 299                     $select[] = "COUNT({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 300                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
 301                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 302                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 303                     break;
 304
 305                   case 'avg':
 306                     $select[] = "ROUND(AVG({$field['dbAlias']}),2) as {$tableName}_{$fieldName}_{$stat}";
 307                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
 308                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 309                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 310                     break;
 311                 }
 312               }
 313             }
 314             else {
 315               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 316               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 317               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 318             }
 319           }
 320         }
 321       }
 322     }
 323
 324     $this->_select = 'SELECT ' . implode(', ', $select) . ' ';
 325   }
 326
 327   static function formRule($fields, $files, $self) {
 328     $errors = $grouping = array();
 329     return $errors;
 330   }
 331
 332   function from() {
 333     $alias_constituent = 'constituentname';
 334     $alias_creditor    = 'contact_civireport';
 335     $this->_from       = "
 336         FROM  civicrm_contribution {$this->_aliases['civicrm_contribution']}
 337               INNER JOIN civicrm_contribution_soft {$this->_aliases['civicrm_contribution_soft']}
 338                          ON {$this->_aliases['civicrm_contribution_soft']}.contribution_id =
 339                             {$this->_aliases['civicrm_contribution']}.id
 340               INNER JOIN civicrm_contact {$alias_constituent}
 341                          ON {$this->_aliases['civicrm_contribution']}.contact_id =
 342                             {$alias_constituent}.id
 343               LEFT  JOIN civicrm_financial_type  {$this->_aliases['civicrm_financial_type']}
 344                          ON {$this->_aliases['civicrm_contribution']}.financial_type_id =
 345                             {$this->_aliases['civicrm_financial_type']}.id
 346               LEFT  JOIN civicrm_contact {$alias_creditor}
 347                          ON {$this->_aliases['civicrm_contribution_soft']}.contact_id =
 348                             {$alias_creditor}.id
 349               {$this->_aclFrom} ";
 350
 351     // include Constituent email field if email column is to be included
 352     if ($this->_emailField) {
 353       $alias = 'emailconst';
 354       $this->_from .= "
 355             LEFT JOIN civicrm_email {$alias}
 356                       ON {$alias_constituent}.id =
 357                          {$alias}.contact_id   AND
 358                          {$alias}.is_primary = 1\n";
 359     }
 360
 361     // include  Creditors email field if email column is to be included
 362     if ($this->_emailFieldCredit) {
 363       $alias = 'emailcredit';
 364       $this->_from .= "
 365             LEFT JOIN civicrm_email {$alias}
 366                       ON {$alias_creditor}.id =
 367                          {$alias}.contact_id  AND
 368                          {$alias}.is_primary = 1\n";
 369     }
 370
 371     // include  Constituents phone field if email column is to be included
 372     if ($this->_phoneField) {
 373       $alias = 'pconst';
 374       $this->_from .= "
 375             LEFT JOIN civicrm_phone {$alias}
 376                       ON {$alias_constituent}.id =
 377                          {$alias}.contact_id  AND
 378                          {$alias}.is_primary = 1\n";
 379     }
 380
 381     // include  Creditors phone field if email column is to be included
 382     if ($this->_phoneFieldCredit) {
 383       $alias = 'pcredit';
 384       $this->_from .= "
 385             LEFT JOIN civicrm_phone pcredit
 386                       ON {$alias_creditor}.id =
 387                          {$alias}.contact_id  AND
 388                          {$alias}.is_primary = 1\n";
 389     }
 390   }
 391
 392   function groupBy() {
 393     $this->_rollup     = 'WITH ROLLUP';
 394     $this->_groupBy    = "
 395 GROUP BY {$this->_aliases['civicrm_contribution_soft']}.contact_id, constituentname.id {$this->_rollup}";
 396   }
 397
 398   function where() {
 399     parent::where();
 400     $this->_where .= " AND {$this->_aliases['civicrm_contribution']}.is_test = 0 ";
 401   }
 402
 403   function statistics(&$rows) {
 404     $statistics = parent::statistics($rows);
 405
 406     $select = "
 407         SELECT COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as count,
 408                SUM({$this->_aliases['civicrm_contribution']}.total_amount ) as amount,
 409                ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as avg,
 410                {$this->_aliases['civicrm_contribution']}.currency as currency
 411         ";
 412
 413     $sql = "{$select} {$this->_from} {$this->_where}
 414 GROUP BY   {$this->_aliases['civicrm_contribution']}.currency
 415 ";
 416
 417     $dao = CRM_Core_DAO::executeQuery($sql);
 418     $count = 0;
 419     while ($dao->fetch()) {
 420       $totalAmount[] = CRM_Utils_Money::format($dao->amount, $dao->currency).'('.$dao->count.')';
 421       $average[] =   CRM_Utils_Money::format($dao->avg, $dao->currency);
 422       $count += $dao->count;
 423     }
 424     $statistics['counts']['amount'] = array(
 425       'title' => ts('Total Amount'),
 426       'value' => implode(',  ', $totalAmount),
 427       'type' => CRM_Utils_Type::T_STRING,
 428     );
 429     $statistics['counts']['count'] = array(
 430       'title' => ts('Total Donations'),
 431       'value' => $count,
 432     );
 433     $statistics['counts']['avg'] = array(
 434       'title' => ts('Average'),
 435       'value' => implode(',  ', $average),
 436       'type' => CRM_Utils_Type::T_STRING,
 437     );
 438
 439     return $statistics;
 440   }
 441
 442   function postProcess() {
 443     $this->beginPostProcess();
 444
 445     $this->buildACLClause(array('constituentname', 'contact_civireport'));
 446     $sql = $this->buildQuery();
 447
 448     $dao   = CRM_Core_DAO::executeQuery($sql);
 449     $rows  = $graphRows = array();
 450     $count = 0;
 451     while ($dao->fetch()) {
 452       $row = array();
 453       foreach ($this->_columnHeaders as $key => $value) {
 454         $row[$key] = $dao->$key;
 455       }
 456       $rows[] = $row;
 457     }
 458     $this->formatDisplay($rows);
 459
 460     // to hide the contact ID field from getting displayed
 461     unset($this->_columnHeaders['civicrm_contact_id_constituent']);
 462     unset($this->_columnHeaders['civicrm_contact_id_creditor']);
 463
 464     // assign variables to templates
 465     $this->doTemplateAssignment($rows);
 466     $this->endPostProcess($rows);
 467   }
 468
 469   function alterDisplay(&$rows) {
 470     // custom code to alter rows
 471
 472     $entryFound    = FALSE;
 473     $dispname_flag = $phone_flag = $email_flag = 0;
 474     $prev_email    = $prev_dispname = $prev_phone = NULL;
 475
 476     foreach ($rows as $rowNum => $row) {
 477       // Link constituent (contributor) to contribution detail
 478       if (array_key_exists('civicrm_contact_display_name_constituent', $row) &&
 479         array_key_exists('civicrm_contact_id_constituent', $row)
 480       ) {
 481
 482         $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
 483           'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id_constituent'],
 484           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 485         );
 486         $rows[$rowNum]['civicrm_contact_display_name_constituent_link'] = $url;
 487         $rows[$rowNum]['civicrm_contact_display_name_constituent_hover'] = ts('List all direct contribution(s) from this contact.');
 488         $entryFound = TRUE;
 489       }
 490
 491       // make subtotals look nicer
 492       if (array_key_exists('civicrm_contact_id_constituent', $row) &&
 493         !$row['civicrm_contact_id_constituent']
 494       ) {
 495         $this->fixSubTotalDisplay($rows[$rowNum], $this->_statFields);
 496         $entryFound = TRUE;
 497       }
 498
 499       // convert campaign_id to campaign title
 500       if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
 501         if ($value = $row['civicrm_contribution_campaign_id']) {
 502           $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->activeCampaigns[$value];
 503           $entryFound = TRUE;
 504         }
 505       }
 506
 507       // skip looking further in rows, if first row itself doesn't
 508       // have the column we need
 509       if (!$entryFound) {
 510         break;
 511       }
 512     }
 513
 514     $this->removeDuplicates($rows);
 515   }
 516 }
 517