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

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.
Reply


Messages In This Thread
sql is not compiled properly - by cperrot - 12-31-2014, 08:51 AM
RE: sql is not compiled properly - by jlp - 12-31-2014, 04:06 PM
RE: sql is not compiled properly - by cperrot - 12-31-2014, 11:30 PM
RE: sql is not compiled properly - by mwhitney - 01-07-2015, 12:28 PM
RE: sql is not compiled properly - by cperrot - 01-08-2015, 12:52 AM
RE: sql is not compiled properly - by cperrot - 01-08-2015, 12:53 AM
RE: sql is not compiled properly - by mwhitney - 01-08-2015, 09:36 AM
RE: sql is not compiled properly - by zepernick - 01-14-2015, 06:56 AM
RE: sql is not compiled properly - by CroNiX - 01-21-2015, 04:34 PM
RE: sql is not compiled properly - by cperrot - 04-18-2015, 12:19 AM
RE: sql is not compiled properly - by cperrot - 04-18-2015, 06:09 AM
RE: sql is not compiled properly - by cperrot - 04-18-2015, 06:56 AM
RE: sql is not compiled properly - by cperrot - 11-02-2016, 11:39 AM



Theme © iAndrew 2016 - Forum software by © MyBB