From 3de59aba2776184a6fd2e9943d86a7c66e8f7648 Mon Sep 17 00:00:00 2001 From: Ryan Higgins Date: Tue, 8 Jan 2008 10:51:56 -0600 Subject: [PATCH] Serials updates to link item record to serial table. This commit makes db changes to subscription, serials tables: allow subscriptions larger than 127 issues link serial to item uniquely. MARC Framework: Adding MARC tag 942$s (990$s unimarc) to map biblio.serial Udate biblio.serial on new-subscription. Use biblio.serial to check if Items.pm should return pubdate and enumeration text string from serials table with items. Update opac-detail so serial items are sorted most recent first. Adding link from items table to serial table. Allows sorting of serials in details pages, and removes enum data from itemnotes field. Also fixes visibility in serials item editor Signed-off-by: Joshua Ferraro --- C4/Biblio.pm | 2 +- C4/Items.pm | 51 ++++++++++++------- C4/Serials.pm | 10 ++++ .../mandatory/marc21_framework_DEFAULT.sql | 5 +- .../mandatory/unimarc_framework_DEFAULT.sql | 3 +- installer/data/mysql/kohastructure.sql | 11 ++-- installer/data/mysql/updatedatabase.pl | 10 ++++ .../prog/en/modules/serials/serials-edit.tmpl | 14 ++--- .../prog/en/modules/opac-detail.tmpl | 6 ++- kohaversion.pl | 2 +- opac/opac-detail.pl | 3 +- serials/serials-edit.pl | 16 ++++-- serials/subscription-add.pl | 2 +- 13 files changed, 90 insertions(+), 45 deletions(-) diff --git a/C4/Biblio.pm b/C4/Biblio.pm index b89e32a36a..3cae75327f 100755 --- a/C4/Biblio.pm +++ b/C4/Biblio.pm @@ -1943,7 +1943,7 @@ sub PrepareItemrecordDisplay { my ( $itemtagfield, $itemtagsubfield ) = &GetMarcFromKohaField( "items.itemnumber", $frameworkcode ); my $tagslib = &GetMarcStructure( 1, $frameworkcode ); - my $itemrecord = GetMarcItem( $bibnum, $itemnum) if ($itemnum); + my $itemrecord = C4::Items::GetMarcItem( $bibnum, $itemnum) if ($itemnum); my @loop_data; my $authorised_values_sth = $dbh->prepare( diff --git a/C4/Items.pm b/C4/Items.pm index feb64ba42f..f263629cc0 100644 --- a/C4/Items.pm +++ b/C4/Items.pm @@ -109,35 +109,40 @@ of C =over 4 -$item = GetItem($itemnumber,$barcode); +$item = GetItem($itemnumber,$barcode,$serial); =back Return item information, for a given itemnumber or barcode. The return value is a hashref mapping item column -names to values. +names to values. If C<$serial> is true, include serial publication data. =cut sub GetItem { - my ($itemnumber,$barcode) = @_; + my ($itemnumber,$barcode, $serial) = @_; my $dbh = C4::Context->dbh; + my $data; if ($itemnumber) { my $sth = $dbh->prepare(" SELECT * FROM items WHERE itemnumber = ?"); $sth->execute($itemnumber); - my $data = $sth->fetchrow_hashref; - return $data; + $data = $sth->fetchrow_hashref; } else { my $sth = $dbh->prepare(" SELECT * FROM items WHERE barcode = ?" ); - $sth->execute($barcode); - my $data = $sth->fetchrow_hashref; - return $data; + $sth->execute($barcode); + $data = $sth->fetchrow_hashref; } + if ( $serial) { + my $ssth = $dbh->prepare("SELECT serialseq,publisheddate from serial where itemnumber=?"); + $ssth->execute($data->{'itemnumber'}) ; + ($data->{'serialseq'} , $data->{'publisheddate'}) = $ssth->fetchrow_array(); + } + return $data; } # sub GetItem =head2 AddItemFromMarc @@ -161,7 +166,6 @@ sub AddItemFromMarc { # parse item hash from MARC my $frameworkcode = GetFrameworkCode( $biblionumber ); my $item = &TransformMarcToKoha( $dbh, $source_item_marc, $frameworkcode ); - return AddItem($item, $biblionumber, $dbh, $frameworkcode); } @@ -199,7 +203,7 @@ sub AddItem { _set_defaults_for_add($item); _set_derived_columns_for_add($item); # FIXME - checks here - my ( $itemnumber, $error ) = _koha_new_item( $dbh, $item, $item->{barcode} ); + my ( $itemnumber, $error ) = _koha_new_item( $dbh, $item, $item->{barcode} ); $item->{'itemnumber'} = $itemnumber; # create MARC tag representing item and add to bib @@ -1086,7 +1090,7 @@ sub GetItemsInfo { $sth->execute($biblionumber); my $i = 0; my @results; - my ( $date_due, $count_reserves ); + my ( $date_due, $count_reserves, $serial ); my $isth = $dbh->prepare( "SELECT issues.*,borrowers.cardnumber,borrowers.surname,borrowers.firstname,borrowers.branchcode as bcode @@ -1094,7 +1098,8 @@ sub GetItemsInfo { WHERE itemnumber = ? AND returndate IS NULL" ); - while ( my $data = $sth->fetchrow_hashref ) { + my $ssth = $dbh->prepare("SELECT serialseq,publisheddate from serial where itemnumber=?"); + while ( my $data = $sth->fetchrow_hashref ) { my $datedue = ''; $isth->execute( $data->{'itemnumber'} ); if ( my $idata = $isth->fetchrow_hashref ) { @@ -1110,7 +1115,12 @@ sub GetItemsInfo { } } } - if ( $datedue eq '' ) { + if ( $data->{'serial'}) { + $ssth->execute($data->{'itemnumber'}) ; + ($data->{'serialseq'} , $data->{'publisheddate'}) = $ssth->fetchrow_array(); + $serial = 1; + } + if ( $datedue eq '' ) { my ( $restype, $reserves ) = C4::Reserves::CheckReserves( $data->{'itemnumber'} ); if ($restype) { @@ -1118,7 +1128,7 @@ sub GetItemsInfo { } } $isth->finish; - + $ssth->finish; #get branch information..... my $bsth = $dbh->prepare( "SELECT * FROM branches WHERE branchcode = ? @@ -1193,8 +1203,11 @@ sub GetItemsInfo { $i++; } $sth->finish; - - return (@results); + if($serial) { + return( sort { $b->{'publisheddate'} cmp $a->{'publisheddate'} } @results ); + } else { + return (@results); + } } =head2 get_itemnumbers_of @@ -1574,7 +1587,7 @@ sub _koha_new_item { my ( $dbh, $item, $barcode ) = @_; my $error; - my $query = + my $query = "INSERT INTO items SET biblionumber = ?, biblioitemnumber = ?, @@ -1607,10 +1620,10 @@ sub _koha_new_item { ccode = ?, itype = ?, materials = ?, - uri = ? + uri = ?, "; my $sth = $dbh->prepare($query); - $sth->execute( + $sth->execute( $item->{'biblionumber'}, $item->{'biblioitemnumber'}, $barcode, diff --git a/C4/Serials.pm b/C4/Serials.pm index b6fec22ea4..e0f260c687 100644 --- a/C4/Serials.pm +++ b/C4/Serials.pm @@ -1389,6 +1389,16 @@ sub NewSubscription { &logaction(C4::Context->userenv->{'number'},"SERIAL","ADD",$subscriptionid,"") if C4::Context->preference("SubscriptionLog"); +#set serial flag on biblio if not already set. + my ($null, ($bib)) = GetBiblio($biblionumber); + if( ! $bib->{'serial'} ) { + my $record = GetMarcBiblio($biblionumber); + my ($tag,$subf) = GetMarcFromKohaField('biblio.serial',$bib->{'frameworkcode'}); + if($tag) { + $record->field($tag)->update( $subf => 1 ); + } + ModBiblio($record,$biblionumber,$bib->{'frameworkcode'}); + } return $subscriptionid; } diff --git a/installer/data/mysql/en/marcflavour/marc21/mandatory/marc21_framework_DEFAULT.sql b/installer/data/mysql/en/marcflavour/marc21/mandatory/marc21_framework_DEFAULT.sql index d85c7b8a14..2e19fe0cd9 100644 --- a/installer/data/mysql/en/marcflavour/marc21/mandatory/marc21_framework_DEFAULT.sql +++ b/installer/data/mysql/en/marcflavour/marc21/mandatory/marc21_framework_DEFAULT.sql @@ -75,13 +75,14 @@ INSERT INTO `marc_subfield_structure` (`tagfield`, `tagsubfield`, `liblibrarian` ('942', '2', 'Source of classification or shelving scheme', 'Source of classification or shelving scheme', 0, 0, 'biblioitems.cn_source', 9, 'cn_source', '', '', NULL, 0, '', '', '', NULL), ('942', '6', 'Koha normalized classification for sorting', 'Koha normalized classification for sorting', 0, 0, 'biblioitems.cn_sort', -1, '', '', '', 0, 7, '', '', '', NULL), ('942', 'a', 'Institution code [OBSOLETE]', 'Institution code [OBSOLETE]', 0, 0, '', 9, '', '', '', NULL, -5, '', '', '', NULL), - ('942', 'c', 'Koha item type', 'Koha item type', 0, 1, 'biblioitems.itemtype', 9, 'itemtypes', '', '', NULL, 0, '', '', '', NULL), + ('942', 'c', 'Koha (default) item type', 'Koha item type', 0, 1, 'biblioitems.itemtype', 9, 'itemtypes', '', '', NULL, 0, '', '', '', NULL), ('942', 'e', 'Edition', 'Edition', 0, 0, 'biblioitems.cn_edition', 9, 'CN_EDITION', '', '', NULL, 0, '', '', '', NULL), ('942', 'h', 'Classification part', 'Classification part', 0, 0, 'biblioitems.cn_class', 9, '', '', '', NULL, 0, '', '', '', NULL), ('942', 'i', 'Item part', 'Item part', 1, 0, 'biblioitems.cn_item', 9, '', '', '', NULL, 9, '', '', '', NULL), ('942', 'k', 'Call number prefix', 'Call number prefix', 0, 0, 'biblioitems.cn_prefix', 9, '', '', '', NULL, 0, '', '', '', NULL), ('942', 'm', 'Call number suffix', 'Call number suffix', 0, 0, 'biblioitems.cn_suffix', 9, '', '', '', 0, 0, '', '', '', NULL), - ('942', 'n', 'Suppress in OPAC', 'Suppress in OPAC', 0, 0, NULL, 9, '', '', '', 0, 0, '', '', '', NULL); + ('942', 'n', 'Suppress in OPAC', 'Suppress in OPAC', 0, 0, NULL, 9, '', '', '', 0, 0, '', '', '', NULL), + ('942', 's', 'Serial record flag', 'Serial record', 0, 0, 'biblio.serial', 9, '', '', '', NULL, -5, '', '', '', NULL),; -- ****************************************************** diff --git a/installer/data/mysql/en/marcflavour/unimarc/mandatory/unimarc_framework_DEFAULT.sql b/installer/data/mysql/en/marcflavour/unimarc/mandatory/unimarc_framework_DEFAULT.sql index 1f333a3075..16340c455a 100644 --- a/installer/data/mysql/en/marcflavour/unimarc/mandatory/unimarc_framework_DEFAULT.sql +++ b/installer/data/mysql/en/marcflavour/unimarc/mandatory/unimarc_framework_DEFAULT.sql @@ -87,7 +87,8 @@ INSERT INTO `marc_subfield_structure` (`tagfield`, `tagsubfield`, `liblibrarian` ('990', 'i', 'Item part', 'Item part', 1, 0, 'biblioitems.cn_item', 9, '', '', '', NULL, 9, '', '', '', NULL), ('990', 'k', 'Call number prefix', 'Call number prefix', 0, 0, 'biblioitems.cn_prefix', 9, '', '', '', NULL, 0, '', '', '', NULL), ('990', 'm', 'Call number suffix', 'Call number suffix', 0, 0, 'biblioitems.cn_suffix', 9, '', '', '', 0, 0, '', '', '', NULL), - ('990', 'n', 'Suppress in OPAC', 'Suppress in OPAC', 0, 0, NULL, 9, '', '', '', 0, 0, '', '', '', NULL); + ('990', 'n', 'Suppress in OPAC', 'Suppress in OPAC', 0, 0, NULL, 9, '', '', '', 0, 0, '', '', '', NULL), + ('990', 's', 'Serial record flag', 'Serial record', 0, 0, 'biblio.serial', 9, '', '', '', NULL, -5, '', '', '', NULL); -- ****************************************************** diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 937df9b5c5..741122c3b2 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1535,10 +1535,11 @@ CREATE TABLE `serial` ( `planneddate` date default NULL, `notes` text, `publisheddate` date default NULL, - `itemnumber` text, + `itemnumber` int(11) default NULL, `claimdate` date default NULL, `routingnotes` text, - PRIMARY KEY (`serialid`) + PRIMARY KEY (`serialid`), + KEY serialitem (`itemnumber`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -1623,9 +1624,9 @@ CREATE TABLE `subscription` ( `aqbooksellerid` int(11) default 0, `cost` int(11) default 0, `aqbudgetid` int(11) default 0, - `weeklength` tinyint(4) default 0, - `monthlength` tinyint(4) default 0, - `numberlength` tinyint(4) default 0, + `weeklength` int(11) default 0, + `monthlength` int(11) default 0, + `numberlength` int(11) default 0, `periodicity` tinyint(4) default 0, `dow` varchar(100) default '', `numberingmethod` varchar(100) default '', diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index caf9aa0f23..11ea46f469 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -930,6 +930,16 @@ VALUES( 'he', 'Hebr')"); SetVersion ($DBversion); } +$DBversion = "3.00.00.046"; +if (C4::Context->preference("Version") < TransformToNum($DBversion)) { + $dbh->do("ALTER TABLE `serial` CHANGE `itemnumber` `itemnumber` int(11) default NULL"); + $dbh->do("ALTER TABLE `subscription` CHANGE `numberlength` `numberlength` int(11) default NULL , + CHANGE `weeklength` `weeklength` int(11) default NULL"); + $dbh->do("ALTER TABLE `serial` ADD UNIQUE KEY `serialitemidx` (`itemnumber`)"); + print "Upgrade to $DBversion done (Update serial table, add index on itemnumber. )\n"; + SetVersion ($DBversion); +} + =item DropAllForeignKeys($table) Drop all foreign keys of the table $table diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/serials-edit.tmpl b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/serials-edit.tmpl index f45b02938b..56130dfabe 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/serials-edit.tmpl +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/serials-edit.tmpl @@ -178,7 +178,7 @@ Error : Barcode Not Unique For serialseq Numbered Published on - planned for + Planned for Status Notes @@ -263,7 +263,7 @@ Error : Barcode Not Unique For serialseq
" class="items"> -
"> +
"> @@ -272,12 +272,12 @@ Error : Barcode Not Unique For serialseq " /> " /> " /> - + ')">+
- + " /> @@ -287,7 +287,7 @@ Error : Barcode Not Unique For serialseq " /> " />
- +
@@ -367,7 +367,7 @@ Error : Barcode Not Unique For serialseq
" class="items"> -
"> +
"> @@ -376,7 +376,7 @@ Error : Barcode Not Unique For serialseq " /> " /> " /> - + ')">+
diff --git a/koha-tmpl/opac-tmpl/prog/en/modules/opac-detail.tmpl b/koha-tmpl/opac-tmpl/prog/en/modules/opac-detail.tmpl index d154fb5805..14ba7853b0 100755 --- a/koha-tmpl/opac-tmpl/prog/en/modules/opac-detail.tmpl +++ b/koha-tmpl/opac-tmpl/prog/en/modules/opac-detail.tmpl @@ -208,7 +208,8 @@ Status Date Due Last Seen - Volume + Copy / Vol + Published @@ -238,7 +239,8 @@ - + + &item=">Fix Itemtype diff --git a/kohaversion.pl b/kohaversion.pl index c61b37e381..1863f9413e 100644 --- a/kohaversion.pl +++ b/kohaversion.pl @@ -10,7 +10,7 @@ use strict; sub kohaversion { - our $VERSION = "3.00.00.044"; + our $VERSION = "3.00.00.046"; # version needs to be set this way # so that it can be picked up by Makefile.PL # during install diff --git a/opac/opac-detail.pl b/opac/opac-detail.pl index ddb42b847a..dff16c8746 100755 --- a/opac/opac-detail.pl +++ b/opac/opac-detail.pl @@ -62,6 +62,7 @@ my $subscriptionsnumber = CountSubscriptionFromBiblionumber($biblionumber); my @subscriptions = GetSubscriptions( $dat->{title}, $dat->{issn}, $biblionumber ); my @subs; +$dat->{'serial'}=1 if $subscriptionsnumber; foreach my $subscription (@subscriptions) { my %cell; $cell{subscriptionid} = $subscription->{subscriptionid}; @@ -73,7 +74,7 @@ foreach my $subscription (@subscriptions) { push @subs, \%cell; } -$dat->{'count'} = @items; +$dat->{'count'} = scalar(@items); #adding RequestOnOpac filter to allow or not the display of plce reserve button # FIXME - use me or delete me. diff --git a/serials/serials-edit.pl b/serials/serials-edit.pl index f5024a909b..f1e61e4eb7 100755 --- a/serials/serials-edit.pl +++ b/serials/serials-edit.pl @@ -222,6 +222,14 @@ if ($op eq 'serialchangestatus') { my $record=MARC::Record::new_from_xml($xml, 'UTF-8'); if ($item=~/^N/){ #New Item + # add serialid to item record + my ($tagfield,$tagsubfield) = &GetMarcFromKohaField("items.itemnumber"); + my $field = $record->field($tagfield); + my ($tagfield,$tagsubfield) = &GetMarcFromKohaField("items.serialid"); + if ($tagsubfield ) { + $field->update($tagsubfield => $itemhash{$item}->{'serial'}); + } + # if autoBarcode is ON, calculate barcode... my ($tagfield,$tagsubfield) = &GetMarcFromKohaField("items.barcode"); if (C4::Context->preference("autoBarcode") ne 'OFF' ) { @@ -230,10 +238,8 @@ if ($op eq 'serialchangestatus') { my $sth_barcode = $dbh->prepare("select max(abs(barcode)) from items"); $sth_barcode->execute; my ($newbarcode) = $sth_barcode->fetchrow; - $newbarcode++; - # OK, we have the new barcode, now create the entry in MARC record - $record->add_fields( $tagfield, "1", "0", - $tagsubfield => $newbarcode ); + # OK, we have the new barcode, add the entry in MARC record # FIXME -> should be using barcode plugin here. + $field->update( $tagsubfield => ++$newbarcode ); } } # check for item barcode # being unique @@ -241,7 +247,7 @@ if ($op eq 'serialchangestatus') { # push @errors,"barcode_not_unique" if($exists); $template->param("barcode_not_unique" => 1,'errserialseq'=>$serialseqs[$index]); # if barcode exists, don't create, but report The problem. - unless ($exists){ + unless ($exists){ my ($biblionumber,$bibitemnum,$itemnumber) = AddItemFromMarc($record,$itemhash{$item}->{'bibnum'}); AddItem2Serial($itemhash{$item}->{'serial'},$itemnumber); } diff --git a/serials/subscription-add.pl b/serials/subscription-add.pl index 0df63107e0..f79a31ca8a 100755 --- a/serials/subscription-add.pl +++ b/serials/subscription-add.pl @@ -19,6 +19,7 @@ use strict; use CGI; use Date::Calc qw(Today Day_of_Year Week_of_Year Add_Delta_Days); use C4::Koha; +use C4::Biblio; use C4::Auth; use C4::Dates qw/format_date format_date_in_iso/; use C4::Acquisition; @@ -163,7 +164,6 @@ if ($op eq 'mod'||$op eq 'dup') { } if ($op eq 'addsubscription') { - my $auser = $query->param('user'); my $branchcode = $query->param('branchcode'); my $aqbooksellerid = $query->param('aqbooksellerid'); -- 2.39.5