--- trunk/lib/BackupPC/SearchLib.pm 2005/07/29 17:30:25 26 +++ trunk/lib/BackupPC/SearchLib.pm 2005/07/31 16:16:55 31 @@ -5,6 +5,10 @@ use BackupPC::CGI::Lib qw(:all); use BackupPC::Attrib qw(:all); use DBI; +use vars qw(%In $MyURL); + +my $on_page = 100; +my $pager_pages = 10; sub getUnits() { my @ret = (); @@ -62,88 +66,78 @@ } -sub getFiles($$) - { - my ($where, $offset) = @_; - - - my $dbh = DBI->connect( "dbi:SQLite:dbname=${TopDir}/$Conf{SearchDB}", - "", "", { RaiseError => 1, AutoCommit => 1 } ); - my $sql = - q{ - SELECT files.id AS fid, - hosts.name AS hname, - shares.name AS sname, - shares.share AS sharename, - files.backupNum AS backupNum, - files.name AS filename, - files.path AS filepath, - shares.share||files.fullpath AS networkPath, - date(files.date, 'unixepoch', 'localtime') AS date, - files.type AS filetype, - files.size AS size, - dvds.name AS dvd +sub getFiles($$) { + my ($where, $offset) = @_; + + my $dbh = DBI->connect( "dbi:SQLite:dbname=${TopDir}/$Conf{SearchDB}", + "", "", { RaiseError => 1, AutoCommit => 1 } ); + + my $sql_cols = qq{ + files.id AS fid, + hosts.name AS hname, + shares.name AS sname, + shares.share AS sharename, + files.backupNum AS backupNum, + files.name AS filename, + files.path AS filepath, + shares.share||files.fullpath AS networkPath, + date(files.date, 'unixepoch', 'localtime') AS date, + files.type AS filetype, + files.size AS size, + dvds.name 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 LEFT JOIN dvds ON dvds.ID = files.dvdid - }; + }; - if (defined($where) && $where ne "") - { - $sql .= " WHERE ". $where; - } + my $sql_where; + $sql_where = " WHERE ". $where if ($where); - $sql .= - q{ - ORDER BY files.id - LIMIT 100 - OFFSET ? * 100 + 1 + my $sql_order = qq{ + ORDER BY files.id + LIMIT $on_page + OFFSET ? }; - - - - my $st = $dbh->prepare( - $sql - ); - if (!defined($offset) && $offset ne "") - { - $st->bind_param(1, $offset); - } - else - { - $st->bind_param(1,0); - } - $st->execute; - - my @ret = (); - my $tmp; - - while ($tmp = $st->fetchrow_hashref()) - { - push(@ret, { - 'hname' => $tmp->{'hname'}, - 'sname' => $tmp->{'sname'}, - 'sharename' => $tmp->{'sharename'}, - 'backupno' => $tmp->{'backupNum'}, - 'fname' => $tmp->{'filename'}, - 'fpath' => $tmp->{'filepath'}, - 'networkpath' => $tmp->{'networkPath'}, - 'date' => $tmp->{'date'}, - 'type' => $tmp->{'filetype'}, - 'size' => $tmp->{'size'}, - 'id' => $tmp->{'fid'}, - 'dvd' => $tmp->{'dvd'} - } - ); - - } - - $st->finish(); - $dbh->disconnect(); - return @ret; - } + + $offset ||= 0; + $offset = ($offset * $on_page) + 1; + + my $sth = $dbh->prepare(qq{ select count(files.id) $sql_from $sql_where }); + $sth->execute(); + + my ($results) = $sth->fetchrow_array(); + + $sth = $dbh->prepare(qq{ select $sql_cols $sql_from $sql_where $sql_order }); + $sth->execute( $offset ); + + my @ret; + + while (my $row = $sth->fetchrow_hashref()) { + push(@ret, { + 'hname' => $row->{'hname'}, + 'sname' => $row->{'sname'}, + 'sharename' => $row->{'sharename'}, + 'backupno' => $row->{'backupNum'}, + 'fname' => $row->{'filename'}, + 'fpath' => $row->{'filepath'}, + 'networkpath' => $row->{'networkPath'}, + 'date' => $row->{'date'}, + 'type' => $row->{'filetype'}, + 'size' => $row->{'size'}, + 'id' => $row->{'fid'}, + 'dvd' => $row->{'dvd'} + }); + } + + $sth->finish(); + $dbh->disconnect(); + return ($results, \@ret); +} sub getBackupsNotBurned() { @@ -170,14 +164,14 @@ my @ret = (); $st -> execute(); - while ( my $tmp = $st -> fetchrow_hashref() ) + while ( my $row = $st -> fetchrow_hashref() ) { push(@ret, { - 'host' => $tmp->{'host'}, - 'hostid' => $tmp->{'hostID'}, - 'backupno' => $tmp->{'backupno'}, - 'type' => $tmp->{'type'}, - 'date' => $tmp->{'date'} + 'host' => $row->{'host'}, + 'hostid' => $row->{'hostID'}, + 'backupno' => $row->{'backupno'}, + 'type' => $row->{'type'}, + 'date' => $row->{'date'} } ); } @@ -190,8 +184,7 @@ my $retHTML = ""; my $addForm = 1; - if ($addForm) - { + if ($addForm) { $retHTML .= < @@ -214,68 +207,70 @@ //--> EOF3 - $retHTML .= q{
}; $retHTML .= q{}; } - $retHTML .= ""; - $retHTML .= " "; - if ($addForm) - { + $retHTML .= qq{
}; + + if ($addForm) { $retHTML .= ""; } - $retHTML .= ""; - my @backups = getBackupsNotBurned(); - my $backup; - - if ($addForm) - { - $retHTML .= ""; - $retHTML .= ""; - $retHTML .= ""; - + $retHTML .= qq{}; + + my @backups = getBackupsNotBurned(); + my $backup; + + if ($addForm) { + $retHTML .= qq{}; } - foreach $backup(@backups) - { - my $ftype = ""; + + foreach $backup(@backups) { + + my $ftype = ""; - $retHTML .= ""; - if ($addForm) - { - $retHTML .= ""; - } + $retHTML .= ""; + if ($addForm) { + $retHTML .= qq{}; + } - $retHTML .= ""; - $retHTML .= ""; - $retHTML .= ""; - $retHTML .= ""; - } - $retHTML .= "
Host Backup no Type date
"; - $retHTML .= ""; - $retHTML .= "
HostBackup noTypedate
+ +
{'hostid'}."_".$backup->{'backupno'} - ."\" value=\"".$backup->{'hostid'}."_".$backup->{'backupno'}."\">
{'backupno'} . + qq{" value="} . $backup->{'hostid'}."_".$backup->{'backupno'} . + qq{">" . $backup->{'host'} . "" . $backup->{'backupno'} . "" . $backup->{'type'} . "" . $backup->{'date'} . ""; - $retHTML .= "
"; - if ($addForm) - { - $retHTML .= "
"; - } + $retHTML .= '' . $backup->{'host'} . '' . + '' . $backup->{'backupno'} . '' . + '' . $backup->{'type'} . '' . + '' . $backup->{'date'} . '' . + ''; + } + + $retHTML .= ""; + + if ($addForm) { + $retHTML .= ""; + } - return $retHTML; - - - } + return $retHTML; +} sub displayGrid($$$$) { my ($where, $addForm, $offset, $hilite) = @_; my $retHTML = ""; if ($addForm) { - $retHTML .= qq{
}; + $retHTML .= qq{}; $retHTML.= qq{}; $retHTML .= qq{}; } + + my ($results, $files) = getFiles($where, $offset); + + my ($from, $to) = (($offset * $on_page) + 1, ($offset * $on_page) + $on_page); + $retHTML .= qq{ +
Found $results files, showing $from - $to @@ -287,7 +282,7 @@ }; - my @files = getFiles($where, $offset); + my $file; sub hilite_html($$) { @@ -303,7 +298,7 @@ return sprintf(qq{%s}, $action, @_); } - foreach $file (@files) { + foreach $file (@{ $files }) { my $typeStr = BackupPC::Attrib::fileType2Text(undef, $file->{'type'}); $retHTML .= ""; @@ -323,19 +318,52 @@ } $retHTML .= "
ShareMedia
"; - # skip pager - return $retHTML; + # all variables which has to be transfered + foreach my $n (qw/search_day_from search_month_from search_year_from search_day_to search_month_to search_year_to search_backup_day_from search_backup_month_from search_backup_year_from search_backup_day_to search_backup_month_to search_backup_year_to search_filename offset/) { + $retHTML .= qq{\n}; + } + + my $del = ''; + my $max_page = int( $results / $on_page ); + my $page = 0; + + my $link_fmt = '%s'; + + $retHTML .= '
'; + + if ($offset > 0) { + $retHTML .= sprintf($link_fmt, $offset - 1, '<<') . ' '; + } + + while ($page <= $max_page) { + if ($page == $offset) { + $retHTML .= $del . '' . ($page + 1) . ''; + } else { + $retHTML .= $del . sprintf($link_fmt, $page, $page + 1); + } - $retHTML .= ""; - for (my $ii = 1; $ii <= $#files; $ii++) { - $retHTML .= "$ii"; - if ($ii < $#files) { - $retHTML .= " | "; + if ($page < $offset - $pager_pages && $page != 0) { + $retHTML .= " ... "; + $page = $offset - $pager_pages; + $del = ''; + } elsif ($page > $offset + $pager_pages && $page != $max_page) { + $retHTML .= " ... "; + $page = $max_page; + $del = ''; + } else { + $del = ' | '; + $page++; } } + if ($offset < $max_page) { + $retHTML .= ' ' . sprintf($link_fmt, $offset + 1, '>>'); + } + + $retHTML .= "
"; + $retHTML .= "
" if ($addForm); - + return $retHTML; }