Setting up the loans cache for use in the academic dashboard

The academic dashboard has a number of features that are only available if the “loans cache” part of LORLS has been set up.  The installer will create the required database and install the following three scripts in the “/usr/local/LUMP/bin” directory.

  • updateISBNMap
  • updateLoanCache
  • preloadLoanCache

The “updateISBNMap” and “updateLoanCache” scripts require access to the following APIs on your Library Management System (LMS).

  • getISBNMap
  • getLoanTransactions

You will probably have to create these APIs for your LMS.  We have example API scripts for the Aleph LMS from ExLibris that are available at the bottom of this page.

The getISBNMap API takes no parameters and returns the XML mapping between each ISBN in the system and its local control number and at the end it should also contain a total count of the isbns. An example of the output is:

<isbns>
 <map>
  <isbn>155587083x</isbn>
  <sysno>000281827</sysno>
 </map>
 <map>
  <isbn>072920720x</isbn>
  <sysno>000241757</sysno>
 </map>
 .
 .
 .
 <map>
  <isbn>0116308125</isbn>
  <sysno>000158641</sysno>
 </map>
 <total_isbn_count>398964</total_isbn_count>
</isbns>

The getLoanTransactions API should take one parameter “on” which contains the date to list the loan transactions for. The date should be in the format YYYY-MM-DD. The XML returned should contain for each loan either issued or returned on that date the following details:

  • lcn – the local control number for the item being issued or returned.
  • isbn – the isbn of the item being issued or returned.
  • issue_date – the date and time the item was issued (in the format “YYYYMMDD HHMM”)
  • return_date – the date and time the item was returned (in the format “YYYYMMDD HHMM”). If the transaction is an issue then this should be be left empty.
  • item_type – a local code for the type of item (e.g. 01 – long loan, 02 – week loan, 03 – short loan)
  • department – the code for the department the borrower belongs to

Again the XML file should finish with a total count of the number of loan transactions it has returned. An example output of this API is:

<loan_transactions>
 <loan>
  <lcn>001050151</lcn>
  <isbn>0880119683</isbn>
  <issue_date>20121203 1421</issue_date>
  <return_date></return_date>
  <item_type>01</item_type>
  <department>LB</department>
 </loan>
 <loan>
  <lcn>000840951</lcn>
  <isbn>0850361850</isbn>
  <issue_date>20121203 1709</issue_date>
  <return_date></return_date>
  <item_type>01</item_type>
  <department>LB</department>
 </loan>
 .
 .
 .
 <loan>
  <lcn>000224944</lcn>
  <isbn>0631195203</isbn>
  <issue_date>20121203 1709</issue_date>
  <return_date></return_date>
  <item_type>01</item_type>
  <department>LB</department>
 </loan>
 <total_loan_count>3237</total_loan_count>
</loan_transactions>

When the APIs are available then you will need to run the updateISBNMap script which will populate a mapping between item’s ISBNs and its local control number.

After the updateISBNMap script has completed you can then run the preloadLoanCache script which will populate the loancache with a refined version of the last years worth of loan transactions from your LMS.

After the preloadLoanCache script has completed you will need to put entries in your cron to run the updateISBNMap and the updateLoanCache scripts each day.

Example getISBNMap API script for Aleph

You will need to replace <ORACLE HOME DIR>, <DATABASE>, <DB_USERNAME> and <DB_PASSWORD> and <BIB_LIBRARY> with the relevant values for your aleph configuration.

#!/exlibris/aleph/a20_2/product/bin/perl
#
use strict;
use CGI;
use DBI;

$ENV{"ORACLE_HOME"} = "<ORACLE HOME DIR>";

my $dbh = DBI->connect("DBI:Oracle:<DATABASE>","<DB_USERNAME>","<DB_PASSWORD>") || die "$!";
$| = 1;
my $q = new CGI;

my $getISBN2SysNo=$dbh->prepare("select Z00R_TEXT, Z00R_DOC_NUMBER from <BIB_LIBRARY>.Z00R where Z00R_FIELD_CODE='020'");

my $total_number=0;

print STDOUT "Content-type: text/xmlnn";
print STDOUT "<isbns>n";

$getISBN2SysNo->execute();
while (my ($isbn, $sysno) = $getISBN2SysNo->fetchrow_array) {
        $sysno=substr($sysno,0,9);
        $isbn=~s/^$$a//;
        $isbn=~s/s.*$//;
        print STDOUT " <map>n";
        print STDOUT "  <isbn>$isbn</isbn>n";
        print STDOUT "  <sysno>$sysno</sysno>n";
        print STDOUT " </map>n";
        $total_number++;
}
print STDOUT " <total_isbn_count>$total_number</total_isbn_count>n";
print STDOUT "</isbns>n";

Example getLoanTransactions API script for Aleph

You will need to replace <ORACLE HOME DIR>, <DATABASE>, <DB_USERNAME> and <DB_PASSWORD>, <BIB_LIBRARY> and <ADMIN_LIBRARY> with the relevant values for your aleph configuration.

#!/exlibris/aleph/a20_2/product/bin/perl
#
use strict;
use CGI;
use DBI;

$ENV{"ORACLE_HOME"} = "<ORACLE HOME DIR>";

my $dbh = DBI->connect("DBI:Oracle:<DATABASE>","<DB_USERNAME>","<DB_PASSWORD>") || die "$!";
$| = 1;
my $q = new CGI;
my $on = $q->param('on');

my ($onDate)=$on=~m/(d{4}-d{2}-d{2})/;
$onDate=~s/^(d{4})-(d{2})-(d{2})$/$1$2$3/;

if(!$onDate){
        print STDOUT "Content-type: text/xmlnn";
        print STDOUT "<loan_transactions>n";
        print STDOUT "<error>No Date Specified</error>n";
        print STDOUT "</loan_transactions>n";
        exit;
}

my $getISBN=$dbh->prepare("select Z00R_TEXT from <BIB_LIBRARY>.Z00R where Z00R_DOC_NUMBER=? and (Z00R_FIELD_CODE='020' or Z00R_F
IELD_CODE='022')");

my $total_number=0;

print STDOUT "Content-type: text/xmlnn";
print STDOUT "<loan_transactions>n";

my $currentloan =
        $dbh->prepare('select Z36_REC_KEY, Z36_LOAN_DATE, Z36_LOAN_HOUR, Z36_ITEM_STATUS, Z36_BOR_TYPE' .
                      ' from <ADMIN_LIBRARY>.Z36 ' .
                      ' where Z36_LOAN_DATE= ' . $dbh->quote($onDate)
        );
$currentloan->execute;
while (my ($id, $date, $hour, $itemStatus, $borStatus) = $currentloan->fetchrow_array) {
        $id=substr($id,0,9);
        print STDOUT " <loan>n";
        print STDOUT "  <lcn>$id</lcn>n";
        my @ISBNs=getISBNs($id);
        foreach my $ISBN (@ISBNs){
                print STDOUT "  <isbn>$ISBN</isbn>n";
        }
        print STDOUT sprintf("  <issue_date>%08d %04d</issue_date>n", $date, $hour);
        print STDOUT "  <return_date></return_date>n";
        print STDOUT "  <item_type>$itemStatus</item_type>n";
        print STDOUT "  <department>$borStatus</department>n";
        print STDOUT " </loan>n";
        $total_number++;
}
$currentloan->finish;

my $pastloans =
        $dbh->prepare('select Z36H_REC_KEY, Z36H_LOAN_DATE, Z36H_LOAN_HOUR, Z36H_RETURNED_DATE, Z36H_RETURNED_HOUR, Z36H
_ITEM_STATUS, Z36H_BOR_TYPE' .
                      ' from <ADMIN_LIBRARY>.Z36H ' .
                      ' where Z36H_LOAN_DATE= ' . $dbh->quote($onDate) . ' or Z36H_RETURNED_DATE= '.$dbh->quote($onDate)
        );
$pastloans->execute;
while (my ($id, $date, $hour, $rdate, $rhour, $itemStatus, $borStatus) = $pastloans->fetchrow_array) {
        $id=substr($id,0,9);
        print STDOUT " <loan>n";
        print STDOUT "  <lcn>$id</lcn>n";
        my @ISBNs=getISBNs($id);
        foreach my $ISBN (@ISBNs){
                print STDOUT "  <isbn>$ISBN</isbn>n";
        }
        print STDOUT sprintf("  <issue_date>%08d %04d</issue_date>n", $date, $hour);
        print STDOUT sprintf("  <return_date>%08d %04d</return_date>n", $rdate, $rhour);
        print STDOUT "  <item_type>$itemStatus</item_type<n";
        print STDOUT "  <department>$borStatus</department<n";
        print STDOUT " </loan>n";
        $total_number++;
}
$pastloans->finish;

print STDOUT " <total_loan_count>$total_number</total_loan_count>n";
print STDOUT "</loan_transactions>n";

sub getISBNs
{
        my ($lcn)= @_;
        $getISBN->execute($lcn);
        my @isbns;
        while(my ($t)=$getISBN->fetchrow_array){
                if($t=~m/^$$a([dw-]{9,13})/){
                        push @isbns, $1;
                }
        }
        return @isbns;
}
Go to Top