CiviCRM Community Forums (archive)

Hi - hoping someone can suggest a better solution.

To come up with a list of 'who joined, renewed, resigned, expired' etc in the last week/month etc it seems like we are going to have to tie several searches together since the 'change of status' doesn't seem to be easy to grab from the UI.

- date of renewal can be worked out by doing a search based on 'end date' and working backwards
- date of joined can be got from the 'start' or 'join' dates
- resigned?

IF we work on doing this via Report then we are going to need to grab a whole load of other data including addresses, etc

Does a Custom Search seem to fit the case better?

We just used the detail.php report (or a copy of it) and added the modified date field in + the renewal date (and the custom fields).

We're still working with it to see it works OK

Index: CRM/Report/Form/Member/Detail.php
===================================================================
--- CRM/Report/Form/Member/Detail.php   (revision 26715)
+++ CRM/Report/Form/Member/Detail.php   (working copy)
@@ -37,7 +37,7 @@
 require_once 'CRM/Report/Form.php';
 require_once 'CRM/Member/PseudoConstant.php';
 
-class CRM_Report_Form_Member_Detail extends CRM_Report_Form {
+class CRM_Report_Form_Member_DetailMod extends CRM_Report_Form {
 
     protected $_addressField = false;
     
@@ -46,9 +46,9 @@
     protected $_phoneField   = false;
     
     protected $_summary      = null;
-   
+     protected $_customGroupExtends = array( 'Contact' );   
     function __construct( ) {
-        $this->_columns =
+              $this->_columns =
             array( 'civicrm_contact' =>
                    array( 'dao'     => 'CRM_Contact_DAO_Contact',
                           'fields'  =>
@@ -103,7 +103,30 @@
                           
                           'grouping'=> 'member-fields',
                           ),
-                   
+                   'civicrm_membership_log' =>
+                   array( 'dao'       => 'CRM_Member_DAO_MembershipLog',
+                          'fields'    =>
+                          array(                             
+                                'modified_date'    => array( 'title'     => 'Modified Date',
+                                                                  'default'   => false ),
+                                'renewal_reminder_date' => array( 'title'     => ts('Renewal Reminder Date'),
+                                                                  'default'   => false ),
+                                ),
+                          'filters' => array(                         
+                                             'modified_date'    =>
+                                             array( 'operatorType'  => CRM_Report_Form::OP_DATE,
+                                                    'title'         =>  ts( 'Modified Date' ),
+                                                    ),
+                                             'renewal_reminder_date'    =>
+                                             array( 'operatorType'  => CRM_Report_Form::OP_DATE,
+                                                    'title'         =>  ts( 'Renewal Reminder Date' )
+                                                   ),
+                                             ),
+                         
+                          'grouping'=> 'member-fields',
+                          ),
+                         
+                         
                    'civicrm_membership_status' =>
                    array( 'dao'      => 'CRM_Member_DAO_MembershipStatus',
                           'alias'    => 'mem_status',
@@ -218,9 +241,12 @@
                              {$this->_aliases['civicrm_membership']}.contact_id AND {$this->_aliases['civicrm_membership']}.is_test = 0
                LEFT  JOIN civicrm_membership_status {$this->_aliases['civicrm_membership_status']}
                           ON {$this->_aliases['civicrm_membership_status']}.id =
-                             {$this->_aliases['civicrm_membership']}.status_id ";
+                             {$this->_aliases['civicrm_membership']}.status_id
+               LEFT  JOIN civicrm_membership_log {$this->_aliases['civicrm_membership_log']}
+                          ON {$this->_aliases['civicrm_membership']}.id =
+                             {$this->_aliases['civicrm_membership_log']}.membership_id               
 
-       
+        ";
         //used when address field is selected
         if ( $this->_addressField ) {
             $this->_from .= "