ocabacaa
123 lines
2.5 KiB
SQL
123 lines
2.5 KiB
SQL
CREATE DATABASE IF NOT EXISTS webgis_poverty
|
|
CHARACTER SET utf8mb4
|
|
COLLATE utf8mb4_0900_ai_ci;
|
|
|
|
USE webgis_poverty;
|
|
|
|
DROP TABLE IF EXISTS bantuan;
|
|
DROP TABLE IF EXISTS cs_rumah_ibadah;
|
|
DROP TABLE IF EXISTS laporan;
|
|
DROP TABLE IF EXISTS rumah_ibadah;
|
|
DROP TABLE IF EXISTS users;
|
|
|
|
CREATE TABLE users (
|
|
id INT NOT NULL AUTO_INCREMENT,
|
|
nama VARCHAR(100),
|
|
username VARCHAR(50),
|
|
password VARCHAR(255),
|
|
role ENUM('admin','warga','cs'),
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE laporan (
|
|
id INT NOT NULL AUTO_INCREMENT,
|
|
pelapor_id INT,
|
|
nama_warga VARCHAR(100),
|
|
alamat TEXT,
|
|
latitude DOUBLE,
|
|
longitude DOUBLE,
|
|
kondisi TEXT,
|
|
foto VARCHAR(255),
|
|
status ENUM(
|
|
'pending',
|
|
'disetujui',
|
|
'ditolak',
|
|
'diproses',
|
|
'selesai'
|
|
) DEFAULT 'pending',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE rumah_ibadah (
|
|
id INT NOT NULL AUTO_INCREMENT,
|
|
nama VARCHAR(100),
|
|
alamat TEXT,
|
|
latitude DOUBLE,
|
|
longitude DOUBLE,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE cs_rumah_ibadah (
|
|
id INT NOT NULL AUTO_INCREMENT,
|
|
nama VARCHAR(100),
|
|
rumah_ibadah_id INT,
|
|
no_hp VARCHAR(20),
|
|
user_id INT,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE bantuan (
|
|
id INT NOT NULL AUTO_INCREMENT,
|
|
laporan_id INT,
|
|
cs_id INT,
|
|
jenis_bantuan VARCHAR(100),
|
|
status ENUM(
|
|
'belum',
|
|
'proses',
|
|
'selesai'
|
|
) DEFAULT 'belum',
|
|
bukti_foto VARCHAR(255),
|
|
tanggal TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
rumah_ibadah_id INT,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
INSERT INTO users
|
|
(nama, username, password, role)
|
|
VALUES
|
|
('Administrator','admin','0192023a7bbd73250516f069df18b500','admin'),
|
|
('Andi Saputra','andi','e10adc3949ba59abbe56e057f20f883e','warga'),
|
|
('Siti Nurhaliza','siti','e10adc3949ba59abbe56e057f20f883e','warga'),
|
|
('Rahmat Hidayat','rahmat','e10adc3949ba59abbe56e057f20f883e','warga'),
|
|
('Budi Santoso','cs01','8551e0027ff3a8de9662eb3b8a16c23e','cs'),
|
|
('Santoso','cs02','8551e0027ff3a8de9662eb3b8a16c23e','cs');
|
|
|
|
INSERT INTO rumah_ibadah
|
|
(nama, alamat, latitude, longitude)
|
|
VALUES
|
|
(
|
|
'Gereja Katolik Paroki Bunda Maria',
|
|
'Pontianak Barat, Kalimantan Barat',
|
|
-0.005640,
|
|
109.295179
|
|
),
|
|
(
|
|
'Masjid Nurul Hamid',
|
|
'Pontianak Barat, Kalimantan Barat',
|
|
-0.008226,
|
|
109.296014
|
|
),
|
|
(
|
|
'Masjid Jannatun Naim',
|
|
'Kendari, Sulawesi Tenggara',
|
|
-3.997242,
|
|
122.521983
|
|
),
|
|
(
|
|
'GPIB Siloam Pontianak',
|
|
'Pontianak Kota, Kalimantan Barat',
|
|
-0.025091,
|
|
109.337375
|
|
);
|
|
|
|
INSERT INTO cs_rumah_ibadah
|
|
(nama, rumah_ibadah_id, no_hp, user_id)
|
|
VALUES
|
|
(
|
|
'Santoso',
|
|
1,
|
|
'081234567890',
|
|
6
|
|
);
|