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

class Materials_model extends CI_Model {
	public function __construct() {
		parent::__construct();
	}

  function getmaterials()
{

        $this->db->select('P.ID, P.REFERENCE, P.NAME,P.PRICEBUY, C1.NAME AS CATEGORY, P.PRICEBUY,T.NAME AS TAXCATEGORY,MU.AMOUNT,U.NAME AS UOM,P.CATEGORY AS CATID,P.TAXCAT AS TAXCATID,MU.UNIT AS UOM_ID');
        $this->db->from('PRODUCTS AS P, CATEGORIES AS C1');
        $where='P.CATEGORY = C1.ID';
        $this->db->where($where);

        $this->db->join('CATEGORIES AS C2', 'C2.ID = C1.PARENTID', 'LEFT');
        $this->db->join('TAXCATEGORIES AS T', 'T.ID = P.TAXCAT', 'LEFT');
        $this->db->join('MATERIALS_UNITS AS MU' , 'MU.MATERIAL = P.ID', 'LEFT');
        $this->db->join('UNITS AS U' , 'U.ID = MU.UNIT', 'LEFT');
       
        $rows = $this->db->get()->result_array();
       // echo $this->db->last_query();
        return $rows;
    }

    function editmaterials($pid)
{
  
        $this->db->select('P.ID, P.REFERENCE, P.NAME,P.PRICEBUY, C1.NAME AS CATEGORY, P.PRICEBUY,T.NAME AS TAXCATEGORY,MU.AMOUNT,U.NAME AS UOM,P.CATEGORY AS CATID,P.TAXCAT AS TAXCATID,MU.UNIT AS UOM_ID');
        $this->db->from('PRODUCTS AS P, CATEGORIES AS C1');
        $where='P.CATEGORY = C1.ID AND P.ID = "'.$pid.'"';
        $this->db->where($where);

        $this->db->join('CATEGORIES AS C2', 'C2.ID = C1.PARENTID', 'LEFT');
        $this->db->join('TAXCATEGORIES AS T', 'T.ID = P.TAXCAT', 'LEFT');
        $this->db->join('MATERIALS_UNITS AS MU' , 'MU.MATERIAL = P.ID', 'LEFT');
        $this->db->join('UNITS AS U' , 'U.ID = MU.UNIT', 'LEFT');
       
        $rows = $this->db->get()->result_array();
      
        return $rows;
    }


    function getmaterialbyid($id) {
            $this->db->select('PRODUCTS.ID, PRODUCTS.REFERENCE, PRODUCTS.NAME, PRODUCTS.CATEGORY, PRODUCTS.TAXCAT, MATERIALS_UNITS.PRICEBUY, MATERIALS_UNITS.AMOUNT, MATERIALS_UNITS.UNIT');
		$this->db->where('PRODUCTS.ID', $id);
                $this->db->join('MATERIALS_UNITS', 'PRODUCTS.ID = MATERIALS_UNITS.MATERIAL', 'LEFT');
                $this->db->join('UNITS', 'MATERIALS_UNITS.UNIT = MATERIALS_UNITS.MATERIAL', 'LEFT');
		return $this->db->get('PRODUCTS')->result();
     }
   
     function deletematerial($id) {
            $this->db->delete('MATERIALS_UNITS', array('MATERIAL'=>$id));
             $this->db->delete('PRODUCTS_CAT', array('PRODUCT'=>$id));
		return ($this->db->delete('PRODUCTS', array('ID'=>$id))) ? true : false;
     }
        
      // GET CATEGORY DROPDOWN
      public function categorydropdown() {
            $this->db->select('ID, NAME');
            $this->db->where('PARENTID', -1);
            return $this->db->get('CATEGORIES')->result();
      }
      // GET TAXCATEGORY DROPDOWN
      public function taxcategorydropdown() {
            $this->db->select('ID, NAME');
            return $this->db->get('TAXCATEGORIES')->result();
      }
        function gettaxcat() {
		$this->db->select('ID, NAME');
                $this->db->where('ID <> "-1" ');

		return $this->db->get('TAXCATEGORIES')->result();
	}
        
        function getlinesbycategory($datefrom, $dateto, $product) {
        $this->load->helper('docstatus');
        $table_name = "MATERIALREQUESTLINES";
        $this->db->select("MATERIALREQUESTS.REQUIREDON, CATEGORIES.NAME AS CATEGORY, PRODUCTS.ID, PRODUCTS.NAME AS PRODUCT, SUM(MATERIALREQUESTLINES.APPROVEDQUANTITY) AS APPROVEDQUANTITY, SC.UNITS AS QOH, UNITS.NAME AS UOM, POL.POQTY,  (SC.UNITS+IFNULL(POL.POQTY,0))-SUM(MATERIALREQUESTLINES.APPROVEDQUANTITY) AS QTYSTS,PRODUCTS.PRICEBUY * SUM(MATERIALREQUESTLINES.QUANTITY - MATERIALREQUESTLINES.QUANTITYDELIVERED) AS TOTAL")->from($table_name);

        if ($datefrom && $dateto) {
            $this->db->where("REQUIREDON >=", $datefrom);
            $this->db->where("REQUIREDON <=", $dateto);
        } else if ($datefrom) {
            $this->db->where("REQUIREDON", $datefrom);
        }
        if ($product) {
            $this->db->where("MATERIALREQUESTLINES.PRODUCT", $product);
        }
        
        $where = '(MATERIALREQUESTLINES.LINESTATUS = 4 OR MATERIALREQUESTLINES.LINESTATUS = ' . LINE_APPROVED . ')';
        $this->db->where($where);
        $this->db->join('MATERIALREQUESTS', 'MATERIALREQUESTLINES.MATERIALREQUEST=MATERIALREQUESTS.ID', 'LEFT');
        $this->db->join('PRODUCTS', 'PRODUCTS.ID = MATERIALREQUESTLINES.PRODUCT', 'LEFT');
        $this->db->join('CATEGORIES', 'PRODUCTS.CATEGORY=CATEGORIES.ID', 'LEFT');
        $this->db->join('UNITS', 'MATERIALREQUESTLINES.UNIT=UNITS.ID', 'LEFT');
        $this->db->join('STOCKCURRENT AS SC', 'SC.PRODUCT = MATERIALREQUESTLINES.PRODUCT AND SC.LOCATION = "0"', 'LEFT');

        //$this->db->join('PURCHASEORDERLINES', 'MATERIALREQUESTLINES.PRODUCT=PURCHASEORDERLINES.PRODUCT AND PURCHASEORDERLINES.LINESTATUS = ' . LINE_ISSUED, 'LEFT');
        $polquery='(SELECT POL.PRODUCT, SUM(POL.QUANTITY-POL.QUANTITYRECEIVED) AS POQTY FROM PURCHASEORDERLINES POL WHERE POL.LINESTATUS='.LINE_ISSUED.') AS POL';
        $this->db->join($polquery, 'MATERIALREQUESTLINES.PRODUCT=POL.PRODUCT','LEFT');
                //$this->db->join('STOCKDIARY', 'STOCKDIARY.PRODUCT = MATERIALREQUESTLINES.PRODUCT AND STOCKDIARY.REASON=1 AND STOCKDIARY.DATENEW=(SELECT MAX(DATENEW) FROM STOCKDIARY WHERE PRODUCT= MATERIALREQUESTLINES.PRODUCT AND STOCKDIARY.LOCATION="0")', 'LEFT');
        $this->db->group_by('MATERIALREQUESTLINES.PRODUCT');
        $this->db->group_by('MATERIALREQUESTS.REQUIREDON');
        
        $this->db->order_by("CATEGORIES.NAME", "asc");
        $this->db->order_by("PRODUCTS.NAME", "asc");
        $rows = $this->db->get()->result_array();

        $i=0;
        foreach ($rows as $row) {
       
                    $data['rows'][$i]['id'] = $i + 1;
                    //$row['QTYSTS'] = 0;
            $data['rows'][$i]['cell'] = array($row['REQUIREDON'], $row['CATEGORY'], $row['ID'], $row['PRODUCT'], $row['APPROVEDQUANTITY'], $row['QOH'], $row['UOM'], $row['POQTY'], $row['QTYSTS'], $row['TOTAL']);
            $i++;
            
        }
            return json_encode($data);
    }
    //FOR UOM AUTOCOMPLETE
    function getitemautocomplete($term) {
       $this->db->select('P.ID, P.CODE, P.NAME, P.PRICEBUY, P.PRICESELL');
        $where = "P.NAME LIKE '". $term . "%'";
        $this->db->where($where);
       return $this->db->get("PRODUCTS P")->result();
    }
    
    function getitembybarcode($term) {
       $this->db->select('P.ID, P.CODE,P.NAME, P.PRICEBUY, P.PRICESELL');
        $where = "P.CODE LIKE '". $term . "'";
        $this->db->where($where);
       return $this->db->get("PRODUCTS P")->result();
    }
    
    function getCategoryName($id) {
        $this->db->where('ID', $id);
        $q = $this->db->get('CATEGORIES')->result();
        return $q[0]->NAME;
    }
    function addmaterial($form_data1)
     {
        $this->db->insert('STOCKCURRENT', $form_data1);
	if ($this->db->affected_rows() == '1')
		{
			return TRUE;
		}
        return FALSE;
      }
      
      function deletematerial_units($id) {
        return ($this->db->delete('MATERIALS_UNITS', array('MATERIAL' => $id))) ? true : false;
     }
     
    function delete($id) {
        return ($this->db->delete('PRODUCTS', array('ID' => $id))) ? true : false;
     }
     
     function add($form_data)
    {
        $this->db->insert('PRODUCTS', $form_data);
		if ($this->db->affected_rows() == '1')
		{
			return TRUE;
		}
        return FALSE;
     }
       
     function add_materials_units($form_data2)
    {
        $this->db->insert('MATERIALS_UNITS', $form_data2);
		if ($this->db->affected_rows() == '1')
		{
			return TRUE;
		}
        return FALSE;
     }
     
     function add_stockcurrent($form_data2)
    {
        $this->db->insert('STOCKCURRENT', $form_data2);
		if ($this->db->affected_rows() == '1')
		{
			return TRUE;
		}
        return FALSE;
     }
     
     function update($id, $form_data){
        $this->db->where('ID', $id);
        $this->db->update('PRODUCTS', $form_data);
		if ($this->db->affected_rows() == '1')
		{
			return TRUE;
		}
        return FALSE;
     }  
     
     function update_materials_units($matid, $form_data){
        $this->db->where('MATERIAL', $matid);
        $this->db->update('MATERIALS_UNITS', $form_data);
		if ($this->db->affected_rows() == '1')
		{
			return TRUE;
		}
        return FALSE;
     }
       function update_stockcurrent($id, $form_data){
        $this->db->where('PRODUCT', $id);
        $this->db->update('STOCKCURRENT', $form_data);
		if ($this->db->affected_rows() == '1')
		{
			return TRUE;
		}
        return FALSE;
     }
     
}