##=====================================================================##
## Program name:   fy2012millstats.pl                                  ##
## Version:        1.1                                                 ##
## Author:         Alvin Pollock (apollock@library.berkeley.edu)       ##
## Date completed: 2011                                                ##
##                                                                     ##
## Description:    Takes two Millennium crosstab files, all fiscal     ##
##                 year records and withdrawn records, subtracts the   ##
##                 withdrawn records from all records, and generates   ##
##                 an excel spreadsheet of formats for each location.  ##
##                                                                     ##
## Version:        1.1 (6/29/2012)                                     ##
##                   - Updated spreadsheet title and input files with  ##
##                     new year (2012)                                 ##
##                                                                     ##
## Notes:          Put all crosstab files in the data/ subdirectory.   ##
##                 Output spreadsheet will be written to the out/      ##
##                 subdirectory.                                       ##
##                                                                     ##
## Copyright (c) 2012 The Regents of the University of California.     ##
## All rights reserved.                                                ##
##=====================================================================##
use Spreadsheet::ParseExcel::Simple;
use Spreadsheet::WriteExcel;
use Spreadsheet::WriteExcel::Utility;
use strict 'vars';

##===========================================================##
## Change these values                                       ##
##===========================================================##

my $spreadsheet_title = 'All FY 2012 Items in Millennium';

my $fullxlsfile = "data/FY1011_ItemRecords_Crosstab.xls";
my $wdxlsfile = "data/FY1011_SuppressedOrWD_Crosstab.xls";

##===========================================================##
## End of changes                                            ##
##===========================================================##


my $today = Today ();
my $outfile = "$spreadsheet_title ($today).xls";
$outfile =~ s/\//-/g;
$outfile = "out/$outfile";

#-------------------------------------------------------#
# The crosstab spreadsheets are a little inconsistent   #
# in regards to spacing. Usually there are two spaces   #
# after the initial number, sometimes only one. When    #
# adding new categories to this list, if you are copy-  #
# ing and pasting from the crosstab spreadsheet be sure #
# and DELETE the extra space after the number. There    #
# should only be one space after the initial numbers in #
# the list below.                                       #
#-------------------------------------------------------#

my %type_mapping = (

'1. Volumes' => [
		'1 Monograph - regular loan', 
		'2 Monograph - short-term loan', 
		'20 Atlas',
		'35 Score',
		'3 Bad Code', 
		'0 Undefined', 
		'47 Bad Code',
		'86 Reserves 1 Month',
		'87 Reserves 1 Week', 
		'88 Reserves 1-day', 
		'89 Reserves 1-day no renewal',
		'90 Reserves 2-hour', 
		'91 Reserves 2-hour no renewal', 
		'92 Reserves 2-hour no overnight',
		'93 Reserves 2-hr no renew/overnight', 
		'96 Mellon Research Grant'
	],
'2a. Serials received currently - purchased/2b. Serials received currently - not purchased' => [
		'10 Serial-Periodical (regular loan)', 
		'11 Serial - Periodical (other loan)',
		'15 Newspaper',
	],
'3a. Personal manuscripts' => [
		'27 Personal Manuscript',
	],
'3b. UC archival manuscripts' => [
		'28 UC Archival Manuscript',
 	],
'3c. Other archival materials' => [
		'29 Other Archival Material',
	],
'4. Maps' => [
		'19 Map',
		'70 Aerial Photograph',
	],
'5a. Microfilm reels' => [
		'44 Microfilm',
	],
'5b. Microcards' => [
		'43 Microcard',
	],
'5c. Microfiche' => [
		'42 Microfiche',
	],
'5d. Microprints' => [
		'45 Microprint',
	],
'6. Pamphlets' => [
		'26 Pamphlet',
	],
'8a. Audiodiscs' => [
		'51 Phonograph Record/Audio disc LP',
	],
'8b. Audiocassettes' => [
		'53 Audio Cassette',
	],
'8c. Audioreels' => [
		'54 Audio Reel',
	],
'8d. Compact discs, digital audio' => [
		'52 Compact Disc', 
		'55 Sound Recording, MP3, Undefined',
	],
'9a. Videotapes' => [
		'63 Videotape',
	],
'9b. Videodiscs' => [
		'62 Videodisc',
	],
'9c. Multi-media kits' => [
		'82 Multi-Media Kit',
	],
'9d. Motion pictures' => [
		'64 Film',
	],
'10a. Filmstrips' => [
		'65 Filmstrip',
	],
'10b. Pictorial items' => [
		'67 Visual Material: pictorial',
	],
'10c. 35mm slides' => [
		'66 Slide',
	],
'11a. Computer tapes' => [
		'75 Computer File',
	],
'11b. Monographic CD-ROM discs' => [
		'4 Monograph CD-ROM disc',
	],
'11c. Serial CD-ROM discs' => [
		'14 Serial CD-ROM disc',
	],
'23 Online Resources' => [
		'23 Online Resources',
	],
'68 Visual Material: non-pictorial' => [
		'68 Visual Material: non-pictorial',
	],
'69 Visual Format Undefined' => [
		'69 Visual Format Undefined',
	],
'76 Computer Hardware' => [
		'76 Computer Hardware',
	],
'77 Media Equipment (audio/visual)' => [
		'77 Media Equipment (audio/visual)',
	],
'81 Kit' => [
		'81 Kit',
	],
);

#-------------------------------------------------------#
# Here we double check each entry in the mapping to     #
# make sure there isn't still an extra space.           #
#-------------------------------------------------------#
foreach my $type (keys %type_mapping) {
	foreach my $category (@{$type_mapping{$type}}) {
		die "$category type mapping contains too many spaces.\n" if $category =~ / {2,}/;
	}
}


##===========================================================##
## Full spreadsheet                                          ##
##===========================================================##

my @fullxls = ReadSpreadsheet ($fullxlsfile);

# Rearrange spreadsheet data into a data structure
my %colnos;
my %fulldata;
my %tooltips;
my %meanings;
foreach my $row (@fullxls) {
	my $code = $row->{Code};
	my $meaning = $row->{Meaning};
	my $loc;
	if ($code =~ /^nbt/) {
		$loc = 'nbtb';
		$tooltips{$loc} = 'Bancroft (NRLF)';
	} elsif ($code =~ /^n/) {
		($loc) = $code =~ /^(....)/;
		if ($meaning =~ /^NRLF/) {
			$tooltips{$loc} = ' ' unless $tooltips{$loc};
		} else {
			$tooltips{$loc} = $meaning;
		}
	} elsif ($code =~ /^(tb|tm|tn|tp)$/ or $code =~ /^tu/) {
		$loc = 'tb';
		$tooltips{$loc} = 'Bancroft';
	} else {
		($loc) = $code =~ /^(..)/;
		$tooltips{$loc} = $meaning unless $tooltips{$loc};
	}
	$meanings{$loc} = $meaning;
	push @{$fulldata{$loc}->{original}},  $row;
}

foreach my $key (keys %tooltips) {
	unless ($tooltips{$key} =~ /[a-z]/i) {
		$tooltips{$key} = $meanings{$key};
	}
}

foreach my $loc (sort keys %fulldata) {
	foreach my $row (@{$fulldata{$loc}->{original}}) {
		foreach my $format (sort {$a <=> $b} keys %type_mapping) {
			foreach my $equiv (@{$type_mapping{$format}}) {
				$fulldata{$loc}->{calculated}->{$format} += $row->{$equiv};
			}
		}
	}
}


##===========================================================##
## Withdrawn/Suppressed spreadsheet                          ##
##===========================================================##

my @wdxls = ReadSpreadsheet ($wdxlsfile);

my %wddata;
foreach my $row (@wdxls) {
	my $code = $row->{Code};
	my $loc;
	if ($code =~ /^n/) {
		($loc) = $code =~ /^(....)/;
	} else {
		($loc) = $code =~ /^(..)/;
	}
	push @{$wddata{$loc}->{original}},  $row;
}

foreach my $loc (sort keys %wddata) {
	foreach my $row (@{$wddata{$loc}->{original}}) {
		foreach my $format (sort {$a <=> $b} keys %type_mapping) {
			foreach my $equiv (@{$type_mapping{$format}}) {
				$wddata{$loc}->{calculated}->{$format} += $row->{$equiv};
			}
		}
	}
}


##===========================================================##
## Now subtract the latter from the former                   ##
##===========================================================##

foreach my $loc (sort keys %fulldata) {
	foreach my $format (sort {$a <=> $b} keys %{$fulldata{$loc}->{calculated}}) {
		$fulldata{$loc}->{calculated}->{$format} -= $wddata{$loc}->{calculated}->{$format};
	}
}

##===========================================================##
## Write the results to the spreadsheet                      ##
##===========================================================##

# How to write a calculated cell
# $worksheet->write(5,  1, '=SUM(B2:I2)');

my ($workbook, $worksheet) = create_spreadsheet ($outfile);
my $numberfmt = $workbook->add_format(num_format => '#,##0', 'valign' => 'top');
my $total = $workbook->add_format(num_format => '#,##0', 'border' => 1, 'valign' => 'top');
my $bold = $workbook->add_format('bold' => 1, 'border' => 1);
my $bold_center = $workbook->add_format('bold' => 1, 'border' => 1, 'align' => 'center');
my $final_col = $workbook->add_format(num_format => '#,##0', 'left' => 1, 'right' => 1, 'valign' => 'top');
my $wrap = $workbook->add_format(text_wrap => 1);

## Weird. Instead of writing row by row we write column by column. It looks like the Format cells
## are being overwritten multiple times. I have no idea why I did it this way. Too late to fix it now.
## Anyway, for each Millennium location this writes the Format to column one, and then the stats
## for that location in the appropriate column. We save the last row number to the $last_row variable
## so we can come in later and write the column totals on the row after that one. Ditto we save the
## last location column written to so that we can write the row totals in the column after that one.

my ($last_row, $last_col, %cells, %last_format_cells);
foreach my $loc (sort keys %fulldata) {
	my $row = 2;
	$last_col = $colnos{$loc}; 
	foreach my $format (sort {$a <=> $b} keys %type_mapping) {
		my $format_string = $format;
		if ($format =~ /^2a\. /) {
			$format_string =~ s/\// \&\n/;
			$worksheet->write($row, 0, $format_string, $wrap);
		} else {
			$worksheet->write($row, 0, $format);
		}
		$worksheet->write_number($row, $colnos{$loc}, $fulldata{$loc}->{calculated}->{$format}, $numberfmt);
		$cells{$loc}->{first} = xl_rowcol_to_cell($row, $colnos{$loc}) unless $cells{$loc}->{first};
		$cells{$loc}->{last} = xl_rowcol_to_cell($row, $colnos{$loc});
		$last_row = $row;
		$row++;
	}
}

## Write the column totals in the last row
foreach my $loc (sort keys %fulldata) {
	my $cell_span = $cells{$loc}->{first} . ":" . $cells{$loc}->{last};
	$worksheet->write($last_row + 1,  0, "Totals", $bold);
	$worksheet->write($last_row + 1,  $colnos{$loc}, "=SUM($cell_span)", $total);
}

## Write the row totals in the last column
$worksheet->write(1, $last_col + 1, "Totals", $bold_center);
my $count;
foreach my $format (sort {$a <=> $b} keys %type_mapping) {
	$count++;
	my $start = xl_rowcol_to_cell($count + 1, 1);
	my $end = xl_rowcol_to_cell($count + 1, $last_col);
	my $span = "$start:$end";
	$worksheet->write($count + 1,  $last_col + 1, "=SUM($span)", $final_col);
}

## Finally write the grand total in the last cell of the last row
my $start = xl_rowcol_to_cell($last_row + 1, 1);
my $end = xl_rowcol_to_cell($last_row + 1, $last_col);
my $span = "$start:$end";
$worksheet->write($last_row + 1,  $last_col + 1, "=SUM($span)", $total);

$workbook->close();


##===========================================================##
## Generic read spreadsheet                                  ##
##===========================================================##

sub ReadSpreadsheet {
	my $spreadsheet = shift;
	my $excel = Spreadsheet::ParseExcel::Simple->read ($spreadsheet) or die "Cannot read spreadsheet $spreadsheet: $!\n";
	my @sheets = $excel->sheets or die "Cannot fetch sheet: $!\n";
	my $sheet = $sheets[0];
	my @header;
	if ($sheet->has_data) {
		@header = $sheet->next_row;
	}
	my %cols;
	my $col = 0;
	foreach my $head (@header) {
		$head =~ s/\t/ /g;
		$head =~ s/\s{2,}/ /g;
		$head =~ s/^\s//;
		$head =~ s/\s$//;
		$cols{$col} = $head;
		$col++;
	}
	my @data;
	while ($sheet->has_data) {
		my %rowdata;
		my @row = $sheet->next_row;
		my $colno = 0;
		foreach my $cell (@row) {
			## Clean up data a bit
			$cell =~ s/\t/ /g;
			$cell =~ s/\s{2,}/ /g;
			$cell =~ s/^\s//;
			$cell =~ s/\s$//;
			if ($cell or $cell eq '0') {
				$rowdata{$cols{$colno}} = $cell;
			}
			$colno++;
		}
		if (%rowdata) {
			unless ($rowdata{Code} =~ /^(wb|pl|pf|jo)$/ or $rowdata{Code} =~ /^(wr|z|na|n[c-z])/) {
				push @data,  \%rowdata unless $rowdata{Code} =~ /Total/;
			}
		}
	}
	return @data;
}


##===========================================================##
## Create and format the spreadhseet                         ##
##===========================================================##

sub create_spreadsheet {
	my $filename = shift;
	my $workbook = new Spreadsheet::WriteExcel ($filename);
	my $worksheet = $workbook->add_worksheet ('Sheet1');
	
	$worksheet->freeze_panes(2, 1);
	$worksheet->set_row (0, '26.00');      ## Set the height of the first row
	$worksheet->set_column(0, 0, 50.0);    ## Set the width of the first column
	
	my $col = 1;
	foreach my $loc (sort keys %fulldata) {
		$worksheet->set_column($col, $col, 9.0);
		
		## The way to make tooltips appear when you click on a cell is to set up validation on that cell.
		## We're not really interested in validating the data so the only criteria we set up is that the
		## cell's content be greater than 0.
		
		$worksheet->data_validation(1, $col, {validate => 'length', criteria => '>', value => '0', input_message => $tooltips{$loc}, show_input => 1});
		
		## Here store the appropriate column number for each Millennium location. This hash will be used
		## later when we actually write to the output spreadsheet (seems like there must be a better way
		## to do this).
		
		$colnos{$loc} = $col;
		$col++;
	}
	$worksheet->set_column($col, $col, 11.0);
	## Create a font, size, and alignment format for the header column
	my $header = $workbook->add_format(bold => '1', font => 'Arial', size => '9', align => 'center', bg_color => '42', border => '1');
	my $title_format = $workbook->add_format(valign  => 'vcenter', bold => 1, size => 10, indent => 1);
	$worksheet->write(0, 0, $spreadsheet_title, $title_format);
	
	$worksheet->write(1, 0, "Format", $header);

	## Finally print out the list of Millennium locations on the second row. I think this could have
	## been combined with the validation loop above but I wasn't sure what I was doing.
	
	$col = 1;
	foreach my $loc (sort keys %fulldata) {
		$worksheet->write(1, $col, $loc, $header);
		$col++;
	}
	return ($workbook, $worksheet);
}


##===========================================================##
## Today's date (for naming the output file)                 ##
##===========================================================##

sub Today {
	my ($sec, $min, $hours, $mday, $month, $year) = localtime ();
	$year  = sprintf ("%04d", $year + 1900);
	$month = sprintf ("%02d", $month + 1);
	$mday  = sprintf ("%02d", $mday);
	return ("$year$month$mday");
}


