-- ****************************************************************
-- * SQL-Statements to check integrity of the database stock data *
-- ****************************************************************


-- Compare count(ati.ati_serialno) with stkat.stkat_itemquantity
SELECT
    ati.at_id,
    ati.stk_id,
    count(ati.ati_serialno) AS num,
    stkat.stkat_quantity,
    stkat.stkat_itemquantity,
    at.at_name,
    stk.stk_name,
    stk.stk_path
  FROM
    articleitem AS ati,
    stockarticle AS stkat,
    article AS at,
    stock AS stk
  WHERE
    ati.at_id = stkat.at_id   AND
    ati.stk_id = stkat.stk_id AND
    at.at_id = stkat.at_id    AND
    at.at_serialno = '1'      AND
    stk.stk_id = stkat.stk_id
  GROUP BY ati.at_id, ati.stk_id
  HAVING (num != stkat.stkat_itemquantity)
  ORDER BY stk.stk_id, at.at_id
  LIMIT 0,1000


-- Get all rows in "articleitem" not existing in "stockarticle"
-- That is the special case from above where "stkat.stkat_itemquantity = 0",
-- because if it is "0" it will be removed in stockarticle.
-- Therefore these rows are not in the portfolio from the statement above
SELECT
    stk.stk_name,
    stk.stk_path,
    ati.stk_id,
    ati.at_id,
    ati.ati_serialno,
    at.at_name
  FROM
    articleitem AS ati,
    article AS at,
    stock AS stk
  WHERE
    ati.stk_id = stk.stk_id AND
    ati.at_id = at.at_id    AND
    at.at_serialno = '1'    AND
    CONCAT(ati.at_id,'_',ati.stk_id) NOT IN (SELECT CONCAT(at_id,'_',stk_id) FROM stockarticle)
  ORDER BY stk.stk_path
  LIMIT 0,1000


-- The same statement like before but output is a preparation for insert statements in "stockarticle"
SELECT
    CONCAT('INSERT INTO stockarticle (stk_id, at_id, stkat_maxquantity, stkat_quantity, stkat_itemquantity, stkat_modifytime) VALUES (',ati.stk_id,',',ati.at_id,',0,',COUNT(*),',',COUNT(*),',0000-00-00 00:00:00);')
  FROM
    articleitem AS ati,
    article AS at,
    stock AS stk
  WHERE
    ati.stk_id = stk.stk_id AND
    ati.at_id = at.at_id    AND
    at.at_serialno = '1'    AND
    CONCAT(ati.at_id,'_',ati.stk_id) NOT IN (SELECT CONCAT(at_id,'_',stk_id) FROM stockarticle)
  GROUP BY ati.stk_id,ati.at_id
  LIMIT 0,1000



-- Check for multiple entries with the same serial number
-- ACHTUNG: ABFRAGEZEIT KRITISCH !!!
SELECT
    ati.at_id,
    ati.stk_id,
    ati.ati_serialno,
    at.at_name,
    stk.stk_name
  FROM
    articleitem AS ati,
    article AS at,
    stock AS stk
  WHERE
    at.at_id = ati.at_id    AND
    stk.stk_id = ati.stk_id AND
    ati.ati_serialno IN (
      SELECT
          ati2.ati_serialno
        FROM
          articleitem AS ati2
        GROUP BY ati2.ati_serialno
        HAVING count(ati2.ati_serialno) > 1
    )
    ORDER BY ati.ati_serialno
    LIMIT 0,1000