| 1 |
rodolico |
1 |
--
|
|
|
2 |
-- Table structure for table `maintenance_schedule`
|
|
|
3 |
--
|
|
|
4 |
|
|
|
5 |
DROP TABLE IF EXISTS `maintenance_schedule`;
|
|
|
6 |
CREATE TABLE `maintenance_schedule` (
|
|
|
7 |
`maintenance_schedule_id` int(10) unsigned NOT NULL auto_increment,
|
|
|
8 |
`device_id` int(10) unsigned NOT NULL COMMENT 'The device this task is due on' references device(device_id),
|
|
|
9 |
`maintenance_task_id` int(10) unsigned NOT NULL COMMENT 'The task to be performed' references maintenance_task(maintenance_task_id),
|
|
|
10 |
`schedule` int(10) unsigned NOT NULL COMMENT 'The number of days between performance of this task',
|
|
|
11 |
`login_id` int(10) unsigned default NULL COMMENT 'Login ID of the persion who is generally responsible for this maintenance' references login(login_id),
|
|
|
12 |
`added_date` datetime default NULL,
|
|
|
13 |
`removed_date` datetime default NULL,
|
|
|
14 |
PRIMARY KEY (`maintenance_schedule_id`)
|
|
|
15 |
) COMMENT='Holds a list of maintenance to be performed on a device';
|
|
|
16 |
|
|
|
17 |
--
|
|
|
18 |
-- Table structure for table `maintenance_performed`
|
|
|
19 |
--
|
|
|
20 |
|
|
|
21 |
DROP TABLE IF EXISTS `maintenance_performed`;
|
|
|
22 |
CREATE TABLE `maintenance_performed` (
|
|
|
23 |
`maintenance_performed_id` int(10) unsigned NOT NULL auto_increment,
|
|
|
24 |
`device_id` int(10) unsigned NOT NULL COMMENT 'The device this task is due on' references device(device_id),
|
|
|
25 |
`maintenance_task_id` int(10) unsigned NOT NULL COMMENT 'The task to be performed' references maintenance_task(maintenance_task_id),
|
|
|
26 |
`maintenance_date` datetime NOT NULL COMMENT 'Date and Time maintenance was performed',
|
|
|
27 |
`notes` text COMMENT 'Any comments/notes the maintainer wants to put in',
|
|
|
28 |
`login_id` int(10) unsigned NOT NULL COMMENT 'login id of the person performing the maintenance',
|
|
|
29 |
PRIMARY KEY (`maintenance_performed_id`)
|
|
|
30 |
) COMMENT='This holds the actual maintenance records';
|
|
|
31 |
|
|
|
32 |
--
|
|
|
33 |
-- Table structure for table `maintenance_task`
|
|
|
34 |
--
|
|
|
35 |
|
|
|
36 |
DROP TABLE IF EXISTS `maintenance_task`;
|
|
|
37 |
CREATE TABLE `maintenance_task` (
|
|
|
38 |
`maintenance_task_id` int(10) unsigned NOT NULL auto_increment,
|
|
|
39 |
`description` varchar(64) default NULL COMMENT 'A description of the task',
|
|
|
40 |
`default_period` int(11) default NULL COMMENT 'The default number of days between maintenance',
|
|
|
41 |
`notes` text COMMENT 'Detailed instructions on completing task',
|
|
|
42 |
`added_date` datetime default NULL,
|
|
|
43 |
`removed_date` datetime default NULL,
|
|
|
44 |
PRIMARY KEY (`maintenance_task_id`)
|
|
|
45 |
) COMMENT='Holds a list of tasks available to assign for maintnenance o';
|
|
|
46 |
|
|
|
47 |
/* create some keys */
|
|
|
48 |
alter table maintenance_performed add index (login_id);
|
|
|
49 |
alter table maintenance_performed add index (maintenance_task_id);
|
|
|
50 |
alter table maintenance_performed add index (maintenance_date);
|
|
|
51 |
alter table maintenance_performed add index (device_id);
|
|
|
52 |
alter table maintenance_task add index (removed_date);
|
|
|
53 |
alter table maintenance_schedule add index (removed_date);
|
|
|
54 |
alter table maintenance_schedule add index (login_id);
|
|
|
55 |
alter table maintenance_schedule add index (device_id);
|
|
|
56 |
alter table maintenance_schedule add index (maintenance_task_id);
|
|
|
57 |
|
|
|
58 |
insert into _system ( group_name,key_name,theValue,added_date) values ( 'Modules', 'Maintenance', 'modules/maintenance/', now() );
|
|
|
59 |
|
|
|
60 |
insert into menu( url, caption, parent_id) values ('', 'Maintenance', null);
|
|
|
61 |
insert into menu( url,caption,parent_id) select '/modules/maintenance/do_maintenance.html','Scheduled Maintenance',menu_id from menu where caption = 'Maintenance';
|
|
|
62 |
insert into menu( url,caption,parent_id) select '/modules/maintenance/unscheduled_maintenance.html','Unscheduled Maintenance',menu_id from menu where caption = 'Maintenance';
|
|
|
63 |
insert into menu( url,caption,parent_id) select '/modules/maintenance/edit_schedule.html','Edit Schedules',menu_id from menu where caption = 'Maintenance';
|
|
|
64 |
|
|
|
65 |
|
|
|
66 |
/* Maintenance Due Report */
|
|
|
67 |
insert into report ( name, query, parameters ) values ('Maintenance Due','select concat( client.name, \' Site \', site.name ) \"Site\",
|
|
|
68 |
device.name \"Device Name\",
|
|
|
69 |
maintenance_task.description \"Task\",
|
|
|
70 |
DATEDIFF(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY), now()) \"Due\",
|
|
|
71 |
DATE(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY)) \"Date Due\",
|
|
|
72 |
login.email \"Technician\"
|
|
|
73 |
from maintenance_schedule join maintenance_performed on
|
|
|
74 |
maintenance_performed.maintenance_task_id = maintenance_schedule.maintenance_task_id
|
|
|
75 |
and maintenance_performed.device_id = maintenance_schedule.device_id
|
|
|
76 |
join login on login.login_id = maintenance_schedule.login_id
|
|
|
77 |
join maintenance_task on maintenance_task.maintenance_task_id = maintenance_schedule.maintenance_task_id
|
|
|
78 |
join device on maintenance_schedule.device_id = device.device_id
|
|
|
79 |
join site on device.site_id = site.site_id
|
|
|
80 |
join client on site.client_id = client.client_id
|
|
|
81 |
where device.removed_date is null
|
|
|
82 |
and site.removed_date is null
|
|
|
83 |
and client.removed_date is null
|
|
|
84 |
and maintenance_schedule.removed_date is null
|
|
|
85 |
and DATE(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY)) <= <maintenancedate>
|
|
|
86 |
and maintenance_performed.maintenance_performed_id in (
|
|
|
87 |
select max(maintenance_performed_id)
|
|
|
88 |
from maintenance_performed
|
|
|
89 |
group by device_id,maintenance_task_id
|
|
|
90 |
)
|
|
|
91 |
order by client.name,site.name,device.name, maintenance_task.description;
|
|
|
92 |
','maintenancedate++Report Date++++');
|
|
|
93 |
|
|
|
94 |
/* Maintenance Performed on a device */
|
|
|
95 |
|
|
|
96 |
insert into report ( name, query, parameters ) values ('Device Maintenance Record','select maintenance_task.description \"Task\",
|
|
|
97 |
DATE(maintenance_performed.maintenance_date) \"Date\",
|
|
|
98 |
maintenance_performed.notes \"Notes\",
|
|
|
99 |
login.email \"Technician\"
|
|
|
100 |
from maintenance_performed join device using (device_id)
|
|
|
101 |
join maintenance_task using (maintenance_task_id)
|
|
|
102 |
join login using (login_id)
|
|
|
103 |
join site using (site_id)
|
|
|
104 |
join client using (client_id)
|
|
|
105 |
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>');
|
|
|
106 |
|
|
|
107 |
/* Maintenance performed in a period of time */
|
|
|
108 |
|
|
|
109 |
insert into report ( name, query, parameters ) values ('Maintenance Performed in Period','select client.name \"Client\",
|
|
|
110 |
device.name \"Device Name\",
|
|
|
111 |
maintenance_task.description \"Task\",
|
|
|
112 |
DATE(maintenance_performed.maintenance_date) \"Date\",
|
|
|
113 |
maintenance_performed.notes \"Notes\",
|
|
|
114 |
login.email \"Technician\"
|
|
|
115 |
from maintenance_performed join device on maintenance_performed.device_id = device.device_id
|
|
|
116 |
join maintenance_task on maintenance_task.maintenance_task_id = maintenance_performed.maintenance_task_id
|
|
|
117 |
join login on login.login_id = maintenance_performed.login_id
|
|
|
118 |
join site on device.site_id = site.site_id
|
|
|
119 |
join client on client.client_id = site.client_id
|
|
|
120 |
where DATE(maintenance_performed.maintenance_date) >= <start_date>
|
|
|
121 |
and DATE(maintenance_performed.maintenance_date) <= <end_date>
|
|
|
122 |
order by DATE(maintenance_performed.maintenance_date), client.name,site.name,device.name
|
|
|
123 |
','start_date++Starting Date++++
|
|
|
124 |
end_date++Ending Date++++');
|