#!/usr/local/bin/perl ############################################################################### # Program : GetSearchHits # Author : Eric Deutsch # $Id$ # # Description : This program that allows users to # access search hits in the Proteomics database. # # 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::Proteomics; use SBEAMS::Proteomics::Settings; use SBEAMS::Proteomics::Tables; $sbeams = new SBEAMS::Connection; $sbeamsMOD = new SBEAMS::Proteomics; $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 = <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=>['Proteomics_user','Proteomics_admin', 'Proteomics_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 (defined($parameters{action}) && $parameters{action} eq "???") { # Some action } else { $sbeamsMOD->display_page_header(); 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}; #### 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 Search Hits"; $TABLE_NAME="PR_GetSearchHits" 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, resultset_params_ref=>\%rs_params, ); $n_params_found = 99; } #### Set some reasonable defaults if no parameters supplied unless ($n_params_found) { if ($TABLE_NAME eq "PR_GetSearchHits") { $parameters{input_form_format} = "minimum_detail"; $parameters{probability_constraint} = ">0.7"; $parameters{sort_order} = "experiment_tag,set_tag,S.file_root,SH.cross_corr_rank,SH.hit_index"; } } #### Apply any parameter adjustment logic #### If this is a PR_GetSearch query and sort_order is undefined #### (not just ""), then set to a likely default if (($TABLE_NAME eq "PR_GetSearch") && (!defined($parameters{sort_order})) ) { $parameters{sort_order} = "S.file_root,experiment_tag,set_tag,SH.cross_corr_rank,SH.hit_index"; } #### 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 SEARCH BATCH / EXPERIMENT constraint my $search_batch_clause = $sbeams->parseConstraint2SQL( constraint_column=>"SB.search_batch_id", constraint_type=>"int_list", constraint_name=>"Search Batch List", constraint_value=>$parameters{search_batch_id} ); return if ($search_batch_clause eq '-1'); unless (defined($parameters{search_batch_id}) && $parameters{search_batch_id}) { print "

You must select at least one experiment to browse!

\n\n"; return; } #### Build PROBABILITY constraint my $probability_clause = $sbeams->parseConstraint2SQL( constraint_column=>"SH.probability", constraint_type=>"flexible_float", constraint_name=>"Probability Constraint", constraint_value=>$parameters{probability_constraint} ); return if ($probability_clause eq '-1'); #### For much better performance, add in a hit_index constraint if #### there is a probability contraint. This could break if #### probabilities get assigned to rows with not hit_index = 1 !!!! #### The reason seems to be that the NONCLUSTERED INDEX on #### SH.probability is a lousy index that doesn't get used or #### something, likely because most values are NULL. if ($probability_clause) { $probability_clause .= " AND SH.hit_index = 1"; } #### Build XCORR constraint my $xcorr_clause = ""; my ($icharge,$xcorr); for ($icharge=1;$icharge<4;$icharge++) { $xcorr = $parameters{"xcorr_charge$icharge"}; if ($xcorr) { if ($xcorr =~ /^[\d\.]+$/) { $xcorr_clause .= " OR ( S.assumed_charge = $icharge AND SH.cross_corr = $xcorr )\n"; } elsif ($xcorr =~ /^between\s+[\d\.]+\s+and\s+[\d\.]+$/i) { $xcorr_clause .= " OR ( S.assumed_charge = $icharge AND SH.cross_corr $xcorr )\n"; } elsif ($xcorr =~ /^[><=][=]*\s*[\d\.]+$/) { $xcorr_clause .= " OR ( S.assumed_charge = $icharge AND SH.cross_corr $xcorr )\n"; } else { print "

Cannot parse XCorr Constraint $icharge! Check syntax.

\n\n"; return; } } } if ($xcorr_clause) { $xcorr_clause =~ s/^\s+OR/ AND \(/; $xcorr_clause .= " )\n"; } #### Build DELTA CROSS CORRELATION constraint my $delta_xcorr_clause = $sbeams->parseConstraint2SQL( constraint_column=>"SH.next_dCn", constraint_type=>"flexible_float", constraint_name=>"Delta Cross Correlation Constraint", constraint_value=>$parameters{delta_xcorr} ); return if ($delta_xcorr_clause eq '-1'); #### Build FILE_ROOT constraint my $file_root_clause = $sbeams->parseConstraint2SQL( constraint_column=>"S.file_root", constraint_type=>"plain_text", constraint_name=>"file_root", constraint_value=>$parameters{file_root_constraint} ); return if ($file_root_clause eq '-1'); #### Build BEST_HIT constraint my $best_hit_clause = ""; if ($parameters{best_hit_constraint}) { if ($parameters{best_hit_constraint} =~ /Any/i) { $best_hit_clause = " AND best_hit_flag > ''"; } elsif ($parameters{best_hit_constraint} =~ /User/i) { $best_hit_clause = " AND best_hit_flag = 'U'"; } elsif ($parameters{best_hit_constraint} =~ /Default/i) { $best_hit_clause = " AND best_hit_flag = 'D'"; } } #### Build XCORR_RANK constraint my $xcorr_rank_clause = $sbeams->parseConstraint2SQL( constraint_column=>"SH.cross_corr_rank", constraint_type=>"flexible_int", constraint_name=>"XCorr Rank", constraint_value=>$parameters{xcorr_rank_constraint} ); return if ($xcorr_rank_clause eq '-1'); #### Build CHARGE constraint my $charge_clause = $sbeams->parseConstraint2SQL( constraint_column=>"S.assumed_charge", constraint_type=>"int_list", constraint_name=>"Charge", constraint_value=>$parameters{charge_constraint} ); return if ($charge_clause eq '-1'); #### Build REFERENCE PROTEIN constraint my $reference_clause = $sbeams->parseConstraint2SQL( constraint_column=>"SH.reference", constraint_type=>"plain_text", constraint_name=>"Reference", constraint_value=>$parameters{reference_constraint} ); return if ($reference_clause eq '-1'); #### Build BIOSEQUENCE_ACCESSION constraint my $biosequence_accession_clause = $sbeams->parseConstraint2SQL( constraint_column=>"BS.biosequence_accession", constraint_type=>"plain_text", constraint_name=>"Biosequence Accession", constraint_value=>$parameters{biosequence_accession_constraint} ); return if ($biosequence_accession_clause eq '-1'); #### Build PROTEIN DESCRIPTION constraint my $description_clause = $sbeams->parseConstraint2SQL( constraint_column=>"BS.biosequence_desc", constraint_type=>"plain_text", constraint_name=>"Protein Description", constraint_value=>$parameters{description_constraint} ); return if ($description_clause eq '-1'); #### Build PEPTIDE constraint my $peptide_clause = $sbeams->parseConstraint2SQL( constraint_column=>"SH.peptide", constraint_type=>"plain_text", constraint_name=>"Peptide", constraint_value=>$parameters{peptide_constraint} ); return if ($peptide_clause eq '-1'); #### Build PEPTIDE STRING constraint my $peptide_string_clause = $sbeams->parseConstraint2SQL( constraint_column=>"SH.peptide_string", constraint_type=>"plain_text", constraint_name=>"Peptide String", constraint_value=>$parameters{peptide_string_constraint} ); return if ($peptide_string_clause eq '-1'); #### Build PRECURSOR MASS constraint my $precursor_mass_clause = $sbeams->parseConstraint2SQL( constraint_column=>"(S.sample_mass_plus_H+(S.assumed_charge-1)*1.008)/S.assumed_charge", constraint_type=>"flexible_float", constraint_name=>"Precursor_Mass Constraint", constraint_value=>$parameters{precursor_mass_constraint} ); return if ($precursor_mass_clause eq '-1'); #### Build MASS constraint my $mass_clause = $sbeams->parseConstraint2SQL( constraint_column=>"SH.hit_mass_plus_H", constraint_type=>"flexible_float", constraint_name=>"Mass Constraint", constraint_value=>$parameters{mass_constraint} ); return if ($mass_clause eq '-1'); #### Build START_SCAN constraint my $start_scan_clause = $sbeams->parseConstraint2SQL( constraint_column=>"MSS.start_scan", constraint_type=>"flexible_int", constraint_name=>"Start Scan", constraint_value=>$parameters{start_scan_constraint} ); return if ($start_scan_clause eq '-1'); #### Build PERCENT BUFFER B constraint my $percent_buffer_b_clause = $sbeams->parseConstraint2SQL( constraint_column=>"MSS.calc_buffer_percent", constraint_type=>"flexible_float", constraint_name=>"Percent ACN Constraint", constraint_value=>$parameters{percent_buffer_b_constraint} ); return if ($mass_clause eq '-1'); #### Build ISOELECTRIC_POINT constraint my $isoelectric_point_clause = $sbeams->parseConstraint2SQL( constraint_column=>"SH.isoelectric_point", constraint_type=>"flexible_float", constraint_name=>"Isoelectric Point", constraint_value=>$parameters{isoelectric_point_constraint} ); return if ($isoelectric_point_clause eq '-1'); #### Build ANNOTATION_STATUS and ANNOTATION_LABELS constraint my $annotation_status_clause = ""; my $annotation_label_clause = ""; if ($parameters{annotation_label_id}) { if ($parameters{annotation_status_id} eq 'Annot') { $annotation_label_clause = " AND SHA.annotation_label_id IN ( $parameters{annotation_label_id} )"; } elsif ($parameters{annotation_status_id} eq 'UNAnnot') { $annotation_status_clause = " AND SHA.annotation_label_id IS NULL"; $annotation_label_clause = ""; print "WARNING: Annotation status and Annotation label constraints conflict!
\n"; } else { $annotation_label_clause = " AND ( SHA.annotation_label_id IN ( $parameters{annotation_label_id} ) ". "OR SHA.annotation_label_id IS NULL )"; } } else { if ($parameters{annotation_status_id} eq 'Annot') { $annotation_status_clause = " AND SHA.annotation_label_id IS NOT NULL"; } elsif ($parameters{annotation_status_id} eq 'UNAnnot') { $annotation_status_clause = " AND SHA.annotation_label_id IS NULL"; } else { #### Nothing } } #### Build QUANTITATION constraint my $quantitation_clause = ""; if ($parameters{quantitation_constraint}) { if ($parameters{quantitation_constraint} =~ /^[\d\.]+$/) { $quantitation_clause = " AND d0_intensity/ISNULL(NULLIF(d8_intensity,0),0.01) = $parameters{quantitation_constraint}"; } elsif ($parameters{quantitation_constraint} =~ /^between\s+[\d\.]+\s+and\s+[\d\.]+$/i) { $quantitation_clause = " AND d0_intensity/ISNULL(NULLIF(d8_intensity,0),0.01) $parameters{quantitation_constraint}"; } elsif ($parameters{quantitation_constraint} =~ /^[><=][=]*\s*[\d\.]+$/) { $quantitation_clause = " AND d0_intensity/ISNULL(NULLIF(d8_intensity,0),0.01) $parameters{quantitation_constraint}"; } else { print "

Cannot parse Quantitation Constraint! Check syntax.

\n\n"; return; } } #### Build QUANTITATION FORMAT my $quant_format_clause = ""; $parameters{quantitation_format} = "d81" unless $parameters{quantitation_format}; if ($parameters{quantitation_format}) { if ($parameters{quantitation_format} eq "raw") { $quant_format_clause = "STR(d0_intensity,5,2) + ':' + STR(d8_intensity,5,2) + ". "(CASE WHEN QUAN.date_modified != QUAN.date_created THEN ' *' ELSE '' END)"; } elsif ($parameters{quantitation_format} eq "High1") { $quant_format_clause = "(CASE WHEN d0_intensity > d8_intensity ". "THEN '1 : ' + STR(d8_intensity/ISNULL(NULLIF(d0_intensity,0.0),0.001)*ISNULL(NULLIF(PE.quant_normalization,0.0),1.0),5,2) ". "ELSE STR(d0_intensity/ISNULL(NULLIF(d8_intensity,0.0),0.001)/ISNULL(NULLIF(PE.quant_normalization,0.0),1.0),4,2) + ' : 1' ". "END) + ". "ISNULL(manually_changed,'') + (CASE WHEN NULLIF(PE.quant_normalization,0.0) IS NULL THEN '' ELSE ' =' END)"; } elsif ($parameters{quantitation_format} eq "d01") { $quant_format_clause = "'1 :' + STR(d8_intensity/ISNULL(NULLIF(d0_intensity,0.0),0.001)*ISNULL(NULLIF(PE.quant_normalization,0.0),1.0),5,2) + ". "ISNULL(manually_changed,'') + (CASE WHEN NULLIF(PE.quant_normalization,0.0) IS NULL THEN '' ELSE ' =' END)"; } elsif ($parameters{quantitation_format} eq "d81") { $quant_format_clause = "STR(d0_intensity/ISNULL(NULLIF(d8_intensity,0.0),.001)/ISNULL(NULLIF(PE.quant_normalization,0.0),1.0),5,2) + ': 1' + ". "ISNULL(manually_changed,'') + (CASE WHEN NULLIF(PE.quant_normalization,0.0) IS NULL THEN '' ELSE ' =' END)"; } elsif ($parameters{quantitation_format} eq "decimal") { $quant_format_clause = "STR(d0_intensity/ISNULL(NULLIF(d8_intensity,0.0),0.001)/ISNULL(NULLIF(PE.quant_normalization,0.0),1.0),10,4) + ". "ISNULL(manually_changed,'') + (CASE WHEN NULLIF(PE.quant_normalization,0.0) IS NULL THEN '' ELSE ' =' END)"; } elsif ($parameters{quantitation_format} eq "decimalplain") { $quant_format_clause = "STR(d0_intensity/ISNULL(NULLIF(d8_intensity,0.0),0.001)/ISNULL(NULLIF(PE.quant_normalization,0.0),1.0),10,4)"; } else { print "

Cannot parse Quantitation Format! Check syntax.

\n\n"; return; } } #### Build SORT ORDER my $order_by_clause = ""; if ($parameters{sort_order}) { if ($parameters{sort_order} =~ /SELECT|TRUNCATE|DROP|DELETE|FROM|GRANT/i) { print "

Cannot parse Sort Order! Check syntax.

\n\n"; return; } else { $order_by_clause = " ORDER BY $parameters{sort_order}"; } } #### Build Additional peptide constraints my $second_peptide_clause = ""; if ($parameters{peptide_options}) { if ($parameters{peptide_options} =~ /SELECT|TRUNCATE|DROP|DELETE|FROM|GRANT/i) { print "

Cannot parse Peptide Options! Check syntax.

\n\n"; return; } else { my $C = ""; $C = "C%" if ( $parameters{peptide_options} =~ /C_containing/ ); if ( $parameters{peptide_options} =~ /DoublyTryptic/ ) { $second_peptide_clause = " AND SH.peptide_string LIKE '[RK].%${C}%[RK]._'"; } elsif ( $parameters{peptide_options} =~ /AtLeastSinglyTryptic/ ) { $second_peptide_clause = " AND ( SH.peptide_string LIKE '[RK].%${C}%._' OR ". "SH.peptide_string LIKE '_.%${C}%[RK]._' )"; } elsif ( $parameters{peptide_options} =~ /NotAtAllTryptic/ ) { $second_peptide_clause = " AND ( SH.peptide_string NOT LIKE '[RK].%' AND ". "SH.peptide_string NOT LIKE '%[RK]._' AND ". " SH.peptide_string LIKE '_.%${C}%._' )"; } elsif ( $parameters{peptide_options} =~ /OnlySinglyTryptic/ ) { $second_peptide_clause = " AND ( ( SH.peptide_string LIKE '[RK].%${C}%._'\n". " OR SH.peptide_string LIKE '_.%${C}%[RK]._' )\n". " AND SH.peptide_string NOT LIKE '[RK].%${C}%[RK]._' )"; } elsif ( $parameters{peptide_options} =~ /C_containing/ ) { $second_peptide_clause = " AND SH.peptide_string LIKE '_.%${C}%._'"; } if ( $parameters{peptide_options} =~ /C_missing/ ) { $second_peptide_clause .= " AND SH.peptide_string NOT LIKE '_.%C%._'"; } } } #### 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 $group_by_clause = ""; my $final_group_by_clause = ""; my @column_array; my $peptide_column = ""; my $count_column = ""; #### Define the desired columns in the query #### [friendly name used in url_cols,SQL,displayed column title] my @column_array = ( ["experiment_tag","experiment_tag","Exp"], #["set_tag","set_tag","DB"], ["search_batch_subdir","search_batch_subdir","Search Batch"], ["file_root","S.file_root","file_root"], ["out_file","'.out'",".out"], ["fraction_number","F.fraction_number","Frac #"], ["start_scan","MSS.start_scan","Scan Num"], #["scan_time","STR(MSS.scan_time/60.0,7,2)","Scan Time"], ["retention_time","STR((MSS.scan_time-F.column_delay)/60.0,7,2)","EstRT (min)"], ["calc_buffer_percent","STR(MSS.calc_buffer_percent,7,1)","% ACN"], ["best_hit_flag","best_hit_flag","bh"], ["probability","STR(SH.probability,7,3)","Prob"], ["cross_corr_rank","SH.cross_corr_rank","Rxc"], ["prelim_score_rank","SH.prelim_score_rank","RSp"], ["precursor_mass","LTRIM(STR((S.sample_mass_plus_H+(S.assumed_charge-1)*1.008)/S.assumed_charge,7,2))","Precursor m/z"], ["precursor_intensity","STR(MSS.precursor_intensity,10,2)","Precursor Intensity x 10^6"], ["hit_mass_plus_H","STR(SH.hit_mass_plus_H,9,2)","(M+H)+"], ["hit_mass_diff","STR(SH.mass_delta,5,2)","Mass Diff"], ["cross_corr","STR(SH.cross_corr,5,4)","XCorr"], ["next_dCn","STR(SH.next_dCn,5,3)","dCn"], ["prelim_score","STR(SH.prelim_score,8,1)","Sp"], ["ions","STR(SH.identified_ions,2,0) + '/' + STR(SH.total_ions,3,0)","Ions"], #["ions_old","STR(SH.identified_ions,2,0) + '/' + STR(SH.total_ions,3,0)","!Ions"], ["reference","reference","Reference"], ["peptideatlas_protein_link","NULL","PA Prot"], ["additional_proteins","additional_proteins","N+"], ["additional_proteins_old","additional_proteins","!N+"], ["peptide_string","peptide_string","Peptide"], ["peptideatlas_peptide_link","NULL","PA Pep"], ["isoelectric_point","STR(SH.isoelectric_point,8,3)","Calc pI"], ["gravy_score","STR(gravy_score,8,3)","Gravy Score"], ["quantitation_dat","$quant_format_clause","DAT Quant"], ["quantitation_mzXML","$quant_format_clause","mzXML Quant"], #["assumed_charge","S.assumed_charge","assumed_charge"], ["annotation_label","label_desc","Annot"], ["search_batch_id","SB.search_batch_id","search_batch_id"], ["project_id","PE.project_id","project_id"], ["msms_spectrum_id","S.msms_spectrum_id","msms_spectrum_id"], ["search_id","S.search_id","search_id"], ["search_hit_id","SH.search_hit_id","search_hit_id"], ["fraction_tag","F.fraction_tag","fraction_tag"], ["data_location","SB.data_location","data_location"], ["peptide","peptide","actual_peptide"], ["biosequence_set_id","BSS.biosequence_set_id","biosequence_set_id"], ["biosequence_id","SH.biosequence_id","biosequence_id"], ["assumed_charge","S.assumed_charge","assumed_charge"], ["set_path","BSS.set_path","set_path"], ["quantitation_id","QUAN.quantitation_id","quantitation_id"], ["search_hit_annotation_id","SHA.search_hit_annotation_id","search_hit_annotation_id"], ); #### Adjust the columns definition based on user-selected options if ( $parameters{display_options} =~ /BSDesc/ ) { push(@column_array,["biosequence_desc","biosequence_desc","Reference Description"]); } if ( $parameters{display_options} =~ /MaxRefWidth/ ) { $max_widths{'Reference'} = 20; } 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 ); #### Define the SQL statement $sql = qq~ SELECT $limit_clause->{top_clause} $columns_clause FROM $TBPR_SEARCH_HIT SH INNER JOIN $TBPR_SEARCH S ON ( SH.search_id = S.search_id ) INNER JOIN $TBPR_SEARCH_BATCH SB ON ( S.search_batch_id = SB.search_batch_id ) INNER JOIN $TBPR_MSMS_SPECTRUM MSS ON ( S.msms_spectrum_id = MSS.msms_spectrum_id ) INNER JOIN $TBPR_FRACTION F ON ( MSS.fraction_id = F.fraction_id ) INNER JOIN $TBPR_BIOSEQUENCE_SET BSS ON ( SB.biosequence_set_id = BSS.biosequence_set_id ) INNER JOIN $TBPR_PROTEOMICS_EXPERIMENT PE ON ( F.experiment_id = PE.experiment_id ) LEFT JOIN $TBPR_QUANTITATION QUAN ON ( SH.search_hit_id = QUAN.search_hit_id ) LEFT JOIN $TBPR_BIOSEQUENCE BS ON ( SB.biosequence_set_id = BS.biosequence_set_id AND SH.reference = BS.biosequence_name ) LEFT JOIN $TBPR_SEARCH_HIT_ANNOTATION SHA ON ( SH.search_hit_id = SHA.search_hit_id ) LEFT JOIN $TBPR_ANNOTATION_LABEL AL ON ( SHA.annotation_label_id = AL.annotation_label_id ) WHERE 1 = 1 $search_batch_clause $probability_clause $best_hit_clause $xcorr_clause $delta_xcorr_clause $xcorr_rank_clause $charge_clause $reference_clause $biosequence_accession_clause $description_clause $peptide_clause $peptide_string_clause $second_peptide_clause $precursor_mass_clause $mass_clause $start_scan_clause $percent_buffer_b_clause $isoelectric_point_clause $file_root_clause $quantitation_clause $annotation_label_clause $annotation_status_clause $order_by_clause $limit_clause->{trailing_limit_clause} ~; #### Ad-Hoc, and sort of dangerous way of reversing the sense of the #### quantitation ratio if ( $parameters{quantitation_values} eq "d0d8" ) { #### This is the default #### Swap the numerator and denominator } elsif ( $parameters{quantitation_values} eq "d8d0" ) { $sql =~ s/d0\_/dQQQ_/g; $sql =~ s/d8\_/d0_/g; $sql =~ s/dQQQ\_/d8_/g; } #### Certain types of actions should be passed to links my $pass_action = "QUERY"; $pass_action = $apply_action if ($apply_action =~ /QUERY/i); #### Define the hypertext links for columns that need them %url_cols = ('.out' => "$CGI_BASE_DIR/Proteomics/ShowOutFile.cgi?search_id=\%$colnameidx{search_id}V", '.out_ATAG' => 'TARGET="Win1"', 'file_root' => "$base_url?QUERY_NAME=PR_GetSearch&search_batch_id=\%$colnameidx{search_batch_id}V&file_root_constraint=\%$colnameidx{file_root}V&apply_action=QUERY", 'file_root_ATAG' => 'TARGET="Win1"', 'Reference' => "$CGI_BASE_DIR/Proteomics/BrowseBioSequence.cgi?biosequence_name_constraint=\%V&biosequence_set_id=\%$colnameidx{biosequence_set_id}V&label_peptide=\%$colnameidx{peptide}V&apply_action=HIDEQUERY&display_options=SequenceFormat,ShowExtraProteinProps&navigation_bar=SHORT", ##'Reference' => "http://regis-web.systemsbiology.net/cgi-bin/consensus_html4?Ref=%V&Db=\%$colnameidx{set_path}V&Pep=\%$colnameidx{peptide}V&MassType=0", 'Reference_ATAG' => 'TARGET="Win1"', '!Ions' => "http://regis-web.systemsbiology.net/cgi-bin/displayions_html5?Dta=/data/search/\%$colnameidx{data_location}V/\%$colnameidx{fraction_tag}V/\%$colnameidx{file_root}V.dta&MassType=0&NumAxis=1&Pep=\%$colnameidx{peptide}V", '!Ions_ATAG' => 'TARGET="Win1"', 'Ions' => "$CGI_BASE_DIR/Proteomics/ShowSpectrum.cgi?msms_spectrum_id=\%$colnameidx{msms_spectrum_id}V&search_batch_id=\%$colnameidx{search_batch_id}V&assumed_charge=\%$colnameidx{assumed_charge}V&precursor_mass=\%$colnameidx{precursor_mass}V&peptide=\%$colnameidx{peptide_string}V", 'Ions_ATAG' => 'TARGET="Win1"', '!N+' => "http://regis-web.systemsbiology.net/cgi-bin/blast_html4?Db=\%$colnameidx{set_path}V&Pep=\%$colnameidx{peptide}V&MassType=0", '!N+_ATAG' => 'TARGET="Win1"', 'N+' => "$CGI_BASE_DIR/Proteomics/BrowseBioSequence.cgi?biosequence_set_id=\%$colnameidx{biosequence_set_id}V&biosequence_seq_constraint=*\%$colnameidx{peptide}V*&display_options=MaxSeqWidth&search_hit_id=\%$colnameidx{search_hit_id}V&label_peptide=\%$colnameidx{peptide}V&apply_action=HIDEQUERY&display_options=SequenceFormat,ShowExtraProteinProps&navigation_bar=SHORT", 'N+_ATAG' => 'TARGET="Win1"', 'Peptide' => "http://www.ncbi.nlm.nih.gov/blast/Blast.cgi?PROGRAM=blastp&DATABASE=nr&OVERVIEW=TRUE&EXPECT=1000&FILTER=L&QUERY=\%$colnameidx{peptide}V", 'Peptide_ATAG' => 'TARGET="Win1"', 'Annot' => "$CGI_BASE_DIR/Proteomics/ManageTable.cgi?TABLE_NAME=PR_search_hit_annotation&search_hit_annotation_id=\%$colnameidx{search_hit_annotation_id}V&search_hit_id=\%$colnameidx{search_hit_id}V&project_id=\%$colnameidx{project_id}V&ShowEntryForm=1", 'Annot_ATAG' => 'TARGET="Win1"', 'Annot_ISNULL' => ' [Add] ', 'bh' => "$CGI_BASE_DIR/Proteomics/SetBestHit.cgi?search_id=\%$colnameidx{search_id}V&search_hit_id=\%$colnameidx{search_hit_id}V", 'bh_ATAG' => 'TARGET="Win1"', 'DAT Quant' => "$CGI_BASE_DIR/Proteomics/Xpress.cgi?quantitation_id=\%$colnameidx{quantitation_id}V&OutFile=\%$colnameidx{file_root}V", 'DAT Quant_ATAG' => 'TARGET="Win1"', 'mzXML Quant' => "$CGI_BASE_DIR/Proteomics/Xpress-mzXML.cgi?quantitation_id=\%$colnameidx{quantitation_id}V&OutFile=\%$colnameidx{file_root}V", 'mzXML Quant_ATAG' => 'TARGET="Win1"', 'PA Prot' => "https://db.systemsbiology.net/sbeams/cgi/PeptideAtlas/Search?search_key=\%$colnameidx{reference}V&action=GO", 'PA Prot_ATAG' => 'TARGET="WinPeptideAtlas"', 'PA Prot_ISNULL' => '', 'PA Prot_OPTIONS' => {embed_html=>1}, 'PA Pep' => "https://db.systemsbiology.net/sbeams/cgi/PeptideAtlas/Search?search_key=\%$colnameidx{peptide}V&action=GO", 'PA Pep_ATAG' => 'TARGET="WinPeptideAtlas"', 'PA Pep_ISNULL' => '', 'PA Pep_OPTIONS' => {embed_html=>1}, ); #### Only allow best_hit changes from the PR_GetSearch mode unless ($TABLE_NAME eq "PR_GetSearch") { delete($url_cols{bh}); delete($url_cols{bh_TAG}); } #### Define columns that should be hidden in the output table %hidden_cols = ('data_location' => 1, 'search_batch_id' => 1, 'msms_spectrum_id' => 1, 'search_id' => 1, 'search_hit_id' => 1, 'fraction_tag' => 1, 'actual_peptide' => 1, 'set_path' => 1, 'biosequence_set_id' => 1, 'biosequence_id' => 1, 'assumed_charge' => 1, 'search_hit_annotation_id' => 1, 'quantitation_id' => 1, 'project_id' => 1, #'assumed_charge' => 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, ); #### 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, ); #### Prepare a resultset of spectra prepare_msms_spectra_resultset( rs_params_ref=>\%rs_params, resultset_ref=>$resultset_ref, query_parameters_ref=>\%parameters, 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 "

Select parameters above and press QUERY

\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; return eval "\"$sql\""; } # end evalSQL ############################################################################### # prepare_msms_spectra_resultset # # Prepare a resultset of spectra if all the peptides that came back from # the query are identical ############################################################################### sub prepare_msms_spectra_resultset { my %args = @_; my ($i,$element,$key,$value,$line,$result,$sql); #### Process the arguments list my $resultset_ref = $args{'resultset_ref'}; my $rs_params_ref = $args{'rs_params_ref'}; my $query_parameters_ref = $args{'query_parameters_ref'}; my $base_url = $args{'base_url'}; #my %rs_params = %{$rs_params_ref}; my %rs_params = (); my %parameters = %{$query_parameters_ref}; #### Disable this feature temporarily because it can slow things #### down considerably if the user isn't interested in this stuff. #### Should change the implementation so that this work is only #### if the user wants it. return; #### If the output_mode isn't html, then don't bother with all this return unless ($sbeams->output_mode() eq 'html'); #### Find out what column the msms_spectrum_id is or just return my $msms_spectrum_id_column = $resultset_ref->{column_hash_ref}->{msms_spectrum_id}; return unless ($msms_spectrum_id_column); #### Find out what column the peptide_string is or just return my $peptide_string_column = $resultset_ref->{column_hash_ref}->{peptide_string}; return unless ($msms_spectrum_id_column); #### Set up variables for statistics collection my $row; my $n_rows = scalar(@{$resultset_ref->{data_ref}}); return unless ($n_rows); my $first_peptide_string; my @msms_spectrum_ids = (); #### Loop over each row in the resultset, compiling statistics for ($row=0; $row<$n_rows; $row++) { #### Check the peptide_string if ($row == 0) { $first_peptide_string = $resultset_ref->{data_ref}->[$row]->[$peptide_string_column]; } else { return unless ($first_peptide_string eq $resultset_ref->{data_ref}->[$row]->[$peptide_string_column]); } #### Add the msms_spectrum_id to the list push(@msms_spectrum_ids, $resultset_ref->{data_ref}->[$row]->[$msms_spectrum_id_column]); } #### Start a new section print qq~
All peptides are exactly the same in the above resultset.
\n ~; #### Create the list of msms_spectrum_id's my $msms_spectrum_id_list = join(',',@msms_spectrum_ids); #### Get the spectra $sql = qq~ SELECT msms_spectrum_id,mass,intensity FROM $TBPR_MSMS_SPECTRUM_PEAK WHERE msms_spectrum_id IN ( $msms_spectrum_id_list ) ~; #### Fetch the results from the database my %spectra_resultset = (); $sbeams->fetchResultSet(sql_query=>$sql, resultset_ref=>\%spectra_resultset); #### Store the resultset and parameters to disk $rs_params{set_name} = "SETME"; $sbeams->writeResultSet(resultset_file_ref=>\$rs_params{set_name}, resultset_ref=>\%spectra_resultset, query_parameters_ref=>\%parameters, file_prefix=>'spectra_'); #### Supply some additional links to the Result Set print qq~
Click to download all these MS/MS spectra in format: TSV, Excel
~; return; } # end prepare_msms_spectra_resultset