Rev 1 | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
--
-- Table structure for table `maintenance_schedule`
--
DROP TABLE IF EXISTS `maintenance_schedule`;
CREATE TABLE `maintenance_schedule` (
`maintenance_schedule_id` int(10) unsigned NOT NULL auto_increment,
`device_id` int(10) unsigned NOT NULL COMMENT 'The device this task is due on' references device(device_id),
`maintenance_task_id` int(10) unsigned NOT NULL COMMENT 'The task to be performed' references maintenance_task(maintenance_task_id),
`schedule` int(10) unsigned NOT NULL COMMENT 'The number of days between performance of this task',
`login_id` int(10) unsigned default NULL COMMENT 'Login ID of the persion who is generally responsible for this maintenance' references login(login_id),
`added_date` datetime default NULL,
`removed_date` datetime default NULL,
PRIMARY KEY (`maintenance_schedule_id`)
) COMMENT='Holds a list of maintenance to be performed on a device';
--
-- Table structure for table `maintenance_performed`
--
DROP TABLE IF EXISTS `maintenance_performed`;
CREATE TABLE `maintenance_performed` (
`maintenance_performed_id` int(10) unsigned NOT NULL auto_increment,
`device_id` int(10) unsigned NOT NULL COMMENT 'The device this task is due on' references device(device_id),
`maintenance_task_id` int(10) unsigned NOT NULL COMMENT 'The task to be performed' references maintenance_task(maintenance_task_id),
`maintenance_date` datetime NOT NULL COMMENT 'Date and Time maintenance was performed',
`notes` text COMMENT 'Any comments/notes the maintainer wants to put in',
`login_id` int(10) unsigned NOT NULL COMMENT 'login id of the person performing the maintenance',
PRIMARY KEY (`maintenance_performed_id`)
) COMMENT='This holds the actual maintenance records';
--
-- Table structure for table `maintenance_task`
--
DROP TABLE IF EXISTS `maintenance_task`;
CREATE TABLE `maintenance_task` (
`maintenance_task_id` int(10) unsigned NOT NULL auto_increment,
`description` varchar(64) default NULL COMMENT 'A description of the task',
`default_period` int(11) default NULL COMMENT 'The default number of days between maintenance',
`notes` text COMMENT 'Detailed instructions on completing task',
`added_date` datetime default NULL,
`removed_date` datetime default NULL,
PRIMARY KEY (`maintenance_task_id`)
) COMMENT='Holds a list of tasks available to assign for maintnenance o';
/* create some keys */
alter table maintenance_performed add index (login_id);
alter table maintenance_performed add index (maintenance_task_id);
alter table maintenance_performed add index (maintenance_date);
alter table maintenance_performed add index (device_id);
alter table maintenance_task add index (removed_date);
alter table maintenance_schedule add index (removed_date);
alter table maintenance_schedule add index (login_id);
alter table maintenance_schedule add index (device_id);
alter table maintenance_schedule add index (maintenance_task_id);
insert into _system ( group_name,key_name,theValue,added_date) values ( 'Modules', 'Maintenance', 'modules/maintenance/', now() );
/* add maintenance report */
insert into _system( group_name,key_name,theValue,added_date ) values ( 'device view', 'Maintenance', 'callable.php:maintenanceView', now() );
insert into menu( url, caption, parent_id) values ('', 'Maintenance', null);
insert into menu( url,caption,parent_id) select '/modules/maintenance/do_maintenance.html','Scheduled Maintenance',menu_id from menu where caption = 'Maintenance';
insert into menu( url,caption,parent_id) select '/modules/maintenance/unscheduled_maintenance.html','Unscheduled Maintenance',menu_id from menu where caption = 'Maintenance';
insert into menu( url,caption,parent_id) select '/modules/maintenance/edit_schedule.html','Edit Schedules',menu_id from menu where caption = 'Maintenance';
/* Maintenance Due Report */
insert into report ( name, query, parameters ) values ('Maintenance Due','select concat( client.name, \' Site \', site.name ) \"Site\",
device.name \"Device Name\",
maintenance_task.description \"Task\",
DATEDIFF(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY), now()) \"Due\",
DATE(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY)) \"Date Due\",
login.email \"Technician\"
from maintenance_schedule join maintenance_performed on
maintenance_performed.maintenance_task_id = maintenance_schedule.maintenance_task_id
and maintenance_performed.device_id = maintenance_schedule.device_id
join login on login.login_id = maintenance_schedule.login_id
join maintenance_task on maintenance_task.maintenance_task_id = maintenance_schedule.maintenance_task_id
join device on maintenance_schedule.device_id = device.device_id
join site on device.site_id = site.site_id
join client on site.client_id = client.client_id
where device.removed_date is null
and site.removed_date is null
and client.removed_date is null
and maintenance_schedule.removed_date is null
and DATE(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY)) <= <maintenancedate>
and maintenance_performed.maintenance_performed_id in (
select max(maintenance_performed_id)
from maintenance_performed
group by device_id,maintenance_task_id
)
order by client.name,site.name,device.name, maintenance_task.description;
','maintenancedate++Report Date++++');
/* Maintenance Performed on a device */
insert into report ( name, query, parameters ) values ('Device Maintenance Record','select maintenance_task.description \"Task\",
DATE(maintenance_performed.maintenance_date) \"Date\",
maintenance_performed.notes \"Notes\",
login.email \"Technician\"
from maintenance_performed join device using (device_id)
join maintenance_task using (maintenance_task_id)
join login using (login_id)
join site using (site_id)
join client using (client_id)
where device.device_id = <device>','device++Choose Device++select distinct device.device_id, concat( client.name, \' - \',device.name) \"Device\"from device join site using (site_id) join client using (client_id) join maintenance_performed using (device_id)order by concat( client.name, \' - \',device.name)++select concat( client.name, \' - \',device.name) \"Device\" from device join site using (site_id) join client using (client_id)where device.device_id = <value>');
/* Maintenance performed in a period of time */
insert into report ( name, query, parameters ) values ('Maintenance Performed in Period','select client.name \"Client\",
device.name \"Device Name\",
maintenance_task.description \"Task\",
DATE(maintenance_performed.maintenance_date) \"Date\",
maintenance_performed.notes \"Notes\",
login.email \"Technician\"
from maintenance_performed join device on maintenance_performed.device_id = device.device_id
join maintenance_task on maintenance_task.maintenance_task_id = maintenance_performed.maintenance_task_id
join login on login.login_id = maintenance_performed.login_id
join site on device.site_id = site.site_id
join client on client.client_id = site.client_id
where DATE(maintenance_performed.maintenance_date) >= <start_date>
and DATE(maintenance_performed.maintenance_date) <= <end_date>
order by DATE(maintenance_performed.maintenance_date), client.name,site.name,device.name
','start_date++Starting Date++++
end_date++Ending Date++++');