#!/usr/bin/perl

# #################################################################
#
# Project:     CGI-Programming
#
# Filename:    addemp.cgi
#
# Version:     1.0
#
# Autor:       Akadia AG, Martin Zahn 26.12.1999
#
# Purpose:     Shows how to implement a simple form to
#              add employee data, then insert these values
#              to the EMP table.
#
# #################################################################

use strict;
use CGI qw(:standard);                      # load CGI module
use DBI;                                    # load DBI module
use CGI::Carp qw(fatalsToBrowser carpout);  # Error Messages to Browser
use Date::Manip;                            # Date Manipulation Module
$main::TZ = "MET";                          # Set the specific time zone

my ($row,$date,$dbh,$found);

# Print HTML header

print header(), start_html("Enter Employee Form");
print ("<link rel='stylesheet' href='https://www.akadia.com/docroot/css/website.css' type='text/css'>\n");

# If Form already exitsts in browser, then insert values
# in the Database ....

# If User entered employee data in the HTML Form, get and save them ...

if (param()) {

  my $empno = param('empno');
  my $ename = param('ename');
  my $job = param('job');
  my $mgr = param('mgr');
  my $hiredate = param('hiredate');
  my $sal = param('sal');
  my $comm = param('comm');
  my $deptno = param('deptno');

  # Check Input

  if ($hiredate) {
    $date=&UnixDate(&ParseDate($hiredate),"%d.%m.%Y");
    if (!$date) {
      print p("Incorrect Date Format: $hiredate ... try again");
      exit 1;
    }
  }
  if (!$empno) {
    print p("Please enter Employee Identification (empno)");
    exit 1;
  }

  $ENV{ORACLE_HOME} = "/opt/oracle/product/9.2.0";
  $ENV{TNS_ADMIN} = "/home/oracle/config/9.2.0";
  $ENV{NLS_LANG} = "AMERICAN_AMERICA.WE8ISO8859P1";

  my $dbname   = 'RAB1';  # Default Connect String
  my $user     = 'scott';
  my $password = 'tiger';
  my $dbd      = 'Oracle';

  # Set the server specific variables

  if ($ENV{SERVER_NAME} =~ /asus/i ) {
    $dbname = 'ASU1';
  } elsif ( $ENV{SERVER_NAME} =~ /cellar/i ) {
    $dbname = 'CEL2';
  } elsif ( $ENV{SERVER_NAME} =~ /www\.akadia\.com/i ) {
    $dbname = 'RAB1';
  } else {
    print p("Not supported HTTP Server: $ENV{SERVER_NAME}");
    exit;
  }

  # Open Connection to Database

  if (!($dbh = DBI->connect ($dbname,$user,$password,$dbd))) {
    print "Error connecting to $dbd\n";
    print "Error: " . $DBI::errstr . "\n";
    exit;
  }

  # Options for the dbh handle:

  $dbh->{AutoCommit} = 0;  # Turn AutoCommit off for transactions
  $dbh->{PrintError} = 1;  # Turn warnings on (DEFAULT)
  $dbh->{RaiseError} = 1;  # Abort an failures

  # Prepare the INSERT statement

  my $sql = qq {INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
                         VALUES (?,?,?,?,TO_DATE(?,'DD.MM.YYYY'),?,?,?)};
  my $sth = $dbh->prepare($sql);
  if ($DBI::err) {
    print (STDERR "Error: " . $DBI::errstr . "\n");
    exit;
  }

  # INSERT the values into the EMP table

  $sth->execute($empno,$ename,$job,$mgr,$hiredate,$sal,$comm,$deptno);
  $dbh->commit;
  $sth->finish();
  $dbh->disconnect;

  print h1("Employee successfully inserted");
  print ("<pre>\n");
  print ("EmpNo: $empno\n");
  print ("Ename: $ename\n");
  print ("Job: $job\n");
  print ("Mgr: $mgr\n");
  print ("Hiredate: $hiredate\n");
  print ("Sal: $sal\n");
  print ("Comm: $comm\n");
  print ("Deptno: $deptno\n");
  print ("</pre>\n");

  print p("Click <a href=\"oradbi.cgi\">here</a> to fetch employee(s) from database");
}

# ... otherwise create empty HTML Form to enter the values

else {

  print h1("Enter new employee");
  $date=&UnixDate(&ParseDate("today"),"%d.%m.%Y");
  print p("Enter date as: 'DD.MM.YYYY', eg: $date");
  print p("Fields marked with (*) are NOT NULL");

  print hr(), start_form();
  print table({-border=>0},
  Tr({-align=>"LEFT",-valign=>"TOP"},[
       td(['Empno(*):',textfield(-name=>"empno",-size=>30),'(NOT NULL)']),
       td(['Ename:',textfield(-name=>"ename",-size=>30)]),
       td(['Job:',textfield(-name=>"job",-size=>30)]),
       td(['Mgr:',textfield(-name=>"mgr",-size=>30)]),
       td(['Hiredate:',textfield(-name=>"hiredate",-size=>30),'(Enter Date as: DD.MM.YYYY)']),
       td(['Sal:',textfield(-name=>"sal",-size=>30)]),
       td(['Comm:',textfield(-name=>"comm",-size=>30)]),
       td(['Depno:',textfield(-name=>"deptno",-size=>30)])]
    )
  );
  print p(submit("Insert Data"), reset("Clear Form"));
  print end_form(), hr();
}
print ("<P><A HREF=\"../src/addemp.txt\">Show source</a></p>\n");
print end_html();