NOTES.md

Notes

SQL to extract data from MSPL

It's unclear what this was for - but I copied it out of a forum - once I start hacking on the SQLite source we can use this.

WITH DOC_TAGS AS (
		SELECT tags.Z_14RECEIPTS1 as ID, ZTAG.ZNAME as NAME  
		FROM Z_14TAGS tags
			JOIN ZTAG ON Z_PK = tags.Z_18TAGS
	),
	GROUPED_DOC_TAGS AS (
		SELECT DOC_TAGS.ID, GROUP_CONCAT( DOC_TAGS.NAME, '<<[]>>'  ) as TAGS 
			FROM DOC_TAGS
			GROUP BY ID
	)
SELECT 	ZRECEIPT.Z_PK as ID, 
		DATETIME(ZRECEIPT.ZIMPORTDATE + 978307200, 'unixepoch') as IMPORTED,
		ZRECEIPT.ZMERCHANT as TITLE, 
		ZCATEGORY.ZNAME as CATEGORY, 
		ZSUBCATEGORY.ZNAME as SUBCATEGORY, 
		GROUPED_DOC_TAGS.TAGS, 
		ZRECEIPT.ZNOTES as NOTES,
		ZRECEIPT.ZPATH as PATH
FROM ZRECEIPT
	LEFT JOIN GROUPED_DOC_TAGS ON GROUPED_DOC_TAGS.ID = ZRECEIPT.Z_PK
	LEFT JOIN ZCATEGORY ON ZRECEIPT.ZCATEGORY = ZCATEGORY.Z_PK
	LEFT JOIN ZSUBCATEGORY ON ZRECEIPT.ZSUBCATEGORY = ZSUBCATEGORY.Z_PK