Attachment #99273 for bug #23727

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




$DBversion = 'XXX'; # will be replaced by the RM
if( CheckVersion( $DBversion ) ) {
    if ( !column_exists( 'course_items', 'itype_enabled' ) ) {
        $dbh->do(q{
            ALTER TABLE course_items
            ADD COLUMN itype_enabled tinyint(1) NOT NULL DEFAULT 0 AFTER itype,
            ADD COLUMN ccode_enabled tinyint(1) NOT NULL DEFAULT 0 AFTER ccode,
            ADD COLUMN holdingbranch_enabled tinyint(1) NOT NULL DEFAULT 0 AFTER holdingbranch,
            ADD COLUMN location_enabled tinyint(1) NOT NULL DEFAULT 0 AFTER location,
            ADD COLUMN itype_storage varchar(10) DEFAULT NULL AFTER itype_enabled,
            ADD COLUMN ccode_storage varchar(80) DEFAULT NULL AFTER ccode_enabled,
            ADD COLUMN holdingbranch_storage varchar(10) DEFAULT NULL AFTER ccode_enabled,
            ADD COLUMN location_storage varchar(80) DEFAULT NULL AFTER location_enabled
        });

        my $item_level_items = C4::Context->preference('item-level_itypes');
        my $itype_field = $item_level_items ? 'i.itype' : 'bi.itemtype';
        $dbh->do(qq{
            UPDATE course_items ci
            LEFT JOIN items i USING ( itemnumber )
            LEFT JOIN biblioitems bi USING ( biblioitemnumber )
            SET

            -- Assume the column is enabled if the course item is active and i.itype/bi.itemtype is set,
            -- or if the course item is not enabled and ci.itype is set
            ci.itype_enabled = IF( ci.enabled = 'yes', IF( $itype_field IS NULL, 0, 1 ), IF(  ci.itype IS NULL, 0, 1  ) ),
            ci.ccode_enabled = IF( ci.enabled = 'yes', IF( i.ccode IS NULL, 0, 1 ), IF(  ci.ccode IS NULL, 0, 1  ) ),
            ci.holdingbranch_enabled = IF( ci.enabled = 'yes', IF( i.holdingbranch IS NULL, 0, 1 ), IF(  ci.holdingbranch IS NULL, 0, 1  ) ),
            ci.location_enabled = IF( ci.enabled = 'yes', IF( i.location IS NULL, 0, 1 ), IF(  ci.location IS NULL, 0, 1  ) ),

            -- If the course item is enabled, copy the value from the item.
            -- If the course item is not enabled, keep the existing value
            ci.itype = IF( ci.enabled = 'yes', $itype_field, ci.itype ),
            ci.ccode = IF( ci.enabled = 'yes', i.ccode, ci.ccode ),
            ci.holdingbranch = IF( ci.enabled = 'yes', i.holdingbranch, ci.holdingbranch ),
            ci.location = IF( ci.enabled = 'yes', i.location, ci.location ),

            -- If the course is enabled, copy the value from the item to storage.
            -- If it is not enabled, copy the value from the course item to storage
            ci.itype_storage = IF( ci.enabled = 'no', $itype_field, ci.itype ),
            ci.ccode_storage = IF( ci.enabled = 'no', i.ccode, ci.ccode ),
            ci.holdingbranch_storage = IF( ci.enabled = 'no', i.holdingbranch, ci.holdingbranch ),
            ci.location_storage = IF( ci.enabled = 'no', i.location, ci.location );
        });

        # Clean up the storage columns
        $dbh->do(q{
            UPDATE course_items SET
                itype_storage = NULL,
                ccode_storage = NULL,
                holdingbranch_storage = NULL,
                location_storage = NULL
            WHERE enabled = 'no';
        });

        # Clean up the course enabled value columns
        $dbh->do(q{
            UPDATE course_items SET
                itype = IF( itype_enabled = 'no', NULL, itype ),
                ccode = IF( ccode_enabled = 'no', NULL, ccode ),
                holdingbranch = IF( holdingbranch_enabled = 'no', NULL, holdingbranch ),
                location = IF( location_enabled = 'no', NULL, location )
            WHERE enabled = 'no';
        });
    }

    SetVersion( $DBversion );
    print "Upgrade to $DBversion done (Bug 23727 - Editing course reserve items is broken)\n";
}

Lines 3791-3799 CREATE TABLE `course_items` ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (-1 / +8 lines)
3791	`ci_id` int(11) NOT NULL AUTO_INCREMENT, -- course item id	3791	`ci_id` int(11) NOT NULL AUTO_INCREMENT, -- course item id
3792	`itemnumber` int(11) NOT NULL, -- items.itemnumber for the item on reserve	3792	`itemnumber` int(11) NOT NULL, -- items.itemnumber for the item on reserve
3793	`itype` varchar(10) DEFAULT NULL, -- new itemtype for the item to have while on reserve (optional)	3793	`itype` varchar(10) DEFAULT NULL, -- new itemtype for the item to have while on reserve (optional)
		3794	`itype_enabled` tinyint(1) NOT NULL DEFAULT 0, -- indicates if itype should be changed while on course reserve
		3795	`itype_storage` varchar(10) DEFAULT NULL, -- a place to store the itype when item is on course reserve
3794	`ccode` varchar(80) DEFAULT NULL, -- new category code for the item to have while on reserve (optional)	3796	`ccode` varchar(80) DEFAULT NULL, -- new category code for the item to have while on reserve (optional)
		3797	`ccode_enabled` tinyint(1) NOT NULL DEFAULT 0, -- indicates if ccode should be changed while on course reserve
		3798	`ccode_storage` varchar(80) DEFAULT NULL, -- a place to store the ccode when item is on course reserve
3795	`holdingbranch` varchar(10) DEFAULT NULL, -- new holding branch for the item to have while on reserve (optional)	3799	`holdingbranch` varchar(10) DEFAULT NULL, -- new holding branch for the item to have while on reserve (optional)
		3800	`holdingbranch_enabled` tinyint(1) NOT NULL DEFAULT 0, -- indicates if itype should be changed while on course reserve
		3801	`holdingbranch_storage` varchar(10) DEFAULT NULL, -- a place to store the holdingbranch when item is on course reserve
3796	`location` varchar(80) DEFAULT NULL, -- new shelving location for the item to have while on reseve (optional)	3802	`location` varchar(80) DEFAULT NULL, -- new shelving location for the item to have while on reseve (optional)
		3803	`location_enabled` tinyint(1) NOT NULL DEFAULT 0, -- indicates if itype should be changed while on course reserve
		3804	`location_storage` varchar(80) DEFAULT NULL, -- a place to store the location when the item is on course reserve
3797	`enabled` enum('yes','no') NOT NULL DEFAULT 'no', -- if at least one enabled course has this item on reseve, this field will be 'yes', otherwise it will be 'no'	3805	`enabled` enum('yes','no') NOT NULL DEFAULT 'no', -- if at least one enabled course has this item on reseve, this field will be 'yes', otherwise it will be 'no'
3798	`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,	3806	`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
3799	PRIMARY KEY (`ci_id`),	3807	PRIMARY KEY (`ci_id`),
3800	-

Return to bug 23727

Line 0 Link Here

(-)a/installer/data/mysql/atomicupdate/bug_23727.perl (+69 lines)
		1	$DBversion = 'XXX'; # will be replaced by the RM
		2	if( CheckVersion( $DBversion ) ) {
		3	if ( !column_exists( 'course_items', 'itype_enabled' ) ) {
		4	$dbh->do(q{
		5	ALTER TABLE course_items
		6	ADD COLUMN itype_enabled tinyint(1) NOT NULL DEFAULT 0 AFTER itype,
		7	ADD COLUMN ccode_enabled tinyint(1) NOT NULL DEFAULT 0 AFTER ccode,
		8	ADD COLUMN holdingbranch_enabled tinyint(1) NOT NULL DEFAULT 0 AFTER holdingbranch,
		9	ADD COLUMN location_enabled tinyint(1) NOT NULL DEFAULT 0 AFTER location,
		10	ADD COLUMN itype_storage varchar(10) DEFAULT NULL AFTER itype_enabled,
		11	ADD COLUMN ccode_storage varchar(80) DEFAULT NULL AFTER ccode_enabled,
		12	ADD COLUMN holdingbranch_storage varchar(10) DEFAULT NULL AFTER ccode_enabled,
		13	ADD COLUMN location_storage varchar(80) DEFAULT NULL AFTER location_enabled
		14	});
		15
		16	my $item_level_items = C4::Context->preference('item-level_itypes');
		17	my $itype_field = $item_level_items ? 'i.itype' : 'bi.itemtype';
		18	$dbh->do(qq{
		19	UPDATE course_items ci
		20	LEFT JOIN items i USING ( itemnumber )
		21	LEFT JOIN biblioitems bi USING ( biblioitemnumber )
		22	SET
		23
		24	-- Assume the column is enabled if the course item is active and i.itype/bi.itemtype is set,
		25	-- or if the course item is not enabled and ci.itype is set
		26	ci.itype_enabled = IF( ci.enabled = 'yes', IF( $itype_field IS NULL, 0, 1 ), IF( ci.itype IS NULL, 0, 1 ) ),
		27	ci.ccode_enabled = IF( ci.enabled = 'yes', IF( i.ccode IS NULL, 0, 1 ), IF( ci.ccode IS NULL, 0, 1 ) ),
		28	ci.holdingbranch_enabled = IF( ci.enabled = 'yes', IF( i.holdingbranch IS NULL, 0, 1 ), IF( ci.holdingbranch IS NULL, 0, 1 ) ),
		29	ci.location_enabled = IF( ci.enabled = 'yes', IF( i.location IS NULL, 0, 1 ), IF( ci.location IS NULL, 0, 1 ) ),
		30
		31	-- If the course item is enabled, copy the value from the item.
		32	-- If the course item is not enabled, keep the existing value
		33	ci.itype = IF( ci.enabled = 'yes', $itype_field, ci.itype ),
		34	ci.ccode = IF( ci.enabled = 'yes', i.ccode, ci.ccode ),
		35	ci.holdingbranch = IF( ci.enabled = 'yes', i.holdingbranch, ci.holdingbranch ),
		36	ci.location = IF( ci.enabled = 'yes', i.location, ci.location ),
		37
		38	-- If the course is enabled, copy the value from the item to storage.
		39	-- If it is not enabled, copy the value from the course item to storage
		40	ci.itype_storage = IF( ci.enabled = 'no', $itype_field, ci.itype ),
		41	ci.ccode_storage = IF( ci.enabled = 'no', i.ccode, ci.ccode ),
		42	ci.holdingbranch_storage = IF( ci.enabled = 'no', i.holdingbranch, ci.holdingbranch ),
		43	ci.location_storage = IF( ci.enabled = 'no', i.location, ci.location );
		44	});
		45
		46	# Clean up the storage columns
		47	$dbh->do(q{
		48	UPDATE course_items SET
		49	itype_storage = NULL,
		50	ccode_storage = NULL,
		51	holdingbranch_storage = NULL,
		52	location_storage = NULL
		53	WHERE enabled = 'no';
		54	});
		55
		56	# Clean up the course enabled value columns
		57	$dbh->do(q{
		58	UPDATE course_items SET
		59	itype = IF( itype_enabled = 'no', NULL, itype ),
		60	ccode = IF( ccode_enabled = 'no', NULL, ccode ),
		61	holdingbranch = IF( holdingbranch_enabled = 'no', NULL, holdingbranch ),
		62	location = IF( location_enabled = 'no', NULL, location )
		63	WHERE enabled = 'no';
		64	});
65	}
66
67	SetVersion( $DBversion );
68	print "Upgrade to $DBversion done (Bug 23727 - Editing course reserve items is broken)\n";
69	}