--- trunk/lib/BackupPC/SearchLib.pm 2005/08/21 15:29:24 59 +++ trunk/lib/BackupPC/SearchLib.pm 2005/08/28 10:14:48 83 @@ -19,7 +19,7 @@ my @ret; my $dbh = DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } ); - my $sth = $dbh->prepare(qq{ SELECT id, share FROM shares} ); + my $sth = $dbh->prepare(qq{ SELECT id, share FROM shares ORDER BY share} ); $sth->execute(); push @ret, { 'id' => '', 'share' => '-'}; # dummy any @@ -32,14 +32,13 @@ sub epoch_to_iso { my $t = shift || return; - my $dt = DateTime->from_epoch( epoch => $t ) || return; - print STDERR "BUG: $t != " . $dt->epoch . "\n" unless ($t == $dt->epoch); - return $dt->ymd . ' ' . $dt->hms; + my $iso = BackupPC::Lib::timeStamp($t); + $iso =~ s/\s/ /g; + return $iso; } -sub getWhere($) { - my ($param) = @_; - my @conditions; +sub dates_from_form($) { + my $param = shift || return; sub mk_epoch_date($$) { my ($name,$suffix) = @_; @@ -57,32 +56,37 @@ return $dt->epoch || 'NULL'; } - my $backup_from = mk_epoch_date('search_backup', 'from'); + return ( + mk_epoch_date('search_backup', 'from'), + mk_epoch_date('search_backup', 'to'), + mk_epoch_date('search', 'from'), + mk_epoch_date('search', 'to'), + ); +} + + +sub getWhere($) { + my $param = shift || return; + + my ($backup_from, $backup_to, $files_from, $files_to) = dates_from_form($param); + + my @conditions; push @conditions, qq{ backups.date >= $backup_from } if ($backup_from); - my $backup_to = mk_epoch_date('search_backup', 'to'); push @conditions, qq{ backups.date <= $backup_to } if ($backup_to); - - my $files_from = mk_epoch_date('search', 'from'); push @conditions, qq{ files.date >= $files_from } if ($files_from); - my $files_to = mk_epoch_date('search', 'to'); push @conditions, qq{ files.date <= $files_to } if ($files_to); print STDERR "backup: $backup_from - $backup_to files: $files_from - $files_to cond:" . join(" | ",@conditions); - - push( @conditions, ' backups.hostID = ' . $param->{'search_host'} ) if ($param->{'search_host'}); - push (@conditions, " upper(files.name) LIKE upper('%".$param->{'search_filename'}."%')") if ($param->{'search_filename'}); + push( @conditions, ' files.shareid = ' . $param->{'search_share'} ) if ($param->{'search_share'}); + push (@conditions, " upper(files.path) LIKE upper('%".$param->{'search_filename'}."%')") if ($param->{'search_filename'}); - return ( - join(" and ", @conditions), - $files_from, $files_to, - $backup_from, $backup_to - ); + return join(" and ", @conditions); } sub getFiles($$) { - my ($where, $offset) = @_; + my ($param, $offset) = @_; my $dbh = DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } ); @@ -94,29 +98,30 @@ files.backupNum AS backupNum, files.name AS filename, files.path AS filepath, - shares.share||files.fullpath AS networkPath, files.date AS date, files.type AS filetype, files.size AS size, - dvds.name AS dvd + -- dvds.name AS dvd + null AS dvd }; my $sql_from = qq{ FROM files INNER JOIN shares ON files.shareID=shares.ID INNER JOIN hosts ON hosts.ID = shares.hostID - INNER JOIN backups ON backups.num = files.backupNum and backups.hostID = hosts.ID + INNER JOIN backups ON backups.num = files.backupNum and backups.hostID = hosts.ID AND backups.shareID = shares.ID }; my $sql_dvd_from = qq{ - LEFT JOIN dvds ON dvds.ID = files.dvdid + -- LEFT JOIN dvds ON dvds.ID = files.dvdid }; my $sql_where; + my $where = getWhere($param); $sql_where = " WHERE ". $where if ($where); my $sql_order = qq{ - ORDER BY files.id + ORDER BY files.date LIMIT $on_page OFFSET ? }; @@ -169,36 +174,30 @@ my $dbh = DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } ); my $sql = q{ SELECT - hosts.ID AS hostid, + backups.hostID AS hostid, min(hosts.name) AS host, backups.num AS backupno, min(backups.type) AS type, - min(backups.date) AS date - FROM backups, shares, files, hosts + min(backups.date) AS date, + min(backups.size) AS size + FROM files + INNER JOIN shares ON files.shareID=shares.ID + INNER JOIN hosts ON hosts.ID = shares.hostID + INNER JOIN backups ON backups.num = files.backupNum and backups.hostID = hosts.ID AND backups.shareID = shares.ID WHERE - backups.num = files.backupNum AND - shares.ID = files.shareID AND - backups.hostID = shares.hostID AND - hosts.ID = backups.hostID AND files.dvdid IS NULL GROUP BY - backups.hostID, backups.num, hosts.id + backups.hostID, backups.num ORDER BY min(backups.date) }; my $sth = $dbh->prepare( $sql ); my @ret; $sth->execute(); - while ( my $row = $sth->fetchrow_hashref() ) { - push(@ret, { - 'host' => $row->{'host'}, - 'hostid' => $row->{'hostid'}, - 'backupno' => $row->{'backupno'}, - 'type' => $row->{'type'}, - 'date' => $row->{'date'}, - 'age' => sprintf("%0.1f", ( (time() - $row->{'date'}) / 86400 ) ), - } - ); + while ( my $row = $sth->fetchrow_hashref() ) { + $row->{'age'} = sprintf("%0.1f", ( (time() - $row->{'date'}) / 86400 ) ); + $row->{'size'} = sprintf("%0.2f", $row->{'size'} / 1024 / 1024); + push @ret, $row; } return @ret; @@ -236,17 +235,21 @@ $retHTML.= q{}; $retHTML .= q{}; } - $retHTML .= qq{}; + $retHTML .= qq{ +
+ + }; if ($addForm) { $retHTML .= ""; } $retHTML .= qq{ - - - - - + + + + + + }; @@ -278,6 +281,7 @@ '' . '' . '' . + '' . ''; } @@ -290,13 +294,17 @@ return $retHTML; } -sub displayGrid($$$$) { - my ($where, $addForm, $offset, $hilite) = @_; +sub displayGrid($$) { + my ($param, $addForm) = @_; + + my $offset = $param->{'offset'}; + my $hilite = $param->{'search_filename'}; + my $retHTML = ""; my $start_t = time(); - my ($results, $files) = getFiles($where, $offset); + my ($results, $files) = getFiles($param, $offset); my $dur_t = time() - $start_t; my $dur = sprintf("%0.4fs", $dur_t); @@ -323,16 +331,17 @@ $retHTML .= qq{ -
Found $results files showing $from - $to (took $dur) -
HostBackup noTypedateage/daysHostBackup noTypedateage/dayssize/MB
' . $backup->{'type'} . '' . epoch_to_iso( $backup->{'date'} ) . '' . $backup->{'age'} . '' . $backup->{'size'} . '
- - - - - - - - +
+ Found $results files showing $from - $to (took $dur) +
+
ShareNameType#SizeDateMedia
+ + + + + + + }; @@ -353,19 +362,15 @@ foreach $file (@{ $files }) { my $typeStr = BackupPC::Attrib::fileType2Text(undef, $file->{'type'}); - $retHTML .= ""; + $retHTML .= qq{}; - foreach my $v (( - $file->{'sharename'}, - qq{ } . hilite_html( $file->{'fpath'}, $hilite ), - $typeStr, - restore_link( $typeStr, $file->{'hname'}, $file->{'backupno'}, $file->{'sname'}, $file->{'fpath'}, $file->{'backupno'} ), - $file->{'size'}, - epoch_to_iso( $file->{'date'} ), - $file->{'dvd'} - )) { - $retHTML .= qq{}; - } + $retHTML .= + qq{} . + qq{} . + qq{} . + qq{} . + qq{} . + qq{}; $retHTML .= ""; }
ShareType and Name#SizeDateMedia
$v} . $file->{'sharename'} . qq{$typeStr } . hilite_html( $file->{'fpath'}, $hilite ) . qq{} . restore_link( $typeStr, ${EscURI( $file->{'hname'} )}, $file->{'backupno'}, ${EscURI( $file->{'sname'})}, ${EscURI( $file->{'fpath'} )}, $file->{'backupno'} ) . qq{} . $file->{'size'} . qq{} . epoch_to_iso( $file->{'date'} ) . qq{} . $file->{'dvd'} . qq{