Re: [Koha] Report help needed - lost items
Mark, that still didn't do the trick, but thanks! Caroline, this is much closer to what I need. But still not quite right. It's messy because I've altered a report that I found in the report library rather inelegantly. What I really need is a list of titles and biblio numbers of biblios where the last item is lost, or missing, or withdrawn (I can change that part of the query myself). I have to limit it by format because we have articles and components in the catalogue that don't have item records attached. What I'm getting now lis a list of biblios with items that are missing but it includes biblios that still have one or more items that are on the shelf (but at least one item that is missing). Or in other words, I'm looking for biblios where the all the items are missing/lost/withdrawn. Many thanks for your help! Elaine Bradtke VWML English Folk Dance and Song Society | http://www.efdss.org Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and Song Society in London, England. If you wish to phone me personally, send an e-mail first. I work off site) -- Registered Company No. 297142 Charity Registered in England and Wales No. 305999 On Fri, Aug 23, 2019 at 12:30 PM Caroline Cyr-La-Rose < caroline.cyr-la-r...@inlibro.com> wrote: > Hello Elaine, > > from what I understand, you want bibliographic information on lost items? > > I'd need to know exactly the purpose of the report to help you more as > there are things in there I don't understand (like why select barcode, > when afterwards you group by biblionumber, or why the title is there > twice). > > First, instead of selecting from the biblio table, I would go the other > way and select from the items table. That way, you are sure of getting > only things that have an item. > > If you want to limit by itemtype, you can add WHERE itype = < type|itemtypes>> in your query to have the ability to choose your > itemtype upon executing your report. > > I cleaned your query up a little bit, but like I mentioned before, as I > don't know the purpose, it might not be what you need. > > SELECT > CONCAT( > ' biblionumber, > '\">', > title, > '' > ) AS Title, > biblionumber, > barcode, > ccode, > onloan AS 'Checkout date' > FROM > items > JOIN biblio USING (biblionumber) > WHERE itemlost != 0 >AND itype = <> > > Let us know if this works. > Caroline > > > On 19-08-23 15 h 00, Mark Alexander wrote: > > Excerpts from Elaine Bradtke's message of 2019-08-23 11:32:44 -0700: > >> I've got the following adapted from one in the reports library. But I > need > >> to limit it by item type, or not return everything that doesn't have an > >> item record attached. We've got lots of components in our catalogue that > >> don't have items and it's returning all of them: > >> > >> SELECT > >> CONCAT( > >> ' ', > >> bib.biblionumber, > >> '\">', > >> bib.title, > >> '' > >> ) AS Title, > >> bib.title, > >> bib.biblionumber, > >> lostitems.barcode, > >> lostitems.ccode, > >> lostitems.onloan AS 'Checkout date' > >> FROM > >> biblio bib > >> LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND > >> items.itemlost = 0) > >> LEFT JOIN items AS lostitems ON (bib.biblionumber = > >> lostitems.biblionumber AND lostitems.itemlost != 0) > >> GROUP BY bib.biblionumber > >> HAVING count(items.itemnumber) = 0 > > While I'm not an SQL expert by any means, I think I was able to get > > this to work on my catalog by removing the HAVING clause and > > putting "WHERE items.itemnumber IS NULL" before the GROUP clause: > > > > SELECT > > CONCAT( > > ' ', > > bib.biblionumber, > > '\">', > > bib.title, > > '' > > ) AS Title, > > bib.title, > > bib.biblionumber, > > lostitems.barcode, > > lostitems.ccode, > > lostitems.onloan AS 'Checkout date' > > FROM > > biblio bib > > LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND > > items.itemlost = 0) > > LEFT JOIN items AS lostitems ON (bib.biblionumber = > > lostitems.biblionumber AND lostitems.itemlost != 0) > > WHERE items.itemnumber IS NULL > > GROUP BY bib.biblionumber > > ___ > > Koha mailing list http://koha-community.org > > Koha@lists.katipo.co.nz > > https://lists.katipo.co.nz/mailman/listinfo/koha > > -- > Caroline Cyr La Rose, M.S.I. > Bibliothécaire | Responsable de produit > > Tél. : 1-833-465-4276, poste 221 > caroline.cyr-la-r...@inlibro.com
[Koha] Koha calculating but not showing overdue fines
Hi, My system is now calculating overdue fines after setting the homelibrary field but its only after checking in the overdue item that you can see the amount of fine. On the patrons account, its only showing the user as having overdue items but not showing what amount of fine has accrued. What can I do for the amount accrued to be shown even while the item is still checked out? Someone please help Thank you Best Regards On Wed, 21 Aug 2019 10:59 muirunyeri Hi, > > While Koha is now calculating fines, its only possible to see the fine > after item checkin. Its only showing a user as having overdue items but no > amount of fine accrued before checkin. What can be done to show on the > screen the amount accrued even while the item is still checked out? > > Best Regards > > > > Sent from my Samsung Galaxy smartphone. > Original message > From: muirunyeri > Date: 16/08/2019 14:12 (GMT+03:00) > To: Alvaro Cornejo , koha@lists.katipo.co.nz > Subject: Re: [Koha] Koha Not calculating and charging overdue fines > > Hi, > > Thanks Alvaro. The code worked and now overdue is calculating > > Best regards > > > > Sent from my Samsung Galaxy smartphone. > Original message > From: Alvaro Cornejo > Date: 15/08/2019 16:42 (GMT+03:00) > To: muirunyeri > Cc: Carlos Lopez , koha@lists.katipo.co.nz > Subject: Re: [Koha] Koha Not calculating and charging overdue fines > > Hi Muirunyeri > > You can access mysql and run a command like: > > > UPDATE `items` SET `homebranch` = `YourHomeBranchID' [WHERE homebranch is > NULL]; > > As you will be editing your database directly, you should backup your > system before. > > Regards, > > Alvaro > > > |-| > Envíe y Reciba Datos y mensajes de Texto (SMS) hacia y desde cualquier > celular y Nextel > en el Perú, México y en mas de 180 paises. Use aplicaciones 2 vias via SMS > y GPRS online > Visitenos en www.perusms.com > > > Le jeu. 15 août 2019 à 01:54, muirunyeri a écrit : > >> Hi Carlos, >> I ran the report which returns a single column with item numbers. It >> indeed confirms all items are without home branch. However, no batch >> editing option. >> Any assistance to solve this is highly appreciated. >> Thanks >> >> Sent from my Samsung Galaxy smartphone. Original message >> From: Carlos Lopez Date: 15/08/2019 >> 02:02 (GMT+03:00) To: muirunyeri , >> koha@lists.katipo.co.nz Subject: RE: [Koha] Fwd:Re: Koha Not >> calculating and charging overdue fines >> Hi Muirunyeri >> If the homebranch field is empty, you could run a report from this query: >> SELECT itemnumber >> FROM items >> WHERE homebranch is NULL >> >> You should even get a little "Batch modify" button at the top of your >> results. >> >> With kind regards from the Dalton McCaughey Library Team >> >> Carlos Lopez >> >> Dalton McCaughey Library | 29 College Crescent, Parkville, VICTORIA 3052 >> Ph: 03 9340 ext.1 | libr...@dml.vic.edu.au | >> library.dmlibrary.org.au >> >> -Original Message- >> From: Koha On Behalf Of muirunyeri >> Sent: Thursday, 15 August 2019 5:56 AM >> To: koha@lists.katipo.co.nz >> Subject: [Koha] Fwd:Re: Koha Not calculating and charging overdue fines >> >> >> >> >> >> Sent from my Samsung Galaxy smartphone. Original message >> From: muirunyeri Date: 14/08/2019 22:54 >> (GMT+03:00) To: katrin.fischer...@web.de Subject: Re: Re: [Koha] Koha >> Not calculating and charging overdue fines >> Hi, >> >> I have around 44,000 items that were imported from excel. The homebranch >> field is blank and this might be the cause if the non-calculating overdue >> fines. How can i edit items to fill this field (batch)? The homebranch >> field is not available on the items form. If its hidden, how can I unhide >> it? >> >> You could also give me an SQL code if it'll get the > > >> Sent from my Samsung Galaxy smartphone. >> >> >> Original message >> From: Katrin Fischer >> >> Date: 06/08/2019 19:14 (GMT+03:00) >> To: koha@lists.katipo.co.nz >> Subject: Re: [Koha] Koha Not calculating and charging >> overdue fines >> >> >> >> >> Hi all, >> >> >> >> I think what Caroline described would work well for a dev (git) >> or >> >> standard (tarball) installation, while things with the packages >> work a >> >> little differently like Michael describes. So it might depend on >> your >> >> installation type on what you should do. >> >> >> >> For package installations the fines.pl should be scheduled by >> default to >> >> run daily. >> >> >> >> Have you checked everything else that might prevent fines from >> calculating? >> >> >> >> - finesmode systempreference? >> >> - Holidays in the calendar? >
