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

  public function select_subcontract($columns='*',$data='')
  {
    $this->db->select($columns.",GROUP_CONCAT(DISTINCT wt.type_name) as work_type_names",false)->from('sub_contracts sc');
    $this->db->join('work_contracts wc','sc.work_id=wc.work_id');
    $this->db->join('work_types wt', ("FIND_IN_SET(wt.type_id , sc.work_type_id)"),'left');
    $this->db->join('users u','sc.created_by=u.user_id','left');
    $this->db->join('sub_contractors c','c.contractor_id=sc.contractor','left');
    if(!empty($data)){
      $this->db->where($data);
    }
    $this->db->where("wc.delete_status",0);
    $this->db->where("sc.delete_status",0);
    if ($this->session->userdata('branch_id') != 0) {
      $this->db->where('wc.branch_id', $this->session->userdata('branch_id'));
    }
    $this->db->order_by("contract_id",'desc');
    $this->db->group_by("contract_id");
    $query = $this->db->get();
    return $query;

  }
  public function select_subcontract_2($columns='*',$data='')
  {
    $this->db->select($columns)->from('sub_contracts sc');
    $this->db->join('work_contracts wc','sc.work_id=wc.work_id');
    // $this->db->join('work_types wt', ("FIND_IN_SET(wt.type_id , sc.work_type_id)"),'left');
    $this->db->join('users u','sc.created_by=u.user_id','left');
    $this->db->join('sub_contractors c','c.contractor_id=sc.contractor','left');
    if(!empty($data)){
      $this->db->where($data);
    }
    $this->db->where("wc.delete_status",0);
    $this->db->where("sc.delete_status",0);
    if ($this->session->userdata('branch_id') != 0) {
      $this->db->where('wc.branch_id', $this->session->userdata('branch_id'));
    }
    $this->db->order_by("contract_id",'desc');
    $query = $this->db->get();
    return $query;

  }
  public function create_subcontract($data){
    $query = $this->db->insert('sub_contracts', $data);
    $result=array();
    $result['insert_id']=$this->db->insert_id();
    $result['status']=$query;
    return $result;
  }
  public function update_subcontract($data)
  {
    $this->db->where('contract_id', $data['contract_id']);
    $query=$this->db->update('sub_contracts', $data);
    return $query;
  }

  public function select_subcontract_work_items($columns='*',$data='')
  {
    $this->db->select($columns)->from('sub_contract_work_items scwi');
    $this->db->join('work_types wt','scwi.work_type=wt.type_id');
    $this->db->join('measure_units mu','scwi.measure_unit=mu.unit_id','left');
    $this->db->join('users u','scwi.created_by=u.user_id','left');
    $this->db->join('sub_contracts sc','scwi.subcontract_id=sc.contract_id');
    $this->db->join('work_contracts wc','sc.work_id=wc.work_id');
    if(!empty($data)){
      $this->db->where($data);
    }
    $this->db->where("wc.delete_status",0);
    $this->db->where("scwi.delete_status",0);
    $this->db->where("sc.delete_status",0);
    if ($this->session->userdata('branch_id') != 0) {
      $this->db->where('wc.branch_id', $this->session->userdata('branch_id'));
    }
    $this->db->order_by("scwi.item_id",'asc');
    $query = $this->db->get();
    return $query;
  }
  public function create_subcontract_work_item($data){
    $query = $this->db->insert('sub_contract_work_items', $data);
    $result=array();
    $result['insert_id']=$this->db->insert_id();
    $result['status']=$query;
    return $result;
  }
  public function update_subcontract_work_item($data)
  {
    $this->db->where('item_id', $data['item_id']);
    $query=$this->db->update('sub_contract_work_items', $data);
    return $query;
  }


  public function select_labours($columns="*",$data="",$order_by_1="",$order_by_2="",$group_by="",$limit_start="",$limit="")
  {
    if($columns==''){
      $columns = '*';
    }
    $this->db->select($columns)->from('sub_contract_labours sl');
    $this->db->join('sub_contractors sc','sl.contractor_id=sc.contractor_id','left');
    $this->db->join('users u','sl.created_by=u.user_id','left');
    $this->db->join('work_types wt','sl.work_type=wt.type_id','left');
    $this->db->join('sub_contractors c','c.contractor_id=sl.contractor_id','left');
    // $this->db->join('work_contracts wc', ("FIND_IN_SET(wc.work_id , sl.assigned_projects)"),'left');
    if(!empty($data)){
      $this->db->where($data);
    }
    $this->db->where("sl.delete_status",0);
    $this->db->where("sc.delete_status",0);
    if ($this->session->userdata('branch_id') != 0) {
      $this->db->where('c.branch_id', $this->session->userdata('branch_id'));
    }
    if(!empty($order_by_1)){
      $this->db->order_by($order_by_1[0],$order_by_1[1]);
    }
    if(!empty($order_by_2)){
      $this->db->order_by($order_by_2[0],$order_by_2[1]);
    }
    // $this->db->order_by('group_position','desc');
    if($group_by!=''){
      $this->db->group_by($group_by);
    }else{
      $this->db->group_by('sl.labour_id');
    }
    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_labour($data)
  {
    $this->db->where('labour_id', $data['labour_id']);
    $query=$this->db->update('sub_contract_labours', $data);
    return $query;
  }
  public function create_labour($data){
    $query = $this->db->insert('sub_contract_labours', $data);
    $result=array();
    $result['insert_id']=$this->db->insert_id();
    $result['status']=$query;
    return $result;
  }

  public function create_subcontract_labour_wage($data){
    $query = $this->db->insert('sub_contract_labour_wages', $data);
    $result=array();
    $result['insert_id']=$this->db->insert_id();
    $result['status']=$query;
    return $result;
  }
  public function update_subcontract_labour_wage($data,$where)
  {
    $query = $this->db->update('sub_contract_labour_wages', $data, $where);
    return $query;
  }
  public function select_subcontract_labour_wage($columns="*",$data="",$order_by="",$group_by="",$limit_start="",$limit="")
  {
    $this->db->select($columns)->from('sub_contract_labour_wages slw');
    $this->db->join('sub_contract_labours sl','sl.labour_id=slw.labour_id','left');
    $this->db->join('work_types wt','sl.work_type=wt.type_id','left');
    $this->db->join('bills b','b.bill_id=slw.bill_id');
    $this->db->join('sub_contracts sc','b.subcontract_id=sc.contract_id');
    $this->db->join('work_contracts wc','sc.work_id=wc.work_id');
    $this->db->join('sub_contractors c','c.contractor_id=sl.contractor_id','left');
    if(!empty($data)){
      $this->db->where($data);
    }
    if ($this->session->userdata('branch_id') != 0) {
      $this->db->where('c.branch_id', $this->session->userdata('branch_id'));
    }
    $this->db->where("slw.delete_status",0);
    $this->db->where("b.delete_status",0);
    $this->db->where("sc.delete_status",0);
    if(!empty($order_by)){
      $this->db->order_by($order_by[0],$order_by[1]);
    }
    if($group_by!=''){
      $this->db->group_by($group_by);
    }
    if($limit!=''){
      $this->db->limit($limit);
    }
    $query = $this->db->get();
    return $query;
  }

  public function create_subcontract_labour_attendance($data){

    $query = $this->db->insert('sub_contract_labour_attendance', $data);
    $result=array();
    $result['insert_id']=$this->db->insert_id();
    $result['status']=$query;
    return $result;
  }
  public function update_subcontract_labour_attendance($data,$where)
  {
    $query = $this->db->update('sub_contract_labour_attendance', $data, $where);
    return $query;
  }
  public function select_subcontract_labour_attendance($columns="*",$data="",$order_by="",$group_by="",$limit_start="",$limit="")
  {
    $this->db->select($columns)->from('sub_contract_labour_attendance sla');
    $this->db->join('sub_contract_labours sl','sl.labour_id=sla.labour_id','left');
    $this->db->join('work_types wt','sl.work_type=wt.type_id','left');
    $this->db->join('sub_contracts sc','sc.contract_id=sla.subcontract_id');
    $this->db->join('work_contracts wc','sc.work_id=wc.work_id');
    $this->db->join('sub_contractors scr','sl.contractor_id=scr.contractor_id','left');
    $this->db->join('users u','sla.created_by=u.user_id','left');
    if(!empty($data)){
      $this->db->where($data);
    }
    $this->db->where("sla.delete_status",0);
    $this->db->where("sc.delete_status",0);
    if ($this->session->userdata('branch_id') != 0) {
      $this->db->where('scr.branch_id', $this->session->userdata('branch_id'));
    }
    if(!empty($order_by)){
      $this->db->order_by($order_by[0],$order_by[1]);
    }
    if($group_by!=''){
      $this->db->group_by($group_by);
    }
    if($limit!=''){
      $this->db->limit($limit);
    }
    $query = $this->db->get();
    return $query;
  }

  public function select_contractors($columns="*",$data="")
  {
    $this->db->select($columns)->from('sub_contractors c');
    if(!empty($data)){
      $this->db->where($data);
    }
    $this->db->where("c.delete_status",0);
    if ($this->session->userdata('branch_id') != 0) {
      $this->db->where('c.branch_id', $this->session->userdata('branch_id'));
    }
    $this->db->order_by('c.contractor_id','desc');
    $query = $this->db->get();
    return $query;
  }
  public function update_contractor($data)
  {
    if ($this->session->userdata('branch_id') != 0) {
      $this->db->where('branch_id', $this->session->userdata('branch_id'));
    }
    $this->db->where('contractor_id', $data['contractor_id']);
    $query=$this->db->update('sub_contractors', $data);
    return $query;
  }
  public function create_contractor($data){
    if ($this->session->userdata('branch_id') != 0) {
      $data['branch_id'] = $this->session->userdata('branch_id');
    }
    $query = $this->db->insert('sub_contractors', $data);
    $result=array();
    $result['insert_id']=$this->db->insert_id();
    $result['status']=$query;
    return $result;
  }

  public function select_material_issue_bill($columns="*",$data="",$group_by="")
  {
    $this->db->select($columns)->from('sub_contract_material_issue_bill smb');
    $this->db->join('sub_contracts sc','sc.contract_id=smb.subcontract_id');
    $this->db->join('sub_contractors c','c.contractor_id=sc.contractor','left');
    $this->db->join('stock_locations sl','smb.from_location_id=sl.location_id');
    $this->db->join('work_contracts wc','sl.work_id=wc.work_id','left');
    $this->db->join('users u1','smb.created_by=u1.user_id','left');
    $this->db->join('users u2','smb.supervisor=u2.user_id','left');
    if(!empty($data)){
      $this->db->where($data);
    }
    $this->db->where("smb.delete_status",0);
    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("smb.bill_id",'desc');
    $query = $this->db->get();
    return $query;
  }
  public function create_material_issue_bill($data){
    $query = $this->db->insert('sub_contract_material_issue_bill', $data);
    $result=array();
    $result['insert_id']=$this->db->insert_id();
    $result['status']=$query;
    return $result;
  }
  public function update_material_issue_bill($where_data,$update_data)
  {
    if(!empty($where_data)){
      $this->db->where($where_data);
      $query=$this->db->update('sub_contract_material_issue_bill', $update_data);
      return $query;
    }
  }

  public function create_material_issue_items($data){
    $query = $this->db->insert('sub_contract_material_issue_items', $data);
    $result=array();
    $result['insert_id']=$this->db->insert_id();
    $result['status']=$query;
    return $result;
  }
  public function update_material_issue_items($where_data,$update_data)
  {
    if(!empty($where_data)){
      $this->db->where($where_data);
      $query=$this->db->update('sub_contract_material_issue_items', $update_data);
      return $query;
    }
  }

  public function select_material_issue_items($columns="*",$data="",$group_by="",$entry_approval=1)
  {
    $this->db->select($columns)->from('sub_contract_material_issue_items smi');
    $this->db->join('sub_contract_material_issue_bill smb','smi.bill_id=smb.bill_id');
    $this->db->join('sub_contracts sc','sc.contract_id=smb.subcontract_id');
    $this->db->join('sub_contractors c','c.contractor_id=sc.contractor','left');
    $this->db->join('stock_register sr','smi.stock_id=sr.stock_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('materials m','sr.material_id=m.material_id');
    $this->db->join('measure_units mu','sr.unit = mu.unit_id','left');
    $this->db->join('users u1','smb.created_by=u1.user_id','left');
    $this->db->join('users u2','smb.supervisor=u2.user_id','left');
    if(!empty($data)){
      $this->db->where($data);
    }
    $this->db->where("smi.delete_status",0);
    $this->db->where("smb.delete_status",0);
    $this->db->where("sr.delete_status",0);
    if($entry_approval!='all'){
      // $this->db->where("smb.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("smb.bill_id",'desc');
    $query = $this->db->get();
    return $query;
  }

  public function create_material_return($data){
    $query = $this->db->insert('sub_contract_material_return', $data);
    $result=array();
    $result['insert_id']=$this->db->insert_id();
    $result['status']=$query;
    return $result;
  }
  public function select_material_return($columns="*",$data="",$group_by="",$entry_approval=1)
  {
    $this->db->select($columns)->from('sub_contract_material_return smr');
    $this->db->join('sub_contract_material_issue_items smi','smr.issue_item_id=smi.item_id');
    $this->db->join('sub_contract_material_issue_bill smb','smi.bill_id=smb.bill_id');
    $this->db->join('sub_contracts sc','sc.contract_id=smb.subcontract_id');
    $this->db->join('sub_contractors c','c.contractor_id=sc.contractor','left');
    $this->db->join('stock_register sr','smi.stock_id=sr.stock_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('materials m','sr.material_id=m.material_id');
    $this->db->join('measure_units mu','sr.unit = mu.unit_id','left');
    $this->db->join('users u1','smr.created_by=u1.user_id','left');
    $this->db->join('users u2','smb.supervisor=u2.user_id','left');
    if(!empty($data)){
      $this->db->where($data);
    }
    $this->db->where("smi.delete_status",0);
    $this->db->where("smb.delete_status",0);
    $this->db->where("sr.delete_status",0);
    if($entry_approval!='all'){
      // $this->db->where("smb.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("smr.returned_date",'desc');
    $query = $this->db->get();
    return $query;
  }

  public function select_subcontract_bill_types($columns="*",$data="")
  {
    $this->db->select($columns)->from('sub_contract_bill_types');
    if(!empty($data)){
      $this->db->where($data);
    }
    $query = $this->db->get();
    return $query;
  }


  public function create_subcontract_deduction($data){
    $query = $this->db->insert('sub_contract_deductions', $data);
    $result=array();
    $result['insert_id']=$this->db->insert_id();
    $result['status']=$query;
    return $result;
  }
  public function update_subcontract_deductions($data)
  {
    $this->db->where('id', $data['id']);
    $query=$this->db->update('sub_contract_deductions', $data);
    return $query;
  }
  public function select_subcontract_deductions($columns="*",$data="")
  {
    $this->db->select($columns)->from('sub_contract_deductions sd');
    if(!empty($data)){
      $this->db->where($data);
    }
    $this->db->where('sd.delete_status',0);
    $query = $this->db->get();
    return $query;
  }
}
?>
