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

(-)a/installer/data/mysql/atomicupdate/bug_22435.perl (-3 / +31 lines)
Lines 1-8 Link Here
1
$DBversion = 'XXX'; # will be replaced by the RM
1
$DBversion = 'XXX'; # will be replaced by the RM
2
if( CheckVersion( $DBversion ) ) {
2
if( CheckVersion( $DBversion ) ) {
3
    $dbh->do( "INSERT IGNORE INTO account_offset_types ( type ) VALUES ( 'CREATE' ), ( 'APPLY' )" );
3
    # Remove foreign key for offset types
4
    if ( foreign_key_exists( 'account_offsets', 'account_offsets_ibfk_t' ) ) {
5
        $dbh->do( "ALTER TABLE account_offsets DROP FOREIGN KEY account_offsets_ibfk_t" );
6
    }
7
8
    # Drop account_offset_types table
9
    $dbh->do( "DROP TABLE IF EXISTS account_offset_types" );
10
11
    # Update offset_types to 'CREATE' where appropriate
4
    $dbh->do( "UPDATE account_offsets SET type = 'CREATE' WHERE type != 'OVERDUE_INCREASE' AND type != 'OVERDUE_DECREASE' AND ( debit_id IS NULL OR credit_id IS NULL)" );
12
    $dbh->do( "UPDATE account_offsets SET type = 'CREATE' WHERE type != 'OVERDUE_INCREASE' AND type != 'OVERDUE_DECREASE' AND ( debit_id IS NULL OR credit_id IS NULL)" );
5
    $dbh->do( "UPDATE account_offsets SET type = 'APPLY' WHERE type != 'OVERDUE_INCREASE' AND type != 'OVERDUE_DECREASE' AND type != 'CREATE'" );
6
13
7
    NewVersion( $DBversion, 22435, "Update offsets to CREATE type");
14
    # Update offset_types to 'APPLY' where appropriate
15
    $dbh->do( "UPDATE account_offsets SET type = 'APPLY' WHERE type != 'OVERDUE_INCREASE' AND type != 'OVERDUE_DECREASE' AND type != 'CREATE' AND type != 'VOID'" );
16
17
    # Update table to ENUM
18
    $dbh->do(
19
        q{
20
            ALTER TABLE
21
                `account_offsets`
22
            MODIFY COLUMN
23
                `type` enum(
24
                    'CREATE',
25
                    'APPLY',
26
                    'VOID',
27
                    'OVERDUE_INCREASE',
28
                    'OVERDUE_DECREASE'
29
                )
30
            AFTER `debit_id`
31
          }
32
    );
33
34
35
    NewVersion( $DBversion, 22435, "Update existing offsets");
8
}
36
}
(-)a/installer/data/mysql/kohastructure.sql (-17 / +2 lines)
Lines 86-104 CREATE TABLE `account_debit_types_branches` ( Link Here
86
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
86
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
87
/*!40101 SET character_set_client = @saved_cs_client */;
87
/*!40101 SET character_set_client = @saved_cs_client */;
88
88
89
--
90
-- Table structure for table `account_offset_types`
91
--
92
93
DROP TABLE IF EXISTS `account_offset_types`;
94
/*!40101 SET @saved_cs_client     = @@character_set_client */;
95
/*!40101 SET character_set_client = utf8 */;
96
CREATE TABLE `account_offset_types` (
97
  `type` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'The type of offset this is',
98
  PRIMARY KEY (`type`)
99
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
100
/*!40101 SET character_set_client = @saved_cs_client */;
101
102
--
89
--
103
-- Table structure for table `account_offsets`
90
-- Table structure for table `account_offsets`
104
--
91
--
Lines 110-125 CREATE TABLE `account_offsets` ( Link Here
110
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique identifier for each offset',
97
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique identifier for each offset',
111
  `credit_id` int(11) DEFAULT NULL COMMENT 'The id of the accountline the increased the patron''s balance',
98
  `credit_id` int(11) DEFAULT NULL COMMENT 'The id of the accountline the increased the patron''s balance',
112
  `debit_id` int(11) DEFAULT NULL COMMENT 'The id of the accountline that decreased the patron''s balance',
99
  `debit_id` int(11) DEFAULT NULL COMMENT 'The id of the accountline that decreased the patron''s balance',
113
  `type` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'The type of offset this is',
100
  `type` enum('CREATE','APPLY','VOID','OVERDUE_INCREASE','OVERDUE_DECREASE') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'The type of offset this is',
114
  `amount` decimal(26,6) NOT NULL COMMENT 'The amount of the change',
101
  `amount` decimal(26,6) NOT NULL COMMENT 'The amount of the change',
115
  `created_on` timestamp NOT NULL DEFAULT current_timestamp(),
102
  `created_on` timestamp NOT NULL DEFAULT current_timestamp(),
116
  PRIMARY KEY (`id`),
103
  PRIMARY KEY (`id`),
117
  KEY `account_offsets_ibfk_p` (`credit_id`),
104
  KEY `account_offsets_ibfk_p` (`credit_id`),
118
  KEY `account_offsets_ibfk_f` (`debit_id`),
105
  KEY `account_offsets_ibfk_f` (`debit_id`),
119
  KEY `account_offsets_ibfk_t` (`type`),
120
  CONSTRAINT `account_offsets_ibfk_f` FOREIGN KEY (`debit_id`) REFERENCES `accountlines` (`accountlines_id`) ON DELETE CASCADE ON UPDATE CASCADE,
106
  CONSTRAINT `account_offsets_ibfk_f` FOREIGN KEY (`debit_id`) REFERENCES `accountlines` (`accountlines_id`) ON DELETE CASCADE ON UPDATE CASCADE,
121
  CONSTRAINT `account_offsets_ibfk_p` FOREIGN KEY (`credit_id`) REFERENCES `accountlines` (`accountlines_id`) ON DELETE CASCADE ON UPDATE CASCADE,
107
  CONSTRAINT `account_offsets_ibfk_p` FOREIGN KEY (`credit_id`) REFERENCES `accountlines` (`accountlines_id`) ON DELETE CASCADE ON UPDATE CASCADE
122
  CONSTRAINT `account_offsets_ibfk_t` FOREIGN KEY (`type`) REFERENCES `account_offset_types` (`type`) ON DELETE CASCADE ON UPDATE CASCADE
123
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
108
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
124
/*!40101 SET character_set_client = @saved_cs_client */;
109
/*!40101 SET character_set_client = @saved_cs_client */;
125
110
(-)a/installer/data/mysql/mandatory/account_offset_types.sql (-29 lines)
Lines 1-28 Link Here
1
INSERT INTO account_offset_types ( type ) VALUES
2
('CREATE'),
3
('Writeoff'),
4
('Payment'),
5
('Purchase'),
6
('Lost Item'),
7
('Lost Item Found'),
8
('Processing Fee'),
9
('Manual Credit'),
10
('Manual Debit'),
11
('Reverse Payment'),
12
('Forgiven'),
13
('Dropbox'),
14
('Account Fee'),
15
('Rental Fee'),
16
('Reserve Fee'),
17
('Hold Expired'),
18
('Overpayment'),
19
('OVERDUE_INCREASE'),
20
('OVERDUE_DECREASE'),
21
('OVERDUE'),
22
('Void Payment'),
23
('Credit Applied'),
24
('PAYOUT'),
25
('DISCOUNT'),
26
('REFUND'),
27
('CANCELLATION'),
28
('VOID');
29
- 

Return to bug 22435