1 $DBversion = 'XXX'; # will be replaced by the RM
2 if ( CheckVersion($DBversion) ) {
4 # Adding account_credit_types
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,
13 ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci
17 # Adding account_credit_types_branches
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
29 # Populating account_credit_types
32 INSERT IGNORE INTO account_credit_types (
35 can_be_added_manually,
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)
49 # Adding credit_type_code to accountlines
50 unless ( column_exists('accountlines', 'credit_type_code') ) {
53 ALTER IGNORE TABLE accountlines
55 credit_type_code varchar(80) DEFAULT NULL
62 # Linking credit_type_code in accountlines to code in account_credit_types
63 unless ( foreign_key_exists( 'accountlines', 'accountlines_ibfk_credit_type' ) ) {
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
71 # Dropping the check constraint in accountlines
74 ALTER TABLE accountlines ADD CONSTRAINT `accountlines_check_type` CHECK (accounttype IS NOT NULL OR credit_type_code IS NOT NULL)
78 # Populating credit_type_code
81 UPDATE accountlines SET credit_type_code = accounttype, accounttype = NULL WHERE accounttype IN (SELECT code from account_credit_types)
85 # Adding a check constraints to accountlines
88 ALTER TABLE accountlines ADD CONSTRAINT `accountlines_check_type` CHECK (accounttype IS NOT NULL OR credit_type_code IS NOT NULL)
92 SetVersion($DBversion);
93 print "Upgrade to $DBversion done (Bug 23049 - Add account debit_credit)\n";