#!/usr/bin/perl
package Curator_note;

#####################################################################
# Author : Shuai Weng
# Date   : July 2001
#
# See documentation for the usage details.
#
# http:///usr/local/dicty/www_dictybase/db/lib/html/dictyBase/programmer/Curator_note.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 = (curator_note_no=>undef,
                        'note:is_public'=>undef);


####################################################################
sub getTableNmPrikeyList {
####################################################################
    my ($self) = @_;
    my $schema = $self->schema;
    my ($idList, $sth);
    ####### for feature
    $sth = $self->dbh->prepare("
          SELECT feature_no
          FROM   $schema.feat_cn
          WHERE  curator_note_no = ?
    ");
    $sth->execute($self->curator_note_no);
    while(my ($id) = $sth->fetchrow()) {
        if ($idList) { $idList .= " "; }
        $idList .= "Feature,$id";
    }
    $sth->finish;
    ####### for locus
    $sth = $self->dbh->prepare("
         SELECT locus_no
         FROM   $schema.locus_cn
         WHERE  curator_note_no = ?
    ");
    $sth->execute($self->curator_note_no);
    while(my ($id) = $sth->fetchrow()) {
        if ($idList) { $idList .= " "; }
        $idList .= "Locus,$id";
    }
    $sth->finish;
    ####### for colleague
    $sth = $self->dbh->prepare("
        SELECT colleague_no
        FROM   $schema.coll_cn
        WHERE  curator_note_no = ?
    ");
    $sth->execute($self->curator_note_no);
    while(my ($id) = $sth->fetchrow()) {
        if ($idList) { $idList .= " "; }
        $idList .= "Colleague,$id";
    }
    $sth->finish;
    ####### for dictyBaseid
    $sth = $self->dbh->prepare("
        SELECT dictyBaseid_no
        FROM   $schema.dictyBaseid
        WHERE  curator_note_no = ?
    ");
    $sth->execute($self->curator_note_no);
    while(my ($id) = $sth->fetchrow()) {
        if ($idList) { $idList .= " "; }
        $idList .= "dictyBaseid,$id";
    }
    $sth->finish;
    return $idList;
}

####################################################################
sub InsertCuratorNoteLinkInfo {
####################################################################
    my ($self, %args) = @_;
    my $schema = $self->schema;
    my $dbh = $args{'dbh'} || die "A database handle must be passed into InsertCuratorNoteLinkInfo method.";
    my $idList = $args{'idList'};
    my $noteNo = $args{'curator_note_no'};
    my @idList = split(/ /, $idList);
    foreach my $tabNmId (@idList) {
        my ($tabNm, $id) = split(/,/, $tabNmId);
        my $NoNm;
        if ($tabNm =~ /Locus/i) {
            $tabNm = "locus_cn";
            $NoNm = "locus_no";
        }
        elsif ($tabNm =~ /feature/i) {
            $tabNm = "feat_cn";
            $NoNm = "feature_no";
        }
        elsif ($tabNm =~ /colleague/i) {
            $tabNm = "coll_cn";
            $NoNm = "colleague_no";
        }
        elsif ($tabNm =~ /dictyBaseid/i) {
            $tabNm = "dictyBaseid";
            $NoNm = "dictyBaseid_no";
        }
        else {
            print "Unknown curator_note linking table for '$tabNm'";
            next ;
        }
        my ($sth, $stmt);
        if ($tabNm =~ /dictyBaseid/i) {
            $stmt = "UPDATE $schema.$tabNm set curator_note_no = ? where $NoNm = ?";
        }
        else {
            $stmt = "INSERT INTO $schema.$tabNm(curator_note_no, $NoNm)VALUES(?,?)";
        }
        $sth = $dbh->prepare($stmt);
        $sth->execute($noteNo, $id);
        $sth->finish;
    }
}

####################################################################
sub DeleteCuratorNoteLinkInfo {
####################################################################
    my ($self, %args) = @_;
    my $schema = $self->schema;
    my $dbh = $args{'dbh'} || die "A database handle must be passed into DeleteCuratorNoteLinkInfo method.";
    my $idList = $args{'idList'};
    my $noteNo = $args{'curator_note_no'};

    my @idList = split(/ /, $idList);
    foreach my $tabNmId (@idList) {
        my ($tabNm, $id) = split(/,/, $tabNmId);
        my $NoNm;
        if ($tabNm =~ /Locus/i) {
            $tabNm = "locus_cn";
            $NoNm = "locus_no";
        }
        elsif ($tabNm =~ /feature/i) {
            $tabNm = "feat_cn";
            $NoNm = "feature_no";
        }
        elsif ($tabNm =~ /colleague/i) {
            $tabNm = "coll_cn";
            $NoNm = "colleague_no";
        }
        elsif ($tabNm =~ /dictyBaseid/i) {
            $tabNm = "dictyBaseid";
            $NoNm = "dictyBaseid_no";
        }
        else {
            print "Unknown curator_note linking table for '$tabNm'";
            next ;
        }
        my $stmt;
        if ($tabNm =~ /^dictyBaseid/i) {
            $stmt = "UPDATE $schema.$tabNm set curator_note_no = ? where $NoNm = ?";
            $noteNo = "";
        }
        else {
            $stmt = "DELETE from $schema.$tabNm
                     WHERE  curator_note_no = ?
                     AND    $NoNm = ?";
        }
        my $sth = $dbh->prepare($stmt);
        $sth->execute($noteNo, $id);
        $sth->finish;
    }
}

####################################################################
sub GetCuratorNoteNosBYid {
####################################################################
    my ($self, %args) = @_;
    my $schema = $self->schema;
    my $dbh = $args{'dbh'} || die "A database handle must be passed into GetCuratorNoteNosBYid method.";
    my $table = $args{'tabNm'};
    my $id = $args{'id'};
    my $sth;
    if ($table =~ /colleague/i) { ### colleague, coll_cn
        $sth = $dbh->prepare("
              SELECT curator_note_no
              FROM   $schema.coll_cn
              WHERE  colleague_no = ?
        ");
    }
    elsif ($table =~ /locus/i) { ### locus, locus_cn
        $sth = $dbh->prepare("
              SELECT curator_note_no
              FROM   $schema.locus_cn
              WHERE  locus_no = ?
        ");
    }
    elsif ($table =~ /feature/i) { ### feature, feat_cn
        $sth = $dbh->prepare("
              SELECT curator_note_no
              FROM   $schema.feat_cn
              WHERE  feature_no = ?
        ");
    }
    elsif ($table =~ /dictyBaseid/i) { ### dictyBaseid
        $sth = $dbh->prepare("
              SELECT curator_note_no
              FROM   $schema.dictyBaseid
              WHERE  dictyBaseid_no = ?
        ");
    }
    $sth->execute($id);
    my $noteNos;
    while(my ($number) = $sth->fetchrow()) {
         $noteNos .= $number.":";
    }
    $noteNos =~ s/:$//;
    $sth->finish();
    return $noteNos;
}

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

}


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

=pod

=head1 Name

Curator_note.pm

=head1 Description

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

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

my $Obj = Curator_note->new(dbh=>$dbh,
                            curator_note_no=>$noteNo);


OR


my $Obj = Curator_note->new(dbh=>$dbh,
                            note=>$note,
                            is_public=>$isPublic);


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 $note = $Obj->note;


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


See valid columns in curator_note table:

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

You may use following methods to retrieve related info from other tables or manipulate related info in database.


=head1 getTableNmPrikeyList

Usage:

my $idList = $Obj->getTableNmPrikeyList;


This method returns table_names and their primary_keys list (i.e., Colleague,10 Locus,555 Feature,88) associated with this specified note.


=head1 getRow method

Usage:

my $row = $Obj->getRow;

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

=head1 InsertCuratorNoteLinkInfo

Usage :

eval {

    Curator_note->InsertCuratorNoteLinkInfo(dbh=>$dbh,
                                        curator_note_no=>$noteNo,
                                        idList=>$idList);


};


This method will insert info into various linking tables based on the idList (i.e., Colleague,10 Locus,555 Feature,88) and $noteNo.


=head1 DeleteCuratorNoteLinkInfo

Usage:

eval {

    Curator_note->DeleteCuratorNoteLinkInfo(dbh=>$dbh,
                                        curator_note_no=>$noteNo,
                                        idList=>$idList);


};


This method will delete info from various linking tables based on the $idList (i.e., Colleague,10 Locus,555 Feature,88) and $noteNo.


=head1 GetCuratorNoteNosBYid


Usage:

my $noteNoList = Curator_note->GetCuratorNoteNosBYid(dbh=>$dbh,
                                                     tabNm=>$tabNm,
                                                     id=>$id);


e.g.,


my $noteNoList = Curator_note->GetCuratorNoteNosBYid(dbh=>$dbh,
                                                     tabNm=>'Colleague',
                                                     id=>10);


This method will return curator_note_no list (colon ':' delimited) for the
specified table name and its primary_key.


=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