Annotation of /trunk/bin/BackupPC_xls_report

#!/usr/bin/perl

use strict;
use lib "__INSTALLDIR__/lib";
use BackupPC::Lib;
use BackupPC::View;
use Data::Dumper;
use Spreadsheet::WriteExcel;
use POSIX qw/strftime/;

my $xls_file = '/tmp/report.xls';

my $workbook  = Spreadsheet::WriteExcel->new($xls_file) || die "can't write to $xls_file: $!";

my @cols = qw(
hostname
type
num
startTime
endTime
=E##-D##,duration
level
noFill
fillFromNum
mangle

nFiles
nFilesNew
nFilesExist

xferErrs
xferMethod
xferBadShare
xferBadFile
tarErrs

size
sizeNew
sizeExist

compress
sizeNewComp
=W##/T##,sizeNewRatio
sizeExistComp
=Y##/U##,sizeExistRatio
);

my $date_format = $workbook->add_format(num_format => 'yyyy-mm-dd hh:mm:ss');
my $duration_format = $workbook->add_format(num_format => 'hh:mm:ss');
my $header_format = $workbook->add_format(color => 'gray');
my $pcnt_format = $workbook->add_format(num_format => '0.0%');
my $size_format = $workbook->add_format(num_format => '#0');

my $formats = {
        duration => $duration_format,
        sizeNewRatio => $pcnt_format,
        sizeExistRatio => $pcnt_format,
        size => $size_format,
        sizeNew => $size_format,
        sizeExist => $size_format,
        sizeNewComp => $size_format,
        sizeExistComp => $size_format,
};

my $bpc = BackupPC::Lib->new || die;
my %Conf = $bpc->Conf();
my $TopDir = $bpc->TopDir();

my $hosts = $bpc->HostInfoRead();

my $worksheet = $workbook->add_worksheet('BackupPC');
my $row = 0;

my $i = 0;
# insert sheet header (with formatting)
foreach my $c (@cols) {
        if ($c =~ m/^=.*,(.+)/) {
                $worksheet->write($row, $i++, $1, $header_format);
        } else {
                $worksheet->write($row, $i++, $c, $header_format);
        }
}
$row++;

foreach my $host_key (sort keys %{$hosts}) {

        my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";

        print $hostname, " ";

        # get backups for a host
        my @backups = $bpc->BackupInfoRead($hostname);
        print scalar @backups, " increments\n";

        foreach my $backup (@backups) {
                $worksheet->write($row, 0, $hostname);

                for (my $i = 0; $i <= $#cols; $i++) {
                        my $col = $cols[$i];
                        if ($col =~ m/Time/) {
                                $worksheet->write_date_time($row, $i, 
                                        strftime("%Y-%m-%dT%H:%M:%S", localtime( $backup->{ $cols[$i] }) ),
                                        $date_format
                                );
                        } elsif ($col =~ m/(=[^,]+),*(.*)/) {
                                my ($f,$n) = ($1,$2);
                                $f =~ s/##/($row+1)/ge;
                                $worksheet->write($row, $i, $f, $formats->{$n});
                        } else {
                                $worksheet->write($row, $i, $backup->{$col}, $formats->{$col});
                        }
                }
                $row++;
        }


}

$workbook->close();

print "Created $xls_file\n";

1	dpavlin	108	#!/usr/bin/perl
2	dpavlin	41
3			use strict;
4			use lib "__INSTALLDIR__/lib";
5			use BackupPC::Lib;
6			use BackupPC::View;
7			use Data::Dumper;
8			use Spreadsheet::WriteExcel;
9			use POSIX qw/strftime/;
10
11			my $xls_file = '/tmp/report.xls';
12
13			my $workbook = Spreadsheet::WriteExcel->new($xls_file) \|\| die "can't write to $xls_file: $!";
14
15			my @cols = qw(
16	dpavlin	108	hostname
17	dpavlin	41	type
18			num
19			startTime
20			endTime
21	dpavlin	108	=E##-D##,duration
22	dpavlin	41	level
23			noFill
24			fillFromNum
25			mangle
26
27			nFiles
28			nFilesNew
29			nFilesExist
30
31			xferErrs
32			xferMethod
33			xferBadShare
34			xferBadFile
35			tarErrs
36
37			size
38			sizeNew
39			sizeExist
40
41			compress
42			sizeNewComp
43	dpavlin	108	=W##/T##,sizeNewRatio
44	dpavlin	41	sizeExistComp
45	dpavlin	108	=Y##/U##,sizeExistRatio
46	dpavlin	41	);
47
48			my $date_format = $workbook->add_format(num_format => 'yyyy-mm-dd hh:mm:ss');
49			my $duration_format = $workbook->add_format(num_format => 'hh:mm:ss');
50			my $header_format = $workbook->add_format(color => 'gray');
51			my $pcnt_format = $workbook->add_format(num_format => '0.0%');
52	dpavlin	43	my $size_format = $workbook->add_format(num_format => '#0');
53	dpavlin	41
54			my $formats = {
55			duration => $duration_format,
56			sizeNewRatio => $pcnt_format,
57			sizeExistRatio => $pcnt_format,
58	dpavlin	42	size => $size_format,
59			sizeNew => $size_format,
60			sizeExist => $size_format,
61			sizeNewComp => $size_format,
62			sizeExistComp => $size_format,
63	dpavlin	41	};
64
65			my $bpc = BackupPC::Lib->new \|\| die;
66			my %Conf = $bpc->Conf();
67			my $TopDir = $bpc->TopDir();
68
69			my $hosts = $bpc->HostInfoRead();
70
71	dpavlin	108	my $worksheet = $workbook->add_worksheet('BackupPC');
72			my $row = 0;
73
74			my $i = 0;
75			# insert sheet header (with formatting)
76			foreach my $c (@cols) {
77			if ($c =~ m/^=.*,(.+)/) {
78			$worksheet->write($row, $i++, $1, $header_format);
79			} else {
80			$worksheet->write($row, $i++, $c, $header_format);
81			}
82			}
83			$row++;
84
85	dpavlin	68	foreach my $host_key (sort keys %{$hosts}) {
86	dpavlin	41
87			my $hostname = $hosts->{$host_key}->{'host'} \|\| die "can't find host for $host_key";
88
89			print $hostname, " ";
90
91			# get backups for a host
92			my @backups = $bpc->BackupInfoRead($hostname);
93			print scalar @backups, " increments\n";
94
95	dpavlin	110	foreach my $backup (@backups) {
96			$worksheet->write($row, 0, $hostname);
97	dpavlin	41
98			for (my $i = 0; $i <= $#cols; $i++) {
99			my $col = $cols[$i];
100			if ($col =~ m/Time/) {
101			$worksheet->write_date_time($row, $i,
102			strftime("%Y-%m-%dT%H:%M:%S", localtime( $backup->{ $cols[$i] }) ),
103			$date_format
104			);
105			} elsif ($col =~ m/(=[^,]+),(.)/) {
106			my ($f,$n) = ($1,$2);
107			$f =~ s/##/($row+1)/ge;
108			$worksheet->write($row, $i, $f, $formats->{$n});
109			} else {
110	dpavlin	42	$worksheet->write($row, $i, $backup->{$col}, $formats->{$col});
111	dpavlin	41	}
112			}
113			$row++;
114			}
115
116
117			}
118
119			$workbook->close();
120
121			print "Created $xls_file\n";
122