subscription system

CREATE VIEW sku_group AS
SELECT substring_index(substring_index(customer_group,-1),1) AS `sku`,
       customer_group.customer_group_id AS `customer_group_id`
FROM   customer_group;

CREATE VIEW sku_customer AS
SELECT order.order_id,
		order.customer_id,
		product.sku
FROM   order
       LEFT JOIN order_product ON order.order_id = order_product.order_id
       LEFT JOIN product ON order_product.product_id = product.product_id
	   ;

DELIMITER $$
DROP TRIGGER if EXISTS membership_purchase $$

CREATE TRIGGER membership_purchase 
	AFTER INSERT ON order_history
	FOR EACH ROW
BEGIN
	DECLARE end_data, c_customer_id, c_customer_group_id INT;
	DECLARE c_sku VARCHAR(64);
	DECLARE curs_prod_sku CURSOR FOR SELECT customer_id, sku_group.customer_group_id, order_sku_customer.sku FROM order_sku_customer LEFT JOIN sku_group ON order_sku_customer.sku = sku_group.sku WHERE order_sku_customer.order_id = NEW.order_id;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_data=1;

	if NEW.order_status_id=5 THEN
		OPEN curs_prod_sku;
		cursor_loop: LOOP
			FETCH curs_prod_sku INTO c_customer_id, c_customer_group_id, c_sku;
			if end_data = 1 THEN
				LEAVE cursor_loop;
			END if;
			if CHAR_LENGTH(c_sku) > 1 THEN /*SKUs already checked as matching a membership level*/
				UPDATE customer SET customer_group_id = c_customer_group_id WHERE customer.customer_id = c_customer_id;
			END if;
		END LOOP cursor_loop;
		CLOSE curs_prod_sku;	
	END if;
	SET end_data=0;
END $$

DELIMITER ;

Blue Membership (BLU2010)

BLU2010

sjordan wrote:@Qphoria

I have visited your site and like the idea of your Paypal Recurring payment module.

Let's say for the sake of illustration I use WordPress to write an online financial blog called Financial Times. I want to charge a recurring monthly fee to allow access.

So I assume I would have a sales page of some kind with a "Subscribe Now" button. Clicking the button drops the user into the purchase process. (Correct me if I have this wrong.)

Once a customer purchases a subscription, how does Open Cart and/or your module manage access to Financial Times so as to only allow current paid subscribers to view content?

Thank you in advance.

Qphoria wrote:You setup the price and subscription period for each product in the admin area.
The customer subscribes to the item directly from the product page (no add to cart, subscribe now instead)
They are taken through the paypal login steps and shown the breakdown
Then they return to your store's success page.

Now the paypal IPN process manages the product status. When the subscription stops due to expiration or cancelation, paypal ipn will update the order from the active state to the unactive state. You will also be notified by email. From there you can choose to delete the downloads or whatever from the customer's account

Xsecrets wrote:

sjordan wrote:@Qphoria

I have visited your site and like the idea of your Paypal Recurring payment module.

Let's say for the sake of illustration I use WordPress to write an online financial blog called Financial Times. I want to charge a recurring monthly fee to allow access.

So I assume I would have a sales page of some kind with a "Subscribe Now" button. Clicking the button drops the user into the purchase process. (Correct me if I have this wrong.)

Once a customer purchases a subscription, how does Open Cart and/or your module manage access to Financial Times so as to only allow current paid subscribers to view content?

Thank you in advance.

If that is your situation I would say that you are looking at the wrong solution. There are plugins for wp that can manage subscriptions and the access to content that those subscriptions allow. If you do something like this with opencart then you would still have alot of coding to do to allow/restrict the content. I love opencart, but it's just not the right tool for what you are looking to do.

sjordan wrote: @Xsecrets
Thank you for your honest answer. So in short OpenCart (at least out of the box) is designed for a sales process that involves manual intervention on the part of the seller; correct? For example, the seller receives an email (or logs in to OpenCart admin panel), is alerted of a sales, and then does something with that sale such as ship a product.

OpenCart is not really geared for automatic sales processes such as activating/deactivating subscriptions to premium content (eg. blog posts), activating/deactivating access to downloads (eg. ebooks), or managing sales of future service (eg. when someone purchases a banner ad space, they are not necessarily obligated to such the ad straight away; they might post it tonight, tomorrow, or next week. what is important is that they have purchased to right to place the ad).

Xsecrets wrote: It works just fine for things like ebooks because you upload those into the opencart system itself and once the person purchases it they have a download page within opencart where they can download the file they purchased. Where it does not work is for triggering outside things like activating and deactivating subscriptions in a cms or blog which is what I understand you are wanting to do.

that's not to say you can't make it do triggers, but it'll just be much more work than simply using a plugin for your cms/blog that is designed to do that very thing.