stompro

[% phone = target.0.sms_notify | replace('[\s\-\(\)]', '') -%]
[% cid = target.0.pickup_lib.phone | replace('[\s\-\(\)]', '') -%]
[% IF phone.match('^[2-9]') %][% country = 1 %][% ELSE %][% country = '' %][% END -%]
[%-
user = target.0.usr
system_shortname = user.home_ou.parent_ou.shortname
#target.0.sms_notify
realphone = country _ phone
tophone = '15555552110'
fromphone = '15555556040'

stompro

-- Use history to find the date and staff member that changed the card
select * from (
select aauh.audit_id, aauh.audit_time, aauh.audit_user, aauh.card, aauh.id

,lead(aauh.card) OVER (PARTITION BY aauh.id ORDER BY aauh.audit_time) as next_card

from auditor.actor_usr_lifecycle aauh

where aauh.id > 112900
--and aauh.id=124212

stompro

-- Check max length after super simple changes
select x.*, length(x.title) from (
SELECT  r.id,
    r.fingerprint,
    r.quality,
    r.tcn_source,
    r.tcn_value,
    FIRST(title.value) AS title,
    FIRST(author.value) AS author,
    STRING_AGG(DISTINCT publisher.value, ', ') AS publisher,

stompro

-- Create dictionaries

CREATE TEXT SEARCH DICTIONARY public.synonym_larl (template=pg_catalog.synonym, synonyms='synonym_larl');

CREATE TEXT SEARCH DICTIONARY public.synonym_larl_int (template=pg_catalog.synonym, synonyms='synonym_larl_int');

CREATE TEXT SEARCH DICTIONARY public.synonym_larl_int_roman (template=pg_catalog.synonym, synonyms='synonym_larl_int_roman');

CREATE TEXT SEARCH DICTIONARY public.synonym_larl_txt_roman (template=pg_catalog.synonym, synonyms='synonym_larl_txt_roman');

stompro

-- Report on holds not picked up because of phone notify issues.

select aou.shortname "Patron Home Lib"
, acd.barcode "Patron Barcode", au.family_name, au.first_given_name
,ahr.shelf_expire_time "Holdshelf Expired"
,(select value from metabib.record_attr_flat where attr='icon_format' and id=bre.id limit 1) as Type
,ARRAY_TO_STRING(
    XPATH('//marc:datafield[@tag="245"][1]//text()',
      marc::XML, ARRAY[ARRAY['marc', 'http://www.loc.gov/MARC21/slim']]),' ') as "Title"
,bre.id

stompro

-- Our ILL workflow works best when ILL staff use force holds to placed holds on incoming ILL items for our users.
-- This causes a problem becuase hold notifications are not set for those holds.
-- This script adds back in the default hold notifications for those holds.

begin;

-- Add notification to forced holds where possible
update action.hold_request ahr

set email_notify = CASE WHEN (aus.value~'email' or aus.value is null) and au.email is not null THEN true else false END,

stompro

-- Problem: For ILL items, we don't know the price until after the item is billed.  We need to update the billed amount after the price 
-- changes.

-- set the replacement billing price = copy price when they don't match for ILL items.

begin;

update money.billing mb
set amount=acp.price
from 

stompro

--- Boopsie Extract

--List of opac visible records
select aovc.record 
from asset.opac_visible_copies aovc
where aovc.record > 0
group by 1
order by 1
;

stompro

-- Method of keeping the copy/item templates the same for certain users.

-- Copy Linda's copy templates to Molly and Jeanne

begin;

-- LARL Catalogers Updates
update actor.usr_setting aus
set value=auslinda.value
from actor.usr_setting auslinda

stompro

\echo Post-Hook SQL Scripts - Run after new events have been created.

begin;

\echo Summary of events for event_def=105
select state,count(id) from action_trigger.event where event_def=105 group by state;

\echo Change event to invalid if phone notify not set.
-- Set to invalid if phone notify not set.
UPDATE action_trigger.event ate