Rev 87 | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
<?php
include_once( './database.php' );
define(SQL_GET_PACKAGES,
'select client.name,device.name,software.package_name,software_version.version
from client join site on client.client_id = site.client_id
join device on device.site_id = site.site_id
join installed_packages on device.device_id = installed_packages.device_id
join software on installed_packages.software_id = software.software_id
join software_version on installed_packages.software_version_id = software_version.software_version_id
where <whereClause>
order by package_name,version
'
);
define( SQL_AVAILABLE_PACKAGES,
'select distinct software.software_id,software.package_name
from installed_packages join software on installed_packages.software_id = software.software_id
where <whereClause>
order by software.package_name'
);
define( SQL_DEVICE_LIST,
'select client.name,device.name,device.added_date \'Initial Date\', max(disk_space.added_date) \'Last Update\'
from client join site on site.client_id = client.client_id
join device on device.site_id = site.site_id
join device_type on device_type.device_type_id = device.device_type_id
join disk_info on disk_info.device_id = device.device_id
join disk_space on disk_space.disk_info_id = disk_info.disk_info_id
where <whereClause>
group by client.name,device.name,device.added_date
order by client.name,device.name
'
);
define( SQL_AVAILABLE_DEVICES,
'select distinct device_id,device.name
from device join site on site.site_id=device.site_id
join client on client.client_id = site.client_id
where <whereClause>
order by device.name
'
);
define ( SQL_SOFTWARE_UPDATES,
"select date(installed_packages.added_date) 'Added',
date(installed_packages.removed_date) 'Removed',
device.name 'Device',
package_name 'Package',
version 'Version'
from installed_packages join software on installed_packages.software_id = software.software_id
join software_version on installed_packages.software_version_id = software_version.software_version_id
join device on installed_packages.device_id = device.device_id
join site on device.site_id = site.site_id
join client on site.client_id = client.client_id
where <whereClause>
order by device.name,package_name,installed_packages.added_date
"
);
define (SQL_GET_DISKSPACE,
'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
from device join disk_info on disk_info.device_id = device.device_id
join disk_space on disk_info.disk_info_id=disk_space.disk_info_id
where disk_info.removed_date is null
and device.removed_date is null
and disk_space.added_date = (select max(added_date) from disk_space b where b.disk_info_id = disk_space.disk_info_id)
and <whereClause>
order by device.name,disk_device
'
);
define (SQL_DO_MAINTENANCE,
"select login.name 'Maintainer',
concat(client.name,'<br /> ',site.name) 'Site',
device.name 'Device',
date(date_add(maintenance_performed.maintenance_date,interval maintenance_schedule.schedule day)) 'Next Due',
concat('<INPUT type=\"checkbox\" name=\"',device.device_id,'-',maintenance_task.maintenance_task_id,'_done\"><b>Completed</b><br/>',maintenance_task.description) 'Work',
concat(
'<b>Date</b> <INPUT type=\"text\" name=\"',device.device_id,'-',maintenance_task.maintenance_task_id,'_date\" value=\"',date(now()),'\" size=\"10\" maxlength=\"10\">',
'<br /><textarea name=\"',device.device_id,'-',maintenance_task.maintenance_task_id,'_notes\" cols=\"20\" rows=\"4\"></textarea>') 'Notes'
from device join maintenance_schedule on device.device_id=maintenance_schedule.device_id
join maintenance_task on maintenance_task.maintenance_task_id = maintenance_schedule.maintenance_task_id
join maintenance_performed on maintenance_performed.device_id = maintenance_schedule.device_id
and maintenance_performed.maintenance_task_id = maintenance_schedule.maintenance_task_id
join login on maintenance_schedule.login_id = login.login_id
join site on site.site_id = device.site_id
join client on site.client_id = client.client_id
where device.removed_date is null
and maintenance_task.removed_date is null
and <additionalWhere>
order by login.email,client.name,site.name,device.name,maintenance_task.description"
);
define (SQL_GET_MAINTAINERS,'
select distinct login.login_id,login.name
from maintenance_schedule join login on maintenance_schedule.login_id = login.login_id
'
);
define (SQL_GET_MAINTENANCE_CLIENTS,'
select distinct client.client_id,client.name
from maintenance_schedule join device on maintenance_schedule.device_id = device.device_id
join site on device.site_id = site.site_id join client on site.client_id = client.client_id
'
);
// DATE(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY)) "Due Date",
define (SQL_GET_OUTSTANDING_MAINTENANCE, <<<EOD
select concat( view_client_site_device.Client, ' Site ', view_client_site_device.Site ) Site,
view_client_site_device.device_id "Device ID",
Device "Device Name",
device_notes "Device Notes",
device_restrictions "Restrictions",
maintenance_task.description "Task",
maintenance_task.notes "Task Notes",
DATEDIFF(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY), now()) "Due",
DATE(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY)) "Date Due",
login.email "Technician",
maintenance_schedule.maintenance_schedule_id "ID"
from
maintenance_schedule
join maintenance_performed on
maintenance_performed.maintenance_task_id = maintenance_schedule.maintenance_task_id
and maintenance_performed.device_id = maintenance_schedule.device_id
join login on login.login_id = maintenance_schedule.login_id
join maintenance_task on maintenance_task.maintenance_task_id = maintenance_schedule.maintenance_task_id
join view_client_site_device on (view_client_site_device.device_id = maintenance_schedule.device_id )
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
where maintenance_schedule.removed_date is null
and <additionalWhere>
order by
Client,
Site,
Device,
maintenance_task.description
EOD
// DATEDIFF(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY), now()),
);
define (SQL_MAINTENANCE_REPORT, '
select concat( client.name, \' - \', site.name) "Site",
device.name "Device Name",
maintenance_task.description "Task",
DATE(maintenance_performed.maintenance_date) "Date",
maintenance_performed.notes "Notes",
login.email "Technician"
from maintenance_performed join device on maintenance_performed.device_id = device.device_id
join maintenance_task on maintenance_task.maintenance_task_id = maintenance_performed.maintenance_task_id
join login on login.login_id = maintenance_performed.login_id
join site on device.site_id = site.site_id
join client on client.client_id = site.client_id
<whereClause>
<orderBy>
');
define (SQL_SHOW_maintenance_schedule, '
select maintenance_schedule.schedule "Period (d)",
maintenance_task.description "Task",
login.email "Technician",
max(date(maintenance_performed.maintenance_date)) "Last Done",
date(maintenance_schedule.added_date) "Added"
from maintenance_schedule join login on maintenance_schedule.login_id = login.login_id
join maintenance_task on maintenance_schedule.maintenance_task_id = maintenance_task.maintenance_task_id
join maintenance_performed on maintenance_schedule.device_id = maintenance_performed.device_id
and maintenance_schedule.maintenance_task_id = maintenance_performed.maintenance_task_id
where maintenance_schedule.device_id = <device_id>
group by maintenance_schedule.schedule,
maintenance_task.description,
login.email,
maintenance_schedule.added_date,
maintenance_schedule.removed_date
');
?>