@@ -, +, @@ Auto_Renewals in message_attributes, and configured message_transports --- .../data/mysql/atomicupdate/bug-18532.perl | 100 ++++++++++++++++++ .../mysql/en/mandatory/sample_notices.sql | 31 ++++++ .../sample_notices_message_attributes.sql | 3 +- .../sample_notices_message_transports.sql | 6 +- 4 files changed, 138 insertions(+), 2 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug-18532.perl --- a/installer/data/mysql/atomicupdate/bug-18532.perl +++ a/installer/data/mysql/atomicupdate/bug-18532.perl @@ -0,0 +1,100 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + # you can use $dbh here like: + $dbh->do( qq{ + INSERT IGNORE INTO letter (module, code, name, title, content, message_transport_type) VALUES ('circulation', 'AUTO_RENEWALS_DGST', 'notification on auto renewing', 'Auto renewals (Digest)', + "Dear [% borrower.firstname %] [% borrower.surname %], + [% IF checkout.auto_renew_errors %] + There were [% checkout.auto_renew_errors %] items that where not correctly renewed. + [% END %] + [% IF checkout.auto_renew %] + There were [% checkout.auto_renew %] items that where correctly renewed. + [% END %] + ", 'email'); + }); + + $dbh->do( qq{ + INSERT IGNORE INTO `message_attributes` + (`message_attribute_id`, message_name, `takes_days`) + VALUES (7, 'Auto_Renewals', 0) + }); + + $dbh->do( qq{ + INSERT IGNORE INTO `message_transports` + (`message_attribute_id`, `message_transport_type`, `is_digest`, `letter_module`, `letter_code`) + VALUES (7, 'email', 0, 'circulation', 'AUTO_RENEWALS'), + (7, 'sms', 0, 'circulation', 'AUTO_RENEWALS'), + (7, 'email', 1, 'circulation', 'AUTO_RENEWALS_DGST'), + (7, 'sms', 1, 'circulation', 'AUTO_RENEWALS_DGST') + }); + + $dbh->do( qq{ + insert ignore into borrower_message_preferences (categorycode, message_attribute_id, days_in_advance, wants_digest) + select c.categorycode, mp.message_attribute_id, mp.days_in_advance, mp.wants_digest + from + ( + select 1 as message_attribute_id, NULL as days_in_advance, 0 as wants_digest + union + select 2 as message_attribute_id, 0 as days_in_advance, 0 as wants_digest + union + select 3 as message_attribute_id, NULL as days_in_advance, 0 as wants_digest + union + select 4 as message_attribute_id, NULL as days_in_advance, 0 as wants_digest + union + select 5 as message_attribute_id, NULL as days_in_advance, 0 as wants_digest + union + select 6 as message_attribute_id, NULL as days_in_advance, 0 as wants_digest + union + select 7 as message_attribute_id, NULL as days_in_advance, 0 as wants_digest + ) mp + inner join + ( + select c.categorycode, + count(m.categorycode) as count + from categories c + left join + borrower_message_preferences m + on c.categorycode = m.categorycode + group by 1 + ) c + on c.count = 0 or (c.count > 0 and mp.message_attribute_id = 7); + }); + + $dbh->do( qq{ + insert ignore into borrower_message_preferences (borrowernumber, message_attribute_id, days_in_advance, wants_digest) + select c.borrowernumber, mp.message_attribute_id, mp.days_in_advance, mp.wants_digest + from + ( + select 7 as message_attribute_id, NULL as days_in_advance, 0 as wants_digest + ) mp + inner join + ( + select distinct + m1.borrowernumber + from borrower_message_preferences m1 + left join + borrower_message_preferences m2 + on m1.borrowernumber = m2.borrowernumber + and m2.message_attribute_id = 7 + where m2.borrowernumber is null + and m1.borrowernumber is not null + ) c + on true; + }); + + $dbh->do( qq{ + insert into borrower_message_transport_preferences (borrower_message_preference_id, message_transport_type) + select p.borrower_message_preference_id, 'email' + from borrower_message_preferences p + left join + borrower_message_transport_preferences t + on p.borrower_message_preference_id = t.borrower_message_preference_id + where p.message_attribute_id = 7 + and t.borrower_message_preference_id is null; + }); + + + # Always end with this (adjust the bug info) + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 18532 - Add AUTO_RENEWALS in message_attributes and add AUTO_RENEWALS_DGST letter)\n"; +} --- a/installer/data/mysql/en/mandatory/sample_notices.sql +++ a/installer/data/mysql/en/mandatory/sample_notices.sql @@ -316,3 +316,34 @@ INSERT IGNORE INTO `letter` (`module`, `code`, `branchcode`, `name`, `is_html`, INSERT INTO `letter` (`module`, `code`, `branchcode`, `name`, `is_html`, `title`, `content`, `message_transport_type`) VALUES ('circulation', 'SR_SLIP', '', 'Stock rotation slip', 0, 'Stock rotation report', 'Stock rotation 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 != \'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'); + +INSERT INTO letter (module, code, name, title, content, message_transport_type) VALUES ('circulation', 'AUTO_RENEWALS', 'notification on auto renewing', 'Auto renewals', +"Dear [% borrower.firstname %] [% borrower.surname %], +[% IF checkout.auto_renew_error %] +The following item [% biblio.title %] has not been correctly renewed +[% IF checkout.auto_renew_error == 'too_many' %] +You have reach the maximum of checkouts possible. +[% ELSIF checkout.auto_renew_error == 'on_reserve' %] +This item is on hold for another patron. +[% ELSIF checkout.auto_renew_error == 'restriction' %] +You are currently restricted. +[% ELSIF checkout.auto_renew_error == 'overdue' %] +You have overdues. +[% ELSIF checkout.auto_renew_error == 'auto_too_late' %] +It\'s too late to renew this checkout. +[% ELSIF checkout.auto_renew_error == 'auto_too_much_oweing' %] +You have too much unpaid fines. +[% END %] +[% ELSE %] +The following item [% biblio.title %] has correctly been renewed and is now due [% checkout.date_due %] +[% END %]", 'email'); + +INSERT INTO letter (module, code, name, title, content, message_transport_type) VALUES ('circulation', 'AUTO_RENEWALS_DGST', 'notification on auto renewing', 'Auto renewals (Digest)', +"Dear [% borrower.firstname %] [% borrower.surname %], +[% IF <> %] +There were <> items that where not correctly renewed. +[% END %] +[% IF <> %] +There were <> items that where correctly renewed. +[% END %] +", 'email'); --- a/installer/data/mysql/mandatory/sample_notices_message_attributes.sql +++ a/installer/data/mysql/mandatory/sample_notices_message_attributes.sql @@ -5,5 +5,6 @@ values (2, 'Advance_Notice', 1), (4, 'Hold_Filled', 0), (5, 'Item_Check_in', 0), -(6, 'Item_Checkout', 0); +(6, 'Item_Checkout', 0), +(7, 'Auto_Renewals', 0); --- a/installer/data/mysql/mandatory/sample_notices_message_transports.sql +++ a/installer/data/mysql/mandatory/sample_notices_message_transports.sql @@ -17,4 +17,8 @@ values (5, 'email', 0, 'circulation', 'CHECKIN'), (5, 'sms', 0, 'circulation', 'CHECKIN'), (6, 'email', 0, 'circulation', 'CHECKOUT'), -(6, 'sms', 0, 'circulation', 'CHECKOUT'); +(6, 'sms', 0, 'circulation', 'CHECKOUT'), +(7, 'email', 0, 'circulation', 'AUTO_RENEWALS'), +(7, 'sms', 0, 'circulation', 'AUTO_RENEWALS'), +(7, 'email', 1, 'circulation', 'AUTO_RENEWALS_DGST'), +(7, 'sms', 1, 'circulation', 'AUTO_RENEWALS_DGST'); --