
Imports System.Data.SqlClient
Imports System.IO
Imports System.Net
Imports System.Reflection

Public Class cIDS


    Property transaction_id As Integer
    Property YearMonthDay As Object = Nothing
    Property Paymentsummarynumber As Object = Nothing
    Property CustomerCode As Object = Nothing
    Property CustomerName As Object = Nothing
    Property OutletCountryCode As Object = Nothing
    Property OutletCountry As Object = Nothing
    Property OutletCode As Object = Nothing
    Property OutletName As Object = Nothing
    Property ProductTypeCode As Object = Nothing
    Property TransactionVolume As Object = Nothing
    Property AmminclVAT As Object = Nothing
    Property TotalNetAmount As Object = Nothing
    Property VATAmount As Object = Nothing
    Property avpriceexclVAT As Object = Nothing
    Property Rechenwert As Object = Nothing
    Property Kz As Object = Nothing
    Property Rabattbetrag As Object = Nothing
    Property RabattbetragProLiter As Object = Nothing
    Property RabattbetragOrig As Object = Nothing
    Property RabattbetragProLiterOrig As Object = Nothing
    Property Zeitstempel As Object = Nothing
    Property Sachbearbeiter As Object = Nothing
    Property KdNrVERAG As Object = Nothing
    Property CardNumber As Object = Nothing
    Property EmissionNumber As Object = Nothing
    Property CardComments As Object = Nothing
    Property CalendarDate As Object = Nothing
    Property TransactionHHMM As Object = Nothing
    Property UStVAn_ID As Object = Nothing
    Property VRNumber As Object = Nothing
    Property OBONumber As Object = Nothing
    Property charged As Boolean = False
    Property chargedDatetime As Object = Nothing


    Public hasEntry = False

    Dim SQL As New SQL

    Sub New()

    End Sub

    Sub New(transaction_id)
        Me.transaction_id = transaction_id
        LOADID()
    End Sub

    Sub New(YearMonthDay, Paymentsummarynumber, CustomerCode, OutletCountryCode, OutletCode, ProductTypeCode)
        Me.YearMonthDay = YearMonthDay
        Me.Paymentsummarynumber = Paymentsummarynumber
        Me.CustomerCode = CustomerCode
        Me.OutletCountryCode = OutletCountryCode
        Me.OutletCode = OutletCode
        Me.ProductTypeCode = ProductTypeCode
        LOAD()

    End Sub


    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("transaction_id", transaction_id,, True))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("YearMonthDay", YearMonthDay))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Paymentsummarynumber", Paymentsummarynumber))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("CustomerCode", CustomerCode))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("CustomerName", CustomerName))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("OutletCountryCode", OutletCountryCode))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("OutletCountry", OutletCountry))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("OutletCode", OutletCode))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("OutletName", OutletName))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("ProductTypeCode", ProductTypeCode))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("TransactionVolume", TransactionVolume))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("AmminclVAT", AmminclVAT))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("TotalNetAmount", TotalNetAmount))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("VATAmount", VATAmount))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("avpriceexclVAT", avpriceexclVAT))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Rechenwert", Rechenwert))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Kz", Kz))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Rabattbetrag", Rabattbetrag))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("RabattbetragProLiter", RabattbetragProLiter))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("RabattbetragOrig", RabattbetragOrig))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("RabattbetragProLiterOrig", RabattbetragProLiterOrig))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Zeitstempel", Zeitstempel))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Sachbearbeiter", Sachbearbeiter))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("KdNrVERAG", KdNrVERAG))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("CardNumber", CardNumber))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("EmissionNumber", EmissionNumber))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("CardComments", CardComments))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("CalendarDate", CalendarDate))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("TransactionHHMM", TransactionHHMM))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("UStVAn_ID", UStVAn_ID))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("VRNumber", VRNumber))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("OBONumber", OBONumber))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("charged", charged))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("chargedDatetime", chargedDatetime))
        Return list
    End Function





    Public Function SAVE() As Boolean
        Dim list As List(Of VERAG_PROG_ALLGEMEIN.SQLVariable) = getParameterList()

        Dim sqlstr = " BEGIN TRAN IF EXISTS(SELECT * FROM tblIDSTransactionsNew WHERE YearMonthDay=@YearMonthDay AND Paymentsummarynumber=@Paymentsummarynumber AND CustomerCode=@CustomerCode AND OutletCountryCode=@OutletCountryCode AND OutletCode=@OutletCode AND ProductTypeCode=@ProductTypeCode and isnull(charged,0) = 0) " &
                           " BEGIN " & getUpdateCmd() & " END " &
                           " Else " &
                           " BEGIN " & getInsertCmd() & " END " &
                           " commit tran "

        Return SQL.doSQLVarList(sqlstr, "FMZOLL", , list)
    End Function

    Public Sub LOADID()
        Try
            hasEntry = False
            Using conn As SqlConnection = SQL.GetNewOpenConnectionFMZOLL()
                Using cmd As New SqlCommand("SELECT * FROM tblIDSTransactionsNew WHERE transaction_id=@transaction_id  ", conn)
                    cmd.Parameters.AddWithValue("@transaction_id", transaction_id)
                    Dim dr = cmd.ExecuteReader()
                    If dr.Read Then
                        For Each li In getParameterList()
                            Dim propInfo As PropertyInfo = Me.GetType.GetProperty(li.Scalarvariable)

                            If dr.Item(li.Text) Is DBNull.Value Then
                                propInfo.SetValue(Me, Nothing)
                            Else
                                propInfo.SetValue(Me, dr.Item(li.Text))
                            End If
                        Next
                        hasEntry = True
                    End If
                    dr.Close()
                End Using
            End Using
        Catch ex As Exception
            VERAG_PROG_ALLGEMEIN.cErrorHandler.ERR(ex.Message, ex.StackTrace, System.Reflection.MethodInfo.GetCurrentMethod.Name)
        End Try
    End Sub


    Public Sub LOAD()
        Try
            hasEntry = False
            Using conn As SqlConnection = SQL.GetNewOpenConnectionFMZOLL()
                Using cmd As New SqlCommand("SELECT * FROM tblIDSTransactionsNew WHERE YearMonthDay=@YearMonthDay AND Paymentsummarynumber=@Paymentsummarynumber AND CustomerCode=@CustomerCode AND OutletCountryCode=@OutletCountryCode AND OutletCode=@OutletCode AND ProductTypeCode=@ProductTypeCode", conn)
                    cmd.Parameters.AddWithValue("@YearMonthDay", YearMonthDay)
                    cmd.Parameters.AddWithValue("@Paymentsummarynumber", Paymentsummarynumber)
                    cmd.Parameters.AddWithValue("@CustomerCode", CustomerCode)
                    cmd.Parameters.AddWithValue("@OutletCountryCode", OutletCountryCode)
                    cmd.Parameters.AddWithValue("@OutletCode", OutletCode)
                    cmd.Parameters.AddWithValue("@ProductTypeCode", ProductTypeCode)
                    Dim dr = cmd.ExecuteReader()
                    If dr.Read Then
                        For Each li In getParameterList()
                            Dim propInfo As PropertyInfo = Me.GetType.GetProperty(li.Scalarvariable)

                            If dr.Item(li.Text) Is DBNull.Value Then
                                propInfo.SetValue(Me, Nothing)
                            Else
                                propInfo.SetValue(Me, dr.Item(li.Text))
                            End If
                        Next
                        hasEntry = True
                    End If
                    dr.Close()
                End Using
            End Using
        Catch ex As Exception
            VERAG_PROG_ALLGEMEIN.cErrorHandler.ERR(ex.Message, ex.StackTrace, System.Reflection.MethodInfo.GetCurrentMethod.Name)
        End Try
    End Sub



    Public Function getUpdateCmd() As String
        Try
            Dim list As List(Of VERAG_PROG_ALLGEMEIN.SQLVariable) = getParameterList()

            Dim str As String = ""
            For Each i In list
                If Not i.isPrimaryParam Then
                    str &= "[" & i.Text & "] = @" & i.Scalarvariable & "," '.Replace("-", "").Replace(" ", "") & ","
                End If
            Next
            str = str.Substring(0, str.Length - 1) 'wg. ','
            Return (" UPDATE [tblIDSTransactionsNew] SET " & str & " WHERE YearMonthDay=@YearMonthDay AND Paymentsummarynumber=@Paymentsummarynumber AND CustomerCode=@CustomerCode AND OutletCountryCode=@OutletCountryCode AND OutletCode=@OutletCode AND ProductTypeCode=@ProductTypeCode AND isnull(charged,0) = 0  ")

        Catch ex As Exception
            VERAG_PROG_ALLGEMEIN.cErrorHandler.ERR(ex.Message, ex.StackTrace, System.Reflection.MethodInfo.GetCurrentMethod.Name)
        End Try
        Return ""
    End Function


    Public Function getInsertCmd() As String
        Try
            Dim list As List(Of VERAG_PROG_ALLGEMEIN.SQLVariable) = getParameterList()
            Dim str As String = ""
            Dim values As String = ""
            For Each i In list
                If Not i.isPrimaryParam Then
                    str &= "[" & i.Text & "],"
                    values &= "@" & i.Scalarvariable & "," '.Replace("-", "").Replace(" ", "") & ","
                End If
            Next
            str = str.Substring(0, str.Length - 1) 'wg. ','
            values = values.Substring(0, values.Length - 1) 'wg. ','
            Return (" INSERT INTO tblIDSTransactionsNew  (" & str & ") VALUES(" & values & ")  ")
        Catch ex As Exception
            VERAG_PROG_ALLGEMEIN.cErrorHandler.ERR(ex.Message, ex.StackTrace, System.Reflection.MethodInfo.GetCurrentMethod.Name)
        End Try
        Return ""
    End Function

    Public Function readAndSaveIDS(objFileRead As StreamReader, fi As FileInfo, cnt As Integer) As Boolean


        Dim lngRecordCount As Long = 0

        objFileRead.DiscardBufferedData()
        objFileRead.BaseStream.Seek(0, System.IO.SeekOrigin.Begin)


        Do While (objFileRead.Peek() > -1)
            Dim currentRow As String()
            currentRow = objFileRead.ReadLine().Split(",")

            lngRecordCount = lngRecordCount + 1

            If lngRecordCount = 1 Then
                If Not (isleernothing((currentRow(0))) = "Year Month Day" AndAlso isleernothing((currentRow(14))) = "OBO Number") Then
                    Return False
                End If


            Else
                Try


                    Dim ymd = isleernothing((currentRow(0)))
                    Dim cc = isleernothing((currentRow(1)))
                    Dim pyn = isleernothing((currentRow(2)))
                    Dim occ = isleernothing((currentRow(5)))
                    Dim oc = isleernothing((currentRow(6)))
                    Dim ptc = isleernothing((currentRow(7)))
                    Dim ids As New cIDS(ymd, pyn, cc, occ, oc, ptc)

                    Dim avprive = isleernothing((currentRow(12)))

                    With ids
                        .YearMonthDay = isleernothing((currentRow(0)))
                        .CustomerCode = isleernothing((currentRow(1)))
                        .Paymentsummarynumber = isleernothing((currentRow(2)))
                        .CustomerName = isleernothing((currentRow(3)))
                        .OutletCountry = isleernothing((currentRow(4)))
                        .OutletCountryCode = isleernothing((currentRow(5)))
                        .OutletCode = isleernothing((currentRow(6)))
                        .ProductTypeCode = isleernothing((currentRow(7)))
                        .TransactionVolume = isleernothing((currentRow(8)))
                        .AmminclVAT = isleernothing((currentRow(9)))
                        .TotalNetAmount = isleernothing((currentRow(10)))
                        .VATAmount = isleernothing((currentRow(11)))
                        .avpriceexclVAT = IIf(avprive <> "" AndAlso IsNumeric(avprive), avprive, 0)
                        .VRNumber = isleernothing((currentRow(13)))
                        .OBONumber = isleernothing((currentRow(14)))

                        Console.WriteLine("IDS: " & isleernothing((currentRow(1))) & " - " & lngRecordCount & " - " & isleernothing((currentRow(10))))
                        .SAVE()

                    End With

                Catch ex As Exception
                    VERAG_PROG_ALLGEMEIN.cErrorHandler.ERR(ex.Message, ex.StackTrace, System.Reflection.MethodInfo.GetCurrentMethod.Name)
                End Try

            End If

        Loop
        cnt = CInt(lngRecordCount)
        objFileRead.Close()
        Return True



    End Function

    Function isleernothing(s) As Object
        If s Is DBNull.Value Then Return Nothing
        If s.ToString.Trim = "" Then Return Nothing
        Return s.trim
    End Function




    Public VERARBEITUNG_PFAD
    Public ARCHIV_PFAD
    Public ERROR_PFAD
    Public ZIEL_PFAD
    Public FTP_PFAD
    Public ANLAGEN
    Public ARCHIV
    Public API_STRING As String
    Public API As New DataTable

    Dim Dateiname = ""

    Public Function initImportPfade(programName As String) As Boolean


        ERROR_PFAD = cIDS.Paramter.GET_PARAM_ByName("ERROR_PFAD", VERAG_PROG_ALLGEMEIN.cAllgemein.TESTSYSTEM)
        ARCHIV_PFAD = cIDS.Paramter.GET_PARAM_ByName("ARCHIV_PFAD", VERAG_PROG_ALLGEMEIN.cAllgemein.TESTSYSTEM)
        VERARBEITUNG_PFAD = cIDS.Paramter.GET_PARAM_ByName("VERARBEITUNG_PFAD", VERAG_PROG_ALLGEMEIN.cAllgemein.TESTSYSTEM)
        ZIEL_PFAD = cIDS.Paramter.GET_PARAM_ByName("ZIEL_PFAD", VERAG_PROG_ALLGEMEIN.cAllgemein.TESTSYSTEM)
        ANLAGEN = cIDS.Paramter.GET_PARAM_ByName("ANLAGEN", VERAG_PROG_ALLGEMEIN.cAllgemein.TESTSYSTEM)
        ARCHIV = cIDS.Paramter.GET_PARAM_ByName("ARCHIV", VERAG_PROG_ALLGEMEIN.cAllgemein.TESTSYSTEM)

        If Not System.IO.Directory.Exists(VERARBEITUNG_PFAD) AndAlso VERARBEITUNG_PFAD <> "" Then
            System.IO.Directory.CreateDirectory(VERARBEITUNG_PFAD)
        ElseIf VERARBEITUNG_PFAD = "" Then
            MsgBox("Verabreitungspfad nicht gesetzt!")
        End If

        Return Paramter.getFTPConenction(API_STRING, API, programName)

    End Function

    Public Shared Function GET_Antraege_IDS(ByRef dt As DataTable, von As Date, bis As Date, KundenNr As Integer, LandKZ As String, Optional Archiv As Boolean = False) As Boolean
        Try
            Dim SQL As New VERAG_PROG_ALLGEMEIN.SQL

            Using conn As SqlConnection = SQL.GetNewOpenConnectionFMZOLL()

                Using cmd As New SqlCommand("SELECT 
                                            tblIDSTransactionsNew.KdNrVERAG as Kundennummer,
                                            ISNULL(tblIDSTransactionsNew.[OBONumber],ISNULL(tblIDSTransactionsNew.[VRNumber], tblIDSTransactionsNew.[Paymentsummarynumber])) AS Rechnungsnummer,
                                            tblIDSTransactionsNew.[YearMonthDay] AS Rechnungsdatum,                                            
                                            SUM(tblIDSTransactionsNew.[VATAmount]) AS Steuerbetrag
                                        FROM 
                                            
                                            tblIDSTransactionsNew 
                                        INNER JOIN 
                                            tbl_IDS_Länder 
                                            ON tblIDSTransactionsNew.[OutletCountryCode] = tbl_IDS_Länder.OutletCountryCode
                                        WHERE 
                                            tblIDSTransactionsNew.[YearMonthDay] BETWEEN @von AND @bis 
                                            AND tblIDSTransactionsNew.KdNrVERAG = @AdressenNr 
                                            AND tbl_IDS_Länder.[Lieferland_ISO2] = @LandKZ
                                            AND tblIDSTransactionsNew.[VATAmount] <> 0 
                                            " & IIf(Archiv, " ", " AND isnull(UStVAn_ID,0) = 0 ") & "
                                        GROUP BY 
                                            tblIDSTransactionsNew.KdNrVERAG,
                                            tblIDSTransactionsNew.[YearMonthDay], 
                                            ISNULL(tblIDSTransactionsNew.[OBONumber], 
                                            ISNULL(tblIDSTransactionsNew.[VRNumber], tblIDSTransactionsNew.[Paymentsummarynumber]))
                                        ORDER BY 
                                            tblIDSTransactionsNew.KdNrVERAG,
                                            tblIDSTransactionsNew.[YearMonthDay], 
                                            ISNULL(tblIDSTransactionsNew.[OBONumber], 
                                            ISNULL(tblIDSTransactionsNew.[VRNumber], tblIDSTransactionsNew.[Paymentsummarynumber]));", conn)

                    cmd.Parameters.AddWithValue("@LandKZ", LandKZ)
                    cmd.Parameters.AddWithValue("@von", von)
                    cmd.Parameters.AddWithValue("@bis", bis)
                    cmd.Parameters.AddWithValue("@AdressenNr", KundenNr)

                    Dim dr = cmd.ExecuteReader()
                    dt.Load(dr)
                    'While dr.Read
                    '    dt.Load(dr)
                    'End While
                    dr.Close()

                End Using
            End Using
            Return True
        Catch ex As Exception
            VERAG_PROG_ALLGEMEIN.cErrorHandler.ERR(ex.Message, ex.StackTrace, System.Reflection.MethodInfo.GetCurrentMethod.Name)
        End Try
        Return False

    End Function

    Public Shared Function UPDATE_ARCHIV(reDat As Date, reNr As String, ids_kdNr As Integer, country As String, UStVAn_ID As Integer) As Boolean
        Try
            Dim SQL As New VERAG_PROG_ALLGEMEIN.SQL

            Dim list As New List(Of VERAG_PROG_ALLGEMEIN.SQLVariable)
            list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("reDat", reDat))
            list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("reNr", reNr))
            list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("ids_kdNr", ids_kdNr))
            list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("country", country))
            list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("UStVAn_ID", UStVAn_ID))


            Return SQL.doSQLVarList("update tblIDSTransactionsNew set  UStVAn_ID = @UStVAn_ID 
                        FROM tblIDSTransactionsNew
                        INNER JOIN tbl_IDS_Länder 
                            ON tblIDSTransactionsNew.[OutletCountryCode] = tbl_IDS_Länder.OutletCountryCode
                        WHERE 
                            tblIDSTransactionsNew.[YearMonthDay] = @reDat AND ISNULL(tblIDSTransactionsNew.[OBONumber], ISNULL(tblIDSTransactionsNew.[VRNumber], tblIDSTransactionsNew.[Paymentsummarynumber])) = @reNr
                            AND tblIDSTransactionsNew.KdNrVERAG = @ids_kdNr
                            AND tbl_IDS_Länder.[Lieferland_ISO2] = @country", "FMZOLL",, list)
        Catch ex As Exception
            VERAG_PROG_ALLGEMEIN.cErrorHandler.ERR(ex.Message, ex.StackTrace, System.Reflection.MethodInfo.GetCurrentMethod.Name)
        End Try
        Return ""
    End Function


    Public Class Paramter

        Shared apiSettingsloaded As Boolean = False
        Shared SQL As New VERAG_PROG_ALLGEMEIN.SQL
        Shared Function GET_PARAM_ByName(tcParam_name, TESTSYSTEM) As String
            Return SQL.getValueTxtBySql("SELECT TOP 1 [Param_value]  FROM [tblPartnersystem_Paramter] WHERE  Param_system='IDS' AND [Param_name]='" & tcParam_name & "' AND Param_testsystem = " & IIf(TESTSYSTEM, 1, 0), , , SQL.GetNewOpenConnectionFMZOLL_SYSTEM(TESTSYSTEM))
        End Function

        Shared Function getFTPConenction(ByRef API_String As String, ByRef API As DataTable, ByRef program As String) As Boolean

            API = SQL.loadDgvBySql("SELECT top(1) * FROM tblAPIEinstellungen WHERE api_program='" & program & "' and api_productive ='" & IIf(VERAG_PROG_ALLGEMEIN.cAllgemein.TESTSYSTEM, "0", "1") & "'", "ADMIN")
            If API.Rows.Count = 0 Then
                MsgBox("keine gültigen API-Einstellungen für " & program & " gefunden!")
            Else
                apiSettingsloaded = True
                API_String = API.Rows(0).Item("api_url")
            End If

            Return apiSettingsloaded
        End Function

    End Class


End Class



Public Class cIDSInvoice

    Property invoice_id As Integer
    Property YearMonthDay As Object = Nothing
    Property CustomerCode As Object = Nothing
    Property Invoicenumber As Object = Nothing
    Property DocumentName As Object = Nothing
    Property Zeitstempel As Object = Nothing
    Property daid As Object = Nothing
    Property archiv As Boolean
    Property archiviertDatum As Object = Nothing



    Public hasEntry = False

    Dim SQL As New SQL

    Sub New()

    End Sub

    Sub New(invoice_id)
        Me.invoice_id = invoice_id
        LOADID()
    End Sub

    Sub New(YearMonthDay, CustomerCode, Invoicenumber)
        Me.YearMonthDay = YearMonthDay
        Me.Invoicenumber = Invoicenumber
        Me.CustomerCode = CustomerCode
        LOAD()

    End Sub


    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("invoice_id", invoice_id,, True))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("YearMonthDay", YearMonthDay))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("CustomerCode", CustomerCode))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Invoicenumber", Invoicenumber))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("DocumentName", DocumentName))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Zeitstempel", Zeitstempel))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("daid", daid))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("archiv", archiv))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("archiviertDatum", archiviertDatum))
        Return list
    End Function
    Public Function SAVE() As Boolean
        Dim list As List(Of VERAG_PROG_ALLGEMEIN.SQLVariable) = getParameterList()

        Dim sqlstr = " BEGIN TRAN IF EXISTS(SELECT * FROM tblIDSInvoicesNew WHERE YearMonthDay=@YearMonthDay AND CustomerCode=@CustomerCode AND Invoicenumber=@Invoicenumber) " &
                               " BEGIN " & getUpdateCmd() & " END " &
                               " Else " &
                               " BEGIN " & getInsertCmd() & " END " &
                               " commit tran "

        Return SQL.doSQLVarList(sqlstr, "FMZOLL", , list)
    End Function
    Public Sub LOADID()
        Try
            hasEntry = False
            Using conn As SqlConnection = SQL.GetNewOpenConnectionFMZOLL()
                Using cmd As New SqlCommand("SELECT * FROM tblIDSInvoicesNew WHERE invoice_id=@invoice_id  ", conn)
                    cmd.Parameters.AddWithValue("@invoice_id", invoice_id)
                    Dim dr = cmd.ExecuteReader()
                    If dr.Read Then
                        For Each li In getParameterList()
                            Dim propInfo As PropertyInfo = Me.GetType.GetProperty(li.Scalarvariable)

                            If dr.Item(li.Text) Is DBNull.Value Then
                                propInfo.SetValue(Me, Nothing)
                            Else
                                propInfo.SetValue(Me, dr.Item(li.Text))
                            End If
                        Next
                        hasEntry = True
                    End If
                    dr.Close()
                End Using
            End Using
        Catch ex As Exception
            VERAG_PROG_ALLGEMEIN.cErrorHandler.ERR(ex.Message, ex.StackTrace, System.Reflection.MethodInfo.GetCurrentMethod.Name)
        End Try
    End Sub


    Public Sub LOAD()
        Try
            hasEntry = False
            Using conn As SqlConnection = SQL.GetNewOpenConnectionFMZOLL()
                Using cmd As New SqlCommand("SELECT * FROM tblIDSInvoicesNew WHERE YearMonthDay=@YearMonthDay AND CustomerCode=@CustomerCode AND Invoicenumber=@Invoicenumber ", conn)
                    cmd.Parameters.AddWithValue("@YearMonthDay", YearMonthDay)
                    cmd.Parameters.AddWithValue("@Invoicenumber", Invoicenumber)
                    cmd.Parameters.AddWithValue("@CustomerCode", CustomerCode)

                    Dim dr = cmd.ExecuteReader()
                    If dr.Read Then
                        For Each li In getParameterList()
                            Dim propInfo As PropertyInfo = Me.GetType.GetProperty(li.Scalarvariable)

                            If dr.Item(li.Text) Is DBNull.Value Then
                                propInfo.SetValue(Me, Nothing)
                            Else
                                propInfo.SetValue(Me, dr.Item(li.Text))
                            End If
                        Next
                        hasEntry = True
                    End If
                    dr.Close()
                End Using
            End Using
        Catch ex As Exception
            VERAG_PROG_ALLGEMEIN.cErrorHandler.ERR(ex.Message, ex.StackTrace, System.Reflection.MethodInfo.GetCurrentMethod.Name)
        End Try
    End Sub
    Public Function getUpdateCmd() As String
        Try
            Dim list As List(Of VERAG_PROG_ALLGEMEIN.SQLVariable) = getParameterList()
            Dim str As String = ""
            For Each i In list
                If Not i.isPrimaryParam Then
                    str &= "[" & i.Text & "] = @" & i.Scalarvariable & "," '.Replace("-", "").Replace(" ", "") & ","
                End If
            Next
            str = str.Substring(0, str.Length - 1) 'wg. ','
            Return (" UPDATE [tblIDSInvoicesNew] SET " & str & " WHERE YearMonthDay=@YearMonthDay AND CustomerCode=@CustomerCode AND Invoicenumber=@Invoicenumber")

        Catch ex As Exception
            VERAG_PROG_ALLGEMEIN.cErrorHandler.ERR(ex.Message, ex.StackTrace, System.Reflection.MethodInfo.GetCurrentMethod.Name)
        End Try
        Return ""
    End Function
    Public Function getInsertCmd() As String
        Try
            Dim list As List(Of VERAG_PROG_ALLGEMEIN.SQLVariable) = getParameterList()
            Dim str As String = ""
            Dim values As String = ""
            For Each i In list
                If Not i.isPrimaryParam Then
                    str &= "[" & i.Text & "],"
                    values &= "@" & i.Scalarvariable & "," '.Replace("-", "").Replace(" ", "") & ","
                End If
            Next
            str = str.Substring(0, str.Length - 1) 'wg. ','
            values = values.Substring(0, values.Length - 1) 'wg. ','
            Return (" INSERT INTO tblIDSInvoicesNew  (" & str & ") VALUES(" & values & ")  ")
        Catch ex As Exception
            VERAG_PROG_ALLGEMEIN.cErrorHandler.ERR(ex.Message, ex.StackTrace, System.Reflection.MethodInfo.GetCurrentMethod.Name)
        End Try
        Return ""
    End Function





    'Shared Function isMail_IDS(mailItem As Outlook.MailItem) As Boolean
    Shared Function isMail_IDS(html As String) As Boolean
        Try
            If html IsNot Nothing Then
                Dim srch As String = "https://admin.unifiedpost.com/umadmin/viewDoc?doc="
                Return html.Contains(srch)
            End If
        Catch ex As Exception
            VERAG_PROG_ALLGEMEIN.cErrorHandler.ERR(ex.Message, ex.StackTrace, System.Reflection.MethodInfo.GetCurrentMethod.Name)
        End Try
        Return False
    End Function




    'Shared Function getPDF_IDS(ByRef mailItem As Outlook.MailItem, ByRef filename As String, ByRef targetpath As String) As Boolean
    Shared Function getPDF_IDS(ByRef htmlText As String, ByRef filename As String, ByRef targetpath As String) As Boolean

        Try

            If htmlText Is Nothing Then Return False

            Dim html = htmlText

            Dim srch As String = "https://admin.unifiedpost.com/"
            If html.ToString.Contains(srch) Then

                Dim myDelims As String() = New String() {srch}
                Dim sp = html.Split(myDelims, StringSplitOptions.None)

                Dim cnt = 0

                For Each txtHTML In sp
                    If cnt > 0 AndAlso txtHTML.Contains(""">") Then

                        System.Net.ServicePointManager.SecurityProtocol = System.Net.ServicePointManager.SecurityProtocol Or SecurityProtocolType.Tls11 Or SecurityProtocolType.Tls12

                        Dim pdfNameWithCounter As String = ""
                        Dim counter As Integer = 1
                        Dim lastPdfName As String = ""
                        Dim linkinHTML = "umadmin/viewDoc?doc="

                        Dim lastIndexLink = txtHTML.ToString.IndexOf(linkinHTML) + linkinHTML.Length
                        Dim link = ("https://admin.unifiedpost.com/umadmin/viewDoc?doc=" & txtHTML.ToString.Substring(lastIndexLink, txtHTML.ToString.IndexOf(("Open document")) - lastIndexLink - 2))
                        Dim pdf = VERAG_PROG_ALLGEMEIN.cFormularManager.getPDFViaSpirePDF_FromURLStream(link, , targetpath, False)
                        If pdf <> "" Then
                            Dim fi As New FileInfo(pdf)
                            filename = fi.Name
                            Return True
                        End If

                    End If
                    cnt += 1
                Next
            End If

        Catch ex As Exception
            VERAG_PROG_ALLGEMEIN.cErrorHandler.ERR(ex.Message, ex.StackTrace, System.Reflection.MethodInfo.GetCurrentMethod.Name)
        End Try
        Return False
    End Function

End Class




Public Class cIDSInvoiceSplittedByCountry

    Property invoice_id As Integer
    Property YearMonthDay As Object = Nothing
    Property CustomerCode As Object = Nothing
    Property TotalInvoiceId As Object = Nothing
    Property Country As Object = Nothing
    Property Invoicenumber As Object = Nothing
    Property DocumentName As Object = Nothing
    Property Zeitstempel As Object = Nothing
    Property daid As Object = Nothing
    Property archiv As Boolean
    Property archiviertDatum As Object = Nothing



    Public hasEntry = False

    Dim SQL As New SQL

    Sub New()

    End Sub

    Sub New(invoice_id)
        Me.invoice_id = invoice_id
        LOADID()
    End Sub

    Sub New(YearMonthDay, CustomerCode, Invoicenumber, Country)
        Me.YearMonthDay = YearMonthDay
        Me.Invoicenumber = Invoicenumber
        Me.CustomerCode = CustomerCode
        Me.Country = Country
        LOAD()

    End Sub


    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("invoice_id", invoice_id,, True))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("YearMonthDay", YearMonthDay))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("TotalInvoiceId", TotalInvoiceId))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("CustomerCode", CustomerCode))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Invoicenumber", Invoicenumber))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("DocumentName", DocumentName))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Country", Country))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("Zeitstempel", Zeitstempel))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("daid", daid))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("archiv", archiv))
        list.Add(New VERAG_PROG_ALLGEMEIN.SQLVariable("archiviertDatum", archiviertDatum))



        Return list
    End Function
    Public Function SAVE() As Boolean
        Dim list As List(Of VERAG_PROG_ALLGEMEIN.SQLVariable) = getParameterList()

        Dim sqlstr = " BEGIN TRAN IF EXISTS(SELECT * FROM tblIDSInvoicesNewSplittedByCountry WHERE YearMonthDay=@YearMonthDay AND CustomerCode=@CustomerCode AND Invoicenumber=@Invoicenumber AND Country=@Country) " &
                               " BEGIN " & getUpdateCmd() & " END " &
                               " Else " &
                               " BEGIN " & getInsertCmd() & " END " &
                               " commit tran "

        Return SQL.doSQLVarList(sqlstr, "FMZOLL", , list)
    End Function
    Public Sub LOADID()
        Try
            hasEntry = False
            Using conn As SqlConnection = SQL.GetNewOpenConnectionFMZOLL()
                Using cmd As New SqlCommand("SELECT * FROM tblIDSInvoicesNewSplittedByCountry WHERE invoice_id=@invoice_id  ", conn)
                    cmd.Parameters.AddWithValue("@invoice_id", invoice_id)
                    Dim dr = cmd.ExecuteReader()
                    If dr.Read Then
                        For Each li In getParameterList()
                            Dim propInfo As PropertyInfo = Me.GetType.GetProperty(li.Scalarvariable)

                            If dr.Item(li.Text) Is DBNull.Value Then
                                propInfo.SetValue(Me, Nothing)
                            Else
                                propInfo.SetValue(Me, dr.Item(li.Text))
                            End If
                        Next
                        hasEntry = True
                    End If
                    dr.Close()
                End Using
            End Using
        Catch ex As Exception
            VERAG_PROG_ALLGEMEIN.cErrorHandler.ERR(ex.Message, ex.StackTrace, System.Reflection.MethodInfo.GetCurrentMethod.Name)
        End Try
    End Sub


    Public Sub LOAD()
        Try
            hasEntry = False
            Using conn As SqlConnection = SQL.GetNewOpenConnectionFMZOLL()
                Using cmd As New SqlCommand("SELECT * FROM tblIDSInvoicesNewSplittedByCountry WHERE YearMonthDay=@YearMonthDay AND CustomerCode=@CustomerCode AND Invoicenumber=@Invoicenumber AND Country=@Country", conn)
                    cmd.Parameters.AddWithValue("@YearMonthDay", YearMonthDay)
                    cmd.Parameters.AddWithValue("@Invoicenumber", Invoicenumber)
                    cmd.Parameters.AddWithValue("@CustomerCode", CustomerCode)
                    cmd.Parameters.AddWithValue("@Country", Country)

                    Dim dr = cmd.ExecuteReader()
                    If dr.Read Then
                        For Each li In getParameterList()
                            Dim propInfo As PropertyInfo = Me.GetType.GetProperty(li.Scalarvariable)

                            If dr.Item(li.Text) Is DBNull.Value Then
                                propInfo.SetValue(Me, Nothing)
                            Else
                                propInfo.SetValue(Me, dr.Item(li.Text))
                            End If
                        Next
                        hasEntry = True
                    End If
                    dr.Close()
                End Using
            End Using
        Catch ex As Exception
            VERAG_PROG_ALLGEMEIN.cErrorHandler.ERR(ex.Message, ex.StackTrace, System.Reflection.MethodInfo.GetCurrentMethod.Name)
        End Try
    End Sub
    Public Function getUpdateCmd() As String
        Try
            Dim list As List(Of VERAG_PROG_ALLGEMEIN.SQLVariable) = getParameterList()
            Dim str As String = ""
            For Each i In list
                If Not i.isPrimaryParam Then
                    str &= "[" & i.Text & "] = @" & i.Scalarvariable & "," '.Replace("-", "").Replace(" ", "") & ","
                End If
            Next
            str = str.Substring(0, str.Length - 1) 'wg. ','
            Return (" UPDATE [tblIDSInvoicesNewSplittedByCountry] SET " & str & " WHERE YearMonthDay=@YearMonthDay AND CustomerCode=@CustomerCode AND Invoicenumber=@Invoicenumber AND Country=@Country")

        Catch ex As Exception
            VERAG_PROG_ALLGEMEIN.cErrorHandler.ERR(ex.Message, ex.StackTrace, System.Reflection.MethodInfo.GetCurrentMethod.Name)
        End Try
        Return ""
    End Function
    Public Function getInsertCmd() As String
        Try
            Dim list As List(Of VERAG_PROG_ALLGEMEIN.SQLVariable) = getParameterList()
            Dim str As String = ""
            Dim values As String = ""
            For Each i In list
                If Not i.isPrimaryParam Then
                    str &= "[" & i.Text & "],"
                    values &= "@" & i.Scalarvariable & "," '.Replace("-", "").Replace(" ", "") & ","
                End If
            Next
            str = str.Substring(0, str.Length - 1) 'wg. ','
            values = values.Substring(0, values.Length - 1) 'wg. ','
            Return (" INSERT INTO tblIDSInvoicesNewSplittedByCountry  (" & str & ") VALUES(" & values & ")  ")
        Catch ex As Exception
            VERAG_PROG_ALLGEMEIN.cErrorHandler.ERR(ex.Message, ex.StackTrace, System.Reflection.MethodInfo.GetCurrentMethod.Name)
        End Try
        Return ""
    End Function

End Class






