From 364ba90a6a6065e453f24133bc0d57c8a18e66a2 Mon Sep 17 00:00:00 2001 From: Olli-Antti Kivilahti Date: Fri, 13 Feb 2015 13:58:02 +0200 Subject: [PATCH] Bug 13707 - 1. Adding a new column datereceived for items and biblioitems This column tells when the Item or Biblio has been received for the first time into a library. Date-of-acquisition (dateaccessioned) answers, when the Item has been ordered. Date-of-receival (datereceived) answers, when the Item has arrived to the library or when the first Item for the Biblio has for the first time arrived to the library. This is very useful regarding statistics of acquisitions if they are based on the date received instead of the date acquired. Also other cool stuff can be built on top of it, like better searches for new material. This patch adds new DB columns items.datereceived, biblioitems.datereceived, deleteditems.datereceived, deletedbiblioitems.datereceived and populates those columns from the acorders.datereceived if the Item has been acquired using the acquisitions module, or from the items.dateaccessioned-column if no acquisitions module has been used. Biblioitems.datereceived is always the oldest timestamp from all the Items the Biblio has. --- installer/data/mysql/kohastructure.sql | 4 ++++ installer/data/mysql/updatedatabase.pl | 43 ++++++++++++++++++++++++++++++++++ 2 files changed, 47 insertions(+) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 13b6a6a..f06062f 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -183,6 +183,7 @@ CREATE TABLE `biblioitems` ( -- information related to bibliographic records in `agerestriction` varchar(255) default NULL, -- target audience/age restriction from the bib record (MARC21 521$a) `totalissues` int(10), `marcxml` longtext, -- full bibliographic MARC record in MARCXML + `datereceived` timestamp default NULL, --When the first Item for this Biblio is received. Updated only once when the first Item has been received. PRIMARY KEY (`biblioitemnumber`), KEY `bibinoidx` (`biblioitemnumber`), KEY `bibnoidx` (`biblionumber`), @@ -815,6 +816,7 @@ CREATE TABLE `deletedbiblioitems` ( -- information about bibliographic records t `agerestriction` varchar(255) default NULL, -- target audience/age restriction from the bib record (MARC21 521$a) `totalissues` int(10), `marcxml` longtext, -- full bibliographic MARC record in MARCXML + `datereceived` timestamp default NULL, -- When the first Item for this Biblio is received. Updated only once when the first Item has been received. PRIMARY KEY (`biblioitemnumber`), KEY `bibinoidx` (`biblioitemnumber`), KEY `bibnoidx` (`biblionumber`), @@ -911,6 +913,7 @@ CREATE TABLE `deleteditems` ( `biblioitemnumber` int(11) NOT NULL default 0, -- foreign key from the biblioitems table to link to item to additional information `barcode` varchar(20) default NULL, -- item barcode (MARC21 952$p) `dateaccessioned` date default NULL, -- date the item was acquired or added to Koha (MARC21 952$d) + `datereceived` timestamp default NULL, -- When this Item was received. `booksellerid` mediumtext default NULL, -- where the item was purchased (MARC21 952$e) `homebranch` varchar(10) default NULL, -- foreign key from the branches table for the library that owns this item (MARC21 952$a) `price` decimal(8,2) default NULL, -- purchase price (MARC21 952$g) @@ -1213,6 +1216,7 @@ CREATE TABLE `items` ( -- holdings/item information `biblioitemnumber` int(11) NOT NULL default 0, -- foreign key from the biblioitems table to link to item to additional information `barcode` varchar(20) default NULL, -- item barcode (MARC21 952$p) `dateaccessioned` date default NULL, -- date the item was acquired or added to Koha (MARC21 952$d) + `datereceived` timestamp default NULL, -- When this Item was received. `booksellerid` mediumtext default NULL, -- where the item was purchased (MARC21 952$e) `homebranch` varchar(10) default NULL, -- foreign key from the branches table for the library that owns this item (MARC21 952$a) `price` decimal(8,2) default NULL, -- purchase price (MARC21 952$g) diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 10809a1..76b9a6f 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -7904,6 +7904,49 @@ if ( CheckVersion($DBversion) ) { SetVersion ($DBversion); } +$DBversion = "3.19.00.XXX"; +if ( CheckVersion($DBversion) ) { + print "- Upgrading (Bug 13707). Adding and recalculating datereceived-column for biblioitems- and items-tables. This may take a while. Sorry :(\n"; + #Check if this upgrade has already been deployed, and skip this. + my $sth_desc_biblioitems = $dbh->prepare("DESC biblioitems"); + $sth_desc_biblioitems->execute(); + my $desc_biblioitems = $sth_desc_biblioitems->fetchall_hashref('Field'); + if ($desc_biblioitems->{datereceived}) { + print "- It looks like this DB change has already been applied. If you want to rebuild datereceived-columns for items and biblioitems, run the following command:\n". + "- ALTER TABLE biblioitems DROP column datereceived; ALTER TABLE deletedbiblioitems DROP column datereceived; ALTER TABLE items DROP column datereceived; ALTER TABLE deleteditems DROP column datereceived;\n". + "- And rerun this updatedatabase.pl subroutine in a separate Perl-script.\n"; + } + else { + + #Datereceived is the aqorders.datereceived, so we can copy it from there. For alive and deleted items and biblioitems. + #If no aqorders.datereceived is present, we use the dateaccessioned. + #it is important to UPDTE also deleted items/biblioitems due to statistical consistency. + + ###Items UPDATE here ### + $dbh->do("ALTER TABLE items ADD COLUMN datereceived timestamp NULL"); + $dbh->do("ALTER TABLE deleteditems ADD COLUMN datereceived timestamp NULL"); + $dbh->do("UPDATE items i LEFT JOIN aqorders_items ai ON i.itemnumber = ai.itemnumber LEFT JOIN aqorders ao ON ai.ordernumber = ao.ordernumber SET i.datereceived = IF(ai.ordernumber IS NOT NULL, ao.datereceived, i.dateaccessioned);"); + $dbh->do("UPDATE deleteditems i LEFT JOIN aqorders_items ai ON i.itemnumber = ai.itemnumber LEFT JOIN aqorders ao ON ai.ordernumber = ao.ordernumber SET i.datereceived = IF(ai.ordernumber IS NOT NULL, ao.datereceived, i.dateaccessioned);"); + + ###Biblioitems UPDATE here ### + $dbh->do("ALTER TABLE biblioitems ADD COLUMN datereceived timestamp NULL"); + $dbh->do("ALTER TABLE deletedbiblioitems ADD COLUMN datereceived timestamp NULL"); + + #Set the biblioitems.datereceived to the smallest of items vs deleteditems or NULL + $dbh->do("UPDATE biblioitems bi + LEFT JOIN items i ON i.biblionumber = bi.biblionumber LEFT JOIN deleteditems di ON di.biblionumber = bi.biblionumber + SET bi.datereceived = + IF(IFNULL(i.datereceived,'9999-12-31') < IFNULL(di.datereceived,'9999-12-31'), i.datereceived, di.datereceived);"); + #UPDATE deletedbiblioitems as well. + $dbh->do("UPDATE biblioitems bi + LEFT JOIN items i ON i.biblionumber = bi.biblionumber LEFT JOIN deleteditems di ON di.biblionumber = bi.biblionumber + SET bi.datereceived = + IF(IFNULL(i.datereceived,'9999-12-31') < IFNULL(di.datereceived,'9999-12-31'), i.datereceived, di.datereceived);"); + } + print "Upgrade to $DBversion done (Bug 13707 - Add datereceived-column and and search index, for genuinely knowing the real Biblios datereceived.)\n"; + SetVersion ($DBversion); +} + $DBversion = "3.15.00.008"; if ( CheckVersion($DBversion) ) { $dbh->do(q{ -- 1.9.1