HSM

-- phpMyAdmin SQL Dump
-- version 4.8.2
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Apr 12, 2019 at 03:55 PM
-- Server version: 10.1.34-MariaDB
-- PHP Version: 7.2.7

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
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: `hsm`
--

-- --------------------------------------------------------

--
-- Table structure for table `accounts`
--

CREATE TABLE `accounts` (
  `oauth_provider` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `oauth_uid` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `first_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `gender` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `locale` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `picture` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `link` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `accounts`
--

INSERT INTO `accounts` (`oauth_provider`, `oauth_uid`, `first_name`, `last_name`, `email`, `gender`, `locale`, `picture`, `link`, `type`, `created`, `modified`) VALUES
('google', '106407918499387818629', 'Best from', 'the World', 'realshivamsingh@gmail.com', '', 'en', 'https://lh6.googleusercontent.com/-_hnR4mDf5oc/AAAAAAAAAAI/AAAAAAAAAC4/v5lqHDDf1EY/photo.jpg', 'https://plus.google.com/106407918499387818629', 'housekeeping', '2019-04-04 22:28:17', '2019-04-04 22:28:18'),
('google', '116518375750345983552', 'Shivam', 'Singh1', 'shivam.singh1@avantika.edu.in', '', 'en', 'https://lh3.googleusercontent.com/-CBudWRtNmag/AAAAAAAAAAI/AAAAAAAAAAA/ACHi3rfgoR0SzJFvCi2qzMgGUS9gF7yOmw/mo/photo.jpg', '', 'student', '2019-04-04 21:52:28', '2019-04-09 16:13:04'),
('google', '107745595479613697458', 'Shivam', 'Singh', 'shivam.singh78925@gmail.com', '', 'en', 'https://lh5.googleusercontent.com/-a1a7VfOAjro/AAAAAAAAAAI/AAAAAAAAAAc/3EkdxlqiZJE/photo.jpg', 'https://plus.google.com/107745595479613697458', 'admin', '2019-04-04 22:27:15', '2019-04-04 22:27:17'),
('google', '117610743481368119414', 'shivam', 'singh', 'shivam789singh@gmail.com', 'male', 'en', 'https://lh6.googleusercontent.com/-ElqoRfeUEWo/AAAAAAAAAAI/AAAAAAAAAJI/WRWGVlvI4n4/photo.jpg', 'https://plus.google.com/117610743481368119414', 'security', '2019-04-04 21:53:42', '2019-04-06 18:17:29');

-- --------------------------------------------------------

--
-- Table structure for table `attendance`
--

CREATE TABLE `attendance` (
  `id` int(11) NOT NULL,
  `date` date NOT NULL,
  `time_in` time NOT NULL,
  `time_out` time NOT NULL,
  `employee_id` varchar(20) NOT NULL,
  `category` varchar(100) NOT NULL,
  `remarks` text NOT NULL,
  `entry_by` varchar(100) NOT NULL,
  `entry_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `hsm_service_request`
--

CREATE TABLE `hsm_service_request` (
  `request_id` int(255) NOT NULL,
  `place` varchar(150) NOT NULL,
  `room` varchar(50) NOT NULL,
  `category` varchar(150) NOT NULL,
  `user_mail` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `remarks` varchar(150) NOT NULL,
  `incharge_employee_id` varchar(20) NOT NULL,
  `requested_date` date NOT NULL,
  `requested_time` time NOT NULL,
  `otp` varchar(10) NOT NULL,
  `status` varchar(25) NOT NULL,
  `rating` varchar(5) NOT NULL,
  `verified` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `hsm_service_request`
--

INSERT INTO `hsm_service_request` (`request_id`, `place`, `room`, `category`, `user_mail`, `remarks`, `incharge_employee_id`, `requested_date`, `requested_time`, `otp`, `status`, `rating`, `verified`) VALUES
(1, 'Aavaas 02', '307', 'Housekeeping', 'shivam.singh1@avantika.edu.in', '', 'AU003', '2019-04-06', '21:28:23', '131227', 'Pending', '', NULL),
(2, 'Aavaas 02', '307', 'Housekeeping', 'shivam.singh1@avantika.edu.in', '', 'AU002', '2019-04-06', '22:32:48', '565829', 'Pending', '', NULL),
(3, 'Aavaas 02', '307', 'Housekeeping', 'shivam.singh1@avantika.edu.in', '', 'AU003', '2019-04-07', '15:55:39', '895350', 'Completed', '', NULL),
(4, 'Aavaas 02', '307', 'Housekeeping', 'shivam.singh1@avantika.edu.in', 'sample', 'AU003', '2019-04-08', '17:33:00', '489742', 'Completed', '', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `outing`
--

CREATE TABLE `outing` (
  `date_out` date NOT NULL,
  `time_out` time NOT NULL,
  `date_in` date NOT NULL,
  `time_in` time NOT NULL,
  `purpose` varchar(200) NOT NULL,
  `remarks` text NOT NULL,
  `mail` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `entry_by_employee_id` varchar(20) NOT NULL,
  `status` varchar(100) NOT NULL,
  `last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `places`
--

CREATE TABLE `places` (
  `place` varchar(150) NOT NULL,
  `remarks` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `places`
--

INSERT INTO `places` (`place`, `remarks`) VALUES
('Aavaas 01', NULL),
('Aavaas 02', NULL),
('Sandipani', NULL),
('Yuti', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `SEC_grooming`
--

CREATE TABLE `SEC_grooming` (
  `id` int(255) NOT NULL,
  `date` date NOT NULL,
  `time` time NOT NULL,
  `employee_id` varchar(20) NOT NULL,
  `active` varchar(10) NOT NULL,
  `shaving` varchar(10) NOT NULL,
  `cutting` varchar(10) NOT NULL,
  `nails` varchar(10) NOT NULL,
  `uniform` varchar(10) NOT NULL,
  `remarks` varchar(255) NOT NULL,
  `entry_by` varchar(20) NOT NULL,
  `entry` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `SEC_occurance`
--

CREATE TABLE `SEC_occurance` (
  `id` int(255) NOT NULL,
  `date` date NOT NULL,
  `time` time NOT NULL,
  `student_involved` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `staff_involved` varchar(20) DEFAULT NULL,
  `category` varchar(255) NOT NULL,
  `reported_by` varchar(20) NOT NULL,
  `remarks` varchar(255) NOT NULL,
  `entry` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `SEC_register_general_entry`
--

CREATE TABLE `SEC_register_general_entry` (
  `id` int(255) NOT NULL,
  `vehicle_number` varchar(30) NOT NULL,
  `visitor_name` varchar(50) NOT NULL,
  `visitor_phone` varchar(20) NOT NULL,
  `place` varchar(100) NOT NULL,
  `purpose` varchar(150) NOT NULL,
  `date_in` date NOT NULL,
  `time_in` time NOT NULL,
  `date_out` date DEFAULT NULL,
  `time_out` time DEFAULT NULL,
  `status` varchar(50) NOT NULL,
  `remarks` text,
  `entry_by_employee_id` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `SEC_register_general_entry`
--

INSERT INTO `SEC_register_general_entry` (`id`, `vehicle_number`, `visitor_name`, `visitor_phone`, `place`, `purpose`, `date_in`, `time_in`, `date_out`, `time_out`, `status`, `remarks`, `entry_by_employee_id`) VALUES
(7, '1234', 'Shivam Singh', '12345', 'qwerty', 'qwerty', '2019-04-06', '12:00:00', '2019-04-06', '12:30:00', 'Checked in', '', 'AU001'),
(9, '1234', 'Hatim Nomani', '7000774848', 'Dahod', 'Guest', '2019-04-10', '12:00:00', '0000-00-00', '00:00:00', 'Checked in', '', 'AU001');

-- --------------------------------------------------------

--
-- Table structure for table `shift_duty`
--

CREATE TABLE `shift_duty` (
  `date` date NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `duty_place` varchar(200) NOT NULL,
  `employee_id` varchar(20) NOT NULL,
  `assigned_by` varchar(100) NOT NULL,
  `remarks` text NOT NULL,
  `assigned` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `shift_duty`
--

INSERT INTO `shift_duty` (`date`, `start_time`, `end_time`, `duty_place`, `employee_id`, `assigned_by`, `remarks`, `assigned`) VALUES
('2019-04-06', '19:00:00', '23:00:00', 'Aavaas 02', 'AU002', 'realshivamsingh@gmail.com', '', '2019-04-06 17:01:14'),
('2019-04-06', '20:00:00', '23:00:00', 'Aavaas 01', 'AU005', 'realshivamsingh@gmail.com', '', '2019-04-06 17:01:40'),
('2019-04-07', '14:00:00', '17:00:00', 'Aavaas 02', 'AU003', 'realshivamsingh@gmail.com', '', '2019-04-07 10:25:15'),
('2019-04-08', '17:00:00', '20:00:00', 'Aavaas 02', 'AU003', 'realshivamsingh@gmail.com', '', '2019-04-08 11:59:33'),
('2019-04-08', '17:00:00', '20:00:00', 'Aavaas 01', 'AU005', 'realshivamsingh@gmail.com', '', '2019-04-08 11:59:56');

-- --------------------------------------------------------

--
-- Table structure for table `staffs`
--

CREATE TABLE `staffs` (
  `employee_id` varchar(20) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `dob` date NOT NULL,
  `identity_type` varchar(100) NOT NULL,
  `identity_number` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `contact` varchar(12) NOT NULL,
  `designation` varchar(50) NOT NULL,
  `department` varchar(50) NOT NULL,
  `address_line1` varchar(200) NOT NULL,
  `address_line2` varchar(200) NOT NULL,
  `locality` varchar(100) NOT NULL,
  `pincode` int(10) NOT NULL,
  `city` text NOT NULL,
  `state` varchar(50) NOT NULL,
  `status` varchar(20) NOT NULL,
  `registered` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `staffs`
--

INSERT INTO `staffs` (`employee_id`, `first_name`, `last_name`, `gender`, `dob`, `identity_type`, `identity_number`, `email`, `contact`, `designation`, `department`, `address_line1`, `address_line2`, `locality`, `pincode`, `city`, `state`, `status`, `registered`) VALUES
('AU001', 'First Name', 'Last Name', 'male', '1990-04-09', 'Aadhaar Card', '123456789', 'realshivamsingh@gmail.com', '8962582946', 'Housekeeper - Male', 'housekeeping', 'a', 'b', 'c', 496001, 'qwerty', 'qwerty', 'enabled', '2019-04-04 20:53:46'),
('AU002', 'ABCD', 'EFGH', 'male', '2019-04-02', 'pan', 'abcdefg', 'email@email.com', '12345', 'Housekeeper - Male', 'housekeeping', 'a', 'b', 'c', 123456, 'abcd', 'abcd', 'enabled', '2019-04-04 21:56:15'),
('AU003', 'HK Staff 3', 'EFGH', 'male', '2019-04-02', 'pan', 'abcdefg', 'email3@email.com', '122345', 'Housekeeper - Male', 'housekeeping', 'a', 'b', 'c', 123456, 'abcd', 'abcd', 'enabled', '2019-04-04 21:57:32'),
('AU004', 'HK Staff 4', 'EFGH', 'male', '2019-04-02', 'pan', 'abcdefg', 'email4@email.com', '12232345', 'Housekeeper - Male', 'housekeeping', 'a', 'b', 'c', 1234356, 'abcd', 'abcd', 'enabled', '2019-04-04 21:58:02'),
('AU005', 'HK Staff 5', 'EFGH', 'male', '2019-04-02', 'pan', 'abcdefg', 'email5@email.com', '1223234555', 'Housekeeper - Male', 'housekeeping', 'a', 'b', 'c', 1234356, 'abcd', 'abcd', 'enabled', '2019-04-04 21:58:28');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `first_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `gender` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `picture` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `au_id` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `type` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `hostel` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `room` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `contact` varchar(12) COLLATE utf8_unicode_ci NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `status` varchar(20) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`first_name`, `last_name`, `email`, `gender`, `picture`, `au_id`, `type`, `hostel`, `room`, `contact`, `created`, `modified`, `status`) VALUES
('Shivam', 'Singh', 'shivam.singh1@avantika.edu.in', 'male', NULL, 'AU17B1021', 'student', 'Aavaas 02', '307', '7000774848', '2019-04-05 11:29:31', '2019-04-05 11:29:31', 'enabled');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `accounts`
--
ALTER TABLE `accounts`
  ADD PRIMARY KEY (`email`);

--
-- Indexes for table `attendance`
--
ALTER TABLE `attendance`
  ADD PRIMARY KEY (`id`),
  ADD KEY `employee_id` (`employee_id`),
  ADD KEY `entry_by` (`entry_by`);

--
-- Indexes for table `hsm_service_request`
--
ALTER TABLE `hsm_service_request`
  ADD PRIMARY KEY (`request_id`),
  ADD KEY `incharge_employee_id` (`incharge_employee_id`),
  ADD KEY `user_mail` (`user_mail`);

--
-- Indexes for table `outing`
--
ALTER TABLE `outing`
  ADD KEY `mail` (`mail`),
  ADD KEY `entry_by_employee_id` (`entry_by_employee_id`);

--
-- Indexes for table `places`
--
ALTER TABLE `places`
  ADD PRIMARY KEY (`place`);

--
-- Indexes for table `SEC_grooming`
--
ALTER TABLE `SEC_grooming`
  ADD PRIMARY KEY (`id`),
  ADD KEY `employee_id` (`employee_id`),
  ADD KEY `entry_by` (`entry_by`);

--
-- Indexes for table `SEC_occurance`
--
ALTER TABLE `SEC_occurance`
  ADD PRIMARY KEY (`id`),
  ADD KEY `reported_by` (`reported_by`),
  ADD KEY `staff_involved` (`staff_involved`),
  ADD KEY `student_involved` (`student_involved`);

--
-- Indexes for table `SEC_register_general_entry`
--
ALTER TABLE `SEC_register_general_entry`
  ADD PRIMARY KEY (`id`),
  ADD KEY `entry_by_employee_id` (`entry_by_employee_id`);

--
-- Indexes for table `shift_duty`
--
ALTER TABLE `shift_duty`
  ADD KEY `employee_id` (`employee_id`),
  ADD KEY `assigned_by` (`assigned_by`);

--
-- Indexes for table `staffs`
--
ALTER TABLE `staffs`
  ADD PRIMARY KEY (`employee_id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD UNIQUE KEY `contact` (`contact`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`email`),
  ADD UNIQUE KEY `au_id` (`au_id`),
  ADD UNIQUE KEY `contact` (`contact`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `attendance`
--
ALTER TABLE `attendance`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `hsm_service_request`
--
ALTER TABLE `hsm_service_request`
  MODIFY `request_id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `SEC_grooming`
--
ALTER TABLE `SEC_grooming`
  MODIFY `id` int(255) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `SEC_occurance`
--
ALTER TABLE `SEC_occurance`
  MODIFY `id` int(255) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `SEC_register_general_entry`
--
ALTER TABLE `SEC_register_general_entry`
  MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `attendance`
--
ALTER TABLE `attendance`
  ADD CONSTRAINT `attendance_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `staffs` (`employee_id`),
  ADD CONSTRAINT `attendance_ibfk_2` FOREIGN KEY (`entry_by`) REFERENCES `staffs` (`email`);

--
-- Constraints for table `hsm_service_request`
--
ALTER TABLE `hsm_service_request`
  ADD CONSTRAINT `hsm_service_request_ibfk_1` FOREIGN KEY (`incharge_employee_id`) REFERENCES `staffs` (`employee_id`),
  ADD CONSTRAINT `hsm_service_request_ibfk_2` FOREIGN KEY (`user_mail`) REFERENCES `users` (`email`);

--
-- Constraints for table `outing`
--
ALTER TABLE `outing`
  ADD CONSTRAINT `outing_ibfk_1` FOREIGN KEY (`mail`) REFERENCES `users` (`email`),
  ADD CONSTRAINT `outing_ibfk_2` FOREIGN KEY (`entry_by_employee_id`) REFERENCES `staffs` (`employee_id`);

--
-- Constraints for table `SEC_grooming`
--
ALTER TABLE `SEC_grooming`
  ADD CONSTRAINT `SEC_grooming_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `staffs` (`employee_id`),
  ADD CONSTRAINT `SEC_grooming_ibfk_2` FOREIGN KEY (`entry_by`) REFERENCES `staffs` (`employee_id`);

--
-- Constraints for table `SEC_occurance`
--
ALTER TABLE `SEC_occurance`
  ADD CONSTRAINT `SEC_occurance_ibfk_1` FOREIGN KEY (`reported_by`) REFERENCES `staffs` (`employee_id`),
  ADD CONSTRAINT `SEC_occurance_ibfk_2` FOREIGN KEY (`staff_involved`) REFERENCES `staffs` (`employee_id`),
  ADD CONSTRAINT `SEC_occurance_ibfk_3` FOREIGN KEY (`student_involved`) REFERENCES `users` (`email`);

--
-- Constraints for table `SEC_register_general_entry`
--
ALTER TABLE `SEC_register_general_entry`
  ADD CONSTRAINT `SEC_register_general_entry_ibfk_1` FOREIGN KEY (`entry_by_employee_id`) REFERENCES `staffs` (`employee_id`);

--
-- Constraints for table `shift_duty`
--
ALTER TABLE `shift_duty`
  ADD CONSTRAINT `shift_duty_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `staffs` (`employee_id`),
  ADD CONSTRAINT `shift_duty_ibfk_2` FOREIGN KEY (`assigned_by`) REFERENCES `staffs` (`email`);

--
-- Constraints for table `users`
--
ALTER TABLE `users`
  ADD CONSTRAINT `users_ibfk_1` FOREIGN KEY (`email`) REFERENCES `accounts` (`email`);
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 */;