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

class Tariff_model extends CI_Model {

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

  //GET TARIFF AUTOCOMPLETE
  function gettariffautocomplete($term)
    {

        $this->db->select('ID, NAME');
        $this->db->like('NAME', $term);
        return $this->db->get("TARIFFAREAS")->result();
    }
    //GET TARIFF AUTOCOMPLETE
    function getalltariff() {
        $this->db->select('ID, NAME');
        return $this->db->get("TARIFFAREAS")->result();
    }
        //GET TARIFF
function gettarif()
        {
$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');
$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."";



$this->db->select("ID,NAME");
$this->db->order_by($sidx, $sord);
//$this->db->limit($limit, $start);
$rows = $this->db->get("TARIFFAREAS")->result_array();

$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 addTariffareas($arr) {
		return ($this->db->insert('TARIFFAREAS', $arr)) ? true : false;
	}
 function addTariffareas_prod($arr) {
		return ($this->db->insert('TARIFFAREAS_PROD', $arr)) ? true : false;
	}
function getGroupById($id) {
        $this->db->where('ID', $id);
        return $this->db->get('TARIFFAREAS')->result();
    }
 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 updateTariff($id, $PRODUCT) {
        $this->db->where('ID', $id);
        $update = array(
            'NAME' => $PRODUCT
                    );
        return ($this->db->update('TARIFFAREAS', $update)) ? true : false;
    }
 function deleteLines($id) {
        return ($this->db->delete('TARIFFAREAS_PROD', array('TARIFFID' => $id))) ? true : false;
    }
    
    function deleteTariffbyproduct($id) {
        return ($this->db->delete('TARIFFAREAS_PROD', array('PRODUCTID' => $id))) ? true : false;
    }
    
   function tariffDelete($id) {
		return ($this->db->delete('TARIFFAREAS', array('ID' => $id))) ? true : false;
	}

    function all_products_count(){
        return $this->db->query("SELECT count(*) AS total FROM PRODUCTS P WHERE P.CATEGORY!=-1")->result();
    }
    
    
    function all_products($limit,$start,$dir,$ownerid,$tariffid='',$col='date') {
        if ($col=='date'){
            $col='created';
        }
        $sql = "SELECT * FROM (SELECT P.ID,P.NAME,P.CODE,P.PRICESELL,P.CATEGORY,TP.PRICESELL AS TARIFFPRICE,(SELECT MAX(RATE) FROM TAXES WHERE CATEGORY=T.ID) AS TAXRATE"
            . " FROM PRODUCTS P JOIN CATEGORIES C ON P.CATEGORY=C.ID"
            . " LEFT JOIN TAXCATEGORIES T ON P.TAXCAT=T.ID "
            . " LEFT JOIN TARIFFAREAS_PROD TP ON TP.PRODUCTID=P.ID AND TP.TARIFFID='".$tariffid."' WHERE P.CATEGORY!=-1) MYTABLE";
        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($limit,$start,$search_array,$dir, $ownerid,$col='date') {
        if ($col=='date'){
            $col='created';
        }
        $where = '';
        if(isset($search_array['searchtext'])){
            $where .= ' and (NAME like "%'.$search_array['searchtext'].'%" || PRICESELL like "%'.$search_array['searchtext'].'%"'
                    . ' || CODE like "%'.$search_array['searchtext'].'%")';
        }
        if(isset($search_array['searchcategory'])){
            $where .= ' and CATEGORY="'.$search_array['searchcategory'].'"';
        }
        $sql = "SELECT * FROM (SELECT P.ID,P.NAME,P.CODE,P.PRICESELL,P.CATEGORY,TP.PRICESELL AS TARIFFPRICE,(SELECT MAX(RATE) FROM TAXES WHERE CATEGORY=T.ID) AS TAXRATE"
            . " FROM PRODUCTS P JOIN CATEGORIES C ON P.CATEGORY=C.ID"
            . " LEFT JOIN TAXCATEGORIES T ON P.TAXCAT=T.ID"
            . " LEFT JOIN TARIFFAREAS_PROD TP ON TP.PRODUCTID=P.ID AND TP.TARIFFID='".$search_array['tariffid']."' WHERE P.CATEGORY!=-1)"
            . " MYTABLE WHERE 1 ".$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 = '';
        if(isset($search_array['search'])){
            $where .= ' and (NAME like "%'.$search_array['searchtext'].'%" || PRICESELL like "%'.$search_array['searchtext'].'%"'
                . ' || CODE like "%'.$search_array['searchtext'].'%")';
        }
        if(isset($search_array['searchcategory'])){
            $where .= ' and CATEGORY="'.$search_array['searchcategory'].'"';
        }
        $sql = "SELECT count(*) as cnt FROM PRODUCTS WHERE CATEGORY!=-1 ".$where;
        $query = $this->db->query($sql);
        $srchcnt    =   $query->row();
        return $srchcnt->cnt;
    }
}