Subversion Repositories computer_asset_manager_v1

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
1 rodolico 1 
/* SQL_DO_MAINTENANCE */
2 
select login.name 'Maintainer',
3 
      concat(client.name,'<br /> ',site.name) 'Site',
4 
      device.name 'Device',
5 
      date(date_add(maintenance_performed.maintenance_date,interval device_maintenance_schedule.schedule day)) 'Next Due',
6 
      concat('<INPUT type=\"checkbox\" name=\"',device.device_id,'-',maintenance_task.maintenance_task_id,'_done\"><b>Completed</b><br/>',maintenance_task.description
7 
      concat(
8 
      '<b>Date</b> <INPUT type=\"text\" name=\"',device.device_id,'-',maintenance_task.maintenance_task_id,'_date\" value=\"',date(now()),'\" size=\"10\" maxlength=\"'
9 
      '<br /><textarea name=\"',device.device_id,'-',maintenance_task.maintenance_task_id,'_notes\" cols=\"20\" rows=\"4\"></textarea>') 'Notes'
10 
from device join device_maintenance_schedule on device.device_id=device_maintenance_schedule.device_id
11 
      join maintenance_task on maintenance_task.maintenance_task_id = device_maintenance_schedule.maintenance_task_id
12 
      join maintenance_performed on maintenance_performed.device_id = device_maintenance_schedule.device_id
13 
         and maintenance_performed.maintenance_task_id = device_maintenance_schedule.maintenance_task_id
14 
      join login on device_maintenance_schedule.login_id = login.login_id
15 
      join site on site.site_id = device.site_id
16 
      join client on site.client_id = client.client_id
17 
where device.removed_date is null
18 
      and maintenance_task.removed_date is null
19 
      and <additionalWhere>
20 
order by login.email,client.name,site.name,device.name,maintenance_task.description
21 
 
22 
 
23 
/* SQL_GET_MAINTAINERS */
24 
select distinct login.login_id,login.name
25 
from device_maintenance_schedule join login on device_maintenance_schedule.login_id = login.login_id
26 
 
27 
/* SQL_GET_MAINTENANCE_CLIENTS */
28 
select distinct client.client_id,client.name
29 
from device_maintenance_schedule join device on device_maintenance_schedule.device_id = device.device_id
30 
   join site on device.site_id = site.site_id join client on site.client_id = client.client_id
31 
 
32 
 
33 
/* SQL_GET_OUTSTANDING_MAINTENANCE */
34 
select concat( client.name, \' Site \', site.name ) "Site",
35 
      device.name "Device Name",
36 
      device.notes "Device Notes",
37 
      maintenance_task.description "Task",
38 
      maintenance_task.notes "Task Notes",
39 
      DATEDIFF(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY), now()) "Due",
40 
      DATE(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY)) "Date Due",
41 
      login.email "Technician",
42 
      device_maintenance_schedule.device_maintenance_schedule_id "ID"
43 
from device_maintenance_schedule join maintenance_performed on
44 
            maintenance_performed.maintenance_task_id = device_maintenance_schedule.maintenance_task_id
45 
            and maintenance_performed.device_id = device_maintenance_schedule.device_id
46 
   join login on login.login_id = device_maintenance_schedule.login_id
47 
   join maintenance_task on maintenance_task.maintenance_task_id = device_maintenance_schedule.maintenance_task_id
48 
   join device on device_maintenance_schedule.device_id = device.device_id
49 
   join site on device.site_id = site.site_id
50 
   join client on site.client_id = client.client_id
51 
where device.removed_date is null
52 
      and site.removed_date is null
53 
      and client.removed_date is null
54 
      and device_maintenance_schedule.removed_date is null
55 
      and maintenance_performed.maintenance_performed_id in (
56 
               select max(maintenance_performed_id)
57 
               from maintenance_performed
58 
               group by device_id,maintenance_task_id
59 
            )
60 
      and <additionalWhere>
61 
order by client.name,site.name,device.name, maintenance_task.description;
62 
 
63 
/* SQL_MAINTENANCE_REPORT */
64 
select concat( client.name, \' - \', site.name) "Site",
65 
         device.name "Device Name",
66 
         maintenance_task.description "Task",
67 
         DATE(maintenance_performed.maintenance_date) "Date",
68 
         maintenance_performed.notes "Notes",
69 
         login.email "Technician"
70 
from maintenance_performed join device on maintenance_performed.device_id = device.device_id
71 
   join maintenance_task on maintenance_task.maintenance_task_id = maintenance_performed.maintenance_task_id
72 
   join login on login.login_id = maintenance_performed.login_id
73 
   join site on device.site_id = site.site_id
74 
   join client on client.client_id = site.client_id
75 
<whereClause>
76 
<orderBy>
77 
 
78 
/* SQL_SHOW_DEVICE_MAINTENANCE_SCHEDULE */
79 
select device_maintenance_schedule.schedule "Period (d)",
80 
      maintenance_task.description "Task",
81 
      login.email "Technician",
82 
      max(date(maintenance_performed.maintenance_date)) "Last Done",
83 
      date(device_maintenance_schedule.added_date) "Added"
84 
from device_maintenance_schedule join login on device_maintenance_schedule.login_id = login.login_id
85 
      join maintenance_task on device_maintenance_schedule.maintenance_task_id = maintenance_task.maintenance_task_id
86 
      join maintenance_performed on device_maintenance_schedule.device_id = maintenance_performed.device_id
87 
            and device_maintenance_schedule.maintenance_task_id = maintenance_performed.maintenance_task_id
88 
where device_maintenance_schedule.device_id = <device_id>
89 
group by device_maintenance_schedule.schedule,
90 
         maintenance_task.description,
91 
         login.email,
92 
         device_maintenance_schedule.added_date,
93 
         device_maintenance_schedule.removed_date
94