#!/usr/bin/perl
use strict;
use warnings;
use Spreadsheet::WriteExcel;
use Text::CSV;
use POSIX qw(strftime);
use Scalar::Util qw(looks_like_number);

my $date = strftime "%m/%d/%Y", localtime;

my $num_args = $#ARGV + 1;
if ($num_args != 2)
{
	print "\nUsage: convertBacklogXLS.pl inputFilename outputFilename\n";
	exit -1;
}
my $inputFilename = $ARGV[0];
my $outputFilename = $ARGV[1];

my $csv = Text::CSV->new();
open(CSV, "<", $inputFilename) or die("Can not open input file: $!");

my $workbook = Spreadsheet::WriteExcel->new($outputFilename);
$workbook->compatibility_mode();

my $worksheet = $workbook->add_worksheet("Backlog");

$worksheet->set_landscape();
$worksheet->set_margins(0.79);
$worksheet->hide_gridlines(1);

my $header1 = $workbook->add_format(
				font  => 'Arial',
				size  => 12,
				color => 'black',
				bold  => 0,
				align => 'left',
			);
my $header2 = $workbook->add_format(
				font  => 'Arial',
				size  => 12,
				color => 'black',
				bold  => 0,
				align => 'center',
			);
my $columnHeaderLeft = $workbook->add_format(
				font  => 'Arial',
				size  => 10,
				color => 'black',
				bold  => 1,
				align => 'left',
			);
my $columnHeaderRight = $workbook->add_format(
				font  => 'Arial',
				size  => 10,
				color => 'black',
				bold  => 1,
				align => 'right',
			);
my $currencyFormat = $workbook->add_format(
				font  => 'Arial',
				size  => 10,
				color => 'black',
				bold  => 0,
				num_format => '$0.00',
			);
my $textFormat = $workbook->add_format(
				font  => 'Arial',
				size  => 10,
				color => 'black',
				bold  => 0,
			);
my $textSmallFormat = $workbook->add_format(
				font  => 'Arial',
				size  => 8,
				color => 'black',
				bold  => 0,
			);
my $textSmallWrapFormat = $workbook->add_format(
				font  => 'Arial',
				size  => 8,
				color => 'black',
				bold  => 0,
				align => 'vjustify',
			);
my $mergedTextFormat = $workbook->add_format(
				font  => 'Arial',
				size  => 10,
				color => 'black',
				bold  => 0,
			);
my $text1Format = $workbook->add_format(
				font  => 'Arial',
				size  => 10,
				color => 'black',
				bold  => 0,
				num_format => '@',
			);
my $numberFormat = $workbook->add_format(
				font  => 'Arial',
				size  => 10,
				color => 'black',
				bold  => 0,
				num_format => '0',
			);

# Set column heights
$worksheet->set_row(0, 20);

$worksheet->merge_range('A1:B1', "Backlog - $date", $header1);

my $row = 2;
my $firstRowId = $row + 2;
my @columnWidths = ();
my @alphabet = ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
	'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');

while(<CSV>)
{
	if ($csv->parse($_)) {
		my @field = $csv->fields();

		for(my $col = 0;$col < @field;$col++)
		{
			if($row == 2)
			{
				my $width = string_width($field[$col]);
				if($col == 3)
				{
					$width = 40;
				}
				push(@columnWidths, $width);
			}
			
			my $format = $numberFormat;

			if($row == 2)
			{
				$format = $columnHeaderLeft;
			}
			elsif($col >= 1 && $col <= 3)
			{
				$format = $textFormat;
			}
			else
			{
				$format = $numberFormat;
			}
			if($field[0] eq 'Total' && looks_like_number($field[$col]))
			{
				$worksheet->write_formula($row, $col, '=SUM(' . $alphabet[$col] . $firstRowId . ':' . $alphabet[$col] . $row . ')', $numberFormat);
			}
			elsif($field[0] eq 'Shortage' && $col >= 4)
			{
				$worksheet->write_formula($row, $col, '=IF(' . $alphabet[$col] . ($row - 1) . ' > ' . $alphabet[$col] . $row . ',' . $alphabet[$col] . ($row - 1) . '-' . $alphabet[$col] . $row . ',"")', $numberFormat);
			}
			else
			{
				$worksheet->write($row, $col, $field[$col], $format);
			}
		}
		$row++;
	}
}
my $col = 0;
for my $width (@columnWidths)
{
	$worksheet->set_column($col, $col, $width) if $width;
	$col++;
}
$workbook->close();
exit;

sub string_width {
    return 1.4 * length $_[0];
}