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