Imports System.Data.SqlClient
Imports EXCEL = Microsoft.Office.Interop.Excel
Public Class AdminAdvanceReportForm

    Private Sub AdminAdvanceReportForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'AttendanceDBDataSet.AttendanceRecord' table. You can move, or remove it, as needed.
        Me.AttendanceRecordTableAdapter.Fill(Me.AttendanceDBDataSet.AttendanceRecord)
        DataGridView1.GridColor = Color.Red
        DataGridView1.CellBorderStyle = DataGridViewCellBorderStyle.None
        DataGridView1.BackgroundColor = Color.LightGray

        DataGridView1.DefaultCellStyle.SelectionBackColor = Color.Red
        DataGridView1.DefaultCellStyle.SelectionForeColor = Color.White

        DataGridView1.DefaultCellStyle.WrapMode = DataGridViewTriState.[True]

        DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        DataGridView1.AllowUserToResizeColumns = False

        DataGridView1.RowsDefaultCellStyle.BackColor = Color.Bisque
        DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
        DataGridView1.Columns(0).Visible = True
        DataGridView1.Columns(1).Width = 200
        DataGridView1.Columns(2).Width = 220
        DataGridView1.Columns(3).Width = 230
        'Dim query As String = "SELECT * FROM AttendanceRecord"
        'Using con As SqlConnection = New SqlConnection("Data Source=STALKER01\SQLEXPRESSSS;Initial Catalog=attendanceDB;Integrated Security=True;Pooling=False")
        '    Using cmd As SqlCommand = New SqlCommand(query, con)
        '        Using sda As New SqlDataAdapter()
        '            sda.SelectCommand = cmd
        '            Using dt As New DataTable()
        '                con.Open()
        '                sda.Fill(dt)
        '                DataGridView1.DataSource = dt
        '                DataGridView1.GridColor = Color.Red
        '                DataGridView1.CellBorderStyle = DataGridViewCellBorderStyle.None
        '                DataGridView1.BackgroundColor = Color.LightGray

        '                DataGridView1.DefaultCellStyle.SelectionBackColor = Color.Red
        '                DataGridView1.DefaultCellStyle.SelectionForeColor = Color.White

        '                DataGridView1.DefaultCellStyle.WrapMode = DataGridViewTriState.[True]

        '                DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        '                DataGridView1.AllowUserToResizeColumns = False

        '                DataGridView1.RowsDefaultCellStyle.BackColor = Color.Bisque
        '                DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
        '                DataGridView1.Columns(0).Visible = True
        '                DataGridView1.Columns(1).Width = 200
        '                DataGridView1.Columns(2).Width = 230
        '                DataGridView1.Columns(3).Width = 240

        '            End Using
        '        End Using
        '    End Using
        'End Using
        ComboBox1.Items.Clear()
        Try

            Dim mycon As New SqlConnection("Data Source=STALKER01\SQLEXPRESSSS;Initial Catalog=attendanceDB;Integrated Security=True;Pooling=False")
            mycon.Open()
            Dim da As New SqlDataAdapter("SELECT * FROM UserTable WHERE USERTYPE='User'", mycon)
            Dim dt As New DataTable
            da.Fill(dt)
            ComboBox1.DataSource = dt
            ComboBox1.DisplayMember = "USERNAME"
            ComboBox1.ValueMember = "USERNAME"
            mycon.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub RefreshBTN_Click(sender As Object, e As EventArgs) Handles RefreshBTN.Click
        Dim Refreshquery As String = "SELECT * FROM AttendanceRecord"
        Using con As SqlConnection = New SqlConnection("Data Source=STALKER01\SQLEXPRESSSS;Initial Catalog=attendanceDB;Integrated Security=True;Pooling=False")
            Using cmd As SqlCommand = New SqlCommand(Refreshquery, con)
                Using sda As SqlDataAdapter = New SqlDataAdapter()
                    sda.SelectCommand = cmd
                    Using dt As New DataTable()
                        sda.Fill(dt)
                        DataGridView1.DataSource = dt
                    End Using
                End Using
            End Using
        End Using
    End Sub

    Private Sub FilterBTN_Click(sender As Object, e As EventArgs) Handles FilterBTN.Click
        Dim query As String = "SELECT * FROM AttendanceRecord WHERE DATE >='" & DateTimePicker1.Value & "' AND DATE <='" & DateTimePicker2.Value & "' AND USERNAME ='" & ComboBox1.SelectedValue & "'"
        Using con As SqlConnection = New SqlConnection("Data Source=STALKER01\SQLEXPRESSSS;Initial Catalog=attendanceDB;Integrated Security=True;Pooling=False")
            Using cmd As SqlCommand = New SqlCommand(query, con)
                Using sda As New SqlDataAdapter()
                    sda.SelectCommand = cmd
                    Using dt As New DataTable()
                        sda.Fill(dt)
                        DataGridView1.DataSource = dt
                        
                    End Using
                End Using
            End Using
        End Using
    End Sub

    Private Sub ExportBTN_Click(sender As Object, e As EventArgs) Handles ExportBTN.Click
        Dim cnn As SqlConnection
        Dim connectionString As String
        Dim sql As String
        Dim i, j As Integer

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        connectionString = "Data Source=STALKER01\SQLEXPRESSSS;Initial Catalog=attendanceDB;Integrated Security=True;Pooling=False"
        cnn = New SqlConnection(connectionString)
        cnn.Open()
        sql = "SELECT * FROM AttendanceRecord WHERE DATE >='" & DateTimePicker1.Value & "' AND DATE <='" & DateTimePicker2.Value & "' AND USERNAME ='" & ComboBox1.SelectedValue & "'"
        Dim dscmd As New SqlDataAdapter(sql, cnn)
        Dim ds As New DataSet
        dscmd.Fill(ds)

        For i = 0 To ds.Tables(0).Rows.Count - 1
            For j = 0 To ds.Tables(0).Columns.Count - 1
                xlWorkSheet.Cells(i + 1, j + 1) = _
                ds.Tables(0).Rows(i).Item(j)
            Next
        Next

        Try
            Dim fbd As New FolderBrowserDialog

            If fbd.ShowDialog() = vbOK Then
                'xlWorkSheet.SaveAs(fbd.SelectedPath & "\'" & ComboBox1.SelectedValue & "'AttendanceRecord.xlsx")
                xlWorkSheet.SaveAs(fbd.SelectedPath & "\" & ComboBox1.SelectedValue & "AttendanceRecord.xlsx")
                xlWorkBook.Close()
                xlApp.Quit()

                releaseObject(xlApp)
                releaseObject(xlWorkBook)
                releaseObject(xlWorkSheet)

                MessageBox.Show("Succcessfully exported to Excel file!", "Export", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class