|
Lines 7858-7863
if ( CheckVersion($DBversion) ) {
Link Here
|
| 7858 |
SetVersion ($DBversion); |
7858 |
SetVersion ($DBversion); |
| 7859 |
} |
7859 |
} |
| 7860 |
|
7860 |
|
|
|
7861 |
$DBversion = "3.19.00.XXX"; |
| 7862 |
if ( CheckVersion($DBversion) ) { |
| 7863 |
print "- Upgrading (Bug XXXX). Adding and recalculating datereceived-column for biblioitems- and items-tables. This may take a while. Sorry :(\n"; |
| 7864 |
#Check if this upgrade has already been deployed, and skip this. |
| 7865 |
my $sth_desc_biblioitems = $dbh->prepare("DESC biblioitems"); |
| 7866 |
$sth_desc_biblioitems->execute(); |
| 7867 |
my $desc_biblioitems = $sth_desc_biblioitems->fetchall_hashref('Field'); |
| 7868 |
if ($desc_biblioitems->{datereceived}) { |
| 7869 |
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". |
| 7870 |
"- 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". |
| 7871 |
"- And rerun this updatedatabase.pl subroutine in a separate Perl-script.\n"; |
| 7872 |
} |
| 7873 |
else { |
| 7874 |
|
| 7875 |
#Datereceived is the aqorders.datereceived, so we can copy it from there. For alive and deleted items and biblioitems. |
| 7876 |
#If no aqorders.datereceived is present, we use the dateaccessioned. |
| 7877 |
#it is important to UPDTE also deleted items/biblioitems due to statistical consistency. |
| 7878 |
#Biblioitems UPDATE here |
| 7879 |
$dbh->do("ALTER TABLE biblioitems ADD COLUMN datereceived timestamp NULL"); |
| 7880 |
$dbh->do("ALTER TABLE deletedbiblioitems ADD COLUMN datereceived timestamp NULL"); |
| 7881 |
|
| 7882 |
#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. |
| 7883 |
my $sth_fetchall = $dbh->prepare(" |
| 7884 |
SELECT dada.biblionumber, dada.ordernumber, min(dada.datereceived) as datereceived, min(dada.dateaccessioned) as dateaccessioned, dada.deleted FROM |
| 7885 |
( (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) |
| 7886 |
UNION ALL |
| 7887 |
(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) |
| 7888 |
) as dada GROUP BY dada.biblionumber;"); |
| 7889 |
$sth_fetchall->execute(); |
| 7890 |
my $results = $sth_fetchall->fetchall_hashref('biblionumber'); |
| 7891 |
foreach my $biblionumber (keys $results) { #UPDATE each biblio |
| 7892 |
my $data = $results->{$biblionumber}; |
| 7893 |
my $datereceived; |
| 7894 |
if ($data->{ordernumber}) { |
| 7895 |
$datereceived = $data->{datereceived} ? $data->{datereceived} : 'NULL'; #We can have an Biblio on order, but no Items have been received yet. |
| 7896 |
} |
| 7897 |
else { |
| 7898 |
$datereceived = $data->{dateaccessioned} ? $data->{dateaccessioned} : 'NULL'; |
| 7899 |
} |
| 7900 |
$dbh->do("UPDATE deletedbiblioitems bi SET bi.datereceived = '$datereceived' WHERE biblionumber = $biblionumber") if $data->{deleted}; |
| 7901 |
$dbh->do("UPDATE biblioitems bi SET bi.datereceived = '$datereceived' WHERE biblionumber = $biblionumber") unless $data->{deleted}; |
| 7902 |
} |
| 7903 |
#Items UPDATE here |
| 7904 |
$dbh->do("ALTER TABLE items ADD COLUMN datereceived timestamp NULL"); |
| 7905 |
$dbh->do("ALTER TABLE deleteditems ADD COLUMN datereceived timestamp NULL"); |
| 7906 |
$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);"); |
| 7907 |
$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);"); |
| 7908 |
|
| 7909 |
|
| 7910 |
} |
| 7911 |
print "Upgrade to $DBversion done (Bug XXXX - Add datereceived-column and and search index, for genuinely knowing the real Biblios datereceived.)\n"; |
| 7912 |
SetVersion ($DBversion); |
| 7913 |
} |
| 7914 |
|
| 7861 |
$DBversion = "3.15.00.008"; |
7915 |
$DBversion = "3.15.00.008"; |
| 7862 |
if ( CheckVersion($DBversion) ) { |
7916 |
if ( CheckVersion($DBversion) ) { |
| 7863 |
$dbh->do(q{ |
7917 |
$dbh->do(q{ |
| 7864 |
- |
|
|