#!/usr/bin/perl -w

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 "Material mapping generator - Version 0.0.5 - Final \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
#----------------------------------------------------------------------------------------
unless (defined $materialmapping_file and defined $data_folder)
   {
      print "Usage: $0 <materialmapping_file> <data_folder>\n";
      exit;
   }

	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 "loading...\n";
	while(($filename = readdir(DIR))){
		
		next unless $filename =~ /\.xls$/i;

			print " - $filename \n";
			
			my $parser = Spreadsheet::ParseExcel->new();


			my $oBook = $parser->parse($data_folder.$filename);


			 next unless defined $oBook;
				for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++) {
       				$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++)
					{
						$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++)
								{
									$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++){
							$internal_cell = $oWkS->{Cells}[$iR][$map_Cmin];
							$active_cell = $oWkS->{Cells}[$iR][$active_col];
							$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)
							{
								#fo every family print a line
								my @families = split(/[,.]+/, decode('cp1252',$family_cell->{Val}));
								foreach $family(@families){
                                                                      
									 my $prnumbero = 0;
                                                                        my $materialmapping_item = $materialmapping_table_xml->createElement("item");
                                                                        
                                                                        #set family
									$materialmapping_item->setAttribute("pr_family",$family);
									
									#set item_number
									$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}));
									}
                                                                     
                                                                        #add all propriety
									for(my $prC2 = $map_Cmin+2; $prC2 <= $map_Cmax ; $prC2++)
										{
											$pr_cell_name2 = $oWkS->{Cells}[$title_row][$prC2];
											$pr_cell2 = $oWkS->{Cells}[$iR][$prC2];
											if(defined $pr_cell2 and defined $pr_cell_name2 and length(decode('cp1252',$pr_cell2->{Val})) gt 0 and length(decode('cp1252',$pr_cell_name2->{Val})) gt 0)
											{
                                                                                           
											my @pr2 = split(/[,.]+/, decode('cp1252',$pr_cell2->{Val}));
                                                                             foreach $pr2(@pr2){
                                                                                   
                        
                                                                                    
                                                           
                                                                                                $materialmapping_item->setAttribute(decode('cp1252',$pr_cell_name2->{Val}),$pr2);
                                                                                                
                                                                                             }
                                                                                        }
                                                                                }     
												if ($prnumbero eq 0){ 

                                                                        #set internal
									$materialmapping_item->setAttribute("internal",decode('cp1252',$internal_cell->{Val}));

									#set colorzones 
									$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 description
									$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++)
										{
											$brand_cell_name = $oWkS->{Cells}[$title_row][$brandC];
											if(defined $brand_cell_name and decode('cp1252',$brand_cell_name->{Val}) ne "Epta std")
											{
												$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++)
										{
											$customer_cell_name = $oWkS->{Cells}[$title_row][$customerC];
											$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++)
										{
											$market_cell_name = $oWkS->{Cells}[$title_row][$marketC];
											$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);	
									}
								}
							}
						}
					}
			
				}
		
	}
        
	$materialmapping_table_xml->toFile($materialmapping_file,2);