<?php
class XFo extends MY_Controller
{
   var $db_onedev;
   public function index()
   {
      echo "Email API";
	  /*truncate result_authorization;
		truncate result_office;
		truncate result_email;
		truncate t_orderauthorization;*/
   }
   
   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;
		}
		$number_limit = 10;
		$number_offset = ($prm['current_page'] - 1) * $number_limit ;
		
		if($prm['filter_status'] == 'ready' || $prm['filter_status'] == 'done'){
			$sql_join_result_email = '';
			$sql_having = 'AND status_done = "N"';
			$sql_where = '';
			
			
			if($prm['filter_status'] != 'ready'){
				$sql_having = ' AND status_done = "Y"';
			}
			
			if($prm['name'] != '')
				$sql_where .= " AND M_PatientName LIKE CONCAT('%','{$prm['name']}','%')";
			
			if($prm['nolab'] != '')
				$sql_where .= " AND T_OrderHeaderLabNumber LIKE CONCAT('%','{$prm['nolab']}','%')";
			
			

			$sql = "	SELECT count(*) as total 
					FROM (
						SELECT 
					T_OrderHeaderID as orderid,
					T_OrderPromiseID as promiseid,
					T_OrderDeliveryID as orderdeliveryid,
					Nat_GroupID as group_id,
					CONCAT(T_OrderHeaderID,'.',T_OrderPromiseID) as uniqid,
					T_OrderHeaderLabNumber as labnumber,
					CONCAT(IFNULL(M_TitleName,''),' ',M_PatientName) as patient_fullname,
					DATE_FORMAT(T_OrderHeaderDate,'%d-%m-%Y') as date_order,
					DATE_FORMAT(T_OrderPromiseDateTime,'%d-%m-%Y %H:%m') as date_promise,
					'' as xgroups,
					fn_sum_print_by_order_promise(T_OrderHeaderID,T_OrderPromiseID) as sum_print,
					fn_result_handling_check_done_fo(T_OrderHeaderID,T_OrderPromiseID) as status_done
					FROM t_orderdetail
					JOIN t_test ON T_OrderDetailT_TestID = T_TestID
					JOIN nat_group ON T_TestNat_GroupID = Nat_GroupID
					JOIN t_orderdelivery ON T_OrderDeliveryT_OrderHeaderID = T_OrderDetailT_OrderHeaderID AND
					T_OrderDeliveryM_DeliveryTypeID = 1 AND T_OrderDeliveryIsActive = 'Y'
					JOIN t_orderpromise ON T_OrderDetailT_OrderPromiseID = T_OrderPromiseID AND T_OrderPromiseIsActive = 'Y'
					JOIN t_orderheader ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID
					JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
					LEFT JOIN m_title ON M_PatientM_TitleID = M_TitleID
					$sql_join_result_office
					WHERE
					T_OrderDetailIsActive = 'Y' 
					
					$sql_where
					GROUP BY T_OrderHeaderID, T_OrderPromiseID
					HAVING sum_print > 0 $sql_having
					ORDER BY T_OrderHeaderLabNumber ASC
					) xs
					";
			//echo $sql;
			$query = $this->db_onedev->query($sql);
//echo $this->db->last_query();
			$tot_count = 0;
		$tot_page = 0;
		if ($query) {
			$tot_count = $query->result_array()[0]["total"];
			$tot_page = ceil($tot_count/$number_limit);
		} else {
			$this->sys_error_db("t_samplestorageout count", $this->db_onedev);
			exit;
		}  
				  
			$sql = 	"SELECT 
					T_OrderHeaderID as orderid,
					T_OrderPromiseID as promiseid,
					T_OrderDeliveryID as orderdeliveryid,
					Nat_GroupID as group_id,
					CONCAT(T_OrderHeaderID,'.',T_OrderPromiseID) as uniqid,
					T_OrderHeaderLabNumber as labnumber,
					CONCAT(IFNULL(M_TitleName,''),' ',M_PatientName) as patient_fullname,
					DATE_FORMAT(T_OrderHeaderDate,'%d-%m-%Y') as date_order,
					DATE_FORMAT(T_OrderPromiseDateTime,'%d-%m-%Y %H:%m') as date_promise,
					'' as xgroups,
					fn_sum_print_by_order_promise(T_OrderHeaderID,T_OrderPromiseID) as sum_print,
					fn_result_handling_check_done_fo(T_OrderHeaderID,T_OrderPromiseID) as status_done
					FROM t_orderdetail
					JOIN t_test ON T_OrderDetailT_TestID = T_TestID
					JOIN nat_group ON T_TestNat_GroupID = Nat_GroupID
					JOIN t_orderdelivery ON T_OrderDeliveryT_OrderHeaderID = T_OrderDetailT_OrderHeaderID AND
					T_OrderDeliveryM_DeliveryTypeID = 1 AND T_OrderDeliveryIsActive = 'Y'
					JOIN t_orderpromise ON T_OrderDetailT_OrderPromiseID = T_OrderPromiseID AND T_OrderPromiseIsActive = 'Y'
					JOIN t_orderheader ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID
					JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
					LEFT JOIN m_title ON M_PatientM_TitleID = M_TitleID
					$sql_join_result_office
					WHERE
					T_OrderDetailIsActive = 'Y' 
					
					$sql_where
					GROUP BY T_OrderHeaderID, T_OrderPromiseID
					HAVING sum_print > 0 $sql_having
					ORDER BY T_OrderHeaderLabNumber ASC
					limit $number_limit offset $number_offset";

			$query = $this->db_onedev->query($sql);
			//echo $this->db_onedev->last_query();
			$rows = $query->result_array();
			if($rows){
				foreach($rows as $k => $v){
					$sql = "SELECT IF(T_TestIsNonLab = '','LAB',T_TestIsNonLab) as xgroup,  
							SUM(T_OrderDetailPrintCount) as print_count,
							'' as received,
							fn_result_handling_get_cout_print_by_type(T_OrderDetailT_OrderHeaderID,T_OrderDetailT_OrderPromiseID,T_TestIsNonLab) as print_type,
							fn_result_handling_status_button(T_OrderDetailT_OrderHeaderID,T_OrderDetailT_OrderPromiseID,T_TestIsNonLab) as status,
							fn_result_handling_status_button_fo(T_OrderDetailT_OrderHeaderID,T_OrderDetailT_OrderPromiseID,T_TestIsNonLab) as status_fo,
							fn_result_handling_check_received_fotype(T_OrderDetailT_OrderHeaderID,T_OrderDetailT_OrderPromiseID,T_TestIsNonLab) as status_received
							FROM t_orderdetail
							JOIN t_test ON T_OrderDetailT_TestID = T_TestID
							WHERE
								T_OrderDetailT_OrderHeaderID = {$v['orderid']} AND 
								T_OrderDetailT_OrderPromiseID = {$v['promiseid']} AND 
								T_OrderDetailT_TestIsResult = 'Y' AND
								T_OrderDetailIsActive = 'Y'
							GROUP BY T_TestIsNonLab";
					//echo $sql;
					
					$rows[$k]['xgroups'] = $this->db_onedev->query($sql)->result();
				}
			}

		}else{
			
			
			if($prm['name'] != '')
				$sql_where .= " AND M_PatientName LIKE CONCAT('%','{$prm['name']}','%')";
			
			if($prm['nolab'] != '')
				$sql_where .= " AND T_OrderHeaderLabNumber LIKE CONCAT('%','{$prm['nolab']}','%')";
		  
			$sqlx = '';
			if($prm['filter_status'] === 'readyhandover'){

				$sqlx = "	
					SELECT 
						T_OrderHeaderID as orderid,
						T_OrderPromiseID as promiseid,
						T_OrderHeaderLabNumber as labnumber,
						CONCAT(IFNULL(M_TitleName,''),' ',M_PatientName) as patient_fullname,
						DATE_FORMAT(T_OrderHeaderDate,'%d-%m-%Y') as date_order,
						DATE_FORMAT(T_OrderPromiseDateTime,'%d-%m-%Y %H:%m') as date_promise,
						'' as xgroups,
						Result_OfficeStatus as status
					FROM result_office
					JOIN t_orderpromise ON Result_OfficeT_OrderPromiseID = T_OrderPromiseID
					JOIN t_orderheader ON Result_OfficeT_OrderHeaderID = T_OrderHeaderID
					JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
					LEFT JOIN m_title ON M_PatientM_TitleID = M_TitleID
					WHERE
						Result_OfficeStatus = 'RECEIVED'
						$sql_where
					GROUP BY T_OrderHeaderID, T_OrderPromiseID	
						limit $number_limit offset $number_offset
					";
			}
			else{
				$sqlx = "	
					SELECT 
						T_OrderHeaderID as orderid,
						T_OrderPromiseID as promiseid,
						T_OrderHeaderLabNumber as labnumber,
						CONCAT(IFNULL(M_TitleName,''),' ',M_PatientName) as patient_fullname,
						DATE_FORMAT(T_OrderHeaderDate,'%d-%m-%Y') as date_order,
						DATE_FORMAT(T_OrderPromiseDateTime,'%d-%m-%Y %H:%m') as date_promise,
						'' as xgroups,
						Result_OfficeStatus as status,
						Result_HandOverReceivedBy as receiver,
						DATE_FORMAT(Result_HandOverDate,'%d-%m-%Y %H:%m') as date_received
					FROM result_handover
					JOIN result_office ON Result_OfficeResult_HandOverID = Result_HandOverID AND Result_OfficeStatus = 'HANDOVER'
					JOIN t_orderpromise ON Result_OfficeT_OrderPromiseID = T_OrderPromiseID
					JOIN t_orderheader ON Result_OfficeT_OrderHeaderID = T_OrderHeaderID
					JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
					LEFT JOIN m_title ON M_PatientM_TitleID = M_TitleID
					WHERE
						Result_OfficeIsActive = 'Y'
						$sql_where
					GROUP BY Result_HandOverID	
limit $number_limit offset $number_offset					
					";
			}			
			//echo $sql;
			$sql = "	SELECT count(*) as total 
					FROM (
						$sqlx 
					) xs
					";
			
			$query = $this->db_onedev->query($sql);
			//echo $this->db_onedev->last_query();
			
			$tot_count = 0;
		$tot_page = 0;
		if ($query) {
			$tot_count = $query->result_array()[0]["total"];
			$tot_page = ceil($tot_count/$number_limit);
		} else {
			$this->sys_error_db("t_samplestorageout count", $this->db_onedev);
			exit;
		}  
				  
			$sql = 	"$sqlx";

			$query = $this->db_onedev->query($sql);
			//echo $this->db_onedev->last_query();
			$rows = $query->result_array();
			if($rows){
				foreach($rows as $k => $v){
					$sql = "SELECT IF(T_TestIsNonLab = '','LAB',T_TestIsNonLab) as xgroup,  
							0 as print_count,
							0 as print_type,
							'Y' as received,
							'N' as status,
							'N' as status_fo,
							'Y' as status_received
							FROM t_orderdetail
							JOIN t_test ON T_OrderDetailT_TestID = T_TestID
							WHERE
								T_OrderDetailT_OrderHeaderID = {$v['orderid']} AND 
								T_OrderDetailT_OrderPromiseID = {$v['promiseid']} AND 
								T_OrderDetailT_TestIsResult = 'Y' AND
								T_OrderDetailIsActive = 'Y'
							GROUP BY T_TestIsNonLab";
					//echo $sql;
					
					$rows[$k]['xgroups'] = $this->db_onedev->query($sql)->result();
				}
			}
			
		}
		
	  
		//$tot_count = 25;
		//$this->_add_address($rows);
		$result = array("total" => $tot_page, "records" => $rows, "sql"=> $this->db_onedev->last_query());
		$this->sys_ok($result);
		exit;
   }
   
	function getstaffs(){
	   if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		
		$rows = [];
		$query ="	SELECT M_StaffID as id, M_StaffName as name
					FROM m_staff 
					WHERE	
						M_StaffIsActive = 'Y'
				";
				//echo $query;
		$rows['staffs'] = $this->db_onedev->query($query)->result_array();
		
		

		$result = array(
			"total" => count($rows) , 
			"records" => $rows, 
		); 
		$this->sys_ok($result);
		exit;
   }
      
   function searchstaff(){
		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_staff
				WHERE 
				M_StaffName like ?
				AND M_StaffIsActive = '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_staff count",$this->db_onedev);
			exit;
		}

		$sql = "
			SELECT *
			FROM m_staff
			WHERE 
			M_StaffName like ?
			AND M_StaffIsActive = 'Y'
			ORDER BY M_StaffName 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_staff rows",$this->db_onedev);
			 exit;
		  }
	}
	
	function doauthorization(){
	   if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$userid = $this->sys_user["M_UserID"];
		if($prm['type'] == 'LAB'){
			$prm['type'] = '';
		}
		
		$sql = "UPDATE result_office SET 
					Result_OfficeStatus = 'RECEIVED', 
					Result_OfficeUserID = {$userid}
				WHERE
					Result_OfficeT_OrderHeaderID = {$prm['orderid']} AND
					Result_OfficeT_OrderPromiseID = {$prm['promiseid']} AND
					Result_OfficeIsNonLab = '{$prm['type']}' AND
					Result_OfficeStatus = 'NEW'
				";
		
		$this->db_onedev->query($sql);
		
		$result = array(
			"total" => 1,
			"records" => $prm
		); 
		$this->sys_ok($result);
		exit;
   }
   
   function dohandover(){
	   if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$userid = $this->sys_user["M_UserID"];
		
		$sql = "INSERT INTO result_handover (
					Result_HandOverDate,
					Result_HandOverType,
					Result_HandOverReceivedBy,
					Result_HandOverReff,
					Result_HandOverUserID,
					Result_HandOverCreated
				)
				VALUES(
					NOW(),
					'OFFICE',
					'{$prm['receiver']}',
					'{$prm['receivernote']}',
					{$userid},
					NOW()
				)";
		$insert_new = $this->db_onedev->query($sql);
		$last_id = $this->db_onedev->insert_id();
		$sql = "UPDATE result_office SET 
					Result_OfficeStatus = 'HANDOVER', 
					Result_OfficeUserID = {$userid},
					Result_OfficeResult_HandOverID = {$last_id}
				WHERE
					Result_OfficeT_OrderHeaderID = {$prm['orderid']} AND
					Result_OfficeT_OrderPromiseID = {$prm['promiseid']} AND
					Result_OfficeStatus = 'RECEIVED'
				";
		
		$this->db_onedev->query($sql);
		
		$result = array(
			"total" => 1,
			"records" => $prm
		); 
		$this->sys_ok($result);
		exit;
   }
   
}