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/ — mfue...@hmcpl.org
> On Jan 17, 2021, at 6:06 PM, Elaine Bradtke <e...@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.librar...@gmail.com>
> Date: Sat, Jan 16, 2021 at 7:30 PM
> Subject: Re: [Koha] SQL Report Coding need.
> To: Elaine Bradtke <e...@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, <e...@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.librar...@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@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
