Imports System.IO
Imports System.Data.Sql
Imports System.Data.SqlClient

Public Class DB


    Public dsDaten As New DataSet
    Public dsdaten1 As New DataSet
    Public dsdatenblacklist As New DataSet
    Public dadaten As SqlDataAdapter
    Public dadatenblacklist As SqlDataAdapter
    Dim sql As String



    Public Function Get_DokumentTYP(ByVal dokumenttypnr As Integer) As Boolean
        Try
            dsDaten.Tables.Clear()
            sql = "Select dokumenttypnr, bezeichnung from dokumenttyp where dokumenttypnr=" + dokumenttypnr.ToString
            dadaten = New SqlDataAdapter(sql, Globals.sConnectionstring)
            dadaten.Fill(dsDaten, "Dokumenttypen")
            If dsDaten.Tables(0).Rows.Count < 1 Then Return False Else Return True
        Catch
        End Try
    End Function

    Public Function Get_Mitarbeiter(ByVal Mitarbeiternr As Integer) As Boolean
        Try
            dsDaten.Tables.Clear()
            sql = "Select mitarbeiternr from mitarbeiter where aktiv=1 and mitarbeiternr=" + Mitarbeiternr.ToString
            dadaten = New SqlDataAdapter(sql, Globals.sConnectionstring)
            dadaten.Fill(dsDaten, "mitarbeiter")
            If dsDaten.Tables(0).Rows.Count < 1 Then Return False Else Return True
        Catch
        End Try
    End Function

    'Public Function Get_Doktyp_Ma(ByVal Doktypnr As Integer, ByVal Mitarbeiternr As Integer) As Boolean
    '    Try
    '        dsDaten.Tables.Clear()
    '        sql = "Select dokumenttypnr from dokumenttyp where dokumenttypnr=" + Doktypnr.ToString + " and fachverantwortung=" + Mitarbeiternr.ToString
    '        dadaten = New SqlDataAdapter(sql, Globals.sConnectionstring)
    '        dadaten.Fill(dsDaten, "mitarbeiter")
    '        If dsDaten.Tables(0).Rows.Count < 1 Then Return False Else Return True
    '    Catch
    '    End Try
    'End Function

    Public Function Get_ov(ByVal ovnr As Integer) As Boolean
        Try
            dsDaten.Tables.Clear()
            sql = "Select office_vorlagenr from office_vorlage where office_vorlagenr=" + ovnr.ToString
            dadaten = New SqlDataAdapter(sql, Globals.sConnectionstring)
            dadaten.Fill(dsDaten, "ov")
            If dsDaten.Tables(0).Rows.Count < 1 Then Return False Else Return True
        Catch
        End Try
    End Function
    Public Function Update_Doktype(ByVal Doktypnr As Integer, ByVal maneu As Integer) As Boolean
        Try
            Dim sqlcmd As New SqlCommand()
            Dim sqlconn As New SqlConnection(Globals.sConnectionstring)
            sqlcmd.CommandText = "Update dokumenttyp set owner=" + maneu.ToString + " where dokumenttypnr=" + Doktypnr.ToString
            sqlcmd.Connection = sqlconn
            sqlconn.Open()
            sqlcmd.ExecuteNonQuery()
            sqlconn.Close()
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function Update_Officevorlage(ByVal ovnr As Integer, ByVal maneu As Integer) As Boolean
        Try
            Dim sqlcmd As New SqlCommand()
            Dim sqlconn As New SqlConnection(Globals.sConnectionstring)
            sqlcmd.CommandText = "Update office_vorlage set owner=" + maneu.ToString + " where office_vorlagenr=" + ovnr.ToString
            sqlcmd.Connection = sqlconn
            sqlconn.Open()
            sqlcmd.ExecuteNonQuery()
            sqlconn.Close()
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

End Class


Public Class DB_Connection

    ''' <summary>
    ''' Liest sämtlcihe CFG-Dateien mit dem Namen "Vertragsverwaltung...". Sind meherere Dateien vorhanden,
    ''' wird ein Auswahldialog zur Datenbank-Selektion angezeigt.
    ''' Standardmässig wird Vertragsverwaltung.cfg als CFG-Datei benutzt.
    ''' 
    ''' Die CFG-Datei ist verschlüsselt und wird über die Crypto-Funktionen entschlüsselt.
    ''' </summary>
    ''' <remarks></remarks>
    Shared Sub New()
        Dim path As String = ""
        Dim fc As Integer = 0
        If fc < 2 Then Globals.ConnectionFilename = "edokaconn.cfg"
        Dim ofile As System.IO.File
        Dim oread As System.IO.StreamReader

        oread = ofile.OpenText(ApplicationPath() + "\" + Globals.ConnectionFilename)
        sConnectionstring = oread.ReadLine
        sConnectionstring = Crypto.DecryptText(sConnectionstring, "HutterundMueller")
        sConnectionstring = Left(sConnectionstring, Len(sConnectionstring) - 1)
        Globals.sConnectionstring = sConnectionstring
        oread.Close()

        Dim connection As New SqlConnection()
        Dim da As New SqlDataAdapter("Select  beschreibung from pluginparameter where pluginparamnr=1", connection)
        Dim CB As SqlCommandBuilder = New SqlCommandBuilder(da)
        Dim ds As New DataSet()
        Try
            connection.ConnectionString = Globals.sConnectionstring
            connection.Open()
            da.Fill(ds, "params")
            path = ds.Tables(0).Rows(0).Item(0)
        Catch ex As Exception
            path = ""
            CB = Nothing
        End Try

        ds = Nothing
        da = Nothing
        connection.Close()
        connection = Nothing

        Globals.ConnectionFilename = "edokaconn.cfg"
        oread = ofile.OpenText(path + "\" + Globals.ConnectionFilename)
        sConnectionstring = oread.ReadLine
        sConnectionstring = Crypto.DecryptText(sConnectionstring, "HutterundMueller")
        sConnectionstring = Left(sConnectionstring, Len(sConnectionstring) - 1)
        Globals.sConnectionstring = sConnectionstring
        oread.Close()
    End Sub

End Class