Danial HB
207 lines
10 KiB
SQL
207 lines
10 KiB
SQL
-- MySQL dump 10.13 Distrib 9.5.0, for macos15 (x86_64)
|
|
--
|
|
-- Host: localhost Database: webgis_donations
|
|
-- ------------------------------------------------------
|
|
-- Server version 9.5.0
|
|
|
|
/*!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 */;
|
|
/*!50503 SET NAMES utf8mb4 */;
|
|
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
|
|
/*!40103 SET TIME_ZONE='+00:00' */;
|
|
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
|
|
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
|
|
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
|
|
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
|
|
|
|
--
|
|
-- Current Database: `webgis_donations`
|
|
--
|
|
|
|
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `webgis_donations` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
|
|
|
|
USE `webgis_donations`;
|
|
|
|
--
|
|
-- Table structure for table `distribution_history`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `distribution_history`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!50503 SET character_set_client = utf8mb4 */;
|
|
CREATE TABLE `distribution_history` (
|
|
`id` int NOT NULL AUTO_INCREMENT,
|
|
`house_id` int NOT NULL,
|
|
`place_id` int DEFAULT NULL,
|
|
`distributed_by` int DEFAULT NULL,
|
|
`aid_category` varchar(100) NOT NULL,
|
|
`amount_value` decimal(15,2) NOT NULL,
|
|
`notes` text,
|
|
`verification_status` enum('pending','verified','rejected') NOT NULL DEFAULT 'pending',
|
|
`verified_by` int DEFAULT NULL,
|
|
`verified_at` datetime DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `house_id` (`house_id`),
|
|
KEY `place_id` (`place_id`),
|
|
KEY `distributed_by` (`distributed_by`),
|
|
KEY `verified_by` (`verified_by`),
|
|
CONSTRAINT `distribution_history_ibfk_1` FOREIGN KEY (`house_id`) REFERENCES `households` (`id`) ON DELETE CASCADE,
|
|
CONSTRAINT `distribution_history_ibfk_2` FOREIGN KEY (`place_id`) REFERENCES `religious_places` (`id`) ON DELETE SET NULL,
|
|
CONSTRAINT `distribution_history_ibfk_3` FOREIGN KEY (`distributed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
|
|
CONSTRAINT `distribution_history_ibfk_4` FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
|
|
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Dumping data for table `distribution_history`
|
|
--
|
|
|
|
LOCK TABLES `distribution_history` WRITE;
|
|
/*!40000 ALTER TABLE `distribution_history` DISABLE KEYS */;
|
|
INSERT INTO `distribution_history` VALUES (1,9,4,1,'sembako',1.00,'Diberikan sembako 10kg berupa bahan pangan','verified',1,'2026-06-11 23:59:05'),(2,11,2,1,'sembako',1.00,'Diberikan sembako berupa PC Gaming','pending',NULL,NULL);
|
|
/*!40000 ALTER TABLE `distribution_history` ENABLE KEYS */;
|
|
UNLOCK TABLES;
|
|
|
|
--
|
|
-- Table structure for table `households`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `households`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!50503 SET character_set_client = utf8mb4 */;
|
|
CREATE TABLE `households` (
|
|
`id` int NOT NULL AUTO_INCREMENT,
|
|
`kk_number` varchar(16) NOT NULL,
|
|
`head_nik` varchar(16) NOT NULL,
|
|
`head_name` varchar(255) NOT NULL,
|
|
`resident_count` int NOT NULL,
|
|
`lat` decimal(10,8) NOT NULL,
|
|
`lng` decimal(11,8) NOT NULL,
|
|
`linked_place_id` int DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `linked_place_id` (`linked_place_id`),
|
|
CONSTRAINT `households_ibfk_1` FOREIGN KEY (`linked_place_id`) REFERENCES `religious_places` (`id`) ON DELETE SET NULL
|
|
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Dumping data for table `households`
|
|
--
|
|
|
|
LOCK TABLES `households` WRITE;
|
|
/*!40000 ALTER TABLE `households` DISABLE KEYS */;
|
|
INSERT INTO `households` VALUES (6,'6171050505670005','6171052511790005','Hendra Kurniawan',6,-0.02563119,109.33623791,3),(7,'6171020202340002','6171021508820002','Saputra Wijaya',5,-0.02630174,109.33527231,3),(8,'6171010101230001','6171011203840001','Budi Santoso',4,-0.02426863,109.33210194,4),(9,'6171030303450003','6171030912850003','Syahputra Ramadhan',6,-0.02335668,109.33048725,4),(10,'6171040404560004','6171042104900004','Dian Lestari',2,-0.02706885,109.33138311,NULL),(11,'6171098765432','6171234567890','Darin',6,-0.04112363,109.34434891,2),(12,'617193843984394','617123829734828','Bagas',4,-0.04228234,109.33483243,1),(13,'61712387293847','61713423423478','Dimas',3,-0.04366636,109.33228970,NULL);
|
|
/*!40000 ALTER TABLE `households` ENABLE KEYS */;
|
|
UNLOCK TABLES;
|
|
|
|
--
|
|
-- Table structure for table `poverty_assessments`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `poverty_assessments`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!50503 SET character_set_client = utf8mb4 */;
|
|
CREATE TABLE `poverty_assessments` (
|
|
`id` int NOT NULL AUTO_INCREMENT,
|
|
`household_id` int NOT NULL,
|
|
`surveyor_id` int DEFAULT NULL,
|
|
`electricity_va` varchar(50) NOT NULL,
|
|
`floor_material` varchar(100) NOT NULL,
|
|
`wall_material` varchar(100) NOT NULL,
|
|
`drinking_water_source` varchar(100) NOT NULL,
|
|
`photo_evidence_url` varchar(255) DEFAULT NULL,
|
|
`verification_status` enum('pending','verified','rejected') NOT NULL DEFAULT 'pending',
|
|
`verified_by` int DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `household_id` (`household_id`),
|
|
KEY `surveyor_id` (`surveyor_id`),
|
|
KEY `verified_by` (`verified_by`),
|
|
CONSTRAINT `poverty_assessments_ibfk_1` FOREIGN KEY (`household_id`) REFERENCES `households` (`id`) ON DELETE CASCADE,
|
|
CONSTRAINT `poverty_assessments_ibfk_2` FOREIGN KEY (`surveyor_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
|
|
CONSTRAINT `poverty_assessments_ibfk_3` FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
|
|
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Dumping data for table `poverty_assessments`
|
|
--
|
|
|
|
LOCK TABLES `poverty_assessments` WRITE;
|
|
/*!40000 ALTER TABLE `poverty_assessments` DISABLE KEYS */;
|
|
INSERT INTO `poverty_assessments` VALUES (6,6,3,'450','cement','wood','well',NULL,'verified',1),(7,7,3,'450','cement','brick','pdam',NULL,'verified',1),(8,8,4,'450','wood','wood','rainwater',NULL,'verified',1),(9,9,4,'900','ceramic','brick','pdam',NULL,'verified',1),(10,10,4,'450','cement','brick','bottled',NULL,'verified',1),(11,11,6,'1300','ceramic','brick','pdam',NULL,'verified',1),(12,12,5,'900','dirt','bamboo','river',NULL,'pending',NULL),(13,13,5,'450','cement','brick','rainwater',NULL,'pending',NULL);
|
|
/*!40000 ALTER TABLE `poverty_assessments` ENABLE KEYS */;
|
|
UNLOCK TABLES;
|
|
|
|
--
|
|
-- Table structure for table `religious_places`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `religious_places`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!50503 SET character_set_client = utf8mb4 */;
|
|
CREATE TABLE `religious_places` (
|
|
`id` int NOT NULL AUTO_INCREMENT,
|
|
`name` varchar(255) NOT NULL,
|
|
`lat` decimal(10,8) NOT NULL,
|
|
`lng` decimal(11,8) NOT NULL,
|
|
`radius` int NOT NULL DEFAULT '500',
|
|
`place_type` enum('mosque','church_catholic','church_protestant','pura','vihara','kelenteng') NOT NULL DEFAULT 'mosque',
|
|
`added_by` int DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `added_by` (`added_by`),
|
|
CONSTRAINT `religious_places_ibfk_1` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
|
|
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Dumping data for table `religious_places`
|
|
--
|
|
|
|
LOCK TABLES `religious_places` WRITE;
|
|
/*!40000 ALTER TABLE `religious_places` DISABLE KEYS */;
|
|
INSERT INTO `religious_places` VALUES (1,'Mujahideen Mosque',-0.04149913,109.33635592,500,'mosque',1),(2,'Majid Amanatul Hasanah',-0.04104852,109.34329748,500,'mosque',2),(3,'GPIB Siloam',-0.02507329,109.33727860,500,'church_protestant',3),(4,'Masjid Al-Manar',-0.02383947,109.33471441,500,'mosque',4);
|
|
/*!40000 ALTER TABLE `religious_places` ENABLE KEYS */;
|
|
UNLOCK TABLES;
|
|
|
|
--
|
|
-- Table structure for table `users`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `users`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!50503 SET character_set_client = utf8mb4 */;
|
|
CREATE TABLE `users` (
|
|
`id` int NOT NULL AUTO_INCREMENT,
|
|
`username` varchar(50) NOT NULL,
|
|
`email` varchar(100) NOT NULL,
|
|
`password_hash` varchar(255) NOT NULL,
|
|
`role` enum('admin','surveyor') NOT NULL DEFAULT 'surveyor',
|
|
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `username` (`username`),
|
|
UNIQUE KEY `email` (`email`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Dumping data for table `users`
|
|
--
|
|
|
|
LOCK TABLES `users` WRITE;
|
|
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
|
|
INSERT INTO `users` VALUES (1,'admin','admin@webgis.com','$2b$10$wOFuhFBZzEWdnNIt7hxNBeDW.sivkV5Y0jCHQz0k0Ew7eC7T3xJQe','admin','2026-06-11 16:28:22'),(2,'Surveyor1','surveyor1@webgis.com','$2b$10$C6TlOgTG6FQhfq6USToWHen3gRouR3z0tnkaVbWCQlcsd7m/aluM2','surveyor','2026-06-11 16:28:22'),(3,'GPIB Siloam','gpibsiloam@webgis.com','$2b$10$nEUOivMsFlPwvBpbaGCIMO.ciqqllOTRZisPaLJXot3OsXsOsOiUW','surveyor','2026-06-11 16:43:02'),(4,'Masjid Al-Manar','masjidalmanar@webgis.com','$2b$10$QMaZwRx0PsoxXFyOEVKghey.yUUK76ER2hW7gmlD4lGq3GM00uP.6','surveyor','2026-06-11 16:45:16'),(5,'Masjid Mujahideen','mujahideen@webgis.com','$2b$10$KYGptHaDBQLB93/eCNf/t.n.nEosWx4KELb8qiEG2rMG3THPUzKdC','surveyor','2026-06-11 16:53:55'),(6,'Masjid Amanatul Hasanah','amanatulhasanah@webgis.com','$2b$10$BqozVZ6M8NrFBnUuWt/BvekEmd8T1mmv0cBb9NZdZ2z.eTlhxBHZu','surveyor','2026-06-11 16:54:29');
|
|
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
|
|
UNLOCK TABLES;
|
|
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
|
|
|
|
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
|
|
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
|
|
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
|
|
/*!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 */;
|
|
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
|
|
|
|
-- Dump completed on 2026-06-12 0:03:34
|