#!/usr/local/bin/perl

### You will need to set this up for your installation.
### Assign appropriate values for the following:
$your_machine_name = "";
$user = "";
$pw = "";
$dbname = "";
### as supplied, this will extract ALL patrons.
### modify the query(ies), if that is not what you want

### if there are more than 3 barcodes for a patron, multiple SIF records
###   will be created until all barcodes are included

# sifextract.pl
# desc: for patrons with last names beginning with "RO", extract patron
# desc: records from voyager and create a SIF file
# wordsettings: 
# end_autoz

# written by Roy Zimmer, Western Michigan University

use DBI;

### connect to database
$dbh = DBI->connect('DBI:Oracle:host=$your_machine_name;sid=LIBR',
                    $user, $pw)
  or die "connecting: $DBI::errstr";

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

$sqlquery = sprintf("select p.last_name,
                            p.first_name,
                            p.middle_name,
                            p.ssan,
                            p.note_count,
                            p.patron_id,
                            to_char(p.expire_date,'yyyy.mm.dd'),
                            to_char(p.purge_date,'yyyy.mm.dd'),
                            to_char(p.registration_date,'yyyy.mm.dd'),
                            to_char(p.create_date,'yyyy.mm.dd'),
                            to_char(p.modify_date,'yyyy.mm.dd'),
                            p.institution_id,
                            p.name_type,
                            p.title,
                            p.historical_charges,
                            p.claims_return,
                            p.self_shelved,
                            p.lost_items,
                            p.late_media_returns,
                            p.historical_bookings,
                            p.cancelled_bookings,
                            p.unclaimed_bookings,
                            p.historical_call_slips,
                            p.historical_distributions,
                            p.historical_short_loans,
                            p.unclaimed_short_loans
                     from $dbname.patron p");

$sth = $dbh->prepare($sqlquery) or die "preparing query statement";
$rc = $sth->execute;


### step through patrons
while (($lastname, $firstname, $mi, $ssn, $notecnt, $patid, $expiredate,
        $purgedate, $regisdate, $createdate, $moddate, $instid, $nametype,
        $title, $histchgs, $claimreturn, $selfshelved, $lostitems, $latmedrtn,
        $histbook, $canbook, $unclaimbook, $histcallslip, $histdist,
        $histshortloan, $unclshortloan) = $sth->fetchrow_array)
{

### get groups and barcodes
  $sqlquery2 = sprintf("select pb.patron_barcode_id,
                               pb.patron_barcode,
                               pg.patron_group_code,
                               pb.barcode_status,
                               to_char(pb.barcode_status_date,'yyyy.mm.dd')
                        from $dbname.patron_group pg, $dbname.patron_barcode pb
                        where pb.patron_id = %s and
                              pb.patron_group_id = pg.patron_group_id", $patid);
  $sth2 = $dbh->prepare($sqlquery2) or die "preparing query statement";
  $rc2 = $sth2->execute;
  $pidx = 0;
  while (($pbi[$pidx], $pbc[$pidx], $pgc[$pidx],
          $pbs[$pidx], $pbd[$pidx]) = $sth2->fetchrow_array) {$pidx++;}
  $sth2->finish;
  if ($pidx != 0) {finishthisrec();}
}
close(outfile);

### clean up at end
$sth->finish;
$dbh->disconnect;


sub finishthisrec()
{
### statistical categories
  $sqlquery2 = sprintf("select psc.patron_stat_code
                        from $dbname.patron_stat_code psc, $dbname.patron_stats ps
                        where ps.patron_id = %s and
                              ps.patron_stat_id = psc.patron_stat_id", $patid);
  $sth2 = $dbh->prepare($sqlquery2) or die "preparing query statement";
  $rc2 = $sth2->execute;
  $sidx = 0;
  while ($statc[$sidx] = $sth2->fetchrow_array) {$sidx++;}
  $sth2->finish;

### count of addresses
  $sqlquery2 = sprintf("select count(*)
                        from $dbname.patron_address pa
                        where pa.patron_id = %s", $patid);
  $sth2 = $dbh->prepare($sqlquery2) or die "preparing query statement";
  $rc2 = $sth2->execute;
  $paddrcount = $sth2->fetchrow_array;
  $sth2->finish;

### data for address(es)
  $sqlquery2 = sprintf("select pa.address_type,
                               pa.address_status,
                               to_char(pa.effect_date,'yyyy.mm.dd'),
                               to_char(pa.expire_date,'yyyy.mm.dd'),
                               pa.address_line1,
                               pa.address_line2,
                               pa.address_line3,
                               pa.address_line4,
                               pa.address_line5,
                               pa.city,
                               pa.state_province,
                               pa.zip_postal,
                               pa.country,
                               pa.address_id,
                               to_char(pa.modify_date,'yyyy.mm.dd')
                        from $dbname.patron_address pa
                        where pa.patron_id = %s", $patid);
  $sth2 = $dbh->prepare($sqlquery2) or die "preparing query statement";
  $rc2 = $sth2->execute;
  $aidx = 0;
  while (($addrtype[$aidx], $addrstat[$aidx], $addreffdate[$aidx], $addrexpdate[$aidx],
          $addrline1[$aidx], $addrline2[$aidx], $addrline3[$aidx], $addrline4[$aidx],
          $addrline5[$aidx], $city[$aidx], $state[$aidx], $zip[$aidx], $country[$aidx],
          $addrid[$aidx], $moddate[$aidx])
         = $sth2->fetchrow_array)
    {$aidx++;}
  $sth2->finish;

### output a patron record the SIF way
### shift off up to 3 patron-barcode data groupings (from arrays)
###   per output record until those arrays are depleted
### this ensures that we get all the patron barcode data
  while ($pidx)
  {
    printf outfile ("%s", "0"x10);        # patron id
    $j = 0;
    while ($j < 3)
    {
      if ($j < @pbc)
      {
        $epbi = shift @pbi;
        $epbc = shift @pbc;
        $epgc = shift @pgc;
        $epbs = shift @pbs;
        $epbd = shift @pbd;
        printf outfile ("%10.10d", $epbi);    # patron barcode id
        printf outfile ("%-25.25s", $epbc);   # patron barcode
        printf outfile ("%-10.10s", $epgc);   # patron group code
        printf outfile ("%1d", $epbs);        # patron barcode status
        printf outfile ("%10s", $epbd);       # barcode modified date
        $pidx--;
      }
      else
      {
        printf outfile ("%s", "0"x10);        # patron barcode id
        printf outfile ("%s", " "x25);        # patron barcode
        printf outfile ("%s", " "x10);        # patron group code
        printf outfile ("0");                 # barcode status
        printf outfile ("%s", " "x10);        # barcode modified date
      }
      $j++;
    }

    printf outfile ("%10.10s", $regisdate);    # registration date
    printf outfile ("%10.10s", $expiredate);   # patron expiration date
    printf outfile ("%10.10s", $purgedate);    # patron purge date
    printf outfile ("%10.10s", $createdate);   # voyager date
    printf outfile ("%10.10s", $moddate);      # voyager updated
    printf outfile ("%s", " "x10);             # library location code
    printf outfile ("%-30.30s", $instid);      # institution id
    printf outfile ("%-11.11s", $ssn);         # social security number

    $j = 0;
    while ($j < 10)   # patron statistical categories
    {
      if ($j < $sidx) {printf outfile ("%-3.3s", $statc[$j]);}
      else {printf outfile ("%s", " "x3);}
      $j++;
    }

    printf outfile ("%1d", $nametype);         # name type
    printf outfile ("%-30.30s", $lastname);    # surname
    printf outfile ("%-20.20s", $firstname);   # first name
    printf outfile ("%-20.20s", $mi);          # middle name
    printf outfile ("%s", " "x10);             # title

    printf outfile ("%10.10d", $histchgs);      # historical charges
    printf outfile ("%5.5d", $claimreturn);     # claims returned count
    printf outfile ("%5.5d", $selfshelved);     # self-shelved count
    printf outfile ("%5.5d", $lostitems);       # lost items count
    printf outfile ("%5.5d", $latmedrtn);       # late media returns
    printf outfile ("%5.5d", $histbook);        # historical bookings
    printf outfile ("%5.5d", $canbook);         # cancelled bookings
    printf outfile ("%5.5d", $unclaimbook);     # unclaimed bookings
    printf outfile ("%5.5d", $histcallslip);    # historical callslips
    printf outfile ("%5.5d", $histdist);        # historical distributions
    printf outfile ("%5.5d", $histshortloan);   # historical short loans
    printf outfile ("%5.5d", $unclshortloan);   # unclaimed short loans

    printf outfile ("%1.1d", $paddrcount);   # address count

    $j = 0;
    while ($j < $aidx)
    {
      printf outfile ("%10.10d", $addrid[$j]);       # address id
      printf outfile ("%1.1d", $addrtype[$j]);       # address type
      printf outfile ("%1.1s", $addrstat[$j]);       # address status
      printf outfile ("%10s", $addreffdate[$j]);     # address begin date
      printf outfile ("%10s", $addrexpdate[$j]);     # address end date
      printf outfile ("%-50.50s", $addrline1[$j]);   # address line 1
      printf outfile ("%-40.40s", $addrline2[$j]);   # address line 2
      printf outfile ("%-40.40s", $addrline3[$j]);   # address line 3
      printf outfile ("%-40.40s", $addrline4[$j]);   # address line 4
      printf outfile ("%-40.40s", $addrline5[$j]);   # address line 5
      printf outfile ("%-40.40s", $city[$j]);        # city
      printf outfile ("%-7.7s", $state[$j]);         # state/province code
      printf outfile ("%-10.10s", $zip[$j]);         # zipcode/postal code
      printf outfile ("%-20.20s", $country[$j]);     # country
      # phone numbers for this address
      for ($k=1; $k<=4; $k++)
      {
        $sqlquery2 = sprintf("select ph.phone_number
                              from $dbname.patron_phone ph
                              where ph.address_id = %s and
                                    ph.phone_type = %s", $addrid[$j], $k);
        $sth2 = $dbh->prepare($sqlquery2) or die "preparing query statement";
        $rc2 = $sth2->execute;
        ($phnumber) = $sth2->fetchrow_array;
        $sth2->finish;
        printf outfile ("%-25.25s", $phnumber);
      }
      printf outfile ("%10s", $moddate[$j]);         # date updated/added
      $j++;
    }
    $sqlquery = sprintf("select note
                         from $dbname.patron_notes
                         where patron_id = %s and
                               note_type = 5", $patronid);
    $sth2 = $dbh->prepare($sqlquery2) or die "preparing query statement";
    $rc2 = $sth2->execute;
    ($note) = $sth2->fetchrow_array;
    $sth2->finish;                                   # note (popup)
    printf outfile ("%c%s", 9, substr($note, 0, 1000));

    printf outfile ("\n");                           # end of record
  }
}