-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------





--
-- Definition of table `asset_groups`
--

CREATE TABLE `asset_groups` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `code` varchar(8) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `asset_groups`
--

INSERT INTO `asset_groups` (`id`,`code`,`name`) VALUES 
 (1,'PW','Planned Work'),
 (2,'SBT','Standby Time'),
 (3,'DS','Distribution Subs'),
 (4,'ZS','Zone Substations'),
 (5,'TS','Subtrans Substations'),
 (6,'UT','Subtrans UG Mains'),
 (7,'OT','Subtrans OH Mains'),
 (8,'DO','Distribution OH Mains'),
 (9,'DU','Distribution UG Mains'),
 (10,'SL','Street Lighting'),
 (11,'NPT','Non-Productive Time'),
 (12,'PIO','Proj / Int Order #'),
 (13,'DEP','Damage by Ext Parties'),
 (14,'TRN','Training'),
 (15,'OTH','Other');


--
-- Definition of table `asset_mappings`
--

CREATE TABLE `asset_mappings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `region_id` tinyint(4) NOT NULL,
  `asset_group_id` tinyint(4) NOT NULL,
  `job_type_id` tinyint(4) DEFAULT NULL,
  `code` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_region` (`region_id`),
  KEY `idx_asset_group` (`asset_group_id`),
  KEY `idx_job_type` (`job_type_id`),
  CONSTRAINT `asset_mappings_ibfk_1` FOREIGN KEY (`region_id`) REFERENCES `regions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `asset_mappings_ibfk_2` FOREIGN KEY (`asset_group_id`) REFERENCES `asset_groups` (`id`) ON DELETE CASCADE,
  CONSTRAINT `asset_mappings_ibfk_3` FOREIGN KEY (`job_type_id`) REFERENCES `job_types` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=85 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `asset_mappings`
--

INSERT INTO `asset_mappings` (`id`,`region_id`,`asset_group_id`,`job_type_id`,`code`) VALUES 
 (1,2,3,1,'1300010405'),
 (2,2,4,1,'1300010409'),
 (3,2,5,1,'1300010413'),
 (4,2,6,1,'1300010417'),
 (5,2,7,1,'1300010421'),
 (6,2,8,1,'1300010426'),
 (7,2,9,1,'1300010430'),
 (8,2,10,1,'1300000056/0020'),
 (9,2,3,2,'1400001164'),
 (10,2,4,2,'1400001168'),
 (11,2,5,2,'1400001172'),
 (12,2,6,2,'1400001176'),
 (13,2,7,2,'1400001180'),
 (14,2,8,2,'1400001184'),
 (15,2,9,2,'1400001188'),
 (16,2,10,2,'1400000022/0020'),
 (17,2,11,NULL,'1719'),
 (18,2,2,NULL,'1719'),
 (19,2,13,NULL,'1719'),
 (20,3,3,NULL,'1300010407'),
 (21,3,4,1,'1300010411'),
 (22,3,5,1,'1300010415'),
 (23,3,6,1,'1300010419'),
 (24,3,7,1,'1300010424'),
 (25,3,8,1,'1300010428'),
 (26,3,9,1,'1300010432'),
 (27,3,10,1,'1300000068/0020'),
 (28,3,3,2,'1400001166'),
 (29,3,4,2,'1400001170'),
 (30,3,5,2,'1400001174'),
 (31,3,6,2,'1400001178'),
 (32,3,7,2,'1400001182'),
 (33,3,8,2,'1400001186'),
 (34,3,9,2,'1400001190'),
 (35,3,10,2,'1400000034/0020'),
 (36,3,11,NULL,'1718'),
 (37,3,2,NULL,'1718'),
 (38,3,13,NULL,'1718'),
 (39,1,3,1,'1300010406'),
 (40,1,4,1,'1300010410'),
 (41,1,5,1,'1300010414'),
 (42,1,6,1,'1300010418'),
 (43,1,7,1,'1300010423'),
 (44,1,8,1,'1300010427'),
 (45,1,9,1,'1300010431'),
 (46,1,10,1,'1300000053/0020'),
 (47,1,3,2,'1400001165'),
 (48,1,4,2,'1400001169'),
 (49,1,5,2,'1400001173'),
 (50,1,6,2,'1400001177'),
 (51,1,7,2,'1400001181'),
 (52,1,8,2,'1400001185'),
 (53,1,9,2,'1400001189'),
 (54,1,10,2,'1400000019/0020'),
 (55,1,11,NULL,'1724'),
 (56,1,2,NULL,'1724'),
 (57,1,13,NULL,'1724'),
 (58,4,3,1,'1300010408'),
 (59,4,4,1,'1300010412'),
 (60,4,5,1,'1300010416'),
 (61,4,6,1,'1300010420'),
 (62,4,7,1,'1300010424'),
 (63,4,8,1,'1300010429'),
 (64,4,9,1,'1300010433'),
 (65,4,10,1,'1300000176/0020'),
 (66,4,3,2,'1400001167'),
 (67,4,4,2,'1400001171'),
 (68,4,5,2,'1400001175'),
 (69,4,6,2,'1400001179'),
 (70,4,7,2,'1400001183'),
 (71,4,8,2,'1400001187'),
 (72,4,9,2,'1400001191'),
 (73,4,10,2,'1400000084/0020'),
 (74,4,11,NULL,'1725'),
 (75,4,2,NULL,'1725'),
 (76,4,13,NULL,'1725'),
 (77,1,14,NULL,'1724'),
 (78,2,14,NULL,'1719'),
 (79,3,14,NULL,'1718'),
 (80,4,14,NULL,'1725'),
 (81,1,15,NULL,'1724'),
 (82,2,15,NULL,'1719'),
 (83,3,15,NULL,'1718'),
 (84,4,15,NULL,'1725');


--
-- Definition of table `delegations`
--

CREATE TABLE `delegations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `delegator` int(11) NOT NULL,
  `delegatee` int(11) NOT NULL,
  `date_from` date DEFAULT NULL,
  `date_to` date DEFAULT NULL,
  `active` bit(1) DEFAULT b'1',
  PRIMARY KEY (`id`),
  KEY `fk_users_delegator` (`delegator`),
  KEY `fk_users_delegatee` (`delegatee`),
  KEY `idx_date_from` (`date_from`),
  KEY `idx_date_to` (`date_to`),
  CONSTRAINT `delegations_ibfk_1` FOREIGN KEY (`delegator`) REFERENCES `users` (`service_number`),
  CONSTRAINT `delegations_ibfk_2` FOREIGN KEY (`delegatee`) REFERENCES `users` (`service_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `delegations`
--



--
-- Definition of table `entry_statuses`
--

CREATE TABLE `entry_statuses` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `code` varchar(16) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `entry_statuses`
--

INSERT INTO `entry_statuses` (`id`,`code`,`name`) VALUES 
 (1,'pending','Pending'),
 (2,'completed','Completed'),
 (3,'rejected','Rejected');


--
-- Definition of table `job_entries`
--

CREATE TABLE `job_entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `request_id` int(11) NOT NULL,
  `serial_number` int(11) DEFAULT NULL,
  `region_id` tinyint(4) NOT NULL,
  `asset_group_id` tinyint(4) NOT NULL,
  `job_type_id` tinyint(4) DEFAULT NULL,
  `job_type_details` varchar(255) DEFAULT NULL,
  `job_number` bigint(20) unsigned DEFAULT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `comments` text,
  `status_id` tinyint(4) NOT NULL DEFAULT '1',
  `overtime_minutes_before` int(11) DEFAULT '0',
  `overtime_minutes_after` int(11) DEFAULT '0',
  `last_updated_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `request_id` (`request_id`,`serial_number`),
  KEY `idx_region` (`region_id`),
  KEY `idx_asset_group` (`asset_group_id`),
  KEY `idx_job_type` (`job_type_id`),
  KEY `idx_status` (`status_id`),
  CONSTRAINT `job_entries_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`),
  CONSTRAINT `job_entries_ibfk_2` FOREIGN KEY (`region_id`) REFERENCES `regions` (`id`),
  CONSTRAINT `job_entries_ibfk_3` FOREIGN KEY (`asset_group_id`) REFERENCES `asset_groups` (`id`),
  CONSTRAINT `job_entries_ibfk_4` FOREIGN KEY (`job_type_id`) REFERENCES `job_types` (`id`),
  CONSTRAINT `job_entries_ibfk_5` FOREIGN KEY (`status_id`) REFERENCES `entry_statuses` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=270 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `job_entries`
--

INSERT INTO `job_entries` (`id`,`request_id`,`serial_number`,`region_id`,`asset_group_id`,`job_type_id`,`job_type_details`,`job_number`,`start_time`,`end_time`,`comments`,`status_id`,`overtime_minutes_before`,`overtime_minutes_after`,`last_updated_by`) VALUES 
 (1,1,1,1,2,NULL,NULL,NULL,'06:00:00','07:00:00','',2,0,0,NULL),
 (2,1,2,1,1,NULL,NULL,123456789123,'07:00:00','08:00:00','comments',2,0,0,NULL),
 (3,1,3,2,15,3,'some comment',NULL,'08:00:00','09:00:00','comments here',3,0,0,NULL),
 (4,2,1,1,2,NULL,NULL,NULL,'06:00:00','07:00:00','',1,0,0,NULL),
 (5,2,2,1,1,NULL,NULL,123456789123,'07:00:00','08:00:00','comments',1,0,0,NULL),
 (6,2,3,2,15,3,'some comment',NULL,'08:00:00','09:00:00','comments here',1,0,0,NULL),
 (7,3,1,1,2,NULL,NULL,NULL,'06:00:00','07:00:00','',1,0,0,NULL),
 (8,3,2,1,1,NULL,NULL,123456789123,'07:00:00','08:00:00','comments',1,0,0,NULL),
 (9,3,3,2,15,3,'some comment',NULL,'08:00:00','09:00:00','comments here',1,0,0,NULL),
 (10,4,1,1,1,NULL,NULL,112345678912,'05:00:00','05:30:00','Over time example',1,0,0,NULL),
 (11,4,2,1,3,1,NULL,NULL,'05:30:00','06:15:00','Over time example',1,0,0,NULL),
 (12,4,3,1,2,NULL,NULL,NULL,'08:00:00','09:00:00','Over time example',1,0,0,NULL),
 (13,4,4,1,4,2,NULL,NULL,'01:30:00','02:30:00','Over time example',1,0,0,NULL),
 (14,4,5,1,5,2,NULL,NULL,'03:00:00','03:30:00','Over time example',1,0,0,NULL),
 (15,5,1,1,1,NULL,NULL,1111111111111,'06:00:00','14:00:00',NULL,1,0,0,NULL),
 (16,6,1,1,1,NULL,NULL,1234567891011,'14:00:00','16:00:00',NULL,1,0,0,NULL),
 (17,6,2,1,7,1,NULL,NULL,'16:00:00','22:00:00',NULL,1,0,0,NULL),
 (18,7,1,1,1,NULL,NULL,9000123456789,'06:00:00','08:45:00',NULL,1,0,0,NULL),
 (19,7,2,1,3,1,NULL,NULL,'08:45:00','11:23:00',NULL,1,0,0,NULL),
 (24,8,1,1,1,NULL,NULL,9000182604000,'06:00:00','08:00:00',NULL,1,0,0,NULL),
 (25,8,2,1,2,NULL,NULL,NULL,'08:00:00','10:00:00',NULL,1,0,0,NULL),
 (26,8,3,1,4,1,NULL,NULL,'10:00:00','14:00:00',NULL,1,0,0,NULL),
 (27,8,4,1,13,3,'DEP Car Hit Pole 14 Nelson Street DOP Truck',NULL,'14:00:00','20:30:00',NULL,1,0,390,NULL),
 (28,9,1,1,4,2,NULL,NULL,'06:00:00','08:00:00',NULL,1,0,0,NULL),
 (29,9,2,1,1,NULL,NULL,9000182600040,'08:00:00','10:00:00',NULL,1,0,0,NULL),
 (30,9,3,1,1,NULL,NULL,9000182600040,'10:00:00','12:00:00',NULL,1,0,0,NULL),
 (31,9,4,1,2,NULL,NULL,NULL,'12:00:00','14:00:00',NULL,1,0,0,NULL),
 (49,14,1,1,1,NULL,NULL,123456789123456,'20:00:00','23:00:00','was required to start early',1,120,0,NULL),
 (50,14,2,1,4,1,NULL,NULL,'23:00:00','04:30:00',NULL,1,0,0,NULL),
 (51,14,3,1,11,NULL,NULL,NULL,'04:30:00','05:00:00',NULL,1,0,0,NULL),
 (52,14,4,1,3,2,NULL,NULL,'05:00:00','07:00:00',NULL,1,0,60,NULL),
 (53,14,5,1,9,2,NULL,NULL,'07:00:00','11:55:00',NULL,1,0,295,NULL),
 (54,14,6,1,11,NULL,NULL,NULL,'11:55:00','12:00:00',NULL,1,0,5,NULL),
 (55,15,1,4,1,NULL,NULL,123456789789456123,'19:00:00','00:00:00','required to start early',1,240,0,NULL),
 (56,15,2,4,11,NULL,NULL,NULL,'00:00:00','01:00:00',NULL,1,0,0,NULL),
 (57,15,3,4,5,2,NULL,NULL,'01:00:00','03:00:00',NULL,1,0,0,NULL),
 (58,15,4,4,6,1,NULL,NULL,'03:00:00','04:00:00',NULL,1,0,0,NULL),
 (59,15,5,4,8,2,NULL,NULL,'04:00:00','07:00:00',NULL,1,0,0,NULL),
 (60,15,6,4,10,2,NULL,NULL,'07:00:00','10:00:00',NULL,1,0,180,NULL),
 (61,16,1,1,2,NULL,NULL,NULL,'06:00:00','07:00:00',NULL,1,0,0,NULL),
 (62,16,2,1,3,1,NULL,NULL,'07:00:00','08:00:00',NULL,1,0,0,NULL),
 (63,16,3,1,3,2,NULL,NULL,'08:00:00','08:30:00',NULL,1,0,0,NULL),
 (64,16,4,1,4,1,NULL,NULL,'08:30:00','09:00:00',NULL,1,0,0,NULL),
 (65,16,5,1,4,2,NULL,NULL,'09:00:00','09:30:00',NULL,1,0,0,NULL),
 (66,16,6,1,5,1,NULL,NULL,'09:30:00','10:00:00',NULL,1,0,0,NULL),
 (67,16,7,1,4,2,NULL,NULL,'10:00:00','10:30:00',NULL,1,0,0,NULL),
 (68,16,8,1,5,1,NULL,NULL,'10:30:00','11:00:00',NULL,1,0,0,NULL),
 (69,16,9,1,5,2,NULL,NULL,'11:00:00','11:30:00',NULL,1,0,0,NULL),
 (70,16,10,1,6,1,NULL,NULL,'11:30:00','12:00:00',NULL,1,0,0,NULL),
 (71,16,11,1,6,2,NULL,NULL,'12:00:00','12:30:00',NULL,1,0,0,NULL),
 (72,16,12,1,7,1,NULL,NULL,'12:30:00','13:00:00',NULL,1,0,0,NULL),
 (73,16,13,1,7,2,NULL,NULL,'13:00:00','13:30:00',NULL,1,0,0,NULL),
 (74,16,14,1,8,1,NULL,NULL,'13:30:00','14:00:00',NULL,1,0,0,NULL),
 (75,16,15,1,8,2,NULL,NULL,'14:00:00','14:30:00',NULL,1,0,30,NULL),
 (76,16,16,1,9,1,NULL,NULL,'14:30:00','15:00:00',NULL,1,0,30,NULL),
 (77,16,17,1,9,2,NULL,NULL,'15:00:00','15:30:00',NULL,1,0,30,NULL),
 (78,16,18,1,10,1,NULL,NULL,'15:30:00','16:00:00',NULL,1,0,30,NULL),
 (79,16,19,1,10,2,NULL,NULL,'16:00:00','16:30:00',NULL,1,0,30,NULL),
 (80,16,20,1,11,NULL,NULL,NULL,'16:30:00','17:00:00',NULL,1,0,30,NULL),
 (81,16,21,1,14,NULL,NULL,NULL,'17:00:00','17:30:00',NULL,1,0,30,NULL),
 (82,17,1,2,2,NULL,NULL,NULL,'13:00:00','13:30:00',NULL,1,0,30,NULL),
 (83,17,2,2,3,1,NULL,NULL,'13:30:00','14:00:00',NULL,1,0,30,NULL),
 (84,17,3,2,3,2,NULL,NULL,'14:00:00','14:30:00',NULL,1,30,0,NULL),
 (85,17,4,2,4,1,NULL,NULL,'14:30:00','15:00:00',NULL,1,30,0,NULL),
 (86,17,5,2,4,2,NULL,NULL,'15:00:00','15:30:00',NULL,1,30,0,NULL),
 (87,17,6,2,5,1,NULL,NULL,'15:30:00','16:00:00',NULL,1,30,0,NULL),
 (88,17,7,2,5,2,NULL,NULL,'16:00:00','17:00:00',NULL,1,60,0,NULL),
 (89,17,8,2,6,1,NULL,NULL,'17:00:00','17:30:00',NULL,1,30,0,NULL),
 (90,17,9,2,6,2,NULL,NULL,'17:30:00','18:00:00',NULL,1,30,0,NULL),
 (91,17,10,2,7,1,NULL,NULL,'18:00:00','22:00:00',NULL,1,240,0,NULL),
 (92,17,11,2,7,2,NULL,NULL,'22:00:00','00:00:00',NULL,1,0,0,NULL),
 (93,17,12,2,9,1,NULL,NULL,'00:00:00','01:00:00',NULL,1,0,0,NULL),
 (94,17,13,2,9,2,NULL,NULL,'01:00:00','02:00:00',NULL,1,0,0,NULL),
 (95,17,14,2,10,1,NULL,NULL,'02:00:00','03:00:00',NULL,1,0,0,NULL),
 (96,17,15,2,10,2,NULL,NULL,'03:00:00','07:00:00',NULL,1,0,60,NULL),
 (97,17,16,2,11,NULL,NULL,NULL,'07:00:00','09:00:00',NULL,1,0,120,NULL),
 (98,17,17,2,14,NULL,NULL,NULL,'09:00:00','10:00:00',NULL,1,0,60,NULL),
 (99,17,18,2,15,3,'just wanted to bludge',NULL,'10:00:00','12:00:00',NULL,1,0,120,NULL),
 (100,18,1,2,1,NULL,NULL,123456789123456,'20:00:00','23:30:00',NULL,1,120,0,NULL),
 (101,18,2,2,6,2,NULL,NULL,'23:30:00','01:30:00',NULL,1,0,0,NULL),
 (102,18,3,2,11,NULL,NULL,NULL,'01:30:00','03:00:00',NULL,1,0,0,NULL),
 (103,18,4,2,3,1,NULL,NULL,'03:00:00','09:00:00',NULL,1,0,180,NULL),
 (104,18,5,2,14,NULL,NULL,NULL,'09:00:00','11:00:00',NULL,1,0,120,NULL),
 (105,19,1,4,15,3,'afternoon siesta',NULL,'13:00:00','22:00:00',NULL,1,540,0,NULL),
 (106,19,2,4,9,2,NULL,NULL,'22:00:00','00:30:00',NULL,1,60,0,NULL),
 (107,19,3,4,4,1,NULL,NULL,'00:30:00','05:00:00',NULL,1,0,0,NULL),
 (108,19,4,4,9,2,NULL,NULL,'05:00:00','12:00:00',NULL,1,0,300,NULL),
 (109,20,1,4,15,3,'123456789',NULL,'14:00:00','22:00:00',NULL,1,0,0,NULL),
 (110,21,1,3,15,3,'',NULL,'15:00:00','23:00:00',NULL,1,0,0,NULL),
 (111,22,1,1,4,1,NULL,NULL,'06:00:00','08:00:00',NULL,1,0,0,NULL),
 (112,22,2,1,14,NULL,NULL,NULL,'08:00:00','10:00:00','training',1,0,0,NULL),
 (113,22,3,1,10,1,NULL,NULL,'10:00:00','10:30:00',NULL,1,0,0,NULL),
 (114,22,4,1,1,NULL,NULL,110012465230,'10:30:00','12:30:00',NULL,1,0,0,NULL),
 (115,22,5,1,2,NULL,NULL,NULL,'12:30:00','14:00:00',NULL,1,0,0,NULL),
 (116,22,6,1,13,3,'DEP 14 Nelson St',NULL,'14:00:00','21:00:00',NULL,1,0,420,NULL),
 (117,23,1,1,8,1,NULL,NULL,'10:00:00','12:00:00',NULL,1,120,0,NULL),
 (118,23,2,1,3,1,NULL,NULL,'12:00:00','14:00:00',NULL,1,120,0,NULL),
 (119,23,3,1,2,NULL,NULL,NULL,'14:00:00','16:00:00',NULL,1,0,0,NULL),
 (120,23,4,1,5,1,NULL,NULL,'16:00:00','17:30:00',NULL,1,0,0,NULL),
 (121,23,5,1,8,1,NULL,NULL,'17:30:00','22:00:00',NULL,1,0,0,NULL),
 (122,23,6,1,4,1,NULL,NULL,'22:00:00','02:30:00',NULL,1,0,270,NULL),
 (123,24,1,1,13,3,'DEP Mowbray Rd',NULL,'22:00:00','21:00:00',NULL,1,0,900,NULL),
 (124,25,1,1,3,1,NULL,NULL,'12:00:00','18:00:00',NULL,1,180,0,NULL),
 (125,25,2,1,2,NULL,NULL,NULL,'18:00:00','20:00:00',NULL,1,0,0,NULL),
 (126,25,3,1,4,1,NULL,NULL,'20:00:00','02:00:00',NULL,1,0,180,NULL),
 (127,26,1,1,4,1,NULL,NULL,'04:00:00','06:00:00',NULL,1,120,0,NULL),
 (128,26,2,1,2,NULL,NULL,NULL,'06:00:00','10:00:00',NULL,1,0,0,NULL),
 (129,26,3,1,5,1,NULL,NULL,'10:00:00','12:00:00',NULL,1,0,0,NULL),
 (130,26,4,1,13,3,'DEP 14 Nelson St',NULL,'12:00:00','16:00:00',NULL,1,0,120,NULL),
 (133,27,1,1,1,NULL,NULL,11111111111111,'06:00:00','08:00:00','Peter slept in',1,0,0,NULL),
 (134,27,2,1,5,2,NULL,NULL,'08:00:00','14:00:00',NULL,1,0,0,NULL),
 (135,27,3,1,4,1,NULL,NULL,'14:00:00','22:00:00',NULL,1,0,480,NULL),
 (136,28,1,3,1,NULL,NULL,123456789,'06:00:00','08:00:00',NULL,1,0,0,NULL),
 (137,29,1,3,1,NULL,NULL,1234567889,'06:00:00','09:00:00',NULL,1,0,0,NULL),
 (138,30,1,1,13,3,'DEP Car hit Pole 14 Nelson St',NULL,'06:00:00','14:00:00','DEP Car hit Pole 14 Nelson St',1,0,0,NULL),
 (142,31,1,2,13,3,'comment job type',NULL,'10:00:00','04:00:00','comment add comment',1,240,360,NULL),
 (143,32,1,2,13,3,'comment job type',NULL,'12:00:00','04:30:00','add comments',1,120,390,NULL),
 (144,33,1,1,3,1,NULL,NULL,'06:00:00','16:00:00',NULL,1,0,120,NULL),
 (145,34,1,1,6,1,NULL,NULL,'06:00:00','14:00:00',NULL,1,0,0,NULL),
 (146,35,1,1,1,NULL,NULL,123456789,'06:00:00','14:00:00',NULL,1,0,0,47802),
 (147,36,1,1,1,NULL,NULL,123456789,'06:00:00','14:00:00',NULL,1,0,0,47802),
 (148,37,1,3,1,NULL,NULL,123456789,'06:00:00','10:00:00',NULL,1,0,0,43796),
 (149,37,2,3,7,1,NULL,NULL,'10:00:00','12:00:00',NULL,1,0,0,43796),
 (150,37,3,3,14,NULL,NULL,NULL,'12:00:00','14:00:00',NULL,1,0,0,43796),
 (151,36,2,3,14,NULL,NULL,NULL,'12:00:00','14:00:00',NULL,1,0,0,43796),
 (152,37,4,3,11,NULL,NULL,NULL,'14:00:00','16:00:00',NULL,1,0,120,43796),
 (153,38,1,1,1,NULL,NULL,987654321,'06:00:00','10:00:00',NULL,1,0,0,28595),
 (154,38,2,1,4,2,NULL,NULL,'10:00:00','14:30:00',NULL,1,0,30,28595),
 (155,39,1,1,3,1,NULL,NULL,'06:00:00','14:00:00',NULL,1,0,0,36102),
 (156,40,1,1,4,2,NULL,NULL,'06:00:00','10:00:00',NULL,1,0,0,32360),
 (157,40,2,1,6,1,NULL,NULL,'10:00:00','13:50:00',NULL,1,0,0,32360),
 (158,40,3,1,10,1,NULL,NULL,'13:50:00','16:00:00',NULL,1,0,120,32360),
 (159,41,1,1,1,NULL,NULL,1000000000000,'07:00:00','13:00:00',NULL,1,0,0,47761),
 (161,43,1,1,1,NULL,NULL,16985,'06:00:00','09:30:00',NULL,1,0,0,44658),
 (164,42,1,1,1,NULL,NULL,16985,'06:00:00','09:30:00',NULL,1,0,0,44658),
 (165,42,2,1,3,1,NULL,NULL,'09:30:00','11:30:00',NULL,1,0,0,44658),
 (166,42,3,2,4,1,NULL,NULL,'11:30:00','14:00:00',NULL,1,0,0,44658),
 (170,45,1,3,1,NULL,NULL,123456789,'14:00:00','18:00:00',NULL,1,0,0,43796),
 (171,45,2,3,1,NULL,NULL,123234345,'18:00:00','20:00:00',NULL,1,0,0,43796),
 (172,45,3,3,2,NULL,NULL,NULL,'20:00:00','22:00:00',NULL,1,0,0,43796),
 (173,46,1,3,15,3,'',NULL,'22:00:00','01:00:00','Site visit assessment',1,0,0,43796),
 (174,46,2,3,8,2,NULL,NULL,'01:00:00','05:00:00',NULL,1,0,0,43796),
 (175,46,3,3,3,2,NULL,NULL,'05:00:00','06:00:00',NULL,1,0,0,43796),
 (176,47,1,3,3,2,NULL,NULL,'05:00:00','06:00:00',NULL,1,0,0,43796),
 (177,44,1,1,2,NULL,NULL,NULL,'06:00:00','08:00:00',NULL,1,0,0,43796),
 (178,44,2,2,5,1,NULL,NULL,'08:00:00','12:00:00',NULL,1,0,0,43796),
 (179,44,3,1,3,2,NULL,NULL,'12:00:00','14:00:00',NULL,1,0,0,43796),
 (180,48,1,4,2,NULL,NULL,NULL,'06:00:00','14:00:00',NULL,1,0,0,43796),
 (181,49,1,1,3,1,NULL,NULL,'06:00:00','09:00:00',NULL,1,0,0,44658),
 (182,50,1,1,3,1,NULL,NULL,'06:00:00','09:00:00',NULL,1,0,0,44658),
 (183,51,1,1,2,NULL,NULL,NULL,'06:00:00','06:45:00',NULL,1,0,0,34383),
 (184,51,2,1,13,3,'11kV Feeder Failure',NULL,'06:45:00','11:30:00','Crane in contact with feeder',1,0,0,34383),
 (185,51,3,1,1,NULL,NULL,305503,'11:30:00','12:45:00',NULL,1,0,0,34383),
 (186,51,4,1,2,NULL,NULL,NULL,'12:45:00','14:00:00',NULL,1,0,0,34383),
 (187,52,1,2,4,1,NULL,NULL,'06:00:00','08:00:00',NULL,1,0,0,28613),
 (188,52,2,2,2,NULL,NULL,NULL,'08:00:00','12:00:00',NULL,1,0,0,28613),
 (189,52,3,2,9,1,NULL,NULL,'12:00:00','14:00:00',NULL,1,0,0,28613),
 (190,53,1,2,3,1,NULL,NULL,'06:00:00','10:00:00',NULL,1,0,0,37323),
 (191,53,2,2,3,1,NULL,NULL,'10:00:00','12:00:00',NULL,1,0,0,37323),
 (192,53,3,2,2,NULL,NULL,NULL,'12:00:00','14:00:00',NULL,1,0,0,37323),
 (193,54,1,2,14,NULL,NULL,NULL,'07:00:00','15:00:00','EWP POLE TOP Training @ Zetland',1,0,0,43066),
 (194,55,1,2,13,3,'Car Hit Pole Princess Hwy. REGO BH98AS',NULL,'12:00:00','16:00:00',NULL,1,120,0,43067),
 (195,55,2,2,2,NULL,NULL,NULL,'16:00:00','18:00:00',NULL,1,0,0,43067),
 (196,55,3,2,8,1,NULL,NULL,'18:00:00','20:30:00',NULL,1,0,0,43067),
 (197,55,4,2,4,1,NULL,NULL,'20:30:00','23:00:00','DMA',1,0,60,43067),
 (198,56,1,1,1,NULL,NULL,1564264642645,'06:00:00','08:30:00',NULL,1,0,0,47670),
 (199,57,1,1,1,NULL,NULL,1564264642645,'06:00:00','08:30:00',NULL,1,0,0,47670),
 (200,58,1,1,8,1,NULL,NULL,'07:00:00','14:00:00',NULL,1,0,0,47670),
 (201,58,2,1,4,2,NULL,NULL,'14:00:00','16:00:00',NULL,1,0,0,47670),
 (202,59,1,1,2,NULL,NULL,NULL,'15:00:00','19:00:00',NULL,1,0,0,47670),
 (203,59,2,1,2,NULL,NULL,NULL,'19:00:00','20:00:00',NULL,1,0,0,47670),
 (204,59,3,1,9,1,NULL,NULL,'20:00:00','21:00:00',NULL,1,0,0,47670),
 (205,59,4,1,1,NULL,NULL,1,'21:00:00','22:00:00',NULL,1,0,0,47670),
 (206,59,5,1,10,2,NULL,NULL,'22:00:00','23:00:00',NULL,1,0,0,47670),
 (207,59,6,1,11,NULL,NULL,NULL,'23:00:00','01:00:00',NULL,1,0,120,47670),
 (208,60,1,1,5,1,NULL,NULL,'12:00:00','14:00:00',NULL,1,120,0,47670),
 (209,61,1,2,2,NULL,NULL,NULL,'22:00:00','04:00:00',NULL,1,0,0,43105),
 (210,61,2,2,8,2,NULL,NULL,'04:00:00','06:00:00','Branch on mains',1,0,0,43105),
 (211,62,1,2,1,NULL,NULL,1700021758,'14:00:00','18:00:00',NULL,1,0,0,43452),
 (212,63,1,2,1,NULL,NULL,1700021758,'14:00:00','18:00:00',NULL,1,0,0,43452),
 (213,62,2,2,3,1,NULL,NULL,'18:00:00','22:00:00',NULL,1,0,0,43452),
 (214,62,3,2,9,1,NULL,NULL,'22:00:00','23:30:00',NULL,1,0,90,43452),
 (215,64,1,2,1,NULL,NULL,110020895,'15:00:00','20:00:00',NULL,1,0,0,44815),
 (216,65,1,2,1,NULL,NULL,110020895,'15:00:00','20:00:00',NULL,1,0,0,44815),
 (217,64,2,2,5,1,NULL,NULL,'20:00:00','23:00:00',NULL,1,0,0,44815),
 (218,65,2,2,5,1,NULL,NULL,'20:00:00','23:00:00',NULL,1,0,0,44815),
 (219,66,1,2,10,1,NULL,NULL,'06:00:00','08:00:00',NULL,1,0,0,47106),
 (220,66,2,2,6,1,NULL,NULL,'08:00:00','12:00:00',NULL,1,0,0,47106),
 (221,66,3,2,2,NULL,NULL,NULL,'12:00:00','14:00:00',NULL,1,0,0,47106),
 (222,66,4,2,1,NULL,NULL,1700021758,'14:00:00','20:00:00',NULL,1,0,360,47106),
 (223,66,5,2,4,1,NULL,NULL,'20:00:00','22:00:00',NULL,1,0,120,47106),
 (246,75,1,2,4,1,NULL,NULL,'07:00:00','10:00:00',NULL,1,0,0,48300),
 (247,76,1,2,4,1,NULL,NULL,'07:00:00','10:00:00',NULL,1,0,0,48300),
 (248,75,2,2,8,1,NULL,NULL,'10:00:00','15:00:00',NULL,1,0,0,48300),
 (249,76,2,2,8,1,NULL,NULL,'10:00:00','15:00:00',NULL,1,0,0,48300),
 (250,75,3,2,3,1,NULL,NULL,'15:00:00','20:00:00',NULL,1,0,180,48300),
 (251,76,3,2,3,1,NULL,NULL,'15:00:00','20:00:00',NULL,1,0,180,48300),
 (252,75,4,2,2,NULL,NULL,NULL,'20:00:00','23:00:00',NULL,1,0,180,48300),
 (253,76,4,2,2,NULL,NULL,NULL,'20:00:00','23:00:00',NULL,1,0,180,48300),
 (254,77,1,2,14,NULL,NULL,NULL,'07:00:00','15:00:00','CPR RRLV 1st Aid - Homebush',1,0,0,43056),
 (255,78,1,2,9,1,NULL,NULL,'06:00:00','12:00:00',NULL,1,0,0,34496),
 (256,79,1,2,9,1,NULL,NULL,'06:00:00','12:00:00',NULL,1,0,0,34496),
 (257,78,2,2,8,2,NULL,NULL,'12:00:00','14:00:00',NULL,1,0,0,34496),
 (258,79,2,2,8,2,NULL,NULL,'12:00:00','14:00:00',NULL,1,0,0,34496),
 (259,80,1,2,9,1,NULL,NULL,'06:00:00','12:00:00',NULL,1,0,0,37728),
 (260,80,2,2,8,2,NULL,NULL,'12:00:00','14:00:00',NULL,1,0,0,37728),
 (261,81,1,2,4,1,NULL,NULL,'14:00:00','22:00:00',NULL,1,0,0,28613),
 (262,82,1,2,4,1,NULL,NULL,'14:00:00','22:00:00',NULL,1,0,0,28613),
 (263,83,1,2,3,1,NULL,NULL,'14:00:00','22:00:00',NULL,1,0,0,37142),
 (264,84,1,2,13,3,'DEP Car Hit Pole',NULL,'14:00:00','20:00:00','REGO BR8543',1,0,0,44396),
 (265,84,2,2,2,NULL,NULL,NULL,'20:00:00','22:00:00',NULL,1,0,0,44396),
 (266,85,1,2,4,1,NULL,NULL,'06:00:00','14:00:00',NULL,1,0,0,46871),
 (267,86,1,2,13,3,'DEP Car Hit Pole',NULL,'06:00:00','14:00:00','REGO AY23HR',1,0,0,43446),
 (268,87,1,2,13,3,'CAR HIT POLE',NULL,'06:00:00','14:00:00','FRANK ST REGO BG54FG',1,0,0,43446),
 (269,88,1,2,13,3,'Car Hit Pole',NULL,'06:00:00','14:00:00','Test Street  REGO BH87GR',1,0,0,41434);


--
-- Definition of table `job_types`
--

CREATE TABLE `job_types` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `code` varchar(16) NOT NULL,
  `name` varchar(255) NOT NULL,
  `requires_details` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `job_types`
--

INSERT INTO `job_types` (`id`,`code`,`name`,`requires_details`) VALUES 
 (1,'breakdown','Breakdown',0),
 (2,'nature_induced','Nature Induced',0),
 (3,'other','Other',1);


--
-- Definition of table `login_codes`
--

CREATE TABLE `login_codes` (
  `service_number` int(11) NOT NULL,
  `code` varchar(64) NOT NULL,
  `valid_until` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`service_number`,`code`),
  CONSTRAINT `login_codes_ibfk_1` FOREIGN KEY (`service_number`) REFERENCES `users` (`service_number`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `login_codes`
--



--
-- Definition of table `permission_resources`
--

CREATE TABLE `permission_resources` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `description` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `permission_resources`
--

INSERT INTO `permission_resources` (`id`,`name`,`description`) VALUES 
 (1,'trainees',NULL),
 (2,'users',NULL);


--
-- Definition of table `permission_roles`
--

CREATE TABLE `permission_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `description` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `permission_roles`
--

INSERT INTO `permission_roles` (`id`,`name`,`description`) VALUES 
 (1,'field_support_officer',NULL),
 (2,'system_admin',NULL);


--
-- Definition of table `permission_rules`
--

CREATE TABLE `permission_rules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `role_id` int(11) NOT NULL,
  `resource_id` int(11) NOT NULL,
  `allowed` bit(1) DEFAULT b'1',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `fk_role_id` (`role_id`),
  KEY `fk_permission_resource_id` (`resource_id`),
  CONSTRAINT `permission_rules_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `permission_roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `permission_rules_ibfk_2` FOREIGN KEY (`resource_id`) REFERENCES `permission_resources` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `permission_rules`
--

INSERT INTO `permission_rules` (`id`,`name`,`role_id`,`resource_id`,`allowed`) VALUES 
 (1,'edit',1,1,0x01),
 (2,'edit',2,2,0x01),
 (3,'edit',2,1,0x01);


--
-- Definition of table `region_cost_centres`
--

CREATE TABLE `region_cost_centres` (
  `region_id` tinyint(4) NOT NULL,
  `cost_centre` int(11) NOT NULL,
  PRIMARY KEY (`region_id`,`cost_centre`),
  UNIQUE KEY `cost_centre` (`cost_centre`),
  CONSTRAINT `region_cost_centres_ibfk_1` FOREIGN KEY (`region_id`) REFERENCES `regions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `region_cost_centres`
--

INSERT INTO `region_cost_centres` (`region_id`,`cost_centre`) VALUES 
 (3,1718),
 (2,1719),
 (1,1724),
 (4,1725);


--
-- Definition of table `regions`
--

CREATE TABLE `regions` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `regions`
--

INSERT INTO `regions` (`id`,`name`) VALUES 
 (1,'Central'),
 (3,'Central Coast'),
 (4,'Newcastle'),
 (2,'South');


--
-- Definition of table `requests`
--

CREATE TABLE `requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `serial_number` int(11) NOT NULL,
  `service_number` int(11) NOT NULL,
  `full_name` varchar(255) NOT NULL,
  `sheet_date` date NOT NULL,
  `section` varchar(255) NOT NULL,
  `cost_centre` int(11) NOT NULL,
  `rostered_shift_id` tinyint(4) NOT NULL,
  `manager_service_number` int(11) NOT NULL,
  `date_submitted` datetime NOT NULL,
  `last_altered_by` int(11) DEFAULT NULL,
  `ot_shift` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `service_number` (`service_number`,`sheet_date`,`rostered_shift_id`),
  KEY `idx_rostered_shift` (`rostered_shift_id`),
  KEY `idx_manager_service_number` (`manager_service_number`),
  KEY `idx_last_altered_by` (`last_altered_by`),
  CONSTRAINT `requests_ibfk_1` FOREIGN KEY (`rostered_shift_id`) REFERENCES `rostered_shifts` (`id`),
  CONSTRAINT `requests_ibfk_2` FOREIGN KEY (`manager_service_number`) REFERENCES `users` (`service_number`),
  CONSTRAINT `requests_ibfk_3` FOREIGN KEY (`last_altered_by`) REFERENCES `users` (`service_number`)
) ENGINE=InnoDB AUTO_INCREMENT=89 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `requests`
--

INSERT INTO `requests` (`id`,`serial_number`,`service_number`,`full_name`,`sheet_date`,`section`,`cost_centre`,`rostered_shift_id`,`manager_service_number`,`date_submitted`,`last_altered_by`,`ot_shift`) VALUES 
 (1,1,39887,'Peter Terry','2012-10-09','Business Systems Development',2101,1,43880,'2012-10-15 00:24:41',NULL,0),
 (2,2,39887,'Peter Terry','2012-10-10','Business Systems Development',2101,1,43880,'2012-10-15 00:25:52',NULL,0),
 (3,3,39887,'Peter Terry','2012-10-11','Business Systems Development',2101,1,43880,'2012-10-15 00:27:06',NULL,0),
 (4,4,39887,'Peter Terry','2012-10-17','Business Systems Development',2101,1,43880,'2012-10-17 19:37:17',NULL,0),
 (5,5,47802,'Matthew Clarke','2012-10-30','Business Systems Development',2101,1,43880,'2012-10-30 11:32:34',NULL,0),
 (6,6,47761,'Stuart Donaldson','2012-10-30','Field Operating',1726,3,44639,'2012-10-30 17:00:34',NULL,0),
 (7,7,44639,'Sam Sofi','2012-10-31','Management',1701,1,39798,'2012-10-31 10:55:31',NULL,0),
 (8,8,50908,'Daryl Johnston','2012-10-02','Field Operating',1724,1,34383,'2012-10-31 13:13:55',NULL,0),
 (9,9,50905,'Steven Mcclure','2012-10-02','Field Operating',1724,1,34383,'2012-10-31 13:36:25',NULL,0),
 (10,1,47802,'Matthew Clarke','2012-11-06','Business Systems Development',2101,1,43880,'2012-11-06 07:01:22',NULL,0),
 (11,2,47802,'Matthew Clarke','2012-11-06','Business Systems Development',2101,6,43880,'2012-11-06 16:35:18',NULL,0),
 (12,3,39887,'Peter Terry','2012-11-06','Business Systems Development',2101,5,43880,'2012-11-06 16:40:31',NULL,0),
 (13,4,43796,'Bernard O\'Brien','2012-11-07','Field Operating',1726,5,45183,'2012-11-07 13:00:35',NULL,0),
 (14,5,47802,'Matthew Clarke','2012-11-08','Business Systems Development',2101,5,43880,'2012-11-08 14:16:22',NULL,0),
 (15,6,47802,'Matthew Clarke','2012-11-08','Business Systems Development',2101,6,43880,'2012-11-08 14:23:02',NULL,0),
 (16,7,47802,'Matthew Clarke','2012-11-09','Business Systems Development',2101,1,43880,'2012-11-09 13:20:56',NULL,0),
 (17,8,47802,'Matthew Clarke','2012-11-09','Business Systems Development',2101,5,43880,'2012-11-09 13:26:04',NULL,0),
 (18,9,47802,'Matthew Clarke','2012-11-12','Business Systems Development',2101,5,43880,'2012-11-12 07:52:13',NULL,0),
 (19,10,47802,'Matthew Clarke','2012-11-11','Business Systems Development',2101,6,43880,'2012-11-12 08:58:38',NULL,0),
 (20,11,47802,'Matthew Clarke','2012-11-10','Business Systems Development',2101,3,43880,'2012-11-12 09:05:15',NULL,0),
 (21,12,47802,'Matthew Clarke','2012-11-05','Business Systems Development',2101,4,43880,'2012-11-12 11:56:57',NULL,0),
 (22,13,50908,'Daryl Johnston','2012-11-04','Field Operating',1724,1,34383,'2012-11-12 18:57:12',NULL,0),
 (23,14,44819,'Scott Burchell','2012-11-04','Field Operating',1724,3,34383,'2012-11-12 19:02:54',NULL,0),
 (24,15,28595,'David Howell','2012-11-04','Field Operating',1724,5,34383,'2012-11-12 19:10:26',NULL,0),
 (25,16,46868,'Tom Duffy','2012-11-04','Field Operating',1719,4,36820,'2012-11-12 19:13:47',NULL,0),
 (26,17,50905,'Steven Mcclure','2012-11-04','Field Operating',1724,1,34383,'2012-11-13 09:51:10',NULL,0),
 (27,18,47802,'Matthew Clarke','2012-11-15','Business Systems Development',2101,1,43880,'2012-11-15 15:05:37',NULL,0),
 (28,19,43796,'Bernard O\'Brien','2012-11-19','Field Operating',1726,1,45183,'2012-11-19 15:23:13',NULL,0),
 (29,20,43796,'Bernard O\'Brien','2012-11-20','Field Operating',1726,1,45183,'2012-11-20 08:28:12',NULL,0),
 (30,21,46306,'John Price','2012-11-04','Field Operating',1724,1,34383,'2012-11-22 09:33:35',NULL,0),
 (31,22,42634,'Trevor Hogan','2012-11-04','Field Operating',1724,3,34383,'2012-11-22 09:54:57',NULL,0),
 (32,23,42634,'Trevor Hogan','2012-11-11','Field Operating',1724,3,34383,'2012-11-22 09:57:36',NULL,0),
 (33,24,43857,'Ray Chand','2012-11-04','Field Operating',1724,1,34383,'2012-11-22 10:20:06',NULL,0),
 (34,25,46681,'Simon Grant','2012-11-05','Field Operating',1724,1,34383,'2012-11-26 10:17:39',NULL,0),
 (35,1,47802,'Matthew Clarke','2012-12-03','Business Systems Development',2101,1,43880,'2012-12-03 13:31:31',NULL,1),
 (36,2,46180,'Jason Bridge','2012-12-03','Business Systems Development',2101,1,43880,'2012-12-03 13:31:31',NULL,1),
 (37,3,43796,'Bernard O\'Brien','2012-12-03','Field Operating',1726,1,45183,'2012-12-03 13:43:59',NULL,1),
 (38,4,28595,'David Howell','2012-12-03','Field Operating',1724,1,34383,'2012-12-03 13:49:34',NULL,1),
 (39,5,36102,'Gary Adams','2012-12-03','Field Operating',1724,1,34383,'2012-12-03 13:51:34',NULL,0),
 (40,6,32360,'Steve Gates','2012-12-03','Field Operating',1724,1,34383,'2012-12-03 13:55:03',NULL,0),
 (41,7,47761,'Stuart Donaldson','2012-12-04','Field Operating',1726,2,44639,'2012-12-04 11:59:58',NULL,0),
 (42,8,44658,'Glenn Scicluna','2012-12-16','Field Operating',1724,1,34383,'2012-12-17 11:44:37',NULL,1),
 (43,9,44659,'Ryan Smith','2012-12-16','Field Operating',1724,1,34383,'2012-12-17 11:39:18',NULL,1),
 (44,10,43796,'Bernard O\'Brien','2012-12-01','Field Operating',1726,1,45183,'2012-12-17 13:00:20',NULL,0),
 (45,11,43796,'Bernard O\'Brien','2012-12-01','Field Operating',1726,3,45183,'2012-12-17 12:54:43',NULL,1),
 (46,12,43796,'Bernard O\'Brien','2012-12-01','Field Operating',1726,5,45183,'2012-12-17 12:56:24',NULL,1),
 (47,13,44797,'Casey Dabovic','2012-12-01','Field Operating',1726,5,45183,'2012-12-17 12:56:24',NULL,1),
 (48,14,43796,'Bernard O\'Brien','2012-12-02','Field Operating',1726,1,45183,'2012-12-17 13:01:08',NULL,0),
 (49,15,44658,'Glenn Scicluna','2012-12-17','Field Operating',1724,1,34383,'2012-12-17 13:17:46',NULL,1),
 (50,16,44659,'Ryan Smith','2012-12-17','Field Operating',1724,1,34383,'2012-12-17 13:17:46',NULL,1),
 (51,17,34383,'Gary Parker','2012-12-15','Field Operating',1724,1,47761,'2012-12-17 13:49:58',NULL,0),
 (52,18,28613,'Milton Rowe','2012-12-14','Field Operating',1719,1,36820,'2012-12-17 16:52:45',NULL,0),
 (53,19,37323,'Craig J Turner','2012-12-14','Field Operating',1719,1,36820,'2012-12-18 10:35:33',NULL,1),
 (54,20,43066,'Stuart Morgan','2012-12-14','Field Operating',1719,2,36820,'2012-12-18 10:36:37',NULL,0),
 (55,21,43067,'James Papaharalambous','2012-12-14','Field Operating',1719,3,36820,'2012-12-18 10:40:29',NULL,0),
 (56,22,47670,'Joshua Johnston','2012-12-18','Field Operating',1724,1,34383,'2012-12-18 10:41:49',NULL,0),
 (57,23,48151,'Mark Buskens','2012-12-18','Field Operating',1724,1,34383,'2012-12-18 10:41:49',NULL,0),
 (58,24,47670,'Joshua Johnston','2012-12-18','Field Operating',1724,2,34383,'2012-12-18 10:42:57',NULL,1),
 (59,25,47670,'Joshua Johnston','2012-12-18','Field Operating',1724,4,34383,'2012-12-18 10:45:58',NULL,1),
 (60,26,47670,'Joshua Johnston','2012-12-18','Field Operating',1724,3,34383,'2012-12-18 10:47:38',NULL,0),
 (61,27,43105,'James Pennington','2012-12-14','Field Operating',1719,5,36820,'2012-12-18 13:12:23',NULL,1),
 (62,28,43452,'Matthew Howard','2012-12-14','Field Operating',1719,3,36820,'2012-12-18 13:37:42',NULL,0),
 (63,29,47852,'Scott Stanborough','2012-12-14','Field Operating',1719,3,36820,'2012-12-18 13:37:42',NULL,0),
 (64,30,44815,'John A Mclean','2012-12-14','Field Operating',1719,4,36820,'2012-12-18 13:39:34',NULL,1),
 (65,31,48151,'Mark Buskens','2012-12-14','Field Operating',1719,4,36820,'2012-12-18 13:39:34',NULL,1),
 (66,32,47106,'Carl Chicco','2012-12-14','Field Operating',1719,1,36820,'2012-12-18 13:41:37',NULL,0),
 (75,33,48300,'Troy Howland','2012-12-14','Field Operating',1719,2,36820,'2012-12-18 13:55:28',NULL,0),
 (76,34,48165,'Scott Miller','2012-12-14','Field Operating',1719,2,36820,'2012-12-18 13:55:28',NULL,0),
 (77,35,43056,'Jason Keen','2012-12-14','Field Operating',1719,2,36820,'2012-12-18 13:56:37',NULL,0),
 (78,36,34496,'Mark Moroney','2012-12-14','Field Operating',1719,1,36820,'2012-12-18 13:58:08',NULL,1),
 (79,37,48411,'Luke Weatherstone','2012-12-14','Field Operating',1719,1,36820,'2012-12-18 13:58:08',NULL,1),
 (80,38,37728,'James Meyer','2012-12-14','Field Operating',1719,1,36820,'2012-12-18 14:11:36',NULL,1),
 (81,39,28613,'Milton Rowe','2012-12-14','Field Operating',1719,3,36820,'2012-12-18 14:36:31',NULL,0),
 (82,40,46897,'Murray Roach','2012-12-14','Field Operating',1719,3,36820,'2012-12-18 14:36:31',NULL,0),
 (83,41,37142,'Steve Payne','2012-12-14','Field Operating',1719,3,36820,'2012-12-18 14:38:52',NULL,1),
 (84,42,44396,'Dinh Phan','2012-12-14','Field Operating',1719,3,36820,'2012-12-18 14:42:47',NULL,1),
 (85,43,46871,'Grant Rogers','2012-12-14','Field Operating',1719,1,36820,'2012-12-18 14:44:12',NULL,1),
 (86,44,43446,'Kyle Farrar','2012-12-04','Field Operating',1719,1,36820,'2012-12-18 14:45:17',NULL,1),
 (87,45,43446,'Kyle Farrar','2012-12-14','Field Operating',1719,1,36820,'2012-12-18 14:48:02',NULL,1),
 (88,46,41434,'Chad Springall','2012-12-14','Field Operating',1719,1,36820,'2012-12-18 14:56:48',NULL,1);


--
-- Definition of table `rostered_shift_categories`
--

CREATE TABLE `rostered_shift_categories` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `code` varchar(16) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `rostered_shift_categories`
--

INSERT INTO `rostered_shift_categories` (`id`,`name`,`code`) VALUES 
 (1,'Day','day'),
 (2,'Afternoon','afternoon'),
 (3,'Night','night');


--
-- Definition of table `rostered_shifts`
--

CREATE TABLE `rostered_shifts` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `category_id` tinyint(4) NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_category_id` (`category_id`),
  CONSTRAINT `rostered_shifts_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `rostered_shift_categories` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `rostered_shifts`
--

INSERT INTO `rostered_shifts` (`id`,`category_id`,`start_time`,`end_time`) VALUES 
 (1,1,'06:00:00','14:00:00'),
 (2,1,'07:00:00','17:00:00'),
 (3,2,'14:00:00','22:00:00'),
 (4,2,'15:00:00','23:00:00'),
 (5,3,'22:00:00','06:00:00'),
 (6,3,'23:00:00','07:00:00');


--
-- Definition of table `trainees`
--

CREATE TABLE `trainees` (
  `service_number` int(11) NOT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`service_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `trainees`
--

INSERT INTO `trainees` (`service_number`,`first_name`,`last_name`) VALUES 
 (42113,'Craig','Birkbeck'),
 (43812,'Sean','Fitzpatrick'),
 (44797,'Casey','Dabovic'),
 (46180,'Jason','Bridge'),
 (46683,'Tim','Holman'),
 (46897,'Murray','Roach'),
 (47265,'James','Huby'),
 (47570,'Andrew','Dally'),
 (47605,'Aaron','Mills'),
 (47660,'Chong','Ding'),
 (47852,'Scott','Stanborough'),
 (48151,'Mark','Buskens'),
 (48154,'Tim','Carney'),
 (48165,'Scott','Miller'),
 (48411,'Luke','Weatherstone'),
 (48737,'John','Bennis'),
 (49623,'Phillip','Holden'),
 (49628,'Aaron','Mote'),
 (49815,'Nigel','Lasheras');


--
-- Definition of table `user_defaults`
--

CREATE TABLE `user_defaults` (
  `service_number` int(11) NOT NULL,
  `region_id` tinyint(4) DEFAULT NULL,
  KEY `idx_service_number` (`service_number`),
  KEY `idx_region` (`region_id`),
  CONSTRAINT `user_defaults_ibfk_1` FOREIGN KEY (`service_number`) REFERENCES `users` (`service_number`) ON DELETE CASCADE,
  CONSTRAINT `user_defaults_ibfk_2` FOREIGN KEY (`region_id`) REFERENCES `regions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `user_defaults`
--

INSERT INTO `user_defaults` (`service_number`,`region_id`) VALUES 
 (47187,3);


--
-- Definition of table `user_roles`
--

CREATE TABLE `user_roles` (
  `service_number` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`service_number`,`role_id`),
  KEY `fk_roles_role_id` (`role_id`),
  CONSTRAINT `user_roles_ibfk_1` FOREIGN KEY (`service_number`) REFERENCES `users` (`service_number`) ON DELETE CASCADE,
  CONSTRAINT `user_roles_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `permission_roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `user_roles`
--

INSERT INTO `user_roles` (`service_number`,`role_id`) VALUES 
 (47187,1),
 (47802,1),
 (47187,2);




