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