#!/usr/bin/perl
package Pdb_alignment;

#####################################################################
# Author : Shuai Weng
# Date   : July 2001
# 
# See documentation for the usage details. 
#    
# http:///usr/local/dicty/www_dictybase/db/lib/html/dictyBase/programmer/Pdb_alignment.html
#
#####################################################################

use strict;
use DBI;
use Carp;
use vars qw (@ISA %allowedConstructors);
use dictyBase_Table;
@ISA = qw (dictyBase_Table); # base class

# Class Globals

# put column names in the hash below, that are able to uniquely
# specify a row in the table

%allowedConstructors = (pdb_alignment_no=>undef,
			'query_seq_no:target_seq_no'=>undef);


####################################################################
sub GetAlignInfoArrayRefBYqueryNo {
####################################################################
    my ($self, %args) = @_;
    my $dbh = $args{'dbh'} || die "A database handle must be passed into 'GetAlignInfoArrayRefBYqueryNo' method.";
    my $schema = $self->schema;	
    my $sth = $dbh->prepare("
        SELECT S.sequence_name, S.organism, S.note, A.pct_identical, 
               A.pct_similar, A.score
        FROM   $schema.pdb_sequence S, $schema.pdb_alignment A 
        WHERE  S.pdb_sequence_no = A.target_seq_no
        AND    A.query_seq_no = ?
        AND    A.score <= ?
        AND    A.pct_aligned >= ?
        ORDER BY A.score asc
    ");
    my $cutoff = $args{'score_cutoff'} || '0.01';
    my $pctAligned = $args{'pct_aligned_cutoff'} || '0';
    $sth->execute($args{'query_seq_no'}, log($cutoff), 
		  $pctAligned);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}


####################################################################
sub GetAlignInfoArrayRefBYqueryNmTargetNm {
####################################################################
    my ($self, %args) = @_;
    my $dbh = $args{'dbh'} || die "A database handle must be passed into 'GetAlignInfoArrayRefBYqueryNmTargetNm' method.";
    my $schema = $self->schema;
    my $sth = $dbh->prepare("
        SELECT A.query_align_start_coord, A.query_align_stop_coord, 
               A.target_align_start_coord, A.target_align_stop_coord, 
               A.pct_aligned, A.pct_identical, A.pct_similar,
               A.score, S.query_seq, S.target_seq, S.alignment_symbol,
               PS.organism, PS.note
        FROM   $schema.pdb_alignment A, $schema.alignment_sequence S ,
               $schema.pdb_sequence PS
        WHERE  A.pdb_alignment_no = S.primary_key
        AND    upper(PS.sequence_name) = ?
        AND    S.tab_name = 'PDB_ALIGNMENT'
        AND    A.query_seq_no in
               (select pdb_sequence_no
                from   $schema.pdb_sequence
                where  upper(sequence_name) = ? 
               )
        AND    A.target_seq_no in
               (select pdb_sequence_no
                from   $schema.pdb_sequence
                where  upper(sequence_name) = ? 
               )
        AND    A.score <= ?
        AND    A.pct_aligned >= ?
        ORDER BY A.score asc
    ");
    my $cutoff = $args{'score_cutoff'} || '0.01';
    my $pctAligned = $args{'pct_aligned_cutoff'} || '0';
    $sth->execute(uc($args{'target_name'}),
		  uc($args{'query_name'}), 
		  uc($args{'target_name'}),
		  log($cutoff), 
		  $pctAligned);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

####################################################################
sub GetQueryNmArrayRefBYtargetNm {
####################################################################
    my ($self, %args) = @_;
    my $dbh = $args{'dbh'} || die "A database handle must be passed into 'GetQueryNmArrayRefBYtargetNm' method.";
    my $schema = $self->schema;
    my $sth = $dbh->prepare("
        SELECT F.feature_name, L.locus_name 
        FROM   $schema.feature F, $schema.locus L,
               $schema.pdb_sequence S   
        WHERE  F.locus_no = L.locus_no(+)
        AND    upper(F.feature_name) = upper(S.sequence_name)   
        AND    S.pdb_sequence_no in
               (select a.query_seq_no
                from   $schema.pdb_Alignment a, $schema.pdb_sequence s 
                where  upper(s.sequence_name) like ?
                and    s.pdb_sequence_no = a.target_seq_no
                and    a.score <= ?
                and    a.pct_aligned >= ?
               )         
        ORDER BY F.feature_name
    ");
    my $cutoff = $args{'score_cutoff'} || '0.01';
    my $pctAligned = $args{'pct_aligned_cutoff'} || '0';
    $sth->execute(uc($args{'target_name'}), log($cutoff), $pctAligned);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

####################################################################
sub GetQueryNmArrayRefBYtargetNo {
####################################################################
    my ($self, %args) = @_;
    my $dbh = $args{'dbh'} || die "A database handle must be passed into 'GetQueryNmArrayRefBYtargetNo' method.";
    my $schema = $self->schema;
    my $sth = $dbh->prepare("
        SELECT sequence_name
        FROM   $schema.pdb_sequence   
        WHERE  pdb_sequence_no in
               (select query_seq_no
                from   $schema.pdb_Alignment 
                where  target_seq_no = ? 
                and    score <= ?
                and    pct_aligned >= ?
               )         
        ORDER BY sequence_name
    ");
    my $cutoff = $args{'score_cutoff'} || '0.01';
    my $pctAligned = $args{'pct_aligned_cutoff'} || '0';
    $sth->execute($args{'target_no'}, log($cutoff), $pctAligned);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

####################################################################
sub GetORFGeneArrayRefBYkeyword {
####################################################################
    my ($self, %args) = @_;
    my $dbh = $args{'dbh'} || die "A database handle must be passed to 'GetORFGeneArrayRefBYkeyword' method.";
    my $schema = $self->schema;
    my $sth = $dbh->prepare("
        SELECT unique F.feature_name, L.locus_name, GP.gene_product
        FROM   $schema.feature F, $schema.locus L, 
               $schema.gene_product GP, $schema.locus_gp LGP
        WHERE  upper(GP.gene_product) like ?
        AND    GP.gene_product_no = LGP.gene_product_no
        AND    LGP.locus_no = L.locus_no
        AND    L.locus_no = F.locus_no
        AND    F.feature_name in
               (select S.sequence_name
                from   $schema.pdb_sequence S,
                       $schema.pdb_alignment A
                where  S.source = 'dictyBase'
                and    S.pdb_sequence_no = A.query_seq_no
                and    A.score <= ?
                and    A.pct_aligned >= ?
               )
        UNION
        SELECT unique F.feature_name, L.locus_name, G.go_term
        FROM   $schema.feature F, $schema.locus L, 
               $schema.go G, $schema.go_locus_goev GLG
        WHERE  upper(G.go_term) like ?
        AND    G.goid = GLG.goid
        AND    GLG.locus_no = L.locus_no
        AND    L.locus_no = F.locus_no
        AND    F.feature_name in
               (select S.sequence_name
                from   $schema.pdb_sequence S,
                       $schema.pdb_alignment A
                where  S.source = 'dictyBase'
                and    S.pdb_sequence_no = A.query_seq_no
                and    A.score <= ?
                and    A.pct_aligned >= ?
               )
        UNION
        SELECT unique F.feature_name, L.locus_name, G.go_term
        FROM   $schema.feature F, $schema.locus L, 
               $schema.go G, $schema.go_feat_goev GFG
        WHERE  upper(G.go_term) like ?
        AND    G.goid = GFG.goid
        AND    GFG.feature_no = F.feature_no
        AND    F.locus_no = L.locus_no(+)
        AND    F.feature_name in
               (select S.sequence_name
                from   $schema.pdb_sequence S,
                       $schema.pdb_alignment A
                where  S.source = 'dictyBase'
                and    S.pdb_sequence_no = A.query_seq_no
                and    A.score <= ?
                and    A.pct_aligned >= ?
               )
    ");
    my $cutoff = $args{'cutoff'} || '0.01';
    my $pctAligned = $args{'pct_aligned_cutoff'} || '0';
    $sth->execute(uc($args{'keyword'}), log($cutoff), $pctAligned,
		  uc($args{'keyword'}), log($cutoff), $pctAligned,
		  uc($args{'keyword'}), log($cutoff), $pctAligned);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}



####################################################################
sub GetAlignInfoArrayRefBYchrnum {
####################################################################
    my ($self, %args) = @_;
    my $dbh = $args{'dbh'} || die "A database handle must be passed to 'GetAlignInfoArrayRefBYchrnum' method.";
    my $schema = $self->schema;
    my $sth = $dbh->prepare("
        SELECT F.feature_name, L.locus_name, St.sequence_name,
               St.organism, St.note, 
               A.pct_identical, A.pct_similar, A.score
        FROM   $schema.feature F, $schema.locus L, 
               $schema.pdb_sequence Sq, $schema.pdb_sequence St,
               $schema.pdb_alignment A
        WHERE  F.chromosome = ?
        AND    F.locus_no = L.locus_no(+)
        AND    F.feature_name = Sq.sequence_name
        AND    Sq.pdb_sequence_no = A.query_seq_no
        AND    St.pdb_sequence_no = A.target_seq_no
        AND    A.score <= ?
        AND    A.pct_aligned >= ?
        ORDER BY F.start_coord, A.score asc
    ");
    my $cutoff = $args{'cutoff'} || '0.01';
    my $pctAligned = $args{'pct_aligned_cutoff'} || '0';
    $sth->execute($args{'chromosome'}, log($cutoff), $pctAligned);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

####################################################################
sub DESTROY{
####################################################################
# nothing needs to be done 

}

####################################################################
1; #################################################################
####################################################################

=pod

=head1 Name

Pdb_alignment.pm

=head1 Description

This perl object (Pdb_alignment.pm) acts as container for Pdb_alignment info in oracle database. Once an object has been instantiated, several methods are available to retrieve the attributes of the object. 

=head1 Instantiating a New Pdb_alignment Object

To instantiate a new Pdb_alignment object, you may use one of following syntaxes: 

my $obj = Pdb_alignment->new(dbh=>$dbh,
			     pdb_alignment_no=>$alignmentNo);


OR


my $obj = Pdb_alignment->new(dbh=>$dbh,
			     query_seq_no=>$queryNo,
			     target_seq_no=>$targetNo);


where $dbh is a valid database handle to either dictyBase or SDEV. All passed in values must be valid values for the columns that were provided, otherwise the script will die, with an appropriate error message.
 

=head1 Accessor Methods


All accessor methods take the form of : 


my $column_value = $obj->column_name, eg: 

my $pctAligned = $obj->pct_aligned; 

my $pctSimilar = $obj->pct_similar;

etc. You can use an accessor for any valid column in the pdb_alignment table. 

See valid columns in pdb_alignment table:

http:///usr/local/dicty/www_dictybase/db/lib/cgi-bin/dictyBase/tableSpecifications?table=PDB_ALIGNMENT

=head1 getRow method

Usage:

my $row = $obj->getRow;

This method returns a tab-delimited row from pdb_alignment table.

=head1 Insert, update and delete Methods

You can also use 'Insert' class method and 'update' and 'delete' 
instance methods for inserting new row into database, updating and 
deleting info for a specified row. 

See dictyBase_Table documentation for usage details :

Update : http:///usr/local/dicty/www_dictybase/db/lib/staff/dictyBase/programmer/dictyBase_Table.html#Update_Method

Insert : http:///usr/local/dicty/www_dictybase/db/lib/staff/dictyBase/programmer/dictyBase_Table.html#Insert_Method

Delete : http:///usr/local/dicty/www_dictybase/db/lib/staff/dictyBase/programmer/dictyBase_Table.html#Delete_Method

=head1 Author

Shuai Weng

shuai@genome.stanford.edu

=cut