#!/usr/bin/perl -w

my $version="0.0.2";
use strict;
use Encode;
use utf8;
use XML::LibXML;
use Spreadsheet::ParseExcel;

system("cls");

# Read command line arguments
#----------------------------------------------------------------------------------------
my $materialmapping_file = shift;
my $data_folder = shift;


print "OPTIONS\nmaterialmapping_file:$materialmapping_file\ndata_folder:$data_folder\n";

# subroutine
sub getSQLTimeStamp { 
       my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time);
       return sprintf "%4d-%02d-%02d %02d:%02d:%02d",$year+1900,$mon+1,$mday,$hour,$min,$sec;
}

# Main
#----------------------------------------------------------------------------------------
if(length($materialmapping_file) gt 0 and length($data_folder) gt 0)
{
	opendir ( DIR, $data_folder ) || die "Error in opening dir $data_folder\n";
	
	my $materialmapping_table_xml = XML::LibXML->createDocument( "1.0", "UTF-8");
	my $materialmapping_table_xml_root = $materialmapping_table_xml->createElement("masterdata");
	$materialmapping_table_xml_root->setAttribute('version',getSQLTimeStamp());
	$materialmapping_table_xml->setDocumentElement($materialmapping_table_xml_root);
	
	print "Parsing files...\n";
	while(  my $filename = readdir(DIR)){
		
		if($filename =~ /\.xls$/i) {
			
			
			my $parser     = Spreadsheet::ParseExcel->new();
			my $oBook  = $parser->parse($data_folder.$filename);
			if (defined $oBook ) {
				for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++) {
       				my $oWkS = $oBook->{Worksheet}[$iSheet];

					# find the needed columns
					
					my $map_Cmin = -1; # the first is internal
					my $map_Cmax = -1;
					my $artno_col = -1;
					my $active_col = -1;
					my $colorzones_col = -1;
					my $family_col = -1;
					my $tec_desc_col = -1;
					my $brand_Cmin = -1;
					my $brand_Cmax = -1;
					my $customer_Cmin = -1;
					my $customer_Cmax = -1;
					my $market_Cmin = -1;
					my $market_Cmax = -1;
					my $title_row = -1;
					
					for (my $iC = $oWkS->{MinCol}; defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ; $iC++)
					{
						my $oWkC = $oWkS->{Cells}[$oWkS->{MinRow}][$iC];
						if(defined $oWkC)
						{
							if(decode('cp1252',$oWkC->{Val}) eq "Configurator mapping")
							{
								for (my $iR = $oWkS->{MinRow} +1; defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $iR++)
								{
									my $oWkC_int = $oWkS->{Cells}[$iR][$iC];
									if(defined $oWkC_int and decode('cp1252',$oWkC_int->{Val}) eq "internal")
									{
										$title_row = $iR;
									}
								}
								if($title_row > 0)
								{
									foreach my $area ( @{ $oWkS->{MergedArea} } ) {
										if($area->[1] eq $iC and $area->[0] eq $oWkS->{MinRow}){
											$map_Cmax = $area->[3];
										}
									}
									$map_Cmin = $iC;
									$artno_col = $map_Cmin -2;
									$active_col = $map_Cmin -1;
									$family_col = $map_Cmin +1;
									$colorzones_col =  $map_Cmax +2;
									$tec_desc_col = $map_Cmax +1;
								}
								
								
							} elsif(decode('cp1252',$oWkC->{Val}) eq "Market"){
								foreach my $area ( @{ $oWkS->{MergedArea} } ) {
									if($area->[1] eq $iC and $area->[0] eq $oWkS->{MinRow}){
										$market_Cmax = $area->[3];
									}
								}
								$market_Cmin = $iC;
							} elsif(decode('cp1252',$oWkC->{Val}) eq "Customer"){
								foreach my $area ( @{ $oWkS->{MergedArea} } ) {
									if($area->[1] eq $iC and $area->[0] eq $oWkS->{MinRow}){
										$customer_Cmax = $area->[3];
									}
								}
								$customer_Cmin = $iC;
							} elsif(decode('cp1252',$oWkC->{Val}) eq "Brand"){
								foreach my $area ( @{ $oWkS->{MergedArea} } ) {
									if($area->[1] eq $iC and $area->[0] eq $oWkS->{MinRow}){
										$brand_Cmax = $area->[3];
									}
								}
								$brand_Cmin = $iC;
							}
						}
					}
					
					if($map_Cmin >= 0 and $map_Cmin >= 0 and $title_row >= 0){
						for(my $iR = $title_row +1; defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $iR++){
							my $internal_cell = $oWkS->{Cells}[$iR][$map_Cmin];
							my $active_cell = $oWkS->{Cells}[$iR][$active_col];
							my $family_cell = $oWkS->{Cells}[$iR][$family_col];
							if(defined $internal_cell and defined $active_cell and defined $family_cell and length(decode('cp1252',$internal_cell->{Val})) gt 0 and !(length(decode('cp1252',$active_cell->{Val})) gt 0) and length(decode('cp1252',$family_cell->{Val})) gt 0)
							{
								#for every family print a line
								my @families = split(/\,/, decode('cp1252',$family_cell->{Val}));
								foreach my $family(@families){
									my $materialmapping_item = $materialmapping_table_xml->createElement("item");
									
									#set internal
									$materialmapping_item->setAttribute("internal",decode('cp1252',$internal_cell->{Val}));
									
									#set family
									$materialmapping_item->setAttribute("pr_family",$family);
									
									#set item_number
									my $item_number_cell = $oWkS->{Cells}[$iR][$artno_col];
									if(defined $item_number_cell and length(decode('cp1252',$item_number_cell->{Val})) gt 0)
									{
										$materialmapping_item->setAttribute("item_number",decode('cp1252',$item_number_cell->{Val}));
									}
									
									#set colorzones 
									my $colorzones_cell = $oWkS->{Cells}[$iR][$colorzones_col];
									if(defined $colorzones_cell and length(decode('cp1252',$colorzones_cell->{Val})) gt 0)
									{
										$materialmapping_item->setAttribute("colorzone",decode('cp1252',$colorzones_cell->{Val}));
									}
									
									#set properties
									for(my $prC = $map_Cmin+2; $prC <= $map_Cmax ; $prC++)
										{
											my $pr_cell_name = $oWkS->{Cells}[$title_row][$prC];
											my $pr_cell = $oWkS->{Cells}[$iR][$prC];
											if(defined $pr_cell and defined $pr_cell_name and length(decode('cp1252',$pr_cell->{Val})) gt 0 and length(decode('cp1252',$pr_cell_name->{Val})) gt 0)
											{
												$materialmapping_item->setAttribute(decode('cp1252',$pr_cell_name->{Val}),decode('cp1252',$pr_cell->{Val}));
											}
										}
									#set description
									my $description_cell = $oWkS->{Cells}[$iR][$tec_desc_col];
									if(defined $description_cell and length(decode('cp1252',$description_cell->{Val})) gt 0)
									{
										$materialmapping_item->setAttribute("description",decode('cp1252',$description_cell->{Val}));
									}
									
									#set Brands
									if($brand_Cmin gt 0 and $brand_Cmax gt 0)
									{
										my $brand_string = "";
										my $ignored_brand_string = "";
										for(my $brandC = $brand_Cmin; $brandC <= $brand_Cmax ; $brandC++)
										{
											my $brand_cell_name = $oWkS->{Cells}[$title_row][$brandC];
											if(defined $brand_cell_name and decode('cp1252',$brand_cell_name->{Val}) ne "Epta std")
											{
												my $brand_cell = $oWkS->{Cells}[$iR][$brandC];
												if(defined $brand_cell and length(decode('cp1252',$brand_cell->{Val})) gt 0)
												{
													if(decode('cp1252',$brand_cell->{Val}) eq '0')
													{
														$ignored_brand_string = $ignored_brand_string . decode('cp1252',$brand_cell_name->{Val}) . ",";
													} else {
														$brand_string = $brand_string . decode('cp1252',$brand_cell_name->{Val}) . ",";
													}
												}
											}
										}
										if(length($brand_string) gt 0){
											$materialmapping_item->setAttribute('brand',$brand_string);
										}
										if(length($ignored_brand_string) gt 0){
											$materialmapping_item->setAttribute('ignore_brand',$ignored_brand_string);
										}
									}
									
									#set Customer
									if($customer_Cmin gt 0 and $customer_Cmax gt 0)
									{
										my $customer_string = "";
										my $ignored_customer_string = "";
										for(my $customerC = $customer_Cmin; $customerC <= $customer_Cmax ; $customerC++)
										{
											my $customer_cell_name = $oWkS->{Cells}[$title_row][$customerC];
											my $customer_cell = $oWkS->{Cells}[$iR][$customerC];
												if(defined $customer_cell_name and defined $customer_cell and length(decode('cp1252',$customer_cell->{Val})) gt 0 and length(decode('cp1252',$customer_cell_name->{Val})) gt 0)
												{
													if(decode('cp1252',$customer_cell->{Val}) eq '0')
													{
														$ignored_customer_string = $ignored_customer_string . decode('cp1252',$customer_cell_name->{Val}) . ",";
													} else {	
														$customer_string = $customer_string . decode('cp1252',$customer_cell_name->{Val}) . ",";
													}
												}
										}
										if(length($customer_string) gt 0){
											$materialmapping_item->setAttribute('customer',$customer_string);
										}
										if(length($ignored_customer_string) gt 0){
											$materialmapping_item->setAttribute('ignore_customer',$ignored_customer_string);
										}
									}
									
									#set Market
									if($market_Cmin gt 0 and $market_Cmax gt 0)
									{
										my $market_string = "";
										my $ignored_market_string = "";
										for(my $marketC = $market_Cmin; $marketC <= $market_Cmax ; $marketC++)
										{
											my $market_cell_name = $oWkS->{Cells}[$title_row][$marketC];
											my $market_cell = $oWkS->{Cells}[$iR][$marketC];
												if(defined $market_cell_name and defined $market_cell and length(decode('cp1252',$market_cell->{Val})) gt 0 and length(decode('cp1252',$market_cell_name->{Val})) gt 0)
												{
													if(decode('cp1252',$market_cell->{Val}) eq '0')
													{
														$ignored_market_string = $ignored_market_string . decode('cp1252',$market_cell_name->{Val}) . ",";
													} else {	
														$market_string = $market_string . decode('cp1252',$market_cell_name->{Val}) . ",";
													}
												}
										}
										if(length($market_string) gt 0){
											$materialmapping_item->setAttribute('market',$market_string);
										}
										if(length($ignored_market_string) gt 0){
											$materialmapping_item->setAttribute('ignore_market',$ignored_market_string);
										}
									}
									
									$materialmapping_table_xml_root->addChild($materialmapping_item);
								};
							}
						}
					}
				}
			}
			print "Parsed $filename \n";
		}
	}
	$materialmapping_table_xml->toFile($materialmapping_file,2);
}