1 ################################################################
6 Bweb - A Bacula web interface
7 Bacula® - The Network Backup Solution
9 Copyright (C) 2000-2007 Free Software Foundation Europe e.V.
11 The main author of Bweb is Eric Bollengier.
12 The main author of Bacula is Kern Sibbald, with contributions from
13 many others, a complete list can be found in the file AUTHORS.
15 This program is Free Software; you can redistribute it and/or
16 modify it under the terms of version two of the GNU General Public
17 License as published by the Free Software Foundation plus additions
18 that are listed in the file LICENSE.
20 This program is distributed in the hope that it will be useful, but
21 WITHOUT ANY WARRANTY; without even the implied warranty of
22 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
23 General Public License for more details.
25 You should have received a copy of the GNU General Public License
26 along with this program; if not, write to the Free Software
27 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
30 Bacula® is a registered trademark of John Walker.
31 The licensor of Bacula is the Free Software Foundation Europe
32 (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zurich,
33 Switzerland, email:ftf@fsfeurope.org.
45 Bweb::Gui - Base package for all Bweb object
49 This package define base fonction like new, display, etc..
54 our $template_dir='/usr/share/bweb/tpl';
58 new - creation a of new Bweb object
62 This function take an hash of argument and place them
65 IE : $obj = new Obj(name => 'test', age => '10');
67 $obj->{name} eq 'test' and $obj->{age} eq 10
73 my ($class, %arg) = @_;
78 map { $self->{lc($_)} = $arg{$_} } keys %arg ;
85 my ($self, $what) = @_;
89 print "<pre>" . Data::Dumper::Dumper($what) . "</pre>";
91 print "<pre>$what</pre>";
98 error - display an error to the user
102 this function set $self->{error} with arg, display a message with
103 error.tpl and return 0
108 return $self->error("Can't use this file");
115 my ($self, $what) = @_;
116 $self->{error} = $what;
117 $self->display($self, 'error.tpl');
123 display - display an html page with HTML::Template
127 this function is use to render all html codes. it takes an
128 ref hash as arg in which all param are usable in template.
130 it will use global template_dir to search the template file.
132 hash keys are not sensitive. See HTML::Template for more
133 explanations about the hash ref. (it's can be quiet hard to understand)
137 $ref = { name => 'me', age => 26 };
138 $self->display($ref, "people.tpl");
144 my ($self, $hash, $tpl) = @_ ;
146 my $template = HTML::Template->new(filename => $tpl,
147 path =>[$template_dir],
148 die_on_bad_params => 0,
149 case_sensitive => 0);
151 foreach my $var (qw/limit offset/) {
153 unless ($hash->{$var}) {
154 my $value = CGI::param($var) || '';
156 if ($value =~ /^(\d+)$/) {
157 $template->param($var, $1) ;
162 $template->param('thisurl', CGI::url(-relative => 1, -query=>1));
163 $template->param('loginname', CGI::remote_user());
165 $template->param($hash);
166 print $template->output();
170 ################################################################
172 package Bweb::Config;
174 use base q/Bweb::Gui/;
178 Bweb::Config - read, write, display, modify configuration
182 this package is used for manage configuration
186 $conf = new Bweb::Config(config_file => '/path/to/conf');
197 =head1 PACKAGE VARIABLE
199 %k_re - hash of all acceptable option.
203 this variable permit to check all option with a regexp.
207 our %k_re = ( dbi => qr/^(dbi:(Pg|mysql):(?:\w+=[\w\d\.-]+;?)+)$/i,
208 user => qr/^([\w\d\.-]+)$/i,
209 password => qr/^(.*)$/i,
210 fv_write_path => qr!^([/\w\d\.-]*)$!,
211 template_dir => qr!^([/\w\d\.-]+)$!,
212 debug => qr/^(on)?$/,
213 email_media => qr/^([\w\d\.-]+@[\d\w\.-]+)$/,
214 graph_font => qr!^([/\w\d\.-]+.ttf)$!,
215 bconsole => qr!^(.+)?$!,
216 syslog_file => qr!^(.+)?$!,
217 log_dir => qr!^(.+)?$!,
218 stat_job_table => qr!^(\w*)$!,
219 display_log_time => qr!^(on)?$!,
220 enable_security => qr/^(on)?$/,
221 enable_security_acl => qr/^(on)?$/,
226 load - load config_file
230 this function load the specified config_file.
238 unless (open(FP, $self->{config_file}))
240 return $self->error("can't load config_file $self->{config_file} : $!");
242 my $f=''; my $tmpbuffer;
243 while(read FP,$tmpbuffer,4096)
251 no strict; # I have no idea of the contents of the file
258 return $self->error("If you update from an old bweb install, your must reload this page and if it's fail again, you have to configure bweb again...") ;
261 foreach my $k (keys %$VAR1) {
262 $self->{$k} = $VAR1->{$k};
270 load_old - load old configuration format
278 unless (open(FP, $self->{config_file}))
280 return $self->error("$self->{config_file} : $!");
283 while (my $line = <FP>)
286 my ($k, $v) = split(/\s*=\s*/, $line, 2);
298 save - save the current configuration to config_file
306 if ($self->{ach_list}) {
307 # shortcut for display_begin
308 $self->{achs} = [ map {{ name => $_ }}
309 keys %{$self->{ach_list}}
313 unless (open(FP, ">$self->{config_file}"))
315 return $self->error("$self->{config_file} : $!\n" .
316 "You must add this to your config file\n"
317 . Data::Dumper::Dumper($self));
320 print FP Data::Dumper::Dumper($self);
328 edit, view, modify - html form ouput
336 $self->display($self, "config_edit.tpl");
342 $self->display($self, "config_view.tpl");
350 # we need to reset checkbox first
352 $self->{display_log_time} = 0;
353 $self->{enable_security} = 0;
354 $self->{enable_security_acl} = 0;
356 foreach my $k (CGI::param())
358 next unless (exists $k_re{$k}) ;
359 my $val = CGI::param($k);
360 if ($val =~ $k_re{$k}) {
363 $self->{error} .= "bad parameter : $k = [$val]";
369 if ($self->{error}) { # an error as occured
370 $self->display($self, 'error.tpl');
378 ################################################################
380 package Bweb::Client;
382 use base q/Bweb::Gui/;
386 Bweb::Client - Bacula FD
390 this package is use to do all Client operations like, parse status etc...
394 $client = new Bweb::Client(name => 'zog-fd');
395 $client->status(); # do a 'status client=zog-fd'
401 display_running_job - Html display of a running job
405 this function is used to display information about a current job
409 sub display_running_job
411 my ($self, $conf, $jobid) = @_ ;
413 my $status = $self->status($conf);
416 if ($status->{$jobid}) {
417 $self->display($status->{$jobid}, "client_job_status.tpl");
420 for my $id (keys %$status) {
421 $self->display($status->{$id}, "client_job_status.tpl");
428 $client = new Bweb::Client(name => 'plume-fd');
430 $client->status($bweb);
434 dirty hack to parse "status client=xxx-fd"
438 JobId 105 Job Full_plume.2006-06-06_17.22.23 is running.
439 Backup Job started: 06-jun-06 17:22
440 Files=8,971 Bytes=194,484,132 Bytes/sec=7,480,158
441 Files Examined=10,697
442 Processing file: /home/eric/.openoffice.org2/user/config/standard.sod
448 JobName => Full_plume.2006-06-06_17.22.23,
451 Bytes => 194,484,132,
461 my ($self, $conf) = @_ ;
463 if (defined $self->{cur_jobs}) {
464 return $self->{cur_jobs} ;
468 my $b = new Bconsole(pref => $conf);
469 my $ret = $b->send_cmd("st client=$self->{name}");
473 for my $r (split(/\n/, $ret)) {
475 $r =~ s/(^\s+|\s+$)//g;
476 if ($r =~ /JobId (\d+) Job (\S+)/) {
478 $arg->{$jobid} = { @param, JobId => $jobid } ;
482 @param = ( JobName => $2 );
484 } elsif ($r =~ /=.+=/) {
485 push @param, split(/\s+|\s*=\s*/, $r) ;
487 } elsif ($r =~ /=/) { # one per line
488 push @param, split(/\s*=\s*/, $r) ;
490 } elsif ($r =~ /:/) { # one per line
491 push @param, split(/\s*:\s*/, $r, 2) ;
495 if ($jobid and @param) {
496 $arg->{$jobid} = { @param,
498 Client => $self->{name},
502 $self->{cur_jobs} = $arg ;
508 ################################################################
510 package Bweb::Autochanger;
512 use base q/Bweb::Gui/;
516 Bweb::Autochanger - Object to manage Autochanger
520 this package will parse the mtx output and manage drives.
524 $auto = new Bweb::Autochanger(precmd => 'sudo');
526 $auto = new Bweb::Autochanger(precmd => 'ssh root@robot');
530 $auto->slot_is_full(10);
531 $auto->transfer(10, 11);
537 my ($class, %arg) = @_;
540 name => '', # autochanger name
541 label => {}, # where are volume { label1 => 40, label2 => drive0 }
542 drive => [], # drive use [ 'media1', 'empty', ..]
543 slot => [], # slot use [ undef, 'empty', 'empty', ..] no slot 0
544 io => [], # io slot number list [ 41, 42, 43...]
545 info => {slot => 0, # informations (slot, drive, io)
549 mtxcmd => '/usr/sbin/mtx',
551 device => '/dev/changer',
552 precmd => '', # ssh command
553 bweb => undef, # link to bacula web object (use for display)
556 map { $self->{lc($_)} = $arg{$_} } keys %arg ;
563 status - parse the output of mtx status
567 this function will launch mtx status and parse the output. it will
568 give a perlish view of the autochanger content.
570 it uses ssh if the autochanger is on a other host.
577 my @out = `$self->{precmd} $self->{mtxcmd} -f $self->{device} status` ;
579 # TODO : reset all infos
580 $self->{info}->{drive} = 0;
581 $self->{info}->{slot} = 0;
582 $self->{info}->{io} = 0;
584 #my @out = `cat /home/eric/travail/brestore/plume/mtx` ;
587 # Storage Changer /dev/changer:2 Drives, 45 Slots ( 5 Import/Export )
588 #Data Transfer Element 0:Full (Storage Element 1 Loaded):VolumeTag = 000000
589 #Data Transfer Element 1:Empty
590 # Storage Element 1:Empty
591 # Storage Element 2:Full :VolumeTag=000002
592 # Storage Element 3:Empty
593 # Storage Element 4:Full :VolumeTag=000004
594 # Storage Element 5:Full :VolumeTag=000001
595 # Storage Element 6:Full :VolumeTag=000003
596 # Storage Element 7:Empty
597 # Storage Element 41 IMPORT/EXPORT:Empty
598 # Storage Element 41 IMPORT/EXPORT:Full :VolumeTag=000002
603 # Storage Element 7:Empty
604 # Storage Element 2:Full :VolumeTag=000002
605 if ($l =~ /Storage Element (\d+):(Empty|Full)(\s+:VolumeTag=([\w\d]+))?/){
608 $self->set_empty_slot($1);
610 $self->set_slot($1, $4);
613 } elsif ($l =~ /Data Transfer.+(\d+):(Full|Empty)(\s+.Storage Element (\d+) Loaded.(:VolumeTag = ([\w\d]+))?)?/) {
616 $self->set_empty_drive($1);
618 $self->set_drive($1, $4, $6);
621 } elsif ($l =~ /Storage Element (\d+).+IMPORT\/EXPORT:(Empty|Full)( :VolumeTag=([\d\w]+))?/)
624 $self->set_empty_io($1);
626 $self->set_io($1, $4);
629 # Storage Changer /dev/changer:2 Drives, 30 Slots ( 1 Import/Export )
631 } elsif ($l =~ /Storage Changer .+:(\d+) Drives, (\d+) Slots/) {
632 $self->{info}->{drive} = $1;
633 $self->{info}->{slot} = $2;
634 if ($l =~ /(\d+)\s+Import/) {
635 $self->{info}->{io} = $1 ;
637 $self->{info}->{io} = 0;
642 $self->debug($self) ;
647 my ($self, $slot) = @_;
650 if ($self->{slot}->[$slot] eq 'loaded') {
654 my $label = $self->{slot}->[$slot] ;
656 return $self->is_media_loaded($label);
661 my ($self, $drive, $slot) = @_;
663 return 0 if (not defined $drive or $self->{drive}->[$drive] eq 'empty') ;
664 return 0 if ($self->slot_is_full($slot)) ;
666 my $out = `$self->{precmd} $self->{mtxcmd} -f $self->{device} unload $slot $drive 2>&1`;
669 my $content = $self->get_slot($slot);
670 print "content = $content<br/> $drive => $slot<br/>";
671 $self->set_empty_drive($drive);
672 $self->set_slot($slot, $content);
675 $self->{error} = $out;
680 # TODO: load/unload have to use mtx script from bacula
683 my ($self, $drive, $slot) = @_;
685 return 0 if (not defined $drive or $self->{drive}->[$drive] ne 'empty') ;
686 return 0 unless ($self->slot_is_full($slot)) ;
688 print "Loading drive $drive with slot $slot<br/>\n";
689 my $out = `$self->{precmd} $self->{mtxcmd} -f $self->{device} load $slot $drive 2>&1`;
692 my $content = $self->get_slot($slot);
693 print "content = $content<br/> $slot => $drive<br/>";
694 $self->set_drive($drive, $slot, $content);
697 $self->{error} = $out;
705 my ($self, $media) = @_;
707 unless ($self->{label}->{$media}) {
711 if ($self->{label}->{$media} =~ /drive\d+/) {
721 return (defined $self->{info}->{io} and $self->{info}->{io} > 0);
726 my ($self, $slot, $tag) = @_;
727 $self->{slot}->[$slot] = $tag || 'full';
728 push @{ $self->{io} }, $slot;
731 $self->{label}->{$tag} = $slot;
737 my ($self, $slot) = @_;
739 push @{ $self->{io} }, $slot;
741 unless ($self->{slot}->[$slot]) { # can be loaded (parse before)
742 $self->{slot}->[$slot] = 'empty';
748 my ($self, $slot) = @_;
749 return $self->{slot}->[$slot];
754 my ($self, $slot, $tag) = @_;
755 $self->{slot}->[$slot] = $tag || 'full';
758 $self->{label}->{$tag} = $slot;
764 my ($self, $slot) = @_;
766 unless ($self->{slot}->[$slot]) { # can be loaded (parse before)
767 $self->{slot}->[$slot] = 'empty';
773 my ($self, $drive) = @_;
774 $self->{drive}->[$drive] = 'empty';
779 my ($self, $drive, $slot, $tag) = @_;
780 $self->{drive}->[$drive] = $tag || $slot;
782 $self->{slot}->[$slot] = $tag || 'loaded';
785 $self->{label}->{$tag} = "drive$drive";
791 my ($self, $slot) = @_;
793 # slot don't exists => full
794 if (not defined $self->{slot}->[$slot]) {
798 if ($self->{slot}->[$slot] eq 'empty') {
801 return 1; # vol, full, loaded
804 sub slot_get_first_free
807 for (my $slot=1; $slot < $self->{info}->{slot}; $slot++) {
808 return $slot unless ($self->slot_is_full($slot));
812 sub io_get_first_free
816 foreach my $slot (@{ $self->{io} }) {
817 return $slot unless ($self->slot_is_full($slot));
824 my ($self, $media) = @_;
826 return $self->{label}->{$media} ;
831 my ($self, $media) = @_;
833 return defined $self->{label}->{$media} ;
838 my ($self, $slot) = @_;
840 unless ($self->slot_is_full($slot)) {
841 print "Autochanger $self->{name} slot $slot is empty\n";
846 if ($self->is_slot_loaded($slot)) {
849 print "Autochanger $self->{name} $slot is currently in use\n";
853 # autochanger must have I/O
854 unless ($self->have_io()) {
855 print "Autochanger $self->{name} don't have I/O, you can take media yourself\n";
859 my $dst = $self->io_get_first_free();
862 print "Autochanger $self->{name} you must empty I/O first\n";
865 $self->transfer($slot, $dst);
870 my ($self, $src, $dst) = @_ ;
871 if ($self->{debug}) {
872 print "<pre>$self->{precmd} $self->{mtxcmd} -f $self->{device} transfer $src $dst</pre>\n";
874 my $out = `$self->{precmd} $self->{mtxcmd} -f $self->{device} transfer $src $dst 2>&1`;
877 my $content = $self->get_slot($src);
878 $self->{slot}->[$src] = 'empty';
879 $self->set_slot($dst, $content);
882 $self->{error} = $out;
889 my ($self, $index) = @_;
890 return $self->{drive_name}->[$index];
893 # TODO : do a tapeinfo request to get informations
903 for my $slot (@{$self->{io}})
905 if ($self->is_slot_loaded($slot)) {
906 print "$slot is currently loaded\n";
910 if ($self->slot_is_full($slot))
912 my $free = $self->slot_get_first_free() ;
913 print "move $slot to $free :\n";
916 if ($self->transfer($slot, $free)) {
917 print "<img src='/bweb/T.png' alt='ok'><br/>\n";
919 print "<img src='/bweb/E.png' alt='ok' title='$self->{error}'><br/>\n";
923 $self->{error} = "<img src='/bweb/E.png' alt='ok' title='E : Can t find free slot'><br/>\n";
929 # TODO : this is with mtx status output,
930 # we can do an other function from bacula view (with StorageId)
934 my $bweb = $self->{bweb};
936 # $self->{label} => ('vol1', 'vol2', 'vol3', ..);
937 my $media_list = $bweb->dbh_join( keys %{ $self->{label} });
940 SELECT Media.VolumeName AS volumename,
941 Media.VolStatus AS volstatus,
942 Media.LastWritten AS lastwritten,
943 Media.VolBytes AS volbytes,
944 Media.MediaType AS mediatype,
946 Media.InChanger AS inchanger,
948 $bweb->{sql}->{FROM_UNIXTIME}(
949 $bweb->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
950 + $bweb->{sql}->{TO_SEC}(Media.VolRetention)
953 INNER JOIN Pool USING (PoolId)
955 WHERE Media.VolumeName IN ($media_list)
958 my $all = $bweb->dbh_selectall_hashref($query, 'volumename') ;
960 # TODO : verify slot and bacula slot
964 for (my $slot=1; $slot <= $self->{info}->{slot} ; $slot++) {
966 if ($self->slot_is_full($slot)) {
968 my $vol = $self->{slot}->[$slot];
969 if (defined $all->{$vol}) { # TODO : autochanger without barcodes
971 my $bslot = $all->{$vol}->{slot} ;
972 my $inchanger = $all->{$vol}->{inchanger};
974 # if bacula slot or inchanger flag is bad, we display a message
975 if ($bslot != $slot or !$inchanger) {
976 push @to_update, $slot;
979 $all->{$vol}->{realslot} = $slot;
981 push @{ $param }, $all->{$vol};
983 } else { # empty or no label
984 push @{ $param }, {realslot => $slot,
985 volstatus => 'Unknown',
986 volumename => $self->{slot}->[$slot]} ;
989 push @{ $param }, {realslot => $slot, volumename => 'empty'} ;
993 my $i=0; my $drives = [] ;
994 foreach my $d (@{ $self->{drive} }) {
995 $drives->[$i] = { index => $i,
996 load => $self->{drive}->[$i],
997 name => $self->{drive_name}->[$i],
1002 $bweb->display({ Name => $self->{name},
1003 nb_drive => $self->{info}->{drive},
1004 nb_io => $self->{info}->{io},
1007 Update => scalar(@to_update) },
1015 ################################################################
1019 use base q/Bweb::Gui/;
1023 Bweb - main Bweb package
1027 this package is use to compute and display informations
1032 use POSIX qw/strftime/;
1034 our $config_file='/etc/bacula/bweb.conf';
1040 %sql_func - hash to make query mysql/postgresql compliant
1046 UNIX_TIMESTAMP => '',
1047 FROM_UNIXTIME => '',
1048 TO_SEC => " interval '1 second' * ",
1049 SEC_TO_INT => "SEC_TO_INT",
1052 STARTTIME_DAY => " date_trunc('day', Job.StartTime) ",
1053 STARTTIME_HOUR => " date_trunc('hour', Job.StartTime) ",
1054 STARTTIME_MONTH => " date_trunc('month', Job.StartTime) ",
1055 STARTTIME_PHOUR=> " date_part('hour', Job.StartTime) ",
1056 STARTTIME_PDAY => " date_part('day', Job.StartTime) ",
1057 STARTTIME_PMONTH => " date_part('month', Job.StartTime) ",
1058 STARTTIME_PWEEK => " date_part('week', Job.StartTime) ",
1059 DB_SIZE => " SELECT pg_database_size(current_database()) ",
1060 CAT_POOL_TYPE => " MediaType || '_' || Pool.Name ",
1064 UNIX_TIMESTAMP => 'UNIX_TIMESTAMP',
1065 FROM_UNIXTIME => 'FROM_UNIXTIME',
1068 SEC_TO_TIME => 'SEC_TO_TIME',
1069 MATCH => " REGEXP ",
1070 STARTTIME_DAY => " DATE_FORMAT(StartTime, '%Y-%m-%d') ",
1071 STARTTIME_HOUR => " DATE_FORMAT(StartTime, '%Y-%m-%d %H') ",
1072 STARTTIME_MONTH => " DATE_FORMAT(StartTime, '%Y-%m') ",
1073 STARTTIME_PHOUR=> " DATE_FORMAT(StartTime, '%H') ",
1074 STARTTIME_PDAY => " DATE_FORMAT(StartTime, '%d') ",
1075 STARTTIME_PMONTH => " DATE_FORMAT(StartTime, '%m') ",
1076 STARTTIME_PWEEK => " DATE_FORMAT(StartTime, '%v') ",
1077 # with mysql < 5, you have to play with the ugly SHOW command
1078 DB_SIZE => " SELECT 0 ",
1079 # works only with mysql 5
1080 # DB_SIZE => " SELECT sum(DATA_LENGTH) FROM INFORMATION_SCHEMA.TABLES ",
1081 CAT_POOL_TYPE => " CONCAT(MediaType,'_',Pool.Name) ",
1082 CONCAT_SEP => " SEPARATOR '' ",
1089 return $self->{info}->{dbi} =~ /dbi:mysql/i;
1096 $self->{dbh}->disconnect();
1101 sub dbh_selectall_arrayref
1103 my ($self, $query) = @_;
1104 $self->connect_db();
1105 $self->debug($query);
1106 return $self->{dbh}->selectall_arrayref($query);
1111 my ($self, @what) = @_;
1112 return join(',', $self->dbh_quote(@what)) ;
1117 my ($self, @what) = @_;
1119 $self->connect_db();
1121 return map { $self->{dbh}->quote($_) } @what;
1123 return $self->{dbh}->quote($what[0]) ;
1129 my ($self, $query) = @_ ;
1130 $self->connect_db();
1131 $self->debug($query);
1132 return $self->{dbh}->do($query);
1135 sub dbh_selectall_hashref
1137 my ($self, $query, $join) = @_;
1139 $self->connect_db();
1140 $self->debug($query);
1141 return $self->{dbh}->selectall_hashref($query, $join) ;
1144 sub dbh_selectrow_hashref
1146 my ($self, $query) = @_;
1148 $self->connect_db();
1149 $self->debug($query);
1150 return $self->{dbh}->selectrow_hashref($query) ;
1155 my ($self, @what) = @_;
1156 if ($self->dbh_is_mysql()) {
1157 return 'CONCAT(' . join(',', @what) . ')' ;
1159 return join(' || ', @what);
1165 my ($self, $query) = @_;
1166 $self->debug($query, up => 1);
1167 return $self->{dbh}->prepare($query);
1173 my @unit = qw(B KB MB GB TB);
1174 my $val = shift || 0;
1176 my $format = '%i %s';
1177 while ($val / 1024 > 1) {
1181 $format = ($i>0)?'%0.1f %s':'%i %s';
1182 return sprintf($format, $val, $unit[$i]);
1185 # display Day, Hour, Year
1191 $val /= 60; # sec -> min
1193 if ($val / 60 <= 1) {
1197 $val /= 60; # min -> hour
1198 if ($val / 24 <= 1) {
1199 return "$val hours";
1202 $val /= 24; # hour -> day
1203 if ($val / 365 < 2) {
1207 $val /= 365 ; # day -> year
1209 return "$val years";
1215 my $val = shift || 0;
1217 if ($val eq '1' or $val eq "yes") {
1219 } elsif ($val eq '2' or $val eq "archived") {
1227 sub from_human_enabled
1229 my $val = shift || 0;
1231 if ($val == 1 or $val eq "yes") {
1233 } elsif ($val == 2 or $val eq "archived") {
1240 # get Day, Hour, Year
1246 unless ($val =~ /^\s*(\d+)\s*(\w)\w*\s*$/) {
1250 my %times = ( m => 60,
1256 my $mult = $times{$2} || 0;
1266 unless ($self->{dbh}) {
1268 $self->{dbh} = DBI->connect($self->{info}->{dbi},
1269 $self->{info}->{user},
1270 $self->{info}->{password});
1272 $self->error("Can't connect to your database:\n$DBI::errstr\n")
1273 unless ($self->{dbh});
1275 $self->{dbh}->{FetchHashKeyName} = 'NAME_lc';
1277 if ($self->dbh_is_mysql()) {
1278 $self->{dbh}->do("SET group_concat_max_len=1000000");
1280 $self->{dbh}->do("SET datestyle TO 'ISO, YMD'");
1287 my ($class, %arg) = @_;
1289 dbh => undef, # connect_db();
1291 dbi => '', # DBI:Pg:database=bacula;host=127.0.0.1
1297 map { $self->{lc($_)} = $arg{$_} } keys %arg ;
1299 if ($self->{info}->{dbi} =~ /DBI:(\w+):/i) {
1300 $self->{sql} = $sql_func{$1};
1303 $self->{loginname} = CGI::remote_user();
1304 $self->{debug} = $self->{info}->{debug};
1305 $Bweb::Gui::template_dir = $self->{info}->{template_dir};
1313 $self->display($self->{info}, "begin.tpl");
1319 $self->display($self->{info}, "end.tpl");
1325 my $where=''; # by default
1327 my $arg = $self->get_form("client", "qre_client",
1328 "jclient_groups", "qnotingroup");
1330 if ($arg->{qre_client}) {
1331 $where = "WHERE Name $self->{sql}->{MATCH} $arg->{qre_client} ";
1332 } elsif ($arg->{client}) {
1333 $where = "WHERE Name = '$arg->{client}' ";
1334 } elsif ($arg->{jclient_groups}) {
1335 # $filter could already contains client_group_member
1337 JOIN client_group_member USING (ClientId)
1338 JOIN client_group USING (client_group_id)
1339 WHERE client_group_name IN ($arg->{jclient_groups}) ";
1340 } elsif ($arg->{qnotingroup}) {
1343 (SELECT 1 FROM client_group_member
1344 WHERE Client.ClientId = client_group_member.ClientId
1350 SELECT Name AS name,
1352 AutoPrune AS autoprune,
1353 FileRetention AS fileretention,
1354 JobRetention AS jobretention
1355 FROM Client " . $self->get_client_filter() .
1358 my $all = $self->dbh_selectall_hashref($query, 'name') ;
1360 my $dsp = { ID => $cur_id++,
1361 clients => [ values %$all] };
1363 $self->display($dsp, "client_list.tpl") ;
1368 my ($self, %arg) = @_;
1375 "AND $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
1377 ( $self->{sql}->{UNIX_TIMESTAMP}(NOW())
1379 $self->{sql}->{TO_SEC}($arg{age})
1382 $label = "last " . human_sec($arg{age});
1385 if ($arg{groupby}) {
1386 $limit .= " GROUP BY $arg{groupby} ";
1390 $limit .= " ORDER BY $arg{order} ";
1394 $limit .= " LIMIT $arg{limit} ";
1395 $label .= " limited to $arg{limit}";
1399 $limit .= " OFFSET $arg{offset} ";
1400 $label .= " with $arg{offset} offset ";
1404 $label = 'no filter';
1407 return ($limit, $label);
1412 $bweb->get_form(...) - Get useful stuff
1416 This function get and check parameters against regexp.
1418 If word begin with 'q', the return will be quoted or join quoted
1419 if it's end with 's'.
1424 $bweb->get_form('jobid', 'qclient', 'qpools') ;
1427 qclient => 'plume-fd',
1428 qpools => "'plume-fd', 'test-fd', '...'",
1435 my ($self, @what) = @_;
1436 my %what = map { $_ => 1 } @what;
1458 my %opt_ss =( # string with space
1462 my %opt_s = ( # default to ''
1483 my %opt_p = ( # option with path
1490 my %opt_r = (regexwhere => 1);
1492 my %opt_d = ( # option with date
1497 foreach my $i (@what) {
1498 if (exists $opt_i{$i}) {# integer param
1499 my $value = CGI::param($i) || $opt_i{$i} ;
1500 if ($value =~ /^(\d+)$/) {
1503 } elsif ($opt_s{$i}) { # simple string param
1504 my $value = CGI::param($i) || '';
1505 if ($value =~ /^([\w\d\.-]+)$/) {
1508 } elsif ($opt_ss{$i}) { # simple string param (with space)
1509 my $value = CGI::param($i) || '';
1510 if ($value =~ /^([\w\d\.\-\s]+)$/) {
1513 } elsif ($i =~ /^j(\w+)s$/) { # quote join args "'arg1', 'arg2'"
1514 my @value = grep { ! /^\s*$/ } CGI::param($1) ;
1516 $ret{$i} = $self->dbh_join(@value) ;
1519 } elsif ($i =~ /^q(\w+[^s])$/) { # 'arg1'
1520 my $value = CGI::param($1) ;
1522 $ret{$i} = $self->dbh_quote($value);
1525 } elsif ($i =~ /^q(\w+)s$/) { #[ 'arg1', 'arg2']
1526 $ret{$i} = [ map { { name => $self->dbh_quote($_) } }
1527 grep { ! /^\s*$/ } CGI::param($1) ];
1528 } elsif (exists $opt_p{$i}) {
1529 my $value = CGI::param($i) || '';
1530 if ($value =~ /^([\w\d\.\/\s:\@\-]+)$/) {
1533 } elsif (exists $opt_r{$i}) {
1534 my $value = CGI::param($i) || '';
1535 if ($value =~ /^([^'"']+)$/) {
1538 } elsif (exists $opt_d{$i}) {
1539 my $value = CGI::param($i) || '';
1540 if ($value =~ /^\s*(\d+\s+\w+)$/) {
1547 foreach my $s (CGI::param('slot')) {
1548 if ($s =~ /^(\d+)$/) {
1549 push @{$ret{slots}}, $s;
1555 my $when = CGI::param('when') || '';
1556 if ($when =~ /^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})$/) {
1561 if ($what{db_clients}) {
1563 if ($what{filter}) {
1564 # get security filter only if asked
1565 $filter = $self->get_client_filter();
1569 SELECT Client.Name as clientname
1573 my $clients = $self->dbh_selectall_hashref($query, 'clientname');
1574 $ret{db_clients} = [sort {$a->{clientname} cmp $b->{clientname} }
1578 if ($what{db_client_groups}) {
1580 if ($what{filter}) {
1581 # get security filter only if asked
1582 $filter = $self->get_client_group_filter();
1586 SELECT client_group_name AS name
1587 FROM client_group $filter
1590 my $grps = $self->dbh_selectall_hashref($query, 'name');
1591 $ret{db_client_groups} = [sort {$a->{name} cmp $b->{name} }
1595 if ($what{db_usernames}) {
1601 my $users = $self->dbh_selectall_hashref($query, 'username');
1602 $ret{db_usernames} = [sort {$a->{username} cmp $b->{username} }
1606 if ($what{db_roles}) {
1612 my $r = $self->dbh_selectall_hashref($query, 'rolename');
1613 $ret{db_roles} = [sort {$a->{rolename} cmp $b->{rolename} }
1617 if ($what{db_mediatypes}) {
1619 SELECT MediaType as mediatype
1623 my $media = $self->dbh_selectall_hashref($query, 'mediatype');
1624 $ret{db_mediatypes} = [sort {$a->{mediatype} cmp $b->{mediatype} }
1628 if ($what{db_locations}) {
1630 SELECT Location as location, Cost as cost
1633 my $loc = $self->dbh_selectall_hashref($query, 'location');
1634 $ret{db_locations} = [ sort { $a->{location}
1640 if ($what{db_pools}) {
1641 my $query = "SELECT Name as name FROM Pool";
1643 my $all = $self->dbh_selectall_hashref($query, 'name') ;
1644 $ret{db_pools} = [ sort { $a->{name} cmp $b->{name} } values %$all ];
1647 if ($what{db_filesets}) {
1649 SELECT FileSet.FileSet AS fileset
1653 my $filesets = $self->dbh_selectall_hashref($query, 'fileset');
1655 $ret{db_filesets} = [sort {lc($a->{fileset}) cmp lc($b->{fileset}) }
1656 values %$filesets] ;
1659 if ($what{db_jobnames}) {
1661 if ($what{filter}) {
1662 $filter = " JOIN Client USING (ClientId) " . $self->get_client_filter();
1665 SELECT DISTINCT Job.Name AS jobname
1669 my $jobnames = $self->dbh_selectall_hashref($query, 'jobname');
1671 $ret{db_jobnames} = [sort {lc($a->{jobname}) cmp lc($b->{jobname}) }
1672 values %$jobnames] ;
1675 if ($what{db_devices}) {
1677 SELECT Device.Name AS name
1681 my $devices = $self->dbh_selectall_hashref($query, 'name');
1683 $ret{db_devices} = [sort {lc($a->{name}) cmp lc($b->{name}) }
1694 my $fields = $self->get_form(qw/age level status clients filesets
1695 graph gtype type filter db_clients
1696 limit db_filesets width height
1697 qclients qfilesets qjobnames db_jobnames/);
1700 my $url = CGI::url(-full => 0,
1703 $url =~ s/^.+?\?//; # http://path/to/bweb.pl?arg => arg
1705 # this organisation is to keep user choice between 2 click
1706 # TODO : fileset and client selection doesn't work
1715 sub get_selected_media_location
1719 my $media = $self->get_form('jmedias');
1721 unless ($media->{jmedias}) {
1726 SELECT Media.VolumeName AS volumename, Location.Location AS location
1727 FROM Media LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
1728 WHERE Media.VolumeName IN ($media->{jmedias})
1731 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1733 # { 'vol1' => { [volumename => 'vol1', location => 'ici'],
1742 my ($self, $in) = @_ ;
1744 my $media = $self->get_selected_media_location();
1750 my $elt = $self->get_form('db_locations');
1752 $self->display({ ID => $cur_id++,
1753 enabled => human_enabled($in),
1754 %$elt, # db_locations
1756 sort { $a->{volumename} cmp $b->{volumename} } values %$media
1766 my $elt = $self->get_form(qw/db_pools db_mediatypes db_locations/) ;
1768 $self->display($elt, "help_extern.tpl");
1771 sub help_extern_compute
1775 my $number = CGI::param('limit') || '' ;
1776 unless ($number =~ /^(\d+)$/) {
1777 return $self->error("Bad arg number : $number ");
1780 my ($sql, undef) = $self->get_param('pools',
1781 'locations', 'mediatypes');
1784 SELECT Media.VolumeName AS volumename,
1785 Media.VolStatus AS volstatus,
1786 Media.LastWritten AS lastwritten,
1787 Media.MediaType AS mediatype,
1788 Media.VolMounts AS volmounts,
1790 Media.Recycle AS recycle,
1791 $self->{sql}->{FROM_UNIXTIME}(
1792 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1793 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1796 INNER JOIN Pool ON (Pool.PoolId = Media.PoolId)
1797 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
1799 WHERE Media.InChanger = 1
1800 AND Media.VolStatus IN ('Disabled', 'Error', 'Full')
1802 ORDER BY expire DESC, recycle, Media.VolMounts DESC
1806 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1808 $self->display({ Media => [ values %$all ] },
1809 "help_extern_compute.tpl");
1816 my $param = $self->get_form(qw/db_locations db_pools db_mediatypes/) ;
1817 $self->display($param, "help_intern.tpl");
1820 sub help_intern_compute
1824 my $number = CGI::param('limit') || '' ;
1825 unless ($number =~ /^(\d+)$/) {
1826 return $self->error("Bad arg number : $number ");
1829 my ($sql, undef) = $self->get_param('pools', 'locations', 'mediatypes');
1831 if (CGI::param('expired')) {
1833 AND ( $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1834 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1840 SELECT Media.VolumeName AS volumename,
1841 Media.VolStatus AS volstatus,
1842 Media.LastWritten AS lastwritten,
1843 Media.MediaType AS mediatype,
1844 Media.VolMounts AS volmounts,
1846 $self->{sql}->{FROM_UNIXTIME}(
1847 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1848 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1851 INNER JOIN Pool ON (Pool.PoolId = Media.PoolId)
1852 LEFT JOIN Location ON (Location.LocationId = Media.LocationId)
1854 WHERE Media.InChanger <> 1
1855 AND Media.VolStatus IN ('Purged', 'Full', 'Append')
1856 AND Media.Recycle = 1
1858 ORDER BY Media.VolUseDuration DESC, Media.VolMounts ASC, expire ASC
1862 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1864 $self->display({ Media => [ values %$all ] },
1865 "help_intern_compute.tpl");
1871 my ($self, %arg) = @_ ;
1873 my ($limit, $label) = $self->get_limit(%arg);
1877 (SELECT count(Pool.PoolId) FROM Pool) AS nb_pool,
1878 (SELECT count(Media.MediaId) FROM Media) AS nb_media,
1879 (SELECT count(Job.JobId) FROM Job) AS nb_job,
1880 (SELECT sum(VolBytes) FROM Media) AS nb_bytes,
1881 ($self->{sql}->{DB_SIZE}) AS db_size,
1882 (SELECT count(Job.JobId)
1884 WHERE Job.JobStatus IN ('E','e','f','A')
1887 (SELECT count(Client.ClientId) FROM Client) AS nb_client
1890 my $row = $self->dbh_selectrow_hashref($query) ;
1892 $row->{nb_bytes} = human_size($row->{nb_bytes});
1894 $row->{db_size} = human_size($row->{db_size});
1895 $row->{label} = $label;
1897 $self->display($row, "general.tpl");
1902 my ($self, @what) = @_ ;
1903 my %elt = map { $_ => 1 } @what;
1908 if ($elt{clients}) {
1909 my @clients = grep { ! /^\s*$/ } CGI::param('client');
1911 $ret{clients} = \@clients;
1912 my $str = $self->dbh_join(@clients);
1913 $limit .= "AND Client.Name IN ($str) ";
1917 if ($elt{client_groups}) {
1918 my @clients = grep { ! /^\s*$/ } CGI::param('client_group');
1920 $ret{client_groups} = \@clients;
1921 my $str = $self->dbh_join(@clients);
1922 $limit .= "AND client_group_name IN ($str) ";
1926 if ($elt{filesets}) {
1927 my @filesets = grep { ! /^\s*$/ } CGI::param('fileset');
1929 $ret{filesets} = \@filesets;
1930 my $str = $self->dbh_join(@filesets);
1931 $limit .= "AND FileSet.FileSet IN ($str) ";
1935 if ($elt{mediatypes}) {
1936 my @media = grep { ! /^\s*$/ } CGI::param('mediatype');
1938 $ret{mediatypes} = \@media;
1939 my $str = $self->dbh_join(@media);
1940 $limit .= "AND Media.MediaType IN ($str) ";
1945 my $client = CGI::param('client');
1946 $ret{client} = $client;
1947 $client = $self->dbh_join($client);
1948 $limit .= "AND Client.Name = $client ";
1952 my $level = CGI::param('level') || '';
1953 if ($level =~ /^(\w)$/) {
1955 $limit .= "AND Job.Level = '$1' ";
1960 my $jobid = CGI::param('jobid') || '';
1962 if ($jobid =~ /^(\d+)$/) {
1964 $limit .= "AND Job.JobId = '$1' ";
1969 my $status = CGI::param('status') || '';
1970 if ($status =~ /^(\w)$/) {
1973 $limit .= "AND Job.JobStatus IN ('f','E') ";
1974 } elsif ($1 eq 'W') {
1975 $limit .= "AND Job.JobStatus = 'T' AND Job.JobErrors > 0 ";
1977 $limit .= "AND Job.JobStatus = '$1' ";
1982 if ($elt{volstatus}) {
1983 my $status = CGI::param('volstatus') || '';
1984 if ($status =~ /^(\w+)$/) {
1986 $limit .= "AND Media.VolStatus = '$1' ";
1990 if ($elt{locations}) {
1991 my @location = grep { ! /^\s*$/ } CGI::param('location') ;
1993 $ret{locations} = \@location;
1994 my $str = $self->dbh_join(@location);
1995 $limit .= "AND Location.Location IN ($str) ";
2000 my @pool = grep { ! /^\s*$/ } CGI::param('pool') ;
2002 $ret{pools} = \@pool;
2003 my $str = $self->dbh_join(@pool);
2004 $limit .= "AND Pool.Name IN ($str) ";
2008 if ($elt{location}) {
2009 my $location = CGI::param('location') || '';
2011 $ret{location} = $location;
2012 $location = $self->dbh_quote($location);
2013 $limit .= "AND Location.Location = $location ";
2018 my $pool = CGI::param('pool') || '';
2021 $pool = $self->dbh_quote($pool);
2022 $limit .= "AND Pool.Name = $pool ";
2026 if ($elt{jobtype}) {
2027 my $jobtype = CGI::param('jobtype') || '';
2028 if ($jobtype =~ /^(\w)$/) {
2030 $limit .= "AND Job.Type = '$1' ";
2034 return ($limit, %ret);
2045 my ($self, %arg) = @_ ;
2046 return if $self->cant_do('r_view_job');
2048 $arg{order} = ' Job.JobId DESC ';
2050 my ($limit, $label) = $self->get_limit(%arg);
2051 my ($where, undef) = $self->get_param('clients',
2060 if (CGI::param('client_group')) {
2062 JOIN client_group_member USING (ClientId)
2063 JOIN client_group USING (client_group_id)
2066 my $filter = $self->get_client_filter();
2069 SELECT Job.JobId AS jobid,
2070 Client.Name AS client,
2071 FileSet.FileSet AS fileset,
2072 Job.Name AS jobname,
2074 StartTime AS starttime,
2076 Pool.Name AS poolname,
2077 JobFiles AS jobfiles,
2078 JobBytes AS jobbytes,
2079 JobStatus AS jobstatus,
2080 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
2081 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime))
2084 JobErrors AS joberrors
2086 FROM Client $filter $cgq,
2087 Job LEFT JOIN Pool ON (Job.PoolId = Pool.PoolId)
2088 LEFT JOIN FileSet ON (Job.FileSetId = FileSet.FileSetId)
2089 WHERE Client.ClientId=Job.ClientId
2090 AND Job.JobStatus NOT IN ('R', 'C')
2095 my $all = $self->dbh_selectall_hashref($query, 'jobid') ;
2097 $self->display({ Filter => $label,
2101 sort { $a->{jobid} <=> $b->{jobid} }
2108 # display job informations
2109 sub display_job_zoom
2111 my ($self, $jobid) = @_ ;
2112 $self->can_do('r_view_job');
2114 $jobid = $self->dbh_quote($jobid);
2116 # get security filter
2117 my $filter = $self->get_client_filter();
2120 SELECT DISTINCT Job.JobId AS jobid,
2121 Client.Name AS client,
2122 Job.Name AS jobname,
2123 FileSet.FileSet AS fileset,
2125 Pool.Name AS poolname,
2126 StartTime AS starttime,
2127 JobFiles AS jobfiles,
2128 JobBytes AS jobbytes,
2129 JobStatus AS jobstatus,
2130 JobErrors AS joberrors,
2131 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
2132 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime)) AS duration
2134 FROM Client $filter,
2135 Job LEFT JOIN FileSet ON (Job.FileSetId = FileSet.FileSetId)
2136 LEFT JOIN Pool ON (Job.PoolId = Pool.PoolId)
2137 WHERE Client.ClientId=Job.ClientId
2138 AND Job.JobId = $jobid
2141 my $row = $self->dbh_selectrow_hashref($query) ;
2143 # display all volumes associate with this job
2145 SELECT Media.VolumeName as volumename
2146 FROM Job,Media,JobMedia
2147 WHERE Job.JobId = $jobid
2148 AND JobMedia.JobId=Job.JobId
2149 AND JobMedia.MediaId=Media.MediaId
2152 my $all = $self->dbh_selectall_hashref($query, 'volumename');
2154 $row->{volumes} = [ values %$all ] ;
2156 $self->display($row, "display_job_zoom.tpl");
2159 sub display_job_group
2161 my ($self, %arg) = @_;
2162 $self->can_do('r_view_job');
2164 my ($limit, $label) = $self->get_limit(groupby => 'client_group_name', %arg);
2166 my ($where, undef) = $self->get_param('client_groups',
2169 my $filter = $self->get_client_group_filter();
2172 SELECT client_group_name AS client_group_name,
2173 COALESCE(jobok.jobfiles,0) + COALESCE(joberr.jobfiles,0) AS jobfiles,
2174 COALESCE(jobok.jobbytes,0) + COALESCE(joberr.jobbytes,0) AS jobbytes,
2175 COALESCE(jobok.joberrors,0) + COALESCE(joberr.joberrors,0) AS joberrors,
2176 COALESCE(jobok.nbjobs,0) AS nbjobok,
2177 COALESCE(joberr.nbjobs,0) AS nbjoberr,
2178 COALESCE(jobok.duration, '0:0:0') AS duration
2180 FROM client_group $filter LEFT JOIN (
2181 SELECT client_group_name AS client_group_name, COUNT(1) AS nbjobs,
2182 SUM(JobFiles) AS jobfiles, SUM(JobBytes) AS jobbytes,
2183 SUM(JobErrors) AS joberrors,
2184 SUM($self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
2185 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime)))
2188 FROM Job JOIN client_group_member ON (Job.ClientId = client_group_member.ClientId)
2189 JOIN client_group USING (client_group_id)
2191 WHERE JobStatus = 'T'
2194 ) AS jobok USING (client_group_name) LEFT JOIN
2197 SELECT client_group_name AS client_group_name, COUNT(1) AS nbjobs,
2198 SUM(JobFiles) AS jobfiles, SUM(JobBytes) AS jobbytes,
2199 SUM(JobErrors) AS joberrors
2200 FROM Job JOIN client_group_member ON (Job.ClientId = client_group_member.ClientId)
2201 JOIN client_group USING (client_group_id)
2203 WHERE JobStatus IN ('f','E', 'A')
2206 ) AS joberr USING (client_group_name)
2210 my $all = $self->dbh_selectall_hashref($query, 'client_group_name');
2212 my $rep = { groups => [ values %$all ], age => $arg{age}, filter => $label };
2215 $self->display($rep, "display_job_group.tpl");
2220 my ($self, %arg) = @_ ;
2222 my ($limit, $label) = $self->get_limit(%arg);
2223 my ($where, %elt) = $self->get_param('pools',
2228 my $arg = $self->get_form('jmedias', 'qre_media', 'expired');
2230 if ($arg->{jmedias}) {
2231 $where = "AND Media.VolumeName IN ($arg->{jmedias}) $where";
2233 if ($arg->{qre_media}) {
2234 $where = "AND Media.VolumeName $self->{sql}->{MATCH} $arg->{qre_media} $where";
2236 if ($arg->{expired}) {
2238 AND VolStatus = 'Full'
2239 AND ( $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2240 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2241 ) < NOW() " . $where ;
2245 SELECT Media.VolumeName AS volumename,
2246 Media.VolBytes AS volbytes,
2247 Media.VolStatus AS volstatus,
2248 Media.MediaType AS mediatype,
2249 Media.InChanger AS online,
2250 Media.LastWritten AS lastwritten,
2251 Location.Location AS location,
2252 (volbytes*100/COALESCE(media_avg_size.size,-1)) AS volusage,
2253 Pool.Name AS poolname,
2254 $self->{sql}->{FROM_UNIXTIME}(
2255 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2256 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2259 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2260 LEFT JOIN (SELECT avg(Media.VolBytes) AS size,
2261 Media.MediaType AS MediaType
2263 WHERE Media.VolStatus = 'Full'
2264 GROUP BY Media.MediaType
2265 ) AS media_avg_size ON (Media.MediaType = media_avg_size.MediaType)
2267 WHERE Media.PoolId=Pool.PoolId
2272 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
2274 $self->display({ ID => $cur_id++,
2276 Location => $elt{location},
2277 Media => [ values %$all ],
2279 "display_media.tpl");
2282 sub display_allmedia
2286 my $pool = $self->get_form('db_pools');
2288 foreach my $name (@{ $pool->{db_pools} }) {
2289 CGI::param('pool', $name->{name});
2290 $self->display_media();
2294 sub display_media_zoom
2298 my $media = $self->get_form('jmedias');
2300 unless ($media->{jmedias}) {
2301 return $self->error("Can't get media selection");
2305 SELECT InChanger AS online,
2306 Media.Enabled AS enabled,
2307 VolBytes AS nb_bytes,
2308 VolumeName AS volumename,
2309 VolStatus AS volstatus,
2310 VolMounts AS nb_mounts,
2311 Media.VolUseDuration AS voluseduration,
2312 Media.MaxVolJobs AS maxvoljobs,
2313 Media.MaxVolFiles AS maxvolfiles,
2314 Media.MaxVolBytes AS maxvolbytes,
2315 VolErrors AS nb_errors,
2316 Pool.Name AS poolname,
2317 Location.Location AS location,
2318 Media.Recycle AS recycle,
2319 Media.VolRetention AS volretention,
2320 Media.LastWritten AS lastwritten,
2321 Media.VolReadTime/1000000 AS volreadtime,
2322 Media.VolWriteTime/1000000 AS volwritetime,
2323 Media.RecycleCount AS recyclecount,
2324 Media.Comment AS comment,
2325 $self->{sql}->{FROM_UNIXTIME}(
2326 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2327 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2330 Media LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2331 WHERE Pool.PoolId = Media.PoolId
2332 AND VolumeName IN ($media->{jmedias})
2335 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
2337 foreach my $media (values %$all) {
2338 my $mq = $self->dbh_quote($media->{volumename});
2341 SELECT DISTINCT Job.JobId AS jobid,
2343 Job.StartTime AS starttime,
2346 Job.JobFiles AS files,
2347 Job.JobBytes AS bytes,
2348 Job.jobstatus AS status
2349 FROM Media,JobMedia,Job
2350 WHERE Media.VolumeName=$mq
2351 AND Media.MediaId=JobMedia.MediaId
2352 AND JobMedia.JobId=Job.JobId
2355 my $jobs = $self->dbh_selectall_hashref($query, 'jobid') ;
2358 SELECT LocationLog.Date AS date,
2359 Location.Location AS location,
2360 LocationLog.Comment AS comment
2361 FROM Media,LocationLog INNER JOIN Location ON (LocationLog.LocationId = Location.LocationId)
2362 WHERE Media.MediaId = LocationLog.MediaId
2363 AND Media.VolumeName = $mq
2367 my $log = $self->dbh_selectall_arrayref($query) ;
2369 $logtxt = join("\n", map { ($_->[0] . ' ' . $_->[1] . ' ' . $_->[2])} @$log ) ;
2372 $self->display({ jobs => [ values %$jobs ],
2373 LocationLog => $logtxt,
2375 "display_media_zoom.tpl");
2382 $self->can_do('r_location_mgnt');
2384 my $loc = $self->get_form('qlocation');
2385 unless ($loc->{qlocation}) {
2386 return $self->error("Can't get location");
2390 SELECT Location.Location AS location,
2391 Location.Cost AS cost,
2392 Location.Enabled AS enabled
2394 WHERE Location.Location = $loc->{qlocation}
2397 my $row = $self->dbh_selectrow_hashref($query);
2398 $row->{enabled} = human_enabled($row->{enabled});
2399 $self->display({ ID => $cur_id++,
2400 %$row }, "location_edit.tpl") ;
2406 $self->can_do('r_location_mgnt');
2408 my $arg = $self->get_form(qw/qlocation qnewlocation cost enabled/) ;
2409 unless ($arg->{qlocation}) {
2410 return $self->error("Can't get location");
2412 unless ($arg->{qnewlocation}) {
2413 return $self->error("Can't get new location name");
2415 unless ($arg->{cost}) {
2416 return $self->error("Can't get new cost");
2419 my $enabled = from_human_enabled($arg->{enabled});
2422 UPDATE Location SET Cost = $arg->{cost},
2423 Location = $arg->{qnewlocation},
2425 WHERE Location.Location = $arg->{qlocation}
2428 $self->dbh_do($query);
2430 $self->location_display();
2436 $self->can_do('r_location_mgnt');
2438 my $arg = $self->get_form(qw/qlocation/) ;
2440 unless ($arg->{qlocation}) {
2441 return $self->error("Can't get location");
2445 SELECT count(Media.MediaId) AS nb
2446 FROM Media INNER JOIN Location USING (LocationID)
2447 WHERE Location = $arg->{qlocation}
2450 my $res = $self->dbh_selectrow_hashref($query);
2453 return $self->error("Sorry, the location must be empty");
2457 DELETE FROM Location WHERE Location = $arg->{qlocation}
2460 $self->dbh_do($query);
2462 $self->location_display();
2468 $self->can_do('r_location_mgnt');
2470 my $arg = $self->get_form(qw/qlocation cost/) ;
2472 unless ($arg->{qlocation}) {
2473 $self->display({}, "location_add.tpl");
2476 unless ($arg->{cost}) {
2477 return $self->error("Can't get new cost");
2480 my $enabled = CGI::param('enabled') || '';
2481 $enabled = $enabled?1:0;
2484 INSERT INTO Location (Location, Cost, Enabled)
2485 VALUES ($arg->{qlocation}, $arg->{cost}, $enabled)
2488 $self->dbh_do($query);
2490 $self->location_display();
2493 sub location_display
2498 SELECT Location.Location AS location,
2499 Location.Cost AS cost,
2500 Location.Enabled AS enabled,
2501 (SELECT count(Media.MediaId)
2503 WHERE Media.LocationId = Location.LocationId
2508 my $location = $self->dbh_selectall_hashref($query, 'location');
2510 $self->display({ ID => $cur_id++,
2511 Locations => [ values %$location ] },
2512 "display_location.tpl");
2519 my $media = $self->get_selected_media_location();
2524 my $arg = $self->get_form('db_locations', 'qnewlocation');
2526 $self->display({ email => $self->{info}->{email_media},
2528 media => [ values %$media ],
2530 "update_location.tpl");
2533 ###########################################################
2538 $self->can_do('r_group_mgnt');
2540 my $grp = $self->get_form(qw/qclient_group db_clients/);
2542 unless ($grp->{qclient_group}) {
2543 $self->display({ ID => $cur_id++,
2544 client_group => "''",
2546 }, "groups_edit.tpl");
2552 FROM Client JOIN client_group_member using (clientid)
2553 JOIN client_group using (client_group_id)
2554 WHERE client_group_name = $grp->{qclient_group}
2557 my $row = $self->dbh_selectall_hashref($query, "name");
2559 $self->display({ ID => $cur_id++,
2560 client_group => $grp->{qclient_group},
2562 client_group_member => [ values %$row]},
2569 $self->can_do('r_group_mgnt');
2571 my $arg = $self->get_form(qw/qclient_group jclients qnewgroup/);
2573 if (!$arg->{qclient_group} and $arg->{qnewgroup}) {
2575 INSERT INTO client_group (client_group_name)
2576 VALUES ($arg->{qnewgroup})
2578 $self->dbh_do($query);
2579 $arg->{qclient_group} = $arg->{qnewgroup};
2582 unless ($arg->{qclient_group}) {
2583 return $self->error("Can't get groups");
2586 $self->{dbh}->begin_work();
2589 DELETE FROM client_group_member
2590 WHERE client_group_id IN
2591 (SELECT client_group_id
2593 WHERE client_group_name = $arg->{qclient_group})
2595 $self->dbh_do($query);
2598 INSERT INTO client_group_member (clientid, client_group_id)
2600 (SELECT client_group_id
2602 WHERE client_group_name = $arg->{qclient_group})
2603 FROM Client WHERE Name IN ($arg->{jclients})
2606 $self->dbh_do($query);
2608 if ($arg->{qclient_group} ne $arg->{qnewgroup}) {
2611 SET client_group_name = $arg->{qnewgroup}
2612 WHERE client_group_name = $arg->{qclient_group}
2615 $self->dbh_do($query);
2618 $self->{dbh}->commit() or $self->error($self->{dbh}->errstr);
2620 $self->display_groups();
2626 $self->can_do('r_group_mgnt');
2628 my $arg = $self->get_form(qw/qclient_group/);
2630 unless ($arg->{qclient_group}) {
2631 return $self->error("Can't get groups");
2634 $self->{dbh}->begin_work();
2637 DELETE FROM client_group_member
2638 WHERE client_group_id IN
2639 (SELECT client_group_id
2641 WHERE client_group_name = $arg->{qclient_group});
2643 DELETE FROM bweb_client_group_acl
2644 WHERE client_group_id IN
2645 (SELECT client_group_id
2647 WHERE client_group_name = $arg->{qclient_group});
2649 DELETE FROM client_group
2650 WHERE client_group_name = $arg->{qclient_group};
2652 $self->dbh_do($query);
2654 $self->{dbh}->commit();
2656 $self->display_groups();
2663 my $arg = $self->get_form(qw/db_client_groups/) ;
2665 if ($self->{dbh}->errstr) {
2666 return $self->error("Can't use groups with bweb, read INSTALL to enable them");
2671 $self->display({ ID => $cur_id++,
2673 "display_groups.tpl");
2676 ###########################################################
2681 if (not $self->{info}->{enable_security}) {
2684 # admin is a special user that can do everything
2685 if ($self->{loginname} eq 'admin') {
2688 if (!$self->{loginname}) {
2692 if (defined $self->{security}) {
2695 $self->{security} = {};
2696 my $u = $self->dbh_quote($self->{loginname});
2699 SELECT use_acl, rolename
2701 JOIN bweb_role_member USING (userid)
2702 JOIN bweb_role USING (roleid)
2705 my $rows = $self->dbh_selectall_arrayref($query);
2706 # do cache with this role
2710 foreach my $r (@$rows) {
2711 $self->{security}->{$r->[1]}=1;
2714 $self->{security}->{use_acl} = $rows->[0]->[0];
2720 my ($self, $action) = @_;
2721 # is security enabled in configuration ?
2722 if (not $self->{info}->{enable_security}) {
2725 # admin is a special user that can do everything
2726 if ($self->{loginname} eq 'admin') {
2730 if (!$self->{loginname}) {
2731 $self->{error} = "Can't do $action, your are not logged. " .
2732 "Check security with your administrator";
2736 if (!$self->{security}->{$action}) {
2738 "$self->{loginname} sorry, but this action ($action) " .
2739 "is not permited. " .
2740 "Check security with your administrator";
2746 # make like an assert (program die)
2749 my ($self, $action) = @_;
2750 if ($self->cant_do($action)) {
2751 $self->error($self->{error});
2752 $self->display_end();
2762 if (!$self->{info}->{enable_security} or
2763 !$self->{info}->{enable_security_acl})
2768 if ($self->get_roles()) {
2769 return $self->{security}->{use_acl};
2775 # JOIN Client USING (ClientId) " . $b->get_client_filter() . "
2776 sub get_client_filter
2778 my ($self, $login) = @_;
2781 $u = $self->dbh_quote($login);
2782 } elsif ($self->use_filter()) {
2783 $u = $self->dbh_quote($self->{loginname});
2788 JOIN (SELECT ClientId FROM client_group_member
2789 JOIN client_group USING (client_group_id)
2790 JOIN bweb_client_group_acl USING (client_group_id)
2791 JOIN bweb_user USING (userid)
2792 WHERE bweb_user.username = $u
2793 ) AS filter USING (ClientId)";
2796 #JOIN client_group USING (client_group_id)" . $b->get_client_group_filter()
2797 sub get_client_group_filter
2799 my ($self, $login) = @_;
2802 $u = $self->dbh_quote($login);
2803 } elsif ($self->use_filter()) {
2804 $u = $self->dbh_quote($self->{loginname});
2809 JOIN (SELECT client_group_id
2810 FROM bweb_client_group_acl
2811 JOIN bweb_user USING (userid)
2812 WHERE bweb_user.username = $u
2813 ) AS filter USING (client_group_id)";
2816 # role and username have to be quoted before
2817 # role and username can be a quoted list
2820 my ($self, $role, $username) = @_;
2821 $self->can_do("r_user_mgnt");
2823 my $nb = $self->dbh_do("
2824 DELETE FROM bweb_role_member
2825 WHERE roleid = (SELECT roleid FROM bweb_role
2826 WHERE rolename IN ($role))
2827 AND userid = (SELECT userid FROM bweb_user
2828 WHERE username IN ($username))");
2832 # role and username have to be quoted before
2833 # role and username can be a quoted list
2836 my ($self, $role, $username) = @_;
2837 $self->can_do("r_user_mgnt");
2839 my $nb = $self->dbh_do("
2840 INSERT INTO bweb_role_member (roleid, userid)
2841 SELECT roleid, userid FROM bweb_role, bweb_user
2842 WHERE rolename IN ($role)
2843 AND username IN ($username)
2848 # role and username have to be quoted before
2849 # role and username can be a quoted list
2852 my ($self, $copy, $user) = @_;
2853 $self->can_do("r_user_mgnt");
2855 my $nb = $self->dbh_do("
2856 INSERT INTO bweb_role_member (roleid, userid)
2857 SELECT roleid, a.userid
2858 FROM bweb_user AS a, bweb_role_member
2859 JOIN bweb_user USING (userid)
2860 WHERE bweb_user.username = $copy
2861 AND a.username = $user");
2865 # username can be a join quoted list of usernames
2868 my ($self, $username) = @_;
2869 $self->can_do("r_user_mgnt");
2872 DELETE FROM bweb_role_member
2876 WHERE username in ($username))");
2878 DELETE FROM bweb_client_group_acl
2882 WHERE username IN ($username))");
2889 $self->can_do("r_user_mgnt");
2891 my $arg = $self->get_form(qw/jusernames/);
2893 unless ($arg->{jusernames}) {
2894 return $self->error("Can't get user");
2897 $self->{dbh}->begin_work();
2899 $self->revoke_all($arg->{jusernames});
2901 DELETE FROM bweb_user WHERE username IN ($arg->{jusernames})");
2903 $self->{dbh}->commit();
2905 $self->display_users();
2911 $self->can_do("r_user_mgnt");
2913 # we don't quote username directly to check that it is conform
2914 my $arg = $self->get_form(qw/username qpasswd qcomment jrolenames qcreate qcopy_username jclient_groups/) ;
2916 if (not $arg->{qcreate}) {
2917 $arg = $self->get_form(qw/db_roles db_usernames db_client_groups/);
2918 $self->display($arg, "display_user.tpl");
2922 my $u = $self->dbh_quote($arg->{username});
2924 $arg->{use_acl}=(CGI::param('use_acl')?'true':'false');
2926 if (!$arg->{qpasswd}) {
2927 $arg->{qpasswd} = "''";
2929 if (!$arg->{qcomment}) {
2930 $arg->{qcomment} = "''";
2933 # will fail if user already exists
2934 # UPDATE with mysql dbi does not return if update is ok
2937 SET passwd=$arg->{qpasswd}, comment=$arg->{qcomment},
2938 use_acl=$arg->{use_acl}
2939 WHERE username = $u")
2940 # and (! $self->dbh_is_mysql() )
2943 INSERT INTO bweb_user (username, passwd, use_acl, comment)
2944 VALUES ($u, $arg->{qpasswd}, $arg->{use_acl}, $arg->{qcomment})");
2946 $self->{dbh}->begin_work();
2948 $self->revoke_all($u);
2950 if ($arg->{qcopy_username}) {
2951 $self->grant_like($arg->{qcopy_username}, $u);
2953 $self->grant($arg->{jrolenames}, $u);
2956 if ($arg->{jclient_groups}) {
2958 INSERT INTO bweb_client_group_acl (client_group_id, userid)
2959 SELECT client_group_id, userid
2960 FROM client_group, bweb_user
2961 WHERE client_group_name IN ($arg->{jclient_groups})
2966 $self->{dbh}->commit();
2968 $self->display_users();
2971 # TODO: we miss a matrix with all user/roles
2975 $self->can_do("r_user_mgnt");
2977 my $arg = $self->get_form(qw/db_usernames/) ;
2979 if ($self->{dbh}->errstr) {
2980 return $self->error("Can't use users with bweb, read INSTALL to enable them");
2983 $self->display({ ID => $cur_id++,
2985 "display_users.tpl");
2991 $self->can_do("r_user_mgnt");
2993 my $arg = $self->get_form('username');
2994 my $user = $self->dbh_quote($arg->{username});
2996 my $userp = $self->dbh_selectrow_hashref("
2997 SELECT username, passwd, comment, use_acl
2999 WHERE username = $user
3002 return $self->error("Can't find $user in catalog");
3004 my $filter = $self->get_client_group_filter($arg->{username});
3005 my $scg = $self->dbh_selectall_hashref("
3006 SELECT client_group_name AS name
3007 FROM client_group $filter
3011 #------------+--------
3016 my $role = $self->dbh_selectall_hashref("
3017 SELECT rolename, temp.userid
3019 LEFT JOIN (SELECT roleid, userid
3020 FROM bweb_user JOIN bweb_role_member USING (userid)
3021 WHERE username = $user) AS temp USING (roleid)
3025 $arg = $self->get_form(qw/db_usernames db_client_groups/);
3028 db_usernames => $arg->{db_usernames},
3029 username => $userp->{username},
3030 comment => $userp->{comment},
3031 passwd => $userp->{passwd},
3032 use_acl => $userp->{use_acl},
3033 db_client_groups => $arg->{db_client_groups},
3034 client_group => [ values %$scg ],
3035 db_roles => [ values %$role],
3036 }, "display_user.tpl");
3040 ###########################################################
3042 sub get_media_max_size
3044 my ($self, $type) = @_;
3046 "SELECT avg(VolBytes) AS size
3048 WHERE Media.VolStatus = 'Full'
3049 AND Media.MediaType = '$type'
3052 my $res = $self->selectrow_hashref($query);
3055 return $res->{size};
3065 my $media = $self->get_form('qmedia');
3067 unless ($media->{qmedia}) {
3068 return $self->error("Can't get media");
3072 SELECT Media.Slot AS slot,
3073 PoolMedia.Name AS poolname,
3074 Media.VolStatus AS volstatus,
3075 Media.InChanger AS inchanger,
3076 Location.Location AS location,
3077 Media.VolumeName AS volumename,
3078 Media.MaxVolBytes AS maxvolbytes,
3079 Media.MaxVolJobs AS maxvoljobs,
3080 Media.MaxVolFiles AS maxvolfiles,
3081 Media.VolUseDuration AS voluseduration,
3082 Media.VolRetention AS volretention,
3083 Media.Comment AS comment,
3084 PoolRecycle.Name AS poolrecycle,
3085 Media.Enabled AS enabled
3087 FROM Media INNER JOIN Pool AS PoolMedia ON (Media.PoolId = PoolMedia.PoolId)
3088 LEFT JOIN Pool AS PoolRecycle ON (Media.RecyclePoolId = PoolRecycle.PoolId)
3089 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
3091 WHERE Media.VolumeName = $media->{qmedia}
3094 my $row = $self->dbh_selectrow_hashref($query);
3095 $row->{volretention} = human_sec($row->{volretention});
3096 $row->{voluseduration} = human_sec($row->{voluseduration});
3097 $row->{enabled} = human_enabled($row->{enabled});
3099 my $elt = $self->get_form(qw/db_pools db_locations/);
3104 }, "update_media.tpl");
3110 $self->can_do('r_media_mgnt');
3112 my $arg = $self->get_form('jmedias', 'qnewlocation') ;
3114 unless ($arg->{jmedias}) {
3115 return $self->error("Can't get selected media");
3118 unless ($arg->{qnewlocation}) {
3119 return $self->error("Can't get new location");
3124 SET LocationId = (SELECT LocationId
3126 WHERE Location = $arg->{qnewlocation})
3127 WHERE Media.VolumeName IN ($arg->{jmedias})
3130 my $nb = $self->dbh_do($query);
3132 print "$nb media updated, you may have to update your autochanger.";
3134 $self->display_media();
3140 $self->can_do('r_media_mgnt');
3142 my $media = $self->get_selected_media_location();
3144 return $self->error("Can't get media selection");
3146 my $newloc = CGI::param('newlocation');
3148 my $user = CGI::param('user') || 'unknown';
3149 my $comm = CGI::param('comment') || '';
3150 $comm = $self->dbh_quote("$user: $comm");
3152 my $arg = $self->get_form('enabled');
3153 my $en = human_enabled($arg->{enabled});
3154 my $b = $self->get_bconsole();
3157 foreach my $vol (keys %$media) {
3159 INSERT LocationLog (Date, Comment, MediaId, LocationId, NewVolStatus)
3161 NOW(), $comm, (SELECT MediaId FROM Media WHERE VolumeName = '$vol'),
3162 (SELECT LocationId FROM Location WHERE Location = '$media->{$vol}->{location}'),
3163 (SELECT VolStatus FROM Media WHERE VolumeName = '$vol')
3166 $self->dbh_do($query);
3167 $self->debug($query);
3168 $b->send_cmd("update volume=\"$vol\" enabled=$en");
3173 $q->param('action', 'update_location');
3174 my $url = $q->url(-full => 1, -query=>1);
3176 $self->display({ email => $self->{info}->{email_media},
3178 newlocation => $newloc,
3179 # [ { volumename => 'vol1' }, { volumename => 'vol2'},..]
3180 media => [ values %$media ],
3182 "change_location.tpl");
3186 sub display_client_stats
3188 my ($self, %arg) = @_ ;
3189 $self->can_do('r_view_stat');
3191 my $client = $self->dbh_quote($arg{clientname});
3192 # get security filter
3193 my $filter = $self->get_client_filter();
3195 my ($limit, $label) = $self->get_limit(%arg);
3198 count(Job.JobId) AS nb_jobs,
3199 sum(Job.JobBytes) AS nb_bytes,
3200 sum(Job.JobErrors) AS nb_err,
3201 sum(Job.JobFiles) AS nb_files,
3202 Client.Name AS clientname
3203 FROM Job JOIN Client USING (ClientId) $filter
3205 Client.Name = $client
3207 GROUP BY Client.Name
3210 my $row = $self->dbh_selectrow_hashref($query);
3212 $row->{ID} = $cur_id++;
3213 $row->{label} = $label;
3214 $row->{grapharg} = "client";
3216 $self->display($row, "display_client_stats.tpl");
3220 sub display_group_stats
3222 my ($self, %arg) = @_ ;
3224 my $carg = $self->get_form(qw/qclient_group/);
3226 unless ($carg->{qclient_group}) {
3227 return $self->error("Can't get group");
3230 my ($limit, $label) = $self->get_limit(%arg);
3234 count(Job.JobId) AS nb_jobs,
3235 sum(Job.JobBytes) AS nb_bytes,
3236 sum(Job.JobErrors) AS nb_err,
3237 sum(Job.JobFiles) AS nb_files,
3238 client_group.client_group_name AS clientname
3239 FROM Job JOIN Client USING (ClientId)
3240 JOIN client_group_member ON (Client.ClientId = client_group_member.clientid)
3241 JOIN client_group USING (client_group_id)
3243 client_group.client_group_name = $carg->{qclient_group}
3245 GROUP BY client_group.client_group_name
3248 my $row = $self->dbh_selectrow_hashref($query);
3250 $row->{ID} = $cur_id++;
3251 $row->{label} = $label;
3252 $row->{grapharg} = "client_group";
3254 $self->display($row, "display_client_stats.tpl");
3257 # poolname can be undef
3260 my ($self, $poolname) = @_ ;
3264 my $arg = $self->get_form('jmediatypes', 'qmediatypes');
3265 if ($arg->{jmediatypes}) {
3266 $whereW = "WHERE MediaType IN ($arg->{jmediatypes}) ";
3267 $whereA = "AND MediaType IN ($arg->{jmediatypes}) ";
3270 # TODO : afficher les tailles et les dates
3273 SELECT subq.volmax AS volmax,
3274 subq.volnum AS volnum,
3275 subq.voltotal AS voltotal,
3277 Pool.Recycle AS recycle,
3278 Pool.VolRetention AS volretention,
3279 Pool.VolUseDuration AS voluseduration,
3280 Pool.MaxVolJobs AS maxvoljobs,
3281 Pool.MaxVolFiles AS maxvolfiles,
3282 Pool.MaxVolBytes AS maxvolbytes,
3283 subq.PoolId AS PoolId,
3284 subq.MediaType AS mediatype,
3285 $self->{sql}->{CAT_POOL_TYPE} AS uniq
3288 SELECT COALESCE(media_avg_size.volavg,0) * count(Media.MediaId) AS volmax,
3289 count(Media.MediaId) AS volnum,
3290 sum(Media.VolBytes) AS voltotal,
3291 Media.PoolId AS PoolId,
3292 Media.MediaType AS MediaType
3294 LEFT JOIN (SELECT avg(Media.VolBytes) AS volavg,
3295 Media.MediaType AS MediaType
3297 WHERE Media.VolStatus = 'Full'
3298 GROUP BY Media.MediaType
3299 ) AS media_avg_size ON (Media.MediaType = media_avg_size.MediaType)
3300 GROUP BY Media.MediaType, Media.PoolId, media_avg_size.volavg
3302 LEFT JOIN Pool ON (Pool.PoolId = subq.PoolId)
3306 my $all = $self->dbh_selectall_hashref($query, 'uniq') ;
3309 SELECT Pool.Name AS name,
3310 sum(VolBytes) AS size
3311 FROM Media JOIN Pool ON (Media.PoolId = Pool.PoolId)
3312 WHERE Media.VolStatus IN ('Recycled', 'Purged')
3316 my $empty = $self->dbh_selectall_hashref($query, 'name');
3318 foreach my $p (values %$all) {
3319 if ($p->{volmax} > 0) { # mysql returns 0.0000
3320 # we remove Recycled/Purged media from pool usage
3321 if (defined $empty->{$p->{name}}) {
3322 $p->{voltotal} -= $empty->{$p->{name}}->{size};
3324 $p->{poolusage} = sprintf('%.2f', $p->{voltotal} * 100/ $p->{volmax}) ;
3326 $p->{poolusage} = 0;
3330 SELECT VolStatus AS volstatus, count(MediaId) AS nb
3332 WHERE PoolId=$p->{poolid}
3333 AND Media.MediaType = '$p->{mediatype}'
3337 my $content = $self->dbh_selectall_hashref($query, 'volstatus');
3338 foreach my $t (values %$content) {
3339 $p->{"nb_" . $t->{volstatus}} = $t->{nb} ;
3344 $self->display({ ID => $cur_id++,
3345 MediaType => $arg->{qmediatypes}, # [ { name => type1 } , { name => type2 } ]
3346 Pools => [ values %$all ]},
3347 "display_pool.tpl");
3350 sub display_running_job
3353 return if $self->cant_do('r_view_running_job');
3355 my $arg = $self->get_form('client', 'jobid');
3357 if (!$arg->{client} and $arg->{jobid}) {
3358 # get security filter
3359 my $filter = $self->get_client_filter();
3362 SELECT Client.Name AS name
3363 FROM Job INNER JOIN Client USING (ClientId) $filter
3364 WHERE Job.JobId = $arg->{jobid}
3367 my $row = $self->dbh_selectrow_hashref($query);
3370 $arg->{client} = $row->{name};
3371 CGI::param('client', $arg->{client});
3375 if ($arg->{client}) {
3376 my $cli = new Bweb::Client(name => $arg->{client});
3377 $cli->display_running_job($self->{info}, $arg->{jobid});
3378 if ($arg->{jobid}) {
3379 $self->get_job_log();
3382 $self->error("Can't get client or jobid");
3386 sub display_running_jobs
3388 my ($self, $display_action) = @_;
3389 return if $self->cant_do('r_view_running_job');
3391 # get security filter
3392 my $filter = $self->get_client_filter();
3395 SELECT Job.JobId AS jobid,
3396 Job.Name AS jobname,
3398 Job.StartTime AS starttime,
3399 Job.JobFiles AS jobfiles,
3400 Job.JobBytes AS jobbytes,
3401 Job.JobStatus AS jobstatus,
3402 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(NOW())
3403 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime))
3405 Client.Name AS clientname
3406 FROM Job INNER JOIN Client USING (ClientId) $filter
3408 JobStatus IN ('C','R','B','e','D','F','S','m','M','s','j','c','d','t','p')
3410 my $all = $self->dbh_selectall_hashref($query, 'jobid') ;
3412 $self->display({ ID => $cur_id++,
3413 display_action => $display_action,
3414 Jobs => [ values %$all ]},
3415 "running_job.tpl") ;
3418 # return the autochanger list to update
3422 $self->can_do('r_media_mgnt');
3425 my $arg = $self->get_form('jmedias');
3427 unless ($arg->{jmedias}) {
3428 return $self->error("Can't get media selection");
3432 SELECT Media.VolumeName AS volumename,
3433 Storage.Name AS storage,
3434 Location.Location AS location,
3436 FROM Media INNER JOIN Storage ON (Media.StorageId = Storage.StorageId)
3437 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
3438 WHERE Media.VolumeName IN ($arg->{jmedias})
3439 AND Media.InChanger = 1
3442 my $all = $self->dbh_selectall_hashref($query, 'volumename');
3444 foreach my $vol (values %$all) {
3445 my $a = $self->ach_get($vol->{location});
3447 $ret{$vol->{location}} = 1;
3449 unless ($a->{have_status}) {
3451 $a->{have_status} = 1;
3454 print "eject $vol->{volumename} from $vol->{storage} : ";
3455 if ($a->send_to_io($vol->{slot})) {
3456 print "<img src='/bweb/T.png' alt='ok'><br/>";
3458 print "<img src='/bweb/E.png' alt='err'><br/>";
3468 my ($to, $subject, $content) = (CGI::param('email'),
3469 CGI::param('subject'),
3470 CGI::param('content'));
3471 $to =~ s/[^\w\d\.\@<>,]//;
3472 $subject =~ s/[^\w\d\.\[\]]/ /;
3474 open(MAIL, "|mail -s '$subject' '$to'") ;
3475 print MAIL $content;
3485 my $arg = $self->get_form('jobid', 'client');
3487 print CGI::header('text/brestore');
3488 print "jobid=$arg->{jobid}\n" if ($arg->{jobid});
3489 print "client=$arg->{client}\n" if ($arg->{client});
3490 print "\n\nYou have to assign this mime type with /usr/bin/brestore.pl\n";
3494 # TODO : move this to Bweb::Autochanger ?
3495 # TODO : make this internal to not eject tape ?
3501 my ($self, $name) = @_;
3504 return $self->error("Can't get your autochanger name ach");
3507 unless ($self->{info}->{ach_list}) {
3508 return $self->error("Could not find any autochanger");
3511 my $a = $self->{info}->{ach_list}->{$name};
3514 $self->error("Can't get your autochanger $name from your ach_list");
3519 $a->{debug} = $self->{debug};
3526 my ($self, $ach) = @_;
3527 $self->can_do('r_configure');
3529 $self->{info}->{ach_list}->{$ach->{name}} = $ach;
3531 $self->{info}->save();
3539 $self->can_do('r_configure');
3541 my $arg = $self->get_form('ach');
3543 or !$self->{info}->{ach_list}
3544 or !$self->{info}->{ach_list}->{$arg->{ach}})
3546 return $self->error("Can't get autochanger name");
3549 my $ach = $self->{info}->{ach_list}->{$arg->{ach}};
3553 [ map { { name => $_, index => $i++ } } @{$ach->{drive_name}} ] ;
3555 my $b = $self->get_bconsole();
3557 my @storages = $b->list_storage() ;
3559 $ach->{devices} = [ map { { name => $_ } } @storages ];
3561 $self->display($ach, "ach_add.tpl");
3562 delete $ach->{drives};
3563 delete $ach->{devices};
3570 $self->can_do('r_configure');
3572 my $arg = $self->get_form('ach');
3575 or !$self->{info}->{ach_list}
3576 or !$self->{info}->{ach_list}->{$arg->{ach}})
3578 return $self->error("Can't get autochanger name");
3581 delete $self->{info}->{ach_list}->{$arg->{ach}} ;
3583 $self->{info}->save();
3584 $self->{info}->view();
3590 $self->can_do('r_configure');
3592 my $arg = $self->get_form('ach', 'mtxcmd', 'device', 'precmd');
3594 my $b = $self->get_bconsole();
3595 my @storages = $b->list_storage() ;
3597 unless ($arg->{ach}) {
3598 $arg->{devices} = [ map { { name => $_ } } @storages ];
3599 return $self->display($arg, "ach_add.tpl");
3603 foreach my $drive (CGI::param('drives'))
3605 unless (grep(/^$drive$/,@storages)) {
3606 return $self->error("Can't find $drive in storage list");
3609 my $index = CGI::param("index_$drive");
3610 unless (defined $index and $index =~ /^(\d+)$/) {
3611 return $self->error("Can't get $drive index");
3614 $drives[$index] = $drive;
3618 return $self->error("Can't get drives from Autochanger");
3621 my $a = new Bweb::Autochanger(name => $arg->{ach},
3622 precmd => $arg->{precmd},
3623 drive_name => \@drives,
3624 device => $arg->{device},
3625 mtxcmd => $arg->{mtxcmd});
3627 $self->ach_register($a) ;
3629 $self->{info}->view();
3635 $self->can_do('r_delete_job');
3637 my $arg = $self->get_form('jobid');
3639 if ($arg->{jobid}) {
3640 my $b = $self->get_bconsole();
3641 my $ret = $b->send_cmd("delete jobid=\"$arg->{jobid}\"");
3645 title => "Delete a job ",
3646 name => "delete jobid=$arg->{jobid}",
3654 $self->can_do('r_media_mgnt');
3656 my $arg = $self->get_form(qw/media volstatus inchanger pool
3657 slot volretention voluseduration
3658 maxvoljobs maxvolfiles maxvolbytes
3659 qcomment poolrecycle enabled
3662 unless ($arg->{media}) {
3663 return $self->error("Can't find media selection");
3666 my $update = "update volume=$arg->{media} ";
3668 if ($arg->{volstatus}) {
3669 $update .= " volstatus=$arg->{volstatus} ";
3672 if ($arg->{inchanger}) {
3673 $update .= " inchanger=yes " ;
3675 $update .= " slot=$arg->{slot} ";
3678 $update .= " slot=0 inchanger=no ";
3681 if ($arg->{enabled}) {
3682 $update .= " enabled=$arg->{enabled} ";
3686 $update .= " pool=$arg->{pool} " ;
3689 if (defined $arg->{volretention}) {
3690 $update .= " volretention=\"$arg->{volretention}\" " ;
3693 if (defined $arg->{voluseduration}) {
3694 $update .= " voluse=\"$arg->{voluseduration}\" " ;
3697 if (defined $arg->{maxvoljobs}) {
3698 $update .= " maxvoljobs=$arg->{maxvoljobs} " ;
3701 if (defined $arg->{maxvolfiles}) {
3702 $update .= " maxvolfiles=$arg->{maxvolfiles} " ;
3705 if (defined $arg->{maxvolbytes}) {
3706 $update .= " maxvolbytes=$arg->{maxvolbytes} " ;
3709 if (defined $arg->{poolrecycle}) {
3710 $update .= " recyclepool=\"$arg->{poolrecycle}\" " ;
3713 my $b = $self->get_bconsole();
3716 content => $b->send_cmd($update),
3717 title => "Update a volume ",
3723 my $media = $self->dbh_quote($arg->{media});
3725 my $loc = CGI::param('location') || '';
3727 $loc = $self->dbh_quote($loc); # is checked by db
3728 push @q, "LocationId=(SELECT LocationId FROM Location WHERE Location=$loc)";
3730 if (!$arg->{qcomment}) {
3731 $arg->{qcomment} = "''";
3733 push @q, "Comment=$arg->{qcomment}";
3738 SET " . join (',', @q) . "
3739 WHERE Media.VolumeName = $media
3741 $self->dbh_do($query);
3743 $self->update_media();
3749 $self->can_do('r_autochanger_mgnt');
3751 my $ach = CGI::param('ach') ;
3752 $ach = $self->ach_get($ach);
3754 return $self->error("Bad autochanger name");
3758 my $b = new Bconsole(pref => $self->{info},timeout => 60,log_stdout => 1);
3759 $b->update_slots($ach->{name});
3766 $self->can_do('r_view_log');
3768 my $arg = $self->get_form('jobid', 'limit', 'offset');
3769 unless ($arg->{jobid}) {
3770 return $self->error("Can't get jobid");
3773 if ($arg->{limit} == 100) {
3774 $arg->{limit} = 1000;
3776 # get security filter
3777 my $filter = $self->get_client_filter();
3780 SELECT Job.Name as name, Client.Name as clientname
3781 FROM Job INNER JOIN Client USING (ClientId) $filter
3782 WHERE JobId = $arg->{jobid}
3785 my $row = $self->dbh_selectrow_hashref($query);
3788 return $self->error("Can't find $arg->{jobid} in catalog");
3791 # display only Error and Warning messages
3793 if (CGI::param('error')) {
3794 $filter = " AND LogText $self->{sql}->{MATCH} 'Error|Warning' ";
3798 if (CGI::param('time') || $self->{info}->{display_log_time}) {
3799 $logtext = $self->dbh_strcat('Time', " ' ' ", 'LogText');
3801 $logtext = 'LogText';
3805 SELECT count(1) AS nbline, JobId AS jobid,
3806 GROUP_CONCAT($logtext $self->{sql}->{CONCAT_SEP}) AS logtxt
3808 SELECT JobId, Time, LogText
3810 WHERE ( Log.JobId = $arg->{jobid}
3812 AND Time >= (SELECT StartTime FROM Job WHERE JobId=$arg->{jobid})
3813 AND Time <= (SELECT COALESCE(EndTime,NOW()) FROM Job WHERE JobId=$arg->{jobid})
3817 OFFSET $arg->{offset}
3823 my $log = $self->dbh_selectrow_hashref($query);
3825 return $self->error("Can't get log for jobid $arg->{jobid}");
3828 $self->display({ lines=> $log->{logtxt},
3829 nbline => $log->{nbline},
3830 jobid => $arg->{jobid},
3831 name => $row->{name},
3832 client => $row->{clientname},
3833 offset => $arg->{offset},
3834 limit => $arg->{limit},
3835 }, 'display_log.tpl');
3841 $self->can_do('r_autochanger_mgnt');
3843 my $arg = $self->get_form('ach', 'slots', 'drive');
3845 unless ($arg->{ach}) {
3846 return $self->error("Can't find autochanger name");
3849 my $a = $self->ach_get($arg->{ach});
3851 return $self->error("Can't find autochanger name in configuration");
3854 my $storage = $a->get_drive_name($arg->{drive});
3856 return $self->error("Can't get your drive name");
3862 if ($arg->{slots}) {
3863 $slots = join(",", @{ $arg->{slots} });
3864 $slots_sql = " AND Slot IN ($slots) ";
3865 $t += 60*scalar( @{ $arg->{slots} }) ;
3868 my $b = new Bconsole(pref => $self->{info}, timeout => $t,log_stdout => 1);
3869 print "<h1>This command can take long time, be patient...</h1>";
3871 $b->label_barcodes(storage => $storage,
3872 drive => $arg->{drive},
3880 SET LocationId = (SELECT LocationId
3882 WHERE Location = '$arg->{ach}')
3884 WHERE (LocationId = 0 OR LocationId IS NULL)
3893 $self->can_do('r_purge');
3895 my @volume = CGI::param('media');
3898 return $self->error("Can't get media selection");
3901 my $b = new Bconsole(pref => $self->{info}, timeout => 60);
3903 foreach my $v (@volume) {
3905 content => $b->purge_volume($v),
3906 title => "Purge media",
3907 name => "purge volume=$v",
3916 $self->can_do('r_prune');
3918 my @volume = CGI::param('media');
3920 return $self->error("Can't get media selection");
3923 my $b = new Bconsole(pref => $self->{info}, timeout => 60);
3925 foreach my $v (@volume) {
3927 content => $b->prune_volume($v),
3928 title => "Prune volume",
3929 name => "prune volume=$v",
3938 $self->can_do('r_cancel_job');
3940 my $arg = $self->get_form('jobid');
3941 unless ($arg->{jobid}) {
3942 return $self->error("Can't get jobid");
3945 my $b = $self->get_bconsole();
3947 content => $b->cancel($arg->{jobid}),
3948 title => "Cancel job",
3949 name => "cancel jobid=$arg->{jobid}",
3955 # Warning, we display current fileset
3958 my $arg = $self->get_form('fileset');
3960 if ($arg->{fileset}) {
3961 my $b = $self->get_bconsole();
3962 my $ret = $b->get_fileset($arg->{fileset});
3963 $self->display({ fileset => $arg->{fileset},
3965 }, "fileset_view.tpl");
3967 $self->error("Can't get fileset name");
3971 sub director_show_sched
3975 my $arg = $self->get_form('days');
3977 my $b = $self->get_bconsole();
3978 my $ret = $b->director_get_sched( $arg->{days} );
3983 }, "scheduled_job.tpl");
3986 sub enable_disable_job
3988 my ($self, $what) = @_ ;
3989 $self->can_do('r_run_job');
3991 my $name = CGI::param('job') || '';
3992 unless ($name =~ /^[\w\d\.\-\s]+$/) {
3993 return $self->error("Can't find job name");
3996 my $b = $self->get_bconsole();
4006 content => $b->send_cmd("$cmd job=\"$name\""),
4007 title => "$cmd $name",
4008 name => "$cmd job=\"$name\"",
4015 return new Bconsole(pref => $self->{info});
4021 $self->can_do('r_run_job');
4023 my $b = $self->get_bconsole();
4025 my $joblist = [ map { { name => $_ } } $b->list_job() ];
4027 $self->display({ Jobs => $joblist }, "run_job.tpl");
4032 my ($self, $ouput) = @_;
4035 foreach my $l (split(/\r\n/, $ouput)) {
4036 if ($l =~ /(\w+): name=([\w\d\.\s-]+?)(\s+\w+=.+)?$/) {
4042 if (my @l = $l =~ /(\w+)=([\w\d*]+)/g) {
4048 foreach my $k (keys %arg) {
4049 $lowcase{lc($k)} = $arg{$k} ;
4058 $self->can_do('r_run_job');
4060 my $b = $self->get_bconsole();
4062 my $job = CGI::param('job') || '';
4064 # we take informations from director, and we overwrite with user wish
4065 my $info = $b->send_cmd("show job=\"$job\"");
4066 my $attr = $self->run_parse_job($info);
4068 my $arg = $self->get_form('pool', 'level', 'client', 'fileset', 'storage');
4069 my %job_opt = (%$attr, %$arg);
4071 my $jobs = [ map {{ name => $_ }} $b->list_job() ];
4073 my $pools = [ map { { name => $_ } } $b->list_pool() ];
4074 my $clients = [ map { { name => $_ } }$b->list_client()];
4075 my $filesets= [ map { { name => $_ } }$b->list_fileset() ];
4076 my $storages= [ map { { name => $_ } }$b->list_storage()];
4081 clients => $clients,
4082 filesets => $filesets,
4083 storages => $storages,
4085 }, "run_job_mod.tpl");
4091 $self->can_do('r_run_job');
4093 my $b = $self->get_bconsole();
4095 my $jobs = [ map {{ name => $_ }} $b->list_job() ];
4105 $self->can_do('r_run_job');
4107 my $b = $self->get_bconsole();
4109 # TODO: check input (don't use pool, level)
4111 my $arg = $self->get_form('pool', 'level', 'client', 'priority', 'when', 'fileset');
4112 my $job = CGI::param('job') || '';
4113 my $storage = CGI::param('storage') || '';
4115 my $jobid = $b->run(job => $job,
4116 client => $arg->{client},
4117 priority => $arg->{priority},
4118 level => $arg->{level},
4119 storage => $storage,
4120 pool => $arg->{pool},
4121 fileset => $arg->{fileset},
4122 when => $arg->{when},
4125 print $jobid, $b->{error};
4127 print "<br>You can follow job execution <a href='?action=dsp_cur_job;client=$arg->{client};jobid=$jobid'> here </a>";