102 lines
5.6 KiB
VB.net
102 lines
5.6 KiB
VB.net
Imports System.Data.SqlClient
|
|
Imports System.Reflection
|
|
Imports System.Linq
|
|
|
|
'Fachdaten zu einem bereits gespeicherten tblSendungen-Datensatz.
|
|
'Zuerst cSendungen.SAVE(), anschließend cSendungCustomsCases.SAVE() aufrufen.
|
|
Public Class cSendungCustomsCases
|
|
Public hasEntry As Boolean = False
|
|
Private ReadOnly SQL As New VERAG_PROG_ALLGEMEIN.SQL
|
|
|
|
Public Property scc_SendungID As Integer = 0
|
|
Public Property scc_Type As Short = 0
|
|
Public Property scc_Subject As String = Nothing
|
|
Public Property scc_CustomsOffice As String = Nothing
|
|
Public Property scc_CustomsReference As String = Nothing
|
|
Public Property scc_DocumentDate As Date? = Nothing
|
|
Public Property scc_ReceivedDate As Date? = Nothing
|
|
Public Property scc_Deadline As Date? = Nothing
|
|
Public Property scc_ReminderDate As Date? = Nothing
|
|
Public Property scc_ReferenceMRN As String = Nothing
|
|
Public Property scc_Amount As Decimal? = Nothing
|
|
Public Property scc_Currency As String = Nothing
|
|
Public Property scc_Status As Short = 0
|
|
Public Property scc_Result As Short? = Nothing
|
|
Public Property scc_ClosedDate As Date? = Nothing
|
|
|
|
Public Sub New(sendungId As Integer, Optional autoLoad As Boolean = True)
|
|
scc_SendungID = sendungId
|
|
If autoLoad AndAlso sendungId > 0 Then LOAD()
|
|
End Sub
|
|
|
|
Public Function getParameterList() As List(Of VERAG_PROG_ALLGEMEIN.SQLVariable)
|
|
Dim list As New List(Of VERAG_PROG_ALLGEMEIN.SQLVariable)
|
|
list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("scc_SendungID", scc_SendungID,, True))
|
|
list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("scc_Type", scc_Type))
|
|
list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("scc_Subject", scc_Subject))
|
|
list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("scc_CustomsOffice", scc_CustomsOffice))
|
|
list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("scc_CustomsReference", scc_CustomsReference))
|
|
list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("scc_DocumentDate", scc_DocumentDate))
|
|
list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("scc_ReceivedDate", scc_ReceivedDate))
|
|
list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("scc_Deadline", scc_Deadline))
|
|
list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("scc_ReminderDate", scc_ReminderDate))
|
|
list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("scc_ReferenceMRN", scc_ReferenceMRN))
|
|
list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("scc_Amount", scc_Amount))
|
|
list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("scc_Currency", scc_Currency))
|
|
list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("scc_Status", scc_Status))
|
|
list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("scc_Result", scc_Result))
|
|
list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("scc_ClosedDate", scc_ClosedDate))
|
|
Return list
|
|
End Function
|
|
|
|
Public Sub LOAD()
|
|
Try
|
|
hasEntry = False
|
|
If scc_SendungID <= 0 Then Exit Sub
|
|
Using conn As SqlConnection = SQL.GetNewOpenConnectionAVISO()
|
|
Using cmd As New SqlCommand("SELECT * FROM dbo.tblSendungCustomsCases WHERE scc_SendungID=@scc_SendungID", conn)
|
|
cmd.Parameters.Add("@scc_SendungID", SqlDbType.Int).Value = scc_SendungID
|
|
Using dr As SqlDataReader = cmd.ExecuteReader()
|
|
If dr.Read() Then
|
|
For Each li In getParameterList()
|
|
Dim propInfo As PropertyInfo = Me.GetType().GetProperty(li.Scalarvariable)
|
|
If propInfo Is Nothing Then Continue For
|
|
Dim value As Object = dr(li.Text)
|
|
If value Is DBNull.Value Then
|
|
propInfo.SetValue(Me, Nothing)
|
|
Else
|
|
Dim targetType As Type = Nullable.GetUnderlyingType(propInfo.PropertyType)
|
|
If targetType Is Nothing Then targetType = propInfo.PropertyType
|
|
propInfo.SetValue(Me, Convert.ChangeType(value, targetType))
|
|
End If
|
|
Next
|
|
hasEntry = True
|
|
End If
|
|
End Using
|
|
End Using
|
|
End Using
|
|
Catch ex As Exception
|
|
VERAG_PROG_ALLGEMEIN.cErrorHandler.ERR(ex.Message, ex.StackTrace, System.Reflection.MethodInfo.GetCurrentMethod.Name)
|
|
Throw
|
|
End Try
|
|
End Sub
|
|
|
|
Public Function SAVE() As Boolean
|
|
If scc_SendungID <= 0 Then Throw New InvalidOperationException("Zuerst die Sendung speichern; SendungID fehlt.")
|
|
Dim sqlstr As String = "SET XACT_ABORT ON; BEGIN TRAN; " &
|
|
"IF EXISTS(SELECT 1 FROM dbo.tblSendungCustomsCases WITH (UPDLOCK,HOLDLOCK) WHERE scc_SendungID=@scc_SendungID) " &
|
|
"BEGIN " & getUpdateCmd() & " END ELSE BEGIN " & getInsertCmd() & " END; COMMIT TRAN;"
|
|
Return SQL.doSQLVarList(sqlstr, "AVISO", , getParameterList())
|
|
End Function
|
|
|
|
Public Function getUpdateCmd() As String
|
|
Dim cols = getParameterList().Where(Function(x) Not x.isPrimaryParam).Select(Function(x) "[" & x.Text & "]=@" & x.Scalarvariable)
|
|
Return "UPDATE dbo.tblSendungCustomsCases SET " & String.Join(",", cols) & " WHERE scc_SendungID=@scc_SendungID;"
|
|
End Function
|
|
|
|
Public Function getInsertCmd() As String
|
|
Dim list = getParameterList()
|
|
Return "INSERT INTO dbo.tblSendungCustomsCases (" & String.Join(",", list.Select(Function(x) "[" & x.Text & "]")) & ") VALUES (" & String.Join(",", list.Select(Function(x) "@" & x.Scalarvariable)) & ");"
|
|
End Function
|
|
End Class
|