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

class Products_model extends CI_Model {

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

  function getmaterialautocomplete($term) {
        $this->db->select('P.ID, P.NAME');
        $this->db->from('PRODUCTS AS P');
        $where = "P.NAME LIKE '". $term. "%'  AND CATEGORIES.PARENTID IS NOT NULL";
        $this->db->where($where);
        $this->db->join('CATEGORIES', 'CATEGORIES.ID = P.CATEGORY', 'LEFT');
        return $this->db->get()->result();
 }
 function getitemsautocomplete($term) {
        $this->db->select('P.ID, P.NAME, P.PRICEBUY, P.PRICESELL,P.CODE,P.REFERENCE');
        $this->db->from('PRODUCTS AS P');
        $where = "P.NAME LIKE '". $term. "%'";
        $this->db->where($where);
        return $this->db->get()->result();
        
 }
  function materialautocomplete($term) {
        $this->db->select('P.ID, P.NAME');
        $this->db->from('PRODUCTS AS P');
        $where = "P.NAME LIKE '". $term. "%'";
        $this->db->where($where);
        return $this->db->get()->result();
        
 }
 function addProductUnits($arr) {
        return ($this->db->insert('MATERIALS_UNITS', $arr))? true : false;;
}
function getproductsautocomplete($term) {
        $this->db->select('P.ID, P.NAME');
        $this->db->from('PRODUCTS AS P');
        $where = "P.NAME LIKE '". $term. "%' AND CATEGORIES.PARENTID IS NULL";
        $this->db->where($where);
        $this->db->join('CATEGORIES', 'CATEGORIES.ID = P.CATEGORY', 'LEFT');
        return $this->db->get()->result();

 }
 function getproductsautocompleteforpblocation($term) {
        $this->db->select('P.ID, P.NAME');
        $this->db->from('PRODUCTS AS P');
        $where = "P.NAME LIKE '". $term. "%' AND CATEGORIES.PARENTID IS NULL AND 0=(SELECT COUNT(*) FROM LOCATIONS_PROD WHERE LOCATIONS_PROD.PRODUCT=P.ID)";
        $this->db->where($where);
        $this->db->join('CATEGORIES', 'CATEGORIES.ID = P.CATEGORY', 'LEFT');
        $this->db->join('LOCATIONS_PROD', 'LOCATIONS_PROD.PRODUCT = P.ID', 'LEFT');
        return $this->db->get()->result();
    }
 function getassetautocomplete($term) {
        $this->db->select('P.ID, P.NAME');
        $this->db->from('PRODUCTS AS P');
        $where = "P.NAME LIKE '". $term. "%' AND CATEGORIES.PARENTID=-2";
        $this->db->where($where);
        $this->db->join('CATEGORIES', 'CATEGORIES.ID = P.CATEGORY', 'LEFT');
        return $this->db->get()->result();
    }
 function getreferenceautocomplete($term) {
        $this->db->select('ID, REFERENCE, NAME');
        $where = "REFERENCE LIKE '". $term. "%'";
        $this->db->where($where);
        return $this->db->get("PRODUCTS")->result();
    }
    function getmatreferenceautocomplete($term) {
        $this->db->select('P.ID, P.REFERENCE, P.NAME');
        $this->db->join('CATEGORIES', 'CATEGORIES.ID = P.CATEGORY', 'LEFT');
        $where = "P.REFERENCE LIKE '". $term. "%' AND CATEGORIES.PARENTID='-1'";
        $this->db->where($where);
        return $this->db->get("PRODUCTS AS P")->result();
    }
 function getauxiparentproductsautocomplete($term) {
        $this->db->select('P.ID, P.NAME');
        $this->db->from('PRODUCTS AS P');
        $where = "P.NAME LIKE '". $term. "%' AND CATEGORIES.PARENTID IS NULL";
        $this->db->where($where);
        $this->db->join('CATEGORIES', 'CATEGORIES.ID = P.CATEGORY', 'LEFT');
        return $this->db->get()->result();
    }
 function getauxiproductsautocomplete($term) {
        $this->db->select('P.ID, P.NAME');
        $this->db->from('PRODUCTS AS P');
        $where = "P.NAME LIKE '". $term. "%' AND CATEGORIES.PARENTID IS NULL";
        $this->db->where($where);
        $this->db->join('CATEGORIES', 'CATEGORIES.ID = P.CATEGORY', 'LEFT');
        return $this->db->get()->result();
    }
//AUTOCOMPLETE FOR WAREHOUSE
 function getwarehouseautocomplete($term) {
        $this->db->select('ID, NAME');
        $this->db->like('NAME',$term);
        return $this->db->get("LOCATIONS")->result();
 }

//GET PRODUCTS
 function getproducts()
        {
        $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 = 'id';
        if (!$sord) $sord = 'asc';
        if (!$page) $page = 1;
        if (!$limit) $limit = 25;
        $search = $this->input->post('_search');

        if($search=='true')
        {
        $searchField = $this->input->post('searchField');
        $searchOper = $this->input->post('searchOper');
        $searchString = $this->input->post('searchString');
        $oper='';
        if($searchField=='CATEGORY'){
                if($searchOper=='cn')
                    $searchQry = 'C.NAME LIKE "%' . $searchString . '%"';
                else if($searchOper=='eq')
                    $searchQry = 'C.NAME = "' . $searchString .'"';
                $this->db->where($searchQry);
        }
        else if($searchField=='TAXCATEGORY'){
                if($searchOper=='cn')
                    $searchQry = 'T.NAME LIKE "%' . $searchString . '%"';
                else if($searchOper=='eq')
                    $searchQry = 'T.NAME = "' . $searchString .'"';
                $this->db->where($searchQry);
        }
        else{
                if($searchOper=='cn')
                    $searchQry = 'P.'.$searchField . ' LIKE "%' . $searchString . '%"';
                else if($searchOper=='eq')
                    $searchQry = 'P.'.$searchField . ' = "' . $searchString .'"';
                $this->db->where($searchQry);
        }
        }
        //Total Returned Rows Count
        $this->db->from('PRODUCTS AS P, CATEGORIES AS C');
      
//$where='P.CATEGORY <> -1 AND P.CATEGORY=C.ID AND P.CATEGORY NOT IN (SELECT C1.ID FROM CATEGORIES AS C1 LEFT JOIN CATEGORIES AS C2 ON C1.PARENTID = C2.ID WHERE C1.PARENTID = -1 OR C1.PARENTID = C2.ID)';
            $where='P.CATEGORY <> "-1" AND P.CATEGORY=C.ID AND C.ID <> "-1"';
            if($search=='true') 
            $this->db->where($searchQry);
            $where1='P.CATEGORY <> "-1" AND P.CATEGORY=C.ID AND C.ID <> "-1" AND (C.PARENTID IS NULL OR C.PARENTID >= "1") ';
            $this->db->where($where1);
            $this->db->join('TAXCATEGORIES AS T', 'T.ID = P.TAXCAT', 'LEFT');
            $this->db->join('CATEGORIES AS B', 'C.PARENTID = B.ID', 'LEFT');
            $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;
        }
        $this->db->limit($limit, $start);
          //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("P.ID, P.REFERENCE, P.NAME, C.NAME AS CATEGORY, P.CODE, P.PRICEBUY, P.PRICESELL, T.NAME AS TAXCAT,P.ATTRIBUTES,P.TAXCAT AS TAXCATID,P.CATEGORY AS CATID");
        $this->db->from('PRODUCTS AS P, CATEGORIES AS C');
      
            //$where='P.CATEGORY <> -1 AND P.CATEGORY=C.ID AND P.CATEGORY NOT IN (SELECT C1.ID FROM CATEGORIES AS C1 LEFT JOIN CATEGORIES AS C2 ON C1.PARENTID = C2.ID WHERE C1.PARENTID = -1 OR C1.PARENTID = C2.ID)';
        $where='P.CATEGORY <> "-1" AND P.CATEGORY=C.ID AND C.ID <> "-1" AND (C.PARENTID IS NULL OR C.PARENTID >= "1") ';
        
        $this->db->where($where);
        
        $this->db->join('TAXCATEGORIES AS T', 'T.ID = P.TAXCAT', 'LEFT');
        $this->db->join('CATEGORIES AS B', 'C.PARENTID = B.ID', 'LEFT');
        if($search=='true') $this->db->where($searchQry);
        //$this->db->order_by($sidx, $sord);
         if ($count > 0 && $limit > 0) {
            $total_pages = ceil($count / $limit);
            $start = $limit * $page - $limit;
        } else {
            $total_pages = 0;
            $start=0;
        }
        $this->db->limit($limit, $start);
        
        $rows = $this->db->get()->result_array();
        //Format data
        $i=0;
        foreach($rows as $row){
            $data['rows'][$i]['id']=$i+1;
            $data['rows'][$i]['cell']=array($row['ID'],$row['CATEGORY'],$row['REFERENCE'],$row['CODE'],$row['NAME'],$row['PRICEBUY'],$row['PRICESELL'],$row['ATTRIBUTES'],$row['CATID']);
            $i++;
        }
        
        return json_encode($data);
   }
   function getinventory1()
        {

            $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 = 'id';
            if (!$sord) $sord = 'asc';
            if (!$page) $page = 1;
            if (!$limit) $limit = 25;
            //Total Returned Rows Count
            $this->db->from('STOCKLEVEL');
            $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("STOCKLEVEL.ID, LOCATIONS.NAME AS LOCATION,PRODUCTS.NAME AS PRODUCT, STOCKLEVEL.STOCKSECURITY,STOCKLEVEL.STOCKMAXIMUM, STOCKDIARY.UNITS");
                $this->db->join('STOCKDIARY','STOCKDIARY.LOCATION = STOCKLEVEL.LOCATION','LEFT');
                $this->db->join('LOCATIONS','LOCATIONS.ID = STOCKLEVEL.LOCATION','LEFT');
                $this->db->join('PRODUCTS','PRODUCTS.ID = STOCKLEVEL.PRODUCT','LEFT');
                $this->db->order_by($sidx, $sord);
                $this->db->limit($limit, $start);
                $rows = $this->db->get("STOCKLEVEL")->result_array();

                //Format data
                $i=0;
           foreach($rows as $row){
                $data['rows'][$i]['id']=$i+1;
                $data['rows'][$i]['cell']=array($row['ID'],$row['LOCATION'],$row['PRODUCT'],$row['STOCKSECURITY'],$row['STOCKMAXIMUM'],$row['UNITS']);
                $i++;
           }
        return json_encode($data);
    }
    function productsCount() {
         return $this->db->get('PRODUCTS')->num_rows();
    }

    function stockDiaryCount() {
        return $this->db->get('STOCKDIARY')->num_rows();
    }
    function getCategoryName($id) {
        $this->db->where('ID', $id);
        $q = $this->db->get('CATEGORIES')->result();
        return $q[0]->NAME;
    }


    function getTariffname($id) {
        $this->db->where('ID', $id);
        $q = $this->db->get('TARIFFAREAS')->result();
        return $q[0]->NAME;
    }

    function pidbbcode($code) {
        $this->db->where('CODE', $code);
        $q = $this->db->get('PRODUCTS')->result();
        return $q[0]->ID;
    }

    function getTaxCatName($id) {
        $this->db->where('ID', $id);
        $q = $this->db->get('TAXCATEGORIES')->result();
        return $q[0]->NAME;
    }

    function getLocName($id) {
        $this->db->where('ID', $id);
        $q = $this->db->get('LOCATIONS')->result();
        return $q[0]->NAME;
    }

    function getPIDbB($id) {
        $this->db->where('CODE', $id);
        $q = $this->db->get('PRODUCTS')->result();
        return $q[0]->ID;
    }

    function getpidbn($name) {
        $this->db->where('NAME', $name);
        // return $this->db->get('PRODUCTS')->result();
        $q = $this->db->get('PRODUCTS')->result();
        return $q[0]->ID;
    }
    function getmidbn($name) {
        $this->db->where('NAME', $name);
        // return $this->db->get('PRODUCTS')->result();
        $q = $this->db->get('PRODUCTS')->result();
        return $q[0]->ID;
    }
        

    function getpnbid($id) {
        $this->db->where('ID', $id);
        return $this->db->get('PRODUCTGROUPS')->result();
    }

    function getProdnamebPID($id) {
        $this->db->where('ID', $id);
        $q = $this->db->get('PRODUCTS')->result();
        return $q[0]->NAME;
    }
    
    function getGroupById($id) {
        $this->db->where('ID', $id);
        return $this->db->get('TARIFFAREAS')->result();
    }
    function diaryData($id) {
        $this->db->where('ID', $id);
        return $this->db->get('STOCKDIARY')->result();
    }

    //GET TAX CATEGORY
    function gettaxcats() {
        $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 = 'id';
        if (!$sord) $sord = 'asc';
        if (!$page) $page = 1;
        if (!$limit) $limit = 25;
        //Total Returned Rows Count
        $this->db->from('TAXCATEGORIES');
        $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("ID, NAME");
        $this->db->order_by($sidx, $sord);
        $this->db->limit($limit, $start);
        $rows = $this->db->get("TAXCATEGORIES")->result_array();
        //Format data
        $i=0;
        foreach($rows as $row){
            $data['rows'][$i]['id']=$i+1;
            $data['rows'][$i]['cell']=array($row['ID'],$row['NAME']);
            $i++;
        }
        return json_encode($data);
    }

    function ajgetpbw() {
        return false;
    }

    function ajgetPriceList() {
        $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 = 'id';
        if (!$sord) $sord = 'asc';

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

        //Total Returned Rows Count
        $this->db->from('TARIFFAREAS_PROD');
        $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('TARIFFID, PRODUCTID,PRICESELL');
        $this->db->select('TARIFFAREAS_PROD.TARIFFID,TARIFFAREAS.NAME AS TARIFF, PRODUCTS.NAME AS PRODUCT','TARIFFAREAS_PROD.PRICESELL');
        $this->db->join("TARIFFAREAS","TARIFFAREAS.ID = TARIFFAREAS_PROD.TARIFFID");
        $this->db->join("PRODUCTS","PRODUCTS.ID = TARIFFAREAS_PROD.TARIFFID");
        $this->db->order_by($sidx, $sord);
        $this->db->limit($limit, $start);
        $rows = $this->db->get("TARIFFAREAS_PROD")->result_array();

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

    public function searchProducts($keyword, $uri, $perpage) {
        $limit = (is_numeric($uri)) ? $uri.','. $perpage : $perpage;
        return $this->db->query(" SELECT PRODUCTS.*, CATEGORIES.ID as catid, CATEGORIES.NAME as catname, TAXCATEGORIES.ID as taxid, TAXCATEGORIES.NAME as taxname
            FROM PRODUCTS LEFT JOIN CATEGORIES ON PRODUCTS.CATEGORY = CATEGORIES.ID LEFT JOIN TAXCATEGORIES ON PRODUCTS.TAXCAT = TAXCATEGORIES.ID
            WHERE PRODUCTS.NAME LIKE '%$keyword%' OR PRODUCTS.NAME = '$keyword'
            LIMIT $limit")->result();
    }

    public function searchProductsRows($keyword) {
        return $this->db->query("SELECT PRODUCTS.*, CATEGORIES.ID as catid, CATEGORIES.NAME as catname, TAXCATEGORIES.ID as taxid, TAXCATEGORIES.NAME as taxname
            FROM PRODUCTS
            LEFT JOIN CATEGORIES
            ON PRODUCTS.CATEGORY = CATEGORIES.ID
            LEFT JOIN TAXCATEGORIES
            ON PRODUCTS.TAXCAT = TAXCATEGORIES.ID
            WHERE PRODUCTS.NAME LIKE '%$keyword%'
            OR PRODUCTS.NAME = '$keyword'")->num_rows;
    }

    public function getauxiliaryproductslist($id) {
       $table_name = "PRODUCTS_COM";
        $this->db->select("PRODUCTS.NAME AS PRODUCT, P2.NAME AS PRODUCT2")->from($table_name);
        $this->db->where('PRODUCTS_COM.ID', $id);
        $this->db->join('PRODUCTS', 'PRODUCTS.ID = PRODUCTS_COM.PRODUCT','LEFT');
        $this->db->join('PRODUCTS AS P2', 'P2.ID = PRODUCTS_COM.PRODUCT2','LEFT');
        return $this->db->get()->result();
    }
    
    function addTariffareas($arr) {
        return ($this->db->insert('TARIFFAREAS', $arr)) ? true : false;
    }
    
    function addTariffareas_prod($arr) {
        return ($this->db->insert('TARIFFAREAS_PROD', $arr)) ? true : false;
    }

    function addProductS2($arr, $where) {
        $this->db->where('ID', $where);
        return ($this->db->update('PRODUCTS', $arr)) ? true : false;
    }

    function getauxiliaryproducts() {
        //Fetch data
        $this->db->select('PRODUCTS_COM.ID, PRODUCTS.NAME AS PRODUCT,P2.NAME AS PRODUCT2');
        $this->db->join('PRODUCTS' , 'PRODUCTS_COM.PRODUCT = PRODUCTS.ID', 'LEFT');
        $this->db->join('PRODUCTS AS P2' , 'PRODUCTS_COM.PRODUCT2 = P2.ID', 'LEFT');
        $rows = $this->db->get("PRODUCTS_COM")->result_array();
        return $rows;
    }

    public function getlines($id) {
        $table_name = "TARIFFAREAS_PROD";
        $this->db->select("TARIFFAREAS_PROD.TARIFFID AS ID,PRODUCTS.NAME AS PRODUCT,TARIFFAREAS_PROD.PRICESELL AS PRICE")->from($table_name);
        $this->db->where('TARIFFAREAS_PROD.TARIFFID', $id);
        $this->db->join("PRODUCTS","PRODUCTS.ID = TARIFFAREAS_PROD.PRODUCTID","LEFT");
        return $this->db->get()->result();
    }
    
    function pricelDelete($data) {
        $exp = explode("T", $data);
        return ($this->db->delete('TARIFFAREAS_PROD', array('PRODUCTID'=> $exp[0], 'TARIFFID'=> $exp[1]))) ? true : false;
    }

    function tariffDelete($id) {
        return ($this->db->delete('TARIFFAREAS', array('ID' => $id))) ? true : false;
    }


        function getcustcategories() {
		$this->db->select('ID, NAME');
            	return $this->db->get('TAXCUSTCATEGORIES')->result();
	}
        function get() {
		$this->db->select('ID, NAME');
		return $this->db->get('TAXCATEGORIES')->result();
	}
	function getLocations() {
		$this->db->select('ID, NAME');
		return $this->db->get('LOCATIONS')->result();
	}
        function getSuppliers() {
		$this->db->select('ID, NAME');
		return $this->db->get('SUPPLIERS')->result();
	}
	function gettaxcat() {
		$this->db->select('ID, NAME');
                $this->db->where('ID <> "-1"');

		return $this->db->get('TAXCATEGORIES')->result();
	}
        function getparenttax() {
		$this->db->select('ID, NAME');
                $this->db->where('ID <> "-1"');
		return $this->db->get('TAXES')->result();
        }
	function gettariff() {
		$this->db->select('ID, NAME');
		return $this->db->get('TARIFFAREAS')->result();
	}

	function getprodbycat($id) {
		$this->db->select('ID, NAME');
		$this->db->where('CATEGORY', $id);
		return $this->db->get('PRODUCTS')->result();
	}

	function getTaxes() {
		return $this->db->get('TAXES')->result();
	}

//	function deleteTaxes($id) {
//		return ($this->db->delete('TAXES', array('ID' => $id))) ? true : false;
//	}

//	function deleteTaxCat($id){
//		return ($this->db->delete('TAXCATEGORIES', array('ID' => $id))) ? true : false;
//	}

	function diaryDelete($id) {
		return ($this->db->delete('STOCKDIARY', array('ID' => $id))) ? true : false;
	}
        
     //original
//  function addTax($data) {
//		return ($this->db->insert('TAXES', $data)) ? true : false;
//	}

	function addPrice($data) {
		$this->db->where('TARIFFID', $data['TARIFFID']);
		$this->db->where('PRODUCTID', $data['PRODUCTID']);
		if($this->db->get('TARIFFAREAS_PROD')->num_rows > 0) {
			return false;
		} else {
			return ($this->db->insert('TARIFFAREAS_PROD', $data)) ? true : false;
		}
	}




	function updateTax($data, $id) {
		$this->db->where('ID', $id);
		return ($this->db->update('TAXES', $data)) ? true : false;
	}

	function updatePrice($data, $uridata) {
		$exp = explode("T", $uridata);
		$this->db->where('PRODUCTID', $exp[0]);
		$this->db->where('TARIFFID', $exp[1]);
		return ($this->db->update('TARIFFAREAS_PROD', $data)) ? true : false;
	}



	function updateDiary($data, $id) {
		$this->db->where('ID', $id);
		return ($this->db->update('STOCKDIARY', $data)) ? true : false;
	}

	function getTaxData($id) {
		$this->db->where('ID', $id);
		return $this->db->get('TAXES')->result();
	}

	function priceData($data) {
		$exp = explode("T", $data);
		$this->db->where('PRODUCTID', $exp[0]);
		$this->db->where('TARIFFID', $exp[1]);
		return $this->db->get('TARIFFAREAS_PROD')->result();
	}

	function tariffData($id) {
		$this->db->where('ID', $id);
		return $this->db->get('TARIFFAREAS')->result();
	}


	function getProductByID($id) {
		$this->db->select('PRODUCTS.ID, PRODUCTS.REFERENCE, PRODUCTS.NAME,PRODUCTS.CODE, PRODUCTS.CATEGORY, PRODUCTS.TAXCAT, PRODUCTS.PRICEBUY,PRODUCTS.PRICESELL, MATERIALS_UNITS.AMOUNT, MATERIALS_UNITS.UNIT, PRODUCTS.ATTRIBUTES');
                $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 updateProductUnits($data, $id) {
		$this->db->where('MATERIAL', $id);
		return ($this->db->update('MATERIALS_UNITS', $data)) ? true : false;
	}
        function countProductUnits($id) {
            $this->db->select('MATERIAL');
		$this->db->where('MATERIAL', $id);
		return $this->db->get('MATERIALS_UNITS')->result();
	}
         function removeProductUnits($id) {
        return ($this->db->delete('MATERIALS_UNITS', array('MATERIAL' => $id))) ? true : false;
    }
        function getproductbuyprice($product){

             //$this->db->select('PRICE');
            $this->db->where('A.PRODUCT',$product);

            $this->db->join('STOCKDIARY B', 'B.PRODUCT = A.PRODUCT  AND B.REASON = 1 AND B.LOCATION = 0 AND B.DATENEW=(SELECT MAX(DATENEW) FROM STOCKDIARY WHERE PRODUCT = A.PRODUCT)' );
        
		$q = $this->db->get('STOCKDIARY A')->result();
                if($q)
		return $q[0]->PRICE;
                else
                    return 0;

        }
 function update_products_cat($id, $form_data2) {
       $this->db->where('PRODUCT', $id);
           		$this->db->update('PRODUCTS_CAT', array('PRODUCT'=>$form_data2['ID']));
	}
       function update($id, $form_data)
    {
    $this->db->where('ID', $id);
           		$this->db->update('PRODUCTS', $form_data);
         if ($this->db->_error_message())
		{
			return $this->db->_error_message();
		}
                else
                {
                    return "true";
                }
     }
     
    
    
    function deleteproducts_cat($id) {
        return ($this->db->delete('PRODUCTS_CAT', array('PRODUCT' => $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->_error_message())
		{
			return $this->db->_error_message();
		}
                else
                {
                    return "true";
                }
     }
     
   function add2($newid) {
           		$this->db->insert('PRODUCTS_CAT', array('PRODUCT'=>$newid));
	}
   function delete1($id) {
        return ($this->db->delete('PRODUCTS_COM', array('ID' => $id))) ? true : false;
     }
      function add1($form_data)
    {
  $this->db->insert('PRODUCTS_COM', $form_data);
		if ($this->db->affected_rows() == '1')
		{
			return TRUE;
		}
        return FALSE;
     }
          function update1($id, $form_data)
    {
   $this->db->where('ID', $id);
            $this->db->update('PRODUCTS_COM', $form_data);
		if ($this->db->affected_rows() == '1')
		{
			return TRUE;
		}
		return FALSE;    
     }
    function getprodcat() {
        $this->db->select('NAME');
        $this->db->from('CATEGORIES');
        return $this->db->get()->result();
    }
 
    function productfindcodewise($term) {
        return $this->db->query("
                SELECT *
                    FROM PRODUCTS
                WHERE CODE='$term'")->result();
    }
    function getallproducts() {
        $this->db->select('ID,CODE,NAME,PRICEBUY,PRICESELL,TAXCAT');
        $this->db->from('PRODUCTS');
        return $this->db->get()->result();
    }
    
    function all_products_count(){
        return $this->db->query("SELECT count(*)AS total FROM   PRODUCTS P JOIN CATEGORIES C ON P.CATEGORY = C.ID LEFT JOIN TAXCATEGORIES T ON P.TAXCAT=T.ID WHERE P.CATEGORY!=-1")->result();
    }
    
    
    function all_products($limit,$start,$dir,$ownerid,$col='date') {
        if ($col=='date'){
            $col='created';
        }
        $sql = "SELECT P.*,T.NAME AS taxcategory, C.NAME AS categoryname,(((SELECT MAX(RATE) FROM TAXES WHERE CATEGORY=T.ID)*P.PRICESELL)+P.PRICESELL) AS TAXRATE FROM PRODUCTS P JOIN CATEGORIES C ON P.CATEGORY=C.ID LEFT JOIN TAXCATEGORIES T ON P.TAXCAT=T.ID  WHERE P.CATEGORY!=-1";
        if($col)
            $sql.= " ORDER BY {$col} {$dir}";
        if($limit!='' && $start!='')
            $sql.= " Limit {$start}, {$limit}";
//        echo $sql;exit;
        $query = $this->db->query($sql);
        if($query->num_rows()>0) {
            return $query->result(); 
        } else {
            return null;
        }
    }

    function products_search($limit,$start,$search_array,$dir, $ownerid,$col='date') {
        if ($col=='date'){
            $col='created';
        }
        $where = ' Where 1';
        if(isset($search_array['search'])){
            $where .= ' and (REFERENCE like "%'.$search_array['search'].'%" || CODE like "%'.$search_array['search'].'%" ||  NAME like "%'.$search_array['search'].'%" || PRICEBUY like "%'.$search_array['search'].'%" || PRICESELL like "%'.$search_array['search'].'%" || taxcategory like "%'.$search_array['search'].'%" || TAXRATE like "%'.$search_array['search'].'%")';
        }
        if(isset($search_array['searchbarcode'])){
            $where .= ' and CODE="'.$search_array['searchbarcode'].'"';
        }
        if(isset($search_array['searchbycategory'])){
            $where .= ' and CATEGORY="'.$search_array['searchbycategory'].'"';
        }
        if(isset($search_array['searchbyitemtype'])){
            if($search_array['searchbyitemtype']=='P')
                $searchitemtype=   0;
            else if($search_array['searchbyitemtype']=='M')
                $searchitemtype=   1;
            $where .= ' and ITEMTYPE="'.$searchitemtype.'"';
        }
        $sql = "SELECT * FROM (SELECT P.*,T.NAME AS taxcategory, C.NAME AS categoryname,(((SELECT MAX(RATE) FROM TAXES WHERE CATEGORY=T.ID)*P.PRICESELL)+P.PRICESELL) AS TAXRATE FROM PRODUCTS P JOIN CATEGORIES C ON P.CATEGORY=C.ID LEFT JOIN TAXCATEGORIES T ON P.TAXCAT=T.ID WHERE P.CATEGORY!=-1) MYTABLE ".$where;
        if($col)
            $sql.= " ORDER BY {$col} {$dir}";
        if($limit!='' && $start!='')
            $sql.= " Limit {$start}, {$limit}";  
        $query = $this->db->query($sql);
        if($query->num_rows()>0) {
            return $query->result(); 
        } else {
            return null;
        }
    }

    function products_search_count($search_array, $ownerid) {
        $where = ' Where 1';
        if(isset($search_array['search'])){
            $where .= ' and (REFERENCE like "%'.$search_array['search'].'%" || CODE like "%'.$search_array['search'].'%" ||  NAME like "%'.$search_array['search'].'%" || PRICEBUY like "%'.$search_array['search'].'%" || PRICESELL like "%'.$search_array['search'].'%" || taxcategory like "%'.$search_array['search'].'%" || TAXRATE like "%'.$search_array['search'].'%")';
        }
        if(isset($search_array['searchbarcode'])){
            $where .= ' and CODE="'.$search_array['searchbarcode'].'"';
        }
        if(isset($search_array['searchbycategory'])){
            $where .= ' and CATEGORY="'.$search_array['searchbycategory'].'"';
        }
        if(isset($search_array['searchbyitemtype'])){
            if($search_array['searchbyitemtype']=='P')
                $searchitemtype=   0;
            else if($search_array['searchbyitemtype']=='M')
                $searchitemtype=   1;
            $where .= ' and ITEMTYPE="'.$searchitemtype.'"';
        }
        $sql = "SELECT * FROM (SELECT P.*,T.NAME AS taxcategory, C.NAME AS categoryname,(((SELECT MAX(RATE) FROM TAXES WHERE CATEGORY=T.ID)*P.PRICESELL)+P.PRICESELL) AS TAXRATE FROM PRODUCTS P JOIN CATEGORIES C ON P.CATEGORY=C.ID LEFT JOIN TAXCATEGORIES T ON P.TAXCAT=T.ID WHERE P.CATEGORY!=-1) MYTABLE ".$where;
        $query = $this->db->query($sql);
        return $query->num_rows();
    }
    
    
    
    
    function getproductbyidautofill($id='') {
        $this->db->select('ID,CODE,NAME,PRICEBUY,PRICESELL,TAXCAT');
        $this->db->where('ID',$id);
        $this->db->from('PRODUCTS');
        return $this->db->get()->result();
    }
    
    
    function productpricecalculation($newbuyprice=0,$id='',$currentunits=0, $newunits=0) {
//        echo $newbuyprice;echo '-';
//        echo $currentunits;echo '-';
//        echo $newunits;echo '-';
//        exit;
        $where_resources = array('NAME' => 'Ticket.Buttons');
        $resourcesticketbutton =  $this->common->getWhere('RESOURCES',$where_resources);
        $xml = simplexml_load_string($resourcesticketbutton[0]->CONTENT);
        $inventorycostObj   =   $xml->inventorycosting;
        if($inventorycostObj) {
            $costattrbVal   =   $inventorycostObj->attributes()->value;
        } else {
            $costattrbVal='last';
        }
        $prevpricebuyrec = $this->getproductbyidautofill($id);
        
        if($prevpricebuyrec[0]) {
            $prevpricebuy    =  $prevpricebuyrec[0]->PRICEBUY;
        } else {
            $prevpricebuy    =  0;
        }
        if($id != '' && $costattrbVal!='disabled') {
			$dataprice = $newbuyprice;
            if($newbuyprice==0) {
                //
            } else if($prevpricebuy==0) {
                $dataprice = $newbuyprice;
            } else {
                if($costattrbVal=='last') {
                    $dataprice = $newbuyprice;
                } else if($costattrbVal=='maximum') {
                    if($prevpricebuy>$newbuyprice) {
                        $dataprice = $prevpricebuy;
                    } else {
                        $dataprice = $newbuyprice;
                    }
                } else if($costattrbVal=='minimum') {
                    if($prevpricebuy>$newbuyprice) {
                        $dataprice = $newbuyprice;
                    } else {
                        $dataprice = $prevpricebuy;
                    }
                } else if($costattrbVal=='average') {
                    $avgprice = ($newbuyprice+$prevpricebuy)/2;
                    $dataprice = $avgprice;
                } else if($costattrbVal=='wac') {
                    
                    $avgprice = (($prevpricebuy*$currentunits)+($newbuyprice*$newunits))/($currentunits+$newunits);
                    $dataprice = $avgprice;
                }
                else {
                    $dataprice = $newbuyprice;
                }
            }
            $this->db->where('ID',$id);
			$this->db->set('PRICEBUY',$dataprice);
            $this->db->update('PRODUCTS');
        }
    }
    
    function productmaterialidwise($matid='') {
        $sql = "SELECT PRODUCTS_MAT.PRODUCT,PRODUCTS_MAT.AMOUNT*PRODUCTS.PRICEBUY AS AMOUNT"
            . " FROM PRODUCTS_MAT JOIN PRODUCTS ON PRODUCTS_MAT.MATERIAL=PRODUCTS.ID WHERE MATERIAL='".$matid."'";
        $query = $this->db->query($sql);
        return $query->result();
    }
    
    function productmaterialmaterialwise($proid='') {
        $sql = "SELECT PRODUCT,SUM(AMOUNT) AS TOTALAMOUNT FROM ( SELECT PRODUCTS_MAT.MATERIAL,PRODUCTS.PRICEBUY,
            PRODUCTS_MAT.AMOUNT*PRODUCTS.PRICEBUY AS AMOUNT,PRODUCTS_MAT.PRODUCT
            FROM PRODUCTS_MAT JOIN PRODUCTS ON PRODUCTS_MAT.MATERIAL=PRODUCTS.ID
            WHERE PRODUCTS_MAT.PRODUCT='".$proid."') A";
        $query = $this->db->query($sql);
        return $query->row();
    }
    
}
