This is the SQL query that I use to generate operations reports for Xero

ops-report.sql

SELECT 
    kiosk.name AS ContactName,
    '' AS 'EmailAddress',
    '' AS 'POAddressLine1',
    '' AS 'POAddressLine2',
    '' AS 'POAddressLine3',
    '' AS 'POAddressLine4',
    '' AS 'POCity',
    'Saintard' AS 'PORegion',
    '' AS 'POPostalCode',
    '' AS 'POCountry',
    CASE
        WHEN
            kiosk.name = 'Saintard'
        THEN
            CONCAT('K1',
                    DATE_FORMAT(receipt.created_date, '%y%m%d'))
        WHEN
            kiosk.name = 'Corail'
        THEN
            CONCAT('K2',
                    DATE_FORMAT(receipt.created_date, '%y%m%d'))
        WHEN
            kiosk.name = 'Cabaret'
        THEN
            CONCAT('K4',
                    DATE_FORMAT(receipt.created_date, '%y%m%d'))
        WHEN
            kiosk.name = 'Santo19'
        THEN
            CONCAT('K5',
                    DATE_FORMAT(receipt.created_date, '%y%m%d'))
        WHEN
            kiosk.name = 'Bois9'
        THEN
            CONCAT('K6',
                    DATE_FORMAT(receipt.created_date, '%y%m%d'))
        WHEN
            kiosk.name = 'Quartier Morin'
        THEN
            CONCAT('K7',
                    DATE_FORMAT(receipt.created_date, '%y%m%d'))
        WHEN
            kiosk.name = 'Limonade'
        THEN
            CONCAT('K8',
                    DATE_FORMAT(receipt.created_date, '%y%m%d'))
        WHEN
            kiosk.name = 'Ouanaminthe'
        THEN
            CONCAT('K9',
                    DATE_FORMAT(receipt.created_date, '%y%m%d'))
    END AS 'InvoiceNumber',
    '' AS 'Reference',
    DATE_FORMAT(receipt.created_date, '%c/%e/%y') AS InvoiceDate,
    DATE_FORMAT(receipt.created_date, '%c/%e/%y') AS DueDate,
    '' AS 'Total',
    receipt_line_item.sku AS 'InventoryItemCode',
    product.description AS 'Description',
    receipt_line_item.quantity AS 'Quantity',
    product.price_amount AS 'UnitAmount',
    '' AS 'Discount',
    CASE
        WHEN receipt_line_item.sku = 'OB5G' THEN 1208
        WHEN receipt_line_item.sku = 'MP' THEN 4010
        WHEN receipt_line_item.sku = 'PPP' THEN 4011
        WHEN receipt_line_item.sku = 'M5GALON' THEN 4020
        WHEN receipt_line_item.sku = 'R5GALON' THEN 4021
        WHEN receipt_line_item.sku = 'RSP' THEN 4025
        WHEN receipt_line_item.sku = 'DLM' THEN 4023
        WHEN receipt_line_item.sku = 'DLM4' THEN 4023
        WHEN receipt_line_item.sku = 'DLM5' THEN 4024
        WHEN receipt_line_item.sku = 'RPIYAY' THEN 4016
        WHEN receipt_line_item.sku = 'DTGP' THEN 4150
        WHEN receipt_line_item.sku = 'B1G' THEN 4045
        WHEN receipt_line_item.sku = 'B10L' THEN 4046
        WHEN receipt_line_item.sku = 'TMP' THEN 4151
        WHEN receipt_line_item.sku = 'FIN10' THEN 4144
        WHEN receipt_line_item.sku = 'P140' THEN 4016
    END AS 'AccountCode',
    'Tax on Sales' AS 'TaxType',
    '' AS 'TaxAmount',
    'Kiosk' AS 'TrackingName1',
    kiosk.name AS 'TrackingOption1',
    'Funding Source' AS 'TrackingName2',
    'Revenue' AS 'TrackingOption2'
FROM
    receipt
        INNER JOIN
    kiosk ON kiosk.id = receipt.kiosk_id
        INNER JOIN
    receipt_line_item ON receipt.id = receipt_line_item.receipt_id
        INNER JOIN
    customer_account ON receipt.customer_account_id = customer_account.id
        INNER JOIN
    product ON receipt_line_item.sku = product.sku
WHERE
    receipt.created_date BETWEEN '2018-01-01' AND '2018-02-01'
        AND receipt_line_item.sku IN ('MP' , 'PPP',
        'DLM',
        'DLM4',
        'DLM5',
        'RSP',
        'M5GALON',
        'R5GALON',
        'DTGP',
        'OB5G',
        'B1G',
        'B10L',
        'TMP',
        'FIN10',
        'P140')
ORDER BY kiosk.name , receipt.created_date;