Bugzilla – Attachment 2603 Details for
Bug 4310
No Migration for budgets from 3.0 to 3.2
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Proposed Patch
0001-Bug-4310-Map-orders-to-new-budget-structure.patch (text/plain), 4.66 KB, created by
Colin Campbell
on 2010-09-06 14:06:37 UTC
(
hide
)
Description:
Proposed Patch
Filename:
MIME Type:
Creator:
Colin Campbell
Created:
2010-09-06 14:06:37 UTC
Size:
4.66 KB
patch
obsolete
>From 2328c6a8212cc53b5380b28dcacdaa688b36c2cb Mon Sep 17 00:00:00 2001 >From: Colin Campbell <colin.campbell@ptfs-europe.com> >Date: Mon, 6 Sep 2010 14:25:07 +0100 >Subject: [PATCH] Bug 4310 Map orders to new budget structure >Content-Type: text/plain; charset="utf-8" > >Map orders to newly created budgets >creates a temporary file fundmapping >which is not dropped so that conversion can be >checked subsequently > >As acquisitions historically let you do some odd things >with dates and funds. This probably needs close checking >if you are converting active data >--- > installer/data/mysql/updatedatabase.pl | 43 +++++++++++++++++++++++++++++-- > 1 files changed, 40 insertions(+), 3 deletions(-) > >diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl >index b8325e8..1a21e25 100755 >--- a/installer/data/mysql/updatedatabase.pl >+++ b/installer/data/mysql/updatedatabase.pl >@@ -2875,12 +2875,28 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > print "Upgrade to $DBversion done (adding basketgroups)\n"; > SetVersion ($DBversion); > } >- > $DBversion = '3.01.00.077'; > if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > > $dbh->do("SET FOREIGN_KEY_CHECKS=0 "); >+ # create a mapping table holding the info we need to match orders to budgets >+ $dbh->do('DROP TABLE IF EXISTS fundmapping'); >+ $dbh->do( >+ q|CREATE TABLE fundmapping AS >+ SELECT aqorderbreakdown.ordernumber, branchcode, bookfundid, budgetdate, entrydate >+ FROM aqorderbreakdown JOIN aqorders ON aqorderbreakdown.ordernumber = aqorders.ordernumber|); >+ # match the new type of the corresponding field >+ $dbh->do('ALTER TABLE fundmapping modify column bookfundid varchar(30)'); >+ # System did not ensure budgetdate was valid historically >+ $dbh->do(q|UPDATE fundmapping SET budgetdate = entrydate WHERE budgetdate = '0000-00-00' OR budgetdate IS NULL|); >+ # We save the map in fundmapping in case you need later processing >+ $dbh->do(q|ALTER TABLE fundmapping add column aqbudgetid integer|); >+ # these can speed processing up >+ $dbh->do(q|CREATE INDEX fundmaporder ON fundmapping (ordernumber)|); >+ $dbh->do(q|CREATE INDEX fundmapid ON fundmapping (bookfundid)|); >+ > $dbh->do("DROP TABLE IF EXISTS `aqbudgetperiods` "); >+ > $dbh->do(qq| > CREATE TABLE `aqbudgetperiods` ( > `budget_period_id` int(11) NOT NULL auto_increment, >@@ -2999,10 +3015,31 @@ BUDGETDROPDATES > ADD COLUMN `budgetgroup_id` int(11) NOT NULL, > ADD COLUMN `sort1_authcat` varchar(10) default NULL, > ADD COLUMN `sort2_authcat` varchar(10) default NULL" ); >- >+ # We need to map the orders to the budgets >+ # For Historic reasons this is more complex than it should be on occasions >+ my $budg_arr = $dbh->selectall_arrayref( >+ q|SELECT aqbudgets.budget_id, aqbudgets.budget_code, aqbudgetperiods.budget_period_startdate, >+ aqbudgetperiods.budget_period_enddate >+ FROM aqbudgets JOIN aqbudgetperiods ON aqbudgets.budget_period_id = aqbudgetperiods.budget_period_id >+ ORDER BY budget_code, budget_period_startdate|, { Slice => {} }); >+ # We arbitarily order on start date, this means if you have overlapping periods the order will be >+ # linked to the latest matching budget YMMV >+ my $b_sth = $dbh->prepare( >+ 'UPDATE fundmapping set aqbudgetid = ? where bookfundid =? AND budgetdate >= ? AND budgetdate <= ?'); >+ for my $b ( @{$budg_arr}) { >+ $b_sth->execute($b->{budget_id}, $b->{budget_code}, $b->{budget_period_startdate}, $b->{budget_period_enddate}); >+ } >+ # move the budgetids to aqorders >+ $dbh->do(q|UPDATE aqorders, fundmapping SET aqorders.budget_id = fundmapping.aqbudgetid >+ WHERE aqorders.ordernumber = fundmapping.ordernumber AND fundmapping.aqbudgetid IS NOT NULL|); >+ # NB fundmapping is left as an accontants trail also if you have budgetids that werent set >+ # you can decide what to do with them >+ >+ $dbh->do( >+ q|UPDATE aqorders, aqbudgets SET aqorders.budgetgroup_id = aqbudgets.budget_period_id >+ WHERE aqorders.budget_id = aqbudgets.budget_id|); > # cannot do until aqorderbreakdown removed > # $dbh->do("DROP TABLE aqbookfund "); >- > # $dbh->do("ALTER TABLE aqorders ADD FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON UPDATE CASCADE " ); ???? > $dbh->do("SET FOREIGN_KEY_CHECKS=1 "); > >-- >1.7.2.2 >
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 4310
:
2564
| 2603