From 72ce78cad933afe637fa635f72f9084fa639c0b0 Mon Sep 17 00:00:00 2001
From: Agustin Moyano <agustinmoyano@theke.io>
Date: Tue, 29 Oct 2019 15:27:37 -0300
Subject: [PATCH] Bug 18532: Add new letter AUTO_RENEWALS_DGST, new
 Auto_Renewals in message_attributes, and configured message_transports

---
 .../data/mysql/atomicupdate/bug-18532.perl    | 100 ++++++++++++++++++
 .../mysql/en/mandatory/sample_notices.sql     |  10 ++
 .../sample_notices_message_attributes.sql     |   3 +-
 .../sample_notices_message_transports.sql     |   6 +-
 4 files changed, 117 insertions(+), 2 deletions(-)
 create mode 100644 installer/data/mysql/atomicupdate/bug-18532.perl

diff --git a/installer/data/mysql/atomicupdate/bug-18532.perl b/installer/data/mysql/atomicupdate/bug-18532.perl
new file mode 100644
index 0000000000..9e16942142
--- /dev/null
+++ b/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";
+}
diff --git a/installer/data/mysql/en/mandatory/sample_notices.sql b/installer/data/mysql/en/mandatory/sample_notices.sql
index 506f309785..05eea96f00 100644
--- a/installer/data/mysql/en/mandatory/sample_notices.sql
+++ b/installer/data/mysql/en/mandatory/sample_notices.sql
@@ -337,3 +337,13 @@ You have too much unpaid fines.
 [% 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 <<error>> %]
+There were <<error>> items that where not correctly renewed.
+[% END %]
+[% IF <<success>> %]
+There were <<success>> items that where correctly renewed.
+[% END %]
+", 'email');
diff --git a/installer/data/mysql/mandatory/sample_notices_message_attributes.sql b/installer/data/mysql/mandatory/sample_notices_message_attributes.sql
index a7a6032cc7..32ab818d03 100644
--- a/installer/data/mysql/mandatory/sample_notices_message_attributes.sql
+++ b/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);
 
diff --git a/installer/data/mysql/mandatory/sample_notices_message_transports.sql b/installer/data/mysql/mandatory/sample_notices_message_transports.sql
index 5954bafc7b..eecdbdf16a 100644
--- a/installer/data/mysql/mandatory/sample_notices_message_transports.sql
+++ b/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');
-- 
2.17.1