From efe59759bd54e95e7d9b104c9c6a9d64fb709de5 Mon Sep 17 00:00:00 2001
From: Eric Bollengier <eric@eb.homelinux.org>
Date: Sun, 25 Nov 2007 21:22:13 +0000
Subject: [PATCH] ebl  Add a new job overview view

git-svn-id: https://bacula.svn.sourceforge.net/svnroot/bacula/trunk@5985 91ce42f0-d328-0410-95d8-f526ca767f89
---
 gui/bweb/cgi/bweb.pl                |  38 +++++-
 gui/bweb/lib/Bweb.pm                | 175 +++++++++++++++++++++++++---
 gui/bweb/script/bweb-mysql.sql      |   6 +
 gui/bweb/script/bweb-postgresql.sql |   6 +-
 gui/bweb/technotes-2.3              |   1 +
 gui/bweb/tpl/begin.tpl              |   1 +
 gui/bweb/tpl/display_form_job.tpl   |  20 ++++
 gui/bweb/tpl/overview.tpl           | 117 +++++++++++++++++++
 8 files changed, 342 insertions(+), 22 deletions(-)
 create mode 100644 gui/bweb/tpl/overview.tpl

diff --git a/gui/bweb/cgi/bweb.pl b/gui/bweb/cgi/bweb.pl
index 6319cb9ff0..948788cf20 100755
--- a/gui/bweb/cgi/bweb.pl
+++ b/gui/bweb/cgi/bweb.pl
@@ -328,17 +328,21 @@ if ($action eq 'begin') {		# main display
     $bweb->can_do('r_view_job');
     print "<div><table border='0'><tr><td valign='top'>\n";
     my $fields = $bweb->get_form(qw/status level filter db_clients
-				    db_filesets 
+				    db_filesets since
 				    limit age offset qclients qfilesets
 				    jobtype qpools db_pools
 				    db_client_groups qclient_groups/); # drop this to hide 
-
+    if (!CGI::param('since')) {
+	$fields->{hide_since}=1 ;
+	delete $fields->{since};
+    }
     $bweb->display($fields, "display_form_job.tpl");
 
     print "</td><td valign='top'>";
-    $bweb->display_job(age => $arg->{age},  # last 7 days
-		       offset => $arg->{offset},
-		       limit => $arg->{limit});
+    $bweb->display_job(age => $fields->{age},  # last 7 days
+		       since => $fields->{since},
+		       offset => $fields->{offset},
+		       limit => $fields->{limit});
     print "</td></tr></table></div>";
 } elsif ($action eq 'job_group') {
     $bweb->can_do('r_view_job');
@@ -348,6 +352,7 @@ if ($action eq 'begin') {		# main display
 
     $fields->{hide_status} = 1;
     $fields->{hide_type} = 1;
+    $fields->{hide_since}=1;
     $fields->{action} = 'job_group';
 
     $bweb->display($fields, "display_form_job.tpl");
@@ -449,6 +454,29 @@ if ($action eq 'begin') {		# main display
 } elsif ($action eq 'fileset_view') {
     $bweb->fileset_view();
 
+} elsif ($action eq 'overview') {
+    $bweb->can_do('r_view_job');
+    print "<div><table border='0'><tr><td valign='top'>\n";
+    my $fields = $bweb->get_form(qw/level filter age jobtype since
+				    db_client_groups qclient_groups/); # drop this to hide 
+    $fields->{action}='overview';
+    $bweb->display($fields, "display_form_job.tpl");
+
+    print "</td><td valign='top'>";
+    $bweb->display_overview();
+    print "</td></tr></table></div>";
+
+} elsif ($action eq 'overview_zoom') {
+    $bweb->can_do('r_view_job');
+    print "<div><table border='0'><tr><td valign='top'>\n";
+    my $fields = $bweb->get_form(qw/level filter age jobtype since
+				 db_client_groups qclient_groups/); # drop this to hide 
+    $fields->{action}='overview_zoom';
+    $bweb->display($fields, "display_form_job.tpl");
+    print "</td><td valign='top'>";
+    $bweb->display_overview_zoom();
+    print "</td></tr></table></div>";
+
 } else {
     $bweb->error("Sorry, this action doesn't exist");
 }
diff --git a/gui/bweb/lib/Bweb.pm b/gui/bweb/lib/Bweb.pm
index 890fc5879d..2c7b2713f2 100644
--- a/gui/bweb/lib/Bweb.pm
+++ b/gui/bweb/lib/Bweb.pm
@@ -1053,6 +1053,7 @@ our %sql_func = (
 	      STARTTIME_DAY  => " date_trunc('day', Job.StartTime) ",
 	      STARTTIME_HOUR => " date_trunc('hour', Job.StartTime) ",
 	      STARTTIME_MONTH  => " date_trunc('month', Job.StartTime) ",
+	      STARTTIME_WEEK => " date_trunc('week', Job.StartTime) ",
 	      STARTTIME_PHOUR=> " date_part('hour', Job.StartTime) ",
 	      STARTTIME_PDAY => " date_part('day', Job.StartTime) ",
 	      STARTTIME_PMONTH => " date_part('month', Job.StartTime) ",
@@ -1071,6 +1072,7 @@ our %sql_func = (
 	      STARTTIME_DAY  => " DATE_FORMAT(StartTime, '%Y-%m-%d') ",
 	      STARTTIME_HOUR => " DATE_FORMAT(StartTime, '%Y-%m-%d %H') ",
 	      STARTTIME_MONTH => " DATE_FORMAT(StartTime, '%Y-%m') ",
+	      STARTTIME_WEEK => " DATE_FORMAT(StartTime, '%v') ",
 	      STARTTIME_PHOUR=> " DATE_FORMAT(StartTime, '%H') ",
 	      STARTTIME_PDAY => " DATE_FORMAT(StartTime, '%d') ",
 	      STARTTIME_PMONTH => " DATE_FORMAT(StartTime, '%m') ",
@@ -1371,16 +1373,23 @@ sub get_limit
     my $limit = '';
     my $label = '';
 
-    if ($arg{age}) {
-	$limit = 
-  "AND $self->{sql}->{UNIX_TIMESTAMP}(EndTime) 
-         > 
-       ( $self->{sql}->{UNIX_TIMESTAMP}(NOW()) 
-         - 
+    if ($arg{since} and $arg{age}) {
+	my $now = "$self->{sql}->{UNIX_TIMESTAMP}(TIMESTAMP '$arg{since}')";
+	$limit .= "
+ AND $self->{sql}->{UNIX_TIMESTAMP}(StartTime) > $now 
+ AND $self->{sql}->{UNIX_TIMESTAMP}(EndTime) < ($now + $self->{sql}->{TO_SEC}($arg{age}))";
+	$label .= "since $arg{since} and during " . human_sec($arg{age});
+
+    } elsif ($arg{age}) {
+        $limit .=
+  "AND $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
+         >
+       ( $self->{sql}->{UNIX_TIMESTAMP}(NOW())
+         -
          $self->{sql}->{TO_SEC}($arg{age})
        )" ;
 
-	$label = "last " . human_sec($arg{age});
+        $label = "last " . human_sec($arg{age});
     }
 
     if ($arg{groupby}) {
@@ -1490,7 +1499,6 @@ sub get_form
 		 where  => 1,
 		 );
     my %opt_r = (regexwhere => 1);
-
     my %opt_d = (		# option with date
 		 voluseduration=> 1,
 		 volretention => 1,
@@ -1542,7 +1550,7 @@ sub get_form
 	    if ($value =~ /^\s*(\d+\s+\w+)$/) {
 		$ret{$i} = $1;
 	    }
-	}
+	} 
     }
 
     if ($what{slots}) {
@@ -1553,6 +1561,14 @@ sub get_form
 	}
     }
 
+    if ($what{since}) {
+        my $age = $ret{age} || $opt_i{age};
+        my $since = CGI::param('since') || strftime('%F %T', localtime(time - $age));
+        if ($since =~ /^(\d{4}-\d{2}-\d{2}( \d{2}:\d{2}:\d{2})?)$/) {
+            $ret{since} = $1;
+        }
+    }
+
     if ($what{when}) {
 	my $when = CGI::param('when') || '';
 	if ($when =~ /^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})$/) {
@@ -1588,7 +1604,6 @@ SELECT Client.Name as clientname
 SELECT client_group_name AS name 
   FROM client_group $filter
 ";
-
 	my $grps = $self->dbh_selectall_hashref($query, 'name');
 	$ret{db_client_groups} = [sort {$a->{name} cmp $b->{name} } 
 				  values %$grps] ;
@@ -1599,7 +1614,6 @@ SELECT client_group_name AS name
 SELECT username 
   FROM bweb_user
 ";
-
 	my $users = $self->dbh_selectall_hashref($query, 'username');
 	$ret{db_usernames} = [sort {$a->{username} cmp $b->{username} } 
 				  values %$users] ;
@@ -1610,7 +1624,6 @@ SELECT username
 SELECT rolename 
   FROM bweb_role
 ";
-
 	my $r = $self->dbh_selectall_hashref($query, 'rolename');
 	$ret{db_roles} = [sort {$a->{rolename} cmp $b->{rolename} } 
 				  values %$r] ;
@@ -1621,7 +1634,6 @@ SELECT rolename
 SELECT MediaType as mediatype
   FROM MediaType
 ";
-
 	my $media = $self->dbh_selectall_hashref($query, 'mediatype');
 	$ret{db_mediatypes} = [sort {$a->{mediatype} cmp $b->{mediatype} } 
 			          values %$media] ;
@@ -1651,7 +1663,6 @@ SELECT Location as location, Cost as cost
 SELECT FileSet.FileSet AS fileset 
   FROM FileSet
 ";
-
 	my $filesets = $self->dbh_selectall_hashref($query, 'fileset');
 
 	$ret{db_filesets} = [sort {lc($a->{fileset}) cmp lc($b->{fileset}) } 
@@ -1667,7 +1678,6 @@ SELECT FileSet.FileSet AS fileset
 SELECT DISTINCT Job.Name AS jobname 
   FROM Job $filter
 ";
-
 	my $jobnames = $self->dbh_selectall_hashref($query, 'jobname');
 
 	$ret{db_jobnames} = [sort {lc($a->{jobname}) cmp lc($b->{jobname}) } 
@@ -1679,7 +1689,6 @@ SELECT DISTINCT Job.Name AS jobname
 SELECT Device.Name AS name
   FROM Device
 ";
-
 	my $devices = $self->dbh_selectall_hashref($query, 'name');
 
 	$ret{db_devices} = [sort {lc($a->{name}) cmp lc($b->{name}) } 
@@ -3272,6 +3281,140 @@ GROUP BY client_group.client_group_name
     $self->display($row, "display_client_stats.tpl");
 }
 
+# [ name, num, value, joberrors, nb_job ] =>
+# {  items => 
+#      [ { name => 'ALL',
+#          events => [ { num => 1, label => '2007-01', 
+#                        value => 'T', title => 10 },
+#                      { num => 2, label => '2007-02', 
+#                        value => 'R', title => 11 },
+#                     ]
+#         },
+#         { name => 'Other',
+#            ...
+#       ]
+# };
+sub make_overview_tab
+{
+    my ($self, $q) = @_;
+    my $ret = $self->dbh_selectall_arrayref($q);
+    my @items;
+    my $events=[];
+    my $cur_name='';
+    for my $elt (@$ret) {
+	if ($cur_name and $cur_name ne $elt->[0]) { # order by name, num
+	    push @items, { name => $cur_name, events => $events};
+	    $events = [];
+	}
+	$cur_name = $elt->[0];
+	push @$events, 
+	  { num => $elt->[1], status => $elt->[2], 
+	    joberrors => $elt->[3], title => "$elt->[4] jobs"};
+    }
+    push @items, { name => $cur_name, events => $events};
+    return \@items;
+}
+
+sub get_time_overview
+{
+    my ($self, $arg) = @_; # want since et age from get_form();
+    my $type = CGI::param('type') || 'day';
+    if ($type =~ /^(day|week|hour|month)$/) {
+	$type = uc($1);
+    } else {
+	$type = 'DAY';
+    }
+    my $jobt = $self->{info}->{stat_job_table} || 'Job';
+    my $stime1 = $self->{sql}->{"STARTTIME_P" . $type}; # get 1,2,3
+    $stime1 =~ s/Job.StartTime/date/;
+    my $stime2 = $self->{sql}->{"STARTTIME_" . $type}; # get 2007-01-03, 2007-01-23
+
+    my ($limit, $label) = $self->get_limit('since' => $arg->{since},
+					   'age' => $arg->{age});
+    return ($stime1, $stime2, $limit, $label, $jobt);
+}
+
+#              lu ma me je ve sa di
+#  groupe1     v  v  x  w  v  v  v    overview
+#   |-- s1     v  v  v  v  v  v  v    overview_zoom
+#   |-- s2     v  v  x  v  v  v  v
+#   `-- s3     v  v  v  w  v  v  v
+sub display_overview_zoom
+{
+    my ($self) = @_;
+    $self->can_do('r_view_stat');
+
+    my $arg = $self->get_form(qw/jclient_groups age since/);
+
+    if (!$arg->{jclient_groups}) {
+	return $self->error("Can't get client_group selection");
+    }
+    my ($filter2, undef) = $self->get_param(qw/level jobtype/);
+    my ($stime1, $stime2, $limit, $label, $jobt) = $self->get_time_overview($arg);
+
+    my $filter = $self->get_client_filter();
+    my $q = "
+SELECT name, $stime1 AS num,
+       JobStatus AS value, joberrors, nb_job
+FROM (
+  SELECT $stime2        AS date,
+	 Client.Name    AS name,
+         MAX(severity)  AS severity,
+         COUNT(1)       AS nb_job,
+         SUM(JobErrors) AS joberrors
+    FROM $jobt AS Job
+    JOIN client_group_member USING (ClientId)
+    JOIN client_group        USING (client_group_id)
+    JOIN Client              USING (ClientId)  $filter
+    JOIN Status              USING (JobStatus)
+   WHERE client_group_name IN ($arg->{jclient_groups})
+         $limit $filter2
+
+   GROUP BY Client.Name, date
+) AS sub JOIN Status USING (severity)
+ ORDER BY name, date
+";
+    my $items = $self->make_overview_tab($q);
+    $self->display({label => $label,
+		    action => "job;since=$arg->{since};age=$arg->{age};client=", 
+		    items => $items}, "overview.tpl");
+}
+
+sub display_overview
+{
+    my ($self) = @_ ;
+    $self->can_do('r_view_stat');
+
+    my $arg = $self->get_form(qw/jclient_groups age since/);
+    my ($filter2, undef) = $self->get_param(qw/client_groups level jobtype/);
+    my $filter3 = $self->get_client_group_filter();
+    my ($stime1, $stime2, $filter1, $label, $jobt) = $self->get_time_overview($arg);
+
+    my $q = "
+SELECT name, $stime1 AS num, 
+       JobStatus AS value, joberrors, nb_job
+FROM (
+  SELECT $stime2        AS date, 
+         client_group_name AS name,
+         MAX(severity)  AS severity,
+         COUNT(1)       AS nb_job,
+	 SUM(JobErrors) AS joberrors
+    FROM $jobt AS Job
+    JOIN client_group_member USING (ClientId)
+    JOIN client_group        USING (client_group_id) $filter3
+    JOIN Status              USING (JobStatus)
+   WHERE true $filter1 $filter2
+   GROUP BY client_group_name, date
+) AS sub JOIN Status USING (severity)
+ ORDER BY name, date
+";
+    my $items = $self->make_overview_tab($q);
+    $self->display({label=>$label,
+		    action => "overview_zoom;since=$arg->{since};age=$arg->{age};client_group=", 
+		    items => $items}, "overview.tpl");
+
+}
+
 # poolname can be undef
 sub display_pool
 {
diff --git a/gui/bweb/script/bweb-mysql.sql b/gui/bweb/script/bweb-mysql.sql
index 2e21aaf82c..667c07c30d 100644
--- a/gui/bweb/script/bweb-mysql.sql
+++ b/gui/bweb/script/bweb-mysql.sql
@@ -2,6 +2,12 @@
 -- Upgrade from 2.2
 -- --------------------------------------------------
 
+ALTER TABLE Status ADD COLUMN severity int;
+UPDATE status SET severity = 15;
+UPDATE status SET severity = 100 where jobstatus = 'f';
+UPDATE status SET severity = 90 where jobstatus = 'A';
+UPDATE status SET severity = 10 where jobstatus = 'T';
+
 -- New tables for bresto (same as brestore)
 
 CREATE TABLE brestore_knownjobid
diff --git a/gui/bweb/script/bweb-postgresql.sql b/gui/bweb/script/bweb-postgresql.sql
index b02acb5598..babb4f9c43 100644
--- a/gui/bweb/script/bweb-postgresql.sql
+++ b/gui/bweb/script/bweb-postgresql.sql
@@ -1,8 +1,12 @@
-
 -- --------------------------------------------------
 -- Upgrade from 2.2
 -- --------------------------------------------------
 
+ALTER TABLE Status ADD COLUMN severity int;
+UPDATE status SET severity = 15;
+UPDATE status SET severity = 100 where jobstatus = 'f';
+UPDATE status SET severity = 90 where jobstatus = 'A';
+UPDATE status SET severity = 10 where jobstatus = 'T';
 
 -- New tables for bresto (same as brestore)
 
diff --git a/gui/bweb/technotes-2.3 b/gui/bweb/technotes-2.3
index dc1098aa48..d11700dee8 100644
--- a/gui/bweb/technotes-2.3
+++ b/gui/bweb/technotes-2.3
@@ -1,5 +1,6 @@
 25Nov07
 ebl  Use a automatic rediction when launching a new job
+ebl  Add new overview view.
 
 24Nov07
 ebl  Add ERR= to error log filter
diff --git a/gui/bweb/tpl/begin.tpl b/gui/bweb/tpl/begin.tpl
index 3463f11dbd..2fc7fb5188 100644
--- a/gui/bweb/tpl/begin.tpl
+++ b/gui/bweb/tpl/begin.tpl
@@ -30,6 +30,7 @@ if (navigator.appName == 'Konqueror') {
    <ul> 
      <li><a href="bweb.pl?action=run_job">Defined Jobs</a>
      <li><a href="bweb.pl?action=job_group">Jobs by group</a>
+     <li><a href="bweb.pl?action=overview">Jobs overview</a>
      <li><a href="bweb.pl?action=job">Last Jobs</a> </li>
      <li><a href="bweb.pl?action=running">Running Jobs</a>
      <li><a href="bweb.pl?action=next_job">Next Jobs</a> </li>
diff --git a/gui/bweb/tpl/display_form_job.tpl b/gui/bweb/tpl/display_form_job.tpl
index b402d3d90a..8386539bf7 100644
--- a/gui/bweb/tpl/display_form_job.tpl
+++ b/gui/bweb/tpl/display_form_job.tpl
@@ -43,6 +43,25 @@
   </td>
 </tr>
 </TMPL_IF>
+<TMPL_UNLESS hide_since>
+<tr>
+  <td valign='top'>
+    <h2>Since</h2>
+    <input type='text' name='since' size='17' title='YYY-MM-DD'
+     value='<TMPL_VAR since>' class='formulaire'>
+  </td>
+ </tr>
+<tr>
+  <td valign='top'>
+    <h2>Age</h2>
+    <select name='age' class='formulaire'>
+      <option id='age_604800'   value='604800'>1 week</option>
+      <option id='age_2678400'  value='2678400'>30 days</option>
+      <option id='age_15552000' value='15552000'>6 month</option>
+    </select>     
+  </td>
+ </tr>
+<TMPL_ELSE>
 <tr>
   <td valign='top'>
     <h2>Age</h2>
@@ -53,6 +72,7 @@
     </select>     
   </td>
  </tr>
+</TMPL_UNLESS>
  <tr>
   <td valign='bottom'> 
     <h2>Number of items</h2>
diff --git a/gui/bweb/tpl/overview.tpl b/gui/bweb/tpl/overview.tpl
new file mode 100644
index 0000000000..9f44534259
--- /dev/null
+++ b/gui/bweb/tpl/overview.tpl
@@ -0,0 +1,117 @@
+<style type="text/css">
+table.sample {
+	border-width: 1px 1px 1px 1px;
+	border-spacing: 3px;
+	border-style: solid solid solid solid;
+	border-color: black black black black;
+	border-collapse: collapse;
+	background-color: white;
+}
+table.sample th {
+	border-width: 1px 1px 1px 1px;
+	padding: 4px 4px 4px 4px;
+	border-style: solid solid solid solid;
+	border-color: gray gray gray gray;
+	background-color: white;
+}
+table.sample td {
+	border-width: 1px 1px 1px 1px;
+	padding: 4px 4px 4px 4px;
+	border-style: solid solid solid solid;
+	border-color: gray gray gray gray;
+	background-color: white;
+}
+</style>
+ <div class='titlediv'>
+  <h1 class='newstitle'> Jobs overview (<TMPL_VAR label>)</h1>
+ </div>
+ <div class='bodydiv'>
+  <table class='sample' id='report'>
+   <tr id='days'><td/>
+  </table>
+ </div>
+
+
+
+<script type="text/javascript" language="JavaScript">
+
+var table = document.getElementById('report');
+var nodate = new Array();
+var nb_col=1;
+var tr; var td; var img; var infos;
+var all = new Array();
+var max_cel=0;
+var min_cel=200;
+
+<TMPL_LOOP items>
+infos = new Array();
+ <TMPL_LOOP events>
+ min_cel=(min_cel< <TMPL_VAR num>)?min_cel:<TMPL_VAR num>;
+ infos[<TMPL_VAR num>] = new Array('<TMPL_VAR num>', '<TMPL_VAR status>', 
+                                   <TMPL_VAR joberrors>, '<TMPL_VAR title>');
+ </TMPL_LOOP>
+max_cel=(max_cel>infos.length)?max_cel:infos.length;
+all.push({ name: "<TMPL_VAR name>", values: infos});
+</TMPL_LOOP>
+
+//infos = new Array();
+//infos[1] = new Array('2007-10-01', 'T', 8);
+//infos[2] = new Array('2007-10-02', 'T', 8);
+//infos[5] = new Array('2007-10-05', 'R', 8);
+//
+//max_cel=(max_cel>infos.length)?max_cel:infos.length;
+//all.push({ name: "zog", values: infos});
+
+function init_tab() // initialize the table
+{
+    for(var j=min_cel; j < max_cel ; j++) {
+       var t=document.createElement("TD");
+       t.setAttribute("id", "day" + j);
+       nodate[j]=1;
+       document.getElementById("days").appendChild(t);
+    }
+}
+
+function add_client(name, infos)
+{
+    tr=document.createElement("TR"); // client row
+    table.appendChild(tr);
+
+    td=document.createElement("TD"); // client name
+    tr.appendChild(td);
+    a=document.createElement("A");
+    a.setAttribute("href", "?action=<TMPL_VAR action>" + name);
+    a.appendChild(document.createTextNode(name));
+    td.appendChild(a);
+    var len = infos.length;
+
+    for(var j=min_cel; j < max_cel ; j++) { // one img for each days
+        td=document.createElement("TD"); 
+        tr.appendChild(td);
+        if (len > j && infos[j]) {
+	    if (nodate[j] == 1) { // put the date in the first row if empty
+	       var t = document.getElementById("day" + j);
+	       t.appendChild(document.createTextNode(infos[j][0]));
+	       nodate[j]=0;
+	    }
+//	    a=document.createElement("A"); // create a link to action=job
+//	    a.setAttribute('href', "?action=job;client_group=" + name);
+            img=document.createElement("IMG");
+            img.setAttribute("src", bweb_get_job_img(infos[j][1],infos[j][2]));
+            img.setAttribute("title", infos[j][3]);
+//	    a.appendChild(img);
+            td.appendChild(img);
+        } else {
+//            td.appendChild(document.createTextNode('N/A'));
+        }
+    }
+}
+
+init_tab();
+
+for(var i=0; i<all.length; i++) {
+   var elt = all[i];
+   add_client(elt['name'], elt['values']);
+}
+
+</script>
-- 
2.39.5