Press "Enter" to skip to content

Tag: e-commerce

ClickCartPro: Extracting Sales Figures

December 31, 2008
Comments closed

If you want to get an idea of the stock you’ve sold using the ClickCartPro UK e-commerce software, then you may find the following SQL query useful. Run in phpMySQL to be able to export the data into a spreadsheet.

SELECT SUM(itemquan) as itemssold, count(*) AS timesordered, itemname, itemnum, itemopts, SUM(itemquan)/COUNT(*) AS average FROM `gbu0_orderitems` WHERE FROM_UNIXTIME(epochorder) BETWEEN ‘2007-01-01’ and ‘2008-12-31’ GROUP BY itemnum,itemopts ORDER BY itemssold DESC

What does this mean?
Well, “SUM(itemquan) AS itemssold” grabs the number of items sold, “count(*) AS timesordered” grabs the number of times that item has actually been ordered (as an order could be for 2 or more items), “itemname, itemnum, itemopts” grabs the items name, item number and the options ordered (handy for sizes), “SUM(itemquan)/COUNT(*) AS average” works out the average number of items per order, “FROM `gbu0_orderitems`” says to look at the order table (ok, I’m not distinguishing between cancelled, pending and completed orders at the moment), “FROM_UNIXTIME(epochorder) BETWEEN ‘2007-01-01’ and ‘2008-12-31′” says to only include orders placed between the 1st of January 2007 and the 31st of December 2008 and “GROUP BY itemnum,itemopts ORDER BY itemssold DESC” means group the items for the counts by the item number and options and then order everything by the number of times sold in decending order.

e-Commerce: ClickCartPro making all product ids safe

December 8, 2008
Comments closed

Basically, in ClickCartPro product “identifers” can not have spaces, commas, full stops or brackets in them (basically only the letters A-z, numbers and underscores) and they have to be a maximum length (I’m not sure what the exact maximum is).

If you are maintaining an “established” store (i.e. one with products) where a number of products are showing up on the front end, but when you try to “Add to cart” they don’t appear in the cart then this is probably the problem. To “fix” it (this is more a work around), run the following UNREVERSABLE MySQL code (i.e. make sure you have a backup before hand):

create table temp_replacetest(id varchar(250),hash char(32));
INSERT INTO temp_replacetest (SELECT id,md5(id) from gbu0_prod);
update gbu0_prod,temp_replacetest SET gbu0_prod.xprod=replace(gbu0_prod.xprod,temp_replacetest.id,temp_replacetest.hash) where temp_replacetest.id IN (gbu0_prod.xprod);
drop table temp_replacetest;
update gbu0_prod set id=md5(id);
select id,xprod from gbu0_prod

This will set all product ids to be md5 hashes which, since they only consist of 32 numbers or the letters a-f, are “safe” to use as product identifiers.

e-Commerce: Changing the VAT Rate in ClickCartPro (UK Edition)

December 5, 2008
Comments closed

If you are using the UK edition of Kryptonic’s ClickCartPro ecommerce system (sold by Greenbarn Web) and you still haven’t updated the VAT rates in it to take into account the reduction in UK VAT on the 1st of December from 17.5% to 15%, then here’s how to do it.

To change the VAT rate in ClickCartPro from 17.5% to 15% (or vice versa), just login to the admin panel (normally via http://www.example.com/admin.php ) and select from the left hand menu “Commerce: Orders and Checkout” (it should be the third option down) and then “Manage EU Tax Rates”. You’ll probably see three rates “High Rate”, “Low Rate” and “Zero Rate”. If you select “Update” next to “High Rate”. In the “EU Tax Rate” box, you should see the current rate of VAT being shown and be able to adjust it to 15%. Click “Submit” and it should take immediate effect.

Book Review: php|architect’s Guide to E-commerce Programming with Magento

November 30, 2008
Comments closed

I’ve decided to switch the back end of my side-line e-commerce dance wear and leisure wear clothing site from ClickCartPro to Magento. Why? Well, Magento is cheaper (moot point as I’ve already brought the licence for ClickCartPro), Open Source (so’s ClickCartPro), is more flexible (so it seems), has more options and it’s another shopping cart system for me to learn and have experience with (plus it does seem to be growing in popularity quite a bit).

However, the switch has come with a price: it’s quite a complex bit of kit! So, since Waterstones had an offer on and I needed to buy some other books I purchased php|architect’s Guide To E-Commerce Programming with Magento from them (Waterstones’ price is £19.99 and Amazon’s is £18.99: both with free shipping. It’s also available as a PDF for $29.99 from the publisher’s site). Why did I buy this book? Well, it was the only Magento book Waterstones (or Amazon) had!

Continue ReadingBook Review: php|architect’s Guide to E-commerce Programming with Magento