GeMIS_WEB/GUI/g-excel-preisliste.w

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

/* 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:  <none>
------------------------------------------------------------------------------*/

END PROCEDURE.

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

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE AKTUALISIEREN g-excel-preisliste 
PROCEDURE AKTUALISIEREN :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  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:  <none>
  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:  <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 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:  <none>
  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:  <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_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:  <none>
  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