[Koha] Recovering circulation history

[Bernardo Gonzalez Kriegel]

Holly,
this is my idea, perhaps others have better ones.

I suppose that the items (deleted and recovered), are not issued.
And the history I will try to reconstruct is for the old_issues table.

The following SQL code needs a barcode (XXXX highlighted), then search
issues date and the first return date, and it's output could be used to
fill the old_issues table.

SELECT a.borrowernumber AS borrowernumber, a.itemnumber AS itemnumber, NULL
AS date_due, a.branch AS branchcode, NULL AS issuingbranch,
date(b.datetime) AS returndate, NULL AS lastrenewdate, NULL AS `return`,
NULL AS renewals, b.datetime AS timestamp, date(a.datetime) AS issuedate
FROM statistics AS a, statistics AS b WHERE b.datetime = (SELECT datetime
FROM statistics WHERE datetime > a.datetime AND type = 'return' AND
borrowernumber = a.borrowernumber AND itemnumber = a.itemnumber LIMIT 1)
AND a.itemnumber = (SELECT itemnumber FROM items WHERE barcode = 'XXXXX')
AND a.type = 'issue' ORDER BY issuedate;

You could check the output with a barcode with known history to verify.
There are some null values, but can't be recovered from statistics, and
lose track of renewals.

Next, if you are brave, backup your old_issues table and do the
following (*only
once*) for each deleted barcode:

INSERT INTO old_issues SELECT a.borrowernumber AS borrowernumber,
a.itemnumber AS itemnumber, NULL AS date_due, a.branch AS branchcode, NULL
AS issuingbranch, DATE(b.datetime) AS returndate, NULL AS lastrenewdate,
NULL AS `return`, NULL AS renewals, b.datetime AS timestamp,
DATE(a.datetime) AS issuedate FROM statistics AS a, statistics AS b WHERE
b.datetime = (SELECT datetime FROM statistics WHERE datetime > a.datetime
AND type = 'return' AND borrowernumber = a.borrowernumber AND itemnumber =
a.itemnumber LIMIT 1) AND a.itemnumber = (SELECT itemnumber FROM items
WHERE barcode = 'XXXXX') AND a.type = 'issue' ORDER BY issuedate;

This must be done working directly with the database, and could take a
while (statistics is a big table)
Hope that someone could get a better idea.

Good luck!

-- 
Bernardo Gonzalez Kriegel
bgkriegel at gmail.com



On Fri, May 4, 2012 at 12:51 PM, Holly Hancock <hollyh at bridgton.lib.me.us>wrote:

> We've had a couple instances lately where item records have been
> accidentally deleted.  Once the item is restored all circulation history is
> gone.  I can find the issue, renew & return data under Statistics in MySQL.
>  Is there a way to restore this data to Issues so that it again appears in
> the Circulation History?
>
> We are currently running 3.02.06 on Debian Lenny.
>
>
> -Holly
>
>
>
>
>
> Holly Hancock
> Library Director
> Bridgton Public Library
> 207-647-2472
> hollyh at bridgton.lib.me.us
>
>
>
>
> ______________________________**_________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> http://lists.katipo.co.nz/**mailman/listinfo/koha<http://lists.katipo.co.nz/mailman/listinfo/koha>
>