<?php

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

class Categories_model extends CI_Model {

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

    //PRODUCT CATEGORIES GRID
    function getproductcategoriesgrid() {
        $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($searchOper=='cn')
                    $searchQry = 'A.'.$searchField . ' LIKE "%' . $searchString . '%"';
            else if($searchOper=='eq')
                    $searchQry = 'A.'.$searchField . ' = "' . $searchString .'"';
        
            $this->db->where($searchQry);
        }
    
        //Total Returned Rows Count
        $this->db->from('CATEGORIES AS A');
        $where = '((A.PARENTID IS NULL AND A.ID <> "-1" AND A.ID <> "-2") OR (A.PARENTID <> "-1" AND A.PARENTID <> "-2"))';
        $this->db->where($where);
        $this->db->join('CATEGORIES AS B', 'A.PARENTID = B.ID', 'LEFT');
        $count = $this->db->count_all_results();

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

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

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

        //Fetch data
        $this->db->select("A.ID, A.NAME, B.NAME AS PARENTID");
        $where = '((A.PARENTID IS NULL AND A.ID <> "-1" AND A.ID <> "-2") OR (A.PARENTID <> "-1" AND A.PARENTID <> "-2"))';
        $this->db->where($where);
        $this->db->join('CATEGORIES AS B', 'A.PARENTID = B.ID', 'LEFT');
        if($search=='true') $this->db->where($searchQry);
        $this->db->order_by($sidx, $sord);
        $this->db->limit($limit, $start);
        $rows = $this->db->get("CATEGORIES AS A")->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'], $row['PARENTID']);
            $i++;
        }
        return json_encode($data);
    }

    //MATERIAL CATEGORIES GRID
    function getmaterialcategoriesgrid() {
        $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($searchOper=='cn')
                $searchQry = 'CATEGORIES.'.$searchField . ' LIKE "%' . $searchString . '%"';
            else if($searchOper=='eq')
                $searchQry = 'CATEGORIES.'.$searchField . ' = "' . $searchString .'"';
            $this->db->where($searchQry);
        }
 
        

        //Total Returned Rows Count
        $this->db->from('CATEGORIES');
        $where = 'PARENTID = "-1"';
        $this->db->where($where);
        $count = $this->db->count_all_results();

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

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

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

        //Fetch data
        $this->db->select("ID, NAME");
        $where = 'PARENTID LIKE "-1"';
        $this->db->where($where);
        $this->db->order_by($sidx, $sord);
        $this->db->limit($limit, $start);
        if($search=='true') $this->db->where($searchQry);
        $rows = $this->db->get("CATEGORIES")->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);
    }


    //RETURN ALL CATEGORIES, FOR COMBO
    function getallcategories() {
        $this->db->select('ID, NAME');
        $where = 'ID <> "-1" AND ID <> "-2"';
        $this->db->where($where);
        $this->db->order_by('NAME');
        return $this->db->get('CATEGORIES')->result();
    }

    //RETURN PRODUCT CATEGORIES, FOR COMBO
    function getproductcategories() {
        $this->db->select('ID, NAME');
        $where = '(PARENTID IS NULL AND ID <> "-1" AND ID <> "-2") OR (PARENTID <> "-1" AND PARENTID <> "-2")';
        $this->db->where($where);
        return $this->db->get('CATEGORIES')->result();
    }

    //RETURN MATERIAL CATEGORIES, FOR COMBO
    function getmaterialcategories() {
        $this->db->select('ID, NAME');
        $where = 'PARENTID = "-1"';
        $this->db->where($where);
        return $this->db->get('CATEGORIES')->result();
    }

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


    //FOR AUTOCOMPLETE
    function getcategoriesautocomplete($term) {
        $this->db->select('ID, NAME');
        $where = "NAME LIKE '". $term. "%' AND (ID != '-1' OR ID != '-2') AND (PARENTID IS NULL OR PARENTID >= '1') ";
        $this->db->where($where);
        return $this->db->get("CATEGORIES")->result();        
    }
    
    function getparentcategoriesautocomplete($term) {
        $this->db->select('C.ID, C.NAME');
        $where = "C.NAME LIKE '". $term. "%' AND (C.ID != '-1' OR C.ID != '-2') AND (C.PARENTID IS NULL OR C.PARENTID >= '1')";
        $this->db->where($where);
        $this->db->join('CATEGORIES AS B', 'C.PARENTID = B.ID', 'LEFT');
        return $this->db->get("CATEGORIES C")->result();        
    }
    
    function getmaterialcategoriescombo() {
        $this->db->from('CATEGORIES');
        $this->db->select('ID, NAME');
        $this->db->where('PARENTID = "-1"');
	return $this->db->get()->result();
    }
    
    function getmatcategoriesautocomplete($term) {
        $this->db->select('ID, NAME');
        $where = 'PARENTID LIKE "-1"';
        $this->db->where($where);
        return $this->db->get("CATEGORIES")->result();     
    }
    function taxcategoriesautocomplete($term) {
        $this->db->select('ID, NAME');
        $where = "NAME LIKE '". $term. "%'";
        $this->db->where($where);
        return $this->db->get("TAXCATEGORIES")->result();        
    }
    function getprodcategoriesautocomplete($term) {
         $this->db->select('C.ID, C.NAME');
            $where1 = "C.NAME LIKE '". $term . "%'";
            $where2 = '(PARENTID IS NULL AND ID <> "-1" AND ID <> "-2") OR (PARENTID <> "-1" AND PARENTID <> "-2")';
         $this->db->where($where1);
         $this->db->where($where2);
         return $this->db->get("CATEGORIES C")->result();
        
    }
    
     function getprodcategoriesautocompletewithnextbarcode($term) {
         $this->db->select('MAX(P.CODE)+1 AS CODE, C.ID, C.NAME');
            $where1 = "C.NAME LIKE '". $term . "%'";
            $where2 = '(C.PARENTID IS NULL AND C.ID <> "-1" AND C.ID <> "-2")';
            //$where2 = '(C.PARENTID IS NULL AND C.ID <> "-1" AND C.ID <> "-2") OR (C.PARENTID <> "-1" AND C.PARENTID <> "-2")';
            $this->db->join('PRODUCTS P','P.CATEGORY=C.ID', 'LEFT');
            $this->db->group_by('P.CATEGORY');
            $this->db->where($where1);
         $this->db->where($where2);
         return $this->db->get("CATEGORIES C")->result();
        
    }
     function prodcategoriesautocomplete($term) {
         $this->db->select('C.ID, C.NAME');
            $where1 = "C.NAME LIKE '". $term . "%'";
            $where2 = '(PARENTID IS NULL AND ID <> "-1" AND ID <> "-2") OR (PARENTID <> "-1" AND PARENTID <> "-2")';
         $this->db->where($where1);
         $this->db->where($where2);
         return $this->db->get("CATEGORIES C")->result();
        
    }
    function getcategoryautocomplete($term) {
        $this->db->select('ID, NAME');
        $where = "NAME LIKE '". $term. "%'";
        $this->db->where($where);
        return $this->db->get("CATEGORIES")->result();        
    }
    function gettaxcategoriesautocomplete($term) {
        $this->db->select('T.ID, T.NAME');
        $where = "T.NAME LIKE '". $term . "%'";
         $this->db->where($where);
         return $this->db->get("TAXCATEGORIES T")->result();
    }
     function getmaterialcatautocomplete($term) {
        $this->db->select('T.ID, T.NAME');
        $where = "T.NAME LIKE '". $term . "%'";
         $this->db->where($where);
         return $this->db->get("CATEGORIES T")->result();
    }
    
    function getparentcat($name) {
        $this->db->where('NAME', $name);
        $q = $this->db->get('CATEGORIES')->result();
        return $q[0]->ID;
    }
    
   function delete($id) {
        return ($this->db->delete('CATEGORIES', array('ID' => $id))) ? true : false;
   }
   
   function add($form_data){
        $this->db->insert('CATEGORIES', $form_data);
		if ($this->db->affected_rows() == '1')
		{
			return TRUE;
		}
        return FALSE;
   }
           
   function update($id, $form_data) {
        $this->db->where('ID', $id);
        $this->db->update('CATEGORIES', $form_data);
		if ($this->db->affected_rows() == '1')
		{
			return TRUE;
		}
        return FALSE;        
    }
    
    function deletematerialcat($id) {
       return ($this->db->delete('CATEGORIES', array('ID' => $id))) ? true : false;
     }
     
    function addmaterialcat($form_data){
        $this->db->insert('CATEGORIES', $form_data);
		if ($this->db->affected_rows() == '1')
		{
			return TRUE;
		}
        return FALSE;
    }
    
    function updatematerialcat($id, $form_data){
        $this->db->where('ID', $id);
        $this->db->update('CATEGORIES', $form_data);
		if ($this->db->affected_rows() == '1')
		{
			return TRUE;
		}
		return FALSE;       
    }

    //edited by geethu

      function getProductmaterialsforBreak($cate_id='') {
        $this->db->select('ID, NAME,PRICEBUY');    
        if(isset($cate_id)) {
            if($cate_id!='') {
                $this->db->where('CATEGORY', $cate_id);   
            }
        }
       
        return $this->db->get('PRODUCTS')->result();
    }
}
