# purchase alerts

# This is run by purchase-alerts-init.pl, which gives this script parameters (part
# of the filename to be created, the bib location, and the mat type.

#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 $filenamepart;
my $bib_loc;
my $mat_type;

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]";
#print $parameters;

my ($filenamepart, $bib_loc, $mat_types) = split /,/, $parameters;
$mat_types =~ s/-/|/g;
$bib_loc =~ s/-/|/g;
#print "$filenamepart\n";
#print "$bib_loc-";
#print "$mat_types-";

# 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
id2reckey(b.id) AS bib_num,
b.title AS title,
s100a.content AS author,

(
SELECT count(h.id) as holds
FROM sierra_view.hold  h
WHERE b.id = h.record_id
),

--count of items with icode2 <> u
(
SELECT count(i2.id) as items
FROM sierra_view.bib_view  b2

JOIN sierra_view.bib_record_item_record_link  l2
ON b2.id = l2.bib_record_id

JOIN sierra_view.item_record  i2
ON l2.item_record_id = i2.id

WHERE b.id = b2.id
AND i2.icode2 <> 'u'
AND i2.item_status_code NOT SIMILAR TO '(m|n|$|z|x|w)'
),

--sum of copies of orders where order status = o
(
SELECT sum(cmf3.copies) as copies_on_order
FROM sierra_view.bib_view  b3

JOIN sierra_view.bib_record_order_record_link  l3
ON b3.id = l3.bib_record_id

JOIN sierra_view.order_record  o3
ON l3.order_record_id = o3.record_id

JOIN sierra_view.order_record_cmf  cmf3
ON l3.order_record_id = cmf3.order_record_id

WHERE b.id = b3.id
AND o3.order_status_code = 'o'
),

b.bcode2 AS bib_mat_type,
b.bcode3 AS suppression_code


FROM
sierra_view.bib_view  b

JOIN sierra_view.hold  h
ON b.id = h.record_id

JOIN sierra_view.bib_record_location  l
ON b.id = l.bib_record_id

LEFT JOIN sierra_view.subfield  s100a
ON b.id = s100a.record_id AND s100a.marc_tag = '100' AND s100a.tag = 'a'

WHERE
  l.location_code similar to '$bib_loc'
AND
  b.bcode2 similar to '$mat_types';


");

$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 $bib_num = %$ref{bib_num};
	my $title = %$ref{title};
	my $author = %$ref{author};
	my $holds = %$ref{holds};
	my $items = %$ref{items};
	my $suppression_code = %$ref{suppression_code};
	my $copies_on_order = %$ref{copies_on_order};
	my $total_items = ($items + $copies_on_order);
	my $bib_mat_type = %$ref{bib_mat_type};
	my $holds_per_item;

	if ($total_items == 0) {
		$holds_per_item = 1000;
	}
	else {
		$holds_per_item = $holds / $total_items;
	}

	#truncate
	$author = substr( $author, 0, 30 );
	$title = substr( $title, 0, 50 );

	# truncate numbers on the right of the decimal point
	my $d = 2;          # Num of right hand digits to keep
	my ($lhs,$rhs) = split /\./,$holds_per_item,2;
	$holds_per_item_text = join '.',$lhs,substr($rhs,0,$d);

	if ($rhs eq "") {
		$holds_per_item_text = $lhs;
	}
	else {
		$holds_per_item_text = join '.',$lhs,substr($rhs,0,$d);
	}

	if ($total_items == 0) {
	     $holds_per_item_text = "no items"; 
	}

	# put an "a" at the end of the bib number
	$bib_num = $bib_num . "a";

	my $mat_type_label;

	if ($bib_mat_type eq "1") {
		$mat_type_label = "Blu-ray";
	} elsif ($bib_mat_type eq "2") {
		$mat_type_label = "Playaway";
	} elsif ($bib_mat_type eq "3") {
		$mat_type_label = "Playaway View";
	} elsif ($bib_mat_type eq "4") {
		$mat_type_label = "MP3 book";
	} elsif ($bib_mat_type eq "6") {
		$mat_type_label = "Launchpad";
	} elsif ($bib_mat_type eq "a") {
		$mat_type_label = "Book";
	} elsif ($bib_mat_type eq "g") {
		$mat_type_label = "DVD";
	} elsif ($bib_mat_type eq "j") {
		$mat_type_label = "CD";
	} elsif ($bib_mat_type eq "m") {
		$mat_type_label = "CD-ROM";
	} elsif ($bib_mat_type eq "p") {
		$mat_type_label = "mixed mat";
	} elsif ($bib_mat_type eq "w") {
		$mat_type_label = "Paperback";
	} elsif ($bib_mat_type eq "x") {
		$mat_type_label = "Large print";
	} elsif ($bib_mat_type eq "y") {
		$mat_type_label = "Book on CD";
	}	

#	if ($holds_per_item >= 2) {

		$holds_per_item_hash{$bib_num}=$holds_per_item;
		#print "$holds_per_item_hash{$bib_num}\n";
	
		# Build a hash for each bib record
		%{$bib_num}=         (bib_num         => $bib_num,
		                      suppression_code => $suppression_code,
		                      title           => $title,
		                      author          => $author,
		                      mat_type_label  => $mat_type_label,
	        	              holds           => $holds,
	                	      items           => $items,
		                      copies_on_order => $copies_on_order,
	        	              total_items     => $total_items,
	        	              holds_per_item_text => $holds_per_item_text,
	                	      holds_per_item  => $holds_per_item);
#	}

} 

# Sort holds_per_item_hash in descending order by holds_per_item
my @skeys = "";
@skeys = sort by_holds_per_item keys(%holds_per_item_hash);

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

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

# OUTPUT FILE 
open (OUTFILE,">/inetpub/wwwroot/staff/purchase-alerts-$filenamepart.html") || 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.

print OUTFILE <<END;
<html>
<head>
<title>Titles with Holds - $filenamepart</title>
</head>

<p>
<center><font size="+2" COLOR="010688"><B> $filenamepart Titles with Holds</B></FONT></CENTER>
<P>
<center><?php echo date ("F d, Y ", getlastmod()); ?></center>
<center>$today</center><br />
<center><a href="http://www3.cuyahogafallslibrary.org/staff/purchase-alerts-$filenamepart.xlsx">Download Excel Worbook</a></center>
<HR>
<CENTER>Click on the titles below to search the catalog.<br />
Titles that are not hyperlinked are suppressed.</CENTER><P>
<TABLE BORDER CELLPADDING=5>
<TR ALIGN=LEFT>
    <TH>Bib #</TH>
    <TH>Title</TH>
    <TH>Mat<br />Type</TH>
    <TH>Author</TH>
    <TH>#Holds</TH>
    <TH>Items<br />Held</TH>
    <TH>Items<br />Ordered</TH>
    <TH>Total<br />Items</TH>
    <TH>Holds / Items</TH>
</TR>
END



# Print the HTML
foreach $bib (@skeys) {

    print OUTFILE "\n<TR>";
    print OUTFILE "\n    <TD>${$bib}{'bib_num'}</TD>";

	if (${$bib}{'suppression_code'} eq "n") {
	    print OUTFILE "\n    <TD>${$bib}{'title'}</TD>";
	}
	else {
	    print OUTFILE "\n    <TD><A HREF=\"http://catalog.cuyahogafallslibrary.org/record=$bib\">${$bib}{'title'}</A></TD>";
	}

    print OUTFILE "\n    <TD>${$bib}{'mat_type_label'}</TD>";
    print OUTFILE "\n    <TD>${$bib}{'author'}</TD>";
    print OUTFILE "\n    <TD>${$bib}{'holds'}</TD>\n"; 
    print OUTFILE "\n    <TD>${$bib}{'items'}</TD>\n"; 
    print OUTFILE "\n    <TD>${$bib}{'copies_on_order'}</TD>\n"; 
    print OUTFILE "\n    <TD>${$bib}{'total_items'}</TD>\n"; 
    print OUTFILE "\n    <TD>${$bib}{'holds_per_item_text'}</TD>\n"; 
    print OUTFILE "</TR>\n";
}

# FOOTER
# Text between <<END and END will be added to the end of the output file.
# You can add footer text, mailto links, etc. before the </BODY> tag.

print OUTFILE <<END;
</TABLE>
</BODY>
</HTML>
END

close(OUTFILE);

sub by_holds_per_item {
    $holds_per_item_hash{$b} <=> $holds_per_item_hash{$a}
}


#************************************************************
# Create an Excel workbook

#use strict;
#use warnings;
 
use Excel::Writer::XLSX;
 
my $workbook  = Excel::Writer::XLSX->new( "/inetpub/wwwroot/staff/purchase-alerts-$filenamepart.xlsx" );
my $worksheet = $workbook->add_worksheet();
my $format2 = $workbook->add_format();
$format2->set_bold();

$worksheet->set_column( 0, 0, 10 );    # Column  A (Bib #)  width set to 10
$worksheet->set_column( 1, 0, 40 );    # Column  B (Title)  width set to 40
$worksheet->set_column( 2, 0, 10 );    # Column  C (Mat Type) width set to 10
$worksheet->set_column( 3, 0, 30 );    # Column  D (Author) width set to 30

$worksheet->write( "A1", "Bib #", $format2 );
$worksheet->write( "B1", "Title", $format2 );
$worksheet->write( "C1", "Mat Type", $format2 );
$worksheet->write( "D1", "Author", $format2 );
$worksheet->write( "E1", "#Holds", $format2 );
$worksheet->write( "F1", "Items Held", $format2 );
$worksheet->write( "G1", "Items Ordered", $format2 );
$worksheet->write( "H1", "Total Items", $format2 );
$worksheet->write( "I1", "Holds / Items", $format2 );

my $i = 1;
#print the data into the Excel worksheet
foreach $bib (@skeys) {
	$i++;
	$worksheet->write( "A$i", "${$bib}{'bib_num'}" );
	$worksheet->write( "B$i", "${$bib}{'title'}" );
	$worksheet->write( "C$i", "${$bib}{'mat_type_label'}" );
	$worksheet->write( "D$i", "${$bib}{'author'}" );
	$worksheet->write( "E$i", "${$bib}{'holds'}" ); 
	$worksheet->write( "F$i", "${$bib}{'items'}" ); 
	$worksheet->write( "G$i", "${$bib}{'copies_on_order'}" ); 
	$worksheet->write( "H$i", "${$bib}{'total_items'}" ); 
	$worksheet->write( "I$i", "${$bib}{'holds_per_item_text'}" ); 
} 
$workbook->close;