2457 lines
99 KiB
PHP
2457 lines
99 KiB
PHP
<?php
|
|
class Generatedata extends MY_Controller
|
|
{
|
|
public function __construct()
|
|
{
|
|
parent::__construct();
|
|
$this->db = $this->load->database("onedev", true);
|
|
$this->db_corporate = $this->load->database("cpone_corporate", true);
|
|
$this->db_dashboard = $this->load->database("cpone_dashboard", true);
|
|
$this->db_log = $this->load->database("log", true);
|
|
}
|
|
|
|
function generate_code_string(){
|
|
$length = 10;
|
|
$characters = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
|
|
|
|
do {
|
|
$code = '';
|
|
for ($i = 0; $i < $length; $i++) {
|
|
$code .= $characters[rand(0, strlen($characters) - 1)];
|
|
}
|
|
|
|
// Check if code already exists in mcu_number table
|
|
$sql = "SELECT COUNT(*) as count FROM mcu_number WHERE Mcu_NumberTableName = ?";
|
|
$query = $this->db_corporate->query($sql, [$code]);
|
|
$result = $query->row_array();
|
|
$exists = $result['count'] > 0;
|
|
|
|
} while ($exists);
|
|
|
|
return $code;
|
|
}
|
|
|
|
private function dashboard_table_exists($table_name)
|
|
{
|
|
return $this->db_dashboard->table_exists($table_name);
|
|
}
|
|
|
|
private function dashboard_column_exists($table_name, $column_name)
|
|
{
|
|
$query = $this->db_dashboard->query("SHOW COLUMNS FROM `{$table_name}` LIKE ?", [$column_name]);
|
|
return $query && $query->num_rows() > 0;
|
|
}
|
|
|
|
private function ensure_dashboard_table_structures()
|
|
{
|
|
if (!$this->dashboard_table_exists('mcu_generate')) {
|
|
$sql = "CREATE TABLE IF NOT EXISTS `mcu_generate` (
|
|
`Mcu_GenerateID` BIGINT NOT NULL AUTO_INCREMENT,
|
|
`Mgm_McuID` INT NOT NULL,
|
|
`Mcu_ProjectID` INT DEFAULT NULL,
|
|
`GenerateDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`Mcu_GenerateID`),
|
|
KEY `idx_mgm_mcuid` (`Mgm_McuID`),
|
|
KEY `idx_project_id` (`Mcu_ProjectID`),
|
|
KEY `idx_generate_date` (`GenerateDate`)
|
|
) ENGINE=InnoDB";
|
|
$query = $this->db_dashboard->query($sql);
|
|
if (!$query) {
|
|
$this->sys_error_db("create table mcu_generate", $this->db_dashboard);
|
|
exit;
|
|
}
|
|
}
|
|
|
|
$targetTables = ['kelainan_details', 'kelainan_summary', 'mcu_result_all'];
|
|
foreach ($targetTables as $tableName) {
|
|
if (!$this->dashboard_table_exists($tableName)) {
|
|
$query = $this->db_dashboard->query("CREATE TABLE IF NOT EXISTS `{$tableName}` LIKE `cpone_corporate`.`{$tableName}`");
|
|
if (!$query) {
|
|
$this->sys_error_db("create dashboard table {$tableName}", $this->db_dashboard);
|
|
exit;
|
|
}
|
|
}
|
|
|
|
if (!$this->dashboard_column_exists($tableName, 'Mgm_McuID')) {
|
|
$query = $this->db_dashboard->query("ALTER TABLE `{$tableName}` ADD COLUMN `Mgm_McuID` INT NULL");
|
|
if (!$query) {
|
|
$this->sys_error_db("add column Mgm_McuID on {$tableName}", $this->db_dashboard);
|
|
exit;
|
|
}
|
|
}
|
|
|
|
if (!$this->dashboard_column_exists($tableName, 'Mcu_GenerateID')) {
|
|
$query = $this->db_dashboard->query("ALTER TABLE `{$tableName}` ADD COLUMN `Mcu_GenerateID` BIGINT NULL");
|
|
if (!$query) {
|
|
$this->sys_error_db("add column Mcu_GenerateID on {$tableName}", $this->db_dashboard);
|
|
exit;
|
|
}
|
|
}
|
|
|
|
if (!$this->dashboard_column_exists($tableName, 'Mcu_ProjectID')) {
|
|
$query = $this->db_dashboard->query("ALTER TABLE `{$tableName}` ADD COLUMN `Mcu_ProjectID` INT NULL");
|
|
if (!$query) {
|
|
$this->sys_error_db("add column Mcu_ProjectID on {$tableName}", $this->db_dashboard);
|
|
exit;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
private function resolve_dashboard_project_id($mgm_mcuid)
|
|
{
|
|
if (!$this->dashboard_table_exists('mcu_project')) {
|
|
return null;
|
|
}
|
|
|
|
$columns = $this->db_dashboard->query("SHOW COLUMNS FROM `mcu_project`")->result_array();
|
|
if (count($columns) === 0) {
|
|
return null;
|
|
}
|
|
|
|
$colNames = array_map(function($col) {
|
|
return $col['Field'];
|
|
}, $columns);
|
|
|
|
$projectIdCol = null;
|
|
foreach (['Mcu_ProjectID', 'mcu_project_id', 'ProjectID', 'id'] as $candidate) {
|
|
if (in_array($candidate, $colNames, true)) {
|
|
$projectIdCol = $candidate;
|
|
break;
|
|
}
|
|
}
|
|
if ($projectIdCol === null) {
|
|
return null;
|
|
}
|
|
|
|
$mgmCol = null;
|
|
foreach (['Mgm_McuID', 'Mcu_ProjectMcuID', 'Mcu_ProjectMgm_McuID', 'McuProjectMgmMcuID', 'mgm_mcuid', 'mgm_mcu_id'] as $candidate) {
|
|
if (in_array($candidate, $colNames, true)) {
|
|
$mgmCol = $candidate;
|
|
break;
|
|
}
|
|
}
|
|
if ($mgmCol === null) {
|
|
return null;
|
|
}
|
|
|
|
$sql = "SELECT `{$projectIdCol}` AS project_id
|
|
FROM `mcu_project`
|
|
WHERE `{$mgmCol}` = ?
|
|
ORDER BY `{$projectIdCol}` DESC
|
|
LIMIT 1";
|
|
$query = $this->db_dashboard->query($sql, [$mgm_mcuid]);
|
|
if (!$query || $query->num_rows() === 0) {
|
|
return null;
|
|
}
|
|
|
|
return $query->row_array()['project_id'];
|
|
}
|
|
|
|
private function sync_to_dashboard($mgm_mcuid)
|
|
{
|
|
$mgm_mcuid = (int)$mgm_mcuid;
|
|
if ($mgm_mcuid <= 0) {
|
|
$this->sys_error("Mgm_McuID is required for dashboard sync");
|
|
exit;
|
|
}
|
|
|
|
$this->ensure_dashboard_table_structures();
|
|
|
|
$setupQuery = $this->db_corporate->query(
|
|
"SELECT * FROM mcu_number WHERE Mcu_NumberMgm_McuID = ? AND Mcu_NumberIsActive = 'Y' ORDER BY Mcu_NumberID DESC LIMIT 1",
|
|
[$mgm_mcuid]
|
|
);
|
|
if (!$setupQuery || $setupQuery->num_rows() === 0) {
|
|
$this->sys_error("MGM MCU not found for dashboard sync");
|
|
exit;
|
|
}
|
|
$setup = $setupQuery->row_array();
|
|
$numbering = $setup['Mcu_NumberTableName'];
|
|
|
|
$mcu_project_id = $this->resolve_dashboard_project_id($mgm_mcuid);
|
|
|
|
$this->db_dashboard->trans_begin();
|
|
|
|
$insertGenerate = $this->db_dashboard->query(
|
|
"INSERT INTO mcu_generate (Mgm_McuID, Mcu_ProjectID, GenerateDate) VALUES (?, ?, NOW())",
|
|
[$mgm_mcuid, $mcu_project_id]
|
|
);
|
|
if (!$insertGenerate) {
|
|
$this->db_dashboard->trans_rollback();
|
|
$this->sys_error_db("insert mcu_generate", $this->db_dashboard);
|
|
exit;
|
|
}
|
|
$generate_id = $this->db_dashboard->insert_id();
|
|
|
|
$query = $this->db_dashboard->query("DELETE FROM kelainan_details WHERE Mgm_McuID = ?", [$mgm_mcuid]);
|
|
if (!$query) {
|
|
$this->db_dashboard->trans_rollback();
|
|
$this->sys_error_db("delete dashboard kelainan_details", $this->db_dashboard);
|
|
exit;
|
|
}
|
|
|
|
$query = $this->db_dashboard->query("DELETE FROM kelainan_summary WHERE Mcu_KelainanMgm_McuID = ? OR Mgm_McuID = ?", [$mgm_mcuid, $mgm_mcuid]);
|
|
if (!$query) {
|
|
$this->db_dashboard->trans_rollback();
|
|
$this->sys_error_db("delete dashboard kelainan_summary", $this->db_dashboard);
|
|
exit;
|
|
}
|
|
|
|
$query = $this->db_dashboard->query("DELETE FROM mcu_result_all WHERE Mcu_ResultAllMgm_McuID = ? OR Mgm_McuID = ?", [$mgm_mcuid, $mgm_mcuid]);
|
|
if (!$query) {
|
|
$this->db_dashboard->trans_rollback();
|
|
$this->sys_error_db("delete dashboard mcu_result_all", $this->db_dashboard);
|
|
exit;
|
|
}
|
|
|
|
$query = $this->db_dashboard->query(
|
|
"INSERT INTO kelainan_details
|
|
SELECT kd.*, ?, ?, ?
|
|
FROM cpone_corporate.kelainan_details kd
|
|
WHERE kd.Numbering = ?",
|
|
[$mgm_mcuid, $generate_id, $mcu_project_id, $numbering]
|
|
);
|
|
if (!$query) {
|
|
$this->db_dashboard->trans_rollback();
|
|
$this->sys_error_db("insert dashboard kelainan_details", $this->db_dashboard);
|
|
exit;
|
|
}
|
|
|
|
$query = $this->db_dashboard->query(
|
|
"INSERT INTO kelainan_summary
|
|
SELECT ks.*, ?, ?, ?
|
|
FROM cpone_corporate.kelainan_summary ks
|
|
WHERE ks.Mcu_KelainanMgm_McuID = ?",
|
|
[$mgm_mcuid, $generate_id, $mcu_project_id, $mgm_mcuid]
|
|
);
|
|
if (!$query) {
|
|
$this->db_dashboard->trans_rollback();
|
|
$this->sys_error_db("insert dashboard kelainan_summary", $this->db_dashboard);
|
|
exit;
|
|
}
|
|
|
|
$query = $this->db_dashboard->query(
|
|
"INSERT INTO mcu_result_all
|
|
SELECT mra.*, ?, ?, ?
|
|
FROM cpone_corporate.mcu_result_all mra
|
|
WHERE mra.Mcu_ResultAllMgm_McuID = ?",
|
|
[$mgm_mcuid, $generate_id, $mcu_project_id, $mgm_mcuid]
|
|
);
|
|
if (!$query) {
|
|
$this->db_dashboard->trans_rollback();
|
|
$this->sys_error_db("insert dashboard mcu_result_all", $this->db_dashboard);
|
|
exit;
|
|
}
|
|
|
|
$this->db_dashboard->trans_commit();
|
|
|
|
return array(
|
|
"Mcu_GenerateID" => $generate_id,
|
|
"Mcu_ProjectID" => $mcu_project_id,
|
|
"Mgm_McuID" => $mgm_mcuid
|
|
);
|
|
}
|
|
|
|
function generate_table_setup(){
|
|
/*if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}*/
|
|
|
|
$prm = $this->sys_input;
|
|
$mgm_mcuid = $prm['Mgm_McuID'];
|
|
|
|
$userid = 0;
|
|
|
|
$sql = "SELECT *
|
|
FROM mgm_mcu
|
|
JOIN corporate ON CorporateID = Mgm_McuCorporateID
|
|
WHERE
|
|
Mgm_McuID = ? AND
|
|
Mgm_McuIsActive = 'Y'
|
|
LIMIT 1";
|
|
$query = $this->db->query($sql, [$mgm_mcuid]);
|
|
|
|
$data = $query->result_array();
|
|
if(count($data) > 0){
|
|
|
|
$sql = "SELECT COUNT(*) as total
|
|
FROM t_orderheader
|
|
WHERE
|
|
T_OrderHeaderMgm_McuID = ? AND
|
|
T_OrderHeaderIsActive = 'Y'";
|
|
$query = $this->db->query($sql, [$mgm_mcuid]);
|
|
$result = $query->row_array();
|
|
$total_participants = $result['total'];
|
|
|
|
$code = $this->generate_code_string();
|
|
|
|
$sql = "SELECT COUNT(*) as total
|
|
FROM `mcu_number`
|
|
WHERE Mcu_NumberMgm_McuID = ? AND Mcu_NumberIsActive = 'Y'";
|
|
$query = $this->db_corporate->query($sql, [$mgm_mcuid]);
|
|
$result = $query->row_array();
|
|
$total_mcu_number = $result['total'];
|
|
|
|
if($total_mcu_number > 0){
|
|
$this->sys_error("MGM MCU already has a table setup");
|
|
exit;
|
|
}
|
|
|
|
$sql = "INSERT INTO `mcu_number` (
|
|
`Mcu_NumberMgm_McuID`,
|
|
`Mcu_NumberMgm_McuNumber`,
|
|
`Mcu_NumberTableName`,
|
|
`Mcu_NumberStartDate`,
|
|
`Mcu_NumberEndDate`,
|
|
`Mcu_NumberTotalParticipants`,
|
|
`Mcu_NumberCorporateCode`,
|
|
`Mcu_NumberCorporateName`,
|
|
`Mcu_NumberCorporateAddress`,
|
|
`Mcu_NumberCreated`,
|
|
`Mcu_NumberCreatedUserID`
|
|
) VALUES(?,?,?,?,?,?,?,?,?,NOW(),?)";
|
|
$query = $this->db_corporate->query($sql, [
|
|
$data[0]['Mgm_McuID'],
|
|
$data[0]['Mgm_McuNumber'],
|
|
$code,
|
|
$data[0]['Mgm_McuStartDate'],
|
|
$data[0]['Mgm_McuEndDate'],
|
|
$total_participants,
|
|
$data[0]['CorporateCode'],
|
|
$data[0]['CorporateName'],
|
|
$data[0]['CorporateAddress'],
|
|
$userid
|
|
]);
|
|
if(!$query){
|
|
$this->sys_error_db("save table setup", $this->db_corporate);
|
|
exit;
|
|
}else{
|
|
$result = array("code" => $code, "sql"=> '', 'message' => 'Table setup generated successfully');
|
|
$this->sys_ok($result);
|
|
exit;
|
|
}
|
|
}else{
|
|
$this->sys_error("MGM MCU not found");
|
|
exit;
|
|
}
|
|
}
|
|
|
|
function generate_kelainan_lab()
|
|
{
|
|
/*if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}*/
|
|
|
|
$prm = $this->sys_input;
|
|
$mgm_mcuid = $prm['Mgm_McuID'];
|
|
|
|
$sql = "SELECT * FROM mcu_number WHERE Mcu_NumberMgm_McuID = ? AND Mcu_NumberIsActive = 'Y' ORDER BY Mcu_NumberID DESC LIMIT 1";
|
|
$query = $this->db_corporate->query($sql, [$mgm_mcuid]);
|
|
if(!$query){
|
|
$this->sys_error_db("select data mcu_number", $this->db_corporate);
|
|
//echo $this->db_corporate->error_message();
|
|
echo $this->db_corporate->last_query();
|
|
exit;
|
|
}
|
|
$data_setup = $query->result_array();
|
|
if(count($data_setup) == 0){
|
|
$this->sys_error("MGM MCU not found");
|
|
exit;
|
|
}
|
|
//print_r($data_setup); exit;
|
|
|
|
$numbering_column = $data_setup[0]['Mcu_NumberTableName'];
|
|
|
|
$table_name = "kelainan_details";
|
|
$exist_table = $this->db_corporate->table_exists($table_name);
|
|
|
|
$this->db_corporate->trans_begin();
|
|
|
|
if(!$exist_table){
|
|
$sql = "CREATE TABLE IF NOT EXISTS `{$table_name}` (
|
|
Tx_KelainanID INT,
|
|
Tx_Type VARCHAR(10),
|
|
T_OrderHeaderID INT,
|
|
T_OrderHeaderDate DATETIME,
|
|
T_OrderHeaderLabNumber VARCHAR(100),
|
|
AgePatient INT,
|
|
M_PatientID INT,
|
|
M_PatientNoReg VARCHAR(50),
|
|
M_PatientDOB DATE,
|
|
M_PatientGender VARCHAR(10),
|
|
M_PatientIdentifierValue VARCHAR(100),
|
|
M_PatientNIP VARCHAR(50),
|
|
M_PatientJob VARCHAR(100),
|
|
M_PatientPosisi VARCHAR(100),
|
|
M_PatientDivisi VARCHAR(100),
|
|
M_PatientLocation VARCHAR(100),
|
|
M_PatientDepartement VARCHAR(100),
|
|
PatientName VARCHAR(255),
|
|
M_PatientName VARCHAR(255),
|
|
GroupResult VARCHAR(100),
|
|
Nat_TestID INT,
|
|
Nat_TestCode VARCHAR(50),
|
|
Nat_TestName VARCHAR(255),
|
|
T_TestName VARCHAR(255),
|
|
Result VARCHAR(255),
|
|
Mcu_KelainanID INT,
|
|
Mcu_KelainanName VARCHAR(255),
|
|
Mcu_KelainanGroupSummaryID INT,
|
|
Mcu_KelainanGroupSummaryName VARCHAR(255),
|
|
Mcu_FitnessCategoryID INT,
|
|
Mcu_FitnessCategoryName VARCHAR(100),
|
|
Mcu_FitnessCategoryEng VARCHAR(100),
|
|
Mcu_FitnessCategoryLevel INT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_kelainan_lab (Tx_KelainanID),
|
|
INDEX idx_order_header (T_OrderHeaderID),
|
|
INDEX idx_order_lab_number (T_OrderHeaderLabNumber),
|
|
INDEX idx_patient_id (M_PatientID),
|
|
INDEX idx_patient_noreg (M_PatientNoReg),
|
|
INDEX idx_patient_location (M_PatientLocation),
|
|
INDEX idx_patient_posisi (M_PatientPosisi),
|
|
INDEX idx_patient_divisi (M_PatientDivisi),
|
|
INDEX idx_patient_departement (M_PatientDepartement),
|
|
INDEX idx_test (Nat_TestID),
|
|
INDEX idx_kelainan (Mcu_KelainanID),
|
|
INDEX idx_group_result (GroupResult),
|
|
INDEX idx_numbering (Numbering)
|
|
) ENGINE=InnoDB";
|
|
$query = $this->db_corporate->query($sql);
|
|
|
|
if(!$query){
|
|
$this->db_corporate->trans_rollback();
|
|
$this->sys_error_db("create table kelainan_details", $this->db_corporate);
|
|
exit;
|
|
}
|
|
}
|
|
|
|
$sql = "INSERT INTO `cpone_corporate`.`{$table_name}` (
|
|
Numbering,
|
|
Tx_KelainanID,
|
|
Tx_Type,
|
|
T_OrderHeaderID,
|
|
T_OrderHeaderDate,
|
|
T_OrderHeaderLabNumber,
|
|
AgePatient,
|
|
M_PatientID,
|
|
M_PatientNoReg,
|
|
M_PatientDOB,
|
|
M_PatientGender,
|
|
M_PatientIdentifierValue,
|
|
M_PatientNIP,
|
|
M_PatientJob,
|
|
M_PatientPosisi,
|
|
M_PatientDivisi,
|
|
M_PatientLocation,
|
|
M_PatientDepartement,
|
|
PatientName,
|
|
M_PatientName,
|
|
GroupResult,
|
|
Nat_TestID,
|
|
Nat_TestCode,
|
|
Nat_TestName,
|
|
T_TestName,
|
|
Result,
|
|
Mcu_KelainanID,
|
|
Mcu_KelainanName,
|
|
Mcu_KelainanGroupSummaryID,
|
|
Mcu_KelainanGroupSummaryName,
|
|
Mcu_FitnessCategoryID,
|
|
Mcu_FitnessCategoryName,
|
|
Mcu_FitnessCategoryEng,
|
|
Mcu_FitnessCategoryLevel
|
|
)
|
|
SELECT
|
|
'{$numbering_column}',
|
|
T_KelainanLabID as Tx_KelainanID,
|
|
'LAB' as Tx_Type,
|
|
T_OrderHeaderID,
|
|
T_OrderHeaderDate,
|
|
T_OrderHeaderLabNumber,
|
|
T_OrderHeaderM_PatientAge as AgePatient,
|
|
M_PatientID,
|
|
M_PatientNoReg,
|
|
M_PatientDOB,
|
|
M_PatientGender,
|
|
M_PatientIdentifierValue,
|
|
M_PatientNIP,
|
|
M_PatientJob,
|
|
M_PatientPosisi,
|
|
M_PatientDivisi,
|
|
M_PatientLocation,
|
|
M_PatientDepartement,
|
|
CONCAT(
|
|
IF(M_TitleName IS NULL, '', CONCAT(M_TitleName, '. ')),
|
|
IF(M_PatientPrefix IS NULL, '', CONCAT(M_PatientPrefix, ' ')),
|
|
M_PatientName,
|
|
IF(M_PatientSuffix IS NULL, '', CONCAT(M_PatientSuffix, ' '))
|
|
) as PatientName,
|
|
M_PatientName,
|
|
IF(Group_ResultName = 'LAB', 'LAB', Nat_TestName) as GroupResult,
|
|
Nat_TestID,
|
|
Nat_TestCode,
|
|
Nat_TestName,
|
|
T_TestName,
|
|
IFNULL(T_OrderDetailResult, '') as Result,
|
|
Mcu_KelainanID,
|
|
Mcu_KelainanName,
|
|
Mcu_KelainanGroupSummaryID,
|
|
Mcu_KelainanGroupSummaryName,
|
|
Mcu_FitnessCategoryID,
|
|
Mcu_FitnessCategoryName,
|
|
Mcu_FitnessCategoryEng,
|
|
Mcu_FitnessCategoryLevel
|
|
FROM t_kelainan_lab
|
|
JOIN t_orderdetail ON T_KelainanLabT_OrderDetailID = T_OrderDetailID AND T_OrderDetailIsActive = 'Y'
|
|
JOIN t_orderheader ON T_KelainanLabT_OrderHeaderID = T_OrderHeaderID
|
|
AND T_OrderHeaderIsActive = 'Y'
|
|
JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
|
|
AND M_PatientIsActive = 'Y'
|
|
JOIN mgm_mcu ON T_OrderHeaderMgm_McuID = Mgm_McuID
|
|
AND Mgm_McuID = ?
|
|
JOIN mcu_summarylab ON T_KelainanLabMcu_SummaryLabID = Mcu_SummaryLabID
|
|
AND Mcu_SummaryLabIsActive = 'Y'
|
|
JOIN nat_test ON Mcu_SummaryLabNat_TestID = Nat_TestID
|
|
AND Nat_TestIsActive = 'Y'
|
|
JOIN t_test ON T_TestNat_TestID = Nat_TestID AND T_TestIsActive = '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_ResultIsActive = 'Y'
|
|
JOIN mcu_kelainan ON Mcu_summaryLabMcu_KelainanID = Mcu_KelainanID
|
|
AND Mcu_KelainanIsActive = 'Y'
|
|
JOIN mcu_kelainangroupsummarydetail ON Mcu_KelainanGroupSummaryDetailMcu_KelainanID = Mcu_KelainanID
|
|
AND Mcu_KelainanGroupSummaryDetailIsActive = 'Y'
|
|
JOIN mcu_kelainangroupsummary ON Mcu_KelainanGroupSummaryDetailMcu_KelainanGroupSummaryID = Mcu_KelainanGroupSummaryID
|
|
AND Mcu_KelainanGroupSummaryIsActive = 'Y'
|
|
JOIN mcu_fitness_category ON Mcu_SummaryLabMcu_FitnessCategoryID = Mcu_FitnessCategoryID
|
|
AND Mcu_FitnessCategoryIsActive = 'Y'
|
|
LEFT JOIN m_title ON M_PatientM_TitleID = M_TitleID
|
|
AND M_TitleIsActive = 'Y'
|
|
WHERE T_KelainanLabIsActive = 'Y'
|
|
GROUP BY T_KelainanLabID";
|
|
|
|
$query = $this->db->query($sql,[$mgm_mcuid]);
|
|
if(!$query){
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("insert data lab kelainan_details", $this->db);
|
|
exit;
|
|
}
|
|
|
|
$this->db_corporate->trans_commit();
|
|
|
|
$dashboard_sync = $this->sync_to_dashboard($mgm_mcuid);
|
|
$result = array("message" => "Data lab kelainan_details generated successfully", "dashboard_sync" => $dashboard_sync);
|
|
$this->sys_ok($result);
|
|
exit;
|
|
|
|
}
|
|
|
|
function generate_kelainan_nonlab()
|
|
{
|
|
/*if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}*/
|
|
|
|
$prm = $this->sys_input;
|
|
$mgm_mcuid = $prm['Mgm_McuID'];
|
|
|
|
$sql = "SELECT *
|
|
FROM mcu_number
|
|
WHERE Mcu_NumberMgm_McuID = ? AND
|
|
Mcu_NumberIsActive = 'Y'
|
|
ORDER BY Mcu_NumberID DESC
|
|
LIMIT 1";
|
|
$query = $this->db_corporate->query($sql, [$mgm_mcuid]);
|
|
$data_setup = $query->result_array();
|
|
if(count($data_setup) == 0){
|
|
$this->sys_error("MGM MCU not found");
|
|
exit;
|
|
}
|
|
$numbering_column = $data_setup[0]['Mcu_NumberTableName'];
|
|
|
|
|
|
$table_name = "kelainan_details";
|
|
$exist_table = $this->db_corporate->table_exists($table_name);
|
|
|
|
$this->db_corporate->trans_begin();
|
|
|
|
if(!$exist_table){
|
|
$sql = "CREATE TABLE IF NOT EXISTS `{$table_name}` (
|
|
Numbering VARCHAR(150),
|
|
Tx_KelainanID INT,
|
|
Tx_Type VARCHAR(10),
|
|
T_OrderHeaderID INT,
|
|
T_OrderHeaderDate DATETIME,
|
|
T_OrderHeaderLabNumber VARCHAR(100),
|
|
AgePatient INT,
|
|
M_PatientID INT,
|
|
M_PatientNoReg VARCHAR(50),
|
|
M_PatientDOB DATE,
|
|
M_PatientGender VARCHAR(10),
|
|
M_PatientIdentifierValue VARCHAR(100),
|
|
M_PatientNIP VARCHAR(50),
|
|
M_PatientJob VARCHAR(100),
|
|
M_PatientPosisi VARCHAR(100),
|
|
M_PatientDivisi VARCHAR(100),
|
|
M_PatientLocation VARCHAR(100),
|
|
M_PatientDepartement VARCHAR(100),
|
|
PatientName VARCHAR(255),
|
|
M_PatientName VARCHAR(255),
|
|
GroupResult VARCHAR(100),
|
|
Nat_TestID INT,
|
|
Nat_TestCode VARCHAR(50),
|
|
Nat_TestName VARCHAR(255),
|
|
T_TestName VARCHAR(255),
|
|
Result VARCHAR(255),
|
|
Mcu_KelainanID INT,
|
|
Mcu_KelainanName VARCHAR(255),
|
|
Mcu_KelainanGroupSummaryID INT,
|
|
Mcu_KelainanGroupSummaryName VARCHAR(255),
|
|
Mcu_FitnessCategoryID INT,
|
|
Mcu_FitnessCategoryName VARCHAR(100),
|
|
Mcu_FitnessCategoryEng VARCHAR(100),
|
|
Mcu_FitnessCategoryLevel INT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_kelainan_lab (Tx_KelainanID),
|
|
INDEX idx_order_header (T_OrderHeaderID),
|
|
INDEX idx_order_lab_number (T_OrderHeaderLabNumber),
|
|
INDEX idx_patient_id (M_PatientID),
|
|
INDEX idx_patient_noreg (M_PatientNoReg),
|
|
INDEX idx_patient_location (M_PatientLocation),
|
|
INDEX idx_patient_posisi (M_PatientPosisi),
|
|
INDEX idx_patient_divisi (M_PatientDivisi),
|
|
INDEX idx_patient_departement (M_PatientDepartement),
|
|
INDEX idx_test (Nat_TestID),
|
|
INDEX idx_kelainan (Mcu_KelainanID),
|
|
INDEX idx_group_result (GroupResult),
|
|
INDEX idx_numbering (Numbering)
|
|
) ENGINE=InnoDB";
|
|
$query = $this->db_corporate->query($sql);
|
|
if(!$query){
|
|
$this->db_corporate->trans_rollback();
|
|
$this->sys_error_db("create table kelainan_details", $this->db_corporate);
|
|
exit;
|
|
}
|
|
}
|
|
|
|
$sql = "INSERT INTO `cpone_corporate`.`{$table_name}` (
|
|
Numbering,
|
|
Tx_KelainanID,
|
|
Tx_Type,
|
|
T_OrderHeaderID,
|
|
T_OrderHeaderDate,
|
|
T_OrderHeaderLabNumber,
|
|
AgePatient,
|
|
M_PatientID,
|
|
M_PatientNoReg,
|
|
M_PatientDOB,
|
|
M_PatientGender,
|
|
M_PatientIdentifierValue,
|
|
M_PatientNIP,
|
|
M_PatientJob,
|
|
M_PatientPosisi,
|
|
M_PatientDivisi,
|
|
M_PatientLocation,
|
|
M_PatientDepartement,
|
|
PatientName,
|
|
M_PatientName,
|
|
GroupResult,
|
|
Nat_TestID,
|
|
Nat_TestCode,
|
|
Nat_TestName,
|
|
T_TestName,
|
|
Result,
|
|
Mcu_KelainanID,
|
|
Mcu_KelainanName,
|
|
Mcu_KelainanGroupSummaryID,
|
|
Mcu_KelainanGroupSummaryName,
|
|
Mcu_FitnessCategoryID,
|
|
Mcu_FitnessCategoryName,
|
|
Mcu_FitnessCategoryEng,
|
|
Mcu_FitnessCategoryLevel
|
|
)
|
|
SELECT
|
|
'{$numbering_column}',
|
|
T_KelainanNonLabID as Tx_KelainanID,
|
|
'NONLAB' as Tx_Type,
|
|
T_OrderHeaderID,
|
|
T_OrderHeaderDate,
|
|
T_OrderHeaderLabNumber,
|
|
T_OrderHeaderM_PatientAge as AgePatient,
|
|
M_PatientID,
|
|
M_PatientNoReg,
|
|
M_PatientDOB,
|
|
M_PatientGender,
|
|
M_PatientIdentifierValue,
|
|
M_PatientNIP,
|
|
M_PatientJob,
|
|
M_PatientPosisi,
|
|
M_PatientDivisi,
|
|
M_PatientLocation,
|
|
M_PatientDepartement,
|
|
CONCAT(IF(M_TitleName IS NULL,'',CONCAT(M_TitleName,'. ')), IF(M_PatientPrefix IS NULL,'',CONCAT(M_PatientPrefix,' ')), M_PatientName, IF(M_PatientSuffix IS NULL,'',CONCAT(M_PatientPrefix,' '))) as PatientName,
|
|
M_PatientName,
|
|
IF(Group_ResultName = 'LAB', 'LAB', Nat_TestName) as GroupResult,
|
|
Nat_TestID,
|
|
Nat_TestCode,
|
|
Nat_TestName,
|
|
T_TestName,
|
|
'',
|
|
Mcu_KelainanID,
|
|
Mcu_KelainanName,
|
|
Mcu_KelainanGroupSummaryID,
|
|
Mcu_KelainanGroupSummaryName,
|
|
Mcu_FitnessCategoryID,
|
|
Mcu_FitnessCategoryName,
|
|
Mcu_FitnessCategoryEng,
|
|
Mcu_FitnessCategoryLevel
|
|
FROM t_kelainan_nonlab
|
|
JOIN t_orderheader ON T_KelainanNonLabT_OrderHeaderID = T_OrderHeaderID AND
|
|
T_OrderHeaderIsActive = 'Y'
|
|
JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID AND M_PatientIsActive = 'Y'
|
|
JOIN mgm_mcu ON T_OrderHeaderMgm_McuID = Mgm_McuID AND
|
|
Mgm_McuID = ?
|
|
JOIN mcu_summarynonlab ON T_KelainanNonLabMcu_SummaryNonlabID = Mcu_SummaryNonlabID AND
|
|
Mcu_SummaryNonlabIsActive = 'Y'
|
|
JOIN nat_test ON Mcu_SummaryNonlabNat_TestID = Nat_TestID AND Nat_TestIsActive = 'Y'
|
|
JOIN t_test ON T_TestNat_TestID = Nat_TestID AND T_TestIsActive = '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_ResultIsActive = 'Y'
|
|
JOIN mcu_kelainan ON Mcu_SummaryNonlabMcu_KelainanID = Mcu_KelainanID AND Mcu_KelainanIsActive = 'Y'
|
|
JOIN mcu_kelainangroupsummarydetail ON Mcu_KelainanGroupSummaryDetailMcu_KelainanID = Mcu_KelainanID AND Mcu_KelainanGroupSummaryDetailIsActive = 'Y'
|
|
JOIN mcu_kelainangroupsummary ON Mcu_KelainanGroupSummaryDetailMcu_KelainanGroupSummaryID = Mcu_KelainanGroupSummaryID AND Mcu_KelainanGroupSummaryIsActive = 'Y'
|
|
JOIN mcu_fitness_category ON Mcu_SummaryNonlabMcu_FitnessCategoryID = Mcu_FitnessCategoryID AND Mcu_FitnessCategoryIsActive = 'Y'
|
|
LEFT JOIN m_title ON M_PatientM_TitleID = M_TitleID AND M_TitleIsActive = 'Y'
|
|
WHERE
|
|
T_KelainanNonLabIsActive = 'Y'";
|
|
|
|
$query = $this->db->query($sql,[$mgm_mcuid]);
|
|
//echo $this->db->last_query();
|
|
//exit;
|
|
if(!$query){
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("insert data nonlab kelainan_details", $this->db);
|
|
exit;
|
|
}
|
|
|
|
$this->db_corporate->trans_commit();
|
|
|
|
$dashboard_sync = $this->sync_to_dashboard($mgm_mcuid);
|
|
$result = array("message" => "Data nonlab kelainan_details generated successfully", "dashboard_sync" => $dashboard_sync);
|
|
$this->sys_ok($result);
|
|
exit;
|
|
|
|
}
|
|
|
|
function generate_kelainan_fisik()
|
|
{
|
|
/*if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}*/
|
|
|
|
$prm = $this->sys_input;
|
|
$mgm_mcuid = $prm['Mgm_McuID'];
|
|
|
|
$sql = "SELECT *
|
|
FROM mcu_number
|
|
WHERE Mcu_NumberMgm_McuID = ? AND
|
|
Mcu_NumberIsActive = 'Y'
|
|
ORDER BY Mcu_NumberID DESC
|
|
LIMIT 1";
|
|
$query = $this->db_corporate->query($sql, [$mgm_mcuid]);
|
|
$data_setup = $query->result_array();
|
|
if(count($data_setup) == 0){
|
|
$this->sys_error("MGM MCU not found");
|
|
exit;
|
|
}
|
|
|
|
$numbering_column = $data_setup[0]['Mcu_NumberTableName'];
|
|
|
|
$table_name = "kelainan_details";
|
|
$exist_table = $this->db_corporate->table_exists($table_name);
|
|
|
|
$this->db_corporate->trans_begin();
|
|
|
|
if(!$exist_table){
|
|
$sql = "CREATE TABLE IF NOT EXISTS `{$table_name}` (
|
|
Numbering VARCHAR(150),
|
|
Tx_KelainanID INT,
|
|
Tx_Type VARCHAR(10),
|
|
T_OrderHeaderID INT,
|
|
T_OrderHeaderDate DATETIME,
|
|
T_OrderHeaderLabNumber VARCHAR(100),
|
|
AgePatient INT,
|
|
M_PatientID INT,
|
|
M_PatientNoReg VARCHAR(50),
|
|
M_PatientDOB DATE,
|
|
M_PatientGender VARCHAR(10),
|
|
M_PatientIdentifierValue VARCHAR(100),
|
|
M_PatientNIP VARCHAR(50),
|
|
M_PatientJob VARCHAR(100),
|
|
M_PatientPosisi VARCHAR(100),
|
|
M_PatientDivisi VARCHAR(100),
|
|
M_PatientLocation VARCHAR(100),
|
|
M_PatientDepartement VARCHAR(100),
|
|
PatientName VARCHAR(255),
|
|
M_PatientName VARCHAR(255),
|
|
GroupResult VARCHAR(100),
|
|
Nat_TestID INT,
|
|
Nat_TestCode VARCHAR(50),
|
|
Nat_TestName VARCHAR(255),
|
|
T_TestName VARCHAR(255),
|
|
Result VARCHAR(255),
|
|
Mcu_KelainanID INT,
|
|
Mcu_KelainanName VARCHAR(255),
|
|
Mcu_KelainanGroupSummaryID INT,
|
|
Mcu_KelainanGroupSummaryName VARCHAR(255),
|
|
Mcu_FitnessCategoryID INT,
|
|
Mcu_FitnessCategoryName VARCHAR(100),
|
|
Mcu_FitnessCategoryEng VARCHAR(100),
|
|
Mcu_FitnessCategoryLevel INT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_numbering (Numbering),
|
|
INDEX idx_kelainan_lab (Tx_KelainanID),
|
|
INDEX idx_order_header (T_OrderHeaderID),
|
|
INDEX idx_order_lab_number (T_OrderHeaderLabNumber),
|
|
INDEX idx_patient_id (M_PatientID),
|
|
INDEX idx_patient_noreg (M_PatientNoReg),
|
|
INDEX idx_patient_location (M_PatientLocation),
|
|
INDEX idx_patient_posisi (M_PatientPosisi),
|
|
INDEX idx_patient_divisi (M_PatientDivisi),
|
|
INDEX idx_patient_departement (M_PatientDepartement),
|
|
INDEX idx_test (Nat_TestID),
|
|
INDEX idx_kelainan (Mcu_KelainanID),
|
|
INDEX idx_group_result (GroupResult)
|
|
) ENGINE=InnoDB";
|
|
$query = $this->db_corporate->query($sql);
|
|
if(!$query){
|
|
$this->db_corporate->trans_rollback();
|
|
$this->sys_error_db("create table kelainan_details", $this->db_corporate);
|
|
exit;
|
|
}
|
|
}
|
|
|
|
$sql = "INSERT INTO `cpone_corporate`.`{$table_name}` (
|
|
Numbering,
|
|
Tx_KelainanID,
|
|
Tx_Type,
|
|
T_OrderHeaderID,
|
|
T_OrderHeaderDate,
|
|
T_OrderHeaderLabNumber,
|
|
AgePatient,
|
|
M_PatientID,
|
|
M_PatientNoReg,
|
|
M_PatientDOB,
|
|
M_PatientGender,
|
|
M_PatientIdentifierValue,
|
|
M_PatientNIP,
|
|
M_PatientJob,
|
|
M_PatientPosisi,
|
|
M_PatientDivisi,
|
|
M_PatientLocation,
|
|
M_PatientDepartement,
|
|
PatientName,
|
|
M_PatientName,
|
|
GroupResult,
|
|
Nat_TestID,
|
|
Nat_TestCode,
|
|
Nat_TestName,
|
|
T_TestName,
|
|
Result,
|
|
Mcu_KelainanID,
|
|
Mcu_KelainanName,
|
|
Mcu_KelainanGroupSummaryID,
|
|
Mcu_KelainanGroupSummaryName,
|
|
Mcu_FitnessCategoryID,
|
|
Mcu_FitnessCategoryName,
|
|
Mcu_FitnessCategoryEng,
|
|
Mcu_FitnessCategoryLevel
|
|
)
|
|
SELECT '{$numbering_column}',
|
|
T_KelainanFiskID as Tx_KelainanID,
|
|
'FISIK' as Tx_Type,
|
|
T_OrderHeaderID,
|
|
T_OrderHeaderDate,
|
|
T_OrderHeaderLabNumber,
|
|
T_OrderHeaderM_PatientAge as AgePatient,
|
|
M_PatientID,
|
|
M_PatientNoReg,
|
|
M_PatientDOB,
|
|
M_PatientGender,
|
|
M_PatientIdentifierValue,
|
|
M_PatientNIP,
|
|
M_PatientJob,
|
|
M_PatientPosisi,
|
|
M_PatientDivisi,
|
|
M_PatientLocation,
|
|
M_PatientDepartement,
|
|
CONCAT(IF(M_TitleName IS NULL,'',CONCAT(M_TitleName,'. ')), IF(M_PatientPrefix IS NULL,'',CONCAT(M_PatientPrefix,' ')), M_PatientName, IF(M_PatientSuffix IS NULL,'',CONCAT(M_PatientPrefix,' '))) as PatientName,
|
|
M_PatientName,
|
|
IF(Group_ResultName = 'LAB', 'LAB', Nat_TestName) as GroupResult,
|
|
Nat_TestID,
|
|
Nat_TestCode,
|
|
Nat_TestName,
|
|
CONCAT(IF(label IS NULL, Mcu_KelainanGroupName, CONCAT(label, ' - ',IF(level = 1, parent_level_1,IF(level = 2, parent_level_2, IF(level = 3,parent_level_3,parent_level_4)))))) as T_TestName,
|
|
IF(Mcu_FisikValueNote = '',Mcu_FisikValueLabel,Mcu_FisikValueNote) as Result,
|
|
Mcu_KelainanID,
|
|
Mcu_KelainanName,
|
|
Mcu_KelainanGroupSummaryID,
|
|
Mcu_KelainanGroupSummaryName,
|
|
Mcu_FitnessCategoryID,
|
|
Mcu_FitnessCategoryName,
|
|
Mcu_FitnessCategoryEng,
|
|
Mcu_FitnessCategoryLevel
|
|
FROM t_kelainan_fisik
|
|
JOIN t_orderheader ON T_KelainanFiskT_OrderHeaderID = T_OrderHeaderID AND
|
|
T_OrderHeaderIsActive = 'Y'
|
|
JOIN so_resultentry ON T_KelainanFiskSo_ResultEntryID = So_ResultEntryID AND So_ResultEntryIsActive = 'Y'
|
|
JOIN t_orderdetail ON So_ResultEntryT_OrderDetailID = T_OrderDetailID AND T_OrderDetailIsActive = 'Y'
|
|
JOIN nat_test ON Nat_TestCode = T_OrderDetailT_TestCode AND Nat_TestIsActive = 'Y'
|
|
JOIN t_test ON T_TestNat_TestID = Nat_TestID AND T_TestIsActive = '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_ResultIsActive = 'Y'
|
|
JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID AND M_PatientIsActive = 'Y'
|
|
JOIN mgm_mcu ON T_OrderHeaderMgm_McuID = Mgm_McuID AND
|
|
Mgm_McuID = ?
|
|
JOIN mcu_fisiksummary ON T_KelainanFiskMcu_FisikSummaryID = Mcu_FisikSummaryID AND
|
|
Mcu_FisikSummaryIsActive = 'Y'
|
|
JOIN mcu_fisiksummarydetail ON Mcu_FisikSummaryDetailMcu_FisikSummaryID = Mcu_FisikSummaryID AND Mcu_FisikSummaryDetailIsActive = 'Y'
|
|
JOIN mcu_kelainan ON Mcu_FisikSummaryMcu_KelainanID = Mcu_KelainanID AND Mcu_KelainanIsActive = 'Y'
|
|
JOIN mcu_kelainangroup ON Mcu_KelainanMcu_KelainanGroupID = Mcu_KelainanGroupID AND Mcu_KelainanGroupIsActive = 'Y'
|
|
JOIN mcu_kelainangroupsummarydetail ON Mcu_KelainanGroupSummaryDetailMcu_KelainanID = Mcu_KelainanID AND
|
|
Mcu_KelainanGroupSummaryDetailIsActive = 'Y'
|
|
JOIN mcu_kelainangroupsummary ON Mcu_KelainanGroupSummaryDetailMcu_KelainanGroupSummaryID = Mcu_KelainanGroupSummaryID AND Mcu_KelainanGroupSummaryIsActive = 'Y'
|
|
JOIN mcu_fitness_category ON Mcu_FisikSummaryMcu_FitnessCategoryID = Mcu_FitnessCategoryID AND Mcu_FitnessCategoryIsActive = 'Y'
|
|
JOIN mcu_fisikvalue ON Mcu_FisikValueT_OrderHeaderID = T_OrderHeaderID AND Mcu_FisikValueIsActive = 'Y' AND
|
|
Mcu_FisikValueCode = Mcu_FisikSummaryDetailCode
|
|
LEFT JOIN fisik_template_map ON id_code = Mcu_FisikSummaryDetailCode
|
|
LEFT JOIN m_title ON M_PatientM_TitleID = M_TitleID AND M_TitleIsActive = 'Y'
|
|
WHERE
|
|
T_KelainanFiskIsActive = 'Y'";
|
|
|
|
$query = $this->db->query($sql,[$mgm_mcuid]);
|
|
if(!$query){
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("insert data fisik kelainan_details", $this->db);
|
|
exit;
|
|
}
|
|
|
|
$this->db_corporate->trans_commit();
|
|
|
|
$dashboard_sync = $this->sync_to_dashboard($mgm_mcuid);
|
|
$result = array("message" => "Data fisik kelainan_details generated successfully", "dashboard_sync" => $dashboard_sync);
|
|
$this->sys_ok($result);
|
|
exit;
|
|
|
|
}
|
|
|
|
|
|
function generate_all_results()
|
|
{
|
|
/*if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}*/
|
|
|
|
$prm = $this->sys_input;
|
|
$mgm_mcuid = $prm['Mgm_McuID'];
|
|
|
|
$sql = "SELECT * FROM mcu_number WHERE Mcu_NumberMgm_McuID = ? AND Mcu_NumberIsActive = 'Y' ORDER BY Mcu_NumberID DESC LIMIT 1";
|
|
$query = $this->db_corporate->query($sql, [$mgm_mcuid]);
|
|
if(!$query){
|
|
$this->sys_error_db("select data mcu_number", $this->db_corporate);
|
|
//echo $this->db_corporate->error_message();
|
|
echo $this->db_corporate->last_query();
|
|
exit;
|
|
}
|
|
$data_setup = $query->result_array();
|
|
if(count($data_setup) == 0){
|
|
$this->sys_error("MGM MCU not found");
|
|
exit;
|
|
}
|
|
|
|
$numbering_column = $data_setup[0]['Mcu_NumberTableName'];
|
|
|
|
$table_name = "mcu_result_all";
|
|
$exist_table = $this->db_corporate->table_exists($table_name);
|
|
|
|
$this->db_corporate->trans_begin();
|
|
|
|
if(!$exist_table){
|
|
$sql = "CREATE TABLE IF NOT EXISTS `{$table_name}` (
|
|
`Mcu_ResultAllID` int NOT NULL AUTO_INCREMENT,
|
|
`Numbering` varchar(150) DEFAULT NULL,
|
|
`Mcu_ResultAllM_LangID` int NOT NULL DEFAULT '1',
|
|
`Mcu_ResultAllMgm_McuID` int DEFAULT NULL,
|
|
`Mcu_ResultAllT_OrderHeaderID` int DEFAULT NULL,
|
|
`Mcu_ResultAllT_OrderHeaderLabNumber` varchar(50) DEFAULT NULL,
|
|
`Mcu_ResultAllReffID` int DEFAULT NULL,
|
|
`Mcu_ResultAllT_TestCode` varchar(50) DEFAULT NULL,
|
|
`Mcu_ResultAllT_TestName` varchar(255) DEFAULT NULL,
|
|
`Mcu_ResultAllResult` text,
|
|
`Mcu_ResultAllUnit` varchar(255) DEFAULT NULL,
|
|
`Mcu_ResultAllRefference` text,
|
|
`Mcu_ResultAllCreated` datetime DEFAULT NULL,
|
|
PRIMARY KEY (`Mcu_ResultAllID`),
|
|
KEY `Mcu_ResultAllMgm_McuID` (`Mcu_ResultAllMgm_McuID`),
|
|
KEY `Mcu_ResultAllT_OrderHeaderID` (`Mcu_ResultAllT_OrderHeaderID`),
|
|
KEY `Mcu_ResultAllT_OrderHeaderLabNumber` (`Mcu_ResultAllT_OrderHeaderLabNumber`),
|
|
KEY `Mcu_ResultAllReffID` (`Mcu_ResultAllReffID`),
|
|
KEY `Mcu_ResultAllT_TestCode` (`Mcu_ResultAllT_TestCode`),
|
|
KEY `Numbering` (`Numbering`)
|
|
) ENGINE=InnoDB";
|
|
$query = $this->db_corporate->query($sql);
|
|
|
|
if(!$query){
|
|
$this->db_corporate->trans_rollback();
|
|
$this->sys_error_db("create table group_summary", $this->db_corporate);
|
|
exit;
|
|
}
|
|
}
|
|
|
|
$sql = "INSERT INTO `cpone_corporate`.`{$table_name}` (
|
|
Numbering,
|
|
Mcu_ResultAllM_LangID,
|
|
Mcu_ResultAllMgm_McuID,
|
|
Mcu_ResultAllT_OrderHeaderID,
|
|
Mcu_ResultAllT_OrderHeaderLabNumber,
|
|
Mcu_ResultAllReffID,
|
|
Mcu_ResultAllT_TestCode,
|
|
Mcu_ResultAllT_TestName,
|
|
Mcu_ResultAllResult,
|
|
Mcu_ResultAllUnit,
|
|
Mcu_ResultAllRefference,
|
|
Mcu_ResultAllCreated
|
|
)
|
|
SELECT '{$numbering_column}',
|
|
Mcu_ResultAllM_LangID,
|
|
Mcu_ResultAllMgm_McuID,
|
|
Mcu_ResultAllT_OrderHeaderID,
|
|
Mcu_ResultAllT_OrderHeaderLabNumber,
|
|
Mcu_ResultAllReffID,
|
|
Mcu_ResultAllT_TestCode,
|
|
Mcu_ResultAllT_TestName,
|
|
Mcu_ResultAllResult,
|
|
Mcu_ResultAllUnit,
|
|
Mcu_ResultAllRefference,
|
|
Mcu_ResultAllCreated
|
|
FROM `cpone_log`.`mcu_result_all`
|
|
WHERE
|
|
Mcu_ResultAllMgm_McuID = ?";
|
|
|
|
$query = $this->db->query($sql,[$mgm_mcuid]);
|
|
if(!$query){
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("insert data mcu_result_all", $this->db);
|
|
exit;
|
|
}
|
|
|
|
$this->db_corporate->trans_commit();
|
|
|
|
$dashboard_sync = $this->sync_to_dashboard($mgm_mcuid);
|
|
$result = array("message" => "Data mcu_result_all generated successfully", "dashboard_sync" => $dashboard_sync);
|
|
$this->sys_ok($result);
|
|
exit;
|
|
|
|
}
|
|
|
|
function summary_kelainan_sepuluh()
|
|
{
|
|
/*if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}*/
|
|
|
|
$prm = $this->sys_input;
|
|
$mgm_mcuid = $prm['Mgm_McuID'];
|
|
|
|
$sql = "SELECT * FROM mcu_number WHERE Mcu_NumberMgm_McuID = ? AND Mcu_NumberIsActive = 'Y' ORDER BY Mcu_NumberID DESC LIMIT 1";
|
|
$query = $this->db_corporate->query($sql, [$mgm_mcuid]);
|
|
if(!$query){
|
|
$this->sys_error_db("select data mcu_number", $this->db_corporate);
|
|
//echo $this->db_corporate->error_message();
|
|
echo $this->db_corporate->last_query();
|
|
exit;
|
|
}
|
|
$data_setup = $query->result_array();
|
|
if(count($data_setup) == 0){
|
|
$this->sys_error("MGM MCU not found");
|
|
exit;
|
|
}
|
|
|
|
$table_name = "kelainan_summary";
|
|
$exist_table = $this->db_corporate->table_exists($table_name);
|
|
|
|
$this->db_corporate->trans_begin();
|
|
|
|
if(!$exist_table){
|
|
$sql = "CREATE TABLE IF NOT EXISTS `{$table_name}` (
|
|
Mcu_KelainanID INT,
|
|
Mcu_KelainanMgm_McuID INT,
|
|
Mcu_KelainanName VARCHAR(250),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_kelainan_summary (Mcu_KelainanID)
|
|
) ENGINE=InnoDB";
|
|
$query = $this->db_corporate->query($sql);
|
|
|
|
if(!$query){
|
|
$this->db_corporate->trans_rollback();
|
|
$this->sys_error_db("create table group_summary", $this->db_corporate);
|
|
exit;
|
|
}
|
|
}
|
|
|
|
$sql = "INSERT INTO `cpone_corporate`.`{$table_name}` (
|
|
Mcu_KelainanID,
|
|
Mcu_KelainanMgm_McuID,
|
|
Mcu_KelainanName
|
|
)
|
|
SELECT Mcu_KelainanID,
|
|
Kelainan_SummariesProjectMgm_McuID,
|
|
Mcu_KelainanName
|
|
FROM mcu_kelainan
|
|
JOIN kelainan_summaries_project ON Kelainan_SummariesProjectKelainanID = Mcu_KelainanID
|
|
WHERE
|
|
Kelainan_SummariesProjectMgm_McuID = ? AND Kelainan_SummariesProjectIsActive = 'Y'";
|
|
|
|
$query = $this->db->query($sql,[$mgm_mcuid]);
|
|
if(!$query){
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("insert data group_summary", $this->db);
|
|
exit;
|
|
}
|
|
|
|
$this->db_corporate->trans_commit();
|
|
|
|
$dashboard_sync = $this->sync_to_dashboard($mgm_mcuid);
|
|
$result = array("message" => "Data kelainan_summary generated successfully", "dashboard_sync" => $dashboard_sync);
|
|
$this->sys_ok($result);
|
|
exit;
|
|
|
|
}
|
|
|
|
|
|
|
|
/*
|
|
UPDATE kelainan_details_JYMXG02AQ5
|
|
SET M_PatientDepartement = CASE
|
|
WHEN MOD(T_OrderHeaderID, 5) = 0 THEN 'IT Department'
|
|
WHEN MOD(T_OrderHeaderID, 5) = 1 THEN 'HR Department'
|
|
WHEN MOD(T_OrderHeaderID, 5) = 2 THEN 'Finance Department'
|
|
WHEN MOD(T_OrderHeaderID, 5) = 3 THEN 'Operations Department'
|
|
WHEN MOD(T_OrderHeaderID, 5) = 4 THEN 'Marketing Department'
|
|
END;
|
|
*/
|
|
|
|
// ==================== FISIK TEMPLATE MAP FUNCTIONS ====================
|
|
/*
|
|
* FISIK TEMPLATE MAP - Dokumentasi & Urutan Penggunaan
|
|
* ========================================================
|
|
*
|
|
* TUJUAN:
|
|
* Mengekstrak dan menyimpan semua id_code, label, dan parent hierarchy dari
|
|
* JSON template (FisikTemplateJSON) ke dalam satu tabel terstruktur (fisik_template_map)
|
|
* untuk memudahkan query dan mapping data.
|
|
*
|
|
* URUTAN PENGGUNAAN:
|
|
* ------------------
|
|
*
|
|
* STEP 1: Lihat Template yang Aktif (Opsional)
|
|
* Endpoint: /generatedata/list_active_fisik_templates
|
|
* Fungsi : list_active_fisik_templates()
|
|
* Tujuan : Melihat list semua template dengan FisikTemplateIsActive = 'Y'
|
|
* Request : Tidak ada parameter
|
|
* Response: { total: 9, data: [template_list...] }
|
|
*
|
|
* STEP 2: Lihat Detail Template (Opsional)
|
|
* Endpoint: /generatedata/get_fisik_template_detail
|
|
* Fungsi : get_fisik_template_detail()
|
|
* Tujuan : Melihat detail template tertentu dengan JSON parsed
|
|
* Request : { FisikTemplateCode: "RI00" }
|
|
* Response: { FisikTemplateJSON_Parsed: {...} }
|
|
*
|
|
* STEP 3: Buat Tabel Mapping (Hanya Sekali)
|
|
* Endpoint: /generatedata/create_fisik_template_map_table
|
|
* Fungsi : create_fisik_template_map_table()
|
|
* Tujuan : Membuat tabel fisik_template_map jika belum ada
|
|
* Request : Tidak ada parameter
|
|
* Response: { message: "Table created successfully" }
|
|
*
|
|
* STEP 4a: Generate Semua Template (Full Generate)
|
|
* Endpoint: /generatedata/generate_fisik_template_map
|
|
* Fungsi : generate_fisik_template_map()
|
|
* Tujuan : Extract & simpan mapping SEMUA template aktif
|
|
* Request : Tidak ada parameter
|
|
* Response: { total_records: 250, total_templates: 9 }
|
|
* Note : Truncate semua data lama, insert semua data baru
|
|
*
|
|
* STEP 4b: Generate Template Tertentu (Partial Update)
|
|
* Endpoint: /generatedata/generate_fisik_template_map_by_code
|
|
* Fungsi : generate_fisik_template_map_by_code()
|
|
* Tujuan : Extract & update mapping untuk satu template saja
|
|
* Request : { FisikTemplateCode: "RI00" }
|
|
* Response: { template_code: "RI00", total_records: 9 }
|
|
* Note : Hapus data lama template ini saja, insert data baru
|
|
* Use Case: Ketika ada perubahan di satu template, tidak perlu regenerate semua
|
|
*
|
|
* STEP 5a: Query Data Mapping
|
|
* Endpoint: /generatedata/get_fisik_template_map
|
|
* Fungsi : get_fisik_template_map()
|
|
* Tujuan : Ambil data mapping dengan filter opsional
|
|
* Request : { FisikTemplateCode: "RI00", level: 2 } (opsional)
|
|
* Response: Array of mapping data
|
|
*
|
|
* STEP 5b: Search Data Mapping
|
|
* Endpoint: /generatedata/search_fisik_template_map
|
|
* Fungsi : search_fisik_template_map()
|
|
* Tujuan : Cari mapping berdasarkan id_code atau label
|
|
* Request : { keyword: "perut" }
|
|
* Response: Array of matching records
|
|
*
|
|
* FUNGSI HELPER (Internal):
|
|
* -------------------------
|
|
* - get_active_fisik_templates() : Helper untuk query template aktif
|
|
* - extract_data_from_json() : Extract data dari JSON recursively
|
|
* - extract_flat_details() : Extract untuk structure flat
|
|
* - extract_nested_with_name() : Extract untuk structure dengan grouping
|
|
* - extract_sub_details() : Extract sub-details recursively
|
|
*
|
|
* STRUKTUR TABEL MAPPING:
|
|
* ----------------------
|
|
* - MapID : Primary key auto increment
|
|
* - FisikTemplateID : ID template source
|
|
* - FisikTemplateCode : Code template (RI00, RI01, FI00, dst)
|
|
* - FisikTemplateType : Type template (Riwayat, Fisik, K3)
|
|
* - id_code : ID unik dari JSON (perut_1, perut_14_1, dst)
|
|
* - label : Label dari item
|
|
* - parent_level_1 : Title utama (PERUT / ABDOMEN)
|
|
* - parent_level_2 : Section/Group name (Hernia, Abdomen, dst)
|
|
* - parent_level_3 : Sub-item parent
|
|
* - parent_level_4 : Sub-sub-item parent
|
|
* - level : Kedalaman level (1-5)
|
|
* - table_name : Nama tabel target (perut, fisik_etnis, dst)
|
|
* - segment_name : Segment name dari item
|
|
* - type_form : Type form (XVS, XVS-LXX, XV, dst)
|
|
*
|
|
* CONTOH QUERY DATA:
|
|
* ------------------
|
|
* 1. Ambil semua item dari template RI00:
|
|
* SELECT * FROM fisik_template_map WHERE FisikTemplateCode = 'RI00'
|
|
*
|
|
* 2. Ambil semua item level 2 (biasanya item utama):
|
|
* SELECT * FROM fisik_template_map WHERE level = 2
|
|
*
|
|
* 3. Cari berdasarkan id_code:
|
|
* SELECT * FROM fisik_template_map WHERE id_code = 'perut_14_1'
|
|
*
|
|
* 4. Cari item dengan parent tertentu:
|
|
* SELECT * FROM fisik_template_map
|
|
* WHERE parent_level_1 = 'PERUT / ABDOMEN'
|
|
* AND parent_level_2 = 'Hernia'
|
|
*
|
|
* UPDATE WORKFLOW:
|
|
* ----------------
|
|
* Jika ada perubahan di fisik_template:
|
|
* 1. Update JSON di tabel fisik_template
|
|
* 2. Jalankan generate_fisik_template_map_by_code() untuk template yg berubah
|
|
* (Tidak perlu regenerate semua template)
|
|
* 3. Mapping otomatis terupdate untuk template tersebut
|
|
*/
|
|
|
|
/**
|
|
* STEP 3: Create table fisik_template_map
|
|
* ----------------------------------------
|
|
* Endpoint: POST /generatedata/create_fisik_template_map_table
|
|
* Request : No parameters
|
|
* Response: { message: "Table created successfully" }
|
|
*/
|
|
function create_fisik_template_map_table()
|
|
{
|
|
$sql = "CREATE TABLE IF NOT EXISTS `fisik_template_map` (
|
|
`MapID` int NOT NULL AUTO_INCREMENT,
|
|
`FisikTemplateID` int NOT NULL,
|
|
`FisikTemplateCode` varchar(50) NOT NULL,
|
|
`FisikTemplateType` varchar(25) NOT NULL,
|
|
`id_code` varchar(100) NOT NULL,
|
|
`label` varchar(255) NOT NULL,
|
|
`parent_level_1` varchar(255) NOT NULL DEFAULT '' COMMENT 'Title utama',
|
|
`parent_level_2` varchar(255) NOT NULL DEFAULT '' COMMENT 'Section/Group name',
|
|
`parent_level_3` varchar(255) NOT NULL DEFAULT '' COMMENT 'Sub-item parent',
|
|
`parent_level_4` varchar(255) NOT NULL DEFAULT '' COMMENT 'Sub-sub-item parent',
|
|
`level` int NOT NULL DEFAULT '1' COMMENT 'Kedalaman level',
|
|
`table_name` varchar(100) NOT NULL DEFAULT '',
|
|
`segment_name` varchar(255) NOT NULL DEFAULT '',
|
|
`type_form` varchar(50) NOT NULL DEFAULT '',
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`MapID`),
|
|
KEY `idx_template_id` (`FisikTemplateID`),
|
|
KEY `idx_id_code` (`id_code`),
|
|
KEY `idx_template_code` (`FisikTemplateCode`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
|
|
|
|
$query = $this->db->query($sql);
|
|
|
|
if(!$query){
|
|
$this->sys_error_db("create table fisik_template_map", $this->db);
|
|
exit;
|
|
}
|
|
|
|
$result = array("message" => "Table fisik_template_map created successfully");
|
|
$this->sys_ok($result);
|
|
exit;
|
|
}
|
|
|
|
/**
|
|
* HELPER FUNCTION (Internal)
|
|
* ---------------------------
|
|
* Get active fisik templates from database
|
|
* Mengambil data template yang aktif (FisikTemplateIsActive = 'Y')
|
|
*
|
|
* @param int|null $template_id Filter berdasarkan ID tertentu
|
|
* @param string|null $template_code Filter berdasarkan code tertentu
|
|
* @return array|false Array of templates atau false jika error
|
|
*
|
|
* Usage:
|
|
* - $this->get_active_fisik_templates() // Semua template aktif
|
|
* - $this->get_active_fisik_templates(1, null) // Template dengan ID = 1
|
|
* - $this->get_active_fisik_templates(null, 'RI00') // Template dengan code = RI00
|
|
*/
|
|
function get_active_fisik_templates($template_id = null, $template_code = null)
|
|
{
|
|
$sql = "SELECT FisikTemplateID, FisikTemplateType, FisikTemplateCode,
|
|
FisikTemplateTitle, FisikTemplateJSON, FisikTemplateTableName
|
|
FROM fisik_template
|
|
WHERE FisikTemplateIsActive = 'Y'";
|
|
|
|
$params = array();
|
|
|
|
if ($template_id !== null) {
|
|
$sql .= " AND FisikTemplateID = ?";
|
|
$params[] = $template_id;
|
|
}
|
|
|
|
if ($template_code !== null) {
|
|
$sql .= " AND FisikTemplateCode = ?";
|
|
$params[] = $template_code;
|
|
}
|
|
|
|
$sql .= " ORDER BY FisikTemplateID";
|
|
|
|
$query = $this->db->query($sql, $params);
|
|
|
|
if (!$query) {
|
|
return false;
|
|
}
|
|
|
|
return $query->result_array();
|
|
}
|
|
|
|
/**
|
|
* Extract data from JSON recursively
|
|
*/
|
|
function extract_data_from_json($data, $templateInfo, &$results)
|
|
{
|
|
$mainTitle = isset($data['title']) ? $data['title'] : '';
|
|
$typeForm = isset($data['type_form']) ? $data['type_form'] : '';
|
|
|
|
if (isset($data['details']) && is_array($data['details'])) {
|
|
$firstDetail = reset($data['details']);
|
|
|
|
if (isset($firstDetail['name'])) {
|
|
// Structure dengan grouping name
|
|
$this->extract_nested_with_name($data['details'], $mainTitle, $typeForm, $templateInfo, $results);
|
|
} else {
|
|
// Structure flat
|
|
$this->extract_flat_details($data['details'], $mainTitle, $typeForm, $templateInfo, $results);
|
|
}
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Extract flat details (tanpa grouping name)
|
|
*/
|
|
function extract_flat_details($details, $mainTitle, $typeForm, $templateInfo, &$results)
|
|
{
|
|
foreach ($details as $item) {
|
|
$result = array(
|
|
'FisikTemplateID' => $templateInfo['FisikTemplateID'],
|
|
'FisikTemplateCode' => $templateInfo['FisikTemplateCode'],
|
|
'FisikTemplateType' => $templateInfo['FisikTemplateType'],
|
|
'id_code' => isset($item['id_code']) ? $item['id_code'] : '',
|
|
'label' => isset($item['label']) ? $item['label'] : '',
|
|
'parent_level_1' => $mainTitle,
|
|
'parent_level_2' => '',
|
|
'parent_level_3' => '',
|
|
'parent_level_4' => '',
|
|
'level' => 1,
|
|
'table_name' => isset($item['table_name']) ? $item['table_name'] : '',
|
|
'segment_name' => isset($item['segment_name']) ? $item['segment_name'] : '',
|
|
'type_form' => isset($item['type_form']) ? $item['type_form'] : $typeForm
|
|
);
|
|
$results[] = $result;
|
|
|
|
// Cek sub-details
|
|
if (isset($item['details']) && is_array($item['details'])) {
|
|
$this->extract_sub_details(
|
|
$item['details'],
|
|
$mainTitle,
|
|
'',
|
|
isset($item['label']) ? $item['label'] : '',
|
|
'',
|
|
$templateInfo,
|
|
$results,
|
|
isset($item['table_name']) ? $item['table_name'] : '',
|
|
isset($item['segment_name']) ? $item['segment_name'] : '',
|
|
$typeForm
|
|
);
|
|
}
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Extract nested with name grouping
|
|
*/
|
|
function extract_nested_with_name($details, $mainTitle, $typeForm, $templateInfo, &$results)
|
|
{
|
|
foreach ($details as $section) {
|
|
$sectionName = isset($section['name']) ? $section['name'] : '';
|
|
|
|
if (isset($section['details']) && is_array($section['details'])) {
|
|
foreach ($section['details'] as $item) {
|
|
$result = array(
|
|
'FisikTemplateID' => $templateInfo['FisikTemplateID'],
|
|
'FisikTemplateCode' => $templateInfo['FisikTemplateCode'],
|
|
'FisikTemplateType' => $templateInfo['FisikTemplateType'],
|
|
'id_code' => isset($item['id_code']) ? $item['id_code'] : '',
|
|
'label' => isset($item['label']) ? $item['label'] : '',
|
|
'parent_level_1' => $mainTitle,
|
|
'parent_level_2' => $sectionName,
|
|
'parent_level_3' => '',
|
|
'parent_level_4' => '',
|
|
'level' => 2,
|
|
'table_name' => isset($item['table_name']) ? $item['table_name'] : '',
|
|
'segment_name' => isset($item['segment_name']) ? $item['segment_name'] : '',
|
|
'type_form' => isset($item['type_form']) ? $item['type_form'] : $typeForm
|
|
);
|
|
$results[] = $result;
|
|
|
|
// Cek sub-details
|
|
if (isset($item['details']) && is_array($item['details'])) {
|
|
$this->extract_sub_details(
|
|
$item['details'],
|
|
$mainTitle,
|
|
$sectionName,
|
|
isset($item['label']) ? $item['label'] : '',
|
|
'',
|
|
$templateInfo,
|
|
$results,
|
|
isset($item['table_name']) ? $item['table_name'] : '',
|
|
isset($item['segment_name']) ? $item['segment_name'] : '',
|
|
$typeForm
|
|
);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Extract sub-details recursively
|
|
*/
|
|
function extract_sub_details($subDetails, $parentL1, $parentL2, $parentL3, $parentL4,
|
|
$templateInfo, &$results, $tableName, $segmentName, $typeForm)
|
|
{
|
|
foreach ($subDetails as $subItem) {
|
|
// Tentukan level berdasarkan parent yang terisi
|
|
$level = 3;
|
|
if ($parentL4 != '') $level = 5;
|
|
elseif ($parentL3 != '') $level = 4;
|
|
elseif ($parentL2 != '') $level = 3;
|
|
else $level = 2;
|
|
|
|
$result = array(
|
|
'FisikTemplateID' => $templateInfo['FisikTemplateID'],
|
|
'FisikTemplateCode' => $templateInfo['FisikTemplateCode'],
|
|
'FisikTemplateType' => $templateInfo['FisikTemplateType'],
|
|
'id_code' => isset($subItem['id_code']) ? $subItem['id_code'] : '',
|
|
'label' => isset($subItem['label']) ? $subItem['label'] : '',
|
|
'parent_level_1' => $parentL1,
|
|
'parent_level_2' => $parentL2,
|
|
'parent_level_3' => $parentL3,
|
|
'parent_level_4' => $parentL4,
|
|
'level' => $level,
|
|
'table_name' => isset($subItem['table_name']) ? $subItem['table_name'] : $tableName,
|
|
'segment_name' => isset($subItem['segment_name']) ? $subItem['segment_name'] : $segmentName,
|
|
'type_form' => isset($subItem['type_form']) ? $subItem['type_form'] : $typeForm
|
|
);
|
|
$results[] = $result;
|
|
|
|
// Cek sub-details lagi (rekursif)
|
|
if (isset($subItem['details']) && is_array($subItem['details'])) {
|
|
if ($level == 3) {
|
|
$this->extract_sub_details(
|
|
$subItem['details'],
|
|
$parentL1,
|
|
$parentL2,
|
|
$parentL3,
|
|
isset($subItem['label']) ? $subItem['label'] : '',
|
|
$templateInfo,
|
|
$results,
|
|
$tableName,
|
|
$segmentName,
|
|
$typeForm
|
|
);
|
|
} elseif ($level == 4) {
|
|
$this->extract_sub_details(
|
|
$subItem['details'],
|
|
$parentL1,
|
|
$parentL2,
|
|
$parentL3,
|
|
isset($subItem['label']) ? $subItem['label'] : '',
|
|
$templateInfo,
|
|
$results,
|
|
$tableName,
|
|
$segmentName,
|
|
$typeForm
|
|
);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
/**
|
|
* STEP 4a: Generate fisik template map data (semua template aktif)
|
|
* ------------------------------------------------------------------
|
|
* Endpoint: POST /generatedata/generate_fisik_template_map
|
|
* Request : No parameters
|
|
* Response: { message: "...", total_records: 250, total_templates: 9 }
|
|
*
|
|
* Proses:
|
|
* 1. Ambil SEMUA template aktif (FisikTemplateIsActive = 'Y')
|
|
* 2. Extract semua id_code, label, dan hierarchy dari JSON
|
|
* 3. TRUNCATE tabel fisik_template_map (hapus semua data lama)
|
|
* 4. Insert semua data baru secara batch
|
|
*
|
|
* Kapan digunakan:
|
|
* - Setup awal (pertama kali)
|
|
* - Regenerate semua data mapping
|
|
* - Sinkronisasi ulang semua template
|
|
*
|
|
* Note: Akan hapus SEMUA data lama dan insert ulang semua
|
|
*/
|
|
function generate_fisik_template_map()
|
|
{
|
|
// Cek apakah tabel sudah ada
|
|
if (!$this->db->table_exists('fisik_template_map')) {
|
|
$this->sys_error("Table fisik_template_map does not exist. Please create it first.");
|
|
exit;
|
|
}
|
|
|
|
$this->db->trans_begin();
|
|
|
|
try {
|
|
// Ambil data dari fisik_template menggunakan helper function
|
|
$templates = $this->get_active_fisik_templates();
|
|
|
|
if ($templates === false) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("select fisik_template", $this->db);
|
|
exit;
|
|
}
|
|
|
|
if (count($templates) == 0) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error("No active templates found");
|
|
exit;
|
|
}
|
|
|
|
$allResults = array();
|
|
$totalProcessed = 0;
|
|
|
|
foreach ($templates as $row) {
|
|
$templateInfo = array(
|
|
'FisikTemplateID' => $row['FisikTemplateID'],
|
|
'FisikTemplateCode' => $row['FisikTemplateCode'],
|
|
'FisikTemplateType' => $row['FisikTemplateType']
|
|
);
|
|
|
|
// Parse JSON
|
|
$jsonData = json_decode($row['FisikTemplateJSON'], true);
|
|
|
|
if ($jsonData === null) {
|
|
continue;
|
|
}
|
|
|
|
$results = array();
|
|
$this->extract_data_from_json($jsonData, $templateInfo, $results);
|
|
|
|
$allResults = array_merge($allResults, $results);
|
|
$totalProcessed += count($results);
|
|
}
|
|
|
|
// Truncate tabel sebelum insert
|
|
$this->db->truncate('fisik_template_map');
|
|
|
|
// Insert data
|
|
if (count($allResults) > 0) {
|
|
// Insert batch untuk performa lebih baik
|
|
$batchSize = 100;
|
|
$batches = array_chunk($allResults, $batchSize);
|
|
|
|
foreach ($batches as $batch) {
|
|
$this->db->insert_batch('fisik_template_map', $batch);
|
|
}
|
|
}
|
|
|
|
$this->db->trans_commit();
|
|
|
|
$result = array(
|
|
"message" => "Fisik template map generated successfully",
|
|
"total_records" => $totalProcessed,
|
|
"total_templates" => count($templates)
|
|
);
|
|
$this->sys_ok($result);
|
|
exit;
|
|
|
|
} catch (Exception $e) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error("Error: " . $e->getMessage());
|
|
exit;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* STEP 4b: Generate fisik template map untuk template tertentu saja
|
|
* -------------------------------------------------------------------
|
|
* Endpoint: POST /generatedata/generate_fisik_template_map_by_code
|
|
* Request : { "FisikTemplateCode": "RI00" }
|
|
* Response: { message: "...", template_code: "RI00", total_records: 9 }
|
|
*
|
|
* Proses:
|
|
* 1. Ambil satu template berdasarkan code (harus aktif)
|
|
* 2. Extract semua id_code, label, dan hierarchy dari JSON
|
|
* 3. DELETE hanya data template ini saja dari tabel
|
|
* 4. Insert data baru untuk template ini
|
|
*
|
|
* Kapan digunakan:
|
|
* - Update satu template saja (tidak perlu regenerate semua)
|
|
* - Ada perubahan di JSON satu template tertentu
|
|
* - Testing/debugging untuk satu template
|
|
*
|
|
* Keuntungan:
|
|
* - Lebih cepat (hanya proses satu template)
|
|
* - Tidak affect data template lain
|
|
* - Ideal untuk maintenance/update parsial
|
|
*
|
|
* Note: Hanya hapus & update data template yang dipilih saja
|
|
*/
|
|
function generate_fisik_template_map_by_code()
|
|
{
|
|
$prm = $this->sys_input;
|
|
$template_code = isset($prm['FisikTemplateCode']) ? $prm['FisikTemplateCode'] : '';
|
|
|
|
if ($template_code == '') {
|
|
$this->sys_error("FisikTemplateCode is required");
|
|
exit;
|
|
}
|
|
|
|
// Cek apakah tabel sudah ada
|
|
if (!$this->db->table_exists('fisik_template_map')) {
|
|
$this->sys_error("Table fisik_template_map does not exist. Please create it first.");
|
|
exit;
|
|
}
|
|
|
|
$this->db->trans_begin();
|
|
|
|
try {
|
|
// Ambil data template berdasarkan code
|
|
$templates = $this->get_active_fisik_templates(null, $template_code);
|
|
|
|
if ($templates === false) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("select fisik_template", $this->db);
|
|
exit;
|
|
}
|
|
|
|
if (count($templates) == 0) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error("Template with code '{$template_code}' not found or inactive");
|
|
exit;
|
|
}
|
|
|
|
$allResults = array();
|
|
$totalProcessed = 0;
|
|
|
|
foreach ($templates as $row) {
|
|
$templateInfo = array(
|
|
'FisikTemplateID' => $row['FisikTemplateID'],
|
|
'FisikTemplateCode' => $row['FisikTemplateCode'],
|
|
'FisikTemplateType' => $row['FisikTemplateType']
|
|
);
|
|
|
|
// Parse JSON
|
|
$jsonData = json_decode($row['FisikTemplateJSON'], true);
|
|
|
|
if ($jsonData === null) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error("Failed to parse JSON for template {$template_code}");
|
|
exit;
|
|
}
|
|
|
|
$results = array();
|
|
$this->extract_data_from_json($jsonData, $templateInfo, $results);
|
|
|
|
$allResults = array_merge($allResults, $results);
|
|
$totalProcessed += count($results);
|
|
}
|
|
|
|
// Hapus data lama untuk template ini
|
|
$this->db->where('FisikTemplateCode', $template_code);
|
|
$this->db->delete('fisik_template_map');
|
|
|
|
// Insert data baru
|
|
if (count($allResults) > 0) {
|
|
// Insert batch untuk performa lebih baik
|
|
$batchSize = 100;
|
|
$batches = array_chunk($allResults, $batchSize);
|
|
|
|
foreach ($batches as $batch) {
|
|
$this->db->insert_batch('fisik_template_map', $batch);
|
|
}
|
|
}
|
|
|
|
$this->db->trans_commit();
|
|
|
|
$result = array(
|
|
"message" => "Fisik template map generated successfully for {$template_code}",
|
|
"template_code" => $template_code,
|
|
"total_records" => $totalProcessed
|
|
);
|
|
$this->sys_ok($result);
|
|
exit;
|
|
|
|
} catch (Exception $e) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error("Error: " . $e->getMessage());
|
|
exit;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* STEP 1: List semua fisik template yang aktif
|
|
* ----------------------------------------------
|
|
* Endpoint: POST /generatedata/list_active_fisik_templates
|
|
* Request : No parameters
|
|
* Response: { message: "...", total: 9, data: [...] }
|
|
*
|
|
* Fungsi:
|
|
* - Menampilkan list semua template dengan FisikTemplateIsActive = 'Y'
|
|
* - Tidak termasuk FisikTemplateJSON (terlalu besar)
|
|
* - Info: ID, Code, Type, Title, TableName
|
|
*
|
|
* Kapan digunakan:
|
|
* - Lihat template apa saja yang aktif
|
|
* - Get list template code untuk parameter fungsi lain
|
|
* - Monitoring template yang available
|
|
*/
|
|
function list_active_fisik_templates()
|
|
{
|
|
$templates = $this->get_active_fisik_templates();
|
|
|
|
if ($templates === false) {
|
|
$this->sys_error_db("select fisik_template", $this->db);
|
|
exit;
|
|
}
|
|
|
|
// Hapus FisikTemplateJSON dari response (terlalu besar)
|
|
foreach ($templates as &$template) {
|
|
unset($template['FisikTemplateJSON']);
|
|
}
|
|
|
|
$result = array(
|
|
"message" => "Active templates retrieved successfully",
|
|
"total" => count($templates),
|
|
"data" => $templates
|
|
);
|
|
$this->sys_ok($result);
|
|
exit;
|
|
}
|
|
|
|
/**
|
|
* STEP 2: Get detail satu fisik template berdasarkan code
|
|
* ---------------------------------------------------------
|
|
* Endpoint: POST /generatedata/get_fisik_template_detail
|
|
* Request : { "FisikTemplateCode": "RI00" }
|
|
* Response: { FisikTemplateJSON_Parsed: {...}, ... }
|
|
*
|
|
* Fungsi:
|
|
* - Menampilkan detail lengkap satu template
|
|
* - Termasuk FisikTemplateJSON yang sudah di-parse
|
|
* - Untuk melihat struktur JSON sebelum generate mapping
|
|
*
|
|
* Kapan digunakan:
|
|
* - Debug/inspect struktur JSON template
|
|
* - Validasi JSON sebelum generate
|
|
* - Melihat detail template tertentu
|
|
*/
|
|
function get_fisik_template_detail()
|
|
{
|
|
$prm = $this->sys_input;
|
|
$template_code = isset($prm['FisikTemplateCode']) ? $prm['FisikTemplateCode'] : '';
|
|
|
|
if ($template_code == '') {
|
|
$this->sys_error("FisikTemplateCode is required");
|
|
exit;
|
|
}
|
|
|
|
$templates = $this->get_active_fisik_templates(null, $template_code);
|
|
|
|
if ($templates === false) {
|
|
$this->sys_error_db("select fisik_template", $this->db);
|
|
exit;
|
|
}
|
|
|
|
if (count($templates) == 0) {
|
|
$this->sys_error("Template with code '{$template_code}' not found or inactive");
|
|
exit;
|
|
}
|
|
|
|
$template = $templates[0];
|
|
|
|
// Parse JSON untuk ditampilkan
|
|
$template['FisikTemplateJSON_Parsed'] = json_decode($template['FisikTemplateJSON'], true);
|
|
|
|
$result = $template;
|
|
$this->sys_ok($result);
|
|
exit;
|
|
}
|
|
|
|
/**
|
|
* STEP 5a: Get fisik template map data
|
|
* -------------------------------------
|
|
* Endpoint: POST /generatedata/get_fisik_template_map
|
|
* Request : { "FisikTemplateCode": "RI00", "level": 2 } (opsional)
|
|
* Response: Array of mapping data
|
|
*
|
|
* Filter opsional:
|
|
* - FisikTemplateCode : Filter berdasarkan template tertentu
|
|
* - level : Filter berdasarkan level (1, 2, 3, 4, 5)
|
|
*
|
|
* Contoh request:
|
|
* 1. Semua data : {}
|
|
* 2. Template RI00 : { "FisikTemplateCode": "RI00" }
|
|
* 3. Level 2 saja : { "level": 2 }
|
|
* 4. RI00 level 2 : { "FisikTemplateCode": "RI00", "level": 2 }
|
|
*
|
|
* Kapan digunakan:
|
|
* - Query data mapping untuk ditampilkan
|
|
* - Get list items untuk satu template
|
|
* - Filter berdasarkan level hierarchy
|
|
*/
|
|
function get_fisik_template_map()
|
|
{
|
|
$prm = $this->sys_input;
|
|
$template_code = isset($prm['FisikTemplateCode']) ? $prm['FisikTemplateCode'] : '';
|
|
$level = isset($prm['level']) ? $prm['level'] : '';
|
|
|
|
$sql = "SELECT * FROM fisik_template_map WHERE 1=1";
|
|
$params = array();
|
|
|
|
if ($template_code != '') {
|
|
$sql .= " AND FisikTemplateCode = ?";
|
|
$params[] = $template_code;
|
|
}
|
|
|
|
if ($level != '') {
|
|
$sql .= " AND level = ?";
|
|
$params[] = $level;
|
|
}
|
|
|
|
$sql .= " ORDER BY MapID";
|
|
|
|
$query = $this->db->query($sql, $params);
|
|
|
|
if(!$query){
|
|
$this->sys_error_db("select fisik_template_map", $this->db);
|
|
exit;
|
|
}
|
|
|
|
$result = $query->result_array();
|
|
$this->sys_ok($result);
|
|
exit;
|
|
}
|
|
|
|
/**
|
|
* STEP 5b: Search fisik template map by id_code or label
|
|
* --------------------------------------------------------
|
|
* Endpoint: POST /generatedata/search_fisik_template_map
|
|
* Request : { "keyword": "perut" }
|
|
* Response: Array of matching records
|
|
*
|
|
* Fungsi:
|
|
* - Search berdasarkan id_code atau label (LIKE %keyword%)
|
|
* - Case insensitive search
|
|
* - Return semua matching records dengan full info
|
|
*
|
|
* Contoh penggunaan:
|
|
* 1. Cari "perut" : Find semua item dengan id_code/label mengandung "perut"
|
|
* 2. Cari "perut_14" : Find item dengan id_code perut_14*
|
|
* 3. Cari "Hernia" : Find item dengan label mengandung "Hernia"
|
|
*
|
|
* Kapan digunakan:
|
|
* - Cari item tertentu berdasarkan keyword
|
|
* - Lookup id_code untuk mapping
|
|
* - Cari label untuk matching data
|
|
*
|
|
* Note: Keyword wajib diisi
|
|
*/
|
|
function search_fisik_template_map()
|
|
{
|
|
$prm = $this->sys_input;
|
|
$keyword = isset($prm['keyword']) ? $prm['keyword'] : '';
|
|
|
|
if ($keyword == '') {
|
|
$this->sys_error("Keyword is required");
|
|
exit;
|
|
}
|
|
|
|
$sql = "SELECT * FROM fisik_template_map
|
|
WHERE id_code LIKE ? OR label LIKE ?
|
|
ORDER BY FisikTemplateCode, level, MapID";
|
|
|
|
$query = $this->db->query($sql, ["%{$keyword}%", "%{$keyword}%"]);
|
|
|
|
if(!$query){
|
|
$this->sys_error_db("search fisik_template_map", $this->db);
|
|
exit;
|
|
}
|
|
|
|
$result = $query->result_array();
|
|
$this->sys_ok($result);
|
|
exit;
|
|
}
|
|
|
|
function generate_result_mcu_lab(){
|
|
$prm = $this->sys_input;
|
|
$mgm_mcuid = $prm['Mgm_McuID'];
|
|
|
|
$sql = "SELECT *
|
|
FROM t_orderheader
|
|
WHERE
|
|
T_OrderheaderMgm_McuID = ? AND T_OrderHeaderIsActive = 'Y'";
|
|
$query = $this->db->query($sql, [$mgm_mcuid]);
|
|
if(!$query){
|
|
$this->sys_error_db("select t_orderheader", $this->db);
|
|
exit;
|
|
}
|
|
$result = $query->result_array();
|
|
if(count($result) == 0){
|
|
$this->sys_error("No data found");
|
|
exit;
|
|
}
|
|
|
|
foreach ($result as $row) {
|
|
$this->generate_result_lab($row['T_OrderHeaderID'], $row['T_OrderHeaderLabNumber']);
|
|
}
|
|
|
|
$this->sys_ok($result);
|
|
exit;
|
|
}
|
|
|
|
function generate_result_mcu_nonlab(){
|
|
$prm = $this->sys_input;
|
|
$mgm_mcuid = $prm['Mgm_McuID'];
|
|
|
|
$this->load->library('mcuresult');
|
|
|
|
$sql = "SELECT So_ResultEntryID, So_ResultEntryT_OrderHeaderID, So_ResultEntryT_OrderDetailID
|
|
FROM so_resultentry
|
|
JOIN t_orderheader ON So_ResultEntryT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y' AND
|
|
T_OrderHeaderMgm_McuID = ? AND So_ResultEntryT_OrderHeaderID = T_OrderHeaderID
|
|
JOIN t_orderdetail ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID AND T_OrderDetailIsActive = 'Y' AND
|
|
So_ResultEntryT_OrderDetailID = T_OrderDetailID AND T_OrderDetailIsActive = 'Y'
|
|
JOIN group_resultdetail ON Group_ResultDetailT_TestID = T_OrderDetailT_TestID AND Group_ResultDetailIsActive = 'Y'
|
|
JOIN group_result ON Group_ResultDetailGroup_ResultID = Group_ResultID AND Group_ResultID NOT IN (35,27)
|
|
WHERE
|
|
So_ResultEntryIsActive = 'Y' AND So_ResultEntryStatus <> 'NEW'
|
|
GROUP BY So_ResultEntryT_OrderHeaderID, So_ResultEntryT_OrderDetailID
|
|
";
|
|
$query = $this->db->query($sql, [$mgm_mcuid]);
|
|
if(!$query){
|
|
$this->sys_error_db("select t_orderheader", $this->db);
|
|
exit;
|
|
}
|
|
$result = $query->result_array();
|
|
if(count($result) == 0){
|
|
$this->sys_error("No data found");
|
|
exit;
|
|
}
|
|
|
|
foreach ($result as $row) {
|
|
//$this->generate_result_lab($row['T_OrderHeaderID'], $row['T_OrderHeaderLabNumber']);
|
|
$this->mcuresult->generate_result_nonlab($row['So_ResultEntryID']);
|
|
}
|
|
|
|
$this->sys_ok($result);
|
|
exit;
|
|
}
|
|
|
|
function generate_result_mcu_kenal_warna(){
|
|
$prm = $this->sys_input;
|
|
$mgm_mcuid = $prm['Mgm_McuID'];
|
|
|
|
$this->load->library('mcuresult');
|
|
|
|
$sql = "SELECT T_OrderHeaderID, T_OrderHeaderLabNumber, T_OrderHeaderMgm_McuID as Mgm_McuID
|
|
FROM t_orderheader
|
|
WHERE T_OrderHeaderMgm_McuID = ? AND T_OrderHeaderIsActive = 'Y'
|
|
";
|
|
$query = $this->db->query($sql, [$mgm_mcuid]);
|
|
if(!$query){
|
|
$this->sys_error_db("select t_orderheader", $this->db);
|
|
exit;
|
|
}
|
|
$result = $query->result_array();
|
|
if(count($result) == 0){
|
|
$this->sys_error("No data found");
|
|
exit;
|
|
}
|
|
|
|
foreach ($result as $row) {
|
|
//$this->generate_result_lab($row['T_OrderHeaderID'], $row['T_OrderHeaderLabNumber']);
|
|
$this->mcuresult->generate_kenal_warna($row);
|
|
}
|
|
|
|
$this->sys_ok($result);
|
|
exit;
|
|
}
|
|
|
|
function generate_result_mcu_visus(){
|
|
$prm = $this->sys_input;
|
|
$mgm_mcuid = $prm['Mgm_McuID'];
|
|
|
|
$this->load->library('mcuresult');
|
|
|
|
$sql = "SELECT T_OrderHeaderID, T_OrderHeaderLabNumber, T_OrderHeaderMgm_McuID as Mgm_McuID
|
|
FROM t_orderheader
|
|
WHERE T_OrderHeaderMgm_McuID = ? AND T_OrderHeaderIsActive = 'Y'
|
|
|
|
";
|
|
$query = $this->db->query($sql, [$mgm_mcuid]);
|
|
if(!$query){
|
|
$this->sys_error_db("select t_orderheader", $this->db);
|
|
exit;
|
|
}
|
|
$result = $query->result_array();
|
|
if(count($result) == 0){
|
|
$this->sys_error("No data found");
|
|
exit;
|
|
}
|
|
|
|
foreach ($result as $row) {
|
|
//$this->generate_result_lab($row['T_OrderHeaderID'], $row['T_OrderHeaderLabNumber']);
|
|
$this->mcuresult->generate_visus($row);
|
|
}
|
|
|
|
$this->sys_ok($result);
|
|
exit;
|
|
}
|
|
|
|
function generate_result_mcu_status_gizi(){
|
|
$prm = $this->sys_input;
|
|
$mgm_mcuid = $prm['Mgm_McuID'];
|
|
|
|
$this->load->library('mcuresult');
|
|
|
|
$sql = "SELECT T_OrderHeaderID, T_OrderHeaderLabNumber, T_OrderHeaderMgm_McuID as Mgm_McuID
|
|
FROM t_orderheader
|
|
JOIN t_orderdetail ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID AND T_OrderDetailIsActive = 'Y' AND
|
|
T_OrderDetailT_TestCode = '40110901'
|
|
WHERE T_OrderHeaderMgm_McuID = ? AND T_OrderHeaderIsActive = 'Y'
|
|
GROUP BY T_OrderHeaderID
|
|
LIMIT 1";
|
|
$query = $this->db->query($sql, [$mgm_mcuid]);
|
|
if(!$query){
|
|
$this->sys_error_db("select t_orderheader", $this->db);
|
|
exit;
|
|
}
|
|
$result = $query->result_array();
|
|
if(count($result) == 0){
|
|
$this->sys_error("No data found");
|
|
exit;
|
|
}
|
|
|
|
foreach ($result as $row) {
|
|
//$this->generate_result_lab($row['T_OrderHeaderID'], $row['T_OrderHeaderLabNumber']);
|
|
$this->mcuresult->generate_status_gizi($row);
|
|
}
|
|
|
|
$this->sys_ok($result);
|
|
exit;
|
|
}
|
|
|
|
function generate_result_mcu_bodyfatmonitoring(){
|
|
$prm = $this->sys_input;
|
|
$mgm_mcuid = $prm['Mgm_McuID'];
|
|
|
|
$this->load->library('mcuresult');
|
|
|
|
$sql = "SELECT T_OrderHeaderID, T_OrderHeaderLabNumber, T_OrderHeaderMgm_McuID as Mgm_McuID
|
|
FROM t_orderheader
|
|
JOIN t_orderdetail ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID AND T_OrderDetailIsActive = 'Y' AND
|
|
T_OrderDetailT_TestCode = '40110902'
|
|
WHERE T_OrderHeaderMgm_McuID = ? AND T_OrderHeaderIsActive = 'Y'
|
|
GROUP BY T_OrderHeaderID
|
|
LIMIT 1
|
|
";
|
|
$query = $this->db->query($sql, [$mgm_mcuid]);
|
|
if(!$query){
|
|
$this->sys_error_db("select t_orderheader", $this->db);
|
|
exit;
|
|
}
|
|
$result = $query->result_array();
|
|
if(count($result) == 0){
|
|
$this->sys_error("No data found");
|
|
exit;
|
|
}
|
|
|
|
foreach ($result as $row) {
|
|
//$this->generate_result_lab($row['T_OrderHeaderID'], $row['T_OrderHeaderLabNumber']);
|
|
$this->mcuresult->generate_bodyfatmonitoring($row);
|
|
}
|
|
|
|
$this->sys_ok($result);
|
|
exit;
|
|
}
|
|
|
|
function generate_result_mcu_fisik(){
|
|
$prm = $this->sys_input;
|
|
$mgm_mcuid = $prm['Mgm_McuID'];
|
|
|
|
$this->load->library('mcuresult');
|
|
|
|
$sql = "SELECT So_ResultEntryID, So_ResultEntryT_OrderHeaderID, So_ResultEntryT_OrderDetailID
|
|
FROM so_resultentry
|
|
JOIN t_orderheader ON So_ResultEntryT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y' AND
|
|
T_OrderHeaderMgm_McuID = ? AND So_ResultEntryT_OrderHeaderID = T_OrderHeaderID
|
|
JOIN t_orderdetail ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID AND T_OrderDetailIsActive = 'Y' AND
|
|
So_ResultEntryT_OrderDetailID = T_OrderDetailID AND T_OrderDetailIsActive = 'Y'
|
|
JOIN group_resultdetail ON Group_ResultDetailT_TestID = T_OrderDetailT_TestID AND Group_ResultDetailIsActive = 'Y'
|
|
JOIN group_result ON Group_ResultDetailGroup_ResultID = Group_ResultID
|
|
WHERE
|
|
So_ResultEntryIsActive = 'Y' AND So_ResultEntryStatus <> 'NEW' AND So_ResultEntryNonlab_TemplateID = 27
|
|
GROUP BY So_ResultEntryT_OrderHeaderID, So_ResultEntryT_OrderDetailID
|
|
";
|
|
$query = $this->db->query($sql, [$mgm_mcuid]);
|
|
if(!$query){
|
|
$this->sys_error_db("select t_orderheader", $this->db);
|
|
exit;
|
|
}
|
|
$result = $query->result_array();
|
|
if(count($result) == 0){
|
|
$this->sys_error("No data found");
|
|
exit;
|
|
}
|
|
|
|
foreach ($result as $row) {
|
|
//$this->generate_result_lab($row['T_OrderHeaderID'], $row['T_OrderHeaderLabNumber']);
|
|
$this->mcuresult->get_data_fisik_resultentry($row['So_ResultEntryID']);
|
|
}
|
|
|
|
$this->sys_ok($result);
|
|
exit;
|
|
}
|
|
|
|
|
|
|
|
function generate_result_lab($T_OrderHeaderID = 0,$T_OrderHeaderLabNumber = '')
|
|
{
|
|
|
|
|
|
$this->db_log->trans_begin();
|
|
|
|
$sql = "SELECT T_OrderHeaderMgm_McuID as Mgm_McuID,
|
|
T_OrderHeaderID,
|
|
T_OrderHeaderLabNumber,
|
|
T_OrderDetailID as ReffID,
|
|
Group_ResultID,
|
|
Group_ResultName,
|
|
T_TestCode,
|
|
T_TestName,
|
|
IFNULL(T_OrderDetailResult,'') as Result,
|
|
IFNULL(T_OrderDetailNat_UnitName,'') as UnitName,
|
|
IFNULL(T_OrderDetailNormalValueNote,'') as refference
|
|
FROM t_orderdetail
|
|
JOIN t_orderheader ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID AND
|
|
( T_OrderHeaderID = ? OR T_OrderHeaderLabNumber = ?)
|
|
JOIN t_test ON T_OrderDetailT_TestID = T_TestID
|
|
JOIN group_resultdetail ON Group_ResultDetailT_TestID = T_TestID AND Group_ResultDetailIsactive = 'Y'
|
|
JOIN group_result ON Group_ResultDetailGroup_ResultID = Group_ResultID AND Group_ResultIsActive = 'Y' AND Group_ResultResumeMcu = 'LAB'
|
|
WHERE
|
|
T_OrderDetailIsActive = 'Y'
|
|
ORDER BY T_OrderDetailT_TestSasCode ASC";
|
|
$query = $this->db->query($sql, [$T_OrderHeaderID, $T_OrderHeaderLabNumber]);
|
|
|
|
$result = $query->result_array();
|
|
if(count($result) == 0){
|
|
$results = array("message" => "No data found", "data" => array());
|
|
$this->sys_ok($results);
|
|
exit;
|
|
}
|
|
|
|
foreach ($result as $row) {
|
|
$sql = "SELECT * FROM mcu_result_all WHERE
|
|
Mcu_ResultAllMgm_McuID = ? AND
|
|
Mcu_ResultAllT_OrderHeaderID = ? AND
|
|
Mcu_ResultAllT_OrderHeaderLabNumber = ? AND
|
|
Mcu_ResultAllReffID = ? AND
|
|
Mcu_ResultAllT_TestCode = ? AND
|
|
Mcu_ResultAllT_TestName = ?";
|
|
$query = $this->db_log->query($sql, [
|
|
$row['Mgm_McuID'],
|
|
$row['T_OrderHeaderID'],
|
|
$row['T_OrderHeaderLabNumber'],
|
|
$row['ReffID'],
|
|
$row['T_TestCode'],
|
|
$row['T_TestName']
|
|
]);
|
|
|
|
$exist_result = $query->result_array();
|
|
if(count($exist_result) > 0){
|
|
foreach ($exist_result as $key => $row_exist) {
|
|
$sql = "DELETE FROM mcu_result_all WHERE
|
|
Mcu_ResultAllID = ?";
|
|
$query = $this->db_log->query($sql, [$row_exist['Mcu_ResultAllID']]);
|
|
if(!$query){
|
|
$this->db_log->trans_rollback();
|
|
$this->sys_error_db("delete mcu_result_".$row_exist['Mcu_ResultAllT_TestCode'], $this->db_log);
|
|
exit;
|
|
}
|
|
}
|
|
}
|
|
|
|
$sql = "INSERT INTO mcu_result_all (
|
|
Mcu_ResultAllMgm_McuID,
|
|
Mcu_ResultAllT_OrderHeaderID,
|
|
Mcu_ResultAllT_OrderHeaderLabNumber,
|
|
Mcu_ResultAllReffID,
|
|
Mcu_ResultAllT_TestCode,
|
|
Mcu_ResultAllT_TestName,
|
|
Mcu_ResultAllResult,
|
|
Mcu_ResultAllUnit,
|
|
Mcu_ResultAllRefference,
|
|
Mcu_ResultAllCreated
|
|
)
|
|
VALUES(?,?,?,?,?,?,?,?,?,NOW())";
|
|
$query = $this->db_log->query($sql, [
|
|
$row['Mgm_McuID'],
|
|
$row['T_OrderHeaderID'],
|
|
$row['T_OrderHeaderLabNumber'],
|
|
$row['ReffID'],
|
|
$row['T_TestCode'],
|
|
$row['T_TestName'],
|
|
$row['Result'],
|
|
$row['UnitName'],
|
|
$row['refference']
|
|
]);
|
|
|
|
if(!$query){
|
|
$this->db_log->trans_rollback();
|
|
$this->sys_error_db("insert mcu_result_".$row['T_TestCode'], $this->db_log);
|
|
exit;
|
|
}
|
|
|
|
}
|
|
|
|
|
|
$this->db_log->trans_commit();
|
|
|
|
$results = array("message" => "Data generated successfully", "data" => $result);
|
|
return $results;
|
|
|
|
}
|
|
|
|
function generate_sqlite_by_mgm_mcuid()
|
|
{
|
|
// Get MGM_MCUID from input
|
|
$prm = $this->sys_input;
|
|
$mgm_mcuid = isset($prm['Mgm_McuID']) ? $prm['Mgm_McuID'] : 0;
|
|
|
|
if (empty($mgm_mcuid)) {
|
|
$this->sys_error("Mgm_McuID is required");
|
|
exit;
|
|
}
|
|
|
|
// Get table name from mcu_number
|
|
$sql = "SELECT * FROM mcu_number WHERE Mcu_NumberMgm_McuID = ? AND Mcu_NumberIsActive = 'Y' LIMIT 1";
|
|
$query = $this->db_corporate->query($sql, [$mgm_mcuid]);
|
|
|
|
if ($query->num_rows() == 0) {
|
|
$this->sys_error("MCU Number not found for Mgm_McuID: " . $mgm_mcuid);
|
|
exit;
|
|
}
|
|
|
|
$mcu_data = $query->row_array();
|
|
$table_suffix = $mcu_data['Mcu_NumberTableName'];
|
|
$mcu_number = $mcu_data['Mcu_NumberMgm_McuNumber'];
|
|
|
|
// Define output file path
|
|
$output_dir = FCPATH; // Root directory
|
|
$output_file = $output_dir . 'mcu_' . $table_suffix . '.sqlite';
|
|
|
|
// Remove existing file if exists
|
|
if (file_exists($output_file)) {
|
|
unlink($output_file);
|
|
}
|
|
|
|
try {
|
|
// Create SQLite database
|
|
$sqlite = new SQLite3($output_file);
|
|
$sqlite->exec('PRAGMA foreign_keys = OFF;');
|
|
$sqlite->exec('BEGIN TRANSACTION;');
|
|
|
|
// Table names to export
|
|
$tables_to_export = [
|
|
'kelainan_details',
|
|
'kelainan_summary',
|
|
'mcu_result_all'
|
|
];
|
|
|
|
foreach ($tables_to_export as $table_name) {
|
|
// Check if table exists
|
|
$check_sql = "SHOW TABLES LIKE ?";
|
|
$check_query = $this->db_corporate->query($check_sql, [$table_name]);
|
|
|
|
if ($check_query->num_rows() == 0) {
|
|
continue; // Skip if table doesn't exist
|
|
}
|
|
|
|
// Get table structure
|
|
$structure_sql = "SHOW CREATE TABLE `{$table_name}`";
|
|
$structure_query = $this->db_corporate->query($structure_sql);
|
|
$structure_row = $structure_query->row_array();
|
|
$create_table_sql = $structure_row['Create Table'];
|
|
|
|
// Convert MySQL to SQLite syntax
|
|
$create_table_sql = $this->convert_mysql_to_sqlite($create_table_sql, $table_name);
|
|
|
|
// Create table in SQLite
|
|
$sqlite->exec($create_table_sql);
|
|
|
|
// Get data from MySQL
|
|
$data_sql = "SELECT * FROM `{$table_name}`";
|
|
$data_query = $this->db_corporate->query($data_sql);
|
|
$rows = $data_query->result_array();
|
|
|
|
if (count($rows) > 0) {
|
|
// Prepare insert statement
|
|
$columns = array_keys($rows[0]);
|
|
$placeholders = implode(',', array_fill(0, count($columns), '?'));
|
|
$insert_sql = "INSERT INTO {$table_name} (" . implode(',', $columns) . ") VALUES ({$placeholders})";
|
|
|
|
$stmt = $sqlite->prepare($insert_sql);
|
|
|
|
foreach ($rows as $row) {
|
|
$i = 1;
|
|
foreach ($row as $value) {
|
|
$stmt->bindValue($i++, $value);
|
|
}
|
|
$stmt->execute();
|
|
$stmt->reset();
|
|
}
|
|
$stmt->close();
|
|
}
|
|
}
|
|
|
|
// Also include mcu_number table with current record only
|
|
// Get mcu_number table structure from MySQL
|
|
$mcu_structure_sql = "SHOW CREATE TABLE mcu_number";
|
|
$mcu_structure_query = $this->db_corporate->query($mcu_structure_sql);
|
|
$mcu_structure_row = $mcu_structure_query->row_array();
|
|
$mcu_create_table_sql = $mcu_structure_row['Create Table'];
|
|
|
|
// Convert MySQL to SQLite syntax
|
|
$mcu_create_table_sql = $this->convert_mysql_to_sqlite($mcu_create_table_sql, 'mcu_number');
|
|
$sqlite->exec($mcu_create_table_sql);
|
|
|
|
// Insert current mcu_number record (only the one matching Mgm_McuID)
|
|
$columns = array_keys($mcu_data);
|
|
$placeholders = implode(',', array_fill(0, count($columns), '?'));
|
|
$mcu_insert_sql = "INSERT INTO mcu_number (" . implode(',', $columns) . ") VALUES ({$placeholders})";
|
|
|
|
$stmt = $sqlite->prepare($mcu_insert_sql);
|
|
$i = 1;
|
|
foreach ($mcu_data as $value) {
|
|
$stmt->bindValue($i++, $value);
|
|
}
|
|
$stmt->execute();
|
|
$stmt->close();
|
|
|
|
$sqlite->exec('COMMIT;');
|
|
$sqlite->close();
|
|
|
|
// Return success with file path
|
|
$all_tables_exported = array_merge($tables_to_export, ['mcu_number']);
|
|
$results = [
|
|
"message" => "SQLite file generated successfully",
|
|
"file" => basename($output_file),
|
|
"path" => $output_file,
|
|
"table_suffix" => $table_suffix,
|
|
"mcu_number" => $mcu_number,
|
|
"mgm_mcuid" => $mgm_mcuid,
|
|
"tables_exported" => $all_tables_exported,
|
|
"note" => "Table mcu_number only contains the record for Mgm_McuID: " . $mgm_mcuid
|
|
];
|
|
|
|
$this->sys_ok($results);
|
|
exit;
|
|
|
|
} catch (Exception $e) {
|
|
$this->sys_error("Error generating SQLite: " . $e->getMessage());
|
|
exit;
|
|
}
|
|
}
|
|
|
|
function convert_mysql_to_sqlite($create_sql, $table_name)
|
|
{
|
|
// Remove MySQL specific syntax
|
|
$create_sql = preg_replace('/ENGINE=\w+/', '', $create_sql);
|
|
$create_sql = preg_replace('/DEFAULT CHARSET=\w+/', '', $create_sql);
|
|
$create_sql = preg_replace('/AUTO_INCREMENT=\d+/', '', $create_sql);
|
|
$create_sql = preg_replace('/COLLATE=\w+/', '', $create_sql);
|
|
|
|
// Convert data types
|
|
$create_sql = preg_replace('/INT\(\d+\)/', 'INTEGER', $create_sql);
|
|
$create_sql = preg_replace('/TINYINT\(\d+\)/', 'INTEGER', $create_sql);
|
|
$create_sql = preg_replace('/SMALLINT\(\d+\)/', 'INTEGER', $create_sql);
|
|
$create_sql = preg_replace('/MEDIUMINT\(\d+\)/', 'INTEGER', $create_sql);
|
|
$create_sql = preg_replace('/BIGINT\(\d+\)/', 'INTEGER', $create_sql);
|
|
$create_sql = preg_replace('/VARCHAR\(\d+\)/', 'TEXT', $create_sql);
|
|
$create_sql = preg_replace('/DATETIME/', 'DATETIME', $create_sql);
|
|
$create_sql = preg_replace('/TIMESTAMP/', 'DATETIME', $create_sql);
|
|
$create_sql = preg_replace('/DATE/', 'DATE', $create_sql);
|
|
$create_sql = preg_replace('/TEXT/', 'TEXT', $create_sql);
|
|
$create_sql = preg_replace('/LONGTEXT/', 'TEXT', $create_sql);
|
|
$create_sql = preg_replace('/MEDIUMTEXT/', 'TEXT', $create_sql);
|
|
|
|
// Remove backticks
|
|
$create_sql = str_replace('`', '', $create_sql);
|
|
|
|
// Handle AUTO_INCREMENT
|
|
$create_sql = preg_replace('/\s+AUTO_INCREMENT\s*/i', ' AUTOINCREMENT ', $create_sql);
|
|
|
|
// Clean up
|
|
$create_sql = preg_replace('/,\s*\)/', ')', $create_sql);
|
|
$create_sql = preg_replace('/\s+/', ' ', $create_sql);
|
|
|
|
return $create_sql;
|
|
}
|
|
|
|
|
|
}
|