From 2f2e40ca4674449c598799b1a47d156e50b91fa0 Mon Sep 17 00:00:00 2001 From: Nick Clemens Date: Tue, 9 Oct 2018 15:51:34 +0000 Subject: [PATCH 1/1] Bug 11897: DBRev 18.06.00.040 Signed-off-by: Nick Clemens --- Koha.pm | 2 +- Koha/Schema/Result/Branch.pm | 19 ++- Koha/Schema/Result/Item.pm | 19 ++- Koha/Schema/Result/Stockrotationitem.pm | 113 +++++++++++++++++ Koha/Schema/Result/Stockrotationrota.pm | 105 ++++++++++++++++ Koha/Schema/Result/Stockrotationstage.pm | 136 +++++++++++++++++++++ .../data/mysql/atomicupdate/stockrot_tables.perl | 90 -------------- installer/data/mysql/updatedatabase.pl | 83 +++++++++++++ 8 files changed, 472 insertions(+), 95 deletions(-) create mode 100644 Koha/Schema/Result/Stockrotationitem.pm create mode 100644 Koha/Schema/Result/Stockrotationrota.pm create mode 100644 Koha/Schema/Result/Stockrotationstage.pm delete mode 100644 installer/data/mysql/atomicupdate/stockrot_tables.perl diff --git a/Koha.pm b/Koha.pm index 5d02f2180c..7f769898fe 100644 --- a/Koha.pm +++ b/Koha.pm @@ -29,7 +29,7 @@ use vars qw{ $VERSION }; # - #4 : the developer version. The 4th number is the database subversion. # used by developers when the database changes. updatedatabase take care of the changes itself # and is automatically called by Auth.pm when needed. -$VERSION = "18.06.00.039"; +$VERSION = "18.06.00.040"; sub version { return $VERSION; diff --git a/Koha/Schema/Result/Branch.pm b/Koha/Schema/Result/Branch.pm index ce3527a45a..13fe183742 100644 --- a/Koha/Schema/Result/Branch.pm +++ b/Koha/Schema/Result/Branch.pm @@ -616,6 +616,21 @@ __PACKAGE__->has_many( { cascade_copy => 0, cascade_delete => 0 }, ); +=head2 stockrotationstages + +Type: has_many + +Related object: L + +=cut + +__PACKAGE__->has_many( + "stockrotationstages", + "Koha::Schema::Result::Stockrotationstage", + { "foreign.branchcode_id" => "self.branchcode" }, + { cascade_copy => 0, cascade_delete => 0 }, +); + =head2 transport_cost_frombranches Type: has_many @@ -647,8 +662,8 @@ __PACKAGE__->has_many( ); -# Created by DBIx::Class::Schema::Loader v0.07042 @ 2018-10-09 10:38:33 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:nKWRDScCAwZUDrRhm1kYMA +# Created by DBIx::Class::Schema::Loader v0.07042 @ 2018-10-09 15:50:42 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:0yMUX1UukdV7eMol06JXTQ # You can replace this text with custom code or comments, and it will be preserved on regeneration diff --git a/Koha/Schema/Result/Item.pm b/Koha/Schema/Result/Item.pm index bcfd383404..106ebd2edb 100644 --- a/Koha/Schema/Result/Item.pm +++ b/Koha/Schema/Result/Item.pm @@ -686,9 +686,24 @@ __PACKAGE__->might_have( { cascade_copy => 0, cascade_delete => 0 }, ); +=head2 stockrotationitem -# Created by DBIx::Class::Schema::Loader v0.07042 @ 2018-09-26 16:15:09 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:d5sg0dXWdq0NkHsYchyUyw +Type: might_have + +Related object: L + +=cut + +__PACKAGE__->might_have( + "stockrotationitem", + "Koha::Schema::Result::Stockrotationitem", + { "foreign.itemnumber_id" => "self.itemnumber" }, + { cascade_copy => 0, cascade_delete => 0 }, +); + + +# Created by DBIx::Class::Schema::Loader v0.07042 @ 2018-10-09 15:50:42 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:PQvA8qoxvTe4In5/oa5WIQ __PACKAGE__->belongs_to( biblioitem => "Koha::Schema::Result::Biblioitem", "biblioitemnumber" ); diff --git a/Koha/Schema/Result/Stockrotationitem.pm b/Koha/Schema/Result/Stockrotationitem.pm new file mode 100644 index 0000000000..2c571e5b18 --- /dev/null +++ b/Koha/Schema/Result/Stockrotationitem.pm @@ -0,0 +1,113 @@ +use utf8; +package Koha::Schema::Result::Stockrotationitem; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +=head1 NAME + +Koha::Schema::Result::Stockrotationitem + +=cut + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +=head1 TABLE: C + +=cut + +__PACKAGE__->table("stockrotationitems"); + +=head1 ACCESSORS + +=head2 itemnumber_id + + data_type: 'integer' + is_foreign_key: 1 + is_nullable: 0 + +=head2 stage_id + + data_type: 'integer' + is_foreign_key: 1 + is_nullable: 0 + +=head2 indemand + + data_type: 'tinyint' + default_value: 0 + is_nullable: 0 + +=head2 fresh + + data_type: 'tinyint' + default_value: 0 + is_nullable: 0 + +=cut + +__PACKAGE__->add_columns( + "itemnumber_id", + { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, + "stage_id", + { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, + "indemand", + { data_type => "tinyint", default_value => 0, is_nullable => 0 }, + "fresh", + { data_type => "tinyint", default_value => 0, is_nullable => 0 }, +); + +=head1 PRIMARY KEY + +=over 4 + +=item * L + +=back + +=cut + +__PACKAGE__->set_primary_key("itemnumber_id"); + +=head1 RELATIONS + +=head2 itemnumber + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "itemnumber", + "Koha::Schema::Result::Item", + { itemnumber => "itemnumber_id" }, + { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" }, +); + +=head2 stage + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "stage", + "Koha::Schema::Result::Stockrotationstage", + { stage_id => "stage_id" }, + { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" }, +); + + +# Created by DBIx::Class::Schema::Loader v0.07042 @ 2018-10-09 15:50:42 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:gkOISrUyWYqUHtmqe7ZHug + + +# You can replace this text with custom code or comments, and it will be preserved on regeneration +1; diff --git a/Koha/Schema/Result/Stockrotationrota.pm b/Koha/Schema/Result/Stockrotationrota.pm new file mode 100644 index 0000000000..3711675146 --- /dev/null +++ b/Koha/Schema/Result/Stockrotationrota.pm @@ -0,0 +1,105 @@ +use utf8; +package Koha::Schema::Result::Stockrotationrota; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +=head1 NAME + +Koha::Schema::Result::Stockrotationrota + +=cut + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +=head1 TABLE: C + +=cut + +__PACKAGE__->table("stockrotationrotas"); + +=head1 ACCESSORS + +=head2 rota_id + + data_type: 'integer' + is_auto_increment: 1 + is_nullable: 0 + +=head2 title + + data_type: 'varchar' + is_nullable: 0 + size: 100 + +=head2 description + + data_type: 'text' + is_nullable: 0 + +=head2 cyclical + + data_type: 'tinyint' + default_value: 0 + is_nullable: 0 + +=head2 active + + data_type: 'tinyint' + default_value: 0 + is_nullable: 0 + +=cut + +__PACKAGE__->add_columns( + "rota_id", + { data_type => "integer", is_auto_increment => 1, is_nullable => 0 }, + "title", + { data_type => "varchar", is_nullable => 0, size => 100 }, + "description", + { data_type => "text", is_nullable => 0 }, + "cyclical", + { data_type => "tinyint", default_value => 0, is_nullable => 0 }, + "active", + { data_type => "tinyint", default_value => 0, is_nullable => 0 }, +); + +=head1 PRIMARY KEY + +=over 4 + +=item * L + +=back + +=cut + +__PACKAGE__->set_primary_key("rota_id"); + +=head1 RELATIONS + +=head2 stockrotationstages + +Type: has_many + +Related object: L + +=cut + +__PACKAGE__->has_many( + "stockrotationstages", + "Koha::Schema::Result::Stockrotationstage", + { "foreign.rota_id" => "self.rota_id" }, + { cascade_copy => 0, cascade_delete => 0 }, +); + + +# Created by DBIx::Class::Schema::Loader v0.07042 @ 2018-10-09 15:50:42 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:lTIDZ+w+46Kniub5Tqpqlg + + +# You can replace this text with custom code or comments, and it will be preserved on regeneration +1; diff --git a/Koha/Schema/Result/Stockrotationstage.pm b/Koha/Schema/Result/Stockrotationstage.pm new file mode 100644 index 0000000000..e95e2bdb4e --- /dev/null +++ b/Koha/Schema/Result/Stockrotationstage.pm @@ -0,0 +1,136 @@ +use utf8; +package Koha::Schema::Result::Stockrotationstage; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +=head1 NAME + +Koha::Schema::Result::Stockrotationstage + +=cut + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +=head1 TABLE: C + +=cut + +__PACKAGE__->table("stockrotationstages"); + +=head1 ACCESSORS + +=head2 stage_id + + data_type: 'integer' + is_auto_increment: 1 + is_nullable: 0 + +=head2 position + + data_type: 'integer' + is_nullable: 0 + +=head2 rota_id + + data_type: 'integer' + is_foreign_key: 1 + is_nullable: 0 + +=head2 branchcode_id + + data_type: 'varchar' + is_foreign_key: 1 + is_nullable: 0 + size: 10 + +=head2 duration + + data_type: 'integer' + default_value: 4 + is_nullable: 0 + +=cut + +__PACKAGE__->add_columns( + "stage_id", + { data_type => "integer", is_auto_increment => 1, is_nullable => 0 }, + "position", + { data_type => "integer", is_nullable => 0 }, + "rota_id", + { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, + "branchcode_id", + { data_type => "varchar", is_foreign_key => 1, is_nullable => 0, size => 10 }, + "duration", + { data_type => "integer", default_value => 4, is_nullable => 0 }, +); + +=head1 PRIMARY KEY + +=over 4 + +=item * L + +=back + +=cut + +__PACKAGE__->set_primary_key("stage_id"); + +=head1 RELATIONS + +=head2 branchcode + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "branchcode", + "Koha::Schema::Result::Branch", + { branchcode => "branchcode_id" }, + { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" }, +); + +=head2 rota + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "rota", + "Koha::Schema::Result::Stockrotationrota", + { rota_id => "rota_id" }, + { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" }, +); + +=head2 stockrotationitems + +Type: has_many + +Related object: L + +=cut + +__PACKAGE__->has_many( + "stockrotationitems", + "Koha::Schema::Result::Stockrotationitem", + { "foreign.stage_id" => "self.stage_id" }, + { cascade_copy => 0, cascade_delete => 0 }, +); + + +# Created by DBIx::Class::Schema::Loader v0.07042 @ 2018-10-09 15:50:42 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:SKkedF8PL3DWma8kv8yYmg + + +# You can replace this text with custom code or comments, and it will be preserved on regeneration +1; diff --git a/installer/data/mysql/atomicupdate/stockrot_tables.perl b/installer/data/mysql/atomicupdate/stockrot_tables.perl deleted file mode 100644 index d1ea5033e9..0000000000 --- a/installer/data/mysql/atomicupdate/stockrot_tables.perl +++ /dev/null @@ -1,90 +0,0 @@ -$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 ); -} diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 21d65ca70e..6469f511d0 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -16561,6 +16561,89 @@ if( CheckVersion( $DBversion ) ) { print "Upgrade to $DBversion done (Bug 15520 - Add more granular permission for only editing own library's circ rules)\n"; } +$DBversion = '18.06.00.040'; +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 ); +} + # SEE bug 13068 # if there is anything in the atomicupdate, read and execute it. -- 2.11.0