
SELECT
i.id AS `Invoice ID`,
CONCAT(d.firstname,' ',d.lastname) AS `Client`,
hb_currency(i.total) AS `Invoice Subtotal`,
hb_currency(i.credit) AS `Invoice Credit`,
hb_currency(i.tax) AS `Invoice Tax L1`,
hb_currency(i.tax2) AS `Invoice Tax L2`,
hb_currency(i.total) AS `Invoice Total`,
hb_date(i.datepaid) AS `Invoice Paid Date`,
hb_date(i.date) AS `Invoice Date`,
m.modname AS `Gateway Name`,
t.trans_id AS `Related transaction`

FROM hb_invoices i
    JOIN hb_client_details d ON (i.client_id=d.id)
    JOIN hb_client_access ca ON (i.client_id=ca.id)
    LEFT JOIN hb_modules_configuration m ON (m.id=i.payment_module)
    LEFT JOIN hb_transactions t ON (t.invoice_id=i.id)
WHERE
    i.id NOT IN (
        SELECT invoice_id
        FROM hb_invoice_items
        WHERE `type` = 'Invoice'
    )
    AND i.status IN ('Paid')
    AND i.datepaid>= :date_bottom
    AND i.datepaid<= :date_top

ORDER BY
	i.id ASC