<?php
require_once("report.php");
class Summary_items extends Report
{
	function __construct()
	{
		parent::__construct();
	}
	
	public function getDataColumns()
	{		
		return array(array('data'=>lang('reports_item'), 'align'=> 'left'),array('data'=>lang('reports_item_number'), 'align'=> 'left'), array('data'=>lang('items_product_id'), 'align'=> 'left'),array('data'=>lang('reports_category'), 'align'=> 'left'),array('data'=>lang('reports_quantity'), 'align'=> 'center'),array('data'=>lang('reports_quantity_purchased'), 'align'=> 'left'), array('data'=>lang('reports_subtotal'), 'align'=> 'right'), array('data'=>lang('reports_total'), 'align'=> 'right'), array('data'=>lang('reports_tax'), 'align'=> 'right'),array('data'=>lang('reports_profit'), 'align'=> 'right'));
	}
	
	public function getData()
	{
		$this->db->select('items.name, items.item_number, items.product_id, items.category, location_items.quantity , sum(quantity_purchased) as quantity_purchased, sum(subtotal) as subtotal, sum(total) as total, sum(tax) as tax, sum(profit) as profit', false);
		$this->db->from('sales_items_temp');
		$this->db->join('items', 'sales_items_temp.item_id = items.item_id');
		$this->db->join('location_items', 'sales_items_temp.item_id = location_items.item_id');
		if ($this->params['category_search'] !='0')
		{
			$this->db->where('items.category', $this->params['category_search']);
		}
		if ($this->params['item_search'] !='')
		{
			$this->db->like('items.name', $this->params['item_search']);
		}
		
		if ($this->params['sale_type'] == 'sales')
		{
			$this->db->where('quantity_purchased > 0');
		}
		elseif ($this->params['sale_type'] == 'returns')
		{
			$this->db->where('quantity_purchased < 0');
		}
		$this->db->where($this->db->dbprefix('sales_items_temp').'.deleted', 0);
		$this->db->group_by('items.item_id');
		$this->db->order_by('name');

		//If we are exporting NOT exporting to excel make sure to use offset and limit
		if (isset($this->params['export_excel']) && !$this->params['export_excel'])
		{
			$this->db->limit($this->report_limit);
			$this->db->offset($this->params['offset']);
		}

		return $this->db->get()->result_array();		
	}
	
	function getTotalRows()
	{
		$this->db->select('COUNT(DISTINCT('.$this->db->dbprefix('sales_items_temp').'.item_id)) as item_count');
		$this->db->from('sales_items_temp');		
		$this->db->join('items', 'items.item_id = sales_items_temp.item_id');
		if ($this->params['category_search'] !='0')
		{
			$this->db->where('items.category', $this->params['category_search']);
		}
		if ($this->params['item_search'] !='')
		{
			$this->db->like('items.name', $this->params['item_search']);
		}
		
		if ($this->params['sale_type'] == 'sales')
		{
			$this->db->where('quantity_purchased > 0');
		}
		elseif ($this->params['sale_type'] == 'returns')
		{
			$this->db->where('quantity_purchased < 0');
		}
		
		$this->db->where($this->db->dbprefix('sales_items_temp').'.deleted', 0);
		
		$ret = $this->db->get()->row_array();
		return $ret['item_count'];
	}
	
	public function getSummaryData()
	{
		$this->db->select('sum(subtotal) as subtotal, sum(total) as total, sum(tax) as tax, sum(profit) as profit', false);
		$this->db->from('sales_items_temp');
		$this->db->join('items', 'sales_items_temp.item_id = items.item_id');
		
		if ($this->params['category_search'] !='0')
		{
			$this->db->where('items.category', $this->params['category_search']);
		}
		if ($this->params['item_search'] !='')
		{
			$this->db->like('items.name', $this->params['item_search']);
		}
		
		if ($this->params['sale_type'] == 'sales')
		{
			$this->db->where('quantity_purchased > 0');
		}
		elseif ($this->params['sale_type'] == 'returns')
		{
			$this->db->where('quantity_purchased < 0');
		}
		$this->db->where($this->db->dbprefix('sales_items_temp').'.deleted', 0);
		return $this->db->get()->row_array();
	}
        
        public function getStock_high ($location_id){
            
            $this->db->select('items.name, items.product_id, location_items.quantity,items.item_id');
            $this->db->from('location_items');
            $this->db->join('items', 'location_items.item_id = items.item_id');
            $this->db->where('location_items.location_id', $location_id);
            $this->db->where('location_items.quantity >', 0);
            $this->db->where('items.deleted =', 0);
            $this->db->order_by('location_items.quantity','desc');
            $this->db->limit(15);
            $resutl = $this->db->get()->result_array();
        
            return $resutl;
			
        }
        public function getStock ($location_id){
            
            $this->db->select('items.name, items.product_id, location_items.quantity,items.item_id');
            $this->db->from('location_items');
            $this->db->join('items', 'location_items.item_id = items.item_id');
            $this->db->where('location_items.location_id', $location_id);
            $this->db->where('location_items.quantity >', 0);
            $this->db->where('items.deleted =', 0);
            $this->db->order_by('location_items.quantity','Asc');
            $this->db->limit(15);
            $resutl = $this->db->get()->result_array();
        
            return $resutl;
			
        }
        
        function getStockOut($location_id){
            $this->db->select('items.name, location_items.quantity,items.item_id');
            $this->db->from('location_items');
            $this->db->join('items', 'location_items.item_id = items.item_id');
            $this->db->where('location_items.location_id', $location_id);
            $this->db->order_by('location_items.quantity','asc');
            $this->db->limit(15);
            $resutl = $this->db->get()->result_array();
            
            return $resutl;
        }
        public function getPyamentDue($location_id){
            $this->db->select('sales_payments.sale_id,sales_payments.payment_type,sales_payments.payment_amount,customers.location_id,sales.customer_id,people.first_name');
            $this->db->from('sales_payments');
            $this->db->join('sales', 'sales_payments.sale_id = sales.sale_id');
            $this->db->join('customers', 'sales.customer_id = customers.person_id');
            $this->db->join('people', 'customers.person_id = people.person_id');
            $this->db->where('sales_payments.payment_type', 'Credit Account');
            $this->db->where('customers.location_id', $location_id);
            

            $result = $this->db->get()->result_array();
            
            return $result;
        }
        
        public function getCollectedPyament($location_id){
            $this->db->select('sales_payments.sale_id,sales_payments.payment_type,sales_payments.payment_amount,customers.location_id,sales.customer_id,people.first_name');
            $this->db->from('sales_payments');
            $this->db->join('sales', 'sales_payments.sale_id = sales.sale_id');
            $this->db->join('customers', 'sales.customer_id = customers.person_id');
            $this->db->join('people', 'customers.person_id = people.person_id');
            $this->db->where('sales_payments.payment_type', 'Cash');
            $this->db->where('customers.location_id', $location_id);
            

            $result = $this->db->get()->result_array();
            
            return $result;
        }
}
?>