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 2700-2749 CREATE TABLE `messages` ( -- circulation messages left via the patron's check ou Link Here
2700
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2701
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2701
2702
2702
--
2703
--
2703
-- Table structure for table `accountlines`
2704
--
2705
2706
DROP TABLE IF EXISTS `accountlines`;
2707
CREATE TABLE `accountlines` (
2708
  `accountlines_id` int(11) NOT NULL AUTO_INCREMENT,
2709
  `borrowernumber` int(11) NOT NULL default 0,
2710
  `accountno` smallint(6) NOT NULL default 0,
2711
  `itemnumber` int(11) default NULL,
2712
  `date` date default NULL,
2713
  `amount` decimal(28,6) default NULL,
2714
  `description` mediumtext,
2715
  `dispute` mediumtext,
2716
  `accounttype` varchar(5) default NULL,
2717
  `amountoutstanding` decimal(28,6) default NULL,
2718
  `lastincrement` decimal(28,6) default NULL,
2719
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2720
  `notify_id` int(11) NOT NULL default 0,
2721
  `notify_level` int(2) NOT NULL default 0,
2722
  `note` text NULL default NULL,
2723
  `manager_id` int(11) NULL,
2724
  PRIMARY KEY (`accountlines_id`),
2725
  KEY `acctsborridx` (`borrowernumber`),
2726
  KEY `timeidx` (`timestamp`),
2727
  KEY `itemnumber` (`itemnumber`),
2728
  CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2729
  CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2730
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2731
2732
--
2733
-- Table structure for table `accountoffsets`
2734
--
2735
2736
DROP TABLE IF EXISTS `accountoffsets`;
2737
CREATE TABLE `accountoffsets` (
2738
  `borrowernumber` int(11) NOT NULL default 0,
2739
  `accountno` smallint(6) NOT NULL default 0,
2740
  `offsetaccount` smallint(6) NOT NULL default 0,
2741
  `offsetamount` decimal(28,6) default NULL,
2742
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2743
  CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2744
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2745
2746
--
2747
-- Table structure for table `action_logs`
2704
-- Table structure for table `action_logs`
2748
--
2705
--
2749
2706
Lines 3402-3407 CREATE TABLE IF NOT EXISTS marc_modification_template_actions ( Link Here
3402
  CONSTRAINT `mmta_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `marc_modification_templates` (`template_id`) ON DELETE CASCADE ON UPDATE CASCADE
3359
  CONSTRAINT `mmta_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `marc_modification_templates` (`template_id`) ON DELETE CASCADE ON UPDATE CASCADE
3403
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
3360
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
3404
3361
3362
--
3363
-- Table structure for table 'account_credits'
3364
--
3365
DROP TABLE IF EXISTS account_credits;
3366
CREATE TABLE IF account_credits (
3367
    credit_id int(11) NOT NULL AUTO_INCREMENT,  -- The unique id for this credit
3368
    borrowernumber int(11) NOT NULL,            -- The borrower this credit applies to
3369
    `type` varchar(255) NOT NULL,               -- The type of credit this is ( defined by Koha::Accounts::CreditTypes )
3370
    amount_received decimal(28,6) DEFAULT NULL, -- If this was a cash payment, the amount of money given
3371
    amount_paid decimal(28,6) NOT NULL,         -- The actual ammount paid, if less than amount_recieved, change was given back
3372
    amount_remaining decimal(28,6) NOT NULL,    -- The amount of this credit that has not been applied to outstanding debits
3373
    notes text,                                 -- Misc notes for this credit
3374
    branchcode VARCHAR( 10 ) NULL DEFAULT NULL, -- Branchcode where the credit was created ( if any )
3375
    manager_id int(11) DEFAULT NULL,            -- The borrowernumber of the user who created this credit ( if any )
3376
    created_on timestamp NULL DEFAULT NULL,     -- Timestamp for when this credit was created
3377
    updated_on timestamp NULL DEFAULT NULL,     -- Timestamp for when this credit was last modified
3378
    PRIMARY KEY (credit_id),
3379
    KEY borrowernumber (borrowernumber),
3380
    KEY branchcode (branchcode)
3381
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
3382
3383
--
3384
-- Constraints for table `account_credits`
3385
--
3386
ALTER TABLE `account_credits`
3387
  ADD CONSTRAINT account_credits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
3388
  ADD CONSTRAINT account_credits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE;
3389
3390
--
3391
-- Table structure for table 'account_debits'
3392
--
3393
3394
DROP TABLE IF EXISTS account_debits;
3395
CREATE TABLE account_debits (
3396
    debit_id int(11) NOT NULL AUTO_INCREMENT,           -- The unique id for this debit
3397
    borrowernumber int(11) NOT NULL DEFAULT '0',        -- The borrower this debit applies to
3398
    itemnumber int(11) DEFAULT NULL,                    -- The item related to this debit ( for fines, lost fees, etc )
3399
    issue_id int(11) DEFAULT NULL,                      -- The checkout this debit is related to ( again, for fines, lost fees, etc )
3400
    `type` varchar(255) NOT NULL,                       -- The type of debit this is ( defined by Koha::Accounts::DebitTypes )
3401
    accruing tinyint(1) NOT NULL DEFAULT '0',           -- Boolean flag, tells of if this is a fine that is still accruing
3402
    amount_original decimal(28,6) DEFAULT NULL,         -- The total amount of this debit
3403
    amount_outstanding decimal(28,6) DEFAULT NULL,      -- The amount still owed on this debit
3404
    amount_last_increment decimal(28,6) DEFAULT NULL,   -- The amount by which this debit last changed
3405
    description mediumtext,                             -- The description for this debit
3406
    notes text,                                         -- Misc notes for this debit
3407
    branchcode VARCHAR( 10 ) NULL DEFAULT NULL,         -- Branchcode where the debit was created ( if any )
3408
    manager_id int(11) DEFAULT NULL,                    -- The borrowernumber of the user who created this debit ( if any )
3409
    created_on timestamp NULL DEFAULT NULL,             -- Timestamp for when this credit was created
3410
    updated_on timestamp NULL DEFAULT NULL,             -- Timestamp for when this credit was last modified
3411
    PRIMARY KEY (debit_id),
3412
    KEY acctsborridx (borrowernumber),
3413
    KEY itemnumber (itemnumber),
3414
    KEY borrowernumber (borrowernumber),
3415
    KEY issue_id (issue_id),
3416
    KEY branchcode (branchcode)
3417
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
3418
3419
--
3420
-- Constraints for table `account_debits`
3421
--
3422
ALTER TABLE `account_debits`
3423
    ADD CONSTRAINT account_debits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
3424
    ADD CONSTRAINT account_debits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE;
3425
3426
--
3427
-- Table structure for table 'account_offsets'
3428
--
3429
3430
DROP TABLE IF EXISTS account_offsets;
3431
CREATE TABLE account_offsets (
3432
    offset_id int(11) NOT NULL AUTO_INCREMENT,                                              -- Unique id for this offset
3433
    debit_id int(11) DEFAULT NULL,                                                          -- Related debit
3434
    credit_id int(11) DEFAULT NULL,                                                         -- Related credit ( if any )
3435
    `type` varchar(255) DEFAULT NULL,                                                       -- The type of this offset ( defined by Koha::Accounts::OffsetTypes ), if any
3436
    amount decimal(28,6) NOT NULL,                                                          -- The amount of the offset, positive means patron owes more, negative means patron owes less
3437
    created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,    -- Timestamp for when this offset was created
3438
    PRIMARY KEY (offset_id),
3439
    KEY fee_id (debit_id),
3440
    KEY payment_id (credit_id)
3441
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
3442
3443
--
3444
-- Constraints for table `account_offsets`
3445
--
3446
ALTER TABLE `account_offsets`
3447
    ADD CONSTRAINT account_offsets_ibfk_1 FOREIGN KEY (debit_id) REFERENCES account_debits (debit_id) ON DELETE CASCADE ON UPDATE CASCADE,
3448
    ADD CONSTRAINT account_offsets_ibfk_2 FOREIGN KEY (credit_id) REFERENCES account_credits (credit_id) ON DELETE CASCADE ON UPDATE CASCADE;
3449
3450
3405
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
3451
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
3406
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
3452
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
3407
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
3453
/*!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 8465-8470 if ( CheckVersion($DBversion) ) { Link Here
8465
    SetVersion($DBversion);
8467
    SetVersion($DBversion);
8466
}
8468
}
8467
8469
8470
$DBversion = "3.15.00.XXX";
8471
if ( CheckVersion($DBversion) ) {
8472
    $dbh->do(q{
8473
        ALTER TABLE old_issues ADD issue_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
8474
    });
8475
    $dbh->do(q{
8476
        ALTER TABLE issues ADD issue_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
8477
    });
8478
    $dbh->do(q{
8479
        UPDATE issues SET issue_id = issue_id + ( SELECT COUNT(*) FROM old_issues ) ORDER BY issue_id DESC
8480
    });
8481
8482
    $dbh->do("
8483
        CREATE TABLE IF NOT EXISTS account_credits (
8484
            credit_id int(11) NOT NULL AUTO_INCREMENT,
8485
            borrowernumber int(11) NOT NULL,
8486
            `type` varchar(255) NOT NULL,
8487
            amount_received decimal(28,6) DEFAULT NULL,
8488
            amount_paid decimal(28,6) NOT NULL,
8489
            amount_remaining decimal(28,6) NOT NULL,
8490
            notes text,
8491
            branchcode VARCHAR( 10 ) NULL DEFAULT NULL,
8492
            manager_id int(11) DEFAULT NULL,
8493
            created_on timestamp NULL DEFAULT NULL,
8494
            updated_on timestamp NULL DEFAULT NULL,
8495
            PRIMARY KEY (credit_id),
8496
            KEY borrowernumber (borrowernumber),
8497
            KEY branchcode (branchcode)
8498
        ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
8499
    ");
8500
    $dbh->do("
8501
        CREATE TABLE IF NOT EXISTS account_debits (
8502
            debit_id int(11) NOT NULL AUTO_INCREMENT,
8503
            borrowernumber int(11) NOT NULL DEFAULT '0',
8504
            itemnumber int(11) DEFAULT NULL,
8505
            issue_id int(11) DEFAULT NULL,
8506
            `type` varchar(255) NOT NULL,
8507
            accruing tinyint(1) NOT NULL DEFAULT '0',
8508
            amount_original decimal(28,6) DEFAULT NULL,
8509
            amount_outstanding decimal(28,6) DEFAULT NULL,
8510
            amount_last_increment decimal(28,6) DEFAULT NULL,
8511
            description mediumtext,
8512
            notes text,
8513
            branchcode VARCHAR( 10 ) NULL DEFAULT NULL,
8514
            manager_id int(11) DEFAULT NULL,
8515
            created_on timestamp NULL DEFAULT NULL,
8516
            updated_on timestamp NULL DEFAULT NULL,
8517
            PRIMARY KEY (debit_id),
8518
            KEY acctsborridx (borrowernumber),
8519
            KEY itemnumber (itemnumber),
8520
            KEY borrowernumber (borrowernumber),
8521
            KEY issue_id (issue_id),
8522
            KEY branchcode (branchcode)
8523
        ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
8524
    ");
8525
8526
    $dbh->do("
8527
        CREATE TABLE account_offsets (
8528
            offset_id int(11) NOT NULL AUTO_INCREMENT,
8529
            debit_id int(11) DEFAULT NULL,
8530
            credit_id int(11) DEFAULT NULL,
8531
            `type` varchar(255) DEFAULT NULL,
8532
            amount decimal(28,6) NOT NULL COMMENT 'A positive number here represents a payment, a negative is a increase in a fine.',
8533
            created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
8534
            PRIMARY KEY (offset_id),
8535
            KEY fee_id (debit_id),
8536
            KEY payment_id (credit_id)
8537
        ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
8538
    ");
8539
8540
    $dbh->do("
8541
        ALTER TABLE `account_credits`
8542
          ADD CONSTRAINT account_credits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
8543
          ADD CONSTRAINT account_credits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE;
8544
    ");
8545
    $dbh->do("
8546
        ALTER TABLE `account_debits`
8547
          ADD CONSTRAINT account_debits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
8548
          ADD CONSTRAINT account_debits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE;
8549
    ");
8550
    $dbh->do("
8551
        ALTER TABLE `account_offsets`
8552
          ADD CONSTRAINT account_offsets_ibfk_1 FOREIGN KEY (debit_id) REFERENCES account_debits (debit_id) ON DELETE CASCADE ON UPDATE CASCADE,
8553
          ADD CONSTRAINT account_offsets_ibfk_2 FOREIGN KEY (credit_id) REFERENCES account_credits (credit_id) ON DELETE CASCADE ON UPDATE CASCADE;
8554
    ");
8555
8556
    $dbh->do("
8557
        ALTER TABLE borrowers ADD account_balance DECIMAL( 28, 6 ) NOT NULL;
8558
    ");
8559
8560
    my $schema = Koha::Database->new()->schema;
8561
    my $debit_rs = $schema->resultset('AccountDebit');
8562
    my $credit_rs = $schema->resultset('AccountCredit');
8563
    my $issues_rs = $schema->resultset('Issue');
8564
8565
    use Koha::Accounts::DebitTypes;
8566
    use Koha::Accounts::CreditTypes;
8567
8568
    my $debit_types_map = {
8569
        'A'    => Koha::Accounts::DebitTypes::AccountManagementFee,
8570
        'F'    => Koha::Accounts::DebitTypes::Fine,
8571
        'FU'   => Koha::Accounts::DebitTypes::Fine,
8572
        'L'    => Koha::Accounts::DebitTypes::Lost,
8573
        'M'    => Koha::Accounts::DebitTypes::Sundry,
8574
        'N'    => Koha::Accounts::DebitTypes::NewCard,
8575
        'Rent' => Koha::Accounts::DebitTypes::Rental,
8576
    };
8577
8578
    my $credit_types_map = {
8579
        'FOR' => Koha::Accounts::CreditTypes::Forgiven,
8580
        'LR'  => Koha::Accounts::CreditTypes::Found,
8581
        'Pay' => Koha::Accounts::CreditTypes::Payment,
8582
        'PAY' => Koha::Accounts::CreditTypes::Payment,
8583
        'WO'  => Koha::Accounts::CreditTypes::WriteOff,
8584
        'W'   => Koha::Accounts::CreditTypes::WriteOff,
8585
        'C'   => Koha::Accounts::CreditTypes::Credit,
8586
        'CR'  => Koha::Accounts::CreditTypes::Credit,
8587
    };
8588
8589
    my $sth = $dbh->prepare("SELECT * FROM accountlines");
8590
    $sth->execute();
8591
    while ( my $a = $sth->fetchrow_hashref() ) {
8592
        if ( $debit_types_map->{ $a->{accounttype} } ) {
8593
            $debit_rs->create(
8594
                {
8595
                    borrowernumber     => $a->{borrowernumber},
8596
                    itemnumber         => $a->{itemnumber},
8597
                    amount_original    => $a->{amount},
8598
                    amount_outstanding => $a->{amountoutstanding},
8599
                    created_on         => $a->{timestamp},
8600
                    description        => $a->{description},
8601
                    notes              => $a->{note},
8602
                    manager_id         => $a->{manager_id},
8603
                    accruing           => $a->{accounttype} eq 'FU',
8604
                    type     => $debit_types_map->{ $a->{accounttype} },
8605
                    issue_id => $a->{accounttype} eq 'FU'
8606
                    ? $issues_rs->single(
8607
                        {
8608
                            borrowernumber => $a->{borrowernumber},
8609
                            itemnumber     => $a->{itemnumber},
8610
                        }
8611
                      )->issue_id()
8612
                    : undef,
8613
                }
8614
            );
8615
        }
8616
        elsif ( $credit_types_map->{ $a->{accounttype} } ) {
8617
            $credit_rs->create(
8618
                {
8619
                    borrowernumber   => $a->{borrowernumber},
8620
                    amount_paid      => $a->{amount} * -1,
8621
                    amount_remaining => $a->{amountoutstanding} * -1,
8622
                    created_on       => $a->{timestamp},
8623
                    notes            => $a->{note},
8624
                    manager_id       => $a->{manager_id},
8625
                    type => $credit_types_map->{ $a->{accounttype} },
8626
                }
8627
            );
8628
        }
8629
        else {
8630
            # Everything else must be a MANUAL_INV
8631
            $debit_rs->create(
8632
                {
8633
                    borrowernumber     => $a->{borrowernumber},
8634
                    itemnumber         => $a->{itemnumber},
8635
                    amount_original    => $a->{amount},
8636
                    amount_outstanding => $a->{amountoutstanding},
8637
                    created_on         => $a->{timestamp},
8638
                    description        => $a->{description},
8639
                    notes              => $a->{note},
8640
                    manager_id         => $a->{manager_id},
8641
                    type               => Koha::Accounts::DebitTypes::Sundry,
8642
                }
8643
            );
8644
        }
8645
    }
8646
8647
    print "Upgrade to $DBversion done ( Bug 6427 - Rewrite of the accounts system )\n";
8648
    SetVersion ($DBversion);
8649
}
8650
8468
=head1 FUNCTIONS
8651
=head1 FUNCTIONS
8469
8652
8470
=head2 TableExists($table)
8653
=head2 TableExists($table)
8471
- 

Return to bug 6427