do 'dbi-lib.pl';



use CGI ':standard';
use DBI;
use strict;
use vars qw($baseURL);

# Declare base URL for return from this page
$baseURL = "../index.html";

my $q = new CGI;
my $user = $q->param('user');
my $passwd = $q->param('passwd');
my $action = $q->param('action');
my $s_off_no = $q->param('s_off_no');


printHead('TDPS - Delete Offence');

# check that user is logon
if (($user eq '') || ($passwd eq '')) {

  goLogon();
  return;
}


print <<EOM;
<table border=0 cellpadding=0 cellspacing=0 width=100%>
<tr>
<td>
EOM

searchform();
queryresult($s_off_no);


print <<EOM;
<hr>
EOM

print <<EOM;
</td>
</tr>
</table>
EOM

printclose();



sub searchform {
	
print <<EOM;

<form action="query.pl" method="post">
<input type="hidden" name="user" value="$user">
<input type="hidden" name="passwd" value="$passwd">
<table border="0" cellpadding="2" cellspacing="2">
<tr>
<td colspan="2" class="pagetitle">Delete Offence Details</td>
</tr>
<tr>
<td>Offence Number</td>
<td><input type="text" name="s_off_no" size="5" maxlength="5">&nbsp;<input type="submit" value="Search"></td>
</tr>
</table>
</form>

EOM


}



sub queryresult {
    my $debug = 1; # 0 to off, 1 to turn it on	 	
    my $results;

    my $dbuser = '111';
    my $dbpasswd = '222';
    
    my $s_off_no = $s_off_no;
    	
    # Query to check if driver exist
    my $query1 = "select veh_reg_no from offence " .
                 "where off_no = $s_off_no";	

    # Database DBI driver name
    my $dbname = "DBI:Oracle:";

    # Create database handle
    my $dbh = DBI->connect($dbname, $dbuser, $dbpasswd);
    if (!$dbh) {
       showSQLerror("Connecting to the database");
       return;
    } else {
      # set AutoCommit  1 = ON, 0 = OFF (1 is default)
      # transaction control is *required* here, hence set OFF
      $dbh->{AutoCommit} = 0;
    }

    if ($debug == 1) {
      print("<br>query1: $query1");    
    }
    $results = run_statement($dbh,$query1,"Y");
    if ($results == 0) {
        printErrors('Invalid Offence No.!');
        return;
    } elsif ($results == -1) {
        $dbh->rollback;
        $dbh->disconnect;
        return;
    } else {
    
      $dbh->commit;
      $dbh->disconnect;
      print("<br>$results");
    }
}


sub run_statement {

    my ($dbh,$sql,$getNo)=@_;
    my $new_offNo;

    # Create a statement handle - prepare the statement
    my $sth = $dbh->prepare($sql);
    if (!$sth) {
        showSQLerror("Preparing SQL statement");
        return (-1);
    }

    # Execute the statement against the database
    $sth->execute;
    if ($DBI::errstr) {
        showSQLerror("Executing SQL Statment");
        $sth->finish;
        return (-1);
    }

    if ($getNo eq "Y") {
        ($new_offNo) = $sth->fetchrow;
    } else {
        $new_offNo = 0;
    }
    $sth->finish;

    return ($new_offNo);
}