--- trunk/lib/BackupPC/SearchLib.pm	2005/08/25 11:41:58	72
+++ trunk/lib/BackupPC/SearchLib.pm	2005/08/28 10:45:51	85
@@ -15,10 +15,17 @@
 my $dsn = $Conf{SearchDSN};
 my $db_user = $Conf{SearchUser} || '';
 
+my $dbh;
+
+sub get_dbh {
+	$dbh ||= DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } );
+	return $dbh;
+}
+
 sub getUnits() {
 	my @ret;
 
-	my $dbh = DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } );
+	my $dbh = get_dbh();
 	my $sth = $dbh->prepare(qq{ SELECT id, share FROM shares ORDER BY share} );
 	$sth->execute();
 	push @ret, { 'id' => '', 'share' => '-'};	# dummy any
@@ -26,21 +33,18 @@
 	while ( my $row = $sth->fetchrow_hashref() ) {
 		push @ret, $row;
 	}
-	$dbh->disconnect();
 	return @ret;
 }
 
 sub epoch_to_iso {
 	my $t = shift || return;
-	$t += 60 * 60 * +2;	# FIXME add TZ
-	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) = @_;
@@ -58,34 +62,39 @@
 		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, ' files.shareid = ' . $param->{'search_share'} ) if ($param->{'search_share'});
 
+    	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 } );
+	my $dbh = get_dbh();
 
 	my $sql_cols = qq{
 		files.id			AS fid,
@@ -98,7 +107,8 @@
 		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{
@@ -109,10 +119,11 @@
 	};
 
 	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{
@@ -160,13 +171,12 @@
 	}
      
 	$sth->finish();
-	$dbh->disconnect();
 	return ($results, \@ret);
 }
 
 sub getBackupsNotBurned() {
 
-	my $dbh = DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } );
+	my $dbh = get_dbh();
 	my $sql = q{ 
 	SELECT
 		backups.hostID		AS hostid,
@@ -175,8 +185,10 @@
 		min(backups.type)	AS type,
 		min(backups.date)	AS date,
 		min(backups.size)	AS size
-	FROM backups
-		INNER JOIN hosts	ON hosts.ID = backups.hostID
+	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 
 		files.dvdid	IS NULL
 	GROUP BY 
@@ -228,18 +240,21 @@
               $retHTML.= q{<input type="hidden" value="burn" name="action">};
               $retHTML .= q{<input type="hidden" value="results" name="search_results">};
 	}
-	$retHTML .= qq{<table style="fview"><tr>};
+	$retHTML .= qq{
+		<table style="fview" border="1" cellspacing="0" cellpadding="3">
+		<tr class="tableheader">
+	};
 
 	if ($addForm) {
 	    $retHTML .= "<td class=\"tableheader\"><input type=\"checkbox\" name=\"allFiles\" onClick=\"checkAll('allFiles');\"></td>";
 	}
 	$retHTML .=  qq{
-		<td class="tableheader">Host</td>
-		<td class="tableheader">Backup no</td>
-		<td class="tableheader">Type</td>
-		<td class="tableheader">date</td>
-		<td class="tableheader">age/days</td>
-		<td class="tableheader">size/MB</td>
+		<td align="center">Host</td>
+		<td align="center">Backup no</td>
+		<td align="center">Type</td>
+		<td align="center">date</td>
+		<td align="center">age/days</td>
+		<td align="center">size/MB</td>
 		</tr>
 	};
 
@@ -284,13 +299,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);
@@ -317,16 +336,17 @@
 
 
 	$retHTML .= qq{
-	<br/>Found <b>$results files</b> showing <b>$from - $to</b> (took $dur)
-      	<table style="fview" width="100%">
-		<tr> 
-		<td class="tableheader">Share</td>
-		<td class="tableheader">Name</td>
-		<td class="tableheader">Type</td>
-		<td class="tableheader">#</td>
-		<td class="tableheader">Size</td>
-		<td class="tableheader">Date</td>
-		<td class="tableheader">Media</td>
+	<div>
+	Found <b>$results files</b> showing <b>$from - $to</b> (took $dur)
+	</div>
+      	<table style="fview" width="100%" border="0" cellpadding="2" cellspacing="0">
+		<tr class="fviewheader"> 
+		<td align="center">Share</td>
+		<td align="center">Type and Name</td>
+		<td align="center">#</td>
+		<td align="center">Size</td>
+		<td align="center">Date</td>
+		<td align="center">Media</td>
 		</tr>
 	};
 
@@ -347,19 +367,15 @@
 
 	foreach $file (@{ $files }) {
 		my $typeStr  = BackupPC::Attrib::fileType2Text(undef, $file->{'type'});
-		$retHTML .= "<tr>";
+		$retHTML .= qq{<tr class="fviewborder">};
 
-		foreach my $v ((
-			$file->{'sharename'},
-			qq{<img src="$Conf{CgiImageDirURL}/icon-$typeStr.gif" align="center">&nbsp;} . 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{<td class="fviewborder">$v</td>};
-		}
+		$retHTML .=
+			qq{<td class="fviewborder" align="right">} . $file->{'sharename'} . qq{</td>} .
+			qq{<td class="fviewborder"><img src="$Conf{CgiImageDirURL}/icon-$typeStr.gif" alt="$typeStr" align="middle">&nbsp;} . hilite_html( $file->{'fpath'}, $hilite ) . qq{</td>} .
+			qq{<td class="fviewborder" align="center">} . restore_link( $typeStr, ${EscURI( $file->{'hname'} )}, $file->{'backupno'}, ${EscURI( $file->{'sname'})}, ${EscURI( $file->{'fpath'} )}, $file->{'backupno'} ) . qq{</td>} .
+			qq{<td class="fviewborder" align="right">} . $file->{'size'} . qq{</td>} .
+			qq{<td class="fviewborder">} . epoch_to_iso( $file->{'date'} ) . qq{</td>} .
+			qq{<td class="fviewborder">} . $file->{'dvd'} . qq{</td>};
 
 		$retHTML .= "</tr>";
 	}
@@ -374,19 +390,33 @@
 	my $max_page = int( $results / $on_page );
 	my $page = 0;
 
-	my $link_fmt = '<a href = "#" onclick="document.forma.offset.value=%d;document.forma.submit();">%s</a>';
+	sub page_link($$$) {
+		my ($param,$page,$display) = @_;
+
+		$param->{'offset'} = $page;
+
+		my $html = '<a href = "' . $MyURL;
+		my $del = '?';
+		foreach my $k (keys %{ $param }) {
+			if ($param->{$k}) {
+				$html .= $del . $k . '=' . ${EscURI( $param->{$k} )};
+				$del = '&';
+			}
+		}
+		$html .= '">' . $display . '</a>';
+	}
 
 	$retHTML .= '<div style="text-align: center;">';
 
 	if ($offset > 0) {
-		$retHTML .= sprintf($link_fmt, $offset - 1, '&lt;&lt;') . ' ';
+		$retHTML .= page_link($param, $offset - 1, '&lt;&lt;') . ' ';
 	}
 
 	while ($page <= $max_page) {
 		if ($page == $offset) {
 			$retHTML .= $del . '<b>' . ($page + 1) . '</b>';
 		} else {
-			$retHTML .= $del . sprintf($link_fmt, $page, $page + 1);
+			$retHTML .= $del . page_link($param, $page, $page + 1);
 		}
 
 		if ($page < $offset - $pager_pages && $page != 0) {
@@ -404,7 +434,7 @@
 	}
 
 	if ($offset < $max_page) {
-		$retHTML .= ' ' . sprintf($link_fmt, $offset + 1, '&gt;&gt;');
+		$retHTML .= ' ' . page_link($param, $offset + 1, '&gt;&gt;');
 	}
 
 	$retHTML .= "</div>";