#!/usr/bin/perl -w
# Usage: To get today's report, run with no parameters
#        To get a report from yesterday's backups, execute this with the parameter 'yesterday' (w/o quotes)
#
# Original author statement: 
## only works with mysql currently, should work with postgres
## (c) 2007 Falk Stern (falk.stern AT akquinet DOT de) 
## akquinet System Integration GmbH - http://www.akquinet.de/
## published under GPLv2
## 
## November 5, 2010: Greatly adapted in 2009 and 2010 by Bob Hetzel beh AT case DOT edu to produce 
## listing of failed backups from the last 10 days.
# 


use DBI;
use DBD::mysql;
use Data::Dumper;
use Date::Calc qw(Today Add_Delta_Days Day_of_Week_to_Text Day_of_Week Month_to_Text);
#use strict;


my $user = "bacula";
my $password = "";
my $database = "bacula";
my $dbhost = "localhost";
my $dsn = "DBI:mysql:database=$database;host=$dbhost;port=3306";
my $dbh = DBI->connect($dsn, $user, $password);
my $sth;

my $reportday = $ARGV[0];

my (undef,undef,undef,$mday,$mon,$year,$wday,undef,undef) = localtime;

($year,$mon,$mday) = Today();
if ($reportday eq 'yesterday') {
  ($year,$mon,$mday) = Add_Delta_Days($year,$mon,$mday,-1);
  $wday=Day_of_Week($year,$mon,$mday);
}
#$year = $year - 1900;

#print "Year = $year \n";
#print "Month = $mon \n";
#print "Day of month = $mday \n";
#print "Weekday = $wday \n";

#print "nowish = $nowish\n";
my @days = ('Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday');
my @months = ('January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December');
 

#$year += 1900;
#$mon++;
$mon = sprintf("%02d",$mon);
#$mon = 11;
#print "MDAY = $mday \n";
$mday = sprintf("%02d",$mday);
#print "MDAY = $mday \n";
#$mday = 20;

my %jobstates = (
	"A" => "canceled by admin",
	"B" => "blocked",
	"C" => "created, but not running",
	"c" => "waiting for client resource",
	"D" => "verify differences",
	"d" => "waiting for maximum jobs",
	"E" => "term. in error",
	"e" => "term. with non-fatal error",
	"f" => "term. with fatal error",
	"F" => "waiting on File Daemon",
	"j" => "waiting for job resource",
	"M" => "waiting for mount",
	"m" => "waiting for new media",
	"p" => "waiting for higher priority jobs to finish",
	"R" => "running",
	"S" => "scan",
	"s" => "waiting for storage resource",
	"T" => "OK",
	"t" => "waiting for start time"
);

my %levels = (
    "F" => "Full",
    "D" => "Differential",
	"C" => "Verify from catalog",
	"V" => "Verify init db",
	"O" => "Verify volume to catalog",
	"d" => "Verify disk to catalog",
	"A" => "Verify data on volume",
	"B" => "Base job",
	"I" => "Incremental"
);


my ($jid,$cname,$jsize,$jobdur,$jlev,$jstatus,$jrate,$jst);


print "\nBacula Failures report for $days[$wday] $months[$mon-1] $mday, $year\n";

print "\n  The following computers haven't been backed up in the last 10 days:\n\n";

print "\n";

format STDOUT_TOP =
    Client              Last Good Backup  
    ------------------  ---------------- 
.

format STDOUT = 
@<< @<<<<<<<<<<<<<<<<<  @<<<<<<<<<<<<<<< 
$cn,$cname,             $jst
.


# get job totals 

my $report_query_allbad = "select Name from (select Name,sum(if (JobStatus = 'T',1,0)) as TotSuccesses from Job group by Name) SuccessTable 
				where TotSuccesses = 0 and Name in 
					(select name from Job where StartTime like \"$year-$mon-$mday\%\" and jobstatus <>'T')";


my $report_query_somegood="select Name,LastGoodDate from 
		     (select Name,max(EndTime) as LastGoodDate from Job where JobStatus='T' group by Name) LastGoodTable 
			where LastGoodTable.LastGoodDate < (date_sub(now(), interval '10' day)) and Name in 
			   (select name from Job where StartTime  like \"$year-$mon-$mday\%\" and jobstatus <>'T') order by LastGoodDate,Name";

$sth = $dbh->prepare($report_query_allbad);
$sth->execute;
$cn=0;
while ($row = $sth->fetchrow_hashref)  {
  $cname = $row->{"Name"};
  $jst = "NEVER";
  $cn++;
  write;
}
$sth = $dbh->prepare($report_query_somegood);
$sth->execute;

while ($row = $sth->fetchrow_hashref)  {
  $cname = $row->{"Name"};
  $jst = $row->{"LastGoodDate"};
  $cn++;
  write;
}