Athallah Ghathfan Aqila
6215b4367a
Sistem WebGIS analisis sebaran penduduk miskin dan jangkauan rumah ibadah. Fitur: peta interaktif Leaflet.js, CRUD rumah ibadah & penduduk miskin, auto-kategorisasi haversine, heatmap, export PDF/CSV, dashboard analitik, manajemen pengguna (superadmin/admin), login sistem. Teknologi: PHP, MySQL, Leaflet.js, OpenStreetMap, Nominatim Geocoding, mPDF. Catatan: jalankan `composer install` di folder uas/ untuk install dependencies. Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
108 lines
5.6 KiB
SQL
108 lines
5.6 KiB
SQL
-- ================================================================
|
|
-- WebGIS Sebaran Penduduk Miskin
|
|
-- Database: db_webgis
|
|
-- ================================================================
|
|
|
|
CREATE DATABASE IF NOT EXISTS db_webgis CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
USE db_webgis;
|
|
|
|
-- ----------------------------------------------------------------
|
|
-- Users
|
|
-- ----------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) UNIQUE NOT NULL,
|
|
password VARCHAR(255) NOT NULL,
|
|
nama VARCHAR(100),
|
|
role ENUM('admin','operator') DEFAULT 'operator',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- ----------------------------------------------------------------
|
|
-- Rumah Ibadah
|
|
-- ----------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS rumah_ibadah (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama VARCHAR(255) NOT NULL,
|
|
jenis ENUM('Masjid','Musholla','Gereja Protestan','Gereja Katolik','Pura','Vihara','Klenteng','Lainnya') DEFAULT 'Masjid',
|
|
kontak VARCHAR(100),
|
|
alamat TEXT,
|
|
lat DECIMAL(10,7) NOT NULL,
|
|
lng DECIMAL(10,7) NOT NULL,
|
|
radius INT DEFAULT 300,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- ----------------------------------------------------------------
|
|
-- Penduduk Miskin
|
|
-- ----------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS penduduk_miskin (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
kk_nama VARCHAR(255) NOT NULL,
|
|
nik VARCHAR(20),
|
|
alamat TEXT,
|
|
lat DECIMAL(10,7) NOT NULL,
|
|
lng DECIMAL(10,7) NOT NULL,
|
|
jumlah_anggota INT DEFAULT 1,
|
|
rumah_ibadah_id INT,
|
|
jarak_meter DECIMAL(10,2),
|
|
status_bantuan ENUM('belum_dibantu','sudah_dibantu','menunggu_verifikasi') DEFAULT 'belum_dibantu',
|
|
jenis_bantuan VARCHAR(255),
|
|
tanggal_bantuan DATE,
|
|
bukti_file VARCHAR(255),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (rumah_ibadah_id) REFERENCES rumah_ibadah(id) ON DELETE SET NULL
|
|
);
|
|
|
|
-- ----------------------------------------------------------------
|
|
-- Anggota Keluarga
|
|
-- ----------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS anggota_keluarga (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
penduduk_id INT NOT NULL,
|
|
nama VARCHAR(255) NOT NULL,
|
|
hubungan VARCHAR(50),
|
|
umur INT,
|
|
pekerjaan VARCHAR(100),
|
|
keterangan TEXT,
|
|
FOREIGN KEY (penduduk_id) REFERENCES penduduk_miskin(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- ----------------------------------------------------------------
|
|
-- Default admin (password: admin123)
|
|
-- Ganti hash ini dengan php -r "echo password_hash('admin123', PASSWORD_DEFAULT);"
|
|
-- ----------------------------------------------------------------
|
|
INSERT IGNORE INTO users (username, password, nama, role) VALUES
|
|
('admin', '$2y$10$TKh8H1.PfGLmAdaQXDLZQO3L4LYKE/VNRN3hJ3oZpYb7U0u0EWxLe', 'Administrator', 'admin'),
|
|
('operator', '$2y$10$TKh8H1.PfGLmAdaQXDLZQO3L4LYKE/VNRN3hJ3oZpYb7U0u0EWxLe', 'Operator Lapangan', 'operator');
|
|
|
|
-- ----------------------------------------------------------------
|
|
-- Sample data (Pontianak area)
|
|
-- ----------------------------------------------------------------
|
|
INSERT IGNORE INTO rumah_ibadah (id, nama, jenis, kontak, alamat, lat, lng, radius) VALUES
|
|
(1, 'Masjid Raya Mujahidin', 'Masjid', '0561-123456', 'Jl. Ahmad Yani, Pontianak', -0.0262, 109.3429, 500),
|
|
(2, 'Gereja Katedral Santo Yosef', 'Gereja Katolik', '0561-234567', 'Jl. Pattimura, Pontianak', -0.0312, 109.3301, 400),
|
|
(3, 'Vihara Bodhisattva Karaniya Metta', 'Vihara', '0561-345678', 'Jl. Tanjungpura, Pontianak', -0.0355, 109.3344, 350),
|
|
(4, 'Pura Agung Nusantara', 'Pura', '0561-456789', 'Jl. Gajah Mada, Pontianak', -0.0409, 109.3221, 400),
|
|
(5, 'Musholla Al-Ikhlas', 'Musholla', NULL, 'Jl. Kom Yos Sudarso, Pontianak', -0.0195, 109.3553, 250);
|
|
|
|
INSERT IGNORE INTO penduduk_miskin (id, kk_nama, nik, alamat, lat, lng, rumah_ibadah_id, jarak_meter, status_bantuan, jenis_bantuan, tanggal_bantuan) VALUES
|
|
(1, 'Ahmad Subandi', '6171011234560001', 'Gg. Melati No.3, Pontianak Selatan', -0.0290, 109.3410, 1, 312.5, 'sudah_dibantu', 'PKH', '2024-03-01'),
|
|
(2, 'Siti Rahayu', '6171021234560002', 'Jl. Budi Utomo Gg.5, Pontianak', -0.0278, 109.3450, 1, 245.0, 'belum_dibantu', NULL, NULL),
|
|
(3, 'Budi Santoso', '6171031234560003', 'Jl. Gusti Hamzah, Pontianak Utara', -0.0320, 109.3290, 2, 198.3, 'menunggu_verifikasi', NULL, NULL),
|
|
(4, 'Maria Yuliana', '6171041234560004', 'Jl. Purnama Gg.2, Pontianak', -0.0350, 109.3320, 3, 267.1, 'sudah_dibantu', 'BPNT + PKH', '2024-01-15'),
|
|
(5, 'Wati Sari', '6171051234560005', 'Jl. Kom Yos Sudarso, Pontianak Timur', -0.0188, 109.3561, 5, 89.4, 'belum_dibantu', NULL, NULL);
|
|
|
|
INSERT IGNORE INTO anggota_keluarga (penduduk_id, nama, hubungan, umur, pekerjaan) VALUES
|
|
(1, 'Ahmad Subandi', 'Kepala Keluarga', 45, 'Buruh Harian'),
|
|
(1, 'Dewi Subandi', 'Istri', 40, 'Ibu Rumah Tangga'),
|
|
(1, 'Rizki Subandi', 'Anak', 18, 'Pelajar'),
|
|
(2, 'Siti Rahayu', 'Kepala Keluarga', 38, 'Pedagang Kecil'),
|
|
(2, 'Putri Rahayu', 'Anak', 12, 'Pelajar'),
|
|
(3, 'Budi Santoso', 'Kepala Keluarga', 52, 'Tidak Bekerja'),
|
|
(3, 'Sari Santoso', 'Istri', 48, 'Buruh Cuci'),
|
|
(4, 'Maria Yuliana', 'Kepala Keluarga', 41, 'Pedagang'),
|
|
(5, 'Wati Sari', 'Kepala Keluarga', 33, 'Buruh');
|