Imports System.ComponentModel
Imports System.Data.SqlClient

Public Class frmFahrerDatenSuche
    Dim SQL As New VERAG_PROG_ALLGEMEIN.SQL

    Private Sub txtSuche_KeyDown(sender As Object, e As KeyEventArgs) Handles txtSuche.KeyDown
        If e.KeyCode = Keys.Return Then
            takeIt()
        End If
        If e.KeyCode = Keys.Down Then
            If dgvFD.SelectedRows.Count > 0 AndAlso dgvFD.SelectedRows(0).Index < dgvFD.RowCount - 1 Then
                Dim id = dgvFD.SelectedRows(0).Index
                dgvFD.ClearSelection()
                dgvFD.Rows(id + 1).Selected = True
            End If
        End If
        If e.KeyCode = Keys.Up Then
            If dgvFD.SelectedRows.Count > 0 AndAlso dgvFD.SelectedRows(0).Index > 0 Then
                Dim id = dgvFD.SelectedRows(0).Index
                dgvFD.ClearSelection()
                dgvFD.Rows(id - 1).Selected = True
            End If
        End If
    End Sub


    Private Sub txtSuche_TextChanged(sender As Object, e As EventArgs) Handles txtSuche.TextChanged
        showDGV()
    End Sub

    Sub showDGV()

        Dim sort As String = " fd_fraechter" 'default

        '! Der Sort-Index muss gespeichert und beim erneuten Anzeigen der Tabelle berücksichtigt werden, da durch die Einschränktung 'SELECT TOP' nur das Ergebnis der ersten Zeilen sortiert werden würde
        Dim sortnr As Integer = 0
        Dim sortorder As Integer = 0
        Try
            With dgvFD
                If .SortedColumn.Index = 1 Then sort = "fd_fraechter"
                If .SortedColumn.Index = 2 Then sort = "fd_lkw"
                If .SortedColumn.Index = 3 Then sort = "fd_fahrerName"
                If .SortedColumn.Index = 4 Then sort = "fd_tel"
                If .SortedColumn.Index = 5 Then sort = "fd_passNummer"

                If .SortOrder = 1 Then sort = sort & " ASC"
                If .SortOrder = 2 Then sort = sort & " DESC"
                sortnr = .SortedColumn.Index
                sortorder = (.SortOrder - 1)
            End With
        Catch ex As Exception
        End Try


        Dim sqlstr As String = "SELECT top 100 fd_id, fd_fraechter, fd_lkw, fd_fahrerName, fd_tel, fd_passNummer,fd_wohnAdresse,fd_dsId FROM tblFahrerDaten "
        Dim where As String = ""
        Dim andstr As String = ""
        If txtSuche.Text <> "" Then
            Dim suchstr As String = txtSuche.Text
            suchstr = suchstr.Replace(",", " ")
            suchstr = suchstr.Replace(";", " ")
            Dim split = suchstr.Split(" ")
            For Each s In split
                where &= andstr & " (fd_fraechter LIKE '%" & s & "%' OR fd_lkw LIKE '%" & s & "%'  OR fd_fahrerName LIKE '%" & s & "%' " &
                    " OR fd_tel LIKE '%" & s & "%' OR fd_passNummer LIKE '%" & s & "%') "
                andstr = " AND "
            Next
        End If
        If where <> "" Then sqlstr &= " WHERE " & where
        sqlstr &= " ORDER BY " & sort

        dgvFD.DataSource = SQL.loadDgvBySql(sqlstr, "FMZOLL")

        setColumns()

        'Sortierung nach sort-Index - Default ist der Firmenwortlaut
        Try
            If sortorder = 0 Then
                dgvFD.Sort(dgvFD.Columns(sortnr), ListSortDirection.Ascending)
            Else
                dgvFD.Sort(dgvFD.Columns(sortnr), ListSortDirection.Descending)
            End If

        Catch ex As Exception
            dgvFD.Sort(dgvFD.Columns(1), ListSortDirection.Descending)
        End Try



        '        lblInfo.Text = dgvFD.RowCount & " ausgewählt"
        '       If dgvFD.RowCount = 100 Then lblInfo.Text = lblInfo.Text & " (Die Suche wurde auf 100 Ergebnisse beschränkt.)"

    End Sub

    Sub setColumns()
        With dgvFD
            .RowTemplate.Height = 20
            .RowTemplate.DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopLeft
            .AllowUserToAddRows = False
            .AllowUserToDeleteRows = False
            .AllowUserToOrderColumns = False
            .AllowUserToResizeColumns = False
            .AllowUserToResizeRows = False
            .RowTemplate.ReadOnly = True
            .RowHeadersVisible = False
            .SelectionMode = DataGridViewSelectionMode.FullRowSelect

            .AllowUserToOrderColumns = True
            
            .Columns(0).Visible = False     'ID nicht anzeigen
            .Columns("fd_dsId").Visible = False     'ID nicht anzeigen

            .Columns(1).Width = 250
            .Columns(1).HeaderText = "Frächter"

            .Columns(2).Width = 120
            .Columns(2).HeaderText = "LKW-Kennz."

            .Columns(3).MinimumWidth = 200
            .Columns(3).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
            .Columns(3).HeaderText = "Fahrer"

            .Columns(4).Width = 200
            .Columns(4).HeaderText = "Mob"

            .Columns(5).Width = 180
            .Columns(5).HeaderText = "Pass -Nummer"
            .Columns("fd_wohnAdresse").Visible = False


        End With
    End Sub

    Private Sub btnOK_Click(sender As Object, e As EventArgs) Handles btnOK.Click
        takeIt()
    End Sub

    Sub takeIt()
        If dgvFD.SelectedRows.Count > 0 Then
            Me.DialogResult = Windows.Forms.DialogResult.OK
            Me.Close()
        Else
            Me.DialogResult = Windows.Forms.DialogResult.None
        End If
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs)
        If dgvFD.SelectedRows.Count > 0 Then
            If vbYes = MsgBox("Eintag wirklich löschen?", vbYesNoCancel) Then
                cFahrerDaten.delData(dgvFD.SelectedRows(0).Cells(0).Value)
                showDGV()
            End If
        End If
    End Sub


    Private Sub dgvFD_DoubleClick(sender As Object, e As EventArgs) Handles dgvFD.DoubleClick
        takeIt()
    End Sub

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

    End Sub

    Private Sub frmFahrerDatenSuche_Shown(sender As Object, e As EventArgs) Handles Me.Shown
        txtSuche.Focus()
    End Sub
End Class

Public Class cFahrerDaten
    Property fd_id As Integer = -1
    Property fd_fraechter As String = ""
    Property fd_lkw As String = ""
    Property fd_fahrerName As String = ""
    Property fd_tel As String = ""
    Property fd_passNummer As String = ""
    Property fd_wohnAdresse As String = ""
    Property fd_dsId As String = ""


    Public Shared Function setData(ByRef s As cFahrerDaten, Optional ByRef id As Integer = -1) As Boolean

        Dim sql = ""
        If s.fd_id >= 0 Then
            sql = "UPDATE tblFahrerDaten " &
                       " SET fd_fraechter=@fd_fraechter,fd_lkw=@fd_lkw,fd_fahrerName=@fd_fahrerName,fd_tel=@fd_tel,fd_passNummer=@fd_passNummer,fd_wohnAdresse=@fd_wohnAdresse,fd_dsId=@fd_dsId " &
                       " WHERE fd_id=@fd_id "

        Else
            sql = "INSERT INTO tblFahrerDaten " &
                       "(fd_fraechter,fd_lkw,fd_fahrerName,fd_tel,fd_passNummer,fd_wohnAdresse,fd_dsId) VALUES " &
                       "(@fd_fraechter,@fd_lkw,@fd_fahrerName,@fd_tel,@fd_passNummer,@fd_wohnAdresse,@fd_dsId)"
        End If
        Using conn As SqlConnection = cSqlDb.GetNewOpenConnectionFMZOLL()
            Using cmd As New SqlCommand(sql, conn)
                cmd.Parameters.AddWithValue("@fd_fraechter", s.fd_fraechter)
                cmd.Parameters.AddWithValue("@fd_lkw", s.fd_lkw)
                cmd.Parameters.AddWithValue("@fd_fahrerName", s.fd_fahrerName)
                cmd.Parameters.AddWithValue("@fd_tel", s.fd_tel)
                cmd.Parameters.AddWithValue("@fd_passNummer", s.fd_passNummer)
                cmd.Parameters.AddWithValue("@fd_wohnAdresse", s.fd_wohnAdresse)
                cmd.Parameters.AddWithValue("@fd_dsId", s.fd_dsId)
                cmd.Parameters.AddWithValue("@fd_id", If(id > 0, id, s.fd_id))

                Try
                    cmd.ExecuteNonQuery()
                    If id < 0 Then
                        Dim newcmd As New SqlCommand("SELECT @@IDENTITY", conn)
                        id = CInt(newcmd.ExecuteScalar)
                        s.fd_id = id
                    End If
                    Return True
                Catch ex As Exception
                    MsgBox(System.Reflection.MethodInfo.GetCurrentMethod.Name & ": Datensatz kann nicht gespeichert werden!" & vbCrLf & vbCrLf & ex.Message, vbExclamation, "Fehler beim Speichern Data")
                End Try
            End Using
        End Using
        Return False
    End Function

    Public Shared Function delData(ByVal id As Integer) As Boolean
        Dim sql As String = "DELETE FROM tblFahrerDaten WHERE fd_id = " & id
        Using conn As SqlConnection = cSqlDb.GetNewOpenConnectionFMZOLL()
            Using cmd As New SqlCommand(sql, conn)
                Try
                    cmd.ExecuteNonQuery()
                Catch ex As SqlException
                    MsgBox(System.Reflection.MethodInfo.GetCurrentMethod.Name & ": Datensatz kann nicht gelöscht werden!" & vbCrLf & vbCrLf & ex.Message, vbExclamation, "Fehler beim Löschen")
                    Return False
                End Try
            End Using
        End Using
        Return True
    End Function

    Public Shared Function LOADByKdNrDate(fd_id As Integer) As cFahrerDaten
        Try
            Dim FD As New cFahrerDaten
            Using conn As SqlConnection = cSqlDb.GetNewOpenConnectionFMZOLL()
                Using cmd As New SqlCommand("SELECT TOP 1 * FROM tblFahrerDaten WHERE fd_id=@fd_id ", conn)
                    cmd.Parameters.AddWithValue("@fd_id", fd_id)
                    Dim dr = cmd.ExecuteReader()
                    If dr.Read Then

                        FD.fd_id = fd_id
                        FD.fd_fraechter = VERAG_PROG_ALLGEMEIN.SQL.checkNullReturnValue(dr.Item("fd_fraechter"), "")
                        FD.fd_lkw = VERAG_PROG_ALLGEMEIN.SQL.checkNullReturnValue(dr.Item("fd_lkw"), "")
                        FD.fd_fahrerName = VERAG_PROG_ALLGEMEIN.SQL.checkNullReturnValue(dr.Item("fd_fahrerName"), "")
                        FD.fd_tel = VERAG_PROG_ALLGEMEIN.SQL.checkNullReturnValue(dr.Item("fd_tel"), "")
                        FD.fd_passNummer = VERAG_PROG_ALLGEMEIN.SQL.checkNullReturnValue(dr.Item("fd_passNummer"), "")
                        FD.fd_wohnAdresse = VERAG_PROG_ALLGEMEIN.SQL.checkNullReturnValue(dr.Item("fd_wohnAdresse"), "")
                        FD.fd_dsId = VERAG_PROG_ALLGEMEIN.SQL.checkNullReturnValue(dr.Item("fd_dsId"), "")

                        dr.Close()
                        Return FD
                    End If
                    dr.Close()
                End Using
            End Using
        Catch ex As Exception
            MsgBox("Fehler in der Funktion '" & System.Reflection.MethodInfo.GetCurrentMethod.Name & "'" & vbNewLine & vbNewLine & ex.Message & vbNewLine & vbNewLine & ex.StackTrace)
        End Try
        Return Nothing
    End Function
End Class