<?php
class Screening extends MY_Controller
{
   var $db_onedev;
   public function index()
   {
      echo "Screening 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;
		}	
		$rows = [];
		$tot_page = 0;
		$xsearch = '';
		if($prm['search'] !== ''){
			$xsearch = " AND M_PatientName LIKE CONCAT('%','{$prm['search']}','%') ";
		}

		
				$sql = 	"SELECT 
							0 as prepare_id,
							T_OrderHeaderID as orderid,
							T_OrderHeaderM_MouID as mouid,
							T_OrderHeaderLabNumber as labnumber,
							CONCAT(IFNULL(M_TitleName,''),' ',M_PatientName) as patient_fullname,
							DATE_FORMAT(T_OrderHeaderDate,'%d-%m-%Y') as date_order,
							'' as tests,
							'' as sampletypes
							FROM t_orderheader
							JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
							LEFT JOIN m_title ON M_PatientM_TitleID = M_TitleID
							WHERE
							T_OrderHeaderIsActive = 'Y' $xsearch
							ORDER BY T_OrderHeaderLabNumber ASC";
//echo $sql;
				$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 T_OrderHeaderID as order_id,
								T_OrderHeaderLabNumber as lab_number,
								T_SampleTypeID as sampletest_id,
								T_BarcodeLabBarcode as barcode,
								T_SampleTypeName as samplename,
								IF(T_OrderSampleID IS NULL OR T_OrderSampleReceive = 'N','X',IF(Mcu_ScreeningID IS NULL, T_OrderSampleReceive,Mcu_ScreeningIsExport)) as status,
								T_TestIsNonLab as isnonlab
								FROM t_barcodelab
								JOIN t_orderheader ON T_BarcodeLabT_OrderHeaderID = T_OrderHeaderID AND
									T_OrderHeaderIsActive = 'Y' 
								JOIN t_ordersample ON T_OrderSampleT_OrderHeaderID = T_OrderHeaderID AND
									T_OrderSampleT_BarcodeLabID = T_BarcodeLabID AND 
									T_OrderSampleT_SampleTypeID = T_BarcodeLabT_SampleTypeID AND
									T_OrderSampleIsActive = 'Y'
								JOIN t_sampletype ON T_BarcodeLabT_SampleTypeID = T_SampleTypeID
								JOIN t_test ON T_TestT_SampleTypeID = T_SampleTypeID
								LEFT JOIN mcu_screening ON Mcu_ScreeningT_OrderHeaderID = T_OrderHeaderID AND 
									Mcu_ScreeningT_SampleTypeID = T_BarcodeLabT_SampleTypeID
								WHERE
									T_BarcodeLabIsActive = 'Y' AND T_BarcodeLabT_OrderHeaderID = {$v['orderid']}
									GROUP BY T_BarcodeLabID
								UNION
								SELECT T_OrderHeaderID as order_id,
								T_OrderHeaderLabNumber as lab_number,
								T_TestID as sampletest_id,
								CONCAT(LEFT(T_OrderHeaderLabNumber,8),'-',Nat_TestCode) as barcode,
								T_TestName as samplename,
								IF(T_SamplingSoID IS NULL OR T_SamplingSoProcessDate IS NULL,'X',IF(Mcu_ScreeningID IS NULL, 'Y',Mcu_ScreeningIsExport)) as status,
								T_TestIsNonLab as isnonlab
								FROM t_orderdetail
								JOIN t_orderheader ON T_OrderHeaderID = T_OrderDetailT_OrderHeaderID AND
									T_OrderHeaderIsActive = 'Y' 
								JOIN t_test ON T_TestID = T_OrderDetailT_TestID AND (T_TestIsNonLab <> '' OR  T_TestIsNonLab = 'N')
								JOIN t_sampletype ON T_TestT_SampleTypeID = T_SampleTypeID
								LEFT JOIN t_samplingso ON T_SamplingSoT_OrderHeaderID = T_OrderHeaderID AND 
								T_SamplingSoT_TestID = T_TestID AND T_SamplingSoIsActive = 'Y'
								JOIN nat_test ON T_TestNat_TestID = Nat_TestID
								LEFT JOIN mcu_screening ON Mcu_ScreeningT_OrderHeaderID = T_OrderHeaderID AND 
									T_OrderDetailT_TestID = Mcu_ScreeningT_TestID
								WHERE
									T_OrderDetailIsActive = 'Y' AND T_OrderDetailT_OrderHeaderID = {$v['orderid']} 
									GROUP BY T_TestID
								";
								//echo $sql;
						$rows[$k]['sampletypes'] = $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 getinitialdatas(){
	   if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		
		$rows = [];
		$query ="	SELECT M_MouID as id,
					Mcu_AgreementID,
					Mcu_AgreementNumbering,
					M_CompanyName,
					M_MouID,
					M_MouName,
					M_MouStartDate,
					M_MouEndDate,
					CONCAT(M_CompanyName,' ( ',DATE_FORMAT(M_MouStartDate,'%d-%m-%Y'),' s.d. ',DATE_FORMAT(M_MouEndDate,'%d-%m-%Y'),' ) ') as name
					FROM mcu_agreement
					JOIN mcu_agreement_customer ON Mcu_AgreementCustomerMcu_AgreementID = Mcu_AgreementID
					JOIN m_company ON Mcu_AgreementCustomerM_CompanyID = M_CompanyID
					JOIN m_mou ON Mcu_AgreementCustomerM_MouID = M_MouID
					WHERE	
						( DATE(NOW()) BETWEEN CONCAT(M_MouStartDate,' 00:00:00') AND CONCAT(M_MouEndDate,' 23:59:59') ) AND
						Mcu_AgreementFlagAgree = 'Y' AND Mcu_AgreementIsActive = 'Y'
				";
				//echo $query;
		$rows['mous'] = $this->db_onedev->query($query)->result_array();
		
		

		$result = array(
			"total" => count($rows) , 
			"records" => $rows, 
		); 
		$this->sys_ok($result);
		exit;
   }
      
	
	function dosampled(){
	   if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$userid = $this->sys_user["M_UserID"];
		
		if($prm['trx_id'] == '0' || $prm['trx_id'] == 0){
			$sql = "INSERT INTO mcu_screening (
						Mcu_ScreeningMcu_AgreementID,
						Mcu_ScreeningT_OrderHeaderID,
						Mcu_ScreeningT_SampleTypeID,
						Mcu_ScreeningBarcode,
						Mcu_ScreeningUserID,
						Mcu_ScreeningCreated
					)
					VALUES(
						{$prm['agreement_id']},
						{$prm['orderid']},
						{$prm['sampletype_id']},
						'{$prm['barcode']}',
						{$userid},
						NOW()
					)";
			//echo $sql;
			$this->db_onedev->query($sql);
			$msg = "Spesimen ".$prm['name']." [".$prm['barcode']."] berhasil masuk screening";
		}
		else{
			$sql ="	UPDATE mcu_screening SET Mcu_ScreeningIsActive = 'N' , Mcu_ScreeningUserID = {$userid}
					WHERE
					Mcu_ScreeningID = {$prm['trx_id']}";
			//echo $sql;
			$this->db_onedev->query($sql);
			$msg = "Spesimen ".$prm['name']." [".$prm['barcode']."] berhasil dibatalkan";
		}
		
		$result = array(
			"total" => 1,
			"records" => $msg
		); 
		$this->sys_ok($result);
		exit;
   }
   
	function scanbarcode(){
	   if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		
		$prm = $this->sys_input;
		$userid = $this->sys_user["M_UserID"];
		
		$sql = "SELECT T_OrderDetailT_OrderHeaderID as orderid,
				Mcu_AgreementCustomerMcu_AgreementID as agreement_id,
				Mcu_AgreementCustomerM_CompanyID as company_id,
				Mcu_AgreementCustomerM_MouID as mouid,
				T_TestT_SampleTypeID as sampletype_id,
				T_SampleTypeName as name,
				T_BarcodeLabBarcode as barcode,
				T_OrderDetailID as orderdetailid,
				IFNULL(Mcu_ScreeningID,0) as trx_id,
				IF(ISNULL(Mcu_ScreeningID), 'N', 'Y') as status
				FROM t_orderdetail
				JOIN t_orderheader ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID
				JOIN t_test ON T_OrderDetailT_TestID = T_TestID
				JOIN t_barcodelab ON T_BarcodeLabT_OrderHeaderID = T_OrderDetailT_OrderHeaderID AND
					T_BarcodeLabT_SampleTypeID = T_TestT_SampleTypeID AND T_BarcodeLabIsActive = 'Y'
				JOIN t_sampletype ON T_TestT_SampleTypeID = T_SampleTypeID
				JOIN mcu_agreement_customer ON Mcu_AgreementCustomerM_MouID = T_OrderHeaderM_MouID AND Mcu_AgreementCustomerIsActive = 'Y'
				LEFT JOIN mcu_screening ON Mcu_ScreeningT_SampleTypeID = T_SampleTypeID AND Mcu_ScreeningIsActive = 'Y'
				WHERE
					T_BarcodeLabBarcode = '{$prm['barcode']}' AND
					T_OrderDetailIsActive = 'Y'
				GROUP BY T_BarcodeLabID
				LIMIT 1";
		//echo $sql;
		$row = $this->db_onedev->query($sql)->row_array();
		
		$msg = "";
		if($row){
			if($row['trx_id'] == '0' || $row['trx_id'] == 0){
				$sql = "INSERT INTO mcu_screening (
							Mcu_ScreeningMcu_AgreementID,
							Mcu_ScreeningT_OrderHeaderID,
							Mcu_ScreeningT_SampleTypeID,
							Mcu_ScreeningBarcode,
							Mcu_ScreeningUserID,
							Mcu_ScreeningCreated
						)
						VALUES(
							{$row['agreement_id']},
							{$row['orderid']},
							{$row['sampletype_id']},
							'{$row['barcode']}',
							{$userid},
							NOW()
						)";
				//echo $sql;
				$this->db_onedev->query($sql);
				$msg = "Spesimen ".$row['name']." [".$row['barcode']."] berhasil masuk screening";
			}
			else{
				$msg = "Spesimen ".$row['name']." [".$row['barcode']."] sudah di screening";
			}
		}else{
			$msg = "Spesimen dengan barcode ".$prm['name']." [".$prm['barcode']."] tidak ditemukan";
		}
		
		
		$result = array(
			"total" => 1,
			"records" => $msg
		); 
		$this->sys_ok($result);
		exit;
	}
	
	function screening(){
	   if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		
		$row = $this->sys_input;
		$userid = $this->sys_user["M_UserID"];
		
		$test_id = 0;
		$sample_id = 0;
		if($row['isnonlab'] == '' || $row['isnonlab'] == 'N'){
			$test_id = 0;
			$sample_id = $row["sampletest_id"];
		}
		else{
			$test_id = $row["sampletest_id"];
			$sample_id = 0;
		}
		
		$sql = "INSERT INTO mcu_screening (
			Mcu_ScreeningT_OrderHeaderID,
			Mcu_ScreeningT_SampleTypeID,
			Mcu_ScreeningT_TestID,
			Mcu_ScreeningIsNonLab,
			Mcu_ScreeningIsExport,
			Mcu_ScreeningUserID,
			Mcu_ScreeningCreated
		)
		VALUES(
			{$row['order_id']},
			{$sample_id},
			{$test_id},
			'{$row['isnonlab']}',
			'V',
			{$userid},
			NOW()
		)";
		//echo $sql;
		$this->db_onedev->query($sql);
		$last_id = $this->db_onedev->insert_id();
		if($row['isnonlab'] == ''){
			$sql = "SELECT * 
					FROM t_ordersample
					WHERE
						T_OrderSampleT_OrderHeaderID = {$row['order_id']} AND
						T_OrderSampleT_SampleTypeID = {$sample_id} AND
						T_OrderSampleIsActive = 'Y'";
						//echo $sql;
			$row_selected = $this->db_onedev->query($sql)->row_array();
			if($row_selected['T_OrderSampleVerification'] == 'N'){
				$sql = "UPDATE t_ordersample SET 
						T_OrderSampleVerification = 'Y',
						T_OrderSampleVerificationDate = CURDATE(),
						T_OrderSampleVerificationTime = CURTIME(),
						T_OrderSampleVerificationUserID = {$userid},
						T_OrderSampleSendHandling = 'Y',
						T_OrderSampleSendHandlingDate = CURDATE(),
						T_OrderSampleSendHandlingTime = CURTIME(),
						T_OrderSampleSendHandlingUserID = {$userid}
					WHERE
					T_OrderSampleID = {$row_selected['T_OrderSampleID']}";
					//echo $sql;
				$this->db_onedev->query($sql);
			}
		}
		else{
			$sql = "SELECT * 
					FROM t_samplingso
					WHERE
						T_SamplingSoT_OrderHeaderID = {$row['order_id']} AND
						T_SamplingSoT_TestID = {$test_id} AND
						T_SamplingSoIsActive = 'Y'";
			$row_selected = $this->db_onedev->query($sql)->row_array();
			if($row_selected['T_SamplingSoFlag'] == 'D'){
				$sql = "UPDATE t_samplingso SET 
						T_SamplingSoFlag = 'V',
						T_SamplingSoVerifyDate = CURDATE(),
						T_SamplingSoVerifyTime = CURTIME(),
						T_SamplingSoVerifyUserID = {$userid},
						T_SamplingSoFlagSend = 'Y',
						T_SamplingSoSendAdmDate = CURDATE(),
						T_SamplingSoSendAdmTime = CURTIME(),
						T_SamplingSoSendAdmUserID = {$userid}
					WHERE
					T_SamplingSoID = {$row_selected['T_SamplingSoID']}";
				$this->db_onedev->query($sql);
			}
		}
		$result = array(
			"total" => 1,
			"records" => 'ok'
		); 
		$this->sys_ok($result);
		exit;
	}
   
    function search_staff(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$rows = [];
		$query ="	SELECT M_StaffID as id, M_StaffName as name, M_StaffCode as code, M_UserID as userid
					FROM m_staff
					JOIN m_user ON M_UserM_StaffID = M_StaffID AND M_UserIsActive = 'Y'
					WHERE	
						M_StaffIsActive = 'Y' AND M_StaffCode = '{$prm['search']}' LIMIT 1
				";
				//echo $query;
		$rows = $this->db_onedev->query($query)->row_array();	

		$result = array(
			"total" => count($rows) , 
			"records" => $rows, 
		); 
		$this->sys_ok($result);
		exit;
	}
	
	function search_barcode(){
	   if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		
		$prm = $this->sys_input;
		$userid = $this->sys_user["M_UserID"];
		
		$lengstr = strlen($prm['search']);
		$sql = "SELECT T_OrderSampleT_SampleTypeID as sampletest_id,
						'' as isnonlab,
						T_OrderSampleT_OrderHeaderID as order_id,
						T_OrderSampleReceive as status,
						T_SampleTypeName as sample_name
					FROM t_ordersample
					JOIN t_sampletype ON T_OrderSampleT_SampleTypeID = T_SampleTypeID
					WHERE
						LEFT(T_OrderSampleBarcode,12)  = '{$prm['search']}' AND T_OrderSampleIsActive = 'Y'";
		//echo $lengstr;
		//echo $sql;
		$row = $this->db_onedev->query($sql)->row_array();
		if($lengstr == 12 && $row['status'] == 'Y'){
			$test_id = 0;
			$sample_id = 0;
			
			if($row['isnonlab'] == '' || $row['isnonlab'] == 'N'){
				$test_id = 0;
				$sample_id = $row["sampletest_id"];
			}
			else{
				$test_id = $row["sampletest_id"];
				$sample_id = 0;
			}
			
			$sql = "INSERT INTO mcu_screening (
				Mcu_ScreeningT_OrderHeaderID,
				Mcu_ScreeningT_SampleTypeID,
				Mcu_ScreeningT_TestID,
				Mcu_ScreeningIsNonLab,
				Mcu_ScreeningIsExport,
				Mcu_ScreeningUserID,
				Mcu_ScreeningCreated
			)
			VALUES(
				{$row['order_id']},
				{$sample_id},
				{$test_id},
				'{$row['isnonlab']}',
				'V',
				{$userid},
				NOW()
			)";
			//echo $sql;
			$this->db_onedev->query($sql);
			$last_id = $this->db_onedev->insert_id();
			if($row['isnonlab'] == ''){
				$sql = "SELECT * 
						FROM t_ordersample
						WHERE
							T_OrderSampleT_OrderHeaderID = {$row['order_id']} AND
							T_OrderSampleT_SampleTypeID = {$sample_id} AND
							T_OrderSampleIsActive = 'Y'";
							//echo $sql;
				$row_selected = $this->db_onedev->query($sql)->row_array();
				if($row_selected['T_OrderSampleVerification'] == 'N'){
					$sql = "UPDATE t_ordersample SET 
							T_OrderSampleVerification = 'Y',
							T_OrderSampleVerificationDate = CURDATE(),
							T_OrderSampleVerificationTime = CURTIME(),
							T_OrderSampleVerificationUserID = {$userid},
							T_OrderSampleSendHandling = 'Y',
							T_OrderSampleSendHandlingDate = CURDATE(),
							T_OrderSampleSendHandlingTime = CURTIME(),
							T_OrderSampleSendHandlingUserID = {$userid}
						WHERE
						T_OrderSampleID = {$row_selected['T_OrderSampleID']}";
						//echo $sql;
					$this->db_onedev->query($sql);
				}
			}
			else{
				$sql = "SELECT * 
						FROM t_samplingso
						WHERE
							T_SamplingSoT_OrderHeaderID = {$row['order_id']} AND
							T_SamplingSoT_TestID = {$test_id} AND
							T_SamplingSoIsActive = 'Y'";
				$row_selected = $this->db_onedev->query($sql)->row_array();
				if($row_selected['T_SamplingSoFlag'] == 'D'){
					$sql = "UPDATE t_samplingso SET 
							T_SamplingSoFlag = 'V',
							T_SamplingSoVerifyDate = CURDATE(),
							T_SamplingSoVerifyTime = CURTIME(),
							T_SamplingSoVerifyUserID = {$userid},
							T_SamplingSoFlagSend = 'Y',
							T_SamplingSoSendAdmDate = CURDATE(),
							T_SamplingSoSendAdmTime = CURTIME(),
							T_SamplingSoSendAdmUserID = {$userid}
						WHERE
						T_SamplingSoID = {$row_selected['T_SamplingSoID']}";
					$this->db_onedev->query($sql);
				}
			}
			
			$msg = "Sample ".$row['sample_name']." berhasil screening";
			$result = array(
				"total" => 1,
				"records" => $msg
			); 
		}
		else{
			$result = array(
					"total" => 1,
					"records" => 'Sample belum diambil'
				); 
			
		}
		
		
		$this->sys_ok($result);
		exit;
	}
	
	
}