#! /usr/bin/perl -w

use strict;
use CGI qw(:standard);
use DBI;

use Time::ParseDate;

$ENV{ORACLE_HOME}="/opt/oracle/product/11.2.0/db_1";
$ENV{ORACLE_BASE}="/opt/oracle/product/11.2.0";
$ENV{ORACLE_SID}="CS339";

# database connections
my $oracle_dbh = DBI->connect("DBI:Oracle:","dtm536","o47a2dad6")
    or die "Couldn't connect to the Oracle database: ". DBI->errstr;
my $mysql_dbh = DBI->connect("DBI:mysql:database=cs339","cs339","cs339")
    or die "Couldn't connect to the Mysql database: ", DBI->errstr;

my $cookiename="PortfolioSession";
my $inputcookiecontent = cookie($cookiename);

my $outputcookiecontent = undef;
my $deletecookie=0;
my $user = undef;
my $password = undef;
my $loginok=0;
my $logincomplain=0;
my $action;

if (param("act")) {
  $action=param("act");
} else {
  $action="login";
}

if ($action eq "login" || param('loginrun')) {
  if (param('loginrun')) {
    ($user,$password)=(param('user'),param('password'));
    if (ValidUser($oracle_dbh,$user,$password)) {
      $outputcookiecontent=join("/",$user,$password);
      $loginok=1;
    } else {
      $logincomplain=1;
      $action="login";
    }
  } else {
    #
    # Just a login screen request. Still, ignore any cookie that's there.
    #
  }
} else {
  if ($inputcookiecontent) {
    ($user,$password) = split(/\//,$inputcookiecontent);
    if (!ValidUser($oracle_dbh,$user,$password)) {
      $action="login";
    } else {
      $outputcookiecontent=$inputcookiecontent;
    }
  } else {
    $action="login";
  }
}

if ($action eq "logout") {
  $deletecookie=1;
}

if ($outputcookiecontent) {
  my $cookie=cookie(-name=>$cookiename,
		    -value=>$outputcookiecontent,
		    -expires=>($deletecookie ? '-1h' : '+1h'));
  print header(-expires=>'now', -cookie=>$cookie);
} else {
  print header(-expires=>'now');
}



###############################
# HTML generation starts here #
###############################

print start_html('Portfolio Manager');
print "<style type=\"text/css\">\n\@import \"portfolio.css\";\n</style>\n";

if ($loginok) {
  print "<h2>You have successfully logged in</h2>";
}

if ($action eq "login") {
  if ($logincomplain) {
    print "Login failed.  Try again.<p>"
  }
  if ($logincomplain or !param('loginrun')) {
    # login if you already have a username
    print start_form(-name=>'Login'),
          h2('Login to Portfolio Manager'),
	  "Name:",textfield(-name=>'user'),	p,
	  "Password:",password_field(-name=>'password'),p,
	  hidden(-name=>'act',default=>['login']),
	  hidden(-name=>'loginrun',default=>['1']),
	  submit,
    end_form;
  }
}

if ($action eq "indivdualportfolio") {
    print "<h2>Por</h2>";
}

if ($action eq "portfolio") {
    print "<h2>portfolio</h2>";
}

if ($action eq "stock") {
    print "stock";
}

if ($action eq "users") {
    # add user form
    print start_form(-name=>'AddUser'),
    h2('Add User'),
    "Name: ", textfield(-name=>'name'), p,
    "Password: ", textfield(-name=>'password'), p,
    hidden(-name=>'adduserrun',-default=>['1']),
    hidden(-name=>'act',-default=>['users']),
    submit,
    end_form;

    # delete user form
    print start_form(-name=>'DeleteUser'),
    h2('Delete User'),
    "Name: ", textfield(-name=>'name'), p,
    hidden(-name=>'deluserrun',-default=>['1']),
    hidden(-name=>'act',-default=>['users']),
    submit,
    end_form;

    # add the user
    if (param('adduserrun')) {
	my $name=param('name');
	my $password=param('password');
	my $error;
	$error=UserAdd($oracle_dbh,$name,$password);
	if ($error != 1) {
	    print "Can't add user because: $error";
	} else {
	    print "Added user $name\n";
	}
    }

    # delete the user
    if (param('deluserrun')) {
	my $name=param('name');
	my $error=UserDel($oracle_dbh,$name);
	if ($error != 1) {
	    print "Can't delete user because: $error";
	} else {
	    print "User $name deleted.";
	}
    }


}

if ($action eq "logout") {
  print "<h2>You have been successfully logged out</h2>";
}


print end_html;

sub UserAdd {
    my ($dbh, $name, $password) = @_;
    my $sth = $dbh->prepare("insert into portfolio_users (username,password) values (?,?)")
	or die "Couldn't prepare statement: ". $dbh->errstr;

    $sth->execute($name, $password) or die "Couldn't execute statement: ". $dbh->errstr;
    $sth->finish;
    $dbh->disconnect;
}

sub UserDel {
    my ($dbh,$name) = @_;
    my $sth = $dbh->prepare("delete from portfolio_users where name=?")
	or die "Couldn't prepare statement: ". $dbh->errstr;

    $sth->execute($name) or die "Couldn't execute statement: ". $dbh->errstr;
    $sth->finish;
    $dbh->disconnect;
}

sub ValidUser {
  my ($dbh,$user,$password)=@_;
  my $sth = $dbh->prepare("select count(*) from portfolio_users where username=? and password=?")
      or die "Couldn't prepare statement: ". $dbh->errstr;
  $sth->execute($user,$password)
      or die "Couldn't execute statement: ". $dbh->errstr;
  my ($user_exists) = $sth->fetchrow_array();
  return $user_exists;
}

sub QueryStock {
  my ($symbol, $from, $to)=@_;
  my @info =  `/usr/bin/perl get_data.pl --close --from="$from" --to="$to" $symbol`;
  open(OUTP, ">$symbol") or die("Cannot open file for writing\n");
  print OUTP @info;
  close OUTP;
  GraphAndPrint($symbol,$symbol);
  unlink("$symbol");
}


sub GraphAndPrint
 {
  my ($name) = @_;
  my ($graphfile)="$name.png";
  print "<b>Graph</b><p><img src =\"" . GnuPlot($name,$graphfile) ."\"><p>\n";
  print "<b>Data</b><p><pre>";
  open (FILE,$name);
  while (<FILE>) {
    print $_;
  }
  close(FILE);
  print "</pre>";
}

sub GnuPlot
 {
  my ($datafile, $outputfile)=@_;
  open(GNUPLOT,"|gnuplot");
  print GNUPLOT "set terminal png\n";
  print GNUPLOT "set output \"$outputfile\"\n";
  print GNUPLOT "plot \"$datafile\" with linespoints\n";
  close(GNUPLOT);
  return $outputfile;
}