lib/BackupPC/SearchLib.pm

   1 #!/usr/bin/perl
   2 package BackupPC::SearchLib;
   3
   4 use strict;
   5 use BackupPC::CGI::Lib qw(:all);
   6 use BackupPC::Attrib qw(:all);
   7 use Data::Dumper;
   8 use DBI;
   9
  10 sub getUnits() {
  11     my @ret = ();
  12     my $tmp;
  13     my $dbh = DBI->connect( "dbi:SQLite:dbname=${TopDir}/$Conf{SearchDB}",
  14         "", "", { RaiseError => 1, AutoCommit => 1 } );
  15     my $st =
  16       $dbh->prepare(
  17         " SELECT shares.ID AS ID, shares.share AS name FROM shares;");
  18     $st->execute();
  19     push (@ret, { 'ID' => '', 'name' => '-'});
  20     while ( $tmp = $st->fetchrow_hashref() ) {
  21         push( @ret, { 'ID' => $tmp->{'ID'}, 'name' => $tmp->{'name'} } );
  22     }
  23     $dbh->disconnect();
  24     return @ret;
  25 }
  26
  27 sub getWhere($) {
  28         my ($param)    = @_;
  29         my @conditions;
  30
  31         sub mk_iso_date($$) {
  32                 my ($name,$suffix) = @_;
  33
  34                 my $yyyy = $param->{ $name . '_year_' . $suffix} || return;
  35                 my $mm .= $param->{ $name . '_month_' . $suffix} ||
  36                         ( $suffix eq 'from' ? 1 : 12);
  37                 my $dd .= $param->{ $name . '_day_' . $suffix} ||
  38                         ( $suffix eq 'from' ? 1 : 31);
  39                 return sprintf("%04d-%02d-%02d", $yyyy, $mm, $dd);
  40         }
  41
  42         my $backup_from = mk_iso_date('search_backup', 'from');
  43         push @conditions, qq{ date(backups.date, 'unixepoch','localtime') >= '$backup_from' } if ($backup_from);
  44         my $backup_to = mk_iso_date('search_backup', 'to');
  45         push @conditions, qq{ date(backups.date, 'unixepoch','localtime') <= '$backup_to' } if ($backup_to);
  46
  47         my $files_from = mk_iso_date('search', 'from');
  48         push @conditions, qq{ date(files.date, 'unixepoch','localtime') >= '$files_from' } if ($files_from);
  49         my $files_to = mk_iso_date('search', 'to');
  50         push @conditions, qq{ date(files.date, 'unixepoch','localtime') <= '$files_to' } if ($files_to);
  51
  52         print STDERR "backup: $backup_from - $backup_to files: $files_from - $files_to cond:",join(" | ",@conditions);
  53
  54         push( @conditions, ' backups.hostID = ' . $param->{'search_host'} ) if ($param->{'search_host'});
  55
  56         push (@conditions, " files.name LIKE '".$param->{'search_filename'}."%'") if ($param->{'search_filename'});
  57
  58         return (
  59                 join(" and ", @conditions),
  60                 $files_from, $files_to,
  61                 $backup_from, $backup_to
  62         );
  63 }
  64
  65
  66 sub getFiles($$)
  67   {
  68       my ($where, $offset) = @_;
  69
  70
  71       my $dbh = DBI->connect( "dbi:SQLite:dbname=${TopDir}/$Conf{SearchDB}",
  72         "", "", { RaiseError => 1, AutoCommit => 1 } );
  73       my $sql =
  74         q{
  75                 SELECT  files.id                        AS fid,
  76                         hosts.name                      AS hname,
  77                         shares.name                     AS sname,
  78                         shares.share                    AS sharename,
  79                         files.backupNum                 AS backupNum,
  80                         files.name                      AS filename,
  81                         files.path                      AS filepath,
  82                         shares.share||files.fullpath    AS networkPath,
  83                         date(files.date, 'unixepoch', 'localtime') AS date,
  84                         files.type                      AS filetype,
  85                         files.size                      AS size,
  86                         dvds.name                       AS dvd
  87                 FROM files
  88                         INNER JOIN shares       ON files.shareID=shares.ID
  89                         INNER JOIN hosts        ON hosts.ID = shares.hostID
  90                         INNER JOIN backups      ON backups.num = files.backupNum
  91                         LEFT  JOIN dvds         ON dvds.ID = files.dvdid
  92           };
  93
  94       if (defined($where) && $where ne "")
  95         {
  96             $sql .= " WHERE ". $where;
  97         }
  98
  99       $sql .=
 100         q{
 101             ORDER BY files.id
 102               LIMIT 100
 103               OFFSET ? * 100 + 1
 104         };
 105
 106
 107
 108       my $st = $dbh->prepare(
 109           $sql
 110           );
 111       if (!defined($offset) && $offset ne "")
 112       {
 113         $st->bind_param(1, $offset);
 114       }
 115       else
 116       {
 117         $st->bind_param(1,0);
 118       }
 119       $st->execute;
 120
 121       my @ret = ();
 122       my $tmp;
 123
 124       while ($tmp = $st->fetchrow_hashref())
 125         {
 126             push(@ret, {
 127                            'hname'       => $tmp->{'hname'},
 128                            'sname'       => $tmp->{'sname'},
 129                            'sharename'   => $tmp->{'sharename'},
 130                            'backupno'    => $tmp->{'backupNum'},
 131                            'fname'       => $tmp->{'filename'},
 132                            'fpath'       => $tmp->{'filepath'},
 133                            'networkpath' => $tmp->{'networkPath'},
 134                            'date'        => $tmp->{'date'},
 135                            'type'        => $tmp->{'filetype'},
 136                            'size'        => $tmp->{'size'},
 137                            'id'          => $tmp->{'fid'},
 138                            'dvd'         => $tmp->{'dvd'}
 139                        }
 140             );
 141
 142         }
 143
 144       $st->finish();
 145       $dbh->disconnect();
 146       return @ret;
 147   }
 148
 149 sub getBackupsNotBurned()
 150   {
 151       my $dbh = DBI->connect( "dbi:SQLite:dbname=${TopDir}/$Conf{SearchDB}",
 152         "", "", { RaiseError => 1, AutoCommit => 1 } );
 153       my $sql = q{
 154           SELECT
 155             hosts.ID         AS hostID,
 156             hosts.name       AS host,
 157             backups.num      AS backupno,
 158             backups.type     AS type,
 159             backups.date     AS date
 160           FROM backups, shares, files, hosts
 161           WHERE
 162             backups.num    = files.backupNum  AND
 163             shares.ID      = files.shareID    AND
 164             backups.hostID = shares.hostID    AND
 165             hosts.ID       = backups.hostID   AND
 166             files.dvdid    IS NULL
 167           GROUP BY
 168             backups.hostID, backups.num
 169       };
 170       my $st = $dbh -> prepare( $sql );
 171       my @ret = ();
 172       $st -> execute();
 173
 174       while ( my $tmp = $st -> fetchrow_hashref() )
 175         {
 176             push(@ret, {
 177                          'host'     => $tmp->{'host'},
 178                          'hostid'   => $tmp->{'hostID'},
 179                          'backupno' => $tmp->{'backupno'},
 180                          'type'     => $tmp->{'type'},
 181                          'date'     => $tmp->{'date'}
 182                        }
 183             );
 184         }
 185
 186       return @ret;
 187   }
 188
 189 sub displayBackupsGrid()
 190   {
 191       my $retHTML = "";
 192       my $addForm = 1;
 193
 194       if ($addForm)
 195         {
 196
 197             $retHTML .= <<EOF3;
 198 <script language="javascript" type="text/javascript">
 199 <!--
 200
 201     function checkAll(location)
 202     {
 203       for (var i=0;i<document.forma.elements.length;i++)
 204       {
 205         var e = document.forma.elements[i];
 206         if ((e.checked || !e.checked) && e.name != \'all\') {
 207             if (eval("document.forma."+location+".checked")) {
 208                 e.checked = true;
 209             } else {
 210                 e.checked = false;
 211             }
 212         }
 213       }
 214     }
 215 //-->
 216 </script>
 217 EOF3
 218               $retHTML .= q{<form name="forma" method="POST" action="}."$MyURL"."?action=burn\"";
 219               $retHTML.= q{<input type="hidden" value="burn" name="action">};
 220               $retHTML .= q{<input type="hidden" value="results" name="search_results">};
 221         }
 222       $retHTML .= "<table style=\"fview\">";
 223       $retHTML .= "<tr> ";
 224       if ($addForm)
 225         {
 226             $retHTML .= "<td class=\"tableheader\"><input type=\"checkbox\" name=\"allFiles\" onClick=\"checkAll('allFiles');\"></td>";
 227         }
 228       $retHTML .=  "<td class=\"tableheader\">Host</td> <td class=\"tableheader\">Backup no</td> <td class=\"tableheader\">Type</td> <td class=\"tableheader\">date</td></tr>";
 229       my @backups = getBackupsNotBurned();
 230       my $backup;
 231
 232       if ($addForm)
 233         {
 234             $retHTML .= "<tr>";
 235             $retHTML .= "<td colspan=7 style=\"tableheader\">";
 236             $retHTML .= "<input type=\"submit\" value=\"Burn selected backups on medium\" name=\"submitBurner\">";
 237             $retHTML .= "</td>";
 238             $retHTML .= "</tr>";
 239
 240         }
 241       foreach $backup(@backups)
 242         {
 243             my $ftype = "";
 244
 245             $retHTML .= "<tr>";
 246             if ($addForm)
 247               {
 248                   $retHTML .= "<td class=\"fview\"> <input type=\"checkbox\" name=\"fcb"
 249                     .$backup->{'hostid'}."_".$backup->{'backupno'}
 250                   ."\" value=\"".$backup->{'hostid'}."_".$backup->{'backupno'}."\"> </td>";
 251               }
 252
 253             $retHTML .= "<td class=\"fviewborder\">" . $backup->{'host'} . "</td>";
 254             $retHTML .= "<td class=\"fviewborder\">" . $backup->{'backupno'} . "</td>";
 255             $retHTML .= "<td class=\"fviewborder\">" . $backup->{'type'} . "</td>";
 256             $retHTML .= "<td class=\"fviewborder\">" . $backup->{'date'} . "<td>";
 257             $retHTML .= "</tr>";
 258         }
 259       $retHTML .= "</table>";
 260       if ($addForm)
 261        {
 262            $retHTML .= "</form>";
 263        }
 264
 265       return $retHTML;
 266
 267
 268   }
 269
 270 sub displayGrid($$$$) {
 271         my ($where, $addForm, $offset, $hilite) = @_;
 272         my $retHTML = "";
 273
 274         if ($addForm) {
 275                 $retHTML .= qq{<form name="forma" method="POST" action="}.$MyURL.qq{?action=search">};
 276                 $retHTML.= qq{<input type="hidden" value="search" name="action">};
 277                 $retHTML .= qq{<input type="hidden" value="results" name="search_results">};
 278         }
 279         $retHTML .= qq{
 280         <table style="fview" width="100%">
 281                 <tr>
 282                 <td class="tableheader">Host</td>
 283                 <td class="tableheader">Type</td>
 284                 <td class="tableheader">Name</td>
 285                 <td class="tableheader">backup no.</td>
 286                 <td class="tableheader">size</td>
 287                 <td class="tableheader">date</td>
 288                 <td class="tableheader">Media</td>
 289                 </tr>
 290         };
 291         my @files = getFiles($where, $offset);
 292         my $file;
 293
 294         sub hilite_html($$) {
 295                 my ($html, $search) = @_;
 296                 $html =~ s#($search)#<b>$1</b>#gis;
 297                 return $html;
 298         }
 299
 300         foreach $file (@files) {
 301                 my $ftype = "file";
 302                 $ftype = "dir" if ($file->{'type'} == BPC_FTYPE_DIR);
 303
 304                 $retHTML .= "<tr>";
 305
 306                 foreach my $v ((
 307                         $file->{'hname'},
 308                         $ftype,
 309                         hilite_html( $file->{'fpath'}, $hilite ),
 310                         $file->{'backupno'},
 311                         $file->{'size'},
 312                         $file->{'date'},
 313                         $file->{'dvd'}
 314                 )) {
 315                         $retHTML .= qq{<td class="fviewborder">$v</td>};
 316                 }
 317
 318                 $retHTML .= "</tr>";
 319         }
 320         $retHTML .= "</table>";
 321
 322         # skip pager
 323         return $retHTML;
 324
 325         $retHTML .= "<INPUT TYPE=\"hidden\" VALUE=\"\" NAME=\"offset\">";
 326         for (my $ii = 1; $ii <= $#files; $ii++) {
 327                 $retHTML .= "<a href = \"#\" onclick=\"document.forma.offset.value=$ii;document.forma.submit();\">$ii</a>";
 328                 if ($ii < $#files) {
 329                         $retHTML .= " | ";
 330                 }
 331         }
 332
 333         $retHTML .= "</form>" if ($addForm);
 334
 335         return $retHTML;
 336 }
 337
 338 1;