receipts.sql

.mode json
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, 
        CASE ZRECEIPT.ZDATATYPE
            WHEN 1 THEN 'receipt'
            WHEN 2 THEN 'contact'
            WHEN 3 THEN 'activity'
            WHEN 4 THEN 'document'
            ELSE 'unknown'
        END as TYPE,
        DATETIME(ZRECEIPT.ZIMPORTDATE + 978307200, 'unixepoch') as IMPORTED,
        DATETIME(ZRECEIPT.ZDATE + 978307200, 'unixepoch') as DATE,
        case ZRECEIPT.ZDATATYPE 
            when 4 then ZRECEIPT.ZMERCHANT
            else NULL
        end as TITLE,
        case ZRECEIPT.ZDATATYPE 
            when 1 then ZRECEIPT.ZMERCHANT
            else COALESCE(ZCUSTOMRECEIPTITEM.ZSTRINGDATA, ZRECEIPT.ZMERCHANT)
        end as ORGANIZATION,
        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 ZCUSTOMRECEIPTITEM ON ZRECEIPT.Z_PK = ZCUSTOMRECEIPTITEM.ZRECEIPT
    LEFT JOIN ZSUBCATEGORY ON ZRECEIPT.ZSUBCATEGORY = ZSUBCATEGORY.Z_PK
ORDER BY ZRECEIPT.Z_PK;