pbmc-cmod/app/Controllers/API_DictTests.php

<?php
namespace App\Controllers;

use CodeIgniter\RESTful\ResourceController;

class API_DictTests extends ResourceController {
	protected $format = 'json';

	public function index()	{
		$db = \Config\Database::connect();
		$sql = "select TESTCODE, TEXT1, TEXT2 from cmod.dbo.CM_DICT_TESTS";
		$query = $db->query($sql);
		$results = $query->getResultArray();
		$data['dictTests'] = $results;
		
		return $this->respond($data, 200);
	}
	
	public function search()	{
		$db = \Config\Database::connect();
		$testcode = $this->request->getPost('testcode');
		$shorttext = $this->request->getPost('shorttext');
		$sql = "select dt.TESTCODE, dt.SHORTTEXT, cdt.TEXT1, cdt.TEXT2, cdt.UNIT, cdt.REFFTEXT from DICT_TESTS dt 
	left join cmod.dbo.CM_DICT_TESTS cdt on dt.TESTCODE=cdt.TESTCODE";
		// Initialize a WHERE clause
		$where= '';

		// Check if either testcode or shorttext is provided
		if (!empty($testcode) || !empty($shorttext)) {
			$where= ' WHERE ';
			if (!empty($testcode)) { $where .= "dt.TESTCODE like '%$testcode%'"; }
			if (!empty($shorttext)) {
				if (!empty($testcode)) { $where .= ' OR '; }
				$where .= "LOWER(dt.SHORTTEXT) LIKE '%$shorttext%'";
			}
		}
		$sql .= $where;
		$query = $db->query($sql);
		$results = $query->getResultArray();
		$data['dictTests'] = $results;
		
		return $this->respond($data, 200);
	}
	
	public function detail($testcode)	{
		$data = array();
		$db = \Config\Database::connect();
		$sql = "select * from cmod.dbo.CM_DICT_TESTS where TESTCODE='$testcode'";
		$query = $db->query($sql);
		$results = $query->getResultArray();
		if(isset($results[0])) { $data = $results[0]; }
		return $this->respond($data, 200);
	}
	
	public function save() {
		$update = $this->request->getPost('update');
		$testcode = $this->request->getPost('testcode');
		$text1 = $this->request->getPost('text1');
		$text2 = $this->request->getPost('text2');
		$unit = $this->request->getPost('unit');
		$refftext = $this->request->getPost('refftext');
        
		$db = \Config\Database::connect();
		$sql = "MERGE INTO cmod.dbo.CM_DICT_TESTS AS t
			USING ( VALUES ('$testcode', '$text1', '$text2', '$unit', '$refftext')
			) AS s (TESTCODE, TEXT1, TEXT2, UNIT, REFFTEXT) on t.TESTCODE=s.TESTCODE
			WHEN NOT MATCHED BY TARGET THEN
				INSERT (TESTCODE, TEXT1, TEXT2, UNIT, REFFTEXT, LOGDATE)
				VALUES (s.TESTCODE, s.TEXT1, s.TEXT2, s.UNIT, s.REFFTEXT, GETDATE())
			WHEN MATCHED THEN
				UPDATE set TEXT1=s.TEXT1, TEXT2=s.TEXT2, UNIT=s.UNIT, REFFTEXT=s.REFFTEXT;"; 
		
		if( $db->query($sql) )  {
			return $this->respond(['message' => 'Save Success'],201);
		} else {
			$response = [
				'errors' => $db->errors(),
				'message' => 'Invalid Inputs'
			];
	       return $this->fail($response , 409);
        }
	}
}