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

(-)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 2673-2722 CREATE TABLE `messages` ( -- circulation messages left via the patron's check ou Link Here
2673
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2674
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2674
2675
2675
--
2676
--
2676
-- Table structure for table `accountlines`
2677
--
2678
2679
DROP TABLE IF EXISTS `accountlines`;
2680
CREATE TABLE `accountlines` (
2681
  `accountlines_id` int(11) NOT NULL AUTO_INCREMENT,
2682
  `borrowernumber` int(11) NOT NULL default 0,
2683
  `accountno` smallint(6) NOT NULL default 0,
2684
  `itemnumber` int(11) default NULL,
2685
  `date` date default NULL,
2686
  `amount` decimal(28,6) default NULL,
2687
  `description` mediumtext,
2688
  `dispute` mediumtext,
2689
  `accounttype` varchar(5) default NULL,
2690
  `amountoutstanding` decimal(28,6) default NULL,
2691
  `lastincrement` decimal(28,6) default NULL,
2692
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2693
  `notify_id` int(11) NOT NULL default 0,
2694
  `notify_level` int(2) NOT NULL default 0,
2695
  `note` text NULL default NULL,
2696
  `manager_id` int(11) NULL,
2697
  PRIMARY KEY (`accountlines_id`),
2698
  KEY `acctsborridx` (`borrowernumber`),
2699
  KEY `timeidx` (`timestamp`),
2700
  KEY `itemnumber` (`itemnumber`),
2701
  CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2702
  CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2703
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2704
2705
--
2706
-- Table structure for table `accountoffsets`
2707
--
2708
2709
DROP TABLE IF EXISTS `accountoffsets`;
2710
CREATE TABLE `accountoffsets` (
2711
  `borrowernumber` int(11) NOT NULL default 0,
2712
  `accountno` smallint(6) NOT NULL default 0,
2713
  `offsetaccount` smallint(6) NOT NULL default 0,
2714
  `offsetamount` decimal(28,6) default NULL,
2715
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2716
  CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2717
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2718
2719
--
2720
-- Table structure for table `action_logs`
2677
-- Table structure for table `action_logs`
2721
--
2678
--
2722
2679
Lines 3382-3387 CREATE TABLE IF NOT EXISTS marc_modification_template_actions ( Link Here
3382
  CONSTRAINT `mmta_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `marc_modification_templates` (`template_id`) ON DELETE CASCADE ON UPDATE CASCADE
3339
  CONSTRAINT `mmta_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `marc_modification_templates` (`template_id`) ON DELETE CASCADE ON UPDATE CASCADE
3383
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
3340
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
3384
3341
3342
--
3343
-- Table structure for table 'account_credits'
3344
--
3345
DROP TABLE IF EXISTS account_credits;
3346
CREATE TABLE IF account_credits (
3347
    credit_id int(11) NOT NULL AUTO_INCREMENT,  -- The unique id for this credit
3348
    borrowernumber int(11) NOT NULL,            -- The borrower this credit applies to
3349
    `type` varchar(255) NOT NULL,               -- The type of credit this is ( defined by Koha::Accounts::CreditTypes )
3350
    amount_received decimal(28,6) DEFAULT NULL, -- If this was a cash payment, the amount of money given
3351
    amount_paid decimal(28,6) NOT NULL,         -- The actual ammount paid, if less than amount_recieved, change was given back
3352
    amount_remaining decimal(28,6) NOT NULL,    -- The amount of this credit that has not been applied to outstanding debits
3353
    notes text,                                 -- Misc notes for this credit
3354
    branchcode VARCHAR( 10 ) NULL DEFAULT NULL, -- Branchcode where the credit was created ( if any )
3355
    manager_id int(11) DEFAULT NULL,            -- The borrowernumber of the user who created this credit ( if any )
3356
    created_on timestamp NULL DEFAULT NULL,     -- Timestamp for when this credit was created
3357
    updated_on timestamp NULL DEFAULT NULL,     -- Timestamp for when this credit was last modified
3358
    PRIMARY KEY (credit_id),
3359
    KEY borrowernumber (borrowernumber),
3360
    KEY branchcode (branchcode)
3361
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
3362
3363
--
3364
-- Constraints for table `account_credits`
3365
--
3366
ALTER TABLE `account_credits`
3367
  ADD CONSTRAINT account_credits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
3368
  ADD CONSTRAINT account_credits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE;
3369
3370
--
3371
-- Table structure for table 'account_debits'
3372
--
3373
3374
DROP TABLE IF EXISTS account_debits;
3375
CREATE TABLE account_debits (
3376
    debit_id int(11) NOT NULL AUTO_INCREMENT,           -- The unique id for this debit
3377
    borrowernumber int(11) NOT NULL DEFAULT '0',        -- The borrower this debit applies to
3378
    itemnumber int(11) DEFAULT NULL,                    -- The item related to this debit ( for fines, lost fees, etc )
3379
    issue_id int(11) DEFAULT NULL,                      -- The checkout this debit is related to ( again, for fines, lost fees, etc )
3380
    `type` varchar(255) NOT NULL,                       -- The type of debit this is ( defined by Koha::Accounts::DebitTypes )
3381
    accruing tinyint(1) NOT NULL DEFAULT '0',           -- Boolean flag, tells of if this is a fine that is still accruing
3382
    amount_original decimal(28,6) DEFAULT NULL,         -- The total amount of this debit
3383
    amount_outstanding decimal(28,6) DEFAULT NULL,      -- The amount still owed on this debit
3384
    amount_last_increment decimal(28,6) DEFAULT NULL,   -- The amount by which this debit last changed
3385
    description mediumtext,                             -- The description for this debit
3386
    notes text,                                         -- Misc notes for this debit
3387
    branchcode VARCHAR( 10 ) NULL DEFAULT NULL,         -- Branchcode where the debit was created ( if any )
3388
    manager_id int(11) DEFAULT NULL,                    -- The borrowernumber of the user who created this debit ( if any )
3389
    created_on timestamp NULL DEFAULT NULL,             -- Timestamp for when this credit was created
3390
    updated_on timestamp NULL DEFAULT NULL,             -- Timestamp for when this credit was last modified
3391
    PRIMARY KEY (debit_id),
3392
    KEY acctsborridx (borrowernumber),
3393
    KEY itemnumber (itemnumber),
3394
    KEY borrowernumber (borrowernumber),
3395
    KEY issue_id (issue_id),
3396
    KEY branchcode (branchcode)
3397
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
3398
3399
--
3400
-- Constraints for table `account_debits`
3401
--
3402
ALTER TABLE `account_debits`
3403
    ADD CONSTRAINT account_debits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
3404
    ADD CONSTRAINT account_debits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE;
3405
3406
--
3407
-- Table structure for table 'account_offsets'
3408
--
3409
3410
DROP TABLE IF EXISTS account_offsets;
3411
CREATE TABLE account_offsets (
3412
    offset_id int(11) NOT NULL AUTO_INCREMENT,                                              -- Unique id for this offset
3413
    debit_id int(11) DEFAULT NULL,                                                          -- Related debit
3414
    credit_id int(11) DEFAULT NULL,                                                         -- Related credit ( if any )
3415
    `type` varchar(255) DEFAULT NULL,                                                       -- The type of this offset ( defined by Koha::Accounts::OffsetTypes ), if any
3416
    amount decimal(28,6) NOT NULL,                                                          -- The amount of the offset, positive means patron owes more, negative means patron owes less
3417
    created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,    -- Timestamp for when this offset was created
3418
    PRIMARY KEY (offset_id),
3419
    KEY fee_id (debit_id),
3420
    KEY payment_id (credit_id)
3421
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
3422
3423
--
3424
-- Constraints for table `account_offsets`
3425
--
3426
ALTER TABLE `account_offsets`
3427
    ADD CONSTRAINT account_offsets_ibfk_1 FOREIGN KEY (debit_id) REFERENCES account_debits (debit_id) ON DELETE CASCADE ON UPDATE CASCADE,
3428
    ADD CONSTRAINT account_offsets_ibfk_2 FOREIGN KEY (credit_id) REFERENCES account_credits (credit_id) ON DELETE CASCADE ON UPDATE CASCADE;
3429
3430
3385
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
3431
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
3386
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
3432
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
3387
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
3433
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
(-)a/installer/data/mysql/updatedatabase.pl (-1 / +183 lines)
Lines 37-42 use Getopt::Long; Link Here
37
use C4::Context;
37
use C4::Context;
38
use C4::Installer;
38
use C4::Installer;
39
use C4::Dates;
39
use C4::Dates;
40
use Koha::Database;
40
41
41
use MARC::Record;
42
use MARC::Record;
42
use MARC::File::XML ( BinaryEncoding => 'utf8' );
43
use MARC::File::XML ( BinaryEncoding => 'utf8' );
Lines 7259-7264 if ( CheckVersion($DBversion) ) { Link Here
7259
7260
7260
    $dbh->{AutoCommit} = 1;
7261
    $dbh->{AutoCommit} = 1;
7261
    $dbh->{RaiseError} = 0;
7262
    $dbh->{RaiseError} = 0;
7263
   SetVersion ($DBversion);
7262
}
7264
}
7263
7265
7264
$DBversion = "3.13.00.031";
7266
$DBversion = "3.13.00.031";
Lines 8012-8017 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { Link Here
8012
    SetVersion($DBversion);
8014
    SetVersion($DBversion);
8013
}
8015
}
8014
8016
8017
$DBversion = "3.15.00.XXX";
8018
if ( CheckVersion($DBversion) ) {
8019
    $dbh->do(q{
8020
        ALTER TABLE old_issues ADD issue_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
8021
    });
8022
    $dbh->do(q{
8023
        ALTER TABLE issues ADD issue_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
8024
    });
8025
    $dbh->do(q{
8026
        UPDATE issues SET issue_id = issue_id + ( SELECT COUNT(*) FROM old_issues ) ORDER BY issue_id DESC
8027
    });
8028
8029
    $dbh->do("
8030
        CREATE TABLE IF NOT EXISTS account_credits (
8031
            credit_id int(11) NOT NULL AUTO_INCREMENT,
8032
            borrowernumber int(11) NOT NULL,
8033
            `type` varchar(255) NOT NULL,
8034
            amount_received decimal(28,6) DEFAULT NULL,
8035
            amount_paid decimal(28,6) NOT NULL,
8036
            amount_remaining decimal(28,6) NOT NULL,
8037
            notes text,
8038
            branchcode VARCHAR( 10 ) NULL DEFAULT NULL,
8039
            manager_id int(11) DEFAULT NULL,
8040
            created_on timestamp NULL DEFAULT NULL,
8041
            updated_on timestamp NULL DEFAULT NULL,
8042
            PRIMARY KEY (credit_id),
8043
            KEY borrowernumber (borrowernumber),
8044
            KEY branchcode (branchcode)
8045
        ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
8046
    ");
8047
    $dbh->do("
8048
        CREATE TABLE IF NOT EXISTS account_debits (
8049
            debit_id int(11) NOT NULL AUTO_INCREMENT,
8050
            borrowernumber int(11) NOT NULL DEFAULT '0',
8051
            itemnumber int(11) DEFAULT NULL,
8052
            issue_id int(11) DEFAULT NULL,
8053
            `type` varchar(255) NOT NULL,
8054
            accruing tinyint(1) NOT NULL DEFAULT '0',
8055
            amount_original decimal(28,6) DEFAULT NULL,
8056
            amount_outstanding decimal(28,6) DEFAULT NULL,
8057
            amount_last_increment decimal(28,6) DEFAULT NULL,
8058
            description mediumtext,
8059
            notes text,
8060
            branchcode VARCHAR( 10 ) NULL DEFAULT NULL,
8061
            manager_id int(11) DEFAULT NULL,
8062
            created_on timestamp NULL DEFAULT NULL,
8063
            updated_on timestamp NULL DEFAULT NULL,
8064
            PRIMARY KEY (debit_id),
8065
            KEY acctsborridx (borrowernumber),
8066
            KEY itemnumber (itemnumber),
8067
            KEY borrowernumber (borrowernumber),
8068
            KEY issue_id (issue_id),
8069
            KEY branchcode (branchcode)
8070
        ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
8071
    ");
8072
8073
    $dbh->do("
8074
        CREATE TABLE account_offsets (
8075
            offset_id int(11) NOT NULL AUTO_INCREMENT,
8076
            debit_id int(11) DEFAULT NULL,
8077
            credit_id int(11) DEFAULT NULL,
8078
            `type` varchar(255) DEFAULT NULL,
8079
            amount decimal(28,6) NOT NULL COMMENT 'A positive number here represents a payment, a negative is a increase in a fine.',
8080
            created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
8081
            PRIMARY KEY (offset_id),
8082
            KEY fee_id (debit_id),
8083
            KEY payment_id (credit_id)
8084
        ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
8085
    ");
8086
8087
    $dbh->do("
8088
        ALTER TABLE `account_credits`
8089
          ADD CONSTRAINT account_credits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
8090
          ADD CONSTRAINT account_credits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE;
8091
    ");
8092
    $dbh->do("
8093
        ALTER TABLE `account_debits`
8094
          ADD CONSTRAINT account_debits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
8095
          ADD CONSTRAINT account_debits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE;
8096
    ");
8097
    $dbh->do("
8098
        ALTER TABLE `account_offsets`
8099
          ADD CONSTRAINT account_offsets_ibfk_1 FOREIGN KEY (debit_id) REFERENCES account_debits (debit_id) ON DELETE CASCADE ON UPDATE CASCADE,
8100
          ADD CONSTRAINT account_offsets_ibfk_2 FOREIGN KEY (credit_id) REFERENCES account_credits (credit_id) ON DELETE CASCADE ON UPDATE CASCADE;
8101
    ");
8102
8103
    $dbh->do("
8104
        ALTER TABLE borrowers ADD account_balance DECIMAL( 28, 6 ) NOT NULL;
8105
    ");
8106
8107
    my $schema = Koha::Database->new()->schema;
8108
    my $debit_rs = $schema->resultset('AccountDebit');
8109
    my $credit_rs = $schema->resultset('AccountCredit');
8110
    my $issues_rs = $schema->resultset('Issue');
8111
8112
    use Koha::Accounts::DebitTypes;
8113
    use Koha::Accounts::CreditTypes;
8114
8115
    my $debit_types_map = {
8116
        'A'    => Koha::Accounts::DebitTypes::AccountManagementFee,
8117
        'F'    => Koha::Accounts::DebitTypes::Fine,
8118
        'FU'   => Koha::Accounts::DebitTypes::Fine,
8119
        'L'    => Koha::Accounts::DebitTypes::Lost,
8120
        'M'    => Koha::Accounts::DebitTypes::Sundry,
8121
        'N'    => Koha::Accounts::DebitTypes::NewCard,
8122
        'Rent' => Koha::Accounts::DebitTypes::Rental,
8123
    };
8124
8125
    my $credit_types_map = {
8126
        'FOR' => Koha::Accounts::CreditTypes::Forgiven,
8127
        'LR'  => Koha::Accounts::CreditTypes::Found,
8128
        'Pay' => Koha::Accounts::CreditTypes::Payment,
8129
        'PAY' => Koha::Accounts::CreditTypes::Payment,
8130
        'WO'  => Koha::Accounts::CreditTypes::WriteOff,
8131
        'W'   => Koha::Accounts::CreditTypes::WriteOff,
8132
        'C'   => Koha::Accounts::CreditTypes::Credit,
8133
        'CR'  => Koha::Accounts::CreditTypes::Credit,
8134
    };
8135
8136
    my $sth = $dbh->prepare("SELECT * FROM accountlines");
8137
    $sth->execute();
8138
    while ( my $a = $sth->fetchrow_hashref() ) {
8139
        if ( $debit_types_map->{ $a->{accounttype} } ) {
8140
            $debit_rs->create(
8141
                {
8142
                    borrowernumber     => $a->{borrowernumber},
8143
                    itemnumber         => $a->{itemnumber},
8144
                    amount_original    => $a->{amount},
8145
                    amount_outstanding => $a->{amountoutstanding},
8146
                    created_on         => $a->{timestamp},
8147
                    description        => $a->{description},
8148
                    notes              => $a->{note},
8149
                    manager_id         => $a->{manager_id},
8150
                    accruing           => $a->{accounttype} eq 'FU',
8151
                    type     => $debit_types_map->{ $a->{accounttype} },
8152
                    issue_id => $a->{accounttype} eq 'FU'
8153
                    ? $issues_rs->single(
8154
                        {
8155
                            borrowernumber => $a->{borrowernumber},
8156
                            itemnumber     => $a->{itemnumber},
8157
                        }
8158
                      )->issue_id()
8159
                    : undef,
8160
                }
8161
            );
8162
        }
8163
        elsif ( $credit_types_map->{ $a->{accounttype} } ) {
8164
            $credit_rs->create(
8165
                {
8166
                    borrowernumber   => $a->{borrowernumber},
8167
                    amount_paid      => $a->{amount} * -1,
8168
                    amount_remaining => $a->{amountoutstanding} * -1,
8169
                    created_on       => $a->{timestamp},
8170
                    notes            => $a->{note},
8171
                    manager_id       => $a->{manager_id},
8172
                    type => $credit_types_map->{ $a->{accounttype} },
8173
                }
8174
            );
8175
        }
8176
        else {
8177
            # Everything else must be a MANUAL_INV
8178
            $debit_rs->create(
8179
                {
8180
                    borrowernumber     => $a->{borrowernumber},
8181
                    itemnumber         => $a->{itemnumber},
8182
                    amount_original    => $a->{amount},
8183
                    amount_outstanding => $a->{amountoutstanding},
8184
                    created_on         => $a->{timestamp},
8185
                    description        => $a->{description},
8186
                    notes              => $a->{note},
8187
                    manager_id         => $a->{manager_id},
8188
                    type               => Koha::Accounts::DebitTypes::Sundry,
8189
                }
8190
            );
8191
        }
8192
    }
8193
8194
    print "Upgrade to $DBversion done ( Bug 6427 - Rewrite of the accounts system )\n";
8195
    SetVersion ($DBversion);
8196
}
8197
8015
=head1 FUNCTIONS
8198
=head1 FUNCTIONS
8016
8199
8017
=head2 TableExists($table)
8200
=head2 TableExists($table)
8018
- 

Return to bug 6427