View | Details | Raw Unified | Return to bug 4310
Collapse All | Expand All

(-)a/installer/data/mysql/updatedatabase.pl (-4 / +40 lines)
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
- 

Return to bug 4310