<?php
class Normalvalueanimal extends MY_Controller
{
   var $db_regional;
   public function index()
   {
      echo "Normal Value API";
   }
   
   public function __construct()
   {
      parent::__construct();
      $this->db_regional = $this->load->database("regional", true);
   }
   
   public function search()
   {
      $prm = $this->sys_input;
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$code = $prm["code"];
		$nama = $prm["nama"];
  
	   // echo $norm;
		
		$sql_where = "WHERE Nat_TestIsActive = 'Y' AND Nat_TestIsResult = 'Y'";
		$sql_param = array();
		if ($code != "") {
		   if ($sql_where != "") {
			  $sql_where .=" and ";
		   }
		   $sql_where .= " Nat_TestCode like ? ";
		   $sql_param[] = "%$code%";
		}
		if ($nama != "") {
		   if ($sql_where != "") {
			  $sql_where .=" and ";
		   }
		   $sql_where .= " Nat_TestName like ? ";
		   $sql_param[] = "%$nama%";
		}
	   
		//if ($sql_where != "") $sql_where .= " and ";
  
		// Order masih dalam status registrasi
		//$sql_where .= " M_PatientIsActive = 'Y' ";
		
  
		$sql = "	SELECT count(*) as total
				  FROM nat_test
                  $sql_where
				  ";
  //echo $sql;
		  $query = $this->db_regional->query($sql, $sql_param);
  
		  $tot_count = 0;
		  if ($query) {
			  $tot_count = $query->result_array()[0]["total"];
		  } else {
			  $this->sys_error_db("nat_test count", $this->db_regional);
			  exit;
		  }  
		
		  $sql = 	"SELECT *
                      FROM nat_test
					  $sql_where
				  ORDER BY Nat_TestCode ASC
				  limit 0,$tot_count";
  //echo $sql;
		  $query = $this->db_regional->query($sql, $sql_param);
		  $rows = $query->result_array();
		  if($rows){			
			foreach($rows as $k => $v){		
				
			}
		}
	  
	  
		//$this->_add_address($rows);
		$result = array("total" => $tot_count, "records" => $rows, "sql"=> $this->db_regional->last_query());
		$this->sys_ok($result);
		exit;

   }
   function searchmethode(){
	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 nat_methode
			WHERE 
			Nat_MethodeName like ?
			AND Nat_MethodeIsActive = 'Y'";
	$query = $this->db_regional->query($sql,$q['search']);
	//echo $query;
	if ($query) { 
		$tot_count = $query->result_array()[0]["total"]; 
	} 
	else {
		$this->sys_error_db("nat_methode count",$this->db);
		exit;
	}

	$sql = "
	SELECT * 
	FROM nat_methode
	WHERE 
		Nat_MethodeName like ?
		AND Nat_MethodeIsActive = 'Y'
		ORDER BY Nat_MethodeName ASC
	  ";
	  $query = $this->db_regional->query($sql, array($q['search']));
		
	  if ($query) { 
		 $rows = $query->result_array();
		 //echo $this->db_regional->last_query();
		 $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
		 $this->sys_ok($result);
	  } 
	  else {
		 $this->sys_error_db("nat_methode rows",$this->db);
		 exit;
	  }
}
   function searchcompany(){
	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_company
			WHERE 
			Nat_TestName like ?
			AND Nat_TestIsActive = 'Y' AND Nat_TestIsLabFrom = 'Y'";
	$query = $this->db_regional->query($sql,$q['search']);
	//echo $query;
	if ($query) { 
		$tot_count = $query->result_array()[0]["total"]; 
	} 
	else {
		$this->sys_error_db("m_company count",$this->db_regional);
		exit;
	}

	$sql = "
		SELECT *
		FROM m_company
		WHERE 
		Nat_TestName like ?
		AND Nat_TestIsActive = 'Y' AND Nat_TestIsLabFrom = 'Y'
		ORDER BY Nat_TestName DESC
	  ";
	  $query = $this->db_regional->query($sql, array($q['search']));
		
	  if ($query) { 
		 $rows = $query->result_array();
		 //echo $this->db_regional->last_query();
		 $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
		 $this->sys_ok($result);
	  } 
	  else {
		 $this->sys_error_db("m_company rows",$this->db_regional);
		 exit;
	  }
}
function getmou(){
	$prm = $this->sys_input;
	$query ="	SELECT * 
				FROM m_mou 
				WHERE	
					M_MouIsActive = 'Y' AND M_MouNat_TestID = ?
			";
			//echo $query;
	$rows = $this->db_regional->query($query,array($prm['id']))->result_array();
	
	$result = array(
		"total" => count($rows) , 
		"records" => $rows, 
	); 
	$this->sys_ok($result);
	exit;
}
function getsexreg(){
	if (! $this->isLogin) {
		$this->sys_error("Invalid Token");
		exit;
	}
	$rows = [];
	$query ="	SELECT * 
				FROM nat_sex 
				WHERE	
					Nat_SexIsActive = 'Y'
			";
			//echo $query;
	$rows['sexes'] = $this->db_regional->query($query)->result_array();

	$query ="	SELECT * 
	FROM nat_normalvaluetype 
	WHERE	
		Nat_NormalValueTypeIsActive = 'Y'
";
//echo $query;
$rows['normalvaluetypees'] = $this->db_regional->query($query)->result_array();

$query ="	SELECT * 
FROM nat_flag 
WHERE	
	Nat_FlagIsActive = 'Y'
";
//echo $query;
$rows['flages'] = $this->db_regional->query($query)->result_array();

$query ="	SELECT * 
FROM nat_animal 
WHERE	
	Nat_AnimalIsActive = 'Y'
";
//echo $query;
$rows['animals'] = $this->db_regional->query($query)->result_array();

$query ="	SELECT 'HARI' as Nat_AgeUnitID,'HARI' as Nat_AgeUnitName
UNION SELECT 'BULAN' as Nat_AgeUnitID, 'BULAN' as Nat_AgeUnitName
UNION SELECT 'TAHUN' as Nat_AgeUnitID, 'TAHUN' as Nat_AgeUnitName
";
//echo $query;
$rows['ageunites'] = $this->db_regional->query($query)->result_array();

$query ="	
SELECT 0 as Nat_SexID, 'Semua' as Nat_SexName
UNION
SELECT Nat_SexID, Nat_SexName
 FROM nat_sex
 WHERE
 Nat_SexIsActive = 'Y'

";
//echo $query;
$rows['f_sexs'] = $this->db_regional->query($query)->result_array();


$query ="	
SELECT 0 as Nat_AnimalID, 'Semua' as Nat_AnimalName
UNION
SELECT Nat_AnimalID, Nat_AnimalName
 FROM nat_animal
 WHERE
 Nat_AnimalIsActive = 'Y'

";
//echo $query;
$rows['f_animals'] = $this->db_regional->query($query)->result_array();

$query ="	
SELECT 0 as Nat_FlagID, 'Semua' as Nat_FlagName
UNION
SELECT Nat_FlagID, Nat_FlagName
 FROM nat_flag
 WHERE
 Nat_FlagIsActive = 'Y'";
//echo $query;
$rows['f_flags'] = $this->db_regional->query($query)->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_PatientDOB']));
		$query ="UPDATE m_patient SET
					M_PatientM_TitleID = '{$prm['M_PatientM_TitleID']}',
					M_PatientName = '{$prm['M_PatientName']}',
					M_PatientDOB = '{$pdob}',
					M_PatientM_SexID = '{$prm['M_PatientM_SexID']}',
					M_PatientM_ReligionID = '{$prm['M_PatientM_ReligionID']}',
					M_PatientEmail = '{$prm['M_PatientEmail']}',
					M_PatientHP = '{$prm['M_PatientHP']}',
					M_PatientPhone = '{$prm['M_PatientPhone']}',
					M_PatientM_IdTypeID = '{$prm['M_PatientM_IdTypeID']}',
					M_PatientIDNumber = '{$prm['M_PatientIDNumber']}',
					M_PatientNote = '{$prm['M_PatientNote']}'
				WHERE
					M_PatientID = '{$prm['M_PatientID']}'
				";
				//echo $query;
		$rows = $this->db_regional->query($query);
		
		$result = array(
			"total" => 1 , 
			"records" => array('status'=>'OK')
		); 
		$this->sys_ok($result);
		exit;
	}
	
	function newreceivereference(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$pdate = date('Y-m-d',strtotime($prm['sdate']));
		$userid = $this->sys_user["M_UserID"];
		$query ="INSERT INTO nat_test (
						Nat_TestNat_TestID,
						Nat_TestM_MouID,
						Nat_TestDate,
						Nat_TestNote,
						Nat_TestUserID,
						Nat_TestCreated
					)
					VALUES(
						'{$prm['companyid']}',
						'{$prm['mouid']}',
						'{$pdate}',
						'{$prm['note']}',
						'{$userid}',
						NOW()
					)
				";
			//	echo $query;
		$rows = $this->db_regional->query($query);
		$last_id = $this->db_regional->insert_id();

		$querylog ="INSERT INTO g_receivereferenceheaderstatuslog (
			G_ReceiveReferenceHeaderStatusLogDate,
			G_ReceiveReferenceHeaderStatusLogNat_TestID,
			G_ReceiveReferenceHeaderStatusLogM_StatusReferenceID,
			G_ReceiveReferenceHeaderStatusLogM_UserID,
			G_ReceiveReferenceHeaderStatusLogUserID,
			G_ReceiveReferenceHeaderStatusLogCreated,
			G_ReceiveReferenceHeaderStatusLogLastUpdated
		)
		VALUES(
			NOW(),
			'{$last_id}',
			'1',
			'{$userid}',
			'{$userid}',
			NOW(),
			NOW()
		)";
	//echo $querylog;
	$insert_new_log = $this->db_regional->query($querylog);
		$result = array(
			"total" => 1 , 
			"records" => array('status'=>'OK'),
			"id" => $last_id 
		); 
		$this->sys_ok($result);
		exit;
	}
	function sendorder(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$userid = $this->sys_user["M_UserID"];
		
		$query ="UPDATE nat_test SET 
						Nat_TestIsSent = 'Y',
						Nat_TestSentDate = now(),
						Nat_TestUserID = '{$userid}'
					WHERE
					Nat_TestID = '{$prm['Nat_TestID']}'
				";
				//echo $query;
		$rows = $this->db_regional->query($query);

$rows = $this->db_regional->query($query);

$querylog ="INSERT INTO g_receivereferenceheaderstatuslog (
	G_ReceiveReferenceHeaderStatusLogDate,
	G_ReceiveReferenceHeaderStatusLogNat_TestID,
	G_ReceiveReferenceHeaderStatusLogM_StatusReferenceID,
	G_ReceiveReferenceHeaderStatusLogM_UserID,
	G_ReceiveReferenceHeaderStatusLogUserID,
	G_ReceiveReferenceHeaderStatusLogCreated,
	G_ReceiveReferenceHeaderStatusLogLastUpdated
)
VALUES(
	NOW(),
	'{$prm['Nat_TestID']}',
	'2',
	'{$userid}',
	'{$userid}',
	NOW(),
	NOW()
)";
//echo $querylog;
$insert_new_log = $this->db_regional->query($querylog);
		$result = array(
			"total" => 1 , 
			"records" => array('status'=>'OK')
		); 
		$this->sys_ok($result);
		exit;
	}
	function getnilainormal(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$methodename = $prm['methodename'];
		$sexid = $prm['sexid'];
		$flagid = $prm['flagid'];
		$animalid = $prm['animalid'];
		$filter = '';
		if(isset($sexid)){
			$filter .= "AND ($sexid = 0 or ($sexid > 0 and Nat_SexID = $sexid)) ";
		}
		if(isset($animalid)){
			$filter .= "AND ($animalid = 0 or ($animalid > 0 and Nat_AnimalID = $animalid)) ";
		}
		if(isset($flagid)){
			$filter .= "AND ($flagid = 0 or ($flagid > 0 and Nat_FlagID = $flagid))";
		}
		$query ="	SELECT nat_normalvalueanimal.*,
					Nat_TestName,
					Nat_SexID,
					Nat_SexName,
					Nat_AnimalID,
					Nat_AnimalName,
					Nat_MethodeID,
					Nat_MethodeName,
					Nat_NormalValueTypeID,
					Nat_NormalValueTypeName,
					Nat_FlagID,
					Nat_FlagName,
					CASE
    WHEN Nat_NormalValueAnimalMinAgeInclusive = 'Y' AND Nat_NormalValueAnimalMaxAgeInclusive = 'Y' THEN CONCAT(Nat_NormalValueAnimalMinAge,' - ',Nat_NormalValueAnimalMaxAge,' (',Nat_NormalValueAnimalAgeUnit,')')
    WHEN Nat_NormalValueAnimalMinAgeInclusive = 'Y' AND Nat_NormalValueAnimalMaxAgeInclusive = 'N' THEN CONCAT(Nat_NormalValueAnimalMinAge,' > ',Nat_NormalValueAnimalMaxAge,' (',Nat_NormalValueAnimalAgeUnit,')')
    WHEN Nat_NormalValueAnimalMinAgeInclusive = 'N' AND Nat_NormalValueAnimalMaxAgeInclusive = 'Y' THEN CONCAT(Nat_NormalValueAnimalMinAge,' < ',Nat_NormalValueAnimalMaxAge,' (',Nat_NormalValueAnimalAgeUnit,')')
    ELSE '-'
END as Nat_NormalValueAnimalAge,
CASE
    WHEN Nat_NormalValueAnimalMinValueInclusive = 'Y' AND Nat_NormalValueAnimalMaxValueInclusive = 'Y' THEN CONCAT(Nat_NormalValueAnimalMinValue,' - ',Nat_NormalValueAnimalMaxValue)
    WHEN Nat_NormalValueAnimalMinValueInclusive = 'Y' AND Nat_NormalValueAnimalMaxValueInclusive = 'N' THEN CONCAT(Nat_NormalValueAnimalMinValue,' > ',Nat_NormalValueAnimalMaxValue)
    WHEN Nat_NormalValueAnimalMinValueInclusive = 'N' AND Nat_NormalValueAnimalMaxValueInclusive = 'Y' THEN CONCAT(Nat_NormalValueAnimalMinValue,' < ',Nat_NormalValueAnimalMaxValue)
    ELSE '-'
END as Nat_NormalValueAnimalValue,
CASE
WHEN Nat_NormalValueAnimalAgeUnit = 'HARI' THEN '1'
WHEN Nat_NormalValueAnimalAgeUnit = 'BULAN' THEN '2'
WHEN Nat_NormalValueAnimalAgeUnit = 'TAHUN' THEN '3'
ELSE '4'
END as ageunit,
IF(Nat_NormalValueAnimalIsAbnormal = 'N',Nat_NormalValueAnimalID,Nat_NormalValueAnimalParentID) as parentid,
					DATE_FORMAT(Nat_NormalValueAnimalValidDate,'%d-%m-%Y') as Nat_NormalValueAnimalValidDatex,
                    '' as tes,
					'xxx' as tests,
					'' as action,
					'N' as show_detail
					
					FROM nat_normalvalueanimal
					LEFT JOIN nat_sex ON Nat_NormalValueAnimalNat_SexID = Nat_SexID
					LEFT JOIN nat_animal ON Nat_NormalValueAnimalNat_AnimalID = Nat_AnimalID
					JOIN nat_test ON Nat_NormalValueAnimalNat_TestID = Nat_TestID
					JOIN nat_methode ON Nat_NormalValueAnimalNat_MethodeID = Nat_MethodeID AND Nat_MethodeIsActive = 'Y'
					JOIN nat_normalvaluetype ON Nat_NormalValueAnimalNat_NormalValueTypeID = Nat_NormalValueTypeID
					LEFT JOIN nat_flag ON Nat_NormalValueAnimalNat_FlagID = Nat_FlagID
					WHERE	
                    Nat_NormalValueAnimalIsActive = 'Y' AND Nat_NormalValueAnimalNat_TestID = {$prm['id']} AND
					Nat_MethodeName like '%{$methodename}%' 
					$filter
				GROUP BY Nat_NormalValueAnimalID
				ORDER BY Nat_NormalValueAnimalNat_SexID ASC, Nat_NormalValueAnimalNat_AnimalID ASC, ageunit ASC, Nat_NormalValueAnimalMinAge ASC, Nat_NormalValueAnimalMaxAge ASC, parentid ASC
				";
				//echo $query;
		$rows = $this->db_regional->query($query)->result_array();
		//echo $this->db_regional->last_query();
		if($rows){
			
			foreach($rows as $k => $v){		
				//$rows[$k]['tesx'] = json_decode($x->n);	
				$rows[$k]['tests'] = $this->add_test($v['Nat_NormalValueAnimalID']);	
				$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 add_test($orderid){
		$query ="SELECT IFNULL(Nat_NormalValueLangAnimalID,0) as id,
    IFNULL(Nat_NormalValueLangAnimalNat_NormalValueAnimalID,$orderid) as Nat_NormalValueLangAnimalNat_NormalValueAnimalID,
    Nat_LangName,
    IFNULL(Nat_NormalValueLangAnimalNote, '') as Nat_NormalValueLangAnimalNote,
    IFNULL(Nat_NormalValueLangAnimalNoteSI, '') as Nat_NormalValueLangAnimalNoteSI,
	IFNULL(Nat_NormalValueLangAnimalDescription, '') as Nat_NormalValueLangAnimalDescription,
	IFNULL(Nat_NormalValueLangAnimalDescriptionSI, '') as Nat_NormalValueLangAnimalDescriptionSI,
    IFNULL(Nat_NormalValueLangAnimalNat_LangID, Nat_LangID) as Nat_NormalValueLangAnimalNat_LangID,
    'xxx' as action,
    'Y' as show_detail,
    Nat_LangID as idx,
    Nat_LangID
    from nat_lang
    left join nat_normalvaluelanganimal ON Nat_LangID = Nat_NormalValueLangAnimalNat_LangID  AND Nat_NormalValueLangAnimalNat_NormalValueAnimalID = {$orderid}
    left join nat_normalvalueanimal ON Nat_NormalValueLangAnimalNat_NormalValueAnimalID = Nat_NormalValueAnimalID 
    where 
    Nat_LangIsActive = 'Y'";
				//echo $query;
		$rows = $this->db_regional->query($query)->result_array();
		if(!$rows)
			$rows = array();
		return $rows;
	}
	
	function savenewnilainormal(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$pvaliddate = date('Y-m-d',strtotime($prm['Nat_NormalValueAnimalValidDate	']));
		$userid = $this->sys_user["M_UserID"];
		$query ="INSERT INTO nat_normalvalueanimal (
						Nat_NormalValueAnimalNat_TestID,
						Nat_NormalValueAnimalNat_MethodeID,
						Nat_NormalValueAnimalNat_NormalValueTypeID, 
						Nat_NormalValueAnimalNat_FlagID,	 
						Nat_NormalValueAnimalValidDate,	 
						Nat_NormalValueAnimalMinAge,	 
						Nat_NormalValueAnimalMaxAge, 
						Nat_NormalValueAnimalMinAgeInclusive,
						Nat_NormalValueAnimalMaxAgeInclusive,
						Nat_NormalValueAnimalAgeUnit,
						Nat_NormalValueAnimalNat_SexID, 
						Nat_NormalValueAnimalNat_AnimalID,
						Nat_NormalValueAnimalNote, 
						Nat_NormalValueAnimalMinValue, 
						Nat_NormalValueAnimalMaxValue, 
						Nat_NormalValueAnimalMinValueInclusive,
						Nat_NormalValueAnimalMaxValueInclusive,
						Nat_NormalValueAnimalDescription,
						Nat_NormalValueAnimalIsAbnormal,
						Nat_NormalValueAnimalUserID,
						Nat_NormalValueAnimalLastUpdated
						
					)
					VALUES(
						'{$prm['Nat_NormalValueAnimalNat_TestID']}',
						'{$prm['Nat_NormalValueAnimalNat_MethodeID']}',
						'{$prm['Nat_NormalValueAnimalNat_NormalValueTypeID']}',
						'{$prm['Nat_NormalValueAnimalNat_FlagID']}',
						'{$prm['Nat_NormalValueAnimalValidDate']}',
						'{$prm['Nat_NormalValueAnimalMinAge']}',
						'{$prm['Nat_NormalValueAnimalMaxAge']}',
						'{$prm['Nat_NormalValueAnimalMinAgeInclusive']}',
						'{$prm['Nat_NormalValueAnimalMaxAgeInclusive']}',
						'{$prm['Nat_NormalValueAnimalAgeUnit']}',
						'{$prm['Nat_NormalValueAnimalNat_SexID']}',
						'{$prm['Nat_NormalValueAnimalNat_AnimalID']}',
						'{$prm['Nat_NormalValueAnimalNote']}',
						'{$prm['Nat_NormalValueAnimalMinValue']}',
						'{$prm['Nat_NormalValueAnimalMaxValue']}',
						'{$prm['Nat_NormalValueAnimalMinValueInclusive']}',
						'{$prm['Nat_NormalValueAnimalMaxValueInclusive']}',
						'{$prm['Nat_NormalValueAnimalDescription']}',
						'{$prm['Nat_NormalValueAnimalIsAbnormal']}',
						'{$userid}',
						NOW()
					)
				";
				//echo $query;
		$rows = $this->db_regional->query($query);
		$ordernormalvalue_id = $this->db_regional->insert_id();	
		$sql_param = $ordernormalvalue_id;
		$sql = "select * from nat_normalvalueanimal
		where Nat_NormalValueAnimalID= ?";
	 $query = $this->db_regional->query($sql,$sql_param); 

	 if ($query) {
		$rows = $query->result_array();
	 } else {
		$this->sys_error_db("nat_normalvalueanimal select by normalvalue");
		exit;
	 }
	 /*$sql = "insert into nasional_log.log_normalvalue(Log_NormalValueCode, 
		Log_NormalValueData, Log_NormalValueUserID) values(?,?,?)";
	 $data = json_encode($rows);
	 $userID = $this->sys_user["M_UserID"];
	 $sql_param = array("NormalValue.Add", $data , $userID);
	 $query = $this->db_regional->query($sql,$sql_param); 
	 if (! $query) {
		$this->sys_error_db( $this->db_regional->last_query());
		exit;
	 }*/
		$querylang ="INSERT INTO nat_normalvaluelanganimal (
			Nat_NormalValueLangAnimalNat_NormalValueAnimalID, 
			Nat_NormalValueLangAnimalNat_LangID, 
			Nat_NormalValueLangAnimalNote,	  
			Nat_NormalValueLangAnimalDescription,	 	 	 
			Nat_NormalValueLangAnimalUserID, 
			Nat_NormalValueLangAnimalCreated, 
			Nat_NormalValueLangAnimalLastUpdated
			
		)
		VALUES(
			'{$ordernormalvalue_id}',
			'1',
			'{$prm['Nat_NormalValueAnimalNote']}',
			'{$prm['Nat_NormalValueAnimalDescription']}',
			'{$userid}',
			NOW(),
			now()
		)
	";	
	$rows = $this->db_regional->query($querylang);		
		$result = array(
			"total" => 1 , 
			"records" => array('status'=>'OK')
		); 
		$this->sys_ok($result);
		exit;
	}
	
	function saveeditnilainormal(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$order_id = $prm['Nat_NormalValueAnimalID'];
		$pvaliddate = date('Y-m-d',strtotime($prm['Nat_NormalValueAnimalValidDate	']));
		$userid = $this->sys_user["M_UserID"];
		if($prm['Nat_NormalValueAnimalNat_NormalValueTypeID'] == '4'){
			$prm['Nat_NormalValueAnimalMinAge'] = 0;
			$prm['Nat_NormalValueAnimalMaxAge'] = 0;
			$prm['Nat_NormalValueAnimalMinAgeInclusive'] = 'N';
			$prm['Nat_NormalValueAnimalMaxAgeInclusive'] = 'N';
			$prm['Nat_NormalValueAnimalAgeUnit'] = '';
			$prm['Nat_NormalValueAnimalNat_SexID'] = 0;
			
		}
		if($prm['Nat_NormalValueAnimalNat_NormalValueTypeID'] == '2'){
			$prm['Nat_NormalValueAnimalNat_SexID'] = 0;
			
		}
		if($prm['Nat_NormalValueAnimalNat_NormalValueTypeID'] == '3'){
			$prm['Nat_NormalValueAnimalMinAge'] = 0;
			$prm['Nat_NormalValueAnimalMaxAge'] = 0;
			$prm['Nat_NormalValueAnimalMinAgeInclusive'] = 'N';
			$prm['Nat_NormalValueAnimalMaxAgeInclusive'] = 'N';
			$prm['Nat_NormalValueAnimalAgeUnit'] = '';
		}
		
		$query ="UPDATE nat_normalvalueanimal SET 
						Nat_NormalValueAnimalNat_TestID = '{$prm['Nat_NormalValueAnimalNat_TestID']}',
						Nat_NormalValueAnimalNat_MethodeID = '{$prm['Nat_NormalValueAnimalNat_MethodeID']}',
						Nat_NormalValueAnimalNat_NormalValueTypeID = '{$prm['Nat_NormalValueAnimalNat_NormalValueTypeID']}', 
						Nat_NormalValueAnimalNat_FlagID = '{$prm['Nat_NormalValueAnimalNat_FlagID']}',	 
						Nat_NormalValueAnimalValidDate = '{$prm['Nat_NormalValueAnimalValidDate']}',	 
						Nat_NormalValueAnimalMinAge = '{$prm['Nat_NormalValueAnimalMinAge']}',	 
						Nat_NormalValueAnimalMaxAge =  '{$prm['Nat_NormalValueAnimalMaxAge']}', 
						Nat_NormalValueAnimalMinAgeInclusive = '{$prm['Nat_NormalValueAnimalMinAgeInclusive']}',
						Nat_NormalValueAnimalMaxAgeInclusive =  '{$prm['Nat_NormalValueAnimalMaxAgeInclusive']}',
						Nat_NormalValueAnimalAgeUnit = '{$prm['Nat_NormalValueAnimalAgeUnit']}',
						Nat_NormalValueAnimalNat_SexID = '{$prm['Nat_NormalValueAnimalNat_SexID']}', 
						Nat_NormalValueAnimalNat_AnimalID = '{$prm['Nat_NormalValueAnimalNat_AnimalID']}', 
						Nat_NormalValueAnimalNote = '{$prm['Nat_NormalValueAnimalNote']}', 
						Nat_NormalValueAnimalMinValue = '{$prm['Nat_NormalValueAnimalMinValue']}', 
						Nat_NormalValueAnimalMaxValue = '{$prm['Nat_NormalValueAnimalMaxValue']}', 
						Nat_NormalValueAnimalMinValueInclusive = '{$prm['Nat_NormalValueAnimalMinValueInclusive']}',
						Nat_NormalValueAnimalMaxValueInclusive = '{$prm['Nat_NormalValueAnimalMaxValueInclusive']}',
						Nat_NormalValueAnimalDescription = '{$prm['Nat_NormalValueAnimalDescription']}',
						Nat_NormalValueAnimalIsAbnormal = '{$prm['Nat_NormalValueAnimalIsAbnormal']}',
						Nat_NormalValueAnimalUserID = '{$userid}',
						Nat_NormalValueAnimalLastUpdated = now()
					WHERE
					Nat_NormalValueAnimalID = '{$prm['Nat_NormalValueAnimalID']}'
				";
				//echo $query;
		$rows = $this->db_regional->query($query);
		$sql_param = $order_id;
		$sql = "select * from nat_normalvalueanimal
		where Nat_NormalValueAnimalID= ?";
	 $query = $this->db_regional->query($sql,$sql_param); 

	 if ($query) {
		$rows = $query->result_array();
	 } else {
		$this->sys_error_db("nat_normalvalueanimal select by normalvalue");
		exit;
	 }
	/* $sql = "insert into nasional_log.log_normalvalue(Log_NormalValueCode, 
		Log_NormalValueData, Log_NormalValueUserID) values(?,?,?)";
	 $data = json_encode($rows);
	 $userID = $this->sys_user["M_UserID"];
	 $sql_param = array("NormalValue.Update", $data , $userID);
	 $query = $this->db_regional->query($sql,$sql_param); 
	 if (! $query) {
		$this->sys_error_db( $this->db_regional->last_query());
		exit;
	 }*/
		
		$result = array(
			"total" => 1 , 
			"records" => array('status'=>'OK')
		); 
		$this->sys_ok($result);
		exit;
	}
	function savenewnilaiabnormal(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$pvaliddate = date('Y-m-d',strtotime($prm['Nat_NormalValueAnimalValidDate	']));
		$userid = $this->sys_user["M_UserID"];
		$query ="INSERT INTO nat_normalvalueanimal (
						Nat_NormalValueAnimalNat_TestID,
						Nat_NormalValueAnimalNat_MethodeID,
						Nat_NormalValueAnimalNat_NormalValueTypeID, 
						Nat_NormalValueAnimalNat_FlagID,	 
						Nat_NormalValueAnimalValidDate,	 
						Nat_NormalValueAnimalMinAge,	 
						Nat_NormalValueAnimalMaxAge, 
						Nat_NormalValueAnimalMinAgeInclusive,
						Nat_NormalValueAnimalMaxAgeInclusive,
						Nat_NormalValueAnimalAgeUnit,
						Nat_NormalValueAnimalNat_SexID, 
						Nat_NormalValueAnimalNat_AnimalID, 
						Nat_NormalValueAnimalNote, 
						Nat_NormalValueAnimalMinValue, 
						Nat_NormalValueAnimalMaxValue, 
						Nat_NormalValueAnimalMinValueInclusive,
						Nat_NormalValueAnimalMaxValueInclusive,
						Nat_NormalValueAnimalDescription,
						Nat_NormalValueAnimalParentID,
						Nat_NormalValueAnimalIsAbnormal,
						Nat_NormalValueAnimalUserID,
						Nat_NormalValueAnimalLastUpdated
						
					)
					VALUES(
						'{$prm['Nat_NormalValueAnimalNat_TestID']}',
						'{$prm['Nat_NormalValueAnimalNat_MethodeID']}',
						'{$prm['Nat_NormalValueAnimalNat_NormalValueTypeID']}',
						'{$prm['Nat_NormalValueAnimalNat_FlagID']}',
						'{$prm['Nat_NormalValueAnimalValidDate']}',
						'{$prm['Nat_NormalValueAnimalMinAge']}',
						'{$prm['Nat_NormalValueAnimalMaxAge']}',
						'{$prm['Nat_NormalValueAnimalMinAgeInclusive']}',
						'{$prm['Nat_NormalValueAnimalMaxAgeInclusive']}',
						'{$prm['Nat_NormalValueAnimalAgeUnit']}',
						'{$prm['Nat_NormalValueAnimalNat_SexID']}',
						'{$prm['Nat_NormalValueAnimalNat_AnimalID']}',
						'{$prm['Nat_NormalValueAnimalNote']}',
						'{$prm['Nat_NormalValueAnimalMinValue']}',
						'{$prm['Nat_NormalValueAnimalMaxValue']}',
						'{$prm['Nat_NormalValueAnimalMinValueInclusive']}',
						'{$prm['Nat_NormalValueAnimalMaxValueInclusive']}',
						'{$prm['Nat_NormalValueAnimalDescription']}',
						'{$prm['Nat_NormalValueAnimalParentID']}',
						'{$prm['Nat_NormalValueAnimalIsAbnormal']}',
						'{$userid}',
						NOW()
					)
				";
				//echo $query;
		$rows = $this->db_regional->query($query);
		$ordernormalvalue_id = $this->db_regional->insert_id();	
		$sql_param = $ordernormalvalue_id;
		$sql = "select * from nat_normalvalueanimal
		where Nat_NormalValueAnimalID= ?";
	 $query = $this->db_regional->query($sql,$sql_param); 

	 if ($query) {
		$rows = $query->result_array();
	 } else {
		$this->sys_error_db("nat_normalvalueanimal select by normalvalue");
		exit;
	 }
	 /*$sql = "insert into nasional_log.log_normalvalue(Log_NormalValueCode, 
		Log_NormalValueData, Log_NormalValueUserID) values(?,?,?)";
	 $data = json_encode($rows);
	 $userID = $this->sys_user["M_UserID"];
	 $sql_param = array("NormalValue.Add", $data , $userID);
	 $query = $this->db_regional->query($sql,$sql_param); 
	 if (! $query) {
		$this->sys_error_db( $this->db_regional->last_query());
		exit;
	 }*/
		$querylang ="INSERT INTO nat_normalvaluelanganimal (
			Nat_NormalValueLangAnimalNat_NormalValueAnimalID, 
			Nat_NormalValueLangAnimalNat_LangID, 
			Nat_NormalValueLangAnimalNote,	  
			Nat_NormalValueLangAnimalDescription,	 	 	 
			Nat_NormalValueLangAnimalUserID, 
			Nat_NormalValueLangAnimalCreated, 
			Nat_NormalValueLangAnimalLastUpdated
			
		)
		VALUES(
			'{$ordernormalvalue_id}',
			'1',
			'{$prm['Nat_NormalValueAnimalNote']}',
			'{$prm['Nat_NormalValueAnimalDescription']}',
			'{$userid}',
			NOW(),
			now()
		)
	";	
	$rows = $this->db_regional->query($querylang);		
		$result = array(
			"total" => 1 , 
			"records" => array('status'=>'OK')
		); 
		$this->sys_ok($result);
		exit;
	}
	function deletenilainormal(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$userid = $this->sys_user["M_UserID"];
		$query ="UPDATE nat_normalvalueanimal SET 
						Nat_NormalValueAnimalIsActive = 'N',
						Nat_NormalValueAnimalUserID = '{$userid}'
					WHERE
					Nat_NormalValueAnimalID = '{$prm['Nat_NormalValueAnimalID']}'
				";
				//echo $query;
		$rows = $this->db_regional->query($query);
		$sql_param = $prm['Nat_NormalValueAnimalID'];
		$sql = "select * from nat_normalvalueanimal
		where Nat_NormalValueAnimalID= ?";
	 $query = $this->db_regional->query($sql,$sql_param); 

	 if ($query) {
		$rows = $query->result_array();
	 } else {
		$this->sys_error_db("nat_normalvalueanimal select by normalvalue");
		exit;
	 }
	/* $sql = "insert into nasional_log.log_normalvalue(Log_NormalValueCode, 
		Log_NormalValueData, Log_NormalValueUserID) values(?,?,?)";
	 $data = json_encode($rows);
	 $userID = $this->sys_user["M_UserID"];
	 $sql_param = array("NormalValue.Delete", $data , $userID);
	 $query = $this->db_regional->query($sql,$sql_param); 
	 if (! $query) {
		$this->sys_error_db( $this->db_regional->last_query());
		exit;
	 }*/
		$query ="UPDATE nat_normalvaluelanganimal SET 
		Nat_NormalValueLangAnimalIsActive = 'N',
		Nat_NormalValueLangAnimalUserID = '{$userid}'
	WHERE
	Nat_NormalValueLangAnimalNat_NormalValueAnimalID = '{$prm['Nat_NormalValueAnimalID']}' AND Nat_NormalValueLangAnimalIsActive = 'Y'
";
//echo $query;
$rows = $this->db_regional->query($query);
		
		$result = array(
			"total" => 1 , 
			"records" => array('status'=>'OK')
		); 
		$this->sys_ok($result);
		exit;
	}
	function savenormalvaluelang(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$normalvalue_id = $prm['normalvalueid'];
		$userid = $this->sys_user["M_UserID"];   
			foreach($prm['ordertests'] as $k=>$v){
				if($v['id'] == 0 || $v['id'] == '0'){
					$query = "INSERT INTO nat_normalvaluelanganimal (
						Nat_NormalValueLangAnimalNat_NormalValueAnimalID, 
						Nat_NormalValueLangAnimalNat_LangID, 
						Nat_NormalValueLangAnimalNote,	 
						Nat_NormalValueLangAnimalNoteSI,	 
						Nat_NormalValueLangAnimalDescription,	 
						Nat_NormalValueLangAnimalDescriptionSI,	 	 
						Nat_NormalValueLangAnimalUserID, 
						Nat_NormalValueLangAnimalCreated, 
						Nat_NormalValueLangAnimalLastUpdated
					)
					VALUES(
						?,?,?,?,?,?,?,now(),now()
					)";
					$insert_new_normalvaluelang = $this->db_regional->query($query,array(
														$normalvalue_id,
														$v['Nat_LangID'],
														$v['Nat_NormalValueLangAnimalNote'],
														$v['Nat_NormalValueLangAnimalNoteSI'],
														$v['Nat_NormalValueLangAnimalDescription'],
														$v['Nat_NormalValueLangAnimalDescriptionSI'],
														$userid
													));
				} else {
					$query = "UPDATE nat_normalvaluelanganimal SET 
					Nat_NormalValueLangAnimalNat_LangID = ?, 
					Nat_NormalValueLangAnimalNote = ?,	 
					Nat_NormalValueLangAnimalNoteSI = ?,	 
					Nat_NormalValueLangAnimalDescription = ?,	 
					Nat_NormalValueLangAnimalDescriptionSI = ?,	 	 
					Nat_NormalValueLangAnimalUserID = ?
					WHERE
					Nat_NormalValueLangAnimalID = ?";
			   $update_normalvaluelang = $this->db_regional->query($query,array($v['Nat_LangID'],$v['Nat_NormalValueLangAnimalNote'],$v['Nat_NormalValueLangAnimalNoteSI'],$v['Nat_NormalValueLangAnimalDescription'],$v['Nat_NormalValueLangAnimalDescriptionSI'],$userid,$v['id']));
				}
				
			}
			
			
		$result = array(
			"total" => 1 , 
			"records" => array('status'=>'OK')
		); 
		$this->sys_ok($result);
		exit;
	}
	function searchtest(){
		$prm = $this->sys_input;

		$max_rst = 12;
		$tot_count = 0;

		$q = [
			'search'     => '%'
		];

		if ($prm['search'] != '')
		{
			$q['search'] = "%{$prm['search']}%";  
		}

		$mou_id = $prm['mouid']; 
		  // QUERY TOTAL
		$sql = "SELECT count(*) as total
				FROM t_test
				JOIN t_price ON T_PriceT_TestID = T_TestID AND T_PriceIsCito = 'N' AND T_PriceM_MouID = '{$mou_id}'
				WHERE 
					T_TestName like ? AND
					T_TestIsActive = 'Y'
				ORDER BY T_TestName ASC";
		$query = $this->db_regional->query($sql,$q['search']);
		//echo $query;
		if ($query) { 
			$tot_count = $query->result_array()[0]["total"]; 
		} 
		else {
			$this->sys_error_db("test count",$this->db_regional);
			exit;
		}

		$sql = "
			SELECT 'Y' as editable, 0 as xid, T_TestID, T_TestCode, T_TestName, T_PriceAmount, T_PriceDisc, T_PriceDiscRp, T_PriceAmount - ((T_PriceDisc/100) * T_PriceAmount) - T_PriceDiscRp as total,
			Nat_TestID, Nat_TestIsBill, Nat_TestMinDP
			FROM one.t_test
			JOIN one.t_price ON T_PriceT_TestID = T_TestID AND T_PriceIsCito = 'N' AND T_PriceM_MouID = '{$mou_id}'
			JOIN one.m_mou ON M_MouID = '{$mou_id}'
			JOIN one.m_company ON M_MouNat_TestID = Nat_TestID
			WHERE 
				T_TestName like ? AND
				T_TestIsActive = 'Y'
			ORDER BY T_TestName ASC
		  ";
		$query = $this->db_regional->query($sql, array($q['search']));
			
		if ($query) { 
			$rows = $query->result_array();
			 //echo $this->db_regional->last_query();
			$result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
			$this->sys_ok($result);
		} 
		else {
			$this->sys_error_db("test rows",$this->db_regional);
			exit;
		}
	}

}