Newly arrived items, and newly ordered items lists

update-new-items2.sql

begin;

\set regioncode 101

--- Temp table to hold the last 5 months of new titles
CREATE TEMPORARY TABLE new_items
ON COMMIT DROP
AS (
-- New Items
select row_number() over (order by x."Most Recent Active Date" desc nulls last) as rownum, * from (
select rssr.id bibid, rssr.title, rssr.author
--, concat_ws(' ',acnp.label, acn.label) "Call Number"
, rssr.pubdate, acp.circ_modifier
, string_agg(distinct aou.shortname, ', ') "Branches", count(acp.id) "Copy Count"
, string_agg(distinct ccs.name,', ') "Copy Status", max(acp.active_date) "Most Recent Active Date"
, string_agg(distinct acl.name,', ') "Shelving Locations"
, string_agg(distinct asce21.value, ', ') as "Book Genre"
, string_agg(distinct asce20.value, ', ') as "Special Collections"
, string_agg(distinct asce19.value, ', ') as "Fiction/NF"
, string_agg(distinct asce18.value, ', ') as "Format"
, string_agg(distinct asce17.value, ', ') as "Reading Watching Level"
, string_agg(distinct asce16.value, ', ') as "Genre Music"
, string_agg(distinct asce15.value, ', ') as "Genre Movie"
from asset.copy acp
join actor.org_unit aou on aou.id=acp.circ_lib
join config.copy_status ccs on ccs.id=acp.status
join asset.call_number acn on acn.id=acp.call_number
left outer join asset.call_number_prefix acnp on acnp.id=acn.prefix and acnp.id!=(-1)
join biblio.record_entry bre on acn.record=bre.id
join reporter.super_simple_record rssr on rssr.id=bre.id
join asset.copy_location acl on acl.id=acp.location
-- 15 = Movie Genre
left outer join asset.stat_cat_entry_copy_map ascecm15 on ascecm15.owning_copy=acp.id and ascecm15.stat_cat=15
left outer join asset.stat_cat_entry asce15 on asce15.id=ascecm15.stat_cat_entry

-- 16 = Music Genre
left outer join asset.stat_cat_entry_copy_map ascecm16 on ascecm16.owning_copy=acp.id and ascecm16.stat_cat=16
left outer join asset.stat_cat_entry asce16 on asce16.id=ascecm16.stat_cat_entry

-- 17 = Reading Level
left outer join asset.stat_cat_entry_copy_map ascecm17 on ascecm17.owning_copy=acp.id and ascecm17.stat_cat=17
left outer join asset.stat_cat_entry asce17 on asce17.id=ascecm17.stat_cat_entry

-- 18 = Format
left outer join asset.stat_cat_entry_copy_map ascecm18 on ascecm18.owning_copy=acp.id and ascecm18.stat_cat=18
left outer join asset.stat_cat_entry asce18 on asce18.id=ascecm18.stat_cat_entry

-- 19 = Fiction/NF
left outer join asset.stat_cat_entry_copy_map ascecm19 on ascecm19.owning_copy=acp.id and ascecm19.stat_cat=19
left outer join asset.stat_cat_entry asce19 on asce19.id=ascecm19.stat_cat_entry

-- 20 - Special Collections
left outer join asset.stat_cat_entry_copy_map ascecm20 on ascecm20.owning_copy=acp.id and ascecm20.stat_cat=20
left outer join asset.stat_cat_entry asce20 on asce20.id=ascecm20.stat_cat_entry

-- 21 - Book Genre
left outer join asset.stat_cat_entry_copy_map ascecm21 on ascecm21.owning_copy=acp.id and ascecm21.stat_cat=21
left outer join asset.stat_cat_entry asce21 on asce21.id=ascecm21.stat_cat_entry

where
acp.circ_lib in (SELECT id FROM actor.org_unit_descendants(:regioncode))
and acp.active_date > now()-'5 months'::interval
-- and acp.location=122
and acp.status in (0,1,6,8,5)
--and acp.circ_modifier='Large Print Book'
and acp.circ_modifier not in ('Magazine','ILL')
and not acp.deleted
and acp.opac_visible

group by 1,2,3,4,5
order by 9 desc
limit 4000
) x
)
;

-- New Arrivals - Newest Arrivals for the Lake Agassize Regional Library System. Updated daily.
\set list 5317

--Clear out old entries
delete from container.biblio_record_entry_bucket_item where bucket=:list;

insert into container.biblio_record_entry_bucket_item (pos,bucket,target_biblio_record_entry)
select ni.rownum pos, :list bucket, ni.bibid target_biblio_record_entry from
new_items ni
where
ni."Shelving Locations"~'New'

limit 200
;


-- New Fiction Books - Newest Arrivals for the Lake Agassize Regional Library System. Updated daily.
\set list 87792

--Clear out old entries
delete from container.biblio_record_entry_bucket_item where bucket=:list;

insert into container.biblio_record_entry_bucket_item (pos,bucket,target_biblio_record_entry)
select ni.rownum pos, :list bucket, ni.bibid target_biblio_record_entry from
new_items ni
where
ni."Shelving Locations"~'New'
and ni."Format" = 'Book'
and ni."Fiction/NF" = 'Fiction'
limit 200
;

-- New Non-Fiction Books - Newest Arrivals for the Lake Agassize Regional Library System. Updated daily.
\set list 87793

--Clear out old entries
delete from container.biblio_record_entry_bucket_item where bucket=:list;

insert into container.biblio_record_entry_bucket_item (pos,bucket,target_biblio_record_entry)
select ni.rownum pos, :list bucket, ni.bibid target_biblio_record_entry from
new_items ni
where
ni."Shelving Locations"~'New'
and ni."Format" = 'Book'
and ni."Fiction/NF" = 'Non-Fiction'
limit 200
;


-- New Young Adult - youth - bibid=30459
\set list 30459

 -- Clear out old entries
delete from container.biblio_record_entry_bucket_item where bucket=:list;

insert into container.biblio_record_entry_bucket_item (pos,bucket,target_biblio_record_entry)
select ni.rownum pos, :list bucket, ni.bibid target_biblio_record_entry from
new_items ni
where
ni."Shelving Locations"~'New'
and ni."Format" = 'Book'
and ni."Reading Watching Level" = 'Youth'

limit 200
;


-- New Children's - Juv, bibid=30460
\set list 30460

 -- Clear out old entries
delete from container.biblio_record_entry_bucket_item where bucket=:list;

insert into container.biblio_record_entry_bucket_item (pos,bucket,target_biblio_record_entry)
select ni.rownum pos, :list bucket, ni.bibid target_biblio_record_entry from
new_items ni
where
ni."Shelving Locations"~'New'
and ni."Format" = 'Book'
and ni."Reading Watching Level" = 'Juvenile'

limit 200
;


-- New DVDs - DVDs that have recently arrived at Lake Agassiz Regional Library branches.
-- Updated Daily. Includes newly purchased copies of older DVD titles.
\set list 5318

delete from container.biblio_record_entry_bucket_item where bucket=:list;

insert into container.biblio_record_entry_bucket_item (pos,bucket,target_biblio_record_entry)
select ni.rownum pos, :list bucket, ni.bibid target_biblio_record_entry from
new_items ni
where
--ni."Shelving Locations"~'New'
--and
ni.circ_modifier='DVD'

limit 200
;


--New Audiobooks - Audiobook that have arrived at Lake Agassiz Regional Library branches.
-- Updated Daily.
\echo new audiobooks that have arrived
\set list 5319

delete from container.biblio_record_entry_bucket_item where bucket=:list;

insert into container.biblio_record_entry_bucket_item (pos,bucket,target_biblio_record_entry)
select ni.rownum pos, :list bucket, ni.bibid target_biblio_record_entry from
new_items ni
where
ni.circ_modifier='Audiobook'

limit 100
;

--New Books and Audiobooks - combined list

\set list 80981

delete from container.biblio_record_entry_bucket_item where bucket=:list;

insert into container.biblio_record_entry_bucket_item (pos,bucket,target_biblio_record_entry)
select ni.rownum pos, :list bucket, ni.bibid target_biblio_record_entry from
new_items ni
where
ni.circ_modifier='Audiobook'
or (ni.circ_modifier='Book' and ni."Shelving Locations"~'New' )

limit 300
;


-- DVD Adult Fiction
-- DVD Juvenile Fiction
-- DVD TV Series
-- DVD Non Fiction


-- Large Print
--

commit;


-- ##### On Order Copies
begin;

-- LARL On Order Copies
CREATE TEMPORARY TABLE on_order_items
ON COMMIT DROP
AS (
select row_number() over (order by x."Most Recent Create Date" desc nulls last) as rownum, * from (
select rssr.id bibid, rssr.title, rssr.author
--, concat_ws(' ',acnp.label, acn.label) "Call Number"
, rssr.pubdate, acp.circ_modifier
, string_agg(distinct aou.shortname, ', ') "Branches", count(acp.id) "Copy Count"
, string_agg(distinct ccs.name,', ') "Copy Status", max(acp.create_date) "Most Recent Create Date"
, string_agg(distinct acl.name,', ') "Shelving Locations"
, string_agg(distinct asce21.value, ', ') as "Book Genre"
, string_agg(distinct asce20.value, ', ') as "Special Collections"
, string_agg(distinct asce19.value, ', ') as "Fiction/NF"
, string_agg(distinct asce18.value, ', ') as "Format"
, string_agg(distinct asce17.value, ', ') as "Reading Watching Level"
, string_agg(distinct asce16.value, ', ') as "Genre Music"
, string_agg(distinct asce15.value, ', ') as "Genre Movie"
from asset.copy acp
join actor.org_unit aou on aou.id=acp.circ_lib
join config.copy_status ccs on ccs.id=acp.status
join asset.call_number acn on acn.id=acp.call_number
left outer join asset.call_number_prefix acnp on acnp.id=acn.prefix and acnp.id!=(-1)
join biblio.record_entry bre on acn.record=bre.id
join reporter.super_simple_record rssr on rssr.id=bre.id
join asset.copy_location acl on acl.id=acp.location
-- 15 = Movie Genre
left outer join asset.stat_cat_entry_copy_map ascecm15 on ascecm15.owning_copy=acp.id and ascecm15.stat_cat=15
left outer join asset.stat_cat_entry asce15 on asce15.id=ascecm15.stat_cat_entry

-- 16 = Music Genre
left outer join asset.stat_cat_entry_copy_map ascecm16 on ascecm16.owning_copy=acp.id and ascecm16.stat_cat=16
left outer join asset.stat_cat_entry asce16 on asce16.id=ascecm16.stat_cat_entry

-- 17 = Reading Level
left outer join asset.stat_cat_entry_copy_map ascecm17 on ascecm17.owning_copy=acp.id and ascecm17.stat_cat=17
left outer join asset.stat_cat_entry asce17 on asce17.id=ascecm17.stat_cat_entry

-- 18 = Format
left outer join asset.stat_cat_entry_copy_map ascecm18 on ascecm18.owning_copy=acp.id and ascecm18.stat_cat=18
left outer join asset.stat_cat_entry asce18 on asce18.id=ascecm18.stat_cat_entry

-- 19 = Fiction/NF
left outer join asset.stat_cat_entry_copy_map ascecm19 on ascecm19.owning_copy=acp.id and ascecm19.stat_cat=19
left outer join asset.stat_cat_entry asce19 on asce19.id=ascecm19.stat_cat_entry

-- 20 - Special Collections
left outer join asset.stat_cat_entry_copy_map ascecm20 on ascecm20.owning_copy=acp.id and ascecm20.stat_cat=20
left outer join asset.stat_cat_entry asce20 on asce20.id=ascecm20.stat_cat_entry

-- 21 - Book Genre
left outer join asset.stat_cat_entry_copy_map ascecm21 on ascecm21.owning_copy=acp.id and ascecm21.stat_cat=21
left outer join asset.stat_cat_entry asce21 on asce21.id=ascecm21.stat_cat_entry

where
acp.circ_lib in (SELECT id FROM actor.org_unit_descendants(101))
and not acp.deleted
--and acp.active_date > now()-'5 months'::interval
-- and acp.location=122
and acp.status in (5,9)
--and acp.circ_modifier='Large Print Book'
and acp.circ_modifier not in ('Magazine','ILL')

group by 1,2,3,4,5
order by 10 desc
limit 4000
) x

);

-- All Coming Soon Titles - The library has ordered these titles and will receive them when they are released.
\set list 5345

--Clear out old entries
delete from container.biblio_record_entry_bucket_item where bucket=:list;

insert into container.biblio_record_entry_bucket_item (pos,bucket,target_biblio_record_entry)
select ooi.rownum pos, :list bucket, ooi.bibid target_biblio_record_entry
from
on_order_items ooi
;

-- DVDs Coming Soon - The library has ordered these DVD titles and will receive them when they are released.
\set list 5346

--Clear out old entries
delete from container.biblio_record_entry_bucket_item where bucket=:list;

insert into container.biblio_record_entry_bucket_item (pos,bucket,target_biblio_record_entry)
select ooi.rownum pos, :list bucket, ooi.bibid target_biblio_record_entry
from
on_order_items ooi
where
ooi.circ_modifier='DVD'
;

-- Books Coming Soon - The library has ordered these titles and will receive them when they are released.
\set list 5347

--Clear out old entries
delete from container.biblio_record_entry_bucket_item where bucket=:list;

insert into container.biblio_record_entry_bucket_item (pos,bucket,target_biblio_record_entry)
select ooi.rownum pos, :list bucket, ooi.bibid target_biblio_record_entry
from
on_order_items ooi
where
ooi.circ_modifier='Book'
;

commit;

-- ##### Native American Collection
begin;

-- Native American Collection
CREATE TEMPORARY TABLE na_collection
ON COMMIT DROP
AS (


select row_number() over () as rownum, * from (

select rssr.id bibid, rssr.title, rssr.author
--, concat_ws(' ',acnp.label, acn.label) "Call Number"
, rssr.pubdate, acp.circ_modifier
, string_agg(distinct aou.shortname, ', ') "Branches", count(acp.id) "Copy Count"
, string_agg(distinct ccs.name,', ') "Copy Status", max(acp.create_date) "Most Recent Create Date"
, string_agg(distinct acl.name,', ') "Shelving Locations"
, string_agg(distinct asce21.value, ', ') as "Book Genre"
, string_agg(distinct asce20.value, ', ') as "Special Collections"
, string_agg(distinct asce19.value, ', ') as "Fiction/NF"
, string_agg(distinct asce18.value, ', ') as "Format"
, string_agg(distinct asce17.value, ', ') as "Reading Watching Level"
, string_agg(distinct asce16.value, ', ') as "Genre Music"
, string_agg(distinct asce15.value, ', ') as "Genre Movie"
, string_agg(distinct asce24.value, ', ') as "Native American Collection"

from asset.copy acp
join actor.org_unit aou on aou.id=acp.circ_lib
join config.copy_status ccs on ccs.id=acp.status
join asset.call_number acn on acn.id=acp.call_number
left outer join asset.call_number_prefix acnp on acnp.id=acn.prefix and acnp.id!=(-1)
join biblio.record_entry bre on acn.record=bre.id
join reporter.super_simple_record rssr on rssr.id=bre.id
join asset.copy_location acl on acl.id=acp.location
-- 15 = Movie Genre
left outer join asset.stat_cat_entry_copy_map ascecm15 on ascecm15.owning_copy=acp.id and ascecm15.stat_cat=15
left outer join asset.stat_cat_entry asce15 on asce15.id=ascecm15.stat_cat_entry

-- 16 = Music Genre
left outer join asset.stat_cat_entry_copy_map ascecm16 on ascecm16.owning_copy=acp.id and ascecm16.stat_cat=16
left outer join asset.stat_cat_entry asce16 on asce16.id=ascecm16.stat_cat_entry

-- 17 = Reading Level
left outer join asset.stat_cat_entry_copy_map ascecm17 on ascecm17.owning_copy=acp.id and ascecm17.stat_cat=17
left outer join asset.stat_cat_entry asce17 on asce17.id=ascecm17.stat_cat_entry

-- 18 = Format
left outer join asset.stat_cat_entry_copy_map ascecm18 on ascecm18.owning_copy=acp.id and ascecm18.stat_cat=18
left outer join asset.stat_cat_entry asce18 on asce18.id=ascecm18.stat_cat_entry

-- 19 = Fiction/NF
left outer join asset.stat_cat_entry_copy_map ascecm19 on ascecm19.owning_copy=acp.id and ascecm19.stat_cat=19
left outer join asset.stat_cat_entry asce19 on asce19.id=ascecm19.stat_cat_entry

-- 20 - Special Collections
left outer join asset.stat_cat_entry_copy_map ascecm20 on ascecm20.owning_copy=acp.id and ascecm20.stat_cat=20
left outer join asset.stat_cat_entry asce20 on asce20.id=ascecm20.stat_cat_entry

-- 21 - Book Genre
left outer join asset.stat_cat_entry_copy_map ascecm21 on ascecm21.owning_copy=acp.id and ascecm21.stat_cat=21
left outer join asset.stat_cat_entry asce21 on asce21.id=ascecm21.stat_cat_entry

-- 24 - Native American Collection
left outer join asset.stat_cat_entry_copy_map ascecm24 on ascecm24.owning_copy=acp.id and ascecm24.stat_cat=24
left outer join asset.stat_cat_entry asce24 on asce24.id=ascecm24.stat_cat_entry

where
acp.circ_lib in (SELECT id FROM actor.org_unit_descendants(101))
and not acp.deleted
-- and acp.active_date > now()-'5 months'::interval
-- and acp.location=122
and acp.status in (0,1,6,8,5,9)
-- and acp.circ_modifier='Large Print Book'
and acp.circ_modifier not in ('ILL')
and asce24.value = 'NAC'

group by 1,2,3,4,5
order by random()
limit 400
) x
)
;

-- Native American Collection
\set list 20025

--Clear out old entries
delete from container.biblio_record_entry_bucket_item where bucket=:list;

insert into container.biblio_record_entry_bucket_item (pos,bucket,target_biblio_record_entry)
select ooi.rownum pos, :list bucket, ooi.bibid target_biblio_record_entry
from
na_collection ooi
;

commit;


-- ##### Book Club Kits
begin;

-- Book Club Kits
CREATE TEMPORARY TABLE bc_collection
ON COMMIT DROP
AS (


select row_number() over () as rownum, * from (

select rssr.id bibid, rssr.title, rssr.author
--, concat_ws(' ',acnp.label, acn.label) "Call Number"
, rssr.pubdate, acp.circ_modifier
, string_agg(distinct aou.shortname, ', ') "Branches", count(acp.id) "Copy Count"
, string_agg(distinct ccs.name,', ') "Copy Status", max(acp.create_date) "Most Recent Create Date"
, string_agg(distinct acl.name,', ') "Shelving Locations"
, string_agg(distinct asce21.value, ', ') as "Book Genre"
, string_agg(distinct asce20.value, ', ') as "Special Collections"
, string_agg(distinct asce19.value, ', ') as "Fiction/NF"
, string_agg(distinct asce18.value, ', ') as "Format"
, string_agg(distinct asce17.value, ', ') as "Reading Watching Level"
, string_agg(distinct asce16.value, ', ') as "Genre Music"
, string_agg(distinct asce15.value, ', ') as "Genre Movie"
, string_agg(distinct asce24.value, ', ') as "Native American Collection"

from asset.copy acp
join actor.org_unit aou on aou.id=acp.circ_lib
join config.copy_status ccs on ccs.id=acp.status
join asset.call_number acn on acn.id=acp.call_number
left outer join asset.call_number_prefix acnp on acnp.id=acn.prefix and acnp.id!=(-1)
join biblio.record_entry bre on acn.record=bre.id
join reporter.super_simple_record rssr on rssr.id=bre.id
join asset.copy_location acl on acl.id=acp.location
-- 15 = Movie Genre
left outer join asset.stat_cat_entry_copy_map ascecm15 on ascecm15.owning_copy=acp.id and ascecm15.stat_cat=15
left outer join asset.stat_cat_entry asce15 on asce15.id=ascecm15.stat_cat_entry

-- 16 = Music Genre
left outer join asset.stat_cat_entry_copy_map ascecm16 on ascecm16.owning_copy=acp.id and ascecm16.stat_cat=16
left outer join asset.stat_cat_entry asce16 on asce16.id=ascecm16.stat_cat_entry

-- 17 = Reading Level
left outer join asset.stat_cat_entry_copy_map ascecm17 on ascecm17.owning_copy=acp.id and ascecm17.stat_cat=17
left outer join asset.stat_cat_entry asce17 on asce17.id=ascecm17.stat_cat_entry

-- 18 = Format
left outer join asset.stat_cat_entry_copy_map ascecm18 on ascecm18.owning_copy=acp.id and ascecm18.stat_cat=18
left outer join asset.stat_cat_entry asce18 on asce18.id=ascecm18.stat_cat_entry

-- 19 = Fiction/NF
left outer join asset.stat_cat_entry_copy_map ascecm19 on ascecm19.owning_copy=acp.id and ascecm19.stat_cat=19
left outer join asset.stat_cat_entry asce19 on asce19.id=ascecm19.stat_cat_entry

-- 20 - Special Collections
left outer join asset.stat_cat_entry_copy_map ascecm20 on ascecm20.owning_copy=acp.id and ascecm20.stat_cat=20
left outer join asset.stat_cat_entry asce20 on asce20.id=ascecm20.stat_cat_entry

-- 21 - Book Genre
left outer join asset.stat_cat_entry_copy_map ascecm21 on ascecm21.owning_copy=acp.id and ascecm21.stat_cat=21
left outer join asset.stat_cat_entry asce21 on asce21.id=ascecm21.stat_cat_entry

-- 24 - Native American Collection
left outer join asset.stat_cat_entry_copy_map ascecm24 on ascecm24.owning_copy=acp.id and ascecm24.stat_cat=24
left outer join asset.stat_cat_entry asce24 on asce24.id=ascecm24.stat_cat_entry

where
acp.circ_lib in (SELECT id FROM actor.org_unit_descendants(101))
and not acp.deleted
-- and acp.active_date > now()-'5 months'::interval
-- and acp.location=122
and acp.status in (0,1,6,8,5,9)
-- and acp.circ_modifier='Large Print Book'
and acp.circ_modifier not in ('ILL')
and asce18.value = 'Book Club Kit'

group by 1,2,3,4,5
order by rssr.title
limit 400
) x
)
;

-- Book Club Kits
\set list 87976

--Clear out old entries
delete from container.biblio_record_entry_bucket_item where bucket=:list;

insert into container.biblio_record_entry_bucket_item (pos,bucket,target_biblio_record_entry)
select ooi.rownum pos, :list bucket, ooi.bibid target_biblio_record_entry
from
bc_collection ooi
;

commit;