if( CheckVersion( $DBversion ) ) {

if ( CheckVersion($DBversion) ) {

    # replace patron variable with credit.patron

    # backup existing notice to action_logs

    $dbh->do("UPDATE letter SET content = REPLACE(content, '[% patron', '[% credit.patron') WHERE code = 'ACCOUNT_CREDIT' ");

    my $credit_arr = $dbh->selectall_arrayref(

    # replace library variable with credit.library.branchname

        "SELECT lang FROM letter WHERE code = 'ACCOUNT_CREDIT'", { Slice => {} });

    $dbh->do("UPDATE letter SET content = REPLACE(content, '[% library', '[% credit.library.branchname') WHERE code = 'ACCOUNT_CREDIT' ");

    my $c_sth = $dbh->prepare(q{

      INSERT INTO action_logs ( timestamp, module, action, object, info, interface )

    # replace offsets variable with credit.offsets

      SELECT NOW(), 'NOTICES', 'UPGRADE', id, content, 'cli'

    $dbh->do("UPDATE letter SET content = REPLACE(content, ' offsets %]', ' credit.offsets %]') WHERE code = 'ACCOUNT_CREDIT' ");

      FROM letter

    # replace change_given variable with change

      WHERE lang = ? AND code = 'ACCOUNT_CREDIT'

    $dbh->do("UPDATE letter SET content = REPLACE(content, '[% change_given', '[% change') WHERE code = 'ACCOUNT_CREDIT' ");

    });

    # replace accounts foreach with basic check

    for my $c ( @{$credit_arr} ) {

    $dbh->do("UPDATE letter SET content = REPLACE(content, '[% FOREACH account IN accounts %]', '[% IF credit %]') WHERE code = 'ACCOUNT_CREDIT' ");

        $c_sth->execute( $c->{lang} );

    # replace account with credit

    }

    $dbh->do("UPDATE letter SET content = REPLACE(content, 'account.', 'credit.') WHERE code = 'ACCOUNT_CREDIT' ");

    # replace amountcredit with amount >= 0

    # replace notice with default

    $dbh->do("UPDATE letter SET content = REPLACE(content, '( credit.amountcredit )', '( credit.amount <= 0 )') WHERE code = 'ACCOUNT_CREDIT' ");

    my $c_notice = q{

[% USE Price %]

[% PROCESS 'accounts.inc' %]

<table>

[% IF ( LibraryName ) %]

 <tr>

    <th colspan="4" class="centerednames">

        <h3>[% LibraryName | html %]</h3>

    </th>

 </tr>

[% END %]

 <tr>

    <th colspan="4" class="centerednames">

        <h2><u>Fee receipt</u></h2>

    </th>

 </tr>

 <tr>

    <th colspan="4" class="centerednames">

        <h2>[% Branches.GetName( credit.patron.branchcode ) | html %]</h2>

    </th>

 </tr>

 <tr>

    <th colspan="4">

        Received with thanks from  [% credit.patron.firstname | html %] [% credit.patron.surname | html %] <br />

        Card number: [% credit.patron.cardnumber | html %]<br />

    </th>

 </tr>

  <tr>

    <th>Date</th>

    <th>Description of charges</th>

    <th>Note</th>

    <th>Amount</th>

 </tr>

 <tr class="highlight">

    <td>[% credit.date | $KohaDates %]</td>

    <td>

      [% PROCESS account_type_description account=credit %]

      [%- IF credit.description %], [% credit.description | html %][% END %]

    </td>

    <td>[% credit.note | html %]</td>

    <td class="credit">[% credit.amount | $Price %]</td>

 </tr>

<tfoot>

  <tr>

    <td colspan="3">Total outstanding dues as on date: </td>

    [% IF ( credit.patron.account.balance >= 0 ) %]<td class="credit">[% ELSE %]<td class="debit">[% END %][% credit.patron.account.balance | $Price %]</td>

  </tr>

</tfoot>

</table>

    };

    my $c_insert = $dbh->prepare("UPDATE letter SET content = ?, is_html = 1 WHERE code = 'ACCOUNT_CREDIT'");

    $c_insert->execute($c_notice);

    # replace patron variable with debit.patron

    # backup existing notice to action_logs

    $dbh->do("UPDATE letter SET content = REPLACE(content, '[% patron', '[% debit.patron') WHERE code = 'ACCOUNT_DEBIT' ");

    my $debit_arr = $dbh->selectall_arrayref(

    # replace library variable with debit.library.branchname

        "SELECT lang FROM letter WHERE code = 'ACCOUNT_DEBIT'", { Slice => {} });

    $dbh->do("UPDATE letter SET content = REPLACE(content, '[% library', '[% debit.library.branchname') WHERE code = 'ACCOUNT_DEBIT' ");

    my $d_sth = $dbh->prepare(q{

    # replace offsets variable with debit.offsets

      INSERT INTO action_logs ( timestamp, module, action, object, info, interface )

    $dbh->do("UPDATE letter SET content = REPLACE(content, ' offsets %]', ' debit.offsets %]') WHERE code = 'ACCOUNT_DEBIT' ");

      SELECT NOW(), 'NOTICES', 'UPGRADE', id, content, 'cli'

      FROM letter

    # replace accounts foreach with basic check

      WHERE lang = ? AND code = 'ACCOUNT_DEBIT'

    $dbh->do("UPDATE letter SET content = REPLACE(content, '[% FOREACH account IN accounts %]', '[% IF debit %]') WHERE code = 'ACCOUNT_DEBIT' ");

    });

    # replace account with debit

    $dbh->do("UPDATE letter SET content = REPLACE(content, 'account.', 'debit.') WHERE code = 'ACCOUNT_DEBIT' ");

    for my $d ( @{$debit_arr} ) {

    # replace amountcredit with amount >= 0

        $d_sth->execute( $d->{lang} );

    $dbh->do("UPDATE letter SET content = REPLACE(content, '( debit.amountcredit )', '( debit.amount <= 0 )') WHERE code = 'ACCOUNT_DEBIT' ");

    }

    # replace amountoutstandingcredit with amount >= 0

    $dbh->do("UPDATE letter SET content = REPLACE(content, '( debit.amountoutstandingcredit )', '( debit.amountoutstanding <= 0 )') WHERE code = 'ACCOUNT_DEBIT' ");

    # replace notice with default

    my $d_notice = q{

    # replace total variable with debit.patron.account.balance

[% USE Price %]

    $dbh->do("UPDATE letter SET content = REPLACE(content, '[% total ', '[% debit.patron.account.balance ') WHERE code = 'ACCOUNT_DEBIT' ");

[% PROCESS 'accounts.inc' %]

    # replace totalcredit variable with debit.patron.account.balance <= 0

<table>

    $dbh->do("UPDATE letter SET content = REPLACE(content, 'totalcredit', 'debit.patron.account.balance <= 0') WHERE code = 'ACCOUNT_DEBIT' ");

  [% IF ( LibraryName ) %]

    <tr>

    NewVersion( $DBversion, 26734, "Update notices to use standard variables");

      <th colspan="5" class="centerednames">

        <h3>[% LibraryName | html %]</h3>

      </th>

    </tr>

  [% END %]

  <tr>

    <th colspan="5" class="centerednames">

      <h2><u>INVOICE</u></h2>

    </th>

  </tr>

  <tr>

    <th colspan="5" class="centerednames">

      <h2>[% Branches.GetName( debit.patron.branchcode ) | html %]</h2>

    </th>

  </tr>

  <tr>

    <th colspan="5" >

      Bill to: [% debit.patron.firstname | html %] [% debit.patron.surname | html %] <br />

      Card number: [% debit.patron.cardnumber | html %]<br />

    </th>

  </tr>

  <tr>

    <th>Date</th>

    <th>Description of charges</th>

    <th>Note</th>

    <th style="text-align:right;">Amount</th>

    <th style="text-align:right;">Amount outstanding</th>

  </tr>

  <tr class="highlight">

    <td>[% debit.date | $KohaDates%]</td>

    <td>

      [% PROCESS account_type_description account=debit %]

      [%- IF debit.description %], [% debit.description | html %][% END %]

    </td>

    <td>[% debit.note | html %]</td>

    <td class="debit">[% debit.amount | $Price %]</td>

    <td class="debit">[% debit.amountoutstanding | $Price %]</td>

  </tr>

  <tfoot>

    <tr>

      <td colspan="4">Total outstanding dues as on date: </td>

      [% IF ( debit.patron.account.balance <= 0 ) %]<td class="credit">[% ELSE %]<td class="debit">[% END %][% debit.patron.account.balance | $Price %]</td>

    </tr>

  </tfoot>

</table>

    };

    my $d_insert = $dbh->prepare("UPDATE letter SET content = ?, is_html = 1 WHERE code = 'ACCOUNT_DEBIT'");

    $d_insert->execute($d_notice);

    NewVersion( $DBversion, 26734, "Update notices to use defaults" );