Imports System.Data
Imports System.Data.OleDb

Public Class Form_SN
    Inherits System.Windows.Forms.Form

    ' VARIABILI PER IL DATABASE DA APRIRE
    Dim ConPubs As OleDb.OleDbConnection
    Dim AdaptSql As OleDb.OleDbDataAdapter
    Dim DatPubs As DataSet
    Dim CurrentRecord As Integer
    Dim EditState As Boolean

    ' VARIABILI PER I MESSAGGI DA VISUALIZZARE
    Dim nameapp As String = "Serial Number"
    Dim dbname As String = "pcemail"
    Dim dberror As String = "Database non trovato, nel percorso!"
    Dim dbrecordupdate As String = "Record aggiornato correttamente!"
    Dim dbrecordagg As String = "Record aggiunto correttamente!"
    Dim dbrecordcanc As String = "Eliminare il record selezionato!"
    Dim dbrecordedit As String = "Editare il record selezionato!"
    Dim dbrecordcopia As String = "Copiare il record selezionato!"
    Dim dbrecordnot As String = "Nessuna record selezionato!"
    Dim dbrecordobb As String = "Campo obbligatorio!"
    Dim dbmsgbox As String

    ' CHIUSURA DELLA CONNESSIONE DEL DATABASE
    Private Sub Form_SN_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        ConPubs.Close()
    End Sub

    ' APERTURA DELLA CONNESSIONE DEL DATABASE
    Private Sub Form_SN_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If My.Computer.FileSystem.FileExists(dbname + ".eft") Then

            ConPubs = New OleDb.OleDbConnection
            Dim dblocation As String = System.IO.Directory.GetCurrentDirectory & "\" & dbname & ".eft"
            ConPubs.ConnectionString = "Provider=Microsoft.Jet.oledb.4.0;Data Source='" & dblocation & "'"

            ConPubs.Open()

            Call Populate_Adapter_Dataset()
            Call Dataset_Styles()
            Call CleartTxt()

            ' LOADING DATI NEL COMBOBOX ARTICOLI
            Populate_Combo(ComboBox_SN_Articolo, "CodArticolo", "TArticoli")

            ' LOADING DATI NEL COMBOBOX FORNITORE
            Populate_Combo(ComboBox_SN_Fornitore, "fornitore", "pcemail_fornitore")

            ' LOADING DATI NEL COMBOBOX CERCA IN
            ComboBox_SN_CercaIn.Text = "articolo"

            Call DisableButton()

            Botton_SN_Nuovo.Enabled = True
            Botton_SN_Edita.Enabled = True
            Botton_SN_Elimina.Enabled = True

            Call DisableTxt()

            DataGrid_SN.CaptionText = "Record n. " & DataGrid_SN.CurrentRowIndex + 1 & " di " & DatPubs.Tables("pcemail_seriale").Rows.Count

        Else

            dbmsgbox = MsgBox(dberror, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, nameapp)
            End

        End If
    End Sub

    ' NUMERO DEI RECORD
    Private Sub DataGrid_SN_CurrentCellChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGrid_SN.CurrentCellChanged
        DataGrid_SN.CaptionText = "Record n. " & DataGrid_SN.CurrentRowIndex + 1 & " di " & DatPubs.Tables("pcemail_seriale").Rows.Count
    End Sub

    Sub CleartTxt()
        ComboBox_SN_Articolo.Text = ""
        ComboBox_SN_Fornitore.Text = ""
        TextBox_SN_Numero.Clear()
        DateTimePicker_SN_Data.Text = ""
        TextBox_SN_Seriale.Clear()
        TextBox_SN_CercaTesto.Text = ""
    End Sub

    Sub DisableTxt()
        ComboBox_SN_Articolo.Enabled = False
        ComboBox_SN_Fornitore.Enabled = False
        TextBox_SN_Numero.Enabled = False
        DateTimePicker_SN_Data.Enabled = False
        TextBox_SN_Seriale.Enabled = False
    End Sub

    Sub EnableText()
        ComboBox_SN_Articolo.Enabled = True
        ComboBox_SN_Fornitore.Enabled = True
        TextBox_SN_Numero.Enabled = True
        DateTimePicker_SN_Data.Enabled = True
        TextBox_SN_Seriale.Enabled = True
    End Sub

    Sub DisableButton()
        Botton_SN_Nuovo.Enabled = False
        Botton_SN_Edita.Enabled = False
        Botton_SN_Undo.Enabled = False
        Botton_SN_Salva.Enabled = False
        Botton_SN_Elimina.Enabled = False
        Botton_SN_TrovaRemove.Enabled = False
    End Sub

    ' STILE RECORD
    Sub Dataset_Styles()

        Dim stileTabellaDataGrid As New DataGridTableStyle
        stileTabellaDataGrid.MappingName = "pcemail_seriale"

        Dim stileColonne As GridColumnStylesCollection
        DataGrid_SN.TableStyles.Add(stileTabellaDataGrid)

        stileColonne = DataGrid_SN.TableStyles(0).GridColumnStyles
        stileColonne(0).Width = 0
        stileColonne(1).Width = 350
        stileColonne(2).Width = 250
        stileColonne(3).Width = 80
        stileColonne(4).Width = 70
        stileColonne(5).Width = 150

        stileColonne(0).HeaderText = "ID"
        stileColonne(1).HeaderText = "Articolo"
        stileColonne(2).HeaderText = "Fornitore"
        stileColonne(3).HeaderText = "Numero F."
        stileColonne(4).HeaderText = "Data F."
        stileColonne(5).HeaderText = "Seriale"

    End Sub

    ' VISUALIZZA I RECORD
    Sub Populate_Adapter_Dataset()
        DatPubs = New DataSet

        AdaptSql = New OleDb.OleDbDataAdapter("Select * from pcemail_seriale order by articolo", ConPubs)
        AdaptSql.Fill(DatPubs, "pcemail_seriale")
        DataGrid_SN.DataSource = DatPubs.Tables("pcemail_seriale")

    End Sub

    ' VISUALIZZA I RECORD PER TROVA
    Sub Populate_Adapter_Dataset_Search()
        DatPubs = New DataSet

        AdaptSql = New OleDb.OleDbDataAdapter("Select * from pcemail_seriale where " & ComboBox_SN_CercaIn.Text & " like '%" & TextBox_SN_CercaTesto.Text & "%' order by articolo", ConPubs)
        AdaptSql.Fill(DatPubs, "pcemail_seriale")
        DataGrid_SN.DataSource = DatPubs.Tables("pcemail_seriale")

    End Sub

    'this will remove the data bindings to textboxes
    Private Sub RemoveDataBinding()
        Dim c As Control
        For Each c In GroupBox_SN_Inserisci.Controls
            c.DataBindings.Clear()
        Next c
    End Sub

    'This function will populate the record of a certain field in a table to combo box
    Function Populate_Combo(ByRef cmb As ComboBox, ByVal sfield As String, ByVal stable As String)
        Dim DC As OleDbCommand
        DC = New OleDbCommand("Select " & sfield & " from " & stable & "", ConPubs)
        Dim dr As OleDb.OleDbDataReader = DC.ExecuteReader
        cmb.Items.Clear()
        While dr.Read
            cmb.Items.Add(dr("" & sfield & ""))
        End While
        dr.Close()
    End Function

    ' PULSANTE NUOVO
    Private Sub Botton_SN_Nuovo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Botton_SN_Nuovo.Click
        Call CleartTxt()
        Call DisableButton()
        Call EnableText()
        EditState = False
        Botton_SN_Salva.Enabled = True
        Botton_SN_Undo.Enabled = True
        ComboBox_SN_Articolo.Focus()
    End Sub

    ' PULSANTE SALVA
    Private Sub Botton_SN_Salva_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Botton_SN_Salva.Click
        If Len(Trim(ComboBox_SN_Articolo.Text)) = 0 Then
            dbmsgbox = MsgBox(dbrecordobb, MsgBoxStyle.Information + MsgBoxStyle.OkOnly, nameapp)
            ComboBox_SN_Articolo.Focus()
        ElseIf Len(Trim(ComboBox_SN_Fornitore.Text)) = 0 Then
            dbmsgbox = MsgBox(dbrecordobb, MsgBoxStyle.Information + MsgBoxStyle.OkOnly, nameapp)
            ComboBox_SN_Fornitore.Focus()
        ElseIf Len(Trim(TextBox_SN_Numero.Text)) = 0 Then
            dbmsgbox = MsgBox(dbrecordobb, MsgBoxStyle.Information + MsgBoxStyle.OkOnly, nameapp)
            TextBox_SN_Numero.Focus()
        ElseIf Len(Trim(DateTimePicker_SN_Data.Text)) = 0 Then
            dbmsgbox = MsgBox(dbrecordobb, MsgBoxStyle.Information + MsgBoxStyle.OkOnly, nameapp)
            DateTimePicker_SN_Data.Focus()
        ElseIf Len(Trim(TextBox_SN_Seriale.Text)) = 0 Then
            dbmsgbox = MsgBox(dbrecordobb, MsgBoxStyle.Information + MsgBoxStyle.OkOnly, nameapp)
            TextBox_SN_Seriale.Focus()
        Else

            Select Case EditState
                Case Is = False 'AGGIUNGI RECORD

                    Dim DrNewRow As DataRow = DatPubs.Tables("pcemail_seriale").NewRow
                    Try
                        DrNewRow("articolo") = ComboBox_SN_Articolo.Text
                        DrNewRow("fornitore") = ComboBox_SN_Fornitore.Text
                        DrNewRow("numero") = TextBox_SN_Numero.Text
                        DrNewRow("data") = DateTimePicker_SN_Data.Text
                        DrNewRow("seriale") = TextBox_SN_Seriale.Text
                        DatPubs.Tables("pcemail_seriale").Rows.Add(DrNewRow)

                        Dim CommBuild As New OleDbCommandBuilder(AdaptSql)
                        AdaptSql.Update(DatPubs, "pcemail_seriale")

                    Catch err As Exception
                        dbmsgbox = MsgBox(err.Message, MsgBoxStyle.Information + MsgBoxStyle.OkOnly, nameapp)
                        Call Populate_Adapter_Dataset()
                        Exit Sub
                    End Try

                Case Is = True ' MODIFICA RECORD
                    Dim drEditrow As DataRow = DatPubs.Tables("pcemail_seriale").Rows(DataGrid_SN.CurrentRowIndex)
                    drEditrow.BeginEdit()
                    drEditrow("articolo") = ComboBox_SN_Articolo.Text
                    drEditrow("fornitore") = ComboBox_SN_Fornitore.Text
                    drEditrow("numero") = TextBox_SN_Numero.Text
                    drEditrow("data") = DateTimePicker_SN_Data.Text
                    drEditrow("seriale") = TextBox_SN_Seriale.Text
                    drEditrow.EndEdit()

                    Dim CommBuild As New OleDbCommandBuilder(AdaptSql)
                    AdaptSql.Update(DatPubs, "pcemail_seriale")
            End Select


            If EditState = True Then
                dbmsgbox = MsgBox(dbrecordupdate, MsgBoxStyle.Information + MsgBoxStyle.OkOnly, nameapp)
                DataGrid_SN.Enabled = True
            Else
                dbmsgbox = MsgBox(dbrecordagg, MsgBoxStyle.Information + MsgBoxStyle.OkOnly, nameapp)
            End If

            Call DisableButton()

            Botton_SN_Nuovo.Enabled = True
            Botton_SN_Edita.Enabled = True
            Botton_SN_Elimina.Enabled = True

            Call CleartTxt()
            Call DisableTxt()

        End If
    End Sub

    ' PULSANTE EDITA
    Private Sub Botton_SN_Edita_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Botton_SN_Edita.Click
        If BindingContext(DatPubs.Tables("pcemail_seriale")).Position < 0 Then
            dbmsgbox = MsgBox(dbrecordnot, MsgBoxStyle.Information + MsgBoxStyle.OkOnly, nameapp)
        Else
            If MsgBox(dbrecordedit, MsgBoxStyle.Information + MsgBoxStyle.OkCancel, nameapp) = MsgBoxResult.Ok Then
                Call EnableText()

                'Binding of data to textboxes base on the current record pointer of datagrid
                Me.BindingContext(DatPubs.Tables("pcemail_seriale")).Position = DataGrid_SN.CurrentRowIndex
                ComboBox_SN_Articolo.DataBindings.Add("text", DatPubs.Tables("pcemail_seriale"), "articolo")
                ComboBox_SN_Fornitore.DataBindings.Add("text", DatPubs.Tables("pcemail_seriale"), "fornitore")
                TextBox_SN_Numero.DataBindings.Add("text", DatPubs.Tables("pcemail_seriale"), "numero")
                DateTimePicker_SN_Data.DataBindings.Add("text", DatPubs.Tables("pcemail_seriale"), "data")
                TextBox_SN_Seriale.DataBindings.Add("text", DatPubs.Tables("pcemail_seriale"), "seriale")

                Call DisableButton()

                Botton_SN_Salva.Enabled = True
                Botton_SN_Undo.Enabled = True

                Call RemoveDataBinding()
                EditState = True

                CurrentRecord = DataGrid_SN.CurrentRowIndex
                DataGrid_SN.Enabled = False
            End If
        End If
    End Sub

    ' PULDANTE UNDO
    Private Sub Botton_SN_Undo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Botton_SN_Undo.Click
        Call CleartTxt()
        Call DisableTxt()
        Call DisableButton()

        Botton_SN_Nuovo.Enabled = True
        Botton_SN_Edita.Enabled = True
        Botton_SN_Elimina.Enabled = True
        EditState = False
        DataGrid_SN.Enabled = True
    End Sub

    ' PULSANTE ELIMINA
    Private Sub Botton_SN_Elimina_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Botton_SN_Elimina.Click
        If BindingContext(DatPubs.Tables("pcemail_seriale")).Position < 0 Then
            dbmsgbox = MsgBox(dbrecordnot, MsgBoxStyle.Information + MsgBoxStyle.OkOnly, nameapp)
        Else
            If MsgBox(dbrecordcanc, MsgBoxStyle.Information + MsgBoxStyle.OkCancel, nameapp) = MsgBoxResult.Ok Then
                DatPubs.Tables("pcemail_seriale").Rows(DataGrid_SN.CurrentRowIndex).Delete()
                Dim CommBuild As New OleDbCommandBuilder(AdaptSql)
                AdaptSql.Update(DatPubs, "pcemail_seriale")
            End If
        End If
    End Sub

    ' PULSANTE COPIA
    Private Sub Botton_SN_Copia_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Botton_SN_Copia.Click
        If BindingContext(DatPubs.Tables("pcemail_seriale")).Position < 0 Then
            dbmsgbox = MsgBox(dbrecordnot, MsgBoxStyle.Information + MsgBoxStyle.OkOnly, nameapp)
        Else
            If MsgBox(dbrecordcopia, MsgBoxStyle.Information + MsgBoxStyle.OkCancel, nameapp) = MsgBoxResult.Ok Then
                Call EnableText()

                'Binding of data to textboxes base on the current record pointer of datagrid
                Me.BindingContext(DatPubs.Tables("pcemail_seriale")).Position = DataGrid_SN.CurrentRowIndex
                ComboBox_SN_Articolo.DataBindings.Add("text", DatPubs.Tables("pcemail_seriale"), "articolo")
                ComboBox_SN_Fornitore.DataBindings.Add("text", DatPubs.Tables("pcemail_seriale"), "fornitore")
                TextBox_SN_Numero.DataBindings.Add("text", DatPubs.Tables("pcemail_seriale"), "numero")
                DateTimePicker_SN_Data.DataBindings.Add("text", DatPubs.Tables("pcemail_seriale"), "data")

                Call DisableButton()

                Botton_SN_Salva.Enabled = True
                Botton_SN_Undo.Enabled = True

                Call RemoveDataBinding()
                EditState = False

                CurrentRecord = DataGrid_SN.CurrentRowIndex
                DataGrid_SN.Enabled = True
            End If
        End If
    End Sub

    ' PULSANTE CERCA
    Private Sub Botton_SN_Trova_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Botton_SN_Trova.Click
        If Len(Trim(TextBox_SN_CercaTesto.Text)) = 0 Then
            dbmsgbox = MsgBox(dbrecordobb, MsgBoxStyle.Information + MsgBoxStyle.OkOnly, nameapp)
            TextBox_SN_CercaTesto.Focus()
        Else
            Call Populate_Adapter_Dataset_Search()
            Botton_SN_TrovaRemove.Enabled = True
        End If
    End Sub

    ' PULSANTE RIMUOVI FILTRO CERCA
    Private Sub Botton_SN_TrovaRemove_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Botton_SN_TrovaRemove.Click
        Call Populate_Adapter_Dataset()
        Call CleartTxt()
        Botton_SN_TrovaRemove.Enabled = False
    End Sub

End Class