<?php
class Staff extends MY_Controller
{
   var $db_regional;
   public function index()
   {
      echo "Staff API";
   }
   
   public function __construct()
   {
      parent::__construct();
      $this->db_regional = $this->load->database("regional", true);
   }
   
   public function search()
   {
      $prm = $this->sys_input;
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$nik = $prm["snik"];
		$nama = $prm["nama"];
		$status = $prm["status"];
  
	   // echo $nik;
		
		$sql_where = "WHERE  Nat_StaffIsActive = 'Y' ";
		$sql_param = array();
		if ($nama != "") {
		   if ($sql_where != "") {
			  $sql_where .=" and ";
		   }
		   $sql_where .= " Nat_StaffName like ? ";
		   $sql_param[] = "%$nama%";
		}
		if ($nik != "") {
		   if ($sql_where != "") {
			  $sql_where .=" and ";
		   }
		   $sql_where .= " Nat_StaffNIK like ? ";
		   $sql_param[] = "%$nik%";
		}
	   
		//if ($sql_where != "") $sql_where .= " and ";
  
		// Order masih dalam status registrasi
		//$sql_where .= " Nat_StaffIsActive = 'Y' ";
		
  
		$sql = "	SELECT count(*) as total
				  FROM nat_staff
				  LEFT JOIN m_sex ON Nat_StaffM_SexID = M_SexID
				  LEFT JOIN m_religion ON Nat_StaffM_ReligionID = M_ReligionID
				  LEFT JOIN m_branch ON Nat_StaffM_BranchID = M_BranchID
                  LEFT JOIN m_position ON Nat_StaffM_PositionID = M_PositionID
				  $sql_where
				  ";
  //echo $sql;
		  $query = $this->db_regional->query($sql, $sql_param);
  
		  $tot_count = 0;
		  if ($query) {
			  $tot_count = $query->result_array()[0]["total"];
		  } else {
			  $this->sys_error_db("nat_staff count", $this->db_regional);
			  exit;
		  }  
		
		  $sql = 	"SELECT 
					  nat_staff.*,
                      DATE_FORMAT(Nat_StaffDOB,'%d-%m-%Y') as Nat_StaffDOBx, 
					  Nat_StaffM_SexID,
                      M_SexID,
					  m_sexname,
                      M_ReligionID,
					  Nat_StaffM_ReligionID,
					  M_ReligionName,
                      M_BranchID,
					  Nat_StaffM_BranchID,
					  M_BranchName,
					  Nat_StaffM_PositionID,
                      M_PositionID,
                      M_PositionName,
					  M_CityName,
					  M_SubareaName,
					  IF(Nat_StaffIsCourier = 'Y','YA','TIDAK') as iskurir
				  FROM nat_staff 
				  LEFT JOIN m_sex ON Nat_StaffM_SexID = M_SexID
				  LEFT JOIN m_religion ON Nat_StaffM_ReligionID = M_ReligionID
				  LEFT JOIN m_branch ON Nat_StaffM_BranchID = M_BranchID
                  LEFT JOIN m_position ON Nat_StaffM_PositionID = M_PositionID
				  LEFT JOIN m_city ON Nat_StaffM_CityID = M_CityID
				  LEFT JOIN m_subarea ON Nat_StaffM_SubareaID = M_SubareaID
				  $sql_where
				  ORDER BY Nat_StaffName ASC
				  ";
  //echo $sql;
		  $query = $this->db_regional->query($sql, $sql_param);
		 // echo $this->db_regional->last_query();
		  $rows = $query->result_array();
		  if($rows){
			foreach($rows as $k => $v){				
				//$rows[$k]['verification_px'] = $this->add_verification_test($v['Nat_StaffID']);
				
			}
		}
	  
	  
		//$this->_add_address($rows);
		$result = array("total" => $tot_count, "records" => $rows, "sql"=> $this->db_regional->last_query());
		$this->sys_ok($result);
		exit;
   }
   
   function getsexreg(){
	if (! $this->isLogin) {
		$this->sys_error("Invalid Token");
		exit;
	}
	$rows = [];
	$query ="	SELECT * 
	FROM m_branch 
	WHERE	
		M_BranchIsActive = 'Y'
";
//echo $query;
$rows['branchs'] = $this->db_regional->query($query)->result_array();

	$query ="	SELECT * 
				FROM m_sex 
				WHERE	
					M_SexIsActive = 'Y'
			";
			//echo $query;
	$rows['sexes'] = $this->db_regional->query($query)->result_array();
	$query ="	SELECT * 
				FROM m_religion 
				WHERE	
					M_ReligionIsActive = 'Y'
			";
			//echo $query;
	$rows['religions'] = $this->db_regional->query($query)->result_array();

	$query ="	SELECT *, COUNT(Nat_StaffID) as used
FROM (SELECT m_position.*,Nat_StaffID
FROM
m_position
LEFT JOIN nat_staff ON M_PositionID = Nat_StaffM_PositionID AND Nat_StaffIsActive = 'Y'
WHERE M_PositionIsActive = 'Y') a
GROUP BY M_PositionID
";
//echo $query;
$rows['positions'] = $this->db_regional->query($query)->result_array();


	
	$result = array(
		"total" => count($rows) , 
		"records" => $rows, 
	); 
	$this->sys_ok($result);
	exit;
}
public function addnewposition()
{
 try {
	 //# cek token valid
	 if (! $this->isLogin) {
		 $this->sys_error("Invalid Token");
		 exit;
	 }

	 //# ambil parameter input
	 $prm = $this->sys_input;
	 $name_position = $prm['name'];
	 $userid = $this->sys_user["M_UserID"];
		 $sql = "insert into m_position(
			 M_PositionName,
			 M_PositionUserID,
			 M_PositionCreated,
			 M_PositionLastUpdated
			 ) 
			 values(?,?,now(),now())";
		 $query = $this->db_regional->query($sql,
			 array(
				 $name_position,
				 $userid
			 )
		 );
		 //echo $query;
		 if (!$query) {
			 $this->sys_error_db("m_position insert");
			 exit;
		 } 
		 $rows = [];
		 $query ="	SELECT *, COUNT(Nat_StaffID) as used
FROM (SELECT m_position.*,Nat_StaffID
FROM
m_position
LEFT JOIN nat_staff ON M_PositionID = Nat_StaffM_PositionID AND Nat_StaffIsActive = 'Y'
WHERE M_PositionIsActive = 'Y') a
GROUP BY M_PositionID
				 ";
				 //echo $query;
		 $rows['positions'] = $this->db_regional->query($query)->result_array();
		 $result = array ("total" => 1, "records" => $rows);
		 $this->sys_ok($result);
		 $last_id = $this->db_regional->insert_id();
	 
 } catch(Exception $exc) {
	 $message = $exc->getMessage();
	 $this->sys_error($message);
 }
}
public function editposition()
{
 try {
	 //# cek token valid
	 if (! $this->isLogin) {
	 $this->sys_error("Invalid Token");
	 exit;
	 }
	 //# ambil parameter input
	 $prm = $this->sys_input;
	 $id_staff = $prm['id'];
	 $name_staff = $prm['name'];
	 $userid = $this->sys_user["M_UserID"];
	   $sqlstaff = "update m_position SET 
	   M_PositionName = ?,
	   M_PositionUserID = ?,
	   M_PositionLastUpdated = now()
		   where
		   M_PositionID = ?
		   ";
	   $querystaff = $this->db_regional->query($sqlstaff,
		   array(
			   $name_staff,
			   $userid,
			   $id_staff
		   )
	   );
	 //  echo $query;
	   if (!$querystaff) {
		   $this->sys_error_db("m_position update");
		   exit;
	   } 
	   $rows = [];
	   $query ="	SELECT *, COUNT(Nat_StaffID) as used
FROM (SELECT m_position.*,Nat_StaffID
FROM
m_position
LEFT JOIN nat_staff ON M_PositionID = Nat_StaffM_PositionID AND Nat_StaffIsActive = 'Y'
WHERE M_PositionIsActive = 'Y') a
GROUP BY M_PositionID";
			   //echo $query;
	   $rows['positions'] = $this->db_regional->query($query)->result_array();
	   $result = array ("total" => 1, "records" => $rows);
	   $this->sys_ok($result);    
 } catch(Exception $exc) {
	 $message = $exc->getMessage();
	 $this->sys_error($message);
 }
}
public function deleteposition()
{
 try {
	 //# cek token valid
	 if (! $this->isLogin) {
	 $this->sys_error("Invalid Token");
	 exit;
	 }
	 //# ambil parameter input
	 $prm = $this->sys_input;
	 $id_staff = $prm['id'];
	 $userid = $this->sys_user["M_UserID"];
	   $sqlstaff = "update m_position SET 
	   M_PositionIsActive = 'N',
	   M_PositionUserID = ?,
	   M_PositionLastUpdated = now()
		   where
		   M_PositionID = ?
		   ";
	   $querystaff = $this->db_regional->query($sqlstaff,
		   array(
			   $userid,
			   $id_staff
		   )
	   );
	 //  echo $query;
	   if (!$querystaff) {
		   $this->sys_error_db("m_position update");
		   exit;
	   } 
	   $rows = [];
	   $query ="	SELECT * 
				   FROM m_position 
				   WHERE	
					   M_PositionIsActive = 'Y'
			   ";
			   //echo $query;
	   $rows['positions'] = $this->db_regional->query($query)->result_array();
	   $result = array ("total" => 1, "records" => $rows);
	   $this->sys_ok($result);    
 } catch(Exception $exc) {
	 $message = $exc->getMessage();
	 $this->sys_error($message);
 }
}
	function searchcity(){
		if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }
		$prm = $this->sys_input;

		$max_rst = 12;
		$tot_count =0;

		$q = [
			'search'     => '%'
		];

		if ($prm['search'] != '')
		{
			$q['search'] = "%{$prm['search']}%";  
		}

		  // QUERY TOTAL
		$sql = "SELECT count(*) as total
				FROM m_city
				WHERE 
				M_CityName like ?
				AND M_CityIsActive = 'Y'";
		$query = $this->db_regional->query($sql,$q['search']);
		//echo $query;
		if ($query) { 
			$tot_count = $query->result_array()[0]["total"]; 
		} 
		else {
			$this->sys_error_db("m_city count",$this->db_regional);
			exit;
		}

		$sql = "
			SELECT *
			FROM m_city
			WHERE 
			M_CityName like ?
			AND M_CityIsActive = 'Y'
			ORDER BY M_CityName DESC
		  ";
		  $query = $this->db_regional->query($sql, array($q['search']));
			
		  if ($query) { 
			 $rows = $query->result_array();
			 //echo $this->db_regional->last_query();
			 $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
			 $this->sys_ok($result);
		  } 
		  else {
			 $this->sys_error_db("m_city rows",$this->db_regional);
			 exit;
		  }
	}
	function getsubarea(){
		$prm = $this->sys_input;
		$query ="	SELECT * 
					FROM m_subarea 
					WHERE	
						M_SubareaIsActive = 'Y' AND M_SubareaM_CityID = ?
				";
				//echo $query;
		$rows = $this->db_regional->query($query,array($prm['id']))->result_array();
		
		$result = array(
			"total" => count($rows) , 
			"records" => $rows, 
		); 
		$this->sys_ok($result);
		exit;
	}

	
	function save(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$pdob = date('Y-m-d',strtotime($prm['Nat_StaffDOB']));
		$iscourier = $prm['Nat_StaffIsCourier'];
		$userid = $this->sys_user["M_UserID"];
		$query ="UPDATE nat_staff SET
					Nat_StaffM_BranchID = '{$prm['Nat_StaffM_BranchID']}',
					Nat_StaffName = '{$prm['Nat_StaffName']}',
					Nat_StaffDOB = '{$pdob}',
					Nat_StaffM_SexID = '{$prm['Nat_StaffM_SexID']}',
					Nat_StaffM_ReligionID = '{$prm['Nat_StaffM_ReligionID']}',
					Nat_StaffAddress = '{$prm['Nat_StaffAddress']}',
					Nat_StaffM_CityID = '{$prm['Nat_StaffM_CityID']}',
					Nat_StaffM_SubareaID = '{$prm['Nat_StaffM_SubareaID']}',
					Nat_StaffHP = '{$prm['Nat_StaffHP']}',
					Nat_StaffPhone = '{$prm['Nat_StaffPhone']}',
					Nat_StaffM_PositionID = '{$prm['Nat_StaffM_PositionID']}',
					Nat_StaffNIK = '{$prm['Nat_StaffNIK']}',
					Nat_StaffBlood = '{$prm['Nat_StaffBlood']}',
                    Nat_StaffStudy = '{$prm['Nat_StaffStudy']}',
                    Nat_StaffStartDate = '{$prm['Nat_StaffStartDate']}',
                    Nat_StaffEndDate = '{$prm['Nat_StaffEndDate']}',
                    Nat_StaffTimeWork = '{$prm['Nat_StaffTimeWork']}',
                    Nat_StaffTimeWorkSaturday = '{$prm['Nat_StaffTimeWorkSaturday']}',
					Nat_StaffIsCourier = '{$iscourier}',
					Nat_StaffUserID = '{$userid}'
                    
				WHERE
					Nat_StaffID = '{$prm['Nat_StaffID']}'
				";
				//echo $query;
		$rows = $this->db_regional->query($query);
		if($rows){
			}
		$result = array(
			"total" => 1 , 
			"records" => array('status'=>'OK')
		); 
		$this->sys_ok($result);
		exit;
	}
	
	function newstaff(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$pdob = date('Y-m-d',strtotime($prm['Nat_StaffDOB']));
		$iscourier = $prm['Nat_StaffIsCourier'];
		$userid = $this->sys_user["M_UserID"];
		$query ="INSERT INTO nat_staff (
						Nat_StaffM_BranchID,
                        Nat_StaffName,
                        Nat_StaffDOB,
                        Nat_StaffM_SexID,
                        Nat_StaffM_ReligionID,
						Nat_StaffAddress,
						Nat_StaffM_CityID,
						Nat_StaffM_SubareaID,
                        Nat_StaffHP,
                        Nat_StaffPhone,
                        Nat_StaffM_PositionID,
                        Nat_StaffNIK,
                        Nat_StaffBlood,
                        Nat_StaffStudy,
                        Nat_StaffStartDate ,
                        Nat_StaffEndDate,
                        Nat_StaffTimeWork,
                        Nat_StaffTimeWorkSaturday,
						Nat_StaffIsCourier,
						Nat_StaffUserID
					)
					VALUES(
						'{$prm['Nat_StaffM_BranchID']}',
                        '{$prm['Nat_StaffName']}',
                        '{$pdob}',
                        '{$prm['Nat_StaffM_SexID']}',
                        '{$prm['Nat_StaffM_ReligionID']}',
						'{$prm['Nat_StaffAddress']}',
						'{$prm['Nat_StaffM_CityID']}',
						'{$prm['Nat_StaffM_SubareaID']}',
                        '{$prm['Nat_StaffHP']}',
                        '{$prm['Nat_StaffPhone']}',
                        '{$prm['Nat_StaffM_PositionID']}',
                        '{$prm['Nat_StaffNIK']}',
                        '{$prm['Nat_StaffBlood']}',
                        '{$prm['Nat_StaffStudy']}',
                        '{$prm['Nat_StaffStartDate']}',
                        '{$prm['Nat_StaffEndDate']}',
                        '{$prm['Nat_StaffTimeWork']}',
                        '{$prm['Nat_StaffTimeWorkSaturday']}',
						'{$iscourier}',
						'{$userid}'
					)
				";
				//echo $query;
		$rows = $this->db_regional->query($query);
		$last_id = $this->db_regional->insert_id();
		if($rows){
			if($iscourier == 'Y'){
				$querycourier ="INSERT INTO m_courier(M_CourierNat_StaffID,M_CourierCreated,M_CourierLastUpdated,M_CourierUserID)      
					VALUES('{$last_id}',now(),now(),'{$userid}')
			";
			$rows = $this->db_regional->query($querycourier);
			}
		}
		$result = array(
			"total" => 1 , 
			"records" => array('status'=>'OK'),
			"id" => $last_id 
		); 
		$this->sys_ok($result);
		exit;
	}
	
	function deletestaff(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$query ="UPDATE nat_staff SET
					Nat_StaffIsActive = 'N'
				WHERE
					Nat_StaffID = '{$prm['Nat_StaffID']}'
				";
				//echo $query;
		$rows = $this->db_regional->query($query);
		
		$result = array(
			"total" => 1 , 
			"records" => array('status'=>'OK')
		); 
		$this->sys_ok($result);
		exit;
	}
	
	function getaddress(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$query ="	SELECT nat_staffaddress.*,
					M_KelurahanName,
					M_DistrictID,
					M_DistrictName,
					M_CityID,
					M_CityName,
					'' as action
					FROM nat_staffaddress
					JOIN m_kelurahan ON Nat_StaffAddressM_KelurahanID = M_KelurahanID
					JOIN m_district ON M_KelurahanM_DistrictID = M_DistrictID
					JOIN m_city ON M_DistrictM_CityID = M_CityID
					WHERE	
						Nat_StaffAddressIsActive = 'Y' AND Nat_StaffAddressNat_StaffID = ?
				";
				//echo $query;
		$rows = $this->db_regional->query($query,array($prm['id']))->result_array();
		if($rows){
			foreach($rows as $k => $v){				
				$rows[$k]['action'] = '<v-icon color="error" @click="deleteAddress(props.item)">delete</v-icon>';
				$rows[$k]['action'] .= '<v-icon color="primary" @click="deleteAddress(props.item)">edit</v-icon>';
				
			}
		}
		$result = array(
			"total" => count($rows) , 
			"records" => $rows, 
		); 
		$this->sys_ok($result);
		exit;
	}
	function savenewaddress(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$count_addrs = $this->db_regional->query("SELECT COUNT(*) as countx FROM nat_staffaddress WHERE Nat_StaffAddressNat_StaffID = '{$prm['Nat_StaffAddressNat_StaffID']}' AND Nat_StaffAddressIsActive = 'Y'")->row()->countx;
		
		//echo $this->db_regional->last_query();
		if($count_addrs == 0){
			$prm['Nat_StaffAddressNote'] = 'Utama';
		}
		else{
			$count_addrs_utama = $this->db_regional->query("SELECT COUNT(*) as countx FROM nat_staffaddress WHERE Nat_StaffAddressNat_StaffID = '{$prm['Nat_StaffAddressNat_StaffID']}' AND Nat_StaffAddressNote = 'Utama' AND Nat_StaffAddressIsActive = 'Y'")->row()->countx;
			if($count_addrs_utama > 0 && strtolower($prm['Nat_StaffAddressNote']) == 'utama'){
				$rx = date('YmdHis');
				$prm['Nat_StaffAddressNote'] = 'Utama_'.$rx;
			}
		}
		$query ="INSERT INTO nat_staffaddress (
						Nat_StaffAddressNat_StaffID,
						Nat_StaffAddressNote,
						Nat_StaffAddressDescription,
						Nat_StaffAddressM_KelurahanID,
						Nat_StaffAddressCreated
					)
					VALUES(
						'{$prm['Nat_StaffAddressNat_StaffID']}',
						'{$prm['Nat_StaffAddressNote']}',
						'{$prm['Nat_StaffAddressDescription']}',
						'{$prm['Nat_StaffAddressM_KelurahanID']}',
						NOW()
					)
				";
				//echo $query;
		$rows = $this->db_regional->query($query);
		
		$result = array(
			"total" => 1 , 
			"records" => array('status'=>'OK')
		); 
		$this->sys_ok($result);
		exit;
	}
	
	function saveeditaddress(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		
		$query ="UPDATE nat_staffaddress SET 
						Nat_StaffAddressNat_StaffID = '{$prm['Nat_StaffAddressNat_StaffID']}',
						Nat_StaffAddressNote = '{$prm['Nat_StaffAddressNote']}',
						Nat_StaffAddressDescription = '{$prm['Nat_StaffAddressDescription']}',
						Nat_StaffAddressM_KelurahanID = '{$prm['Nat_StaffAddressM_KelurahanID']}'
					WHERE
						Nat_StaffAddressID = '{$prm['Nat_StaffAddressID']}'
				";
				//echo $query;
		$rows = $this->db_regional->query($query);
		
		$result = array(
			"total" => 1 , 
			"records" => array('status'=>'OK')
		); 
		$this->sys_ok($result);
		exit;
	}
	
	function deleteaddress(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		
		$query ="UPDATE nat_staffaddress SET 
						Nat_StaffAddressIsActive = 'N'
					WHERE
						Nat_StaffAddressID = '{$prm['Nat_StaffAddressID']}'
				";
				//echo $query;
		$rows = $this->db_regional->query($query);
		
		$result = array(
			"total" => 1 , 
			"records" => array('status'=>'OK')
		); 
		$this->sys_ok($result);
		exit;
	}


}