installer/data/mysql/db_revs/211200018.pl

   1 use Modern::Perl;
   2
   3 return {
   4     bug_number => "19532",
   5     description => "Add Recalls",
   6     up => sub {
   7         my ($args) = @_;
   8         my ($dbh, $out) = @$args{qw(dbh out)};
   9
  10         unless( TableExists('recalls') ) {
  11
  12             # Add recalls table
  13             $dbh->do(q{
  14                 CREATE TABLE recalls (
  15                     recall_id int(11) NOT NULL auto_increment,
  16                     borrowernumber int(11) NOT NULL DEFAULT 0,
  17                     recalldate datetime DEFAULT NULL,
  18                     biblionumber int(11) NOT NULL DEFAULT 0,
  19                     branchcode varchar(10) DEFAULT NULL,
  20                     cancellationdate datetime DEFAULT NULL,
  21                     recallnotes mediumtext,
  22                     priority smallint(6) DEFAULT NULL,
  23                     status ENUM('requested','overdue','waiting','in_transit','cancelled','expired','fulfilled') DEFAULT 'requested',
  24                     timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  25                     itemnumber int(11) DEFAULT NULL,
  26                     waitingdate datetime DEFAULT NULL,
  27                     expirationdate datetime DEFAULT NULL,
  28                     old TINYINT(1) NOT NULL DEFAULT 0,
  29                     item_level_recall TINYINT(1) NOT NULL DEFAULT 0,
  30                     PRIMARY KEY (recall_id),
  31                     KEY borrowernumber (borrowernumber),
  32                     KEY biblionumber (biblionumber),
  33                     KEY itemnumber (itemnumber),
  34                     KEY branchcode (branchcode),
  35                     CONSTRAINT recalls_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
  36                     CONSTRAINT recalls_ibfk_2 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE,
  37                     CONSTRAINT recalls_ibfk_3 FOREIGN KEY (itemnumber) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE,
  38                     CONSTRAINT recalls_ibfk_4 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE
  39                 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  40             });
  41
  42             # Add RecallsLog, RecallsMaxPickUpDelay and UseRecalls system preferences
  43             $dbh->do(q{
  44                 INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type) VALUES
  45                 ('RecallsLog','1',NULL,'If ON, log create/cancel/expire/fulfill actions on recalls','YesNo'),
  46                 ('RecallsMaxPickUpDelay','7',NULL,'Define the maximum time a recall can be awaiting pickup','Integer'),
  47                 ('UseRecalls','0',NULL,'Enable or disable recalls','YesNo')
  48             });
  49
  50             # Add recalls notices: RETURN_RECALLED_ITEM, PICKUP_RECALLED_ITEM, RECALL_REQUESTER_DET
  51             $dbh->do(q{
  52                 INSERT IGNORE INTO `letter` (`module`, `code`, `branchcode`, `name`, `is_html`, `title`, `content`, `message_transport_type`) VALUES
  53                 ('circulation','RETURN_RECALLED_ITEM','','Notification to return a recalled item','0','Notification to return a recalled item','Date: <<today>>
  54
  55 <<borrowers.firstname>> <<borrowers.surname>>,
  56
  57 A recall has been placed on the following item: <<biblio.title>> / <<biblio.author>> (<<items.barcode>>). The due date has been updated, and is now <<issues.date_due>>. Please return the item before the due date.
  58
  59 Thank you!','email'),
  60         ('circulation','PICKUP_RECALLED_ITEM','','Recalled item awaiting pickup','0','Recalled item awaiting pickup','Date: <<today>>
  61
  62 <<borrowers.firstname>> <<borrowers.surname>>,
  63
  64 A recall that you requested on the following item: <<biblio.title>> / <<biblio.author>> (<<items.barcode>>) is now ready for you to pick up at <<recalls.branchcode>>. Please pick up your item by <<recalls.expirationdate>>.
  65
  66 Thank you!','email'),
  67         ('circulation','RECALL_REQUESTER_DET','','Details of patron who recalled item',0,'Details of patron who recalled item','Date: <<today>>
  68
  69 Recall for pickup at <<branches.branchname>>
  70 <<borrowers.surname>>, <<borrowers.firstname>> (<<borrowers.cardnumber>>)
  71 <<borrowers.phone>>
  72 <<borrowers.streetnumber>> <<borrowers.address>>, <<borrowers.address2>>, <<borrowers.city>> <<borrowers.zipcode>>
  73 <<borrowers.email>>
  74
  75 ITEM RECALLED
  76 <<biblio.title>> by <<biblio.author>>
  77 Barcode: <<items.barcode>>
  78 Callnumber: <<items.itemcallnumber>>
  79 Waiting since: <<recalls.waitingdate>>
  80 Notes: <<recalls.recallnotes>>', 'print')
  81             });
  82
  83             # Add recalls user flag and manage_recalls user permission
  84             $dbh->do(q{
  85                 INSERT IGNORE INTO userflags (bit, flag, flagdesc, defaulton) VALUES (27, 'recalls', 'Recalls', 0)
  86             });
  87             $dbh->do(q{
  88                 INSERT IGNORE INTO permissions (module_bit, code, description) VALUES (27, 'manage_recalls', 'Manage recalls for patrons')
  89             });
  90
  91             # Add Recall and CancelReserve ENUM options to branchtransfers.reason
  92             $dbh->do(q{
  93                 ALTER TABLE branchtransfers MODIFY COLUMN reason
  94                 ENUM('Manual', 'StockrotationAdvance', 'StockrotationRepatriation', 'ReturnToHome', 'ReturnToHolding', 'RotatingCollection', 'Reserve', 'LostReserve', 'CancelReserve', 'Recall', 'CancelRecall')
  95             });
  96
  97         }
  98     },
  99 };