[Koha-bugs] [Bug 9351] item type not recorded correctly in statistics for returns and some local use

[bugzilla-daemon at bugs.koha-community.org]

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=9351

--- Comment #1 from Galen Charlton <gmcharlt at gmail.com> ---
Fortunately, after the code is fixed, the statistics table can be readily
corrected.  The following two statements should do it when item-level item
types are in effect:

UPDATE statistics s
SET itemtype = (
  SELECT itype
  FROM items i
  WHERE i.itemnumber = s.itemnumber
)
WHERE itemtype IS NULL
AND type IN ('localuse', 'return')
AND itemnumber IN (SELECT itemnumber FROM items);

UPDATE statistics s
SET itemtype = (
  SELECT itype
  FROM deleteditems i
  WHERE i.itemnumber = s.itemnumber
)
WHERE itemtype IS NULL
AND type IN ('localuse', 'return')
AND itemnumber IN (SELECT itemnumber FROM deleteditems);

The following three statements should do it when bib-level item type are in
effect:

UPDATE statistics s
SET itemtype = (
  SELECT itemtype
  FROM (
    SELECT itemnumber, itemtype
    FROM items
    JOIN biblioitems USING (biblioitemnumber)
  ) i
  WHERE i.itemnumber = s.itemnumber
)
WHERE itemtype IS NULL
AND type IN ('localuse', 'return')
AND itemnumber IN (
  SELECT itemnumber 
  FROM items
  JOIN biblioitems USING (biblioitemnumber)
);

UPDATE statistics s
SET itemtype = (
  SELECT itemtype
  FROM (
    SELECT itemnumber, itemtype
    FROM deleteditems
    JOIN biblioitems USING (biblioitemnumber)
  ) i
  WHERE i.itemnumber = s.itemnumber
)
WHERE itemtype IS NULL
AND type IN ('localuse', 'return')
AND itemnumber IN (
  SELECT itemnumber 
  FROM deleteditems 
  JOIN biblioitems USING (biblioitemnumber)
);

UPDATE statistics s
SET itemtype = (
  SELECT itemtype
  FROM (
    SELECT itemnumber, itemtype
    FROM deleteditems
    JOIN deletedbiblioitems USING (biblioitemnumber)
  ) i
  WHERE i.itemnumber = s.itemnumber
)
WHERE itemtype IS NULL
AND type IN ('localuse', 'return')
AND itemnumber IN (
  SELECT itemnumber 
  FROM deleteditems 
  JOIN deletedbiblioitems USING (biblioitemnumber)
);

-- 
You are receiving this mail because:
You are watching all bug changes.