following sql is not properly compiled.
PHP Code:
$itemsPfx = $this->db->dbprefix('invoice_items');
$invPfx = $this->db->dbprefix('invoices');
$this->db->select('name, ' . $this->db->dbprefix('invoices') . '.currency_symbol');
$this->db->select_sum('amount * quantity', 'amount', FALSE);
$this->db->select('SUM( ROUND((' . $itemsPfx . '.amount*' . $invPfx . '.tax1_rate/100 * ' . $itemsPfx . '.quantity) / 0.05 , 0) * 0.05) as tax1_collected', FALSE);
$this->db->select('SUM( ROUND((' . $itemsPfx . '.amount*' . $invPfx . '.tax2_rate/100 * ' . $itemsPfx . '.quantity) / 0.05 , 0) * 0.05) as tax2_collected', FALSE);
$this->db->select('SUM(ROUND((' . $itemsPfx . '.amount * ' .$invPfx . '.accounting_invoice_exchange_rate * ' . $itemsPfx . '.quantity + ((' . $itemsPfx . '.amount * ' . $invPfx . '.accounting_invoice_exchange_rate) *' . $itemsPfx . '.quantity * (' . $invPfx . '.tax1_rate/100 + ' . $invPfx . '.tax2_rate/100) * ' . $itemsPfx . '.taxable)) / 0.05, 0 ) * 0.05 ) AS accounting_total_with_tax', FALSE);
$this->db->join('invoices', 'invoices.client_id = clients.id');
$this->db->join('invoice_items', 'invoices.id = invoice_items.invoice_id');
$this->db->where('dateIssued >= "' . $start_date . '" and dateIssued <= "' . $end_date . '"');
$this->db->order_by($invPfx.'.currency_type,' . 'clients.name');
$this->db->group_by('name');
return $this->db->get('clients');
resulting in following sql
SELECT `name`, `bamboo_invoices`.`currency_symbol`, SUM(`amount` * quantity) AS amount, SUM( ROUND((bamboo_invoice_items.amount*bamboo_invoices.tax1_rate/100 * bamboo_invoice_items.quantity) / 0.05, 0) * 0.05) as tax1_collected, SUM( ROUND((bamboo_invoice_items.amount*bamboo_invoices.tax2_rate/100 * bamboo_invoice_items.quantity) / 0.05, 0) * 0.05) as tax2_collected, SUM(ROUND((bamboo_invoice_items.amount * bamboo_invoices.accounting_invoice_exchange_rate * bamboo_invoice_items.quantity + ((bamboo_invoice_items.amount * bamboo_invoices.accounting_invoice_exchange_rate) *bamboo_invoice_items.quantity * (bamboo_invoices.tax1_rate/100 + bamboo_invoices.tax2_rate/100) * bamboo_invoice_items.taxable)) / 0.05,
`0` ) * 0.05 ) AS accounting_total_with_tax FROM (`bamboo_clients`) JOIN `bamboo_invoices` ON `bamboo_invoices`.`client_id` = `bamboo_clients`.`id` JOIN `bamboo_invoice_items` ON `bamboo_invoices`.`id` = `bamboo_invoice_items`.`invoice_id` WHERE `dateIssued` >= "2014-12-31" and dateIssued <= "2014-12-31" GROUP BY `name` ORDER BY `bamboo_invoices`.`currency_type`, `bamboo_clients`.`name`
The , 0 in the round function gets some quoted.