From da345655cd91243ba5b4f7f5a63131e22da4bb91 Mon Sep 17 00:00:00 2001 From: Edsel Date: Fri, 17 Mar 2017 17:25:26 +0530 Subject: [PATCH] IIDA-116 Added extra columns and made existing columns selectable Conflicts: CRM/Report/Form/Contribute/DeferredRevenue.php Conflicts: CRM/Report/Form/Contribute/DeferredRevenue.php IIDA-116 Indentation for better readability IIDA-116 Refactored deferred revenue report Conflicts: CRM/Report/Form/Contribute/DeferredRevenue.php Conflicts: CRM/Report/Form/Contribute/DeferredRevenue.php IIDA-119 Used API rather than query Conflicts: CRM/Financial/BAO/FinancialAccount.php IIDA-116 Added Revenue Recognition date toggle filter IIDA-116 Added hotlinks IIDA-116 Fixed jenkins errors --- CRM/Financial/BAO/FinancialAccount.php | 18 +- .../Form/Contribute/DeferredRevenue.php | 444 ++++++++++++------ .../Form/Contribute/DeferredRevenue.tpl | 30 +- 3 files changed, 313 insertions(+), 179 deletions(-) diff --git a/CRM/Financial/BAO/FinancialAccount.php b/CRM/Financial/BAO/FinancialAccount.php index 6c429f5d4e..182865e66c 100644 --- a/CRM/Financial/BAO/FinancialAccount.php +++ b/CRM/Financial/BAO/FinancialAccount.php @@ -546,15 +546,17 @@ In other words, please create deferred revenue accounts at Administer > CiviCont * */ public static function getAllDeferredFinancialAccount() { - $query = "SELECT cfa.id, cfa.name FROM civicrm_entity_financial_account ce -INNER JOIN civicrm_financial_account cfa ON ce.financial_account_id = cfa.id -WHERE `entity_table` = 'civicrm_financial_type' AND cfa.is_active = 1 AND ce.account_relationship = %1 GROUP BY cfa.id"; - $deferredAccountRel = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Deferred Revenue Account is' ")); - $queryParams = array(1 => array($deferredAccountRel, 'Integer')); - $dao = CRM_Core_DAO::executeQuery($query, $queryParams); $financialAccount = array(); - while ($dao->fetch()) { - $financialAccount[$dao->id] = $dao->name; + $result = civicrm_api3('EntityFinancialAccount', 'get', array( + 'sequential' => 1, + 'return' => array("financial_account_id.id", "financial_account_id.name", "financial_account_id.accounting_code"), + 'entity_table' => "civicrm_financial_type", + 'account_relationship' => "Deferred Revenue Account is", + )); + if ($result['count'] > 0) { + foreach ($result['values'] as $key => $value) { + $financialAccount[$value['financial_account_id.id']] = $value['financial_account_id.name'] . ' (' . $value['financial_account_id.accounting_code'] . ')'; + } } return $financialAccount; } diff --git a/CRM/Report/Form/Contribute/DeferredRevenue.php b/CRM/Report/Form/Contribute/DeferredRevenue.php index 8b34e55c9b..ae75beb6b5 100644 --- a/CRM/Report/Form/Contribute/DeferredRevenue.php +++ b/CRM/Report/Form/Contribute/DeferredRevenue.php @@ -45,6 +45,86 @@ class CRM_Report_Form_Contribute_DeferredRevenue extends CRM_Report_Form { $this->_exposeContactID = FALSE; $this->_deferredFinancialAccount = CRM_Financial_BAO_FinancialAccount::getAllDeferredFinancialAccount(); $this->_columns = array( + 'civicrm_financial_trxn' => array( + 'dao' => 'CRM_Financial_DAO_FinancialTrxn', + 'fields' => array( + 'status_id' => array( + 'title' => ts('Transaction'), + ), + 'trxn_date' => array( + 'title' => ts('Transaction Date'), + 'type' => CRM_Utils_Type::T_DATE, + 'required' => TRUE, + ), + 'total_amount' => array( + 'title' => ts('Transaction Amount'), + 'type' => CRM_Utils_Type::T_MONEY, + 'required' => TRUE, + ), + ), + 'filters' => array( + 'trxn_date' => array( + 'title' => ts('Transaction Date'), + 'operatorType' => CRM_Report_Form::OP_DATE, + 'type' => CRM_Utils_Type::T_DATE, + ), + ), + ), + 'civicrm_contribution' => array( + 'dao' => 'CRM_Contribute_DAO_Contribution', + 'fields' => array( + 'id' => array( + 'title' => ts('Contribution ID'), + ), + 'contribution_id' => array( + 'title' => ts('Contribution ID'), + 'required' => TRUE, + 'no_display' => TRUE, + 'dbAlias' => 'contribution_civireport.id', + ), + 'contact_id' => array( + 'title' => ts('Contact ID'), + ), + 'source' => array( + 'title' => ts('Source'), + ), + 'receive_date' => array( + 'title' => ts('Receive Date'), + 'type' => CRM_Utils_Type::T_DATE, + ), + 'cancel_date' => array( + 'title' => ts('Cancel Date'), + 'type' => CRM_Utils_Type::T_DATE, + ), + 'revenue_recognition_date' => array( + 'title' => ts('Revenue Recognition Date'), + 'type' => CRM_Utils_Type::T_DATE, + ), + ), + 'filters' => array( + 'receive_date' => array( + 'title' => ts('Receive Date'), + 'operatorType' => CRM_Report_Form::OP_DATE, + 'type' => CRM_Utils_Type::T_DATE, + ), + 'cancel_date' => array( + 'title' => ts('Cancel Date'), + 'operatorType' => CRM_Report_Form::OP_DATE, + 'type' => CRM_Utils_Type::T_DATE, + ), + 'revenue_recognition_date' => array( + 'title' => ts('Revenue Recognition Date'), + 'operatorType' => CRM_Report_Form::OP_DATE, + 'type' => CRM_Utils_Type::T_DATE, + ), + 'revenue_recognition_date_toggle' => array( + 'title' => ts("Current month's revenue?"), + 'type' => CRM_Utils_Type::T_BOOLEAN, + 'default' => 0, + 'pseudofield' => TRUE, + ), + ), + ), 'civicrm_financial_account' => array( 'dao' => 'CRM_Financial_DAO_FinancialAccount', 'fields' => array( @@ -107,9 +187,7 @@ class CRM_Report_Form_Contribute_DeferredRevenue extends CRM_Report_Form { 'no_display' => TRUE, ), 'description' => array( - 'title' => ts('Description'), - 'required' => TRUE, - 'no_display' => TRUE, + 'title' => ts('Item'), ), ), ), @@ -118,6 +196,7 @@ class CRM_Report_Form_Contribute_DeferredRevenue extends CRM_Report_Form { 'fields' => array( 'total_amount' => array( 'title' => ts('Deferred Transaction Amount'), + 'type' => CRM_Utils_Type::T_MONEY, 'required' => TRUE, 'no_display' => TRUE, 'dbAlias' => 'GROUP_CONCAT(financial_trxn_1_civireport.total_amount)', @@ -127,6 +206,7 @@ class CRM_Report_Form_Contribute_DeferredRevenue extends CRM_Report_Form { 'required' => TRUE, 'no_display' => TRUE, 'dbAlias' => 'GROUP_CONCAT(financial_trxn_1_civireport.trxn_date)', + 'type' => CRM_Utils_Type::T_DATE, ), ), ), @@ -134,7 +214,10 @@ class CRM_Report_Form_Contribute_DeferredRevenue extends CRM_Report_Form { 'dao' => 'CRM_Contact_DAO_Contact', 'fields' => array( 'display_name' => array( - 'title' => ts('Display_name'), + 'title' => ts('Contact Name'), + ), + 'id' => array( + 'title' => ts('Contact ID'), 'required' => TRUE, 'no_display' => TRUE, ), @@ -145,15 +228,13 @@ class CRM_Report_Form_Contribute_DeferredRevenue extends CRM_Report_Form { 'fields' => array( 'start_date' => array( 'title' => ts('Start Date'), - 'required' => TRUE, - 'no_display' => TRUE, 'dbAlias' => 'IFNULL(membership_civireport.start_date, event_civireport.start_date)', + 'type' => CRM_Utils_Type::T_DATE, ), 'end_date' => array( 'title' => ts('End Date'), - 'required' => TRUE, - 'no_display' => TRUE, 'dbdbAlias' => 'IFNULL(membership_civireport.end_date, event_civireport.end_date)', + 'type' => CRM_Utils_Type::T_DATE, ), ), ), @@ -166,6 +247,11 @@ class CRM_Report_Form_Contribute_DeferredRevenue extends CRM_Report_Form { 'civicrm_batch' => array( 'dao' => 'CRM_Batch_DAO_EntityBatch', 'grouping' => 'contri-fields', + 'fields' => array( + 'batch_id' => array( + 'title' => ts('Batch Title'), + ), + ), 'filters' => array( 'batch_id' => array( 'title' => ts('Batch Title'), @@ -175,70 +261,6 @@ class CRM_Report_Form_Contribute_DeferredRevenue extends CRM_Report_Form { ), ), ), - 'civicrm_contribution' => array( - 'dao' => 'CRM_Contribute_DAO_Contribution', - 'fields' => array( - 'id' => array( - 'title' => ts('Contribution ID'), - 'required' => TRUE, - 'no_display' => TRUE, - ), - 'contact_id' => array( - 'title' => ts('Contact ID'), - 'required' => TRUE, - 'no_display' => TRUE, - ), - 'source' => array( - 'title' => ts('Source'), - 'required' => TRUE, - 'no_display' => TRUE, - ), - ), - 'filters' => array( - 'receive_date' => array( - 'title' => ts('Receive Date'), - 'operatorType' => CRM_Report_Form::OP_DATE, - 'type' => CRM_Utils_Type::T_DATE, - ), - 'cancel_date' => array( - 'title' => ts('Cancel Date'), - 'operatorType' => CRM_Report_Form::OP_DATE, - 'type' => CRM_Utils_Type::T_DATE, - ), - 'revenue_recognition_date' => array( - 'title' => ts('Revenue Recognition Date'), - 'operatorType' => CRM_Report_Form::OP_DATE, - 'type' => CRM_Utils_Type::T_DATE, - ), - ), - ), - 'civicrm_financial_trxn' => array( - 'dao' => 'CRM_Financial_DAO_FinancialTrxn', - 'fields' => array( - 'status_id' => array( - 'title' => ts('Transaction Status'), - 'required' => TRUE, - 'no_display' => TRUE, - ), - 'trxn_date' => array( - 'title' => ts('Transaction Date'), - 'required' => TRUE, - 'no_display' => TRUE, - ), - 'total_amount' => array( - 'title' => ts('Transaction Amount'), - 'required' => TRUE, - 'no_display' => TRUE, - ), - ), - 'filters' => array( - 'trxn_date' => array( - 'title' => ts('Transaction Date'), - 'operatorType' => CRM_Report_Form::OP_DATE, - 'type' => CRM_Utils_Type::T_DATE, - ), - ), - ), ); parent::__construct(); } @@ -258,52 +280,70 @@ class CRM_Report_Form_Contribute_DeferredRevenue extends CRM_Report_Form { public function from() { $deferredRelationship = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Deferred Revenue Account is' ")); $revenueRelationship = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Income Account is' ")); - $this->_from = " FROM civicrm_financial_item {$this->_aliases['civicrm_financial_item']} -INNER JOIN civicrm_entity_financial_account entity_financial_account_deferred - ON {$this->_aliases['civicrm_financial_item']}.financial_account_id = entity_financial_account_deferred.financial_account_id AND entity_financial_account_deferred.entity_table = 'civicrm_financial_type' - AND entity_financial_account_deferred.account_relationship = {$deferredRelationship} -INNER JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account']} - ON entity_financial_account_deferred.financial_account_id = {$this->_aliases['civicrm_financial_account']}.id -INNER JOIN civicrm_entity_financial_account entity_financial_account_revenue - ON entity_financial_account_deferred.entity_id = entity_financial_account_revenue.entity_id - AND entity_financial_account_deferred.entity_table= entity_financial_account_revenue.entity_table -INNER JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account_1']} - ON entity_financial_account_revenue.financial_account_id = {$this->_aliases['civicrm_financial_account_1']}.id - AND {$revenueRelationship} = entity_financial_account_revenue.account_relationship -INNER JOIN civicrm_entity_financial_trxn entity_financial_trxn_item - ON entity_financial_trxn_item.entity_id = {$this->_aliases['civicrm_financial_item']}.id AND entity_financial_trxn_item.entity_table = 'civicrm_financial_item' -INNER JOIN civicrm_financial_trxn {$this->_aliases['civicrm_financial_trxn_1']} - ON {$this->_aliases['civicrm_financial_trxn_1']}.from_financial_account_id = {$this->_aliases['civicrm_financial_account']}.id AND {$this->_aliases['civicrm_financial_trxn_1']}.id = entity_financial_trxn_item.financial_trxn_id -INNER JOIN civicrm_entity_financial_trxn financial_trxn_contribution - ON financial_trxn_contribution.financial_trxn_id = {$this->_aliases['civicrm_financial_trxn_1']}.id AND financial_trxn_contribution.entity_table = 'civicrm_contribution' -INNER JOIN civicrm_entity_financial_trxn entity_financial_trxn_contribution ON entity_financial_trxn_contribution.entity_id = {$this->_aliases['civicrm_financial_item']}.id and entity_financial_trxn_contribution.entity_table = 'civicrm_financial_item' -INNER JOIN civicrm_financial_trxn {$this->_aliases['civicrm_financial_trxn']} ON {$this->_aliases['civicrm_financial_trxn']}.id = entity_financial_trxn_contribution.financial_trxn_id AND ({$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id NOT IN (" . implode(',', array_keys($this->_deferredFinancialAccount)) . ") OR {$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id IS NULL) -INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} - ON {$this->_aliases['civicrm_contribution']}.id = financial_trxn_contribution.entity_id -INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']} - ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id -INNER JOIN civicrm_line_item line_item - ON line_item.contribution_id = {$this->_aliases['civicrm_contribution']}.id -LEFT JOIN civicrm_membership {$this->_aliases['civicrm_membership']} - ON CASE - WHEN line_item.entity_table = 'civicrm_membership' - THEN line_item.entity_id = {$this->_aliases['civicrm_membership']}.id - ELSE {$this->_aliases['civicrm_membership']}.id = 0 - END -LEFT JOIN civicrm_participant {$this->_aliases['civicrm_participant']} - ON CASE - WHEN line_item.entity_table = 'civicrm_participant' - THEN line_item.entity_id = {$this->_aliases['civicrm_participant']}.id - ELSE {$this->_aliases['civicrm_participant']}.id = 0 - END -LEFT JOIN civicrm_event {$this->_aliases['civicrm_event']} ON {$this->_aliases['civicrm_participant']}.event_id = {$this->_aliases['civicrm_event']}.id -"; + $this->_from = " + FROM civicrm_financial_item {$this->_aliases['civicrm_financial_item']} + INNER JOIN civicrm_entity_financial_account entity_financial_account_deferred + ON {$this->_aliases['civicrm_financial_item']}.financial_account_id = entity_financial_account_deferred.financial_account_id + AND entity_financial_account_deferred.entity_table = 'civicrm_financial_type' + AND entity_financial_account_deferred.account_relationship = {$deferredRelationship} + INNER JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account']} + ON entity_financial_account_deferred.financial_account_id = {$this->_aliases['civicrm_financial_account']}.id + INNER JOIN civicrm_entity_financial_account entity_financial_account_revenue + ON entity_financial_account_deferred.entity_id = entity_financial_account_revenue.entity_id + AND entity_financial_account_deferred.entity_table= entity_financial_account_revenue.entity_table + INNER JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account_1']} + ON entity_financial_account_revenue.financial_account_id = {$this->_aliases['civicrm_financial_account_1']}.id + AND {$revenueRelationship} = entity_financial_account_revenue.account_relationship + INNER JOIN civicrm_entity_financial_trxn entity_financial_trxn_item + ON entity_financial_trxn_item.entity_id = {$this->_aliases['civicrm_financial_item']}.id + AND entity_financial_trxn_item.entity_table = 'civicrm_financial_item' + INNER JOIN civicrm_financial_trxn {$this->_aliases['civicrm_financial_trxn_1']} + ON {$this->_aliases['civicrm_financial_trxn_1']}.from_financial_account_id = {$this->_aliases['civicrm_financial_account']}.id + AND {$this->_aliases['civicrm_financial_trxn_1']}.id = entity_financial_trxn_item.financial_trxn_id + INNER JOIN civicrm_entity_financial_trxn financial_trxn_contribution + ON financial_trxn_contribution.financial_trxn_id = {$this->_aliases['civicrm_financial_trxn_1']}.id + AND financial_trxn_contribution.entity_table = 'civicrm_contribution' + INNER JOIN civicrm_entity_financial_trxn entity_financial_trxn_contribution + ON entity_financial_trxn_contribution.entity_id = {$this->_aliases['civicrm_financial_item']}.id + AND entity_financial_trxn_contribution.entity_table = 'civicrm_financial_item' + INNER JOIN civicrm_financial_trxn {$this->_aliases['civicrm_financial_trxn']} + ON {$this->_aliases['civicrm_financial_trxn']}.id = entity_financial_trxn_contribution.financial_trxn_id + AND ({$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id NOT IN (" . implode(',', array_keys($this->_deferredFinancialAccount)) . ") + OR {$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id IS NULL) + INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} + ON {$this->_aliases['civicrm_contribution']}.id = financial_trxn_contribution.entity_id + INNER JOIN civicrm_line_item line_item + ON line_item.contribution_id = {$this->_aliases['civicrm_contribution']}.id + LEFT JOIN civicrm_participant {$this->_aliases['civicrm_participant']} + ON CASE + WHEN line_item.entity_table = 'civicrm_participant' + THEN line_item.entity_id = {$this->_aliases['civicrm_participant']}.id + ELSE {$this->_aliases['civicrm_participant']}.id = 0 + END + LEFT JOIN civicrm_event {$this->_aliases['civicrm_event']} + ON {$this->_aliases['civicrm_participant']}.event_id = {$this->_aliases['civicrm_event']}.id"; + + if ($this->isTableSelected('civicrm_contact')) { + $this->_from .= " + INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']} + ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id"; + } + + if ($this->isTableSelected('civicrm_membership')) { + $this->_from .= " + LEFT JOIN civicrm_membership {$this->_aliases['civicrm_membership']} + ON CASE + WHEN line_item.entity_table = 'civicrm_membership' + THEN line_item.entity_id = {$this->_aliases['civicrm_membership']}.id + ELSE {$this->_aliases['civicrm_membership']}.id = 0 + END"; + } - if (!empty($this->_params['batch_id_value'])) { + if ($this->isTableSelected('civicrm_batch')) { $this->_from .= " LEFT JOIN civicrm_entity_batch {$this->_aliases['civicrm_batch']} - ON {$this->_aliases['civicrm_batch']}.entity_id = {$this->_aliases['civicrm_financial_trxn_1']}.id AND - {$this->_aliases['civicrm_batch']}.entity_table = 'civicrm_financial_trxn'\n"; + ON {$this->_aliases['civicrm_batch']}.entity_id = {$this->_aliases['civicrm_financial_trxn_1']}.id + AND {$this->_aliases['civicrm_batch']}.entity_table = 'civicrm_financial_trxn'"; } } @@ -341,6 +381,47 @@ LEFT JOIN civicrm_event {$this->_aliases['civicrm_event']} ON {$this->_aliases[' $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_financial_account']}.id, {$this->_aliases['civicrm_financial_account_1']}.id, {$this->_aliases['civicrm_financial_item']}.id"; } + /** + * Modify column headers. + */ + public function modifyColumnHeaders() { + // Re-order the columns in a custom order defined below. + $sortArray = array( + 'civicrm_batch_batch_id', + 'civicrm_financial_trxn_status_id', + 'civicrm_financial_trxn_trxn_date', + 'civicrm_contribution_receive_date', + 'civicrm_contribution_cancel_date', + 'civicrm_contribution_revenue_recognition_date', + 'civicrm_financial_trxn_total_amount', + 'civicrm_financial_item_description', + 'civicrm_contribution_contact_id', + 'civicrm_contact_display_name', + 'civicrm_contribution_source', + ); + // Only re-order selected columns. + $sortArray = array_flip(array_intersect_key(array_flip($sortArray), $this->_columnHeaders)); + + // Re-ordering. + $this->_columnHeaders = array_merge(array_flip($sortArray), $this->_columnHeaders); + + // Add months to the columns. + if ($this->_params['revenue_recognition_date_toggle_value']) { + $this->_columnHeaders[date('M, Y', strtotime(date('Y-m-d')))] = array( + 'title' => date('M, Y', strtotime(date('Y-m-d'))), + 'type' => CRM_Utils_Type::T_DATE, + ); + } + else { + for ($i = 0; $i < 12; $i++) { + $this->_columnHeaders[date('M, Y', strtotime(date('Y-m-d') . "+{$i} month"))] = array( + 'title' => date('M, Y', strtotime(date('Y-m-d') . "+{$i} month")), + 'type' => CRM_Utils_Type::T_DATE, + ); + } + } + } + /** * Build output rows. * @@ -349,56 +430,107 @@ LEFT JOIN civicrm_event {$this->_aliases['civicrm_event']} ON {$this->_aliases[' */ public function buildRows($sql, &$rows) { $dao = CRM_Core_DAO::executeQuery($sql); + + // use this method to modify $this->_columnHeaders + $this->modifyColumnHeaders(); + + // Get custom date format. + $dateFormat = Civi::settings()->get('dateformatFinancialBatch'); + if (!is_array($rows)) { $rows = array(); } - $statuses = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name'); - $dateColumn = array(); - $columns = array( - 'Transaction' => 1, - 'Date of Transaction' => 1, - 'Amount' => 1, - 'Contribution ID' => 1, - 'Item' => 1, - 'Contact ID' => 1, - 'Contact Name' => 1, - 'Source' => 1, - 'Start Date' => 1, - 'End Date' => 1, - ); - $dateFormat = Civi::settings()->get('dateformatFinancialBatch'); - for ($i = 0; $i < 12; $i++) { - //$columns[date('M, Y', strtotime("+1 month", date('Y-m-d')))] = 1; - $columns[date('M, Y', strtotime(date('Y-m-d') . "+{$i} month"))] = 1; - } + while ($dao->fetch()) { - $arraykey = $dao->civicrm_financial_account_id . '_' . $dao->civicrm_financial_account_1_id; - if (empty($rows[$arraykey])) { + $row = array(); + foreach ($this->_columnHeaders as $key => $value) { + $arraykey = $dao->civicrm_financial_account_id . '_' . $dao->civicrm_financial_account_1_id; + + if (property_exists($dao, $key)) { + if (CRM_Utils_Array::value('type', $value) & CRM_Utils_Type::T_DATE) { + $row[$key] = CRM_Utils_Date::customFormat($dao->$key, $dateFormat); + } + elseif (CRM_Utils_Array::value('type', $value) & CRM_Utils_Type::T_MONEY) { + $row[$key] = CRM_Utils_Money::format($dao->$key); + } + else { + $row[$key] = $dao->$key; + } + } + + $rows[$arraykey]['rows'][$dao->civicrm_financial_item_id] = $row; $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}"; + $trxnDate = explode(',', $dao->civicrm_financial_trxn_1_trxn_date); + $trxnAmount = explode(',', $dao->civicrm_financial_trxn_1_total_amount); + foreach ($trxnDate as $key => $date) { + $keyDate = date('M, Y', strtotime($date)); + $rows[$arraykey]['rows'][$dao->civicrm_financial_item_id][$keyDate] = CRM_Utils_Money::format($trxnAmount[$key]); + } } - $rows[$arraykey]['rows'][$dao->civicrm_financial_item_id] = array( - 'Transaction' => $statuses[$dao->civicrm_financial_trxn_status_id], - 'Date of Transaction' => CRM_Utils_Date::customFormat($dao->civicrm_financial_trxn_trxn_date, $dateFormat), - 'Amount' => CRM_Utils_Money::format($dao->civicrm_financial_trxn_total_amount), - 'Contribution ID' => $dao->civicrm_contribution_id, - 'Item' => $dao->civicrm_financial_item_description, - 'Contact ID' => $dao->civicrm_contribution_contact_id, - 'Contact Name' => $dao->civicrm_contact_display_name, - 'Source' => $dao->civicrm_contribution_source, - 'Start Date' => CRM_Utils_Date::customFormat($dao->civicrm_membership_start_date, $dateFormat), - 'End Date' => CRM_Utils_Date::customFormat($dao->civicrm_membership_end_date, $dateFormat), - ); - $trxnDate = explode(',', $dao->civicrm_financial_trxn_1_trxn_date); - $trxnAmount = explode(',', $dao->civicrm_financial_trxn_1_total_amount); - foreach ($trxnDate as $key => $date) { - $keyDate = date('M, Y', strtotime($date)); - if (!array_key_exists($keyDate, $columns)) { - continue; + } + } + + /** + * Alter display of rows. + * + * Iterate through the rows retrieved via SQL and make changes for display purposes, + * such as rendering contacts as links. + * + * @param array $rows + * Rows generated by SQL, with an array for each row. + */ + public function alterDisplay(&$rows) { + $entryFound = FALSE; + $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name'); + + foreach ($rows as &$entry) { + foreach ($entry['rows'] as $rowNum => &$row) { + + // convert transaction status id to status name + if ($status = CRM_Utils_Array::value('civicrm_financial_trxn_status_id', $row)) { + $row['civicrm_financial_trxn_status_id'] = CRM_Core_PseudoConstant::getLabel('CRM_Core_BAO_FinancialTrxn', 'status_id', $status); + $entryFound = TRUE; + } + + // convert batch id to batch title + if ($batchId = CRM_Utils_Array::value('civicrm_batch_batch_id', $row)) { + $row['civicrm_batch_batch_id'] = $this->getLabels($batchId, 'CRM_Batch_BAO_EntityBatch', 'batch_id'); + $entryFound = TRUE; + } + + // add hotlink for contribution + if ($amount = CRM_Utils_Array::value('civicrm_financial_trxn_total_amount', $row)) { + $contributionUrl = CRM_Utils_System::url("civicrm/contact/view/contribution", + 'reset=1&action=view&cid=' . $row['civicrm_contact_id'] . '&id=' . $row['civicrm_contribution_contribution_id'], + $this->_absoluteUrl + ); + $row['civicrm_financial_trxn_total_amount'] = "{$amount}"; + if ($contributionId = CRM_Utils_Array::value('civicrm_contribution_id', $row)) { + $row['civicrm_contribution_id'] = "{$contributionId}"; + } + $entryFound = TRUE; + } + + // add hotlink for contact + if ($contactName = CRM_Utils_Array::value('civicrm_contact_display_name', $row)) { + $contactUrl = CRM_Utils_System::url("civicrm/contact/view", + 'reset=1&cid=' . $row['civicrm_contact_id'], + $this->_absoluteUrl + ); + $row['civicrm_contact_display_name'] = "{$contactName}"; + $entryFound = TRUE; + } + + if ($contactId = CRM_Utils_Array::value('civicrm_contribution_contact_id', $row)) { + $contactUrl = CRM_Utils_System::url("civicrm/contact/view", + 'reset=1&cid=' . $row['civicrm_contact_id'], + $this->_absoluteUrl + ); + $row['civicrm_contribution_contact_id'] = "{$contactId}"; + $entryFound = TRUE; } - $rows[$arraykey]['rows'][$dao->civicrm_financial_item_id][$keyDate] = CRM_Utils_Money::format($trxnAmount[$key]); } } - $this->_columnHeaders = $columns; } } diff --git a/templates/CRM/Report/Form/Contribute/DeferredRevenue.tpl b/templates/CRM/Report/Form/Contribute/DeferredRevenue.tpl index c8141b27ec..e949d4ddf2 100644 --- a/templates/CRM/Report/Form/Contribute/DeferredRevenue.tpl +++ b/templates/CRM/Report/Form/Contribute/DeferredRevenue.tpl @@ -38,23 +38,23 @@ {foreach from=$rows item=row} - + - - - {foreach from=$columnHeaders item=ignore key=header} - - {/foreach} - - - {foreach from=$row.rows item=innerRow} - - {foreach from=$columnHeaders item=ignore key=header} - + + + {foreach from=$columnHeaders item=label key=header} + + {/foreach} + + + {foreach from=$row.rows item=innerRow key=rowid} + + {foreach from=$columnHeaders item=ignore key=header} + + {/foreach} + {/foreach} + {/foreach} - {/foreach} - -{/foreach}
{$row.label}
{$row.label}
{$header}
{$innerRow.$header}
{$label.title}
{$innerRow.$header}

-- 2.25.1