bin/BackupPC_xls_report

   1 #!/usr/bin/perl
   2
   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 hostname
  17 type
  18 num
  19 startTime
  20 endTime
  21 =E##-D##,duration
  22 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 =W##/T##,sizeNewRatio
  44 sizeExistComp
  45 =Y##/U##,sizeExistRatio
  46 );
  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 my $size_format = $workbook->add_format(num_format => '#0');
  53
  54 my $formats = {
  55         duration => $duration_format,
  56         sizeNewRatio => $pcnt_format,
  57         sizeExistRatio => $pcnt_format,
  58         size => $size_format,
  59         sizeNew => $size_format,
  60         sizeExist => $size_format,
  61         sizeNewComp => $size_format,
  62         sizeExistComp => $size_format,
  63 };
  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 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 foreach my $host_key (sort keys %{$hosts}) {
  86
  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         foreach my $backup (@backups) {
  96                 $worksheet->write($row, 0, $hostname);
  97
  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                                 $worksheet->write($row, $i, $backup->{$col}, $formats->{$col});
 111                         }
 112                 }
 113                 $row++;
 114         }
 115
 116
 117 }
 118
 119 $workbook->close();
 120
 121 print "Created $xls_file\n";
 122