lib/BackupPC/SearchLib.pm

#!/usr/bin/perl
package BackupPC::SearchLib;

use strict;
use BackupPC::CGI::Lib qw(:all);
use BackupPC::Attrib qw(:all);
use Data::Dumper;
use DBI;

sub getUnits() {
    my @ret = ();
    my $tmp;
    my $dbh = DBI->connect( "dbi:SQLite:dbname=${TopDir}/$Conf{SearchDB}",
        "", "", { 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;
}

sub getWhere($) {
        my ($param)    = @_;
        my @conditions;

        sub mk_iso_date($$) {
                my ($name,$suffix) = @_;

                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);

        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'});

        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) = @_;
      
      
      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,
                        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 "")
        {
            $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 = ();
      my $tmp;
      
      while ($tmp = $st->fetchrow_hashref())
        {
            push(@ret, { 
                           'hname'       => $tmp->{'hname'}, 
                           'sname'       => $tmp->{'sname'},
                           'sharename'   => $tmp->{'sharename'},
                           'backupno'    => $tmp->{'backupNum'},
                           'fname'       => $tmp->{'filename'},
                           'fpath'       => $tmp->{'filepath'},
                           'networkpath' => $tmp->{'networkPath'},
                           'date'        => $tmp->{'date'},
                           'type'        => $tmp->{'filetype'},
                           'size'        => $tmp->{'size'},
                           'id'          => $tmp->{'fid'},
                           'dvd'         => $tmp->{'dvd'}
                       }
            );
                                
        }
      
      $st->finish();
      $dbh->disconnect();
      return @ret;
  }

sub getBackupsNotBurned()
  {
      my $dbh = DBI->connect( "dbi:SQLite:dbname=${TopDir}/$Conf{SearchDB}",
        "", "", { RaiseError => 1, AutoCommit => 1 } );      
      my $sql = q{ 
          SELECT
            hosts.ID         AS hostID,
            hosts.name       AS host,
            backups.num      AS backupno,
            backups.type     AS type,
            backups.date     AS date
          FROM backups, shares, files, hosts
          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
      };
      my $st = $dbh -> prepare( $sql );
      my @ret = ();
      $st -> execute();

      while ( my $tmp = $st -> fetchrow_hashref() )
        {           
            push(@ret, { 
                         'host'     => $tmp->{'host'},
                         'hostid'   => $tmp->{'hostID'},
                         'backupno' => $tmp->{'backupno'},
                         'type'     => $tmp->{'type'},
                         'date'     => $tmp->{'date'}
                       }
            );
        }
      
      return @ret;      
  }

sub displayBackupsGrid()
  {
      my $retHTML = "";
      my $addForm = 1;
      
      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">};
        }
      $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\">Backup no</td> <td class=\"tableheader\">Type</td> <td class=\"tableheader\">date</td></tr>";
      my @backups = getBackupsNotBurned();
      my $backup;

      if ($addForm)
        {
            $retHTML .= "<tr>";
            $retHTML .= "<td colspan=7 style=\"tableheader\">";
            $retHTML .= "<input type=\"submit\" value=\"Burn selected backups on medium\" name=\"submitBurner\">";
            $retHTML .= "</td>";
            $retHTML .= "</tr>";
            
        }
      foreach $backup(@backups)
        {
            my $ftype = "";
            
            $retHTML .= "<tr>";
            if ($addForm)
              {
                  $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>";
            $retHTML .= "<td class=\"fviewborder\">" . $backup->{'backupno'} . "</td>";
            $retHTML .= "<td class=\"fviewborder\">" . $backup->{'type'} . "</td>";
            $retHTML .= "<td class=\"fviewborder\">" . $backup->{'date'} . "<td>";
            $retHTML .= "</tr>";
        }
      $retHTML .= "</table>";
      if ($addForm)
       {
           $retHTML .= "</form>";
       }
      
      return $retHTML;
  
  
  }      

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 .= "</form>" if ($addForm);
      
        return $retHTML;
}

1;
1	dpavlin	4	#!/usr/bin/perl
2			package BackupPC::SearchLib;
3
4			use strict;
5			use BackupPC::CGI::Lib qw(:all);
6			use BackupPC::Attrib qw(:all);
7			use Data::Dumper;
8			use DBI;
9
10			sub getUnits() {
11			my @ret = ();
12			my $tmp;
13	dpavlin	6	my $dbh = DBI->connect( "dbi:SQLite:dbname=${TopDir}/$Conf{SearchDB}",
14	dpavlin	4	"", "", { RaiseError => 1, AutoCommit => 1 } );
15			my $st =
16			$dbh->prepare(
17			" SELECT shares.ID AS ID, shares.share AS name FROM shares;");
18			$st->execute();
19			push (@ret, { 'ID' => '', 'name' => '-'});
20			while ( $tmp = $st->fetchrow_hashref() ) {
21			push( @ret, { 'ID' => $tmp->{'ID'}, 'name' => $tmp->{'name'} } );
22			}
23			$dbh->disconnect();
24			return @ret;
25			}
26
27			sub getWhere($) {
28	dpavlin	19	my ($param) = @_;
29			my @conditions;
30	dpavlin	4
31	dpavlin	19	sub mk_iso_date($$) {
32			my ($name,$suffix) = @_;
33	dpavlin	4
34	dpavlin	19	my $yyyy = $param->{ $name . '_year_' . $suffix} \|\| return;
35			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			}
41	dpavlin	4
42	dpavlin	19	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	dpavlin	4
47	dpavlin	19	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	dpavlin	4
54	dpavlin	19	push( @conditions, ' backups.hostID = ' . $param->{'search_host'} ) if ($param->{'search_host'});
55	dpavlin	4
56	dpavlin	19	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	dpavlin	4	}
64
65	dpavlin	19
66	dpavlin	9	sub getFiles($$)
67	dpavlin	4	{
68	dpavlin	9	my ($where, $offset) = @_;
69	dpavlin	4
70	dpavlin	9
71	dpavlin	6	my $dbh = DBI->connect( "dbi:SQLite:dbname=${TopDir}/$Conf{SearchDB}",
72	dpavlin	4	"", "", { RaiseError => 1, AutoCommit => 1 } );
73			my $sql =
74			q{
75	dpavlin	16	SELECT files.id AS fid,
76			hosts.name AS hname,
77			shares.name AS sname,
78			shares.share AS sharename,
79			files.backupNum AS backupNum,
80			files.name AS filename,
81			files.path AS filepath,
82			shares.share\|\|files.fullpath AS networkPath,
83			date(files.date, 'unixepoch', 'localtime') AS date,
84			files.type AS filetype,
85			files.size AS size,
86			dvds.name AS dvd
87			FROM files
88			INNER JOIN shares ON files.shareID=shares.ID
89			INNER JOIN hosts ON hosts.ID = shares.hostID
90	dpavlin	20	INNER JOIN backups ON backups.num = files.backupNum and backups.hostID = hosts.ID
91	dpavlin	16	LEFT JOIN dvds ON dvds.ID = files.dvdid
92	dpavlin	4	};
93
94			if (defined($where) && $where ne "")
95			{
96			$sql .= " WHERE ". $where;
97			}
98
99	dpavlin	9	$sql .=
100			q{
101			ORDER BY files.id
102			LIMIT 100
103			OFFSET ? * 100 + 1
104			};
105	dpavlin	4
106	dpavlin	9
107
108	dpavlin	4	my $st = $dbh->prepare(
109			$sql
110			);
111	dpavlin	9	if (!defined($offset) && $offset ne "")
112			{
113			$st->bind_param(1, $offset);
114			}
115			else
116			{
117			$st->bind_param(1,0);
118			}
119	dpavlin	4	$st->execute;
120
121			my @ret = ();
122			my $tmp;
123
124			while ($tmp = $st->fetchrow_hashref())
125			{
126			push(@ret, {
127			'hname' => $tmp->{'hname'},
128			'sname' => $tmp->{'sname'},
129			'sharename' => $tmp->{'sharename'},
130			'backupno' => $tmp->{'backupNum'},
131			'fname' => $tmp->{'filename'},
132			'fpath' => $tmp->{'filepath'},
133			'networkpath' => $tmp->{'networkPath'},
134			'date' => $tmp->{'date'},
135			'type' => $tmp->{'filetype'},
136			'size' => $tmp->{'size'},
137			'id' => $tmp->{'fid'},
138			'dvd' => $tmp->{'dvd'}
139			}
140			);
141
142			}
143
144			$st->finish();
145			$dbh->disconnect();
146			return @ret;
147			}
148
149			sub getBackupsNotBurned()
150			{
151	dpavlin	6	my $dbh = DBI->connect( "dbi:SQLite:dbname=${TopDir}/$Conf{SearchDB}",
152	dpavlin	4	"", "", { RaiseError => 1, AutoCommit => 1 } );
153			my $sql = q{
154			SELECT
155			hosts.ID AS hostID,
156			hosts.name AS host,
157			backups.num AS backupno,
158			backups.type AS type,
159			backups.date AS date
160			FROM backups, shares, files, hosts
161			WHERE
162			backups.num = files.backupNum AND
163			shares.ID = files.shareID AND
164			backups.hostID = shares.hostID AND
165			hosts.ID = backups.hostID AND
166			files.dvdid IS NULL
167			GROUP BY
168			backups.hostID, backups.num
169			};
170			my $st = $dbh -> prepare( $sql );
171			my @ret = ();
172			$st -> execute();
173
174			while ( my $tmp = $st -> fetchrow_hashref() )
175			{
176			push(@ret, {
177			'host' => $tmp->{'host'},
178			'hostid' => $tmp->{'hostID'},
179			'backupno' => $tmp->{'backupno'},
180			'type' => $tmp->{'type'},
181			'date' => $tmp->{'date'}
182			}
183			);
184			}
185
186			return @ret;
187			}
188
189			sub displayBackupsGrid()
190			{
191			my $retHTML = "";
192			my $addForm = 1;
193
194			if ($addForm)
195			{
196
197			$retHTML .= <<EOF3;
198			<script language="javascript" type="text/javascript">
199			<!--
200
201			function checkAll(location)
202			{
203			for (var i=0;i<document.forma.elements.length;i++)
204			{
205			var e = document.forma.elements[i];
206			if ((e.checked \|\| !e.checked) && e.name != \'all\') {
207			if (eval("document.forma."+location+".checked")) {
208			e.checked = true;
209			} else {
210			e.checked = false;
211			}
212			}
213			}
214			}
215			//-->
216			</script>
217			EOF3
218			$retHTML .= q{<form name="forma" method="POST" action="}."$MyURL"."?action=burn\"";
219			$retHTML.= q{<input type="hidden" value="burn" name="action">};
220			$retHTML .= q{<input type="hidden" value="results" name="search_results">};
221			}
222			$retHTML .= "<table style=\"fview\">";
223			$retHTML .= "<tr> ";
224			if ($addForm)
225			{
226			$retHTML .= "<td class=\"tableheader\"><input type=\"checkbox\" name=\"allFiles\" onClick=\"checkAll('allFiles');\"></td>";
227			}
228			$retHTML .= "<td class=\"tableheader\">Host</td> <td class=\"tableheader\">Backup no</td> <td class=\"tableheader\">Type</td> <td class=\"tableheader\">date</td></tr>";
229			my @backups = getBackupsNotBurned();
230			my $backup;
231
232			if ($addForm)
233			{
234			$retHTML .= "<tr>";
235			$retHTML .= "<td colspan=7 style=\"tableheader\">";
236			$retHTML .= "<input type=\"submit\" value=\"Burn selected backups on medium\" name=\"submitBurner\">";
237			$retHTML .= "</td>";
238			$retHTML .= "</tr>";
239
240			}
241			foreach $backup(@backups)
242			{
243			my $ftype = "";
244
245			$retHTML .= "<tr>";
246			if ($addForm)
247			{
248			$retHTML .= "<td class=\"fview\"> <input type=\"checkbox\" name=\"fcb"
249			.$backup->{'hostid'}."_".$backup->{'backupno'}
250			."\" value=\"".$backup->{'hostid'}."_".$backup->{'backupno'}."\"> </td>";
251			}
252
253			$retHTML .= "<td class=\"fviewborder\">" . $backup->{'host'} . "</td>";
254			$retHTML .= "<td class=\"fviewborder\">" . $backup->{'backupno'} . "</td>";
255			$retHTML .= "<td class=\"fviewborder\">" . $backup->{'type'} . "</td>";
256			$retHTML .= "<td class=\"fviewborder\">" . $backup->{'date'} . "<td>";
257			$retHTML .= "</tr>";
258			}
259			$retHTML .= "</table>";
260			if ($addForm)
261			{
262			$retHTML .= "</form>";
263			}
264
265			return $retHTML;
266
267
268			}
269
270	dpavlin	17	sub displayGrid($$$$) {
271			my ($where, $addForm, $offset, $hilite) = @_;
272			my $retHTML = "";
273
274			if ($addForm) {
275			$retHTML .= qq{<form name="forma" method="POST" action="}.$MyURL.qq{?action=search">};
276			$retHTML.= qq{<input type="hidden" value="search" name="action">};
277			$retHTML .= qq{<input type="hidden" value="results" name="search_results">};
278	dpavlin	4	}
279	dpavlin	17	$retHTML .= qq{
280			<table style="fview" width="100%">
281			<tr>
282			<td class="tableheader">Host</td>
283	dpavlin	24	<td class="tableheader">Name</td>
284	dpavlin	17	<td class="tableheader">Type</td>
285	dpavlin	24	<td class="tableheader">#</td>
286			<td class="tableheader">Size</td>
287			<td class="tableheader">Date</td>
288	dpavlin	17	<td class="tableheader">Media</td>
289			</tr>
290			};
291			my @files = getFiles($where, $offset);
292			my $file;
293	dpavlin	4
294	dpavlin	17	sub hilite_html($$) {
295			my ($html, $search) = @_;
296			$html =~ s#($search)#<b>$1</b>#gis;
297			return $html;
298	dpavlin	4	}
299	dpavlin	9
300	dpavlin	17	foreach $file (@files) {
301	dpavlin	24	my $typeStr = BackupPC::Attrib::fileType2Text(undef, $file->{'type'});
302	dpavlin	17	$retHTML .= "<tr>";
303	dpavlin	9
304	dpavlin	17	foreach my $v ((
305			$file->{'hname'},
306	dpavlin	24	qq{<img src="$Conf{CgiImageDirURL}/icon-$typeStr.gif" align="center"> } . hilite_html( $file->{'fpath'}, $hilite ),
307			$typeStr,
308	dpavlin	17	$file->{'backupno'},
309			$file->{'size'},
310			$file->{'date'},
311			$file->{'dvd'}
312			)) {
313			$retHTML .= qq{<td class="fviewborder">$v</td>};
314			}
315	dpavlin	9
316	dpavlin	17	$retHTML .= "</tr>";
317			}
318			$retHTML .= "</table>";
319
320			# skip pager
321			return $retHTML;
322
323			$retHTML .= "<INPUT TYPE=\"hidden\" VALUE=\"\" NAME=\"offset\">";
324			for (my $ii = 1; $ii <= $#files; $ii++) {
325			$retHTML .= "<a href = \"#\" onclick=\"document.forma.offset.value=$ii;document.forma.submit();\">$ii</a>";
326			if ($ii < $#files) {
327			$retHTML .= " \| ";
328			}
329			}
330
331			$retHTML .= "</form>" if ($addForm);
332	dpavlin	4
333	dpavlin	17	return $retHTML;
334			}
335	dpavlin	4
336			1;