|
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 |
} |