How To Set up Recurring Billing (Membership) With Business Rule Combined With Customer Group Feature

In this helpnote, we will show you an example on how to setup recurring billing (e.g. membership) with specific business rule and using customer group feature

Let's say we will create a product name called "3 Month Membership" with description "Sign up for our 3 month membership plan and receive fantastic discounts!" .
This product will have a price $99, Free shipping "Yes" and have recurring billing setup as below :

The above means VPASP system will send the customer a bill for $99 every month.
There will be 3 bills in total. The first bill will be sent a month from the original order.

Let's say we have a business rule here for example, once they pay the subscription/membership, they should be able order other products and check out in the normal way and the price will be zero.

To apply such business rule, you will need to use customer group feature and insert a custom SQL query.
Go to administration menu : Customers > Customer Groups

You should see an existing record type "dynamic", please edit the record and make sure the following fields are filled in :

Product Price Field : price2
SQL Query :

For Access DB :

select distinct c.contactid from customers c,orders o, oitems oi Where c.contactid = o.ocustomerid and o.orderid = oi.orderid and o.odate <= now() and o.odate >= DATEADD('m', '-3',now()) and oi.catalogid = 25 and o.oprocessed = true and o.ocardtype in ('Visa','Mastercard','Cash')

For SQL Server DB :

select distinct c.contactid from customers c,orders o, oitems oi Where c.contactid = o.ocustomerid and o.orderid = oi.orderid and o.odate <= GETDATE() and o.odate >= DATEADD(month,-3,GETDATE()) and oi.catalogid = 25 and o.oprocessed = 1 and o.ocardtype in ('Visa','Mastercard','American Express','PayPal','Check')

For MYSQL DB :

select distinct c.contactid FROM customers c,orders o, oitems oi Where c.contactid = o.ocustomerid and o.orderid = oi.orderid and o.odate <= CURDATE() AND o.odate >= DATE_ADD(CURDATE(),INTERVAL -3 MONTH) and oi.catalogid = 25 AND o.oprocessed = 1 and o.ocardtype in ('Visa','Mastercard','American Express','PayPal','Check')

Notes For the SQL query above:
- Change the 25 with the catalogid of the membership product.

- o.oprocessed = True in Access db or o.oprocessed = 1 in SQL/MYSQL would mean only for orders that have been processed.

- With the use of price2 field of Products table, that means you should go to edit the other products and set the price2 field value to 0.
In this example, let's say we have other product with cname "Membership Digital Magazine" and cdescription " Free only for subscribed members. $20 for non member."
This product has price $20, if you want the subcribers to have $0, then please set price 2 with 0.



- The -3 in the SQL query means VPASP will auto search any orders that have been made in the past 3 months. You can change this to your liking e.g. to past 6 months etc..
To change to past 6 months, then just change -3 to -6 in the sql function above.

- Change then 'Visa','Mastercard','Cash' with the value of config "xendofordervalidpayments" and adding a ' ' into each value inside.
For example, if you have config "xendofordervalidpayments" with existing value Visa,Mastercard,American Express,PayPal,Check then to put into the SQL above, you should change it to eg. : 'Visa','Mastercard','American Express','PayPal','Check'.



After you have filled in the required fields, click "Update record" and then click "Back To Groups".

In the Action column, you can click refresh icon to see if you have any of membership orders in the past 3 months.



After you have applied this, the customer who purchased the membership in the past 3 months, when they logged in again to your front store, they should see price $0 for any of the products that you have set to have price2 field value 0.

---