
<?php

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

class Reorderlevel_model extends CI_Model {

    public function __construct() {
        parent::__construct();
    }


    //GET PRODUCTS BY WAREHOUSE
     function getproductsbw($spcategory,$location){
           $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 = 'PRODUCT';
           if (!$sord) $sord = 'ASC';

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

           //Total Returned Rows Count
           $this->db->from('PRODUCTS');
           $count = $this->db->count_all_results();

           //Total Pages
           if( $count > 0 && $limit > 0) {
           $total_pages = ceil($count/$limit);
           } else {
           $total_pages = 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.NAME AS PRODUCT, STOCKCURRENT.UNITS AS STOCK, STOCKLEVEL.STOCKSECURITY AS MINIMUM, STOCKLEVEL.STOCKMAXIMUM AS MAXIMUM");
                if ($spcategory) {
                        $this->db->where("CATEGORIES.NAME", $spcategory);
                }
                $this->db->where('LOCATIONS.ID', $location);
                //$this->db->where('LOCATIONS.ID= "'.$location.'" AND 0 != SELECT COUNT(*) MATERIALS_UNTIS.MATERIAL WHERE PRODUCTS.ID=MATERIALS_UNTIS.MATERIAL');
                $this->db->join('STOCKLEVEL','PRODUCTS.ID = STOCKLEVEL.PRODUCT AND STOCKLEVEL.LOCATION = LOCATIONS.ID', 'LEFT');
                $this->db->join('STOCKCURRENT','PRODUCTS.ID = STOCKCURRENT.PRODUCT AND STOCKCURRENT.LOCATION = LOCATIONS.ID', 'LEFT');
                $this->db->join('CATEGORIES','CATEGORIES.ID = PRODUCTS.CATEGORY ', 'LEFT');
                $this->db->join('MATERIALS_UNITS','MATERIALS_UNITS.MATERIAL = PRODUCTS.ID ', 'LEFT');
                $this->db->order_by($sidx, $sord);
                $this->db->limit($limit, $start);
                $rows = $this->db->get("PRODUCTS, LOCATIONS")->result_array();

           //Format data
           $i=0;
           foreach($rows as $row){
           $data['rows'][$i]['id']=$i+1;
           $data['rows'][$i]['cell']=array($row['PRODUCT'],$row['STOCK'],$row['MINIMUM'],$row['MAXIMUM']);
           $i++;
           }
           return json_encode($data);


     }

    function getrecordlevel($locationsel='',$searchbycategory=''){
        //Fetch data
        $this->db->select("PRODUCTS.NAME AS PRODUCT,PRODUCTS.CODE,PRODUCTS.ID AS PRODUCTID,STOCKLEVEL.ID AS STOCKLEVELID, FORMAT(STOCKCURRENT.UNITS,3) AS STOCK, "
            ."STOCKLEVEL.STOCKSECURITY AS MINIMUM, STOCKLEVEL.STOCKMAXIMUM AS MAXIMUM");
        $this->db->join('STOCKLEVEL','PRODUCTS.ID = STOCKLEVEL.PRODUCT AND STOCKLEVEL.LOCATION = LOCATIONS.ID', 'LEFT');
        $this->db->join('STOCKCURRENT','PRODUCTS.ID = STOCKCURRENT.PRODUCT AND STOCKCURRENT.LOCATION = LOCATIONS.ID', 'LEFT');
        $this->db->join('CATEGORIES','CATEGORIES.ID = PRODUCTS.CATEGORY ', 'LEFT');
        $this->db->join('MATERIALS_UNITS','MATERIALS_UNITS.MATERIAL = PRODUCTS.ID ', 'LEFT');
        if($locationsel)
            $this->db->where('LOCATIONS.ID=', $locationsel);
        if($searchbycategory)
            $this->db->where('PRODUCTS.CATEGORY=', $searchbycategory);
        $this->db->group_by('PRODUCTS.ID');
        $rows = $this->db->get("PRODUCTS, LOCATIONS")->result_array();
        return $rows;
    }


   //GET PRODUCTS BY WAREHOUSE
    function getproductsbwtoib($location){
        $page = $this->input->post('page');
        $this->load->helper('docstatus');
        $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 = 'PRODUCT';
        if (!$sord) $sord = 'ASC';

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

        //Total Returned Rows Count
        $this->db->from('PRODUCTS');
        $count = $this->db->count_all_results();

        //Total Pages
        if( $count > 0 && $limit > 0) {
        $total_pages = ceil($count/$limit);
        } else {
        $total_pages = 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.ID,PRODUCTS.NAME AS PRODUCT, STOCKCURRENT.UNITS AS STOCK, STOCKLEVEL.STOCKSECURITY AS MINIMUM, STOCKLEVEL.STOCKMAXIMUM AS MAXIMUM,(PURCHASEORDERLINES.QUANTITY-PURCHASEORDERLINES.QUANTITYRECEIVED) AS POQTY,((STOCKLEVEL.STOCKSECURITY)-IFNULL((PURCHASEORDERLINES.QUANTITY-PURCHASEORDERLINES.QUANTITYRECEIVED),0)-(STOCKCURRENT.UNITS)) AS NEWPO");
        //$this->db->where('LOCATIONS.ID', $location);
        //$this->db->where('STOCKCURRENT.UNITS < STOCKLEVEL.STOCKSECURITY');
        $where = '(STOCKCURRENT.UNITS < STOCKLEVEL.STOCKSECURITY)';
         $this->db->where('CATEGORIES.PARENTID', '-1');
         $this->db->where('LOCATIONS.ID', $location);
        $this->db->where($where);
        $this->db->join('STOCKLEVEL','PRODUCTS.ID = STOCKLEVEL.PRODUCT AND STOCKLEVEL.LOCATION = LOCATIONS.ID', 'LEFT');
        $this->db->join('STOCKCURRENT','PRODUCTS.ID = STOCKCURRENT.PRODUCT AND STOCKCURRENT.LOCATION = LOCATIONS.ID', 'LEFT');
         $this->db->join('PURCHASEORDERLINES', 'PRODUCTS.ID=PURCHASEORDERLINES.PRODUCT AND PURCHASEORDERLINES.LINESTATUS = ' . LINE_ISSUED , 'LEFT');
        $this->db->join('CATEGORIES','CATEGORIES.ID = PRODUCTS.CATEGORY ', 'LEFT');

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

        //Format data
        $i=0;
        foreach($rows as $row){
        $data['rows'][$i]['id']=$i+1;
        $data['rows'][$i]['cell']=array($row['ID'],$row['PRODUCT'],$row['STOCK'],$row['MINIMUM'],$row['MAXIMUM'],$row['POQTY'],$row['NEWPO']);
        $i++;
        }
        return json_encode($data);


    }
     function addreorder($data)
     {
         return ($this->db->insert('STOCKLEVEL', $data));
     }

     function deletereorder($data) {
        return ($this->db->delete('STOCKLEVEL', $data)) ? true : false;
    }
}