Untitled

Imports System
Imports System.Windows.Forms
Imports System.Drawing

Imports Microsoft.Office.Interop.Excel
Imports Excel = Microsoft.Office.Interop.Excel



Public Class frmPRMIS_Search
    Private Sub Button2_Click(sender As Object, e As EventArgs)
        connect()
        command("SEELCT * FROM tblPlatesClaimed WHERE Date BETWEEN '" & DateTimePicker1.Value & "' and '" & DateTimePicker2.Value & "'", 2)

        Dim Lv As ListViewItem
        ListView1.Items.Clear()
        While dr.Read
            Lv = ListView1.Items.Add(dr.Item("LTO_Plate"))
            Lv.SubItems.Add(dr.Item("LTO_Name"))
            Lv.SubItems.Add(dr.Item("LTO_Address"))
            Lv.SubItems.Add(dr.Item("LTO_Date"))
            Lv.SubItems.Add(dr.Item("LTO_Claimed"))
        End While


    End Sub

    Private Sub frmSearch_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.MdiParent = MDIParent1
        cBox1.SelectedIndex = 1
        'refresherLiaison()
        SearchNow(False, False)
    End Sub
    Private Sub SearchNow(withDate As Boolean, withFilter As Boolean)
        Dim columnName As String = ""

        If cBox1.SelectedIndex = 0 Then
            columnName = ""
        ElseIf cBox1.SelectedIndex = 1 Then
            columnName = "Plates"
        ElseIf cBox1.SelectedIndex = 2 Then
            columnName = "Dealer"
        ElseIf cBox1.SelectedIndex = 3 Then
            columnName = "UName"
        End If

        If withDate And withFilter Then
            temp_globalval = "SELECT * FROM tblPRMIS_PlatesClaimed WHERE " & columnName & " LIKE '%" & TextBox1.Text & "%' and (Date BETWEEN CONVERT(DATETIME, '" & DateTimePicker1.Value & "', 120) and CONVERT(DATETIME, '" & DateTimePicker2.Value & "', 120))"
        ElseIf withDate Then ' Query only with Date
            temp_globalval = "SELECT * FROM tblPRMIS_PlatesClaimed WHERE Date BETWEEN CONVERT(DATETIME, '" & DateTimePicker1.Value & "', 120) and CONVERT(DATETIME, '" & DateTimePicker2.Value & "', 120)"
        ElseIf withFilter Then ' Query only with Filter
            temp_globalval = "SELECT * FROM tblPRMIS_PlatesClaimed WHERE " & columnName & " LIKE '%" & TextBox1.Text & "%'"
        Else
            temp_globalval = "SELECT TOP 100 * FROM tblPRMIS_PlatesClaimed"
        End If

        connect()
        command(temp_globalval, 2)

        ListView1.Items.Clear()
        ListView1.Visible = True
        ListView2.Visible = False

        Dim Lv As ListViewItem
        Dim counttotal As Integer
        While dr.Read
            Lv = ListView1.Items.Add(dr.Item("Plates"))
            Lv.SubItems.Add(dr.Item("Uname"))
            Lv.SubItems.Add(dr.Item("Dealer"))
            Lv.SubItems.Add(dr.Item("Branch"))
            Lv.SubItems.Add(dr.Item("Date"))
            counttotal = counttotal + 1
        End While
        Label20.Text = counttotal
    End Sub


    Private Sub refresherPlateClaimed()
        connect()
        command("SELECT * FROM tblPlatesClaimed", 2)
        Dim Lv As ListViewItem
        ListView1.Items.Clear()
        While dr.Read
            Lv = ListView1.Items.Add(dr.Item("LTO_Plates"))
            Lv.SubItems.Add(dr.Item("LTO_Name"))
            Lv.SubItems.Add(dr.Item("LTO_Address"))
            Lv.SubItems.Add(dr.Item("LTO_Date"))
            Lv.SubItems.Add(dr.Item("LTO_ClaimedBy"))
        End While
    End Sub

    Private Sub refresherLiaison()
        connect()
        command("SELECT * FROM tblLiaisons", 2)
        ListView2.Show()
        Dim Lv As ListViewItem
        ListView2.Items.Clear()
        While dr.Read
            Lv = ListView2.Items.Add(dr.Item("LTO_Fname") & " " & dr.Item("LTO_Lname"))
            Lv.SubItems.Add(dr.Item("LTO_Address"))
            Lv.SubItems.Add(dr.Item("LTO_Company"))
        End While
    End Sub
    Private Sub Button9_Click(sender As Object, e As EventArgs)

        ListView1.Visible = False
        ListView2.Visible = True
        LiaisonRefresh()

        'connect()
        'command("SELECT * FROM tblLiaisons", 2)
        'Dim Lv As ListViewItem
        'ListView2.Items.Clear()
        'While dr.Read
        '    Lv = ListView2.Items.Add(dr.Item("LTO_Fname") & dr.Item("LTO_Lname"))
        '    Lv.SubItems.Add(dr.Item("LTO_Address"))
        '    Lv.SubItems.Add(dr.Item("LTO_Company"))
        'End While
    End Sub

    Private Sub LiaisonRefresh()
        connect()
        command("SELECT * FROM tblLiaisons", 2)
        ListView2.Show()
        Dim Lv As ListViewItem
        ListView2.Items.Clear()
        While dr.Read
            Lv = ListView2.Items.Add(dr.Item("LTO_Fname") & " " & dr.Item("LTO_Lname"))
            Lv.SubItems.Add(dr.Item("LTO_Address"))
            Lv.SubItems.Add(dr.Item("LTO_Company"))
        End While
    End Sub

    Private Sub Button8_Click(sender As Object, e As EventArgs)
        Dim Lv As ListViewItem

        connect()
        If cBox1.SelectedIndex = 0 Then
            Exit Sub
        ElseIf cBox1.SelectedIndex = 1 Then
            command("SELECT * FROM tblPlatesClaimed WHERE LTO_Plates = '" & TextBox1.Text & "'", 2)
            ListView1.Items.Clear()
            ListView1.Visible = True
            ListView2.Visible = False

            While dr.Read
                Lv = ListView1.Items.Add(dr.Item("LTO_Plates"))
                Lv.SubItems.Add(dr.Item("LTO_Name"))
                Lv.SubItems.Add(dr.Item("LTO_Address"))
                Lv.SubItems.Add(dr.Item("LTO_Date"))
                Lv.SubItems.Add(dr.Item("LTO_Claimed"))
            End While

        ElseIf cBox1.SelectedIndex = 2 Then
            command("SELECT * FROM tblPlatesClaimed WHERE LTO_Name = '%" & TextBox1.Text & "%'", 2)
            ListView1.Items.Clear()
            ListView1.Visible = True
            ListView2.Visible = False

            While dr.Read
                Lv = ListView1.Items.Add(dr.Item("LTO_Plates"))
                Lv.SubItems.Add(dr.Item("LTO_Name"))
                Lv.SubItems.Add(dr.Item("LTO_Address"))
                Lv.SubItems.Add(dr.Item("LTO_Date"))
                Lv.SubItems.Add(dr.Item("LTO_Claimed"))
            End While

        ElseIf cBox1.SelectedIndex = 3 Then
            command("SEELCT * FROM tblPlatesClaimed WHERE LTO_Claimed = '" & TextBox1.Text & "'", 2)
            ListView1.Items.Clear()
            ListView1.Visible = True
            ListView2.Visible = False

            While dr.Read
                Lv = ListView1.Items.Add(dr.Item("LTO_Plates"))
                Lv.SubItems.Add(dr.Item("LTO_Name"))
                Lv.SubItems.Add(dr.Item("LTO_Address"))
                Lv.SubItems.Add(dr.Item("LTO_Date"))
                Lv.SubItems.Add(dr.Item("LTO_Claimed"))
            End While

        End If


    End Sub

    Private Sub CloudDesktopButton2_Click(sender As Object, e As EventArgs) Handles CloudDesktopButton1.Click
        If a Or d Or c Or f Or g Or h Or l Or b Or i Or j Or k Or m Or n = 1 And h2 = 2 Then
            Me.Close()
            b = 0
            h2 = 0
            TextBox1.Text = Nothing
            LiaisonRefresh()
            refresherPlateClaimed()

        Else
            MDIParent1.CloudDigitalClock1.Visible = True
            Me.Close()
            b = 0
            h2 = 0
            LiaisonRefresh()
            refresherPlateClaimed()

        End If
    End Sub

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

    End Sub

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

    End Sub

    Private Sub Button5_Click(sender As Object, e As EventArgs)
        If a Or d Or c Or f Or g Or h Or l Or b Or i Or j Or k Or m Or n = 1 And h2 = 2 Then
            Me.Close()
            b = 0
            h2 = 0
            TextBox1.Text = Nothing
            LiaisonRefresh()
            refresherLiaison()

        Else
            MDIParent1.CloudDigitalClock1.Visible = True
            Me.Close()
            b = 0
            h2 = 0
            LiaisonRefresh()
            refresherLiaison()

        End If
    End Sub

    Private Sub CloudDesktopButton2_Click_1(sender As Object, e As EventArgs) Handles CloudDesktopButton2.Click
        SearchNow(True, False)
    End Sub

    Private Sub CloudDesktopButton5_Click(sender As Object, e As EventArgs) Handles CloudDesktopButton5.Click
        SearchNow(True, True)
    End Sub

    Private Sub CloudDesktopButton6_Click(sender As Object, e As EventArgs) Handles CloudDesktopButton6.Click
        'ListView1.Visible = False
        'ListView2.Visible = True
        'LiaisonRefresh()
        cBox1.SelectedValue = ""
        TextBox1.Text = ""
        SearchNow(False, False)
    End Sub

    Private Sub CloudDesktopButton7_Click(sender As Object, e As EventArgs) Handles CloudDesktopButton7.Click
        If a Or d Or c Or f Or g Or h Or l Or b Or i Or j Or k Or m Or n = 1 And h2 = 2 Then
            Me.Close()
            b = 0
            h2 = 0
            TextBox1.Text = Nothing
            LiaisonRefresh()
            refresherPlateClaimed()
            ListView1.Hide()
        Else
            MDIParent1.CloudDigitalClock1.Visible = True
            Me.Close()
            b = 0
            h2 = 0
            LiaisonRefresh()
            refresherPlateClaimed()
            ListView1.Hide()
        End If
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        If ListView1.Items.Count = 0 Then
            MsgBox("There's no record to Print", MsgBoxStyle.Critical, "PRINT")
            Exit Sub
        End If

        Dim saveFileDialog As New SaveFileDialog()
        saveFileDialog.Filter = "Excel Files|*.xlsx"
        saveFileDialog.Title = "Save Excel File"
        saveFileDialog.FileName = "Report_Releasing.xlsx"  ' Default file name

        If saveFileDialog.ShowDialog() = DialogResult.OK Then
            Dim xlApp As New Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet

            xlWorkBook = xlApp.Workbooks.Add()
            xlWorkSheet = xlWorkBook.Sheets("sheet1")

            connect()
            command(temp_globalval, 2)

            ' populate header
            xlWorkSheet.Cells(1, 1) = "NO"
            xlWorkSheet.Cells(1, 2) = "PLATE"
            xlWorkSheet.Cells(1, 3) = "USERNAME"
            xlWorkSheet.Cells(1, 4) = "DEALER"
            xlWorkSheet.Cells(1, 5) = "BRANCH"
            xlWorkSheet.Cells(1, 6) = "DATE RELEASE"

            ' populate data
            Dim y As Integer = 1
            While dr.Read
                xlWorkSheet.Cells(y + 1, 1) = y
                xlWorkSheet.Cells(y + 1, 2) = dr.Item("Plates")
                xlWorkSheet.Cells(y + 1, 3) = dr.Item("Uname")
                xlWorkSheet.Cells(y + 1, 4) = dr.Item("Dealer")
                xlWorkSheet.Cells(y + 1, 5) = dr.Item("Branch")
                xlWorkSheet.Cells(y + 1, 6) = dr.Item("Date")
                y += 1
            End While

            ' Save the file to chosen path
            xlWorkBook.SaveAs(saveFileDialog.FileName)
            xlWorkBook.Close()
            xlApp.Quit()

            ' Release objects
            releaseObject(xlWorkSheet)
            releaseObject(xlWorkBook)
            releaseObject(xlApp)

            MsgBox("Excel file saved successfully!" & vbCrLf & saveFileDialog.FileName)
        End If


    End Sub

    Private Sub CloudDesktopButton3_Click(sender As Object, e As EventArgs) Handles CloudDesktopButton3.Click
        'ListView1.Hide()
        'refresherLiaison()
        DateTimePicker1.Value = Date.Now
        DateTimePicker2.Value = Date.Now
        SearchNow(False, False)
    End Sub

    Private Sub TextBox1_KeyDown(sender As Object, e As KeyEventArgs) Handles TextBox1.KeyDown
        If e.KeyCode = Keys.Enter Then
            Dim Lv As ListViewItem

            If cBox1.SelectedIndex = 0 Then
                Exit Sub
            ElseIf cBox1.SelectedIndex = 1 Then
                connect()
                command("SELECT * FROM tblPRMIS_PlatesClaimed WHERE Plates = '" & TextBox1.Text & "'", 2)
                temp_globalval = "SELECT * FROM tblPRMIS_PlatesClaimed WHERE Plates = '" & TextBox1.Text & "'"
                ListView1.Items.Clear()
                ListView1.Visible = True
                ListView2.Visible = False

                While dr.Read
                    Lv = ListView1.Items.Add(dr.Item("Plates"))
                    Lv.SubItems.Add(dr.Item("Uname"))
                    Lv.SubItems.Add(dr.Item("Dealer"))
                    Lv.SubItems.Add(dr.Item("Branch"))
                    'Lv.SubItems.Add(dr.Item("LTO_Name"))
                    'Lv.SubItems.Add(dr.Item("LTO_Address"))
                    Lv.SubItems.Add(dr.Item("Date"))
                    'Lv.SubItems.Add(dr.Item("LTO_ClaimedBy"))
                End While

            ElseIf cBox1.SelectedIndex = 2 Then
                connect()
                command("SELECT * FROM tblPRMIS_PlatesClaimed WHERE Dealer LIKE '%" & TextBox1.Text & "%'", 2)
                temp_globalval = "SELECT * FROM tblPRMIS_PlatesClaimed WHERE Dealer LIKE '%" & TextBox1.Text & "%'"

                'ListView1.Items.Clear()
                ListView1.Visible = True
                ListView2.Visible = False


                While dr.Read
                    Lv = ListView1.Items.Add(dr.Item("Plates"))
                    Lv.SubItems.Add(dr.Item("Uname"))
                    Lv.SubItems.Add(dr.Item("Dealer"))
                    Lv.SubItems.Add(dr.Item("Branch"))
                    Lv.SubItems.Add(dr.Item("Date"))
                End While

            ElseIf cBox1.SelectedIndex = 3 Then
                connect()
                command("SELECT * FROM tblPRMIS_PlatesClaimed WHERE UName LIKE '%" & TextBox1.Text & "%'", 2)
                temp_globalval = "SEELCT * FROM tblPRMIS_latesClaimed WHERE UName LIKE '%" & TextBox1.Text & "%'"
                ListView1.Items.Clear()
                ListView1.Visible = True
                ListView2.Visible = False

                While dr.Read
                    Lv = ListView1.Items.Add(dr.Item("Plates"))
                    Lv.SubItems.Add(dr.Item("Uname"))
                    Lv.SubItems.Add(dr.Item("Dealer"))
                    Lv.SubItems.Add(dr.Item("Branch"))
                    Lv.SubItems.Add(dr.Item("Date"))
                End While
            End If
        End If
    End Sub

    Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged

    End Sub

    Private Sub GroupBox2_Enter(sender As Object, e As EventArgs) Handles GroupBox2.Enter

    End Sub
End Class