Welcome Guest, Not a member yet? Register   Sign In
sql is not compiled properly
#1

Hi

I reported the same issue already in CI 2.x see http://forum.codeigniter.com/thread-646.html

The problem persists in CI 3.x however slightly differently instead of escaping with an ` the table prefix is prepended.

SELECT SUM( ROUND((bamboo_invoice_items.quantity * bamboo_invoice_items.amount) * bamboo_invoices.accounting_invoice_exchange_rate, 2)) AS accounting_total_without_tax, SUM( ROUND((bamboo_invoice_items.amount*bamboo_invoices.tax1_rate/100 * bamboo_invoice_items.quantity) / 0.01, bamboo_0) * 0.01) as tax1_collected, SUM( ROUND((bamboo_invoice_items.amount*bamboo_invoices.tax2_rate/100 * bamboo_invoice_items.quantity) / 0.01, bamboo_0) * 0.01) as tax2_collected, SUM(ROUND(((bamboo_invoice_items.amount*bamboo_invoice_items.quantity)*bamboo_invoices.tax1_rate/100 * bamboo_invoices.accounting_invoice_exchange_rate) / 0.01, bamboo_0) * 0.01) AS accounting_tax1_collected, SUM(ROUND(((bamboo_invoice_items.amount*bamboo_invoice_items.quantity)*bamboo_invoices.tax2_rate/100 * bamboo_invoices.accounting_invoice_exchange_rate) / 0.01, bamboo_0) * 0.01) AS accounting_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.01, bamboo_0 ) * 0.01 ) AS accounting_total_with_tax, SUM(`amount *` `quantity`) AS `amount` 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` >= '2015-01-01' AND `dateIssued` <= '2015-01-31'

I hope this issue is resolved soon.

Regards

Cedric
#2

this is another one where Round is not properly compile either

SELECT `bamboo_invoices`.*, `bamboo_clients`.`name`, `bamboo_clients`.`address1`, `bamboo_clients`.`address2`, `bamboo_clients`.`city`, `bamboo_clients`.`country`, `bamboo_clients`.`province`, `bamboo_clients`.`website`, `bamboo_clients`.`postal_code`, `bamboo_clients`.`tax_code`, (SELECT SUM(bamboo_invoice_payments.amount_paid) FROM bamboo_invoice_payments WHERE bamboo_invoice_payments.invoice_id=162) AS amount_paid, TO_DAYS(bamboo_invoices.dateIssued) - TO_DAYS(curdate()) AS daysOverdue, (SELECT SUM(bamboo_invoice_items.amount * bamboo_invoice_items.quantity) FROM bamboo_invoice_items WHERE bamboo_invoice_items.invoice_id=162) AS total_notax, (SELECT SUM((bamboo_invoice_items.amount * bamboo_invoices.accounting_invoice_exchange_rate) * bamboo_invoice_items.quantity) FROM bamboo_invoice_items WHERE bamboo_invoice_items.invoice_id=162) AS accounting_total_notax, (SELECT SUM(bamboo_invoice_items.amount * bamboo_invoice_items.quantity * (bamboo_invoices.tax1_rate/100 * bamboo_invoice_items.taxable)) FROM bamboo_invoice_items WHERE bamboo_invoice_items.invoice_id=162) AS total_tax1, (SELECT SUM(bamboo_invoice_items.amount * bamboo_invoice_items.quantity * (bamboo_invoices.tax2_rate/100 * bamboo_invoice_items.taxable)) FROM bamboo_invoice_items WHERE bamboo_invoice_items.invoice_id=162) AS total_tax2, (SELECT SUM(bamboo_invoice_items.amount * bamboo_invoice_items.quantity + ROUND((bamboo_invoice_items.amount * bamboo_invoice_items.quantity * (bamboo_invoices.tax1_rate/100 + bamboo_invoices.tax2_rate/100) * bamboo_invoice_items.taxable), bamboo_2)) FROM bamboo_invoice_items WHERE bamboo_invoice_items.invoice_id=162) AS total_with_tax, (SELECT SUM(bamboo_invoice_items.amount * bamboo_invoice_items.quantity + ROUND(((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), bamboo_2)) FROM bamboo_invoice_items WHERE bamboo_invoice_items.invoice_id=162) AS accounting_total_with_tax FROM `bamboo_invoices` JOIN `bamboo_clients` ON `bamboo_invoices`.`client_id` = `bamboo_clients`.`id` LEFT JOIN `bamboo_invoice_items` ON `bamboo_invoices`.`id` = `bamboo_invoice_items`.`invoice_id` LEFT JOIN `bamboo_invoice_payments` ON `bamboo_invoices`.`id` = `bamboo_invoice_payments`.`invoice_id` WHERE `bamboo_invoices`.`id` = '162' GROUP BY `bamboo_invoices`.`id`
#3

You do not need to post twice.
#4

http://forum.codeigniter.com/thread-646-...ml#pid3402





Theme © iAndrew 2016 - Forum software by © MyBB