Imports System.Data.SqlServerCe
Imports CamadaDTO
Imports System.Collections.Generic
Public Class EnsaioDAL
    Public Function DeletaEnsaio(ByVal EnsaioID As Integer) As Boolean
        Dim strSql As String
        Dim objdb As New AcessoDados
        Try
            strSql = "Delete from ensaio_amostra where numeroEnsaio= " & EnsaioID
            objdb.ExecuteQuery(strSql)
            objdb = New AcessoDados
            strSql = "Delete from ensaio where numeroEnsaio= " & EnsaioID
            objdb.ExecuteQuery(strSql)
        Catch ex As Exception
            Throw New Exception
        End Try
        Return True
    End Function

    Public Function GetEnsaios() As List(Of Ensaio)
        Dim strSql As String
        Dim objdb As New AcessoDados
        Dim lista As New List(Of Ensaio)
        strSql = "Select * from ensaio"
        Dim dataReader As SQLCEdataReader = objdb.ExecuteAndGetReader(strSql)
        While dataReader.Read
            Dim ens As Ensaio = New Ensaio
            ens.numeroEnsaio = IIf(IsDBNull(dataReader("numeroEnsaio")), 0, dataReader("numeroEnsaio"))
            ens.nome = IIf(IsDBNull(dataReader("nome")), "", dataReader("nome"))
            ens.codigo = IIf(IsDBNull(dataReader("codigo")), "", dataReader("codigo"))
            ens.numeroLote = IIf(IsDBNull(dataReader("numeroLote")), "", dataReader("numeroLote"))
            ens.dataEnsaio = IIf(IsDBNull(dataReader("dataEnsaio")), Date.Now, dataReader("dataEnsaio"))
            ens.numeroAmostras = IIf(IsDBNull(dataReader("numeroAmostras")), 0, dataReader("numeroAmostras"))
            ens.referenciasPorBloco = IIf(IsDBNull(dataReader("referenciasPorBloco")), 0, dataReader("referenciasPorBloco"))
            ens.numeroBlocos = IIf(IsDBNull(dataReader("numeroBlocos")), 0, dataReader("numeroBlocos"))
            ens.intervaloEntreMedicoes = IIf(IsDBNull(dataReader("intervaloEntreMedicoes")), 0, dataReader("intervaloEntreMedicoes"))
            ens.tempoIncubacao01 = IIf(IsDBNull(dataReader("tempoIncubacao01")), 0, dataReader("tempoIncubacao01"))
            ens.tempoIncubacao02 = IIf(IsDBNull(dataReader("tempoIncubacao02")), 0, dataReader("tempoIncubacao02"))
            ens.tempoIncubacao03 = IIf(IsDBNull(dataReader("tempoIncubacao03")), 0, dataReader("tempoIncubacao03"))
            ens.referencia01 = IIf(IsDBNull(dataReader("referencia01")), "", dataReader("referencia01"))
            ens.referencia02 = IIf(IsDBNull(dataReader("referencia02")), "", dataReader("referencia02"))
            ens.referencia03 = IIf(IsDBNull(dataReader("referencia03")), "", dataReader("referencia03"))
            ens.concentracao01 = IIf(IsDBNull(dataReader("concentracao01")), "", dataReader("concentracao01"))
            ens.concentracao02 = IIf(IsDBNull(dataReader("concentracao02")), "", dataReader("concentracao02"))
            ens.concentracao03 = IIf(IsDBNull(dataReader("concentracao03")), "", dataReader("concentracao03"))
            ens.unidade01 = IIf(IsDBNull(dataReader("unidade01")), "", dataReader("unidade01"))
            ens.unidade02 = IIf(IsDBNull(dataReader("unidade02")), "", dataReader("unidade02"))
            ens.unidade03 = IIf(IsDBNull(dataReader("unidade03")), "", dataReader("unidade03"))
            ens.tecnicoResponsavel = IIf(IsDBNull(dataReader("tecnicoResponsavel")), "", dataReader("tecnicoResponsavel"))
            ens.ampola = IIf(IsDBNull(dataReader("ampola")), "", dataReader("ampola"))
            ens.tipoEnsaio = IIf(IsDBNull(dataReader("tipoEnsaio")), "", dataReader("tipoEnsaio"))
            lista.Add(ens)
        End While
        dataReader.Close()
        Return lista
    End Function

    Public Function GetEnsaiosPorData(ByVal dataInicial As Date, ByVal dataFinal As Date, ByVal CodigoEnsaio As String) As List(Of Ensaio)
        Dim strSql As String
        Dim objdb As New AcessoDados
        Dim lista As New List(Of Ensaio)
        Dim inicio As String
        Dim fim As String
        inicio = dataInicial.Year.ToString() & "/" & dataInicial.Month.ToString() & "/" & dataInicial.Day.ToString()
        fim = dataFinal.Year.ToString() & "/" & dataFinal.Month.ToString() & "/" & dataFinal.Day.ToString()

        If (CodigoEnsaio = "") Then
            strSql = "Select * from ensaio where dataEnsaio >= '" & inicio & "' and dataEnsaio <= '" & fim & "'"
        Else
            strSql = "Select * from ensaio where dataEnsaio >= '" & inicio & "' and dataEnsaio <= '" & fim & "' and codigo like '%" & CodigoEnsaio & "%'"
        End If

        Dim dataReader As SQLCEDataReader = objdb.ExecuteAndGetReader(strSql)
        While dataReader.Read
            Dim ens As Ensaio = New Ensaio
            ens.numeroEnsaio = IIf(IsDBNull(dataReader("numeroEnsaio")), 0, dataReader("numeroEnsaio"))
            ens.nome = IIf(IsDBNull(dataReader("nome")), "", dataReader("nome"))
            ens.codigo = IIf(IsDBNull(dataReader("codigo")), "", dataReader("codigo"))
            ens.numeroLote = IIf(IsDBNull(dataReader("numeroLote")), "", dataReader("numeroLote"))
            ens.dataEnsaio = IIf(IsDBNull(dataReader("dataEnsaio")), Date.Now, dataReader("dataEnsaio"))
            ens.numeroAmostras = IIf(IsDBNull(dataReader("numeroAmostras")), 0, dataReader("numeroAmostras"))
            ens.referenciasPorBloco = IIf(IsDBNull(dataReader("referenciasPorBloco")), 0, dataReader("referenciasPorBloco"))
            ens.numeroBlocos = IIf(IsDBNull(dataReader("numeroBlocos")), 0, dataReader("numeroBlocos"))
            ens.intervaloEntreMedicoes = IIf(IsDBNull(dataReader("intervaloEntreMedicoes")), 0, dataReader("intervaloEntreMedicoes"))
            ens.tempoIncubacao01 = IIf(IsDBNull(dataReader("tempoIncubacao01")), 0, dataReader("tempoIncubacao01"))
            ens.tempoIncubacao02 = IIf(IsDBNull(dataReader("tempoIncubacao02")), 0, dataReader("tempoIncubacao02"))
            ens.tempoIncubacao03 = IIf(IsDBNull(dataReader("tempoIncubacao03")), 0, dataReader("tempoIncubacao03"))
            ens.referencia01 = IIf(IsDBNull(dataReader("referencia01")), "", dataReader("referencia01"))
            ens.referencia02 = IIf(IsDBNull(dataReader("referencia02")), "", dataReader("referencia02"))
            ens.referencia03 = IIf(IsDBNull(dataReader("referencia03")), "", dataReader("referencia03"))
            ens.concentracao01 = IIf(IsDBNull(dataReader("concentracao01")), "", dataReader("concentracao01"))
            ens.concentracao02 = IIf(IsDBNull(dataReader("concentracao02")), "", dataReader("concentracao02"))
            ens.concentracao03 = IIf(IsDBNull(dataReader("concentracao03")), "", dataReader("concentracao03"))
            ens.unidade01 = IIf(IsDBNull(dataReader("unidade01")), "", dataReader("unidade01"))
            ens.unidade02 = IIf(IsDBNull(dataReader("unidade02")), "", dataReader("unidade02"))
            ens.unidade03 = IIf(IsDBNull(dataReader("unidade03")), "", dataReader("unidade03"))
            ens.tecnicoResponsavel = IIf(IsDBNull(dataReader("tecnicoResponsavel")), "", dataReader("tecnicoResponsavel"))
            ens.ampola = IIf(IsDBNull(dataReader("ampola")), "", dataReader("ampola"))
            ens.tipoEnsaio = IIf(IsDBNull(dataReader("tipoEnsaio")), "", dataReader("tipoEnsaio"))
            lista.Add(ens)
        End While
        dataReader.Close()
        Return lista
    End Function

    Public Function GetEnsaioPorId(ByVal Id As Integer) As Ensaio
        Dim strSql As String
        Dim objdb As New AcessoDados
        Dim ens As Ensaio = Nothing
        strSql = "Select * from Ensaio where numeroEnsaio= " & Id
        Dim dataReader As SQLCEdataReader = objdb.ExecuteAndGetReader(strSql)
        Dim arr As New ArrayList
        While dataReader.Read
            ens = New Ensaio
            ens.numeroEnsaio = IIf(IsDBNull(dataReader("numeroEnsaio")), 0, dataReader("numeroEnsaio"))
            ens.nome = IIf(IsDBNull(dataReader("nome")), "", dataReader("nome"))
            ens.codigo = IIf(IsDBNull(dataReader("codigo")), "", dataReader("codigo"))
            ens.numeroLote = IIf(IsDBNull(dataReader("numeroLote")), "", dataReader("numeroLote"))
            ens.dataEnsaio = IIf(IsDBNull(dataReader("dataEnsaio")), Date.Now, dataReader("dataEnsaio"))
            ens.numeroAmostras = IIf(IsDBNull(dataReader("numeroAmostras")), 0, dataReader("numeroAmostras"))
            ens.referenciasPorBloco = IIf(IsDBNull(dataReader("referenciasPorBloco")), 0, dataReader("referenciasPorBloco"))
            ens.numeroBlocos = IIf(IsDBNull(dataReader("numeroBlocos")), 0, dataReader("numeroBlocos"))
            ens.intervaloEntreMedicoes = IIf(IsDBNull(dataReader("intervaloEntreMedicoes")), 0, dataReader("intervaloEntreMedicoes"))
            ens.tempoIncubacao01 = IIf(IsDBNull(dataReader("tempoIncubacao01")), 0, dataReader("tempoIncubacao01"))
            ens.tempoIncubacao02 = IIf(IsDBNull(dataReader("tempoIncubacao02")), 0, dataReader("tempoIncubacao02"))
            ens.tempoIncubacao03 = IIf(IsDBNull(dataReader("tempoIncubacao03")), 0, dataReader("tempoIncubacao03"))
            ens.referencia01 = IIf(IsDBNull(dataReader("referencia01")), "", dataReader("referencia01"))
            ens.referencia02 = IIf(IsDBNull(dataReader("referencia02")), "", dataReader("referencia02"))
            ens.referencia03 = IIf(IsDBNull(dataReader("referencia03")), "", dataReader("referencia03"))
            ens.concentracao01 = IIf(IsDBNull(dataReader("concentracao01")), "", dataReader("concentracao01"))
            ens.concentracao02 = IIf(IsDBNull(dataReader("concentracao02")), "", dataReader("concentracao02"))
            ens.concentracao03 = IIf(IsDBNull(dataReader("concentracao03")), "", dataReader("concentracao03"))
            ens.unidade01 = IIf(IsDBNull(dataReader("unidade01")), "", dataReader("unidade01"))
            ens.unidade02 = IIf(IsDBNull(dataReader("unidade02")), "", dataReader("unidade02"))
            ens.unidade03 = IIf(IsDBNull(dataReader("unidade03")), "", dataReader("unidade03"))
            ens.tecnicoResponsavel = IIf(IsDBNull(dataReader("tecnicoResponsavel")), "", dataReader("tecnicoResponsavel"))
            ens.ampola = IIf(IsDBNull(dataReader("ampola")), "", dataReader("ampola"))
            ens.tipoEnsaio = IIf(IsDBNull(dataReader("tipoEnsaio")), "", dataReader("tipoEnsaio"))
        End While
        dataReader.Close()
        Return ens
    End Function

    Public Function SalvarFormEnsaio(ByVal numeroEnsaio As Integer, ByVal TextBoxNomeEnsaio As String, ByVal TextBoxNumeroLote As String, ByVal dataEnsaio As Date, ComboBoxNumeroAmostra As String, ByVal ComboBoxNumeroBlocos As String, ByVal ComboBoxReferenciasPorBloco As String, ByVal ComboBoxIntervaloEntreMedicoes As String, ByVal ComboBox1aIncubacao As String, ByVal ComboBox2aIncubacao As String, ByVal ComboBox3aIncubacao As String, ByVal ComboBoxReferencia01 As String, ByVal ComboBoxReferencia02 As String, ByVal ComboBoxReferencia03 As String, ByVal TextBoxConcentracao01 As String, ByVal TextBoxConcentracao02 As String, ByVal TextBoxConcentracao03 As String, ByVal ComboBoxUnidade01 As String, ByVal ComboBoxUnidade02 As String, ByVal ComboBoxUnidade03 As String, ByVal tecnicoResponsavel As String, ByVal ampola As String) As String

        Dim strsql As String = String.Empty
        Dim sql01 As String = String.Empty
        Dim objDB As New AcessoDados
        Dim objDB2 As New AcessoDados
        Dim data As String = dataEnsaio.Year.ToString() & "/" & dataEnsaio.Month.ToString() & "/" & dataEnsaio.Day.ToString()

        If numeroEnsaio.ToString = 0 Then

            strsql = "insert into ensaio (nome, numeroLote, dataEnsaio, numeroAmostras, numeroBlocos, referenciasPorBloco, intervaloEntreMedicoes,tempoIncubacao01, tempoIncubacao02, tempoIncubacao03, referencia01, referencia02, referencia03, concentracao01, concentracao02, concentracao03, unidade01, unidade02, unidade03 , tecnicoResponsavel, ampola) values( '" & TextBoxNomeEnsaio & "','" & TextBoxNumeroLote & "','" & data & "','" & ComboBoxNumeroAmostra & "','" & ComboBoxNumeroBlocos & "','" & ComboBoxReferenciasPorBloco & "','" & ComboBoxIntervaloEntreMedicoes & "','" & ComboBox1aIncubacao & "','" & ComboBox2aIncubacao & "','" & ComboBox3aIncubacao & "','" & ComboBoxReferencia01 & "','" & ComboBoxReferencia02 & "','" & ComboBoxReferencia03 & "','" & TextBoxConcentracao01 & "','" & TextBoxConcentracao02 & "','" & TextBoxConcentracao03 & "','" & ComboBoxUnidade01 & "','" & ComboBoxUnidade02 & "','" & ComboBoxUnidade03 & "','" & tecnicoResponsavel & "','" & ampola & "')"

            '%%%%
            'PEGA O ULTIMO ID INSERIDO
            '%%%%
            numeroEnsaio = objDB.ExecuteAndGetID(strsql)
            Return numeroEnsaio
            '%%%%%
        Else
            strsql = " update ensaio set" _
               & " nome = '" & TextBoxNomeEnsaio & "'" _
               & " , numeroLote = '" & TextBoxNumeroLote & "'" _
               & " , dataEnsaio = '" & data & "'" _
               & " , numeroAmostras = '" & ComboBoxNumeroAmostra & "'" _
               & " , numeroBlocos = '" & ComboBoxNumeroBlocos & "'" _
               & " , referenciasPorBloco = '" & ComboBoxReferenciasPorBloco & "'" _
               & " , intervaloEntreMedicoes = '" & ComboBoxIntervaloEntreMedicoes & "'" _
               & " , tempoIncubacao01 = '" & ComboBox1aIncubacao & "'" _
               & " , tempoIncubacao02 = '" & ComboBox2aIncubacao & "'" _
               & " , tempoIncubacao03 = '" & ComboBox3aIncubacao & "'" _
               & " , referencia01 = '" & ComboBoxReferencia01 & "'" _
               & " , referencia02 = '" & ComboBoxReferencia02 & "'" _
               & " , referencia03 = '" & ComboBoxReferencia03 & "'" _
               & " , concentracao01 = '" & TextBoxConcentracao01 & "'" _
               & " , concentracao02 = '" & TextBoxConcentracao02 & "'" _
               & " , concentracao03 = '" & TextBoxConcentracao03 & "'" _
               & " , unidade01 = '" & ComboBoxUnidade01 & "'" _
               & " , unidade02 = '" & ComboBoxUnidade02 & "'" _
               & " , unidade03 = '" & ComboBoxUnidade03 & "'" _
                & " , tecnicoResponsavel = '" & tecnicoResponsavel & "'" _
                & " , ampola = '" & ampola & "'" _
               & " where numeroEnsaio= '" & numeroEnsaio & "'"
            objDB.ExecuteQuery(strsql)
        End If

        Return numeroEnsaio

    End Function

    Public Function Salva(ByVal ens As Ensaio) As Boolean
        Dim strsql As String = String.Empty
        Dim objDB As New AcessoDados
        If ens.Acao = FlagAcao.Insert Then
            strsql = "Insert into Ensaio (nome) values( '" & ens.nome.Trim() & "')"
        ElseIf ens.Acao = FlagAcao.Update Then
            strsql = "Update Ensaio set nome='" & ens.nome.Trim() & "'" & " where numeroEnsaio=" & ens.numeroEnsaio
        Else
            Return False
        End If

        Try
            objDB.ExecuteQuery(strsql)
        Catch ex As Exception
            Throw New Exception
        End Try

        Return True
    End Function

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