From 1dc429d19fe2ad2e939fc428ad07fdd4fac33c33 Mon Sep 17 00:00:00 2001
From: Ellen Hendricks <ellen.hendricks@sprydigital.com>
Date: Tue, 10 May 2016 16:00:29 -0500
Subject: [PATCH] CRM-17684 Expand Auto-Renew membership search to seach
 subscription status

----------------------------------------
* CRM-17684: Find Memberships with no auto-renew
  https://issues.civicrm.org/jira/browse/CRM-17684
---
 CRM/Member/BAO/Query.php                    | 57 ++++++++++++++++++---
 templates/CRM/Member/Form/Search.hlp        | 13 +++++
 templates/CRM/Member/Form/Search/Common.tpl |  7 ++-
 3 files changed, 68 insertions(+), 9 deletions(-)

diff --git a/CRM/Member/BAO/Query.php b/CRM/Member/BAO/Query.php
index b226fa8539..6fcb7e1228 100644
--- a/CRM/Member/BAO/Query.php
+++ b/CRM/Member/BAO/Query.php
@@ -262,8 +262,36 @@ class CRM_Member_BAO_Query {
         return;
 
       case 'member_auto_renew':
-        $op = "!=";
-        if ($value) {
+        $op = "=";
+        if ($value == 1) {
+          $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NULL";
+          $query->_qill[$grouping][] = ts("Membership is NOT Auto-Renew");
+        }
+        elseif ($value == 2) {
+          $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NOT NULL";
+          $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause(
+            "ccr.contribution_status_id",
+            $op,
+            array_search(
+              'In Progress',
+              CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name')
+            ),
+            "Integer");
+          $query->_qill[$grouping][] = ts("Membership is Auto-Renew and In Progress");
+        } 
+        elseif ($value == 3) {
+          $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NOT NULL";
+          $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause(
+            "ccr.contribution_status_id",
+            $op,
+            array_search(
+              'Failed',
+              CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name')
+            ),
+            "Integer");
+          $query->_qill[$grouping][] = ts("Membership is Auto-Renew and Failed");
+        } 
+        elseif ($value == 4) {
           $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NOT NULL";
           $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause(
             "ccr.contribution_status_id",
@@ -274,11 +302,12 @@ class CRM_Member_BAO_Query {
             ),
             "Integer"
           );
-          $query->_qill[$grouping][] = ts("Membership is Auto-Renew");
-        }
-        else {
-          $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NULL";
-          $query->_qill[$grouping][] = ts("Membership is NOT Auto-Renew");
+          $query->_qill[$grouping][] = ts("Membership is Auto-Renew and Cancelled");
+        } 
+        elseif ($value == 5) {
+          $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NOT NULL";
+          $query->_where[$grouping][] = " ccr.end_date IS NOT NULL";
+          $query->_qill[$grouping][] = ts("Membership is Auto-Renew and Ended");
         }
         $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1;
         return;
@@ -393,6 +422,7 @@ class CRM_Member_BAO_Query {
         'membership_recur_id' => 1,
         'member_campaign_id' => 1,
         'member_is_override' => 1,
+        'member_auto_renew' => '1',
       );
 
       if ($includeCustomFields) {
@@ -444,7 +474,18 @@ class CRM_Member_BAO_Query {
 
     $form->addYesNo('member_is_primary', ts('Primary Member?'), TRUE);
     $form->addYesNo('member_pay_later', ts('Pay Later?'), TRUE);
-    $form->addYesNo('member_auto_renew', ts('Auto-Renew?'), TRUE);
+
+    $form->add('select', 'member_auto_renew',
+      ts('Auto-Renew Subscription Status?'),
+      array(
+        '1' => ts('- None -'),
+        '2' => ts('In Progress'),
+        '3' => ts('Failed'),
+        '4' => ts('Cancelled'),
+        '5' => ts('Ended'),
+      )
+    );
+
     $form->addYesNo('member_test', ts('Membership is a Test?'), TRUE);
     $form->addYesNo('member_is_override', ts('Membership Status Is Override?'), TRUE);
 
diff --git a/templates/CRM/Member/Form/Search.hlp b/templates/CRM/Member/Form/Search.hlp
index 6f1c228e61..5dd6e9df51 100644
--- a/templates/CRM/Member/Form/Search.hlp
+++ b/templates/CRM/Member/Form/Search.hlp
@@ -43,3 +43,16 @@
     <li>{ts}Select neither to show all memberships{/ts}</li>
   </ul>
 {/htxt}
+{htxt id="id-member_auto_renew-title"}
+  {ts}Auto-Renew Subscription Status{/ts}
+{/htxt}
+{htxt id="id-member_auto_renew"}
+  <ul>
+    <li>{ts}Select "None" to display memberships that have never had an auto-renew subscription.{/ts}</li>
+    <li>{ts}Select "In Progress" to display memberships with current auto-renew subscriptions.{/ts}</li>
+    <li>{ts}Select "Failed" to display memberships with failed subscription payment.{/ts}</li>
+    <li>{ts}Select "Cancelled" to display memberships with cancelled auto-renew subscriptions.{/ts}</li>
+    <li>{ts}Select "Ended" to display memberships with an ended auto-renew subscription.{/ts}</li>
+  </ul>
+{/htxt}
+
diff --git a/templates/CRM/Member/Form/Search/Common.tpl b/templates/CRM/Member/Form/Search/Common.tpl
index 2a9b859b62..5694630185 100644
--- a/templates/CRM/Member/Form/Search/Common.tpl
+++ b/templates/CRM/Member/Form/Search/Common.tpl
@@ -50,7 +50,12 @@
     {$form.member_pay_later.label}&nbsp;{$form.member_pay_later.html}
     </p>
     <p>
-    {$form.member_auto_renew.label}&nbsp;{$form.member_auto_renew.html}
+      {if $form.member_auto_renew}
+          <label>{$form.member_auto_renew.label}</label>
+          {help id="id-member_auto_renew" file="CRM/Member/Form/Search.hlp"}
+          <br/>
+          {$form.member_auto_renew.html}
+        {/if}
     </p>
     <p>
     {$form.member_is_override.label}
-- 
2.25.1