NurseryManager-Project/ApplicationCrèche 1/DataManager/SQLManager.vb

Imports System.Data.SqlClient
Imports System.IO

Public Class SQLManager
    Shared DBConnexion As SqlConnection

    Shared Sub New()
        Dim fileName As String = "DBString.ini"
        Dim exePath As String = Application.StartupPath()
        Dim filePath As String = exePath & "\" & fileName
        If File.Exists(filePath) Then
            Dim txtReader As StreamReader
            Dim StrContents As String
            Try
                txtReader = New StreamReader(filePath)
                StrContents = txtReader.ReadLine()
            Catch ex As Exception
                MsgBox("The application was unable to load the database connexion string from the file : " & vbNewLine & filePath & vbNewLine & "Please check if the database is launched and loaded, and the initialisation file containt a operationnal connexion string." & vbNewLine & "The program will close.", MsgBoxStyle.OkOnly & MsgBoxStyle.Critical, "Impossible to connect to the database")
                End
            End Try
            Try
                DBConnexion = New SqlConnection(StrContents)
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.OkOnly & MsgBoxStyle.Critical)
            End Try
        Else
            Dim txtReader As StreamWriter
            Try
                File.Create(filePath)
                txtReader = New StreamWriter(filePath)
                txtReader.WriteLine("Data Source=TIMONIUM\SQLEXPRESS8;Initial Catalog=Creche;Integrated Security=True;Pooling=False")
            Catch ex As Exception
                MsgBox(ex.Message & vbNewLine & "The program will close.", MsgBoxStyle.OkOnly & MsgBoxStyle.Critical, "Impossible to start")
                End
            End Try
            MsgBox("The application was unable to find the connexion string file at " & vbNewLine & filePath & vbNewLine & "Please check if the fill exists and contains a valid database connexion string.", MsgBoxStyle.OkOnly & MsgBoxStyle.Critical, "File not found")
            End
        End If
        DBConnexion.Open()
    End Sub


    ' ----- DB Methods for Children -----'
    ''' <summary>
    ''' Get a all information about a Child whose his ID is passed as a parameter
    ''' </summary>
    ''' <param name="id">The id of the child to get from the database</param>
    ''' <returns>a Child as complete as possible</returns>
    ''' <remarks></remarks>
    Public Function getChildByID(id As Integer) As Child
        Dim aChild As Child = Nothing
        Dim request As SqlCommand = New SqlCommand()
        Dim results As SqlDataReader
        Dim FK_Parent1id As UInteger
        Dim FK_Parent2id As UInteger

        request.Connection = DBConnexion
        request.CommandText = "SELECT * FROM Child WHERE idChild = @id"
        request.Parameters.Add("@id", SqlDbType.Int).Value = id
        results = request.ExecuteReader()
        'While (results.Read())
        '    Console.WriteLine(results.GetInt32(2))
        'End While
        If (results.HasRows) Then
            results.Read()
            If results.IsDBNull(0) Then
                FK_Parent1id = 0
            Else
                FK_Parent1id = results.GetInt32(0)
            End If
            If results.IsDBNull(1) Then
                FK_Parent2id = 0
            Else
                FK_Parent2id = results.GetInt32(1)
            End If

            aChild = New Child(results.GetInt32(2))
            aChild.firstname = results.GetString(3)
            aChild.lastname = results.GetString(4)
            aChild.gender = results.GetBoolean(5)
            aChild.bornDate = results.GetDateTime(6)
            aChild.address = results.GetString(7)
            results.Close()
        Else
            Throw New IndexOutOfRangeException("The specified index does not exist on the database")
        End If

        If FK_Parent1id <> 0 Then
            aChild.parent1 = Me.getParentByID(FK_Parent1id)
        End If
        If FK_Parent2id <> 0 Then
            aChild.parent2 = Me.getParentByID(FK_Parent2id)
        End If
        aChild.meet = Me.getMeetByChildId(id)

        'request.CommandText = "SELECT * FROM Parent WHERE idParent = @id"
        'request.Parameters("@id").Value = FK_Parent1id
        'results = request.ExecuteReader()
        'If (results.HasRows) Then
        '    results.Read()
        '    Dim parent1 As Parent = New Parent(results.GetInt32(0))
        '    parent1.firstname = results.GetString(1)
        '    parent1.lastname = results.GetString(2)
        '    parent1.gender = results.GetBoolean(3)
        '    parent1.phone = results.GetString(4)
        '    parent1.address = results.GetString(5)
        '    aChild.parent1 = parent1
        '    results.Close()
        '    Console.WriteLine(parent1.toString())
        'Else
        '    aChild.parent1 = Nothing
        'End If

        'request.CommandText = "SELECT * FROM Parent WHERE idParent = @id"
        'request.Parameters("@id").Value = FK_Parent2id
        'results = request.ExecuteReader()
        'If (results.HasRows) Then
        '    results.Read()
        '    Dim parent2 As Parent = New Parent(results.GetInt32(0))
        '    parent2.firstname = results.GetString(1)
        '    parent2.lastname = results.GetString(2)
        '    parent2.gender = results.GetBoolean(3)
        '    parent2.phone = results.GetString(4)
        '    parent2.address = results.GetString(5)
        '    aChild.parent2 = parent2
        '    results.Close()
        '    Console.WriteLine(parent2.toString())
        'Else
        '    aChild.parent2 = Nothing
        'End If

        If Not results.IsClosed Then results.Close()
        Return aChild
    End Function

    ''' <summary>
    ''' Send requests to the database to compose a list of all the children
    ''' </summary>
    ''' <returns>A list of all the children</returns>
    ''' <remarks></remarks>
    Public Function getChildren() As List(Of Child)
        Dim request As SqlCommand = New SqlCommand()
        request.Connection = DBConnexion
        request.CommandText = "SELECT idChild FROM Child"

        Dim results As SqlDataReader
        results = request.ExecuteReader()
        Dim allID As List(Of Integer) = New List(Of Integer)
        While (results.Read())
            allID.Add(results.GetInt32(0))
        End While
        results.Close()

        Dim AllChildren As List(Of Child) = New List(Of Child)
        For Each id As Integer In allID
            AllChildren.Add(getChildByID(id))
        Next

        Return AllChildren
    End Function

    ''' <summary>
    ''' Add a Child to the database with all needed data
    ''' </summary>
    ''' <param name="aChild">The child to add to the database</param>
    ''' <remarks>Currently, it only add a new children, no parents, no meet.</remarks>
    Public Function addChild(aChild As Child) As Integer
        Dim request As SqlCommand = New SqlCommand()
        request.Connection = DBConnexion
        request.CommandText = "INSERT INTO Child (FK_idParent1, FK_idParent2, ChildFirstName, ChildLastName, ChildGender, ChildBornDate, ChildCareAddress) VALUES (@idp1, @idp2, @chfn, @chln, @chgd, @chbd, @chds)"

        If Not aChild.parent1 Is Nothing Then
            request.Parameters.Add("@idp1", SqlDbType.Int).Value = aChild.parent1.id
        Else
            request.Parameters.Add("@idp1", SqlDbType.Int).Value = DBNull.Value
        End If

        If Not aChild.parent2 Is Nothing Then
            request.Parameters.Add("@idp2", SqlDbType.Int).Value = aChild.parent2.id
        Else
            request.Parameters.Add("@idp2", SqlDbType.Int).Value = DBNull.Value
        End If

        request.Parameters.Add("@chfn", SqlDbType.NVarChar).Value = aChild.firstname
        request.Parameters.Add("@chln", SqlDbType.NVarChar).Value = aChild.lastname
        request.Parameters.Add("@chgd", SqlDbType.Bit).Value = aChild.gender
        request.Parameters.Add("@chbd", SqlDbType.Date).Value = aChild.bornDate
        request.Parameters.Add("@chds", SqlDbType.NVarChar).Value = aChild.address
        request.ExecuteReader().Close()

        'If Not aChild.meet Is Nothing And aChild.meet.id = 0 Then
        '    addMeet(aChild.meet, aChild.id)
        'End If

        request.CommandText = "SELECT idChild FROM Child where ChildFirstName = @chfn AND ChildLastName = @chln AND ChildGender = @chgd AND ChildBornDate = @chbd AND ChildCareAddress = @chds"
        Dim results As SqlDataReader
        results = request.ExecuteReader()
        results.Read()
        Dim idChild As Integer = results.GetInt32(0)
        results.Close()
        Return idChild
    End Function

    ''' <summary>
    ''' Edit the specified Child in the database
    ''' </summary>
    ''' <param name="aChild">The child to edit (The most important is it's ID)</param>
    ''' <remarks></remarks>
    Public Sub editChild(aChild As Child)
        Dim request As SqlCommand = New SqlCommand()
        request.Connection = DBConnexion
        request.CommandText = "UPDATE Child SET FK_idParent1 = @idp1, FK_idParent2 = @idp2, ChildFirstName = @chfn, ChildLastName = @chln, ChildGender = @chgd, ChildBornDate = @chbd, ChildCareAddress = @chds WHERE idChild = @chid"
        request.Parameters.Add("@chid", SqlDbType.Int).Value = aChild.id

        If Not aChild.parent1 Is Nothing Then
            request.Parameters.Add("@idp1", SqlDbType.Int).Value = aChild.parent1.id
        Else
            request.Parameters.Add("@idp1", SqlDbType.Int).Value = DBNull.Value
        End If

        If Not aChild.parent2 Is Nothing Then
            request.Parameters.Add("@idp2", SqlDbType.Int).Value = aChild.parent2.id
        Else
            request.Parameters.Add("@idp2", SqlDbType.Int).Value = DBNull.Value
        End If

        request.Parameters.Add("@chfn", SqlDbType.NVarChar).Value = aChild.firstname
        request.Parameters.Add("@chln", SqlDbType.NVarChar).Value = aChild.lastname
        request.Parameters.Add("@chgd", SqlDbType.Bit).Value = aChild.gender
        request.Parameters.Add("@chbd", SqlDbType.Date).Value = aChild.bornDate
        request.Parameters.Add("@chds", SqlDbType.NVarChar).Value = aChild.address
        request.ExecuteReader().Close()
    End Sub

    ''' <summary>
    ''' Delete the Child in the Database based on his id.
    ''' </summary>
    ''' <param name="id">The id of the Child to delete</param>
    ''' <remarks>The Child is deleted with the corresponding meet.</remarks>
    Public Sub deleteChildByID(id As Integer)
        Dim request As SqlCommand = New SqlCommand()
        request.Connection = DBConnexion

        ' Delete the associated Meet of the child.
        request.CommandText = "SELECT idMeet FROM Meet WHERE FK_idChild = @chidmeet"
        request.Parameters.Add("@chidmeet", SqlDbType.Int).Value = id
        Dim result As SqlDataReader = request.ExecuteReader()

        Dim idmeet As Integer = Nothing
        If result.HasRows() Then
            result.Read()
            idmeet = result.GetInt32(0)
            result.Close()
        End If

        ' if there is no meet asociated, we can delete child safely from de db.
        ' else, we delete meeet first.
        If Not idmeet = Nothing Then
            deleteMeet(idmeet)
        End If
        If Not result.IsClosed Then result.Close()

        ' Then delete the child from the database.
        request = New SqlCommand()
        request.Connection = DBConnexion
        request.CommandText = "DELETE FROM Child WHERE idChild = @chid"
        request.Parameters.Add("@chid", SqlDbType.Int).Value = id
        request.ExecuteReader.Close()
    End Sub

    Function getChildByMeetID(id As Integer) As Child
        Dim request As SqlCommand = New SqlCommand()
        request.Connection = DBConnexion
        request.CommandText = "SELECT FK_idChild FROM Meet WHERE idMeet = @id"
        request.Parameters.Add("@id", SqlDbType.Int).Value = id
        Dim results As SqlDataReader
        results = request.ExecuteReader()

        Dim aChild As Child = Nothing
        If results.HasRows Then
            results.Read()
            Dim idChild As UInteger = results.GetValue(0)
            results.Close()
            aChild = getChildByID(idChild)
        End If
        If Not results.IsClosed Then results.Close()
        Return aChild
    End Function

    ' ----- DB Methods for Parents -----'
    ''' <summary>
    ''' Get all information about a PArent whose his ID is passed as a parameter
    ''' </summary>
    ''' <param name="id">The id of the Parent to get from the database</param>
    ''' <returns>a Parent</returns>
    ''' <remarks></remarks>
    Public Function getParentByID(id As Integer) As Parent
        Dim request As SqlCommand = New SqlCommand()
        request.Connection = DBConnexion
        request.CommandText = "SELECT * FROM Parent WHERE idParent = @id"
        request.Parameters.Add("@id", SqlDbType.Int).Value = id
        Dim results As SqlDataReader
        results = request.ExecuteReader()

        Dim aParent As Parent = Nothing
        If results.HasRows Then
            results.Read()
            aParent = New Parent(results.GetInt32(0))
            aParent.firstname = results.GetString(1)
            aParent.lastname = results.GetString(2)
            aParent.phone = results.GetString(4)
            aParent.gender = results.GetBoolean(3)
            aParent.address = results.GetString(5)
            results.Close()
        End If
        If Not results.IsClosed Then results.Close()
        Return aParent
    End Function

    ''' <summary>
    ''' Send requests to the database to compose a list of all the parents
    ''' </summary>
    ''' <returns>The list containing all parents</returns>
    ''' <remarks></remarks>
    Public Function getParents() As List(Of Parent)
        Dim request As SqlCommand = New SqlCommand()
        request.Connection = DBConnexion
        request.CommandText = "SELECT idParent FROM Parent"

        Dim results As SqlDataReader
        results = request.ExecuteReader()
        Dim allID As List(Of Integer) = New List(Of Integer)
        While (results.Read())
            allID.Add(results.GetInt32(0))
        End While
        results.Close()

        Dim AllParent As List(Of Parent) = New List(Of Parent)
        For Each id As Integer In allID
            AllParent.Add(getParentByID(id))
        Next

        Return AllParent
    End Function

    ''' <summary>
    ''' Get a list of children for the parent where the id is passed as a parameter
    ''' </summary>
    ''' <param name="id">the id of the parent</param>
    ''' <returns>a list of childrens of this parent</returns>
    ''' <remarks></remarks>
    Public Function getChildenOf(id As Integer) As List(Of Child)
        Dim request As SqlCommand = New SqlCommand()
        request.Connection = DBConnexion
        request.CommandText = "SELECT idChild FROM Child WHERE FK_idParent1 = @id OR FK_idParent2 = @id"
        request.Parameters.Add("@id", SqlDbType.Int).Value = id

        Dim results As SqlDataReader
        results = request.ExecuteReader()
        Dim allId As List(Of Integer) = New List(Of Integer)
        While results.Read
            allId.Add(results.GetInt32(0))
        End While
        results.Close()

        Dim theChildren As List(Of Child) = New List(Of Child)
        For Each Childid As Integer In allId
            Dim aChild As Child = getChildByID(Childid)
            theChildren.Add(aChild)
        Next

        Return theChildren
    End Function

    ''' <summary>
    ''' Add a Parent to the database with all needed data
    ''' </summary>
    ''' <param name="parent">the parent to add into the database</param>
    ''' <remarks></remarks>
    Public Function addParent(parent As Parent) As Integer
        Dim request As SqlCommand = New SqlCommand()
        request.Connection = DBConnexion
        request.CommandText = "INSERT INTO Parent (ParentFirstName, ParentLastName, ParentGender, ParentPhone, ParentAddress) VALUES (@pfn, @pln, @pgd, @pph, @pds)"

        request.Parameters.Add("@pfn", SqlDbType.NVarChar).Value = parent.firstname
        request.Parameters.Add("@pln", SqlDbType.NVarChar).Value = parent.lastname
        request.Parameters.Add("@pgd", SqlDbType.Bit).Value = parent.gender
        request.Parameters.Add("@pph", SqlDbType.NVarChar).Value = parent.phone
        request.Parameters.Add("@pds", SqlDbType.NVarChar).Value = parent.address
        request.ExecuteReader().Close()

        request.CommandText = "SELECT idParent FROM Parent WHERE ParentFirstName = @pfn AND ParentLastName = @pln AND ParentGender = @pgd AND ParentPhone = @pph AND ParentAddress = @pds"
        Dim results As SqlDataReader
        results = request.ExecuteReader()
        results.Read()
        Dim idParent As Integer = results.GetInt32(0)
        results.Close()
        Return idParent
    End Function

    ''' <summary>
    ''' Edit the specified Parent in the database
    ''' </summary>
    ''' <param name="parent">The parent to edit in the database (The most important is ot's ID)</param>
    ''' <remarks></remarks>
    Public Sub editParent(parent As Parent)
        Dim request As SqlCommand = New SqlCommand()
        request.Connection = DBConnexion
        request.CommandText = "UPDATE Parent SET ParentFirstName = @pfn, ParentLastName = @pln, ParentGender = @pgd, ParentPhone = @pph, ParentAddress = @pds WHERE idParent = @pid"

        request.Parameters.Add("@pid", SqlDbType.Int).Value = parent.id
        request.Parameters.Add("@pfn", SqlDbType.NVarChar).Value = parent.firstname
        request.Parameters.Add("@pln", SqlDbType.NVarChar).Value = parent.lastname
        request.Parameters.Add("@pgd", SqlDbType.Bit).Value = parent.gender
        request.Parameters.Add("@pph", SqlDbType.NVarChar).Value = parent.phone
        request.Parameters.Add("@pds", SqlDbType.NVarChar).Value = parent.address
        request.ExecuteReader().Close()
    End Sub

    ''' <summary>
    ''' Delete the Parent in the Database based on the id.
    ''' </summary>
    ''' <param name="id">The id of the Parent to delete</param>
    ''' <remarks>Delete only the Parent without nothing more</remarks>
    Public Sub deleteParentByID(id As Integer)
        Dim request As SqlCommand = New SqlCommand()
        request.Connection = DBConnexion
        request.CommandText = "SELECT idChild FROM Child WHERE FK_idParent1 = @pid OR FK_idParent2 = @pid "
        request.Parameters.Add("@pid", SqlDbType.Int).Value = id
        Dim result As SqlDataReader = request.ExecuteReader

        If result.HasRows Then
            Dim allChildID As List(Of Integer) = New List(Of Integer)
            While result.Read
                allChildID.Add(result.GetInt32(0))
            End While
            result.Close()

            Dim allChild As List(Of Child) = New List(Of Child)
            For Each idChild As Integer In allChildID
                allChild.Add(getChildByID(idChild))
            Next

            For Each Child As Child In allChild

                If Not Child.parent1 Is Nothing AndAlso Child.parent1.id = id Then
                    request = New SqlCommand()
                    request.Connection = DBConnexion
                    request.CommandText = "UPDATE Child SET FK_idParent1 = @pfk WHERE idChild = @cid"
                    request.Parameters.Add("@pfk", SqlDbType.Int).Value = DBNull.Value
                    request.Parameters.Add("@cid", SqlDbType.Int).Value = Child.id
                    request.ExecuteReader.Close()
                End If
                If Not Child.parent2 Is Nothing AndAlso Child.parent2.id = id Then
                    request = New SqlCommand()
                    request.Connection = DBConnexion
                    request.CommandText = "UPDATE Child SET FK_idParent2 = @pfk WHERE idChild = @cid"
                    request.Parameters.Add("@pfk", SqlDbType.Int).Value = DBNull.Value
                    request.Parameters.Add("@cid", SqlDbType.Int).Value = Child.id
                    request.ExecuteReader.Close()
                End If
            Next
        End If

        request = New SqlCommand()
        request.Connection = DBConnexion
        request.CommandText = "DELETE FROM Parent WHERE idParent = @pid"
        request.Parameters.Add("@pid", SqlDbType.Int).Value = id
        request.ExecuteReader.Close()
    End Sub

    ' ----- DB Methods for Babysitter -----'

    ''' <summary>
    ''' Get all information about a Babysitter whose his ID is passed as a parameter
    ''' </summary>
    ''' <param name="id">The id of the child to get from the database</param>
    ''' <returns>A Babysitter as complete as possible</returns>
    ''' <remarks>If the babysitter have Meets the function also these meets into the Babysitters objet, or Nohting insead.</remarks>
    Public Function getBabysitterByID(id As Integer) As BabySitter
        Dim request As SqlCommand = New SqlCommand()
        request.Connection = DBConnexion
        request.CommandText = "SELECT * FROM Babysitter WHERE idBS = @id"
        request.Parameters.Add("@id", SqlDbType.Int).Value = id
        Dim results As SqlDataReader
        results = request.ExecuteReader()

        Dim aSB As BabySitter
        If results.HasRows Then
            results.Read()
            aSB = New BabySitter(results.GetInt32(3))
            aSB.firstname = results.GetString(4)
            aSB.lastname = results.GetString(5)
            aSB.gender = results.GetBoolean(6)
            aSB.address = results.GetString(7)
            aSB.phone1 = results.GetString(8)

            Dim nbMeet1 As Integer
            Dim nbMeet2 As Integer
            Dim nbMeet3 As Integer

            If Not results.IsDBNull(0) Then
                nbMeet1 = results.GetInt32(0)
            Else
                nbMeet1 = Nothing
            End If
            If Not results.IsDBNull(1) Then
                nbMeet2 = results.GetInt32(1)
            Else
                nbMeet2 = Nothing
            End If
            If Not results.IsDBNull(2) Then
                nbMeet3 = results.GetInt32(2)
            Else
                nbMeet3 = Nothing
            End If

            If Not results.IsDBNull(9) Then
                aSB.phone2 = results.GetString(9)
            Else
                aSB.phone2 = Nothing
            End If
            results.Close()

            If Not nbMeet1 = Nothing Then
                aSB.meet1 = Me.getMeetByID(nbMeet1)
            End If
            If Not nbMeet2 = Nothing Then
                aSB.meet2 = Me.getMeetByID(nbMeet2)
            End If
            If Not nbMeet3 = Nothing Then
                aSB.meet3 = Me.getMeetByID(nbMeet3)
            End If

        Else
            Throw New IndexOutOfRangeException("The specified index does not exist on the database")
        End If

        Return aSB
    End Function

    ''' <summary>
    ''' Send requests to the database to compose a liste of all the babysitters
    ''' </summary>
    ''' <returns>A List(of BabySitters)</returns>
    ''' <remarks>Use getBabysitter(id)</remarks>
    Public Function getBabysitters() As List(Of BabySitter)
        Dim request As SqlCommand = New SqlCommand()
        request.Connection = DBConnexion
        request.CommandText = "SELECT idBS FROM Babysitter"

        Dim results As SqlDataReader
        results = request.ExecuteReader()
        Dim allID As List(Of Integer) = New List(Of Integer)
        While (results.Read())
            allID.Add(results.GetInt32(0))
        End While
        results.Close()

        Dim AllBS As List(Of BabySitter) = New List(Of BabySitter)
        For Each id As Integer In allID
            Try
                AllBS.Add(getBabysitterByID(id))
            Catch ex As SqlException
                Console.WriteLine("SQLManager")
                Console.WriteLine(ex.Message)
            End Try
        Next
        Return AllBS
    End Function

    ''' <summary>
    ''' Get a babysitter based on the Meet ID provided as a parameter
    ''' </summary>
    ''' <param name="id">The ID of the meet</param>
    ''' <returns>A babysitter if a meet is founded in the Database, or Nothing else.</returns>
    ''' <remarks>If a meet has no babysitter in the database, the meet is immediately deleted.</remarks>
    Public Function getBabysitterByMeetID(id As Integer) As BabySitter
        Dim request As SqlCommand = New SqlCommand()
        request.Connection = DBConnexion
        request.CommandText = "SELECT * FROM Babysitter WHERE FK_idMeet1 = @id OR FK_idMeet2 = @id OR FK_idMeet3 = @id"
        request.Parameters.Add("@id", SqlDbType.Int).Value = id
        Dim results As SqlDataReader
        results = request.ExecuteReader()
        Dim aSB As BabySitter = Nothing
        If results.HasRows Then
            results.Read()

            aSB = New BabySitter(results.GetInt32(3))
            aSB.firstname = results.GetString(4)
            aSB.lastname = results.GetString(5)
            aSB.gender = results.GetBoolean(6)
            aSB.address = results.GetString(7)
            aSB.phone1 = results.GetString(8)
            If Not results.IsDBNull(9) Then
                aSB.phone2 = results.GetString(9)
            Else
                aSB.phone2 = Nothing
            End If
            results.Close()
        End If
        Return aSB
    End Function

    ''' <summary>
    ''' Get all availables babysitters from the databases.
    ''' </summary>
    ''' <returns>z List(of Babysitter)</returns>
    ''' <remarks>Babysitters that have less than 3 meets</remarks>
    Public Function getAvailableBabysitters() As List(Of BabySitter)
        Dim request As SqlCommand = New SqlCommand()
        request.Connection = DBConnexion
        request.CommandText = "SELECT idBS FROM Babysitter WHERE FK_idMeet1 IS NULL OR FK_idMeet2 IS NULL OR FK_idMeet3 IS NULL"

        Dim results As SqlDataReader
        results = request.ExecuteReader()
        Dim allID As List(Of Integer) = New List(Of Integer)
        While (results.Read())
            allID.Add(results.GetInt32(0))
        End While
        results.Close()

        Dim AllBS As List(Of BabySitter) = New List(Of BabySitter)
        For Each id As Integer In allID
            Try
                AllBS.Add(getBabysitterByID(id))
            Catch ex As SqlException
                Console.WriteLine("SQLManager")
                Console.WriteLine(ex.Message)
            End Try
        Next
        Return AllBS
    End Function

    ''' <summary>
    ''' Add a Babysitter to the database.
    ''' </summary>
    ''' <param name="aBS"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function addBabysitter(aBS As BabySitter) As Integer
        Dim request As SqlCommand = New SqlCommand()
        request.Connection = DBConnexion
        request.CommandText = "INSERT INTO Babysitter (BSFirstName, BSLastName, BSGender, BSAddress, BSPhone1, BSPhone2) VALUES (@bsfn, @bsln, @bsgd, @bsds, @bsp1, @bsp2)"

        request.Parameters.Add("@bsfn", SqlDbType.NVarChar).Value = aBS.firstname
        request.Parameters.Add("@bsln", SqlDbType.NVarChar).Value = aBS.lastname
        request.Parameters.Add("@bsgd", SqlDbType.Bit).Value = aBS.gender
        request.Parameters.Add("@bsp1", SqlDbType.NVarChar).Value = aBS.phone1
        request.Parameters.Add("@bsds", SqlDbType.NVarChar).Value = aBS.address
        If Not aBS.phone2 Is Nothing Then
            request.Parameters.Add("@bsp2", SqlDbType.NVarChar).Value = aBS.phone2
        Else
            request.Parameters.Add("@bsp2", SqlDbType.NVarChar).Value = DBNull.Value
        End If
        request.ExecuteReader().Close()

        request.CommandText = "SELECT idBS FROM babysitter WHERE BSFirstName = @bsfn AND BSLastName = @bsln AND BSGender = @bsgd AND BSAddress = @bsds"
        Dim results As SqlDataReader
        results = request.ExecuteReader()
        results.Read()
        Dim idBS As Integer = results.GetInt32(0)
        results.Close()
        Return idBS
    End Function

    ''' <summary>
    ''' Edit a BabySitter with the givent babysitter
    ''' </summary>
    ''' <param name="aBS">The babysitter with new information. The ID of the babysitter is mandatory.</param>
    ''' <remarks>The meet is not managed here. see updateBabysitterMeets</remarks>
    Public Sub editBabysitter(aBS As BabySitter)
        Dim request As SqlCommand = New SqlCommand()
        request.Connection = DBConnexion
        request.CommandText = "UPDATE Babysitter SET BSFirstName = @bsfn, BSLastName = @bsln, BSGender = @bsg, BSAddress = @bsad, BSPhone1 = @bsph1, BSPhone2 = @bsph2 WHERE idBS = @bsid"

        'If Not aBS.meet1 Is Nothing Then
        '    request.Parameters.Add("@bsfk1", SqlDbType.Int).Value = aBS.meet1.id
        'End If
        'If Not aBS.meet2 Is Nothing Then
        '    request.Parameters.Add("@fkbs2", SqlDbType.Int).Value = aBS.meet2.id
        'End If
        'If Not aBS.meet3 Is Nothing Then
        '    request.Parameters.Add("@fkbs3", SqlDbType.Int).Value = aBS.meet3.id
        'End If
        request.Parameters.Add("@bsfn", SqlDbType.NVarChar).Value = aBS.firstname
        request.Parameters.Add("@bsln", SqlDbType.NVarChar).Value = aBS.lastname
        request.Parameters.Add("@bsg", SqlDbType.Bit).Value = aBS.gender
        request.Parameters.Add("@bsad", SqlDbType.NVarChar).Value = aBS.address
        request.Parameters.Add("@bsph1", SqlDbType.NVarChar).Value = aBS.phone1
        If Not aBS.phone2 Is Nothing Then
            request.Parameters.Add("@bsph2", SqlDbType.NVarChar).Value = aBS.phone2
        Else
            request.Parameters.Add("@bsph2", SqlDbType.NVarChar).Value = DBNull.Value
        End If
        request.Parameters.Add("@bsid", SqlDbType.Int).Value = aBS.id
        request.ExecuteReader().Close()
    End Sub

    ''' <summary>
    ''' Update the relationshop between a babysitter and a meet. Many parameters can be added, making this method powerfull :
    '''  - The meet and a babysitter to add : The meet is associated to the babysitter (BSID) in one of the 3 avalaible meeting (BSPod).
    '''  - The meet and a babysitter to remove : The meet can be nothing, one of the 3 babisitter's meets is removed.
    '''  - The meet and both. The meet is removed from one babyssiter and then added to another babysitter.
    ''' </summary>
    ''' <param name="meetNumber">The ID of the meet</param>
    ''' <param name="BSIDAdd">The ID of the babyssiter who recieved the meet. This parameter is mandatory with the next one.</param>
    ''' <param name="BSPosAdd">The firts avalaible empty meet of the babysitter where the meet Id will be placed. This parameter is mandatory with the previous one.</param>
    ''' <param name="BSIDRemove">The ID of the babyssiter where the meet is to remove. This parameter is mandatory with the next one.</param>
    ''' <param name="BSPosRemove">The firts avalaible empty meet of the babysitter where the meet Id will be removed. This parameter is mandatory with the previous one.</param>
    ''' <remarks>TO get the first empty meet of a babysitter, see getFirstFreeMeetOf method</remarks>
    Public Sub updateBabysitterMeets(meetNumber As UShort, Optional BSIDAdd As Integer = 0, Optional BSPosAdd As Integer = 0, Optional BSIDRemove As Integer = 0, Optional BSPosRemove As Integer = 0)
        Dim request As SqlCommand = New SqlCommand()
        request.Connection = DBConnexion
        If BSPosRemove >= 1 And BSPosRemove <= 4 Then
            Select Case BSPosRemove
                'Case 1
                '    request.CommandText = "UPDATE Babysitter SET FK_idMeet1 = @bsfkR WHERE idBS = @bsidR"
                Case 2
                    request.CommandText = "UPDATE Babysitter SET FK_idMeet1 = @bsfkR WHERE idBS = @bsidR"
                Case 3
                    request.CommandText = "UPDATE Babysitter SET FK_idMeet2 = @bsfkR WHERE idBS = @bsidR"
                Case 4
                    request.CommandText = "UPDATE Babysitter SET FK_idMeet3 = @bsfkR WHERE idBS = @bsidR"
            End Select
            'request.Parameters.Add("@bsPosR", SqlDbType.Int).Value = BSPosRemove
            request.Parameters.Add("@bsfkR", SqlDbType.Int).Value = DBNull.Value
            request.Parameters.Add("@bsidR", SqlDbType.Int).Value = BSIDRemove

            request.ExecuteReader().Close()
        End If

        If BSPosAdd >= 1 And BSPosAdd <= 3 Then
            Select Case BSPosAdd
                Case 1
                    request.CommandText = "UPDATE Babysitter SET FK_idMeet1 = @bsfkA WHERE idBS = @bsidA"
                Case 2
                    request.CommandText = "UPDATE Babysitter SET FK_idMeet2 = @bsfkA WHERE idBS = @bsidA"
                Case 3
                    request.CommandText = "UPDATE Babysitter SET FK_idMeet3 = @bsfkA WHERE idBS = @bsidA"
            End Select
            'request.Parameters.Add("@bsPosA", SqlDbType.Int).Value = BSPosAdd
            request.Parameters.Add("@bsfkA", SqlDbType.Int).Value = meetNumber
            request.Parameters.Add("@bsidA", SqlDbType.Int).Value = BSIDAdd

            request.ExecuteReader().Close()
        End If
    End Sub

    ''' <summary>
    ''' Delete a babysitter and associated Meets from the database
    ''' </summary>
    ''' <param name="id">The id of the babysiter to delete</param>
    ''' <remarks>Be aware that some child will loose their Meet.</remarks>
    Public Sub deleteBabysitterByID(id As Integer)
        Dim aBS As BabySitter = getBabysitterByID(id)

        If Not aBS.meet1 Is Nothing Then deleteMeet(aBS.meet1.id)
        If Not aBS.meet2 Is Nothing Then deleteMeet(aBS.meet2.id)
        If Not aBS.meet3 Is Nothing Then deleteMeet(aBS.meet3.id)

        Dim request As SqlCommand = New SqlCommand()
        request.Connection = DBConnexion
        request.CommandText = "DELETE FROM Babysitter WHERE idBS = @BSid"
        request.Parameters.Add("@BSid", SqlDbType.Int).Value = id
        request.ExecuteReader.Close()

    End Sub

    '----- DB Methods for Meet -----'
    ''' <summary>
    ''' Get a meet by its ID
    ''' </summary>
    ''' <param name="id"></param>
    ''' <returns>a meet or nothing</returns>
    ''' <remarks></remarks>
    Public Function getMeetByID(id As Integer) As Meet
        Dim request As SqlCommand = New SqlCommand()
        request.Connection = DBConnexion
        request.CommandText = "SELECT * FROM Meet WHERE idMeet = @id"
        request.Parameters.Add("@id", SqlDbType.Int).Value = id
        Dim results As SqlDataReader
        results = request.ExecuteReader()

        Dim aMeet As Meet = Nothing
        If (results.HasRows) Then
            results.Read()
            aMeet = New Meet(results.GetInt32(1))
            aMeet.monday = results.GetBoolean(2)
            aMeet.tuesday = results.GetBoolean(3)
            aMeet.wednesday = results.GetBoolean(4)
            aMeet.thursday = results.GetBoolean(5)
            aMeet.friday = results.GetBoolean(6)
            aMeet.saturday = results.GetBoolean(7)
            aMeet.sunday = results.GetBoolean(8)
            results.Close()
        End If
        If Not results.IsClosed Then results.Close()
        Return aMeet
    End Function

    ''' <summary>
    ''' Get a meet for a given Child ID
    ''' </summary>
    ''' <param name="id">The Id of the Child associated with the meet</param>
    ''' <returns>The corresponding meet or nothing if the child has no meet</returns>
    ''' <remarks></remarks>
    Public Function getMeetByChildId(id As Integer) As Meet
        Dim request As SqlCommand = New SqlCommand()
        request.Connection = DBConnexion
        request.CommandText = "SELECT * FROM Meet WHERE FK_idChild = @id"
        request.Parameters.Add("@id", SqlDbType.Int).Value = id
        Dim results As SqlDataReader
        results = request.ExecuteReader()

        Dim aMeet As Meet = Nothing
        If (results.HasRows) Then
            results.Read()
            aMeet = New Meet(results.GetInt32(1))
            aMeet.monday = results.GetBoolean(2)
            aMeet.tuesday = results.GetBoolean(3)
            aMeet.wednesday = results.GetBoolean(4)
            aMeet.thursday = results.GetBoolean(5)
            aMeet.friday = results.GetBoolean(6)
            aMeet.saturday = results.GetBoolean(7)
            aMeet.sunday = results.GetBoolean(8)
            results.Close()
        End If

        If Not results.IsClosed Then results.Close()
        Return aMeet
    End Function

    ''' <summary>
    ''' Edit this meet by updating his values
    ''' </summary>
    ''' <param name="aMeet">The meet to edit with new modifications</param>
    ''' <remarks>The Meet Id is mandatory</remarks>
    Public Sub editMeet(aMeet As Meet)
        Dim request As SqlCommand = New SqlCommand()
        request.Connection = DBConnexion
        request.CommandText = "UPDATE Meet SET Monday = @mon, Tuesday = @tue, Wednesday = @wed, Thursday = @thr, Friday = @fri, Saturday = @sat, Sunday = @sun WHERE idMeet = @idm"

        request.Parameters.Add("@mon", SqlDbType.Bit).Value = aMeet.monday
        request.Parameters.Add("@tue", SqlDbType.Bit).Value = aMeet.tuesday
        request.Parameters.Add("@wed", SqlDbType.Bit).Value = aMeet.wednesday
        request.Parameters.Add("@thr", SqlDbType.Bit).Value = aMeet.thursday
        request.Parameters.Add("@fri", SqlDbType.Bit).Value = aMeet.friday
        request.Parameters.Add("@sat", SqlDbType.Bit).Value = aMeet.saturday
        request.Parameters.Add("@sun", SqlDbType.Bit).Value = aMeet.sunday
        request.Parameters.Add("@idm", SqlDbType.Int).Value = aMeet.id
        request.ExecuteReader().Close()

    End Sub

    ''' <summary>
    ''' Add a meet to the database. 
    ''' </summary>
    ''' <param name="aMeet">The meet to add</param>
    ''' <param name="childID">The id of the child associated</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function addMeet(aMeet As Meet, childID As Integer) As Integer
        Dim request As SqlCommand = New SqlCommand()
        request.Connection = DBConnexion
        request.CommandText = "INSERT INTO Meet (FK_idChild, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday) VALUES (@idc, @mon, @tue, @wed, @thr, @fri, @sat, @sun)"

        request.Parameters.Add("@mon", SqlDbType.Bit).Value = aMeet.monday
        request.Parameters.Add("@tue", SqlDbType.Bit).Value = aMeet.tuesday
        request.Parameters.Add("@wed", SqlDbType.Bit).Value = aMeet.wednesday
        request.Parameters.Add("@thr", SqlDbType.Bit).Value = aMeet.thursday
        request.Parameters.Add("@fri", SqlDbType.Bit).Value = aMeet.friday
        request.Parameters.Add("@sat", SqlDbType.Bit).Value = aMeet.saturday
        request.Parameters.Add("@sun", SqlDbType.Bit).Value = aMeet.sunday
        request.Parameters.Add("@idc", SqlDbType.Int).Value = childID
        request.ExecuteReader().Close()

        request.CommandText = "SELECT idMeet FROM Meet WHERE FK_idChild = @idch"
        request.Parameters.Add("@idch", SqlDbType.Int).Value = childID
        Dim results As SqlDataReader
        results = request.ExecuteReader()
        results.Read()
        Dim idBS As Integer = results.GetInt32(0)
        results.Close()
        Return idBS
    End Function

    ''' <summary>
    ''' Delete the Meet where the id is given. Also update the associated Babysitter
    ''' </summary>
    ''' <param name="id">The id of the meet to delete</param>
    ''' <remarks>Be aware of the difference between the Child ID (Foreing Key) and the Meet ID.</remarks>
    Public Sub deleteMeet(id As Integer)
        Dim request As SqlCommand = New SqlCommand()
        request.Connection = DBConnexion

        ' First wee search wich babysitter has the meet.
        request.CommandText = "SELECT FK_idMeet1, FK_idMeet2, FK_idMeet3, idBS FROM Babysitter WHERE FK_idMeet1 = @id OR FK_idMeet2 = @id OR FK_idMeet3 = @id"
        request.Parameters.Add("@id", SqlDbType.Int).Value = id

        Dim results As SqlDataReader
        results = request.ExecuteReader()

        ' Then we delete this meet from it.
        'If there is no bs associated with this meet, we can  delete it from the db
        If results.HasRows Then
            results.Read()
            Dim idBS As Integer = results.GetInt32(3)
            Dim wantedID As UShort = 0
            If Not results.IsDBNull(0) AndAlso id = results.GetInt32(0) Then
                wantedID = 1
            ElseIf Not results.IsDBNull(1) AndAlso id = results.GetInt32(1) Then
                wantedID = 2
            ElseIf Not results.IsDBNull(2) AndAlso id = results.GetInt32(2) Then
                wantedID = 3
            End If

            results.Close()

            Select Case wantedID
                Case 1
                    request.CommandText = "UPDATE Babysitter SET FK_idMeet1 = @bsfk WHERE idBS = @bsid"
                Case 2
                    request.CommandText = "UPDATE Babysitter SET FK_idMeet2 = @bsfk WHERE idBS = @bsid"
                Case 3
                    request.CommandText = "UPDATE Babysitter SET FK_idMeet3 = @bsfk WHERE idBS = @bsid"
            End Select
            request.Parameters.Add("@bsfk", SqlDbType.Int).Value = DBNull.Value
            request.Parameters.Add("@bsid", SqlDbType.Int).Value = idBS
            request.ExecuteReader().Close()
        End If

        'Finaly, the meet has no more constraint in the database, we can safely delete it.
        request.CommandText = "DELETE FROM Meet WHERE idMeet = @chid"
        request.Parameters.Add("@chid", SqlDbType.Int).Value = id
        request.ExecuteReader.Close()
    End Sub





End Class