#!/usr/local/bin/perl

# findbadleader.pl
# desc: find bib records where leader does not end with '4500'
# desc: specifically, the next to last character is not zero
# wordsettings: MonoSpac821DL 9pt portrait, .5" margins
# end_autoz

use DBI;

### constants
$incr = 50000;  # "size" of subloops through database so process doesn't crash

if ($#ARGV < 0) {usage();}
$outfile = sprintf(">%s", $ARGV[0]);

$fopen = sprintf("Cannot open %s for output\n", $outfile);
open(outfile, $outfile) or die $fopen;

### 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";

$recctr = 0;

### get max bib_id
$sqlquery = "select max(bib_id)
             from wmichdb.bib_data";
$sth = $dbh->prepare($sqlquery) or die "preparing query statement";
$rc = $sth->execute;
$maxbib_id = $sth->fetchrow_array;
$sth->finish;
printf ("Max Bib_ID is %d, incr is %d\n", $maxbib_id, $incr);

### chunk your way through the table
$begbib_id = 0;
$endbib_id = $incr;

while ($begbib_id < $maxbib_id)
{
  chunkthrudb();
  printf ("processed bib through %d\n", $endbib_id);
  $begbib_id = $endbib_id + 1;
  $endbib_id += $incr;
}
printf ("\nRetrieved %d records\n", $recctr);
printf outfile ("\nRetrieved %d records\n", $recctr);

close (outfile);
$dbh->disconnect;


sub chunkthrudb
### process the table one large chunk at a time
{
  $sqlquery = sprintf("select bib_id,
                              record_segment,
                              seqnum
                       from wmichdb.bib_data
                       where bib_id >= %s and bib_id < %s
                       order by bib_id asc, seqnum desc",
                      $begbib_id, $endbib_id);
  $sth = $dbh->prepare($sqlquery) or die "preparing first bib query statement";
  $rc = $sth->execute;

### usual assembly of marc data in reverse order (per sort in query)
###   by bib_id
  $marc = "";
  $oldrec_id = 0;
  $lidx = 0;
  while (($rec_id, $recseg, $seqnum) = $sth->fetchrow_array)
  {
    if ($rec_id != $oldrec_id)
    {
      if ($lidx > 0)
      {
        $wantrec_id = $oldrec_id;
        processrec();
      }
      $oldrec_id = $rec_id;
      $marc = $recseg;
      $lidx++;
    }
    else {$marc = $recseg . $marc;}
  }
  if ($oldrec_id != 0)
  {
    $wantrec_id = $oldrec_id;
    processrec();
  }
  $sth->finish;
}

sub processrec()
### process each marc record
{
  $idx = 0;
  @thistagid = ();
  @thistagdata = ();

  if (substr($marc, 22, 1) ne '0')   # bad leader
  {
    $recctr++;
    $leader = substr($marc, 0, 24);
    $marclen = substr($marc, 0, 5);
    $baseaddr = substr($marc, 12, 5);
    $mptr = 24;
    while ($mptr < ($baseaddr-1))
    {
      $tagid  = substr($marc, $mptr,   3);
      $taglen = substr($marc, $mptr+3, 4);
      $offset = substr($marc, $mptr+7, 5);
      $tagaddr = $baseaddr + $offset - 1;
      $tagdata = substr($marc, $tagaddr, $taglen);

      $tagdata =~ s/\x1f[a-z]/ \|$& /g;        # use " |x " for subfield ind,
      $tagdata =~ s/\x1f//g;                   #  remove original subfield ind,
      $tagdata =~ s/\x1e//g;                   #  remove field ind,
      if (substr($tagdata, 2, 2) eq " |")      #  & remove the "1st" space in the line
        {$tagdata = substr($tagdata, 0, 2) . substr($tagdata, 3);}
      $thistagid[$idx] = $tagid;
      $thistagdata[$idx] = $tagdata;
      $idx++;
      $mptr += 12;
    }

    $sqlquery2 = sprintf("select title
                          from wmichdb.bib_text
                          where bib_id = %s",
                         $wantrec_id);
    $sth2 = $dbh->prepare($sqlquery2) or die "preparing bib title query";
    $rc2 = $sth2->execute;
    $title = $sth2->fetchrow_array;
    $sth2->finish;

    printf outfile ("%7.7d <%-24.24s> %-64.64s\n", $wantrec_id, $leader, $title);

    $sqlquery2 = sprintf("select operator_id,
                                 action_date,
                                 to_char(action_date,'mm/dd/yyyy'),
                                 action_type_id
                          from wmichdb.bib_history
                          where bib_id = %s and
                                action_type_id in (1, 2)
                          order by action_type_id, action_date",
                         $wantrec_id);
    $sth2 = $dbh->prepare($sqlquery2) or die "preparing bib other query";
    $rc2 = $sth2->execute;
    while (($operator, $d, $date, $type) = $sth2->fetchrow_array)
    {
      if ($type == 1) {$typeout = "Created";}
      else            {$typeout = "Updated";}
      printf outfile ("  %s %s %s\n", $typeout, $date, $operator);
    }

    for ($idx=0; $idx < @thistagdata; $idx++)
    {
      if ($thistagid[$idx] eq '035')
        {printf outfile ("  %s %s\n", $thistagid[$idx], $thistagdata[$idx]);}
    }
    printf outfile ("\n");
  }
}


sub usage()
{
  printf ("\nUsage: findbadleader.pl outputfile\n");
  printf ("       Give an output file spec.\n");
  printf ("       Find bad leader bibs.\n");
  exit(0);
}