@@ -, +, @@ --- Koha/Schema/Result/Hold.pm | 437 +++++++++++++++++++++++++ installer/data/mysql/kohastructure.sql | 269 ++++++++++----- 2 files changed, 621 insertions(+), 85 deletions(-) create mode 100644 Koha/Schema/Result/Hold.pm --- a/Koha/Schema/Result/Hold.pm +++ a/Koha/Schema/Result/Hold.pm @@ -0,0 +1,437 @@ +use utf8; +package Koha::Schema::Result::Hold; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +=head1 NAME + +Koha::Schema::Result::Hold + +=cut + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +=head1 TABLE: C + +=cut + +__PACKAGE__->table("holds"); + +=head1 ACCESSORS + +=head2 id + + data_type: 'integer' + is_auto_increment: 1 + is_nullable: 0 + +internal hold identifier + +=head2 patron_id + + data_type: 'integer' + is_foreign_key: 1 + is_nullable: 1 + +foreign key from the borrowers table defining which patron this hold is for + +=head2 pickup_library_id + + data_type: 'varchar' + is_foreign_key: 1 + is_nullable: 1 + size: 10 + +foreign key from the branches table defining which branch the patron wishes to pick this hold up at + +=head2 desk_id + + data_type: 'integer' + is_foreign_key: 1 + is_nullable: 1 + +foreign key from the desks table defining which desk the patron should pick this hold up at + +=head2 created_date + + data_type: 'date' + datetime_undef_if_invalid: 1 + is_nullable: 1 + +the date the hold was placed + +=head2 biblio_id + + data_type: 'integer' + is_foreign_key: 1 + is_nullable: 1 + +foreign key from the biblio table defining which bib record this hold is on + +=head2 item_id + + data_type: 'integer' + is_foreign_key: 1 + is_nullable: 1 + +foreign key from the items table defining the specific item the patron has placed on hold or the item this hold was filled with + +=head2 item_type + + data_type: 'varchar' + is_foreign_key: 1 + is_nullable: 1 + size: 10 + +If record level hold, the optional itemtype of the item the patron is requesting + +=head2 item_level + + data_type: 'tinyint' + default_value: 0 + is_nullable: 0 + +Is the hold was placed at item level + +=head2 completed_date + + data_type: 'date' + datetime_undef_if_invalid: 1 + is_nullable: 1 + +the date this hold was completed (fulfilled or cancelled) + +=head2 notes + + data_type: 'longtext' + is_nullable: 1 + +notes related to this hold + +=head2 priority + + data_type: 'smallint' + default_value: 1 + is_nullable: 0 + +where in the queue the patron sits + +=head2 status + + data_type: 'enum' + default_value: 'placed' + extra: {list => ["placed","fulfilled","processing","waiting","in_transit","cancelled"]} + is_nullable: 0 + +the status the hold is ('placed', 'fulfilled', 'waiting', 'in_transit', 'cancelled') + +=head2 timestamp + + data_type: 'timestamp' + datetime_undef_if_invalid: 1 + default_value: current_timestamp + is_nullable: 0 + +the date and time this hold was last updated + +=head2 waiting_date + + data_type: 'date' + datetime_undef_if_invalid: 1 + is_nullable: 1 + +the date the item was marked as waiting for the patron at the library + +=head2 expiration_date + + data_type: 'date' + datetime_undef_if_invalid: 1 + is_nullable: 1 + +the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date) + +=head2 lowest_priority + + data_type: 'tinyint' + default_value: 0 + is_nullable: 0 + +if it was assigned the lowest priority + +=head2 non_priority + + data_type: 'tinyint' + default_value: 0 + is_nullable: 0 + +if it was assigned no priority + +=head2 suspended + + data_type: 'tinyint' + default_value: 0 + is_nullable: 0 + +If it is a non-priority hold + +=head2 suspended_until_date + + data_type: 'datetime' + datetime_undef_if_invalid: 1 + is_nullable: 1 + +Date until it is suspended + +=head2 completed + + data_type: 'tinyint' + default_value: 0 + is_nullable: 0 + +If it has been completed (i.e. either 'fulfilled' or 'cancelled') + +=head2 cancellation_reason + + data_type: 'varchar' + is_nullable: 1 + size: 80 + +optional authorised value CANCELLATION_REASON + +=cut + +__PACKAGE__->add_columns( + "id", + { data_type => "integer", is_auto_increment => 1, is_nullable => 0 }, + "patron_id", + { data_type => "integer", is_foreign_key => 1, is_nullable => 1 }, + "pickup_library_id", + { data_type => "varchar", is_foreign_key => 1, is_nullable => 1, size => 10 }, + "desk_id", + { data_type => "integer", is_foreign_key => 1, is_nullable => 1 }, + "created_date", + { data_type => "date", datetime_undef_if_invalid => 1, is_nullable => 1 }, + "biblio_id", + { data_type => "integer", is_foreign_key => 1, is_nullable => 1 }, + "item_id", + { data_type => "integer", is_foreign_key => 1, is_nullable => 1 }, + "item_type", + { data_type => "varchar", is_foreign_key => 1, is_nullable => 1, size => 10 }, + "item_level", + { data_type => "tinyint", default_value => 0, is_nullable => 0 }, + "completed_date", + { data_type => "date", datetime_undef_if_invalid => 1, is_nullable => 1 }, + "notes", + { data_type => "longtext", is_nullable => 1 }, + "priority", + { data_type => "smallint", default_value => 1, is_nullable => 0 }, + "status", + { + data_type => "enum", + default_value => "placed", + extra => { + list => [ + "placed", + "fulfilled", + "processing", + "waiting", + "in_transit", + "cancelled", + ], + }, + is_nullable => 0, + }, + "timestamp", + { + data_type => "timestamp", + datetime_undef_if_invalid => 1, + default_value => \"current_timestamp", + is_nullable => 0, + }, + "waiting_date", + { data_type => "date", datetime_undef_if_invalid => 1, is_nullable => 1 }, + "expiration_date", + { data_type => "date", datetime_undef_if_invalid => 1, is_nullable => 1 }, + "lowest_priority", + { data_type => "tinyint", default_value => 0, is_nullable => 0 }, + "non_priority", + { data_type => "tinyint", default_value => 0, is_nullable => 0 }, + "suspended", + { data_type => "tinyint", default_value => 0, is_nullable => 0 }, + "suspended_until_date", + { + data_type => "datetime", + datetime_undef_if_invalid => 1, + is_nullable => 1, + }, + "completed", + { data_type => "tinyint", default_value => 0, is_nullable => 0 }, + "cancellation_reason", + { data_type => "varchar", is_nullable => 1, size => 80 }, +); + +=head1 PRIMARY KEY + +=over 4 + +=item * L + +=back + +=cut + +__PACKAGE__->set_primary_key("id"); + +=head1 RELATIONS + +=head2 biblio + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "biblio", + "Koha::Schema::Result::Biblio", + { biblionumber => "biblio_id" }, + { + is_deferrable => 1, + join_type => "LEFT", + on_delete => "SET NULL", + on_update => "CASCADE", + }, +); + +=head2 club_holds_to_patron_holds + +Type: has_many + +Related object: L + +=cut + +__PACKAGE__->has_many( + "club_holds_to_patron_holds", + "Koha::Schema::Result::ClubHoldsToPatronHold", + { "foreign.hold_id" => "self.id" }, + { cascade_copy => 0, cascade_delete => 0 }, +); + +=head2 desk + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "desk", + "Koha::Schema::Result::Desk", + { desk_id => "desk_id" }, + { + is_deferrable => 1, + join_type => "LEFT", + on_delete => "SET NULL", + on_update => "CASCADE", + }, +); + +=head2 item + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "item", + "Koha::Schema::Result::Item", + { itemnumber => "item_id" }, + { + is_deferrable => 1, + join_type => "LEFT", + on_delete => "SET NULL", + on_update => "CASCADE", + }, +); + +=head2 item_type + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "item_type", + "Koha::Schema::Result::Itemtype", + { itemtype => "item_type" }, + { + is_deferrable => 1, + join_type => "LEFT", + on_delete => "SET NULL", + on_update => "CASCADE", + }, +); + +=head2 patron + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "patron", + "Koha::Schema::Result::Borrower", + { borrowernumber => "patron_id" }, + { + is_deferrable => 1, + join_type => "LEFT", + on_delete => "SET NULL", + on_update => "CASCADE", + }, +); + +=head2 pickup_library + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "pickup_library", + "Koha::Schema::Result::Branch", + { branchcode => "pickup_library_id" }, + { + is_deferrable => 1, + join_type => "LEFT", + on_delete => "SET NULL", + on_update => "CASCADE", + }, +); + + +# Created by DBIx::Class::Schema::Loader v0.07046 @ 2021-01-27 19:43:42 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:EjBk5LQLG2761uSkk/35Sw + +__PACKAGE__->add_columns( + '+completed' => { is_boolean => 1 }, + '+item_level' => { is_boolean => 1 }, + '+suspended' => { is_boolean => 1 }, + '+lowest_priority' => { is_boolean => 1 }, +); + +1; --- a/installer/data/mysql/kohastructure.sql +++ a/installer/data/mysql/kohastructure.sql @@ -1861,7 +1861,7 @@ CREATE TABLE `club_holds_to_patron_holds` ( KEY `clubs_holds_paton_holds_ibfk_3` (`hold_id`), CONSTRAINT `clubs_holds_paton_holds_ibfk_1` FOREIGN KEY (`club_hold_id`) REFERENCES `club_holds` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `clubs_holds_paton_holds_ibfk_2` FOREIGN KEY (`patron_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `clubs_holds_paton_holds_ibfk_3` FOREIGN KEY (`hold_id`) REFERENCES `reserves` (`reserve_id`) ON DELETE CASCADE ON UPDATE CASCADE + CONSTRAINT `clubs_holds_paton_holds_ibfk_3` FOREIGN KEY (`hold_id`) REFERENCES `holds` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; @@ -2635,6 +2635,53 @@ CREATE TABLE `hold_fill_targets` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; +-- +-- Table structure for table `holds` +-- + +DROP TABLE IF EXISTS `holds`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `holds` ( + `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'internal hold identifier', + `patron_id` int(11) DEFAULT NULL COMMENT 'foreign key from the borrowers table defining which patron this hold is for', + `pickup_library_id` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the branches table defining which branch the patron wishes to pick this hold up at', + `desk_id` int(11) DEFAULT NULL COMMENT 'foreign key from the desks table defining which desk the patron should pick this hold up at', + `created_date` date DEFAULT NULL COMMENT 'the date the hold was placed', + `biblio_id` int(11) DEFAULT NULL COMMENT 'foreign key from the biblio table defining which bib record this hold is on', + `item_id` int(11) DEFAULT NULL COMMENT 'foreign key from the items table defining the specific item the patron has placed on hold or the item this hold was filled with', + `item_type` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'If record level hold, the optional itemtype of the item the patron is requesting', + `item_level` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Is the hold was placed at item level', + `completed_date` date DEFAULT NULL COMMENT 'the date this hold was completed (fulfilled or cancelled)', + `notes` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'notes related to this hold', + `priority` smallint(6) NOT NULL DEFAULT 1 COMMENT 'where in the queue the patron sits', + `status` enum('placed','fulfilled','processing','waiting','in_transit','cancelled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'placed' COMMENT 'the status the hold is (''placed'', ''fulfilled'', ''waiting'', ''in_transit'', ''cancelled'')', + `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'the date and time this hold was last updated', + `waiting_date` date DEFAULT NULL COMMENT 'the date the item was marked as waiting for the patron at the library', + `expiration_date` date DEFAULT NULL COMMENT 'the date the hold expires (usually the date entered by the patron to say they don''t need the hold after a certain date)', + `lowest_priority` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'if it was assigned the lowest priority', + `non_priority` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'if it was assigned no priority', + `suspended` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'If it is a non-priority hold', + `suspended_until_date` datetime DEFAULT NULL COMMENT 'Date until it is suspended', + `completed` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'If it has been completed (i.e. either ''fulfilled'' or ''cancelled'')', + `cancellation_reason` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'optional authorised value CANCELLATION_REASON', + PRIMARY KEY (`id`), + KEY `priority_status_idx` (`priority`,`status`), + KEY `patron_id` (`patron_id`), + KEY `biblio_id` (`biblio_id`), + KEY `item_id` (`item_id`), + KEY `pickup_library_id` (`pickup_library_id`), + KEY `item_type` (`item_type`), + KEY `holds_ibfk_2` (`desk_id`), + CONSTRAINT `holds_ibfk_1` FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `holds_ibfk_2` FOREIGN KEY (`desk_id`) REFERENCES `desks` (`desk_id`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `holds_ibfk_3` FOREIGN KEY (`item_id`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `holds_ibfk_4` FOREIGN KEY (`item_type`) REFERENCES `itemtypes` (`itemtype`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `holds_ibfk_5` FOREIGN KEY (`patron_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `holds_ibfk_6` FOREIGN KEY (`pickup_library_id`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + -- -- Table structure for table `housebound_profile` -- @@ -3848,48 +3895,37 @@ CREATE TABLE `old_issues` ( /*!40101 SET character_set_client = @saved_cs_client */; -- --- Table structure for table `old_reserves` +-- Temporary table structure for view `old_reserves` -- DROP TABLE IF EXISTS `old_reserves`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `old_reserves` ( - `reserve_id` int(11) NOT NULL COMMENT 'primary key', - `borrowernumber` int(11) DEFAULT NULL COMMENT 'foreign key from the borrowers table defining which patron this hold is for', - `reservedate` date DEFAULT NULL COMMENT 'the date the hold was places', - `biblionumber` int(11) DEFAULT NULL COMMENT 'foreign key from the biblio table defining which bib record this hold is on', - `branchcode` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the branches table defining which branch the patron wishes to pick this hold up at', - `desk_id` int(11) DEFAULT NULL COMMENT 'foreign key from the desks table defining which desk the patron should pick this hold up at', - `notificationdate` date DEFAULT NULL COMMENT 'currently unused', - `reminderdate` date DEFAULT NULL COMMENT 'currently unused', - `cancellationdate` date DEFAULT NULL COMMENT 'the date this hold was cancelled', - `cancellation_reason` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'optional authorised value CANCELLATION_REASON', - `reservenotes` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'notes related to this hold', - `priority` smallint(6) NOT NULL DEFAULT 1 COMMENT 'where in the queue the patron sits', - `found` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'a one letter code defining what the status is of the hold is after it has been confirmed', - `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'the date and time this hold was last updated', - `itemnumber` int(11) DEFAULT NULL COMMENT 'foreign key from the items table defining the specific item the patron has placed on hold or the item this hold was filled with', - `waitingdate` date DEFAULT NULL COMMENT 'the date the item was marked as waiting for the patron at the library', - `expirationdate` date DEFAULT NULL COMMENT 'the date the hold expires (usually the date entered by the patron to say they don''t need the hold after a certain date)', - `lowestPriority` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'has this hold been pinned to the lowest priority in the holds queue (1 for yes, 0 for no)', - `suspend` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'in this hold suspended (1 for yes, 0 for no)', - `suspend_until` datetime DEFAULT NULL COMMENT 'the date this hold is suspended until (NULL for infinitely)', - `itemtype` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'If record level hold, the optional itemtype of the item the patron is requesting', - `item_level_hold` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Is the hpld placed at item level', - `non_priority` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Is this a non priority hold', - PRIMARY KEY (`reserve_id`), - KEY `old_reserves_borrowernumber` (`borrowernumber`), - KEY `old_reserves_biblionumber` (`biblionumber`), - KEY `old_reserves_itemnumber` (`itemnumber`), - KEY `old_reserves_branchcode` (`branchcode`), - KEY `old_reserves_itemtype` (`itemtype`), - CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL, - CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE SET NULL, - CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL, - CONSTRAINT `old_reserves_ibfk_4` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE SET NULL ON UPDATE SET NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -/*!40101 SET character_set_client = @saved_cs_client */; +/*!50001 DROP VIEW IF EXISTS `old_reserves`*/; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +/*!50001 CREATE TABLE `old_reserves` ( + `reserve_id` tinyint NOT NULL, + `borrowernumber` tinyint NOT NULL, + `reservedate` tinyint NOT NULL, + `biblionumber` tinyint NOT NULL, + `branchcode` tinyint NOT NULL, + `desk_id` tinyint NOT NULL, + `cancellationdate` tinyint NOT NULL, + `cancellation_reason` tinyint NOT NULL, + `reservenotes` tinyint NOT NULL, + `priority` tinyint NOT NULL, + `found` tinyint NOT NULL, + `timestamp` tinyint NOT NULL, + `itemnumber` tinyint NOT NULL, + `waitingdate` tinyint NOT NULL, + `expirationdate` tinyint NOT NULL, + `lowestPriority` tinyint NOT NULL, + `suspend` tinyint NOT NULL, + `suspend_until` tinyint NOT NULL, + `itemtype` tinyint NOT NULL, + `item_level_hold` tinyint NOT NULL, + `non_priority` tinyint NOT NULL +) ENGINE=MyISAM */; +SET character_set_client = @saved_cs_client; -- -- Table structure for table `opac_news` @@ -4287,52 +4323,37 @@ CREATE TABLE `reports_dictionary` ( /*!40101 SET character_set_client = @saved_cs_client */; -- --- Table structure for table `reserves` +-- Temporary table structure for view `reserves` -- DROP TABLE IF EXISTS `reserves`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `reserves` ( - `reserve_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key', - `borrowernumber` int(11) NOT NULL DEFAULT 0 COMMENT 'foreign key from the borrowers table defining which patron this hold is for', - `reservedate` date DEFAULT NULL COMMENT 'the date the hold was placed', - `biblionumber` int(11) NOT NULL DEFAULT 0 COMMENT 'foreign key from the biblio table defining which bib record this hold is on', - `branchcode` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the branches table defining which branch the patron wishes to pick this hold up at', - `desk_id` int(11) DEFAULT NULL COMMENT 'foreign key from the desks table defining which desk the patron should pick this hold up at', - `notificationdate` date DEFAULT NULL COMMENT 'currently unused', - `reminderdate` date DEFAULT NULL COMMENT 'currently unused', - `cancellationdate` date DEFAULT NULL COMMENT 'the date this hold was cancelled', - `cancellation_reason` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'optional authorised value CANCELLATION_REASON', - `reservenotes` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'notes related to this hold', - `priority` smallint(6) NOT NULL DEFAULT 1 COMMENT 'where in the queue the patron sits', - `found` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'a one letter code defining what the status is of the hold is after it has been confirmed', - `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'the date and time this hold was last updated', - `itemnumber` int(11) DEFAULT NULL COMMENT 'foreign key from the items table defining the specific item the patron has placed on hold or the item this hold was filled with', - `waitingdate` date DEFAULT NULL COMMENT 'the date the item was marked as waiting for the patron at the library', - `expirationdate` date DEFAULT NULL COMMENT 'the date the hold expires (usually the date entered by the patron to say they don''t need the hold after a certain date)', - `lowestPriority` tinyint(1) NOT NULL DEFAULT 0, - `suspend` tinyint(1) NOT NULL DEFAULT 0, - `suspend_until` datetime DEFAULT NULL, - `itemtype` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'If record level hold, the optional itemtype of the item the patron is requesting', - `item_level_hold` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Is the hpld placed at item level', - `non_priority` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Is this a non priority hold', - PRIMARY KEY (`reserve_id`), - KEY `priorityfoundidx` (`priority`,`found`), - KEY `borrowernumber` (`borrowernumber`), - KEY `biblionumber` (`biblionumber`), - KEY `itemnumber` (`itemnumber`), - KEY `branchcode` (`branchcode`), - KEY `desk_id` (`desk_id`), - KEY `itemtype` (`itemtype`), - CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `reserves_ibfk_5` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `reserves_ibfk_6` FOREIGN KEY (`desk_id`) REFERENCES `desks` (`desk_id`) ON DELETE SET NULL ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -/*!40101 SET character_set_client = @saved_cs_client */; +/*!50001 DROP VIEW IF EXISTS `reserves`*/; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +/*!50001 CREATE TABLE `reserves` ( + `reserve_id` tinyint NOT NULL, + `borrowernumber` tinyint NOT NULL, + `reservedate` tinyint NOT NULL, + `biblionumber` tinyint NOT NULL, + `branchcode` tinyint NOT NULL, + `desk_id` tinyint NOT NULL, + `cancellationdate` tinyint NOT NULL, + `cancellation_reason` tinyint NOT NULL, + `reservenotes` tinyint NOT NULL, + `priority` tinyint NOT NULL, + `found` tinyint NOT NULL, + `timestamp` tinyint NOT NULL, + `itemnumber` tinyint NOT NULL, + `waitingdate` tinyint NOT NULL, + `expirationdate` tinyint NOT NULL, + `lowestPriority` tinyint NOT NULL, + `suspend` tinyint NOT NULL, + `suspend_until` tinyint NOT NULL, + `itemtype` tinyint NOT NULL, + `item_level_hold` tinyint NOT NULL, + `non_priority` tinyint NOT NULL +) ENGINE=MyISAM */; +SET character_set_client = @saved_cs_client; -- -- Table structure for table `return_claims` @@ -5325,7 +5346,85 @@ CREATE TABLE `zebraqueue` ( KEY `zebraqueue_lookup` (`server`,`biblio_auth_number`,`operation`,`done`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +-- +-- Table structure for table `reserves` +-- + +CREATE VIEW `reserves` +AS + SELECT + `id` AS `reserve_id`, + `patron_id` AS `borrowernumber`, + `created_date` AS `reservedate`, + `biblio_id` AS `biblionumber`, + `pickup_library_id` AS `branchcode`, + `desk_id`, + NULL AS `cancellationdate`, + `cancellation_reason`, + `notes` AS `reservenotes`, + `priority`, + CASE + when `status` = 'waiting' then + 'W' + when `status` = 'in_transit' then + 'T' + else + NULL + END AS `found`, + `timestamp`, + `item_id` AS `itemnumber`, + `waiting_date` AS `waitingdate`, + `expiration_date` AS `expirationdate`, + `lowest_priority` AS `lowestPriority`, + `suspended` AS `suspend`, + `suspended_until_date` AS `suspend_until`, + `item_type` AS `itemtype`, + `item_level` AS `item_level_hold`, + `non_priority` + FROM `holds` + WHERE completed=0; + +-- +-- Table structure for view `old_reserves` +-- + +CREATE VIEW `old_reserves` +AS + SELECT + `id` AS `reserve_id`, + `patron_id` AS `borrowernumber`, + `created_date` AS `reservedate`, + `biblio_id` AS `biblionumber`, + `pickup_library_id` AS `branchcode`, + `desk_id`, + CASE + WHEN `status` = 'cancelled' THEN + `completed_date` + ELSE + NULL + END AS `cancellationdate`, + `cancellation_reason`, + `notes` AS `reservenotes`, + `priority`, + CASE + WHEN `status` = 'fulfilled' THEN + 'F' + ELSE + NULL + END AS `found`, + `timestamp`, + `item_id` AS `itemnumber`, + `waiting_date` AS `waitingdate`, + `expiration_date` AS `expirationdate`, + `lowest_priority` AS `lowestPriority`, + `suspended` AS `suspend`, + `suspended_until_date` AS `suspend_until`, + `item_type` AS `itemtype`, + `item_level` AS `item_level_hold`, + `non_priority` + FROM `holds` + WHERE completed=1; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; @@ -5335,4 +5434,4 @@ CREATE TABLE `zebraqueue` ( /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2021-01-12 11:28:48 +-- Dump completed on 2021-01-27 18:32:36 --