<?php
class Staff extends MY_Controller
{
	var $db_onedev;
	public function index()
	{
		echo "Staff API";
	}

	public function __construct()
	{
		parent::__construct();
		$this->db_onedev = $this->load->database("onedev", 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  M_StaffIsActive = 'Y' ";
		$sql_param = array();
		if ($nama != "") {
			if ($sql_where != "") {
				$sql_where .= " and ";
			}
			$sql_where .= " M_StaffName like ? ";
			$sql_param[] = "%$nama%";
		}
		if ($nik != "") {
			if ($sql_where != "") {
				$sql_where .= " and ";
			}
			$sql_where .= " M_StaffNIK like ? ";
			$sql_param[] = "%$nik%";
		}

		//if ($sql_where != "") $sql_where .= " and ";

		// Order masih dalam status registrasi
		//$sql_where .= " M_StaffIsActive = 'Y' ";


		$sql = "	SELECT count(*) as total
				  FROM m_staff

				  LEFT JOIN m_branch ON M_StaffM_BranchID = M_BranchID
                  LEFT JOIN m_position ON M_StaffM_PositionID = M_PositionID
				  $sql_where
				  ";
		//echo $sql;
		$query = $this->db_onedev->query($sql, $sql_param);

		$tot_count = 0;
		if ($query) {
			$tot_count = $query->result_array()[0]["total"];
		} else {
			$this->sys_error_db("m_staff count", $this->db_onedev);
			exit;
		}
		// CONCAT(M_DoctorPrefix, ' ', M_DoctorPrefix2, ' ', M_DoctorName, ' ', M_DoctorSuffix, ' ', M_DoctorSuffix2, ' ')
		$sql = 	"SELECT 
					  m_staff.*,
                      DATE_FORMAT(M_StaffDOB,'%d-%m-%Y') as M_StaffDOBx, 
                      M_BranchID,
					  M_StaffM_BranchID,
					  M_BranchName,
					  M_StaffM_PositionID,
                      M_PositionID,
                      M_PositionName,
					  IFNULL(M_PositionIsDoctor, 'N') as isDoctor,
					  IF(M_StaffIsCourier = 'Y','YA','TIDAK') as iskurir,
					  M_StaffM_SexID,
					  M_StaffM_SexID as M_SexID,
					  IF(M_StaffM_SexID = 'Male','Laki-laki','Perempuan') as M_SexName,
					  CASE 
					  	WHEN M_StaffM_DoctorID IS NOT NULL THEN CONCAT(M_DoctorPrefix, ' ', M_DoctorPrefix2, ' ', M_DoctorName, ' ', M_DoctorSuffix, ' ', M_DoctorSuffix2, ' ')
						WHEN M_StaffM_DoctorID <> '' THEN CONCAT(M_DoctorPrefix, ' ', M_DoctorPrefix2, ' ', M_DoctorName, ' ', M_DoctorSuffix, ' ', M_DoctorSuffix2, ' ')
						ELSE ''
					END AS doctorName,
					M_StaffM_DoctorID as doctorID
				  FROM m_staff 

				  LEFT JOIN m_branch ON M_StaffM_BranchID = M_BranchID
                  LEFT JOIN m_position ON M_StaffM_PositionID = M_PositionID
				  LEFT JOIN m_doctor ON M_StaffM_DoctorID = M_DoctorID
				  $sql_where
				  ORDER BY M_StaffName ASC
				  ";
		//echo $sql;
		$query = $this->db_onedev->query($sql, $sql_param);
		// echo $this->db_onedev->last_query();

		$rows = $query->result_array();
		if ($rows) {
			foreach ($rows as $k => $v) {
				//$rows[$k]['verification_px'] = $this->add_verification_test($v['M_StaffID']);

			}
		}


		//$this->_add_address($rows);
		$result = array("total" => $tot_count, "records" => $rows, "sql" => $this->db_onedev->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_onedev->query($query)->result_array();

		$rows['sexes'] = array(
			array('M_SexID' => "Male", "M_SexName" => "Laki-laki"),
			array('M_SexID' => "Female", "M_SexName" => "Perempuan")
		);

		$rows['religions'] = [];

		$query = "	SELECT *, COUNT(M_StaffID) as used
FROM (SELECT m_position.*,M_StaffID
FROM
m_position
LEFT JOIN m_staff ON M_PositionID = M_StaffM_PositionID AND M_StaffIsActive = 'Y'
WHERE M_PositionIsActive = 'Y') a
GROUP BY M_PositionID
";
		//echo $query;
		$rows['positions'] = $this->db_onedev->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_onedev->query(
				$sql,
				array(
					$name_position,
					$userid
				)
			);
			//echo $query;
			if (!$query) {
				$this->sys_error_db("m_position insert");
				exit;
			}
			$rows = [];
			$query = "	SELECT *, COUNT(M_StaffID) as used
FROM (SELECT m_position.*,M_StaffID
FROM
m_position
LEFT JOIN m_staff ON M_PositionID = M_StaffM_PositionID AND M_StaffIsActive = 'Y'
WHERE M_PositionIsActive = 'Y') a
GROUP BY M_PositionID
				 ";
			//echo $query;
			$rows['positions'] = $this->db_onedev->query($query)->result_array();
			$result = array("total" => 1, "records" => $rows);
			$this->sys_ok($result);
			$last_id = $this->db_onedev->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_onedev->query(
				$sqlstaff,
				array(
					$name_staff,
					$userid,
					$id_staff
				)
			);
			//  echo $query;
			if (!$querystaff) {
				$this->sys_error_db("m_position update");
				exit;
			}
			$rows = [];
			$query = "	SELECT *, COUNT(M_StaffID) as used
FROM (SELECT m_position.*,M_StaffID
FROM
m_position
LEFT JOIN m_staff ON M_PositionID = M_StaffM_PositionID AND M_StaffIsActive = 'Y'
WHERE M_PositionIsActive = 'Y') a
GROUP BY M_PositionID";
			//echo $query;
			$rows['positions'] = $this->db_onedev->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_onedev->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_onedev->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_onedev->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_onedev);
			exit;
		}

		$sql = "
			SELECT *
			FROM m_city
			WHERE 
			M_CityName like ?
			AND M_CityIsActive = 'Y'
			ORDER BY M_CityName DESC
		  ";
		$query = $this->db_onedev->query($sql, array($q['search']));

		if ($query) {
			$rows = $query->result_array();
			//echo $this->db_onedev->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_onedev);
			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_onedev->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['M_StaffDOB']));
		$iscourier = $prm['M_StaffIsCourier'];
		$userid = $this->sys_user["M_UserID"];
		$doctorid = 0;
		$sql = "SELECT M_PositionIsDoctor as isdoctor FROM m_position
				WHERE M_PositionID = '{$prm['M_StaffM_PositionID']}' AND M_PositionIsActive = 'Y' LIMIT 1";
		$rows = $this->db_onedev->query($sql);
		$dataPosition = $rows->result_array();
		if (count($dataPosition) > 0) {
			if ($dataPosition[0]['isdoctor'] == 'Y') {
				$doctorid = $prm['M_StaffM_DoctorID'];
			}
		}

		$query = "UPDATE m_staff SET
					M_StaffM_BranchID = '{$prm['M_StaffM_BranchID']}',
					M_StaffName = '{$prm['M_StaffName']}',
					M_StaffDOB = '{$pdob}',
					M_StaffM_SexID = '{$prm['M_StaffM_SexID']}',
					M_StaffM_ReligionID = '{$prm['M_StaffM_ReligionID']}',
					M_StaffAddress = '{$prm['M_StaffAddress']}',
					M_StaffM_CityID = '{$prm['M_StaffM_CityID']}',
					M_StaffM_SubareaID = '{$prm['M_StaffM_SubareaID']}',
					M_StaffHP = '{$prm['M_StaffHP']}',
					M_StaffPhone = '{$prm['M_StaffPhone']}',
					M_StaffM_PositionID = '{$prm['M_StaffM_PositionID']}',
					M_StaffNIK = '{$prm['M_StaffNIK']}',
					M_StaffIDNumber = '{$prm['M_StaffIDNumber']}',
					M_StaffBlood = '{$prm['M_StaffBlood']}',
                    M_StaffStudy = '{$prm['M_StaffStudy']}',
                    M_StaffStartDate = '{$prm['M_StaffStartDate']}',
                    M_StaffEndDate = '{$prm['M_StaffEndDate']}',
                    M_StaffTimeWork = '{$prm['M_StaffTimeWork']}',
                    M_StaffTimeWorkSaturday = '{$prm['M_StaffTimeWorkSaturday']}',
					M_StaffM_DoctorID = '{$doctorid}',
					M_StaffIsCourier = '{$iscourier}',
					M_StaffUserID = '{$userid}'
                    
				WHERE
					M_StaffID = '{$prm['M_StaffID']}'
				";
		//echo $query;
		$rows = $this->db_onedev->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['M_StaffDOB']));
		$iscourier = $prm['M_StaffIsCourier'];
		$userid = $this->sys_user["M_UserID"];
		$doctorid = 0;
		$sql = "SELECT M_PositionIsDoctor as isdoctor FROM m_position
				WHERE M_PositionID = '{$prm['M_StaffM_PositionID']}' AND M_PositionIsActive = 'Y' LIMIT 1";
		$rows = $this->db_onedev->query($sql);
		$dataPosition = $rows->result_array();
		if (count($dataPosition) > 0) {
			if ($dataPosition[0]['isdoctor'] == 'Y') {
				$doctorid = $prm['M_StaffM_DoctorID'];
			}
		}
		$query = "INSERT INTO m_staff (
						M_StaffM_BranchID,
                        M_StaffName,
                        M_StaffDOB,
                        M_StaffM_SexID,
                        M_StaffM_ReligionID,
						M_StaffAddress,
						M_StaffM_CityID,
						M_StaffM_SubareaID,
                        M_StaffHP,
                        M_StaffPhone,
                        M_StaffM_PositionID,
                        M_StaffNIK,
						M_StaffIDNumber,
                        M_StaffBlood,
                        M_StaffStudy,
                        M_StaffStartDate ,
                        M_StaffEndDate,
                        M_StaffTimeWork,
                        M_StaffTimeWorkSaturday,
						M_StaffIsCourier,
						M_StaffUserID,
						M_StaffM_DoctorID
					)
					VALUES(
						'{$prm['M_StaffM_BranchID']}',
                        '{$prm['M_StaffName']}',
                        '{$pdob}',
                        '{$prm['M_StaffM_SexID']}',
                        '{$prm['M_StaffM_ReligionID']}',
						'{$prm['M_StaffAddress']}',
						'{$prm['M_StaffM_CityID']}',
						'{$prm['M_StaffM_SubareaID']}',
                        '{$prm['M_StaffHP']}',
                        '{$prm['M_StaffPhone']}',
                        '{$prm['M_StaffM_PositionID']}',
                        '{$prm['M_StaffNIK']}',
						'{$prm['M_StaffIDNumber']}',
                        '{$prm['M_StaffBlood']}',
                        '{$prm['M_StaffStudy']}',
                        '{$prm['M_StaffStartDate']}',
                        '{$prm['M_StaffEndDate']}',
                        '{$prm['M_StaffTimeWork']}',
                        '{$prm['M_StaffTimeWorkSaturday']}',
						'{$iscourier}',
						'{$userid}',
						'{$doctorid}'
					)
				";
		//echo $query;
		$rows = $this->db_onedev->query($query);
		$last_id = $this->db_onedev->insert_id();
		if ($rows) {
			if ($iscourier == 'Y') {
				$querycourier = "INSERT INTO m_courier(M_CourierM_StaffID,M_CourierCreated,M_CourierLastUpdated,M_CourierUserID)      
					VALUES('{$last_id}',now(),now(),'{$userid}')
			";
				$rows = $this->db_onedev->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 m_staff SET
					M_StaffIsActive = 'N'
				WHERE
					M_StaffID = '{$prm['M_StaffID']}'
				";
		//echo $query;
		$rows = $this->db_onedev->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 m_staffaddress.*,
					M_KelurahanName,
					M_DistrictID,
					M_DistrictName,
					M_CityID,
					M_CityName,
					'' as action
					FROM m_staffaddress
					JOIN m_kelurahan ON M_StaffAddressM_KelurahanID = M_KelurahanID
					JOIN m_district ON M_KelurahanM_DistrictID = M_DistrictID
					JOIN m_city ON M_DistrictM_CityID = M_CityID
					WHERE	
						M_StaffAddressIsActive = 'Y' AND M_StaffAddressM_StaffID = ?
				";
		//echo $query;
		$rows = $this->db_onedev->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_onedev->query("SELECT COUNT(*) as countx FROM m_staffaddress WHERE M_StaffAddressM_StaffID = '{$prm['M_StaffAddressM_StaffID']}' AND M_StaffAddressIsActive = 'Y'")->row()->countx;

		//echo $this->db_onedev->last_query();
		if ($count_addrs == 0) {
			$prm['M_StaffAddressNote'] = 'Utama';
		} else {
			$count_addrs_utama = $this->db_onedev->query("SELECT COUNT(*) as countx FROM m_staffaddress WHERE M_StaffAddressM_StaffID = '{$prm['M_StaffAddressM_StaffID']}' AND M_StaffAddressNote = 'Utama' AND M_StaffAddressIsActive = 'Y'")->row()->countx;
			if ($count_addrs_utama > 0 && strtolower($prm['M_StaffAddressNote']) == 'utama') {
				$rx = date('YmdHis');
				$prm['M_StaffAddressNote'] = 'Utama_' . $rx;
			}
		}
		$query = "INSERT INTO m_staffaddress (
						M_StaffAddressM_StaffID,
						M_StaffAddressNote,
						M_StaffAddressDescription,
						M_StaffAddressM_KelurahanID,
						M_StaffAddressCreated
					)
					VALUES(
						'{$prm['M_StaffAddressM_StaffID']}',
						'{$prm['M_StaffAddressNote']}',
						'{$prm['M_StaffAddressDescription']}',
						'{$prm['M_StaffAddressM_KelurahanID']}',
						NOW()
					)
				";
		//echo $query;
		$rows = $this->db_onedev->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 m_staffaddress SET 
						M_StaffAddressM_StaffID = '{$prm['M_StaffAddressM_StaffID']}',
						M_StaffAddressNote = '{$prm['M_StaffAddressNote']}',
						M_StaffAddressDescription = '{$prm['M_StaffAddressDescription']}',
						M_StaffAddressM_KelurahanID = '{$prm['M_StaffAddressM_KelurahanID']}'
					WHERE
						M_StaffAddressID = '{$prm['M_StaffAddressID']}'
				";
		//echo $query;
		$rows = $this->db_onedev->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 m_staffaddress SET 
						M_StaffAddressIsActive = 'N'
					WHERE
						M_StaffAddressID = '{$prm['M_StaffAddressID']}'
				";
		//echo $query;
		$rows = $this->db_onedev->query($query);

		$result = array(
			"total" => 1,
			"records" => array('status' => 'OK')
		);
		$this->sys_ok($result);
		exit;
	}
	function getdoctor()
	{
		if (!$this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$search =  $prm['search'];
		$query = "	SELECT M_DoctorID, 
					M_DoctorID doctorID,
					M_DoctorPrefix,
					M_DoctorPrefix2,
					M_DoctorName,
					M_DoctorSuffix,
					M_DoctorSuffix2, 
					M_DoctorCode ,
					CONCAT(M_DoctorPrefix,' ',M_DoctorPrefix2, ' ', M_DoctorName, ' ',M_DoctorSuffix,' ', M_DoctorSuffix2, ' ') doctorName
					FROM m_doctor
					WHERE M_DoctorIsActive = 'Y'
					AND CONCAT(M_DoctorPrefix,' ',M_DoctorPrefix2, ' ', M_DoctorName, ' ',M_DoctorSuffix,' ', M_DoctorSuffix2, ' ') LIKE '%$search%'
				";
		//echo $query;
		$rows = $this->db_onedev->query($query)->result_array();

		$result = array(
			"total" => count($rows),
			"records" => $rows,
			"query" => $this->db_onedev->last_query()
		);
		$this->sys_ok($result);
		exit;
	}
}