$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";

}

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 <<error>> %]

There were <<error>> items that where not correctly renewed.

[% END %]

[% IF <<success>> %]

There were <<success>> items that where correctly renewed.

[% END %]

", 'email');