Attachment #80289 for bug #11897

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




$DBversion = 'XXX';  # will be replaced by the RM
if( CheckVersion( $DBversion ) ) {

    # Stock Rotation Rotas
    unless (TableExists('stockrotationrotas')){
        $dbh->do(q{
          CREATE TABLE `stockrotationrotas` (
            `rota_id` int(11) auto_increment,         -- Stockrotation rota ID
            `title` varchar(100) NOT NULL,            -- Title for this rota
            `description` text NOT NULL default '',   -- Description for this rota
            `cyclical` tinyint(1) NOT NULL default 0, -- Should items on this rota keep cycling?
            `active` tinyint(1) NOT NULL default 0,   -- Is this rota currently active?
            PRIMARY KEY (`rota_id`),
            CONSTRAINT `stockrotationrotas_title`
            UNIQUE (`title`)
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        });
    }

    # Stock Rotation Stages
    unless (TableExists('stockrotationstages')){
        $dbh->do(q{
          CREATE TABLE `stockrotationstages` (
              `stage_id` int(11) auto_increment,     -- Unique stage ID
              `position` int(11) NOT NULL,           -- The position of this stage within its rota
              `rota_id` int(11) NOT NULL,            -- The rota this stage belongs to
              `branchcode_id` varchar(10) NOT NULL,  -- Branch this stage relates to
              `duration` int(11) NOT NULL default 4, -- The number of days items shoud occupy this stage
              PRIMARY KEY (`stage_id`),
              CONSTRAINT `stockrotationstages_rifk`
                FOREIGN KEY (`rota_id`)
                REFERENCES `stockrotationrotas` (`rota_id`)
                ON UPDATE CASCADE ON DELETE CASCADE,
              CONSTRAINT `stockrotationstages_bifk`
                FOREIGN KEY (`branchcode_id`)
                REFERENCES `branches` (`branchcode`)
                ON UPDATE CASCADE ON DELETE CASCADE
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        });
    }

    # Stock Rotation Items
    unless (TableExists('stockrotationitems')){
        $dbh->do(q{
          CREATE TABLE `stockrotationitems` (
              `itemnumber_id` int(11) NOT NULL,         -- Itemnumber to link to a stage & rota
              `stage_id` int(11) NOT NULL,              -- stage ID to link the item to
              `indemand` tinyint(1) NOT NULL default 0, -- Should this item be skipped for rotation?
              `fresh` tinyint(1) NOT NULL default 0,    -- Flag showing item is only just added to rota
              PRIMARY KEY (itemnumber_id),
              CONSTRAINT `stockrotationitems_iifk`
                FOREIGN KEY (`itemnumber_id`)
                REFERENCES `items` (`itemnumber`)
                ON UPDATE CASCADE ON DELETE CASCADE,
              CONSTRAINT `stockrotationitems_sifk`
                FOREIGN KEY (`stage_id`)
                REFERENCES `stockrotationstages` (`stage_id`)
                ON UPDATE CASCADE ON DELETE CASCADE
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        });
    }

    # System preferences
    $dbh->do(q{
        INSERT IGNORE INTO `systempreferences` (`variable`,`value`,`explanation`,`options`,`type`)
        VALUES ('StockRotation','0','If ON, enables the stock rotation module','','YesNo'),
               ('RotationPreventTransfers','0','If ON, prevent any transfers for items on stock rotation rotas, except for stock rotation transfers','','YesNo');
    });

    # Permissions
    $dbh->do(q{
        INSERT IGNORE INTO `userflags` (`bit`, `flag`, `flagdesc`, `defaulton`)
        VALUES (24, 'stockrotation', 'Manage stockrotation operations', 0);
    });

    $dbh->do(q{
        INSERT IGNORE INTO `permissions` (`module_bit`, `code`, `description`)
        VALUES (24, 'manage_rotas', 'Create, edit and delete rotas'),
               (24, 'manage_rota_items', 'Add and remove items from rotas');
    });

    # Notices
    $dbh->do(q{
        INSERT IGNORE INTO `letter` (`module`, `code`, `branchcode`, `name`, `is_html`, `title`, `content`, `message_transport_type`)
        VALUES ('circulation', 'SR_SLIP', '', 'Stock Rotation Slip', 0, 'Stockrotation Report', 'Stockrotation report for [% branch.name %]:\r\n\r\n[% IF branch.items.size %][% branch.items.size %] items to be processed for this branch.\r\n[% ELSE %]No items to be processed for this branch\r\n[% END %][% FOREACH item IN branch.items %][% IF item.reason ne \'in-demand\' %]Title: [% item.title %]\r\nAuthor: [% item.author %]\r\nCallnumber: [% item.callnumber %]\r\nLocation: [% item.location %]\r\nBarcode: [% item.barcode %]\r\nOn loan?: [% item.onloan %]\r\nStatus: [% item.reason %]\r\nCurrent Library: [% item.branch.branchname %] [% item.branch.branchcode %]\r\n\r\n[% END %][% END %]', 'email');
    });

    print "Upgrade to $DBversion done (Bug 11897 - Add Stock Rotation Feature)\n";
    SetVersion( $DBversion );
}




-- Stock Rotation Rotas

CREATE TABLE IF NOT EXISTS stockrotationrotas (
    rota_id int(11) auto_increment,          -- Stockrotation rota ID
    title varchar(100) NOT NULL,            -- Title for this rota
    description text NOT NULL default '',   -- Description for this rota
    cyclical tinyint(1) NOT NULL default 0, -- Should items on this rota keep cycling?
    active tinyint(1) NOT NULL default 0,   -- Is this rota currently active?
    PRIMARY KEY (`rota_id`),
    CONSTRAINT `stockrotationrotas_title`
      UNIQUE (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Stock Rotation Stages

CREATE TABLE IF NOT EXISTS stockrotationstages (
    stage_id int(11) auto_increment,     -- Unique stage ID
    position int(11) NOT NULL,           -- The position of this stage within its rota
    rota_id int(11) NOT NULL,            -- The rota this stage belongs to
    branchcode_id varchar(10) NOT NULL,  -- Branch this stage relates to
    duration int(11) NOT NULL default 4, -- The number of days items shoud occupy this stage
    PRIMARY KEY (`stage_id`),
    CONSTRAINT `stockrotationstages_rifk`
      FOREIGN KEY (`rota_id`)
      REFERENCES `stockrotationrotas` (`rota_id`)
      ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT `stockrotationstages_bifk`
      FOREIGN KEY (`branchcode_id`)
      REFERENCES `branches` (`branchcode`)
      ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Stock Rotation Items

CREATE TABLE IF NOT EXISTS stockrotationitems (
    itemnumber_id int(11) NOT NULL,         -- Itemnumber to link to a stage & rota
    stage_id int(11) NOT NULL,              -- stage ID to link the item to
    indemand tinyint(1) NOT NULL default 0, -- Should this item be skipped for rotation?
    fresh tinyint(1) NOT NULL default 0,    -- Flag showing item is only just added to rota
    PRIMARY KEY (itemnumber_id),
    CONSTRAINT `stockrotationitems_iifk`
      FOREIGN KEY (`itemnumber_id`)
      REFERENCES `items` (`itemnumber`)
      ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT `stockrotationitems_sifk`
      FOREIGN KEY (`stage_id`)
      REFERENCES `stockrotationstages` (`stage_id`)
      ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- System preferences

INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES
       ('StockRotation','0','If ON, enables the stock rotation module','','YesNo'),
       ('RotationPreventTransfers','0','If ON, prevent any transfers for items on stock rotation rotas, except for stock rotation transfers','','YesNo');

-- Permissions

INSERT IGNORE INTO userflags (bit, flag, flagdesc, defaulton) VALUES
       (24, 'stockrotation', 'Manage stockrotation operations', 0);

INSERT IGNORE INTO permissions (module_bit, code, description) VALUES
       (24, 'manage_rotas', 'Create, edit and delete rotas'),
       (24, 'manage_rota_items', 'Add and remove items from rotas');

-- Notices

INSERT IGNORE INTO letter (module, code, branchcode, name, is_html, title, content, message_transport_type) VALUES
       ('circulation', 'SR_SLIP', '', 'Stock Rotation Slip', 0, 'Stockrotation Report', 'Stockrotation report for [% branch.name %]:\r\n\r\n[% IF branch.items.size %][% branch.items.size %] items to be processed for this branch.\r\n[% ELSE %]No items to be processed for this branch\r\n[% END %][% FOREACH item IN branch.items %][% IF item.reason ne \'in-demand\' %]Title: [% item.title %]\r\nAuthor: [% item.author %]\r\nCallnumber: [% item.callnumber %]\r\nLocation: [% item.location %]\r\nBarcode: [% item.barcode %]\r\nOn loan?: [% item.onloan %]\r\nStatus: [% item.reason %]\r\nCurrent Library: [% item.branch.branchname %] [% item.branch.branchcode %]\r\n\r\n[% END %][% END %]', 'email');
- 

Return to bug 11897

Line 0 Link Here

(-)a/installer/data/mysql/atomicupdate/stockrot_tables.perl (+90 lines)
		1	$DBversion = 'XXX'; # will be replaced by the RM
		2	if( CheckVersion( $DBversion ) ) {
		3
		4	# Stock Rotation Rotas
		5	unless (TableExists('stockrotationrotas')){
		6	$dbh->do(q{
		7	CREATE TABLE `stockrotationrotas` (
		8	`rota_id` int(11) auto_increment, -- Stockrotation rota ID
		9	`title` varchar(100) NOT NULL, -- Title for this rota
		10	`description` text NOT NULL default '', -- Description for this rota
		11	`cyclical` tinyint(1) NOT NULL default 0, -- Should items on this rota keep cycling?
		12	`active` tinyint(1) NOT NULL default 0, -- Is this rota currently active?
		13	PRIMARY KEY (`rota_id`),
		14	CONSTRAINT `stockrotationrotas_title`
		15	UNIQUE (`title`)
		16	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
		17	});
		18	}
		19
		20	# Stock Rotation Stages
		21	unless (TableExists('stockrotationstages')){
		22	$dbh->do(q{
		23	CREATE TABLE `stockrotationstages` (
		24	`stage_id` int(11) auto_increment, -- Unique stage ID
		25	`position` int(11) NOT NULL, -- The position of this stage within its rota
		26	`rota_id` int(11) NOT NULL, -- The rota this stage belongs to
		27	`branchcode_id` varchar(10) NOT NULL, -- Branch this stage relates to
		28	`duration` int(11) NOT NULL default 4, -- The number of days items shoud occupy this stage
		29	PRIMARY KEY (`stage_id`),
		30	CONSTRAINT `stockrotationstages_rifk`
		31	FOREIGN KEY (`rota_id`)
		32	REFERENCES `stockrotationrotas` (`rota_id`)
		33	ON UPDATE CASCADE ON DELETE CASCADE,
		34	CONSTRAINT `stockrotationstages_bifk`
		35	FOREIGN KEY (`branchcode_id`)
		36	REFERENCES `branches` (`branchcode`)
		37	ON UPDATE CASCADE ON DELETE CASCADE
		38	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
		39	});
		40	}
		41
		42	# Stock Rotation Items
		43	unless (TableExists('stockrotationitems')){
		44	$dbh->do(q{
		45	CREATE TABLE `stockrotationitems` (
		46	`itemnumber_id` int(11) NOT NULL, -- Itemnumber to link to a stage & rota
		47	`stage_id` int(11) NOT NULL, -- stage ID to link the item to
		48	`indemand` tinyint(1) NOT NULL default 0, -- Should this item be skipped for rotation?
		49	`fresh` tinyint(1) NOT NULL default 0, -- Flag showing item is only just added to rota
		50	PRIMARY KEY (itemnumber_id),
		51	CONSTRAINT `stockrotationitems_iifk`
		52	FOREIGN KEY (`itemnumber_id`)
		53	REFERENCES `items` (`itemnumber`)
		54	ON UPDATE CASCADE ON DELETE CASCADE,
		55	CONSTRAINT `stockrotationitems_sifk`
		56	FOREIGN KEY (`stage_id`)
		57	REFERENCES `stockrotationstages` (`stage_id`)
		58	ON UPDATE CASCADE ON DELETE CASCADE
		59	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
		60	});
		61	}
		62
		63	# System preferences
		64	$dbh->do(q{
65	INSERT IGNORE INTO `systempreferences` (`variable`,`value`,`explanation`,`options`,`type`)
66	VALUES ('StockRotation','0','If ON, enables the stock rotation module','','YesNo'),
67	('RotationPreventTransfers','0','If ON, prevent any transfers for items on stock rotation rotas, except for stock rotation transfers','','YesNo');
68	});
69
70	# Permissions
71	$dbh->do(q{
72	INSERT IGNORE INTO `userflags` (`bit`, `flag`, `flagdesc`, `defaulton`)
73	VALUES (24, 'stockrotation', 'Manage stockrotation operations', 0);
74	});
75
76	$dbh->do(q{
77	INSERT IGNORE INTO `permissions` (`module_bit`, `code`, `description`)
78	VALUES (24, 'manage_rotas', 'Create, edit and delete rotas'),
79	(24, 'manage_rota_items', 'Add and remove items from rotas');
80	});
81
82	# Notices
83	$dbh->do(q{
84	INSERT IGNORE INTO `letter` (`module`, `code`, `branchcode`, `name`, `is_html`, `title`, `content`, `message_transport_type`)
85	VALUES ('circulation', 'SR_SLIP', '', 'Stock Rotation Slip', 0, 'Stockrotation Report', 'Stockrotation report for [% branch.name %]:\r\n\r\n[% IF branch.items.size %][% branch.items.size %] items to be processed for this branch.\r\n[% ELSE %]No items to be processed for this branch\r\n[% END %][% FOREACH item IN branch.items %][% IF item.reason ne \'in-demand\' %]Title: [% item.title %]\r\nAuthor: [% item.author %]\r\nCallnumber: [% item.callnumber %]\r\nLocation: [% item.location %]\r\nBarcode: [% item.barcode %]\r\nOn loan?: [% item.onloan %]\r\nStatus: [% item.reason %]\r\nCurrent Library: [% item.branch.branchname %] [% item.branch.branchcode %]\r\n\r\n[% END %][% END %]', 'email');
86	});
87
88	print "Upgrade to $DBversion done (Bug 11897 - Add Stock Rotation Feature)\n";
89	SetVersion( $DBversion );
90	}

Lines 1-69 Link Here

(-)a/installer/data/mysql/atomicupdate/stockrot_tables.sql (-70 lines)
1	-- Stock Rotation Rotas
2
3	CREATE TABLE IF NOT EXISTS stockrotationrotas (
4	rota_id int(11) auto_increment, -- Stockrotation rota ID
5	title varchar(100) NOT NULL, -- Title for this rota
6	description text NOT NULL default '', -- Description for this rota
7	cyclical tinyint(1) NOT NULL default 0, -- Should items on this rota keep cycling?
8	active tinyint(1) NOT NULL default 0, -- Is this rota currently active?
9	PRIMARY KEY (`rota_id`),
10	CONSTRAINT `stockrotationrotas_title`
11	UNIQUE (`title`)
12	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
13
14	-- Stock Rotation Stages
15
16	CREATE TABLE IF NOT EXISTS stockrotationstages (
17	stage_id int(11) auto_increment, -- Unique stage ID
18	position int(11) NOT NULL, -- The position of this stage within its rota
19	rota_id int(11) NOT NULL, -- The rota this stage belongs to
20	branchcode_id varchar(10) NOT NULL, -- Branch this stage relates to
21	duration int(11) NOT NULL default 4, -- The number of days items shoud occupy this stage
22	PRIMARY KEY (`stage_id`),
23	CONSTRAINT `stockrotationstages_rifk`
24	FOREIGN KEY (`rota_id`)
25	REFERENCES `stockrotationrotas` (`rota_id`)
26	ON UPDATE CASCADE ON DELETE CASCADE,
27	CONSTRAINT `stockrotationstages_bifk`
28	FOREIGN KEY (`branchcode_id`)
29	REFERENCES `branches` (`branchcode`)
30	ON UPDATE CASCADE ON DELETE CASCADE
31	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
32
33	-- Stock Rotation Items
34
35	CREATE TABLE IF NOT EXISTS stockrotationitems (
36	itemnumber_id int(11) NOT NULL, -- Itemnumber to link to a stage & rota
37	stage_id int(11) NOT NULL, -- stage ID to link the item to
38	indemand tinyint(1) NOT NULL default 0, -- Should this item be skipped for rotation?
39	fresh tinyint(1) NOT NULL default 0, -- Flag showing item is only just added to rota
40	PRIMARY KEY (itemnumber_id),
41	CONSTRAINT `stockrotationitems_iifk`
42	FOREIGN KEY (`itemnumber_id`)
43	REFERENCES `items` (`itemnumber`)
44	ON UPDATE CASCADE ON DELETE CASCADE,
45	CONSTRAINT `stockrotationitems_sifk`
46	FOREIGN KEY (`stage_id`)
47	REFERENCES `stockrotationstages` (`stage_id`)
48	ON UPDATE CASCADE ON DELETE CASCADE
49	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
50
51	-- System preferences
52
53	INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES
54	('StockRotation','0','If ON, enables the stock rotation module','','YesNo'),
55	('RotationPreventTransfers','0','If ON, prevent any transfers for items on stock rotation rotas, except for stock rotation transfers','','YesNo');
56
57	-- Permissions
58
59	INSERT IGNORE INTO userflags (bit, flag, flagdesc, defaulton) VALUES
60	(24, 'stockrotation', 'Manage stockrotation operations', 0);
61
62	INSERT IGNORE INTO permissions (module_bit, code, description) VALUES
63	(24, 'manage_rotas', 'Create, edit and delete rotas'),
64	(24, 'manage_rota_items', 'Add and remove items from rotas');
65
66	-- Notices
67
68	INSERT IGNORE INTO letter (module, code, branchcode, name, is_html, title, content, message_transport_type) VALUES
69	('circulation', 'SR_SLIP', '', 'Stock Rotation Slip', 0, 'Stockrotation Report', 'Stockrotation report for [% branch.name %]:\r\n\r\n[% IF branch.items.size %][% branch.items.size %] items to be processed for this branch.\r\n[% ELSE %]No items to be processed for this branch\r\n[% END %][% FOREACH item IN branch.items %][% IF item.reason ne \'in-demand\' %]Title: [% item.title %]\r\nAuthor: [% item.author %]\r\nCallnumber: [% item.callnumber %]\r\nLocation: [% item.location %]\r\nBarcode: [% item.barcode %]\r\nOn loan?: [% item.onloan %]\r\nStatus: [% item.reason %]\r\nCurrent Library: [% item.branch.branchname %] [% item.branch.branchcode %]\r\n\r\n[% END %][% END %]', 'email');
70	-