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/^(.*)$/,
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 wiki_url => qr!(.*)$!,
219 stat_job_table => qr!^(\w*)$!,
220 display_log_time => qr!^(on)?$!,
221 enable_security => qr/^(on)?$/,
222 enable_security_acl => qr/^(on)?$/,
227 load - load config_file
231 this function load the specified config_file.
239 unless (open(FP, $self->{config_file}))
241 return $self->error("can't load config_file $self->{config_file} : $!");
243 my $f=''; my $tmpbuffer;
244 while(read FP,$tmpbuffer,4096)
252 no strict; # I have no idea of the contents of the file
259 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...") ;
262 foreach my $k (keys %$VAR1) {
263 $self->{$k} = $VAR1->{$k};
271 load_old - load old configuration format
279 unless (open(FP, $self->{config_file}))
281 return $self->error("$self->{config_file} : $!");
284 while (my $line = <FP>)
287 my ($k, $v) = split(/\s*=\s*/, $line, 2);
299 save - save the current configuration to config_file
307 if ($self->{ach_list}) {
308 # shortcut for display_begin
309 $self->{achs} = [ map {{ name => $_ }}
310 keys %{$self->{ach_list}}
314 unless (open(FP, ">$self->{config_file}"))
316 return $self->error("$self->{config_file} : $!\n" .
317 "You must add this to your config file\n"
318 . Data::Dumper::Dumper($self));
321 print FP Data::Dumper::Dumper($self);
329 edit, view, modify - html form ouput
337 $self->display($self, "config_edit.tpl");
343 $self->display($self, "config_view.tpl");
351 # we need to reset checkbox first
353 $self->{display_log_time} = 0;
354 $self->{enable_security} = 0;
355 $self->{enable_security_acl} = 0;
357 foreach my $k (CGI::param())
359 next unless (exists $k_re{$k}) ;
360 my $val = CGI::param($k);
361 if ($val =~ $k_re{$k}) {
364 $self->{error} .= "bad parameter : $k = [$val]";
370 if ($self->{error}) { # an error as occured
371 $self->display($self, 'error.tpl');
379 ################################################################
381 package Bweb::Client;
383 use base q/Bweb::Gui/;
387 Bweb::Client - Bacula FD
391 this package is use to do all Client operations like, parse status etc...
395 $client = new Bweb::Client(name => 'zog-fd');
396 $client->status(); # do a 'status client=zog-fd'
402 display_running_job - Html display of a running job
406 this function is used to display information about a current job
410 sub display_running_job
412 my ($self, $conf, $jobid) = @_ ;
414 my $status = $self->status($conf);
417 if ($status->{$jobid}) {
418 $self->display($status->{$jobid}, "client_job_status.tpl");
421 for my $id (keys %$status) {
422 $self->display($status->{$id}, "client_job_status.tpl");
429 $client = new Bweb::Client(name => 'plume-fd');
431 $client->status($bweb);
435 dirty hack to parse "status client=xxx-fd"
439 JobId 105 Job Full_plume.2006-06-06_17.22.23 is running.
440 Backup Job started: 06-jun-06 17:22
441 Files=8,971 Bytes=194,484,132 Bytes/sec=7,480,158
442 Files Examined=10,697
443 Processing file: /home/eric/.openoffice.org2/user/config/standard.sod
449 JobName => Full_plume.2006-06-06_17.22.23,
452 Bytes => 194,484,132,
462 my ($self, $conf) = @_ ;
464 if (defined $self->{cur_jobs}) {
465 return $self->{cur_jobs} ;
469 my $b = new Bconsole(pref => $conf);
470 my $ret = $b->send_cmd("st client=$self->{name}");
474 for my $r (split(/\n/, $ret)) {
476 $r =~ s/(^\s+|\s+$)//g;
477 if ($r =~ /JobId (\d+) Job (\S+)/) {
479 $arg->{$jobid} = { @param, JobId => $jobid } ;
483 @param = ( JobName => $2 );
485 } elsif ($r =~ /=.+=/) {
486 push @param, split(/\s+|\s*=\s*/, $r) ;
488 } elsif ($r =~ /=/) { # one per line
489 push @param, split(/\s*=\s*/, $r) ;
491 } elsif ($r =~ /:/) { # one per line
492 push @param, split(/\s*:\s*/, $r, 2) ;
496 if ($jobid and @param) {
497 $arg->{$jobid} = { @param,
499 Client => $self->{name},
503 $self->{cur_jobs} = $arg ;
509 ################################################################
511 package Bweb::Autochanger;
513 use base q/Bweb::Gui/;
517 Bweb::Autochanger - Object to manage Autochanger
521 this package will parse the mtx output and manage drives.
525 $auto = new Bweb::Autochanger(precmd => 'sudo');
527 $auto = new Bweb::Autochanger(precmd => 'ssh root@robot');
531 $auto->slot_is_full(10);
532 $auto->transfer(10, 11);
538 my ($class, %arg) = @_;
541 name => '', # autochanger name
542 label => {}, # where are volume { label1 => 40, label2 => drive0 }
543 drive => [], # drive use [ 'media1', 'empty', ..]
544 slot => [], # slot use [ undef, 'empty', 'empty', ..] no slot 0
545 io => [], # io slot number list [ 41, 42, 43...]
546 info => {slot => 0, # informations (slot, drive, io)
550 mtxcmd => '/usr/sbin/mtx',
552 device => '/dev/changer',
553 precmd => '', # ssh command
554 bweb => undef, # link to bacula web object (use for display)
557 map { $self->{lc($_)} = $arg{$_} } keys %arg ;
564 status - parse the output of mtx status
568 this function will launch mtx status and parse the output. it will
569 give a perlish view of the autochanger content.
571 it uses ssh if the autochanger is on a other host.
578 my @out = `$self->{precmd} $self->{mtxcmd} -f $self->{device} status` ;
580 # TODO : reset all infos
581 $self->{info}->{drive} = 0;
582 $self->{info}->{slot} = 0;
583 $self->{info}->{io} = 0;
585 #my @out = `cat /home/eric/travail/brestore/plume/mtx` ;
588 # Storage Changer /dev/changer:2 Drives, 45 Slots ( 5 Import/Export )
589 #Data Transfer Element 0:Full (Storage Element 1 Loaded):VolumeTag = 000000
590 #Data Transfer Element 1:Empty
591 # Storage Element 1:Empty
592 # Storage Element 2:Full :VolumeTag=000002
593 # Storage Element 3:Empty
594 # Storage Element 4:Full :VolumeTag=000004
595 # Storage Element 5:Full :VolumeTag=000001
596 # Storage Element 6:Full :VolumeTag=000003
597 # Storage Element 7:Empty
598 # Storage Element 41 IMPORT/EXPORT:Empty
599 # Storage Element 41 IMPORT/EXPORT:Full :VolumeTag=000002
604 # Storage Element 7:Empty
605 # Storage Element 2:Full :VolumeTag=000002
606 if ($l =~ /Storage Element (\d+):(Empty|Full)(\s+:VolumeTag=([\w\d]+))?/){
609 $self->set_empty_slot($1);
611 $self->set_slot($1, $4);
614 } elsif ($l =~ /Data Transfer.+(\d+):(Full|Empty)(\s+.Storage Element (\d+) Loaded.(:VolumeTag = ([\w\d]+))?)?/) {
617 $self->set_empty_drive($1);
619 $self->set_drive($1, $4, $6);
622 } elsif ($l =~ /Storage Element (\d+).+IMPORT\/EXPORT:(Empty|Full)( :VolumeTag=([\d\w]+))?/)
625 $self->set_empty_io($1);
627 $self->set_io($1, $4);
630 # Storage Changer /dev/changer:2 Drives, 30 Slots ( 1 Import/Export )
632 } elsif ($l =~ /Storage Changer .+:(\d+) Drives, (\d+) Slots/) {
633 $self->{info}->{drive} = $1;
634 $self->{info}->{slot} = $2;
635 if ($l =~ /(\d+)\s+Import/) {
636 $self->{info}->{io} = $1 ;
638 $self->{info}->{io} = 0;
643 $self->debug($self) ;
648 my ($self, $slot) = @_;
651 if ($self->{slot}->[$slot] eq 'loaded') {
655 my $label = $self->{slot}->[$slot] ;
657 return $self->is_media_loaded($label);
662 my ($self, $drive, $slot) = @_;
664 return 0 if (not defined $drive or $self->{drive}->[$drive] eq 'empty') ;
665 return 0 if ($self->slot_is_full($slot)) ;
667 my $out = `$self->{precmd} $self->{mtxcmd} -f $self->{device} unload $slot $drive 2>&1`;
670 my $content = $self->get_slot($slot);
671 print "content = $content<br/> $drive => $slot<br/>";
672 $self->set_empty_drive($drive);
673 $self->set_slot($slot, $content);
676 $self->{error} = $out;
681 # TODO: load/unload have to use mtx script from bacula
684 my ($self, $drive, $slot) = @_;
686 return 0 if (not defined $drive or $self->{drive}->[$drive] ne 'empty') ;
687 return 0 unless ($self->slot_is_full($slot)) ;
689 print "Loading drive $drive with slot $slot<br/>\n";
690 my $out = `$self->{precmd} $self->{mtxcmd} -f $self->{device} load $slot $drive 2>&1`;
693 my $content = $self->get_slot($slot);
694 print "content = $content<br/> $slot => $drive<br/>";
695 $self->set_drive($drive, $slot, $content);
698 $self->{error} = $out;
706 my ($self, $media) = @_;
708 unless ($self->{label}->{$media}) {
712 if ($self->{label}->{$media} =~ /drive\d+/) {
722 return (defined $self->{info}->{io} and $self->{info}->{io} > 0);
727 my ($self, $slot, $tag) = @_;
728 $self->{slot}->[$slot] = $tag || 'full';
729 push @{ $self->{io} }, $slot;
732 $self->{label}->{$tag} = $slot;
738 my ($self, $slot) = @_;
740 push @{ $self->{io} }, $slot;
742 unless ($self->{slot}->[$slot]) { # can be loaded (parse before)
743 $self->{slot}->[$slot] = 'empty';
749 my ($self, $slot) = @_;
750 return $self->{slot}->[$slot];
755 my ($self, $slot, $tag) = @_;
756 $self->{slot}->[$slot] = $tag || 'full';
759 $self->{label}->{$tag} = $slot;
765 my ($self, $slot) = @_;
767 unless ($self->{slot}->[$slot]) { # can be loaded (parse before)
768 $self->{slot}->[$slot] = 'empty';
774 my ($self, $drive) = @_;
775 $self->{drive}->[$drive] = 'empty';
780 my ($self, $drive, $slot, $tag) = @_;
781 $self->{drive}->[$drive] = $tag || $slot;
783 $self->{slot}->[$slot] = $tag || 'loaded';
786 $self->{label}->{$tag} = "drive$drive";
792 my ($self, $slot) = @_;
794 # slot don't exists => full
795 if (not defined $self->{slot}->[$slot]) {
799 if ($self->{slot}->[$slot] eq 'empty') {
802 return 1; # vol, full, loaded
805 sub slot_get_first_free
808 for (my $slot=1; $slot < $self->{info}->{slot}; $slot++) {
809 return $slot unless ($self->slot_is_full($slot));
813 sub io_get_first_free
817 foreach my $slot (@{ $self->{io} }) {
818 return $slot unless ($self->slot_is_full($slot));
825 my ($self, $media) = @_;
827 return $self->{label}->{$media} ;
832 my ($self, $media) = @_;
834 return defined $self->{label}->{$media} ;
839 my ($self, $slot) = @_;
841 unless ($self->slot_is_full($slot)) {
842 print "Autochanger $self->{name} slot $slot is empty\n";
847 if ($self->is_slot_loaded($slot)) {
850 print "Autochanger $self->{name} $slot is currently in use\n";
854 # autochanger must have I/O
855 unless ($self->have_io()) {
856 print "Autochanger $self->{name} don't have I/O, you can take media yourself\n";
860 my $dst = $self->io_get_first_free();
863 print "Autochanger $self->{name} you must empty I/O first\n";
866 $self->transfer($slot, $dst);
871 my ($self, $src, $dst) = @_ ;
872 if ($self->{debug}) {
873 print "<pre>$self->{precmd} $self->{mtxcmd} -f $self->{device} transfer $src $dst</pre>\n";
875 my $out = `$self->{precmd} $self->{mtxcmd} -f $self->{device} transfer $src $dst 2>&1`;
878 my $content = $self->get_slot($src);
879 $self->{slot}->[$src] = 'empty';
880 $self->set_slot($dst, $content);
883 $self->{error} = $out;
890 my ($self, $index) = @_;
891 return $self->{drive_name}->[$index];
894 # TODO : do a tapeinfo request to get informations
904 for my $slot (@{$self->{io}})
906 if ($self->is_slot_loaded($slot)) {
907 print "$slot is currently loaded\n";
911 if ($self->slot_is_full($slot))
913 my $free = $self->slot_get_first_free() ;
914 print "move $slot to $free :\n";
917 if ($self->transfer($slot, $free)) {
918 print "<img src='/bweb/T.png' alt='ok'><br/>\n";
920 print "<img src='/bweb/E.png' alt='ok' title='$self->{error}'><br/>\n";
924 $self->{error} = "<img src='/bweb/E.png' alt='ok' title='E : Can t find free slot'><br/>\n";
930 # TODO : this is with mtx status output,
931 # we can do an other function from bacula view (with StorageId)
935 my $bweb = $self->{bweb};
937 # $self->{label} => ('vol1', 'vol2', 'vol3', ..);
938 my $media_list = $bweb->dbh_join( keys %{ $self->{label} });
941 SELECT Media.VolumeName AS volumename,
942 Media.VolStatus AS volstatus,
943 Media.LastWritten AS lastwritten,
944 Media.VolBytes AS volbytes,
945 Media.MediaType AS mediatype,
947 Media.InChanger AS inchanger,
949 $bweb->{sql}->{FROM_UNIXTIME}(
950 $bweb->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
951 + $bweb->{sql}->{TO_SEC}(Media.VolRetention)
954 INNER JOIN Pool USING (PoolId)
956 WHERE Media.VolumeName IN ($media_list)
959 my $all = $bweb->dbh_selectall_hashref($query, 'volumename') ;
961 # TODO : verify slot and bacula slot
965 for (my $slot=1; $slot <= $self->{info}->{slot} ; $slot++) {
967 if ($self->slot_is_full($slot)) {
969 my $vol = $self->{slot}->[$slot];
970 if (defined $all->{$vol}) { # TODO : autochanger without barcodes
972 my $bslot = $all->{$vol}->{slot} ;
973 my $inchanger = $all->{$vol}->{inchanger};
975 # if bacula slot or inchanger flag is bad, we display a message
976 if ($bslot != $slot or !$inchanger) {
977 push @to_update, $slot;
980 $all->{$vol}->{realslot} = $slot;
982 push @{ $param }, $all->{$vol};
984 } else { # empty or no label
985 push @{ $param }, {realslot => $slot,
986 volstatus => 'Unknown',
987 volumename => $self->{slot}->[$slot]} ;
990 push @{ $param }, {realslot => $slot, volumename => 'empty'} ;
994 my $i=0; my $drives = [] ;
995 foreach my $d (@{ $self->{drive} }) {
996 $drives->[$i] = { index => $i,
997 load => $self->{drive}->[$i],
998 name => $self->{drive_name}->[$i],
1003 $bweb->display({ Name => $self->{name},
1004 nb_drive => $self->{info}->{drive},
1005 nb_io => $self->{info}->{io},
1008 Update => scalar(@to_update) },
1016 ################################################################
1020 use base q/Bweb::Gui/;
1024 Bweb - main Bweb package
1028 this package is use to compute and display informations
1033 use POSIX qw/strftime/;
1035 our $config_file='/etc/bacula/bweb.conf';
1041 %sql_func - hash to make query mysql/postgresql compliant
1047 UNIX_TIMESTAMP => '',
1048 FROM_UNIXTIME => '',
1049 TO_SEC => " interval '1 second' * ",
1050 SEC_TO_INT => "SEC_TO_INT",
1053 STARTTIME_DAY => " date_trunc('day', Job.StartTime) ",
1054 STARTTIME_HOUR => " date_trunc('hour', Job.StartTime) ",
1055 STARTTIME_MONTH => " date_trunc('month', Job.StartTime) ",
1056 STARTTIME_PHOUR=> " date_part('hour', Job.StartTime) ",
1057 STARTTIME_PDAY => " date_part('day', Job.StartTime) ",
1058 STARTTIME_PMONTH => " date_part('month', Job.StartTime) ",
1059 STARTTIME_PWEEK => " date_part('week', Job.StartTime) ",
1060 DB_SIZE => " SELECT pg_database_size(current_database()) ",
1061 CAT_POOL_TYPE => " MediaType || '_' || Pool.Name ",
1065 UNIX_TIMESTAMP => 'UNIX_TIMESTAMP',
1066 FROM_UNIXTIME => 'FROM_UNIXTIME',
1069 SEC_TO_TIME => 'SEC_TO_TIME',
1070 MATCH => " REGEXP ",
1071 STARTTIME_DAY => " DATE_FORMAT(StartTime, '%Y-%m-%d') ",
1072 STARTTIME_HOUR => " DATE_FORMAT(StartTime, '%Y-%m-%d %H') ",
1073 STARTTIME_MONTH => " DATE_FORMAT(StartTime, '%Y-%m') ",
1074 STARTTIME_PHOUR=> " DATE_FORMAT(StartTime, '%H') ",
1075 STARTTIME_PDAY => " DATE_FORMAT(StartTime, '%d') ",
1076 STARTTIME_PMONTH => " DATE_FORMAT(StartTime, '%m') ",
1077 STARTTIME_PWEEK => " DATE_FORMAT(StartTime, '%v') ",
1078 # with mysql < 5, you have to play with the ugly SHOW command
1079 DB_SIZE => " SELECT 0 ",
1080 # works only with mysql 5
1081 # DB_SIZE => " SELECT sum(DATA_LENGTH) FROM INFORMATION_SCHEMA.TABLES ",
1082 CAT_POOL_TYPE => " CONCAT(MediaType,'_',Pool.Name) ",
1083 CONCAT_SEP => " SEPARATOR '' ",
1090 return $self->{info}->{dbi} =~ /dbi:mysql/i;
1097 $self->{dbh}->disconnect();
1102 sub dbh_selectall_arrayref
1104 my ($self, $query) = @_;
1105 $self->connect_db();
1106 $self->debug($query);
1107 return $self->{dbh}->selectall_arrayref($query);
1112 my ($self, @what) = @_;
1113 return join(',', $self->dbh_quote(@what)) ;
1118 my ($self, @what) = @_;
1120 $self->connect_db();
1122 return map { $self->{dbh}->quote($_) } @what;
1124 return $self->{dbh}->quote($what[0]) ;
1130 my ($self, $query) = @_ ;
1131 $self->connect_db();
1132 $self->debug($query);
1133 return $self->{dbh}->do($query);
1136 sub dbh_selectall_hashref
1138 my ($self, $query, $join) = @_;
1140 $self->connect_db();
1141 $self->debug($query);
1142 return $self->{dbh}->selectall_hashref($query, $join) ;
1145 sub dbh_selectrow_hashref
1147 my ($self, $query) = @_;
1149 $self->connect_db();
1150 $self->debug($query);
1151 return $self->{dbh}->selectrow_hashref($query) ;
1156 my ($self, @what) = @_;
1157 if ($self->dbh_is_mysql()) {
1158 return 'CONCAT(' . join(',', @what) . ')' ;
1160 return join(' || ', @what);
1166 my ($self, $query) = @_;
1167 $self->debug($query, up => 1);
1168 return $self->{dbh}->prepare($query);
1174 my @unit = qw(B KB MB GB TB);
1175 my $val = shift || 0;
1177 my $format = '%i %s';
1178 while ($val / 1024 > 1) {
1182 $format = ($i>0)?'%0.1f %s':'%i %s';
1183 return sprintf($format, $val, $unit[$i]);
1186 # display Day, Hour, Year
1192 $val /= 60; # sec -> min
1194 if ($val / 60 <= 1) {
1198 $val /= 60; # min -> hour
1199 if ($val / 24 <= 1) {
1200 return "$val hours";
1203 $val /= 24; # hour -> day
1204 if ($val / 365 < 2) {
1208 $val /= 365 ; # day -> year
1210 return "$val years";
1216 my $val = shift || 0;
1218 if ($val eq '1' or $val eq "yes") {
1220 } elsif ($val eq '2' or $val eq "archived") {
1228 sub from_human_enabled
1230 my $val = shift || 0;
1232 if ($val eq '1' or $val eq "yes") {
1234 } elsif ($val eq '2' or $val eq "archived") {
1241 # get Day, Hour, Year
1247 unless ($val =~ /^\s*(\d+)\s*(\w)\w*\s*$/) {
1251 my %times = ( m => 60,
1257 my $mult = $times{$2} || 0;
1267 unless ($self->{dbh}) {
1269 $self->{dbh} = DBI->connect($self->{info}->{dbi},
1270 $self->{info}->{user},
1271 $self->{info}->{password});
1273 $self->error("Can't connect to your database:\n$DBI::errstr\n")
1274 unless ($self->{dbh});
1276 $self->{dbh}->{FetchHashKeyName} = 'NAME_lc';
1278 if ($self->dbh_is_mysql()) {
1279 $self->{dbh}->do("SET group_concat_max_len=1000000");
1281 $self->{dbh}->do("SET datestyle TO 'ISO, YMD'");
1288 my ($class, %arg) = @_;
1290 dbh => undef, # connect_db();
1292 dbi => '', # DBI:Pg:database=bacula;host=127.0.0.1
1298 map { $self->{lc($_)} = $arg{$_} } keys %arg ;
1300 if ($self->{info}->{dbi} =~ /DBI:(\w+):/i) {
1301 $self->{sql} = $sql_func{$1};
1304 $self->{loginname} = CGI::remote_user();
1305 $self->{debug} = $self->{info}->{debug};
1306 $Bweb::Gui::template_dir = $self->{info}->{template_dir};
1314 $self->display($self->{info}, "begin.tpl");
1320 $self->display($self->{info}, "end.tpl");
1326 my $where=''; # by default
1328 my $arg = $self->get_form("client", "qre_client",
1329 "jclient_groups", "qnotingroup");
1331 if ($arg->{qre_client}) {
1332 $where = "WHERE Name $self->{sql}->{MATCH} $arg->{qre_client} ";
1333 } elsif ($arg->{client}) {
1334 $where = "WHERE Name = '$arg->{client}' ";
1335 } elsif ($arg->{jclient_groups}) {
1336 # $filter could already contains client_group_member
1338 JOIN client_group_member USING (ClientId)
1339 JOIN client_group USING (client_group_id)
1340 WHERE client_group_name IN ($arg->{jclient_groups}) ";
1341 } elsif ($arg->{qnotingroup}) {
1344 (SELECT 1 FROM client_group_member
1345 WHERE Client.ClientId = client_group_member.ClientId
1351 SELECT Name AS name,
1353 AutoPrune AS autoprune,
1354 FileRetention AS fileretention,
1355 JobRetention AS jobretention
1356 FROM Client " . $self->get_client_filter() .
1359 my $all = $self->dbh_selectall_hashref($query, 'name') ;
1361 my $dsp = { ID => $cur_id++,
1362 clients => [ values %$all] };
1364 $self->display($dsp, "client_list.tpl") ;
1369 my ($self, %arg) = @_;
1376 "AND $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
1378 ( $self->{sql}->{UNIX_TIMESTAMP}(NOW())
1380 $self->{sql}->{TO_SEC}($arg{age})
1383 $label = "last " . human_sec($arg{age});
1386 if ($arg{groupby}) {
1387 $limit .= " GROUP BY $arg{groupby} ";
1391 $limit .= " ORDER BY $arg{order} ";
1395 $limit .= " LIMIT $arg{limit} ";
1396 $label .= " limited to $arg{limit}";
1400 $limit .= " OFFSET $arg{offset} ";
1401 $label .= " with $arg{offset} offset ";
1405 $label = 'no filter';
1408 return ($limit, $label);
1413 $bweb->get_form(...) - Get useful stuff
1417 This function get and check parameters against regexp.
1419 If word begin with 'q', the return will be quoted or join quoted
1420 if it's end with 's'.
1425 $bweb->get_form('jobid', 'qclient', 'qpools') ;
1428 qclient => 'plume-fd',
1429 qpools => "'plume-fd', 'test-fd', '...'",
1436 my ($self, @what) = @_;
1437 my %what = map { $_ => 1 } @what;
1460 my %opt_ss =( # string with space
1464 my %opt_s = ( # default to ''
1485 my %opt_p = ( # option with path
1492 my %opt_r = (regexwhere => 1);
1494 my %opt_d = ( # option with date
1499 foreach my $i (@what) {
1500 if (exists $opt_i{$i}) {# integer param
1501 my $value = CGI::param($i) || $opt_i{$i} ;
1502 if ($value =~ /^(\d+)$/) {
1505 } elsif ($opt_s{$i}) { # simple string param
1506 my $value = CGI::param($i) || '';
1507 if ($value =~ /^([\w\d\.-]+)$/) {
1510 } elsif ($opt_ss{$i}) { # simple string param (with space)
1511 my $value = CGI::param($i) || '';
1512 if ($value =~ /^([\w\d\.\-\s]+)$/) {
1515 } elsif ($i =~ /^j(\w+)s$/) { # quote join args "'arg1', 'arg2'"
1516 my @value = grep { ! /^\s*$/ } CGI::param($1) ;
1518 $ret{$i} = $self->dbh_join(@value) ;
1521 } elsif ($i =~ /^q(\w+[^s])$/) { # 'arg1'
1522 my $value = CGI::param($1) ;
1524 $ret{$i} = $self->dbh_quote($value);
1527 } elsif ($i =~ /^q(\w+)s$/) { #[ 'arg1', 'arg2']
1528 $ret{$i} = [ map { { name => $self->dbh_quote($_) } }
1529 grep { ! /^\s*$/ } CGI::param($1) ];
1530 } elsif (exists $opt_p{$i}) {
1531 my $value = CGI::param($i) || '';
1532 if ($value =~ /^([\w\d\.\/\s:\@\-]+)$/) {
1535 } elsif (exists $opt_r{$i}) {
1536 my $value = CGI::param($i) || '';
1537 if ($value =~ /^([^'"']+)$/) {
1540 } elsif (exists $opt_d{$i}) {
1541 my $value = CGI::param($i) || '';
1542 if ($value =~ /^\s*(\d+\s+\w+)$/) {
1549 foreach my $s (CGI::param('slot')) {
1550 if ($s =~ /^(\d+)$/) {
1551 push @{$ret{slots}}, $s;
1557 my $when = CGI::param('when') || '';
1558 if ($when =~ /^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})$/) {
1563 if ($what{db_clients}) {
1565 if ($what{filter}) {
1566 # get security filter only if asked
1567 $filter = $self->get_client_filter();
1571 SELECT Client.Name as clientname
1575 my $clients = $self->dbh_selectall_hashref($query, 'clientname');
1576 $ret{db_clients} = [sort {$a->{clientname} cmp $b->{clientname} }
1580 if ($what{db_client_groups}) {
1582 if ($what{filter}) {
1583 # get security filter only if asked
1584 $filter = $self->get_client_group_filter();
1588 SELECT client_group_name AS name
1589 FROM client_group $filter
1592 my $grps = $self->dbh_selectall_hashref($query, 'name');
1593 $ret{db_client_groups} = [sort {$a->{name} cmp $b->{name} }
1597 if ($what{db_usernames}) {
1603 my $users = $self->dbh_selectall_hashref($query, 'username');
1604 $ret{db_usernames} = [sort {$a->{username} cmp $b->{username} }
1608 if ($what{db_roles}) {
1614 my $r = $self->dbh_selectall_hashref($query, 'rolename');
1615 $ret{db_roles} = [sort {$a->{rolename} cmp $b->{rolename} }
1619 if ($what{db_mediatypes}) {
1621 SELECT MediaType as mediatype
1625 my $media = $self->dbh_selectall_hashref($query, 'mediatype');
1626 $ret{db_mediatypes} = [sort {$a->{mediatype} cmp $b->{mediatype} }
1630 if ($what{db_locations}) {
1632 SELECT Location as location, Cost as cost
1635 my $loc = $self->dbh_selectall_hashref($query, 'location');
1636 $ret{db_locations} = [ sort { $a->{location}
1642 if ($what{db_pools}) {
1643 my $query = "SELECT Name as name FROM Pool";
1645 my $all = $self->dbh_selectall_hashref($query, 'name') ;
1646 $ret{db_pools} = [ sort { $a->{name} cmp $b->{name} } values %$all ];
1649 if ($what{db_filesets}) {
1651 SELECT FileSet.FileSet AS fileset
1655 my $filesets = $self->dbh_selectall_hashref($query, 'fileset');
1657 $ret{db_filesets} = [sort {lc($a->{fileset}) cmp lc($b->{fileset}) }
1658 values %$filesets] ;
1661 if ($what{db_jobnames}) {
1663 if ($what{filter}) {
1664 $filter = " JOIN Client USING (ClientId) " . $self->get_client_filter();
1667 SELECT DISTINCT Job.Name AS jobname
1671 my $jobnames = $self->dbh_selectall_hashref($query, 'jobname');
1673 $ret{db_jobnames} = [sort {lc($a->{jobname}) cmp lc($b->{jobname}) }
1674 values %$jobnames] ;
1677 if ($what{db_devices}) {
1679 SELECT Device.Name AS name
1683 my $devices = $self->dbh_selectall_hashref($query, 'name');
1685 $ret{db_devices} = [sort {lc($a->{name}) cmp lc($b->{name}) }
1695 $self->can_do('r_view_stat');
1696 my $fields = $self->get_form(qw/age level status clients filesets
1697 graph gtype type filter db_clients
1698 limit db_filesets width height
1699 qclients qfilesets qjobnames db_jobnames/);
1702 my $url = CGI::url(-full => 0,
1705 $url =~ s/^.+?\?//; # http://path/to/bweb.pl?arg => arg
1707 # this organisation is to keep user choice between 2 click
1708 # TODO : fileset and client selection doesn't work
1717 sub get_selected_media_location
1721 my $media = $self->get_form('jmedias');
1723 unless ($media->{jmedias}) {
1728 SELECT Media.VolumeName AS volumename, Location.Location AS location
1729 FROM Media LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
1730 WHERE Media.VolumeName IN ($media->{jmedias})
1733 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1735 # { 'vol1' => { [volumename => 'vol1', location => 'ici'],
1744 my ($self, $in) = @_ ;
1745 $self->can_do('r_media_mgnt');
1746 my $media = $self->get_selected_media_location();
1752 my $elt = $self->get_form('db_locations');
1754 $self->display({ ID => $cur_id++,
1755 enabled => human_enabled($in),
1756 %$elt, # db_locations
1758 sort { $a->{volumename} cmp $b->{volumename} } values %$media
1767 $self->can_do('r_media_mgnt');
1769 my $elt = $self->get_form(qw/db_pools db_mediatypes db_locations/) ;
1771 $self->display($elt, "help_extern.tpl");
1774 sub help_extern_compute
1777 $self->can_do('r_media_mgnt');
1779 my $number = CGI::param('limit') || '' ;
1780 unless ($number =~ /^(\d+)$/) {
1781 return $self->error("Bad arg number : $number ");
1784 my ($sql, undef) = $self->get_param('pools',
1785 'locations', 'mediatypes');
1788 SELECT Media.VolumeName AS volumename,
1789 Media.VolStatus AS volstatus,
1790 Media.LastWritten AS lastwritten,
1791 Media.MediaType AS mediatype,
1792 Media.VolMounts AS volmounts,
1794 Media.Recycle AS recycle,
1795 $self->{sql}->{FROM_UNIXTIME}(
1796 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1797 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1800 INNER JOIN Pool ON (Pool.PoolId = Media.PoolId)
1801 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
1803 WHERE Media.InChanger = 1
1804 AND Media.VolStatus IN ('Disabled', 'Error', 'Full')
1806 ORDER BY expire DESC, recycle, Media.VolMounts DESC
1810 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1812 $self->display({ Media => [ values %$all ] },
1813 "help_extern_compute.tpl");
1819 $self->can_do('r_media_mgnt');
1821 my $param = $self->get_form(qw/db_locations db_pools db_mediatypes/) ;
1822 $self->display($param, "help_intern.tpl");
1825 sub help_intern_compute
1828 $self->can_do('r_media_mgnt');
1830 my $number = CGI::param('limit') || '' ;
1831 unless ($number =~ /^(\d+)$/) {
1832 return $self->error("Bad arg number : $number ");
1835 my ($sql, undef) = $self->get_param('pools', 'locations', 'mediatypes');
1837 if (CGI::param('expired')) {
1839 AND ( $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1840 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1846 SELECT Media.VolumeName AS volumename,
1847 Media.VolStatus AS volstatus,
1848 Media.LastWritten AS lastwritten,
1849 Media.MediaType AS mediatype,
1850 Media.VolMounts AS volmounts,
1852 $self->{sql}->{FROM_UNIXTIME}(
1853 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1854 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1857 INNER JOIN Pool ON (Pool.PoolId = Media.PoolId)
1858 LEFT JOIN Location ON (Location.LocationId = Media.LocationId)
1860 WHERE Media.InChanger <> 1
1861 AND Media.VolStatus IN ('Purged', 'Full', 'Append')
1862 AND Media.Recycle = 1
1864 ORDER BY Media.VolUseDuration DESC, Media.VolMounts ASC, expire ASC
1868 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1870 $self->display({ Media => [ values %$all ] },
1871 "help_intern_compute.tpl");
1877 my ($self, %arg) = @_ ;
1879 my ($limit, $label) = $self->get_limit(%arg);
1883 (SELECT count(Pool.PoolId) FROM Pool) AS nb_pool,
1884 (SELECT count(Media.MediaId) FROM Media) AS nb_media,
1885 (SELECT count(Job.JobId) FROM Job) AS nb_job,
1886 (SELECT sum(VolBytes) FROM Media) AS nb_bytes,
1887 ($self->{sql}->{DB_SIZE}) AS db_size,
1888 (SELECT count(Job.JobId)
1890 WHERE Job.JobStatus IN ('E','e','f','A')
1893 (SELECT count(Client.ClientId) FROM Client) AS nb_client
1896 my $row = $self->dbh_selectrow_hashref($query) ;
1898 $row->{nb_bytes} = human_size($row->{nb_bytes});
1900 $row->{db_size} = human_size($row->{db_size});
1901 $row->{label} = $label;
1903 $self->display($row, "general.tpl");
1908 my ($self, @what) = @_ ;
1909 my %elt = map { $_ => 1 } @what;
1914 if ($elt{clients}) {
1915 my @clients = grep { ! /^\s*$/ } CGI::param('client');
1917 $ret{clients} = \@clients;
1918 my $str = $self->dbh_join(@clients);
1919 $limit .= "AND Client.Name IN ($str) ";
1923 if ($elt{client_groups}) {
1924 my @clients = grep { ! /^\s*$/ } CGI::param('client_group');
1926 $ret{client_groups} = \@clients;
1927 my $str = $self->dbh_join(@clients);
1928 $limit .= "AND client_group_name IN ($str) ";
1932 if ($elt{filesets}) {
1933 my @filesets = grep { ! /^\s*$/ } CGI::param('fileset');
1935 $ret{filesets} = \@filesets;
1936 my $str = $self->dbh_join(@filesets);
1937 $limit .= "AND FileSet.FileSet IN ($str) ";
1941 if ($elt{mediatypes}) {
1942 my @media = grep { ! /^\s*$/ } CGI::param('mediatype');
1944 $ret{mediatypes} = \@media;
1945 my $str = $self->dbh_join(@media);
1946 $limit .= "AND Media.MediaType IN ($str) ";
1951 my $client = CGI::param('client');
1952 $ret{client} = $client;
1953 $client = $self->dbh_join($client);
1954 $limit .= "AND Client.Name = $client ";
1958 my $level = CGI::param('level') || '';
1959 if ($level =~ /^(\w)$/) {
1961 $limit .= "AND Job.Level = '$1' ";
1966 my $jobid = CGI::param('jobid') || '';
1968 if ($jobid =~ /^(\d+)$/) {
1970 $limit .= "AND Job.JobId = '$1' ";
1975 my $status = CGI::param('status') || '';
1976 if ($status =~ /^(\w)$/) {
1979 $limit .= "AND Job.JobStatus IN ('f','E') ";
1980 } elsif ($1 eq 'W') {
1981 $limit .= "AND Job.JobStatus = 'T' AND Job.JobErrors > 0 ";
1983 $limit .= "AND Job.JobStatus = '$1' ";
1988 if ($elt{volstatus}) {
1989 my $status = CGI::param('volstatus') || '';
1990 if ($status =~ /^(\w+)$/) {
1992 $limit .= "AND Media.VolStatus = '$1' ";
1996 if ($elt{locations}) {
1997 my @location = grep { ! /^\s*$/ } CGI::param('location') ;
1999 $ret{locations} = \@location;
2000 my $str = $self->dbh_join(@location);
2001 $limit .= "AND Location.Location IN ($str) ";
2006 my @pool = grep { ! /^\s*$/ } CGI::param('pool') ;
2008 $ret{pools} = \@pool;
2009 my $str = $self->dbh_join(@pool);
2010 $limit .= "AND Pool.Name IN ($str) ";
2014 if ($elt{location}) {
2015 my $location = CGI::param('location') || '';
2017 $ret{location} = $location;
2018 $location = $self->dbh_quote($location);
2019 $limit .= "AND Location.Location = $location ";
2024 my $pool = CGI::param('pool') || '';
2027 $pool = $self->dbh_quote($pool);
2028 $limit .= "AND Pool.Name = $pool ";
2032 if ($elt{jobtype}) {
2033 my $jobtype = CGI::param('jobtype') || '';
2034 if ($jobtype =~ /^(\w)$/) {
2036 $limit .= "AND Job.Type = '$1' ";
2040 return ($limit, %ret);
2051 my ($self, %arg) = @_ ;
2052 return if $self->cant_do('r_view_job');
2054 $arg{order} = ' Job.JobId DESC ';
2056 my ($limit, $label) = $self->get_limit(%arg);
2057 my ($where, undef) = $self->get_param('clients',
2066 if (CGI::param('client_group')) {
2068 JOIN client_group_member USING (ClientId)
2069 JOIN client_group USING (client_group_id)
2072 my $filter = $self->get_client_filter();
2075 SELECT Job.JobId AS jobid,
2076 Client.Name AS client,
2077 FileSet.FileSet AS fileset,
2078 Job.Name AS jobname,
2080 StartTime AS starttime,
2082 Pool.Name AS poolname,
2083 JobFiles AS jobfiles,
2084 JobBytes AS jobbytes,
2085 JobStatus AS jobstatus,
2086 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
2087 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime))
2090 JobErrors AS joberrors
2092 FROM Client $filter $cgq,
2093 Job LEFT JOIN Pool ON (Job.PoolId = Pool.PoolId)
2094 LEFT JOIN FileSet ON (Job.FileSetId = FileSet.FileSetId)
2095 WHERE Client.ClientId=Job.ClientId
2096 AND Job.JobStatus NOT IN ('R', 'C')
2101 my $all = $self->dbh_selectall_hashref($query, 'jobid') ;
2103 $self->display({ Filter => $label,
2107 sort { $a->{jobid} <=> $b->{jobid} }
2114 # display job informations
2115 sub display_job_zoom
2117 my ($self, $jobid) = @_ ;
2118 $self->can_do('r_view_job');
2120 $jobid = $self->dbh_quote($jobid);
2122 # get security filter
2123 my $filter = $self->get_client_filter();
2126 SELECT DISTINCT Job.JobId AS jobid,
2127 Client.Name AS client,
2128 Job.Name AS jobname,
2129 FileSet.FileSet AS fileset,
2131 Pool.Name AS poolname,
2132 StartTime AS starttime,
2133 JobFiles AS jobfiles,
2134 JobBytes AS jobbytes,
2135 JobStatus AS jobstatus,
2136 JobErrors AS joberrors,
2137 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
2138 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime)) AS duration
2140 FROM Client $filter,
2141 Job LEFT JOIN FileSet ON (Job.FileSetId = FileSet.FileSetId)
2142 LEFT JOIN Pool ON (Job.PoolId = Pool.PoolId)
2143 WHERE Client.ClientId=Job.ClientId
2144 AND Job.JobId = $jobid
2147 my $row = $self->dbh_selectrow_hashref($query) ;
2149 # display all volumes associate with this job
2151 SELECT Media.VolumeName as volumename
2152 FROM Job,Media,JobMedia
2153 WHERE Job.JobId = $jobid
2154 AND JobMedia.JobId=Job.JobId
2155 AND JobMedia.MediaId=Media.MediaId
2158 my $all = $self->dbh_selectall_hashref($query, 'volumename');
2160 $row->{volumes} = [ values %$all ] ;
2161 $row->{wiki_url} = $self->{info}->{wiki_url};
2163 $self->display($row, "display_job_zoom.tpl");
2166 sub display_job_group
2168 my ($self, %arg) = @_;
2169 $self->can_do('r_view_job');
2171 my ($limit, $label) = $self->get_limit(groupby => 'client_group_name', %arg);
2173 my ($where, undef) = $self->get_param('client_groups',
2176 my $filter = $self->get_client_group_filter();
2179 SELECT client_group_name AS client_group_name,
2180 COALESCE(jobok.jobfiles,0) + COALESCE(joberr.jobfiles,0) AS jobfiles,
2181 COALESCE(jobok.jobbytes,0) + COALESCE(joberr.jobbytes,0) AS jobbytes,
2182 COALESCE(jobok.joberrors,0) + COALESCE(joberr.joberrors,0) AS joberrors,
2183 COALESCE(jobok.nbjobs,0) AS nbjobok,
2184 COALESCE(joberr.nbjobs,0) AS nbjoberr,
2185 COALESCE(jobok.duration, '0:0:0') AS duration
2187 FROM client_group $filter LEFT JOIN (
2188 SELECT client_group_name AS client_group_name, COUNT(1) AS nbjobs,
2189 SUM(JobFiles) AS jobfiles, SUM(JobBytes) AS jobbytes,
2190 SUM(JobErrors) AS joberrors,
2191 SUM($self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
2192 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime)))
2195 FROM Job JOIN client_group_member ON (Job.ClientId = client_group_member.ClientId)
2196 JOIN client_group USING (client_group_id)
2198 WHERE JobStatus = 'T'
2201 ) AS jobok USING (client_group_name) LEFT JOIN
2204 SELECT client_group_name AS client_group_name, COUNT(1) AS nbjobs,
2205 SUM(JobFiles) AS jobfiles, SUM(JobBytes) AS jobbytes,
2206 SUM(JobErrors) AS joberrors
2207 FROM Job JOIN client_group_member ON (Job.ClientId = client_group_member.ClientId)
2208 JOIN client_group USING (client_group_id)
2210 WHERE JobStatus IN ('f','E', 'A')
2213 ) AS joberr USING (client_group_name)
2217 my $all = $self->dbh_selectall_hashref($query, 'client_group_name');
2219 my $rep = { groups => [ values %$all ], age => $arg{age}, filter => $label };
2222 $self->display($rep, "display_job_group.tpl");
2227 my ($self, %arg) = @_ ;
2228 $self->can_do('r_view_media');
2230 my ($limit, $label) = $self->get_limit(%arg);
2231 my ($where, %elt) = $self->get_param('pools',
2236 my $arg = $self->get_form('jmedias', 'qre_media', 'expired');
2238 if ($arg->{jmedias}) {
2239 $where = "AND Media.VolumeName IN ($arg->{jmedias}) $where";
2241 if ($arg->{qre_media}) {
2242 $where = "AND Media.VolumeName $self->{sql}->{MATCH} $arg->{qre_media} $where";
2244 if ($arg->{expired}) {
2246 AND VolStatus = 'Full'
2247 AND ( $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2248 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2249 ) < NOW() " . $where ;
2253 SELECT Media.VolumeName AS volumename,
2254 Media.VolBytes AS volbytes,
2255 Media.VolStatus AS volstatus,
2256 Media.MediaType AS mediatype,
2257 Media.InChanger AS online,
2258 Media.LastWritten AS lastwritten,
2259 Location.Location AS location,
2260 (volbytes*100/COALESCE(media_avg_size.size,-1)) AS volusage,
2261 Pool.Name AS poolname,
2262 $self->{sql}->{FROM_UNIXTIME}(
2263 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2264 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2267 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2268 LEFT JOIN (SELECT avg(Media.VolBytes) AS size,
2269 Media.MediaType AS MediaType
2271 WHERE Media.VolStatus = 'Full'
2272 GROUP BY Media.MediaType
2273 ) AS media_avg_size ON (Media.MediaType = media_avg_size.MediaType)
2275 WHERE Media.PoolId=Pool.PoolId
2280 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
2282 $self->display({ ID => $cur_id++,
2284 Location => $elt{location},
2285 Media => [ values %$all ],
2287 "display_media.tpl");
2290 sub display_allmedia
2294 my $pool = $self->get_form('db_pools');
2296 foreach my $name (@{ $pool->{db_pools} }) {
2297 CGI::param('pool', $name->{name});
2298 $self->display_media();
2302 sub display_media_zoom
2306 my $media = $self->get_form('jmedias');
2308 unless ($media->{jmedias}) {
2309 return $self->error("Can't get media selection");
2313 SELECT InChanger AS online,
2314 Media.Enabled AS enabled,
2315 VolBytes AS nb_bytes,
2316 VolumeName AS volumename,
2317 VolStatus AS volstatus,
2318 VolMounts AS nb_mounts,
2319 Media.VolUseDuration AS voluseduration,
2320 Media.MaxVolJobs AS maxvoljobs,
2321 Media.MaxVolFiles AS maxvolfiles,
2322 Media.MaxVolBytes AS maxvolbytes,
2323 VolErrors AS nb_errors,
2324 Pool.Name AS poolname,
2325 Location.Location AS location,
2326 Media.Recycle AS recycle,
2327 Media.VolRetention AS volretention,
2328 Media.LastWritten AS lastwritten,
2329 Media.VolReadTime/1000000 AS volreadtime,
2330 Media.VolWriteTime/1000000 AS volwritetime,
2331 Media.RecycleCount AS recyclecount,
2332 Media.Comment AS comment,
2333 $self->{sql}->{FROM_UNIXTIME}(
2334 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2335 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2338 Media LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2339 WHERE Pool.PoolId = Media.PoolId
2340 AND VolumeName IN ($media->{jmedias})
2343 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
2345 foreach my $media (values %$all) {
2346 my $mq = $self->dbh_quote($media->{volumename});
2349 SELECT DISTINCT Job.JobId AS jobid,
2351 Job.StartTime AS starttime,
2354 Job.JobFiles AS files,
2355 Job.JobBytes AS bytes,
2356 Job.jobstatus AS status
2357 FROM Media,JobMedia,Job
2358 WHERE Media.VolumeName=$mq
2359 AND Media.MediaId=JobMedia.MediaId
2360 AND JobMedia.JobId=Job.JobId
2363 my $jobs = $self->dbh_selectall_hashref($query, 'jobid') ;
2366 SELECT LocationLog.Date AS date,
2367 Location.Location AS location,
2368 LocationLog.Comment AS comment
2369 FROM Media,LocationLog INNER JOIN Location ON (LocationLog.LocationId = Location.LocationId)
2370 WHERE Media.MediaId = LocationLog.MediaId
2371 AND Media.VolumeName = $mq
2375 my $log = $self->dbh_selectall_arrayref($query) ;
2377 $logtxt = join("\n", map { ($_->[0] . ' ' . $_->[1] . ' ' . $_->[2])} @$log ) ;
2380 $self->display({ jobs => [ values %$jobs ],
2381 LocationLog => $logtxt,
2383 "display_media_zoom.tpl");
2390 $self->can_do('r_location_mgnt');
2392 my $loc = $self->get_form('qlocation');
2393 unless ($loc->{qlocation}) {
2394 return $self->error("Can't get location");
2398 SELECT Location.Location AS location,
2399 Location.Cost AS cost,
2400 Location.Enabled AS enabled
2402 WHERE Location.Location = $loc->{qlocation}
2405 my $row = $self->dbh_selectrow_hashref($query);
2406 $row->{enabled} = human_enabled($row->{enabled});
2407 $self->display({ ID => $cur_id++,
2408 %$row }, "location_edit.tpl") ;
2414 $self->can_do('r_location_mgnt');
2416 my $arg = $self->get_form(qw/qlocation qnewlocation cost enabled/) ;
2417 unless ($arg->{qlocation}) {
2418 return $self->error("Can't get location");
2420 unless ($arg->{qnewlocation}) {
2421 return $self->error("Can't get new location name");
2423 unless ($arg->{cost}) {
2424 return $self->error("Can't get new cost");
2427 my $enabled = from_human_enabled($arg->{enabled});
2430 UPDATE Location SET Cost = $arg->{cost},
2431 Location = $arg->{qnewlocation},
2433 WHERE Location.Location = $arg->{qlocation}
2436 $self->dbh_do($query);
2438 $self->location_display();
2444 $self->can_do('r_location_mgnt');
2446 my $arg = $self->get_form(qw/qlocation/) ;
2448 unless ($arg->{qlocation}) {
2449 return $self->error("Can't get location");
2453 SELECT count(Media.MediaId) AS nb
2454 FROM Media INNER JOIN Location USING (LocationID)
2455 WHERE Location = $arg->{qlocation}
2458 my $res = $self->dbh_selectrow_hashref($query);
2461 return $self->error("Sorry, the location must be empty");
2465 DELETE FROM Location WHERE Location = $arg->{qlocation}
2468 $self->dbh_do($query);
2470 $self->location_display();
2476 $self->can_do('r_location_mgnt');
2478 my $arg = $self->get_form(qw/qlocation cost/) ;
2480 unless ($arg->{qlocation}) {
2481 $self->display({}, "location_add.tpl");
2484 unless ($arg->{cost}) {
2485 return $self->error("Can't get new cost");
2488 my $enabled = CGI::param('enabled') || '';
2489 $enabled = from_human_enabled($enabled);
2492 INSERT INTO Location (Location, Cost, Enabled)
2493 VALUES ($arg->{qlocation}, $arg->{cost}, $enabled)
2496 $self->dbh_do($query);
2498 $self->location_display();
2501 sub location_display
2506 SELECT Location.Location AS location,
2507 Location.Cost AS cost,
2508 Location.Enabled AS enabled,
2509 (SELECT count(Media.MediaId)
2511 WHERE Media.LocationId = Location.LocationId
2516 my $location = $self->dbh_selectall_hashref($query, 'location');
2518 $self->display({ ID => $cur_id++,
2519 Locations => [ values %$location ] },
2520 "display_location.tpl");
2527 my $media = $self->get_selected_media_location();
2532 my $arg = $self->get_form('db_locations', 'qnewlocation');
2534 $self->display({ email => $self->{info}->{email_media},
2536 media => [ values %$media ],
2538 "update_location.tpl");
2541 ###########################################################
2546 $self->can_do('r_group_mgnt');
2548 my $grp = $self->get_form(qw/qclient_group db_clients/);
2550 unless ($grp->{qclient_group}) {
2551 $self->display({ ID => $cur_id++,
2552 client_group => "''",
2554 }, "groups_edit.tpl");
2560 FROM Client JOIN client_group_member using (clientid)
2561 JOIN client_group using (client_group_id)
2562 WHERE client_group_name = $grp->{qclient_group}
2565 my $row = $self->dbh_selectall_hashref($query, "name");
2567 $self->display({ ID => $cur_id++,
2568 client_group => $grp->{qclient_group},
2570 client_group_member => [ values %$row]},
2577 $self->can_do('r_group_mgnt');
2579 my $arg = $self->get_form(qw/qclient_group jclients qnewgroup/);
2581 if (!$arg->{qclient_group} and $arg->{qnewgroup}) {
2583 INSERT INTO client_group (client_group_name)
2584 VALUES ($arg->{qnewgroup})
2586 $self->dbh_do($query);
2587 $arg->{qclient_group} = $arg->{qnewgroup};
2590 unless ($arg->{qclient_group}) {
2591 return $self->error("Can't get groups");
2594 $self->{dbh}->begin_work();
2597 DELETE FROM client_group_member
2598 WHERE client_group_id IN
2599 (SELECT client_group_id
2601 WHERE client_group_name = $arg->{qclient_group})
2603 $self->dbh_do($query);
2605 if ($arg->{jclients}) {
2607 INSERT INTO client_group_member (clientid, client_group_id)
2609 (SELECT client_group_id
2611 WHERE client_group_name = $arg->{qclient_group})
2612 FROM Client WHERE Name IN ($arg->{jclients})
2615 $self->dbh_do($query);
2617 if ($arg->{qclient_group} ne $arg->{qnewgroup}) {
2620 SET client_group_name = $arg->{qnewgroup}
2621 WHERE client_group_name = $arg->{qclient_group}
2624 $self->dbh_do($query);
2627 $self->{dbh}->commit() or $self->error($self->{dbh}->errstr);
2629 $self->display_groups();
2635 $self->can_do('r_group_mgnt');
2637 my $arg = $self->get_form(qw/qclient_group/);
2639 unless ($arg->{qclient_group}) {
2640 return $self->error("Can't get groups");
2643 $self->{dbh}->begin_work();
2646 DELETE FROM client_group_member
2647 WHERE client_group_id IN
2648 (SELECT client_group_id
2650 WHERE client_group_name = $arg->{qclient_group})");
2653 DELETE FROM bweb_client_group_acl
2654 WHERE client_group_id IN
2655 (SELECT client_group_id
2657 WHERE client_group_name = $arg->{qclient_group})");
2660 DELETE FROM client_group
2661 WHERE client_group_name = $arg->{qclient_group}");
2663 $self->{dbh}->commit();
2664 $self->display_groups();
2672 if ($self->cant_do('r_group_mgnt')) {
2673 $arg = $self->get_form(qw/db_client_groups filter/) ;
2675 $arg = $self->get_form(qw/db_client_groups/) ;
2678 if ($self->{dbh}->errstr) {
2679 return $self->error("Can't use groups with bweb, read INSTALL to enable them");
2684 $self->display({ ID => $cur_id++,
2686 "display_groups.tpl");
2689 ###########################################################
2694 if (not $self->{info}->{enable_security}) {
2697 # admin is a special user that can do everything
2698 if ($self->{loginname} eq 'admin') {
2701 if (!$self->{loginname}) {
2702 $self->error("Can't get your login name");
2703 $self->display_end();
2707 if (defined $self->{security}) {
2710 $self->{security} = {};
2711 my $u = $self->dbh_quote($self->{loginname});
2714 SELECT use_acl, rolename
2716 JOIN bweb_role_member USING (userid)
2717 JOIN bweb_role USING (roleid)
2720 my $rows = $self->dbh_selectall_arrayref($query);
2721 # do cache with this role
2722 if (!$rows or !scalar(@$rows)) {
2723 $self->error("Can't get $self->{loginname}'s roles");
2724 $self->display_end();
2727 foreach my $r (@$rows) {
2728 $self->{security}->{$r->[1]}=1;
2731 $self->{security}->{use_acl} = $rows->[0]->[0];
2737 my ($self, $action) = @_;
2738 # is security enabled in configuration ?
2739 if (not $self->{info}->{enable_security}) {
2742 # admin is a special user that can do everything
2743 if ($self->{loginname} eq 'admin') {
2747 if (!$self->{loginname}) {
2748 $self->{error} = "Can't do $action, your are not logged. " .
2749 "Check security with your administrator";
2752 if (!$self->get_roles()) {
2755 if (!$self->{security}->{$action}) {
2757 "$self->{loginname} sorry, but this action ($action) " .
2758 "is not permited. " .
2759 "Check security with your administrator";
2765 # make like an assert (program die)
2768 my ($self, $action) = @_;
2769 if ($self->cant_do($action)) {
2770 $self->error($self->{error});
2771 $self->display_end();
2781 if (!$self->{info}->{enable_security} or
2782 !$self->{info}->{enable_security_acl})
2787 if ($self->get_roles()) {
2788 return $self->{security}->{use_acl};
2794 # JOIN Client USING (ClientId) " . $b->get_client_filter() . "
2795 sub get_client_filter
2797 my ($self, $login) = @_;
2800 $u = $self->dbh_quote($login);
2801 } elsif ($self->use_filter()) {
2802 $u = $self->dbh_quote($self->{loginname});
2807 JOIN (SELECT ClientId FROM client_group_member
2808 JOIN client_group USING (client_group_id)
2809 JOIN bweb_client_group_acl USING (client_group_id)
2810 JOIN bweb_user USING (userid)
2811 WHERE bweb_user.username = $u
2812 ) AS filter USING (ClientId)";
2815 #JOIN client_group USING (client_group_id)" . $b->get_client_group_filter()
2816 sub get_client_group_filter
2818 my ($self, $login) = @_;
2821 $u = $self->dbh_quote($login);
2822 } elsif ($self->use_filter()) {
2823 $u = $self->dbh_quote($self->{loginname});
2828 JOIN (SELECT client_group_id
2829 FROM bweb_client_group_acl
2830 JOIN bweb_user USING (userid)
2831 WHERE bweb_user.username = $u
2832 ) AS filter USING (client_group_id)";
2835 # role and username have to be quoted before
2836 # role and username can be a quoted list
2839 my ($self, $role, $username) = @_;
2840 $self->can_do("r_user_mgnt");
2842 my $nb = $self->dbh_do("
2843 DELETE FROM bweb_role_member
2844 WHERE roleid = (SELECT roleid FROM bweb_role
2845 WHERE rolename IN ($role))
2846 AND userid = (SELECT userid FROM bweb_user
2847 WHERE username IN ($username))");
2851 # role and username have to be quoted before
2852 # role and username can be a quoted list
2855 my ($self, $role, $username) = @_;
2856 $self->can_do("r_user_mgnt");
2858 my $nb = $self->dbh_do("
2859 INSERT INTO bweb_role_member (roleid, userid)
2860 SELECT roleid, userid FROM bweb_role, bweb_user
2861 WHERE rolename IN ($role)
2862 AND username IN ($username)
2867 # role and username have to be quoted before
2868 # role and username can be a quoted list
2871 my ($self, $copy, $user) = @_;
2872 $self->can_do("r_user_mgnt");
2874 my $nb = $self->dbh_do("
2875 INSERT INTO bweb_role_member (roleid, userid)
2876 SELECT roleid, a.userid
2877 FROM bweb_user AS a, bweb_role_member
2878 JOIN bweb_user USING (userid)
2879 WHERE bweb_user.username = $copy
2880 AND a.username = $user");
2884 # username can be a join quoted list of usernames
2887 my ($self, $username) = @_;
2888 $self->can_do("r_user_mgnt");
2891 DELETE FROM bweb_role_member
2895 WHERE username in ($username))");
2897 DELETE FROM bweb_client_group_acl
2901 WHERE username IN ($username))");
2908 $self->can_do("r_user_mgnt");
2910 my $arg = $self->get_form(qw/jusernames/);
2912 unless ($arg->{jusernames}) {
2913 return $self->error("Can't get user");
2916 $self->{dbh}->begin_work();
2918 $self->revoke_all($arg->{jusernames});
2920 DELETE FROM bweb_user WHERE username IN ($arg->{jusernames})");
2922 $self->{dbh}->commit();
2924 $self->display_users();
2930 $self->can_do("r_user_mgnt");
2932 # we don't quote username directly to check that it is conform
2933 my $arg = $self->get_form(qw/username qpasswd qcomment jrolenames qcreate qcopy_username jclient_groups/) ;
2935 if (not $arg->{qcreate}) {
2936 $arg = $self->get_form(qw/db_roles db_usernames db_client_groups/);
2937 $self->display($arg, "display_user.tpl");
2941 my $u = $self->dbh_quote($arg->{username});
2943 $arg->{use_acl}=(CGI::param('use_acl')?'true':'false');
2945 if (!$arg->{qpasswd}) {
2946 $arg->{qpasswd} = "''";
2948 if (!$arg->{qcomment}) {
2949 $arg->{qcomment} = "''";
2952 # will fail if user already exists
2953 # UPDATE with mysql dbi does not return if update is ok
2956 SET passwd=$arg->{qpasswd}, comment=$arg->{qcomment},
2957 use_acl=$arg->{use_acl}
2958 WHERE username = $u")
2959 # and (! $self->dbh_is_mysql() )
2962 INSERT INTO bweb_user (username, passwd, use_acl, comment)
2963 VALUES ($u, $arg->{qpasswd}, $arg->{use_acl}, $arg->{qcomment})");
2965 $self->{dbh}->begin_work();
2967 $self->revoke_all($u);
2969 if ($arg->{qcopy_username}) {
2970 $self->grant_like($arg->{qcopy_username}, $u);
2972 $self->grant($arg->{jrolenames}, $u);
2975 if ($arg->{jclient_groups}) {
2977 INSERT INTO bweb_client_group_acl (client_group_id, userid)
2978 SELECT client_group_id, userid
2979 FROM client_group, bweb_user
2980 WHERE client_group_name IN ($arg->{jclient_groups})
2985 $self->{dbh}->commit();
2987 $self->display_users();
2990 # TODO: we miss a matrix with all user/roles
2994 $self->can_do("r_user_mgnt");
2996 my $arg = $self->get_form(qw/db_usernames/) ;
2998 if ($self->{dbh}->errstr) {
2999 return $self->error("Can't use users with bweb, read INSTALL to enable them");
3002 $self->display({ ID => $cur_id++,
3004 "display_users.tpl");
3010 $self->can_do("r_user_mgnt");
3012 my $arg = $self->get_form('username');
3013 my $user = $self->dbh_quote($arg->{username});
3015 my $userp = $self->dbh_selectrow_hashref("
3016 SELECT username, passwd, comment, use_acl
3018 WHERE username = $user
3021 return $self->error("Can't find $user in catalog");
3023 my $filter = $self->get_client_group_filter($arg->{username});
3024 my $scg = $self->dbh_selectall_hashref("
3025 SELECT client_group_name AS name
3026 FROM client_group $filter
3030 #------------+--------
3035 my $role = $self->dbh_selectall_hashref("
3036 SELECT rolename, temp.userid
3038 LEFT JOIN (SELECT roleid, userid
3039 FROM bweb_user JOIN bweb_role_member USING (userid)
3040 WHERE username = $user) AS temp USING (roleid)
3044 $arg = $self->get_form(qw/db_usernames db_client_groups/);
3047 db_usernames => $arg->{db_usernames},
3048 username => $userp->{username},
3049 comment => $userp->{comment},
3050 passwd => $userp->{passwd},
3051 use_acl => $userp->{use_acl},
3052 db_client_groups => $arg->{db_client_groups},
3053 client_group => [ values %$scg ],
3054 db_roles => [ values %$role],
3055 }, "display_user.tpl");
3059 ###########################################################
3061 sub get_media_max_size
3063 my ($self, $type) = @_;
3065 "SELECT avg(VolBytes) AS size
3067 WHERE Media.VolStatus = 'Full'
3068 AND Media.MediaType = '$type'
3071 my $res = $self->selectrow_hashref($query);
3074 return $res->{size};
3084 my $media = $self->get_form('qmedia');
3086 unless ($media->{qmedia}) {
3087 return $self->error("Can't get media");
3091 SELECT Media.Slot AS slot,
3092 PoolMedia.Name AS poolname,
3093 Media.VolStatus AS volstatus,
3094 Media.InChanger AS inchanger,
3095 Location.Location AS location,
3096 Media.VolumeName AS volumename,
3097 Media.MaxVolBytes AS maxvolbytes,
3098 Media.MaxVolJobs AS maxvoljobs,
3099 Media.MaxVolFiles AS maxvolfiles,
3100 Media.VolUseDuration AS voluseduration,
3101 Media.VolRetention AS volretention,
3102 Media.Comment AS comment,
3103 PoolRecycle.Name AS poolrecycle,
3104 Media.Enabled AS enabled
3106 FROM Media INNER JOIN Pool AS PoolMedia ON (Media.PoolId = PoolMedia.PoolId)
3107 LEFT JOIN Pool AS PoolRecycle ON (Media.RecyclePoolId = PoolRecycle.PoolId)
3108 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
3110 WHERE Media.VolumeName = $media->{qmedia}
3113 my $row = $self->dbh_selectrow_hashref($query);
3114 $row->{volretention} = human_sec($row->{volretention});
3115 $row->{voluseduration} = human_sec($row->{voluseduration});
3116 $row->{enabled} = human_enabled($row->{enabled});
3118 my $elt = $self->get_form(qw/db_pools db_locations/);
3123 }, "update_media.tpl");
3129 $self->can_do('r_media_mgnt');
3131 my $arg = $self->get_form('jmedias', 'qnewlocation') ;
3133 unless ($arg->{jmedias}) {
3134 return $self->error("Can't get selected media");
3137 unless ($arg->{qnewlocation}) {
3138 return $self->error("Can't get new location");
3143 SET LocationId = (SELECT LocationId
3145 WHERE Location = $arg->{qnewlocation})
3146 WHERE Media.VolumeName IN ($arg->{jmedias})
3149 my $nb = $self->dbh_do($query);
3151 print "$nb media updated, you may have to update your autochanger.";
3153 $self->display_media();
3159 $self->can_do('r_media_mgnt');
3161 my $media = $self->get_selected_media_location();
3163 return $self->error("Can't get media selection");
3165 my $newloc = CGI::param('newlocation');
3167 my $user = CGI::param('user') || 'unknown';
3168 my $comm = CGI::param('comment') || '';
3169 $comm = $self->dbh_quote("$user: $comm");
3171 my $arg = $self->get_form('enabled');
3172 my $en = from_human_enabled($arg->{enabled});
3173 my $b = $self->get_bconsole();
3176 foreach my $vol (keys %$media) {
3178 INSERT INTO LocationLog (Date,Comment,MediaId,LocationId,NewEnabled,NewVolStatus)
3179 SELECT NOW(), $comm, Media.MediaId, Location.LocationId, $en, VolStatus
3180 FROM Media, Location
3181 WHERE Media.VolumeName = '$vol'
3182 AND Location.Location = '$media->{$vol}->{location}'
3184 $self->dbh_do($query);
3185 $self->debug($query);
3186 $b->send_cmd("update volume=\"$vol\" enabled=$en");
3191 $q->param('action', 'update_location');
3192 my $url = $q->url(-full => 1, -query=>1);
3194 $self->display({ email => $self->{info}->{email_media},
3196 newlocation => $newloc,
3197 # [ { volumename => 'vol1' }, { volumename => 'vol2'},..]
3198 media => [ values %$media ],
3200 "change_location.tpl");
3204 sub display_client_stats
3206 my ($self, %arg) = @_ ;
3207 $self->can_do('r_view_stat');
3209 my $client = $self->dbh_quote($arg{clientname});
3210 # get security filter
3211 my $filter = $self->get_client_filter();
3213 my ($limit, $label) = $self->get_limit(%arg);
3216 count(Job.JobId) AS nb_jobs,
3217 sum(Job.JobBytes) AS nb_bytes,
3218 sum(Job.JobErrors) AS nb_err,
3219 sum(Job.JobFiles) AS nb_files,
3220 Client.Name AS clientname
3221 FROM Job JOIN Client USING (ClientId) $filter
3223 Client.Name = $client
3225 GROUP BY Client.Name
3228 my $row = $self->dbh_selectrow_hashref($query);
3230 $row->{ID} = $cur_id++;
3231 $row->{label} = $label;
3232 $row->{grapharg} = "client";
3234 $self->display($row, "display_client_stats.tpl");
3238 sub display_group_stats
3240 my ($self, %arg) = @_ ;
3242 my $carg = $self->get_form(qw/qclient_group/);
3244 unless ($carg->{qclient_group}) {
3245 return $self->error("Can't get group");
3248 my ($limit, $label) = $self->get_limit(%arg);
3252 count(Job.JobId) AS nb_jobs,
3253 sum(Job.JobBytes) AS nb_bytes,
3254 sum(Job.JobErrors) AS nb_err,
3255 sum(Job.JobFiles) AS nb_files,
3256 client_group.client_group_name AS clientname
3257 FROM Job JOIN Client USING (ClientId)
3258 JOIN client_group_member ON (Client.ClientId = client_group_member.clientid)
3259 JOIN client_group USING (client_group_id)
3261 client_group.client_group_name = $carg->{qclient_group}
3263 GROUP BY client_group.client_group_name
3266 my $row = $self->dbh_selectrow_hashref($query);
3268 $row->{ID} = $cur_id++;
3269 $row->{label} = $label;
3270 $row->{grapharg} = "client_group";
3272 $self->display($row, "display_client_stats.tpl");
3275 # poolname can be undef
3278 my ($self, $poolname) = @_ ;
3279 $self->can_do('r_view_media');
3284 my $arg = $self->get_form('jmediatypes', 'qmediatypes');
3285 if ($arg->{jmediatypes}) {
3286 $whereW = "WHERE MediaType IN ($arg->{jmediatypes}) ";
3287 $whereA = "AND MediaType IN ($arg->{jmediatypes}) ";
3290 # TODO : afficher les tailles et les dates
3293 SELECT subq.volmax AS volmax,
3294 subq.volnum AS volnum,
3295 subq.voltotal AS voltotal,
3297 Pool.Recycle AS recycle,
3298 Pool.VolRetention AS volretention,
3299 Pool.VolUseDuration AS voluseduration,
3300 Pool.MaxVolJobs AS maxvoljobs,
3301 Pool.MaxVolFiles AS maxvolfiles,
3302 Pool.MaxVolBytes AS maxvolbytes,
3303 subq.PoolId AS PoolId,
3304 subq.MediaType AS mediatype,
3305 $self->{sql}->{CAT_POOL_TYPE} AS uniq
3308 SELECT COALESCE(media_avg_size.volavg,0) * count(Media.MediaId) AS volmax,
3309 count(Media.MediaId) AS volnum,
3310 sum(Media.VolBytes) AS voltotal,
3311 Media.PoolId AS PoolId,
3312 Media.MediaType AS MediaType
3314 LEFT JOIN (SELECT avg(Media.VolBytes) AS volavg,
3315 Media.MediaType AS MediaType
3317 WHERE Media.VolStatus = 'Full'
3318 GROUP BY Media.MediaType
3319 ) AS media_avg_size ON (Media.MediaType = media_avg_size.MediaType)
3320 GROUP BY Media.MediaType, Media.PoolId, media_avg_size.volavg
3322 LEFT JOIN Pool ON (Pool.PoolId = subq.PoolId)
3326 my $all = $self->dbh_selectall_hashref($query, 'uniq') ;
3329 SELECT Pool.Name AS name,
3330 sum(VolBytes) AS size
3331 FROM Media JOIN Pool ON (Media.PoolId = Pool.PoolId)
3332 WHERE Media.VolStatus IN ('Recycled', 'Purged')
3336 my $empty = $self->dbh_selectall_hashref($query, 'name');
3338 foreach my $p (values %$all) {
3339 if ($p->{volmax} > 0) { # mysql returns 0.0000
3340 # we remove Recycled/Purged media from pool usage
3341 if (defined $empty->{$p->{name}}) {
3342 $p->{voltotal} -= $empty->{$p->{name}}->{size};
3344 $p->{poolusage} = sprintf('%.2f', $p->{voltotal} * 100/ $p->{volmax}) ;
3346 $p->{poolusage} = 0;
3350 SELECT VolStatus AS volstatus, count(MediaId) AS nb
3352 WHERE PoolId=$p->{poolid}
3353 AND Media.MediaType = '$p->{mediatype}'
3357 my $content = $self->dbh_selectall_hashref($query, 'volstatus');
3358 foreach my $t (values %$content) {
3359 $p->{"nb_" . $t->{volstatus}} = $t->{nb} ;
3364 $self->display({ ID => $cur_id++,
3365 MediaType => $arg->{qmediatypes}, # [ { name => type1 } , { name => type2 } ]
3366 Pools => [ values %$all ]},
3367 "display_pool.tpl");
3370 sub display_running_job
3373 return if $self->cant_do('r_view_running_job');
3375 my $arg = $self->get_form('client', 'jobid');
3377 if (!$arg->{client} and $arg->{jobid}) {
3378 # get security filter
3379 my $filter = $self->get_client_filter();
3382 SELECT Client.Name AS name
3383 FROM Job INNER JOIN Client USING (ClientId) $filter
3384 WHERE Job.JobId = $arg->{jobid}
3387 my $row = $self->dbh_selectrow_hashref($query);
3390 $arg->{client} = $row->{name};
3391 CGI::param('client', $arg->{client});
3395 if ($arg->{client}) {
3396 my $cli = new Bweb::Client(name => $arg->{client});
3397 $cli->display_running_job($self->{info}, $arg->{jobid});
3398 if ($arg->{jobid}) {
3399 $self->get_job_log();
3402 $self->error("Can't get client or jobid");
3406 sub display_running_jobs
3408 my ($self, $display_action) = @_;
3409 return if $self->cant_do('r_view_running_job');
3411 # get security filter
3412 my $filter = $self->get_client_filter();
3415 SELECT Job.JobId AS jobid,
3416 Job.Name AS jobname,
3418 Job.StartTime AS starttime,
3419 Job.JobFiles AS jobfiles,
3420 Job.JobBytes AS jobbytes,
3421 Job.JobStatus AS jobstatus,
3422 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(NOW())
3423 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime))
3425 Client.Name AS clientname
3426 FROM Job INNER JOIN Client USING (ClientId) $filter
3428 JobStatus IN ('C','R','B','e','D','F','S','m','M','s','j','c','d','t','p')
3430 my $all = $self->dbh_selectall_hashref($query, 'jobid') ;
3432 $self->display({ ID => $cur_id++,
3433 display_action => $display_action,
3434 Jobs => [ values %$all ]},
3435 "running_job.tpl") ;
3438 # return the autochanger list to update
3442 $self->can_do('r_media_mgnt');
3445 my $arg = $self->get_form('jmedias');
3447 unless ($arg->{jmedias}) {
3448 return $self->error("Can't get media selection");
3452 SELECT Media.VolumeName AS volumename,
3453 Storage.Name AS storage,
3454 Location.Location AS location,
3456 FROM Media INNER JOIN Storage ON (Media.StorageId = Storage.StorageId)
3457 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
3458 WHERE Media.VolumeName IN ($arg->{jmedias})
3459 AND Media.InChanger = 1
3462 my $all = $self->dbh_selectall_hashref($query, 'volumename');
3464 foreach my $vol (values %$all) {
3465 my $a = $self->ach_get($vol->{location});
3467 $ret{$vol->{location}} = 1;
3469 unless ($a->{have_status}) {
3471 $a->{have_status} = 1;
3474 print "eject $vol->{volumename} from $vol->{storage} : ";
3475 if ($a->send_to_io($vol->{slot})) {
3476 print "<img src='/bweb/T.png' alt='ok'><br/>";
3478 print "<img src='/bweb/E.png' alt='err'><br/>";
3488 my ($to, $subject, $content) = (CGI::param('email'),
3489 CGI::param('subject'),
3490 CGI::param('content'));
3491 $to =~ s/[^\w\d\.\@<>,]//;
3492 $subject =~ s/[^\w\d\.\[\]]/ /;
3494 open(MAIL, "|mail -s '$subject' '$to'") ;
3495 print MAIL $content;
3505 my $arg = $self->get_form('jobid', 'client');
3507 print CGI::header('text/brestore');
3508 print "jobid=$arg->{jobid}\n" if ($arg->{jobid});
3509 print "client=$arg->{client}\n" if ($arg->{client});
3510 print "\n\nYou have to assign this mime type with /usr/bin/brestore.pl\n";
3514 # TODO : move this to Bweb::Autochanger ?
3515 # TODO : make this internal to not eject tape ?
3521 my ($self, $name) = @_;
3524 return $self->error("Can't get your autochanger name ach");
3527 unless ($self->{info}->{ach_list}) {
3528 return $self->error("Could not find any autochanger");
3531 my $a = $self->{info}->{ach_list}->{$name};
3534 $self->error("Can't get your autochanger $name from your ach_list");
3539 $a->{debug} = $self->{debug};
3546 my ($self, $ach) = @_;
3547 $self->can_do('r_configure');
3549 $self->{info}->{ach_list}->{$ach->{name}} = $ach;
3551 $self->{info}->save();
3559 $self->can_do('r_configure');
3561 my $arg = $self->get_form('ach');
3563 or !$self->{info}->{ach_list}
3564 or !$self->{info}->{ach_list}->{$arg->{ach}})
3566 return $self->error("Can't get autochanger name");
3569 my $ach = $self->{info}->{ach_list}->{$arg->{ach}};
3573 [ map { { name => $_, index => $i++ } } @{$ach->{drive_name}} ] ;
3575 my $b = $self->get_bconsole();
3577 my @storages = $b->list_storage() ;
3579 $ach->{devices} = [ map { { name => $_ } } @storages ];
3581 $self->display($ach, "ach_add.tpl");
3582 delete $ach->{drives};
3583 delete $ach->{devices};
3590 $self->can_do('r_configure');
3592 my $arg = $self->get_form('ach');
3595 or !$self->{info}->{ach_list}
3596 or !$self->{info}->{ach_list}->{$arg->{ach}})
3598 return $self->error("Can't get autochanger name");
3601 delete $self->{info}->{ach_list}->{$arg->{ach}} ;
3603 $self->{info}->save();
3604 $self->{info}->view();
3610 $self->can_do('r_configure');
3612 my $arg = $self->get_form('ach', 'mtxcmd', 'device', 'precmd');
3614 my $b = $self->get_bconsole();
3615 my @storages = $b->list_storage() ;
3617 unless ($arg->{ach}) {
3618 $arg->{devices} = [ map { { name => $_ } } @storages ];
3619 return $self->display($arg, "ach_add.tpl");
3623 foreach my $drive (CGI::param('drives'))
3625 unless (grep(/^$drive$/,@storages)) {
3626 return $self->error("Can't find $drive in storage list");
3629 my $index = CGI::param("index_$drive");
3630 unless (defined $index and $index =~ /^(\d+)$/) {
3631 return $self->error("Can't get $drive index");
3634 $drives[$index] = $drive;
3638 return $self->error("Can't get drives from Autochanger");
3641 my $a = new Bweb::Autochanger(name => $arg->{ach},
3642 precmd => $arg->{precmd},
3643 drive_name => \@drives,
3644 device => $arg->{device},
3645 mtxcmd => $arg->{mtxcmd});
3647 $self->ach_register($a) ;
3649 $self->{info}->view();
3655 $self->can_do('r_delete_job');
3657 my $arg = $self->get_form('jobid');
3659 if ($arg->{jobid}) {
3660 my $b = $self->get_bconsole();
3661 my $ret = $b->send_cmd("delete jobid=\"$arg->{jobid}\"");
3665 title => "Delete a job ",
3666 name => "delete jobid=$arg->{jobid}",
3674 $self->can_do('r_media_mgnt');
3676 my $arg = $self->get_form(qw/media volstatus inchanger pool
3677 slot volretention voluseduration
3678 maxvoljobs maxvolfiles maxvolbytes
3679 qcomment poolrecycle enabled
3682 unless ($arg->{media}) {
3683 return $self->error("Can't find media selection");
3686 my $update = "update volume=$arg->{media} ";
3688 if ($arg->{volstatus}) {
3689 $update .= " volstatus=$arg->{volstatus} ";
3692 if ($arg->{inchanger}) {
3693 $update .= " inchanger=yes " ;
3695 $update .= " slot=$arg->{slot} ";
3698 $update .= " slot=0 inchanger=no ";
3701 if ($arg->{enabled}) {
3702 $update .= " enabled=$arg->{enabled} ";
3706 $update .= " pool=$arg->{pool} " ;
3709 if (defined $arg->{volretention}) {
3710 $update .= " volretention=\"$arg->{volretention}\" " ;
3713 if (defined $arg->{voluseduration}) {
3714 $update .= " voluse=\"$arg->{voluseduration}\" " ;
3717 if (defined $arg->{maxvoljobs}) {
3718 $update .= " maxvoljobs=$arg->{maxvoljobs} " ;
3721 if (defined $arg->{maxvolfiles}) {
3722 $update .= " maxvolfiles=$arg->{maxvolfiles} " ;
3725 if (defined $arg->{maxvolbytes}) {
3726 $update .= " maxvolbytes=$arg->{maxvolbytes} " ;
3729 if (defined $arg->{poolrecycle}) {
3730 $update .= " recyclepool=\"$arg->{poolrecycle}\" " ;
3733 my $b = $self->get_bconsole();
3736 content => $b->send_cmd($update),
3737 title => "Update a volume ",
3743 my $media = $self->dbh_quote($arg->{media});
3745 my $loc = CGI::param('location') || '';
3747 $loc = $self->dbh_quote($loc); # is checked by db
3748 push @q, "LocationId=(SELECT LocationId FROM Location WHERE Location=$loc)";
3750 if (!$arg->{qcomment}) {
3751 $arg->{qcomment} = "''";
3753 push @q, "Comment=$arg->{qcomment}";
3758 SET " . join (',', @q) . "
3759 WHERE Media.VolumeName = $media
3761 $self->dbh_do($query);
3763 $self->update_media();
3769 $self->can_do('r_autochanger_mgnt');
3771 my $ach = CGI::param('ach') ;
3772 $ach = $self->ach_get($ach);
3774 return $self->error("Bad autochanger name");
3778 my $b = new Bconsole(pref => $self->{info},timeout => 60,log_stdout => 1);
3779 $b->update_slots($ach->{name});
3786 $self->can_do('r_view_log');
3788 my $arg = $self->get_form('jobid', 'limit', 'offset');
3789 unless ($arg->{jobid}) {
3790 return $self->error("Can't get jobid");
3793 if ($arg->{limit} == 100) {
3794 $arg->{limit} = 1000;
3796 # get security filter
3797 my $filter = $self->get_client_filter();
3800 SELECT Job.Name as name, Client.Name as clientname
3801 FROM Job INNER JOIN Client USING (ClientId) $filter
3802 WHERE JobId = $arg->{jobid}
3805 my $row = $self->dbh_selectrow_hashref($query);
3808 return $self->error("Can't find $arg->{jobid} in catalog");
3811 # display only Error and Warning messages
3813 if (CGI::param('error')) {
3814 $filter = " AND LogText $self->{sql}->{MATCH} 'Error|Warning|ERR=' ";
3818 if (CGI::param('time') || $self->{info}->{display_log_time}) {
3819 $logtext = $self->dbh_strcat('Time', " ' ' ", 'LogText');
3821 $logtext = 'LogText';
3825 SELECT count(1) AS nbline, JobId AS jobid,
3826 GROUP_CONCAT($logtext $self->{sql}->{CONCAT_SEP}) AS logtxt
3828 SELECT JobId, Time, LogText
3830 WHERE ( Log.JobId = $arg->{jobid}
3832 AND Time >= (SELECT StartTime FROM Job WHERE JobId=$arg->{jobid})
3833 AND Time <= (SELECT COALESCE(EndTime,NOW()) FROM Job WHERE JobId=$arg->{jobid})
3837 OFFSET $arg->{offset}
3843 my $log = $self->dbh_selectrow_hashref($query);
3845 return $self->error("Can't get log for jobid $arg->{jobid}");
3848 $self->display({ lines=> $log->{logtxt},
3849 nbline => $log->{nbline},
3850 jobid => $arg->{jobid},
3851 name => $row->{name},
3852 client => $row->{clientname},
3853 offset => $arg->{offset},
3854 limit => $arg->{limit},
3855 }, 'display_log.tpl');
3861 $self->can_do('r_media_mgnt');
3862 my $arg = $self->get_form('storage', 'pool', 'nb', 'media', 'offset');
3863 my $b = $self->get_bconsole();
3865 if (!$arg->{storage} || !$arg->{pool} || not defined $arg->{nb} || !$arg->{media} || !$arg->{offset}) {
3866 CGI::param(offset => 0);
3867 $arg = $self->get_form('db_pools');
3868 $arg->{storage} = [ map { { name => $_ } }$b->list_storage()];
3869 $self->display($arg, 'add_media.tpl');
3874 if ($arg->{nb} > 0) {
3875 $arg->{offset} = $arg->{offset}?$arg->{offset}:1;
3876 $cmd = "add pool=\"$arg->{pool}\" storage=\"$arg->{storage}\"\n$arg->{nb}\n$arg->{media}\n$arg->{offset}\n";
3878 $cmd = "add pool=\"$arg->{pool}\" storage=\"$arg->{storage}\"\n0\n$arg->{media}\n";
3884 CGI::param('media', '');
3885 CGI::param('re_media', $arg->{media});
3886 $self->display_media();
3892 $self->can_do('r_autochanger_mgnt');
3894 my $arg = $self->get_form('ach', 'slots', 'drive');
3896 unless ($arg->{ach}) {
3897 return $self->error("Can't find autochanger name");
3900 my $a = $self->ach_get($arg->{ach});
3902 return $self->error("Can't find autochanger name in configuration");
3905 my $storage = $a->get_drive_name($arg->{drive});
3907 return $self->error("Can't get your drive name");
3913 if ($arg->{slots}) {
3914 $slots = join(",", @{ $arg->{slots} });
3915 $slots_sql = " AND Slot IN ($slots) ";
3916 $t += 60*scalar( @{ $arg->{slots} }) ;
3919 my $b = new Bconsole(pref => $self->{info}, timeout => $t,log_stdout => 1);
3920 print "<h1>This command can take long time, be patient...</h1>";
3922 $b->label_barcodes(storage => $storage,
3923 drive => $arg->{drive},
3931 SET LocationId = (SELECT LocationId
3933 WHERE Location = '$arg->{ach}')
3935 WHERE (LocationId = 0 OR LocationId IS NULL)
3944 $self->can_do('r_purge');
3946 my @volume = CGI::param('media');
3949 return $self->error("Can't get media selection");
3952 my $b = new Bconsole(pref => $self->{info}, timeout => 60);
3954 foreach my $v (@volume) {
3956 content => $b->purge_volume($v),
3957 title => "Purge media",
3958 name => "purge volume=$v",
3967 $self->can_do('r_prune');
3969 my @volume = CGI::param('media');
3971 return $self->error("Can't get media selection");
3974 my $b = new Bconsole(pref => $self->{info}, timeout => 60);
3976 foreach my $v (@volume) {
3978 content => $b->prune_volume($v),
3979 title => "Prune volume",
3980 name => "prune volume=$v",
3989 $self->can_do('r_cancel_job');
3991 my $arg = $self->get_form('jobid');
3992 unless ($arg->{jobid}) {
3993 return $self->error("Can't get jobid");
3996 my $b = $self->get_bconsole();
3998 content => $b->cancel($arg->{jobid}),
3999 title => "Cancel job",
4000 name => "cancel jobid=$arg->{jobid}",
4006 # Warning, we display current fileset
4009 my $arg = $self->get_form('fileset');
4011 if ($arg->{fileset}) {
4012 my $b = $self->get_bconsole();
4013 my $ret = $b->get_fileset($arg->{fileset});
4014 $self->display({ fileset => $arg->{fileset},
4016 }, "fileset_view.tpl");
4018 $self->error("Can't get fileset name");
4022 sub director_show_sched
4025 $self->can_do('r_view_job');
4026 my $arg = $self->get_form('days');
4028 my $b = $self->get_bconsole();
4029 my $ret = $b->director_get_sched( $arg->{days} );
4034 }, "scheduled_job.tpl");
4037 sub enable_disable_job
4039 my ($self, $what) = @_ ;
4040 $self->can_do('r_run_job');
4042 my $name = CGI::param('job') || '';
4043 unless ($name =~ /^[\w\d\.\-\s]+$/) {
4044 return $self->error("Can't find job name");
4047 my $b = $self->get_bconsole();
4057 content => $b->send_cmd("$cmd job=\"$name\""),
4058 title => "$cmd $name",
4059 name => "$cmd job=\"$name\"",
4066 return new Bconsole(pref => $self->{info});
4072 $self->can_do('r_run_job');
4074 my $b = $self->get_bconsole();
4076 my $joblist = [ map { { name => $_ } } $b->list_job() ];
4078 $self->display({ Jobs => $joblist }, "run_job.tpl");
4083 my ($self, $ouput) = @_;
4086 foreach my $l (split(/\r\n/, $ouput)) {
4087 if ($l =~ /(\w+): name=([\w\d\.\s-]+?)(\s+\w+=.+)?$/) {
4093 if (my @l = $l =~ /(\w+)=([\w\d*]+)/g) {
4099 foreach my $k (keys %arg) {
4100 $lowcase{lc($k)} = $arg{$k} ;
4109 $self->can_do('r_run_job');
4111 my $b = $self->get_bconsole();
4113 my $job = CGI::param('job') || '';
4115 # we take informations from director, and we overwrite with user wish
4116 my $info = $b->send_cmd("show job=\"$job\"");
4117 my $attr = $self->run_parse_job($info);
4119 my $arg = $self->get_form(qw/pool level client fileset storage media/);
4121 if (!$arg->{pool} and $arg->{media}) {
4122 my $r = $self->dbh_selectrow_hashref("
4123 SELECT Pool.Name AS name
4124 FROM Media JOIN Pool USING (PoolId)
4125 WHERE Media.VolumeName = '$arg->{media}'
4126 AND Pool.Name != 'Scratch'
4129 $arg->{pool} = $r->{name};
4133 my %job_opt = (%$attr, %$arg);
4135 my $jobs = [ map {{ name => $_ }} $b->list_job() ];
4137 my $pools = [ map { { name => $_ } } $b->list_pool() ];
4138 my $clients = [ map { { name => $_ } }$b->list_client()];
4139 my $filesets= [ map { { name => $_ } }$b->list_fileset() ];
4140 my $storages= [ map { { name => $_ } }$b->list_storage()];
4145 clients => $clients,
4146 filesets => $filesets,
4147 storages => $storages,
4149 }, "run_job_mod.tpl");
4155 $self->can_do('r_run_job');
4157 my $b = $self->get_bconsole();
4159 my $jobs = [ map {{ name => $_ }} $b->list_job() ];
4169 $self->can_do('r_run_job');
4171 my $b = $self->get_bconsole();
4173 # TODO: check input (don't use pool, level)
4175 my $arg = $self->get_form('pool', 'level', 'client', 'priority', 'when', 'fileset');
4176 my $job = CGI::param('job') || '';
4177 my $storage = CGI::param('storage') || '';
4179 my $jobid = $b->run(job => $job,
4180 client => $arg->{client},
4181 priority => $arg->{priority},
4182 level => $arg->{level},
4183 storage => $storage,
4184 pool => $arg->{pool},
4185 fileset => $arg->{fileset},
4186 when => $arg->{when},
4191 print "<br>You can follow job (jobid=$jobid) execution <a href='?action=dsp_cur_job;client=$arg->{client};jobid=$jobid'> here </a><script type='text/javascript' language='JavaScript'>setTimeout(function() { window.location='?action=dsp_cur_job;client=$arg->{client};jobid=$jobid'},2000);</script>";