#!/usr/bin/perl -w

    use strict;
    use Spreadsheet::ParseExcel;

    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 = 4;
	my @warehouses = ();
	my $warehouse = "";
	my $readState = "";
	my $nextCell = "";
	my $month_year = "";
	my $month = "";
	my $year = "";
	my $amount = "";
	my $title = "";
	my $local_currency = "";
	my $testi = 0;
	my $date_created_day; 
	my $date_created_month; 
	my $date_created_year;
	my $date_created;
	($date_created_day, $date_created_month, $date_created_year) = (localtime)[3,4,5];
	$date_created = $date_created_day . "." . ($date_created_month+1) . "." . ($date_created_year+1900);
	
	# 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 {
		print $testi;
		$testi++;
	}

	sub yhdistaKentat {
		# Loopataan kaikki rowit läpi ja etsimään etuliitteitä celleille.
		
		$warehouse = $worksheet->{Cells}[$warehouses[0]][1]->Value;
		
		#pick up amount of the month
	                
						
						
	                       #pick up month and year
	                        
	                            #$worksheet->{Cells}[$row][$col]->Value;
									$month_year = $worksheet->{Cells}[$warehouses[0]][$column]->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;
								
						
					
					
		for my $row ( $warehouses[0] + 1 .. $warehouses[1] - 2 ) {
			if (defined $worksheet->{Cells}[$row][0])
			{	
			if  ( $worksheet->{Cells}[$row][0]->Value eq "Economical" )
			{
				$local_currency = $worksheet->{Cells}[$row][1]->Value;
				$local_currency = substr $local_currency, 2, 3;
			}
		}
			
			#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);
			}

			if (defined $worksheet->{Cells}[$row][0])
		{
			my $temp = $worksheet->get_cell($row, 0);
			if  ( $temp->unformatted() eq "Warehouse:" )
			{

			}
		}

			
				$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.
					$title = $readState ."-" . $cell->unformatted();
					}
					# Molemmat cellit olivat tyhjiä, hypätään loopin seuraavaan.
					} else 
					{ 
					 next; 
					 } 
				    
					
						if (defined $worksheet->{Cells}[$row][$column])
						{
							# cell on tyhjä?
							if ($worksheet->get_cell($row, $column)->unformatted() eq "")
							{
								$amount = "|";
								
							}
							# cell ei ole tyhjä?
							if ($worksheet->get_cell($row, $column)->unformatted() ne "")
							{
								#print $worksheet->{Cells}[$row][$column]->Value . "|\n";
								$amount = $worksheet->{Cells}[$row][$column]->Value;								
							} 
	                    }	
						print "$country|$warehouse|$title|$year|$month|$local_currency|$amount|\n";						
					}
										
				}

		


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