CRM/Report/Form/Contribute/DeferredRevenue.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_DeferredRevenue extends CRM_Report_Form {
  18
  19   /**
  20    * Holds Deferred Financial Account
  21    * @var array
  22    */
  23   protected $_deferredFinancialAccount = [];
  24
  25   /**
  26    */
  27   public function __construct() {
  28     $this->_exposeContactID = FALSE;
  29     $this->_deferredFinancialAccount = CRM_Financial_BAO_FinancialAccount::getAllDeferredFinancialAccount();
  30     $this->_columns = [
  31       'civicrm_financial_trxn' => [
  32         'dao' => 'CRM_Financial_DAO_FinancialTrxn',
  33         'fields' => [
  34           'status_id' => [
  35             'title' => ts('Transaction'),
  36           ],
  37           'trxn_date' => [
  38             'title' => ts('Transaction Date'),
  39             'type' => CRM_Utils_Type::T_DATE,
  40             'required' => TRUE,
  41           ],
  42           'total_amount' => [
  43             'title' => ts('Transaction Amount'),
  44             'type' => CRM_Utils_Type::T_MONEY,
  45             'required' => TRUE,
  46             'dbAlias' => 'SUM(financial_trxn_1_civireport.total_amount )',
  47           ],
  48         ],
  49         'filters' => [
  50           'trxn_date' => [
  51             'title' => ts('Transaction Date'),
  52             'operatorType' => CRM_Report_Form::OP_DATE,
  53             'type' => CRM_Utils_Type::T_DATE,
  54           ],
  55         ],
  56       ],
  57       'civicrm_contribution' => [
  58         'dao' => 'CRM_Contribute_DAO_Contribution',
  59         'fields' => [
  60           'id' => [
  61             'title' => ts('Contribution ID'),
  62           ],
  63           'contribution_id' => [
  64             'title' => ts('Contribution ID'),
  65             'required' => TRUE,
  66             'no_display' => TRUE,
  67             'dbAlias' => 'contribution_civireport.id',
  68           ],
  69           'contact_id' => [
  70             'title' => ts('Contact ID'),
  71           ],
  72           'source' => [
  73             'title' => ts('Source'),
  74           ],
  75           'receive_date' => [
  76             'title' => ts('Receive Date'),
  77             'type' => CRM_Utils_Type::T_DATE,
  78           ],
  79           'cancel_date' => [
  80             'name' => 'contribution_cancel_date',
  81             'title' => ts('Cancel Date'),
  82             'type' => CRM_Utils_Type::T_DATE,
  83           ],
  84           'revenue_recognition_date' => [
  85             'title' => ts('Revenue Recognition Date'),
  86             'type' => CRM_Utils_Type::T_DATE,
  87           ],
  88         ],
  89         'filters' => [
  90           'receive_date' => [
  91             'title' => ts('Receive Date'),
  92             'operatorType' => CRM_Report_Form::OP_DATE,
  93             'type' => CRM_Utils_Type::T_DATE,
  94           ],
  95           'receipt_date' => ['operatorType' => CRM_Report_Form::OP_DATE],
  96           'cancel_date' => [
  97             'title' => ts('Cancel Date'),
  98             'operatorType' => CRM_Report_Form::OP_DATE,
  99             'type' => CRM_Utils_Type::T_DATE,
 100             'name' => 'contribution_cancel_date',
 101           ],
 102           'revenue_recognition_date' => [
 103             'title' => ts('Revenue Recognition Date'),
 104             'operatorType' => CRM_Report_Form::OP_DATE,
 105             'type' => CRM_Utils_Type::T_DATE,
 106           ],
 107           'revenue_recognition_date_toggle' => [
 108             'title' => ts("Current month's revenue?"),
 109             'type' => CRM_Utils_Type::T_BOOLEAN,
 110             'default' => 0,
 111             'pseudofield' => TRUE,
 112           ],
 113         ],
 114       ],
 115       'civicrm_financial_account' => [
 116         'dao' => 'CRM_Financial_DAO_FinancialAccount',
 117         'fields' => [
 118           'name' => [
 119             'title' => ts('Deferred Account'),
 120             'required' => TRUE,
 121             'no_display' => TRUE,
 122           ],
 123           'id' => [
 124             'title' => ts('Deferred Account ID'),
 125             'required' => TRUE,
 126             'no_display' => TRUE,
 127           ],
 128           'accounting_code' => [
 129             'title' => ts('Deferred Accounting Code'),
 130             'required' => TRUE,
 131             'no_display' => TRUE,
 132           ],
 133         ],
 134         'filters' => [
 135           'id' => [
 136             'title' => ts('Deferred Financial Account'),
 137             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 138             'options' => $this->_deferredFinancialAccount,
 139             'type' => CRM_Utils_Type::T_INT,
 140           ],
 141         ],
 142       ],
 143       'civicrm_financial_account_1' => [
 144         'dao' => 'CRM_Financial_DAO_FinancialAccount',
 145         'fields' => [
 146           'name' => [
 147             'title' => ts('Revenue Account'),
 148             'required' => TRUE,
 149             'no_display' => TRUE,
 150           ],
 151           'id' => [
 152             'title' => ts('Revenue Account ID'),
 153             'required' => TRUE,
 154             'no_display' => TRUE,
 155           ],
 156           'accounting_code' => [
 157             'title' => ts('Revenue Accounting code'),
 158             'no_display' => TRUE,
 159             'required' => TRUE,
 160           ],
 161         ],
 162       ],
 163       'civicrm_financial_item' => [
 164         'dao' => 'CRM_Financial_DAO_FinancialItem',
 165         'fields' => [
 166           'status_id' => [
 167             'title' => ts('Status'),
 168             'required' => TRUE,
 169             'no_display' => TRUE,
 170           ],
 171           'id' => [
 172             'title' => ts('Financial Item ID'),
 173             'required' => TRUE,
 174             'no_display' => TRUE,
 175           ],
 176           'description' => [
 177             'title' => ts('Item'),
 178           ],
 179         ],
 180       ],
 181       'civicrm_financial_trxn_1' => [
 182         'dao' => 'CRM_Financial_DAO_FinancialTrxn',
 183         'fields' => [
 184           'total_amount' => [
 185             'title' => ts('Deferred Transaction Amount'),
 186             'type' => CRM_Utils_Type::T_MONEY,
 187             'required' => TRUE,
 188             'no_display' => TRUE,
 189             'dbAlias' => 'GROUP_CONCAT(financial_trxn_1_civireport.total_amount)',
 190           ],
 191           'trxn_date' => [
 192             'title' => ts('Deferred Transaction Date'),
 193             'required' => TRUE,
 194             'no_display' => TRUE,
 195             'dbAlias' => 'GROUP_CONCAT(financial_trxn_1_civireport.trxn_date)',
 196             'type' => CRM_Utils_Type::T_DATE,
 197           ],
 198         ],
 199       ],
 200       'civicrm_contact' => [
 201         'dao' => 'CRM_Contact_DAO_Contact',
 202         'fields' => [
 203           'display_name' => [
 204             'title' => ts('Contact Name'),
 205           ],
 206           'id' => [
 207             'title' => ts('Contact ID'),
 208             'required' => TRUE,
 209             'no_display' => TRUE,
 210           ],
 211         ],
 212       ],
 213       'civicrm_membership' => [
 214         'dao' => 'CRM_Member_DAO_Membership',
 215         'fields' => [
 216           'start_date' => [
 217             'title' => ts('Start Date'),
 218             'dbAlias' => 'IFNULL(membership_civireport.start_date, event_civireport.start_date)',
 219             'type' => CRM_Utils_Type::T_DATE,
 220           ],
 221           'end_date' => [
 222             'title' => ts('End Date'),
 223             'dbdbAlias' => 'IFNULL(membership_civireport.end_date, event_civireport.end_date)',
 224             'type' => CRM_Utils_Type::T_DATE,
 225           ],
 226         ],
 227       ],
 228       'civicrm_event' => [
 229         'dao' => 'CRM_Event_DAO_Event',
 230       ],
 231       'civicrm_participant' => [
 232         'dao' => 'CRM_Event_DAO_Participant',
 233       ],
 234       'civicrm_batch' => [
 235         'dao' => 'CRM_Batch_DAO_EntityBatch',
 236         'grouping' => 'contri-fields',
 237         'fields' => [
 238           'batch_id' => [
 239             'title' => ts('Batch Title'),
 240             'dbAlias' => "GROUP_CONCAT(DISTINCT batch_civireport.batch_id
 241                                     ORDER BY batch_civireport.batch_id SEPARATOR ',')",
 242
 243           ],
 244         ],
 245         'filters' => [
 246           'batch_id' => [
 247             'title' => ts('Batch Title'),
 248             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 249             'options' => CRM_Batch_BAO_Batch::getBatches(),
 250             'type' => CRM_Utils_Type::T_INT,
 251           ],
 252         ],
 253       ],
 254     ];
 255     parent::__construct();
 256   }
 257
 258   /**
 259    * Pre process function.
 260    *
 261    * Called prior to build form.
 262    */
 263   public function preProcess() {
 264     parent::preProcess();
 265   }
 266
 267   /**
 268    * Build from clause.
 269    */
 270   public function from() {
 271     $deferredRelationship = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Deferred Revenue Account is' "));
 272     $revenueRelationship = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Income Account is' "));
 273     $this->_from = "
 274       FROM civicrm_financial_item {$this->_aliases['civicrm_financial_item']}
 275       INNER JOIN civicrm_entity_financial_account entity_financial_account_deferred
 276         ON {$this->_aliases['civicrm_financial_item']}.financial_account_id = entity_financial_account_deferred.financial_account_id
 277         AND entity_financial_account_deferred.entity_table = 'civicrm_financial_type'
 278         AND entity_financial_account_deferred.account_relationship = {$deferredRelationship}
 279       INNER JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account']}
 280         ON entity_financial_account_deferred.financial_account_id = {$this->_aliases['civicrm_financial_account']}.id
 281       INNER JOIN civicrm_entity_financial_account entity_financial_account_revenue
 282         ON entity_financial_account_deferred.entity_id = entity_financial_account_revenue.entity_id
 283         AND entity_financial_account_deferred.entity_table= entity_financial_account_revenue.entity_table
 284       INNER JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account_1']}
 285         ON entity_financial_account_revenue.financial_account_id = {$this->_aliases['civicrm_financial_account_1']}.id
 286         AND {$revenueRelationship} = entity_financial_account_revenue.account_relationship
 287       INNER JOIN civicrm_entity_financial_trxn entity_financial_trxn_item
 288         ON entity_financial_trxn_item.entity_id = {$this->_aliases['civicrm_financial_item']}.id
 289         AND entity_financial_trxn_item.entity_table = 'civicrm_financial_item'
 290       INNER JOIN civicrm_financial_trxn {$this->_aliases['civicrm_financial_trxn_1']}
 291         ON {$this->_aliases['civicrm_financial_trxn_1']}.to_financial_account_id = {$this->_aliases['civicrm_financial_account']}.id
 292         AND {$this->_aliases['civicrm_financial_trxn_1']}.id =  entity_financial_trxn_item.financial_trxn_id
 293       INNER JOIN civicrm_entity_financial_trxn financial_trxn_contribution
 294         ON financial_trxn_contribution.financial_trxn_id = {$this->_aliases['civicrm_financial_trxn_1']}.id
 295         AND financial_trxn_contribution.entity_table = 'civicrm_contribution'
 296       INNER JOIN civicrm_entity_financial_trxn entity_financial_trxn_contribution
 297         ON entity_financial_trxn_contribution.entity_id = {$this->_aliases['civicrm_financial_item']}.id
 298         AND entity_financial_trxn_contribution.entity_table = 'civicrm_financial_item'
 299       INNER JOIN civicrm_financial_trxn {$this->_aliases['civicrm_financial_trxn']}
 300         ON {$this->_aliases['civicrm_financial_trxn']}.id = entity_financial_trxn_contribution.financial_trxn_id
 301         AND ({$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id NOT IN (" . implode(',', array_keys($this->_deferredFinancialAccount)) . ")
 302         OR {$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id IS NULL)
 303       INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
 304         ON {$this->_aliases['civicrm_contribution']}.id = financial_trxn_contribution.entity_id
 305       INNER JOIN civicrm_line_item line_item
 306         ON line_item.contribution_id = {$this->_aliases['civicrm_contribution']}.id
 307         AND line_item.financial_type_id = entity_financial_account_deferred.entity_id
 308       LEFT JOIN civicrm_participant {$this->_aliases['civicrm_participant']}
 309         ON CASE
 310           WHEN line_item.entity_table = 'civicrm_participant'
 311           THEN line_item.entity_id = {$this->_aliases['civicrm_participant']}.id
 312           ELSE {$this->_aliases['civicrm_participant']}.id = 0
 313         END
 314       LEFT JOIN civicrm_event {$this->_aliases['civicrm_event']}
 315         ON {$this->_aliases['civicrm_participant']}.event_id = {$this->_aliases['civicrm_event']}.id";
 316
 317     if ($this->isTableSelected('civicrm_contact')) {
 318       $this->_from .= "
 319         INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 320           ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id";
 321     }
 322
 323     if ($this->isTableSelected('civicrm_membership')) {
 324       $this->_from .= "
 325         LEFT JOIN  civicrm_membership {$this->_aliases['civicrm_membership']}
 326           ON CASE
 327             WHEN line_item.entity_table = 'civicrm_membership'
 328             THEN line_item.entity_id = {$this->_aliases['civicrm_membership']}.id
 329             ELSE {$this->_aliases['civicrm_membership']}.id = 0
 330           END";
 331     }
 332
 333     if ($this->isTableSelected('civicrm_batch')) {
 334       $this->_from .= "
 335         LEFT JOIN civicrm_entity_batch {$this->_aliases['civicrm_batch']}
 336           ON {$this->_aliases['civicrm_batch']}.entity_id = {$this->_aliases['civicrm_financial_trxn']}.id
 337           AND {$this->_aliases['civicrm_batch']}.entity_table = 'civicrm_financial_trxn'";
 338     }
 339   }
 340
 341   /**
 342    * Post process function.
 343    */
 344   public function postProcess() {
 345     $this->_noFields = TRUE;
 346     parent::postProcess();
 347   }
 348
 349   /**
 350    * Set limit.
 351    *
 352    * @param int|null $rowCount
 353    */
 354   public function limit($rowCount = NULL) {
 355     $rowCount = $rowCount ?? $this->getRowCount();
 356     $this->_limit = NULL;
 357   }
 358
 359   /**
 360    * Build where clause.
 361    */
 362   public function where() {
 363     parent::where();
 364     $startDate = date('Y-m-01');
 365     $endDate = date('Y-m-t', strtotime(date('ymd') . '+11 month'));
 366     $this->_where .= " AND {$this->_aliases['civicrm_financial_trxn_1']}.trxn_date BETWEEN '{$startDate}' AND '{$endDate}'";
 367   }
 368
 369   /**
 370    * Build group by clause.
 371    */
 372   public function groupBy() {
 373     $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_financial_account']}.id,  {$this->_aliases['civicrm_financial_account_1']}.id, {$this->_aliases['civicrm_financial_item']}.id";
 374     $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect(
 375       $this->_selectClauses,
 376       [
 377         "{$this->_aliases['civicrm_financial_account_1']}.id",
 378         "{$this->_aliases['civicrm_financial_item']}.id",
 379       ]
 380     );
 381   }
 382
 383   /**
 384    * Modify column headers.
 385    */
 386   public function modifyColumnHeaders() {
 387     // Re-order the columns in a custom order defined below.
 388     $sortArray = [
 389       'civicrm_batch_batch_id',
 390       'civicrm_financial_trxn_status_id',
 391       'civicrm_financial_trxn_trxn_date',
 392       'civicrm_contribution_receive_date',
 393       'civicrm_contribution_cancel_date',
 394       'civicrm_contribution_revenue_recognition_date',
 395       'civicrm_financial_trxn_total_amount',
 396       'civicrm_financial_item_description',
 397       'civicrm_contribution_contact_id',
 398       'civicrm_contact_display_name',
 399       'civicrm_contribution_source',
 400     ];
 401     // Only re-order selected columns.
 402     $sortArray = array_flip(array_intersect_key(array_flip($sortArray), $this->_columnHeaders));
 403
 404     // Re-ordering.
 405     $this->_columnHeaders = array_merge(array_flip($sortArray), $this->_columnHeaders);
 406
 407     // Add months to the columns.
 408     if ($this->_params['revenue_recognition_date_toggle_value']) {
 409       $this->_columnHeaders[date('M, Y', strtotime(date('Y-m-d')))] = [
 410         'title' => date('M, Y', strtotime(date('Y-m-d'))),
 411         'type' => CRM_Utils_Type::T_DATE,
 412       ];
 413     }
 414     else {
 415       for ($i = 0; $i < 12; $i++) {
 416         $this->_columnHeaders[date('M, Y', strtotime(date('Y-m-d') . "+{$i} month"))] = [
 417           'title' => date('M, Y', strtotime(date('Y-m-d') . "+{$i} month")),
 418           'type' => CRM_Utils_Type::T_DATE,
 419         ];
 420       }
 421     }
 422   }
 423
 424   /**
 425    * Build output rows.
 426    *
 427    * @param string $sql
 428    * @param array $rows
 429    *
 430    * @throws \CRM_Core_Exception
 431    */
 432   public function buildRows($sql, &$rows) {
 433     $dao = CRM_Core_DAO::executeQuery($sql);
 434
 435     // use this method to modify $this->_columnHeaders
 436     $this->modifyColumnHeaders();
 437
 438     // Get custom date format.
 439     $dateFormat = Civi::settings()->get('dateformatFinancialBatch');
 440
 441     if (!is_array($rows)) {
 442       $rows = [];
 443     }
 444
 445     while ($dao->fetch()) {
 446       $row = [];
 447       foreach ($this->_columnHeaders as $key => $value) {
 448         $arraykey = $dao->civicrm_financial_account_id . '_' . $dao->civicrm_financial_account_1_id;
 449
 450         if (property_exists($dao, $key)) {
 451           if (CRM_Utils_Array::value('type', $value) & CRM_Utils_Type::T_DATE) {
 452             $row[$key] = CRM_Utils_Date::customFormat($dao->$key, $dateFormat);
 453           }
 454           elseif (CRM_Utils_Array::value('type', $value) & CRM_Utils_Type::T_MONEY) {
 455             $values = [];
 456             foreach (explode(',', $dao->$key) as $moneyValue) {
 457               $values[] = CRM_Utils_Money::format($moneyValue);
 458             }
 459             $row[$key] = implode(',', $values);
 460           }
 461           else {
 462             $row[$key] = $dao->$key;
 463           }
 464         }
 465
 466         $rows[$arraykey]['rows'][$dao->civicrm_financial_item_id] = $row;
 467         $rows[$arraykey]['label'] = "Deferred Revenue Account: {$dao->civicrm_financial_account_name} ({$dao->civicrm_financial_account_accounting_code}), Revenue Account: {$dao->civicrm_financial_account_1_name} {$dao->civicrm_financial_account_1_accounting_code}";
 468         $trxnDate = explode(',', $dao->civicrm_financial_trxn_1_trxn_date);
 469         $trxnAmount = explode(',', $dao->civicrm_financial_trxn_1_total_amount);
 470         foreach ($trxnDate as $trxnKey => $date) {
 471           $keyDate = date('M, Y', strtotime($date));
 472           $rows[$arraykey]['rows'][$dao->civicrm_financial_item_id][$keyDate] = CRM_Utils_Money::format($trxnAmount[$trxnKey]);
 473         }
 474       }
 475     }
 476   }
 477
 478   /**
 479    * Alter display of rows.
 480    *
 481    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 482    * such as rendering contacts as links.
 483    *
 484    * @param array $rows
 485    *   Rows generated by SQL, with an array for each row.
 486    */
 487   public function alterDisplay(&$rows) {
 488     $entryFound = FALSE;
 489     $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name');
 490
 491     foreach ($rows as &$entry) {
 492       foreach ($entry['rows'] as $rowNum => &$row) {
 493
 494         // convert transaction status id to status name
 495         if ($status = CRM_Utils_Array::value('civicrm_financial_trxn_status_id', $row)) {
 496           $row['civicrm_financial_trxn_status_id'] = CRM_Core_PseudoConstant::getLabel('CRM_Core_BAO_FinancialTrxn', 'status_id', $status);
 497           $entryFound = TRUE;
 498         }
 499
 500         // convert batch id to batch title
 501         if ($batchId = CRM_Utils_Array::value('civicrm_batch_batch_id', $row)) {
 502           $row['civicrm_batch_batch_id'] = $this->getLabels($batchId, 'CRM_Batch_BAO_EntityBatch', 'batch_id');
 503           $entryFound = TRUE;
 504         }
 505
 506         // add hotlink for contribution
 507         if ($amount = CRM_Utils_Array::value('civicrm_financial_trxn_total_amount', $row)) {
 508           $contributionUrl = CRM_Utils_System::url("civicrm/contact/view/contribution",
 509             'reset=1&action=view&cid=' . $row['civicrm_contact_id'] . '&id=' . $row['civicrm_contribution_contribution_id'],
 510             $this->_absoluteUrl
 511           );
 512           $row['civicrm_financial_trxn_total_amount'] = "<a href={$contributionUrl}>{$amount}</a>";
 513           if ($contributionId = CRM_Utils_Array::value('civicrm_contribution_id', $row)) {
 514             $row['civicrm_contribution_id'] = "<a href={$contributionUrl}>{$contributionId}</a>";
 515           }
 516           $entryFound = TRUE;
 517         }
 518
 519         // add hotlink for contact
 520         if ($contactName = CRM_Utils_Array::value('civicrm_contact_display_name', $row)) {
 521           $contactUrl = CRM_Utils_System::url("civicrm/contact/view",
 522             'reset=1&cid=' . $row['civicrm_contact_id'],
 523             $this->_absoluteUrl
 524           );
 525           $row['civicrm_contact_display_name'] = "<a href={$contactUrl}>{$contactName}</a>";
 526           $entryFound = TRUE;
 527         }
 528
 529         if ($contactId = CRM_Utils_Array::value('civicrm_contribution_contact_id', $row)) {
 530           $contactUrl = CRM_Utils_System::url("civicrm/contact/view",
 531             'reset=1&cid=' . $row['civicrm_contact_id'],
 532             $this->_absoluteUrl
 533           );
 534           $row['civicrm_contribution_contact_id'] = "<a href={$contactUrl}>{$contactId}</a>";
 535           $entryFound = TRUE;
 536         }
 537       }
 538     }
 539   }
 540
 541 }