Annual Cleanup - Dewey

/*

call number dewey / generic clean up

*/

/* first, find the call number ids to be cleaned up by using using names of shelving locations
shelving locations -> copy -> call number

1 generic
2 dewey
3 library of congress

so we want to ignore those that are already dewey which will make it easier to do smaller cleanups in the future

 */

drop table if exists rogan.dewey_cleanup_acns;

create table rogan.dewey_cleanup_acns as
select acn.id
from (
    select aou.shortname, acl.name, acl.id as acl_id
    from asset.copy_location acl
    join actor.org_unit aou on aou.id = acl.owning_lib
    where acl.name ilike '%non%' and acl.name ilike '%fiction%'
    and acl.name not ilike '%dvd%' and acl.name not ilike '%cd%'
    and acl.name not ilike '%audio%' and acl.name not ilike '%video%'
    ) a
join asset.copy ac on ac.location = a.acl_id
join asset.call_number acn on acn.id = ac.call_number
where acn.label_class != 2
;

select count(id) from rogan.dewey_cleanup_acns;

alter table rogan.dewey_cleanup_acns add column converted boolean default false;

update asset.call_number set label_class = 2 where id in
    (select id from rogan.dewey_cleanup_acns where converted = false order by id limit 20000);
update rogan.dewey_cleanup_acns set converted = true where id in
    (select id from rogan.dewey_cleanup_acns where converted = false order by id limit 20000);

select count(id) from rogan.dewey_cleanup_acns where converted = TRUE;


drop table if exists rogan.dewey_cleanup_acns;