--- trunk/lib/BackupPC/SearchLib.pm	2005/08/20 17:19:48	53
+++ trunk/lib/BackupPC/SearchLib.pm	2005/08/28 10:14:48	83
@@ -7,6 +7,7 @@
 use DBI;
 use DateTime;
 use vars qw(%In $MyURL);
+use Time::HiRes qw/time/;
 
 my $on_page = 100;
 my $pager_pages = 10;
@@ -15,31 +16,29 @@
 my $db_user = $Conf{SearchUser} || '';
 
 sub getUnits() {
-    my @ret = ();
-    my $tmp;
-    my $dbh = DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } );
-    my $st =
-      $dbh->prepare(
-        " SELECT shares.ID AS ID, shares.share AS name FROM shares;");
-    $st->execute();
-    push (@ret, { 'ID' => '', 'name' => '-'});
-    while ( $tmp = $st->fetchrow_hashref() ) {
-        push( @ret, { 'ID' => $tmp->{'ID'}, 'name' => $tmp->{'name'} } );
-    }
-    $dbh->disconnect();
-    return @ret;
+	my @ret;
+
+	my $dbh = DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } );
+	my $sth = $dbh->prepare(qq{ SELECT id, share FROM shares ORDER BY share} );
+	$sth->execute();
+	push @ret, { 'id' => '', 'share' => '-'};	# dummy any
+
+	while ( my $row = $sth->fetchrow_hashref() ) {
+		push @ret, $row;
+	}
+	$dbh->disconnect();
+	return @ret;
 }
 
 sub epoch_to_iso {
 	my $t = shift || return;
-	my $dt = DateTime->from_epoch( epoch => $t ) || return;
-print STDERR "$t == ",$dt->epoch,"\n";
-	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'});
+	print STDERR "backup: $backup_from - $backup_to files: $files_from - $files_to cond:" . join(" | ",@conditions);
 
-	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,41 +98,53 @@
 		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
-			LEFT  JOIN dvds		ON dvds.ID = files.dvdid
+			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
 	};
 
 	my $sql_where;
+	my $where = getWhere($param);
 	$sql_where = " WHERE ". $where if ($where);
 
 	my $sql_order = qq{
-		ORDER BY files.id
-			LIMIT $on_page
-			OFFSET ?
+		ORDER BY files.date
+		LIMIT $on_page
+		OFFSET ?
 	};
 
+	my $sql_count = qq{ select count(files.id) $sql_from $sql_where };
+	my $sql_results = qq{ select $sql_cols $sql_from $sql_dvd_from $sql_where $sql_order };
+
 	$offset ||= 0;
-	$offset = ($offset * $on_page) + 1;
+	$offset = ($offset * $on_page);
 
-	my $sth = $dbh->prepare(qq{ select count(files.id) $sql_from $sql_where });
+	my $sth = $dbh->prepare($sql_count);
 	$sth->execute();
-
 	my ($results) = $sth->fetchrow_array();
 
-	$sth = $dbh->prepare(qq{ select $sql_cols $sql_from $sql_where $sql_order });
+	$sth = $dbh->prepare($sql_results);
 	$sth->execute( $offset );
 
+	if ($sth->rows != $results) {
+		my $bug = "$0 BUG: [[ $sql_count ]] = $results while [[ $sql_results ]] = " . $sth->rows;
+		$bug =~ s/\s+/ /gs;
+		print STDERR "$bug\n";
+	}
+
 	my @ret;
       
 	while (my $row = $sth->fetchrow_hashref()) {
@@ -147,7 +163,7 @@
 			'dvd'		=> $row->{'dvd'}
 		});
 	}
-      
+     
 	$sth->finish();
 	$dbh->disconnect();
 	return ($results, \@ret);
@@ -158,34 +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'}
-		       }
-		);
+	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;      
@@ -223,20 +235,33 @@
               $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></tr>};
+	$retHTML .=  qq{
+		<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>
+	};
 
 	my @backups = getBackupsNotBurned();
 	my $backup;
 
 	if ($addForm) {
-		$retHTML .= qq{<tr><td colspan=7 style="tableheader">
+		$retHTML .= qq{
+			<tr><td colspan=7 style="tableheader">
 			<input type="submit" value="Burn selected backups on medium" name="submitBurner">
-			</td></tr>};
+			</td></tr>
+		};
 	}
 
 	foreach $backup(@backups) {
@@ -245,16 +270,18 @@
 	    
 		$retHTML .= "<tr>";
 		if ($addForm) {
-			$retHTML .= qq{<td class="fview"><input type="checkbox" name="fcb} .
-				$backup->{'hostid'}."_".$backup->{'backupno'} . 
-				qq{" value="} . $backup->{'hostid'}."_".$backup->{'backupno'} .
-				qq{"></td>};
+			$retHTML .= '<td class="fview"><input type="checkbox" name="fcb' .
+				$backup->{'hostid'}.'_'.$backup->{'backupno'} . 
+				'" value="' . $backup->{'hostid'}.'_'.$backup->{'backupno'} .
+				'"></td>';
 		}	    
 	    
 		$retHTML .= '<td class="fviewborder">' . $backup->{'host'} . '</td>' .
 			'<td class="fviewborder">' . $backup->{'backupno'} . '</td>' .
 			'<td class="fviewborder">' . $backup->{'type'} . '</td>' .
-			'<td class="fviewborder">' . epoch_to_iso( $backup->{'date'} ) . '<td>' .
+			'<td class="fviewborder">' . epoch_to_iso( $backup->{'date'} ) . '</td>' .
+			'<td class="fviewborder">' . $backup->{'age'} . '</td>' .
+			'<td class="fviewborder">' . $backup->{'size'} . '</td>' .
 			'</tr>';
 	}
 
@@ -267,31 +294,54 @@
 	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($param, $offset);
+
+	my $dur_t = time() - $start_t;
+	my $dur = sprintf("%0.4fs", $dur_t);
+
+	my ($from, $to) = (($offset * $on_page) + 1, ($offset * $on_page) + $on_page);
+
+	if ($results <= 0) {
+		$retHTML .= qq{
+			<p style="color: red;">No results found...</p>
+		};
+		return $retHTML;
+	} else {
+		# DEBUG
+		#use Data::Dumper;
+		#$retHTML .= '<pre>' . Dumper($files) . '</pre>';
+	}
+
+
 	if ($addForm) {
 		$retHTML .= qq{<form name="forma" method="GET" action="$MyURL">};
 		$retHTML.= qq{<input type="hidden" value="search" name="action">};
 		$retHTML .= qq{<input type="hidden" value="results" name="search_results">};
 	}
 
-	my ($results, $files) = getFiles($where, $offset);
-
-	my ($from, $to) = (($offset * $on_page) + 1, ($offset * $on_page) + $on_page);
 
 	$retHTML .= qq{
-	<br/>Found $results files, showing $from - $to
-      	<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>
 	};
 
@@ -312,19 +362,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>";
 	}