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