Import Sales into Outright from OpenCart Shopping Cart. Breaks each Sale into line item parts such as: Sub-Total, Shipping, Tax, Coupon and Credit, so you can see detailed Reports in Outright.

opencart-export-outright.sql

/*
Description: Import Sales into Outright from OpenCart Shopping Cart.
  Breaks each Sale into line item parts such as: Sub-Total, Shipping,
  Tax, Coupon and Credit, so you can see detailed Reports in Outright.
Author: Jhaura Wachsman
URL: http://JhauraWachsman.com
Copyright 2013 Jhaura Wachsman

Usage:
  1) Create categories 'Sales Shipping', 'Sales Tax', 'Sales Coupon'
    and 'Sales Credit' in Outright. If you don't like these names,
    you can rename them in the query below and then create the EXACT
    same category NAMES in Outright.
  2) Modify date range in SQL to return the orders you want.
  3) Export result set as CSV (Not CSV for Excel). Be sure to select:
    "Put columns names in the first row".
  4) Import the .csv file into Outright as Income.

Example result:
Date      Payee         Description           Category  Item ID  Amount
01/01/13  Fname  Lname  eStore Order: 100000  Sales     100000   100.00
*/

SELECT
  DATE_FORMAT(oh.date_added, '%c/%e/%y') AS `Date`,
  CONCAT_WS(' ',
    CONCAT(UPPER(LEFT(o.firstname, 1)), LOWER(SUBSTRING(o.firstname, 2))),
    CONCAT(UPPER(LEFT(o.lastname, 1)), LOWER(SUBSTRING(o.lastname, 2)))
  ) AS `Payee`,

  CONCAT_WS(' ', 'eStore Order:', o.order_id) AS `Description`,

  CASE ot.code
    WHEN 'shipping' THEN 'Sales Shipping'
    WHEN 'tax' THEN 'Sales Tax'
    WHEN 'coupon' THEN 'Sales Coupon'
    WHEN 'credit' THEN 'Sales Credit'
    WHEN 'sub_total' THEN 'Sales'
    ELSE 'Uncategorized'
  END AS `Category`,
  o.order_id AS `Item ID`,
  ot.value AS `Amount`
FROM `order_total` ot
 LEFT JOIN
  (
    `order` o,
    `order_history` oh
  )
  ON
  (
    o.order_id = ot.order_id
    AND oh.order_id = ot.order_id
  )
 WHERE
  oh.order_status_id = 3
  AND ot.value != 0
  AND ot.code != 'total'
  AND oh.date_added BETWEEN '2013-01-01 00:00:00' AND '2013-12-31 23:59:59'
  ORDER BY ot.order_id ASC
  ;