|
Lines 2875-2886
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
Link Here
|
| 2875 |
print "Upgrade to $DBversion done (adding basketgroups)\n"; |
2875 |
print "Upgrade to $DBversion done (adding basketgroups)\n"; |
| 2876 |
SetVersion ($DBversion); |
2876 |
SetVersion ($DBversion); |
| 2877 |
} |
2877 |
} |
| 2878 |
|
|
|
| 2879 |
$DBversion = '3.01.00.077'; |
2878 |
$DBversion = '3.01.00.077'; |
| 2880 |
if (C4::Context->preference("Version") < TransformToNum($DBversion)) { |
2879 |
if (C4::Context->preference("Version") < TransformToNum($DBversion)) { |
| 2881 |
|
2880 |
|
| 2882 |
$dbh->do("SET FOREIGN_KEY_CHECKS=0 "); |
2881 |
$dbh->do("SET FOREIGN_KEY_CHECKS=0 "); |
|
|
2882 |
# create a mapping table holding the info we need to match orders to budgets |
| 2883 |
$dbh->do('DROP TABLE IF EXISTS fundmapping'); |
| 2884 |
$dbh->do( |
| 2885 |
q|CREATE TABLE fundmapping AS |
| 2886 |
SELECT aqorderbreakdown.ordernumber, branchcode, bookfundid, budgetdate, entrydate |
| 2887 |
FROM aqorderbreakdown JOIN aqorders ON aqorderbreakdown.ordernumber = aqorders.ordernumber|); |
| 2888 |
# match the new type of the corresponding field |
| 2889 |
$dbh->do('ALTER TABLE fundmapping modify column bookfundid varchar(30)'); |
| 2890 |
# System did not ensure budgetdate was valid historically |
| 2891 |
$dbh->do(q|UPDATE fundmapping SET budgetdate = entrydate WHERE budgetdate = '0000-00-00' OR budgetdate IS NULL|); |
| 2892 |
# We save the map in fundmapping in case you need later processing |
| 2893 |
$dbh->do(q|ALTER TABLE fundmapping add column aqbudgetid integer|); |
| 2894 |
# these can speed processing up |
| 2895 |
$dbh->do(q|CREATE INDEX fundmaporder ON fundmapping (ordernumber)|); |
| 2896 |
$dbh->do(q|CREATE INDEX fundmapid ON fundmapping (bookfundid)|); |
| 2897 |
|
| 2883 |
$dbh->do("DROP TABLE IF EXISTS `aqbudgetperiods` "); |
2898 |
$dbh->do("DROP TABLE IF EXISTS `aqbudgetperiods` "); |
|
|
2899 |
|
| 2884 |
$dbh->do(qq| |
2900 |
$dbh->do(qq| |
| 2885 |
CREATE TABLE `aqbudgetperiods` ( |
2901 |
CREATE TABLE `aqbudgetperiods` ( |
| 2886 |
`budget_period_id` int(11) NOT NULL auto_increment, |
2902 |
`budget_period_id` int(11) NOT NULL auto_increment, |
|
Lines 2999-3008
BUDGETDROPDATES
Link Here
|
| 2999 |
ADD COLUMN `budgetgroup_id` int(11) NOT NULL, |
3015 |
ADD COLUMN `budgetgroup_id` int(11) NOT NULL, |
| 3000 |
ADD COLUMN `sort1_authcat` varchar(10) default NULL, |
3016 |
ADD COLUMN `sort1_authcat` varchar(10) default NULL, |
| 3001 |
ADD COLUMN `sort2_authcat` varchar(10) default NULL" ); |
3017 |
ADD COLUMN `sort2_authcat` varchar(10) default NULL" ); |
| 3002 |
|
3018 |
# We need to map the orders to the budgets |
|
|
3019 |
# For Historic reasons this is more complex than it should be on occasions |
| 3020 |
my $budg_arr = $dbh->selectall_arrayref( |
| 3021 |
q|SELECT aqbudgets.budget_id, aqbudgets.budget_code, aqbudgetperiods.budget_period_startdate, |
| 3022 |
aqbudgetperiods.budget_period_enddate |
| 3023 |
FROM aqbudgets JOIN aqbudgetperiods ON aqbudgets.budget_period_id = aqbudgetperiods.budget_period_id |
| 3024 |
ORDER BY budget_code, budget_period_startdate|, { Slice => {} }); |
| 3025 |
# We arbitarily order on start date, this means if you have overlapping periods the order will be |
| 3026 |
# linked to the latest matching budget YMMV |
| 3027 |
my $b_sth = $dbh->prepare( |
| 3028 |
'UPDATE fundmapping set aqbudgetid = ? where bookfundid =? AND budgetdate >= ? AND budgetdate <= ?'); |
| 3029 |
for my $b ( @{$budg_arr}) { |
| 3030 |
$b_sth->execute($b->{budget_id}, $b->{budget_code}, $b->{budget_period_startdate}, $b->{budget_period_enddate}); |
| 3031 |
} |
| 3032 |
# move the budgetids to aqorders |
| 3033 |
$dbh->do(q|UPDATE aqorders, fundmapping SET aqorders.budget_id = fundmapping.aqbudgetid |
| 3034 |
WHERE aqorders.ordernumber = fundmapping.ordernumber AND fundmapping.aqbudgetid IS NOT NULL|); |
| 3035 |
# NB fundmapping is left as an accontants trail also if you have budgetids that werent set |
| 3036 |
# you can decide what to do with them |
| 3037 |
|
| 3038 |
$dbh->do( |
| 3039 |
q|UPDATE aqorders, aqbudgets SET aqorders.budgetgroup_id = aqbudgets.budget_period_id |
| 3040 |
WHERE aqorders.budget_id = aqbudgets.budget_id|); |
| 3003 |
# cannot do until aqorderbreakdown removed |
3041 |
# cannot do until aqorderbreakdown removed |
| 3004 |
# $dbh->do("DROP TABLE aqbookfund "); |
3042 |
# $dbh->do("DROP TABLE aqbookfund "); |
| 3005 |
|
|
|
| 3006 |
# $dbh->do("ALTER TABLE aqorders ADD FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON UPDATE CASCADE " ); ???? |
3043 |
# $dbh->do("ALTER TABLE aqorders ADD FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON UPDATE CASCADE " ); ???? |
| 3007 |
$dbh->do("SET FOREIGN_KEY_CHECKS=1 "); |
3044 |
$dbh->do("SET FOREIGN_KEY_CHECKS=1 "); |
| 3008 |
|
3045 |
|
| 3009 |
- |
|
|