#!/usr/bin/perl
package CuratorNote;

# Author:           Shuai Weng                                     
# Date:             June 2000  
# Description:      This package contains all dictyBase curator note curation  
#                   related subroutines for SDEV and dictyBase databases
#
use strict;
use DBI;
use CGI qw/:standard :html3/;
use LWP::UserAgent;
use lib "/usr/local/dicty/www_dictybase/db/lib/common"; 
use Login  qw (ConnectToDatabase);


use Exporter();
use vars qw ($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);

$VERSION = 1.00;
@ISA = qw( Exporter );

%EXPORT_TAGS = ( 
		getInfo=> [qw/getNoteBYnoteNo getIdListBYnoteNo getLnameBYcolleagueNo getNoteNosBYid getTableNmPriKeyListBYnoteNo/],
		updateInfo => [qw/insertNote4idList updateNote4idList deleteNoteLinkBYtabId/]
);

#  add all subs in getInfo and other tags to @EXPORT_OK
Exporter::export_ok_tags('getInfo');
Exporter::export_ok_tags('updateInfo');
my $ua = new LWP::UserAgent;

my $query = new CGI;

###########################################################################
###### getNoteBYnoteNo() is used to get curator note and its is_public value
###### for the specified note_no
###### Usage: $noteIsPublic = &getNoteBYnoteNo($dbh, $noteNo);
###### tab is the delimiter for note and its is_public 
###########################################################################
sub getNoteBYnoteNo {
###########################################################################
    my ($dbh, $noteNo) = @_;
    my ($sth, $noteIsPublic);
    $sth = $dbh->prepare("
         SELECT note, is_public
         FROM   CGM_DDB.curator_note
         WHERE  curator_note_no = ?
    ");
    if ($sth->execute($noteNo)) {
	while(my ($note, $isPublic) = $sth->fetchrow()) {
	    $noteIsPublic = "${note}\t${isPublic}";
	}
    }
    return $noteIsPublic;
}

###########################################################################
###### getIdListBYnoteNo() is used to get a list of the table name and 
###### its primary key pair
###### Usage: $idList = &getIdListBYnoteNo($dbh, $noteNo);
###### Example idList : Locus,6 Colleague,55 Feature,6666
###########################################################################
sub getIdListBYnoteNo {
###########################################################################
    my ($dbh, $noteNo) = @_;
    my ($sth, $idList);

    ### colleague, coll_cn
    $sth = $dbh->prepare( "
         SELECT  colleague_no
         FROM    CGM_DDB.coll_cn
         WHERE   curator_note_no = ?
    ");
    if ($sth->execute($noteNo)) {
	while(my ($id) = $sth->fetchrow()) {
	    $idList = "${idList}Colleague,$id ";
	}
    }
    
    ### locus, locus_cn
    $sth = $dbh->prepare( "
         SELECT  locus_no
         FROM    CGM_DDB.locus_cn
         WHERE   curator_note_no = ?
    ");
    if ($sth->execute($noteNo)) {
	while(my ($id) = $sth->fetchrow()) {
	    $idList = "${idList}Locus,$id ";
	}
    }

    ### feature, feat_cn
    $sth = $dbh->prepare( "
         SELECT  feature_no
         FROM    CGM_DDB.feat_cn
         WHERE   curator_note_no = ?
    ");
    if ($sth->execute($noteNo)) {
	while(my ($id) = $sth->fetchrow()) {
	    $idList = "${idList}Feature,$id ";
	}
    }

    ### clone
    $sth = $dbh->prepare( "
         SELECT  clone_no
         FROM    CGM_DDB.clone
         WHERE   curator_note_no = ?
    ");
    if ($sth->execute($noteNo)) {
	while(my ($id) = $sth->fetchrow()) {
	    $idList = "${idList}Clone,$id ";
	}
    }

    ### dictyBaseid
    $sth = $dbh->prepare( "
         SELECT  dictyBaseid_no
         FROM    CGM_DDB.dictyBaseid
         WHERE   curator_note_no = ?
    ");
    if ($sth->execute($noteNo)) {
	while(my ($id) = $sth->fetchrow()) {
	    $idList = "${idList}dictyBaseid,$id ";
	}
    }

    $idList =~ s/ *$//;

    return $idList;

}


###########################################################################
sub getLnameBYcolleagueNo {
###########################################################################
    my ($dbh, $collNo) = @_;
    my $sth = $dbh->prepare("
        SELECT  last_name
        FROM    CGM_DDB.colleague
        WHERE   colleague_no = ?
    ");
    my $lname;
    if ($sth->execute($collNo)) {
	while(my($name) = $sth->fetchrow()) {
	    $lname = $name;
	}
    }
    return $lname;
}

###########################################################################
sub getNoteNosBYid {
###########################################################################
    my ($dbh, $table, $id) = @_;
    my ($sth, $noteNos);
    if ($table =~ /colleague/i) { ### colleague, coll_cn
	$sth = $dbh->prepare("
              SELECT curator_note_no
              FROM   CGM_DDB.coll_cn
              WHERE  colleague_no = ?
        ");
    }
    elsif ($table =~ /locus/i) { ### locus, locus_cn
	$sth = $dbh->prepare("
              SELECT curator_note_no
              FROM   CGM_DDB.locus_cn
              WHERE  locus_no = ?
        ");
    }
    elsif ($table =~ /feature/i) { ### feature, feat_cn
	$sth = $dbh->prepare("
              SELECT curator_note_no
              FROM   CGM_DDB.feat_cn
              WHERE  feature_no = ?
        ");
    }
    elsif ($table =~ /clone/i) { ### clone
	$sth = $dbh->prepare("
              SELECT curator_note_no
              FROM   CGM_DDB.clone
              WHERE  clone_no = ?
        ");
    }
    elsif ($table =~ /dictyBaseid/i) { ### dictyBaseid
	$sth = $dbh->prepare("
              SELECT curator_note_no
              FROM   CGM_DDB.dictyBaseid
              WHERE  dictyBaseid_no = ?
        ");
    }
    
    if ($sth->execute($id)) {
	while(my ($number) = $sth->fetchrow()) {
	    $noteNos = "${noteNos}${number}:";
	}
    }
    $noteNos =~ s/:$//;
    return $noteNos;

}


##########################################################################
###### getTableNmPriKeyListBYnoteNo() is used to get Table Name(s) & their
###### primary key(s) for the specified note. (eg. Locus,10 Colleague,15)
###### Usage: $idList = &getTableNmPriKeyListBYnoteNo($dbh, $noteNo);
###### The list is separated by space. Each pair of the table name and 
###### its parimary key is separated by comma (,);
#########################################################################
sub getTableNmPriKeyListBYnoteNo {
#########################################################################
    my ($dbh, $noteNo) = @_;
    my ($idList, $sth);
    $sth = $dbh->prepare("
          SELECT feature_no
          FROM   CGM_DDB.feat_cn
          WHERE  curator_note_no = ?
    ");
    if ($sth->execute($noteNo)) {
	while(my ($number) = $sth->fetchrow()) {
	    if ($idList) {
		$idList = "${idList} Feature,$number";
	    }
	    else {
		$idList = "Feature,$number";
	    }

	}
    }
    $sth = $dbh->prepare("
         SELECT locus_no
         FROM   CGM_DDB.locus_cn
         WHERE  curator_note_no = ?
    ");
    if ($sth->execute($noteNo)) {
	while(my ($number) = $sth->fetchrow()) {
	    if ($idList) {
		$idList = "${idList} Locus,$number";
	    }
	    else {
		$idList = "Locus,$number";
	    }
	}
    }
    $sth = $dbh->prepare("
        SELECT colleague_no
        FROM   CGM_DDB.coll_cn
        WHERE  curator_note_no = ?
    ");
    if ($sth->execute($noteNo)) {
	while(my ($number) = $sth->fetchrow()) {
	    if ($idList) {
		$idList = "${idList} Colleague,$number";
	    }
	    else {
		$idList = "Colleague,$number";
	    }
	}
    }
    return $idList;
}


###########################################################################
###### insertNote4idList() is used to insert curator note info into 
###### curator_note table and add related into into linking tables 
###### usage : &insertNote4idList($dbh, $user, $note, $isPublic, $idList);  
###########################################################################
sub insertNote4idList {
###########################################################################
    my ($dbh, $user, $note, $isPublic, $idList) = @_;
    $note =~ s/[\t\n\r\f]/ /g;
    $note =~ s/ +/ /g;
    $note =~ s/ *$//;
    $note =~ s/^ *//;
    $note =~ s/\'/\'\'/g;
    if ($isPublic =~ /y/i) {
	$isPublic = "Y";
    }
    else {
	$isPublic = "N";
    }
    my $sth = $dbh->prepare("
       INSERT INTO CGM_DDB.curator_note(curator_note_no, note, is_public, 
                                     date_created, created_by)
       VALUES(CGM_DDB.cnno_seq.nextval, ?, ?, SYSDATE, ?)
    ");
    $sth->execute($note, $isPublic, $user);
    $dbh->commit;
    $sth = $dbh->prepare("
          SELECT CGM_DDB.cnno_seq.currval FROM dual
    ");
    my $noteNo;
    if ($sth->execute) {
	while(my ($number) = $sth->fetchrow()) {
	    $noteNo = $number;
	}
    }
    my @TabNm_id = split(/ /, $idList);
    foreach my $tabNmId (@TabNm_id) {
	my ($TabNm, $id) = split(/\,/, $tabNmId);
	my $sth;
	if ($TabNm =~ /Colleague/i) { ### colleague, coll_cn
	    $sth = $dbh->prepare("
                INSERT INTO CGM_DDB.coll_cn(curator_note_no, colleague_no)
                VALUES(CGM_DDB.cnno_seq.currval, ?)
            ");
	    $sth->execute($id);
	}
	elsif ($TabNm =~ /Locus/i) { ### locus, locus_cn
	    $sth = $dbh->prepare("
                INSERT INTO CGM_DDB.locus_cn(curator_note_no, locus_no)
                VALUES(CGM_DDB.cnno_seq.currval, ?)
            ");
	    $sth->execute($id);
	}
	elsif ($TabNm =~ /Feature/i) { ### feature, feat_cn
	    $sth = $dbh->prepare("
                INSERT INTO CGM_DDB.feat_cn(curator_note_no, feature_no)
                VALUES(CGM_DDB.cnno_seq.currval, ?)
            ");
	    $sth->execute($id);
	}
	elsif ($TabNm =~ /Clone/i) { ### clone
	    $sth = $dbh->prepare("
                UPDATE CGM_DDB.clone
                SET    curator_note_no = ? 
                WHERE  clone_no = ?
            ");
	    $sth->execute($noteNo, $id);
	}
	elsif ($TabNm =~ /dictyBaseid/i) { ### dictyBaseid
	    $sth = $dbh->prepare("
                UPDATE CGM_DDB.dictyBaseid
                SET    curator_note_no = ? 
                WHERE  dictyBaseid_no = ?
            ");
	    $sth->execute($noteNo, $id);
	}
	$dbh->commit;
    }

}

###########################################################################
###### updateNote4idList() is used to update curator note info in 
###### curator_note table and add or delete related info into linking tables 
###### usage : &updateNote4idList($dbh, $noteNo, $note, $isPublic, $idList);  
###########################################################################
sub updateNote4idList {
###########################################################################
    my ($dbh, $noteNo, $note, $isPublic, $idList) = @_;
    $note =~ s/[\t\n\r\f]/ /g;
    $note =~ s/ +/ /g;
    $note =~ s/ *$//;
    $note =~ s/^ *//;
#    $note =~ s/\'/\'\'/g;
    if ($isPublic =~ /y/i) {
	$isPublic = "Y";
    }
    else {
	$isPublic = "N";
    }
    my $idListInDB = &getIdListBYnoteNo($dbh, $noteNo);
    $note =~ s/\'/\'\'/g;
    my $sth = $dbh->prepare("
        UPDATE CGM_DDB.curator_note
        SET    note = ?, 
               is_public= ?
        WHERE  curator_note_no = ?
    ");
    $sth->execute($note, $isPublic, $noteNo);
    $dbh->commit;

    if ($idList ne $idListInDB) {
	my @newIdList = split(/ /, $idList);
	my @oldIdList = split(/ /, $idListInDB);
	my (%foundInOld, %foundInNew);
	foreach my $tabId (@newIdList) {
	    $foundInNew{"\U$tabId"}++;
	}
	foreach my $tabId (@oldIdList) {
	    if (!$foundInNew{"\U$tabId"}) {
		my($table, $id) = split(/\,/, $tabId);
		my $sth;
		if ($table =~ /locus/i) { ### locus_cn
		    $sth = $dbh->prepare("
                         DELETE from CGM_DDB.locus_cn
                         WHERE  locus_no = ?
                         AND    curator_note_no = ?
                    ");
		    $sth->execute($id, $noteNo);
		}
		elsif ($table =~ /feature/i) { ### feat_cn
		    $sth = $dbh->prepare("
                         DELETE from CGM_DDB.feat_cn
                         WHERE  feature_no = ?
                         AND    curator_note_no = ?
                    ");
		    $sth->execute($id, $noteNo);
		}
		elsif ($table =~ /colleague/i) { ### coll_cn
		    $sth = $dbh->prepare("
                         DELETE from CGM_DDB.coll_cn
                         WHERE  colleague_no = ?
                         AND    curator_note_no = ?
                    ");
		    $sth->execute($id, $noteNo);
		}
		elsif ($table =~ /clone/i) { ### clone
		    $sth = $dbh->prepare("
                         UPDATE CGM_DDB.clone
                         SET    curator_note_no = NULL
                         WHERE  clone_no = ?
                    ");
		    $sth->execute($id);
		}
		elsif ($table =~ /dictyBaseid/i) { ### dictyBaseid
		    $sth = $dbh->prepare("
                         UPDATE CGM_DDB.dictyBaseid
                         SET    curator_note_no = NULL
                         WHERE  dictyBaseid_no = ?
                    ");
		    $sth->execute($id);
		}
		$dbh->commit;
	    }
	    $foundInOld{"\U$tabId"}++;
	}
	foreach my $tabId (@newIdList) {
	    if (!$foundInOld{"\U$tabId"}) {
		my($table, $id) = split(/\,/, $tabId);
		my $sth;
		if ($table =~ /locus/i) { ### locus_cn
		   $sth = $dbh->prepare("
                        INSERT INTO CGM_DDB.locus_cn(curator_note_no, locus_no)
                        VALUES(?, ?)
                   "); 
		   $sth->execute($noteNo, $id);
		}
		elsif ($table =~ /feature/i) { ### feat_cn
		   $sth = $dbh->prepare("
                        INSERT INTO CGM_DDB.feat_cn(curator_note_no, feature_no)
                        VALUES(?, ?)
                   ");
		   $sth->execute($noteNo, $id);
		}
		elsif ($table =~ /colleague/i) { ### coll_cn
		   $sth = $dbh->prepare("
                        INSERT INTO CGM_DDB.coll_cn(curator_note_no, colleague_no)
                        VALUES(?, ?)
                   ");
		   $sth->execute($noteNo, $id);
		}
		elsif ($table =~ /clone/i) { ### clone
		   $sth = $dbh->prepare("
                        UPDATE CGM_DDB.clone
                        SET    curator_note_no = ?
                        WHERE  clone_no = ?
                   ");
		   $sth->execute($noteNo, $id);
		}
		elsif ($table =~ /dictyBaseid/i) { ### dictyBaseid
		   $sth = $dbh->prepare("
                        UPDATE CGM_DDB.dictyBaseid
                        SET    curator_note_no = ?
                        WHERE  dictyBaseid_no = ?
                   ");
		   $sth->execute($noteNo, $id);
		}

		$dbh->commit;
	    }
	}
    }
}

##########################################################################
sub deleteNoteLinkBYtabId {
##########################################################################
    my ($dbh, $noteNo, $table, $id) = @_;
    my $err = 0;
    my $sth;
    if ($table =~ /locus/i) { ### locus_cn
	$sth = $dbh->prepare("
            DELETE from CGM_DDB.locus_cn
            WHERE  curator_note_no = ?
            AND    locus_no = ?
        ");
	$sth->execute($noteNo, $id);
    }
    elsif ($table =~ /feature/i) { ### feat_no
	$sth = $dbh->prepare("
            DELETE from CGM_DDB.feat_cn
            WHERE  curator_note_no = ?
            AND    feature_no = ?
        ");
	$sth->execute($noteNo, $id);
    }
    elsif ($table =~ /colleague/i) { ### coll_cn
	$sth = $dbh->prepare("
            DELETE from CGM_DDB.coll_cn
            WHERE  curator_note_no = ?
            AND    colleague_no = ?
        ");
	$sth->execute($noteNo, $id);
    }
    elsif ($table =~ /clone/i) { ### clone
	$sth = $dbh->prepare("
            UPDATE CGM_DDB.clone
            SET    curator_note_no = NULL
            WHERE  clone_no = ?
        ");
	$sth->execute($id);
    }
    elsif ($table =~ /dictyBaseid/i) { ### dictyBaseid
	$sth = $dbh->prepare("
            UPDATE CGM_DDB.dictyBaseid
            SET    curator_note_no = NULL
            WHERE  dictyBaseid_no = ?
        ");
	$sth->execute($id);
    }
    else {
	$err = 1;
	print "We do not have any linking table to link $table with curator note or $table table is not found in our database.<br>";
    }
    $dbh->commit;
    return $err;
}


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