Imports System.Data.SqlClient
Public Class frm_assign
    Dim da As SqlDataAdapter = Nothing
    Dim conn As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("SO_Conn"))
    Dim cmd As SqlCommand = Nothing
    Private Sub frm_assign_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Filluser()
        FillreceiveGrid()
        FillassignGrid()
    End Sub
    Public Sub Filluser()
        Try
            If conn.State <> ConnectionState.Open Then
                conn.Open()
            End If
            Dim cmdtext As String = "SO_SelectUser"
            cmd = Nothing
            cmd = New SqlCommand(cmdtext, conn)
            cmd.CommandType = CommandType.StoredProcedure
            Dim ds As New DataSet
            'da = New SqlDataAdapter("select id,USER_NAME from tbl_users where STATUS <> 'I' and usertype= 'DEP' order by USER_NAME", conn)
            da = New SqlDataAdapter(cmd)
            da.Fill(ds)
            cbm_users.DataSource = ds.Tables(0)
            cbm_users.DisplayMember = "USER_NAME"
            cbm_users.ValueMember = "id"
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Private Sub FillassignGrid()
        If cbm_users.SelectedValue.ToString.Trim = "System.Data.DataRowView" Or cbm_users.SelectedValue.ToString.Trim = "" Then Exit Sub
        Try
            If conn.State <> ConnectionState.Open Then
                conn.Open()
            End If
            Dim cmdtext As String = "SO_Fillassigngrid"
            cmd = Nothing
            cmd = New SqlCommand(cmdtext, conn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@name", cbm_users.SelectedValue.ToString.Trim)
            'Dim ssql As String
            'ssql = "select file_name as Files, assigned_date as [Assigned on] from tbl_parent  where assigned_to = '" & cbm_users.SelectedValue.ToString.Trim & "' and status = 'A' order by file_id desc"
            Dim ds As New DataSet
            Dim da As New SqlDataAdapter(cmd)
            da.Fill(ds)
            gdv_assign.DataSource = ds.Tables(0)
            conn.Close()
        Catch ex As Exception
            '  MsgBox(ex.Message)
        End Try
    End Sub
    Private Sub FillreceiveGrid()
        Try
            If conn.State <> ConnectionState.Open Then
                conn.Open()
            End If
            Dim cmdtext As String = "SO_FillreceiveGrid"
            cmd = Nothing
            cmd = New SqlCommand(cmdtext, conn)
            cmd.CommandType = CommandType.StoredProcedure

            'Dim ssql As String
            'ssql = "select file_id as Serial, file_name as Files, received_date as dated from tbl_parent  where status = 'N' order by file_id desc"
            Dim ds As New DataSet
            Dim da As New SqlDataAdapter(cmd)
            da.Fill(ds)
            gdv_rec.DataSource = ds.Tables(0)
            If conn.State <> ConnectionState.Closed Then
                conn.Close()
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Private Sub cbm_users_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbm_users.SelectedIndexChanged
        FillassignGrid()
        reset_form()
    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If Me.txt_start.Text.Trim = "" Or Me.txt_end.Text.Trim = "" Then
            Exit Sub
        End If
        If CInt(Me.txt_start.Text.Trim) > CInt(Me.txt_end.Text.Trim) Then
            MsgBox("Sequence start value is more than end value. Check Start and end value.", MsgBoxStyle.Information, "Assigning Data")
            Exit Sub
        End If
        Dim a As DialogResult = MsgBox("Are you sure, You want to assign data to " & Me.cbm_users.Text & " ?", MsgBoxStyle.OkCancel, "Receive Files")
        If a = Windows.Forms.DialogResult.Cancel Then Exit Sub
        Dim cmdtext As String = "SO_AssignData"
        cmd = Nothing
        cmd = New SqlCommand(cmdtext, conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@Start", Me.txt_start.Text.Trim)
        cmd.Parameters.AddWithValue("@end", txt_end.Text.Trim)
        Dim ds As New DataSet
        ' Dim da As New SqlDataAdapter("select * from tbl_parent where status <> 'N' and (file_id between " & Me.txt_start.Text.Trim & " and " & txt_end.Text.Trim & ")", conn)
        Dim da As New SqlDataAdapter(cmd)
        da.Fill(ds)
        If ds.Tables(0).Rows.Count > 0 Then
            MsgBox("The sequence specified is either fully or partly already assigned to other users. Check Start and end value for data serial.", MsgBoxStyle.Information, "Assigning Data")
            Exit Sub
        Else
            Dim text As String = "SO_UpdatetblParent"
            cmd = Nothing
            cmd = New SqlCommand(text, conn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@user", cbm_users.SelectedValue.ToString.Trim)
            cmd.Parameters.AddWithValue("@Start", Me.txt_start.Text.Trim)
            cmd.Parameters.AddWithValue("@end", txt_end.Text.Trim)
            'cmd = New SqlCommand("update tbl_parent set assigned_date = getdate() , assigned_to = " & cbm_users.SelectedValue.ToString.Trim & ", status = 'A' where file_id between '" & Me.txt_start.Text.Trim & "' and '" & txt_end.Text.Trim & "'", conn)
            If conn.State = 0 Then
                conn.Open()
            End If
            cmd.ExecuteNonQuery()
            MsgBox("The data sequence specified is succesfully assigned to " & Me.cbm_users.Text & " .", MsgBoxStyle.Information, "Assigning Data")
            reset_form()
        End If
        FillreceiveGrid()
        FillassignGrid()
    End Sub
    Private Sub txt_start_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_start.KeyPress
        e.Handled = commonUser.Common.TrapKey(Asc(e.KeyChar))
    End Sub
    Private Sub txt_end_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_end.KeyPress
        e.Handled = commonUser.Common.TrapKey(Asc(e.KeyChar))
    End Sub
    Private Sub reset_form()
        txt_start.Text = ""
        txt_end.Text = ""
        txt_start.Focus()
    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        reset_form()
    End Sub
End Class