<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
require_once('core_model.php');
class Delivery_Model extends Core_Model {
 
  public $table_config=array(
      'mater_table'=>'delivery_master',
      'master_table_primary_id'=>'d_id',
      'master_table_seconrary_id'=>'',
      'item_table'=>'',
      'item_table_primary_id'=>'',
      'item_table_seconrary_id'=>'',
      'sub_item_table'=>'',
      'sub_item_table_primary_id'=>'',
      'sub_item_table_seconrary_id'=>'',
      );
  
  function __construct() 
  {
    /* Call the Model constructor */
    parent::__construct($this->table_config);
  }

	function save_delivery($data)
	{
		return $this->save_master_table($data);
	}
	function save_delivery_item($data)
	{
		return $this->save_item_table($data);
	}
	function get_list_delivery() {
	    return $this->get_result_mater_record_data();
	}
	public function get_row_order_info($id)
	 {
	     return $this->get_row_array_mater_record_data_by_primary_id($id);
	 }
	public function delete_delivery($id)
	{
	    return $this->delete_master_table_by_primary_id($id);
	}
	public function delete_delivery_item($id)
	{
	    return $this->delete_item_table_by_primary_id($id);
	}

	public function disable_delivery($id)
	{
		$data = array(
			'status' => 0
		);	
	    return $this->update_master_table_by_primary_id($warehouse_id,$data);
	}
	public function enable_delivery($warehouse_id)
	{
		$data = array(
			'status' => 1
		);	
		return  $this->update_master_table_by_primary_id($warehouse_id,$data);
	}
	
	public function update_delivery_master($id,$data)
	{
		return  $this->update_master_table_by_primary_id($id,$data);
	}
	
	function update_delivery_item($id,$data){
	    	return  $this->update_item_table_by_primary_id($id,$data);
	}
	
	function get_search_vehicle($search_key_val){
	    $this->db->select("o.*");
        $this->db->from('vehicles o');
        $this->db->where("o.vehicle_status = 1  AND  o.vehicle_code LIKE '%$search_key_val%'");
        $query = $this->db->get();
        return $query->result_array();
	}
	function get_pending_delivery_list($start, $length, $search_key_val){
       	$this->db->select("o.*,u.user_first_name,u.user_last_name,w.name,w.code,m.mas_name,v.vehicle_code,v.capacity,dm.ds_name");
       	$this->db->select('ua.user_first_name AS approval_user_first_name ,ua.user_last_name AS approval_user_last_name');
        $this->db->from('delivery_master o');
        $this->db->join("vehicles v", "v.vehicle_id = o.vehicle_id", "inner");
        $this->db->join("user u", "u.user_id = o.driver_id", "inner");
        $this->db->join("warehouses w", "w.id = o.d_warehouse_id", "inner");
        $this->db->join('master_approval_status m', 'm.approval_status_id = o.d_approval_status', 'inner');
        $this->db->join('master_delivery_status dm', 'dm.ds_value = o.d_status', 'inner');
        $this->db->join('user ua', 'ua.user_id = o.approval_by', 'left'); 
        
        if ($search_key_val) {
             $this->db->where("o.d_status = 0 AND o.d_ref_no LIKE '$search_key_val%'");
             $this->db->or_where("o.d_status = 1 AND o.d_ref_no LIKE '$search_key_val%'");
        }else{
             $this->db->where("o.d_status",0);
              $this->db->or_where("o.d_status",1);
        }
		$this->db->order_by("o.d_id", "desc");
        if ($start != '' && $length != '') {
            $this->db->limit($length, $start);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            } else {
                return false;
            }
        } else {
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->num_rows();
            } else {
                return false;
            }
        }
    }
    function get_currant_delivery_list($start, $length, $search_key_val){
       	$this->db->select("o.*,u.user_first_name,u.user_last_name,w.name,w.code,m.mas_name,v.vehicle_code,v.capacity,dm.ds_name");
       	$this->db->select('ua.user_first_name AS approval_user_first_name ,ua.user_last_name AS approval_user_last_name');
        $this->db->from('delivery_master o');
        $this->db->join("vehicles v", "v.vehicle_id = o.vehicle_id", "inner");
         $this->db->join("user u", "u.user_id = o.driver_id", "inner");
        $this->db->join("warehouses w", "w.id = o.d_warehouse_id", "inner");
        $this->db->join('master_approval_status m', 'm.approval_status_id = o.d_approval_status', 'inner');
        $this->db->join('master_delivery_status dm', 'dm.ds_value = o.d_status', 'inner');
        $this->db->join('user ua', 'ua.user_id = o.approval_by', 'left'); 
        if ($search_key_val) {
             $this->db->where("o.d_status = 2 AND o.d_ref_no LIKE '$search_key_val%'");
             //$this->db->or_where("c.cus_name LIKE '$search_key_val%'");
        }else{
             $this->db->where("o.d_status",2);
        }
		$this->db->order_by("o.d_id", "desc");
        if ($start != '' && $length != '') {
            $this->db->limit($length, $start);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            } else {
                return false;
            }
        } else {
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->num_rows();
            } else {
                return false;
            }
        }
    }
	function get_all_unallocated_dispatch_list(){
       	$this->db->select("o.*,c.cus_code,c.cus_name,c.cus_phone,c.cus_address,c.cus_email,u.user_first_name,u.user_last_name,w.name,w.email,w.code,w.address,w.phone ");
        $this->db->from('dispatch_note_master o');
        $this->db->join("customer c", "c.cus_id = o.dpn_customer_id", "inner");
        $this->db->join("user u", "u.user_id = o.dpn_sale_rep_id", "inner");
        $this->db->join("warehouses w", "w.id = o.dpn_warehouse_id", "inner");
        $this->db->join("dispatch_note_item dni", "dni.dnm_id = o.dpn_id", "inner");
        $this->db->where("o.pickup_status",0);
        $this->db->where("o.dpn_status",1);
        $this->db->where("o.dpn_type_id",1);
        $this->db->group_by('o.dpn_id');
        $query = $this->db->get();
        return $query->result();
    }
	function get_pending_approved_delivery(){
       	$this->db->select("o.*,u.user_first_name,u.user_last_name,w.name,w.code,m.mas_name,v.vehicle_code,v.capacity,dm.ds_name");
       	$this->db->select('ua.user_first_name AS approval_user_first_name ,ua.user_last_name AS approval_user_last_name');
        $this->db->from('delivery_master o');
        $this->db->join("vehicles v", "v.vehicle_id = o.vehicle_id", "inner");
         $this->db->join("user u", "u.user_id = o.driver_id", "inner");
        $this->db->join("warehouses w", "w.id = o.d_warehouse_id", "inner");
        $this->db->join('master_approval_status m', 'm.approval_status_id = o.d_approval_status', 'inner');
        $this->db->join('user ua', 'ua.user_id = o.approval_by', 'left'); 
        $this->db->join('master_delivery_status dm', 'dm.ds_value = o.d_status', 'inner');
        $this->db->where("o.d_status",0);
        $this->db->where("o.d_approval_status",1);
        $query = $this->db->get();
        return $query->result();
    }
	public function update_dispatch_master($id,$data){
	     if($id>0){
	   	$this->db->where('dpn_id', $id);
		return $this->db->update('dispatch_note_master',$data);
	     }else{
	        return false;
	    }
	}
	function get_delivery_allocated_dispatch_notes($id){
       	$this->db->select("o.*,c.cus_code,c.cus_name,c.cus_phone,c.cus_address,c.cus_email,u.user_first_name,u.user_last_name,w.name,w.email,w.code,w.address,w.phone ");
        $this->db->from('dispatch_note_master o');
        $this->db->join("customer c", "c.cus_id = o.dpn_customer_id", "inner");
        $this->db->join("user u", "u.user_id = o.dpn_sale_rep_id", "inner");
        $this->db->join("warehouses w", "w.id = o.dpn_warehouse_id", "inner");
        $this->db->where("o.delivery_id",$id);
        $this->db->order_by('o.dpn_priority','desc');
        $this->db->group_by('o.dpn_id');
        $query = $this->db->get();
        return $query->result_array();
    }
    	function get_delivery_allocated_dispatch_notes_asc_order($id){
       	$this->db->select("o.*,c.cus_code,c.cus_name,c.cus_phone,c.cus_address,c.cus_email,u.user_first_name,u.user_last_name,w.name,w.email,w.code,w.address,w.phone ");
        $this->db->from('dispatch_note_master o');
        $this->db->join("customer c", "c.cus_id = o.dpn_customer_id", "inner");
        $this->db->join("user u", "u.user_id = o.dpn_sale_rep_id", "inner");
        $this->db->join("warehouses w", "w.id = o.dpn_warehouse_id", "inner");
        $this->db->where("o.delivery_id",$id);
        $this->db->order_by('o.dpn_priority','asc');
        $this->db->group_by('o.dpn_id');
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_delivery_allocated_dispatch_customer_list($id){
       	$this->db->select("o.*,c.cus_code,c.cus_name,c.cus_phone,c.cus_address,c.cus_email,u.user_first_name,u.user_last_name,w.name,w.email,w.code,w.address,w.phone ");
        $this->db->from('dispatch_note_master o');
        $this->db->join("customer c", "c.cus_id = o.dpn_customer_id", "inner");
        $this->db->join("user u", "u.user_id = o.dpn_sale_rep_id", "inner");
        $this->db->join("warehouses w", "w.id = o.dpn_warehouse_id", "inner");
        $this->db->where("o.delivery_id",$id);
        $this->db->order_by('o.dpn_priority','asc');
        $this->db->group_by('o.dpn_customer_id');
        $query = $this->db->get();
        return $query->result_array();
    }
	function get_all_unallocated_dispatch_customer_list(){
       	$this->db->select("o.*,c.cus_code,c.cus_name,c.cus_phone,c.cus_address,c.place_id,u.user_first_name,u.user_last_name,w.name,w.email,w.code,w.address,w.phone ");
        $this->db->from('dispatch_note_master o');
        $this->db->join("customer c", "c.cus_id = o.dpn_customer_id", "inner");
        $this->db->join("user u", "u.user_id = o.dpn_sale_rep_id", "inner");
        $this->db->join("warehouses w", "w.id = o.dpn_warehouse_id", "inner");
        $this->db->where("o.pickup_status",0);
        $this->db->where("o.dpn_type_id",1);
        $this->db->limit(15);
        $this->db->group_by('c.cus_id');
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_all_dilivery_allocated_dispatch_customer_list($id){
       	$this->db->select("o.*,c.cus_code,c.cus_name,c.cus_phone,c.cus_address,c.place_id,u.user_first_name,u.user_last_name,w.name,w.email,w.code,w.address,w.phone ");
        $this->db->from('dispatch_note_master o');
        $this->db->join("customer c", "c.cus_id = o.dpn_customer_id", "inner");
        $this->db->join("user u", "u.user_id = o.dpn_sale_rep_id", "inner");
        $this->db->join("warehouses w", "w.id = o.dpn_warehouse_id", "inner");
        $this->db->where("o.delivery_id",$id);
        $this->db->order_by('o.dpn_priority','asc');
        $this->db->limit(15);
        $this->db->group_by('c.cus_id');
        $query = $this->db->get();
        return $query->result_array();
    }
	
	
/*		function get_delivery_details($id)
    {
       	$this->db->select("o.*,u.user_first_name,u.user_last_name,w.name,w.code,m.mas_name,v.vehicle_code,v.capacity");
       	$this->db->select('ua.user_first_name AS approval_user_first_name ,ua.user_last_name AS approval_user_last_name');
        $this->db->from('delivery_master o');
        $this->db->join("vehicles v", "v.vehicle_id = o.vehicle_id", "inner");
         $this->db->join("user u", "u.user_id = o.driver_id", "inner");
        $this->db->join("warehouses w", "w.id = o.d_warehouse_id", "inner");
        $this->db->join('master_approval_status m', 'm.approval_status_id = o.d_approval_status', 'inner');
        $this->db->join('user ua', 'ua.user_id = o.approval_by', 'left'); 
        $this->db->where("o.d_id",$id);
        $query = $this->db->get();
        return $query->row_array();
           
    }*/
	
	
	function get_delivery_master_details($id){
       	$this->db->select("o.*,u.user_first_name,u.user_last_name,w.name,w.code,m.mas_name,v.vehicle_code,v.capacity");
       	$this->db->select('ua.user_first_name AS approval_user_first_name ,ua.user_last_name AS approval_user_last_name,dm.ds_name');
        $this->db->from('delivery_master o');
        $this->db->join("vehicles v", "v.vehicle_id = o.vehicle_id", "inner");
        $this->db->join("user u", "u.user_id = o.driver_id", "inner");
        $this->db->join("warehouses w", "w.id = o.d_warehouse_id", "inner");
        $this->db->join('master_approval_status m', 'm.approval_status_id = o.d_approval_status', 'inner');
        $this->db->join('master_delivery_status dm', 'dm.ds_value = o.d_status', 'inner');
        $this->db->join('user ua', 'ua.user_id = o.approval_by', 'left'); 
        $this->db->where("o.d_id",$id);
        $query = $this->db->get();
        return $query->row_array();
    }
   function  get_delivery_allocated_dispatch_note_product($id){
       
       $this->db->select("p.product_code,p.product_name");
       $this->db->select_sum("dni.issued_qty");
       $this->db->select_sum("dni.invoice_qty");
       $this->db->select_sum("dni.return_qy");
       $this->db->from('dispatch_note_item dni'); 
       $this->db->join("dispatch_note_master dnm", "dnm.dpn_id = dni.dnm_id", "inner");
       $this->db->join("product p", "p.product_id = dni.product_id", "inner");
       $this->db->where("dnm.delivery_id",$id);
       $this->db->group_by('dni.product_id');
        $query = $this->db->get();
        return $query->result();
   }
    public function update_dispath_priority($id,$cus_id,$data){
	     if($id>0){
	   	$this->db->where('delivery_id', $id);
	   	$this->db->where('dpn_customer_id', $cus_id);
		return $this->db->update('dispatch_note_master',$data);
	     }else{
	        return false;
	    }
	}
	function get_return_list_for_currant_delivery(){
       	$this->db->select("o.*,c.cus_code,c.cus_name,u.user_first_name,u.user_last_name,w.name,w.code,m.mas_name,r.m_rtn_type_name");
       	$this->db->select('ua.user_first_name AS approval_user_first_name ,ua.user_last_name AS approval_user_last_name');
        $this->db->from('return_master o');
        $this->db->join("customer c", "c.cus_id = o.rtn_customer_id", "inner");
        $this->db->join("user u", "u.user_id = o.rtn_sale_rep_id", "inner");
        $this->db->join("warehouses w", "w.id = o.rtn_warehouse_id", "inner");
        $this->db->join('master_approval_status m', 'm.approval_status_id = o.rtn_approval_status', 'inner');
        $this->db->join('master_return_type r', 'r.m_rtn_type_id = o.return_type_id', 'inner');
        $this->db->join('user ua', 'ua.user_id = o.approval_by', 'left');
        $this->db->where('o.rtn_status = 1 AND o.rtn_approval_status = 1 AND o.pickup_status = 0 AND allocated_dispatch_note = 0');
		$this->db->order_by("o.rtn_id", "asc");
        $query = $this->db->get();
        return $query->result();
    }
	function get_approved_return_note_for_currant_delivery(){
       	$this->db->select("dnm.*");
        $this->db->from('dispatch_note_master dnm');
        $this->db->join("delivery_master d", "d.d_id = dnm.delivery_id", "inner");
        $this->db->join("return_master r", "r.rtn_customer_id = dnm.dpn_customer_id", "inner");
        $this->db->where("d.d_status",0);
        $this->db->where("d.d_approval_status",1);
        $query = $this->db->get();
        return $query->result();
    }
	public function update_return_master($id,$data)
	{
	     if($id>0){
	   	$this->db->where('rtn_id', $id);
		return $this->db->update('return_master',$data);
	     }else{
	        return false;
	    }
	}
	function get_dispatch_note_data($id){
		$this->db->select('*');
		$this->db->from('dispatch_note_master');
		$this->db->where("dpn_id",$id);
		$query = $this->db->get();
		return $query->row_array();
	}
		function get_dispatch_note_item_data($id){
		$this->db->select('*');
		$this->db->from('dispatch_note_item');
		$this->db->where("dnm_id",$id);
		$query = $this->db->get();
		return $query->result_array();
	}
	function save_splitted_master($data){
	    $this->db->insert('splitted_dispatch_note_master',$data);
		 return $this->db->insert_id();
	}
	function save_splitted_item($data){
	    return $this->db->insert('splitted_dispatch_note_item',$data);
	}
	public function update_return_master_by_dispatch_note_id($id,$data)
	{
	     if($id>0){
	   	$this->db->where('allocated_dispatch_note', $id);
		return $this->db->update('return_master',$data);
	     }else{
	        return false;
	    }
	}
	function get_return_list_for_delivery_id($id){
       	$this->db->select("o.*,c.cus_code,c.cus_name,u.user_first_name,u.user_last_name,w.name,w.code,m.mas_name,r.m_rtn_type_name");
       	$this->db->select('ua.user_first_name AS approval_user_first_name ,ua.user_last_name AS approval_user_last_name');
        $this->db->from('return_master o');
        $this->db->join("dispatch_note_master dnm", "dnm.dpn_id = o.allocated_dispatch_note", "inner");
        $this->db->join("customer c", "c.cus_id = o.rtn_customer_id", "inner");
        $this->db->join("user u", "u.user_id = o.rtn_sale_rep_id", "inner");
        $this->db->join("warehouses w", "w.id = o.rtn_warehouse_id", "inner");
        $this->db->join('master_approval_status m', 'm.approval_status_id = o.rtn_approval_status', 'inner');
        $this->db->join('master_return_type r', 'r.m_rtn_type_id = o.return_type_id', 'inner');
        $this->db->join('user ua', 'ua.user_id = o.approval_by', 'left');
        $this->db->where('dnm.delivery_id',$id);
        $query = $this->db->get();
        return $query->result();
    }
	function get_all_product_id_relevnt_delivery($id){
	    $this->db->select('dnm.dpn_warehouse_id,dni.product_id,SUM(dni.issued_qty) AS req_qty');
        $this->db->from('dispatch_note_item dni');
        $this->db->join("dispatch_note_master dnm", "dnm.dpn_id = dni.dnm_id", "inner");
        $this->db->where('dnm.delivery_id',$id);
        $this->db->where('dni.issue_status',0);
        $this->db->group_by('dni.product_id');
        $query = $this->db->get();
        return $query->result_array();
	}
	function get_all_batch_id_relevnt_delivery($id){
	    $this->db->select('bp.batch_id,bp.batch_ref_number,bp.product_cost,bp.product_price,bp.credit_salling_price,bp.wholesale_price,bp.product_id,dnm.dpn_warehouse_id');
        $this->db->from('batch_product bp');
        $this->db->join("dispatch_note_item dni", "dni.product_id = bp.product_id", "inner");
        $this->db->join("dispatch_note_master dnm", "dnm.dpn_id = dni.dnm_id", "inner");
        $this->db->where('dnm.delivery_id',$id);
        $this->db->where('bp.batch_status',1);
        $this->db->where('bp.is_deleted',0);
        $this->db->where('dni.issue_status',0);
        $this->db->group_by('bp.batch_id');
        $this->db->order_by('bp.batch_id','asc');
        $query = $this->db->get();
        return $query->result_array();
	}
	function get_all_dispatch_note_item_list_relevnt_delivery($id){
	    $this->db->select('dnm.dpn_warehouse_id,dni.dni_id,dni.dnm_id,dni.product_id,dni.issued_qty,dni.price_important,dni.odri_id,dni.batch_id,dni.is_batch_validate');
        $this->db->from('dispatch_note_item dni');
        $this->db->join("dispatch_note_master dnm", "dnm.dpn_id = dni.dnm_id", "inner");
        $this->db->where('dnm.delivery_id',$id);
        $this->db->where('dni.issue_status',0);
        $this->db->order_by('dni.is_batch_validate','desc');
        $query = $this->db->get();
        return $query->result_array();
	}
	function save_dispatch_batch_issue_item($data){
	    return $this->db->insert('dispatch_note_batch',$data);
	}
	
    public function update_dispatch_note_item($id,$data)
	{
	     if($id>0){
	   	$this->db->where('dni_id', $id);
		return $this->db->update('dispatch_note_item',$data);
	     }else{
	        return false;
	    }
	}
	
		function get_pending_approval_delivery_list($start, $length, $search_key_val){
       	$this->db->select("o.*,u.user_first_name,u.user_last_name,w.name,w.code,m.mas_name,v.vehicle_code,v.capacity,dm.ds_name");
       	$this->db->select('ua.user_first_name AS approval_user_first_name ,ua.user_last_name AS approval_user_last_name');
        $this->db->from('delivery_master o');
        $this->db->join("vehicles v", "v.vehicle_id = o.vehicle_id", "inner");
        $this->db->join("user u", "u.user_id = o.driver_id", "inner");
        $this->db->join("warehouses w", "w.id = o.d_warehouse_id", "inner");
        $this->db->join('master_approval_status m', 'm.approval_status_id = o.d_approval_status', 'inner');
        $this->db->join('master_delivery_status dm', 'dm.ds_value = o.d_status', 'inner');
        $this->db->join('user ua', 'ua.user_id = o.approval_by', 'left'); 
        
        if ($search_key_val) {
             $this->db->where("o.d_status = 1 AND d_approval_status = 0 AND o.d_ref_no LIKE '$search_key_val%'");
           //  $this->db->or_where("o.d_status = 1 AND d_approval_status = 0 AND o.d_ref_no LIKE '$search_key_val%'");
        }else{
             $this->db->where("o.d_status = 1 AND d_approval_status = 0");
        }
		$this->db->order_by("o.d_id", "desc");
        if ($start != '' && $length != '') {
            $this->db->limit($length, $start);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            } else {
                return false;
            }
        } else {
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->num_rows();
            } else {
                return false;
            }
        }
    }
	
	 function  get_delivery_dispatch_note_item($id){
       $this->db->select("p.product_code,p.product_name,u.unit_code");
       $this->db->select("dni.*");
       $this->db->from('dispatch_note_item dni'); 
       $this->db->join("dispatch_note_master dnm", "dnm.dpn_id = dni.dnm_id", "inner");
       $this->db->join("product p", "p.product_id = dni.product_id", "inner");
       $this->db->join("mstr_unit u", "u.unit_id = p.product_unit", "inner");
       $this->db->where("dnm.delivery_id",$id);
       $this->db->group_by('dni.dni_id');
       $query = $this->db->get();
        return $query->result();
   }
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	

	
}