Attachment 149872 Details for Bug 32450 – Bug 32450: Update db to add noissuescharge flag and remove sysprefs

[patch] Bug 32450: Update db to add noissuescharge flag and remove sysprefs

Bug-32450-Update-db-to-add-noissuescharge-flag-and.patch (text/plain), 10.45 KB, created by Marcel de Rooy on 2023-04-19 12:42:11 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Marcel de Rooy

Created: 2023-04-19 12:42:11 UTC

Size: 10.45 KB

patch

obsolete

>From f853652afd890c918cefcc41bf5630eb00e3dfb5 Mon Sep 17 00:00:00 2001
>From: Matt Blenkinsop <matt.blenkinsop@ptfs-europe.com>
>Date: Fri, 6 Jan 2023 13:52:32 +0000
>Subject: [PATCH] Bug 32450: Update db to add noissuescharge flag and remove
> sysprefs
>Content-Type: text/plain; charset=utf-8
>
>This commit updates the database to add a new flag to show which debit_types are
>included in noissuescharge. It also deletes the current sysprefs that are
>hardcoded for this as these are now redundant.
>
>Test plan:
>1) Choose a patron and add some fines to this patron that have different debit_types
>2) Navigate to system preferences and observe that currently you can only amend the
>   noissuescharge included debit types using three preferences: ManInvInNoissuesCharge,
>   RentalsInNoissuesCharge, HoldsInNoissuesCharge
>3) Apply both commits attached to this bug
>4) Navigate as above and observe that these three system preferences are now gone
>5) Navigate to Debit Types in System Preferences, the table should have a column
>   called No issues charge that shows whether a debit_type is Included or Not included
>6) Click the edit button and there should be a checkbox for Included in noissuescharge
>7) Change some of the debit_types using this option and observe that the patron you
>   added fines to will either be blocked from checkouts or able to checkout depending
>   on which debit_types you include and the value of these fines.
>
>Mentored-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
>Signed-off-by: Michaela Sieber <michaela.sieber@kit.edu>
>
>Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
>---
> .../bug_32450-add_debit_type_flag.pl          | 77 +++++++++++++++++++
> installer/data/mysql/kohastructure.sql        |  1 +
> installer/data/mysql/mandatory/sysprefs.sql   |  2 -
> .../admin/preferences/circulation.pref        | 18 -----
> 4 files changed, 78 insertions(+), 20 deletions(-)
> create mode 100644 installer/data/mysql/atomicupdate/bug_32450-add_debit_type_flag.pl
>
>diff --git a/installer/data/mysql/atomicupdate/bug_32450-add_debit_type_flag.pl b/installer/data/mysql/atomicupdate/bug_32450-add_debit_type_flag.pl
>new file mode 100644
>index 0000000000..404747061e
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_32450-add_debit_type_flag.pl
>@@ -0,0 +1,77 @@
>+use Modern::Perl;
>+
>+return {
>+    bug_number => "32450",
>+    description => "Create a database flag for whether a debit type should be included in the noissuescharge block on circulation and remove redundant sysprefs.",
>+    up => sub {
>+        my ($args) = @_;
>+        my ($dbh, $out) = @$args{qw(dbh out)};
>+
>+        if( !column_exists( 'account_debit_types', 'restricts_checkouts' ) ) {
>+          $dbh->do(q{
>+              ALTER TABLE account_debit_types ADD COLUMN `restricts_checkouts` tinyint(1) NOT NULL DEFAULT 1 AFTER `archived`
>+          });
>+
>+          say $out "Added column 'account_debit_types.restricts_checkouts'";
>+        }
>+
>+        # Before deleting the redundant system preferences we need to check if they had been modified and update the new database flags accordingly
>+        my @sysprefs = ('ManInvInNoissuesCharge', 'RentalsInNoissuesCharge', 'HoldsInNoissuesCharge');
>+
>+        # Hardcoded values from sub non_issues_charges
>+        my @holds = ('RESERVE');
>+        my @renewals = ('RENT', 'RENT_DAILY', 'RENT_RENEW', 'RENT_DAILY_RENEW');
>+        my @manual;
>+        my $sth = $dbh->prepare("SELECT code FROM account_debit_types WHERE is_system = 0");
>+        $sth->execute;
>+        while (my $code = $sth->fetchrow_array) {
>+            push @manual, $code;
>+        }
>+
>+        for (@sysprefs){
>+            # Check if the syspref exists in the database
>+            my $check_syspref_exists = "SELECT COUNT(*) FROM systempreferences WHERE variable = '$_'";
>+            my $sth = $dbh->prepare($check_syspref_exists);
>+            $sth->execute;
>+            my $exists = $sth->fetchrow();
>+            $sth->finish;
>+
>+            if($exists) {
>+                # If it exists, retrieve its value
>+                my $find_syspref_value = "SELECT value FROM systempreferences WHERE variable = '$_'";
>+                my $sth = $dbh->prepare($find_syspref_value);
>+                $sth->execute;
>+                my $value = $sth->fetchrow();
>+                $sth->finish;
>+
>+                if($value){
>+                    say $out "$_ is included in the charge, default database value of 1 can be applied.";
>+                } else {
>+                    # Update account_debit_types to reflect existing syspref value.
>+                    my @debit_types_to_update;
>+
>+                    if($_ eq 'ManInvInNoissuesCharge') { push @debit_types_to_update, @manual};
>+                    if($_ eq 'RentalsInNoissuesCharge') { push @debit_types_to_update, @renewals};
>+                    if($_ eq 'HoldsInNoissuesCharge') { push @debit_types_to_update, @holds};
>+
>+                    my $string = join(",", map { $dbh->quote($_) } @debit_types_to_update);
>+                    my $update_query = "UPDATE account_debit_types SET restricts_checkouts = 0 WHERE code IN (" . $string . ")";
>+                    my $sth = $dbh->prepare($update_query);
>+                    $sth->execute;
>+                    $sth->finish;
>+
>+                    say $out "$_ has been updated to not be included in the charge, account_debit_types has been updated to match this.";
>+                }
>+
>+                # Delete syspref as it is no longer required and the value has been transferred to account_debit_types
>+                my $delete_redundant_syspref = "DELETE FROM systempreferences WHERE variable = '$_'";
>+                $dbh->do($delete_redundant_syspref);
>+
>+            } else {
>+                # If it doesn't exist then revert to default value in the database schema
>+                say $out "$_ was not found in this Koha instance, default value has been applied.";
>+            }
>+
>+        }
>+    },
>+};
>\ No newline at end of file
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index cba889d5e0..b771c39b97 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -65,6 +65,7 @@ CREATE TABLE `account_debit_types` (
>   `default_amount` decimal(28,6) DEFAULT NULL,
>   `is_system` tinyint(1) NOT NULL DEFAULT 0,
>   `archived` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'boolean flag to denote if this till is archived or not',
>+  `restricts_checkouts` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'boolean flag to denote if the noissuescharge syspref for this debit type is active',
>   PRIMARY KEY (`code`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
> /*!40101 SET character_set_client = @saved_cs_client */;
>diff --git a/installer/data/mysql/mandatory/sysprefs.sql b/installer/data/mysql/mandatory/sysprefs.sql
>index e899c44ba5..3bfcd98b2f 100644
>--- a/installer/data/mysql/mandatory/sysprefs.sql
>+++ b/installer/data/mysql/mandatory/sysprefs.sql
>@@ -358,7 +358,6 @@ INSERT INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `
> ('LockExpiredDelay','','','Delay for locking expired patrons (empty means no locking)','Integer'),
> ('makePreviousSerialAvailable','0','','make previous serial automatically available when collecting a new serial. Please note that the item-level_itypes syspref must be set to specific item.','YesNo'),
> ('Mana','2',NULL,'request to Mana Webservice. Mana centralize common information between other Koha to facilitate the creation of new subscriptions, vendors, report queries etc... You can search, share, import and comment the content of Mana.','Choice'),
>-('ManInvInNoissuesCharge','1',NULL,'MANUAL_INV charges block checkouts (added to noissuescharge).','YesNo'),
> ('MARCAuthorityControlField008','|| aca||aabn           | a|a     d',NULL,'Define the contents of MARC21 authority control field 008 position 06-39','Textarea'),
> ('MarcFieldDocURL', NULL, NULL, 'URL used for MARC field documentation. Following substitutions are available: {MARC} = marc flavour, eg. "MARC21" or "UNIMARC". {FIELD} = field number, eg. "000" or "048". {LANG} = user language, eg. "en" or "fi-FI"', 'free'),
> ('MarcFieldForCreatorId','',NULL,'Where to store the borrowernumber of the record''s creator','Free'),
>@@ -605,7 +604,6 @@ INSERT INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `
> ('RenewalSendNotice','0','',NULL,'YesNo'),
> ('RenewSerialAddsSuggestion','0',NULL,'If ON, adds a new suggestion at serial subscription renewal','YesNo'),
> ('RentalFeesCheckoutConfirmation', '0', NULL , 'Allow user to confirm when checking out an item with rental fees.', 'YesNo'),
>-('RentalsInNoissuesCharge','1',NULL,'Rental charges block checkouts (added to noissuescharge).','YesNo'),
> ('ReplyToDefault','',NULL,'Use this email address as the replyto in emails','Free'),
> ('ReportsLog','0',NULL,'If ON, log information about reports.','YesNo'),
> ('RequireCashRegister','0',NULL,'Require a cash register when collecting a payment','YesNo'),
>diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/circulation.pref b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/circulation.pref
>index cafd44be51..0deb72f7f4 100644
>--- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/circulation.pref
>+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/circulation.pref
>@@ -375,24 +375,6 @@ Circulation:
>             - pref: NoIssuesChargeGuarantorsWithGuarantees
>               class: integer
>             - '[% local_currency %] in fines.'
>-        -
>-            - pref: RentalsInNoissuesCharge
>-              choices:
>-                  1: Include
>-                  0: "Don't include"
>-            - rental charges when summing up charges for limit set in the <a href="/cgi-bin/koha/admin/preferences.pl?op=search&searchfield=noissuescharge">noissuescharge</a> system preference.
>-        -
>-            - pref: ManInvInNoissuesCharge
>-              choices:
>-                  1: Include
>-                  0: "Don't include"
>-            - custom debit type charges when summing up charges for limit set in the <a href="/cgi-bin/koha/admin/preferences.pl?op=search&searchfield=noissuescharge">noissuescharge</a> system preference.
>-        -
>-            - pref: HoldsInNoissuesCharge
>-              choices:
>-                  1: Include
>-                  0: "Don't include"
>-            - hold charges when summing up charges for limit set in the <a href="/cgi-bin/koha/admin/preferences.pl?op=search&searchfield=noissuescharge">noissuescharge</a> system preference.
>         -
>             - pref: ReturnBeforeExpiry
>               choices:
>-- 
>2.30.2

Actions: View | Diff | Splinter Review

Attachments on bug 32450: 145080 | 145081 | 145127 | 145128 | 145171 | 145172 | 145189 | 145190 | 145191 | 145194 | 145198 | 147563 | 149653 | 149654 | 149655 | 149656 | 149657 | 149658 | 149659 | 149660 | 149858 | 149859 | 149872 | 149873 | 149874 | 149875 | 149876 | 149877 | 149878 | 149879 | 149880 | 149881 | 149882