#!/usr/bin/perl
# A quick and dirty access to the SIS database.
# The office needed it in a hurry, so...
# Should be safe, though.
# Just lets an HTML pick up a record by surname or id.

use IPC::Open2;
use CGI qw(:all);
use CGI::Carp qw(fatalsToBrowser);

use strict;
use warnings;

# credentials
my $database = 'CSE';
my $user     = 'parke';
my $password = 'HiHi';
my $hostname = 'indigo.cse.yorku.ca';
my $port     = 9999;

my $connect = "connect '"
            . "jdbc:derby://$hostname:$port"
            . "/$database;"
            . "user=$user;password=$password'";

# harvest HTTP parameters sent by POST
# e.g., id=200988511
my $payload = <>;
my @pairs   = split("&", $payload);
my %param;
foreach my $pair (@pairs) {
    my ($key, $val) = split("=", $pair);
    $param{$key} = CGI::unescape($val);
}

# set up the insersion strings for the query
my $sur = "'" . "" . "'";
my $id  = "'" . "" . "'";
$sur = "'" . $param{'surname'} . "'" if (defined($param{'surname'}));
$id  = "'" . $param{'id'}      . "'" if (defined($param{'id'}));

# connect to the database with IJ @ RED
my $IJin;
my $IJout;
open2($IJout, $IJin, "ssh red ij");

# ask our SQL
my $query =<< "QUERY";
$connect;
set schema roumani;
select * from sis
where id = $id or surname = $sur;
QUERY
print $IJin $query;

# close the connection and leave IJ
my $finish =<< "END";
disconnect;
quit;
END
print $IJin $finish;

close($IJin);

# write the HTTP response
print "Content-Type: text/plain\n\n";

# loop through IJ's return, and pass along just the answer table rows
while (<$IJout>) {
    last if (/^[-]+$/);
}
while (<$IJout>) {
    last if (/^\s$/);
    print;
}
close($IJout);