--- trunk/lib/BackupPC/SearchLib.pm	2005/06/23 09:47:59	6
+++ trunk/lib/BackupPC/SearchLib.pm	2005/07/11 16:48:33	24
@@ -25,125 +25,70 @@
 }
 
 sub getWhere($) {
-    my ($param)    = @_;
-    my $retSQL     = "";
-    my @conditions = ();
-    my $cond;
+	my ($param)    = @_;
+	my @conditions;
 
-    
-   
-    
-    if ( defined( $param->{'search_backup_day_from'} ) && $param->{'search_backup_day_from'} ne "") {
-        push( @conditions,
-	    ' strftime("%d", datetime(backups.date, "unixepoch","localtime")) >= "'
-              . $param->{'search_backup_day_from'} ."\"");
-    }
-    if ( defined( $param->{'search_backup_day_to'} ) && $param->{'search_backup_day_to'} ne "") {
-        push( @conditions,
-	    ' strftime("%d", datetime(backups.date, "unixepoch","localtime")) <= "'
-              . $param->{'search_backup_day_from'}  ."\"");
-    }
-    if ( defined( $param->{'search_backup_month_from'} ) && $param->{'search_backup_month_from'} ne "") {
-        push( @conditions,
-	    ' strftime("%m", datetime(backups.date, "unixepoch","localtime")) >= "'
-              . $param->{'search_backup_month_from'}  ."\"");
-    }
-    if ( defined( $param->{'search_backup_month_to'} ) && $param->{'search_backup_month_to'} ne "") {
-        push( @conditions,
-	    ' strftime("%m", datetime(backups.date, "unixepoch","localtime")) <= "'
-              . $param->{'search_backup_month_to'}  ."\"");
-    }
-    if ( defined( $param->{'search_backup_year_from'} ) && $param->{'search_backup_year_from'} ne "") {
-        push( @conditions,
-	    ' strftime("%Y", datetime(backups.date, "unixepoch","localtime")) >= "'
-              . $param->{'search_backup_year_from'}  ."\"");
-    }
-    if ( defined( $param->{'search_backup_year_to'} ) && $param->{'search_backup_year_to'} ne "") {
-        push( @conditions,
-	    ' strftime("%Y", datetime(backups.date, "unixepoch","localtime")) <= "'
-              . $param->{'search_backup_year_to'}  ."\"");
-    }
-
-    if ( defined( $param->{'search_day_from'} )   && $param->{'search_day_from'} ne "" ) {
-        push( @conditions,
-            ' strftime("%d", datetime(files.date, "unixepoch","localtime")) >= "'
-              . $param->{'search_day_from'}  ."\"");
-    }
-    if ( defined( $param->{'search_month_from'} ) && $param->{'search_month_from'} ne "") {
-        push( @conditions,
-            ' strftime("%m", datetime(files.date, "unixepoch","localtime")) >= "'
-              . $param->{'search_month_from'}  ."\"");
-    }
-    if ( defined( $param->{'search_year_from'} ) && $param->{'search_year_from'} ne "") {
-        push( @conditions,
-            ' strftime("%Y", datetime(files.date, "unixepoch","localtime")) >= "'
-              . $param->{'search_year_from'}  ."\"");
-    }
-    if ( defined( $param->{'search_day_to'} )   && $param->{'search_day_to'} ne "" ) {
-        push( @conditions,
-            ' strftime("%d", datetime(files.date, "unixepoch","localtime")) <= "'
-              . $param->{'search_day_to'}  ."\"");
-    }
-    if ( defined( $param->{'search_month_to'} ) && $param->{'search_month_to'} ne "" ) {
-        push( @conditions,
-            ' strftime("%m", datetime(files.date, "unixepoch","localtime")) <= "'
-              . $param->{'search_month_to'} ."\"" );
-    }
-    if ( defined( $param->{'search_year_to'} )&& $param->{'search_year_to'} ne "" )  {
-        push( @conditions,
-            ' strftime("%Y", datetime(files.date, "unixepoch","localtime")) <= "'
-              . $param->{'search_year_to'} ."\"");
-    }
+	sub mk_iso_date($$) {
+		my ($name,$suffix) = @_;
 
-    if ( defined( $param->{'search_host'} ) && $param->{'search_host'} ne "") {
-      push( @conditions, ' backups.hostID = ' . $param->{'search_host'} );
-    }
+		my $yyyy = $param->{ $name . '_year_' . $suffix} || return;
+		my $mm .= $param->{ $name . '_month_' . $suffix} ||
+			( $suffix eq 'from' ? 1 : 12);
+		my $dd .= $param->{ $name . '_day_' . $suffix} ||
+			( $suffix eq 'from' ? 1 : 31);
+		return sprintf("%04d-%02d-%02d", $yyyy, $mm, $dd);
+	}
+
+	my $backup_from = mk_iso_date('search_backup', 'from');
+	push @conditions, qq{ date(backups.date, 'unixepoch','localtime') >= '$backup_from' } if ($backup_from);
+	my $backup_to = mk_iso_date('search_backup', 'to');
+	push @conditions, qq{ date(backups.date, 'unixepoch','localtime') <= '$backup_to' } if ($backup_to);
+
+	my $files_from = mk_iso_date('search', 'from');
+	push @conditions, qq{ date(files.date, 'unixepoch','localtime') >= '$files_from' } if ($files_from);
+	my $files_to = mk_iso_date('search', 'to');
+	push @conditions, qq{ date(files.date, 'unixepoch','localtime') <= '$files_to' } if ($files_to);
 
-    if ( defined ($param->{'search_filename'}) && $param->{'search_filename'} ne "") {
-	push (@conditions, " files.name LIKE '".$param->{'search_filename'}."%'");
-	}
+	print STDERR "backup: $backup_from - $backup_to files: $files_from - $files_to cond:",join(" | ",@conditions);
     
-    $retSQL = "";
-    foreach $cond(@conditions)
-      {
-	  if ($retSQL ne "")
-	    {
-		$retSQL .= " AND ";
-	    }
-	  $retSQL .= $cond;
-      }       
+    	push( @conditions, ' backups.hostID = ' . $param->{'search_host'} ) if ($param->{'search_host'});
 
-    
-    return $retSQL;
+	push (@conditions, " files.name LIKE '".$param->{'search_filename'}."%'") if ($param->{'search_filename'});
+
+	return (
+		join(" and ", @conditions),
+		$files_from, $files_to,
+		$backup_from, $backup_to
+	);
 }
 
-sub getFiles($)
+
+sub getFiles($$)
   {
-      my ($where) = @_;
+      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,
-	             backups.num                    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
-		  FROM 
-	             files 
-	                INNER JOIN shares  ON files.shareID=shares.ID
-	                INNER JOIN hosts   ON hosts.ID = shares.hostID
-	                INNER JOIN backups ON backups.hostID = hosts.ID
-	                LEFT  JOIN dvds    ON dvds.ID = files.dvdid
-	            
+		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
+		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 "")
@@ -151,11 +96,26 @@
 	    $sql .= " WHERE ". $where;	    
 	}
 
+      $sql .=
+	q{	    
+	    ORDER BY files.id
+	      LIMIT 100
+	      OFFSET ? * 100 + 1
+	};
+      
+      
       
       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 = ();
@@ -307,95 +267,70 @@
   
   }      
 
-sub displayGrid($$)
-  {
-      my ($where, $addForm) = @_;
-      my $retHTML = "";
-      
-      if ($addForm)
-	{
-      $retHTML .= <<EOF3;
-<script language="javascript" type="text/javascript">
-<!--
-
-    function checkAll(location)
-    {
-      for (var i=0;i<document.forma.elements.length;i++)
-      {
-        var e = document.forma.elements[i];
-        if ((e.checked || !e.checked) && e.name != \'all\') {
-            if (eval("document.forma."+location+".checked")) {
-                e.checked = true;
-            } else {
-                e.checked = false;
-            }
-        }
-      }
-    }
-
-
-//-->
-</script>      
-EOF3
-	      $retHTML .= q{<form name="forma" method="POST" action="}."$MyURL"."?action=burn\"";
-              $retHTML.= q{<input type="hidden" value="burn" name="action">};
-              $retHTML .= q{<input type="hidden" value="results" name="search_results">};
+sub displayGrid($$$$) {
+	my ($where, $addForm, $offset, $hilite) = @_;
+	my $retHTML = "";
+ 
+	if ($addForm) {
+		$retHTML .= qq{<form name="forma" method="POST" action="}.$MyURL.qq{?action=search">};
+		$retHTML.= qq{<input type="hidden" value="search" name="action">};
+		$retHTML .= qq{<input type="hidden" value="results" name="search_results">};
+	}
+	$retHTML .= qq{
+      	<table style="fview" width="100%">
+		<tr> 
+		<td class="tableheader">Host</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>
+		</tr>
+	};
+	my @files = getFiles($where, $offset);
+	my $file;
+
+	sub hilite_html($$) {
+		my ($html, $search) = @_;
+		$html =~ s#($search)#<b>$1</b>#gis;
+		return $html;
+	}
+
+	foreach $file (@files) {
+		my $typeStr  = BackupPC::Attrib::fileType2Text(undef, $file->{'type'});
+		$retHTML .= "<tr>";
+
+		foreach my $v ((
+			$file->{'hname'},
+			qq{<img src="$Conf{CgiImageDirURL}/icon-$typeStr.gif" align="center">&nbsp;} . hilite_html( $file->{'fpath'}, $hilite ),
+			$typeStr,
+			$file->{'backupno'},
+			$file->{'size'},
+			$file->{'date'},
+			$file->{'dvd'}
+		)) {
+			$retHTML .= qq{<td class="fviewborder">$v</td>};
+		}
+
+		$retHTML .= "</tr>";
+	}
+	$retHTML .= "</table>";
+
+	# skip pager
+	return $retHTML;
+
+	$retHTML .= "<INPUT TYPE=\"hidden\" VALUE=\"\" NAME=\"offset\">";
+	for (my $ii = 1; $ii <= $#files; $ii++) {
+		$retHTML .= "<a href = \"#\" onclick=\"document.forma.offset.value=$ii;document.forma.submit();\">$ii</a>";
+		if ($ii < $#files) {
+			$retHTML .= " | ";
+		}
 	}
-      $retHTML .= "<table style=\"fview\">";
-      $retHTML .= "<tr> ";
-      if ($addForm)
-	{
-	    $retHTML .= "<td class=\"tableheader\"><input type=\"checkbox\" name=\"allFiles\" onClick=\"checkAll('allFiles');\"></td>";
-	}
-      $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>";
-      my @files = getFiles($where);
-      my $file;
 
-      if ($addForm)
-	{
-	    $retHTML .= "<tr>";
-	    $retHTML .= "<td colspan=7 style=\"tableheader\">";
-	    $retHTML .= "<input type=\"submit\" value=\"Burn selected files on medium\" name=\"submitBurner\">";
-	    $retHTML .= "</td>";
-	    $retHTML .= "</tr>";
-	    
-	}
-      foreach $file(@files)
-	{
-	    my $ftype = "";
-	    
-	    if ($file->{'type'} == BPC_FTYPE_DIR)
-	      {
-		  $ftype = "dir";
-	      }
-	    else
-	      {
-		  $ftype = "file";
-	      }
-	    $retHTML .= "<tr>";
-	    if ($addForm)
-	      {
-		  $retHTML .= "<td class=\"fview\"> <input type=\"checkbox\" name=\"fcb"
-		    .$file->{'id'}
-		  ."\" value=\"".$file->{'id'}."\"> </td>";
-	      }	    
-	    
-	    $retHTML .= "<td class=\"fviewborder\">" . $file->{'hname'} ."</td>";
-	    $retHTML .= "<td class=\"fviewborder\">" . $file->{'fname'} . "</td>";
-	    $retHTML .= "<td class=\"fviewborder\">" . $ftype . "</td>";
-	    $retHTML .= "<td class=\"fviewborder\">" . $file->{'backupno'} . "</td>";
-	    $retHTML .= "<td class=\"fviewborder\">" . $file->{'size'} . "</td>";
-	    $retHTML .= "<td class=\"fviewborder\">" . $file->{'date'} . "</td>";
-	    $retHTML .= "<td class=\"fviewborder\">" . $file->{'dvd'} . "</td>";
-	    $retHTML .= "</tr>";
-	}
-      $retHTML .= "</table>";
-      if ($addForm)
-       {
-	   $retHTML .= "</form>";
-       }
+	$retHTML .= "</form>" if ($addForm);
       
-      return $retHTML;
-  }
+	return $retHTML;
+}
 
 1;