EG install scripts: typo fix
[sitka/sitka-tools.git] / marc_import_overdrive / overdrive-ingest-db-func.sql
CommitLineData
4856f9b1 1CREATE OR REPLACE FUNCTION sitka.overdrive_bc_delete_all_uri_fields( TEXT ) RETURNS TEXT AS $func$
38891e1e
JF
2 use MARC::Record;
3 use MARC::File::XML (BinaryEncoding => 'UTF-8');
4
5 my $xml = shift;
6
7 my $r = MARC::Record->new_from_xml( $xml );
8
9 # check all 856s
10 URIFIELD: foreach my $uri ($r->field('856')){
11
12 # Overdrive uses a $3 for Excerpts, we want to keep this intact so carry on then
13 next URIFIELD if ($uri->subfield('3'));
14
15 # otherwise, delete away
4856f9b1 16 $r->delete_field($uri);
38891e1e
JF
17 }
18
19 $xml = $r->as_xml_record();
20 $xml =~ s/^<\?.+?\?>$//mo;
21 $xml =~ s/\n//sgo;
22 $xml =~ s/>\s+</></sgo;
23 return $xml;
24$func$ LANGUAGE PLPERLU;
25
4856f9b1 26CREATE OR REPLACE FUNCTION sitka.overdrive_bc_add_uri_field( TEXT, TEXT, TEXT, TEXT, TEXT ) RETURNS TEXT AS $func$
38891e1e
JF
27 use MARC::Record;
28 use MARC::File::XML (BinaryEncoding => 'UTF-8');
29
30 my $xml = shift;
31 my $tcn_prefix = shift;
32 my $url_prefix = shift;
33 my $uri_caption = shift;
34 my $shortname = shift;
35
36 my $r = MARC::Record->new_from_xml( $xml );
37
38 my $tcn = $r->subfield('901','a');
39 $tcn =~ s/^$tcn_prefix//g;
40
41 foreach my $uri ($r->field('856')){
42 if($uri->subfield('9')){
43 if($uri->subfield('9') eq $shortname){
44 $r->delete_field($uri);
45 }
46 }
47 }
48 my $field856 = MARC::Field->new(
49 '856' => ('4', '0'),
50 u => $url_prefix . $tcn,
1cfb727d 51 y => $uri_caption,
38891e1e
JF
52 9 => $shortname
53 );
54
55 $r->insert_fields_ordered($field856);
56
57 $xml = $r->as_xml_record();
58 $xml =~ s/^<\?.+?\?>$//mo;
59 $xml =~ s/\n//sgo;
60 $xml =~ s/>\s+</></sgo;
61 return $xml;
62
63$func$ LANGUAGE PLPERLU;
64
65-- #u => "http://downloads.bclibrary.ca/ContentDetails.htm?ID=" . $tcn,
66-- "Click to access online (library card required)",
67
68
69CREATE OR REPLACE FUNCTION sitka.overdrive_bc_refresh_uris ( rid BIGINT ) RETURNS BOOLEAN AS $$
70DECLARE
71 marcxml TEXT;
72 tcnprefix TEXT := 'Overdrive_';
73 urlprefix TEXT := 'http://downloads.bclibrary.ca/ContentDetails.htm?ID=';
74 urlcaption TEXT := 'Click to access online (library card required)';
75BEGIN
4856f9b1 76 SELECT sitka.overdrive_bc_delete_all_uri_fields(marc) FROM biblio.record_entry where id = rid INTO marcxml;
38891e1e
JF
77
78 -- clean out asset.call_number and asset.uri and asset.uri_call_number_map
79
80 -- could be modified to pull shortnames based on an ou setting
81 -- select sitka.overdrive_MARCXML_add_uri_field(rid,tcnprefix,urlprefix,urlcaption,a.shortname)
82 -- from actor.org_unit_setting b join actor.org_unit a on (a.id = b.org_unit) where b.name = 'sitka.overdrive_setting' and b.value = true
83 -- something like that
84
50ff9f06 85 SELECT sitka.overdrive_bc_add_uri_field(marcxml::TEXT, tcnprefix::TEXT, urlprefix::TEXT, urlcaption::TEXT, shortname::TEXT) FROM sitka.bc_overdrive_subscribers INTO marcxml;
38891e1e 86
4856f9b1 87 UPDATE biblio.record_entry set marc = marcxml WHERE id = rid;
38891e1e
JF
88 return true;
89END;
4856f9b1 90$$ LANGUAGE PLPGSQL;
50ff9f06
JF
91
92CREATE TABLE sitka.bc_overdrive_subscribers (shortname TEXT);
93INSERT INTO sitka.bc_overdrive_subscribers VALUES
94('BB'),
95('BNV'),
96('BMV'),
97('BVA'),
98('BPE'),
99('BCOQ'),
100('BNW'),
101('BSUR'),
102('BNVD'),
103('BBI'),
104('BSQ'),
105('BW'),
106('BRI'),
107('BABF'),
108('BLP'),
109('BGIBSE'),
110('BPMP'),
111('BMB'),
112('BVDH'),
113('BFSJA'),
114('BPG'),
115('BWLCR'),
116('BMK'),
117('BGSI'),
118('BCR'),
119('BVI'),
120('BS'),
121('BFN'),
122('BPRDP'),
123('BTA'),
124('BNA'),
125('BF'),
126('BH'),
127('BSPA'),
128('BHA'),
129('BTR'),
130('BHH'),
131('BPR'),
132('BCHE'),
133('BKASL'),
134('BFSJ'),
135('BR'),
136('BFBV'),
137('BKI'),
138('BCD'),
139('BIN'),
140('BGF'),
141('BSA'),
142('BNE'),
143('BT'),
144('BTE'),
145('BCRE'),
146('BKO'),
147('BP'),
148('BKCT'),
149('BNVI');