#!/oracle/app/oracle/product/7.3.3/bin/oraperl

##### set Oracle's variables #####
$ENV{'ORACLE_HOME'}='/oracle/app/oracle/product/7.3.3';
$ENV{'ORACLE_SID'}='rabbit';
$orauser="vod";
$orapass="vod";

#### set web server address ####
$WSA = "http://clarissa.npac.syr.edu/vod";
$PROJDIR="/project/K136/VOD";




$/="\x0d";

$words;
@words;
@archives;
@encoding;

$inp_line=<>;
# print LOG "vnsquery.pl: ", $inp_line, "\n";
#chop $inp_line;

@params=split(/\&/,$inp_line);

foreach (@params)
{
  if (/encoding=(.*)$/  && !/''/) {
    push(@encoding, "CT.ENCODING='$1'");
    $encc++;
  }
  push(@enc, " $1 ") if /encoding=(.*)$/;
  
  if (/archive=(.*)$/  && !/''/) {
    push(@archives, "AT.ARCHIVEID=$1");
    $archivc++;
  }
  push(@archiv, "  $1 ,") if /archive=(.*)$/ && !/''/;
  $words=$1 if /captions=(.*)$/;
  $scope=$1 if /scope=(.*)$/;
}
$archiv = join(' ', @archiv); 
$enc  = join(' ', @enc);

# translation into the upper case
#$words =~ tr/a-z/A-Z/;

#
# Make archive and encoding 'where' clause
#

$archivequery='';
unless($archiv eq '') {
  $archivequery = join(' or ',@archives);
  $archivequery = " and (" . $archivequery . ")";
}

$encodingquery='';
unless($enc eq '') {
  $encodingquery = join(' or ',@encoding);
  $encodingquery = " and (" . $encodingquery . ")";
}

$words =~ s/&/&&/g;
$words =~ s/\'/&1/g;
$words =~ s/%/&2/g;

$captionquery='';
@capquery=();
@capwords = split(/\+/,$words);

if($words eq '') {
  $captionquery = " and CAP.FRAME=0 ";
  $query  = "select unique CT.TITLE, CT.ENCODING, ";
  $query .=" CT.LENGTH,CT.CLIPID,AT.ANAME,CT.FRAMERATE,CT.FILESIZE ";
  $query .= "from   CLIPTABLE CT, ARCHIVETABLE AT, SERVERTABLE ST ";
  $query .= "where (AT.ARCHIVEID = CT.ARCHIVEID)";
  $query .=        $archivequery . $encodingquery;     
  $query .=	   " ORDER BY CT.TITLE";
} else {
  if ( $scope eq "Captions" || $scope eq "AllFields" ) {
    foreach(@capwords) {
      $temp = $_;
      $temp =~ tr/a-z/A-Z/;
      push(@capquery,"TEXT like '%$_%' or TEXT like '%$temp%'");
    } 
    $captionquery = join(' or ', @capquery);
    $captionquerya = join(' and ', @capquery);
    $captquery = $captionquery;
    $captionquery = " and (CT.CLIPID in (SELECT DISTINCT CLIPID FROM
                      CAPTIONS WHERE " . $captionquery . "))";
  } elsif ( $scope eq "Titles" ) {
    foreach(@capwords) {
      push(@capquery,"UPPER(TITLE) like '%$_%'");
    } 
    $captionquery = join(' or ', @capquery);
    $captionquerya = join(' and ', @capquery);
    $captquery = $captionquery;
    $captionquery = " and (CT.CLIPID in (SELECT DISTINCT CLIPID FROM
                      CLIPTABLE WHERE " . $captionquery . "))";
  } elsif ( $scope eq "Annotations" ) {
    foreach(@capwords) {
      push(@capquery,"UPPER(DATA) like '%$_%'");
    } 
    $captionquery = join(' or ', @capquery);
    $captionquerya = join(' and ', @capquery);
    $captquery = $captionquery;
    $captionquery = " and (CT.CLIPID in (SELECT DISTINCT CLIPID FROM
                      ANNOTATIONS WHERE " . $captionquery . "))";
  }
  $query  = "select distinct CT.TITLE,CT.ENCODING, ";
  $query .= "CT.LENGTH,CT.CLIPID,AT.ANAME,CT.FRAMERATE,CT.FILESIZE ";
  $query .= "from   CLIPTABLE CT, ARCHIVETABLE AT, SERVERTABLE ST ";
  $query .= "where (AT.ARCHIVEID = CT.ARCHIVEID )";
  $query .=        $archivequery . $encodingquery . $captionquery;     
  $query .=	   " ORDER BY CT.TITLE";
}

print "Content-type: text/html\n\n ";

print <<"EndOfHTML";
<HTML>
<head>
<TITLE>NPAC VoD Database search results</TITLE>
</head>
<BODY bgcolor="moccasin">
<HR><h2>NPAC VoD Database Query Results: </H2>
<b>SEARCHED FOR :
EndOfHTML

if($words eq '') { print "All clips"; }
else { 
  print "Clips that contain captions like ";
  foreach(@capwords) {
    print "'$_' ";
  }
}
print "<BR>";
print "FROM ARCHIVE ID: ";
if ($archiv eq '') { $archiv = 'All archives'; }
print " $archiv <BR>";
print "ENCODING : ";
if ($enc eq '') { $enc = 'All types'; }
print " $enc <BR>";
print "SEARCH SCOPE: $scope <BR>\n";

unless($words eq '') {
  print <<"EndOfHTML";
  <h3> You have searched for: @capwords <h3>
  <h4> If this phrase has multiple occurences, you can start video
       playback at any of the associated time codes.<h4><br>
EndOfHTML
}
print <<"EndOfHTML";
<h4>If any clip is available on more than one server, you may select
a video server to deliver the clip.</h4><br>
EndOfHTML

#print "$query <br>\n";


$lda=&ora_login('', $orauser, $orapass);
$csr=&ora_open($lda,$query);

#
#print "$ora_errstr<BR>";
#

$count = 1;
while(@tab=&ora_fetch($csr))
{
  if($words eq '') {
    ($title,$enc,$len,$clipid,$aname,$framerate,$filsiz) = @tab;
    $frame = 0;
  } else {
    ($title,$enc,$len,$clipid,$aname,$framerate,$filsiz) = @tab;
  }
  $clipid1 = $clipid2;
  $clipid2 = $clipid;
  if( $count == 1 ) {
    if( $archivc == 1 ) {
      print "<B>All clips located in $aname archive</b>\n";
    }
    if( $encc == 1 ) {
      print "<BR><B>Encoding for all clips: $enc </b><br>\n";
    } 
    print "<BR>";
    print "<TABLE BORDER=1 WIDTH=97%>\n ";
    print "<TR><TD align=center><b>Title</TD>";
    unless ($archivc == 1) { print "<TD align=center><B>Archive</TD>"; }
    print "<TD align=center><B>Duration</TD>";
    print "<TD align=center><B>Start playback at</TD>";
    unless ($encc == 1) { print "<TD align=center><B>Codec</TD>"; }
    print "<TD align=center><B>Server</TD>";
    print "<TD align=center><B> Client type</TD>";
    print "<TD align=center><B>Action!</TD></TR>\n";
  }

  if($clipid1 != $clipid2) {
    $count++;
    $hitsperclip = 1; 

    print <<"EndOfHTML";
    <FORM ACTION=\"playclip.nvs\" METHOD=\"POST\">
    <INPUT TYPE="HIDDEN" NAME="clipid" value="$clipid">
    <INPUT TYPE="HIDDEN" NAME="len" value="$len">
    <INPUT TYPE="HIDDEN" NAME="filsiz" value="$filsiz">
    <INPUT TYPE="HIDDEN" NAME="enc" value="$enc">
EndOfHTML

    print "<TR><TD>";
    print "<a href=\"showclipdata?$clipid\">\n";
    print "<B>$title</B></a>";
    print "</TD>\n";

    unless ($archivc == 1) {
      print "<TD align=center>$aname</TD>\n";
    }

    $len = int($len/$framerate);
    $minute = int($len/60);
    $second = ($len%60);		
    print "<TD align=center> $minute min $second sec.\n </TD>";

    unless ( $scope eq "Captions" ) {
    print "<TD align=center>the beginning</TD>";
    $frame = 0;
    print "<INPUT TYPE=\"HIDDEN\" NAME=\"frame\" value=\"$frame\">";
    } else {
      $query = " select frame from captions where (clipid=$clipid
                 and ($captquery)) order by frame";
      #print " $query <br>\n";
      $cur = &ora_open ($lda, $query);
      print "<TD align=center><SELECT NAME=\"frame\">\n";
      while ( ($frame) = &ora_fetch($cur) ) {
        $len = int($frame/$framerate);
        $minute = int($len/60);
        $second = ($len%60);
        print " <OPTION VALUE=\"$frame\">$minute min $second sec.\n ";
      } 
      print "</SELECT></TD>\n";
      &ora_close($cur);
    } 

    unless ($encc == 1) {
    print "<TD>$enc</TD>\n ";
    }

    print "<TD align=center><SELECT name=\"serverid\">";
    $cur = &ora_open
    ($lda, " select serverid from clipserver where clipid = $clipid
    order by serverid ");

    while (($sid) = &ora_fetch($cur))
    {
      $cur1  = &ora_open
      ($lda, " select sname from servertable where serverid=$sid " );
      ($sname) = &ora_fetch($cur1);
      print ("<OPTION VALUE=\"$sid\">$sname \n");
      &ora_close($cur1);
    }
    &ora_close($cur);
    print "</SELECT>\n";
    print "</TD>";

    print "<TD>";
    print "<INPUT TYPE=\"RADIO\" NAME=\"cltype\" VALUE='Master' CHECKED>M";
    print "<INPUT TYPE=\"RADIO\" NAME=\"cltype\" VALUE='Slave'>S";
    print "</TD>";

    print "<TD align=center><INPUT TYPE=\"SUBMIT\" VALUE=\"PLAY\"></TD>";
    print "</TR>";
    print "</FORM>\n";
  } 
}

&ora_close($csr);
&ora_logoff($lda);

print "</TABLE>\n";
if ( $count == 1 ) { 
print "<p><font size=\"+1\" color=\"red\">No clips found!<br></font>\n" }

print <<"EndOfHTML";
<BR><A HREF="vnsqueryform"> Specify Another Search</A> 
<HR><ADDRESS>Please report problems to NPAC VoD Group</ADDRESS>
<ADDRESS> Marek Podgorny <A Href="mailto:marek@npac.syr.edu">        
          &lt;marek@npac.syr.edu&gt;</A><BR>                 
          Last Modified on Fri Aug 16 12:42:56 EDT 1996      
</address>              
EndOfHTML
print "</BODY>\n";
print "</HTML>\n";



