5 description => "Add a way to record users concerns about catalog records",
8 my ($dbh, $out) = @$args{qw(dbh out)};
9 unless ( TableExists('tickets') ) {
11 CREATE TABLE IF NOT EXISTS `tickets` (
12 `id` int(11) NOT NULL auto_increment COMMENT 'primary key',
13 `reporter_id` int(11) NOT NULL DEFAULT 0 COMMENT 'id of the patron who reported the ticket',
14 `reported_date` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'date and time this ticket was reported',
15 `title` text NOT NULL COMMENT 'ticket title',
16 `body` text NOT NULL COMMENT 'ticket details',
17 `resolver_id` int(11) DEFAULT NULL COMMENT 'id of the user who resolved the ticket',
18 `resolved_date` datetime DEFAULT NULL COMMENT 'date and time this ticket was resolved',
19 `biblio_id` int(11) DEFAULT NULL COMMENT 'id of biblio linked',
21 CONSTRAINT `tickets_ibfk_1` FOREIGN KEY (`reporter_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
22 CONSTRAINT `tickets_ibfk_2` FOREIGN KEY (`resolver_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
23 CONSTRAINT `tickets_ibfk_3` FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
24 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
27 say $out "Added new table 'tickets'";
30 unless ( TableExists('ticket_updates') ) {
32 CREATE TABLE IF NOT EXISTS `ticket_updates` (
33 `id` int(11) NOT NULL auto_increment COMMENT 'primary key',
34 `ticket_id` int(11) NOT NULL COMMENT 'id of catalog ticket the update relates to',
35 `user_id` int(11) NOT NULL DEFAULT 0 COMMENT 'id of the user who logged the update',
36 `public` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'boolean flag to denote whether this update is public',
37 `date` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'date and time this update was logged',
38 `message` text NOT NULL COMMENT 'update message content',
40 CONSTRAINT `ticket_updates_ibfk_1` FOREIGN KEY (`ticket_id`) REFERENCES `tickets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
41 CONSTRAINT `ticket_updates_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
42 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
45 say $out "Added new table 'ticket_updates'";
50 INSERT IGNORE INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` ) VALUES
51 ('OpacCatalogConcerns', '0', NULL, 'Allow logged in OPAC users to report catalog concerns', 'YesNo')
54 say $out "`OpacCatalogConcerns` preference added";
56 if ( ( $dbh->selectrow_array('SELECT COUNT(*) FROM additional_contents WHERE location=?', undef, 'CatalogConcernHelp') )[0] == 0 ) { # Check to make idempotent
59 INSERT INTO additional_contents ( category, code, location, title, content, lang, published_on, expirationdate, number ) VALUES ('html_customizations', 'CatalogConcernHelp_1', 'CatalogConcernHelp', 'Catalog concern help text', 'Please describe your concern clearly and the library will try to deal with it as quickly as possible', 'default', CAST(NOW() AS date), '2099-01-10', 1 )
62 say $out "`CatalogConcernHelp` block added to html_customization";
65 if ( ( $dbh->selectrow_array('SELECT COUNT(*) FROM additional_contents WHERE location=?', undef, 'CatalogConcernTemplate') )[0] == 0 ) { # Check to make idempotent
66 my $cc_template = <<~ 'END_TEMPLATE';
67 **Describe the concern**
68 A clear and concise description of what the concern is.
71 Steps to reproduce the behavior:
74 3. Scroll down to '....'
78 A clear and concise description of what you expected to happen.
83 INSERT INTO additional_contents ( category, code, location, title, content, lang, published_on, expirationdate, number ) VALUES ('html_customizations', 'CatalogConcernTemplate_1', 'CatalogConcernTemplate', 'Catalog concern template text', "$cc_template", 'default', CAST(NOW() AS date), '2099-01-10', 1 )
86 say $out "`CatalogConcernTemplate` block added to html_customization";
91 INSERT IGNORE INTO letter(module,code,branchcode,name,is_html,title,content,message_transport_type)
92 VALUES ( 'catalog', 'TICKET_ACKNOWLEDGE', '', 'Concern acknowledgement', '1', 'Catalog concern acknowledgement', "Dear [% INCLUDE 'patron-title.inc' patron => ticket.reporter %],\r\n\r\nThankyou for your report concerning [% INCLUDE 'biblio-title.inc' biblio=ticket.biblio link = 0 %].\r\n\r\nYou reported: \r\n[% ticket.body %]\r\n\r\nThankyou", 'email' );
95 say $out "Added new notice 'TICKET_ACKNOWLEDGE'";
99 INSERT IGNORE INTO letter(module,code,branchcode,name,is_html,title,content,message_transport_type)
100 VALUES ( 'catalog', 'TICKET_UPDATE', '', 'Concern updated', '1', 'Catalog concern updated', "Dear [% INCLUDE 'patron-title.inc' patron => ticket_update.ticket.reporter %],\r\n\r\nThe library has added an update to the concern you reported against [% INCLUDE 'biblio-title.inc' biblio=ticket_update.ticket.biblio link = 0 %].\r\n\r\nThe following comment was left: \r\n[% ticket_update.message %]\r\n\r\nThankyou", 'email' );
103 say $out "Added new notice 'TICKET_UPDATE'";
107 INSERT IGNORE INTO letter(module,code,branchcode,name,is_html,title,content,message_transport_type)
108 VALUES ( 'catalog', 'TICKET_RESOLVE', '', 'Concern resolved', '1', 'Catalog concern resolved', "Dear [% INCLUDE 'patron-title.inc' patron => ticket_update.ticket.reporter %],\r\n\r\nThe library has now marked your concern with [% INCLUDE 'biblio-title.inc' biblio=ticket_update.ticket.biblio link = 0 %] as resolved.\r\n\r\nThe following comment was left: \r\n[% ticket_update.message %]\r\n\r\nThankyou", 'email' );
111 say $out "Added new notice 'TICKET_RESOLVE'";
115 INSERT IGNORE INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` ) VALUES
116 ('CatalogerEmails', '', '', 'Notify these catalogers by email when a catalog concern is submitted', 'free')
119 say $out "`CatalogerEmails` preference added";
123 INSERT IGNORE INTO letter(module,code,branchcode,name,is_html,title,content,message_transport_type)
124 VALUES ( 'catalog', 'TICKET_NOTIFY', '', 'Catalog concern notification', '1', 'Catalog concern reported', "Dear cataloger,\r\n\r\n[% INCLUDE 'patron-title.inc' patron => ticket.reporter %] reported the following concern with [% INCLUDE 'biblio-title.inc' biblio=ticket.biblio link = 1 %]\r\n\r\n\r\n\r\n[% ticket.body %]\r\n\r\n\r\n\r\nYou can mark this concern as resolved from the concern management <a href='[% Koha.Preference('IntranetBaseURL') %]/cgi-bin/koha/cataloguing/concerns.pl'>page</a>.", 'email' );
127 say $out "Added new notice 'TICKET_NOTIFY'";
131 INSERT IGNORE INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` ) VALUES
132 ('CatalogConcerns', '0', NULL, 'Allow users to report catalog concerns', 'YesNo')
135 say $out "`CatalogConcerns` preference added";