jhauraw · March 7, 2013 02:30
diff --git a/opencart-export-outright.sql b/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
  ;
	/*
	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
	;