From 757a1cf2eaf6b6355847d99f6a075fb9c5eb41bc Mon Sep 17 00:00:00 2001
From: Kyle M Hall <kyle@bywatersolutions.com>
Date: Tue, 23 Oct 2012 14:48:41 -0400
Subject: [PATCH] Bug 8956 - Split serials enumeration data into separate fields - Part 1 - Add fields to DB
Content-Type: text/plain; charset="utf-8"

---
 installer/data/mysql/updatedatabase.pl |   71 ++++++++++++++++++++++++++++++++
 1 files changed, 71 insertions(+), 0 deletions(-)

diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index 4759034..4801476 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -6019,6 +6019,77 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
    SetVersion ($DBversion);
 }
 
+$DBversion = "3.09.00.XXX";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+   $dbh->do("ALTER TABLE serial ADD published_end_date DATE NULL DEFAULT NULL AFTER publisheddate");
+   $dbh->do("
+       ALTER TABLE  serial ADD serialseq_x VARCHAR( 100 ) NULL DEFAULT NULL AFTER serialseq,
+       ADD serialseq_y VARCHAR( 100 ) NULL DEFAULT NULL AFTER serialseq_x,
+       ADD serialseq_z VARCHAR( 100 ) NULL DEFAULT NULL AFTER serialseq_y
+   ");
+   $dbh->do("
+       ALTER TABLE subscription ADD serialseq_x_label VARCHAR( 100 ) NULL DEFAULT NULL AFTER numberingmethod,
+       ADD serialseq_y_label VARCHAR( 100 ) NULL DEFAULT NULL AFTER serialseq_x_label,
+       ADD serialseq_z_label VARCHAR( 100 ) NULL DEFAULT NULL AFTER serialseq_y_label
+   ");
+
+    my $trim = sub {
+        my $string = shift;
+        $string =~ s/^\s+//;
+        $string =~ s/\s+$//;
+        return $string;
+    };
+   
+   my $dbh = C4::Context->dbh;
+   my $sth = $dbh->prepare("SELECT * FROM subscription ORDER BY numberingmethod ASC");
+   $sth->execute();
+   while ( my $subscription = $sth->fetchrow_hashref() ) {
+       ## parse out the labels and save them
+       my $nm = $subscription->{'numberingmethod'};
+       print "NUMBERING METHOD: '$nm'\n";
+       my @labels = split(/\{[XYZ]\}[, ]?/, $nm);
+       @labels = map( $trim->( $_ ), @labels );
+       my ( $x_label, $y_label, $z_label ) = @labels;
+       
+       $dbh->do(
+           "UPDATE subscription SET serialseq_x_label = ?, serialseq_y_label = ?, serialseq_z_label = ? WHERE subscriptionid = ?",
+           undef,
+           ( $x_label, $y_label, $z_label, $subscription->{'subscriptionid'} )
+       );
+
+       ## parse of the data and save
+       my @splits = split(/\{[XYZ]\}/, $nm);
+       foreach my $s ( @splits ) {
+           $s = quotemeta( $s );
+       }
+       
+       my $sth2 = $dbh->prepare("SELECT * FROM serial WHERE subscriptionid = ?");
+       $sth2->execute( $subscription->{'subscriptionid'} );
+       while( my $serial = $sth2->fetchrow_hashref() ) {
+           my $serialseq = $serial->{'serialseq'};
+           my ( $x, $y, $z );
+           ## We cannot split on multiple values at once,
+           ## so let's replace each of those values with __SPLIT__
+           if ( @splits ) {
+               map( $serialseq =~ s/$_/__SPLIT__/ , @splits );
+               ( undef, $x, $y, $z ) = split(/__SPLIT__/, $serialseq);
+           } else { ## Nothing to split on means the only thing in serialseq is {X}
+               $x = $serialseq;
+           }
+           
+           $dbh->do(
+               "UPDATE serial SET serialseq_x = ?, serialseq_y = ?, serialseq_z = ? WHERE serialid = ?",
+               undef,
+               ( $x, $y, $z, $serial->{'serialid'} )
+           );
+       }
+   }
+
+   print "Upgrade to $DBversion done ( Split serials enumeration data into separate fields )\n";
+   SetVersion ($DBversion);
+
+}
+
 =head1 FUNCTIONS
 
 =head2 TableExists($table)
-- 
1.7.2.5