Imports System.Data.SqlServerCe
Imports System.Data
Imports CamadaDTO
Imports System.Collections.Generic
Public Class EnsaioAmostraResultadoDAL
    Public Function DeletaEnsaioAmostraPorAmostra(ByVal numeroEnsaio As Integer, ByVal numeroIncubacao As Integer) As Boolean
        Dim strSql As String
        Dim objDB As AcessoDados
        strSql = "Delete from ensaio_amostra_resultado where numeroIncubacao =" & numeroIncubacao & "numeroEnsaio =" & numeroEnsaio
        objDB = New AcessoDados
        objDB.ExecuteQuery(strSql)
        Return True
    End Function

    Public Function DeletaEnsaioAmostraPorEnsaio(ByVal numeroEnsaio As Integer, ByVal numeroIncubacao As Integer) As Boolean
        Dim strSql As String
        Dim objDB As AcessoDados
        strSql = "Delete from ensaio_amostra_resultado where numeroEnsaio =" & numeroEnsaio
        objDB = New AcessoDados
        objDB.ExecuteQuery(strSql)
        Return True
    End Function

    Public Function GetEnsaioAmostras() As List(Of EnsaioAmostraResultado)
        Dim objdb As New AcessoDados
        Dim strSql As String = ""
        strSql = "Select * from ensaio_resultado"
        Dim dataReader As SQLCEDataReader = objdb.ExecuteAndGetReader(strSql)
        Dim lista As New List(Of EnsaioAmostraResultado)
        While dataReader.Read
            Dim ensAmotra As EnsaioAmostraResultado = New EnsaioAmostraResultado
            ensAmotra.numeroEnsaio = IIf(IsDBNull(dataReader("numeroEnsaio")), 0, dataReader("numeroEnsaio"))
            ensAmotra.numeroIncubacao = IIf(IsDBNull(dataReader("numeroIncubacao")), 0, dataReader("numeroIncubacao"))
            ensAmotra.A2 = IIf(IsDBNull(dataReader("A2")), 0, dataReader("A2"))
            ensAmotra.A3 = IIf(IsDBNull(dataReader("A3")), 0, dataReader("A3"))
            ensAmotra.B2 = IIf(IsDBNull(dataReader("B2")), 0, dataReader("B2"))
            ensAmotra.B3 = IIf(IsDBNull(dataReader("B3")), 0, dataReader("B3"))
            ensAmotra.C2 = IIf(IsDBNull(dataReader("C2")), 0, dataReader("C2"))
            ensAmotra.C3 = IIf(IsDBNull(dataReader("C3")), 0, dataReader("C3"))
            ensAmotra.D2 = IIf(IsDBNull(dataReader("D2")), 0, dataReader("D2"))
            ensAmotra.D3 = IIf(IsDBNull(dataReader("D3")), 0, dataReader("D3"))
            ensAmotra.A5 = IIf(IsDBNull(dataReader("A5")), 0, dataReader("A5"))
            ensAmotra.A6 = IIf(IsDBNull(dataReader("A6")), 0, dataReader("A6"))
            ensAmotra.B5 = IIf(IsDBNull(dataReader("B5")), 0, dataReader("B5"))
            ensAmotra.B6 = IIf(IsDBNull(dataReader("B6")), 0, dataReader("B6"))
            ensAmotra.C5 = IIf(IsDBNull(dataReader("C5")), 0, dataReader("C5"))
            ensAmotra.C6 = IIf(IsDBNull(dataReader("C6")), 0, dataReader("C6"))
            ensAmotra.D5 = IIf(IsDBNull(dataReader("D5")), 0, dataReader("D5"))
            ensAmotra.D6 = IIf(IsDBNull(dataReader("D6")), 0, dataReader("D6"))

            ensAmotra.b2A2 = IIf(IsDBNull(dataReader("B2A2")), 0, dataReader("B2A2"))
            ensAmotra.b2A3 = IIf(IsDBNull(dataReader("B2A3")), 0, dataReader("B2A3"))
            ensAmotra.b2B2 = IIf(IsDBNull(dataReader("B2B2")), 0, dataReader("B2B2"))
            ensAmotra.b2B3 = IIf(IsDBNull(dataReader("B2B3")), 0, dataReader("B2B3"))
            ensAmotra.b2C2 = IIf(IsDBNull(dataReader("B2C2")), 0, dataReader("B2C2"))
            ensAmotra.b2C3 = IIf(IsDBNull(dataReader("B2C3")), 0, dataReader("B2C3"))
            ensAmotra.b2D2 = IIf(IsDBNull(dataReader("B2D2")), 0, dataReader("B2D2"))
            ensAmotra.b2D3 = IIf(IsDBNull(dataReader("B2D3")), 0, dataReader("B2D3"))
            ensAmotra.b2A5 = IIf(IsDBNull(dataReader("B2A5")), 0, dataReader("B2A5"))
            ensAmotra.b2A6 = IIf(IsDBNull(dataReader("B2A6")), 0, dataReader("B2A6"))
            ensAmotra.b2B5 = IIf(IsDBNull(dataReader("B2B5")), 0, dataReader("B2B5"))
            ensAmotra.b2B6 = IIf(IsDBNull(dataReader("B2B6")), 0, dataReader("B2B6"))
            ensAmotra.b2C5 = IIf(IsDBNull(dataReader("B2C5")), 0, dataReader("B2C5"))
            ensAmotra.b2C6 = IIf(IsDBNull(dataReader("B2C6")), 0, dataReader("B2C6"))
            ensAmotra.b2D5 = IIf(IsDBNull(dataReader("B2D5")), 0, dataReader("B2D5"))
            ensAmotra.b2D6 = IIf(IsDBNull(dataReader("B2D6")), 0, dataReader("B2D6"))
            ensAmotra.alterados = IIf(IsDBNull(dataReader("alterados")), "", dataReader("alterados"))
            ensAmotra.posicaoGrafico = IIf(IsDBNull(dataReader("posicaoGrafico")), "", dataReader("posicaoGrafico"))
            lista.Add(ensAmotra)
        End While
        dataReader.Close()
        Return lista
    End Function

    Public Function GetEnsaioAmostrasPorEnsaioIncubacao(ByVal numeroEnsaio As Integer, ByVal numeroIncubacao As String)
        Dim objdb As New AcessoDados
        Dim strSql As String = ""
        strSql = "Select * from ensaio_resultado  where numeroEnsaio= '" & numeroEnsaio & "' and numeroIncubacao= '" & numeroIncubacao & "'"
        Dim dataReader As DataRow = objdb.ExecuteAndGetRow(strSql)

        Dim ensAmotra As EnsaioAmostraResultado = New EnsaioAmostraResultado
        ensAmotra.numeroEnsaio = IIf(IsDBNull(dataReader("numeroEnsaio")), 0, dataReader("numeroEnsaio"))
        ensAmotra.numeroIncubacao = IIf(IsDBNull(dataReader("numeroIncubacao")), 0, dataReader("numeroIncubacao"))
        ensAmotra.A2 = IIf(IsDBNull(dataReader("A2")), 0, dataReader("A2"))
        ensAmotra.A3 = IIf(IsDBNull(dataReader("A3")), 0, dataReader("A3"))
        ensAmotra.B2 = IIf(IsDBNull(dataReader("B2")), 0, dataReader("B2"))
        ensAmotra.B3 = IIf(IsDBNull(dataReader("B3")), 0, dataReader("B3"))
        ensAmotra.C2 = IIf(IsDBNull(dataReader("C2")), 0, dataReader("C2"))
        ensAmotra.C3 = IIf(IsDBNull(dataReader("C3")), 0, dataReader("C3"))
        ensAmotra.D2 = IIf(IsDBNull(dataReader("D2")), 0, dataReader("D2"))
        ensAmotra.D3 = IIf(IsDBNull(dataReader("D3")), 0, dataReader("D3"))
        ensAmotra.A5 = IIf(IsDBNull(dataReader("A5")), 0, dataReader("A5"))
        ensAmotra.A6 = IIf(IsDBNull(dataReader("A6")), 0, dataReader("A6"))
        ensAmotra.B5 = IIf(IsDBNull(dataReader("B5")), 0, dataReader("B5"))
        ensAmotra.B6 = IIf(IsDBNull(dataReader("B6")), 0, dataReader("B6"))
        ensAmotra.C5 = IIf(IsDBNull(dataReader("C5")), 0, dataReader("C5"))
        ensAmotra.C6 = IIf(IsDBNull(dataReader("C6")), 0, dataReader("C6"))
        ensAmotra.D5 = IIf(IsDBNull(dataReader("D5")), 0, dataReader("D5"))
        ensAmotra.D6 = IIf(IsDBNull(dataReader("D6")), 0, dataReader("D6"))

        ensAmotra.b2A2 = IIf(IsDBNull(dataReader("B2A2")), 0, dataReader("B2A2"))
        ensAmotra.b2A3 = IIf(IsDBNull(dataReader("B2A3")), 0, dataReader("B2A3"))
        ensAmotra.b2B2 = IIf(IsDBNull(dataReader("B2B2")), 0, dataReader("B2B2"))
        ensAmotra.b2B3 = IIf(IsDBNull(dataReader("B2B3")), 0, dataReader("B2B3"))
        ensAmotra.b2C2 = IIf(IsDBNull(dataReader("B2C2")), 0, dataReader("B2C2"))
        ensAmotra.b2C3 = IIf(IsDBNull(dataReader("B2C3")), 0, dataReader("B2C3"))
        ensAmotra.b2D2 = IIf(IsDBNull(dataReader("B2D2")), 0, dataReader("B2D2"))
        ensAmotra.b2D3 = IIf(IsDBNull(dataReader("B2D3")), 0, dataReader("B2D3"))
        ensAmotra.b2A5 = IIf(IsDBNull(dataReader("B2A5")), 0, dataReader("B2A5"))
        ensAmotra.b2A6 = IIf(IsDBNull(dataReader("B2A6")), 0, dataReader("B2A6"))
        ensAmotra.b2B5 = IIf(IsDBNull(dataReader("B2B5")), 0, dataReader("B2B5"))
        ensAmotra.b2B6 = IIf(IsDBNull(dataReader("B2B6")), 0, dataReader("B2B6"))
        ensAmotra.b2C5 = IIf(IsDBNull(dataReader("B2C5")), 0, dataReader("B2C5"))
        ensAmotra.b2C6 = IIf(IsDBNull(dataReader("B2C6")), 0, dataReader("B2C6"))
        ensAmotra.b2D5 = IIf(IsDBNull(dataReader("B2D5")), 0, dataReader("B2D5"))
        ensAmotra.b2D6 = IIf(IsDBNull(dataReader("B2D6")), 0, dataReader("B2D6"))
        ensAmotra.alterados = IIf(IsDBNull(dataReader("alterados")), "", dataReader("alterados"))
        ensAmotra.posicaoGrafico = IIf(IsDBNull(dataReader("posicaoGrafico")), "", dataReader("posicaoGrafico"))
        Return ensAmotra
    End Function

    Public Function GetEnsaioAmostraPorAmostra(ByVal EnsaioAmostraid As Integer) As EnsaioAmostraResultado
        Dim objdb As New AcessoDados
        Dim strSql As String = ""
        Dim ensAmotra As EnsaioAmostraResultado = Nothing
        strSql = "Select * from ensaio_resultado where numeroIncubacao= " & EnsaioAmostraid
        Dim dataReader As SQLCEDataReader = objdb.ExecuteAndGetReader(strSql)
        While dataReader.Read
            ensAmotra = New EnsaioAmostraResultado
            ensAmotra.numeroEnsaio = IIf(IsDBNull(dataReader("numeroEnsaio")), 0, dataReader("numeroEnsaio"))
            ensAmotra.numeroIncubacao = IIf(IsDBNull(dataReader("numeroIncubacao")), 0, dataReader("numeroIncubacao"))
            ensAmotra.A2 = IIf(IsDBNull(dataReader("A2")), 0, dataReader("A2"))
        End While
        dataReader.Close()
        Return ensAmotra
    End Function

    Public Function GetEnsaioAmostrasPorEnsaio(ByVal numeroEnsaio As Integer) As List(Of EnsaioAmostraResultado)
        Dim objdb As New AcessoDados
        Dim strSql As String = ""
        strSql = "Select * from ensaio_resultado where numeroEnsaio= " & numeroEnsaio
        Dim dataReader As SQLCEDataReader = objdb.ExecuteAndGetReader(strSql)
        Dim lista As New List(Of EnsaioAmostraResultado)
        Dim ensAmotra As EnsaioAmostraResultado = Nothing
        While dataReader.Read
            ensAmotra = New EnsaioAmostraResultado
            ensAmotra.numeroEnsaio = IIf(IsDBNull(dataReader("numeroEnsaio")), 0, dataReader("numeroEnsaio"))
            ensAmotra.numeroIncubacao = IIf(IsDBNull(dataReader("numeroIncubacao")), 0, dataReader("numeroIncubacao"))
            ensAmotra.A2 = IIf(IsDBNull(dataReader("codigo")), "", dataReader("codigo"))
            lista.Add(ensAmotra)
        End While
        dataReader.Close()
        Return lista
    End Function

    Public Function SalvaAtributo(ByVal _EnsaioAmostra As EnsaioAmostraResultado, ByVal atributo As String, ByVal valorAtributo As String) As Boolean
        Dim strsql As String = ""
        Dim objDB As New AcessoDados
        strsql = "Update ensaio_resultado set" & atributo & " ='" & valorAtributo & "' where numeroIncubacao = '" & _EnsaioAmostra.numeroIncubacao & "' and numeroEnsaio = '" & _EnsaioAmostra.numeroEnsaio & "'"
        objDB.ExecuteQuery(strsql)
        Return True
    End Function

    Public Function SalvaAtributoEnsaioAmostra(ByVal numeroEnsaio As Integer, ByVal numeroIncubacao As Integer, ByVal campo As String, ByVal valor As String) As Boolean
        Dim strsql As String = ""
        Dim objDB As New AcessoDados
        strsql = "Update ensaio_resultado set " & campo & " ='" & valor & "'  where numeroEnsaio= '" & numeroEnsaio & "' and numeroIncubacao= '" & numeroIncubacao & "'"
        objDB.ExecuteQuery(strsql)
        Return True
    End Function

    Public Function SalvaEnsaioAmostra(ByVal _EnsaioAmostra As EnsaioAmostraResultado) As Boolean
        Dim strsql As String = ""
        Dim objDB As New AcessoDados
        If _EnsaioAmostra.Acao = FlagAcao.Insert Then
            strsql = "Insert into ensaio_resultado (numeroEnsaio, numeroIncubacao) " & "values( '" & _EnsaioAmostra.numeroEnsaio & "', " & _EnsaioAmostra.numeroIncubacao
        ElseIf _EnsaioAmostra.Acao = FlagAcao.Update Then
            strsql = "Update ensaio_resultado set" & "numeroEnsaio='" & _EnsaioAmostra.numeroEnsaio & "'" & " where numeroIncubacao = '" & _EnsaioAmostra.numeroIncubacao
        End If
        objDB.ExecuteQuery(strsql)
        Return True
    End Function

    Public Function SalvaAtributoEnsaioResultado(ByVal numeroEnsaio As Integer, ByVal numeroIncubacao As String, ByVal campo As String, ByVal valor As String) As Boolean
        Dim strsql As String = ""
        Dim objDB As New AcessoDados
        strsql = "Update ensaio_resultado set " & campo & " ='" & valor & "'  where numeroEnsaio= '" & numeroEnsaio & "' and numeroIncubacao = '" & numeroIncubacao & "'"
        objDB.ExecuteQuery(strsql)
        Return True
    End Function


    Public Function SalvaEnsaioResultadoNova(ByVal numeroEnsaio As Integer, ByVal numeroIncubacao As String) As Boolean
        Dim strsql As String = ""
        Dim existe As Boolean = True
        Dim objDB As New AcessoDados
        strsql = " Select * from ensaio_resultado where numeroEnsaio= '" & numeroEnsaio & "' and numeroIncubacao = '" & numeroIncubacao & "'"
        existe = objDB.VerificaExiste(strsql)
        If existe = False Then
            strsql = "Insert into ensaio_resultado (numeroEnsaio, numeroIncubacao) " & "values( '" & numeroEnsaio & " ', '" & numeroIncubacao & "')"
        End If
        objDB.ExecuteQuery(strsql)
        Return True
    End Function

End Class
