&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: Output Parameters: 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: ------------------------------------------------------------------------------*/ 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: 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: 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: 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: 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: 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: 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: 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