#!/usr/local/bin/perl

###############################################################################
# Program     : CompareBuildsProteins
# Author      : Terry Farrah <tfarrah@systemsbiology.org>
# $Id: GetPeptides 5436 2007-07-03 23:56:02Z sloeveni $
#
# Description : This program that allows users to
#               get proteins from the PeptideAtlas based on various criteria.
#
# SBEAMS is Copyright (C) 2000-2021 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 $log);
use SBEAMS::Connection::Settings;
use SBEAMS::Connection::Tables;
use SBEAMS::Connection::TabMenu;

use SBEAMS::PeptideAtlas;
use SBEAMS::PeptideAtlas::Settings;
use SBEAMS::PeptideAtlas::Tables;
use SBEAMS::BioLink::Tables;

use Venn::Chart;


use lib "$FindBin::Bin";
use DBI;

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


###############################################################################
# Set program name and usage banner for command line use
###############################################################################
$PROG_NAME = $FindBin::Script;
$USAGE = <<EOU;
Usage: $PROG_NAME [OPTIONS] key=value key=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=>['PeptideAtlas_user','PeptideAtlas_admin'],
    # connect_read_only=>1,
    allow_anonymous_access=>1,
  ));


  #### Read in the default input parameters
  ### TMF: these are input via the form, which is defined
  ###   in PeptideAtlas_table_column.txt under CompareBuildsProteins.
  my %parameters;

  $parameters{uploaded_file_not_saved} = 1;

  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 {

    my $project_id = $sbeamsMOD->getProjectID(
        atlas_build_id => $parameters{atlas_build_id}
      );

    $sbeamsMOD->display_page_header(project_id => $project_id, use_tabbed_panes => 1,);

    handle_request(ref_parameters=>\%parameters);

    $sbeamsMOD->display_page_footer();

  }

} # 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};
  my @keys = keys %parameters;

# Print statements for debugging SWAP button
#  print "<br>Parameters!<br>\n";
#  for my $key (@keys) {
#    print "$key $parameters{$key}<br>\n";
#  }

  #### Declare some variables
  my ($chrom_num, $genetic_locus, $stend_option, $no_coord_count, $pid, $map, $file_name, $tmp_img_path);

  #### Show current user context information
  #$sbeams->printUserContext();

  #### Get the HTML to display the tabs
  my $tabMenu = $sbeamsMOD->getTabMenu(
    parameters_ref => \%parameters,
    program_name => $PROG_NAME,
  );
  if ($sbeams->output_mode() eq 'html') {
    print $tabMenu->asHTML();
    print"<script>document.title = 'PeptideAtlas: Compare Proteins in 2 Builds';</script>\n";
  }

  #### Get the current atlas_build_ids based on parameters or session
  my @atlas_build_id;
  my @atlas_build_name;
# Print statements for debugging SWAP button
#print "build1 = $parameters{atlas_build_id}<br>\n";
#print "build1 = $atlas_build_id[1]<br>\n";
#print "build2 = $parameters{atlas_build_id_2}<br>\n";
#print "build2 = $atlas_build_id[2]<br>\n";

  $atlas_build_id[1] = $sbeamsMOD->getCurrentAtlasBuildID(
      parameters_ref => \%parameters,
  );
  $atlas_build_id[2] = $sbeamsMOD->getCurrentAtlasBuildID(
      parameters_ref => \%parameters,
      secondary_build => 1,
  );
  
  if (defined($atlas_build_id[1]) && $atlas_build_id[1] < 0) {
    return;
  }
  if (defined($atlas_build_id[2]) && $atlas_build_id[2] < 0) {
    return;
  }
  $atlas_build_name[1] = $sbeamsMOD->getBuildName(build_id => $atlas_build_id[1]);
  $atlas_build_name[2] = $sbeamsMOD->getBuildName(build_id => $atlas_build_id[2]);



  $parameters{atlas_build_id} = $atlas_build_id[1];
  $parameters{atlas_build_id_2} = $atlas_build_id[2];

  #### Define some generic variables
  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'};

  # for some reason, this returns nothing.
  my $TABLE_NAME = $parameters{'QUERY_NAME'};

  #### Set some specific settings for this program
  my $CATEGORY="Compare Proteins in 2 Builds";
  $TABLE_NAME="AT_CompareBuildsProteins" unless ($TABLE_NAME);
  ($PROGRAM_FILE_NAME) =
    $sbeamsMOD->returnTableInfo($TABLE_NAME,"PROGRAM_FILE_NAME");
  # the above doesn't work for this program
  $PROGRAM_FILE_NAME = "CompareBuildsProteins";
  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);
   #$sbeams->printDebuggingInfo($q);


  #### 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{comparison_type_constraint} = "intersection";
  }
  my $comparison_type = $parameters{comparison_type_constraint};

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

  #### Display button for swapping Build 1/2.
  #### Code stolen from display_form_buttons() and
  #### http://www.htmlquick.com/reference/tags/input.html
  ####  DISABLED because it doesn't work yet.
  if ( 0 && $sbeams->output_mode() eq 'html') {

  my $pad = '&nbsp;' x 6;

  print qq~
      <TR><TD COLSPAN=3>
      $pad <INPUT TYPE="submit" NAME="action" VALUE="Swap Builds">
       </TR>
  ~;
  }


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


  #########################################################################
  #### Process all the constraints

  #### If no atlas_build_ids were selected, stop here
  unless ($parameters{atlas_build_id} && $parameters{atlas_build_id_2}) {
    $sbeams->reportException(
      state => 'ERROR',
      message => 'You must select two Atlas Builds',
    );
    return;
  }


  my @total_obs;
  my @atlas_build_clause;
  my @bsr_atlas_build_clause;

  my %canonicals_build1;
  my @canonicals_arefs;
  my @exhaust_hrefs;
  my @n_canonicals;
  my @exhaust_prot_sqls;
  my @canonical_sqls;
 
  my $module = $sbeams->getSBEAMS_SUBDIR();
	my $DATABASE = $DBPREFIX{$module};
  $DATABASE=~ s/\.$//;
 
  my $database_1 = $DATABASE; 
  my $database_2 = $DATABASE;
 
  my $sql = qq~SELECT NAME FROM MASTER..SYSDATABASES WHERE NAME LIKE 'PEPTIDEATLAS_BUILD%'~;
  my @result = $sbeams->selectOneColumn($sql);
  foreach my $row(@result){
    if ($row =~ /build$atlas_build_id[1]$/){
      $database_1 = "$row.dbo"; 
    }
    if ($row =~ /build$atlas_build_id[2]$/){
      $database_2 = "$row.dbo";
    }
  }
  my %databases =( 1=> $database_1, 2=>$database_2);;
 
  #### Construct constraints, gather data for each build
  for my $i (1, 2) {

    #### Get total PSMs for calculating obs per 100,000
    my $pep_count = $sbeams->selectrow_hashref( <<"    PEP" );
      SELECT COUNT(*) cnt,  SUM(n_observations) obs
      FROM $databases{$i}.PEPTIDE_INSTANCE
      WHERE atlas_build_id = $atlas_build_id[$i];
    PEP
    $total_obs[$i] = sprintf( "%0.1f", $pep_count->{obs});

		#### canonical sql
		my $sql = qq~
			SELECT BS$i.biosequence_name AS BIOSEQUENCE_NAME, 
						 PPL$i.level_name, 
						 BS_REP$i.biosequence_name AS REP_BIOSEQUENCE_NAME,
						 PID$i.n_observations,
             PID$i.percentage_coverage,
             PID$i.n_distinct_peptides,
             PID$i.norm_PSMs_per_100K,
             BS$i.biosequence_desc
			FROM  $databases{$i}.PROTEIN_IDENTIFICATION PID$i
			JOIN $TBAT_PROTEIN_PRESENCE_LEVEL PPL$i ON PPL$i.protein_presence_level_id = PID$i.presence_level_id
			JOIN $TBAT_BIOSEQUENCE BS$i ON BS$i.biosequence_id = PID$i.biosequence_id
			JOIN $TBAT_BIOSEQUENCE BS_REP$i ON BS_REP$i.biosequence_id = PID$i.represented_by_biosequence_id
			WHERE PID$i.atlas_build_id = $atlas_build_id[$i]
			AND PPL$i.level_name = 'canonical'
      AND (BS$i.biosequence_name not like 'DECOY%' AND BS$i.biosequence_name not like 'CONTAM%')
		~;
    $canonical_sqls[$i] = $sql;
    
    #### sql exhaust protids 
    my $exhaust_prot_sql = qq~
			 (SELECT BS$i.BIOSEQUENCE_NAME AS BIOSEQUENCE_NAME, 
               PRL$i.LEVEL_NAME AS LEVEL_NAME,
               BS_REP$i.BIOSEQUENCE_NAME AS REP_BIOSEQUENCE_NAME,
               NULL  AS REF_biosequence_name,
               PID$i.norm_PSMs_per_100K,
               PID$i.N_OBSERVATIONS AS N_OBSERVATIONS,
               PID$i.n_distinct_peptides,
               PID$i.percentage_coverage
			 FROM $databases{$i}.PROTEIN_IDENTIFICATION PID$i
			 JOIN $TBAT_PROTEIN_PRESENCE_LEVEL PRL$i ON (PID$i.PRESENCE_LEVEL_ID = PRL$i.PROTEIN_PRESENCE_LEVEL_ID)
       JOIN $TBAT_BIOSEQUENCE BS$i ON (BS$i.biosequence_id = PID$i.biosequence_id)
       JOIN $TBAT_BIOSEQUENCE BS_REP$i ON (BS_REP$i.biosequence_id = PID$i.represented_by_biosequence_id)
			 WHERE PID$i.atlas_build_id IN ($atlas_build_id[$i])
       AND (BS$i.biosequence_name not like 'DECOY%' AND BS$i.biosequence_name not like 'CONTAM%')
       )
			 UNION
			 (SELECT BS$i.biosequence_name as BIOSEQUENCE_NAME, 
              BRT$i.RELATIONSHIP_NAME AS LEVEL_NAME, 
              BS_REP$i.BIOSEQUENCE_NAME AS REP_BIOSEQUENCE_NAME,
              BS_REF$i.BIOSEQUENCE_NAME AS REF_biosequence_name,
              NULL AS norm_PSMs_per_100K,
              NULL AS N_OBSERVATIONS,
              NULL AS n_distinct_peptides, 
              BR$i.related_biosequence_percentage_coverage as percentage_coverage
			 FROM $databases{$i}.BIOSEQUENCE_RELATIONSHIP BR$i
			 JOIN $TBAT_BIOSEQUENCE_RELATIONSHIP_TYPE BRT$i ON (BR$i.RELATIONSHIP_TYPE_ID = BRT$i.BIOSEQUENCE_RELATIONSHIP_TYPE_ID)
       JOIN $databases{$i}.PROTEIN_IDENTIFICATION PID$i ON (PID$i.biosequence_id = BR$i.reference_biosequence_id)
       JOIN $TBAT_BIOSEQUENCE BS$i ON (BS$i.BIOSEQUENCE_ID = BR$i.RELATED_BIOSEQUENCE_ID)
       JOIN $TBAT_BIOSEQUENCE BS_REP$i ON (BS_REP$i.BIOSEQUENCE_ID =PID$i.REPRESENTED_BY_BIOSEQUENCE_ID)
       JOIN $TBAT_BIOSEQUENCE BS_REF$i ON (BS_REF$i.BIOSEQUENCE_ID = BR$i.reference_biosequence_id) 
			 WHERE PID$i.atlas_build_id IN ($atlas_build_id[$i])
       AND BR$i.atlas_build_id IN ($atlas_build_id[$i])
       AND (BS$i.biosequence_name not like 'DECOY%' AND BS$i.biosequence_name not like 'CONTAM%')
       )
       UNION
       (
       SELECT BS$i.biosequence_name as BIOSEQUENCE_NAME,
              BRT$i.RELATIONSHIP_NAME AS LEVEL_NAME,
              BS_REP$i.BIOSEQUENCE_NAME AS REP_BIOSEQUENCE_NAME,
              BS_REF$i.BIOSEQUENCE_NAME AS REF_biosequence_name,
              NULL AS norm_PSMs_per_100K,
              NULL AS N_OBSERVATIONS,
              NULL AS n_distinct_peptides,
              BR2$i.related_biosequence_percentage_coverage as percentage_coverage
       FROM $databases{$i}.BIOSEQUENCE_RELATIONSHIP BR$i
       JOIN $databases{$i}.BIOSEQUENCE_RELATIONSHIP BR2$i 
            ON (BR$i.reference_biosequence_id = BR2$i.related_biosequence_id
            and BR2$i.atlas_build_id = $atlas_build_id[$i]
            and BR$i.atlas_build_id  = $atlas_build_id[$i]) 
       JOIN $TBAT_BIOSEQUENCE_RELATIONSHIP_TYPE BRT$i ON (BR$i.RELATIONSHIP_TYPE_ID = BRT$i.BIOSEQUENCE_RELATIONSHIP_TYPE_ID)
       JOIN $databases{$i}.PROTEIN_IDENTIFICATION PID$i ON (PID$i.biosequence_id = BR2$i.reference_biosequence_id)
       JOIN $TBAT_BIOSEQUENCE BS$i ON (BS$i.BIOSEQUENCE_ID = BR$i.RELATED_BIOSEQUENCE_ID)
       JOIN $TBAT_BIOSEQUENCE BS_REP$i ON (BS_REP$i.BIOSEQUENCE_ID =PID$i.REPRESENTED_BY_BIOSEQUENCE_ID)
       JOIN $TBAT_BIOSEQUENCE BS_REF$i ON (BS_REF$i.BIOSEQUENCE_ID = BR$i.reference_biosequence_id)
       WHERE PID$i.atlas_build_id IN ($atlas_build_id[$i])
       AND (BS$i.biosequence_name not like 'DECOY%' AND BS$i.biosequence_name not like 'CONTAM%')
       )
		~;
    $exhaust_prot_sqls[$i] = $exhaust_prot_sql;
		my @rows = $sbeams->selectSeveralColumns($exhaust_prot_sql) or
							 die "\nERROR: Unable to get protein identifications with $sql\n\n";
    my @canonicals;
    for my $row (@rows) {
      my ($protid,$level,$rep_protid, $ref_prot_id, $nobs)=@$row;
      $exhaust_hrefs[$i]->{$protid} =1;
      push @canonicals, $protid if ($level eq 'canonical');
    }
    $n_canonicals[$i] = scalar @canonicals;
    $canonicals_arefs[$i] = \@canonicals;
    if (! scalar @canonicals ) {
			 print "\nSorry, Build $i does not have its protein ".
                "identifications classified as canonical, ".
                "possibly distinguished, and subsumed. It may be an ".
                "older build or a specialty build.\n\n";
         exit;
    }
  }
	if ( ! ($total_obs[1] && $total_obs[2]) ) {
		print "Can't find total observations for both atlases. ".
					"Found $total_obs[1] and $total_obs[2].<br>\n";
	}



  ## get organism_id to pass on to url_cols
  my $sql = qq~
      SELECT BSS.organism_id
      FROM $TBAT_BIOSEQUENCE_SET BSS
      JOIN $TBAT_ATLAS_BUILD AB
			ON (AB.biosequence_set_id = BSS.biosequence_set_id)
      WHERE AB.atlas_build_id ='$atlas_build_id[1]'
      ~;
 
   my ($organism_id) = $sbeams->selectOneColumn($sql) or
       die "\nERROR: Unable to find the organism_id with $sql\n\n";

  $parameters{organism_id} = $organism_id;

  #### Build ROWCOUNT constraint
  $parameters{row_limit} = 5000
    unless ($parameters{row_limit} > 0 && $parameters{row_limit}<=1000000);
  my $limit_clause = "TOP $parameters{row_limit}";
  #### Disable row limits
  $limit_clause = "";

  ### Construct SQL for each item to be SELECTed
  my $biosequence_name_sql = "bs.biosequence_name";
  my $enrichment_sql =
    "(CASE WHEN build2.n_observations > 0 ".
    "THEN (build1.n_observations/$total_obs[1])/(build2.n_observations/$total_obs[2]) ".
    "ELSE (build1.n_observations/$total_obs[1])/(0.3/$total_obs[2]) END)";

  my @per_100K_sql;
  for my $i (1, 2) {
    $per_100K_sql[$i] = "case when (build$i.norm_PSMs_per_100K is null) then STR(build$i.norm_PSMs_per_100K,7,3) else '' end";
  }

  #### Set flag to display SQL statement if user selected
  if ( $parameters{display_options} =~ /ShowSQL/ ) {
    $show_sql = 1;
  }

  ### Gather info for columns to be displayed:
  ### column name in SQL results, value, and column header to print.
  my @column_array = (
      ["biosequence_name","isnull(build1.biosequence_name, build2.biosequence_name)","Biosequence Name"],
      ["enrichment",$enrichment_sql,"Enrichment"],
      ["presence_level_1","build1.level_name","Presence Level Build 1"],
      ["n_observations_1","build1.n_observations","N Obs Build 1"],
      ["pct_coverage_1","build1.percentage_coverage", "Percent Coverage 1"],
      ["n_distinct_peptides_1","build1.n_distinct_peptides", "# Distinct Peptides 1"],
      ["obs_per_100K_1","$per_100K_sql[1]","Norm Obs per 100K Build 1"],
      ["represented_by_biosequence_1","build1.rep_biosequence_name", "Build 1 Protein Group"],
      ["indist_from","build2.ref_biosequence_name","Indistinguishable from in Build 2"],
      ["presence_level_2","build2.level_name","Presence Level Build 2"],
      ["n_observations_2","build2.n_observations","N Obs Build 2"],
      ["n_distinct_peptides_2","build2.n_distinct_peptides", "# Distinct Peptides 2"],
      ["pct_coverage_2","build2.percentage_coverage", "Percent Coverage 2"],
      ["obs_per_100K_2","$per_100K_sql[2]","Norm Obs per 100K Build 2"],
      ["represented_by_biosequence_2","build2.rep_biosequence_name","Build 2 Protein Group"],
      ["biosequence_description","cast(build1.biosequence_desc as varchar(255))","Protein Description"],
  );

  ### Combine the above info into SQL for the SELECT part of the query
  ### with the side-effect of filling in @column_titles and %colnameidx.
  ### We actually use $columns_clause only for some of the subqueries;
  ### the others have variations on it.
  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
    );


  my $select = qq~ 
    SELECT
		$limit_clause
		$columns_clause
  ~;

  my $sql ='';
  if ($comparison_type eq "intersection") {
   #### Get the intersection (build 1 canonicals also in build 2 exhaustive set) 
    $sql .= qq~
        $select 
        FROM ($canonical_sqls[1] )build1
        JOIN ($exhaust_prot_sqls[2]) build2 on (build1.biosequence_name = build2.biosequence_name)
        WHERE 1=1
     ~; 
  } elsif ($comparison_type eq "build_1_not_build_2") {
    #### Get the difference (build 1 canonicals NOT in build 2 exhaustive set). 
    $sql .= qq~
			$select
			FROM ($canonical_sqls[1] )build1
		  LEFT JOIN ($exhaust_prot_sqls[2]) build2 ON ( build1.biosequence_name = build2.biosequence_name)
			WHERE 1=1 
      AND build2.biosequence_name is null 
    ~;
  } elsif ($comparison_type eq "enrich_build_1") {
    $sql .= qq~
      $select
      FROM ($canonical_sqls[1] )build1
      LEFT JOIN ($exhaust_prot_sqls[2]) build2 ON ( build1.biosequence_name = build2.biosequence_name)
      WHERE 1=1
      AND build1.biosequence_name is not null
    ~;
  } elsif ($comparison_type eq "union") {
    $sql .= qq~
      $select
      FROM ($canonical_sqls[1] )build1
      FULL JOIN ($exhaust_prot_sqls[2]) build2 ON ( build1.biosequence_name = build2.biosequence_name)
      WHERE 1=1
      AND (build1.biosequence_name is not null OR (build1.biosequence_name is  null and build2.level_name='canonical'))
    ~;
  }

  #### 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 'sort_order' && $key ne 'display_options') {
      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 = (
          'Biosequence Name' => "$CGI_BASE_DIR/PeptideAtlas/GetProtein?protein_name=\%$colnameidx{biosequence_name}V&apply_action=$pass_action",
          'Biosequence Name_ATAG' => 'TARGET="Win1" ONMOUSEOVER="window.status=\'Show more information about this protein\'; return true"',
          'Build 1 Protein Group' => "$CGI_BASE_DIR/PeptideAtlas/GetProteins?protein_group_representative_constraint=\%$colnameidx{protein_group_representative}V&apply_action=$pass_action",
          'Build 1 Protein Group_ATAG' => 'TARGET="Win1" ONMOUSEOVER="window.status=\'Display all biosequences in this group\'; return true"',
          'Indistinguishable from in Build 2' => "$CGI_BASE_DIR/PeptideAtlas/GetProtein?protein_name=\%$colnameidx{indist_from}V&apply_action=$pass_action&atlas_build_id=$atlas_build_id[2]",
          'Indistinguishable from in Build 2_ATAG' => 'TARGET="Win1" ONMOUSEOVER="window.status=\'Show more information about this protein\'; return true"',
          'Presence Level Build 2' => "$CGI_BASE_DIR/PeptideAtlas/GetProtein?protein_name=\%$colnameidx{biosequence_name}V&apply_action=$pass_action&atlas_build_id=$atlas_build_id[2]",
          'Build 2 Protein Group' => "$CGI_BASE_DIR/PeptideAtlas/GetProteins?protein_group_representative_constraint=\%$colnameidx{protein_group_representative}V&apply_action=$pass_action&atlas_build_id=$atlas_build_id[2]",
          'Build 2 Protein Group_ATAG' => 'TARGET="Win1" ONMOUSEOVER="window.status=\'Display all biosequences in this group\'; return true"',
  );        


  #### Define columns that should be hidden in the output table
  # biosequence_accessor is prefix to compose URL
  # biosequence_accessor_suffix is suffix for same


  #########################################################################
  #### If "Swap Builds" was selected, do so.
  if ($apply_action =~ /swap builds/i ) {
### debugging statements for SWAP button
#print "build1 = $parameters{atlas_build_id}<br>\n";
#print "build1 = $atlas_build_id[1]<br>\n";
#print "build2 = $parameters{atlas_build_id_2}<br>\n";
#print "build2 = $atlas_build_id[2]<br>\n";
#print "-- Swapping --<br>\n";

    my $tmp = $parameters{atlas_build_id};
    $parameters{atlas_build_id} = $parameters{atlas_build_id_2};
    #$atlas_build_id[1] = $parameters{atlas_build_id};
    $parameters{atlas_build_id_2} = $tmp;
    #$atlas_build_id[2] = $parameters{atlas_build_id_2};

    $atlas_build_id[1] = $sbeamsMOD->getCurrentAtlasBuildID(
	parameters_ref => \%parameters,
    );
    $atlas_build_id[2] = $sbeamsMOD->getCurrentAtlasBuildID(
	parameters_ref => \%parameters,
	secondary_build => 1,
    );
# Print statements for debugging SWAP button
#print "build1 = $parameters{atlas_build_id}<br>\n";
#print "build1 = $atlas_build_id[1]<br>\n";
#print "build2 = $parameters{atlas_build_id_2}<br>\n";
#print "build2 = $atlas_build_id[2]<br>\n";
  }


  #### If QUERY or VIEWRESULTSET was selected, display the data
  #print "action = $apply_action<br>\n";
  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
      if ($show_sql) {
				$sbeams->display_sql(sql=>$sql,use_tabbed_panes => 1,);
      }

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

    my $venn_html='';
    #### Define explanatory text depending on set operation.
    if ($sbeams->output_mode() eq 'html') {
      my $explanatory_text;
      if ($comparison_type eq "intersection") {
	      $explanatory_text = "from Build 1 found in exhaustive set for Build 2";
      } elsif ($comparison_type eq "build_1_not_build_2") {
      	$explanatory_text = "from Build 1 not found in exhaustive set for Build 2";
      } elsif ($comparison_type eq "enrich_build_1") {
	      $explanatory_text = "from Build 1 displayed";
      } elsif ($comparison_type eq "union") {
      	$explanatory_text = "in Build 1 and Build 2 combined.<br>";
      	$explanatory_text .= "Note: Combining of canonicals from two builds employs ".
														 "a heuristic process to eliminate redundant identifiers, and is not ".
														 "a symmetric operation.<br>If you swap builds, the results will ".
														 "be somewhat different.";
      }

      #### Draw graphical depiction of the overlap between the two
      #### atlases.
       my @build2_exhaust = keys %{$exhaust_hrefs[2]};
       my $n_build2_exhaust = scalar @build2_exhaust;
       my $venn1 = $sbeamsMOD->draw_venn(data => [($canonicals_arefs[1], $canonicals_arefs[2])],
																				 data_label=> [("Build 1 Canonical", "Build 2 Canonical")],
																				 title => "Protein Identifiers",
																				 id => 'venn1',
                                         proportion=> 'no');
       
       my $venn2 = $sbeamsMOD->draw_venn(data => [($canonicals_arefs[1], $canonicals_arefs[2], \@build2_exhaust)],
                                         data_label=> [("Build 1 Canonical", "Build 2 Canonical", "Build 2 Exhaustive")],
                                         title => "Protein Identifiers",
                                         id => 'venn2',
                                         proportion=> 'no');

      #### Display statistics about the resultset

      my $n_rows = scalar(@{$resultset_ref->{data_ref}});
  #    print "<p>&nbsp;<p>";
      $venn_html = qq~
        <TABLE>
        <tr>
        <td><div id="venn1"></div></td>
        <td><div id="venn2"></div></td>
        </tr>
        </TABLE>
        <ul style="font-weight: bold">
          <li> Build 1 ($atlas_build_name[1]) canonical ($n_canonicals[1]) </li>
          <li> Build 2 ($atlas_build_name[2]) canonical ($n_canonicals[2]) </li>
          <li> Build 2 exhaustive ($n_build2_exhaust) </li>
        </ul>
        <b>$n_rows canonical protein sequences</b> $explanatory_text.<br>
        $venn1
        $venn2 
      ~;
      my $percent = sprintf("%4.1f", 100 * $n_rows / $n_canonicals[1]);
      if (($comparison_type eq "intersection") || ($comparison_type eq "build_1_not_build_2")) {
       	 $venn_html .= " ($percent% of Build 1 total)";
      }
      $venn_html . "<br>\n";
    }

    #### 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",
      column_titles_ref=>\@column_titles,
      use_tabbed_panes => 1, 
    );


    #### Display table help
    my $obs_help = get_table_help( 'proteins' );
    my $tr = qq~ $venn_html
              <TABLE><TR><TD ALIGN=left>$obs_help</TD></TR></TABLE>
      ~ if ( $sbeams->output_mode() eq 'html' );
 
    #### Display the resultset
    $sbeams->displayResultSet(
      resultset_ref=>$resultset_ref,
      query_parameters_ref=>\%parameters,
      rs_params_ref=>\%rs_params,
      column_help=>$tr, 
      url_cols_ref=>\%url_cols,
      hidden_cols_ref=>\%hidden_cols,
      max_widths=>\%max_widths,
      column_titles_ref=>\@column_titles,
      base_url=>$base_url,
      use_tabbed_panes => 1,
    );


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


    #### Display a plot of data from the resultset
    $sbeams->displayResultSetPlot(
      rs_params_ref=>\%rs_params,
      resultset_ref=>$resultset_ref,
      query_parameters_ref=>\%parameters,
      column_titles_ref=>\@column_titles,
      base_url=>$base_url,
      use_tabbed_panes => 1,
    );
    #### 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',use_tabbed_panes => 1,
      separator_bar=>'YES',display_footer=>'NO');


  #### 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 constrain the query\n";
    }
  }


# Print statements for debugging SWAP button
#print "build1 = $parameters{atlas_build_id}<br>\n";
#print "build1 = $atlas_build_id[1]<br>\n";
#print "build2 = $parameters{atlas_build_id_2}<br>\n";
#print "build2 = $atlas_build_id[2]<br>\n";

} # end handle_request



#######################################################################
sub get_table_help {
  my $name = shift;
  return '' unless $name;
  my @entries;
  my $hidetext;
  my $showtext;
  my $heading;
  my $description;
  if ( $name eq 'proteins' ) {
    @entries = (
      { key => 'Biosequence Name', value => 'Sequence database accession' },
      { key => 'Enrichment', value => 'Enrichment in Build 1 relative to Build 2 = (Obs per 100,000 Build 1) / (Obs per 100,000 Build 2).<br>If not observed in Build 2, it is considered to have 0.3 observations for the purpose of this calculation.' },
      { key => 'Presence Level Build 1', value => 'Always canonical' },
      { key => 'N Obs Build 1', value => 'ProteinProphet adjusted PSM (peptide-spectrum match) count, usually lower than actual PSM count' },
      { key => 'Norm Obs per 100K Build 1', value => 'N Obs, normalized to total observable peps in protein, divided by total PSMs in build, multiplied by 100,000' },
      { key => 'Build 1 Protein Group', value => 'Link to group of proteins sharing peptides with this protein in Build 1' },
      { key => 'Indistinguishable from in Build 2', value => 'If, in build 2, Biosequence Name has the exact same peptide set as another biosequence with a preferred identifier (e.g. Swiss-Prot), that other biosequence is displayed here.' },
      { key => 'Presence Level Build 2', value => 'Canonical, possibly_distinguished, subsumed, or ntt_subsumed' },
      { key => 'N Obs Build 2', value => 'ProteinProphet adjusted PSM (peptide-spectrum match) count, usually lower than actual PSM count' },
      { key => 'Obs per 100,000 Build 2', value => 'N Obs divided by total PSMs in build, multiplied by 100,000' },
      { key => 'Build 2 Protein Group', value => 'Link to group of proteins sharing peptides with this protein in Build 2' },
      { key => 'Protein Description', value => 'Description as taken from sequence database fasta file' },
    );
    
    $showtext = 'show column descriptions';
    $hidetext = 'hide column descriptions';
    $heading = 'Identified Proteins';
    $description= 'Protein sequences mapped to by identified peptides';
  }

  return unless @entries;
  my $help = $sbeamsMOD->get_table_help_section( name => $name,
  description => $description,
  heading => $heading,
  entries => \@entries,
  showtext => $showtext,
  hidetext => $hidetext  );
  return $help;
    
} # end get_table_help