Re: [Koha] In House Use

[Carlos Lopez]

Hi Ma. Victoria

We have a slightly different query that lists author, title, branch, location, 
collection (ccode), call number, copy, barcode, issue/checkout count, renewal 
count and local use count - you should be able to adapt it to your needs fairly 
easily (add limits for date, location and branch). We started from the biblio 
table rather than from statistics, and added an inner join for items and a left 
join for statistics (you should be able to use an inner join or a left join (I 
can never remember which does what) for biblioitems, though on second thought 
I'm not sure why you might be calling up that table in the first place)

> SELECT b.author AS AUTHOR, b.title AS TITLE, i.homebranch, i.location, 
> i.ccode AS COLLECTION, i.itemcallnumber AS "CALL NUMBER", i.copynumber AS 
> COPY, i.barcode AS BARCODE, 
>   COUNT( IF( s.type = 'issue', s.itemnumber, NULL)) AS "ISSUE COUNT",
>   COUNT( IF( s.type = 'renew', s.itemnumber, NULL)) AS "RENEWAL COUNT",
>   COUNT( IF( s.type = 'localuse', s.itemnumber, NULL)) AS "LOCAL USE 
> COUNT"
> FROM
>   biblio b
> INNER JOIN items i USING (biblionumber)
> LEFT JOIN statistics s USING (itemnumber)
> WHERE s.type IN ('issue', 'renew', 'localuse')
> GROUP BY itemnumber
> ORDER BY i.homebranch, i.location, i.itemcallnumber, i.copynumber

Good luck!

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 Ma. Victoria H. 
Silva-Manuel
Sent: Wednesday, March 29, 2023 2:39 PM
To: koha 
Subject: [Koha] In House Use

CAUTION: This email originated from outside of the organization. Do not click 
links or open attachments unless you recognize the sender and know the content 
is safe.


I am using the SQL report below to generate in house use:

> SELECT count(statistics.type), items.itemcallnumber
> FROM borrowers LEFT JOIN statistics on
> (statistics.borrowernumber=borrowers.borrowernumber) LEFT JOIN items on
> (items.itemnumber = statistics.itemnumber) LEFT JOIN biblioitems on
> (biblioitems.biblioitemnumber = items.biblioitemnumber)
> WHERE statistics.type='localuse' AND date(statistics.datetime) BETWEEN
> <> AND <>   AND
> statistics.branch=<> AND items.location =
> <>
> GROUP BY items.itemcallnumber
> ORDER BY items.itemcallnumber asc


The original SQL only shows the count, I added the call number and filtered
it by library and location successfully but I cannot add the title. I am
always getting an error.
What should I do?
--
Ma. Victoria H. Silva-Manuel
Registered Librarian, 3892
___

Koha mailing list  
https://urldefense.com/v3/__http://koha-community.org__;!!DVrgiXjqvl2yLjg!awLbOIv_hmNvH3eTVS3SOke5WYZEeKLdkF8ZRi7sVzaK0BaPSowsNR68OmmhmlrxePHK4SXrvEyHXgoeatAZH4ry$
Koha@lists.katipo.co.nz
Unsubscribe: 
https://urldefense.com/v3/__https://lists.katipo.co.nz/mailman/listinfo/koha__;!!DVrgiXjqvl2yLjg!awLbOIv_hmNvH3eTVS3SOke5WYZEeKLdkF8ZRi7sVzaK0BaPSowsNR68OmmhmlrxePHK4SXrvEyHXgoeatnYQZk8$
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

[Koha] Upgrade KOHA 20.05 to KOHA 21.11

[zen zenitram]

good day!

I encountered this

ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Cannot change
column 'borrowernumber': used in a foreign key constraint 'issues_ibfk_1'
at /usr/share/koha/lib/C4/Installer.pm line 739


Thank you!
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

[Koha] In House Use

[Ma. Victoria H. Silva-Manuel]

I am using the SQL report below to generate in house use:

> SELECT count(statistics.type), items.itemcallnumber
> FROM borrowers LEFT JOIN statistics on
> (statistics.borrowernumber=borrowers.borrowernumber) LEFT JOIN items on
> (items.itemnumber = statistics.itemnumber) LEFT JOIN biblioitems on
> (biblioitems.biblioitemnumber = items.biblioitemnumber)
> WHERE statistics.type='localuse' AND date(statistics.datetime) BETWEEN
> <> AND <>   AND
> statistics.branch=<> AND items.location =
> <>
> GROUP BY items.itemcallnumber
> ORDER BY items.itemcallnumber asc


The original SQL only shows the count, I added the call number and filtered
it by library and location successfully but I cannot add the title. I am
always getting an error.
What should I do?
-- 
Ma. Victoria H. Silva-Manuel
Registered Librarian, 3892
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Add time lapse between notices when sending emails.

[Ms. Naveen Ali]

Thank you. Will do so..

With best regards, 

Naveen Ali 

ITM-JE (EAKL) 
Inst Representative for 
HEC Digital Library Resources. 
NEDUET, Karachi. 


- Original Message -
From: "Katrin Fischer" 
To: koha@lists.katipo.co.nz
Sent: Saturday, 25 March, 2023 1:45:19 AM
Subject: Re: [Koha] Add time lapse between notices when sending emails.

Hi Naveen,

I am not aware of any configuration setting to do this. Maybe you want
to file a bug on bugzilla (https://bugs.koha-community.org/) suggesting
a delay option?

Katrin

On 21.03.23 11:39, Ms. Naveen Ali wrote:
> Dear All,
>
> When sending advance or overdue notices, the emails are not sent / failed. 
> This happens because the a number a emails are sent per second and univerity 
> mail server has some restrictions.
> I'd like to add a time lapse of a couple of seconds between emails.
> Is it possible to do this?
>
> With best regards,
>
> Naveen Ali
>
> ITM-JE (EAKL)
> Inst Representative for
> HEC Digital Library Resources.
> NEDUET, Karachi.
>
>
> ___
>
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Click Tracker

[Ma. Victoria H. Silva-Manuel]

Thanks Mr. Andrew for your input.
Below is the final SQL report that works:

SELECT count(*) AS 'No. of Times Clicked', biblio.title AS 'Title', URL
> FROM linktracker
> JOIN biblio USING(biblionumber)
> JOIN items USING (biblionumber)
> WHERE date(timeclicked) BETWEEN <> AND
> <> AND items.itype=<>
> GROUP by url


On Tue, Mar 28, 2023 at 9:28 PM Andrew Fuerste-Henry 
wrote:

> Hello!
>
> The notation is table.column, so itype is a column in the items table. You
> can see the database schema here: https://schema.koha-community.org/
>
> So you'd want to join in the items table to get your query to limit by
> itemtype:
>
> SELECT count(DISTINCT biblionumber) AS 'No. of Times Clicked',
> biblio.title AS 'Title', URL
> FROM linktracker
> JOIN biblio USING(biblionumber)
> JOIN items USING (biblionumber)
> WHERE date(timeclicked) BETWEEN <> AND
> <> AND items.itype=<>
>
> Hope that helps!
> Andrew
> --
> *From:* Koha  on behalf of Ma. Victoria
> H. Silva-Manuel 
> *Sent:* Monday, March 27, 2023 7:36 PM
> *To:* Alvaro Cornejo 
> *Cc:* koha 
> *Subject:* Re: [Koha] Click Tracker
>
> Thank you for responding, unfortunately it still didn't work.
>
> On Mon, Mar 27, 2023, 9:32 PM Alvaro Cornejo 
> wrote:
>
> > Hi Victoria
> >
> >
> > I don't recall koha's tables content, but in mysql the dot delimit column
> > and table. Therefore, items.itype means column "items" from table "itype"
> > and in your query you are not calling table itype.
> >
> > So you can add a join with itype table or, if column "items" exist, in
> > linktracker or biblio tables, you can use items.linktracker
> > =<> or items.biblio=<>
> >
> > Regards,
> >
> > Alvaro
> >
> >
> >
> >
> >
> ||
> >  Stay safe / Cuídate/  Reste sécurisé
> > *7* Switch off as you go / Apaga lo que no usas /  Débranchez au fur et à
> > mesure.
> >  *q *Recycle always / Recicla siempre / Recyclez toujours
> >  P Print only if absolutely necessary / Imprime solo si es necesario /
> > Imprimez seulement si nécessaire
> >
> >
> > Le lun. 27 mars 2023 à 00:31, Ma. Victoria H. Silva-Manuel <
> > mavicsi...@gmail.com> a écrit :
> >
> >> I am using this SQL to generate Click Tracker report:
> >>
> >> SELECT count(*) AS 'No. of Times Clicked', biblio.title AS 'Title', URL
> >> > FROM linktracker
> >> >
> >> JOIN biblio USING(biblionumber)
> >>
> >> WHERE date(timeclicked) BETWEEN <> AND
> >> > <>
> >>
> >> GROUP BY url
> >>
> >>
> >> I want to add a drop down list for item type. I tried
> >> adding items.itype=<> in WHERE part, but I'm getting
> the
> >> following error when I run the report:
> >>
> >> The following error was encountered:
> >> > The database returned the following error:
> >> > Unknown column 'items.itype' in 'where clause'
> >> > Please check the log for further details.
> >>
> >>
> >> What should I do?
> >>
> >>
> >> --
> >> Ma. Victoria H. Silva-Manuel
> >> Registered Librarian, 3892
> >> ___
> >>
> >> Koha mailing list  http://koha-community.org
> >> Koha@lists.katipo.co.nz
> >> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
> >>
> >
> ___
>
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>


-- 
Ma. Victoria H. Silva-Manuel
Registered Librarian, 3892
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report generation

[Elaine Bradtke]

Warning: what I don't know about SQL queries would fill a book, but I've
adapted one that works for us.

SELECT biblionumber, ExtractValue(`metadata`,'//datafield[@tag="521"]/*')
FROM `biblio_metadata` WHERE
ExtractValue(`metadata`,'count(//datafield[@tag="521"])>0')

If that works you could also do a similar thing for 526 by changing the tag
numbers
Elaine
VWML 



On Tue, Mar 28, 2023 at 12:54 AM zen zenitram  wrote:

> We need cataloging report thats shows Tag 521 or Tag 526.
>
> On Tue, Mar 28, 2023 at 12:54 PM Ketan Kulkarni 
> wrote:
>
> > What kind of report are you trying to generate ?
> > You can get many here
> > https://wiki.koha-community.org/wiki/SQL_Reports_Library
> >
> > Ketan
> >
> > On Tue, Mar 28, 2023 at 9:47 AM zen zenitram 
> > wrote:
> >
> >> Hello
> >>
> >> Any one know how to create report in sql, of your collection that has
> Tag
> >> 521 and 526 in your output.
> >>
> >> Thank you!
> >> ___
> >>
> >> Koha mailing list  http://koha-community.org
> >> Koha@lists.katipo.co.nz
> >> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
> >>
> >
> ___
>
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] [EXTERNAL] Re: Customize OPAC search results

[King, Fred]

Welcome to Koha, Daniele!

You may have/want to add the 521 tag to the index, too. If you're using 
Elasticsearch, it's fairly easy.

I recently redesigned our MedStar Authors catalog, part of which included 
modifying the xsl templates. You can see and download my files at 
medstarauthors.org/cgi-bin/koha/opac-page.pl?page_id=9.

Fred King, MSLS, AHIP
Medical Librarian, MedStar Washington Hospital Center
fred.k...@medstar.net
202-877-6670
ORCID -0001-5266-0279
MedStar Authors Catalog: http://medstarauthors.org

You know it's cold outside when you go outside and it's cold.
--NPS Twitter

-Original Message-
From: Koha  On Behalf Of Coehoorn, Joel
Sent: Tuesday, March 28, 2023 11:32 AM
To: Daniele Piccoli 
Cc: Koha : access 
Subject: [EXTERNAL] Re: [Koha] Customize OPAC search results



**ATTENTION: This email originated from outside the MedStar Health network.
 ** DO NOT CLICK links or attachments unless you recognize the sender and know 
the content is safe.

 

Grade level data can go in the MARC 521 $0  or 521 $2 tags. From there, I 
expect you'd need to modify the XSLT template to show the data on the page.

*Joel Coehoorn*
Director of Information Technology
*York University*
Office: 402-363-5603 | jcoeho...@york.edu | york.edu



On Tue, Mar 28, 2023 at 5:43 AM Daniele Piccoli 
wrote:

> Hi all, my first message here.
>
> We have been starting tu use Koha in our school for organize our 
> books, it works very well.
>
> I'd like to know if it is possible to customize the search results and 
> put a circle with a color side to each book, based on class that it 
> suited for
>
> For instance, first class --> BLU, second class --> RED, and so on
>
> Do you have any hint?
>
> Thanks
>
> Daniele
> ___
>
> Koha mailing list  http://koha-community.org Koha@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
___

Koha mailing list  http://koha-community.org Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

--
MedStar Health is a not-for-profit, integrated healthcare delivery system, the 
largest in Maryland and the Washington, D.C., region. Nationally recognized for 
clinical quality in heart, orthopedics, cancer and GI.

IMPORTANT: This e-mail (including any attachments) may contain information that 
is private, confidential, or protected by attorney-client or other privilege. 
If you received this e-mail in error, please delete it from your system without 
copying it and notify sender by reply e-mail, so that our records can be 
corrected... Thank you.

Help conserve valuable resources - only print this email if necessary.


___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Customize OPAC search results

[Coehoorn, Joel]

Grade level data can go in the MARC 521 $0  or 521 $2 tags. From there, I
expect you'd need to modify the XSLT template to show the data on the page.

*Joel Coehoorn*
Director of Information Technology
*York University*
Office: 402-363-5603 | jcoeho...@york.edu | york.edu



On Tue, Mar 28, 2023 at 5:43 AM Daniele Piccoli 
wrote:

> Hi all, my first message here.
>
> We have been starting tu use Koha in our school for organize our books,
> it works very well.
>
> I'd like to know if it is possible to customize the search results and
> put a circle with a color side to each book, based on class that it
> suited for
>
> For instance, first class --> BLU, second class --> RED, and so on
>
> Do you have any hint?
>
> Thanks
>
> Daniele
> ___
>
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report generation

[Barbara Johnson]

Hi Zen,

This should provide you with a list of bibs with those two MARC tags:

SELECT i.dateaccessioned, b.biblionumber, i.permanent_location AS 'location', 
i.itemcallnumber AS 'call#', b.title, 
ExtractValue(metadata,'//datafield[@tag="521"]/subfield[@code>="a"]') AS 
'audience', 
ExtractValue(metadata,'//datafield[@tag="526"]/subfield[@code>="a"]') AS 'note'
FROM biblio b
LEFT JOIN biblio_metadata m using (biblionumber)
LEFT JOIN items i using (biblionumber)
GROUP BY b.biblionumber

Hope this helps,
Barbara

--
Barbara Johnson
Technical Services Manager
Bedford Public Library
2424 Forest Ridge Drive, Bedford, TX 76021-4667
Office: 817-952-2360 | Fax: 817-952-2396 | 
barbara.john...@bedfordtx.gov


From: Koha  on behalf of zen zenitram 

Sent: Monday, March 27, 2023 11:17 PM
To: koha@lists.katipo.co.nz 
Subject: [Koha] Report generation

EXTERNAL SENDER


Hello

Any one know how to create report in sql, of your collection that has Tag
521 and 526 in your output.

Thank you!
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
CONFIDENTIALITY NOTICE: This City of Bedford (CoB) email transmission is 
intended only for the use of the individual to whom it is addressed and may 
contain information that is confidential, privileged, and exempt from 
disclosure. Any use, copying, retention or disclosure by any person other than 
the intended recipient or the intended recipient's designees is strictly 
prohibited. If you have received this email in error, please notify the sender 
immediately by return email and destroy all electronic and paper copies of the 
original message and any attachments immediately.
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Click Tracker

[Andrew Fuerste-Henry]

Hello!

The notation is table.column, so itype is a column in the items table. You can 
see the database schema here: https://schema.koha-community.org/

So you'd want to join in the items table to get your query to limit by itemtype:

SELECT count(DISTINCT biblionumber) AS 'No. of Times Clicked', biblio.title AS 
'Title', URL
FROM linktracker
JOIN biblio USING(biblionumber)
JOIN items USING (biblionumber)
WHERE date(timeclicked) BETWEEN <> AND <> AND items.itype=<>

Hope that helps!
Andrew

From: Koha  on behalf of Ma. Victoria H. 
Silva-Manuel 
Sent: Monday, March 27, 2023 7:36 PM
To: Alvaro Cornejo 
Cc: koha 
Subject: Re: [Koha] Click Tracker

Thank you for responding, unfortunately it still didn't work.

On Mon, Mar 27, 2023, 9:32 PM Alvaro Cornejo 
wrote:

> Hi Victoria
>
>
> I don't recall koha's tables content, but in mysql the dot delimit column
> and table. Therefore, items.itype means column "items" from table "itype"
> and in your query you are not calling table itype.
>
> So you can add a join with itype table or, if column "items" exist, in
> linktracker or biblio tables, you can use items.linktracker
> =<> or items.biblio=<>
>
> Regards,
>
> Alvaro
>
>
>
>
> ||
>  Stay safe / Cuídate/  Reste sécurisé
> *7* Switch off as you go / Apaga lo que no usas /  Débranchez au fur et à
> mesure.
>  *q *Recycle always / Recicla siempre / Recyclez toujours
>  P Print only if absolutely necessary / Imprime solo si es necesario /
> Imprimez seulement si nécessaire
>
>
> Le lun. 27 mars 2023 à 00:31, Ma. Victoria H. Silva-Manuel <
> mavicsi...@gmail.com> a écrit :
>
>> I am using this SQL to generate Click Tracker report:
>>
>> SELECT count(*) AS 'No. of Times Clicked', biblio.title AS 'Title', URL
>> > FROM linktracker
>> >
>> JOIN biblio USING(biblionumber)
>>
>> WHERE date(timeclicked) BETWEEN <> AND
>> > <>
>>
>> GROUP BY url
>>
>>
>> I want to add a drop down list for item type. I tried
>> adding items.itype=<> in WHERE part, but I'm getting the
>> following error when I run the report:
>>
>> The following error was encountered:
>> > The database returned the following error:
>> > Unknown column 'items.itype' in 'where clause'
>> > Please check the log for further details.
>>
>>
>> What should I do?
>>
>>
>> --
>> Ma. Victoria H. Silva-Manuel
>> Registered Librarian, 3892
>> ___
>>
>> Koha mailing list  http://koha-community.org
>> Koha@lists.katipo.co.nz
>> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

[Koha] Customize OPAC search results

[Daniele Piccoli]

Hi all, my first message here.

We have been starting tu use Koha in our school for organize our books, 
it works very well.


I'd like to know if it is possible to customize the search results and 
put a circle with a color side to each book, based on class that it 
suited for


For instance, first class --> BLU, second class --> RED, and so on

Do you have any hint?

Thanks

Daniele
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report generation

[zen zenitram]

We need cataloging report thats shows Tag 521 or Tag 526.

On Tue, Mar 28, 2023 at 12:54 PM Ketan Kulkarni  wrote:

> What kind of report are you trying to generate ?
> You can get many here
> https://wiki.koha-community.org/wiki/SQL_Reports_Library
>
> Ketan
>
> On Tue, Mar 28, 2023 at 9:47 AM zen zenitram 
> wrote:
>
>> Hello
>>
>> Any one know how to create report in sql, of your collection that has Tag
>> 521 and 526 in your output.
>>
>> Thank you!
>> ___
>>
>> Koha mailing list  http://koha-community.org
>> Koha@lists.katipo.co.nz
>> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha