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 ('PAYMENT', 'Payment', 0, 1),
40 ('WRITEOFF', 'Writeoff', 0, 1),
41 ('FORGIVEN', 'Forgiven', 1, 1),
42 ('CREDIT', 'Credit', 1, 1),
43 ('LOST_RETURN', 'Lost item fee refund', 0, 1)
47 # Adding credit_type_code to accountlines
48 unless ( column_exists('accountlines', 'credit_type_code') ) {
51 ALTER IGNORE TABLE accountlines
53 credit_type_code varchar(80) DEFAULT NULL
60 # Linking credit_type_code in accountlines to code in account_credit_types
61 unless ( foreign_key_exists( 'accountlines', 'accountlines_ibfk_credit_type' ) ) {
64 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
69 # Dropping the check constraint in accountlines
72 ALTER TABLE accountlines ADD CONSTRAINT `accountlines_check_type` CHECK (accounttype IS NOT NULL OR credit_type_code IS NOT NULL)
76 # Update accountype 'C' to 'CREDIT'
79 UPDATE accountlines SET accounttype = 'CREDIT' WHERE accounttype = 'C' OR accounttype = 'CR'
83 # Update accountype 'FOR' to 'FORGIVEN'
86 UPDATE accountlines SET accounttype = 'FORGIVEN' WHERE accounttype = 'FOR' OR accounttype = 'FORW'
90 # Update accountype 'Pay' to 'PAYMENT'
93 UPDATE accountlines SET accounttype = 'PAYMENT' WHERE accounttype = 'Pay' OR accounttype = 'PAY'
97 # Update accountype 'W' to 'WRITEOFF'
100 UPDATE accountlines SET accounttype = 'WRITEOFF' WHERE accounttype = 'W' OR accounttype = 'WO'
104 # Populating credit_type_code
107 UPDATE accountlines SET credit_type_code = accounttype, accounttype = NULL WHERE accounttype IN (SELECT code from account_credit_types)
111 # Adding a check constraints to accountlines
114 ALTER TABLE accountlines ADD CONSTRAINT `accountlines_check_type` CHECK (accounttype IS NOT NULL OR credit_type_code IS NOT NULL)
118 SetVersion($DBversion);
119 print "Upgrade to $DBversion done (Bug 23049 - Add account debit_credit)\n";