Since I have yet to find a reward points / customer loyalty system for opencart, I figured I would try my hand at developing one myself. As I am new to opencart and have not fully gone through it, I thinks its best to share my workings with the community and hope for other users input throughout the development process. Since it is me doing the majority of this work, it will most likely end up as one hell of a hack job, but I have hopes that my works will provide some more experienced developers with everything they need to create a proper module.
The goals:
- Create method of inserting points changes into database.
- Create a master value in admin to configure the base rate of store credit to award per default currency unit (USD, euro, pound etc)
- Create fields in Admin > Edit Customer section to show credit balance, and add / remove credit.
- Create page in admin to view credit history for a customer.
- Create front end page for customer to view their current as well as their credit history.
Overview:
I reviewed what little code was available on the forums in this post: http://forum.opencart.com/viewtopic.php?f=24&t=10420 and noticed this user was setting up an individual credit value per product. In essence this would be nice to have, but it causes issues when a customer places an order with points + cash when you want to total up how much credit they should be awarded. I am open to solution suggestions for this problem, but I think for now I will just focus on doing a credit per order total. A future idea was to implement a toggle per product to not allow specific products to eligible for store credit awards (gift vouchers maybe?).
Database Table oc_customer_credit_pending:
Code: Select all
CREATE TABLE IF NOT EXISTS `oc_customer_credit_pending` (
`id` int(11) NOT NULL auto_increment,
`customer_id` int(11) NOT NULL,
`order_id` int(11) NOT NULL,
`value` decimal(15,2) NOT NULL default '0.00',
`comment` varchar(200) default NULL,
`date_added` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Code: Select all
CREATE TABLE IF NOT EXISTS `oc_customer_credit_history` (
`id` int(11) NOT NULL auto_increment,
`customer_id` int(11) NOT NULL,
`order_id` int(11) NOT NULL,
`value` decimal(15,2) NOT NULL default '0.00',
`comment` varchar(200) default NULL,
`date_added` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Assuming we have our location to drop our code while noting that I am not a great developer, I am thinking at this point I need to locate the already existing currency conversion functions and push our order total to it to get our total cash value spent that we want to use for credit awarding.
With this returned value, we will do an insert into the credit_pending table with our converted amount multiplied by our value set in admin, roughly like:
Code: Select all
$credit_to_award = currency_converter_function($order_total) * $credit_multiplier
$this->db->query("INSERT INTO oc_customer_credit_pending SET customer_id = '" . (int)$data['customer_id'] . "', order_id = '" . (int)$order_id . "', value = '" . (float)$credit_to_award . "', comment = 'Credit awarded for order " . (int)$order_id . "'");
Code: Select all
// Here we assume that $credit_spent is a negative number
$this->db->query("INSERT INTO oc_reward_points_pending SET customer_id = '" . (int)$data['customer_id'] . "', order_id = '" . (int)$order_id . "', value = '" . (float)$credit_spent . "', comment = 'Credit Deducted for order " . (int)$order_id . "'");
Also in this function, we need to deduct the amount of credit used from the customers_current_credit field which I forgot to mention we would need to add to the customers table. Since we are adding it to the pending table, the customer should no longer have access to this credit amount to avoid customers using the same credit for multiple orders. In the event an order gets cancelled, we will toss in a function on the update order function to allow for the credit to be applied back to the customer account.
Moving on:
I feel that the Completed order status is a great place to handle the final arrangements of credit. This makes sure that no customers are granted credit before the order is fully completed to make absolutely sure customers aren't able to use credit they shouldn't be using. I have seen a lot of systems that award credit once a payment completes, but feel this is not a great way of doing things.
While I haven't looked into the code much to find out exactly where to put all this, it should be rather clear what will happen:
Code: Select all
- Order status is set to Completed.
-> A Select statement is performed on the customer_credit_pending table which grabs all entries Where order_id = XXX
-> Negative balances are placed into customer_credit_history and this record is removed from customer_credit_pending
-> Positive balances are placed into customer_credit_history, it's applied to the customers_current_credit field in the customers table and finally this record is removed from the customer_credit_pending table.Code: Select all
- Order status is set to Cancelled
-> A Select statement is performed on the customer_credit_pending table which grabs all entries Where order_id = XXX
-> Negative balances are converted to positive and applied to the customer_current_credit field in the customers table
-> All entries in customers_credit_pending table related to this order_id are deleted and no history entry is necessary in my opinion.
