Retail and reseller pricing for thousands of products via spreadsheet

The challenge

An Italian retailer of IT products and technical assistance sold to two kinds of customer: private buyers and resellers. Each needed to see a different price for the same product, and the catalogue held around 3,500 products.

Setting a second price on every product by hand in the store's admin was not realistic for a catalogue of that size. The client needed a way to manage both price lists in bulk, and the store's customised theme had to display the right price to the right customer everywhere it appeared.

What we did

• Customer-group pricing. We supplied our customer-group pricing extension for OpenCart, which lets each product carry a separate price for each customer group, such as private and reseller.
• Bulk spreadsheet export/import. We built a custom tool, added to the store's System menu, that exports group prices to an XLS spreadsheet and imports them back. The client can update thousands of reseller and private prices in a spreadsheet and load them in one step.
• Server preparation. We uploaded the tool to the client's server and listed the PHP extensions needed for spreadsheet handling (XMLWriter, zip, XML and GD).
• Theme compatibility fix. With the client's customised theme, reseller prices initially appeared only in the cart. We adjusted the pricing extension's modification file so reseller prices also show on product pages and in product listings, changing that single file and leaving the rest of the store untouched.
• Ongoing guidance. When the client returned later, we showed where the bulk tool lives in the admin and which files had been modified, to help with a planned store upgrade.

Tech stack

PHP, MySQL, OpenCart, vQmod, PHPExcel (XLS import/export)