Subversion Repositories computer_asset_manager_v1

Rev

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++++');