-- http://www.phpmyadmin.net
--




--
-- Database: `sciencelabinv`
--

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

--
-- Table structure for table `items`
--

CREATE TABLE IF NOT EXISTS `items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key of the table',
  `name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `type_id` int(10) unsigned NOT NULL COMMENT 'shows the type of the item, it is a foreign key to "types" table',
  `room_id` char(5) COLLATE utf8_unicode_ci NOT NULL,
  `store_id` char(10) COLLATE utf8_unicode_ci NOT NULL,
  `comments` text COLLATE utf8_unicode_ci COMMENT 'any comments or observation can by entered either when the item first catalogued or at any time when it is in the store',
  `unit` char(5) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'the unit of measurement of the item i.e. gram, kilo, box, .. etc',
  `quantity` double NOT NULL DEFAULT '1' COMMENT 'the quantity of the item in the store',
  `consumption` double DEFAULT '0' COMMENT 'how much got consumed of the item',
  `vendor` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'vendor''s information',
  `catno` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'catalogue''s no in the vendors catalogue',
  `arrived` date DEFAULT NULL COMMENT 'when did the item arrived',
  `expires` date DEFAULT NULL COMMENT 'the expiry date of the item',
  `image` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `item_type_id` (`type_id`),
  KEY `unit` (`unit`),
  KEY `building` (`room_id`),
  KEY `store_id` (`store_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='holds information about stored items, it''s the main table.' AUTO_INCREMENT=5 ;

--
-- Dumping data for table `items`
--

INSERT INTO `items` (`id`, `name`, `type_id`, `room_id`, `store_id`, `comments`, `unit`, `quantity`, `consumption`, `vendor`, `catno`, `arrived`, `expires`, `image`) VALUES
(1, 'ethanol', 1, 'SR1', 'SR1FC1', '96.2 vol\r\nModern Distillery, Khartoum North', 'ml', 36000, 3000, 'local market Modern Distillery', 'NULL', NULL, NULL, NULL),
(2, 'Hydroxylammonium chloride', 5, 'SR1', 'SR1SH', 'Hydroxylamine Hydrochloride\r\nHarmful\r\nNH2OH.HCl', 'gram', 100, 0, 'Local - Seisaban', NULL, '2009-10-14', NULL, NULL),
(3, 'Beaker - 250ml - pyrex', 19, 'PR1', 'PR1', NULL, 'unit', 10, 0, NULL, NULL, NULL, NULL, NULL),
(4, 'Author', 22, 'SCOF', 'off1', '<p>\r\n	my photo</p>', 'unit', 1, 0, NULL, NULL, '2010-06-21', '2010-06-20', 'avator.jpg');

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

--
-- Stand-in structure for view `items_view`
--
DROP VIEW IF EXISTS `items_view`;
CREATE TABLE IF NOT EXISTS `items_view` (
`id` int(10) unsigned
,`item` varchar(100)
,`type` varchar(45)
,`unit` char(5)
,`quantity` double
,`consumption` double
,`remaining` double
,`vendor` varchar(50)
,`catno` varchar(50)
,`arrived` date
,`expires` date
,`room` varchar(25)
,`store` varchar(25)
,`image` varchar(50)
,`comments` text
);
-- --------------------------------------------------------

--
-- Table structure for table `lesson`
--

CREATE TABLE IF NOT EXISTS `lesson` (
  `id` tinyint(4) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `starts` time DEFAULT NULL,
  `ends` time DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;

--
-- Dumping data for table `lesson`
--

INSERT INTO `lesson` (`id`, `name`, `starts`, `ends`) VALUES
(1, 'Lesson 1', '08:05:00', '09:00:00'),
(2, 'Lesson 2', '09:05:00', '10:00:00'),
(3, 'Lesson 3', '10:40:00', '11:45:00'),
(4, 'Lesson 4', '11:50:00', '12:45:00'),
(5, 'Lesson 5', '00:00:00', '00:00:00'),
(6, 'ECA', '00:00:00', '00:00:00');

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

--
-- Table structure for table `orders`
--

CREATE TABLE IF NOT EXISTS `orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'auto incremented primary key for the table',
  `title` varchar(25) COLLATE utf8_unicode_ci NOT NULL COMMENT 'lesson title, could be the year group',
  `lesson` tinyint(4) unsigned NOT NULL COMMENT 'the number of the lesson 1 to 6',
  `room` char(5) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'the room where the ordred item should be delivered',
  `order_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'autogenerated timestamp when the order created',
  `request_date` date NOT NULL COMMENT 'the day at which the ordered items are neede',
  `return_date` date NOT NULL COMMENT 'the day at which the ordered items will be returned to the system',
  `teacher` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'the name of the teacher who ordered the items for his/her practical',
  `note` text COLLATE utf8_unicode_ci COMMENT 'any special note about the order or the practical',
  `attachment` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`teacher`),
  KEY `room` (`room`),
  KEY `lesson` (`lesson`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='holds information about orders for practicals' AUTO_INCREMENT=2 ;

--
-- Dumping data for table `orders`
--

INSERT INTO `orders` (`id`, `title`, `lesson`, `room`, `order_date`, `request_date`, `return_date`, `teacher`, `note`, `attachment`) VALUES
(1, 'rr', 1, 'SC4', '2010-05-27 00:00:00', '2010-05-27', '2010-05-29', 'technician', NULL, NULL);

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

--
-- Stand-in structure for view `orders_detail_view`
--
DROP VIEW IF EXISTS `orders_detail_view`;
CREATE TABLE IF NOT EXISTS `orders_detail_view` (
`id` int(10) unsigned
,`teacher` varchar(50)
,`lesson` tinyint(4) unsigned
,`room` varchar(25)
,`request_date` date
,`order note` text
,`item` varchar(100)
,`quantity` double
,`unit` char(5)
,`concentration` varchar(25)
,`detail note` text
,`in room` char(5)
,`in store` char(10)
,`return_date` date
,`order_date` timestamp
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `orders_view`
--
DROP VIEW IF EXISTS `orders_view`;
CREATE TABLE IF NOT EXISTS `orders_view` (
`id` int(10) unsigned
,`room` varchar(25)
,`order_date` timestamp
,`request_date` date
,`return_date` date
,`lesson` varchar(10)
,`starts` time
,`ends` time
,`teacher` varchar(50)
,`note` text
);
-- --------------------------------------------------------

--
-- Table structure for table `order_detail`
--

CREATE TABLE IF NOT EXISTS `order_detail` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key of the table',
  `order_id` int(10) unsigned NOT NULL COMMENT 'foreign key to "orders" table shows that this item is ordered for that certain order ',
  `item_id` int(10) unsigned NOT NULL COMMENT 'the item which is orderd',
  `quantity` double NOT NULL COMMENT 'the required quantity of the item',
  `unit` char(5) COLLATE utf8_unicode_ci NOT NULL COMMENT 'measurment of that quantity like gram, kilo, unit ... etc',
  `concentration` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'if it''s something to be prepared, at which concentration',
  `note` text COLLATE utf8_unicode_ci COMMENT 'special note about this special item',
  `room_id` char(5) COLLATE utf8_unicode_ci DEFAULT NULL,
  `store_id` char(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  KEY `item_id` (`item_id`),
  KEY `unit` (`unit`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='displays the ordered items in each order' AUTO_INCREMENT=4 ;

--
-- Dumping data for table `order_detail`
--

INSERT INTO `order_detail` (`id`, `order_id`, `item_id`, `quantity`, `unit`, `concentration`, `note`, `room_id`, `store_id`) VALUES
(1, 1, 3, 12, 'unit', NULL, NULL, 'PR1', 'PR1'),
(2, 1, 3, 12, 'unit', NULL, '<p>\r\n	ggggg</p>', 'PR1', 'PR1'),
(3, 1, 3, 12, 'unit', NULL, '<p>\r\n	ggggg</p>\r\n<p>\r\n	<img alt=":(" src="http://localhost/xampp/mylab1/ckeditor/plugins/smiley/images/sad_smile.gif" title=":(" /></p>\r\n<p>\r\n	^</p>\r\n<p>\r\n	<img alt="" dir="ltr" /></p>', 'PR1', 'PR1');

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

--
-- Table structure for table `rooms`
--

CREATE TABLE IF NOT EXISTS `rooms` (
  `id` char(5) COLLATE utf8_unicode_ci NOT NULL COMMENT 'primary key of the table',
  `name` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'name of the room',
  `description` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'information about where is the room located and how many students can it takes',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='hold information about the rooms in the science department';

--
-- Dumping data for table `rooms`
--

INSERT INTO `rooms` (`id`, `name`, `description`) VALUES
('PR1', 'Preparation Room GROUND', NULL),
('PR2', 'Preparation Room2 ABOVE', NULL),
('SC1', 'Chemistry Room GROUND', 'John''s'),
('SC2', 'Biology Room GROUND', 'Cindy''s'),
('SC3', 'Physics Room ABOVE', 'Andy''s'),
('SC4', 'Biology Room ABOVE', 'John and Yasir'),
('SCOF', 'Science Office', NULL),
('SR1', 'Store Room GROUND', 'Chemical Store'),
('SR2', 'Dark Room ABOVE', NULL);

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

--
-- Table structure for table `stores`
--

CREATE TABLE IF NOT EXISTS `stores` (
  `id` char(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'primary key of the table',
  `name` varchar(25) COLLATE utf8_unicode_ci NOT NULL COMMENT 'the name of the store or the cupboard',
  `lock_no` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'the lock number which matches it''s key for security and safety isues',
  `room_id` char(5) COLLATE utf8_unicode_ci NOT NULL COMMENT 'the building at which the store is located',
  PRIMARY KEY (`id`),
  KEY `room_id` (`room_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='cupboards and special stores inside science buildings';

--
-- Dumping data for table `stores`
--

INSERT INTO `stores` (`id`, `name`, `lock_no`, `room_id`) VALUES
('off1', 'Office', NULL, 'SCOF'),
('PR1FG1', ' Fume Guard', '', 'PR1'),
('PR1FRG', 'fridge', '', 'PR1'),
('PR2A1', 'cupboard A1', '', 'PR2'),
('PR2A2', 'Cupboard A2', NULL, 'PR2'),
('PR2C4', 'Cupboard C4', 'ZL 1390', 'PR2'),
('PR2M1', 'Cupboard M1', NULL, 'PR2'),
('pr2pr2', 'in the room', NULL, 'PR2'),
('SR1', 'Store room', '', 'SR1'),
('SR1A1', 'cupboard A1', '', 'SR1'),
('SR1AC2', 'acid cupboard grey', '', 'SR1'),
('SR1FC1', 'flammable cupboard', '', 'SR1'),
('SR1SH', 'shelves', NULL, 'SR1'),
('SR2RC1', 'Radioactive cupboard', '', 'SR2');

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

--
-- Table structure for table `types`
--

CREATE TABLE IF NOT EXISTS `types` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key of the table',
  `name` varchar(45) COLLATE utf8_unicode_ci NOT NULL COMMENT 'the name of the type that can be used to describe the items and clssify them accordingly, it help also in choosing the right store',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='indicates the types of items which are stored in the system' AUTO_INCREMENT=24 ;

--
-- Dumping data for table `types`
--

INSERT INTO `types` (`id`, `name`) VALUES
(1, 'flammable'),
(2, 'equipment'),
(3, 'geography'),
(4, 'acid'),
(5, 'chemical'),
(6, 'living_sample'),
(7, 'radioactive'),
(8, 'indicator'),
(9, 'Stain'),
(10, 'Buffer'),
(11, 'Disinfectant'),
(12, 'Kit - Chemistry'),
(13, 'Kit - Physics'),
(14, 'Kit - Biology'),
(15, 'Kit - Electric'),
(16, 'Kit - Electronic'),
(17, 'Kit - Tools'),
(18, 'Kit - Glassware'),
(19, 'Glassware'),
(20, 'consumables'),
(21, 'geology'),
(22, 'IT'),
(23, 'Stationary');

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

--
-- Table structure for table `unit`
--

CREATE TABLE IF NOT EXISTS `unit` (
  `unit` char(5) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`unit`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `unit`
--

INSERT INTO `unit` (`unit`) VALUES
('box'),
('gram'),
('kilo'),
('litre'),
('ml'),
('unit');

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

--
-- Structure for view `items_view`
--

CREATE  VIEW `items_view` AS select `items`.`id` AS `id`,`items`.`name` AS `item`,`types`.`name` AS `type`,`items`.`unit` AS `unit`,`items`.`quantity` AS `quantity`,`items`.`consumption` AS `consumption`,(`items`.`quantity` - `items`.`consumption`) AS `remaining`,`items`.`vendor` AS `vendor`,`items`.`catno` AS `catno`,`items`.`arrived` AS `arrived`,`items`.`expires` AS `expires`,`rooms`.`name` AS `room`,`stores`.`name` AS `store`,`items`.`image` AS `image`,`items`.`comments` AS `comments` from (((`items` join `stores` on((`stores`.`id` = `items`.`store_id`))) join `rooms` on((`rooms`.`id` = `stores`.`room_id`))) join `types` on((`types`.`id` = `items`.`type_id`))) order by `items`.`name`;

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

--
-- Structure for view `orders_detail_view`
--

CREATE  VIEW `orders_detail_view` AS select `order_detail`.`id` AS `id`,`orders`.`teacher` AS `teacher`,`orders`.`lesson` AS `lesson`,`rooms`.`name` AS `room`,`orders`.`request_date` AS `request_date`,`orders`.`note` AS `order note`,`items`.`name` AS `item`,`order_detail`.`quantity` AS `quantity`,`order_detail`.`unit` AS `unit`,`order_detail`.`concentration` AS `concentration`,`order_detail`.`note` AS `detail note`,`order_detail`.`room_id` AS `in room`,`order_detail`.`store_id` AS `in store`,`orders`.`return_date` AS `return_date`,`orders`.`order_date` AS `order_date` from (((`order_detail` join `orders` on((`orders`.`id` = `order_detail`.`order_id`))) join `rooms` on((`rooms`.`id` = `orders`.`room`))) join `items` on((`items`.`id` = `order_detail`.`item_id`)));

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

--
-- Structure for view `orders_view`
--

CREATE  VIEW `orders_view` AS select `orders`.`id` AS `id`,`rooms`.`name` AS `room`,`orders`.`order_date` AS `order_date`,`orders`.`request_date` AS `request_date`,`orders`.`return_date` AS `return_date`,`lesson`.`name` AS `lesson`,`lesson`.`starts` AS `starts`,`lesson`.`ends` AS `ends`,`orders`.`teacher` AS `teacher`,`orders`.`note` AS `note` from ((`orders` join `lesson` on((`lesson`.`id` = `orders`.`lesson`))) join `rooms` on((`rooms`.`id` = `orders`.`room`))) order by `orders`.`request_date` desc;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `items`
--
ALTER TABLE `items`
  ADD CONSTRAINT `items_ibfk_1` FOREIGN KEY (`type_id`) REFERENCES `types` (`id`),
  ADD CONSTRAINT `items_ibfk_2` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`),
  ADD CONSTRAINT `items_ibfk_3` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`),
  ADD CONSTRAINT `items_ibfk_4` FOREIGN KEY (`unit`) REFERENCES `unit` (`unit`),
  ADD CONSTRAINT `items_ibfk_5` FOREIGN KEY (`type_id`) REFERENCES `types` (`id`),
  ADD CONSTRAINT `items_ibfk_6` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`),
  ADD CONSTRAINT `items_ibfk_7` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`),
  ADD CONSTRAINT `items_ibfk_8` FOREIGN KEY (`unit`) REFERENCES `unit` (`unit`);

--
-- Constraints for table `orders`
--
ALTER TABLE `orders`
  ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`lesson`) REFERENCES `lesson` (`id`),
  ADD CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`room`) REFERENCES `rooms` (`id`),
  ADD CONSTRAINT `orders_ibfk_3` FOREIGN KEY (`teacher`) REFERENCES `useraccounts` (`username`),
  ADD CONSTRAINT `orders_ibfk_4` FOREIGN KEY (`lesson`) REFERENCES `lesson` (`id`),
  ADD CONSTRAINT `orders_ibfk_5` FOREIGN KEY (`room`) REFERENCES `rooms` (`id`),
  ADD CONSTRAINT `orders_ibfk_6` FOREIGN KEY (`teacher`) REFERENCES `useraccounts` (`username`);

--
-- Constraints for table `order_detail`
--
ALTER TABLE `order_detail`
  ADD CONSTRAINT `order_detail_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  ADD CONSTRAINT `order_detail_ibfk_2` FOREIGN KEY (`unit`) REFERENCES `unit` (`unit`),
  ADD CONSTRAINT `order_detail_ibfk_3` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `order_detail_ibfk_4` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  ADD CONSTRAINT `order_detail_ibfk_5` FOREIGN KEY (`unit`) REFERENCES `unit` (`unit`),
  ADD CONSTRAINT `order_detail_ibfk_6` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `stores`
--
ALTER TABLE `stores`
  ADD CONSTRAINT `stores_ibfk_1` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`),
  ADD CONSTRAINT `stores_ibfk_2` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`);
