You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
78 lines
3.1 KiB
78 lines
3.1 KiB
Imports System.Data.SqlClient
|
|
|
|
''' <summary>
|
|
''' Adapter für Edoka Datenbank auf dem SQL Server
|
|
''' </summary>
|
|
Public Class EdokaSqlDb
|
|
Public Sub New(connectionString As String)
|
|
Me.ConnectionString = connectionString
|
|
End Sub
|
|
|
|
Public Function GetDokumentStatusBezeichnungsNr(dokumentId As String) As Integer
|
|
Dim ret As Integer = 0
|
|
Using connection As New SqlConnection(ConnectionString),
|
|
cmd As SqlCommand = New SqlCommand("select top 1 ds.status_bezeichnungnr from dbo.statushistory sh
|
|
inner join dbo.dokument_status ds on ds.dokument_statusnr = sh.status
|
|
where dokumentid = @dokumentid
|
|
order by sh.erstellt_am desc", connection),
|
|
DataTable As DataTable = New DataTable(),
|
|
sqlDataAdapter As SqlDataAdapter = New SqlDataAdapter()
|
|
cmd.Parameters.AddWithValue("@dokumentid", dokumentId)
|
|
sqlDataAdapter.SelectCommand = cmd
|
|
sqlDataAdapter.Fill(DataTable)
|
|
If DataTable.Rows.Count = 1 Then
|
|
ret = DataTable.Rows(0)(0)
|
|
End If
|
|
End Using
|
|
Return ret
|
|
End Function
|
|
|
|
Public Function IsDokumentAbgeschlossen(dokumentId As String) As Boolean
|
|
Dim ret As Boolean = False
|
|
Dim dokumentStatusBezeichnungsNr As Integer =
|
|
GetDokumentStatusBezeichnungsNr(dokumentId)
|
|
If dokumentStatusBezeichnungsNr = -2 Then ret = True
|
|
Return ret
|
|
End Function
|
|
|
|
Public Function IsDokumentAufgehoben(dokumentId As String) As Boolean
|
|
Dim ret As Boolean = False
|
|
Dim dokumentStatusBezeichnungsNr As Integer =
|
|
GetDokumentStatusBezeichnungsNr(dokumentId)
|
|
If dokumentStatusBezeichnungsNr = -3 Then ret = True
|
|
Return ret
|
|
End Function
|
|
|
|
''' <summary>
|
|
''' gemäss Stackoverflow https://stackoverflow.com/a/10373384
|
|
''' </summary>
|
|
''' <param name="email"></param>
|
|
''' <param name="betreff"></param>
|
|
''' <param name="meldung"></param>
|
|
Public Sub SendMail(ByVal email As String, ByVal betreff As String, ByVal meldung As String)
|
|
If meldung.Length > 1020 Then meldung = meldung.Substring(0, 1020)
|
|
Using connection As New SqlConnection(ConnectionString),
|
|
cmd As SqlCommand = New SqlCommand("dbo.sp_sendmail", connection)
|
|
connection.Open()
|
|
cmd.CommandType = CommandType.StoredProcedure
|
|
cmd.Parameters.AddWithValue("@email", email)
|
|
cmd.Parameters.AddWithValue("@betreff", betreff)
|
|
cmd.Parameters.AddWithValue("@meldung", meldung)
|
|
cmd.ExecuteNonQuery()
|
|
End Using
|
|
End Sub
|
|
|
|
Public Sub CancelDocument(ByVal dokumentId As String, ByVal meldung As String)
|
|
Using connection As New SqlConnection(ConnectionString),
|
|
cmd As SqlCommand = New SqlCommand("dbo.sp_dokument_aufheben_nach_dokumentid", connection)
|
|
connection.Open()
|
|
cmd.CommandType = CommandType.StoredProcedure
|
|
cmd.Parameters.AddWithValue("@dokumentid", dokumentId)
|
|
cmd.Parameters.AddWithValue("@Meldung ", meldung)
|
|
cmd.ExecuteNonQuery()
|
|
End Using
|
|
End Sub
|
|
|
|
Property ConnectionString As String
|
|
End Class
|