#!/usr/bin/perl
package RefCuration;
#######################################################################
##### Author : Shuai Weng
##### Date : June 2001
##### Description : This package contains all necessary methods for
##### dictyBase curators to search, update or delete
##### reference info or associate reference info with
##### other data in oracle database.
#####
#######################################################################
use strict;
use DBI;
use CGI qw/:all :html3/;
use lib "/usr/local/dicty/www_dictybase/db/lib/common/";
use Login qw (ConnectToDatabase);
use TextUtil qw( DeleteUnwantedChar );
use lib "/usr/local/dicty/www_dictybase/db/lib/dictyBase";
use dictyBaseCentralMod qw(:formatPage :getInfo);
use lib "/usr/local/dicty/www_dictybase/db/lib/dictyBase/curation";
use printReferenceMod qw (:formatSub);
use lib "/usr/local/dicty/www_dictybase/db/lib/dictyBase/Objects";
use ConfigURLdictyBase;
use Reference;
use Reflink;
use Abstract;
use Journal;
use Author;
use Author_editor;
use Publication_type;
#######################################################################
#################### global variables #################################
#######################################################################
my $dbh;
my $dblink;
my $configUrl;
#######################################################################
sub new { ############ constructor ###############################
#######################################################################
my ($self, %args) = @_;
$self = {};
bless $self;
$self->{'_database'} = $args{'database'};
$self->{'_help'} = $args{'help'};
$self->{'_title'} = defined($args{'title'}) ?
$args{'title'} : "Reference Curation Page";
$self->{'_user'} = $args{'user'};
$dbh = &ConnectToDatabase($self->database);
return $self;
}
sub help { $_[0]->{_help} }
sub database { $_[0]->{_database} }
sub title { $_[0]->{_title} }
sub user { $_[0]->{_user} }
######################################################################
sub DESTROY { ############ destructor ##############################
######################################################################
if (defined $dbh) {
$dbh->disconnect;
}
}
######################################################################
sub start{
######################################################################
my ($self) = @_;
$configUrl = ConfigURLdictyBase->new;
$dblink = $configUrl->dblink($self->database);
if (!$self->user) {
print "location: ", $configUrl->dictyBaseCGIRoot, "$dblink/curatorLogin\n";
print "Content-type: text/html\n\n";
exit;
}
my ($dbuser, $dbpasswd) = &getUsernamePassword(uc($self->user),
$self->database);
if (!$dbuser || !$dbpasswd) {
print "location: ", $configUrl->dictyBaseCGIRoot, "$dblink/curatorLogin\n";
print "Content-type: text/html\n\n";
exit;
}
if (param('commit')) {
$dbh->disconnect;
$dbh = &ConnectToDatabase($self->database, $dbuser, $dbpasswd);
$self->commitInfo;
}
elsif (param('type') =~ /edit/i) {
$self->editInfo;
}
elsif (param('type') =~ /delete/i) {
$self->deleteInfo;
}
elsif (param('type') =~ /link/i) {
$self->linkInfo;
}
elsif (!param('search')) {
$self->displayEntryForm;
}
else {
$self->displayInfo;
}
}
#######################################################################
sub displayEntryForm {
#######################################################################
my ($self) = @_;
&printStartPage($self->database, $self->title, $self->help);
my ($minYear, $maxYear) = Reference->GetMinMaxYear(dbh=>$dbh);
my (%minYearLabels, %maxYearLabels, @minYearValues, @maxYearValues);
foreach my $year ($minYear .. $maxYear) {
$minYearLabels{$year} = $year;
$maxYearLabels{$year} = $year;
push(@minYearValues, $year);
push(@maxYearValues, $year);
}
print center(table(Tr(td(start_form)).
Tr(td({-bgcolor=>"#b7d8e4",
-width=>120,
-align=>'CENTER'},
font({size=>'+1'}, b("by Pubmed"))).
td(b("Pubmed ").
textfield(-name=>'pubmed',
-size=>'26')).
td(reset(-name=>'Clear')." ".
submit(-name=>'pubmedSubmit',
-value=>'Search'))).
Tr(td(hidden(-name=>'user',
-value=>param('user'))).
td(hidden(-name=>'search',
-value=>'1')).
td(end_form)).
Tr(td({-colspan=>'3'}, hr)).
Tr(td(start_form)).
Tr(td({-bgcolor=>"#b7d8e4",
-width=>120,
-align=>'CENTER'},
font({size=>'+1'}, b("by Citation"))).
td(b("Volume ").
textfield(-name=>'volume',
-size=>'10')." ".
b(" Page ").
textfield(-name=>'page',
-size=>'10')).
td(reset(-name=>'Clear')." ".
submit(-name=>'citationSubmit',
-value=>'Search'))).
Tr(td(hidden(-name=>'user',
-value=>param('user'))).
td(hidden(-name=>'search',
-value=>'1')).
td(end_form)).
Tr(td({-colspan=>'3'}, hr)).
Tr(td(start_form)).
Tr(td({-bgcolor=>"#b7d8e4",
-width=>120,
-align=>'CENTER'},
font({size=>'+1'}, b("by Authors or Keywords"))).
td({colspan=>'2'},
table(Tr(td(b(" Author ").
"(Last Name, First Initial)").
td(textfield(-name=>'author1',
-size=>'24'))).
Tr(td(b(" Author ").
"(Last Name, First Initial)").
td(textfield(-name=>'author2',
-size=>'24'))).
Tr(td(b(" Word(s) in Title or Abstract ").
br." ( if more than one, separated by | ) ").
td(textfield(-name=>'keyword',
-size=>'24'))).
Tr(td(b('From ').
popup_menu(-name=>'minYear',
-"values"=>\@minYearValues,
-default=>$minYear,
-labels=>\%minYearLabels
)).
td(b('through ').
popup_menu(-name=>'maxYear',
-"values"=>\@maxYearValues,
-default=>$maxYear,
-labels=>\%maxYearLabels
))).
Tr(td({-align=>'CENTER',
-colspan=>'2'},
reset(-name=>'Clear')." ".
submit(-name=>'keywordSubmit',
-value=>'Search')))
))).
Tr(td(hidden(-name=>'user',
-value=>param('user'))).
td(hidden(-name=>'search',
-value=>'1')).
td(end_form))
));
&printEndPage;
}
#######################################################################
sub displayInfo {
#######################################################################
my ($self) = @_;
&printStartPage($self->database, $self->title, $self->help);
my @rows;
if (param('pubmedSubmit')) {
my $pubmed = param('pubmed');
&DeleteUnwantedChar(\$pubmed);
if ($pubmed !~ /^[0-9]+$/) {
print p, "You have to enter number for pubmed id.", p;
&printEndPage;
exit;
}
if (!$pubmed) {
print p, "You have to enter pubmed id before press the Search button in the by Pubmed section.", p;
&printEndPage;
exit;
}
my $ref = Reference->new(dbh=>$dbh,
pubmed=>$pubmed);
if (!$ref) {
print p, "The pubmed '$pubmed' you entered is not found in database. Please correct it and try again.", p;
&printEndPage;
exit;
}
my $refNo = $ref->reference_no;
my $citation = $ref->citation;
my $source = $ref->ref_source;
my $status = $ref->status;
my $urlArrayRef = $ref->urlArrayRef;
my $url = $$urlArrayRef[0]->[0];
push(@rows, $self->tableCell($refNo, $citation, $source, $status, $pubmed, $url));
print p, center(b("Search result for Pubmed ID = ", font({-color=>'red'}, $pubmed))), p;
}
elsif (param('citationSubmit')) {
my $volume = param('volume');
my $page = param('page');
&DeleteUnwantedChar(\$volume);
&DeleteUnwantedChar(\$page);
if (!$volume || !$page) {
print p, "You have to enter volume and page before press the Search button in the by Citation section.", p;
&printEndPage;
exit;
}
my $issue;
if ($volume =~ /^([0-9]+) *\(([0-9]+)\)$/) {
$volume = $1;
$issue = $2;
}
my $newPage;
if ($page !~ /\-/) {
$newPage = $page."-%";
}
my $arrayRef = $self->searchBYcitation($volume, $issue,
$page, $newPage);
if (!@$arrayRef) {
if (!$issue) {
print p, "No reference is found for volume = '$volume' and page = '$page' in database. Please correct them and try again.", p;
}
else {
print p, "No reference is found for volume = '$volume', issue = '$issue' and page = '$page' in database. Please correct them and try again.", p;
}
&printEndPage;
exit;
}
foreach my $rowRef (@$arrayRef) {
my ($refNo) = @$rowRef;
my $ref = Reference->new(dbh=>$dbh,
reference_no=>$refNo);
my $citation = $ref->citation;
my $source = $ref->ref_source;
my $status = $ref->status;
my $pubmed = $ref->pubmed;
my $urlArrayRef = $ref->urlArrayRef;
my $url = $$urlArrayRef[0]->[0];
push(@rows, $self->tableCell($refNo, $citation, $source, $status, $pubmed, $url));
}
if (@$arrayRef == 1) {
if (!$issue) {
print p, center(b("One reference was found for volume = ", font({-color=>'red'}, $volume), " and page = ", font({-color=>'red'}, $page))), p;
}
else {
print p, center(b("One reference was found for volume = ", font({-color=>'red'}, $volume), ", issue = ", font({-color=>'red'}, $issue), " and page = ", font({-color=>'red'}, $page))), p;
}
}
else {
if (!$issue) {
print p, center(b(@$arrayRef, " references were found for volume = ", font({-color=>'red'}, $volume), " and page = ", font({-color=>'red'}, $page))), p;
}
else {
print p, center(b(@$arrayRef, " references were found for volume = ", font({-color=>'red'}, $volume), ", issue = ", font({-color=>'red'}, $issue), " and page = ", font({-color=>'red'}, $page))), p;
}
}
}
elsif (param('keywordSubmit')) {
my $author1 = param('author1');
my $author2 = param('author2');
$author1 =~ s/\,/ /g;
$author1 =~ s/ +/ /g;
$author2 =~ s/\,/ /g;
$author2 =~ s/ +/ /g;
&DeleteUnwantedChar(\$author1);
&DeleteUnwantedChar(\$author2);
if ($author2 && !$author1) {
$author1 = $author2;
$author2 = "";
}
my $keyword = param('keyword');
&DeleteUnwantedChar(\$keyword);
my $minYear = param('minYear');
my $maxYear = param('maxYear');
if (!$author1 && !$keyword) {
print p, "You have to enter author(s) and/or keyword(s) before press the Search button in the by Authors or Keywords section.", p;
&printEndPage;
exit;
}
####################
if (!param('confirmation')) {
my ($authorArrayRef1, $authorArrayRef2);
if ($author1) {
$authorArrayRef1 = $self->getAuthorList($author1);
if (!@$authorArrayRef1) {
print "No author matches '$author1' in database. Please go back, correct it and try again.", p;
&printEndPage;
exit;
}
}
else { @$authorArrayRef1 = (); }
if ($author2) {
$authorArrayRef2 = $self->getAuthorList($author2);
if (!@$authorArrayRef2) {
print "No author matches '$author2' in database. Please go back, correct it and try again.", p;
&printEndPage;
exit;
}
}
else { @$authorArrayRef2 = (); }
if (@$authorArrayRef1 > 1 || @$authorArrayRef2 > 1) {
$self->displayAuthorList($authorArrayRef1,
$authorArrayRef2,
$keyword, $minYear, $maxYear);
&printEndPage;
exit;
}
else {
if ($author1) {
my ($rowRef) = @$authorArrayRef1;
($author1) = @$rowRef;
}
if ($author2) {
my ($rowRef) = @$authorArrayRef2;
($author2) = @$rowRef;
}
}
}
####################
my @refNo = $self->searchBYkeyword($author1, $author2,
$keyword, $minYear, $maxYear);
foreach my $refNo (@refNo) {
my $ref = Reference->new(dbh=>$dbh,
reference_no=>$refNo);
my $year = $ref->year;
if ($year > $maxYear || $year < $minYear) { next; }
my $citation = $ref->citation;
my $source = $ref->ref_source;
my $status = $ref->status;
my $pubmed = $ref->pubmed;
my $urlArrayRef = $ref->urlArrayRef;
my $url = $$urlArrayRef[0]->[0];
push(@rows, $self->tableCell($refNo, $citation, $source, $status, $pubmed, $url));
}
if (@rows == 1) {
if ($author1 && $author2 && $keyword) {
print p, center(b("One reference was found for author = ", font({-color=>'red'}, $author1), ", author = ", font({-color=>'red'}, $author2), ", and keyword = ", font({-color=>'red'}, $keyword))), p;
}
elsif ($author1 && $keyword) {
print p, center(b("One reference was found for author = ", font({-color=>'red'}, $author1), " and keyword = ", font({-color=>'red'}, $keyword))), p;
}
elsif ($author1) {
print p, center(b("One reference was found for author = ", font({-color=>'red'}, $author1))), p;
}
elsif ($keyword) {
print p, center(b("One reference was found for keyword = ", font({-color=>'red'}, $keyword))), p;
}
}
else {
my $num = @rows;
if ($author1 && $author2 && $keyword) {
print p, center(b($num, " references were found for author = ", font({-color=>'red'}, $author1), ", author = ", font({-color=>'red'}, $author2), ", and keyword = ", font({-color=>'red'}, $keyword))), p;
}
elsif ($author1 && $keyword) {
print p, center(b($num, " references were found for author = ", font({-color=>'red'}, $author1), " and keyword = ", font({-color=>'red'}, $keyword))), p;
}
elsif ($author1) {
print p, center(b($num, " references were found for author = ", font({-color=>'red'}, $author1))), p;
}
elsif ($keyword) {
print p, center(b($num, " references were found for keyword = ", font({-color=>'red'}, $keyword))), p;
}
}
}
print table({-border=>3,
-cellpadding=>4,
-cellspacing=>4},
$self->tableHeader.
Tr[@rows]
), p;
&printEndPage;
}
#######################################################################
sub displayAuthorList {
#######################################################################
my ($self, $authorArrayRef1, $authorArrayRef2, $keyword, $minYear,
$maxYear) = @_;
print p, center(h3("Please select author(s), enter or edit keyword(s) before press 'Search' button")), p;
my $authorPopup;
if (@$authorArrayRef1) {
my @author1;
foreach my $rowRef (@$authorArrayRef1) {
my ($author) = @$rowRef;
push(@author1, $author);
}
$authorPopup = td(b('Author ').
popup_menu(-name=>'author1',
-"values"=>\@author1));
}
if (@$authorArrayRef2) {
my @author2;
foreach my $rowRef (@$authorArrayRef2) {
my ($author) = @$rowRef;
push(@author2, $author);
}
if ($authorPopup) {
$authorPopup .= " ".td(b('Author ').
popup_menu(-name=>'author2',
-"values"=>\@author2));
}
else {
$authorPopup = td(b('Author ').
popup_menu(-name=>'author2',
-"values"=>\@$authorArrayRef2));
}
}
print center(table(Tr(td(start_form)).
Tr(td({-bgcolor=>"#b7d8e4",
-width=>120,
-align=>'CENTER'},
font({size=>'+1'}, b("by Authors or Keywords"))).
td({colspan=>'2'},
table(Tr($authorPopup).
Tr(td(b(" Word(s) in Title or Abstract ").
br." ( if more than one, separated by | ) ").
td(textfield(-name=>'keyword',
-size=>'24'))).
Tr(td(hidden(-name=>'minYear',
-value=>"$minYear").
hidden(-name=>'maxYear',
-value=>"$maxYear")).
td(hidden(-name=>'confirmation',
-value=>"1"))).
Tr(td({-align=>'CENTER',
-colspan=>'2'},
reset(-name=>'Clear')." ".
submit(-name=>'keywordSubmit',
-value=>'Search')))
))).
Tr(td(hidden(-name=>'user',
-value=>param('user'))).
td(hidden(-name=>'search',
-value=>'1')).
td(end_form))
));
}
######################################################################
sub commitInfo {
######################################################################
my ($self) = @_;
&printStartPage($self->database, $self->title, $self->help);
if (param('type') =~ /edit/i) {
$self->doUpdate;
}
elsif (param('type') =~ /delete/i) {
$self->doDelete;
}
elsif (param('type') =~ /link/i) {
$self->doLink;
}
&printEndPage;
}
######################################################################
sub editInfo {
######################################################################
my ($self) = @_;
&printStartPage($self->database, $self->title, $self->help);
print p, b(font({-size=>'+2'},
"Edit the reference info below")),p;
############
my $refObject = Reference->new(dbh=>$dbh,
reference_no=>param('refNo'));
my @author = split(/\, /, $refObject->authorList);
my @type;
foreach my $rowRef (@{$refObject->pubtypeArrayRef}) {
my ($type) = @$rowRef;
push(@type, $type);
}
############
print startform;
&author(@author);
&refTitle($refObject->Title);
if ($refObject->pubmed) {
print table(&journal($refObject->journal).
&volume($refObject->volume).
&page($refObject->page).
&year($refObject->year).
&pubmed($refObject->pubmed));
}
else {
print table(&journal($refObject->journal).
&volume($refObject->volume).
&page($refObject->page).
&year($refObject->year));
}
&abstract($refObject->abstract);
&publishedStatus($refObject->status);
&pubType(@type);
print
hidden('user', $self->user),
hidden('type', param('type')),
hidden('refNo', param('refNo')),
submit(-name=>'commit',
-value=>'Submit'), " ",
reset,
endform, p;
&printEndPage;
}
######################################################################
sub doUpdate {
######################################################################
my ($self) = @_;
my $refObject = Reference->new(dbh=>$dbh,
reference_no=>param('refNo'));
my @DBauthor = split(/\, /, $refObject->authorList);
my @DBtype;
foreach my $rowRef (@{$refObject->pubtypeArrayRef}) {
my ($type) = @$rowRef;
push(@DBtype, $type);
}
my @author;
for (my $i = 1; $i <= 12; $i++) {
my $author = param('author');
&DeleteUnwantedChar(\$author);
if (!$author) { next; }
push(@author, $author);
}
#### update reference and journal table
$self->updateReferenceTable($refObject);
#### update abstract table
$self->updateAbstractTable($refObject);
#### update author and author_editor table
$self->updateAuthorTable($refObject);
#### update publication_type table
$self->updatePublicationTypeTable($refObject);
}
######################################################################
sub deleteInfo {
######################################################################
my ($self) = @_;
&printStartPage($self->database, $self->title, $self->help);
my $refObj = Reference->new(dbh=>$dbh,
reference_no=>param('refNo'));
if (!$refObj) {
print "The reference_no = ".param('refNo')." is not found in database.",p;
&printEndPage;
exit;
}
my $arrayRef = Reflink->GetRefLinkInfoBYrefNo(dbh=>$dbh,
reference_no=>param('refNo'));
my $rows;
foreach my $rowRef (@$arrayRef) {
my ($tabNm, $prikey, $prikeycol, $colNm) = @$rowRef;
$rows .= Tr(td({-align=>'left'}, $tabNm).
td({-align=>'left'}, $prikey).
td({-align=>'left'}, $prikeycol));
}
if ($rows) {
print p, font({-color=>'red'},
b("You are about to delete a reference.")), p;
print b("This reference is linked to the following database items:"), p;
print table({-border=>'1'},
Tr(th({-align=>'left'},"Table Name").
th({-align=>'left'}, "Primary Key").
th({-align=>'left'}, "Primary Key Column")).
$rows),p;
}
my $locusArrayRef = $refObj->locusArrayRef;
my $featureArrayRef = $refObj->featureArrayRef;
if (@$locusArrayRef || @$featureArrayRef) {
if ($rows) {
print b("And it is in the following loci/features' literature guide:"), p;
}
else {
print b("This reference is in the following loci/features' literature guide:"), p;
}
}
foreach my $rowRef (@$locusArrayRef, @$featureArrayRef) {
my ($locusNm) = @$rowRef;
print li($locusNm);
}
print p, font({-color=>'red',
-size=>'+2'},
b("If you want to delete this reference from database, press 'Delete' button below.")),p;
print startform;
print
hidden('user', $self->user),
hidden('type', param('type')),
hidden('refNo', param('refNo')),
submit(-name=>'commit',
-value=>'Delete'), " ",
endform, p;
&printEndPage;
}
######################################################################
sub doDelete {
######################################################################
my ($self) = @_;
my $refObj = Reference->new(dbh=>$dbh,
reference_no=>param('refNo'));
eval { $refObj->delete; };
if ($@) {
print "An error occurred when deleting reference info for reference_no = ".param('refNo')." from database:$@", br;
$dbh->rollback;
}
else {
print "The reference info for reference_no = ".param('refNo')." has been deleted from database.", br;
$dbh->commit;
}
}
######################################################################
sub linkInfo {
######################################################################
my ($self) = @_;
&printStartPage($self->database, $self->title, $self->help);
print startform;
my $rows;
for (my $i = 1; $i <= 6; $i++) {
$rows .= Tr(td(textfield(-name=>"tabNm$i")).
td(textfield(-name=>"prikey$i")).
td(textfield(-name=>"prikeycol$i")).
td(textfield(-name=>"colNm$i")));
}
print font({-size=>'+2'}, b("Associate a reference to some data:")),p;
print a({-href=>$configUrl->dictyBaseHtmlRoot.uc($self->database)."/doc/db_specifications.html",
-target=>'infowin'},
uc($self->database)." Table Specifications"),p;
print startform;
print table({-border=>'1'},
Tr(th({-align=>'left'},"Table Name").
th({-align=>'left'}, "Primary Key").
th({-align=>'left'}, "Primary Key Column").
th({-align=>'left'}, "Column Name")).
$rows),p;
print font({-color=>'red'},
b("Note: Table Name, Primary Key and Primary Key Column are required. The Column Name is optional.")),p;
print
hidden('user', $self->user),
hidden('type', param('type')),
hidden('refNo', param('refNo')),
submit(-name=>'commit',
-value=>'Submit'), " ",
reset,
endform, p;
print b("Examples:"),p;
print b("EG 1: To link a paper to a locus's phenotype:"),br;
print "tab_name = LOCUS_PHENO",br;
print "primary_Key = 1111::830", br;
print "primary_key_col = LOCUS_NO::PHENOTYPE_NO", br;
print "col_name can be null",p;
print b("EG 2: To link a paper to a locus's description in locus table:"), br;
print "tab_name = LOCUS",br;
print "primary_Key = 1111",br;
print "primary_key_col = LOCUS_NO",br;
print "col_name = DESCRIPTION",p;
&printEndPage;
}
######################################################################
sub doLink {
######################################################################
my ($self) = @_;
for (my $i = 1; $i <= 6; $i++) {
my $tabNm = param("tabNm$i");
my $prikey = param("prikey$i");
my $prikeycol = param("prikeycol$i");
my $colNm = param("colNm$i");
&DeleteUnwantedChar(\$tabNm);
&DeleteUnwantedChar(\$prikey);
&DeleteUnwantedChar(\$prikeycol);
&DeleteUnwantedChar(\$colNm);
if (!$tabNm || !$prikey || !$prikeycol) { next; }
eval {
Reflink->Insert(dbh=>$dbh,
literals=>{reflink_no=>'CGM_DDB.reflinkno_seq.nextval'},
binds=>{reference_no=>param('refNo'),
tab_name=>$tabNm,
primary_key=>$prikey,
primary_key_col=>$prikeycol,
col_name=>$colNm});
};
if ($@) {
print "An error occurred when inserting new reflink entry for reference_no = ".param('refNo').", tab_name = '$tabNm', primary_key = '$prikey', primary_key_col = '$prikeycol' and col_name = '$colNm' into database:$@", br;
$dbh->rollback;
}
else {
print "The new reflink entry for reference_no = ".param('refNo').", tab_name = '$tabNm', primary_key = '$prikey', primary_key_col = '$prikeycol' and col_name = '$colNm' has been inserted into database.", br;
$dbh->commit;
}
}
}
#######################################################################
sub updatePublicationTypeTable {
#######################################################################
my ($self, $refObj) = @_;
my @DBtype;
foreach my $rowRef (@{$refObj->pubtypeArrayRef}) {
my ($type) = @$rowRef;
push(@DBtype, $type);
}
my @type = param('pubtype');
my $typeList = join(':', @type);
my $DBtypeList = join(':', @DBtype);
if ($typeList eq $DBtypeList) { return; }
eval { $refObj->deletePubType; };
if ($@) {
print "An error occurred when deleting the pub_type for reference_no = ".$refObj->reference_no.":$@", br;
$dbh->rollback;
return;
}
else {
print "The pub_type for reference_no = ".$refObj->reference_no." has been deleted from database.", br;
$dbh->commit;
}
foreach my $type (@type) {
eval {
Publication_type->Insert(dbh=>$dbh,
binds=>{reference_no=>$refObj->reference_no,
pub_type=>$type});
};
if ($@) {
print "An error occurred when inserting pub_type = '$type' into database:$@", br;
$dbh->rollback;
}
else {
print "The pub_type = '$type' has been inserted into database.", br;
$dbh->commit;
}
}
}
#######################################################################
sub updateAuthorTable {
#######################################################################
my ($self, $refObj) = @_;
my @author;
for (my $i = 1; $i <= 12; $i++) {
my $author = param("author$i");
$author =~ s/[\,\.]//g;
&DeleteUnwantedChar(\$author);
if (!$author) { next; }
push(@author, $author);
}
my $authorList = join(", ", @author);
if ($authorList eq $refObj->authorList) { return; }
eval { $refObj->deleteAuthorEditor; };
if ($@) {
print "An error occurred when deleting the author_editor info for reference_no = ".$refObj->reference_no.":$@", br;
$dbh->rollback;
}
else {
$dbh->commit;
}
my $order;
foreach my $author (@author) {
$order++;
my $authorObj = Author->new(dbh=>$dbh,
author_name=>$author);
if (!$authorObj) {
$authorObj = $self->insertAuthor($author);
}
if (!$authorObj) { next; }
my $authorNo = $authorObj->author_no;
$self->insertAuthorEditor($authorNo, $refObj->reference_no,
$order);
}
}
#######################################################################
sub insertAuthor {
#######################################################################
my ($self, $author) = @_;
eval {
Author->Insert(dbh=>$dbh,
literals=>{author_no=>'CGM_DDB.authorno_seq.nextval'},
binds=>{author_name=>$author});
};
if ($@) {
print "An error occurred when inserting author '$author' into database:$@", br;
$dbh->rollback;
}
else {
print "The author '$author' has been inserted into database.", br;
$dbh->commit;
my $authorObj = Author->new(dbh=>$dbh,
author_name=>$author);
return $authorObj;
}
}
#######################################################################
sub insertAuthorEditor {
#######################################################################
my ($self, $authorNo, $refNo, $order) = @_;
eval {
Author_editor->Insert(dbh=>$dbh,
binds=>{author_no=>$authorNo,
reference_no=>$refNo,
author_type=>'Author',
author_order=>$order});
};
if ($@) {
print "An error occurred when inserting author_no = $authorNo, reference_no = $refNo, author_type = 'Author' and author_order = $order into author_editor table:$@", br;
$dbh->rollback;
}
else {
print "The new author_editor entry for author_no = $authorNo, reference_no = $refNo, author_type = 'Author' and author_order = $order has been inserted into database.", br;
$dbh->commit;
}
}
#######################################################################
sub updateAbstractTable {
#######################################################################
my ($self, $refObj) = @_;
my $abstract = param('abstract');
&DeleteUnwantedChar(\$abstract);
if ($abstract eq $refObj->abstract) { return ; }
my $absObj = Abstract->new(dbh=>$dbh,
reference_no=>param('refNo'));
if ($absObj) {
if (!$abstract) {
eval { $absObj->delete; };
if ($@) {
print "An error occurred when deleting abstract for reference_no = ".param('refNo')." from database:$@", br;
$dbh->rollback;
}
else {
print "The abstract for reference_no = ".param('refNo')." has been deleted from database.", br;
$dbh->commit;
}
}
else {
$absObj->updateAbstract($abstract);
eval { $absObj->enterUpdates; };
if ($@) {
print "An error occurred when updating abstract table:$@", br;
$dbh->rollback;
}
else {
print "The abstract table has been updated.", br;
$dbh->commit;
}
}
}
else {
eval {
Abstract->Insert(dbh=>$dbh,
binds=>{reference_no=>param('refNo'),
abstract=>$abstract});
};
if ($@) {
print "An error occurred when inserting abstract '$abstract' into database:$@", br;
$dbh->rollback;
}
else {
print "The abstract has been inserted into database.", br;
$dbh->commit;
}
}
}
#######################################################################
sub updateReferenceTable {
#######################################################################
my ($self, $refObj) = @_;
my $status = param('reftype');
my $year = param('year');
my $pubmed = param('pubmed');
my $page = param('page');
my $volume = param('volume');
my $title = param('reftitle');
my $journal = param('journal');
&DeleteUnwantedChar(\$status);
&DeleteUnwantedChar(\$year);
&DeleteUnwantedChar(\$pubmed);
&DeleteUnwantedChar(\$page);
&DeleteUnwantedChar(\$volume);
&DeleteUnwantedChar(\$title);
&DeleteUnwantedChar(\$journal);
my $update;
if ($refObj->status ne $status) {
$refObj->updateStatus($status);
$update++;
}
if ($refObj->year != $year) {
$refObj->updateYear($year);
$update++;
}
if ($refObj->pubmed != $pubmed) {
$refObj->updatePubmed($pubmed);
$update++;
}
if ($refObj->page ne $page) {
$refObj->updatePage($page);
$update++;
}
if ($refObj->volume ne $volume) {
$refObj->updateVolume($volume);
$update++;
}
if ($refObj->title ne $title) {
$refObj->updateTitle($title);
$update++;
}
my $journalNo;
if ($journal =~ /^[0-9]+$/) {
my $journalObj = Journal->new(dbh=>$dbh,
journal_no=>$journal);
if (!$journalObj) {
print "The journal_no = $journal is not found in database.",br;
return;
}
$journal = $journalObj->abbreviation;
$journalNo = $journalObj->journal_no;
}
elsif ($journal) {
my $journalObj = Journal->new(dbh=>$dbh,
abbreviation=>$journal);
if (!$journalObj) {
$journalObj = $self->insertJournal($journal);
}
if (!$journalObj) {
return;
}
$journalNo = $journalObj->journal_no;
}
if ($refObj->journal_no ne $journalNo) {
$refObj->updateJournal_no($journalNo);
$update++;
}
if ($update) {
eval { $refObj->enterUpdates; };
if ($@) {
print "An error occurred when updating reference table:$@",br;
$dbh->rollback;
}
else {
print "The reference table has been updated.", br;
$dbh->commit;
}
}
}
#######################################################################
sub insertJournal {
#######################################################################
my ($self, $journal) = @_;
my ($abbrev, $issn, $Jtitle) = &findFullJournalInfo($journal);
eval {
Journal->Insert(dbh=>$dbh,
literals=>{journal_no=>'CGM_DDB.journalno_seq.nextval'},
binds=>{abbreviation=>$abbrev,
issn=>$issn,
full_name=>$Jtitle});
};
if ($@) {
print "An error occurred when inserting journal '$journal' into database:$@", br;
$dbh->rollback;
return;
}
else {
print "The new journal '$journal' has been inserted into database.", br;
$dbh->commit;
my $journalObj = Journal->new(dbh=>$dbh,
abbreviation=>$abbrev);
return $journalObj;
}
}
#######################################################################
sub getAuthorList {
#######################################################################
my ($self, $author) = @_;
$author =~ s/\*/\%/g;
if ($author !~ /\%/) { $author .= "\%"; }
my $arrayRef = Author->GetAuthorListBYname(dbh=>$dbh,
author=>$author);
return $arrayRef;
}
#######################################################################
sub searchBYcitation {
#######################################################################
my ($self, $volume, $issue, $page, $newPage) = @_;
my $sth;
if (!$issue) {
if (!$newPage) {
$sth = $dbh->prepare("
SELECT reference_no
FROM CGM_DDB.reference
WHERE volume = ?
AND page = ?
");
$sth->execute($volume, $page);
}
else {
$sth = $dbh->prepare("
SELECT reference_no
FROM CGM_DDB.reference
WHERE volume = ?
AND (page = ? or page like ?)
");
$sth->execute($volume, $page, $newPage);
}
}
else {
if (!$newPage) {
$sth = $dbh->prepare("
SELECT reference_no
FROM CGM_DDB.reference
WHERE volume = ?
AND issue = ?
AND page = ?
");
$sth->execute($volume, $issue, $page);
}
else {
$sth = $dbh->prepare("
SELECT reference_no
FROM CGM_DDB.reference
WHERE volume = ?
AND issue = ?
AND (page = ? or page like ?)
");
$sth->execute($volume, $issue, $page, $newPage);
}
}
my $arrayRef = $sth->fetchall_arrayref();
return $arrayRef;
}
#######################################################################
sub searchBYkeyword {
#######################################################################
my ($self, $author1, $author2, $keyword, $minYear, $maxYear)
= @_;
my $sth;
if ($author2) {
$sth = $dbh->prepare("
SELECT AE.reference_no
FROM CGM_DDB.author A, CGM_DDB.author_editor AE
WHERE AE.author_no = A.author_no
AND A.author_name = ?
AND AE.reference_no in (
select ae.reference_no
from CGM_DDB.author a, CGM_DDB.author_editor ae
where ae.author_no = a.author_no
and a.author_name = ?)
ORDER BY AE.reference_no
");
$sth->execute($author1, $author2);
}
elsif ($author1) {
$sth = $dbh->prepare("
SELECT AE.reference_no
FROM CGM_DDB.author A, CGM_DDB.author_editor AE
WHERE AE.author_no = A.author_no
AND A.author_name = ?
ORDER BY AE.reference_no
");
$sth->execute($author1);
}
my @refNo;
my %foundRefNo;
if ($author1) {
while(my($refNo) = $sth->fetchrow()) {
push(@refNo, $refNo);
$foundRefNo{$refNo}++;
}
if (!$keyword || !@refNo) {
return @refNo;
}
}
$keyword =~ s/ *\| */\|/g;
my @kw = split(/\|/, $keyword);
if (@kw) {
my ($titleWhereClause, $absWhereClause);
foreach my $kw (@kw) {
$kw = "\U$kw";
$titleWhereClause .= " AND upper(title) like '%$kw%'";
$absWhereClause .= " AND upper(abstract) like '%$kw%'";
}
my $sth = $dbh->prepare("
SELECT reference_no
FROM CGM_DDB.reference
WHERE year >= $minYear
AND year <= $maxYear
$titleWhereClause
");
$sth->execute;
my @refNo;
my %foundKWinTitle;
while(my ($refNo) = $sth->fetchrow()) {
if (!$author1) {
push(@refNo, $refNo);
$foundKWinTitle{$refNo}++;
}
elsif ($foundRefNo{$refNo}) {
push(@refNo, $refNo);
$foundKWinTitle{$refNo}++;
}
}
$absWhereClause =~ s/^ *AND//;
$sth = $dbh->prepare("
SELECT reference_no
FROM CGM_DDB.abstract
WHERE $absWhereClause
");
$sth->execute;
while(my ($refNo) = $sth->fetchrow()) {
if (!$author1 && !$foundKWinTitle{$refNo}) {
push(@refNo, $refNo);
}
elsif ($foundRefNo{$refNo} && !$foundKWinTitle{$refNo}) {
push(@refNo, $refNo);
}
}
return sort{$a<=>$b}(@refNo);
}
return;
}
#######################################################################
sub tableHeader {
#######################################################################
return Tr({-bgcolor=>'#b7d8e4'},
th("Del/Edit/Link").
th("Reference_no").
th("Citation").
th("Source").
th("Status").
th("Pubmed ID").
th("Fulltext Link"));
}
#######################################################################
sub tableCell {
#######################################################################
my ($self, $refNo, $citation, $source, $status, $pubmed, $url)
= @_;
if (!$url) { $url = br; }
else { $url = a({-href=>"$url", -target=>'infowin'}, "Full Text"); }
if (!$pubmed) { $pubmed = br; }
else {
my $pubmedUrl = "http://www.ncbi.nlm.nih.gov/entrez/query.fcgi?cmd=Retrieve&db=PubMed&list_uids=${pubmed}&dopt=Abstract";
$pubmed = a({-href=>"$pubmedUrl",
-target=>'infowin'},
$pubmed);
}
my $links = b(a({-href=>"/db/cgi-bin/dictyBase/curation/refCuration"."?user=".param('user')."&refNo=$refNo&type=delete"}, "Delete")."|".
a({-href=>"/db/cgi-bin/dictyBase/curation/refCuration"."?user=".param('user')."&refNo=$refNo&type=edit"}, "Edit")."|".
a({-href=>"/db/cgi-bin/dictyBase/curation/refCuration"."?user=".param('user')."&refNo=$refNo&type=link"}, "Link"));
return td({-align=>'center'},
$links).
td({-align=>'center'},
$refNo).
td($citation).
td($source).
td($status).
td({-align=>'center'},
$pubmed).
td($url);
}
#######################################################################
1; #######
######################## END ##########################################