<?php
if (!defined('BASEPATH')) exit('No direct script access allowed');

/*
 * InvoicePlane
 *
 * @author		InvoicePlane Developers & Contributors
 * @copyright	Copyright (c) 2012 - 2017 InvoicePlane.com
 * @license		https://invoiceplane.com/license.txt
 * @link		https://invoiceplane.com
 */

/**
 * Class Mdl_Reports
 */
class Mdl_Reports extends CI_Model
{
    /**
     * @param null $from_date
     * @param null $to_date
     * @return mixed
     */
    public function sales_by_client($from_date = null, $to_date = null,$a = null, $b = null, $c = null, $client = null)
    {
        $this->db->select('client_id, client_name, client_surname, CONCAT(client_name," ", client_surname) AS client_namesurname');
		
		if ($from_date and $to_date and $client) {

            $from_date = date_to_mysql($from_date);
            $to_date = date_to_mysql($to_date);

            $this->db->select("
            (
                SELECT COUNT(*) FROM ip_invoices
                    WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5 
                        AND invoice_date_created >= " . $this->db->escape($from_date) . "
                        AND invoice_date_created <= " . $this->db->escape($to_date) . "
            ) AS invoice_count");
			 $this->db->select("
            (
                select sum(item_quantity) from ip_invoice_items it
                
                 WHERE it.invoice_id IN
                        (
                        SELECT invoice_id FROM ip_invoices
                            WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
                                AND invoice_date_created >= " . $this->db->escape($from_date) . "
                                AND invoice_date_created <= " . $this->db->escape($to_date) . "
                    )
						
            ) AS quantity");

            $this->db->select("
            (
                SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts
                    WHERE ip_invoice_amounts.invoice_id IN
                    (
                        SELECT invoice_id FROM ip_invoices
                            WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
                                AND invoice_date_created >= " . $this->db->escape($from_date) . "
                                AND invoice_date_created <= " . $this->db->escape($to_date) . "
                    )
            ) AS sales");

            $this->db->select("
            (
                SELECT SUM(invoice_total) FROM ip_invoice_amounts
                    WHERE ip_invoice_amounts.invoice_id IN
                    (
                        SELECT invoice_id FROM ip_invoices
                            WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
                                AND invoice_date_created >= " . $this->db->escape($from_date) . "
                                AND invoice_date_created <= " . $this->db->escape($to_date) . "
                    )
            ) AS sales_with_tax");

            $this->db->where('
                client_id IN
                (
                    SELECT client_id FROM ip_invoices
                        WHERE invoice_date_created >=' . $this->db->escape($from_date) . '
                            AND invoice_date_created <= ' . $this->db->escape($to_date) . ' and client_name = '.$this->db->escape($client).' and invoice_status_id != 1 and invoice_status_id != 5
                )');

        }

        else if ($from_date and $to_date) {

            $from_date = date_to_mysql($from_date);
            $to_date = date_to_mysql($to_date);

            $this->db->select("
            (
                SELECT COUNT(*) FROM ip_invoices
                    WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
                        AND invoice_date_created >= " . $this->db->escape($from_date) . "
                        AND invoice_date_created <= " . $this->db->escape($to_date) . "
            ) AS invoice_count");
			 $this->db->select("
            (
                select sum(item_quantity) from ip_invoice_items it
                
                 WHERE it.invoice_id IN
                        (
                        SELECT invoice_id FROM ip_invoices
                            WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
                                AND invoice_date_created >= " . $this->db->escape($from_date) . "
                                AND invoice_date_created <= " . $this->db->escape($to_date) . "
                    )
						
            ) AS quantity");

            $this->db->select("
            (
                SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts
                    WHERE ip_invoice_amounts.invoice_id IN
                    (
                        SELECT invoice_id FROM ip_invoices
                            WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
                                AND invoice_date_created >= " . $this->db->escape($from_date) . "
                                AND invoice_date_created <= " . $this->db->escape($to_date) . "
                    )
            ) AS sales");

            $this->db->select("
            (
                SELECT SUM(invoice_total) FROM ip_invoice_amounts
                    WHERE ip_invoice_amounts.invoice_id IN
                    (
                        SELECT invoice_id FROM ip_invoices
                            WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
                                AND invoice_date_created >= " . $this->db->escape($from_date) . "
                                AND invoice_date_created <= " . $this->db->escape($to_date) . "
                    )
            ) AS sales_with_tax");

            $this->db->where('
                client_id IN
                (
                    SELECT client_id FROM ip_invoices 
                        WHERE invoice_date_created >=' . $this->db->escape($from_date) . '
                            AND invoice_date_created <= ' . $this->db->escape($to_date) . ' and invoice_status_id != 1 and invoice_status_id != 5
                )');

        }  else if ($a and $b and $c and $client){
			
           
           $this->db->select("
            (
                SELECT COUNT(*) FROM ip_invoices
                    WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
                        AND  
							case
							when (month(invoice_date_created) >=4) then
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR(        invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3  )
							else 
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3  )
							end	
							AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
            ) AS invoice_count");
			  $this->db->select("
            (
                select sum(item_quantity) from ip_invoice_items it
                
                 WHERE it.invoice_id IN
                        (
                        SELECT invoice_id FROM ip_invoices
                            WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
							AND case
							when (month(invoice_date_created) >=4) then
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR(        invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3  )
							else 
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3  )
							end	
							AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
							
                            
                    )
						
            ) AS quantity");


            $this->db->select("
            (
                SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts 
                    WHERE ip_invoice_amounts.invoice_id IN
                    (
                        SELECT invoice_id FROM ip_invoices
                            WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
							AND case
							when (month(invoice_date_created) >=4) then
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR(        invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3  )
							else 
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3  )
							end	
							AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
							
                    )
            ) AS sales");

            $this->db->select("
            (
                SELECT SUM(invoice_total) FROM ip_invoice_amounts 
                    WHERE ip_invoice_amounts.invoice_id IN
                    (
                        SELECT invoice_id FROM ip_invoices
                            WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
							AND case
							when (month(invoice_date_created) >=4) then
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR(        invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3  )
							else 
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3  )
							end	
							AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
							
                    ) 
            ) AS sales_with_tax");
            
			$this->db->where('
                client_id IN
                (
                    SELECT client_id FROM ip_invoices
					WHERE  case
							when (month(invoice_date_created) >=4) then
								 (YEAR(invoice_date_created) = ' . $this->db->escape($a) . ' and MONTH(invoice_date_created) >= 4 ) or ( YEAR(        invoice_date_created) = ' . $this->db->escape($b) . '+1 and MONTH(invoice_date_created) <= 3  )
							else 
								 (YEAR(invoice_date_created) = ' . $this->db->escape($a) . '-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = ' . $this->db->escape($b) . ' and MONTH(invoice_date_created) <= 3  )
							end	
							AND monthname(invoice_date_created) = ' . $this->db->escape($c) . ' and client_name = '.$this->db->escape($client).' and invoice_status_id != 1 and invoice_status_id != 5
                        
                )');
           
                            
        }
		else if ($client and $c){
			
           
           $this->db->select("
            (
                SELECT COUNT(*) FROM ip_invoices
                    WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
                        AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
							
            ) AS invoice_count");
			  $this->db->select("
            (
                select sum(item_quantity) from ip_invoice_items it
                
                 WHERE it.invoice_id IN
                        (
                        SELECT invoice_id FROM ip_invoices
                            WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
							AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
							
							
                    )
						
            ) AS quantity");


            $this->db->select("
            (
                SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts 
                    WHERE ip_invoice_amounts.invoice_id IN
                    (
                        SELECT invoice_id FROM ip_invoices
                            WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
							AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
							
							
                    )
            ) AS sales");

            $this->db->select("
            (
                SELECT SUM(invoice_total) FROM ip_invoice_amounts 
                    WHERE ip_invoice_amounts.invoice_id IN
                    (
                        SELECT invoice_id FROM ip_invoices
                            WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
							AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
							
							
                    ) 
            ) AS sales_with_tax");
            
			$this->db->where('
                client_id IN
                (
                    SELECT client_id FROM ip_invoices
					WHERE  client_name = '.$this->db->escape($client).' and invoice_status_id != 1 and invoice_status_id != 5
					AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
							
                        
                )');
           
                            
        }
		else if ($a and $b and $client){
			
           
           $this->db->select("
            (
                SELECT COUNT(*) FROM ip_invoices
                    WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
                        AND 
							case
							when (month(invoice_date_created) >=4) then
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR(        invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3  )
							else 
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3  )
							end	
							
            ) AS invoice_count");
			  $this->db->select("
            (
                select sum(item_quantity) from ip_invoice_items it
                
                 WHERE it.invoice_id IN
                        (
                        SELECT invoice_id FROM ip_invoices
                            WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
							AND case
							when (month(invoice_date_created) >=4) then
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR(        invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3  )
							else 
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3  )
							end	
							
							
                    )
						
            ) AS quantity");


            $this->db->select("
            (
                SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts 
                    WHERE ip_invoice_amounts.invoice_id IN
                    (
                        SELECT invoice_id FROM ip_invoices
                            WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
							AND case
							when (month(invoice_date_created) >=4) then
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR(        invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3  )
							else 
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3  )
							end	
							
							
                    )
            ) AS sales");

            $this->db->select("
            (
                SELECT SUM(invoice_total) FROM ip_invoice_amounts 
                    WHERE ip_invoice_amounts.invoice_id IN
                    (
                        SELECT invoice_id FROM ip_invoices
                            WHERE ip_invoices.client_id = ip_clients.client_id and client_name = ".$this->db->escape($client)." and invoice_status_id != 1 and invoice_status_id != 5
							AND case
							when (month(invoice_date_created) >=4) then
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR(        invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3  )
							else 
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3  )
							end	
							
							
                    ) 
            ) AS sales_with_tax");
            
			$this->db->where('
                client_id IN
                (
                    SELECT client_id FROM ip_invoices
					WHERE  client_name = '.$this->db->escape($client).' and invoice_status_id != 1 and invoice_status_id != 5
					and case
							when (month(invoice_date_created) >=4) then
								 (YEAR(invoice_date_created) = ' . $this->db->escape($a) . ' and MONTH(invoice_date_created) >= 4 ) or ( YEAR(        invoice_date_created) = ' . $this->db->escape($b) . '+1 and MONTH(invoice_date_created) <= 3  )
							else 
								 (YEAR(invoice_date_created) = ' . $this->db->escape($a) . '-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = ' . $this->db->escape($b) . ' and MONTH(invoice_date_created) <= 3  )
							end	
							
                        
                )');
           
                            
        }
		else if ($a and $b and $c){
			
           
           $this->db->select("
            (
                SELECT COUNT(*) FROM ip_invoices
                    WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
                        AND 
							case
							when (month(invoice_date_created) >=4) then
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR(        invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3  )
							else 
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3  )
							end	
							AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
            ) AS invoice_count");
			  $this->db->select("
            (
                select sum(item_quantity) from ip_invoice_items it
                
                 WHERE it.invoice_id IN
                        (
                        SELECT invoice_id FROM ip_invoices
                            WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
							AND case
							when (month(invoice_date_created) >=4) then
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR(        invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3  )
							else 
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3  )
							end	
							AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
							
                            
                    )
						
            ) AS quantity");


            $this->db->select("
            (
                SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts 
                    WHERE ip_invoice_amounts.invoice_id IN
                    (
                        SELECT invoice_id FROM ip_invoices
                            WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
							AND case
							when (month(invoice_date_created) >=4) then
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR(        invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3  )
							else 
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3  )
							end	
							AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
							
                    )
            ) AS sales");

            $this->db->select("
            (
                SELECT SUM(invoice_total) FROM ip_invoice_amounts 
                    WHERE ip_invoice_amounts.invoice_id IN
                    (
                        SELECT invoice_id FROM ip_invoices
                            WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
							AND case
							when (month(invoice_date_created) >=4) then
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR(        invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3  )
							else 
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3  )
							end	
							AND monthname(invoice_date_created) = " . $this->db->escape($c) . "
							
                    ) 
            ) AS sales_with_tax");
            
			$this->db->where('
                client_id IN
                (
                    SELECT client_id FROM ip_invoices
					WHERE  case
							when (month(invoice_date_created) >=4) then
								 (YEAR(invoice_date_created) = ' . $this->db->escape($a) . ' and MONTH(invoice_date_created) >= 4 ) or ( YEAR(        invoice_date_created) = ' . $this->db->escape($b) . '+1 and MONTH(invoice_date_created) <= 3  )
							else 
								 (YEAR(invoice_date_created) = ' . $this->db->escape($a) . '-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = ' . $this->db->escape($b) . ' and MONTH(invoice_date_created) <= 3  )
							end	
							AND monthname(invoice_date_created) = ' . $this->db->escape($c) .  ' and invoice_status_id != 1 and invoice_status_id != 5
                        
                )');
           
                            
        }
		else if ($a and $b){
			
           
           $this->db->select("
            (
                SELECT COUNT(*) FROM ip_invoices
                    WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
                        AND 
							case
							when (month(invoice_date_created) >=4) then
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR(        invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3  )
							else 
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3  )
							end	
							
            ) AS invoice_count");
			  $this->db->select("
            (
                select sum(item_quantity) from ip_invoice_items it
                
                 WHERE it.invoice_id IN
                        (
                        SELECT invoice_id FROM ip_invoices
                            WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
							AND case
							when (month(invoice_date_created) >=4) then
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR(        invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3  )
							else 
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3  )
							end	
							
							
                    )
						
            ) AS quantity");


            $this->db->select("
            (
                SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts 
                    WHERE ip_invoice_amounts.invoice_id IN
                    (
                        SELECT invoice_id FROM ip_invoices
                            WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
							AND case
							when (month(invoice_date_created) >=4) then
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR(        invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3  )
							else 
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3  )
							end	
							
							
                    )
            ) AS sales");

            $this->db->select("
            (
                SELECT SUM(invoice_total) FROM ip_invoice_amounts 
                    WHERE ip_invoice_amounts.invoice_id IN
                    (
                        SELECT invoice_id FROM ip_invoices
                            WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
							AND case
							when (month(invoice_date_created) >=4) then
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . " and MONTH(invoice_date_created) >= 4 ) or ( YEAR(        invoice_date_created) = " . $this->db->escape($b) . "+1 and MONTH(invoice_date_created) <= 3  )
							else 
								 (YEAR(invoice_date_created) = " . $this->db->escape($a) . "-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = " . $this->db->escape($b) . " and MONTH(invoice_date_created) <= 3  )
							end	
							
							
                    ) 
            ) AS sales_with_tax");
            
			$this->db->where('
                client_id IN
                (
                    SELECT client_id FROM ip_invoices
					WHERE invoice_status_id != 1 and invoice_status_id != 5
					and case
							when (month(invoice_date_created) >=4) then
								 (YEAR(invoice_date_created) = ' . $this->db->escape($a) . ' and MONTH(invoice_date_created) >= 4 ) or ( YEAR(        invoice_date_created) = ' . $this->db->escape($b) . '+1 and MONTH(invoice_date_created) <= 3  )
							else 
								 (YEAR(invoice_date_created) = ' . $this->db->escape($a) . '-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = ' . $this->db->escape($b) . ' and MONTH(invoice_date_created) <= 3  )
							end	
							
                        
                )');
           
                            
        }
		else if($client){
			// $a = date_to_mysql($a);
            // $b = date_to_mysql($b);
			// $c = date_to_mysql($c);
           
           $this->db->select("
            (
                SELECT COUNT(*) FROM ip_invoices
                    WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
                        AND client_name = " . $this->db->escape($client) . "
							
            ) AS invoice_count");
			  $this->db->select("
            (
                select sum(item_quantity) from ip_invoice_items it
                
                 WHERE it.invoice_id IN
                        (
                        SELECT invoice_id FROM ip_invoices
                            WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
							AND client_name = " . $this->db->escape($client) . "
							
                    )
						
            ) AS quantity");


            $this->db->select("
            (
                SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts 
                    WHERE ip_invoice_amounts.invoice_id IN
                    (
                        SELECT invoice_id FROM ip_invoices
                            WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
							AND client_name = " . $this->db->escape($client) . "
                    )
            ) AS sales");

            $this->db->select("
            (
                SELECT SUM(invoice_total) FROM ip_invoice_amounts 
                    WHERE ip_invoice_amounts.invoice_id IN
                    (
                        SELECT invoice_id FROM ip_invoices
                            WHERE ip_invoices.client_id = ip_clients.client_id and invoice_status_id != 1 and invoice_status_id != 5
							AND client_name = " . $this->db->escape($client) . "
                    ) 
            ) AS sales_with_tax");
            
			$this->db->where('
                client_id IN
                (
                    SELECT client_id FROM ip_invoices
					WHERE  client_name = ' . $this->db->escape($client) . ' and invoice_status_id != 1 and invoice_status_id != 5
                        
                )');
           
                            
        }
        //$this->db->group_by('client_namesurname,quantity,invoice_count');
        $this->db->order_by('client_namesurname');

        return $this->db->get('ip_clients')->result();
    }

    /**
     * @param null $from_date
     * @param null $to_date
     * @return mixed
     */
    public function payment_history($from_date = null, $to_date = null)
    {
        $this->load->model('payments/mdl_payments');

        if ($from_date and $to_date) {
            $from_date = date_to_mysql($from_date);
            $to_date = date_to_mysql($to_date);

            $this->mdl_payments->where('payment_date >=', $from_date);
            $this->mdl_payments->where('payment_date <=', $to_date);
        }

        return $this->mdl_payments->get()->result();
    }

    /**
     * @return mixed
     */
	 // public function invoice_a()
	 // {
		 
		 // $sql="SELECT invoice_amount_id,invoice_id,invoice_sign,invoice_item_subtotal,invoice_tax_total,invoice_total FROM kasiram9_InvoiceDEV.ip_invoice_amounts;";
        // $query = $this->db->query($sql);
        // return $query->result();	
		// print_r($query);
	 // }
    public function invoice_aging()
    {
        $this->db->select('client_name, client_surname');

        $this->db->select('
        (
            SELECT SUM(invoice_balance) FROM ip_invoice_amounts 
                WHERE invoice_id IN
                (
                    SELECT invoice_id FROM ip_invoices
                        WHERE ip_invoices.client_id = ip_clients.client_id  and ip_invoices.invoice_status_id != 1 and ip_invoices.invoice_status_id != 5
                            AND invoice_date_due <= DATE_SUB(NOW(),INTERVAL 1 DAY) 
                            AND invoice_date_due >= DATE_SUB(NOW(), INTERVAL 30 DAY)
                )
        ) AS range_1', false);

        $this->db->select('
        (
            SELECT SUM(invoice_balance) FROM ip_invoice_amounts 
                WHERE invoice_id IN
                (
                    SELECT invoice_id FROM ip_invoices
                        WHERE ip_invoices.client_id = ip_clients.client_id and ip_invoices.invoice_status_id != 1 and ip_invoices.invoice_status_id != 5
                            AND invoice_date_due <= DATE_SUB(NOW(),INTERVAL 30 DAY) 
                            AND invoice_date_due >= DATE_SUB(NOW(), INTERVAL 60 DAY)
                )
        ) AS range_2', false);
		 $this->db->select('
        (
            SELECT SUM(invoice_balance) FROM ip_invoice_amounts 
                WHERE invoice_id IN
                (
                    SELECT invoice_id FROM ip_invoices
                        WHERE ip_invoices.client_id = ip_clients.client_id and ip_invoices.invoice_status_id != 1 and ip_invoices.invoice_status_id != 5
                            AND invoice_date_due <= DATE_SUB(NOW(),INTERVAL 60 DAY) 
                            AND invoice_date_due >= DATE_SUB(NOW(), INTERVAL 90 DAY)
                )
        ) AS range_3', false);

        $this->db->select('
        (
            SELECT SUM(invoice_balance) FROM ip_invoice_amounts 
                WHERE invoice_id IN
                (
                    SELECT invoice_id FROM ip_invoices
                        WHERE ip_invoices.client_id = ip_clients.client_id and ip_invoices.invoice_status_id != 1 and ip_invoices.invoice_status_id != 5
                            AND invoice_date_due <= DATE_SUB(NOW(),INTERVAL 90 DAY)
                )
        ) AS range_4', false);

        $this->db->select('
        (
            SELECT SUM(invoice_balance) FROM ip_invoice_amounts 
                WHERE invoice_id IN
                (
                    SELECT invoice_id FROM ip_invoices
                        WHERE ip_invoices.client_id = ip_clients.client_id and ip_invoices.invoice_status_id != 1 and ip_invoices.invoice_status_id != 5
                            AND invoice_date_due <= DATE_SUB(NOW(), INTERVAL 1 DAY)
                )
        ) AS total_balance', false);

        $this->db->having('range_1 >', 0);
        $this->db->or_having('range_2 >', 0);
        $this->db->or_having('range_3 >', 0);
		$this->db->or_having('range_4 >', 0);
        $this->db->or_having('total_balance >', 0);

        return $this->db->get('ip_clients')->result();
    }
	

    /**
     * @param null $from_date
     * @param null $to_date
     * @param null $minQuantity
     * @param null $maxQuantity
     * @param bool $taxChecked
     * @return mixed
     */
    public function sales_by_year($from_date = null, $to_date = null, $minQuantity = null, $maxQuantity = null, $taxChecked = False)
    {
        if ($minQuantity == "") {
            $minQuantity = 0;
        }

        if ($from_date == "") {
            $from_date = date("Y-m-d");
        } else {
            $from_date = date_to_mysql($from_date);
        }

        if ($to_date == "") {
            $to_date = date("Y-m-d");
        } else {
            $to_date = date_to_mysql($to_date);
        }

        $from_date_year = intval(substr($from_date, 0, 4));
        $to_date_year = intval(substr($to_date, 0, 4));

        $this->db->select('client_name as Name');
        $this->db->select('client_name');
        $this->db->select('client_surname');
        $this->db->select('CONCAT(client_name," ", client_surname) AS client_namesurname');

        if ($taxChecked == false) {

            if ($maxQuantity) {

                $this->db->select('client_id');
                $this->db->select('client_vat_id AS VAT_ID');
                $this->db->select('
                (
                    SELECT SUM(amounts.invoice_item_subtotal) FROM ip_invoice_amounts amounts
                        WHERE amounts.invoice_id IN
                        (
                            SELECT inv.invoice_id FROM ip_invoices inv
                                WHERE inv.client_id=ip_clients.client_id
                                    AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created
                                    AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created
                        )
                ) AS total_payment', false);

                for ($index = $from_date_year; $index <= $to_date_year; $index++) {
                    $this->db->select('
                    (
                        SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts
                            WHERE invoice_id IN
                            (
                                SELECT invoice_id FROM ip_invoices inv
                                    WHERE inv.client_id=ip_clients.client_id 
                                        AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created 
                                        AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created 
                                        AND
                                        (
                                            inv.invoice_date_created LIKE \'%' . $index . '-01-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-02-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-03-%\'
                                        )
                            )
                    ) AS payment_t1_' . $index . '', false);

                    $this->db->select('
                    (
                        SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts
                            WHERE invoice_id IN
                            (
                                SELECT invoice_id FROM ip_invoices inv
                                    WHERE inv.client_id=ip_clients.client_id 
                                        AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created 
                                        AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created 
                                        AND
                                        (
                                            inv.invoice_date_created LIKE \'%' . $index . '-04-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-05-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-06-%\'
                                        )
                            )
                    ) AS payment_t2_' . $index . '', false);

                    $this->db->select('
                    (
                        SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts
                            WHERE invoice_id IN
                            (
                                SELECT invoice_id FROM ip_invoices inv
                                    WHERE inv.client_id=ip_clients.client_id 
                                        AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created 
                                        AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created 
                                        AND
                                        (
                                            inv.invoice_date_created LIKE \'%' . $index . '-07-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-08-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-09-%\'
                                        )
                            )
                    ) AS payment_t3_' . $index . '', false);

                    $this->db->select('
                    (
                        SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts
                            WHERE invoice_id IN
                            (
                                SELECT invoice_id FROM ip_invoices inv
                                    WHERE inv.client_id=ip_clients.client_id 
                                        AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created 
                                        AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created 
                                        AND
                                        (
                                            inv.invoice_date_created LIKE \'%' . $index . '-10-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-11-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-12-%\'
                                        )
                            )
                    ) AS payment_t4_' . $index . '', false);

                }

                $this->db->where('
                (
                    SELECT SUM(amounts.invoice_item_subtotal) FROM ip_invoice_amounts amounts 
                        WHERE amounts.invoice_id IN 
                        (
                            SELECT inv.invoice_id FROM ip_invoices inv
                                WHERE inv.client_id=ip_clients.client_id 
                                    AND ' . $this->db->escape($from_date) . ' <= inv.invoice_date_created 
                                    AND ' . $this->db->escape($to_date) . ' >= inv.invoice_date_created 
                                    AND ' . $minQuantity . ' <= 
                                    (
                                        SELECT SUM(amounts2.invoice_item_subtotal) FROM ip_invoice_amounts amounts2 
                                            WHERE amounts2.invoice_id IN 
                                            (
                                                SELECT inv2.invoice_id FROM ip_invoices inv2 
                                                    WHERE inv2.client_id=ip_clients.client_id 
                                                        AND ' . $this->db->escape($from_date) . ' <= inv2.invoice_date_created 
                                                        AND ' . $this->db->escape($to_date) . ' >= inv2.invoice_date_created
                                            )
                                    ) AND ' . $maxQuantity . ' >= 
                                    (
                                        SELECT SUM(amounts3.invoice_item_subtotal) FROM ip_invoice_amounts amounts3 
                                            WHERE amounts3.invoice_id IN 
                                            (
                                                SELECT inv3.invoice_id FROM ip_invoices inv3 
                                                    WHERE inv3.client_id=ip_clients.client_id 
                                                        AND ' . $this->db->escape($from_date) . ' <= inv3.invoice_date_created 
                                                        AND ' . $this->db->escape($to_date) . ' >= inv3.invoice_date_created
                                            )
                                    )
                        )
                ) <>0');

            } else {

                $this->db->select('client_id');
                $this->db->select('client_vat_id AS VAT_ID');
                $this->db->select('client_name as Name');

                $this->db->select('
                (
                    SELECT SUM(amounts.invoice_item_subtotal) FROM ip_invoice_amounts amounts 
                        WHERE amounts.invoice_id IN 
                        (
                            SELECT inv.invoice_id FROM ip_invoices inv 
                                WHERE inv.client_id=ip_clients.client_id 
                                    AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created 
                                    AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created
                        )
                ) AS total_payment', false);

                for ($index = $from_date_year; $index <= $to_date_year; $index++) {

                    $this->db->select('
                    (
                        SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts 
                            WHERE invoice_id IN 
                            (
                                SELECT invoice_id FROM ip_invoices inv
                                    WHERE inv.client_id=ip_clients.client_id 
                                        AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created 
                                        AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created 
                                        AND 
                                        (
                                            inv.invoice_date_created LIKE \'%' . $index . '-01-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-02-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-03-%\'
                                        )
                            )
                    ) AS payment_t1_' . $index . '', false);

                    $this->db->select('
                    (
                        SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts 
                            WHERE invoice_id IN 
                            (
                                SELECT invoice_id FROM ip_invoices inv
                                    WHERE inv.client_id=ip_clients.client_id 
                                        AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created 
                                        AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created 
                                        AND 
                                        (
                                            inv.invoice_date_created LIKE \'%' . $index . '-04-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-05-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-06-%\'
                                        )
                            )
                    ) AS payment_t2_' . $index . '', false);

                    $this->db->select('
                    (
                        SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts 
                            WHERE invoice_id IN 
                            (
                                SELECT invoice_id FROM ip_invoices inv
                                    WHERE inv.client_id=ip_clients.client_id 
                                        AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created 
                                        AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created 
                                        AND 
                                        (
                                            inv.invoice_date_created LIKE \'%' . $index . '-07-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-08-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-09-%\'
                                        )
                            )
                    ) AS payment_t3_' . $index . '', false);

                    $this->db->select('
                    (
                        SELECT SUM(invoice_item_subtotal) FROM ip_invoice_amounts 
                            WHERE invoice_id IN 
                            (
                                SELECT invoice_id FROM ip_invoices inv
                                    WHERE inv.client_id=ip_clients.client_id 
                                        AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created 
                                        AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created 
                                        AND 
                                        (
                                            inv.invoice_date_created LIKE \'%' . $index . '-10-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-11-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-12-%\'
                                        )
                            )
                    ) AS payment_t4_' . $index . '', false);

                }

                $this->db->where('
                (
                    SELECT SUM(amounts.invoice_item_subtotal) FROM ip_invoice_amounts amounts 
                        WHERE amounts.invoice_id IN 
                        (
                            SELECT inv.invoice_id FROM ip_invoices inv 
                                WHERE inv.client_id=ip_clients.client_id 
                                    AND ' . $this->db->escape($from_date) . ' <= inv.invoice_date_created 
                                    AND ' . $this->db->escape($to_date) . ' >= inv.invoice_date_created 
                                    AND ' . $minQuantity . ' <= 
                                    (
                                        SELECT SUM(amounts2.invoice_item_subtotal) FROM ip_invoice_amounts amounts2 
                                            WHERE amounts2.invoice_id IN 
                                            (
                                                SELECT inv2.invoice_id FROM ip_invoices inv2 
                                                WHERE inv2.client_id=ip_clients.client_id 
                                                    AND ' . $this->db->escape($from_date) . ' <= inv2.invoice_date_created 
                                                    AND ' . $this->db->escape($to_date) . ' >= inv2.invoice_date_created
                                            )
                                    )
                        )
                ) <>0');

            }

        } else if ($taxChecked == true) {

            if ($maxQuantity) {

                $this->db->select('client_id');
                $this->db->select('client_vat_id AS VAT_ID');
                $this->db->select('client_name as Name');

                $this->db->select('
                (
                    SELECT SUM(amounts.invoice_total) FROM ip_invoice_amounts amounts 
                        WHERE amounts.invoice_id IN 
                        (
                            SELECT inv.invoice_id FROM ip_invoices inv 
                                WHERE inv.client_id=ip_clients.client_id 
                                    AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created 
                                    AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created
                        )
                ) AS total_payment', false);

                for ($index = $from_date_year; $index <= $to_date_year; $index++) {

                    $this->db->select('
                    (
                        SELECT SUM(invoice_total) FROM ip_invoice_amounts 
                            WHERE invoice_id IN 
                            (
                                SELECT invoice_id FROM ip_invoices inv 
                                    WHERE inv.client_id=ip_clients.client_id 
                                        AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created 
                                        AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created 
                                        AND 
                                        (
                                            inv.invoice_date_created LIKE \'%' . $index . '-01-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-02-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-03-%\'
                                        )
                            )
                    ) AS payment_t1_' . $index . '', false);

                    $this->db->select('
                    (
                        SELECT SUM(invoice_total) FROM ip_invoice_amounts 
                            WHERE invoice_id IN 
                            (
                                SELECT invoice_id FROM ip_invoices inv 
                                    WHERE inv.client_id=ip_clients.client_id 
                                        AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created 
                                        AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created 
                                        AND 
                                        (
                                            inv.invoice_date_created LIKE \'%' . $index . '-04-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-05-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-06-%\'
                                        )
                            )
                    ) AS payment_t2_' . $index . '', false);

                    $this->db->select('
                    (
                        SELECT SUM(invoice_total) FROM ip_invoice_amounts 
                            WHERE invoice_id IN 
                            (
                                SELECT invoice_id FROM ip_invoices inv 
                                    WHERE inv.client_id=ip_clients.client_id 
                                        AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created 
                                        AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created 
                                        AND 
                                        (
                                            inv.invoice_date_created LIKE \'%' . $index . '-07-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-08-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-09-%\'
                                        )
                            )
                    ) AS payment_t3_' . $index . '', false);

                    $this->db->select('
                    (
                        SELECT SUM(invoice_total) FROM ip_invoice_amounts 
                            WHERE invoice_id IN 
                            (
                                SELECT invoice_id FROM ip_invoices inv 
                                    WHERE inv.client_id=ip_clients.client_id 
                                        AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created 
                                        AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created 
                                        AND 
                                        (
                                            inv.invoice_date_created LIKE \'%' . $index . '-10-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-11-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-12-%\'
                                        )
                            )
                    ) AS payment_t4_' . $index . '', false);

                }

                $this->db->where('
                (
                    SELECT SUM(amounts.invoice_total) FROM ip_invoice_amounts amounts 
                        WHERE amounts.invoice_id IN 
                        (
                            SELECT inv.invoice_id FROM ip_invoices inv 
                                WHERE inv.client_id=ip_clients.client_id 
                                    AND ' . $this->db->escape($from_date) . ' <= inv.invoice_date_created 
                                    AND ' . $this->db->escape($to_date) . ' >= inv.invoice_date_created 
                                    AND ' . $minQuantity . ' <= 
                                    (
                                        SELECT SUM(amounts2.invoice_total) FROM ip_invoice_amounts amounts2 
                                            WHERE amounts2.invoice_id IN 
                                            (
                                                SELECT inv2.invoice_id FROM ip_invoices inv2 
                                                    WHERE inv2.client_id=ip_clients.client_id 
                                                        AND ' . $this->db->escape($from_date) . ' <= inv2.invoice_date_created 
                                                        AND ' . $this->db->escape($to_date) . ' >= inv2.invoice_date_created
                                            )
                                    ) AND ' . $maxQuantity . ' >= 
                                    (
                                        SELECT SUM(amounts3.invoice_total) FROM ip_invoice_amounts amounts3 
                                            WHERE amounts3.invoice_id IN 
                                            (
                                                SELECT inv3.invoice_id FROM ip_invoices inv3 
                                                    WHERE inv3.client_id=ip_clients.client_id 
                                                        AND ' . $this->db->escape($from_date) . ' <= inv3.invoice_date_created 
                                                        AND ' . $this->db->escape($to_date) . ' >= inv3.invoice_date_created
                                            )
                                    )
                        )
                ) <>0');

            } else {

                $this->db->select('client_id');
                $this->db->select('client_vat_id AS VAT_ID');
                $this->db->select('client_name as Name');

                $this->db->select('
                (
                    SELECT SUM(amounts.invoice_total) FROM ip_invoice_amounts amounts 
                        WHERE amounts.invoice_id IN 
                        (
                            SELECT inv.invoice_id FROM ip_invoices inv 
                                WHERE inv.client_id=ip_clients.client_id 
                                    AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created 
                                    AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created
                        )
                ) AS total_payment', false);

                for ($index = $from_date_year; $index <= $to_date_year; $index++) {

                    $this->db->select('
                    (
                        SELECT SUM(invoice_total) FROM ip_invoice_amounts 
                            WHERE invoice_id IN 
                            (
                                SELECT invoice_id FROM ip_invoices inv 
                                    WHERE inv.client_id=ip_clients.client_id 
                                        AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created 
                                        AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created 
                                        AND 
                                        (
                                            inv.invoice_date_created LIKE \'%' . $index . '-01-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-02-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-03-%\'
                                        )
                            )
                    ) AS payment_t1_' . $index . '', false);

                    $this->db->select('
                    (
                        SELECT SUM(invoice_total) FROM ip_invoice_amounts 
                            WHERE invoice_id IN 
                            (
                                SELECT invoice_id FROM ip_invoices inv 
                                    WHERE inv.client_id=ip_clients.client_id 
                                        AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created 
                                        AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created 
                                        AND 
                                        (
                                            inv.invoice_date_created LIKE \'%' . $index . '-04-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-05-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-06-%\'
                                        )
                            )
                    ) AS payment_t2_' . $index . '', false);

                    $this->db->select('
                    (
                        SELECT SUM(invoice_total) FROM ip_invoice_amounts 
                            WHERE invoice_id IN 
                            (
                                SELECT invoice_id FROM ip_invoices inv 
                                    WHERE inv.client_id=ip_clients.client_id 
                                        AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created 
                                        AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created 
                                        AND 
                                        (
                                            inv.invoice_date_created LIKE \'%' . $index . '-07-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-08-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-09-%\'
                                        )
                            )
                    ) AS payment_t3_' . $index . '', false);

                    $this->db->select('
                    (
                        SELECT SUM(invoice_total) FROM ip_invoice_amounts 
                            WHERE invoice_id IN 
                            (
                                SELECT invoice_id FROM ip_invoices inv 
                                    WHERE inv.client_id=ip_clients.client_id 
                                        AND ' . $this->db->escape($from_date) . '<= inv.invoice_date_created 
                                        AND ' . $this->db->escape($to_date) . '>= inv.invoice_date_created 
                                        AND 
                                        (
                                            inv.invoice_date_created LIKE \'%' . $index . '-10-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-11-%\' 
                                            OR inv.invoice_date_created LIKE \'%' . $index . '-12-%\'
                                        )
                            )
                    ) AS payment_t4_' . $index . '', false);

                }

                $this->db->where('
                (
                    SELECT SUM(amounts.invoice_total) FROM ip_invoice_amounts amounts 
                        WHERE amounts.invoice_id IN 
                        (
                            SELECT inv.invoice_id FROM ip_invoices inv 
                                WHERE inv.client_id=ip_clients.client_id 
                                    AND ' . $this->db->escape($from_date) . ' <= inv.invoice_date_created 
                                    AND ' . $this->db->escape($to_date) . ' >= inv.invoice_date_created 
                                    AND ' . $minQuantity . ' <= 
                                    (
                                        SELECT SUM(amounts2.invoice_total) FROM ip_invoice_amounts amounts2 
                                            WHERE amounts2.invoice_id IN 
                                            (
                                                SELECT inv2.invoice_id FROM ip_invoices inv2 
                                                    WHERE inv2.client_id=ip_clients.client_id 
                                                        AND ' . $this->db->escape($from_date) . ' <= inv2.invoice_date_created 
                                                        AND ' . $this->db->escape($to_date) . ' >= inv2.invoice_date_created
                                            )
                                    )
                        )
                ) <>0');

            }

        }

        $this->db->order_by('client_namesurname');
        return $this->db->get('ip_clients')->result();
    }
	public function cumulative_report()
	{
		          if (date('m') >= 4) {
								$yearl = date('Y').'-'.(date('Y')+1);
							} else {
								$yearl = (date('Y')-1).'-'.date('Y');
							}
					 $ab=$yearl;
		           $fa=substr($ab,0,-5);
			        $aa=substr($ab,5,5);
					//echo $aa;
				$sql="select distinct Year.client_id,Year.client_name,Year.product_id,Year.product_description,Year.invoice_count,Year.Qty,Year.Value,Month.Qty as MQty,Month.Value as MValue,Today.Qty as TQty,Today.Value as TValue from 
					(SELECT invoice.client_id as client_id,client.client_name as client_name,ifnull(count(invoice.invoice_id),0) as invoice_count,pro.product_id,pro.product_description,ifnull(sum(items.item_quantity),0) as Qty,ifnull(sum(amt.item_subtotal),0) as Value from ip_invoices invoice
					join ip_clients client on invoice.client_id =client.client_id 
					left join ip_invoice_items items on invoice.invoice_id = items.invoice_id 
					left join ip_products pro on items.item_product_id = pro.product_id
					left join ip_invoice_item_amounts amt on items.item_id = amt.item_id
					where (invoice.invoice_date_created >= '".$fa."-04-01' and invoice.invoice_date_created <= '".$aa."-03-31') and invoice.invoice_status_id != 1 and invoice.invoice_status_id != 5
					group by client_id,pro.product_id) as Year left join 

					(SELECT invoice.client_id as client_id,client.client_name as client_name,ifnull(count(invoice.invoice_id),0) as invoice_count,pro.product_id,pro.product_description,ifnull(sum(items.item_quantity),0) as Qty,ifnull(sum(amt.item_subtotal),0) as Value from ip_invoices invoice
					join ip_clients client on invoice.client_id =client.client_id 
					left join ip_invoice_items items on invoice.invoice_id = items.invoice_id 
					left join ip_products pro on items.item_product_id = pro.product_id
					left join ip_invoice_item_amounts amt on items.item_id = amt.item_id
					where month(invoice.invoice_date_created)=month(CURRENT_DATE()) and invoice.invoice_status_id != 1 and invoice.invoice_status_id != 5
					group by client_id,pro.product_id) as Month on Year.client_name = Month.client_name and Year.product_id = Month.product_id

					left join 
					(SELECT invoice.client_id as client_id,client.client_name as client_name,ifnull(count(invoice.invoice_id),0) as invoice_count,pro.product_id,pro.product_description,ifnull(sum(items.item_quantity),0) as Qty,ifnull(sum(amt.item_subtotal),0) as Value from ip_invoices invoice
					join ip_clients client on invoice.client_id =client.client_id 
					left join ip_invoice_items items on invoice.invoice_id = items.invoice_id 
					left join ip_products pro on items.item_product_id = pro.product_id
					left join ip_invoice_item_amounts amt on items.item_id = amt.item_id
					where invoice.invoice_date_created=CURRENT_DATE and invoice.invoice_status_id != 1 and invoice.invoice_status_id != 5
					group by client_id,pro.product_id) as Today on Year.client_name = Today.client_name and Year.product_id = Today.product_id

					group by Year.client_id,Year.product_id;";
					//echo $sql;
				$query = $this->db->query($sql);
				return $query->result();	

	}
	
	
	public function cumulative_report_year($clientid,$productid)
	{
		if (date('m') >= 4) {
								$yearl = date('Y').'-'.(date('Y')+1);
							} else {
								$yearl = (date('Y')-1).'-'.date('Y');
							}
					 $ab=$yearl;
		           $fa=substr($ab,0,-5);
			        $aa=substr($ab,5,5);
		$sql = "SELECT invoice.client_id as client_id,client.client_name as client_name,invoice.invoice_id as invoice_count,pro.product_id,pro.product_description,items.item_quantity as Qty,amt.item_subtotal as Value from ip_invoices invoice
				join ip_clients client on invoice.client_id =client.client_id 
				left join ip_invoice_items items on invoice.invoice_id = items.invoice_id 
				left join ip_products pro on items.item_product_id = pro.product_id 
				left join ip_invoice_item_amounts amt on items.item_id = amt.item_id
				where (invoice.invoice_date_created >= '".$fa."-04-01' and invoice.invoice_date_created <= '".$aa."-03-31')
				and invoice.invoice_status_id != 1 and invoice.invoice_status_id != 5 and invoice.client_id  = ? and items.item_product_id = ?";
				
				
				$query = $this->db->query($sql,array($clientid,$productid));
				return $query->result();
				
		
	}
	
	public function cumulative_report_month($clientid,$productid)
	{
			$sql = "SELECT invoice.client_id as client_id,client.client_name as client_name,invoice.invoice_id as invoice_count,pro.product_id,pro.product_description,items.item_quantity as MQty,amt.item_subtotal as MValue from ip_invoices invoice
					join ip_clients client on invoice.client_id =client.client_id 
					left join ip_invoice_items items on invoice.invoice_id = items.invoice_id 
					left join ip_products pro on items.item_product_id = pro.product_id
					left join ip_invoice_item_amounts amt on items.item_id = amt.item_id
					where month(invoice.invoice_date_created)=month(CURRENT_DATE()) and  invoice.invoice_status_id != 1 and invoice.invoice_status_id != 5
					and invoice.client_id = ? and  items.item_product_id = ?";
					
					$query = $this->db->query($sql,array($clientid,$productid));
				return $query->result();
	}
	
	public function cumulative_report_day($clientid,$productid)
	{
		
		$sql = "SELECT invoice.invoice_number as invoice_number,invoice.client_id as client_id,client.client_name as client_name,invoice.invoice_id as invoice_count,pro.product_id,pro.product_description,items.item_quantity as TQty,amt.item_subtotal as TValue from ip_invoices invoice
				join ip_clients client on invoice.client_id =client.client_id 
				left join ip_invoice_items items on invoice.invoice_id = items.invoice_id 
				left join ip_products pro on items.item_product_id = pro.product_id
				left join ip_invoice_item_amounts amt on items.item_id = amt.item_id
				where invoice.invoice_date_created=CURRENT_DATE and invoice.invoice_status_id != 1 and invoice.invoice_status_id != 5 and
				invoice.client_id = ? and items.item_product_id = ?
				";
				$query = $this->db->query($sql,array($clientid,$productid));
				return $query->result();
				
	}
	public function sales($cust=null,$prod=null,$a=null,$b=null,$c=null,$from=null,$to=null)
	{
		
		$sql = "select ii.invoice_id as invoice_id,ii.client_id as client_id,ii.invoice_number as invoice_number,ii.invoice_date_created as invoice_date_created,TIME_FORMAT(ii.invoice_time_created,'%l:%i %p') as invoice_time_created,icf.invoice_custom_fieldvalue as vehicle_no,ip.product_description as product_description,concat(ic.client_name,'-',ic.client_surname)as client_name,sum(iit.item_quantity) as item_quantity,sum(iit.item_price) as item_price,sum(iia.item_subtotal) as item_subtotal,
				sum(ifnull(((sgst.tax_rate_percent * (iit.item_quantity * iit.item_price))/100),0))
				as sgst,
				sum(ifnull(((iit.item_quantity * iit.item_price) * cgst.tax_rate_percent)/100,0))
				as cgst,
				sum(ifnull((igst.tax_rate_percent * (iit.item_quantity * iit.item_price))/100,0))
				as igst,
				sum((iia.item_subtotal + ifnull(((sgst.tax_rate_percent * iia.item_subtotal)/100),0)
				+
				ifnull(((iia.item_subtotal * cgst.tax_rate_percent)/100),0)
				+
				ifnull(((igst.tax_rate_percent * iia.item_subtotal)/100),0)))
				 as total
				from ip_invoices ii
				join ip_invoice_custom icf on icf.invoice_id = ii.invoice_id
				join ip_invoice_items iit on iit.invoice_id = ii.invoice_id 
				join ip_invoice_item_amounts iia on iia.item_id = iit.item_id
				join ip_clients ic on ic.client_id = ii.client_id
				join ip_products ip on ip.product_id = iit.item_product_id
				left join ip_tax_rates sgst on sgst.tax_rate_id = iit.sgst_item_tax_rate_id 
				left join ip_tax_rates cgst on cgst.tax_rate_id = iit.cgst_item_tax_rate_id 
				left join ip_tax_rates igst on igst.tax_rate_id = iit.igst_item_tax_rate_id 
				where icf.invoice_custom_fieldid = '8' and ii.invoice_status_id != 1 and ii.invoice_status_id != 5";
				
				
				if ($prod!= ''){
			
					$sql.="and ip.product_description = '".$prod."'";
				
				}
				
				if ($cust!= '')
				{
					$sql.="and ic.client_name = '".$cust."'";
				}
				if ($a and $b != ''){
			
					$sql.=" and (ii.invoice_date_created >= '".$a."-04-01' and ii.invoice_date_created <= '".$b."-03-31')";
				
				}
				
				if ($c!= ''){
			
					$sql.="and monthname(ii.invoice_date_created) = '".$c."'";
				
				}
				
				if ($from and $to != ''){
					$fromdate= date("Y-m-d",strtotime($from));
		            $todate=date("Y-m-d",strtotime($to));
			
					$sql.="and ii.invoice_date_created >= '".$fromdate."'
                        and ii.invoice_date_created <= '".$todate."'";
				
				}
				
				
				// echo $fromdate;
				// echo $sql;
				$sql.="group by invoice_number,product_description";
				$query = $this->db->query($sql);
				return $query->result();
				
				
	}             
	public function sales_l($cname=null,$fa=null,$aa=null,$m=null,$frm=null,$t=null,$prod=null,$datec=null,$dmon=null)
	{
		
		$sql = "select ii.invoice_id as invoice_id,ii.client_id as client_id,ii.invoice_number as invoice_number,ii.invoice_date_created as invoice_date_created,TIME_FORMAT(ii.invoice_time_created,'%l:%i %p') as invoice_time_created,icf.invoice_custom_fieldvalue as vehicle_no,ip.product_description as product_description,concat(ic.client_name,'-',ic.client_surname)as client_name,sum(iit.item_quantity) as item_quantity,sum(iit.item_price) as item_price,sum(iia.item_subtotal) as item_subtotal,
				sum(ifnull(((sgst.tax_rate_percent * (iit.item_quantity * iit.item_price))/100),0))
				as sgst,
				sum(ifnull(((iit.item_quantity * iit.item_price) * cgst.tax_rate_percent)/100,0))
				as cgst,
				sum(ifnull((igst.tax_rate_percent * (iit.item_quantity * iit.item_price))/100,0))
				as igst,
				sum((iia.item_subtotal + ifnull(((sgst.tax_rate_percent * iia.item_subtotal)/100),0)
				+
				ifnull(((iia.item_subtotal * cgst.tax_rate_percent)/100),0)
				+
				ifnull(((igst.tax_rate_percent * iia.item_subtotal)/100),0)))
				 as total
				from ip_invoices ii
				join ip_invoice_custom icf on icf.invoice_id = ii.invoice_id
				join ip_invoice_items iit on iit.invoice_id = ii.invoice_id 
				join ip_invoice_item_amounts iia on iia.item_id = iit.item_id
				join ip_clients ic on ic.client_id = ii.client_id
				join ip_products ip on ip.product_id = iit.item_product_id
				left join ip_tax_rates sgst on sgst.tax_rate_id = iit.sgst_item_tax_rate_id 
				left join ip_tax_rates cgst on cgst.tax_rate_id = iit.cgst_item_tax_rate_id 
				left join ip_tax_rates igst on igst.tax_rate_id = iit.igst_item_tax_rate_id 
				where icf.invoice_custom_fieldid = '8' and ii.invoice_status_id != 1 ";
				
				
				if ($cname!= ''){
			
					$sql.="and ic.client_name = '".$cname."'";
				
				}
				
				
				if ($fa and $aa != ''){
			
					$sql.=" and (ii.invoice_date_created >= '".$fa."-04-01' and ii.invoice_date_created <= '".$aa."-03-31')";
				
				}
				if ($m!= ''){
			
					$sql.="and monthname(ii.invoice_date_created) = '".$m."'";
				
				}
				
				if ($frm and $t != ''){
					$fromd= date("Y-m-d",strtotime($frm));
		            $tod=date("Y-m-d",strtotime($t));
			
					$sql.="and ii.invoice_date_created >= '".$fromd."'
                        and ii.invoice_date_created <= '".$tod."'";
				
				}
				if ($prod!= ''){
			
					$sql.=" and ip.product_description = '".$prod."' ";
				
				}
				if ($dmon!= ''){
					$mon=substr($dmon,0,-5);
			        $mony=substr($dmon,3,4);
			
					$sql.="and year(ii.invoice_date_created) = year(CURRENT_DATE()) and month(ii.invoice_date_created) = MONTH(CURRENT_DATE())";
				
				}
				if ($datec!= ''){
			
					$sql.="and ii.invoice_date_created = '".$datec."'";
				
				}
				
				
				// echo $mon;
				// echo $mony;
				// echo $sql;
				$sql.=" group by invoice_number,product_description";
				$query = $this->db->query($sql);
				return $query->result();
				
				
	}
	public function sales_dashboard()
	{
		
		$sql = "select ii.invoice_id as invoice_id,ii.client_id as client_id,ii.invoice_number as invoice_number,ii.invoice_date_created as invoice_date_created,TIME_FORMAT(ii.invoice_time_created,'%l:%i %p') as invoice_time_created,icf.invoice_custom_fieldvalue as vehicle_no,ip.product_description as product_description,concat(ic.client_name,'-',ic.client_surname)as client_name,sum(iit.item_quantity) as item_quantity,sum(iit.item_price) as item_price,sum(iia.item_subtotal) as item_subtotal, sum(iia.item_sgst_amt) as sgst,sum(iia.item_cgst_amt) as cgst, sum(iia.item_igst_amt) as igst,
				sum(ifnull(tcs.tax_rate_percent * iia.item_total/100,0))
				as tcs,
				
				
				sum(ifnull(tcs.tax_rate_percent * iia.item_total/100,0))
+sum(iia.item_total)
				 as total
				from ip_invoices ii
				left join ip_invoice_custom icf on icf.invoice_id = ii.invoice_id
				
				left join ip_invoice_tax_rates tc on tc.invoice_id = ii.invoice_id
				
				left join ip_invoice_items iit on iit.invoice_id = ii.invoice_id 
left join ip_invoice_item_amounts iia on iia.item_id = iit.item_id
				left join ip_clients ic on ic.client_id = ii.client_id
				left join ip_products ip on ip.product_id = iit.item_product_id
				left join ip_tax_rates sgst on sgst.tax_rate_id = iit.sgst_item_tax_rate_id 
				left join ip_tax_rates cgst on cgst.tax_rate_id = iit.cgst_item_tax_rate_id 
				left join ip_tax_rates igst on igst.tax_rate_id = iit.igst_item_tax_rate_id 
				left join ip_tax_rates tcs  on tcs.tax_rate_id =  tc.tax_rate_id 
				where icf.invoice_custom_fieldid = '8' and ii.invoice_status_id != 1 and ii.invoice_date_created = CURRENT_DATE";
				$sql.=" group by invoice_number,product_description";
				$query = $this->db->query($sql);
                // print_r($this->db->last_query());
				return $query->result();
				
				
	}
	public function consolidate_sales($fa,$aa,$cname=null)
	{
		
		$sql = "select ic.client_name as client_name,ip.product_description as product_description,
SUM(IF(month(ii.invoice_date_created) = 4, iit.item_quantity, 0)) AS April,
SUM(IF(month(ii.invoice_date_created) = 5, iit.item_quantity, 0)) AS May,
SUM(IF(month(ii.invoice_date_created) = 6, iit.item_quantity, 0)) AS June,
SUM(IF(month(ii.invoice_date_created) = 7, iit.item_quantity, 0)) AS July,
SUM(IF(month(ii.invoice_date_created) = 8, iit.item_quantity, 0)) AS August,
SUM(IF(month(ii.invoice_date_created) = 9, iit.item_quantity, 0)) AS September,
SUM(IF(month(ii.invoice_date_created) = 10, iit.item_quantity, 0)) AS October,
SUM(IF(month(ii.invoice_date_created) = 11, iit.item_quantity, 0)) AS November,
SUM(IF(month(ii.invoice_date_created) = 12, iit.item_quantity, 0)) AS December,
SUM(IF(month(ii.invoice_date_created) = 1, iit.item_quantity, 0)) AS January,
SUM(IF(month(ii.invoice_date_created) = 2, iit.item_quantity, 0)) AS February,
SUM(IF(month(ii.invoice_date_created) = 3, iit.item_quantity, 0)) AS March,
sum(iit.item_quantity) as qtotal,
SUM(IF(month(ii.invoice_date_created) = 4, iit.item_quantity * iit.item_price, 0)) AS vApril,
SUM(IF(month(ii.invoice_date_created) = 5, iit.item_quantity * iit.item_price, 0)) AS vMay,
SUM(IF(month(ii.invoice_date_created) = 6, iit.item_quantity * iit.item_price, 0)) AS vJune,
SUM(IF(month(ii.invoice_date_created) = 7, iit.item_quantity * iit.item_price, 0)) AS vJuly,
SUM(IF(month(ii.invoice_date_created) = 8, iit.item_quantity * iit.item_price, 0)) AS vAugust,
SUM(IF(month(ii.invoice_date_created) = 9, iit.item_quantity * iit.item_price, 0)) AS vSeptember,
SUM(IF(month(ii.invoice_date_created) = 10, iit.item_quantity * iit.item_price, 0)) AS vOctober,
SUM(IF(month(ii.invoice_date_created) = 11, iit.item_quantity * iit.item_price, 0)) AS vNovember,
SUM(IF(month(ii.invoice_date_created) = 12, iit.item_quantity * iit.item_price, 0)) AS vDecember,
SUM(IF(month(ii.invoice_date_created) = 1, iit.item_quantity * iit.item_price, 0)) AS vJanuary,
SUM(IF(month(ii.invoice_date_created) = 2, iit.item_quantity * iit.item_price, 0)) AS vFebruary,
SUM(IF(month(ii.invoice_date_created) = 3, iit.item_quantity * iit.item_price, 0)) AS vMarch,
sum(iit.item_quantity * iit.item_price) as vtotal
from ip_invoices ii
join ip_invoice_items iit on iit.invoice_id = ii.invoice_id 
join ip_clients ic on ic.client_id = ii.client_id
join ip_products ip on ip.product_id = iit.item_product_id
where ii.invoice_status_id != 1 and ii.invoice_status_id != 5";
                
				if ($fa and $aa != ''){
			
					$sql.=" and (ii.invoice_date_created >= '".$fa."-04-01' and ii.invoice_date_created <= '".$aa."-03-31')";
				
				}
                if ($cname!= ''){
			
					$sql.="and ic.client_name = '".$cname."'";
				
				}
				$sql.=" group by client_name,product_description";
				$query = $this->db->query($sql);
				return $query->result();
				
				
	}

}