|
Description
Andrew Isherwood
2019-12-09 12:17:25 UTC
Created attachment 96364 [details] [review] Bug 24190 - Add acquisition logging Created attachment 96369 [details] [review] Bug 24190 - Add acquisition logging 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>>
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>>
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 Created attachment 100243 [details] [review] Bug 24190 - Add acquisition logging Created attachment 105082 [details] [review] Bug 24190 - Add acquisition logging 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. Created attachment 106096 [details] [review] Bug 24190: Add acquisition logging 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. Created attachment 106098 [details] [review] Bug 24190: Add acquisition logging Signed-off-by: Maura Stephens <maura.stephens@gmit.ie> 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> Tested and signed off on PTFS-E sandboxes by Maura Stephens 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.
4. You should also consider adding tests 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 Please also check the comments! (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 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.
I gather you've spoken with Martin, I'm going to proceed with storing in JSON Created attachment 111080 [details] [review] Bug 24190: Add acquisition logging Signed-off-by: Maura Stephens <maura.stephens@gmit.ie> 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> 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 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 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. 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>>
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 Currently blocked by dependent bug 23971 being Failed QA. Bug 23971 is no longer FQA, so setting this back to Signed Off Waiting for the final QA on bug 23971. 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" Created attachment 112509 [details] [review] Bug 24190: Add acquisition logging Signed-off-by: Maura Stephens <maura.stephens@gmit.ie> 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> 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 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 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. 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. (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. (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 Created attachment 116148 [details] [review] Bug 24190: Add acquisition logging Signed-off-by: Maura Stephens <maura.stephens@gmit.ie> 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> 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 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 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. 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. 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 (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! 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 Pref renamed AcquisitionLog on bug 23971, run % perl -p -i -e 's#AcqLog#AcquisitionLog#' **/*.t **/*.pl **/*.pm **/*.sql **/*.perl **/*.pref To apply it here. Created attachment 122040 [details] [review] Bug 24190: Add acquisition logging Signed-off-by: Maura Stephens <maura.stephens@gmit.ie> 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> 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 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 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. Created attachment 122045 [details] [review] Bug 24190: (Follow-up) Rename AcqLog As requested in comment #49, renamed uses of AcqLog to AcquisitionLog 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> 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> 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> 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> 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> 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> Created attachment 123391 [details] [review] Bug 24190: (QA follow-up) record unchanged bookfund and fix typo Signed-off-by: Nick Clemens <nick@bywatersolutions.com> 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 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 Pushed to master for 21.11, thanks to everybody involved! |