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