&ANALYZE-SUSPEND _VERSION-NUMBER AB_v10r12 GUI ADM2 &ANALYZE-RESUME &Scoped-define WINDOW-NAME CURRENT-WINDOW &Scoped-define FRAME-NAME g-excel-preisliste {adecomm/appserv.i} &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _DEFINITIONS g-excel-preisliste /*------------------------------------------------------------------------ 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 --- */ /* Local Variable Definitions --- */ DEF VAR VDateiName AS CHAR NO-UNDO. DEF VAR VxlsDatei AS CHAR NO-UNDO. DEF VAR VcsvDatei AS CHAR NO-UNDO. DEF VAR VWert AS CHAR NO-UNDO. DEF VAR ok AS LOG NO-UNDO. DEF VAR VPrGrp AS INT NO-UNDO. DEF VAR cArtnr_1 AS CHAR NO-UNDO. DEF VAR cArtnr_2 AS CHAR NO-UNDO. DEF VAR cInhalt_1 AS CHAR NO-UNDO. DEF VAR cInhalt_2 AS CHAR NO-UNDO. DEF VAR cJahr_1 AS CHAR NO-UNDO. DEF VAR cJahr_2 AS CHAR NO-UNDO. DEF VAR cPreis AS CHAR NO-UNDO. DEF VAR excelAppl AS COM-HANDLE NO-UNDO. DEF TEMP-TABLE TExcel FIELD Zeile AS INT FIELD Artnr AS INT FIELD Inhalt AS INT FIELD Jahr AS INT FIELD Preis AS DEC DECIMALS 4 FIELD aArtnr AS INT FIELD aInhalt AS INT FIELD aJahr AS INT INDEX TExcel-k1 IS PRIMARY Artnr Inhalt Jahr. DEF TEMP-TABLE tDaten_csv NO-UNDO FIELD Feld AS CHAR EXTENT 20 FIELD Zeile AS INT. DEF STREAM In_Stream. { 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 g-excel-preisliste /* Standard List Definitions */ &Scoped-Define ENABLED-OBJECTS RECT-2 Btn_Datei F_ExcelDatei CB_Artnr_1 ~ CB_Artnr_2 CB_Inhalt_1 CB_Inhalt_2 CB_Jahr_1 CB_Jahr_2 CB_Preis ~ CB_Preisliste T_Netto F_Datum Btn_OK Btn_Cancel &Scoped-Define DISPLAYED-OBJECTS F_ExcelDatei CB_Artnr_1 CB_Artnr_2 ~ CB_Inhalt_1 CB_Inhalt_2 CB_Jahr_1 CB_Jahr_2 CB_Preis CB_Preisliste T_Netto ~ F_Datum /* Custom List Definitions */ /* List-1,List-2,List-3,List-4,List-5,List-6 */ &Scoped-define List-6 F_ExcelDatei CB_Preisliste T_Netto F_Datum /* _UIB-PREPROCESSOR-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 LABEL "ExcelDatei" SIZE 12 BY 1. DEFINE BUTTON Btn_OK AUTO-GO LABEL "Aktualisieren" SIZE 15 BY 1. DEFINE VARIABLE CB_Artnr_1 AS CHARACTER FORMAT "X(256)":U LABEL "Spalte Artnr DB" VIEW-AS COMBO-BOX INNER-LINES 5 LIST-ITEMS "A" DROP-DOWN-LIST SIZE 8 BY 1 TOOLTIP "Artikelnummer für Datenbank" NO-UNDO. DEFINE VARIABLE CB_Artnr_2 AS CHARACTER FORMAT "X(256)":U LABEL "Spalte Artnr Preisliste" VIEW-AS COMBO-BOX INNER-LINES 5 LIST-ITEMS "A" DROP-DOWN-LIST SIZE 8 BY 1 TOOLTIP "Artikelnummer für Datenbank" NO-UNDO. DEFINE VARIABLE CB_Inhalt_1 AS CHARACTER FORMAT "X(256)":U LABEL "Spalte Inhalt DB" VIEW-AS COMBO-BOX INNER-LINES 5 LIST-ITEMS "A" DROP-DOWN-LIST SIZE 8 BY 1 TOOLTIP "Artikelnummer für Datenbank" NO-UNDO. DEFINE VARIABLE CB_Inhalt_2 AS CHARACTER FORMAT "X(256)":U LABEL "Spalte Inhalt Preisliste" VIEW-AS COMBO-BOX INNER-LINES 5 LIST-ITEMS "A" DROP-DOWN-LIST SIZE 8 BY 1 TOOLTIP "Artikelnummer für Datenbank" NO-UNDO. DEFINE VARIABLE CB_Jahr_1 AS CHARACTER FORMAT "X(256)":U LABEL "Spalte Jahr DB" VIEW-AS COMBO-BOX INNER-LINES 5 LIST-ITEMS "A" DROP-DOWN-LIST SIZE 8 BY 1 TOOLTIP "Artikelnummer für Datenbank" NO-UNDO. DEFINE VARIABLE CB_Jahr_2 AS CHARACTER FORMAT "X(256)":U LABEL "Spalte Jahr Preisliste" VIEW-AS COMBO-BOX INNER-LINES 5 LIST-ITEMS "A" DROP-DOWN-LIST SIZE 8 BY 1 TOOLTIP "Artikelnummer für Datenbank" NO-UNDO. DEFINE VARIABLE CB_Preis AS CHARACTER FORMAT "X(256)":U LABEL "Spalte Preis Preisliste" VIEW-AS COMBO-BOX INNER-LINES 5 LIST-ITEMS "A" DROP-DOWN-LIST SIZE 8 BY 1 TOOLTIP "Artikelnummer für Datenbank" NO-UNDO. DEFINE VARIABLE CB_Preisliste AS CHARACTER FORMAT "X(256)":U LABEL "Preisgruppe" VIEW-AS COMBO-BOX INNER-LINES 5 LIST-ITEM-PAIRS "Item 1","Item 1" DROP-DOWN-LIST SIZE 32 BY 1 BGCOLOR 15 NO-UNDO. DEFINE VARIABLE F_Datum AS DATE FORMAT "99.99.9999":U LABEL "Konditionsdatum" VIEW-AS FILL-IN NATIVE SIZE 16 BY 1 BGCOLOR 15 NO-UNDO. DEFINE VARIABLE F_ExcelDatei AS CHARACTER FORMAT "X(256)":U VIEW-AS FILL-IN NATIVE SIZE 61 BY 1 BGCOLOR 15 NO-UNDO. DEFINE RECTANGLE RECT-2 EDGE-PIXELS 2 GRAPHIC-EDGE NO-FILL SIZE 80 BY 12.86. DEFINE VARIABLE T_Netto AS LOGICAL INITIAL yes LABEL "exkl. MWSt" VIEW-AS TOGGLE-BOX SIZE 13.8 BY 1 NO-UNDO. /* ************************ Frame Definitions *********************** */ DEFINE FRAME g-excel-preisliste Btn_Datei AT ROW 2 COL 5 WIDGET-ID 4 F_ExcelDatei AT ROW 2 COL 16 COLON-ALIGNED NO-LABEL WIDGET-ID 6 CB_Artnr_1 AT ROW 3.52 COL 23 COLON-ALIGNED WIDGET-ID 8 CB_Artnr_2 AT ROW 3.52 COL 69 COLON-ALIGNED WIDGET-ID 10 CB_Inhalt_1 AT ROW 4.52 COL 23 COLON-ALIGNED WIDGET-ID 12 CB_Inhalt_2 AT ROW 4.52 COL 69 COLON-ALIGNED WIDGET-ID 14 CB_Jahr_1 AT ROW 5.52 COL 23 COLON-ALIGNED WIDGET-ID 16 CB_Jahr_2 AT ROW 5.52 COL 69 COLON-ALIGNED WIDGET-ID 18 CB_Preis AT ROW 6.52 COL 69 COLON-ALIGNED WIDGET-ID 26 CB_Preisliste AT ROW 8.52 COL 23 COLON-ALIGNED WIDGET-ID 20 T_Netto AT ROW 8.52 COL 59 WIDGET-ID 24 F_Datum AT ROW 9.52 COL 23 COLON-ALIGNED WIDGET-ID 22 Btn_OK AT ROW 15 COL 12.2 Btn_Cancel AT ROW 15 COL 53 RECT-2 AT ROW 1.48 COL 3 WIDGET-ID 2 SPACE(1.99) SKIP(2.36) WITH VIEW-AS DIALOG-BOX KEEP-TAB-ORDER SIDE-LABELS NO-UNDERLINE THREE-D SCROLLABLE TITLE "EXCEL-Preisliste" 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 g-excel-preisliste /* ************************* 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 g-excel-preisliste FRAME-NAME */ ASSIGN FRAME g-excel-preisliste:SCROLLABLE = FALSE FRAME g-excel-preisliste:HIDDEN = TRUE. /* SETTINGS FOR COMBO-BOX CB_Preisliste IN FRAME g-excel-preisliste 6 */ /* SETTINGS FOR FILL-IN F_Datum IN FRAME g-excel-preisliste 6 */ /* SETTINGS FOR FILL-IN F_ExcelDatei IN FRAME g-excel-preisliste 6 */ /* SETTINGS FOR TOGGLE-BOX T_Netto IN FRAME g-excel-preisliste 6 */ /* _RUN-TIME-ATTRIBUTES-END */ &ANALYZE-RESUME /* Setting information for Queries and Browse Widgets fields */ &ANALYZE-SUSPEND _QUERY-BLOCK DIALOG-BOX g-excel-preisliste /* Query rebuild information for DIALOG-BOX g-excel-preisliste _Options = "SHARE-LOCK" _Query is NOT OPENED */ /* DIALOG-BOX g-excel-preisliste */ &ANALYZE-RESUME /* ************************ Control Triggers ************************ */ &Scoped-define SELF-NAME g-excel-preisliste &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL g-excel-preisliste g-excel-preisliste ON END-ERROR OF FRAME g-excel-preisliste /* EXCEL-Preisliste */ DO: RUN ENDE. RETURN NO-APPLY. END. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL g-excel-preisliste g-excel-preisliste ON WINDOW-CLOSE OF FRAME g-excel-preisliste /* EXCEL-Preisliste */ 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 g-excel-preisliste ON CHOOSE OF Btn_Cancel IN FRAME g-excel-preisliste /* 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 g-excel-preisliste ON CHOOSE OF Btn_Datei IN FRAME g-excel-preisliste /* ExcelDatei */ DO: RUN DATEIEINGABE. END. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &Scoped-define SELF-NAME Btn_OK &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL Btn_OK g-excel-preisliste ON CHOOSE OF Btn_OK IN FRAME g-excel-preisliste /* Aktualisieren */ DO: btnOK = TRUE. DO WITH FRAME {&FRAME-NAME}: DISABLE Btn_Ok Btn_Cancel. ASSIGN {&list-6}. RUN EINGABETEST. DISPLAY {&List-6}. DO WHILE TRUE: IF RETURN-VALUE = '' THEN LEAVE. ENABLE Btn_Ok Btn_Cancel. RETURN NO-APPLY. END. RUN AKTUALISIEREN. END. RUN ENDE. RETURN NO-APPLY. END. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &Scoped-define SELF-NAME CB_Preisliste &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL CB_Preisliste g-excel-preisliste ON RETURN OF CB_Preisliste IN FRAME g-excel-preisliste /* Preisgruppe */ DO: APPLY 'TAB' TO SELF. RETURN NO-APPLY. END. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL CB_Preisliste g-excel-preisliste ON VALUE-CHANGED OF CB_Preisliste IN FRAME g-excel-preisliste /* Preisgruppe */ DO: DEF VAR iPrgrp AS INT NO-UNDO. iPrgrp = INTEGER(SELF:SCREEN-VALUE). FIND Tabel USE-INDEX Tabel-k2 NO-LOCK WHERE Tabel.Firma = Firma AND Tabel.RecArt = 'PREGRP' AND Tabel.CodeI = iPrgrp AND Tabel.CodeC = '' AND Tabel.SPrcd = 1 NO-ERROR. IF Tabel.Int_1 = 0 THEN T_Netto:SCREEN-VALUE = STRING(TRUE). ELSE T_Netto:SCREEN-VALUE = STRING(FALSE). END. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &Scoped-define SELF-NAME F_Datum &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL F_Datum g-excel-preisliste ON RETURN OF F_Datum IN FRAME g-excel-preisliste /* Konditionsdatum */ DO: APPLY 'TAB' TO SELF. RETURN NO-APPLY. END. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &Scoped-define SELF-NAME F_ExcelDatei &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL F_ExcelDatei g-excel-preisliste ON RETURN OF F_ExcelDatei IN FRAME g-excel-preisliste DO: APPLY 'TAB' TO SELF. RETURN NO-APPLY. END. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &Scoped-define SELF-NAME T_Netto &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL T_Netto g-excel-preisliste ON RETURN OF T_Netto IN FRAME g-excel-preisliste /* exkl. MWSt */ DO: APPLY 'TAB' TO SELF. RETURN NO-APPLY. END. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &UNDEFINE SELF-NAME &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _MAIN-BLOCK g-excel-preisliste /* *************************** Main Block *************************** */ { incl/dlgmainblock.i } ON 'RETURN':U OF CB_Artnr_1 , CB_Artnr_2 , CB_Inhalt_1, CB_Inhalt_2, CB_Jahr_1 , CB_Jahr_2 , CB_Preis , CB_Preisliste IN FRAME {&FRAME-NAME} DO: APPLY 'TAB' TO SELF. RETURN NO-APPLY. END. {src/adm2/dialogmn.i} /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME /* ********************** Internal Procedures *********************** */ &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE adm-create-objects g-excel-preisliste _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 AKTUALISIEREN g-excel-preisliste PROCEDURE AKTUALISIEREN : /*------------------------------------------------------------------------------ Purpose: Parameters: Notes: ------------------------------------------------------------------------------*/ DEF VAR fAlt AS LOG NO-UNDO. DEF VAR iFehler AS INT NO-UNDO. DEF VAR iAnzahl AS INT NO-UNDO. DEF VAR cWort AS CHAR NO-UNDO. DEF VAR iRecid AS RECID NO-UNDO. DEF VAR ix AS INT NO-UNDO. DEF VAR i1 AS INT NO-UNDO. DEF VAR sArtnr AS INT NO-UNDO. DEF VAR sInhalt AS INT NO-UNDO. DEF VAR sJahr AS INT NO-UNDO. DEF VAR cAltNummer AS CHAR FORMAT "x(14)" NO-UNDO. DO WHILE TRUE: 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 RETURN. VxlsDatei = VDateiName. ix = R-INDEX(VxlsDatei, '.'). IF ix = 0 THEN VcsvDatei = VxlsDatei + '.csv'. ELSE VcsvDatei = SUBSTRING(VxlsDatei,01,ix - 1) + '.csv'. IF SEARCH(VcsvDatei) <> ? THEN OS-DELETE VALUE(VcsvDatei). excelAppl:Visible = TRUE. excelAppl:Workbooks:Open(VxlsDatei, 0, TRUE , 4, "", "", TRUE , , ";", ,). excelAppl:ActiveWorkbook:SaveAs (VcsvDatei, 6, "", "", FALSE, FALSE, FALSE ). excelAppl:ActiveWorkbook:Close ( FALSE, VcsvDatei ). EMPTY TEMP-TABLE TExcel. EMPTY TEMP-TABLE tDaten_csv. DEF VAR VNr AS INT NO-UNDO. DEF VAR VZelle AS CHAR NO-UNDO. DEF VAR VZeile AS CHAR NO-UNDO. DEF VAR VArtnr AS INT NO-UNDO. DEF VAR VInhalt AS INT NO-UNDO. DEF VAR VJahr AS INT NO-UNDO. excelAppl:Workbooks:Open(VxlsDatei, 0, FALSE, 4, "", "", TRUE, , ";", ,). VNr = 0. DO WHILE VNr < 11: VNr = VNr + 1. VZelle = 'A' + STRING(VNr). excelAppl:Range(VZelle):Select. IF excelAppl:Selection:Value <> ? THEN LEAVE. VZelle = 'B' + STRING(VNr). excelAppl:Range(VZelle):Select. IF excelAppl:Selection:Value <> ? THEN LEAVE. CREATE TExcel. ASSIGN TExcel.Zeile = VNr. END. INPUT STREAM In_Stream FROM VALUE(VcsvDatei) NO-ECHO NO-MAP. ix = 0. i1 = 0. REPEAT ON STOP UNDO, LEAVE: ix = ix + 1. CREATE tDaten_csv. IMPORT STREAM In_Stream DELIMITER ';' tDaten_csv EXCEPT tDaten_csv.Zeile. ASSIGN tDaten_csv.Zeile = ix. END. INPUT STREAM In_Stream CLOSE. DO WITH FRAME {&FRAME-NAME}: sArtnr = ASC(cArtnr_1 ) - 64. sInhalt = ASC(cInhalt_1) - 64. sJahr = ASC(cJahr_1 ) - 64. END. FOR EACH tDaten_csv WHERE tDaten_csv.Feld[sArtnr] <> '' BY tDaten_csv.Zeile: VArtnr = INTEGER(tDaten_csv.Feld[sArtnr]) NO-ERROR. IF ERROR-STATUS:ERROR THEN NEXT. IF VArtnr = 0 THEN NEXT. VInhalt = INTEGER(tDaten_csv.Feld[sInhalt]) NO-ERROR. VJahr = INTEGER(tDaten_csv.Feld[sJahr] ) NO-ERROR. CREATE TExcel. ASSIGN TExcel.Zeile = tDaten_csv.Zeile TExcel.Artnr = VArtnr TExcel.Inhalt = VInhalt TExcel.Jahr = VJahr TExcel.aArtnr = 0. END. iFehler = 0. iAnzahl = 0. FOR EACH TExcel WHERE TExcel.Artnr <> 0: iAnzahl = iAnzahl + 1. FIND Artst USE-INDEX Artst-k1 WHERE Artst.Firma = Firma AND Artst.Artnr = TExcel.Artnr AND Artst.Inhalt = TExcel.Inhalt AND Artst.Jahr = TExcel.Jahr NO-LOCK NO-ERROR. IF AVAILABLE Artst THEN NEXT. iFehler = iFehler + 1. IF iFehler >= 20 THEN LEAVE. END. IF iFehler > (iAnzahl / 2) THEN DO: FOR EACH TExcel WHERE TExcel.Artnr <> 0: cAltNummer = STRING(TExcel.Artnr ,'999999') + STRING(TExcel.Inhalt,'9999') + STRING(TExcel.Jahr ,'9999'). FIND FIRST Artst NO-LOCK USE-INDEX Artst-FremdNr WHERE Artst.Firma = Firma AND Artst.FremdNr = cAltNummer NO-ERROR. IF NOT AVAILABLE Artst THEN DO: ASSIGN tExcel.aArtnr = 999999 tExcel.Preis = 9999.99. NEXT. END. ASSIGN TExcel.aArtnr = TExcel.Artnr TExcel.aInhalt = TExcel.Inhalt TExcel.aJahr = TExcel.Jahr TExcel.Artnr = Artst.Artnr TExcel.Inhalt = Artst.Inhalt TExcel.Jahr = Artst.Jahr. END. END. FOR EACH TExcel WHERE TExcel.Artnr <> 0 AND TExcel.aArtnr <> 999999. FIND Artst USE-INDEX Artst-k1 WHERE Artst.Firma = Firma AND Artst.Artnr = TExcel.Artnr AND Artst.Inhalt = TExcel.Inhalt AND Artst.Jahr = TExcel.Jahr NO-LOCK NO-ERROR. IF NOT AVAILABLE Artst THEN DO: TExcel.Preis = 9999.99. NEXT. END. FIND LAST ArtPreis USE-INDEX ArtPreis-k1 WHERE ArtPreis.Firma = Firma AND ArtPreis.Artnr = Artst.Artnr AND ArtPreis.Inhalt = Artst.Inhalt AND ArtPreis.Jahr = Artst.Jahr AND ArtPreis.Preis_Grp = VPrGrp AND ArtPreis.Aktion = FALSE AND ArtPreis.Ab_Datum <= F_Datum NO-LOCK NO-ERROR. IF NOT AVAILABLE ArtPreis THEN DO: FIND LAST ArtPreis USE-INDEX ArtPreis-k1 WHERE ArtPreis.Firma = Firma AND ArtPreis.Artnr = Artst.Artnr AND ArtPreis.Inhalt = Artst.Inhalt AND ArtPreis.Jahr = Artst.Jahr AND ArtPreis.Preis_Grp = 0 AND ArtPreis.Aktion = FALSE AND ArtPreis.Ab_Datum <= F_Datum NO-LOCK NO-ERROR. END. IF NOT AVAILABLE ArtPreis THEN DO: TExcel.Preis = 9999.99. NEXT. END. IF T_Netto THEN TExcel.Preis = ArtPreis.VK_Netto. ELSE TExcel.Preis = ArtPreis.VK_Brutto. END. FOR EACH TExcel WHERE TExcel.Artnr <> 0 BY TExcel.Zeile: VZelle = CB_Preis:SCREEN-VALUE + STRING(TExcel.Zeile). excelAppl:Range(VZelle):Select. excelAppl:Selection:Value = TRIM(STRING(TExcel.Preis,'>>>>>9.9999')). IF TExcel.aArtnr = 0 THEN NEXT. IF TExcel.aArtnr = 999999 THEN DO: VZelle = 'A' + STRING(TExcel.Zeile) + ':C' + STRING(TExcel.Zeile). excelAppl:Range(VZelle):Select. excelAppl:Selection:Interior:Pattern = 1. excelAppl:Selection:Interior:Color = 255. NEXT. END. VZelle = cArtnr_1 + STRING(TExcel.Zeile). excelAppl:Range(VZelle):Select. excelAppl:Selection:Value = TRIM(STRING(TExcel.Artnr ,'999999')). VZelle = cInhalt_1 + STRING(TExcel.Zeile). excelAppl:Range(VZelle):Select. excelAppl:Selection:Value = TRIM(STRING(TExcel.Inhalt,'9999')). VZelle = cJahr_1 + STRING(TExcel.Zeile). excelAppl:Range(VZelle):Select. excelAppl:Selection:Value = TRIM(STRING(TExcel.Jahr ,'9999')). IF cArtnr_2 <> '' THEN DO: VZelle = cArtnr_2 + STRING(TExcel.Zeile). excelAppl:Range(VZelle):Select. excelAppl:Selection:Value = TRIM(STRING(TExcel.Artnr ,'999999')). END. IF cInhalt_2 <> '' THEN DO: VZelle = cInhalt_2 + STRING(TExcel.Zeile). excelAppl:Range(VZelle):Select. excelAppl:Selection:Value = TRIM(STRING(TExcel.Inhalt,'9999')). END. IF cJahr_2 <> '' THEN DO: VZelle = cJahr_2 + STRING(TExcel.Zeile). excelAppl:Range(VZelle):Select. excelAppl:Selection:Value = TRIM(STRING(TExcel.Jahr,'9999')). END. END. RELEASE OBJECT excelAppl. END PROCEDURE. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE DATEIEINGABE g-excel-preisliste PROCEDURE DATEIEINGABE : /*------------------------------------------------------------------------------ Purpose: Parameters: Notes: ------------------------------------------------------------------------------*/ DEF VAR OKpressed AS LOG NO-UNDO. DO WITH FRAME {&FRAME-NAME}: ASSIGN F_ExcelDatei. VDateiName = F_ExcelDatei. SYSTEM-DIALOG GET-FILE VDateiName TITLE 'Oeffnen der Preisliste' FILTERS 'Excel-Dateien (*.xls, *.xlsx)' '*.xls,*.xlsx', 'Alle Dateien (*.*) ' '*.*' MUST-EXIST USE-FILENAME UPDATE OKpressed. IF OKPressed = TRUE THEN F_ExcelDatei = VDateiName. DISPLAY F_ExcelDatei. END. END PROCEDURE. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE disable_UI g-excel-preisliste _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 g-excel-preisliste. END PROCEDURE. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE EINGABETEST g-excel-preisliste PROCEDURE EINGABETEST : /*------------------------------------------------------------------------------ Purpose: Parameters: Notes: ------------------------------------------------------------------------------*/ DO WITH FRAME {&FRAME-NAME}: VDateiName = SEARCH(F_ExcelDatei). IF VDateiName = ? THEN RETURN 'ERROR'. ASSIGN cArtnr_1 = CB_Artnr_1 :SCREEN-VALUE cArtnr_2 = CB_Artnr_2 :SCREEN-VALUE cInhalt_1 = CB_Inhalt_1:SCREEN-VALUE cInhalt_2 = CB_Inhalt_2:SCREEN-VALUE cJahr_1 = CB_Jahr_1 :SCREEN-VALUE cJahr_2 = CB_Jahr_2 :SCREEN-VALUE cPreis = CB_Preis :SCREEN-VALUE. IF cArtnr_1 = ? THEN cArtnr_1 = ''. IF cArtnr_2 = ? THEN cArtnr_2 = ''. IF cInhalt_1 = ? THEN cInhalt_1 = ''. IF cInhalt_2 = ? THEN cInhalt_2 = ''. IF cJahr_1 = ? THEN cJahr_1 = ''. IF cJahr_2 = ? THEN cJahr_2 = ''. IF cPreis = ? THEN cPreis = ''. IF cPreis = '' THEN RETURN 'ERROR'. PUT-KEY-VALUE SECTION 'Preisliste' KEY 'DateiName' VALUE VDateiName. PUT-KEY-VALUE SECTION 'Preisliste' KEY 'SpalteArtnrDB' VALUE cArtnr_1. PUT-KEY-VALUE SECTION 'Preisliste' KEY 'SpalteInhaltDB' VALUE cInhalt_1. PUT-KEY-VALUE SECTION 'Preisliste' KEY 'SpalteJahrDB' VALUE cJahr_1. PUT-KEY-VALUE SECTION 'Preisliste' KEY 'SpalteArtnrPL' VALUE cArtnr_2. PUT-KEY-VALUE SECTION 'Preisliste' KEY 'SpalteInhaltPL' VALUE cInhalt_2. PUT-KEY-VALUE SECTION 'Preisliste' KEY 'SpalteJahrPL' VALUE cJahr_2. PUT-KEY-VALUE SECTION 'Preisliste' KEY 'SpaltePreis' VALUE CB_Preis:SCREEN-VALUE. PUT-KEY-VALUE SECTION 'Preisliste' KEY 'Netto' VALUE T_Netto:SCREEN-VALUE. IF F_Datum = ? THEN F_Datum = TODAY. VPrGrp = INTEGER(CB_Preisliste:SCREEN-VALUE). RETURN ''. END. END PROCEDURE. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE enableObject g-excel-preisliste PROCEDURE enableObject : /*------------------------------------------------------------------------------ Purpose: Super Override Parameters: Notes: ------------------------------------------------------------------------------*/ { incl/dlgenableobject.i } END PROCEDURE. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE enable_UI g-excel-preisliste _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_ExcelDatei CB_Artnr_1 CB_Artnr_2 CB_Inhalt_1 CB_Inhalt_2 CB_Jahr_1 CB_Jahr_2 CB_Preis CB_Preisliste T_Netto F_Datum WITH FRAME g-excel-preisliste. ENABLE RECT-2 Btn_Datei F_ExcelDatei CB_Artnr_1 CB_Artnr_2 CB_Inhalt_1 CB_Inhalt_2 CB_Jahr_1 CB_Jahr_2 CB_Preis CB_Preisliste T_Netto F_Datum Btn_OK Btn_Cancel WITH FRAME g-excel-preisliste. VIEW FRAME g-excel-preisliste. {&OPEN-BROWSERS-IN-QUERY-g-excel-preisliste} END PROCEDURE. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE ENDE g-excel-preisliste PROCEDURE ENDE : /*------------------------------------------------------------------------------ Purpose: Parameters: Notes: ------------------------------------------------------------------------------*/ IF btnOK THEN DO: END. { incl/dlgende.i } END PROCEDURE. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE initializeObject g-excel-preisliste PROCEDURE initializeObject PRIVATE : /*------------------------------------------------------------------------------ Purpose: Super Override Parameters: Notes: ------------------------------------------------------------------------------*/ DEF VAR cListe1 AS CHAR NO-UNDO INIT 'A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z'. DEF VAR cListe2 AS CHAR NO-UNDO INIT ',A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z'. DEF VAR cPreisGrp AS CHAR NO-UNDO. DO WITH FRAME {&FRAME-NAME}: CB_Artnr_1 :LIST-ITEMS = cListe1 NO-ERROR. CB_Artnr_2 :LIST-ITEMS = cListe2 NO-ERROR. CB_Inhalt_1:LIST-ITEMS = cListe1 NO-ERROR. CB_Inhalt_2:LIST-ITEMS = cListe2 NO-ERROR. CB_Jahr_1 :LIST-ITEMS = cListe1 NO-ERROR. CB_Jahr_2 :LIST-ITEMS = cListe2 NO-ERROR. CB_Preis :LIST-ITEMS = cListe1 NO-ERROR. GET-KEY-VALUE SECTION 'Preisliste' KEY 'DateiName' VALUE VDateiName. IF VDateiName = ? THEN VDateiName = '*.xls'. F_ExcelDatei = VDateiName. GET-KEY-VALUE SECTION 'Preisliste' KEY 'SpalteArtnrDB' VALUE VWert. IF VWert = ? THEN VWert = 'A'. CB_Artnr_1:SCREEN-VALUE = VWert. GET-KEY-VALUE SECTION 'Preisliste' KEY 'SpalteInhaltDB' VALUE VWert. IF VWert = ? THEN VWert = 'A'. CB_Inhalt_1:SCREEN-VALUE = VWert. GET-KEY-VALUE SECTION 'Preisliste' KEY 'SpalteJahrDB' VALUE VWert. IF VWert = ? THEN VWert = 'A'. CB_Jahr_1:SCREEN-VALUE = VWert. GET-KEY-VALUE SECTION 'Preisliste' KEY 'SpalteArtnrPL' VALUE VWert. IF VWert = ? THEN VWert = ''. CB_Artnr_2:SCREEN-VALUE = VWert. GET-KEY-VALUE SECTION 'Preisliste' KEY 'SpalteInhaltPL' VALUE VWert. IF VWert = ? THEN VWert = ''. CB_Inhalt_2:SCREEN-VALUE = VWert. GET-KEY-VALUE SECTION 'Preisliste' KEY 'SpalteJahrPL' VALUE VWert. IF VWert = ? THEN VWert = ''. CB_Jahr_2:SCREEN-VALUE = VWert. GET-KEY-VALUE SECTION 'Preisliste' KEY 'SpaltePreis' VALUE VWert. IF VWert = ? THEN VWert = 'A'. CB_Preis:SCREEN-VALUE = VWert. GET-KEY-VALUE SECTION 'Preisliste' KEY 'Netto' VALUE VWert. IF VWert = ? THEN VWert = 'yes'. IF VWert BEGINS 'y' THEN T_Netto:SCREEN-VALUE = STRING(TRUE). ELSE T_Netto:SCREEN-VALUE = STRING(FALSE). cPreisGrp = ''. FOR EACH Tabel USE-INDEX Tabel-k2 NO-LOCK WHERE Tabel.Firma = Firma AND Tabel.RecArt = 'PREGRP': cPreisGrp = cPreisGrp + (IF cPreisGrp = '' THEN '' ELSE ';') + Tabel.Bez1 + ' (' + TRIM(STRING(Tabel.CodeI,'>>>9')) + ');' + STRING(Tabel.CodeI,"999"). END. CB_Preisliste:DELIMITER = ';'. CB_Preisliste:LIST-ITEM-PAIRS = cPreisGrp. CB_Preisliste:SCREEN-VALUE = CB_Preisliste:ENTRY(1). GET-KEY-VALUE SECTION 'Preisliste' KEY 'Preigruppe' VALUE VWert. IF VWert <> ? AND VWert <> '' THEN CB_Preisliste:SCREEN-VALUE = VWert. F_Datum:SCREEN-VALUE = STRING(TODAY). END. RUN SUPER. APPLY 'VALUE-CHANGED' TO CB_Preisliste. END PROCEDURE. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME