988 lines
32 KiB
VB.net
988 lines
32 KiB
VB.net
Imports System.Data.SqlClient
|
|
Imports System.Reflection
|
|
|
|
' ==========================================================================
|
|
' MASTER CLASS
|
|
' Customs Declaration (Header + Items + Duties + Documents)
|
|
' ==========================================================================
|
|
|
|
Public Class cVERAG_Zollanmeldung
|
|
|
|
'=== KONSTRUKTOR =======================================================
|
|
|
|
Public Sub New()
|
|
End Sub
|
|
|
|
Public Sub New(id As Integer)
|
|
za_Id = id
|
|
LOAD()
|
|
End Sub
|
|
|
|
|
|
'=== FELDER ============================================================
|
|
|
|
Public Property za_Id As Integer
|
|
Public Property za_System As String
|
|
Public Property za_ExternalSystem As Boolean
|
|
Public Property za_Firma As String
|
|
Public Property za_Niederlassung As String
|
|
|
|
Public Property za_AvisoId As Integer?
|
|
Public Property za_SendungsId As Integer?
|
|
Public Property za_InternalRef As String
|
|
|
|
Public Property za_MRN As String
|
|
Public Property za_LRN As String
|
|
Public Property za_DeclarationNo As String
|
|
|
|
Public Property za_Procedure As String
|
|
Public Property za_RepresentationCode As String
|
|
Public Property za_FiscalRepresentation As Boolean?
|
|
|
|
Public Property za_CountryDispatch As String
|
|
Public Property za_CountryDestination As String
|
|
Public Property za_CountryOrigin As String
|
|
|
|
Public Property za_TotalItems As Integer?
|
|
Public Property za_TotalGross As Decimal?
|
|
Public Property za_TotalInvoice As Decimal?
|
|
Public Property za_TotalCustomsValue As Decimal?
|
|
|
|
Public Property za_TransportMode_Border As Integer?
|
|
Public Property za_VehicleId As String
|
|
Public Property za_Incoterm As String
|
|
Public Property za_IncotermPlace As String
|
|
|
|
Public Property za_DocumentsPresent As Boolean
|
|
|
|
Public Property za_ReleaseDate As Date?
|
|
Public Property za_DeclarationDate As Date?
|
|
Public Property za_Created As Date = Now
|
|
Public Property za_Modified As Date = Now
|
|
|
|
|
|
'=== UNTERKLASSEN ======================================================
|
|
|
|
Public Property Items As New List(Of cVERAG_Zollanmeldung_Item)
|
|
Public Property Duties As New List(Of cVERAG_Zollanmeldung_Duty)
|
|
Public Property Documents As New List(Of cVERAG_Zollanmeldung_Document)
|
|
Public Property ItemDocuments As New List(Of cVERAG_Zollanmeldung_ItemDocument)
|
|
|
|
|
|
Private SQL As New VERAG_PROG_ALLGEMEIN.SQL
|
|
|
|
|
|
|
|
|
|
'========================================================================
|
|
' PARAMETERLISTE (Header)
|
|
'========================================================================
|
|
|
|
Public Function getParameterList() As List(Of VERAG_PROG_ALLGEMEIN.SQLVariable)
|
|
|
|
Dim L As New List(Of VERAG_PROG_ALLGEMEIN.SQLVariable)
|
|
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_Id", za_Id,, True, True))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_System", za_System))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_ExternalSystem", za_ExternalSystem))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_Firma", za_Firma))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_Niederlassung", za_Niederlassung))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_AvisoId", za_AvisoId))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_SendungsId", za_SendungsId))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_InternalRef", za_InternalRef))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_MRN", za_MRN))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_LRN", za_LRN))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_DeclarationNo", za_DeclarationNo))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_Procedure", za_Procedure))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_RepresentationCode", za_RepresentationCode))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_FiscalRepresentation", za_FiscalRepresentation))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_CountryDispatch", za_CountryDispatch))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_CountryDestination", za_CountryDestination))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_CountryOrigin", za_CountryOrigin))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_TotalItems", za_TotalItems))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_TotalGross", za_TotalGross))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_TotalInvoice", za_TotalInvoice))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_TotalCustomsValue", za_TotalCustomsValue))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_TransportMode_Border", za_TransportMode_Border))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_VehicleId", za_VehicleId))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_Incoterm", za_Incoterm))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_IncotermPlace", za_IncotermPlace))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_DocumentsPresent", za_DocumentsPresent))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_ReleaseDate", za_ReleaseDate))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_DeclarationDate", za_DeclarationDate))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_Created", za_Created))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("za_Modified", za_Modified))
|
|
|
|
Return L
|
|
|
|
End Function
|
|
|
|
|
|
|
|
|
|
'========================================================================
|
|
' SAVE (HEADER)
|
|
'========================================================================
|
|
|
|
Public Function SAVE() As Boolean
|
|
|
|
Dim list = getParameterList()
|
|
|
|
Dim sqlstr As String =
|
|
" BEGIN TRAN " &
|
|
" IF EXISTS(SELECT 1 FROM tblVERAG_CustomsDeclarations WHERE za_Id=@za_Id) " &
|
|
" BEGIN " & getUpdateCmd() & " END " &
|
|
" ELSE BEGIN " & getInsertCmd() & " END " &
|
|
" COMMIT TRAN "
|
|
|
|
za_Id = SQL.doSQLVarListID(za_Id, sqlstr, "VERAG", , list)
|
|
Return za_Id > 0
|
|
|
|
End Function
|
|
|
|
|
|
|
|
|
|
'========================================================================
|
|
' SAVE_ALL (Header + Items + Duties + Docs)
|
|
'========================================================================
|
|
|
|
Public Function SAVE_ALL() As Boolean
|
|
|
|
If Not SAVE() Then Return False
|
|
If Not SAVE_Items() Then Return False
|
|
If Not SAVE_Duties() Then Return False
|
|
If Not SAVE_Documents() Then Return False
|
|
If Not SAVE_ItemDocuments() Then Return False
|
|
|
|
Return True
|
|
|
|
End Function
|
|
|
|
|
|
|
|
|
|
'========================================================================
|
|
' LOAD HEADER
|
|
'========================================================================
|
|
|
|
Public Function LOAD(Optional loadALL As Boolean = True) As Boolean
|
|
|
|
If loadALL Then
|
|
Items.Clear()
|
|
Duties.Clear()
|
|
Documents.Clear()
|
|
ItemDocuments.Clear()
|
|
End If
|
|
|
|
Using conn = SQL.GetNewOpenConnectionFMZOLL()
|
|
Using cmd As New SqlCommand("SELECT * FROM tblVERAG_CustomsDeclarations WHERE za_Id=@id", conn)
|
|
cmd.Parameters.AddWithValue("@id", za_Id)
|
|
Using dr = cmd.ExecuteReader()
|
|
If dr.Read Then
|
|
|
|
For Each p In getParameterList()
|
|
Dim prop = Me.GetType().GetProperty(p.Scalarvariable)
|
|
If prop IsNot Nothing Then
|
|
If IsDBNull(dr(p.Text)) Then
|
|
prop.SetValue(Me, Nothing)
|
|
Else
|
|
prop.SetValue(Me, dr(p.Text))
|
|
End If
|
|
End If
|
|
Next
|
|
|
|
dr.Close()
|
|
|
|
If loadALL Then
|
|
LOAD_Items()
|
|
LOAD_Duties()
|
|
LOAD_Documents()
|
|
LOAD_ItemDocuments()
|
|
End If
|
|
|
|
Return True
|
|
|
|
End If
|
|
End Using
|
|
End Using
|
|
End Using
|
|
|
|
Return False
|
|
|
|
End Function
|
|
|
|
|
|
|
|
|
|
|
|
'========================================================================
|
|
' SAVE ITEMS
|
|
'========================================================================
|
|
|
|
Private Function SAVE_Items() As Boolean
|
|
For Each it In Items
|
|
it.zaItem_DeclarationId = za_Id
|
|
If Not it.SAVE() Then Return False
|
|
Next
|
|
Return True
|
|
End Function
|
|
|
|
|
|
|
|
'========================================================================
|
|
' SAVE DUTIES
|
|
'========================================================================
|
|
|
|
Private Function SAVE_Duties() As Boolean
|
|
For Each du In Duties
|
|
du.zaDuty_DeclarationId = za_Id
|
|
If Not du.SAVE() Then Return False
|
|
Next
|
|
Return True
|
|
End Function
|
|
|
|
|
|
|
|
'========================================================================
|
|
' SAVE DOCS HEADER
|
|
'========================================================================
|
|
|
|
Private Function SAVE_Documents() As Boolean
|
|
For Each d In Documents
|
|
d.zaDoc_DeclarationId = za_Id
|
|
If Not d.SAVE() Then Return False
|
|
Next
|
|
Return True
|
|
End Function
|
|
|
|
|
|
|
|
'========================================================================
|
|
' SAVE DOCS ITEM
|
|
'========================================================================
|
|
|
|
Private Function SAVE_ItemDocuments() As Boolean
|
|
For Each d In ItemDocuments
|
|
If Not d.SAVE() Then Return False
|
|
Next
|
|
Return True
|
|
End Function
|
|
|
|
|
|
|
|
|
|
|
|
'========================================================================
|
|
' LOAD UNTERKLASSEN
|
|
'========================================================================
|
|
|
|
Private Sub LOAD_Items()
|
|
|
|
Using conn = SQL.GetNewOpenConnectionFMZOLL()
|
|
Using cmd As New SqlCommand("SELECT * FROM tblVERAG_CustomsDeclarations_Items WHERE zaItem_DeclarationId=@id ORDER BY zaItem_Line", conn)
|
|
cmd.Parameters.AddWithValue("@id", za_Id)
|
|
Using dr = cmd.ExecuteReader()
|
|
While dr.Read
|
|
Dim it As New cVERAG_Zollanmeldung_Item
|
|
it.LOAD_FROM_READER(dr)
|
|
Items.Add(it)
|
|
End While
|
|
End Using
|
|
End Using
|
|
End Using
|
|
|
|
End Sub
|
|
|
|
|
|
|
|
Private Sub LOAD_Duties()
|
|
|
|
Using conn = SQL.GetNewOpenConnectionFMZOLL()
|
|
Using cmd As New SqlCommand("SELECT * FROM tblVERAG_CustomsDeclarations_Duties WHERE zaDuty_DeclarationId=@id", conn)
|
|
cmd.Parameters.AddWithValue("@id", za_Id)
|
|
Using dr = cmd.ExecuteReader()
|
|
While dr.Read
|
|
Dim du As New cVERAG_Zollanmeldung_Duty
|
|
du.LOAD_FROM_READER(dr)
|
|
Duties.Add(du)
|
|
End While
|
|
End Using
|
|
End Using
|
|
End Using
|
|
|
|
End Sub
|
|
|
|
|
|
|
|
Private Sub LOAD_Documents()
|
|
|
|
Using conn = SQL.GetNewOpenConnectionFMZOLL()
|
|
Using cmd As New SqlCommand("SELECT * FROM tblVERAG_CustomsDeclarations_Documents WHERE zaDoc_DeclarationId=@id AND zaDoc_Level='H'", conn)
|
|
cmd.Parameters.AddWithValue("@id", za_Id)
|
|
Using dr = cmd.ExecuteReader()
|
|
While dr.Read
|
|
Dim d As New cVERAG_Zollanmeldung_Document
|
|
d.LOAD_FROM_READER(dr)
|
|
Documents.Add(d)
|
|
End While
|
|
End Using
|
|
End Using
|
|
End Using
|
|
|
|
End Sub
|
|
|
|
|
|
|
|
|
|
Private Sub LOAD_ItemDocuments()
|
|
|
|
Using conn = SQL.GetNewOpenConnectionFMZOLL()
|
|
Using cmd As New SqlCommand("SELECT * FROM tblVERAG_CustomsDeclarations_Documents WHERE zaDoc_Level='I' AND zaDoc_DeclarationId=@id", conn)
|
|
cmd.Parameters.AddWithValue("@id", za_Id)
|
|
Using dr = cmd.ExecuteReader()
|
|
While dr.Read
|
|
Dim d As New cVERAG_Zollanmeldung_ItemDocument
|
|
d.LOAD_FROM_READER(dr)
|
|
ItemDocuments.Add(d)
|
|
End While
|
|
End Using
|
|
End Using
|
|
End Using
|
|
|
|
End Sub
|
|
|
|
|
|
|
|
|
|
|
|
'========================================================================
|
|
' SQL HELPERS
|
|
'========================================================================
|
|
|
|
Private Function getUpdateCmd() As String
|
|
|
|
Dim cols As New Text.StringBuilder()
|
|
|
|
For Each p In getParameterList()
|
|
If Not p.isPrimaryParam Then
|
|
cols.Append("[" & p.Text & "]=@" & p.Scalarvariable & ",")
|
|
End If
|
|
Next
|
|
|
|
Dim setClause = cols.ToString().TrimEnd(","c)
|
|
|
|
Return "UPDATE tblVERAG_CustomsDeclarations SET " & setClause & " WHERE za_Id=@za_Id"
|
|
|
|
End Function
|
|
|
|
|
|
|
|
Private Function getInsertCmd() As String
|
|
|
|
Dim C As New List(Of String)
|
|
Dim V As New List(Of String)
|
|
|
|
For Each p In getParameterList()
|
|
If Not p.isPrimaryParam Then
|
|
C.Add("[" & p.Text & "]")
|
|
V.Add("@" & p.Scalarvariable)
|
|
End If
|
|
Next
|
|
|
|
Return "INSERT INTO tblVERAG_CustomsDeclarations (" &
|
|
String.Join(",", C) &
|
|
") VALUES (" &
|
|
String.Join(",", V) &
|
|
");"
|
|
|
|
End Function
|
|
|
|
|
|
|
|
End Class
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
' ==========================================================================
|
|
' ITEM
|
|
' ==========================================================================
|
|
|
|
Public Class cVERAG_Zollanmeldung_Item
|
|
|
|
Public Property zaItem_Id As Integer
|
|
Public Property zaItem_DeclarationId As Integer
|
|
|
|
Public Property zaItem_Line As Integer
|
|
Public Property zaItem_ArticleCode As String
|
|
Public Property zaItem_HSCode As String
|
|
Public Property zaItem_Description As String
|
|
|
|
Public Property zaItem_Qty As Decimal?
|
|
Public Property zaItem_Unit As String
|
|
Public Property zaItem_Gross As Decimal?
|
|
Public Property zaItem_Net As Decimal?
|
|
|
|
Public Property zaItem_InvoiceValue As Decimal?
|
|
Public Property zaItem_CustomsValue As Decimal?
|
|
Public Property zaItem_StatisticalValue As Decimal?
|
|
Public Property zaItem_Currency As String
|
|
|
|
Public Property zaItem_OriginCountry As String
|
|
Public Property zaItem_PreferenceCode As String
|
|
|
|
Private SQL As New VERAG_PROG_ALLGEMEIN.SQL
|
|
|
|
|
|
|
|
'--- PARAMLIST ----------------------------------------------------------
|
|
|
|
Public Function getParameterList() As List(Of VERAG_PROG_ALLGEMEIN.SQLVariable)
|
|
|
|
Dim L As New List(Of VERAG_PROG_ALLGEMEIN.SQLVariable)
|
|
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaItem_Id", zaItem_Id,, True, True))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaItem_DeclarationId", zaItem_DeclarationId))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaItem_Line", zaItem_Line))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaItem_ArticleCode", zaItem_ArticleCode))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaItem_HSCode", zaItem_HSCode))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaItem_Description", zaItem_Description))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaItem_Qty", zaItem_Qty))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaItem_Unit", zaItem_Unit))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaItem_Gross", zaItem_Gross))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaItem_Net", zaItem_Net))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaItem_InvoiceValue", zaItem_InvoiceValue))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaItem_CustomsValue", zaItem_CustomsValue))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaItem_StatisticalValue", zaItem_StatisticalValue))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaItem_Currency", zaItem_Currency))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaItem_OriginCountry", zaItem_OriginCountry))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaItem_PreferenceCode", zaItem_PreferenceCode))
|
|
|
|
Return L
|
|
|
|
End Function
|
|
|
|
|
|
|
|
'--- SAVE ---------------------------------------------------------------
|
|
|
|
Public Function SAVE() As Boolean
|
|
|
|
Dim list = getParameterList()
|
|
|
|
Dim sqlstr As String =
|
|
" BEGIN TRAN " &
|
|
" IF EXISTS(SELECT 1 FROM tblVERAG_CustomsDeclarations_Items WHERE zaItem_Id=@zaItem_Id) " &
|
|
" BEGIN " & getUpdateCmd() & " END " &
|
|
" ELSE BEGIN " & getInsertCmd() & " END " &
|
|
" COMMIT TRAN "
|
|
|
|
zaItem_Id = SQL.doSQLVarListID(zaItem_Id, sqlstr, "VERAG", , list)
|
|
Return zaItem_Id > 0
|
|
|
|
End Function
|
|
|
|
|
|
|
|
'--- LOAD FROM DATAREADER ----------------------------------------------
|
|
|
|
Public Sub LOAD_FROM_READER(dr As SqlDataReader)
|
|
|
|
For Each p In getParameterList()
|
|
Dim prop = Me.GetType().GetProperty(p.Scalarvariable)
|
|
If prop IsNot Nothing Then
|
|
If IsDBNull(dr(p.Text)) Then
|
|
prop.SetValue(Me, Nothing)
|
|
Else
|
|
prop.SetValue(Me, dr(p.Text))
|
|
End If
|
|
End If
|
|
Next
|
|
|
|
End Sub
|
|
|
|
|
|
|
|
|
|
'--- SQL HELPERS --------------------------------------------------------
|
|
|
|
Private Function getUpdateCmd() As String
|
|
|
|
Dim cols As New List(Of String)
|
|
|
|
For Each p In getParameterList()
|
|
If Not p.isPrimaryParam Then
|
|
cols.Add("[" & p.Text & "]=@" & p.Scalarvariable)
|
|
End If
|
|
Next
|
|
|
|
Return "UPDATE tblVERAG_CustomsDeclarations_Items SET " &
|
|
String.Join(",", cols) &
|
|
" WHERE zaItem_Id=@zaItem_Id"
|
|
|
|
End Function
|
|
|
|
|
|
|
|
Private Function getInsertCmd() As String
|
|
|
|
Dim C As New List(Of String)
|
|
Dim V As New List(Of String)
|
|
|
|
For Each p In getParameterList()
|
|
If Not p.isPrimaryParam Then
|
|
C.Add("[" & p.Text & "]")
|
|
V.Add("@" & p.Scalarvariable)
|
|
End If
|
|
Next
|
|
|
|
Return "INSERT INTO tblVERAG_CustomsDeclarations_Items (" &
|
|
String.Join(",", C) &
|
|
") VALUES (" &
|
|
String.Join(",", V) &
|
|
")"
|
|
|
|
End Function
|
|
|
|
|
|
|
|
End Class
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
' ==========================================================================
|
|
' DUTY
|
|
' ==========================================================================
|
|
|
|
Public Class cVERAG_Zollanmeldung_Duty
|
|
|
|
Public Property zaDuty_Id As Integer
|
|
Public Property zaDuty_DeclarationId As Integer
|
|
Public Property zaDuty_ItemId As Integer?
|
|
|
|
Public Property zaDuty_Type As String
|
|
Public Property zaDuty_BaseAmountForeign As Decimal?
|
|
Public Property zaDuty_BaseCurrency As String
|
|
Public Property zaDuty_BaseAmountEUR As Decimal?
|
|
|
|
Public Property zaDuty_Rate As String
|
|
Public Property zaDuty_AmountForeign As Decimal?
|
|
Public Property zaDuty_AmountCurrency As String
|
|
Public Property zaDuty_AmountEUR As Decimal?
|
|
|
|
Public Property zaDuty_DeferredFlag As Boolean?
|
|
Public Property zaDuty_DeferredAccount As String
|
|
|
|
Public Property zaDuty_MeasureId As String
|
|
Public Property zaDuty_Indicator As String
|
|
Public Property zaDuty_Remarks As String
|
|
|
|
Private SQL As New VERAG_PROG_ALLGEMEIN.SQL
|
|
|
|
|
|
|
|
'--- PARAMLIST ----------------------------------------------------------
|
|
|
|
Public Function getParameterList() As List(Of VERAG_PROG_ALLGEMEIN.SQLVariable)
|
|
|
|
Dim L As New List(Of VERAG_PROG_ALLGEMEIN.SQLVariable)
|
|
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDuty_Id", zaDuty_Id,, True, True))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDuty_DeclarationId", zaDuty_DeclarationId))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDuty_ItemId", zaDuty_ItemId))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDuty_Type", zaDuty_Type))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDuty_BaseAmountForeign", zaDuty_BaseAmountForeign))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDuty_BaseCurrency", zaDuty_BaseCurrency))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDuty_BaseAmountEUR", zaDuty_BaseAmountEUR))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDuty_Rate", zaDuty_Rate))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDuty_AmountForeign", zaDuty_AmountForeign))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDuty_AmountCurrency", zaDuty_AmountCurrency))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDuty_AmountEUR", zaDuty_AmountEUR))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDuty_DeferredFlag", zaDuty_DeferredFlag))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDuty_DeferredAccount", zaDuty_DeferredAccount))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDuty_MeasureId", zaDuty_MeasureId))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDuty_Indicator", zaDuty_Indicator))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDuty_Remarks", zaDuty_Remarks))
|
|
|
|
Return L
|
|
|
|
End Function
|
|
|
|
|
|
|
|
'--- SAVE ---------------------------------------------------------------
|
|
|
|
Public Function SAVE() As Boolean
|
|
|
|
Dim list = getParameterList()
|
|
|
|
Dim sqlstr As String =
|
|
" BEGIN TRAN " &
|
|
" IF EXISTS(SELECT 1 FROM tblVERAG_CustomsDeclarations_Duties WHERE zaDuty_Id=@zaDuty_Id) " &
|
|
" BEGIN " & getUpdateCmd() & " END " &
|
|
" ELSE BEGIN " & getInsertCmd() & " END " &
|
|
" COMMIT TRAN "
|
|
|
|
zaDuty_Id = SQL.doSQLVarListID(zaDuty_Id, sqlstr, "VERAG", , list)
|
|
Return zaDuty_Id > 0
|
|
|
|
End Function
|
|
|
|
|
|
|
|
'--- LOAD FROM DATAREADER ----------------------------------------------
|
|
|
|
Public Sub LOAD_FROM_READER(dr As SqlDataReader)
|
|
|
|
For Each p In getParameterList()
|
|
Dim prop = Me.GetType().GetProperty(p.Scalarvariable)
|
|
If prop IsNot Nothing Then
|
|
If IsDBNull(dr(p.Text)) Then
|
|
prop.SetValue(Me, Nothing)
|
|
Else
|
|
prop.SetValue(Me, dr(p.Text))
|
|
End If
|
|
End If
|
|
Next
|
|
|
|
End Sub
|
|
|
|
|
|
|
|
|
|
'--- SQL HELPERS --------------------------------------------------------
|
|
|
|
Private Function getUpdateCmd() As String
|
|
|
|
Dim cols As New List(Of String)
|
|
|
|
For Each p In getParameterList()
|
|
If Not p.isPrimaryParam Then
|
|
cols.Add("[" & p.Text & "]=@" & p.Scalarvariable)
|
|
End If
|
|
Next
|
|
|
|
Return "UPDATE tblVERAG_CustomsDeclarations_Duties SET " &
|
|
String.Join(",", cols) &
|
|
" WHERE zaDuty_Id=@zaDuty_Id"
|
|
|
|
End Function
|
|
|
|
|
|
|
|
Private Function getInsertCmd() As String
|
|
|
|
Dim C As New List(Of String)
|
|
Dim V As New List(Of String)
|
|
|
|
For Each p In getParameterList()
|
|
If Not p.isPrimaryParam Then
|
|
C.Add("[" & p.Text & "]")
|
|
V.Add("@" & p.Scalarvariable)
|
|
End If
|
|
Next
|
|
|
|
Return "INSERT INTO tblVERAG_CustomsDeclarations_Duties (" &
|
|
String.Join(",", C) &
|
|
") VALUES (" &
|
|
String.Join(",", V) &
|
|
")"
|
|
|
|
End Function
|
|
|
|
|
|
|
|
End Class
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
' ==========================================================================
|
|
' DOCUMENT (HEADER)
|
|
' ==========================================================================
|
|
|
|
Public Class cVERAG_Zollanmeldung_Document
|
|
|
|
Public Property zaDoc_Id As Integer
|
|
Public Property zaDoc_DeclarationId As Integer
|
|
|
|
Public Property zaDoc_Level As String = "H"
|
|
|
|
Public Property zaDoc_Code As String
|
|
Public Property zaDoc_Type As String
|
|
Public Property zaDoc_Reference As String
|
|
Public Property zaDoc_IssueDate As Date?
|
|
Public Property zaDoc_IssueCountry As String
|
|
Public Property zaDoc_IssueTIN As String
|
|
Public Property zaDoc_UID As String
|
|
Public Property zaDoc_ValidTo As Date?
|
|
Public Property zaDoc_Remarks As String
|
|
|
|
Private SQL As New VERAG_PROG_ALLGEMEIN.SQL
|
|
|
|
|
|
|
|
|
|
'--- PARAMLIST ----------------------------------------------------------
|
|
|
|
Public Function getParameterList() As List(Of VERAG_PROG_ALLGEMEIN.SQLVariable)
|
|
|
|
Dim L As New List(Of VERAG_PROG_ALLGEMEIN.SQLVariable)
|
|
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_Id", zaDoc_Id,, True, True))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_DeclarationId", zaDoc_DeclarationId))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_Level", zaDoc_Level))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_Code", zaDoc_Code))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_Type", zaDoc_Type))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_Reference", zaDoc_Reference))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_IssueDate", zaDoc_IssueDate))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_IssueCountry", zaDoc_IssueCountry))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_IssueTIN", zaDoc_IssueTIN))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_UID", zaDoc_UID))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_ValidTo", zaDoc_ValidTo))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_Remarks", zaDoc_Remarks))
|
|
|
|
Return L
|
|
|
|
End Function
|
|
|
|
|
|
|
|
'--- SAVE ---------------------------------------------------------------
|
|
|
|
Public Function SAVE() As Boolean
|
|
|
|
Dim list = getParameterList()
|
|
|
|
Dim sqlstr As String =
|
|
" BEGIN TRAN " &
|
|
" IF EXISTS(SELECT 1 FROM tblVERAG_CustomsDeclarations_Documents WHERE zaDoc_Id=@zaDoc_Id) " &
|
|
" BEGIN " & getUpdateCmd() & " END " &
|
|
" ELSE BEGIN " & getInsertCmd() & " END " &
|
|
" COMMIT TRAN "
|
|
|
|
zaDoc_Id = SQL.doSQLVarListID(zaDoc_Id, sqlstr, "VERAG", , list)
|
|
Return zaDoc_Id > 0
|
|
|
|
End Function
|
|
|
|
|
|
|
|
'--- LOAD FROM DATAREADER ----------------------------------------------
|
|
|
|
Public Sub LOAD_FROM_READER(dr As SqlDataReader)
|
|
|
|
For Each p In getParameterList()
|
|
Dim prop = Me.GetType().GetProperty(p.Scalarvariable)
|
|
If prop IsNot Nothing Then
|
|
If IsDBNull(dr(p.Text)) Then
|
|
prop.SetValue(Me, Nothing)
|
|
Else
|
|
prop.SetValue(Me, dr(p.Text))
|
|
End If
|
|
End If
|
|
Next
|
|
|
|
End Sub
|
|
|
|
|
|
|
|
|
|
'--- SQL HELPERS --------------------------------------------------------
|
|
|
|
Private Function getUpdateCmd() As String
|
|
|
|
Dim cols As New List(Of String)
|
|
|
|
For Each p In getParameterList()
|
|
If Not p.isPrimaryParam Then
|
|
cols.Add("[" & p.Text & "]=@" & p.Scalarvariable)
|
|
End If
|
|
Next
|
|
|
|
Return "UPDATE tblVERAG_CustomsDeclarations_Documents SET " &
|
|
String.Join(",", cols) &
|
|
" WHERE zaDoc_Id=@zaDoc_Id"
|
|
|
|
End Function
|
|
|
|
|
|
|
|
Private Function getInsertCmd() As String
|
|
|
|
Dim C As New List(Of String)
|
|
Dim V As New List(Of String)
|
|
|
|
For Each p In getParameterList()
|
|
If Not p.isPrimaryParam Then
|
|
C.Add("[" & p.Text & "]")
|
|
V.Add("@" & p.Scalarvariable)
|
|
End If
|
|
Next
|
|
|
|
Return "INSERT INTO tblVERAG_CustomsDeclarations_Documents (" &
|
|
String.Join(",", C) &
|
|
") VALUES (" &
|
|
String.Join(",", V) &
|
|
")"
|
|
|
|
End Function
|
|
|
|
|
|
|
|
End Class
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
' ==========================================================================
|
|
' DOCUMENT (ITEM)
|
|
' ==========================================================================
|
|
|
|
Public Class cVERAG_Zollanmeldung_ItemDocument
|
|
|
|
Public Property zaDoc_Id As Integer
|
|
Public Property zaDoc_DeclarationId As Integer?
|
|
Public Property zaDoc_ItemId As Integer?
|
|
|
|
Public Property zaDoc_Level As String = "I"
|
|
|
|
Public Property zaDoc_Code As String
|
|
Public Property zaDoc_Type As String
|
|
Public Property zaDoc_Reference As String
|
|
Public Property zaDoc_IssueDate As Date?
|
|
Public Property zaDoc_IssueCountry As String
|
|
Public Property zaDoc_IssueTIN As String
|
|
Public Property zaDoc_UID As String
|
|
Public Property zaDoc_ValidTo As Date?
|
|
Public Property zaDoc_Remarks As String
|
|
|
|
Private SQL As New VERAG_PROG_ALLGEMEIN.SQL
|
|
|
|
|
|
|
|
|
|
'--- PARAMLIST ----------------------------------------------------------
|
|
|
|
Public Function getParameterList() As List(Of VERAG_PROG_ALLGEMEIN.SQLVariable)
|
|
|
|
Dim L As New List(Of VERAG_PROG_ALLGEMEIN.SQLVariable)
|
|
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_Id", zaDoc_Id,, True, True))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_DeclarationId", zaDoc_DeclarationId))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_ItemId", zaDoc_ItemId))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_Level", zaDoc_Level))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_Code", zaDoc_Code))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_Type", zaDoc_Type))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_Reference", zaDoc_Reference))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_IssueDate", zaDoc_IssueDate))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_IssueCountry", zaDoc_IssueCountry))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_IssueTIN", zaDoc_IssueTIN))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_UID", zaDoc_UID))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_ValidTo", zaDoc_ValidTo))
|
|
L.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("zaDoc_Remarks", zaDoc_Remarks))
|
|
|
|
Return L
|
|
|
|
End Function
|
|
|
|
|
|
|
|
'--- SAVE ---------------------------------------------------------------
|
|
|
|
Public Function SAVE() As Boolean
|
|
|
|
Dim list = getParameterList()
|
|
|
|
Dim sqlstr As String =
|
|
" BEGIN TRAN " &
|
|
" IF EXISTS(SELECT 1 FROM tblVERAG_CustomsDeclarations_Documents WHERE zaDoc_Id=@zaDoc_Id) " &
|
|
" BEGIN " & getUpdateCmd() & " END " &
|
|
" ELSE BEGIN " & getInsertCmd() & " END " &
|
|
" COMMIT TRAN "
|
|
|
|
zaDoc_Id = SQL.doSQLVarListID(zaDoc_Id, sqlstr, "VERAG", , list)
|
|
Return zaDoc_Id > 0
|
|
|
|
End Function
|
|
|
|
|
|
|
|
'--- LOAD FROM DATAREADER ----------------------------------------------
|
|
|
|
Public Sub LOAD_FROM_READER(dr As SqlDataReader)
|
|
|
|
For Each p In getParameterList()
|
|
Dim prop = Me.GetType().GetProperty(p.Scalarvariable)
|
|
If prop IsNot Nothing Then
|
|
If IsDBNull(dr(p.Text)) Then
|
|
prop.SetValue(Me, Nothing)
|
|
Else
|
|
prop.SetValue(Me, dr(p.Text))
|
|
End If
|
|
End If
|
|
Next
|
|
|
|
End Sub
|
|
|
|
|
|
|
|
|
|
'--- SQL HELPERS --------------------------------------------------------
|
|
|
|
Private Function getUpdateCmd() As String
|
|
|
|
Dim cols As New List(Of String)
|
|
|
|
For Each p In getParameterList()
|
|
If Not p.isPrimaryParam Then
|
|
cols.Add("[" & p.Text & "]=@" & p.Scalarvariable)
|
|
End If
|
|
Next
|
|
|
|
Return "UPDATE tblVERAG_CustomsDeclarations_Documents SET " &
|
|
String.Join(",", cols) &
|
|
" WHERE zaDoc_Id=@zaDoc_Id"
|
|
|
|
End Function
|
|
|
|
|
|
|
|
Private Function getInsertCmd() As String
|
|
|
|
Dim C As New List(Of String)
|
|
Dim V As New List(Of String)
|
|
|
|
For Each p In getParameterList()
|
|
If Not p.isPrimaryParam Then
|
|
C.Add("[" & p.Text & "]")
|
|
V.Add("@" & p.Scalarvariable)
|
|
End If
|
|
Next
|
|
|
|
Return "INSERT INTO tblVERAG_CustomsDeclarations_Documents (" &
|
|
String.Join(",", C) &
|
|
") VALUES (" &
|
|
String.Join(",", V) &
|
|
")"
|
|
|
|
End Function
|
|
|
|
|
|
End Class |