From 9b213560fe662e0fc7626226ff70266779c8599a Mon Sep 17 00:00:00 2001
From: Seamus Lee <seamuslee001@gmail.com>
Date: Mon, 8 Aug 2016 20:54:00 +1000
Subject: [PATCH] CRM-19060 fix Payment table join in membership Detail report
 to ensure latest membership payment is picked up

Fix where no membership type is selected, ensure only most recent contribution is found and pass through date filter to the finding latest contribution sql

Turn max_payment join into joining on tempoary table so that we can create an index for the join

Shift to using civicrm_membership_payment table rather than line item
---
 CRM/Report/Form/Member/Detail.php | 47 ++++++++++++++++++++++++++++---
 1 file changed, 43 insertions(+), 4 deletions(-)

diff --git a/CRM/Report/Form/Member/Detail.php b/CRM/Report/Form/Member/Detail.php
index 2760886720..9fe46495cd 100644
--- a/CRM/Report/Form/Member/Detail.php
+++ b/CRM/Report/Form/Member/Detail.php
@@ -339,13 +339,15 @@ class CRM_Report_Form_Member_Detail extends CRM_Report_Form {
                            {$this->_aliases['civicrm_phone']}.contact_id AND
                            {$this->_aliases['civicrm_phone']}.is_primary = 1\n";
     }
-    //used when contribution field is selected
+    //used when contribution field is selected.
     if ($this->_contribField) {
+      $contribution_table_join = $this->contributionTableJoin();
       $this->_from .= "
-             LEFT JOIN civicrm_membership_payment cmp
-                 ON {$this->_aliases['civicrm_membership']}.id = cmp.membership_id
+             LEFT JOIN $contribution_table_join as max_payment
+                 ON max_payment.entity_id = {$this->_aliases['civicrm_membership']}.id
              LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
-                 ON cmp.contribution_id={$this->_aliases['civicrm_contribution']}.id\n";
+                 ON max_payment.contact_id = {$this->_aliases['civicrm_contribution']}.contact_id
+                 AND max_payment.receive_date = {$this->_aliases['civicrm_contribution']}.receive_date\n";
     }
   }
 
@@ -478,4 +480,41 @@ class CRM_Report_Form_Member_Detail extends CRM_Report_Form {
     }
   }
 
+  /**
+   * Create temporary table to get most recent
+   * contribution for each contact and membership type.
+   *
+   * @return string $tempTable.
+   */
+  public function contributionTableJoin() {
+    static $tempTable = NULL;
+    if (!empty($tempTable)) {
+      return $tempTable;
+    }
+    $params = $this->_params;
+    $receive_date_relative = empty($params['receive_date_relative']) ? '' : $params['receive_date_relative'];
+    $receive_date_from = empty($params['receive_date_from']) ? '' : $params['receive_date_from'];
+    $receive_date_to = empty($params['receive_date_to']) ? '' : $params['receive_date_to'];
+    $dateFilter = parent::dateClause('receive_date', $receive_date_relative,
+      $receive_date_from, $receive_date_to);
+    if ($dateFilter) {
+      $dateWhere = 'AND ' . str_replace('receive_date', 'cc.receive_date', $dateFilter);
+    }
+    else {
+      $dateWhere = '';
+    }
+    $tempTable = CRM_Core_DAO::createTempTableName('civicrm_report_memcontr', TRUE);
+    $sql = "
+      CREATE TEMPORARY TABLE $tempTable (index (contact_id, receive_date, entity_id))
+      SELECT cc.contact_id, max(cc.receive_date) as receive_date, cmp.membership_id as entity_id
+      FROM civicrm_membership_payment cmp
+      INNER JOIN civicrm_contribution cc
+      ON cc.id = cmp.contribution_id
+      WHERE cc.is_test = 0
+      $dateWhere
+      GROUP BY cc.contact_id";
+    CRM_Core_DAO::executeQuery($sql);
+    return $tempTable;
+  }
+
 }
-- 
2.25.1