export.sql

SELECT 
    `item_collections`.id AS OrderID,
    `item_collections`.total AS OrderTotal,
    CONVERT_TZ(`item_collections`.despatched_at,
            '+00:00',
            '+10:00') AS DespatchTime,
    `item_collections`.status AS OrderStatus,
    p.paid AS Paid,
    p.received AS Received,
    p.finalised AS PaidAt,
    `payment_methods`.name as 'Transaction',
    `countries`.name AS Country
FROM
    `item_collections`
        INNER JOIN
    (SELECT 
        item_collection_id,
            SUM(`payments`.amount_paid) AS Paid,
            SUM(`payments`.amount_received) AS Received,
            MAX(CONVERT_TZ(finalised_at, '+00:00', '+10:00')) AS finalised
    FROM
        `payments`
    GROUP By item_collection_id
    ORDER BY finalised ASC) AS p ON p.item_collection_id = item_collections.id
        INNER JOIN
    `addresses` ON addresses.user_id = item_collections.user_id
        INNER join
    `payment_methods` ON payment_methods.id = item_collections.payment_method_id
        INNER join
    `countries` ON countries.id = addresses.country_id
WHERE
    CONVERT_TZ(`item_collections`.`despatched_at`,
            '+00:00',
            '+10:00') > '2015-06-30 23:59:59'
        AND finalised < '2015-06-30 23:59:59'
GROUP BY `item_collections`.id
ORDER BY country ASC;