Prepare populararticles

PopularArticles

This call accepts 3 parameters: * Supplier (string, optional), the code of a supplier. * Period (integer) a period in days. * Kind (string) one of dead or popular.

The call fills PrintStock based on the following field map:

• Artcode goes to PrintStock.ARTCODE.
• QTY goes to PrintStock.QTYSTOCK.
• MQTY goes to PrintStock.QTYIN.
• LastDate goes to PrintStock.DateStock.

The preparation inserts the result of the following query in the printstock table if Kind is 'popular':

• The MinFillDate parameter is today minus the value passed in Period.

  SELECT
    A.ARTCODE,
    SUM(QTY) AS QTY,
    (ISNULL(SUM(QTY), 0)/:Period) AS MQTY,
    max(A.FILLDATE) AS LASTDATE
  FROM
    OUTDETAILS A
    INNER JOIN OUT C ON (A.AVIZNO=C.AVIZ)
  WHERE  
     ISNULL(A.TRANSACTIONTYPE, 'X') in ('ORD', 'KIT')
     AND (A.FILLDATE>=:MinFillDate)
  --   {CustomFilter}
  GROUP BY
    A.ARTCODE, B.NAME2
  HAVING
    (ISNULL(SUM(QTY), 0)/:Period)>1
  ORDER BY
    MQTY DESC
  

if Kind is 'dead' then the result of the following query is inserted in the printStock table:

• The MinFillDate parameter is today minus the value passed in Period.
• The OneYearAgo is today minus one year.

  SELECT
    A.ARTCODE,
    B.NAME2,
    SUM(CASE WHEN A.FILLDATE>=:MinFilllDate) THEN QTY ELSE 0 END) AS QTY,
    ( ISNULL(SUM(CASE WHEN A.FILLDATE>=:MinFillDate THEN QTY ELSE 0 END), 0)/:Period) AS MQTY,
    max(A.FILLDATE) AS LASTDATE
  FROM
    OUTDETAILS A
    INNER JOIN ARTICLE B ON (A.ARTCODE=B.NAME)
    INNER JOIN OUT C ON (A.AVIZNO=C.AVIZ)
  WHERE  
     (A.FILLDATE>=:OneYearAgo)
     AND ISNULL(A.TRANSACTIONTYPE, 'X') in ('ORD', 'KIT')
  --   {CustomFilter}
     AND
       A.ARTCODE in
         (Select distinct X.ARTCODE from STOCTOTAL X
          where X.ARTCODE=A.ARTCODE and X.QTYIN-isnull(X.QTYOUT, 0)>0 AND X.STOCKCODE IN ('75') )
  GROUP
    BY A.ARTCODE, B.NAME2
  HAVING
    (ISNULL(SUM(CASE WHEN A.FILLDATE>=:MinFillDate THEN QTY ELSE 0 END), 0)/:Period)<5
  ORDER BY
    MQTY asc,
    LastDate asc