Subversion Repositories computer_asset_manager_v1

/*
   Only list currently active maintenance
*/
drop view if exists view_active_maintenance;
create view view_active_maintenance as
   select
      device_name, 
      maintenance_schedule.device_id device_id,
      maintenance_schedule.schedule schedule,
      maintenance_schedule.login_id login_id,
      maintenance_task.description description,
      maintenance_task.notes task_notes
   from
      view_current_client_systems
      join maintenance_schedule using ( device_id )
      join maintenance_task using ( maintenance_task_id )
   where
      1=1
      and maintenance_schedule.removed_date is null
      and maintenance_task.removed_date is null;


/*
   get all maintenance performed on all current devices
*/
drop view if exists view_maintenance_performed;
create view view_maintenance_performed as
   select
      device_name, 
      maintenance_schedule.device_id device_id,
      maintenance_schedule.schedule schedule,
      maintenance_schedule.login_id login_id,
      date( maintenance_performed.maintenance_date ) maintenance_date,
      maintenance_performed.notes notes,
      maintenance_performed.login_id done_by,
      maintenance_task.maintenance_task_id,
      maintenance_task.description description,
      maintenance_task.notes task_notes
   from
      view_current_client_systems
      join maintenance_schedule using ( device_id )
      join maintenance_performed using (maintenance_task_id, device_id )
      join maintenance_task using ( maintenance_task_id );


/*
   Most recent maintenance, and how long until next one is due
*/
drop view if exists view_last_maintenance_performed;
create view view_last_maintenance_performed as
   select 
      date(max(maintenance_date)) last_performed, 
      device_id,
      maintenance_task_id,
      datediff( adddate( date(max(maintenance_date)), schedule), now() ) next_due
   from 
      view_maintenance_performed 
   group by 
      device_id,
      maintenance_task_id;