##

#############################################################################################################
# Script that parses first 3 xml levels to an excel sheet "general information". All the level4 tags        #
# get their own sheet. Every sheet has main information (title and header) and, if available deeper         #
# levels, it also has subinformation on the spreadsheet. This subinformation is the name of the element     #
# With all of the containing tags + values in a title/value layout (like the main information)              #
# The script will parse all of the xml files it finds in your working directory.                            #
#############################################################################################################

#!/usr/bin/perl --

use strict;
use warnings;
use Cwd;
use Win32::OLE;
use XML::Simple;
use Win32::OLE::Const 'Microsoft Excel';


##########################################
# Get all xml files in working directory #
##########################################
my $dir = cwd();
opendir(DIR, $dir) || die "can't opendir $dir: $!";
print "$dir\n";
my @files = grep(/\.xml$/,readdir(DIR));
closedir(DIR);


use Data::Dumper;
local $Data::Dumper::Indent=1;
#print Dumper($doc);


########################
# Ask for confirmation #
########################
my $input = "x";			
while ($input ne "y")
{
	print "\n\nThis script will try to parse all the xml files in the scripts folder. \nThis might take up to 5 minutes depending on the xml file(s) size. \nYou can see what is being parsed at a certain time.\n\nDo you want to run the script? [y/n]\n";
	$input = ;			
	chomp $input;
    }

unless( scalar @files ){
  print "\n\nNo Files Found in $dir,\nMake sure your script is in a directory with the XML files\n\n";
  exit;
}


parseFile($_) for @files;




sub writeNonArray {
  my ($worksheet, $rowCount, $colCount, $header, $value) = @_;
  $worksheet->Cells($rowCount, $colCount)->{Value} = $header;
  $worksheet->Cells($rowCount, $colCount)->Borders() -> {Weight} = 2;
  $colCount++;
  $worksheet->Cells($rowCount, $colCount)->{Value} = $value;
  $worksheet->Cells($rowCount, $colCount)->Borders() -> {Weight} = 2;
  $rowCount++;
  $colCount--;
  return ($rowCount, $colCount);
}

sub parseFile {

print "\n#############################################\n\nInitializing script ...\n \nAnalysing file $_[0]\n\n#############################################\n\n";

#######################
# Excel Initialisation# 
#######################
my $xs1 = XML::Simple->new();
my $doc = $xs1->XMLin($_[0], keyattr=>[], ForceContent=>1, ForceArray=>1);
my $application = Win32::OLE->new("Excel.Application");
$application->{DisplayAlerts} = 0; 
my $workbook    = $application->Workbooks->Add();

for (my $count = 3; $count >= 0; $count--) {
if ($workbook->WorkSheets($count)) {$workbook->WorkSheets($count)->Delete();}
}
my $worksheet = $workbook->Worksheets->Add({After=>$workbook->Worksheets($workbook->Worksheets->{Count})});


############################################
# First worksheet with general information #
############################################
$worksheet -> {Name} = "General Information";
$worksheet -> Range("A1") -> Font -> {Size}= 14;
$worksheet -> Range("A1") -> Font -> {ColorIndex}= 2;
$worksheet -> Range("A1") -> {Value} = "General Information";
$worksheet -> Range("A1:E1") -> Merge;
$worksheet -> Range("A1:E1") -> Interior -> {ColorIndex} = 25;
$worksheet -> Range("A1:E1") -> Borders() -> {Weight} = 3;


####################################################################
# Excel index variables for writing to column and row with headers #
####################################################################
my $LastRow = 0;
my $new = 0;
my $mainHeaders = 3;		#main header row
my $rowCount=4;			#main info row
my $colCount = 2;		#main info column
my $subHeaderRow = 0;		#additional info header row
my $subDataRow = 50;		#additional info data row


print "Collecting general information...";

foreach my $Level1 ( sort keys %{ $doc } ){
 	
 	my $aoh1 = $doc->{$Level1};
    	unless (ref($aoh1) eq 'ARRAY') {
		($rowCount,$colCount) = writeNonArray($worksheet, $rowCount, $colCount, $Level1, $aoh1);
		next;
	}
	my $count1 = $#{ $aoh1 };

	foreach my $i(0 .. $count1) { 
									
		foreach my $Level2 ( sort keys %{ $doc->{$Level1}[$i] } ){
			
			my $aoh2 = $doc->{$Level1}[$i]{$Level2};
			unless (ref($aoh2) eq 'ARRAY') {
				($rowCount,$colCount) = writeNonArray($worksheet, $rowCount, $colCount, $Level2, $aoh2);
				next;
			}			
			my $count2 = $#{ $aoh2 };
			
			foreach my $j(0 .. $count2) { 
					
				foreach my $Level3 ( sort keys %{ $doc->{$Level1}[$i]{$Level2}[$j] } ){

					my $aoh3 = $doc->{$Level1}[$i]{$Level2}[$j]{$Level3};
					unless (ref($aoh3) eq 'ARRAY') {
						($rowCount,$colCount) = writeNonArray($worksheet, $rowCount, $colCount, $Level3, $aoh3);			
						next;							
	    				}
					my $count3 = $#{ $aoh3 };

					foreach my $k(0 .. $count3) {
						
						foreach my $Level4 ( sort keys %{ $doc->{$Level1}[$i]{$Level2}[$j]{$Level3}[$k] } ){
							
							my $aoh4 = $doc->{$Level1}[$i]{$Level2}[$j]{$Level3}[$k]{$Level4};
							unless (ref($aoh4) eq 'ARRAY') {
								($rowCount,$colCount) = writeNonArray($worksheet, $rowCount, $colCount, $Level4, $aoh4);							
								next;
			    				
			    				}

							
							print "Completed\nCollecting $Level4 ...";

							my $count4 = $#{ $aoh4 };				
							
							$subDataRow = ($count4 + 1) + ($rowCount + 1);
							my $worksheet = $workbook->Worksheets->Add({After=>$workbook->Worksheets($workbook->Worksheets->{Count})});
							$worksheet -> {Name} = $Level4;
							$worksheet -> Range("A1") -> Font -> {Size}= 14;
							$worksheet -> Range("A1") -> Font -> {ColorIndex}= 2;
							$worksheet -> Range("A1") -> {Value} = "$Level4";
							$worksheet -> Range("A1:E1") -> Merge;
							$worksheet -> Range("A1:E1") -> Interior -> {ColorIndex} = 25;
							$worksheet -> Range("A1:E1") -> Borders() -> {Weight} = 3;
							my $subDataCol = 1;
							
							foreach my $l(0 .. $count4) {
						
								my $temp = "";
					
								foreach my $Level5 ( sort keys %{ $doc->{$Level1}[$i]{$Level2}[$j]{$Level3}[$k]{$Level4}[$l] } ){
									my $aoh5 = $doc->{$Level1}[$i]{$Level2}[$j]{$Level3}[$k]{$Level4}[$l]{$Level5};
									my $curID = 0;
									my $nextup = "";
 									
                             						if($doc->{$Level1}[$i]{$Level2}[$j]{$Level3}[$k]{$Level4}[$l]{number}){ $curID = $doc->{$Level1}[$i]{$Level2}[$j]{$Level3}[$k]{$Level4}[$l]{number};}
                             						if($doc->{$Level1}[$i]{$Level2}[$j]{$Level3}[$k]{$Level4}[$l]{enclosureNumber}){ $curID = $doc->{$Level1}[$i]{$Level2}[$j]{$Level3}[$k]{$Level4}[$l]{enclosureNumber};}
									
                             						if(ref($aoh5)){
                             						########################
                             						# EXCEL SUBINFORMATION #
                             						######################## 

	                             						my $count5 = $#{ $aoh5 };

                             						
	                             							foreach my $m(0 .. $count5) {
                             						
			                             						if($nextup ne $Level5){
			                             							$new = 1;
			                             							$subDataRow++;
			                             							$subHeaderRow = $subDataRow;
			                             							$subDataCol = 1 ;
			                             							$nextup = $Level5;
		                             							}
		                             							else {$new = 0;}

												$subDataRow++;
	
													foreach my $Level6 ( sort keys %{ $doc->{$Level1}[$i]{$Level2}[$j]{$Level3}[$k]{$Level4}[$l]{$Level5}[$m]} ){
                             											# print "\n$Level1->$Level2->$Level3->$Level4->$Level5->$Level6 : $doc->{$Level1}[$i]{$Level2}[$j]{$Level3}[$k]{$Level4}[$l]{$Level5}[$m]{$Level6}";											
														# subinfo title for new element eg "Rank : 1" in colors
														
														if ($temp ne $curID){
															
															$temp = $curID;
															$worksheet->Cells($subDataRow, $subDataCol)->{Value} = "$Level4 : $curID";
															$worksheet->Cells($subDataRow, $subDataCol)->Interior->{ColorIndex} = 33;
															$worksheet->Cells($subDataRow, $subDataCol)->{Font}->{Bold} = 1;
															$worksheet ->Cells($subDataRow, $subDataCol)-> Borders() -> {Weight} = 1;
															$subDataRow+=2;
															$subHeaderRow = $subDataRow;
															$subDataRow++;			
														}
														
														# We should write the header only once 
												 		if($new==1){
															$worksheet->Cells($subHeaderRow, $subDataCol)->{Value} = $Level6; 
															$worksheet->Cells($subHeaderRow, $subDataCol)->{Font}->{Bold} = 1;
															$worksheet ->Cells($subHeaderRow, $subDataCol)-> Borders() -> {Weight} = 2;
															$worksheet ->Cells($subHeaderRow, $subDataCol)-> Borders() -> {ColorIndex} = 25; 
														 }
    										        	
    														$worksheet->Cells($subDataRow, $subDataCol)->{Value} = $doc->{$Level1}[$i]{$Level2}[$j]{$Level3}[$k]{$Level4}[$l]{$Level5}[$m]{$Level6};
														$subDataCol++;
                             										}
											$subDataCol=1;
                             								}
                             						}
                             						
                             						else{
                             						
                             						##########################
                             						# EXCEL MAIN INFORMATION #
                             						##########################
                             						
                             						
                             							# Index, if any of those, will be placed in first column
                             							if ($Level5 eq "number" | $Level5 eq "id" | $Level5 eq "enclosureNumber"){
                             								$worksheet->Cells($mainHeaders, 1)->{Value} = $Level5;
											$worksheet->Cells($mainHeaders, 1)->{Font} -> {ColorIndex} = 25;
											$worksheet->Cells($mainHeaders, 1)->{Interior} -> {ColorIndex} = 15;
											$worksheet->Cells($mainHeaders, 1)->Borders() -> {Weight} = 2;
											$worksheet->Cells($mainHeaders, 1)->{Font}->{Bold} = 1;
		              								$worksheet->Cells($rowCount, 1)->{Value} = $doc->{$Level1}[$i]{$Level2}[$j]{$Level3}[$k]{$Level4}[$l]{$Level5};
											$worksheet->Cells($rowCount, 1)->{Font}->{Bold} = 1;
											$worksheet->Cells($rowCount, 1)->Borders() -> {Weight} = 2;
                             							}	

                             							else{
                             								unless ($worksheet->Cells($mainHeaders, $colCount)->{Value}){
                             	
                             									$worksheet->Cells($mainHeaders, $colCount)->{Value} = $Level5;
    									                }
    									        
    										        if ($Level5 eq "wwpn"){
												$worksheet->Cells($rowCount, $colCount)->{NumberFormat} = "0";
												$worksheet->Cells($rowCount, $colCount)->Borders() -> {Weight} = 2;
                         									    									        	
    									        	}
    										        
                             								$worksheet->Cells($rowCount, $colCount)->Borders() -> {Weight} = 2;
                             								$worksheet->Cells($rowCount, $colCount)->{Value} = $doc->{$Level1}[$i]{$Level2}[$j]{$Level3}[$k]{$Level4}[$l]{$Level5};
                             								$worksheet->Cells($mainHeaders, $colCount)->Borders() -> {Weight} = 2;
											$worksheet->Cells($mainHeaders, $colCount)->{Font}->{Bold} = 1;
											$worksheet->Cells($mainHeaders, $colCount)->{Font} -> {ColorIndex} = 25;
											$worksheet->Cells($mainHeaders, $colCount)->{Interior} -> {ColorIndex} = 15;
											$colCount++;
										}
									}
								} $rowCount++; 
								  $subDataRow++;
								  $colCount=2; 
								  $worksheet -> Range("A:X") -> {Columns} -> Autofit;
								  
							} 
							  $subDataRow=$rowCount+2;;
							  $colCount=2;	
							  $rowCount=4;		

						#Seperate general information on first sheet.
						} $rowCount=4; $colCount+=3;			
					}  			
				} $rowCount=4; $colCount+=3;		
			}
		} $rowCount=4; $colCount+=3;
	}
}	
$LastRow = $worksheet->UsedRange->Find({What=>"*", SearchDirection=>xlPrevious, SearchOrder=>xlByRows})->{Row};										
$worksheet -> Range("1:$LastRow") -> {Columns} -> Autofit;

$workbook->SaveAs($dir . '/'.$_[0].'.xls');
$workbook->Close;
print "\n\n$_[0].xls generated in $dir\n" ;
#print "\a";

}