[Koha] SQL Report Coding need.

[Martha Fuerst]

Assuming you’re running 19.11 or later, accounttype got changed to credit_type_code and debit_type_code.

SELECT
   CONCAT("<a href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=
<http://boraccount.pl/?borrowernumber=>", T1.borrowernumber, "\"
target=\"_blank\">View Transaction</a>") AS 'Click to view'
   , DATE_FORMAT(DATE(T1.timestamp), "%d/%m/%Y") AS 'Txn Date'
   , T2.cardnumber AS 'Lib Card No.'
   , CONCAT(T2.surname) AS 'Students Name'
   , T3.description AS 'Category'
 , T2.borrowernotes AS 'Dapartment'
   , CONCAT(LPAD(REPLACE(ROUND(T1.amount, 2),"-", ""), 8, " ")) AS 'Paid
Amount'
FROM
   accountlines T1
    LEFT JOIN borrowers T2 USING (borrowernumber)
   LEFT JOIN categories T3 USING (categorycode)
WHERE
   T1.credit_type_code="PAYMENT"
   AND
  DATE(T1.timestamp) BETWEEN <<From date|date>> AND <<To date|date>>
ORDER BY DATE(T1.timestamp)

That should give you the results you need - for more information on this change, I suggest Bywater’s blog post about it: https://bywatersolutions.com/education/the-accountlines-table-in-19-11 <https://bywatersolutions.com/education/the-accountlines-table-in-19-11>

Best,

Marti Fuerst
Systems Librarian
Huntsville-Madison County Public Library
915 Monroe St, Huntsville, AL 35801
http://hmcpl.org/ — mfuerst at hmcpl.org

> On Jan 17, 2021, at 6:06 PM, Elaine Bradtke <eb at efdss.org> wrote:
> 
> I'm a cut and paste report writer, I think you need someone who really
> understands SQL.  Forwarding to the list.
> Elaine
> VWML <https://vwml.org>
> 
> 
> 
> ---------- Forwarded message ---------
> From: MAMCET Library <mamcet.library10 at gmail.com>
> Date: Sat, Jan 16, 2021 at 7:30 PM
> Subject: Re: [Koha] SQL Report Coding need.
> To: Elaine Bradtke <eb at efdss.org>
> 
> 
> This report genereted but not out put ples help.
> Koha version 20.4
> 
> This report run Error in "T1.accounttype "
> 
> SELECT
>    CONCAT("<a href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=
> <http://boraccount.pl/?borrowernumber=>", T1.borrowernumber, "\"
> target=\"_blank\">View Transaction</a>") AS 'Click to view'
>    , DATE_FORMAT(DATE(T1.timestamp), "%d/%m/%Y") AS 'Txn Date'
>    , T2.cardnumber AS 'Lib Card No.'
>    , CONCAT(T2.surname) AS 'Students Name'
>    , T3.description AS 'Category'
>  , T2.borrowernotes AS 'Dapartment'
>    , CONCAT(LPAD(REPLACE(ROUND(T1.amount, 2),"-", ""), 8, " ")) AS 'Paid
> Amount'
> 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)
> 
> On Wed, 13 Jan 2021, 11:49 pm Elaine Bradtke, <eb at efdss.org> wrote:
> 
>> Do any of the reports here come close to what you need?
>> https://wiki.koha-community.org/wiki/SQL_Reports_Patrons
>> Elaine
>> VWML <https://vwml.org>
>> 
>> 
>> 
>> On Tue, Jan 12, 2021 at 10:28 PM MAMCET Library <
>> mamcet.library10 at gmail.com> wrote:
>> 
>>> Dear all good morning
>>> Please send the SQL Patrons Fine payment date wise report coding.
>>> Koha version 20.5
>>> 
>>> --
>>> Thanking you..
>>> 
>>> Librarian,
>>> MAMCET <http://mamcet.com/>
>>> Trichy-Chennai Trunk Road,
>>> Siruganur, Tiruchirappalli-621 105.
>>> _______________________________________________
>>> 
>>> Koha mailing list  http://koha-community.org
>>> Koha at lists.katipo.co.nz
>>> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>>> 
>> 
> _______________________________________________
> 
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha