#!/usr/bin/perl -w

    use strict;
    use Spreadsheet::ParseExcel;
	use switch;

    my $filename = $ARGV[0];
    my $parser   = Spreadsheet::ParseExcel->new();
    my $workbook = $parser->Parse($filename);
    my $worksheet = $workbook->worksheet('Periodic');
    my $characters_before_country = "15";
    my $country = substr $filename, $characters_before_country, (index($filename, '.') - $characters_before_country);

    my ( $row_min, $row_max ) = $worksheet->row_range();
    my ( $col_min, $col_max ) = $worksheet->col_range();
    my $column = 3;
	my @warehouses = ();
	my @economical = ( "Rental revenue", "Handling revenue", "Freight revenue", "Other sales revenue", "Rental margin",
						"Handling margin 1", "EBIT");
	my $readState = "";
	my $nextCell = "";
	my $month_year = "";
	my $month = "";
	my $year = "";
	my $amount = "";
	
	# Haetaan Warehouse solujen sijainti ja talletetaan taulukkoon.
	sub haeVarastot {
		my @warehouse = [];
		my $i = 0;
		for my $row ( $row_min .. $row_max ) {
			my $cell = $worksheet->get_cell( $row, 0 );
			if (defined $worksheet->{Cells}[$row][0]) {
				if ($worksheet->{Cells}[$row][0]->Value eq "Warehouse:")
				{
					print "Warehouse founded on row: " . $row . " column: 0\n";
					print "Warehouse name: " . $worksheet->{Cells}[$row][1]->Value . "\n";
					$warehouse[$i] = $row;
					$i++;
				}
			}
		}
		return @warehouse;
	}

	# Haetaan yhden kuukauden tiedot
	sub haeKuukausi {

	}

	sub yhdistaKentat {
		# Loopataan kaikki rowit läpi ja etsimään etuliitteitä celleille.
		for my $row ( $row_min .. $row_max ) {
			#Haetaan joka rowin eka cell.
			my $cell = $worksheet->get_cell( $row, 0 );
			if (defined $cell) {
				
				# Jos cellissa on tekstiä...
				if ($cell->unformatted() ne "")
				{
					# Erikoistapaus Economical.
					if ($cell->unformatted() eq "Economical")
					{
						$readState = "Economical";
						next;
					}
					my $nextCell2 = $worksheet->get_cell($row, 1);
					if (defined $nextCell2)
					{
					$nextCell = $nextCell2->unformatted();
					}
					# ... Tarkistetaan onko viereisessä cellissä tekstiä.
					if ($nextCell ne "")
					{
						$readState = $cell->unformatted(); # Oli, otetaan teksti talteen etuliitteeksi.
					} else { $readState = ""; } # Viereinen oli tyhjä, unohdetaan teksti.

				}
				
				my $nextCell2 = $worksheet->get_cell($row + 1, 1);
			}


			
				$cell = $worksheet->get_cell($row, 1);
				if (defined $cell) {
					# Etuliite löytynyt ja viereisessä cellissä tekstiä.
					if ($readState ne "" &&  $cell->unformatted() ne "" && $readState ne "Warehouse:")
					{
					# Tulostetaan molemmat ja erotinmerkki perään.
					print $readState ."-" . $cell->unformatted() . " |";
					}
					# Molemmat cellit olivat tyhjiä, hypätään loopin seuraavaan.
					} else { next; } 
				    # pick up amount of the month
	                for my $col ( $column .. $column ) {
						my $cell = $worksheet->get_cell( $row, $col );
						#next unless $cell;
	                    if (defined $worksheet->{Cells}[$row][0])
	                    {
	                        # pick up month and year
	                        if  ( $worksheet->{Cells}[$row][0]->Value eq "Warehouse:" )
	                            {
									$month_year = $worksheet->{Cells}[$row][$col]->Value;
									$month = substr $month_year, 0, 3;
									my %mon2num = ("Jan", "1", "Feb", "2", "Mar", "3", "Apr", "4", "May", "5", "Jun", "6", "Jul", "7", "Aug", "8", "Sep", "9", "Oct", "10", "Nov", "11", "Dec", "12");
									$month = $mon2num{ substr($month, 0, 3) };
									$year = "20" . substr $month_year, 4, 2;
								}
						}
						if (defined $worksheet->{Cells}[$row][$col])
						{
							# cell on tyhjä?
							if ($worksheet->get_cell($row, $col)->unformatted() eq "")
							{
								print "|\n";
								next;
							}
							# cell ei ole tyhjä?
							if ($worksheet->get_cell($row, $col)->unformatted() ne "")
							{
								print $worksheet->{Cells}[$row][$col]->Value . "|\n";
								$amount = $worksheet->{Cells}[$row][$col]->Value;								
							} else { $amount = "|";	}
	                    }				
					}
				}
		}


    # tähän do while columnien mukaan. mennään rivejä läpi kunnes päästään viimeiseen
	#@warehouses = haeVarastot();
	yhdistaKentat();