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();
2761 if (!$self->{info}->{enable_security} or
2762 !$self->{info}->{enable_security_acl})
2767 if ($self->get_roles()) {
2768 return $self->{security}->{use_acl};
2774 # JOIN Client USING (ClientId) " . $b->get_client_filter() . "
2775 sub get_client_filter
2777 my ($self, $login) = @_;
2780 $u = $self->dbh_quote($login);
2781 } elsif ($self->use_filter()) {
2782 $u = $self->dbh_quote($self->{loginname});
2787 JOIN (SELECT ClientId FROM client_group_member
2788 JOIN client_group USING (client_group_id)
2789 JOIN bweb_client_group_acl USING (client_group_id)
2790 JOIN bweb_user USING (userid)
2791 WHERE bweb_user.username = $u
2792 ) AS filter USING (ClientId)";
2795 #JOIN client_group USING (client_group_id)" . $b->get_client_group_filter()
2796 sub get_client_group_filter
2798 my ($self, $login) = @_;
2801 $u = $self->dbh_quote($login);
2802 } elsif ($self->use_filter()) {
2803 $u = $self->dbh_quote($self->{loginname});
2808 JOIN (SELECT client_group_id
2809 FROM bweb_client_group_acl
2810 JOIN bweb_user USING (userid)
2811 WHERE bweb_user.username = $u
2812 ) AS filter USING (client_group_id)";
2815 # role and username have to be quoted before
2816 # role and username can be a quoted list
2819 my ($self, $role, $username) = @_;
2820 $self->can_do("r_user_mgnt");
2822 my $nb = $self->dbh_do("
2823 DELETE FROM bweb_role_member
2824 WHERE roleid = (SELECT roleid FROM bweb_role
2825 WHERE rolename IN ($role))
2826 AND userid = (SELECT userid FROM bweb_user
2827 WHERE username IN ($username))");
2831 # role and username have to be quoted before
2832 # role and username can be a quoted list
2835 my ($self, $role, $username) = @_;
2836 $self->can_do("r_user_mgnt");
2838 my $nb = $self->dbh_do("
2839 INSERT INTO bweb_role_member (roleid, userid)
2840 SELECT roleid, userid FROM bweb_role, bweb_user
2841 WHERE rolename IN ($role)
2842 AND username IN ($username)
2847 # role and username have to be quoted before
2848 # role and username can be a quoted list
2851 my ($self, $copy, $user) = @_;
2852 $self->can_do("r_user_mgnt");
2854 my $nb = $self->dbh_do("
2855 INSERT INTO bweb_role_member (roleid, userid)
2856 SELECT roleid, a.userid
2857 FROM bweb_user AS a, bweb_role_member
2858 JOIN bweb_user USING (userid)
2859 WHERE bweb_user.username = $copy
2860 AND a.username = $user");
2864 # username can be a join quoted list of usernames
2867 my ($self, $username) = @_;
2868 $self->can_do("r_user_mgnt");
2871 DELETE FROM bweb_role_member
2875 WHERE username in ($username))");
2877 DELETE FROM bweb_client_group_acl
2881 WHERE username IN ($username))");
2888 $self->can_do("r_user_mgnt");
2890 my $arg = $self->get_form(qw/jusernames/);
2892 unless ($arg->{jusernames}) {
2893 return $self->error("Can't get user");
2896 $self->{dbh}->begin_work();
2898 $self->revoke_all($arg->{jusernames});
2900 DELETE FROM bweb_user WHERE username IN ($arg->{jusernames})");
2902 $self->{dbh}->commit();
2904 $self->display_users();
2910 $self->can_do("r_user_mgnt");
2912 # we don't quote username directly to check that it is conform
2913 my $arg = $self->get_form(qw/username qpasswd qcomment jrolenames qcreate qcopy_username jclient_groups/) ;
2915 if (not $arg->{qcreate}) {
2916 $arg = $self->get_form(qw/db_roles db_usernames db_client_groups/);
2917 $self->display($arg, "display_user.tpl");
2921 my $u = $self->dbh_quote($arg->{username});
2923 $arg->{use_acl}=(CGI::param('use_acl')?'true':'false');
2925 if (!$arg->{qpasswd}) {
2926 $arg->{qpasswd} = "''";
2928 if (!$arg->{qcomment}) {
2929 $arg->{qcomment} = "''";
2932 # will fail if user already exists
2933 # UPDATE with mysql dbi does not return if update is ok
2936 SET passwd=$arg->{qpasswd}, comment=$arg->{qcomment},
2937 use_acl=$arg->{use_acl}
2938 WHERE username = $u")
2939 # and (! $self->dbh_is_mysql() )
2942 INSERT INTO bweb_user (username, passwd, use_acl, comment)
2943 VALUES ($u, $arg->{qpasswd}, $arg->{use_acl}, $arg->{qcomment})");
2945 $self->{dbh}->begin_work();
2947 $self->revoke_all($u);
2949 if ($arg->{qcopy_username}) {
2950 $self->grant_like($arg->{qcopy_username}, $u);
2952 $self->grant($arg->{jrolenames}, $u);
2955 if ($arg->{jclient_groups}) {
2957 INSERT INTO bweb_client_group_acl (client_group_id, userid)
2958 SELECT client_group_id, userid
2959 FROM client_group, bweb_user
2960 WHERE client_group_name IN ($arg->{jclient_groups})
2965 $self->{dbh}->commit();
2967 $self->display_users();
2970 # TODO: we miss a matrix with all user/roles
2974 $self->can_do("r_user_mgnt");
2976 my $arg = $self->get_form(qw/db_usernames/) ;
2978 if ($self->{dbh}->errstr) {
2979 return $self->error("Can't use users with bweb, read INSTALL to enable them");
2982 $self->display({ ID => $cur_id++,
2984 "display_users.tpl");
2990 $self->can_do("r_user_mgnt");
2992 my $arg = $self->get_form('username');
2993 my $user = $self->dbh_quote($arg->{username});
2995 my $userp = $self->dbh_selectrow_hashref("
2996 SELECT username, passwd, comment, use_acl
2998 WHERE username = $user
3001 return $self->error("Can't find $user in catalog");
3003 my $filter = $self->get_client_group_filter($arg->{username});
3004 my $scg = $self->dbh_selectall_hashref("
3005 SELECT client_group_name AS name
3006 FROM client_group $filter
3010 #------------+--------
3015 my $role = $self->dbh_selectall_hashref("
3016 SELECT rolename, temp.userid
3018 LEFT JOIN (SELECT roleid, userid
3019 FROM bweb_user JOIN bweb_role_member USING (userid)
3020 WHERE username = $user) AS temp USING (roleid)
3024 $arg = $self->get_form(qw/db_usernames db_client_groups/);
3027 db_usernames => $arg->{db_usernames},
3028 username => $userp->{username},
3029 comment => $userp->{comment},
3030 passwd => $userp->{passwd},
3031 use_acl => $userp->{use_acl},
3032 db_client_groups => $arg->{db_client_groups},
3033 client_group => [ values %$scg ],
3034 db_roles => [ values %$role],
3035 }, "display_user.tpl");
3039 ###########################################################
3041 sub get_media_max_size
3043 my ($self, $type) = @_;
3045 "SELECT avg(VolBytes) AS size
3047 WHERE Media.VolStatus = 'Full'
3048 AND Media.MediaType = '$type'
3051 my $res = $self->selectrow_hashref($query);
3054 return $res->{size};
3064 my $media = $self->get_form('qmedia');
3066 unless ($media->{qmedia}) {
3067 return $self->error("Can't get media");
3071 SELECT Media.Slot AS slot,
3072 PoolMedia.Name AS poolname,
3073 Media.VolStatus AS volstatus,
3074 Media.InChanger AS inchanger,
3075 Location.Location AS location,
3076 Media.VolumeName AS volumename,
3077 Media.MaxVolBytes AS maxvolbytes,
3078 Media.MaxVolJobs AS maxvoljobs,
3079 Media.MaxVolFiles AS maxvolfiles,
3080 Media.VolUseDuration AS voluseduration,
3081 Media.VolRetention AS volretention,
3082 Media.Comment AS comment,
3083 PoolRecycle.Name AS poolrecycle,
3084 Media.Enabled AS enabled
3086 FROM Media INNER JOIN Pool AS PoolMedia ON (Media.PoolId = PoolMedia.PoolId)
3087 LEFT JOIN Pool AS PoolRecycle ON (Media.RecyclePoolId = PoolRecycle.PoolId)
3088 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
3090 WHERE Media.VolumeName = $media->{qmedia}
3093 my $row = $self->dbh_selectrow_hashref($query);
3094 $row->{volretention} = human_sec($row->{volretention});
3095 $row->{voluseduration} = human_sec($row->{voluseduration});
3096 $row->{enabled} = human_enabled($row->{enabled});
3098 my $elt = $self->get_form(qw/db_pools db_locations/);
3103 }, "update_media.tpl");
3109 $self->can_do('r_media_mgnt');
3111 my $arg = $self->get_form('jmedias', 'qnewlocation') ;
3113 unless ($arg->{jmedias}) {
3114 return $self->error("Can't get selected media");
3117 unless ($arg->{qnewlocation}) {
3118 return $self->error("Can't get new location");
3123 SET LocationId = (SELECT LocationId
3125 WHERE Location = $arg->{qnewlocation})
3126 WHERE Media.VolumeName IN ($arg->{jmedias})
3129 my $nb = $self->dbh_do($query);
3131 print "$nb media updated, you may have to update your autochanger.";
3133 $self->display_media();
3139 $self->can_do('r_media_mgnt');
3141 my $media = $self->get_selected_media_location();
3143 return $self->error("Can't get media selection");
3145 my $newloc = CGI::param('newlocation');
3147 my $user = CGI::param('user') || 'unknown';
3148 my $comm = CGI::param('comment') || '';
3149 $comm = $self->dbh_quote("$user: $comm");
3151 my $arg = $self->get_form('enabled');
3152 my $en = human_enabled($arg->{enabled});
3153 my $b = $self->get_bconsole();
3156 foreach my $vol (keys %$media) {
3158 INSERT LocationLog (Date, Comment, MediaId, LocationId, NewVolStatus)
3160 NOW(), $comm, (SELECT MediaId FROM Media WHERE VolumeName = '$vol'),
3161 (SELECT LocationId FROM Location WHERE Location = '$media->{$vol}->{location}'),
3162 (SELECT VolStatus FROM Media WHERE VolumeName = '$vol')
3165 $self->dbh_do($query);
3166 $self->debug($query);
3167 $b->send_cmd("update volume=\"$vol\" enabled=$en");
3172 $q->param('action', 'update_location');
3173 my $url = $q->url(-full => 1, -query=>1);
3175 $self->display({ email => $self->{info}->{email_media},
3177 newlocation => $newloc,
3178 # [ { volumename => 'vol1' }, { volumename => 'vol2'},..]
3179 media => [ values %$media ],
3181 "change_location.tpl");
3185 sub display_client_stats
3187 my ($self, %arg) = @_ ;
3188 $self->can_do('r_view_stat');
3190 my $client = $self->dbh_quote($arg{clientname});
3191 # get security filter
3192 my $filter = $self->get_client_filter();
3194 my ($limit, $label) = $self->get_limit(%arg);
3197 count(Job.JobId) AS nb_jobs,
3198 sum(Job.JobBytes) AS nb_bytes,
3199 sum(Job.JobErrors) AS nb_err,
3200 sum(Job.JobFiles) AS nb_files,
3201 Client.Name AS clientname
3202 FROM Job JOIN Client USING (ClientId) $filter
3204 Client.Name = $client
3206 GROUP BY Client.Name
3209 my $row = $self->dbh_selectrow_hashref($query);
3211 $row->{ID} = $cur_id++;
3212 $row->{label} = $label;
3213 $row->{grapharg} = "client";
3215 $self->display($row, "display_client_stats.tpl");
3219 sub display_group_stats
3221 my ($self, %arg) = @_ ;
3223 my $carg = $self->get_form(qw/qclient_group/);
3225 unless ($carg->{qclient_group}) {
3226 return $self->error("Can't get group");
3229 my ($limit, $label) = $self->get_limit(%arg);
3233 count(Job.JobId) AS nb_jobs,
3234 sum(Job.JobBytes) AS nb_bytes,
3235 sum(Job.JobErrors) AS nb_err,
3236 sum(Job.JobFiles) AS nb_files,
3237 client_group.client_group_name AS clientname
3238 FROM Job JOIN Client USING (ClientId)
3239 JOIN client_group_member ON (Client.ClientId = client_group_member.clientid)
3240 JOIN client_group USING (client_group_id)
3242 client_group.client_group_name = $carg->{qclient_group}
3244 GROUP BY client_group.client_group_name
3247 my $row = $self->dbh_selectrow_hashref($query);
3249 $row->{ID} = $cur_id++;
3250 $row->{label} = $label;
3251 $row->{grapharg} = "client_group";
3253 $self->display($row, "display_client_stats.tpl");
3256 # poolname can be undef
3259 my ($self, $poolname) = @_ ;
3263 my $arg = $self->get_form('jmediatypes', 'qmediatypes');
3264 if ($arg->{jmediatypes}) {
3265 $whereW = "WHERE MediaType IN ($arg->{jmediatypes}) ";
3266 $whereA = "AND MediaType IN ($arg->{jmediatypes}) ";
3269 # TODO : afficher les tailles et les dates
3272 SELECT subq.volmax AS volmax,
3273 subq.volnum AS volnum,
3274 subq.voltotal AS voltotal,
3276 Pool.Recycle AS recycle,
3277 Pool.VolRetention AS volretention,
3278 Pool.VolUseDuration AS voluseduration,
3279 Pool.MaxVolJobs AS maxvoljobs,
3280 Pool.MaxVolFiles AS maxvolfiles,
3281 Pool.MaxVolBytes AS maxvolbytes,
3282 subq.PoolId AS PoolId,
3283 subq.MediaType AS mediatype,
3284 $self->{sql}->{CAT_POOL_TYPE} AS uniq
3287 SELECT COALESCE(media_avg_size.volavg,0) * count(Media.MediaId) AS volmax,
3288 count(Media.MediaId) AS volnum,
3289 sum(Media.VolBytes) AS voltotal,
3290 Media.PoolId AS PoolId,
3291 Media.MediaType AS MediaType
3293 LEFT JOIN (SELECT avg(Media.VolBytes) AS volavg,
3294 Media.MediaType AS MediaType
3296 WHERE Media.VolStatus = 'Full'
3297 GROUP BY Media.MediaType
3298 ) AS media_avg_size ON (Media.MediaType = media_avg_size.MediaType)
3299 GROUP BY Media.MediaType, Media.PoolId, media_avg_size.volavg
3301 LEFT JOIN Pool ON (Pool.PoolId = subq.PoolId)
3305 my $all = $self->dbh_selectall_hashref($query, 'uniq') ;
3308 SELECT Pool.Name AS name,
3309 sum(VolBytes) AS size
3310 FROM Media JOIN Pool ON (Media.PoolId = Pool.PoolId)
3311 WHERE Media.VolStatus IN ('Recycled', 'Purged')
3315 my $empty = $self->dbh_selectall_hashref($query, 'name');
3317 foreach my $p (values %$all) {
3318 if ($p->{volmax} > 0) { # mysql returns 0.0000
3319 # we remove Recycled/Purged media from pool usage
3320 if (defined $empty->{$p->{name}}) {
3321 $p->{voltotal} -= $empty->{$p->{name}}->{size};
3323 $p->{poolusage} = sprintf('%.2f', $p->{voltotal} * 100/ $p->{volmax}) ;
3325 $p->{poolusage} = 0;
3329 SELECT VolStatus AS volstatus, count(MediaId) AS nb
3331 WHERE PoolId=$p->{poolid}
3332 AND Media.MediaType = '$p->{mediatype}'
3336 my $content = $self->dbh_selectall_hashref($query, 'volstatus');
3337 foreach my $t (values %$content) {
3338 $p->{"nb_" . $t->{volstatus}} = $t->{nb} ;
3343 $self->display({ ID => $cur_id++,
3344 MediaType => $arg->{qmediatypes}, # [ { name => type1 } , { name => type2 } ]
3345 Pools => [ values %$all ]},
3346 "display_pool.tpl");
3349 sub display_running_job
3352 return if $self->cant_do('r_view_running_job');
3354 my $arg = $self->get_form('client', 'jobid');
3356 if (!$arg->{client} and $arg->{jobid}) {
3357 # get security filter
3358 my $filter = $self->get_client_filter();
3361 SELECT Client.Name AS name
3362 FROM Job INNER JOIN Client USING (ClientId) $filter
3363 WHERE Job.JobId = $arg->{jobid}
3366 my $row = $self->dbh_selectrow_hashref($query);
3369 $arg->{client} = $row->{name};
3370 CGI::param('client', $arg->{client});
3374 if ($arg->{client}) {
3375 my $cli = new Bweb::Client(name => $arg->{client});
3376 $cli->display_running_job($self->{info}, $arg->{jobid});
3377 if ($arg->{jobid}) {
3378 $self->get_job_log();
3381 $self->error("Can't get client or jobid");
3385 sub display_running_jobs
3387 my ($self, $display_action) = @_;
3388 return if $self->cant_do('r_view_running_job');
3390 # get security filter
3391 my $filter = $self->get_client_filter();
3394 SELECT Job.JobId AS jobid,
3395 Job.Name AS jobname,
3397 Job.StartTime AS starttime,
3398 Job.JobFiles AS jobfiles,
3399 Job.JobBytes AS jobbytes,
3400 Job.JobStatus AS jobstatus,
3401 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(NOW())
3402 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime))
3404 Client.Name AS clientname
3405 FROM Job INNER JOIN Client USING (ClientId) $filter
3407 JobStatus IN ('C','R','B','e','D','F','S','m','M','s','j','c','d','t','p')
3409 my $all = $self->dbh_selectall_hashref($query, 'jobid') ;
3411 $self->display({ ID => $cur_id++,
3412 display_action => $display_action,
3413 Jobs => [ values %$all ]},
3414 "running_job.tpl") ;
3417 # return the autochanger list to update
3421 $self->can_do('r_media_mgnt');
3424 my $arg = $self->get_form('jmedias');
3426 unless ($arg->{jmedias}) {
3427 return $self->error("Can't get media selection");
3431 SELECT Media.VolumeName AS volumename,
3432 Storage.Name AS storage,
3433 Location.Location AS location,
3435 FROM Media INNER JOIN Storage ON (Media.StorageId = Storage.StorageId)
3436 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
3437 WHERE Media.VolumeName IN ($arg->{jmedias})
3438 AND Media.InChanger = 1
3441 my $all = $self->dbh_selectall_hashref($query, 'volumename');
3443 foreach my $vol (values %$all) {
3444 my $a = $self->ach_get($vol->{location});
3446 $ret{$vol->{location}} = 1;
3448 unless ($a->{have_status}) {
3450 $a->{have_status} = 1;
3453 print "eject $vol->{volumename} from $vol->{storage} : ";
3454 if ($a->send_to_io($vol->{slot})) {
3455 print "<img src='/bweb/T.png' alt='ok'><br/>";
3457 print "<img src='/bweb/E.png' alt='err'><br/>";
3467 my ($to, $subject, $content) = (CGI::param('email'),
3468 CGI::param('subject'),
3469 CGI::param('content'));
3470 $to =~ s/[^\w\d\.\@<>,]//;
3471 $subject =~ s/[^\w\d\.\[\]]/ /;
3473 open(MAIL, "|mail -s '$subject' '$to'") ;
3474 print MAIL $content;
3484 my $arg = $self->get_form('jobid', 'client');
3486 print CGI::header('text/brestore');
3487 print "jobid=$arg->{jobid}\n" if ($arg->{jobid});
3488 print "client=$arg->{client}\n" if ($arg->{client});
3489 print "\n\nYou have to assign this mime type with /usr/bin/brestore.pl\n";
3493 # TODO : move this to Bweb::Autochanger ?
3494 # TODO : make this internal to not eject tape ?
3500 my ($self, $name) = @_;
3503 return $self->error("Can't get your autochanger name ach");
3506 unless ($self->{info}->{ach_list}) {
3507 return $self->error("Could not find any autochanger");
3510 my $a = $self->{info}->{ach_list}->{$name};
3513 $self->error("Can't get your autochanger $name from your ach_list");
3518 $a->{debug} = $self->{debug};
3525 my ($self, $ach) = @_;
3526 $self->can_do('r_configure');
3528 $self->{info}->{ach_list}->{$ach->{name}} = $ach;
3530 $self->{info}->save();
3538 $self->can_do('r_configure');
3540 my $arg = $self->get_form('ach');
3542 or !$self->{info}->{ach_list}
3543 or !$self->{info}->{ach_list}->{$arg->{ach}})
3545 return $self->error("Can't get autochanger name");
3548 my $ach = $self->{info}->{ach_list}->{$arg->{ach}};
3552 [ map { { name => $_, index => $i++ } } @{$ach->{drive_name}} ] ;
3554 my $b = $self->get_bconsole();
3556 my @storages = $b->list_storage() ;
3558 $ach->{devices} = [ map { { name => $_ } } @storages ];
3560 $self->display($ach, "ach_add.tpl");
3561 delete $ach->{drives};
3562 delete $ach->{devices};
3569 $self->can_do('r_configure');
3571 my $arg = $self->get_form('ach');
3574 or !$self->{info}->{ach_list}
3575 or !$self->{info}->{ach_list}->{$arg->{ach}})
3577 return $self->error("Can't get autochanger name");
3580 delete $self->{info}->{ach_list}->{$arg->{ach}} ;
3582 $self->{info}->save();
3583 $self->{info}->view();
3589 $self->can_do('r_configure');
3591 my $arg = $self->get_form('ach', 'mtxcmd', 'device', 'precmd');
3593 my $b = $self->get_bconsole();
3594 my @storages = $b->list_storage() ;
3596 unless ($arg->{ach}) {
3597 $arg->{devices} = [ map { { name => $_ } } @storages ];
3598 return $self->display($arg, "ach_add.tpl");
3602 foreach my $drive (CGI::param('drives'))
3604 unless (grep(/^$drive$/,@storages)) {
3605 return $self->error("Can't find $drive in storage list");
3608 my $index = CGI::param("index_$drive");
3609 unless (defined $index and $index =~ /^(\d+)$/) {
3610 return $self->error("Can't get $drive index");
3613 $drives[$index] = $drive;
3617 return $self->error("Can't get drives from Autochanger");
3620 my $a = new Bweb::Autochanger(name => $arg->{ach},
3621 precmd => $arg->{precmd},
3622 drive_name => \@drives,
3623 device => $arg->{device},
3624 mtxcmd => $arg->{mtxcmd});
3626 $self->ach_register($a) ;
3628 $self->{info}->view();
3634 $self->can_do('r_delete_job');
3636 my $arg = $self->get_form('jobid');
3638 if ($arg->{jobid}) {
3639 my $b = $self->get_bconsole();
3640 my $ret = $b->send_cmd("delete jobid=\"$arg->{jobid}\"");
3644 title => "Delete a job ",
3645 name => "delete jobid=$arg->{jobid}",
3653 $self->can_do('r_media_mgnt');
3655 my $arg = $self->get_form(qw/media volstatus inchanger pool
3656 slot volretention voluseduration
3657 maxvoljobs maxvolfiles maxvolbytes
3658 qcomment poolrecycle enabled
3661 unless ($arg->{media}) {
3662 return $self->error("Can't find media selection");
3665 my $update = "update volume=$arg->{media} ";
3667 if ($arg->{volstatus}) {
3668 $update .= " volstatus=$arg->{volstatus} ";
3671 if ($arg->{inchanger}) {
3672 $update .= " inchanger=yes " ;
3674 $update .= " slot=$arg->{slot} ";
3677 $update .= " slot=0 inchanger=no ";
3680 if ($arg->{enabled}) {
3681 $update .= " enabled=$arg->{enabled} ";
3685 $update .= " pool=$arg->{pool} " ;
3688 if (defined $arg->{volretention}) {
3689 $update .= " volretention=\"$arg->{volretention}\" " ;
3692 if (defined $arg->{voluseduration}) {
3693 $update .= " voluse=\"$arg->{voluseduration}\" " ;
3696 if (defined $arg->{maxvoljobs}) {
3697 $update .= " maxvoljobs=$arg->{maxvoljobs} " ;
3700 if (defined $arg->{maxvolfiles}) {
3701 $update .= " maxvolfiles=$arg->{maxvolfiles} " ;
3704 if (defined $arg->{maxvolbytes}) {
3705 $update .= " maxvolbytes=$arg->{maxvolbytes} " ;
3708 if (defined $arg->{poolrecycle}) {
3709 $update .= " recyclepool=\"$arg->{poolrecycle}\" " ;
3712 my $b = $self->get_bconsole();
3715 content => $b->send_cmd($update),
3716 title => "Update a volume ",
3722 my $media = $self->dbh_quote($arg->{media});
3724 my $loc = CGI::param('location') || '';
3726 $loc = $self->dbh_quote($loc); # is checked by db
3727 push @q, "LocationId=(SELECT LocationId FROM Location WHERE Location=$loc)";
3729 if (!$arg->{qcomment}) {
3730 $arg->{qcomment} = "''";
3732 push @q, "Comment=$arg->{qcomment}";
3737 SET " . join (',', @q) . "
3738 WHERE Media.VolumeName = $media
3740 $self->dbh_do($query);
3742 $self->update_media();
3748 $self->can_do('r_autochanger_mgnt');
3750 my $ach = CGI::param('ach') ;
3751 $ach = $self->ach_get($ach);
3753 return $self->error("Bad autochanger name");
3757 my $b = new Bconsole(pref => $self->{info},timeout => 60,log_stdout => 1);
3758 $b->update_slots($ach->{name});
3765 $self->can_do('r_view_log');
3767 my $arg = $self->get_form('jobid', 'limit', 'offset');
3768 unless ($arg->{jobid}) {
3769 return $self->error("Can't get jobid");
3772 if ($arg->{limit} == 100) {
3773 $arg->{limit} = 1000;
3775 # get security filter
3776 my $filter = $self->get_client_filter();
3779 SELECT Job.Name as name, Client.Name as clientname
3780 FROM Job INNER JOIN Client USING (ClientId) $filter
3781 WHERE JobId = $arg->{jobid}
3784 my $row = $self->dbh_selectrow_hashref($query);
3787 return $self->error("Can't find $arg->{jobid} in catalog");
3790 # display only Error and Warning messages
3792 if (CGI::param('error')) {
3793 $filter = " AND LogText $self->{sql}->{MATCH} 'Error|Warning' ";
3797 if (CGI::param('time') || $self->{info}->{display_log_time}) {
3798 $logtext = $self->dbh_strcat('Time', " ' ' ", 'LogText');
3800 $logtext = 'LogText';
3804 SELECT count(1) AS nbline, JobId AS jobid,
3805 GROUP_CONCAT($logtext $self->{sql}->{CONCAT_SEP}) AS logtxt
3807 SELECT JobId, Time, LogText
3809 WHERE ( Log.JobId = $arg->{jobid}
3811 AND Time >= (SELECT StartTime FROM Job WHERE JobId=$arg->{jobid})
3812 AND Time <= (SELECT COALESCE(EndTime,NOW()) FROM Job WHERE JobId=$arg->{jobid})
3816 OFFSET $arg->{offset}
3822 my $log = $self->dbh_selectrow_hashref($query);
3824 return $self->error("Can't get log for jobid $arg->{jobid}");
3827 $self->display({ lines=> $log->{logtxt},
3828 nbline => $log->{nbline},
3829 jobid => $arg->{jobid},
3830 name => $row->{name},
3831 client => $row->{clientname},
3832 offset => $arg->{offset},
3833 limit => $arg->{limit},
3834 }, 'display_log.tpl');
3840 $self->can_do('r_autochanger_mgnt');
3842 my $arg = $self->get_form('ach', 'slots', 'drive');
3844 unless ($arg->{ach}) {
3845 return $self->error("Can't find autochanger name");
3848 my $a = $self->ach_get($arg->{ach});
3850 return $self->error("Can't find autochanger name in configuration");
3853 my $storage = $a->get_drive_name($arg->{drive});
3855 return $self->error("Can't get your drive name");
3861 if ($arg->{slots}) {
3862 $slots = join(",", @{ $arg->{slots} });
3863 $slots_sql = " AND Slot IN ($slots) ";
3864 $t += 60*scalar( @{ $arg->{slots} }) ;
3867 my $b = new Bconsole(pref => $self->{info}, timeout => $t,log_stdout => 1);
3868 print "<h1>This command can take long time, be patient...</h1>";
3870 $b->label_barcodes(storage => $storage,
3871 drive => $arg->{drive},
3879 SET LocationId = (SELECT LocationId
3881 WHERE Location = '$arg->{ach}')
3883 WHERE (LocationId = 0 OR LocationId IS NULL)
3892 $self->can_do('r_purge');
3894 my @volume = CGI::param('media');
3897 return $self->error("Can't get media selection");
3900 my $b = new Bconsole(pref => $self->{info}, timeout => 60);
3902 foreach my $v (@volume) {
3904 content => $b->purge_volume($v),
3905 title => "Purge media",
3906 name => "purge volume=$v",
3915 $self->can_do('r_prune');
3917 my @volume = CGI::param('media');
3919 return $self->error("Can't get media selection");
3922 my $b = new Bconsole(pref => $self->{info}, timeout => 60);
3924 foreach my $v (@volume) {
3926 content => $b->prune_volume($v),
3927 title => "Prune volume",
3928 name => "prune volume=$v",
3937 $self->can_do('r_cancel_job');
3939 my $arg = $self->get_form('jobid');
3940 unless ($arg->{jobid}) {
3941 return $self->error("Can't get jobid");
3944 my $b = $self->get_bconsole();
3946 content => $b->cancel($arg->{jobid}),
3947 title => "Cancel job",
3948 name => "cancel jobid=$arg->{jobid}",
3954 # Warning, we display current fileset
3957 my $arg = $self->get_form('fileset');
3959 if ($arg->{fileset}) {
3960 my $b = $self->get_bconsole();
3961 my $ret = $b->get_fileset($arg->{fileset});
3962 $self->display({ fileset => $arg->{fileset},
3964 }, "fileset_view.tpl");
3966 $self->error("Can't get fileset name");
3970 sub director_show_sched
3974 my $arg = $self->get_form('days');
3976 my $b = $self->get_bconsole();
3977 my $ret = $b->director_get_sched( $arg->{days} );
3982 }, "scheduled_job.tpl");
3985 sub enable_disable_job
3987 my ($self, $what) = @_ ;
3988 $self->can_do('r_run_job');
3990 my $name = CGI::param('job') || '';
3991 unless ($name =~ /^[\w\d\.\-\s]+$/) {
3992 return $self->error("Can't find job name");
3995 my $b = $self->get_bconsole();
4005 content => $b->send_cmd("$cmd job=\"$name\""),
4006 title => "$cmd $name",
4007 name => "$cmd job=\"$name\"",
4014 return new Bconsole(pref => $self->{info});
4020 $self->can_do('r_run_job');
4022 my $b = $self->get_bconsole();
4024 my $joblist = [ map { { name => $_ } } $b->list_job() ];
4026 $self->display({ Jobs => $joblist }, "run_job.tpl");
4031 my ($self, $ouput) = @_;
4034 foreach my $l (split(/\r\n/, $ouput)) {
4035 if ($l =~ /(\w+): name=([\w\d\.\s-]+?)(\s+\w+=.+)?$/) {
4041 if (my @l = $l =~ /(\w+)=([\w\d*]+)/g) {
4047 foreach my $k (keys %arg) {
4048 $lowcase{lc($k)} = $arg{$k} ;
4057 $self->can_do('r_run_job');
4059 my $b = $self->get_bconsole();
4061 my $job = CGI::param('job') || '';
4063 # we take informations from director, and we overwrite with user wish
4064 my $info = $b->send_cmd("show job=\"$job\"");
4065 my $attr = $self->run_parse_job($info);
4067 my $arg = $self->get_form('pool', 'level', 'client', 'fileset', 'storage');
4068 my %job_opt = (%$attr, %$arg);
4070 my $jobs = [ map {{ name => $_ }} $b->list_job() ];
4072 my $pools = [ map { { name => $_ } } $b->list_pool() ];
4073 my $clients = [ map { { name => $_ } }$b->list_client()];
4074 my $filesets= [ map { { name => $_ } }$b->list_fileset() ];
4075 my $storages= [ map { { name => $_ } }$b->list_storage()];
4080 clients => $clients,
4081 filesets => $filesets,
4082 storages => $storages,
4084 }, "run_job_mod.tpl");
4090 $self->can_do('r_run_job');
4092 my $b = $self->get_bconsole();
4094 my $jobs = [ map {{ name => $_ }} $b->list_job() ];
4104 $self->can_do('r_run_job');
4106 my $b = $self->get_bconsole();
4108 # TODO: check input (don't use pool, level)
4110 my $arg = $self->get_form('pool', 'level', 'client', 'priority', 'when', 'fileset');
4111 my $job = CGI::param('job') || '';
4112 my $storage = CGI::param('storage') || '';
4114 my $jobid = $b->run(job => $job,
4115 client => $arg->{client},
4116 priority => $arg->{priority},
4117 level => $arg->{level},
4118 storage => $storage,
4119 pool => $arg->{pool},
4120 fileset => $arg->{fileset},
4121 when => $arg->{when},
4124 print $jobid, $b->{error};
4126 print "<br>You can follow job execution <a href='?action=dsp_cur_job;client=$arg->{client};jobid=$jobid'> here </a>";