Imports System.Data
Imports System.Data.SqlClient

Public Class BestellDetail

    Dim bolNeu As Boolean = False

    ''' <summary>
    ''' Konstruktor für Änderungen an einer Bestellung.
    ''' </summary>
    Public Sub New(ByVal BestellID As Integer, ByVal ProduktID As Integer)
        Using conVerbindung As New SqlConnection(My.Settings.NWindConnectionString)
            Dim cmdBestellung As New SqlCommand
            With cmdBestellung
                .CommandText = "SELECT * FROM order_details WHERE OrderID = " & BestellID & " AND ProductID = " & ProduktID
                .Connection = conVerbindung
            End With

            conVerbindung.Open()
            Dim drBestellDetails As SqlDataReader = cmdBestellung.ExecuteReader
            While drBestellDetails.Read()
                _intBestellID = BestellID
                _intProduktID = ProduktID
                _dblStueckpreis = drBestellDetails("UnitPrice")
                _intMenge = drBestellDetails("Quantity")
                _dblRabatt = drBestellDetails("Discount")
            End While

        End Using
    End Sub

    ''' <summary>
    ''' Konstruktor für neue Details zu einer Bestellung.
    ''' </summary>
    ''' <param name="BestellID">ID der Bestellung, zu der ein neuer Eintrag hinzugefügt wird.</param>
    ''' <remarks></remarks>
    Public Sub New(ByVal BestellID As Integer)
        _intBestellID = BestellID
        bolNeu = True
    End Sub

    ''' <summary>
    ''' Überträgt die Änderungen in die Datenbank.
    ''' </summary>
    ''' <returns>True bei Erfolg.</returns>
    Public Function Speichern() As Boolean
        If bolNeu = True Then
            ' INSERT
            Using conVerbindung As New SqlConnection(My.Settings.NWindConnectionString)
                Dim cmdBestellung As New SqlCommand
                With cmdBestellung
                    .CommandText = "INSERT INTO order_details VALUES (@OrderID, @ProduktID, @UnitPrice, @Quantity, @Discount)"
                    .Connection = conVerbindung
                End With

                Dim parBestellID As New SqlParameter("@OrderID", _intBestellID)
                Dim parProduktID As New SqlParameter("@ProduktID", _intProduktID)
                Dim parStueckpreis As New SqlParameter("@UnitPrice", _dblStueckpreis)
                Dim parMenge As New SqlParameter("@Quantity", _intMenge)
                Dim parRabatt As New SqlParameter("@Discount", _dblRabatt)

                With cmdBestellung.Parameters
                    .Add(parBestellID)
                    .Add(parProduktID)
                    .Add(parStueckpreis)
                    .Add(parMenge)
                    .Add(parRabatt)
                End With

                Try
                    conVerbindung.Open()
                    Dim intRowsAffected As Integer = cmdBestellung.ExecuteNonQuery
                    If intRowsAffected = 1 Then
                        Return True
                    Else
                        Return False
                    End If
                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                    Return False
                End Try
            End Using
        Else
            ' UPDATE
            Using conVerbindung As New SqlConnection(My.Settings.NWindConnectionString)
                Dim cmdBestellung As New SqlCommand
                With cmdBestellung
                    .CommandText = "UPDATE order_details " _
                    & "SET unitprice = @UnitPrice, quantity = @Quantity, discount = @Discount " _
                    & "WHERE orderid = @OrderID AND productid = @ProduktID"
                    .Connection = conVerbindung
                End With

                Dim parBestellID As New SqlParameter("@OrderID", _intBestellID)
                Dim parProduktID As New SqlParameter("@ProduktID", _intProduktID)
                Dim parStueckpreis As New SqlParameter("@UnitPrice", _dblStueckpreis)
                Dim parMenge As New SqlParameter("@Quantity", _intMenge)
                Dim parRabatt As New SqlParameter("@Discount", _dblRabatt)

                With cmdBestellung.Parameters
                    .Add(parBestellID)
                    .Add(parProduktID)
                    .Add(parStueckpreis)
                    .Add(parMenge)
                    .Add(parRabatt)
                End With

                Try
                    conVerbindung.Open()
                    Dim intRowsAffected As Integer = cmdBestellung.ExecuteNonQuery
                    If intRowsAffected = 1 Then
                        Return True
                    Else
                        Return False
                    End If
                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                    Return False
                End Try
            End Using

        End If
    End Function

#Region "Properties"
    Private _intBestellID As Integer

    Public Property BestellID() As Integer
        Get
            Return _intBestellID
        End Get
        Set(ByVal value As Integer)
            _intBestellID = value
        End Set
    End Property

    Private _intProduktID As Integer

    Public Property ProduktID() As Integer
        Get
            Return _intProduktID
        End Get
        Set(ByVal value As Integer)
            _intProduktID = value
        End Set
    End Property

    Private _dblStueckpreis As Integer

    Public Property Stueckpreis() As Double
        Get
            Return _dblStueckpreis
        End Get
        Set(ByVal value As Double)
            _dblStueckpreis = value
        End Set
    End Property

    Private _intMenge As Integer

    Public Property Menge() As Integer
        Get
            Return _intMenge
        End Get
        Set(ByVal value As Integer)
            _intMenge = value
        End Set
    End Property

    Private _dblRabatt As Double

    Public Property Rabatt() As Double
        Get
            Return _dblRabatt
        End Get
        Set(ByVal value As Double)
            _dblRabatt = value
        End Set
    End Property
#End Region 'Properties
End Class
