Imports System.Data.SqlClient
Public Class frm_reassign
    Dim da As SqlDataAdapter = Nothing
    Dim conn As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("SO_Conn"))
    Dim cmd As SqlCommand = Nothing
    Private Sub frm_reassign_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Filluser()
        'FillreceiveGrid()
        FillreassignGrid()
        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_reassignusers.DataSource = ds.Tables(0)
            cbm_reassignusers.DisplayMember = "USER_NAME"
            cbm_reassignusers.ValueMember = "id"
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Private Sub Fillassigngrid()
        If cbm_reassignusers.SelectedValue.ToString.Trim = "System.Data.DataRowView" Or cbm_reassignusers.SelectedValue.ToString.Trim = "" Then Exit Sub
        Try
            If conn.State <> ConnectionState.Open Then
                conn.Open()
            End If

            Dim cmdtext As String = "SO_Frmreassignfillassinggrid2"
            cmd = Nothing
            cmd = New SqlCommand(cmdtext, conn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@name", cbm_reassignusers.SelectedValue.ToString.Trim)
            'cmd.Parameters.AddWithValue("@end", txt_end.Text.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_assigned.DataSource = ds.Tables(0)
            conn.Close()
        Catch ex As Exception
            '  MsgBox(ex.Message)
        End Try
    End Sub
    Private Sub FillreassignGrid()
        If cbm_reassignusers.SelectedValue.ToString.Trim = "System.Data.DataRowView" Or cbm_reassignusers.SelectedValue.ToString.Trim = "" Then Exit Sub
        Try
            If conn.State <> ConnectionState.Open Then
                conn.Open()
            End If

            Dim cmdtext As String = "SO_FrmReassignFillassigngrid1"
            cmd = Nothing
            cmd = New SqlCommand(cmdtext, conn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@name", cbm_reassignusers.SelectedValue.ToString.Trim)
            'cmd.Parameters.AddWithValue("@end", txt_end.Text.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_reassignusers_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbm_reassignusers.SelectedIndexChanged
        Fillassigngrid()
        FillreassignGrid()
        reset_form()

    End Sub
    Private Sub reset_form()
        txt_start.Text = ""
        txt_end.Text = ""
        txt_percentage.Text = ""
        txt_start.Focus()
    End Sub

    Private Sub btn_assign_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_assign.Click
        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_reassignusers.Text & " ?", MsgBoxStyle.OkCancel, "Receive Files")
        If a = Windows.Forms.DialogResult.Cancel Then Exit Sub
        Dim cmdtext As String = "SO_FrmReassign_getfiles"
        cmd = Nothing
        cmd = New SqlCommand(cmdtext, conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@assignedto", cbm_reassignusers.SelectedValue.ToString.Trim)
        cmd.Parameters.AddWithValue("@Start", txt_start.Text.ToString.Trim)
        cmd.Parameters.AddWithValue("@end", txt_end.Text.ToString.Trim)
        If conn.State = 0 Then
            conn.Open()
        End If
        Dim ds1 As New DataSet
        Dim da1 As New SqlDataAdapter(cmd)
        da1.Fill(ds1)
        If ds1.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 cmdtext2 As String = "SO_Frmreassigninsertpercentage"
            cmd = Nothing
            cmd = New SqlCommand(cmdtext2, conn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@assignedto", cbm_reassignusers.SelectedValue.ToString.Trim)
            cmd.Parameters.AddWithValue("@percentage", txt_percentage.Text.Trim)

            If conn.State = 0 Then
                conn.Open()
            End If
            cmd.ExecuteNonQuery()
            Dim dr1 As SqlDataReader = Nothing
            Dim value As String = String.Empty
            cmd = New SqlCommand("SO_Frmreassignagainassign", conn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@Start", txt_start.Text.ToString.Trim)
            cmd.Parameters.AddWithValue("@end", txt_end.Text.ToString.Trim)
            dr1 = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            If (dr1.Read) Then
                value = dr1(0).ToString
            End If
            dr1.Close()
            If value = "No rows found" Then

                Dim cmdtext1 As String = "SO_FrmReassignFillassigngrid"
                cmd = Nothing
                cmd = New SqlCommand(cmdtext1, conn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@Start", txt_start.Text.ToString.Trim)
                cmd.Parameters.AddWithValue("@end", txt_end.Text.ToString.Trim)

                If conn.State = 0 Then
                    conn.Open()
                End If
                Dim dr As DataRow
                Dim dt As DataTable
                Dim ds As New DataSet
                Dim da As New SqlDataAdapter(cmd)
                da.Fill(ds)
                dt = ds.Tables(0)
                For Each dr In dt.Rows

                    CreateReassign(dr(0).ToString, dr(1).ToString, cbm_reassignusers.SelectedValue.ToString.Trim)

                Next
                ds.Dispose()
            End If
            Dim cmdtext3 As String = "SO_Frmredataentryassign"
            cmd = Nothing
            cmd = New SqlCommand(cmdtext3, conn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@assignedto", cbm_reassignusers.SelectedValue.ToString.Trim)
            cmd.Parameters.AddWithValue("@Start", txt_start.Text.ToString.Trim)
            cmd.Parameters.AddWithValue("@end", txt_end.Text.ToString.Trim)
            If conn.State = 0 Then
                conn.Open()
            End If
            cmd.ExecuteNonQuery()
            'Dim cmdtext2 As String = "SO_Frmreassigninsertpercentage"
            'cmd = Nothing
            'cmd = New SqlCommand(cmdtext2, conn)
            'cmd.CommandType = CommandType.StoredProcedure
            'cmd.Parameters.AddWithValue("@assignedto", cbm_reassignusers.SelectedValue.ToString.Trim)
            'cmd.Parameters.AddWithValue("@percentage", txt_percentage.Text.Trim)

            'If conn.State = 0 Then
            '    conn.Open()
            'End If
            'cmd.ExecuteNonQuery()
            MsgBox("The data sequence specified is succesfully assigned to " & Me.cbm_reassignusers.Text & " .", MsgBoxStyle.Information, "Assigning Data")
            reset_form()
            FillreassignGrid()
            'FillassignGrid()
            End If
    End Sub
    Private Sub CreateReassign(ByVal file_id As Integer, ByVal File_name As String, ByVal assigned_to As String)

        Dim cmdtext As String = "SO_FrmreassignInsert"
        cmd = Nothing
        cmd = New SqlCommand(cmdtext, conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@fileid", file_id)
        cmd.Parameters.AddWithValue("@filename", File_name)
        cmd.Parameters.AddWithValue("@assignedto", assigned_to)
        If conn.State = 0 Then
            conn.Open()
        End If
        cmd.ExecuteNonQuery()
    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 btn_reset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_reset.Click
        reset_form()
    End Sub

End Class