#!/usr/local/bin/perl

###############################################################################
# Program     : GetInteractions
# Author      : Eric Deutsch <edeutsch@systemsbiology.org>
# $Id$
#
# Description : This program that allows users to
#               browse through the list of possible peptides
#
# SBEAMS is Copyright (C) 2000-2005 Institute for Systems Biology
# This program is governed by the terms of the GNU General Public License (GPL)
# version 2 as published by the Free Software Foundation.  It is provided
# WITHOUT ANY WARRANTY.  See the full description of GPL terms in the
# LICENSE file distributed with this software.
#
###############################################################################


###############################################################################
# Set up all needed modules and objects
###############################################################################
use strict;
use Getopt::Long;
use FindBin;

use lib "$FindBin::Bin/../../lib/perl";
use vars qw ($sbeams $sbeamsMOD $q $current_contact_id $current_username
             $PROG_NAME $USAGE %OPTIONS $QUIET $VERBOSE $DEBUG $DATABASE
             $TABLE_NAME $PROGRAM_FILE_NAME $CATEGORY $DB_TABLE_NAME 
             @MENU_OPTIONS);

use SBEAMS::Connection qw($q);
use SBEAMS::Connection::Settings;
use SBEAMS::Connection::Tables;

use SBEAMS::Interactions;
use SBEAMS::Interactions::Settings;
use SBEAMS::Interactions::Tables;

$sbeams = new SBEAMS::Connection;
$sbeamsMOD = new SBEAMS::Interactions;
$sbeamsMOD->setSBEAMS($sbeams);
$sbeams->setSBEAMS_SUBDIR($SBEAMS_SUBDIR);


#use CGI;
#$q = new CGI;


###############################################################################
# Set program name and usage banner for command like use
###############################################################################
$PROG_NAME = $FindBin::Script;
$USAGE = <<EOU;
Usage: $PROG_NAME [OPTIONS] key=value kay=value ...
Options:
  --verbose n         Set verbosity level.  default is 0
  --quiet             Set flag to print nothing at all except errors
  --debug n           Set debug flag

 e.g.:  $PROG_NAME [OPTIONS] [keyword=value],...

EOU

#### Process options
unless (GetOptions(\%OPTIONS,"verbose:s","quiet","debug:s")) {
  print "$USAGE";
  exit;
}

$VERBOSE = $OPTIONS{"verbose"} || 0;
$QUIET = $OPTIONS{"quiet"} || 0;
$DEBUG = $OPTIONS{"debug"} || 0;
if ($DEBUG) {
  print "Options settings:\n";
  print "  VERBOSE = $VERBOSE\n";
  print "  QUIET = $QUIET\n";
  print "  DEBUG = $DEBUG\n";
}


###############################################################################
# Set Global Variables and execute main()
###############################################################################
main();
exit(0);


###############################################################################
# Main Program:
#
# Call $sbeams->Authenticate() and exit if it fails or continue if it works.
###############################################################################
sub main {

  #### Do the SBEAMS authentication and exit if a username is not returned
  exit unless ($current_username = $sbeams->Authenticate(
    #permitted_work_groups_ref=>['Interactions_user','Interactions_admin',
    #  'Interactions_readonly'],
    #connect_read_only=>1,
    #allow_anonymous_access=>1,
  ));


  #### Read in the default input parameters
  my %parameters;
  my $n_params_found = $sbeams->parse_input_parameters(
    q=>$q,parameters_ref=>\%parameters);
  #$sbeams->printDebuggingInfo($q);


  #### Process generic "state" parameters before we start
  $sbeams->processStandardParameters(parameters_ref=>\%parameters);


  #### Decide what action to take based on information so far
  if ($parameters{action} eq "???") {
    # Some action
  } else {
    $sbeamsMOD->printPageHeader();
    handle_request(ref_parameters=>\%parameters);
    $sbeamsMOD->printPageFooter();
  }


} # end main


###############################################################################
# Handle Request
###############################################################################
sub handle_request {
  my %args = @_;


  #### Process the arguments list
  my $ref_parameters = $args{'ref_parameters'}
    || die "ref_parameters not passed";
  my %parameters = %{$ref_parameters};

 
  #### Define some generic varibles
  my ($i,$element,$key,$value,$line,$result,$sql);


  #### Define some variables for a query and resultset
  my %resultset = ();
  my $resultset_ref = \%resultset;
  my (%url_cols,%hidden_cols,%max_widths,$show_sql);


  #### Read in the standard form values
  my $apply_action  = $parameters{'action'} || $parameters{'apply_action'};
  my $TABLE_NAME = $parameters{'QUERY_NAME'};


  #### Set some specific settings for this program
  my $CATEGORY="Browse Interactions";
  $TABLE_NAME="IN_GetInteractions" unless ($TABLE_NAME);
  ($PROGRAM_FILE_NAME) =
    $sbeamsMOD->returnTableInfo($TABLE_NAME,"PROGRAM_FILE_NAME");
  my $base_url = "$CGI_BASE_DIR/$SBEAMS_SUBDIR/$PROGRAM_FILE_NAME";


  #### Get the columns and input types for this table/query
  my @columns = $sbeamsMOD->returnTableInfo($TABLE_NAME,"ordered_columns");
  my %input_types = 
    $sbeamsMOD->returnTableInfo($TABLE_NAME,"input_types");


  #### Read the input parameters for each column
  my $n_params_found = $sbeams->parse_input_parameters(
    q=>$q,parameters_ref=>\%parameters,
    columns_ref=>\@columns,input_types_ref=>\%input_types);


  #### If the apply action was to recall a previous resultset, do it
  my %rs_params = $sbeams->parseResultSetParams(q=>$q);
  if ($apply_action eq "VIEWRESULTSET") {
    $sbeams->readResultSet(resultset_file=>$rs_params{set_name},
        resultset_ref=>$resultset_ref,query_parameters_ref=>\%parameters);
    $n_params_found = 99;
  }


  #### Set some reasonable defaults if no parameters supplied
  unless ($n_params_found) {
    $parameters{input_form_format} = "minimum_detail";
  }


  #### Apply any parameter adjustment logic
  unless ($parameters{project_id}) {
    $parameters{project_id} = $sbeams->getCurrent_project_id();
  }


  #### Display the user-interaction input form
  $sbeams->display_input_form(
    TABLE_NAME=>$TABLE_NAME,CATEGORY=>$CATEGORY,apply_action=>$apply_action,
    PROGRAM_FILE_NAME=>$PROGRAM_FILE_NAME,
    parameters_ref=>\%parameters,
    input_types_ref=>\%input_types,
  );


  #### Display the form action buttons
  $sbeams->display_form_buttons(TABLE_NAME=>$TABLE_NAME);


  #### Finish the upper part of the page and go begin the full-width
  #### data portion of the page
  $sbeams->display_page_footer(close_tables=>'YES',
    separator_bar=>'YES',display_footer=>'NO');



  #########################################################################
  #### Process all the constraints
  
  #build the interaction detail constraint
  # 1 = intra
  # 2 = inter
  # 3 = all this is the default
   my $detailedClause; 
   if ($parameters{interactionDetailConstraintID} == 1)
   {
     $detailedClause = " AND (I.while_member_of_bioentity1_id is not  NULL  AND I.while_member_of_bioentity2_id is not NULL)";
   }
   elsif ($parameters{interactionDetailConstraintID} == 2)
   {
     $detailedClause = "AND (I.while_member_of_bioentity1_id is  NULL  AND I.while_member_of_bioentity2_id is NULL)";
  }
 
#build the parent constraint 
# 4 = with parent constraint
# 5 = no parent constraint  this is the default
    my $parentString;
    my $parentWhere;
    my @parent1Array;
    my @parent2Array;
    
   my $parentFlag = $parameters{parentConstraintID}; 
    
   
    @parent1Array =  ["bioentity1_parent", "BE1P.bioentity_common_name", "BioEntity 1 Parent"] if $parentFlag  == 4;
    @parent2Array =   ["bioentity2_parent", "BE2P.bioentity_common_name", "BioEntity 2 Parent"] if $parentFlag == 4; 
  
  
   $parentString =" LEFT JOIN $TBIN_BIOENTITY_MEMBER BEM1
           ON ( BE1.bioentity_id = BEM1.child_bioentity_id)
      LEFT JOIN $TBIN_BIOENTITY BE1P ON ( BEM1.parent_bioentity_id = BE1P.bioentity_id )
      LEFT JOIN $TBIN_BIOENTITY_MEMBER BEM2
           ON ( BE2.bioentity_id = BEM2.child_bioentity_id)
      LEFT JOIN $TBIN_BIOENTITY BE2P ON ( BEM2.parent_bioentity_id = BE2P.bioentity_id )" if $parentFlag == 4;
      
           
  $parentWhere =    " AND ( BEM1.record_status != 'D' OR BEM1.record_status IS NULL )
       AND ( BEM2.record_status != 'D' OR BEM2.record_status IS NULL )" if $parentFlag == 4;       
      
      
      
  #### Build PROJECT_ID constraint
  my $project_clause = $sbeams->parseConstraint2SQL(
    constraint_column=>"IG.project_id",
    constraint_type=>"int_list",
    constraint_name=>"Projects",
    constraint_value=>$parameters{project_id} );
  return if ($project_clause eq '-1');


  #### If no valid project_id was selected, stop here
  unless ($parameters{project_id}) {
    if (!$apply_action && $sbeams->output_mode() eq 'html') {
      print "<H1> Begin by selecting one or more projects, selecting ".
	"any additional appropriate constraints, and pressing [QUERY]</H1>\n";
      return;
    }
    $sbeams->reportException(
      state => 'ERROR',
      type => 'INSUFFICIENT CONSTRAINTS',
      message => "You must select at least one valid Project",
    );
    return;
  }


  #### Build ORGANISM_ID constraint
  my $organism_clause = $sbeams->parseConstraint2SQL(
    constraint_column=>"IG.organism_id",
    constraint_type=>"int_list",
    constraint_name=>"Organisms",
    constraint_value=>$parameters{organism_id} );
  return if ($organism_clause eq '-1');


  #### Build INTERACTION_GROUP_ID constraint
  my $interaction_group_clause = $sbeams->parseConstraint2SQL(
    constraint_column=>"IG.interaction_group_id",
    constraint_type=>"int_list",
    constraint_name=>"Interaction Groups",
    constraint_value=>$parameters{interaction_group_id} );
  return if ($interaction_group_clause eq '-1');


  #### Build BIOENTITY_ID constraint
  my $bioentity_clause = $sbeams->parseConstraint2SQL(
    constraint_column=>"bioentity_id",
    constraint_type=>"int_list",
    constraint_name=>"BioEntity List",
    constraint_value=>$parameters{bioentity_id} );
  return if ($bioentity_clause eq '-1');
  #### Translate to look in both sides
  if ($bioentity_clause =~ /(\s+AND )(bioentity_id IN \( [\d,]+ \))$/) {
    $bioentity_clause = $1."( BE1.$2 OR BE2.$2 )";
  }


  #### Build BIOENTITY_NAME constraint
  my $bioentity_common_name_clause = $sbeams->parseConstraint2SQL(
    constraint_column=>"bioentity_common_name",
    constraint_type=>"plain_text",
    constraint_name=>"BioEntity Common Name",
    constraint_value=>$parameters{bioentity_common_name} );
  return if ($bioentity_common_name_clause eq '-1');
  #### Translate to look in both sides
  #if ($bioentity_common_name_clause =~ /(\s+AND )(bioentity_common_name LIKE .+)$/) {
  #  $bioentity_common_name_clause = $1."( BE1.$2 OR BE2.$2 )";
  #}
  #### Need more advanced replacer because of multi-param functionality
  while ($bioentity_common_name_clause =~ /(bioentity_common_name LIKE '.+?')/) {
    my $orig = $1;
    my $new = $orig;
    $new =~ s/common/xxyyzz/;
    $bioentity_common_name_clause =~ s/$orig/( BE1.$new OR BE2.$new )/;
  }
  $bioentity_common_name_clause =~ s/xxyyzz/common/g;


  #### Build INTERACTION_TYPE_ID constraint
  my $interaction_type_clause = $sbeams->parseConstraint2SQL(
    constraint_column=>"IT.interaction_type_id",
    constraint_type=>"int_list",
    constraint_name=>"Interaction Tyoe",
    constraint_value=>$parameters{interaction_type_id} );
  return if ($interaction_type_clause eq '-1');


  #### Build SORT ORDER
  my $order_by_clause = "";
  if ($parameters{sort_order}) {
    if ($parameters{sort_order} =~ /SELECT|TRUNCATE|DROP|DELETE|FROM|GRANT/i) {
      print "<H4>Cannot parse Sort Order!  Check syntax.</H4>\n\n";
      return;
    } else {
      $order_by_clause = " ORDER BY $parameters{sort_order}";
    }
  }


  #### Build ROWCOUNT constraint
  $parameters{row_limit} = 5000
    unless ($parameters{row_limit} > 0 && $parameters{row_limit}<=1000000);
  my $limit_clause = $sbeams->buildLimitClause(
   row_limit=>$parameters{row_limit});


  #### Define some variables needed to build the query
  my @column_array;
  my @additional_columns = ();


  #### Add in some extra columns if the user wants to see them
  if ( $parameters{display_options} =~ /ShowExtraProteinProps/ ||
       $parameters{display_options} =~ /GroupCount/ ) {
    @additional_columns = (
      ["fav_codon_frequency","STR(BPS.fav_codon_frequency,10,3)","Favored Codon Frequency"],
      ["n_transmembrane_regions","BPS.n_transmembrane_regions","Number of Transmembrane Regions"],
      ["protein_length","DATALENGTH(BS.biosequence_seq)","Protein Length"],
      @additional_columns,
    );
  }


  #### Define the desired columns in the query
  #### [friendly name used in url_cols,SQL,displayed column title]

  @column_array = (
    ["interaction_id","I.interaction_id","Interaction ID"],
    ["bioentity1_common_name","BE1.bioentity_common_name","BioEntity 1 Common Name"],
    ["bioentity1_link","'[Edit]'","BE1 Link"],
    ["bioentity1_url","'$SERVER_BASE_DIR$CGI_BASE_DIR/$SBEAMS_SUBDIR/ManageTable.cgi?TABLE_NAME=IN_Bioentity&bioentity_id='+STR(BE1.bioentity_id)","bioentity1_url"],
    ["bioentity1_canonical_name","BE1.bioentity_canonical_name","BioEntity 1 Canonical Name"],
    ["bioentity1_full_name","BE1.bioentity_full_name","BioEntity 1 Full Name"],
    ["bioentity1_state_name","BES1.bioentity_state_name","BioEntity 1 State Name"],
    ["bioentity1_organism", "O1.full_name", "BioEntity 1 Organism"],
    ["bioentity1_type", "BET1.bioentity_type_name", "BioEntity 1 Type"],
    @parent1Array,

    ["regulatory_feature_name1","RF1.regulatory_feature_name","Regulatory Feature 1 Name"],
    ["interaction_type_name","IT.interaction_type_name","Interaction Type Name"],

    ["bioentity2_common_name","BE2.bioentity_common_name","BioEntity 2 Common Name"],
    ["bioentity2_link","'[Edit]'","BE2 Link"],
    ["bioentity2_url","'$SERVER_BASE_DIR$CGI_BASE_DIR/$SBEAMS_SUBDIR/ManageTable.cgi?TABLE_NAME=IN_Bioentity&bioentity_id='+STR(BE2.bioentity_id)","bioentity2_url"],
    ["bioentity2_canonical_name","BE2.bioentity_canonical_name","BioEntity 2 Canonical Name"],
    ["bioentity2_full_name","BE2.bioentity_full_name","BioEntity 2 Full Name"],
    ["bioentity2_state_name","BES2.bioentity_state_name","BioEntity 2 State Name"],
    ["bioentity2_organism", "O2.full_name", "BioEntity 2 Organism"],
    ["bioentity2_type","BET2.bioentity_type_name", "BioEntity 2 Type"],
    @parent2Array,  
    ["regulatory_feature_name2","RF2.regulatory_feature_name","Regulatory Feature 2 Name"],

    ["bioentity_id1","BE1.bioentity_id","BioEntity 1 ID"],
    ["bioentity_id2","BE2.bioentity_id","BioEntity 2 ID"],
    ["Publication_id","PUB.publication_name+ ' (PMID ' + PUB.pubmed_id +')'", "PubMed ID"],
    ["publication_url","PUB.pubmed_id","publication_url"],
    ["interaction_group_id","IG.interaction_group_id","interaction_group_id"],
    ["interaction_group_name","IG.interaction_group_name","interaction_group_name"],
    ["project_id","IG.project_id","project_id"],
    @additional_columns,
  );
#############
## = = "http://www.ncbi.nlm.nih.gov/entrez/utils/pmfetch.fcgi?".
##    "db=PubMed&id=$PubMedID&report=xml&mode=tex
###########		
		
		
		
  #### Set flag to display SQL statement if user selected
  if ( $parameters{display_options} =~ /ShowSQL/ ) {
    $show_sql = 1;
  }


  #### Build the columns part of the SQL statement
  my %colnameidx = ();
  my @column_titles = ();
  my $columns_clause = $sbeams->build_SQL_columns_list(
    column_array_ref=>\@column_array,
    colnameidx_ref=>\%colnameidx,
    column_titles_ref=>\@column_titles
  );


  #### Build the query
  $sql = qq~
    SELECT $limit_clause->{top_clause} $columns_clause
      FROM $TBIN_INTERACTION I
      LEFT JOIN $TBIN_INTERACTION_GROUP IG
           ON ( I.interaction_group_id = IG.interaction_group_id )
      LEFT JOIN $TBIN_INTERACTION_TYPE IT
           ON ( I.interaction_type_id = IT.interaction_type_id )
      LEFT JOIN $TBIN_BIOENTITY BE1 ON ( I.bioentity1_id = BE1.bioentity_id )
      LEFT JOIN $TB_ORGANISM O1 ON ( BE1.organism_id = O1.organism_id )
      LEFT JOIN $TBIN_BIOENTITY BE2 ON ( I.bioentity2_id = BE2.bioentity_id )
      LEFT JOIN $TB_ORGANISM O2 ON ( BE2.organism_id = O2.organism_id )
      LEFT JOIN $TBIN_BIOENTITY_STATE BES1
           ON ( I.bioentity1_state_id = BES1.bioentity_state_id )
      LEFT JOIN $TBIN_BIOENTITY_STATE BES2
           ON ( I.bioentity2_state_id = BES2.bioentity_state_id )
      LEFT JOIN $TBIN_BIOENTITY_TYPE BET1
           ON ( BE1.bioentity_type_id = BET1.bioentity_type_id)
      LEFT JOIN $TBIN_BIOENTITY_TYPE BET2
           ON ( BE2.bioentity_type_id = BET2.bioentity_type_id)
$parentString

           
           
           
           
           

      LEFT JOIN $TBIN_REGULATORY_FEATURE RF1
           ON ( I.regulatory_feature1_id = RF1.regulatory_feature_id )
      LEFT JOIN $TBIN_REGULATORY_FEATURE RF2
           ON ( I.regulatory_feature2_id = RF2.regulatory_feature_id )
			LEFT JOIN $TBIN_PUBLICATION PUB
					 ON (I.publication_id = PUB.publication_id) 
     WHERE 1 = 1

       AND I.record_status != 'D'
       AND ( BE1.record_status != 'D' OR BE1.record_status IS NULL )
       AND ( BE2.record_status != 'D' OR BE2.record_status IS NULL )
       AND ( BES1.record_status != 'D' OR BES1.record_status IS NULL )
       AND ( BES2.record_status != 'D' OR BES2.record_status IS NULL )
       $parentWhere
       AND ( RF1.record_status != 'D' OR RF1.record_status IS NULL )
       AND ( RF2.record_status != 'D' OR RF2.record_status IS NULL )
     
     $project_clause
     $organism_clause
     $interaction_group_clause
     $bioentity_clause
     $bioentity_common_name_clause
     $interaction_type_clause
     $detailedClause
     $limit_clause->{trailing_limit_clause}
    ~;

  #AND $TBIN_INTERACTION.WHILE_MEMBER_OF_BIOENTITY1_ID IS NULL AND 
     #   $TBIN_INTERACTION.WHILE_MEMBER_OF_BIOENTITY2_ID IS NUL
  #### Certain types of actions should be passed to links
  my $pass_action = "QUERY";
  $pass_action = $apply_action if ($apply_action =~ /QUERY/i);


  #### Pass nearly all of the constraints down to a child query
  my @parameters_to_pass;
  my $parameters_list = '';
  while ( ($key,$value) = each %input_types ) {
    if ($key ne 'display_options' &&
        $key ne 'n_occurrences' &&
        $key ne 'biosequence_name_constraint' &&
        $key ne 'xx_constraint'
      ) {
      if ($parameters{$key}) {
        push(@parameters_to_pass,"$key=$parameters{$key}");
      }
    }
  }
  if (@parameters_to_pass) {
    $parameters_list = join('&',@parameters_to_pass);
  }


  #### Define the hypertext links for columns that need them
  %url_cols = (
    	       'Interaction ID' => "$CGI_BASE_DIR/$SBEAMS_SUBDIR/ManageTable.cgi?TABLE_NAME=IN_interaction&interaction_id=\%V",
    	       'Interaction ID_ATAG' => 'TARGET="Win1"',

    	       'BE1 Link' => "$CGI_BASE_DIR/$SBEAMS_SUBDIR/ManageTable.cgi?TABLE_NAME=IN_bioentity&bioentity_id=\%$colnameidx{bioentity_id1}V",
    	       'BE1 Link_ATAG' => 'TARGET="Win1"',
    	       'BE2 Link' => "$CGI_BASE_DIR/$SBEAMS_SUBDIR/ManageTable.cgi?TABLE_NAME=IN_bioentity&bioentity_id=\%$colnameidx{bioentity_id2}V",
    	       'BE2 Link_ATAG' => 'TARGET="Win1"',

    	       'BioEntity 1 Common Name' => "$base_url?bioentity_common_name=\%$colnameidx{bioentity_common_name1}V&apply_action=$pass_action",
    	       'BioEntity 2 Common Name' => "$base_url?bioentity_common_name=\%$colnameidx{bioentity_common_name2}V&apply_action=$pass_action",
						 'PubMed ID' => "http://www.ncbi.nlm.nih.gov/entrez/query.fcgi?cmd=Retrieve&db=PubMed&list_uids=\%$colnameidx{publication_url}V&dopt=Abstract",
  );


  #### Define columns that should be hidden in the output table
  %hidden_cols = ('BioEntity 1 ID' => 1,
		  'BioEntity 2 ID' => 1,
		 );


  unless ( $parameters{display_options} =~ /ShowReg/) {
    %hidden_cols = ( %hidden_cols,
                     'Regulatory Feature 1 Name' => 1,
                     'Regulatory Feature 2 Name' => 1,
                     'bioentity1_url' => 1,
                     'bioentity2_url' => 1,
		     'publication_url'=> 1,
		     'interaction_group_id'=> 1,
		     'interaction_group_name'=> 1,
		     'project_id'=> 1,
    );
  }



  #########################################################################
  #### If QUERY or VIEWRESULTSET was selected, display the data
  if ($apply_action =~ /QUERY/i || $apply_action eq "VIEWRESULTSET") {

    #### If the action contained QUERY, then fetch the results from
    #### the database
    if ($apply_action =~ /QUERY/i) {

      #### Show the SQL that will be or was executed
      $sbeams->display_sql(sql=>$sql) if ($show_sql);

      #### Fetch the results from the database server
      $sbeams->fetchResultSet(
        sql_query=>$sql,
        resultset_ref=>$resultset_ref,
      );

      #### Store the resultset and parameters to disk resultset cache
      $rs_params{set_name} = "SETME";
      $sbeams->writeResultSet(
        resultset_file_ref=>\$rs_params{set_name},
        resultset_ref=>$resultset_ref,
        query_parameters_ref=>\%parameters,
        resultset_params_ref=>\%rs_params,
        query_name=>"$SBEAMS_SUBDIR/$PROGRAM_FILE_NAME",
      );
    }

    #### Display the resultset
    $sbeams->displayResultSet(
      resultset_ref=>$resultset_ref,
      query_parameters_ref=>\%parameters,
      rs_params_ref=>\%rs_params,
      url_cols_ref=>\%url_cols,
      hidden_cols_ref=>\%hidden_cols,
      max_widths=>\%max_widths,
      column_titles_ref=>\@column_titles,
      base_url=>$base_url,
    );


    #### Display the resultset controls
    $sbeams->displayResultSetControls(
      resultset_ref=>$resultset_ref,
      query_parameters_ref=>\%parameters,
      rs_params_ref=>\%rs_params,
      base_url=>$base_url,
    );


  #### If QUERY was not selected, then tell the user to enter some parameters
  } else {
    if ($sbeams->invocation_mode() eq 'http') {
      print "<H4>Select parameters above and press QUERY</H4>\n";
    } else {
      print "You need to supply some parameters to contrain the query\n";
    }
  }


} # end handle_request



###############################################################################
# evalSQL
#
# Callback for translating Perl variables into their values,
# especially the global table variables to table names
###############################################################################
sub evalSQL {
  my $sql = shift;
print "<br>$sql<br>";
  print  eval "\"$sql\"";
  if ($@)
  {
    print "$@";
  }
  return eval "\"$sql\"";
} # end evalSQL