#!/usr/bin/perl -w


use warnings;
no warnings 'uninitialized';
use strict;
use XML::Twig;
use XML::LibXML;
use strict;
use Spreadsheet::WriteExcel;
use Spreadsheet::WriteExcel::Utility;

my @group;					#array for group name
my @Jobs;					#array for jobname
my @Desc;					#array for description
my @Time;					#array for start time
my @Tag;					#array for tags
my @Days;					#array for days
my @Outcond;					#array for out conditions
my @Incond;					#array for in conditions
my $file;					#variable to hold file location
my $output;					#variable to hold output file name


print "This program will parse an XML file and output the data into excel for an overview of the schedule\n\n"; 	#rough menu system for user to interact with
print "Please enter the name of the xml file you wish to parse\n"; 
print "(ensure it is it the same directory, if not include the path)\n";
print "xml file name: ";

$file =<>;													#take input filename in as file variable
print "\nplease enter the name for your output xls file (e.g. Output): ";
$output=<>;													#take output filename
chomp($output);	
$output="$output.xls";												#add .xls extension to the filename

my $twig = new XML::Twig(KeepSpaces => 'true',TwigHandlers => { "JOB " => \&Output,"TAG_NAMES " => \&Tags, " SCHED_GROUP " => \&Group, " JOB/OUTCOND " => \&Outcond, " INCOND " => \&Incond});  	#create new twig with spacing kept and with 2 handlers defined below

$twig->parsefile($file);											#create twig
$twig->flush;


print "The array has " . scalar(@group) . " elements\n";	
print "The array has " . scalar(@Jobs) . " elements\n";								#print out number of elements in arrays (used as test purposes and kpet int for future debug)
print "The array has " . scalar(@Desc) . " elements\n";
print "The array has " . scalar(@Time) . " elements\n";
print "The array has " . scalar(@Tag) . " elements\n";
print "The array has " . scalar(@Days) . " elements\n";
print "The array has " . scalar(@Outcond) . " elements\n";
print "The array has " . scalar(@Incond) . " elements\n";

my $count = 1;
while ($count <= (scalar(@Jobs))) {
  print ("Jobname $Jobs[$count-1] runs on days: $Days[$count-1] and tag: $Tag[$count-1] at: $Time[$count-1] the description is: $Desc[$count-1] with Outcond $Outcond[$count-1] and In condition $Incond[$count-1] \n");		#outputs data to cmd line as a string again used as test purpose but will keep for future debug
  $count++;
}


our $workbook = Spreadsheet::WriteExcel->new($output); 								# create new spreadsheet
our %format = &get_excel_cell_formats;										#set formats based on formats defined below

our %worksheet;
# worksheet for text
$worksheet{text}   = $workbook->add_worksheet('text'); 								#create text outputs defined below
&write_text($worksheet{text});


$workbook->close();
exit;


sub Output {													#tiwg outputs that reffer to JOB tag data (all data in xml file between <JOB>...</JOB>
my( $twig, $s )= @_;
   
push(@Jobs,( $s->att('JOBNAME')));										#Reffers to the value found at JOBNAME under JOB
push(@Time,($s->att('TIMEFROM')));
push(@Desc,($s->att('DESCRIPTION')));
push(@Days,($s->att('DAYS')));

}

sub Tags {													#tiwg outputs that reffer to TAG tag data (all data in xml file between <TAG_NAMES>...</TAG_NAMES>
my( $twig, $s )= @_;
   
push(@Tag,( $s->att('TAG_NAME')));										#Reffers to the Tag name attacted to the job

}

sub Group {													#tiwg outputs that reffer to TAG tag data (all data in xml file between <TAG_NAMES>...</TAG_NAMES>
my( $twig, $s )= @_;
   
push(@group,( $s->att('GROUP')));										#Reffers to the Group name attacted to the job

}

sub Outcond {													#tiwg outputs that reffer to TAG tag data (all data in xml file between <TAG_NAMES>...</TAG_NAMES>
my( $twig, $s )= @_;
   
push(@Outcond,( $s->att('NAME')));										#Reffers to the Tag name attacted to the job

}

sub Incond {													#tiwg outputs that reffer to TAG tag data (all data in xml file between <TAG_NAMES>...</TAG_NAMES>
my( $twig, $s )= @_;
   
push(@Incond,( $s->att('NAME')));										#Reffers to the Tag name attacted to the job

}

sub get_excel_cell_formats {											#format standards for spreadsheet
    my $format;

    # bold
    $format{bold} = $workbook->add_format();
    $format{bold}->set_bold();
    return %format;
}


sub write_text {

	my $worksheet = shift;											

	# Set the column width to 15	
	$worksheet->set_column('A:A', 40);
	$worksheet->set_column('B:B', 40);	
	$worksheet->set_column('C:C', 10);
	$worksheet->set_column('D:D', 18);
	$worksheet->set_column('E:E', 12);
	$worksheet->set_column('F:F', 50);
	$worksheet->set_column('G:G', 100);
	$worksheet->set_column('H:H', 100);
	
	#create starting x and y values for each data 
	my ($x1,$y1)=(0,0);			# starting point for the headings
	my ($xgroup,$ygroup)=(0,1);		# starting point for Group name
	my ($xjobs,$yjobs)=(1,1);		# starting point for jobs
	my ($xDays,$yDays)=(2,1);		# starting point for jobs
	my ($xTags,$yTags)=(3,1);		# starting point for tags
	my ($xStarting,$yStarting)=(4,1);	# starting point for time
	my ($xDesc,$yDesc)=(5,1);		# starting point for Desc
	my ($xOut,$yOut)=(6,1);		# starting point for Desc
	my ($xIn,$yIn)=(7,1);		# starting point for Desc

	my ($xouttest,$youttest)=(8,1);		# starting point for Desc	
	
	my $count = 1;

	#create headings for all data entries
	$worksheet->write($y1, $x1++, "Group Name", $format{bold});	
	$worksheet->write($y1, $x1++, "Job Name", $format{bold});
	$worksheet->write($y1, $x1++, "Days", $format{bold});
	$worksheet->write($y1, $x1++, "Schedule Tag", $format{bold});
	$worksheet->write($y1,$x1++, "Starting time", $format{bold});		
	$worksheet->write($y1,$x1++, "Description", $format{bold});	
	$worksheet->write($y1,$x1++, "Out Conditions", $format{bold});	
	$worksheet->write($y1,$x1++, "In Conditions", $format{bold});	

	#loop all data and output in correct positions
	while ($count <= (scalar(@Jobs))) {
		$worksheet->write($ygroup, $xgroup, $group[$count-1]);
  		$worksheet->write($yjobs++, $xjobs, $Jobs[$count-1]);
  		$worksheet->write($yDays++, $xDays, $Days[$count-1]);
		$worksheet->write($yTags++, $xTags, $Tag[$count-1]);
		$worksheet->write($yStarting++, $xStarting, $Time[$count-1]);
		$worksheet->write($yDesc++, $xDesc, $Desc[$count-1]);
		$worksheet->write($yOut++, $xOut, $Outcond[$count-1]);
		$worksheet->write($yIn++, $xIn, $Incond[$count-1]);	

	$ygroup = ($ygroup+$yjobs);	
	$count++;
	}
}