#!c:/perl/bin/perl.exe -w

    use strict;
    use DBI;
    use CGI;
    #use CGI::Carp qw( fatalsToBrowser warningsToBrowser );
  
 
    #open connection to Access database
    my $dbh = DBI->connect("dbi:ODBC:directory",
    { 'AutoCommit' =>1, 'RaiseError' =>1}) ||
           
    die "Error connecting: '$DBI::errstr'";

    # setup CGI handle
    my $cgi = new CGI;

    # start HTML

    print $cgi->header . $cgi->start_html('Drive');
      
    my $hash ={ Initiative=>'DriveInitiative', 
            ResOrientation => 'DriveResOrient',
            Creativity => 'DriveCreativity',
            ChangeOrientation => 'DriveChangeOrient',
            DecisionMaking => 'DriveDecisionMake',
            SelectLevel => 'ResLevel',
            ManagesPeople => 'ResManage'};

    my @clauses = ();
       foreach my $checkbox (keys %$hash) {
       push @clauses, $hash->{$checkbox} if validate($cgi->param($checkbox));
       }

      
    my $where_clause = join(' and ',map($_.= ' = "on"', @clauses));

    my $sql = "SELECT ResType, ResLevel, ResManage, Details, Length, Source, Cost, FurtherDetails 
          FROM ResourceSettings, Resources
          WHERE ((ResourceSettings.ResID = Resources.ResID) and
                 ($where_clause))
          if     ($where_clause)
          ORDER BY order by ResType, ResLevel, ResManage, 
                Details, Length, Source, Cost, FurtherDetails";

    print $cgi->p($sql);

    my $sth = $dbh->prepare($sql); 
       $sth->execute || 
    die "Could not execute SQL statement ... " . $dbh->errstr;

        
        my $rows = $dbh->selectall_arrayref($sql) || die $dbh->errstr;
        if (@$rows) {
          print "<table border=1 cellspacing=0 cellpadding=3><tr>" .
                "<th>Type</th><th>Level</th><th>Manage</th><th>Details</th><th>Length</th><th>Source</th><th>Cost</th><th>Further Details</th></tr>";
          foreach my $row (@$rows) {
            print "<tr><td>" . join ("</td><td>", @$row) . "</td></tr>\n";
          }
          print "</table>\n";
          }
          else {
          print "<p><i>No matches found</i></p>\n";
         }
        
 
      
    # disconnect from database
    $dbh->disconnect();
    exit(0);

    # validate user input 
      sub validate {
        my $string = shift;
        # get rid of all non-letter, non-numerical characters and percents
        $string =~ s/[^A-Za-z0-9%]//g;
        return $string;
      }