Imports System.Data.OleDb
Imports System.Configuration.ConfigurationSettings
Imports Mediatheek.data

Public Class clsOntleningDA

#Region "Get Ontleningen"


    Public Shared Function GetOntleningen() As ArrayList
        'functie om alle ontleningen te krijgen
        Try
            Dim sSQL As String = "SELECT * FROM tblAlbum_Ontlener"


            Dim oDataTable As DataTable = clsDatabase.GetDT(sSQL)

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

                Dim sOntleningId, sAlbumId, sOntlenerId, sInBezit As String
                Dim sDatumIn, sDatumUit As Date
                sOntleningId = Convert.ToString(oRow.Item("OntleningID"))
                sAlbumId = Convert.ToString(oRow.Item("AlbumID"))
                sOntlenerId = Convert.ToString(oRow.Item("OntlenerID"))
                sDatumIn = Convert.ToString(oRow.Item("DatumIn"))
                sDatumUit = Convert.ToString(oRow.Item("DatumUit"))
                sInBezit = Convert.ToString(oRow.Item("InBezit"))

                Dim oOntlening As New clsOntlening(sOntleningId, sAlbumId, sOntlenerId, sDatumUit, sDatumIn, sInBezit)
                arlOntleningen.Add(oOntlening)
            Next


            Return arlOntleningen

        Catch ex As Exception
            Debug.WriteLine(ex.Message)

            Throw
        End Try
    End Function

    Public Shared Function GetOntleningById(ByVal sIdGevonden As String) As ArrayList
        'Hier maken we een functie getontleners ontlening op te roepen via ID

        Try
            Dim sSQL As String = "SELECT * FROM tblAlbum_Ontlener WHERE OntleningID=" + sIdGevonden

            Dim oDataTable As DataTable = clsDatabase.GetDT(sSQL)

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

                Dim sOntleningId, sAlbumId, sOntlenerId, sInBezit As String
                Dim sDatumIn, sDatumUit As Date
                sOntleningId = Convert.ToString(oRow.Item("OntleningID"))
                sAlbumId = Convert.ToString(oRow.Item("AlbumID"))
                sOntlenerId = Convert.ToString(oRow.Item("OntlenerID"))
                sDatumIn = Convert.ToString(oRow.Item("DatumIn"))
                sDatumUit = Convert.ToString(oRow.Item("DatumUit"))
                sInBezit = Convert.ToString(oRow.Item("InBezit"))

                Dim oOntlening As New clsOntlening(sOntleningId, sAlbumId, sOntlenerId, sDatumUit, sDatumIn, sInBezit)
                arlOntleningen.Add(oOntlening)
            Next

            Return arlOntleningen

        Catch ex As Exception
            Debug.WriteLine(ex.Message)

            Throw
        End Try
    End Function
    Public Shared Function GetOntleningByBeschikbaar() As ArrayList
        'hier maken we een functie om te zien welke ontleningenbeschikbare albums hebben

        Try
            Dim sSQL As String = "SELECT * FROM tblAlbum_Ontlener WHERE InBezit='Ja'"


            Dim oDataTable As DataTable = clsDatabase.GetDT(sSQL)

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

                Dim sOntleningId, sAlbumId, sOntlenerId, sInBezit As String
                Dim sDatumIn, sDatumUit As Date
                sOntleningId = Convert.ToString(oRow.Item("OntleningID"))
                sAlbumId = Convert.ToString(oRow.Item("AlbumID"))
                sOntlenerId = Convert.ToString(oRow.Item("OntlenerID"))
                sDatumIn = Convert.ToString(oRow.Item("DatumIn"))
                sDatumUit = Convert.ToString(oRow.Item("DatumUit"))
                sInBezit = Convert.ToString(oRow.Item("InBezit"))

                Dim oOntlening As New clsOntlening(sOntleningId, sAlbumId, sOntlenerId, sDatumUit, sDatumIn, sInBezit)
                arlOntleningen.Add(oOntlening)
            Next

            Return arlOntleningen

        Catch ex As Exception
            Debug.WriteLine(ex.Message)

            Throw
        End Try
    End Function

    Public Shared Function GetOntleningByAlbumId(ByVal sAlbumID As String) As ArrayList
        'Hier maken we een functie getontleners om alle ontleners op te roepen en te steken in een arraylist 
        'Hier worden alle records ingelezen van de tabel ontlener

        Try
            Dim sSQL As String = "SELECT * FROM tblAlbum_Ontlener WHERE AlbumID=" + sAlbumID

            Dim oDataTable As DataTable = clsDatabase.GetDT(sSQL)

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

                Dim sOntleningId, sOntlenerId, sInBezit As String
                Dim sDatumIn, sDatumUit As Date

                sOntleningId = Convert.ToString(oRow.Item("OntleningID"))
                sAlbumID = Convert.ToString(oRow.Item("AlbumID"))
                sOntlenerId = Convert.ToString(oRow.Item("OntlenerID"))
                sDatumIn = Convert.ToString(oRow.Item("DatumIn"))
                sDatumUit = Convert.ToString(oRow.Item("DatumUit"))
                sInBezit = Convert.ToString(oRow.Item("InBezit"))

                Dim oOntlening As New clsOntlening(sOntleningId, sAlbumID, sOntlenerId, sDatumUit, sDatumIn, sInBezit)
                arlOntleningen.Add(oOntlening)
            Next

            Return arlOntleningen

        Catch ex As Exception
            Debug.WriteLine(ex.Message)

            Throw
        End Try
    End Function
    Public Shared Function GetOntleningByAlbumId2(ByVal sAlbumId As String) As clsOntlening
        Dim oOntlening As clsOntlening
        For Each oOntlening In GetOntleningen()

            If oOntlening.AlbumId = sAlbumId Then
                Return oOntlening
            End If

        Next
    End Function

    Public Shared Function GetOntleningByOntlenerId(ByVal sOntlenerID As String) As ArrayList
        'Hier maken we een functie getontleners ontlening op te roepen via ontlener ID

        Try
            Dim sSQL As String = "SELECT * FROM tblAlbum_Ontlener WHERE OntlenerID=" + sOntlenerID

            Dim oDataTable As DataTable = clsDatabase.GetDT(sSQL)

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

                Dim sOntleningId, sAlbumId, sInBezit As String
                Dim sDatumIn, sDatumUit As Date
                sOntleningId = Convert.ToString(oRow.Item("OntleningID"))
                sAlbumId = Convert.ToString(oRow.Item("AlbumID"))

                sDatumIn = Convert.ToString(oRow.Item("DatumIn"))
                sDatumUit = Convert.ToString(oRow.Item("DatumUit"))
                sInBezit = Convert.ToString(oRow.Item("InBezit"))

                Dim oOntlening As New clsOntlening(sOntleningId, sAlbumId, sOntlenerId, sDatumUit, sDatumIn, sInBezit)
                arlOntleningen.Add(oOntlening)
            Next

            Return arlOntleningen

        Catch ex As Exception
            Debug.WriteLine(ex.Message)

            Throw
        End Try
    End Function
#End Region

#Region "Voeg Ontleningen Toe"
   

    Public Shared Sub VoegOntleningToe(ByVal oOntlening As clsOntlening)
        'hier maken we een functie om ontleningen toe te voegen
        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()

        oOntlening.OntleningId = getVolgendOntleningId()
        Dim sInsert As String
        sInsert = "INSERT INTO tblAlbum_Ontlener(OntleningID,AlbumID,OntlenerID,DatumIn,DatumUit,InBezit)VALUES(?,?,?,?,?,?)"

        Dim oPar1 As New OleDbParameter("@OntleningID", oOntlening.OntleningId)
        Dim oPar2 As New OleDbParameter("@AlbumID", oOntlening.AlbumId)
        Dim oPar3 As New OleDbParameter("@OntlenerID", oOntlening.OntlenerId)
        Dim oPar4 As New OleDbParameter("@DatumIn", oOntlening.DatumIn)
        Dim oPar5 As New OleDbParameter("@DatumUit", oOntlening.DatumUit)
        Dim oPar6 As New OleDbParameter("@InBezit", oOntlening.InBezit)

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

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

    Private Shared Function getVolgendOntleningId() As Integer
        'opnieuw een functie om het hoogste ontleningid te vinden om een nieuwe ontlening toe te voegen
        Try
            Dim sSQL As String = "SELECT MAX(OntleningID) FROM tblAlbum_Ontlener"
            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 "Wijzig Ontlening"

    Public Shared Function WijzigOntlening(ByVal oOntlening As clsOntlening)
        'een functie om een ontlening te wijzigen
        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 tblAlbum_Ontlener SET AlbumID=?, OntlenerID=?, DatumIn=?, DatumUit=?, InBezit=? WHERE OntleningID=?"

            Dim oPar1 As New OleDbParameter("@AlbumID", oOntlening.AlbumId)
            Dim oPar2 As New OleDbParameter("@OntlenerID", oOntlening.OntlenerId)
            Dim oPar3 As New OleDbParameter("@DatumIn", oOntlening.DatumIn)
            Dim oPar4 As New OleDbParameter("@DatumUit", oOntlening.DatumUit)
            Dim oPar5 As New OleDbParameter("@InBezit", oOntlening.InBezit)
            Dim oPar6 As New OleDbParameter("@OntleningID", oOntlening.OntleningId)

            oCommand2.CommandText = sInsert
            oCommand2.Parameters.Add(oPar1)
            oCommand2.Parameters.Add(oPar2)
            oCommand2.Parameters.Add(oPar3)
            oCommand2.Parameters.Add(oPar4)
            oCommand2.Parameters.Add(oPar5)
            oCommand2.Parameters.Add(oPar6)
            oCommand2.ExecuteNonQuery()
            oCon2.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

#Region "Verwijder Ontlening"
    Public Shared Function VerwijderOntlening(ByVal iOntleningId As Integer)
        'functie om ontlening te verwijderen
        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 tblAlbum_Ontlener WHERE OntleningID=?"

            Dim oPar As New OleDbParameter("@OntleningID", iOntleningId)
            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
