Imports System.Data.SqlClient
Imports Microsoft.Office.Interop

Public Class usrCntlZollArtikel

    Dim _berechtigungNew As Boolean = True
    Dim _berechtigungEdit As Boolean = True
    Dim _berechtigungDel As Boolean = True
    Dim _berechtigungExcel As Boolean = True
    Dim _berechtigungAtlas As Boolean = True
    Dim editAllowed As Boolean = True

    Dim kundenSQL As New kundenSQL
    Private bindingSDL As New BindingSource
    Private dataAdapterLKW As New SqlDataAdapter
    Dim SQLZoll As New SQLZoll
    Dim FUNC As New cProgramFunctions
    Dim bindingString As String
    Dim aktbtn As String = ""
    Dim dgvInitWait As Boolean = False
    Public kdnr As Integer
    Public benutzeransicht = False
    Public displayFilter As Boolean = True
    Public parentKundenblatt As Boolean = False

    Private Sub Me_Load(sender As Object, e As EventArgs) Handles Me.Load
        If VERAG_PROG_ALLGEMEIN.cAllgemein.TESTSYSTEM Then lblTestsystem.Visible = True

        If VERAG_PROG_ALLGEMEIN.cBerechtignunen.CHECK_BERECHTIGUNG_bool("ZOLL_Kundenzolltarif", "SDL") Then
            btnNew.Enabled = True
            btnEdit.Enabled = True
            btnDel.Enabled = True
            btnDelAll.Enabled = True
            btnExcel.Enabled = True
            '   btnAtlas.Visible = True
            editAllowed = True
        Else
            btnNew.Visible = False
            btnEdit.Visible = False
            btnDel.Visible = False
            btnDelAll.Visible = False
            btnExcel.Visible = False
            '   btnAtlas.Enabled = False
            editAllowed = False
        End If

        btnAtlas.Visible = False

        'initDGVVermerke()
      
        FlatButton1.Visible = VERAG_PROG_ALLGEMEIN.cBerechtignunen.CHECK_BERECHTIGUNG_bool("Kunden_Vermerke_Erfassen", "SDL")


        btnExcel.ContextMenuStrip = cntxtExcel
        '  btnAtlas.Visible = parentKundenblatt
        ' btnFilter.Left = Panel4.Width - btnFilter.Width
        pnlFilter.AutoScroll = False
        Me.dgvZollArtikel.DataSource = Me.bindingSDL
        Dim where = ""
        Dim top = ""
        If kdnr > 0 Then
            where = " AND KundenNr='" & kdnr & "' "
        Else
            top = " TOP 200 "
        End If

        newBinding("SELECT " & top & " zollArt_id, KundenNr, zollArt_Status, zollArt_Artikelnummer ,  zollArt_Warencodenummer, zollArt_Kennzeichen_Gueltig, zollArt_Kurzbezeichnung,  Erfassungsdatum, Aenderungsdatum, Sachbearbeiter,zollArt_Warenbeschreibung FROM tblZollArtikel WHERE 1=1 " & where)

    End Sub


    Sub initDGVVermerke()
        With dgvKdVermerke
            .DataSource = VERAG_PROG_ALLGEMEIN.cKundenVermerke.getDT(kdnr, {"ZOLLTARIF"})
            .Columns("kvm_id").Visible = False
            .Columns("kvm_art").Visible = False
            .Columns("kvm_sbId").Visible = False
            .Columns("kvm_kundennr").Visible = False
            .Columns("kvm_text").AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
            .Columns("kvm_text").HeaderText = "Vermerk"
            .Columns("kvm_datum").Width = 90
            .Columns("kvm_datum").HeaderText = "Datum"
            .Columns("kvm_datum").DefaultCellStyle.Format = "dd.MM.yyyy"
            .Columns("kvm_sb").HeaderText = "Sachbearbeiter"
            .Columns("kvm_sb").Width = 110
            .ClearSelection()
        End With
    End Sub


    Sub newBinding(ByVal sql As String)
        dgvInitWait = True
        ' bindingSDL = Nothing
        dgvZollArtikel.Columns.Clear()
        txtchanged = True
        clearFilter()
        setBindingstring(sql)
        initSDLBinding()
        initAllg()
        If displayFilter Then : initFilter()
        Else : pnlFilter.Visible = False ' : Panel1.Height = 119
        End If
        txtchanged = False
        dgvInitWait = False
        If dgvZollArtikel.RowCount > 0 Then dgvZollArtikel.Rows(0).Selected = True
        dgvZollArtikel_SelectionChanged(dgvZollArtikel, New EventArgs)
        initDGVVermerke()
    End Sub


    Private Sub initSDLBinding()
        dgvInitWait = True
        Dim sqlstr As String = bindingString
        ' dgvLKW.DataSource = bindingSDL
        Dim d As New SqlDataAdapter()
        Try
            Dim connectionString As String = cSqlDb.GetFMZOLLConnectionString

            Dim filter As String = getFilter()
            If filter <> "" Then sqlstr &= " AND " & filter

            Me.dataAdapterLKW = New SqlDataAdapter(sqlstr, connectionString)
        Catch ex As SqlException
            MessageBox.Show("Der Connection-String kann nicht verarbeitet werden. Wenden Sie sich an den Programmbetreuer.")
        End Try
        Dim commandBuilder As New SqlCommandBuilder(Me.dataAdapterLKW)
        Dim table As New DataTable()
        Me.dataAdapterLKW.Fill(table)
        '   table.Columns("KundenNr").DefaultValue = kdNr
        table.Locale = System.Globalization.CultureInfo.InvariantCulture
        'bindingSDL.Clear()
        dgvZollArtikel.DataBindings.Clear()
        bindingSDL.DataSource = table
        dgvInitWait = False
    End Sub

    Sub initFilter()
        dgvInitWait = True
        Dim left_value As Integer = 40 - dgvZollArtikel.HorizontalScrollingOffset
        pnlFilter.Controls.Clear()
        Dim l As New Label
        l.Left = left_value - 38 : l.Top = 7
        l.Width = 36 : l.Text = "Filter:"
        pnlFilter.Controls.Add(l)

        If dgvZollArtikel.Rows.Count > 0 Then
            ' MsgBox(dgvZollArtikel.Columns(0).CellType.Name)
            For i As Integer = 0 To dgvZollArtikel.ColumnCount - 1 Step 1
                Dim c As DataGridViewColumn = dgvZollArtikel.Columns(i)

                If c.Visible Then '
                    Try
                        ' MsgBox(c.CellType.Name)
                        If c.Name = "clmnVerarbeiten" Then
                            '  MsgBox("aaa")
                            Dim t As New Panel
                            ' t.Enabled = False
                            t.Left = left_value
                            t.Top = 5
                            t.Width = c.Width
                            pnlFilter.Controls.Add(t)
                            left_value += c.Width
                            Exit Try
                        End If
                        ' MsgBox(c.ValueType.Name)
                        If c.ValueType.Name = "Boolean" Then
                            Dim t As New CheckBox
                            t.Left = left_value
                            t.Top = 5
                            t.CheckAlign = ContentAlignment.MiddleCenter
                            t.ThreeState = True
                            t.CheckState = CheckState.Indeterminate
                            t.Name = "cbxSrch_" & c.Name
                            AddHandler t.CheckStateChanged, AddressOf initSearch
                            t.Width = c.Width
                            pnlFilter.Controls.Add(t)
                            left_value += c.Width
                            Exit Try
                        End If
                        If c.ValueType.Name = "String" Or c.ValueType.Name = "Int32" Or c.ValueType.Name = "Decimal" Then
                            Dim t As New TextBox
                            t.Left = left_value
                            t.Top = 5
                            t.Name = "txtSrch_" & c.Name
                            AddHandler t.TextChanged, AddressOf initSearch
                            t.Width = c.Width
                            pnlFilter.Controls.Add(t)
                            left_value += c.Width
                            Exit Try
                        End If
                        If c.ValueType.Name = "DateTime" Then
                            Dim t As New TextBox
                            t.Left = left_value
                            t.Top = 5
                            t.Name = "datSrch_" & c.Name
                            AddHandler t.TextChanged, AddressOf initSearch
                            t.Width = c.Width
                            pnlFilter.Controls.Add(t)
                            left_value += c.Width
                            Exit Try
                        End If


                    Catch ex As Exception

                    End Try
                End If
            Next
        End If
        dgvInitWait = False
    End Sub
    Dim txtchanged As Boolean = False
    Sub initSearch()
        txtchanged = True
        initSDLBinding()
        initAllg()
        txtchanged = False
        ' If dgvZollArtikel.RowCount > 0 Then dgvZollArtikel.Rows(0).Cells(0).Selected = True
        dgvZollArtikel_SelectionChanged(dgvZollArtikel, New EventArgs)
    End Sub

    Function getSqlText(t) As String
        Return t.Replace("'", "")
    End Function
    Function getFilter() As String
        Dim filter As String = ""
        Dim andstr As String = ""
        If pnlFilter.HasChildren Then
            For Each c In pnlFilter.Controls
                If c.GetType.Name = "TextBox" Then
                    Try
                        Dim t As TextBox = DirectCast(c, TextBox)
                        If t.Text <> "" Then
                            If t.Name.Contains("txtSrch_") Then
                                '      MsgBox(t.Name.Replace("txtSrch_", ""))
                                filter &= andstr & " [" & t.Name.Replace("txtSrch_", "") & "] LIKE '%" & getSqlText(t.Text) & "%' "
                                andstr = " AND "
                            End If
                            If t.Name.Contains("datSrch_") AndAlso t.Text.Length = 10 Then
                                filter &= andstr & " ( [" & t.Name.Replace("datSrch_", "") & "] BETWEEN '" & getSqlText(t.Text) & "' AND '" & getSqlText(t.Text) & "' ) "
                                andstr = " AND "
                            End If

                        End If
                    Catch ex As Exception
                    End Try
                ElseIf c.GetType.Name = "CheckBox" Then
                    Try
                        Dim t As CheckBox = DirectCast(c, CheckBox)
                        If Not t.CheckState = CheckState.Indeterminate Then
                            filter &= andstr & " [" & t.Name.Replace("cbxSrch_", "") & "] = '" & t.CheckState & "' "
                            andstr = " AND "
                        End If

                    Catch ex As Exception
                    End Try
                End If
            Next
            '  Me.bindingSDL = New BindingSource
            '   Me.dgvZollArtikel.DataSource = Me.bindingSDL
        End If
        Return filter
    End Function

    Sub clearFilter()
        If pnlFilter.HasChildren Then
            pnlFilter.Controls.Clear()
        End If
    End Sub

    Private Sub dgvZollArtikel_CellMouseUp(sender As Object, e As DataGridViewCellMouseEventArgs) Handles dgvZollArtikel.CellMouseUp
        If e.Button = MouseButtons.Right Then dgvZollArtikel.Rows(e.RowIndex).Selected = True
    End Sub


    Private Sub dgvZollArtikel_ColumnWidthChanged(sender As Object, e As DataGridViewColumnEventArgs) Handles dgvZollArtikel.ColumnWidthChanged
        If Not txtchanged Then initFilter()
    End Sub

    Sub setBindingstring(ByVal b As String)
        bindingString = b
    End Sub

    Sub initAllg()
        With dgvZollArtikel
            If .ColumnCount = 0 Then Exit Sub
            .RowTemplate.Height = 20
            .AllowUserToOrderColumns = False
            .AllowUserToResizeRows = False
            '.Columns("Vermerk").AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill

            '.Columns("Vermerk").MinimumWidth = 150
            .Columns("zollArt_id").Visible = False
            .Columns("zollArt_id").ReadOnly = True
            .Columns("KundenNr").HeaderText = "KundenNr"
            .Columns("KundenNr").Width = 80
            ' .Columns("KundenNr").Visible = False
            .Columns("KundenNr").ReadOnly = True
            .Columns("zollArt_Status").HeaderText = "Status"
            .Columns("zollArt_Status").ReadOnly = True
            .Columns("zollArt_Status").Visible = False

            'Exit Sub
            '.Columns("zollArt_Verarbeitungskennzeichen").Visible = False
            .Columns("zollArt_Artikelnummer").HeaderText = "Artikelnummer"
            .Columns("zollArt_Artikelnummer").Width = 110
            .Columns("zollArt_Artikelnummer").ReadOnly = True

            ' .Columns("zollArt_Dynamische_Ergaenzung_1").HeaderText = "Ergänzung 1"
            ' .Columns("zollArt_Dynamische_Ergaenzung_2").HeaderText = "Ergänzung 2"
            .Columns("zollArt_Warencodenummer").HeaderText = "Tarifnummer"
            .Columns("zollArt_Warencodenummer").Width = 150
            .Columns("zollArt_Warencodenummer").ReadOnly = True
            '   .Columns("zollArt_Zusatzcode_1").HeaderText = "Zusatz 1"
            '   .Columns("zollArt_Zusatzcode_2").HeaderText = "Zusatz 2"
            .Columns("zollArt_Kennzeichen_Gueltig").Visible = False
            .Columns("zollArt_Kurzbezeichnung").HeaderText = "Kurzbezeichnung"
            .Columns("zollArt_Kurzbezeichnung").MinimumWidth = 220
            .Columns("zollArt_Kurzbezeichnung").AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
            .Columns("zollArt_Kurzbezeichnung").ReadOnly = True
            .Columns("zollArt_Warenbeschreibung").Visible = False
            '.Columns("zollArt_Warenbeschreibung").MinimumWidth = 150
            '.Columns("zollArt_Warenbeschreibung").AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill

            '.Columns("zollArt_Statistische_Maßeinheit").Visible = False
            '.Columns("zollArt_Statistische_Maßeinheit_Qualifikator").Visible = False
            '.Columns("zollArt_Besondere_Maßeinheit").Visible = False
            '.Columns("zollArt_Besondere_Maßeinheit_Qualifikator").Visible = False
            '.Columns("zollArt_Gewicht").Visible = False
            '.Columns("zollArt_Menge").Visible = False
            ' .Columns("Erfassungsdatum").HeaderText = "XXXXX"
            '  .Columns("Aenderungsdatum").HeaderText = "XXXXX"
            '  .Columns("Sachbearbeiter").HeaderText = "XXXXX"

            .Columns("Aenderungsdatum").Width = 125
            .Columns("Erfassungsdatum").Width = 125
            .Columns("Erfassungsdatum").ReadOnly = True
            .Columns("Aenderungsdatum").HeaderText = "Änderungsdatum"
            .Columns("Aenderungsdatum").ReadOnly = True
            .Columns("Sachbearbeiter").ReadOnly = True

            If .Columns("clmnVerarbeiten") Is Nothing Then
                Dim c As New DataGridViewCheckBoxColumn
                c.Name = "clmnVerarbeiten"
                c.HeaderText = "ATLAS"
                c.Width = 50
                c.ReadOnly = False
                c.Visible = False
                .Columns.Insert(0, c)
            End If

            If benutzeransicht Then

                .Columns("Erfassungsdatum").Visible = False
                .Columns("Aenderungsdatum").Visible = False
                .Columns("Sachbearbeiter").Visible = False
                .Columns("KundenNr").Visible = False
                .Columns("zollArt_Warenbeschreibung").Visible = True
                .Columns("zollArt_Warenbeschreibung").HeaderText = "Warenbeschreibung"
                .Columns("zollArt_Warenbeschreibung").MinimumWidth = 350
                .Columns("zollArt_Warenbeschreibung").AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill

            End If
        End With


    End Sub

    Private Sub dgvLKW_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles dgvZollArtikel.CellValueChanged
        'btnDel.Enabled = True
    End Sub
    Private Sub dgvZollArtikel_SelectionChanged(sender As Object, e As EventArgs) Handles dgvZollArtikel.SelectionChanged
        btnEdit.Enabled = (dgvZollArtikel.SelectedCells.Count > 0)
    End Sub

    Private Sub btnDel_Click(sender As Object, e As EventArgs) Handles btnDel.Click
        If dgvZollArtikel.SelectedRows.Count > 0 Then
            If dgvZollArtikel.CurrentRow.Selected Then
                If vbYes = MsgBox("Möchten Sie den Eintrag wirklich löschen?", vbYesNoCancel) Then
                    dgvZollArtikel.Rows.Remove(dgvZollArtikel.CurrentRow)
                    save()
                End If
            End If
        End If

        'If dgvZollArtikel.SelectedRows.Count > 0 Then
        '    If vbYes = MsgBox("Möchten Sie '" & dgvZollArtikel.SelectedRows.Count & "' Einträge wirklich löschen?", vbYesNoCancel) Then
        '        For Each r In dgvZollArtikel.SelectedRows
        '            If dgvZollArtikel.CurrentRow.Selected Then
        '                If vbYes = MsgBox("Möchten Sie den Eintrag wirklich löschen?", vbYesNoCancel) Then
        '                    dgvZollArtikel.Rows.Remove(dgvZollArtikel.CurrentRow)
        '                    save()
        '                End If
        '            End If
        '        Next
        '    End If
        'End If

    End Sub

    Private Sub save()
        Try
            Me.dataAdapterLKW.Update(CType(Me.bindingSDL.DataSource, DataTable))
        Catch ex As Exception
            MsgBox(ex.Message, "Es ist ein Fehler aufgetreten:")
        End Try
    End Sub

    Private Sub btnNew_Click(sender As Object, e As EventArgs) Handles btnNew.Click
        showDetails(kdnr, True, parentKundenblatt)
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles btnEdit.Click
        If dgvZollArtikel.SelectedCells.Count > 0 Then
            showDetails(dgvZollArtikel.CurrentRow.Cells("KundenNr").Value, False, parentKundenblatt, dgvZollArtikel.CurrentRow.Cells("zollArt_id").Value)
        End If
    End Sub

    Sub showDetails(kdnr As String, newEntry As Boolean, parentKundenblatt As Boolean, Optional cursorAtID As String = "")
        Dim newartikel As New frmAddArtikelATLAS
        If parentKundenblatt Then newartikel.kdNr = kdnr
        newartikel.parentKundenblatt = parentKundenblatt
        newartikel.newEntry = newEntry
        newartikel.cursorAtID = cursorAtID
        newartikel.editAllowed = editAllowed
        newartikel.Show()
        AddHandler newartikel.FormClosed, Sub()
                                              '   Me_Load(Me, New EventArgs)
                                          End Sub
    End Sub

    Private Sub dgvZollArtikel_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgvZollArtikel.CellDoubleClick
        Button1_Click(btnEdit, New EventArgs)
    End Sub

    Function getZollArtIDs() As List(Of Integer)
        Dim ii As New List(Of Integer)
        For Each r As DataGridViewRow In dgvZollArtikel.Rows
            If r.Cells("clmnVerarbeiten").Value Then
                ii.Add(r.Cells("zollArt_id").Value)
            End If
        Next
        Return ii
    End Function

    Private Sub btnAtlas_Click(sender As Object, e As EventArgs) Handles btnAtlas.Click
        Dim ii As List(Of Integer) = getZollArtIDs()
        If ii.Count = 0 Then Exit Sub
        If vbYes = MsgBox("Möchten Sie wirklich " & ii.Count & " Zeilen einarbeiten?", vbYesNoCancel) Then
            'FlatButton4.PerformClick()
            Dim artikel As List(Of cArtikel) = SQLZoll.getArtikelByIDs(ii)
            For Each a In artikel
                If a.status <> "" And a.status <> "Angelegt" And a.status <> "Erfassung" Then
                    a.Verarbeitungskennzeichen = "1"
                Else
                    a.Verarbeitungskennzeichen = "0"
                End If
            Next

            'zollArt_id
            'artikel.Add(initArtikel)
            frmStatusFSSAtlas.artikel = artikel
            frmStatusFSSAtlas.Show()
        End If
        'If btnUpdate.Enabled Then
        ' btnUpdate.PerformClick()
        ' End If

    End Sub


    Private Sub genExcelArtikelstamm()

        Me.Cursor = Cursors.WaitCursor
        Dim exclApp As Object 'as Application
        Dim Datei As Object 'as WorkBook
        'Dim Blatt As Object 'as WorkSheet
        exclApp = CreateObject("Excel.Application")


        Try
            If Not My.Computer.FileSystem.DirectoryExists(Environment.GetFolderPath(Environment.SpecialFolder.Personal) & "\VERAG\tmp\") Then
                My.Computer.FileSystem.CreateDirectory(Environment.GetFolderPath(Environment.SpecialFolder.Personal) & "\VERAG\tmp\")
            End If

            Dim strFileName As String = Environment.GetFolderPath(Environment.SpecialFolder.Personal) & "\VERAG\tmp\tmp.xlsx"
            With exclApp
                .DisplayAlerts = False

                .Visible = False
                Datei = .Workbooks.Open(AppDomain.CurrentDomain.BaseDirectory & "Resources\Artikelstamm.xlsx")
                '  Blatt = Datei.Worksheets("DIENSTPLAN")
                .Visible = True
                '   .DisplayAlerts = exclApp.WdAlertLevel.wdAlertsNone
                Try : Datei.SaveAs(strFileName) : Catch : End Try

            End With

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Me.Cursor = Cursors.Default

    End Sub



    Private Sub importExcel()
        Dim kdnrTmp As String = ""
        If kdnr > 0 Then
            kdnrTmp = kdnr
        Else
            kdnrTmp = InputBox("Bitte geben Sie die Kundennummer ein: ", "Daten aus Excel Einlesen", "")
            If kdnrTmp.Trim = "" Then MsgBox("Kundennummer ungültig") : Exit Sub
            If Not IsNumeric(kdnrTmp) Then MsgBox("Kundennummer ungültig") : Exit Sub
            If Not kundenSQL.existByKdNr(kdnrTmp) Then MsgBox("Kundennummer ungültig") : Exit Sub
        End If



        Dim fd As New OpenFileDialog
        fd.Filter = "Excel Dateien|*.xls;*.xlsx"
        Dim result As DialogResult = fd.ShowDialog()

        If Not fd.FileName.EndsWith(".xls") And Not fd.FileName.EndsWith(".xlsx") Then
            Me.Cursor = Cursors.Default
            Exit Sub
        End If

        If result = System.Windows.Forms.DialogResult.OK Then


            Me.Cursor = Cursors.WaitCursor
            Dim exclApp As New Excel.Application 'Object 'as Application
            Dim Datei As Excel.Workbook ' 'as WorkBook
            Dim Blatt As Excel.Worksheet 'Object 'as WorkSheet

            With exclApp
                Try

                    .CutCopyMode = False

                    .DisplayAlerts = False


                    Datei = .Workbooks.Open(fd.FileName)
                    Blatt = Datei.Worksheets(1)

                    Datei.Activate()

                    Try
                        Blatt.ShowAllData() 'Falls Filter ausgewählt wurde
                    Catch ex As Exception

                    End Try

                    Dim startFound As Boolean = False
                    Dim endFound As Boolean = False


                    Dim startRows As Integer = 2
                    While startRows < Blatt.UsedRange.Rows.Count
                        If Not Blatt.Range("B" & startRows) Is Nothing And Blatt.Range("B" & startRows).Value.ToString.Contains("Artikel-Nr") Then
                            startRows += 1
                            startFound = True
                            Exit While
                        End If
                        startRows += 1
                    End While

                    If startFound = False Then
                        MsgBox("Falsches Format!")
                        Me.Cursor = Cursors.Default
                        Exit Sub
                    End If

                    Dim endRows As Integer = startRows
                    While endRows < Blatt.UsedRange.Rows.Count
                        If Not Blatt.Range("B" & endRows) Is Nothing AndAlso Not Blatt.Range("B" & endRows).Value Is Nothing AndAlso Not Blatt.Range("B" & endRows).Value.ToString.Trim = "" Then
                            endFound = True
                        Else
                            Exit While
                        End If
                        endRows += 1
                    End While
                    endRows -= 1

                    If Not startFound Or Not endFound Then
                        MsgBox("Keine Daten vorhanden!")
                        Me.Cursor = Cursors.Default
                        Exit Sub
                    End If



                    'Laden des Bereiches aus dem Excel:
                    Dim myRange As Excel.Range
                    myRange = Blatt.Range("B" & startRows & ":G" & endRows & "")
                    Dim myArray As Object(,) '<-- declared as 2D Array
                    myArray = myRange.Value    'store the content of each cell
                    'myArray(i_soll2 - startRows + 1, 4)


                    'Prüfung
                    Try
                        For i As Integer = 1 To endRows - startRows + 1 Step 1
                            Dim c As Boolean
                            If myArray(i, 1) Is Nothing Or myArray(i, 1).ToString() = "" Or myArray(i, 1).ToString().Length > 35 Then c = True : fehler(i, 1, myArray(i, 1).ToString())
                            If Not myArray(i, 2) Is Nothing Then
                                If myArray(i, 2).ToString().Length > 200 Then c = True : fehler(i, 2, myArray(i, 2).ToString())
                            End If
                            If Not myArray(i, 3) Is Nothing Then
                                If myArray(i, 3).ToString().Length > 200 Then c = True : fehler(i, 3, myArray(i, 3).ToString())
                            End If
                            If myArray(i, 4) Is Nothing Or myArray(i, 4).ToString() = "" Or myArray(i, 4).ToString().Replace(" ", "").Replace(".", "").Length > 11 Or Not IsNumeric(myArray(i, 4).ToString().Replace(" ", "").Replace(".", "")) Then c = True : fehler(i, 4, myArray(i, 4).ToString())
                            If myArray(i, 5) Is Nothing Or myArray(i, 5).ToString() = "" Or myArray(i, 5).ToString().Length > 60 Then c = True : fehler(i, 5, myArray(i, 5).ToString())
                            If myArray(i, 6) Is Nothing Or myArray(i, 6).ToString() = "" Or myArray(i, 6).ToString().Length > 240 Then c = True : fehler(i, 6, myArray(i, 6).ToString())

                            If c Then
                                Me.Cursor = Cursors.Default
                                Exit Sub
                            End If
                        Next
                    Catch ex As Exception
                        MsgBox("Fehler bei der Prüfung!" & ex.Message)
                        Me.Cursor = Cursors.Default
                        Exit Sub
                    End Try


                    Dim artikel As New List(Of cArtikel)
                    Try
                        For i As Integer = 1 To endRows - startRows + 1 Step 1
                            Dim a As New cArtikel
                            a.Artikelnummer = myArray(i, 1).ToString
                            If Not myArray(i, 2) Is Nothing Then a.Dynamische_Ergänzung_1 = myArray(i, 2).ToString
                            If Not myArray(i, 3) Is Nothing Then a.Dynamische_Ergänzung_2 = myArray(i, 3).ToString
                            a.Warencodenummer = myArray(i, 4).ToString.Replace(" ", "").Replace(".", "")
                            a.Kurzbezeichnung = myArray(i, 5).ToString
                            a.Warenbeschreibung = myArray(i, 6).ToString
                            a.Verarbeitungskennzeichen = "0"
                            a.Kundennummer = kdnrTmp

                            a.status = "Angelegt"

                            artikel.Add(a)
                        Next
                    Catch ex As Exception
                        MsgBox("Fehler beim Einlesen der Excel-Datei!" & vbNewLine & vbNewLine & ex.Message & ex.StackTrace)
                        Me.Cursor = Cursors.Default
                        Exit Sub
                    End Try

                    Try
                        For Each a In artikel
                            Dim id_tmp = SQLZoll.insertArtikel(a)
                            If id_tmp < 0 Then
                                Throw New Exception
                            Else
                                a.zollArt_id = id_tmp
                            End If
                        Next
                    Catch ex As Exception
                        MsgBox("Fehler beim Einlesen in die Datenbank!" & vbNewLine & vbNewLine & ex.Message & ex.StackTrace)
                        Me.Cursor = Cursors.Default
                        Exit Sub
                    End Try

                    If False Then 'ATLAS
                        If vbYes = MsgBox(artikel.Count & " Datensätze wurden eingelesen. " & vbNewLine & vbNewLine & " Sollen die Daten in ATLAS eingelesen werden?", vbYesNo) Then 'einlesen
                            Try

                                frmStatusFSSAtlas.artikel = artikel
                                frmStatusFSSAtlas.Show()
                                ' AddHandler frmStatusFSSAtlas.FormClosed, AddressOf Me_Load

                            Catch ex As Exception
                                MsgBox("Fehler beim Einlesen in ATLAS!" & vbNewLine & vbNewLine & ex.Message & ex.StackTrace)
                                Me.Cursor = Cursors.Default
                                Exit Sub
                            End Try

                        End If
                    End If


                    .Visible = False

                    '   AddHandler exclApp.WorkbookBeforeClose, AddressOf BeforeBookClose
                    'Excelobjekte freistellten
                    '  For Each obj In New Object() {exclApp, Datei, Datei, Blatt, Blatt}
                    ' System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj)
                    ' Next
                    Datei.Close()

                    Me_Load(Me, New EventArgs)

                    MsgBox("Import erfolgreich!")
                Catch ex As Exception
                    '
                    Me.Cursor = Cursors.Default
                    MsgBox("FEHLER! Datei im richtigen Format?" & vbNewLine & vbNewLine & ex.Message & ex.StackTrace)
                    '  Return False
                End Try
            End With
            'Return True
        Else
            MsgBox("Keine Datei ausgewählt!")
        End If

        Me.Cursor = Cursors.Default


    End Sub
    Sub fehler(zeile, spalte, wert)
        MsgBox("Es sind fehlerhafte Daten vorhanden! " & vbNewLine & vbNewLine & "Zeile: " & zeile & "  Spalte: " & spalte & "  Wert: '" & wert & "'" & vbNewLine & vbNewLine & " Bitte prüfen und erneut versuchen!")
    End Sub

    Private Sub ExcelEinlesenToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ExcelEinlesenToolStripMenuItem.Click
        importExcel()
    End Sub

    Private Sub ExcelListeÖffnenToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ExcelListeÖffnenToolStripMenuItem.Click
        genExcelArtikelstamm()
    End Sub

    Private Sub FlatButton1_Click(sender As Object, e As MouseEventArgs) Handles btnExcel.MouseClick, btnExcel.Click
        btnExcel.ContextMenuStrip = cntxtExcel
        btnExcel.ContextMenuStrip.Show(btnExcel, e.Location)
        ' FlatButton1.ContextMenuStrip.Show()
    End Sub

    Private Sub FlatButton1_MouseLeave(sender As Object, e As EventArgs) Handles btnExcel.MouseLeave
        ' FlatButton1.ContextMenuStrip.Hide()

    End Sub


    Private Sub TarifnummerKopierenToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles TarifnummerKopierenToolStripMenuItem.Click
        If dgvZollArtikel.SelectedRows.Count > 0 Then
            Dim tmp As String = dgvZollArtikel.SelectedRows(0).Cells("zollArt_Warencodenummer").Value
            Clipboard.SetDataObject(tmp, True)
            Dim iData As IDataObject = Clipboard.GetDataObject()
            If iData.GetDataPresent(DataFormats.Text) Then
                If tmp = CType(iData.GetData(DataFormats.Text), String) Then
                    Exit Sub
                End If
            End If
            MsgBox("Problem beim Zwischenspeichern der Daten!")
        End If

    End Sub

    Private Sub FlatButton1_Click(sender As Object, e As EventArgs) Handles btnRefresh.Click, btnExcel.MouseClick, btnExcel.Click
        initSearch()
    End Sub

    Private Sub KurzbeschreibungKopierenToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles KurzbeschreibungKopierenToolStripMenuItem.Click
        If dgvZollArtikel.SelectedRows.Count > 0 Then
            Dim tmp As String = dgvZollArtikel.SelectedRows(0).Cells("zollArt_Kurzbezeichnung").Value
            Clipboard.SetDataObject(tmp, True)
            Dim iData As IDataObject = Clipboard.GetDataObject()
            If iData.GetDataPresent(DataFormats.Text) Then
                If tmp = CType(iData.GetData(DataFormats.Text), String) Then
                    Exit Sub
                End If
            End If
            MsgBox("Problem beim Zwischenspeichern der Daten!")
        End If
    End Sub

    Private Sub BeschreibungToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles BeschreibungToolStripMenuItem.Click
        If dgvZollArtikel.SelectedRows.Count > 0 Then
            Dim tmp As String = dgvZollArtikel.SelectedRows(0).Cells("zollArt_Warenbeschreibung").Value
            Clipboard.SetDataObject(tmp, True)
            Dim iData As IDataObject = Clipboard.GetDataObject()
            If iData.GetDataPresent(DataFormats.Text) Then
                If tmp = CType(iData.GetData(DataFormats.Text), String) Then
                    Exit Sub
                End If
            End If
            MsgBox("Problem beim Zwischenspeichern der Daten!")
        End If
    End Sub

    Private Sub pnl_Paint(sender As Object, e As PaintEventArgs) Handles pnlOptions1.Paint

    End Sub

    Private Sub btnTarifnummerKopieren_Click(sender As Object, e As EventArgs) Handles btnTarifnummerKopieren.Click
        TarifnummerKopierenToolStripMenuItem.PerformClick()
    End Sub

    Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles Button1.Click
        KurzbeschreibungKopierenToolStripMenuItem.PerformClick()
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        BeschreibungToolStripMenuItem.PerformClick()
    End Sub

    Private Sub FlatButton1_Click_1(sender As Object, e As EventArgs) Handles FlatButton1.Click

        If Not VERAG_PROG_ALLGEMEIN.cBerechtignunen.CHECK_BERECHTIGUNG_bool("Kunden_Vermerke_Erfassen", "SDL") Then Exit Sub

        Dim f As New VERAG_PROG_ALLGEMEIN.frmKdVermerkAdd(kdnr, "ZOLLTARIF")
        If f.ShowDialog(Me) = vbOK Then
            initDGVVermerke()
        End If


    End Sub

    
    Private Sub dgvKdVermerke_CellMouseDown(sender As Object, e As DataGridViewCellMouseEventArgs) Handles dgvKdVermerke.CellMouseDown
        If e.Button = Windows.Forms.MouseButtons.Right Then
            dgvKdVermerke.ClearSelection()
            dgvKdVermerke.Rows(e.RowIndex).Selected = True
            cntxtVermerk.Show(System.Windows.Forms.Cursor.Position)
        End If
    End Sub

    Private Sub ToolStripMenuItem1_Click(sender As Object, e As EventArgs) Handles ToolStripMenuItem1.Click
        If Not VERAG_PROG_ALLGEMEIN.cBerechtignunen.CHECK_BERECHTIGUNG_bool("Kunden_Vermerke_Erfassen", "SDL") Then Exit Sub
        If dgvKdVermerke.SelectedRows.Count > 0 Then
            If IsNumeric(dgvKdVermerke.SelectedRows(0).Cells("kvm_id").Value) Then
                If vbYes = MsgBox("Möchten Sie den Eintrag wirklich löschen?", vbYesNoCancel) Then
                    If VERAG_PROG_ALLGEMEIN.cKundenVermerke.DELETE(dgvKdVermerke.SelectedRows(0).Cells("kvm_id").Value) Then
                        initDGVVermerke()
                    End If
                End If
            End If
        End If
    End Sub

    Private Sub Label6_Click(sender As Object, e As EventArgs)

    End Sub

    Private Sub TabelleInExcelÖffnenToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles TabelleInExcelÖffnenToolStripMenuItem.Click
        FUNC.genExcelFromDGV_NEW(dgvZollArtikel)
    End Sub

    Private Sub btnDelAll_Click(sender As Object, e As EventArgs) Handles btnDelAll.Click
        If vbYes = MsgBox("Möchten Sie wirklich ALLE Einträge löschen (" & dgvZollArtikel.RowCount & ")?", vbYesNoCancel) Then
            Dim SQL As New VERAG_PROG_ALLGEMEIN.SQL
            If SQL.doSQL("DELETE [tblZollArtikel] where KundenNr='" & kdnr & "'", "FMZOLL") Then
                initSearch()
            End If
        End If
    End Sub
End Class
