EulandaConnect

2.3.11

EulandaConnect.psm1

# *****************************************************************************
# EULANDA Software GmbH
# Documentation: https://github.com/Eulanda/EulandaConnect
# License: https://github.com/Eulanda/EulandaConnect/blob/master/License.md
# *****************************************************************************

# -----------------------------------------------------------------------------
# Global values
# -----------------------------------------------------------------------------
Set-StrictMode -version latest
Add-Type -AssemblyName System.Windows.Forms | Out-Null



# -----------------------------------------------------------------------------
# Global ado constants
# -----------------------------------------------------------------------------

[long]$global:adCmdText = 1
[long]$global:adLockOptimistic = 3
[long]$global:adOpenKeyset = 1
[long]$global:adStateClosed = 0
[long]$global:adStateConnecting = 2
[long]$global:adStateExecuting = 4
[long]$global:adStateFetching = 8
[long]$global:adStateOpen = 1
[long]$global:adTimeout = 60*20
[long]$global:adUseClient = 3



# -----------------------------------------------------------------------------
# Global helper für Get-Conn functiom
# -----------------------------------------------------------------------------
[bool]$global:adCloseOnExit = $false


# -----------------------------------------------------------------------------
# Global MsgBox
# -----------------------------------------------------------------------------
[int]$global:mbNone = 0

[int]$global:mbOk = [System.Windows.Forms.MessageBoxButtons]::OK
[int]$global:mbOkCancel = [System.Windows.Forms.MessageBoxButtons]::OkCancel
[int]$global:mbAbortRetryIgnore = [System.Windows.Forms.MessageBoxButtons]::AbortRetryIgnore
[int]$global:mbYesNoCancel = [System.Windows.Forms.MessageBoxButtons]::YesNoCancel
[int]$global:mbYesNo = [System.Windows.Forms.MessageBoxButtons]::YesNo
[int]$global:mbRetryCancel = [System.Windows.Forms.MessageBoxButtons]::RetryCancel

[int]$global:mbStop = [System.Windows.Forms.MessageBoxIcon]::Stop
[int]$global:mbHand = [System.Windows.Forms.MessageBoxIcon]::Stop
[int]$global:mbQuestion = [System.Windows.Forms.MessageBoxIcon]::Question
[int]$global:mbWarning = [System.Windows.Forms.MessageBoxIcon]::Warning
[int]$global:mbExclamation = [System.Windows.Forms.MessageBoxIcon]::Warning
[int]$global:mbInfo = [System.Windows.Forms.MessageBoxIcon]::Information
[int]$global:mbAsterisk = [System.Windows.Forms.MessageBoxIcon]::Information
[int]$global:mbInformation = [System.Windows.Forms.MessageBoxIcon]::Information

[int]$global:mbButton1 = [System.Windows.Forms.MessageBoxDefaultButton]::Button1
[int]$global:mbButton2 = [System.Windows.Forms.MessageBoxDefaultButton]::Button2
[int]$global:mbButton3 = [System.Windows.Forms.MessageBoxDefaultButton]::Button3
[int]$global:mbButton4 = [System.Windows.Forms.MessageBoxDefaultButton]::Button4

[int]$global:mbrOk = [System.Windows.Forms.DialogResult]::Ok
[int]$global:mbrCancel = [System.Windows.Forms.DialogResult]::Cancel
[int]$global:mbrYes = [System.Windows.Forms.DialogResult]::Yes
[int]$global:mbrNo = [System.Windows.Forms.DialogResult]::No
[int]$global:mbrAbort = [System.Windows.Forms.DialogResult]::Abort
[int]$global:mbrRetry = [System.Windows.Forms.DialogResult]::Retry
[int]$global:mbrIgnore = [System.Windows.Forms.DialogResult]::Ignore
[int]$global:mbrTryAgain = [System.Windows.Forms.DialogResult]::TryAgain
[int]$global:mbrContinue = [System.Windows.Forms.DialogResult]::Continue













# -----------------------------------------------------------------------------
# Public functions
# -----------------------------------------------------------------------------

function Approve-Signature {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $false)]
        [string]$path
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $filename = [IO.Path]::GetFileNameWithoutExtension($path)
    $extension = [IO.Path]::GetExtension($path)
    $folder = [IO.Path]::GetDirectoryName($path)

    if ((! $folder) -or ($folder -eq '.')) {
        $folder= Get-Location
    }

    # If no path take the last folder
    if (! ($filename)) {
        if (! ($folder -in ".", ".\", "")) {
            $filename = Split-Path $folder -Leaf
        }
    }

    # if no extension take ps1, then psm1 then exe
    if (! ($extension)) {
        $extension= '.ps1'
        if (-not (Test-Path "$folder\$($filename)$($extension)" )) {
            $extension= '.psm1'
            if (-not (Test-Path "$folder\$($filename)$($extension)" )) {
                $extension= '.exe'
                if (-not (Test-Path "$folder\$($filename)$($extension)" )) {
                    # Change back to .ps1 so that Resolve-Path can render
                    # an exeption with the default extension
                    $extension= '.ps1'
                }
            }
        }
    }

    [string]$signFile= Resolve-Path "$folder\$($filename)$($extension)"

    Write-Host "Signing file '$signFile'" -ForegroundColor Blue

    [string]$app= $signFile
    $Process = (Start-Process -NoNewWindow -FilePath $(Get-SignToolPath) -PassThru -Wait -ArgumentList "sign /tr http://timestamp.sectigo.com?td=sha256 /td sha256 /fd sha256 /a ""$app""")
    if ($Process.exitcode -ne 0) {
        throw "ERROR! Signing failed! $_"
    }
}

function Close-Delivery {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $false)]
        [ValidateScript({
            if ($_ -eq $null -or $_ -ge 0) {return $true} else {return $false}
        })]
        [int]$deliveryId
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({
            if ($_ -eq $null -or $_ -ge 0) {return $true} else {return $false}
        })]
        [int]$deliveryNo
        ,
        [Parameter(Mandatory = $false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    if ($deliveryId)  {
        $sql = @"
        SET NOCOUNT ON;
        DECLARE @DeliveryId int
        SET @DeliveryId = $deliveryId
        EXEC dbo.cn_lfBuchen @lf_id=@DeliveryId
"@
    } elseif ($deliveryNo) {
        $sql = @"
        SET NOCOUNT ON;
        DECLARE @DeliveryNo int
        SET @DeliveryNo = $deliveryNo
        EXEC dbo.cn_lfBuchen @lf_Nummer=@DeliveryNo
"@
    } else {
        throw "Neither a delivery note id is specified, nor a delivery note number. Function: $($myInvocation.MyCommand)"
    }

    try {
        $myConn.Execute($sql) | out-null
    } finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }
}

function Close-SalesOrder {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $false)]
        [ValidateRange("Positive")]
        [int]$salesOrderId
        ,
        [Parameter(Mandatory = $false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    $sql = @"
    SET NOCOUNT ON;
    DECLARE @SalesOrderId int
    SET @SalesOrderId = $salesOrderId
    EXEC dbo.cn_afBuchen @af_id=@SalesOrderId
"@

    try {
        $myConn.Execute($sql) | out-null
    } finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }
}

function Confirm-System {
    [CmdletBinding()]
    param(
        [switch]$administrator
        ,
        [switch]$controlledFolderAccess
        ,
        [switch]$memory
        ,
        [switch]$drives
        ,
        [switch]$network
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $result = New-Object System.Collections.ArrayList

    if ($administrator) {
        $item= [PSCustomObject]@{
            Description = 'Administrative rights'
            Value = [string](Test-Administrator)
        }
        $result.Add($item) | Out-Null
    }

    if ($controlledFolderAccess) {
        $item= [PSCustomObject]@{
            Description = 'Ransomware protection aktiv'
            Value = [bool](Get-MpPreference | Select-Object EnableControlledFolderAccess).toString()
        }
        $result.Add($item) | Out-Null
    }

    if ($memory) {
        $item= [PSCustomObject]@{
            Description = 'Working memory (GB)'
            Value = [string]((Get-CimInstance Win32_PhysicalMemory | Measure-Object -Property Capacity -Sum).Sum / 1GB )
        }
        $result.Add($item) | Out-Null
    }

    if ($drives) {
        for ($i=0; $i -lt (Get-PsDrive -PsProvider FileSystem).count-1; $i++) {
            $item= [PSCustomObject]@{
                Description = 'Drive free on ' + (Get-PsDrive -PsProvider FileSystem)[$i].Name + ' (GB)'
                Value = [string]([math]::Round((Get-PsDrive -PsProvider FileSystem)[$i].Free/1GB))
            }
            $result.Add($item) | Out-Null
        }

        $status = wmic diskdrive get model,status
        for ($i=1; $i -lt $Status.count-1; $i++) {
            if ($status[$i].trim()) {
                $lastWord = ($status[$i].trim() -split " ")[-1]
                $lastSpaceIndex = $status[$i].trim().LastIndexOf(" ")-1
                $diskModel= $status[$i].Substring(0, $lastSpaceIndex).trim()
                $item= [PSCustomObject]@{
                    Description = "S.M.A.R.T. ($diskModel)"
                    Value = [string]$Lastword.trim()
                }
                $result.Add($item) | Out-Null
            }
        }
    }

    if ($network) {
        $gatewayIp = Get-GatewayIp
        $item= [PSCustomObject]@{
            Description = "Gateway Ip"
            Value = [string]$gatewayIp
        }
        $result.Add($item) | Out-Null

        $gatewayName = Get-Hostname ($gatewayIp)
        $item= [PSCustomObject]@{
            Description = "Gateway Name"
            Value = [string]$gatewayName
        }
        $result.Add($item) | Out-Null

        $localIp = Get-LocalIp
        $item= [PSCustomObject]@{
            Description = "Local Ip"
            Value = [string]$localIp
        }
        $result.Add($item) | Out-Null

        $localSubnet= Get-Subnet -localIp $localIp
        $item= [PSCustomObject]@{
            Description = "Local Subnet"
            Value = [string]$localSubnet
        }
        $result.Add($item) | Out-Null

        $cidr= Get-Cidr -subnet $localSubnet
        $item= [PSCustomObject]@{
            Description = "Local CIDR"
            Value = [string]$cidr
        }
        $result.Add($item) | Out-Null

        $publicIp = Get-PublicIp
        $item= [PSCustomObject]@{
            Description = "Public Ip"
            Value = [string]$publicIp
        }
        $result.Add($item) | Out-Null

        $maxHosts= Get-MaxHosts -cidr $cidr
        $item= [PSCustomObject]@{
            Description = "Max possible Hosts"
            Value = [string]$maxHosts
        }
        $result.Add($item) | Out-Null

        $networkId= Get-NetworkId -ip $localIp -cidr $cidr
        $item= [PSCustomObject]@{
            Description = "Network ID"
            Value = [string]$networkId
        }
        $result.Add($item) | Out-Null

        $firstIp= Get-FirstIp -networkId $networkId -cidr $cidr
        $item= [PSCustomObject]@{
            Description = "First possible Ip"
            Value = [string]$firstIp
        }
        $result.Add($item) | Out-Null


        $lastIp= Get-LastIp -networkId $networkId -cidr $cidr
        $item= [PSCustomObject]@{
            Description = "Last possible Ip"
            Value = [string]$lastIp
        }
        $result.Add($item) | Out-Null


        $broadcastIp= Get-BroadcastIp -networkId $networkId -cidr $cidr
        $item= [PSCustomObject]@{
            Description = "Broadcast Ip"
            Value = [string]$broadcastIp
        }
        $result.Add($item) | Out-Null
    }


    Return $result
}

function Convert-Accent {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory=$true)]
        [string]$value
        ,
        [ValidateSet('none', 'upper', 'lower', 'capital')]
        [string]$strCase = 'none'
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    # case sensitive
    $hash = New-Object hashtable
    $hash['ä']='ae'
    $hash['ö']='oe'
    $hash['ü']='ue'
    $hash['Ä']='Ae'
    $hash['Ö']='Oe'
    $hash['Ü']='Ue'
    $hash['ß']='ss'
    $hash['œ']='oe'
    $hash['â']='a'
    $hash['à']='a'
    $hash['á']='a'
    $hash['ç']='c'
    $hash['ê']='e'
    $hash['è']='e'
    $hash['é']='e'
    $hash['ë']='e'
    $hash['î']='i'
    $hash['ì']='i'
    $hash['í']='i'
    $hash['ï']='i'
    $hash['ô']='o'
    $hash['ò']='o'
    $hash['ó']='o'
    $hash['û']='u'
    $hash['ù']='u'
    $hash['ú']='u'
    $hash['Â']='A'
    $hash['À']='A'
    $hash['Á']='A'
    $hash['Ê']='E'
    $hash['È']='E'
    $hash['É']='E'
    $hash['Î']='I'
    $hash['Ì']='I'
    $hash['Í']='I'
    $hash['Ô']='O'
    $hash['Ò']='O'
    $hash['Ó']='O'
    $hash['Û']='U'
    $hash['Ù']='U'
    $hash['Ú']='U'

    [string]$result = $value
    foreach ($item in $hash.GetEnumerator()) {
        $result = $result -creplace  "$($item.key)", "$($item.value)"
    }

    switch ( $strCase.ToLower() ) {
        'none' {  }
        'upper' { $value = $value.ToUpper() }
        'lower' { $value = $value.ToLower() }
        'capital' { $value = $value.Substring(0,1).ToUpper() + $value.Substring(1).ToLower() }
    }

    Return $result
}

function Convert-DataToXml {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory=$true)]
        $data
        ,
        [Parameter(Mandatory=$false)]
        [switch]$metadata
        ,
        [Parameter(Mandatory=$false)]
        [ValidateSet('none', 'upper', 'lower','capital')]
        [string]$strCase = 'upper'
        ,
        [Parameter(Mandatory=$false)]
        [string]$root = 'Root'
        ,
        [Parameter(Mandatory=$false)]
        [string]$arrRoot = 'Records'
        ,
        [Parameter(Mandatory=$false)]
        [string]$arrSubRoot = 'Record'
        ,
        [Parameter(Mandatory=$false)]
        $writer
        ,
        [Parameter(Mandatory=$false)]
        [int]$level
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    if ($DebugPreference -eq "Continue") {
        $on = $true
    } else {
        $on = $false
    }

    $root = Convert-StringCase -value $root -strCase $strCase

    if ($level -eq 0) {
        if ($on) { Write-Host "BEGIN: $root (:$level)" -ForegroundColor Cyan }
        # On level=0 it is a good way to initialize the xmlWriter
        $encoding = [System.Text.Encoding]::UTF8
        $settings = New-Object System.Xml.XmlWriterSettings
        $settings.Indent = $true
        $settings.IndentChars = " "
        $settings.Encoding = $encoding
        $stream = New-Object System.IO.MemoryStream
        $writer = [System.XML.XmlWriter]::Create($stream, $settings)

        # Start with the root node
        $writer.WriteStartDocument()
        $writer.WriteStartElement($root)
        if ($metadata) {
            $writer = Write-XmlMetadata -writer $writer -strCase $strCase
        }
    }
    if  (($data -is [System.Collections.ArrayList]) -or ($data -is [System.Object[]])) {
        $nodeName = (Convert-StringCase -value ($arrRoot) -strCase $strCase)
        $writer.WriteStartElement($nodeName)
        if ($on) { Write-Host "ROOT.Array(max$($data.count)): $nodeName (:$level)" -ForegroundColor Red }
        for ($i=0; $i -lt $data.count; $i++) {
            $nodeName = (Convert-StringCase -value $arrSubRoot -strCase $strCase)
            $writer.WriteStartElement($nodeName)
            if ($on) { Write-Host "ROOT.Array($i): $nodeName (:$level)" -ForegroundColor Red }
            # $nodeName = (Convert-StringCase -value ($root+$plural) -strCase $strCase)
            if ($data[$i] -is [System.Collections.Specialized.OrderedDictionary]) {
                if ($on) { Write-Host "hash(max$($data[$i].count)): $nodeName (:$level+1)" -ForegroundColor blue }
            }
            Convert-DataToXml -data $data[$i] -root $nodeName -level ($level+1) -writer $writer -strCase $strCase -arrRoot $arrRoot -arrSubRoot $arrSubRoot
            $writer.WriteEndElement()
        }
        $writer.WriteEndElement()
    } else {
        foreach ($key in $data.Keys) {
            $value = $data[$key]
            if (($value -is [System.Collections.ArrayList]) -or ($value -is [System.Object[]])) {
                $nodeName = (Convert-StringCase -value ($key) -strCase $strCase)
                $writer.WriteStartElement($nodeName)
                if ($on) { Write-Host "array(max$($value.count)): $nodeName (:$level)" -ForegroundColor Red }
                for ($i=0; $i -lt $value.count; $i++) {
                    $nodeName = (Convert-StringCase -value $arrSubRoot -strCase $strCase)
                    $writer.WriteStartElement($nodeName)
                    if ($on) { Write-Host "array($i): $nodeName (:$level)" -ForegroundColor Red }
                    # $nodeName = (Convert-StringCase -value ($key+$plural) -strCase $strCase)
                    if ($value[$i] -is [System.Collections.Specialized.OrderedDictionary]) {
                        if ($on) { Write-Host "hash(max$($value[$i].count)): $nodeName (:$level+1)" -ForegroundColor blue }
                    }
                    Convert-DataToXml -data $value[$i] -root $nodeName -level ($level+1) -writer $writer -strCase $strCase -arrRoot $arrRoot -arrSubRoot $arrSubRoot
                    $writer.WriteEndElement()
                }
                $writer.WriteEndElement()
            } elseif ($value -is [System.Collections.Hashtable]) {
                $nodeName = (Convert-StringCase -value ($key) -strCase $strCase)
                $writer.WriteStartElement($nodeName)
                if ($on) { Write-Host "hash(max$($value.count)): $nodeName (:$level)" -ForegroundColor blue }
                $nodeName = (Convert-StringCase -value ($key) -strCase $strCase)
                Convert-DataToXml -data $value -root $nodeName -level ($level+1) -writer $writer -strCase $strCase -arrRoot $arrRoot -arrSubRoot $arrSubRoot
                $writer.WriteEndElement()
            } elseif ($value -is [System.Collections.Specialized.OrderedDictionary]) {
                $nodeName = (Convert-StringCase -value ($key) -strCase $strCase)
                $writer.WriteStartElement($nodeName)
                Convert-DataToXml -data $value -root $nodeName -level ($level+1) -writer $writer -strCase $strCase -arrRoot $arrRoot -arrSubRoot $arrSubRoot
                $writer.WriteEndElement()
            } else { # hash values comes here like: System.Collections.Specialized.OrderedDictionary
                # Here we have the xml node data
                if ($null -eq $value) {
                    $strValue= ''
                } else {
                    if ($value.GetType().Name -eq 'DateTime') {
                        $strValue = Convert-DateToIso -value $value -noTimeZone
                    } else {
                        $strValue = [string]$value
                    }
                }
                $nodeName = (Convert-StringCase -value ($key) -strCase $strCase)
                $writer.WriteElementString($nodeName,$strValue)
                $spaces = Get-Spaces -count ($level * 4)
                if ($on) { Write-Host "$($spaces)$($nodeName) = '$strValue' (:$level)" -ForegroundColor Green }
            }
        }
    }
    # If the level is '0' when the function exits,
    # all recursions are done and the XmlWriter can be closed.
    if ($level -eq 0) {
        if ($on) { Write-Host "END: $root (:$level)" -ForegroundColor Cyan }
        $writer.WriteEndElement()
        $writer.WriteEndDocument()
        $writer.Flush()
        $writer.Close()

        # Transfer the content of the memory stream to
        # a string and then to a standard XML object
        $stream.Position = 0
        $reader = New-Object System.IO.StreamReader($stream)
        $xmlString = $reader.ReadToEnd()
        [xml]$xml = $xmlString
        Return $xml
    }
}

function Convert-DateToIso {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $true)]
        $value
        ,
        [switch]$asUtc
        ,
        [switch]$noTime
        ,
        [switch]$noDate
        ,
        [switch]$noTimeZone
        ,
        [switch]$zeroTime
        ,
        [switch]$noonTime
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    if ($noTime) {
        [datetime]$date = [datetime]::new($value.Year, $value.Month, $value.Day, 12, 0, 0)

        if ($noTimeZone) {
            if ($asUtc) {
                [string]$result = $(Get-Date -Date $date.ToUniversalTime() -format "yyyy-MM-dd")
            } else {
                [string]$result = $(Get-Date -Date $date -format "yyyy-MM-dd")
            }
        } else {
            if ($asUtc) {
                [string]$result = $(Get-Date -Date $date.ToUniversalTime() -format "yyyy-MM-ddZ")
            } else {
                [string]$result = $(Get-Date -Date $date -format "yyyy-MM-dd.0zzz")
            }
        }
    } elseif ($noDate) {
        if ($noonTime) {
            [datetime]$date = [datetime]::new(1, 1, 1, 12, 0, 0)
        } elseif ($zeroTime) {
            [datetime]$date = [datetime]::new($value.Year, $value.Month, $value.Day, 0, 0, 0)
        } else
         {
            [datetime]$date = [datetime]::new(1, 1, 1, $value.Hour, $value.Minute, $value.Second)
        }

        if ($noTimeZone) {
            if ($asUtc) {
                [string]$result = $(Get-Date -Date $date.ToUniversalTime() -format "HH:mm:ss")
            } else {
                [string]$result = $(Get-Date -Date $date -format "HH:mm:ss")
            }
        } else {
            if ($asUtc) {
                [string]$result = $(Get-Date -Date $date.ToUniversalTime() -format "HH:mm:ss.0Z")
            } else {
                [string]$result = $(Get-Date -Date $date -format "HH:mm:ss.0zzz")
            }
        }
    } else { # time AND date
        if ($noonTime) {
            [datetime]$date = [datetime]::new($value.Year, $value.Month, $value.Day, 12, 0, 0)
        } elseif ($zeroTime) {
            [datetime]$date = [datetime]::new($value.Year, $value.Month, $value.Day, 0, 0, 0)
        } else {
            [datetime]$date = [datetime]::new($value.Year, $value.Month, $value.Day, $value.Hour, $value.Minute, $value.Second)
        }

        if ($noTimeZone) {
            if ($asUtc) {
                [string]$result = $(Get-Date -Date $date.ToUniversalTime() -format "yyyy-MM-ddTHH:mm:ss")
            } else {
                [string]$result = $(Get-Date -Date $date -format "yyyy-MM-ddTHH:mm:ss")
            }
        } else {
            if ($asUtc) {
                [string]$result = $(Get-Date -Date $date.ToUniversalTime() -format "yyyy-MM-ddTHH:mm:ssZ")
            } else {
                [string]$result = $(Get-Date -Date $date.ToUniversalTime() -format "yyyy-MM-ddTHH:mm:ss.0zzz")
            }
        }
    }

    Return $result
}

function Convert-ImageToBase64 {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory=$true)]
        [ValidateScript({
            if($_) {
                if(!(Test-Path $_)) {
                    throw "Image file '$_' did not exist!"
                }
            }
            return $true
        })]
        [string]$path
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $content = [System.IO.File]::ReadAllBytes($path)
    $base64 = [System.Convert]::ToBase64String($content)
    $extension = [System.IO.Path]::GetExtension($path).ToLower()
    switch ($extension) {
        ".jpg"  { $mimeType = "image/jpeg" }
        ".jpeg" { $mimeType = "image/jpeg" }
        ".gif"  { $mimeType = "image/gif" }
        ".png"  { $mimeType = "image/png" }
        ".bmp"  { $mimeType = "image/bmp" }
        ".ico"  { $mimeType = "image/x-icon" }
        default { throw "Unknown image file type: $extension" }
    }
    $result = "data:$mimeType;base64,$base64"
    return $result
}

function Convert-Slugify {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory=$true)]
        [string]$value
        ,
        [ValidateSet('none', 'upper', 'lower', 'capital')]
        [string]$strCase = 'lower'
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $regex = [System.Text.RegularExpressions.Regex]
    $result = convert-accent -value $value
    $result = $regex::Replace($result, "[^a-zA-Z0-9\s-]", "")
    $result = $regex::Replace($result, "\s+", " ").Trim()
    $result = $regex::Replace($result, "\s", '_')
    switch ( $strCase.ToLower() ) {
        'none' {  }
        'upper' { $result = $result.ToUpper() }
        'lower' { $result = $result.ToLower() }
        'capital' { $result = $result.Substring(0,1).ToUpper() + $result.Substring(1).ToLower() }
    }
    return $result
}

Function Convert-StringCase {
    [CmdletBinding()]
    Param (
        [Parameter(Mandatory=$true)]
        [string]$value
        ,
        [Parameter(Mandatory=$false)]
        [ValidateSet('none', 'upper', 'lower', 'capital')]
        [string]$strCase = 'none'
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    switch ( $strCase.ToLower() ) {
        'none' { return $value }
        'upper' { return $value.ToUpper() }
        'lower' { return $value.ToLower() }
        'capital' { return $value.Substring(0,1).ToUpper() + $value.Substring(1).ToLower() }
    }
}

function Convert-SubnetToBitmask {
    [CmdletBinding()]
    param(
        [string]$subnet
        ,
        [int]$cidr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    try {
        if ((! $subnet) -and (! $Cidr)) {
            $subnet = Get-Subnet # get it fron local ip
        }

        if (! $cidr) {
            $cidr = Get-Cidr -subnet $subnet
        }
        $bitmask = ('1' * $cidr).PadRight(32, '0')
    }
    catch {
        $bitmask= [string]"0".PadRight(32, '0')
    }

    Return $bitmask
}

function ConvertTo-WrappedLines {
    [CmdletBinding()]
    param(
        [string]$text
        ,
        [int]$width= 80
        ,
        [switch]$asString
        ,
        [switch]$useCrLf
     )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    # standard arrays dont allow 'add' command
    $result = [System.Collections.ArrayList]@()

    # Normalize string delimiter to unix style
    $text = $text.replace("`r`n", "`n")

    # Delete all unnecessary empty lines and characters at the end of the last paragraph
    $text = $text.TrimEnd()

    # Each line break is a hard return, i.e. a paragraph
    [string[]]$paragraphs = $text -split "\n+"

    foreach ($paragraph in $paragraphs ) {

        [string[]]$rawWords = $paragraph -split "\s+"

        # Create a word list with words and make sure that no word is longer than 'width'
        $words = [System.Collections.ArrayList]@()
        foreach ($word in $rawWords ) {
            if ($word.Length -le $width) {
                $words.Add($word) | Out-Null
            } else {
                # if one word is longer then the width, split it
                [string[]]$wordparts = $word -split "(.{$width})" -ne ''
                foreach ($word in $wordparts) {
                    $words.Add($word) | Out-Null
                }
            }
        }

        # Put as many words as possible in one line, but never longer than 'width'
        [int]$column = 0
        [string]$line = ""
        foreach ($word in $words ) {
            $column += $word.Length + 1
            if ($column -gt $width ) {
                $result.add($line.trim()) | Out-Null
                $column = $word.Length + 1
                $line = ""
            }
            $line = "$line$($word) "
        }
        $result.add($line.trim()) | Out-Null
    }

    if ($asString) {
        if ($useCrLf) {
            [string]$result = $result -join "`r`n"
        } else {
            [string]$result = $result -join "`n"
        }
    }

    Return $result
}

function ConvertTo-WrappedLinesEdi {
    [CmdletBinding()]
    param(
        [string]$text
        ,
        [int]$Width = 80
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    # Standard arrays dont allow 'add' command
    $result = [System.Collections.ArrayList]@()

    # Normalize string delimiter to unix style
    $text = $text.replace("`r`n", "`n")

    # Delete all unnecessary empty lines and characters at the end of the last paragraph
    $text = $text.TrimEnd()

    # Each line break is a hard return, i.e. a paragraph
    [string[]]$paragraphs = $text -split "\n+"

    # Clear unnessecary spaces in each paragraph
    for ($i=0; $i -le $paragraphs.Count-1; $i++) {
        $paragraphs[$i] = $paragraphs[$i].Trim()
    }

    # Check if nativ lines match 2 lines and each line is less $width
     if (($paragraphs.count -eq 2) -and ($paragraphs[0].length -le $width) -and ($paragraphs[1].length -le $width)) {
        $al = $paragraphs[0]
        $a1 = $paragraphs[1]
        write-verbose "Two line meats the shema. Passed 1:1"
    } elseif (($paragraphs.count -eq 1) -and ($paragraphs[0].length -le $width) ) {
        write-verbose "First line meats the shema. Passed 1:1"
        $al = $paragraphs[0]
        $a1 = ""
    } else {
        # AT LEAST ONE LINE IS TOO LONG

        # Make one big line
        [string]$paragraph = $paragraphs -join (' ')

        # Clear double spaces
        $paragraph = $paragraph.Replace(' ', ' ')

        [string]$al = $paragraph.Substring(0,[System.Math]::Min($width, $paragraph.Length))
        [string]$a1 = $paragraph.Substring([System.Math]::Min($width, $paragraph.Length))

        # Only if there is text in A1, it could be better to make a new line wrapping
        if ($a1) {
            $punctuation = Get-PunctuationIdx $al

            [int]$maxPunctuation = $al.Length / 3 * 2
            if (($punctuation -ne -1) -and ($punctuation -lt $maxPunctuation)) {
                # if we are loosing to much chars we are dividung after the last word
                [Int]$test = $al.lastIndexOf(' ')
                if ($test -gt $punctuation) { $punctuation = $test -1 }
            }

            if ($punctuation -ge 0) {
                $a1 = "$($al.Substring($punctuation+1))$a1"
                $a1 = $a1.Trim()
                if ($a1.Length -gt $width) {
                    $a1 = "$($a1.Substring(0,$width-3))..."
                }
                $al = $al.Substring(0, $punctuation+1)
            }
        }

    }

    [string[]]$result = @()

    $result += $al.trim()
    $result += $a1.trim()
    return $result
}

Function Export-DeliveryToXml {
    [CmdletBinding()]
    param(
         [parameter(Mandatory=$false)]
         [ValidateScript({
             if ($_ -eq $null -or $_ -ge 0) {return $true} else {return $false}
         })]
         [int]$deliveryId
         ,
         [parameter(Mandatory=$false)]
         [ValidateScript({
             if ($_ -eq $null -or $_ -ge 0) {return $true} else {return $false}
         })]
         [int]$deliveryNo
         ,
         [parameter(Mandatory=$true)]
         [ValidateScript({
             switch ($PSItem[-1]) {
                 '.' {Throw 'A valid filepath cannot end with a period.'}
                 '\' {Throw 'A valid filepath cannot end with a backslash.'}
                 {$PSItem -match '\s'} {Throw 'A valid filepath cannot end with a blank character.'}
                 Default {$true}
             }
         })]
         [string]$path
         ,
         [parameter(Mandatory=$false)]
         [string]$root = 'Eulanda'
         ,
         [parameter(Mandatory=$false)]
         [string]$arrRoot = "Items"
         ,
         [parameter(Mandatory=$false)]
         [string]$arrSubRoot = "Item"
         ,
         [Parameter(Mandatory = $false)]
         $conn
         ,
         [Parameter(Mandatory = $false)]
         [string]$udl
         ,
         [Parameter(Mandatory = $false)]
         [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    try {
        [string[]]$sql= Get-DeliverySql -deliveryNo $deliveryNo -deliveryId $deliveryId
        [System.Object]$data = Get-DataFromSql -sql $sql -conn $myConn
        [xml]$xml = Convert-DataToXml -data $data -root $root -arrRoot $arrRoot -arrSubRoot $arrSubRoot
        $xml.save($path)
    }
    finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }
 }

Function Export-StockToXml {
    [CmdletBinding()]
    param(
         [parameter(Mandatory=$true)]
         [ValidateScript({
             switch ($PSItem[-1]) {
                 '.' {Throw 'A valid filepath cannot end with a period.'}
                 '\' {Throw 'A valid filepath cannot end with a backslash.'}
                 {$PSItem -match '\s'} {Throw 'A valid filepath cannot end with a blank character.'}
                 Default {$true}
             }
         })]
         [string]$path
         ,
         [parameter(Mandatory=$false)]
         [string[]]$filter
         ,
         [parameter(Mandatory=$false)]
         [string]$alias
         ,
         [parameter(Mandatory=$false)]
         [int]$qtyStatic
         ,
         [parameter(Mandatory=$false)]
         [string]$warehouse
         ,
         [parameter(Mandatory=$false)]
         [switch]$legacy
         ,
         [Parameter(Mandatory = $false)]
         $conn
         ,
         [Parameter(Mandatory = $false)]
         [string]$udl
         ,
         [Parameter(Mandatory = $false)]
         [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    try {
        if ((! $warehouse) -and (! $legacy)) {
            [string]$nodeName = 'LAGERLISTE'
        } else {
            [string]$nodeName = 'LAGER'
        }

        [string[]]$sql= Get-StockSql -filter $filter -alias $alias -qtyStatic $qtyStatic -warehouse $warehouse -legacy:$legacy
        [System.Object]$data = Get-DataFromSql -sql $sql -conn $myConn -arrRoot $nodeName
        [xml]$xml = Convert-DataToXml -data $data -metadata -root 'EULANDA' -arrRoot 'ARTIKELLISTE' -arrSubRoot 'ARTIKEL'
        $xml.save($path)
    }
    finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }
 }

Function Find-MssqlBrowser {
    [CmdletBinding()]
    param(
        [string]$fromIp
        ,
        [string]$toIp
        ,
        [int]$port = 1434
        ,
        [int]$timeout = 300
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $SqlInstances = New-Object System.Collections.ArrayList
    if ((! $toIp) -and ($fromIp)) {
        $result = Find-SqlBrowser -ip $fromIp -port $port -timeout $timeout
        $SqlInstances += $result
    } else {
        if (! $fromIp) {
            $fromIp = Get-FirstIp
            $toIp = Get-LastIp
        }

        [bool]$run = $true
        do {
            Write-Verbose $fromIp
            $result = Find-SqlBrowser -ip $fromIp -port $port -timeout $timeout
            $SqlInstances += $result
            if (($fromIp) -eq ($toIp)) {
                $run = $false
            } else {
                $fromIp = Get-NextIp -ip $fromIp
            }
        } while ($run)

    }
    Return $SqlInstances
}

function Format-Xml {
    param(
        [Parameter(Position=0, Mandatory=$true, ParameterSetName='Text')]
        [string]$text
        ,
        [Parameter(Position=0, Mandatory=$true, ParameterSetName='Path')]
        [ValidateScript( { if ([string]::IsNullOrEmpty($_)) { return $true } else { (Test-Path $_ ) } },
        ErrorMessage= "The file '{0}' does not exist or the path is incorrect!" )]
        [string]$pathIn
        ,
        [Parameter(Position=1, Mandatory = $false)]
        [ValidateScript( { if ([string]::IsNullOrEmpty($_)) { return $true } else { Test-Path (Split-Path $_ -Parent) } },
        ErrorMessage= "The path '{0}' does not exist!" )]
        [string]$pathOut
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    if ($PSCmdlet.ParameterSetName -eq 'Path') {
        $text = Get-Content -Path $pathIn
    }

    # If no encoding exists (common with FoxNet), use a default encoding
    $p = $text.IndexOf("<?")
    if ($p -lt 0) {
        $text = ('<?xml version="1.0" encoding="UTF-8"?>' + $text)
    }

    [System.Collections.ArrayList]$data = New-Object System.Collections.ArrayList
    $data.Add($text -join "`n") | Out-Null
    [System.Xml.XmlDataDocument]$xml= New-Object System.Xml.XmlDataDocument
    $xml.LoadXml($data -join "`n")
    [System.IO.StringWriter]$strWriter= New-Object System.Io.Stringwriter
    [System.Xml.XmlTextWriter]$writer= New-Object System.Xml.XmlTextWriter($strWriter)
    $writer.Formatting = [System.Xml.Formatting]::Indented
    $xml.WriteContentTo($writer)
    if ($pathOut) {
        $strWriter.ToString() | Out-File -FilePath $pathOut -Encoding UTF8
    } else {
        $strWriter.ToString()
    }
}

function Get-AddressId {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $false)]
        [string]$match
        ,
        [Parameter(Mandatory = $false)]
        [ValidateRange("Positive")]
        [int]$id
        ,
        [Parameter(Mandatory = $false)]
        [switch]$like
        ,
        [Parameter(Mandatory = $false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    [int]$result = 0
    if ($Id) {
        [string]$sqlFrag = "Id = $id"
    } elseif ($match) {
        if ($like) {
            [string]$sqlFrag = "Match like '$match%'"
        } else {
            [string]$sqlFrag = "Match = '$match'"
        }
    } else {
        throw "Neither a id is specified, nor an match! Function: $($myInvocation.MyCommand)"
    }
    try {
        [string]$sql = "SELECT Id FROM Adresse WHERE $sqlFrag"
        $rs = $Null
        $rs = $myConn.Execute($sql)
        if ($rs) {
            if (! $rs.eof) {
                [int]$result =  $rs.fields('Id').Value
                if ($rs.RecordCount -gt 1) {
                    throw "The search value '$sqlFrag' gives more then one result! " +`
                          "Function: $($myInvocation.MyCommand)"
                }
            }
        }
    } finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }

    Return $result
}

function Get-AdoRs {
    [CmdletBinding()]
    param(
        [parameter(Mandatory=$true)]
        $recordset
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    if ($recordset) {
        # Toggle all record sets until you find an open one
        Do {
            If ($recordset.State -ne $global:adStateOpen) {
                $recordset = $recordset.NextRecordset()
            }
        } until ( (! $recordset) -or ($recordset.State -eq $global:adStateOpen) )

        if ($recordset) {
            if ($recordset.eof) {
                $recordset= $null
            }
        }
    }

    Return $recordset
}

function Get-ArticleId {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $false)]
        [string]$barcode
        ,
        [Parameter(Mandatory = $false)]
        [string]$articleNo
        ,
        [Parameter(Mandatory = $false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    [int]$result = 0
    try {
        if ($barcode) {
            [string]$sqlFrag = "Barcode = '$barcode'"
        } elseif ($articleId) {
            [string]$sqlFrag = "ArtNummer = '$articleNo'"
        } else {
            throw "Neither a barcode is specified, nor a article number! Function: $($myInvocation.MyCommand)"
        }
        [string]$sql = "SELECT Id FROM Artikel WHERE $sqlFrag"

        $rs = $Null
        $rs = $myConn.Execute($sql)
        if ($rs) {
            if (! $rs.eof) {
                $result =  $rs.fields('Id').Value
            }
        }
    } finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }

    Return $result
}

function Get-ArticleNo {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $false)]
        [string]$barcode
        ,
        [Parameter(Mandatory = $false)]
        [ValidateRange("Positive")]
        [int]$articleId
        ,
        [Parameter(Mandatory = $false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    [string]$result = ""
    try {
        if ($barcode) {
            [string]$sqlFrag = "Barcode = '$barcode'"
        } elseif ($articleId) {
            [string]$sqlFrag = "Id = $articleId"
        } else {
            throw "Neither a barcode is specified, nor a article id! Function: $($myInvocation.MyCommand)"
        }
        $sql = "SELECT ArtNummer FROM Artikel WHERE $sqlFrag"

        $rs = $Null
        $rs = $myConn.Execute($sql)
        if ($rs) {
            if (! $rs.eof) {
                $result =  $rs.fields('ArtNummer').Value
            }
        }
    } finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }

    Return $result
}

function Get-ArticlePackingUnit {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $true)]
        [ValidateRange("Positive")]
        [Int]$articleId
        ,
        [Parameter(Mandatory = $false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    [int]$result = 1  # Default packing unit is 1
    try {
        [string]$sql = "SELECT VerpackEH [PackingUnit] FROM Artikel WHERE Id = $articleId"
        $rs = $Null
        $rs = $myConn.Execute($sql)
        if ($rs) {
            if (! $rs.eof) {
                [int]$result =  $rs.fields('PackingUnit').Value
            }
        }
    } finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }

    Return $result
}

function Get-ArticleSql {
    [CmdletBinding()]
    param(
        [parameter(Position = 0, Mandatory=$false)]
        [ValidateScript({
            if ($_ -eq "") {
              throw "Parameter 'select' cannot be an empty string!"
            }
            return $true
          })]
        [string]$select= '*'
        ,
        [parameter(Position = 1, Mandatory=$false)]
        [string[]]$filter
        ,
        [parameter(Position = 2, Mandatory=$false)]
        [string]$order= $alias
        ,
        [Parameter(Position = 3, Mandatory=$false)]
        [ValidateSet('none', 'upper', 'lower', 'capital')]
        [string]$strCase = 'none'
        ,
        [parameter(Position = 4, Mandatory=$false)]
        [ValidateSet('Id', 'Uid', 'ArtNummer', 'Barcode', IgnoreCase = $true)]
        [string]$alias = 'ArtNummer'
        ,
        [parameter(Mandatory=$false)]
        [switch]$noIdAlias
        ,
        [parameter(Mandatory=$false)]
        [ValidateRange(0, [int]::MaxValue)]
        [Nullable[int]]$limit
        ,
        [parameter(Mandatory=$false)]
        [switch]$reorder
        ,
        [parameter(Mandatory=$false)]
        [switch]$revers
    )

    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    if ($filter) {
        [string]$sqlFilter= "WHERE (NOT $alias IS NULL) AND $($filter -join(' AND ')) "
    } else {
        [string]$sqlFilter= 'WHERE (NOT $alias IS NULL)'
    }

    if ($order) {
        [string]$sqlOrder = "ORDER BY $order"
    } else {
        [string]$sqlOrder = ''
    }

    $sqlSelect = $select
    if ($sqlSelect -ne '*') {
        if (! ($SqlSelect.Contains($alias, [System.StringComparison]::InvariantCultureIgnoreCase))) {
            $sqlSelect = "$alias,$sqlSelect"
        }

        if ($order) {
            [string[]]$arrOrder = $order -split ','
            for ($i=0; $i -lt $arrOrder.count-1; $i++) {
                # Delete ASC and DESC
                $arrOrder[$i] =  $arrOrder[$i].Split(' ')[0]
                if ($SqlSelect.Contains($arrOrder[$i], [System.StringComparison]::InvariantCultureIgnoreCase)) {
                    $arrOrder[$i] = ''
                }
            }
            $arrOrder = $arrOrder | Where-Object { $_ -ne "" }
            $fieldList = $arrOrder -join ','
            if ($fieldList) {
                $sqlSelect = "$fieldlist,$SqlSelect"
            }
        }
    }

    if (! ($null -eq $limit)) {
        $sqlLimit = "TOP $limit"
    } else {
        $SqlLimit = ''
    }

    if ($revers) {
        $sqlRevers = 'DESC'
    } else {
        $sqlRevers = ''
    }

    $arrSelect = $sqlSelect -split ','
    $arrSelect = $arrSelect | ForEach-Object { $_.Trim() }
    if ($reorder) {
          $arrSelect = $arrSelect | Sort-Object
    }
    $arrSelect = $arrSelect | ForEach-Object { Convert-StringCase -value $_ -strCase $strcase }
    $sqlSelect = $arrSelect -join ','

    if (! ($noIdAlias)) {
        $sqlSelect = "$alias [ID.ALIAS], $sqlSelect"
    }



    [string]$sqlMaster = @"
        SELECT $sqlSelect FROM (
        SELECT
 
        $sqlLimit
 
        /* KEYS */
        Id, ArtNummer, IsNull(Barcode,'') [Barcode], [Uid],
 
        /* IDENTIFIER */
        IsNull(ArtMatch,'') [ArtMatch], IsNull(ArtNummerErsatz,'') [ArtNummerErsatz],
        IsNull(ArtNummerHersteller,'') [ArtNummerHersteller],
 
        /* GROUPS */
        IsNull(RabattGr,'') [RabattGr], IsNull(WarenGr,'') [WarenGr],
        IsNull(ErloesGr,'') [ErloesGr], VerpackEh, PreisEh, MengenEh,
 
        /* CURRENCY */
        Waehrung, MwStGr, MwStSatz, EkNetto, IsNull(Ek2Netto,0.0)[Ek2Netto], BruttoFlg,
        Vk, VkNetto, VkBrutto,
 
        /* SHOP */
        IsNull(ShopExportDatum,'1900-01-01') [ShopExportDatum], ShopFreigabeFlg,
 
        /* FLAGS */
        AuslaufFlg, NeuFlg, SonderFlg,
 
        /* INTRASTAT */
        IsNull(Upper(UrsprungsLand),'')[UrsprungsLand],
        IsNull(UrsprungsRegion,'') [UrsprungsRegion], IsNull(WarenNr,'') [WarenNr],
 
        /* DESCRIPTION */
        IsNull(Ultrakurztext,'') [UltraKurztext], IsNull(Kurztext1,'') [Kurztext1],
        IsNull(Kurztext2,'') [Kurztext2], IsNull(Info,'') [Info], IsNull(Langtext,'') [Langtext],
 
        /* OTHER */
        Gewicht, Volumen, IsNull(Warnung,'') [Warnung], IsNull(Lagerplatz,'') [Lagerplatz]
 
        FROM Artikel
 
        $sqlFilter
        ) Dummy
 
        $sqlOrder $sqlRevers
"@

    Return [string[]]@($sqlMaster)
}

function Get-Bool {
    [CmdletBinding()]
    Param (
        [Parameter(Mandatory = $true)]
        [string]$boolStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $boolStr = $boolStr.ToUpper()
    if (($boolStr -eq "1") -or ($boolStr -eq "$TRUE") -or ($boolStr -eq "TRUE")) {
        [bool]$result = $true
    } else {
        [bool]$result = $false
    }
    Return $result
}

function Get-BroadcastIp {
    [CmdletBinding()]
    param(
        [string]$networkId
        ,
        [string]$ip
        ,
        [string]$subnet
        ,
        [int]$cidr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    try {
        if ((! $subnet) -and (! $cidr)) {
            $subnet = Get-Subnet # get it from localIp
        }

        if (! $networkId) {
            $networkId = Get-NetworkId -ip $ip -subnet $subnet -cidr $cidr
        }
        $maxHosts = Get-MaxHosts -subnet $subnet -cidr $cidr
        $broadcastIp = Get-NextIp -ip $networkId -inc ($maxHosts + 1)
    }
    catch {
        $broadcastIp= '0.0.0.0'
    }

    Return $broadcastIp
}

function Get-Cidr {
    [CmdletBinding()]
    param(
        [string]$subnet
        ,
        [string]$ip
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    try {
        if (! $subnet) {
            if (! $ip) {
                $ip= Get-LocalIp
            }
            $ipObj= [IPAddress]::Parse($ip)
            $cidr =  (Get-NetIPAddress -AddressFamily IPv4 -IPAddress $ipObj -ErrorAction SilentlyContinue).PrefixLength
        } else {
            $subnetBytes = $subnet.Split('.') | ForEach-Object { [byte] $_ }
            $subnetBitString = [Convert]::ToString($subnetBytes[0], 2).PadLeft(8, '0') + `
                                  [Convert]::ToString($subnetBytes[1], 2).PadLeft(8, '0') + `
                                  [Convert]::ToString($subnetBytes[2], 2).PadLeft(8, '0') + `
                                  [Convert]::ToString($subnetBytes[3], 2).PadLeft(8, '0')
            $cidr = $subnetBitString.Replace('0','').Length
        }
    }
    catch {
        $cidr= 0
    }

    Return $cidr
}

function Get-Conn {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [long]$adStateClosed = 0

    $global:adCloseOnExit = $false
    if ($conn) {
        $myConn = $conn
        if ($myConn.state -eq $adStateClosed) {
            $myConn.open()
            $global:adCloseOnExit = $true
        }
    } elseif ($udl) {
        $myConn = Get-ConnFromUdl -udl $udl
        $global:adCloseOnExit = $true
    } elseif ($connStr) {
        $myConn = Get-ConnFromStr $connStr
        $global:adCloseOnExit = $true
    } else {
        throw "Neither a ado connection object, or udl filename or an connection string is specified. Function: $($myInvocation.MyCommand)"
    }

    if ($global:adCloseOnExit) {
        # Avoid warning of vscode, because the variable is not used
    }
    Return $myConn
}

function Get-ConnItems {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    try {
        $params = $myConn.ConnectionString.Split(';')
        $conStrList = @{}
        foreach ($param in $params) {
            $key, $value = $param.Split('=')
            if (($key) -and ($value)) {
                $key = $key.trim()
                $value = $value.trim()
                $conStrList[$key] = $value
            }
        }
    } finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }
    Return $conStrList
}

function Get-ConnStr {
    [CmdletBinding()]
    param(
        [parameter(Mandatory=$true)]
        [string]$database
        ,
        [parameter(Mandatory=$false)]
        [string]$server
        ,
        [parameter(Mandatory=$false)]
        [string]$user
        ,
        [parameter(Mandatory=$false)]
        [string]$password
        ,
        [parameter(Mandatory=$false)]
        $conn
        ,
        [ValidateScript({
            if($_) {
                if(!(Test-Path $_)) {
                    throw "UDL file '$_' did not exist!"
                }
            }
            return $true
        })]
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    if (! ($server)) {
        $connItems = Get-ConnItems -conn $conn -udl $udl -connStr $connStr
        $server= $connItems['Data Source']
        $user= $connItems['User ID']
        $password= $connItems['Password']
    }

    if (($user) -and ($password)) {
        [string]$connStr = `
            "Provider=SQLOLEDB.1;Data Source=$server;Initial Catalog=$database;" +`
            "Persist Security Info=True;User ID=$user;Password=$password"
    } else {
    [string]$connStr = "Provider=SQLOLEDB.1;Data Source=$server;Initial Catalog=$database;Integrated Security=SSPI"
    }
    Return $connStr
}

function Get-DataFromSql {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory=$true)]
        [string[]]$sql
        ,
        [Parameter(Mandatory=$false)]
        [string]$arrRoot = 'Items'
        ,
        [Parameter(Mandatory = $false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    $data = $null
    try {
        $data = New-Object System.Collections.ArrayList
        $rs = $Null
        $rs = $myConn.Execute($sql[0])

        # Toggle all record sets until you find an open one
        Do {
            If ($rs.State -ne $global:adStateOpen) {
                $rs = $rs.NextRecordset()
            }
        } until ( (! $rs) -or ($rs.State -eq $global:adStateOpen) )


        if ($rs) {
            while (! $rs.eof) {
                $record = [ordered]@{}
                for ($i=0; $i -lt $rs.fields.count; $i++) {
                    $field = $rs.fields[$i]
                    $record.add($field.name, $field.value) | Out-Null
                }
                if ($sql.Count -gt 1) {
                    # Detail exists
                    [string]$sqlTemp= $sql[1]
                    if ($sql.Count -gt 2) {
                        # Master / Detail Link exists
                        [string]$sqlLink= $rs.fields[$sql[2]].value.replace("'","''")
                        $sqlTemp = ($sqlTemp -f $sqlLink)
                    }
                    $record.add($arrRoot, (Get-DataFromSql -sql @($sqlTemp) -conn $myConn -udl $udl -connStr $connStr )) | Out-Null
                }
                $data.Add($record) | Out-Null
                $rs.MoveNext() | Out-Null
            }
        }
    } finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }
    Return $data
}

function Get-DeliveryId {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [ValidateRange("Positive")]
        [int]$deliveryNo
        ,
        [Parameter(Mandatory = $false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    [int]$deliveryId = 0
    try {
        $rs = $Null
        [string]$sql = "SELECT Id [DeliveryId] FROM Lieferschein WHERE KopfNummer = $deliveryNo"
        $rs = $myConn.Execute($sql)
        if ($rs) {
            if (! $rs.eof) {
                $deliveryId = $rs.fields('DeliveryId').value
            }
        }
    } finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }

    Return $deliveryId
}

function Get-DeliveryLink {
    [CmdletBinding()]
    Param (
        [parameter(Mandatory=$false)]
        [ValidateScript({
            if ($_ -eq $null -or $_ -ge 0) {return $true} else {return $false}
        })]
        [int]$deliveryId
        ,
        [parameter(Mandatory=$false)]
        [ValidateScript({
            if ($_ -eq $null -or $_ -ge 0) {return $true} else {return $false}
        })]
        [int]$deliveryNo
        ,
        [Parameter(Mandatory = $false)]
        [string]$alias
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    if ($alias) { $alias = "$alias." }

    if ($deliveryId) {
        [string]$result = "$($alias)Id = $deliveryId"
    } elseif ($deliveryNo) {
        [string]$result = "$($alias)KopfNummer = $deliveryNo"
    } else {
        throw "The delivery note cannot be loaded, either parameter DeliveryId or " +`
              "DeliveryNo must be specified. Function: $($myInvocation.MyCommand)"
    }
    Return [string]$result
}

function Get-DeliveryNo {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [ValidateRange("Positive")]
        [int]$deliveryId
        ,
        [Parameter(Mandatory = $false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    [int]$deliveryNo = 0
    try {
        $sql = "SELECT KopfNummer [DeliveryNo] FROM Lieferschein WHERE Id = $deliveryId"
        $rs = $Null
        $rs = $myConn.Execute($sql)
        if ($rs) {
            if (! $rs.eof) {
                $deliveryNo = $rs.fields('DeliveryNo').value
            }
        }
    } finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }

    Return $deliveryNo
}

function Get-DeliveryQty {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $true)]
        [ValidateRange("Positive")]
        [int]$deliveryNo
        ,
        [Parameter(Mandatory = $false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [long]$adUseClient = 3
    [long]$adOpenKeyset = 1
    [long]$adLockOptimistic = 3
    [long]$adCmdText = 1

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    try {
        [hashtable]$result = [ordered]@{}
        [string]$article = ""
        [float]$quantity = 0.0

        # The barcode is being misused here as an article no.
        [string]$sql = @"
        SELECT Art.Barcode [Article], lfp.Menge [Quantity] FROM LieferscheinPos [lfp]
        JOIN Lieferschein [lf] ON lfp.KopfId = lf.id
        JOIN Artikel [art] ON art.Id = lfp.ArtikelId
        WHERE lf.KopfNummer = $deliveryNo ORDER BY lfp.PosNummer
"@
        $rs = $Null
        $rs = new-object -comObject ADODB.Recordset
        $rs.CursorLocation = $adUseClient
        $rs.Open($sql, $myConn, $adOpenKeyset, $adLockOptimistic, $adCmdText)
        if ($rs.eof) {
            throw "The delivery note '$deliveryNo' does not exist in EULANDA-ERP system. " +`
                  "EULANDA DeliveryNo: $deliveryNo"
        }
        while (! $rs.eof) {
            [string]$article = $rs.fields('Article').value
            [float]$quantity = $rs.fields('Quantity').value

            # Under certain circumstances, the delivery note may contain the same article several times
            if (! $result.ContainsKey($article)) {
                $result.Add($article, $quantity) | Out-Null
            } else {
                $result.Item($article) += $quantity
            }
            $rs.MoveNext() | Out-Null
        }
    } finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }

    Return $result
}

function Get-DeliverySql {
    [CmdletBinding()]
    param(
        [parameter(Mandatory=$false)]
        [ValidateScript({
            if ($_ -eq $null -or $_ -ge 0) {return $true} else {return $false}
        })]
        [int]$deliveryId
        ,
        [parameter(Mandatory=$false)]
        [ValidateScript({
            if ($_ -eq $null -or $_ -ge 0) {return $true} else {return $false}
        })]
        [int]$deliveryNo
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [string]$sqlMaster = "SELECT Name1, Name2, Name3, Strasse [Street], Plz [Zip], " + `
        "Ort [City], Provinz [Province], Land [Country], dbo.cnf_LfDruckAnschrift(Id) [Address], " + `
        "FibuKonto [CustomerNo], " +`
        "Id [DeliveryId],KopfNummer [DeliveryNo], Datum [DeliveryDate], " + `
        "af_id [SalesOrderId], af_Nummer [SalesOrderNo], af_Datum [SalesOrderDate], " + `
        "Objekt [Reference], af_BestellNummer [CustomerOrderNo] " +`
        "FROM PRINT_Lieferschein WHERE $(Get-DeliveryLink -deliveryNo $deliveryNo -deliveryId $deliveryId) "

    [string]$sqlDetail = "SELECT lfp.PosNummer [Pos], lfp.Menge [Qty], art.Id [ArticleId], " + `
        "art.ArtNummer [ArticleNo], " + `
        "lfp.Langtext [Description], art.WarenNr [TradeNo], (lfp.Menge / lfp.VerpackEH) [Units], " + `
        "CAST(((lfp.Menge + lfp.VerpackEH -1 ) / lfp.VerpackEH) AS int) [Boxes], art.Gewicht [Weight] " + `
        "FROM LieferscheinPos [lfp] " + `
        "JOIN Artikel [art] ON art.Id = lfp.ArtikelId " + `
        "JOIN Lieferschein [lf] ON lf.Id = lfp.KopfId " + `
        "WHERE $(Get-DeliveryLink -deliveryNo $deliveryNo -deliveryId $deliveryId -alias 'lf') " + `
        "ORDER BY Pos"

    Return [string[]]@($sqlMaster,$sqlDetail)
}

function Get-DeliveryStatus {
    [CmdletBinding()]
    param (
        [parameter(Mandatory=$false)]
        [ValidateScript({
            if ($_ -eq $null -or $_ -ge 0) {return $true} else {return $false}
        })]
        [int]$deliveryId
        ,
        [parameter(Mandatory=$false)]
        [ValidateScript({
            if ($_ -eq $null -or $_ -ge 0) {return $true} else {return $false}
        })]
        [int]$deliveryNo
        ,
        [Parameter(Mandatory = $false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    if ($deliveryId)  {
        [string]$sqlFrag = "Id = $deliveryId"
    } elseif ($deliveryNo) {
        [string]$sqlFrag = "KopfNummer = $deliveryNo"
    } else {
        throw "Neither a delivery note id is specified, nor a delivery note number. Function: $($myInvocation.MyCommand)"
    }

    [int]$result = 0
    try {
        [string]$sql = "SELECT Status FROM Lieferschein WHERE $sqlFrag"
        $rs = $Null
        $rs = $myConn.Execute($sql)
        if ($rs) {
            if (! $rs.eof) {
                $result =  $rs.fields('Status').value
            } else { $rs = $null }
        }
    } finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }

    if (! $rs) {
        throw "The delivery note cannot be loaded! Sql: $sql"
    }

    Return $result
}

function Get-DesktopDir {
    [CmdletBinding()]
    param()
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [string]$desktopDir = [Environment]::GetFolderPath("Desktop")
    return $desktopDir
}

function Get-DmsFolderDelivery {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [string]$dmsBaseFolder
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({
            if ($_ -eq $null -or $_ -ge 0) {return $true} else {return $false}
        })]
        [int]$deliveryNo
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({
            if ($_ -eq $null -or $_ -ge 0) {return $true} else {return $false}
        })]
        [int]$deliveryId
        ,
        [Parameter(Mandatory = $false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    try {
        if ($deliveryNo) {
            [string]$condition = "lf.KopfNummer = $deliveryNo"
        } elseif ($deliveryId) {
            [string]$condition = "lf.Id = $deliveryId"
        } else {
            throw "Delivery note could not be found because of missing parameter. Function $($myInvocation.MyCommand)"
        }

        [string]$match = ""
        [int]$deliveryNo = 0

        [string]$sql = "SELECT ladr.Match [Match], lf.KopfNummer [DeliveryNo] FROM Lieferschein [lf] `r`n" +`
            "JOIN Adresse [ladr] ON lf.AdresseId = ladr.Id AND $condition"

        $rs = $Null
        $rs = $myConn.Execute($sql)
        if ($rs) {
            if (! $rs.eof) {
                $match = $rs.fields('Match').value
                $deliveryNo = $rs.fields('DeliveryNo').value
            }
        } else {
            throw "Delivery note could not be found for condition '$condition'. Function: $($myInvocation.MyCommand)"
        }
    } finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }

    [string]$result = "$dmsBaseFolder\Adressen\$match\Lieferscheine\$deliveryNo"

    Return $result
}

function Get-DmsFolderSalesOrder {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [string]$dmsBaseFolder
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({
            if ($_ -eq $null -or $_ -ge 0) {return $true} else {return $false}
        })]
        [int]$salesOrderNo
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({
            if ($_ -eq $null -or $_ -ge 0) {return $true} else {return $false}
        })]
        [int]$salesOrderId
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({
            if ($_ -eq $null -or $_ -ge 0) {return $true} else {return $false}
        })]
        [int]$customerOrderNo
        ,
        [Parameter(Mandatory = $false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    try {
        if ($salesOrderId) {
            [string]$condition = "so.Id = $salesOrderId"
        } elseif ($salesOrderNo) {
            [string]$condition = "so.KopfNummer = $salesOrderNo"
        } elseif ($customerOrderNo) {
            [string]$condition = "so.Bestellnummer = '$customerOrderNo'"
        } else {
            throw "Sales order could not be found because of missing parameter. Function: $($myInvocation.MyCommand)"
        }

        [string]$match = ""
        [int]$salesOrderNo = 0

        [string]$sql = "SELECT radr.Match [Match], so.KopfNummer [SalesOrderNo] FROM Auftrag [so] `r`n" +`
            "JOIN Adresse [radr] ON so.AdresseId = radr.Id AND $condition"

        $rs = $Null
        $rs = $myConn.Execute($sql)
        if ($rs) {
            if (! $rs.eof) {
                $match = $rs.fields('Match').value
                $salesOrderNo = $rs.fields('SalesOrderNo').value
            }
        } else {
            throw "Delivery note could not be found for condition '$condition'. Function: $($myInvocation.MyCommand)"
        }
    } finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }

    [string]$result = "$dmsBaseFolder\Adressen\$match\Aufträge\$salesOrderNo"

    Return $result
}

function Get-FieldTruncated {
    [CmdletBinding()]
    Param(
        [Parameter(Mandatory = $true)]
        $rs
        ,
        [Parameter(Mandatory = $true)]
        [string]$fieldName
        ,
        [Parameter(Mandatory = $true)]
        [ValidateRange("Positive")]
        [int]$maxLen
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [string]$Value = $rs.fields($fieldName).Value
    $value = $value.Trim()
    $value = $value.Substring(0, [System.Math]::Min($maxLen, $value.Length))
    $value = $value.Trim()

    Return $value
}

function Get-Filename {
    [CmdletBinding()]
    Param (
        [Parameter(Mandatory = $true)]
        [ValidateScript({
            switch ($PSItem[-1]) {
                '.' {Throw 'A valid filepath cannot end with a period.'}
                '\' {Throw 'A valid filepath cannot end with a backslash.'}
                {$PSItem -match '\s'} {Throw 'A valid filepath cannot end with a blank character.'}
                Default {$true}
            }
        })]
        [string]$path
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    Return [string]((Split-Path -Path $path -Leaf).Split(".")[0] + '.' + (Split-Path -Path $path -Leaf).Split(".")[1])
}

function Get-FirstIp {
    [CmdletBinding()]
    param(
        [string]$networkId
        ,
        [string]$ip
        ,
        [string]$subnet
        ,
        [int]$cidr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    try {
        if ((! $subnet) -and (! $Cidr)) {
            $subnet = Get-Subnet # get it from loacalIp
        }

        if (! $networkId) {
            $networkId = Get-NetworkId -ip $ip -subnet $subnet -cidr $cidr
        }
        $firstIp = Get-NextIp -ip $networkId
    }
    catch {
        $firstIp= '0.0.0.0'
    }

    Return $firstIp
}

function Get-GatewayIp {
    [CmdletBinding()]
    param()
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    try {
        $gatewayIp= (Get-NetRoute -AddressFamily IPv4 |
        Where-Object DestinationPrefix -eq '0.0.0.0/0' |
        Select-Object NextHop).NextHop
    }
    catch {
        $gatewayIp='0.0.0.0'
    }

    Return $gatewayIp
}

function Get-Hostname {
    [CmdletBinding()]
    param(
        [string]$ip
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    try {
        if (! $ip) {
            $ip= Get-LocalIp
        }
        $hostname= [System.Net.Dns]::GetHostByAddress($ip).Hostname
    }
    catch {
        $hostname= ''
    }

    Return $hostname
}

function Get-HtmlEncoded {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $false)]
        [string]$taggedString
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $taggedString = $taggedString.replace('\r', '')
    $taggedString = $taggedString.replace('\n', '{br/}')

    # Replace <br> and <strong> tags with placeholders
    $taggedString = $taggedString -replace "`r", ''
    $taggedString = $taggedString -replace "`n", '{br/}'
    $taggedString = $taggedString -replace '<br>', '{br/}'
    $taggedString = $taggedString -replace '<br/>', '{br/}'
    $taggedString = $taggedString -replace '<br />', '{br/}'

    $taggedString = $taggedString -replace '<strong>', '{strong}'
    $taggedString = $taggedString -replace '</strong>', '{/strong}'
    $taggedString = $taggedString -replace '<b>', '{b}'
    $taggedString = $taggedString -replace '</b>', '{/b}'
    $taggedString = $taggedString -replace '<i>', '{i}'
    $taggedString = $taggedString -replace '</i>', '{/i}'
    $taggedString = $taggedString -replace '<h1>', '{h1}'
    $taggedString = $taggedString -replace '</h1>', '{/h1}'
    $taggedString = $taggedString -replace '<h2>', '{h2}'
    $taggedString = $taggedString -replace '</h2>', '{/h2}'
    $taggedString = $taggedString -replace '<h3>', '{h3}'
    $taggedString = $taggedString -replace '</h3>', '{/h3}'
    $taggedString = $taggedString -replace '<p>', '{p}'
    $taggedString = $taggedString -replace '</p>', '{/p}'
    $taggedString = $taggedString -replace '<pre>', '{pre}'
    $taggedString = $taggedString -replace '</pre>', '{/pre}'
    $taggedString = $taggedString -replace '<ul>', '{ul}'
    $taggedString = $taggedString -replace '</ul>', '{/ul}'
    $taggedString = $taggedString -replace '<ol>', '{ol}'
    $taggedString = $taggedString -replace '</ol>', '{/ol}'
    $taggedString = $taggedString -replace '<li>', '{li}'
    $taggedString = $taggedString -replace '</li>', '{/li}'

    # Encode the string
    [string]$taggedString = [System.Web.HttpUtility]::HtmlEncode($taggedString)

    # Replace the placeholders with the original tags
    $taggedString = $taggedString -replace '{br/}', '<br/>'
    $taggedString = $taggedString -replace '{strong}', '<strong>'
    $taggedString = $taggedString -replace '{/strong}', '</strong>'
    $taggedString = $taggedString -replace '{b}', '<b>'
    $taggedString = $taggedString -replace '{/b}', '</b>'
    $taggedString = $taggedString -replace '{i}', '<i>'
    $taggedString = $taggedString -replace '{/i}', '</i>'
    $taggedString = $taggedString -replace '{h1}', '<h1>'
    $taggedString = $taggedString -replace '{/h1}', '</h1>'
    $taggedString = $taggedString -replace '{h2}', '<h2>'
    $taggedString = $taggedString -replace '{/h2}', '</h2>'
    $taggedString = $taggedString -replace '{h3}', '<h3>'
    $taggedString = $taggedString -replace '{/h3}', '</h3>'
    $taggedString = $taggedString -replace '{p}', '<p>'
    $taggedString = $taggedString -replace '{/p}', '</p>'
    $taggedString = $taggedString -replace '{pre}', '<pre>'
    $taggedString = $taggedString -replace '{/pre}', '</pre>'
    $taggedString = $taggedString -replace '{ul}', '<ul>'
    $taggedString = $taggedString -replace '{/ul}', '</ul>'
    $taggedString = $taggedString -replace '{ol}', '<ol>'
    $taggedString = $taggedString -replace '{/ol}', '</ol>'
    $taggedString = $taggedString -replace '{li}', '<li>'
    $taggedString = $taggedString -replace '{/li}', '</li>'

    Return $taggedString
}

function Get-HtmlMetaData {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $false)]
        [string]$description
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [hashtable]$metaData = @{
        author="EULANDA Software GmbH - ERP-Systems - Germany"
        generator="Powershell $($PsVersiontable.GitCommitId) by function ConvertTo-Html"
        keywords="eulanda, erp, powershell, convertto-html, html"
        viewport="width=device-width, initial-scale=1.0"
    }
    if ($description) {
        $metaData.Add('description', $description) | Out-Null
    }
    return $metaData
}

function Get-HtmlStyle {
    [CmdletBinding()]
    param()
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [string]$style = @"
    <style>
    html {
        margin: 0 auto;
        max-width: 800px;
    }
    body {
        font-family: Arial, Helvetica, sans-serif;
        font-size: 14px;
    }
    h1 {
        font-family: Arial, Helvetica, sans-serif;
        color: #e68a00;
        font-size: 28px;
    }
    h2 {
        font-family: Arial, Helvetica, sans-serif;
        color: #000099;
        font-size: 16px;
    }
    h3 {
        font-family: Arial, Helvetica, sans-serif;
        font-size: 16px;
    }
    table {
        font-size: 12px;
        border: 0px;
        width: 600px;
        height: auto;
        font-family: Arial, Helvetica, sans-serif;
    }
    td {
        padding: 4px;
        margin: 0px;
        border: 0;
        vertical-align: top;
    }
    th {
        background: #395870;
        background: linear-gradient(#49708f, #293f50);
        color: #fff;
        font-size: 11px;
        text-transform: uppercase;
        padding: 10px 15px;
        vertical-align: middle;
    }
    tbody tr:nth-child(even) {
    background-color: #f0f0f2;
    }
    #CreationDate {
        font-family: Arial, Helvetica, sans-serif;
        color: #ff3300;
        font-size: 12px;
    }
    .StatusAttention {
        color: #ff0000;
    }
    .StatusNormal {
        color: #008000;
    }
    </style>
"@

   Return $style
}

function Get-IniBool {
    [CmdletBinding()]
    param(
        [string]$section
        ,
        [string]$variable
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    if (test-path variable:global:ini) {
        [string]$test = $global:ini[$section][$variable]
        Return Get-Bool -boolStr $test
    } else {
        Return $false
    }
}

function Get-LastIp {
    [CmdletBinding()]
    param(
        [string]$networkId
        ,
        [string]$ip
        ,
        [string]$subnet
        ,
        [int]$cidr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    try {
        if ((! $subnet) -and (! $Cidr)) {
            $subnet = Get-Subnet # get it from localIp
        }

        if (! $networkId) {
            $networkId = Get-NetworkId -ip $ip -subnet $subnet -cidr $cidr
        }
        $maxHosts = Get-MaxHosts -subnet $subnet -cidr $cidr
        $lastIp = Get-NextIp -ip $networkId -inc ($maxHosts)
    }
    catch {
        $lastIp= '0.0.0.0'
    }

    Return $lastIp
}

function Get-LocalIp {
    [CmdletBinding()]
    param()
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    try {
        $gatewayIp= Get-GatewayIp
        $gatewayIpObj= [IPAddress]::Parse($gatewayIp)

        $localIp = ""
        [string[]]$localIps= (Get-NetIPAddress -AddressFamily IPV4 -PrefixOrigin DHCP,MANUAL).IpAddress

        foreach ($ip in $localIps) {
            $ipObj = [IPAddress]::Parse($ip)
            $subnetMask = Get-Subnet -localIp $ip
            $subnetMaskObj = [IPAddress]::Parse($subnetMask)
            if (($ipObj.Address -band $subnetMaskObj.Address) -eq ($gatewayIpObj.Address -band $subnetMaskObj.Address)) {
                $localIp = $ip
                break
            }
        }
    }
    catch {
        $localIp= '0.0.0.0'
    }

    Return $localIp
}

function Get-LogName {
    [CmdletBinding()]
    param(
        [string]$ident
        ,
        [string]$dateMask = "yyyy-MM-dd--HH-mm-ss-fff"
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    if (! $ident) {$ident = 'DEF'}
    if (! (test-path variable:global:startTime)) {
        $global:startTime = Get-Date
    }
    [string]$datePart = Get-Date -date $global:startTime -format $dateMask
    [string]$result = "LOG-$ident-$datePart.txt"

    Return $result
}


function Get-LoremIpsum {
    param(
        [int]$MinParagraphs = 1,
        [int]$MaxParagraphs = 5
    )
    $numParagraphs = Get-Random -Minimum $MinParagraphs -Maximum $MaxParagraphs

    for ($i = 1; $i -le $numParagraphs; $i++) {
        $Paragraph = "$(Get-RandomParagraph)"
        if (! $Paragraphs) {
            $Paragraphs = $Paragraph
        } else { $Paragraphs += "`r`n$Paragraph" }

    }
    return $Paragraphs.TrimEnd()
}

function Get-MaxHosts {
    [CmdletBinding()]
    param(
        [string]$subnet
        ,
        [int]$cidr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    if ((! $subnet) -and (! $cidr)) {
        $subnet= Get-Subnet # get it from localIp
    }

    try {
        if (! $cidr) {
            $cidr = Get-Cidr -subnet $subnet
        }

        [int]$maxHosts = [math]::Pow(2,(32-$cidr)) - 2
    }
    catch {
        [int]$maxHosts= 0
    }

    Return $maxHosts
}

function Get-ModulePath {
    param (
        [Parameter(Mandatory = $true)]
        [string]$module
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $moduleFile = (Get-Module $module).Path
    $modulePath = Split-Path $moduleFile
    return $modulePath
}

function Get-MssqlInstances {
    [CmdletBinding()]
    param(
        [switch]$show
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $regKey = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL"
    $result = New-Object System.Collections.ArrayList

    try {
        foreach ($instance in (Get-Item -Path $regKey | Select-Object -ExpandProperty Property)) {
            $regKey = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$instance\MSSQLServer\CurrentVersion"
            $version = (Get-ItemProperty -Path $regKey).CurrentVersion
            $regKeyCombi = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL"
            $combi = (Get-ItemProperty -Path $regKeyCombi).$instance
            $regKeyCombi = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$combi\MSSQLServer"
            $backupPath = (Get-ItemProperty -Path $regKeyCombi).BackupDirectory
            $regKeyCombi = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$combi\Setup"
            $binnPath = (Get-ItemProperty -Path $regKeyCombi).SQLBinRoot
            $collation = (Get-ItemProperty -Path $regKeyCombi).Collation
            $patchLevel = (Get-ItemProperty -Path $regKeyCombi).PatchLevel
            $dataPath = "$((Get-ItemProperty -Path $regKeyCombi).SQLDataRoot)\DATA"
            $regKeyCombi = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$combi\MSSQLServer\SuperSocketNetLib\Tcp"
            $tcpIp = (Get-ItemProperty -Path $regKeyCombi).Enabled
            $regKeyCombi = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$combi\MSSQLServer\SuperSocketNetLib\Sm"
            $sharedMemory = (Get-ItemProperty -Path $regKeyCombi).Enabled
            $regKeyCombi = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$combi\MSSQLServer\SuperSocketNetLib\Np"
            $namedPipes = (Get-ItemProperty -Path $regKeyCombi).Enabled
            $pipeName = (Get-ItemProperty -Path $regKeyCombi).PipeName

            $sqlService = Get-Service -Name "MSSQL`$${instance}" -ErrorAction SilentlyContinue | Select-Object -ExpandProperty Status
            $browserService = Get-Service -Name "SQLBrowser" -ErrorAction SilentlyContinue  | Select-Object -ExpandProperty Status

            [bool]$firewallChecked = $false
            [bool]$sqlBrowserRule= $false
            [bool]$sqlServerRule= $false

            try {
                $firewallBrowser= Get-NetFirewallPortFilter -ErrorAction Stop | Where-Object LocalPort -eq 1434 | Get-NetFirewallRule -ErrorAction Stop | Where-Object Enabled -eq True | Where-Object Direction -eq Inbound
                if ($firewallBrowser) {
                    [bool]$sqlBrowserRule= $true
                }
                # No Exception, so it was checked
                [bool]$firewallChecked = $true
            } catch [Microsoft.Management.Infrastructure.CimException]  { }

            try {
                $firewallSqlServer = Get-NetFirewallApplicationFilter -ErrorAction Stop | Where-Object Program -match "$($binnPath.Replace('\','\\'))\\sqlservr.exe" | Get-NetFirewallRule -ErrorAction Stop
                if ($firewallSqlServer) {
                    [bool]$sqlServerRule= $true
                }
                # No Exception, so it was checked
                [bool]$firewallChecked = $true
            } catch [Microsoft.Management.Infrastructure.CimException]  { }

            $mdfFiles = Get-ChildItem -Path "$dataPath\*.mdf" -Name | Sort-Object
            $filesResult = New-Object System.Collections.ArrayList
            foreach ($mdfFile in $mdfFiles) {
                $databaseName = [System.IO.Path]::GetFileNameWithoutExtension($mdfFile)
                $ldfFile = Get-ChildItem -Path "$dataPath\$databaseName*.ldf" -Name | Select-Object -First 1
                if ($ldfFile) {
                    $mdfFilePath = [System.IO.Path]::Combine($dataPath, $mdfFile)
                    $mdfFileInfo = New-Object System.IO.FileInfo ([System.IO.Path]::Combine($dataPath, $mdfFile))
                    $ldfFileInfo = New-Object System.IO.FileInfo ([System.IO.Path]::Combine($dataPath, $ldfFile))
                    $databaseSize = [math]::Round(($mdfFileInfo.Length + $ldfFileInfo.Length) / 1MB, 2)
                    $databaseLastModified = [System.IO.File]::GetLastWriteTime($mdfFilePath).ToString("yyyy-MM-dd HH:mm:ss")
                    $tempFilesObj = [PSCustomObject]@{
                        MdfFile = [string]$mdfFile
                        LdfFile = [string]$ldfFile
                        MdfSize = [double]$mdfFileInfo.Length / 1MB
                        LdfSize = [double]$ldfFileInfo.Length / 1MB
                        TotalSize = [double]$databaseSize
                        LastModified = [string]$databaseLastModified
                    }
                    $filesResult.Add($tempFilesObj) | Out-Null
                }
            }
            $tempObj = [PSCustomObject]@{
                Instance = [string]$instance
                Version = [version]$version
                PatchLevel = [version]$patchLevel
                Collation = [string]$collation
                BinnPath = [string]$binnPath
                DataPath = [string]$dataPath
                BackupPath = [string]$BackupPath
                SqlService = [string]$sqlService
                BrowserService = [string]$browserService
                TcpIp = [boolean]$tcpIp
                SharedMemory = [boolean]$sharedMemory
                NamedPipes = [boolean]$namedPipes
                PipeName = [string]$pipeName
                FirewallChecked = [bool]$firewallChecked
                SqlBrowserRule = [bool]$sqlBrowserRule
                SqlServerRule = [bool]$sqlServerRule
                Files = [PSCustomObject]$filesResult
            }

            $result.Add($tempObj) | Out-Null
        }

        if ($show) {
            foreach ($item in $result) {
                Write-Host "SERVER SETTINGS FOR: [ $($item.Instance) ]" -ForegroundColor Yellow
                $item  | Select-Object -Property Instance, Version, PatchLevel, Collation, BinnPath, DataPath, BackupPath, SqlService, BrowserService, NamedPipes, TcpIp, SharedMemory, FirewallChecked, SqlBrowserRule, SqlServerRule | Format-List | Out-Host
                Write-Host "DATABASE FILES FOR: [ $($item.Instance) ]" -ForegroundColor Blue
                Write-Host "Sizes are shown in MB" -ForegroundColor Blue
                $item.Files | Format-List | Out-Host
            }
            If (-NOT ([Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")) {
                Write-Host "Firewall rules was not to check, this requires administrator privileges. For this reason FirewallValid=False." -ForegroundColor Red
              }
        }

    } catch {
        if ($show) {
            Write-Host "$_" -ForegroundColor Red
        }
    }
    Return $result
}

function Get-MultipleOptions {
    [CmdletBinding()]
    Param (
        [string]$values
        ,
        [string[]]$list
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [string]$newValues = ''

    if ($values) {
        [string[]]$valuesArray = $values.Split(',')
        foreach ($value in $valuesArray) {
            [int]$idx = $list.ToUpper().IndexOf($value.ToUpper())
            if ($idx -eq -1 ) { [string]$item = $list[0]
            } else { [string]$item = $list[$idx] }
            if ($newValues) {
                $i = $newValues.ToUpper().IndexOf($item.ToUpper())
                if ($i -eq -1) {
                    [string]$newValues +=  (',' + $item)
                }
            } else { [string]$newValues = $Item }
        }
        $result = $newValues
    } else { [string]$result = $list[0] }
    Return $result
}

function Get-NetworkId {
    [CmdletBinding()]
    param(
        [string]$ip
        ,
        [string]$subnet
        ,
        [int]$cidr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    try {
        if (! $ip) {
            $ip= Get-LocalIp
            $subnet= Get-Subnet -localIp $ip
        }
        $ipObj= [IpAddress]$ip
        if (! $subnet) {
            $subnet = Get-Subnet -cidr $cidr
        }
        $subnetObj= [IpAddress]$subnet
        $networkId = ([IpAddress]($ipObj.Address -band $subnetObj.Address)).IpAddressToString
    }
    catch {
        $networkId= '0.0.0.0'
    }

    Return $networkId
}

function Get-NextIp {
    [CmdletBinding()]
    param(
        [string]$ip
        ,
        [int]$inc = 1
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    try {
        $ipObj = [IPAddress]$ip
        $ipBytes = $ipObj.GetAddressBytes()
        [System.Array]::Reverse($ipBytes)
        $ipInt = [System.BitConverter]::ToUInt32($ipBytes, 0)
        $nextIpInt = $ipInt + $inc
        $nextIpBytes = [System.BitConverter]::GetBytes($nextIpInt)
        [System.Array]::Reverse($nextIpBytes)
        if ([IntPtr]::Size -eq 4) { # x86
            $newIp = New-Object System.Net.IPAddress ([System.Array]::CreateInstance([byte], 4))
            [System.Buffer]::BlockCopy($nextIpBytes, 0, $nextIp.GetAddressBytes(), 0, 4)
        } else { # x64
            $newIp = New-Object System.Net.IPAddress -ArgumentList (,$nextIpBytes)
        }
        $newIpAddress= $newIp.IPAddressToString
    }
    catch {
        $newIpAddress= '0.0.0.0'
    }

    Return $newIpAddress
}

function Get-OldestFile {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $true)]
        [string]$fileMask
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [string]$path = "$fileMask"
    [string]$oldestFile = ""

    $entry = Get-ChildItem -Path $path | Sort-Object -Property LastWriteTime | select-object -First 1
    if ($entry) {
        [string]$oldestFile = $entry.Name
    }

    Return $oldestFile
}

function Get-Path32 {
    [CmdletBinding()]
    param()
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    if ( $($env:PROCESSOR_ARCHITECTURE) -eq "x86") {
        $Path32 = $($env:PROGRAMFILES)
    } else {
        $Path32 = $(${env:PROGRAMFILES(x86)})
    }
    Return $Path32
}

function Get-PublicIp {
    [CmdletBinding()]
    Param ()
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $result = ""

    if (! $result) {
        try {
            $result = (Resolve-DnsName myip.opendns.com -Server resolver1.opendns.com -type A -ErrorAction SilentlyContinue).IPAddress
        }
        catch {
        }
    }

    if (! $result)  {
        try {
            $html = Invoke-RestMethod https://checkip.eurodyndns.org
            $lines = $html.split("`n")
            foreach ($line in $lines) {
                $p = $Line.IndexOf("IP Address:")
                if ($p -gt 0) {
                    $p = $line.IndexOf(":")
                    if ($p -gt 0) {
                        $result = $line.SubString($p +1).Trim()
                    }
                }
            }
        }
        catch {
        }
    }

    if (! $result) {
        try {
            $result = Invoke-RestMethod https://ifconfig.me/ip
        }
        catch {
        }
    }

    if (! $result) {
        $result= '0.0.0.0'
    }

    return $result
}

function Get-ResStr {
    [CmdletBinding()]
    param (
        [string]$Key
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    if (!(test-path variable:global:resx)) {
        $global:userlang = [System.Threading.Thread]::CurrentThread.CurrentUICulture.Name
        if ($global:userlang.Substring(0,2) -eq 'de') {$global:userlang = 'de-DE'}
        if ($global:userlang.Substring(0,2) -eq 'en') {$global:userlang = 'en-US'}

        $global:resx = @{}
        [string]$path = (Get-Module -Name EulandaConnect | Select-Object -ExpandProperty Path)
        [string]$path = Split-path $path


        $xmlFiles = Get-ChildItem "$path\*.resx"
        foreach ($xmlFile in $xmlFiles) {
            $reslang = $xmlFile.Name.Split('.')[1]

            $xmlContent = Get-Content $xmlFile -Raw
            $xml = [xml]$xmlContent

            foreach ($data in $xml.root.data) {
                if ($resx[$data.name]) {
                    $resx[$data.name][$reslang]= $data.value
                } else {
                    $values = @{}
                    $values[$reslang] =$data.value
                    $resx[$data.name] = $values
                }
            }
        }
    }

    if ($resx.ContainsKey($key)) {
        if ($resx[$key].ContainsKey($userlang)) {
            $value = $resx[$Key][$userlang]
        } elseif ($resx[$key].ContainsKey('en-US')) {
            $value = $resx[$Key]['en-US']
        } else {
            $value = "?[$userlang`:$key]"
        }
    } else {
        $value = "?[$key]"
    }
    return $value
}

function Get-ScriptDir {
    [CmdletBinding()]
    Param ()
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [string]$result = ""
    if (!(test-path variable:hostinvocation)) {$hostinvocation = $null}
    if ($null -ne $hostinvocation)
      { $result = (Split-Path $hostinvocation.MyCommand.path) }
    else
    {
        # scope 1 is the parent scope of the actual scope
        $invocation=(get-variable MyInvocation -Scope 1).Value
        if ($invocation.scriptname) {
            $result = (Split-Path $invocation.scriptname)
        }
    }
    if (-not (Test-Path $result)) { $result = $PSScriptRoot }
    if (-not (Test-Path $result)) { $result = (Get-Item -Path ".\" -Verbose).FullName}
    return $result
}

function Get-SftpNextFilename {
    [CmdletBinding()]
    Param(
        [parameter(Mandatory=$true)]
        [string]$server
        ,
        [parameter(Mandatory=$false)]
        [int]$port= 22
        ,
        [parameter(Mandatory=$false)]
        [string]$user
        ,
        [parameter(Mandatory=$false)]
        [string]$password
        ,
        [parameter(Mandatory=$false)]
        [string]$remoteFolder
        ,
        [parameter(Mandatory=$false)]
        [string]$fileMask
        ,
        [parameter(Mandatory=$false)]
        [string]$comObject= 'IPWorksSSH.SFTP'
        ,
        [parameter(Mandatory=$false)]
        [string]$license= '(you have to buy one license from n/software)'
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [string]$result = $null

    $sftp = New-SftpInstance -comObject $comObject
    $sftp.ok = $license
    $sftp.Config("SSHAcceptAnyServerHostKey=true")
    $sftp.SSHHost = $server
    $sftp.SSHPassword = $password
    $sftp.SSHAuthMode = 2
    $sftp.SSHUser = $user
    $sftp.SSHPort = $port
    $sftp.SSHLogon($sftp.SSHHost, $sftp.SSHPort)
    try {
        $sftp.RemotePath = $remoteFolder
        $sftp.RemoteFile = $fileMask
        $sFtp.ListDirectory()
        write-verbose "DirListCount: $($sftp.DirListCount)"
        For ($i=0; $i -le $sftp.DirListCount-1; $i++) {
            if (! $sftp.DirListIsDir($i)) {
                [string]$result = $sftp.DirListFilename($i)
                break
            }
        }
    }
    finally {
        $sftp.SSHLogoff()
    }
    Return $result
}

function Get-SignToolPath {
    [CmdletBinding()]
    param()
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $rootPath = "$(Get-Path32)\Windows Kits\10\bin\"
    $versionRegex = '^10\.\d+\.\d+\.\d+$'
    $folders = Get-ChildItem -Path $rootPath -Directory | Where-Object { $_.Name -match $versionRegex }
    $sortedFolders = $folders | Sort-Object -Property @{Expression = {[version] $_.Name}; Descending = $true}
    $newestFolder = $sortedFolders | Select-Object -First 1
    $signtoolPath = Join-Path -Path $newestFolder.FullName -ChildPath 'x64\signtool.exe'
    if (Test-Path $signtoolPath) {
        Return $signtoolPath
    } else {
        throw "Signtool not found in expected Windows SDK folder '$rootPath'!"
    }
}

function Get-SingleOption {
    [CmdletBinding()]
    Param (
        [string]$value
        ,
        [string[]]$list
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [int]$idx = $list.ToUpper().IndexOf($value.ToUpper())
    if ($idx -eq -1) {
        [string]$result = $list[0]
    } else {
      [string]$result = $list[$idx]
    }
    Return $result
}

function Get-Spaces {
    [CmdletBinding()]
    param(
        [int]$count
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    return ' ' * $count
}

function Get-StockSql {
    [CmdletBinding()]
    param(
        [parameter(Mandatory=$false)]
        [string[]]$filter
        ,
        [parameter(Mandatory=$false)]
        [ValidateSet('ID', 'UID', 'ARTNUMMER', 'BARCODE', IgnoreCase = $true)]
        [string]$alias = 'ARTNUMMER'
        ,
        [parameter(Mandatory=$false)]
        [int]$qtyStatic
        ,
        [parameter(Mandatory=$false)]
        [string]$warehouse
        ,
        [parameter(Mandatory=$false)]
        [switch]$legacy
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    if ($filter) {
        [string]$sqlFilter= " AND $($filter -join(' AND ')) "
    } else {
        [string]$sqlFilter= ''
    }


    if ($warehouse) {
        # -----------------------------------------------------------------------------
        # Only one warehouse
        # -----------------------------------------------------------------------------
        # Master
        # ------------------------------------------
        [string]$sqlMaster = @"
        SELECT CONVERT(VARCHAR(100),art.$alias) [ID.ALIAS]
        FROM [dbo].Lagerort lo
        JOIN [dbo].LagerKonto lk on lk.Lagerort = lo.id
        JOIN [dbo].Artikel [art] ON art.id = lk.artikelId
            AND art.ArtNummer not like '.MUSTER%'
            $sqlFilter
        WHERE lo.id >= 1000
            AND lo.id < 1400
            AND lk.IdentId IS NULL
            AND lk.PlatzId IS NULL
            AND lo.Bezeichnung = '$warehouse'
        ORDER BY 1
"@

        # ------------------------------------------
        # Detail
        # ------------------------------------------
        if ($qtyStatic) {
            [string]$qty = $qtyStatic
        } else {
            [string]$qty = 'lk.Menge'
        }

        if ($legacy) {
            [string]$sqlLegacy= ", $qty [BESTANDVERFUEGBAR1], $qty [BESTANDVERFUEGBAR2]"
        } else {
            [string]$sqlLegacy = ""
        }

        [string]$sqlDetail = @"
        SELECT $qty [BESTANDVERFUEGBAR] $sqlLegacy
        FROM [dbo].Lagerort lo
        JOIN [dbo].LagerKonto lk on lk.Lagerort = lo.id
        JOIN [dbo].Artikel [art] ON art.$alias = '{0}'
        WHERE lk.ArtikelId = (SELECT TOP 1 Id FROM [dbo].Artikel WHERE $alias = '{0}')
            AND lk.IdentId IS NULL
            AND lk.PlatzId IS NULL
            AND lo.Bezeichnung = '$warehouse'
"@
    } elseif ($legacy) {

        # -----------------------------------------------------------------------------
        # All warehouses with sales and purchase correction. Pur legacy support
        # -----------------------------------------------------------------------------
        # Master
        # ------------------------------------------
        [string]$sqlMaster = @"
        SELECT CONVERT(VARCHAR(100),art.$alias) [ID.ALIAS]
        FROM [dbo].Artikel [art]
        WHERE art.ArtNummer not like '.MUSTER%'
            $sqlFilter
        ORDER BY 1
"@

        # ------------------------------------------
        # Detail
        # ------------------------------------------
        if ($qtyStatic) {
            [string]$qtyFields = `
                "$qtyStatic [BestandVerfuegbar], " +`
                "$qtyStatic [BestandVerfuegbar1], " +`
                "$qtyStatic [BestandVerfuegbar2]"
        } else {
            [string]$qtyFields = `
                "[dbo].[cnf_BestandVerfuegbarGesamt](art.Id,0) [BestandVerfuegbar], " +`
                "[dbo].[cnf_BestandVerfuegbar1](art.Id,0) [BestandVerfuegbar1], " +`
                "[dbo].[cnf_BestandVerfuegbar2](art.Id,0) [BestandVerfuegbar2]"
        }

        [string]$sqlDetail = @"
        -- Total inventory from all own warehouses with sales and purchase output fields
        SELECT $qtyFields
        FROM [dbo].Artikel [art]
        WHERE art.$alias = '{0}'
"@

    } else {

        # -----------------------------------------------------------------------------
        # All warehouses , all suplier and totals with sales and purchase correction
        # -----------------------------------------------------------------------------
        # Master
        # ------------------------------------------
        [string]$sqlMaster = @"
        SELECT
            CONVERT(VARCHAR(100),art.$alias) [ID.ALIAS],
            art.Id [ID],
            CONVERT(varchar(36), art.Uid) [UID],
            art.ArtNummer [ARTNUMMER],
            art.Barcode [BARCODE],
            art.ChangeDate [CHANGEDATE],
            art.ShopFreigabeFlg [SHOPFREIGABEFLG],
            art.ShopExportDatum [SHOPEXPORTDATUM]
        FROM [dbo].Artikel [art]
        WHERE art.ArtNummer not like '.MUSTER%'
            $sqlFilter
        ORDER BY 1
"@

        # ------------------------------------------
        # Detail
        # ------------------------------------------
        if ($qtyStatic) {
            $sqlStaticWarehouse = $qtyStatic
            $sql0 = $qtyStatic
            $sql1 = $qtyStatic
            $sql2 = $qtyStatic
            $sqlSupplier = $qtyStatic
        } else {
            $sqlStaticWarehouse = 'lk.Menge'
            $sql0 = '[dbo].[cnf_BestandVerfuegbarGesamt](art.Id,0)'
            $sql1 = '[dbo].[cnf_BestandVerfuegbar1](art.Id,0)'
            $sql2 = '[dbo].[cnf_BestandVerfuegbar2](art.Id,0)'
            $sqlSupplier = 'IsNull(kart.Bestand,0)'
        }

        [string]$sqlDetail = @"
        SELECT * FROM (
            SELECT lo.id [KONTO], UPPER(lo.Bezeichnung) [BEZEICHNUNG], $sqlStaticWarehouse [MENGE]
            FROM [dbo].Lagerort lo
            JOIN [dbo].LagerKonto lk ON lk.Lagerort = lo.id
            JOIN [dbo].Artikel [art] ON art.$alias = '{0}'
            WHERE lo.id >= 1000
                AND lo.id < 1400
                AND lk.ArtikelId = (SELECT TOP 1 Id FROM [dbo].Artikel WHERE $alias = '{0}')
                AND lk.IdentId IS NULL
                AND lk.PlatzId IS NULL
 
            -- Total inventory from all own warehouses that is physically present
            UNION
            SELECT 10000+0 [KONTO], 'BESTANDVERFUEGBAR' [BEZEICHNUNG], $sql0 [MENGE]
            FROM [dbo].Artikel [art]
            WHERE art.$alias = '{0}'
 
            -- Total inventory from all own warehouses less sales orders
            UNION
            SELECT 10000+1 [KONTO], 'BESTANDVERFUEGBAR1' [BEZEICHNUNG], $sql1 [MENGE]
            FROM [dbo].Artikel [art]
            WHERE art.$alias = '{0}'
 
            -- Total inventory from all own warehouses less sales orders
            UNION
            SELECT 10000+1 [KONTO], 'BESTANDVERFUEGBAR2' [BEZEICHNUNG], $sql2 [MENGE]
            FROM [dbo].Artikel [art]
            WHERE art.$alias = '{0}'
 
            -- Inventory of suppliers, external goods
            UNION
            SELECT 20000+k.id [KONTO], adr.Match [BEZEICHNUNG], $sqlSupplier [MENGE]
            FROM [dbo].Kreditor [k]
            JOIN [dbo].Adresse [adr] ON adr.id = k.AdresseID
            JOIN [dbo].KrArtikel [kart] ON
                kart.ArtikelId = (SELECT TOP 1 Id FROM [dbo].Artikel WHERE $alias = '{0}') AND
                kart.KreditorID = k.id
            JOIN [dbo].Artikel [art] ON art.id = kart.ArtikelID
        ) [INVENTORYREPORT] ORDER BY KONTO
 
"@
    }

<#
        # This model 'flat' is experimental and not yet fully parameterized
 
        [string]$sqlMaster = @"
        SELECT GTIN [ID.ALIAS] FROM
        (SELECT ArtNummer [ArticleNo], Barcode [GTIN] FROM Artikel) [Master]
        WHERE GTIN>='1000000' AND GTIN <='8888889'
@"
 
        [string]$sqlDetail = @"
        DECLARE @Id INT
        DECLARE @IdList TABLE (Id INT)
 
        INSERT INTO @IdList SELECT Id FROM Artikel order by id -- VALUES (31), (32), (33)
        DECLARE @Result TABLE ([Uid] VARCHAR(36), ArticleId INT, ArticleNo VARCHAR(80), GTIN VARCHAR(80),
            ChangeDate DateTime, ExportFlag Bit, ExportDate DateTime, StockGroup INT, Warehouse VARCHAR(50), Quantity INT)
        DECLARE IdCursor CURSOR FOR SELECT Id FROM @IdList
 
        OPEN IdCursor
        FETCH NEXT FROM IdCursor INTO @Id
 
        WHILE @@FETCH_STATUS = 0
        BEGIN
        INSERT INTO @Result
            -- Inventory from own warehouses
            SELECT CONVERT(varchar(36), art.Uid) [Uid], art.Id [ArticleId], art.ArtNummer [ArticleNo], IsNull(Art.Barcode,'') [GTIN],
                art.ChangeDate [ChangeDate], art.ShopFreigabeFlg [ExportFlag], art.ShopExportDatum [ExportDate],
                lo.id [StockGroup], UPPER(lo.Bezeichnung) [Warehouse], lk.Menge [Quantity]
            FROM Lagerort lo
            JOIN dbo.LagerKonto lk on lk.Lagerort = lo.id
            JOIN Artikel [art] ON art.id = @Id AND art.ArtNummer not like '.MUSTER%'
            WHERE lo.id >= 1000
                AND lo.id < 1400
                AND lk.ArtikelId = @Id
                AND lk.IdentId IS NULL
                AND lk.PlatzId IS NULL
 
            -- Total inventory from all own warehouses that is physically present
            UNION
            SELECT CONVERT(varchar(36), art.Uid) [Uid], art.Id [ArticleId], art.ArtNummer [ArticleNo], IsNull(Art.Barcode,'') [GTIN],
                art.ChangeDate [ChangeDate], art.ShopFreigabeFlg [ExportFlag], art.ShopExportDatum [ExportDate],
                10000+1 [StockGroup], 'AVAILABLE' [Warehouse], [dbo].[cnf_BestandVerfuegbarGesamt](@Id,0) [Quantity]
            FROM Artikel [art]
            WHERE art.id = @Id AND art.ArtNummer not like '.MUSTER%'
 
            -- Total inventory from all own warehouses less sales orders
            UNION
            SELECT CONVERT(varchar(36),art.Uid) [Uid], art.Id [ArticleId], art.ArtNummer [ArticleNo], IsNull(Art.Barcode, '') [GTIN],
                art.ChangeDate [ChangeDate], art.ShopFreigabeFlg [ExportFlag], art.ShopExportDatum [ExportDate],
                10000+2 [StockGroup], 'SALES' [Warehouse], [dbo].[cnf_BestandVerfuegbar1](@Id,0) [Quantity]
            FROM Artikel [art]
            WHERE art.id = @Id AND art.ArtNummer not like '.MUSTER%'
 
            -- Total inventory from all own warehouses less sales orders and plus purchase orders
            UNION
            SELECT CONVERT(varchar(36),art.Uid) [Uid], art.Id [ArticleId], art.ArtNummer [ArticleNo], IsNull(Art.Barcode, '') [GTIN],
                art.ChangeDate [ChangeDate], art.ShopFreigabeFlg [ExportFlag], art.ShopExportDatum [ExportDate],
                10000+3 [StockGroup], 'PURCHASE' [Warehouse], [dbo].[cnf_BestandVerfuegbar2](@Id,0) [Quantity] FROM Artikel [art]
            WHERE art.id = @Id AND art.ArtNummer not like '.MUSTER%'
 
            -- Inventory of suppliers, external goods
            UNION
            SELECT CONVERT(varchar(36),art.Uid) [Uid], art.Id [ArticleId], art.ArtNummer [ArticleNo], IsNull(Art.Barcode, '') [GTIN],
                art.ChangeDate [ChangeDate], art.ShopFreigabeFlg [ExportFlag], art.ShopExportDatum [ExportDate],
                20000+k.id [StockGroup], adr.Match [Warehouse], IsNull(kart.Bestand,0) [Quantity]
            FROM Kreditor [k]
            JOIN Adresse [adr] ON adr.id = k.AdresseID
            JOIN KrArtikel [kart] ON kart.ArtikelId = @Id AND kart.KreditorID = k.id
            JOIN Artikel [art] ON art.id = kart.ArtikelID AND art.ArtNummer not like '.MUSTER%'
 
            FETCH NEXT FROM IdCursor INTO @Id
        END
 
        CLOSE IdCursor
        DEALLOCATE IdCursor
 
        SELECT GTIN [ARTNUMMER], Quantity [BESTANDVERFUEGBAR], Quantity [BESTANDVERFUEGBAR1], Quantity [BESTANDVERFUEGBAR2] FROM
        (SELECT [Uid], ArticleId, ArticleNo, GTIN,
                ChangeDate, ExportFlag, ExportDate,
                StockGroup, Warehouse, Quantity
        FROM @Result) [Details]
 
        WHERE StockGroup = 1001 AND GTIN>='1000000' AND GTIN <='8888889' AND GTIN = '{0}'
"@
 
#>

   [string]$sqlLink= 'ID.ALIAS'

   Write-Debug $sqlMaster
   Write-Debug $sqlDetail
   Write-Debug $sqlLink

   Return [string[]]@($sqlMaster, $sqlDetail, $sqlLink)
}

function Get-Subnet {
    [CmdletBinding()]
    param(
        [Parameter(Position=0, Mandatory=$false)]
        [int]$cidr
        ,
        [Parameter(Position=1, Mandatory=$false)]
        [string]$localIp
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    try {
        if (! $Cidr) {
            if (! $localIp) {
                $localIp= Get-LocalIp
            }
            $localIpObj= [IPAddress]::Parse($localIp)
            $cidr =  (Get-NetIPAddress -AddressFamily IPv4 -IPAddress $localIpObj).PrefixLength
        }
        $subnet= ([IpAddress]([math]::pow(2, 32) -1 -bxor [math]::pow(2, (32 - $cidr))-1)).IpAddressToString
    }
    catch {
        $subnet= '0.0.0.0'
    }

    Return $subnet
}

function Get-TempDir {
    [CmdletBinding()]
    param()
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [string]$tempDir = [System.IO.Path]::GetTempPath()
    if ($tempDir.Substring($tempDir.Length-1) -eq '\') {
        $tempDir = $tempDir.substring(0, $tempDir.Length - 1)
    }
    if ($tempDir -eq "\") { $tempDir = "" }

    Return $tempDir
}

function Get-TranslateSection {
    [CmdletBinding()]
    param(
        [Parameter(Position=0, Mandatory = $true)]
        [string]$text
        ,
        [Parameter(Position=1, Mandatory = $true)]
        [ValidateLength(2,2)]
        [string]$iso
        ,
        [Parameter(Mandatory = $false)]
        [string]$sub
        ,
        [Parameter(Mandatory = $false)]
        [string]$subDefault
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $iso = $iso.ToUpper()
    $sub = $sub.ToUpper()
    $subDefault = $subDefault.ToUpper()

    [string]$isoTemp = ""
    [string]$subTemp = ""
    [string]$isoCurrent = ""
    [string]$subCurrent = ""

    [System.Collections.ArrayList]$result = @()
    $arrLines = $text.Replace("`r`n", "`n").Split("`n")

    Foreach ($line in $arrLines) {
        if ((Get-TranslateIsDelim -value $line) -eq $true) {
            [string[]]$delim = Get-TranslateExtractTag -value $line
            [string]$isoTemp = $delim[0]
            [string]$subTemp = $delim[1]

            If (($isoCurrent -eq $iso) -and ($subCurrent -eq $sub)) {
                break
            }

            if ((! $isoCurrent) -and ($isoTemp -eq $iso) -and ($subTemp -eq $sub)) {
                [System.Collections.ArrayList]$result = @()
                $isoCurrent = $isoTemp
                $subCurrent = $subTemp
            }

        } else {
            # Iso tag language separator found and sort by it
            if ((($isoCurrent -eq $iso) -and ($subCurrent -eq $sub)) -or
                ((! $isoCurrent) -and (! $isoTemp) -and (!$sub)) -or
                ((! $isoCurrent) -and ($isoTemp -eq "00") -and ($subTemp -eq $sub))) {
                   $result.add($line) | Out-Null
                }
        }

    }
    if (($result.count -eq 0) -and ($sub) -and ($subDefault)) { $result.Add($subDefault) | Out-Null }

    Return $result -join "`r`n"
}

function Hide-Extensions {
    [CmdletBinding()]
    param()
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    Push-Location
    Set-Location HKCU:\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced
    if ((Get-ItemProperty .).HideFileExt -eq 0) {
        Set-ItemProperty . HideFileExt 1
        Start-Sleep -Milliseconds 500
        Update-Desktop
    }
    Pop-Location
}

Function Install-SignTool {
    [CmdletBinding()]
    param(
        [string]$signToolBasePath= "$env:TEMP"
        ,
        [string]$isoBasePath= "$env:TEMP"
        ,
        [switch]$leaveIso
        ,
        [switch]$noBuild
        ,
        [switch]$noInstall
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [hashtable]$headers = @{"User-Agent" = "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36 Edge/16.16299"}
    [string]$url = "https://developer.microsoft.com/en-us/windows/downloads/windows-sdk/"


    # ------------------------
    # Download Link
    # ------------------------
    $response = Invoke-WebRequest -uri $url -Headers $headers -UseDefaultCredentials -UseBasicParsing -SessionVariable cookieContainer
    [string]$downloadLink= $null
    foreach ($link in $response.Links) {if ($link.outerHTML -match "Download the \.iso") {$downloadLink = $link.href; break } }


    # ------------------------
    # Download Iso
    # ------------------------
    if ($downloadLink) {
        $response = Invoke-WebRequest -uri $downloadLink -Headers $headers -Method Head -UseBasicParsing -SessionVariable cookieContainer
        [string]$filename = $response.Headers['Content-Disposition'].Split('; ')[1].Split('=')[1]
        [string]$build= $filename.Split('.')[0]
        [string]$isoPath= "$isoBasePath\$filename"
        # Invoke-WebRequest -uri $downloadLink -Headers $headers -UseBasicParsing -SessionVariable cookieContainer -OutFile "$isoPath"
        Write-Host -NoNewline "Downloading Windows SDK (ca. 1.5 GB)..." -ForegroundColor Blue
        (New-Object System.Net.WebClient).DownloadFile($downloadLink, $isoPath)
        Write-Host " (ready)"
    } else {
        throw "Download link for SDK not found on sdk website!"
    }


    # ------------------------
    # Extract ISO
    # ------------------------
    Mount-DiskImage -ImagePath $isoPath -StorageType ISO | Out-Null
    [string]$isoDrive = (Get-Volume -FriendlyName "KSDK*" | Select-Object -ExpandProperty DriveLetter) + ":\"
    [string]$sourcePath = "$isoDrive\Installers"
    if ($noBuild) {
        [string]$signToolPath = "$signToolBasePath\Signtool"
    } else {
        [string]$signToolPath = "$signToolBasePath\Signtool($build)"
    }
    if (! (Test-Path $signToolPath )) {
        New-Item -ItemType Directory -Path $signToolPath -force | Out-Null
    }
    [string]$installer= "Windows SDK Signing Tools-x86_en-us.msi"
    if (! (Test-path "$sourcePath\$installer")) {
        [string]$installer= "Windows SDK Signing Tools-x86_en-us.exe"
    }

    # Extracting Items
    Copy-Item "$sourcePath\$installer" $signToolPath -force
    Copy-Item "$sourcePath\4c3ef4b2b1dc72149f979f4243d2accf.cab" $signToolPath -force
    Copy-Item "$sourcePath\685f3d4691f444bc382762d603a99afc.cab" $signToolPath -force
    Copy-Item "$sourcePath\e5c4b31ff9997ac5603f4f28cd7df602.cab" $signToolPath -force
    Copy-Item "$sourcePath\e98fa5eb5fee6ce17a7a69d585870b7c.cab" $signToolPath -force

    Dismount-DiskImage -ImagePath $isoPath | Out-Null
    if (! $leaveIso) {
        Remove-Item $isoPath -Force
    }


    # ------------------------
    # Install SignTool
    # ------------------------
    if (! $noInstall) {
        $msiPath = "$signToolPath\$installer"
        $arguments = "/i `"$msiPath`" /qn"
        Start-Process msiexec.exe -ArgumentList $arguments -Wait -Verb RunAs

        Remove-Item -Path "$signToolPath\*.cab" -Force
        Remove-Item -Path "$signToolPath\$installer" -Force
        Remove-Item -Path "$signToolPath" -Force
    }
}

function New-Delivery {
    [CmdletBinding()]
    param (
        [parameter(Mandatory=$true)]
        [ValidateRange(1, [int]::MaxValue)]
        [int]$salesOrderId
        ,
        [Parameter(Mandatory = $false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [long]$adUseClient = 3
    [long]$adOpenKeyset = 1
    [long]$adLockOptimistic = 3
    [long]$adCmdText = 1
    [long]$adStateOpen = 1

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    try {
        [int]$deliveryId = 0
        $sql = "SET NOCOUNT ON`r`n" +`
               "DECLARE @Af_Id int`r`n" +`
               "DECLARE @Lf_Id int`r`n" +`
               "SET @Af_Id = $salesOrderId`r`n" +`
               "EXEC dbo.cn_TraAfLf_SingleAf @Lf_Id=@Lf_Id OUT, @af_id=@Af_Id`r`n" +`
               "SELECT @Lf_Id [Id]"
        $rs = new-object -comObject ADODB.Recordset
        $rs.CursorLocation = $adUseClient
        $rs.Open($sql, $myConn, $adOpenKeyset, $adLockOptimistic, $adCmdText)

        if ($rs) {
            # Toggle all record sets until you find an open one
            Do {
                If ($rs.State -ne $adStateOpen) {
                    $rs = $rs.NextRecordset()
                }
            } until ( (! $rs) -or ($rs.State -eq $adStateOpen) )

            # Get the id of the new order
            if (! $rs.eof) {
                [int]$deliveryId = $rs.fields('Id').value
            }
        } else {
            throw "The '$($myInvocation.MyCommand)' function has failed. Record set is closed. Sql: $sql"
        }
    } finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }

    Return $deliveryId
}

function New-DeliveryPropertyItem {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $false)]
        [ValidateScript({
            if ($_ -eq $null -or $_ -ge 0) {return $true} else {return $false}
        })]
        [int]$deliveryNo
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({
            if ($_ -eq $null -or $_ -ge 0) {return $true} else {return $false}
        })]
        [int]$deliveryId
        ,
        [Parameter(Mandatory = $true)]
        [ValidateRange("Positive")]
        [int]$propertyId
        ,
        [Parameter(Mandatory = $false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    try {
        if ($deliveryId) {
            [string]$sqlFrag = "Id = $deliveryId"
        } elseif ($deliveryNo) {
            [string]$sqlFrag = "KopfNummer = $deliveryNo"
        } else {
            throw "The delivery note cannot be loaded, either parameter DeliveryId or DeliveryNo must be specified. Function: $($myInvocation.MyCommand)"
        }

        [string]$sql = @"
            DECLARE @KopfId INT;
            DECLARE @ObjektId INT;
            SELECT TOP 1 @ObjektId = ID FROM Lieferschein WHERE $sqlFrag;
            SET @KopfId = $propertyId;
            INSERT INTO MerkmalElement (KopfId, ObjektId)
            SELECT @KopfId, @ObjektId
            WHERE NOT EXISTS (SELECT 1 FROM MerkmalElement WHERE KopfId = @KopfId AND ObjektId = @ObjektId);
"@

        $myConn.Execute($sql) | out-null
    } finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }
}

class EulException : Exception {
    [string] $additionalData
    EulException($message, $additionalData) : base($message) {
        $this.additionalData = $additionalData
    }
}

function New-EulException {
    [CmdletBinding()]
    param(
        $message,
        $additionalData
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    return [EulException]::New($message,$additionalData)
}

Class EulLog : System.IDisposable  {
    [string]$filePath
    [string]$name

    EulLog($name, $path) {
        $this.Name = $name
        $this.FilePath = $this.ChkPath($path)
        $this.Put('Initialization')
    }

    [void]Dispose() {
        $this.Put('Finalization')
    }

    [string]Fname($name) {
        $monthYear = Get-Date -Uformat "%Y-%b"
        return "LOG_$name`_$monthYear.txt"
    }

    [string]ChkPath($path) {
        $fName = $this.Fname($this.name)
        if (!(Get-Content "$path\events\$fName")) {
            [void](New-Item -Path "$path\events\$fName" -ItemType File -Force)
        }
        return "$path\events\$fName"
    }

    # main entry for logging an event
    [void]Put($level, $group, $message) {
        $now = (Get-Date).tostring("yyyy-MM-dd HH:mm:ss")
        if (! (test-path variable:global:processId)) {
            $global:processId = "$([System.Guid]::NewGuid())"
        }
        $delim = "`t"
        $log= "{1}{0}{2}{0}{3}{0}{4}{0}{5}{0}{6}" -f $delim, $now, ($this.name), $global:processId,  $level,  $group,  $message
        Write-Debug $log
        [int]$maxRetries = 30
        [int]$i = 0
        while ($i -lt $maxRetries) {
            try {
                $log | Out-File -FilePath $this.filePath -Append -NoClobber
                $i = $maxRetries
            }
            catch {
                $i++
                Write-Host "$_ Logfile: $log retries: $i from $maxRetries" -ForegroundColor Red
                Start-Sleep -Seconds 0.1
            }
        }
    }

    # short way to log an event
    [void]Put($level, $message) {
        $this.Put($level,'(default)',$message)
    }

    # shortest way to log an event
    [void]Put($message) {
        $this.Put(0,'(default)',$message)
    }
}

function New-EulLog {
    [CmdletBinding()]
    param (
        $name,
        $path
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)
    return [EulLog]::New($name, $path)
}


function New-SalesOrder {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [ValidateRange("Positive")]
        [int]$invoiceAddressId
        ,
        [Parameter(Mandatory = $false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [long]$adUseClient = 3
    [long]$adOpenKeyset = 1
    [long]$adLockOptimistic = 3
    [long]$adCmdText = 1
    [long]$adStateOpen = 1

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    [int]$result = 0
    try {
        $rs = $Null
        # Create an empty sales order head
        [string]$sql = "SET NOCOUNT ON`r`n" +`
            "DECLARE @Af_Id int`r`n" +`
            "DECLARE @Ad_Id int`r`n" +`
            "SET @Ad_Id = $invoiceAddressId`r`n" +`
            "EXEC dbo.cn_CreAf @Af_Id = @Af_Id out, @Ad_Id = @Ad_Id`r`n" +`
            "SELECT @Af_Id [Id]"

        $rs = new-object -comObject ADODB.Recordset
        $rs.CursorLocation = $adUseClient
        $rs.Open($sql, $myConn, $adOpenKeyset, $adLockOptimistic, $adCmdText)

        # Toggle all record sets until you find an open one
        Do {
            If ($rs.State -ne $adStateOpen) {
                $rs = $rs.NextRecordset()
            }
        } until ( (! $rs) -or ($rs.State -eq $adStateOpen) )

        # Get the id of the new order
        if (! $rs.eof) {
            [int]$result = $rs.fields('Id').value
        }

    } finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }

    Return $result
}

function New-Shortcut {
    [CmdletBinding()]
    param(
        [string]$file
        ,
        [string]$link
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    try {
        if (! (Test-Path $link)) {
            if (Test-Path $file) {
                $wshShell = New-Object -comObject WScript.Shell
                $shortcut = $wshShell.CreateShortcut($Link)
                $shortcut.TargetPath = $file
                $shortcut.Save()
            } else {
                Write-Host "WARNING! File '$file' is missing, no shortcut is created! Function: $($myInvocation.MyCommand)" -foregroundcolor Magenta
            }
        }
    } catch {
        Write-Host "ERROR! $_. Function: $($myInvocation.MyCommand)" -foregroundcolor "red"
    }
}

function New-Table {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory=$true)]
        [string]$tableName
        ,
        [Parameter(Mandatory=$true)]
        $columnNames
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    if ($columnNames.GetType().Name -eq "String") {
        $columnNames = $columnNames -split ","
    } elseif ($columnNames.GetType().BaseType.Name -ne "Array") {
        throw 'The name of the collumn must be string!'
    }

    $tempTable = New-Object System.Data.DataTable
    $count = 0

    if ($columnNames.count -ne 0) {
        do {
            Remove-Variable -Name datatype -ErrorAction SilentlyContinue -WarningAction SilentlyContinue
            $tempTable.Columns.Add() | Out-Null
                if ($columnNames[$count] -like "*/?*") {
                    $datatype = $columnNames[$count].Substring($columnNames[$count].IndexOf("/?")+2)
                    $columnNames[$count] = $columnNames[$count].Substring(0,$columnNames[$count].IndexOf("/?"))
                    if ($datatype -notlike "System.*") {
                        $datatype = "System."+$datatype
                    }
                    $tempTable.Columns[$count].DataType = $datatype
                }
            $tempTable.Columns[$count].ColumnName = $columnNames[$count]
            $tempTable.Columns[$count].Caption = $columnNames[$count]
            $count++
        } until ($count -eq $columnNames.Count)
    }
    Set-Variable -Name $tableName -Scope Global -Value (New-Object System.Data.DataTable)
    Set-Variable -Name $tableName -Scope Global -Value $tempTable
    Remove-Variable -Name TempTable -ErrorAction SilentlyContinue -WarningAction SilentlyContinue
}

function New-TempDir {
    [CmdletBinding()]
    param()
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $parent = [System.IO.Path]::GetTempPath()
    [string]$name = [System.Guid]::NewGuid()
    [string]$path = (Join-Path $parent $name)
    New-Item -ItemType Directory -Path $path | Out-Null
    Return $path
}

function Out-Beep {
    [CmdletBinding()]
    param()
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [console]::beep(500,300)
}

function Out-Goodbye {
    [CmdletBinding()]
    Param (
        [switch]$normally
        ,
        [switch]$abnormally
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    if (! (test-path variable:global:startTime)) {
        $global:startTime = Get-Date
    }

    $global:endTime = Get-Date
    write-host "Execution start: $(Use-Culture -culture 'en-US' -script {$($global:startTime)})" -ForegroundColor "blue"
    write-host "Execution end: $(Use-Culture -culture 'en-US' -script {$($global:endTime)})" -ForegroundColor "blue"
    $ts = New-Timespan -end $global:endtime -start $global:startTime
    Write-Host "Duration: $(($ts.TotalSeconds).ToString('0.##')) seconds" -ForegroundColor "blue"

    if ($normally) { Write-Host "Job finished normally" -ForegroundColor "blue" }
    if ($abnormally) { Write-Host "Job finished abnormally" -ForegroundColor "red" }
}

function Out-Welcome {
    [CmdletBinding()]
    Param (
        [parameter(Position = 0, Mandatory=$false)]
        [ValidateScript({
            if(-Not ($_ | Test-Path) ) {
                throw "File or folder '$_' does not exist!"
             } else {
                 $true
            }
        })]
        [ValidateScript({
            if($_ -notmatch "(\.ps1)") {
                throw "The file '$_' specified in the path argument must of type .ps1"
            } else {
                 $true
            }
        })]
        [string]$projectScript
        ,
        [parameter(Position = 1, Mandatory=$false)]
        [switch]$noBanner
        ,
        [parameter(Position = 2, Mandatory=$false)]
        [switch]$noInfo
        ,
        [parameter(Position = 3, Mandatory=$false)]
        [string]$culture
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [string]$OUT_WELCOME_VERSION = 'OUT_WELCOME_VERSION'                # EulandaConnect: {0} v{1}
    [string]$OUT_WELCOME_LICENSEURI = 'OUT_WELCOME_LICENSEURI'          # {0}
    [string]$OUT_WELCOME_COPYRIGHT = 'OUT_WELCOME_COPYRIGHT'            # Copyright: {0}
    [string]$OUT_WELCOME_PROJECTVERSION = 'OUT_WELCOME_PROJECTVERSION'  # CustomProject: {0} v{1}
    [string]$OUT_WELCOME_STARTTIME = 'OUT_WELCOME_STARTTIME'            # Execution start: {0}
    [string]$OUT_WELCOME_PATH = 'OUT_WELCOME_PATH'                      # Module Path: {0}

    $global:startTime = Get-Date

    $banner = @"
          ________ ____ ___ _ ______ ___
         / ____/ / / / / / | / | / / __ \/ |
        / __/ / / / / / / /| | / |/ / / / / /| |
       / /___/ /_/ / /___/ ___ |/ /| / /_/ / ___ |
      /_____/\____/_____/_/ |_/_/ |_/_____/_/ |_|
         _____ ______
        / ___/____ / __/ /__ ______ _________
        \__ \/ __ \/ /_/ __/ | /| / / __ / ___/ _ \
       ___/ / /_/ / __/ /_ | |/ |/ / /_/ / / / __/
      /____/\____/_/ \__/ |__/|__/\__,_/_/ \___/
 
"@
    if (! $noBanner) {
        write-host $banner -ForegroundColor "blue"
    }

    $moduleName = 'EulandaConnect'
    $module = Get-Module -Name $moduleName
    if (! $culture) {
        $culture = [System.Threading.Thread]::CurrentThread.CurrentCulture.Name
    } else {
        $cultureObj = [System.Globalization.CultureInfo]::GetCultureInfo($culture)
        [System.Threading.Thread]::CurrentThread.CurrentCulture = $cultureObj
        [System.Threading.Thread]::CurrentThread.CurrentUiCulture = $cultureObj
        # Set-Culture -CultureInfo $culture
        $global:userlang = $culture
    }
    [version]$global:eulandaConnectVersion = $module.Version
    [string]$copyright = $module.Copyright
    [string]$licenseURI = $module.LicenseUri

    if ($projectScript) {
        [string]$global:scriptDir= Split-Path -Parent $projectScript
        Set-Location $global:scriptDir
        $global:projectName=  Split-Path -LeafBase $projectScript
        [version]$global:projectVersion= Read-VersionFromSynopsis -path $projectScript
    } else {
        [string]$global:scriptDir= ""
        [string]$global:projectName= ""
        [version]$global:projectVersion = "0.0"
    }

    Write-Debug (get-module -Name EulandaConnect).path

    if (! $noInfo) {
        if ($global:eulandaConnectVersion) {
            Write-Host ( (Get-ResStr($OUT_WELCOME_VERSION) ) -f $moduleName, $($global:eulandaConnectVersion.ToString()))  -ForegroundColor Blue
        }

        if ($copyright) {
            Write-Host  ( (Get-ResStr($OUT_WELCOME_COPYRIGHT) ) -f $($copyright))  -ForegroundColor Blue
        }

        if ($licenseURI) {
            Write-Host  ( (Get-ResStr($OUT_WELCOME_LICENSEURI) ) -f $($licenseURI)) -ForegroundColor Blue
        }

        Write-Host  ( (Get-ResStr($OUT_WELCOME_PATH) ) -f $($module.path)) -ForegroundColor Blue

        write-host  ( (Get-ResStr($OUT_WELCOME_STARTTIME) ) -f $(Use-Culture -culture $culture -script {$($global:startTime.toString())})) -ForegroundColor Blue

        if ($global:projectVersion -gt [version]"0.0") {
            write-host  ( (Get-ResStr($OUT_WELCOME_PROJECTVERSION) ) -f $($global:projectName), $($global:projectVersion.ToString())) -ForegroundColor Green
        }

    }
}

function Protect-String {
    [CmdletBinding()]
    param(
        [Parameter(Position=0, Mandatory = $true)]
        [string]$plainText,
        [Parameter(Position=1, Mandatory = $true)]
        $key
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $key = Get-ProtectedKey $key
    $securestring = New-Object System.Security.SecureString
    $chars = $plainText.toCharArray()
    foreach ($char in $chars) {
        $secureString.AppendChar($char)
    }
    $protectedText = ConvertFrom-SecureString -SecureString $secureString -Key $key

    return $protectedText
}

function Read-IniFile {
    [CmdletBinding()]
    Param (
        [Parameter(Mandatory = $true)]
        [ValidateScript({
            if(-Not ($_ | Test-Path) ){
                throw "File or folder does not exist"
            }
            if(-Not ($_ | Test-Path -PathType Leaf) ){
                throw "The path argument must be a file. Folder paths are not allowed."
            }
            if($_ -notmatch "(\.ini)"){
                throw "The file specified in the path argument must be either of type ini"
            }
            return $true
        })]
        [System.IO.FileInfo]$path
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    if (!(test-path variable:section)) {[string]$section = $null}
    [int]$commentCount = 0

    if (test-path $path) {
        $ini = @{}
        $data = get-content $path -Encoding Default
        switch -regex($data)
        {
            "^\[(.+)\]" # Section
            {
                $section = $matches[1]
                if (!$ini[$section]) { $ini[$section] = [ordered]@{} }
                $commentCount = 0
            }
            "^(;.*)$" # Comment
            {
                if (!($section))
                {
                    $section = "No-Section"
                    if (! $ini[$section]) { $ini[$section] = @{} }
                }
                [string]$value = $matches[1]
                $commentCount = $commentCount + 1
                $name = "Comment" + $commentCount
            }
            "(.+?)\s*=(.*)" # Key
            {
                if (!($section))
                {
                    $section = "No-Section"
                    if (!$ini[$section]) { $ini[$section] = [ordered]@{} }
                }
                $name,$value = $matches[1..2]
                if ($ini[$section].Contains($name))
                    { $ini[$section][$name] = $value.Trim() }
                else
                    { $ini[$section].Add($name, $value.Trim()) }
            }
        }
    }
    return $ini
}

function Read-VersionFromSynopsis {
    [CmdletBinding()]
    param(
        [parameter(Position = 0, Mandatory=$true)]
        [ValidateScript({
            if(-Not ($_ | Test-Path) ) {
                throw "File or folder '$_' does not exist!"
             } else {
                 $true
            }
        })]
        [ValidateScript({
            if($_ -notmatch "(\.ps1)") {
                throw "The file '$_' specified in the path argument must of type .ps1"
            } else {
                 $true
            }
        })]
        [string]$path
        ,
        [parameter(Position = 1, Mandatory=$false)]
        [ValidateRange(2, [int]::MaxValue)]
        [int]$maxLines = 250
    )
    [version]$ProjectVersion = "0.0"
    try {
        $content = Get-Content -Path $path -TotalCount $maxLines | Out-String
        $startIndex = $content.IndexOf(".NOTES")
        $endIndex = $content.IndexOf("#>") + 2
        if ($endIndex -gt $startIndex) {
            $synopsis = $content.Substring($startIndex, $endIndex - $startIndex)
            $arrSynopsis = $synopsis.Split("`n")
            foreach ($line in $arrSynopsis) {
                if ($line.ToLower().Contains('version:')) {
                    [version]$ProjectVersion = $line.Substring($line.IndexOf(":") + 1).Trim()
                    break
                }
            }
        }
    } catch { }
    Return $ProjectVersion
}

function Receive-SftpFile {
    [CmdletBinding()]
    Param(
        [parameter(Mandatory=$true)]
        [string]$server
        ,
        [parameter(Mandatory=$false)]
        [int]$port= 22
        ,
        [parameter(Mandatory=$false)]
        [string]$user
        ,
        [parameter(Mandatory=$false)]
        [string]$password
        ,
        [parameter(Mandatory=$false)]
        [string]$remoteFolder
        ,
        [parameter(Mandatory=$false)]
        [string]$file
        ,
        [parameter(Mandatory=$false)]
        [string]$localFolder
        ,
        [parameter(Mandatory=$false)]
        [string]$comObject= 'IPWorksSSH.SFTP'
        ,
        [parameter(Mandatory=$false)]
        [string]$license= '(you have to buy one license from n/software)'
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $sftp = New-SftpInstance -comObject $comObject
    $sftp.ok = $license
    $sftp.Config("SSHAcceptAnyServerHostKey=true")
    $sftp.Overwrite = $true
    $sftp.SSHHost = $server
    $sftp.SSHPassword = $password
    $sftp.SSHAuthMode = 2
    $sftp.SSHUser = $user
    $sftp.SSHPort = $port
    $sftp.SSHLogon($sftp.SSHHost, $sftp.SSHPort)
    $sftp.LocalFile = "$localFolder\$file"
    $sftp.RemoteFile = "$remoteFolder/$file"
    $sftp.Download()
    $sftp.SSHLogoff()
}

function Remove-DeliveryPropertyItem {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $false)]
        [ValidateScript({
            if ($_ -eq $null -or $_ -ge 0) {return $true} else {return $false}
        })]
        [int]$deliveryNo
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({
            if ($_ -eq $null -or $_ -ge 0) {return $true} else {return $false}
        })]
        [int]$deliveryId
        ,
        [Parameter(Mandatory = $true)]
        [ValidateRange("Positive")]
        [int]$propertyId
        ,
        [Parameter(Mandatory = $false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    try {
        if ($deliveryId) {
            [string]$sqlFrag = "Id = $deliveryId"
        } elseif ($deliveryNo) {
            [string]$sqlFrag = "KopfNummer = $deliveryNo"
        } else {
            throw "Property cannot be removed, either DeliveryId or DeliveryNo parameter must be specified. Function $($myInvocation.MyCommand)"
        }

        [string]$sql = @"
            DECLARE @KopfId INT;
            DECLARE @ObjektId INT;
            SELECT TOP 1 @ObjektId = ID FROM Lieferschein WHERE $sqlFrag;
            SET @KopfId = $propertyId;
            DELETE MerkmalElement WHERE KopfId = @KopfId AND ObjektId = @ObjektId;
"@

        $myConn.Execute($sql) | out-null
    } finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }
}

function Remove-ItemWithRetry {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $true)]
        [string]$path
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $stoploop = $false
    [int]$retrycount = "0"

    do {
        try {
            if (Test-Path $path) {
                if ($path.ToUpper().IndexOf('\TEMP\')) {
                    Remove-Item -Path $path -Recurse -Force
                } else {
                    Remove-Item -Path $path -Force
                }
            }
            $stopLoop = $true
    } catch {
        if ($retryCount -gt 5) {
            Write-Host "ERROR In $($myInvocation.MyCommand) Not possible to delete file $path! $_" -foregroundcolor "red"
            $stopLoop = $true
            $Error.Clear()
    } else {
        $Error.Clear()
        Write-Host "HINT! Removing file '$path' not working. Retry count '$retryCount'. Trying again..." -foregroundcolor "green"
        Start-Sleep -Seconds 10
        $retryCount = $retryCount + 1
    } } }
    While ($stopLoop -eq $false)
}

function Remove-SftpFile {
    [CmdletBinding()]
    Param(
        [parameter(Mandatory=$true)]
        [string]$server
        ,
        [parameter(Mandatory=$false)]
        [int]$port= 22
        ,
        [parameter(Mandatory=$false)]
        [string]$user
        ,
        [parameter(Mandatory=$false)]
        [string]$password
        ,
        [parameter(Mandatory=$false)]
        [string]$remoteFolder
        ,
        [parameter(Mandatory=$true)]
        [string]$file
        ,
        [parameter(Mandatory=$false)]
        [string]$comObject= 'IPWorksSSH.SFTP'
        ,
        [parameter(Mandatory=$false)]
        [string]$license= '(you have to buy one license from n/software)'
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $sftp = New-SftpInstance -comObject $comObject
    $sftp.ok = $license
    $sftp.Config("SSHAcceptAnyServerHostKey=true")
    $sftp.Overwrite = $true
    $sftp.SSHHost = $server
    $sftp.SSHPassword = $password
    $sftp.SSHAuthMode = 2
    $sftp.SSHUser = $user
    $sftp.SSHPort = $port
    $sftp.SSHLogon($sftp.SSHHost, $sftp.SSHPort)
    $sftp.DeleteFile("$remoteFolder/$file")
    $sftp.SSHLogoff()
}

function Rename-MssqlDatabase {
    [CmdletBinding()]
    param(
        [parameter(Mandatory=$true)]
        [string]$oldName
        ,
        [parameter(Mandatory=$true)]
        [string]$newName
        ,
        [parameter(Mandatory=$false)]
        [string]$server
        ,
        [parameter(Mandatory=$false)]
        [string]$user
        ,
        [parameter(Mandatory=$false)]
        [string]$password
        ,
        [parameter(Mandatory=$false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $connParams = @{
        server = $server
        database = 'Master'
        user = $user
        password = $password
        conn = $conn
        udl = $udl
        connStr = $connStr
    }

    # Initialize connection and command objects
    $myConn= Get-Conn -conn $conn -udl $udl -connStr (Get-ConnStr @connParams)
    try {
        [string]$sql = "SELECT DB_ID('$oldName') [DbId]"
        $rs = Get-AdoRs -recordset $myConn.Execute($sql)
        if ($rs) {
            [int]$dbId= $rs.fields('DbId').value
            if (! $dbId) { throw "No DB_ID found!" }
        } else { throw "No valid recordset found! Function: $myInvocation.Mycommand" }

        # Get path the logical file names
        [string]$sql = "SELECT name FROM sys.sysaltfiles WHERE dbid = $dbId and filename like N'%.mdf'"
        $rs = Get-AdoRs -recordset $myConn.Execute($sql)
        if ($rs) {
            [string]$oldLogicalMdf= $rs.fields('name').value
            if (! $oldLogicalMdf) { throw "No path for oldLogicalMdf found! Function: $myInvocation.Mycommand" }
        } else { throw "No valid recordset found! Function: $myInvocation.Mycommand" }

        [string]$sql = "SELECT name FROM sys.sysaltfiles WHERE dbid = $dbId and filename like N'%.ldf'"
        $rs = Get-AdoRs -recordset $myConn.Execute($sql)
        if ($rs) {
            [string]$oldLogicalLdf= $rs.fields('name').value
            if (! $oldLogicalLdf) { throw "No path for oldLogicalLdf found! Function: $myInvocation.Mycommand" }
        } else { throw "No valid recordset found! Function: $myInvocation.Mycommand" }

        # Get path the physical file names
        [string]$sql = "SELECT filename FROM sys.sysaltfiles WHERE dbid = $dbId and filename like N'%.mdf'"
        $rs = Get-AdoRs -recordset $myConn.Execute($sql)
        if ($rs) {
            [string]$oldPhysicalMdf= $rs.fields('filename').value
            if (! $oldPhysicalMdf) { throw "No path for oldPhysicalMdf found! Function: $myInvocation.Mycommand" }
        } else { throw "No valid recordset found! Function: $myInvocation.Mycommand" }

        [string]$sql = "SELECT filename FROM sys.sysaltfiles WHERE dbid = $dbId and filename like N'%.ldf'"
        $rs = Get-AdoRs -recordset $myConn.Execute($sql)
        if ($rs) {
            [string]$oldPhysicalLdf= $rs.fields('filename').value
            if (! $oldPhysicalLdf) { throw "No path for oldPhysicalLdf found! Function: $myInvocation.Mycommand" }
        } else { throw "No valid recordset found! Function: $myInvocation.Mycommand" }

        # Get the path from the data folder and the names from the MDF and LDF file
        [string]$path = Split-Path $oldPhysicalMdf
        $oldPhysicalMdf = Split-Path $oldPhysicalMdf -Leaf
        $oldPhysicalLdf = Split-Path $oldPhysicalLdf -Leaf

        # Rename the database, to do this log out all logged in users by putting
        # the database into single user mode
        [string]$sql =  `
            "ALTER DATABASE [$oldName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE", `
            "ALTER DATABASE [$oldName] MODIFY NAME = [$newName]", `
            "ALTER DATABASE [$newName] SET MULTI_USER"
        $myConn.Execute($sql) | Out-Null

        # Build the new logical filenames
        [string]$newLogicalMdf = $newName
        [string]$newLogicalLdf = $newName + "_Log"

        # Build the new physical filenames
        [string]$newPhysicalMdf = $newName + ".mdf"
        [string]$newPhysicalLdf = $newName + "_log.ldf"

        # Rename the logical file names, this is only possible under their new name,
        # then take them offline to rename the files in the file system as well
        [string]$sql =  `
            "ALTER DATABASE [$newName] MODIFY FILE (NAME = [$oldLogicalMdf], NEWNAME = [$newLogicalMdf])", `
            "ALTER DATABASE [$newName] MODIFY FILE (NAME = [$oldLogicalLdf], NEWNAME = [$newLogicalLdf])", `
            "ALTER DATABASE [$newName] SET offline"
        $myConn.Execute($sql) | Out-Null

        # Prepare sql server for xp_cmdshell
        [string]$sql = `
            "EXEC sp_configure 'show advanced options', 1", `
            "RECONFIGURE", `
            "EXEC sp_configure 'xp_cmdshell', 1", `
            "RECONFIGURE"
        $myConn.Execute($sql) | Out-Null

        # Calls to the sql command shell to rename the file to the new file
        [string]$sql =  `
            "EXEC master..xp_cmdshell 'RENAME ""$path\$oldPhysicalMdf"" ""$newPhysicalMdf""'", `
            "EXEC master..xp_cmdshell 'RENAME ""$path\$oldPhysicalLdf"" ""$newPhysicalLdf""'"
        $myConn.Execute($sql) | Out-Null

        # Makes the new physical files for the database known and puts the DB back online
        [string]$sql =  `
            "ALTER DATABASE [$newName] MODIFY FILE (NAME = [$newLogicalMdf], FILENAME = '$path\$newPhysicalMdf')", `
            "ALTER DATABASE [$newName] MODIFY FILE (NAME = [$newLogicalLdf], FILENAME = '$path\$newPhysicalLdf')", `
            "ALTER DATABASE [$newName] SET online"
        $myConn.Execute($sql) | Out-Null
    } finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }
}

function Resize-Image {
    [CmdletBinding()]
    param(
        [Parameter(ValueFromPipeline=$true, Position=0, Mandatory=$true)]
        [ValidateScript({
            if (Test-Path $_) { $true } else { throw "Der Pfad '$_' existiert nicht." }
        })]
        [string]$pathIn
        ,
        [Parameter(Position=1, Mandatory=$false)]
        [string]$pathOut
        ,
        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [string]$modifier = "-resized"
        ,
        [Parameter(Mandatory=$false)]
        [ValidateRange(10, 100)]
        [int]$quality = 65
        ,
        [Parameter(Mandatory=$false)]
        [ValidateRange(32, 5000)]
        [int]$maxWidth = 1200
        ,
        [Parameter(Mandatory=$false)]
        [ValidateRange(32, 5000)]
        [int]$maxHeight = 1200
        ,
        [Parameter(Mandatory=$false)]
        [switch]$passthru
    )
    Begin {
        Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

        try {
            $useDefaultOutputPath = $pathOut -eq ''
            $pathOutOrg = $pathOut
            [string]$module= "WIA.ImageFile"
            $img = New-Object -COMObject $module
            $imgNew = New-Object -COMObject $module
            [string]$module= "WIA.ImageProcess"
            $imgProcess = New-Object -COMObject $module
            $wiaFormatJpg = "{B96B3CAE-0728-11D3-9D7B-0000F81EF32E}"
            $results = @()
        }
        catch {
            throw "The Windows library '$module' is not available. You can install it in the Windows Setup."
        }
    }

    Process {
        if ($useDefaultOutputPath) {
            $directoryIn = Split-Path $pathIn
            $filenameIn = [System.IO.Path]::GetFileNameWithoutExtension($pathIn)
            $extensionIn = [System.IO.Path]::GetExtension($pathIn)
            $pathOut = Join-Path $directoryIn ($filenameIn + '$modifier' + $extensionIn)
        } else {
            $pathOut = $pathOutOrg
            $directoryOut = Split-Path $pathOut
            $filenameOut = [System.IO.Path]::GetFileNameWithoutExtension($pathOut)
            $extensionOut = [System.IO.Path]::GetExtension($pathOut)

            $directoryIn = Split-Path $pathIn
            $filenameIn = [System.IO.Path]::GetFileNameWithoutExtension($pathIn)
            $extensionIn = [System.IO.Path]::GetExtension($pathIn)

            write-verbose "***** OUTPUT *****"
            write-verbose "DirectoryOut $directoryOut"
            write-verbose "FilenameOut $filenameOut"
            write-verbose "ExtensionOut $extensionOut"

            write-verbose "***** INPUT *****"
            write-verbose "DirectoryIn $directoryIn"
            write-verbose "FilenameIn $filenameIn"
            write-verbose "ExtensionIn $extensionIn"

            if ($directoryOut -eq $directoryIn) {
                $filenameOut = $filenameIn + '$modifier'
            } elseif (($filenameOut -eq $filenameIn) -and ($extensionOut -eq $extensionIn)) {
                $filenameOut = $filenameIn + '$modifier'
            } elseif (! $extensionOut) {
                $filenameOut = $filenameIn
                $extensionOut = $extensionIn
                $directoryOut = $pathOut
            }

            $pathOut = Join-Path $directoryOut ($filenameOut + $extensionOut)
        }

        write-verbose "***** USED *****"
        write-verbose "PFADIN $pathIn"
        write-verbose "PFADOUT $pathOut"

        $img.LoadFile($pathIn)

        $imgProcess.Filters.Add($imgProcess.FilterInfos("Scale").FilterID)
        $imgProcess.Filters(1).Properties("MaximumWidth") = $maxWidth
        $imgProcess.Filters(1).Properties("MaximumHeight") = $maxHeight

        $imgProcess.Filters.Add($imgProcess.FilterInfos("Convert").FilterID)
        $imgProcess.Filters(2).Properties("FormatID").Value = $wiaFormatJpg
        $imgProcess.Filters(2).Properties("Quality").Value = $quality

        $imgNew = $imgProcess.Apply($img)
        If (Test-Path $pathOut) { Remove-Item -path $pathOut -force }
        $imgNew.SaveFile($pathOut)
        $imgProcess.Filters.Remove(2) # Remove the filter backwards
        $imgProcess.Filters.Remove(1)

        $result = [PSCustomObject]@{
            PathOut = $pathOut
        }

        $results += $result

        if ($passthru) {
            # Return $result
        }
    }

    End {
        [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$imgProcess)
        [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$img)
        [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$imgNew)
        if ($passthru) {
            Return $results
        }
    }
}

function Send-Mail {
    [CmdletBinding()]
    Param(
        [Parameter(Mandatory = $true)]
        [string]$from
        ,
        [Parameter(Mandatory = $true)]
        [string[]]$to
        ,
        [string[]]$cc
        ,
        [string[]]$bcc
        ,
        [string]$replyTo = $from
        ,
        [Parameter(Mandatory = $true)]
        [Alias("server")]
        [string]$smtpServer
        ,
        [ValidateSet('Normal', 'High', 'Low', IgnoreCase = $true)]
        [string]$priority= 'Normal'
        ,
        [ValidateSet('utf8NoBOM', 'ascii','bigendianunicode','bigendianutf32', 'oem', 'unicode', 'utf7', 'utf8', 'utf8BOM', 'utf8NoBOM', 'utf32', IgnoreCase = $true)]
        [string]$encoding= 'utf8'
        ,
        [string]$user
        ,
        [string]$password
        ,
        [secureString]$secPassword
        ,
        [psCredential]$credential
        ,
        [switch]$useSsl
        ,
        [Alias("useHtml")]
        [switch]$bodyAsHtml
        ,
        [int]$port= 25
        ,
        [Alias("DNO")]
        [ValidateSet('None', 'OnSuccess', 'OnFailure', 'Delay', 'Never')]
        [string[]]$deliveryNotificationOption = 'None'
        ,
        [Parameter(Mandatory = $true)]
        [string]$subject
        ,
        [string]$body
        ,
        [string[]]$attachment
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    # Allowed values SystemDefault, SSL3, Tls, Tls11, Tls12, Tls13.
    # Since 2012-2023: Tls, Tls11 and Tls12 are working
    [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls -bor `
              [Net.SecurityProtocolType]::Tls11 -bor `
              [Net.SecurityProtocolType]::Tls12
    # Get Credential by Dialog : $Credential = Get-Credential
    # Retruning user is like : $User = $credential.Username
    # Returning plain pPassword : $Password = $credential.GetNetworkCredential().Password

    [System.Object]$credential = $null
    if ( ($user) -and ($password ) ) {
        $secPassword = ConvertTo-SecureString $password -AsPlainText -Force
        $credential = New-Object System.Management.Automation.PSCredential ($user, $secPassword)
    }

    $mailParams = @{
        From = $from
        To = $to.Split(',')
        Subject = $subject
        Port = $port
        Priority = $priority
        Encoding = $encoding
        SmtpServer = $smtpServer
        DeliveryNotificationOption = $deliveryNotificationOption
    }

    if ($cc) {$mailParams.Add('CC', $cc.split(',')) }
    if ($bcc) {$mailParams.Add('BCC', $bcc.split(',')) }
    if ($useSsl) {$mailParams.Add('useSSL', $true) }
    if ($credential) { $mailParams.Add('Credential', $credential ) }
    if ($bodyAsHtml) {$mailParams.Add('bodyAsHtml', $true) }
    if ($body) {$mailParams.Add('Body', $body) }
    if ($attachment) { $mailParams.Add('Attachments', $attachment) }
    if (( $PSVersionTable.PsVersion.Major -gt 6 ) -or `
        (($PSVersionTable.PsVersion.Major -eq 6) -and `
        ($PSVersionTable.PsVersion.Minor -ge 2))) {
        if ($replyTo) {
            $mailParams.Add('ReplyTo', $replyTo )
        }
    }

    try {
        $oldPref = $WarningPreference
        $WarningPreference = 'SilentlyContinue'
        Send-MailMessage @mailParams
        $WarningPreference = $oldPref
        [string]$message = "Email was sent with subject '$subject'"
        if ($attachment) {$message +=  "and attachment: '$attachment'"}

        write-verbose $message
    }
    catch {
        write-host "ERROR! Email was not sent to '$to'! Subject '$subject'. $_" -foregroundcolor Red
    }

    Start-Sleep -Seconds 1
}

function Send-SftpFile {
    [CmdletBinding()]
    Param(
        [parameter(Mandatory=$true)]
        [string]$server
        ,
        [parameter(Mandatory=$false)]
        [int]$port= 22
        ,
        [parameter(Mandatory=$false)]
        [string]$user
        ,
        [parameter(Mandatory=$false)]
        [string]$password
        ,
        [parameter(Mandatory=$false)]
        [string]$remoteFolder
        ,
        [parameter(Mandatory=$true)]
        [string]$file
        ,
        [parameter(Mandatory=$true)]
        [string]$localFolder
        ,
        [parameter(Mandatory=$false)]
        [string]$comObject= 'IPWorksSSH.SFTP'
        ,
        [parameter(Mandatory=$false)]
        [string]$license= '(you have to buy one license from n/software)'
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $sftp = New-SftpInstance -comObject $comObject
    $sftp.ok = $license
    $sftp.Config("SSHAcceptAnyServerHostKey=true")
    $sftp.SSHHost = $server
    $sftp.SSHPassword = $password
    $sftp.SSHAuthMode = 2
    $sftp.SSHUser = $user
    $sftp.SSHPort = $port
    $sftp.SSHLogon($sftp.SSHHost, $sftp.SSHPort)
    $sftp.LocalFile = "$localFolder\$file"
    $sftp.RemoteFile = "$remoteFolder/$file"
    $sftp.Upload()
    $sftp.SSHLogoff()
}

function Set-DeliveryQty {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $true)]
        [ValidateScript({
            if($_.Count -lt 1) {
                throw "The array 'quantities' must contain at least one hashtable element with a pair of 'articleNo' and 'qty'."
            }
            $true
        })]
        [array]$quantities
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({
            if ($_ -eq $null -or $_ -ge 0) {return $true} else {return $false}
        })]
        [int]$deliveryNo
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({
            if ($_ -eq $null -or $_ -ge 0) {return $true} else {return $false}
        })]
        [int]$deliveryId
        ,
        [Parameter(Mandatory = $false)]
        [string]$bookingInfo
        ,
        [Parameter(Mandatory = $false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    if ($deliveryId) {
        [string]$sqlFrag = "@lf_id = $deliveryId,"
    } elseif ($deliveryNo) {
        [string]$sqlFrag = "@KopfNummer = $deliveryNo,"
    } else {
        throw "There is neither a variable with valid delivery note ID nor a delivery " +`
              "note number passed!! Function: $($myInvocation.MyCommand) bookingInfo: $bookingInfo"
    }

    $xmlFrag = @(
        $quantities.foreach({
            "<UPDATE><ARTNUMMER>" + [Security.SecurityElement]::Escape($_.articleNo) + "</ARTNUMMER>" +
            "<MENGE>$($_.qty)</MENGE></UPDATE>"})
    );
    $xmlFrag = $xmlFrag -join "`r`n"

    $sql = @"
    set nocount on;
    declare @Updates xml = N'$xmlFrag';
    declare @exec int;
 
    exec @exec = dbo.cn_LfUpdateArFromXml
    $sqlFrag
    @Updates = @Updates,
    @xmlVersion = 1,
    @AfStornoRest = 1,
    @AfStornoGrund = '$bookingInfo'
 
    if @exec < 0 PRINT 'FEHLER'
"@

    try {
        $myConn.Execute($sql) | out-null
    } finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }

    <#
        if ($exec -lt 0) {
            throw "The sql function to set the new delivery quantities throw an exception! Sql: $sql"
        }
    #>
}

function Set-StockQty {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $true)]
        [ValidateScript({
            if($_.Count -lt 1) {
                throw "The array 'quantities' must contain at least one hashtable element with a pair of 'articleNo' and 'qty'."
            }
            $true
        })]
        [array]$quantities
        ,
        [Parameter(Mandatory = $false)]
        [int]$stockGroup = 1
        ,
        [Parameter(Mandatory = $false)]
        [string]$bookingInfo
        ,
        [Parameter(Mandatory = $false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    $xmlFrag = @(
        $quantities.foreach({
            "<item><ARTNUMMER>" + [Security.SecurityElement]::Escape($_.articleNo) + "</ARTNUMMER>" +
            "<MENGE>$($_.qty)</MENGE></item>"})
    );
    $xmlFrag = $xmlFrag -join "`r`n"

    $bookingInfo = if ($bookingInfo) {"'" + $bookingInfo.replace("'","''") + "'"} else {'null'};

    $sql = @"
    set nocount on;
    declare @x xml = N'$xmlFrag';
 
    select error from dbo.cnf_ExtractArMenge(@x,1) where error is not null;
 
    declare @Mengen [dbo].[TIdQtyTable], @ll_id int;
 
    insert @Mengen (id, quantity)
    select ArtikelId, Menge
    from dbo.cnf_ExtractArMenge(@x,0);
 
    exec dbo.cn_LL_SetBestandAbsolut
        @LagerGr = $stockGroup,
        @Mengen = @Mengen,
        @Objekt = $bookingInfo,
        @ll_id = @ll_id OUT;
"@

    try {
        $myConn.Execute($sql) | out-null
    } finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }
}

function Set-TrackingNo {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory=$true)]
        [System.Array]$trackingNo
        ,
        [Parameter(Mandatory=$false)]
        [ValidateScript({
            if ($_ -eq $null -or $_ -ge 0) {return $true} else {return $false}
        })]
        [int]$deliveryId
        ,
        [Parameter(Mandatory=$false)]
        [ValidateScript({
            if ($_ -eq $null -or $_ -ge 0) {return $true} else {return $false}
        })]
        [int]$deliveryNo
        ,
        [Parameter(Mandatory = $false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    try {
        if ($deliveryId) {
            [string]$sqlFrag = "Id = $deliveryId"
        } elseif ($deliveryNo) {
            [string]$sqlFrag = "KopfNummer = $deliveryNo"
        } else {
            throw "Neither a delivery note id is specified, nor a delivery note number. Function: $($myInvocation.MyCommand)"
        }

        [string]$sql = "SELECT Id, KopfNummer [DeliveryNo], SpedAuftragNr [CarrierNo], TrackingNr [TrackingNo], " +`
                       "AnzahlPakete [Units], VersandDatum [ShippingDate] FROM Lieferschein WHERE $sqlFrag"

        $rs = $Null
        $rs = new-object -comObject ADODB.Recordset
        $rs.CursorLocation = $global:adUseClient
        $rs.Open($sql, $myConn, $global:adOpenKeyset, $global:adLockOptimistic, $global:adCmdText)
        if (! $rs.eof) {
            [string]$carrierNo = $rs.fields('CarrierNo').value
            # Only if this is the secoond document we have for this delivery note
            # be need to keep the old trackings also. This is indicated by an existing carrierNo.
            if ($carrierNo) {
                [string]$tempStr = $rs.fields('TrackingNo').value
                [int]$oldUnits = $rs.fields('Units').value
            } else {
                [string]$tempStr = ""
                [int]$oldUnits = 0
            }
            [string]$oldValue = $tempStr
            $tempStr = $tempStr.Replace("`r","").Trim()
            [System.Array]$tempArray = [System.Collections.ArrayList]::new()
            $tempArray = $tempStr.split("`n")
            $tempArray = $tempArray + $trackingNo
            $tempArray = $tempArray | Select-Object -Unique
            $tempArray = $tempArray | Sort-Object -Descending
            $tempStr = $tempArray -join "`r`n"
            $tempStr = $tempStr.Trim()
            $tempArray = $tempStr.Split("`r`n")
            # Avoid unnecessary change dates in the database
            if (($oldValue -ne $tempStr) -or ($oldUnits -ne $tempArray.count)) {
                $rs.fields('TrackingNo').value = $tempStr
                $rs.fields('Units').value = [int]$tempArray.count
                $rs.fields('ShippingDate').value = (Get-Date).ToString("yyyy-MM-dd HH:mm:ss")
                $rs.update()
            }
        } else {
            throw "The delivery note does not exist in EULANDA-ERP system! DeliveryNo: $deliveryNo"
        }
    } finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }
}

function Show-Extensions {
    [CmdletBinding()]
    param()
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    Push-Location
    Set-Location HKCU:\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced
    if ((Get-ItemProperty .).HideFileExt -eq 1) {
        Set-ItemProperty . HideFileExt 0
        Start-Sleep -Milliseconds 500
        Update-Desktop
    }
    Pop-Location
}

function Show-MsgBox {
    [CmdletBinding()]
    param(
        [Parameter(Position = 0, Mandatory = $true)]
        [string]$prompt
        ,
        [Parameter(Position = 1, Mandatory = $false)]
        [ValidateSet(0,1,2,3,4,5)]
        [int]$btn = $mbYesNo
        ,
        [Parameter(Position = 2, Mandatory = $false)]
        [string]$title = 'Info'
        ,
        [Parameter(Position = 3, Mandatory = $false)]
        [ValidateSet(0,16,32,48,64)]
        [int]$icon = $mbInfo
        ,
        [Parameter(Position = 4, Mandatory = $false)]
        [ValidateSet(0,256,512,768)]
        [int]$btnDef = $mbNone
    )


    Add-Type -AssemblyName PresentationFramework | Out-Null
    [int]$result = [System.Windows.MessageBox]::Show((new-object System.Windows.Window -Property @{TopMost = $True}), $prompt, $title, $btn, $icon, $btnDef)

    Return $result
}

function Show-MsgBoxYes {
    [CmdletBinding()]
    param(
        [Parameter(Position = 0, Mandatory = $true)]
        [string]$prompt
    )

    [int]$Result = Show-MsgBox -prompt $prompt -title 'Info' -icon $mbNone -btnDef $mbNone
    if ($result -eq $mbrYes) {
        Return $true
    } else {
        Return $false
    }
}

function Test-Administrator {
    [CmdletBinding()]
    param()
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $User = New-Object Security.Principal.WindowsPrincipal $([Security.Principal.WindowsIdentity]::GetCurrent())
    $User.IsInRole([Security.Principal.WindowsBuiltinRole]::Administrator)
}

function Test-ArticlePropertyItem {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $true)]
        [ValidateRange("Positive")]
        [Int]$articleId
        ,
        [Parameter(Mandatory = $true)]
        [ValidateRange("Positive")]
        [int]$propertyId
        ,
        [Parameter(Mandatory = $false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    [bool]$result = $false
    try {
        [string]$sql = @"
        SELECT CASE WHEN EXISTS
            (SELECT me.ObjektId
                FROM MerkmalElement me
                JOIN Merkmal m ON
                    m.Id = me.KopfId AND
                    m.id = $propertyId AND
                    m.Tabelle = 'Artikel' AND m.MerkmalTyp = 1
            WHERE me.ObjektId = $articleId )
        THEN 1 ELSE 0 END [Item]
"@

        $rs = $Null
        $rs = $myConn.Execute($sql)
        if ($rs) {
            if (! $rs.eof) {
                if ($rs.fields('Item').Value -eq 1) {
                    $result = $true
                }
            }
        }
    } finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }

    Return $result
}

function Test-HasProperty {
    [CmdletBinding()]
    param(
        $inputVar
        ,
        [string]$propertyName
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [bool]$result = $false
    if ($propertyName) {
        if ($inputVar) {
            if ($null -ne $inputVar -and -not $inputVar.GetType().IsValueType) {
                $result = ($propertyName -in $inputVar.PSobject.Properties.Name)
            }
        }
    }
    return $result
}

function Test-SalesOrder {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $false)]
        [string]$customerOrderNo
        ,
        [Parameter(Mandatory = $false)]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr

    try {
        [string]$Sql = "SELECT Id FROM Auftrag WHERE BestellNummer = '$customerOrderNo'"
        $rs = $Null
        [bool]$result = $false
        $rs = $myConn.Execute($sql)
        if ($rs) {
            if (! $rs.eof) {
                [bool]$result = $true
            }
        }
    } finally {
        if ($global:adCloseOnExit) {
            $myConn.Close()
        }
    }

    Return $result
}

function Test-Verbose {
    [CmdletBinding()]
    param()
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [bool](Write-Verbose ([String]::Empty) 4>&1)
}

function Test-XmlSchema {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory=$true)]
        [string] $xmlFile
        ,
        [Parameter(Mandatory=$true)]
        [string] $schemaFile
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [string[]]$Script:xmlValidationErrorLog = @()
    [scriptblock] $validationEventHandler = {
        $Script:xmlValidationErrorLog += "$($args[1].Exception.Message)`r`n"
    }

    $xml = New-Object System.Xml.XmlDocument
    $schemaReader = New-Object System.Xml.XmlTextReader $schemaFile
    $schema = [System.Xml.Schema.XmlSchema]::Read($schemaReader, $validationEventHandler)
    $xml.Schemas.Add($schema) | Out-Null
    $xml.Load($XmlFile)
    $xml.Validate($validationEventHandler)

    if ($Script:xmlValidationErrorLog) {
        Write-Warning "$($Script:xmlValidationErrorLog.Count) errors found"
        Throw "$Script:xmlValidationErrorLog"
    }
    else {
        Write-Verbose "The '$(Get-Filename -path $xmlFile)' file has been successfully validated against the '$(Get-Filename -path $schemaFile)' schema."
    }
}

function Unprotect-String {
    [CmdletBinding()]
    param(
        [Parameter(Position=0, Mandatory = $true)]
        $protectedText,
        [Parameter(Position=1, Mandatory = $true)]
        $key
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $key = Get-ProtectedKey $key
    $protectedText | ConvertTo-SecureString -key $key |
        ForEach-Object {
            [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($_))
        }
}

function Update-Desktop {
    [CmdletBinding()]
    param()
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    try {
        $c= Add-Type -Name WinAPI -PassThru -MemberDefinition @'
        [DllImport("user32.dll")] public static extern IntPtr GetShellWindow();
        [DllImport("user32.dll")] public static extern int SendMessageW(IntPtr hWnd, uint Msg, UIntPtr wParam, IntPtr lParam);
'@
        $dsktp=$c::GetShellWindow();
        $WM_COMMAND=273;
        $accel_F5=New-Object UIntPtr(41504);
        $nullptr=[IntPtr]::Zero;
        [int](($dsktp -eq $nullptr) -or ($c::SendMessageW($dsktp, $WM_COMMAND, $accel_F5, $nullptr) -ne 0)) | Out-Null
    } catch { }

}

Function Use-Culture {
    [CmdletBinding()]
    param(
        [System.Globalization.CultureInfo]$culture = (throw "USAGE: Use-Culture -culture culture -script {ScriptBlock}")
        ,
        [ScriptBlock]$script= (throw "USAGE: Use-Culture -culture culture -script {ScriptBlock}")
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    $oldCulture = [System.Threading.Thread]::CurrentThread.CurrentCulture
    trap
    {
        [System.Threading.Thread]::CurrentThread.CurrentCulture = $oldCulture
    }
    [System.Threading.Thread]::CurrentThread.CurrentCulture = $culture
    Invoke-Command $script
    [System.Threading.Thread]::CurrentThread.CurrentCulture = $oldCulture
}

function Write-XmlMetadata {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory=$true)]
        $writer
        ,
        [Parameter(Mandatory=$false)]
        [ValidateSet('none', 'upper', 'lower','capital')]
        [string]$strCase = 'upper'
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [string]$nodeName = (Convert-StringCase -value ('Metadata') -strCase $strCase)
    $writer.WriteStartElement($nodeName)
        try {
            [string]$path = "$(Get-ModulePath('eulLib'))\..\..\EulandaConnect.ps1"
            [string]$version = "$((Test-ScriptFileInfo -Path $path).Version)"
        }
        catch {
            [string]$version = ''
        }
        [string]$nodeName = (Convert-StringCase -value ('Version') -strCase $strCase)
        $writer.WriteElementString($nodeName,$version)

        [string]$nodeName = (Convert-StringCase -value ('Generator') -strCase $strCase)
        $writer.WriteElementString($nodeName,'EulandaConnect')

        [string]$nodeName = (Convert-StringCase -value ('Dateformat') -strCase $strCase)
        $writer.WriteElementString($nodeName,'ISO8601')

        [string]$nodeName = (Convert-StringCase -value ('Floatformat') -strCase $strCase)
        $writer.WriteElementString($nodeName,'US')

        [string]$nodeName = (Convert-StringCase -value ('Countryformat') -strCase $strCase)
        $writer.WriteElementString($nodeName,'ISO2')

        [string]$nodeName = (Convert-StringCase -value ('Fieldnames') -strCase $strCase)
        $writer.WriteElementString($nodeName,'NATIVE')

        [string]$nodeName = (Convert-StringCase -value ('Date') -strCase $strCase)
        $writer.WriteElementString($nodeName,(Convert-DateToIso -value $(Get-Date) -noTimeZone ))

        [string]$nodeName = (Convert-StringCase -value ('PCName') -strCase $strCase)
        $writer.WriteElementString($nodeName, "$env:COMPUTERNAME".ToUpper())

        [string]$nodeName = (Convert-StringCase -value ('UserName') -strCase $strCase)
        $writer.WriteElementString($nodeName, "$env:USERNAME".ToUpper())

    $writer.WriteEndElement()

    Return $writer
}




# -----------------------------------------------------------------------------
# Private functions
# -----------------------------------------------------------------------------

function Find-SqlBrowser {
    [CmdletBinding()]
    param(
        [string]$ip
        ,
        [int]$port = 1434
        ,
        [int]$timeout = 300
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    try {
        $instances = New-Object System.Collections.ArrayList
        $toASCII = New-Object System.Text.AsciiEncoding

        $udpEndpoint = New-Object System.Net.IpEndpoint([system.net.ipaddress]::Any,0)
        $udpPacket = 0x02,0x00,0x00

        $udpClient = New-Object System.Net.Sockets.Udpclient
        $udpClient.Client.ReceiveTimeout = $timeout
        $udpClient.Connect($ip,$port)
        $udpClient.Client.Blocking = $true

        [void]$udpClient.Send($udpPacket, $udpPacket.length)
        $bytesRecived = $udpClient.Receive([ref]$udpEndpoint)
        [string]$result = $toASCII.GetString($bytesRecived)

        If ($result) {
            $result = $result.Substring(3,$result.Length-3)
            $result = $result.Replace(';;',"`t")
            $list = $result.Split("`t").trim()
            $list = $list | Where-Object { $_ -ne "" }

            foreach ($item in $list) {
                $pairs = $item.Split(';')
                $tempObj= [PSCustomObject]@{
                    Ip=$Ip
                  }
                for ($i = 0; $i -lt $pairs.Length; $i += 2) {
                    $key = $pairs[$i]
                    $value = $pairs[$i+1]
                    Add-Member -InputObject $tempObj -MemberType NoteProperty -Name $key -Value $value
                }

                $instances.Add($tempObj) | Out-Null
            }

        }

    }
    catch {
        # write-host "$_"
    }
    finally {
        $udpClient.close()
    }
    Return $instances
}

function Get-ConnFromStr {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [string]$connStr
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [long]$adUseClient = 3
    [long]$adTimeout = 60*20

    $conn = new-object -comObject ADODB.Connection
    $conn.CursorLocation = $adUseClient
    $conn.ConnectionString = $connStr
    $conn.CommandTimeout = $adTimeout
    $conn.open()

    Return $conn
}

function Get-ConnFromUdl {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [string]$udl
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [long]$adUseClient = 3
    [long]$adTimeout = 60*20

    $conn = new-object -comObject ADODB.Connection
    $conn.CursorLocation = $adUseClient
    $conn.ConnectionString = "File Name=$udl"
    $conn.CommandTimeout = $adTimeout
    $conn.open()

    Return $conn
}

function Get-ProtectedKey {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $true)]
        [string]$key
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    if ($key.length -lt 16) {
        $key = $key.PadRight(16,' ')
    } elseif ($key.length -lt 32) {
        $key = $key.PadRight(32,' ')
    } else {
        Throw "The key should contain no more than 32 characters. The best security is 32 characters, otherwise 16 characters!"
    }

    $length = $key.length
    $pad = 32-$length
    $encoding = New-Object System.Text.ASCIIEncoding
    $bytes = $encoding.GetBytes($key + "0" * $pad)

    return $bytes
}

function Get-PunctuationIdx {
    [CmdletBinding()]
    Param(
        [string]$text
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [int]$idx = -1

    [int]$i = Get-PunctuationIdxByChar -text $text -Match '.'
    if ($i -gt $idx) { $idx = $i }

    [int]$i = Get-PunctuationIdxByChar -text $text -Match '!'
    if ($i -gt $idx) { $idx = $i }

    [int]$i = Get-PunctuationIdxByChar -text $text -Match '?'
    if ($i -gt $idx) { $idx = $i }

    if ($idx -eq -1) {
        # if no punctuation is found, then take space char
        [int]$i = Get-PunctuationIdxByChar -text  $text -Match  ' '
        if ($i -gt $idx) { $idx = $i }
    }

    if ($idx -eq -1 ) { $idx = $text.Length-1 }
    return $idx
}

function Get-PunctuationIdxByChar {
    [CmdletBinding()]
    Param(
        [string]$text,
        [string]$match
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [int]$idx = -1
    while (1 -eq 1 ) {
        [Int]$Idx = $text.lastIndexOf($match)
        if ($match -ne ' ') {
            if (($idx -ne -1) -and ($idx -lt $text.length-1)) {
                if ($text.Substring($idx+1, 1) -eq ' ') {
                    break
                } else {
                    if ($text.length -gt 1) {
                        $text = $Text.SubString(0, $idx)
                    } else { break }
                }
            } else { break }
        } else { break }
    }

    if ($idx -le 0) {
        return -1
    } else {
        if ($match -eq ' ') {
            return $idx
        } else { return $idx+1 }
    }
}

function Get-RandomParagraph {
    param(
        [int]$MinSentences = 2,
        [int]$MaxSentences = 4
    )
    $numSentences = Get-Random -Minimum $MinSentences -Maximum $MaxSentences

    $sentence = "$(Get-RandomWords)$(Get-RandomPunctuation)"


    for ($i = 1; $i -le $numSentences; $i++) {
        $sentence = "$(Get-RandomWords)$(Get-RandomPunctuation)"
        if (! $sentences) {
            $sentences = $sentence
        } else { $sentences += " $sentence" }
    }

    return $sentences.TrimEnd()
}

function Get-RandomPunctuation {
    $punctuations = @(".", ".", ".", ".", ".", "!", "!", "!", "?", "?")
    $index = Get-Random -Minimum 0 -Maximum $punctuations.Count

    return $punctuations[$index]
}

function Get-RandomWords {
    param(
        [int]$MinWords = 5,
        [int]$MaxWords = 20
    )

    $loremIpsum = "Lorem ipsum dolor sit amet consectetur adipiscing elit sed do " +`
        "eiusmod tempor incididunt ut labore et dolore magna aliqua ut enim ad minim " +`
        "veniam quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea " +`
        "commodo consequat Duis aute irure dolor in reprehenderit in voluptate " +`
        "velit esse cillum dolore eu fugiat nulla pariatur excepteur sint occaecat " +`
        "cupidatat non proident sunt in culpa qui officia deserunt mollit anim id est " +`
        "laborum"


    $words = $loremIpsum.Split() | Where-Object { $_.Length -gt 1 }
    $numWords = Get-Random -Minimum $MinWords -Maximum $MaxWords

    $result = ""

    for ($i = 1; $i -le $numWords; $i++) {
        $word = $words[(Get-Random -Minimum 0 -Maximum $words.Count)]

        if ($i -eq 1) {
            $word = $word.Substring(0, 1).ToUpper() + $word.Substring(1)
        }

        if ((Get-Random -Minimum 0 -Maximum 10) -eq 0) {
            $word = $word.Substring(0, 1).ToUpper() + $word.Substring(1)
        }

        $result += $word + " "
    }

    return $result.TrimEnd()
}

function Get-TranslateExtractTag {
    [CmdletBinding()]
    param(
        [string]$value
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [string]$iso = ""
    [string]$SubVar = ""
    [string[]]$result = @("", "")

    $value = $value.Substring(0, $value.Length-1) # delete last
    $value = $value.Substring(1, $value.Length-1) # delete first
    $value = $value.ToUpper()

    # check for an optional part
    $p = $value.IndexOf(":")
    if ($p -eq -1) {
        $iso = $value.ToUpper()
    } else {
        if ($p -eq 0 ) {
            $subVar = $value.Substring(1, $value.Length-1).ToUpper()
            $iso = "00" # Default
        } else {
            $iso = $value.Substring(0, 2).ToUpper()
            $subVar = $value.SubString(3, $value.Length-3).ToUpper()
        }

    }
    $result[0] = $iso
    $result[1] = $subVar
    Return $result
}

function Get-TranslateIsDelim {
    [CmdletBinding()]
    param(
        [string]$value
    )

    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    [bool]$result = $false
    # test if the first character is an open bracket
    if ($value.SubString(0,1) -eq "[") {
        $value = $value.TrimEnd()
        # test if the last character is a closed bracket
        if ($value.Substring($value.Length-1) -eq "]") {
            $value = $value.Substring(0, $value.Length-1)  # delete last
            $value = $value.Substring(1, $value.Length-1)  # delete first

            # check if there is an optional part
            $p = $value.IndexOf(":")
            if ($p -eq -1) {
                # without optional part the iso tag must be 2 characters long
                if ($value.Length -eq 2) {$result = $True } else { $result = $False }
            } else {
                # valid positions for the colon are the first or the third (after the iso-tag)
                if (($p -eq 0) -or ($p -eq 2)) { $result = $True } else { $result = $False }
            }
        }
    }
    Return $result
}

function New-SftpInstance {
    [CmdletBinding()]
    param(
        [parameter(Mandatory=$false)]
        [string]$comObject= 'IPWorksSSH.SFTP'
    )
    Write-Verbose -Message ('Starting: {0}' -f $MyInvocation.Mycommand)

    try {
        $sftp = New-Object -COMObject $comObject
    }
    catch {
        throw "The SFTP library (ocx) '$comObject' is not registred on this pc. " +`
              "Register it with 'regsvr32 path\sftp160.ocx'. " +`
              "ATTENTION: The name of the OCX could be diffrent on your system!"
    }

    Return $sftp
}


# SIG # Begin signature block
# MIIpigYJKoZIhvcNAQcCoIIpezCCKXcCAQExDzANBglghkgBZQMEAgEFADB5Bgor
# BgEEAYI3AgEEoGswaTA0BgorBgEEAYI3AgEeMCYCAwEAAAQQH8w7YFlLCE63JNLG
# KX7zUQIBAAIBAAIBAAIBAAIBADAxMA0GCWCGSAFlAwQCAQUABCCvbC6eKh+kKnAg
# mi24rv1ffiyXxRuWY63uSYfa7jWH56CCEngwggVvMIIEV6ADAgECAhBI/JO0YFWU
# jTanyYqJ1pQWMA0GCSqGSIb3DQEBDAUAMHsxCzAJBgNVBAYTAkdCMRswGQYDVQQI
# DBJHcmVhdGVyIE1hbmNoZXN0ZXIxEDAOBgNVBAcMB1NhbGZvcmQxGjAYBgNVBAoM
# EUNvbW9kbyBDQSBMaW1pdGVkMSEwHwYDVQQDDBhBQUEgQ2VydGlmaWNhdGUgU2Vy
# dmljZXMwHhcNMjEwNTI1MDAwMDAwWhcNMjgxMjMxMjM1OTU5WjBWMQswCQYDVQQG
# EwJHQjEYMBYGA1UEChMPU2VjdGlnbyBMaW1pdGVkMS0wKwYDVQQDEyRTZWN0aWdv
# IFB1YmxpYyBDb2RlIFNpZ25pbmcgUm9vdCBSNDYwggIiMA0GCSqGSIb3DQEBAQUA
# A4ICDwAwggIKAoICAQCN55QSIgQkdC7/FiMCkoq2rjaFrEfUI5ErPtx94jGgUW+s
# hJHjUoq14pbe0IdjJImK/+8Skzt9u7aKvb0Ffyeba2XTpQxpsbxJOZrxbW6q5KCD
# J9qaDStQ6Utbs7hkNqR+Sj2pcaths3OzPAsM79szV+W+NDfjlxtd/R8SPYIDdub7
# P2bSlDFp+m2zNKzBenjcklDyZMeqLQSrw2rq4C+np9xu1+j/2iGrQL+57g2extme
# me/G3h+pDHazJyCh1rr9gOcB0u/rgimVcI3/uxXP/tEPNqIuTzKQdEZrRzUTdwUz
# T2MuuC3hv2WnBGsY2HH6zAjybYmZELGt2z4s5KoYsMYHAXVn3m3pY2MeNn9pib6q
# RT5uWl+PoVvLnTCGMOgDs0DGDQ84zWeoU4j6uDBl+m/H5x2xg3RpPqzEaDux5mcz
# mrYI4IAFSEDu9oJkRqj1c7AGlfJsZZ+/VVscnFcax3hGfHCqlBuCF6yH6bbJDoEc
# QNYWFyn8XJwYK+pF9e+91WdPKF4F7pBMeufG9ND8+s0+MkYTIDaKBOq3qgdGnA2T
# OglmmVhcKaO5DKYwODzQRjY1fJy67sPV+Qp2+n4FG0DKkjXp1XrRtX8ArqmQqsV/
# AZwQsRb8zG4Y3G9i/qZQp7h7uJ0VP/4gDHXIIloTlRmQAOka1cKG8eOO7F/05QID
# AQABo4IBEjCCAQ4wHwYDVR0jBBgwFoAUoBEKIz6W8Qfs4q8p74Klf9AwpLQwHQYD
# VR0OBBYEFDLrkpr/NZZILyhAQnAgNpFcF4XmMA4GA1UdDwEB/wQEAwIBhjAPBgNV
# HRMBAf8EBTADAQH/MBMGA1UdJQQMMAoGCCsGAQUFBwMDMBsGA1UdIAQUMBIwBgYE
# VR0gADAIBgZngQwBBAEwQwYDVR0fBDwwOjA4oDagNIYyaHR0cDovL2NybC5jb21v
# ZG9jYS5jb20vQUFBQ2VydGlmaWNhdGVTZXJ2aWNlcy5jcmwwNAYIKwYBBQUHAQEE
# KDAmMCQGCCsGAQUFBzABhhhodHRwOi8vb2NzcC5jb21vZG9jYS5jb20wDQYJKoZI
# hvcNAQEMBQADggEBABK/oe+LdJqYRLhpRrWrJAoMpIpnuDqBv0WKfVIHqI0fTiGF
# OaNrXi0ghr8QuK55O1PNtPvYRL4G2VxjZ9RAFodEhnIq1jIV9RKDwvnhXRFAZ/ZC
# J3LFI+ICOBpMIOLbAffNRk8monxmwFE2tokCVMf8WPtsAO7+mKYulaEMUykfb9gZ
# pk+e96wJ6l2CxouvgKe9gUhShDHaMuwV5KZMPWw5c9QLhTkg4IUaaOGnSDip0TYl
# d8GNGRbFiExmfS9jzpjoad+sPKhdnckcW67Y8y90z7h+9teDnRGWYpquRRPaf9xH
# +9/DUp/mBlXpnYzyOmJRvOwkDynUWICE5EV7WtgwggYcMIIEBKADAgECAhAz1wio
# kUBTGeKlu9M5ua1uMA0GCSqGSIb3DQEBDAUAMFYxCzAJBgNVBAYTAkdCMRgwFgYD
# VQQKEw9TZWN0aWdvIExpbWl0ZWQxLTArBgNVBAMTJFNlY3RpZ28gUHVibGljIENv
# ZGUgU2lnbmluZyBSb290IFI0NjAeFw0yMTAzMjIwMDAwMDBaFw0zNjAzMjEyMzU5
# NTlaMFcxCzAJBgNVBAYTAkdCMRgwFgYDVQQKEw9TZWN0aWdvIExpbWl0ZWQxLjAs
# BgNVBAMTJVNlY3RpZ28gUHVibGljIENvZGUgU2lnbmluZyBDQSBFViBSMzYwggGi
# MA0GCSqGSIb3DQEBAQUAA4IBjwAwggGKAoIBgQC70f4et0JbePWQp64sg/GNIdMw
# hoV739PN2RZLrIXFuwHP4owoEXIEdiyBxasSekBKxRDogRQ5G19PB/YwMDB/NSXl
# wHM9QAmU6Kj46zkLVdW2DIseJ/jePiLBv+9l7nPuZd0o3bsffZsyf7eZVReqskmo
# PBBqOsMhspmoQ9c7gqgZYbU+alpduLyeE9AKnvVbj2k4aOqlH1vKI+4L7bzQHkND
# brBTjMJzKkQxbr6PuMYC9ruCBBV5DFIg6JgncWHvL+T4AvszWbX0w1Xn3/YIIq62
# 0QlZ7AGfc4m3Q0/V8tm9VlkJ3bcX9sR0gLqHRqwG29sEDdVOuu6MCTQZlRvmcBME
# Jd+PuNeEM4xspgzraLqVT3xE6NRpjSV5wyHxNXf4T7YSVZXQVugYAtXueciGoWnx
# G06UE2oHYvDQa5mll1CeHDOhHu5hiwVoHI717iaQg9b+cYWnmvINFD42tRKtd3V6
# zOdGNmqQU8vGlHHeBzoh+dYyZ+CcblSGoGSgg8sCAwEAAaOCAWMwggFfMB8GA1Ud
# IwQYMBaAFDLrkpr/NZZILyhAQnAgNpFcF4XmMB0GA1UdDgQWBBSBMpJBKyjNRsjE
# osYqORLsSKk/FDAOBgNVHQ8BAf8EBAMCAYYwEgYDVR0TAQH/BAgwBgEB/wIBADAT
# BgNVHSUEDDAKBggrBgEFBQcDAzAaBgNVHSAEEzARMAYGBFUdIAAwBwYFZ4EMAQMw
# SwYDVR0fBEQwQjBAoD6gPIY6aHR0cDovL2NybC5zZWN0aWdvLmNvbS9TZWN0aWdv
# UHVibGljQ29kZVNpZ25pbmdSb290UjQ2LmNybDB7BggrBgEFBQcBAQRvMG0wRgYI
# KwYBBQUHMAKGOmh0dHA6Ly9jcnQuc2VjdGlnby5jb20vU2VjdGlnb1B1YmxpY0Nv
# ZGVTaWduaW5nUm9vdFI0Ni5wN2MwIwYIKwYBBQUHMAGGF2h0dHA6Ly9vY3NwLnNl
# Y3RpZ28uY29tMA0GCSqGSIb3DQEBDAUAA4ICAQBfNqz7+fZyWhS38Asd3tj9lwHS
# /QHumS2G6Pa38Dn/1oFKWqdCSgotFZ3mlP3FaUqy10vxFhJM9r6QZmWLLXTUqwj3
# ahEDCHd8vmnhsNufJIkD1t5cpOCy1rTP4zjVuW3MJ9bOZBHoEHJ20/ng6SyJ6UnT
# s5eWBgrh9grIQZqRXYHYNneYyoBBl6j4kT9jn6rNVFRLgOr1F2bTlHH9nv1HMePp
# GoYd074g0j+xUl+yk72MlQmYco+VAfSYQ6VK+xQmqp02v3Kw/Ny9hA3s7TSoXpUr
# OBZjBXXZ9jEuFWvilLIq0nQ1tZiao/74Ky+2F0snbFrmuXZe2obdq2TWauqDGIgb
# MYL1iLOUJcAhLwhpAuNMu0wqETDrgXkG4UGVKtQg9guT5Hx2DJ0dJmtfhAH2KpnN
# r97H8OQYok6bLyoMZqaSdSa+2UA1E2+upjcaeuitHFFjBypWBmztfhj24+xkc6Zt
# CDaLrw+ZrnVrFyvCTWrDUUZBVumPwo3/E3Gb2u2e05+r5UWmEsUUWlJBl6MGAAjF
# 5hzqJ4I8O9vmRsTvLQA1E802fZ3lqicIBczOwDYOSxlP0GOabb/FKVMxItt1UHeG
# 0PL4au5rBhs+hSMrl8h+eplBDN1Yfw6owxI9OjWb4J0sjBeBVESoeh2YnZZ/WVim
# VGX/UUIL+Efrz/jlvzCCBuEwggVJoAMCAQICEGilgQZhq4aQSRu7qELTizkwDQYJ
# KoZIhvcNAQELBQAwVzELMAkGA1UEBhMCR0IxGDAWBgNVBAoTD1NlY3RpZ28gTGlt
# aXRlZDEuMCwGA1UEAxMlU2VjdGlnbyBQdWJsaWMgQ29kZSBTaWduaW5nIENBIEVW
# IFIzNjAeFw0yMjAxMjcwMDAwMDBaFw0yNDAxMjcyMzU5NTlaMIGmMRIwEAYDVQQF
# EwlIUkIgMTg4NzAxEzARBgsrBgEEAYI3PAIBAxMCREUxHTAbBgNVBA8TFFByaXZh
# dGUgT3JnYW5pemF0aW9uMQswCQYDVQQGEwJERTEPMA0GA1UECAwGSGVzc2VuMR4w
# HAYDVQQKDBVFVUxBTkRBIFNvZnR3YXJlIEdtYkgxHjAcBgNVBAMMFUVVTEFOREEg
# U29mdHdhcmUgR21iSDCCAiIwDQYJKoZIhvcNAQEBBQADggIPADCCAgoCggIBAMzT
# O8Mbj+llr7AC7DuCfXcQUJJsAnYeHudXCXyw/vCpdBJLvd3IMkk0EGtl/owTlq9t
# m6tdzPTjh4bUjJQVqmsdfo15EldPOI9fTuaoG4xBDeJwhdFC/tiNfq/JsCWWclca
# 50QBBo5JDAZvCJSrw71t7s5LLzs0rHMUnDjKyjAWwd7qC+tTZdwaGAppx6bpL9vd
# CzLZDQZGbOkZL1eaPikwkou98XY7DeaF/SzpAuQP6oVfgjoe82fLlgXBhl92KGB8
# M+E96okXJNrNFPY3iYWLRJk5D/r0NUkJR+NY/hxC8wnRqLyc9ANh99ykTSe5OlD9
# kx4DlStypnmQIm9oV+oRKr3cXKH+QvWh23sjdw42pkiHK0MP39RJDs5Jk0NG8FUY
# MrMTFiTmsxg9sJsLKWmFy/MzvJycVHnyKe6WTMeVz63ouCK/Ep41BIuajW/ivcCx
# LfSYAwzxpGQgEdta2eZrvna9+EzTiuEBoaV4mDGTdg1/P8Hjr/9a6fREl82Rqw3W
# CCt6ymOb31fxkUSr6Be26CY3JEHnxbb30PsVmNFCdayKjKbjf39zzTEWQRGSywC7
# +bgQWHu2kFvSH9YSWTxDjZdsdrL2bUvmobCTmaDCxTgjRiaBh+jSzhQ0M1MkQNzf
# W/gwP4z6a0FO2YatdXJFVGpV4rOzlHOsulFiyvgNAgMBAAGjggHXMIIB0zAfBgNV
# HSMEGDAWgBSBMpJBKyjNRsjEosYqORLsSKk/FDAdBgNVHQ4EFgQUsZ5evsRYcZpr
# WcPwOErnS2ZxP7cwDgYDVR0PAQH/BAQDAgeAMAwGA1UdEwEB/wQCMAAwEwYDVR0l
# BAwwCgYIKwYBBQUHAwMwEQYJYIZIAYb4QgEBBAQDAgQQMEkGA1UdIARCMEAwNQYM
# KwYBBAGyMQECAQYBMCUwIwYIKwYBBQUHAgEWF2h0dHBzOi8vc2VjdGlnby5jb20v
# Q1BTMAcGBWeBDAEDMEsGA1UdHwREMEIwQKA+oDyGOmh0dHA6Ly9jcmwuc2VjdGln
# by5jb20vU2VjdGlnb1B1YmxpY0NvZGVTaWduaW5nQ0FFVlIzNi5jcmwwewYIKwYB
# BQUHAQEEbzBtMEYGCCsGAQUFBzAChjpodHRwOi8vY3J0LnNlY3RpZ28uY29tL1Nl
# Y3RpZ29QdWJsaWNDb2RlU2lnbmluZ0NBRVZSMzYuY3J0MCMGCCsGAQUFBzABhhdo
# dHRwOi8vb2NzcC5zZWN0aWdvLmNvbTA2BgNVHREELzAtoBwGCCsGAQUFBwgDoBAw
# DgwMREUtSFJCIDE4ODcwgQ1jbkBldWxhbmRhLmRlMA0GCSqGSIb3DQEBCwUAA4IB
# gQBNj78jtsmPrpiGXPRt/DAyJJDd8CHHbH7SQpEC2abBc4ypmnseXbSIQlZdySaJ
# BOrDqj75gjB5FOSbir9FsIzPLgP2qjTyxsd3FtRcNcXDK7VIZgxmeJh2ipsXAqsB
# nivA9Vut2mu72wQ877VSkoUYIe7JMAUDKG0qwHU4LyMXoNYttto2y7yR6odDYwGC
# YxXNlPNx9wNVzJOMhnRalP1fwRC9RwjUoizGAEUg5BNh7dKNkSFRzimHh2iAvgeV
# akhbV2+IkekWo7GdEwGLLUSUG8tLwLxOXQ2qsJyOPv0bK/OXiqkgyvqzJ3TRZWiS
# KNX5jSiwwUBrA8vNyCh6d8ZCorwimYkDyGtstF0D9UoU9dX66QrfTsK+zxO7/0QF
# 1qIc5CTZe6Kcsuxe99p5UbPU665d5BvOwq0lJKg59k+6exo1Cc5awip+d4krfyWl
# D1sMkS0eiRSN1UNVs3Hg5gbaEEBx98sQMBF45vv0DFgY/SQVRp9yaFayTyfbb/qk
# jc8xghZoMIIWZAIBATBrMFcxCzAJBgNVBAYTAkdCMRgwFgYDVQQKEw9TZWN0aWdv
# IExpbWl0ZWQxLjAsBgNVBAMTJVNlY3RpZ28gUHVibGljIENvZGUgU2lnbmluZyBD
# QSBFViBSMzYCEGilgQZhq4aQSRu7qELTizkwDQYJYIZIAWUDBAIBBQCgfDAQBgor
# BgEEAYI3AgEMMQIwADAZBgkqhkiG9w0BCQMxDAYKKwYBBAGCNwIBBDAcBgorBgEE
# AYI3AgELMQ4wDAYKKwYBBAGCNwIBFTAvBgkqhkiG9w0BCQQxIgQguoczrma2Ixby
# cuhYuErjxpTLl3x3X/HBnSP4T7YUnogwDQYJKoZIhvcNAQEBBQAEggIAoGyGja0a
# Ik8hLkFWJBzkBwffW5UHUwychPCHxn26FLzPVMRw4pXH4Wpjmx97IGljOOtznv6y
# /5Q2fW6jtG7nZPHZCLhAsK3Y8hunNN+Xo9CONq6/DDx+9hlKCr9ptRB2ZE1KIn+U
# HyMqmupC2b82uRydQ1dQdZnqbty/gbKhAtWXoxv86viUpygg1QJ+AznWGzz2ckal
# CU8aw7ig/yDasIPQ+G3Xk13rCGNn0UsLa0UPUq2Lf0PyHREUizwO8l4zrPJAwvo6
# gb8sqAL+balYt6RUa1+HsNgxK+2WJIJx/DjGyeGvdq2sMumh+37lCnqK5WEg5MJF
# G/5kJBOJ82RUxV3nUArl2rE+6ECB8gwXVP3QTjOsTyZffLpZRgGIv7XJPOlHrlRQ
# LnGNSgB6H9izfLg+Bd/x6zY0UeNVhAc81nu7NRjZuPiNxiomNNKIMlGyU6c+S+nq
# mNdzn4M3v2gDJd+s01zBsFlbQAb14HsCwSHXgQgu0AGx+6K227AMSZBBhx0UW/ei
# tBi7NvWiDOBmLhJGhadwVF9I+TUMGO7ltf/3a08i6cXlQo9orRaxvbdPufCgu9GA
# mm231rYdJBYSKGD81aMJId8HqLQbmZTS0kIDyO/yGTW9ZrRnRY+8A6ShZ7BzuYHj
# 3kRqYiJvIvEg34S0I/imi1YJh+Kiv0FPb8mhghNQMIITTAYKKwYBBAGCNwMDATGC
# EzwwghM4BgkqhkiG9w0BBwKgghMpMIITJQIBAzEPMA0GCWCGSAFlAwQCAgUAMIHv
# BgsqhkiG9w0BCRABBKCB3wSB3DCB2QIBAQYKKwYBBAGyMQIBATAxMA0GCWCGSAFl
# AwQCAQUABCDTZbwA3MxEJ/UgF2vFHc3CHGAtFKpVfte9gFHhrVjbrwIUVf23seIF
# aBlB2VpDqxBee65tPmYYDzIwMjMwMzEwMjE0MTE1WqBupGwwajELMAkGA1UEBhMC
# R0IxEzARBgNVBAgTCk1hbmNoZXN0ZXIxGDAWBgNVBAoTD1NlY3RpZ28gTGltaXRl
# ZDEsMCoGA1UEAwwjU2VjdGlnbyBSU0EgVGltZSBTdGFtcGluZyBTaWduZXIgIzOg
# gg3qMIIG9jCCBN6gAwIBAgIRAJA5f5rSSjoT8r2RXwg4qUMwDQYJKoZIhvcNAQEM
# BQAwfTELMAkGA1UEBhMCR0IxGzAZBgNVBAgTEkdyZWF0ZXIgTWFuY2hlc3RlcjEQ
# MA4GA1UEBxMHU2FsZm9yZDEYMBYGA1UEChMPU2VjdGlnbyBMaW1pdGVkMSUwIwYD
# VQQDExxTZWN0aWdvIFJTQSBUaW1lIFN0YW1waW5nIENBMB4XDTIyMDUxMTAwMDAw
# MFoXDTMzMDgxMDIzNTk1OVowajELMAkGA1UEBhMCR0IxEzARBgNVBAgTCk1hbmNo
# ZXN0ZXIxGDAWBgNVBAoTD1NlY3RpZ28gTGltaXRlZDEsMCoGA1UEAwwjU2VjdGln
# byBSU0EgVGltZSBTdGFtcGluZyBTaWduZXIgIzMwggIiMA0GCSqGSIb3DQEBAQUA
# A4ICDwAwggIKAoICAQCQsnE/eeHUuYoXzMOXwpCUcu1aOm8BQ39zWiifJHygNUAG
# +pSvCqGDthPkSxUGXmqKIDRxe7slrT9bCqQfL2x9LmFR0IxZNz6mXfEeXYC22B9g
# 480Saogfxv4Yy5NDVnrHzgPWAGQoViKxSxnS8JbJRB85XZywlu1aSY1+cuRDa3/J
# oD9sSq3VAE+9CriDxb2YLAd2AXBF3sPwQmnq/ybMA0QfFijhanS2nEX6tjrOlNEf
# vYxlqv38wzzoDZw4ZtX8fR6bWYyRWkJXVVAWDUt0cu6gKjH8JgI0+WQbWf3jOtTo
# uEEpdAE/DeATdysRPPs9zdDn4ZdbVfcqA23VzWLazpwe/OpwfeZ9S2jOWilh06Bc
# JbOlJ2ijWP31LWvKX2THaygM2qx4Qd6S7w/F7KvfLW8aVFFsM7ONWWDn3+gXIqN5
# QWLP/Hvzktqu4DxPD1rMbt8fvCKvtzgQmjSnC//+HV6k8+4WOCs/rHaUQZ1kHfqA
# /QDh/vg61MNeu2lNcpnl8TItUfphrU3qJo5t/KlImD7yRg1psbdu9AXbQQXGGMBQ
# 5Pit/qxjYUeRvEa1RlNsxfThhieThDlsdeAdDHpZiy7L9GQsQkf0VFiFN+XHaafS
# JYuWv8at4L2xN/cf30J7qusc6es9Wt340pDVSZo6HYMaV38cAcLOHH3M+5YVxQID
# AQABo4IBgjCCAX4wHwYDVR0jBBgwFoAUGqH4YRkgD8NBd0UojtE1XwYSBFUwHQYD
# VR0OBBYEFCUuaDxrmiskFKkfot8mOs8UpvHgMA4GA1UdDwEB/wQEAwIGwDAMBgNV
# HRMBAf8EAjAAMBYGA1UdJQEB/wQMMAoGCCsGAQUFBwMIMEoGA1UdIARDMEEwNQYM
# KwYBBAGyMQECAQMIMCUwIwYIKwYBBQUHAgEWF2h0dHBzOi8vc2VjdGlnby5jb20v
# Q1BTMAgGBmeBDAEEAjBEBgNVHR8EPTA7MDmgN6A1hjNodHRwOi8vY3JsLnNlY3Rp
# Z28uY29tL1NlY3RpZ29SU0FUaW1lU3RhbXBpbmdDQS5jcmwwdAYIKwYBBQUHAQEE
# aDBmMD8GCCsGAQUFBzAChjNodHRwOi8vY3J0LnNlY3RpZ28uY29tL1NlY3RpZ29S
# U0FUaW1lU3RhbXBpbmdDQS5jcnQwIwYIKwYBBQUHMAGGF2h0dHA6Ly9vY3NwLnNl
# Y3RpZ28uY29tMA0GCSqGSIb3DQEBDAUAA4ICAQBz2u1ocsvCuUChMbu0A6MtFHsk
# 57RbFX2o6f2t0ZINfD02oGnZ85ow2qxp1nRXJD9+DzzZ9cN5JWwm6I1ok87xd4k5
# f6gEBdo0wxTqnwhUq//EfpZsK9OU67Rs4EVNLLL3OztatcH714l1bZhycvb3Byjz
# 07LQ6xm+FSx4781FoADk+AR2u1fFkL53VJB0ngtPTcSqE4+XrwE1K8ubEXjp8vmJ
# BDxO44ISYuu0RAx1QcIPNLiIncgi8RNq2xgvbnitxAW06IQIkwf5fYP+aJg05Hfl
# sc6MlGzbA20oBUd+my7wZPvbpAMxEHwa+zwZgNELcLlVX0e+OWTOt9ojVDLjRrIy
# 2NIphskVXYCVrwL7tNEunTh8NeAPHO0bR0icImpVgtnyughlA+XxKfNIigkBTKZ5
# 8qK2GpmU65co4b59G6F87VaApvQiM5DkhFP8KvrAp5eo6rWNes7k4EuhM6sLdqDV
# aRa3jma/X/ofxKh/p6FIFJENgvy9TZntyeZsNv53Q5m4aS18YS/to7BJ/lu+aSSR
# /5P8V2mSS9kFP22GctOi0MBk0jpCwRoD+9DtmiG4P6+mslFU1UzFyh8SjVfGOe1c
# /+yfJnatZGZn6Kow4NKtt32xakEnbgOKo3TgigmCbr/j9re8ngspGGiBoZw/bhZZ
# SxQJCZrmrr9gFd2G9TCCBuwwggTUoAMCAQICEDAPb6zdZph0fKlGNqd4LbkwDQYJ
# KoZIhvcNAQEMBQAwgYgxCzAJBgNVBAYTAlVTMRMwEQYDVQQIEwpOZXcgSmVyc2V5
# MRQwEgYDVQQHEwtKZXJzZXkgQ2l0eTEeMBwGA1UEChMVVGhlIFVTRVJUUlVTVCBO
# ZXR3b3JrMS4wLAYDVQQDEyVVU0VSVHJ1c3QgUlNBIENlcnRpZmljYXRpb24gQXV0
# aG9yaXR5MB4XDTE5MDUwMjAwMDAwMFoXDTM4MDExODIzNTk1OVowfTELMAkGA1UE
# BhMCR0IxGzAZBgNVBAgTEkdyZWF0ZXIgTWFuY2hlc3RlcjEQMA4GA1UEBxMHU2Fs
# Zm9yZDEYMBYGA1UEChMPU2VjdGlnbyBMaW1pdGVkMSUwIwYDVQQDExxTZWN0aWdv
# IFJTQSBUaW1lIFN0YW1waW5nIENBMIICIjANBgkqhkiG9w0BAQEFAAOCAg8AMIIC
# CgKCAgEAyBsBr9ksfoiZfQGYPyCQvZyAIVSTuc+gPlPvs1rAdtYaBKXOR4O168TM
# STTL80VlufmnZBYmCfvVMlJ5LsljwhObtoY/AQWSZm8hq9VxEHmH9EYqzcRaydvX
# XUlNclYP3MnjU5g6Kh78zlhJ07/zObu5pCNCrNAVw3+eolzXOPEWsnDTo8Tfs8Vy
# rC4Kd/wNlFK3/B+VcyQ9ASi8Dw1Ps5EBjm6dJ3VV0Rc7NCF7lwGUr3+Az9ERCleE
# yX9W4L1GnIK+lJ2/tCCwYH64TfUNP9vQ6oWMilZx0S2UTMiMPNMUopy9Jv/TUyDH
# YGmbWApU9AXn/TGs+ciFF8e4KRmkKS9G493bkV+fPzY+DjBnK0a3Na+WvtpMYMyo
# u58NFNQYxDCYdIIhz2JWtSFzEh79qsoIWId3pBXrGVX/0DlULSbuRRo6b83XhPDX
# 8CjFT2SDAtT74t7xvAIo9G3aJ4oG0paH3uhrDvBbfel2aZMgHEqXLHcZK5OVmJyX
# nuuOwXhWxkQl3wYSmgYtnwNe/YOiU2fKsfqNoWTJiJJZy6hGwMnypv99V9sSdvqK
# QSTUG/xypRSi1K1DHKRJi0E5FAMeKfobpSKupcNNgtCN2mu32/cYQFdz8HGj+0p9
# RTbB942C+rnJDVOAffq2OVgy728YUInXT50zvRq1naHelUF6p4MCAwEAAaOCAVow
# ggFWMB8GA1UdIwQYMBaAFFN5v1qqK0rPVIDh2JvAnfKyA2bLMB0GA1UdDgQWBBQa
# ofhhGSAPw0F3RSiO0TVfBhIEVTAOBgNVHQ8BAf8EBAMCAYYwEgYDVR0TAQH/BAgw
# BgEB/wIBADATBgNVHSUEDDAKBggrBgEFBQcDCDARBgNVHSAECjAIMAYGBFUdIAAw
# UAYDVR0fBEkwRzBFoEOgQYY/aHR0cDovL2NybC51c2VydHJ1c3QuY29tL1VTRVJU
# cnVzdFJTQUNlcnRpZmljYXRpb25BdXRob3JpdHkuY3JsMHYGCCsGAQUFBwEBBGow
# aDA/BggrBgEFBQcwAoYzaHR0cDovL2NydC51c2VydHJ1c3QuY29tL1VTRVJUcnVz
# dFJTQUFkZFRydXN0Q0EuY3J0MCUGCCsGAQUFBzABhhlodHRwOi8vb2NzcC51c2Vy
# dHJ1c3QuY29tMA0GCSqGSIb3DQEBDAUAA4ICAQBtVIGlM10W4bVTgZF13wN6Mgst
# JYQRsrDbKn0qBfW8Oyf0WqC5SVmQKWxhy7VQ2+J9+Z8A70DDrdPi5Fb5WEHP8ULl
# EH3/sHQfj8ZcCfkzXuqgHCZYXPO0EQ/V1cPivNVYeL9IduFEZ22PsEMQD43k+Thi
# vxMBxYWjTMXMslMwlaTW9JZWCLjNXH8Blr5yUmo7Qjd8Fng5k5OUm7Hcsm1BbWfN
# yW+QPX9FcsEbI9bCVYRm5LPFZgb289ZLXq2jK0KKIZL+qG9aJXBigXNjXqC72NzX
# StM9r4MGOBIdJIct5PwC1j53BLwENrXnd8ucLo0jGLmjwkcd8F3WoXNXBWiap8k3
# ZR2+6rzYQoNDBaWLpgn/0aGUpk6qPQn1BWy30mRa2Coiwkud8TleTN5IPZs0lpoJ
# X47997FSkc4/ifYcobWpdR9xv1tDXWU9UIFuq/DQ0/yysx+2mZYm9Dx5i1xkzM3u
# J5rloMAMcofBbk1a0x7q8ETmMm8c6xdOlMN4ZSA7D0GqH+mhQZ3+sbigZSo04N6o
# +TzmwTC7wKBjLPxcFgCo0MR/6hGdHgbGpm0yXbQ4CStJB6r97DDa8acvz7f9+tCj
# hNknnvsBZne5VhDhIG7GrrH5trrINV0zdo7xfCAMKneutaIChrop7rRaALGMq+P5
# CslUXdS5anSevUiumDGCBC0wggQpAgEBMIGSMH0xCzAJBgNVBAYTAkdCMRswGQYD
# VQQIExJHcmVhdGVyIE1hbmNoZXN0ZXIxEDAOBgNVBAcTB1NhbGZvcmQxGDAWBgNV
# BAoTD1NlY3RpZ28gTGltaXRlZDElMCMGA1UEAxMcU2VjdGlnbyBSU0EgVGltZSBT
# dGFtcGluZyBDQQIRAJA5f5rSSjoT8r2RXwg4qUMwDQYJYIZIAWUDBAICBQCgggFr
# MBoGCSqGSIb3DQEJAzENBgsqhkiG9w0BCRABBDAcBgkqhkiG9w0BCQUxDxcNMjMw
# MzEwMjE0MTE1WjA/BgkqhkiG9w0BCQQxMgQwA5BuBCXEGL5UYfZyPdT1gDwcH2Pr
# KdDcQvG2tJY49GyjqaZmSHqFttjGFYVj6DG9MIHtBgsqhkiG9w0BCRACDDGB3TCB
# 2jCB1zAWBBSrNAE6rECXMZ8IGvCzGOGD+A94gTCBvAQUAtZbleKDcMFXAJX6iPkj
# 3ZN/rY8wgaMwgY6kgYswgYgxCzAJBgNVBAYTAlVTMRMwEQYDVQQIEwpOZXcgSmVy
# c2V5MRQwEgYDVQQHEwtKZXJzZXkgQ2l0eTEeMBwGA1UEChMVVGhlIFVTRVJUUlVT
# VCBOZXR3b3JrMS4wLAYDVQQDEyVVU0VSVHJ1c3QgUlNBIENlcnRpZmljYXRpb24g
# QXV0aG9yaXR5AhAwD2+s3WaYdHypRjaneC25MA0GCSqGSIb3DQEBAQUABIICADvN
# CeYCfq/t4g8OTLq/pdKOVdXY0163yz6LQKy2LVeamFvklgEcLDigeS+RkXdbQDyR
# Z4yAhDjozoLd5ieMvT89+P0TIil+9kOu1vIfPS6XzYwA6XZesHWJc5mDPIbHNC2F
# JAq2yTk5NKjfeKGGncdbCbSiyojVw1/Jp84JJkEWIZmnA6q4MFDZfrVPGnceFvMt
# tJmNbJDEgnrd0GCoOF2AKvSn3RKDYSYIhDTHzLTUkjcxCTGk3sCVoa1EeUdFknhD
# 9U08a+RMILPi+MtBQWuD8yqcAV7OwlQ9OH3kJaxrOdtp8UcZrlycJ8S78ebigheN
# 9gHZypWtjibFbJqStI4VSiNdEEOj8SGsD1D2cZ/2q5LzKCCY+9JUPnMcDYnDagi5
# 0myHb/zqY0yD5AthzXqfayEW55X7UQRRnKLNwovQmQzznZ37TMtVA8y6judsFBzX
# 8MYYwZReIK+wM6YE5NgIHKzVxn1wz5uO5EjU64BD76k2SGDsGdAGOTNnUeuKQUVx
# saDd1uunwIkgdDOC/LmOJjfGv29Gbv1ezco8h+S7fQNg77lQ7i584xpQplhiRk6t
# YVdmeuQf9Dxbq90KR/ANnCEmKZffRI7+EC9TV3CJ5Eqm4E/FDzwe4z7kxKDPe6RB
# H2gXKWxav8CblAn7xFGZuPaJkb/zrUR3Qwv8A1I9
# SIG # End signature block