Subversion Repositories computer_asset_manager_v1

/* do some housecleaning. This sync's the original tags with what we use in the new database */
update camp.device_type set name = 'Virtual' where name = 'Xen Virtual';
update camp.device_type set name = 'PCI Card' where name = 'PCI Periphial';
update camp.device_type set name = 'Switch' where name = 'Network Switch';

/* get some device types not in the new database */
insert into camp_test.device_type (name,added_date,removed_date,show_as_system) 
   select name,added_date,removed_date,show_as_system 
   from camp.device_type 
   where name not in (select name from camp_test.device_type);

/* copy any devices that don't exist in the new database into it */

insert into device (site_id,device_type_id,name,notes,added_date,removed_date)
select min_site.default_site_id, 
       device_type.device_type_id, 
       to_be_updated.devicename, 
       to_be_updated.notes, 
       to_be_updated.added_date,
       to_be_updated.removed_date  
from
 camp_test.client, 
 camp_test.device_type, 
 (  
   select client_id,min(site_id) default_site_id
   from site 
   group by client_id
 ) min_site,
 (
   select device.name devicename,device.notes, device.added_date,device.removed_date,client.name clientname,device_type.name devicetypename
   from camp.device join camp.site using (site_id) join camp.client using (client_id) join camp.device_type using (device_type_id)
   where device.name not like 'HASH%' 
         and device.name not like '[memory%'
         and device.name not in
            ( 
            select device.name from camp_test.device join camp_test.device_type using (device_type_id) where device_type.show_as_system = 'Y'
            union
            select alias from device_alias
            )
) to_be_updated
where client.name = to_be_updated.clientname
      and device_type.name = to_be_updated.devicetypename
      and min_site.client_id = client.client_id
;

/* we need login populated for the maintenance schedule */
insert into camp_test.login (email,pass,where_clause,added_date,removed_date)
select email,pass,where_clause,added_date,removed_date from camp.login where email not in (select email from camp_test.login);

/* get all the maintenance tasks */
insert into camp_test.maintenance_task(description, default_period,notes,added_date,removed_date) 
     select description, default_period,notes,added_date,removed_date from camp.maintenance_task;


/* now, get the maintenance schedules in */
insert into maintenance_schedule (device_id,maintenance_task_id,schedule,login_id,added_date,removed_date)
select deviceinfo.device_id,maintenance_task.maintenance_task_id,oldtasks.schedule,login.login_id,oldtasks.added_date,oldtasks.removed_date
from camp_test.client join camp_test.site using (client_id),
      (select device_alias.alias name, device_alias.device_id device_id, device.site_id site_id from device_alias join device using (device_id) join site using (site_id)
      union
      select device.name name, device.device_id device_id, device.site_id site_id from device join device_type using (device_type_id) where device_type.show_as_system = 'Y'
      )  deviceinfo,
     camp_test.login,
     camp_test.maintenance_task,
     (
      select camp.device.name devicename,
            camp.client.name clientname,
            camp.maintenance_task.description task,
            camp.device_maintenance_schedule.schedule,
            camp.login.email loginname,
            camp.device_maintenance_schedule.added_date,
            camp.device_maintenance_schedule.removed_date
      from camp.device_maintenance_schedule 
         join camp.maintenance_task using (maintenance_task_id)
         join camp.login using (login_id)
         join camp.device using (device_id)
         join camp.site using (site_id)
         join camp.client using (client_id)
     ) oldtasks
where camp_test.client.client_id = camp_test.site.client_id
      and camp_test.site.site_id = deviceinfo.site_id
      and deviceinfo.name = oldtasks.devicename
      and camp_test.login.email = oldtasks.loginname
      and camp_test.maintenance_task.description = oldtasks.task
;

/* finally, get the maintenance actually performed */

insert into camp_test.maintenance_performed(device_id,maintenance_task_id,maintenance_date,notes,login_id)
select deviceinfo.device_id,maintenance_task.maintenance_task_id,oldtasks.maintenance_date,oldtasks.notes,login.login_id
from camp_test.client,
     camp_test.site,
      (select device_alias.alias name, device_alias.device_id device_id, device.site_id site_id from device_alias join device using (device_id) join site using (site_id)
      union
      select device.name name, device.device_id device_id, device.site_id site_id from device join device_type using (device_type_id) where device_type.show_as_system = 'Y'
      )  deviceinfo,
     camp_test.login,
     camp_test.maintenance_task,
     (
      select camp.device.name devicename,
            camp.client.name clientname,
            camp.maintenance_task.description task,
            camp.login.email loginname,
            camp.maintenance_performed.maintenance_date maintenance_date,
            camp.maintenance_performed.notes notes
      from camp.maintenance_performed 
         join camp.maintenance_task using (maintenance_task_id)
         join camp.login using (login_id)
         join camp.device using (device_id)
         join camp.site using (site_id)
         join camp.client using (client_id)
     ) oldtasks
where camp_test.client.client_id = camp_test.site.client_id
      and camp_test.site.site_id = deviceinfo.site_id
      and deviceinfo.name = oldtasks.devicename
      and camp_test.login.email = oldtasks.loginname
      and camp_test.maintenance_task.description = oldtasks.task
;

select count(*) from camp_test.maintenance_schedule;
select count(*) from camp.device_maintenance_schedule;
select count(*) from camp_test.maintenance_performed;
select count(*) from camp.maintenance_performed;