Subversion Repositories computer_asset_manager_v1

/* 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 device_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
      concat(
      '<b>Date</b> <INPUT type=\"text\" name=\"',device.device_id,'-',maintenance_task.maintenance_task_id,'_date\" value=\"',date(now()),'\" size=\"10\" maxlength=\"' 
      '<br /><textarea name=\"',device.device_id,'-',maintenance_task.maintenance_task_id,'_notes\" cols=\"20\" rows=\"4\"></textarea>') 'Notes'
from device join device_maintenance_schedule on device.device_id=device_maintenance_schedule.device_id
      join maintenance_task on maintenance_task.maintenance_task_id = device_maintenance_schedule.maintenance_task_id
      join maintenance_performed on maintenance_performed.device_id = device_maintenance_schedule.device_id
         and maintenance_performed.maintenance_task_id = device_maintenance_schedule.maintenance_task_id   
      join login on device_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

   
/* SQL_GET_MAINTAINERS */
select distinct login.login_id,login.name 
from device_maintenance_schedule join login on device_maintenance_schedule.login_id = login.login_id
   
/* SQL_GET_MAINTENANCE_CLIENTS */
select distinct client.client_id,client.name 
from device_maintenance_schedule join device on device_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
   

/* SQL_GET_OUTSTANDING_MAINTENANCE */
select concat( client.name, \' Site \', site.name ) "Site",
      device.name "Device Name",
      device.notes "Device Notes",
      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",
      device_maintenance_schedule.device_maintenance_schedule_id "ID"
from device_maintenance_schedule join maintenance_performed on 
            maintenance_performed.maintenance_task_id = device_maintenance_schedule.maintenance_task_id
            and maintenance_performed.device_id = device_maintenance_schedule.device_id
   join login on login.login_id = device_maintenance_schedule.login_id
   join maintenance_task on maintenance_task.maintenance_task_id = device_maintenance_schedule.maintenance_task_id
   join device on device_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
where device.removed_date is null
      and site.removed_date is null
      and client.removed_date is null
      and device_maintenance_schedule.removed_date is null
      and maintenance_performed.maintenance_performed_id in (
               select max(maintenance_performed_id) 
               from maintenance_performed 
               group by device_id,maintenance_task_id
            )
      and <additionalWhere>
order by client.name,site.name,device.name, maintenance_task.description;
   
/* 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>
   
/* SQL_SHOW_DEVICE_MAINTENANCE_SCHEDULE */
select device_maintenance_schedule.schedule "Period (d)",
      maintenance_task.description "Task",
      login.email "Technician",
      max(date(maintenance_performed.maintenance_date)) "Last Done",
      date(device_maintenance_schedule.added_date) "Added"
from device_maintenance_schedule join login on device_maintenance_schedule.login_id = login.login_id 
      join maintenance_task on device_maintenance_schedule.maintenance_task_id = maintenance_task.maintenance_task_id
      join maintenance_performed on device_maintenance_schedule.device_id = maintenance_performed.device_id 
            and device_maintenance_schedule.maintenance_task_id = maintenance_performed.maintenance_task_id  
where device_maintenance_schedule.device_id = <device_id>
group by device_maintenance_schedule.schedule,
         maintenance_task.description,
         login.email,
         device_maintenance_schedule.added_date,
         device_maintenance_schedule.removed_date