Imports System.IO
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Imports System.IO.File
Imports System.IO.FileStream
Imports Excel = Microsoft.Office.Interop.Excel
Public Class AdminDashboard

    Shared Property AdminDboard As String
    Private Sub AdminDashboard_Load(sender As Object, e As EventArgs) Handles MyBase.Load
     
        Timer1.Start()
        Me.AttendanceRecordTableAdapter.Fill(Me.AttendanceDBDataSet.AttendanceRecord)
        Label1.Text = AdminDboard
        'ALL EMPLOYEE
        Dim NAMEquery As String = "SELECT * FROM UserTable"
        Using NAMEcon As SqlConnection = New SqlConnection("Data Source=.;Initial Catalog=attendanceDB;Integrated Security=True;Pooling=False")
            Using NAMEcmd As SqlCommand = New SqlCommand(NAMEquery, NAMEcon)
                Using da As New SqlDataAdapter()
                    da.SelectCommand = NAMEcmd
                    Using dt As New DataTable()
                        NAMEcon.Open()
                        da.Fill(dt)

                        If dt.Rows.Count > 0 Then
                            Label5.Text = dt.Rows.Count()
                            Label12.Text = dt.Rows.Count()
                        Else
                            Label5.Text = ""
                            Label12.Text = ""
                        End If

                    End Using
                    NAMEcon.Close()
                End Using
            End Using
        End Using

        'TIME IN TODAY
        Dim query As String = "SELECT * FROM AttendanceRecord WHERE DATE ='" & DateAndTime.Now.ToString("dd/MM/yyyy") & "'"
        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)
                        If dt.Rows.Count > 0 Then
                            Label7.Text = dt.Rows.Count.ToString()

                        Else
                            Label7.Text = ""

                        End If
                    End Using
                End Using
            End Using
        End Using

        'ALL ATTENDANCE
        Dim allattendancequery 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(allattendancequery, con)
                Using sda As New SqlDataAdapter()
                    sda.SelectCommand = cmd
                    Using dt As New DataTable()
                        sda.Fill(dt)
                        If dt.Rows.Count > 0 Then
                            Label9.Text = dt.Rows.Count()
                            Label14.Text = dt.Rows.Count.ToString()

                        Else
                            Label9.Text = ""
                            Label14.Text = ""

                        End If
                    End Using
                End Using
            End Using
        End Using
       
    End Sub
    Private Sub ExportExcelBTN_Click(sender As Object, e As EventArgs) Handles ExportExcelBTN.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"
        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 & "\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
    Private Sub AdvanceReportBTN_Click(sender As Object, e As EventArgs) Handles AdvanceReportBTN.Click
        AdminAdvanceReportForm.Show()

    End Sub

    Private Sub EmployeeBTN_Click(sender As Object, e As EventArgs) Handles EmployeeBTN.Click
        AdminEmpForm.Show()
    End Sub

    Private Sub TimeInTodayBTN_Click(sender As Object, e As EventArgs) Handles TimeInTodayBTN.Click
        AdminEmpTimInForm.Show()
    End Sub

    Private Sub AttendanceBTN_Click(sender As Object, e As EventArgs) Handles AttendanceBTN.Click
        AdminEmpAttendanceForm.Show()
    End Sub

    Private Sub UsersBTN_Click(sender As Object, e As EventArgs) Handles UsersBTN.Click
        AdminUsersForm.Show()
    End Sub

    Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
        TimeBTN.Text = DateAndTime.Now
    End Sub
End Class