Summary: | No Migration for budgets from 3.0 to 3.2 | ||
---|---|---|---|
Product: | Koha | Reporter: | Colin Campbell <colin.campbell> |
Component: | Acquisitions | Assignee: | Henri-Damien LAURENT <henridamien> |
Status: | CLOSED FIXED | QA Contact: | Bugs List <koha-bugs> |
Severity: | blocker | ||
Priority: | PATCH-Sent (DO NOT USE) | CC: | chris, dev_patches, gmcharlt |
Version: | rel_3_2 | ||
Hardware: | PC | ||
OS: | All | ||
Change sponsored?: | --- | Patch complexity: | --- |
Documentation contact: | Documentation submission: | ||
Text to go in the release notes: | Version(s) released in: | ||
Circulation function: | |||
Attachments: |
patch proposed for updatedatabase
Proposed Patch |
Description
Chris Cormack
2010-05-21 01:25:39 UTC
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. |