Bug 24190 - Add additional Acquisition logging
Summary: Add additional Acquisition logging
Status: RESOLVED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Acquisitions (show other bugs)
Version: master
Hardware: All All
: P5 - low enhancement (vote)
Assignee: Andrew Isherwood
QA Contact: Nick Clemens
URL:
Keywords:
Depends on: 23971
Blocks: 25916
  Show dependency treegraph
 
Reported: 2019-12-09 12:17 UTC by Andrew Isherwood
Modified: 2022-05-27 21:20 UTC (History)
7 users (show)

See Also:
Change sponsored?: Sponsored
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
This enhancement adds additional logging of acquisition-related changes including: - Order line creation and cancellation - Invoice adjustment additions, amendments and deletions - Order line receipts against an invoice - Budget adjustments - Fund adjustments - Order release date (EDIFACT) The name of the system preference that enables logging of acquisition-related changes was changed from AcqLog to AcquisitionLog. Note: Acquisition logging was added in Koha 21.05.
Version(s) released in:
21.11.00


Attachments
Bug 24190 - Add acquisition logging (17.11 KB, patch)
2019-12-17 11:47 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190 - Add acquisition logging (17.11 KB, patch)
2019-12-17 12:41 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190 - Add acquisition logging (22.22 KB, patch)
2020-03-06 10:53 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190 - Add acquisition logging (22.46 KB, patch)
2020-05-19 11:10 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: (follow-up) Cancel order del item bug (1.54 KB, patch)
2020-05-19 11:10 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: Add acquisition logging (22.45 KB, patch)
2020-06-19 14:09 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 24190: (follow-up) Cancel order del item bug (1.54 KB, patch)
2020-06-19 14:09 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 24190: Add acquisition logging (22.51 KB, patch)
2020-06-19 14:11 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 24190: (follow-up) Cancel order del item bug (1.59 KB, patch)
2020-06-19 14:11 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 24190: Add acquisition logging (22.36 KB, patch)
2020-10-02 09:54 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: (follow-up) Cancel order del item bug (1.59 KB, patch)
2020-10-02 09:54 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: (follow-up) Respond to QA feedback (12.33 KB, patch)
2020-10-02 09:54 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: (follow-up) Add tests (4.46 KB, patch)
2020-10-02 09:54 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: (follow-up) Fix erroneous tabs (3.67 KB, patch)
2020-10-02 09:55 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: Add acquisition logging (22.35 KB, patch)
2020-10-26 11:46 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: (follow-up) Cancel order del item bug (1.31 KB, patch)
2020-10-26 11:46 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: (follow-up) Respond to QA feedback (12.33 KB, patch)
2020-10-26 11:46 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: (follow-up) Add tests (4.46 KB, patch)
2020-10-26 11:47 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: (follow-up) Fix erroneous tabs (3.67 KB, patch)
2020-10-26 11:47 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: (follow-up) Modify order of logging (1.41 KB, patch)
2020-10-26 11:47 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: Add acquisition logging (22.55 KB, patch)
2021-02-01 12:05 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: (follow-up) Cancel order del item bug (1.31 KB, patch)
2021-02-01 12:05 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: (follow-up) Respond to QA feedback (12.38 KB, patch)
2021-02-01 12:05 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: (follow-up) Add tests (4.46 KB, patch)
2021-02-01 12:05 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: (follow-up) Fix erroneous tabs (1.65 KB, patch)
2021-02-01 12:05 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: (follow-up) Modify order of logging (1.31 KB, patch)
2021-02-01 12:05 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: Add acquisition logging (17.56 KB, patch)
2021-06-16 13:29 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: (follow-up) Cancel order del item bug (1.31 KB, patch)
2021-06-16 13:29 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: (follow-up) Respond to QA feedback (12.38 KB, patch)
2021-06-16 13:29 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: (follow-up) Add tests (4.47 KB, patch)
2021-06-16 13:29 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: (follow-up) Modify order of logging (1.32 KB, patch)
2021-06-16 13:29 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: (Follow-up) Rename AcqLog (6.06 KB, patch)
2021-06-16 13:29 UTC, Andrew Isherwood
Details | Diff | Splinter Review
Bug 24190: Add acquisition logging (18.30 KB, patch)
2021-08-02 13:47 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 24190: (follow-up) Cancel order del item bug (1.37 KB, patch)
2021-08-02 13:47 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 24190: (follow-up) Respond to QA feedback (12.93 KB, patch)
2021-08-02 13:47 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 24190: (follow-up) Add tests (5.00 KB, patch)
2021-08-02 13:47 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 24190: (follow-up) Modify order of logging (1.37 KB, patch)
2021-08-02 13:47 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 24190: (follow-up) Rename AcqLog (6.12 KB, patch)
2021-08-02 13:47 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 24190: (QA follow-up) record unchanged bookfund and fix typo (1.47 KB, patch)
2021-08-02 13:48 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 24190: (follow-up) Rename AcqLog (6.68 KB, patch)
2021-09-21 17:35 UTC, Jonathan Druart
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Andrew Isherwood 2019-12-09 12:17:25 UTC
A start was made on adding logging for Acquisitions in bug 23971. This bug will extend this as detailed below (note, some of the information detailed here will be derived via reporting and not explicitly logged):

1. Order line cancellations
Details including who made the cancellation and when the cancellation was made


2. Order line creations
Details including who created the order line, when it was created, the fund, and amount


3. Invoice adjustment additions
Details including who added the adjustment, when it was added, the fund, and amount


4. Invoice adjustment amendments
Details including who amended the adjustment, when it was amended, the fund, and amount


5. Invoice adjustment deletions
Details including who deleted the adjustment, when it was deleted, the fund, and amount


6. Order line receipts against an invoice
Details including who received the order line, when it was received, the fund, and amount


7. Budget Adjustments
This would show amounts added or removed from a budget (excluding spent), who, when, and amount
(In Koha the actual amount is edited rather than typing in the amount to change)

8. Fund adjustments
This is essentially a bank statement. It would show additions and subtractions to a fund (excluding spent), who, when, amount, previous total, new total

9. Order release date (1)
This would show individual order lines from an EDI message, when they were sent, who sent it, fund, and amount

10. Order release date (2)
This would show individual order lines from a basket, when it was closed, who closed it, fund, and amount
Comment 1 Andrew Isherwood 2019-12-17 11:47:12 UTC
Created attachment 96364 [details] [review]
Bug 24190 - Add acquisition logging
Comment 2 Andrew Isherwood 2019-12-17 12:41:43 UTC
Created attachment 96369 [details] [review]
Bug 24190 - Add acquisition logging
Comment 3 Andrew Isherwood 2019-12-17 12:41:55 UTC
Associated reports:

1. Order line cancellations
SELECT
    o.ordernumber AS 'Order number',
    ba.basketname AS 'Basket',
    f.budget_name AS 'Fund',
    CONCAT(b.firstname, ' ', b.surname) AS 'Cancelled by',
    a.timestamp AS 'Cancelled timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
INNER JOIN aqorders o ON a.object = o.ordernumber
INNER JOIN aqbasket ba ON o.basketno = ba.basketno
LEFT JOIN aqbudgets f ON o.budget_id = f.budget_id
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'CANCEL_ORDER' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>>

2. Order line creations
SELECT
    o.ordernumber AS 'Order number',
    ba.basketname AS 'Basket',
    f.budget_name AS 'Fund',
    CONCAT(b.firstname, ' ', b.surname) AS 'Created by',
    a.timestamp AS 'Created timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
INNER JOIN aqorders o ON a.object = o.ordernumber
INNER JOIN aqbasket ba ON o.basketno = ba.basketno
LEFT JOIN aqbudgets f ON o.budget_id = f.budget_id
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'CREATE_ORDER' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>>

3. Invoice adjustment additions
SELECT
    f.budget_name AS 'Fund',
    i.invoicenumber,
    FORMAT(SUBSTRING(a.info, 1, 10), 2) AS 'Amount',
    ia.note AS 'Note',
    CASE WHEN SUBSTRING(a.info, 101, 10) = 0 THEN 'No' ELSE 'Yes' END AS 'Encumber open',
    av.lib AS 'Reason',
    CONCAT(b.firstname, ' ', b.surname) AS 'Created by',
    a.timestamp AS 'Created timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
INNER JOIN aqinvoice_adjustments ia ON a.object = ia.adjustment_id
INNER JOIN aqinvoices i ON i.invoiceid = ia.invoiceid
LEFT JOIN aqbudgets f ON SUBSTRING(a.info, 91, 10) = f.budget_id
LEFT JOIN authorised_values av ON av.category = 'ADJ_REASON' AND av.authorised_value = TRIM(LEADING ' ' FROM SUBSTRING(a.info, 11, 80))
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'CREATE_INVOICE_ADJUSTMENT' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>>

4. Invoice adjustment amendments
SELECT
    i.invoicenumber AS 'Invoice number',
    f_before.budget_name AS 'Fund before',
    f_after.budget_name AS 'Fund after',
    FORMAT(SUBSTRING(a.info, 131, 10), 2) AS 'Amount before',
    FORMAT(SUBSTRING(a.info, 1, 10), 2) AS 'Amount after',
    ia.note AS 'Note',
    CASE WHEN SUBSTRING(a.info, 121, 10) = 0 THEN 'No' ELSE 'Yes' END AS 'Encumber open before',
    CASE WHEN SUBSTRING(a.info, 101, 10) = 0 THEN 'No' ELSE 'Yes' END AS 'Encumber open after',
    av_before.lib AS 'Reason before',
    av_after.lib AS 'Reason after',
    CONCAT(b.firstname, ' ', b.surname) AS 'Created by',
    a.timestamp AS 'Created timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
INNER JOIN aqinvoice_adjustments ia ON a.object = ia.adjustment_id
INNER JOIN aqinvoices i ON i.invoiceid = ia.invoiceid
LEFT JOIN aqbudgets f_before ON SUBSTRING(a.info, 111, 10) = f_before.budget_id
LEFT JOIN aqbudgets f_after ON SUBSTRING(a.info, 91, 10) = f_after.budget_id
LEFT JOIN authorised_values av_before ON av_before.category = 'ADJ_REASON' AND av_before.authorised_value = TRIM(LEADING ' ' FROM SUBSTRING(a.info, 141, 80))
LEFT JOIN authorised_values av_after ON av_after.category = 'ADJ_REASON' AND av_after.authorised_value = TRIM(LEADING ' ' FROM SUBSTRING(a.info, 11, 80))
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'UPDATE_INVOICE_ADJUSTMENT' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>>

5. Invoice adjustment deletions
SELECT
    i.invoicenumber AS 'Invoice number',
    f.budget_name AS 'Fund',
    FORMAT(SUBSTRING(a.info, 31, 10), 2) AS 'Amount',
    CASE WHEN SUBSTRING(a.info, 21, 10) = 0 THEN 'No' ELSE 'Yes' END AS 'Encumber open',
    av.lib AS 'Reason',
    CONCAT(b.firstname, ' ', b.surname) AS 'Deleted by',
    a.timestamp AS 'Deleted timestamp',
    SUBSTRING(a.info, 31, 10)
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
INNER JOIN aqinvoices i ON i.invoiceid = SUBSTRING(a.info, 1, 10)
LEFT JOIN aqbudgets f ON SUBSTRING(a.info, 11, 10) = f.budget_id
LEFT JOIN authorised_values av ON av.category = 'ADJ_REASON' AND av.authorised_value = TRIM(LEADING ' ' FROM SUBSTRING(a.info, 41, 80))
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'DELETE_INVOICE_ADJUSTMENT' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>>

6. Order line receipts against an invoice
SELECT
	o.ordernumber AS 'Order number',
    FORMAT(SUBSTRING(a.info, 1, 10), 0) AS 'Quantity received',
    f.budget_name AS 'Fund',
    FORMAT(SUBSTRING(a.info, 21, 10), 2) AS 'Tax rate',
    FORMAT(SUBSTRING(a.info, 31, 10), 2) AS 'Replacement price',
    FORMAT(SUBSTRING(a.info, 41, 10), 2) AS 'Actual cost',
    CONCAT(b.firstname, ' ', b.surname) AS 'Received by',
    a.timestamp AS 'Received timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
INNER JOIN aqorders o ON a.object = o.ordernumber
LEFT JOIN aqbudgets f ON SUBSTRING(a.info, 11, 10) = f.budget_id
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'RECEIVE_ORDER' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>>

7. Budget Adjustments
SELECT
    SUBSTR(a.info, 31, 10) AS 'Budget start before',
	SUBSTR(a.info, 1, 10) AS 'Budget start after',
    SUBSTR(a.info, 41, 10) AS 'Budget end before',
	SUBSTR(a.info, 11, 10) AS 'Budget end after',
    FORMAT(SUBSTR(a.info, 51, 10), 2) AS 'Total amount before',
    FORMAT(SUBSTR(a.info, 21, 10), 2) AS 'Total amount after',
    FORMAT(SUBSTR(a.info, 61, 10), 2) AS 'Difference',
    CONCAT(b.firstname, ' ', b.surname) AS 'Modified by',
    a.timestamp AS 'Modified timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'MODIFY_BUDGET' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>>

8. Fund adjustments
SELECT
    a.object AS 'Fund ID',
    FORMAT(SUBSTR(a.info, 31, 10), 2) AS 'Amount before',
    FORMAT(SUBSTR(a.info, 1, 10) ,2 ) AS 'Amount after',
	FORMAT(SUBSTR(a.info, 41, 10), 2) AS 'Warn at % before',
	FORMAT(SUBSTR(a.info, 11, 10), 2) AS 'Warn at % after',
    FORMAT(SUBSTR(a.info, 51, 10), 2) AS 'Warn at amount before',
    FORMAT(SUBSTR(a.info, 21, 10), 2) AS 'Warn at amount after',
    FORMAT(SUBSTR(a.info, 61, 10), 2) AS 'Difference',
    CONCAT(b.firstname, ' ', b.surname) AS 'Modified by',
    a.timestamp AS 'Modified timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'MODIFY_FUND' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>>

9. Order release date (1)
SELECT
    ba.basketname AS "Basket name",
    bs.name AS "Vendor name",
    bi.title AS 'Title',
    bi.author AS 'Author',
    o.quantity AS 'Quantity',
    FORMAT(o.listprice, 2) AS 'Vendor price',
    FORMAT(o.rrp, 2) AS 'Retail price',
    FORMAT(o.ecost, 2) AS 'Budgeted cost',
    FORMAT(o.unitprice, 2) AS 'Actual cost',
    bu.budget_name AS 'Fund',
    CONCAT(b.firstname, ' ', b.surname) AS 'Basket closed by',
    a.timestamp AS 'Basket closed timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
INNER JOIN aqbasket ba ON a.object = ba.basketno
INNER JOIN aqorders o ON o.basketno = ba.basketno
INNER JOIN aqbooksellers bs ON ba.booksellerid = bs.id
LEFT JOIN aqbudgets bu ON bu.budget_id = o.budget_id
LEFT JOIN biblio bi ON bi.biblionumber = o.biblionumber
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'CLOSE_BASKET' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>> AND
    a.object = <<Basket ID>>

10. Order release date (2)
SELECT
    o.ordernumber AS 'Order number',
    b.basketname AS 'Basket',
    bu.budget_name AS 'Fund',
    bi.title AS 'Title',
    bi.author AS 'Author',
    CONCAT(bo.firstname, ' ', bo.surname) AS 'Sent by',
    e.transfer_date AS 'Sent date'
FROM
    edifact_messages e
LEFT JOIN aqbasket b ON e.basketno = b.basketno
LEFT JOIN aqorders o ON o.basketno = b.basketno
LEFT JOIN action_logs a ON e.basketno = a.object
LEFT JOIN biblio bi ON o.biblionumber = bi.biblionumber
LEFT JOIN borrowers bo ON a.user = bo.borrowernumber
INNER JOIN aqbudgets bu ON o.budget_id = bu.budget_id
WHERE
    e.message_type='ORDER' AND
    e.status = 'Sent' AND
    a.module = 'ACQUISITIONS' AND
    a.action = 'APPROVE_BASKET' AND
    date(e.transfer_date) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>> AND
    e.basketno = <<Basket ID>>
Comment 4 Andrew Isherwood 2019-12-17 14:07:59 UTC
1. Order line cancellations:
----------------------------
SELECT
    o.ordernumber AS 'Order number',
    bi.title AS 'Title',
    bi.author AS 'Author',
    ba.basketname AS 'Basket',
    f.budget_name AS 'Fund',
    CONCAT(b.firstname, ' ', b.surname) AS 'Cancelled by',
    a.timestamp AS 'Cancelled timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
INNER JOIN aqorders o ON a.object = o.ordernumber
INNER JOIN aqbasket ba ON o.basketno = ba.basketno
LEFT JOIN aqbudgets f ON o.budget_id = f.budget_id
LEFT JOIN biblio bi ON o.biblionumber = bi.biblionumber
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'CANCEL_ORDER' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>>

Order line creations:
---------------------
SELECT
    o.ordernumber AS 'Order number',
    ba.basketname AS 'Basket',
    bi.title AS 'Title',
    bi.author AS 'Author',
    f.budget_name AS 'Fund',
    CONCAT(b.firstname, ' ', b.surname) AS 'Created by',
    a.timestamp AS 'Created timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
INNER JOIN aqorders o ON a.object = o.ordernumber
INNER JOIN aqbasket ba ON o.basketno = ba.basketno
LEFT JOIN aqbudgets f ON o.budget_id = f.budget_id
LEFT JOIN biblio bi ON o.biblionumber = bi.biblionumber
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'CREATE_ORDER' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>>

3. Invoice adjustment additions:
--------------------------------
SELECT
    f.budget_name AS 'Fund',
    i.invoicenumber,
    FORMAT(SUBSTRING(a.info, 1, 10), 2) AS 'Amount',
    ia.note AS 'Note',
    CASE WHEN SUBSTRING(a.info, 101, 10) = 0 THEN 'No' ELSE 'Yes' END AS 'Encumber open',
    av.lib AS 'Reason',
    CONCAT(b.firstname, ' ', b.surname) AS 'Created by',
    a.timestamp AS 'Created timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
INNER JOIN aqinvoice_adjustments ia ON a.object = ia.adjustment_id
INNER JOIN aqinvoices i ON i.invoiceid = ia.invoiceid
LEFT JOIN aqbudgets f ON SUBSTRING(a.info, 91, 10) = f.budget_id
LEFT JOIN authorised_values av ON av.category = 'ADJ_REASON' AND av.authorised_value = TRIM(LEADING ' ' FROM SUBSTRING(a.info, 11, 80))
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'CREATE_INVOICE_ADJUSTMENT' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>>

4. Invoice adjustment amendments:
---------------------------------
SELECT
    i.invoicenumber AS 'Invoice number',
    f_before.budget_name AS 'Fund before',
    f_after.budget_name AS 'Fund after',
    FORMAT(SUBSTRING(a.info, 131, 10), 2) AS 'Amount before',
    FORMAT(SUBSTRING(a.info, 1, 10), 2) AS 'Amount after',
    ia.note AS 'Note',
    CASE WHEN SUBSTRING(a.info, 121, 10) = 0 THEN 'No' ELSE 'Yes' END AS 'Encumber open before',
    CASE WHEN SUBSTRING(a.info, 101, 10) = 0 THEN 'No' ELSE 'Yes' END AS 'Encumber open after',
    av_before.lib AS 'Reason before',
    av_after.lib AS 'Reason after',
    CONCAT(b.firstname, ' ', b.surname) AS 'Created by',
    a.timestamp AS 'Created timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
INNER JOIN aqinvoice_adjustments ia ON a.object = ia.adjustment_id
INNER JOIN aqinvoices i ON i.invoiceid = ia.invoiceid
LEFT JOIN aqbudgets f_before ON SUBSTRING(a.info, 111, 10) = f_before.budget_id
LEFT JOIN aqbudgets f_after ON SUBSTRING(a.info, 91, 10) = f_after.budget_id
LEFT JOIN authorised_values av_before ON av_before.category = 'ADJ_REASON' AND av_before.authorised_value = TRIM(LEADING ' ' FROM SUBSTRING(a.info, 141, 80))
LEFT JOIN authorised_values av_after ON av_after.category = 'ADJ_REASON' AND av_after.authorised_value = TRIM(LEADING ' ' FROM SUBSTRING(a.info, 11, 80))
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'UPDATE_INVOICE_ADJUSTMENT' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>>

5. Invoice adjustment deletions:
--------------------------------
SELECT
    i.invoicenumber AS 'Invoice number',
    f.budget_name AS 'Fund',
    FORMAT(SUBSTRING(a.info, 31, 10), 2) AS 'Amount',
    CASE WHEN SUBSTRING(a.info, 21, 10) = 0 THEN 'No' ELSE 'Yes' END AS 'Encumber open',
    av.lib AS 'Reason',
    CONCAT(b.firstname, ' ', b.surname) AS 'Deleted by',
    a.timestamp AS 'Deleted timestamp',
    SUBSTRING(a.info, 31, 10)
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
INNER JOIN aqinvoices i ON i.invoiceid = SUBSTRING(a.info, 1, 10)
LEFT JOIN aqbudgets f ON SUBSTRING(a.info, 11, 10) = f.budget_id
LEFT JOIN authorised_values av ON av.category = 'ADJ_REASON' AND av.authorised_value = TRIM(LEADING ' ' FROM SUBSTRING(a.info, 41, 80))
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'DELETE_INVOICE_ADJUSTMENT' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>>

6. Order line receipts against an invoice:
-----------------------------------------
SELECT
	o.ordernumber AS 'Order number',
    FORMAT(SUBSTRING(a.info, 1, 10), 0) AS 'Quantity received',
    f.budget_name AS 'Fund',
    FORMAT(SUBSTRING(a.info, 21, 10), 2) AS 'Tax rate',
    FORMAT(SUBSTRING(a.info, 31, 10), 2) AS 'Replacement price',
    FORMAT(SUBSTRING(a.info, 41, 10), 2) AS 'Actual cost',
    CONCAT(b.firstname, ' ', b.surname) AS 'Received by',
    a.timestamp AS 'Received timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
INNER JOIN aqorders o ON a.object = o.ordernumber
LEFT JOIN aqbudgets f ON SUBSTRING(a.info, 11, 10) = f.budget_id
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'RECEIVE_ORDER' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>>

7. Budget Adjustments:
----------------------
SELECT
    SUBSTR(a.info, 31, 10) AS 'Budget start before',
	SUBSTR(a.info, 1, 10) AS 'Budget start after',
    SUBSTR(a.info, 41, 10) AS 'Budget end before',
	SUBSTR(a.info, 11, 10) AS 'Budget end after',
    FORMAT(SUBSTR(a.info, 51, 10), 2) AS 'Total amount before',
    FORMAT(SUBSTR(a.info, 21, 10), 2) AS 'Total amount after',
    FORMAT(SUBSTR(a.info, 61, 10), 2) AS 'Difference',
    CONCAT(b.firstname, ' ', b.surname) AS 'Modified by',
    a.timestamp AS 'Modified timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'MODIFY_BUDGET' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>>

8. Fund adjustments:
--------------------
SELECT
    a.object AS 'Fund ID',
    FORMAT(SUBSTR(a.info, 31, 10), 2) AS 'Amount before',
    FORMAT(SUBSTR(a.info, 1, 10) ,2 ) AS 'Amount after',
	FORMAT(SUBSTR(a.info, 41, 10), 2) AS 'Warn at % before',
	FORMAT(SUBSTR(a.info, 11, 10), 2) AS 'Warn at % after',
    FORMAT(SUBSTR(a.info, 51, 10), 2) AS 'Warn at amount before',
    FORMAT(SUBSTR(a.info, 21, 10), 2) AS 'Warn at amount after',
    FORMAT(SUBSTR(a.info, 61, 10), 2) AS 'Difference',
    CONCAT(b.firstname, ' ', b.surname) AS 'Modified by',
    a.timestamp AS 'Modified timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'MODIFY_FUND' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>>

9. Order release date (1):
--------------------------
SELECT
    ba.basketname AS "Basket name",
    bs.name AS "Vendor name",
    bi.title AS 'Title',
    bi.author AS 'Author',
    o.quantity AS 'Quantity',
    FORMAT(o.listprice, 2) AS 'Vendor price',
    FORMAT(o.rrp, 2) AS 'Retail price',
    FORMAT(o.ecost, 2) AS 'Budgeted cost',
    FORMAT(o.unitprice, 2) AS 'Actual cost',
    bu.budget_name AS 'Fund',
    CONCAT(b.firstname, ' ', b.surname) AS 'Basket closed by',
    a.timestamp AS 'Basket closed timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
INNER JOIN aqbasket ba ON a.object = ba.basketno
INNER JOIN aqorders o ON o.basketno = ba.basketno
INNER JOIN aqbooksellers bs ON ba.booksellerid = bs.id
LEFT JOIN aqbudgets bu ON bu.budget_id = o.budget_id
LEFT JOIN biblio bi ON bi.biblionumber = o.biblionumber
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'CLOSE_BASKET' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>> AND
    a.object = <<Basket ID>>

10. Order release date (2):
---------------------------
SELECT
    o.ordernumber AS 'Order number',
    b.basketname AS 'Basket',
    bu.budget_name AS 'Fund',
    bi.title AS 'Title',
    bi.author AS 'Author',
    CONCAT(bo.firstname, ' ', bo.surname) AS 'Sent by',
    e.transfer_date AS 'Sent date'
FROM
    edifact_messages e
LEFT JOIN aqbasket b ON e.basketno = b.basketno
LEFT JOIN aqorders o ON o.basketno = b.basketno
LEFT JOIN action_logs a ON e.basketno = a.object
LEFT JOIN biblio bi ON o.biblionumber = bi.biblionumber
LEFT JOIN borrowers bo ON a.user = bo.borrowernumber
INNER JOIN aqbudgets bu ON o.budget_id = bu.budget_id
WHERE
    e.message_type='ORDER' AND
    e.status = 'Sent' AND
    a.module = 'ACQUISITIONS' AND
    a.action = 'APPROVE_BASKET' AND
    date(e.transfer_date) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>> AND
    e.basketno = <<Basket ID>>
Comment 5 Andrew Isherwood 2019-12-17 14:08:34 UTC
Test plan:

Setup:
1. Apply patch and run database update if necessary

Order line cancellations:
1. Go to "Aquisitions", find an open basket with at least one order
2. Cancel the order and confirm the cancellation
3. From the staff main page, go to "Reports > Create from SQL"
4. Name the report "Order line cancellations"
5. In the SQL box, enter the report labelled "Order line cancellations" in comment #4 above
6. Save the report, the run it, choosing a start and end date
7. TEST => Observe that the order cancellation you just carried out is listed

Order line creations:
1. Go to "Aquisitions", find or create an open basket
2. Create a new order in the basket
3. From the staff main page, go to "Reports > Create from SQL"
4. Name the report "Order line creations"
5. In the SQL box, enter the report labelled "Order line creations" in comment #4 above
6. Save the report, the run it, choosing a start and end date
7. TEST => Observe that the order creation you just carried out is listed

Invoice adjustment additions:
1. Go to "Aquisitions", find or create an invoice
2. Click "Add an adjustment"
3. Fill in the details of the adjustment, then click "Update adjustments"
4. From the staff main page, go to "Reports > Create from SQL"
5. Name the report "Invoice adjustment additions"
6. In the SQL box, enter the report labelled "Invoice adjustment additions" in comment #4 above
7. Save the report, the run it, choosing a start and end date
8. TEST => Observe that the adjustment creation you just carried out is listed

Invoice adjustment amendments:
1. Go to "Aquisitions", find the invoice from the previous steps
2. Find the adjustment you added and modify it
3. Click "Update adjustments"
4. From the staff main page, go to "Reports > Create from SQL"
5. Name the report "Invoice adjustment amendments"
6. In the SQL box, enter the report labelled "Invoice adjustment amendments" in comment #4 above
7. Save the report, the run it, choosing a start and end date
8. TEST => Observe that the adjustment amendment you just carried out is listed

Invoice adjustment deletions:
1. Go to "Aquisitions", find the invoice from the previous steps
2. Find the adjustment you added and delete it
3. From the staff main page, go to "Reports > Create from SQL"
4. Name the report "Invoice adjustment deletions"
5. In the SQL box, enter the report labelled "Invoice adjustment deletions" in comment #4 above
6. Save the report, the run it, choosing a start and end date
7. TEST => Observe that the adjustment deletion you just carried out is listed

Order line receipts against an invoice:
1. Go to "Aquisitions", find an open invoice with unreceived items
2. Go to the "Receipt summary" page
3. Click "Receive" for an item
4. Fill in the data as appropriate, then click "Save"
5. From the staff main page, go to "Reports > Create from SQL"
6. Name the report "Order line receipts against an invoice"
7. In the SQL box, enter the report labelled "Order line receipts against an invoice" in comment #4 above
8. Save the report, the run it, choosing a start and end date
9. TEST => Observe that the receipt you just carried out is listed

Budget adjustments:
1. Go to "Aquisitions", find a budget
2. Click "Actions" > "Edit"
3. Modify some properties of the budget
4. Click "Save"
5. From the staff main page, go to "Reports > Create from SQL"
6. Name the report "Budget adjustments"
7. In the SQL box, enter the report labelled "Budget adjustments" in comment #4 above
8. Save the report, the run it, choosing a start and end date
9. TEST => Observe that the adjustment you just carried out is listed, including details of the "before" and "after" values

Fund adjustments:
1. Go to "Aquisitions", find a fund
2. Click "Actions" > "Edit"
3. Modify some properties of the fund
4. Click "Submit"
5. From the staff main page, go to "Reports > Create from SQL"
6. Name the report "Fund adjustments"
7. In the SQL box, enter the report labelled "Fund adjustments" in comment #4 above
8. Save the report, the run it, choosing a start and end date
9. TEST => Observe that the adjustment you just carried out is listed, including details of the "before" and "after" values

Order release date (1):
1. Go to "Aquisitions", find an open basket
2. Close the basket, noting it's ID
3. From the staff main page, go to "Reports > Create from SQL"
4. Name the report "Order release date (1)"
5. In the SQL box, enter the report labelled "Order release date (1)" in comment #4 above
6. Save the report, the run it, choosing a start and end date and basket ID
7. TEST => Observe that the details of the closed basket are listed

Order release date (2):
1. Release a basket via an EDI message, resulting in a row in edifact_messages table
3. From the staff main page, go to "Reports > Create from SQL"
4. Name the report "Order release date (2)"
5. In the SQL box, enter the report labelled "Order release date (2)" in comment #4 above
6. Save the report, the run it, choosing a start and end date and basket ID
7. TEST => Observe that the details of the released orders are listed
Comment 6 Andrew Isherwood 2020-03-06 10:53:06 UTC
Created attachment 100243 [details] [review]
Bug 24190 - Add acquisition logging
Comment 7 Andrew Isherwood 2020-05-19 11:10:43 UTC
Created attachment 105082 [details] [review]
Bug 24190 - Add acquisition logging
Comment 8 Andrew Isherwood 2020-05-19 11:10:46 UTC
Created attachment 105083 [details] [review]
Bug 24190: (follow-up) Cancel order del item bug

If an order is cancelled but the associated bib / items are unable to be
removed, we go into error handling. We still need to log the
cancellation though. So this fix moves the logging to just after the
cancellation, so it will be logged regardless of the outcome with
associated things.
Comment 9 Martin Renvoize 2020-06-19 14:09:10 UTC
Created attachment 106096 [details] [review]
Bug 24190: Add acquisition logging
Comment 10 Martin Renvoize 2020-06-19 14:09:13 UTC
Created attachment 106097 [details] [review]
Bug 24190: (follow-up) Cancel order del item bug

If an order is cancelled but the associated bib / items are unable to be
removed, we go into error handling. We still need to log the
cancellation though. So this fix moves the logging to just after the
cancellation, so it will be logged regardless of the outcome with
associated things.
Comment 11 Martin Renvoize 2020-06-19 14:11:38 UTC
Created attachment 106098 [details] [review]
Bug 24190: Add acquisition logging

Signed-off-by: Maura Stephens <maura.stephens@gmit.ie>
Comment 12 Martin Renvoize 2020-06-19 14:11:41 UTC
Created attachment 106099 [details] [review]
Bug 24190: (follow-up) Cancel order del item bug

If an order is cancelled but the associated bib / items are unable to be
removed, we go into error handling. We still need to log the
cancellation though. So this fix moves the logging to just after the
cancellation, so it will be logged regardless of the outcome with
associated things.

Signed-off-by: Maura Stephens <maura.stephens@gmit.ie>
Comment 13 Martin Renvoize 2020-06-19 14:12:30 UTC
Tested and signed off on PTFS-E sandboxes by Maura Stephens
Comment 14 Jonathan Druart 2020-08-04 14:17:32 UTC
Some comments:

1. You really should avoid indentation spaces outside of the block you are editing. It makes rebase a nightmare for you and for those who will have to rebase on top of it.

2. Why are you using the "%010d" format?
Why are not you dumping (using Data::Dumper) the whole $budget?
I have not tested but
+                sprintf("%010d", $del_adj->invoiceid) .
+                sprintf("%010d", $del_adj->budget_id) .

That will produce a concat of 00000000010000000001 (for invoiceid=1 and budget_id=1
Is it really what we expect?

And why 10? rrp_tax_excluded is decimal(28,6) for instance


3. +                eval { output_pref({ dt => dt_from_string( $input->param('budget_period_startdate') ), dateformat => 'iso', dateonly => 1 } ); } .
I don't think we should store formatted dates.
Comment 15 Jonathan Druart 2020-08-04 14:22:02 UTC
4. You should also consider adding tests
Comment 16 Katrin Fischer 2020-08-23 11:08:08 UTC
I just noticed that there is a dependent bug, that needs to be tested first, but worth noting, that this does no longer apply on top:

Apply? [(y)es, (n)o, (i)nteractive] y
Applying: Bug 24190: Add acquisition logging
error: sha1 information is lacking or useless (koha-tmpl/intranet-tmpl/prog/en/modules/tools/viewlog.tt).
error: could not build fake ancestor
Patch failed at 0001 Bug 24190: Add acquisition logging
The copy of the patch that failed is found in: .git/rebase-apply/patch
When you have resolved this problem run "git bz apply --continue".
If you would prefer to skip this patch, instead run "git bz apply --skip".
To restore the original branch and stop patching run "git bz apply --abort".
Patch left in /tmp/Bug-24190-Add-acquisition-logging-mCG2UX.patch
Comment 17 Katrin Fischer 2020-08-23 11:32:46 UTC
Please also check the comments!
Comment 18 Andrew Isherwood 2020-09-25 09:33:56 UTC
(In reply to Jonathan Druart from comment #14)
Hi Jonathan

> 1. You really should avoid indentation spaces outside of the block you are editing

It was a while ago, but this was almost certainly my editor. I'm very conscious of irrelevant changes, so I'm sure I wouldn't have done this deliberately. I'll keep a closer eye on it in future.

> 2. Why are you using the "%010d" format?

Since the data I was storing was pretty small, storing an entire Dumper output felt over the top. However, I have since discovered that a precedent has been set for storing action_log data in JSON, so I will move to using this as it is infinitely more readable than zero padded strings.

> 3. I don't think we should store formatted dates.

OK, fair enough, I'll change that

> 4. You should also consider adding tests

OK, I'll look at doing that. I don't think they'll be anything beyond "does this function call logaction with the correct payload", but it's better than nothing
Comment 19 Andrew Isherwood 2020-10-01 10:29:17 UTC
Hi Jonathan - After creating the patches to use JSON rather than zero padded strings, I remembered the reason I went for zero padded strings rather than another format. Since the content of the logged data needs to be used in SQL reports, I need it to be in a format that can be parsed using SQL. JSON is the obvious choice, however JSON support doesn't exist on the older versions of MySQL that Koha supports (e.g. 5.5), so that is not a viable option. The only method I could come up with for storing arbitrary data was in padded strings, then using the SUBSTRING operator to extract the data in a report.

> And why 10? rrp_tax_excluded is decimal(28,6) for instance

OK, I'd not appreciated that. I can increase the padding to a much larger value if we're allowing values with precision of 28 to be stored... I wanted the padding to be sufficient, 10 seemed to be enough, but apparently not.
Comment 20 Andrew Isherwood 2020-10-01 10:43:24 UTC
I gather you've spoken with Martin, I'm going to proceed with storing in JSON
Comment 21 Andrew Isherwood 2020-10-02 09:54:43 UTC
Created attachment 111080 [details] [review]
Bug 24190: Add acquisition logging

Signed-off-by: Maura Stephens <maura.stephens@gmit.ie>
Comment 22 Andrew Isherwood 2020-10-02 09:54:47 UTC
Created attachment 111081 [details] [review]
Bug 24190: (follow-up) Cancel order del item bug

If an order is cancelled but the associated bib / items are unable to be
removed, we go into error handling. We still need to log the
cancellation though. So this fix moves the logging to just after the
cancellation, so it will be logged regardless of the outcome with
associated things.

Signed-off-by: Maura Stephens <maura.stephens@gmit.ie>
Comment 23 Andrew Isherwood 2020-10-02 09:54:52 UTC
Created attachment 111082 [details] [review]
Bug 24190: (follow-up) Respond to QA feedback

This commit makes changes in response to Jonathan's feedback in comment

- Moved from using zero padded strings to store log data to a JSON
object
- Stopped storing formatted dates in logged data
Comment 24 Andrew Isherwood 2020-10-02 09:54:58 UTC
Created attachment 111083 [details] [review]
Bug 24190: (follow-up) Add tests

In response to Jonathan's request for tests, I have now added additional
tests for the methods in C4/Budgets.pm that do logging
Comment 25 Andrew Isherwood 2020-10-02 09:55:04 UTC
Created attachment 111084 [details] [review]
Bug 24190: (follow-up) Fix erroneous tabs

The QA script was reporting forbidden tabs in some of the lines in this
bug, these have now been removed.
Comment 26 Andrew Isherwood 2020-10-02 09:55:44 UTC
Revised reports:

1. Order line cancellations:
----------------------------
SELECT
    o.ordernumber AS 'Order number',
    bi.title AS 'Title',
    bi.author AS 'Author',
    ba.basketname AS 'Basket',
    f.budget_name AS 'Fund',
    CONCAT(b.firstname, ' ', b.surname) AS 'Cancelled by',
    a.timestamp AS 'Cancelled timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
INNER JOIN aqorders o ON a.object = o.ordernumber
INNER JOIN aqbasket ba ON o.basketno = ba.basketno
LEFT JOIN aqbudgets f ON o.budget_id = f.budget_id
LEFT JOIN biblio bi ON o.biblionumber = bi.biblionumber
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'CANCEL_ORDER' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>>

2. Order line creations:
---------------------
SELECT
    o.ordernumber AS 'Order number',
    ba.basketname AS 'Basket',
    bi.title AS 'Title',
    bi.author AS 'Author',
    f.budget_name AS 'Fund',
    CONCAT(b.firstname, ' ', b.surname) AS 'Created by',
    a.timestamp AS 'Created timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
INNER JOIN aqorders o ON a.object = o.ordernumber
INNER JOIN aqbasket ba ON o.basketno = ba.basketno
LEFT JOIN aqbudgets f ON o.budget_id = f.budget_id
LEFT JOIN biblio bi ON o.biblionumber = bi.biblionumber
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'CREATE_ORDER' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>>


3. Invoice adjustment additions:
--------------------------------
SELECT
    f.budget_name AS 'Fund',
    i.invoicenumber,
    FORMAT(JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.adjustment')), 2) AS 'Amount',
    ia.note AS 'Note',
    CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.adjustment')) = 0 THEN 'No' ELSE 'Yes' END AS 'Encumber open',
    av.lib AS 'Reason',
    CONCAT(b.firstname, ' ', b.surname) AS 'Created by',
    a.timestamp AS 'Created timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
INNER JOIN aqinvoice_adjustments ia ON a.object = ia.adjustment_id
INNER JOIN aqinvoices i ON i.invoiceid = ia.invoiceid
LEFT JOIN aqbudgets f ON JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_id')) = f.budget_id
LEFT JOIN authorised_values av ON av.category = 'ADJ_REASON' AND av.authorised_value = JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.reason'))
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'CREATE_INVOICE_ADJUSTMENT' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>>

4. Invoice adjustment amendments:
---------------------------------
SELECT
    i.invoicenumber AS 'Invoice number',
    f_before.budget_name AS 'Fund before',
    f_after.budget_name AS 'Fund after',
    FORMAT(JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.adjustment_old')), 2) AS 'Amount before',
    FORMAT(JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.adjustment')), 2) AS 'Amount after',
    ia.note AS 'Note',
    CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.encumber_open_old')) = 0 THEN 'No' ELSE 'Yes' END AS 'Encumber open before',
    CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.encumber_open')) = 0 THEN 'No' ELSE 'Yes' END AS 'Encumber open after',
    av_before.lib AS 'Reason before',
    av_after.lib AS 'Reason after',
    CONCAT(b.firstname, ' ', b.surname) AS 'Created by',
    a.timestamp AS 'Created timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
INNER JOIN aqinvoice_adjustments ia ON a.object = ia.adjustment_id
INNER JOIN aqinvoices i ON i.invoiceid = ia.invoiceid
LEFT JOIN aqbudgets f_before ON JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_id_old')) = f_before.budget_id
LEFT JOIN aqbudgets f_after ON JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_id')) = f_after.budget_id
LEFT JOIN authorised_values av_before ON av_before.category = 'ADJ_REASON' AND av_before.authorised_value = JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.reason_old'))
LEFT JOIN authorised_values av_after ON av_after.category = 'ADJ_REASON' AND av_after.authorised_value = JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.reason'))
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'UPDATE_INVOICE_ADJUSTMENT' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>>

5. Invoice adjustment deletions:
--------------------------------
SELECT
    i.invoicenumber AS 'Invoice number',
    f.budget_name AS 'Fund',
    FORMAT(JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.adjustment')), 2) AS 'Amount',
    CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.encumber_open')) = 0 THEN 'No' ELSE 'Yes' END AS 'Encumber open',
    av.lib AS 'Reason',
    CONCAT(b.firstname, ' ', b.surname) AS 'Deleted by',
    a.timestamp AS 'Deleted timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
INNER JOIN aqinvoices i ON i.invoiceid = JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.invoiceid'))
LEFT JOIN aqbudgets f ON JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_id')) = f.budget_id
LEFT JOIN authorised_values av ON av.category = 'ADJ_REASON' AND av.authorised_value = JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.reason'))
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'DELETE_INVOICE_ADJUSTMENT' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>>

6. Order line receipts against an invoice
-----------------------------------------
SELECT
    o.ordernumber AS 'Order number',
    FORMAT(JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.quantityrec')), 0) AS 'Quantity received',
    f.budget_name AS 'Fund',
    FORMAT(JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.tax_rate')), 2) AS 'Tax rate',
    FORMAT(JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.replacementprice')), 2) AS 'Replacement price',
    FORMAT(JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.unitprice')), 2) AS 'Actual cost',
    CONCAT(b.firstname, ' ', b.surname) AS 'Received by',
    a.timestamp AS 'Received timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
INNER JOIN aqorders o ON a.object = o.ordernumber
LEFT JOIN aqbudgets f ON JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.bookfund')) = f.budget_id
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'RECEIVE_ORDER' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>>

7. Budget Adjustments
---------------------
SELECT
    JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_period_startdate_old')) AS 'Budget start before',
    JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_period_startdate')) AS 'Budget start after',
    JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_period_enddate_old')) AS 'Budget end before',
    JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_period_enddate')) AS 'Budget end after',
    JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_period_total_old')) AS 'Total amount before',
    JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_period_total')) AS 'Total amount after',
    JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_period_total_change')) AS 'Difference',
    CONCAT(b.firstname, ' ', b.surname) AS 'Modified by',
    a.timestamp AS 'Modified timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'MODIFY_BUDGET' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>>

8. Fund adjustments
-------------------
SELECT
    a.object AS 'Fund ID',
    JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_amount_old')) AS 'Amount before',
    JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_amount_new')) AS 'Amount after',
	JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_encumb_old')) AS 'Warn at % before',
	JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_encumb_new')) AS 'Warn at % after',
    JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_expend_old')) AS 'Warn at amount before',
    JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_expend_new')) AS 'Warn at amount after',
    JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_amount_change')) AS 'Difference',
    CONCAT(b.firstname, ' ', b.surname) AS 'Modified by',
    a.timestamp AS 'Modified timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'MODIFY_FUND' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>>

9. Order release date (1)
-------------------------
SELECT
    ba.basketname AS "Basket name",
    bs.name AS "Vendor name",
    bi.title AS 'Title',
    bi.author AS 'Author',
    o.quantity AS 'Quantity',
    FORMAT(o.listprice, 2) AS 'Vendor price',
    FORMAT(o.rrp, 2) AS 'Retail price',
    FORMAT(o.ecost, 2) AS 'Budgeted cost',
    FORMAT(o.unitprice, 2) AS 'Actual cost',
    bu.budget_name AS 'Fund',
    CONCAT(b.firstname, ' ', b.surname) AS 'Basket closed by',
    a.timestamp AS 'Basket closed timestamp'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
INNER JOIN aqbasket ba ON a.object = ba.basketno
INNER JOIN aqorders o ON o.basketno = ba.basketno
INNER JOIN aqbooksellers bs ON ba.booksellerid = bs.id
LEFT JOIN aqbudgets bu ON bu.budget_id = o.budget_id
LEFT JOIN biblio bi ON bi.biblionumber = o.biblionumber
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'CLOSE_BASKET' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>> AND
    a.object = <<Basket ID>>

10. Order release date (2):
---------------------------
SELECT
    o.ordernumber AS 'Order number',
    b.basketname AS 'Basket',
    bu.budget_name AS 'Fund',
    bi.title AS 'Title',
    bi.author AS 'Author',
    CONCAT(bo.firstname, ' ', bo.surname) AS 'Sent by',
    e.transfer_date AS 'Sent date'
FROM
    edifact_messages e
LEFT JOIN aqbasket b ON e.basketno = b.basketno
LEFT JOIN aqorders o ON o.basketno = b.basketno
LEFT JOIN action_logs a ON e.basketno = a.object
LEFT JOIN biblio bi ON o.biblionumber = bi.biblionumber
LEFT JOIN borrowers bo ON a.user = bo.borrowernumber
INNER JOIN aqbudgets bu ON o.budget_id = bu.budget_id
WHERE
    e.message_type='ORDER' AND
    e.status = 'Sent' AND
    a.module = 'ACQUISITIONS' AND
    a.action = 'APPROVE_BASKET' AND
    date(e.transfer_date) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>> AND
    e.basketno = <<Basket ID>>
Comment 27 Andrew Isherwood 2020-10-02 09:56:55 UTC
Revised test plan:

Setup:
1. Apply patch and run database update if necessary

Order line cancellations:
1. Go to "Aquisitions", find an open basket with at least one order
2. Cancel the order and confirm the cancellation
3. From the staff main page, go to "Reports > Create from SQL"
4. Name the report "Order line cancellations"
5. In the SQL box, enter the report labelled "Order line cancellations" in comment #26 above
6. Save the report, the run it, choosing a start and end date
7. TEST => Observe that the order cancellation you just carried out is listed

Order line creations:
1. Go to "Aquisitions", find or create an open basket
2. Create a new order in the basket
3. From the staff main page, go to "Reports > Create from SQL"
4. Name the report "Order line creations"
5. In the SQL box, enter the report labelled "Order line creations" in comment #26 above
6. Save the report, the run it, choosing a start and end date
7. TEST => Observe that the order creation you just carried out is listed

Invoice adjustment additions:
1. Go to "Aquisitions", find or create an invoice
2. Click "Add an adjustment"
3. Fill in the details of the adjustment, then click "Update adjustments"
4. From the staff main page, go to "Reports > Create from SQL"
5. Name the report "Invoice adjustment additions"
6. In the SQL box, enter the report labelled "Invoice adjustment additions" in comment #26 above
7. Save the report, the run it, choosing a start and end date
8. TEST => Observe that the adjustment creation you just carried out is listed

Invoice adjustment amendments:
1. Go to "Aquisitions", find the invoice from the previous steps
2. Find the adjustment you added and modify it
3. Click "Update adjustments"
4. From the staff main page, go to "Reports > Create from SQL"
5. Name the report "Invoice adjustment amendments"
6. In the SQL box, enter the report labelled "Invoice adjustment amendments" in comment #26 above
7. Save the report, the run it, choosing a start and end date
8. TEST => Observe that the adjustment amendment you just carried out is listed

Invoice adjustment deletions:
1. Go to "Aquisitions", find the invoice from the previous steps
2. Find the adjustment you added and delete it
3. From the staff main page, go to "Reports > Create from SQL"
4. Name the report "Invoice adjustment deletions"
5. In the SQL box, enter the report labelled "Invoice adjustment deletions" in comment #26 above
6. Save the report, the run it, choosing a start and end date
7. TEST => Observe that the adjustment deletion you just carried out is listed

Order line receipts against an invoice:
1. Go to "Aquisitions", find an open invoice with unreceived items
2. Go to the "Receipt summary" page
3. Click "Receive" for an item
4. Fill in the data as appropriate, then click "Save"
5. From the staff main page, go to "Reports > Create from SQL"
6. Name the report "Order line receipts against an invoice"
7. In the SQL box, enter the report labelled "Order line receipts against an invoice" in comment #26 above
8. Save the report, the run it, choosing a start and end date
9. TEST => Observe that the receipt you just carried out is listed

Budget adjustments:
1. Go to "Aquisitions", find a budget
2. Click "Actions" > "Edit"
3. Modify some properties of the budget
4. Click "Save"
5. From the staff main page, go to "Reports > Create from SQL"
6. Name the report "Budget adjustments"
7. In the SQL box, enter the report labelled "Budget adjustments" in comment #26 above
8. Save the report, the run it, choosing a start and end date
9. TEST => Observe that the adjustment you just carried out is listed, including details of the "before" and "after" values

Fund adjustments:
1. Go to "Aquisitions", find a fund
2. Click "Actions" > "Edit"
3. Modify some properties of the fund
4. Click "Submit"
5. From the staff main page, go to "Reports > Create from SQL"
6. Name the report "Fund adjustments"
7. In the SQL box, enter the report labelled "Fund adjustments" in comment #26 above
8. Save the report, the run it, choosing a start and end date
9. TEST => Observe that the adjustment you just carried out is listed, including details of the "before" and "after" values

Order release date (1):
1. Go to "Aquisitions", find an open basket
2. Close the basket, noting it's ID
3. From the staff main page, go to "Reports > Create from SQL"
4. Name the report "Order release date (1)"
5. In the SQL box, enter the report labelled "Order release date (1)" in comment #26 above
6. Save the report, the run it, choosing a start and end date and basket ID
7. TEST => Observe that the details of the closed basket are listed

Order release date (2):
1. Release a basket via an EDI message, resulting in a row in edifact_messages table
3. From the staff main page, go to "Reports > Create from SQL"
4. Name the report "Order release date (2)"
5. In the SQL box, enter the report labelled "Order release date (2)" in comment #26 above
6. Save the report, the run it, choosing a start and end date and basket ID
7. TEST => Observe that the details of the released orders are listed
Comment 28 Katrin Fischer 2020-10-14 21:33:20 UTC
Currently blocked by dependent bug 23971 being Failed QA.
Comment 29 Andrew Isherwood 2020-10-19 10:01:49 UTC
Bug 23971 is no longer FQA, so setting this back to Signed Off
Comment 30 Katrin Fischer 2020-10-22 23:53:36 UTC
Waiting for the final QA on bug 23971.
Comment 31 Nick Clemens 2020-10-25 03:24:27 UTC
Hi Andrew,

Overall I like this, it adds useful information, but I wonder why the scope of information is limited for each object, it seems to create some issues for Budgets/Funds/Orders - if I modify a field that isn't logged a line is added to the log, but no info on what was changed is recorded, e.g.:
 - update order notes, change statistics
 - add user to budget, lock budget, make budget active, change name
 - change fund name, change statistics
Is the size concern reason enough to avoid logging the object unblessed?


Other notes:

Adding a new basket records two lines:
  Create an acquisitions basket 
  Modify an acquisitions basket header 

Cancelling receipt of an order is not logged

Creating a budget is logged as modification

Budget/funds record a change of amount because of decimal difference:
"budget_amount_new":"6.00"
"budget_amount_old":"6.000000"
Comment 32 Andrew Isherwood 2020-10-26 11:46:29 UTC
Created attachment 112509 [details] [review]
Bug 24190: Add acquisition logging

Signed-off-by: Maura Stephens <maura.stephens@gmit.ie>
Comment 33 Andrew Isherwood 2020-10-26 11:46:40 UTC
Created attachment 112510 [details] [review]
Bug 24190: (follow-up) Cancel order del item bug

If an order is cancelled but the associated bib / items are unable to be
removed, we go into error handling. We still need to log the
cancellation though. So this fix moves the logging to just after the
cancellation, so it will be logged regardless of the outcome with
associated things.

Signed-off-by: Maura Stephens <maura.stephens@gmit.ie>
Comment 34 Andrew Isherwood 2020-10-26 11:46:49 UTC
Created attachment 112511 [details] [review]
Bug 24190: (follow-up) Respond to QA feedback

This commit makes changes in response to Jonathan's feedback in comment

- Moved from using zero padded strings to store log data to a JSON
object
- Stopped storing formatted dates in logged data
Comment 35 Andrew Isherwood 2020-10-26 11:47:01 UTC
Created attachment 112512 [details] [review]
Bug 24190: (follow-up) Add tests

In response to Jonathan's request for tests, I have now added additional
tests for the methods in C4/Budgets.pm that do logging
Comment 36 Andrew Isherwood 2020-10-26 11:47:11 UTC
Created attachment 112513 [details] [review]
Bug 24190: (follow-up) Fix erroneous tabs

The QA script was reporting forbidden tabs in some of the lines in this
bug, these have now been removed.
Comment 37 Andrew Isherwood 2020-10-26 11:47:20 UTC
Created attachment 112514 [details] [review]
Bug 24190: (follow-up) Modify order of logging

Basket creation involves two steps in Koha, creating the basket then
modifying its header. We were logging these two steps in the wrong
order. This commit fixes that.
Comment 38 Andrew Isherwood 2020-10-26 11:47:37 UTC
(In reply to Nick Clemens from comment #31)

Hi Nick

Thanks for the feedback

> [..] I wonder why the scope
> of information is limited for each object
> if I modify a field that isn't logged a line is
> added to the log, but no info on what was changed is recorded, e.g.:
>  - update order notes, change statistics
>  - add user to budget, lock budget, make budget active, change name
>  - change fund name, change statistics
> Is the size concern reason enough to avoid logging the object unblessed?
The development was funded by a few customers who had very specific requirements for what they wanted logging, the patches address these requirements. As you know, acquisitions is a vast area and even relatively simple objects (for example, a fund) have many properties. We **could** just do what has been done in other areas and just dump the entire object into the log, but another key requirement for our customers was that they actually had to be able to create meaningful reports on the logged data. I have therefore been discriminating in what data is logged and how it is logged to enable them to do this. I hope that the approach I've taken will make it relatively trivial for others to extend this logging as and what required, but I felt it was more prudent to take a step by step approach rather than just logging everything.

> Adding a new basket records two lines:
>   Create an acquisitions basket 
>   Modify an acquisitions basket header 
This is actually reflecting what Koha does, it creates the basket then modifies the header (see: https://github.com/PTFS-Europe/koha/blob/master/C4/Acquisition.pm#L190-L207) however, I did notice that the logging of the basket creation was happening after the header had been modified, so I've corrected that.

> Cancelling receipt of an order is not logged
This was not on our customers list of requirements, hence has not been added.

> Creating a budget is logged as modification
I can't replicate this, budget creation isn't logged at all, for the reason given above.

> Budget/funds record a change of amount because of decimal difference:
> "budget_amount_new":"6.00"
> "budget_amount_old":"6.000000"
I'm just logging what Koha is using, it feels like it's asking for trouble to add or remove precision for the purpose of logging, particularly when those values will be used for reporting.
Comment 39 Nick Clemens 2021-01-27 14:06:39 UTC
(In reply to Andrew Isherwood from comment #38)
> (In reply to Nick Clemens from comment #31)
> 
> Hi Nick
> 
> Thanks for the feedback
> 
> > [..] I wonder why the scope
> > of information is limited for each object

> required, but I felt it was more prudent to take a step by step approach
> rather than just logging everything.

On bug 23971 it looks like the decision was to log the full object, I feel like the same approach applies here

> > Adding a new basket records two lines:
> This is actually reflecting what Koha does, it creates the basket then
> modifies the header (see:

OK

> > Cancelling receipt of an order is not logged
> This was not on our customers list of requirements, hence has not been added.

OK

I will retest the rest once this applies
Comment 40 Andrew Isherwood 2021-02-01 12:05:42 UTC
Created attachment 116148 [details] [review]
Bug 24190: Add acquisition logging

Signed-off-by: Maura Stephens <maura.stephens@gmit.ie>
Comment 41 Andrew Isherwood 2021-02-01 12:05:45 UTC
Created attachment 116149 [details] [review]
Bug 24190: (follow-up) Cancel order del item bug

If an order is cancelled but the associated bib / items are unable to be
removed, we go into error handling. We still need to log the
cancellation though. So this fix moves the logging to just after the
cancellation, so it will be logged regardless of the outcome with
associated things.

Signed-off-by: Maura Stephens <maura.stephens@gmit.ie>
Comment 42 Andrew Isherwood 2021-02-01 12:05:49 UTC
Created attachment 116150 [details] [review]
Bug 24190: (follow-up) Respond to QA feedback

This commit makes changes in response to Jonathan's feedback in comment

- Moved from using zero padded strings to store log data to a JSON
object
- Stopped storing formatted dates in logged data
Comment 43 Andrew Isherwood 2021-02-01 12:05:52 UTC
Created attachment 116151 [details] [review]
Bug 24190: (follow-up) Add tests

In response to Jonathan's request for tests, I have now added additional
tests for the methods in C4/Budgets.pm that do logging
Comment 44 Andrew Isherwood 2021-02-01 12:05:56 UTC
Created attachment 116152 [details] [review]
Bug 24190: (follow-up) Fix erroneous tabs

The QA script was reporting forbidden tabs in some of the lines in this
bug, these have now been removed.
Comment 45 Andrew Isherwood 2021-02-01 12:05:59 UTC
Created attachment 116153 [details] [review]
Bug 24190: (follow-up) Modify order of logging

Basket creation involves two steps in Koha, creating the basket then
modifying its header. We were logging these two steps in the wrong
order. This commit fixes that.
Comment 46 Andrew Isherwood 2021-02-01 12:06:57 UTC
Revised test plan:

Setup:
1. Apply patch and run database update if necessary
2. Enable AcqLog syspref

Order line cancellations:
1. Go to "Aquisitions", find an open basket with at least one order
2. Cancel the order and confirm the cancellation
3. From the staff main page, go to "Reports > Create from SQL"
4. Name the report "Order line cancellations"
5. In the SQL box, enter the report labelled "Order line cancellations" in comment #26 above
6. Save the report, the run it, choosing a start and end date
7. TEST => Observe that the order cancellation you just carried out is listed

Order line creations:
1. Go to "Aquisitions", find or create an open basket
2. Create a new order in the basket
3. From the staff main page, go to "Reports > Create from SQL"
4. Name the report "Order line creations"
5. In the SQL box, enter the report labelled "Order line creations" in comment #26 above
6. Save the report, the run it, choosing a start and end date
7. TEST => Observe that the order creation you just carried out is listed

Invoice adjustment additions:
1. Go to "Aquisitions", find or create an invoice
2. Click "Add an adjustment"
3. Fill in the details of the adjustment, then click "Update adjustments"
4. From the staff main page, go to "Reports > Create from SQL"
5. Name the report "Invoice adjustment additions"
6. In the SQL box, enter the report labelled "Invoice adjustment additions" in comment #26 above
7. Save the report, the run it, choosing a start and end date
8. TEST => Observe that the adjustment creation you just carried out is listed

Invoice adjustment amendments:
1. Go to "Aquisitions", find the invoice from the previous steps
2. Find the adjustment you added and modify it
3. Click "Update adjustments"
4. From the staff main page, go to "Reports > Create from SQL"
5. Name the report "Invoice adjustment amendments"
6. In the SQL box, enter the report labelled "Invoice adjustment amendments" in comment #26 above
7. Save the report, the run it, choosing a start and end date
8. TEST => Observe that the adjustment amendment you just carried out is listed

Invoice adjustment deletions:
1. Go to "Aquisitions", find the invoice from the previous steps
2. Find the adjustment you added and delete it
3. From the staff main page, go to "Reports > Create from SQL"
4. Name the report "Invoice adjustment deletions"
5. In the SQL box, enter the report labelled "Invoice adjustment deletions" in comment #26 above
6. Save the report, the run it, choosing a start and end date
7. TEST => Observe that the adjustment deletion you just carried out is listed

Order line receipts against an invoice:
1. Go to "Aquisitions", find an open invoice with unreceived items
2. Go to the "Receipt summary" page
3. Click "Receive" for an item
4. Fill in the data as appropriate, then click "Save"
5. From the staff main page, go to "Reports > Create from SQL"
6. Name the report "Order line receipts against an invoice"
7. In the SQL box, enter the report labelled "Order line receipts against an invoice" in comment #26 above
8. Save the report, the run it, choosing a start and end date
9. TEST => Observe that the receipt you just carried out is listed

Budget adjustments:
1. Go to "Aquisitions", find a budget
2. Click "Actions" > "Edit"
3. Modify some properties of the budget
4. Click "Save"
5. From the staff main page, go to "Reports > Create from SQL"
6. Name the report "Budget adjustments"
7. In the SQL box, enter the report labelled "Budget adjustments" in comment #26 above
8. Save the report, the run it, choosing a start and end date
9. TEST => Observe that the adjustment you just carried out is listed, including details of the "before" and "after" values

Fund adjustments:
1. Go to "Aquisitions", find a fund
2. Click "Actions" > "Edit"
3. Modify some properties of the fund
4. Click "Submit"
5. From the staff main page, go to "Reports > Create from SQL"
6. Name the report "Fund adjustments"
7. In the SQL box, enter the report labelled "Fund adjustments" in comment #26 above
8. Save the report, the run it, choosing a start and end date
9. TEST => Observe that the adjustment you just carried out is listed, including details of the "before" and "after" values

Order release date (1):
1. Go to "Aquisitions", find an open basket
2. Close the basket, noting it's ID
3. From the staff main page, go to "Reports > Create from SQL"
4. Name the report "Order release date (1)"
5. In the SQL box, enter the report labelled "Order release date (1)" in comment #26 above
6. Save the report, the run it, choosing a start and end date and basket ID
7. TEST => Observe that the details of the closed basket are listed

Order release date (2):
1. Release a basket via an EDI message, resulting in a row in edifact_messages table
3. From the staff main page, go to "Reports > Create from SQL"
4. Name the report "Order release date (2)"
5. In the SQL box, enter the report labelled "Order release date (2)" in comment #26 above
6. Save the report, the run it, choosing a start and end date and basket ID
7. TEST => Observe that the details of the released orders are listed
Comment 47 Andrew Isherwood 2021-02-01 12:08:05 UTC
(In reply to Nick Clemens from comment #39)

> I will retest the rest once this applies

Hi Nick - thanks so much for your efforts on this one! :-) I've now rebased it (having first rebased the bug it depends on). Hopefully this time it will work!
Comment 48 Nick Clemens 2021-03-19 17:46:43 UTC
I still don't like that there are columns not tracked. If the full object cannot be logged, then I would like to see modifications that create no tracked change are not logged, some of these are confusing:
 - I can change a budget name, or lock/unlock the budget and a modification with no changes is logged
 - Changing the fund for an order logs a modification but not the change

When receiving an order the bookfund is coming through blank. I am not a huge fan of the name 'bookfund' - we could be ordering anything here, I would stick with fund. It looks like this comes from the fact that not changing the fund on receipt sends the value ""

Several of the reports caused ISEs for me - I tested by simply checking the action logs full contents

Adding an order to a basket logs a basket modification, but I cannot see what changes

This is a super cool feature, I just think it needs a bit more to be production ready
Comment 49 Jonathan Druart 2021-04-21 12:28:59 UTC
Pref renamed AcquisitionLog on bug 23971, run
   % perl -p -i -e 's#AcqLog#AcquisitionLog#' **/*.t **/*.pl **/*.pm **/*.sql **/*.perl **/*.pref

To apply it here.
Comment 50 Andrew Isherwood 2021-06-16 13:29:31 UTC
Created attachment 122040 [details] [review]
Bug 24190: Add acquisition logging

Signed-off-by: Maura Stephens <maura.stephens@gmit.ie>
Comment 51 Andrew Isherwood 2021-06-16 13:29:36 UTC
Created attachment 122041 [details] [review]
Bug 24190: (follow-up) Cancel order del item bug

If an order is cancelled but the associated bib / items are unable to be
removed, we go into error handling. We still need to log the
cancellation though. So this fix moves the logging to just after the
cancellation, so it will be logged regardless of the outcome with
associated things.

Signed-off-by: Maura Stephens <maura.stephens@gmit.ie>
Comment 52 Andrew Isherwood 2021-06-16 13:29:40 UTC
Created attachment 122042 [details] [review]
Bug 24190: (follow-up) Respond to QA feedback

This commit makes changes in response to Jonathan's feedback in comment

- Moved from using zero padded strings to store log data to a JSON
object
- Stopped storing formatted dates in logged data
Comment 53 Andrew Isherwood 2021-06-16 13:29:44 UTC
Created attachment 122043 [details] [review]
Bug 24190: (follow-up) Add tests

In response to Jonathan's request for tests, I have now added additional
tests for the methods in C4/Budgets.pm that do logging
Comment 54 Andrew Isherwood 2021-06-16 13:29:48 UTC
Created attachment 122044 [details] [review]
Bug 24190: (follow-up) Modify order of logging

Basket creation involves two steps in Koha, creating the basket then
modifying its header. We were logging these two steps in the wrong
order. This commit fixes that.
Comment 55 Andrew Isherwood 2021-06-16 13:29:52 UTC
Created attachment 122045 [details] [review]
Bug 24190: (Follow-up) Rename AcqLog

As requested in comment #49, renamed uses of AcqLog to AcquisitionLog
Comment 56 Nick Clemens 2021-08-02 13:47:35 UTC
Created attachment 123385 [details] [review]
Bug 24190: Add acquisition logging

Signed-off-by: Maura Stephens <maura.stephens@gmit.ie>

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 57 Nick Clemens 2021-08-02 13:47:40 UTC
Created attachment 123386 [details] [review]
Bug 24190: (follow-up) Cancel order del item bug

If an order is cancelled but the associated bib / items are unable to be
removed, we go into error handling. We still need to log the
cancellation though. So this fix moves the logging to just after the
cancellation, so it will be logged regardless of the outcome with
associated things.

Signed-off-by: Maura Stephens <maura.stephens@gmit.ie>

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 58 Nick Clemens 2021-08-02 13:47:44 UTC
Created attachment 123387 [details] [review]
Bug 24190: (follow-up) Respond to QA feedback

This commit makes changes in response to Jonathan's feedback in comment

- Moved from using zero padded strings to store log data to a JSON
object
- Stopped storing formatted dates in logged data

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 59 Nick Clemens 2021-08-02 13:47:49 UTC
Created attachment 123388 [details] [review]
Bug 24190: (follow-up) Add tests

In response to Jonathan's request for tests, I have now added additional
tests for the methods in C4/Budgets.pm that do logging

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 60 Nick Clemens 2021-08-02 13:47:53 UTC
Created attachment 123389 [details] [review]
Bug 24190: (follow-up) Modify order of logging

Basket creation involves two steps in Koha, creating the basket then
modifying its header. We were logging these two steps in the wrong
order. This commit fixes that.

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 61 Nick Clemens 2021-08-02 13:47:57 UTC
Created attachment 123390 [details] [review]
Bug 24190: (follow-up) Rename AcqLog

As requested in comment #49, renamed uses of AcqLog to AcquisitionLog

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 62 Nick Clemens 2021-08-02 13:48:01 UTC
Created attachment 123391 [details] [review]
Bug 24190: (QA follow-up) record unchanged bookfund and fix typo

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 63 Nick Clemens 2021-08-02 13:49:29 UTC
Slight rebase and small follow-up to record when fund is not changed upon receipt.

While I would like to see fuller logging of objects these patches add more information and will help track acquisitions so I think further enhancements can be on follow up bugs.

Passing QA
Comment 64 Jonathan Druart 2021-09-21 17:35:28 UTC
Created attachment 125119 [details] [review]
Bug 24190: (follow-up) Rename AcqLog

As requested in comment #49, renamed uses of AcqLog to AcquisitionLog

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>

JD amended patch: replace one missing occurrence in Budgets.t
Comment 65 Jonathan Druart 2021-09-21 18:18:34 UTC
Pushed to master for 21.11, thanks to everybody involved!