Imports System.Data
Imports System.Data.SqlClient

Public Class frmComCars
    Dim VehConnection As SqlConnection
    Dim CarsCommand As SqlCommand
    Dim CarsAdapter As SqlDataAdapter
    Dim CarsTable As DataTable
    Dim CarsManager As CurrencyManager
    Dim MyState As String, MyBookmark As Integer

    Private Sub frmComCars_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Try
            'point to help file
            hlpComCars.HelpNamespace = Application.StartupPath + "\ComCarsHelp.chm"

            'connect to the database
            VehConnection = New SqlConnection("Data Source=.\SQLEXPRESS; AttachDbFilename=" _
                + Application.StartupPath + "\ComCar.mdf;  Integrated Security=True;Connect Timeout=30; User Instance=True")
            VehConnection.Open()

            'establish the command object
            CarsCommand = New SqlCommand("Select * FROM Cars", VehConnection)

            'establish the data adapter/data table
            CarsAdapter = New SqlDataAdapter()
            CarsAdapter.SelectCommand = CarsCommand
            CarsTable = New DataTable()
            CarsAdapter.Fill(CarsTable)

            'bind form controls to the data table
            txtCarID.DataBindings.Add("Text", CarsTable, "CarID")
            txtVehReg.DataBindings.Add("Text", CarsTable, "VehReg")
            txtDvrCode.DataBindings.Add("Text", CarsTable, "DvrCode")
            dtpPlateDate.DataBindings.Add("Value", CarsTable, "PlateDate")
            dtpMoTDate.DataBindings.Add("Value", CarsTable, "MoTDate")
            dtpInsDate.DataBindings.Add("Value", CarsTable, "InsDate")

            'establish the currency manager
            CarsManager = DirectCast(Me.BindingContext(CarsTable), CurrencyManager)
            Me.ControlBox = True
            Me.MaximizeBox = False
            Me.MinimizeBox = False

        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error establishing Cars table.", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try

        With tmrReminders
            .Interval = 1000 * 25 'set 25 seconds for testing.  Normal use = 1000 * 60 * 60 * 12 
        End With

        Me.Show()
        Call SetState("View")

    End Sub

    Private Sub tmrReminders_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles tmrReminders.Tick

        'display notifyicon on the taskbar and warn of impending license actions required
        Try
            With tmrReminders
                .Enabled = False
                CarsAdapter = New SqlDataAdapter()
                CarsAdapter.SelectCommand = CarsCommand
                CarsTable = New DataTable()
                CarsAdapter.Fill(CarsTable)
                Dim DateDataView As DataView = CarsTable.DefaultView

                With DateDataView
                    If .Count > 0 Then
                        Dim bFoundDatesToRemember As Boolean = False
                        Dim sDateReminders As String = vbNullString
                        For DateCheckCounter As Integer = 0 To .Count - 1
                            With .Item(DateCheckCounter)
                                'Dates from database fields PlateDate, MotDate and InsDate
                                Dim CarDate1 As Date = CType(.Item("PlateDate"), Date)
                                Dim CarDate2 As Date = CType(.Item("MoTDate"), Date)
                                Dim CarDate3 As Date = CType(.Item("InsDate"), Date)

                                'Days difference between two dates
                                Dim NumberOfDays1 As Long = DateDiff(DateInterval.Day, Now, _
                                  CarDate1) + 1
                                Dim NumberOfDays2 As Long = DateDiff(DateInterval.Day, Now, _
                                 CarDate2) + 1
                                Dim NumberOfDays3 As Long = DateDiff(DateInterval.Day, Now, _
                                  CarDate3) + 1

                                'Test for PlateDate renewal
                                If NumberOfDays1 < 7 Then
                                    If NumberOfDays1 > -1 And NumberOfDays1 < 0 Then
                                        Dim DayString As String = IIf(NumberOfDays1 = -1, " day", _
                                          " days").ToString
                                    ElseIf NumberOfDays1 > -1 Then
                                        bFoundDatesToRemember = True
                                        If sDateReminders <> vbNullString Then sDateReminders &= vbCrLf
                                        Dim DayString As String = IIf(NumberOfDays1 = 1, " day", _
                                          " days").ToString
                                        sDateReminders &= .Item("VehReg").ToString.Trim & _
                                        "-Plate Date due renewal in " & NumberOfDays1.ToString & DayString
                                    End If
                                End If
                                'Test for MoTDate renewale
                                If NumberOfDays2 < 7 Then
                                    If NumberOfDays2 > -1 And NumberOfDays2 < 0 Then
                                        Dim DayString As String = IIf(NumberOfDays2 = -1, " day", _
                                          " days").ToString
                                    ElseIf NumberOfDays2 > -1 Then
                                        bFoundDatesToRemember = True
                                        If sDateReminders <> vbNullString Then sDateReminders &= vbCrLf
                                        Dim DayString As String = IIf(NumberOfDays1 = 1, " day", _
                                          " days").ToString
                                        sDateReminders &= .Item("VehReg").ToString.Trim & _
                                        "-MoT Date due renewal in " & NumberOfDays2.ToString & DayString
                                    End If
                                End If
                                'Test for InsDate renewale
                                If NumberOfDays3 < 7 Then
                                    If NumberOfDays3 > -1 And NumberOfDays3 < 0 Then
                                        Dim DayString As String = IIf(NumberOfDays3 = -1, " day", _
                                          " days").ToString
                                    ElseIf NumberOfDays3 > -1 Then
                                        bFoundDatesToRemember = True
                                        If sDateReminders <> vbNullString Then sDateReminders &= vbCrLf
                                        Dim DayString As String = IIf(NumberOfDays3 = 1, " day", _
                                          " days").ToString
                                        sDateReminders &= .Item("VehReg").ToString.Trim & _
                                        "-Ins Date due renewal in " & NumberOfDays3.ToString & DayString
                                    End If
                                End If
                            End With
                        Next
                        With niReminders
                            .Visible = True
                            If bFoundDatesToRemember Then
                                .BalloonTipIcon = ToolTipIcon.Warning
                                .BalloonTipText = sDateReminders
                                .BalloonTipTitle = "CarCom Licence Renewals"
                            End If
                            .ShowBalloonTip(5)
                        End With
                    End If
                End With
                .Enabled = True
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
            Exit Sub
        End Try

    End Sub

    Private Sub frmComCars_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing

        If MyState = "Edit" Or MyState = "Add" Then
            MessageBox.Show("You must finish the current edit before stopping the application.", _
                    "", MessageBoxButtons.OK, MessageBoxIcon.Information)
            e.Cancel = True
        Else
            Try
                'turn off timer to save any changes
                If tmrReminders.Enabled = True Then tmrReminders.Stop()
                'save changes to the database
                Dim CarsAdapterCommands As New SqlCommandBuilder(CarsAdapter)
                CarsAdapter.Update(CarsTable)
            Catch ex As Exception
                MessageBox.Show("Error saving database to file:" & ControlChars.CrLf & ex.Message, _
                        "Save Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try

            'close the connection and dispose of the objects
            VehConnection.Dispose()
            CarsCommand.Dispose()
            CarsAdapter.Dispose()
            CarsTable.Dispose()
        End If

    End Sub

    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        CarsManager.Position = 0
    End Sub

    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
        If CarsManager.Position = 0 Then Console.Beep()
        CarsManager.Position -= 1
    End Sub

    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        If CarsManager.Position = CarsManager.Count - 1 Then Console.Beep()
        CarsManager.Position += 1
    End Sub

    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        CarsManager.Position = CarsManager.Count - 1
    End Sub

    Private Sub btnAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddNew.Click

        Try
            MyBookmark = CarsManager.Position
            CarsManager.AddNew()
            Call SetState("Add")
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error adding record.", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        Call SetState("Edit")
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

        If Not (ValidateData()) Then Exit Sub
        Dim SavedVehReg As String = txtVehReg.Text
        Dim SavedRow As Integer

        Try
            If tmrReminders.Enabled = True Then tmrReminders.Stop()
            CarsManager.EndCurrentEdit()
            CarsTable.DefaultView.Sort = "VehReg"   'field name
            SavedRow = CarsTable.DefaultView.Find(SavedVehReg)
            CarsManager.Position = SavedRow
            MessageBox.Show("Record saved.", "Save", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Call SetState("View")
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error saving record.", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        If tmrReminders.Enabled = False Then tmrReminders.Start()
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click

        tmrReminders.Enabled = False
        Dim Response As Windows.Forms.DialogResult
        Response = MessageBox.Show("Are you sure you want to delete this record?", "Delete", _
            MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)

        If Response = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If

        Try
            CarsManager.RemoveAt(CarsManager.Position)
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error deleting record.", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        tmrReminders.Enabled = True
    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click

        CarsManager.CancelCurrentEdit()

        If MyState = "Add" Then
            CarsManager.Position = MyBookmark
        End If

        Call SetState("View")

    End Sub

    Private Sub SetState(ByVal AppState As String)

        MyState = AppState
        Select Case AppState
            Case "View"
                'following any Add or Edit restart timer
                tmrReminders.Start()
                If tmrReminders.Enabled = False Then tmrReminders.Enabled = True
                txtCarID.BackColor = Color.LightGray
                txtCarID.ForeColor = Color.Black
                txtVehReg.ReadOnly = True
                txtDvrCode.ReadOnly = True
                dtpPlateDate.Enabled = False
                dtpMoTDate.Enabled = False
                dtpInsDate.Enabled = False
                btnFirst.Enabled = True
                btnPrevious.Enabled = True
                btnNext.Enabled = True
                btnLast.Enabled = True
                btnAddNew.Enabled = True
                btnSave.Enabled = False
                btnCancel.Enabled = False
                btnEdit.Enabled = True
                btnDelete.Enabled = True
                btnDone.Enabled = True
                txtVehReg.Focus()
            Case "Add", "Edit"
                'switch off timer to allow Add and Edit
                If tmrReminders.Enabled = True Then tmrReminders.Stop() 'Enabled = False
                txtCarID.BackColor = Color.Red
                txtCarID.ForeColor = Color.White
                txtVehReg.ReadOnly = False
                txtDvrCode.ReadOnly = False
                dtpPlateDate.Enabled = True
                dtpMoTDate.Enabled = True
                dtpInsDate.Enabled = True
                btnFirst.Enabled = False
                btnPrevious.Enabled = False
                btnNext.Enabled = False
                btnLast.Enabled = False
                btnAddNew.Enabled = False
                btnSave.Enabled = True
                btnCancel.Enabled = True
                btnEdit.Enabled = False
                btnDelete.Enabled = False
                btnDone.Enabled = False
                txtVehReg.Focus()
        End Select

    End Sub

    Private Function ValidateData() As Boolean

        Dim Message As String = ""
        Dim AllOK As Boolean = True

        'check for vehicle registration number
        If txtVehReg.Text.Trim = "" Then
            Message = "You must enter a vehicle registration number." + ControlChars.CrLf
            txtVehReg.Focus()
            AllOK = False
        End If

        'check for driver code
        If txtDvrCode.Text.Trim = "" Then
            Message = "You must enter a driver code." + ControlChars.CrLf
            txtDvrCode.Focus()
            AllOK = False
        End If

        If Not (AllOK) Then
            MessageBox.Show(Message, "Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If

        Return (AllOK)

    End Function

    Private Sub Label13_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label13.Click
        Help.ShowHelp(Me, hlpComCars.HelpNamespace)
    End Sub

    Private Sub btnDone_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDone.Click

        Randomize()
        Dim Num As Integer
        Dim random As New Random
        Num = random.Next(1, 5)

        Select Case Num
            Case 1
                picCarImage.Image = My.Resources.aston_martin
            Case 2
                picCarImage.Image = My.Resources.ferrari
            Case 3
                picCarImage.Image = My.Resources.hyundai_coupe
            Case 4
                picCarImage.Image = My.Resources.lamborghini
            Case 5
                picCarImage.Image = My.Resources.dodge_viper2
        End Select

        Me.ControlBox = True
        Me.MaximizeBox = False
        Me.MinimizeBox = False
        Me.WindowState = FormWindowState.Minimized
    End Sub

    Private Sub Label14_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label14.Click
        frmKSBSplash.Show()
        frmKSBSplash.Button1.Visible = True
    End Sub
End Class