-- SKEMA DATABASE MYSQL POSYANDU PLUS
CREATE DATABASE IF NOT EXISTS db_posyandu_plus;
USE db_posyandu_plus;
-- 1. Tabel Balita
CREATE TABLE IF NOT EXISTS balita (
id VARCHAR(50) PRIMARY KEY,
nama VARCHAR(150) NOT NULL,
sex ENUM('L', 'P') NOT NULL,
tanggal_lahir DATE NOT NULL,
nama_ibu VARCHAR(150) NOT NULL,
notif_hp VARCHAR(20),
alamat TEXT,
penyakit_menular VARCHAR(255) DEFAULT 'Tidak ada.'
);
-- 2. Tabel Pengukuran Balita (History)
CREATE TABLE IF NOT EXISTS balita_history (
id INT AUTO_INCREMENT PRIMARY KEY,
balita_id VARCHAR(50) NOT NULL,
tanggal DATE NOT NULL,
tinggi FLOAT NOT NULL,
berat FLOAT NOT NULL,
lingkar_kepala FLOAT,
catatan TEXT,
FOREIGN KEY (balita_id) REFERENCES balita(id) ON DELETE CASCADE
);
-- 3. Tabel Imunisasi Balita
CREATE TABLE IF NOT EXISTS balita_imunisasi (
balita_id VARCHAR(50) PRIMARY KEY,
hb0 TINYINT(1) DEFAULT 0,
bcg TINYINT(1) DEFAULT 0,
polio1 TINYINT(1) DEFAULT 0,
dpt1 TINYINT(1) DEFAULT 0,
polio2 TINYINT(1) DEFAULT 0,
dpt2 TINYINT(1) DEFAULT 0,
polio3 TINYINT(1) DEFAULT 0,
campak TINYINT(1) DEFAULT 0,
FOREIGN KEY (balita_id) REFERENCES balita(id) ON DELETE CASCADE
);
-- 4. Tabel Ibu Hamil
CREATE TABLE IF NOT EXISTS ibu_hamil (
id VARCHAR(50) PRIMARY KEY,
nama VARCHAR(150) NOT NULL,
suami VARCHAR(150) NOT NULL,
umur INT NOT NULL,
hpht DATE NOT NULL,
notif_hp VARCHAR(20),
alamat TEXT
);
-- 5. Tabel Pemeriksaan Ibu Hamil (ANC History)
CREATE TABLE IF NOT EXISTS ibu_hamil_history (
id INT AUTO_INCREMENT PRIMARY KEY,
bumil_id VARCHAR(50) NOT NULL,
tanggal DATE NOT NULL,
berat_ibu FLOAT NOT NULL,
lila FLOAT NOT NULL,
tensi VARCHAR(20) NOT NULL,
hb FLOAT DEFAULT 0,
catatan TEXT,
FOREIGN KEY (bumil_id) REFERENCES ibu_hamil(id) ON DELETE CASCADE
);
-- 6. Tabel Lansia
CREATE TABLE IF NOT EXISTS lansia (
id VARCHAR(50) PRIMARY KEY,
nama VARCHAR(150) NOT NULL,
sex ENUM('L', 'P') NOT NULL,
umur INT NOT NULL,
notif_hp VARCHAR(20),
alamat TEXT
);
-- 7. Tabel Pemeriksaan Lansia (Metabolik History)
CREATE TABLE IF NOT EXISTS lansia_history (
id INT AUTO_INCREMENT PRIMARY KEY,
lansia_id VARCHAR(50) NOT NULL,
tanggal DATE NOT NULL,
berat FLOAT NOT NULL,
tensi VARCHAR(20) NOT NULL,
gds INT NOT NULL,
asam_urat FLOAT DEFAULT 0,
kolesterol INT DEFAULT 0,
catatan TEXT,
FOREIGN KEY (lansia_id) REFERENCES lansia(id) ON DELETE CASCADE
);
<?php
/**
* BACKEND API JEMBATAN MYSQL - POSYANDU PLUS
* Simpan berkas ini dengan nama 'api.php' di server lokal Anda (XAMPP htdocs)
*/
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Headers: Content-Type");
header("Access-Control-Allow-Methods: GET, POST, OPTIONS");
header("Content-Type: application/json; charset=UTF-8");
if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
exit(0);
}
// 1. Konfigurasi Database
$host = "localhost";
$db_name = "db_posyandu_plus";
$username = "root";
$password = "";
try {
$conn = new PDO("mysql:host=" . $host . ";dbname=" . $db_name, $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $exception) {
echo json_encode(["status" => "error", "message" => "Koneksi database gagal: " . $exception->getMessage()]);
exit();
}
$action = isset($_GET['action']) ? $_GET['action'] : '';
$input = json_decode(file_get_contents('php://input'), true);
switch ($action) {
// ---- BALITA OPERATIONS ----
case 'get_balita':
$stmt = $conn->prepare("SELECT * FROM balita");
$stmt->execute();
$balitas = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($balitas as &$b) {
// Get history
$h_stmt = $conn->prepare("SELECT * FROM balita_history WHERE balita_id = ? ORDER BY tanggal ASC");
$h_stmt->execute([$b['id']]);
$b['history'] = $h_stmt->fetchAll(PDO::FETCH_ASSOC);
// Get imunisasi
$i_stmt = $conn->prepare("SELECT * FROM balita_imunisasi WHERE balita_id = ?");
$i_stmt->execute([$b['id']]);
$imun = $i_stmt->fetch(PDO::FETCH_ASSOC);
if ($imun) {
unset($imun['balita_id']);
// Convert 1/0 to true/false for frontend
foreach ($imun as $k => $v) { $imun[$k] = (bool)$v; }
$b['imunisasi'] = $imun;
} else {
$b['imunisasi'] = ["HB0" => false, "BCG" => false, "Polio1" => false, "DPT1" => false, "Polio2" => false, "DPT2" => false, "Polio3" => false, "Campak" => false];
}
}
echo json_encode($balitas);
break;
case 'add_balita':
$id = $input['id'];
$stmt = $conn->prepare("INSERT INTO balita (id, nama, sex, tanggal_lahir, nama_ibu, notif_hp, alamat) VALUES (?, ?, ?, ?, ?, ?, ?)");
$stmt->execute([$id, $input['nama'], $input['sex'], $input['tanggalLahir'], $input['namaIbu'], $input['notifHP'], $input['alamat']]);
$stmt_imun = $conn->prepare("INSERT INTO balita_imunisasi (balita_id) VALUES (?)");
$stmt_imun->execute([$id]);
echo json_encode(["status" => "success", "id" => $id]);
break;
case 'add_balita_history':
$stmt = $conn->prepare("INSERT INTO balita_history (balita_id, tanggal, tinggi, berat, lingkar_kepala, catatan) VALUES (?, ?, ?, ?, ?, ?)");
$stmt->execute([$input['balita_id'], $input['tanggal'], $input['tinggi'], $input['berat'], $input['lingkarKepala'], $input['catatan']]);
if (isset($input['penyakitMenular']) && !empty($input['penyakitMenular'])) {
$upd = $conn->prepare("UPDATE balita SET penyakit_menular = ? WHERE id = ?");
$upd->execute([$input['penyakitMenular'], $input['balita_id']]);
}
echo json_encode(["status" => "success"]);
break;
case 'update_balita_imunisasi':
$id = $input['balita_id'];
$imun = $input['imunisasi'];
$stmt = $conn->prepare("UPDATE balita_imunisasi SET hb0=?, bcg=?, polio1=?, dpt1=?, polio2=?, dpt2=?, polio3=?, campak=? WHERE balita_id=?");
$stmt->execute([
$imun['HB0']?1:0, $imun['BCG']?1:0, $imun['Polio1']?1:0, $imun['DPT1']?1:0,
$imun['Polio2']?1:0, $imun['DPT2']?1:0, $imun['Polio3']?1:0, $imun['Campak']?1:0, $id
]);
echo json_encode(["status" => "success"]);
break;
// ---- IBU HAMIL OPERATIONS ----
case 'get_bumil':
$stmt = $conn->prepare("SELECT * FROM ibu_hamil");
$stmt->execute();
$bumils = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($bumils as &$h) {
$h_stmt = $conn->prepare("SELECT * FROM ibu_hamil_history WHERE bumil_id = ? ORDER BY tanggal ASC");
$h_stmt->execute([$h['id']]);
$h['history'] = $h_stmt->fetchAll(PDO::FETCH_ASSOC);
}
echo json_encode($bumils);
break;
case 'add_bumil':
$id = $input['id'];
$stmt = $conn->prepare("INSERT INTO ibu_hamil (id, nama, suami, umur, hpht, notif_hp, alamat) VALUES (?, ?, ?, ?, ?, ?, ?)");
$stmt->execute([$id, $input['nama'], $input['suami'], $input['umur'], $input['hpht'], $input['notifHP'], $input['alamat']]);
echo json_encode(["status" => "success", "id" => $id]);
break;
case 'add_bumil_history':
$stmt = $conn->prepare("INSERT INTO ibu_hamil_history (bumil_id, tanggal, berat_ibu, lila, tensi, hb, catatan) VALUES (?, ?, ?, ?, ?, ?, ?)");
$stmt->execute([$input['bumil_id'], $input['tanggal'], $input['beratIbu'], $input['lila'], $input['tensi'], $input['hb'], $input['catatan']]);
echo json_encode(["status" => "success"]);
break;
// ---- LANSIA OPERATIONS ----
case 'get_lansia':
$stmt = $conn->prepare("SELECT * FROM lansia");
$stmt->execute();
$lansias = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($lansias as &$l) {
$h_stmt = $conn->prepare("SELECT * FROM lansia_history WHERE lansia_id = ? ORDER BY tanggal ASC");
$h_stmt->execute([$l['id']]);
$l['history'] = $h_stmt->fetchAll(PDO::FETCH_ASSOC);
}
echo json_encode($lansias);
break;
case 'add_lansia':
$id = $input['id'];
$stmt = $conn->prepare("INSERT INTO lansia (id, nama, sex, umur, notif_hp, alamat) VALUES (?, ?, ?, ?, ?, ?)");
$stmt->execute([$id, $input['nama'], $input['sex'], $input['umur'], $input['notifHP'], $input['alamat']]);
echo json_encode(["status" => "success", "id" => $id]);
break;
case 'add_lansia_history':
$stmt = $conn->prepare("INSERT INTO lansia_history (lansia_id, tanggal, berat, tensi, gds, asam_urat, kolesterol, catatan) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
$stmt->execute([$input['lansia_id'], $input['tanggal'], $input['berat'], $input['tensi'], $input['gds'], $input['asamUrat'], $input['kolesterol'], $input['catatan']]);
echo json_encode(["status" => "success"]);
break;
default:
echo json_encode(["status" => "error", "message" => "Endpoint tidak valid."]);
break;
}
?>