muthianura
221 lines
8.8 KiB
SQL
221 lines
8.8 KiB
SQL
-- phpMyAdmin SQL Dump
|
|
-- version 5.2.1
|
|
-- https://www.phpmyadmin.net/
|
|
--
|
|
-- Host: 127.0.0.1
|
|
-- Waktu pembuatan: 13 Jun 2026 pada 06.34
|
|
-- Versi server: 10.4.32-MariaDB
|
|
-- Versi PHP: 8.2.12
|
|
|
|
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
|
|
START TRANSACTION;
|
|
SET time_zone = "+00:00";
|
|
|
|
|
|
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
|
|
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
|
|
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
|
|
/*!40101 SET NAMES utf8mb4 */;
|
|
|
|
--
|
|
-- Database: `gis_penduduk_miskin`
|
|
--
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Struktur dari tabel `penduduk_miskin`
|
|
--
|
|
|
|
CREATE TABLE `penduduk_miskin` (
|
|
`id` int(11) NOT NULL,
|
|
`nama_kk` varchar(200) NOT NULL COMMENT 'Nama Kepala Keluarga',
|
|
`jumlah_anggota` int(11) NOT NULL DEFAULT 1 COMMENT 'Jumlah Anggota KK',
|
|
`alamat` text DEFAULT NULL,
|
|
`foto_rumah` varchar(255) DEFAULT NULL COMMENT 'Path foto rumah penduduk',
|
|
`foto` varchar(255) DEFAULT NULL,
|
|
`lat` double NOT NULL,
|
|
`lng` double NOT NULL,
|
|
`rumah_ibadah_id` int(11) DEFAULT NULL COMMENT 'Rumah ibadah terdekat (FK)',
|
|
`jarak_ke_ibadah` double DEFAULT NULL COMMENT 'Jarak dalam meter ke rumah ibadah terdekat',
|
|
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
|
|
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
`jenis_bantuan` varchar(100) DEFAULT NULL,
|
|
`tanggal_bantuan` date DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
--
|
|
-- Dumping data untuk tabel `penduduk_miskin`
|
|
--
|
|
|
|
INSERT INTO `penduduk_miskin` (`id`, `nama_kk`, `jumlah_anggota`, `alamat`, `foto_rumah`, `foto`, `lat`, `lng`, `rumah_ibadah_id`, `jarak_ke_ibadah`, `created_at`, `updated_at`, `jenis_bantuan`, `tanggal_bantuan`) VALUES
|
|
(1, 'Budi Santoso', 4, 'Jl. Ahmad Yani Gg. 3, Pontianak', NULL, NULL, -0.041, 109.333, 1, 7.3496927573051005, '2026-04-21 00:39:18', '2026-04-27 23:39:22', NULL, NULL),
|
|
(2, 'Siti Rahayu', 3, 'Jl. Ahmad Yani Gg. 5, Pontianak', NULL, NULL, -0.038, 109.331, 1, 402.74089736140365, '2026-04-21 00:39:18', '2026-04-27 23:39:22', NULL, NULL),
|
|
(3, 'Muhammad Amin', 5, 'Jl. Tanjungpura No. 12, Pontianak', NULL, NULL, -0.0255, 109.342, 2, 118.20182314920238, '2026-04-21 00:39:18', '2026-06-11 07:43:32', 'BPH', '2026-06-11'),
|
|
(4, 'Dewi Lestari', 2, 'Jl. Gajahmada No. 7, Pontianak', 'uploads/foto_rumah/foto_4_1779805588.png', NULL, -0.029, 109.336, 6, 365.21444098246127, '2026-04-21 00:39:18', '2026-06-11 10:48:25', 'BPH', NULL),
|
|
(5, 'Hendra Kusuma', 6, 'Jl. Khatulistiwa Gg. 2, Pontianak', NULL, NULL, -0.013, 109.326, NULL, NULL, '2026-04-21 00:39:18', '2026-06-11 10:45:24', NULL, NULL),
|
|
(6, 'Yanti Wulandari', 3, 'Jl. Reformasi, Pontianak Utara', NULL, NULL, -0.01, 109.324, NULL, NULL, '2026-04-21 00:39:18', '2026-04-21 01:31:32', NULL, NULL),
|
|
(7, 'Agus Prasetyo', 4, 'Jl. Sultan Hamid, Pontianak Barat', NULL, NULL, -0.045, 109.32, 7, 424.59694642882687, '2026-04-21 00:39:18', '2026-06-11 10:51:32', NULL, NULL),
|
|
(8, 'Sumanto', 1, 'Jalan Teuku Umar, Pontianak Kota, Pontianak, Kalimantan Barat', NULL, NULL, -0.031044206655679432, 109.33486461639406, 6, 229.58461183844528, '2026-04-27 23:23:35', '2026-06-11 10:48:25', NULL, NULL),
|
|
(9, 'Andi', 1, 'Gang Dua, Pontianak Kota, Pontianak, Kalimantan Barat', 'uploads/foto_rumah/foto_9_1779806089.jpeg', NULL, -0.03261019378425077, 109.33256864547731, 6, 444.5282858786418, '2026-04-27 23:24:17', '2026-06-11 10:48:25', NULL, NULL);
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Struktur dari tabel `riwayat_bantuan`
|
|
--
|
|
|
|
CREATE TABLE `riwayat_bantuan` (
|
|
`id` int(11) NOT NULL,
|
|
`penduduk_id` int(11) NOT NULL,
|
|
`jenis_bantuan` varchar(100) NOT NULL,
|
|
`tanggal_pemberian` date NOT NULL,
|
|
`keterangan` text DEFAULT NULL,
|
|
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
|
|
`rumah_ibadah_id` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
--
|
|
-- Dumping data untuk tabel `riwayat_bantuan`
|
|
--
|
|
|
|
INSERT INTO `riwayat_bantuan` (`id`, `penduduk_id`, `jenis_bantuan`, `tanggal_pemberian`, `keterangan`, `created_at`, `rumah_ibadah_id`) VALUES
|
|
(1, 3, 'BPH', '2026-06-11', 'Bantuan Pangan Tahap I', '2026-06-11 07:43:32', NULL);
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Struktur dari tabel `rumah_ibadah`
|
|
--
|
|
|
|
CREATE TABLE `rumah_ibadah` (
|
|
`id` int(11) NOT NULL,
|
|
`nama` varchar(200) NOT NULL,
|
|
`jenis` enum('Masjid','Gereja','Pura','Vihara','Kelenteng','Lainnya') DEFAULT 'Masjid',
|
|
`alamat` text DEFAULT NULL,
|
|
`pic` varchar(150) DEFAULT NULL COMMENT 'Person In Charge',
|
|
`no_wa` varchar(20) DEFAULT NULL,
|
|
`lat` double NOT NULL,
|
|
`lng` double NOT NULL,
|
|
`radius` int(11) DEFAULT 500 COMMENT 'Radius dalam meter',
|
|
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
|
|
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
--
|
|
-- Dumping data untuk tabel `rumah_ibadah`
|
|
--
|
|
|
|
INSERT INTO `rumah_ibadah` (`id`, `nama`, `jenis`, `alamat`, `pic`, `no_wa`, `lat`, `lng`, `radius`, `created_at`, `updated_at`) VALUES
|
|
(1, 'Masjid Raya Mujahidin', 'Masjid', 'Jl. Ahmad Yani, Pontianak Selatan', 'H. Abdurrahman', '081234567890', -0.040977560852975214, 109.33306217193605, 500, '2026-04-21 00:39:18', '2026-04-27 23:39:22'),
|
|
(2, 'Masjid Jami Pontianak', 'Masjid', 'Jl. Tanjungpura, Pontianak Kota', 'H. Mukhlis', '081234567891', -0.0263, 109.3413, 500, '2026-04-21 00:39:18', '2026-04-27 13:30:18'),
|
|
(6, 'Vihara Paticca Samuppada', 'Vihara', 'Jalan Wage Rudolf Supratman, Pontianak Selatan, Pontianak, Kalimantan Barat', 'Vira', '0812090807', -0.032238236952232296, 109.33654904365541, 500, '2026-06-11 10:48:25', '2026-06-11 10:48:25'),
|
|
(7, 'GBI El Shaddai', 'Gereja', 'Jalan Mohammad Yamin, Pontianak Kota, Pontianak, Kubu Raya, Kalimantan Barat', 'Gerry', '08912192112', -0.048485944221699245, 109.31844145059587, 500, '2026-06-11 10:51:32', '2026-06-11 10:51:32');
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Struktur dari tabel `users`
|
|
--
|
|
|
|
CREATE TABLE `users` (
|
|
`id` int(11) NOT NULL,
|
|
`nama` varchar(100) NOT NULL,
|
|
`username` varchar(50) NOT NULL,
|
|
`password` varchar(255) NOT NULL,
|
|
`role` enum('admin','superadmin') NOT NULL DEFAULT 'admin',
|
|
`created_at` timestamp NOT NULL DEFAULT current_timestamp()
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
--
|
|
-- Dumping data untuk tabel `users`
|
|
--
|
|
|
|
INSERT INTO `users` (`id`, `nama`, `username`, `password`, `role`, `created_at`) VALUES
|
|
(1, 'Super Admin', 'superadmin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'superadmin', '2026-05-26 15:26:44'),
|
|
(2, 'Admin', 'admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', '2026-05-26 15:26:44');
|
|
|
|
--
|
|
-- Indexes for dumped tables
|
|
--
|
|
|
|
--
|
|
-- Indeks untuk tabel `penduduk_miskin`
|
|
--
|
|
ALTER TABLE `penduduk_miskin`
|
|
ADD PRIMARY KEY (`id`),
|
|
ADD KEY `idx_penduduk_coords` (`lat`,`lng`),
|
|
ADD KEY `idx_penduduk_ibadah` (`rumah_ibadah_id`);
|
|
|
|
--
|
|
-- Indeks untuk tabel `riwayat_bantuan`
|
|
--
|
|
ALTER TABLE `riwayat_bantuan`
|
|
ADD PRIMARY KEY (`id`),
|
|
ADD KEY `penduduk_id` (`penduduk_id`),
|
|
ADD KEY `rumah_ibadah_id` (`rumah_ibadah_id`);
|
|
|
|
--
|
|
-- Indeks untuk tabel `rumah_ibadah`
|
|
--
|
|
ALTER TABLE `rumah_ibadah`
|
|
ADD PRIMARY KEY (`id`),
|
|
ADD KEY `idx_rumah_ibadah_coords` (`lat`,`lng`);
|
|
|
|
--
|
|
-- Indeks untuk tabel `users`
|
|
--
|
|
ALTER TABLE `users`
|
|
ADD PRIMARY KEY (`id`),
|
|
ADD UNIQUE KEY `username` (`username`);
|
|
|
|
--
|
|
-- AUTO_INCREMENT untuk tabel yang dibuang
|
|
--
|
|
|
|
--
|
|
-- AUTO_INCREMENT untuk tabel `penduduk_miskin`
|
|
--
|
|
ALTER TABLE `penduduk_miskin`
|
|
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
|
|
|
|
--
|
|
-- AUTO_INCREMENT untuk tabel `riwayat_bantuan`
|
|
--
|
|
ALTER TABLE `riwayat_bantuan`
|
|
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
|
|
|
|
--
|
|
-- AUTO_INCREMENT untuk tabel `rumah_ibadah`
|
|
--
|
|
ALTER TABLE `rumah_ibadah`
|
|
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
|
|
|
|
--
|
|
-- AUTO_INCREMENT untuk tabel `users`
|
|
--
|
|
ALTER TABLE `users`
|
|
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
|
|
|
|
--
|
|
-- Ketidakleluasaan untuk tabel pelimpahan (Dumped Tables)
|
|
--
|
|
|
|
--
|
|
-- Ketidakleluasaan untuk tabel `penduduk_miskin`
|
|
--
|
|
ALTER TABLE `penduduk_miskin`
|
|
ADD CONSTRAINT `penduduk_miskin_ibfk_1` FOREIGN KEY (`rumah_ibadah_id`) REFERENCES `rumah_ibadah` (`id`) ON DELETE SET NULL;
|
|
|
|
--
|
|
-- Ketidakleluasaan untuk tabel `riwayat_bantuan`
|
|
--
|
|
ALTER TABLE `riwayat_bantuan`
|
|
ADD CONSTRAINT `riwayat_bantuan_ibfk_1` FOREIGN KEY (`penduduk_id`) REFERENCES `penduduk_miskin` (`id`) ON DELETE CASCADE,
|
|
ADD CONSTRAINT `riwayat_bantuan_ibfk_2` FOREIGN KEY (`rumah_ibadah_id`) REFERENCES `rumah_ibadah` (`id`) ON DELETE SET NULL;
|
|
COMMIT;
|
|
|
|
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
|
|
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
|
|
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
|