
INSERT INTO PRODUCTS (ID, REFERENCE, CODE, NAME, CATEGORY, TAXCAT) 
   SELECT PRODUCTID, IF( TEXTVALUE='', PRODUCTID, TEXTVALUE),  IF( TEXTVALUE='', PRODUCTID, TEXTVALUE), CONCAT('**NEW PRODUCT',  IF( TEXTVALUE='', PRODUCTID, TEXTVALUE)), 'xxx999', '000' FROM STOCKCHANGES 
   WHERE CHANGETYPE = 6 AND CHANGES_PROCESSED = 0
   ON DUPLICATE KEY UPDATE PRODUCTS.ID=PRODUCTS.ID;

INSERT INTO STOCKCURRENT (LOCATION, PRODUCT, UNITS)
   SELECT LOCATION, PRODUCTID, 0.0 FROM STOCKCHANGES
      WHERE CHANGETYPE = 6 AND CHANGES_PROCESSED = 0;
   
UPDATE PRODUCTS P, STOCKCHANGES C SET P.NAME = C.TEXTVALUE 
   WHERE C.FIELD = 'NAME' AND P.ID = C.PRODUCTID
      AND C.CHANGES_PROCESSED = 0;
   
UPDATE PRODUCTS P, STOCKCHANGES C SET P.REFERENCE = C.TEXTVALUE 
   WHERE C.FIELD = 'REFERENCE' AND P.ID = C.PRODUCTID
         AND C.CHANGES_PROCESSED = 0;
   
UPDATE PRODUCTS P, STOCKCHANGES C SET P.CODE = C.TEXTVALUE 
   WHERE C.FIELD = 'CODE' AND P.ID = C.PRODUCTID
         AND C.CHANGES_PROCESSED = 0;
   
UPDATE PRODUCTS P, STOCKCHANGES C SET P.CATEGORY = C.TEXTVALUE 
   WHERE C.FIELD = 'CATEGORY' AND P.ID = C.PRODUCTID
         AND C.CHANGES_PROCESSED = 0;
   
UPDATE PRODUCTS P, STOCKCHANGES C SET P.TAXCAT = C.TEXTVALUE 
   WHERE C.FIELD = 'TAXCAT' AND P.ID = C.PRODUCTID
         AND C.CHANGES_PROCESSED = 0;
   
UPDATE PRODUCTS P, STOCKCHANGES C SET P.PRICEBUY = C.TEXTVALUE 
   WHERE C.FIELD = 'PRICEBUY' AND P.ID = C.PRODUCTID
         AND C.CHANGES_PROCESSED = 0;
         
UPDATE PRODUCTS P, STOCKCHANGES C SET P.PRICESELL = C.TEXTVALUE 
   WHERE C.FIELD = 'PRICESELL' AND P.ID = C.PRODUCTID
         AND C.CHANGES_PROCESSED = 0;

UPDATE PRODUCTS P, STOCKCHANGES C SET P.IMAGE = C.BLOBVALUE 
   WHERE C.FIELD = 'IMAGE' AND P.ID = C.PRODUCTID
         AND C.CHANGES_PROCESSED = 0;

INSERT INTO STOCKDIARY (ID, DATENEW, REASON, LOCATION, PRODUCT, UNITS, PRICE, APPUSER ) 
    SELECT C.ID, C.UPLOADTIME, IF( C.TEXTVALUE > 0, -7, -8 ), 
		C.LOCATION, C.PRODUCTID, C.TEXTVALUE, 0.0, C.USERNAME  
    FROM STOCKCHANGES C
    WHERE C.FIELD = 'QTY_INSTOCK' AND C.CHANGES_PROCESSED = 0 AND NOT C.CHANGETYPE = 1;

INSERT INTO STOCKDIARY (ID, DATENEW, REASON, LOCATION, PRODUCT, UNITS, PRICE, APPUSER ) 
    SELECT C.ID, C.UPLOADTIME, IF( S.UNITS + C.TEXTVALUE > 0, -7, -8 ), 
		C.LOCATION, C.PRODUCTID, S.UNITS + C.TEXTVALUE, 0.0, C.USERNAME 
    FROM STOCKCHANGES C LEFT JOIN STOCKCURRENT S 
        ON C.LOCATION = S.LOCATION AND C.PRODUCTID = S.PRODUCT
    WHERE C.FIELD = 'QTY_INSTOCK' AND  C.CHANGES_PROCESSED = 0 AND C.CHANGETYPE = 1;

UPDATE STOCKCURRENT S, STOCKCHANGES C SET S.UNITS = S.UNITS + C.TEXTVALUE 
   WHERE C.FIELD = 'QTY_INSTOCK' AND S.PRODUCT = C.PRODUCTID AND S.LOCATION = C.LOCATION
         AND C.CHANGES_PROCESSED = 0 AND C.CHANGETYPE = 1;
 
UPDATE STOCKCURRENT S, STOCKCHANGES C SET S.UNITS = C.TEXTVALUE 
   WHERE C.FIELD = 'QTY_INSTOCK' AND S.PRODUCT = C.PRODUCTID AND S.LOCATION = C.LOCATION
         AND C.CHANGES_PROCESSED = 0 AND NOT C.CHANGETYPE = 1;
       
INSERT INTO STOCKLEVEL (ID, LOCATION, PRODUCT, STOCKSECURITY, STOCKMAXIMUM )
 SELECT C.ID, C.LOCATION, C.PRODUCTID, 0.0, 0.0 
    FROM STOCKCHANGES C LEFT JOIN STOCKLEVEL S 
        ON C.LOCATION = S.LOCATION AND C.PRODUCTID = S.PRODUCT
   WHERE S.PRODUCT = null AND ( C.FIELD = 'QTY_MIN' OR C.FIELD = 'QTY_MAX' )
      AND CHANGES_PROCESSED = 0;
   
UPDATE STOCKLEVEL S, STOCKCHANGES C SET S.STOCKSECURITY = C.TEXTVALUE 
       WHERE C.FIELD = 'QTY_MIN' AND S.PRODUCT = C.PRODUCTID AND S.LOCATION = C.LOCATION
         AND C.CHANGES_PROCESSED = 0 AND NOT C.CHANGETYPE = 1;

UPDATE STOCKLEVEL S, STOCKCHANGES C SET S.STOCKMAXIMUM = C.TEXTVALUE 
       WHERE C.FIELD = 'QTY_MAX' AND S.PRODUCT = C.PRODUCTID AND S.LOCATION = C.LOCATION
         AND C.CHANGES_PROCESSED = 0 AND NOT C.CHANGETYPE = 1;

UPDATE STOCKCHANGES SET CHANGES_PROCESSED = 2 WHERE CHANGES_PROCESSED = 0;

