View | Details | Raw Unified | Return to bug 18532
Collapse All | Expand All

(-)a/installer/data/mysql/atomicupdate/bug-18532.perl (+100 lines)
Line 0 Link Here
1
$DBversion = 'XXX'; # will be replaced by the RM
2
if( CheckVersion( $DBversion ) ) {
3
    # you can use $dbh here like:
4
    $dbh->do( qq{
5
        INSERT IGNORE INTO letter (module, code, name, title, content, message_transport_type) VALUES ('circulation', 'AUTO_RENEWALS_DGST', 'notification on auto renewing', 'Auto renewals (Digest)',
6
        "Dear [% borrower.firstname %] [% borrower.surname %],
7
        [% IF checkout.auto_renew_errors %]
8
        There were [% checkout.auto_renew_errors %] items that where not correctly renewed.
9
        [% END %]
10
        [% IF checkout.auto_renew %]
11
        There were [% checkout.auto_renew %] items that where correctly renewed.
12
        [% END %]
13
        ", 'email');
14
    });
15
16
    $dbh->do( qq{
17
        INSERT IGNORE INTO `message_attributes`
18
            (`message_attribute_id`, message_name, `takes_days`)
19
        VALUES (7, 'Auto_Renewals', 0)
20
    });
21
22
    $dbh->do( qq{
23
        INSERT IGNORE INTO `message_transports`
24
            (`message_attribute_id`, `message_transport_type`, `is_digest`, `letter_module`, `letter_code`)
25
        VALUES  (7, 'email', 0, 'circulation', 'AUTO_RENEWALS'),
26
                (7, 'sms', 0, 'circulation', 'AUTO_RENEWALS'),
27
                (7, 'email', 1, 'circulation', 'AUTO_RENEWALS_DGST'),
28
                (7, 'sms', 1, 'circulation', 'AUTO_RENEWALS_DGST')
29
    });
30
31
    $dbh->do( qq{
32
        insert ignore into borrower_message_preferences (categorycode, message_attribute_id, days_in_advance, wants_digest)
33
        select c.categorycode, mp.message_attribute_id, mp.days_in_advance, mp.wants_digest
34
        from
35
            (
36
                select 1 as message_attribute_id, NULL as days_in_advance, 0 as wants_digest
37
                union
38
                select 2 as message_attribute_id, 0 as days_in_advance, 0 as wants_digest
39
                union
40
                select 3 as message_attribute_id, NULL as days_in_advance, 0 as wants_digest
41
                union
42
                select 4 as message_attribute_id, NULL as days_in_advance, 0 as wants_digest
43
                union
44
                select 5 as message_attribute_id, NULL as days_in_advance, 0 as wants_digest
45
                union
46
                select 6 as message_attribute_id, NULL as days_in_advance, 0 as wants_digest
47
                union
48
                select 7 as message_attribute_id, NULL as days_in_advance, 0 as wants_digest
49
            ) mp
50
        inner join
51
            (
52
                select 	c.categorycode,
53
                        count(m.categorycode) as count
54
                from 	categories c
55
                left join
56
                        borrower_message_preferences m
57
                on      c.categorycode = m.categorycode
58
                group by 1
59
            ) c
60
        on  c.count = 0 or (c.count > 0 and mp.message_attribute_id = 7);
61
    });
62
63
    $dbh->do( qq{
64
        insert ignore into borrower_message_preferences (borrowernumber, message_attribute_id, days_in_advance, wants_digest)
65
        select c.borrowernumber, mp.message_attribute_id, mp.days_in_advance, mp.wants_digest
66
        from
67
            (
68
                select 7 as message_attribute_id, NULL as days_in_advance, 0 as wants_digest
69
            ) mp
70
        inner join
71
            (
72
                select 	distinct
73
                        m1.borrowernumber
74
                from 	borrower_message_preferences m1
75
                left join
76
                        borrower_message_preferences m2
77
                on      m1.borrowernumber = m2.borrowernumber
78
                        and m2.message_attribute_id = 7
79
                where   m2.borrowernumber is null
80
                        and m1.borrowernumber is not null
81
            ) c
82
        on  true;
83
    });
84
85
    $dbh->do( qq{
86
        insert into borrower_message_transport_preferences (borrower_message_preference_id, message_transport_type)
87
        select  p.borrower_message_preference_id, 'email'
88
        from    borrower_message_preferences p
89
        left join
90
                borrower_message_transport_preferences t
91
        on      p.borrower_message_preference_id = t.borrower_message_preference_id
92
        where   p.message_attribute_id = 7
93
                and t.borrower_message_preference_id is null;
94
    });
95
96
97
    # Always end with this (adjust the bug info)
98
    SetVersion( $DBversion );
99
    print "Upgrade to $DBversion done (Bug 18532 - Add AUTO_RENEWALS in message_attributes and add AUTO_RENEWALS_DGST letter)\n";
100
}
(-)a/installer/data/mysql/en/mandatory/sample_notices.sql (+31 lines)
Lines 316-318 INSERT IGNORE INTO `letter` (`module`, `code`, `branchcode`, `name`, `is_html`, Link Here
316
316
317
INSERT INTO `letter` (`module`, `code`, `branchcode`, `name`, `is_html`, `title`, `content`, `message_transport_type`) VALUES
317
INSERT INTO `letter` (`module`, `code`, `branchcode`, `name`, `is_html`, `title`, `content`, `message_transport_type`) VALUES
318
('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');
318
('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');
319
320
INSERT INTO letter (module, code, name, title, content, message_transport_type) VALUES ('circulation', 'AUTO_RENEWALS', 'notification on auto renewing', 'Auto renewals',
321
"Dear [% borrower.firstname %] [% borrower.surname %],
322
[% IF checkout.auto_renew_error %]
323
The following item [% biblio.title %] has not been correctly renewed
324
[% IF checkout.auto_renew_error == 'too_many' %]
325
You have reach the maximum of checkouts possible.
326
[% ELSIF checkout.auto_renew_error == 'on_reserve' %]
327
This item is on hold for another patron.
328
[% ELSIF checkout.auto_renew_error == 'restriction' %]
329
You are currently restricted.
330
[% ELSIF checkout.auto_renew_error == 'overdue' %]
331
You have overdues.
332
[% ELSIF checkout.auto_renew_error == 'auto_too_late' %]
333
It\'s too late to renew this checkout.
334
[% ELSIF checkout.auto_renew_error == 'auto_too_much_oweing' %]
335
You have too much unpaid fines.
336
[% END %]
337
[% ELSE %]
338
The following item [% biblio.title %] has correctly been renewed and is now due [% checkout.date_due %]
339
[% END %]", 'email');
340
341
INSERT INTO letter (module, code, name, title, content, message_transport_type) VALUES ('circulation', 'AUTO_RENEWALS_DGST', 'notification on auto renewing', 'Auto renewals (Digest)',
342
"Dear [% borrower.firstname %] [% borrower.surname %],
343
[% IF <<error>> %]
344
There were <<error>> items that where not correctly renewed.
345
[% END %]
346
[% IF <<success>> %]
347
There were <<success>> items that where correctly renewed.
348
[% END %]
349
", 'email');
(-)a/installer/data/mysql/mandatory/sample_notices_message_attributes.sql (-1 / +2 lines)
Lines 5-9 values Link Here
5
(2, 'Advance_Notice', 1),
5
(2, 'Advance_Notice', 1),
6
(4, 'Hold_Filled', 0),
6
(4, 'Hold_Filled', 0),
7
(5, 'Item_Check_in', 0),
7
(5, 'Item_Check_in', 0),
8
(6, 'Item_Checkout', 0);
8
(6, 'Item_Checkout', 0),
9
(7, 'Auto_Renewals', 0);
9
10
(-)a/installer/data/mysql/mandatory/sample_notices_message_transports.sql (-2 / +5 lines)
Lines 17-20 values Link Here
17
(5, 'email', 0, 'circulation', 'CHECKIN'),
17
(5, 'email', 0, 'circulation', 'CHECKIN'),
18
(5, 'sms',   0, 'circulation', 'CHECKIN'),
18
(5, 'sms',   0, 'circulation', 'CHECKIN'),
19
(6, 'email', 0, 'circulation', 'CHECKOUT'),
19
(6, 'email', 0, 'circulation', 'CHECKOUT'),
20
(6, 'sms',   0, 'circulation', 'CHECKOUT');
20
(6, 'sms',   0, 'circulation', 'CHECKOUT'),
21
(7, 'email', 0, 'circulation', 'AUTO_RENEWALS'),
22
(7, 'sms', 0, 'circulation', 'AUTO_RENEWALS'),
23
(7, 'email', 1, 'circulation', 'AUTO_RENEWALS_DGST'),
24
(7, 'sms', 1, 'circulation', 'AUTO_RENEWALS_DGST');
21
- 

Return to bug 18532