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