$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, 7, NULL, 1

        from    categories c

        left join

                borrower_message_preferences p

        on      c.categorycode = p.categorycode

                and p.message_attribute_id = 7

        where   p.categorycode is null

    });

    $dbh->do( qq{

        insert ignore into borrower_message_preferences (borrowernumber, message_attribute_id, days_in_advance, wants_digest)

        select 	b.borrowernumber, 7, NULL, 1

        from    borrowers b

        left join

                borrower_message_preferences p

        on      b.borrowernumber = p.borrowernumber

                and p.message_attribute_id = 7

        where   p.borrowernumber is null

    });

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