Imports System.Data.SqlClient

Public Class frm_home
    Dim conn As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("SO_Conn"))
    Dim cmd As SqlCommand = Nothing
    Private Sub frm_home_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            personal()
            assigned()
            datapros()
            datapass()
            todaysdata()
            FillGrid()
        Catch ex As Exception
        Finally
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
        End Try


    End Sub
    Private Sub personal()
        If conn.State = 0 Then
            conn.Open()
        End If
        Dim dr As SqlDataReader = Nothing
        Dim cmdtext As String = "SO_FrmhomePersonal"
        'cmd = New SqlCommand("select * from tbl_users  where id = " & commonUser.Common.uID, conn)
        cmd = New SqlCommand(cmdtext, conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@id", commonUser.Common.uID)
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        If (dr.Read) Then
            Me.txt_name.Text = dr(4).ToString
            Me.txt_user.Text = dr(1).ToString
            Me.txt_type.Text = dr(3).ToString
        End If
        dr.Close()
    End Sub
    Private Sub assigned()
        If conn.State = 0 Then
            conn.Open()
        End If
        Dim dr As SqlDataReader = Nothing
        Dim cmdtext As String = "SO_FrmhomeAssigned"
        'cmd = New SqlCommand("select * from tbl_users  where id = " & commonUser.Common.uID, conn)
        cmd = New SqlCommand(cmdtext, conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@id", commonUser.Common.uID)
        'cmd = New SqlCommand("select count(*) as ASGN from tbl_parent where status = 'A' and assigned_to = " & commonUser.Common.uID, conn)
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        If (dr.Read) Then
            Me.txt_dataA.Text = dr(0).ToString

        End If
        dr.Close()
    End Sub
    Private Sub datapros()
        If conn.State = 0 Then
            conn.Open()
        End If
        Dim dr As SqlDataReader = Nothing
        Dim cmdtext As String = "SO_Frmhomedatapros"
        'cmd = New SqlCommand("select * from tbl_users  where id = " & commonUser.Common.uID, conn)
        cmd = New SqlCommand(cmdtext, conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@id", commonUser.Common.uID)
        'cmd = New SqlCommand("select count(*) as PROS from tbl_parent where status = 'E' and assigned_to = " & commonUser.Common.uID, conn)
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        If (dr.Read) Then
            Me.txt_datap.Text = dr(0).ToString
        End If
        dr.Close()
    End Sub
    Private Sub datapass()
        If conn.State = 0 Then
            conn.Open()
        End If
        Dim dr As SqlDataReader = Nothing
        Dim cmdtext As String = "SO_Frmhomedatapass"
        'cmd = New SqlCommand("select * from tbl_users  where id = " & commonUser.Common.uID, conn)
        cmd = New SqlCommand(cmdtext, conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@id", commonUser.Common.uID)
        'cmd = New SqlCommand("select count(*) as PASS from tbl_parent where status = 'P' and assigned_to = " & commonUser.Common.uID, conn)
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        If (dr.Read) Then
            Me.txt_passS.Text = dr(0).ToString
        End If
        dr.Close()
    End Sub
    Private Sub todaysdata()
        If conn.State = 0 Then
            conn.Open()
        End If
        Dim dr As SqlDataReader = Nothing
        Dim cmdtext As String = "SO_FrmhomeTodaydata"
        'cmd = New SqlCommand("select * from tbl_users  where id = " & commonUser.Common.uID, conn)
        cmd = New SqlCommand(cmdtext, conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@Fromdate", Me.dtpFromDate.Text)
        cmd.Parameters.AddWithValue("@id", commonUser.Common.uID)
        'cmd = New SqlCommand("select count(*) as dataenter from tbl_parent where status = 'E' and dataentry_by is not null and dataentry_date > '" & Me.dtpFromDate.Text & "' and dataentry_date < '" & DateAdd(DateInterval.Day, 1, CDate(Me.dtpFromDate.Text)) & "' and assigned_to = " & commonUser.Common.uID, conn)
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        If (dr.Read) Then
            Me.txt_todaydata.Text = dr(0).ToString
        End If
        dr.Close()
    End Sub
    Private Sub FillGrid()
        Try
            If conn.State <> ConnectionState.Open Then
                conn.Open()
            End If
            Dim ssql As String
            Dim cmdtext As String = "SO_FrmhomeFillgrid"
            'cmd = New SqlCommand("select * from tbl_users  where id = " & commonUser.Common.uID, conn)
            cmd = New SqlCommand(cmdtext, conn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@id", commonUser.Common.uID)
           
            'ssql = "select top 10 login_logout_flag as [Login Or Logout], datentime as [At] from tbl_log_archive where user_id = " & commonUser.Common.uID & " order by datentime desc"

            Dim ds As New DataSet
            Dim da As New SqlDataAdapter(cmd)
            da.Fill(ds)
            gdv_login.DataSource = ds.Tables(0)
            conn.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
  
    Private Sub btn_refresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_refresh.Click
        Try
            personal()
            assigned()
            datapros()
            datapass()
            todaysdata()
            FillGrid()
        Catch ex As Exception
        Finally
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
        End Try

    End Sub

    Private Sub dtpFromDate_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtpFromDate.ValueChanged
        Try
            todaysdata()
        Catch ex As Exception
        Finally
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
        End Try

    End Sub
End Class