Subversion Repositories computer_asset_manager_v1

Rev

Rev 87 | Details | Compare with Previous | Last modification | View Log | RSS feed

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