25 |
} |
} |
26 |
|
|
27 |
sub getWhere($) { |
sub getWhere($) { |
28 |
my ($param) = @_; |
my ($param) = @_; |
29 |
my $retSQL = ""; |
my @conditions; |
|
my @conditions = (); |
|
|
my $cond; |
|
30 |
|
|
31 |
|
sub mk_iso_date($$) { |
32 |
|
my ($name,$suffix) = @_; |
|
|
|
|
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'} ); |
|
|
} |
|
33 |
|
|
34 |
if ( defined ($param->{'search_filename'}) && $param->{'search_filename'} ne "") { |
my $yyyy = $param->{ $name . '_year_' . $suffix} || return; |
35 |
push (@conditions, " files.name LIKE '".$param->{'search_filename'}."%'"); |
my $mm .= $param->{ $name . '_month_' . $suffix} || |
36 |
|
( $suffix eq 'from' ? 1 : 12); |
37 |
|
my $dd .= $param->{ $name . '_day_' . $suffix} || |
38 |
|
( $suffix eq 'from' ? 1 : 31); |
39 |
|
return sprintf("%04d-%02d-%02d", $yyyy, $mm, $dd); |
40 |
} |
} |
|
|
|
|
$retSQL = ""; |
|
|
foreach $cond(@conditions) |
|
|
{ |
|
|
if ($retSQL ne "") |
|
|
{ |
|
|
$retSQL .= " AND "; |
|
|
} |
|
|
$retSQL .= $cond; |
|
|
} |
|
41 |
|
|
42 |
|
my $backup_from = mk_iso_date('search_backup', 'from'); |
43 |
|
push @conditions, qq{ date(backups.date, 'unixepoch','localtime') >= '$backup_from' } if ($backup_from); |
44 |
|
my $backup_to = mk_iso_date('search_backup', 'to'); |
45 |
|
push @conditions, qq{ date(backups.date, 'unixepoch','localtime') <= '$backup_to' } if ($backup_to); |
46 |
|
|
47 |
|
my $files_from = mk_iso_date('search', 'from'); |
48 |
|
push @conditions, qq{ date(files.date, 'unixepoch','localtime') >= '$files_from' } if ($files_from); |
49 |
|
my $files_to = mk_iso_date('search', 'to'); |
50 |
|
push @conditions, qq{ date(files.date, 'unixepoch','localtime') <= '$files_to' } if ($files_to); |
51 |
|
|
52 |
|
print STDERR "backup: $backup_from - $backup_to files: $files_from - $files_to cond:",join(" | ",@conditions); |
53 |
|
|
54 |
return $retSQL; |
push( @conditions, ' backups.hostID = ' . $param->{'search_host'} ) if ($param->{'search_host'}); |
55 |
|
|
56 |
|
push (@conditions, " files.name LIKE '".$param->{'search_filename'}."%'") if ($param->{'search_filename'}); |
57 |
|
|
58 |
|
return ( |
59 |
|
join(" and ", @conditions), |
60 |
|
$files_from, $files_to, |
61 |
|
$backup_from, $backup_to |
62 |
|
); |
63 |
} |
} |
64 |
|
|
65 |
|
|
66 |
sub getFiles($$) |
sub getFiles($$) |
67 |
{ |
{ |
68 |
my ($where, $offset) = @_; |
my ($where, $offset) = @_; |
87 |
FROM files |
FROM files |
88 |
INNER JOIN shares ON files.shareID=shares.ID |
INNER JOIN shares ON files.shareID=shares.ID |
89 |
INNER JOIN hosts ON hosts.ID = shares.hostID |
INNER JOIN hosts ON hosts.ID = shares.hostID |
90 |
|
INNER JOIN backups ON backups.num = files.backupNum |
91 |
LEFT JOIN dvds ON dvds.ID = files.dvdid |
LEFT JOIN dvds ON dvds.ID = files.dvdid |
92 |
}; |
}; |
93 |
|
|