<?php
class Testsas extends MY_Controller
{
	var $db_regional;
	public function index()
	{
		echo "SampleStorage API";
	}

	public function __construct()
	{
		parent::__construct();
		$this->db_regional = $this->load->database("regional", true);
	}

	public function deletetest()
	{
		$prm = $this->sys_input;
		if (!$this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}

		$this->db_regional->trans_begin();

		$sastestcode = isset($prm["test_id"]) ? trim($prm["test_id"]) : '';

		if ($sastestcode === '' || strlen($sastestcode) < 8) {
			$this->sys_ok([
				"records" => 0,
				"ids" => [],
				"sql" => $this->db_regional->last_query(),
				"msg" => "Tidak ada data yang didelete"
			]);
			return;
		}
		$query_sql = $sastestcode . '%'; // lebih jelas

		// ambil dulu id yang akan diupdate
		$sql_select = "SELECT T_TestID 
			FROM t_test
			WHERE T_TestSasCode LIKE ?
				AND T_TestIsActive = 'Y'";
		$ids = $this->db_regional->query($sql_select, [$query_sql])->result_array();

		if (empty($ids)) {
			$this->db_regional->trans_commit(); // tetap commit
			$this->sys_ok([
				"records" => 0,
				"ids" => [],
				"sql" => $this->db_regional->last_query(),
				"msg" => "Tidak ada data yang didelete"
			]);
			return;
		}

		$sql = "UPDATE t_test
			SET T_TestIsActive = 'N',
				T_TestLastUpdated = NOW()
            WHERE T_TestSasCode LIKE ?
            AND T_TestIsActive = 'Y'";

		$query = $this->db_regional->query($sql, [$query_sql]);
		$sql_qry = $this->db_regional->last_query();

		if (!$query) {
			$this->db_regional->trans_rollback();
			$this->sys_error("Error Delete \n" . $sql_qry);
			return;
		}

		$rows = $this->db_regional->affected_rows();

		$this->db_regional->trans_commit();
		$result = array(
			"records" => $rows,
			"sql" => $this->db_regional->last_query(),
			"ids" => array_column($ids, "T_TestID"),
			"msg" => "Success Delete Test"
		);
		$this->sys_ok($result);
		exit;

	}

	public function search()
	{
		$prm = $this->sys_input;
		if (!$this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}

		$start_date = $prm["startdate"];
		$end_date = $prm["enddate"];
		$search_code = $prm['code'];
		$search_name = $prm['name'];
		$number_limit = 100;
		$number_offset = ($prm['current_page'] - 1) * $number_limit;
		$sql_where = '';
		if ($search_code != '' || $search_name != '') {
			$sql_where .= ' AND (';
			if ($search_code != '') {
				$sql_where .= " ( T_TestSasCode LIKE CONCAT('{$search_code}','%') OR T_TestCode LIKE CONCAT('{$search_code}','%'))";
			}

			if ($search_name != '') {
				if ($search_code != '')
					$sql_where .= ' AND ';

				$sql_where .= " T_TestName LIKE CONCAT('%','{$search_name}','%') ";
			}

			$sql_where .= ')';
		}



		$sql_param = array($start_date, $end_date);


		$sql = "	SELECT count(*) as total
				FROM t_test
				LEFT JOIN nat_test ON T_TestCode = Nat_TestCode
				LEFT JOIN nat_sampletype ON T_TestT_SampleTypeID = Nat_SampleTypeID
				LEFT JOIN nat_group ON T_TestNat_GroupID = Nat_GroupID
				LEFT JOIN nat_subgroup ON T_TestNat_SubGroupID = Nat_SubGroupID
				LEFT JOIN nat_unit ON Nat_TestNat_UnitID = Nat_UnitID
				WHERE T_TestIsActive = 'Y' $sql_where
				ORDER BY T_TestCode ASC
				";
		//echo $sql;
		$query = $this->db_regional->query($sql);

		$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_samplestorage count", $this->db_regional);
			exit;
		}

		$sql = "SELECT *, if(T_TestParentT_TestID > 0 , fn_get_testname(T_TestParentT_TestID),'-') as parenttestname
				FROM t_test 
				LEFT JOIN nat_test ON T_TestCode = Nat_TestCode AND Nat_TestIsActive = 'Y'
				LEFT JOIN nat_sampletype ON T_TestT_SampleTypeID = Nat_SampleTypeID 
				LEFT JOIN nat_group ON T_TestNat_GroupID = Nat_GroupID
				LEFT JOIN nat_subgroup ON T_TestNat_SubGroupID = Nat_SubGroupID
				LEFT JOIN nat_unit ON Nat_TestNat_UnitID = Nat_UnitID
				WHERE T_TestIsActive = 'Y' $sql_where
				ORDER BY T_TestSasCode ASC
				limit $number_limit offset $number_offset";

		$query = $this->db_regional->query($sql);
		//echo $this->db_regional->last_query();
		$rows = $query->result_array();
		if ($rows) {

		}


		//$this->_add_address($rows);
		$result = array("total" => $tot_page, "records" => $rows, "sql" => $this->db_regional->last_query());
		$this->sys_ok($result);
		exit;
	}

	function getalmaries()
	{
		if (!$this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}

		$rows = [];
		$query = "	SELECT M_AlmariID as id, CONCAT('[ ',M_AlmariCode,' ] ', M_AlmariName) as name, M_AlmariCode as code
					FROM m_almari 
					WHERE	
						M_AlmariIsActive = 'Y'
				";
		//echo $query;
		$rows = $this->db_regional->query($query)->result_array();


		$result = array(
			"total" => count($rows),
			"records" => $rows,
		);
		$this->sys_ok($result);
		exit;
	}

	function getracks()
	{
		if (!$this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$rows = [];
		$query = "	SELECT Summary_SampleStorageM_AlmariID as almarid, Summary_SampleStorageM_RackID as rackid , Summary_SampleStorageRowPosition as row, Summary_SampleStorageColPosition as col
					FROM summary_samplestorage 
					WHERE	
						Summary_SampleStorageStatus = 'FILLED'
				";
		//echo $query;
		$filledrows = $this->db_regional->query($query)->result_array();
		$query = "	SELECT {$prm['id']} as almariid, 
						M_RackID as id, 
						CONCAT(M_RackCode,' ( ',M_RackRows,' x ',M_RackColumns,' )') as name, 
						M_RackCode as code, 
						M_RackRows as row, 
						M_RackColumns as col,
						'' as rackcontens
					FROM m_rack 
					WHERE	
						M_RackM_AlmariID = {$prm['id']} AND M_RackIsActive = 'Y'
				";
		//echo $query;
		$datarows = $this->db_regional->query($query)->result_array();
		foreach ($datarows as $k => $v) {
			$rows = $v['row'];
			$cols = $v['col'];
			$rackcontens = array();
			for ($x = 1; $x <= $rows; $x++) {
				$children = array();
				for ($i = 1; $i <= $cols; $i++) {
					$content = $x . ' x ' . $i;
					$xrow = $x;
					$xcol = $i;
					$status = $this->checkexistfilled($filledrows, $v['id'], $xrow, $xcol);
					array_push($children, array('content' => $content, 'row' => $xrow, 'col' => $xcol, 'status' => $status, 'selected' => 'N'));
				}
				array_push($rackcontens, $children);
			}
			$datarows[$k]['rackcontens'] = $rackcontens;
		}


		$result = array(
			"total" => count($datarows),
			"records" => $datarows,
		);
		$this->sys_ok($result);
		exit;
	}



	function checkexistfilled($datas, $rackid, $row, $col)
	{
		$rtn = 'N';
		foreach ($datas as $k => $v) {
			if ($v['rackid'] == $rackid && $v['row'] == $row && $v['col'] == $col) {
				$rtn = 'Y';
			}
		}
		return $rtn;
	}

	function save()
	{
		if (!$this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$userid = $this->sys_user["M_UserID"];
		$numbering = $this->db_regional->query("SELECT fn_numbering('SS') as numbering")->row()->numbering;
		$xdate = date('Y-m-d', strtotime($prm["date"]));
		$query = "INSERT INTO t_samplestorage (
						T_SampleStorageNumbering,
						T_SampleStorageDate,
						T_SampleStorageTime,
						T_SampleStorageNote,
						T_SampleStorageUserID,
						T_SampleStorageCreated
					)
					VALUES(
						'{$numbering}',
						'{$xdate}',
						'{$prm['time']}',
						'{$prm['note']}',
						'{$userid}',
						NOW()
					)
				";
		//echo $query;
		$saveheader = $this->db_regional->query($query);
		$last_id = $this->db_regional->insert_id();
		if ($saveheader) {
			foreach ($prm['details'] as $k => $v) {
				$query = "INSERT INTO t_samplestorage_detail (
					T_SampleStorageDetailT_SampleStorageID,
					T_SampleStorageDetailT_OrderSampleID,
					T_SampleStorageDetailBarcode,
					T_SampleStorageDetailM_AlmariID,
					T_SampleStorageDetailM_RackID,
					T_SampleStorageDetailRowPosition,
					T_SampleStorageDetailColumnPosition,
					T_SampleStorageDetailUserID,
					T_SampleStorageDetailCreated
				)
				VALUES(
					'{$last_id}',
					'{$v['ordersampleid']}',
					'{$v['barcode']}',
					'{$v['almari']['id']}',
					'{$v['rack']['id']}',
					'{$v['row']}',
					'{$v['col']}',
					'{$userid}',
					NOW()
				)";
				//echo $query;
				$savedetail = $this->db_regional->query($query);
			}
		}

		$sql = "SELECT * FROM t_samplestorage WHERE T_SampleStorageID = {$last_id}";
		$data_log_header = $this->db_regional->query($sql)->result();
		$sql = "SELECT * FROM t_samplestorage_detail WHERE T_SampleStorageDetailT_SampleStorageID = {$last_id}";
		$data_log_details = $this->db_regional->query($sql)->result();

		$data_log = json_encode(array('header' => $data_log_header, 'details' => $data_log_details));
		$sql = "INSERT INTO one_log.log_samplestorage (
					Log_SampleStorageCode,
					Log_SampleStorageDate,
					Log_SampleStorageJSON,
					Log_SampleStorageUserID
				)
				VALUES(
					'CREATED.SAVE',
					 CURDATE(),
					'{$data_log}',
					{$userid}
				)";
		//echo $sql;
		$this->db_regional->query($sql);

		$result = array(
			"total" => 1,
			"records" => array('status' => 'OK'),
			"numbering" => $numbering,
			"id" => $last_id
		);
		$this->sys_ok($result);
		exit;
	}


	function checkbarcode()
	{
		if (!$this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$datarows = [];
		$query = "	SELECT CONCAT(IFNULL(M_TitleName,''),' ',M_PatientName) as patientname, T_OrderSampleID
					FROM t_ordersample
					JOIN t_orderheader ON T_OrderSampleT_OrderHeaderID = T_OrderHeaderID
					JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
					LEFT JOIN m_title ON M_PatientM_TitleID = M_TitleID
					WHERE	T_OrderSampleBarcode = '{$prm['barcode']}' AND T_OrderSampleIsActive = 'Y' ORDER BY T_OrderSampleID DESC LIMIT 1
				";
		//echo $query;
		$datarows = $this->db_regional->query($query)->row();

		$result = array(
			"total" => count($datarows),
			"records" => $datarows,
		);
		$this->sys_ok($result);
		exit;
	}
}