#!/usr/local/bin/perl

# retirees.pl
# desc: get retirees who've borrowed within the past year
# desc: check their barcodes
# wordsettings: 
# end_autoz

use strict;
use DBI;

my ($machine, $user, $pw, $sid, $dbh, $sqlquery, $sth, $rc);
my ($sqlquery2, $sth2, $rc2, $fopen, $outfile, $inst_id);
my ($lname, $fname, $mi, $name, $pgroup, $patron_id, $barcode, $bcstatdate, $bcstat);

### connect to database
open(pwfile, "/usr/local/bin/sqlpw.cfg") or die "Cannot open password file";
$pw = <pwfile>;
close(pwfile);
chomp $pw;
($machine, $user, $pw, $sid) = split /\//, $pw;
$dbh = DBI->connect("DBI:Oracle:host=$machine;sid=$sid", $user, $pw)
  or die "connecting: $DBI::errstr";

### retrieve patrons
$outfile = ">retirees.rpt";
$fopen = sprintf("Cannot open file %s for output\n", $outfile);
open(outfile, $outfile) or die $fopen;

$sqlquery = sprintf("select distinct
                            p.last_name,
                            p.first_name,
                            p.middle_name,
                            p.patron_id,
                            p.institution_id,
                            pg.patron_group_code
                     from patron p, patron_barcode pb, patron_group pg,
                          circ_transactions ct, circ_trans_archive cta
                     where p.patron_id = pb.patron_id and
                           pb.barcode_status = 1 and
                           pb.patron_group_id = pg.patron_group_id and
                           (pg.patron_group_id = 18 or pg.patron_group_id = 19) and
                           ((p.patron_id = ct.patron_id and
                            to_char(ct.charge_date,'yyyymmdd') >= to_char(sysdate-365,'yyyymmdd')) or
                            (p.patron_id = cta.patron_id and
                            to_char(cta.charge_date,'yyyymmdd') >= to_char(sysdate-365,'yyyymmdd')))");
$sth = $dbh->prepare($sqlquery) or die "preparing query statement";
$rc = $sth->execute;

while (($lname, $fname, $mi, $patron_id, $inst_id, $pgroup) = $sth->fetchrow_array)
{
  $lname =~ s/\s+$//;
  $fname =~ s/\s+$//;
  $name = $lname . ", " . $fname . " " . substr($mi, 0, 1);
  if ($mi eq "") {$name = $lname . ", " . $fname;}
  printf outfile ("%-40.40s %-15.15s pgroup: %-10.10s\n", $name, $inst_id, $pgroup);
  $sqlquery2 = sprintf("select pb.patron_barcode,
                               to_char(pb.barcode_status_date, 'mm/dd/yyyy'),
                               pbs.barcode_status_desc
                        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);
  $sth2 = $dbh->prepare($sqlquery2) or die "preparing query statement";
  $rc2 = $sth2->execute;
  while (($barcode, $bcstatdate, $bcstat) = $sth2->fetchrow_array)
  {
    printf outfile ("   %14.14s %10.10s %-25.25s\n", $barcode, $bcstatdate, $bcstat);
  }
  print outfile "\n";
  $sth2->finish;
}
close(outfile);