#!/usr/bin/perl
package Dataset;

#####################################################################
# Author : Shuai Weng
# Date   : Nov. 2002
# 
# 
#
#####################################################################

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 = (dataset_no=>undef,
			dataset_name=>undef);


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

}

######################################################################
sub query_url {
######################################################################
    my ($self) = @_;

    return $self->_getParameterValue('query_url');

}

######################################################################
sub title {
######################################################################
    my ($self) = @_;

    return $self->_getParameterValue('title');
    
}

######################################################################
sub web_site_url {
######################################################################
    my ($self) = @_;

    return $self->_getParameterValue('web_site_url');

}

######################################################################
sub x_blocksize {
######################################################################
    my ($self) = @_;

    return $self->_getParameterValue('x_blocksize');

}

######################################################################
sub y_blocksize {
######################################################################
    my ($self) = @_;

    return $self->_getParameterValue('y_blocksize');

}

######################################################################
sub hasECpoint {
######################################################################
    my ($self) = @_;

    my $dbh = $self->dbh;

    my $schema = $self->schema;

    my $sth = $dbh->prepare("
         SELECT sample_no 
         FROM   $schema.dataset_sample
         WHERE  dataset_no = ?
         AND    is_on_graph = 'Y'
    ");
    $sth->execute($self->dataset_no);

    my $arrayRef = $sth->fetchall_arrayref();

    $sth->finish;

    if (@$arrayRef) { 
	return 1; 
    }
    else { 
	return; 
    }
   
}

######################################################################
sub isInDatasetGroup {
######################################################################
    my ($self) = @_;

    my $dbh = $self->dbh;

    my $schema = $self->schema;

    my $sth = $dbh->prepare("
         SELECT count(dataset_group_no)
         FROM   $schema.dataset_group
         WHERE  dataset_no = ?
    ");
    $sth->execute($self->dataset_no);

    my $count = $sth->fetchrow();

    $sth->finish;

    return $count;
   
}


######################################################################
sub _getParameterValue {
######################################################################
    my ($self, $parameter) = @_;

    my $dbh = $self->dbh;
    
    my $schema = $self->schema;

    my $sth = $dbh->prepare("
         SELECT parameter_value
         FROM   $schema.dataset_display
         WHERE  dataset_no = ?
         AND    display_parameter = ?
    ");
    $sth->execute($self->dataset_no, $parameter);

    my $paramVal = $sth->fetchrow;

    $sth->finish;

    return $paramVal;

}

####################################################################
sub getRefInfoArrayRef {
####################################################################
    my ($self) = @_; 

    my $dbh = $self->dbh;

    my $schema = $self->schema;

    my $sth = $dbh->prepare("
        SELECT R.reference_no, R.citation
        FROM   $schema.reflink RL, $schema.reference R
        WHERE  RL.primary_key = ?
        AND    RL.tab_name = 'DATASET'
        AND    RL.reference_no = R.reference_no
    ");
    $sth->execute($self->dataset_no);

    my $arrayRef = $sth->fetchall_arrayref();

    $sth->finish;

    return $arrayRef;

}

####################################################################
sub getFeatureNum {
####################################################################
    my ($self) = @_;
    
    my $dbh = $self->dbh;

    my $schema = $self->schema;

    my $sth = $dbh->prepare("
        SELECT count (unique SV.feature_no)
        FROM   $schema.sample_value SV, 
               $schema.dataset_sample DS
        WHERE  DS.dataset_no = ?
        AND    DS.sample_no = SV.sample_no
    ");
    $sth->execute($self->dataset_no);

    my $featNum = $sth->fetchrow;

    $sth->finish;

    return $featNum;
    
}

####################################################################
sub getNoDataFeatureArrayRef {
####################################################################
    my ($self) = @_;
    
    my $dbh = $self->dbh;

    my $schema = $self->schema;

    my $sth = $dbh->prepare("
        SELECT unique F.feature_name, L.locus_name
        FROM   $schema.feature F, $schema.locus L, 
               $schema.feature_type FT
        WHERE  F.locus_no = L.locus_no(+)
        AND    F.is_on_pmap = 'Y'
        AND    FT.feature_no = F.feature_no 
        AND    FT.feature_type = 'ORF'
        AND    F.feature_no not in 
               (select feature_no 
                from   $schema.feature_type
                where  feature_type = 'Merged'
                or     feature_type = 'Deleted'
               )
        AND    F.feature_no not in 
               (select SV.feature_no 
                from   $schema.sample_value SV,
                       $schema.dataset_sample DS
                where  DS.dataset_no = ?
                and    DS.sample_no = SV.sample_no
               )
    ");
    $sth->execute($self->dataset_no);

    my $arrayRef = $sth->fetchall_arrayref();

    $sth->finish;

    return $arrayRef;

}

####################################################################
sub GetExprValBYfeatureNo {
####################################################################
    my ($self, %args) = @_; 

    my $dbh = $args{'dbh'} || $self->_err_report('dbh');
    my $featureNo = $args{'feature_no'} || 
	$self->_err_report('feature_no');
    
    my $schema = $self->schema;

    my $sth = $dbh->prepare("
        SELECT sample_value
        FROM   $schema.sample_value
        WHERE  feature_no = ?
    ");

    $sth->execute($featureNo);

    my $arrayRef = $sth->fetchall_arrayref();

    $sth->finish;

    return $arrayRef;

}

####################################################################
sub GetDatasetArrayRefBYfeatureNo {
####################################################################
    my ($self, %args) = @_;

    my $dbh = $args{'dbh'} || $self->_err_report('dbh');
    my $featNo = $args{'feature_no'} || 
	$self->_err_report('feature_no');
    
    my $schema = $self->schema;

    my $sth = $dbh->prepare("
        SELECT unique D.dataset_name
        FROM   $schema.dataset D, $schema.dataset_sample DS
        WHERE  D.dataset_no = DS.dataset_no
        AND    DS.sample_no not in
               (select sample_no 
                from   $schema.sample_value
                where  feature_no = ?
               )
    ");
    $sth->execute($featNo);

    my $arrayRef = $sth->fetchall_arrayref();

    $sth->finish;

    return $arrayRef;
    
}

####################################################################
sub GetDatasetSampleBYfeatureNo {
####################################################################
    my ($self, %args) = @_; 

    my $dbh = $args{'dbh'} || $self->_err_report('dbh');
    my $featureNo = $args{'feature_no'} || 
	$self->_err_report('feature_no');
    
    my $schema = $self->schema;

    my $sth = $dbh->prepare("
        SELECT DD.parameter_value, DS.sample_name, SV.sample_value
        FROM   $schema.dataset_display DD, $schema.dataset_sample DS, 
               $schema.sample_value SV
        WHERE  DD.display_parameter = 'title'
        AND    DD.dataset_no = DS.dataset_no
        AND    DS.sample_no = SV.sample_no
        AND    SV.feature_no = ?
        ORDER BY DD.parameter_value, DS.sample_name
    ");

    $sth->execute($featureNo);

    my $arrayRef = $sth->fetchall_arrayref();

    $sth->finish;

    return $arrayRef;

}

####################################################################
sub GetRegulatorArrayRef {
####################################################################
    my ($self, %args) = @_; 

    my $dbh = $args{'dbh'} || $self->_err_report('dbh');
    my $datasetNo = $args{'dataset_no'} || 
	$self->_err_report('dataset_no');
    my $featureNo = $args{'feature_no'} || 
	$self->_err_report('feature_no');
    
    my $schema = $self->schema;

    my $sth = $dbh->prepare("
        SELECT F.feature_name
        FROM   $schema.feature F, $schema.regulator R,
               $schema.dataset_regulator DR,
               $schema.dataset_group DG, $schema.dg_feat DGF
        WHERE  DG.dataset_no = ?
        AND    DG.dataset_group_no = DGF.dataset_group_no
        AND    DGF.feature_no = ?
        AND    DG.dataset_group_no = DR.dataset_group_no
        AND    DR.regulator_no = R.regulator_no
        AND    R.feature_no = F.feature_no
        ORDER BY DR.regulator_order
    ");
    $sth->execute($datasetNo, $featureNo);

    my $arrayRef = $sth->fetchall_arrayref();

    $sth->finish;

    return $arrayRef;

}

####################################################################
sub RegulatorGifFile {
####################################################################
    my ($self, %args) = @_; 

    my $dbh = $args{'dbh'} || $self->_err_report('dbh');
    my $datasetNo = $args{'dataset_no'} || 
	$self->_err_report('dataset_no');
    my $featureNo = $args{'feature_no'} || 
	$self->_err_report('feature_no');
    
    my $schema = $self->schema;

    my $sth = $dbh->prepare("
        SELECT EF.file_path
        FROM   $schema.external_file EF, $schema.dataset_group DG,
               $schema.dataset_regulator DR, $schema.regulator R
        WHERE  DG.dataset_no = ?
        AND    DG.dataset_group_no = DR.dataset_group_no
        AND    DR.regulator_no = R.regulator_no
        AND    R.feature_no = ?
        AND    EF.tab_name = 'DATASET_GROUP'
        AND    EF.primary_key = DG.dataset_group_no
    ");
    $sth->execute($datasetNo, $featureNo);

    my $file = $sth->fetchrow;

    $sth->finish;

    return $file;

}


####################################################################
sub RegulatorModuleArrayRef {
####################################################################
    my ($self, %args) = @_; 

    my $dbh = $args{'dbh'} || $self->_err_report('dbh');
    my $datasetNo = $args{'dataset_no'} || 
	$self->_err_report('dataset_no');
    my $featureNo = $args{'feature_no'} || 
	$self->_err_report('feature_no');
    
    my $schema = $self->schema;

    my $sth = $dbh->prepare("
        SELECT DG.description
        FROM   $schema.dataset_group DG,
               $schema.dataset_regulator DR, $schema.regulator R
        WHERE  DG.dataset_no = ?
        AND    DG.dataset_group_no = DR.dataset_group_no
        AND    DR.regulator_no = R.regulator_no
        AND    R.feature_no = ?
    ");
    $sth->execute($datasetNo, $featureNo);

    my $arrayRef = $sth->fetchall_arrayref();

    $sth->finish;

    return $arrayRef;

}

####################################################################
sub MaxExprValBYdatasetNofeatureNo {
####################################################################
    my ($self, %args) = @_; 

    my $dbh = $args{'dbh'} || $self->_err_report('dbh');
    my $datasetNo = $args{'dataset_no'} || 
	$self->_err_report('dataset_no');
    my $featureNo = $args{'feature_no'} || 
	$self->_err_report('feature_no');
    
    my $schema = $self->schema;

    my $sth = $dbh->prepare("
        SELECT max(S.sample_value) 
        FROM   $schema.sample_value S, $schema.dataset_sample DS
        WHERE  DS.dataset_no = ?
        AND    DS.sample_no = S.sample_no
        AND    S.feature_no = ?
    ");
    $sth->execute($datasetNo, $featureNo);

    my $maxExprVal = $sth->fetchrow;

    $sth->finish;

    return $maxExprVal;

}

####################################################################
sub MinExprValBYdatasetNofeatureNo {
####################################################################
    my ($self, %args) = @_; 

    my $dbh = $args{'dbh'} || $self->_err_report('dbh');
    my $datasetNo = $args{'dataset_no'} || 
	$self->_err_report('dataset_no');
    my $featureNo = $args{'feature_no'} || 
	$self->_err_report('feature_no');
    
    my $schema = $self->schema;

    my $sth = $dbh->prepare("
        SELECT min(S.sample_value) 
        FROM   $schema.sample_value S, $schema.dataset_sample DS
        WHERE  DS.dataset_no = ?
        AND    DS.sample_no = S.sample_no
        AND    S.feature_no = ?
    ");
    $sth->execute($datasetNo, $featureNo);

    my $minExprVal = $sth->fetchrow;

    $sth->finish;

    return $minExprVal;

}

####################################################################
sub GetDatasetMaxExprChangeArrayRefBYfeatureNo {
####################################################################
    my ($self, %args) = @_; 

    my $dbh = $args{'dbh'} || $self->_err_report('dbh');
    my $featureNo = $args{'feature_no'} || 
	$self->_err_report('feature_no');
    
    my $schema = $self->schema;

    my $sth = $dbh->prepare("
        SELECT D.dataset_name, max(S.sample_value), 
               min(S.sample_value) 
        FROM   $schema.dataset D, $schema.sample_value S, 
               $schema.dataset_sample DS
        WHERE  D.dataset_no = DS.dataset_no
        AND    DS.sample_no = S.sample_no
        AND    S.feature_no = ?
        GROUP BY D.dataset_name
    ");
    $sth->execute($featureNo);

    my $arrayRef = $sth->fetchall_arrayref();

    $sth->finish;

    return $arrayRef;

}

####################################################################
sub GetGeneNameArrayRefBYdataset {
####################################################################
    my ($self, %args) = @_; 

    my $dbh = $args{'dbh'} || $self->_err_report('dbh');
    my $dataset = $args{'dataset_name'} || 
	$self->_err_report('dataset_name');
    
    my $schema = $self->schema;

    my $sth = $dbh->prepare("
        SELECT F.feature_name, max(S.sample_value), 
               min(S.sample_value) 
        FROM   $schema.feature F, $schema.dataset D, 
               $schema.sample_value S, $schema.dataset_sample DS
        WHERE  D.dataset_name = ?
        AND    D.dataset_no = DS.dataset_no
        AND    DS.sample_no = S.sample_no
        AND    S.feature_no = F.feature_no
        GROUP BY F.feature_name
    ");
    $sth->execute($dataset);

    my $arrayRef = $sth->fetchall_arrayref();

    $sth->finish;

    return $arrayRef;

}

####################################################################
sub GetDatasetInfoArrayRef {
####################################################################
    my ($self, %args) = @_;

    my $dbh = $args{'dbh'} || $self->_err_report('dbh');
   
    my $schema = $self->schema;

    my $sth = $dbh->prepare("
        SELECT D.dataset_no, D.dataset_name, D.source,
               DD.parameter_value, R.reference_no, 
               R.citation
        FROM   $schema.dataset D, $schema.dataset_display DD,
               $schema.reflink RL, $schema.reference R
        WHERE  D.dataset_no = DD.dataset_no
        AND    DD.display_parameter = 'title'
        AND    D.dataset_no = RL.primary_key
        AND    RL.tab_name = 'DATASET'
        AND    RL.reference_no = R.reference_no
        ORDER BY DD.parameter_value
    ");
    $sth->execute;

    my $arrayRef = $sth->fetchall_arrayref();

    $sth->finish;

    return $arrayRef;

}

######################################################################
sub GetSamplesArrayRef {
######################################################################
    my ($self, %args) = @_;

    my $dbh = $args{'dbh'} || $self->_err_report('dbh');
    my $dataset = $args{'dataset_name'} || $self->_err_report('dataset_name');

    my $schema = $self->schema;

    my $sth = $dbh->prepare("
        SELECT DS.sample_name, DS.sample_unit
        FROM   $schema.dataset_sample DS, $schema.dataset D
        WHERE  D.dataset_name = ?
        AND    D.dataset_no = DS.dataset_no
        ORDER BY DS.sample_order
    ");
    
    $sth->execute($dataset);

    my $arrayRef = $sth->fetchall_arrayref();

    $sth->finish;

    return $arrayRef;

}

######################################################################
sub GetSpotNumArrayRef {
######################################################################
    my ($self, %args) = @_;

    my $dbh = $args{'dbh'} || $self->_err_report('dbh');
    my $dataset = $args{'dataset_name'} || $self->_err_report('dataset_name');
    my $sampleValCutoff = $args{'sample_value_cutoff'};
    my $type = $args{'type'};

    my $schema = $self->schema;

    my $whereClause;
    if ($type =~ /^decreased/i) {
	if ($sampleValCutoff > 0) {
	    $whereClause = "AND S.sample_value <= -$sampleValCutoff";
	}
	elsif ($sampleValCutoff == 0) {
	    $whereClause = "AND S.sample_value < 0";
	}
    }
    elsif ($type =~ /^increased/i) {
	if ($sampleValCutoff > 0) {
	    $whereClause = "AND S.sample_value >= $sampleValCutoff";
	}
	elsif ($sampleValCutoff == 0) {
	    $whereClause = "AND S.sample_value > 0";
	}
    }
	
    my $sth = $dbh->prepare("
        SELECT DS.sample_name, count(S.sample_value)
        FROM   $schema.sample_value S, $schema.dataset_sample DS,
               $schema.dataset D
        WHERE  D.dataset_name = ?
        AND    D.dataset_no = DS.dataset_no 
        AND    DS.sample_no = S.sample_no
        $whereClause
        GROUP BY DS.sample_name
    ");
    $sth->execute($dataset);

    my $arrayRef = $sth->fetchall_arrayref();

    $sth->finish;

    return $arrayRef;

}

######################################################################
sub GetECpointArrayRef {
######################################################################
    my ($self, %args) = @_;

    my $dbh = $args{'dbh'} || $self->_err_report('dbh');
    my $dataset = $args{'dataset_name'} || $self->_err_report('dataset_name');
    my $featNm = $args{'feature_name'} || $self->_err_report('feature_name');
    
    $dataset = "\U$dataset";

    $featNm = "\U$featNm";

    my $schema = $self->schema;

    my $sth = $dbh->prepare("
        SELECT DS.sample_name, DS.sample_unit, SV.sample_value
        FROM   $schema.dataset_sample DS, $schema.dataset D, 
               $schema.sample_value SV, $schema.feature F
        WHERE  upper(D.dataset_name) = ?
        AND    D.dataset_no = DS.dataset_no
        AND    DS.is_on_graph = 'Y'
        AND    DS.sample_no = SV.sample_no
        AND    SV.feature_no = F.feature_no 
        AND    upper(F.feature_name) = ?
        ORDER BY DS.sample_order
    ");
    
    $sth->execute($dataset, $featNm);

    my $arrayRef = $sth->fetchall_arrayref();

    $sth->finish;

    return $arrayRef;

}


######################################################################
sub _err_report {
######################################################################
    my ($self, $args) = @_;

    my ($file, $line, $method) = (caller(1))[1..3];

    print "The argument '$args' must be passed to '$method' method.",br;

    print "Please add this argument to line $line in $file.",br; 

    exit;

}

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

=pod

=head1 Name

Dataset.pm

=head1 Description

This perl object (Dataset.pm) acts as container for Dataset 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 Dataset Object

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

my $obj = Dataset->new(dbh=>$dbh,
		       dataset_no=>$datasetNo);


OR


my $obj = Dataset->new(dbh=>$dbh,
		       dataset_name=>$datasetNm);


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 $source = $obj->source; 


my $dataset_no = $obj->dataset_no;


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

See valid columns in dataset table:

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

=head1 getRow method

Usage:

my $row = $obj->getRow;

This method returns a tab-delimited row from dataset 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