SELECT
i.id AS `Invoice ID`,
CONCAT(d.firstname,' ',d.lastname) AS `Client`,
hb_currency(i.total,i.currency_id,i.rate) AS `Invoice Total`,
i.status AS `Invoice Status`,
hb_date(i.date) AS `Invoice Date`,
hb_currency(i.credit,i.currency_id,i.rate) AS `Invoice Credit`,
m.modname AS `Gateway Name`,
taxrate as `Tax1 rate`,
taxrate2 as `Tax2 rate`,
tax as `Tax1 Amount`,
tax2 as `Tax2 Amount`,
i.rate AS `Exchange Rate`,
hb_date(i.duedate) AS `Invoice Due Date`,
hb_date(i.datepaid) AS `Invoice Paid Date`,
t.trans_id AS `Related transaction`,
ca.email AS `Client Email`,
d.phonenumber AS `Client Phone`,
d.companyname  AS `Client Company`,
d.city AS `Client City`,
d.country AS `Client Country`,
d.address1 AS `Client Address`,
d.postcode AS `Client ZIP`

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_currencies c ON (c.id=i.currency_id)
    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 NOT IN ('Draft','Recurring')
    AND i.`date`>= :date_bottom
    AND i.`date`<= :date_top

ORDER BY
	i.id ASC