331 lines
11 KiB
PHP
331 lines
11 KiB
PHP
<?php
|
|
/**
|
|
* WebGIS Poverty Mapping - Database Setup Script
|
|
* Version: 1.0
|
|
* Description: Initialize database schema and required tables
|
|
*/
|
|
|
|
// Error reporting
|
|
error_reporting(E_ALL);
|
|
ini_set('display_errors', 1);
|
|
|
|
// Buat koneksi tanpa database dulu
|
|
$conn = new mysqli("localhost", "root", "");
|
|
|
|
if ($conn->connect_error) {
|
|
die("Connection failed: " . $conn->connect_error);
|
|
}
|
|
|
|
echo "=== WebGIS Poverty Mapping - Database Setup ===<br><br>";
|
|
|
|
// ========== 1. CREATE DATABASE ==========
|
|
$sql_db = "CREATE DATABASE IF NOT EXISTS webgis_unified CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";
|
|
if ($conn->query($sql_db) === TRUE) {
|
|
echo "✓ Database 'webgis_unified' ready<br>";
|
|
} else {
|
|
die("✗ Error creating database: " . $conn->error . "<br>");
|
|
}
|
|
|
|
$conn->select_db("webgis_unified");
|
|
|
|
// ========== 2. CREATE MAIN DATA TABLE ==========
|
|
$sql_table = "CREATE TABLE IF NOT EXISTS data_unified (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
tipe ENUM('point','worship','poor_house','jalan','parsil') NOT NULL DEFAULT 'point',
|
|
|
|
-- Atribut Umum
|
|
nama VARCHAR(200) NULL,
|
|
jenis VARCHAR(100) NULL,
|
|
alamat VARCHAR(255) NULL,
|
|
kontak VARCHAR(100) NULL,
|
|
no_wa VARCHAR(50) NULL,
|
|
status VARCHAR(50) NOT NULL DEFAULT 'aktif',
|
|
|
|
-- Atribut Rumah Ibadah
|
|
kegiatan VARCHAR(255) NULL,
|
|
kapasitas INT NULL,
|
|
radius_meter INT NULL,
|
|
|
|
-- Atribut Rumah Miskin
|
|
nama_kepala_keluarga VARCHAR(200) NULL,
|
|
jumlah_anggota INT NULL,
|
|
kondisi_rumah VARCHAR(100) NULL,
|
|
sumber_penghasilan VARCHAR(255) NULL,
|
|
kebutuhan_prioritas VARCHAR(255) NULL,
|
|
status_bantuan VARCHAR(100) NULL,
|
|
|
|
-- Koordinat Geografis
|
|
latitude DOUBLE NULL,
|
|
longitude DOUBLE NULL,
|
|
|
|
-- Untuk Jalan & Parsil
|
|
panjang DECIMAL(10,2) NULL,
|
|
luas DECIMAL(10,2) NULL,
|
|
geom LONGTEXT NULL,
|
|
|
|
-- Audit Trail
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
created_by VARCHAR(50) NULL,
|
|
updated_by VARCHAR(50) NULL,
|
|
|
|
-- Indexes
|
|
KEY idx_tipe (tipe),
|
|
KEY idx_status (status),
|
|
KEY idx_created_at (created_at),
|
|
KEY idx_koordinat (latitude, longitude),
|
|
FULLTEXT KEY ft_nama (nama),
|
|
FULLTEXT KEY ft_alamat (alamat)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
|
|
|
|
if ($conn->query($sql_table) === TRUE) {
|
|
echo "✓ Table 'data_unified' created<br>";
|
|
} else {
|
|
echo "✗ Error creating table: " . $conn->error . "<br>";
|
|
}
|
|
|
|
// ========== 3. CREATE AUDIT LOG TABLE ==========
|
|
$sql_audit = "CREATE TABLE IF NOT EXISTS audit_log (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
table_name VARCHAR(100) NOT NULL,
|
|
record_id INT NOT NULL,
|
|
action ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
|
|
old_values JSON NULL,
|
|
new_values JSON NULL,
|
|
user_name VARCHAR(50) NULL,
|
|
changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
KEY idx_table (table_name),
|
|
KEY idx_record (record_id),
|
|
KEY idx_action (action),
|
|
KEY idx_changed_at (changed_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
|
|
|
|
if ($conn->query($sql_audit) === TRUE) {
|
|
echo "✓ Table 'audit_log' created<br>";
|
|
} else {
|
|
echo "✗ Error creating audit_log: " . $conn->error . "<br>";
|
|
}
|
|
|
|
// ========== 3.1 CREATE ACTIVITY LOG TABLE (for API logging) ==========
|
|
$sql_activity = "CREATE TABLE IF NOT EXISTS activity_log (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
action VARCHAR(50) NOT NULL,
|
|
details JSON NULL,
|
|
user_name VARCHAR(50) NULL,
|
|
ip_address VARCHAR(45) NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
KEY idx_action (action),
|
|
KEY idx_user (user_name),
|
|
KEY idx_created_at (created_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
|
|
|
|
if ($conn->query($sql_activity) === TRUE) {
|
|
echo "✓ Table 'activity_log' created<br>";
|
|
} else {
|
|
echo "✗ Error creating activity_log: " . $conn->error . "<br>";
|
|
}
|
|
|
|
// ========== 4. CREATE REFERENCE TABLES ==========
|
|
|
|
// House Condition Reference
|
|
$sql_kondisi = "CREATE TABLE IF NOT EXISTS ref_kondisi_rumah (
|
|
id INT PRIMARY KEY,
|
|
kode VARCHAR(20) NOT NULL UNIQUE,
|
|
nama VARCHAR(100) NOT NULL,
|
|
deskripsi TEXT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
|
|
|
|
if ($conn->query($sql_kondisi) === TRUE) {
|
|
echo "✓ Table 'ref_kondisi_rumah' created<br>";
|
|
|
|
// Insert reference data
|
|
$insert_kondisi = "INSERT IGNORE INTO ref_kondisi_rumah (id, kode, nama, deskripsi) VALUES
|
|
(1, 'SANGAT_BURUK', 'Sangat Buruk', 'Rumah dalam kondisi sangat buruk/rawan roboh'),
|
|
(2, 'BURUK', 'Buruk', 'Rumah banyak kerusakan'),
|
|
(3, 'SEDANG', 'Sedang', 'Rumah dengan kerusakan ringan'),
|
|
(4, 'BAIK', 'Baik', 'Rumah dalam kondisi baik')";
|
|
$conn->query($insert_kondisi);
|
|
}
|
|
|
|
// Assistance Status Reference
|
|
$sql_bantuan = "CREATE TABLE IF NOT EXISTS ref_status_bantuan (
|
|
id INT PRIMARY KEY,
|
|
kode VARCHAR(20) NOT NULL UNIQUE,
|
|
nama VARCHAR(100) NOT NULL,
|
|
deskripsi TEXT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
|
|
|
|
if ($conn->query($sql_bantuan) === TRUE) {
|
|
echo "✓ Table 'ref_status_bantuan' created<br>";
|
|
|
|
// Insert reference data
|
|
$insert_bantuan = "INSERT IGNORE INTO ref_status_bantuan (id, kode, nama, deskripsi) VALUES
|
|
(1, 'BELUM', 'Belum Menerima', 'Belum menerima bantuan apapun'),
|
|
(2, 'PENERIMA_PKH', 'Penerima PKH', 'Penerima Program Keluarga Harapan'),
|
|
(3, 'PENERIMA_BLT', 'Penerima BLT', 'Penerima Bantuan Langsung Tunai'),
|
|
(4, 'PENERIMA_BANTSOS', 'Penerima Bantuan Sosial', 'Penerima bantuan sosial lainnya')";
|
|
$conn->query($insert_bantuan);
|
|
}
|
|
|
|
// Income Source Reference
|
|
$sql_penghasilan = "CREATE TABLE IF NOT EXISTS ref_sumber_penghasilan (
|
|
id INT PRIMARY KEY,
|
|
kode VARCHAR(20) NOT NULL UNIQUE,
|
|
nama VARCHAR(100) NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
|
|
|
|
if ($conn->query($sql_penghasilan) === TRUE) {
|
|
echo "✓ Table 'ref_sumber_penghasilan' created<br>";
|
|
|
|
// Insert reference data
|
|
$insert_penghasilan = "INSERT IGNORE INTO ref_sumber_penghasilan (id, kode, nama) VALUES
|
|
(1, 'PETANI', 'Petani'),
|
|
(2, 'BURUH', 'Buruh'),
|
|
(3, 'PEDAGANG', 'Pedagang'),
|
|
(4, 'PNS', 'PNS'),
|
|
(5, 'SWASTA', 'Pegawai Swasta'),
|
|
(6, 'USAHA_KECIL', 'Usaha Kecil'),
|
|
(7, 'PENSIUN', 'Pensiun'),
|
|
(8, 'TIDAK_BEKERJA', 'Tidak Bekerja')";
|
|
$conn->query($insert_penghasilan);
|
|
}
|
|
|
|
// ========== 5. CREATE STATISTICS TABLE ==========
|
|
$sql_stats = "CREATE TABLE IF NOT EXISTS data_statistics (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
stat_date DATE NOT NULL UNIQUE,
|
|
total_records INT DEFAULT 0,
|
|
total_worship INT DEFAULT 0,
|
|
total_poor_house INT DEFAULT 0,
|
|
total_points INT DEFAULT 0,
|
|
total_roads INT DEFAULT 0,
|
|
total_parcels INT DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
KEY idx_stat_date (stat_date)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
|
|
|
|
if ($conn->query($sql_stats) === TRUE) {
|
|
echo "✓ Table 'data_statistics' created<br>";
|
|
}
|
|
|
|
// ========== 6. CREATE ADMIN USER TABLE ==========
|
|
$sql_users = "CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
full_name VARCHAR(100) NOT NULL,
|
|
email VARCHAR(100) NULL,
|
|
role ENUM('admin', 'user', 'viewer') DEFAULT 'user',
|
|
is_active TINYINT DEFAULT 1,
|
|
last_login DATETIME NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
KEY idx_username (username),
|
|
KEY idx_role (role)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
|
|
|
|
if ($conn->query($sql_users) === TRUE) {
|
|
echo "✓ Table 'users' created<br>";
|
|
}
|
|
|
|
// ========== 7. CREATE TRIGGERS (For Audit Trail) ==========
|
|
|
|
// Trigger for INSERT
|
|
$trigger_insert = "CREATE TRIGGER IF NOT EXISTS trg_data_unified_insert
|
|
AFTER INSERT ON data_unified
|
|
FOR EACH ROW
|
|
BEGIN
|
|
INSERT INTO audit_log (table_name, record_id, action, new_values, user_name, changed_at)
|
|
VALUES ('data_unified', NEW.id, 'INSERT',
|
|
JSON_OBJECT('tipe', NEW.tipe, 'nama', NEW.nama, 'alamat', NEW.alamat),
|
|
IFNULL(NEW.created_by, 'system'), NOW());
|
|
END";
|
|
|
|
if ($conn->query($trigger_insert) === TRUE) {
|
|
echo "✓ Trigger 'trg_data_unified_insert' created<br>";
|
|
} else {
|
|
echo " Trigger insert: " . $conn->error . "<br>";
|
|
}
|
|
|
|
// Trigger for UPDATE
|
|
$trigger_update = "CREATE TRIGGER IF NOT EXISTS trg_data_unified_update
|
|
AFTER UPDATE ON data_unified
|
|
FOR EACH ROW
|
|
BEGIN
|
|
INSERT INTO audit_log (table_name, record_id, action, old_values, new_values, user_name, changed_at)
|
|
VALUES ('data_unified', NEW.id, 'UPDATE',
|
|
JSON_OBJECT('nama', OLD.nama, 'alamat', OLD.alamat, 'status', OLD.status),
|
|
JSON_OBJECT('nama', NEW.nama, 'alamat', NEW.alamat, 'status', NEW.status),
|
|
IFNULL(NEW.updated_by, 'system'), NOW());
|
|
END";
|
|
|
|
if ($conn->query($trigger_update) === TRUE) {
|
|
echo "✓ Trigger 'trg_data_unified_update' created<br>";
|
|
} else {
|
|
echo " Trigger update: " . $conn->error . "<br>";
|
|
}
|
|
|
|
// Trigger for DELETE
|
|
$trigger_delete = "CREATE TRIGGER IF NOT EXISTS trg_data_unified_delete
|
|
BEFORE DELETE ON data_unified
|
|
FOR EACH ROW
|
|
BEGIN
|
|
INSERT INTO audit_log (table_name, record_id, action, old_values, changed_at)
|
|
VALUES ('data_unified', OLD.id, 'DELETE',
|
|
JSON_OBJECT('tipe', OLD.tipe, 'nama', OLD.nama, 'alamat', OLD.alamat),
|
|
NOW());
|
|
END";
|
|
|
|
if ($conn->query($trigger_delete) === TRUE) {
|
|
echo "✓ Trigger 'trg_data_unified_delete' created<br>";
|
|
} else {
|
|
echo " Trigger delete: " . $conn->error . "<br>";
|
|
}
|
|
|
|
// ========== 8. CREATE VIEWS ==========
|
|
|
|
$view_summary = "CREATE OR REPLACE VIEW v_data_summary AS
|
|
SELECT
|
|
tipe,
|
|
COUNT(*) as count,
|
|
status
|
|
FROM data_unified
|
|
GROUP BY tipe, status";
|
|
|
|
if ($conn->query($view_summary) === TRUE) {
|
|
echo "✓ View 'v_data_summary' created<br>";
|
|
}
|
|
|
|
$view_poor_analysis = "CREATE OR REPLACE VIEW v_poor_house_analysis AS
|
|
SELECT
|
|
nama_kepala_keluarga,
|
|
alamat,
|
|
kondisi_rumah,
|
|
jumlah_anggota,
|
|
kebutuhan_prioritas,
|
|
status_bantuan,
|
|
latitude,
|
|
longitude
|
|
FROM data_unified
|
|
WHERE tipe = 'poor_house' AND status = 'aktif'
|
|
ORDER BY jumlah_anggota DESC";
|
|
|
|
if ($conn->query($view_poor_analysis) === TRUE) {
|
|
echo "✓ View 'v_poor_house_analysis' created<br>";
|
|
}
|
|
|
|
// ========== COMPLETION MESSAGE ==========
|
|
echo "<br>=== Setup Selesai ===" . date('Y-m-d H:i:s') . "<br>";
|
|
echo "Database: webgis_unified<br>";
|
|
echo "Tables: 9 (data_unified, audit_log, ref_kondisi_rumah, ref_status_bantuan, ref_sumber_penghasilan, data_statistics, users, + 2 views)<br>";
|
|
echo "Triggers: 3 (INSERT, UPDATE, DELETE)<br>";
|
|
echo "Ready for production use!<br>";
|
|
|
|
$conn->close();
|
|
?>
|