<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Model_sfmove extends CI_Model 
{

	private $type 	            = 'hr__movement_type';
	private $group 	            = 'hr__movement_group';
  private $detail             = 'hr__movement_group_detail';
  private $replacement        = 'hr__movement_group_replacement_detail';

  //table not hr__
  private $user               = 'user';
  private $user_detail        = 'user__employment_detail';
  private $company_department = 'company__department'; 
  function __construct()
  {
    parent::__construct();
  }

  public function getMoveType($id = null)
  {
  	if($id)$this->db->where("id", $id);
      $this->db->where_not_in('id', 1); //OFF Time Off for 2021
    	$this->db->order_by('seq asc');
    	$query = $this->db->get($this->type);
    if($query->num_rows() >= 1)
    {
		  return $query->result();
    }else{
  		return FALSE;
    }
  }

  //------------------------
  // getGroups
  //------------------------
  // 1. obselete (27 June 2018)
  // 2. Used to replace function getMovement
  //    - grouped data to id cousing some staff_name been left out
  //
  //------------------------
  public function getGroups($id = null, $customerid = null,$date = null, $start_date = null,$end_date = null,$parent = null,$type=null)
  {     
    
      $this->db->select("b.id,
                        b.customerid,
                        b.date,
                        b.description,
                        b.destination,
                        b.parentCopyid,b.status,
                        c.id as typeid,
                        c.movementType,
                        g.name as customerName,
                        d.id as departmentid,
                        d.name as departmentName,
                        h.date as replacement_date,
                        h.time_out as replacement_end,
                        h.time_in as replacement_start,
                        i.staffid, j.name as staff_name");
    

      if($id)$this->db->where('b.id',$id);
      if($customerid)$this->db->where('b.customerid',$customerid);
      $this->db->order_by('b.date', "desc");
      if($date)$this->db->where('b.date',$date);
      if($start_date)$this->db->where('b.date >=',$start_date);
      if($end_date)$this->db->where('b.date <=',$end_date);
      if($parent)$this->db->where('b.parentCopyId',$parent);
      if($type)$this->db->where('b.movementType',$type);

      $this->db->where('b.is_delete',0);
      $this->db->join($this->company_department.' d','b.department_id = d.id');
      $this->db->join('customer g','b.customerid = g.id', 'left');
      $this->db->join($this->type.' c','c.id = b.movementType');
      $this->db->join($this->replacement.' h','h.group_id = b.id','left');
      $this->db->join($this->detail.' i','i.groupid = b.id','left');
      $this->db->join('user j','j.id = i.staffid');

      $this->db->group_by('id');

      $query = $this->db->get($this->group.' b');

     if($query->num_rows() >= 1)
     {
        return $query->result();
     }else{
        return 'no result';
     }
  }

  public function getMovementStaff($id = null, $customerid = null,$date = null,$start_date = null,$end_date = null,$parent = null,$type=null,$staffid = null,$deptid = null,$groupid = null)
  {  
       $this->db->select("b.id,b.customerid,b.date,b.description,b.destination,b.parentCopyid,b.department_id as movementDept, b.created_by,b.status,
                        a.id as detailid,
                        a.staffid,a.timeOut,a.timeIn,
                        c.id as typeid,c.color_code,c.movementType,
                        d.name as staffName,
                        e.title as staffPosition,
                        f.id as departmentid,f.name as departmentName,
                        g.name as customerName,
                        h.name as creator");
    
      if($id)$this->db->where('b.id',$id);
      if($customerid)$this->db->where('b.customerid',$customerid);
      if($date)$this->db->where('b.date',$date);
	    if($start_date == NULL && $end_date == NULL) 
		  $this->db->where('b.date >',' DATE_ADD(Now(), INTERVAL- 6 MONTH)',FALSE);
	  else {
		  if($start_date)$this->db->where('b.date >=',$start_date);
		  if($end_date)$this->db->where('b.date <=',$end_date);
	  }
    //      if($start_date)$this->db->where('b.date >=',$start_date);
    //      if($end_date)$this->db->where('b.date <=',$end_date);
	  
      if($parent)$this->db->where('b.parentCopyId',$parent);
      if($type)$this->db->where('b.movementType',$type);
      if($staffid)$this->db->where('a.staffid',$staffid);
      if($deptid)$this->db->where('f.id',$deptid);
      if($groupid)$this->db->where('a.groupid',$groupid);

      $this->db->where('b.is_delete',0);

      $this->db->join($this->group.' b','a.groupid = b.id');
      $this->db->join('customer g','b.customerid = g.id','left');
      $this->db->join($this->type.' c','c.id = b.movementType','left');

      $this->db->join($this->user.' d','d.id = a.staffid','left');
      $this->db->join($this->user.' h','h.id = b.created_by','left');

      $this->db->join($this->user_detail.' e','e.user_id = d.id','left');
      $this->db->join($this->company_department.' f','f.id = e.department_id','left');
      $this->db->order_by('b.date desc');
      
      $query = $this->db->get($this->detail.' a');

     if($query->num_rows() >= 1)
     {
        $results = $query->result();
        
        return $results;
     }else{
        return 'no result';
     }
  }

  public function getMovement($id = null, $customerid = null,$date = null,$start_date = null,$end_date = null,$parent = null,$type=null,$staffid = null,$deptid = null,$groupid = null)
  {   
   
      $this->db->select("b.id,b.customerid,b.date,b.description,b.destination,b.parentCopyid,b.department_id as movementDept, b.created_by,b.status,
                        a.id as detailid,
                        a.staffid,a.timeOut,a.timeIn,
                        c.id as typeid,c.color_code,c.movementType,
                        d.name as staffName,
                        e.title as staffPosition,
                        f.id as departmentid,f.name as departmentName,
                        g.name as customerName,
                        h.name as creator,
                        i.name as movementDeptName") ;
      //				->select('(select group_concat( uu.name) from user uu, hr__movement_group_detail dd where uu.is_delete =0 and uu.id = dd.staffid and dd.groupid = b.id ) as staffNameAll')	;
    
      if($id)$this->db->where('b.id',$id);
      if($customerid)$this->db->where('b.customerid',$customerid);
      if($date)$this->db->where('b.date',$date);
      if($start_date)$this->db->where('b.date >=',$start_date);
      if($end_date)$this->db->where('b.date <=',$end_date);
      if($parent)$this->db->where('b.parentCopyId',$parent);
      if($type)$this->db->where('b.movementType',$type);
      if($staffid)$this->db->where('a.staffid',$staffid);
      if($deptid)$this->db->where('f.id',$deptid);
      if($groupid)$this->db->where('a.groupid',$groupid);

      $this->db->where('b.is_delete',0);

      $this->db->join($this->group.' b','a.groupid = b.id');
      $this->db->join('customer g','b.customerid = g.id','left');
      $this->db->join($this->type.' c','c.id = b.movementType','left');

      $this->db->join($this->user.' d','d.id = a.staffid','left');
      $this->db->join($this->user.' h','h.id = b.created_by','left');

      $this->db->join($this->user_detail.' e','e.user_id = d.id','left');
      $this->db->join($this->company_department.' f','f.id = e.department_id','left');
      $this->db->join($this->company_department.' i','i.id = b.department_id','left');

      $this->db->order_by('b.date desc');
      $this->db->group_by('id');
      
      
      $query = $this->db->get($this->detail.' a');

     if($query->num_rows() >= 1)
     {
        $results = $query->result(); //return $results;
        $constructed = array();

        foreach ($results as $row) {
          
          $row->staffName = $this->getStaff($row->id);
          array_push($constructed, $row);
        }
        return $constructed; 
     }else{
        return 'no result';
     }
  }  
  
  private function getStaff($groupid)
  {
    $this->db->select('b.name');
    $this->db->where('b.is_delete',0);
    $this->db->where('a.groupid',$groupid);

    $this->db->join($this->user.' b','b.id = a.staffid');
    $query = $this->db->get($this->detail.' a');

    return $query->result();
  }

  public function createGroup($data = array())
  {
    $this->db->insert($this->group, $data);
    return $this->db->insert_id();
  }

  public function createDetail($data = array())
  {
    $this->db->insert_batch($this->detail, $data);
    return $this->db->affected_rows();
  }

  public function removeGroup($id)
  {
      $this->db->where('id',$id);
      $this->db->delete($this->group);
  }

  public function removeDetail($groupid,$staffid=null,$detailid=null)
  {
    $this->db->where('groupid',$groupid);
    if($staffid)$this->db->where('staffid',$staffid);
    if($detailid)$this->db->where('id',$detailid);
    $this->db->delete($this->detail);
  }

  public function createMovement($groupData = array(), $detailData = array())
  {
    if($id = $this->createGroup($groupData))
    {
      foreach ($detailData as $key => $value) 
      {
        $detailData[$key]['groupid'] = $id;
      }

      if($this->createDetail($detailData))
      {
        return $id;
      }else{
        $this->removeGroup();
      }
    }else{
      return false;
    }
  }

  public function editMovement($groupdata, $detaildata, $groupid)
  {
    //----------------- from this version
    // $this->updateGroup($groupdata,$groupid);
    // foreach ($detaildata as $detail) {
      
    //   $this->updateMovementTime($detail['detailid'],$groupid, $detail['timeOut'], $detail['timeIn'],$detail['staffid']);
    // }

    // return true;
    //------------------- from this version
    $this->updateGroup($groupdata,$groupid);

    $init_movement = $this->getMovement(null,null,null,null,null,null,null,null,null,$groupid);

    foreach ($init_movement as $init) {
      $flag = false;
      foreach ($detaildata as $key => $new_movement) {
        if($init->detailid == $new_movement['detailid']){
          $flag = true;
        }
      }
      if(!$flag){
        $this->removeMovementById($init->detailid);
      }
    }

    foreach ($detaildata as $detail) {
      $this->updateMovementTime($detail['detailid'],$groupid, $detail['timeOut'], $detail['timeIn'],$detail['staffid']);
    }

    return true;

  }

  public function removeMovementById($id)
  {
    $this->db->delete($this->detail, array('id' => $id));
  }
  
  public function editReplacement($replacement_data,$groupid)
  { 
    $this->db->set($replacement_data);
    $this->db->where('group_id',$groupid);
    $this->db->update($this->replacement);

    return $this->db->affected_rows();
  }

  public function updateGroup($groupdata,$groupid)
  {
    $this->db->set($groupdata);
    $this->db->where('id',$groupid);
    $this->db->update($this->group); 

  }

  public function updateMovementTime($id = null, $groupid = null, $timeout = null, $timein = null,$staffid = null)
  {
    if($id != null){
        $this->db->set('timeOut', $timeout);
        $this->db->set('timeIn',$timein);
        if($id)$this->db->where('id',$id);
        if($groupid)$this->db->where('groupid',$groupid);
        $this->db->update($this->detail);

      return $this->db->affected_rows();
    }else{
      $detailData = array();
      $tmp = array
      (
        'groupid' =>  $groupid,
        'staffid' =>  $staffid,
        'timeOut' =>  $timeout,
        'timeIn'   =>  $timein
      );
      array_push($detailData, $tmp);
      $this->createDetail($detailData);
    }
  }


  public function updateMovement($groupid = null, $typeid = null, $customerid = null, $staffid = null, $timeOut = null, $timeIn = null)
  {

    if($groupid && ($typeid || $customerid))
    {
        if($typeid)$data['movementType']    = $typeid;
        if($customerid && $customerid != 'others')$data['customerid']  = $customerid;
        $where = array
        ('id' => $groupid);
      
        $this->db->where($where);
        if(!$this->db->update($this->group,$data))
        {
          return false;
        }
    }

    if($groupid && $staffid && ($timeOut || $timeIn))
    {
      if($timeOut && $timeIn)
      {
        $data = array
        (
          'timeOut' => $timeOut, //$timeOut
          'timeIn'  => $timeIn, //timeIn
        );
      }elseif ($timeOut) {
        $data = array(
          'timeOut' => $timeOut,
        );
      }else{
        $data = array
        (
          'timeIn'  => $timeIn,
        );
      }
      $where = array
      (
        'groupid' =>  $groupid,
        'staffid' =>  $staffid
      );
      $this->db->where($where);
      if(!$this->db->update($this->detail,$data))
      {
        return false;
      }
    }
    return true;
  }

  public function removeMovement($groupid,$staffid=null)
  {
    $this->db->set('is_delete',1);
    $this->db->where('id',$groupid);
    $this->db->update($this->group);
    // $this->removeGroup($groupid);
    // $this->removeDetail($groupid,$staffid);
  }

  public function updateStatus($groupid,$status,$update_by)
  {
    $where = array('id'=>$groupid);
    $data = array('status'=>$status,'update_status_by'=>$update_by);
    $this->db->where($where);
    if(!$this->db->update($this->group,$data)){
        return false;
    }
    return true;
  }
  
  public function create_replacement($group_id = null,$date = null,$timein = null,$timeout = null)
  {
    if($group_id != null && $date != null && $timein != null && $timeout != null){
      $data = array(
        'group_id' => $group_id,
        'date'     => $date,
        'time_out' => $timeout,
        'time_in'  => $timein 
      );
      $this->db->insert($this->replacement, $data);
      return $this->db->insert_id();
    }

    return null;
  }

    /**
   * updated  : farouq zakwan bin zulkifli
   * date     : 26 February 2019
   * - this function used in claim to fetch user movement between two date
   * - reason : function getMovement() have bugs to fetch between date
   */
  public function getUserMovementBetweenDate($date_from,$date_to,$user_id,$is_eight_hours = false)
  {
    $this->db->select('
            grp.id as group_id, grp.date as movement_date, grp.description as description, grp.destination, grp.movementType as movement_type_id,
            typ.movementType as movement_type,
            dtl.timeOut,dtl.timeIn,timediff(dtl.timeIn,dtl.timeOut) as hours,
            cust.name as client');
    $this->db->where('dtl.staffid',$user_id);
    $this->db->where('grp.is_delete',0);
    $this->db->where('grp.date >= ',date('Y-m-d',strtotime($date_from)));
    $this->db->where('grp.date <= ',date('Y-m-d',strtotime($date_to)));
    if($is_eight_hours){
      $this->db->where('timediff(dtl.timeIn,dtl.timeOut) >= ','08:00:00');
    }
    //20200119 - farid *solve user keyin until next day so hour diff become -ve 
    // else{
    //   $this->db->where('timediff(dtl.timeIn,dtl.timeOut) >= ','00:00:00');
    // }

    $this->db->join('hr__movement_group_detail dtl','dtl.groupid = grp.id','left');
    $this->db->join('hr__movement_type typ','typ.id = grp.movementType','left');
    $this->db->join('customer cust','cust.id = grp.customerid','left');

    $result = $this->db->get('hr__movement_group grp');

    return ($result->num_rows() > 0)?$result->result():null;
  }

  /**
   * updated : farouq zakwan bin zulkifli
   * date : 26 September 2019
   * - this function use only create meal alowance for field works 
   */
  public function getUserMovement($date,$user_id)
  {
      $this->db->select('a.*,b.*,c.name as client_name,c.address1,c.address2,c.postcode,c.city');
      $this->db->where('a.date = ',date('Y-m-d',strtotime($date)));
      $this->db->where('a.is_delete',0);
      $this->db->where('b.staffid',$user_id);

      $this->db->join('hr__movement_group_detail b','a.id = b.groupid','left');
      $this->db->join('customer c','c.id = a.customerid','left'); 

      $result = $this->db->get('hr__movement_group a')->result();
      return $result;
  }

//akmal 20190529 improve from getMovement for speed 
  public function getMovement2($id = null, $customerid = null,$date = null,$start_date = null,$end_date = null,$parent = null,$type=null,$staffid = null,$deptid = null,$groupid = null)
  {   
   
      $this->db->select("b.id,b.customerid,b.date,b.description,b.destination,b.parentCopyid,b.department_id as movementDept, b.created_by,b.status,
                        a.id as detailid,
                        a.staffid,a.timeOut,a.timeIn,
                        c.id as typeid,c.color_code,c.movementType,
                        d.name as staffName,
                        e.title as staffPosition,
                        f.id as departmentid,f.name as departmentName,
                        g.name as customerName,
                        h.name as creator,
                        i.name as movementDeptName") 
				->select('(select group_concat( uu.name) from user uu, hr__movement_group_detail dd where uu.is_delete =0 and uu.id = dd.staffid and dd.groupid = b.id ) as staffNameAll')	;
    
      if($id)$this->db->where('b.id',$id);
      if($customerid)$this->db->where('b.customerid',$customerid);
      if($date)$this->db->where('b.date',$date);
	  
	  if($start_date == NULL && $end_date == NULL) 
		  $this->db->where('b.date >',' DATE_ADD(Now(), INTERVAL- 6 MONTH)',FALSE);
	  else {
		  if($start_date)$this->db->where('b.date >=',$start_date);
		  if($end_date)$this->db->where('b.date <=',$end_date);
	  }	  
      if($parent)$this->db->where('b.parentCopyId',$parent);
      if($type)$this->db->where('b.movementType',$type);
      if($staffid)$this->db->where('a.staffid',$staffid);
      if($deptid)$this->db->where('f.id',$deptid);
      if($groupid)$this->db->where('a.groupid',$groupid);

      $this->db->where('b.is_delete',0);

      $this->db->join($this->group.' b','a.groupid = b.id');
      $this->db->join('customer g','b.customerid = g.id','left');
      $this->db->join($this->type.' c','c.id = b.movementType','left');

      $this->db->join($this->user.' d','d.id = a.staffid','left');
      $this->db->join($this->user.' h','h.id = b.created_by','left');

      $this->db->join($this->user_detail.' e','e.user_id = d.id','left');
      $this->db->join($this->company_department.' f','f.id = e.department_id','left');
      $this->db->join($this->company_department.' i','i.id = b.department_id','left');

      $this->db->order_by('b.date desc');
      $this->db->group_by('id');
      
      $query = $this->db->get($this->detail.' a');

     if($query->num_rows() >= 1)
     {
        $results = $query->result(); return $results;
/*        $constructed = array();

        foreach ($results as $row) {
          
          $row->staffName = $this->getStaff($row->id);
          array_push($constructed, $row);
        }
        return $constructed; */
     }else{
        return 'no result';
     }
  }

  public function getMovementStaff_detail($id = null, $customerid = null,$date = null,$start_date = null,$end_date = null,$parent = null,$type=null,$staffid = null,$deptid = null,$groupid = null)
  {  
    $this->db->select("b.id,b.customerid,b.date,b.description,b.destination,b.parentCopyid,b.department_id as movementDept, b.created_by,b.status,
      a.id as detailid,
      a.staffid,a.timeOut,a.timeIn,
      c.id as typeid,c.color_code,c.movementType,
      d.name as staffName,
      e.title as staffPosition,
      f.id as departmentid,f.name as departmentName,
      g.name as customerName,
      h.name as creator");

    if($id)$this->db->where('b.id',$id);
    if($customerid)$this->db->where('b.customerid',$customerid);
    if($date)$this->db->where('b.date',$date);
    if($start_date)$this->db->where('b.date >=',$start_date);
    if($end_date)$this->db->where('b.date <=',$end_date);

    if($parent)$this->db->where('b.parentCopyId',$parent);
    if($type)$this->db->where('b.movementType',$type);
    if($staffid)$this->db->where('a.staffid',$staffid);
    if($deptid)$this->db->where('f.id',$deptid);
    if($groupid)$this->db->where('a.groupid',$groupid);

    $this->db->where('b.is_delete',0);

    $this->db->join($this->group.' b','a.groupid = b.id');
    $this->db->join('customer g','b.customerid = g.id','left');
    $this->db->join($this->type.' c','c.id = b.movementType','left');

    $this->db->join($this->user.' d','d.id = a.staffid','left');
    $this->db->join($this->user.' h','h.id = b.created_by','left');

    $this->db->join($this->user_detail.' e','e.user_id = d.id','left');
    $this->db->join($this->company_department.' f','f.id = e.department_id','left');
    $this->db->order_by('b.date desc');

    $query = $this->db->get($this->detail.' a');

    if($query->num_rows() >= 1)
    {
      $results = $query->result();

      return $results;
    }else{
      return 'no result';
    }
  } 	
}
?>