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 '' ",
1090 $self->{dbh}->disconnect();
1095 sub dbh_selectall_arrayref
1097 my ($self, $query) = @_;
1098 $self->connect_db();
1099 $self->debug($query);
1100 return $self->{dbh}->selectall_arrayref($query);
1105 my ($self, @what) = @_;
1106 return join(',', $self->dbh_quote(@what)) ;
1111 my ($self, @what) = @_;
1113 $self->connect_db();
1115 return map { $self->{dbh}->quote($_) } @what;
1117 return $self->{dbh}->quote($what[0]) ;
1123 my ($self, $query) = @_ ;
1124 $self->connect_db();
1125 $self->debug($query);
1126 return $self->{dbh}->do($query);
1129 sub dbh_selectall_hashref
1131 my ($self, $query, $join) = @_;
1133 $self->connect_db();
1134 $self->debug($query);
1135 return $self->{dbh}->selectall_hashref($query, $join) ;
1138 sub dbh_selectrow_hashref
1140 my ($self, $query) = @_;
1142 $self->connect_db();
1143 $self->debug($query);
1144 return $self->{dbh}->selectrow_hashref($query) ;
1149 my ($self, @what) = @_;
1150 if ($self->{conf}->{connection_string} =~ /dbi:mysql/i) {
1151 return 'CONCAT(' . join(',', @what) . ')' ;
1153 return join(' || ', @what);
1159 my ($self, $query) = @_;
1160 $self->debug($query, up => 1);
1161 return $self->{dbh}->prepare($query);
1167 my @unit = qw(B KB MB GB TB);
1168 my $val = shift || 0;
1170 my $format = '%i %s';
1171 while ($val / 1024 > 1) {
1175 $format = ($i>0)?'%0.1f %s':'%i %s';
1176 return sprintf($format, $val, $unit[$i]);
1179 # display Day, Hour, Year
1185 $val /= 60; # sec -> min
1187 if ($val / 60 <= 1) {
1191 $val /= 60; # min -> hour
1192 if ($val / 24 <= 1) {
1193 return "$val hours";
1196 $val /= 24; # hour -> day
1197 if ($val / 365 < 2) {
1201 $val /= 365 ; # day -> year
1203 return "$val years";
1209 my $val = shift || 0;
1211 if ($val eq '1' or $val eq "yes") {
1213 } elsif ($val eq '2' or $val eq "archived") {
1221 sub from_human_enabled
1223 my $val = shift || 0;
1225 if ($val == 1 or $val eq "yes") {
1227 } elsif ($val == 2 or $val eq "archived") {
1234 # get Day, Hour, Year
1240 unless ($val =~ /^\s*(\d+)\s*(\w)\w*\s*$/) {
1244 my %times = ( m => 60,
1250 my $mult = $times{$2} || 0;
1260 unless ($self->{dbh}) {
1261 $self->{dbh} = DBI->connect($self->{info}->{dbi},
1262 $self->{info}->{user},
1263 $self->{info}->{password});
1265 $self->error("Can't connect to your database:\n$DBI::errstr\n")
1266 unless ($self->{dbh});
1268 $self->{dbh}->{FetchHashKeyName} = 'NAME_lc';
1270 if ($self->{info}->{dbi} =~ /^dbi:Pg/i) {
1271 $self->{dbh}->do("SET datestyle TO 'ISO, YMD'");
1273 $self->{dbh}->do("SET group_concat_max_len=1000000");
1280 my ($class, %arg) = @_;
1282 dbh => undef, # connect_db();
1284 dbi => '', # DBI:Pg:database=bacula;host=127.0.0.1
1290 map { $self->{lc($_)} = $arg{$_} } keys %arg ;
1292 if ($self->{info}->{dbi} =~ /DBI:(\w+):/i) {
1293 $self->{sql} = $sql_func{$1};
1296 $self->{loginname} = CGI::remote_user();
1297 $self->{debug} = $self->{info}->{debug};
1298 $Bweb::Gui::template_dir = $self->{info}->{template_dir};
1306 $self->display($self->{info}, "begin.tpl");
1312 $self->display($self->{info}, "end.tpl");
1318 my $where=''; # by default
1320 my $arg = $self->get_form("client", "qre_client",
1321 "jclient_groups", "qnotingroup");
1323 if ($arg->{qre_client}) {
1324 $where = "WHERE Name $self->{sql}->{MATCH} $arg->{qre_client} ";
1325 } elsif ($arg->{client}) {
1326 $where = "WHERE Name = '$arg->{client}' ";
1327 } elsif ($arg->{jclient_groups}) {
1328 # $filter could already contains client_group_member
1330 JOIN client_group_member USING (ClientId)
1331 JOIN client_group USING (client_group_id)
1332 WHERE client_group_name IN ($arg->{jclient_groups}) ";
1333 } elsif ($arg->{qnotingroup}) {
1336 (SELECT 1 FROM client_group_member
1337 WHERE Client.ClientId = client_group_member.ClientId
1343 SELECT Name AS name,
1345 AutoPrune AS autoprune,
1346 FileRetention AS fileretention,
1347 JobRetention AS jobretention
1348 FROM Client " . $self->get_client_filter() .
1351 my $all = $self->dbh_selectall_hashref($query, 'name') ;
1353 my $dsp = { ID => $cur_id++,
1354 clients => [ values %$all] };
1356 $self->display($dsp, "client_list.tpl") ;
1361 my ($self, %arg) = @_;
1368 "AND $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
1370 ( $self->{sql}->{UNIX_TIMESTAMP}(NOW())
1372 $self->{sql}->{TO_SEC}($arg{age})
1375 $label = "last " . human_sec($arg{age});
1378 if ($arg{groupby}) {
1379 $limit .= " GROUP BY $arg{groupby} ";
1383 $limit .= " ORDER BY $arg{order} ";
1387 $limit .= " LIMIT $arg{limit} ";
1388 $label .= " limited to $arg{limit}";
1392 $limit .= " OFFSET $arg{offset} ";
1393 $label .= " with $arg{offset} offset ";
1397 $label = 'no filter';
1400 return ($limit, $label);
1405 $bweb->get_form(...) - Get useful stuff
1409 This function get and check parameters against regexp.
1411 If word begin with 'q', the return will be quoted or join quoted
1412 if it's end with 's'.
1417 $bweb->get_form('jobid', 'qclient', 'qpools') ;
1420 qclient => 'plume-fd',
1421 qpools => "'plume-fd', 'test-fd', '...'",
1428 my ($self, @what) = @_;
1429 my %what = map { $_ => 1 } @what;
1451 my %opt_ss =( # string with space
1455 my %opt_s = ( # default to ''
1476 my %opt_p = ( # option with path
1483 my %opt_r = (regexwhere => 1);
1485 my %opt_d = ( # option with date
1490 foreach my $i (@what) {
1491 if (exists $opt_i{$i}) {# integer param
1492 my $value = CGI::param($i) || $opt_i{$i} ;
1493 if ($value =~ /^(\d+)$/) {
1496 } elsif ($opt_s{$i}) { # simple string param
1497 my $value = CGI::param($i) || '';
1498 if ($value =~ /^([\w\d\.-]+)$/) {
1501 } elsif ($opt_ss{$i}) { # simple string param (with space)
1502 my $value = CGI::param($i) || '';
1503 if ($value =~ /^([\w\d\.\-\s]+)$/) {
1506 } elsif ($i =~ /^j(\w+)s$/) { # quote join args "'arg1', 'arg2'"
1507 my @value = grep { ! /^\s*$/ } CGI::param($1) ;
1509 $ret{$i} = $self->dbh_join(@value) ;
1512 } elsif ($i =~ /^q(\w+[^s])$/) { # 'arg1'
1513 my $value = CGI::param($1) ;
1515 $ret{$i} = $self->dbh_quote($value);
1518 } elsif ($i =~ /^q(\w+)s$/) { #[ 'arg1', 'arg2']
1519 $ret{$i} = [ map { { name => $self->dbh_quote($_) } }
1520 grep { ! /^\s*$/ } CGI::param($1) ];
1521 } elsif (exists $opt_p{$i}) {
1522 my $value = CGI::param($i) || '';
1523 if ($value =~ /^([\w\d\.\/\s:\@\-]+)$/) {
1526 } elsif (exists $opt_r{$i}) {
1527 my $value = CGI::param($i) || '';
1528 if ($value =~ /^([^'"']+)$/) {
1531 } elsif (exists $opt_d{$i}) {
1532 my $value = CGI::param($i) || '';
1533 if ($value =~ /^\s*(\d+\s+\w+)$/) {
1540 foreach my $s (CGI::param('slot')) {
1541 if ($s =~ /^(\d+)$/) {
1542 push @{$ret{slots}}, $s;
1548 my $when = CGI::param('when') || '';
1549 if ($when =~ /^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})$/) {
1554 if ($what{db_clients}) {
1556 if ($what{filter}) {
1557 # get security filter only if asked
1558 $filter = $self->get_client_filter();
1562 SELECT Client.Name as clientname
1566 my $clients = $self->dbh_selectall_hashref($query, 'clientname');
1567 $ret{db_clients} = [sort {$a->{clientname} cmp $b->{clientname} }
1571 if ($what{db_client_groups}) {
1573 if ($what{filter}) {
1574 # get security filter only if asked
1575 $filter = $self->get_client_group_filter();
1579 SELECT client_group_name AS name
1580 FROM client_group $filter
1583 my $grps = $self->dbh_selectall_hashref($query, 'name');
1584 $ret{db_client_groups} = [sort {$a->{name} cmp $b->{name} }
1588 if ($what{db_usernames}) {
1594 my $users = $self->dbh_selectall_hashref($query, 'username');
1595 $ret{db_usernames} = [sort {$a->{username} cmp $b->{username} }
1599 if ($what{db_roles}) {
1605 my $r = $self->dbh_selectall_hashref($query, 'rolename');
1606 $ret{db_roles} = [sort {$a->{rolename} cmp $b->{rolename} }
1610 if ($what{db_mediatypes}) {
1612 SELECT MediaType as mediatype
1616 my $media = $self->dbh_selectall_hashref($query, 'mediatype');
1617 $ret{db_mediatypes} = [sort {$a->{mediatype} cmp $b->{mediatype} }
1621 if ($what{db_locations}) {
1623 SELECT Location as location, Cost as cost
1626 my $loc = $self->dbh_selectall_hashref($query, 'location');
1627 $ret{db_locations} = [ sort { $a->{location}
1633 if ($what{db_pools}) {
1634 my $query = "SELECT Name as name FROM Pool";
1636 my $all = $self->dbh_selectall_hashref($query, 'name') ;
1637 $ret{db_pools} = [ sort { $a->{name} cmp $b->{name} } values %$all ];
1640 if ($what{db_filesets}) {
1642 SELECT FileSet.FileSet AS fileset
1646 my $filesets = $self->dbh_selectall_hashref($query, 'fileset');
1648 $ret{db_filesets} = [sort {lc($a->{fileset}) cmp lc($b->{fileset}) }
1649 values %$filesets] ;
1652 if ($what{db_jobnames}) {
1654 if ($what{filter}) {
1655 $filter = " JOIN Client USING (ClientId) " . $self->get_client_filter();
1658 SELECT DISTINCT Job.Name AS jobname
1662 my $jobnames = $self->dbh_selectall_hashref($query, 'jobname');
1664 $ret{db_jobnames} = [sort {lc($a->{jobname}) cmp lc($b->{jobname}) }
1665 values %$jobnames] ;
1668 if ($what{db_devices}) {
1670 SELECT Device.Name AS name
1674 my $devices = $self->dbh_selectall_hashref($query, 'name');
1676 $ret{db_devices} = [sort {lc($a->{name}) cmp lc($b->{name}) }
1687 my $fields = $self->get_form(qw/age level status clients filesets
1688 graph gtype type filter db_clients
1689 limit db_filesets width height
1690 qclients qfilesets qjobnames db_jobnames/);
1693 my $url = CGI::url(-full => 0,
1696 $url =~ s/^.+?\?//; # http://path/to/bweb.pl?arg => arg
1698 # this organisation is to keep user choice between 2 click
1699 # TODO : fileset and client selection doesn't work
1708 sub get_selected_media_location
1712 my $media = $self->get_form('jmedias');
1714 unless ($media->{jmedias}) {
1719 SELECT Media.VolumeName AS volumename, Location.Location AS location
1720 FROM Media LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
1721 WHERE Media.VolumeName IN ($media->{jmedias})
1724 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1726 # { 'vol1' => { [volumename => 'vol1', location => 'ici'],
1735 my ($self, $in) = @_ ;
1737 my $media = $self->get_selected_media_location();
1743 my $elt = $self->get_form('db_locations');
1745 $self->display({ ID => $cur_id++,
1746 enabled => human_enabled($in),
1747 %$elt, # db_locations
1749 sort { $a->{volumename} cmp $b->{volumename} } values %$media
1759 my $elt = $self->get_form(qw/db_pools db_mediatypes db_locations/) ;
1761 $self->display($elt, "help_extern.tpl");
1764 sub help_extern_compute
1768 my $number = CGI::param('limit') || '' ;
1769 unless ($number =~ /^(\d+)$/) {
1770 return $self->error("Bad arg number : $number ");
1773 my ($sql, undef) = $self->get_param('pools',
1774 'locations', 'mediatypes');
1777 SELECT Media.VolumeName AS volumename,
1778 Media.VolStatus AS volstatus,
1779 Media.LastWritten AS lastwritten,
1780 Media.MediaType AS mediatype,
1781 Media.VolMounts AS volmounts,
1783 Media.Recycle AS recycle,
1784 $self->{sql}->{FROM_UNIXTIME}(
1785 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1786 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1789 INNER JOIN Pool ON (Pool.PoolId = Media.PoolId)
1790 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
1792 WHERE Media.InChanger = 1
1793 AND Media.VolStatus IN ('Disabled', 'Error', 'Full')
1795 ORDER BY expire DESC, recycle, Media.VolMounts DESC
1799 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1801 $self->display({ Media => [ values %$all ] },
1802 "help_extern_compute.tpl");
1809 my $param = $self->get_form(qw/db_locations db_pools db_mediatypes/) ;
1810 $self->display($param, "help_intern.tpl");
1813 sub help_intern_compute
1817 my $number = CGI::param('limit') || '' ;
1818 unless ($number =~ /^(\d+)$/) {
1819 return $self->error("Bad arg number : $number ");
1822 my ($sql, undef) = $self->get_param('pools', 'locations', 'mediatypes');
1824 if (CGI::param('expired')) {
1826 AND ( $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1827 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1833 SELECT Media.VolumeName AS volumename,
1834 Media.VolStatus AS volstatus,
1835 Media.LastWritten AS lastwritten,
1836 Media.MediaType AS mediatype,
1837 Media.VolMounts AS volmounts,
1839 $self->{sql}->{FROM_UNIXTIME}(
1840 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1841 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1844 INNER JOIN Pool ON (Pool.PoolId = Media.PoolId)
1845 LEFT JOIN Location ON (Location.LocationId = Media.LocationId)
1847 WHERE Media.InChanger <> 1
1848 AND Media.VolStatus IN ('Purged', 'Full', 'Append')
1849 AND Media.Recycle = 1
1851 ORDER BY Media.VolUseDuration DESC, Media.VolMounts ASC, expire ASC
1855 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1857 $self->display({ Media => [ values %$all ] },
1858 "help_intern_compute.tpl");
1864 my ($self, %arg) = @_ ;
1866 my ($limit, $label) = $self->get_limit(%arg);
1870 (SELECT count(Pool.PoolId) FROM Pool) AS nb_pool,
1871 (SELECT count(Media.MediaId) FROM Media) AS nb_media,
1872 (SELECT count(Job.JobId) FROM Job) AS nb_job,
1873 (SELECT sum(VolBytes) FROM Media) AS nb_bytes,
1874 ($self->{sql}->{DB_SIZE}) AS db_size,
1875 (SELECT count(Job.JobId)
1877 WHERE Job.JobStatus IN ('E','e','f','A')
1880 (SELECT count(Client.ClientId) FROM Client) AS nb_client
1883 my $row = $self->dbh_selectrow_hashref($query) ;
1885 $row->{nb_bytes} = human_size($row->{nb_bytes});
1887 $row->{db_size} = human_size($row->{db_size});
1888 $row->{label} = $label;
1890 $self->display($row, "general.tpl");
1895 my ($self, @what) = @_ ;
1896 my %elt = map { $_ => 1 } @what;
1901 if ($elt{clients}) {
1902 my @clients = grep { ! /^\s*$/ } CGI::param('client');
1904 $ret{clients} = \@clients;
1905 my $str = $self->dbh_join(@clients);
1906 $limit .= "AND Client.Name IN ($str) ";
1910 if ($elt{client_groups}) {
1911 my @clients = grep { ! /^\s*$/ } CGI::param('client_group');
1913 $ret{client_groups} = \@clients;
1914 my $str = $self->dbh_join(@clients);
1915 $limit .= "AND client_group_name IN ($str) ";
1919 if ($elt{filesets}) {
1920 my @filesets = grep { ! /^\s*$/ } CGI::param('fileset');
1922 $ret{filesets} = \@filesets;
1923 my $str = $self->dbh_join(@filesets);
1924 $limit .= "AND FileSet.FileSet IN ($str) ";
1928 if ($elt{mediatypes}) {
1929 my @media = grep { ! /^\s*$/ } CGI::param('mediatype');
1931 $ret{mediatypes} = \@media;
1932 my $str = $self->dbh_join(@media);
1933 $limit .= "AND Media.MediaType IN ($str) ";
1938 my $client = CGI::param('client');
1939 $ret{client} = $client;
1940 $client = $self->dbh_join($client);
1941 $limit .= "AND Client.Name = $client ";
1945 my $level = CGI::param('level') || '';
1946 if ($level =~ /^(\w)$/) {
1948 $limit .= "AND Job.Level = '$1' ";
1953 my $jobid = CGI::param('jobid') || '';
1955 if ($jobid =~ /^(\d+)$/) {
1957 $limit .= "AND Job.JobId = '$1' ";
1962 my $status = CGI::param('status') || '';
1963 if ($status =~ /^(\w)$/) {
1966 $limit .= "AND Job.JobStatus IN ('f','E') ";
1967 } elsif ($1 eq 'W') {
1968 $limit .= "AND Job.JobStatus = 'T' AND Job.JobErrors > 0 ";
1970 $limit .= "AND Job.JobStatus = '$1' ";
1975 if ($elt{volstatus}) {
1976 my $status = CGI::param('volstatus') || '';
1977 if ($status =~ /^(\w+)$/) {
1979 $limit .= "AND Media.VolStatus = '$1' ";
1983 if ($elt{locations}) {
1984 my @location = grep { ! /^\s*$/ } CGI::param('location') ;
1986 $ret{locations} = \@location;
1987 my $str = $self->dbh_join(@location);
1988 $limit .= "AND Location.Location IN ($str) ";
1993 my @pool = grep { ! /^\s*$/ } CGI::param('pool') ;
1995 $ret{pools} = \@pool;
1996 my $str = $self->dbh_join(@pool);
1997 $limit .= "AND Pool.Name IN ($str) ";
2001 if ($elt{location}) {
2002 my $location = CGI::param('location') || '';
2004 $ret{location} = $location;
2005 $location = $self->dbh_quote($location);
2006 $limit .= "AND Location.Location = $location ";
2011 my $pool = CGI::param('pool') || '';
2014 $pool = $self->dbh_quote($pool);
2015 $limit .= "AND Pool.Name = $pool ";
2019 if ($elt{jobtype}) {
2020 my $jobtype = CGI::param('jobtype') || '';
2021 if ($jobtype =~ /^(\w)$/) {
2023 $limit .= "AND Job.Type = '$1' ";
2027 return ($limit, %ret);
2038 my ($self, %arg) = @_ ;
2039 $self->can_do('r_view_job');
2041 $arg{order} = ' Job.JobId DESC ';
2043 my ($limit, $label) = $self->get_limit(%arg);
2044 my ($where, undef) = $self->get_param('clients',
2053 if (CGI::param('client_group')) {
2055 JOIN client_group_member USING (ClientId)
2056 JOIN client_group USING (client_group_id)
2059 my $filter = $self->get_client_filter();
2062 SELECT Job.JobId AS jobid,
2063 Client.Name AS client,
2064 FileSet.FileSet AS fileset,
2065 Job.Name AS jobname,
2067 StartTime AS starttime,
2069 Pool.Name AS poolname,
2070 JobFiles AS jobfiles,
2071 JobBytes AS jobbytes,
2072 JobStatus AS jobstatus,
2073 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
2074 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime))
2077 JobErrors AS joberrors
2079 FROM Client $filter $cgq,
2080 Job LEFT JOIN Pool ON (Job.PoolId = Pool.PoolId)
2081 LEFT JOIN FileSet ON (Job.FileSetId = FileSet.FileSetId)
2082 WHERE Client.ClientId=Job.ClientId
2083 AND Job.JobStatus NOT IN ('R', 'C')
2088 my $all = $self->dbh_selectall_hashref($query, 'jobid') ;
2090 $self->display({ Filter => $label,
2094 sort { $a->{jobid} <=> $b->{jobid} }
2101 # display job informations
2102 sub display_job_zoom
2104 my ($self, $jobid) = @_ ;
2105 $self->can_do('r_view_job');
2107 $jobid = $self->dbh_quote($jobid);
2109 # get security filter
2110 my $filter = $self->get_client_filter();
2113 SELECT DISTINCT Job.JobId AS jobid,
2114 Client.Name AS client,
2115 Job.Name AS jobname,
2116 FileSet.FileSet AS fileset,
2118 Pool.Name AS poolname,
2119 StartTime AS starttime,
2120 JobFiles AS jobfiles,
2121 JobBytes AS jobbytes,
2122 JobStatus AS jobstatus,
2123 JobErrors AS joberrors,
2124 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
2125 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime)) AS duration
2127 FROM Client $filter,
2128 Job LEFT JOIN FileSet ON (Job.FileSetId = FileSet.FileSetId)
2129 LEFT JOIN Pool ON (Job.PoolId = Pool.PoolId)
2130 WHERE Client.ClientId=Job.ClientId
2131 AND Job.JobId = $jobid
2134 my $row = $self->dbh_selectrow_hashref($query) ;
2136 # display all volumes associate with this job
2138 SELECT Media.VolumeName as volumename
2139 FROM Job,Media,JobMedia
2140 WHERE Job.JobId = $jobid
2141 AND JobMedia.JobId=Job.JobId
2142 AND JobMedia.MediaId=Media.MediaId
2145 my $all = $self->dbh_selectall_hashref($query, 'volumename');
2147 $row->{volumes} = [ values %$all ] ;
2149 $self->display($row, "display_job_zoom.tpl");
2152 sub display_job_group
2154 my ($self, %arg) = @_;
2155 $self->can_do('r_view_job');
2157 my ($limit, $label) = $self->get_limit(groupby => 'client_group_name', %arg);
2159 my ($where, undef) = $self->get_param('client_groups',
2162 my $filter = $self->get_client_group_filter();
2165 SELECT client_group_name AS client_group_name,
2166 COALESCE(jobok.jobfiles,0) + COALESCE(joberr.jobfiles,0) AS jobfiles,
2167 COALESCE(jobok.jobbytes,0) + COALESCE(joberr.jobbytes,0) AS jobbytes,
2168 COALESCE(jobok.joberrors,0) + COALESCE(joberr.joberrors,0) AS joberrors,
2169 COALESCE(jobok.nbjobs,0) AS nbjobok,
2170 COALESCE(joberr.nbjobs,0) AS nbjoberr,
2171 COALESCE(jobok.duration, '0:0:0') AS duration
2173 FROM client_group $filter LEFT JOIN (
2174 SELECT client_group_name AS client_group_name, COUNT(1) AS nbjobs,
2175 SUM(JobFiles) AS jobfiles, SUM(JobBytes) AS jobbytes,
2176 SUM(JobErrors) AS joberrors,
2177 SUM($self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
2178 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime)))
2181 FROM Job JOIN client_group_member ON (Job.ClientId = client_group_member.ClientId)
2182 JOIN client_group USING (client_group_id)
2184 WHERE JobStatus = 'T'
2187 ) AS jobok USING (client_group_name) LEFT JOIN
2190 SELECT client_group_name AS client_group_name, COUNT(1) AS nbjobs,
2191 SUM(JobFiles) AS jobfiles, SUM(JobBytes) AS jobbytes,
2192 SUM(JobErrors) AS joberrors
2193 FROM Job JOIN client_group_member ON (Job.ClientId = client_group_member.ClientId)
2194 JOIN client_group USING (client_group_id)
2196 WHERE JobStatus IN ('f','E', 'A')
2199 ) AS joberr USING (client_group_name)
2203 my $all = $self->dbh_selectall_hashref($query, 'client_group_name');
2205 my $rep = { groups => [ values %$all ], age => $arg{age}, filter => $label };
2208 $self->display($rep, "display_job_group.tpl");
2213 my ($self, %arg) = @_ ;
2215 my ($limit, $label) = $self->get_limit(%arg);
2216 my ($where, %elt) = $self->get_param('pools',
2221 my $arg = $self->get_form('jmedias', 'qre_media', 'expired');
2223 if ($arg->{jmedias}) {
2224 $where = "AND Media.VolumeName IN ($arg->{jmedias}) $where";
2226 if ($arg->{qre_media}) {
2227 $where = "AND Media.VolumeName $self->{sql}->{MATCH} $arg->{qre_media} $where";
2229 if ($arg->{expired}) {
2231 AND VolStatus = 'Full'
2232 AND ( $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2233 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2234 ) < NOW() " . $where ;
2238 SELECT Media.VolumeName AS volumename,
2239 Media.VolBytes AS volbytes,
2240 Media.VolStatus AS volstatus,
2241 Media.MediaType AS mediatype,
2242 Media.InChanger AS online,
2243 Media.LastWritten AS lastwritten,
2244 Location.Location AS location,
2245 (volbytes*100/COALESCE(media_avg_size.size,-1)) AS volusage,
2246 Pool.Name AS poolname,
2247 $self->{sql}->{FROM_UNIXTIME}(
2248 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2249 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2252 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2253 LEFT JOIN (SELECT avg(Media.VolBytes) AS size,
2254 Media.MediaType AS MediaType
2256 WHERE Media.VolStatus = 'Full'
2257 GROUP BY Media.MediaType
2258 ) AS media_avg_size ON (Media.MediaType = media_avg_size.MediaType)
2260 WHERE Media.PoolId=Pool.PoolId
2265 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
2267 $self->display({ ID => $cur_id++,
2269 Location => $elt{location},
2270 Media => [ values %$all ],
2272 "display_media.tpl");
2275 sub display_allmedia
2279 my $pool = $self->get_form('db_pools');
2281 foreach my $name (@{ $pool->{db_pools} }) {
2282 CGI::param('pool', $name->{name});
2283 $self->display_media();
2287 sub display_media_zoom
2291 my $media = $self->get_form('jmedias');
2293 unless ($media->{jmedias}) {
2294 return $self->error("Can't get media selection");
2298 SELECT InChanger AS online,
2299 Media.Enabled AS enabled,
2300 VolBytes AS nb_bytes,
2301 VolumeName AS volumename,
2302 VolStatus AS volstatus,
2303 VolMounts AS nb_mounts,
2304 Media.VolUseDuration AS voluseduration,
2305 Media.MaxVolJobs AS maxvoljobs,
2306 Media.MaxVolFiles AS maxvolfiles,
2307 Media.MaxVolBytes AS maxvolbytes,
2308 VolErrors AS nb_errors,
2309 Pool.Name AS poolname,
2310 Location.Location AS location,
2311 Media.Recycle AS recycle,
2312 Media.VolRetention AS volretention,
2313 Media.LastWritten AS lastwritten,
2314 Media.VolReadTime/1000000 AS volreadtime,
2315 Media.VolWriteTime/1000000 AS volwritetime,
2316 Media.RecycleCount AS recyclecount,
2317 Media.Comment AS comment,
2318 $self->{sql}->{FROM_UNIXTIME}(
2319 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2320 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2323 Media LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2324 WHERE Pool.PoolId = Media.PoolId
2325 AND VolumeName IN ($media->{jmedias})
2328 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
2330 foreach my $media (values %$all) {
2331 my $mq = $self->dbh_quote($media->{volumename});
2334 SELECT DISTINCT Job.JobId AS jobid,
2336 Job.StartTime AS starttime,
2339 Job.JobFiles AS files,
2340 Job.JobBytes AS bytes,
2341 Job.jobstatus AS status
2342 FROM Media,JobMedia,Job
2343 WHERE Media.VolumeName=$mq
2344 AND Media.MediaId=JobMedia.MediaId
2345 AND JobMedia.JobId=Job.JobId
2348 my $jobs = $self->dbh_selectall_hashref($query, 'jobid') ;
2351 SELECT LocationLog.Date AS date,
2352 Location.Location AS location,
2353 LocationLog.Comment AS comment
2354 FROM Media,LocationLog INNER JOIN Location ON (LocationLog.LocationId = Location.LocationId)
2355 WHERE Media.MediaId = LocationLog.MediaId
2356 AND Media.VolumeName = $mq
2360 my $log = $self->dbh_selectall_arrayref($query) ;
2362 $logtxt = join("\n", map { ($_->[0] . ' ' . $_->[1] . ' ' . $_->[2])} @$log ) ;
2365 $self->display({ jobs => [ values %$jobs ],
2366 LocationLog => $logtxt,
2368 "display_media_zoom.tpl");
2375 $self->can_do('r_location_mgnt');
2377 my $loc = $self->get_form('qlocation');
2378 unless ($loc->{qlocation}) {
2379 return $self->error("Can't get location");
2383 SELECT Location.Location AS location,
2384 Location.Cost AS cost,
2385 Location.Enabled AS enabled
2387 WHERE Location.Location = $loc->{qlocation}
2390 my $row = $self->dbh_selectrow_hashref($query);
2391 $row->{enabled} = human_enabled($row->{enabled});
2392 $self->display({ ID => $cur_id++,
2393 %$row }, "location_edit.tpl") ;
2399 $self->can_do('r_location_mgnt');
2401 my $arg = $self->get_form(qw/qlocation qnewlocation cost enabled/) ;
2402 unless ($arg->{qlocation}) {
2403 return $self->error("Can't get location");
2405 unless ($arg->{qnewlocation}) {
2406 return $self->error("Can't get new location name");
2408 unless ($arg->{cost}) {
2409 return $self->error("Can't get new cost");
2412 my $enabled = from_human_enabled($arg->{enabled});
2415 UPDATE Location SET Cost = $arg->{cost},
2416 Location = $arg->{qnewlocation},
2418 WHERE Location.Location = $arg->{qlocation}
2421 $self->dbh_do($query);
2423 $self->location_display();
2429 $self->can_do('r_location_mgnt');
2431 my $arg = $self->get_form(qw/qlocation/) ;
2433 unless ($arg->{qlocation}) {
2434 return $self->error("Can't get location");
2438 SELECT count(Media.MediaId) AS nb
2439 FROM Media INNER JOIN Location USING (LocationID)
2440 WHERE Location = $arg->{qlocation}
2443 my $res = $self->dbh_selectrow_hashref($query);
2446 return $self->error("Sorry, the location must be empty");
2450 DELETE FROM Location WHERE Location = $arg->{qlocation} LIMIT 1
2453 $self->dbh_do($query);
2455 $self->location_display();
2461 $self->can_do('r_location_mgnt');
2463 my $arg = $self->get_form(qw/qlocation cost/) ;
2465 unless ($arg->{qlocation}) {
2466 $self->display({}, "location_add.tpl");
2469 unless ($arg->{cost}) {
2470 return $self->error("Can't get new cost");
2473 my $enabled = CGI::param('enabled') || '';
2474 $enabled = $enabled?1:0;
2477 INSERT INTO Location (Location, Cost, Enabled)
2478 VALUES ($arg->{qlocation}, $arg->{cost}, $enabled)
2481 $self->dbh_do($query);
2483 $self->location_display();
2486 sub location_display
2491 SELECT Location.Location AS location,
2492 Location.Cost AS cost,
2493 Location.Enabled AS enabled,
2494 (SELECT count(Media.MediaId)
2496 WHERE Media.LocationId = Location.LocationId
2501 my $location = $self->dbh_selectall_hashref($query, 'location');
2503 $self->display({ ID => $cur_id++,
2504 Locations => [ values %$location ] },
2505 "display_location.tpl");
2512 my $media = $self->get_selected_media_location();
2517 my $arg = $self->get_form('db_locations', 'qnewlocation');
2519 $self->display({ email => $self->{info}->{email_media},
2521 media => [ values %$media ],
2523 "update_location.tpl");
2526 ###########################################################
2531 $self->can_do('r_group_mgnt');
2533 my $grp = $self->get_form(qw/qclient_group db_clients/);
2535 unless ($grp->{qclient_group}) {
2536 return $self->error("Can't get group");
2541 FROM Client JOIN client_group_member using (clientid)
2542 JOIN client_group using (client_group_id)
2543 WHERE client_group_name = $grp->{qclient_group}
2546 my $row = $self->dbh_selectall_hashref($query, "name");
2548 $self->display({ ID => $cur_id++,
2549 client_group => $grp->{qclient_group},
2551 client_group_member => [ values %$row]},
2558 $self->can_do('r_group_mgnt');
2560 my $arg = $self->get_form(qw/qclient_group jclients qnewgroup/);
2561 unless ($arg->{qclient_group}) {
2562 return $self->error("Can't get groups");
2565 $self->{dbh}->begin_work();
2568 DELETE FROM client_group_member
2569 WHERE client_group_id IN
2570 (SELECT client_group_id
2572 WHERE client_group_name = $arg->{qclient_group})
2574 $self->dbh_do($query);
2577 INSERT INTO client_group_member (clientid, client_group_id)
2579 (SELECT client_group_id
2581 WHERE client_group_name = $arg->{qclient_group})
2582 FROM Client WHERE Name IN ($arg->{jclients})
2585 $self->dbh_do($query);
2587 if ($arg->{qclient_group} ne $arg->{qnewgroup}) {
2590 SET client_group_name = $arg->{qnewgroup}
2591 WHERE client_group_name = $arg->{qclient_group}
2594 $self->dbh_do($query);
2597 $self->{dbh}->commit() or $self->error($self->{dbh}->errstr);
2599 $self->display_groups();
2605 $self->can_do('r_group_mgnt');
2607 my $arg = $self->get_form(qw/qclient_group/);
2609 unless ($arg->{qclient_group}) {
2610 return $self->error("Can't get groups");
2613 $self->{dbh}->begin_work();
2616 DELETE FROM client_group_member
2617 WHERE client_group_id IN
2618 (SELECT client_group_id
2620 WHERE client_group_name = $arg->{qclient_group});
2622 DELETE FROM bweb_client_group_acl
2623 WHERE client_group_id IN
2624 (SELECT client_group_id
2626 WHERE client_group_name = $arg->{qclient_group});
2628 DELETE FROM client_group
2629 WHERE client_group_name = $arg->{qclient_group};
2631 $self->dbh_do($query);
2633 $self->{dbh}->commit();
2635 $self->display_groups();
2642 $self->can_do('r_group_mgnt');
2644 my $arg = $self->get_form(qw/qclient_group/) ;
2646 unless ($arg->{qclient_group}) {
2647 $self->display({}, "groups_add.tpl");
2652 INSERT INTO client_group (client_group_name)
2653 VALUES ($arg->{qclient_group})
2656 $self->dbh_do($query);
2658 $self->display_groups();
2665 my $arg = $self->get_form(qw/db_client_groups/) ;
2667 if ($self->{dbh}->errstr) {
2668 return $self->error("Can't use groups with bweb, read INSTALL to enable them");
2673 $self->display({ ID => $cur_id++,
2675 "display_groups.tpl");
2678 ###########################################################
2683 if (not $self->{info}->{enable_security}) {
2686 # admin is a special user that can do everything
2687 if ($self->{loginname} eq 'admin') {
2690 if (!$self->{loginname}) {
2694 if (defined $self->{security}) {
2697 $self->{security} = {};
2698 my $u = $self->dbh_quote($self->{loginname});
2701 SELECT use_acl, rolename
2703 JOIN bweb_role_member USING (userid)
2704 JOIN bweb_role USING (roleid)
2707 my $rows = $self->dbh_selectall_arrayref($query);
2708 # do cache with this role
2712 foreach my $r (@$rows) {
2713 $self->{security}->{$r->[1]}=1;
2716 $self->{security}->{use_acl} = $rows->[0]->[0];
2720 # TODO: avoir un mode qui coupe le programme avec une page d'erreur
2721 # we can also get all security and fill {security} hash
2724 my ($self, $action) = @_;
2725 # is security enabled in configuration ?
2726 if (not $self->{info}->{enable_security}) {
2729 # admin is a special user that can do everything
2730 if ($self->{loginname} eq 'admin') {
2734 if (!$self->{loginname}) {
2735 $self->error("Can't do $action, your are not logged. " .
2736 "Check security with your administrator");
2737 $self->display_end();
2741 if (!$self->{security}->{$action}) {
2742 $self->error("$self->{loginname} sorry, but this action ($action) " .
2743 "is not permited. " .
2744 "Check security with your administrator");
2745 $self->display_end();
2755 if (!$self->{info}->{enable_security} or
2756 !$self->{info}->{enable_security_acl})
2761 if ($self->get_roles()) {
2762 return $self->{security}->{use_acl};
2768 # JOIN Client USING (ClientId) " . $b->get_client_filter() . "
2769 sub get_client_filter
2772 if ($self->use_filter()) {
2773 my $u = $self->dbh_quote($self->{loginname});
2775 JOIN (SELECT ClientId FROM client_group_member
2776 JOIN client_group USING (client_group_id)
2777 JOIN bweb_client_group_acl USING (client_group_id)
2778 JOIN bweb_user USING (userid)
2779 WHERE bweb_user.username = $u
2780 ) AS filter USING (ClientId)";
2786 #JOIN client_group USING (client_group_id)" . $b->get_client_group_filter()
2787 sub get_client_group_filter
2790 if ($self->use_filter()) {
2791 my $u = $self->dbh_quote($self->{loginname});
2793 JOIN (SELECT client_group_id
2794 FROM bweb_client_group_acl
2795 JOIN bweb_user USING (userid)
2796 WHERE bweb_user.username = $u
2797 ) AS filter USING (client_group_id)";
2803 # role and username have to be quoted before
2804 # role and username can be a quoted list
2807 my ($self, $role, $username) = @_;
2808 $self->can_do("r_user_mgnt");
2810 my $nb = $self->dbh_do("
2811 DELETE FROM bweb_role_member
2812 WHERE roleid = (SELECT roleid FROM bweb_role
2813 WHERE rolename IN ($role))
2814 AND userid = (SELECT userid FROM bweb_user
2815 WHERE username IN ($username))");
2819 # role and username have to be quoted before
2820 # role and username can be a quoted list
2823 my ($self, $role, $username) = @_;
2824 $self->can_do("r_user_mgnt");
2826 my $nb = $self->dbh_do("
2827 INSERT INTO bweb_role_member (roleid, userid)
2828 SELECT roleid, userid FROM bweb_role, bweb_user
2829 WHERE rolename IN ($role)
2830 AND username IN ($username)
2835 # role and username have to be quoted before
2836 # role and username can be a quoted list
2839 my ($self, $copy, $user) = @_;
2840 $self->can_do("r_user_mgnt");
2842 my $nb = $self->dbh_do("
2843 INSERT INTO bweb_role_member (roleid, userid)
2844 SELECT roleid, a.userid
2845 FROM bweb_user AS a, bweb_role_member
2846 JOIN bweb_user USING (userid)
2847 WHERE bweb_user.username = $copy
2848 AND a.username = $user");
2852 # username can be a join quoted list of usernames
2855 my ($self, $username) = @_;
2856 $self->can_do("r_user_mgnt");
2859 DELETE FROM bweb_role_member
2863 WHERE username in ($username))");
2865 DELETE FROM bweb_client_group_acl
2869 WHERE username IN ($username))");
2876 $self->can_do("r_user_mgnt");
2878 my $arg = $self->get_form(qw/jusernames/);
2880 unless ($arg->{jusernames}) {
2881 return $self->error("Can't get user");
2884 $self->{dbh}->begin_work();
2886 $self->revoke_all($arg->{jusernames});
2888 DELETE FROM bweb_user WHERE username IN ($arg->{jusernames})");
2890 $self->{dbh}->commit();
2892 $self->display_users();
2898 $self->can_do("r_user_mgnt");
2900 # we don't quote username directly to check that it is conform
2901 my $arg = $self->get_form(qw/username qpasswd qcomment jrolenames qcreate qcopy_username jclient_groups/) ;
2903 if (not $arg->{qcreate}) {
2904 $arg = $self->get_form(qw/db_roles db_usernames db_client_groups/);
2905 $self->display($arg, "display_user.tpl");
2909 my $u = $self->dbh_quote($arg->{username});
2911 $arg->{use_acl}=(CGI::param('use_acl')?'true':'false');
2913 if (!$arg->{qpasswd}) {
2914 $arg->{qpasswd} = "''";
2916 if (!$arg->{qcomment}) {
2917 $arg->{qcomment} = "''";
2920 # will fail if user already exists
2923 SET passwd=$arg->{qpasswd}, comment=$arg->{qcomment},
2924 use_acl=$arg->{use_acl}
2925 WHERE username = $u")
2928 INSERT INTO bweb_user (username, passwd, use_acl, comment)
2929 VALUES ($u, $arg->{qpasswd}, $arg->{use_acl}, $arg->{qcomment})");
2931 $self->{dbh}->begin_work();
2933 $self->revoke_all($u);
2935 if ($arg->{qcopy_username}) {
2936 $self->grant_like($arg->{qcopy_username}, $u);
2938 $self->grant($arg->{jrolenames}, $u);
2942 INSERT INTO bweb_client_group_acl (client_group_id, userid)
2943 SELECT client_group_id, userid
2944 FROM client_group, bweb_user
2945 WHERE client_group_name IN ($arg->{jclient_groups})
2950 $self->{dbh}->commit();
2952 $self->display_users();
2955 # TODO: we miss a matrix with all user/roles
2959 $self->can_do("r_user_mgnt");
2961 my $arg = $self->get_form(qw/db_usernames/) ;
2963 if ($self->{dbh}->errstr) {
2964 return $self->error("Can't use users with bweb, read INSTALL to enable them");
2967 $self->display({ ID => $cur_id++,
2969 "display_users.tpl");
2975 $self->can_do("r_user_mgnt");
2977 my $arg = $self->get_form('username');
2978 my $user = $self->dbh_quote($arg->{username});
2980 my $userp = $self->dbh_selectrow_hashref("
2981 SELECT username, passwd, comment, use_acl
2983 WHERE username = $user
2987 return $self->error("Can't find $user in catalog");
2989 $arg = $self->get_form(qw/db_usernames db_client_groups/);
2990 my $arg2 = $self->get_form(qw/filter db_client_groups/);
2993 #------------+--------
2998 my $role = $self->dbh_selectall_hashref("
2999 SELECT rolename, temp.userid
3001 LEFT JOIN (SELECT roleid, userid
3002 FROM bweb_user JOIN bweb_role_member USING (userid)
3003 WHERE username = $user) AS temp USING (roleid)
3008 db_usernames => $arg->{db_usernames},
3009 username => $userp->{username},
3010 comment => $userp->{comment},
3011 passwd => $userp->{passwd},
3012 use_acl => $userp->{use_acl},
3013 db_client_groups => $arg->{db_client_groups},
3014 client_group => $arg2->{db_client_groups},
3015 db_roles => [ values %$role],
3016 }, "display_user.tpl");
3020 ###########################################################
3022 sub get_media_max_size
3024 my ($self, $type) = @_;
3026 "SELECT avg(VolBytes) AS size
3028 WHERE Media.VolStatus = 'Full'
3029 AND Media.MediaType = '$type'
3032 my $res = $self->selectrow_hashref($query);
3035 return $res->{size};
3045 my $media = $self->get_form('qmedia');
3047 unless ($media->{qmedia}) {
3048 return $self->error("Can't get media");
3052 SELECT Media.Slot AS slot,
3053 PoolMedia.Name AS poolname,
3054 Media.VolStatus AS volstatus,
3055 Media.InChanger AS inchanger,
3056 Location.Location AS location,
3057 Media.VolumeName AS volumename,
3058 Media.MaxVolBytes AS maxvolbytes,
3059 Media.MaxVolJobs AS maxvoljobs,
3060 Media.MaxVolFiles AS maxvolfiles,
3061 Media.VolUseDuration AS voluseduration,
3062 Media.VolRetention AS volretention,
3063 Media.Comment AS comment,
3064 PoolRecycle.Name AS poolrecycle,
3065 Media.Enabled AS enabled
3067 FROM Media INNER JOIN Pool AS PoolMedia ON (Media.PoolId = PoolMedia.PoolId)
3068 LEFT JOIN Pool AS PoolRecycle ON (Media.RecyclePoolId = PoolRecycle.PoolId)
3069 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
3071 WHERE Media.VolumeName = $media->{qmedia}
3074 my $row = $self->dbh_selectrow_hashref($query);
3075 $row->{volretention} = human_sec($row->{volretention});
3076 $row->{voluseduration} = human_sec($row->{voluseduration});
3077 $row->{enabled} = human_enabled($row->{enabled});
3079 my $elt = $self->get_form(qw/db_pools db_locations/);
3084 }, "update_media.tpl");
3090 $self->can_do('r_media_mgnt');
3092 my $arg = $self->get_form('jmedias', 'qnewlocation') ;
3094 unless ($arg->{jmedias}) {
3095 return $self->error("Can't get selected media");
3098 unless ($arg->{qnewlocation}) {
3099 return $self->error("Can't get new location");
3104 SET LocationId = (SELECT LocationId
3106 WHERE Location = $arg->{qnewlocation})
3107 WHERE Media.VolumeName IN ($arg->{jmedias})
3110 my $nb = $self->dbh_do($query);
3112 print "$nb media updated, you may have to update your autochanger.";
3114 $self->display_media();
3120 $self->can_do('r_media_mgnt');
3122 my $media = $self->get_selected_media_location();
3124 return $self->error("Can't get media selection");
3126 my $newloc = CGI::param('newlocation');
3128 my $user = CGI::param('user') || 'unknown';
3129 my $comm = CGI::param('comment') || '';
3130 $comm = $self->dbh_quote("$user: $comm");
3132 my $arg = $self->get_form('enabled');
3133 my $en = human_enabled($arg->{enabled});
3134 my $b = $self->get_bconsole();
3137 foreach my $vol (keys %$media) {
3139 INSERT LocationLog (Date, Comment, MediaId, LocationId, NewVolStatus)
3141 NOW(), $comm, (SELECT MediaId FROM Media WHERE VolumeName = '$vol'),
3142 (SELECT LocationId FROM Location WHERE Location = '$media->{$vol}->{location}'),
3143 (SELECT VolStatus FROM Media WHERE VolumeName = '$vol')
3146 $self->dbh_do($query);
3147 $self->debug($query);
3148 $b->send_cmd("update volume=\"$vol\" enabled=$en");
3153 $q->param('action', 'update_location');
3154 my $url = $q->url(-full => 1, -query=>1);
3156 $self->display({ email => $self->{info}->{email_media},
3158 newlocation => $newloc,
3159 # [ { volumename => 'vol1' }, { volumename => 'vol2'},..]
3160 media => [ values %$media ],
3162 "change_location.tpl");
3166 sub display_client_stats
3168 my ($self, %arg) = @_ ;
3169 $self->can_do('r_view_stats');
3171 my $client = $self->dbh_quote($arg{clientname});
3172 # get security filter
3173 my $filter = $self->get_client_filter();
3175 my ($limit, $label) = $self->get_limit(%arg);
3178 count(Job.JobId) AS nb_jobs,
3179 sum(Job.JobBytes) AS nb_bytes,
3180 sum(Job.JobErrors) AS nb_err,
3181 sum(Job.JobFiles) AS nb_files,
3182 Client.Name AS clientname
3183 FROM Job JOIN Client USING (ClientId) $filter
3185 Client.Name = $client
3187 GROUP BY Client.Name
3190 my $row = $self->dbh_selectrow_hashref($query);
3192 $row->{ID} = $cur_id++;
3193 $row->{label} = $label;
3194 $row->{grapharg} = "client";
3196 $self->display($row, "display_client_stats.tpl");
3200 sub display_group_stats
3202 my ($self, %arg) = @_ ;
3204 my $carg = $self->get_form(qw/qclient_group/);
3206 unless ($carg->{qclient_group}) {
3207 return $self->error("Can't get group");
3210 my ($limit, $label) = $self->get_limit(%arg);
3214 count(Job.JobId) AS nb_jobs,
3215 sum(Job.JobBytes) AS nb_bytes,
3216 sum(Job.JobErrors) AS nb_err,
3217 sum(Job.JobFiles) AS nb_files,
3218 client_group.client_group_name AS clientname
3219 FROM Job JOIN Client USING (ClientId)
3220 JOIN client_group_member ON (Client.ClientId = client_group_member.clientid)
3221 JOIN client_group USING (client_group_id)
3223 client_group.client_group_name = $carg->{qclient_group}
3225 GROUP BY client_group.client_group_name
3228 my $row = $self->dbh_selectrow_hashref($query);
3230 $row->{ID} = $cur_id++;
3231 $row->{label} = $label;
3232 $row->{grapharg} = "client_group";
3234 $self->display($row, "display_client_stats.tpl");
3237 # poolname can be undef
3240 my ($self, $poolname) = @_ ;
3244 my $arg = $self->get_form('jmediatypes', 'qmediatypes');
3245 if ($arg->{jmediatypes}) {
3246 $whereW = "WHERE MediaType IN ($arg->{jmediatypes}) ";
3247 $whereA = "AND MediaType IN ($arg->{jmediatypes}) ";
3250 # TODO : afficher les tailles et les dates
3253 SELECT subq.volmax AS volmax,
3254 subq.volnum AS volnum,
3255 subq.voltotal AS voltotal,
3257 Pool.Recycle AS recycle,
3258 Pool.VolRetention AS volretention,
3259 Pool.VolUseDuration AS voluseduration,
3260 Pool.MaxVolJobs AS maxvoljobs,
3261 Pool.MaxVolFiles AS maxvolfiles,
3262 Pool.MaxVolBytes AS maxvolbytes,
3263 subq.PoolId AS PoolId,
3264 subq.MediaType AS mediatype,
3265 $self->{sql}->{CAT_POOL_TYPE} AS uniq
3268 SELECT COALESCE(media_avg_size.volavg,0) * count(Media.MediaId) AS volmax,
3269 count(Media.MediaId) AS volnum,
3270 sum(Media.VolBytes) AS voltotal,
3271 Media.PoolId AS PoolId,
3272 Media.MediaType AS MediaType
3274 LEFT JOIN (SELECT avg(Media.VolBytes) AS volavg,
3275 Media.MediaType AS MediaType
3277 WHERE Media.VolStatus = 'Full'
3278 GROUP BY Media.MediaType
3279 ) AS media_avg_size ON (Media.MediaType = media_avg_size.MediaType)
3280 GROUP BY Media.MediaType, Media.PoolId, media_avg_size.volavg
3282 LEFT JOIN Pool ON (Pool.PoolId = subq.PoolId)
3286 my $all = $self->dbh_selectall_hashref($query, 'uniq') ;
3289 SELECT Pool.Name AS name,
3290 sum(VolBytes) AS size
3291 FROM Media JOIN Pool ON (Media.PoolId = Pool.PoolId)
3292 WHERE Media.VolStatus IN ('Recycled', 'Purged')
3296 my $empty = $self->dbh_selectall_hashref($query, 'name');
3298 foreach my $p (values %$all) {
3299 if ($p->{volmax} > 0) { # mysql returns 0.0000
3300 # we remove Recycled/Purged media from pool usage
3301 if (defined $empty->{$p->{name}}) {
3302 $p->{voltotal} -= $empty->{$p->{name}}->{size};
3304 $p->{poolusage} = sprintf('%.2f', $p->{voltotal} * 100/ $p->{volmax}) ;
3306 $p->{poolusage} = 0;
3310 SELECT VolStatus AS volstatus, count(MediaId) AS nb
3312 WHERE PoolId=$p->{poolid}
3313 AND Media.MediaType = '$p->{mediatype}'
3317 my $content = $self->dbh_selectall_hashref($query, 'volstatus');
3318 foreach my $t (values %$content) {
3319 $p->{"nb_" . $t->{volstatus}} = $t->{nb} ;
3324 $self->display({ ID => $cur_id++,
3325 MediaType => $arg->{qmediatypes}, # [ { name => type1 } , { name => type2 } ]
3326 Pools => [ values %$all ]},
3327 "display_pool.tpl");
3330 sub display_running_job
3333 $self->can_do('r_view_running_job');
3335 my $arg = $self->get_form('client', 'jobid');
3337 if (!$arg->{client} and $arg->{jobid}) {
3338 # get security filter
3339 my $filter = $self->get_client_filter();
3342 SELECT Client.Name AS name
3343 FROM Job INNER JOIN Client USING (ClientId) $filter
3344 WHERE Job.JobId = $arg->{jobid}
3347 my $row = $self->dbh_selectrow_hashref($query);
3350 $arg->{client} = $row->{name};
3351 CGI::param('client', $arg->{client});
3355 if ($arg->{client}) {
3356 my $cli = new Bweb::Client(name => $arg->{client});
3357 $cli->display_running_job($self->{info}, $arg->{jobid});
3358 if ($arg->{jobid}) {
3359 $self->get_job_log();
3362 $self->error("Can't get client or jobid");
3366 sub display_running_jobs
3368 my ($self, $display_action) = @_;
3369 $self->can_do('r_view_running_job');
3371 # get security filter
3372 my $filter = $self->get_client_filter();
3375 SELECT Job.JobId AS jobid,
3376 Job.Name AS jobname,
3378 Job.StartTime AS starttime,
3379 Job.JobFiles AS jobfiles,
3380 Job.JobBytes AS jobbytes,
3381 Job.JobStatus AS jobstatus,
3382 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(NOW())
3383 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime))
3385 Client.Name AS clientname
3386 FROM Job INNER JOIN Client USING (ClientId) $filter
3388 JobStatus IN ('C','R','B','e','D','F','S','m','M','s','j','c','d','t','p')
3390 my $all = $self->dbh_selectall_hashref($query, 'jobid') ;
3392 $self->display({ ID => $cur_id++,
3393 display_action => $display_action,
3394 Jobs => [ values %$all ]},
3395 "running_job.tpl") ;
3398 # return the autochanger list to update
3402 $self->can_do('r_media_mgnt');
3405 my $arg = $self->get_form('jmedias');
3407 unless ($arg->{jmedias}) {
3408 return $self->error("Can't get media selection");
3412 SELECT Media.VolumeName AS volumename,
3413 Storage.Name AS storage,
3414 Location.Location AS location,
3416 FROM Media INNER JOIN Storage ON (Media.StorageId = Storage.StorageId)
3417 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
3418 WHERE Media.VolumeName IN ($arg->{jmedias})
3419 AND Media.InChanger = 1
3422 my $all = $self->dbh_selectall_hashref($query, 'volumename');
3424 foreach my $vol (values %$all) {
3425 my $a = $self->ach_get($vol->{location});
3427 $ret{$vol->{location}} = 1;
3429 unless ($a->{have_status}) {
3431 $a->{have_status} = 1;
3434 print "eject $vol->{volumename} from $vol->{storage} : ";
3435 if ($a->send_to_io($vol->{slot})) {
3436 print "<img src='/bweb/T.png' alt='ok'><br/>";
3438 print "<img src='/bweb/E.png' alt='err'><br/>";
3448 my ($to, $subject, $content) = (CGI::param('email'),
3449 CGI::param('subject'),
3450 CGI::param('content'));
3451 $to =~ s/[^\w\d\.\@<>,]//;
3452 $subject =~ s/[^\w\d\.\[\]]/ /;
3454 open(MAIL, "|mail -s '$subject' '$to'") ;
3455 print MAIL $content;
3465 my $arg = $self->get_form('jobid', 'client');
3467 print CGI::header('text/brestore');
3468 print "jobid=$arg->{jobid}\n" if ($arg->{jobid});
3469 print "client=$arg->{client}\n" if ($arg->{client});
3470 print "\n\nYou have to assign this mime type with /usr/bin/brestore.pl\n";
3474 # TODO : move this to Bweb::Autochanger ?
3475 # TODO : make this internal to not eject tape ?
3481 my ($self, $name) = @_;
3484 return $self->error("Can't get your autochanger name ach");
3487 unless ($self->{info}->{ach_list}) {
3488 return $self->error("Could not find any autochanger");
3491 my $a = $self->{info}->{ach_list}->{$name};
3494 $self->error("Can't get your autochanger $name from your ach_list");
3499 $a->{debug} = $self->{debug};
3506 my ($self, $ach) = @_;
3507 $self->can_do('r_configure');
3509 $self->{info}->{ach_list}->{$ach->{name}} = $ach;
3511 $self->{info}->save();
3519 $self->can_do('r_configure');
3521 my $arg = $self->get_form('ach');
3523 or !$self->{info}->{ach_list}
3524 or !$self->{info}->{ach_list}->{$arg->{ach}})
3526 return $self->error("Can't get autochanger name");
3529 my $ach = $self->{info}->{ach_list}->{$arg->{ach}};
3533 [ map { { name => $_, index => $i++ } } @{$ach->{drive_name}} ] ;
3535 my $b = $self->get_bconsole();
3537 my @storages = $b->list_storage() ;
3539 $ach->{devices} = [ map { { name => $_ } } @storages ];
3541 $self->display($ach, "ach_add.tpl");
3542 delete $ach->{drives};
3543 delete $ach->{devices};
3550 $self->can_do('r_configure');
3552 my $arg = $self->get_form('ach');
3555 or !$self->{info}->{ach_list}
3556 or !$self->{info}->{ach_list}->{$arg->{ach}})
3558 return $self->error("Can't get autochanger name");
3561 delete $self->{info}->{ach_list}->{$arg->{ach}} ;
3563 $self->{info}->save();
3564 $self->{info}->view();
3570 $self->can_do('r_configure');
3572 my $arg = $self->get_form('ach', 'mtxcmd', 'device', 'precmd');
3574 my $b = $self->get_bconsole();
3575 my @storages = $b->list_storage() ;
3577 unless ($arg->{ach}) {
3578 $arg->{devices} = [ map { { name => $_ } } @storages ];
3579 return $self->display($arg, "ach_add.tpl");
3583 foreach my $drive (CGI::param('drives'))
3585 unless (grep(/^$drive$/,@storages)) {
3586 return $self->error("Can't find $drive in storage list");
3589 my $index = CGI::param("index_$drive");
3590 unless (defined $index and $index =~ /^(\d+)$/) {
3591 return $self->error("Can't get $drive index");
3594 $drives[$index] = $drive;
3598 return $self->error("Can't get drives from Autochanger");
3601 my $a = new Bweb::Autochanger(name => $arg->{ach},
3602 precmd => $arg->{precmd},
3603 drive_name => \@drives,
3604 device => $arg->{device},
3605 mtxcmd => $arg->{mtxcmd});
3607 $self->ach_register($a) ;
3609 $self->{info}->view();
3615 $self->can_do('r_delete_job');
3617 my $arg = $self->get_form('jobid');
3619 if ($arg->{jobid}) {
3620 my $b = $self->get_bconsole();
3621 my $ret = $b->send_cmd("delete jobid=\"$arg->{jobid}\"");
3625 title => "Delete a job ",
3626 name => "delete jobid=$arg->{jobid}",
3634 $self->can_do('r_media_mgnt');
3636 my $arg = $self->get_form(qw/media volstatus inchanger pool
3637 slot volretention voluseduration
3638 maxvoljobs maxvolfiles maxvolbytes
3639 qcomment poolrecycle enabled
3642 unless ($arg->{media}) {
3643 return $self->error("Can't find media selection");
3646 my $update = "update volume=$arg->{media} ";
3648 if ($arg->{volstatus}) {
3649 $update .= " volstatus=$arg->{volstatus} ";
3652 if ($arg->{inchanger}) {
3653 $update .= " inchanger=yes " ;
3655 $update .= " slot=$arg->{slot} ";
3658 $update .= " slot=0 inchanger=no ";
3661 if ($arg->{enabled}) {
3662 $update .= " enabled=$arg->{enabled} ";
3666 $update .= " pool=$arg->{pool} " ;
3669 if (defined $arg->{volretention}) {
3670 $update .= " volretention=\"$arg->{volretention}\" " ;
3673 if (defined $arg->{voluseduration}) {
3674 $update .= " voluse=\"$arg->{voluseduration}\" " ;
3677 if (defined $arg->{maxvoljobs}) {
3678 $update .= " maxvoljobs=$arg->{maxvoljobs} " ;
3681 if (defined $arg->{maxvolfiles}) {
3682 $update .= " maxvolfiles=$arg->{maxvolfiles} " ;
3685 if (defined $arg->{maxvolbytes}) {
3686 $update .= " maxvolbytes=$arg->{maxvolbytes} " ;
3689 if (defined $arg->{poolrecycle}) {
3690 $update .= " recyclepool=\"$arg->{poolrecycle}\" " ;
3693 my $b = $self->get_bconsole();
3696 content => $b->send_cmd($update),
3697 title => "Update a volume ",
3703 my $media = $self->dbh_quote($arg->{media});
3705 my $loc = CGI::param('location') || '';
3707 $loc = $self->dbh_quote($loc); # is checked by db
3708 push @q, "LocationId=(SELECT LocationId FROM Location WHERE Location=$loc)";
3710 if (!$arg->{qcomment}) {
3711 $arg->{qcomment} = "''";
3713 push @q, "Comment=$arg->{qcomment}";
3718 SET " . join (',', @q) . "
3719 WHERE Media.VolumeName = $media
3721 $self->dbh_do($query);
3723 $self->update_media();
3729 $self->can_do('r_autochanger_mgnt');
3731 my $ach = CGI::param('ach') ;
3732 $ach = $self->ach_get($ach);
3734 return $self->error("Bad autochanger name");
3738 my $b = new Bconsole(pref => $self->{info},timeout => 60,log_stdout => 1);
3739 $b->update_slots($ach->{name});
3746 $self->can_do('r_view_log');
3748 my $arg = $self->get_form('jobid', 'limit', 'offset');
3749 unless ($arg->{jobid}) {
3750 return $self->error("Can't get jobid");
3753 if ($arg->{limit} == 100) {
3754 $arg->{limit} = 1000;
3756 # get security filter
3757 my $filter = $self->get_client_filter();
3760 SELECT Job.Name as name, Client.Name as clientname
3761 FROM Job INNER JOIN Client USING (ClientId) $filter
3762 WHERE JobId = $arg->{jobid}
3765 my $row = $self->dbh_selectrow_hashref($query);
3768 return $self->error("Can't find $arg->{jobid} in catalog");
3771 # display only Error and Warning messages
3773 if (CGI::param('error')) {
3774 $filter = " AND LogText $self->{sql}->{MATCH} 'Error|Warning' ";
3778 if (CGI::param('time') || $self->{info}->{display_log_time}) {
3779 $logtext = 'LogText';
3781 $logtext = $self->dbh_strcat('Time', ' ', 'LogText')
3785 SELECT count(1) AS nbline, JobId AS jobid,
3786 GROUP_CONCAT($logtext $self->{sql}->{CONCAT_SEP}) AS logtxt
3788 SELECT JobId, Time, LogText
3790 WHERE ( Log.JobId = $arg->{jobid}
3792 AND Time >= (SELECT StartTime FROM Job WHERE JobId=$arg->{jobid})
3793 AND Time <= (SELECT COALESCE(EndTime,NOW()) FROM Job WHERE JobId=$arg->{jobid})
3797 OFFSET $arg->{offset}
3803 my $log = $self->dbh_selectrow_hashref($query);
3805 return $self->error("Can't get log for jobid $arg->{jobid}");
3808 $self->display({ lines=> $log->{logtxt},
3809 nbline => $log->{nbline},
3810 jobid => $arg->{jobid},
3811 name => $row->{name},
3812 client => $row->{clientname},
3813 offset => $arg->{offset},
3814 limit => $arg->{limit},
3815 }, 'display_log.tpl');
3821 $self->can_do('r_autochanger_mgnt');
3823 my $arg = $self->get_form('ach', 'slots', 'drive');
3825 unless ($arg->{ach}) {
3826 return $self->error("Can't find autochanger name");
3829 my $a = $self->ach_get($arg->{ach});
3831 return $self->error("Can't find autochanger name in configuration");
3834 my $storage = $a->get_drive_name($arg->{drive});
3836 return $self->error("Can't get your drive name");
3842 if ($arg->{slots}) {
3843 $slots = join(",", @{ $arg->{slots} });
3844 $slots_sql = " AND Slot IN ($slots) ";
3845 $t += 60*scalar( @{ $arg->{slots} }) ;
3848 my $b = new Bconsole(pref => $self->{info}, timeout => $t,log_stdout => 1);
3849 print "<h1>This command can take long time, be patient...</h1>";
3851 $b->label_barcodes(storage => $storage,
3852 drive => $arg->{drive},
3860 SET LocationId = (SELECT LocationId
3862 WHERE Location = '$arg->{ach}')
3864 WHERE (LocationId = 0 OR LocationId IS NULL)
3873 $self->can_do('r_purge');
3875 my @volume = CGI::param('media');
3878 return $self->error("Can't get media selection");
3881 my $b = new Bconsole(pref => $self->{info}, timeout => 60);
3883 foreach my $v (@volume) {
3885 content => $b->purge_volume($v),
3886 title => "Purge media",
3887 name => "purge volume=$v",
3896 $self->can_do('r_prune');
3898 my @volume = CGI::param('media');
3900 return $self->error("Can't get media selection");
3903 my $b = new Bconsole(pref => $self->{info}, timeout => 60);
3905 foreach my $v (@volume) {
3907 content => $b->prune_volume($v),
3908 title => "Prune volume",
3909 name => "prune volume=$v",
3918 $self->can_do('r_cancel_job');
3920 my $arg = $self->get_form('jobid');
3921 unless ($arg->{jobid}) {
3922 return $self->error("Can't get jobid");
3925 my $b = $self->get_bconsole();
3927 content => $b->cancel($arg->{jobid}),
3928 title => "Cancel job",
3929 name => "cancel jobid=$arg->{jobid}",
3935 # Warning, we display current fileset
3938 my $arg = $self->get_form('fileset');
3940 if ($arg->{fileset}) {
3941 my $b = $self->get_bconsole();
3942 my $ret = $b->get_fileset($arg->{fileset});
3943 $self->display({ fileset => $arg->{fileset},
3945 }, "fileset_view.tpl");
3947 $self->error("Can't get fileset name");
3951 sub director_show_sched
3955 my $arg = $self->get_form('days');
3957 my $b = $self->get_bconsole();
3958 my $ret = $b->director_get_sched( $arg->{days} );
3963 }, "scheduled_job.tpl");
3966 sub enable_disable_job
3968 my ($self, $what) = @_ ;
3969 $self->can_do('r_run_job');
3971 my $name = CGI::param('job') || '';
3972 unless ($name =~ /^[\w\d\.\-\s]+$/) {
3973 return $self->error("Can't find job name");
3976 my $b = $self->get_bconsole();
3986 content => $b->send_cmd("$cmd job=\"$name\""),
3987 title => "$cmd $name",
3988 name => "$cmd job=\"$name\"",
3995 return new Bconsole(pref => $self->{info});
4001 $self->can_do('r_run_job');
4003 my $b = $self->get_bconsole();
4005 my $joblist = [ map { { name => $_ } } $b->list_job() ];
4007 $self->display({ Jobs => $joblist }, "run_job.tpl");
4012 my ($self, $ouput) = @_;
4015 foreach my $l (split(/\r\n/, $ouput)) {
4016 if ($l =~ /(\w+): name=([\w\d\.\s-]+?)(\s+\w+=.+)?$/) {
4022 if (my @l = $l =~ /(\w+)=([\w\d*]+)/g) {
4028 foreach my $k (keys %arg) {
4029 $lowcase{lc($k)} = $arg{$k} ;
4038 $self->can_do('r_run_job');
4040 my $b = $self->get_bconsole();
4042 my $job = CGI::param('job') || '';
4044 # we take informations from director, and we overwrite with user wish
4045 my $info = $b->send_cmd("show job=\"$job\"");
4046 my $attr = $self->run_parse_job($info);
4048 my $arg = $self->get_form('pool', 'level', 'client', 'fileset', 'storage');
4049 my %job_opt = (%$attr, %$arg);
4051 my $jobs = [ map {{ name => $_ }} $b->list_job() ];
4053 my $pools = [ map { { name => $_ } } $b->list_pool() ];
4054 my $clients = [ map { { name => $_ } }$b->list_client()];
4055 my $filesets= [ map { { name => $_ } }$b->list_fileset() ];
4056 my $storages= [ map { { name => $_ } }$b->list_storage()];
4061 clients => $clients,
4062 filesets => $filesets,
4063 storages => $storages,
4065 }, "run_job_mod.tpl");
4071 $self->can_do('r_run_job');
4073 my $b = $self->get_bconsole();
4075 my $jobs = [ map {{ name => $_ }} $b->list_job() ];
4085 $self->can_do('r_run_job');
4087 my $b = $self->get_bconsole();
4089 # TODO: check input (don't use pool, level)
4091 my $arg = $self->get_form('pool', 'level', 'client', 'priority', 'when', 'fileset');
4092 my $job = CGI::param('job') || '';
4093 my $storage = CGI::param('storage') || '';
4095 my $jobid = $b->run(job => $job,
4096 client => $arg->{client},
4097 priority => $arg->{priority},
4098 level => $arg->{level},
4099 storage => $storage,
4100 pool => $arg->{pool},
4101 fileset => $arg->{fileset},
4102 when => $arg->{when},
4105 print $jobid, $b->{error};
4107 print "<br>You can follow job execution <a href='?action=dsp_cur_job;client=$arg->{client};jobid=$jobid'> here </a>";