CiviCRM Community Forums (archive)

check: http://wiki.civicrm.org/confluence/display/CRMDOC/Custom+Search+Components

you need to be familiar with php and mysql to take advantage of this option

lobo

Do your contacts generally have multiple membership records? I'm trying to understand the reasons for needing to do the queries you've described. Generally CiviMember updates status on a single membership record for a given contact as time progresses....

New -> Current -> Grace -> Expired

... and I'm pretty sure that searching (Find Members) on Status = Grace for a particular membership type does NOT return records with status = Current.

Yes - explained :-)
Unfortunately there's no "quick fix" for this that we can think of. The only other approach I can think of (which is probably no better than the query approach u already have) is to export the results of two searches (one on Current status and one on Grace) and use Excel to find the non-dupes in the Grace set.

It is possible my PHP and MySQL skills are not up to par or I overestimate the tasks required.  I will attempt the custom query page and tell you how it turns out.  Failing that I will manually generate reports and exclude the undesired records from our solicitation. 

Thank you both for your continued attention.  I want you to know the project is going well and already the staff of our NGO are enjoying the flexibility of CiviCRM for record keeping and so on.   It's just this challenge of memberships that is troublesome.

Yes once our new site is published I will definitely post something in your forums.

And also I am stuck!     Can you help please?

I followed the instructions at http://wiki.civicrm.org/confluence/display/CRMDOC/Custom+Search+Components

Let me give you as much info as I can and maybe you can help me figure out why this is happening...I believe it is not even getting as far as the query, the page won't load at all.

Error when I load the page /civicrm/contact/search/custom?csid=7&reset=1
backTrace

/var/www/html/rn/sites/all/modules/civicrm/CRM/Core/Error.php, backtrace, 258
/var/www/html/rn/sites/all/modules/civicrm/CRM/Contact/Form/Search/Custom.php, fatal, 54
/var/www/html/rn/sites/all/modules/civicrm/CRM/Core/Form.php, preProcess, 300
/var/www/html/rn/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Display.php, buildForm, 98
/var/www/html/rn/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php, perform, 195
/var/www/html/rn/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php, handle, 95
/var/www/html/rn/sites/all/modules/civicrm/CRM/Core/Controller.php, handle, 223
/var/www/html/rn/sites/all/modules/civicrm/CRM/Core/Invoke.php, run, 540
/var/www/html/rn/sites/all/modules/civicrm/CRM/Core/Invoke.php, search, 435
/var/www/html/rn/sites/all/modules/civicrm/CRM/Core/Invoke.php, contact, 98
/var/www/html/rn/sites/all/modules/civicrm/drupal/civicrm.module, invoke, 321
, civicrm_invoke,
/var/www/html/rn/includes/menu.inc, call_user_func_array, 418
/var/www/html/rn/index.php, menu_execute_active_handler, 15

unrecoverable error
    Sorry. A non-recoverable error has occurred.

    Could not get details for custom search

    Return to home page.




line 38 from my MembershipStatus.php file: 
class CRM_Contact_Form_Search_Custom_Membership

Excerpt from the database where I inserted the class and file as value 7 in the `civicrm_option_value` table
| id  | option_group_id | label                                     | value | name
| 203 |              24 | CRM_Contact_Form_Search_Custom_Membership | 7     | CRM/Contact/Form/Search/Custom/MembershipStatus.php

There ya go, make any sense to you?  I'm lost...

Also, although I don't believe the query is the problem (as the page won't even load in the first place) here is the query I wish to run.  It's an interesting query because I don't need to accept user input at all.  The query is designed simply to find contacts who have a grace membership or a membership that has expired within 2 years while simultaneously NOT having a current membership.

When the contacts are returned, I would like to ultimately be able to print out a mailing list of these members that fit the above criteria.  This query serves my purposes:

SELECT count(*) as n, mem.contact_id as contact_id, mem.id as membership_id, con.display_name as name, stat.name as status

FROM
civicrm_membership mem
LEFT JOIN civicrm_contact con ON (mem.contact_id = con.id)
LEFT JOIN civicrm_membership_status stat ON (mem.status_id = stat.id)

WHERE contact_id NOT IN (SELECT mem.contact_id FROM civicrm_membership mem WHERE mem.status_id = 2)
AND mem.status_id IN (3,4) AND mem.end_date > DATE_SUB(NOW(), INTERVAL 2 YEAR)

GROUP BY contact_id HAVING n > 0

ORDER BY
name, status


Sure here you go... I was a little unsure of how to handle the "HAVING" clause.  Maybe that is the problem.  Or maybe something else.  Thank you!

Ok was a missing ) here is the reuploaded file.

$ php MembershipStatus.txt   >  no warnings, errors

Thanks.