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

(-)a/installer/data/mysql/atomicupdate/bug_6427.perl (+173 lines)
Line 0 Link Here
1
my $dbh = C4::Context->dbh();
2
3
$dbh->do(q{
4
    UPDATE accountlines a LEFT JOIN issues i USING ( borrowernumber, itemnumber ) SET accounttype = 'F' WHERE i.issue_id IS NULL
5
});
6
7
$dbh->do("
8
    CREATE TABLE IF NOT EXISTS account_credits (
9
        credit_id int(11) NOT NULL AUTO_INCREMENT,
10
        borrowernumber int(11) NOT NULL,
11
        `type` varchar(255) NOT NULL,
12
        amount_received decimal(28,6) DEFAULT NULL,
13
        amount_paid decimal(28,6) NOT NULL,
14
        amount_remaining decimal(28,6) NOT NULL,
15
        amount_voided decimal(28,6) NULL DEFAULT NULL,
16
        notes text,
17
        branchcode VARCHAR( 10 ) NULL DEFAULT NULL,
18
        manager_id int(11) DEFAULT NULL,
19
        created_on timestamp NULL DEFAULT NULL,
20
        updated_on timestamp NULL DEFAULT NULL,
21
        PRIMARY KEY (credit_id),
22
        KEY borrowernumber (borrowernumber),
23
        KEY branchcode (branchcode)
24
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
25
");
26
$dbh->do("
27
    CREATE TABLE IF NOT EXISTS account_debits (
28
        debit_id int(11) NOT NULL AUTO_INCREMENT,
29
        borrowernumber int(11) NOT NULL DEFAULT '0',
30
        itemnumber int(11) DEFAULT NULL,
31
        issue_id int(11) DEFAULT NULL,
32
        `type` varchar(255) NOT NULL,
33
        accruing tinyint(1) NOT NULL DEFAULT '0',
34
        amount_original decimal(28,6) DEFAULT NULL,
35
        amount_outstanding decimal(28,6) DEFAULT NULL,
36
        amount_last_increment decimal(28,6) DEFAULT NULL,
37
        description mediumtext,
38
        notes text,
39
        branchcode VARCHAR( 10 ) NULL DEFAULT NULL,
40
        manager_id int(11) DEFAULT NULL,
41
        created_on timestamp NULL DEFAULT NULL,
42
        updated_on timestamp NULL DEFAULT NULL,
43
        PRIMARY KEY (debit_id),
44
        KEY acctsborridx (borrowernumber),
45
        KEY itemnumber (itemnumber),
46
        KEY borrowernumber (borrowernumber),
47
        KEY issue_id (issue_id),
48
        KEY branchcode (branchcode)
49
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
50
");
51
52
$dbh->do("
53
    CREATE TABLE account_offsets (
54
        offset_id int(11) NOT NULL AUTO_INCREMENT,
55
        debit_id int(11) DEFAULT NULL,
56
        credit_id int(11) DEFAULT NULL,
57
        `type` varchar(255) DEFAULT NULL,
58
        amount decimal(28,6) NOT NULL COMMENT 'A positive number here represents a payment, a negative is a increase in a fine.',
59
        created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
60
        PRIMARY KEY (offset_id),
61
        KEY fee_id (debit_id),
62
        KEY payment_id (credit_id)
63
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
64
");
65
66
$dbh->do("
67
    ALTER TABLE `account_credits`
68
      ADD CONSTRAINT account_credits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
69
      ADD CONSTRAINT account_credits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE;
70
");
71
$dbh->do("
72
    ALTER TABLE `account_debits`
73
      ADD CONSTRAINT account_debits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
74
      ADD CONSTRAINT account_debits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE;
75
");
76
$dbh->do("
77
    ALTER TABLE `account_offsets`
78
      ADD CONSTRAINT account_offsets_ibfk_1 FOREIGN KEY (debit_id) REFERENCES account_debits (debit_id) ON DELETE CASCADE ON UPDATE CASCADE,
79
      ADD CONSTRAINT account_offsets_ibfk_2 FOREIGN KEY (credit_id) REFERENCES account_credits (credit_id) ON DELETE CASCADE ON UPDATE CASCADE;
80
");
81
82
$dbh->do("
83
    ALTER TABLE borrowers ADD account_balance DECIMAL( 28, 6 ) NOT NULL;
84
");
85
86
my $schema = Koha::Database->new()->schema;
87
my $debit_rs = $schema->resultset('AccountDebit');
88
my $credit_rs = $schema->resultset('AccountCredit');
89
my $issues_rs = $schema->resultset('Issue');
90
91
use Koha::Accounts::DebitTypes;
92
use Koha::Accounts::CreditTypes;
93
94
my $debit_types_map = {
95
    'A'    => Koha::Accounts::DebitTypes::AccountManagementFee,
96
    'F'    => Koha::Accounts::DebitTypes::Fine,
97
    'FU'   => Koha::Accounts::DebitTypes::Fine,
98
    'L'    => Koha::Accounts::DebitTypes::Lost,
99
    'M'    => Koha::Accounts::DebitTypes::Sundry,
100
    'N'    => Koha::Accounts::DebitTypes::NewCard,
101
    'Rent' => Koha::Accounts::DebitTypes::Rental,
102
};
103
104
my $credit_types_map = {
105
    'FOR' => Koha::Accounts::CreditTypes::Forgiven,
106
    'LR'  => Koha::Accounts::CreditTypes::Found,
107
    'Pay' => Koha::Accounts::CreditTypes::Payment,
108
    'PAY' => Koha::Accounts::CreditTypes::Payment,
109
    'WO'  => Koha::Accounts::CreditTypes::WriteOff,
110
    'W'   => Koha::Accounts::CreditTypes::WriteOff,
111
    'C'   => Koha::Accounts::CreditTypes::Credit,
112
    'CR'  => Koha::Accounts::CreditTypes::Credit,
113
};
114
115
my $sth = $dbh->prepare("SELECT * FROM accountlines");
116
$sth->execute();
117
while ( my $a = $sth->fetchrow_hashref() ) {
118
    if ( $debit_types_map->{ $a->{accounttype} } ) {
119
        $debit_rs->create(
120
            {
121
                borrowernumber     => $a->{borrowernumber},
122
                itemnumber         => $a->{itemnumber},
123
                amount_original    => $a->{amount},
124
                amount_outstanding => $a->{amountoutstanding},
125
                created_on         => $a->{timestamp},
126
                description        => $a->{description},
127
                notes              => $a->{note},
128
                manager_id         => $a->{manager_id},
129
                accruing           => $a->{accounttype} eq 'FU',
130
                type     => $debit_types_map->{ $a->{accounttype} },
131
                issue_id => $a->{accounttype} eq 'FU'
132
                ? $issues_rs->single(
133
                    {
134
                        borrowernumber => $a->{borrowernumber},
135
                        itemnumber     => $a->{itemnumber},
136
                    }
137
                  )->issue_id()
138
                : undef,
139
            }
140
        );
141
    }
142
    elsif ( $credit_types_map->{ $a->{accounttype} } ) {
143
        $credit_rs->create(
144
            {
145
                borrowernumber   => $a->{borrowernumber},
146
                amount_paid      => $a->{amount} * -1,
147
                amount_remaining => $a->{amountoutstanding} * -1,
148
                created_on       => $a->{timestamp},
149
                notes            => $a->{note},
150
                manager_id       => $a->{manager_id},
151
                type => $credit_types_map->{ $a->{accounttype} },
152
            }
153
        );
154
    }
155
    else {
156
        # Everything else must be a MANUAL_INV
157
        $debit_rs->create(
158
            {
159
                borrowernumber     => $a->{borrowernumber},
160
                itemnumber         => $a->{itemnumber},
161
                amount_original    => $a->{amount},
162
                amount_outstanding => $a->{amountoutstanding},
163
                created_on         => $a->{timestamp},
164
                description        => $a->{description},
165
                notes              => $a->{note},
166
                manager_id         => $a->{manager_id},
167
                type               => Koha::Accounts::DebitTypes::Sundry,
168
            }
169
        );
170
    }
171
}
172
173
print "Upgrade to $DBversion done ( Bug 6427 - Rewrite of the accounts system )\n";
(-)a/installer/data/mysql/kohastructure.sql (-44 / +90 lines)
Lines 265-270 CREATE TABLE `borrowers` ( -- this table includes information about your patrons Link Here
265
  `altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower
265
  `altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower
266
  `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on)
266
  `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on)
267
  `privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history
267
  `privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history
268
  `account_balance` decimal(28,6) NOT NULL,
268
  UNIQUE KEY `cardnumber` (`cardnumber`),
269
  UNIQUE KEY `cardnumber` (`cardnumber`),
269
  PRIMARY KEY `borrowernumber` (`borrowernumber`),
270
  PRIMARY KEY `borrowernumber` (`borrowernumber`),
270
  KEY `categorycode` (`categorycode`),
271
  KEY `categorycode` (`categorycode`),
Lines 2726-2775 CREATE TABLE `messages` ( -- circulation messages left via the patron's check ou Link Here
2726
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
2727
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
2727
2728
2728
--
2729
--
2729
-- Table structure for table `accountlines`
2730
--
2731
2732
DROP TABLE IF EXISTS `accountlines`;
2733
CREATE TABLE `accountlines` (
2734
  `accountlines_id` int(11) NOT NULL AUTO_INCREMENT,
2735
  `borrowernumber` int(11) NOT NULL default 0,
2736
  `accountno` smallint(6) NOT NULL default 0,
2737
  `itemnumber` int(11) default NULL,
2738
  `date` date default NULL,
2739
  `amount` decimal(28,6) default NULL,
2740
  `description` mediumtext,
2741
  `dispute` mediumtext,
2742
  `accounttype` varchar(5) default NULL,
2743
  `amountoutstanding` decimal(28,6) default NULL,
2744
  `lastincrement` decimal(28,6) default NULL,
2745
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2746
  `notify_id` int(11) NOT NULL default 0,
2747
  `notify_level` int(2) NOT NULL default 0,
2748
  `note` text NULL default NULL,
2749
  `manager_id` int(11) NULL,
2750
  PRIMARY KEY (`accountlines_id`),
2751
  KEY `acctsborridx` (`borrowernumber`),
2752
  KEY `timeidx` (`timestamp`),
2753
  KEY `itemnumber` (`itemnumber`),
2754
  CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2755
  CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2756
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
2757
2758
--
2759
-- Table structure for table `accountoffsets`
2760
--
2761
2762
DROP TABLE IF EXISTS `accountoffsets`;
2763
CREATE TABLE `accountoffsets` (
2764
  `borrowernumber` int(11) NOT NULL default 0,
2765
  `accountno` smallint(6) NOT NULL default 0,
2766
  `offsetaccount` smallint(6) NOT NULL default 0,
2767
  `offsetamount` decimal(28,6) default NULL,
2768
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2769
  CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2770
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
2771
2772
--
2773
-- Table structure for table `action_logs`
2730
-- Table structure for table `action_logs`
2774
--
2731
--
2775
2732
Lines 3519-3524 CREATE TABLE discharges ( Link Here
3519
  CONSTRAINT borrower_discharges_ibfk1 FOREIGN KEY (borrower) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE
3476
  CONSTRAINT borrower_discharges_ibfk1 FOREIGN KEY (borrower) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE
3520
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
3477
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
3521
3478
3479
--
3480
-- Table structure for table 'account_credits'
3481
--
3482
DROP TABLE IF EXISTS account_credits;
3483
CREATE TABLE IF account_credits (
3484
    credit_id int(11) NOT NULL AUTO_INCREMENT,     -- The unique id for this credit
3485
    borrowernumber int(11) NOT NULL,               -- The borrower this credit applies to
3486
    `type` varchar(255) NOT NULL,                  -- The type of credit this is ( defined by Koha::Accounts::CreditTypes )
3487
    amount_received decimal(28,6) DEFAULT NULL,    -- If this was a cash payment, the amount of money given
3488
    amount_paid decimal(28,6) NOT NULL,            -- The actual ammount paid, if less than amount_recieved, change was given back
3489
    amount_remaining decimal(28,6) NOT NULL,       -- The amount of this credit that has not been applied to outstanding debits
3490
    amount_voided decimal(28,6) NULL DEFAULT NULL, -- The amount of this credit was for before it was voided
3491
    notes text,                                    -- Misc notes for this credit
3492
    branchcode VARCHAR( 10 ) NULL DEFAULT NULL,    -- Branchcode where the credit was created ( if any )
3493
    manager_id int(11) DEFAULT NULL,               -- The borrowernumber of the user who created this credit ( if any )
3494
    created_on timestamp NULL DEFAULT NULL,        -- Timestamp for when this credit was created
3495
    updated_on timestamp NULL DEFAULT NULL,        -- Timestamp for when this credit was last modified
3496
    PRIMARY KEY (credit_id),
3497
    KEY borrowernumber (borrowernumber),
3498
    KEY branchcode (branchcode)
3499
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
3500
3501
--
3502
-- Constraints for table `account_credits`
3503
--
3504
ALTER TABLE `account_credits`
3505
  ADD CONSTRAINT account_credits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
3506
  ADD CONSTRAINT account_credits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE;
3507
3508
--
3509
-- Table structure for table 'account_debits'
3510
--
3511
3512
DROP TABLE IF EXISTS account_debits;
3513
CREATE TABLE account_debits (
3514
    debit_id int(11) NOT NULL AUTO_INCREMENT,           -- The unique id for this debit
3515
    borrowernumber int(11) NOT NULL DEFAULT '0',        -- The borrower this debit applies to
3516
    itemnumber int(11) DEFAULT NULL,                    -- The item related to this debit ( for fines, lost fees, etc )
3517
    issue_id int(11) DEFAULT NULL,                      -- The checkout this debit is related to ( again, for fines, lost fees, etc )
3518
    `type` varchar(255) NOT NULL,                       -- The type of debit this is ( defined by Koha::Accounts::DebitTypes )
3519
    accruing tinyint(1) NOT NULL DEFAULT '0',           -- Boolean flag, tells of if this is a fine that is still accruing
3520
    amount_original decimal(28,6) DEFAULT NULL,         -- The total amount of this debit
3521
    amount_outstanding decimal(28,6) DEFAULT NULL,      -- The amount still owed on this debit
3522
    amount_last_increment decimal(28,6) DEFAULT NULL,   -- The amount by which this debit last changed
3523
    description mediumtext,                             -- The description for this debit
3524
    notes text,                                         -- Misc notes for this debit
3525
    branchcode VARCHAR( 10 ) NULL DEFAULT NULL,         -- Branchcode where the debit was created ( if any )
3526
    manager_id int(11) DEFAULT NULL,                    -- The borrowernumber of the user who created this debit ( if any )
3527
    created_on timestamp NULL DEFAULT NULL,             -- Timestamp for when this credit was created
3528
    updated_on timestamp NULL DEFAULT NULL,             -- Timestamp for when this credit was last modified
3529
    PRIMARY KEY (debit_id),
3530
    KEY acctsborridx (borrowernumber),
3531
    KEY itemnumber (itemnumber),
3532
    KEY borrowernumber (borrowernumber),
3533
    KEY issue_id (issue_id),
3534
    KEY branchcode (branchcode)
3535
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
3536
3537
--
3538
-- Constraints for table `account_debits`
3539
--
3540
ALTER TABLE `account_debits`
3541
    ADD CONSTRAINT account_debits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
3542
    ADD CONSTRAINT account_debits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE;
3543
3544
--
3545
-- Table structure for table 'account_offsets'
3546
--
3547
3548
DROP TABLE IF EXISTS account_offsets;
3549
CREATE TABLE account_offsets (
3550
    offset_id int(11) NOT NULL AUTO_INCREMENT,                                              -- Unique id for this offset
3551
    debit_id int(11) DEFAULT NULL,                                                          -- Related debit
3552
    credit_id int(11) DEFAULT NULL,                                                         -- Related credit ( if any )
3553
    `type` varchar(255) DEFAULT NULL,                                                       -- The type of this offset ( defined by Koha::Accounts::OffsetTypes ), if any
3554
    amount decimal(28,6) NOT NULL,                                                          -- The amount of the offset, positive means patron owes more, negative means patron owes less
3555
    created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,    -- Timestamp for when this offset was created
3556
    PRIMARY KEY (offset_id),
3557
    KEY fee_id (debit_id),
3558
    KEY payment_id (credit_id)
3559
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
3560
3561
--
3562
-- Constraints for table `account_offsets`
3563
--
3564
ALTER TABLE `account_offsets`
3565
    ADD CONSTRAINT account_offsets_ibfk_1 FOREIGN KEY (debit_id) REFERENCES account_debits (debit_id) ON DELETE CASCADE ON UPDATE CASCADE,
3566
    ADD CONSTRAINT account_offsets_ibfk_2 FOREIGN KEY (credit_id) REFERENCES account_credits (credit_id) ON DELETE CASCADE ON UPDATE CASCADE;
3567
3522
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
3568
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
3523
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
3569
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
3524
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
3570
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
(-)a/installer/data/mysql/updatedatabase.pl (-3 / +3 lines)
Lines 38-44 use C4::Context; Link Here
38
use C4::Installer;
38
use C4::Installer;
39
use C4::Dates;
39
use C4::Dates;
40
use Koha::Database;
40
use Koha::Database;
41
42
use Koha;
41
use Koha;
43
42
44
use MARC::Record;
43
use MARC::Record;
Lines 7304-7309 if ( CheckVersion($DBversion) ) { Link Here
7304
7303
7305
    $dbh->{AutoCommit} = 1;
7304
    $dbh->{AutoCommit} = 1;
7306
    $dbh->{RaiseError} = 0;
7305
    $dbh->{RaiseError} = 0;
7306
   SetVersion ($DBversion);
7307
}
7307
}
7308
7308
7309
$DBversion = "3.13.00.031";
7309
$DBversion = "3.13.00.031";
Lines 8861-8867 if ( CheckVersion($DBversion) ) { Link Here
8861
    $dbh->do("ALTER TABLE  `biblioitems` CHANGE  `cn_sort`  `cn_sort` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL");
8861
    $dbh->do("ALTER TABLE  `biblioitems` CHANGE  `cn_sort`  `cn_sort` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL");
8862
    $dbh->do("ALTER TABLE  `deletedbiblioitems` CHANGE  `cn_sort`  `cn_sort` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL");
8862
    $dbh->do("ALTER TABLE  `deletedbiblioitems` CHANGE  `cn_sort`  `cn_sort` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL");
8863
    print "Upgrade to $DBversion done (Bug 12424 - ddc sorting of call numbers truncates long Cutter parts)\n";
8863
    print "Upgrade to $DBversion done (Bug 12424 - ddc sorting of call numbers truncates long Cutter parts)\n";
8864
    SetVersion ($DBversion);
8864
    SetVersion($DBversion);
8865
}
8865
}
8866
8866
8867
$DBversion = "3.17.00.030";
8867
$DBversion = "3.17.00.030";
Lines 10406-10411 foreach my $file ( sort readdir $dirh ) { Link Here
10406
        my $rv = $installer->load_sql( $update_dir . $file ) ? 0 : 1;
10406
        my $rv = $installer->load_sql( $update_dir . $file ) ? 0 : 1;
10407
    } elsif ( $file =~ /\.perl$/ ) {
10407
    } elsif ( $file =~ /\.perl$/ ) {
10408
        do $update_dir . $file;
10408
        do $update_dir . $file;
10409
        warn $@ if $@;
10409
    }
10410
    }
10410
}
10411
}
10411
10412
10412
- 

Return to bug 6427