GeMIS_WEB/GUI/g-excelbestellung.w

&ANALYZE-SUSPEND _VERSION-NUMBER AB_v10r12 GUI ADM2
&ANALYZE-RESUME
&Scoped-define WINDOW-NAME CURRENT-WINDOW
&Scoped-define FRAME-NAME gExcelbestellung
{adecomm/appserv.i}
&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _DEFINITIONS gExcelbestellung 
/*------------------------------------------------------------------------

  File: 

  Description: from cntnrdlg.w - ADM2 SmartDialog Template

  Input Parameters:
      <none>

  Output Parameters:
      <none>

  Author: 

  Created: 
------------------------------------------------------------------------*/
/*          This .W file was created with the Progress AppBuilder.      */
/*----------------------------------------------------------------------*/

/* Create an unnamed pool to store all the widgets created 
     by this procedure. This is a good default which assures
     that this procedure's triggers and internal procedures 
     will execute in this procedure's storage, and that proper
     cleanup will occur on deletion of the procedure. */

CREATE WIDGET-POOL.

/* ***************************  Definitions  ************************** */

/* Parameters Definitions ---                                           */

DEF INPUT  PARAMETER ipAufnr        AS INT          NO-UNDO.

/* Local Variable Definitions ---                                       */

DEF VAR htKunBest   AS HANDLE                   NO-UNDO.
DEF VAR excelAppl   AS COM-HANDLE               NO-UNDO.
DEF VAR iKnr        AS INT                      NO-UNDO.
DEF VAR iAufnr      AS INT                      NO-UNDO.

DEF TEMP-TABLE TExcel   FIELD Zeile         AS INT
                        FIELD Artnr         AS INT
                        FIELD Inhalt        AS INT
                        FIELD Jahr          AS INT
                        FIELD iGGeb         AS INT
                        FIELD iVGeb         AS INT
                        FIELD iKGeb         AS INT
                        
                        INDEX TExcel-k1 IS PRIMARY
                              Artnr
                              Inhalt
                              Jahr.
                              
DEF TEMP-TABLE tKunbest LIKE Kunbest.
DEF TEMP-TABLE tAufze   LIKE Aufze
    FIELD Knr           AS INT.

{ incl/dlgdefinition.i }

{ src/adm2/widgetprto.i }

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&ANALYZE-SUSPEND _UIB-PREPROCESSOR-BLOCK 

/* ********************  Preprocessor Definitions  ******************** */

&Scoped-define PROCEDURE-TYPE SmartDialog
&Scoped-define DB-AWARE no

&Scoped-define ADM-CONTAINER DIALOG-BOX

&Scoped-define ADM-SUPPORTED-LINKS Data-Target,Data-Source,Page-Target,Update-Source,Update-Target

/* Name of designated FRAME-NAME and/or first browse and/or first query */
&Scoped-define FRAME-NAME gExcelbestellung

/* Standard List Definitions                                            */
&Scoped-Define ENABLED-OBJECTS RECT-2 F_Knr F_Kunde F_Datei Btn_Datei ~
F_Artnr F_Inhalt F_Jahr F_GGebinde F_VGebinde F_KGebinde Btn_Vorschau ~
Btn_OK Btn_Cancel 
&Scoped-Define DISPLAYED-OBJECTS F_Knr F_Kunde F_Datei F_Artnr F_Inhalt ~
F_Jahr F_GGebinde F_VGebinde F_KGebinde 

/* Custom List Definitions                                              */
/* List-1,List-2,List-3,List-4,List-5,List-6                            */
&Scoped-define List-6 F_Knr F_Datei F_Artnr F_Inhalt F_Jahr F_GGebinde ~
F_VGebinde F_KGebinde 

/* _UIB-PREPROCESSOR-BLOCK-END */
&ANALYZE-RESUME


/* ************************  Function Prototypes ********************** */

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD createAuftrag gExcelbestellung 
FUNCTION createAuftrag RETURNS INTEGER
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD getFehlwerte gExcelbestellung 
FUNCTION getFehlwerte RETURNS LOGICAL
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD setFehlwerte gExcelbestellung 
FUNCTION setFehlwerte RETURNS LOGICAL
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


/* ***********************  Control Definitions  ********************** */

/* Define a dialog box                                                  */

/* Definitions of the field level widgets                               */
DEFINE BUTTON Btn_Cancel AUTO-END-KEY 
     LABEL "abbrechen" 
     SIZE 15 BY 1.

DEFINE BUTTON Btn_Datei 
     IMAGE-UP FILE "grafik/select.bmp":U
     IMAGE-DOWN FILE "grafik/select.bmp":U
     IMAGE-INSENSITIVE FILE "grafik/select.bmp":U
     LABEL "Button 1" 
     SIZE 5 BY 1.14.

DEFINE BUTTON Btn_OK 
     LABEL "OK" 
     SIZE 15 BY 1.

DEFINE BUTTON Btn_Vorschau 
     LABEL "Vorschau" 
     SIZE 15 BY 1.

DEFINE VARIABLE F_Artnr AS CHARACTER FORMAT "x(02)":U 
     LABEL "Spalte(n) Artikelnummer" 
     VIEW-AS FILL-IN NATIVE 
     SIZE 4 BY 1
     BGCOLOR 15  NO-UNDO.

DEFINE VARIABLE F_Datei AS CHARACTER FORMAT "X(256)":U 
     LABEL "Dateiname" 
     VIEW-AS FILL-IN NATIVE 
     SIZE 65 BY 1
     BGCOLOR 15  NO-UNDO.

DEFINE VARIABLE F_GGebinde AS CHARACTER FORMAT "x(02)":U 
     LABEL "Spalte(n) Menge G / V / KGeb" 
     VIEW-AS FILL-IN NATIVE 
     SIZE 4 BY 1
     BGCOLOR 15  NO-UNDO.

DEFINE VARIABLE F_Inhalt AS CHARACTER FORMAT "x(02)":U 
     LABEL "/" 
     VIEW-AS FILL-IN NATIVE 
     SIZE 4 BY 1
     BGCOLOR 15  NO-UNDO.

DEFINE VARIABLE F_Jahr AS CHARACTER FORMAT "x(02)":U 
     LABEL "/" 
     VIEW-AS FILL-IN NATIVE 
     SIZE 4 BY 1
     BGCOLOR 15  NO-UNDO.

DEFINE VARIABLE F_KGebinde AS CHARACTER FORMAT "x(02)":U 
     LABEL "/" 
     VIEW-AS FILL-IN NATIVE 
     SIZE 4 BY 1
     BGCOLOR 15  NO-UNDO.

DEFINE VARIABLE F_Knr AS INTEGER FORMAT "999999":U INITIAL 0 
     LABEL "Kunde" 
     VIEW-AS FILL-IN NATIVE 
     SIZE 11 BY 1
     BGCOLOR 15  NO-UNDO.

DEFINE VARIABLE F_Kunde AS CHARACTER FORMAT "X(256)":U 
     VIEW-AS FILL-IN NATIVE 
     SIZE 58 BY 1 NO-UNDO.

DEFINE VARIABLE F_VGebinde AS CHARACTER FORMAT "x(02)":U 
     LABEL "/" 
     VIEW-AS FILL-IN NATIVE 
     SIZE 4 BY 1
     BGCOLOR 15  NO-UNDO.

DEFINE RECTANGLE RECT-2
     EDGE-PIXELS 2 GRAPHIC-EDGE  NO-FILL   
     SIZE 88 BY 6.19.


/* ************************  Frame Definitions  *********************** */

DEFINE FRAME gExcelbestellung
     F_Knr AT ROW 1.52 COL 15 COLON-ALIGNED WIDGET-ID 18 NO-TAB-STOP 
     F_Kunde AT ROW 1.52 COL 27 COLON-ALIGNED NO-LABEL WIDGET-ID 20 NO-TAB-STOP 
     F_Datei AT ROW 3 COL 15 COLON-ALIGNED WIDGET-ID 4
     Btn_Datei AT ROW 3 COL 83 WIDGET-ID 16 NO-TAB-STOP 
     F_Artnr AT ROW 4.48 COL 33 COLON-ALIGNED WIDGET-ID 6
     F_Inhalt AT ROW 4.48 COL 41 COLON-ALIGNED WIDGET-ID 8
     F_Jahr AT ROW 4.48 COL 49 COLON-ALIGNED WIDGET-ID 10
     F_GGebinde AT ROW 5.48 COL 33 COLON-ALIGNED WIDGET-ID 24
     F_VGebinde AT ROW 5.48 COL 41 COLON-ALIGNED WIDGET-ID 12
     F_KGebinde AT ROW 5.48 COL 49 COLON-ALIGNED WIDGET-ID 14
     Btn_Vorschau AT ROW 9 COL 18 WIDGET-ID 22
     Btn_OK AT ROW 9 COL 39
     Btn_Cancel AT ROW 9 COL 60
     RECT-2 AT ROW 1.24 COL 2 WIDGET-ID 2
     SPACE(0.99) SKIP(3.56)
    WITH VIEW-AS DIALOG-BOX KEEP-TAB-ORDER 
         SIDE-LABELS NO-UNDERLINE THREE-D  SCROLLABLE 
         TITLE "Bestellung per ExcelDatei"
         CANCEL-BUTTON Btn_Cancel WIDGET-ID 100.


/* *********************** Procedure Settings ************************ */

&ANALYZE-SUSPEND _PROCEDURE-SETTINGS
/* Settings for THIS-PROCEDURE
   Type: SmartDialog
   Allow: Basic,Browse,DB-Fields,Query,Smart
   Container Links: Data-Target,Data-Source,Page-Target,Update-Source,Update-Target
   Design Page: 1
   Other Settings: COMPILE APPSERVER
 */
&ANALYZE-RESUME _END-PROCEDURE-SETTINGS

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _INCLUDED-LIB gExcelbestellung 
/* ************************* Included-Libraries *********************** */

{src/adm2/containr.i}

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME




/* ***********  Runtime Attributes and AppBuilder Settings  *********** */

&ANALYZE-SUSPEND _RUN-TIME-ATTRIBUTES
/* SETTINGS FOR DIALOG-BOX gExcelbestellung
   FRAME-NAME                                                           */
ASSIGN 
       FRAME gExcelbestellung:SCROLLABLE       = FALSE
       FRAME gExcelbestellung:HIDDEN           = TRUE.

/* SETTINGS FOR FILL-IN F_Artnr IN FRAME gExcelbestellung
   6                                                                    */
/* SETTINGS FOR FILL-IN F_Datei IN FRAME gExcelbestellung
   6                                                                    */
/* SETTINGS FOR FILL-IN F_GGebinde IN FRAME gExcelbestellung
   6                                                                    */
/* SETTINGS FOR FILL-IN F_Inhalt IN FRAME gExcelbestellung
   6                                                                    */
/* SETTINGS FOR FILL-IN F_Jahr IN FRAME gExcelbestellung
   6                                                                    */
/* SETTINGS FOR FILL-IN F_KGebinde IN FRAME gExcelbestellung
   6                                                                    */
/* SETTINGS FOR FILL-IN F_Knr IN FRAME gExcelbestellung
   6                                                                    */
ASSIGN 
       F_Knr:READ-ONLY IN FRAME gExcelbestellung        = TRUE.

ASSIGN 
       F_Kunde:READ-ONLY IN FRAME gExcelbestellung        = TRUE.

/* SETTINGS FOR FILL-IN F_VGebinde IN FRAME gExcelbestellung
   6                                                                    */
/* _RUN-TIME-ATTRIBUTES-END */
&ANALYZE-RESUME


/* Setting information for Queries and Browse Widgets fields            */

&ANALYZE-SUSPEND _QUERY-BLOCK DIALOG-BOX gExcelbestellung
/* Query rebuild information for DIALOG-BOX gExcelbestellung
     _Options          = "SHARE-LOCK"
     _Query            is NOT OPENED
*/  /* DIALOG-BOX gExcelbestellung */
&ANALYZE-RESUME

 



/* ************************  Control Triggers  ************************ */

&Scoped-define SELF-NAME gExcelbestellung
&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL gExcelbestellung gExcelbestellung
ON END-ERROR OF FRAME gExcelbestellung /* Bestellung per ExcelDatei */
DO:
    RUN ENDE.
    RETURN NO-APPLY.
END.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL gExcelbestellung gExcelbestellung
ON WINDOW-CLOSE OF FRAME gExcelbestellung /* Bestellung per ExcelDatei */
DO:  
    RUN ENDE.
    RETURN NO-APPLY.
END.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&Scoped-define SELF-NAME Btn_Cancel
&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL Btn_Cancel gExcelbestellung
ON CHOOSE OF Btn_Cancel IN FRAME gExcelbestellung /* abbrechen */
DO:
    RUN ENDE.
    RETURN NO-APPLY.
END.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&Scoped-define SELF-NAME Btn_Datei
&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL Btn_Datei gExcelbestellung
ON CHOOSE OF Btn_Datei IN FRAME gExcelbestellung /* Button 1 */
DO:

    DEF VAR lok     AS LOG                      NO-UNDO.
    DEF VAR cPath   AS CHAR                     NO-UNDO.
    DEF VAR cDatei  AS CHAR                     NO-UNDO.
    DEF VAR ii      AS INT                      NO-UNDO.

    DO WITH FRAME {&FRAME-NAME}:
        ASSIGN  F_Datei.
        IF F_Datei = '' THEN F_Datei = SUBSTITUTE('&1&2\Downloads\;A;B;C;D;E;',
                                       OS-GETENV("HOMEDRIVE"), OS-GETENV("HOMEPATH")).
        FILE-INFO:FILE-NAME = F_Datei NO-ERROR.

        IF FILE-INFO:FULL-PATHNAME  <> '' AND
           FILE-INFO:FULL-PATHNAME  <> ?  THEN DO:
            IF INDEX(FILE-INFO:FILE-TYPE, 'D') > 0 THEN DO:
                cPath   = F_Datei.
                F_Datei = FILE-INFO:FULL-PATHNAME.
            END.
            ELSE DO:
                cDatei = REPLACE(FILE-INFO:FULL-PATHNAME, '\', '/').
                ii = R-INDEX(cDatei, '/').
                IF ii = 0 THEN cPath = './'.
                ELSE           cPath = SUBSTRING(cDatei,01,ii - 1) NO-ERROR.
            END.
        END.
        SYSTEM-DIALOG   GET-FILE F_Datei
                        FILTERS "Excel" "*.xls, *.xlsx"
                        INITIAL-FILTER 1
                        INITIAL-DIR cPath
                        TITLE "Name der Bestelldatei"
                        USE-FILENAME 
                        UPDATE lok.
        IF NOT lok THEN RETURN NO-APPLY.
        
        FILE-INFO:FILE-NAME = F_Datei.
        IF FILE-INFO:FULL-PATHNAME = ?  OR
           FILE-INFO:FULL-PATHNAME = '' THEN RETURN NO-APPLY.
           
        DISPLAY F_Datei.
        APPLY 'ENTRY' TO F_Datei.
        RETURN NO-APPLY.
    END.
    
END.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&Scoped-define SELF-NAME Btn_OK
&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL Btn_OK gExcelbestellung
ON CHOOSE OF Btn_OK IN FRAME gExcelbestellung /* OK */
DO:
    DO WHILE TRUE ON ERROR UNDO, RETURN: 
        RUN EINLESEN_EXCEL.
        IF RETURN-VALUE = 'ERROR' THEN RETURN NO-APPLY.
        LEAVE.
    END.
    
    BtnOK = TRUE.
    RUN ENDE.
    RETURN NO-APPLY.
END.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&Scoped-define SELF-NAME Btn_Vorschau
&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL Btn_Vorschau gExcelbestellung
ON CHOOSE OF Btn_Vorschau IN FRAME gExcelbestellung /* Vorschau */
DO:
    DO WHILE TRUE ON ERROR UNDO, RETURN: 
        RUN OEFFNEN_EXCEL.
        IF RETURN-VALUE = 'ERROR' THEN RETURN NO-APPLY.
        LEAVE.
    END.

    APPLY 'ENTRY' TO F_Artnr.
    RETURN NO-APPLY.
END.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&Scoped-define SELF-NAME F_Knr
&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL F_Knr gExcelbestellung
ON ALT-F OF F_Knr IN FRAME gExcelbestellung /* Kunde */
DO:
    RUN FINDEN.
    APPLY 'ENTRY' TO SELF.
    RETURN NO-APPLY.
END.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL F_Knr gExcelbestellung
ON CTRL-N OF F_Knr IN FRAME gExcelbestellung /* Kunde */
DO:
    RUN FINDEN_NUMMER.
    APPLY 'ENTRY' TO SELF.
    RETURN NO-APPLY.
END.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL F_Knr gExcelbestellung
ON LEAVE OF F_Knr IN FRAME gExcelbestellung /* Kunde */
DO:
    ASSIGN  {&List-6}.
    FIND Adresse NO-LOCK
            WHERE Adresse.Firma = AdFirma
            AND   Adresse.Knr   = F_Knr NO-ERROR.
    IF NOT AVAILABLE Adresse THEN DO:
        APPLY 'ENTRY' TO SELF.
        RETURN NO-APPLY.
    END.
    DO WHILE TRUE WITH FRAME {&FRAME-NAME}:
        ASSIGN  F_Knr   = Adresse.Knr
                F_Kunde = Adresse.Anzeig_Br.
        DISPLAY F_Knr   F_Kunde.
        LEAVE.
    END.
END.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&UNDEFINE SELF-NAME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _MAIN-BLOCK gExcelbestellung 


/* ***************************  Main Block  *************************** */

    { incl/dlgmainblock.i }

    {src/adm2/dialogmn.i}

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


/* **********************  Internal Procedures  *********************** */

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE adm-create-objects gExcelbestellung  _ADM-CREATE-OBJECTS
PROCEDURE adm-create-objects :
/*------------------------------------------------------------------------------
  Purpose:     Create handles for all SmartObjects used in this procedure.
               After SmartObjects are initialized, then SmartLinks are added.
  Parameters:  <none>
------------------------------------------------------------------------------*/

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE disable_UI gExcelbestellung  _DEFAULT-DISABLE
PROCEDURE disable_UI :
/*------------------------------------------------------------------------------
  Purpose:     DISABLE the User Interface
  Parameters:  <none>
  Notes:       Here we clean-up the user-interface by deleting
               dynamic widgets we have created and/or hide 
               frames.  This procedure is usually called when
               we are ready to "clean-up" after running.
------------------------------------------------------------------------------*/
  /* Hide all frames. */
  HIDE FRAME gExcelbestellung.
END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE EINLESEN_EXCEL gExcelbestellung 
PROCEDURE EINLESEN_EXCEL :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/

    DEF VAR cDokument   AS CHAR                     NO-UNDO.
    DEF VAR cAktuell    AS CHAR                     NO-UNDO.

    DO WITH FRAME {&FRAME-NAME}:
        ASSIGN  {&List-6}.
        
        FILE-INFO:FILE-NAME = F_Datei.
        IF FILE-INFO:FULL-PATHNAME = ?          OR
           FILE-INFO:FULL-PATHNAME = ''         OR
           INDEX(FILE-INFO:FILE-TYPE, 'D') > 0  THEN DO:
            MESSAGE 'ungültiger Dateiname'
                VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.
            APPLY 'ENTRY' TO F_Datei.
            RETURN 'ERROR'.
        END.
        F_Datei  = FILE-INFO:FULL-PATHNAME.
        cAktuell = REPLACE(F_Datei, '\', '/').
        cAktuell = ENTRY(NUM-ENTRIES(cAktuell, '/'), cAktuell, '/').

        IF F_Artnr = '' THEN DO:
            MESSAGE 'Spalte für Artikelnummer zwingend'
                VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.
            APPLY 'ENTRY' TO F_Artnr.
            RETURN 'ERROR'.
        END.
        IF F_GGebinde = '' AND
           F_VGebinde = '' AND
           F_KGebinde = '' THEN DO:
            MESSAGE 'Spalte für Verkaufgebinde und/oder Kleingebinde ist zwingend'
                VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.
            APPLY 'ENTRY' TO F_VGebinde.
            RETURN 'ERROR'.
        END.
    END.

    DO WHILE TRUE:
        IF VALID-HANDLE(excelAppl) THEN DO:
            cDokument = excelAppl:ActiveWorkbook:NAME NO-ERROR.
            IF cDokument = ? THEN cDokument = ''.
            LEAVE.
        END.
        CREATE "Excel.Application" excelAppl CONNECT NO-ERROR.
        IF VALID-HANDLE(excelAppl) THEN LEAVE.

        CREATE "Excel.Application" excelAppl NO-ERROR.
        LEAVE.
    END.
    
    IF NOT VALID-HANDLE(excelAppl) THEN DO:
        MESSAGE 'Excel konnte nicht gestartet werden'
            VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.
        RETURN.
    END.

    IF cAktuell <> cDokument THEN 
        excelAppl:Workbooks:Open(F_Datei, 0, FALSE, 4, "", "", TRUE, , ";", ,).
    excelAppl:Visible = TRUE.

    DEF VAR ii      AS INT                      NO-UNDO.
    DEF VAR cZelle  AS CHAR                     NO-UNDO.
    DEF VAR iEmpty  AS INT                      NO-UNDO.
    DEF VAR cValue  AS CHAR                     NO-UNDO.
    DEF VAR iArtnr  AS INT                      NO-UNDO.
    DEF VAR iInhalt AS INT                      NO-UNDO.
    DEF VAR iJahr   AS INT                      NO-UNDO.
    DEF VAR iGGeb   AS INT                      NO-UNDO.
    DEF VAR iVGeb   AS INT                      NO-UNDO.
    DEF VAR iKGeb   AS INT                      NO-UNDO.
    
    ii     = 0.
    iEmpty = 0.
    EMPTY TEMP-TABLE tKunBest.
    EMPTY TEMP-TABLE tExcel.
    
    DO WHILE TRUE:
        ii = ii + 1.
        ASSIGN  iArtnr  = 0
                iInhalt = 0
                iJahr   = 0
                iVGeb   = 0
                iKGeb   = 0.
        cZelle = F_Artnr + STRING(ii).
        excelAppl:Range(cZelle):Select.
        cValue = excelAppl:ActiveCell:Value.
        IF cValue = ?  OR
           cValue = '' THEN cValue = excelAppl:ActiveCell:FormulaR1C1.
        IF cValue = '' OR
           cValue = ? THEN DO:
            iEmpty = iEmpty + 1.
            IF iEmpty >= 3 THEN LEAVE.
            NEXT.
        END.
        iArtnr = INTEGER(cValue) NO-ERROR.
        IF ERROR-STATUS:ERROR THEN NEXT.
        IF F_Inhalt <> '' THEN DO:
            cZelle = F_Inhalt + STRING(ii).
            excelAppl:Range(cZelle):Select.
            cValue = excelAppl:ActiveCell:Value.
            IF cValue = ?  OR
               cValue = '' THEN cValue = excelAppl:ActiveCell:FormulaR1C1.
            iInhalt = INTEGER(cValue) NO-ERROR.
            IF ERROR-STATUS:ERROR THEN NEXT.
        END.
        IF F_Jahr <> '' THEN DO:
            cZelle = F_Jahr + STRING(ii).
            excelAppl:Range(cZelle):Select.
            cValue = excelAppl:ActiveCell:Value.
            IF cValue = ?  OR
               cValue = '' THEN cValue = excelAppl:ActiveCell:FormulaR1C1.
            iJahr = INTEGER(cValue) NO-ERROR.
            IF ERROR-STATUS:ERROR THEN NEXT.
        END.
        IF F_GGebinde <> '' THEN DO:
            cZelle = F_GGebinde + STRING(ii).
            excelAppl:Range(cZelle):Select.
            cValue = excelAppl:ActiveCell:Value.
            IF cValue = ?  OR
               cValue = '' THEN cValue = excelAppl:ActiveCell:FormulaR1C1.
            iGGeb = INTEGER(cValue) NO-ERROR.
            IF ERROR-STATUS:ERROR THEN NEXT.
        END.
        IF F_VGebinde <> '' THEN DO:
            cZelle = F_VGebinde + STRING(ii).
            excelAppl:Range(cZelle):Select.
            cValue = excelAppl:ActiveCell:Value.
            IF cValue = ?  OR
               cValue = '' THEN cValue = excelAppl:ActiveCell:FormulaR1C1.
            iVGeb = INTEGER(cValue) NO-ERROR.
            IF ERROR-STATUS:ERROR THEN NEXT.
        END.
        IF F_KGebinde <> '' THEN DO:
            cZelle = F_KGebinde + STRING(ii).
            excelAppl:Range(cZelle):Select.
            cValue = excelAppl:ActiveCell:Value.
            IF cValue = ?  OR
               cValue = '' THEN cValue = excelAppl:ActiveCell:FormulaR1C1.
            iKGeb = INTEGER(cValue) NO-ERROR.
            IF ERROR-STATUS:ERROR THEN NEXT.
        END.
        
        IF iGGeb = 0 AND
           iVGeb = 0 AND
           iKGeb = 0 THEN NEXT.
        
        FIND Artst NO-LOCK
                WHERE Artst.Firma  = Firma
                AND   Artst.Artnr  = iArtnr
                AND   Artst.Inhalt = iInhalt
                AND   Artst.Jahr   = iJahr NO-ERROR.
        IF NOT AVAILABLE Artst THEN NEXT.
                
        CREATE  tExcel.
        ASSIGN  tExcel.Zeile  = ii
                tExcel.Artnr  = iArtnr
                tExcel.Inhalt = iInhalt
                tExcel.Jahr   = iJahr
                tExcel.iGGeb  = iGGeb
                tExcel.iVGeb  = iVGeb
                tExcel.iKGeb  = iKGeb.
                
        cZelle = STRING(ii) + ':' + STRING(ii).
        excelAppl:Rows(cZelle):Select.
        excelAppl:Selection:Interior:ColorIndex = 4.
        excelAppl:Selection:Interior:Pattern    = 1.
    END.
    
    ii = 0.
    FOR EACH tExcel:
        FIND Artst NO-LOCK
                WHERE Artst.Firma  = Firma
                AND   Artst.Artnr  = tExcel.Artnr
                AND   Artst.Inhalt = tExcel.Inhalt
                AND   Artst.Jahr   = tExcel.Jahr NO-ERROR.
        FIND GGebinde NO-LOCK
                WHERE GGebinde.Firma  = Artst.Firma
                AND   GGebinde.Geb_Cd = Artst.GGeb_Cd NO-ERROR.
        IF NOT AVAILABLE GGebinde THEN DO:
            cZelle = STRING(tExcel.Zeile) + ':' + STRING(tExcel.Zeile).
            excelAppl:Rows(cZelle):Select.
            excelAppl:Selection:Interior:ColorIndex = 6.
            excelAppl:Selection:Interior:Pattern    = 1.
            NEXT.
        END.
        FIND VGebinde NO-LOCK
                WHERE VGebinde.Firma  = Artst.Firma
                AND   VGebinde.Geb_Cd = Artst.VGeb_Cd NO-ERROR.
        IF NOT AVAILABLE VGebinde THEN DO:
            cZelle = STRING(tExcel.Zeile) + ':' + STRING(tExcel.Zeile).
            excelAppl:Rows(cZelle):Select.
            excelAppl:Selection:Interior:ColorIndex = 6.
            excelAppl:Selection:Interior:Pattern    = 1.
            NEXT.
        END.
        FIND KGebinde NO-LOCK
                WHERE KGebinde.Firma  = Artst.Firma
                AND   KGebinde.Geb_Cd = Artst.KGeb_Cd NO-ERROR.
        IF NOT AVAILABLE KGebinde THEN DO:
            cZelle = STRING(tExcel.Zeile) + ':' + STRING(tExcel.Zeile).
            excelAppl:Rows(cZelle):Select.
            excelAppl:Selection:Interior:ColorIndex = 6.
            excelAppl:Selection:Interior:Pattern    = 1.
            NEXT.
        END.
        
        IF tExcel.iGGeb > 0 THEN tExcel.iVGeb = tExcel.iGGeb * GGebinde.Inhalt.
        IF tExcel.iVGeb > 0 THEN tExcel.iKGeb = tExcel.iVGeb * VGebinde.Inhalt.
        
        ii = ii + 1.
        CREATE  tKunBest.
        ASSIGN  tKunBest.Firma     = Firma
                tKunBest.Knr       = iKnr
                tKunBest.Pos       = tExcel.Zeile
                tKunBest.Artnr     = Artst.Artnr
                tKunBest.Inhalt    = Artst.Inhalt
                tKunBest.Jahr      = Artst.jahr
                tKunBest.Best      = 0
                tKunBest.GGeb_Cd   = Artst.GGeb_Cd
                tKunBest.GGeb_Me   = 0
                tKunBest.VGeb_Cd   = Artst.VGeb_Cd
                tKunBest.VGeb_Me   = tExcel.iVGeb
                tKunBest.KGeb_Cd   = Artst.KGeb_Cd
                tKunBest.KGeb_Me   = tExcel.iKGeb
                tKunBest.Lag_Buch  = Artst.Lager
                tKunBest.Soll      = 0.
        IF tKunBest.KGeb_Me = 0 THEN DO:
            tKunBest.KGeb_Me = tKunBest.VGeb_Me * VGebinde.Inhalt.
            NEXT.
        END.
        IF tKunBest.VGeb_Me > 0 THEN NEXT.
        IF (tKunBest.KGeb_Me MOD VGebinde.Inhalt) = 0 THEN DO:
            tKunBest.VGeb_Me = tKunBest.KGeb_Me / VGebinde.Inhalt.
            NEXT.
        END.
        tKunBest.KGeb_Me = tKunbest.KGeb_Me - (tKunBest.KGeb_Me MOD VGebinde.Inhalt).
        tKunBest.VGeb_Me = tKunBest.KGeb_Me / VGebinde.Inhalt.
    END.

    RELEASE OBJECT excelAppl.
    
    ii = DYNAMIC-FUNCTION('createAuftrag':U IN THIS-PROCEDURE) NO-ERROR.
    MESSAGE SUBSTITUTE('Es wurden &1 Auftragszeilen erstellt', ii)
        VIEW-AS ALERT-BOX.
    
END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE enableObject gExcelbestellung 
PROCEDURE enableObject :
/*------------------------------------------------------------------------------
  Purpose:     Super Override
  Parameters:  
  Notes:       
------------------------------------------------------------------------------*/

    F_Knr = 0.
    FIND Aufko NO-LOCK
            WHERE Aufko.Firma = Firma
            AND   Aufko.Aufnr = ipAufnr NO-ERROR.
    IF AVAILABLE Aufko THEN DO:
        F_Knr  = Aufko.Knr.
        iKnr   = Aufko.Knr.
        iAufnr = Aufko.Aufnr.
        FIND Adresse NO-LOCK
                WHERE Adresse.Firma = AdFirma
                AND   Adresse.Knr   = F_Knr NO-ERROR.
        F_Kunde = Adresse.Anzeig_Br.
    END.
    
    DYNAMIC-FUNCTION('getFehlwerte':U) NO-ERROR.
  
    { incl/dlgenableobject.i }

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE enable_UI gExcelbestellung  _DEFAULT-ENABLE
PROCEDURE enable_UI :
/*------------------------------------------------------------------------------
  Purpose:     ENABLE the User Interface
  Parameters:  <none>
  Notes:       Here we display/view/enable the widgets in the
               user-interface.  In addition, OPEN all queries
               associated with each FRAME and BROWSE.
               These statements here are based on the "Other 
               Settings" section of the widget Property Sheets.
------------------------------------------------------------------------------*/
  DISPLAY F_Knr F_Kunde F_Datei F_Artnr F_Inhalt F_Jahr F_GGebinde F_VGebinde 
          F_KGebinde 
      WITH FRAME gExcelbestellung.
  ENABLE RECT-2 F_Knr F_Kunde F_Datei Btn_Datei F_Artnr F_Inhalt F_Jahr 
         F_GGebinde F_VGebinde F_KGebinde Btn_Vorschau Btn_OK Btn_Cancel 
      WITH FRAME gExcelbestellung.
  VIEW FRAME gExcelbestellung.
  {&OPEN-BROWSERS-IN-QUERY-gExcelbestellung}
END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE ENDE gExcelbestellung 
PROCEDURE ENDE :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/

    IF btnOK THEN DO:
        DYNAMIC-FUNCTION('setFehlwerte':U) NO-ERROR.
    END.

    { incl/dlgende.i }

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE FINDEN gExcelbestellung 
PROCEDURE FINDEN :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/

    DEF VAR iRecid      AS RECID                    NO-UNDO.
    DEF VAR cRetVal     AS CHAR                     NO-UNDO.
    DEF VAR cSection    AS CHAR                     NO-UNDO.
    DEF VAR cKey        AS CHAR                     NO-UNDO.

    RUN 'g-suchen-adresse.w':U ( '', OUTPUT iRecid ) NO-ERROR.
    IF iRecid = ? OR
       iRecid = 0 THEN RETURN.
       
    FIND Adresse NO-LOCK WHERE RECID(Adresse) = iRecid.
    DO WHILE TRUE WITH FRAME {&FRAME-NAME}:
        ASSIGN  F_Knr   = Adresse.Knr
                F_Kunde = Adresse.Anzeig_Br.
        DISPLAY F_Knr   F_Kunde.
        LEAVE.
    END.
    
    DYNAMIC-FUNCTION('getFehlwerte':U).

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE FINDEN_NUMMER gExcelbestellung 
PROCEDURE FINDEN_NUMMER :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/

    DEF VAR iRecid      AS RECID                    NO-UNDO.

    RUN 'g-kundennr.w':U ( OUTPUT iRecid ).

    IF iRecid = ? OR
       iRecid = 0 THEN RETURN.
       
    FIND Adresse NO-LOCK WHERE RECID(Adresse) = iRecid.
    DO WHILE TRUE WITH FRAME {&FRAME-NAME}:
        ASSIGN  F_Knr   = Adresse.Knr
                F_Kunde = Adresse.Anzeig_Br.
        DISPLAY F_Knr   F_Kunde.
        LEAVE.
    END.
    
    DYNAMIC-FUNCTION('getFehlwerte':U).

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE OEFFNEN_EXCEL gExcelbestellung 
PROCEDURE OEFFNEN_EXCEL :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/

    DEF VAR cDokument   AS CHAR                     NO-UNDO.

    DO WITH FRAME {&FRAME-NAME}:
        ASSIGN  {&List-6}.
        
        FILE-INFO:FILE-NAME = F_Datei.
        IF FILE-INFO:FULL-PATHNAME = ?          OR
           FILE-INFO:FULL-PATHNAME = ''         OR
           INDEX(FILE-INFO:FILE-TYPE, 'D') > 0  THEN DO:
            MESSAGE 'ungültiger Dateiname'
                VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.
            APPLY 'ENTRY' TO F_Datei.
            RETURN 'ERROR'.
        END.
        F_Datei = FILE-INFO:FULL-PATHNAME.
    END.

    DO WHILE TRUE:
        IF VALID-HANDLE(excelAppl) THEN DO:
            cDokument = excelAppl:ActiveWorkbook:NAME NO-ERROR.
            IF cDokument <> '' AND
               cDokument <> ?  THEN DO:
                excelAppl:ActiveWorkbook:CLOSE ( FALSE, cDokument ) NO-ERROR.
            END.
            RELEASE OBJECT excelAppl NO-ERROR.
        END.
        CREATE "Excel.Application" excelAppl CONNECT NO-ERROR.
        IF VALID-HANDLE(excelAppl) THEN LEAVE.

        CREATE "Excel.Application" excelAppl NO-ERROR.
        LEAVE.
    END.
    
    IF NOT VALID-HANDLE(excelAppl) THEN DO:
        MESSAGE 'Excel konnte nicht gestartet werden'
            VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.
        RETURN.
    END.

    excelAppl:Workbooks:Open(F_Datei, 0, FALSE, 4, "", "", TRUE, , ";", ,).
    excelAppl:Visible = TRUE.

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

/* ************************  Function Implementations ***************** */

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION createAuftrag gExcelbestellung 
FUNCTION createAuftrag RETURNS INTEGER
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/

    DEF VAR htAufze     AS HANDLE                   NO-UNDO.
    DEF VAR lRetVal     AS LOG                      NO-UNDO.
    DEF VAR iRetVal     AS INT                      NO-UNDO.
    DEF VAR iPreisArt   AS INT                      NO-UNDO.
    DEF VAR nTotal      AS DEC  DECIMALS 4          NO-UNDO EXTENT 15.
    DEF VAR cRetValue   AS CHAR                     NO-UNDO.
    DEF VAR iAnz        AS INT                      NO-UNDO.
    
    DEF BUFFER bAufze       FOR Aufze.

    htAufze = TEMP-TABLE tAufze:DEFAULT-BUFFER-HANDLE.
    FOR EACH tKunBest
            WHERE tKunBest.KGeb_Me > 0:
        EMPTY TEMP-TABLE tAufze.
        CREATE  tAufze.
        ASSIGN  tAufze.Firma     = Firma
                tAufze.Aufnr     = iAufnr
                tAufze.Artnr     = tKunBest.Artnr
                tAufze.Inhalt    = tKunBest.Inhalt
                tAufze.Jahr      = tKunBest.Jahr
                tAufze.Preis_Mut = FALSE
                tAufze.Knr       = iKnr.
    
        htAufze = TEMP-TABLE tAufze:DEFAULT-BUFFER-HANDLE.
        iRetVal = DYNAMIC-FUNCTION ( 'fillAufze':U, INPUT-OUTPUT htAufze ) NO-ERROR.
        IF iRetVal > 0 THEN DO:
        MESSAGE iRetVal
            VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.
            DYNAMIC-FUNCTION('fehlerMeldung':U, iRetVal, '' ) NO-ERROR.
            RETURN 0.
        END.
        
        FIND FIRST tAufze.
        ASSIGN  tAufze.Preis_Mut = FALSE
                tAufze.MBest     = tKunBest.KGeb_Me
                tAufze.MGeli     = tKunBest.KGeb_Me
                tAufze.VGeb_Be   = tKunBest.VGeb_Me
                tAufze.VGeb_Me   = tKunBest.VGeb_Me
                tAufze.KGeb_Be   = tKunBest.KGeb_Me
                tAufze.KGeb_Me   = tKunBest.KGeb_Me
                .

        iPreisArt = DYNAMIC-FUNCTION ( 'getPreisAufze':U       , INPUT-OUTPUT htAufze ) NO-ERROR.
        FIND FIRST tAufze.
        lRetVal   = DYNAMIC-FUNCTION ( 'calculateZeilenTotal':U, INPUT-OUTPUT htAufze ) NO-ERROR.
        FIND FIRST tAufze.
        
        FIND LAST bAufze NO-LOCK
                WHERE bAufze.Firma = tAufze.Firma
                AND   bAufze.Aufnr = tAufze.Aufnr NO-ERROR.
        IF NOT AVAILABLE bAufze THEN tAufze.Pos = 05.
        ELSE                         tAufze.Pos = bAufze.Pos - (bAufze.Pos MOD 5) + 05.
        
        DYNAMIC-FUNCTION('fillArtbwFromAufze':U, INPUT-OUTPUT htAufze ).
        FIND FIRST tAufze.
        CREATE  bAufze.
        BUFFER-COPY tAufze TO bAufze.
        RELEASE bAufze.
        
        iAnz = iAnz + 1.
    END.
    
    cRetValue = DYNAMIC-FUNCTION('calculateAuftragsTotal':U, Firma, iAufnr, OUTPUT nTotal ) NO-ERROR.

    IF cRetValue BEGINS 'KREDIT' THEN DO:
        RUN FEHLER ( 1086 ) NO-ERROR.
    END.
    
    RETURN iAnz.
    
END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION getFehlwerte gExcelbestellung 
FUNCTION getFehlwerte RETURNS LOGICAL
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/

    DEF VAR cRetVal     AS CHAR                     NO-UNDO.
    DEF VAR ii          AS INT                      NO-UNDO.

    DO WITH FRAME {&FRAME-NAME}:
        ASSIGN  {&List-6}.
    END.

    FIND Tabel NO-LOCK
            WHERE Tabel.Firma  = Firma
            AND   Tabel.RecArt = 'EXCELBEST'
            AND   Tabel.CodeC  = ''
            AND   Tabel.CodeI  = F_Knr
            AND   Tabel.Sprcd  = 1 NO-ERROR.
    IF AVAILABLE Tabel THEN cRetVal = Tabel.Bez1.
    
    IF cRetVal = '' THEN cRetVal = SUBSTITUTE('&1&2\Downloads\;A;B;C;D;E;F;',
                                        OS-GETENV("HOMEDRIVE"),
                                        OS-GETENV("HOMEPATH")).
    
    DO ii = 1 TO NUM-ENTRIES(cRetVal, ';'):
        CASE ii:
            WHEN 1 THEN F_Datei     = ENTRY(ii, cRetVal, ';') NO-ERROR.
            WHEN 2 THEN F_Artnr     = ENTRY(ii, cRetVal, ';') NO-ERROR.
            WHEN 3 THEN F_Inhalt    = ENTRY(ii, cRetVal, ';') NO-ERROR.
            WHEN 4 THEN F_Jahr      = ENTRY(ii, cRetVal, ';') NO-ERROR.
            WHEN 5 THEN F_GGebinde  = ENTRY(ii, cRetVal, ';') NO-ERROR.
            WHEN 6 THEN F_VGebinde  = ENTRY(ii, cRetVal, ';') NO-ERROR.
            WHEN 7 THEN F_KGebinde  = ENTRY(ii, cRetVal, ';') NO-ERROR.
        END CASE.
    END.

    DO WITH FRAME {&FRAME-NAME}:
        DISPLAY {&Liast-6}.
    END.
    
    RETURN TRUE.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION setFehlwerte gExcelbestellung 
FUNCTION setFehlwerte RETURNS LOGICAL
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/

    DEF VAR cRetVal     AS CHAR                     NO-UNDO.
    DEF VAR ii          AS INT                      NO-UNDO.

    REPEAT TRANSACTION:
        FIND Tabel
                WHERE Tabel.Firma  = Firma
                AND   Tabel.RecArt = 'EXCELBEST'
                AND   Tabel.CodeC  = ''
                AND   Tabel.CodeI  = iKnr
                AND   Tabel.Sprcd  = 1 NO-ERROR.
        IF NOT AVAILABLE Tabel THEN DO:
            CREATE  Tabel.
            ASSIGN  Tabel.Firma  = Firma
                    Tabel.RecArt = 'EXCELBEST'
                    Tabel.CodeC  = ''
                    Tabel.CodeI  = iKnr
                    Tabel.Sprcd  = 1 NO-ERROR.
        END.
        Tabel.Bez1 = SUBSTITUTE('&1;&2;&3;&4;&5;&6;&7;',
                     F_Datei, F_Artnr, F_Inhalt, F_Jahr,
                     F_GGebinde, F_VGebinde, F_KGebinde).
        RELEASE Tabel.
        LEAVE.
    END.
    
    RETURN TRUE.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME