#!/usr/bin/perl --

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

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);

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 = <STDIN>;			
	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 parseFile {


print "\n#############################################\n\nInitializing script ...\n \nAnalysing file $_[0]\n\n#############################################\n\n";
print "ok";
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();}
}
print "ok";
my $worksheet = $workbook->Worksheets->Add({After=>$workbook->Worksheets($workbook->Worksheets->{Count})});


$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;

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


foreach my $sub1 ( sort keys %{ $doc } ){
 
	unless (ref($doc->{$sub1})) {

		$worksheet->Cells($rowCount, $colCount)->{Value} = $sub1;
		$worksheet->Cells($rowCount, $colCount)->Borders() -> {Weight} = 2;
		$colCount++;
		$worksheet->Cells($rowCount, $colCount)->{Value} = $doc->{$sub1};
		$worksheet->Cells($rowCount, $colCount)->Borders() -> {Weight} = 2;
		$rowCount++;
		$colCount--;
		next;
	}

	my $count = $#{ $doc->{$sub1} };

	foreach my $i(0 .. $count) { 
							
		
		foreach my $sub2 ( sort keys %{ $doc->{$sub1}[$i] } ){
			
			unless (ref($doc->{$sub1}[$i]{$sub2})) {

				$worksheet->Cells($rowCount, $colCount)->{Value} = $sub2;
				$worksheet->Cells($rowCount, $colCount)->Borders() -> {Weight} = 2;
				$colCount++;
				$worksheet->Cells($rowCount, $colCount)->{Value} = $doc->{$sub1}[$i]{$sub2};
				$worksheet->Cells($rowCount, $colCount)->Borders() -> {Weight} = 2;
				$rowCount++;
				$colCount--;
				next;

			}
			
			my $count2 = $#{$doc->{$sub1}[$i]{$sub2}};
			
			foreach my $j(0 .. $count2) { 
					
				foreach my $sub3 ( sort keys %{ $doc->{$sub1}[$i]{$sub2}[$j] } ){

					unless (ref($doc->{$sub1}[$i]{$sub2}[$j]{$sub3})) {
						$worksheet->Cells($rowCount, $colCount)->{Value} = $sub3;
						$worksheet->Cells($rowCount, $colCount)->Borders() -> {Weight} = 2;
						$colCount++;
						$worksheet->Cells($rowCount, $colCount)->{Value} = $doc->{$sub1}[$i]{$sub2}[$j]{$sub3};
						$worksheet->Cells($rowCount, $colCount)->Borders() -> {Weight} = 2;
						$rowCount++;
						$colCount--;							
						next;
							
	    				}

					my $count3 = $#{$doc->{$sub1}[$i]{$sub2}[$j]{$sub3}};

					foreach my $k(0 .. $count3) {
						print "Collecting general information...\n";
						
						foreach my $sub4 ( sort keys %{ $doc->{$sub1}[$i]{$sub2}[$j]{$sub3}[$k] } ){
						
							unless (ref($doc->{$sub1}[$i]{$sub2}[$j]{$sub3}[$k]{$sub4})) {
							
								$worksheet->Cells($rowCount, $colCount)->{Value} = $sub4;
								$worksheet->Cells($rowCount, $colCount)->Borders() -> {Weight} = 2;
								$colCount++;
								$worksheet->Cells($rowCount, $colCount)->{Value} = $doc->{$sub1}[$i]{$sub2}[$j]{$sub3}[$k]{$sub4};
								$worksheet->Cells($rowCount, $colCount)->Borders() -> {Weight} = 2;
								$rowCount++;
								$colCount--;							
								next;
			    				
			    				}

							
							print "Collecting $sub4 ...\n";

							my $count4 = $#{$doc->{$sub1}[$i]{$sub2}[$j]{$sub3}[$k]{$sub4}};				
							
							$subDataRow = ($count4 + 1) + ($rowCount + 1);
							
							my $worksheet = $workbook->Worksheets->Add({After=>$workbook->Worksheets($workbook->Worksheets->{Count})});
							$worksheet -> {Name} = $sub4;
							$worksheet -> Range("A1") -> Font -> {Size}= 14;
							$worksheet -> Range("A1") -> Font -> {ColorIndex}= 2;
							$worksheet -> Range("A1") -> {Value} = "$sub4";
							$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 $sub5 ( sort keys %{ $doc->{$sub1}[$i]{$sub2}[$j]{$sub3}[$k]{$sub4}[$l] } ){
									my $curID = 0;
									my $nextup = "";
 									
 									#print "\n\t$sub4 -> $sub5 -> $doc->{$sub1}[$i]{$sub2}[$j]{$sub3}[$k]{$sub4}[$l]{$sub5}";                            						
                             						if($doc->{$sub1}[$i]{$sub2}[$j]{$sub3}[$k]{$sub4}[$l]{number}){ $curID = $doc->{$sub1}[$i]{$sub2}[$j]{$sub3}[$k]{$sub4}[$l]{number};}
                             						if($doc->{$sub1}[$i]{$sub2}[$j]{$sub3}[$k]{$sub4}[$l]{enclosureNumber}){ $curID = $doc->{$sub1}[$i]{$sub2}[$j]{$sub3}[$k]{$sub4}[$l]{enclosureNumber};}
									
                             						
                             						if(ref($doc->{$sub1}[$i]{$sub2}[$j]{$sub3}[$k]{$sub4}[$l]{$sub5} )){
                             						#################
                             						# SUBINFORMATIE #
                             						#################  $sub 4 is rank $sub5 is attribuut van rank $sub6 is element binnen rank eg arrayref

	                             						my $count5 = $#{$doc->{$sub1}[$i]{$sub2}[$j]{$sub3}[$k]{$sub4}[$l]{$sub5}};

                             						
	                             							foreach my $m(0 .. $count5) {
                             						
			                             						# Nieuwe ArayRef, ExtentbyVol,StorageDeviceFRY (nieuwe titels maken

			                             						if($nextup ne $sub5){
			                             							$new = 1;
                             							
			                             							$subDataRow++;
			                             							$subHeaderRow = $subDataRow;
			                             							$subDataCol=1 ;
			                             							$nextup = $sub5;
		                             							}
		                             							else {$new = 0;}

												$subDataRow++;
	
													foreach my $sub6 ( sort keys %{ $doc->{$sub1}[$i]{$sub2}[$j]{$sub3}[$k]{$sub4}[$l]{$sub5}[$m]} ){
#                             											print "\n$sub1->$sub2->$sub3->$sub4->$sub5->$sub6 : $doc->{$sub1}[$i]{$sub2}[$j]{$sub3}[$k]{$sub4}[$l]{$sub5}[$m]{$sub6}";


												
														# subinfo title on new element
														if ($temp ne $curID){
															
															$temp = $curID;
															

															$worksheet->Cells($subDataRow, $subDataCol)->{Value} = "$sub4 : $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++;			
														}
														
														# Header 
												 		if($new==1){
															$worksheet->Cells($subHeaderRow, $subDataCol)->{Value} = $sub6; 
															$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->{$sub1}[$i]{$sub2}[$j]{$sub3}[$k]{$sub4}[$l]{$sub5}[$m]{$sub6};
														$subDataCol++;
                             										}
											$subDataCol=1;
                             								}
                             						}
                             						
                             						else{
                             						
                             						###################
                             						# MAIN INFORMATION#
                             						###################
                             						
                             						
                             							# Index, if any of those, will be placed in first column
                             							if ($sub5 eq "number" | $sub5 eq "id" | $sub5 eq "enclosureNumber"){
                             								$worksheet->Cells($mainHeaders, 1)->{Value} = $sub5;
											$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->{$sub1}[$i]{$sub2}[$j]{$sub3}[$k]{$sub4}[$l]{$sub5};
											$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} = $sub5;
    									                }
    									        
    										        if ($sub5 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->{$sub1}[$i]{$sub2}[$j]{$sub3}[$k]{$sub4}[$l]{$sub5};
                             								$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++;
											$LastRow = $worksheet->UsedRange->Find({What=>"*", SearchDirection=>xlPrevious, SearchOrder=>xlByRows})->{Row};
										}
									}
								} $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;
	}
}	
$worksheet -> Range("A:X") -> {Columns} -> Autofit;

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

}