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 $self->can_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];
2718 # TODO: avoir un mode qui coupe le programme avec une page d'erreur
2719 # we can also get all security and fill {security} hash
2722 my ($self, $action) = @_;
2723 # is security enabled in configuration ?
2724 if (not $self->{info}->{enable_security}) {
2727 # admin is a special user that can do everything
2728 if ($self->{loginname} eq 'admin') {
2732 if (!$self->{loginname}) {
2733 $self->error("Can't do $action, your are not logged. " .
2734 "Check security with your administrator");
2735 $self->display_end();
2739 if (!$self->{security}->{$action}) {
2740 $self->error("$self->{loginname} sorry, but this action ($action) " .
2741 "is not permited. " .
2742 "Check security with your administrator");
2743 $self->display_end();
2753 if (!$self->{info}->{enable_security} or
2754 !$self->{info}->{enable_security_acl})
2759 if ($self->get_roles()) {
2760 return $self->{security}->{use_acl};
2766 # JOIN Client USING (ClientId) " . $b->get_client_filter() . "
2767 sub get_client_filter
2769 my ($self, $login) = @_;
2772 $u = $self->dbh_quote($login);
2773 } elsif ($self->use_filter()) {
2774 $u = $self->dbh_quote($self->{loginname});
2779 JOIN (SELECT ClientId FROM client_group_member
2780 JOIN client_group USING (client_group_id)
2781 JOIN bweb_client_group_acl USING (client_group_id)
2782 JOIN bweb_user USING (userid)
2783 WHERE bweb_user.username = $u
2784 ) AS filter USING (ClientId)";
2787 #JOIN client_group USING (client_group_id)" . $b->get_client_group_filter()
2788 sub get_client_group_filter
2790 my ($self, $login) = @_;
2793 $u = $self->dbh_quote($login);
2794 } elsif ($self->use_filter()) {
2795 $u = $self->dbh_quote($self->{loginname});
2800 JOIN (SELECT client_group_id
2801 FROM bweb_client_group_acl
2802 JOIN bweb_user USING (userid)
2803 WHERE bweb_user.username = $u
2804 ) AS filter USING (client_group_id)";
2807 # role and username have to be quoted before
2808 # role and username can be a quoted list
2811 my ($self, $role, $username) = @_;
2812 $self->can_do("r_user_mgnt");
2814 my $nb = $self->dbh_do("
2815 DELETE FROM bweb_role_member
2816 WHERE roleid = (SELECT roleid FROM bweb_role
2817 WHERE rolename IN ($role))
2818 AND userid = (SELECT userid FROM bweb_user
2819 WHERE username IN ($username))");
2823 # role and username have to be quoted before
2824 # role and username can be a quoted list
2827 my ($self, $role, $username) = @_;
2828 $self->can_do("r_user_mgnt");
2830 my $nb = $self->dbh_do("
2831 INSERT INTO bweb_role_member (roleid, userid)
2832 SELECT roleid, userid FROM bweb_role, bweb_user
2833 WHERE rolename IN ($role)
2834 AND username IN ($username)
2839 # role and username have to be quoted before
2840 # role and username can be a quoted list
2843 my ($self, $copy, $user) = @_;
2844 $self->can_do("r_user_mgnt");
2846 my $nb = $self->dbh_do("
2847 INSERT INTO bweb_role_member (roleid, userid)
2848 SELECT roleid, a.userid
2849 FROM bweb_user AS a, bweb_role_member
2850 JOIN bweb_user USING (userid)
2851 WHERE bweb_user.username = $copy
2852 AND a.username = $user");
2856 # username can be a join quoted list of usernames
2859 my ($self, $username) = @_;
2860 $self->can_do("r_user_mgnt");
2863 DELETE FROM bweb_role_member
2867 WHERE username in ($username))");
2869 DELETE FROM bweb_client_group_acl
2873 WHERE username IN ($username))");
2880 $self->can_do("r_user_mgnt");
2882 my $arg = $self->get_form(qw/jusernames/);
2884 unless ($arg->{jusernames}) {
2885 return $self->error("Can't get user");
2888 $self->{dbh}->begin_work();
2890 $self->revoke_all($arg->{jusernames});
2892 DELETE FROM bweb_user WHERE username IN ($arg->{jusernames})");
2894 $self->{dbh}->commit();
2896 $self->display_users();
2902 $self->can_do("r_user_mgnt");
2904 # we don't quote username directly to check that it is conform
2905 my $arg = $self->get_form(qw/username qpasswd qcomment jrolenames qcreate qcopy_username jclient_groups/) ;
2907 if (not $arg->{qcreate}) {
2908 $arg = $self->get_form(qw/db_roles db_usernames db_client_groups/);
2909 $self->display($arg, "display_user.tpl");
2913 my $u = $self->dbh_quote($arg->{username});
2915 $arg->{use_acl}=(CGI::param('use_acl')?'true':'false');
2917 if (!$arg->{qpasswd}) {
2918 $arg->{qpasswd} = "''";
2920 if (!$arg->{qcomment}) {
2921 $arg->{qcomment} = "''";
2924 # will fail if user already exists
2925 # UPDATE with mysql dbi does not return if update is ok
2928 SET passwd=$arg->{qpasswd}, comment=$arg->{qcomment},
2929 use_acl=$arg->{use_acl}
2930 WHERE username = $u")
2931 # and (! $self->dbh_is_mysql() )
2934 INSERT INTO bweb_user (username, passwd, use_acl, comment)
2935 VALUES ($u, $arg->{qpasswd}, $arg->{use_acl}, $arg->{qcomment})");
2937 $self->{dbh}->begin_work();
2939 $self->revoke_all($u);
2941 if ($arg->{qcopy_username}) {
2942 $self->grant_like($arg->{qcopy_username}, $u);
2944 $self->grant($arg->{jrolenames}, $u);
2947 if ($arg->{jclient_groups}) {
2949 INSERT INTO bweb_client_group_acl (client_group_id, userid)
2950 SELECT client_group_id, userid
2951 FROM client_group, bweb_user
2952 WHERE client_group_name IN ($arg->{jclient_groups})
2957 $self->{dbh}->commit();
2959 $self->display_users();
2962 # TODO: we miss a matrix with all user/roles
2966 $self->can_do("r_user_mgnt");
2968 my $arg = $self->get_form(qw/db_usernames/) ;
2970 if ($self->{dbh}->errstr) {
2971 return $self->error("Can't use users with bweb, read INSTALL to enable them");
2974 $self->display({ ID => $cur_id++,
2976 "display_users.tpl");
2982 $self->can_do("r_user_mgnt");
2984 my $arg = $self->get_form('username');
2985 my $user = $self->dbh_quote($arg->{username});
2987 my $userp = $self->dbh_selectrow_hashref("
2988 SELECT username, passwd, comment, use_acl
2990 WHERE username = $user
2993 return $self->error("Can't find $user in catalog");
2995 my $filter = $self->get_client_group_filter($arg->{username});
2996 my $scg = $self->dbh_selectall_hashref("
2997 SELECT client_group_name AS name
2998 FROM client_group $filter
3002 #------------+--------
3007 my $role = $self->dbh_selectall_hashref("
3008 SELECT rolename, temp.userid
3010 LEFT JOIN (SELECT roleid, userid
3011 FROM bweb_user JOIN bweb_role_member USING (userid)
3012 WHERE username = $user) AS temp USING (roleid)
3016 $arg = $self->get_form(qw/db_usernames db_client_groups/);
3019 db_usernames => $arg->{db_usernames},
3020 username => $userp->{username},
3021 comment => $userp->{comment},
3022 passwd => $userp->{passwd},
3023 use_acl => $userp->{use_acl},
3024 db_client_groups => $arg->{db_client_groups},
3025 client_group => [ values %$scg ],
3026 db_roles => [ values %$role],
3027 }, "display_user.tpl");
3031 ###########################################################
3033 sub get_media_max_size
3035 my ($self, $type) = @_;
3037 "SELECT avg(VolBytes) AS size
3039 WHERE Media.VolStatus = 'Full'
3040 AND Media.MediaType = '$type'
3043 my $res = $self->selectrow_hashref($query);
3046 return $res->{size};
3056 my $media = $self->get_form('qmedia');
3058 unless ($media->{qmedia}) {
3059 return $self->error("Can't get media");
3063 SELECT Media.Slot AS slot,
3064 PoolMedia.Name AS poolname,
3065 Media.VolStatus AS volstatus,
3066 Media.InChanger AS inchanger,
3067 Location.Location AS location,
3068 Media.VolumeName AS volumename,
3069 Media.MaxVolBytes AS maxvolbytes,
3070 Media.MaxVolJobs AS maxvoljobs,
3071 Media.MaxVolFiles AS maxvolfiles,
3072 Media.VolUseDuration AS voluseduration,
3073 Media.VolRetention AS volretention,
3074 Media.Comment AS comment,
3075 PoolRecycle.Name AS poolrecycle,
3076 Media.Enabled AS enabled
3078 FROM Media INNER JOIN Pool AS PoolMedia ON (Media.PoolId = PoolMedia.PoolId)
3079 LEFT JOIN Pool AS PoolRecycle ON (Media.RecyclePoolId = PoolRecycle.PoolId)
3080 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
3082 WHERE Media.VolumeName = $media->{qmedia}
3085 my $row = $self->dbh_selectrow_hashref($query);
3086 $row->{volretention} = human_sec($row->{volretention});
3087 $row->{voluseduration} = human_sec($row->{voluseduration});
3088 $row->{enabled} = human_enabled($row->{enabled});
3090 my $elt = $self->get_form(qw/db_pools db_locations/);
3095 }, "update_media.tpl");
3101 $self->can_do('r_media_mgnt');
3103 my $arg = $self->get_form('jmedias', 'qnewlocation') ;
3105 unless ($arg->{jmedias}) {
3106 return $self->error("Can't get selected media");
3109 unless ($arg->{qnewlocation}) {
3110 return $self->error("Can't get new location");
3115 SET LocationId = (SELECT LocationId
3117 WHERE Location = $arg->{qnewlocation})
3118 WHERE Media.VolumeName IN ($arg->{jmedias})
3121 my $nb = $self->dbh_do($query);
3123 print "$nb media updated, you may have to update your autochanger.";
3125 $self->display_media();
3131 $self->can_do('r_media_mgnt');
3133 my $media = $self->get_selected_media_location();
3135 return $self->error("Can't get media selection");
3137 my $newloc = CGI::param('newlocation');
3139 my $user = CGI::param('user') || 'unknown';
3140 my $comm = CGI::param('comment') || '';
3141 $comm = $self->dbh_quote("$user: $comm");
3143 my $arg = $self->get_form('enabled');
3144 my $en = human_enabled($arg->{enabled});
3145 my $b = $self->get_bconsole();
3148 foreach my $vol (keys %$media) {
3150 INSERT LocationLog (Date, Comment, MediaId, LocationId, NewVolStatus)
3152 NOW(), $comm, (SELECT MediaId FROM Media WHERE VolumeName = '$vol'),
3153 (SELECT LocationId FROM Location WHERE Location = '$media->{$vol}->{location}'),
3154 (SELECT VolStatus FROM Media WHERE VolumeName = '$vol')
3157 $self->dbh_do($query);
3158 $self->debug($query);
3159 $b->send_cmd("update volume=\"$vol\" enabled=$en");
3164 $q->param('action', 'update_location');
3165 my $url = $q->url(-full => 1, -query=>1);
3167 $self->display({ email => $self->{info}->{email_media},
3169 newlocation => $newloc,
3170 # [ { volumename => 'vol1' }, { volumename => 'vol2'},..]
3171 media => [ values %$media ],
3173 "change_location.tpl");
3177 sub display_client_stats
3179 my ($self, %arg) = @_ ;
3180 $self->can_do('r_view_stats');
3182 my $client = $self->dbh_quote($arg{clientname});
3183 # get security filter
3184 my $filter = $self->get_client_filter();
3186 my ($limit, $label) = $self->get_limit(%arg);
3189 count(Job.JobId) AS nb_jobs,
3190 sum(Job.JobBytes) AS nb_bytes,
3191 sum(Job.JobErrors) AS nb_err,
3192 sum(Job.JobFiles) AS nb_files,
3193 Client.Name AS clientname
3194 FROM Job JOIN Client USING (ClientId) $filter
3196 Client.Name = $client
3198 GROUP BY Client.Name
3201 my $row = $self->dbh_selectrow_hashref($query);
3203 $row->{ID} = $cur_id++;
3204 $row->{label} = $label;
3205 $row->{grapharg} = "client";
3207 $self->display($row, "display_client_stats.tpl");
3211 sub display_group_stats
3213 my ($self, %arg) = @_ ;
3215 my $carg = $self->get_form(qw/qclient_group/);
3217 unless ($carg->{qclient_group}) {
3218 return $self->error("Can't get group");
3221 my ($limit, $label) = $self->get_limit(%arg);
3225 count(Job.JobId) AS nb_jobs,
3226 sum(Job.JobBytes) AS nb_bytes,
3227 sum(Job.JobErrors) AS nb_err,
3228 sum(Job.JobFiles) AS nb_files,
3229 client_group.client_group_name AS clientname
3230 FROM Job JOIN Client USING (ClientId)
3231 JOIN client_group_member ON (Client.ClientId = client_group_member.clientid)
3232 JOIN client_group USING (client_group_id)
3234 client_group.client_group_name = $carg->{qclient_group}
3236 GROUP BY client_group.client_group_name
3239 my $row = $self->dbh_selectrow_hashref($query);
3241 $row->{ID} = $cur_id++;
3242 $row->{label} = $label;
3243 $row->{grapharg} = "client_group";
3245 $self->display($row, "display_client_stats.tpl");
3248 # poolname can be undef
3251 my ($self, $poolname) = @_ ;
3255 my $arg = $self->get_form('jmediatypes', 'qmediatypes');
3256 if ($arg->{jmediatypes}) {
3257 $whereW = "WHERE MediaType IN ($arg->{jmediatypes}) ";
3258 $whereA = "AND MediaType IN ($arg->{jmediatypes}) ";
3261 # TODO : afficher les tailles et les dates
3264 SELECT subq.volmax AS volmax,
3265 subq.volnum AS volnum,
3266 subq.voltotal AS voltotal,
3268 Pool.Recycle AS recycle,
3269 Pool.VolRetention AS volretention,
3270 Pool.VolUseDuration AS voluseduration,
3271 Pool.MaxVolJobs AS maxvoljobs,
3272 Pool.MaxVolFiles AS maxvolfiles,
3273 Pool.MaxVolBytes AS maxvolbytes,
3274 subq.PoolId AS PoolId,
3275 subq.MediaType AS mediatype,
3276 $self->{sql}->{CAT_POOL_TYPE} AS uniq
3279 SELECT COALESCE(media_avg_size.volavg,0) * count(Media.MediaId) AS volmax,
3280 count(Media.MediaId) AS volnum,
3281 sum(Media.VolBytes) AS voltotal,
3282 Media.PoolId AS PoolId,
3283 Media.MediaType AS MediaType
3285 LEFT JOIN (SELECT avg(Media.VolBytes) AS volavg,
3286 Media.MediaType AS MediaType
3288 WHERE Media.VolStatus = 'Full'
3289 GROUP BY Media.MediaType
3290 ) AS media_avg_size ON (Media.MediaType = media_avg_size.MediaType)
3291 GROUP BY Media.MediaType, Media.PoolId, media_avg_size.volavg
3293 LEFT JOIN Pool ON (Pool.PoolId = subq.PoolId)
3297 my $all = $self->dbh_selectall_hashref($query, 'uniq') ;
3300 SELECT Pool.Name AS name,
3301 sum(VolBytes) AS size
3302 FROM Media JOIN Pool ON (Media.PoolId = Pool.PoolId)
3303 WHERE Media.VolStatus IN ('Recycled', 'Purged')
3307 my $empty = $self->dbh_selectall_hashref($query, 'name');
3309 foreach my $p (values %$all) {
3310 if ($p->{volmax} > 0) { # mysql returns 0.0000
3311 # we remove Recycled/Purged media from pool usage
3312 if (defined $empty->{$p->{name}}) {
3313 $p->{voltotal} -= $empty->{$p->{name}}->{size};
3315 $p->{poolusage} = sprintf('%.2f', $p->{voltotal} * 100/ $p->{volmax}) ;
3317 $p->{poolusage} = 0;
3321 SELECT VolStatus AS volstatus, count(MediaId) AS nb
3323 WHERE PoolId=$p->{poolid}
3324 AND Media.MediaType = '$p->{mediatype}'
3328 my $content = $self->dbh_selectall_hashref($query, 'volstatus');
3329 foreach my $t (values %$content) {
3330 $p->{"nb_" . $t->{volstatus}} = $t->{nb} ;
3335 $self->display({ ID => $cur_id++,
3336 MediaType => $arg->{qmediatypes}, # [ { name => type1 } , { name => type2 } ]
3337 Pools => [ values %$all ]},
3338 "display_pool.tpl");
3341 sub display_running_job
3344 $self->can_do('r_view_running_job');
3346 my $arg = $self->get_form('client', 'jobid');
3348 if (!$arg->{client} and $arg->{jobid}) {
3349 # get security filter
3350 my $filter = $self->get_client_filter();
3353 SELECT Client.Name AS name
3354 FROM Job INNER JOIN Client USING (ClientId) $filter
3355 WHERE Job.JobId = $arg->{jobid}
3358 my $row = $self->dbh_selectrow_hashref($query);
3361 $arg->{client} = $row->{name};
3362 CGI::param('client', $arg->{client});
3366 if ($arg->{client}) {
3367 my $cli = new Bweb::Client(name => $arg->{client});
3368 $cli->display_running_job($self->{info}, $arg->{jobid});
3369 if ($arg->{jobid}) {
3370 $self->get_job_log();
3373 $self->error("Can't get client or jobid");
3377 sub display_running_jobs
3379 my ($self, $display_action) = @_;
3380 $self->can_do('r_view_running_job');
3382 # get security filter
3383 my $filter = $self->get_client_filter();
3386 SELECT Job.JobId AS jobid,
3387 Job.Name AS jobname,
3389 Job.StartTime AS starttime,
3390 Job.JobFiles AS jobfiles,
3391 Job.JobBytes AS jobbytes,
3392 Job.JobStatus AS jobstatus,
3393 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(NOW())
3394 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime))
3396 Client.Name AS clientname
3397 FROM Job INNER JOIN Client USING (ClientId) $filter
3399 JobStatus IN ('C','R','B','e','D','F','S','m','M','s','j','c','d','t','p')
3401 my $all = $self->dbh_selectall_hashref($query, 'jobid') ;
3403 $self->display({ ID => $cur_id++,
3404 display_action => $display_action,
3405 Jobs => [ values %$all ]},
3406 "running_job.tpl") ;
3409 # return the autochanger list to update
3413 $self->can_do('r_media_mgnt');
3416 my $arg = $self->get_form('jmedias');
3418 unless ($arg->{jmedias}) {
3419 return $self->error("Can't get media selection");
3423 SELECT Media.VolumeName AS volumename,
3424 Storage.Name AS storage,
3425 Location.Location AS location,
3427 FROM Media INNER JOIN Storage ON (Media.StorageId = Storage.StorageId)
3428 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
3429 WHERE Media.VolumeName IN ($arg->{jmedias})
3430 AND Media.InChanger = 1
3433 my $all = $self->dbh_selectall_hashref($query, 'volumename');
3435 foreach my $vol (values %$all) {
3436 my $a = $self->ach_get($vol->{location});
3438 $ret{$vol->{location}} = 1;
3440 unless ($a->{have_status}) {
3442 $a->{have_status} = 1;
3445 print "eject $vol->{volumename} from $vol->{storage} : ";
3446 if ($a->send_to_io($vol->{slot})) {
3447 print "<img src='/bweb/T.png' alt='ok'><br/>";
3449 print "<img src='/bweb/E.png' alt='err'><br/>";
3459 my ($to, $subject, $content) = (CGI::param('email'),
3460 CGI::param('subject'),
3461 CGI::param('content'));
3462 $to =~ s/[^\w\d\.\@<>,]//;
3463 $subject =~ s/[^\w\d\.\[\]]/ /;
3465 open(MAIL, "|mail -s '$subject' '$to'") ;
3466 print MAIL $content;
3476 my $arg = $self->get_form('jobid', 'client');
3478 print CGI::header('text/brestore');
3479 print "jobid=$arg->{jobid}\n" if ($arg->{jobid});
3480 print "client=$arg->{client}\n" if ($arg->{client});
3481 print "\n\nYou have to assign this mime type with /usr/bin/brestore.pl\n";
3485 # TODO : move this to Bweb::Autochanger ?
3486 # TODO : make this internal to not eject tape ?
3492 my ($self, $name) = @_;
3495 return $self->error("Can't get your autochanger name ach");
3498 unless ($self->{info}->{ach_list}) {
3499 return $self->error("Could not find any autochanger");
3502 my $a = $self->{info}->{ach_list}->{$name};
3505 $self->error("Can't get your autochanger $name from your ach_list");
3510 $a->{debug} = $self->{debug};
3517 my ($self, $ach) = @_;
3518 $self->can_do('r_configure');
3520 $self->{info}->{ach_list}->{$ach->{name}} = $ach;
3522 $self->{info}->save();
3530 $self->can_do('r_configure');
3532 my $arg = $self->get_form('ach');
3534 or !$self->{info}->{ach_list}
3535 or !$self->{info}->{ach_list}->{$arg->{ach}})
3537 return $self->error("Can't get autochanger name");
3540 my $ach = $self->{info}->{ach_list}->{$arg->{ach}};
3544 [ map { { name => $_, index => $i++ } } @{$ach->{drive_name}} ] ;
3546 my $b = $self->get_bconsole();
3548 my @storages = $b->list_storage() ;
3550 $ach->{devices} = [ map { { name => $_ } } @storages ];
3552 $self->display($ach, "ach_add.tpl");
3553 delete $ach->{drives};
3554 delete $ach->{devices};
3561 $self->can_do('r_configure');
3563 my $arg = $self->get_form('ach');
3566 or !$self->{info}->{ach_list}
3567 or !$self->{info}->{ach_list}->{$arg->{ach}})
3569 return $self->error("Can't get autochanger name");
3572 delete $self->{info}->{ach_list}->{$arg->{ach}} ;
3574 $self->{info}->save();
3575 $self->{info}->view();
3581 $self->can_do('r_configure');
3583 my $arg = $self->get_form('ach', 'mtxcmd', 'device', 'precmd');
3585 my $b = $self->get_bconsole();
3586 my @storages = $b->list_storage() ;
3588 unless ($arg->{ach}) {
3589 $arg->{devices} = [ map { { name => $_ } } @storages ];
3590 return $self->display($arg, "ach_add.tpl");
3594 foreach my $drive (CGI::param('drives'))
3596 unless (grep(/^$drive$/,@storages)) {
3597 return $self->error("Can't find $drive in storage list");
3600 my $index = CGI::param("index_$drive");
3601 unless (defined $index and $index =~ /^(\d+)$/) {
3602 return $self->error("Can't get $drive index");
3605 $drives[$index] = $drive;
3609 return $self->error("Can't get drives from Autochanger");
3612 my $a = new Bweb::Autochanger(name => $arg->{ach},
3613 precmd => $arg->{precmd},
3614 drive_name => \@drives,
3615 device => $arg->{device},
3616 mtxcmd => $arg->{mtxcmd});
3618 $self->ach_register($a) ;
3620 $self->{info}->view();
3626 $self->can_do('r_delete_job');
3628 my $arg = $self->get_form('jobid');
3630 if ($arg->{jobid}) {
3631 my $b = $self->get_bconsole();
3632 my $ret = $b->send_cmd("delete jobid=\"$arg->{jobid}\"");
3636 title => "Delete a job ",
3637 name => "delete jobid=$arg->{jobid}",
3645 $self->can_do('r_media_mgnt');
3647 my $arg = $self->get_form(qw/media volstatus inchanger pool
3648 slot volretention voluseduration
3649 maxvoljobs maxvolfiles maxvolbytes
3650 qcomment poolrecycle enabled
3653 unless ($arg->{media}) {
3654 return $self->error("Can't find media selection");
3657 my $update = "update volume=$arg->{media} ";
3659 if ($arg->{volstatus}) {
3660 $update .= " volstatus=$arg->{volstatus} ";
3663 if ($arg->{inchanger}) {
3664 $update .= " inchanger=yes " ;
3666 $update .= " slot=$arg->{slot} ";
3669 $update .= " slot=0 inchanger=no ";
3672 if ($arg->{enabled}) {
3673 $update .= " enabled=$arg->{enabled} ";
3677 $update .= " pool=$arg->{pool} " ;
3680 if (defined $arg->{volretention}) {
3681 $update .= " volretention=\"$arg->{volretention}\" " ;
3684 if (defined $arg->{voluseduration}) {
3685 $update .= " voluse=\"$arg->{voluseduration}\" " ;
3688 if (defined $arg->{maxvoljobs}) {
3689 $update .= " maxvoljobs=$arg->{maxvoljobs} " ;
3692 if (defined $arg->{maxvolfiles}) {
3693 $update .= " maxvolfiles=$arg->{maxvolfiles} " ;
3696 if (defined $arg->{maxvolbytes}) {
3697 $update .= " maxvolbytes=$arg->{maxvolbytes} " ;
3700 if (defined $arg->{poolrecycle}) {
3701 $update .= " recyclepool=\"$arg->{poolrecycle}\" " ;
3704 my $b = $self->get_bconsole();
3707 content => $b->send_cmd($update),
3708 title => "Update a volume ",
3714 my $media = $self->dbh_quote($arg->{media});
3716 my $loc = CGI::param('location') || '';
3718 $loc = $self->dbh_quote($loc); # is checked by db
3719 push @q, "LocationId=(SELECT LocationId FROM Location WHERE Location=$loc)";
3721 if (!$arg->{qcomment}) {
3722 $arg->{qcomment} = "''";
3724 push @q, "Comment=$arg->{qcomment}";
3729 SET " . join (',', @q) . "
3730 WHERE Media.VolumeName = $media
3732 $self->dbh_do($query);
3734 $self->update_media();
3740 $self->can_do('r_autochanger_mgnt');
3742 my $ach = CGI::param('ach') ;
3743 $ach = $self->ach_get($ach);
3745 return $self->error("Bad autochanger name");
3749 my $b = new Bconsole(pref => $self->{info},timeout => 60,log_stdout => 1);
3750 $b->update_slots($ach->{name});
3757 $self->can_do('r_view_log');
3759 my $arg = $self->get_form('jobid', 'limit', 'offset');
3760 unless ($arg->{jobid}) {
3761 return $self->error("Can't get jobid");
3764 if ($arg->{limit} == 100) {
3765 $arg->{limit} = 1000;
3767 # get security filter
3768 my $filter = $self->get_client_filter();
3771 SELECT Job.Name as name, Client.Name as clientname
3772 FROM Job INNER JOIN Client USING (ClientId) $filter
3773 WHERE JobId = $arg->{jobid}
3776 my $row = $self->dbh_selectrow_hashref($query);
3779 return $self->error("Can't find $arg->{jobid} in catalog");
3782 # display only Error and Warning messages
3784 if (CGI::param('error')) {
3785 $filter = " AND LogText $self->{sql}->{MATCH} 'Error|Warning' ";
3789 if (CGI::param('time') || $self->{info}->{display_log_time}) {
3790 $logtext = $self->dbh_strcat('Time', " ' ' ", 'LogText');
3792 $logtext = 'LogText';
3796 SELECT count(1) AS nbline, JobId AS jobid,
3797 GROUP_CONCAT($logtext $self->{sql}->{CONCAT_SEP}) AS logtxt
3799 SELECT JobId, Time, LogText
3801 WHERE ( Log.JobId = $arg->{jobid}
3803 AND Time >= (SELECT StartTime FROM Job WHERE JobId=$arg->{jobid})
3804 AND Time <= (SELECT COALESCE(EndTime,NOW()) FROM Job WHERE JobId=$arg->{jobid})
3808 OFFSET $arg->{offset}
3814 my $log = $self->dbh_selectrow_hashref($query);
3816 return $self->error("Can't get log for jobid $arg->{jobid}");
3819 $self->display({ lines=> $log->{logtxt},
3820 nbline => $log->{nbline},
3821 jobid => $arg->{jobid},
3822 name => $row->{name},
3823 client => $row->{clientname},
3824 offset => $arg->{offset},
3825 limit => $arg->{limit},
3826 }, 'display_log.tpl');
3832 $self->can_do('r_autochanger_mgnt');
3834 my $arg = $self->get_form('ach', 'slots', 'drive');
3836 unless ($arg->{ach}) {
3837 return $self->error("Can't find autochanger name");
3840 my $a = $self->ach_get($arg->{ach});
3842 return $self->error("Can't find autochanger name in configuration");
3845 my $storage = $a->get_drive_name($arg->{drive});
3847 return $self->error("Can't get your drive name");
3853 if ($arg->{slots}) {
3854 $slots = join(",", @{ $arg->{slots} });
3855 $slots_sql = " AND Slot IN ($slots) ";
3856 $t += 60*scalar( @{ $arg->{slots} }) ;
3859 my $b = new Bconsole(pref => $self->{info}, timeout => $t,log_stdout => 1);
3860 print "<h1>This command can take long time, be patient...</h1>";
3862 $b->label_barcodes(storage => $storage,
3863 drive => $arg->{drive},
3871 SET LocationId = (SELECT LocationId
3873 WHERE Location = '$arg->{ach}')
3875 WHERE (LocationId = 0 OR LocationId IS NULL)
3884 $self->can_do('r_purge');
3886 my @volume = CGI::param('media');
3889 return $self->error("Can't get media selection");
3892 my $b = new Bconsole(pref => $self->{info}, timeout => 60);
3894 foreach my $v (@volume) {
3896 content => $b->purge_volume($v),
3897 title => "Purge media",
3898 name => "purge volume=$v",
3907 $self->can_do('r_prune');
3909 my @volume = CGI::param('media');
3911 return $self->error("Can't get media selection");
3914 my $b = new Bconsole(pref => $self->{info}, timeout => 60);
3916 foreach my $v (@volume) {
3918 content => $b->prune_volume($v),
3919 title => "Prune volume",
3920 name => "prune volume=$v",
3929 $self->can_do('r_cancel_job');
3931 my $arg = $self->get_form('jobid');
3932 unless ($arg->{jobid}) {
3933 return $self->error("Can't get jobid");
3936 my $b = $self->get_bconsole();
3938 content => $b->cancel($arg->{jobid}),
3939 title => "Cancel job",
3940 name => "cancel jobid=$arg->{jobid}",
3946 # Warning, we display current fileset
3949 my $arg = $self->get_form('fileset');
3951 if ($arg->{fileset}) {
3952 my $b = $self->get_bconsole();
3953 my $ret = $b->get_fileset($arg->{fileset});
3954 $self->display({ fileset => $arg->{fileset},
3956 }, "fileset_view.tpl");
3958 $self->error("Can't get fileset name");
3962 sub director_show_sched
3966 my $arg = $self->get_form('days');
3968 my $b = $self->get_bconsole();
3969 my $ret = $b->director_get_sched( $arg->{days} );
3974 }, "scheduled_job.tpl");
3977 sub enable_disable_job
3979 my ($self, $what) = @_ ;
3980 $self->can_do('r_run_job');
3982 my $name = CGI::param('job') || '';
3983 unless ($name =~ /^[\w\d\.\-\s]+$/) {
3984 return $self->error("Can't find job name");
3987 my $b = $self->get_bconsole();
3997 content => $b->send_cmd("$cmd job=\"$name\""),
3998 title => "$cmd $name",
3999 name => "$cmd job=\"$name\"",
4006 return new Bconsole(pref => $self->{info});
4012 $self->can_do('r_run_job');
4014 my $b = $self->get_bconsole();
4016 my $joblist = [ map { { name => $_ } } $b->list_job() ];
4018 $self->display({ Jobs => $joblist }, "run_job.tpl");
4023 my ($self, $ouput) = @_;
4026 foreach my $l (split(/\r\n/, $ouput)) {
4027 if ($l =~ /(\w+): name=([\w\d\.\s-]+?)(\s+\w+=.+)?$/) {
4033 if (my @l = $l =~ /(\w+)=([\w\d*]+)/g) {
4039 foreach my $k (keys %arg) {
4040 $lowcase{lc($k)} = $arg{$k} ;
4049 $self->can_do('r_run_job');
4051 my $b = $self->get_bconsole();
4053 my $job = CGI::param('job') || '';
4055 # we take informations from director, and we overwrite with user wish
4056 my $info = $b->send_cmd("show job=\"$job\"");
4057 my $attr = $self->run_parse_job($info);
4059 my $arg = $self->get_form('pool', 'level', 'client', 'fileset', 'storage');
4060 my %job_opt = (%$attr, %$arg);
4062 my $jobs = [ map {{ name => $_ }} $b->list_job() ];
4064 my $pools = [ map { { name => $_ } } $b->list_pool() ];
4065 my $clients = [ map { { name => $_ } }$b->list_client()];
4066 my $filesets= [ map { { name => $_ } }$b->list_fileset() ];
4067 my $storages= [ map { { name => $_ } }$b->list_storage()];
4072 clients => $clients,
4073 filesets => $filesets,
4074 storages => $storages,
4076 }, "run_job_mod.tpl");
4082 $self->can_do('r_run_job');
4084 my $b = $self->get_bconsole();
4086 my $jobs = [ map {{ name => $_ }} $b->list_job() ];
4096 $self->can_do('r_run_job');
4098 my $b = $self->get_bconsole();
4100 # TODO: check input (don't use pool, level)
4102 my $arg = $self->get_form('pool', 'level', 'client', 'priority', 'when', 'fileset');
4103 my $job = CGI::param('job') || '';
4104 my $storage = CGI::param('storage') || '';
4106 my $jobid = $b->run(job => $job,
4107 client => $arg->{client},
4108 priority => $arg->{priority},
4109 level => $arg->{level},
4110 storage => $storage,
4111 pool => $arg->{pool},
4112 fileset => $arg->{fileset},
4113 when => $arg->{when},
4116 print $jobid, $b->{error};
4118 print "<br>You can follow job execution <a href='?action=dsp_cur_job;client=$arg->{client};jobid=$jobid'> here </a>";