Merge branch 'master' of git+ssh://git.sitka.bclibraries.ca/sitka/sitka-tools

[sitka/sitka-tools.git] / marc_import_overdrive / overdrive-ingest-db-func.sql

CREATE OR REPLACE FUNCTION sitka.overdrive_MARCXML_delete_all_uri_fields( TEXT ) RETURNS TEXT AS $func$
    use MARC::Record;
    use MARC::File::XML (BinaryEncoding => 'UTF-8');

    my $xml = shift;

    my $r = MARC::Record->new_from_xml( $xml );

    # check all 856s
    URIFIELD: foreach my $uri ($r->field('856')){

        # Overdrive uses a $3 for Excerpts, we want to keep this intact so carry on then
        next URIFIELD if ($uri->subfield('3')); 

        # otherwise, delete away
        $r->delete_fields($uri);
    }

    $xml = $r->as_xml_record();
    $xml =~ s/^<\?.+?\?>$//mo;
    $xml =~ s/\n//sgo;
    $xml =~ s/>\s+</></sgo;
    return $xml;
$func$ LANGUAGE PLPERLU;

CREATE OR REPLACE FUNCTION sitka.overdrive_MARCXML_add_uri_field( TEXT, TEXT, TEXT, TEXT, TEXT ) RETURNS TEXT AS $func$
    use MARC::Record;
    use MARC::File::XML (BinaryEncoding => 'UTF-8');

    my $xml = shift;
	my $tcn_prefix = shift;
	my $url_prefix = shift;
	my $uri_caption = shift;
    my $shortname = shift;

    my $r = MARC::Record->new_from_xml( $xml );

    my $tcn = $r->subfield('901','a');
    $tcn =~ s/^$tcn_prefix//g;

    foreach my $uri ($r->field('856')){
        if($uri->subfield('9')){
            if($uri->subfield('9') eq $shortname){
                $r->delete_field($uri);
            }
        }
    }
    my $field856 = MARC::Field->new(
            '856' => ('4', '0'),
            u => $url_prefix . $tcn,
            z => $uri_caption,
            9 => $shortname
    );

    $r->insert_fields_ordered($field856);

    $xml = $r->as_xml_record();
    $xml =~ s/^<\?.+?\?>$//mo;
    $xml =~ s/\n//sgo;
    $xml =~ s/>\s+</></sgo;
    return $xml;

$func$ LANGUAGE PLPERLU;

-- #u => "http://downloads.bclibrary.ca/ContentDetails.htm?ID=" . $tcn,
-- "Click to access online (library card required)",


CREATE OR REPLACE FUNCTION sitka.overdrive_bc_refresh_uris ( rid BIGINT ) RETURNS BOOLEAN AS $$
DECLARE
	marcxml TEXT;
	tcnprefix TEXT := 'Overdrive_';
	urlprefix TEXT := 'http://downloads.bclibrary.ca/ContentDetails.htm?ID=';
	urlcaption TEXT := 'Click to access online (library card required)';
BEGIN
    EXECUTE SELECT sitka.overdrive_MARCXML_delete_all_uri_fields(marc) FROM biblio.record_entry where id = rid INTO marcxml;

	-- clean out asset.call_number and asset.uri and asset.uri_call_number_map

	-- could be modified to pull shortnames based on an ou setting
	-- select sitka.overdrive_MARCXML_add_uri_field(rid,tcnprefix,urlprefix,urlcaption,a.shortname) 
	-- 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
	-- something like that

	EXECUTE SELECT sitka.overdrive_bc_add_uri_field (rid, tcnprefix, urlprefix, urlcaption, 'BBGVL') INTO marcxml;
	EXECUTE SELECT sitka.overdrive_bc_add_uri_field (rid, tcnprefix, urlprefix, urlcaption, 'BBNCLF') INTO marcxml;
	EXECUTE SELECT sitka.overdrive_bc_add_uri_field (rid, tcnprefix, urlprefix, urlcaption, 'BCK') INTO marcxml;
	EXECUTE SELECT sitka.overdrive_bc_add_uri_field (rid, tcnprefix, urlprefix, urlcaption, 'BNCLF') INTO marcxml;
	EXECUTE SELECT sitka.overdrive_bc_add_uri_field (rid, tcnprefix, urlprefix, urlcaption, 'BNELF') INTO marcxml;
	EXECUTE SELECT sitka.overdrive_bc_add_uri_field (rid, tcnprefix, urlprefix, urlcaption, 'ISLANDLINK') INTO marcxml;
	EXECUTE SELECT sitka.overdrive_bc_add_uri_field (rid, tcnprefix, urlprefix, urlcaption, 'UNFEDERATED') INTO marcxml;

    EXECUTE UPDATE biblio.record_entry set marc = marcxml WHERE id = rid;
	return true;
END;
$$ LANGUAGE PLPGSQL IMMUTABLE;