IEM Trust chooses L2C2 Technologies as their Koha support partner.

To provide pan-India support to its campuses as a single consortium.

We are delighted to extend a very warm welcome to our newest client-partner – the Institute of Engineering & Management (IEM) Trust. We congratulate them on selecting our cloud hosted Koha ILS platform for catering to all their campuses across India. The IEM Trust is the first multi-campus consortium client-partner on L2C2 Technologies’s Koha support network. We are truly proud to be partnered with the institution that setup the first private engineering college in West Bengal and consistently remains a leading top-notch educational institution, attracting some of the best students.

At present L2C2 Technologies will cater to the hosted support for libraries at the following campuses:

1) IEM Gurukul Campus, Kolkata, West Bengal.

2) IEM Ashram Campus, Kolkata, West Bengal.

3) IEM Management House Campus, Kolkata, West Bengal.

4) UEM Kolkata Campus, Kolkata, West Bengal.

5) UEM Jaipur Campus, Jaipur, Rajasthan.

About IEM-UEM Group

The IEM-UEM Group, led by the IEM Trust, has spearheaded engineering education in the state of West Bengal with the setting up of the Institute of Engineering & Management in 1989 as the first self-financed engineering college in the state. Starting from a single campus, the IEM Trust over the years have expanded across the country with multiple campuses. It has setup University of Engineering & Management which is a deemed University under UGC rules, with campuses currently in Rajasthan and West Bengal.

IEM is a NAAC A-rated educational institution and has fared very well in the NIRF (National Institutional Ranking Framework) rankings conducted by Ministry of Human Resource Development (MHRD), Govt of India. In 2016, it was ranked as the 3rd best engineering college from West Bengal, coming after IIT Kharagpur and NIT Durgapur in the NIRF rankings.

At present it operates from 6 campuses around the country.

.

When the Laws of Library Science comes and knocks on your door

When an online Koha Library OPAC is the sole answer to finding an out-of-print book.

This post is somewhat of a personal nature. My love for libraries came from my original passion for Free & Open Source software. Being able to solve people’s problems using open source technology, to be able to preserve our local languages, our history, our culture and then being able to share and disseminate meant a lot to me in my youth. Now on the wrong side of 40s, it still continues to drive me.

Just a while back Parama di (Smt Parama Sarkhel) the librarian at Ramakrishna Sarada Mission Vivekananda Vidyabhavan college shared the following missive over FB Messenger to me:

মাননীয় গ্রন্থাগারীক সমীপেষু,
রামকৃষ্ণ সারদামিশন বিবেকানন্দ বিদ্যাভবন লাইব্রেরি

মাননীয়,

আমি শ্রী বিনোদ ঘোষাল, পেশায় একজন সাহিত্যিক, গত তিন বছর ধরে আমি কাজী নজরুল ইসলামের সমগ্র জীবনকে উপজিব্য করে একতি দীর্ঘ উপন্যাস রচনা করছি। যার প্রথম পর্বটি দীর্ঘ একবছর সংবাদ প্রতিদিনের রোববার পত্রিকায় ধারাবাহিকরূপে প্রকাশ পাবার পর মিত্র ও ঘোষ প্রকাশন থেকে ‘কে বাজায় বাঁশি’ নামে বই আকারে প্রকাশিত হয়েছে।

বর্তমানে আমি উক্ত গ্রন্থের দ্বিতীয় খণ্ড রচনা করছি। রিসার্চের কাজে আমার নরেন্দ্রনারায়ণ চক্রবর্তী রচিত ‘নজরুলের সঙ্গে কারাগারে’ গ্রন্থটি বিশেষ প্রয়োজন। বইটি কলেজস্ট্রিটে বা অন্যত্র খোঁজ করে কোথাও পাইনি।

ইন্টারনেটের সূত্রে দেখলাম আপনাদের লাইব্রেরিতে বইটি রয়েছে। আপনার কাছে অনুরোধ আমাকে অনুগ্রহ করে উক্ত বইটি যদি ফটোকপি করার অনুমতি দেন বা নির্দিষ্ট মূল্যের বিনিময়ে বইটির একটি ফটোকপি দেন তাহলে আমি উপন্যাসটি আরও তথ্যসমৃদ্ধ করে তুলতে পারি।

আশা করি অনুরোধটি বিবেচনা করবেন।

আপনার সাহায্যপ্রার্থী,
বিনোদ ঘোষাল

Briefly, this is a request from Sri Binod Ghosal, a young, multi award winning Bengali novelist and short story writer. He needs access to a book – “Nazrul-r songay karagarey” (lit. ‘In the prison with Nazrul’) by Sri Narendra Narayan Chackroborty, as research material for the next volume of his biographical novel “Ke Baajaye BaNshi” on Kazi Nazrul Islam – a Bengali poet, writer, musician, anti-colonial revolutionary and the national poet of Bangladesh. The book is presently out of print, and Sri Ghosal had no luck in locating a copy of it anywhere in College Street’s Boi para or elsewhere for that matter.

Luckily Google’s search engine came to his assistance. Parama di’s college library OPAC, being hosted online is indexed by Google and her library happens to have a copy of it. As a matter of fact it is probably the sole reference to a copy available at a library and that too within the distance of a local train ride from Sri Ghosal’s residence.

The five laws of library science by S.R. Ranganathan are often learnt rote. As practitioners, we often merely amble in the general direction of the Laws. But precisely in moments like this, when everything falls into place, the laws of Library Science manifest themselves in sheer, brilliant clarity.

As a co-founder of L2C2 Technologies, it is in these few and far between moments, that I once again find the reason and strength to carry on.

Antara Psychiatric Hospital & Rehabilitation Centre Library partners with L2C2 Technologies

Antara Psychiatric Hospital & Rehabilitation Centre at Kolkata chooses to automate their library using L2C2 Technologies’s cloud hosted Koha ILS platform

We are pleased to extend a warm welcome to the newest client-partner in Eastern India – Antara Psychiatric Hospital & Rehabilitation Centre, Kolkata. Incidentally Antara happens to be our forth hospital library and the first in psychiatric specialty. The Antara library has gone live on Koha ILS 18.11 series on L2C2 Technologies‘s cloud hosting platform.

About Antara

Established in 1971 and located at Antaragram, Baruipur in the outskirts of Kolkata, Antara today is the largest non-profit, non-governmental organisation in India to extend holistic mental healthcare facilities to people from all walks of life. The foundation stone for the present campus was laid in 1980 by Mother Teresa.

All payments in a date range with quick transaction view

A SQL report for Koha ILS

Yesterday was the last day of the year, when Sri Ashish Kumar Barik the young and enthusiastic librarian at Midnapore City College requested for a new custom SQL report that would allow him:

To see every payment made by every user in a given date range, sorted by MM/YY. It should show every type of payment made e.g. overdue fines, lost book fees, library card replacement. Basically everything that was charged to and paid by a patron

In this blog post we share it with all, so that others looking for something similar can have a ready template for their use. It’s our way to saying thank you and Happy New Year 2019 to all our readers, clients and well-wishers.

Putting it together

The report uses three tables in the Koha database – (a) accountlines; (b) borrowers and (c) categories.

The report adds a quick transaction view link next to each and every payment. For library staff wanting to find out the details of a particular payment, with this report that information is just a single click away.

The report metadata
Report name List all payments made by patrons between two dates
Report group Accounts
Notes List all payments made by patrons between two dates, sorted by date and patron name.
The SQL statement
SELECT 
    CONCAT("<a href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=", T1.borrowernumber, "\" target=\"_blank\">View Transaction</a>") AS 'Click to view'
    , T2.cardnumber AS 'Card No.'
    , CONCAT(T2.firstname, " ", T2.surname) AS 'Name'
    , T3.description AS 'Category'
    , CONCAT(SUBSTRING(MONTHNAME(T1.timestamp), 1,3), " ", YEAR(T1.timestamp)) AS 'Billing Period'
    , DATE_FORMAT(DATE(T1.timestamp), "%d/%m/%Y") AS 'Txn Date'
    , CONCAT("₹", LPAD(REPLACE(ROUND(T1.amount, 2),"-", ""), 8, " ")) AS 'Paid' 
FROM 
    `accountlines` T1 
     LEFT JOIN borrowers T2 USING (borrowernumber) 
    LEFT JOIN categories T3 USING (categorycode) 
WHERE 
    T1.accounttype="PAY" 
    AND
   DATE(T1.timestamp) BETWEEN <<From date|date>> AND <<To date|date>> 
ORDER BY DATE(T1.timestamp), CONCAT(T2.firstname, " ", T2.surname)
See it in action

Here is a short 2 minute video of it for those who want to see it action.

Wishing everyone a Happy New Year 2019.

Extracting patron images from Koha DB

This is part of the series of blog posts on how to extract your data from a Koha system where you unable to access the DB directly, but where you have “superlibrarian” access. In this part, we talk about extracting the patron images stored inside the patronimage table in your Koha database.

Prequisites

  • You should be comfortable using the GNU/Linux command line
  • Familiarity with SQL syntax
  • The Koha DB schema reference for your version of Koha
  • A Linux system with cURL and LibreOffice installed
  • Superlibrarian userid and password of your Koha instance

Modus operandi

  1. Use the Koha Reports module to download the list of the borrowernumbers of patrons having their image in the db
  2. Remove the header line and save it as a text file, with one borrowernumber per line
  3. Use cURL to get the CGISESSID cookie using the authentication SVC API call
  4. Execute the BASH script, passing the authenticated session id and the file holding the list of borrowernumbers as command line parameters
  5. Use the Koha Reports module to generate the IDLINK.txt batch patron image uploader file

The background

Patron images in Koha are stored in the patronimage table of the database. The table has following schema:

Column Type Size Parents Comments
borrowernumber int 10 borrowers the borrowernumber of the patron this image is attached to (borrowers.borrowernumber)
mimetype varchar 15   the format of the image (png, jpg, etc)
imagefile mediumblob 16777215   the image

A blob is a “binary large object” which means it is not a text field. The contents won’t make any sense if we used the report module to SELECT imagefile FROM patronimage. This brings cURL into the picture.

The patron image is served on the OPAC / staff client using the patronimage.pl Perl script using an URL like this: http://<your_instance>/cgi-bin/koha/members/patronimage.pl?borrowernumber=<borrowernumber>.

But before we can run cURL on this URL, we must do one more thing. We need our BASH script to login using our superlibrarian userid and password using Koha authentication web service API call.

We need to do this for two reasons: (a) login into our Koha instance with superlibrarian access and (b) get hold of the CGISESSID cookie.

Putting it all together

  1. A simple SQL report as given below with fetch us the borrowernumber of patrons with their image stored in the DB.

    SELECT borrowernumber FROM patronimage

    We will download the result as Open Document spreadsheet.

    Next we copy and save the list of patron borrowernumbers into a text file (let’s call it list-of-pix.txt), with one borrowernumber per line.

  2. Lets put together our small BASH script. We shall call it get_pix.sh.
    #!/bin/bash
    
    mkdir patronimages
    
    while read LINE
        do  curl -b CGISESSID=$1  -o ./patronimages/$LINE.png http://<your_instance>/cgi-bin/koha/members/patronimage.pl?borrowernumber=$LINE
    done < $2

    NOTE: <your_instance> need to be changed with **your** actual server URL.
    Make the script executable by running chmod a+x get_pix.sh.

  3. From the command-line run the following command:
    $ curl -i http://<your_instance>/cgi-bin/koha/svc/authentication -d 'userid=<yourid>&password=<yourpass>'

    If we had put in the correct information, we shall see something like this:

    HTTP/1.1 200 OK
    Date: Thu, 20 Dec 2018 09:53:02 GMT
    Server: Apache/2.4.18 (Ubuntu)
    Set-Cookie: CGISESSID=4a732ae75d8991a994d7ad0df584f84c; path=/; HttpOnly
    Vary: Accept-Encoding
    Transfer-Encoding: chunked
    Content-Type: text/xml; charset=ISO-8859-1
    
    <?xml version='1.0' standalone='yes'?>
    <response>
      <status>ok</status>
    </response>

    This gives us the two things we need for the next step: (a) the CGISESSID and (b) the status as OK. OK signifies that we were successfully logged in.

  4. Now we run the get_pix.sh script
    ./get_pix.sh f03a894f1885e891e30f6d40e6e9838c ./list-of-pix.txt

    And we shall see the patron images getting downloaded.

  5. Once the patron images have all been downloaded to patronimages folder created by the get_pix.sh script, its time to generate the IDLINK.txt file, which we will place in the aforementioned patronimages folder. Again, we shall turn to Koha's Reports module with this small SQL script:
    SELECT CONCAT(borrowers.cardnumber,", ", patronimage.borrowernumber, ".png") AS 'IDLINK'
      FROM patronimage 
      LEFT JOIN borrowers ON patronimage.borrowernumber=borrowers.borrowernumber

    This will generate our listing for the IDLINK.txt. If you are not sure about what IDLINK.txt does, please read up from here.

  6. Download the report as Open Document Spreadsheet, remove the header line and copy the rest into a text file and save it as IDLINK.txt in the patronimages folder.
  7. Zip up the patronimages folder as mentioned here and we have the patron images ready for a batch upload.

Display the totals for fines & fees, payments made, the outstanding, written off and forgiven amounts between a specified date range

A nifty single line SQL report for accounting using Koha.

Earlier this week, Sri Kalipada Jana, librarian at our client-partner Basanti Devi College, Kolkata, filed a new custom SQL report request. He wanted a report that did the following:

To list the total fines accrued, total paid so far and the total outstanding fines / fee between 02 (two) given dates.

Now, the readers of this blog who are acquinted with ready-made, user contributed SQL report library on the Koha Community wiki will know that there are a quite a few reports available that generate reports similar to Kalipada’s requirement. However these ready-made SQL reports usually generate this data at one data point at a time e.g. one report will provide total fines, another will provide total outstanding fines and so on. Further (and perhaps left as a exercise to the reader) these ready reports usually do not take into account “reversed charges“, “partial payments” entered as credits etc.

The report

The report presents a consolidated, single line of the total fines & Fees (F + FU + N + A + M + L), amount outstanding (F + FU + N + A + M + L), paid (P + C), written off (W) and forgiven (FOR) between a date range. If you wish to learn more about the mnemonics used within the brackets, you should look at the “Hard Coded Values” entry on the Koha Wiki.

By itself, the report is simple, it aggregates the totals presented by 05 (five) SQL sub-queries and shows it together. One interesting thing to note in the report is the use of “runtime variables“. The two run-time variables @FromDate and @ToDate are used to hold the user specified start and end date, instead of asking for the same repeatedly for each sub query.

SELECT * FROM 

(SELECT (@FromDate:=<<From date|date>>) AS 'From (y-m-d)', (@ToDate:=<<To date|date>>) AS 'To (y-m-d)') AS T1, 

(SELECT
    IFNULL(ROUND(SUM(accountlines.amount), 2), "0.00") AS 'Total Fines/Fees' FROM accountlines 
WHERE 
    accounttype IN ('F', 'FU', 'N', 'A', 'M', 'L') AND  DATE(timestamp) BETWEEN @FromDate AND @ToDate) AS T2, 

(SELECT 
    IFNULL(ROUND(SUM(accountlines.amountoutstanding), 2), "0.00") AS 'Total O/S' FROM accountlines 
WHERE 
    accounttype IN ('F', 'FU', 'N', 'A', 'M', 'L') AND  DATE(timestamp) BETWEEN @FromDate AND @ToDate) AS T3, 

(SELECT
    IFNULL(REPLACE(ROUND(SUM(amount),2),"-",""), "0.00") AS 'Paid / Credited'  FROM accountlines 
WHERE
   accounttype IN ('PAY', 'C') AND description NOT LIKE "%Reversed%" AND DATE(timestamp) BETWEEN @FromDate AND @ToDate) AS T4, 

(SELECT
    IFNULL(REPLACE(ROUND(SUM(amount),2),"-",""),"0.00") AS 'Written off'  FROM accountlines 
WHERE 
    accounttype='W' AND DATE(timestamp) BETWEEN @FromDate AND @ToDate) AS T5, 

(SELECT
    IFNULL(REPLACE(ROUND(SUM(amount),2), "-", ""), "0.00") AS 'Forgiven'  FROM accountlines 
WHERE 
    accounttype='FOR' AND DATE(timestamp) BETWEEN @FromDate AND @ToDate) AS T6

Cheers!

L2C2 Technologies extends technological support to KLA for open access journal

L2C2 Technologies to provide OJS software hosting and maintenance along with publishing support to Kerala Library Association for KJIST for the next 5 years.

Earlier today the first open access (OA) LIS journal from Kerala, India – “The Kerala Library Association Journal of Information Science & Technology” (KJIST) was launched with the release of its inaugural issue – Vol. 1 No. 1 July 2018 by Dr. Rajan Gurukkal (Vice Chairman, Kerala State Higher Education Council) at the DLIS Hall, Kerala University Library Building, Thiruvananthapuram.

As the first open access LIS journal being published from Kerala, all articles published in KJIST will be available online at https://www.kjist.in for reading and download. As the technology support partner L2C2 Technologies is providing the journal publishing software hosting & maintenance along with publishing support to Kerala Library Association for KJIST, initially for a period of 5 years. The journal is being hosted using the latest stable version of Open Journal Systems (OJS) journal publishing platform.

While readers are not required to register at the site to read the articles, registered users will receive automatic updates about articles and issues as and when they are published. The privacy policy is GDPR compliant and all personal data submitted by the readers, authors, reviewers and editors are maintained on servers geographically located in India. Mandatory SSL has been implemented for the site, so that when users interact with the site, e.g. when they login or read or download articles, all their transactions remain securely encrypted end-to-end at all times. L2C2 Technologies will also be providing KLA with in-depth usage statistics and data analytics for the online Journal site.

About KJIST

KLA Journal of Information Science and Technology (KJIST) is published twice every year (January and July) in open access (OA) and print formats. It will include original papers, short communications, book reviews and letters pertaining to library science, information science and related fields. The papers included will focus on aspects of exploring, applying, and evaluating new theories and ideas to develop and modernize libraries and enhance library services. It is meant for library professionals, documentation and information professionals, researchers, students and others interested in the library and information science field.

About Kerala Library Association

Formed in 1972 by a group of librarians who strongly felt the need for an independent professional association at the state level to promote the cause of library development, professional standards of librarians, entirely dedicated for libraries and librarians, the Kerala Library Association (KLA) is headquartered at Thiruvananathapuram, the capital of the state of Kerala, India.The association has boldly voiced the sentiments and the views of professionals on several occasions to the concerned authorities. It has successfully projected the need for improving library services in College & Universities. KLA helped the Government to formulate policies in relation to library development in the state.

Chinmaya University partners with L2C2 Technologies for Koha support

We are pleased to extend a warm welcome to the newest member of our growing client-partner family in Southern India – Chinmaya Viswavidyapeeth – a Deemed to be University under UGC, with campuses in Kochi and Pune. The CVV library, which is self-hosted, has gone live on Koha ILS 18.05.04, with server management and Koha maintenance being handled by L2C2 Technologies.

About Chinmaya Viswavidyapeeth

Inaugerated on 9th July, 2017 at Kochi as the Chinmaya Vishwavidyapeeth University for Sanskrit and Indic Traditions, de novo deemed university under the UGC, presently operates from two campuses – the Chinmaya Eswar Gurukula campus at Peppathy in Ernakulam and Chinmaya Naada Bindu Gurukula campus at Kolwan in Pune. It presently offers courses in a number of traditional and contemporary streams.

The university’s motto can be summed up as “विद्यया रक्षिता संस्कृतिः सर्वदा । संस्कृतेर्मानवाः संस्कृता भूरिदा:” (translated:Knowledge protects culture forever; Cultured people share abundantly) in the words of Swami Tejomayananda, founder, Chinmaya Viswavidyapeeth.

Surendranath Law College partners with L2C2 Technologies to automate their library

136 year old Surendranath Law College selects L2C2 Technologies’ Koha support.

We are pleased to extend a warm welcome to the newest member of our growing client-partner family in Eastern India – Surendranath Law College, Kolkata. At 136 years, SNLC is regarded as one the oldest law college of the country. The SNLC library has gone live on Koha ILS 17.11 series on L2C2 Technologies‘s cloud hosting platform.

About Surendranath Law College

Surendranath Law College formerly known as Ripon College) is an undergraduate law college affiliated with the University of Calcutta in Kolkata, India. It was established in 1885 by a trust formed by the nationalist leader, scholar and educationist Surendranath Banerjee, a year after he founded Surendranath College. This is now regarded one of the oldest Law college of the country. Dr. Rajendra Prasad, the first president of the Republic of India is among the several notable alumni of the college.

Source: Wikipedia

AskALibrarian – Koha’s new feedback form plugin

A Koha plugin that adds user feedback functionality to Koha’s OPAC.

What is the ‘AskALibrarian’ Koha plugin?

It is a Koha plugin that adds a modal user feedback form to the Koha OPAC. It is written to utility the Koha Plugin System that was originally introduced into #kohails with version 3.12. This means that while the feature is an extension to a stock Koha installation, it is essentially a koha-native solution. Thus it is well-integrated within Koha as compared to any other third party solution. In terms of direct advantages it brings in (a) ease of maintainability across Koha upgrades, (b) ability to use the submitted data directly from Koha’s SQL reports, (c) data privacy – the data is stored directly inside your Koha database, as opposed to with any other 3rd party solution (something that assumes far greater significance in the GDPR era).

Why this plugin?

The Koha OPAC provides a lot of functionality to both the library staff as well as the library patrons. Below is a short list of things you can (there are more) and things you can not do.

Things you can ? Things you can’t ?
  • Login with your user account
  • Reserve a book
  • Renew a book
  • Pay a fine or fee
  • Update your contact information
  • Do a self-registration
  • Submit a purchase suggestion
  • Add star ratings
  • And much more
  • Leave a feedback for the library staff

Since a lot of L2C2 Technologies’s client partners seem to be wanting the facility of user feedback forms, we finally decided “Why not… let’s do a plugin!” And so the AskALibrarian plugin was born.

How does it work?

The plugin adds a bootstrap modal feedback form as a menu option on the navbar at the top of the Koha OPAC. On submission of the form by the user / visitor, the information is submitted via an AJAX call to a Perl script – askalibrarian.pl, on the staff client side. The script invokes the required sub-routines from the plugin to do two things : (a) send the user an acknowledgement of the submitted input to the user’s email address, it provided is reachable and (b) store the submitted data in the koha_plugin_com_l2c2technologies_askalibrarian_feedback table in the Koha database. A callback sends up an alert popup on the OPAC, informing if it has successfully captured the data.

How to get the plugin?

The plugin is published under GNU GPL v3.0 license and is available here from L2C2 Technologies‘s GitHub repository. Pull requests for bugs or improvements are welcome 🙂

Please show me step by step!

Given how our readers from the Indian sub-continent are fond of “step-by-step” instructions, we have a small video for our readers (about 11 mins) that shows how to setup and use plugin.

Enjoy! May the source be with you!