#!/usr/local/bin/perl ############################################################################### # Program : SummarizePeptides # Author : Eric Deutsch # $Id$ # # Description : This program that allows users to # summarize proteins/peptides across one or more experiments. # # 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="Summarize Peptides"; $TABLE_NAME="PR_SummarizePeptides" 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) { $parameters{input_form_format} = "minimum_detail"; $parameters{probability_constraint} = '>.7'; $parameters{display_options} = 'GroupReference'; $parameters{n_annotations_constraint} = '>0'; $parameters{sort_order} = 'tABS.row_count DESC'; } #### Apply any parameter adjustment logic #none #### 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 over which ". "to summarize!

\n\n"; return; } #### Build PROBABILITY constraint my $probability_clause = $sbeams->parseConstraint2SQL( constraint_column=>"SH.probability", constraint_type=>"flexible_float", constraint_name=>"Probability", 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 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'); #### If there is a constraint, also apply it to BS.biosequence_name my $biosequence_name_clause = ""; if ($reference_clause) { $biosequence_name_clause = $reference_clause; $biosequence_name_clause =~ s/SH\.reference/BS\.biosequence_name/; } #### Build GENE NAME constraint my $gene_name_clause = $sbeams->parseConstraint2SQL( constraint_column=>"BS.biosequence_gene_name", constraint_type=>"plain_text", constraint_name=>"Gene Name", constraint_value=>$parameters{gene_name_constraint} ); return if ($gene_name_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 MOLECULAR FUNCTION constraint my $molecular_function_clause = $sbeams->parseConstraint2SQL( constraint_column=>"MFA.annotation", constraint_type=>"plain_text", constraint_name=>"Molecular Function", constraint_value=>$parameters{molecular_function_constraint} ); return if ($molecular_function_clause eq '-1'); #### Build BIOLOGICAL PROCESS constraint my $biological_process_clause = $sbeams->parseConstraint2SQL( constraint_column=>"BPA.annotation", constraint_type=>"plain_text", constraint_name=>"Biological Process", constraint_value=>$parameters{biological_process_constraint} ); return if ($biological_process_clause eq '-1'); #### Build CELLULAR COMPONENT constraint my $cellular_component_clause = $sbeams->parseConstraint2SQL( constraint_column=>"CCA.annotation", constraint_type=>"plain_text", constraint_name=>"Cellular Component", constraint_value=>$parameters{cellular_component_constraint} ); return if ($cellular_component_clause eq '-1'); #### Build INTERPRO PROTEIN DOMAIN constraint my $protein_domain_clause = $sbeams->parseConstraint2SQL( constraint_column=>"IPDA.annotation", constraint_type=>"plain_text", constraint_name=>"InterPro Protein Domain", constraint_value=>$parameters{protein_domain_constraint} ); return if ($protein_domain_clause eq '-1'); #### Build FAVORED CODON FREQUENCY constraint my $fav_codon_frequency_clause = $sbeams->parseConstraint2SQL( constraint_column=>"BPS.fav_codon_frequency", constraint_type=>"flexible_float", constraint_name=>"Favored Codon Frequency", constraint_value=>$parameters{fav_codon_frequency_constraint} ); return if ($fav_codon_frequency_clause eq '-1'); #### Build TRANSMEMBRANE CLASS constraint my $transmembrane_class_clause = $sbeams->parseConstraint2SQL( constraint_column=>"BPS.transmembrane_class", constraint_type=>"text_list", constraint_name=>"Transmembrane Class", constraint_value=>$parameters{transmembrane_class_constraint} ); return if ($transmembrane_class_clause eq '-1'); #### Build NUMBER OF TRANSMEMBRANE REGIONS constraint my $n_transmembrane_regions_clause = $sbeams->parseConstraint2SQL( constraint_column=>"BPS.n_transmembrane_regions", constraint_type=>"flexible_int", constraint_name=>"Number of Transmembrane regions", constraint_value=>$parameters{n_transmembrane_regions_constraint} ); return if ($n_transmembrane_regions_clause eq '-1'); #### Build PROTEIN LENGTH constraint my $protein_length_clause = $sbeams->parseConstraint2SQL( constraint_column=>"DATALENGTH(BS.biosequence_seq)", constraint_type=>"flexible_int", constraint_name=>"Protein Length", constraint_value=>$parameters{protein_length_constraint} ); return if ($protein_length_clause eq '-1'); #### Build ACCESSION constraint my $accession_clause = $sbeams->parseConstraint2SQL( constraint_column=>"BS.biosequence_accession", constraint_type=>"plain_text", constraint_name=>"Accession", constraint_value=>$parameters{accession_constraint} ); return if ($accession_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 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 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 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 ($percent_buffer_b_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 NUMBER OF ANNOTATIONS constraint my $n_annotations_clause = $sbeams->parseConstraint2SQL( constraint_column=>"row_count", constraint_type=>"flexible_int", constraint_name=>"Number of Matches", constraint_value=>$parameters{n_annotations_constraint} ); return if ($n_annotations_clause eq '-1'); #### 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 GENE ANNOTATION LEVEL constraint $parameters{gene_annotation_level_constraint} = 'leaf' unless ($parameters{gene_annotation_level_constraint}); my $gene_annotation_level_clause = $sbeams->parseConstraint2SQL( constraint_column=>"hierarchy_level", constraint_type=>"plain_text", constraint_name=>"Gene Annotation Level Constraint", constraint_value=>$parameters{gene_annotation_level_constraint} ); return if ($gene_annotation_level_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 "

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 = ""; #### If the user opted to see the GO columns, add them in my @additional_columns = (); if ( $parameters{display_options} =~ /ShowGOColumns/ || $molecular_function_clause.$biological_process_clause. $cellular_component_clause.$protein_domain_clause ) { @additional_columns = ( ["molecular_function","MFA.annotation","Molecular Function"], ["molecular_function_GO","MFA.external_accession","molecular_function_GO"], ["biological_process","BPA.annotation","Biological Process"], ["biological_process_GO","BPA.external_accession","biological_process_GO"], ["cellular_component","CCA.annotation","Cellular Component"], ["cellular_component_GO","CCA.external_accession","cellular_component_GO"], ["interpro_protein_domain","IPDA.annotation","InterPro Protein Domain"], ["interpro_protein_domain_GO","IPDA.external_accession","interpro_protein_domain_GO"], ); } #### If the user opted to see GO columns or provided some GO constraints, #### then join in the GO tables my $GO_join = ""; if ( $parameters{display_options} =~ /ShowGOColumns/ || $molecular_function_clause.$biological_process_clause. $cellular_component_clause.$protein_domain_clause ) { $GO_join = qq~ LEFT JOIN $TBPR_BIOSEQUENCE_ANNOTATED_GENE AG ON ( BS.biosequence_id = AG.biosequence_id ) LEFT JOIN $TBBL_GENE_ANNOTATION MFA ON ( AG.annotated_gene_id = MFA.annotated_gene_id AND MFA.gene_annotation_type_id = 1 AND MFA.idx = 0 AND MFA.hierarchy_level = '$parameters{gene_annotation_level_constraint}' ) LEFT JOIN $TBBL_GENE_ANNOTATION BPA ON ( AG.annotated_gene_id = BPA.annotated_gene_id AND BPA.gene_annotation_type_id = 2 AND BPA.idx = 0 AND BPA.hierarchy_level = '$parameters{gene_annotation_level_constraint}' ) LEFT JOIN $TBBL_GENE_ANNOTATION CCA ON ( AG.annotated_gene_id = CCA.annotated_gene_id AND CCA.gene_annotation_type_id = 3 AND CCA.idx = 0 AND CCA.hierarchy_level = '$parameters{gene_annotation_level_constraint}' ) LEFT JOIN $TBBL_GENE_ANNOTATION IPDA ON ( AG.annotated_gene_id = IPDA.annotated_gene_id AND IPDA.gene_annotation_type_id = 4 AND IPDA.idx = 0 AND IPDA.hierarchy_level = '$parameters{gene_annotation_level_constraint}' ) ~; } #### Add in some extra columns if the user wants to see them if ( $parameters{display_options} =~ /ShowExtraProteinProps/ ) { @additional_columns = ( ["fav_codon_frequency","STR(BPS.fav_codon_frequency,10,3)","Favored Codon Frequency"], ["transmembrane_class","BPS.transmembrane_class","Transmembrane Regions Class"], ["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] #### If grouping by peptide or peptide_string and reference if ( $parameters{display_options} =~ /GroupPeptide/ ) { my $colname = "peptide"; my $dispname = "Peptide"; if ( $parameters{display_options} =~ /GroupPeptideString/ ) { $colname = "peptide_string"; $dispname = "Peptide String"; } @column_array = ( ["biosequence_gene_name","BS.biosequence_gene_name","Gene Name"], ["accessor","DBX.accessor","accessor"], ["biosequence_accession","BS.biosequence_accession","Accession"], ["peptideatlas_protein_link","NULL","PA Prot"], ["reference","BS.biosequence_name","Reference"], ["$colname","$colname","$dispname"], ["peptideatlas_peptide_link","NULL","PA Pep"], ["count","tABS.row_count","Count"], ["average_quant","tABS.average_quant","Average Quant"], ["stdev_quant","tABS.stdev_quant","StdDev Quant"], ["n_quant","tABS.n_quant","N Quant"], ["isoelectric_point","STR(tABS.isoelectric_point,8,3)","Calc pI"], @additional_columns, ["biosequence_desc","BS.biosequence_desc","Reference Description"], ["organism","O.full_name","Organism"], ); $group_by_clause = " GROUP BY SH.reference,$colname,SH.isoelectric_point"; $peptide_column = "$colname,"; $count_column = "COUNT(*) AS 'row_count',STR(AVG(NULLIF(d0_intensity,0.0)/NULLIF(d8_intensity,0.0)/ISNULL(NULLIF(PE.quant_normalization,0.0),1.0)),10,2) AS 'average_quant',STR(STDEV(NULLIF(d0_intensity,0.0)/NULLIF(d8_intensity,0.0)/ISNULL(NULLIF(PE.quant_normalization,0.0),1.0)),10,2) AS 'stdev_quant',SUM(CASE WHEN (NULLIF(d0_intensity,0.0) IS NULL OR NULLIF(d8_intensity,0.0) IS NULL) THEN 0 ELSE 1 END) AS 'n_quant',SH.isoelectric_point"; #### If grouping by reference } elsif ( $parameters{display_options} =~ /GroupReference/ ) { @column_array = ( ["biosequence_gene_name","BS.biosequence_gene_name","Gene Name"], ["accessor","DBX.accessor","accessor"], ["accessor_suffix","DBX.accessor_suffix","accessor_suffix"], ["biosequence_accession","BS.biosequence_accession","Accession"], ["peptideatlas_protein_link","NULL","PA Prot"], ["reference","BS.biosequence_name","Reference"], ["count","tABS.row_count","Count"], ["average_quant","tABS.average_quant","Quant Avg"], ["stdev_quant","tABS.stdev_quant","Quant StDev"], ["n_quant","tABS.n_quant","Quant N"], @additional_columns, ["biosequence_desc","BS.biosequence_desc","Reference Description"], ["organism","O.full_name","Organism"], ); $group_by_clause = " GROUP BY SH.reference"; $count_column = "COUNT(*) AS 'row_count',STR(AVG(NULLIF(d0_intensity,0.0)/NULLIF(d8_intensity,0.0)/ISNULL(NULLIF(PE.quant_normalization,0.0),1.0)),10,2) AS 'average_quant',STR(STDEV(NULLIF(d0_intensity,0.0)/NULLIF(d8_intensity,0.0)/ISNULL(NULLIF(PE.quant_normalization,0.0),1.0)),10,2) AS 'stdev_quant',SUM(CASE WHEN (NULLIF(d0_intensity,0.0) IS NULL OR NULLIF(d8_intensity,0.0) IS NULL) THEN 0 ELSE 1 END) AS 'n_quant'"; #### If no grouping } else { @column_array = ( ["biosequence_gene_name","BS.biosequence_gene_name","Gene Name"], ["accessor","DBX.accessor","accessor"], ["accessor_suffix","DBX.accessor_suffix","accessor_suffix"], ["biosequence_accession","BS.biosequence_accession","Accession"], ["peptideatlas_protein_link","NULL","PA Prot"], ["reference","BS.biosequence_name","Reference"], ["peptide","peptide","Peptide"], ["peptideatlas_peptide_link","NULL","PA Pep"], ["count","tABS.row_count","Count"], ["average_quant","tABS.average_quant","Quant"], ["isoelectric_point","STR(tABS.isoelectric_point,8,3)","Calc pI"], @additional_columns, ["biosequence_desc","BS.biosequence_desc","Reference Description"], ["organism","O.full_name","Organism"], ); $peptide_column = "peptide,"; $count_column = "1 AS 'row_count',STR(NULLIF(d0_intensity,0.0)/NULLIF(d8_intensity,0.0)/ISNULL(NULLIF(PE.quant_normalization,0.0),1.0),10,2) AS 'average_quant',SH.isoelectric_point"; } #### Limit the width of the Reference column if user selected if ( $parameters{display_options} =~ /MaxRefWidth/ ) { $max_widths{'Reference'} = 20; } #### 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 = (); $colnameidx{peptide} = 99; $colnameidx{peptide_string} = 99; 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 BS.biosequence_name, MIN(BS.biosequence_id) AS 'min_biosequence_id' INTO #tmpBSids FROM $TBPR_BIOSEQUENCE BS INNER JOIN $TBPR_SEARCH_BATCH SB ON ( BS.biosequence_set_id = SB.biosequence_set_id ) LEFT JOIN $TBPR_BIOSEQUENCE_PROPERTY_SET BPS ON ( BS.biosequence_id = BPS.biosequence_id ) $GO_join WHERE 1 = 1 $search_batch_clause $biosequence_name_clause $description_clause $gene_name_clause $accession_clause $molecular_function_clause $biological_process_clause $cellular_component_clause $protein_domain_clause $transmembrane_class_clause $n_transmembrane_regions_clause $fav_codon_frequency_clause $protein_length_clause GROUP BY BS.biosequence_name -- SELECT SH.reference,$peptide_column$count_column INTO #tmpAnnBSids FROM $TBPR_SEARCH_HIT SH INNER JOIN $TBPR_SEARCH S ON ( SH.search_id = S.search_id ) INNER JOIN $TBPR_MSMS_SPECTRUM MSS ON ( S.msms_spectrum_id = MSS.msms_spectrum_id ) LEFT JOIN $TBPR_SEARCH_HIT_ANNOTATION SHA ON ( SH.search_hit_id = SHA.search_hit_id ) LEFT JOIN $TBPR_QUANTITATION QUAN ON ( SH.search_hit_id = QUAN.search_hit_id ) INNER JOIN $TBPR_SEARCH_BATCH SB ON ( S.search_batch_id = SB.search_batch_id ) INNER JOIN $TBPR_PROTEOMICS_EXPERIMENT PE ON ( SB.experiment_id = PE.experiment_id ) INNER JOIN $TBPR_BIOSEQUENCE BS ON ( SB.biosequence_set_id = BS.biosequence_set_id AND SH.reference = BS.biosequence_name ) WHERE 1 = 1 $search_batch_clause $probability_clause $best_hit_clause $xcorr_clause $delta_xcorr_clause $xcorr_rank_clause $charge_clause $reference_clause $gene_name_clause $description_clause $accession_clause $peptide_clause $peptide_string_clause $second_peptide_clause $precursor_mass_clause $mass_clause $percent_buffer_b_clause $isoelectric_point_clause $file_root_clause $quantitation_clause $annotation_label_clause $annotation_status_clause $group_by_clause -- SELECT $limit_clause->{top_clause} $columns_clause FROM #tmpBSids tBS LEFT JOIN #tmpAnnBSids tABS ON ( tBS.biosequence_name = tABS.reference ) INNER JOIN $TBPR_BIOSEQUENCE BS ON ( tBS.min_biosequence_id = BS.biosequence_id ) LEFT JOIN $TB_DBXREF DBX ON ( BS.dbxref_id = DBX.dbxref_id ) LEFT JOIN $TBPR_BIOSEQUENCE_PROPERTY_SET BPS ON ( BS.biosequence_id = BPS.biosequence_id ) INNER JOIN $TBPR_BIOSEQUENCE_SET BSS ON ( BS.biosequence_set_id = BSS.biosequence_set_id ) INNER JOIN $TB_ORGANISM O ON ( BSS.organism_id = O.organism_id ) $GO_join WHERE 1 = 1 $n_annotations_clause $description_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); #### 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' && $key ne 'reference_constraint') { if ($parameters{$key}) { use CGI::Util qw(escape unescape); push(@parameters_to_pass,"$key=".escape($parameters{$key})); } } } if (@parameters_to_pass) { $parameters_list = join('&',@parameters_to_pass); } #### Define the hypertext links for columns that need them %url_cols = ('Accession' => "\%$colnameidx{accessor}V\%$colnameidx{biosequence_accession}V\%$colnameidx{accessor_suffix}V", 'Accession_ATAG' => 'TARGET="Win1" ONMOUSEOVER="window.status=\'Show more information about this protein in source database\'; return true"', 'Reference' => "$CGI_BASE_DIR/Proteomics/GetSearchHits?QUERY_NAME=PR_GetSearchHits&search_batch_id=$parameters{search_batch_id}&reference_constraint=\%$colnameidx{reference}V&display_options=BSDesc,MaxRefWidth&apply_action=$pass_action", 'Reference_ATAG' => 'TARGET="Win2" ONMOUSEOVER="window.status=\'Show all occurrences of this protein in these experiments\'; return true"', 'Peptide' => "$CGI_BASE_DIR/Proteomics/GetSearchHits?QUERY_NAME=PR_GetSearchHits&search_batch_id=$parameters{search_batch_id}&peptide_constraint=\%$colnameidx{peptide}V&display_options=BSDesc,MaxRefWidth&apply_action=$pass_action", 'Peptide_ATAG' => 'TARGET="Win2" ONMOUSEOVER="window.status=\'Show all occurrences of this peptide in these experiments\'; return true"', 'Peptide String' => "$CGI_BASE_DIR/Proteomics/GetSearchHits?QUERY_NAME=PR_GetSearchHits&search_batch_id=$parameters{search_batch_id}&peptide_string_constraint=\%$colnameidx{peptide_string}V&display_options=BSDesc,MaxRefWidth&apply_action=$pass_action", 'Peptide String_ATAG' => 'TARGET="Win2" ONMOUSEOVER="window.status=\'Show all occurrences of this exact peptide instance in these experiments\'; return true"', 'Count' => "$CGI_BASE_DIR/Proteomics/GetSearchHits?QUERY_NAME=PR_GetSearchHits&search_batch_id=$parameters{search_batch_id}&reference_constraint=\%$colnameidx{reference}V&peptide_constraint=\%$colnameidx{peptide}V&peptide_string_constraint=\%$colnameidx{peptide_string}V&${parameters_list}&display_options=BSDesc,MaxRefWidth&apply_action=$pass_action", 'Count_ATAG' => 'TARGET="Win2" ONMOUSEOVER="window.status=\'Show just this many that match all above criteria\'; return true"', 'Molecular Function' => "http://www.ebi.ac.uk/ego/QuickGO?mode=display&entry=\%$colnameidx{molecular_function_GO}V", 'Molecular Function_ATAG' => 'TARGET="WinExt"', 'Molecular Function_OPTIONS' => {semicolon_separated_list=>1}, 'Biological Process' => "http://www.ebi.ac.uk/ego/QuickGO?mode=display&entry=\%$colnameidx{biological_process_GO}V", 'Biological Process_ATAG' => 'TARGET="WinExt"', 'Biological Process_OPTIONS' => {semicolon_separated_list=>1}, 'Cellular Component' => "http://www.ebi.ac.uk/ego/QuickGO?mode=display&entry=\%$colnameidx{cellular_component_GO}V", 'Cellular Component_ATAG' => 'TARGET="WinExt"', 'Cellular Component_OPTIONS' => {semicolon_separated_list=>1}, 'InterPro Protein Domain' => "http://www.ebi.ac.uk/interpro/IEntry?ac=\%$colnameidx{interpro_protein_domain_GO}V", 'InterPro Protein Domain_ATAG' => 'TARGET="WinExt"', 'InterPro Protein Domain_OPTIONS' => {semicolon_separated_list=>1}, 'PA Prot' => "https://db.systemsbiology.net/sbeams/cgi/PeptideAtlas/Search?search_key=\%$colnameidx{biosequence_accession}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}, ); ##'Reference_ATAG' => "TARGET=\"Win1\" ONMOUSEOVER=\"window.status='%V'; return true\"", #### Define columns that should be hidden in the output table %hidden_cols = ('accessor' => 1, 'accessor_suffix' => 1, 'molecular_function_GO' => 1, 'biological_process_GO' => 1, 'cellular_component_GO' => 1, 'interpro_protein_domain_GO' => 1, 'Organism' => 1, ); ######################################################################### #### If QUERY or VIEWRESULTSET was selected, display the data if ($apply_action =~ /QUERY/i || $apply_action eq "VIEWRESULTSET") { #### Print out a message at the top of the resultset if ($sbeams->invocation_mode() eq 'http') { print "WARNING: The average quantitation values ". "are from XPRESS, not ASAPRatio, and these values are just a plain, ". "unweighted average (weight information not available). Use with ". "caution.

\n"; } #### 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, ); #### 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