<?php
class Archanai_namelist_report_model extends CI_Model {
    
    function __construct() {
        parent::__construct();
        $this->category = TBL_CATEGORY;
        $this->items = TBL_ITEM;
        $this->archanai = TBL_BAR;
        $this->archanai_item = TBL_BARI;
        $this->star = TBL_STAR;
    }

    public function category() {
        $this->db->select('c.*');
        $this->db->where('c.status',1);
        return $this->db->get($this->category . ' as c')->result();
    }

    function get_items( $cat_id = '' ) {
        
        $whr = array(
            'itm.status' => 1
        );
        
        if( !empty( $cat_id ) ) :
            $whr['itm.category_id'] = $cat_id;
        endif;
        
        $this->db->select('cat.id, cat.category_name, cat.grouping, itm.*, cat.id AS cat_id, itm.id AS item_id, itm.is_inventory, itm.inv_item_id, itm.avail_counter_qty AS actual_counter_qty, CASE WHEN `itm`.`avail_counter_qty` < 0 THEN 0 ELSE `itm`.`avail_counter_qty` END AS avail_counter_qty, (SELECT price FROM `'. TBL_PRICE .'` WHERE FIND_IN_SET( itm.id, `item_id`) AND `category_id` = cat.id AND `status` = "1" AND CURDATE() BETWEEN start_date AND end_date) AS date_price', FALSE);
        $this->db->join( TBL_CATEGORY . ' AS cat', 'cat.id = itm.category_id' );
        $this->db->order_by('itm.order_sequence');
        $q = $this->db->get_where( TBL_ITEM . ' AS itm', $whr );
        $results = $q->result_array();
        return $results;
    }
    
    public function list_item(){        
        $this->db->select('i.*');
        $this->db->join($this->items . ' as i','i.id = ai.bari_item_id');
        $this->db->where('i.status !=',2);
        $this->db->where('i.is_inventory',1);
        $this->db->group_by('ai.bari_item_id');
        return $this->db->get($this->archanai_item . ' as ai')->result();
    
    }

    public function get_by_item($id){        
        $this->db->select('i.*');
        $this->db->where('i.id', $id);
        $this->db->where('i.status',1);
        return $this->db->get($this->items . ' as i')->row();
    
    }

    public function get_data($start_date='',$end_date='',$cat_id = '',$item_id=''){
        
        $this->db->select('a.*, s1.tamil_name as star_name1, s2.tamil_name as star_name2, s3.tamil_name as star_name3, ai.bari_cat_id as cat_id, ai.bari_item_id as item_it, i.name as item_name', FALSE);
        $this->db->join($this->archanai . ' as a','a.id = ai.bar_id', 'LEFT');
        $this->db->join($this->star . ' as s1','s1.id = a.star_id_1', 'LEFT');
        $this->db->join($this->star . ' as s2','s2.id = a.star_id_2', 'LEFT');
        $this->db->join($this->star . ' as s3','s3.id = a.star_id_3', 'LEFT');
        $this->db->join($this->items . ' as i','i.id = ai.bari_item_id', 'LEFT');
        if(!empty($start_date) && !empty($end_date)) {
            $this->db->where('ai.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('ai.created_on <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } else {
            $this->db->where('ai.created_on >=',date('Y-m-d 00:00:00'));
            $this->db->where('ai.created_on <=',date('Y-m-d 23:59:59'));
        }
        if(!empty($cat_id)) {
            $this->db->where('ai.bari_cat_id',$cat_id);
        }
        if(!empty($item_id)) {
            $this->db->where('ai.bari_item_id',$item_id);
        }
        $this->db->where('ai.status',1);
       // $this->db->group_by('ai.bar_id');
        return $this->db->get($this->archanai_item . ' as ai')->result_array();
    }
}
?>