--- trunk/lib/BackupPC/SearchLib.pm	2005/07/11 14:31:51	18
+++ trunk/lib/BackupPC/SearchLib.pm	2005/07/11 14:32:40	19
@@ -25,98 +25,44 @@
 }
 
 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'} ."\"");
-    }
-
-    if ( defined( $param->{'search_host'} ) && $param->{'search_host'} ne "") {
-      push( @conditions, ' backups.hostID = ' . $param->{'search_host'} );
-    }
+	sub mk_iso_date($$) {
+		my ($name,$suffix) = @_;
 
-    if ( defined ($param->{'search_filename'}) && $param->{'search_filename'} ne "") {
-	push (@conditions, " files.name LIKE '".$param->{'search_filename'}."%'");
+		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);
 	}
-    
-    $retSQL = "";
-    foreach $cond(@conditions)
-      {
-	  if ($retSQL ne "")
-	    {
-		$retSQL .= " AND ";
-	    }
-	  $retSQL .= $cond;
-      }       
 
+	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);
+
+	print STDERR "backup: $backup_from - $backup_to files: $files_from - $files_to cond:",join(" | ",@conditions);
     
-    return $retSQL;
+    	push( @conditions, ' backups.hostID = ' . $param->{'search_host'} ) if ($param->{'search_host'});
+
+	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($$)
   {
       my ($where, $offset) = @_;
@@ -141,6 +87,7 @@
 		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
 			LEFT  JOIN dvds		ON dvds.ID = files.dvdid
 	  };