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

(-)a/installer/data/mysql/kohastructure.sql (-44 / +84 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 2678-2727 CREATE TABLE `messages` ( -- circulation messages left via the patron's check ou Link Here
2678
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2679
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2679
2680
2680
--
2681
--
2681
-- Table structure for table `accountlines`
2682
--
2683
2684
DROP TABLE IF EXISTS `accountlines`;
2685
CREATE TABLE `accountlines` (
2686
  `accountlines_id` int(11) NOT NULL AUTO_INCREMENT,
2687
  `borrowernumber` int(11) NOT NULL default 0,
2688
  `accountno` smallint(6) NOT NULL default 0,
2689
  `itemnumber` int(11) default NULL,
2690
  `date` date default NULL,
2691
  `amount` decimal(28,6) default NULL,
2692
  `description` mediumtext,
2693
  `dispute` mediumtext,
2694
  `accounttype` varchar(5) default NULL,
2695
  `amountoutstanding` decimal(28,6) default NULL,
2696
  `lastincrement` decimal(28,6) default NULL,
2697
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2698
  `notify_id` int(11) NOT NULL default 0,
2699
  `notify_level` int(2) NOT NULL default 0,
2700
  `note` text NULL default NULL,
2701
  `manager_id` int(11) NULL,
2702
  PRIMARY KEY (`accountlines_id`),
2703
  KEY `acctsborridx` (`borrowernumber`),
2704
  KEY `timeidx` (`timestamp`),
2705
  KEY `itemnumber` (`itemnumber`),
2706
  CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2707
  CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2708
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2709
2710
--
2711
-- Table structure for table `accountoffsets`
2712
--
2713
2714
DROP TABLE IF EXISTS `accountoffsets`;
2715
CREATE TABLE `accountoffsets` (
2716
  `borrowernumber` int(11) NOT NULL default 0,
2717
  `accountno` smallint(6) NOT NULL default 0,
2718
  `offsetaccount` smallint(6) NOT NULL default 0,
2719
  `offsetamount` decimal(28,6) default NULL,
2720
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2721
  CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2722
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2723
2724
--
2725
-- Table structure for table `action_logs`
2682
-- Table structure for table `action_logs`
2726
--
2683
--
2727
2684
Lines 3387-3392 CREATE TABLE IF NOT EXISTS marc_modification_template_actions ( Link Here
3387
  CONSTRAINT `mmta_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `marc_modification_templates` (`template_id`) ON DELETE CASCADE ON UPDATE CASCADE
3344
  CONSTRAINT `mmta_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `marc_modification_templates` (`template_id`) ON DELETE CASCADE ON UPDATE CASCADE
3388
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
3345
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
3389
3346
3347
--
3348
-- Table structure for table 'account_credits'
3349
--
3350
DROP TABLE IF EXISTS account_credits;
3351
CREATE TABLE IF account_credits (
3352
    credit_id int(11) NOT NULL AUTO_INCREMENT,  -- The unique id for this credit
3353
    borrowernumber int(11) NOT NULL,            -- The borrower this credit applies to
3354
    `type` varchar(255) NOT NULL,               -- The type of credit this is ( defined by Koha::Accounts::CreditTypes )
3355
    amount_received decimal(28,6) DEFAULT NULL, -- If this was a cash payment, the amount of money given
3356
    amount_paid decimal(28,6) NOT NULL,         -- The actual ammount paid, if less than amount_recieved, change was given back
3357
    amount_remaining decimal(28,6) NOT NULL,    -- The amount of this credit that has not been applied to outstanding debits
3358
    notes text,                                 -- Misc notes for this credit
3359
    manager_id int(11) DEFAULT NULL,            -- The borrowernumber of the user who created this credit ( if any )
3360
    created_on timestamp NULL DEFAULT NULL,     -- Timestamp for when this credit was created
3361
    updated_on timestamp NULL DEFAULT NULL,     -- Timestamp for when this credit was last modified
3362
    PRIMARY KEY (credit_id),
3363
    KEY borrowernumber (borrowernumber)
3364
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
3365
3366
--
3367
-- Constraints for table `account_credits`
3368
--
3369
ALTER TABLE `account_credits`
3370
  ADD CONSTRAINT account_credits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE;
3371
3372
--
3373
-- Table structure for table 'account_debits'
3374
--
3375
3376
DROP TABLE IF EXISTS account_debits;
3377
CREATE TABLE account_debits (
3378
    debit_id int(11) NOT NULL AUTO_INCREMENT,           -- The unique id for this debit
3379
    borrowernumber int(11) NOT NULL DEFAULT '0',        -- The borrower this debit applies to
3380
    itemnumber int(11) DEFAULT NULL,                    -- The item related to this debit ( for fines, lost fees, etc )
3381
    issue_id int(11) DEFAULT NULL,                      -- The checkout this debit is related to ( again, for fines, lost fees, etc )
3382
    `type` varchar(255) NOT NULL,                       -- The type of debit this is ( defined by Koha::Accounts::DebitTypes )
3383
    accruing tinyint(1) NOT NULL DEFAULT '0',           -- Boolean flag, tells of if this is a fine that is still accruing
3384
    amount_original decimal(28,6) DEFAULT NULL,         -- The total amount of this debit
3385
    amount_outstanding decimal(28,6) DEFAULT NULL,      -- The amount still owed on this debit
3386
    amount_last_increment decimal(28,6) DEFAULT NULL,   -- The amount by which this debit last changed
3387
    description mediumtext,                             -- The description for this debit
3388
    notes text,                                         -- Misc notes for this debit
3389
    manager_id int(11) DEFAULT NULL,                    -- The borrowernumber of the user who created this debit ( if any )
3390
    created_on timestamp NULL DEFAULT NULL,             -- Timestamp for when this credit was created
3391
    updated_on timestamp NULL DEFAULT NULL,             -- Timestamp for when this credit was last modified
3392
    PRIMARY KEY (debit_id),
3393
    KEY acctsborridx (borrowernumber),
3394
    KEY itemnumber (itemnumber),
3395
    KEY borrowernumber (borrowernumber),
3396
    KEY issue_id (issue_id)
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
3405
--
3406
-- Table structure for table 'account_offsets'
3407
--
3408
3409
DROP TABLE IF EXISTS account_offsets;
3410
CREATE TABLE account_offsets (
3411
    offset_id int(11) NOT NULL AUTO_INCREMENT,                                              -- Unique id for this offset
3412
    debit_id int(11) DEFAULT NULL,                                                          -- Related debit
3413
    credit_id int(11) DEFAULT NULL,                                                         -- Related credit ( if any )
3414
    `type` varchar(255) DEFAULT NULL,                                                       -- The type of this offset ( defined by Koha::Accounts::OffsetTypes ), if any
3415
    amount decimal(28,6) NOT NULL,                                                          -- The amount of the offset, positive means patron owes more, negative means patron owes less
3416
    created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,    -- Timestamp for when this offset was created
3417
    PRIMARY KEY (offset_id),
3418
    KEY fee_id (debit_id),
3419
    KEY payment_id (credit_id)
3420
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
3421
3422
--
3423
-- Constraints for table `account_offsets`
3424
--
3425
ALTER TABLE `account_offsets`
3426
    ADD CONSTRAINT account_offsets_ibfk_1 FOREIGN KEY (debit_id) REFERENCES account_debits (debit_id) ON DELETE CASCADE ON UPDATE CASCADE,
3427
    ADD CONSTRAINT account_offsets_ibfk_2 FOREIGN KEY (credit_id) REFERENCES account_credits (credit_id) ON DELETE CASCADE ON UPDATE CASCADE;
3428
3429
3390
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
3430
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
3391
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
3431
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
3392
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
3432
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
(-)a/installer/data/mysql/updatedatabase.pl (-1 / +176 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 7824-7829 if ( CheckVersion($DBversion) ) { Link Here
7824
    SetVersion($DBversion);
7826
    SetVersion($DBversion);
7825
}
7827
}
7826
7828
7829
$DBversion = "3.15.00.XXX";
7830
if ( CheckVersion($DBversion) ) {
7831
    $dbh->do(q{
7832
        ALTER TABLE old_issues ADD issue_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
7833
    });
7834
    $dbh->do(q{
7835
        ALTER TABLE issues ADD issue_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
7836
    });
7837
    $dbh->do(q{
7838
        UPDATE issues SET issue_id = issue_id + ( SELECT COUNT(*) FROM old_issues ) ORDER BY issue_id DESC
7839
    });
7840
7841
    $dbh->do("
7842
        CREATE TABLE IF NOT EXISTS account_credits (
7843
            credit_id int(11) NOT NULL AUTO_INCREMENT,
7844
            borrowernumber int(11) NOT NULL,
7845
            `type` varchar(255) NOT NULL,
7846
            amount_received decimal(28,6) DEFAULT NULL,
7847
            amount_paid decimal(28,6) NOT NULL,
7848
            amount_remaining decimal(28,6) NOT NULL,
7849
            notes text,
7850
            manager_id int(11) DEFAULT NULL,
7851
            created_on timestamp NULL DEFAULT NULL,
7852
            updated_on timestamp NULL DEFAULT NULL,
7853
            PRIMARY KEY (credit_id),
7854
            KEY borrowernumber (borrowernumber)
7855
        ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
7856
    ");
7857
    $dbh->do("
7858
        CREATE TABLE IF NOT EXISTS account_debits (
7859
            debit_id int(11) NOT NULL AUTO_INCREMENT,
7860
            borrowernumber int(11) NOT NULL DEFAULT '0',
7861
            itemnumber int(11) DEFAULT NULL,
7862
            issue_id int(11) DEFAULT NULL,
7863
            `type` varchar(255) NOT NULL,
7864
            accruing tinyint(1) NOT NULL DEFAULT '0',
7865
            amount_original decimal(28,6) DEFAULT NULL,
7866
            amount_outstanding decimal(28,6) DEFAULT NULL,
7867
            amount_last_increment decimal(28,6) DEFAULT NULL,
7868
            description mediumtext,
7869
            notes text,
7870
            manager_id int(11) DEFAULT NULL,
7871
            created_on timestamp NULL DEFAULT NULL,
7872
            updated_on timestamp NULL DEFAULT NULL,
7873
            PRIMARY KEY (debit_id),
7874
            KEY acctsborridx (borrowernumber),
7875
            KEY itemnumber (itemnumber),
7876
            KEY borrowernumber (borrowernumber),
7877
            KEY issue_id (issue_id)
7878
        ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
7879
    ");
7880
7881
    $dbh->do("
7882
        CREATE TABLE account_offsets (
7883
            offset_id int(11) NOT NULL AUTO_INCREMENT,
7884
            debit_id int(11) DEFAULT NULL,
7885
            credit_id int(11) DEFAULT NULL,
7886
            `type` varchar(255) DEFAULT NULL,
7887
            amount decimal(28,6) NOT NULL COMMENT 'A positive number here represents a payment, a negative is a increase in a fine.',
7888
            created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
7889
            PRIMARY KEY (offset_id),
7890
            KEY fee_id (debit_id),
7891
            KEY payment_id (credit_id)
7892
        ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
7893
    ");
7894
7895
    $dbh->do("
7896
        ALTER TABLE `account_credits`
7897
          ADD CONSTRAINT account_credits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE;
7898
    ");
7899
    $dbh->do("
7900
        ALTER TABLE `account_debits`
7901
          ADD CONSTRAINT account_debits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE;
7902
    ");
7903
    $dbh->do("
7904
        ALTER TABLE `account_offsets`
7905
          ADD CONSTRAINT account_offsets_ibfk_1 FOREIGN KEY (debit_id) REFERENCES account_debits (debit_id) ON DELETE CASCADE ON UPDATE CASCADE,
7906
          ADD CONSTRAINT account_offsets_ibfk_2 FOREIGN KEY (credit_id) REFERENCES account_credits (credit_id) ON DELETE CASCADE ON UPDATE CASCADE;
7907
    ");
7908
7909
    $dbh->do("
7910
        ALTER TABLE borrowers ADD account_balance DECIMAL( 28, 6 ) NOT NULL;
7911
    ");
7912
7913
    my $schema = Koha::Database->new()->schema;
7914
    my $debit_rs = $schema->resultset('AccountDebit');
7915
    my $credit_rs = $schema->resultset('AccountCredit');
7916
    my $issues_rs = $schema->resultset('Issue');
7917
7918
    use Koha::Accounts::DebitTypes;
7919
    use Koha::Accounts::CreditTypes;
7920
7921
    my $debit_types_map = {
7922
        'A'    => Koha::Accounts::DebitTypes::AccountManagementFee,
7923
        'F'    => Koha::Accounts::DebitTypes::Fine,
7924
        'FU'   => Koha::Accounts::DebitTypes::Fine,
7925
        'L'    => Koha::Accounts::DebitTypes::Lost,
7926
        'M'    => Koha::Accounts::DebitTypes::Sundry,
7927
        'N'    => Koha::Accounts::DebitTypes::NewCard,
7928
        'Rent' => Koha::Accounts::DebitTypes::Rental,
7929
    };
7930
7931
    my $credit_types_map = {
7932
        'FOR' => Koha::Accounts::CreditTypes::Forgiven,
7933
        'LR'  => Koha::Accounts::CreditTypes::Found,
7934
        'Pay' => Koha::Accounts::CreditTypes::Payment,
7935
        'PAY' => Koha::Accounts::CreditTypes::Payment,
7936
        'WO'  => Koha::Accounts::CreditTypes::WriteOff,
7937
        'W'   => Koha::Accounts::CreditTypes::WriteOff,
7938
        'C'   => Koha::Accounts::CreditTypes::Credit,
7939
        'CR'  => Koha::Accounts::CreditTypes::Credit,
7940
    };
7941
7942
    my $sth = $dbh->prepare("SELECT * FROM accountlines");
7943
    $sth->execute();
7944
    while ( my $a = $sth->fetchrow_hashref() ) {
7945
        if ( $debit_types_map->{ $a->{accounttype} } ) {
7946
            $debit_rs->create(
7947
                {
7948
                    borrowernumber     => $a->{borrowernumber},
7949
                    itemnumber         => $a->{itemnumber},
7950
                    amount_original    => $a->{amount},
7951
                    amount_outstanding => $a->{amountoutstanding},
7952
                    created_on         => $a->{timestamp},
7953
                    description        => $a->{description},
7954
                    notes              => $a->{note},
7955
                    manager_id         => $a->{manager_id},
7956
                    accruing           => $a->{accounttype} eq 'FU',
7957
                    type     => $debit_types_map->{ $a->{accounttype} },
7958
                    issue_id => $a->{accounttype} eq 'FU'
7959
                    ? $issues_rs->single(
7960
                        {
7961
                            borrowernumber => $a->{borrowernumber},
7962
                            itemnumber     => $a->{itemnumber},
7963
                        }
7964
                      )->issue_id()
7965
                    : undef,
7966
                }
7967
            );
7968
        }
7969
        elsif ( $credit_types_map->{ $a->{accounttype} } ) {
7970
            $credit_rs->create(
7971
                {
7972
                    borrowernumber   => $a->{borrowernumber},
7973
                    amount_paid      => $a->{amount} * -1,
7974
                    amount_remaining => $a->{amountoutstanding} * -1,
7975
                    created_on       => $a->{timestamp},
7976
                    notes            => $a->{note},
7977
                    manager_id       => $a->{manager_id},
7978
                    type => $credit_types_map->{ $a->{accounttype} },
7979
                }
7980
            );
7981
        }
7982
        else {
7983
            # Everything else must be a MANUAL_INV
7984
            $debit_rs->create(
7985
                {
7986
                    borrowernumber     => $a->{borrowernumber},
7987
                    itemnumber         => $a->{itemnumber},
7988
                    amount_original    => $a->{amount},
7989
                    amount_outstanding => $a->{amountoutstanding},
7990
                    created_on         => $a->{timestamp},
7991
                    description        => $a->{description},
7992
                    notes              => $a->{note},
7993
                    manager_id         => $a->{manager_id},
7994
                    type               => Koha::Accounts::DebitTypes::Sundry,
7995
                }
7996
            );
7997
        }
7998
    }
7999
8000
    print "Upgrade to $DBversion done ( Bug 6427 - Rewrite of the accounts system )\n";
8001
    SetVersion ($DBversion);
8002
}
7827
8003
7828
=head1 FUNCTIONS
8004
=head1 FUNCTIONS
7829
8005
7830
- 

Return to bug 6427