-- phpMyAdmin SQL Dump -- version 5.2.1 -- https://www.phpmyadmin.net/ -- -- Host: 127.0.0.1 -- Generation Time: Mar 27, 2024 at 09:31 PM -- Server version: 10.4.32-MariaDB -- PHP Version: 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: `church` -- -- -------------------------------------------------------- -- -- Table structure for table `failed_jobs` -- CREATE TABLE `failed_jobs` ( `id` bigint(20) UNSIGNED NOT NULL, `uuid` varchar(255) NOT NULL, `connection` text NOT NULL, `queue` text NOT NULL, `payload` longtext NOT NULL, `exception` longtext NOT NULL, `failed_at` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `migrations` -- CREATE TABLE `migrations` ( `id` int(10) UNSIGNED NOT NULL, `migration` varchar(255) NOT NULL, `batch` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `password_reset_tokens` -- CREATE TABLE `password_reset_tokens` ( `email` varchar(255) NOT NULL, `token` varchar(255) NOT NULL, `created_at` timestamp NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `personal_access_tokens` -- CREATE TABLE `personal_access_tokens` ( `id` bigint(20) UNSIGNED NOT NULL, `tokenable_type` varchar(255) NOT NULL, `tokenable_id` bigint(20) UNSIGNED NOT NULL, `name` varchar(255) NOT NULL, `token` varchar(64) NOT NULL, `abilities` text DEFAULT NULL, `last_used_at` timestamp NULL DEFAULT NULL, `expires_at` timestamp NULL DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `userdetails` -- CREATE TABLE `userdetails` ( `UserDetailsID` int(10) NOT NULL, `UserID` int(10) NOT NULL, `FirstName` varchar(50) NOT NULL, `LastName` varchar(50) DEFAULT NULL, `DateOfBirth` date NOT NULL, `Province` varchar(20) DEFAULT NULL, `Gender` varchar(6) NOT NULL, `Facilitator` bit(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `userdetails` -- INSERT INTO `userdetails` (`UserDetailsID`, `UserID`, `FirstName`, `LastName`, `DateOfBirth`, `Province`, `Gender`, `Facilitator`) VALUES (1, 12, 'Tebogos', 'Sebake', '2014-03-09', 'Limpopo', 'Male', b'1'), (2, 11, 'Thakgalo', 'Sebake', '2023-03-27', 'Limpopo', 'Male', b'0'), (4, 18, 'Bongani', 'Phasha', '2021-09-28', 'Western Cape', 'Female', b'0'); -- -------------------------------------------------------- -- -- Table structure for table `users` -- CREATE TABLE `users` ( `UserID` int(10) NOT NULL, `Password` varchar(200) NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp () ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `users` -- INSERT INTO `users` (`UserID`, `Password`, `created_at`, `updated_at`) VALUES (11, '1', '2024-03-27 05:51:31', '2024-03-27 13:13:57'), (12, '1', '2024-03-27 06:32:49', '2024-03-27 08:38:53'), (18, '1234', '2024-03-27 11:47:09', '2024-03-27 13:47:09'), (19, '1234', '2024-03-27 19:52:49', '2024-03-27 21:52:49'); -- -- Indexes for dumped tables -- -- -- Indexes for table `failed_jobs` -- ALTER TABLE `failed_jobs` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`); -- -- Indexes for table `migrations` -- ALTER TABLE `migrations` ADD PRIMARY KEY (`id`); -- -- Indexes for table `password_reset_tokens` -- ALTER TABLE `password_reset_tokens` ADD PRIMARY KEY (`email`); -- -- Indexes for table `personal_access_tokens` -- ALTER TABLE `personal_access_tokens` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `personal_access_tokens_token_unique` (`token`), ADD KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`,`tokenable_id`); -- -- Indexes for table `userdetails` -- ALTER TABLE `userdetails` ADD PRIMARY KEY (`UserDetailsID`), ADD KEY `UserID` (`UserID`); -- -- Indexes for table `users` -- ALTER TABLE `users` ADD PRIMARY KEY (`UserID`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `failed_jobs` -- ALTER TABLE `failed_jobs` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `migrations` -- ALTER TABLE `migrations` MODIFY `id` int (10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7; -- -- AUTO_INCREMENT for table `personal_access_tokens` -- ALTER TABLE `personal_access_tokens` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `userdetails` -- ALTER TABLE `userdetails` MODIFY `UserDetailsID` int (10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6; -- -- AUTO_INCREMENT for table `users` -- ALTER TABLE `users` MODIFY `UserID` int (10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20; -- -- Constraints for dumped tables -- -- -- Constraints for table `userdetails` -- ALTER TABLE `userdetails` ADD CONSTRAINT `userdetails_ibfk_1` FOREIGN KEY (`UserID`) REFERENCES `users` (`UserID`) ON UPDATE CASCADE; 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 */; -- CREATE TABLE users -- ( -- user_id INT NOT NULL AUTO_INCREMENT, -- password VARCHAR(30) NOT NULL, -- status_id INT NOT NULL, -- PRIMARY KEY (user_id) -- ) ENGINE=InnoDB AUTO_INCREMENT=8458 DEFAULT CHARSET=latin1; CREATE TABLE user_status ( user_status_id int(11) NOT NULL AUTO_INCREMENT, status_desc varchar(20) NOT NULL, PRIMARY KEY (user_status_id) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; INSERT INTO `user_status` (`user_status_id`, `status_des`) VALUES (1, 'Active'), (2, 'Not Active'); CREATE TABLE gender ( gender_id int(11) NOT NULL AUTO_INCREMENT, gender_desc varchar(20) NOT NULL, PRIMARY KEY (gender_id) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; INSERT INTO `gender` (`gender_id`, `gender_desc`) VALUES (1, 'Female'), (2, 'Male'); -- funeral mpoho CONFERENCE CREATE TABLE activity_desc ( activity_desc_id INT NOT NULL AUTO_INCREMENT, activity_desc varchar(20) NOT NULL, PRIMARY KEY (activity_type_id) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; INSERT INTO `activity_desc` (`activity_desc_id`, `activity_desc`) VALUES (1, 'Funeral'), (2, 'Function'), (3, 'Exam Prayer'), (4, 'Mpoho'), (5, 'Easter Conference'), (6, 'September Conference'); CREATE TABLE member_type -- mass, executive ( member_type_id int(11) NOT NULL AUTO_INCREMENT, member_type_desc varchar(100) NOT NULL, PRIMARY KEY (member_type_id) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; INSERT INTO `member_type` (`member_type_id`, `member_type_desc`) VALUES (1, 'Mass'), (2, 'Executive'); CREATE TABLE executive_types -- bec ( exec_type_id int(11) NOT NULL AUTO_INCREMENT, exec_type_desc varchar(20) NOT NULL, PRIMARY KEY (exec_type_id) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; INSERT INTO `member_type` (`exec_type_id`, `exec_type_desc`) VALUES (1, 'BEC (Branch Executive Committee)'), (2, 'Male Executive'), (3, 'Female Executive'), (4, 'Secretary'); -- active rescheduled , cancelled , rescheduled with a date , rescheduled without a date CREATE TABLE activity_status ( activity_status_id int(11) NOT NULL AUTO_INCREMENT, activity_status_desc varchar(20) NOT NULL, PRIMARY KEY (exec_type_id) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; INSERT INTO `activity_status` (`activity_status_id`, `activity_status_desc`) VALUES (1, 'Active'), (2, 'Rescheduled'), (3, 'Reschedule with a date'), (4, 'Reschedule without a date'); CREATE TABLE members ( member_id INT NOT NULL AUTO_INCREMENT, password VARCHAR(30) DEFAULT NULL, title VARCHAR(30) NOT NULL, first_name VARCHAR(30) NOT NULL, second_name VARCHAR(30), last_name VARCHAR(30) NOT NULL, email VARCHAR(30) NOT NULL, cell_phone VARCHAR(15) NOT NULL, exec_type_id INT DEFAULT NULL, status_id INT NOT NULL, member_type_id INT NOT NULL, gender_id INT NOT NULL, last_updated_by timestamp default current_timestamp not null, captured_date timestamp default current_timestamp not null, captured_by VARCHAR(30) NOT NULL, last_updated_by VARCHAR(30) NOT NULL, PRIMARY KEY (member_id), -- FOREIGN KEY (user_id) REFERENCES users (user_id), UNIQUE (email), UNIQUE (cell_phone) ) ENGINE=InnoDB AUTO_INCREMENT=7546 DEFAULT CHARSET=latin1; CREATE TABLE addresses ( address_id INT NOT NULL AUTO_INCREMENT, member_id INT NOT NULL, address_type VARCHAR(30) NOT NULL, -- home / school / address1 VARCHAR(100) NOT NULL, address2 VARCHAR(100), suburb VARCHAR(30) NOT NULL, city VARCHAR(30) NOT NULL, province_id INT NOT NULL, postal_code VARCHAR(30) NOT NULL, PRIMARY KEY (address_id), FOREIGN KEY (member_id) REFERENCES members (member_id) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; CREATE TABLE activities ( activity_id INT NOT NULL AUTO_INCREMENT, activity_type VARCHAR(30) NOT NULL, -- trip / event / activity_origin VARCHAR(30) NOT NULL, -- internal / external activity_desc_id INT NOT NULL, -- funeral / Mpoho activity_dedicated_by INT NOT NULL, -- someone in the DB exec or secretary will search in the db from members activity_coordinator INT NOT NULL, -- someone in the DB exec or secretary will search in the db start_date timestamp not null, revised_start_date timestamp default null, end_date timestamp not null, revised_end_date timestamp default null, transport_mode INT NULL, activity_total_cost VARCHAR(30) NOT NULL, num_of_cars INT NULL, total_num_of_seats INT NULL, seats_occupied INT NULL, activity_comments VARCHAR(350) DEFAULT NULL, females_attended INT NULL, males_attended INT NULL, females_executives_attended INT NULL, male_executives_attended INT NULL, num_of_females_worn_khaki INT NULL, num_of_males_worn_khaki INT NULL, num_of_females_worn_uniform_structure INT NULL, num_of_males_worn_uniform_structure INT NULL, contribution_amounts_from_mass INT NULL, contribution_amounts_from_BEC INT NULL, contribution_amounts_from_females_executives INT NULL, contribution_amounts_from_male_executives INT NULL, activity_status INT NOT NULL DEFAULT 1, -- rescheduled,Cancelled,, last_updated timestamp not null, captured_date timestamp not null, captured_by VARCHAR(30) not null, PRIMARY KEY (activity_id) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1; CREATE TABLE meetings ( meeting_id INT NOT NULL AUTO_INCREMENT, meeting_type VARCHAR(30) NOT NULL, meeting_agenda VARCHAR(1000) NOT NULL, meeting_recommendation VARCHAR(350), meeting_conductor VARCHAR(30) NOT NULL, -- someone in the DB exec or secretary will search in the db from members start_date timestamp not null, end_date timestamp not null, meeting_status INT DEFAULT 1, -- active last_updated timestamp not null, captured_date timestamp not null, captured_by INT not NULL, PRIMARY KEY (meeting_id) -- FOREIGN KEY (attendees_id) REFERENCES attendees (attendees_id) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; CREATE TABLE attendees ( attendees_id INT NOT NULL AUTO_INCREMENT, member_id INT NOT NULL, activity_id INT NULL, meeting_id INT NULL, apology VARCHAR(30), -- YES / NO last_updated timestamp not null, captured_date timestamp not null, captured_by int not null, PRIMARY KEY (attendees_id), FOREIGN KEY (member_id) REFERENCES members (member_id), FOREIGN KEY (activity_id) REFERENCES activities (activity_id), FOREIGN KEY (meeting_id) REFERENCES meetings (meeting_id) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; CREATE TABLE documents ( document_id INT NOT NULL AUTO_INCREMENT, document_name VARCHAR(30) NOT NULL, activity_id INT NULL, meeting_id INT NULL, normal_docs VARCHAR(5) NOT NULL, -- sermons or minutes from meeting document_title VARCHAR(30) NOT NULL, captured_by VARCHAR(30) NOT NULL, captured_date timestamp default current_timestamp not null, PRIMARY KEY (document_id), FOREIGN KEY (activity_id) REFERENCES activities (activity_id), FOREIGN KEY (meeting_id) REFERENCES meetings (meeting_id) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1; CREATE TABLE location ( location_id INT NOT NULL AUTO_INCREMENT, activity_id INT Default Null, meeting_id INT Default Null, province_id INT NOT NULL, address1 VARCHAR(100) NOT NULL, address2 VARCHAR(100), city VARCHAR(100) NOT NULL, suburb VARCHAR(30) NOT NULL, postal_code VARCHAR(30) NOT NULL, PRIMARY KEY (location_id), FOREIGN KEY (activity_id) REFERENCES activities (activity_id), FOREIGN KEY (meeting_id) REFERENCES meetings (meeting_id), ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; CREATE TABLE months ( month_id int(11) NOT NULL AUTO_INCREMENT, month_name varchar(20) NOT NULL, PRIMARY KEY (month_id) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;