# recently ordered

# This is run by recently-ordered-init.pl, which gives this script parameters (part
# of the filename to be created, the format, and the location(s).

#use strict;
use DBI;
$Class::DBI::Weaken_Is_Available = 0;

#initialize variables
my %orderdate_hash;
my $bibcounter;
my $filename;
my $theres_something_on_order;
my $bib;
my $the_url;
my $other_num = "";

my ($s, $mi, $h, $d, $m, $y) = localtime(time - 60 * 24 * 60 * 60);
my $sixtydaysago = sprintf("%.4d-%.2d-%.2d", $y + 1900, $m + 1, $d);

my $parameters = "$ARGV[0]";

my ($filenamepart, $format, $locations) = split /,/, $parameters;
$locations =~ s/-/|/g;
 

# set up database stuff

my $dbserver = "192.168.999.999"; # ip address of your Sierre Database server
my $dbport   = "1032";
my $dbname   = "iii";
my $dblogin  = "someusername";  # Sierra username you set up with SQL access
my $dbpw     = 'somepassword';  # password for that user


my $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$dbserver;port=$dbport","$dblogin","$dbpw",{AutoCommit=>1,RaiseError=>1,PrintError=>0});

my $sth = $dbh->prepare ("
SELECT 
      (SELECT 
        s.content
      FROM 
        sierra_view.varfield_view v,
        sierra_view.subfield s
      WHERE
        s.varfield_id = v.id AND
        v.record_num = bib_view.record_num AND
        v.marc_tag = '245' AND
        tag = 'a'
      LIMIT 1
      ) AS title_a,

      (SELECT 
        s.content
      FROM 
        sierra_view.varfield_view v,
        sierra_view.subfield s
      WHERE
        s.varfield_id = v.id AND
        v.record_num = bib_view.record_num AND
        v.marc_tag = '245' AND
        tag = 'b'
      LIMIT 1
      ) AS title_b,

      (SELECT 
        s.content
      FROM 
        sierra_view.varfield_view v,
        sierra_view.subfield s
      WHERE
        s.varfield_id = v.id AND
        v.record_num = bib_view.record_num AND
        v.marc_tag = '245' AND
        tag = 'n'
      LIMIT 1
      ) AS title_n,

      (SELECT s024a.content as bib024a
      FROM sierra_view.subfield  s024a
      WHERE bib_view.id = s024a.record_id AND s024a.marc_tag = '024' AND s024a.tag = 'a'
      LIMIT 1
      ),

      (SELECT s028a.content as bib028a
      FROM sierra_view.subfield  s028a
      WHERE bib_view.id = s028a.record_id AND s028a.marc_tag = '028' AND s028a.tag = 'a'
      LIMIT 1
      ),

      (SELECT 
        s.content
      FROM 
        sierra_view.varfield_view v,
        sierra_view.subfield s
      WHERE
        s.varfield_id = v.id AND
        v.record_num = bib_view.record_num AND
        v.marc_tag = '100' AND
        tag = 'a'
      LIMIT 1
      ) AS author,

       (SELECT STRING_AGG(v.field_content, '; ' order by occ_num)
          FROM sierra_view.varfield_view v
          WHERE  v.record_num = bib_view.record_num AND
            v.record_type_code = 'b' AND 
	    v.marc_tag = '020'
         LIMIT 1
       ) AS isbn,
  bib_view.record_num, 
  order_view.order_date_gmt,
  to_char(order_view.order_date_gmt, 'MM/DD/YYYY') AS display_date
FROM 
  sierra_view.bib_record_order_record_link, 
  sierra_view.bib_record_property, 
  sierra_view.bib_view, 
  sierra_view.order_record_cmf, 
  sierra_view.order_view
WHERE 
  bib_record_order_record_link.bib_record_id = bib_record_property.bib_record_id AND
  bib_record_order_record_link.bib_record_id = bib_view.id AND
  bib_record_order_record_link.order_record_id = order_view.id AND
  order_record_cmf.order_record_id = bib_record_order_record_link.order_record_id AND
  order_view.order_status_code = 'o' AND 
  order_view.order_date_gmt > '$sixtydaysago' AND 
  bib_view.bcode3 = '-' AND
  order_record_cmf.location_code similar to '($locations)';
");
  
$sth->execute() or die "Couldn't execute statement: " . $sth->errstr;
 
my $ref = "";
while($ref = $sth->fetchrow_hashref) {
#        print join (", ", keys %$ref), "\n";
#        print join (", ", values %$ref), "\n";

	my $title_a = %$ref{title_a};
	my $title_b = %$ref{title_b};
	my $title_n = %$ref{title_n};
	my $orderdate = %$ref{order_date_gmt};
	my $rec_num = %$ref{record_num};
	my $author = %$ref{author};
	my $isbn = %$ref{isbn};
	my $other_num = %$ref{bib024a};
	if ($other_num eq "") {$other_num = %$ref{bib028a};}
#print "$other_num\n";

	# strip leading zeroes from other_num
	while (substr($other_num, 0, 1) eq "0" and length($other_num) > 13) {
	    $other_num =~ s/^0//g;
	}

	my $display_date = %$ref{display_date};

	# put a "b" at the beginning of the record number
	$rec_num = "b" . $rec_num;

	# concatenate 245 subfields a, b, and n
	my $title = $title_a . " " . $title_b . " " . $title_n;
	# remove final space and slash
	$title =~ s/\s*$//;
	$title =~ s/\/$//;

	# strip the time off the orderdate
	$orderdate =~ /(\d{4}-\d{2}-\d{2})\s.*/;
	$orderdate = $1;
	$orderdate =~ s/\-//g;
	# $orderdate = "a";

	# strip the |a from the ISBN
	$isbn =~ s/\|a//;

	$orderdate_hash{$rec_num}=$orderdate;
	#print "$orderdate_hash{$rec_num}\n";

	# Build a hash for each bib record
	%{$rec_num}=         (title     => $title,
	                      orderdateh  => $orderdate,
	                      display_date  => $display_date,
	                      isbn        => $isbn,
	                      other_num   => $other_num,
	                      author      => $author);
} 


# Sort orderdate_hash in descending order by orderdate
my @skeys = "";
@skeys = sort by_orderdate keys(%orderdate_hash);
#@skeys = sort { $orderdate_hash->{$a} <=> $orderdate_hash->{$b} } keys(%$orderdate_hash);

use POSIX qw(strftime);
my $today = strftime "%m/%d/%Y", localtime;

#********************************************************

#SECTION 1

# OUTPUT FILE 
#$filename = "/inetpub/wwwroot/recently-ordered-$briefname.html";
$filename = "/inetpub/wwwroot/recently-ordered-$filenamepart.html";
open (OUTFILE,">$filename") || die "Unable to open output file";

# HEADER
# Text between <<END and END will be added to the top of the output file.
# You can change the META content, TITLE, IMG SRC, FONT, date format, and
# add additional text.
if ( $theres_something_on_order eq "n" ) {
     print OUTFILE <<END;
     <p>Nothing on order right now - check the "recently-added" list</p>
END
} 
else {
print OUTFILE <<END;
<head>
<link type="text/css" rel="Stylesheet" href="css/lists.css" />
</head>
<div class="everything">
<p>Updated $today<br />
<p>Click on the titles below to search the catalog</p>
END
# Print the HTML
# First, initialize a record counter
$bibcounter = 0;
foreach $bib (@skeys) {
    $bibcounter++;

my $url = "https://www3.cuyahogafallslibrary.org/graphics/blank.jpg";

if ( $format eq "bk") {
    $url = "https://secure.syndetics.com/index.aspx?isbn=${$bib}{'isbn'}/SC.GIF&client=taylp&type=unbound";
}
else {
    $url = "https://secure.syndetics.com/index.aspx?isbn=/sc.gif&client=taylp&type=unbound&upc=${$bib}{'other_num'}";
    if (${$bib}{'other_num'} eq "") {
	$url = "https://www3.cuyahogafallslibrary.org/graphics/blank.jpg";
    }
}

    print OUTFILE "\n";
    print OUTFILE "<div class=\"bibDiv\">\n";
    print OUTFILE "<table>\n";
    print OUTFILE "<tr class=\"bib\">\n";
    print OUTFILE "    <td class=\"rank\">$bibcounter.</td>\n";
    if ( $format eq "cd") {
        print OUTFILE "    <td class=\"coverCds\"><a href=\"https://encore.cuyahogafallslibrary.org/iii/encore/record/C__R$bib\" target=\"_blank\"><img src=\"$url\"} alt=\"\" /></a></td>\n";
    }
    else {
        print OUTFILE "    <td class=\"coverBooks\"><a href=\"https://encore.cuyahogafallslibrary.org/iii/encore/record/C__R$bib\" target=\"_blank\"><img src=\"$url\"} alt=\"\" /></a></td>\n";
    }
    print OUTFILE "    <td class=\"text\">\n";

    print OUTFILE "        <p class=\"title\"><a href=\"https://encore.cuyahogafallslibrary.org/iii/encore/record/C__R$bib\" target=\"_blank\">${$bib}{'title'}</a></p>\n ";
    print OUTFILE "        <p class=\"author\">${$bib}{'author'}</a></p>\n ";
    print OUTFILE "        <p class=\"numbers\">\n";
    print OUTFILE "            <span class=\"primarySortLabel\">Date Ordered:</span> <span class=\"primarySortNumber\"> ${$bib}{'display_date'}</span>\n";
    print OUTFILE "    </p>\n";
    print OUTFILE "</td>\n";
    print OUTFILE "</tr>\n";
    print OUTFILE "</table>\n";
    print OUTFILE "</div>\n";
    last if ($bibcounter == 100);
}

$dbh->disconnect;

}
print OUTFILE "</div>";
close(OUTFILE);


sub by_orderdate {
    $orderdate_hash{$b} <=> $orderdate_hash{$a}
}