#!/usr/bin/perl -w ######################### # # Materialmapping generator # Updated 30.07.2012 # my $version="0.0.3"; # 2012.04.12 v0.0.1 DM: Creation # 2012.04.23 v0.0.2 DM: Added support for active column and comma separeted families # 2012.05.30 v0.0.3 DM: Added support for comma separated propriety - only one for line #Excel format rules: #-a merged cell named "Configurator mapping" on the first line identifies the mapping data (internal+properties) #-a merged cell named "Brand" on the first line identifies the Brand data (optional) #-a merged cell named "Market" on the first line identifies the Market data (optional) #-a merged cell named "Customer" on the first line identifies the Customer data (optional) #-the first column of "Configurator mapping" should have an "internal" as cell(line number is not important), the "internal" define the title bar line; the cell must be on the same line where all the properties/markets/brand/customer names are #-the "active" column must be exactly left of the "internal" column #-the commercial code column must be exacly left of the "active" column #-the "pr_family" column should be exactly right of the "internal" column #-the tecnical description must be exactly right of the last "Configurator mapping" column #-the colorzones must be exactly right of the last "tecnical description" column #Notes: #-in the field "pr_family" the user can concatenate the family value like 'TL1_1,TL1_4' , every value generate a line in the xml file with the right family #-in the colums "propriety" the user can concatenate the propriety value like 'value1,value2' (but only for one pr colum for line), every value add a line in the xml file with the right propriety #-if the "active" column is not empty, the line is not evaluated #-if "internal" or "pr_family" field is empty, the line is not evaluated # ######################### 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 "Material mapping generator v.0.0.3 - Parsing files...\n"; while( ($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++) { $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 $materialmapping_item = $materialmapping_table_xml->createElement("item"); #add all propriety for(my $prC = $map_Cmin+2; $prC <= $map_Cmax ; $prC++) { $pr_cell_name = $oWkS->{Cells}[$title_row][$prC]; $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) { my @pr = split(/[,.]+/, decode('cp1252',$pr_cell->{Val})); foreach $pr(@pr){ $materialmapping_item->setAttribute(decode('cp1252',$pr_cell_name->{Val}),$pr); } } } #for every multiple less 2 propriety print a line 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 1 and length(decode('cp1252',$pr_cell_name2->{Val})) gt 1) { my @pr2 = split(/[,.]+/, decode('cp1252',$pr_cell2->{Val})); foreach $pr2(@pr2){ if(@pr2 gt 1) { my $materialmapping_item2 = $materialmapping_table_xml->createElement("item3"); $materialmapping_item2->setAttribute(decode('cp1252',$pr_cell_name2->{Val}),$pr2); for(my $prC3 = $map_Cmin+2; $prC3 <= $map_Cmax ; $prC3++) { $pr_cell_name3 = $oWkS->{Cells}[$title_row][$prC3]; $pr_cell3 = $oWkS->{Cells}[$iR][$prC3]; if($prC3 ne $prC2) { my @pr3 = split(/[,.]+/, decode('cp1252',$pr_cell3->{Val})); foreach $pr3(@pr3) { $materialmapping_item2->setAttribute(decode('cp1252',$pr_cell_name3->{Val}),$pr3); } } } #set internal $materialmapping_item2->setAttribute("internal",decode('cp1252',$internal_cell->{Val})); #set family $materialmapping_item2->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_item2->setAttribute("item_number",decode('cp1252',$item_number_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_item2->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_item2->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_item2->setAttribute('brand',$brand_string); } if(length($ignored_brand_string) gt 0){ $materialmapping_item2->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_item2->setAttribute('customer',$customer_string); } if(length($ignored_customer_string) gt 0){ $materialmapping_item2->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_item2->setAttribute('market',$market_string); } if(length($ignored_market_string) gt 0){ $materialmapping_item2->setAttribute('ignore_market',$ignored_market_string); } } $materialmapping_item = $materialmapping_item2; $materialmapping_table_xml_root->addChild($materialmapping_item2); } } } } #for every multiple propriety print a line 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){ if(@pr2 gt 1) { for(my $prC3 = $map_Cmin+2; $prC3 <= $map_Cmax ; $prC3++) { $pr_cell_name3 = $oWkS->{Cells}[$title_row][$prC3]; $pr_cell3 = $oWkS->{Cells}[$iR][$prC3]; if($prC3 gt $prC2) { my @pr3 = split(/[,.]+/, decode('cp1252',$pr_cell3->{Val})); foreach $pr3(@pr3) { if(@pr3 gt 1) { my $materialmapping_item3 = $materialmapping_table_xml->createElement("item2"); $materialmapping_item3->setAttribute(decode('cp1252',$pr_cell_name3->{Val}),$pr3); for(my $prC4 = $map_Cmin+2; $prC4 <= $map_Cmax ; $prC4++) { $pr_cell_name4 = $oWkS->{Cells}[$title_row][$prC4]; $pr_cell4 = $oWkS->{Cells}[$iR][$prC4]; if($prC4 ne $prC3) { my @pr4 = split(/[,.]+/, decode('cp1252',$pr_cell4->{Val})); foreach $pr4(@pr4) { $materialmapping_item3->setAttribute(decode('cp1252',$pr_cell_name2->{Val}),$pr2); $materialmapping_item3->setAttribute(decode('cp1252',$pr_cell_name4->{Val}),$pr4); } }} #set internal $materialmapping_item3->setAttribute("internal",decode('cp1252',$internal_cell->{Val})); #set family $materialmapping_item3->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_item3->setAttribute("item_number",decode('cp1252',$item_number_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_item3->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_item3->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_item3->setAttribute('brand',$brand_string); } if(length($ignored_brand_string) gt 0){ $materialmapping_item3->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_item3->setAttribute('customer',$customer_string); } if(length($ignored_customer_string) gt 0){ $materialmapping_item3->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_item3->setAttribute('market',$market_string); } if(length($ignored_market_string) gt 0){ $materialmapping_item3->setAttribute('ignore_market',$ignored_market_string); } } $materialmapping_item = $materialmapping_item3; $materialmapping_table_xml_root->addChild($materialmapping_item3); } }}} }} } } #set internal $materialmapping_item->setAttribute("internal",decode('cp1252',$internal_cell->{Val})); #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})); } #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); }; } } } } } print "Parsed $filename \n"; } } $materialmapping_table_xml->toFile($materialmapping_file,2); }