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