#!/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=<>;
#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=(.*)$/ && !/''/;
  push(@archiv1, "  $1 ") if /archive=(.*)$/ && !/''/;
  $words=$1 if /captions=(.*)$/;
} 
$archiv = join(' ', @archiv);
$archv = join(' ', @archiv1);
$enc  = join(' ', @enc);

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

print ("Content-type: TEXT/HTML\n\n");
print <<"EndOfHTML"; 
<HTML>
<TITLE>Video News Database - Play Clip Form</TITLE>
<BODY>
<H1>List of playable video clips</H1>
<HR>
<FORM ACTION="$WSA/cgi-bin/playclip" METHOD="POST">
<INPUT TYPE="HIDDEN" NAME="keywords" value="$words">
EndOfHTML

#
# 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 CT.TITLE, ST.IPADDRESS, CS.FILENAME, CT.ENCODING, ";
  $query .=        " ST.SNAME, CT.LENGTH, CT.CLIPID, AT.ANAME ";
  $query .= "from   CLIPTABLE CT, ARCHIVETABLE AT, CLIPSERVER CS ";
  $query .= "       , SERVERTABLE ST ";
  $query .= "where ";
  $query .=        "AT.ARCHIVEID = CT.ARCHIVEID and CS.CLIPID = CT.CLIPID ";
  $query .=        $archivequery . $encodingquery;
  $query .=        " ORDER BY CT.TITLE, CT.CLIPID,  AT.ARCHIVEID";
  } else {
  foreach(@capwords) {
    push(@capquery,"CAP.TEXT like '%$_%'");
  } 
  $captionquery = join(' and ', @capquery);
  $captionquery = " and (" . $captionquery . ")";
  
  $query  = "select CT.TITLE, ST.IPADDRESS, CS.FILENAME, CT.ENCODING, ";
  $query .=        " ST.SNAME, CT.LENGTH, CT.CLIPID, CAP.FRAME, AT.ANAME ";
  $query .= "from   CLIPTABLE CT, ARCHIVETABLE AT, CLIPSERVER CS, ";
  $query .=        "CAPTIONS CAP, SERVERTABLE ST ";
  $query .= "where (AT.ARCHIVEID = CT.ARCHIVEID and CS.CLIPID = CT.CLIPID and ";
  $query .=        "CT.CLIPID = CAP.CLIPID) ";
  $query .=        $archivequery . $encodingquery . $captionquery;
  $query .=        " ORDER BY CT.TITLE, CT.CLIPID, CAP.FRAME";
} 

##### oracle login #####
$lda = &ora_login('', $orauser, $orapass);

print "<b>SEARCHED FOR : ";
if($words eq '') { print "All clips"; }
else {
  print "Clips that contain captions like ";
  foreach(@capwords) {
    print "'$_' ";
  } 
}   
print "<BR>";
print "FROM ARCHIVE ID(s): ";
if ($archiv eq '') { $archiv = 'All archives'; }
elsif ($archivc == 1) {
$csr = &ora_open ($lda, " select aname, adesc from archivetable where
                          archiveid=$archv ");
($aname, $adesc) = &ora_fetch($csr);
&ora_close($csr);
$archiv = $archv;
}
else { $archiv = $archv }
print " $archiv <BR>";
print "ENCODING : ";
if ($enc eq '') { $enc = 'All codecs'; }
print " $enc ";

#print " <BR>$query <br>";

$csr=&ora_open($lda,$query);
#
# print "$ora_errstr<BR>";
# 
    if( $archivc == 1 ) {
      print "<HR><B>All clips located in archive: $aname - $adesc </b>";
    }
    if( $encc == 1 ) {
      print "<BR><B>Encoding for all clips: $enc </b><br>";
    }
    print "<HR>";
print <<"EndOfHTML"; 
<FONT Size="4">Select a clip to play<BR>
<FONT SIze="3">
EndOfHTML
print "<SELECT NAME=\"clipid\" SIZE=\"10\">\n";
while(@tab=&ora_fetch($csr))
{
  if($words eq '') {
    ($title, $server, $fname, $enc, $sname, $len, $clipid, $aname) = @tab;
    $frame = 0;
  } else {
    ($title, $server, $fname, $enc, $sname, $len, $clipid, $frame, $aname)
     = @tab;
  }  
  $clipid1 = $clipid2;
  $clipid2 = $clipid;
  if($clipid1 != $clipid2) {
    print ("<OPTION VALUE=\"$clipid\"> $title \n");
  }

}
    print "</table>";

&ora_close($csr);

##### oracle logoff ##### 
&ora_logoff($lda);

print <<"EndOfHTML"; 
</SELECT><P>

<INPUT TYPE="Submit" VALUE="OPEN SELECTED CLIP">
<INPUT TYPE="Reset" VALUE="RESET">
</FORM>
<HR>
<A HREF="$WSA/cgi-bin/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 Sun Aug 18 23:25:08 EDT 1996
</address>
</BODY>
</HTML>
EndOfHTML

