Imports System.Data.OleDb
Imports Mediatheek.data

Public Class clsUitvoeringDA

#Region "Get uitvoeringen"
    Public Shared Function GetUitvoeringenByAlbumID(ByVal sAlbumID As String) As ArrayList
        'functie om  uitvoeringen per albumID terug te vinden
        Dim sSQL As String = "SELECT * FROM tblUitvoering WHERE AlbumID=" & sAlbumID
        Dim oDataTable As DataTable = clsDatabase.GetDT(sSQL)

        Dim arlUitvoeringenGevonden As ArrayList = New ArrayList
        Dim oRow As DataRow
        For Each oRow In oDataTable.Rows

            Dim sUitvoeringID, sGroepID, sLiedID, sGenreID As String

            sUitvoeringID = Convert.ToString(oRow.Item("UitvoeringID"))
            sGroepID = Convert.ToString(oRow.Item("GroepID"))
            sLiedID = Convert.ToString(oRow.Item("LiedID"))
            sGenreID = Convert.ToString(oRow.Item("GenreID"))
            Dim oUitvoering As New clsUitvoering(sUitvoeringID, sGroepID, sLiedID, sGenreID, sAlbumID)
            arlUitvoeringenGevonden.Add(oUitvoering)
        Next

        Return arlUitvoeringenGevonden

    End Function

    Public Shared Function GetUitvoeringenByLiedID(ByVal sLiedID As String) As ArrayList
        'functie om  uitvoeringen per liedID terug te vinden
        Dim sSQL As String = "SELECT * FROM tblUitvoering WHERE LiedID=" + sLiedID + ""

        Dim oDataTable As DataTable = clsDatabase.GetDT(sSQL)

        Dim arlUitvoeringenGevonden As ArrayList = New ArrayList
        Dim oRow As DataRow
        For Each oRow In oDataTable.Rows

            Dim sUitvoeringID, sGroepID, sAlbumID, sGenreID As String

            sUitvoeringID = Convert.ToString(oRow.Item("UitvoeringID"))
            sGroepID = Convert.ToString(oRow.Item("GroepID"))
            sGenreID = Convert.ToString(oRow.Item("GenreID"))
            sAlbumID = Convert.ToString(oRow.Item("AlbumID"))
            Dim oUitvoering As New clsUitvoering(sUitvoeringID, sGroepID, sLiedID, sGenreID, sAlbumID)
            arlUitvoeringenGevonden.Add(oUitvoering)
        Next

        Return arlUitvoeringenGevonden

    End Function

    Public Shared Function getVolgendUitvoeringId() As Integer
        'functie om het hoogste uitvoeringsid + 1 in te voeren om een nieuwe uitvoering te maken
        Try
            Dim sSQL As String = "SELECT MAX(UitvoeringId) FROM tblUitvoering"
            Dim iMaxId As Integer
            Try
                iMaxId = Convert.ToInt32(clsDatabase.ExecuteScalar(sSQL))
            Catch
                iMaxId = 0

            End Try
            Return iMaxId + 1
        Finally
        End Try
    End Function
#End Region

#Region "Toevoegen Uitvoeringen"
    Public Shared Sub VoegUitvoeringToe(ByVal oUitvoering As clsUitvoering)
        'toevoegen van een uitvoering
        Dim sValue As String
        sValue = System.Configuration.ConfigurationSettings.AppSettings.Item("ConnectionString")
        Dim oCon As System.Data.OleDb.OleDbConnection
        oCon = New OleDbConnection(sValue)
        oCon.Open()

        Dim oCommand As OleDbCommand
        oCommand = oCon.CreateCommand()

        oUitvoering.UitvoeringID = getVolgendUitvoeringId()
        Dim sInsert As String
        sInsert = "INSERT INTO tblUitvoering(UitvoeringId,GroepID,LiedID,GenreID,AlbumID)VALUES(?,?,?,?,?)"

        Dim oPar1 As New OleDbParameter("@UitvoeringID", oUitvoering.UitvoeringID)
        Dim oPar2 As New OleDbParameter("@GroepID", oUitvoering.GroepID)
        Dim oPar3 As New OleDbParameter("@LiedID", oUitvoering.LiedID)
        Dim oPar4 As New OleDbParameter("@GenreID", oUitvoering.GenreID)
        Dim oPar5 As New OleDbParameter("@AlbumID", oUitvoering.AlbumID)


        oCommand.CommandText = sInsert
        oCommand.Parameters.Add(oPar1)
        oCommand.Parameters.Add(oPar2)
        oCommand.Parameters.Add(oPar3)
        oCommand.Parameters.Add(oPar4)
        oCommand.Parameters.Add(oPar5)


        oCommand.ExecuteNonQuery()
        oCon.Close()
    End Sub
#End Region

#Region "Wijzigen uitvoeringen"

    Public Shared Function WijzigUitvoeringenGroepByAlbum(ByVal sAlbumID As String, ByVal sGroepID As String)
        'wijzigen van een uitvoering
        Dim oCon As OleDbConnection
        Try
            Dim sValue As String
            sValue = System.Configuration.ConfigurationSettings.AppSettings.Item("ConnectionString")
            Dim oCon2 As System.Data.OleDb.OleDbConnection
            oCon2 = New OleDbConnection(sValue)
            oCon2.Open()

            Dim oCommand2 As OleDbCommand
            oCommand2 = oCon2.CreateCommand()
            Dim sInsert As String
           
            sInsert = "UPDATE tblUitvoering SET GroepID=? WHERE AlbumID=?"


            Dim oPar1 As New OleDbParameter("@GroepID", sGroepID)
            Dim oPar2 As New OleDbParameter("@AlbumID", sAlbumID)

            oCommand2.CommandText = sInsert
            oCommand2.Parameters.Add(oPar1)
            oCommand2.Parameters.Add(oPar2)
            oCommand2.ExecuteNonQuery()
            oCon2.Close()


        Catch ex As Exception
            Debug.WriteLine(ex.Message)
            MsgBox("fout bij wijzigen album", MsgBoxStyle.Critical, "fout")
            Throw
        Finally
            '4. Connectie vrijgeven
            If Not oCon Is Nothing Then
                oCon.Close()
                oCon.Dispose()
            End If
        End Try
    End Function
#End Region

#Region "Verwijder Uitvoeringen"
    'verwijderen van een uitvoering
    Public Shared Function VerwijderUitvoeringenByLiedID(ByVal sLiedId As String)
        Dim oCon As OleDbConnection
        Try
            Dim sValue As String
            sValue = System.Configuration.ConfigurationSettings.AppSettings.Item("ConnectionString")
            Dim oCon3 As System.Data.OleDb.OleDbConnection
            oCon3 = New OleDbConnection(sValue)
            oCon3.Open()

            Dim oCommand3 As OleDbCommand
            oCommand3 = oCon3.CreateCommand()
            Dim sInsert As String
           

            sInsert = "DELETE FROM tblUitvoering WHERE LiedID=?"

            Dim oPar As New OleDbParameter("@LiedID", sLiedId)
            oCommand3.CommandText = sInsert
            oCommand3.Parameters.Add(oPar)


            oCommand3.ExecuteNonQuery()
            oCon3.Close()
        Catch ex As Exception
            Debug.WriteLine(ex.Message)
            Throw
        Finally

            If Not oCon Is Nothing Then
                oCon.Close()
                oCon.Dispose()
            End If
        End Try
    End Function

    Public Shared Function VerwijderUitvoeringenByID(ByVal sUitvoeringId As String)
        Dim oCon As OleDbConnection
        Try
            Dim sValue As String
            sValue = System.Configuration.ConfigurationSettings.AppSettings.Item("ConnectionString")
            Dim oCon3 As System.Data.OleDb.OleDbConnection
            oCon3 = New OleDbConnection(sValue)
            oCon3.Open()

            Dim oCommand3 As OleDbCommand
            oCommand3 = oCon3.CreateCommand()
            Dim sInsert As String


            sInsert = "DELETE FROM tblUitvoering WHERE UitvoeringID=?"

            Dim oPar As New OleDbParameter("@UitvoeringID", sUitvoeringId)
            oCommand3.CommandText = sInsert
            oCommand3.Parameters.Add(oPar)

            '3. Uitvoeren op de DB
            oCommand3.ExecuteNonQuery()
            oCon3.Close()
        Catch ex As Exception
            Debug.WriteLine(ex.Message)
            Throw
        Finally
            '4. Connectie vrijgeven
            If Not oCon Is Nothing Then
                oCon.Close()
                oCon.Dispose()
            End If
        End Try
    End Function

    Public Shared Function VerwijderUitvoeringenByAlbumID(ByVal sAlbumId As String)
        Dim oCon As OleDbConnection
        Try
            Dim sValue As String
            sValue = System.Configuration.ConfigurationSettings.AppSettings.Item("ConnectionString")
            Dim oCon3 As System.Data.OleDb.OleDbConnection
            oCon3 = New OleDbConnection(sValue)
            oCon3.Open()

            Dim oCommand3 As OleDbCommand
            oCommand3 = oCon3.CreateCommand()
            Dim sInsert As String
            '2. Command object aanmaken
            'DELETE FROM tabelNaam WHERE ...
            'DELETE FROM tblRekening WHERE Nummer='000-0000020-20'

            sInsert = "DELETE FROM tblUitvoering WHERE AlbumID=?"

            Dim oPar As New OleDbParameter("@AlbumID", sAlbumId)
            oCommand3.CommandText = sInsert
            oCommand3.Parameters.Add(oPar)

            '3. Uitvoeren op de DB
            oCommand3.ExecuteNonQuery()
            oCon3.Close()
        Catch ex As Exception
            Debug.WriteLine(ex.Message)
            Throw
        Finally
            '4. Connectie vrijgeven
            If Not oCon Is Nothing Then
                oCon.Close()
                oCon.Dispose()
            End If
        End Try
    End Function

    'Public Shared Function VerwijderUitvoeringenByAlbumEnLiedID(ByVal sAlbumId As String, ByVal sLiedID As String)
    '    Dim oCon As OleDbConnection
    '    Try
    '        Dim sValue As String
    '        sValue = System.Configuration.ConfigurationSettings.AppSettings.Item("ConnectionString")
    '        Dim oCon3 As System.Data.OleDb.OleDbConnection
    '        oCon3 = New OleDbConnection(sValue)
    '        oCon3.Open()

    '        Dim oCommand3 As OleDbCommand
    '        oCommand3 = oCon3.CreateCommand()
    '        Dim sInsert As String
    '        '2. Command object aanmaken
    '        'DELETE FROM tabelNaam WHERE ...
    '        'DELETE FROM tblRekening WHERE Nummer='000-0000020-20'

    '        '        sInsert = "DELETE FROM tblAlbum WHERE AlbumId=" & sAlbumId & ",LiedID=" & sLiedID





    '        'Dim oPar As New OleDbParameter("@AlbumId", sAlbumId)
    '        oCommand3.CommandText = sInsert
    '        'oCommand3.Parameters.Add(oPar)

    '        '3. Uitvoeren op de DB
    '        oCommand3.ExecuteNonQuery()
    '        oCon3.Close()
    '    Catch ex As Exception
    '        Debug.WriteLine(ex.Message)
    '        Throw
    '    Finally
    '        '4. Connectie vrijgeven
    '        If Not oCon Is Nothing Then
    '            oCon.Close()
    '            oCon.Dispose()
    '        End If
    '    End Try
    'End Function

#End Region

End Class
