#!/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 = 10;
	my $local_currency = "";
	my $warehouse = "";
	my $title = "";
	my $month_year = "";
	my $month = "";
	my $year = "";
	my $amount = "";
	my $counter = 0;
	my $start_line = 0;
	my $date_changed = "";
	my $date_created = "";
	my $username = "Loader";
	my $date_created_day; 
	my $date_created_month; 
	my $date_created_year;
	
	my $nextCell;
	my $readState;
	
	($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);
	
	# find first warehouse
	while ( $counter <= 2 )
	{
		for my $row ( $row_min .. $row_max ) {
			#pick up warehouse
			if (defined $worksheet->{Cells}[$row][0])
			{
				if  ( $worksheet->{Cells}[$row][0]->Value eq "Warehouse:" )
				{
				$start_line = $row;
				}
			}
		}
		$counter += 1;
	}
	
	# loop through columns until get to the last one
    while ( $column <= $col_max ) {
    
    for my $row ( $start_line .. $row_max ) {
		#print "$country|";
        
		#pick up local currency	
		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;
			}
		}
		
		
				#pick up warehouse
		if (defined $worksheet->{Cells}[$row][0])
		{
			my $temp = $worksheet->get_cell($row, 0);
			if  ( $temp->unformatted() eq "Warehouse:" )
			{
				print "warehouse!";
				$warehouse = $worksheet->{Cells}[$row][1]->Value;
			}
		}
		#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.

			}
		}
                                                   
		$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();
			}
			else { next; }
		}
		

		
		
		
#		for my $col ( 0 .. 1 ) {
#                my $cell = $worksheet->get_cell( $row, $col );
#                #next unless $cell;
#		    
#		    if (defined $worksheet->{Cells}[$row][$col])
#                {
#                    print $worksheet->{Cells}[$row][$col]->Value . "|";
#                } else {
#                	  print "|";
#                }
#        }
		
        # 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])
            {
				#print $worksheet->{Cells}[$row][$col]->Value . "|";
				$amount = $worksheet->{Cells}[$row][$col]->Value;
            } else {
				#print "|";
				$amount = "";
            }
		}
		
		print "$country|$warehouse|$title|$year|$month|$local_currency|$amount|$date_created|$date_changed|$username";
		
	    print "\n";
    }
    $column += 1;
    } #while end