<?php
if (!defined('BASEPATH')) exit('No direct script access allowed');
class purchase_model extends CI_Model {
    public function __construct() {
        parent::__construct();
        $this->purchase = TBL_PURCHASE;
        $this->po_items = TBL_PO_ITEMS;
        $this->supplier = TBL_SUPPLIER;
        $this->inv_category = TBL_INV_CATEGORY;
        $this->item = TBL_INV_ITEM;
        $this->master_item = TBL_ITEM;
        $this->unit = TBL_UNIT;
    }
    
    public function list_all() {
       $this->db->select('p.*, sup.name as supplier_name, CASE p.payment WHEN 1 THEN "Unpaid" WHEN 2 THEN "Paid" ELSE "" END as payment_status, CASE p.status WHEN 1 THEN "Active" ELSE "Inactive" END as status_str', FALSE);
       $this->db->join($this->supplier . ' as sup','sup.id = p.supplier_id');
       $this->db->where('p.status !=',2);
       $this->db->order_by('p.id','desc');
       return $this->db->get($this->purchase . ' as p')->result();
    }
    
    public function supplier_list() {
        $this->db->where('status',1);
        return $this->db->get($this->supplier)->result();
    }
    
    public function category_list() {
        $this->db->where('status',1);
        $this->db->where('is_statue_cat', 0);
        return $this->db->get($this->inv_category)->result();
    }
    public function get_items_by_cat_id($category_id) {
        $this->db->select('i.id, i.item_name');
        $this->db->where('i.category_id',$category_id);
        $this->db->where('i.status',1);
        #$this->db->where('i.is_statue_item', 0);
        return $this->db->get($this->item . ' as i')->result();
    }
    
    public function get_unit_by_item_id($id) {
        $this->db->select('u.id, u.unit_name, i.quantity');
        $this->db->join($this->unit . ' as u','u.id = i.uom');
        $this->db->where('i.id',$id);
        $this->db->where('i.status',1);
        return $this->db->get($this->item . ' as i')->row();
    }
    
    public  function insert($data){  
        $this->db->insert($this->purchase, $data);  
        return $this->db->insert_id();
    }
    
    public function insert_po_item($data){
        $this->db->insert($this->po_items, $data);
        return $this->db->insert_id();
    }
    
    public function update_po_item($data, $id){
        $this->db->where('id', $id);
        $this->db->update($this->po_items, $data);
        return $this->db->affected_rows();
    }
    
    public function update($data, $id){
        $this->db->where('id', $id);
        $this->db->update($this->purchase, $data);
        return $this->db->affected_rows();
    }
    
    public function purchase_url_key_exists($url_key, $id) {
        if (!empty($id)) {
            $this->db->where('id !=', $id);
        }
        $this->db->where('url_key', $url_key);
        return $this->db->get($this->purchase)->num_rows();
    }
    
    public function get_by_id($id) {
        $this->db->select('p.*, sup.name as supplier_name, CASE p.payment WHEN 1 THEN "Unpaid" WHEN 2 THEN "Paid" ELSE "" END as payment_status, CASE p.status WHEN 1 THEN "Active" ELSE "Inactive" END as status_str', FALSE);
        $this->db->join($this->supplier . ' as sup','sup.id = p.supplier_id');
        $this->db->where('p.status !=',2);
        $this->db->where('p.id',$id);
        return $this->db->get($this->purchase . ' as p')->row();
    }
    
    public function get_items_by_po_id($id) {
        $this->db->select('pi.*, u.unit_name,');
        $this->db->join($this->unit . ' as u','u.id = pi.uom','left');
        $this->db->where('pi.po_id',$id);
        $this->db->where('pi.status',1);
        $this->db->where('pi.not_issued_qty = ', 'pi.quantity', FALSE);
        $this->db->order_by('pi.id','asc');
        $res = $this->db->get($this->po_items . ' as pi')->result();
        
        return  $res; 
    }
    
    public function get_used_items_by_po_id($id) {
        $this->db->select('pi.*, u.unit_name');
        $this->db->join($this->unit . ' as u','u.id = pi.uom','left');
        $this->db->where('pi.po_id',$id);
        //$this->db->where('pi.status',9);
        $this->db->where('pi.not_issued_qty != ', 'pi.quantity', FALSE);
        $this->db->order_by('pi.id','asc');
        $res = $this->db->get($this->po_items . ' as pi')->result();
        //echo 'Q: '.$this->db->last_query(); die;
        return  $res; 
    }
    
     public function get_view_items($id) {
        $this->db->select('pi.*, u.unit_name,');
        $this->db->join($this->unit . ' as u','u.id = pi.uom','left');
        $this->db->where('pi.po_id',$id);
        $this->db->where('pi.status !=',2);
        $this->db->order_by('pi.id','asc');
        $res = $this->db->get($this->po_items . ' as pi')->result();
        
        return  $res; 
    }
    
    public function row_delete($id,$item_id) {
       $data = array('status'=>2);
	   $this->db->where('id',$item_id);
       $this->db->update($this->po_items,$data);
       //echo "Q: ". $this->db->last_query(); die;
       
    }
    
    public function get_qtyby_id($id){
        $this->db->where('id', $id);
        $this->db->where('status !=', 2);
        return $this->db->get($this->po_items)->row();
    }
    
    public function check_valid_qty($id){
        $this->db->select('poi.quantity as poi_qty,i.quantity');
        $this->db->join($this->item . ' as i','i.id = poi.item_id');
        $this->db->where('poi.id', $id);
        return $this->db->get($this->po_items . ' as poi')->row();
    }
    
    public function get_items_by_id($id){
        $this->db->select('pi.*, u.unit_name,');
        $this->db->join($this->unit . ' as u','u.id = pi.uom');
        $this->db->where('pi.po_id',$id);
        $this->db->where('pi.status !=',2);
        $this->db->order_by('pi.id','asc');
        return $this->db->get($this->po_items . ' as pi')->result();
    }
    
    public function get_items_cat_id($category_id,$item_id) {
        $this->db->select('i.id, i.item_name, cat.name as category_name');
        $this->db->join($this->inv_category . ' as cat','cat.id = i.category_id');
        $this->db->where('i.category_id',$category_id);
        $this->db->where('i.id',$item_id);
        $this->db->where('i.status',1);
        return $this->db->get($this->item . ' as i')->row();
    }
    
    

}
?>