package Keys;

# Author:      Gail Binkley
# Date:        Sept 1, 2000
# Description: Create hashes of primary keys by values (and their reverse) 
#              for the major database tables.  Call subroutines by reference.

use strict;

use vars qw (@ISA @EXPORT_OK);
use Exporter;
@ISA = ('Exporter');
@EXPORT_OK = qw( GetLocusNoByName GetNameByLocusNo GetFeatureNoByName GetNameByFeatureNo GetPhenotypeNoByName GetColleagueNoByName GetAuthorNoByName GetJournalNoByAbbrev GetReferenceNoByName GetUrlNoByUrl );


#########################################################################
sub GetLocusNoByName {
#########################################################################
# Create hash of locus_no by locus_name

    my ($dbh, $LocusNoByNameRef) = @_;

    my $query = "SELECT locus_no, locus_name
                   FROM CGM_DDB.locus";

    my $sth = $dbh->prepare($query);
    $sth->execute();

    my ($locus_no, $locus_name);
    
    while (($locus_no, $locus_name) = $sth->fetchrow_array){

	$$LocusNoByNameRef{$locus_name} = $locus_no;

    }
    $sth->finish();
}

#########################################################################
sub GetNameByLocusNo {
#########################################################################
# Create hash of locus_name by locus_no

    my ($dbh, $NameByLocusNoRef) = @_;

    my $query = "SELECT locus_no, locus_name
                   FROM CGM_DDB.locus";

    my $sth = $dbh->prepare($query);
    $sth->execute();

    my ($locus_no, $locus_name);
    
    while (($locus_no, $locus_name) = $sth->fetchrow_array){

	$$NameByLocusNoRef{$locus_no} = $locus_name;

    }
    $sth->finish();
}


####################################################################
sub GetFeatureNoByName {
####################################################################
# Create hash of feature_no by feature_name

    my ($dbh, $FeatureNoByNameRef)  = @_;

    my $query = "SELECT feature_no, feature_name
                   FROM CGM_DDB.feature";

    my $sth = $dbh->prepare($query);
    $sth->execute();

    my ($feature_no, $feature_name);
    
    while (($feature_no, $feature_name) = $sth->fetchrow_array){

	$$FeatureNoByNameRef{$feature_name} = $feature_no;

    }
    $sth->finish();	
}


#########################################################################
sub GetNameByFeatureNo {
#########################################################################
# Create hash of feature_name by feature_no

    my ($dbh, $NameByFeatureNoRef) = @_;

    my $query = "SELECT feature_no, feature_name
                   FROM CGM_DDB.feature";

    my $sth = $dbh->prepare($query);
    $sth->execute();

    my ($feature_no, $feature_name);
    
    while (($feature_no, $feature_name) = $sth->fetchrow_array){

	$$NameByFeatureNoRef{$feature_no} = $feature_name;

    }
    $sth->finish();
}


####################################################################
sub GetPhenotypeNoByName {
####################################################################
# Create hash of phenotype_no by phenotype

    my ($dbh, $PhenotypeNoByNameRef)  = @_;

    my $query = "SELECT phenotype_no, phenotype
                   FROM CGM_DDB.phenotype";

    my $sth = $dbh->prepare($query);
    $sth->execute();

    my ($phenotype_no, $phenotype);
    
    while (($phenotype_no, $phenotype) = $sth->fetchrow_array){

	$$PhenotypeNoByNameRef{$phenotype} = $phenotype_no;

    }
    $sth->finish();	
}


####################################################################
sub GetColleagueNoByName {
####################################################################
# Create hash of colleague_no by full colleague name 
# (full name = last_name, first_name, suffix)

    my ($dbh, $ColleagueNoByNameRef)  = @_;

    my $query = "SELECT colleague_no, last_name, first_name, suffix 
                   FROM CGM_DDB.colleague";

    my $sth = $dbh->prepare($query);

    $sth->execute();

    my ($colleague_no, $last_name, $first_name, $suffix, $fullname);


    while (($colleague_no, $last_name, $first_name, $suffix)= $sth->fetchrow_array){

        if ($suffix) {

	    $fullname = $last_name . ", " . $first_name . ", " . $suffix;
	    $$ColleagueNoByNameRef{$fullname} = $colleague_no;

        } else {

	    $fullname = $last_name . ", " . $first_name;
	    $$ColleagueNoByNameRef{$fullname} = $colleague_no;
        }
    }	
    $sth->finish();
}


#########################################################################
sub GetReferenceNoByName {
#########################################################################
# Create hash of reference_nos by PubMed ID

    my ($dbh, $ReferenceNoByNameRef) = @_;

    my $query = "SELECT reference_no, pubmed
                   FROM CGM_DDB.reference";

    my $sth = $dbh->prepare($query);
    $sth->execute();
    
    my ($reference_no, $pubmed);
    
    while (($reference_no, $pubmed) = $sth->fetchrow_array){

	$$ReferenceNoByNameRef{$pubmed} = $reference_no;

    }
    $sth->finish();
}



#########################################################################
sub GetAuthorNoByName {
#########################################################################
# Create hash of author_nos by author_name

    my ($dbh, $AuthorNoByNameRef) = @_;

    my $query = "SELECT author_no, author_name
                   FROM CGM_DDB.author";

    my $sth = $dbh->prepare($query);
    $sth->execute();

    my ($author_no, $author_name);
    
    while (($author_no, $author_name) = $sth->fetchrow_array){

	$$AuthorNoByNameRef{$author_name} = $author_no;

    }
    $sth->finish();
}


#########################################################################
sub GetJournalNoByAbbrev {
#########################################################################
# Create hash of journal_nos by abbreviation

    my ($dbh, $JournalNoByAbbrevRef) = @_;

    my $query = "SELECT journal_no, abbreviation
                   FROM CGM_DDB.journal";

    my $sth = $dbh->prepare($query);
    $sth->execute();

    my ($journal_no, $abbreviation);
    
    while (($journal_no, $abbreviation) = $sth->fetchrow_array){

	$$JournalNoByAbbrevRef{$abbreviation} = $journal_no;

    }
    $sth->finish();
}

#########################################################################
sub GetUrlNoByUrl {
#########################################################################
# Create hash of url_nos by url

    my ($dbh, $UrlNoByUrlRef) = @_;

    my $query = "SELECT url_no, url
                   FROM CGM_DDB.url";

    my $sth = $dbh->prepare($query);
    $sth->execute();

    my ($url_no, $url);
    
    while (($url_no, $url) = $sth->fetchrow_array){

	$$UrlNoByUrlRef{$url} = $url_no;

    }
    $sth->finish();
}