#!/usr/bin/perl
use strict;
use warnings;
use Data::Dumper;
use feature 'say';
my %header_value;
my %count; #hachage local -> le contenu des fichiers et lu dans ce hachage à l'instant T
my %count_corp_nbb2 = fill_hash ("SVC_CORP_NBB2.txt"); #fill_hash ouvre le fichier reçu en paramètre, ferme le fichier et renvoie le contenu dans %count_
my %count_corp_nbb4 = fill_hash ("SVC_CORP_NBB4.txt");
my %count_dmz_nbb2 = fill_hash ("SVC_DMZ_NBB2.txt"); #fill_hash ouvre le fichier reçu en paramètre, ferme le fichier et renvoie le contenu dans %count_+
my %count_dmz_nbb4 = fill_hash ("SVC_DMZ_NBB4.txt");

my $saut = 1;
my $number=0;
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime();
my $filexls = sprintf("Inventory_SVC_%04d%02d%02d.xlsx",$year+1900,$mon+1,$mday);

sub fill_hash {

my $fichier = shift;
open my $FH, "<", $fichier or die "Unable to open file $fichier";
my %count;
#my $saut = 1; # n'est plus nécessaire


while (my $line = <$FH>) {	# première boucle pour lire le premier groupe de lignes
	last if $line =~ /^id:name:IO_group_id:/;	# sort de cette boucle et passe à la suivante pour la suite des données
	next if $line =~/id:name:status:mdisk_count:/;  	# saute la ligne d'entête
	next if $line =~ /^\s*$/;  	# saute d'éventuelles lignes vides
	my ($id, $name, $capacity, $free) = (split /:/, $line)[0, 1, 5, 7];
	next unless defined $free;
	$header_value{$id} = { name => $name, capacity => $capacity, free_capacity => $free};	# stockage des valeurs qui t'intéressent dans un hash
}	

#for my $key (keys %header_value) { #Affichage des données
#	say "\tItem NR: $key";
#	say "\tName: $header_value{$key}{name}";
#	say "\tCapacity: $header_value{$key}{capacity}";
#	say "\tFree Capacity: $header_value{$key}{free_capacity}";
#	}

#print Dumper \%header_value;

while (my $line = <$FH>) {
	#$saut = 0 if $line =~ /^id:name:IO_group_id:/;	# plus nécessaire, fait dans la première boucle
	#next if $saut;									# plus nécessaire non plus
		next if $line =~ /^\s*$/;					# saute d'éventuelles lignes vides
		next if $line =~ /RC = 0/;
	my ($name, $capacity) =  (split /:/, $line)[1, 7];
	
	if ($capacity =~ /^(\d+\.\d+)(\w+)$/) {
		my $number = $1;
		my $type = $2;
		
		#print "$number $type \n"; #Pour test
		$number = $number / 1000 if $type eq 'GB'; #Convertis en TB si le type est GB
		$number = $number / 1000000 if $type eq 'MB'; #Convertis en TB si le type est MB
		
		#SQL#
		if ($name =~ /SQL/xmsi) {
			$count{SQL} += $number;
		#ORACLE#	
		} elsif ($name =~ /OR\d/xmsi) {
			$count{Oracle} += $number;
		#SOLARIS#		
		} elsif ($name =~ /SCN/xmsi or $name =~ /SUN/xmsi) {
			$count{Solaris} += $number;
		#FILE SERVER#
		} elsif ($name =~ /CLUFILP/xmsi or $name =~ /CLUDATP/xmsi or $name =~ /CLUWINP/xmsi) {
			$count{Filer} += $number;
		#VMWARE#
		} elsif ($name =~ /VMware/xmsi) {
			$count{VMware} += $number;
		#ARCHIVING#
		} elsif ($name =~ /ARCHP/xmsi or $name =~ /ARCP/xmsi) {
			$count{Archiving} += $number;
		#NETBACKUP#
		} elsif ($name =~ /NBU/xmsi or $name =~ /OPS/xmsi) {
			$count{NetBackup} += $number;
		#PAS ENCORE DE CATEGORIE#	
		} else {
			$count{Other} += $number;
			$count{"ZZ_".$name} += $number;
		}
	};
}

#print Dumper \%header_value;
#print "Capacity for $_ = $count{$_} TB\n" for sort keys %count;
#print "$_ = $count{$_} TB\n" for grep { /^ZZ/ } keys %count;

close($FH);
return %count;
} 
#EXCEL paramètres, modules et variables
use Excel::Writer::XLSX;
use Spreadsheet::WriteExcel;

my $workbook = Excel::Writer::XLSX->new($filexls); #Création du fichier Excel
my $worksheet = $workbook->add_worksheet('Data'); #Création d'une sheet
my $chart_corp = $workbook->add_chart(type=>'column', name=>'Chart CORP'); #Création d'une chart
my $chart_dmz = $workbook->add_chart(type=>'column', name=>'Chart DMZ');
my $chart_total = $workbook->add_chart(type=>'column', name=>'Chart Total');
#Format  EXCEL	
my $Datatype = $workbook->add_format(bold => 1, right => 2, italic => 1); #Format  des colonnes "Data Type"
my $Data = $workbook->add_format(align => 'center', valign => 'vcenter', right => 2); #Format des colonnes "Data"
my $TypeBottom = $workbook->add_format (bold => 1, border => 2, bg_color => 'silver'); #Format de la ligne "Total"
my $DataBottom = $workbook->add_format (bold =>1, align => 'center', right => 2, top => 2, bottom => 2, bg_color => 'silver'); #Format de la ligne "Total" mais Data
my $formatcorp = $workbook->add_format (bold => 1,align => 'center',valign => 'vcenter',size => 14,border => 2,bg_color => 	44); #Format des titres CORP	
my $formatdmz = $workbook->add_format (bold => 1, align => 'center',valign => 'vcenter',size => 14,border => 2,bg_color => 42); #Format des titres DMZ
my $formattotal = $workbook->add_format (bold => 1, align => 'center',valign => 'vcenter',size => 14,border => 2,bg_color => 'red'); #Format des titres TOTAL
my $formattotal_texte = $workbook->add_format (bold =>1, align => 'center', valign => 'vcenter', size => 11, border => 2, bg_color => 'yellow', italic => 1); #Format de la ligne Total ALL
my $formattotal_nombre = $workbook->add_format  (bold =>1, align => 'center', valign => 'vcenter', size => 11, border => 2, bg_color => 'yellow'); #format de la ligne Total ALL mais Data$

my $cpt = 3;
for my $item (grep { /^ZZ_/ } keys %count) {
    $worksheet->write("J$cpt", "$count{$item}", $Data);
    $worksheet->write("K$cpt", "$count{$item}", $Data);
    $cpt ++;
}

##############################DATA CORP##############################
$worksheet->set_column('A:A', 12); #Défini la Colonne A sur 12 (taille)
$worksheet->set_column('B:B', 10);
$worksheet->set_column('C:C', 10);

$worksheet->merge_range('A1:C1', 'CORP', $formatcorp); #Pour merge des cellules ensembles
$worksheet->write("A2","Data Type", $formatcorp);
$worksheet->write("B2","NBB2", $formatcorp);
$worksheet->write("C2","NBB4", $formatcorp);

my $compteur = 3;
for my $item (qw / Archiving Filer NetBackup Oracle SQL 
                    Solaris VMware Other /) {
    $worksheet->write("A$compteur","$item", $Datatype);
    $worksheet->write("B$compteur","$count_corp_nbb2{$item}", $Data);
    $worksheet->write("C$compteur","$count_corp_nbb4{$item}", $Data);
    $compteur ++;
};


$worksheet->write("A11","Total (TB)", $TypeBottom);
$worksheet->write("B11","=SUM(B3:B10)", $DataBottom);
$worksheet->write("C11","=SUM(C3:C10)", $DataBottom);
##############################DATA CORP##############################


##############################DATA DMZ##############################
$worksheet->set_column('D:D', 12); #Défini la Colonne A sur 15 (taille)
$worksheet->set_column('E:E', 10);
$worksheet->set_column('F:F', 10);

$worksheet->merge_range('D1:F1', 'DMZ', $formatdmz); #Pour merge des cellules ensembles
$worksheet->write("D2","Data Type", $formatdmz);
$worksheet->write("E2","NBB2", $formatdmz);
$worksheet->write("F2","NBB4", $formatdmz);

$compteur = 3;
for my $item (qw / Archiving Filer NetBackup Oracle SQL 
                    Solaris VMware Other /) {
    $worksheet->write("D$compteur","$item", $Datatype);
    $worksheet->write("E$compteur","$count_dmz_nbb2{$item}", $Data);
    $worksheet->write("F$compteur","$count_dmz_nbb4{$item}", $Data);
    $compteur ++;
};

$worksheet->write("D11","Total (TB)", $TypeBottom);
$worksheet->write("E11","=SUM(E3:E10)", $DataBottom);
$worksheet->write("F11","=SUM(F3:F10)", $DataBottom);
##############################DATA DMZ################################

##############################DATA TOTAL##############################
$worksheet->set_column('G:G', 12); #Défini la Colonne A sur 15 (taille)
$worksheet->set_column('H:H', 10);
$worksheet->set_column('F:F', 10); 

$worksheet->merge_range('G1:I1', 'TOTAL', $formattotal); #Pour merge des cellules ensembles selon le format de $formattotal
$worksheet->write("G2","Data Type", $formattotal);
$worksheet->write("H2","NBB2", $formattotal);
$worksheet->write("I2","NBB4", $formattotal);

$compteur = 3;
for my $item (qw / Archiving Filer NetBackup Oracle SQL 
                    Solaris VMware Other /) {
    $worksheet->write("G$compteur","$item", $Datatype);
    $worksheet->write("H$compteur","=B$compteur+E$compteur", $Data);
    $worksheet->write("I$compteur","=C$compteur+F$compteur", $Data);
    $compteur ++;
};

$worksheet->write("G11","Total (TB)", $TypeBottom);
$worksheet->write("H11","=B11+E11", $DataBottom);
$worksheet->write("I11","=C11+F11", $DataBottom);

$worksheet->merge_range('A13:G13', 'TOTAL CORP (NBB2+NBB4) & DMZ (NBB2+NBB4) IN TERABYTE', $formattotal_texte);
$worksheet->merge_range('H13:I13', '=H11+I11', $formattotal_nombre);
##############################DATA TOTAL##############################

##############################STORAGE##############################

$worksheet->set_column('K:K', 26); 
$worksheet->write("K1","Name");


my $row = 1;                             # démarre avec une autre valeur si tu ne veux pas commencer sur la première ligne
for my $key (keys %header_value) { 
    my $col = 9;                         # idem, initialise $col à une autre valeur si tu veux les valeurs sur des colonnes plus à droite
    for my $label (qw / item name capacity free_capacity / ) {       
        $worksheet->write($row, $col, $header_value{$key}{$label} );
        $col++;
    }
    $row++;
}

##############################STORAGE##############################

##############################CHART CORP##############################
$chart_corp->add_series(
	name => '=Data!$A$3',
	categories => '=Data!$B$2:$C$2',
	values => '=Data!$B$3:$C$3');
	
$chart_corp->add_series(
	name => '=Data!$A$4',
	categories => '=Data!$B$2:$C$2',
	values => '=Data!$B$4:$C$4');
	
$chart_corp->add_series(
	name => '=Data!$A$5',
	categories => '=Data!$B$2:$C$2',
	values => '=Data!$B$5:$C$5');
	
$chart_corp->add_series(
	name => '=Data!$A$6',
	categories => '=Data!$B$2:$C$2',
	values => '=Data!$B$6:$C$6');
	
$chart_corp->add_series(
	name => '=Data!$A$7',
	categories => '=Data!$B$2:$C$2',
	values => '=Data!$B$7:$C$7');

$chart_corp->add_series(
	name => '=Data!$A$8',
	categories => '=Data!$B$2:$C$2',
	values => '=Data!$B$8:$C$8');
	
$chart_corp->add_series(
	name => '=Data!$A$9',
	categories => '=Data!$B$2:$C$2',
	values => '=Data!$B$9:$C$9');
	
$chart_corp->add_series(
	name => '=Data!$A$10',
	categories => '=Data!$B$2:$C$2',
	values => '=Data!$B$10:$C$10');
	
$chart_corp->set_title(name => 'SVC Capacity by types - CORP'); #Paramètres des axes de la chart
$chart_corp->set_x_axis(name => 'Types');
$chart_corp->set_y_axis(name => 'Capacity (Terabytes)');
##############################CHART CORP##############################

##############################CHART DMZ###############################
$chart_dmz->add_series(
	name => '=Data!$D$3',
	categories => '=Data!$E$2:$F$2',
	values => '=Data!$E$3:$F$3');
	
$chart_dmz->add_series(
	name => '=Data!$D$4',
	categories => '=Data!$E$2:$F$2',
	values => '=Data!$E$4:$F$4');
	
$chart_dmz->add_series(
	name => '=Data!$D$5',
	categories => '=Data!$E$2:$F$2',
	values => '=Data!$E$5:$F$5');
	
$chart_dmz->add_series(
	name => '=Data!$D$6',
	categories => '=Data!$E$2:$F$2',
	values => '=Data!$E$6:$F$6');
	
$chart_dmz->add_series(
	name => '=Data!$D$7',
	categories => '=Data!$E$2:$F$2',
	values => '=Data!$E$7:$F$7');
	
$chart_dmz->add_series(
	name => '=Data!$D$8',
	categories => '=Data!$E$2:$F$2',
	values => '=Data!$E$8:$F$8');
	
$chart_dmz->add_series(
	name => '=Data!$D$9',
	categories => '=Data!$E$2:$F$2',
	values => '=Data!$E$9:$F$9');
	
$chart_dmz->add_series(
	name => '=Data!$D$10',
	categories => '=Data!$E$2:$F$2',
	values => '=Data!$E$10:$F$10');
	
$chart_dmz->set_title(name => 'SVC Capacity by types - DMZ'); #Paramètres des axes de la chart
$chart_dmz->set_x_axis(name => 'Types');
$chart_dmz->set_y_axis(name => 'Capacity (Terabytes)');
##############################CHART DMZ##############################

##############################CHART TOTAL###############################
$chart_total->add_series(
	name => '=Data!$G$3',
	categories => '=Data!$H$2:$I$2',
	values => '=Data!$H$3:$I$3');

$chart_total->add_series(
	name => '=Data!$G$4',
	categories => '=Data!$H$2:$I$2',
	values => '=Data!$H$4:$I$4');

$chart_total->add_series(
	name => '=Data!$G$5',
	categories => '=Data!$H$2:$I$2',
	values => '=Data!$H$5:$I$5');

$chart_total->add_series(
	name => '=Data!$G$6',
	categories => '=Data!$H$2:$I$2',
	values => '=Data!$H$6:$I$6');

$chart_total->add_series(
	name => '=Data!$G$7',
	categories => '=Data!$H$2:$I$2',
	values => '=Data!$H$7:$I$7');

$chart_total->add_series(
	name => '=Data!$G$8',
	categories => '=Data!$H$2:$I$2',
	values => '=Data!$H$8:$I$8');

$chart_total->add_series(
	name => '=Data!$G$9',
	categories => '=Data!$H$2:$I$2',
	values => '=Data!$H$9:$I$9');

$chart_total->add_series(
	name => '=Data!$G$10',
	categories => '=Data!$H$2:$I$2',
	values => '=Data!$H$10:$I$10');	
	
$chart_total->set_title(name => 'SVC Capacity by types - TOTAL'); #Paramètres des axes de la chart
$chart_total->set_x_axis(name => 'Types');
$chart_total->set_y_axis(name => 'Capacity (Terabytes)');
##############################CHART TOTAL##############################

$workbook->close;