# recently added and most popular and titles for staff

# This is run by recently-added-most-popular-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 $url;
my $popularity;

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 $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
id2reckey(b.id) AS bib_num,
b.title AS title,
s100a.content AS author,
DATE(b.cataloging_date_gmt) AS cat_date,
to_char(b.cataloging_date_gmt, 'MM/DD/YYYY') AS display_date,
(
SELECT content AS bib020a
FROM sierra_view.subfield  s020a
WHERE b.id = s020a.record_id AND s020a.marc_tag = '020' AND s020a.tag = 'a'
LIMIT 1
),

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

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

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

(
SELECT count(c2.id) as checked_out
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

LEFT JOIN sierra_view.checkout  c2
ON i2.id = c2.item_record_id

WHERE b.id = b2.id
),

(
SELECT SUM(i3.year_to_date_checkout_total) AS ytd_checkouts
FROM sierra_view.bib_view  b3

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

JOIN sierra_view.item_record  i3
ON l3.item_record_id = i3.id

WHERE b.id = b3.id
),

(
SELECT SUM(i4.last_year_to_date_checkout_total) AS ly_checkouts
FROM sierra_view.bib_view  b4

JOIN sierra_view.bib_record_item_record_link  l4
ON b4.id = l4.bib_record_id

JOIN sierra_view.item_record  i4
ON l4.item_record_id = i4.id

WHERE b.id = b4.id
),

(
SELECT SUM(i6.last_year_to_date_checkout_total + i6.year_to_date_checkout_total) AS ytd_plus_ly_checkouts
FROM sierra_view.bib_view  b6

JOIN sierra_view.bib_record_item_record_link  l6
ON b6.id = l6.bib_record_id

JOIN sierra_view.item_record  i6
ON l6.item_record_id = i6.id

WHERE b.id = b6.id
),


(
SELECT ROUND(SUM(
(i5.year_to_date_checkout_total + i5.last_year_to_date_checkout_total) / (DATE_PART('day', now() - i5.record_creation_date_gmt) + 1) * 365
)) as circ_factor

 
FROM sierra_view.bib_view  b5

JOIN sierra_view.bib_record_item_record_link  l5
ON b5.id = l5.bib_record_id

JOIN sierra_view.item_view  i5
ON l5.item_record_id = i5.id

WHERE b.id = b5.id
),


(
SELECT SUM(i7.checkout_total) AS checkouts
FROM sierra_view.bib_view  b7

JOIN sierra_view.bib_record_item_record_link  l7
ON b7.id = l7.bib_record_id

JOIN sierra_view.item_record  i7
ON l7.item_record_id = i7.id

WHERE b.id = b7.id
),


(
SELECT SUM(i8.renewal_total) AS renewals
FROM sierra_view.bib_view  b8

JOIN sierra_view.bib_record_item_record_link  l8
ON b8.id = l8.bib_record_id

JOIN sierra_view.item_record  i8
ON l8.item_record_id = i8.id

WHERE b.id = b8.id
),

(
SELECT SUM(i9.checkout_total + i9.renewal_total) AS total_circ
FROM sierra_view.bib_view  b9

JOIN sierra_view.bib_record_item_record_link  l9
ON b9.id = l9.bib_record_id

JOIN sierra_view.item_record  i9
ON l9.item_record_id = i9.id

WHERE b.id = b9.id
)


FROM
sierra_view.bib_view  b

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

JOIN sierra_view.item_record  i
ON l.item_record_id = i.id

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

WHERE
  b.bcode3 = '-'
AND
  i.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 $isbn = "";
	my $other_num = "";

	my $bib_num = %$ref{bib_num};
#print $bib_num;
	my $title = %$ref{title};
	my $author = %$ref{author};
	my $cat_date = %$ref{cat_date};
	my $display_date = %$ref{display_date};
	my $isbn = %$ref{bib020a};
	my $other_num = %$ref{bib024a};
	if ($other_num eq "") {$other_num = %$ref{bib028a};}
#print "$other_num\n";
	my $holds = %$ref{holds};
	my $checked_out = %$ref{checked_out};
	my $ytd_checkouts = %$ref{ytd_checkouts};
	my $ly_checkouts = %$ref{ly_checkouts};
	my $ytd_plus_ly_checkouts = %$ref{ytd_plus_ly_checkouts};
	my $circ_factor = %$ref{circ_factor};
	my $checkouts = %$ref{checkouts};
	my $renewals = %$ref{renewals};
	my $total_circ = %$ref{total_circ};

	# put an "a" at the end of the record number
	my $bib_num_display = $bib_num . "a";

	# take hyphens out of cat date
	$cat_date =~ s/\-//g;
#print $cat_date;


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

        #calculate popularity as holds plus current chekouts
        my $popularity = ($holds + $checked_out + ($ytd_checkouts * 0.0001));
#print "$popularity\n";
#print "$holds\n";
#print "$checked_out\n\n";

	$cat_date_hash{$bib_num}=$cat_date;
	#print "$cat_date_hash{$bib_num}\n";

	$popularity_hash{$bib_num}=$popularity;
	#print "$popularity_hash{$bib_num}\n";

	$circ_factor_hash{$bib_num}=$circ_factor;

	# Build a hash for each bib record
	%{$bib_num}=         (bib_num     => $bib_num,
	                      bib_num_display  => $bib_num_display,
	                      title       => $title,
	                      author      => $author,
	                      cat_date    => $cat_date,
	                      display_date => $display_date,
	                      isbn        => $isbn,
	                      other_num   => $other_num,
	                      holds       => $holds,
	                      popularity  => $popularity,
	                      ytd_checkouts => $ytd_checkouts,
	                      ly_checkouts => $ly_checkouts,
	                      ytd_plus_ly_checkouts => $ytd_plus_ly_checkouts,
	                      circ_factor => $circ_factor,
	                      checkouts => $checkouts,
	                      renewals => $renewals,
	                      total_circ => $total_circ,
	                      checked_out => $checked_out);
} 


# Sort cat_date_hash in descending order by cat_date
my @skeys = "";
@skeys = sort by_catdate keys(%cat_date_hash);

# Sort popularity_hash in descending order by popularity
my @skeys2 = "";
@skeys2 = sort by_popularity keys(%popularity_hash);

# Sort circ_factor_hash in descending order by circ_factor
my @skeys3 = "";
@skeys3 = sort by_circ_factor keys(%circ_factor_hash);


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

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

#SECTION 1 - RECENTLY ADDED

# OUTPUT FILE 
$filename = "/inetpub/wwwroot/recently-added-$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.

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++;

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 Added:</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);
    last if (${$bib}{'cat_date'} < $sixtydaysago);
}

$dbh->disconnect;


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

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

#SECTION 2 MOST POPULAR

# OUTPUT FILES -- one set for public, one for staff with pop number on it

$filename = "/inetpub/wwwroot/most-popular-$filenamepart.html";
open (OUTFILE,">$filename") || die "Unable to open output file";

$filename2 = "/inetpub/wwwroot/most-popular-$filenamepart-staff.html";
open (OUTFILE2,">$filename2") || 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;
<head>
<link type="text/css" rel="Stylesheet" href="css/lists.css" />
</head>
<div class="everything">
<p>Updated $today<br />
Click on title or cover image below to search the catalog (opens in a new tab or window).</p>
END

print OUTFILE2 <<END;
<head>
<link type="text/css" rel="Stylesheet" href="css/lists.css" />
</head>
<div class="everything">
<p>Updated $today<br />
Click on title or cover image below to search the catalog (opens in a new tab or window).<br />
Sorted by popularity (copies currently checked out plus holds), sub-sorted by year-to-date checkouts.
</p>
END

# Print the HTML
# First, initialize a record counter
$bibcounter = 0;
foreach $bib (@skeys2) {
    $bibcounter++;
#print "$bibcounter\n";
#print "${$bib}{'popularity'}\n";


#Find out if syndetics has cover images for the top 50 ytd
#This code is from http://www.justskins.com/forums/getting-remote-image-size-with-image-size-and-lwp-75505.html

use LWP::UserAgent; # This will cover all of them!
use URI::URL;
use HTTP::Request;

# to read the files via http that each url points to.
my $img = "";
if ( $format eq "bk") {
#   $the_url = "http://www.syndetics.com/index.aspx?isbn=${$bib}{'isbn'}/SC.GIF&client=taylp&type=unbound";
    $the_url = "https://secure.syndetics.com/index.aspx?isbn=${$bib}{'isbn'}/SC.GIF&client=taylp&type=unbound";
}
else {
#   $the_url = "http://www.syndetics.com/index.aspx?isbn=/sc.gif&client=taylp&type=unbound&upc=${$bib}{'other_num'}";
    $the_url = "https://secure.syndetics.com/index.aspx?isbn=/sc.gif&client=taylp&type=unbound&upc=${$bib}{'other_num'}";
}
#my $the_url = 'http://syndetics.com/index.aspx?isbn=1435256735/SC.GIF&client=taylp&type=unbound';

my $hdrs = new HTTP::Headers(Accept => 'text/plain', UserAgent => 'MegaBrowser/1.0');

my $url = new URI::URL($the_url);
my $req = new HTTP::Request('GET', $url, $hdrs);
my $ua = new LWP::UserAgent;
my $resp = $ua->request($req);

if ($resp->is_success) {

# If connection is successful the contents of the file
# read will now go into the variable $img
$img = $resp->content;

}
else {

# If connection is not successful then make note of this
print $resp->message;
#$img = "socket_failure";
$url = "http://www3.cuyahogafallslibrary.org/graphics/blank.jpg";
}

    #don't print anythng with popularity < 1
    last if (${$bib}{'popularity'} == 0);
    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 "    </p>\n";
    print OUTFILE "</td>\n";
    print OUTFILE "</tr>\n";
    print OUTFILE "</table>\n";
    print OUTFILE "</div>\n";


    print OUTFILE2 "\n";
    print OUTFILE2 "<div class=\"bibDiv\">\n";
    print OUTFILE2 "<table>\n";
    print OUTFILE2 "<tr class=\"bib\">\n";
    print OUTFILE2 "    <td class=\"rank\">$bibcounter.</td>\n";
    if ( $format eq "cd") {
        print OUTFILE2 "    <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 OUTFILE2 "    <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 OUTFILE2 "    <td class=\"text\">\n";

    print OUTFILE2 "        <p class=\"title\"><a href=\"https://encore.cuyahogafallslibrary.org/iii/encore/record/C__R$bib\" target=\"_blank\">${$bib}{'title'}</a></p>\n ";
    print OUTFILE2 "        <p class=\"author\">${$bib}{'author'}</a></p>\n ";
#    print OUTFILE2 "        <p class=\"numbers\">\n";
#    print OUTFILE2 "            <span class=\"primarySortLabel\">Popularity:</span> <span class=\"primarySortNumber\"> ${$bib}{'popularity'}</span>\n";

    print OUTFILE2 "            <span class=\"primarySortLabel\">

Copies currently checked out: ${$bib}{'checked_out'}; 
Current holds: ${$bib}{'holds'}; 
Year-to-date checkouts:	${$bib}{'ytd_checkouts'}

</span>\n";

    print OUTFILE2 "    </p>\n";
    print OUTFILE2 "</td>\n";
    print OUTFILE2 "</tr>\n";
    print OUTFILE2 "</table>\n";
    print OUTFILE2 "</div>\n";


    last if ($bibcounter == 50);
}
close(OUTFILE);
close(OUTFILE2);


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

#SECTION 3

sub by_catdate {
    $cat_date_hash{$b} <=> $cat_date_hash{$a}
}

sub by_popularity {
    $popularity_hash{$b} <=> $popularity_hash{$a}
}

sub by_circ_factor {
    $circ_factor_hash{$b} <=> $circ_factor_hash{$a}
}

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

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

my $format3 = $workbook->add_format();
$format3->set_num_format();

$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, 30 );    # Column  C (Author) width set to 30
$worksheet->set_column( 3, 0, 15 );    # Column  D (Cat Date) width set to 17
$worksheet->set_column( 4, 0, 15 );    # Column  E (ISBN) width set to 14
$worksheet->set_column( 5, 0, 17 );    # Column  F (Current Checkouts) width set to 17
$worksheet->set_column( 9, 0, 12 );    # Column  J (YTD + LY cko) width set to 12
$worksheet->set_column( 10, 0, 10 );    # Column  K (Circ factor) width set to 10

$worksheet->set_column( 11, 0, 10 );    # Column  L (Checkouts) width set to 10
$worksheet->set_column( 12, 0, 10 );    # Column  M (Renewals) width set to 10
$worksheet->set_column( 13, 0, 10 );    # Column  N (Total Circ) width set to 10



$worksheet->write( "A1", "Bib #", $format2 );
$worksheet->write( "B1", "Title", $format2 );
$worksheet->write( "C1", "Author", $format2 );
$worksheet->write( "D1", "Cat Date", $format2 );
$worksheet->write( "E1", "ISBN", $format2 );
$worksheet->write( "F1", "Checked Out Now", $format2 );
$worksheet->write( "G1", "Holds", $format2 );
$worksheet->write( "H1", "YTD cko", $format2 );
$worksheet->write( "I1", "LY cko", $format2 );
$worksheet->write( "J1", "YTD + LY cko", $format2 );
$worksheet->write( "K1", "Circ factor", $format2 );
$worksheet->write( "L1", "Checkouts", $format2 );
$worksheet->write( "M1", "Renewals", $format2 );
$worksheet->write( "N1", "Total Circ", $format2 );


my $i = 1;
#print the data into the Excel worksheet
foreach $bib (@skeys3) {
	$i++;
	$worksheet->write( "A$i", "${$bib}{'bib_num_display'}" );
	$worksheet->write( "B$i", "${$bib}{'title'}" );
	$worksheet->write( "C$i", "${$bib}{'author'}" );
	$worksheet->write( "D$i", "${$bib}{'display_date'}");
	$worksheet->write( "E$i", "${$bib}{'isbn'}", $format3 );
	$worksheet->write( "F$i", "${$bib}{'checked_out'}" );
	$worksheet->write( "G$i", "${$bib}{'holds'}" );
	$worksheet->write( "H$i", "${$bib}{'ytd_checkouts'}" );
	$worksheet->write( "I$i", "${$bib}{'ly_checkouts'}" );
	$worksheet->write( "J$i", "${$bib}{'ytd_plus_ly_checkouts'}" );
	$worksheet->write( "K$i", "${$bib}{'circ_factor'}" );
	$worksheet->write( "L$i", "${$bib}{'checkouts'}" );
	$worksheet->write( "M$i", "${$bib}{'renewals'}" );
	$worksheet->write( "N$i", "${$bib}{'total_circ'}" );
} 
$workbook->close;