<?php

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

class Currentinventory_model extends CI_Model {

    public function __construct() {
        parent::__construct();
    }
    //GET STOCK DIARY
function getcurrentstockdetails($location, $mcategory, $products ){
    $page = $this->input->post('page');
    $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
    $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
    $sord = $this->input->post('sord'); // get the direction

    //Default values
    if (!$sidx) $sidx = 'REFERENCE';
    if (!$sord) $sord = 'desc';

    if (!$page) $page = 1;
    if (!$limit) $limit = 25;

    //Total Returned Rows Count
    $this->db->from('STOCKCURRENT');
        if ($location) {
            $this->db->where("STOCKCURRENT.LOCATION", $location);
        }
        if ($mcategory) {
            $this->db->where("CATEGORIES.NAME", $mcategory);
        }
        if ($products) {
            $this->db->where("PRODUCTS.NAME", $products);
        }

    $this->db->select("PRODUCTS.REFERENCE,LOCATIONS.NAME AS LOCATION, PRODUCTS.NAME AS PRODUCT,STOCKCURRENT.UNITS,UNITS.NAME AS UOM,PRODUCTS.PRICEBUY AS UNITPRICE,(STOCKCURRENT.UNITS*PRODUCTS.PRICEBUY) AS LINETOTAL,STOCKLEVEL.STOCKSECURITY, STOCKLEVEL.STOCKMAXIMUM");
    //$this->db->where('CATEGORIES.PARENTID', '-1');
        if ($location) {
            $this->db->where("STOCKCURRENT.LOCATION", $location);
        }
    $this->db->join('PRODUCTS', 'STOCKCURRENT.PRODUCT=PRODUCTS.ID');
    $this->db->join('CATEGORIES', 'PRODUCTS.CATEGORY = CATEGORIES.ID');
    $this->db->join('LOCATIONS', 'STOCKCURRENT.LOCATION=LOCATIONS.ID');
    $this->db->group_by("STOCKCURRENT.LOCATION, STOCKCURRENT.PRODUCT");
    $count = $this->db->count_all_results();

    //Total Pages
        if ($count > 0 && $limit > 0) {
            $total_pages = ceil($count / $limit);
            $start = $limit * $page - $limit;
        } else {
            $total_pages = 0;
            $start=0;
        }

    //Check requested page
    if ($page > $total_pages) $page=$total_pages;

    //Set start record number
    // $start = $limit*$page - $limit;

    //Format data for jqGrid output
    $data['page'] = "".$page."";
    $data['total'] = "".$total_pages."";
    $data['records'] = "".$count."";

    //Fetch data
    $this->db->select("PRODUCTS.REFERENCE,LOCATIONS.NAME AS LOCATION,PRODUCTS.NAME AS PRODUCT,SUM(STOCKCURRENT.UNITS) AS TOTALUNITS,UNITS.NAME AS UOM,PRODUCTS.PRICEBUY AS UNITPRICE,SUM(STOCKCURRENT.UNITS)*PRODUCTS.PRICEBUY AS LINETOTAL,STOCKLEVEL.STOCKSECURITY, STOCKLEVEL.STOCKMAXIMUM");
        if ($location) {
            $this->db->where("STOCKCURRENT.LOCATION", $location);
        }
         if ($mcategory) {
            $this->db->where("CATEGORIES.NAME", $mcategory);
        }
         if ($products) {
            $this->db->where("PRODUCTS.NAME", $products);
        }
         //$this->db->where('CATEGORIES.PARENTID', '-1');
                
            $this->db->join('PRODUCTS', 'STOCKCURRENT.PRODUCT=PRODUCTS.ID','LEFT');
            $this->db->join('STOCKLEVEL','PRODUCTS.ID = STOCKLEVEL.PRODUCT AND STOCKCURRENT.LOCATION = STOCKLEVEL.LOCATION','LEFT');
            $this->db->join('CATEGORIES', 'PRODUCTS.CATEGORY = CATEGORIES.ID','LEFT');
            $this->db->join('LOCATIONS', 'STOCKCURRENT.LOCATION=LOCATIONS.ID','LEFT');
            $this->db->join('PRODUCTGROUPS_PROD', 'PRODUCTGROUPS_PROD.PRODUCT=PRODUCTS.ID','LEFT');
            $this->db->join('PRODUCTGROUPS', 'PRODUCTGROUPS.ID=PRODUCTGROUPS_PROD.PRODUCTGROUP','LEFT');
            $this->db->join('MATERIALS_UNITS','MATERIALS_UNITS.MATERIAL = PRODUCTS.ID','LEFT');
            $this->db->join('UNITS','UNITS.ID = MATERIALS_UNITS.UNIT','LEFT');
            $this->db->group_by("STOCKCURRENT.LOCATION, STOCKCURRENT.PRODUCT");

        $this->db->order_by($sidx, $sord);
        $this->db->limit($limit, $start);
        $rows = $this->db->get("STOCKCURRENT")->result_array();

            //Format data
            $i=0;
            foreach($rows as $row){
                $data['rows'][$i]['id']=$i+1;
                $data['rows'][$i]['cell']=array($row['REFERENCE'],$row['LOCATION'],$row['PRODUCT'],$row['TOTALUNITS'],$row['UOM'],$row['UNITPRICE'],$row['LINETOTAL'],$row['STOCKSECURITY'],$row['STOCKMAXIMUM']);
                $i++;
            }
       return json_encode($data);
    }



function getcurrentinventory(){
   
    //Fetch data
    $this->db->select("PRODUCTS.REFERENCE,LOCATIONS.NAME AS LOCATION,"
        . "PRODUCTS.NAME AS PRODUCT,PRODUCTS.CODE,SUM(STOCKCURRENT.UNITS) AS TOTALUNITS,UNITS.NAME AS UOM,"
        . "PRODUCTS.PRICEBUY AS UNITPRICE,SUM(STOCKCURRENT.UNITS)*PRODUCTS.PRICEBUY AS LINETOTAL,"
        . "STOCKLEVEL.STOCKSECURITY, STOCKLEVEL.STOCKMAXIMUM,CATEGORIES.NAME AS CATEGORYNAME");
    $this->db->join('PRODUCTS', 'STOCKCURRENT.PRODUCT=PRODUCTS.ID','LEFT');
    $this->db->join('STOCKLEVEL','PRODUCTS.ID = STOCKLEVEL.PRODUCT AND STOCKCURRENT.LOCATION = STOCKLEVEL.LOCATION','LEFT');
    $this->db->join('CATEGORIES', 'PRODUCTS.CATEGORY = CATEGORIES.ID','LEFT');
    $this->db->join('LOCATIONS', 'STOCKCURRENT.LOCATION=LOCATIONS.ID','LEFT');
    $this->db->join('PRODUCTGROUPS_PROD', 'PRODUCTGROUPS_PROD.PRODUCT=PRODUCTS.ID','LEFT');
    $this->db->join('PRODUCTGROUPS', 'PRODUCTGROUPS.ID=PRODUCTGROUPS_PROD.PRODUCTGROUP','LEFT');
    $this->db->join('MATERIALS_UNITS','MATERIALS_UNITS.MATERIAL = PRODUCTS.ID','LEFT');
    $this->db->join('UNITS','UNITS.ID = MATERIALS_UNITS.UNIT','LEFT');
    $this->db->group_by("STOCKCURRENT.LOCATION, STOCKCURRENT.PRODUCT");

        $rows = $this->db->get("STOCKCURRENT")->result_array();
//        echo $this->db->last_query();exit;
       return $rows;
    }


    public function getcurrentstockdetailsprint($location,$mcategory,$products) {
        $table_name = "STOCKCURRENT";
        $this->db->select("PRODUCTS.REFERENCE,PRODUCTS.NAME AS PRODUCT,STOCKCURRENT.UNITS,UNITS.NAME AS UOM,PRODUCTS.PRICEBUY AS UNITPRICE,(STOCKCURRENT.UNITS*PRODUCTS.PRICEBUY) AS LINETOTAL,STOCKLEVEL.STOCKSECURITY, STOCKLEVEL.STOCKMAXIMUM")->from($table_name);

            if ($location) {
                $this->db->where("STOCKCURRENT.LOCATION", $location);
            }
            if ($mcategory) {
                $this->db->where("CATEGORIES.NAME", $mcategory);
            }
            if ($products) {
                $this->db->where("PRODUCTS.NAME", $products);
            }
            //$this->db->where('CATEGORIES.PARENTID', '-1');

            /*$this->db->join('PRODUCTS', 'STOCKCURRENT.PRODUCT=PRODUCTS.ID');
            $this->db->join('STOCKLEVEL','PRODUCTS.ID = STOCKLEVEL.PRODUCT AND STOCKCURRENT.LOCATION = STOCKLEVEL.LOCATION','LEFT');
            $this->db->join('CATEGORIES', 'PRODUCTS.CATEGORY = CATEGORIES.ID');
            $this->db->join('LOCATxIONS', 'STOCKCURRENT.LOCATION=LOCATIONS.ID');
            $this->db->join('PRODUCTGROUPS_PROD', 'PRODUCTGROUPS_PROD.PRODUCT=PRODUCTS.ID','LEFT');
            $this->db->join('PRODUCTGROUPS', 'PRODUCTGROUPS.ID=PRODUCTGROUPS_PROD.PRODUCTGROUP','LEFT');
            $this->db->join('MATERIALS_UNITS','MATERIALS_UNITS.MATERIAL = PRODUCTS.ID','LEFT');
            $this->db->join('UNITS','UNITS.ID = MATERIALS_UNITS.UNIT');*/
            $this->db->join('PRODUCTS', 'STOCKCURRENT.PRODUCT=PRODUCTS.ID','LEFT');
            $this->db->join('STOCKLEVEL','PRODUCTS.ID = STOCKLEVEL.PRODUCT AND STOCKCURRENT.LOCATION = STOCKLEVEL.LOCATION','LEFT');
            $this->db->join('CATEGORIES', 'PRODUCTS.CATEGORY = CATEGORIES.ID','LEFT');
            $this->db->join('LOCATIONS', 'STOCKCURRENT.LOCATION=LOCATIONS.ID','LEFT');
            $this->db->join('PRODUCTGROUPS_PROD', 'PRODUCTGROUPS_PROD.PRODUCT=PRODUCTS.ID','LEFT');
            $this->db->join('PRODUCTGROUPS', 'PRODUCTGROUPS.ID=PRODUCTGROUPS_PROD.PRODUCTGROUP','LEFT');
            $this->db->join('MATERIALS_UNITS','MATERIALS_UNITS.MATERIAL = PRODUCTS.ID','LEFT');
            $this->db->join('UNITS','UNITS.ID = MATERIALS_UNITS.UNIT','LEFT');

            $this->db->group_by("PRODUCTS.ID");
        return $this->db->get()->result();
    }

}