| 117 |
rodolico |
1 |
/*
|
|
|
2 |
Only list currently active maintenance
|
|
|
3 |
*/
|
|
|
4 |
drop view if exists view_active_maintenance;
|
|
|
5 |
create view view_active_maintenance as
|
|
|
6 |
select
|
|
|
7 |
device_name,
|
|
|
8 |
maintenance_schedule.device_id device_id,
|
|
|
9 |
maintenance_schedule.schedule schedule,
|
|
|
10 |
maintenance_schedule.login_id login_id,
|
|
|
11 |
maintenance_task.description description,
|
|
|
12 |
maintenance_task.notes task_notes
|
|
|
13 |
from
|
|
|
14 |
view_current_client_systems
|
|
|
15 |
join maintenance_schedule using ( device_id )
|
|
|
16 |
join maintenance_task using ( maintenance_task_id )
|
|
|
17 |
where
|
|
|
18 |
1=1
|
|
|
19 |
and maintenance_schedule.removed_date is null
|
|
|
20 |
and maintenance_task.removed_date is null;
|
|
|
21 |
|
|
|
22 |
|
|
|
23 |
/*
|
|
|
24 |
get all maintenance performed on all current devices
|
|
|
25 |
*/
|
|
|
26 |
drop view if exists view_maintenance_performed;
|
|
|
27 |
create view view_maintenance_performed as
|
|
|
28 |
select
|
|
|
29 |
device_name,
|
|
|
30 |
maintenance_schedule.device_id device_id,
|
|
|
31 |
maintenance_schedule.schedule schedule,
|
|
|
32 |
maintenance_schedule.login_id login_id,
|
|
|
33 |
date( maintenance_performed.maintenance_date ) maintenance_date,
|
|
|
34 |
maintenance_performed.notes notes,
|
|
|
35 |
maintenance_performed.login_id done_by,
|
|
|
36 |
maintenance_task.maintenance_task_id,
|
|
|
37 |
maintenance_task.description description,
|
|
|
38 |
maintenance_task.notes task_notes
|
|
|
39 |
from
|
|
|
40 |
view_current_client_systems
|
|
|
41 |
join maintenance_schedule using ( device_id )
|
|
|
42 |
join maintenance_performed using (maintenance_task_id, device_id )
|
|
|
43 |
join maintenance_task using ( maintenance_task_id );
|
|
|
44 |
|
|
|
45 |
|
|
|
46 |
/*
|
|
|
47 |
Most recent maintenance, and how long until next one is due
|
|
|
48 |
*/
|
|
|
49 |
drop view if exists view_last_maintenance_performed;
|
|
|
50 |
create view view_last_maintenance_performed as
|
|
|
51 |
select
|
|
|
52 |
date(max(maintenance_date)) last_performed,
|
|
|
53 |
device_id,
|
|
|
54 |
maintenance_task_id,
|
|
|
55 |
datediff( adddate( date(max(maintenance_date)), schedule), now() ) next_due
|
|
|
56 |
from
|
|
|
57 |
view_maintenance_performed
|
|
|
58 |
group by
|
|
|
59 |
device_id,
|
|
|
60 |
maintenance_task_id;
|
|
|
61 |
|