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

(-)a/C4/Installer.pm (+1 lines)
Lines 332-337 sub load_sql_in_order { Link Here
332
    push @fnames, C4::Context->config('intranetdir') . "/installer/data/mysql/userpermissions.sql";
332
    push @fnames, C4::Context->config('intranetdir') . "/installer/data/mysql/userpermissions.sql";
333
    push @fnames, C4::Context->config('intranetdir') . "/installer/data/mysql/audio_alerts.sql";
333
    push @fnames, C4::Context->config('intranetdir') . "/installer/data/mysql/audio_alerts.sql";
334
    push @fnames, C4::Context->config('intranetdir') . "/installer/data/mysql/account_offset_types.sql";
334
    push @fnames, C4::Context->config('intranetdir') . "/installer/data/mysql/account_offset_types.sql";
335
    push @fnames, C4::Context->config('intranetdir') . "/installer/data/mysql/account_credit_types.sql";
335
    push @fnames, C4::Context->config('intranetdir') . "/installer/data/mysql/account_debit_types.sql";
336
    push @fnames, C4::Context->config('intranetdir') . "/installer/data/mysql/account_debit_types.sql";
336
    foreach my $file (@fnames) {
337
    foreach my $file (@fnames) {
337
        #      warn $file;
338
        #      warn $file;
(-)a/installer/data/mysql/account_credit_types.sql (+8 lines)
Line 0 Link Here
1
INSERT INTO account_debit_types ( code, description, can_be_added_manually, is_system ) VALUES
2
('Pay', 'Payment', 0, 1),
3
('PAY', 'Payment', 0, 1),
4
('W', 'Writeoff', 0, 1),
5
('WO', 'Writeoff', 0, 1),
6
('FOR', 'Forgiven', 1, 1),
7
('C', 'Credit', 1, 1),
8
('LOST_RETURN', 'Lost item fee refund', 0, 1);
(-)a/installer/data/mysql/atomicupdate/bug_23908_credit.perl (+94 lines)
Line 0 Link Here
1
$DBversion = 'XXX';    # will be replaced by the RM
2
if ( CheckVersion($DBversion) ) {
3
4
    # Adding account_credit_types
5
    $dbh->do(
6
        qq{
7
            CREATE TABLE IF NOT EXISTS account_credit_types (
8
              code varchar(80) NOT NULL,
9
              description varchar(200) NULL,
10
              can_be_added_manually tinyint(4) NOT NULL DEFAULT 1,
11
              is_system tinyint(1) NOT NULL DEFAULT 0,
12
              PRIMARY KEY (code)
13
            ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci
14
          }
15
    );
16
17
    # Adding account_credit_types_branches
18
    $dbh->do(
19
        qq{
20
            CREATE TABLE IF NOT EXISTS account_credit_types_branches (
21
                credit_type_code VARCHAR(80),
22
                branchcode VARCHAR(10),
23
                FOREIGN KEY (credit_type_code) REFERENCES account_credit_types(code) ON DELETE CASCADE,
24
                FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE
25
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
26
        }
27
    );
28
29
    # Populating account_credit_types
30
    $dbh->do(
31
        qq{
32
            INSERT IGNORE INTO account_credit_types (
33
              code,
34
              description,
35
              can_be_added_manually,
36
              is_system
37
            )
38
            VALUES
39
              ('Pay', 'Payment', 0, 1),
40
              ('PAY', 'Payment', 0, 1),
41
              ('W', 'Writeoff', 0, 1),
42
              ('WO', 'Writeoff', 0, 1),
43
              ('FOR', 'Forgiven', 1, 1),
44
              ('C', 'Credit', 1, 1),
45
              ('LOST_RETURN', 'Lost item fee refund', 0, 1)
46
        }
47
    );
48
49
    # Adding credit_type_code to accountlines
50
    unless ( column_exists('accountlines', 'credit_type_code') ) {
51
        $dbh->do(
52
            qq{
53
                ALTER IGNORE TABLE accountlines
54
                ADD
55
                  credit_type_code varchar(80) DEFAULT NULL
56
                AFTER
57
                  accounttype
58
              }
59
        );
60
    }
61
62
    # Linking credit_type_code in accountlines to code in account_credit_types
63
    unless ( foreign_key_exists( 'accountlines', 'accountlines_ibfk_credit_type' ) ) {
64
        $dbh->do(
65
            qq{
66
            ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_credit_type` FOREIGN KEY (`credit_type_code`) REFERENCES `account_credit_types` (`code`) ON DELETE SET NULL ON UPDATE CASCADE
67
              }
68
        );
69
    }
70
71
    # Dropping the check constraint in accountlines
72
    $dbh->do(
73
        qq{
74
        ALTER TABLE accountlines ADD CONSTRAINT `accountlines_check_type` CHECK (accounttype IS NOT NULL OR credit_type_code IS NOT NULL)
75
        }
76
    );
77
78
    # Populating credit_type_code
79
    $dbh->do(
80
        qq{
81
        UPDATE accountlines SET credit_type_code = accounttype, accounttype = NULL WHERE accounttype IN (SELECT code from account_credit_types)
82
        }
83
    );
84
85
    # Adding a check constraints to accountlines
86
    $dbh->do(
87
        qq{
88
        ALTER TABLE accountlines ADD CONSTRAINT `accountlines_check_type` CHECK (accounttype IS NOT NULL OR credit_type_code IS NOT NULL)
89
        }
90
    );
91
92
    SetVersion($DBversion);
93
    print "Upgrade to $DBversion done (Bug 23049 - Add account debit_credit)\n";
94
}
(-)a/installer/data/mysql/en/mandatory/account_credit_types.sql (+8 lines)
Line 0 Link Here
1
INSERT INTO account_debit_types ( code, description, can_be_added_manually, is_system ) VALUES
2
('Pay', 'Payment', 0, 1),
3
('PAY', 'Payment', 0, 1),
4
('W', 'Writeoff', 0, 1),
5
('WO', 'Writeoff', 0, 1),
6
('FOR', 'Forgiven', 1, 1),
7
('C', 'Credit', 1, 1),
8
('LOST_RETURN', 'Lost item fee refund', 0, 1);
(-)a/installer/data/mysql/kohastructure.sql (-2 / +28 lines)
Lines 2621-2626 CREATE TABLE `cash_registers` ( Link Here
2621
  CONSTRAINT cash_registers_branch FOREIGN KEY (branch) REFERENCES branches (branchcode) ON UPDATE CASCADE ON DELETE CASCADE
2621
  CONSTRAINT cash_registers_branch FOREIGN KEY (branch) REFERENCES branches (branchcode) ON UPDATE CASCADE ON DELETE CASCADE
2622
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
2622
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
2623
2623
2624
--
2625
-- Table structure for table `account_credit_types`
2626
--
2627
2628
DROP TABLE IF EXISTS `account_credit_types`;
2629
CREATE TABLE `account_credit_types` (
2630
  `code` varchar(80) NOT NULL,
2631
  `description` varchar(200) DEFAULT NULL,
2632
  `can_be_added_manually` tinyint(4) NOT NULL DEFAULT 1,
2633
  `is_system` tinyint(1) NOT NULL DEFAULT 0,
2634
  PRIMARY KEY (`code`)
2635
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2636
2637
--
2638
-- Table structure for table `account_credit_types_branches`
2639
--
2640
2641
DROP TABLE IF EXISTS `account_credit_types_branches`;
2642
CREATE TABLE `account_credit_types_branches` (
2643
    `credit_type_code` VARCHAR(80),
2644
    `branchcode` VARCHAR(10),
2645
    FOREIGN KEY (`credit_type_code`) REFERENCES `account_credit_types` (`code`) ON DELETE CASCADE,
2646
    FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE
2647
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2648
2624
--
2649
--
2625
-- Table structure for table `account_debit_types`
2650
-- Table structure for table `account_debit_types`
2626
--
2651
--
Lines 2660-2666 CREATE TABLE `accountlines` ( Link Here
2660
  `date` date default NULL,
2685
  `date` date default NULL,
2661
  `amount` decimal(28,6) default NULL,
2686
  `amount` decimal(28,6) default NULL,
2662
  `description` LONGTEXT,
2687
  `description` LONGTEXT,
2663
  `accounttype` varchar(80) default NULL,
2688
  `credit_type_code` varchar(80) default NULL,
2664
  `debit_type_code` varchar(80) default NULL,
2689
  `debit_type_code` varchar(80) default NULL,
2665
  `status` varchar(16) default NULL,
2690
  `status` varchar(16) default NULL,
2666
  `payment_type` varchar(80) default NULL, -- optional authorised value PAYMENT_TYPE
2691
  `payment_type` varchar(80) default NULL, -- optional authorised value PAYMENT_TYPE
Lines 2674-2679 CREATE TABLE `accountlines` ( Link Here
2674
  PRIMARY KEY (`accountlines_id`),
2699
  PRIMARY KEY (`accountlines_id`),
2675
  KEY `acctsborridx` (`borrowernumber`),
2700
  KEY `acctsborridx` (`borrowernumber`),
2676
  KEY `timeidx` (`timestamp`),
2701
  KEY `timeidx` (`timestamp`),
2702
  KEY `credit_type_code` (`credit_type_code`),
2677
  KEY `debit_type_code` (`debit_type_code`),
2703
  KEY `debit_type_code` (`debit_type_code`),
2678
  KEY `itemnumber` (`itemnumber`),
2704
  KEY `itemnumber` (`itemnumber`),
2679
  KEY `branchcode` (`branchcode`),
2705
  KEY `branchcode` (`branchcode`),
Lines 2684-2689 CREATE TABLE `accountlines` ( Link Here
2684
  CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2710
  CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2685
  CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE,
2711
  CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE,
2686
  CONSTRAINT `accountlines_ibfk_registers` FOREIGN KEY (`register_id`) REFERENCES `cash_registers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
2712
  CONSTRAINT `accountlines_ibfk_registers` FOREIGN KEY (`register_id`) REFERENCES `cash_registers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
2713
  CONSTRAINT `accountlines_ibfk_credit_type` FOREIGN KEY (`credit_type_code`) REFERENCES `account_credit_types` (`code`) ON DELETE SET NULL ON UPDATE CASCADE,
2687
  CONSTRAINT `accountlines_ibfk_debit_type` FOREIGN KEY (`debit_type_code`) REFERENCES `account_debit_types` (`code`) ON DELETE SET NULL ON UPDATE CASCADE
2714
  CONSTRAINT `accountlines_ibfk_debit_type` FOREIGN KEY (`debit_type_code`) REFERENCES `account_debit_types` (`code`) ON DELETE SET NULL ON UPDATE CASCADE
2688
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2715
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2689
2716
2690
- 

Return to bug 23805