Re: [Koha] Fines question
Hi Ian - thanks for your reply and help. It jogged my memory and reminded me of
the new (to 17.11) account_offsets table. We only upgraded to this last summer,
so only have data in there since then. Doing something like the below would
give the amount of fines we have written off?
select round(sum(amount),2) from account_offsets
where debit_id in (
select accountlines_id
from accountlines
where accounttype = 'f'
and date between '2018-09-01' AND '2019-01-31')
and type = "Writeoff";
cheers, Stephen
-Original Message-
From: Koha On Behalf Of Ian Bays
Sent: 31 January 2019 10:41
To: koha@lists.katipo.co.nz
Subject: Re: [Koha] Fines question
Hi Graham,
This may not be perfect, but do you get a reasonable match if you see which
writeoff accountlines match the amount of the replacement price for the items?
eg:
select count(*) from accountlines where accounttype = 'W' and (amount = (select
replacementprice from items where itemnumber =
accountlines.itemnumber) OR amount = (select replacementprice from deleteditems
where itemnumber = accountlines.itemnumber));
If you want a bit more precision I believe the action_logs link the writeoff
accountline to the original accountline but that might take some fairly complex
SQL and/or scripting. So for example an entry in action_logs for a writeoff
might include in the info field:
$VAR1 = {
'itemnumber' => '524905',
'manager_id' => '143623',
'accounttype' => 'W',
'accountno' => '17',
'amount' => '-0.35',
'action' => 'create_writeoff',
'borrowernumber' => '206622',
'accountlines_paid' => [
'1930'
]
};
Which in turn points to the original accountline which indicates it is an
accountline type "F".
I hope this helps. If anyone has a snappier solution please step forward :-)
Ian
On 31/01/2019 10:04, Stephen Graham wrote:
> Hi All - we have two main ways of generating "fines" - overdue items or book
> replacement costs. I can see in the accountlines table that for overdue books
> the fines have an accounttype of either F or FU, and for book replacement
> costs it's usually L or LR. However, if we write off/wipe a fine the
> accounttype changes to W. Once this has happened is it possible to work out
> if the fine was for a book replacement or a overdue item? We've been asked if
> we can produce a report which has the total amount of all overdue fine wiped
> off in the last six months, but because all these are now set to W I cannot
> see a way of distinguishing the two different fine types. We are on 17.11.
>
> Cheers, Stephen
>
> --
> Stephen Graham
> Library Technology Consultant
> Content and Collections Team
> Library and Computing Services
> University of Hertfordshire
> Tel: 01707 286111
> Ext: 77751
> Email: s.grah...@herts.ac.uk
>
>
>
> ___
> Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
>
> ---
> This email has been checked for viruses by AVG.
> https://www.avg.com
--
Ian Bays
Director, PTFS Europe Limited
Content Management and Library Solutions
+44 (0) 7774 995297 (mobile)
skype: ian.bays
email: ian.b...@ptfs-europe.com
___
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha
___
Koha mailing list http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha
Re: [Koha] Fines question
Hi Graham,
This may not be perfect, but do you get a reasonable match if you see
which writeoff accountlines match the amount of the replacement price
for the items?
eg:
select count(*) from accountlines where accounttype = 'W' and (amount =
(select replacementprice from items where itemnumber =
accountlines.itemnumber) OR amount = (select replacementprice from
deleteditems where itemnumber = accountlines.itemnumber));
If you want a bit more precision I believe the action_logs link the
writeoff accountline to the original accountline but that might take
some fairly complex SQL and/or scripting. So for example an entry in
action_logs for a writeoff might include in the info field:
$VAR1 = {
'itemnumber' => '524905',
'manager_id' => '143623',
'accounttype' => 'W',
'accountno' => '17',
'amount' => '-0.35',
'action' => 'create_writeoff',
'borrowernumber' => '206622',
'accountlines_paid' => [
'1930'
]
};
Which in turn points to the original accountline which indicates it is
an accountline type "F".
I hope this helps. If anyone has a snappier solution please step forward :-)
Ian
On 31/01/2019 10:04, Stephen Graham wrote:
Hi All - we have two main ways of generating "fines" - overdue items or book
replacement costs. I can see in the accountlines table that for overdue books the fines
have an accounttype of either F or FU, and for book replacement costs it's usually L or
LR. However, if we write off/wipe a fine the accounttype changes to W. Once this has
happened is it possible to work out if the fine was for a book replacement or a overdue
item? We've been asked if we can produce a report which has the total amount of all
overdue fine wiped off in the last six months, but because all these are now set to W I
cannot see a way of distinguishing the two different fine types. We are on 17.11.
Cheers, Stephen
--
Stephen Graham
Library Technology Consultant
Content and Collections Team
Library and Computing Services
University of Hertfordshire
Tel: 01707 286111
Ext: 77751
Email: s.grah...@herts.ac.uk
___
Koha mailing list http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha
---
This email has been checked for viruses by AVG.
https://www.avg.com
--
Ian Bays
Director, PTFS Europe Limited
Content Management and Library Solutions
+44 (0) 7774 995297 (mobile)
skype: ian.bays
email: ian.b...@ptfs-europe.com
___
Koha mailing list http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha
[Koha] Fines question
Hi All - we have two main ways of generating "fines" - overdue items or book replacement costs. I can see in the accountlines table that for overdue books the fines have an accounttype of either F or FU, and for book replacement costs it's usually L or LR. However, if we write off/wipe a fine the accounttype changes to W. Once this has happened is it possible to work out if the fine was for a book replacement or a overdue item? We've been asked if we can produce a report which has the total amount of all overdue fine wiped off in the last six months, but because all these are now set to W I cannot see a way of distinguishing the two different fine types. We are on 17.11. Cheers, Stephen -- Stephen Graham Library Technology Consultant Content and Collections Team Library and Computing Services University of Hertfordshire Tel: 01707 286111 Ext: 77751 Email: s.grah...@herts.ac.uk ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
