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

class Stock_model extends CI_Model {

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

//GET STOCK DIARY
function getstockdiary() {
        //Fetch data
        $this->db->select("STOCKDIARY.ID, STOCKDIARY.DATENEW AS DATE,STOCKDIARY.REASON, STOCKDIARY.LOCATION, PRODUCTS.CODE, PRODUCTS.NAME AS PRODUCT, STOCKDIARY.UNITS, STOCKDIARY.PRICE, SUPPLIERS.NAME AS SUPPLIER,LOCATIONS.NAME AS LOCATION");
        $this->db->join('PRODUCTS','STOCKDIARY.PRODUCT = PRODUCTS.ID','LEFT');
        $this->db->join('SUPPLIERS','SUPPLIERS.ID = STOCKDIARY.SUPPLIER','LEFT');
        $this->db->join('LOCATIONS','LOCATIONS.ID = STOCKDIARY.LOCATION');
        $rows = $this->db->get("STOCKDIARY")->result_array();
//        echo $this->db->last_query();exit;
        return $rows;
    }
    
    // GET LOCATIONS
    function getLocations() {
        $this->db->select('ID, NAME');
        //$this->db->where('ID <> "0"');
        return $this->db->get('LOCATIONS')->result();
    }
    function addStockDiary($data) {
        return ($this->db->insert('STOCKDIARY', $data)) ? true : false;
    }

    function getpnamebyrefcode($rcode){
        $table_name = "PRODUCTS P";
        $this->db->select('P.NAME')->from($table_name);
        $this->db->where('P.REFERENCE', $rcode);

        return $this->db->get()->result();
    }
    function getproductcostandqoh($pname, $location){
        $table_name = "PRODUCTS P";
        $this->db->select('UNITS.NAME AS UOM, P.PRICEBUY AS COST, SC.UNITS AS QOH')->from($table_name);
        $this->db->where('P.NAME', $pname);
        //$this->db->join('STOCKDIARY AS SD', 'SD.PRODUCT = P.ID AND SD.REASON=1 AND SD.DATENEW=(SELECT MAX(DATENEW) FROM STOCKDIARY WHERE PRODUCT = P.ID)', 'LEFT');
        $this->db->join('STOCKCURRENT AS SC', 'SC.PRODUCT = P.ID AND SC.LOCATION LIKE ' . $location, 'LEFT');
        $this->db->join('MATERIALS_UNITS AS MU', 'MU.MATERIAL = P.ID', 'LEFT');
        $this->db->join('UNITS', 'UNITS.ID = MU.UNIT');
        return $this->db->get()->result();
    }
    function getproducttaxcat($pname){
        $table_name = "PRODUCTS";
        $this->db->select('TAXCAT,PRICEBUY')->from($table_name);
        $this->db->where('NAME', $pname);
        return $this->db->get()->result();
    }
    function getproducttax($taxcat){
        $table_name = "TAXES";
        $this->db->select('RATE, RATECASCADE, RATEORDER')->from($table_name);
        $this->db->where('CATEGORY', $taxcat);
        return $this->db->get()->result();
    }
    function getcount($taxcat){
        $table_name = "TAXES";
        $this->db->select('count(*) AS COUNT')->from($table_name);
        $this->db->where('CATEGORY', $taxcat);
        return $this->db->get()->result();
    }


   function getpobyid($id) {
        $this->db->from('PURCHASEORDERS AS P');
        $this->db->select('P.*, LOCATIONS.NAME AS LOCATION, P1.NAME AS CREATEDBY, P2.NAME AS MANAGEDBY, POI.INVOICEDATE, POI.INVOICENUMBER, POI.NOTES');
        $this->db->where('P.ID', $id);
        $this->db->join('LOCATIONS', 'P.LOCATION = LOCATIONS.ID', 'LEFT');
        $this->db->join('PURCHASEORDER_INVOICE AS POI', 'POI.POID = P.ID', 'LEFT');
        $this->db->join('PEOPLE AS P1', 'P.CREATEDBY = P1.ID', 'LEFT');
        $this->db->join('PEOPLE AS P2', 'P.MANAGEDBY = P2.ID', 'LEFT');
        return $this->db->get()->result();
    }
    
   function add($form_data)
    {
            $this->db->insert('STOCKDIARY', $form_data);
		if ($this->db->affected_rows() == '1')
		{
			return TRUE;
		}
        return FALSE;
     }
    function add2($form_data2){
        $this->db->insert('MATERIALS_UNITS', $form_data2);
        if ($this->db->affected_rows() == '1') {
            return TRUE;
        }
        return FALSE;
    }
    
      function getproductautocomplete($term) {
        $this->db->select('ID, NAME');
        return $this->db->get("PRODUCTS")->result();
    }
   
     function getsupplierautocomplete($term) {
            $this->db->select('S.ID, S.NAME');
            $where = "S.NAME LIKE '". $term . "%'";
            $this->db->where($where);
            return $this->db->get("SUPPLIERS S")->result();
    } 
    function getwarehouseautocomplete($term) {
            $this->db->select('L.ID, L.NAME');
            $where = "L.NAME LIKE '". $term . "%'";
            $this->db->where($where);
            return $this->db->get("LOCATIONS L")->result();
    } 
   function itemsautocomplete($term) {
            $this->db->select('P.ID, P.NAME');
            $where = "P.NAME LIKE '". $term . "%'";
            $this->db->where($where);
            return $this->db->get("PRODUCTS P")->result();
    } 
    function delete($id) {
        return ($this->db->delete('STOCKDIARY', array('ID' => $id))) ? true : false;
     }
     function delete2($id) {
        return ($this->db->delete('MATERIALS_UNITS', array('MATERIAL' => $id))) ? true : false;
     }
     
      function getPid($pname)
     {
         $this->db->select('*');
         $this->db->where('NAME', $pname);
         return $this->db->get("PRODUCTS")->result();
     }

     function getMaterialsProd($id)
     {
         $this->db->select('PM.MATERIAL, P.PRICEBUY, PM.AMOUNT UNITS');
         $this->db->where('PM.PRODUCT', $id);
         $this->db->join('PRODUCTS P', 'P.ID=PM.MATERIAL');
        
        return $this->db->get("PRODUCTS_MAT PM")->result();
         
     }
     
     function getMaterialsProduction($id)
     {
         $this->db->select('PM.MATERIAL, P.PRICEBUY, PM.AMOUNT UNITS');
         $this->db->where('PM.PRODUCT', $id);
         $this->db->where('P.ITEMTYPE!=', 1,FALSE);
         $this->db->join('PRODUCTS P', 'P.ID=PM.MATERIAL');
            return $this->db->get("PRODUCTS_MAT PM")->result();
         
     }
     
     // 3.1
     function getproductstock($id='',$locationid='') {
        $sql = "SELECT LOCATIONS.NAME AS LOCATION,LOCATIONS.ID AS LOCATIONID, SUM(STOCKCURRENT.UNITS) AS UNITS, STOCKLEVEL.STOCKSECURITY, "
                . "STOCKLEVEL.STOCKMAXIMUM FROM LOCATIONS " .
        " LEFT JOIN STOCKCURRENT ON STOCKCURRENT.LOCATION = LOCATIONS.ID AND STOCKCURRENT.PRODUCT='" . $id . "' " .
        " LEFT JOIN STOCKLEVEL ON STOCKLEVEL.LOCATION = LOCATIONS.ID AND STOCKLEVEL.PRODUCT='" . $id . "'";
        if($locationid)
            $sql = $sql." WHERE LOCATIONS.ID='".$locationid."'";
        $sql = $sql." GROUP BY LOCATIONS.ID ORDER BY LOCATIONS.NAME";
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    function deletereorderstock($id) {
        return ($this->db->delete('STOCKLEVEL', array('PRODUCT' => $id))) ? true : false;
    }
    function addreorderstock($form_data2){
        $this->db->insert('STOCKLEVEL', $form_data2);
        if ($this->db->affected_rows() == '1') {
            return TRUE;
        }
        return FALSE;
    }
}

 
