$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')

    });

    # 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');