[Koha-bugs] [Bug 21065] New: Data in accountoffsets and accountlines is deleted with the patron leaving gaps in financial reports

[bugzilla-daemon at bugs.koha-community.org]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065

            Bug ID: 21065
           Summary: Data in accountoffsets and accountlines is deleted
                    with the patron leaving gaps in financial reports
 Change sponsored?: ---
           Product: Koha
           Version: master
          Hardware: All
                OS: All
            Status: NEW
          Severity: major
          Priority: P5 - low
         Component: Circulation
          Assignee: koha-bugs at lists.koha-community.org
          Reporter: katrin.fischer at bsz-bw.de
        QA Contact: testopia at bugs.koha-community.org
                CC: gmcharlt at gmail.com, kyle.m.hall at gmail.com

The tables accountoffsets and accountlines are both "cleaned up" when a patron
is deleted by using FK constraints. 

This is a problem because 
1) The patrons have a "right to be forgotten" with GDPR and you can't refuse to
delete them if their fines are paid etc.
2) Especially for partial payments and in other cases the data in accountlines
and accountoffsets is neded to create reliable reports for statistics, but also
for financial reports.

It's not possible to determine from other tables like statistics and
action_logs what fines are linked to which payment which doesn't allow to
create reports by fine/fee type and similar.

Instead of deleting the info, we should really just anonymize it.

--

CREATE TABLE `accountoffsets` (
  `borrowernumber` int(11) NOT NULL DEFAULT '0',
  `accountno` smallint(6) NOT NULL DEFAULT '0',
  `offsetaccount` smallint(6) NOT NULL DEFAULT '0',
  `offsetamount` decimal(28,6) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
  KEY `accountoffsets_ibfk_1` (`borrowernumber`),
  CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES
`borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `accountlines` (
  `accountlines_id` int(11) NOT NULL AUTO_INCREMENT,
  `issue_id` int(11) DEFAULT NULL,
  `borrowernumber` int(11) NOT NULL DEFAULT '0',
  `accountno` smallint(6) NOT NULL DEFAULT '0',
  `itemnumber` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `amount` decimal(28,6) DEFAULT NULL,
  `description` mediumtext COLLATE utf8_unicode_ci,
  `dispute` mediumtext COLLATE utf8_unicode_ci,
  `accounttype` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
  `amountoutstanding` decimal(28,6) DEFAULT NULL,
  `lastincrement` decimal(28,6) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
  `notify_id` int(11) NOT NULL DEFAULT '0',
  `notify_level` int(2) NOT NULL DEFAULT '0',
  `note` text COLLATE utf8_unicode_ci,
  `manager_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`accountlines_id`),
  KEY `acctsborridx` (`borrowernumber`),
  KEY `timeidx` (`timestamp`),
  KEY `itemnumber` (`itemnumber`),
  CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES
`borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES
`items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=4666 DEFAULT CHARSET=utf8
COLLATE=utf8_unicode_ci;

-- 
You are receiving this mail because:
You are the assignee for the bug.
You are watching all bug changes.