Tax Time: Getting Sales and Shipping Data out of Ubercart

,

A client needed to know how much he had charged in sales tax in the state of California in 2011, the total for those orders, and the amount charged to the customer for shipping. This is because, as it turns out, shipping is not taxable by the state of California, but any amount over and above the actual amount paid to the shipper is taxable. So in other words, if UPS charges you $2 to ship the package, but you charged $4 for shipping, the second two dollars is taxable. This is a bit of problem in this case because the client uses a flat rate for shipping, which means in California, the cost of shipping is probably high on most orders, and therefore taxable.

You can pull this data from Ubercart simply enough but it requires a few subqueries to get all the data you need. This is because both the sales data and shipping are in the same table, namely uc_order_line_items. So you need to pull each line item from the table as a separate subquery.

There’s one other gotcha. Ubercart carries tax through to four decimal places in the database, so you need to round that to the nearest penny, as Ubercart does on checkout. This gives you a spreadsheet with all the values you need, minus the actual amount paid for shipping, which you’ll have to pull from Quickbooks or whereever.

It’s possible, also, that you’ll have to adjust for your server time if your server is in another time zone. In other words, you may need to adjust the timestamp to take that into account. And of course, Ubercart stores your order dates and times as Unix timestamps, so we have to take that into account when we do whatever conversions we need. Fortunately MySQL handles Unix timestamps with no problem.

When all is said and done, we end up with the following query:

SELECT FROM_UNIXTIME(o.created,"%Y-%m-%d") AS 'Date', o.order_id AS 'Order ID', 
    tax.sales_tax AS `Sales Tax`, shipping.shipping AS 'Shipping Charged', 
    o.order_total AS 'Order Total', o.billing_postal_code AS 'Zip Code', o.billing_city AS 'City', z.zone_name AS 'State'
FROM 
    (SELECT ROUND(amount,2) AS sales_tax, order_id FROM uc_order_line_items WHERE TYPE LIKE 'tax') AS tax,
    (SELECT amount AS shipping, order_id FROM uc_order_line_items WHERE TYPE LIKE 'shipping') AS shipping,
    uc_orders AS o, 
    uc_zones AS z
WHERE o.order_status LIKE 'completed'
    AND tax.order_id LIKE o.order_id AND shipping.order_id LIKE o.order_id
    AND o.created > UNIX_TIMESTAMP('2010-12-31 23:59:59') AND o.created < UNIX_TIMESTAMP('2012-01-01 00:00:00')
    AND z.zone_id LIKE o.billing_zone
    AND o.billing_zone LIKE '12'
ORDER BY o.billing_postal_code;

Notice that we are ordering by billing postal code. This is because in many ways, this is our most relevant piece of information. Many of the client’s customers order while on the road, so the billing address is the one that counts for tax purposes (the same as for a gift purchase or whatever). The postal code typically determines the tax rate, so it allows us to effectively sort by location. Once exported into an Excel spreadsheet, though, we can of course sort and hash however we want.

Here’s a sample of the final output in SQLYog (a Windows MySQL client):

sample output
Sample Output from SQLYog

Enjoy!


Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>