#!/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 $retSQL     = "";
    my @conditions = ();
    my $cond;

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

    if ( defined ($param->{'search_filename'}) && $param->{'search_filename'} ne "") {
	push (@conditions, " files.name LIKE '".$param->{'search_filename'}."%'");
	}
    
    $retSQL = "";
    foreach $cond(@conditions)
      {
	  if ($retSQL ne "")
	    {
		$retSQL .= " AND ";
	    }
	  $retSQL .= $cond;
      }       

    
    return $retSQL;
}

sub getFiles($)
  {
      my ($where) = @_;
      
      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
	            
	  };

      if (defined($where) && $where ne "")
	{
	    $sql .= " WHERE ". $where;	    
	}

      
      my $st = $dbh->prepare(
	  $sql
	  );     

      $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) = @_;
      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">};
	}
      $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>";
       }
      
      return $retHTML;
  }

1;
