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, $offset) = @_;
 123
 124
 125       my $dbh = DBI->connect( "dbi:SQLite:dbname=${TopDir}/$Conf{SearchDB}",
 126         "", "", { RaiseError => 1, AutoCommit => 1 } );
 127       my $sql =
 128         q{
 129                 SELECT  files.id                        AS fid,
 130                         hosts.name                      AS hname,
 131                         shares.name                     AS sname,
 132                         shares.share                    AS sharename,
 133                         files.backupNum                 AS backupNum,
 134                         files.name                      AS filename,
 135                         files.path                      AS filepath,
 136                         shares.share||files.fullpath    AS networkPath,
 137                         date(files.date, 'unixepoch', 'localtime') AS date,
 138                         files.type                      AS filetype,
 139                         files.size                      AS size,
 140                         dvds.name                       AS dvd
 141                 FROM files
 142                         INNER JOIN shares       ON files.shareID=shares.ID
 143                         INNER JOIN hosts        ON hosts.ID = shares.hostID
 144                         LEFT  JOIN dvds         ON dvds.ID = files.dvdid
 145           };
 146
 147       if (defined($where) && $where ne "")
 148         {
 149             $sql .= " WHERE ". $where;
 150         }
 151
 152       $sql .=
 153         q{
 154             ORDER BY files.id
 155               LIMIT 100
 156               OFFSET ? * 100 + 1
 157         };
 158
 159
 160
 161       my $st = $dbh->prepare(
 162           $sql
 163           );
 164       if (!defined($offset) && $offset ne "")
 165       {
 166         $st->bind_param(1, $offset);
 167       }
 168       else
 169       {
 170         $st->bind_param(1,0);
 171       }
 172       $st->execute;
 173
 174       my @ret = ();
 175       my $tmp;
 176
 177       while ($tmp = $st->fetchrow_hashref())
 178         {
 179             push(@ret, {
 180                            'hname'       => $tmp->{'hname'},
 181                            'sname'       => $tmp->{'sname'},
 182                            'sharename'   => $tmp->{'sharename'},
 183                            'backupno'    => $tmp->{'backupNum'},
 184                            'fname'       => $tmp->{'filename'},
 185                            'fpath'       => $tmp->{'filepath'},
 186                            'networkpath' => $tmp->{'networkPath'},
 187                            'date'        => $tmp->{'date'},
 188                            'type'        => $tmp->{'filetype'},
 189                            'size'        => $tmp->{'size'},
 190                            'id'          => $tmp->{'fid'},
 191                            'dvd'         => $tmp->{'dvd'}
 192                        }
 193             );
 194
 195         }
 196
 197       $st->finish();
 198       $dbh->disconnect();
 199       return @ret;
 200   }
 201
 202 sub getBackupsNotBurned()
 203   {
 204       my $dbh = DBI->connect( "dbi:SQLite:dbname=${TopDir}/$Conf{SearchDB}",
 205         "", "", { RaiseError => 1, AutoCommit => 1 } );
 206       my $sql = q{
 207           SELECT
 208             hosts.ID         AS hostID,
 209             hosts.name       AS host,
 210             backups.num      AS backupno,
 211             backups.type     AS type,
 212             backups.date     AS date
 213           FROM backups, shares, files, hosts
 214           WHERE
 215             backups.num    = files.backupNum  AND
 216             shares.ID      = files.shareID    AND
 217             backups.hostID = shares.hostID    AND
 218             hosts.ID       = backups.hostID   AND
 219             files.dvdid    IS NULL
 220           GROUP BY
 221             backups.hostID, backups.num
 222       };
 223       my $st = $dbh -> prepare( $sql );
 224       my @ret = ();
 225       $st -> execute();
 226
 227       while ( my $tmp = $st -> fetchrow_hashref() )
 228         {
 229             push(@ret, {
 230                          'host'     => $tmp->{'host'},
 231                          'hostid'   => $tmp->{'hostID'},
 232                          'backupno' => $tmp->{'backupno'},
 233                          'type'     => $tmp->{'type'},
 234                          'date'     => $tmp->{'date'}
 235                        }
 236             );
 237         }
 238
 239       return @ret;
 240   }
 241
 242 sub displayBackupsGrid()
 243   {
 244       my $retHTML = "";
 245       my $addForm = 1;
 246
 247       if ($addForm)
 248         {
 249
 250             $retHTML .= <<EOF3;
 251 <script language="javascript" type="text/javascript">
 252 <!--
 253
 254     function checkAll(location)
 255     {
 256       for (var i=0;i<document.forma.elements.length;i++)
 257       {
 258         var e = document.forma.elements[i];
 259         if ((e.checked || !e.checked) && e.name != \'all\') {
 260             if (eval("document.forma."+location+".checked")) {
 261                 e.checked = true;
 262             } else {
 263                 e.checked = false;
 264             }
 265         }
 266       }
 267     }
 268 //-->
 269 </script>
 270 EOF3
 271               $retHTML .= q{<form name="forma" method="POST" action="}."$MyURL"."?action=burn\"";
 272               $retHTML.= q{<input type="hidden" value="burn" name="action">};
 273               $retHTML .= q{<input type="hidden" value="results" name="search_results">};
 274         }
 275       $retHTML .= "<table style=\"fview\">";
 276       $retHTML .= "<tr> ";
 277       if ($addForm)
 278         {
 279             $retHTML .= "<td class=\"tableheader\"><input type=\"checkbox\" name=\"allFiles\" onClick=\"checkAll('allFiles');\"></td>";
 280         }
 281       $retHTML .=  "<td class=\"tableheader\">Host</td> <td class=\"tableheader\">Backup no</td> <td class=\"tableheader\">Type</td> <td class=\"tableheader\">date</td></tr>";
 282       my @backups = getBackupsNotBurned();
 283       my $backup;
 284
 285       if ($addForm)
 286         {
 287             $retHTML .= "<tr>";
 288             $retHTML .= "<td colspan=7 style=\"tableheader\">";
 289             $retHTML .= "<input type=\"submit\" value=\"Burn selected backups on medium\" name=\"submitBurner\">";
 290             $retHTML .= "</td>";
 291             $retHTML .= "</tr>";
 292
 293         }
 294       foreach $backup(@backups)
 295         {
 296             my $ftype = "";
 297
 298             $retHTML .= "<tr>";
 299             if ($addForm)
 300               {
 301                   $retHTML .= "<td class=\"fview\"> <input type=\"checkbox\" name=\"fcb"
 302                     .$backup->{'hostid'}."_".$backup->{'backupno'}
 303                   ."\" value=\"".$backup->{'hostid'}."_".$backup->{'backupno'}."\"> </td>";
 304               }
 305
 306             $retHTML .= "<td class=\"fviewborder\">" . $backup->{'host'} . "</td>";
 307             $retHTML .= "<td class=\"fviewborder\">" . $backup->{'backupno'} . "</td>";
 308             $retHTML .= "<td class=\"fviewborder\">" . $backup->{'type'} . "</td>";
 309             $retHTML .= "<td class=\"fviewborder\">" . $backup->{'date'} . "<td>";
 310             $retHTML .= "</tr>";
 311         }
 312       $retHTML .= "</table>";
 313       if ($addForm)
 314        {
 315            $retHTML .= "</form>";
 316        }
 317
 318       return $retHTML;
 319
 320
 321   }
 322
 323 sub displayGrid($$$)
 324   {
 325       my ($where, $addForm, $offset) = @_;
 326       my $retHTML = "";
 327
 328       if ($addForm)
 329         {
 330               $retHTML .= q{<form name="forma" method="POST" action="}."$MyURL"."?action=search\"";
 331               $retHTML.= q{<input type="hidden" value="search" name="action">};
 332               $retHTML .= q{<input type="hidden" value="results" name="search_results">};
 333         }
 334       $retHTML .= "<table style=\"fview\">";
 335       $retHTML .= "<tr> ";
 336       $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>";
 337       my @files = getFiles($where, $offset);
 338       my $file;
 339
 340       foreach $file(@files)
 341         {
 342             my $ftype = "";
 343
 344             if ($file->{'type'} == BPC_FTYPE_DIR)
 345               {
 346                   $ftype = "dir";
 347               }
 348             else
 349               {
 350                   $ftype = "file";
 351               }
 352             $retHTML .= "<tr>";
 353             $retHTML .= "<td class=\"fviewborder\">" . $file->{'hname'} ."</td>";
 354             $retHTML .= "<td class=\"fviewborder\">" . $file->{'fname'} . "</td>";
 355             $retHTML .= "<td class=\"fviewborder\">" . $ftype . "</td>";
 356             $retHTML .= "<td class=\"fviewborder\">" . $file->{'backupno'} . "</td>";
 357             $retHTML .= "<td class=\"fviewborder\">" . $file->{'size'} . "</td>";
 358             $retHTML .= "<td class=\"fviewborder\">" . $file->{'date'} . "</td>";
 359             $retHTML .= "<td class=\"fviewborder\">" . $file->{'dvd'} . "</td>";
 360             $retHTML .= "</tr>";
 361         }
 362       $retHTML .= "</table>";
 363
 364
 365
 366       $retHTML .= "<INPUT TYPE=\"hidden\" VALUE=\"\" NAME=\"offset\">";
 367       for (my $ii = 1; $ii <= $#files; $ii++)
 368       {
 369           $retHTML .= "<a href = \"#\" onclick=\"document.forma.offset.value=$ii;document.forma.submit();\">$ii</a>";
 370           if ($ii < $#files)
 371             {
 372                 $retHTML .= " | ";
 373             }
 374       }
 375
 376
 377        if ($addForm)
 378        {
 379            $retHTML .= "</form>";
 380        }
 381
 382       return $retHTML;
 383   }
 384
 385 1;