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