Bug 4310 - No Migration for budgets from 3.0 to 3.2
Summary: No Migration for budgets from 3.0 to 3.2
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Acquisitions (show other bugs)
Version: rel_3_2
Hardware: PC All
: PATCH-Sent (DO NOT USE) blocker (vote)
Assignee: Henri-Damien LAURENT
QA Contact: Bugs List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2010-03-09 02:43 UTC by Colin Campbell
Modified: 2019-06-27 09:24 UTC (History)
3 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
patch proposed for updatedatabase (1.31 KB, patch)
2010-08-24 11:30 UTC, dev_patches
Details | Diff | Splinter Review
Proposed Patch (4.66 KB, patch)
2010-09-06 14:06 UTC, Colin Campbell
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Chris Cormack 2010-05-21 01:25:39 UTC


---- Reported by colin.campbell@ptfs-europe.com 2010-03-09 14:43:19 ----

No migration or documentation has been supplied for users of 3.0 Acq budgets to the new 3.2 budgets. There should be a path for users to upgrade without losing info



---- Additional Comments From henridamien@koha-fr.org 2010-03-09 22:51:19 ----

Hi,
it is all coped with in dbrev 3.01.00.077
Can you check and update that report?



---- Additional Comments From colin.campbell@ptfs-europe.com 2010-03-10 10:48:56 ----

A comment in the db revision explicitly states there is no upgrade, Will test with copy of real data. An earlier experience suggested that data might be lost on this upgrade, (this may have been caused by other bugs, since fixed in the upgrade script)



---- Additional Comments From colin.campbell@ptfs-europe.com 2010-03-19 18:02:25 ----

An initial test seemed to increase my funds by $10,000. Committed and spent seem to be erased. investigating further. 



---- Additional Comments From colin.campbell@ptfs-europe.com 2010-03-22 08:42:39 ----

Ran a test upgrading from a copy of a live system.

Pre- upgrade Totals
Total in Funds & budgets is 300000
                 spent      777134.44
                 Comitted    53850.39
                 Available -530984.83

After Upgrade:
                Total is  380000
                Spent     0
                Comitted  0
                Available 380000






---- Additional Comments From colin.campbell@ptfs-europe.com 2010-03-22 17:13:13 ----

Yes budget_id is being set to 0 for all existing orders the data held previosly in aqorderbreakdown is lost when that table is dropped. Is this as intended if so it needs a big health warning.



---- Additional Comments From gmcharlt@gmail.com 2010-05-03 16:08:19 ----

(In reply to comment #5)
> Yes budget_id is being set to 0 for all existing orders the data held previosly
> in aqorderbreakdown is lost when that table is dropped. Is this as intended if
> so it needs a big health warning.

Setting as blocker.  This would be a rather large "health warning" unless there is no possible way to avoid it.





---- Additional Comments From colin.campbell@ptfs-europe.com 2010-05-19 11:54:15 ----

On a copy of a live system I ran through the upgrades to 3.2 and compared the results. Using a map derived from the old aqorderbreakdown I managed to reinstate links from the orders to budgets (otherwise all orders become unlinked).  This means I now have spend and committed amounts.  As in the earlier check I now seem to have $80,000 in the budgets. The committed and spent amounds differ wildly the old committed amount was $777,036.20 it is now $138,952.67 the old spent total was $79.076.62 it is now $27,976.81






--- Bug imported by chris@bigballofwax.co.nz 2010-05-21 01:25 UTC  ---

This bug was previously known as _bug_ 4310 at http://bugs.koha.org/cgi-bin/bugzilla3/show_bug.cgi?id=4310

Actual time not defined. Setting to 0.0
Setting qa contact to the default for this product.
   This bug either had no qa contact or an invalid one.

Comment 1 Galen Charlton 2010-07-07 16:09:36 UTC
Pushed patch from Henri-Damien adjusting the budget upgrade.  Please test and advise if that improves the migration of the fund balances.
Comment 2 MJ Ray (software.coop) 2010-07-07 19:17:05 UTC
Updating version as described in http://wiki.koha-community.org/wiki/General_IRC_Meeting,_7_July_2010
Comment 3 Colin Campbell 2010-07-08 13:35:03 UTC
Re ran the earlier test with current HEAD. Situation remains as before

Pre- upgrade Totals
Total in Funds & budgets is 300000
                 spent      809878.74
                 Comitted   112430.39
                 Available -622309.13

After Upgrade:
                Total is  390000
                Spent     0
                Comitted  0
                Available 390000

Copying the relevant files to Henri-Damien for inspection.
Comment 4 Colin Campbell 2010-07-23 12:52:02 UTC
One suggestion is that aqorderbreakdown not be deleted behind the users back. The table contains the info needed to map orders to budgets. Running the updatedatebase script as is causes all orders to be "linked" to the non existent budget_id 0
Comment 5 Chris Cormack 2010-08-23 22:43:26 UTC
What I did, to upgrade was

SELECT aqorders.ordernumber, biblionumber, entrydate, quantity, currency, listprice, totalamount, datereceived, booksellerinvoicenumber, freight, unitprice, quantityreceived, cancelledby, datecancellationprinted, notes, supplierreference, purchaseordernumber, subscription, serialid, basketno, biblioitemnumber, timestamp, rrp, ecost, gst, budgetdate, sort1, sort2, bookfundid)
 from aqorders,aqorderbreakdown WHERE
aqorders.ordernumber=aqorderbreakdown.ordernumber GROUP BY
aqorders.ordernumber INTO OUTFILE '/tmp/aqorders.csv';

LOAD DATA INFILE '/tmp/aqorders.csv' INTO TABLE aqorders (ordernumber, biblionumber, entrydate, quantity, currency, listprice, totalamount, datereceived, booksellerinvoicenumber, freight, unitprice, quantityreceived, cancelledby, datecancellationprinted, notes, supplierreference, purchaseordernumber, subscription, serialid, basketno, biblioitemnumber, timestamp, rrp, ecost, gst, budgetdate, sort1, sort2, budget_id);
Comment 6 dev_patches 2010-08-24 11:30:26 UTC
Created attachment 2564 [details] [review]
patch proposed for updatedatabase

This patch should fix the process of updating aqorders table from 3.0.x to 3.2 and new acquisition system.
Should assign colin campbell problem.
Comment 7 Colin Campbell 2010-09-03 14:33:04 UTC
Problem with the patch (and Chris's SQL) lies in the fact that bookfundid is varchar and its being assigned to an integer budget_id.
Also there is not a one-to-one correspodence between the two. On the conversion I did this generates more budgets than I had funds because of the budgetperiods.

My problem is that I've only done this (in test mode) for a site running 3.1.61. I've no 3.0 site to confirm this and we don't have any documentation suggesting the thinking behind the db change.
Comment 8 Colin Campbell 2010-09-03 20:11:20 UTC
I created a table holding the info needed for mapping:

CREATE TABLE fundmapping AS SELECT aqorderbreakdown.ordernumber, branchcode, bookfundid, budgetdate, entrydate from aqorderbreakdown
join aqorders on aqorderbreakdown.ordernumber = aqorders.ordernumber;
ALTER TABLE fundmapping modify column bookfundid varchar(30);
ALTER TABLE fundmapping add column aqbudgetid integer;
CREATE INDEX fundmaporder ON fundmapping (ordernumber);
CREATE INDEX fundmapid ON fundmapping (bookfundid);
UPDATE fundmapping set budgetdate = entrydate where budgetdate IS NULL;
UPDATE fundmapping set budgetdate = entrydate where budgetdate = '0000-00-00';

and used that however the approach I'm now using is to recreate the budget data and then use this to map, treating it as a data conversion task. This may be problem made worse by nature of the data, but I rthink we need major warnings for anyone running 3.0 Acq
Comment 9 Colin Campbell 2010-09-06 14:06:37 UTC
Created attachment 2603 [details] [review]
Proposed Patch

This works for me. (At least for data which links correctly to my current active budgets) 

The issue of handling the budget mappings will need to be flagged up in release notes as acq allowed a lot of lee way on how strictly you controlled budgets so you may encounter unforeseen cases.

I create a fundmapping table and its not dropped afterwards. This is to leave the required data there in case you need to do further processing/checking, for that reason two it has the new budget_id in it for those orders we've mapped successfully
Comment 10 Galen Charlton 2010-10-22 02:24:22 UTC
Marking resolved; will include warnings in release notes.