Subversion Repositories computer_asset_manager_v1

Rev

Rev 1 | Rev 38 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
1 rodolico 1 
<?php
2 
 
3 
   define(SQL_GET_PACKAGES,
4 
          'select client.name,device.name,software.package_name,software_version.version
5 
           from client join site on client.client_id = site.client_id
6 
                join device on device.site_id = site.site_id
7 
                join installed_packages on device.device_id = installed_packages.device_id
8 
                join software on installed_packages.software_id = software.software_id
9 
                join software_version on installed_packages.software_version_id = software_version.software_version_id
10 
           where <whereClause>
11 
           order by package_name,version
12 
          '
13 
   );
14 
 
15 
   define( SQL_AVAILABLE_PACKAGES,
16 
       'select distinct software.software_id,software.package_name
17 
        from installed_packages join software on installed_packages.software_id = software.software_id
18 
        where <whereClause>
19 
        order by software.package_name'
20 
   );
21 
 
22 
   define( SQL_DEVICE_LIST,
23 
      'select client.name,device.name,device.added_date \'Initial Date\', max(disk_space.added_date) \'Last Update\'
24 
        from client join site on site.client_id = client.client_id
25 
              join device on device.site_id = site.site_id
26 
              join device_type on device_type.device_type_id = device.device_type_id
27 
              join disk_info on disk_info.device_id = device.device_id
28 
              join disk_space on disk_space.disk_info_id = disk_info.disk_info_id
29 
        where <whereClause>
30 
        group by client.name,device.name,device.added_date
31 
        order by client.name,device.name
32 
    '
33 
   );
34 
 
35 
   define( SQL_AVAILABLE_DEVICES,
36 
      'select distinct device_id,device.name
37 
       from device join site on site.site_id=device.site_id
38 
            join client on client.client_id = site.client_id
39 
       where <whereClause>
40 
       order by device.name
41 
      '
42 
   );
43 
 
44 
   define ( SQL_SOFTWARE_UPDATES,
45 
      "select date(installed_packages.added_date) 'Added',
46 
              date(installed_packages.removed_date) 'Removed',
47 
              device.name 'Device',
48 
              package_name 'Package',
49 
              version 'Version'
50 
       from installed_packages join software on installed_packages.software_id = software.software_id
51 
          join software_version on installed_packages.software_version_id = software_version.software_version_id
52 
          join device on installed_packages.device_id = device.device_id
53 
          join site on device.site_id = site.site_id
54 
          join client on site.client_id = client.client_id
55 
       where <whereClause>
56 
       order by device.name,package_name,installed_packages.added_date
57 
      "
58 
   );
59 
 
60 
 
61 
   define (SQL_GET_DISKSPACE,
62 
           'select device.name,date(disk_space.added_date) \'Date Added\',  space_used/capacity*100 "used %", space_used/1048576 used, capacity/1048576 Part_Size, mount_point,  disk_device, filesystem
63 
       from device join disk_info on disk_info.device_id = device.device_id
64 
          join disk_space on disk_info.disk_info_id=disk_space.disk_info_id
65 
       where disk_info.removed_date is null
66 
            and device.removed_date is null
67 
            and disk_space.added_date = (select max(added_date) from disk_space b where b.disk_info_id = disk_space.disk_info_id)
68 
            and <whereClause>
69 
       order by device.name,disk_device
70 
      '
71 
   );
72 
 
73 
   define (SQL_DO_MAINTENANCE,
74 
      "select login.name 'Maintainer',
75 
            concat(client.name,'<br /> ',site.name) 'Site',
76 
            device.name 'Device',
77 
            date(date_add(maintenance_performed.maintenance_date,interval maintenance_schedule.schedule day)) 'Next Due',
78 
            concat('<INPUT type=\"checkbox\" name=\"',device.device_id,'-',maintenance_task.maintenance_task_id,'_done\"><b>Completed</b><br/>',maintenance_task.description) 'Work',
79 
            concat(
80 
            '<b>Date</b> <INPUT type=\"text\" name=\"',device.device_id,'-',maintenance_task.maintenance_task_id,'_date\" value=\"',date(now()),'\" size=\"10\" maxlength=\"10\">',
81 
            '<br /><textarea name=\"',device.device_id,'-',maintenance_task.maintenance_task_id,'_notes\" cols=\"20\" rows=\"4\"></textarea>') 'Notes'
82 
      from device join maintenance_schedule on device.device_id=maintenance_schedule.device_id
83 
            join maintenance_task on maintenance_task.maintenance_task_id = maintenance_schedule.maintenance_task_id
84 
            join maintenance_performed on maintenance_performed.device_id = maintenance_schedule.device_id
85 
               and maintenance_performed.maintenance_task_id = maintenance_schedule.maintenance_task_id
86 
            join login on maintenance_schedule.login_id = login.login_id
87 
            join site on site.site_id = device.site_id
88 
            join client on site.client_id = client.client_id
89 
      where device.removed_date is null
90 
            and maintenance_task.removed_date is null
91 
            and <additionalWhere>
92 
      order by login.email,client.name,site.name,device.name,maintenance_task.description"
93 
   );
94 
 
95 
   define (SQL_GET_MAINTAINERS,'
96 
      select distinct login.login_id,login.name
97 
      from maintenance_schedule join login on maintenance_schedule.login_id = login.login_id
98 
      '
99 
   );
100 
 
101 
   define (SQL_GET_MAINTENANCE_CLIENTS,'
102 
      select distinct client.client_id,client.name
103 
      from maintenance_schedule join device on maintenance_schedule.device_id = device.device_id
104 
         join site on device.site_id = site.site_id join client on site.client_id = client.client_id
105 
      '
106 
   );
107 
//            DATE(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY)) "Due Date",
108 
 
109 
   define (SQL_GET_OUTSTANDING_MAINTENANCE, '
110 
     select concat( client.name, \' Site \', site.name ) "Site",
111 
            device.name "Device Name",
112 
            device.notes "Device Notes",
37 rodolico 113 
            device.restrictions "Restrictions",
1 rodolico 114 
            maintenance_task.description "Task",
115 
            maintenance_task.notes "Task Notes",
116 
            DATEDIFF(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY), now()) "Due",
117 
            DATE(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY)) "Date Due",
118 
            login.email "Technician",
119 
            maintenance_schedule.maintenance_schedule_id "ID"
120 
      from maintenance_schedule join maintenance_performed on
121 
                  maintenance_performed.maintenance_task_id = maintenance_schedule.maintenance_task_id
122 
                  and maintenance_performed.device_id = maintenance_schedule.device_id
123 
         join login on login.login_id = maintenance_schedule.login_id
124 
         join maintenance_task on maintenance_task.maintenance_task_id = maintenance_schedule.maintenance_task_id
125 
         join device on maintenance_schedule.device_id = device.device_id
126 
         join site on device.site_id = site.site_id
127 
         join client on site.client_id = client.client_id
128 
         join (select max(maintenance_performed_id) last_maintenance from maintenance_performed group by device_id,maintenance_task_id) last_performed on maintenance_performed.maintenance_performed_id = last_performed.last_maintenance
129 
      where device.removed_date is null
130 
            and site.removed_date is null
131 
            and client.removed_date is null
132 
            and maintenance_schedule.removed_date is null
133 
            and <additionalWhere>
134 
     order by DATEDIFF(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY), now()),
135 
           client.name,site.name,device.name, maintenance_task.description;
136 
      '
137 
//            and DATEDIFF(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY), now()) <= 7
138 
   );
139 
 
140 
   define (SQL_MAINTENANCE_REPORT, '
141 
      select concat( client.name, \' - \', site.name) "Site",
142 
             device.name "Device Name",
143 
             maintenance_task.description "Task",
144 
             DATE(maintenance_performed.maintenance_date) "Date",
145 
             maintenance_performed.notes "Notes",
146 
             login.email "Technician"
147 
      from maintenance_performed join device on maintenance_performed.device_id = device.device_id
148 
         join maintenance_task on maintenance_task.maintenance_task_id = maintenance_performed.maintenance_task_id
149 
         join login on login.login_id = maintenance_performed.login_id
150 
         join site on device.site_id = site.site_id
151 
         join client on client.client_id = site.client_id
152 
      <whereClause>
153 
      <orderBy>
154 
   ');
155 
 
156 
   define (SQL_SHOW_maintenance_schedule, '
157 
      select maintenance_schedule.schedule "Period (d)",
158 
            maintenance_task.description "Task",
159 
            login.email "Technician",
160 
            max(date(maintenance_performed.maintenance_date)) "Last Done",
161 
            date(maintenance_schedule.added_date) "Added"
162 
      from maintenance_schedule join login on maintenance_schedule.login_id = login.login_id
163 
           join maintenance_task on maintenance_schedule.maintenance_task_id = maintenance_task.maintenance_task_id
164 
           join maintenance_performed on maintenance_schedule.device_id = maintenance_performed.device_id
165 
                and maintenance_schedule.maintenance_task_id = maintenance_performed.maintenance_task_id
166 
      where maintenance_schedule.device_id = <device_id>
167 
      group by maintenance_schedule.schedule,
168 
               maintenance_task.description,
169 
               login.email,
170 
               maintenance_schedule.added_date,
171 
               maintenance_schedule.removed_date
172 
   ');
173 
 
174 
?>