From 50566e18c1c1d79243bad1cec01a2e9cb35fc90f Mon Sep 17 00:00:00 2001 From: Olli-Antti Kivilahti Date: Fri, 13 Feb 2015 13:58:02 +0200 Subject: [PATCH] Bug 13707 - 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 | 2 ++ installer/data/mysql/updatedatabase.pl | 54 ++++++++++++++++++++++++++++++++ 2 files changed, 56 insertions(+) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index cd83f99..fa1f834 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -182,6 +182,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 NOT NULL, -- 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`), @@ -813,6 +814,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 NOT NULL, -- 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`), diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index dab1c62..df207bd 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -7858,6 +7858,60 @@ if ( CheckVersion($DBversion) ) { SetVersion ($DBversion); } +$DBversion = "3.19.00.XXX"; +if ( CheckVersion($DBversion) ) { + print "- Upgrading (Bug XXXX). 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. + #Biblioitems UPDATE here + $dbh->do("ALTER TABLE biblioitems ADD COLUMN datereceived timestamp NULL"); + $dbh->do("ALTER TABLE deletedbiblioitems ADD COLUMN datereceived timestamp NULL"); + + #Fetch the UNION of items and deleteditems with the acquisitions data. It seems impossible to make this SELECT-UPDATE in SQL, so using slow Perl UPDATE statements. + my $sth_fetchall = $dbh->prepare(" + SELECT dada.biblionumber, dada.ordernumber, min(dada.datereceived) as datereceived, min(dada.dateaccessioned) as dateaccessioned, dada.deleted FROM + ( (SELECT i.biblionumber, ai.ordernumber, min(ao.datereceived) as datereceived, min(i.dateaccessioned) as dateaccessioned, 0 as deleted FROM items i LEFT JOIN aqorders_items ai ON i.itemnumber = ai.itemnumber LEFT JOIN aqorders ao ON ai.ordernumber = ao.ordernumber GROUP BY i.biblionumber) + UNION ALL + (SELECT i.biblionumber, ai.ordernumber, min(ao.datereceived) as datereceived, min(i.dateaccessioned) as dateaccessioned, 1 as deleted FROM deleteditems i LEFT JOIN aqorders_items ai ON i.itemnumber = ai.itemnumber LEFT JOIN aqorders ao ON ai.ordernumber = ao.ordernumber GROUP BY i.biblionumber) + ) as dada GROUP BY dada.biblionumber;"); + $sth_fetchall->execute(); + my $results = $sth_fetchall->fetchall_hashref('biblionumber'); + foreach my $biblionumber (keys $results) { #UPDATE each biblio + my $data = $results->{$biblionumber}; + my $datereceived; + if ($data->{ordernumber}) { + $datereceived = $data->{datereceived} ? $data->{datereceived} : 'NULL'; #We can have an Biblio on order, but no Items have been received yet. + } + else { + $datereceived = $data->{dateaccessioned} ? $data->{dateaccessioned} : 'NULL'; + } + $dbh->do("UPDATE deletedbiblioitems bi SET bi.datereceived = '$datereceived' WHERE biblionumber = $biblionumber") if $data->{deleted}; + $dbh->do("UPDATE biblioitems bi SET bi.datereceived = '$datereceived' WHERE biblionumber = $biblionumber") unless $data->{deleted}; + } + #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);"); + + + } + print "Upgrade to $DBversion done (Bug XXXX - 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.7.9.5