CRM/Report/Form/Contribute/DeferredRevenue.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2016                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  10  | CiviCRM is free software; you can copy, modify, and distribute it  |
  11  | under the terms of the GNU Affero General Public License           |
  12  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13  |                                                                    |
  14  | CiviCRM is distributed in the hope that it will be useful, but     |
  15  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17  | See the GNU Affero General Public License for more details.        |
  18  |                                                                    |
  19  | You should have received a copy of the GNU Affero General Public   |
  20  | License and the CiviCRM Licensing Exception along                  |
  21  | with this program; if not, contact CiviCRM LLC                     |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2016
  32  * $Id$
  33  *
  34  */
  35 class CRM_Report_Form_Contribute_DeferredRevenue extends CRM_Report_Form {
  36
  37   /**
  38    * Holds Deferred Financial Account
  39    */
  40   protected $_deferredFinancialAccount = array();
  41
  42   /**
  43    */
  44   public function __construct() {
  45     $this->_autoIncludeIndexedFieldsAsOrderBys = 1;
  46     $this->_deferredFinancialAccount = CRM_Financial_BAO_FinancialAccount::getAllDeferredFinancialAccount();
  47     $this->_columns = array(
  48       'civicrm_financial_account' => array(
  49         'dao' => 'CRM_Financial_DAO_FinancialAccount',
  50         'alias' => 'financial_account_deferred',
  51         'filters' => array(
  52           'id' => array(
  53             'title' => ts('Deferred Financial Account'),
  54             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
  55             'options' => $this->_deferredFinancialAccount,
  56             'type' => CRM_Utils_Type::T_INT,
  57           ),
  58         ),
  59       ),
  60     );
  61     parent::__construct();
  62   }
  63
  64   public function preProcess() {
  65     parent::preProcess();
  66   }
  67
  68   public function select() {
  69     // TODO: add column
  70     $this->_select = ' SELECT
  71 financial_account_deferred_civireport.name deferred_account,
  72 financial_account_deferred_civireport.id deferred_account_id,
  73 financial_account_deferred_civireport.accounting_code deferred_account_code,
  74 financial_account_revenue.name revenue_account,
  75 financial_account_revenue.id revenue_account_id,
  76 financial_account_revenue.accounting_code revenue_account_code,
  77 financial_item.status_id,
  78 financial_item.id item_id,
  79 financial_trxn_contribution_1.status_id,
  80 financial_trxn_contribution_1.trxn_date transaction_date,
  81 financial_trxn_contribution_1.total_amount,
  82 contribution.id contribution_id,
  83 contribution.contact_id,
  84 contact.display_name,
  85 contribution.source,
  86 GROUP_CONCAT(financial_trxn.total_amount) trxn_amount,
  87 GROUP_CONCAT(financial_trxn.trxn_date) trxn_date,
  88 financial_item.description,
  89 IFNULL(membership.start_date, event.start_date) start_date,
  90 IFNULL(membership.end_date, event.end_date) end_date
  91 ';
  92   }
  93
  94   public function from() {
  95     $deferredRelationship = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Deferred Revenue Account is' "));
  96     $revenueRelationship = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Income Account is' "));
  97     $this->_from = " FROM civicrm_financial_item financial_item
  98 INNER JOIN civicrm_entity_financial_account entity_financial_account_deferred
  99   ON financial_item.financial_account_id = entity_financial_account_deferred.financial_account_id AND entity_financial_account_deferred.entity_table = 'civicrm_financial_type'
 100     AND entity_financial_account_deferred.account_relationship = {$deferredRelationship}
 101 INNER JOIN civicrm_financial_account financial_account_deferred_civireport
 102   ON entity_financial_account_deferred.financial_account_id = financial_account_deferred_civireport.id
 103 INNER JOIN civicrm_entity_financial_account entity_financial_account_revenue
 104   ON entity_financial_account_deferred.entity_id = entity_financial_account_revenue.entity_id
 105     AND entity_financial_account_deferred.entity_table= entity_financial_account_revenue.entity_table
 106 INNER JOIN civicrm_financial_account financial_account_revenue
 107   ON entity_financial_account_revenue.financial_account_id = financial_account_revenue.id
 108     AND {$revenueRelationship} = entity_financial_account_revenue.account_relationship
 109 INNER JOIN civicrm_entity_financial_trxn entity_financial_trxn_item
 110   ON entity_financial_trxn_item.entity_id = financial_item.id AND entity_financial_trxn_item.entity_table = 'civicrm_financial_item'
 111 INNER JOIN civicrm_financial_trxn financial_trxn
 112   ON financial_trxn.from_financial_account_id = financial_account_deferred_civireport.id AND financial_trxn.id =  entity_financial_trxn_item.financial_trxn_id
 113 INNER JOIN civicrm_entity_financial_trxn financial_trxn_contribution
 114   ON financial_trxn_contribution.financial_trxn_id = financial_trxn.id AND financial_trxn_contribution.entity_table = 'civicrm_contribution'
 115 INNER JOIN civicrm_entity_financial_trxn entity_financial_trxn_contribution ON entity_financial_trxn_contribution.entity_id = financial_item.id and entity_financial_trxn_contribution.entity_table = 'civicrm_financial_item'
 116 INNER JOIN civicrm_financial_trxn financial_trxn_contribution_1 ON financial_trxn_contribution_1.id = entity_financial_trxn_contribution.financial_trxn_id AND (financial_trxn_contribution_1.from_financial_account_id NOT IN (" . implode(',', array_keys($this->_deferredFinancialAccount)) . ") OR financial_trxn_contribution_1.from_financial_account_id IS NULL)
 117 INNER JOIN civicrm_contribution contribution
 118   ON contribution.id = financial_trxn_contribution.entity_id
 119 INNER JOIN civicrm_contact contact
 120   ON contact.id = contribution.contact_id
 121 INNER JOIN civicrm_line_item line_item
 122   ON line_item.contribution_id = contribution.id
 123 LEFT JOIN  civicrm_membership membership
 124   ON CASE
 125     WHEN line_item.entity_table = 'civicrm_membership'
 126     THEN line_item.entity_id = membership.id
 127     ELSE membership.id = 0
 128   END
 129 LEFT JOIN civicrm_participant participant
 130   ON CASE
 131     WHEN line_item.entity_table = 'civicrm_participant'
 132     THEN line_item.entity_id = participant.id
 133     ELSE participant.id = 0
 134   END
 135 LEFT JOIN civicrm_event event ON participant.event_id = event.id
 136 ";
 137   }
 138
 139   public function orderBy() {
 140     parent::orderBy();
 141   }
 142
 143   public function where() {
 144     $clauses = array();
 145     foreach ($this->_columns as $tableName => $table) {
 146       if (array_key_exists('filters', $table)) {
 147         foreach ($table['filters'] as $fieldName => $field) {
 148           $clause = NULL;
 149           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 150             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 151             $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 152             $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 153
 154             $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 155           }
 156           else {
 157             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 158             if ($op) {
 159               $clause = $this->whereClause($field,
 160                 $op,
 161                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 162                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 163                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 164               );
 165             }
 166           }
 167           if (!empty($clause)) {
 168             $clauses[] = $clause;
 169           }
 170         }
 171       }
 172     }
 173     if (!empty($clauses)) {
 174       $this->_where = 'WHERE ' . implode(' AND ', $clauses);
 175     }
 176   }
 177
 178   public function postProcess() {
 179     $this->_noFields = TRUE;
 180     parent::postProcess();
 181   }
 182
 183   public function groupBy() {
 184     $this->_groupBy = "GROUP BY financial_account_deferred_civireport.id, financial_account_revenue.id, financial_item.id";
 185   }
 186
 187   /**
 188    * Build output rows.
 189    *
 190    * @param string $sql
 191    * @param array $rows
 192    */
 193   public function buildRows($sql, &$rows) {
 194     $dao = CRM_Core_DAO::executeQuery($sql);
 195     if (!is_array($rows)) {
 196       $rows = array();
 197     }
 198     $statuses = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name');
 199     $dateColumn = array();
 200     $columns = array(
 201       'Transaction' => 1,
 202       'Date of Transaction' => 1,
 203       'Amount' => 1,
 204       'Contribution ID' => 1,
 205       'Item' => 1,
 206       'Contact ID' => 1,
 207       'Contact Name' => 1,
 208       'Source' => 1,
 209       'Start Date' => 1,
 210       'End Date' => 1,
 211     );
 212     $dateFormat = Civi::settings()->get('dateformatFinancialBatch');
 213     while ($dao->fetch()) {
 214       $arraykey = $dao->deferred_account_id . '_' . $dao->revenue_account_id;
 215       if (empty($rows[$arraykey])) {
 216         $rows[$arraykey]['label'] = "Deferred Revenue Account: {$dao->deferred_account} ({$dao->deferred_account_code}), Revenue Account: {$dao->revenue_account} {$dao->revenue_account_code}";
 217       }
 218       $rows[$arraykey]['rows'][$dao->item_id] = array(
 219         'Transaction' => $statuses[$dao->status_id],
 220         'Date of Transaction' => CRM_Utils_Date::customFormat($dao->transaction_date, $dateFormat),
 221         'Amount' => CRM_Utils_Money::format($dao->total_amount),
 222         'Contribution ID' => $dao->contribution_id,
 223         'Item' => $dao->description,
 224         'Contact ID' => $dao->contact_id,
 225         'Contact Name' => $dao->display_name,
 226         'Source' => $dao->source,
 227         'Start Date' => CRM_Utils_Date::customFormat($dao->start_date, $dateFormat),
 228         'End Date' => CRM_Utils_Date::customFormat($dao->end_date, $dateFormat),
 229       );
 230       $trxnDate = explode(',', $dao->trxn_date);
 231       $trxnAmount = explode(',', $dao->trxn_amount);
 232       foreach ($trxnDate as $key => $date) {
 233         $keyDate = date('M, Y', strtotime($date));
 234         $rows[$arraykey]['rows'][$dao->item_id][$keyDate] = CRM_Utils_Money::format($trxnAmount[$key]);
 235         $dateColumn[date('Ymd', strtotime($date))] = 1;
 236       }
 237     }
 238     ksort($dateColumn);
 239     foreach ($dateColumn as $key => $ignore) {
 240       $columns[date('M, Y', strtotime($key))] = 1;
 241     }
 242     $this->_columnHeaders = $columns;
 243   }
 244   /**
 245    * @param $rows
 246    *
 247    */
 248   public function statistics(&$rows) {}
 249
 250   /**
 251    * Alter display of rows.
 252    *
 253    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 254    * such as rendering contacts as links.
 255    *
 256    * @param array $rows
 257    *   Rows generated by SQL, with an array for each row.
 258    */
 259   public function alterDisplay(&$rows) {}
 260
 261 }