<?php
if (!defined('BASEPATH')) exit('No direct script access allowed');
class purchase_management_model extends CI_Model {
    public function __construct() {
        parent::__construct();
        $this->purchase 		= TBL_INVENTORY_PURCHASE;
        $this->purchase_items 	= TBL_INVENTORY_PURCHASE_ITEM;
        $this->vendor	 		= TBL_INVENTORY_VENDOR;
        $this->inv_category		= TBL_INVENTORY_CATEGORY;
        $this->item 			= TBL_INVENTORY_ITEM;
        $this->unit 			= TBL_UNIT;	
		$this->purchase_receive	= TBL_INVENTORY_PURCHASE_RECEIVED;	
		$this->purchase_rec		= 'thiru_inventory_purchase_received';	
    }
    
    public function list_all() {
       $this->db->select('p.*, sup.ven_company 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->vendor . ' 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 purchase_order_list() {
       $this->db->select('p.id, p.purchase_ref_no as purchase_ref, sup.ven_company as supplier_name', FALSE);
       $this->db->join($this->vendor . ' as sup','sup.id = p.supplier_id');
       $this->db->where('p.status =',1);
       $this->db->order_by('p.purchase_ref_no','asc');
       return $this->db->get($this->purchase . ' as p')->result();
    }
	
	public function purchase_received_list() {
        $this->db->select('id, po_id as purchase_Id, purchase_ref, sum(received_qty) as received_qty');
        $this->db->order_by('purchase_ref','asc');
		$this->db->group_by('purchase_ref');
        $res = $this->db->get($this->purchase_rec)->result();    
		//echo $this->db->last_query(); exit;
        return  $res; 
    }
	
	public function purchase_received_qty($ref_no) {
        $this->db->select('sum(received_qty) as received_qty');
        $this->db->where('purchase_ref', $ref_no);
		$this->db->group_by('purchase_ref');
        $res = $this->db->get($this->purchase_rec)->row()->received_qty; 
        return  $res; 
    }
	
	public function get_purchase_rec_order_by_ref($ref_no) {
		$this->db->where('purchase_ref', $ref_no);
        $res = $this->db->get($this->purchase_rec)->row();    
		//echo $this->db->last_query(); exit;
        return  $res; 
	}
	
	public function purchase_received_order($id) {
        $this->db->select('id, po_id, received_date');
        $this->db->where('po_id', $id);
		//$this->db->group_by('created_on');
        $res = $this->db->get($this->purchase_rec)->result();  
        return  $res; 
    }
	
	public function purchase_received_order_details($id) {
        $this->db->where('por_id', $id);
        $res = $this->db->get($this->purchase_receive)->result();  
        return  $res; 
    }
	
	public function get_supplier_by_id($id) {       
        $this->db->where('id', $id);
        $res = $this->db->get($this->vendor)->row();  
        return  $res; 
    }
	
	public function get_purchase_order($id) {
        $this->db->select('created_on as po_date, SUM(quantity) as total_qty');
        $this->db->where('po_id', $id);
		$this->db->group_by('po_id');
        $res = $this->db->get($this->purchase_items)->row();    
		//echo $this->db->last_query(); exit;
        return  $res; 
    }
	
	public function get_supplier_data($id) {
        $this->db->select('v.ven_company as supplier');
		$this->db->join($this->vendor .' as v ','v.id = p.supplier_id');
        $this->db->where('p.id', $id);
        $res = $this->db->get($this->purchase .' as p')->row();    
        return  $res; 
    }
	
	public function get_supplier_name($id) {
        $this->db->select('ven_company');
        $this->db->where('id', $id);
        return $this->db->get($this->vendor)->row()->ven_company;
    }
	
	public function purchase_received_items($item_id) {
        $this->db->where('item_Id', $item_id);
        $this->db->order_by('id','asc');
        $res = $this->db->get($this->purchase_receive)->result();
        return  $res; 
    }
    
    public function supplier_list() {
        $this->db->where('status',1);
        return $this->db->get($this->vendor)->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_category_name($id) {
		$this->db->select('name');
        $this->db->where('id', $id);
        return $this->db->get($this->inv_category)->row()->name;
    }
	
	public function get_item_details($id) {
		$this->db->select('item_name');
        $this->db->where('id', $id);
        return $this->db->get($this->item)->row();
    }
	
	public function get_item_name($id) {
		$this->db->select('item_name');
        $this->db->where('id', $id);
        return $this->db->get($this->item)->row()->item_name;
    }
	
    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_itemname_by_id($id) {
        $this->db->select('item_name');
        $this->db->where('id',$id);       
        return $this->db->get($this->item)->row();
    }
    
    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_receive($data){  
        $this->db->insert($this->purchase_rec, $data);  
        return $this->db->insert_id();
    }
	
	public  function insert_receive_qty($data){  
        $this->db->insert($this->purchase_receive, $data);  
        return $this->db->insert_id();
    }
    
    public function insert_po_item($data){
        $this->db->insert($this->purchase_items, $data);
        return $this->db->insert_id();
    }
	
	public function update_receive_qty($data, $id){
        $this->db->where('id', $id);
        $this->db->update($this->purchase_rec, $data);
        return $this->db->affected_rows();
    }
    
    public function update_po_item($data, $id){
        $this->db->where('id', $id);
        $this->db->update($this->purchase_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.ven_company 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->vendor . ' 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, SUM(pr.received_qty) as received_qty');
        $this->db->join($this->unit . ' as u','u.id = pi.uom','left');
		$this->db->join($this->purchase_receive . ' as pr','pr.purchase_Id = pi.po_id AND pr.item_Id = pi.item_id','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');
		$this->db->group_by('pi.item_Id');
        $res = $this->db->get($this->purchase_items . ' as pi')->result();    
		//echo $this->db->last_query(); exit;
        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->purchase_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->purchase_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->purchase_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->purchase_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->purchase_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->purchase_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();
    }
	
	public function get_last_purchase_ref_no() {
        $this->db->select("purchase_ref_no");	
		$this->db->order_by("purchase_ref_no", "desc");
		$this->db->limit('1');
        return $this->db->get($this->purchase)->row()->purchase_ref_no;
    }
	
	public function get_by_purchase_ref_no($ref_no) {
        $this->db->select('p.*, sup.ven_company 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->vendor . ' as sup','sup.id = p.supplier_id');
        $this->db->where('p.purchase_ref_no',$ref_no);
        return $this->db->get($this->purchase . ' as p')->row();
		//echo $this->db->last_query();
    }
}
?>