fajri/BE_CPONE
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
BE_CPONE/application/controllers/v1/su/Test.php--270225
sas.fajri bf9b9097ee Initial import
2026-04-27 10:26:26 +07:00

2385 lines
111 KiB
Plaintext
Raw Permalink Blame History

<?php
class Test extends CI_Controller
{
public function __construct()
{
parent::__construct();
$this->db_onedev = $this->load->database("onedev", true);
}
function index() {
$this->load->library("Resultcalc");
$id = 946;
$rows = $this->resultcalc->auto($id);
echo "<pre> $rows";
}
function add_non_hdl_to_packets() {
// Get Non HDL test ID first
$sql = "SELECT T_TestID FROM t_test WHERE T_TestSasCode = '10521504' AND T_TestIsActive = 'Y'";
$non_hdl_test = $this->db_onedev->query($sql)->row_array();
if (!$non_hdl_test) {
return "Error: Non HDL test not found";
}
// Get all packets with both Cholesterol and HDL
$sql = "SELECT DISTINCT p.T_PacketID, p.T_PacketName, p.T_PacketT_PriceHeaderID
FROM t_packet p
JOIN t_packetdetail pd1 ON p.T_PacketID = pd1.T_PacketDetailT_PacketID AND pd1.T_PacketDetailIsActive = 'Y'
JOIN t_packetdetail pd2 ON p.T_PacketID = pd2.T_PacketDetailT_PacketID AND pd2.T_PacketDetailIsActive = 'Y'
JOIN t_test t1 ON pd1.T_PacketDetailT_TestID = t1.T_TestID AND t1.T_TestIsActive = 'Y'
JOIN t_test t2 ON pd2.T_PacketDetailT_TestID = t2.T_TestID AND t2.T_TestIsActive = 'Y'
WHERE t1.T_TestSasCode = '10520300'
AND t2.T_TestSasCode = '10520500'
AND p.T_PacketIsActive = 'Y'
AND p.T_PacketID = 85";
$eligible_packets = $this->db_onedev->query($sql)->result_array();
$added_count = 0;
$skipped_count = 0;
$updated_packets = [];
foreach ($eligible_packets as $packet) {
// Check if Non HDL already exists in this packet
$sql = "SELECT COUNT(*) as count
FROM t_packetdetail
WHERE T_PacketDetailT_PacketID = ?
AND T_PacketDetailT_TestID = ?
AND T_PacketDetailIsActive = 'Y'";
$exists = $this->db_onedev->query($sql, array(
$packet['T_PacketID'],
$non_hdl_test['T_TestID']
))->row()->count;
if ($exists > 0) {
$skipped_count++;
continue;
}
// Get price from t_price table
$sql = "SELECT T_PriceTotal
FROM t_price
WHERE T_PriceT_PriceHeaderID = ?
AND T_PriceT_TestID = ?
AND T_PriceIsActive = 'Y'";
$price_data = $this->db_onedev->query($sql, array(
$packet['T_PacketT_PriceHeaderID'],
$non_hdl_test['T_TestID']
))->row_array();
// If price doesn't exist, insert it
if (!$price_data) {
$sql = "INSERT INTO t_price (
T_PriceT_PriceHeaderID,
T_PriceT_TestID,
T_PriceIsCito,
T_PricePriority,
T_PriceAmount,
T_PriceDisc,
T_PriceDiscRp,
T_PriceSubTotal,
T_PriceOther,
T_PriceTotal,
T_PriceCreated,
T_PriceUserID
) VALUES (?, ?, 'N', 1, 0, 0, 0, 0, 0, 0, NOW(), 1415)";
$this->db_onedev->query($sql, array(
$packet['T_PacketT_PriceHeaderID'],
$non_hdl_test['T_TestID']
));
$price = 0;
} else {
$price = $price_data['T_PriceTotal'];
}
// Add Non HDL to packet
$sql = "INSERT INTO t_packetdetail (
T_PacketDetailT_PacketID,
T_PacketDetailT_TestID,
T_PacketDetailOriginalPrice,
T_PacketDetailPrice,
T_PacketDetailCreated
) VALUES (?, ?, ?, ?, NOW())";
$this->db_onedev->query($sql, array(
$packet['T_PacketID'],
$non_hdl_test['T_TestID'],
$price,
0
));
// Insert into temp_validation_price if not exists
$sql = "INSERT IGNORE INTO temp_validation_price (
T_PriceHeaderID,
status
) VALUES (?, 'N')";
$this->db_onedev->query($sql, array(
$packet['T_PacketT_PriceHeaderID']
));
$added_count++;
$updated_packets[] = $packet['T_PacketName'];
}
$rtn = array(
'status' => 'success',
'added_to_packets' => $added_count,
'skipped_packets' => $skipped_count,
'updated_packet_names' => $updated_packets
);
echo json_encode($rtn);
}
function fix_duplicate_test_orders($labnumber) {
// Get orders with duplicate tests
$sql = "SELECT T_OrderDetailT_OrderHeaderID, T_OrderDetailT_TestID, COUNT(*) as count
FROM t_orderdetail
JOIN t_orderheader ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y' AND T_OrderHeaderLabNumber = ?
WHERE T_OrderDetailIsActive = 'Y'
GROUP BY T_OrderDetailT_OrderHeaderID, T_OrderDetailT_TestID
HAVING count > 1";
$duplicates = $this->db_onedev->query($sql,array($labnumber))->result_array();
//echo $this->db_onedev->last_query();
//exit;
$updated_records = [];
if(count($duplicates) == 0){
echo 'No data found '.$labnumber.'<br>';
exit;
}
foreach ($duplicates as $duplicate) {
// Get all active records for this order/test combination
$sql = "SELECT T_OrderDetailID, T_OrderDetailResult
FROM t_orderdetail
WHERE T_OrderDetailT_OrderHeaderID = ?
AND T_OrderDetailT_TestID = ?
AND T_OrderDetailIsActive = 'Y'
ORDER BY T_OrderDetailID";
$records = $this->db_onedev->query($sql, array(
$duplicate['T_OrderDetailT_OrderHeaderID'],
$duplicate['T_OrderDetailT_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);
$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();
echo 'OK '.$labnumber.'<br>';
}
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'].'<br>';
}
}
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'].'<br>';
}
}
;
}
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){
$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 = 'N',
So_ResultEntryFisikUmumDeletedUserID = 1417,
So_ResultEntryFisikUmumDeleted = NOW()
WHERE So_ResultEntryFisikUmumSo_ResultEntryID = {$dt_so_resultentry[0]['So_ResultEntryID']}";
$this->db_onedev->query($sql);
$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'";
$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) {
$sql = "INSERT INTO so_resultentry_fisik_umum (
So_ResultEntryFisikUmumSo_ResultEntryID,
So_ResultEntryFisikUmumFisikTemplateID,
So_ResultEntryFisikUmumDetails,
So_ResultEntryFisikUmumCreated,
So_ResultEntryFisikUmumCreatedUserID
) VALUES (
?,
?,
?,
NOW(),
1417
)";
$this->db_onedev->query($sql,array(
$dt_so_resultentry[0]['So_ResultEntryID'],
$v_fisik_template_mapping_detail['FisikTemplateMappingDetailFisikTemplateID'],
$v_fisik_template_mapping_detail['FisikTemplateJSON']));
}
}
}
}
echo 'OK '.$labnumber.'<br>';
}
function additional_fisik_inject(){
$sql = "SELECT *
FROM `inject_price`
WHERE `TEST_NAME` = 'Laporan Pemeriksaan Kesehatan 7' AND `NAMA_PAKET` <> 'Optional' AND
ID_COMPANY NOT IN ('00000223','00000308')";
$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`,
`BEGIN_DATE`,
`EXPIRY_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['BEGIN_DATE']}',
'{$v_inject_price['EXPIRY_DATE']}'
)";
$this->db_onedev->query($sql);
echo $this->db_onedev->last_query();
}
}
print_r($v_inject_price);
}
}
}
function create_ss_price($userid=54712,$price_header_id=0){
$this->load->library("SsPriceMou");
$sql = "SELECT * FROM `t_priceheader` WHERE `T_PriceHeaderCraetdUserID` = ? AND `T_PriceHeaderIsActive` = 'Y' AND `T_PriceHeaderValidasi` = 'N'";
$dt_price_header = $this->db_onedev->query($sql,array($userid))->result_array();
//echo $this->db_onedev->last_query();
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
AND T_PacketIsGenerated= 'N'";
$query = $this->db_onedev->query($sql);
$this->sspricemou->create($id);
echo $v_price_header['T_PriceHeaderCode'].'|'.$v_price_header['T_PriceHeaderName'].'<br>';
}
}
}
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_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) {
$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();
echo $this->db_onedev->last_query();
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(),
54712
)";
$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'].'<br>';
$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 = "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(),
'2024-12-31',
'2025-12-31',
54712
)";
$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'].'<br>';
$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(),
54712
)";
//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'].'<br>';
$sql = "INSERT INTO mgm_mcutemplate(
Mgm_McuTemplateMgm_McuID,
Mgm_McuTemplateFisikTemplateMappingID,
Mgm_McuTemplateCreated,
Mgm_McuTemplateCreatedUserID
)
VALUES(
{$mgm_mcuID},
5,
NOW(),
575
)";
$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(),
54712
)";
$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'].'<br>';
}
}
if(count($dt_price) > 0){
$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['BEGIN_DATE']}',
'{$v_inject_price['EXPIRY_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'].'<br>';
$sql = "INSERT INTO mgm_mcupacket(
Mgm_McuPacketMgm_McuID,
Mgm_McuPacketT_PacketID,
Mgm_McuPacketCreated,
Mgm_McuPacketCreatedUserID
)
VALUES(
{$mgm_mcuID},
{$dt_packet[0]['T_PacketID']},
NOW(),
54712
)";
$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'].'<br>';
$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'].'<br>';
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(),
54712
)";
$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'].'<br>';
}
}
}
}
}
}
}
}
function fix_order_location($labnumber){
$sql = "SELECT * FROM t_orderheader WHERE T_OrderHeaderLabNumber = '{$labnumber}'";
$dt = $this->db_onedev->query($sql)->row_array();
$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 = '{$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);
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();
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 = ? ";
$query = $this->db_onedev->query($sql,array(
$exist_order_location['T_OrderLocationID']
));
//echo $this->db_onedev->last_query();
}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
));
//echo $this->db_onedev->last_query();
}
}
$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 "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,$id){
$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
$params['data'] = "http://devcpone.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'] = $config['imagedir'].$image_name; //simpan image QR CODE ke folder assets/images/
$this->ciqrcode->generate($params); // fungsi untuk generate QR CODE
echo "http://devcpone.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 <br/>";
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));
}
}
}
}
}
Reference in New Issue View Git Blame Copy Permalink