Patron data for %s %s, as of %s %s

#!/usr/local/bin/perl ### patdump.pl ### quick patron data dump ### assuming you've got all the software installed, as mentioned ### in the presentation, you'll also have to edit this program ### to make it work at your installation: ### the database connect statement needs editing ### .table references will have to be changed ### the .ini file location will have to be changed use DBI; @items = (); @sorted = (); print "Enter patron's SSN (digits only): "; $char = ""; while ($char ne "\n") { $char = getc; if ($char ne "\n") {$ssn = $ssn . $char;} } ### get path to desired browser $inifile = "c:\\wmuzimmer\\getprof.ini"; $fopen = sprintf("Cannot open file %s for input\n", $inifile); open(inifile, $inifile) or die $fopen; @iniline = ; close(inifile); chomp @iniline; $browserpath = ''; $j = 0; while (($j < @iniline) and ($browserpath eq '')) { if ($iniline[$j] =~ /^\#/) {$j++;} else {$browserpath = $iniline[$j];} } if ($browserpath eq '') { printf ("No browser path specified. Please edit the .ini file for this program\n"); exit(0); } ### open the html file $pdfile = ">c:\\temp\\patdump.html"; $fopen = sprintf("Cannot open file %s for output\n", substr($pdfile,1)); open(pdfile, $pdfile) or die $fopen; ### connect to database $dbh = DBI->connect('DBI:Oracle:host=whatever.your.host.is;sid=LIBR', 'dbread', 'xxxxx') or die "connecting: $DBI::errstr"; $sqlquery = sprintf("select p.last_name, p.first_name, p.middle_name, p.ssan, to_char(p.create_date,'mm/dd/yyyy'), to_char(p.modify_date,'mm/dd/yyyy'), to_char(p.expire_date,'mm/dd/yyyy'), to_char(p.purge_date,'mm/dd/yyyy'), p.current_charges, p.total_fees_due, p.note_count, p.current_hold_shelf, p.recalls_placed, p.holds_placed, p.items_recalled, p.historical_charges, p.claims_return, p.lost_items, p.current_bookings, p.late_media_returns, p.patron_id, p.modify_operator_id, pg.patron_group_name from wmichdb.patron p, wmichdb.patron_barcode pb, wmichdb.patron_group pg where p.ssan = '%s' and p.patron_id = pb.patron_id and pb.patron_group_id = pg.patron_group_id and pb.barcode_status_date in (select max(pbsd.barcode_status_date) from wmichdb.patron_barcode pbsd where p.patron_id = pbsd.patron_id and pbsd.barcode_status = 1)", $ssn); $sth = $dbh->prepare($sqlquery) or die "preparing query statement"; $rc = $sth->execute; @row = $sth->fetchrow_array; $patron_id = $row[20]; # save for later # "create" today's date ($sec, $min, $hour, $day, $month, $year, $wday, $yday, $isdst) = localtime; $today = sprintf ("%2.2d/%2.2d/%2.2d", $month+1, $day, $year%100); $now = sprintf("%2.2d:%2.2d:%2.2d", $hour, $min, $sec); # do some formatting while (substr($row[0], length($row[0])-1, 1) eq " ") {chop $row[0];} while (substr($row[1], length($row[1])-1, 1) eq " ") {chop $row[1];} $name = $row[0] . ", " . $row[1] . " " . substr($row[2], 0, 1); if ($row[2] eq "") {$name = $row[0] . ", " . $row[1];} $ssn = substr($row[3], 0, 3) . "-" . substr($row[3], 3, 2) . "-" . substr($row[3], 5, 4); if ($row[6] eq '12/31/2382') {$row[6] = "indefinite";} if ($row[7] eq '12/31/2387') {$row[7] = "indefinite";} printf pdfile ("\n\n", 34, 34); printf pdfile ("\n\n"); printf pdfile (" Patron data for %s %s, as of %s %s\n", $row[1], $row[0], $today, $now); printf pdfile ("\n\n\n\n"); printf pdfile ("\n", 34, 34); printf pdfile ("\n\n"); printf pdfile ("

PATRON DATA AS OF %s %s

\n", $today, $now); printf pdfile ("\n", 34, 34, 34, 34, 34, 34); printf pdfile ("\n"); printf pdfile (" \n", 34, 34, $name); printf pdfile (" \n", 34, 34, $ssn); printf pdfile ("\n"); printf pdfile ("\n"); printf pdfile (" \n", 34, 34, $row[22]); printf pdfile (" \n", 34, 34, $row[9]/100); printf pdfile ("\n"); printf pdfile ("

Name: %-40.40s	%11s
Group: %-25.25s	Total Due: $ %7.2f

\n"); printf pdfile ("
\n"); printf pdfile ("\n", 34, 34, 34, 34, 34, 34); printf pdfile ("\n"); printf pdfile (" \n", 34, 34); printf pdfile (" \n", 34, 34, $row[4]); printf pdfile (" \n", 34, 34, $row[6]); printf pdfile (" \n", 34, 34, $row[7]); printf pdfile ("\n"); printf pdfile ("\n"); printf pdfile (" \n", 34, 34); printf pdfile (" \n", 34, 34, $row[5]); printf pdfile (" \n", 34, 34, $row[21]); printf pdfile ("\n"); printf pdfile ("

Created:	%s	Expires: %s	Purge on: %s
Modified: %s	%s	By: %s

\n"); printf pdfile ("
\n"); printf pdfile ("\n", 34, 34, 34, 34, 34, 34); printf pdfile ("\n"); printf pdfile (" \n", 34, 34); printf pdfile (" \n", 34, 34, $row[8]); printf pdfile (" \n"); printf pdfile (" \n"); printf pdfile (" \n", 34, 34); printf pdfile (" \n", 34, 34, $row[19]); printf pdfile ("\n"); printf pdfile ("\n"); printf pdfile (" \n", 34, 34); printf pdfile (" \n", 34, 34, $row[15]); printf pdfile (" \n"); printf pdfile (" \n"); printf pdfile (" \n", 34, 34); printf pdfile (" \n", 34, 34, $row[16]); printf pdfile ("\n"); printf pdfile ("\n"); printf pdfile (" \n", 34, 34); printf pdfile (" \n", 34, 34, $row[11]); printf pdfile (" \n", 34, 34); printf pdfile (" \n", 34, 34, $row[13]); printf pdfile (" \n", 34, 34); printf pdfile (" \n", 34, 34, $row[17]); printf pdfile ("\n"); printf pdfile ("\n"); printf pdfile (" \n", 34, 34); printf pdfile (" \n", 34, 34, $row[12]); printf pdfile (" \n", 34, 34); printf pdfile (" \n", 34, 34, $row[14]); printf pdfile (" \n", 34, 34); printf pdfile (" \n", 34, 34, $row[18]); printf pdfile ("\n"); printf pdfile ("

Current Charges:	%5d			Late Media Returns:	%5d
Historical Charges:	%5d			Claims Returns:	%5d
Shelf Holds:	%5d	Holds Placed:	%5d	Lost Items:	%5d
Recalls Placed:	%5d	Items Recalled:	%5d	Current Bookings:	%5d

\n"); printf pdfile ("

\n"); $sth->finish; $sqlquery = sprintf("select pn.note, nt.note_desc from wmichdb.note_type nt, wmichdb.patron_notes pn where pn.patron_id = '%s' and pn.note_type = nt.note_type", $patron_id); $sth = $dbh->prepare($sqlquery) or die "preparing query statement"; $rc = $sth->execute; $idx = 0; while (@row = $sth->fetchrow_array) { if ($idx == 0) { printf pdfile ("\n", 34, 34, 34, 34, 34, 34); printf pdfile ("\n"); } printf pdfile ("\n", $row[1]); printf pdfile ("\n", $row[0]); $idx++; } if ($idx > 0) {printf pdfile ("

Patron Note

Type: %s

\n

\n");} $sth->finish; $sqlquery = sprintf("select pb.patron_barcode, to_char(pb.barcode_status_date, 'mm/dd/yyyy-hh:mi:ss'), pbs.barcode_status_desc, pg.patron_group_name from wmichdb.patron_barcode pb, wmichdb.patron_group pg, wmichdb.patron_barcode_status pbs where pb.patron_id = '%s' and pb.patron_group_id = pg.patron_group_id and pb.barcode_status = pbs.barcode_status_type order by pb.barcode_status_date", $patron_id); $sth = $dbh->prepare($sqlquery) or die "preparing query statement"; $rc = $sth->execute; $idx = 0; while (@row = $sth->fetchrow_array) { if ($idx == 0) { printf pdfile ("\n", 34, 34, 34, 34, 34, 34); printf pdfile ("\n", 34, 34); printf pdfile ("\n", 34, 34); printf pdfile ("\n", 34, 34); printf pdfile ("\n", 34, 34); } printf pdfile ("\n"); printf pdfile (" \n", 34, 34, $row[0]); printf pdfile (" \n", 34, 34, $row[1]); printf pdfile (" \n", 34, 34, $row[2]); printf pdfile (" \n", 34, 34, $row[3]); printf pdfile ("\n"); $idx++; } if ($idx > 0) {printf pdfile ("

Barcode	Status Date-Time	Status	Group
%-14.14s	%19.19s	%-25.25s	%-25.25s

\n");} $sth->finish; $sqlquery = sprintf("select pa.address_type, pa.address_status, pa.protect_address, to_char(pa.effect_date,'mm/dd/yy'), to_char(pa.expire_date,'mm/dd/yy'), to_char(pa.modify_date,'mm/dd/yy'), pa.modify_operator_id, pa.address_line1, pa.city, pa.state_province, pa.zip_postal, pp.phone_number, pt.phone_desc from wmichdb.patron_address pa, wmichdb.patron_phone pp, wmichdb.phone_type pt where pa.patron_id = '%s' and pa.address_id = pp.address_id and pp.phone_type = pt.phone_type", $patron_id); $sth = $dbh->prepare($sqlquery) or die "preparing query statement"; $rc = $sth->execute; $idx = 0; while (@row = $sth->fetchrow_array) { if ($idx == 0) {printf pdfile ("Address
\n");} if ($row[0] == 1) {$addrtype = 'Permanent';} elsif ($row[0] == 2) {$addrtype = 'Temporary';} elsif ($row[0] == 3) {$addrtype = 'Email';} if ($row[1] == 'N') {$addrstat = 'Normal';} else {$addrstat = 'Hold Mail';} if ($row[2] == 'N') {$addrprot = 'No';} else {$addrprot = 'Yes';} $addrout = sprintf("%s, %s %s", $row[8], $row[9], $row[10]); printf pdfile ("Type: %s Status: %s Protected: %s

\n", $addrtype, $addrstat, $addrprot); printf pdfile ("\n", 34, 34, 34, 34, 34, 34); printf pdfile ("\n"); printf pdfile (" \n", $row[3]); printf pdfile (" \n", $row[4]); printf pdfile ("\n"); printf pdfile ("\n"); printf pdfile (" \n", $row[5]); printf pdfile (" \n", $row[6]); printf pdfile ("\n"); printf pdfile ("

Effective: %s	Expires: %s
Modified: %s	By: %s

\n"); printf pdfile ("
\n"); printf pdfile ("\n", 34, 34, 34, 34, 34, 34); printf pdfile ("\n"); printf pdfile (" \n", $row[7]); printf pdfile (" \n", 34, 34, $row[11]); printf pdfile ("\n"); printf pdfile ("\n"); printf pdfile (" \n", $addrout); printf pdfile (" \n", 34, 34, $row[12]); printf pdfile ("\n"); printf pdfile ("

%-50.50s	%s
%-50.50s	Type: %s

\n"); printf pdfile ("

\n"); $idx++; } $sth->finish; $sqlquery = sprintf("select ib.item_barcode, bt.title, to_char(ct.charge_date,'mm/dd/yy'), to_char(ct.charge_due_date,'mm/dd/yy') from wmichdb.item_barcode ib, wmichdb.circ_transactions ct, wmichdb.bib_item bi, wmichdb.bib_text bt where ct.patron_id = '%s' and ct.item_id = ib.item_id and ct.item_id = bi.item_id and bi.bib_id = bt.bib_id", $patron_id); $sth = $dbh->prepare($sqlquery) or die "preparing query statement"; $rc = $sth->execute; $idx = 0; while (@row = $sth->fetchrow_array) { if ($idx == 0) { printf pdfile ("\n", 34, 34, 34, 34, 34, 34); printf pdfile ("\n", 34, 34); printf pdfile ("\n", 34, 34); printf pdfile ("\n", 34, 34); printf pdfile ("\n", 34, 34) } printf pdfile ("\n"); printf pdfile (" \n", 34, 34, $row[0]); printf pdfile (" \n", 34, 34, $row[1]); printf pdfile (" \n", 34, 34, $row[2]); printf pdfile (" \n", 34, 34, $row[3]); printf pdfile ("\n"); $idx++; } if ($idx > 0) {printf pdfile ("

Barcode	Title	Charge Date	Due Date
%s	%s	%s	%s

\n\n");} ### clean up at end and finish output $sth->finish; $dbh->disconnect; printf pdfile ("\n"); printf pdfile ("\n\n"); close(pdfile); $command = sprintf ("%c%s%c c:\\temp\\patdump.html", 34, $browserpath, 34); system($command); sub reformatdate { my ($indate) = @_; my ($outdate, $found, $idx); my @monthin = qw(JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC); my @monthout = qw(01 02 03 04 05 06 07 08 09 10 11 12); $found = 0; $idx = 0; while ((not $found) and ($idx <= @monthin)) { if (substr($indate, 3, 3) eq $monthin[$idx]) { $outdate = $monthout[$idx] . "/" . substr($indate, 0, 2) . "/" . substr($indate, 7, 2); $found = 1; } $idx++; } return $outdate; }