Imports System
Imports System.Data
Imports System.Data.SqlTypes
Imports System.Data.SqlClient
Imports System.IO

Namespace TKB.VV.Lifecycle
    Public Class clsProduktImage
        Dim images As New DataSet

        Public Function Load_Images(ByVal Produktnr As Integer, ByRef grpbilder As GroupBox, ByRef tstrip As ToolStrip)
            For Each ct As Control In grpbilder.Controls
                If ct.Name.Substring(0, 1) = "P" Then
                    ct.Visible = False
                    ct.Dispose()
                    grpbilder.Controls.Remove(ct)
                End If
            Next
            Application.DoEvents()
            Dim i As Integer = 0
            GetImages(Produktnr)
            If images.Tables(0).Rows.Count > 1 Then tstrip.Visible = True Else tstrip.Visible = False
            For Each r As DataRow In images.Tables(0).Rows
                i = i + 1
                Dim arrayImage() As Byte = CType(r.Item("Image"), Byte())
                Dim ms As New MemoryStream(arrayImage)
                Dim picbox As New PictureBox
                With picbox
                    .Image = Image.FromStream(ms)
                    .SizeMode = PictureBoxSizeMode.Zoom
                End With
                If i = 1 Then picbox.Visible = True Else picbox.Visible = False
                picbox.Name = "P" + i.ToString
                grpbilder.Controls.Add(picbox)
                picbox.Dock = DockStyle.Fill
                ms.Dispose()
            Next
            Try
                Dim lbl As Label = grpbilder.Controls("lblMaxImage")
                lbl.Text = i.ToString
                lbl = grpbilder.Controls("lblCurrentImage")
                lbl.Text = "P1"
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            grpbilder.Refresh()
        End Function

        Private Function GetImages(ByVal Produktnr As Integer) As DataTable
            images.Tables.Clear()
            Dim selectcommand As New SqlCommand
            Dim connection As New SqlConnection()
            Dim da As New SqlDataAdapter("Select * from dbo.lc_produktimage where aktiv=1 and lc_produktnr = " + Produktnr.ToString + " order by lc_produktimagenr", connection)
            selectcommand.CommandType = CommandType.Text
            selectcommand.Connection = connection
            Try
                connection.ConnectionString = Globals.sConnectionString
                connection.Open()
                da.Fill(images, "ProductImages")
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                connection.Close()
                da.Dispose()
                selectcommand.Dispose()
            End Try
        End Function

        Public Function Save_Image(ByVal Produktnr As Integer, ByRef PicBox As PictureBox)
            Dim ms As New MemoryStream
            PicBox.Image.Save(ms, PicBox.Image.RawFormat)
            Dim connection As New SqlConnection(Globals.sConnectionString)

            Dim arrayImage() As Byte = ms.GetBuffer
            ms.Close() ' Closes the Memory Stream

            Dim strQuery As String = "INSERT INTO dbo.lc_produktImage (lc_produktnr, image, erstellt_am, mutiert_am, mutierer, aktiv) " + _
                " VALUES(@Produktnr, @image, @erstellt_am, @mutiert_am, @mutierer, @aktiv)"

            Dim objcommand As New SqlCommand(strQuery, connection)

            With objcommand
                .Parameters.Add(New SqlParameter("@Produktnr", SqlDbType.Int, 4)).Value = Produktnr
                .Parameters.Add(New SqlParameter("@Image", SqlDbType.Image)).Value = arrayImage
                .Parameters.Add(New SqlParameter("@Erstellt_am", SqlDbType.DateTime)).Value = Now
                .Parameters.Add(New SqlParameter("@Mutiert_am", SqlDbType.DateTime)).Value = Now
                .Parameters.Add(New SqlParameter("@Mutierer", SqlDbType.Int)).Value = Globals.clsmitarbeiter.iMitarbeiternr.Value
                .Parameters.Add(New SqlParameter("@aktiv", SqlDbType.Bit)).Value = 1
            End With

            connection.Open()
            objcommand.ExecuteNonQuery()
            connection.Close()
        End Function


        Public Function Delete_Image(Imagenr)
            Dim connection As New SqlConnection(Globals.sConnectionString)
            Dim s As String

            Dim objcommand As New SqlCommand("Update lc_produktimage set aktiv=0,mutierer=" + Globals.clsmitarbeiter.iMitarbeiternr.Value.ToString + ", mutiert_am='" + Now.ToString + "' where lc_produktimageNr=" + Imagenr.ToString, connection)
            connection.Open()
            objcommand.ExecuteNonQuery()
            connection.Close()
        End Function

        Public Function Delete_Image_Physisch(Imagenr)
            Dim connection As New SqlConnection(Globals.sConnectionString)
            Dim s As String

            Dim objcommand As New SqlCommand("delete from  lc_produktimage where lc_produktimageNr=" + Imagenr.ToString, connection)
            connection.Open()
            objcommand.ExecuteNonQuery()
            connection.Close()
        End Function

    End Class


End Namespace