Bug 19036: Add ability to auto generate a number for account credits

In some areas it's required to sequentially number payment slips /
receipts.

This patch adds a database column accountlines.credit_number and a
system preference AutoCreditNumber to control how this number will be
generated.  The following options are available:

- Do not automatically generate credit numbers.
  This is the current behaviour and the default syspref value.

- Automatically generate credit numbers in the form <year>-0001 (annual)

- Automatically generate credit numbers in the form
  <branchcode>yyyymm0001 (branchyyyymmincr)
  where <branchcode> is the branch where the user (staff member) is
  logged in

- Automatically generate credit numbers in the form 1, 2, 3
  (incremental)

It also adds a column (hidden by default) in the table under
Transactions tab to display this number.

Test plan:
0. Apply patch, run updatedatabase and update_dbix_class_files
1. Go to Admin » Column settings, and uncheck the 'hidden' box for
   column credit_number in table account-fines. It will be easier for
   testing
2. Create a manual credit for a borrower. Verify in Transactions tab
   that this credit has no number generated
3. Change syspref 'AutoCreditNumber' to 'incremental'
4. Create more manual credits, and verify that the numbers generated are
   1, 2, 3, ...
5. Change syspref 'AutoCreditNumber' to 'annual'
6. Create more manual credits, and verify that the numbers generated are
   '2020-0001', '2020-0002', ...
7. Change syspref to 'AutoCreditNumber' to 'branchyyyymmincr'
8. Create more manual credits, and verify that the numbers generated are
   'BRANCHA2020020001', 'BRANCHA2020020002', ... (assuming you are
   connected to library BRANCHA, and it's February 2020)
9. Set library to another one, say BRANCHB
10. Create more manual credits, and verify that the numbers generated are
    'BRANCHB2020020001', 'BRANCHB2020020002', ...
11. Edit the letter ACCOUNT_CREDIT, and add [% account.credit_number %]
    somewhere. Go back to Transactions tab and click on 'Print' for one
    line that has a credit number. Make sure the number is there.
12. prove t/db_dependent/Koha/Account.t

Signed-off-by: Michal Denar <black23@gmail.com>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>

diff --git a/Koha/Account/Line.pm b/Koha/Account/Line.pm
index 7ed59a9..5272b34 100644 (file)
--- a/Koha/Account/Line.pm
+++ b/Koha/Account/Line.pm
@@ -876,6 +876,64 @@ sub renew_item {
 
 }
 
+=head3 store
+
+Specific store method to generate credit number before saving
+
+=cut
+
+sub store {
+    my ($self) = @_;
+
+    my $AutoCreditNumber = C4::Context->preference('AutoCreditNumber');
+    if ($AutoCreditNumber && !$self->in_storage && $self->is_credit && !$self->credit_number) {
+        my $rs = Koha::Database->new->schema->resultset($self->_type);
+
+        if ($AutoCreditNumber eq 'incremental') {
+            my $max = $rs->search({
+                credit_number => { -regexp => '^[0-9]+$' }
+            }, {
+                select => \'CAST(credit_number AS UNSIGNED)',
+                as => ['credit_number'],
+            })->get_column('credit_number')->max;
+            $max //= 0;
+            $self->credit_number($max + 1);
+        } elsif ($AutoCreditNumber eq 'annual') {
+            my $now = DateTime->now;
+            my $prefix = sprintf('%d-', $now->year);
+            my $max = $rs->search({
+                -and => [
+                    credit_number => { -regexp => '[0-9]{4}$' },
+                    credit_number => { -like => "$prefix%" },
+                ],
+            })->get_column('credit_number')->max;
+            $max //= $prefix . '0000';
+            my $incr = substr($max, length $prefix);
+            $self->credit_number(sprintf('%s%04d', $prefix, $incr + 1));
+        } elsif ($AutoCreditNumber eq 'branchyyyymmincr') {
+            my $userenv = C4::Context->userenv;
+            if ($userenv) {
+                my $branch = $userenv->{branch};
+                my $now = DateTime->now;
+                my $prefix = sprintf('%s%d%02d', $branch, $now->year, $now->month);
+                my $pattern = $prefix;
+                $pattern =~ s/([\?%_])/\\$1/g;
+                my $max = $rs->search({
+                    -and => [
+                        credit_number => { -regexp => '[0-9]{4}$' },
+                        credit_number => { -like => "$pattern%" },
+                    ],
+                })->get_column('credit_number')->max;
+                $max //= $prefix . '0000';
+                my $incr = substr($max, length $prefix);
+                $self->credit_number(sprintf('%s%04d', $prefix, $incr + 1));
+            }
+        }
+    }
+
+    return $self->SUPER::store();
+}
+
 =head2 Internal methods
 
 =cut

diff --git a/admin/columns_settings.yml b/admin/columns_settings.yml
index a73e577..8ef33f0 100644 (file)
--- a/admin/columns_settings.yml
+++ b/admin/columns_settings.yml
@@ -651,6 +651,9 @@ modules:
             -
               columnname: date
             -
+              columnname: credit_number
+              is_hidden: 1
+            -
               columnname: account_type
             -
               columnname: description

diff --git a/installer/data/mysql/atomicupdate/bug-19036.perl b/installer/data/mysql/atomicupdate/bug-19036.perl
new file mode 100644 (file)
index 0000000..7a144bc
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug-19036.perl
@@ -0,0 +1,11 @@
+$DBversion = 'XXX';
+if (CheckVersion($DBversion)) {
+    unless (column_exists('accountlines', 'credit_number')) {
+        $dbh->do('ALTER TABLE accountlines ADD COLUMN credit_number VARCHAR(20) NULL DEFAULT NULL COMMENT "autogenerated number for credits" AFTER debit_type_code');
+    }
+
+    $dbh->do('INSERT IGNORE INTO systempreferences (variable, value, options, explanation, type) VALUES(?, ?, ?, ?, ?)', undef, 'AutoCreditNumber', '', '', 'Automatically generate a number for account credits', 'Choice');
+
+    SetVersion($DBversion);
+    print "Upgrade to $DBversion done (Bug 19036 - Add column accountlines.credit_number)\n";
+}

diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 35c643f..2e3a42a 100644 (file)
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -2723,6 +2723,7 @@ CREATE TABLE `accountlines` (
   `description` LONGTEXT,
   `credit_type_code` varchar(80) default NULL,
   `debit_type_code` varchar(80) default NULL,
+  `credit_number` varchar(20) NULL DEFAULT NULL COMMENT 'autogenerated number for credits',
   `status` varchar(16) default NULL,
   `payment_type` varchar(80) default NULL, -- optional authorised value PAYMENT_TYPE
   `amountoutstanding` decimal(28,6) default NULL,

diff --git a/installer/data/mysql/sysprefs.sql b/installer/data/mysql/sysprefs.sql
index 0924a42..09564db 100644 (file)
--- a/installer/data/mysql/sysprefs.sql
+++ b/installer/data/mysql/sysprefs.sql
@@ -70,6 +70,7 @@ INSERT INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `
 ('AuthSuccessLog','',NULL,'If enabled, log successful authentications','YesNo'),
 ('autoBarcode','OFF','incremental|annual|hbyymmincr|EAN13|OFF','Used to autogenerate a barcode: incremental will be of the form 1, 2, 3; annual of the form 2007-0001, 2007-0002; hbyymmincr of the form HB08010001 where HB=Home Branch','Choice'),
 ('AutoCreateAuthorities','0',NULL,'Automatically create authorities that do not exist when cataloging records.','YesNo'),
+('AutoCreditNumber', '', '', 'Automatically generate a number for account credits', 'Choice'),
 ('AutoEmailOpacUser','0',NULL,'Sends notification emails containing new account details to patrons - when account is created.','YesNo'),
 ('AutoEmailPrimaryAddress','OFF','email|emailpro|B_email|cardnumber|OFF','Defines the default email address where \'Account Details\' emails are sent.','Choice'),
 ('AutoShareWithMana','subscription','','defines datas automatically shared with mana','multiple'),

diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/accounting.pref b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/accounting.pref
index 4a11872..5cbfe8d 100644 (file)
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/accounting.pref
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/accounting.pref
@@ -33,3 +33,10 @@ Accounting:
                 yes: "Enable"
                 no: "Disable"
             - " the point of sale feature to allow anonymous transactions with the accounting system. (Requires UseCashRegisters)"
+        -
+            - pref: AutoCreditNumber
+              choices:
+                '': 'Do not automatically generate credit numbers'
+                annual: 'Automatically generate credit numbers in the form <year>-0001'
+                branchyyyymmincr: 'Automatically generate credit numbers in the form <branchcode>yyyymm0001'
+                incremental: 'Automatically generate credit numbers in the form 1, 2, 3'

diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/members/boraccount.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/members/boraccount.tt
index ec554de..74243d8 100644 (file)
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/members/boraccount.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/members/boraccount.tt
@@ -45,6 +45,7 @@
     <thead>
       <tr>
           <th class="title-string">Date</th>
+          <th>Credit number</th>
           <th>Account type</th>
           <th>Description of charges</th>
           <th>Barcode</th>
@@ -64,6 +65,7 @@
 
    <tr>
    <td><span title="[% account.date | html %]">[% account.date |$KohaDates %]</span></td>
+        <td>[% account.credit_number | html %]</td>
         <td>[% PROCESS account_type_description account=account %]</td>
       <td>
         [%- IF account.payment_type %][% AuthorisedValues.GetByCode('PAYMENT_TYPE', account.payment_type) | html %][% END %]

diff --git a/t/db_dependent/Koha/Account.t b/t/db_dependent/Koha/Account.t
index ebab6b0..9ebad52 100755 (executable)
--- a/t/db_dependent/Koha/Account.t
+++ b/t/db_dependent/Koha/Account.t
@@ -1049,3 +1049,51 @@ subtest 'Koha::Account::Line::apply() handles lost items' => sub {
 
     $schema->storage->txn_rollback;
 };
+
+subtest 'Koha::Account::pay() generates credit number' => sub {
+    plan tests => 34;
+
+    $schema->storage->txn_begin;
+
+    Koha::Account::Lines->delete();
+
+    my $patron  = $builder->build_object( { class => 'Koha::Patrons' } );
+    my $library = $builder->build_object( { class => 'Koha::Libraries' } );
+    my $account = $patron->account;
+
+    my $context = Test::MockModule->new('C4::Context');
+    $context->mock( 'userenv', { branch => $library->id } );
+
+    my $now = DateTime->now;
+    my $year = $now->year;
+    my $month = $now->month;
+    my ($accountlines_id, $accountline);
+
+    t::lib::Mocks::mock_preference('AutoCreditNumber', '');
+    $accountlines_id = $account->pay({ amount => '1.00', library_id => $library->id })->{payment_id};
+    $accountline = Koha::Account::Lines->find($accountlines_id);
+    is($accountline->credit_number, undef, 'No credit number is generated when syspref is off');
+
+    t::lib::Mocks::mock_preference('AutoCreditNumber', 'incremental');
+    for my $i (1..11) {
+        $accountlines_id = $account->pay({ amount => '1.00', library_id => $library->id })->{payment_id};
+        $accountline = Koha::Account::Lines->find($accountlines_id);
+        is($accountline->credit_number, $i);
+    }
+
+    t::lib::Mocks::mock_preference('AutoCreditNumber', 'annual');
+    for my $i (1..11) {
+        $accountlines_id = $account->pay({ amount => '1.00', library_id => $library->id })->{payment_id};
+        $accountline = Koha::Account::Lines->find($accountlines_id);
+        is($accountline->credit_number, sprintf('%s-%04d', $year, $i));
+    }
+
+    t::lib::Mocks::mock_preference('AutoCreditNumber', 'branchyyyymmincr');
+    for my $i (1..11) {
+        $accountlines_id = $account->pay({ amount => '1.00', library_id => $library->id })->{payment_id};
+        $accountline = Koha::Account::Lines->find($accountlines_id);
+        is($accountline->credit_number, sprintf('%s%d%02d%04d', $library->id, $year, $month, $i));
+    }
+
+    $schema->storage->txn_rollback;
+};