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

(-)a/installer/data/mysql/kohastructure.sql (-44 / +91 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 2702-2751 CREATE TABLE `messages` ( -- circulation messages left via the patron's check ou Link Here
2702
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2703
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2703
2704
2704
--
2705
--
2705
-- Table structure for table `accountlines`
2706
--
2707
2708
DROP TABLE IF EXISTS `accountlines`;
2709
CREATE TABLE `accountlines` (
2710
  `accountlines_id` int(11) NOT NULL AUTO_INCREMENT,
2711
  `borrowernumber` int(11) NOT NULL default 0,
2712
  `accountno` smallint(6) NOT NULL default 0,
2713
  `itemnumber` int(11) default NULL,
2714
  `date` date default NULL,
2715
  `amount` decimal(28,6) default NULL,
2716
  `description` mediumtext,
2717
  `dispute` mediumtext,
2718
  `accounttype` varchar(5) default NULL,
2719
  `amountoutstanding` decimal(28,6) default NULL,
2720
  `lastincrement` decimal(28,6) default NULL,
2721
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2722
  `notify_id` int(11) NOT NULL default 0,
2723
  `notify_level` int(2) NOT NULL default 0,
2724
  `note` text NULL default NULL,
2725
  `manager_id` int(11) NULL,
2726
  PRIMARY KEY (`accountlines_id`),
2727
  KEY `acctsborridx` (`borrowernumber`),
2728
  KEY `timeidx` (`timestamp`),
2729
  KEY `itemnumber` (`itemnumber`),
2730
  CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2731
  CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2732
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2733
2734
--
2735
-- Table structure for table `accountoffsets`
2736
--
2737
2738
DROP TABLE IF EXISTS `accountoffsets`;
2739
CREATE TABLE `accountoffsets` (
2740
  `borrowernumber` int(11) NOT NULL default 0,
2741
  `accountno` smallint(6) NOT NULL default 0,
2742
  `offsetaccount` smallint(6) NOT NULL default 0,
2743
  `offsetamount` decimal(28,6) default NULL,
2744
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2745
  CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2746
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2747
2748
--
2749
-- Table structure for table `action_logs`
2706
-- Table structure for table `action_logs`
2750
--
2707
--
2751
2708
Lines 3422-3427 CREATE TABLE IF NOT EXISTS `misc_files` ( -- miscellaneous files attached to rec Link Here
3422
  KEY `record_id` (`record_id`)
3379
  KEY `record_id` (`record_id`)
3423
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3380
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3424
3381
3382
--
3383
-- Table structure for table 'account_credits'
3384
--
3385
DROP TABLE IF EXISTS account_credits;
3386
CREATE TABLE IF account_credits (
3387
    credit_id int(11) NOT NULL AUTO_INCREMENT,     -- The unique id for this credit
3388
    borrowernumber int(11) NOT NULL,               -- The borrower this credit applies to
3389
    `type` varchar(255) NOT NULL,                  -- The type of credit this is ( defined by Koha::Accounts::CreditTypes )
3390
    amount_received decimal(28,6) DEFAULT NULL,    -- If this was a cash payment, the amount of money given
3391
    amount_paid decimal(28,6) NOT NULL,            -- The actual ammount paid, if less than amount_recieved, change was given back
3392
    amount_remaining decimal(28,6) NOT NULL,       -- The amount of this credit that has not been applied to outstanding debits
3393
    amount_voided decimal(28,6) NULL DEFAULT NULL, -- The amount of this credit was for before it was voided
3394
    notes text,                                    -- Misc notes for this credit
3395
    branchcode VARCHAR( 10 ) NULL DEFAULT NULL,    -- Branchcode where the credit was created ( if any )
3396
    manager_id int(11) DEFAULT NULL,               -- The borrowernumber of the user who created this credit ( if any )
3397
    created_on timestamp NULL DEFAULT NULL,        -- Timestamp for when this credit was created
3398
    updated_on timestamp NULL DEFAULT NULL,        -- Timestamp for when this credit was last modified
3399
    PRIMARY KEY (credit_id),
3400
    KEY borrowernumber (borrowernumber),
3401
    KEY branchcode (branchcode)
3402
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
3403
3404
--
3405
-- Constraints for table `account_credits`
3406
--
3407
ALTER TABLE `account_credits`
3408
  ADD CONSTRAINT account_credits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
3409
  ADD CONSTRAINT account_credits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE;
3410
3411
--
3412
-- Table structure for table 'account_debits'
3413
--
3414
3415
DROP TABLE IF EXISTS account_debits;
3416
CREATE TABLE account_debits (
3417
    debit_id int(11) NOT NULL AUTO_INCREMENT,           -- The unique id for this debit
3418
    borrowernumber int(11) NOT NULL DEFAULT '0',        -- The borrower this debit applies to
3419
    itemnumber int(11) DEFAULT NULL,                    -- The item related to this debit ( for fines, lost fees, etc )
3420
    issue_id int(11) DEFAULT NULL,                      -- The checkout this debit is related to ( again, for fines, lost fees, etc )
3421
    `type` varchar(255) NOT NULL,                       -- The type of debit this is ( defined by Koha::Accounts::DebitTypes )
3422
    accruing tinyint(1) NOT NULL DEFAULT '0',           -- Boolean flag, tells of if this is a fine that is still accruing
3423
    amount_original decimal(28,6) DEFAULT NULL,         -- The total amount of this debit
3424
    amount_outstanding decimal(28,6) DEFAULT NULL,      -- The amount still owed on this debit
3425
    amount_last_increment decimal(28,6) DEFAULT NULL,   -- The amount by which this debit last changed
3426
    description mediumtext,                             -- The description for this debit
3427
    notes text,                                         -- Misc notes for this debit
3428
    branchcode VARCHAR( 10 ) NULL DEFAULT NULL,         -- Branchcode where the debit was created ( if any )
3429
    manager_id int(11) DEFAULT NULL,                    -- The borrowernumber of the user who created this debit ( if any )
3430
    created_on timestamp NULL DEFAULT NULL,             -- Timestamp for when this credit was created
3431
    updated_on timestamp NULL DEFAULT NULL,             -- Timestamp for when this credit was last modified
3432
    PRIMARY KEY (debit_id),
3433
    KEY acctsborridx (borrowernumber),
3434
    KEY itemnumber (itemnumber),
3435
    KEY borrowernumber (borrowernumber),
3436
    KEY issue_id (issue_id),
3437
    KEY branchcode (branchcode)
3438
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
3439
3440
--
3441
-- Constraints for table `account_debits`
3442
--
3443
ALTER TABLE `account_debits`
3444
    ADD CONSTRAINT account_debits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
3445
    ADD CONSTRAINT account_debits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE;
3446
3447
--
3448
-- Table structure for table 'account_offsets'
3449
--
3450
3451
DROP TABLE IF EXISTS account_offsets;
3452
CREATE TABLE account_offsets (
3453
    offset_id int(11) NOT NULL AUTO_INCREMENT,                                              -- Unique id for this offset
3454
    debit_id int(11) DEFAULT NULL,                                                          -- Related debit
3455
    credit_id int(11) DEFAULT NULL,                                                         -- Related credit ( if any )
3456
    `type` varchar(255) DEFAULT NULL,                                                       -- The type of this offset ( defined by Koha::Accounts::OffsetTypes ), if any
3457
    amount decimal(28,6) NOT NULL,                                                          -- The amount of the offset, positive means patron owes more, negative means patron owes less
3458
    created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,    -- Timestamp for when this offset was created
3459
    PRIMARY KEY (offset_id),
3460
    KEY fee_id (debit_id),
3461
    KEY payment_id (credit_id)
3462
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
3463
3464
--
3465
-- Constraints for table `account_offsets`
3466
--
3467
ALTER TABLE `account_offsets`
3468
    ADD CONSTRAINT account_offsets_ibfk_1 FOREIGN KEY (debit_id) REFERENCES account_debits (debit_id) ON DELETE CASCADE ON UPDATE CASCADE,
3469
    ADD CONSTRAINT account_offsets_ibfk_2 FOREIGN KEY (credit_id) REFERENCES account_credits (credit_id) ON DELETE CASCADE ON UPDATE CASCADE;
3470
3471
3425
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
3472
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
3426
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
3473
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
3427
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
3474
/*!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 7257-7262 if ( CheckVersion($DBversion) ) { Link Here
7257
7258
7258
    $dbh->{AutoCommit} = 1;
7259
    $dbh->{AutoCommit} = 1;
7259
    $dbh->{RaiseError} = 0;
7260
    $dbh->{RaiseError} = 0;
7261
   SetVersion ($DBversion);
7260
}
7262
}
7261
7263
7262
$DBversion = "3.13.00.031";
7264
$DBversion = "3.13.00.031";
Lines 8574-8579 $DBversion = "3.17.00.010"; Link Here
8574
if ( CheckVersion($DBversion) ) {
8576
if ( CheckVersion($DBversion) ) {
8575
    $dbh->do("DELETE FROM systempreferences WHERE variable='opacsmallimage'");
8577
    $dbh->do("DELETE FROM systempreferences WHERE variable='opacsmallimage'");
8576
    print "Upgrade to $DBversion done (Bug 11347 - PROG/CCSR deprecation: Remove opacsmallimage system preference)\n";
8578
    print "Upgrade to $DBversion done (Bug 11347 - PROG/CCSR deprecation: Remove opacsmallimage system preference)\n";
8579
}
8580
8581
$DBversion = "3.17.00.XXX";
8582
if ( CheckVersion($DBversion) ) {
8583
    $dbh->do(q{
8584
        ALTER TABLE old_issues ADD issue_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
8585
    });
8586
    $dbh->do(q{
8587
        ALTER TABLE issues ADD issue_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
8588
    });
8589
    $dbh->do(q{
8590
        UPDATE issues SET issue_id = issue_id + ( SELECT COUNT(*) FROM old_issues ) ORDER BY issue_id DESC
8591
    });
8592
8593
    $dbh->do("
8594
        CREATE TABLE IF NOT EXISTS account_credits (
8595
            credit_id int(11) NOT NULL AUTO_INCREMENT,
8596
            borrowernumber int(11) NOT NULL,
8597
            `type` varchar(255) NOT NULL,
8598
            amount_received decimal(28,6) DEFAULT NULL,
8599
            amount_paid decimal(28,6) NOT NULL,
8600
            amount_remaining decimal(28,6) NOT NULL,
8601
            amount_voided decimal(28,6) NULL DEFAULT NULL,
8602
            notes text,
8603
            branchcode VARCHAR( 10 ) NULL DEFAULT NULL,
8604
            manager_id int(11) DEFAULT NULL,
8605
            created_on timestamp NULL DEFAULT NULL,
8606
            updated_on timestamp NULL DEFAULT NULL,
8607
            PRIMARY KEY (credit_id),
8608
            KEY borrowernumber (borrowernumber),
8609
            KEY branchcode (branchcode)
8610
        ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
8611
    ");
8612
    $dbh->do("
8613
        CREATE TABLE IF NOT EXISTS account_debits (
8614
            debit_id int(11) NOT NULL AUTO_INCREMENT,
8615
            borrowernumber int(11) NOT NULL DEFAULT '0',
8616
            itemnumber int(11) DEFAULT NULL,
8617
            issue_id int(11) DEFAULT NULL,
8618
            `type` varchar(255) NOT NULL,
8619
            accruing tinyint(1) NOT NULL DEFAULT '0',
8620
            amount_original decimal(28,6) DEFAULT NULL,
8621
            amount_outstanding decimal(28,6) DEFAULT NULL,
8622
            amount_last_increment decimal(28,6) DEFAULT NULL,
8623
            description mediumtext,
8624
            notes text,
8625
            branchcode VARCHAR( 10 ) NULL DEFAULT NULL,
8626
            manager_id int(11) DEFAULT NULL,
8627
            created_on timestamp NULL DEFAULT NULL,
8628
            updated_on timestamp NULL DEFAULT NULL,
8629
            PRIMARY KEY (debit_id),
8630
            KEY acctsborridx (borrowernumber),
8631
            KEY itemnumber (itemnumber),
8632
            KEY borrowernumber (borrowernumber),
8633
            KEY issue_id (issue_id),
8634
            KEY branchcode (branchcode)
8635
        ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
8636
    ");
8637
8638
    $dbh->do("
8639
        CREATE TABLE account_offsets (
8640
            offset_id int(11) NOT NULL AUTO_INCREMENT,
8641
            debit_id int(11) DEFAULT NULL,
8642
            credit_id int(11) DEFAULT NULL,
8643
            `type` varchar(255) DEFAULT NULL,
8644
            amount decimal(28,6) NOT NULL COMMENT 'A positive number here represents a payment, a negative is a increase in a fine.',
8645
            created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
8646
            PRIMARY KEY (offset_id),
8647
            KEY fee_id (debit_id),
8648
            KEY payment_id (credit_id)
8649
        ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
8650
    ");
8651
8652
    $dbh->do("
8653
        ALTER TABLE `account_credits`
8654
          ADD CONSTRAINT account_credits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
8655
          ADD CONSTRAINT account_credits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE;
8656
    ");
8657
    $dbh->do("
8658
        ALTER TABLE `account_debits`
8659
          ADD CONSTRAINT account_debits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
8660
          ADD CONSTRAINT account_debits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE;
8661
    ");
8662
    $dbh->do("
8663
        ALTER TABLE `account_offsets`
8664
          ADD CONSTRAINT account_offsets_ibfk_1 FOREIGN KEY (debit_id) REFERENCES account_debits (debit_id) ON DELETE CASCADE ON UPDATE CASCADE,
8665
          ADD CONSTRAINT account_offsets_ibfk_2 FOREIGN KEY (credit_id) REFERENCES account_credits (credit_id) ON DELETE CASCADE ON UPDATE CASCADE;
8666
    ");
8667
8668
    $dbh->do("
8669
        ALTER TABLE borrowers ADD account_balance DECIMAL( 28, 6 ) NOT NULL;
8670
    ");
8671
8672
    my $schema = Koha::Database->new()->schema;
8673
    my $debit_rs = $schema->resultset('AccountDebit');
8674
    my $credit_rs = $schema->resultset('AccountCredit');
8675
    my $issues_rs = $schema->resultset('Issue');
8676
8677
    use Koha::Accounts::DebitTypes;
8678
    use Koha::Accounts::CreditTypes;
8679
8680
    my $debit_types_map = {
8681
        'A'    => Koha::Accounts::DebitTypes::AccountManagementFee,
8682
        'F'    => Koha::Accounts::DebitTypes::Fine,
8683
        'FU'   => Koha::Accounts::DebitTypes::Fine,
8684
        'L'    => Koha::Accounts::DebitTypes::Lost,
8685
        'M'    => Koha::Accounts::DebitTypes::Sundry,
8686
        'N'    => Koha::Accounts::DebitTypes::NewCard,
8687
        'Rent' => Koha::Accounts::DebitTypes::Rental,
8688
    };
8689
8690
    my $credit_types_map = {
8691
        'FOR' => Koha::Accounts::CreditTypes::Forgiven,
8692
        'LR'  => Koha::Accounts::CreditTypes::Found,
8693
        'Pay' => Koha::Accounts::CreditTypes::Payment,
8694
        'PAY' => Koha::Accounts::CreditTypes::Payment,
8695
        'WO'  => Koha::Accounts::CreditTypes::WriteOff,
8696
        'W'   => Koha::Accounts::CreditTypes::WriteOff,
8697
        'C'   => Koha::Accounts::CreditTypes::Credit,
8698
        'CR'  => Koha::Accounts::CreditTypes::Credit,
8699
    };
8700
8701
    my $sth = $dbh->prepare("SELECT * FROM accountlines");
8702
    $sth->execute();
8703
    while ( my $a = $sth->fetchrow_hashref() ) {
8704
        if ( $debit_types_map->{ $a->{accounttype} } ) {
8705
            $debit_rs->create(
8706
                {
8707
                    borrowernumber     => $a->{borrowernumber},
8708
                    itemnumber         => $a->{itemnumber},
8709
                    amount_original    => $a->{amount},
8710
                    amount_outstanding => $a->{amountoutstanding},
8711
                    created_on         => $a->{timestamp},
8712
                    description        => $a->{description},
8713
                    notes              => $a->{note},
8714
                    manager_id         => $a->{manager_id},
8715
                    accruing           => $a->{accounttype} eq 'FU',
8716
                    type     => $debit_types_map->{ $a->{accounttype} },
8717
                    issue_id => $a->{accounttype} eq 'FU'
8718
                    ? $issues_rs->single(
8719
                        {
8720
                            borrowernumber => $a->{borrowernumber},
8721
                            itemnumber     => $a->{itemnumber},
8722
                        }
8723
                      )->issue_id()
8724
                    : undef,
8725
                }
8726
            );
8727
        }
8728
        elsif ( $credit_types_map->{ $a->{accounttype} } ) {
8729
            $credit_rs->create(
8730
                {
8731
                    borrowernumber   => $a->{borrowernumber},
8732
                    amount_paid      => $a->{amount} * -1,
8733
                    amount_remaining => $a->{amountoutstanding} * -1,
8734
                    created_on       => $a->{timestamp},
8735
                    notes            => $a->{note},
8736
                    manager_id       => $a->{manager_id},
8737
                    type => $credit_types_map->{ $a->{accounttype} },
8738
                }
8739
            );
8740
        }
8741
        else {
8742
            # Everything else must be a MANUAL_INV
8743
            $debit_rs->create(
8744
                {
8745
                    borrowernumber     => $a->{borrowernumber},
8746
                    itemnumber         => $a->{itemnumber},
8747
                    amount_original    => $a->{amount},
8748
                    amount_outstanding => $a->{amountoutstanding},
8749
                    created_on         => $a->{timestamp},
8750
                    description        => $a->{description},
8751
                    notes              => $a->{note},
8752
                    manager_id         => $a->{manager_id},
8753
                    type               => Koha::Accounts::DebitTypes::Sundry,
8754
                }
8755
            );
8756
        }
8757
    }
8758
8759
    print "Upgrade to $DBversion done ( Bug 6427 - Rewrite of the accounts system )\n";
8577
    SetVersion ($DBversion);
8760
    SetVersion ($DBversion);
8578
}
8761
}
8579
8762
8580
- 

Return to bug 6427