118 lines
5.3 KiB
PHP
118 lines
5.3 KiB
PHP
<?php
|
|
require_once 'config.php';
|
|
header('Content-Type: application/json');
|
|
|
|
checkAdmin();
|
|
|
|
$log_db = getDB(DB_LOG_HOST, DB_LOG_NAME, DB_LOG_USER, DB_LOG_PASS);
|
|
|
|
$action = $_REQUEST['action'] ?? '';
|
|
|
|
try {
|
|
if ($action === 'list') {
|
|
// ดึงชุดข้อมูลทั้งหมดมาก่อน
|
|
$stmt = $log_db->query("SELECT * FROM export_profiles ORDER BY id DESC");
|
|
$profiles = $stmt->fetchAll();
|
|
|
|
// รวบรวม CID ของผู้สร้างทั้งหมดที่ไม่ซ้ำกัน
|
|
$cids = [];
|
|
foreach ($profiles as $profile) {
|
|
if (!empty($profile['created_by'])) {
|
|
$cids[] = $profile['created_by'];
|
|
}
|
|
}
|
|
|
|
// ถ้ามีผู้สร้าง ให้ไปดึงชื่อจากฐานข้อมูล HR (เพื่อป้องกันปัญหา Cross-Database Join และ Permission)
|
|
$creator_names = [];
|
|
if (!empty($cids)) {
|
|
$cids = array_unique($cids);
|
|
$inQuery = implode(',', array_fill(0, count($cids), '?'));
|
|
|
|
try {
|
|
$hr_db = getDB(DB_HR_HOST, DB_HR_NAME, DB_HR_USER, DB_HR_PASS);
|
|
$hr_stmt = $hr_db->prepare("
|
|
SELECT a.HR_CID, CONCAT(e.HR_PREFIX_NAME, a.HR_FNAME, ' ', a.HR_LNAME) as full_name
|
|
FROM hr_person a
|
|
LEFT JOIN hr_prefix e ON a.HR_PREFIX_ID = e.HR_PREFIX_ID
|
|
WHERE a.HR_CID IN ($inQuery)
|
|
");
|
|
$hr_stmt->execute(array_values($cids));
|
|
|
|
while ($row = $hr_stmt->fetch()) {
|
|
$creator_names[$row['HR_CID']] = $row['full_name'];
|
|
}
|
|
} catch (Exception $ex) {
|
|
// ถ้าเชื่อมต่อ HR_DB ไม่ได้หรือ query ผิดพลาด ให้ข้ามไป (ปล่อยให้ชื่อเป็นว่างๆ)
|
|
error_log("Failed to fetch creator names: " . $ex->getMessage());
|
|
}
|
|
}
|
|
|
|
foreach ($profiles as &$profile) {
|
|
$cid = $profile['created_by'];
|
|
$profile['creator_name'] = !empty($cid) && isset($creator_names[$cid]) ? $creator_names[$cid] : null;
|
|
$profile['created_at'] = formatThaiDate($profile['created_at']);
|
|
}
|
|
|
|
echo json_encode(['data' => $profiles]);
|
|
}
|
|
elseif ($action === 'get') {
|
|
$id = $_GET['id'] ?? 0;
|
|
$stmt = $log_db->prepare("SELECT * FROM export_profiles WHERE id = ?");
|
|
$stmt->execute([$id]);
|
|
$profile = $stmt->fetch();
|
|
echo json_encode(['status' => 'success', 'data' => $profile]);
|
|
}
|
|
elseif ($action === 'save') {
|
|
$id = $_POST['id'] ?? '';
|
|
$profile_name = trim($_POST['profile_name'] ?? '');
|
|
$excel_columns = trim($_POST['excel_columns'] ?? '');
|
|
$sql_query = trim($_POST['sql_query'] ?? '');
|
|
|
|
if (empty($profile_name) || empty($excel_columns) || empty($sql_query)) {
|
|
echo json_encode(['status' => 'error', 'message' => 'กรุณากรอกข้อมูลให้ครบถ้วน']);
|
|
exit;
|
|
}
|
|
|
|
if (empty($id)) {
|
|
// Insert
|
|
$cid = $_SESSION['cid'] ?? null;
|
|
$stmt = $log_db->prepare("INSERT INTO export_profiles (profile_name, excel_columns, sql_query, created_by) VALUES (?, ?, ?, ?)");
|
|
$stmt->execute([$profile_name, $excel_columns, $sql_query, $cid]);
|
|
|
|
addSystemLog('CREATE_PROFILE', "สร้างชุดข้อมูลใหม่: $profile_name");
|
|
|
|
echo json_encode(['status' => 'success', 'message' => 'บันทึกชุดข้อมูลสำเร็จ']);
|
|
} else {
|
|
// Update
|
|
$stmt = $log_db->prepare("UPDATE export_profiles SET profile_name = ?, excel_columns = ?, sql_query = ? WHERE id = ?");
|
|
$stmt->execute([$profile_name, $excel_columns, $sql_query, $id]);
|
|
|
|
addSystemLog('EDIT_PROFILE', "แก้ไขชุดข้อมูล: $profile_name (ID: $id)");
|
|
|
|
echo json_encode(['status' => 'success', 'message' => 'แก้ไขชุดข้อมูลสำเร็จ']);
|
|
}
|
|
}
|
|
elseif ($action === 'delete') {
|
|
$id = $_POST['id'] ?? 0;
|
|
|
|
// Get name before delete
|
|
$stmt = $log_db->prepare("SELECT profile_name FROM export_profiles WHERE id = ?");
|
|
$stmt->execute([$id]);
|
|
$profile = $stmt->fetch();
|
|
|
|
if ($profile) {
|
|
$stmt = $log_db->prepare("DELETE FROM export_profiles WHERE id = ?");
|
|
$stmt->execute([$id]);
|
|
|
|
addSystemLog('DELETE_PROFILE', "ลบชุดข้อมูล: {$profile['profile_name']} (ID: $id)");
|
|
}
|
|
|
|
echo json_encode(['status' => 'success', 'message' => 'ลบชุดข้อมูลสำเร็จ']);
|
|
}
|
|
else {
|
|
echo json_encode(['status' => 'error', 'message' => 'Unknown action']);
|
|
}
|
|
} catch (Exception $e) {
|
|
echo json_encode(['status' => 'error', 'message' => 'Database Error: ' . $e->getMessage()]);
|
|
}
|