One of our Circulation Department's annual metric is the number of newly issued barcodes. Unfortunately, Voyager does not store the creation date of its barcodes. Instead, it keeps track of the date it was modified. Thus, there is no way to determine the number of new barcodes issued between two arbitrary dates.
However, if you were to take a snapshot of every barcode on one day and then take another snapshot of every barcode on another day, the barcodes that appear in the newest snapshot but not in the older snapshot are determined new within that time frame. The problem with this approach is that date ranges cannot be arbitrarily chosen and must be selected from the dates, in fact the exact moments of time at which the data was collected.
Here is the SQL query used to take a snapshot of every barcode. Setting feedback to 'off' will prevent sqlplus from displaying the number of rows selected. We also set the pagesize to 0 so that headers are not displayed and repeated throughout the file. Both of these changes will make parsing the results easier.
set feedback off
set pagesize 0
select patron_barcode from patron_barcode where patron_barcode is not null;
exit
Rather than having to manually log into sqlplus and run the sql, this can be executed from the command line. The -S argument executes the command in silent mode, which removes most of the fluff output leaving only the query results.
sqlplus -S username/password @allBarcodes.sql
Most likely, you'll want to redirect the output to a file and perhaps make a cron job out of it. You might be able to do it all on a single line, but it would be difficult to manage. For some reason I had to set ORACLE_HOME because whichever shell is used for cronjobs doesn't have it. This value will be different depending on your OS and version of Oracle. Here's my shell script:
#!/bin/sh
LOGDIR=$HOME
LOGFILE=log.barcodes.`date "+%Y%m%d.%H%M"`
DBUSER=scott
DBPASS=tiger
SQL=allBarcodes.sql
ORACLE_HOME=/oracle/app/oracle/product/10.2.0/db_1
export ORACLE_HOME
SID=VGER
${ORACLE_HOME}/bin/sqlplus -S ${DBUSER}/${DBPASS}@${SID} @${SQL} > ${LOGDIR}/${LOGFILE}
You can then set a cronjob on an hourly, daily, weekly, or monthly basis:
# Hourly
01 * * * * /export/home/voyager/barcodeSnapshot.sh
# Daily
01 4 * * * /export/home/voyager/barcodeSnapshot.sh
# Weekly
01 4 * * 0 /export/home/voyager/barcodeSnapshot.sh
# Monthly
01 4 1 * * /export/home/voyager/barcodeSnapshot.sh
Next, we need a script to compare the snapshots and return the number of barcodes in the newer file that do not appear in the older file. This part will not be automated as the input files must be changed within the source. It's not big deal because I usually only need to determine this number once a year. If counts on an hourly basis were required, I would definitely automate this.
I'll call this newBarcodeReport.php and run it from the command line with php -f newBarcodeReport.php:
<?php
// F1 MUST BE THE OLDER LOGFILE
$f1 = 'log.barcodes.20090606.0000';
// F2 MUST BE THE NEWER LOGFILE
$f2 = 'log.barcodes.20090608.1643';
$old = array();
$new = array();
$fd = fopen($f1, 'r');
if ($fd) {
while (!feof($fd)) {
$buffer = trim(fgets($fd, 100));
if (!empty($buffer))
$old[] = $buffer;
}
fclose($fd);
}
$fd = fopen($f2, 'r');
if ($fd) {
while (!feof($fd)) {
$buffer = trim(fgets($fd, 100));
if (!empty($buffer))
$new[] = $buffer;
}
fclose($fd);
}
$results = array_diff($new, $old);
echo count($results) . "\n";
?>
If all that is too much work, you can always fall back on the number of modified barcodes within a specified time period grouped by patron group and barcode status. Whoa, these reports have long names. This data does not accurately reflect the number of new barcodes (e.g. to track outreach success). However, it is an indicator of staff workload required to track patron barcodes. Here is the SQL for that:
SELECT
PATRON_GROUP.PATRON_GROUP_NAME,
PATRON_BARCODE_STATUS.BARCODE_STATUS_DESC,
count(*)
FROM
PATRON_BARCODE
LEFT JOIN PATRON_GROUP ON
PATRON_BARCODE.PATRON_GROUP_ID = PATRON_GROUP.PATRON_GROUP_ID
LEFT JOIN PATRON_BARCODE_STATUS ON
PATRON_BARCODE.BARCODE_STATUS = PATRON_BARCODE_STATUS.BARCODE_STATUS_TYPE
WHERE
PATRON_BARCODE IS NOT NULL AND
PATRON_BARCODE.BARCODE_STATUS_DATE >= to_date('06-01-2008 00:00:00', 'MM-DD-YYYY HH24:MI:SS') AND
PATRON_BARCODE.BARCODE_STATUS_DATE <= to_date('05-31-2009 00:00:00', 'MM-DD-YYYY HH24:MI:SS')
GROUP BY PATRON_GROUP.PATRON_GROUP_NAME, PATRON_BARCODE_STATUS.BARCODE_STATUS_DESC
ORDER BY PATRON_GROUP.PATRON_GROUP_NAME, PATRON_BARCODE_STATUS.BARCODE_STATUS_DESC;