Imports System.Data.SqlClient
Public Class frm_quickview
    Dim conn As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("SO_Conn"))
    Dim cmd As SqlCommand = Nothing
    'Dim im_location As String = "C:\r_image\"
    Dim im_location As String = "\\PINNACLE-SO\NCFiles"
    'Dim im_location As String = "\\system-1\scana\"
    'Dim im_location As String = "C:\Low Country\Beaufort Office\Part 1\"
    Dim im_loc As String = ""
    Private Sub frm_quickview_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        getfiledetail(commonUser.Common.imageid)
    End Sub
    Private Sub getfiledetail(ByVal file_id As Integer)
        If conn.State = 0 Then
            conn.Open()
        End If

        'Dim dr As SqlDataReader = Nothing
        'cmd = New SqlCommand("select file_id,File_name, File_name, file_type from tbl_parent  where file_id = " & file_id, conn)
        'dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        'If (dr.Read) Then
        '    Me.txt_fileid.Text = dr(0).ToString
        '    Me.txt_filedesc.Text = dr(1).ToString
        '    If dr(3).ToString.Trim.Length > 0 Then
        '        im_loc = im_location & dr(3).ToString.Trim & "\"
        '    End If
        '    AxPdf1.LoadFile(im_loc & dr(2).ToString)
        'End If
        'Dim cmdtext As String = "SO_QVgetfiledetail"
        'cmd = Nothing
        'cmd = New SqlCommand(cmdtext, conn)
        'cmd.CommandType = CommandType.StoredProcedure
        'cmd.Parameters.AddWithValue("@imglocation", im_location)
        'cmd.Parameters.AddWithValue("@fileid", file_id)

        'Dim dr As SqlDataReader = Nothing
        ''cmd = New SqlCommand("select tp.file_id, tp.File_name, tp.status, tp.file_type, replace(replace(replace(ti.externalref,'.tif','.pdf'),'C:\export test\','" & im_location & "'), '\\scana\shares\apps\apps\filenet\efi\Gas Project Files\','" & im_location & "') as externalref from tbl_parent tp, ( select replace(substring(externalref,charindex('Page',externalref),25),'.tif','.pdf') as file_name, externalref, service_order from dbo.tbl_so_imagefiles ) ti where tp.service_order = ti.service_order and tp.file_name = ti.file_name and tp.file_id = " & file_id, conn)
        ''cmd = New SqlCommand("select tp.file_id, tp.File_name, tp.status, tp.file_type, replace(replace(replace(ti.externalref,'.tif','.pdf'),'C:\export test\','" & im_location & "'), '\\scana\shares\apps\apps\filenet\efi\Gas Project Files\','" & im_location & "') as externalref from tbl_parent tp, ( select replace(substring(externalref,charindex('Page',externalref),25),'.tif','.pdf') as file_name, externalref, service_order from dbo.tbl_so_imagefiles ) ti where tp.service_order = ti.service_order and tp.file_name = ti.file_name and tp.file_id = " & file_id & " and tp.assigned_to = " & commonUser.Common.uID, conn)

        'dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        'If (dr.Read) Then
        '    Me.txt_fileid.Text = dr(0).ToString
        '    Me.txt_filedesc.Text = dr(1).ToString
        '    AxPdf1.LoadFile(im_location + dr(4).ToString)
        '    'AxPdf1.LoadFile(dr(4).ToString)
        'End If
        Dim dr1 As SqlDataReader = Nothing
        cmd = New SqlCommand("SO_QVgetfiledetail", conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@imglocation", im_location)
        cmd.Parameters.AddWithValue("@fileid", file_id)
        'cmd.Parameters.AddWithValue("@assignedto", commonUser.Common.uID)
        ' cmd = New SqlCommand("select tp.file_id, tp.File_name, tp.status, tp.file_type, replace(replace(replace(ti.externalref,'.tif','.pdf'),'C:\export test\','" & im_location & "'), '\\scana\shares\apps\apps\filenet\efi\Gas Project Files\','" & im_location & "') as externalref from tbl_parent tp, ( select replace(substring(externalref,charindex('Page',externalref),25),'.tif','.pdf') as file_name, externalref, service_order from dbo.tbl_so_imagefiles ) ti where tp.service_order = ti.service_order and tp.file_name = ti.file_name and tp.file_id = " & file_id & " and tp.assigned_to = " & commonUser.Common.uID, conn)
        dr1 = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        If (dr1.Read) Then
            Me.txt_fileid.Text = CInt(dr1(0).ToString)
            Me.txt_filedesc.Text = dr1(1).ToString
            'AxPdf1.LoadFile("C:\Low Country\Beaufort Office\Part 1\content\Page 3.pdf")
            AxPdf1.LoadFile(im_location + dr1(4).ToString)

        End If
        dr1.Close()
        If txt_filedesc.Text.Trim <> "" Then
            check_dual_pdf(txt_filedesc.Text)
        End If
    End Sub
    Private Sub check_dual_pdf(ByVal file_desc As String)
        If conn.State = 0 Then
            conn.Open()
        End If
        Dim dr As SqlDataReader = Nothing
        'cmd = New SqlCommand("select replace(substring(externalref,charindex('Page',externalref),25),'.tif','.pdf') from tbl_so_imagefiles where service_order = (select service_order from tbl_parent where file_id = " & Me.txt_fileid.Text.Trim & " ) and substring(externalref,charindex('Page',externalref),25) <> replace('" & file_desc & "','.pdf','.tif')", conn)
        Dim cmdtext As String = "SO_QVchkdualpdf"
        cmd = Nothing

        cmd = New SqlCommand(cmdtext, conn)
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.AddWithValue("@fileid", Me.txt_fileid.Text.Trim)
        cmd.Parameters.AddWithValue("@filedesc", file_desc)

        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        If (dr.Read) Then
            btn_flip.Enabled = True
        Else
            btn_flip.Enabled = False
        End If
        dr.Close()
    End Sub
    Function getprev_fileid(ByVal file_id As Integer)
        Dim fileid As Integer
        If conn.State = 0 Then
            conn.Open()
        End If
        Dim dr As SqlDataReader = Nothing
        Dim cmdtext As String = "SO_QVgetprev_fileid"

        cmd = Nothing
        cmd = New SqlCommand(cmdtext, conn)
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.AddWithValue("@fileid", file_id)
        'cmd = New SqlCommand("select ISNULL(max(file_id),0) from tbl_parent  where file_id < " & file_id, conn)
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        

        If (dr.Read) Then
            fileid = CInt(dr(0).ToString)
        Else
            fileid = 0
        End If
        dr.Close()
        Return fileid
    End Function
    Function getnext_fileid(ByVal file_id As Integer)
        Dim fileid As Integer
        If conn.State = 0 Then
            conn.Open()
        End If
        Dim dr As SqlDataReader = Nothing
        Dim cmdtext As String = "SO_QVgetnextfield"

        cmd = Nothing
        cmd = New SqlCommand(cmdtext, conn)
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.AddWithValue("@fileid", file_id)
        'cmd = New SqlCommand("select ISNULL(min(file_id),0) from tbl_parent  where file_id > " & file_id, conn)
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        

        If (dr.Read) Then
            fileid = CInt(dr(0).ToString)
        Else
            fileid = 0
        End If
        dr.Close()
        Return fileid
    End Function

    Private Sub btn_next_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_next.Click
        If (getnext_fileid(CInt(Me.txt_fileid.Text.Trim)) > 0) Then
            getfiledetail(getnext_fileid(CInt(Me.txt_fileid.Text.Trim)))
        Else
            MsgBox("No More image files available.", MsgBoxStyle.Information, "Next Image")
        End If
    End Sub

    Private Sub btn_prev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_prev.Click
        If (getprev_fileid(CInt(Me.txt_fileid.Text.Trim)) > 0) Then
            getfiledetail(getprev_fileid(CInt(Me.txt_fileid.Text.Trim)))
        Else
            MsgBox("No More images available.", MsgBoxStyle.Information, "Previous Image")
        End If
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        getfiledetail(commonUser.Common.imageid)
    End Sub

    Private Sub btn_flip_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_flip.Click
        If conn.State = 0 Then
            conn.Open()
        End If
        Dim file_desc As String
        file_desc = Me.txt_filedesc.Text.Trim
        'Dim dr As SqlDataReader = Nothing
        'cmd = New SqlCommand("select replace(substring(externalref,charindex('Page',externalref),25),'.tif','.pdf') as externalref from tbl_so_imagefiles where service_order = (select service_order from tbl_parent where file_id = " & Me.txt_fileid.Text.Trim & " ) and substring(externalref,charindex('Page',externalref),25) <> replace('" & file_desc & "','.pdf','.tif')", conn)
        'dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        'If (dr.Read) Then
        '    btn_flip.Enabled = True
        '    AxPdf1.LoadFile(im_loc & dr(0).ToString)
        '    txt_filedesc.Text = dr(0).ToString
        'End If
        Dim dr As SqlDataReader = Nothing
        Dim cmdtext As String = "SO_Frmperfobtn_flip"

        cmd = Nothing


        cmd = New SqlCommand(cmdtext, conn)
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.AddWithValue("@fileid", Me.txt_fileid.Text.Trim)
        cmd.Parameters.AddWithValue("@filedesc", file_desc)
        cmd.Parameters.AddWithValue("@im_location", im_location)
        'cmd = New SqlCommand("select replace(substring(externalref,charindex('Page',externalref),25),'.tif','.pdf') as externalref1, replace(replace(replace(externalref,'.tif','.pdf'),'C:\export test\','" & im_location & "'), '\\scana\shares\apps\apps\filenet\efi\Gas Project Files\','" & im_location & "') as externalref from tbl_so_imagefiles where service_order = (select service_order from tbl_parent where file_id = " & Me.txt_fileid.Text.Trim & " ) and substring(externalref,charindex('Page',externalref),25) <> replace('" & file_desc & "','.pdf','.tif')", conn)
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        
        If (dr.Read) Then
            btn_flip.Enabled = True
            AxPdf1.LoadFile(dr(1).ToString)
            txt_filedesc.Text = dr(0).ToString
        End If
        dr.Close()
        If conn.State = ConnectionState.Open Then
            conn.Close()
        End If
    End Sub
End Class