# unavailable-items

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

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

#initialize variables
my %sort_string_hash;
my %orderdate_hash;
my $bibcounter;
my $filename;
my $theres_something_on_order;
my $bib;
my $the_url;
my $popularity;

my $bib_loc;
my $itype;
my $mat_types;

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

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

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

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

#file name part, bib loc, itype, mat type; % means all

my ($filenamepart, $bib_loc, $itype, $mat_types) = split /,/, $parameters;
$mat_types =~ 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,

(SELECT lm.name AS location_name
FROM sierra_view.location_myuser  lm
WHERE  i.location_code = lm.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
),

DATE(rm.record_last_updated_gmt) as updated,

(
SELECT content AS bib490a
FROM sierra_view.subfield  s490a
WHERE b.id = s490a.record_id AND s490a.marc_tag = '490' AND s490a.tag = 'a'
LIMIT 1
),

(
SELECT content AS call_number
FROM sierra_view.subfield  call
WHERE b.id = call.record_id AND call.field_type_code = 'c'
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

JOIN sierra_view.record_metadata  rm
ON i.id = rm.id

WHERE

        (SELECT string_agg(location_code, ',')
        FROM sierra_view.bib_record_location  bl
        WHERE bl.bib_record_id = b.id)
        LIKE '%$bib_loc%'
    AND
        CAST(i.itype_code_num AS VARCHAR) LIKE  '$itype'
    AND
        b.bcode2 LIKE '$mat_types'
    AND
        i.icode2 <> 'u'
    AND	(
        ( i.icode2 = 'w' OR i.item_status_code = 'w')
            OR (
                i.item_status_code SIMILAR TO '(m|n|$|z)'
                AND 
                DATE(rm.record_last_updated_gmt) < '$ninetydaysago'
            )
        )

ORDER BY i.location_code, call_number

;


");
  
$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};
	my $updated = %$ref{updated};
	my $bib490a = %$ref{bib490a};
	my $call_number = %$ref{call_number};
	my $location_name = %$ref{location_name};

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

	my $sort_string = $location_code . "." . $call_number;

	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;

	$sort_string_hash{$bib_num}=$sort_string;

	# 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,
                              location_name => $location_name,
                              call_number => $call_number,
                              updated => $updated,
                              bib490a => $bib490a,
                              sort_string => $sort_string,
	                      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);
#@skeys = keys(%circ_factor_hash);

@skeys = sort by_sort_string keys(%sort_string_hash);


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


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

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

sub by_sort_string {
    $sort_string_hash{$b} <=> $sort_string_hash{$a}
}

#************************************************************
# Create an Excel workbook of all the items in each category

#use strict;
#use warnings;
 
use Excel::Writer::XLSX;
 
my $workbook  = Excel::Writer::XLSX->new( "/inetpub/wwwroot/staff/unavailable-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->hide_gridlines(0);
$worksheet->set_landscape(); 

$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, 15 );   
$worksheet->set_column( 9, 0, 15 );   
$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->set_column( 20, 0, 7 ); 
$worksheet->set_column( 21, 0, 25 ); 
$worksheet->set_column( 22, 0, 25 ); 
$worksheet->set_column( 23, 0, 7 ); 
$worksheet->set_column( 24, 0, 10 ); 
$worksheet->set_column( 25, 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", "Loccation", $format2 );
$worksheet->write( "W1", "Call #", $format2 );
$worksheet->write( "X1", "Price", $format2 );
$worksheet->write( "Y1", "Updated", $format2 );
$worksheet->write( "Z1", "Series", $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}{'location_name'}" );
	$worksheet->write( "W$i", "${$bib}{'call_number'}" );
	$worksheet->write( "X$i", "${$bib}{'price_text'}" );
	$worksheet->write( "Y$i", "${$bib}{'updated'}" );
	$worksheet->write( "Z$i", "${$bib}{'490a'}" );
print 	"${$bib}{'sort_string'}\n";
} 

$workbook->close;