+sub fill_table_for_restore
+{
+ my (@jobid) = @_;
+
+ # in "force" mode, we need the FileId to compute media list
+ my $FileId = CGI::param('force')?",FileId":"";
+
+ my $fileid = join(',', grep { /^\d+$/ } CGI::param('fileid'));
+ # can get dirid=("10,11", 10, 11)
+ my @dirid = grep { /^\d+$/ } map { split(/,/) } CGI::param('dirid') ;
+ my $inclause = join(',', @jobid);
+
+ my @union;
+
+ if ($fileid) {
+ push @union,
+ "(SELECT JobId, FileIndex, FilenameId, PathId $FileId
+ FROM File WHERE FileId IN ($fileid))";
+ }
+
+ foreach my $dirid (@dirid) {
+ my $p = $bvfs->get_path($dirid);
+ $p =~ s/([%_\\])/\\$1/g; # Escape % and _ for LIKE search
+ $p = $bvfs->dbh_quote($p);
+ push @union, "
+ (SELECT File.JobId, File.FileIndex, File.FilenameId, File.PathId $FileId
+ FROM Path JOIN File USING (PathId)
+ WHERE Path.Path LIKE " . $bvfs->dbh_strcat($p, "'%'") . "
+ AND File.JobId IN ($inclause))";
+ }
+
+ return unless scalar(@union);
+
+ my $u = join(" UNION ", @union);
+
+ $bvfs->dbh_do("CREATE TEMPORARY TABLE btemp AS $u");
+ # TODO: remove FilenameId et PathId
+
+ # now we have to choose the file with the max(jobid)
+ # for each file of btemp
+ if ($bvfs->dbh_is_mysql()) {
+ $bvfs->dbh_do("CREATE TABLE b2$$ AS (
+SELECT max(JobId) as JobId, FileIndex $FileId
+ FROM btemp
+ GROUP BY PathId, FilenameId
+ HAVING FileIndex > 0
+)");
+ } else { # postgresql have distinct with more than one criteria
+ $bvfs->dbh_do("CREATE TABLE b2$$ AS (
+SELECT JobId, FileIndex $FileId
+FROM (
+ SELECT DISTINCT ON (PathId, FilenameId) JobId, FileIndex $FileId
+ FROM btemp
+ ORDER BY PathId, FilenameId, JobId DESC
+ ) AS T
+ WHERE FileIndex > 0
+)");
+ }
+
+ return "b2$$";
+}
+
+sub get_media_list_with_dir
+{
+ my ($table) = @_;
+ my $q="
+ SELECT DISTINCT VolumeName, Enabled, InChanger
+ FROM $table,
+ ( -- Get all media from this job
+ SELECT MIN(FirstIndex) AS FirstIndex, MAX(LastIndex) AS LastIndex,
+ VolumeName, Enabled, Inchanger
+ FROM JobMedia JOIN Media USING (MediaId)
+ WHERE JobId IN (SELECT DISTINCT JobId FROM $table)
+ GROUP BY VolumeName,Enabled,InChanger
+ ) AS allmedia
+ WHERE $table.FileIndex >= allmedia.FirstIndex
+ AND $table.FileIndex <= allmedia.LastIndex
+";
+ my $lst = $bvfs->dbh_selectall_arrayref($q);
+ return $lst;
+}
+
+sub get_media_list
+{
+ my ($jobid, $fileid) = @_;
+ my $q="
+ SELECT DISTINCT VolumeName, Enabled, InChanger
+ FROM File,
+ ( -- Get all media from this job
+ SELECT MIN(FirstIndex) AS FirstIndex, MAX(LastIndex) AS LastIndex,
+ VolumeName, Enabled, Inchanger
+ FROM JobMedia JOIN Media USING (MediaId)
+ WHERE JobId IN ($jobid)
+ GROUP BY VolumeName,Enabled,InChanger
+ ) AS allmedia
+ WHERE File.FileId IN ($fileid)
+ AND File.FileIndex >= allmedia.FirstIndex
+ AND File.FileIndex <= allmedia.LastIndex
+";
+ my $lst = $bvfs->dbh_selectall_arrayref($q);
+ return $lst;
+}
+
+# get jobid param and apply user filter