# selectors-items

# This is run by selectors-items-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 $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(i.id) AS item_number,
i.barcode AS barcode,
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,

i.location_code AS location_code,
DATE(i.last_checkin_gmt) AS last_checkin,
DATE(i.record_creation_date_gmt) AS date_created,
i.icode2 AS icode2,
ic2.name AS icode2_name,
i.item_status_code AS status,
n.name AS status_name,

(
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 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
),

i.year_to_date_checkout_total AS ytd_checkouts,
i.last_year_to_date_checkout_total AS ly_checkouts,
i.last_year_to_date_checkout_total + i.year_to_date_checkout_total AS ytd_plus_ly_checkouts,
ROUND((i.year_to_date_checkout_total + i.last_year_to_date_checkout_total) / (DATE_PART('day', now() - i.record_creation_date_gmt) + 1) * 365) as circ_factor,
i.checkout_total AS checkouts,
i.renewal_total AS renewals,
i.checkout_total + i.renewal_total AS total_circ,

(
SELECT estimated_price as price
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

LIMIT 1
)

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_view  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'

JOIN sierra_view.item_status_property  p
ON i.item_status_code = p.code

JOIN sierra_view.item_status_property_name  n
ON p.id = n.item_status_property_id

JOIN sierra_view.user_defined_icode2_myuser ic2
ON ic2.code = i.icode2

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 $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};
	my $item_number = %$ref{item_number};
	my $barcode = %$ref{barcode};
	my $date_created = %$ref{date_created};
	my $last_checkin = %$ref{last_checkin};
	my $icode2 = %$ref{icode2};
	my $icode2_name = %$ref{icode2_name};
	my $status = %$ref{status};
	my $status_name = %$ref{status_name};
	my $checkouts = %$ref{checkouts};
	my $renewals = %$ref{renewals};
	my $total_circ = %$ref{total_circ};
	my $location_code = %$ref{location_code};
	my $price = %$ref{price};

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



	if ($status eq "-") {$status_name = "-";}

if ($checked_out == 1) {
		$checked_out = "y";
	} else {
		$checked_out = "";
	}


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

	# put an "a" at the end of the item number
	 $item_number = $item_number . "a";



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

	# strip leading zeroes from other_num
	$other_num =~ s/^0//g;

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

	$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,
	                      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,
                              item_number   => $item_number,
                              barcode       => $barcode,
                              price_text    => $price_text,
                              date_created  => $date_created,
                              last_checkin  => $last_checkin,
                              icode2        => $icode2,
                              icode2_name   => $icode2_name,
                              status        => $status,
                              status_name   => $status_name,
                              checkouts     => $checkouts,
                              renewals      => $renewals,
                              total_circ    => $total_circ,
                              location_code => $location_code,
	                      checked_out   => $checked_out);
} 

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


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


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

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/items-$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, 13 );    # Column  A width
$worksheet->set_column( 1, 0, 17 );   
$worksheet->set_column( 2, 0, 10 );   
$worksheet->set_column( 3, 0, 35 );   
$worksheet->set_column( 4, 0, 25 );   
$worksheet->set_column( 5, 0, 15 );   
$worksheet->set_column( 6, 0, 15 );   
$worksheet->set_column( 7, 0, 15 );   
$worksheet->set_column( 8, 0, 22 );   
$worksheet->set_column( 9, 0, 22 );   
$worksheet->set_column( 10, 0, 7 );   
$worksheet->set_column( 11, 0, 7 );   

$worksheet->set_column( 14, 0, 12 ); 
$worksheet->set_column( 15, 0, 10 ); 
$worksheet->set_column( 16, 0, 10 ); 
$worksheet->set_column( 17, 0, 10 ); 
$worksheet->set_column( 18, 0, 10 ); 
$worksheet->set_column( 19, 0, 15 ); 

$worksheet->write( "A1", "item_number", $format2 );
$worksheet->write( "B1", "barcode", $format2 );
$worksheet->write( "C1", "Bib #", $format2 );
$worksheet->write( "D1", "Title", $format2 );
$worksheet->write( "E1", "Author", $format2 );
$worksheet->write( "F1", "ISBN", $format2 );
$worksheet->write( "G1", "Cat Date", $format2 );
$worksheet->write( "H1", "Item Created", $format2 );
$worksheet->write( "I1", "Icode2", $format2 );
$worksheet->write( "J1", "Status", $format2 );
$worksheet->write( "K1", "CKO?", $format2 );
$worksheet->write( "L1", "Holds", $format2 );
$worksheet->write( "M1", "YTD cko", $format2 );
$worksheet->write( "N1", "LY cko", $format2 );
$worksheet->write( "O1", "YTD + LY cko", $format2 );
$worksheet->write( "P1", "Circ factor", $format2 );
$worksheet->write( "Q1", "Checkouts", $format2 );
$worksheet->write( "R1", "Renewals", $format2 );
$worksheet->write( "S1", "Total Circ", $format2 );
$worksheet->write( "T1", "Last Checkin", $format2 );
$worksheet->write( "U1", "Loc", $format2 );
$worksheet->write( "V1", "Price", $format2 );

my $i = 1;
#print the data into the Excel worksheet
foreach $bib (@skeys) {
	$i++;
	$worksheet->write( "A$i", "${$bib}{'item_number'}" );
	$worksheet->write( "B$i", "${$bib}{'barcode'}" );
	$worksheet->write( "C$i", "${$bib}{'bib_num'}" );
	$worksheet->write( "D$i", "${$bib}{'title'}" );
	$worksheet->write( "E$i", "${$bib}{'author'}" );
	$worksheet->write( "F$i", "${$bib}{'isbn'}", $format3 );
	$worksheet->write( "G$i", "${$bib}{'display_date'}");
	$worksheet->write( "H$i", "${$bib}{'date_created'}" );
	$worksheet->write( "I$i", "${$bib}{'icode2_name'}" );
	$worksheet->write( "J$i", "${$bib}{'status_name'}" );
	$worksheet->write( "K$i", "${$bib}{'checked_out'}" );
	$worksheet->write( "L$i", "${$bib}{'holds'}" );
	$worksheet->write( "M$i", "${$bib}{'ytd_checkouts'}" );
	$worksheet->write( "N$i", "${$bib}{'ly_checkouts'}" );
	$worksheet->write( "O$i", "${$bib}{'ytd_plus_ly_checkouts'}" );
	$worksheet->write( "P$i", "${$bib}{'circ_factor'}" );
	$worksheet->write( "Q$i", "${$bib}{'checkouts'}" );
	$worksheet->write( "R$i", "${$bib}{'renewals'}" );
	$worksheet->write( "S$i", "${$bib}{'total_circ'}" );
	$worksheet->write( "T$i", "${$bib}{'last_checkin'}" );
	$worksheet->write( "U$i", "${$bib}{'location_code'}" );
	$worksheet->write( "V$i", "${$bib}{'price_text'}" );
} 

$workbook->close;