---- 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.
Pushed patch from Henri-Damien adjusting the budget upgrade. Please test and advise if that improves the migration of the fund balances.
Updating version as described in http://wiki.koha-community.org/wiki/General_IRC_Meeting,_7_July_2010
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.
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
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);
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.
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.
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
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
Marking resolved; will include warnings in release notes.