Imports System.Data.SqlClient
Public Class frm_receive
    Dim conn As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("SO_Conn"))
    Dim cmd As SqlCommand = Nothing
    Dim dr As SqlDataReader = Nothing
    Private Sub frm_receive_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        check_files()
        FillGrid()
    End Sub
    Private Sub check_files()
        Try
            If conn.State = ConnectionState.Open Then
            Else
                'conn.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings("SO_Conn").ToString()
                conn.Open()
            End If
            Dim ssql As String

            'ssql = "select replace(min(externalref),'.tif','.pdf') [Card File],datecreated [Dated], service_order [Service order ]from (select sc.datecreated, substring(externalref,charindex('Page',externalref),25) as externalref, sc.service_order from tbl_servicecards sc join tbl_so_imagefiles si on sc.service_order = si.service_order where sc.status = 'N') ss group by service_order, datecreated order by datecreated"
            Dim cmdtext As String = "SO_FrmReceive"
            cmd = Nothing
            cmd = New SqlCommand(cmdtext, conn)
            cmd.CommandType = CommandType.StoredProcedure

            Dim ds As New DataSet
            Dim da As New SqlDataAdapter(cmd)
            da.Fill(ds)
            gdv_newimages.DataSource = ds.Tables(0)
            conn.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try



    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim a As DialogResult = MsgBox("Are you sure, You want to import image data into the system?", MsgBoxStyle.OkCancel, "Receive Files")
        If a = Windows.Forms.DialogResult.Cancel Then Exit Sub
        'Dim di As New IO.DirectoryInfo("c:\f_image\")
        Dim fileid As Integer
        'Dim dr = "c:\f_image\"
        'Dim da = "c:\r_image\"
        'Dim diar1 As IO.FileInfo() = di.GetFiles()
        'Dim dra As IO.FileInfo
        If conn.State = 0 Then
            conn.Open()
        End If
        fileid = get_lastfileid() + 10

        Dim ssql As String
        'ssql = "select replace(min(externalref),'.tif','.pdf') as externalref,datecreated [Dated], service_order from (select sc.datecreated, substring(externalref,charindex('Page',externalref),25) as externalref, sc.service_order from tbl_servicecards sc join tbl_so_imagefiles si on sc.service_order = si.service_order where sc.status = 'N') ss group by service_order, datecreated order by datecreated"
        Dim cmdtext As String = "SO_FrmReceive1"
        cmd = Nothing
        cmd = New SqlCommand(cmdtext, conn)
        cmd.CommandType = CommandType.StoredProcedure

        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

            createparent(fileid, dr("externalref"), dr("service_order"))
            fileid = fileid + 10
        Next
        ds.Dispose()


        'list the names of all files in the specified directory
        'For Each dra In diar1
        '    createparent(fileid, dra.Name)
        '    IO.File.Copy(dr & dra.Name, da & dra.Name)
        '    IO.File.Delete(dr & dra.Name)
        '    fileid = fileid + 10
        'Next
        MsgBox("File imported sucessfully", MsgBoxStyle.Information, "File Received")
        update_serviceorder()
        check_files()
        FillGrid()
        reset_form()
        If conn.State = 1 Then
            conn.Close()
        End If
    End Sub
    Private Sub createparent(ByVal file_id As Integer, ByVal imgname As String, ByVal service_order As String)
        '' cmd = New SqlCommand("insert into tbl_parent(file_id,file_name,file_type, city," _
        '                            & "street_name,status,received_date,received_by,service_order) " _
        '                            & "values(" & file_id & ",'" & imgname & "','" & cmb_office.Text.Trim & "', '" & txt_city.Text.Trim & "','" & txt_street.Text.Trim & "','N',Getdate(),'" & commonUser.Common.uID & "', '" & service_order & "')", conn)

        Dim cmdtext As String = "SO_FrmReceivecreateparent"
        cmd = Nothing
        cmd = New SqlCommand(cmdtext, conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@FileId", file_id)
        cmd.Parameters.AddWithValue("@FileName", imgname)
        cmd.Parameters.AddWithValue("@FileType", cmb_office.Text.Trim)
        cmd.Parameters.AddWithValue("@City", txt_city.Text.Trim)
        cmd.Parameters.AddWithValue("@StreetName", txt_street.Text.Trim)
        cmd.Parameters.AddWithValue("@ReceivedBy", commonUser.Common.uID)
        cmd.Parameters.AddWithValue("@ServiceOrder", service_order)


        If conn.State = 0 Then
            conn.Open()
        End If
        cmd.ExecuteNonQuery()
    End Sub
    Private Sub update_serviceorder()
        'cmd = New SqlCommand("update tbl_servicecards set status = 'R' where status = 'N'", conn)
        Dim cmdtext As String = "SO_FrmreceiveUpdateServiceOrder"

        cmd = Nothing
        cmd = New SqlCommand(cmdtext, conn)
        cmd.CommandType = CommandType.StoredProcedure
        If conn.State = 0 Then
            conn.Open()
        End If
        cmd.ExecuteNonQuery()
    End Sub
    Private Function get_lastfileid()
        Dim fileid As Integer
        'cmd = New SqlCommand("select ISNULL(max(file_id),0) from tbl_parent ", conn)
        Dim cmdtext As String = "SO_FrmReceiveGetLastfield"

        cmd = Nothing
        cmd = New SqlCommand(cmdtext, conn)
        cmd.CommandType = CommandType.StoredProcedure
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        If (dr.Read) Then
            fileid = CInt(dr(0).ToString)
        End If
        dr.Close()
        Return fileid
    End Function
    Private Sub FillGrid()
        Try
            If conn.State = ConnectionState.Open Then
            Else
                conn.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings("SO_Conn").ToString()
                conn.Open()
            End If
            Dim ssql As String

            'ssql = "select file_name as Files, received_date as dated from tbl_parent  where status = 'N' order by file_id desc"

            Dim cmdtext As String = "SO_FrmReceiveFillGrid"

            cmd = Nothing
            cmd = New SqlCommand(cmdtext, conn)
            cmd.CommandType = CommandType.StoredProcedure
            Dim ds As New DataSet
            Dim da As New SqlDataAdapter(cmd)
            da.Fill(ds)
            gdv_rec.DataSource = ds.Tables(0)
            conn.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Private Sub reset_form()
        txt_city.Text = ""
        txt_street.Text = ""
        Me.cmb_office.Focus()
    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        reset_form()
    End Sub

    Private Sub btn_refresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_refresh.Click
        check_files()
        FillGrid()
    End Sub
End Class