Imports System.Data.SqlClient

Public Class frmKundenSucheUebersicht
    Dim SQL As New SQL

    Private Sub btnSuche_Click(sender As Object, e As EventArgs) Handles btnSuche.Click
        Me.Cursor = Cursors.WaitCursor

        Dim sqlstr As String = ""
        sqlstr &= "SELECT TOP (" & txtMaxSrch.Text & ") [AdressenNr],[Auswahl],[Ordnungsbegriff],RTRIM(LTRIM(isnull([Name 1],'') + ' ' + isnull([Name 2],''))) as [Name],Plz,[Straße],[LandKz],[PLZ],[Ort],[Telefon],[E-Mail],RTRIM(LTRIM(isnull([UstIdKz],'') + ' ' + isnull([UstIdNr],''))) as [UstId] ,[Adressen].[Geändert am],[Adressen].[Sachbearbeiter]" &
                  " FROM [Adressen] INNER JOIN Kunden ON KundenNr=AdressenNr WHERE 1=1 "


        With DataGridView
            '  .DataSource = myTable
            .SET_SQL(sqlstr & getRechnungsAusgangWhere() & " ORDER BY Ordnungsbegriff, AdressenNr", "FMZOLL", getSpedbuchList)
            .LOAD()

            If .RowCount = 0 Then Me.Cursor = Cursors.Default : Exit Sub
            .RowHeadersVisible = False
            .ReadOnly = True
            .SelectionMode = DataGridViewSelectionMode.FullRowSelect
            .MultiSelect = False

         
            .Columns("Auswahl").Width = 30
            .Columns("Auswahl").HeaderText = "A/I"
            .Columns("Auswahl").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
          

            .Columns("AdressenNr").Width = 65
            .Columns("AdressenNr").HeaderText = "KundenNr"
            .Columns("AdressenNr").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            .Columns("AdressenNr").DefaultCellStyle.Padding = New Padding(0, 0, 3, 0)

            
            .Columns("Name").MinimumWidth = 100
            .Columns("Name").AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill

            .Columns("Ordnungsbegriff").MinimumWidth = 200
            .Columns("Ordnungsbegriff").AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill

            .Columns("LandKz").Width = 50
            .Columns("Plz").Width = 50
            .Columns("Ort").MinimumWidth = 150
            .Columns("Ort").AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
            .Columns("Straße").MinimumWidth = 150
            .Columns("Straße").AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
            .Columns("UstId").Width = 100



            lblErgebnis.Text = "Ergebnisse: " & .RowCount
        End With

        Me.Cursor = Cursors.Default
    End Sub


    Function getRechnungsAusgangWhere() As String
        Dim sqlstr = ""

        If txtOrdnungsbegriff.Text <> "" Then sqlstr &= " AND Ordnungsbegriff LIKE @Ordnungsbegriff "
        If txtName.Text <> "" Then sqlstr &= " AND ([Name 1] LIKE @Name OR [Name 2] LIKE @Name ) "
        If txtStrasse.Text <> "" Then sqlstr &= " AND Straße LIKE @Straße "
        If txtLand.Text <> "" Then sqlstr &= " AND LandKz LIKE @LandKz "
        If txtOrt.Text <> "" Then sqlstr &= " AND Ort LIKE @Ort "
        If txtPlz.Text <> "" Then sqlstr &= " AND PLZ LIKE @PLZ "
        If cboFiliale._value <> "" Then sqlstr &= " AND FilialenNr LIKE @FilialenNr "


        If txtKdNr_von.Text <> "" Then
            If txtKdNr_bis.Text <> "" Then
                sqlstr &= " AND (AdressenNr BETWEEN @AdressenNr AND @AdressenNr_bis) "
            Else
                sqlstr &= " AND AdressenNr LIKE @AdressenNr "
            End If
        End If
    
        Return sqlstr
    End Function

    Function getSpedbuchList() As List(Of VERAG_PROG_ALLGEMEIN.SQLVariable)

        Dim list As New List(Of VERAG_PROG_ALLGEMEIN.SQLVariable)


        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Ordnungsbegriff", txtOrdnungsbegriff.Text.Replace("*", "%")))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Name", txtName.Text.Replace("*", "%")))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Straße", txtStrasse.Text.Replace("*", "%")))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("LandKz", txtLand.Text.Replace("*", "%")))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("PLZ", txtPlz.Text.Replace("*", "%")))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Ort", txtOrt.Text.Replace("*", "%")))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("FilialenNr", cboFiliale._value))

        If txtKdNr_bis.Text <> "" Then
            list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("AdressenNr", txtKdNr_von.Text.Replace("*", "")))
            list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("AdressenNr_bis", txtKdNr_bis.Text.Replace("*", "")))
        Else
            list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("AdressenNr", txtKdNr_von.Text.Replace("*", "%")))
        End If
      

        Return list

    End Function
    Private Sub frmSpeditionsbuchSuche_Load(sender As Object, e As EventArgs) Handles Me.Load
        Me.Height = Screen.FromRectangle(Me.Bounds).WorkingArea.Size.Height
        Me.Top = 0

     
        '  cboBerichtRgAbfArt.fillWithSQL(" SELECT [Abfertigungsart],[Abfertigungsbezeichnung] FROM [Abfertigungsarten] ORDER BY Abfertigungsart ", True, "FMZOLL", True)
        ' cboSachbearbeiter.fillWithSQL("SELECT  ID, Name FROM Personal WHERE [Status]<>'L' ORDER BY Name ", True, "FMZOLL", True)
        '     cboSachbearbeiter.fillWithSQL("SELECT  ID,/*cast(ID as nvarchar(3)) +' ' + Name as*/ Name FROM Personal WHERE [Status]<>'L' ORDER BY Name ", False, "FMZOLL", True)
        cboFiliale.fillWithSQL("SELECT FilialenNr, Grenzstelle FROM Filialen WHERE Fil_Status='A' ORDER BY FilialenNr ", True, "FMZOLL", True)
        'ComboBox1.fillWithSQL("SELECT [Abfertigungsart],[Abfertigungsbezeichnung]  FROM [Abfertigungsarten] ORDER BY Abfertigungsart ", True, "FMZOLL")
        Button4.PerformClick()

        If VERAG_PROG_ALLGEMEIN.cAllgemein.STAMMFILIALE <> "" Then
            cboFiliale._allowedValuesFreiText = {VERAG_PROG_ALLGEMEIN.cAllgemein.STAMMFILIALE}
            cboFiliale.changeItem(VERAG_PROG_ALLGEMEIN.cAllgemein.STAMMFILIALE)
            cboFiliale.Enabled = False
        End If

        'Select Case VERAG_PROG_ALLGEMEIN.cAllgemein.FIRMA
        '    Case "IMEX"
        '        cboFiliale._allowedValuesFreiText = {"5501"}
        '        cboFiliale.changeItem("5501")
        '    Case "ATILLA"
        '        cboFiliale._allowedValuesFreiText = {"4801"}
        '        cboFiliale.changeItem("4801")
        'End Select


        If VERAG_PROG_ALLGEMEIN.cBerechtignunen.CHECK_BERECHTIGUNG_bool("ADMIN", Me) Then
            ' btnQSAufteilung.Visible = True
        End If
        If VERAG_PROG_ALLGEMEIN.cBerechtignunen.CHECK_BERECHTIGUNG_bool("ZOLL_Auswertungen", Me) Then
            pnlBerichte.Visible = True
        End If

        If VERAG_PROG_ALLGEMEIN.cAllgemein.FIRMA = "AMBAR" Then
            btnExcelAmbar.Visible = True
        End If


    End Sub
       

    Private Sub DataGridView_CellMouseClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles DataGridView.CellMouseClick
        If e.Button = Windows.Forms.MouseButtons.Right Then
            DataGridView.ClearSelection()
            DataGridView.Rows(e.RowIndex).Selected = True
        End If
    End Sub

    Private Sub DataGridView_CellContentClick(sender As Object, e As EventArgs) Handles DataGridView.DoubleClick
        If DataGridView.SelectedRows.Count = 0 Then Exit Sub

        If VERAG_PROG_ALLGEMEIN.cBerechtignunen.CHECK_BERECHTIGUNG("Kundendaten_bearbeiten", "SDL") = 0 Then
            Dim frmKundenblatt As New frmKundenblatt
            frmKundenblatt.showEdit = True
            frmKundenblatt.kdNr = DataGridView.SelectedRows(0).Cells("AdressenNr").Value
            'frmKundenblatt.BERECHTIGUNEN = BERECHTIGUNEN
            frmKundenblatt.ShowDialog(Me)
        End If
         
        DataGridView.RELOAD()

        '   frmNacherfassung.showAll()
    End Sub

    Private Sub Button1_Click_1(sender As Object, e As EventArgs)
        Dim frmNacherfassung As New frmNacherfassungNEU
        ' frmNacherfassung.cboFiliale.Text = DataGridView.SelectedRows(0).Cells("FilialenNr").Value
        ' frmNacherfassung.txtUnterNr.Text = DataGridView.SelectedRows(0).Cells("UnterNr").Value
        ' frmNacherfassung.txtAbfertigungsnummer.Text = DataGridView.SelectedRows(0).Cells("AbfertigungsNr").Value
        frmNacherfassung.newEntry = True
        '  frmNacherfassung.FMZollPersID = VERAG_PROG_ALLGEMEIN.cglobal.AktiverMitarbeiter.mit_FMZollPersID
        ' frmNacherfassung.initDataBinding()
        frmNacherfassung.ShowDialog(Me)
        DataGridView.RELOAD()
    End Sub

    Private Sub EintragLöschenToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles EintragLöschenToolStripMenuItem.Click
        Exit Sub 'NCH NCIT
        If DataGridView.SelectedRows.Count > 0 Then
            If vbYes = MsgBox("Möchten Sie den Eintrag wirklich löschen?", vbYesNoCancel) Then
                Dim r As DataGridViewRow = DataGridView.SelectedRows(0)
                '   SQL.doSQL("DELETE FROM [Speditionsbuch] WHERE [FilialenNr]='" & r.Cells("FilialenNr").Value & "' AND [AbfertigungsNr] ='" & r.Cells("AbfertigungsNr").Value & "' AND [UnterNr]='" & r.Cells("UnterNr").Value & "' ", "FMZOLL")
                DataGridView.RELOAD()
            End If
        End If
    End Sub



    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        SplitContainer1.Panel2Collapsed = Not SplitContainer1.Panel2Collapsed

        If SplitContainer1.Panel2Collapsed Then
            '    pnlSearch.Height = SplitContainer1.Panel1.Height
            SplitContainer.SplitterDistance -= 150
        Else
            'pnlSearch.Height = SplitContainer1.Height
            SplitContainer.SplitterDistance += 150
        End If

        If Button4.Image Is SDL.My.Resources.Resources.pfeil_unten Then
            Button4.Image = SDL.My.Resources.Resources.pfeil_oben
        Else
            Button4.Image = SDL.My.Resources.Resources.pfeil_unten
        End If
    End Sub


    Private Sub FlatButton7_Click(sender As Object, e As EventArgs) Handles FlatButton7.Click
        DataGridView.RELOAD()
    End Sub

 

    Private Sub btnExcel_Click(sender As Object, e As EventArgs) Handles btnExcel.Click
        Me.Cursor = Cursors.WaitCursor
        cProgramFunctions.genExcelFromDGV_NEW(DataGridView, False)
        Me.Cursor = Cursors.Default
    End Sub



    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        If VERAG_PROG_ALLGEMEIN.cBerechtignunen.CHECK_BERECHTIGUNG_bool("Kundendaten_bearbeiten", "SDL") Then
            Dim frmKundenblatt As New SDL.frmKundenblatt
            frmKundenblatt.showEdit = True
            frmKundenblatt.kdNr = -1
            'frmKundenblatt.BERECHTIGUNEN = BERECHTIGUNEN
            frmKundenblatt.ShowDialog(Me)
            DataGridView.RELOAD()
        Else
            Dim frmKd As New SDL.frmNeuerKunde
            frmKd.openKd = False
            frmKd.timerIntervall = 500
            frmKd.username = VERAG_PROG_ALLGEMEIN.cGlobal.AktiverMitarbeiter.mit_username
            If frmKd.username.Length > 15 Then frmKd.username = frmKd.username.Substring(0, 15)
            '    frmKd.tmpFiliale = VERAG_PROG_ALLGEMEIN.cGlobal.AktiverMitarbeiter.StandartFiliale
            '  frmKd.tmpKdNrKreis = 9
            frmKd.ShowDialog()

            'OPEN!
            If frmKd.kdnr > 0 Then
                DataGridView.RELOAD()
            End If
        End If

    End Sub

    Private Sub frmKundenSucheUebersicht_Shown(sender As Object, e As EventArgs) Handles Me.Shown
        txtOrdnungsbegriff.Focus()
    End Sub

    Private Sub txtOrdnungsbegriff_KeyUp(sender As Object, e As KeyEventArgs) Handles txtOrdnungsbegriff.KeyUp, txtKdNr_bis.KeyUp, txtKdNr_von.KeyUp, cbxAktiv.CheckedChanged
        If e.KeyCode = Keys.Return Then
            btnSuche.PerformClick()
        End If
    End Sub

    Private Sub txtKdNr_von_TextChanged(sender As Object, e As EventArgs) Handles txtKdNr_von.TextChanged
        txtKdNr_bis.Enabled = (txtKdNr_von.Text <> "")
        If txtKdNr_von.Text = "" Then txtKdNr_bis.Text = ""
    End Sub

    Private Sub btnExcelAmbar_Click(sender As Object, e As EventArgs) Handles btnExcelAmbar.Click

        Me.Cursor = Cursors.WaitCursor
        Try

            Dim dt As DataTable = SQL.loadDgvBySql("select AdressenNr,[Name 1],PLZ,   Ort,Straße,LandKz,kde_verzolltBei FROM adressen INNER JOIN tblKundenErweitert ON AdressenNr=kde_KundenNr
            where AdressenNr between 3000000 and 3999999
            And kde_verzolltBei LIKE '%AMBAR%'", "FMZOLL")

            If dt IsNot Nothing And dt.Rows.Count > 0 Then

                cProgramFunctions.genExcelFromDT_NEW(dt)

            End If

        Catch ex As Exception
            MsgBox(ex.Message & ex.StackTrace)

        End Try


    End Sub
End Class