CRM/Report/Form/Contribute/SoftCredit.php

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