Imports System.Data.SqlClient
Public Class frm_performance
    Dim conn As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("SO_Conn"))
    Private Sub frm_performance_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        FillGrid()
        Filloverall()
    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_FrmperformanceFillgrid"
            Dim cmd As New SqlCommand
            cmd = Nothing
            cmd = New SqlCommand(cmdtext, conn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@FromDate", Me.dtpFromDate.Text)
            cmd.Parameters.AddWithValue("@todate", Me.dtptodate.Text)
            'ssql = "select ts.file_id as [ID], ts.file_name as [File], ts.add_streetno + ' ' + ts.add_prefix + ' ' + ts.add_streetname + ' ' + ts.add_suffix as [Address], ts.city as City, last_remark as [Remark] from tbl_servicewo ts join tbl_parent tp on ts.file_id = tp.file_id where tp.status not in ('N','A','S') and (ts.file_id between " & Me.txt_sfrom.Text & " and " & txt_sto.Text & ") order by ts.file_id DESC"
            'ssql = "select  tu.user_name [DEP Name], count(*) as Data  from tbl_parent tp join tbl_users tu on tp.dataentry_by = tu.id where dataentry_by is not null and dataentry_date > '" & Me.dtpFromDate.Text & "' and dataentry_date < '" & DateAdd(DateInterval.Day, 1, CDate(Me.dtptodate.Text)) & "' group by tu.user_name  order by data desc"
            Dim ds As New DataSet
            Dim da As New SqlDataAdapter(cmd)
            da.Fill(ds)
            gdv_perf.DataSource = ds.Tables(0)
        Catch ex As Exception

        Finally
            If conn.State <> ConnectionState.Closed Then
                conn.Close()
            End If


        End Try
    End Sub
    Private Sub Filloverall()
        Try
            If conn.State <> ConnectionState.Open Then
                conn.Open()
            End If
            Dim ssql As String
            Dim cmdtext As String = "SO_FrmperformanceFilloverall"
            Dim cmd As New SqlCommand
            cmd = Nothing
            cmd = New SqlCommand(cmdtext, conn)
            cmd.CommandType = CommandType.StoredProcedure
            'ssql = "select ts.file_id as [ID], ts.file_name as [File], ts.add_streetno + ' ' + ts.add_prefix + ' ' + ts.add_streetname + ' ' + ts.add_suffix as [Address], ts.city as City, last_remark as [Remark] from tbl_servicewo ts join tbl_parent tp on ts.file_id = tp.file_id where tp.status not in ('N','A','S') and (ts.file_id between " & Me.txt_sfrom.Text & " and " & txt_sto.Text & ") order by ts.file_id DESC"
            'ssql = "SELECT  A.USER_NAME, B.ASSIGNED, C.DATAENTERED ,  (B.ASSIGNED - C.DATAENTERED) AS UNPROCESSED FROM (SELECT ID, USER_NAME FROM TBL_USERS WHERE USERTYPE = 'DEP') A , (SELECT COUNT(*) AS ASSIGNED, ASSIGNED_TO FROM TBL_PARENT GROUP BY ASSIGNED_TO) B  , (SELECT COUNT(*) AS DATAENTERED,  DATAENTRY_BY FROM TBL_PARENT GROUP BY DATAENTRY_BY) C  WHERE A.ID = B.ASSIGNED_TO AND A.ID = C.DATAENTRY_BY order by user_name"
            Dim ds As New DataSet
            Dim da As New SqlDataAdapter(cmd)
            da.Fill(ds)
            gdv_overall.DataSource = ds.Tables(0)
        Catch ex As Exception
        Finally
            If conn.State <> ConnectionState.Closed 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, dtptodate.ValueChanged
        FillGrid()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        FillGrid()
        total_dataentered()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Filloverall()
        total_stats()
    End Sub
    Private Sub total_dataentered()
        Dim total As Integer
        For Each row As DataGridViewRow In Me.gdv_perf.Rows
            total = total + row.Cells(1).Value
        Next

        gdv_perf.Rows(gdv_perf.RowCount - 1).Cells(0).Value = "Total"
        gdv_perf.Rows(gdv_perf.RowCount - 1).Cells(1).Value = total
    End Sub
    Private Sub total_stats()
        Dim t1, t2, t3 As Integer
        For Each row As DataGridViewRow In Me.gdv_overall.Rows
            t1 = t1 + row.Cells(1).Value
            t2 = t2 + row.Cells(2).Value
            t3 = t3 + row.Cells(3).Value
        Next

        gdv_overall.Rows(gdv_overall.RowCount - 1).Cells(0).Value = "Total"
        gdv_overall.Rows(gdv_overall.RowCount - 1).Cells(1).Value = t1
        gdv_overall.Rows(gdv_overall.RowCount - 1).Cells(2).Value = t2
        gdv_overall.Rows(gdv_overall.RowCount - 1).Cells(3).Value = t3
    End Sub
End Class