<?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_Items
 */
class Mdl_Items extends Response_Model
{
    public $table = 'ip_invoice_items';
    public $primary_key = 'ip_invoice_items.item_id';
    public $date_created_field = 'item_date_added';
	
	public function today()
	{
		$sql="SELECT count(inv.invoice_id) as today,ifnull(sum(it.item_quantity),0) as sum_of_quan,ifnull(sum(it.item_quantity * it.item_price),0) as sum_of_today FROM ip_invoice_items it 
join ip_invoices inv on inv.invoice_id = it.invoice_id
where inv.invoice_status_id != 1 and date(invoice_date_created) = CURRENT_DATE;";
        $query = $this->db->query($sql);
        return $query->result();	
		
	}
	public function month()
	{
        
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 (SELECT count(distinct inv.invoice_id) FROM ip_invoices inv 
join ip_invoice_custom icf on icf.invoice_id = inv.invoice_id 
where inv.invoice_status_id != 1 and icf.invoice_custom_fieldid = '8' and MONTH(inv.invoice_date_created) = MONTH(CURRENT_DATE()) and 
(inv.invoice_date_created >= '".$fa."-04-01' and inv.invoice_date_created <= '".$aa."-03-31')  and creditinvoice_parent_id is null) as months,ifnull(sum(it.item_quantity),0) as sum_of_quantity,ifnull(sum(if(it.item_quantity=0,1,it.item_quantity) * it.item_price),0) as sum_of_month FROM ip_invoice_items it 
join ip_invoices inv on inv.invoice_id = it.invoice_id
join ip_invoice_custom icf on icf.invoice_id = inv.invoice_id 
where inv.invoice_status_id != 1 and  icf.invoice_custom_fieldid = '8' and MONTH(inv.invoice_date_created) = MONTH(CURRENT_DATE()) and 
(inv.invoice_date_created >= '".$fa."-04-01' and inv.invoice_date_created <= '".$aa."-03-31');";
        $query = $this->db->query($sql);
        return $query->result();	
		
	}
	public function year()
	{
		 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 (SELECT count(distinct inv.invoice_id) FROM ip_invoices inv 
join ip_invoice_custom icf on icf.invoice_id = inv.invoice_id 
where inv.invoice_status_id != 1 and icf.invoice_custom_fieldid = '8' and 
(inv.invoice_date_created >= '".$fa."-04-01' and inv.invoice_date_created <= '".$aa."-03-31')  and creditinvoice_parent_id is null) as year,ifnull(sum(iit.item_quantity),0) as sum_of_quantity,ifnull(sum(if(iit.item_quantity=0,1,iit.item_quantity) * iit.item_price),0) as sum_of_year 
FROM ip_invoice_items iit 
join ip_invoices ii on ii.invoice_id = iit.invoice_id
join ip_invoice_custom icf on icf.invoice_id = ii.invoice_id 
where ii.invoice_status_id != 1 and icf.invoice_custom_fieldid = '8' and (ii.invoice_date_created >= '".$fa."-04-01' and ii.invoice_date_created <= '".$aa."-03-31');";
        $query = $this->db->query($sql);
        return $query->result();	
		
	}
		
		 public function vehicle()
	{
		$sql="select iit.invoice_id as invoice_id,iic.invoice_custom_fieldvalue as invoice_custom_fieldvalue from ip_invoice_custom iic
join ip_invoice_items iit on iit.invoice_id = iic.invoice_id where invoice_custom_fieldid = '8'";
        $query = $this->db->query($sql);
        return $query->result();	
		
	}
	 public function item_name()
	{
		$sql="SELECT product_description as item_name FROM ip_products";
        $query = $this->db->query($sql);
        return $query->result();	
		
	}
	 public function year_wise($a,$b)
	{
		$sql="SELECT inv.invoice_date_created as months,sum(it.item_quantity) as quantity,sum(it.item_quantity * it.item_price) as value FROM ip_invoice_items it
join ip_invoices inv on inv.invoice_id = it.invoice_id where inv.invoice_status_id != 1 and (inv.invoice_date_created) >= '$a-04-01' and date(inv.invoice_date_created) <= '$b-03-31'
group by month(months)
ORDER BY month(months);";
        $query = $this->db->query($sql);
        return $query->result();	
		
	}
	 public function month_wise($new)
	{
		//echo $new;
		$month= date("m",strtotime($new));
	    $year = date("Y",strtotime($new));
		//echo $month;//
		//echo $year;die();
		$sql="select clt.client_id as client_id,clt.client_name as client_name,ip.product_description as product_name,sum(item_quantity) as quantity,sum(item_quantity * item_price) as value
from ip_clients clt
join ip_invoices inv on inv.client_id = clt.client_id
join ip_invoice_items items on items.invoice_id = inv.invoice_id
join ip_products ip on ip.product_id = items.item_product_id
where inv.invoice_status_id != 1 and month(inv.invoice_date_created) = ? and year(inv.invoice_date_created) = ?
group by client_name,product_name";
        $query = $this->db->query($sql,array($month,$year));
      //print_r($query->result());
	  return $query->result();	
		
	}
	public function year_wise_total($a,$b)
	{
		
		$sql="select clt.client_id as client_id,clt.client_name as client_name,ip.product_description as product_name,sum(item_quantity) as quantity,sum(item_quantity * item_price) as value
from ip_clients clt
join ip_invoices inv on inv.client_id = clt.client_id
join ip_invoice_items items on items.invoice_id = inv.invoice_id
join ip_products ip on ip.product_id = items.item_product_id
where inv.invoice_status_id != 1 and (inv.invoice_date_created) >= '$a-04-01' and date(inv.invoice_date_created) <= '$b-03-31'
group by client_name,product_name";
        $query = $this->db->query($sql,array($a,$b));
      //print_r($a);
	  return $query->result();	
		
	}
	public function fiscalYear()
    {
	  $sql="SELECT
   CASE WHEN MONTH(invoice_date_created)>=4 THEN
          concat(YEAR(invoice_date_created), '-',YEAR(invoice_date_created)+1)
   ELSE concat(YEAR(invoice_date_created)-1,'-', YEAR(invoice_date_created)) END AS financial_year
 FROM ip_invoices
GROUP BY financial_year ";	
      $query = $this->db->query($sql);
      //print_r($query->result());
	  return $query->result();	  
    }
	public function selectmonth($a,$b)
    {
	  $sql="SELECT
  monthname(invoice_date_created) as months
FROM ip_invoices 
where case
	when (month(invoice_date_created) >=4) then
		 (YEAR(invoice_date_created) = $a and MONTH(invoice_date_created) >= 4 ) or ( YEAR(        invoice_date_created) = $b+1 and MONTH(invoice_date_created) <= 3  )
	else 
		 (YEAR(invoice_date_created) = $a-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = $b and MONTH(invoice_date_created) <= 3  )
end	
GROUP BY months
ORDER BY FIELD(months,
'December', 'November', 'October',
'September', 'August', 'July',
'June', 'May', 'April',
'March', 'February', 'January') DESC, months ASC";	
      $query = $this->db->query($sql,array($a,$b));
     
	  return $query->result();	  
    }
	public function customer()
	{
		
		$sql="SELECT client_name,client_id FROM ip_clients
order by client_name;";
        $query = $this->db->query($sql);
      //print_r($a);
	  return $query->result();	
		
	}
		
	
    public function default_select()
    {
        $this->db->select('ip_invoice_item_amounts.*, ip_products.*, ip_invoice_items.*,
            Sgst_item_tax_rates.tax_rate_percent AS Sgst_item_tax_rate_percent,
			Sgst_item_tax_rates.tax_rate_name AS Sgst_item_tax_rate_name,(((ip_invoice_items.item_quantity * ip_invoice_items.item_price) - (ip_invoice_items.item_quantity * ip_invoice_items.item_discount_amount)) * Sgst_item_tax_rates.tax_rate_percent /100) AS Sgst_item_tax_rate_amount,Cgst_item_tax_rates.tax_rate_percent AS Cgst_item_tax_rate_percent,
            Cgst_item_tax_rates.tax_rate_name AS Cgst_item_tax_rate_name,(((ip_invoice_items.item_quantity * ip_invoice_items.item_price) - (ip_invoice_items.item_quantity * ip_invoice_items.item_discount_amount)) * Cgst_item_tax_rates.tax_rate_percent /100) AS Cgst_item_tax_rate_amount,Igst_item_tax_rates.tax_rate_percent AS Igst_item_tax_rate_percent,
            Igst_item_tax_rates.tax_rate_name AS Igst_item_tax_rate_name,(((ip_invoice_items.item_quantity * ip_invoice_items.item_price) - (ip_invoice_items.item_quantity * ip_invoice_items.item_discount_amount)) * Igst_item_tax_rates.tax_rate_percent /100) AS Igst_item_tax_rate_amount');
    }

    public function default_order_by()
    {
        $this->db->order_by('ip_invoice_items.item_order');
    }

    public function default_join()
    {
        $this->db->join('ip_invoice_item_amounts', 'ip_invoice_item_amounts.item_id = ip_invoice_items.item_id', 'left');
        $this->db->join('ip_tax_rates AS Sgst_item_tax_rates', 'Sgst_item_tax_rates.tax_rate_id = ip_invoice_items.Sgst_item_tax_rate_id', 'left');
        $this->db->join('ip_tax_rates AS Cgst_item_tax_rates', 'Cgst_item_tax_rates.tax_rate_id = ip_invoice_items.Cgst_item_tax_rate_id', 'left');
        $this->db->join('ip_tax_rates AS Igst_item_tax_rates', 'Igst_item_tax_rates.tax_rate_id = ip_invoice_items.Igst_item_tax_rate_id', 'left');
        $this->db->join('ip_products', 'ip_products.product_id = ip_invoice_items.item_product_id', 'left');
		
    }

    /**
     * @return array
     */
    public function validation_rules()
    {
        return array(
            'invoice_id' => array(
                'field' => 'invoice_id',
                'label' => trans('invoice'),
                'rules' => 'required'
            ),
            'item_sku' => array(
                'field' => 'item_sku',
                'label' => trans('item_sku'),
                'rules' => 'required|unique'
            ),
            'item_name' => array(
                'field' => 'item_name',
                'label' => trans('item_name'),
                'rules' => 'required'
            ),
            'item_description' => array(
                'field' => 'item_description',
                'label' => trans('description')
            ),
            'item_quantity' => array(
                'field' => 'item_quantity',
                'label' => trans('quantity'),
                'rules' => 'required'
            ),
            'item_price' => array(
                'field' => 'item_price',
                'label' => trans('price'),
                'rules' => 'required'
            ),
            'item_tax_rate_id' => array(
                'field' => 'item_tax_rate_id',
                'label' => trans('item_tax_rate')
            ),
            'item_product_id' => array(
                'field' => 'item_product_id',
                'label' => trans('original_product')
            ),
            'item_date' => array(
                'field' => 'item_date',
                'label' => trans('item_date')
            )
        );
    }
	function get_product()
    {
            
            //$this->db->select('product_id,product_description');

            $this->db->join('ip_units unit','unit.unit_id = ip_products.unit_id');
            $this->db->from('ip_products');
            $query = $this->db->get();
            //print_r($query->result());

          //  print_r( $this->db->last_query());
            return $query->result();
             
    }


    function get_tax()
    {
            
            $this->db->select('*');
            $this->db->from('ip_tax_rates');
            $query = $this->db->get();
            //print_r($query->result());
            //print_r( $this->db->last_query());
            return $query->result();
             
    }

    /**
     * @param null $id
     * @param null $db_array
     * @return int|null
     */
    public function save($id = null, $db_array = null)
    {

        $id = parent::save($id, $db_array);

        $this->load->model('invoices/mdl_item_amounts');

        $this->mdl_item_amounts->calculate($id);

        $this->load->model('invoices/mdl_invoice_amounts');

        if (is_object($db_array) && isset($db_array->invoice_id)) {
            $this->mdl_invoice_amounts->calculate($db_array->invoice_id);
        } elseif (is_array($db_array) && isset($db_array['invoice_id'])) {
            $this->mdl_invoice_amounts->calculate($db_array['invoice_id']);
        }

        return $id;
    }

    /**
     * @param int $item_id
     */
    public function delete($item_id)
    {
        // Get item:
        // the invoice id is needed to recalculate invoice amounts
        // and the task id to update status if the item refers a task
        $query = $this->db->get_where($this->table,
            array('item_id' => $item_id));
        if ($query->num_rows() == 0) {
            return null;
        }

        $row = $query->row();
        $invoice_id = $row->invoice_id;

        // Delete the item
        parent::delete($item_id);

        // Delete the item amounts
        $this->db->where('item_id', $item_id);
        $this->db->delete('ip_invoice_item_amounts');

        // Recalculate invoice amounts
        $this->load->model('invoices/mdl_invoice_amounts');
        $this->mdl_invoice_amounts->calculate($invoice_id);
        return $row;
    }
	function get_quotes($client_id)
    {

        $this->db->select('ip_quotes.quote_id,quote_number,quote_pono');
        // $this->db->join('ip_units unit','unit.unit_id = ip_products.unit_id');
        $this->db->from('ip_quotes');
        $this->db->join('ip_quote_items','ip_quote_items.quote_id = ip_quotes.quote_id');
        $this->db->where('client_id ',$client_id);
        $this->db->where('item_status ',1);
        $query = $this->db->get();
    // print_r( $this->db->last_query());
        return $query->result();
             
    }
	 public function Check_invoice($invoice_id,$item_id)
    {
        $this->db->select('*');
        $this->db->from('ip_invoice_items');
        
         $this->db->where('item_product_id',$item_id);
         $this->db->where('invoice_id',$invoice_id);


        $query = $this->db->get();
    //print_r( $this->db->last_query());
        return $query->result();
    }
	public function GetMaterialCode($itemproductid)
    {
          $this->db->select('product_sku');
          $this->db->from('ip_products');
          $this->db->where('product_id',$itemproductid);
         $query = $this->db->get();
       // print_r( $this->db->last_query());
        return $query->result();
         
    }
	 public function GetClientID($invoice_id)
    {
        $this->db->select('client_id');
          $this->db->from('ip_invoices');
          $this->db->where('invoice_id',$invoice_id);
         $query = $this->db->get();
       // print_r( $this->db->last_query());
        return $query->result();
         
    }
	 public function GetTransactionType($invoice_id)
    {

        $this->db->select('creditinvoice_parent_id');
        $this->db->from('ip_invoices');
        $this->db->where('invoice_id',$invoice_id);

         $query = $this->db->get();
        return $query->result();

    }


    // public function CheckCredit($invoice_id)
    // {

        // $this->db->select('Ref_No');
        // $this->db->from('T_Material_stock_history');
        // $this->db->where('Ref_No',$invoice_id);

         // $query = $this->db->get();


         // //print_r($this->db->last_query());die();
        // return $query->result();
    // }
	
}