303 lines
8.8 KiB
PHP
303 lines
8.8 KiB
PHP
<?php
|
|
class Sqlgenerator extends MY_Controller
|
|
{
|
|
public function __construct()
|
|
{
|
|
// Memanggil kontruktor parent dari MY_Controller
|
|
parent::__construct();
|
|
// Memuat koneksi database bernama onedev
|
|
$this->db = $this->load->database("onedev", true);
|
|
// Memuat koneksi database bernama cpone_corporate
|
|
$this->db_corporate = $this->load->database("cpone_corporate", true);
|
|
// Memuat koneksi database bernama log
|
|
$this->db_log = $this->load->database("log", true);
|
|
}
|
|
|
|
/**
|
|
* STEP: Generate data list Corporate
|
|
* ---------------------------------------------
|
|
* Endpoint: POST /sqlgenerator/dump_database
|
|
* Request : { "db_name": "cpone_corporate" }
|
|
* Response: { "message": "...", "sql": "CREATE TABLE ...; INSERT INTO ...;" }
|
|
*
|
|
* Fungsi:
|
|
* - Generate SQL untuk semua tabel di database MySQL
|
|
* - Termasuk struktur tabel (CREATE) dan isi tabel (INSERT)
|
|
* - Bisa digunakan untuk backup atau migrasi database
|
|
*/
|
|
public function list_mcu_corporate()
|
|
{
|
|
// Ambil input POST
|
|
$db_name = $this->sys_input['db_name'] ?? '';
|
|
|
|
// Validasi
|
|
if ($db_name == '') {
|
|
echo json_encode([
|
|
"status" => false,
|
|
"error" => "db_name is required"
|
|
]);
|
|
return;
|
|
}
|
|
|
|
// Tentukan koneksi database
|
|
switch ($db_name) {
|
|
case 'cpone_corporate':
|
|
$db = $this->db_corporate;
|
|
break;
|
|
|
|
case 'onedev':
|
|
$db = $this->db_onedev;
|
|
break;
|
|
|
|
case 'log':
|
|
$db = $this->db_log;
|
|
break;
|
|
|
|
default:
|
|
echo json_encode([
|
|
"status" => false,
|
|
"error" => "Invalid db_name: $db_name"
|
|
]);
|
|
return;
|
|
}
|
|
|
|
// Eksekusi Query
|
|
$query = $db->select('Mcu_NumberID, Mcu_NumberMgm_McuID ,Mcu_NumberCorporateName, Mcu_NumberStartDate, Mcu_NumberEndDate')
|
|
->from('mcu_number')
|
|
->order_by('Mcu_NumberID', 'DESC')
|
|
->get();
|
|
|
|
// Cek error DB
|
|
$dbError = $db->error();
|
|
if ($dbError['code'] !== 0) {
|
|
echo json_encode([
|
|
"status" => false,
|
|
"error_code" => $dbError['code'],
|
|
"error_message" => $dbError['message']
|
|
]);
|
|
return;
|
|
}
|
|
|
|
// Response OK
|
|
echo json_encode([
|
|
"status" => true,
|
|
"data" => $query->result()
|
|
]);
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
function list_pasien_mcu()
|
|
{
|
|
// Query untuk mengambil data pasien dari kelainan_details
|
|
$sql = "
|
|
SELECT
|
|
Mcu_KelainanID,
|
|
M_PatientID,
|
|
M_PatientName
|
|
FROM kelainan_details
|
|
WHERE Mcu_KelainanID IS NOT NULL
|
|
ORDER BY
|
|
Mcu_KelainanID ASC,
|
|
M_PatientID ASC
|
|
";
|
|
|
|
// Eksekusi query
|
|
$query = $this->db_corporate->query($sql);
|
|
|
|
// Error handling
|
|
if (!$query) {
|
|
$this->sys_error_db("Failed to get patient list", $this->db_corporate);
|
|
exit;
|
|
}
|
|
|
|
// Ambil hasil
|
|
$result = $query->result_array();
|
|
|
|
// Response sukses
|
|
$this->sys_ok($result);
|
|
exit;
|
|
}
|
|
|
|
function generate_count_kelainan(): never
|
|
{
|
|
$sql = "
|
|
SELECT
|
|
Mcu_KelainanID,
|
|
Mcu_KelainanName,
|
|
COUNT(T_OrderHeaderID)
|
|
FROM kelainan_details
|
|
GROUP BY Mcu_KelainanID
|
|
";
|
|
$query = $this->db_corporate->query($sql);
|
|
if (!$query) {
|
|
$this->sys_error_db("Failed to generate count kelainan", $this->db_corporate);
|
|
exit;
|
|
}
|
|
$result = $query->result_array();
|
|
$this->sys_ok($result);
|
|
exit;
|
|
|
|
}
|
|
|
|
|
|
function total_pasien_mcu()
|
|
{
|
|
// Query hitung total pasien unik berdasarkan T_OrderHeaderID
|
|
$sql = "
|
|
SELECT COUNT(DISTINCT T_OrderHeaderID) AS total_pasien
|
|
FROM kelainan_details
|
|
WHERE Mcu_KelainanID IS NOT NULL
|
|
";
|
|
|
|
$query = $this->db_corporate->query($sql);
|
|
|
|
if (!$query) {
|
|
$this->sys_error_db("Failed to count total patients", $this->db_corporate);
|
|
exit;
|
|
}
|
|
|
|
$row = $query->row_array();
|
|
|
|
// Response success
|
|
$this->sys_ok([
|
|
"total_pasien" => intval($row['total_pasien'])
|
|
]);
|
|
exit;
|
|
}
|
|
|
|
public function select_sepuluh_pasien()
|
|
{
|
|
// Ambil input JSON
|
|
$prm = $this->sys_input;
|
|
|
|
if (!isset($prm['pasien_id']) || empty($prm['pasien_id'])) {
|
|
$this->sys_error("Parameter pasien_id wajib diisi.");
|
|
exit;
|
|
}
|
|
|
|
// Pisahkan string menjadi array
|
|
$pasien_ids = array_map('trim', explode(',', $prm['pasien_id']));
|
|
|
|
// Pastikan minimal 10 pasien
|
|
if (count($pasien_ids) < 10) {
|
|
$this->sys_error("Data pasien tidak cukup, minimal 10 pasien dibutuhkan.");
|
|
exit;
|
|
}
|
|
|
|
// Ambil data pasien sesuai ID
|
|
$sql = "
|
|
SELECT
|
|
Mcu_KelainanID,
|
|
M_PatientID,
|
|
M_PatientName
|
|
FROM kelainan_details
|
|
WHERE M_PatientID IN (" . implode(',', array_map('intval', $pasien_ids)) . ")
|
|
ORDER BY M_PatientID ASC
|
|
";
|
|
|
|
$query = $this->db_corporate->query($sql);
|
|
|
|
if (!$query) {
|
|
$this->sys_error_db("Failed to select patients", $this->db_corporate);
|
|
exit;
|
|
}
|
|
|
|
$result = $query->result_array();
|
|
|
|
// Response sukses
|
|
$this->sys_ok($result);
|
|
}
|
|
|
|
/**
|
|
* STEP: Dump seluruh database (struktur & data)
|
|
* ---------------------------------------------
|
|
* Endpoint: POST /sqlgenerator/dump_database
|
|
* Request : { "db_name": "cpone_corporate" }
|
|
* Response: { "message": "...", "sql": "CREATE TABLE ...; INSERT INTO ...;" }
|
|
*
|
|
* Fungsi:
|
|
* - Generate SQL untuk semua tabel di database MySQL
|
|
* - Termasuk struktur tabel (CREATE) dan isi tabel (INSERT)
|
|
* - Bisa digunakan untuk backup atau migrasi database
|
|
*/
|
|
function dump_database_sql()
|
|
{
|
|
$prm = $this->sys_input;
|
|
$db_name = isset($prm['db_name']) ? $prm['db_name'] : '';
|
|
|
|
if ($db_name == '') {
|
|
$this->sys_error("db_name is required");
|
|
exit;
|
|
}
|
|
|
|
// Pilih koneksi database sesuai db_name
|
|
switch ($db_name) {
|
|
case 'onedev':
|
|
$db = $this->db_onedev;
|
|
break;
|
|
case 'cpone_corporate':
|
|
$db = $this->db_corporate;
|
|
break;
|
|
case 'log':
|
|
$db = $this->db_log;
|
|
break;
|
|
default:
|
|
$this->sys_error("Invalid db_name: {$db_name}");
|
|
exit;
|
|
}
|
|
|
|
try {
|
|
$sql_dump = "";
|
|
|
|
// 1. Ambil semua tabel
|
|
$tables_query = $db->query("SHOW TABLES");
|
|
$tables = $tables_query->result_array();
|
|
$table_key = "Tables_in_" . $db_name;
|
|
|
|
foreach ($tables as $tbl) {
|
|
$table_name = $tbl[$table_key];
|
|
|
|
// 2. Ambil struktur tabel
|
|
$create_query = $db->query("SHOW CREATE TABLE `{$table_name}`");
|
|
$create_row = $create_query->row_array();
|
|
$sql_dump .= $create_row['Create Table'] . ";\n\n";
|
|
|
|
// 3. Ambil data tabel
|
|
$data_query = $db->query("SELECT * FROM `{$table_name}`");
|
|
$rows = $data_query->result_array();
|
|
|
|
foreach ($rows as $row) {
|
|
$columns = array_keys($row);
|
|
$values = array_map(function($val) {
|
|
if (is_null($val)) return "NULL";
|
|
return "'" . str_replace("'", "''", $val) . "'";
|
|
}, array_values($row));
|
|
|
|
$sql_dump .= "INSERT INTO `" . $table_name . "` (" . implode(',', $columns) . ") VALUES(" . implode(",", $values) . ");\n";
|
|
}
|
|
$sql_dump .= "\n";
|
|
}
|
|
|
|
// 4. Commit
|
|
$sql_dump .= "COMMIT;\n";
|
|
|
|
// 5. Header untuk download file
|
|
header('Content-Type: application/sql');
|
|
header('Content-Disposition: attachment; filename="' . $db_name . '_dump.sql"');
|
|
header('Pragma: no-cache');
|
|
header('Expires: 0');
|
|
|
|
// 6. Output langsung
|
|
echo $sql_dump;
|
|
exit;
|
|
|
|
} catch (Exception $e) {
|
|
$this->sys_error("Error generating database dump: " . $e->getMessage());
|
|
exit;
|
|
}
|
|
}
|
|
|
|
} |