How can I keep track of sales count with Store, Matrix, & Playa?

Question

The Scenario

I'm using Store, Matrix, and Playa addons for an ecommerce site. I now need to add a Gift List section to the site. This is not the typical scenario as an admin user will create the list in the control panel (CP)—no front-end sign-up and users can't add products themselves.

Constraints

I'm already using Store on other ecommerce parts of the site as a regular shop setup so I need all orders to still feed into the Store Orders section and the Store template variables available on the front-end as currently.

All SKU's must match a printed catalogue and they must all be unique within the system.

The Problem

Each gift needs a Quantity Requested and Quantity Received value associated to it. At first thought you think you can use the Limit Stock option in the Store Product Details field but this would tie those values to each and every gift meaning that if it was requested at a later date by someone else with different quantities then it wouldn't be possible to enter it as the SKU would be a duplicate. The SKUs have to match those of a printed catalogue and in the system obviously they have to be unique.

So I need to disassociate Quantity Requested and Quantity Received from the product itself. If I were to create these as separate columns in a Matrix field I need a way to keep a running counter of products purchased, match them to the list, and increment the Quantity Received column in the Matrix field. The checkout is running on Store so I have to use Store's template variables and hooks to retrieve the details upon order completion and then somehow post them back to update the Quantity Received field in the EE CP.

The Setup (not in full or including non-important fields for brevity):

Channel: Gifts

Fields: Store Product Details

Channel: Gift Lists

Fields: Matrix

Within a matrix row I have a column for Gift (Playa field) to pull in a requested gift. Then a column for Quantity Requested and another for Quantity Received. Both of these fields are Number fields containing a simple integer. Looking like this.

The Questions

Is that setup the best way to handle things? If so, what is the best way to grab the values from a completed order run through Store and then update the appropriate entry in the Gift Lists channel? Would I be able to use Safecracker to post back and update values? Or is there a better way to do it in PHP? Recommendations?

Answer 1

This looks like the best way to set things up.

All you really need to do is update the "quantity received" column after a purchase is made. This will require writing an extension to update your matrix field.

The first thing you need to do is remember which gift list the customer is purchasing from. I assume you have a page to display all items in the gift list and an "add to cart" button. What I would do here is use a custom text modifier to store the matrix row ID of the product they purchased. You can submit this as a hidden field as part of the add to cart form, and it will get stored in the cart alongside any other modifiers (e.g. Size, Color). You can also edit your checkout templates with some conditional statements to hide this matrix row ID from displaying the checkout pages (since the customer doesn't need to know or care about it), but you will need to know it later to know which gift list purchase count to increment.

Then you will need to create an extension (don't use php in templates if you can avoid it, it can create security holes in EE). Start by following the general EE extension development guide here. You will want to use the store_order_complete_end hook which means your custom PHP code will be run every time an order is complete (paid for).

In your extension hook code, you will basically loop through the items in the order, and check for the text modifier you stored (which contains the matrix row ID). You can then easily +1 the "quantity received" column directly in your database (I'll leave it to you to decipher the matrix database schema).

It's a fairly involved process, but nothing particularly complicated. If you're not familiar with PHP and SQL it's probably worth hiring a developer who has worked with EE extensions before to help you out.