db_onedev = $this->load->database("onedev", true);
$this->db_smartone = $this->load->database("onedev", true);
}
function update_result_from_api(){
$nolab = 'R2509090031';
$sql = "SELECT *
FROM `api_result`
WHERE `api_ResultNolab` = '{$nolab}' LIMIT 50";
$query = $this->db_smartone->query($sql);
$rows = $query->result_array();
foreach($rows as $row){
$result = $row['api_ResultResult'];
$unit = $row['api_ResultUnit'];
$flag = $row['api_ResultFlag'];
$normal_value = $row['api_ResultNormalText'];
$methode = $row['api_ResultNormalMethode'];
$t_orderdetailid = $row['api_ResultT_OrderDetailID'];
$sql = "UPDATE t_orderdetail
SET T_OrderDetailResult = '{$result}',
T_OrderDetailNat_UnitName = '{$unit}',
T_OrderDetailResultFlag = '{$flag}',
T_OrderDetailNormalValueNote = '{$normal_value}',
T_OrderdetailNat_MethodeName = '{$methode}'
WHERE T_OrderDetailID = '{$t_orderdetailid}'";
//echo $sql."
";
$query = $this->db_smartone->query($sql);
if(!$query){
echo "error : ".$this->db_smartone->error();
return;
}
echo "update t_orderdetail : ".$t_orderdetailid."
";
}
}
function index() {
$this->load->library("Resultcalc");
$id = 946;
$rows = $this->resultcalc->auto($id);
echo " $rows";
}
function generate_random_number($length = 3) {
$characters = '0123456789';
$charactersLength = strlen($characters);
$randomString = '';
for ($i = 0; $i < $length; $i++) {
$randomString .= $characters[rand(0, $charactersLength - 1)];
}
return $randomString;
}
function ganti_paket_preregister($before_packet,$after_packet){
$userid = $this->generate_random_number(3);
echo "before_packet : ".$before_packet." - after_packet : ".$after_packet."
";
$this->db_onedev->trans_begin();
$sql = "SELECT * FROM `mcu_preregister_patients`
WHERE
`Mcu_PreregisterPatientsOrders` = '{$before_packet}' AND
`Mcu_PreregisterPatientsIsActive` = 'Y' AND
`Mcu_PreregisterPatientsIsRegistered` = 'N'";
$query = $this->db_onedev->query($sql);
if(!$query){
echo "error : ".$this->db_onedev->error();
return;
}
$rows = $query->result_array();
if(count($rows) == 0){
echo "no data";
return;
}
echo "total data : ".count($rows)."
";
echo "start update data
";
$errors = [];
foreach($rows as $row){
$pre_id = $row['Mcu_PreregisterPatientsID'];
$mgm_mcuid = $row['Mcu_PreregisterPatientsMgm_McuID'];
$m_patientid = $row['Mcu_PreregisterPatientsM_PatientID'];
$order_before = $row['Mcu_PreregisterPatientsOrders'];
$order_after = $after_packet;
$sql = "INSERT INTO temp_replace_packet(
pre_id,
mgm_mcuid,
m_patientid,
order_before,
order_after,
user,
created)
VALUES (?,?,?,?,?,?,NOW())";
$query = $this->db_onedev->query($sql, [$pre_id, $mgm_mcuid, $m_patientid, $order_before, $order_after, $userid]);
if(!$query){
echo "error : temp";
$errors[] = "error : ".$m_patientid;
//$this->db_onedev->trans_rollback();
echo "error : insert temp_replace_packet : ".$m_patientid."
";
continue;
}
echo "insert temp_replace_packet : ".$m_patientid."
";
$sql = "UPDATE mcu_preregister_patients
SET Mcu_PreregisterPatientsOrders = ?,
Mcu_PreregisterPatientsUserID = ?
WHERE Mcu_PreregisterPatientsID = ? AND
Mcu_PreregisterPatientsIsActive = 'Y' AND
Mcu_PreregisterPatientsIsRegistered = 'N' AND
Mcu_PreregisterPatientsOrders = ?";
//echo $sql."
";
$query = $this->db_onedev->query($sql, [$order_after, $userid, $pre_id, $order_before]);
if(!$query){
echo "error : update mcu_preregister_patients";
$errors[] = "error : ".$m_patientid;
continue;
}
// echo $this->db_onedev->last_query();
}
echo "end update data
";
if(count($errors) > 0){
echo "errors : ".count($errors)."
";
echo "errors : ".implode(", ", $errors)."
";
$this->db_onedev->trans_rollback();
return;
}
else{
$this->db_onedev->trans_commit();
echo "success
";
return;
}
}
function generate_uuid(){
// Generate 16 bytes (128 bits) of random data
$data = random_bytes(16);
// Set version to 0100
$data[6] = chr(ord($data[6]) & 0x0f | 0x40);
// Set bits 6-7 to 10
$data[8] = chr(ord($data[8]) & 0x3f | 0x80);
// Output the 36 character UUID
return vsprintf('%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex($data), 4));
}
function generate_code_string(){
$length = 5;
$characters = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
$code = '';
for ($i = 0; $i < $length; $i++) {
$code .= $characters[rand(0, strlen($characters) - 1)];
}
return $code;
}
function generate_code_form($preid,$orderid){
$userid = 555;
//echo "preid : ".$preid." - orderid : ".$orderid."
";
//return;
$sql = "SELECT COUNT(*) as total
FROM form_riwayat_pasien
WHERE FormRiwayatPasienPreregisterID = ? AND
FormRiwayatPasienIsActive = 'Y'
";
$qry = $this->db_onedev->query($sql, [$preid]);
//echo $this->db_onedev->last_query();
//exit;
if($qry){
$total = $qry->result_array()[0]['total'];
if($total == 0){
$code = $this->generate_code_string();
$uuid = $this->generate_uuid();
// echo "code : ".$code." - uuid : ".$uuid."
";
//exit;
$sql = "INSERT INTO form_riwayat_pasien (
FormRiwayatPasienPreregisterID,
FormRiwayatPasienCode,
FormRiwayatPasienUUID,
FormRiwayatPasienT_OrderHeaderID,
FormRiwayatPasienCreated,
FormRiwayatPasienCreatedUserID)
VALUES (?, ?, ?, ?, NOW(), ?)";
$qry = $this->db_onedev->query($sql, [$preid, $code, $uuid, $orderid, $userid]);
//echo $this->db_onedev->last_query();
//exit;
if(!$qry){
echo "error : ".$this->db_onedev->error();
return;
}
//return array('uuid' => $uuid, 'code' => $code);
$genformqrcode = $this->genformqrcode($uuid);
echo json_encode($genformqrcode);
}
}else{
echo "no data";
}
}
function genformqrcode($uuid){
$this->load->library('ciqrcode'); //pemanggilan library QR CODE
$home_dir = "/home/one/project/one/";
$target_dir = $home_dir . "one-media/one-qrpatient/";
$config['cacheable'] = false; //boolean, the default is true
//$config['cachedir'] = './assets/'; //string, the default is application/cache/
//$config['errorlog'] = './assets/'; //string, the default is application/logs/
$config['imagedir'] = $target_dir; //direktori penyimpanan qr code
$config['quality'] = true; //boolean, the default is true
$config['size'] = '1024'; //interger, the default is 1024
$config['black'] = array(224,255,255); // array, default is array(255,255,255)
$config['white'] = array(70,130,180); // array, default is array(0,0,0)
$this->ciqrcode->initialize($config);
$image_name="form_qr_".$uuid.".png"; //buat name dari qr code sesuai dengan nim
$params['data'] = 'https://cpone.aplikasi.web.id/one-ui/test/vuex/cpone-riwayat-form/?id='.$uuid; //data yang akan di jadikan QR CODE
$params['level'] = 'H'; //H=High
$params['size'] = 10;
$params['savename'] = $config['imagedir'].$image_name; //simpan image QR CODE ke folder assets/images/
$this->ciqrcode->generate($params); // fungsi untuk generate QR CODE
return array('image' => "https://cpone.aplikasi.web.id/one-media/one-qrpatient/".$image_name, 'url' => $params['data']);
//echo "selesai";
}
function fix_order_not_same_in_packet($labnumber,$packet_number){
$this->db_onedev->trans_begin();
$sql = "SELECT * FROM t_orderheader WHERE T_OrderHeaderLabNumber = '{$labnumber}'";
$query = $this->db_onedev->query($sql);
if(!$query){
$this->db_onedev->trans_rollback();
echo "error : ".$this->db_onedev->error();
return;
}
$rows = $query->row_array();
$order_id = $rows['T_OrderHeaderID'];
$sql = "SELECT *
FROM t_orderdetailorder
JOIN t_packet ON T_OrderDetailOrderT_PacketID = T_PacketID AND T_PacketIsActive = 'Y' AND
T_PacketSasCode = '{$packet_number}'
WHERE T_OrderDetailOrderT_OrderHeaderID = '{$order_id}' AND
T_OrderDetailOrderIsActive = 'Y'
LIMIT 1";
$query = $this->db_onedev->query($sql);
if(!$query){
$this->db_onedev->trans_rollback();
echo "error : ".$this->db_onedev->error();
return;
}
$get_packet = $query->result_array();
if(count($get_packet) == 0){
$this->db_onedev->trans_rollback();
echo "no data packet : ".$labnumber." - ".$packet_number."
";
return;
}
$packet_id = $get_packet[0]['T_PacketID'];
$orderdetail_order_id = $get_packet[0]['T_OrderDetailOrderID'];
$sql = "SELECT *
FROM t_packetdetail
JOIN t_packet ON T_PacketDetailT_PacketID = T_PacketID
JOIN t_test ON T_PacketDetailT_TestID = T_TestID AND T_TestIsActive = 'Y'
WHERE
T_PacketDetailT_PacketID = '{$packet_id}' AND
T_PacketDetailIsActive = 'Y'";
$query = $this->db_onedev->query($sql);
if(!$query){
$this->db_onedev->trans_rollback();
echo "error : ".$this->db_onedev->error();
return;
}
$packetdetails = $query->result_array();
if(count($packetdetails) == 0){
$this->db_onedev->trans_rollback();
echo "no data packetdetail : ".$labnumber." - ".$packet_number."
";
return;
}
$packet_price = $get_packet[0]['T_PacketPrice'];
/*loop packetdetail start*/
foreach($packetdetails as $packetdetail){
$test_id = $packetdetail['T_TestID'];
$sql = "SELECT *
FROM t_orderdetail
WHERE
T_OrderDetailT_OrderHeaderID = '{$order_id}' AND
T_OrderDetailT_TestID = '{$test_id}' AND T_OrderDetailIsActive = 'Y'";
$query = $this->db_onedev->query($sql);
if(!$query){
$this->db_onedev->trans_rollback();
echo "error : ".$this->db_onedev->error();
return;
}
$orderdetail = $query->result_array();
if(count($orderdetail) == 0){
$test_sascode = $packetdetail['T_TestSasCode'];
$sql = "SELECT *, IFNULL(Nat_UnitID,0) as Nat_UnitID, IFNULL(Nat_UnitName,'') as Nat_UnitName
FROM t_test
JOIN nat_test ON T_TestNat_TestID = Nat_TestID
LEFT JOIN nat_unit ON Nat_TestNat_UnitID = Nat_UnitID
WHERE
T_TestSasCode LIKE '{$test_sascode}%' AND T_TestIsActive = 'Y'";
$query = $this->db_onedev->query($sql);
if(!$query){
$this->db_onedev->trans_rollback();
echo "error : ".$this->db_onedev->error();
return;
}
$tests = $query->result_array();
foreach($tests as $test){
$isPanel = 'N';
$isPanelChildren = 'N';
$isPanelChildrenPrintNota = 'N';
$price = $test_id == $test['T_TestID'] ? $packetdetail['T_PacketDetailPrice'] : 0;
$price_for_disc = $test_id == $test['T_TestID'] ? $packetdetail['T_PacketDetailPrice'] : 0;
$disc = 0;
$disc_amount = 0;
$disc_total = 0;
$total = $price;
$nat_unitid = $test['Nat_UnitID'];
$nat_unit_name = $test['Nat_UnitName'];
$sql = "INSERT INTO t_orderdetail(
T_OrderDetailT_OrderHeaderID,
T_OrderDetailT_OrderDetailOrderID,
T_OrderDetailT_TestID,
T_OrderDetailT_TestCode,
T_OrderDetailT_TestSasCode,
T_OrderDetailT_TestName,
T_OrderDetailT_TestIsResult,
T_OrderDetailT_TestIsPanel,
T_OrderDetailT_TestIsPanelChildren,
T_OrderDetailT_TestIsPanelChildrenPrintNota,
T_OrderDetailT_TestIsPrice,
T_OrderDetailPrice,
T_OrderDetailPriceForDisc,
T_OrderDetailDisc,
T_OrderDetailDiscAmount,
T_OrderDetailDiscTotal,
T_OrderDetailTotal,
T_OrderDetailNat_UnitID,
T_OrderDetailNat_UnitName,
T_OrderDetailCreated,
T_OrderDetailCreatedUserID)
VALUES (
'{$order_id}',
'{$orderdetail_order_id}',
'{$test['T_TestID']}',
'{$test['T_TestCode']}',
'{$test['T_TestSasCode']}',
'{$test['T_TestName']}',
'{$test['T_TestIsResult']}',
'{$isPanel}',
'{$isPanelChildren}',
'{$isPanelChildrenPrintNota}',
'{$test['T_TestIsPrice']}',
'{$price}',
'{$price_for_disc}',
'{$disc}',
'{$disc_amount}',
'{$disc_total}',
'{$total}',
'{$nat_unitid}',
'{$nat_unit_name}',
'NOW()',
55516
)
";
//echo $sql."
";
$query = $this->db_onedev->query($sql);
if(!$query){
$this->db_onedev->trans_rollback();
echo "error : insert : ".$test['T_TestName']."
";
echo $this->db_onedev->last_query();
return;
}
}
}
}
/*loop packetdetail end*/
$sql = "SELECT SUM(T_OrderDetailTotal) as total_detail
FROM t_orderdetail
WHERE T_OrderDetailT_OrderHeaderID = '{$order_id}' AND T_OrderDetailIsActive = 'Y'";
$query = $this->db_onedev->query($sql);
if(!$query){
$this->db_onedev->trans_rollback();
echo "error : ".$this->db_onedev->error();
return;
}
$orderdetail = $query->row_array();
if($orderdetail['total_detail'] != $packet_price){
$this->db_onedev->trans_rollback();
echo "total detail : ".$orderdetail['total_detail']." - packet price : ".$packet_price."
";
echo "error total not same : ".$labnumber." - ".$packet_number."
";
}
else{
$sql = "UPDATE t_orderheader
SET T_OrderHeaderSubTotal = '{$orderdetail['total_detail']}',
T_OrderHeaderTotal = '{$orderdetail['total_detail']}'
WHERE
T_OrderHeaderID = '{$order_id}' AND T_OrderHeaderIsActive = 'Y'";
$query = $this->db_onedev->query($sql);
if(!$query){
$this->db_onedev->trans_rollback();
echo "error : ".$this->db_onedev->error();
return;
}
echo "total detail : ".$orderdetail['total_detail']." - packet price : ".$packet_price."
";
echo "success : ".$labnumber." - ".$packet_number."
";
}
$this->db_onedev->trans_commit();
$this->fix_ordersample($labnumber);
$this->fix_order_location($labnumber);
}
function fix_ordersample($labnumber){
$this->db_onedev->trans_begin();
$sql = "SELECT T_OrderHeaderID,T_OrderHeaderLabNumber, T_TestID, T_SampleTypeID , T_SampleTypeSuffix, T_SampleStationIsNonLab, T_SampleStationID
from t_orderheader
JOIN t_orderdetail ON t_orderheaderid = t_orderdetailt_orderheaderid AND t_orderdetailIsActive = 'Y'
JOIN t_test ON t_orderdetailt_testid = t_testid AND T_TestIsResult = 'Y'
JOIN t_sampletype ON T_TestT_SampleTypeID = T_SampleTypeID
JOIN t_bahan ON T_SampleTypeT_BahanID = T_BahanID
JOIN t_samplestation ON T_BahanT_SampleStationID = T_SampleStationID
where T_OrderHeaderLabNumber = '{$labnumber}'
group by T_SampleTypeID";
$query = $this->db_onedev->query($sql);
if(!$query){
echo "error : ".$this->db_onedev->error();
return;
}
$rows = $query->result_array();
$new_insert = [];
foreach($rows as $row){
$lab_no = $row['T_OrderHeaderLabNumber'];
$test_id = $row['T_TestID'];
$sample_id = $row['T_SampleTypeID'];
$sample_code = $row['T_SampleTypeSuffix'];
$barcode = $lab_no . $sample_code;
$isnonlab = $row['T_SampleStationIsNonLab'];
$samplestation_id = $row['T_SampleStationID'];
$counter_barcode = 1;
$sql = "SELECT *
FROM t_barcodelab
WHERE T_BarcodeLabT_OrderHeaderID = '{$row['T_OrderHeaderID']}' AND
T_BarcodeLabT_SampleTypeID = '{$row['T_SampleTypeID']}' AND T_BarcodeLabIsActive = 'Y'";
$query = $this->db_onedev->query($sql);
if(!$query){
echo "error : ".$this->db_onedev->error();
return;
}
$barcodelab = $query->result_array();
//echo count($barcodelab)."
";
if(count($barcodelab) == 0){
$new_insert[] = [
'T_OrderHeaderID' => $row['T_OrderHeaderID'],
'T_SampleTypeID' => $sample_id,
'T_BarcodeLabBarcode' => $barcode,
'T_BarcodeLabCounter' => $counter_barcode
];
$barcode = $barcode . $counter_barcode;
$sql = "INSERT INTO t_barcodelab(
T_BarcodeLabT_OrderHeaderID,
T_BarcodeLabBarcode,
T_BarcodeLabT_SampleTypeID,
T_BarcodeLabCounter)
VALUES ('{$row['T_OrderHeaderID']}','{$barcode}','{$sample_id}','{$counter_barcode}')";
//echo $sql."
";
$qry = $this->db_onedev->query($sql);
//echo $this->db_smartone->last_query();
if (!$qry) {
$this->db_onedev->trans_rollback();
$this->sys_error_db(["status" => "ERR", "message" => "insert t_barcodelab | " .
$this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
exit;
}
$last_id_barcode = $this->db_smartone->insert_id();
if ($isnonlab == "") {
$sql = "INSERT INTO t_ordersample(
T_OrderSampleT_OrderHeaderID,
T_OrderSampleT_SampleTypeID,
T_OrderSampleT_BarcodeLabID,
T_OrderSampleBarcode,
T_OrderSampleT_SampleStationID)
VALUES ('{$row['T_OrderHeaderID']}','{$sample_id}','{$last_id_barcode}','{$barcode}','{$samplestation_id}')";
//echo $sql."
";
$qry = $this->db_onedev->query($sql);
if (!$qry) {
$this->db_onedev->trans_rollback();
$this->sys_error_db(["status" => "ERR", "message" => "insert t_ordersample | " .
$this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
exit;
}
}
}
}
$this->db_onedev->trans_commit();
if(count($new_insert) > 0){
return json_encode([
"status" => "OK",
"message" => "success",
"data" => $new_insert
]);
}
else{
return json_encode([
"status" => "OK",
"message" => "no data",
"data" => []
]);
}
}
function price_not_same($stardate,$enddate){
$sql = "SELECT *
FROM t_orderheader
JOIN t_orderdetailorder ON T_OrderHeaderID = T_OrderDetailOrderT_OrderHeaderID AND T_OrderDetailOrderIsActive = 'Y'
WHERE T_OrderHeaderIsActive = 'Y' AND DATE(T_OrderHeaderDate) BETWEEN ? AND ?
GROUP BY T_OrderHeaderID";
$query = $this->db_onedev->query($sql, array($stardate, $enddate));
if(!$query){
echo "error : ".$this->db_onedev->error();
return;
}
$rows = $query->result_array();
foreach($rows as $row){
$sql = "SELECT SUM(T_OrderDetailTotal) as total_detail
FROM t_orderdetail
WHERE T_OrderDetailIsActive = 'Y' AND T_OrderDetailT_OrderHeaderID = ?";
$query = $this->db_onedev->query($sql, array($row['T_OrderHeaderID']));
if(!$query){
echo "error : ".$this->db_onedev->error();
return;
}
$orderdetail = $query->row_array();
if($orderdetail['total_detail'] != $row['T_OrderHeaderTotal']){
$sql = "UPDATE t_orderheader SET
T_OrderHeaderSubTotal = {$orderdetail['total_detail']},
T_OrderHeaderTotal = {$orderdetail['total_detail']}
WHERE T_OrderHeaderID = {$row['T_OrderHeaderID']} AND T_OrderHeaderIsActive = 'Y'";
//echo $sql."
";
$this->db_onedev->query($sql);
//echo $this->db_onedev->last_query();
echo $row['T_OrderHeaderLabNumber']." - ".$orderdetail['total_detail']." - ".$row['T_OrderHeaderTotal']."
";
}
}
}
function delet_duplicate_translation(){
$sql = "UPDATE translate_word tw1
JOIN (
SELECT Translate_WordFrom, MAX(Translate_WordID) as latest_id
FROM translate_word
WHERE Translate_WordIsActive = 'Y'
GROUP BY Translate_WordFrom
HAVING COUNT(*) > 1
) tw2 ON tw1.Translate_WordFrom = tw2.Translate_WordFrom
SET tw1.Translate_WordIsActive = 'N'
WHERE tw1.Translate_WordIsActive = 'Y'
AND tw1.Translate_WordID != tw2.latest_id;";
$query = $this->db_onedev->query($sql);
if(!$query){
echo "error : ".$this->db_onedev->error();
return;
}
echo "success";
}
function delete_reflek_27($mgm_mcu_number){
$sql = "SELECT *
FROM t_orderheader
JOIN mgm_mcu ON T_OrderHeaderMgm_McuID = Mgm_McuID AND Mgm_McuIsActive = 'Y'
AND Mgm_McuNumber = ? AND T_OrderHeaderIsActive = 'Y'
JOIN mgm_mcutemplate ON Mgm_McuTemplateMgm_McuID = Mgm_McuID AND Mgm_McuTemplateIsActive = 'Y'";
$rows = $this->db_onedev->query($sql, array($mgm_mcu_number))->result_array();
if(count($rows) > 0){
foreach($rows as $row){
$sql = "SELECT *
FROM so_resultentry
WHERE So_ResultEntryT_OrderHeaderID = ? AND So_ResultEntryIsActive = 'Y' AND So_ResultEntryNonlab_TemplateID = 27 LIMIT 1";
$so_resultentry = $this->db_onedev->query($sql, array($row['T_OrderHeaderID']))->result_array();
//echo $this->db_onedev->last_query();
if(count($so_resultentry) > 0){
$so_reid = $so_resultentry[0]['So_ResultEntryID'];
//echo $so_reid."
";
$sql = "SELECT COUNT(*) as exist_dt FROM so_resultentry_fisik_umum
WHERE So_ResultEntryFisikUmumFisikTemplateID IN (40,27) AND
So_ResultEntryFisikUmumSo_ResultEntryID = {$so_reid} AND
So_ResultEntryFisikUmumIsActive = 'Y'";
//echo $sql;
$so_resultentry_fisik_umum = $this->db_onedev->query($sql)->row_array();
if($so_resultentry_fisik_umum['exist_dt'] == 2){
$sql = "UPDATE so_resultentry_fisik_umum SET
So_ResultEntryFisikUmumIsActive = 'N',
So_ResultEntryFisikUmumDeleted = NOW(),
So_ResultEntryFisikUmumDeletedUserID = 666
WHERE So_ResultEntryFisikUmumSo_ResultEntryID = {$so_reid} AND So_ResultEntryFisikUmumFisikTemplateID = 27";
//$this->db_onedev->query($sql);
echo $sql."
";
echo "success : ".$row['T_OrderHeaderLabNumber']."
";
}
}
}
}
}
function add_new_reflek($mgm_mcu_number){
$sql = "SELECT *
FROM t_orderheader
JOIN mgm_mcu ON T_OrderHeaderMgm_McuID = Mgm_McuID AND Mgm_McuIsActive = 'Y'
AND Mgm_McuNumber = ? AND T_OrderHeaderIsActive = 'Y'
JOIN mgm_mcutemplate ON Mgm_McuTemplateMgm_McuID = Mgm_McuID AND Mgm_McuTemplateIsActive = 'Y'";
$rows = $this->db_onedev->query($sql, array($mgm_mcu_number))->result_array();
if(count($rows) > 0){
$sql = "SELECT * FROM fisik_template WHERE FisikTemplateIsActive = 'Y' AND FisikTemplateCode = 'FI18.2' LIMIT 1";
$fisik_template = $this->db_onedev->query($sql)->row_array();
//echo $fisik_template['FisikTemplateID']."
";
foreach($rows as $row){
$sql = "SELECT *
FROM so_resultentry
WHERE So_ResultEntryT_OrderHeaderID = ? AND So_ResultEntryIsActive = 'Y' AND So_ResultEntryNonlab_TemplateID = 27 LIMIT 1";
$so_resultentry = $this->db_onedev->query($sql, array($row['T_OrderHeaderID']))->result_array();
//echo $this->db_onedev->last_query();
if(count($so_resultentry) > 0){
$so_reid = $so_resultentry[0]['So_ResultEntryID'];
//echo $so_reid."
";
$sql = "SELECT COUNT(*) as exist_dt FROM so_resultentry_fisik_umum
WHERE So_ResultEntryFisikUmumFisikTemplateID IN ({$fisik_template['FisikTemplateID']},27) AND
So_ResultEntryFisikUmumSo_ResultEntryID = {$so_reid} AND
So_ResultEntryFisikUmumIsActive = 'Y'";
//echo $sql;
$so_resultentry_fisik_umum = $this->db_onedev->query($sql)->row_array();
//echo $this->db_onedev->last_query();
if($so_resultentry_fisik_umum['exist_dt'] == 0){
$sql = "INSERT INTO
so_resultentry_fisik_umum (
So_ResultEntryFisikUmumFisikTemplateID,
So_ResultEntryFisikUmumSo_ResultEntryID,
So_ResultEntryFisikUmumDetails,
So_ResultEntryFisikUmumIsActive,
So_ResultEntryFisikUmumCreated,
So_ResultEntryFisikUmumCreatedUserID)
VALUES (
{$fisik_template['FisikTemplateID']},
{$so_reid},
'{$fisik_template['FisikTemplateJSON']}', 'Y', NOW(), 666)";
//echo $sql."
";
$query = $this->db_onedev->query($sql);
if(!$query){
echo "error : ".$this->db_onedev->error();
return;
}
echo $row['T_OrderHeaderLabNumber']."
";
}
}
}
}
}
function check_undeleted_hba1c_total(){
$sql = "SELECT * FROM undeleted_hba1c WHERE `status` = 'Y'";
$query = $this->db_onedev->query($sql);
if(!$query){
echo "error : ".$this->db_onedev->error();
return;
}
$rows = $query->result_array();
foreach($rows as $row){
$sql = "SELECT SUM(T_OrderDetailTotal) as total_detail, T_OrderHeaderTotal as total_header
FROM `t_orderdetail`
JOIN t_orderheader ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID
WHERE `T_OrderDetailIsActive` = 'Y' AND T_OrderHeaderLabNumber = '{$row['nolab']}'";
$query = $this->db_onedev->query($sql);
if(!$query){
echo "error : ".$this->db_onedev->error();
return;
}
$rows = $query->result_array();
if($rows[0]['total_detail'] != $rows[0]['total_header']){
echo "nolab : ".$row['nolab']. " - total detail : ".$rows[0]['total_detail']. " - total header : ".$rows[0]['total_header']."
";
}
}
}
function fix_undeleted_hba1c_in_orderdetail(){
$this->db_onedev->trans_begin();
$sql = "SELECT * FROM undeleted_hba1c WHERE `status` = 'N' LIMIT 9";
//echo $sql."
";
//echo "start
";
$query = $this->db_onedev->query($sql);
if(!$query){
$this->db_onedev->trans_rollback();
echo "error : ".$this->db_onedev->error();
return;
}
$rows = $query->result_array();
foreach($rows as $row){
$sql = "SELECT *
FROM t_orderdetail
JOIN t_orderheader ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID AND
T_OrderHeaderIsActive = 'Y' AND
T_OrderHeaderLabNumber = '{$row['nolab']}'
WHERE
T_OrderDetailT_TestID IN (1012,1013,1014,3468) AND T_OrderDetailIsActive = 'N'";
//echo $sql."
";
//echo "nolab : ".$row['nolab']."
";
$query = $this->db_onedev->query($sql);
if(!$query){
$this->db_onedev->trans_rollback();
echo "error : ".$this->db_onedev->error();
return;
}
$orderdetail = $query->result_array();
if(count($orderdetail) > 0){
foreach($orderdetail as $detail){
$sql = "UPDATE t_orderdetail SET
T_OrderDetailIsActive = 'Y',
T_OrderDetailLastUpdated = NOW(),
T_OrderDetailLastUpdatedUserID = 666
WHERE T_OrderDetailID = {$detail['T_OrderDetailID']}";
$query = $this->db_onedev->query($sql);
if(!$query){
$this->db_onedev->trans_rollback();
echo "error : ".$this->db_onedev->error();
return;
}
//echo "update detail : ".$detail['T_OrderDetailID']."
";
//echo $sql."
";
}
}
$sql = "SELECT *
FROM t_orderdetail
JOIN t_orderheader ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID AND
T_OrderHeaderIsActive = 'Y' AND
T_OrderHeaderLabNumber = '{$row['nolab']}'
WHERE
T_OrderDetailT_TestID = 3509 AND T_OrderDetailIsActive = 'Y'";
$query = $this->db_onedev->query($sql);
if(!$query){
$this->db_onedev->trans_rollback();
echo "error : ".$this->db_onedev->error();
return;
}
$orderdetail_eag = $query->result_array();
//echo $sql."
";
if(count($orderdetail_eag) > 0){
foreach($orderdetail_eag as $detail_eag){
$sql = "UPDATE t_orderdetail SET
T_OrderDetailIsActive = 'N',
T_OrderDetailLastUpdated = NOW(),
T_OrderDetailLastUpdatedUserID = 666
WHERE T_OrderDetailID = {$detail_eag['T_OrderDetailID']}";
$query = $this->db_onedev->query($sql);
if(!$query){
$this->db_onedev->trans_rollback();
echo "error : ".$this->db_onedev->error();
return;
}
//echo "update eag : ".$detail_eag['T_OrderDetailID']."
";
//echo $sql."
";
}
}
$sql = "UPDATE undeleted_hba1c SET status = 'Y' WHERE nolab = '{$row['nolab']}'";
//echo $sql."
";
$query = $this->db_onedev->query($sql);
if(!$query){
$this->db_onedev->trans_rollback();
echo "error : ".$this->db_onedev->error();
return;
}
$this->db_onedev->trans_commit();
echo "success : ".$row['nolab']."
";
}
}
function update_sampling_status($labnumber){
$sql = "SELECT * FROM t_orderheader WHERE T_OrderHeaderLabNumber = ?";
$rows = $this->db_onedev->query($sql, array($labnumber))->row_array();
$order_id = $rows['T_OrderHeaderID'];
$sql = "UPDATE t_sampling_queue_last_status SET
T_SamplingQueueLastStatusT_SamplingQueueStatusID = 2,
T_SamplingQueueLastStatusLastUpdated = NOW(),
T_SamplingQueueLastStatusLastUpdatedUserID = 3461
WHERE T_SamplingQueueLastStatusT_OrderHeaderID = ? AND
T_SamplingQueueLastStatusT_SamplingQueueStatusID = 3";
$this->db_onedev->query($sql, array($order_id));
echo "Sampling status updated successfully.";
}
function hitungUmur($dob, $dateTo = null) {
// Validasi format tanggal lahir
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $dob)) {
return "Format tanggal lahir tidak valid. Gunakan format YYYY-MM-DD";
}
// Dapatkan tanggal lahir sebagai objek DateTime
$birthDateObj = new DateTime($dob);
// Dapatkan tanggal perhitungan
if ($dateTo === null) {
// Jika tidak ada tanggal perhitungan, gunakan tanggal hari ini
$toDateObj = new DateTime();
} else {
// Validasi format tanggal perhitungan
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $dateTo)) {
return "Format tanggal perhitungan tidak valid. Gunakan format YYYY-MM-DD";
}
// Parse tanggal perhitungan
$toDateObj = new DateTime($dateTo);
}
// Jika tanggal lahir lebih besar dari tanggal perhitungan, return error
if ($birthDateObj > $toDateObj) {
return "Tanggal lahir tidak boleh setelah tanggal perhitungan";
}
// Hitung perbedaan antara tanggal perhitungan dan tanggal lahir
$diff = $toDateObj->diff($birthDateObj);
// Format hasil
$tahun = $diff->y;
$bulan = $diff->m;
$hari = $diff->d;
// Buat hasil string
$hasil = '';
if ($tahun > 0) {
$hasil .= $tahun . ' tahun ';
}
if ($bulan > 0 || $tahun > 0) {
$hasil .= $bulan . ' bulan ';
}
$hasil .= $hari . ' hari';
return trim($hasil);
}
function recount_age($labnumber){
$sql = "SELECT * , DATE(T_OrderHeaderDate) as tanggal_lahir
FROM m_patient
JOIN t_orderheader ON M_PatientID = T_OrderHeaderM_PatientID AND T_OrderHeaderIsActive = 'Y' AND
T_OrderHeaderLabNumber = ?
WHERE M_PatientIsActive = 'Y'";
$rows = $this->db_onedev->query($sql, array($labnumber))->result_array();
if(count($rows) > 0){
$patient = $rows[0];
$age = $this->hitungUmur($patient['M_PatientDOB'], $patient['tanggal_lahir']);
$sql = "UPDATE t_orderheader SET T_OrderHeaderM_PatientAge = ? WHERE T_OrderHeaderID = ?";
$this->db_onedev->query($sql, array($age, $patient['T_OrderHeaderID']));
$data_log = [
'age_before' => $patient['T_OrderHeaderM_PatientAge'],
'age_after' => $age,
'labnumber' => $patient['T_OrderHeaderLabNumber'],
'patient_name' => $patient['M_PatientName'],
'patient_id' => $patient['M_PatientID'],
'order_id' => $patient['T_OrderHeaderID']
];
$sql = "INSERT INTO cpone_log.log_fo(
Log_FoT_OrderHeaderID,
Log_FoDate,
Log_FoCode,
Log_FoJson,
Log_FoUserID
)
VALUES (?, NOW(), 'RECOUNT_AGE', ?, ?)";
$this->db_onedev->query($sql, array($patient['T_OrderHeaderID'], json_encode($data_log), 666));
echo 'Tanggal Order : '.$patient['tanggal_lahir']."
";
echo 'Tanggal Lahir : '.$patient['M_PatientDOB']."
";
echo 'Hasil : '.$patient['M_PatientName']." - ".$age."
";
}
}
function retotal_orderbypacket($pakcetNumber){
$rst = [];
$sql = "SELECT T_OrderHeaderID, T_OrderHeaderLabNumber, SUM(T_OrderDetailTotal) as total , T_OrderHeaderTotal
FROM t_orderdetail
JOIN t_orderheader ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID AND
T_OrderHeaderIsActive = 'Y'
JOIN t_orderdetailorder ON T_OrderDetailOrderT_OrderHeaderID = T_OrderDetailT_OrderHeaderID AND
T_OrderDetailOrderIsActive = 'Y'
JOIN t_packet ON T_OrderDetailOrderT_PacketID = T_PacketID AND T_PacketIsActive = 'Y' AND T_PacketSasCode = ?
WHERE T_OrderDetailIsActive = 'Y'
GROUP BY T_OrderHeaderID
HAVING total <> T_OrderHeaderTotal";
$rows = $this->db_onedev->query($sql, array($pakcetNumber))->result_array();
if(count($rows) > 0){
foreach($rows as $row){
if($row['total'] != $row['T_OrderHeaderTotal']){
$sql = "UPDATE t_orderheader SET T_OrderHeaderLastUpdated = NOW(), T_OrderHeaderLastUpdatedUserID = 1617, T_OrderHeaderSubTotal = ?, T_OrderHeaderTotal = ? WHERE T_OrderHeaderID = ?";
$this->db_onedev->query($sql, array($row['total'], $row['total'], $row['T_OrderHeaderID']));
$rst[] = $row['T_OrderHeaderLabNumber'];
}
}
}
echo json_encode($rst);
}
function fix_hba1c_deleted(){
/*$sql = "SELECT *
FROM t_orderheader
JOIN t_orderdetailorder ON T_OrderDetailOrderT_OrderHeaderID = T_OrderHeaderID AND
T_OrderDetailOrderT_PacketID IN (490,491)
JOIN t_orderdetail ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID AND
T_OrderDetailIsActive = 'N' AND T_OrderDetailT_TestID = 1012
";
*/
$sql = "SELECT DISTINCT T_OrderHeaderID FROM `t_orderdetail`
JOIN t_orderheader ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID AND
T_OrderHeaderIsActive = 'Y'
JOIN t_orderdetailorder ON T_OrderDetailOrderT_OrderHeaderID = T_OrderDetailT_OrderHeaderID AND
T_OrderDetailOrderT_TestID = 1012 AND T_OrderDetailOrderIsActive = 'Y'
WHERE `T_OrderDetailIsActive` = 'N' AND T_OrderDetailT_TestID = 1012 AND
T_OrderDetailDeletedUserID = 313
LIMIT 50";
$rows = $this->db_onedev->query($sql)->result_array();
foreach($rows as $row){
$sql = "SELECT * FROM t_orderdetail WHERE T_OrderDetailT_OrderHeaderID = {$row['T_OrderHeaderID']} AND
T_OrderDetailT_TestID IN (1012,1013,1014,3468) AND T_OrderDetailIsActive = 'Y'";
$orderdetail_active = $this->db_onedev->query($sql)->result_array();
if(count($orderdetail_active) == 0){
$sql = "UPDATE t_orderdetail SET
T_OrderDetailLastUpdatedUserID = 2730,
T_OrderDetailLastUpdated = NOW(),
T_OrderDetailIsActive = 'Y'
WHERE
T_OrderDetailT_OrderHeaderID = {$row['T_OrderHeaderID']} AND
T_OrderDetailT_TestID IN (1012,1013,1014,3468) AND T_OrderDetailIsActive = 'N'";
$this->db_onedev->query($sql);
}
}
}
function change_corporate_order(){
$prm = $this->sys_input;
$labnumber = isset($prm['labnumber']) ? $prm['labnumber'] : '';
$corporate_code_before = isset($prm['corporate_code_before']) ? $prm['corporate_code_before'] : '';
$corporate_code_after = isset($prm['corporate_code_after']) ? $prm['corporate_code_after'] : '';
if($labnumber == '' || $corporate_code_before == '' || $corporate_code_after == ''){
echo 'Labnumber, Corporate Code Before dan Corporate Code After tidak boleh kosong';
exit;
}
$sql = "SELECT * FROM corporate WHERE CorporateCode = ? AND CorporateIsActive = 'Y'";
$corporate = $this->db_onedev->query($sql, array($corporate_code_after))->row_array();
if(count($corporate) == 0){
echo 'Corporate Code After tidak ditemukan';
exit;
}
$corporate_id_after = $corporate['CorporateID'];
$sql = "UPDATE t_orderheader SET T_OrderHeaderCorporateID = ? WHERE T_OrderHeaderLabNumber = ?";
$query = $this->db_onedev->query($sql, array($corporate_id_after, $labnumber));
if(!$query){
echo 'Error : '.$this->db_onedev->error();
exit;
}
$sql = "SELECT *
FROM t_orderheader
JOIN corporate ON T_OrderHeaderCorporateID = CorporateID AND CorporateIsActive = 'Y'
WHERE
T_OrderHeaderLabNumber = ? AND T_OrderHeaderIsActive = 'Y'";
$query = $this->db_onedev->query($sql, array($labnumber));
if(!$query){
echo 'Error : '.$this->db_onedev->error();
exit;
}
$order = $query->row_array();
echo 'Corporate Code Setelah Update : '.$order['CorporateName']." - ".$order['CorporateCode']."
";
echo 'Berhasil mengubah Corporate Code Order : '.$labnumber."
";
}
function change_template_nonlab($labnumber,$test_code){
$sql = "SELECT *
FROM t_orderdetail
JOIN t_test ON T_OrderDetailT_TestID = T_TestID
JOIN t_orderheader ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID
WHERE T_OrderHeaderLabNumber = ? AND T_TestSasCode = ? AND T_OrderDetailIsActive = 'Y'";
$order = $this->db_onedev->query($sql, array($labnumber, $test_code))->row_array();
$order_id = $order['T_OrderDetailT_OrderHeaderID'];
$orderdetail_id = $order['T_OrderDetailID'];
$test_id = $order['T_OrderDetailT_TestID'];
$nat_test_id = $order['T_TestNat_TestID'];
echo $order['T_TestName']."
";
$sql = "SELECT *
FROM nonlab_template_mapping
JOIN nonlab_template ON NonlabTemplateMappingNonlabTemplateID = NonlabTemplateID
WHERE NonlabTemplateMappingNat_TestID = ? AND NonlabTemplateMappingIsActive = 'Y'
ORDER BY NonlabTemplateMappingID DESC
LIMIT 1 ";
$query = $this->db_onedev->query($sql, array($nat_test_id));
if(!$query)
{
echo "Tidak ditemukan template nonlab ".$nat_test_id."
";
exit;
}
$nonlab_template_mapping = $query->row_array();
$nonlab_template_id = $nonlab_template_mapping['NonlabTemplateID'];
$nonlab_template_name = $nonlab_template_mapping['NonlabTemplateName'];
echo "Template Baru : ".$nonlab_template_name."
";
$sql = "SELECT * FROM so_resultentry
WHERE So_ResultEntryT_OrderDetailID = ? AND So_ResultEntryT_OrderHeaderID = ? AND
So_ResultEntryNonlab_TemplateID = ? AND So_ResultEntryIsActive = 'Y'";
$so_resultentry = $this->db_onedev->query($sql, array($orderdetail_id, $order_id, $nonlab_template_id))->result_array();
if(count($so_resultentry) == 0){
$sql = "SELECT * FROM so_resultentry
WHERE So_ResultEntryT_OrderDetailID = ? AND
So_ResultEntryT_OrderHeaderID = ? AND
So_ResultEntryIsActive = 'Y' LIMIT 1";
$query = $this->db_onedev->query($sql, array($orderdetail_id, $order_id));
$resultentry = $query->row_array();
$so_id = $resultentry['So_ResultEntryID'];
$sql = "UPDATE so_resultentry SET So_ResultEntryIsActive = 'N',
So_ResultEntryDeletedUserID = 1617,
So_ResultEntryDeleted = NOW()
WHERE So_ResultEntryID = ? AND So_ResultEntryIsActive = 'Y'";
$this->db_onedev->query($sql, array($so_id));
$sql = "UPDATE so_resultentry_detail SET
So_ResultEntryDetailIsActive = 'N',
So_ResultEntryDetailDeletedUserID = 1617,
So_ResultEntryDetailDeleted = NOW()
WHERE So_ResultEntryDetailSo_ResultEntryID = ? AND So_ResultEntryDetailIsActive = 'Y'";
$this->db_onedev->query($sql, array($so_id));
$sql = "INSERT INTO so_resultentry (
So_ResultEntryT_OrderDetailID,
So_ResultEntryT_OrderHeaderID,
So_ResultEntryNonlab_TemplateID,
So_ResultEntryNonlab_TemplateName,
So_ResultEntryIsActive,
So_ResultEntryCreated,
So_ResultEntryCreatedUserID
)
VALUES (?, ?, ?, ?, 'Y', NOW(), 1617)";
$query = $this->db_onedev->query($sql, array($orderdetail_id, $order_id, $nonlab_template_id, $nonlab_template_name));
if(!$query){
echo $this->db_onedev->last_query();
exit;
}
$last_id = $this->db_onedev->insert_id();
echo $nonlab_template_mapping['NonlabTemplateName']."
";
$sql = "SELECT * FROM nonlab_template_detail WHERE NonlabTemplateDetailNonlabTemplateID = ? AND NonlabTemplateDetailIsActive = 'Y'";
$nonlab_template_detail = $this->db_onedev->query($sql, array($nonlab_template_id))->result_array();
//print_r($nonlab_template_detail);
foreach($nonlab_template_detail as $detail){
$sql = "INSERT INTO so_resultentrydetail (
So_ResultEntryDetailSo_ResultEntryID,
So_ResultEntryDetailNonlab_TemplateDetailID,
So_ResultEntryDetailNonlab_TemplateDetailName,
So_ResultEntryDetailIsActive,
So_ResultEntryDetailCreated,
So_ResultEntryDetailCreatedUserID
)
VALUES (?, ?, ?, 'Y', NOW(), 1617)";
$query = $this->db_onedev->query($sql, array($last_id, $detail['NonlabTemplateDetailID'], $detail['NonlabTemplateDetailName']));
if(!$query){
echo $this->db_onedev->last_query();
exit;
}
//echo $query->last_query();
echo $detail['NonlabTemplateDetailName']."
";
}
}else{
echo "Template sudah sesuai dengan settingan";
}
}
function deactivate_test_in_packets() {
// Define the packet IDs and test ID to check
$packet_ids = [491, 490];
$test_id_to_deactivate = 3509;
// Find orders with the specified packets
$sql = "SELECT DISTINCT T_OrderDetailOrderT_OrderHeaderID
FROM t_orderdetailorder
WHERE T_OrderDetailOrderT_PacketID IN (" . implode(',', $packet_ids) . ")
AND T_OrderDetailOrderIsActive = 'Y'";
$affected_orders = $this->db_onedev->query($sql)->result_array();
if (empty($affected_orders)) {
echo "No orders found with the specified packets.";
return;
}
$updated_orders = 0;
$deactivated_tests = 0;
foreach ($affected_orders as $order) {
$order_header_id = $order['T_OrderDetailOrderT_OrderHeaderID'];
// Check if this order has the test we want to deactivate
$sql = "SELECT T_OrderDetailID
FROM t_orderdetail
WHERE T_OrderDetailT_OrderHeaderID = ?
AND T_OrderDetailT_TestID = ?
AND T_OrderDetailIsActive = 'Y'";
$tests_to_deactivate = $this->db_onedev->query($sql, array($order_header_id, $test_id_to_deactivate))->result_array();
if (!empty($tests_to_deactivate)) {
// Deactivate the tests
$test_ids = array_column($tests_to_deactivate, 'T_OrderDetailID');
$sql = "UPDATE t_orderdetail
SET T_OrderDetailIsActive = 'N',
T_OrderDetailDeleted = NOW(),
T_OrderDetailDeletedUserID = 3461
WHERE T_OrderDetailID IN (" . implode(',', $test_ids) . ")";
$this->db_onedev->query($sql);
$deactivated_tests += count($test_ids);
// Recalculate the order total
$sql = "SELECT SUM(T_OrderDetailTotal) as total
FROM t_orderdetail
WHERE T_OrderDetailT_OrderHeaderID = ?
AND T_OrderDetailIsActive = 'Y'";
$total_result = $this->db_onedev->query($sql, array($order_header_id))->row_array();
$new_total = $total_result['total'] ?? 0;
// Update the order header
$sql = "UPDATE t_orderheader
SET T_OrderHeaderSubTotal = ?,
T_OrderHeaderTotal = ?,
T_OrderHeaderLastUpdated = NOW(),
T_OrderHeaderLastUpdatedUserID = 3461
WHERE T_OrderHeaderID = ?";
$this->db_onedev->query($sql, array($new_total, $new_total, $order_header_id));
$sql = "INSERT INTO delete_eag(orderid) VALUES(?)";
$this->db_onedev->query($sql, array($order_header_id));
$updated_orders++;
}
}
echo "Process completed successfully.
";
echo "Orders updated: $updated_orders
";
echo "Tests deactivated: $deactivated_tests
";
}
function search_test_by_code($xcode, $mcuid)
{
$is_packet = 'N';
$px_type = 'PX';
$pxtype = '';
if (substr($xcode, 0, 2) == 'PN' || substr($xcode, 0, 2) == 'PR') {
$is_packet = 'Y';
$px_type = substr($xcode, 0, 2);
$sql = "SELECT T_PacketID as T_TestID FROM t_packet WHERE T_PacketSasCode = '{$xcode}' AND T_PacketIsActive = 'Y'";
$pxtype = "px_type = '{$px_type}' AND";
} else {
$sql = "SELECT T_TestID FROM t_test WHERE T_TestSasCode = '{$xcode}' AND T_TestIsActive = 'Y'";
}
//echo $sql;
$xrow_test = $this->db_smartone->query($sql)->row_array();
$sql = "SELECT * FROM ";
$sql = "select *
from
ss_price_mou
where
T_PriceT_PriceHeaderID = {$mcuid} AND
T_TestID = {$xrow_test['T_TestID']} AND
$pxtype
is_packet = '{$is_packet}' AND
T_PriceIsCito <> 'Y';";
//echo $sql;
$query = $this->db_smartone->query($sql);
if ($query) {
$row = $query->row_array();
$id_to_remove = [];
// var_dump($rows);
$row['requirement'] = [];
if ($row['px_type'] == "PX") {
$x = $this->db_smartone->query("SELECT fn_fo_requirement_get('{$row['T_TestID']}') x")
->row();
if ($x->x != null)
$row['requirement'] = json_decode($x->x);
}
//print_r($row);
// Janji Hasil per PX
//$x = $this->db_smartone->query("SELECT fn_fo_find_promise_by_one_px('{$row['T_TestID']}') x")->row();
if ($row['is_packet'] == 'N') {
$tests = $row['T_PriceT_TestID'];
$panels = '';
} else {
$tests = '';
$panels = $row['T_PriceT_TestID'];
}
$sql_param = array($tests, $panels);
// $sql = "select fn_fo_find_promise_by_px(?, ?) as x";
// $x = $this->db_smartone->query($sql, $sql_param)->row();
// if ($x->x != null)
// $row['promise'] = $x->x;
$row['nat_test'] = json_decode($row['nat_test']);
$row['child_test'] = json_decode($row['child_test']);
//print_r($row['child_test'] );
// IF PROFILE
if ($row['px_type'] == "PN" || $row['px_type'] == "PR" || $row['px_type'] == "PXR") {
foreach ($row['child_test'] as $l => $w) {
$row['child_test'][$l]->requirement = [];
$row['child_test'][$l]->nat_test = json_decode($w->nat_test);
$row['child_test'][$l]->promise = null;
//echo "SELECT fn_fo_requirement_get('{$w->T_TestID}')";
$x = $this->db_smartone->query("SELECT fn_fo_requirement_get('{$w->T_TestID}') x")->row();
if ($x->x != null)
$row['child_test'][$l]->requirement = json_decode($x->x);
// Janji Hasil per PX
//$x = $this->db_smartone->query("SELECT fn_fo_find_promise_by_one_px('{$w->T_TestID}') x")
// ->row();
//if ($x->x != null)
$row['child_test'][$l]->promise = null;
}
}
echo json_encode($row);
}
}
function inject_rectal_swab($labnumber){
$sql = "SELECT * FROM t_orderheader WHERE T_OrderHeaderLabNumber = ?";
$order = $this->db_onedev->query($sql, array($labnumber))->row_array();
$order_id = $order['T_OrderHeaderID'];
$sql = "UPDATE t_orderdetail SET T_OrderDetailResult = 'Rectal swab'
WHERE T_OrderDetailT_TestID = 3470 AND T_OrderDetailT_OrderHeaderID = ?
AND T_OrderDetailIsActive = 'Y'";
$this->db_onedev->query($sql, array($order_id));
echo "Update Bahan success";
$sql = "UPDATE t_orderdetail SET T_OrderDetailResult = '1. E. coli pathogen : Negatif
2. Salmonella sp : Negatif
3. Shigella sp : Negatif
4. Vibrio cholerae : Negatif'
WHERE T_OrderDetailT_TestID = 3474 AND T_OrderDetailT_OrderHeaderID = ?
AND T_OrderDetailIsActive = 'Y'";
$this->db_onedev->query($sql, array($order_id));
echo "Update Hasil Biakan success";
$sql = "UPDATE t_orderdetail SET T_OrderDetailResult = 'dr. D. Hangga Kusuma, Sp. MK'
WHERE T_OrderDetailT_TestID = 3478 AND T_OrderDetailT_OrderHeaderID = ?
AND T_OrderDetailIsActive = 'Y'";
$this->db_onedev->query($sql, array($order_id));
}
function check_missing_order_location($labnumber, $test_code = null) {
// Get order header details
$sql = "SELECT T_OrderHeaderID
FROM t_orderheader
WHERE T_OrderHeaderLabNumber = ?
AND T_OrderHeaderIsActive = 'Y'";
$order = $this->db_onedev->query($sql, array($labnumber))->row_array();
if (!$order) {
echo "Error: Order tidak ditemukan untuk nomor lab {$labnumber}\n";
return;
}
// Get tests that should have locations
$sql = "SELECT DISTINCT
t.T_TestID,
t.T_TestName,
t.T_TestSasCode,
st.T_SampleTypeID,
st.T_SampleTypeName,
b.T_BahanID,
b.T_BahanName,
ss.T_SampleStationID,
ss.T_SampleStationName,
ml.M_LocationID,
ml.M_LocationName,
CASE
WHEN tol.T_OrderLocationID IS NULL THEN 'Missing'
ELSE 'Present'
END as location_status";
// Add additional fields for specific test analysis
if ($test_code) {
$sql .= ", od.T_OrderDetailID,
od.T_OrderDetailIsActive,
t.T_TestIsResult,
t.T_TestIsActive,
ss.T_SampleStationIsActive,
ml.M_LocationIsActive";
}
$sql .= " FROM t_orderdetail od
JOIN t_test t ON od.T_OrderDetailT_TestID = t.T_TestID
JOIN t_sampletype st ON t.T_TestT_SampleTypeID = st.T_SampleTypeID
JOIN t_bahan b ON st.T_SampleTypeT_BahanID = b.T_BahanID
JOIN t_samplestation ss ON b.T_BahanT_SampleStationID = ss.T_SampleStationID
JOIN m_location ml ON ml.M_LocationT_SampleStationID = ss.T_SampleStationID
LEFT JOIN t_order_location tol ON tol.T_OrderLocationT_OrderHeaderID = od.T_OrderDetailT_OrderHeaderID
AND tol.T_OrderLocationT_SampleStationID = ss.T_SampleStationID
AND tol.T_OrderLocationIsActive = 'Y'
WHERE od.T_OrderDetailT_OrderHeaderID = ?
AND od.T_OrderDetailIsActive = 'Y'
AND t.T_TestIsResult = 'Y'
AND t.T_TestIsActive = 'Y'
AND ss.T_SampleStationIsActive = 'Y'
AND ml.M_LocationIsActive = 'Y'";
if ($test_code) {
$sql .= " AND t.T_TestSasCode = ?";
$results = $this->db_onedev->query($sql, array($order['T_OrderHeaderID'], $test_code))->result_array();
} else {
$results = $this->db_onedev->query($sql, array($order['T_OrderHeaderID']))->result_array();
}
if (empty($results)) {
// Hanya ambil pengaturan untuk test_code yang spesifik
$current_settings = "SELECT
oh.T_OrderHeaderLabNumber,
od.T_OrderDetailIsActive as detail_active,
t.T_TestName,
t.T_TestSasCode,
t.T_TestIsResult as test_result,
t.T_TestIsActive as test_active,
st.T_SampleTypeName,
ss.T_SampleStationName,
ss.T_SampleStationIsActive as station_active,
ml.M_LocationName,
ml.M_LocationIsActive as location_active
FROM t_orderheader oh
LEFT JOIN t_orderdetail od ON oh.T_OrderHeaderID = od.T_OrderDetailT_OrderHeaderID
LEFT JOIN t_test t ON od.T_OrderDetailT_TestID = t.T_TestID
LEFT JOIN t_sampletype st ON t.T_TestT_SampleTypeID = st.T_SampleTypeID
LEFT JOIN t_samplestation ss ON st.T_SampleTypeT_BahanID = ss.T_SampleStationID
LEFT JOIN m_location ml ON ml.M_LocationT_SampleStationID = ss.T_SampleStationID
WHERE oh.T_OrderHeaderLabNumber = ?
AND t.T_TestSasCode = ?";
$settings = $this->db_onedev->query($current_settings, array($labnumber, $test_code))->result_array();
echo "";
echo "
⚠️ Tidak ditemukan tes yang memerlukan lokasi untuk:
";
echo "
Nomor Lab: {$labnumber}
Kode Tes: {$test_code}
";
echo "
";
echo "
📊 Status Konfigurasi Tes:
";
echo "
";
echo "";
echo "| Komponen | ";
echo "Status | ";
echo "Keterangan | ";
echo "
";
if (!empty($settings)) {
foreach ($settings as $setting) {
// Test
echo "";
echo "| Tes {$setting['T_TestSasCode']} | ";
echo "";
echo "Nama: {$setting['T_TestName']}
";
echo "IsResult: " . ($setting['test_result'] == 'Y' ? '✅' : '❌') . "
";
echo "IsActive: " . ($setting['test_active'] == 'Y' ? '✅' : '❌');
echo " | ";
echo "";
if ($setting['test_result'] != 'Y') {
echo "Pemeriksaan ini tidak ada hasil (T_TestIsResult = N)
";
echo "";
echo "- Pemeriksaan ini tidak memerlukan pengaturan stasiun sampel dan lokasi
";
echo "- Jika pemeriksaan ini seharusnya memiliki hasil, silakan ke masterdata test untuk mengubah pengaturan T_TestIsResult menjadi Y (ada hasil dicentang)
";
echo "- Hanya pemeriksaan dengan T_TestIsResult = Y (punya hasil) yang memerlukan pengaturan stasiun sampel dan lokasi
";
echo " ";
} elseif ($setting['test_active'] != 'Y') {
echo "Pemeriksaan tidak aktif, perlu mengaktifkan status IsActive";
} else {
echo "Konfigurasi tes sudah benar";
}
echo " | ";
echo "
";
// Order Detail
echo "";
echo "| Order Detail | ";
echo "" .
($setting['detail_active'] == 'Y' ? '✅ Aktif' : '❌ Tidak Aktif') . " | ";
echo "" .
($setting['detail_active'] == 'Y' ? 'Order detail sudah benar' : 'Order detail perlu diaktifkan') . " | ";
echo "
";
}
} else {
echo "| ";
echo "❌ Tes dengan kode {$test_code} tidak ditemukan dalam sistem";
echo " |
";
}
echo "
";
echo "
";
if (!empty($settings) && $settings[0]['test_result'] == 'N') {
echo "
";
echo "
ℹ️ Informasi Penting
";
echo "
Pemeriksaan {$settings[0]['T_TestName']} ({$settings[0]['T_TestSasCode']}) adalah jenis pemeriksaan tanpa hasil.
";
echo "
Tindakan yang bisa dilakukan:
";
echo "
";
echo "- Jika ini sudah benar (memang tidak perlu hasil), abaikan pesan ini
";
echo "- Jika seharusnya ada hasil, hubungi admin untuk:
";
echo " ";
echo " - Mengubah pengaturan T_TestIsResult menjadi Y (ada hasil dicentang)
";
echo " - Mengatur stasiun sampel yang sesuai (setting sample station sesuai dengan bahan dan jenis sampel)
";
echo " - Mengatur lokasi yang diperlukan (setting location sesuai dengan sample stasiun )
";
echo "
";
echo "
";
echo "
";
}
echo "
Analisis untuk Nomor Lab: {$labnumber}
";
if ($test_code) {
echo "";
echo "
🔍 Analisis Detail untuk Kode Tes: {$test_code}
";
if (empty($results)) {
echo "
Tidak ada data ditemukan untuk kode tes {$test_code}. Kemungkinan penyebab:
";
echo "
";
echo "- Kode tes tidak ada dalam sistem
";
echo "- Tes tidak aktif (T_TestIsActive = 'N')
";
echo "- Tes tidak dikonfigurasi untuk hasil (T_TestIsResult = 'N')
";
echo "- Detail order tidak aktif (T_OrderDetailIsActive = 'N')
";
echo "
";
} else {
foreach ($results as $row) {
echo "
Nama Tes: {$row['T_TestName']}
";
echo "
Stasiun Sampel: {$row['T_SampleStationName']}
";
echo "
Status Lokasi: " . ($row['location_status'] == 'Present' ? 'Tersedia' : 'Tidak Tersedia') . "
";
if ($row['location_status'] == 'Missing') {
echo "
Pemeriksaan Status:
";
echo "
";
echo "- Detail Order Aktif: " . ($row['T_OrderDetailIsActive'] == 'Y' ? '✓' : '✗') . "
";
echo "- Tes Memiliki Hasil: " . ($row['T_TestIsResult'] == 'Y' ? '✓' : '✗') . "
";
echo "- Tes Aktif: " . ($row['T_TestIsActive'] == 'Y' ? '✓' : '✗') . "
";
echo "- Stasiun Sampel Aktif: " . ($row['T_SampleStationIsActive'] == 'Y' ? '✓' : '✗') . "
";
echo "- Lokasi Aktif: " . ($row['M_LocationIsActive'] == 'Y' ? '✓' : '✗') . "
";
echo "
";
}
}
}
echo "
";
}
foreach ($stations as $station) {
echo "";
echo "| 📍 SAMPLE STATION: {$station['station_name']} |
|---|
";
// Locations row
echo "| 🏢 Locations: ";
$location_texts = [];
foreach ($station['locations'] as $location) {
$status_class = ($location['status'] == 'Present') ? 'status-present' : 'status-missing';
$status_symbol = ($location['status'] == 'Present') ? '✓' : '✗';
$location_texts[] = "[{$status_symbol}] {$location['name']}";
}
echo implode(" | ", $location_texts) . " |
";
// Tests header
echo "
| Test Name |
Code |
Type |
";
// Tests rows
foreach ($station['tests'] as $test) {
echo "
| {$test['name']} |
{$test['code']} |
{$test['sample_type']} |
";
}
echo "
";
}
// Summary section
$total_stations = count($stations);
$total_locations = 0;
$missing_count = 0;
foreach ($stations as $station) {
$total_locations += count($station['locations']);
foreach ($station['locations'] as $location) {
if ($location['status'] == 'Missing') {
$missing_count++;
}
}
}
echo "";
echo "
📊 RINGKASAN
";
echo "
Total Stasiun Sampel: {$total_stations}
";
echo "Total Lokasi yang Diperlukan: {$total_locations}
";
if ($missing_count > 0) {
echo "Lokasi yang Belum Tersedia: {$missing_count}
";
echo "⚠️ Tindakan Diperlukan: Mohon tetapkan lokasi yang belum tersedia";
} else {
echo "✓ Semua lokasi telah ditetapkan dengan benar";
}
echo "
';
exit;
}
foreach ($duplicates as $duplicate) {
// Get all active records for this order/test combination
$sql = "SELECT T_OrderDetailID, T_OrderDetailResult
FROM t_orderdetail
JOIN t_test ON T_OrderDetailT_TestID = T_TestID AND T_TestIsActive = 'Y'
WHERE T_OrderDetailT_OrderHeaderID = ?
AND T_TestNat_TestID = ?
AND T_OrderDetailIsActive = 'Y'
ORDER BY T_OrderDetailID";
$records = $this->db_onedev->query($sql, array(
$duplicate['T_OrderDetailT_OrderHeaderID'],
$duplicate['T_TestNat_TestID']
))->result_array();
// Check if any records have results
$has_results = false;
$all_null = true;
foreach ($records as $record) {
if ($record['T_OrderDetailResult'] !== null) {
$has_results = true;
$all_null = false;
break;
}
}
// Keep track of which record to keep
$keep_id = $records[0]['T_OrderDetailID']; // Default to first record
if ($has_results) {
// If some have results, deactivate only those without results
$ids_to_deactivate = array();
foreach ($records as $record) {
if ($record['T_OrderDetailResult'] === null) {
$ids_to_deactivate[] = $record['T_OrderDetailID'];
}
}
} else {
// If all null or all have results, keep first record and deactivate others
$ids_to_deactivate = array();
for ($i = 1; $i < count($records); $i++) {
$ids_to_deactivate[] = $records[$i]['T_OrderDetailID'];
}
}
if (!empty($ids_to_deactivate)) {
// Update records to inactive
$sql = "UPDATE t_orderdetail
SET T_OrderDetailIsActive = 'N',
T_OrderDetailDeleted = NOW(),
T_OrderDetailDeletedUserID = 1
WHERE T_OrderDetailID IN (" . implode(',', $ids_to_deactivate) . ")";
$this->db_onedev->query($sql);
$sql = "UPDATE so_resultentry SET So_ResultEntryIsActive = 'N', So_ResultEntryDeleted = NOW(), So_ResultEntryDeletedUserID = 1
WHERE So_ResultEntryT_OrderDetailID IN (" . implode(',', $ids_to_deactivate) . ")";
$this->db_onedev->query($sql);
$updated_records[] = array(
'order_id' => $duplicate['T_OrderDetailT_OrderHeaderID'],
'test_id' => $duplicate['T_OrderDetailT_TestID'],
'kept_id' => $keep_id,
'deactivated_ids' => $ids_to_deactivate
);
}
}
$sql = "SELECT SUM(T_OrderDetailTotal) FROM t_orderdetail WHERE T_OrderDetailT_OrderHeaderID = ? AND T_OrderDetailIsActive = 'Y'";
$dt_order_header = $this->db_onedev->query($sql,array($duplicates[0]['T_OrderDetailT_OrderHeaderID']))->row_array();
$total = $dt_order_header['SUM(T_OrderDetailTotal)'];
$sql = "UPDATE t_orderheader SET T_OrderHeaderSubTotal = ?, T_OrderHeaderTotal = ? WHERE T_OrderHeaderID = ?";
$this->db_onedev->query($sql,array($total,$total,$duplicates[0]['T_OrderDetailT_OrderHeaderID']));
//echo $this->db_onedev->last_query();
$sql = "SELECT So_ResultEntryT_OrderHeaderID, So_ResultEntryT_OrderDetailID, COUNT(*) as count
FROM so_resultentry
JOIN t_orderheader ON So_ResultEntryT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y' AND
T_OrderHeaderLabNumber = ?
WHERE So_ResultEntryIsActive = 'Y'
GROUP BY So_ResultEntryT_OrderHeaderID, So_ResultEntryT_OrderDetailID
HAVING count > 1";
$dt_so_resultentry = $this->db_onedev->query($sql,array($labnumber))->result_array();
if(count($dt_so_resultentry) > 0){
foreach($dt_so_resultentry as $duplicate) {
// Get all duplicate entries ordered by status and ID
$sql = "SELECT So_ResultEntryID, So_ResultEntryStatus
FROM so_resultentry
WHERE So_ResultEntryT_OrderHeaderID = ?
AND So_ResultEntryT_OrderDetailID = ?
AND So_ResultEntryIsActive = 'Y'
ORDER BY
CASE WHEN So_ResultEntryStatus = 'VAL1' THEN 0 ELSE 1 END,
So_ResultEntryID";
$entries = $this->db_onedev->query($sql, array(
$duplicate['So_ResultEntryT_OrderHeaderID'],
$duplicate['So_ResultEntryT_OrderDetailID']
))->result_array();
// Keep first entry, deactivate others
for($i = 1; $i < count($entries); $i++) {
$sql = "UPDATE so_resultentry
SET So_ResultEntryIsActive = 'N',
So_ResultEntryDeleted = NOW(),
So_ResultEntryDeletedUserID = 1
WHERE So_ResultEntryID = ?";
$this->db_onedev->query($sql, array($entries[$i]['So_ResultEntryID']));
}
}
}
}
echo 'OK '.$labnumber.'
';
}
private function fix_duplicate_orders($table, $test_code, $limit = null) {
// Get records that need fixing
$sql = "SELECT t.NoLab, oh.T_OrderHeaderID
FROM {$table} t
JOIN t_orderheader oh ON oh.T_OrderHeaderLabNumber = t.NoLab
WHERE t.Status = 'N'";
if ($limit) {
$sql .= " LIMIT {$limit}";
}
$records = $this->db_onedev->query($sql)->result_array();
foreach ($records as $record) {
// Get duplicate order details
$sql = "SELECT T_OrderDetailID
FROM t_orderdetail
WHERE T_OrderDetailT_OrderHeaderID = ?
AND T_OrderDetailT_TestSasCode = ?
AND T_OrderDetailIsActive = 'Y'";
$dt_order_detail = $this->db_onedev->query($sql, array($record['T_OrderHeaderID'], $test_code))->result_array();
if (count($dt_order_detail) > 1) {
$this->handle_duplicate_orders($dt_order_detail);
}
// Update status in temp fix table
$sql = "UPDATE {$table} SET Status = 'Y' WHERE NoLab = ?";
$this->db_onedev->query($sql, array($record['NoLab']));
echo "Process completed ".$record['NoLab'].'
';
}
}
private function handle_duplicate_orders($dt_order_detail) {
$order_detail_ids = array_column($dt_order_detail, 'T_OrderDetailID');
// Check so_resultentry for these order details
$sql = "SELECT So_ResultEntryID, So_ResultEntryT_OrderDetailID, So_ResultEntryStatus
FROM so_resultentry
WHERE So_ResultEntryT_OrderDetailID IN (" . implode(',', $order_detail_ids) . ")
AND So_ResultEntryIsActive = 'Y'";
$dt_result_entry = $this->db_onedev->query($sql)->result_array();
if (count($dt_result_entry) > 0) {
// Determine which order detail to keep
$keep_order_detail_id = $this->get_order_detail_to_keep($dt_result_entry);
// Deactivate other entries
$this->deactivate_other_entries($order_detail_ids, $keep_order_detail_id);
}
}
private function get_order_detail_to_keep($dt_result_entry) {
// Check if any entry has VAL1 status
foreach ($dt_result_entry as $result) {
if ($result['So_ResultEntryStatus'] === 'VAL1') {
return $result['So_ResultEntryT_OrderDetailID'];
}
}
// If no VAL1 status found, keep the first one
return $dt_result_entry[0]['So_ResultEntryT_OrderDetailID'];
}
private function deactivate_other_entries($order_detail_ids, $keep_order_detail_id) {
// Deactivate other result entries
$sql = "UPDATE so_resultentry
SET So_ResultEntryIsActive = 'N'
WHERE So_ResultEntryT_OrderDetailID IN (" . implode(',', $order_detail_ids) . ")
AND So_ResultEntryT_OrderDetailID != ?";
$this->db_onedev->query($sql, array($keep_order_detail_id));
// Deactivate other order details
$sql = "UPDATE t_orderdetail
SET T_OrderDetailIsActive = 'N'
WHERE T_OrderDetailID IN (" . implode(',', $order_detail_ids) . ")
AND T_OrderDetailID != ?";
$this->db_onedev->query($sql, array($keep_order_detail_id));
}
function fix_thorax_duplicate() {
$this->fix_duplicate_orders('temp_fix_thorax', '3015010101',100);
}
function fix_ecg_duplicate() {
$this->fix_duplicate_orders('temp_fix_ecg', '2012010101', 100);
}
function replace_proyek($labnumber,$mcu_number){
$sql = "SELECT * FROM t_orderheader WHERE T_OrderHeaderIsActive = 'Y' AND T_OrderHeaderLabNumber = ?";
$dt_order = $this->db_onedev->query($sql,array($labnumber))->result_array();
if(count($dt_order) > 0){
$sql = "SELECT * FROM mgm_mcu WHERE Mgm_McuNumber = ? AND Mgm_McuIsActive = 'Y'";
$dt_mcu = $this->db_onedev->query($sql,array($mcu_number))->result_array();
if(count($dt_mcu) > 0){
$sql = "UPDATE t_orderheader SET T_OrderHeaderMgm_McuID = ? WHERE T_OrderHeaderID = ?";
$this->db_onedev->query($sql,array($dt_mcu[0]['Mgm_McuID'],$dt_order[0]['T_OrderHeaderID']));
echo $dt_order[0]['T_OrderHeaderID'].'|'.$dt_mcu[0]['Mgm_McuID'].'
';
}
}
;
}
function delete_test_packet($code_packet,$delete_sascode){
$orders = [];
$userid = 1417;
$sql = "SELECT T_PacketDetailID, T_PacketID, T_TestID, T_TestSasCode, T_TestName, T_PacketSasCode, T_PacketName, T_PacketT_PriceHeaderID
FROM t_packetdetail
JOIN t_packet ON T_PacketDetailT_PacketID = T_PacketID AND
T_PacketSasCode = ? AND T_PacketIsActive = 'Y'
JOIN t_test ON T_TestID = T_PacketDetailT_TestID AND T_TestIsActive = 'Y' AND T_TestSasCode = ?
";
$dt_packet = $this->db_onedev->query($sql,array($code_packet,$delete_sascode))->result_array();
//print_r($dt_packet);
//exit;
if(count($dt_packet) > 0){
$packet_id = $dt_packet[0]['T_PacketID'];
$test_id = $dt_packet[0]['T_TestID'];
$packet_detail_id = $dt_packet[0]['T_PacketDetailID'];
//$sql = "UPDATE t_packetdetail SET T_PacketDetailIsActive = 'N' WHERE T_PacketDetailID = ?";
//$this->db_onedev->query($sql,array($packet_id,$test_id));
/*$sql = "INSERT INTO cpone_log.log_tools(
Log_ToolsType,
Log_ToolsRefID,
Log_ToolsJSON,
Log_ToolsCreated,
Log_ToolsUserID
) VALUES (
?,
?,
?,
NOW(),
?
)";
$this->db_onedev->query($sql,array('DTP',$packet_detail_id,json_encode($dt_packet[0]),$userid));
$this->create_ss_price($userid,$dt_packet[0]['T_PacketT_PriceHeaderID']);*/
$sql = "SELECT *
FROM t_orderdetailorder
JOIN t_orderdetail ON T_OrderDetailOrderT_OrderHeaderID = T_OrderDetailT_OrderHeaderID AND
T_OrderDetailT_TestID = ? AND T_OrderDetailIsActive = 'Y'
WHERE
T_OrderDetailOrderT_PacketID = ? AND T_OrderDetailOrderIsActive = 'Y'";
$dt_order_detail_order = $this->db_onedev->query($sql,array($test_id,$packet_id))->result_array();
if(count($dt_order_detail_order) > 0){
foreach ($dt_order_detail_order as $k_order_detail_order => $v_order_detail_order) {
$sql = "SELECT * FROM t_orderdetail WHERE T_OrderDetailT_OrderDetailOrderID = ? AND T_OrderDetailT_TestID = ? AND T_OrderDetailIsActive = 'Y'";
$dt_order_detail = $this->db_onedev->query($sql,array($v_order_detail_order['T_OrderDetailOrderID'],$test_id))->result_array();
if(count($dt_order_detail) > 0){
$sql = "UPDATE t_orderdetail SET T_OrderDetailIsActive = 'N' WHERE T_OrderDetailIsActive = 'Y' AND T_OrderDetailID = ?";
$this->db_onedev->query($sql,array($dt_order_detail[0]['T_OrderDetailID']));
}
/*$sql = "SELECT SUM(T_OrderDetailTotal) as total FROM t_orderdetail WHERE T_OrderDetailT_OrderHeaderID = ? AND T_OrderDetailIsActive = 'Y'";
$dt_total = $this->db_onedev->query($sql,array($v_order_detail_order['T_OrderDetailOrderT_OrderHeaderID']))->row_array();
$total = $dt_total['total'];
$sql = "UPDATE t_orderheader SET T_OrderHeaderTotal = ? WHERE T_OrderHeaderID = ?";
$this->db_onedev->query($sql,array($total,$v_order_detail_order['T_OrderDetailOrderT_OrderHeaderID']));
$sql = "SELECT * FROM t_orderheader WHERE T_OrderHeaderID = ?";
$dt_order_header = $this->db_onedev->query($sql,array($v_order_detail_order['T_OrderDetailOrderT_OrderHeaderID']))->result_array();
*/
//$orders[] = $dt_order_header[0]['T_OrderHeaderLabNumber'];
}
}
$result = array('status' => 'OK', 'message' => 'Data berhasil dihapus','datas'=> $dt_packet,'orders_updated'=>$orders);
}else{
$result = array('status' => 'ERR', 'message' => 'Data tidak ditemukan, periksa kode paket dan kode pemeriksaan (gunakan kode struktur)');
}
echo json_encode($result);
}
function replace_template_fisik_by_proyek($mgmnumber){
$sql = "SELECT T_OrderHeaderLabNumber
FROM t_orderheader
JOIN mgm_mcu ON T_OrderHeaderMgm_McuID = Mgm_McuID AND
Mgm_McuNumber = ?
WHERE
T_OrderHeaderIsActive = 'Y'";
$dt_order_header = $this->db_onedev->query($sql,array($mgmnumber))->result_array();
if(count($dt_order_header) > 0){
foreach ($dt_order_header as $k_order_header => $v_order_header) {
$this->replace_template_fisik($v_order_header['T_OrderHeaderLabNumber']);
}
}
}
function replace_template_fisik($labnumber){
$template_active = [];
$template_new = [];
$template_delete = [];
$this->db_onedev->trans_begin();
$sql = "SELECT * FROM so_resultentry WHERE So_ResultEntryT_OrderHeaderID = (
SELECT T_OrderHeaderID FROM t_orderheader WHERE T_OrderHeaderLabNumber = '{$labnumber}') AND
So_ResultEntryIsActive = 'Y' AND So_ResultEntryNonlab_TemplateID = 27";
$dt_so_resultentry = $this->db_onedev->query($sql)->result_array();
if(count($dt_so_resultentry) > 0){
$sql = "UPDATE so_resultentry_fisik_umum SET
So_ResultEntryFisikUmumIsActive = 'R'
WHERE So_ResultEntryFisikUmumSo_ResultEntryID = {$dt_so_resultentry[0]['So_ResultEntryID']} AND
So_ResultEntryFisikUmumIsActive = 'Y'";
$query = $this->db_onedev->query($sql);
if(!$query){
echo $sql;
$this->db_onedev->trans_rollback();
echo "error : update so_resultentry_fisik_umum";
return;
}
$sql = "SELECT *
FROM mgm_mcu
JOIN t_orderheader ON T_OrderHeaderMgm_McuID = Mgm_McuID AND T_OrderHeaderID = ?
JOIN mgm_mcutemplate ON Mgm_McuID = Mgm_McuTemplateMgm_McuID AND Mgm_McuTemplateIsActive = 'Y' ";
//echo $sql;
$dt_mgm_mcu = $this->db_onedev->query($sql,array($dt_so_resultentry[0]['So_ResultEntryT_OrderHeaderID']))->result_array();
if(count($dt_mgm_mcu) > 0){
$sql = "SELECT *
FROM fisik_template_mapping_detail
JOIN fisik_template ON FisikTemplateMappingDetailFisikTemplateID = FisikTemplateID AND FisikTemplateIsActive = 'Y'
WHERE
FisikTemplateMappingDetailFisikTemplateMappingID = {$dt_mgm_mcu[0]['Mgm_McuTemplateFisikTemplateMappingID']} AND
FisikTemplateMappingDetailIsActive = 'Y'";
// echo $sql;
//echo "
";
$dt_fisik_template_mapping_detail = $this->db_onedev->query($sql)->result_array();
if(count($dt_fisik_template_mapping_detail) > 0){
foreach ($dt_fisik_template_mapping_detail as $k_fisik_template_mapping_detail => $v_fisik_template_mapping_detail) {
$template_new[] = $v_fisik_template_mapping_detail['FisikTemplateID'];
$template_active[] = $v_fisik_template_mapping_detail['FisikTemplateID'];
$sql = "SELECT So_ResultEntryFisikUmumID as id
FROM so_resultentry_fisik_umum
WHERE So_ResultEntryFisikUmumSo_ResultEntryID = {$dt_so_resultentry[0]['So_ResultEntryID']} AND
So_ResultEntryFisikUmumFisikTemplateID = {$v_fisik_template_mapping_detail['FisikTemplateID']} AND
So_ResultEntryFisikUmumIsActive = 'R'
ORDER BY So_ResultEntryFisikUmumID DESC
LIMIT 1";
$dt_so_resultentry_fisik_umum = $this->db_onedev->query($sql)->result_array();
if(count($dt_so_resultentry_fisik_umum) > 0){
$sql = "UPDATE so_resultentry_fisik_umum SET
So_ResultEntryFisikUmumIsActive = 'Y'
WHERE So_ResultEntryFisikUmumID = {$dt_so_resultentry_fisik_umum[0]['id']}";
//echo $sql;
//echo "
";
$query = $this->db_onedev->query($sql);
if(!$query){
$this->db_onedev->trans_rollback();
echo "error : update so_resultentry_fisik_umum";
return;
}
}
else{
$sql = "INSERT INTO so_resultentry_fisik_umum (
So_ResultEntryFisikUmumSo_ResultEntryID,
So_ResultEntryFisikUmumFisikTemplateID,
So_ResultEntryFisikUmumDetails,
So_ResultEntryFisikUmumCreated,
So_ResultEntryFisikUmumCreatedUserID
) VALUES (
{$dt_so_resultentry[0]['So_ResultEntryID']},
{$v_fisik_template_mapping_detail['FisikTemplateMappingDetailFisikTemplateID']},
'{$v_fisik_template_mapping_detail['FisikTemplateJSON']}',
NOW(),
1511
)";
// echo $sql;
//echo "
";
$query = $this->db_onedev->query($sql);
if(!$query){
echo $this->db_onedev->last_query();
$this->db_onedev->trans_rollback();
echo "error : insert so_resultentry_fisik_umum";
return;
}
}
}
}
}
$sql = "UPDATE so_resultentry_fisik_umum SET
So_ResultEntryFisikUmumIsActive = 'N',
So_ResultEntryFisikUmumDeletedUserID = 1511,
So_ResultEntryFisikUmumDeleted = NOW()
WHERE So_ResultEntryFisikUmumSo_ResultEntryID = {$dt_so_resultentry[0]['So_ResultEntryID']} AND
So_ResultEntryFisikUmumIsActive = 'R'";
// echo $sql;
// echo "
";
$query = $this->db_onedev->query($sql);
if(!$query){
$this->db_onedev->trans_rollback();
echo "error : update so_resultentry_fisik_umum";
return;
}
}
$this->db_onedev->trans_commit();
echo 'OK '.$labnumber.'
';
}
function additional_fisik_inject(){
$laporans = ['Laporan Pemeriksaan Kesehatan 1','Laporan Pemeriksaan Kesehatan 2','Laporan Pemeriksaan Kesehatan 3','Laporan Pemeriksaan Kesehatan 4','Laporan Pemeriksaan Kesehatan 5','Laporan Pemeriksaan Kesehatan 6','Laporan Pemeriksaan Kesehatan 7'];
foreach ($laporans as $laporan) {
$sql = "SELECT *
FROM `inject_price`
WHERE `TEST_NAME` = '{$laporan}' AND `NAMA_PAKET` <> 'Optional' AND
ID_COMPANY NOT IN ('00000223','00000308','223','308')";
$dt_inject_price = $this->db_onedev->query($sql)->result_array();
if(count($dt_inject_price) > 0){
foreach ($dt_inject_price as $k_inject_price => $v_inject_price) {
$sql = "SELECT * FROM inject_add_fisik_detail
WHERE InjectAddFisikDetailCode = '{$v_inject_price['TEST_ID']}'";
$dt_inject_add_fisik_detail = $this->db_onedev->query($sql)->result_array();
if(count($dt_inject_add_fisik_detail) > 0){
foreach ($dt_inject_add_fisik_detail as $k_inject_add_fisik_detail => $v_inject_add_fisik_detail) {
$sql = "INSERT INTO inject_price (
PRICE_ID,
PRICE_NAME,
NAMA_PAKET,
ID_COMPANY,
COMPANY,
TEST_ID,
CODE_TEST_CP_ONE,
TEST_NAME,
PRICE_LIST,
DISC,
`NET_PRICE`,
`START_DATE`,
`END_DATE`
) VALUES (
'{$v_inject_price['PRICE_ID']}',
'{$v_inject_price['PRICE_NAME']}',
'{$v_inject_price['NAMA_PAKET']}',
'{$v_inject_price['ID_COMPANY']}',
'{$v_inject_price['COMPANY']}',
'{$v_inject_price['TEST_ID']}',
'{$v_inject_add_fisik_detail['InjectAddFisikDetailCponeCode']}',
'{$v_inject_price['TEST_NAME']}',
0,
0,
0,
'{$v_inject_price['START_DATE']}',
'{$v_inject_price['END_DATE']}'
)";
$this->db_onedev->query($sql);
//echo $this->db_onedev->last_query();
}
}
print_r($v_inject_price);
}
}
}
}
function create_ss_price_by_id($id=0, $userid=777555){
$this->load->library("SsPriceMou");
$sql = "UPDATE t_priceheader
SET T_PriceHeaderValidasi = 'Y',
T_PriceHeaderValidasiDate = NOW(),
T_PriceHeaderValidasiUserID = {$userid},
T_PriceHeaderIsGenerated = 'Y',
T_PriceHeaderGeneratedDate = NOW()
WHERE T_PriceHeaderID = $id";
$query = $this->db_onedev->query($sql);
$sql = "UPDATE t_price
SET T_PriceValidasi = 'Y',
T_PriceValidasiDate = NOW(),
T_PriceValidasiDateUserID = {$userid},
T_PriceIsGenerated = 'Y',
T_PriceIsGeneratedDate = NOW()
WHERE T_PriceT_PriceHeaderID = $id
AND T_PriceValidasi = 'N'";
$query = $this->db_onedev->query($sql);
$sql = "UPDATE t_packet
SET T_PacketIsGenerated = 'Y',
T_PacketGeneratedDate = NOW()
WHERE T_PacketT_PriceHeaderID = $id";
$query = $this->db_onedev->query($sql);
//echo $this->db_onedev->last_query();
$this->sspricemou->create($id);
echo "selected price header : $id";
}
function create_ss_price($userid=77726){
$this->load->library("SsPriceMou");
//$sql = "SELECT * FROM `t_priceheader` WHERE `T_PriceHeaderCraetdUserID` = ? AND `T_PriceHeaderID` = ? AND `T_PriceHeaderIsActive` = 'Y'";
//$dt_price_header = $this->db_onedev->query($sql,array($userid,$price_header_id))->result_array();
//echo $this->db_onedev->last_query();
//exit;
$sql = "SELECT * FROM temp_validation_price WHERE status = 'N' LIMIT 30";
$dt_price_header = $this->db_onedev->query($sql)->result_array();
if(count($dt_price_header) > 0){
foreach ($dt_price_header as $k_price_header => $v_price_header) {
$id = $v_price_header['T_PriceHeaderID'];
//$userid = 54712;
$sql = "UPDATE t_priceheader
SET T_PriceHeaderValidasi = 'Y',
T_PriceHeaderValidasiDate = NOW(),
T_PriceHeaderValidasiUserID = {$userid},
T_PriceHeaderIsGenerated = 'Y',
T_PriceHeaderGeneratedDate = NOW()
WHERE T_PriceHeaderID = $id";
$query = $this->db_onedev->query($sql);
$sql = "UPDATE t_price
SET T_PriceValidasi = 'Y',
T_PriceValidasiDate = NOW(),
T_PriceValidasiDateUserID = {$userid},
T_PriceIsGenerated = 'Y',
T_PriceIsGeneratedDate = NOW()
WHERE T_PriceT_PriceHeaderID = $id
AND T_PriceValidasi = 'N'";
$query = $this->db_onedev->query($sql);
$sql = "UPDATE t_packet
SET T_PacketIsGenerated = 'Y',
T_PacketGeneratedDate = NOW()
WHERE T_PacketT_PriceHeaderID = $id";
$query = $this->db_onedev->query($sql);
//echo $this->db_onedev->last_query();
$this->sspricemou->create($id);
$sql = "UPDATE temp_validation_price SET status = 'Y' WHERE T_PriceHeaderID = $id";
$this->db_onedev->query($sql);
echo $v_price_header['T_PriceHeaderID'].'
';
}
}
}
function fix_result_kosong(){
$sql = "SELECT T_OrderDetailID, T_OrderDetailResult, api_ResultResult, T_OrderDEtailT_TestName,
api_ResultNormalNote, T_OrderDetailNormalValueDescription
FROM t_orderdetail
JOIN api_result ON api_ResultT_OrderDetailID = T_OrderDetailID AND
T_OrderDetailResult = '' AND api_ResultResult <> ''
GROUP BY api_ResultT_OrderDetailID";
$dt_order_detail = $this->db_onedev->query($sql)->result_array();
if(count($dt_order_detail) > 0){
foreach ($dt_order_detail as $k_order_detail => $v_order_detail) {
$sql = "UPDATE t_orderdetail SET T_OrderDetailResult = ?, T_OrderDetailNormalValueDescription = ? WHERE T_OrderDetailID = ?";
$this->db_onedev->query($sql,array($v_order_detail['api_ResultResult'],$v_order_detail['api_ResultNormalNote'],$v_order_detail['T_OrderDetailID']));
}
}
}
function inject_corporate($id=77726){
$sql = "SELECT *
FROM inject_corporate
WHERE
ID_CPONE = '' AND DONE = 'N'";
$dt_inject_corporate = $this->db_onedev->query($sql)->result_array();
if(count($dt_inject_corporate) > 0){
foreach ($dt_inject_corporate as $k_inject_corporate => $v_inject_corporate) {
$sql = "SELECT *
FROM corporate
WHERE ( CorporateOldCompanyID = '{$v_inject_corporate['COMPANY_ID']}' OR CorporateName = '{$v_inject_corporate['COMPANY_NAME']}' ) AND
CorporateIsActive = 'Y'";
$dt_corporate = $this->db_onedev->query($sql)->result_array();
if(count($dt_corporate) == 0){
$sql = "INSERT INTO corporate (
CorporateCode,
CorporateOldCompanyID,
CorporateName,
CorporatePICName,
CorporatePICPhone,
CorporatePICEmail,
CorporateEmail,
CorporatePhone,
CorporateAddress,
CorporateAddressCity,
CorporateCreated,
CorporateCreatedUserID
) VALUES (
fn_numbering_cpone('C'),
'{$v_corporate['COMPANY_ID']}',
'{$v_corporate['COMPANY_NAME']}',
'{$v_corporate['PIC_COMPANY']}',
'{$v_corporate['HP']}',
'{$v_corporate['EMAIL']}',
'{$v_corporate['EMAIL']}',
'{$v_corporate['PHONE']}',
'{$v_corporate['ADDRESS_NAME']}',
'{$v_corporate['CITY_NAME']}',
NOW(),
'{$id}'
)";
$this->db_onedev->query($sql);
//echo $this->db_onedev->last_query();
$last_corporate_id = $this->db_onedev->insert_id();
}
}
}
}
function inject_price_header($id=77726){
$sql = "SELECT * FROM corporate WHERE CorporateCreatedUserID = '{$id}' AND CorporateIsActive = 'Y'";
$dt_corporate = $this->db_onedev->query($sql)->result_array();
if(count($dt_corporate) > 0){
foreach ($dt_corporate as $k_corporate => $v_corporate) {
$sql = "SELECT * FROM inject_price WHERE ID_COMPANY = '{$v_corporate['CorporateOldCompanyID']}' LIMIT 1";
$dt_inject_price = $this->db_onedev->query($sql)->result_array();
$start_date = $dt_inject_price[0]['START_DATE'];
$end_date = $dt_inject_price[0]['END_DATE'];
/*create t_priceheader */
$sql = "INSERT INTO t_priceheader (
T_PriceHeaderCompanyOldID,
T_PriceHeaderCode,
T_PriceHeaderName,
T_PriceHeaderStartDate,
T_PriceHeaderEndDate,
T_PriceHeaderCreated,
T_PriceHeaderCraetdUserID
)
VALUES (
'{$v_corporate['CorporateOldCompanyID']}',
fn_numbering('PH'),
CONCAT('{$v_corporate['CorporateName']} - Harga Dasar 2025'),
NOW(),
'{$start_date}',
'{$end_date}',
77726
)";
$query = $this->db_onedev->query($sql);
$last_price_header_id = $this->db_onedev->insert_id();
}
}
}
function inject_price($id=77726,$old_company_id,$price_header_id=0){
if($old_company_id == '' || $price_header_id == 0){
echo 'old_company_id or price_header_id is empty';
exit;
}
$sql = "SELECT * FROM inject_price WHERE ID_COMPANY = '{$old_company_id}'";
$dt_inject_price = $this->db_onedev->query($sql)->result_array();
if(count($dt_inject_price) > 0){
foreach ($dt_inject_price as $k_inject_price => $v_inject_price) {
/*check if price is exist*/
$sql = " SELECT *
FROM t_price
JOIN t_test ON T_PriceT_TestID = T_TestID
WHERE T_PriceT_PriceHeaderID = {$price_header_id} AND T_TestSasCode = '{$v_inject_price['CODE_TEST_CP_ONE']}' AND T_PriceIsActive = 'Y'";
$dt_price = $this->db_onedev->query($sql)->result_array();
if(count($dt_price) == 0){
/*check if test is exist*/
$sql ="SELECT * FROM t_test WHERE T_TestSasCode = '{$v_inject_price['CODE_TEST_CP_ONE']}' AND T_TestIsActive = 'Y' LIMIT 1";
$dt_test = $this->db_onedev->query($sql)->result_array();
if(count($dt_test) > 0){
/*create price*/
$sql = "INSERT INTO t_price (
T_PriceT_PriceHeaderID,
T_PriceT_TestID,
T_PriceAmount,
T_PriceSubTotal,
T_PriceTotal,
T_PriceCreated,
T_PriceUserID
) VALUES (
{$price_header_id},
{$dt_test[0]['T_TestID']},
{$v_inject_price['PRICE_LIST']},
{$v_inject_price['PRICE_LIST']},
{$v_inject_price['PRICE_LIST']},
NOW(),
{$id}
)";
$this->db_onedev->query($sql);
$last_price_id = $this->db_onedev->insert_id();
//echo $this->db_onedev->last_query();
$sql = " SELECT *
FROM t_price
JOIN t_test ON T_PriceT_TestID = T_TestID
WHERE T_PriceID = {$last_price_id}";
$dt_price = $this->db_onedev->query($sql)->result_array();
echo $dt_price[0]['T_PriceID'].'|'.$dt_price[0]['T_TestSasCode'].'|'.$dt_price[0]['T_TestName'].'|'.$dt_price[0]['T_PriceAmount'].'|'.$dt_price[0]['T_PriceDiscRp'].'|'.$dt_price[0]['T_PriceSubTotal'].'|'.$dt_price[0]['T_PriceTotal'].'
';
}
}
$last_price_id = $dt_price[0]['T_PriceID'];
}
echo 'price created';
return;
}
else{
echo 'price not found';
return;
}
}
function get_packet($price_header_id=0,$old_company_id=0){
if($price_header_id == 0 || $old_company_id == 0){
echo 'price_header_id or old_company_id is empty';
exit;
}
$sql = "SELECT *
FROM t_packet
JOIN t_priceheader ON T_PacketT_PriceHeaderID = T_PriceHeaderID
JOIN corporate ON T_PriceHeaderCompanyOldID = CorporateOldCompanyID AND CorporateIsActive = 'Y' AND CorporateOldCompanyID = '{$old_company_id}'
WHERE
T_PacketIsActive = 'Y' AND T_PacketT_PriceHeaderID = {$price_header_id} AND ";
$dt_packet = $this->db_onedev->query($sql)->result_array();
}
function inject_price_v2(){
$sql = "SELECT *
FROM inject_corporate
WHERE
ID_CPONE = '' AND DONE = 'N'";
$dt_inject_corporate = $this->db_onedev->query($sql)->result_array();
//echo $this->db_onedev->last_query();
//echo count($dt_inject_corporate);
if(count($dt_inject_corporate) > 0){
foreach ($dt_inject_corporate as $k_corporate => $v_corporate) {
echo $v_corporate['COMPANY_ID'].'|'.$v_corporate['COMPANY_NAME'].'
';
$sql = "SELECT * FROM corporate Where ( CorporateOldCompanyID = '{$v_corporate['COMPANY_ID']}' OR CorporateName = '{$v_corporate['COMPANY_NAME']}' ) AND CorporateIsActive = 'Y'";
//echo $sql;
$dt_corporate = $this->db_onedev->query($sql)->result_array();
if(count($dt_corporate) == 0){
$sql = "INSERT INTO corporate (
CorporateCode,
CorporateOldCompanyID,
CorporateName,
CorporatePICName,
CorporatePICPhone,
CorporatePICEmail,
CorporateEmail,
CorporatePhone,
CorporateAddress,
CorporateAddressCity,
CorporateCreated,
CorporateCreatedUserID
) VALUES (
fn_numbering_cpone('C'),
'{$v_corporate['COMPANY_ID']}',
'{$v_corporate['COMPANY_NAME']}',
'{$v_corporate['PIC_COMPANY']}',
'{$v_corporate['HP']}',
'{$v_corporate['EMAIL']}',
'{$v_corporate['EMAIL']}',
'{$v_corporate['PHONE']}',
'{$v_corporate['ADDRESS_NAME']}',
'{$v_corporate['CITY_NAME']}',
NOW(),
77726
)";
$this->db_onedev->query($sql);
//echo $this->db_onedev->last_query();
$last_corporate_id = $this->db_onedev->insert_id();
$sql = "SELECT * FROM corporate WHERE CorporateID = '{$last_corporate_id}'";
$dt_corporate = $this->db_onedev->query($sql)->result_array();
echo $dt_corporate[0]['CorporateCode'].'|'.$dt_corporate[0]['CorporateName'].'
';
$sql ="UPDATE inject_corporate SET DONE = 'Y', ID_CPONE = '{$dt_corporate[0]['CorporateCode']}' WHERE COMPANY_ID = '{$v_corporate['COMPANY_ID']}'";
$this->db_onedev->query($sql);
$sql = "SELECT * FROM inject_price WHERE ID_COMPANY = '{$v_corporate['COMPANY_ID']}' LIMIT 1";
$query = $this->db_onedev->query($sql);
$dt_date = $query->result_array();
$begin_date = $dt_date[0]['START_DATE'];
$expiry_date = $dt_date[0]['END_DATE'];
$sql = "INSERT INTO t_priceheader (
T_PriceHeaderCompanyOldID,
T_PriceHeaderCode,
T_PriceHeaderName,
T_PriceHeaderStartDate,
T_PriceHeaderEndDate,
T_PriceHeaderCreated,
T_PriceHeaderCraetdUserID
)
VALUES (
'{$v_corporate['COMPANY_ID']}',
fn_numbering('PH'),
CONCAT('{$v_corporate['COMPANY_NAME']} - Harga Dasar 2025'),
NOW(),
'{$begin_date}',
'{$expiry_date}',
77726
)";
$query = $this->db_onedev->query($sql);
//echo $this->db_onedev->last_query();
$last_price_header_id = $this->db_onedev->insert_id();
$sql = "SELECT * FROM t_priceheader WHERE T_PriceHeaderID = {$last_price_header_id}";
$dt_price_header = $this->db_onedev->query($sql)->result_array();
echo $dt_price_header[0]['T_PriceHeaderCode'].'|'.$dt_price_header[0]['T_PriceHeaderName'].'
';
$sql = "INSERT INTO mgm_mcu(
Mgm_McuNumber,
Mgm_McuT_PriceHeaderID,
Mgm_McuM_BranchID,
Mgm_McuLabel,
Mgm_McuBisaTambahPemeriksaan,
Mgm_McuCorporateID,
Mgm_McuStartDate,
Mgm_McuEndDate,
Mgm_McuCreated,
Mgm_McuCreatedUserID
)
VALUES(
fn_numbering_cpone('SM'),
{$last_price_header_id},
100,
'{$v_corporate['COMPANY_NAME']} (Walk In 2025)',
'Y',
'{$dt_corporate[0]['CorporateID']}',
CURDATE(),
'2025-12-31',
NOW(),
77726
)";
//echo $sql;
$query = $this->db_onedev->query($sql);
$mgm_mcuID = $this->db_onedev->insert_id();
$sql = "SELECT * FROM mgm_mcu WHERE Mgm_McuID = {$mgm_mcuID}";
$dt_mgm_mcu = $this->db_onedev->query($sql)->result_array();
echo $dt_mgm_mcu[0]['Mgm_McuNumber'].'|'.$dt_mgm_mcu[0]['Mgm_McuLabel'].'
';
$sql = "INSERT INTO mgm_mcutemplate(
Mgm_McuTemplateMgm_McuID,
Mgm_McuTemplateFisikTemplateMappingID,
Mgm_McuTemplateCreated,
Mgm_McuTemplateCreatedUserID
)
VALUES(
{$mgm_mcuID},
5,
NOW(),
77726
)";
$this->db_onedev->query($sql);
$sql = "SELECT * FROM inject_price WHERE ID_COMPANY = '{$v_corporate['COMPANY_ID']}' AND NAMA_PAKET <> 'Optional'";
$dt_inject_price = $this->db_onedev->query($sql)->result_array();
//echo $this->db_onedev->last_query();
echo count($dt_inject_price);
if(count($dt_inject_price) > 0){
foreach ($dt_inject_price as $k_inject_price => $v_inject_price) {
$sql = " SELECT *
FROM t_price
JOIN t_test ON T_PriceT_TestID = T_TestID
WHERE T_PriceT_PriceHeaderID = {$last_price_header_id} AND T_TestSasCode = '{$v_inject_price['CODE_TEST_CP_ONE']}' AND T_PriceIsActive = 'Y'";
$dt_price = $this->db_onedev->query($sql)->result_array();
echo $this->db_onedev->last_query();
if(count($dt_price) == 0){
$sql ="SELECT * FROM t_test WHERE T_TestSasCode = '{$v_inject_price['CODE_TEST_CP_ONE']}' AND T_TestIsActive = 'Y'";
$dt_test = $this->db_onedev->query($sql)->result_array();
if(count($dt_test) > 0){
$sql = "INSERT INTO t_price (
T_PriceT_PriceHeaderID,
T_PriceT_TestID,
T_PriceAmount,
T_PriceSubTotal,
T_PriceTotal,
T_PriceCreated,
T_PriceUserID
) VALUES (
{$last_price_header_id},
{$dt_test[0]['T_TestID']},
{$v_inject_price['PRICE_LIST']},
{$v_inject_price['PRICE_LIST']},
{$v_inject_price['PRICE_LIST']},
NOW(),
77726
)";
$this->db_onedev->query($sql);
$last_price_id = $this->db_onedev->insert_id();
//echo $this->db_onedev->last_query();
$sql = " SELECT *
FROM t_price
JOIN t_test ON T_PriceT_TestID = T_TestID
WHERE T_PriceID = {$last_price_id}";
$dt_price = $this->db_onedev->query($sql)->result_array();
echo $dt_price[0]['T_PriceID'].'|'.$dt_price[0]['T_TestSasCode'].'|'.$dt_price[0]['T_TestName'].'|'.$dt_price[0]['T_PriceAmount'].'|'.$dt_price[0]['T_PriceDiscRp'].'|'.$dt_price[0]['T_PriceSubTotal'].'|'.$dt_price[0]['T_PriceTotal'].'
';
}
}
$sql = "SELECT *
FROM t_packet
WHERE
T_PacketT_PriceHeaderID = {$last_price_header_id} AND
T_PacketIsActive = 'Y' AND
T_PacketOldPriceID = '{$v_inject_price['PRICE_ID']}' AND
T_PacketOldCompanyID = '{$v_corporate['COMPANY_ID']}'";
//echo $sql;
$dt_packet = $this->db_onedev->query($sql)->result_array();
echo $this->db_onedev->last_query();
if(count($dt_packet) == 0){
$sql = "INSERT INTO t_packet (
T_PacketOldPriceID,
T_PacketOldCompanyID,
T_PacketT_PriceHeaderID,
T_PacketType,
T_PacketSasCode,
T_PacketName,
T_PacketStartDate,
T_PacketEndDate,
T_PacketCreated
) VALUES (
'{$v_inject_price['PRICE_ID']}',
'{$v_corporate['COMPANY_ID']}',
{$last_price_header_id},
'PN',
fn_numbering_cpone('PCPN'),
'{$v_inject_price['PRICE_NAME']}',
'{$v_inject_price['START_DATE']}',
'{$v_inject_price['END_DATE']}',
NOW()
)";
$this->db_onedev->query($sql);
echo $this->db_onedev->last_query();
$last_packet_id = $this->db_onedev->insert_id();
$sql = "SELECT *
FROM t_packet
WHERE
T_PacketID = {$last_packet_id}";
$dt_packet = $this->db_onedev->query($sql)->result_array();
echo $dt_packet[0]['T_PacketID'].'|'.$dt_packet[0]['T_PacketSasCode'].'|'.$dt_packet[0]['T_PacketName'].'|'.$dt_packet[0]['T_PacketPrice'].'|'.$dt_packet[0]['T_PacketOriginalPrice'].'
';
$sql = "INSERT INTO mgm_mcupacket(
Mgm_McuPacketMgm_McuID,
Mgm_McuPacketT_PacketID,
Mgm_McuPacketCreated,
Mgm_McuPacketCreatedUserID
)
VALUES(
{$mgm_mcuID},
{$dt_packet[0]['T_PacketID']},
NOW(),
77726
)";
$query = $this->db_onedev->query($sql);
}
$sql = "INSERT INTO t_packetdetail (
T_PacketDetailT_PacketID,
T_PacketDetailT_TestID,
T_PacketDetailOriginalPrice,
T_PacketDetailPrice,
T_PacketDetailCreated
) VALUES (
{$dt_packet[0]['T_PacketID']},
{$dt_price[0]['T_TestID']},
{$v_inject_price['PRICE_LIST']},
{$v_inject_price['NET_PRICE']},
NOW()
)";
$this->db_onedev->query($sql);
echo $dt_price[0]['T_PriceID'].'|'.$dt_price[0]['T_TestSasCode'].'|'.$dt_price[0]['T_TestName'].'
';
$sql = "SELECT SUM(T_PacketDetailPrice) as total, SUM(T_PacketDetailOriginalPrice) as original
FROM t_packetdetail
WHERE T_PacketDetailT_PacketID = {$dt_packet[0]['T_PacketID']} AND T_PacketDetailIsActive = 'Y'";
$dt_packetdetail = $this->db_onedev->query($sql)->result_array();
echo $dt_packetdetail[0]['total'].'|'.$dt_packetdetail[0]['original'].'
';
if(count($dt_packetdetail) > 0){
$sql = "UPDATE t_packet SET
T_PacketPrice = {$dt_packetdetail[0]['total']},
T_PacketOriginalPrice = {$dt_packetdetail[0]['original']},
T_PacketLastUpdated = NOW()
WHERE T_PacketID = {$dt_packet[0]['T_PacketID']}";
$this->db_onedev->query($sql);
}
}
}
$sql = "SELECT * FROM inject_price WHERE ID_COMPANY = '{$v_corporate['COMPANY_ID']}' AND NAMA_PAKET = 'Optional'";
$dt_inject_price_optional = $this->db_onedev->query($sql)->result_array();
if(count($dt_inject_price_optional) > 0){
foreach ($dt_inject_price_optional as $k_inject_price_optional => $v_inject_price_optional) {
$sql = " SELECT *
FROM t_price
JOIN t_test ON T_PriceT_TestID = T_TestID
WHERE T_PriceT_PriceHeaderID = {$last_price_header_id} AND T_TestSasCode = '{$v_inject_price_optional['CODE_TEST_CP_ONE']}' AND T_PriceIsActive = 'Y'";
$dt_price_optional = $this->db_onedev->query($sql)->result_array();
if(count($dt_price_optional) == 0){
$sql ="SELECT * FROM t_test WHERE T_TestSasCode = '{$v_inject_price_optional['CODE_TEST_CP_ONE']}' AND T_TestIsActive = 'Y'";
$dt_test = $this->db_onedev->query($sql)->result_array();
if(count($dt_test) > 0){
$sql = "INSERT INTO t_price (
T_PriceT_PriceHeaderID,
T_PriceT_TestID,
T_PriceAmount,
T_PriceDisc,
T_PriceDiscRp,
T_PriceSubTotal,
T_PriceTotal,
T_PriceCreated,
T_PriceUserID
) VALUES (
{$last_price_header_id},
{$dt_test[0]['T_TestID']},
{$v_inject_price_optional['PRICE_LIST']},
0,
{$v_inject_price_optional['DISC']},
{$v_inject_price_optional['NET_PRICE']},
{$v_inject_price_optional['NET_PRICE']},
NOW(),
77726
)";
$this->db_onedev->query($sql);
///echo $this->db_onedev->last_query();
$last_price_optional_id = $this->db_onedev->insert_id();
$sql = "SELECT * FROM
t_price
JOIN t_test ON T_PriceT_TestID = T_TestID
WHERE T_PriceID = {$last_price_optional_id}";
$dt_price_optional = $this->db_onedev->query($sql)->result_array();
//echo $this->db_onedev->last_query();
echo $dt_price_optional[0]['T_PriceID'].'|'.$dt_price_optional[0]['T_TestSasCode'].'|'.$dt_price_optional[0]['T_TestName'].'|'.$dt_price_optional[0]['T_PriceAmount'].'|'.$dt_price_optional[0]['T_PriceDiscRp'].'|'.$dt_price_optional[0]['T_PriceSubTotal'].'|'.$dt_price_optional[0]['T_PriceTotal'].'
';
}
}
}
}
}else{
$sql = "UPDATE inject_corporate SET DONE = 'V', ID_CPONE = '{$dt_corporate[0]['CorporateCode']}' WHERE COMPANY_ID = '{$v_corporate['COMPANY_ID']}'";
$this->db_onedev->query($sql);
}
}
}
}
function fix_order_location($labnumber){
//echo $labnumber;
/// echo '
';
$new_location = array();
$sql = "SELECT * FROM t_orderheader WHERE T_OrderHeaderLabNumber = '{$labnumber}'";
$dt = $this->db_onedev->query($sql)->row_array();
$sql = "SELECT T_SampleStationID, M_LocationID,T_SampleStationName,M_LocationName
FROM t_orderdetail
JOIN t_test ON T_OrderDetailT_TestID = T_TestID AND T_TestIsActive = 'Y'
JOIN t_sampletype ON T_TestT_SampleTypeID = T_SampleTypeID
JOIN t_bahan ON T_SampleTypeT_BahanID = T_BahanID
JOIN t_samplestation ON T_BahanT_SampleStationID = T_SampleStationID AND T_SampleStationIsActive = 'Y'
JOIN m_location ON M_LocationT_SampleStationID = T_SampleStationID AND M_LocationIsActive = 'Y'
WHERE
T_OrderDetailT_OrderHeaderID = '{$dt['T_OrderHeaderID']}' AND T_OrderDetailIsActive = 'Y'
GROUP BY T_SampleStationID";
$dt_location = $this->db_onedev->query($sql)->result_array();
//echo json_encode($dt_location);
//echo '
';
if(count($dt_location) > 0){
$sql = "UPDATE t_order_location SET T_OrderLocationIsActive = 'X' WHERE T_OrderLocationT_OrderHeaderID = ? AND T_OrderLocationIsActive = 'Y'";
$query = $this->db_onedev->query($sql,array(
$dt['T_OrderHeaderID']
));
//echo $this->db_onedev->last_query();
//echo '
';
foreach ($dt_location as $k => $v) {
$exist_order_location = [];
$sql = " SELECT *
FROM t_order_location
WHERE
T_OrderLocationT_OrderHeaderID = ? AND
T_OrderLocationT_SampleStationID= ? AND
T_OrderLocationM_LocationID = ? AND
T_OrderLocationIsActive = 'X' LIMIT 1";
$query = $this->db_onedev->query($sql,array(
$dt['T_OrderHeaderID'],
$v['T_SampleStationID'],
$v['M_LocationID']
));
$dt_exist_order_location = $query->result_array();
if(count($dt_exist_order_location) > 0){
$exist_order_location = $dt_exist_order_location[0];
$sql = "UPDATE t_order_location SET T_OrderLocationIsActive = 'Y' WHERE T_OrderLocationID = ? AND T_OrderLocationIsActive = 'X'";
$query = $this->db_onedev->query($sql,array(
$exist_order_location['T_OrderLocationID']
));
//echo $this->db_onedev->last_query().';';
//echo '
';
}else{
$sql = "SELECT * FROM t_order_location WHERE
T_OrderLocationT_OrderHeaderID = ? AND T_OrderLocationM_LocationID = ? AND
T_OrderLocationT_SampleStationID = ? AND T_OrderLocationIsActive = 'N' LIMIT 1";
$query = $this->db_onedev->query($sql,array(
$dt['T_OrderHeaderID'],
$v['M_LocationID'],
$v['T_SampleStationID']
));
$dt_exist_order_location_deleted = $query->result_array();
if(count($dt_exist_order_location_deleted) > 0){
$sql = "UPDATE t_order_location SET T_OrderLocationIsActive = 'Y'
WHERE T_OrderLocationID = ? AND T_OrderLocationIsActive = 'N'";
$query = $this->db_onedev->query($sql,array(
$dt_exist_order_location_deleted[0]['T_OrderLocationID']
));
//echo $this->db_onedev->last_query().';';
//echo '
';
}
else{
$sql = "INSERT INTO t_order_location (
T_OrderLocationT_OrderHeaderID,
T_OrderLocationM_LocationID,
T_OrderLocationT_SampleStationID,
T_OrderLocationCreated,
T_OrderLocationUserID
)
VALUES(?,?,?,NOW(),?)";
$query = $this->db_onedev->query($sql,array(
$dt['T_OrderHeaderID'],
$v['M_LocationID'],
$v['T_SampleStationID'],
545
));
$new_location[] = array('sample_station_name' => $v['T_SampleStationName'], 'location_name' => $v['M_LocationName']);
//echo $this->db_onedev->last_query().';';
//echo '
';
}
}
}
$sql = "UPDATE t_order_location SET T_OrderLocationIsActive = 'N', T_OrderLocationUserID = ? WHERE T_OrderLocationT_OrderHeaderID = ? AND T_OrderLocationIsActive = 'X'";
$query = $this->db_onedev->query($sql,array(
545,
$dt['T_OrderHeaderID']
));
//echo $this->db_onedev->last_query();
//echo '
';
}
$sql = "SELECT T_SampleStationID, M_LocationID, T_TestName, M_LocationName, T_SampleStationName
FROM t_orderdetail
JOIN t_test ON T_OrderDetailT_TestID = T_TestID AND T_TestIsActive = 'Y'
JOIN t_sampletype ON T_TestT_SampleTypeID = T_SampleTypeID
JOIN t_bahan ON T_SampleTypeT_BahanID = T_BahanID
JOIN t_samplestation ON T_BahanT_SampleStationID = T_SampleStationID AND T_SampleStationIsActive = 'Y'
JOIN m_location ON M_LocationT_SampleStationID = T_SampleStationID AND M_LocationIsActive = 'Y'
WHERE
T_OrderDetailT_OrderHeaderID = '{$dt['T_OrderHeaderID']}' AND T_OrderDetailIsActive = 'Y'
GROUP BY T_SampleStationID";
$dt_location = $this->db_onedev->query($sql)->result_array();
foreach ($dt_location as $k => $v) {
echo $v['T_TestName'].'|'.$v['M_LocationName'].'|'.$v['T_SampleStationName'].'
';
}
if(count($new_location) > 0){
foreach ($new_location as $k => $v) {
echo $v['sample_station_name'].'|'.$v['location_name'].'
';
}
}
echo "selesai".'
';
}
function fix_price_thorax(){
$labnumber = array('I2502030026','I2502030027','I2502030028','I2502030029','I2502030030','I2502030031','I2502030032','I2502030033');
$string_lbnumber = join("','",$labnumber);
$sql = "SELECT * FROM t_orderheader WHERE T_OrderHeaderLabNumber IN ('$string_lbnumber') LIMIT 8";
$dt = $this->db_onedev->query($sql)->result_array();
//print_r($dt);
foreach ($dt as $key => $value) {
//echo $value['T_OrderHeaderID'].'|';
$sql = "UPDATE t_orderdetail SET
T_OrderDetailPrice = 0,
T_OrderDetailTotal = 0,
T_OrderDetailPriceForDisc = 0,
T_OrderDetailLastUpdatedUserID = 545,
T_OrderDetailLastUpdated = NOW()
WHERE
T_OrderDetailT_OrderHeaderID = {$value['T_OrderHeaderID']} AND
T_OrderDetailT_TestID IN ( 2145, 2147, 2149, 2150, 2151, 2152 ) ";
// echo $sql;
$this->db_onedev->query($sql);
$total = 0;
$sql = "SELECT SUM(T_OrderDetailTotal) as total
FROM `t_orderdetail`
WHERE
`T_OrderDetailT_OrderHeaderID` = '{$value['T_OrderHeaderID']}' AND
`T_OrderDetailTotal` > '0'";
//echo $sql;
$dt_total = $this->db_onedev->query($sql)->row_array();
$total = $dt_total['total'];
$sql = "UPDATE t_orderheader SET
T_OrderHeaderSubTotal = {$total} ,
T_OrderHeaderTotal = {$total},
T_OrderHeaderLastUpdatedUserID = 545,
T_OrderHeaderLastUpdated = NOW()
WHERE
T_OrderHeaderID = '{$value['T_OrderHeaderID']}'";
//echo $sql;
$this->db_onedev->query($sql);
}
}
function fix_price_2025X(){
$rtn = [];
$sql = "SELECT T_PriceID,115, T_TestID,T_TestCode,T_TestSasCode,T_TestName, price, netprice, NOW(), 515
FROM tmp_price_25
JOIN t_test ON T_TestCode = code
LEFT JOIN t_price ON T_PriceT_PriceHeaderID = 115 AND T_PriceIsActive = 'Y' AND T_TestID = T_PriceT_TestID
WHERE `code` IS NOT NULL AND
( LENGTH(T_TestSasCode) > 8 AND T_PriceID IS NOT NULL) ";
$dt = $this->db_onedev->query($sql)->result_array();
foreach ($dt as $key => $value) {
$sql = "SELECT * FROM t_test WHERE T_TestCode = {$value['T_TestCode']} AND LENGTH(T_TestSasCode) = 8 AND T_TestIsActive = 'Y' ";
$row_test = $this->db_onedev->query($sql)->row_array();
if($row_test){
$sql = "UPDATE t_price SET T_PriceT_TestID = {$row_test['T_TestID']}, T_PriceLastUpdated = NOW(), T_PriceUserID = 535
WHERE T_PriceID = {$value['T_PriceID']}";
$this->db_onedev->query($sql);
//echo $this->db_onedev->last_query();
$sql = "UPDATE t_packetdetail SET T_PacketDetailT_TestID = {$row_test['T_TestID']}, T_PacketDetailLastUpdated = NOW()
WHERE T_PacketDetailT_TestID = {$value['T_TestID']}";
$this->db_onedev->query($sql);
//echo $this->db_onedev->last_query();
$sql = "UPDATE t_orderdetail
JOIN t_orderheader ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID
JOIN mgm_mcu ON T_OrderHeaderMgm_McuID = Mgm_McuID AND Mgm_McuT_PriceHeaderID = 115
SET T_OrderDetailT_TestID = {$row_test['T_TestID']},
T_OrderDetailT_TestCode = '{$row_test['T_TestCode']}',
T_OrderDetailT_TestSasCode = '{$row_test['T_TestSasCode']}',
T_OrderDetailT_TestName = '{$row_test['T_TestName']}',
T_OrderDetailLastUpdatedUserID = 535,
T_OrderDetailLastUpdated = NOW()
WHERE T_OrderDetailT_TestID = {$value['T_TestID']}";
$this->db_onedev->query($sql);
// echo $this->db_onedev->last_query();
//$rtn[] = array('T_PriceID' => $value['T_PriceID'], 'OLD_T_TestID' => $value['T_TestID'], 'NEW_T_TestID' => $row_test['T_TestID']);
}
}
echo json_encode($rtn);
}
function fix_alkohol(){
$rtn = array();
$sql = "SELECT Reference, T_OrderHeaderID,Nat_UnitID, Nat_UnitName,T_OrderDetailID,LabNumber, PatientID, PatientName, M_PatientName, T_OrderDetailT_TestID, T_OrderDetailT_TestCode, Result, T_OrderDetailResult
FROM tmp_alkohol_breath_test
JOIN t_orderheader ON T_OrderHeaderLabNumber = LabNumber AND T_OrderHeaderLabNumber IN ('T2412100002','T2412100003')
JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
JOIN t_orderdetail ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID
AND T_OrderDetailIsActive = 'Y'
AND T_OrderDetailT_TestCode = '10594000'
JOIN t_test ON T_OrderDetailT_TestID = T_TestID
JOIN nat_test ON T_TestNat_TestID = Nat_TestID
JOIN nat_unit ON Nat_TestNat_UnitID = Nat_UnitID
";
$dt = $this->db_onedev->query($sql)->result_array();
foreach ($dt as $key => $value) {
$sql = "UPDATE t_orderdetail
SET
T_OrderDetailResult = '{$value['Result']}',
T_OrderDetailNat_UnitID = '{$value['Nat_UnitID']}',
T_OrderDetailNat_UnitName = '{$value['Nat_UnitName']}',
T_OrderDetailNormalValueNote = '{$value['Reference']}',
T_OrderDetailDeletedUserID = 565
WHERE
T_OrderDetailID = {$value['T_OrderDetailID']}
";
//echo $sql;
$this->db_onedev->query($sql);
$rtn[] = $value['T_OrderDetailID'];
}
echo json_encode($rtn);
}
function add_template_haji_order(){
$sql = "SELECT T_OrderHeaderID, So_ResultEntryID
FROM t_orderheader
JOIN mgm_mcu ON T_OrderHeaderMgm_McuID = Mgm_McuID
JOIN mgm_mcutemplate ON Mgm_McuTemplateMgm_McuID = Mgm_McuID AND
Mgm_McuTemplateFisikTemplateMappingID = 9
AND T_OrderHeaderMgm_McuID <> 56
JOIN so_resultentry ON So_ResultEntryT_OrderHeaderID = T_OrderHeaderID AND
So_ResultEntryNonlab_TemplateID = 27
";
$dt = $this->db_onedev->query($sql)->result_array();
foreach ($dt as $key => $value) {
$sql = "SELECT FisikTemplateTitle as template_name,
FisikTemplateMappingDetailFisikTemplateID as template_id,
FisikTemplateJSON as details
FROM fisik_template_mapping_detail
JOIN fisik_template ON FisikTemplateMappingDetailFisikTemplateID = FisikTemplateID
WHERE
FisikTemplateMappingDetailFisikTemplateMappingID = 9 AND
FisikTemplateMappingDetailIsActive = 'Y' AND FisikTemplateIsActive = 'Y'";
$dt_template = $this->db_onedev->query($sql)->result_array();
//$exist_dt = 0;
foreach ($dt_template as $k => $v) {
$sql = "SELECT *
FROM so_resultentry_fisik_umum
WHERE
So_ResultEntryFisikUmumSo_ResultEntryID = {$value['So_ResultEntryID']} AND
So_ResultEntryFisikUmumFisikTemplateID = {$v['template_id']} AND So_ResultEntryFisikUmumIsActive = 'Y'";
$chk_exist = $this->db_onedev->query($sql)->result_array();
//echo count($chk_exist) ;
if(count($chk_exist) == 0){
//$exist_dt += 1;
//echo $v['template_name']."^".count($chk_exist).'|';
$sql = "INSERT INTO so_resultentry_fisik_umum (
So_ResultEntryFisikUmumSo_ResultEntryID,
So_ResultEntryFisikUmumFisikTemplateID,
So_ResultEntryFisikUmumDetails,
So_ResultEntryFisikUmumCreated,
So_ResultEntryFisikUmumCreatedUserID
)
VALUES(
{$value['So_ResultEntryID']},
{$v['template_id']},
'{$v['details']}',
NOW(),
575
)";
//if( $exist_dt ==1)
//echo $sql.';';
$ins = $this->db_onedev->query($sql);
}
}
}
}
function generatessprice($mgmmcuid){
$this->load->library('SsPriceMou');
$this->sspricemou->create($mgmmcuid);
echo "selesai";
}
function genqrcode($nomorlab){
$sql = "SELECT T_OrderHeaderID FROM t_orderheader WHERE T_OrderHeaderLabNumber = '{$nomorlab}'";
$qr = $this->db_smartone->query($sql);
$r = $qr->row();
$id = $r->T_OrderHeaderID;
$this->load->library('ciqrcode'); //pemanggilan library QR CODE
$home_dir = "/home/one/project/one/";
$target_dir = $home_dir . "one-media/one-qrcontrolcard/";
$config['cacheable'] = false; //boolean, the default is true
//$config['cachedir'] = './assets/'; //string, the default is application/cache/
//$config['errorlog'] = './assets/'; //string, the default is application/logs/
$config['imagedir'] = $target_dir; //direktori penyimpanan qr code
$config['quality'] = true; //boolean, the default is true
$config['size'] = '1024'; //interger, the default is 1024
$config['black'] = array(224,255,255); // array, default is array(255,255,255)
$config['white'] = array(70,130,180); // array, default is array(0,0,0)
$this->ciqrcode->initialize($config);
$image_name = "qrcode_".$nomorlab.".png"; //buat name dari qr code sesuai dengan nim
$file_path = $config['imagedir'].$image_name;
// Check if file already exists
if(file_exists($file_path)) {
// Generate timestamp
$timestamp = date('YmdHis');
$file_info = pathinfo($file_path);
$new_name = $file_info['filename'] . '_' . $timestamp . '.' . $file_info['extension'];
// Rename existing file
rename($file_path, $config['imagedir'] . $new_name);
}
$params['data'] = "http://cpone.aplikasi.web.id/one-ui/test/vuex/cpone-control-card/?noreg=".$nomorlab."&id=".$id; //data yang akan di jadikan QR CODE
$params['level'] = 'H'; //H=High
$params['size'] = 10;
$params['savename'] = $file_path; //simpan image QR CODE ke folder assets/images/
$this->ciqrcode->generate($params); // fungsi untuk generate QR CODE
echo "http://cpone.aplikasi.web.id/one-api/assets/images/".$image_name;
}
function gen_kesimpulan_fisik($id){
$this->load->library('kesimpulanfisik');
$kesimpulan = $this->kesimpulanfisik->kesimpulan($id);
echo $kesimpulan;
}
function generate_kelainan_fisik_bydate($stardate,$endate){
$sql = "SELECT *
FROM so_resultentry
JOIN t_orderheader ON So_ResultEntryT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y' AND
( DATE(T_OrderHeaderDate) BETWEEN ? AND ? ) AND T_OrderHeaderIsActive = 'Y'
WHERE
So_ResultEntryNonlab_TemplateID = 27 AND So_ResultEntryIsActive = 'Y'
";
$query = $this->db_onedev->query($sql,array($stardate,$endate));
//echo $this->db_onedev->last_query();
$datas = $query->result_array();
foreach ($datas as $key => $value) {
$this->gen_kelainan_fisik($value['So_ResultEntryID']);
}
}
/*function gen_kelainan_fisik_all($mgmmcuid,$limit){
$this->load->library('kesimpulanfisik');
$kesimpulan = $this->kesimpulanfisik->generate_all_kelainan_fisik($mgmmcuid,$limit);
echo json_encode($kesimpulan);
}
function gen_kelainan_fisik($id){
$this->load->library('kesimpulanfisik');
$kesimpulan = $this->kesimpulanfisik->generate_kelainan_fisik($id);
echo json_encode($kesimpulan);
}
function gen_nonlab_template($id){
$this->load->library('nonlabtemplate');
$kesimpulan = $this->nonlabtemplate->generate($id);
print_r($kesimpulan);
}*/
function genpatientqrcode($nomorlab){
$this->load->library('ciqrcode'); //pemanggilan library QR CODE
$home_dir = "/home/one/project/one/";
$target_dir = $home_dir . "one-media/one-qrpatient/";
$config['cacheable'] = false; //boolean, the default is true
//$config['cachedir'] = './assets/'; //string, the default is application/cache/
//$config['errorlog'] = './assets/'; //string, the default is application/logs/
$config['imagedir'] = $target_dir; //direktori penyimpanan qr code
$config['quality'] = true; //boolean, the default is true
$config['size'] = '1024'; //interger, the default is 1024
$config['black'] = array(224,255,255); // array, default is array(255,255,255)
$config['white'] = array(70,130,180); // array, default is array(0,0,0)
$this->ciqrcode->initialize($config);
$image_name="patient_qr_".$nomorlab.".png"; //buat name dari qr code sesuai dengan nim
$params['data'] = $nomorlab; //data yang akan di jadikan QR CODE
$params['level'] = 'H'; //H=High
$params['size'] = 10;
$params['savename'] = $config['imagedir'].$image_name; //simpan image QR CODE ke folder assets/images/
$this->ciqrcode->generate($params); // fungsi untuk generate QR CODE
echo "https://devcpone.aplikasi.web.id/one-media/one-qrpatient/".$image_name;
//echo "selesai";
}
function truncatextable(){
$this->db = $this->load->database("onedev",true);
$xgroup= array('PRICE','ORDER');
$where_in = "";
foreach ($xgroup as $key => $value) {
if($where_in != "") $where_in .= " or ";
$where_in .= "X_TableTransactionGroupName = '".$value."'";
}
$sql = "SELECT * FROM x_table_transaction
WHERE
X_TableTransactionIsActive = 'Y' AND ( $where_in )";
//echo $sql;
$data_tables = $this->db->query($sql)->result_array();
foreach($data_tables as $k => $v){
$sql = "TRUNCATE TABLE {$v['X_TableTransactionTableName']}";
//echo $sql;
//$this->db->query($sql);
}
echo 'selesai';
}
function exporttoxls(){
$this->db = $this->load->database("onedev",true);
$sql = "SELECT * FROM xtable";
$data_asal = $this->db->query($sql)->result_array();
foreach($data_asal as $k => $v){
$xplode = explode(' ',$v['TANGGAL_LAHIR']);
$tgl = sprintf("%02d", $xplode[0]);
$bln = $this->monthtonumber($xplode[1]);
$thn = $xplode[2];
$xdate = $tgl.'-'.$bln.'-'.$thn;
$sql = "UPDATE xtable SET TANGGAL_LAHIR_INA = '{$xdate}' WHERE id = {$v['id']}";
$this->db->query($sql);
}
echo 'selesai';
}
function monthtonumber($xmonth){
$rst = 0;
if($xmonth == 'Januari')
$rst = '01';
if($xmonth == 'Februari')
$rst = '02';
if($xmonth == 'Maret')
$rst = '03';
if($xmonth == 'April')
$rst = '04';
if($xmonth == 'Mei')
$rst = '05';
if($xmonth == 'Juni')
$rst = '06';
if($xmonth == 'Juli')
$rst = '07';
if($xmonth == 'Agustus')
$rst = '08';
if($xmonth == 'September')
$rst = '09';
if($xmonth == 'Oktober')
$rst = '10';
if($xmonth == 'November')
$rst = '11';
if($xmonth == 'Desember')
$rst = '12';
return $rst;
}
function two() {
$this->db = $this->load->database("onedev",true);
$id = 946;
$sql = "select
T_TestCalculationID,T_TestCalculationFormula,
T_OrderDetailID, T_OrderDetailT_TestID, T_TestNat_TestID,
T_TestCalculationID,T_OrderDetailResult,
fn_global_age_count_day(M_PatientDOB, date(T_OrderHeaderDate)) / 365 AgeInYear,
T_TestCalculationNat_SexID
from t_orderdetail
join t_orderheader on T_OrderDetailT_OrderHeaderID = T_OrderHeaderID
and T_OrderHeaderID = ?
join m_patient on T_OrderHeaderM_PatientID = M_PatientID
join t_test on T_OrderDetailT_TestID = T_TestID
and T_OrderDetailIsActive = 'Y' and T_TestIsActive = 'Y'
join t_testcalculation on T_TestNat_TestID = T_TestCalculationNat_TestID
and T_TestCalculationIsActive = 'Y'
and (
T_TestCalculationNat_SexID = M_PatientM_SexID
or
T_TestCalculationNat_SexID = 0
)
";
$sql_det = "select T_TestCalculationDetailCode, T_OrderDetailResult
from
t_testcalculation_detail td
join t_testcalculation on T_TestCalculationID = T_TestCalculationDetailT_TestCalculationID
and T_TestCalculationID = ?
join t_test t on td.T_TestCalculationDetailNat_TestID = T_TestNat_TestID
left join t_orderdetail on T_TestID = T_OrderDetailT_TestID and T_OrderDetailIsActive = 'Y'
and T_OrderDetailT_OrderHeaderID = ?
where T_OrderDetailID is not null";
$qry = $this->db->query($sql, array($id));
$rows = $qry->result_array();
if ( count($rows) > 0 ) {
foreach($rows as $r) {
if (false && $r["T_OrderDetailResult"] != "") continue;
$tc_id = $r["T_TestCalculationID"];
$qry_det = $this->db->query($sql_det, array($tc_id, $id));
$drows = $qry_det->result_array();
$formula = $r["T_TestCalculationFormula"];
$have_all = true;
$have_one = false;
$formula = str_replace("AGE",$r["AgeInYear"], $formula);
foreach($drows as $dr) {
if($dr["T_OrderDetailResult"] == "" ) {
$have_all = false;
break;
}
$have_one = true;
$code = $dr["T_TestCalculationDetailCode"];
$value = $dr["T_OrderDetailResult"];
$formula = str_replace($code, $value,$formula);
}
if ($have_all && $have_one) {
echo "Formula : $formula
";
eval("\$f_value = $formula;");
$od_id = $r["T_OrderDetailID"];
$sql = "update t_orderdetail set T_OrderDetailResult = ?
where T_OrderDetailID = ?";
$this->db->query($sql, array($f_value,$od_id));
}
}
}
}
function dynamic_comparison($varleft, $op, $varright)
{
switch ($op) {
case "=":
return $varleft == $varright;
case "!=":
return $varleft != $varright;
case ">=":
return $varleft >= $varright;
case "<=":
return $varleft <= $varright;
case ">":
return $varleft > $varright;
case "<":
return $varleft < $varright;
default:
return true;
}
}
function usg_abdomen(){
$rtn = [];
$this->load->library('nonlabtemplate');
$sql = "SELECT T_SamplingSoID, T_OrderDetailID, T_OrderDetailT_OrderHeaderID, So_ResultEntryID
FROM `t_orderdetail`
JOIN t_samplingso ON T_SamplingSoT_OrderHeaderID = T_OrderDetailT_OrderHeaderID AND
T_SamplingSoT_TestID = T_OrderDetailT_TestID
LEFT JOIN so_resultentry ON T_OrderDetailT_OrderHeaderID = So_ResultEntryT_OrderHeaderID AND
So_ResultEntryT_OrderDetailID = T_OrderDetailID AND so_resultentryIsActive = 'Y'
WHERE
`T_OrderDetailT_TestID` = '3385' AND So_ResultEntryID IS NULL AND
T_OrderDetailIsActive = 'Y'";
$query = $this->db_onedev->query($sql);
$datas = $query->result_array();
foreach ($datas as $key => $value) {
$kesimpulan = $this->nonlabtemplate->generate($value['T_SamplingSoID']);
$rtn[] = $kesimpulan['So_ResultEntryID'];
}
echo join(", ",$rtn);
}
function updatetotalheader(){
$rtn = [];
$sql = "SELECT T_OrderDetailT_OrderHeaderID
FROM `t_orderdetail`
JOIN t_orderdetailorder ON T_OrderDetailT_OrderDetailOrderID = T_OrderDetailOrderID AND T_OrderDetailOrderIsActive = 'Y' AND
`T_OrderDetailIsActive` = 'Y' AND T_OrderDetailIsActive = 'Y'
JOIN t_packet ON T_OrderDetailOrderT_PacketID = T_PacketID AND T_PacketSasCode IN ('PN2400170','PN2400169','PN2400168','PN2400167','PN2400166','PN2400165','PN2400164','PN2400163','PN2400162',
'PN2400161','PN2400160','PN2400159','PN2400158','PN2400157','PN2400156','PN2400155','PN2400154','PN2400175')
GROUP BY T_OrderDetailT_OrderHeaderID";
$query = $this->db_onedev->query($sql);
$datas = $query->result_array();
foreach ($datas as $key => $value) {
$sql = "SELECT SUM(T_OrderDetailPrice) as total
FROM `t_orderdetail`
WHERE `T_OrderDetailIsActive` = 'Y' AND
`T_OrderDetailT_OrderHeaderID` = {$value['T_OrderDetailT_OrderHeaderID']} AND
`T_OrderDetailPrice` > '0'
";
$query = $this->db_onedev->query($sql);
//echo $this->db_onedev->last_query();
$total = $query->row()->total;
$sql = "UPDATE t_orderheader SET T_OrderHeaderSubTotal = {$total}, T_OrderHeaderLastUpdatedUserID = 181024, T_OrderHeaderLastUpdated = NOW()
WHERE T_OrderHeaderID = {$value['T_OrderDetailT_OrderHeaderID']}
";
$query = $this->db_onedev->query($sql);
//echo $this->db_onedev->last_query();
$rtn[] = $value['T_OrderDetailT_OrderHeaderID'];
}
echo join(", ",$rtn);
}
function billirubin_total(){
$rtn = [];
$tests = [900,901,902];
foreach ($tests as $val) {
$sql = "SELECT *
FROM (
SELECT GROUP_CONCAT(T_OrderDetailT_TestID) as exist,
T_TestID,
T_TestCode,
T_TestName,
T_TestSasCode,
T_TestIsResult,
T_TestIsPrice,
T_OrderDetailT_OrderHeaderID,
T_OrderDetailT_OrderDetailOrderID
FROM `t_orderdetail`
JOIN t_test ON T_TestID = ?
WHERE
T_OrderDetailT_TestID IN (899,900,901,902) AND T_OrderDetailIsActive = 'Y'
GROUP BY T_OrderDetailT_OrderHeaderID
) x
WHERE
exist = '899'";
$query = $this->db_onedev->query($sql,[$val]);
//if($val == 900)
//echo $this->db_onedev->last_query();
$datas = $query->result_array();
foreach ($datas as $key => $value) {
$sql = "INSERT INTO t_orderdetail(
T_OrderDetailT_OrderHeaderID,
T_OrderDetailT_OrderDetailOrderID,
T_OrderDetailT_TestID,
T_OrderDetailT_TestCode,
T_OrderDetailT_TestSasCode,
T_OrderDetailT_TestName,
T_OrderDetailT_TestIsResult,
T_OrderDetailT_TestIsPrice,
T_OrderDetailCreatedUserID,
T_OrderDetailCreated
)
VALUES(?,?,?,?,?,?,?,?,?,NOW())";
$query = $this->db_onedev->query($sql,array(
$value['T_OrderDetailT_OrderHeaderID'],
$value['T_OrderDetailT_OrderDetailOrderID'],
$value['T_TestID'],
$value['T_TestCode'],
$value['T_TestSasCode'],
$value['T_TestName'],
$value['T_TestIsResult'],
$value['T_TestIsPrice'],
270924
));
//if($key == 0)
//echo $this->db_onedev->last_query();
$rtn[] = $this->db_onedev->insert_id();
}
}
echo json_encode($rtn);
}
function tonometri_remove(){
$rtn = [];
$sql = "SELECT So_ResultEntryID, So_ResultEntryT_OrderHeaderID
FROM `t_orderdetail`
JOIN so_resultentry ON So_ResultEntryT_OrderHeaderID = T_OrderDetailT_OrderHeaderID AND
So_ResultEntryNonlab_TemplateID = 27
WHERE `T_OrderDetailT_TestName` LIKE '%tonometri%' AND `T_OrderDetailIsActive` = 'Y'";
$query = $this->db_onedev->query($sql);
if (!$query) {
//$this->db_onedev->trans_rollback();
echo $this->db_onedev->last_query();
$this->sys_error_db('error', 'Error get order: ' . $this->db_onedev);
exit;
}
$datas = $query->result_array();
foreach ($datas as $key => $value) {
$sql = "SELECT * FROM so_resultentry_fisik_umum WHERE So_ResultEntryFisikUmumSo_ResultEntryID = ?";
$query = $this->db_onedev->query($sql,[$value['So_ResultEntryID']]);
$xcount = $query->result_array();
if(count($xcount) == 1 && $xcount[0]['So_ResultEntryFisikUmumFisikTemplateID'] == 41){
$sql = "UPDATE so_resultentry_fisik_umum SET So_ResultEntryFisikUmumIsActive = 'N', So_ResultEntryFisikUmumDeleted = NOW(), So_ResultEntryFisikUmumDeletedUserID = 240924
WHERE So_ResultEntryFisikUmumID = ?";
$query = $this->db_onedev->query($sql,[$xcount[0]['So_ResultEntryFisikUmumID']]);
$rtn[] = $xcount[0]['So_ResultEntryFisikUmumID'];
}
}
echo join(", ",$rtn);
}
function generate_kelainan_lab_all($mgmmcuid,$limit){
$this->load->library('Etlfisik');
$sql = "SELECT *
FROM t_orderheader
WHERE
T_OrderHeaderMgm_McuID = ? AND T_OrderHeaderIsActive = 'Y' LIMIT ? ";
$query = $this->db_onedev->query($sql,array($mgmmcuid,intval($limit)));
//echo $this->db_onedev->last_query();
$datas = $query->result_array();
foreach ($datas as $key => $value) {
//$this->generate_kelainan_lab($value['T_OrderHeaderID']);
$userID = 1;
$this->etlfisik->generate_kelainan_lab($value['T_OrderHeaderID'],$userID);
}
}
function generate_all_kelainan_by_order(){
}
function fix_panel_satuan_lemak_update(){
$id_updates = [];
$id_inserts = [];
$sql = "SELECT T_OrderHeaderID, T_OrderDetailOrderID
FROM t_orderheader
JOIN t_orderdetailorder ON T_OrderDetailOrderT_OrderHeaderID = T_OrderHeaderID AND
T_OrderHeaderIsActive = 'Y'
JOIN t_packet ON T_OrderDetailOrderT_PacketID = T_PacketID AND T_PacketSasCode IN (
'PN2400193'
)
GROUP BY T_OrderHeaderID
";
$query = $this->db_onedev->query($sql);
//echo $this->db_onedev->last_query();
$datas = $query->result_array();
foreach ($datas as $key => $value) {
/*" SELECT *
FROM `t_orderdetail` WHERE `T_OrderDetailT_OrderHeaderID` = '10880' AND
`T_OrderDetailIsActive` = 'Y' AND `T_OrderDetailT_TestSasCode` LIKE '10520200%' "*/
$sql = "UPDATE t_orderdetail SET T_OrderDetailIsActive = 'N', T_OrderDetailDeleted = NOW(), T_OrderDetailDeletedUserID = 231077
WHERE T_OrderDetailT_OrderHeaderID = ? AND `T_OrderDetailT_TestSasCode` LIKE '10520200%' AND T_OrderDetailIsActive = 'Y'";
$query = $this->db_onedev->query($sql,array($value['T_OrderHeaderID']));
if (!$query) {
//$this->db_onedev->trans_rollback();
echo $this->db_onedev->last_query();
$this->sys_error_db('error', 'Error update : ' . $this->db_onedev);
exit;
}
$id_updates[] = $value['T_OrderHeaderID'];
$sql = "SELECT COUNT(*) as x FROM t_orderdetail WHERE T_OrderDetailT_OrderHeaderID = ? AND T_OrderDetailT_TestSasCode = '10520800' AND
T_OrderDetailIsActive = 'Y'";
$query = $this->db_onedev->query($sql,array($value['T_OrderHeaderID']));
$xcount = $query->row()->x;
if($xcount == 0){
$sql = "INSERT INTO t_orderdetail (
T_OrderDetailT_OrderHeaderID,
T_OrderDetailT_OrderDetailOrderID,
T_OrderDetailT_TestID,
T_OrderDetailT_TestCode,
T_OrderDetailT_TestSasCode,
T_OrderDetailT_TestName,
T_OrderDetailT_TestIsResult,
T_OrderDetailT_TestIsPrice,
T_OrderDetailPrice,
T_OrderDetailPriceForDisc,
T_OrderDetailTotal,
T_OrderDetailCreatedUserID,
T_OrderDetailCreated
)
VALUES(?,?,?,?,?,?,?,?,?,?,?,6677,NOW())";
$query = $this->db_onedev->query($sql,array(
$value['T_OrderHeaderID'],
$value['T_OrderDetailOrderID'],
"955",
'10520800',
'10520800',
'Cholesterol/HDL Chol. Ratio',
'Y',
'Y',
0,
0,
0
));
if (!$query) {
//$this->db_onedev->trans_rollback();
echo $this->db_onedev->last_query();
$this->sys_error_db('error', 'Error insert : ' . $this->db_onedev);
exit;
}
$id_inserts[] = $this->db_onedev->insert_id();
}
}
$rtn = array(
"id_inserts" => join(", ",$id_inserts),
"header_id_update" => join(", ",$id_updates)
);
echo json_encode($rtn);
}
function generate_kelainan_fisik_all($mgmmcuid,$limit){
$this->load->library('Etlfisik');
$sql = "SELECT *
FROM t_orderheader
JOIN so_resultentry ON So_ResultEntryT_OrderHeaderID = T_OrderHeaderID AND So_ResultEntryNonlab_TemplateID = 27 AND
So_ResultEntryIsActive = 'Y'
WHERE
T_OrderHeaderMgm_McuID = ? AND T_OrderHeaderIsActive = 'Y' LIMIT ? ";
$query = $this->db_onedev->query($sql,array($mgmmcuid,intval($limit)));
//echo $this->db_onedev->last_query();
$datas = $query->result_array();
foreach ($datas as $key => $value) {
$userID = 1;
$this->generate_etl_fisik($value['T_OrderHeaderID']);
$this->generate_summaries($value['T_OrderHeaderID'], $value['So_ResultEntryID'], $userID);
}
}
function get_kelainan_order($orderID,$userID){
$this->load->library('Etlfisik');
$rtn = $this->etlfisik->generate_kelainan_by_order($orderID,$userID);
echo json_encode($rtn);
}
function generate_kelainan_non_lab_all($mgmmcuid,$limit){
$this->load->library('Etlfisik');
$sql = "SELECT *
FROM t_orderheader
WHERE
T_OrderHeaderMgm_McuID = ? AND T_OrderHeaderIsActive = 'Y' LIMIT ? ";
$query = $this->db_onedev->query($sql,array($mgmmcuid,intval($limit)));
//echo $this->db_onedev->last_query();
$datas = $query->result_array();
foreach ($datas as $key => $value) {
//$this->generate_kelainan_lab($value['T_OrderHeaderID']);
$userID = 1;
$this->etlfisik->generate_kelainan_nonlab($value['T_OrderHeaderID'],$userID);
}
}
function inject_fix_gol_darah(){
$sql = "SELECT *
FROM t_orderdetail
WHERE
T_OrderDetailT_TestID = 292 AND T_OrderDetailIsActive = 'N'; ";
$query = $this->db_onedev->query($sql,array($mgmmcuid,intval($limit)));
//echo $this->db_onedev->last_query();
$datas = $query->result_array();
foreach ($datas as $key => $value) {
$sql = "INSERT INTO t_orderdetail (
T_OrderDetailT_OrderHeaderID,
T_OrderDetailT_OrderDetailOrderID,
T_OrderDetailT_TestID,
T_OrderDetailT_TestCode,
T_OrderDetailT_TestSasCode,
T_OrderDetailT_TestName,
T_OrderDetailPrice,
T_OrderDetailPriceForDisc,
T_OrderDetailTotal,
T_OrderDetailCreatedUserID,
T_OrderDetailCreated
)
VALUES(?,?,?,?,?,?,?,?,?,555,NOW())";
$query = $this->db_onedev->query($sql,array(
$value['T_OrderDetailT_OrderHeaderID'],
$value['T_OrderDetailT_OrderDetailOrderID'],
"288",
'10130100',
'10130100',
'Golongan Darah',
35000,
35000,
35000
));
$sql = "INSERT INTO t_orderdetail (
T_OrderDetailT_OrderHeaderID,
T_OrderDetailT_OrderDetailOrderID,
T_OrderDetailT_TestID,
T_OrderDetailT_TestCode,
T_OrderDetailT_TestSasCode,
T_OrderDetailT_TestName,
T_OrderDetailPrice,
T_OrderDetailPriceForDisc,
T_OrderDetailTotal,
T_OrderDetailCreatedUserID,
T_OrderDetailCreated
)
VALUES(?,?,?,?,?,?,?,?,?,555,NOW())";
$query = $this->db_onedev->query($sql,array(
$value['T_OrderDetailT_OrderHeaderID'],
$value['T_OrderDetailT_OrderDetailOrderID'],
"289",
'10130200',
'1013010001',
'Golongan Darah ABO System',
0,
0,
0
));
if (!$query) {
//$this->db_onedev->trans_rollback();
echo $this->db_onedev->last_query();
$this->sys_error_db('error', 'Error update : ' . $this->db_onedev);
exit;
}
$sql = "INSERT INTO t_orderdetail (
T_OrderDetailT_OrderHeaderID,
T_OrderDetailT_OrderDetailOrderID,
T_OrderDetailT_TestID,
T_OrderDetailT_TestCode,
T_OrderDetailT_TestSasCode,
T_OrderDetailT_TestName,
T_OrderDetailPrice,
T_OrderDetailPriceForDisc,
T_OrderDetailTotal,
T_OrderDetailCreatedUserID,
T_OrderDetailCreated
)
VALUES(?,?,?,?,?,?,?,?,?,555,NOW())";
$query = $this->db_onedev->query($sql,array(
$value['T_OrderDetailT_OrderHeaderID'],
$value['T_OrderDetailT_OrderDetailOrderID'],
"290",
'10130300',
'1013010002',
'Golongan Darah Rhesus',
0,
0,
0
));
if (!$query) {
//$this->db_onedev->trans_rollback();
echo $this->db_onedev->last_query();
$this->sys_error_db('error', 'Error insert : ' . $this->db_onedev);
exit;
}
}
}
function generate_etl_fisik($orderID){
$this->load->library('Etlfisik');
$userID = 1;
$this->etlfisik->generate_all_fisik($orderID,$userID);
}
function generate_summaries($orderID,$soReID,$userID,$debugID=0){
$this->load->library('Etlfisik');
$userID = 1;
$this->etlfisik->generate_summaries($orderID,$soReID,$userID,$debugID=0);
}
function generate_summaries_lab($orderID){
$this->load->library('Etlfisik');
$userID = 1;
$this->etlfisik->generate_kelainan_lab($orderID,$userID);
}
function replace_packet($labnumber,$oldpacket_code,$newpacket_code, $userid){
$sql = " SELECT *
FROM t_packet
JOIN mgm_mcupacket ON Mgm_McuPacketT_PacketID = T_PacketID AND Mgm_McuPacketIsActive = 'Y'
JOIN t_orderheader ON T_OrderHeaderMgm_McuID = Mgm_McuPacketMgm_McuID AND T_OrderHeaderLabNumber = ?
WHERE
T_PacketSasCode = ? AND T_PacketIsActive = 'Y' LIMIT 1";
$query = $this->db_onedev->query($sql,array($labnumber,$newpacket_code));
if (!$query) {
//$this->db_onedev->trans_rollback();
echo $this->db_onedev->last_query();
$this->sys_error_db('error', 'Error get order: ' . $this->db_onedev);
exit;
}
$data_new_packet = $query->row_array();
if($data_new_packet){
$sql = " SELECT *
FROM t_orderheader
JOIN t_orderdetailorder ON T_OrderDetailOrderT_OrderHeaderID = T_OrderHeaderID AND T_OrderDetailOrderIsActive = 'Y' AND
T_OrderDetailOrderIsPacket = 'Y'
JOIN t_packet ON T_PacketSasCode = ? AND T_OrderDetailOrderT_PacketID = T_PacketID
WHERE
T_OrderHeaderLabNumber = ? LIMIT 1";
$query = $this->db_onedev->query($sql,array($oldpacket_code,$labnumber));
//echo $this->db_onedev->last_query();
if (!$query) {
//$this->db_onedev->trans_rollback();
echo $this->db_onedev->last_query();
$this->sys_error_db('error', 'Error get order: ' . $this->db_onedev);
exit;
}
//echo $this->db_onedev->last_query();
$order = $query->row_array();
if($order){
$this->db_onedev->trans_begin();
$new_packet_id = $data_new_packet['T_PacketID'];
$old_packet_id = $order['T_PacketID'];
//INSERT NEW PACKET INTO ORDER DETAIL ORDER
$sql = "INSERT INTO t_orderdetailorder (
T_OrderDetailOrderT_OrderHeaderID,
T_OrderDetailOrderIsPacket,
T_OrderDetailOrderPacketType,
T_OrderDetailOrderT_PacketID,
T_OrderDetailOrderT_PacketName,
T_OrderDetailOrderCreated,
T_OrderDetailOrderCreatedUserID
)
VALUES(?,'Y',?,?,?,NOW(),?)";
$query = $this->db_onedev->query($sql,array(
$order['T_OrderHeaderID'],
$data_new_packet['T_PacketType'],
$data_new_packet['T_PacketID'],
$data_new_packet['T_PacketName'],
$userid
));
if (!$query) {
$this->db_onedev->trans_rollback();
echo $this->db_onedev->last_query();
$this->sys_error_db('error', 'Error insert new packet into orderdetail order: ' . $this->db_onedev);
exit;
}
$new_orderdetail_orderid = $this->db_onedev->insert_id();
$sql = "SELECT *
FROM t_packetdetail
JOIN t_test ON T_PacketDetailT_TestID = T_TestID
WHERE
T_PacketDetailT_PacketID = ? AND
T_PacketDetailIsActive = 'Y'";
$query = $this->db_onedev->query($sql,array($new_packet_id));
if (!$query) {
$this->db_onedev->trans_rollback();
echo $this->db_onedev->last_query();
$this->sys_error_db('error', 'Error get packet detail: ' . $this->db_onedev);
exit;
}
$packet_details = $query->result_array();
foreach ($packet_details as $key => $value) {
$data_detail = [];
$sql = "SELECT *
FROM t_orderdetail
WHERE
T_OrderDetailT_OrderHeaderID = ? AND
T_OrderDetailT_OrderDetailOrderID = ? AND
T_OrderDetailT_TestID = ? AND
T_OrderDetailIsActive = 'Y'";
$query = $this->db_onedev->query($sql,array($order['T_OrderHeaderID'],$order['T_OrderDetailOrderID'],$value['T_PacketDetailT_TestID']));
if (!$query) {
$this->db_onedev->trans_rollback();
echo $this->db_onedev->last_query();
$this->sys_error_db('error', 'Error get packet detail: ' . $this->db_onedev);
exit;
}
//echo $this->db_onedev->last_query();
$data_detail = $query->result_array();
$orderdetail_sascode = $value['T_TestSasCode']."%";
//echo "exist-order-lama-".count($data_detail);
if(count($data_detail) > 0){
// UPDATE order detail lama diganti orderdetailt_orderdetailorderid baru
$detail = $data_detail[0];
$sql = "UPDATE t_orderdetail SET
T_OrderDetailT_OrderDetailOrderID = ?,
T_OrderDetailLastUpdatedUserID = ?
WHERE
T_OrderDetailT_OrderDetailOrderID = ? AND
T_OrderDetailT_TestSasCode LIKE ?
";
$query = $this->db_onedev->query($sql,array(
$new_orderdetail_orderid,
$userid,
$order['T_OrderDetailOrderID'],
$orderdetail_sascode
));
if (!$query) {
$this->db_onedev->trans_rollback();
echo $this->db_onedev->last_query();
$this->sys_error_db('error', 'Error update order detail: ' . $this->db_onedev);
exit;
}
}else{
//insert ke orderdetail jika tidak ditemukan
$sql = "INSERT INTO t_orderdetail (
T_OrderDetailT_OrderHeaderID,
T_OrderDetailT_OrderDetailOrderID,
T_OrderDetailT_TestID,
T_OrderDetailT_TestCode,
T_OrderDetailT_TestSasCode,
T_OrderDetailT_TestName,
T_OrderDetailT_TestIsResult,
T_OrderDetailT_TestIsPanel,
T_OrderDetailT_TestIsPrice,
T_OrderDetailPrice,
T_OrderDetailTotal,
T_OrderDetailCreated,
T_OrderDetailCreatedUserID
)
SELECT ?,
?,
T_TestID,
T_TestCode,
T_TestSasCode,
T_TestName,
T_TestIsResult,
'N',
T_TestIsPrice,
if(T_TestSasCode = ?, ?,0),
if(T_TestSasCode = ?, ?,0),
NOW(),
?
FROM t_test
WHERE
T_TestSasCode LIKE ?
";
$query = $this->db_onedev->query($sql,array(
$order['T_OrderHeaderID'],
$new_orderdetail_orderid,
$value['T_TestSasCode'],
$value['T_PacketDetailPrice'],
$value['T_TestSasCode'],
$value['T_PacketDetailPrice'],
$userid,
$orderdetail_sascode
));
//echo $this->db_onedev->last_query();
if (!$query) {
$this->db_onedev->trans_rollback();
echo $this->db_onedev->last_query();
$this->sys_error_db('error', 'Error insert order detail: ' . $this->db_onedev);
exit;
}
}
}
//update isactive = N test lama yg tdk ada di paket baru
$sql = "UPDATE t_orderdetail SET T_OrderDetailIsActive = 'N', T_OrderDetailDeleted = NOW(), T_OrderDetailDeletedUserID = ?
WHERE
T_OrderDetailT_OrderDetailOrderID = ? AND T_OrderDetailIsActive = 'Y'";
$query = $this->db_onedev->query($sql,array(
$userid,
$order['T_OrderDetailOrderID']
));
if (!$query) {
$this->db_onedev->trans_rollback();
echo $this->db_onedev->last_query();
$this->sys_error_db('error', 'Error update isactive N order detail: ' . $this->db_onedev);
exit;
}
$sql = "UPDATE t_orderdetailorder SET T_OrderDetailOrderIsActive = 'N', T_OrderDetailOrderDeleted = NOW(), T_OrderDetailOrderDeletedUserID = ?
WHERE
T_OrderDetailOrderID = ?";
$query = $this->db_onedev->query($sql,array(
$userid,
$order['T_OrderDetailOrderID']
));
if (!$query) {
$this->db_onedev->trans_rollback();
echo $this->db_onedev->last_query();
$this->sys_error_db('error', 'Error update isactive N order detail order: ' . $this->db_onedev);
exit;
}
$sql = "SELECT T_SampleStationID, M_LocationID
FROM t_orderdetail
JOIN t_test ON T_OrderDetailT_TestID = T_TestID AND T_TestIsActive = 'Y'
JOIN t_sampletype ON T_TestT_SampleTypeID = T_SampleTypeID
JOIN t_bahan ON T_SampleTypeT_BahanID = T_BahanID
JOIN t_samplestation ON T_BahanT_SampleStationID = T_SampleStationID AND T_SampleStationIsActive = 'Y'
JOIN m_location ON M_LocationT_SampleStationID = T_SampleStationID AND M_LocationIsActive = 'Y'
WHERE
T_OrderDetailT_OrderHeaderID = ? AND T_OrderDetailIsActive = 'Y'
GROUP BY T_SampleStationID ";
$query = $this->db_onedev->query($sql,array(
$order['T_OrderHeaderID']
));
if (!$query) {
$this->db_onedev->trans_rollback();
echo $this->db_onedev->last_query();
$this->sys_error_db('error', 'Error get station: ' . $this->db_onedev);
exit;
}
$order_locations = $query->result_array();
if(count($order_locations) > 0){
$sql = "UPDATE t_order_location SET T_OrderLocationIsActive = 'X' WHERE T_OrderLocationT_OrderHeaderID = ? AND T_OrderLocationIsActive = 'Y'";
$query = $this->db_onedev->query($sql,array(
$order['T_OrderHeaderID']
));
foreach ($order_locations as $k => $v) {
$exist_order_location = [];
$sql = " SELECT *
FROM t_order_location
WHERE
T_OrderLocationT_OrderHeaderID = ? AND
T_OrderLocationT_SampleStationID= ? AND
T_OrderLocationM_LocationID = ? AND
T_OrderLocationIsActive = 'X' LIMIT 1";
$query = $this->db_onedev->query($sql,array(
$order['T_OrderHeaderID'],
$v['T_SampleStationID'],
$v['M_LocationID']
));
$dt_exist_order_location = $query->result_array();
if(count($dt_exist_order_location) > 0){
$exist_order_location = $dt_exist_order_location[0];
$sql = "UPDATE t_order_location SET T_OrderLocationIsActive = 'Y' WHERE T_OrderLocationID = ? ";
$query = $this->db_onedev->query($sql,array(
$exist_order_location['T_OrderLocationID']
));
}else{
$sql = "INSERT INTO t_order_location (
T_OrderLocationT_OrderHeaderID,
T_OrderLocationM_LocationID,
T_OrderLocationT_SampleStationID,
T_OrderLocationCreated,
T_OrderLocationUserID
)
VALUES(?,?,?,NOW(),?)";
$query = $this->db_onedev->query($sql,array(
$order['T_OrderHeaderID'],
$v['M_LocationID'],
$v['T_SampleStationID'],
$userid
));
}
}
$sql = "UPDATE t_order_location SET T_OrderLocationIsActive = 'N', T_OrderLocationUserID = ? WHERE T_OrderLocationT_OrderHeaderID = ? AND T_OrderLocationIsActive = 'X'";
$query = $this->db_onedev->query($sql,array(
$userid,
$order['T_OrderHeaderID']
));
}
$sql = "SELECT T_OrderHeaderLabNumber, T_TestID, T_SampleTypeID , T_SampleTypeSuffix, T_SampleStationIsNonLab, T_SampleStationID
FROM t_orderheader
JOIN t_orderdetail ON t_orderheaderid = t_orderdetailt_orderheaderid AND t_orderdetailIsActive = 'Y'
JOIN t_test ON t_orderdetailt_testid = t_testid AND T_TestIsResult = 'Y'
JOIN group_resultdetail ON Group_ResultDetailT_TestID = T_TestID AND Group_resultDetailIsActive = 'Y'
JOIN group_result ON Group_ResultDetailGroup_ResultID = Group_ResultID AND Group_ResultFlagNonLab= 'N'
JOIN t_sampletype ON T_TestT_SampleTypeID = T_SampleTypeID
JOIN t_bahan ON T_SampleTypeT_BahanID = T_BahanID
JOIN t_samplestation ON T_BahanT_SampleStationID = T_SampleStationID
WHERE T_OrderHeaderID = ?
GROUP BY T_SampleTypeID";
$query = $this->db_onedev->query($sql,array(
$order['T_OrderHeaderID']
));
$order_samples = $query->result_array();
if($order_samples){
$sql = "UPDATE t_ordersample SET T_OrderSampleIsActive = 'X'
WHERE
T_OrderSampleT_OrderHeaderID = ? AND T_OrderSampleIsActive = 'Y'";
$this->db_onedev->query($sql,array(
$order['T_OrderHeaderID']
));
foreach ($order_samples as $sample) {
$exist_sample = [];
$sql = "SELECT *
FROM t_ordersample
WHERE
T_OrderSampleT_OrderHeaderID = ? AND
T_OrderSampleT_SampleTypeID = ? AND
T_OrderSampleIsActive = 'X'";
$query = $this->db_onedev->query($sql,array(
$order['T_OrderHeaderID'],
$sample['T_SampleTypeID']
));
//echo $this->db_onedev->last_query();
$dt_exist_sample = $query->result_array();
if(count($dt_exist_sample) > 0){
$exist_sample = $dt_exist_sample[0];
$sql = "UPDATE t_ordersample SET T_OrderSampleIsActive = 'Y' WHERE T_OrderSampleID = ?";
$query = $this->db_onedev->query($sql,array(
$exist_sample['T_OrderSampleID']
));
//echo $this->db_onedev->last_query();
}else{
$barcode = $labnumber . $sample['T_SampleTypeSuffix']."1";
$sql = "INSERT INTO t_barcodelab(
T_BarcodeLabT_OrderHeaderID,
T_BarcodeLabBarcode,
T_BarcodeLabT_SampleTypeID,
T_BarcodeLabCounter,
T_BarcodeLabCreated,
T_BarcodeLabCreatedUserID)
VALUES (?,?,?,?,NOW(),?)";
$query = $this->db_onedev->query($sql,array(
$order['T_OrderHeaderID'],
$barcode,
$sample['T_SampleTypeID'],
"1",
$userid
));
$last_id_barcode = $this->db_onedev->insert_id();
$sql = "INSERT INTO t_ordersample(
T_OrderSampleT_OrderHeaderID,
T_OrderSampleT_SampleTypeID,
T_OrderSampleT_BarcodeLabID,
T_OrderSampleBarcode,
T_OrderSampleT_SampleStationID,
T_OrderSampleCreated,
T_OrderSampleCreatedUserID)
VALUES(
?,?,?,?,?,NOW(),?
)";
$query = $this->db_onedev->query($sql,array(
$order['T_OrderHeaderID'],
$sample['T_SampleTypeID'],
$last_id_barcode,
$barcode,
$sample['T_SampleStationID'],
$userid
));
//echo $this->db_onedev->last_query();
}
}
$sql = "UPDATE t_ordersample SET T_OrderSampleIsActive = 'N'
WHERE
T_OrderSampleT_OrderHeaderID = ? AND T_OrderSampleIsActive = 'X'";
$this->db_onedev->query($sql,array(
$order['T_OrderHeaderID']
));
}
$sql = "SELECT *
FROM t_orderdetail
JOIN t_test ON T_OrderDetailT_TestID = T_TestID
JOIN nonlab_template_mapping ON NonlabTemplateMappingNat_TestID = T_TestNat_TestID AND NonlabTemplateMappingIsActive = 'Y'
JOIN nonlab_template ON NonlabTemplateMappingNonlabTemplateID = NonlabTemplateID AND NonlabTemplateIsActive = 'Y'
JOIN group_resultdetail ON Group_ResultDetailT_TestID = T_TestID
JOIN group_result ON Group_ResultDetailGroup_ResultID = Group_ResultID AND Group_ResultFlagNonLab = 'Y'
WHERE
T_OrderDetailT_OrderHeaderID = {$order['T_OrderHeaderID']} AND T_OrderDetailIsActive = 'Y'
GROUP BY T_TestID";
// echo $sql;
$query = $this->db_onedev->query($sql);
$order_nonlab = $query->result_array();
if(count($order_nonlab) > 0){
$sql = "UPDATE so_resultentry SET So_ResultEntryIsActive = 'X'
WHERE
So_ResultEntryT_OrderHeaderID = ? AND So_ResultEntryIsActive = 'Y'";
$this->db_onedev->query($sql,array(
$order['T_OrderHeaderID']
));
foreach ($order_nonlab as $nonlab) {
$exist_so = [];
$sql = "SELECT *
FROM so_resultentry
WHERE
So_ResultEntryT_OrderHeaderID = ? AND
So_ResultEntryT_OrderDetailID = ? AND
So_ResultEntryIsActive = 'X'";
$query = $this->db_onedev->query($sql,array(
$order['T_OrderHeaderID'],
$nonlab['T_OrderDetailID']
));
$dt_exist_so = $query->result_array();
if(count($dt_exist_so) > 0){
$exist_so = $dt_exist_so[0];
$sql = "UPDATE so_resultentry SET So_ResultEntryIsActive = 'Y'
WHERE
So_ResultEntryID = ? AND So_ResultEntryIsActive = 'X'";
$this->db_onedev->query($sql,array(
$exist_so['So_ResultEntryID']
));
}
}
$sql = "UPDATE so_resultentry SET So_ResultEntryIsActive = 'N'
WHERE
So_ResultEntryT_OrderHeaderID = ? AND So_ResultEntryIsActive = 'X'";
$this->db_onedev->query($sql,array(
$order['T_OrderHeaderID']
));
}
$results = ["status" => true, "msg" => "Ganti Paket Berhasil"];
}else{
$results = ["status" => false, "msg" => "Gagal ganti paket, kode paket tidak ditemukan"];
}
$this->db_onedev->trans_commit();
}
echo json_encode($results);
}
function get_hostname(){
echo $_SERVER['SERVER_NAME'];
}
function fix_kultur_faeces(){
$sql = "SELECT T_OrderHeaderID, T_OrderDetailT_OrderDetailOrderID as detailorderid
FROM t_orderdetail
JOIN t_orderheader ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID AND
T_OrderHeaderMgm_McuID = 147 AND T_OrderDetailT_TestID = 1514 AND
DATE(T_OrderHeaderDate) < '2024-11-11'
GROUP BY T_OrderHeaderID";
$query = $this->db_onedev->query($sql);
$datas = $query->result_array();
foreach($datas as $k => $v){
$tests = [3487,3488,3489,3490,3491];
foreach ($tests as $value) {
$sql = "SELECT COUNT(*) as xcount
FROM t_orderdetail
WHERE
T_OrderDetailT_OrderHeaderID = ? AND T_OrderDetailIsActive = 'Y' AND
T_OrderDetailT_TestID = ?";
$query = $this->db_onedev->query($sql,array($v['T_OrderHeaderID'],$value));
$dt_exist = $query->row()->xcount;
if($dt_exist == 0){
$sql = "INSERT INTO t_orderdetail (
T_OrderDetailT_OrderHeaderID,
T_OrderDetailT_OrderDetailOrderID,
T_OrderDetailT_TestID,
T_OrderDetailT_TestCode,
T_OrderDetailT_TestSasCode,
T_OrderDetailT_TestName,
T_OrderDetailT_TestIsResult,
T_OrderDetailT_TestIsPrice,
T_OrderDetailCreated,
T_OrderDetailCreatedUserID
)
SELECT {$v['T_OrderHeaderID']},
{$v['detailorderid']},
T_TestID,
T_TestCode,
T_TestSasCode,
T_TestName,
T_TestIsResult,
T_TestIsPrice,
NOW(),
122467
FROM t_test
WHERE T_TestID = ?";
$query = $this->db_onedev->query($sql,array($value));
}
}
}
}
}