#!/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";




########  Read the parameters from the stdin ######
$input=<>;
#chop $input;

### split the input line ###
@param = split(/\&/, $input );

foreach (@param)
{
    ##### for URL decoding ######
    ### convert plus to space ###
    $_ =~ s/\+/ /g;
    ### convert %XX from hex numbers to alphanumeric ###
    $_ =~ s/%(..)/pack("c", hex($1))/ge;

    $title    = $1 if /title=(.*)/;
    $filename = $1 if /filename=(.*)/;
    $length   = $1 if /length=(.*)/;
    $filsiz   = $1 if /filsiz=(.*)/;
    $encoding = $1 if /encoding=(.*)/;
    $frsfile  = $1 if /frsfile=(.*)/;
    $capfile  = $1 if /capfile=(.*)/;
    $serverid = $1 if /serverid=(.*)/;
    $ann_start = $1 if /ann_start=(.*)/;
    $datatype = $1 if /datatype=(.*)/;
    $annotations = $1 if /annotations=(.*)/;
    $archive  = $1 if /archive=(.*)/;
    $bitrate  = $1 if /bitrate=(.*)/;
    $framerate = $1 if /framerate=(.*)/;
    $access   = $1 if /access=(.*)/;
    $downloadable = $1 if /downloadable=(.*)/;
    $fnailfile = $1 if /fnailfile=(.*)/;
    $fnailurl = $1 if /fnailurl=(.*)/;
    $dateinsert = $1 if /dateinsert=(.*)/;
}


###### start writing the ouput HTMLdoc
print ("Content-type: TEXT/HTML\n\n");
print <<"EndOfHTML"; 
<HTML>
<head>
<script>
document.bgColor=top.menu.document.bgColor
</script>
<TITLE>NPAC Video New DataBase: Clip added</TITLE>
</head>
<BODY > <B>
<H1> Results of the addclip  operation:</H1>
<HR>
<FONT Size="4"> Input Data <BR><BR>
<FONT Size="3"> Title:  $title   <BR>
Clip : $filename  <BR>
Archive ID : $archive <BR>
Date : $dateinsert <BR>
Accessibility : $access <BR>
Downloadable : $downloadable <BR>
FRS file : $frsfile   <BR>
Caption file : $capfile <BR>
Fingernail file : $fnailfile <BR>
Fingernail URL : $fnailurl <BR>
Annotation Start : $ann_start<BR>
Annotaion data type : $datatype<BR>
Annotation text : $annotations<BR>
Server ID : $serverid <BR>
Encoding :  $encoding <BR>
Length : $length frames  <BR>
Framerate : $framerate fps <BR>
File size : $filsiz bytes  <BR>
Bitrate : $bitrate kpbs <BR>
</B> <HR> <B>
<FONT Size="4"> Results: <BR><BR>
<FONT Size="3">
EndOfHTML

#
# File existence tests
#

unless(-r $frsfile) {
  print "Cannot read frs file. Insert aborted.<BR><HR></B></BODY></HTML>";
  die;
}

$nocaption = 0;
unless(-r $capfile) {
  print "Cannot read caption file - assuming default. <BR>";
  $nocaption = 1;
}
$nofnail = 0;
unless(-r $fnailfile ) {
  print "Cannot find fingernail file - assuming default. <BR>";
  $nofnail = 1;
}

#### Get/calculate stream parameters ####

$lstlin = `tail -2 $frsfile|head -1`;
($F,$L) = split(' ', $lstlin, 2);
print "Got file size: ", $F, " frames, ", $L, " bytes<BR>";
$t = $F/$framerate;
print "Calculated duration: ", $t, " sec<BR>";
$b = 8*$L*$framerate/($F*1000);
print "Calculated bitrate: ", int($b), " Kbps<BR>";
if ( $length eq "Auto" ) { $length = $F }
if ( $filsiz eq "Auto" ) { $filsiz = $L }
if ( $bitrate eq "Auto" ) { $bitrate = int($b) }

#
# Oracle Login
#

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

#
# Getting a CLIP ID
# $clipid will have a unique number.
#

$success = 0;
$fail=0;
$try = 1;                # Trial number of getting a key
$maxtry = 10;            # Maximum trial number
while($success != 1 && $fail != 1) {
  # Try to get a keynum
  $keynum = undef;
  $cursor = &ora_open($lda, "select KEYNUM from CLIPKEY where CKEY=1");
  ($keynum) = &ora_fetch($cursor);
  &ora_close($cursor);
  #if got a key
  unless($keynum == undef) {
    # Delete the key
    $ret = &ora_do($lda, "delete from CLIPKEY where CKEY=1");
    # If the key is deleted successfully
    if($ret =~ /1/) {
      $keynum++;   # New server ID number
      $ret = &ora_do($lda, "insert into CLIPKEY values (1,$keynum)");
      # If the key is inserted successfully...
      if($ret =~ /1/) {
        &ora_commit($lda);  # Done
        $success = 1;
      } else {
        &ora_logoff($lda);
        print "DATABASE ERROR : COULD NOT ASSIGN THE CLIP ID\n";
        $fail = 1;
      }
    }
  }
  if($try > $maxtry) {
     &ora_logoff($lda);
     print "TRIAL ERROR : COULD NOT ASSIGN THE CLIP ID\n";
     $fail = 1;
  }
  $try++;
}
$clipid = $keynum;

if($fail == 1) {
  print "COULD NOT ASSIGN THE CLIP ID\n";
  print"</B></BODY></HTML>\n";
  die;
}

print "Clip ID = $keynum<BR>";

#
# find frsfile, open it, and read into a variable. 
#

$frsvariable = "";
open (FRS, $frsfile);
while(<FRS>) {
  $frsvariable .= $_;
}
close(FRS);

#
# Setting buffer size 32K
#

$ora_long = 32760;

#
# Insert into CLIPTABLE 
#

$csr = &ora_open
($lda, "insert into CLIPTABLE values
(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14)");
$ret = &ora_bind
($csr,$clipid,$title,$encoding,$length,$frsvariable,$filsiz,$bitrate,
$framerate,$archive,$access,$downloadable,$fnailurl,$dateinsert,$frsfile);
&ora_close($csr);
print "CLIPTABLE : ";
if($ret>=1) {
  print "$ret row inserted<BR>";
} else {
  print "No rows were inserted. Exiting with Oracle error $ora_errno : $ora_errstr<BR>";

  print"</B></BODY></HTML>\n";
  die;
}

#
# Insert into CLIPSERVER (clipid, serverid, filename)
#

$ret = &ora_do
($lda," insert into CLIPSERVER values ($clipid,$serverid,'$filename') ");
if($ret>=1) {
print "CLIPSERVER : ";
  print " $ret row inserted<BR>";
} else {
  print "No rows are inserted. Exiting with $ora_errstr<BR>";
  print"</B></BODY></HTML>\n";
  die;
}

#
# Load caption table
#
if($nocaption!=1) {
  print "CAPTIONS : <BR>";
  $cap_count=0;
  $succ_count=0;
  open (CAP, $capfile);
  $begin = 0;
  while (<CAP>) {
    $c_line = $_;
    if ($begin != 1 && $c_line =~ /Caption Script follows(.*)/i) { $begin=1; }
    elsif ($begin==1) {
      $c_line =~ /\s*(\d+)\s*(.*)$/;
      $tstamp      = $1;
      $captiontext = $2;
      if($tstamp ne '') {
        $frame = $tstamp;
#       print "$captiontext<BR>";
        $captiontext =~ s/&/&&/g;
        $captiontext =~ s/\'/&1/g;
        $captiontext =~ s/%/&2/g;
        $ret=&ora_do
($lda, " insert into CAPTIONS values ($clipid, $frame, '$captiontext') ");
#       if($ret =~ /1/) { $succ_count++; print "<BR>";}
        if($ret =~ /1/) { $succ_count++; }
        else { print "[ ERROR : $ora_errstr]<BR>"; }
        $cap_count++;
      }
    }
  }
  close(<CAP>);
  print "Total caption items: $cap_count, successfuly inserted: $succ_count<BR>";
}

#
# 4. load annotation table
# take direct annotations and load them into annotations
#
unless($datatype =~ /NO ANNOTATION/) {
  if($datatype =~ /TEXT/) {
    $annotext = &HexString($annotations);
    $ret = &ora_do($lda,"insert into ANNOTATIONS values ($clipid, $ann_start,0,'$datatype','$annotext')");
    print "ANNOTATIONS : "; 
    if($ret>=1){
      print "$ret row inserted<BR>";
    } else {
      print "No rows are inserted.<BR>$ora_errstr<BR><BR>";
    }
  } else {
    print("This version only supports TEXT annotations.<BR>");
  }
}

#
# Logout from Oracle
#

&ora_commit($lda);
&ora_logoff($lda);

print <<"EndOfHTML";
<HR>
<A HREF="$WSA/cgi-bin/addclipform">ADD MORE</A>
</B></BODY></HTML>
EndOfHTML

sub TimeToFrame {
  local($t,$i,$ub,$lb,$done);
  $t = $_[0];
  $done = 0;
  $ub = $table_count-1;
  $lb = 0;
  if($t >= $timestamp[$ub]) { $i = $ub; $done = 1; }
  while($done!=1) {
    $i= int(($ub + $lb) / 2);
    if($ub==$lb) { $done = 1; }
    elsif (($timestamp[$i] <= $t)  && ($t < $timestamp[$i+1])) { $done = 1; }
    elsif ($t < $timestamp[$i]) { $ub = $i; }
    else { $lb = $i; }
  }
  $frame[$i];
}

sub HexString {
  local(@testing,$srcstr,$retstr);
  $srcstr = $_[0];
  @testing = ();
  $retstr="";
  @testing = unpack("c*",$srcstr);
  foreach(@testing) {
    $retstr.= &Hex2($_);
  }
  $retstr;
}

sub Hex2 {
  local($n,$c1,$c2,@hstr);
  $n=$_[0];
  @hstr=("0","1","2","3","4","5","6","7","8","9","A","B","C","D","E","F");
  $c1 = int($n/16);
  $c2 = $n % 16;
  $hstr[$c1].$hstr[$c2];
}
