GeMIS_WEB/GUI/Tools/ImportLager.p

DEFINE VARIABLE iSf    AS INTEGER   NO-UNDO.
DEFINE VARIABLE lFound AS LOGICAL   NO-UNDO.
DEFINE VARIABLE cFirma AS CHARACTER NO-UNDO INIT '1000'.

DEFINE TEMP-TABLE tHoReLager LIKE HoReLager
    FIELD lBenutzt AS LOG
    
    INDEX tHoReLager-k1 IS PRIMARY
    Firma
    Art
    Lager
    Regal
    Platz
    Niveau
    Sf
    INDEX tHoReLager-k3
    Firma
    Art
    Lager
    Artnr
    Inhalt
    Jahr
    .
DEFINE TEMP-TABLE tExcel
    FIELD cRegal    AS CHARACTER     /*  Spalte A  */
    FIELD cPlatz    AS CHARACTER     /*  Spalte B  */
    FIELD cNiveau   AS CHARACTER     /*  Spalte C  */
    FIELD iArtnr    AS INTEGER       /*  Spalte D  */
    FIELD cBez      AS CHARACTER     /*  Spalte E  */
    FIELD iJahr     AS INTEGER       /*  Spalte F  */
    FIELD AlkGehalt AS CHARACTER     /*  Spalte G  */
    FIELD cGebinde  AS CHARACTER     /*  Spalte H  */
    FIELD iBestand  AS CHARACTER     /*  Spalte I  */
    FIELD cSf       AS CHARACTER
    
    INDEX tExcel-k1 IS PRIMARY 
    cRegal
    cPlatz
    cNiveau
    cSf
    .

/* FOR EACH HoReLager:    */
/*     DELETE  HoReLager. */
/* END.                   */

DISABLE TRIGGERS FOR LOAD OF ArtLager.

FOR EACH HoReLager NO-LOCK 
    WHERE HoReLager.Firma = cFirma:
    
    CREATE  tHoReLager.
    BUFFER-COPY HoReLager TO tHoReLager
        ASSIGN  
        tHoReLager.lBenutzt = FALSE
        tHoReLager.Sf       = ''
        tHoReLager.iStatus  = 0.
    IF HoReLager.Artnr = 0 THEN NEXT.
    
    ASSIGN  
        tHoReLager.iStatus  = 1
        tHoReLager.lBenutzt = TRUE.
    
    FIND FIRST ArtLager OF HoReLager NO-ERROR.
    IF AVAILABLE ArtLager THEN ArtLager.Ort = ''.
END.

INPUT FROM 'C:\Entwicklung\TEMP\Huber\Lagerliste_20201228.csv' NO-MAP NO-CONVERT.
REPEAT TRANSACTION ON STOP UNDO, LEAVE ON ERROR UNDO, NEXT:
    CREATE  tExcel.
    IMPORT DELIMITER ';' tExcel.
END.
INPUT CLOSE.

FOR EACH tExcel
    WHERE tExcel.cRegal  <> ''
    AND   tExcel.cNiveau <> ''
    AND   tExcel.cPlatz  <> ''
    AND   tExcel.iArtnr   > 0
    :
              
    lFound = FALSE.
    FOR EACH tHoReLager USE-INDEX tHoReLager-k3
        WHERE tHoReLager.Firma  = cFirma
        AND   tHoReLager.Art    = 1
        AND   tHoReLager.Lager  = 0
        AND   tHoReLager.Artnr  = tExcel.iArtnr
        AND   tHoReLager.Regal  = tExcel.cRegal
        AND   tHoReLager.Platz  = tExcel.cPlatz
        AND   tHoReLager.Niveau = tExcel.cNiveau :
        lFound = TRUE.
        LEAVE.
    END.
    IF lFound THEN 
    DO:      /*  Artikel an diesem Platz bereits vorhanden   */
        tHoReLager.lBenutzt = TRUE.
        NEXT.
    END.
    
    FIND FIRST tHoReLager USE-INDEX tHoReLager-k3
        WHERE tHoReLager.Firma   = cFirma
        AND   tHoReLager.Art     = 1
        AND   tHoReLager.Lager   = 0
        AND   tHoReLager.Artnr   = tExcel.iArtnr NO-ERROR.
    IF AVAILABLE tHoReLager THEN 
    DO:        /*  Artikel an einem anderen Platz vorhanden    */
        DELETE  tHoReLager.
    END.

    CREATE  tHoReLager.
    ASSIGN  
        tHoReLager.Firma    = cFirma
        tHoReLager.Lager    = 0
        tHoReLager.Art      = 1
        tHoReLager.Regal    = tExcel.cRegal
        tHoReLager.Platz    = tExcel.cPlatz
        tHoReLager.Niveau   = tExcel.cNiveau
        tHoReLager.Artnr    = tExcel.iArtnr
        tHoReLager.Jahr     = tExcel.iJahr
        tHoReLager.iStatus  = (IF tExcel.iArtnr = 0 THEN 0 ELSE 1)
        tHoReLager.lBenutzt = TRUE.
    
    FIND FIRST tHoReLager
        WHERE tHoReLager.Firma   = cFirma
        AND   tHoReLager.Art     = 1
        AND   tHoReLager.Lager   = 0
        AND   tHoReLager.Regal   = tExcel.cRegal
        AND   tHoReLager.Platz   = tExcel.cPlatz
        AND   tHoReLager.Niveau  = tExcel.cNiveau NO-ERROR.
    IF lFound THEN 
    DO:
        IF tHoReLager.Sf = '' THEN 
        DO:
            ASSIGN  
                tHoReLager.Artnr    = tExcel.iArtnr
                tHoReLager.Jahr     = tExcel.iJahr
                tHoReLager.iStatus  = (IF tExcel.iArtnr = 0 THEN 0 ELSE 1)
                tHoReLager.lBenutzt = TRUE.
            NEXT.
        END.
    END.
    
    FIND FIRST tHoReLager
        WHERE tHoReLager.Firma   = cFirma
        AND   tHoReLager.Art     = 1
        AND   tHoReLager.Lager   = 0
        AND   tHoReLager.Regal   = tExcel.cRegal
        AND   tHoReLager.Platz   = tExcel.cPlatz
        AND   tHoReLager.Niveau  = tExcel.cNiveau NO-ERROR.
        
    IF NOT AVAILABLE tHoReLager THEN 
    DO:
        CREATE  tHoReLager.
        ASSIGN  
            tHoReLager.Firma    = cFirma
            tHoReLager.Lager    = 0
            tHoReLager.Art      = 1
            tHoReLager.Regal    = tExcel.cRegal
            tHoReLager.Platz    = tExcel.cPlatz
            tHoReLager.Niveau   = tExcel.cNiveau
            tHoReLager.Artnr    = tExcel.iArtnr
            tHoReLager.Jahr     = tExcel.iJahr
            tHoReLager.iStatus  = (IF tExcel.iArtnr = 0 THEN 0 ELSE 1)
            tHoReLager.lBenutzt = TRUE.
        NEXT.
    END.
    
    IF tHoReLager.Artnr = tExcel.iArtnr THEN NEXT.
        
    ASSIGN  
        tHoReLager.Artnr    = tExcel.iArtnr
        tHoReLager.Jahr     = tExcel.iJahr
        tHoReLager.iStatus  = (IF tExcel.iArtnr = 0 THEN 0 ELSE 1)
        tHoReLager.lBenutzt = TRUE.
END.

FOR EACH HoReLager
    WHERE HoReLager.Firma  = cFirma
    AND   HoReLager.Art    = 1
    AND   HoReLager.Lager  = 0:
    DELETE  HoReLager.
END.

FOR EACH tHoReLager
    BREAK BY tHoReLager.Regal
    BY tHoReLager.Platz
    BY tHoReLager.Niveau
    BY tHoReLager.Artnr:

    IF FIRST-OF ( tHoReLager.Niveau ) AND
        LAST-OF  ( tHoReLager.Niveau ) THEN 
    DO:
        CREATE  HoReLager.
        BUFFER-COPY tHoReLager
            EXCEPT  Sf  HoReLager_Id
            TO      HoReLager
            ASSIGN  
            HoReLager.Sf = ''.
        NEXT.
    END.   
    
    IF FIRST-OF ( tHoReLager.Niveau ) THEN iSf = 0.
    iSf = iSf + 1.
    CREATE  HoReLager.
    BUFFER-COPY tHoReLager
        EXCEPT  Sf  HoReLager_Id
        TO      HoReLager
        ASSIGN  
        HoReLager.Sf = STRING(iSf,'99').
END.

FOR EACH HoReLager
    WHERE HoReLager.Firma  = cFirma
    AND   HoReLager.Art    = 1
    AND   HoReLager.Lager  = 0:
    
    HoReLager.cSort = SUBSTITUTE('&1-&2-&3', HoReLager.Regal, HoReLager.Platz, HoReLager.Niveau).
    IF HoReLager.Sf <> '' THEN HoReLager.cSort = HoReLager.cSort + '-' + HoReLager.Sf.
    IF HoReLager.Artnr = 0 THEN NEXT.
    
    FOR EACH ArtLager
        WHERE ArtLager.Firma  = cFirma
        AND   ArtLager.Lager  = HoReLager.Lager
        AND   ArtLager.Artnr  = HoReLager.Artnr:
        ArtLager.Ort = HoReLager.cSort.
    END.
END.