tistopandita
175 lines
8.3 KiB
SQL
175 lines
8.3 KiB
SQL
-- Schema Database WebGIS SPBU dan Poverty Mapping Gereja
|
|
-- Stack: Node.js, Express.js, MySQL, HTML5, CSS3, Vanilla JavaScript, Leaflet.js
|
|
|
|
CREATE DATABASE IF NOT EXISTS spbu_pontianak
|
|
CHARACTER SET utf8mb4
|
|
COLLATE utf8mb4_unicode_ci;
|
|
|
|
USE spbu_pontianak;
|
|
|
|
CREATE TABLE IF NOT EXISTS spbu (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
kode VARCHAR(20) NOT NULL UNIQUE,
|
|
nama VARCHAR(100) NOT NULL,
|
|
alamat TEXT NOT NULL,
|
|
lat DOUBLE NOT NULL,
|
|
lng DOUBLE NOT NULL,
|
|
jam_buka TIME NOT NULL DEFAULT '06:00:00',
|
|
jam_tutup TIME NOT NULL DEFAULT '22:00:00',
|
|
jenis_bbm JSON NOT NULL,
|
|
manual_override ENUM('auto','open','closed') NOT NULL DEFAULT 'auto',
|
|
aktif TINYINT(1) NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS jalan (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama_jalan VARCHAR(150) NOT NULL,
|
|
jenis_jalan ENUM('nasional','provinsi','kota_kabupaten') NOT NULL DEFAULT 'kota_kabupaten',
|
|
koordinat LONGTEXT NOT NULL,
|
|
aktif TINYINT(1) NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS bangunan (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama_bangunan VARCHAR(150) NOT NULL,
|
|
jenis_bangunan ENUM('SHM','HGB','HGU','HP','HPL') NOT NULL DEFAULT 'SHM',
|
|
nama_jalan VARCHAR(150),
|
|
koordinat LONGTEXT NOT NULL,
|
|
aktif TINYINT(1) NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id_user INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama VARCHAR(120) NOT NULL,
|
|
username VARCHAR(80) NOT NULL UNIQUE,
|
|
password VARCHAR(255) NOT NULL,
|
|
role ENUM('admin','pengurus') NOT NULL DEFAULT 'pengurus',
|
|
aktif TINYINT(1) NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS keluarga (
|
|
id_keluarga INT AUTO_INCREMENT PRIMARY KEY,
|
|
nomor_kartu_keluarga VARCHAR(40) NOT NULL UNIQUE,
|
|
nama_kepala_keluarga VARCHAR(160) NOT NULL,
|
|
alamat TEXT NOT NULL,
|
|
wilayah VARCHAR(120) NOT NULL DEFAULT 'Belum Dipetakan',
|
|
latitude DOUBLE NOT NULL,
|
|
longitude DOUBLE NOT NULL,
|
|
jumlah_anggota INT NOT NULL DEFAULT 1,
|
|
jumlah_tanggungan INT NOT NULL DEFAULT 0,
|
|
pendapatan_bulanan DECIMAL(14,2) NOT NULL DEFAULT 0,
|
|
pekerjaan VARCHAR(60) NOT NULL DEFAULT 'lainnya',
|
|
pendidikan VARCHAR(60) NOT NULL DEFAULT 'sma',
|
|
kondisi_rumah VARCHAR(60) NOT NULL DEFAULT 'sederhana',
|
|
kondisi_kesehatan VARCHAR(60) NOT NULL DEFAULT 'sehat',
|
|
status_verifikasi ENUM('pending','terverifikasi','ditolak') NOT NULL DEFAULT 'pending',
|
|
tanggal_update DATE NOT NULL,
|
|
aktif TINYINT(1) NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_keluarga_lokasi (latitude, longitude),
|
|
INDEX idx_keluarga_wilayah (wilayah),
|
|
INDEX idx_keluarga_verifikasi (status_verifikasi)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS kriteria (
|
|
id_kriteria INT AUTO_INCREMENT PRIMARY KEY,
|
|
kode VARCHAR(60) NOT NULL UNIQUE,
|
|
nama_kriteria VARCHAR(120) NOT NULL,
|
|
bobot DECIMAL(6,4) NOT NULL,
|
|
atribut ENUM('benefit','cost') NOT NULL,
|
|
aktif TINYINT(1) NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS penilaian (
|
|
id_penilaian INT AUTO_INCREMENT PRIMARY KEY,
|
|
id_keluarga INT NOT NULL,
|
|
id_kriteria INT NOT NULL,
|
|
nilai DECIMAL(14,4) NOT NULL DEFAULT 0,
|
|
nilai_normalisasi DECIMAL(14,6) NOT NULL DEFAULT 0,
|
|
nilai_terbobot DECIMAL(14,6) NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uniq_penilaian (id_keluarga, id_kriteria),
|
|
CONSTRAINT fk_penilaian_keluarga FOREIGN KEY (id_keluarga) REFERENCES keluarga(id_keluarga) ON DELETE CASCADE,
|
|
CONSTRAINT fk_penilaian_kriteria FOREIGN KEY (id_kriteria) REFERENCES kriteria(id_kriteria) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS hasil_saw (
|
|
id_hasil INT AUTO_INCREMENT PRIMARY KEY,
|
|
id_keluarga INT NOT NULL UNIQUE,
|
|
skor_saw DECIMAL(12,6) NOT NULL DEFAULT 0,
|
|
kategori VARCHAR(40) NOT NULL,
|
|
ranking INT NOT NULL DEFAULT 0,
|
|
calculated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_hasil_keluarga FOREIGN KEY (id_keluarga) REFERENCES keluarga(id_keluarga) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS bantuan (
|
|
id_bantuan INT AUTO_INCREMENT PRIMARY KEY,
|
|
jenis_bantuan VARCHAR(120) NOT NULL,
|
|
tanggal_bantuan DATE NOT NULL,
|
|
nilai_bantuan DECIMAL(14,2) NOT NULL DEFAULT 0,
|
|
keterangan TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS riwayat_bantuan (
|
|
id_riwayat INT AUTO_INCREMENT PRIMARY KEY,
|
|
id_keluarga INT NOT NULL,
|
|
id_bantuan INT NOT NULL,
|
|
tanggal_diterima DATE NOT NULL,
|
|
jumlah_bantuan DECIMAL(14,2) NOT NULL DEFAULT 0,
|
|
keterangan TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_riwayat_keluarga FOREIGN KEY (id_keluarga) REFERENCES keluarga(id_keluarga) ON DELETE CASCADE,
|
|
CONSTRAINT fk_riwayat_bantuan FOREIGN KEY (id_bantuan) REFERENCES bantuan(id_bantuan) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
INSERT IGNORE INTO users (nama, username, password, role) VALUES
|
|
('Administrator', 'admin', 'admin123', 'admin'),
|
|
('Pengurus Gereja', 'pengurus', 'pengurus123', 'pengurus');
|
|
|
|
INSERT INTO kriteria (kode, nama_kriteria, bobot, atribut) VALUES
|
|
('pendapatan', 'Pendapatan', 0.3500, 'cost'),
|
|
('jumlah_tanggungan', 'Jumlah Tanggungan', 0.2000, 'benefit'),
|
|
('kondisi_rumah', 'Kondisi Rumah', 0.1500, 'benefit'),
|
|
('pekerjaan', 'Pekerjaan', 0.1500, 'benefit'),
|
|
('pendidikan', 'Pendidikan Kepala Keluarga', 0.1000, 'benefit'),
|
|
('kondisi_kesehatan', 'Kondisi Kesehatan', 0.0500, 'benefit')
|
|
ON DUPLICATE KEY UPDATE
|
|
nama_kriteria = VALUES(nama_kriteria),
|
|
bobot = VALUES(bobot),
|
|
atribut = VALUES(atribut),
|
|
aktif = 1;
|
|
|
|
INSERT IGNORE INTO keluarga
|
|
(nomor_kartu_keluarga, nama_kepala_keluarga, alamat, wilayah, latitude, longitude,
|
|
jumlah_anggota, jumlah_tanggungan, pendapatan_bulanan, pekerjaan, pendidikan,
|
|
kondisi_rumah, kondisi_kesehatan, status_verifikasi, tanggal_update)
|
|
VALUES
|
|
('6171010101010001', 'Maria Goretti', 'Jl. Gajah Mada Gang Damai', 'Paroki Katedral', -0.0339, 109.3367, 5, 4, 1250000, 'buruh_harian', 'smp', 'buruk', 'sakit_sering', 'terverifikasi', CURDATE()),
|
|
('6171010101010002', 'Yohanes Rudi', 'Jl. Tanjung Raya II', 'Paroki Santo Yosef', -0.0278, 109.3695, 6, 5, 900000, 'pengangguran', 'sd', 'sangat_buruk', 'kronis', 'terverifikasi', CURDATE()),
|
|
('6171010101010003', 'Agnes Lestari', 'Jl. Husein Hamzah', 'Paroki Santo Hieronymus', -0.0252, 109.3062, 4, 3, 1750000, 'pedagang_kecil', 'sma', 'semi_permanen', 'sehat', 'terverifikasi', CURDATE()),
|
|
('6171010101010004', 'Petrus Antoni', 'Jl. Imam Bonjol', 'Paroki Keluarga Kudus', -0.0266, 109.3444, 3, 2, 2800000, 'karyawan', 'sma', 'sederhana', 'sehat', 'terverifikasi', CURDATE()),
|
|
('6171010101010005', 'Theresia Ani', 'Jl. Khatulistiwa', 'Paroki Santo Agustinus', 0.0023, 109.3237, 7, 6, 1150000, 'buruh_harian', 'sd', 'buruk', 'disabilitas', 'terverifikasi', CURDATE()),
|
|
('6171010101010006', 'Fransiskus Deni', 'Jl. Dr. Wahidin', 'Paroki Santo Paulus', -0.0364, 109.3151, 2, 1, 4200000, 'wiraswasta', 'diploma', 'layak', 'sehat', 'terverifikasi', CURDATE());
|
|
|
|
INSERT INTO bantuan (jenis_bantuan, tanggal_bantuan, nilai_bantuan, keterangan)
|
|
SELECT 'Paket Sembako', CURDATE(), 350000, 'Bantuan pangan bulanan'
|
|
WHERE NOT EXISTS (SELECT 1 FROM bantuan WHERE jenis_bantuan = 'Paket Sembako');
|
|
|
|
INSERT INTO bantuan (jenis_bantuan, tanggal_bantuan, nilai_bantuan, keterangan)
|
|
SELECT 'Bantuan Pendidikan', CURDATE(), 500000, 'Dukungan biaya sekolah'
|
|
WHERE NOT EXISTS (SELECT 1 FROM bantuan WHERE jenis_bantuan = 'Bantuan Pendidikan');
|