https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24190
--- Comment #26 from Andrew Isherwood <andrew.isherw...@ptfs-europe.com> --- 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>> -- You are receiving this mail because: You are watching all bug changes. _______________________________________________ Koha-bugs mailing list Koha-bugs@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/