CRM/Report/Form/Contribute/SoftCredit.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  */
  17 class CRM_Report_Form_Contribute_SoftCredit extends CRM_Report_Form {
  18
  19   protected $_emailField = FALSE;
  20   protected $_emailFieldCredit = FALSE;
  21   protected $_phoneField = FALSE;
  22   protected $_phoneFieldCredit = FALSE;
  23
  24   protected $_customGroupExtends = [
  25     'Contact',
  26     'Individual',
  27     'Contribution',
  28   ];
  29
  30   public $_drilldownReport = ['contribute/detail' => 'Link to Detail Report'];
  31
  32   /**
  33    * This report has not been optimised for group filtering.
  34    *
  35    * The functionality for group filtering has been improved but not
  36    * all reports have been adjusted to take care of it. This report has not
  37    * and will run an inefficient query until fixed.
  38    *
  39    * @var bool
  40    * @see https://issues.civicrm.org/jira/browse/CRM-19170
  41    */
  42   protected $groupFilterNotOptimised = TRUE;
  43
  44   /**
  45    */
  46   public function __construct() {
  47     $this->optimisedForOnlyFullGroupBy = FALSE;
  48
  49     $this->_columns = [
  50       'civicrm_contact' => [
  51         'dao' => 'CRM_Contact_DAO_Contact',
  52         'fields' => [
  53           'display_name_creditor' => [
  54             'title' => ts('Soft Credit Name'),
  55             'name' => 'sort_name',
  56             'alias' => 'contact_civireport',
  57             'required' => TRUE,
  58             'no_repeat' => TRUE,
  59           ],
  60           'id_creditor' => [
  61             'title' => ts('Soft Credit Id'),
  62             'name' => 'id',
  63             'alias' => 'contact_civireport',
  64             'no_display' => TRUE,
  65             'required' => TRUE,
  66           ],
  67           'display_name_constituent' => [
  68             'title' => ts('Contributor Name'),
  69             'name' => 'sort_name',
  70             'alias' => 'constituentname',
  71             'required' => TRUE,
  72           ],
  73           'id_constituent' => [
  74             'title' => ts('Const Id'),
  75             'name' => 'id',
  76             'alias' => 'constituentname',
  77             'no_display' => TRUE,
  78             'required' => TRUE,
  79           ],
  80           'first_name' => [
  81             'title' => ts('First Name'),
  82           ],
  83           'middle_name' => [
  84             'title' => ts('Middle Name'),
  85           ],
  86           'last_name' => [
  87             'title' => ts('Last Name'),
  88           ],
  89           'gender_id' => [
  90             'title' => ts('Gender'),
  91           ],
  92           'birth_date' => [
  93             'title' => ts('Birth Date'),
  94           ],
  95           'age' => [
  96             'title' => ts('Age'),
  97             'dbAlias' => 'TIMESTAMPDIFF(YEAR, contact_civireport.birth_date, CURDATE())',
  98           ],
  99           'contact_type' => [
 100             'title' => ts('Contact Type'),
 101           ],
 102           'contact_sub_type' => [
 103             'title' => ts('Contact Subtype'),
 104           ],
 105         ],
 106         'grouping' => 'contact-fields',
 107         'order_bys' => [
 108           'sort_name' => [
 109             'title' => ts('Last Name, First Name'),
 110             'default' => '1',
 111             'default_weight' => '0',
 112             'default_order' => 'ASC',
 113           ],
 114           'first_name' => [
 115             'name' => 'first_name',
 116             'title' => ts('First Name'),
 117           ],
 118           'gender_id' => [
 119             'name' => 'gender_id',
 120             'title' => ts('Gender'),
 121           ],
 122           'birth_date' => [
 123             'name' => 'birth_date',
 124             'title' => ts('Birth Date'),
 125           ],
 126           'age_at_event' => [
 127             'name' => 'age_at_event',
 128             'title' => ts('Age at Event'),
 129           ],
 130           'contact_type' => [
 131             'title' => ts('Contact Type'),
 132           ],
 133           'contact_sub_type' => [
 134             'title' => ts('Contact Subtype'),
 135           ],
 136         ],
 137         'filters' => [
 138           'sort_name' => [
 139             'name' => 'sort_name',
 140             'title' => ts('Soft Credit Name'),
 141           ],
 142           'gender_id' => [
 143             'title' => ts('Gender'),
 144             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 145             'options' => CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'gender_id'),
 146           ],
 147           'birth_date' => [
 148             'title' => ts('Birth Date'),
 149             'operatorType' => CRM_Report_Form::OP_DATE,
 150           ],
 151           'contact_type' => [
 152             'title' => ts('Contact Type'),
 153           ],
 154           'contact_sub_type' => [
 155             'title' => ts('Contact Subtype'),
 156           ],
 157         ],
 158       ],
 159       'civicrm_email' => [
 160         'dao' => 'CRM_Core_DAO_Email',
 161         'fields' => [
 162           'email_creditor' => [
 163             'title' => ts('Soft Credit Email'),
 164             'name' => 'email',
 165             'alias' => 'emailcredit',
 166             'default' => TRUE,
 167             'no_repeat' => TRUE,
 168           ],
 169           'email_constituent' => [
 170             'title' => ts('Contributor\'s Email'),
 171             'name' => 'email',
 172             'alias' => 'emailconst',
 173           ],
 174         ],
 175         'grouping' => 'contact-fields',
 176       ],
 177       'civicrm_phone' => [
 178         'dao' => 'CRM_Core_DAO_Phone',
 179         'fields' => [
 180           'phone_creditor' => [
 181             'title' => ts('Soft Credit Phone'),
 182             'name' => 'phone',
 183             'alias' => 'pcredit',
 184             'default' => TRUE,
 185           ],
 186           'phone_constituent' => [
 187             'title' => ts('Contributor\'s Phone'),
 188             'name' => 'phone',
 189             'alias' => 'pconst',
 190             'no_repeat' => TRUE,
 191           ],
 192         ],
 193         'grouping' => 'contact-fields',
 194       ],
 195       'civicrm_financial_type' => [
 196         'dao' => 'CRM_Financial_DAO_FinancialType',
 197         'fields' => ['financial_type' => NULL],
 198         'filters' => [
 199           'id' => [
 200             'name' => 'id',
 201             'title' => ts('Financial Type'),
 202             'type' => CRM_Utils_Type::T_INT,
 203             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 204             'options' => CRM_Contribute_BAO_Contribution::buildOptions('financial_type_id', 'search'),
 205           ],
 206         ],
 207         'grouping' => 'softcredit-fields',
 208       ],
 209       'civicrm_contribution' => [
 210         'dao' => 'CRM_Contribute_DAO_Contribution',
 211         'fields' => [
 212           'contribution_source' => NULL,
 213           'currency' => [
 214             'required' => TRUE,
 215             'no_display' => TRUE,
 216           ],
 217         ],
 218         'grouping' => 'softcredit-fields',
 219         'filters' => [
 220           'receive_date' => ['operatorType' => CRM_Report_Form::OP_DATE],
 221           'receipt_date' => ['operatorType' => CRM_Report_Form::OP_DATE],
 222           'currency' => [
 223             'title' => ts('Currency'),
 224             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 225             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 226             'default' => NULL,
 227             'type' => CRM_Utils_Type::T_STRING,
 228           ],
 229           'contribution_status_id' => [
 230             'title' => ts('Contribution Status'),
 231             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 232             'options' => CRM_Contribute_BAO_Contribution::buildOptions('contribution_status_id', 'search'),
 233             'default' => [1],
 234           ],
 235         ],
 236       ],
 237       'civicrm_contribution_soft' => [
 238         'dao' => 'CRM_Contribute_DAO_ContributionSoft',
 239         'fields' => [
 240           'contribution_id' => [
 241             'title' => ts('Contribution ID'),
 242             'no_display' => TRUE,
 243             'default' => TRUE,
 244           ],
 245           'amount' => [
 246             'title' => ts('Amount Statistics'),
 247             'default' => TRUE,
 248             'statistics' => [
 249               'sum' => ts('Aggregate Amount'),
 250               'count' => ts('Contributions'),
 251               'avg' => ts('Average'),
 252             ],
 253           ],
 254           'id' => [
 255             'default' => TRUE,
 256             'no_display' => TRUE,
 257           ],
 258           'soft_credit_type_id' => ['title' => ts('Soft Credit Type')],
 259         ],
 260         'filters' => [
 261           'soft_credit_type_id' => [
 262             'title' => ts('Soft Credit Type'),
 263             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 264             'options' => CRM_Core_OptionGroup::values('soft_credit_type'),
 265             'default' => NULL,
 266             'type' => CRM_Utils_Type::T_STRING,
 267           ],
 268           'amount' => [
 269             'title' => ts('Soft Credit Amount'),
 270           ],
 271         ],
 272         'grouping' => 'softcredit-fields',
 273       ],
 274       'civicrm_financial_trxn' => [
 275         'dao' => 'CRM_Financial_DAO_FinancialTrxn',
 276         'fields' => [
 277           'card_type_id' => [
 278             'title' => ts('Credit Card Type'),
 279             'dbAlias' => 'GROUP_CONCAT(financial_trxn_civireport.card_type_id SEPARATOR ",")',
 280           ],
 281         ],
 282         'filters' => [
 283           'card_type_id' => [
 284             'title' => ts('Credit Card Type'),
 285             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 286             'options' => CRM_Financial_DAO_FinancialTrxn::buildOptions('card_type_id'),
 287             'default' => NULL,
 288             'type' => CRM_Utils_Type::T_STRING,
 289           ],
 290         ],
 291       ],
 292     ];
 293
 294     // If we have a campaign, build out the relevant elements
 295     $this->addCampaignFields('civicrm_contribution');
 296
 297     // Add charts support
 298     $this->_charts = [
 299       '' => ts('Tabular'),
 300       'barChart' => ts('Bar Chart'),
 301       'pieChart' => ts('Pie Chart'),
 302     ];
 303
 304     $this->_groupFilter = TRUE;
 305     $this->_tagFilter = TRUE;
 306
 307     $this->_currencyColumn = 'civicrm_contribution_currency';
 308     parent::__construct();
 309   }
 310
 311   public function preProcess() {
 312     parent::preProcess();
 313   }
 314
 315   public function select() {
 316     $select = [];
 317     $this->_columnHeaders = [];
 318     foreach ($this->_columns as $tableName => $table) {
 319       if (array_key_exists('fields', $table)) {
 320         foreach ($table['fields'] as $fieldName => $field) {
 321           if (!empty($field['required']) ||
 322             !empty($this->_params['fields'][$fieldName])
 323           ) {
 324
 325             // include email column if set
 326             if ($tableName == 'civicrm_email') {
 327               $this->_emailField = TRUE;
 328               $this->_emailFieldCredit = TRUE;
 329             }
 330             elseif ($tableName == 'civicrm_email_creditor') {
 331               $this->_emailFieldCredit = TRUE;
 332             }
 333
 334             // include phone columns if set
 335             if ($tableName == 'civicrm_phone') {
 336               $this->_phoneField = TRUE;
 337               $this->_phoneFieldCredit = TRUE;
 338             }
 339             elseif ($tableName == 'civicrm_phone_creditor') {
 340               $this->_phoneFieldCredit = TRUE;
 341             }
 342
 343             // only include statistics columns if set
 344             if (!empty($field['statistics'])) {
 345               foreach ($field['statistics'] as $stat => $label) {
 346                 switch (strtolower($stat)) {
 347                   case 'sum':
 348                     $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 349                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 350                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
 351                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 352                     break;
 353
 354                   case 'count':
 355                     $select[] = "COUNT({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 356                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
 357                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 358                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 359                     break;
 360
 361                   case 'avg':
 362                     $select[] = "ROUND(AVG({$field['dbAlias']}),2) as {$tableName}_{$fieldName}_{$stat}";
 363                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
 364                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 365                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 366                     break;
 367                 }
 368               }
 369             }
 370             else {
 371               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 372               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = $field['type'] ?? NULL;
 373               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 374             }
 375           }
 376         }
 377       }
 378     }
 379     $this->selectClause = $select;
 380
 381     $this->_select = 'SELECT ' . implode(', ', $select) . ' ';
 382   }
 383
 384   /**
 385    * @param array $fields
 386    * @param array $files
 387    * @param CRM_Core_Form $self
 388    *
 389    * @return array
 390    */
 391   public static function formRule($fields, $files, $self) {
 392     $errors = $grouping = [];
 393     return $errors;
 394   }
 395
 396   public function from() {
 397     $alias_constituent = 'constituentname';
 398     $alias_creditor = 'contact_civireport';
 399     $this->_from = "
 400         FROM  civicrm_contribution {$this->_aliases['civicrm_contribution']}
 401               INNER JOIN civicrm_contribution_soft {$this->_aliases['civicrm_contribution_soft']}
 402                          ON {$this->_aliases['civicrm_contribution_soft']}.contribution_id =
 403                             {$this->_aliases['civicrm_contribution']}.id
 404               INNER JOIN civicrm_contact {$alias_constituent}
 405                          ON {$this->_aliases['civicrm_contribution']}.contact_id =
 406                             {$alias_constituent}.id
 407               LEFT  JOIN civicrm_financial_type  {$this->_aliases['civicrm_financial_type']}
 408                          ON {$this->_aliases['civicrm_contribution']}.financial_type_id =
 409                             {$this->_aliases['civicrm_financial_type']}.id
 410               LEFT  JOIN civicrm_contact {$alias_creditor}
 411                          ON {$this->_aliases['civicrm_contribution_soft']}.contact_id =
 412                             {$alias_creditor}.id
 413               {$this->_aclFrom} ";
 414
 415     // include Constituent email field if email column is to be included
 416     if ($this->_emailField) {
 417       $alias = 'emailconst';
 418       $this->_from .= "
 419             LEFT JOIN civicrm_email {$alias}
 420                       ON {$alias_constituent}.id =
 421                          {$alias}.contact_id   AND
 422                          {$alias}.is_primary = 1\n";
 423     }
 424
 425     // include  Creditors email field if email column is to be included
 426     if ($this->_emailFieldCredit) {
 427       $alias = 'emailcredit';
 428       $this->_from .= "
 429             LEFT JOIN civicrm_email {$alias}
 430                       ON {$alias_creditor}.id =
 431                          {$alias}.contact_id  AND
 432                          {$alias}.is_primary = 1\n";
 433     }
 434
 435     // include  Constituents phone field if email column is to be included
 436     if ($this->_phoneField) {
 437       $alias = 'pconst';
 438       $this->_from .= "
 439             LEFT JOIN civicrm_phone {$alias}
 440                       ON {$alias_constituent}.id =
 441                          {$alias}.contact_id  AND
 442                          {$alias}.is_primary = 1\n";
 443     }
 444
 445     // include  Creditors phone field if email column is to be included
 446     if ($this->_phoneFieldCredit) {
 447       $alias = 'pcredit';
 448       $this->_from .= "
 449             LEFT JOIN civicrm_phone pcredit
 450                       ON {$alias_creditor}.id =
 451                          {$alias}.contact_id  AND
 452                          {$alias}.is_primary = 1\n";
 453     }
 454     // for credit card type
 455     $this->addFinancialTrxnFromClause();
 456   }
 457
 458   public function groupBy() {
 459     $this->_rollup = 'WITH ROLLUP';
 460     $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->selectClause, ["{$this->_aliases['civicrm_contribution_soft']}.contact_id", "constituentname.id"]);
 461     $this->_groupBy = "
 462 GROUP BY {$this->_aliases['civicrm_contribution_soft']}.contact_id, constituentname.id {$this->_rollup}";
 463   }
 464
 465   public function where() {
 466     parent::where();
 467     $this->_where .= " AND {$this->_aliases['civicrm_contribution']}.is_test = 0 AND {$this->_aliases['civicrm_contribution']}.is_template = 0 ";
 468   }
 469
 470   /**
 471    * @param array $rows
 472    *
 473    * @return array
 474    */
 475   public function statistics(&$rows) {
 476     $statistics = parent::statistics($rows);
 477
 478     $select = "
 479         SELECT COUNT({$this->_aliases['civicrm_contribution_soft']}.amount ) as count,
 480                SUM({$this->_aliases['civicrm_contribution_soft']}.amount ) as amount,
 481                ROUND(AVG({$this->_aliases['civicrm_contribution_soft']}.amount), 2) as avg,
 482                {$this->_aliases['civicrm_contribution']}.currency as currency
 483         ";
 484
 485     $sql = "{$select} {$this->_from} {$this->_where}
 486 GROUP BY   {$this->_aliases['civicrm_contribution']}.currency
 487 ";
 488
 489     $dao = CRM_Core_DAO::executeQuery($sql);
 490     $count = 0;
 491     $totalAmount = $average = [];
 492     while ($dao->fetch()) {
 493       $totalAmount[] = CRM_Utils_Money::format($dao->amount, $dao->currency) . '(' .
 494         $dao->count . ')';
 495       $average[] = CRM_Utils_Money::format($dao->avg, $dao->currency);
 496       $count += $dao->count;
 497     }
 498     $statistics['counts']['amount'] = [
 499       'title' => ts('Total Amount'),
 500       'value' => implode(',  ', $totalAmount),
 501       'type' => CRM_Utils_Type::T_STRING,
 502     ];
 503     $statistics['counts']['count'] = [
 504       'title' => ts('Total Contributions'),
 505       'value' => $count,
 506     ];
 507     $statistics['counts']['avg'] = [
 508       'title' => ts('Average'),
 509       'value' => implode(',  ', $average),
 510       'type' => CRM_Utils_Type::T_STRING,
 511     ];
 512
 513     return $statistics;
 514   }
 515
 516   public function postProcess() {
 517     $this->beginPostProcess();
 518
 519     $this->buildACLClause(['constituentname', 'contact_civireport']);
 520     $sql = $this->buildQuery();
 521
 522     $rows = $graphRows = [];
 523     $this->buildRows($sql, $rows);
 524
 525     $this->formatDisplay($rows);
 526
 527     // assign variables to templates
 528     $this->doTemplateAssignment($rows);
 529     $this->endPostProcess($rows);
 530   }
 531
 532   /**
 533    * Alter display of rows.
 534    *
 535    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 536    * such as rendering contacts as links.
 537    *
 538    * @param array $rows
 539    *   Rows generated by SQL, with an array for each row.
 540    */
 541   public function alterDisplay(&$rows) {
 542     $entryFound = FALSE;
 543     $dispname_flag = $phone_flag = $email_flag = 0;
 544     $prev_email = $prev_dispname = $prev_phone = NULL;
 545
 546     foreach ($rows as $rowNum => $row) {
 547       // Link constituent (contributor) to contribution detail
 548       if (array_key_exists('civicrm_contact_display_name_constituent', $row) &&
 549         array_key_exists('civicrm_contact_id_constituent', $row)
 550       ) {
 551
 552         $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
 553           'reset=1&force=1&id_op=eq&id_value=' .
 554           $row['civicrm_contact_id_constituent'],
 555           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 556         );
 557         $rows[$rowNum]['civicrm_contact_display_name_constituent_link'] = $url;
 558         $rows[$rowNum]['civicrm_contact_display_name_constituent_hover'] = ts('List all direct contribution(s) from this contact.');
 559         $entryFound = TRUE;
 560       }
 561
 562       // convert soft credit contact name to link
 563       if (array_key_exists('civicrm_contact_display_name_creditor', $row) &&
 564         !empty($rows[$rowNum]['civicrm_contact_display_name_creditor']) &&
 565         array_key_exists('civicrm_contact_id_creditor', $row)
 566       ) {
 567         $url = CRM_Utils_System::url("civicrm/contact/view",
 568           'reset=1&cid=' . $row['civicrm_contact_id_creditor'],
 569           $this->_absoluteUrl
 570         );
 571         $rows[$rowNum]['civicrm_contact_display_name_creditor_link'] = $url;
 572         $rows[$rowNum]['civicrm_contact_display_name_creditor_hover'] = ts("View contact summary");
 573       }
 574
 575       // make subtotals look nicer
 576       if (array_key_exists('civicrm_contact_id_constituent', $row) &&
 577         !$row['civicrm_contact_id_constituent']
 578       ) {
 579         $this->fixSubTotalDisplay($rows[$rowNum], $this->_statFields);
 580         $entryFound = TRUE;
 581       }
 582
 583       // convert campaign_id to campaign title
 584       if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
 585         if ($value = $row['civicrm_contribution_campaign_id']) {
 586           $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->campaigns[$value];
 587           $entryFound = TRUE;
 588         }
 589       }
 590
 591       //convert soft_credit_type_id into label
 592       if (array_key_exists('civicrm_contribution_soft_soft_credit_type_id', $rows[$rowNum])) {
 593         $rows[$rowNum]['civicrm_contribution_soft_soft_credit_type_id'] = CRM_Core_PseudoConstant::getLabel(
 594           'CRM_Contribute_BAO_ContributionSoft',
 595           'soft_credit_type_id',
 596           $row['civicrm_contribution_soft_soft_credit_type_id']
 597         );
 598       }
 599
 600       if (!empty($row['civicrm_financial_trxn_card_type_id']) && !in_array('Subtotal', $rows[$rowNum])) {
 601         $rows[$rowNum]['civicrm_financial_trxn_card_type_id'] = $this->getLabels($row['civicrm_financial_trxn_card_type_id'], 'CRM_Financial_DAO_FinancialTrxn', 'card_type_id');
 602         $entryFound = TRUE;
 603       }
 604
 605       $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, NULL, NULL) ? TRUE : $entryFound;
 606
 607       // skip looking further in rows, if first row itself doesn't
 608       // have the column we need
 609       if (!$entryFound) {
 610         break;
 611       }
 612     }
 613
 614     $this->removeDuplicates($rows);
 615   }
 616
 617 }