CRM/Report/Form/Contribute/Sybunt.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_Sybunt extends CRM_Report_Form {
  18
  19   protected $_charts = [
  20     '' => 'Tabular',
  21     'barChart' => 'Bar Chart',
  22     'pieChart' => 'Pie Chart',
  23   ];
  24
  25   protected $_customGroupExtends = [
  26     'Contact',
  27     'Individual',
  28     'Contribution',
  29   ];
  30
  31   public $_drilldownReport = ['contribute/detail' => 'Link to Detail Report'];
  32
  33   /**
  34    * This report has been optimised for group filtering.
  35    *
  36    * @var bool
  37    * @see https://issues.civicrm.org/jira/browse/CRM-19170
  38    */
  39   protected $groupFilterNotOptimised = FALSE;
  40
  41   /**
  42    * Class constructor.
  43    */
  44   public function __construct() {
  45     $this->_rollup = 'WITH ROLLUP';
  46     $this->_autoIncludeIndexedFieldsAsOrderBys = 1;
  47     $yearsInPast = 10;
  48     $yearsInFuture = 1;
  49     $date = CRM_Core_SelectValues::date('custom', NULL, $yearsInPast, $yearsInFuture);
  50     $count = $date['maxYear'];
  51     while ($date['minYear'] <= $count) {
  52       $optionYear[$date['minYear']] = $date['minYear'];
  53       $date['minYear']++;
  54     }
  55
  56     $this->_columns = [
  57       'civicrm_contact' => [
  58         'dao' => 'CRM_Contact_DAO_Contact',
  59         'grouping' => 'contact-field',
  60         'fields' => [
  61           'sort_name' => [
  62             'title' => ts('Donor Name'),
  63             'required' => TRUE,
  64           ],
  65           'first_name' => [
  66             'title' => ts('First Name'),
  67           ],
  68           'middle_name' => [
  69             'title' => ts('Middle Name'),
  70           ],
  71           'last_name' => [
  72             'title' => ts('Last Name'),
  73           ],
  74           'id' => [
  75             'no_display' => TRUE,
  76             'required' => TRUE,
  77           ],
  78           'gender_id' => [
  79             'title' => ts('Gender'),
  80           ],
  81           'birth_date' => [
  82             'title' => ts('Birth Date'),
  83           ],
  84           'age' => [
  85             'title' => ts('Age'),
  86             'dbAlias' => 'TIMESTAMPDIFF(YEAR, contact_civireport.birth_date, CURDATE())',
  87           ],
  88           'contact_type' => [
  89             'title' => ts('Contact Type'),
  90           ],
  91           'contact_sub_type' => [
  92             'title' => ts('Contact Subtype'),
  93           ],
  94         ],
  95         'order_bys' => [
  96           'sort_name' => [
  97             'title' => ts('Last Name, First Name'),
  98             'default' => '1',
  99             'default_weight' => '0',
 100             'default_order' => 'ASC',
 101           ],
 102           'first_name' => [
 103             'name' => 'first_name',
 104             'title' => ts('First Name'),
 105           ],
 106           'gender_id' => [
 107             'name' => 'gender_id',
 108             'title' => ts('Gender'),
 109           ],
 110           'birth_date' => [
 111             'name' => 'birth_date',
 112             'title' => ts('Birth Date'),
 113           ],
 114           'age_at_event' => [
 115             'name' => 'age_at_event',
 116             'title' => ts('Age at Event'),
 117           ],
 118           'contact_type' => [
 119             'title' => ts('Contact Type'),
 120           ],
 121           'contact_sub_type' => [
 122             'title' => ts('Contact Subtype'),
 123           ],
 124         ],
 125         'filters' => [
 126           'sort_name' => [
 127             'title' => ts('Donor Name'),
 128             'operator' => 'like',
 129           ],
 130           'id' => [
 131             'title' => ts('Contact ID'),
 132             'no_display' => TRUE,
 133           ],
 134           'gender_id' => [
 135             'title' => ts('Gender'),
 136             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 137             'options' => CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'gender_id'),
 138           ],
 139           'birth_date' => [
 140             'title' => ts('Birth Date'),
 141             'operatorType' => CRM_Report_Form::OP_DATE,
 142           ],
 143           'contact_type' => [
 144             'title' => ts('Contact Type'),
 145           ],
 146           'contact_sub_type' => [
 147             'title' => ts('Contact Subtype'),
 148           ],
 149         ],
 150       ],
 151       'civicrm_line_item' => [
 152         'dao' => 'CRM_Price_DAO_LineItem',
 153       ],
 154       'civicrm_email' => [
 155         'dao' => 'CRM_Core_DAO_Email',
 156         'grouping' => 'contact-field',
 157         'fields' => [
 158           'email' => [
 159             'title' => ts('Email'),
 160             'default' => TRUE,
 161           ],
 162         ],
 163       ],
 164       'civicrm_phone' => [
 165         'dao' => 'CRM_Core_DAO_Phone',
 166         'grouping' => 'contact-field',
 167         'fields' => [
 168           'phone' => [
 169             'title' => ts('Phone'),
 170             'default' => TRUE,
 171           ],
 172         ],
 173       ],
 174     ];
 175     $this->_columns += $this->addAddressFields();
 176     $this->_columns += [
 177       'civicrm_contribution' => [
 178         'dao' => 'CRM_Contribute_DAO_Contribution',
 179         'fields' => [
 180           'contact_id' => [
 181             'title' => ts('contactId'),
 182             'no_display' => TRUE,
 183             'required' => TRUE,
 184             'no_repeat' => TRUE,
 185           ],
 186           'total_amount' => [
 187             'title' => ts('Total Amount'),
 188             'no_display' => TRUE,
 189             'required' => TRUE,
 190             'no_repeat' => TRUE,
 191           ],
 192           'receive_date' => [
 193             'title' => ts('Year'),
 194             'no_display' => TRUE,
 195             'required' => TRUE,
 196             'no_repeat' => TRUE,
 197           ],
 198         ],
 199         'filters' => [
 200           'yid' => [
 201             'name' => 'receive_date',
 202             'title' => ts('This Year'),
 203             'operatorType' => CRM_Report_Form::OP_SELECT,
 204             'options' => $optionYear,
 205             'default' => date('Y'),
 206             'type' => CRM_Utils_Type::T_INT,
 207           ],
 208           'financial_type_id' => [
 209             'title' => ts('Financial Type'),
 210             'type' => CRM_Utils_Type::T_INT,
 211             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 212             'options' => CRM_Contribute_BAO_Contribution::buildOptions('financial_type_id', 'search'),
 213           ],
 214           'contribution_status_id' => [
 215             'title' => ts('Contribution Status'),
 216             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 217             'options' => CRM_Contribute_BAO_Contribution::buildOptions('contribution_status_id', 'search'),
 218             'default' => ['1'],
 219           ],
 220         ],
 221       ],
 222     ];
 223     $this->_columns += [
 224       'civicrm_financial_trxn' => [
 225         'dao' => 'CRM_Financial_DAO_FinancialTrxn',
 226         'fields' => [
 227           'card_type_id' => [
 228             'title' => ts('Credit Card Type'),
 229             'dbAlias' => 'GROUP_CONCAT(financial_trxn_civireport.card_type_id SEPARATOR ",")',
 230           ],
 231         ],
 232         'filters' => [
 233           'card_type_id' => [
 234             'title' => ts('Credit Card Type'),
 235             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 236             'default' => NULL,
 237             'options' => CRM_Financial_DAO_FinancialTrxn::buildOptions('card_type_id'),
 238             'type' => CRM_Utils_Type::T_STRING,
 239           ],
 240         ],
 241       ],
 242     ];
 243
 244     // If we have a campaign, build out the relevant elements
 245     $this->addCampaignFields('civicrm_contribution');
 246
 247     $this->_groupFilter = TRUE;
 248     $this->_tagFilter = TRUE;
 249     parent::__construct();
 250   }
 251
 252   public function preProcess() {
 253     parent::preProcess();
 254   }
 255
 256   public function select() {
 257     $select = [];
 258     $this->_columnHeaders = [];
 259     $current_year = $this->_params['yid_value'];
 260     $previous_year = $current_year - 1;
 261     $previous_pyear = $current_year - 2;
 262     $previous_ppyear = $current_year - 3;
 263     $upTo_year = $current_year - 4;
 264
 265     foreach ($this->_columns as $tableName => $table) {
 266       if (array_key_exists('fields', $table)) {
 267         foreach ($table['fields'] as $fieldName => $field) {
 268
 269           if (!empty($field['required']) ||
 270             !empty($this->_params['fields'][$fieldName])
 271           ) {
 272             if ($fieldName == 'total_amount') {
 273               $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}";
 274
 275               $this->_columnHeaders["civicrm_upto_{$upTo_year}"]['type'] = $field['type'];
 276               $this->_columnHeaders["civicrm_upto_{$upTo_year}"]['title'] = ts("Up To %1", [1 => $upTo_year]);
 277
 278               $this->_columnHeaders["year_{$previous_ppyear}"]['type'] = $field['type'];
 279               $this->_columnHeaders["year_{$previous_ppyear}"]['title'] = $previous_ppyear;
 280
 281               $this->_columnHeaders["year_{$previous_pyear}"]['type'] = $field['type'];
 282               $this->_columnHeaders["year_{$previous_pyear}"]['title'] = $previous_pyear;
 283
 284               $this->_columnHeaders["year_{$previous_year}"]['type'] = $field['type'];
 285               $this->_columnHeaders["year_{$previous_year}"]['title'] = $previous_year;
 286
 287               $this->_columnHeaders["civicrm_life_time_total"]['type'] = $field['type'];
 288               $this->_columnHeaders["civicrm_life_time_total"]['title'] = ts('LifeTime');
 289             }
 290             elseif ($fieldName == 'receive_date') {
 291               $select[] = self::fiscalYearOffset($field['dbAlias']) .
 292                 " as {$tableName}_{$fieldName}";
 293             }
 294             else {
 295               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 296               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = $field['type'] ?? NULL;
 297               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 298             }
 299             if (!empty($field['no_display'])) {
 300               $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = TRUE;
 301             }
 302           }
 303         }
 304       }
 305     }
 306     $this->_selectClauses = $select;
 307
 308     $this->_select = "SELECT " . implode(', ', $select) . " ";
 309   }
 310
 311   public function from() {
 312     $this->setFromBase('civicrm_contribution', 'contact_id');
 313     $this->_from .= "
 314               INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 315                       ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id
 316              {$this->_aclFrom}";
 317
 318     $this->joinPhoneFromContact();
 319     $this->joinEmailFromContact();
 320
 321     // for credit card type
 322     $this->addFinancialTrxnFromClause();
 323
 324     $this->joinAddressFromContact();
 325   }
 326
 327   public function where() {
 328     $this->_statusClause = "";
 329     $clauses = [$this->_aliases['civicrm_contribution'] . '.is_test = 0'];
 330     foreach ($this->_columns as $tableName => $table) {
 331       if (array_key_exists('filters', $table)) {
 332         foreach ($table['filters'] as $fieldName => $field) {
 333           $clause = NULL;
 334           if ($fieldName == 'yid') {
 335             $clause = "contribution_civireport.contact_id NOT IN
 336 (SELECT distinct cont.id FROM civicrm_contact cont, civicrm_contribution contri
 337  WHERE  cont.id = contri.contact_id AND " .
 338               self::fiscalYearOffset('contri.receive_date') .
 339               " = {$this->_params['yid_value']} AND contri.is_test = 0 )";
 340           }
 341           elseif (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE
 342           ) {
 343             $relative = $this->_params["{$fieldName}_relative"] ?? NULL;
 344             $from = $this->_params["{$fieldName}_from"] ?? NULL;
 345             $to = $this->_params["{$fieldName}_to"] ?? NULL;
 346
 347             if ($relative || $from || $to) {
 348               $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 349             }
 350           }
 351           else {
 352             $op = $this->_params["{$fieldName}_op"] ?? NULL;
 353             if ($op) {
 354               $clause = $this->whereClause($field,
 355                 $op,
 356                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 357                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 358                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 359               );
 360               if (($fieldName == 'contribution_status_id' ||
 361                   $fieldName == 'financial_type_id') && !empty($clause)
 362               ) {
 363                 $this->_statusClause .= " AND " . $clause;
 364               }
 365             }
 366           }
 367
 368           if (!empty($clause)) {
 369             $clauses[] = $clause;
 370           }
 371         }
 372       }
 373     }
 374
 375     $this->_where = "WHERE " . implode(' AND ', $clauses);
 376
 377     if ($this->_aclWhere) {
 378       $this->_where .= " AND {$this->_aclWhere} ";
 379     }
 380   }
 381
 382   public function groupBy() {
 383     $this->assign('chartSupported', TRUE);
 384     $fiscalYearOffset = self::fiscalYearOffset("{$this->_aliases['civicrm_contribution']}.receive_date");
 385     $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contribution']}.contact_id, {$fiscalYearOffset}";
 386     $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, ["{$this->_aliases['civicrm_contribution']}.contact_id", $fiscalYearOffset]);
 387     $this->_groupBy .= " {$this->_rollup}";
 388   }
 389
 390   /**
 391    * @param $rows
 392    *
 393    * @return array
 394    */
 395   public function statistics(&$rows) {
 396     $statistics = parent::statistics($rows);
 397
 398     if (!empty($rows)) {
 399       $select = "
 400                    SELECT
 401                         SUM({$this->_aliases['civicrm_contribution']}.total_amount ) as amount ";
 402
 403       $sql = "{$select} {$this->_from} {$this->_where}";
 404       $dao = CRM_Core_DAO::executeQuery($sql);
 405       if ($dao->fetch()) {
 406         $statistics['counts']['amount'] = [
 407           'value' => $dao->amount,
 408           'title' => ts('Total LifeTime'),
 409           'type' => CRM_Utils_Type::T_MONEY,
 410         ];
 411       }
 412     }
 413     return $statistics;
 414   }
 415
 416   public function postProcess() {
 417     // get ready with post process params
 418     $this->beginPostProcess();
 419     $this->buildACLClause($this->_aliases['civicrm_contact']);
 420     $this->buildQuery();
 421
 422     $rows = $contactIds = [];
 423     if (empty($this->_params['charts'])) {
 424       $this->limit();
 425       $getContacts = "SELECT SQL_CALC_FOUND_ROWS {$this->_aliases['civicrm_contact']}.id as cid {$this->_from} {$this->_where} GROUP BY {$this->_aliases['civicrm_contact']}.id {$this->_limit}";
 426
 427       $dao = CRM_Core_DAO::executeQuery($getContacts);
 428
 429       while ($dao->fetch()) {
 430         $contactIds[] = $dao->cid;
 431       }
 432       $this->setPager();
 433     }
 434
 435     if (!empty($contactIds) || !empty($this->_params['charts'])) {
 436       if (!empty($this->_params['charts'])) {
 437         $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy}";
 438       }
 439       else {
 440         $sql = "" .
 441           "{$this->_select} {$this->_from} WHERE {$this->_aliases['civicrm_contact']}.id IN (" .
 442           implode(',', $contactIds) .
 443           ") AND {$this->_aliases['civicrm_contribution']}.is_test = 0 {$this->_statusClause} {$this->_groupBy} ";
 444       }
 445
 446       $current_year = $this->_params['yid_value'];
 447       $previous_year = $current_year - 1;
 448       $previous_pyear = $current_year - 2;
 449       $previous_ppyear = $current_year - 3;
 450       $upTo_year = $current_year - 4;
 451
 452       $rows = $row = [];
 453       $dao = CRM_Core_DAO::executeQuery($sql);
 454       $contributionSum = 0;
 455       $yearcal = [];
 456       while ($dao->fetch()) {
 457         if (!$dao->civicrm_contribution_contact_id) {
 458           continue;
 459         }
 460         $row = [];
 461         foreach ($this->_columnHeaders as $key => $value) {
 462           if (property_exists($dao, $key)) {
 463             $rows[$dao->civicrm_contribution_contact_id][$key] = $dao->$key;
 464           }
 465         }
 466         if ($dao->civicrm_contribution_receive_date) {
 467           if ($dao->civicrm_contribution_receive_date > $upTo_year) {
 468             $contributionSum += $dao->civicrm_contribution_total_amount;
 469             $rows[$dao->civicrm_contribution_contact_id]['year_' . $dao->civicrm_contribution_receive_date] = $dao->civicrm_contribution_total_amount;
 470           }
 471         }
 472         else {
 473           $rows[$dao->civicrm_contribution_contact_id]['civicrm_life_time_total'] = $dao->civicrm_contribution_total_amount;
 474           if (($dao->civicrm_contribution_total_amount - $contributionSum) > 0
 475           ) {
 476             $rows[$dao->civicrm_contribution_contact_id]["civicrm_upto_{$upTo_year}"]
 477               = $dao->civicrm_contribution_total_amount - $contributionSum;
 478           }
 479           $contributionSum = 0;
 480         }
 481       }
 482     }
 483     // format result set.
 484     $this->formatDisplay($rows, FALSE);
 485
 486     // assign variables to templates
 487     $this->doTemplateAssignment($rows);
 488
 489     // do print / pdf / instance stuff if needed
 490     $this->endPostProcess($rows);
 491   }
 492
 493   /**
 494    * @param $rows
 495    */
 496   public function buildChart(&$rows) {
 497     $graphRows = [];
 498     $count = 0;
 499     $current_year = $this->_params['yid_value'];
 500     $previous_year = $current_year - 1;
 501     $previous_two_year = $current_year - 2;
 502     $previous_three_year = $current_year - 3;
 503     $upto = $current_year - 4;
 504
 505     $interval[$previous_year] = $previous_year;
 506     $interval[$previous_two_year] = $previous_two_year;
 507     $interval[$previous_three_year] = $previous_three_year;
 508     $interval["upto_{$upto}"] = "Up To {$upto}";
 509
 510     foreach ($rows as $key => $row) {
 511       $display["upto_{$upto}"]
 512         = CRM_Utils_Array::value("upto_{$upto}", $display) + CRM_Utils_Array::value("civicrm_upto_{$upto}", $row);
 513       $display[$previous_year]
 514         = CRM_Utils_Array::value($previous_year, $display) + CRM_Utils_Array::value($previous_year, $row);
 515       $display[$previous_two_year]
 516         = CRM_Utils_Array::value($previous_two_year, $display) + CRM_Utils_Array::value($previous_two_year, $row);
 517       $display[$previous_three_year]
 518         = CRM_Utils_Array::value($previous_three_year, $display) + CRM_Utils_Array::value($previous_three_year, $row);
 519     }
 520
 521     $graphRows['value'] = $display;
 522     $config = CRM_Core_Config::Singleton();
 523     $chartInfo = [
 524       'legend' => ts('Sybunt Report'),
 525       'xname' => ts('Year'),
 526       'yname' => ts('Amount (%1)', [1 => $config->defaultCurrency]),
 527     ];
 528     if ($this->_params['charts']) {
 529       // build the chart.
 530       CRM_Utils_Chart::reportChart($graphRows, $this->_params['charts'], $interval, $chartInfo);
 531       $this->assign('chartType', $this->_params['charts']);
 532     }
 533   }
 534
 535   /**
 536    * Alter display of rows.
 537    *
 538    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 539    * such as rendering contacts as links.
 540    *
 541    * @param array $rows
 542    *   Rows generated by SQL, with an array for each row.
 543    */
 544   public function alterDisplay(&$rows) {
 545     $entryFound = FALSE;
 546
 547     foreach ($rows as $rowNum => $row) {
 548       //Convert Display name into link
 549       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 550         array_key_exists('civicrm_contribution_contact_id', $row)
 551       ) {
 552         $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
 553           'reset=1&force=1&id_op=eq&id_value=' .
 554           $row['civicrm_contribution_contact_id'],
 555           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 556         );
 557         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 558         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contribution Details for this Contact.");
 559         $entryFound = TRUE;
 560       }
 561
 562       // convert campaign_id to campaign title
 563       if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
 564         if ($value = $row['civicrm_contribution_campaign_id']) {
 565           $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->campaigns[$value];
 566           $entryFound = TRUE;
 567         }
 568       }
 569
 570       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'contribute/detail', 'List all contribution(s)') ? TRUE : $entryFound;
 571       $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, NULL, 'List all contribution(s)') ? TRUE : $entryFound;
 572       if (!empty($row['civicrm_financial_trxn_card_type_id'])) {
 573         $rows[$rowNum]['civicrm_financial_trxn_card_type_id'] = $this->getLabels($row['civicrm_financial_trxn_card_type_id'], 'CRM_Financial_DAO_FinancialTrxn', 'card_type_id');
 574         $entryFound = TRUE;
 575       }
 576
 577       // skip looking further in rows, if first row itself doesn't
 578       // have the column we need
 579       if (!$entryFound) {
 580         break;
 581       }
 582     }
 583   }
 584
 585   /**
 586    * Override "This Year" $op options
 587    * @param string $type
 588    * @param null $fieldName
 589    *
 590    * @return array
 591    */
 592   public function getOperationPair($type = "string", $fieldName = NULL) {
 593     if ($fieldName == 'yid') {
 594       return [
 595         'calendar' => ts('Is Calendar Year'),
 596         'fiscal' => ts('Fiscal Year Starting'),
 597       ];
 598     }
 599     return parent::getOperationPair($type, $fieldName);
 600   }
 601
 602 }