475 lines
28 KiB
SQL
475 lines
28 KiB
SQL
-- ================================================================
|
|
-- WebGIS Pengentasan Kemiskinan — Schema Database
|
|
-- Kota Pontianak, Kalimantan Barat
|
|
-- ================================================================
|
|
|
|
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
|
|
SET time_zone = "+07:00";
|
|
SET NAMES utf8mb4;
|
|
|
|
CREATE DATABASE IF NOT EXISTS `webgis_kemiskinan`
|
|
CHARACTER SET utf8mb4
|
|
COLLATE utf8mb4_unicode_ci;
|
|
USE `webgis_kemiskinan`;
|
|
|
|
-- ================================================================
|
|
-- TABEL ROLE
|
|
-- ================================================================
|
|
CREATE TABLE `role` (
|
|
`id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`nama_role` VARCHAR(50) NOT NULL,
|
|
`deskripsi` VARCHAR(255) DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uq_role_nama` (`nama_role`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
INSERT INTO `role` (`id`, `nama_role`, `deskripsi`) VALUES
|
|
(1, 'admin', 'Administrator sistem, akses penuh'),
|
|
(2, 'pengurus', 'Pengurus rumah ibadah, input & verifikasi data'),
|
|
(3, 'pimpinan', 'Pimpinan daerah, read-only & dashboard'),
|
|
(4, 'masyarakat', 'Warga umum, laporan & lihat data terbatas');
|
|
|
|
-- ================================================================
|
|
-- TABEL USER
|
|
-- ================================================================
|
|
CREATE TABLE `user` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`id_role` TINYINT UNSIGNED NOT NULL,
|
|
`username` VARCHAR(50) NOT NULL,
|
|
`password_hash` VARCHAR(255) NOT NULL,
|
|
`nama_lengkap` VARCHAR(100) NOT NULL,
|
|
`email` VARCHAR(100) DEFAULT NULL,
|
|
`no_hp` VARCHAR(20) DEFAULT NULL,
|
|
`aktif` TINYINT(1) NOT NULL DEFAULT 1,
|
|
`last_login` DATETIME DEFAULT NULL,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uq_username` (`username`),
|
|
KEY `fk_user_role` (`id_role`),
|
|
CONSTRAINT `fk_user_role` FOREIGN KEY (`id_role`) REFERENCES `role` (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ================================================================
|
|
-- TABEL RUMAH IBADAH
|
|
-- ================================================================
|
|
CREATE TABLE `rumah_ibadah` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`id_user` INT UNSIGNED DEFAULT NULL COMMENT 'Pengurus PJ',
|
|
`nama` VARCHAR(150) NOT NULL,
|
|
`jenis` ENUM('Masjid','Gereja','Vihara','Pura','Klenteng','Lainnya') NOT NULL,
|
|
`alamat` TEXT DEFAULT NULL,
|
|
`lat` DECIMAL(10,7) NOT NULL,
|
|
`lng` DECIMAL(10,7) NOT NULL,
|
|
`radius_meter` SMALLINT UNSIGNED NOT NULL DEFAULT 300,
|
|
`kontak` VARCHAR(50) DEFAULT NULL,
|
|
`kecamatan` VARCHAR(80) DEFAULT NULL,
|
|
`kelurahan` VARCHAR(80) DEFAULT NULL,
|
|
`aktif` TINYINT(1) NOT NULL DEFAULT 1,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `fk_ri_user` (`id_user`),
|
|
CONSTRAINT `fk_ri_user` FOREIGN KEY (`id_user`) REFERENCES `user` (`id`) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ================================================================
|
|
-- TABEL KELUARGA (KK)
|
|
-- ================================================================
|
|
CREATE TABLE `keluarga` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`no_kk` CHAR(16) NOT NULL COMMENT 'Nomor Kartu Keluarga',
|
|
`id_rumah_ibadah` INT UNSIGNED DEFAULT NULL COMMENT 'RI terdekat / pengelola',
|
|
`nama_kepala` VARCHAR(100) NOT NULL,
|
|
`alamat` TEXT DEFAULT NULL,
|
|
`rt` VARCHAR(5) DEFAULT NULL,
|
|
`rw` VARCHAR(5) DEFAULT NULL,
|
|
`kelurahan` VARCHAR(80) DEFAULT NULL,
|
|
`kecamatan` VARCHAR(80) DEFAULT NULL,
|
|
`lat` DECIMAL(10,7) DEFAULT NULL,
|
|
`lng` DECIMAL(10,7) DEFAULT NULL,
|
|
`status_ekonomi` ENUM('miskin','rentan','mampu') NOT NULL DEFAULT 'rentan',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uq_no_kk` (`no_kk`),
|
|
KEY `fk_kk_ri` (`id_rumah_ibadah`),
|
|
CONSTRAINT `fk_kk_ri` FOREIGN KEY (`id_rumah_ibadah`) REFERENCES `rumah_ibadah` (`id`) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ================================================================
|
|
-- TABEL PENDUDUK
|
|
-- ================================================================
|
|
CREATE TABLE `penduduk` (
|
|
`nik` CHAR(16) NOT NULL,
|
|
`id_keluarga` INT UNSIGNED NOT NULL,
|
|
`nama_lengkap` VARCHAR(100) NOT NULL,
|
|
`jenis_kelamin` ENUM('L','P') NOT NULL,
|
|
`tanggal_lahir` DATE NOT NULL,
|
|
-- umur dihitung di query: TIMESTAMPDIFF(YEAR, tanggal_lahir, CURDATE())
|
|
`tempat_lahir` VARCHAR(80) DEFAULT NULL,
|
|
`status_keluarga` ENUM('kepala','istri','anak','menantu','cucu','orang_tua','lainnya') NOT NULL DEFAULT 'anak',
|
|
`status_perkawinan` ENUM('belum_kawin','kawin','cerai_hidup','cerai_mati') NOT NULL DEFAULT 'belum_kawin',
|
|
`status_hidup` ENUM('hidup','meninggal') NOT NULL DEFAULT 'hidup',
|
|
`agama` ENUM('Islam','Kristen','Katolik','Hindu','Buddha','Konghucu','Lainnya') DEFAULT NULL,
|
|
`pekerjaan` VARCHAR(80) DEFAULT NULL,
|
|
`penghasilan` INT UNSIGNED DEFAULT 0 COMMENT 'Per bulan, rupiah',
|
|
`status_ekonomi` ENUM('miskin','rentan','mampu') NOT NULL DEFAULT 'rentan',
|
|
-- Pendidikan
|
|
`pendidikan_terakhir` ENUM('tidak_sekolah','SD','SMP','SMA_SMK','D1_D3','S1','S2_S3') DEFAULT 'tidak_sekolah',
|
|
`status_pendidikan` ENUM('sekolah','tidak_sekolah','lulus') DEFAULT 'tidak_sekolah',
|
|
-- Kesehatan
|
|
`kondisi_kesehatan` ENUM('sehat','sakit_ringan','sakit_berat','disabilitas') DEFAULT 'sehat',
|
|
`keterangan_kesehatan` VARCHAR(255) DEFAULT NULL,
|
|
-- Koordinat individual (bisa beda dari rumah)
|
|
`lat` DECIMAL(10,7) DEFAULT NULL,
|
|
`lng` DECIMAL(10,7) DEFAULT NULL,
|
|
-- Timestamps
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`nik`),
|
|
KEY `fk_pddk_kk` (`id_keluarga`),
|
|
KEY `idx_status_ekonomi` (`status_ekonomi`),
|
|
KEY `idx_status_hidup` (`status_hidup`),
|
|
CONSTRAINT `fk_pddk_kk` FOREIGN KEY (`id_keluarga`) REFERENCES `keluarga` (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ================================================================
|
|
-- TABEL RIWAYAT PENDIDIKAN / PELATIHAN
|
|
-- ================================================================
|
|
CREATE TABLE `riwayat_pendidikan` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`nik` CHAR(16) NOT NULL,
|
|
`jenis` ENUM('formal','pelatihan','kursus') NOT NULL DEFAULT 'formal',
|
|
`nama_institusi` VARCHAR(150) DEFAULT NULL,
|
|
`jurusan` VARCHAR(100) DEFAULT NULL,
|
|
`tahun_masuk` YEAR DEFAULT NULL,
|
|
`tahun_lulus` YEAR DEFAULT NULL,
|
|
`ijazah` TINYINT(1) DEFAULT 0,
|
|
`keterangan` TEXT DEFAULT NULL,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `fk_riwpend_nik` (`nik`),
|
|
CONSTRAINT `fk_riwpend_nik` FOREIGN KEY (`nik`) REFERENCES `penduduk` (`nik`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ================================================================
|
|
-- TABEL JENIS BANTUAN (master)
|
|
-- ================================================================
|
|
CREATE TABLE `bantuan` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`nama_bantuan` VARCHAR(100) NOT NULL,
|
|
`kategori` ENUM('sembako','pendidikan','kesehatan','ekonomi','lainnya') NOT NULL DEFAULT 'lainnya',
|
|
`sumber` VARCHAR(100) DEFAULT NULL COMMENT 'APBD, CSR, Zakat, dll',
|
|
`deskripsi` TEXT DEFAULT NULL,
|
|
`satuan` VARCHAR(50) DEFAULT NULL COMMENT 'karung, paket, Rp, dll',
|
|
`aktif` TINYINT(1) NOT NULL DEFAULT 1,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ================================================================
|
|
-- TABEL HISTORI BANTUAN
|
|
-- ================================================================
|
|
CREATE TABLE `histori_bantuan` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`nik` CHAR(16) NOT NULL,
|
|
`id_bantuan` INT UNSIGNED NOT NULL,
|
|
`id_rumah_ibadah` INT UNSIGNED DEFAULT NULL,
|
|
`id_user` INT UNSIGNED DEFAULT NULL COMMENT 'Petugas penyalur',
|
|
`tanggal` DATE NOT NULL,
|
|
`jumlah` DECIMAL(10,2) DEFAULT 1,
|
|
`keterangan` TEXT DEFAULT NULL,
|
|
`status_penyaluran` ENUM('direncanakan','disalurkan','ditolak') NOT NULL DEFAULT 'disalurkan',
|
|
`bukti_foto` VARCHAR(255) DEFAULT NULL,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `fk_hb_nik` (`nik`),
|
|
KEY `fk_hb_bantuan` (`id_bantuan`),
|
|
KEY `fk_hb_ri` (`id_rumah_ibadah`),
|
|
KEY `fk_hb_user` (`id_user`),
|
|
CONSTRAINT `fk_hb_nik` FOREIGN KEY (`nik`) REFERENCES `penduduk` (`nik`),
|
|
CONSTRAINT `fk_hb_bantuan` FOREIGN KEY (`id_bantuan`) REFERENCES `bantuan` (`id`),
|
|
CONSTRAINT `fk_hb_ri` FOREIGN KEY (`id_rumah_ibadah`) REFERENCES `rumah_ibadah` (`id`) ON DELETE SET NULL,
|
|
CONSTRAINT `fk_hb_user` FOREIGN KEY (`id_user`) REFERENCES `user` (`id`) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ================================================================
|
|
-- TABEL LAPORAN MASYARAKAT
|
|
-- ================================================================
|
|
CREATE TABLE `laporan` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`nama_pelapor` VARCHAR(100) DEFAULT NULL,
|
|
`no_hp_pelapor` VARCHAR(20) DEFAULT NULL,
|
|
`nik_terdampak` CHAR(16) DEFAULT NULL COMMENT 'Penduduk yg dilaporkan (opsional)',
|
|
`id_rumah_ibadah` INT UNSIGNED DEFAULT NULL,
|
|
`deskripsi` TEXT NOT NULL,
|
|
`lat` DECIMAL(10,7) DEFAULT NULL,
|
|
`lng` DECIMAL(10,7) DEFAULT NULL,
|
|
`alamat_laporan` TEXT DEFAULT NULL,
|
|
`urgensi` ENUM('rendah','sedang','tinggi','darurat') NOT NULL DEFAULT 'sedang',
|
|
`foto` VARCHAR(255) DEFAULT NULL,
|
|
`status` ENUM('pending','diverifikasi','diproses','selesai') NOT NULL DEFAULT 'pending',
|
|
`catatan_verifikasi` TEXT DEFAULT NULL,
|
|
`id_verifikator` INT UNSIGNED DEFAULT NULL,
|
|
`tgl_verifikasi` DATETIME DEFAULT NULL,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `fk_lap_nik` (`nik_terdampak`),
|
|
KEY `fk_lap_ri` (`id_rumah_ibadah`),
|
|
KEY `fk_lap_verif` (`id_verifikator`),
|
|
CONSTRAINT `fk_lap_nik` FOREIGN KEY (`nik_terdampak`) REFERENCES `penduduk` (`nik`) ON DELETE SET NULL,
|
|
CONSTRAINT `fk_lap_ri` FOREIGN KEY (`id_rumah_ibadah`) REFERENCES `rumah_ibadah` (`id`) ON DELETE SET NULL,
|
|
CONSTRAINT `fk_lap_verif` FOREIGN KEY (`id_verifikator`) REFERENCES `user` (`id`) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ================================================================
|
|
-- TABEL CHAT / KOMUNIKASI
|
|
-- ================================================================
|
|
CREATE TABLE `chat` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`id_user` INT UNSIGNED NOT NULL,
|
|
`id_room` INT UNSIGNED DEFAULT NULL COMMENT 'NULL = channel umum',
|
|
`pesan` TEXT NOT NULL,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `fk_chat_user` (`id_user`),
|
|
CONSTRAINT `fk_chat_user` FOREIGN KEY (`id_user`) REFERENCES `user` (`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Chat rooms
|
|
CREATE TABLE `chat_room` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`nama` VARCHAR(100) NOT NULL,
|
|
`deskripsi` VARCHAR(255) DEFAULT NULL,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
ALTER TABLE `chat` ADD CONSTRAINT `fk_chat_room` FOREIGN KEY (`id_room`) REFERENCES `chat_room` (`id`) ON DELETE SET NULL;
|
|
|
|
-- ================================================================
|
|
-- TABEL LOG AKTIVITAS
|
|
-- ================================================================
|
|
CREATE TABLE `log_aktivitas` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`id_user` INT UNSIGNED DEFAULT NULL,
|
|
`aksi` VARCHAR(100) NOT NULL COMMENT 'create_penduduk, update_bantuan, dll',
|
|
`tabel` VARCHAR(50) DEFAULT NULL,
|
|
`id_record` VARCHAR(50) DEFAULT NULL COMMENT 'PK record yg diubah',
|
|
`data_lama` JSON DEFAULT NULL,
|
|
`data_baru` JSON DEFAULT NULL,
|
|
`ip_address` VARCHAR(45) DEFAULT NULL,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `fk_log_user` (`id_user`),
|
|
KEY `idx_log_aksi` (`aksi`),
|
|
KEY `idx_log_created` (`created_at`),
|
|
CONSTRAINT `fk_log_user` FOREIGN KEY (`id_user`) REFERENCES `user` (`id`) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ================================================================
|
|
-- TABEL PERUBAHAN STATUS (kelahiran, kematian, dll)
|
|
-- ================================================================
|
|
CREATE TABLE `peristiwa_kependudukan` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`nik` CHAR(16) NOT NULL,
|
|
`jenis` ENUM('kelahiran','kematian','pernikahan','perceraian','pindah_masuk','pindah_keluar') NOT NULL,
|
|
`tanggal` DATE NOT NULL,
|
|
`keterangan` TEXT DEFAULT NULL,
|
|
`id_user` INT UNSIGNED DEFAULT NULL,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `fk_perk_nik` (`nik`),
|
|
KEY `fk_perk_user` (`id_user`),
|
|
CONSTRAINT `fk_perk_nik` FOREIGN KEY (`nik`) REFERENCES `penduduk` (`nik`) ON DELETE CASCADE,
|
|
CONSTRAINT `fk_perk_user` FOREIGN KEY (`id_user`) REFERENCES `user` (`id`) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ================================================================
|
|
-- SEED DATA — Users
|
|
-- ================================================================
|
|
-- Password: admin123 (bcrypt hash contoh)
|
|
INSERT INTO `user` (`id_role`,`username`,`password_hash`,`nama_lengkap`,`email`) VALUES
|
|
(1, 'admin', '$2y$10$SCqLjlnUWWOlz6DIB8cXB.aABZyThMWfnf0facH2Lylv4jbs5NQiS', 'Administrator Sistem', 'admin@webgis-ptk.id'),
|
|
(2, 'pengurus_jami','$2y$10$SCqLjlnUWWOlz6DIB8cXB.aABZyThMWfnf0facH2Lylv4jbs5NQiS', 'Ahmad Fauzi (Masjid Jami)', 'fauzi@webgis-ptk.id'),
|
|
(2, 'pengurus_ktdr','$2y$10$SCqLjlnUWWOlz6DIB8cXB.aABZyThMWfnf0facH2Lylv4jbs5NQiS', 'Romo Benediktus (Katedral)', 'romo@webgis-ptk.id'),
|
|
(3, 'pimpinan', '$2y$10$SCqLjlnUWWOlz6DIB8cXB.aABZyThMWfnf0facH2Lylv4jbs5NQiS', 'Camat Pontianak Kota', 'camat@webgis-ptk.id'),
|
|
(4, 'warga1', '$2y$10$SCqLjlnUWWOlz6DIB8cXB.aABZyThMWfnf0facH2Lylv4jbs5NQiS', 'Budi Santoso', 'budi@gmail.com');
|
|
|
|
-- ================================================================
|
|
-- SEED DATA — Rumah Ibadah
|
|
-- ================================================================
|
|
INSERT INTO `rumah_ibadah` (`id_user`,`nama`,`jenis`,`alamat`,`lat`,`lng`,`radius_meter`,`kontak`,`kecamatan`,`kelurahan`) VALUES
|
|
(2, 'Masjid Jami\' Pontianak', 'Masjid', 'Jl. Tanjungpura, Dalam Bugis', -0.0259600, 109.3430700, 350, '0561-732001', 'Pontianak Timur', 'Dalam Bugis'),
|
|
(3, 'Gereja Katedral Pontianak', 'Gereja', 'Jl. Patimura No.9, Akcaya', -0.0320000, 109.3320000, 300, '0561-766000', 'Pontianak Selatan', 'Akcaya'),
|
|
(NULL,'Vihara Bodhisattva Karaniya Metta', 'Vihara', 'Jl. Diponegoro, Pontianak Kota', -0.0210000, 109.3380000, 300, '0561-733456', 'Pontianak Kota', 'Darat Sekip'),
|
|
(NULL,'Klenteng Kwan Ti Miau', 'Klenteng','Jl. Gusti Situt Mahmud', -0.0190000, 109.3410000, 280, '0561-740123', 'Pontianak Kota', 'Pontianak Kota'),
|
|
(2, 'Masjid Mujahidin Pontianak', 'Masjid', 'Jl. Ahmad Yani, Parit Tokaya', -0.0480000, 109.3250000, 400, '0561-764567', 'Pontianak Selatan', 'Parit Tokaya'),
|
|
(3, 'Gereja GPIB Immanuel', 'Gereja', 'Jl. Rahadi Usman, Darat Sekip', -0.0280000, 109.3450000, 300, '0561-735890', 'Pontianak Kota', 'Darat Sekip');
|
|
|
|
-- ================================================================
|
|
-- SEED DATA — Keluarga & Penduduk
|
|
-- ================================================================
|
|
INSERT INTO `keluarga` (`no_kk`,`id_rumah_ibadah`,`nama_kepala`,`alamat`,`rt`,`rw`,`kelurahan`,`kecamatan`,`lat`,`lng`,`status_ekonomi`) VALUES
|
|
('6171010101010001', 1, 'Ahmad Suryana', 'Gg. Mawar No.5', '002','004','Dalam Bugis', 'Pontianak Timur', -0.0270000, 109.3440000, 'miskin'),
|
|
('6171010101010002', 1, 'Samsul Bahri', 'Jl. Veteran No.12', '003','002','Sungai Bangkong','Pontianak Kota', -0.0315000, 109.3390000, 'rentan'),
|
|
('6171010101010003', 2, 'Maria Magdalena', 'Jl. Kemuning No.3', '001','001','Akcaya', 'Pontianak Selatan', -0.0330000, 109.3310000, 'miskin'),
|
|
('6171010101010004', 5, 'Hendra Wijaya', 'Jl. Ahmad Yani Gg. 7', '005','003','Parit Tokaya', 'Pontianak Selatan', -0.0490000, 109.3260000, 'rentan'),
|
|
('6171010101010005', 3, 'Lim Ah Kow', 'Jl. Diponegoro Gg. 2', '001','002','Darat Sekip', 'Pontianak Kota', -0.0220000, 109.3370000, 'miskin'),
|
|
('6171010101010006', 1, 'Mulyadi', 'Gg. Teratai No.8', '004','003','Dalam Bugis', 'Pontianak Timur', -0.0265000, 109.3460000, 'mampu'),
|
|
('6171010101010007', 2, 'Yohanes Kristian', 'Jl. Hos Cokroaminoto No.4','002','001','Akcaya', 'Pontianak Selatan', -0.0340000, 109.3300000, 'rentan'),
|
|
('6171010101010008', 4, 'Tan Eng Huat', 'Jl. Gusti Situt No.7', '003','001','Pontianak Kota', 'Pontianak Kota', -0.0200000, 109.3415000, 'miskin');
|
|
|
|
INSERT INTO `penduduk` (`nik`,`id_keluarga`,`nama_lengkap`,`jenis_kelamin`,`tanggal_lahir`,`tempat_lahir`,`status_keluarga`,`status_perkawinan`,`agama`,`pekerjaan`,`penghasilan`,`status_ekonomi`,`pendidikan_terakhir`,`status_pendidikan`,`kondisi_kesehatan`,`lat`,`lng`) VALUES
|
|
('6171010101010001', 1, 'Ahmad Suryana', 'L', '1980-05-15', 'Pontianak', 'kepala', 'kawin', 'Islam', 'Buruh harian', 900000, 'miskin', 'SMP', 'lulus', 'sehat', -0.0270000, 109.3440000),
|
|
('6171010101010002', 1, 'Siti Rahma', 'P', '1985-08-20', 'Pontianak', 'istri', 'kawin', 'Islam', 'Ibu rumah tangga', 0, 'miskin', 'SD', 'lulus', 'sakit_ringan',-0.0270000, 109.3440000),
|
|
('6171010101010003', 1, 'Budi Suryana', 'L', '2012-03-10', 'Pontianak', 'anak', 'belum_kawin', 'Islam', 'Pelajar', 0, 'miskin', 'SMP', 'sekolah', 'sehat', -0.0270000, 109.3440000),
|
|
('6171010101010004', 2, 'Samsul Bahri', 'L', '1975-11-22', 'Sambas', 'kepala', 'kawin', 'Islam', 'Pedagang kaki lima', 1200000, 'rentan', 'SMA_SMK', 'lulus', 'sehat', -0.0315000, 109.3390000),
|
|
('6171010101010005', 2, 'Dewi Kartika', 'P', '1978-06-14', 'Pontianak', 'istri', 'kawin', 'Islam', 'Buruh cuci', 500000, 'rentan', 'SD', 'lulus', 'sehat', -0.0315000, 109.3390000),
|
|
('6171010101010006', 3, 'Maria Magdalena', 'P', '1970-12-03', 'Pontianak', 'kepala', 'cerai_mati', 'Kristen', 'Penjual gorengan', 800000, 'miskin', 'SD', 'lulus', 'sakit_berat', -0.0330000, 109.3310000),
|
|
('6171010101010007', 3, 'Petrus Yulius', 'L', '2005-04-17', 'Pontianak', 'anak', 'belum_kawin', 'Kristen', 'Pelajar', 0, 'miskin', 'SMA_SMK', 'sekolah', 'sehat', -0.0330000, 109.3310000),
|
|
('6171010101010008', 3, 'Anastasia Dewi', 'P', '2010-09-28', 'Pontianak', 'anak', 'belum_kawin', 'Kristen', 'Pelajar', 0, 'miskin', 'SMP', 'sekolah', 'sehat', -0.0330000, 109.3310000),
|
|
('6171010101010009', 4, 'Hendra Wijaya', 'L', '1988-02-11', 'Pontianak', 'kepala', 'kawin', 'Buddha', 'Tukang ojek', 1500000, 'rentan', 'SMA_SMK', 'lulus', 'sehat', -0.0490000, 109.3260000),
|
|
('6171010101010010', 5, 'Lim Ah Kow', 'L', '1965-07-30', 'Pontianak', 'kepala', 'kawin', 'Buddha', 'Tidak bekerja', 0, 'miskin', 'SD', 'lulus', 'sakit_berat', -0.0220000, 109.3370000),
|
|
('6171010101010011', 5, 'Tan Siu Lan', 'P', '1968-03-19', 'Pontianak', 'istri', 'kawin', 'Buddha', 'Penjual sayur', 700000, 'miskin', 'SD', 'lulus', 'sehat', -0.0220000, 109.3370000),
|
|
('6171010101010012', 6, 'Mulyadi', 'L', '1979-10-05', 'Singkawang','kepala', 'kawin', 'Islam', 'PNS Golongan III', 4500000, 'mampu', 'S1', 'lulus', 'sehat', -0.0265000, 109.3460000),
|
|
('6171010101010013', 7, 'Yohanes Kristian', 'L', '1982-01-25', 'Pontianak', 'kepala', 'kawin', 'Katolik', 'Sopir angkot', 1100000, 'rentan', 'SMA_SMK', 'lulus', 'sehat', -0.0340000, 109.3300000),
|
|
('6171010101010014', 8, 'Tan Eng Huat', 'L', '1960-08-08', 'Pontianak', 'kepala', 'kawin', 'Konghucu','Pemulung', 600000, 'miskin', 'SD', 'lulus', 'disabilitas', -0.0200000, 109.3415000);
|
|
|
|
-- ================================================================
|
|
-- SEED DATA — Bantuan Master
|
|
-- ================================================================
|
|
INSERT INTO `bantuan` (`nama_bantuan`,`kategori`,`sumber`,`deskripsi`,`satuan`) VALUES
|
|
('Sembako Ramadan', 'sembako', 'Zakat Masjid', 'Paket sembako bulan Ramadan', 'paket'),
|
|
('PKH (Program Keluarga Harapan)', 'ekonomi', 'APBN', 'Bantuan tunai conditional', 'Rp'),
|
|
('BPNT / Kartu Sembako', 'sembako', 'APBN', 'Bantuan pangan non-tunai Rp200rb/bln', 'bulan'),
|
|
('Beasiswa Pendidikan', 'pendidikan', 'CSR Perusahaan', 'Beasiswa untuk anak sekolah', 'paket/tahun'),
|
|
('BPJS Kesehatan PBI', 'kesehatan', 'APBD', 'Iuran BPJS ditanggung pemerintah', 'bulan'),
|
|
('Bantuan Sembako Gereja', 'sembako', 'Kolekte Gereja', 'Paket bulanan dari gereja', 'paket'),
|
|
('Bantuan Vihara (Kwan Im)', 'sembako', 'Dana Sosial Vihara', 'Paket sembako bulanan', 'paket'),
|
|
('Raskin / BERAS MISKIN', 'sembako', 'Bulog', '10kg per bulan per KK', 'kg');
|
|
|
|
-- ================================================================
|
|
-- SEED DATA — Histori Bantuan
|
|
-- ================================================================
|
|
INSERT INTO `histori_bantuan` (`nik`,`id_bantuan`,`id_rumah_ibadah`,`id_user`,`tanggal`,`jumlah`,`status_penyaluran`) VALUES
|
|
('6171010101010001', 1, 1, 2, '2025-03-15', 1, 'disalurkan'),
|
|
('6171010101010001', 3, 1, 2, '2025-01-10', 1, 'disalurkan'),
|
|
('6171010101010006', 6, 2, 3, '2025-02-20', 1, 'disalurkan'),
|
|
('6171010101010010', 7, 3, 2, '2025-04-01', 1, 'disalurkan'),
|
|
('6171010101010014', 8, 4, 2, '2025-03-05', 10,'disalurkan');
|
|
|
|
-- ================================================================
|
|
-- SEED DATA — Laporan Masyarakat
|
|
-- ================================================================
|
|
INSERT INTO `laporan` (`nama_pelapor`,`nik_terdampak`,`id_rumah_ibadah`,`deskripsi`,`lat`,`lng`,`alamat_laporan`,`urgensi`,`status`) VALUES
|
|
('Anonim', '6171010101010006', 2, 'Ibu Maria sakit keras sudah seminggu, tidak bisa berobat karena tidak ada biaya', -0.0330, 109.3311, 'Jl. Kemuning No.3, Akcaya', 'darurat', 'diverifikasi'),
|
|
('Eko Prasetyo', NULL, 1, 'Ada keluarga baru pindah ke Gg. Melati yang sepertinya sangat membutuhkan bantuan', -0.0268, 109.3448, 'Gg. Melati, Dalam Bugis', 'sedang', 'pending'),
|
|
('Anonim', '6171010101010014', 4, 'Pak Tan penyandang disabilitas, hidup sebatang kara dan sulit mendapat penghasilan', -0.0200, 109.3415, 'Jl. Gusti Situt No.7', 'tinggi', 'diproses');
|
|
|
|
-- ================================================================
|
|
-- SEED DATA — Chat Room
|
|
-- ================================================================
|
|
INSERT INTO `chat_room` (`nama`,`deskripsi`) VALUES
|
|
('Koordinasi Umum', 'Channel koordinasi semua pengurus rumah ibadah'),
|
|
('Bantuan Darurat', 'Koordinasi penanganan kasus darurat & prioritas'),
|
|
('Distribusi Sembako', 'Jadwal & koordinasi distribusi sembako bulanan');
|
|
|
|
-- ================================================================
|
|
-- VIEW — Data lengkap penduduk (dengan umur otomatis)
|
|
-- ================================================================
|
|
CREATE OR REPLACE VIEW `v_penduduk_lengkap` AS
|
|
SELECT
|
|
p.nik,
|
|
p.nama_lengkap,
|
|
p.jenis_kelamin,
|
|
p.tanggal_lahir,
|
|
TIMESTAMPDIFF(YEAR, p.tanggal_lahir, CURDATE()) AS umur,
|
|
p.tempat_lahir,
|
|
p.status_keluarga,
|
|
p.status_perkawinan,
|
|
p.status_hidup,
|
|
p.agama,
|
|
p.pekerjaan,
|
|
p.penghasilan,
|
|
p.status_ekonomi,
|
|
p.pendidikan_terakhir,
|
|
p.status_pendidikan,
|
|
p.kondisi_kesehatan,
|
|
p.lat,
|
|
p.lng,
|
|
k.no_kk,
|
|
k.nama_kepala,
|
|
k.alamat,
|
|
k.rt,
|
|
k.rw,
|
|
k.kelurahan,
|
|
k.kecamatan,
|
|
k.status_ekonomi AS status_ekonomi_kk,
|
|
ri.nama AS nama_ri,
|
|
ri.jenis AS jenis_ri,
|
|
p.created_at,
|
|
p.updated_at
|
|
FROM `penduduk` p
|
|
JOIN `keluarga` k ON p.id_keluarga = k.id
|
|
LEFT JOIN `rumah_ibadah` ri ON k.id_rumah_ibadah = ri.id;
|
|
|
|
-- ================================================================
|
|
-- VIEW — Statistik keluarga
|
|
-- ================================================================
|
|
CREATE OR REPLACE VIEW `v_statistik_keluarga` AS
|
|
SELECT
|
|
k.id,
|
|
k.no_kk,
|
|
k.nama_kepala,
|
|
k.status_ekonomi,
|
|
k.kecamatan,
|
|
k.kelurahan,
|
|
k.lat,
|
|
k.lng,
|
|
COUNT(p.nik) AS jumlah_anggota,
|
|
SUM(CASE WHEN p.kondisi_kesehatan IN ('sakit_berat','disabilitas') THEN 1 ELSE 0 END) AS anggota_sakit,
|
|
SUM(p.penghasilan) AS total_penghasilan,
|
|
COUNT(hb.id) AS total_bantuan_diterima,
|
|
MAX(hb.tanggal) AS terakhir_dibantu,
|
|
ri.nama AS nama_ri
|
|
FROM `keluarga` k
|
|
LEFT JOIN `penduduk` p ON p.id_keluarga = k.id AND p.status_hidup = 'hidup'
|
|
LEFT JOIN `histori_bantuan` hb ON hb.nik = p.nik AND hb.status_penyaluran = 'disalurkan'
|
|
LEFT JOIN `rumah_ibadah` ri ON k.id_rumah_ibadah = ri.id
|
|
GROUP BY k.id;
|
|
|
|
-- ================================================================
|
|
-- VIEW — Prioritas bantuan (miskin + sakit & belum dibantu)
|
|
-- ================================================================
|
|
CREATE OR REPLACE VIEW `v_prioritas_bantuan` AS
|
|
SELECT
|
|
p.nik,
|
|
p.nama_lengkap,
|
|
TIMESTAMPDIFF(YEAR, p.tanggal_lahir, CURDATE()) AS umur,
|
|
p.kondisi_kesehatan,
|
|
p.status_ekonomi,
|
|
k.nama_kepala AS kepala_keluarga,
|
|
k.alamat,
|
|
k.kecamatan,
|
|
k.lat,
|
|
k.lng,
|
|
ri.nama AS nama_ri,
|
|
COUNT(hb.id) AS jumlah_dibantu,
|
|
CASE
|
|
WHEN p.status_ekonomi = 'miskin' AND p.kondisi_kesehatan IN ('sakit_berat','disabilitas') THEN 'SANGAT TINGGI'
|
|
WHEN p.status_ekonomi = 'miskin' AND COUNT(hb.id) = 0 THEN 'TINGGI'
|
|
WHEN p.status_ekonomi = 'miskin' THEN 'SEDANG'
|
|
WHEN p.status_ekonomi = 'rentan' AND p.kondisi_kesehatan IN ('sakit_berat','disabilitas') THEN 'SEDANG'
|
|
ELSE 'RENDAH'
|
|
END AS level_prioritas
|
|
FROM `penduduk` p
|
|
JOIN `keluarga` k ON p.id_keluarga = k.id
|
|
LEFT JOIN `rumah_ibadah` ri ON k.id_rumah_ibadah = ri.id
|
|
LEFT JOIN `histori_bantuan` hb ON hb.nik = p.nik AND hb.status_penyaluran = 'disalurkan'
|
|
WHERE p.status_hidup = 'hidup'
|
|
AND p.status_ekonomi IN ('miskin','rentan')
|
|
GROUP BY p.nik;
|