Attachment #122912 for bug #22435

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




$DBversion = 'XXX'; # will be replaced by the RM
if( CheckVersion( $DBversion ) ) {
    # Remove foreign key for offset types
    if ( foreign_key_exists( 'account_offsets', 'account_offsets_ibfk_t' ) ) {
        $dbh->do( "ALTER TABLE account_offsets DROP FOREIGN KEY account_offsets_ibfk_t" );
    }

    # Drop account_offset_types table
    $dbh->do( "DROP TABLE IF EXISTS account_offset_types" );

    # Update offset_types to 'CREATE' where appropriate
    $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)" );
    $dbh->do( "UPDATE account_offsets SET type = 'APPLY' WHERE type != 'OVERDUE_INCREASE' AND type != 'OVERDUE_DECREASE' AND type != 'CREATE'" );

    # Update offset_types to 'APPLY' where appropriate
    $dbh->do( "UPDATE account_offsets SET type = 'APPLY' WHERE type != 'OVERDUE_INCREASE' AND type != 'OVERDUE_DECREASE' AND type != 'CREATE' AND type != 'VOID'" );

    # Update table to ENUM
    $dbh->do(
        q{
            ALTER TABLE
                `account_offsets`
            MODIFY COLUMN
                `type` enum(
                    'CREATE',
                    'APPLY',
                    'VOID',
                    'OVERDUE_INCREASE',
                    'OVERDUE_DECREASE'
                )
            AFTER `debit_id`
          }
    );


    NewVersion( $DBversion, 22435, "Update existing offsets");
}




) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `account_offset_types`
--

DROP TABLE IF EXISTS `account_offset_types`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `account_offset_types` (
  `type` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'The type of offset this is',
  PRIMARY KEY (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `account_offsets`
--

  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique identifier for each offset',
  `credit_id` int(11) DEFAULT NULL COMMENT 'The id of the accountline the increased the patron''s balance',
  `debit_id` int(11) DEFAULT NULL COMMENT 'The id of the accountline that decreased the patron''s balance',
  `type` enum('CREATE','APPLY','VOID','OVERDUE_INCREASE','OVERDUE_DECREASE') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'The type of offset this is',
  `amount` decimal(26,6) NOT NULL COMMENT 'The amount of the change',
  `created_on` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `account_offsets_ibfk_p` (`credit_id`),
  KEY `account_offsets_ibfk_f` (`debit_id`),
  KEY `account_offsets_ibfk_t` (`type`),
  CONSTRAINT `account_offsets_ibfk_f` FOREIGN KEY (`debit_id`) REFERENCES `accountlines` (`accountlines_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `account_offsets_ibfk_p` FOREIGN KEY (`credit_id`) REFERENCES `accountlines` (`accountlines_id`) ON DELETE CASCADE ON UPDATE CASCADE
  CONSTRAINT `account_offsets_ibfk_t` FOREIGN KEY (`type`) REFERENCES `account_offset_types` (`type`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;





INSERT INTO account_offset_types ( type ) VALUES
('CREATE'),
('Writeoff'),
('Payment'),
('Purchase'),
('Lost Item'),
('Lost Item Found'),
('Processing Fee'),
('Manual Credit'),
('Manual Debit'),
('Reverse Payment'),
('Forgiven'),
('Dropbox'),
('Account Fee'),
('Rental Fee'),
('Reserve Fee'),
('Hold Expired'),
('Overpayment'),
('OVERDUE_INCREASE'),
('OVERDUE_DECREASE'),
('OVERDUE'),
('Void Payment'),
('Credit Applied'),
('PAYOUT'),
('DISCOUNT'),
('REFUND'),
('CANCELLATION'),
('VOID');
- 

Return to bug 22435

Lines 86-104 CREATE TABLE `account_debit_types_branches` ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (-17 / +2 lines)
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

Lines 1-8 Link Here

(-)a/installer/data/mysql/atomicupdate/bug_22435.perl (-3 / +31 lines)
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	}

Lines 1-28 Link Here

(-)a/installer/data/mysql/mandatory/account_offset_types.sql (-29 lines)
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	-