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

  //  Stock Locations
  public function insert_stock_location($data)
  {

    if ($this->session->userdata('branch_id') != 0) {
      $data['location_branch_id'] =  $this->session->userdata('branch_id');
    }
    $query=$this->db->insert('stock_locations', $data);
    $result=array();
    $result['insert_id']=$this->db->insert_id();
    $result['status']=$query;
    return $result;
  }
  public function update_stock_location($data)
  {
    if ($this->session->userdata('branch_id') != 0) {
      $this->db->where('location_branch_id', $this->session->userdata('branch_id'));
    }
    $this->db->where('location_id', $data['location_id']);
    $query=$this->db->update('stock_locations', $data);
    return $query;
  }
  public function select_stock_locations($columns="*",$data="")
  {
    $this->db->select($columns)->from('stock_locations sl');
    $this->db->join('work_contracts wc','sl.work_id=wc.work_id','left');
    if(!empty($data)){
      $this->db->where($data);
    }
    $this->db->where("sl.delete_status",0);
    if ($this->session->userdata('branch_id') != 0) {
      $this->db->where('sl.location_branch_id', $this->session->userdata('branch_id'));
    }
    $query = $this->db->get();
    return $query;
  }

  //  Stock Register
  public function insert_stock_register($data)
  {
    $query=$this->db->insert('stock_register', $data);
    $result=array();
    $result['insert_id']=$this->db->insert_id();
    $result['status']=$query;
    return $result;
  }
  public function update_stock_register($data)
  {
    $this->db->where('stock_id', $data['stock_id']);
    $query=$this->db->update('stock_register', $data);
    return $query;
  }
  public function select_stock_register($columns="*",$data="",$group_by="",$entry_approval=1,$data_type='')
  {
    $this->db->select($columns)->from('stock_register sr');
    $this->db->join('materials m','sr.material_id=m.material_id');
    $this->db->join('stock_locations sl','sr.location_id=sl.location_id');
    $this->db->join('measure_units mu','sr.unit = mu.unit_id','left');
    $this->db->join('users u','sr.created_by=u.user_id','left');
    $this->db->join('suppliers s','sr.purchase_supplier_id=s.supplier_id','left');
    if(!empty($data)){
      $this->db->where($data);
    }
    if($data_type!='all'){
      $this->db->where("sr.delete_status",0);
      $this->db->where("sl.delete_status",0);
    }
    if($entry_approval!='all'){
      $this->db->where("sr.entry_approval_status",$entry_approval);
    }
    if ($this->session->userdata('branch_id') != 0) {
      $this->db->where('sl.location_branch_id', $this->session->userdata('branch_id'));
    }
    if($group_by!=''){
      $this->db->group_by($group_by);
    }
    $this->db->order_by("sr.stock_id",'asc');
    $query = $this->db->get();
    return $query;
  }
  public function select_stock_register_2($columns="*",$data="",$group_by="",$entry_approval=1)
  {
    $this->db->select($columns)->from('stock_register sr');
    $this->db->join('materials m','sr.material_id=m.material_id');
    $this->db->join('stock_locations sl','sr.location_id=sl.location_id');
    $this->db->join('work_contracts wc','sl.work_id=wc.work_id','left');
    $this->db->join('measure_units mu','sr.unit = mu.unit_id','left');
    $this->db->join('users u','sr.created_by=u.user_id','left');
    $this->db->join('suppliers s','sr.purchase_supplier_id=s.supplier_id','left');
    if(!empty($data)){
      $this->db->where($data);
    }
    $this->db->where("sr.delete_status",0);
    $this->db->where("sl.delete_status",0);
    if($entry_approval!='all'){
      $this->db->where("sr.entry_approval_status",$entry_approval);
    }
    if ($this->session->userdata('branch_id') != 0) {
      $this->db->where('sl.location_branch_id', $this->session->userdata('branch_id'));
    }
    if($group_by!=''){
      $this->db->group_by($group_by);
    }
    $this->db->order_by("sr.stock_id",'asc');
    $query = $this->db->get();
    return $query;
  }

  //  Stock Transfer
  public function insert_stock_transfer($data)
  {
    $query=$this->db->insert('stock_transfers', $data);
    $result=array();
    $result['insert_id']=$this->db->insert_id();
    $result['status']=$query;
    return $result;
  }
  public function update_stock_transfer($data)
  {
    $this->db->where('transfer_id', $data['transfer_id']);
    $query=$this->db->update('stock_transfers', $data);
    return $query;
  }
  public function select_stock_transfers($columns="*",$data="",$group_by="",$entry_approval=1)
  {
    $this->db->select($columns)->from('stock_transfers st');
    $this->db->join('stock_register sr1','st.transferred_from_stock_id=sr1.stock_id');
    $this->db->join('stock_register sr2','st.transferred_to_stock_id=sr2.stock_id');
    $this->db->join('stock_locations sl1','st.transferred_from_loc_id=sl1.location_id');
    $this->db->join('stock_locations sl2','st.transferred_to_loc_id=sl2.location_id');
    $this->db->join('materials m','sr1.material_id=m.material_id');
    $this->db->join('material_categories mc','m.category=mc.cat_id','left');
    $this->db->join('measure_units mu','sr1.unit = mu.unit_id','left');
    $this->db->join('users u','st.created_by=u.user_id','left');
    if(!empty($data)){
      $this->db->where($data);
    }
    $this->db->where("st.delete_status",0);
    if($entry_approval!='all'){
      $this->db->where("st.entry_approval_status",$entry_approval);
    }
    if ($this->session->userdata('branch_id') != 0) {
      $this->db->where('(sl1.location_branch_id='.$this->session->userdata('branch_id').' or sl2.location_branch_id='.$this->session->userdata('branch_id').')');
    }
    if($group_by!=''){
      $this->db->group_by($group_by);
    }
    $this->db->order_by("st.transfer_id",'desc');
    $query = $this->db->get();
    return $query;
  }

  //  Stock Consumption
  public function insert_stock_consumption($data)
  {
    $query=$this->db->insert('stock_consumption', $data);
    $result=array();
    $result['insert_id']=$this->db->insert_id();
    $result['status']=$query;
    return $result;
  }
  public function update_stock_consumption($data)
  {
    $this->db->where('cons_id', $data['cons_id']);
    $query=$this->db->update('stock_consumption', $data);
    return $query;
  }
  public function select_stock_consumption($columns="*",$data="",$group_by="",$entry_approval=1)
  {
    $this->db->select($columns)->from('stock_consumption sc');
    $this->db->join('work_contracts wc','sc.work_id=wc.work_id','left');
    $this->db->join('vehicles v','sc.vehicle_id=v.id','left');
    $this->db->join('stock_register sr','sc.stock_id=sr.stock_id');
    $this->db->join('stock_locations sl','sr.location_id=sl.location_id');
    $this->db->join('materials m','sr.material_id=m.material_id');
    $this->db->join('measure_units mu','sr.unit = mu.unit_id','left');
    $this->db->join('users u','sc.created_by=u.user_id','left');
    if(!empty($data)){
      $this->db->where($data);
    }
    $this->db->where("sc.delete_status",0);
    if($entry_approval!='all'){
      $this->db->where("sc.entry_approval_status",$entry_approval);
    }
    if ($this->session->userdata('branch_id') != 0) {
      $this->db->where('sl.location_branch_id', $this->session->userdata('branch_id'));
    }
    if($group_by!=''){
      $this->db->group_by($group_by);
    }
    $this->db->order_by("sc.cons_id",'desc');
    $query = $this->db->get();
    return $query;
  }

  public function insert_stock_transfer_bill($data)
  {
    $query=$this->db->insert('stock_transfer_bills', $data);
    $result=array();
    $result['insert_id']=$this->db->insert_id();
    $result['status']=$query;
    return $result;
  }
  public function update_stock_transfer_bill($data)
  {
    $this->db->where('bill_id', $data['bill_id']);
    $query=$this->db->update('stock_transfer_bills', $data);
    return $query;
  }
  public function select_stock_transfer_bill($columns="*",$data="",$group_by="")
  {
    $this->db->select($columns)->from('stock_transfer_bills stb');
    $this->db->join('stock_locations sl1','stb.transferred_from_loc_id=sl1.location_id');
    $this->db->join('stock_locations sl2','stb.transferred_to_loc_id=sl2.location_id');
    $this->db->join('work_contracts wc1','sl1.work_id=wc1.work_id','left');
    $this->db->join('work_contracts wc2','sl2.work_id=wc2.work_id','left');
    $this->db->join('users u','stb.created_by=u.user_id','left');
    $this->db->join('vehicles v','stb.vehicle_id=v.id','left');
    if(!empty($data)){
      $this->db->where($data);
    }
    $this->db->where("stb.delete_status",0);
    if ($this->session->userdata('branch_id') != 0) {
      $this->db->where('(sl1.location_branch_id='.$this->session->userdata('branch_id').' or sl2.location_branch_id='.$this->session->userdata('branch_id').')');
    }
    if($group_by!=''){
      $this->db->group_by($group_by);
    }
    $this->db->order_by("stb.bill_id",'desc');
    $query = $this->db->get();
    return $query;
  }


  public function insert_stock_damages($data)
  {
    $query=$this->db->insert('stock_damages', $data);
    $result=array();
    $result['insert_id']=$this->db->insert_id();
    $result['status']=$query;
    return $result;
  }
  public function update_stock_damages($data)
  {
    $this->db->where('id', $data['id']);
    $query=$this->db->update('stock_damages', $data);
    return $query;
  }
  public function select_stock_damages($columns="*",$data="",$group_by="",$entry_approval=1)
  {
    $this->db->select($columns)->from('stock_damages sd');
    $this->db->join('stock_register sr','sd.stock_id=sr.stock_id');
    $this->db->join('stock_locations sl','sd.location_id=sl.location_id');
    $this->db->join('materials m','sr.material_id=m.material_id');
    $this->db->join('measure_units mu','sr.unit = mu.unit_id','left');
    $this->db->join('users u','sd.created_by=u.user_id','left');
    if(!empty($data)){
      $this->db->where($data);
    }
    $this->db->where("sd.delete_status",0);
    // if($entry_approval!='all'){
    //   $this->db->where("sc.entry_approval_status",$entry_approval);
    // }
    // if ($this->session->userdata('branch_id') != 0) {
    //   $this->db->where('sl.location_branch_id', $this->session->userdata('branch_id'));
    // }
    if($group_by!=''){
      $this->db->group_by($group_by);
    }
    $this->db->order_by("sd.id",'desc');
    $query = $this->db->get();
    return $query;
  }

}
?>
