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 $retSQL     = "";
  30     my @conditions = ();
  31     my $cond;
  32
  33
  34
  35
  36     if ( defined( $param->{'search_backup_day_from'} ) && $param->{'search_backup_day_from'} ne "") {
  37         push( @conditions,
  38             ' strftime("%d", datetime(backups.date, "unixepoch","localtime")) >= "'
  39               . $param->{'search_backup_day_from'} ."\"");
  40     }
  41     if ( defined( $param->{'search_backup_day_to'} ) && $param->{'search_backup_day_to'} ne "") {
  42         push( @conditions,
  43             ' strftime("%d", datetime(backups.date, "unixepoch","localtime")) <= "'
  44               . $param->{'search_backup_day_from'}  ."\"");
  45     }
  46     if ( defined( $param->{'search_backup_month_from'} ) && $param->{'search_backup_month_from'} ne "") {
  47         push( @conditions,
  48             ' strftime("%m", datetime(backups.date, "unixepoch","localtime")) >= "'
  49               . $param->{'search_backup_month_from'}  ."\"");
  50     }
  51     if ( defined( $param->{'search_backup_month_to'} ) && $param->{'search_backup_month_to'} ne "") {
  52         push( @conditions,
  53             ' strftime("%m", datetime(backups.date, "unixepoch","localtime")) <= "'
  54               . $param->{'search_backup_month_to'}  ."\"");
  55     }
  56     if ( defined( $param->{'search_backup_year_from'} ) && $param->{'search_backup_year_from'} ne "") {
  57         push( @conditions,
  58             ' strftime("%Y", datetime(backups.date, "unixepoch","localtime")) >= "'
  59               . $param->{'search_backup_year_from'}  ."\"");
  60     }
  61     if ( defined( $param->{'search_backup_year_to'} ) && $param->{'search_backup_year_to'} ne "") {
  62         push( @conditions,
  63             ' strftime("%Y", datetime(backups.date, "unixepoch","localtime")) <= "'
  64               . $param->{'search_backup_year_to'}  ."\"");
  65     }
  66
  67     if ( defined( $param->{'search_day_from'} )   && $param->{'search_day_from'} ne "" ) {
  68         push( @conditions,
  69             ' strftime("%d", datetime(files.date, "unixepoch","localtime")) >= "'
  70               . $param->{'search_day_from'}  ."\"");
  71     }
  72     if ( defined( $param->{'search_month_from'} ) && $param->{'search_month_from'} ne "") {
  73         push( @conditions,
  74             ' strftime("%m", datetime(files.date, "unixepoch","localtime")) >= "'
  75               . $param->{'search_month_from'}  ."\"");
  76     }
  77     if ( defined( $param->{'search_year_from'} ) && $param->{'search_year_from'} ne "") {
  78         push( @conditions,
  79             ' strftime("%Y", datetime(files.date, "unixepoch","localtime")) >= "'
  80               . $param->{'search_year_from'}  ."\"");
  81     }
  82     if ( defined( $param->{'search_day_to'} )   && $param->{'search_day_to'} ne "" ) {
  83         push( @conditions,
  84             ' strftime("%d", datetime(files.date, "unixepoch","localtime")) <= "'
  85               . $param->{'search_day_to'}  ."\"");
  86     }
  87     if ( defined( $param->{'search_month_to'} ) && $param->{'search_month_to'} ne "" ) {
  88         push( @conditions,
  89             ' strftime("%m", datetime(files.date, "unixepoch","localtime")) <= "'
  90               . $param->{'search_month_to'} ."\"" );
  91     }
  92     if ( defined( $param->{'search_year_to'} )&& $param->{'search_year_to'} ne "" )  {
  93         push( @conditions,
  94             ' strftime("%Y", datetime(files.date, "unixepoch","localtime")) <= "'
  95               . $param->{'search_year_to'} ."\"");
  96     }
  97
  98     if ( defined( $param->{'search_host'} ) && $param->{'search_host'} ne "") {
  99       push( @conditions, ' backups.hostID = ' . $param->{'search_host'} );
 100     }
 101
 102     if ( defined ($param->{'search_filename'}) && $param->{'search_filename'} ne "") {
 103         push (@conditions, " files.name LIKE '".$param->{'search_filename'}."%'");
 104         }
 105
 106     $retSQL = "";
 107     foreach $cond(@conditions)
 108       {
 109           if ($retSQL ne "")
 110             {
 111                 $retSQL .= " AND ";
 112             }
 113           $retSQL .= $cond;
 114       }
 115
 116
 117     return $retSQL;
 118 }
 119
 120 sub getFiles($)
 121   {
 122       my ($where) = @_;
 123
 124       my $dbh = DBI->connect( "dbi:SQLite:dbname=${TopDir}/$Conf{SearchDB}",
 125         "", "", { RaiseError => 1, AutoCommit => 1 } );
 126       my $sql =
 127         q{
 128               SELECT files.id                       AS fid,
 129                      hosts.name                     AS hname,
 130                      shares.name                    AS sname,
 131                      shares.share                   AS sharename,
 132                      backups.num                    AS backupNum,
 133                      files.name                     AS filename,
 134                      files.path                     AS filepath,
 135                      shares.share||files.fullpath AS networkPath,
 136                      date(files.date, 'unixepoch', 'localtime') AS date,
 137                      files.type                     AS filetype,
 138                      files.size                     AS size,
 139                      dvds.name                      AS dvd
 140                   FROM
 141                      files
 142                         INNER JOIN shares  ON files.shareID=shares.ID
 143                         INNER JOIN hosts   ON hosts.ID = shares.hostID
 144                         INNER JOIN backups ON backups.hostID = hosts.ID
 145                         LEFT  JOIN dvds    ON dvds.ID = files.dvdid
 146
 147           };
 148
 149       if (defined($where) && $where ne "")
 150         {
 151             $sql .= " WHERE ". $where;
 152         }
 153
 154
 155       my $st = $dbh->prepare(
 156           $sql
 157           );
 158
 159       $st->execute;
 160
 161       my @ret = ();
 162       my $tmp;
 163
 164       while ($tmp = $st->fetchrow_hashref())
 165         {
 166             push(@ret, {
 167                            'hname'       => $tmp->{'hname'},
 168                            'sname'       => $tmp->{'sname'},
 169                            'sharename'   => $tmp->{'sharename'},
 170                            'backupno'    => $tmp->{'backupNum'},
 171                            'fname'       => $tmp->{'filename'},
 172                            'fpath'       => $tmp->{'filepath'},
 173                            'networkpath' => $tmp->{'networkPath'},
 174                            'date'        => $tmp->{'date'},
 175                            'type'        => $tmp->{'filetype'},
 176                            'size'        => $tmp->{'size'},
 177                            'id'          => $tmp->{'fid'},
 178                            'dvd'         => $tmp->{'dvd'}
 179                        }
 180             );
 181
 182         }
 183
 184       $st->finish();
 185       $dbh->disconnect();
 186       return @ret;
 187   }
 188
 189 sub getBackupsNotBurned()
 190   {
 191       my $dbh = DBI->connect( "dbi:SQLite:dbname=${TopDir}/$Conf{SearchDB}",
 192         "", "", { RaiseError => 1, AutoCommit => 1 } );
 193       my $sql = q{
 194           SELECT
 195             hosts.ID         AS hostID,
 196             hosts.name       AS host,
 197             backups.num      AS backupno,
 198             backups.type     AS type,
 199             backups.date     AS date
 200           FROM backups, shares, files, hosts
 201           WHERE
 202             backups.num    = files.backupNum  AND
 203             shares.ID      = files.shareID    AND
 204             backups.hostID = shares.hostID    AND
 205             hosts.ID       = backups.hostID   AND
 206             files.dvdid    IS NULL
 207           GROUP BY
 208             backups.hostID, backups.num
 209       };
 210       my $st = $dbh -> prepare( $sql );
 211       my @ret = ();
 212       $st -> execute();
 213
 214       while ( my $tmp = $st -> fetchrow_hashref() )
 215         {
 216             push(@ret, {
 217                          'host'     => $tmp->{'host'},
 218                          'hostid'   => $tmp->{'hostID'},
 219                          'backupno' => $tmp->{'backupno'},
 220                          'type'     => $tmp->{'type'},
 221                          'date'     => $tmp->{'date'}
 222                        }
 223             );
 224         }
 225
 226       return @ret;
 227   }
 228
 229 sub displayBackupsGrid()
 230   {
 231       my $retHTML = "";
 232       my $addForm = 1;
 233
 234       if ($addForm)
 235         {
 236
 237             $retHTML .= <<EOF3;
 238 <script language="javascript" type="text/javascript">
 239 <!--
 240
 241     function checkAll(location)
 242     {
 243       for (var i=0;i<document.forma.elements.length;i++)
 244       {
 245         var e = document.forma.elements[i];
 246         if ((e.checked || !e.checked) && e.name != \'all\') {
 247             if (eval("document.forma."+location+".checked")) {
 248                 e.checked = true;
 249             } else {
 250                 e.checked = false;
 251             }
 252         }
 253       }
 254     }
 255 //-->
 256 </script>
 257 EOF3
 258               $retHTML .= q{<form name="forma" method="POST" action="}."$MyURL"."?action=burn\"";
 259               $retHTML.= q{<input type="hidden" value="burn" name="action">};
 260               $retHTML .= q{<input type="hidden" value="results" name="search_results">};
 261         }
 262       $retHTML .= "<table style=\"fview\">";
 263       $retHTML .= "<tr> ";
 264       if ($addForm)
 265         {
 266             $retHTML .= "<td class=\"tableheader\"><input type=\"checkbox\" name=\"allFiles\" onClick=\"checkAll('allFiles');\"></td>";
 267         }
 268       $retHTML .=  "<td class=\"tableheader\">Host</td> <td class=\"tableheader\">Backup no</td> <td class=\"tableheader\">Type</td> <td class=\"tableheader\">date</td></tr>";
 269       my @backups = getBackupsNotBurned();
 270       my $backup;
 271
 272       if ($addForm)
 273         {
 274             $retHTML .= "<tr>";
 275             $retHTML .= "<td colspan=7 style=\"tableheader\">";
 276             $retHTML .= "<input type=\"submit\" value=\"Burn selected backups on medium\" name=\"submitBurner\">";
 277             $retHTML .= "</td>";
 278             $retHTML .= "</tr>";
 279
 280         }
 281       foreach $backup(@backups)
 282         {
 283             my $ftype = "";
 284
 285             $retHTML .= "<tr>";
 286             if ($addForm)
 287               {
 288                   $retHTML .= "<td class=\"fview\"> <input type=\"checkbox\" name=\"fcb"
 289                     .$backup->{'hostid'}."_".$backup->{'backupno'}
 290                   ."\" value=\"".$backup->{'hostid'}."_".$backup->{'backupno'}."\"> </td>";
 291               }
 292
 293             $retHTML .= "<td class=\"fviewborder\">" . $backup->{'host'} . "</td>";
 294             $retHTML .= "<td class=\"fviewborder\">" . $backup->{'backupno'} . "</td>";
 295             $retHTML .= "<td class=\"fviewborder\">" . $backup->{'type'} . "</td>";
 296             $retHTML .= "<td class=\"fviewborder\">" . $backup->{'date'} . "<td>";
 297             $retHTML .= "</tr>";
 298         }
 299       $retHTML .= "</table>";
 300       if ($addForm)
 301        {
 302            $retHTML .= "</form>";
 303        }
 304
 305       return $retHTML;
 306
 307
 308   }
 309
 310 sub displayGrid($$)
 311   {
 312       my ($where, $addForm) = @_;
 313       my $retHTML = "";
 314
 315       if ($addForm)
 316         {
 317       $retHTML .= <<EOF3;
 318 <script language="javascript" type="text/javascript">
 319 <!--
 320
 321     function checkAll(location)
 322     {
 323       for (var i=0;i<document.forma.elements.length;i++)
 324       {
 325         var e = document.forma.elements[i];
 326         if ((e.checked || !e.checked) && e.name != \'all\') {
 327             if (eval("document.forma."+location+".checked")) {
 328                 e.checked = true;
 329             } else {
 330                 e.checked = false;
 331             }
 332         }
 333       }
 334     }
 335
 336
 337 //-->
 338 </script>
 339 EOF3
 340               $retHTML .= q{<form name="forma" method="POST" action="}."$MyURL"."?action=burn\"";
 341               $retHTML.= q{<input type="hidden" value="burn" name="action">};
 342               $retHTML .= q{<input type="hidden" value="results" name="search_results">};
 343         }
 344       $retHTML .= "<table style=\"fview\">";
 345       $retHTML .= "<tr> ";
 346       if ($addForm)
 347         {
 348             $retHTML .= "<td class=\"tableheader\"><input type=\"checkbox\" name=\"allFiles\" onClick=\"checkAll('allFiles');\"></td>";
 349         }
 350       $retHTML .=  "<td class=\"tableheader\">Host</td> <td class=\"tableheader\">Name</td> <td class=\"tableheader\">Type</td> <td class=\"tableheader\">backup no.</td> <td class=\"tableheader\">size</td> <td class=\"tableheader\">date</td>  <td class=\"tableheader\">Media</td></tr>";
 351       my @files = getFiles($where);
 352       my $file;
 353
 354       if ($addForm)
 355         {
 356             $retHTML .= "<tr>";
 357             $retHTML .= "<td colspan=7 style=\"tableheader\">";
 358             $retHTML .= "<input type=\"submit\" value=\"Burn selected files on medium\" name=\"submitBurner\">";
 359             $retHTML .= "</td>";
 360             $retHTML .= "</tr>";
 361
 362         }
 363       foreach $file(@files)
 364         {
 365             my $ftype = "";
 366
 367             if ($file->{'type'} == BPC_FTYPE_DIR)
 368               {
 369                   $ftype = "dir";
 370               }
 371             else
 372               {
 373                   $ftype = "file";
 374               }
 375             $retHTML .= "<tr>";
 376             if ($addForm)
 377               {
 378                   $retHTML .= "<td class=\"fview\"> <input type=\"checkbox\" name=\"fcb"
 379                     .$file->{'id'}
 380                   ."\" value=\"".$file->{'id'}."\"> </td>";
 381               }
 382
 383             $retHTML .= "<td class=\"fviewborder\">" . $file->{'hname'} ."</td>";
 384             $retHTML .= "<td class=\"fviewborder\">" . $file->{'fname'} . "</td>";
 385             $retHTML .= "<td class=\"fviewborder\">" . $ftype . "</td>";
 386             $retHTML .= "<td class=\"fviewborder\">" . $file->{'backupno'} . "</td>";
 387             $retHTML .= "<td class=\"fviewborder\">" . $file->{'size'} . "</td>";
 388             $retHTML .= "<td class=\"fviewborder\">" . $file->{'date'} . "</td>";
 389             $retHTML .= "<td class=\"fviewborder\">" . $file->{'dvd'} . "</td>";
 390             $retHTML .= "</tr>";
 391         }
 392       $retHTML .= "</table>";
 393       if ($addForm)
 394        {
 395            $retHTML .= "</form>";
 396        }
 397
 398       return $retHTML;
 399   }
 400
 401 1;