1 CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS DATE AS $$
3 attempt_value ALIAS FOR $1;
4 fail_value ALIAS FOR $2;
8 EXECUTE E'SELECT (\'1970-01-01\'::date + \'' || attempt_value || E' days\'::interval)::date AS a;'
15 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
20 $$ LANGUAGE PLPGSQL STRICT STABLE;
22 CREATE OR REPLACE FUNCTION migration_tools.attempt_sierra_timestamp (TEXT,TEXT) RETURNS TIMESTAMP AS $$
24 attempt_value ALIAS FOR $1;
25 fail_value ALIAS FOR $2;
28 output := REGEXP_REPLACE(attempt_value,E'^(..)(..)(..)(..)(..)$',E'20\\1-\\2-\\3 \\4:\\5')::TIMESTAMP;
33 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMP AS a;'
38 $$ LANGUAGE PLPGSQL STRICT STABLE;
40 CREATE OR REPLACE FUNCTION migration_tools.openbiblio2marc (x_bibid TEXT) RETURNS TEXT AS $func$
42 -- Expects the following table/columns:
46 -- l_create_dt | 2007-03-07 09:03:09
47 -- l_last_change_dt | 2015-01-23 11:18:54
48 -- l_last_change_userid | 2
50 -- l_collection_cd | 13
51 -- l_call_nmbr1 | Canada
53 -- l_call_nmbr3 | Ottawa 18
54 -- l_title | Art and the courts : France ad England
55 -- l_title_remainder | from 1259-1328
56 -- l_responsibility_stmt |
57 -- l_author | National Gallery of Canada
64 -- l_flag_attention | 0
66 -- export_biblio_field_tsv:
73 -- l_field_data | Brieger, Peter Henry
75 -- Map export_biblio_tsv as follows:
76 -- l_call_nmbr? -> 099a
79 -- l_title_remainder -> 245b
80 -- l_responsibility_stmt -> 245c
85 migration_tools.make_stub_bib(y.tag,y.ind1,y.ind2,y.data)
88 array_agg(lpad(l_tag,3,'0') || l_subfield_cd) as "tag",
89 array_agg(l_ind1_cd) as "ind1",
90 array_agg(l_ind2_cd) as "ind2",
91 array_agg(l_field_data) as "data"
99 from export_biblio_field_tsv
100 where l_bibid = x_bibid
104 'a' as "l_subfield_cd",
108 nullif(btrim(l_call_nmbr1),''),
109 nullif(btrim(l_call_nmbr2),''),
110 nullif(btrim(l_call_nmbr3),'')
112 from export_biblio_tsv
113 where l_bibid = x_bibid
117 'a' as "l_subfield_cd",
120 l_author as "l_field_data"
121 from export_biblio_tsv
122 where l_bibid = x_bibid and nullif(btrim(l_author),'') is not null
126 'a' as "l_subfield_cd",
129 l_title as "l_field_data"
130 from export_biblio_tsv
131 where l_bibid = x_bibid and nullif(btrim(l_title),'') is not null
135 'b' as "l_subfield_cd",
138 l_title_remainder as "l_field_data"
139 from export_biblio_tsv
140 where l_bibid = x_bibid and nullif(btrim(l_title_remainder),'') is not null
144 'a' as "l_subfield_cd",
147 l_topic1 as "l_field_data"
148 from export_biblio_tsv
149 where l_bibid = x_bibid and nullif(btrim(l_topic1),'') is not null
153 'a' as "l_subfield_cd",
156 l_topic2 as "l_field_data"
157 from export_biblio_tsv
158 where l_bibid = x_bibid and nullif(btrim(l_topic2),'') is not null
162 'a' as "l_subfield_cd",
165 l_topic3 as "l_field_data"
166 from export_biblio_tsv
167 where l_bibid = x_bibid and nullif(btrim(l_topic3),'') is not null
171 'a' as "l_subfield_cd",
174 l_topic4 as "l_field_data"
175 from export_biblio_tsv
176 where l_bibid = x_bibid and nullif(btrim(l_topic4),'') is not null
180 'a' as "l_subfield_cd",
183 l_topic5 as "l_field_data"
184 from export_biblio_tsv
185 where l_bibid = x_bibid and nullif(btrim(l_topic5),'') is not null
189 '' as "l_subfield_cd",
192 l_bibid as "l_field_data"
193 from export_biblio_tsv
194 where l_bibid = x_bibid
199 $func$ LANGUAGE plpgsql;
201 -- add koha holding tag to marc
202 DROP FUNCTION IF EXISTS migration_tools.generate_koha_holding_tag(TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT);
204 CREATE OR REPLACE FUNCTION migration_tools.generate_koha_holding_tag(marc TEXT, tag TEXT, ind1 TEXT, ind2 TEXT, barcode TEXT, dateaccessioned TEXT, booksellerid TEXT, homebranch TEXT, price TEXT, replacementprice TEXT, replacementpricedate TEXT, datelastborrowed TEXT, datelastseen TEXT, stack TEXT, notforloan TEXT, damaged TEXT, itemlost TEXT, wthdrawn TEXT, itemcallnumber TEXT, issues TEXT, renewals TEXT, reserves TEXT, restricted TEXT, internalnotes TEXT, itemnotes TEXT, holdingbranch TEXT, location TEXT, onloan TEXT, cn_source TEXT, cn_sort TEXT, ccode TEXT, materials TEXT, uri TEXT, itype TEXT, enumchron TEXT, copynumber TEXT, stocknumber TEXT)
212 use MARC::File::XML (BinaryEncoding => 'utf8');
214 binmode(STDERR, ':bytes');
215 binmode(STDOUT, ':utf8');
216 binmode(STDERR, ':utf8');
218 my ($marc_xml, $tag , $ind1 , $ind2 , $barcode , $dateaccessioned , $booksellerid , $homebranch , $price , $replacementprice , $replacementpricedate , $datelastborrowed , $datelastseen , $stack , $notforloan , $damaged , $itemlost , $wthdrawn , $itemcallnumber , $issues , $renewals , $reserves , $restricted , $internalnotes , $itemnotes , $holdingbranch , $location , $onloan , $cn_source , $cn_sort , $ccode , $materials , $uri , $itype , $enumchron , $copynumber , $stocknumber ) = @_;
220 $marc_xml =~ s/(<leader>.........)./${1}a/;
223 $marc_xml = MARC::Record->new_from_xml($marc_xml);
226 #elog("could not parse $bibid: $@\n");
227 import MARC::File::XML (BinaryEncoding => 'utf8');
231 my $new_field = new MARC::Field(
234 'b' => $holdingbranch,
240 if ($dateaccessioned) { $new_field->add_subfields('d' => $dateaccessioned); }
241 if ($booksellerid) { $new_field->add_subfields('e' => $booksellerid); }
242 if ($price) { $new_field->add_subfields('g' => $price); }
243 if ($replacementprice) { $new_field->add_subfields('v' => $replacementprice); }
244 if ($replacementpricedate) { $new_field->add_subfields('w' => $replacementpricedate); }
245 if ($datelastborrowed) { $new_field->add_subfields('s' => $datelastborrowed); }
246 if ($datelastseen) { $new_field->add_subfields('r' => $datelastseen); }
247 if ($stack) { $new_field->add_subfields('j' => $stack); }
248 if ($notforloan) { $new_field->add_subfields('7' => $notforloan); }
249 if ($damaged) { $new_field->add_subfields('4' => $damaged); }
250 if ($itemlost) { $new_field->add_subfields('1' => $itemlost); }
251 if ($wthdrawn) { $new_field->add_subfields('0' => $wthdrawn); }
252 if ($itemcallnumber) { $new_field->add_subfields('o' => $itemcallnumber); }
253 if ($issues) { $new_field->add_subfields('l' => $issues); }
254 if ($renewals) { $new_field->add_subfields('m' => $renewals); }
255 if ($reserves) { $new_field->add_subfields('n' => $reserves); }
256 if ($restricted) { $new_field->add_subfields('5' => $restricted); }
257 if ($internalnotes) { $new_field->add_subfields('x' => $internalnotes); }
258 if ($itemnotes) { $new_field->add_subfields('z' => $itemnotes); }
259 if ($onloan) { $new_field->add_subfields('q' => $onloan); }
260 if ($cn_source) { $new_field->add_subfields('2' => $cn_source); }
261 if ($cn_sort) { $new_field->add_subfields('6' => $cn_sort); }
262 if ($ccode) { $new_field->add_subfields('8' => $ccode); }
263 if ($materials) { $new_field->add_subfields('3' => $materials); }
264 if ($uri) { $new_field->add_subfields('u' => $uri); }
265 if ($enumchron) { $new_field->add_subfields('h' => $enumchron); }
266 if ($copynumber) { $new_field->add_subfields('t' => $copynumber); }
267 if ($stocknumber) { $new_field->add_subfields('i' => $stocknumber); }
269 $marc_xml->insert_grouped_field( $new_field );
271 return $marc_xml->as_xml_record();