<?php
class Tools_model extends CI_Model
{
  function __construct()
  {
    parent::__construct();
  }


  public function select_tools($columns="*",$data="",$order_by=array())
  {
    $this->db->select($columns)->from('tools t');
    $this->db->join('tools_category tc','t.category_id=tc.cat_id');
    $this->db->join('users u','u.user_id=t.created_by');
    if(!empty($data)){
      $this->db->where($data);
    }
    if ($this->session->userdata('branch_id') != 0) {
      $this->db->where('t.branch_id', $this->session->userdata('branch_id'));
    }
    $this->db->where("t.delete_status",0);
    if(!empty($order_by)){
      $this->db->order_by($order_by[0],$order_by[1]);
    }
    $query = $this->db->get();
    return $query;
  }

  public function create_tools($data){
    if ($this->session->userdata('branch_id') != 0) {
      $data['branch_id'] = $this->session->userdata('branch_id');
    }
    $query=$this->db->insert('tools', $data);
    $result=array();
    $result['insert_id']=$this->db->insert_id();
    $result['status']=$query;
    return $result;
  }
  public function update_tools($data)
  {
    if ($this->session->userdata('branch_id') != 0) {
      $this->db->where('branch_id', $this->session->userdata('branch_id'));
    }
    $this->db->where('tool_id', $data['tool_id']);
    $query=$this->db->update('tools', $data);
    return $query;
  }

  public function select_tools_category($columns="*",$data="")
  {
    $this->db->select($columns)->from('tools_category tc');
    if(!empty($data)){
      $this->db->where($data);
    }
    $this->db->where("tc.delete_status",0);
    $query = $this->db->get();
    return $query;
  }
  public function create_tools_category($data){
    $query=$this->db->insert('tools_category', $data);
    $result=array();
    $result['insert_id']=$this->db->insert_id();
    $result['status']=$query;
    return $result;
  }
  public function update_tools_category($data)
  {
    $this->db->where('cat_id', $data['cat_id']);
    $query=$this->db->update('tools_category', $data);
    return $query;
  }


  public function select_tool_transfers($columns="*",$data="",$order_by_column="",$order_by="",$group_by="",$limit_start="",$limit="",$distinct="",$entry_approval=1)
  {
    if($distinct=='distinct'){
      $this->db->distinct();
    }
    $this->db->select($columns)->from('tools_transfers tt');
    $this->db->join('tools t','tt.tool_id=t.tool_id');
    $this->db->join('tools_category tc','t.category_id=tc.cat_id');
    $this->db->join('work_contracts wc1','tt.transfer_from=wc1.work_id','left');
    $this->db->join('work_contracts wc2','tt.transfer_to=wc2.work_id','left');
    $this->db->join('users u','tt.created_by=u.user_id');
    if(!empty($data)){
      $this->db->where($data);
    }
    $this->db->where("(tt.transfer_from=0 or (tt.transfer_from!=0 and wc1.delete_status=0))");
    $this->db->where("(tt.transfer_to=0 or (tt.transfer_to!=0 and wc2.delete_status=0))");
    $this->db->where("t.delete_status",0);
    $this->db->where("tt.delete_status",0);
    if($entry_approval!='all'){
      $this->db->where("tt.entry_approval_status",$entry_approval);
    }
    if ($this->session->userdata('branch_id') != 0) {
      $this->db->where('t.branch_id', $this->session->userdata('branch_id'));
    }
    if($order_by_column!='' && $order_by!=''){
      $this->db->order_by($order_by_column,$order_by);
    }else{
      $this->db->order_by('tt.transfer_id','desc');
    }
    if($group_by!=''){
      $this->db->group_by($group_by);
    }
    if($limit!=''){
      if($limit_start!=''){
        $this->db->limit($limit,$limit_start);
      }else{
        $this->db->limit($limit);
      }

    }
    $query = $this->db->get();
    return $query;
  }
  public function create_tool_transfer($data){
    $query=$this->db->insert('tools_transfers', $data);
    $result=array();
    $result['insert_id']=$this->db->insert_id();
    $result['status']=$query;
    return $result;
  }
  public function update_tool_transfer($data)
  {
    $this->db->where('transfer_id', $data['transfer_id']);
    $query=$this->db->update('tools_transfers', $data);
    return $query;
  }

  ///rent
  public function create_rent_tools($data){
    $query=$this->db->insert('tool_rent', $data);
    $result=array();
    $result['insert_id']=$this->db->insert_id();
    $result['status']=$query;
    return $result;
  }
  public function select_tool_rent($columns="*",$data="",$order_by_column="",$order_by="",$group_by="",$limit_start="",$limit="",$distinct="",$entry_approval=1)
  {
    if($distinct=='distinct'){
      $this->db->distinct();
    }
    $this->db->select($columns)->from('tool_rent tr');
    $this->db->join('tools t','tr.tool_id=t.tool_id');
    $this->db->join('tools_category tc','t.category_id=tc.cat_id');
    $this->db->join('work_contracts wc2','tr.rented_to=wc2.work_id','left');
    $this->db->join('users u','tr.created_by=u.user_id');
    if(!empty($data)){
      $this->db->where($data);
    }
    if ($this->session->userdata('branch_id') != 0) {
      $this->db->where('t.branch_id', $this->session->userdata('branch_id'));
    }
    $this->db->where("wc2.delete_status",0);
    $this->db->where("t.delete_status",0);
    $this->db->where("tr.delete_status",0);
    if($entry_approval!='all'){
      $this->db->where("tr.entry_approval_status",$entry_approval);
    }
    if($order_by_column!='' && $order_by!=''){
      $this->db->order_by($order_by_column,$order_by);
    }else{
      $this->db->order_by('tr.id','desc');
    }
    if($group_by!=''){
      $this->db->group_by($group_by);
    }
    if($limit!=''){
      if($limit_start!=''){
        $this->db->limit($limit,$limit_start);
      }else{
        $this->db->limit($limit);
      }
    }
    $query = $this->db->get();
    return $query;
  }
  public function update_tool_rent($data)
  {
    $this->db->where('id', $data['id']);
    $query=$this->db->update('tool_rent', $data);
    return $query;
  }

  public function create_tools_purchase($data){
    $query=$this->db->insert('tools_purchase', $data);
    $result=array();
    $result['insert_id']=$this->db->insert_id();
    $result['status']=$query;
    return $result;
  }
  public function select_tools_purchase($columns="*",$data="",$order_by_column="",$order_by="",$group_by="",$limit_start="",$limit="",$distinct="")
  {
    if($distinct=='distinct'){
      $this->db->distinct();
    }
    $this->db->select($columns)->from('tools_purchase tp');
    $this->db->join('tools t','tp.tool_id=t.tool_id');
    $this->db->join('tools_category tc','t.category_id=tc.cat_id');
    if(!empty($data)){
      $this->db->where($data);
    }
    if ($this->session->userdata('branch_id') != 0) {
      $this->db->where('t.branch_id', $this->session->userdata('branch_id'));
    }
    $this->db->where("t.delete_status",0);
    $this->db->where("tp.delete_status",0);
    if($order_by_column!='' && $order_by!=''){
      $this->db->order_by($order_by_column,$order_by);
    }else{
      $this->db->order_by('tp.purchase_id','desc');
    }
    if($group_by!=''){
      $this->db->group_by($group_by);
    }
    if($limit!=''){
      if($limit_start!=''){
        $this->db->limit($limit,$limit_start);
      }else{
        $this->db->limit($limit);
      }
    }
    $query = $this->db->get();
    return $query;
  }
  public function update_tools_purchase($data)
  {
    $this->db->where('purchase_id', $data['purchase_id']);
    $query=$this->db->update('tools_purchase', $data);
    return $query;
  }

  public function calculate_total_owned_qty($tool_id)
  {
    $tool_data = $this->select_tools("",array("tool_id"=>$tool_id));
    if($tool_data->num_rows()==1){
      $tool_data = $tool_data->row();
      $opening_owned_qty = $tool_data->opening_owned_qty;
      $damaged_qty = $tool_data->damaged_qty;
      $total_purchased_qty = $this->select_tools_purchase("COALESCE(SUM(tp.purchased_qty),0) as total",array("tp.tool_id"=>$tool_id))->row()->total;
      $tool_update_data['total_purchased_qty'] = $total_purchased_qty;
      $tool_update_data['total_owned_qty'] = $opening_owned_qty+$total_purchased_qty-$damaged_qty;
      $tool_update_data['tool_id'] = $tool_id;
      $result = $this->update_tools($tool_update_data);
      return $result;
    }
  }

  // public function select_tool_racks($columns="*",$data="")
  // {
  //   $this->db->select($columns)->from('tool_racks tr');
  //   if(!empty($data)){
  //     $this->db->where($data);
  //   }
  //   $this->db->where("tr.delete_status",0);
  //   $query = $this->db->get();
  //   return $query;
  // }
  // public function create_tool_racks($data){
  //   // Run the query
  //   $query=$this->db->insert('tool_racks', $data);
  //   $result=array();
  //   $result['insert_id']=$this->db->insert_id();
  //   $result['status']=$query;
  //   return $result;
  // }
  // public function update_tool_racks($data)
  // {
  //   $this->db->where('rack_id', $data['rack_id']);
  //   $query=$this->db->update('tool_racks', $data);
  //   return $query;
  // }

}
?>
