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 |
- |
|
|