EulandaConnect.psm1

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

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

# Used in New-SymbolicLink
try {
    [SymbolicLink]
} catch {
    Add-Type @"
        using System;
        using System.Runtime.InteropServices;
 
        public class SymbolicLink
        {
            [DllImport("kernel32.dll", SetLastError = true)]
            public static extern bool CreateSymbolicLink(string lpSymlinkFileName, string lpTargetFileName, int dwFlags);
        }
"@

}



# -----------------------------------------------------------------------------
# Global EulandaConnect Variables ReadOnly
# -----------------------------------------------------------------------------
New-Variable -Name 'ecModuleName' -Scope 'Global' -Option ReadOnly -Force -Value ([string]'EulandaConnect') -Description 'Module name for EulandaConnect'
New-Variable -Name 'ecModulePath' -Scope 'Global' -Option ReadOnly -Force -Value ([string]'') -Description 'Path including filename to the EulandaConnect module'
New-Variable -Name 'ecModuleBase' -Scope 'Global' -Option ReadOnly -Force -Value ([string]'') -Description 'Path to the EulandaConnect module'
New-Variable -Name 'ecManifestPath' -Scope 'Global' -Option ReadOnly -Force -Value ([string]'') -Description 'Path to the manifest of the EulandaConnect module'
New-Variable -Name 'ecManifest' -Scope 'Global' -Option ReadOnly -Force -Value (@{}) -Description 'Hashtable of the EulandaConnect module manifest'
New-Variable -Name 'ecModuleVersion' -Scope 'Global' -Option ReadOnly -Force -Value ([version]'0.0') -Description 'Version number for the EulandaConnect module'
New-Variable -Name 'ecModuleCopyright' -Scope 'Global'  -Option ReadOnly -Force -Value ([string]'') -Description 'Copyright for the EulandaConnect module'
New-Variable -Name 'ecModuleLicenseUri' -Scope 'Global'  -Option ReadOnly -Force -Value ([string]'') -Description 'License uri for the EulandaConnect module'

New-Variable -Name 'ecStartTime' -Scope 'Global'  -Option ReadOnly -Force -Value ([datetime]'1900-1-1') -Description 'Start time of EulandaConnect module'
New-Variable -Name 'ecEndTime' -Scope 'Global'  -Option ReadOnly -Force -Value ([datetime]'1900-1-1') -Description 'End time of EulandaConnect module'
New-Variable -Name 'ecCulture' -Scope 'Global'  -Option ReadOnly -Force  -Value ([string]'') -Description 'User language like en-US of EulandaConnect module'
New-Variable -Name 'ecProjectName' -Scope 'Global'  -Option ReadOnly -Force -Value ([string]'') -Description 'Project name using EulandaConnect module'
New-Variable -Name 'ecProjectVersion' -Scope 'Global' -Option ReadOnly -Force -Value ([version]'0.0') -Description 'Project version using for the EulandaConnect module'



# -----------------------------------------------------------------------------
# Global EulandaConnect Variables
# -----------------------------------------------------------------------------
New-Variable -Name 'ecResx' -Scope 'Global' -Force -Value ([System.Collections.Hashtable]@{}) -Description  'Language resources of EulandaConnect module'
New-Variable -Name 'ecProcessId' -Scope 'Global' -Force -Value [System.Guid]'00000000-0000-0000-0000-000000000000' -Description  'Process id used in logging of EulandaConnect module'



# -----------------------------------------------------------------------------
# Global Constants: ADO Int64
# -----------------------------------------------------------------------------
New-Variable -Name 'adCmdText' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]1) -Description 'ADO constant from EulandaConnect'
New-Variable -Name 'adLockOptimistic' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]3) -Description 'ADO constant from EulandaConnect'
New-Variable -Name 'adOpenKeyset' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]1) -Description 'ADO constant from EulandaConnect'
New-Variable -Name 'adStateClosed' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]0) -Description 'ADO constant from EulandaConnect'
New-Variable -Name 'adStateConnecting' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]2) -Description 'ADO constant from EulandaConnect'
New-Variable -Name 'adStateExecuting' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]4) -Description 'ADO constant from EulandaConnect'
New-Variable -Name 'adStateFetching' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]8) -Description 'ADO constant from EulandaConnect'
New-Variable -Name 'adStateOpen' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]1) -Description 'ADO constant from EulandaConnect'
New-Variable -Name 'adTimeout' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]60*20) -Description 'ADO constant from EulandaConnect'
New-Variable -Name 'adUseClient' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]3) -Description 'ADO constant from EulandaConnect'

New-Variable -Name 'adEmpty' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]0) -Description 'ADO constant for adEmpty from EulandaConnect'
New-Variable -Name 'adTinyInt' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]16) -Description 'ADO constant for adTinyInt from EulandaConnect'
New-Variable -Name 'adSmallInt' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]2) -Description 'ADO constant for adSmallInt from EulandaConnect'
New-Variable -Name 'adInteger' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]3) -Description 'ADO constant for adInteger from EulandaConnect'
New-Variable -Name 'adBigInt' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]20) -Description 'ADO constant for adBigInt from EulandaConnect'
New-Variable -Name 'adUnsignedTinyInt' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]17) -Description 'ADO constant for adUnsignedTinyInt from EulandaConnect'
New-Variable -Name 'adUnsignedSmallInt' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]18) -Description 'ADO constant for adUnsignedSmallInt from EulandaConnect'
New-Variable -Name 'adUnsignedInt' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]19) -Description 'ADO constant for adUnsignedInt from EulandaConnect'
New-Variable -Name 'adUnsignedBigInt' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]21) -Description 'ADO constant for adUnsignedBigInt from EulandaConnect'
New-Variable -Name 'adSingle' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]4) -Description 'ADO constant for adSingle from EulandaConnect'
New-Variable -Name 'adDouble' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]5) -Description 'ADO constant for adDouble from EulandaConnect'
New-Variable -Name 'adCurrency' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]6) -Description 'ADO constant for adCurrency from EulandaConnect'
New-Variable -Name 'adDecimal' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]14) -Description 'ADO constant for adDecimal from EulandaConnect'
New-Variable -Name 'adNumeric' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]131) -Description 'ADO constant for adNumeric from EulandaConnect'
New-Variable -Name 'adBoolean' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]11) -Description 'ADO constant for adBoolean from EulandaConnect'
New-Variable -Name 'adError' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]10) -Description 'ADO constant for adError from EulandaConnect'
New-Variable -Name 'adUserDefined' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]132) -Description 'ADO constant for adUserDefined from EulandaConnect'
New-Variable -Name 'adVariant' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]12) -Description 'ADO constant for adVariant from EulandaConnect'
New-Variable -Name 'adIDispatch' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]9) -Description 'ADO constant for adIDispatch from EulandaConnect'
New-Variable -Name 'adIUnknown' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]13) -Description 'ADO constant for adIUnknown from EulandaConnect'
New-Variable -Name 'adGUID' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]72) -Description 'ADO constant for adGUID from EulandaConnect'
New-Variable -Name 'adDate' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]7) -Description 'ADO constant for adDate from EulandaConnect'
New-Variable -Name 'adDBDate' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]133) -Description 'ADO constant for adDBDate from EulandaConnect'
New-Variable -Name 'adDBTime' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]134) -Description 'ADO constant for adDBTime from EulandaConnect'
New-Variable -Name 'adDBTimeStamp' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]135) -Description 'ADO constant for adDBTimeStamp from EulandaConnect'
New-Variable -Name 'adBSTR' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]8) -Description 'ADO constant for adBSTR from EulandaConnect'
New-Variable -Name 'adChar' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]129) -Description 'ADO constant for adChar from EulandaConnect'
New-Variable -Name 'adVarChar' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]200) -Description 'ADO constant for adVarChar from EulandaConnect'
New-Variable -Name 'adLongVarChar' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]201) -Description 'ADO constant for adLongVarChar from EulandaConnect'
New-Variable -Name 'adWChar' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]130) -Description 'ADO constant for adWChar from EulandaConnect'
New-Variable -Name 'adVarWChar' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]202) -Description 'ADO constant for adVarWChar from EulandaConnect'
New-Variable -Name 'adLongVarWChar' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]203) -Description 'ADO constant for adLongVarWChar from EulandaConnect'
New-Variable -Name 'adBinary' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]128) -Description 'ADO constant for adBinary from EulandaConnect'
New-Variable -Name 'adVarBinary' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]204) -Description 'ADO constant for adVarBinary from EulandaConnect'
New-Variable -Name 'adLongVarBinary' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]205) -Description 'ADO constant for adLongVarBinary from EulandaConnect'
New-Variable -Name 'adChapter' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]136) -Description 'ADO constant for adChapter from EulandaConnect'
New-Variable -Name 'adFileTime' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]64) -Description 'ADO constant for adFileTime from EulandaConnect'
New-Variable -Name 'adPropVariant' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]138) -Description 'ADO constant for adPropVariant from EulandaConnect'
New-Variable -Name 'adVarNumeric' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]139) -Description 'ADO constant for adVarNumeric from EulandaConnect'
New-Variable -Name 'adArray' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int64]8192) -Description 'ADO constant for adArray from EulandaConnect'



# -----------------------------------------------------------------------------
# Global Constants: MsgBox
# -----------------------------------------------------------------------------
New-Variable -Name 'mbNone' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]0) -Description 'Message Box Buttons from EulandaConnect'

New-Variable -Name 'mbOk' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.MessageBoxButtons]::OK)) -Description 'Message Box Buttons from EulandaConnect'
New-Variable -Name 'mbOkCancel' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.MessageBoxButtons]::OkCancel)) -Description 'Message Box Buttons from EulandaConnect'
New-Variable -Name 'mbAbortRetryIgnore' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.MessageBoxButtons]::AbortRetryIgnore)) -Description 'Message Box Buttons from EulandaConnect'
New-Variable -Name 'mbYesNoCancel' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.MessageBoxButtons]::YesNoCancel)) -Description 'Message Box Buttons from EulandaConnect'
New-Variable -Name 'mbYesNo' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.MessageBoxButtons]::YesNo)) -Description 'Message Box Buttons from EulandaConnect'
New-Variable -Name 'mbRetryCancel' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.MessageBoxButtons]::RetryCancel)) -Description 'Message Box Buttons from EulandaConnect'

New-Variable -Name 'mbStop' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.MessageBoxIcon]::Stop)) -Description 'Message Box Icons from EulandaConnect'
New-Variable -Name 'mbHand' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.MessageBoxIcon]::Stop)) -Description 'Message Box Icons from EulandaConnect'
New-Variable -Name 'mbQuestion' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.MessageBoxIcon]::Question)) -Description 'Message Box Icons from EulandaConnect'
New-Variable -Name 'mbWarning' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.MessageBoxIcon]::Warning)) -Description 'Message Box Icons from EulandaConnect'
New-Variable -Name 'mbExclamation' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.MessageBoxIcon]::Warning)) -Description 'Message Box Icons from EulandaConnect'
New-Variable -Name 'mbInfo' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.MessageBoxIcon]::Information)) -Description 'Message Box Icons from EulandaConnect'
New-Variable -Name 'mbAsterisk' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.MessageBoxIcon]::Information)) -Description 'Message Box Icons from EulandaConnect'
New-Variable -Name 'mbInformation' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.MessageBoxIcon]::Information)) -Description 'Message Box Icons from EulandaConnect'

New-Variable -Name 'mbButton1' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.MessageBoxDefaultButton]::Button1)) -Description 'Message Box Default Button from EulandaConnect'
New-Variable -Name 'mbButton2' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.MessageBoxDefaultButton]::Button2)) -Description 'Message Box Default Button from EulandaConnect'
New-Variable -Name 'mbButton3' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.MessageBoxDefaultButton]::Button3)) -Description 'Message Box Default Button from EulandaConnect'

New-Variable -Name 'mbrOk' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.DialogResult]::Ok)) -Description 'Dialog Results from EulandaConnect'
New-Variable -Name 'mbrCancel' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.DialogResult]::Cancel)) -Description 'Dialog Results from EulandaConnect'
New-Variable -Name 'mbrYes' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.DialogResult]::Yes)) -Description 'Dialog Results from EulandaConnect'
New-Variable -Name 'mbrNo' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.DialogResult]::No)) -Description 'Dialog Results from EulandaConnect'
New-Variable -Name 'mbrAbort' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.DialogResult]::Abort)) -Description 'Dialog Results from EulandaConnect'
New-Variable -Name 'mbrRetry' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.DialogResult]::Retry)) -Description 'Dialog Results from EulandaConnect'
New-Variable -Name 'mbrIgnore' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.DialogResult]::Ignore)) -Description 'Dialog Results from EulandaConnect'

if ($PSVersionTable.PSEdition -eq 'Core') {
    New-Variable -Name 'mbButton4' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.MessageBoxDefaultButton]::Button4)) -Description 'Message Box Default Button from EulandaConnect'
    New-Variable -Name 'mbrTryAgain' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.DialogResult]::TryAgain)) -Description 'Dialog Results from EulandaConnect'
    New-Variable -Name 'mbrContinue' -Scope 'Global' -Option ReadOnly -Force -Value ([System.int32]([System.Windows.Forms.DialogResult]::Continue)) -Description 'Dialog Results from EulandaConnect'
} else {
    New-Variable -Name 'mbButton4' -Scope 'Global' -Option ReadOnly -Force -Value  ([System.int32]0) -Description '(needs PowerShell 7.x) Message Box Default Button from EulandaConnect'
    New-Variable -Name 'mbrTryAgain' -Scope 'Global' -Option ReadOnly -Force -Value  ([System.int32]0) -Description '(needs PowerShell 7.x) Dialog Results from EulandaConnect'
    New-Variable -Name 'mbrContinue' -Scope 'Global' -Option ReadOnly -Force -Value  ([System.int32]0) -Description '(needs PowerShell 7.x) Dialog Results from EulandaConnect'
}


# Handle PowerShell module folder, but also the project source folder for debugging
if (Test-Path "$($PSScriptRoot)\$ecModuleName.psm1")  {
    New-Variable -Name 'ecModulePath' -Scope 'Global'  -Option ReadOnly -Force -Value "$($PSScriptRoot)\$ecModuleName.psm1" -Description 'Path including filename to the EulandaConnect module'
} elseif (Test-Path "$($PSScriptRoot)\..\..\$ecModuleName.psm1") {
    New-Variable -Name 'ecModulePath' -Scope 'Global'  -Option ReadOnly -Force -Value (Resolve-Path("$($PSScriptRoot)\..\..\$ecModuleName.psm1")).Path -Description 'Path including filename to the EulandaConnect module'
} else {
    Throw "Module '$ecModuleName.psm1' not found in '$PSScriptRoot' and also not in '$PSScriptRoot\..\..\'"
}

New-Variable -Name 'ecModuleBase' -Scope 'Global'  -Option ReadOnly -Force -Value (Split-Path $ecModulePath -Parent) -Description 'Path to the EulandaConnect module'
New-Variable -Name 'ecManifestPath' -Scope 'Global'  -Option ReadOnly -Force -Value ([string]($ecModulePath.Replace(".psm1", ".psd1"))) -Description 'Path to the manifest of the EulandaConnect module'
New-Variable -Name 'ecManifest' -Scope 'Global'  -Option ReadOnly -Force -Value ([hashtable](Import-PowerShellDataFile -path $ecManifestPath)) -Description 'Hashtable of the EulandaConnect module manifest'
New-Variable -Name 'ecModuleVersion' -Scope 'Global'  -Option ReadOnly -Force -Value ([version]($ecManifest.ModuleVersion)) -Description 'Version number for the EulandaConnect module'
New-Variable -Name 'ecModuleCopyright' -Scope 'Global'  -Option ReadOnly -Force -Value ([string]($ecManifest.Copyright)) -Description 'Copyright for the EulandaConnect module'
New-Variable -Name 'ecModuleLicenseUri' -Scope 'Global'  -Option ReadOnly -Force -Value ([string]'https://www.github.com/Eulanda/EulandaConnect') -Description 'License uri for the EulandaConnect module'

New-Variable -Name 'ecStartTime' -Scope 'Global'  -Option ReadOnly -Force -Value ([datetime](Get-Date)) -Description 'Start time of EulandaConnect module'

New-Variable -Name 'ecCulture' -Scope 'Global'  -Option ReadOnly -Force -Value ([string]([System.Threading.Thread]::CurrentThread.CurrentCulture.Name)) -Description 'User language like en-US of EulandaConnect module'




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

function Approve-Signature {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $false)]
        [string]$path = $(Throw ((Get-ResStr 'PARAM_MANDATORY_MISSED') -f 'path', $myInvocation.Mycommand))
    )

    begin {
        Write-Verbose -Message ((Get-ResStr 'STARTING_FUNCTION') -f $myInvocation.Mycommand)
        New-Variable -Name 'filename' -Scope 'Private' -Value ''
        New-Variable -Name 'extension' -Scope 'Private' -Value ''
        New-Variable -Name 'folder' -Scope 'Private' -Value ''
        New-Variable -Name 'signFile' -Scope 'Private' -Value ''
        New-Variable -Name 'arguments' -Scope 'Private' -Value ('')
        New-Variable -Name 'exitCode' -Scope 'Private' -Value (0)
        New-Variable -Name 'outputContent' -Scope 'Private' -Value ('')
        New-Variable -Name 'process' -Scope 'Private' -Value $null
        $initialVariables = Get-CurrentVariables -Debug:$DebugPreference
    }

    process {
        $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'
                    }
                }
            }
        }

        # Test to see if undefined vars are in the exception
        # [string]$Tester = '42'

        [string]$signFile= Resolve-Path "$folder\$($filename)$($extension)"
        [string]$arguments = "sign /tr http://timestamp.sectigo.com?td=sha256 /td sha256 /fd sha256 /a ""$signFile"""
        Write-Host ((Get-ResStr 'SIGNING_FILE') -f $signFile) -ForegroundColor Blue

        $process = New-Object System.Diagnostics.Process
        $process.StartInfo.FileName = (Get-SignToolPath)
        $process.StartInfo.Arguments = $arguments
        $process.StartInfo.RedirectStandardOutput = $true
        $process.StartInfo.RedirectStandardError = $true
        $process.StartInfo.UseShellExecute = $false
        $process.StartInfo.CreateNoWindow = $true

        $process.Start() | Out-Null
        $outputContent = $process.StandardOutput.ReadToEnd() + $process.StandardError.ReadToEnd()
        $process.WaitForExit()
        $exitCode = $process.ExitCode
        $process.Dispose()

        if ($exitCode -ne 0 -and $outputContent -notmatch 'Successfully signed') {
            Write-Host -ForegroundColor Red ((Get-ResStr 'SIGNING_FAILED') -f $exitCode)
            Write-Host -ForegroundColor Red $outputContent
            throw ((Get-ResStr 'SIGNING_EXCEPTION') -f $exitCode)
        }
    }

    end {
        Get-CurrentVariables -InitialVariables $initialVariables -Debug:$DebugPreference
    }
    # Test: Approve-Signature .\EulandaConnect.psm1
}

function Backup-MssqlDatabase {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $false)]
        [string]$server
        ,
        [Parameter(Mandatory = $false)]
        [ValidateSet("ftp", "ftps", "sftp")]
        [string]$protocol = 'sftp'
        ,
        [Parameter(Mandatory = $false)]
        [int]$port = $(if ($protocol -eq "ftp") {21} elseif ($protocol -eq "ftps") {21} elseif ($protocol -eq "sftp") {22})
        ,
        [Parameter(Mandatory = $false)]
        [switch]$activeMode
        ,
        [Parameter(Mandatory = $false)]
        [int]$resumeAge = 60*60*3
        ,
        [Parameter(Mandatory = $false)]
        [int]$resumeRetries = 7
        ,
        [Parameter(Mandatory = $false)]
        [string]$certificate
        ,
        [Parameter(Mandatory = $false)]
        [string]$user
        ,
        [Parameter(Mandatory = $false)]
        $password
        ,
        [Parameter(Mandatory = $false)]
        [string]$remoteFolder
        ,
        [Parameter(Mandatory = $false)]
        [string]$storageFolder
        ,
        [Parameter(Mandatory = $false)]
        [switch]$removeBak
        ,
        [Parameter(Mandatory = $false)]
        [int]$history = 3
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateConn -conn $_  })]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidatePathUDL -path $_  })]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateConnStr -connStr $_ })]
        [string]$connStr
    )

    begin {
        Write-Verbose -Message ((Get-ResStr 'STARTING_FUNCTION') -f $myInvocation.Mycommand)
        Test-ValidateSingle -validParams (Get-SingleConnection) @PSBoundParameters

        $initialVariables = Get-CurrentVariables -Debug:$DebugPreference
    }

    process {
        # Get connection and open it
        $myConn = Get-Conn -conn $conn -ConnStr $connStr -udl $udl

        # Create time stamp to enhance the filename to be stored
        [string]$timeStamp = Get-Date -Format "yyyy-MM-dd-HH-mm-ss-ffff"


        # Extract parameters from connection
        $connItems = Get-ConnItems -conn $myConn
        [string]$datasource = $connItems['Data Source']
        [string]$database = $connItems['Initial Catalog']
        [string[]]$splitDataSource = $datasource -split '\\'
        if ($splitDataSource.Count -gt 1) {
            [string]$instance = $splitDataSource[1]
        } else {
            [string]$instance = "MSSQLSERVER"
        }

        # Get standard backup path
        [hashtable]$instances = @{}
        Get-MssqlInstances | ForEach-Object { $instances[$_.Instance] = $_ }
        [string]$backupPath = $instances[$instance].BackupPath


        # Backup database from connection
        $backupFile = "$backupPath\$database.bak"
        $sql = "BACKUP DATABASE [$database] TO DISK = N'$backupFile' WITH FORMAT, INIT, NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD, NO_COMPRESSION, STATS = 10"
        Write-Verbose ((Get-ResStr 'VERBOSE_BACKUP_DATABASE_TO_FILE') -f $database, $backupFile)
        $myConn.Execute($sql) | Out-Null


        # Zip the database backup file
        $zipFile = "$backupPath\$database.zip"
        if (($storageFolder) -or ($server) -or ($removeBak)) {
            Compress-Archive -Path $backupFile -DestinationPath $zipFile -CompressionLevel Optimal -Update
            $result = $zipFile
        } else {
            $result = $backupFile
        }


        # File system or NAS is used to store zip file
        if ($storageFolder) {
            Write-Verbose ((Get-ResStr 'VERBOSE_COPY_ZIP_TO_FILE') -f  "$storageFolder\$database-$timeStamp.zip")
            Copy-Item -Path $zipFile -Destination "$storageFolder\$database-$timeStamp.zip" -Force

            $files = Get-ChildItem -Path $storageFolder -File | Select-Object -ExpandProperty Name
            $files = Select-OutdatedFilenames -filenames $files -basename $database -extension '.zip' -history $history
            foreach ($file in $files) {
                Remove-Item -Path "$storageFolder\$file" -Force
                Write-Verbose ((Get-ResStr 'VERBOSE_DELETED_FILE_FROM_STORAGE') -f "$storageFolder\$file")
            }
        }


        # A remote server (ftp/sftp) is used to store the zip file
        if ($server) {
            if ($password.GetType().Name -eq 'String') {
                [securestring]$password = ConvertTo-SecureString -String $password -AsPlainText -Force
            }

            $remoteFinalFolder = "$($remoteFolder.TrimEnd('/'))/$instance"
            $remoteFinalFolder = "$($remoteFinalFolder.TrimEnd('/'))"

            $remoteParams = @{
                server = $server
                protocol = $protocol
                port = $port
                activeMode = $activeMode
                certificate = $certificate
                user = $user
                password = $password
            }

            $folderParams = @{
                localFolder = $backupPath
                localFile = "$database.zip"
                remoteFolder = $remoteFinalFolder
                remoteFile = "$database-$timeStamp.zip"
            }

            Send-RemoteFile @remoteParams @folderParams -resumeAge $resumeAge -resumeRetries $resumeRetries
            $files = Get-RemoteDir @remoteParams -mask '*.zip' -remoteFolder $remoteFinalFolder
            $files = Select-OutdatedFilenames -filenames $files -basename $database -extension '.zip' -history $history
            foreach ($file in $files) {
                Remove-RemoteFile @remoteParams -remoteFolder $remoteFinalFolder -remoteFile $file
                Write-Verbose ((Get-ResStr 'VERBOSE_DELETED_FILE_FROM_REMOTE') -f "$remoteFinalFolder/$file")
            }
        }


        # Clean-Up mssql backup folder
        if ($removeBak) {
            Remove-Item $backupFile -force
            Write-Verbose ((Get-ResStr 'VERBOSE_DELETED_BACKUP_FROM_SQL') -f "$backupFile")
        }

    }

    end {
        Get-CurrentVariables -InitialVariables $initialVariables -Debug:$DebugPreference
        Return $result
    }

    <#
 
        $pesterFolder = Resolve-Path -path ".\source\tests"
        $iniPath = Join-Path -path $pesterFolder "pester.ini"
        $ini = Read-IniFile -path $iniPath
        $path = $ini['SFTP']['SecurePasswordPath']
        $path = $path -replace '\$home', $HOME
        $secure = Import-Clixml -path $path
        $server = $ini['SFTP']['Server']
        $user = $ini['SFTP']['User']
        Backup-MssqlDatabase -udl "$pesterFolder\Eulanda_1 Pester.udl" -storageFolder 'C:\store' -server $server -user $user -password $secure -remoteFolder '/inbox'
 
    #>


}

function Close-Delivery {
    [CmdletBinding()]
    param (
        [parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateId -id $_ })]
        [int]$deliveryId
        ,
        [parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateNo -no $_ })]
        [int]$deliveryNo
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateConn -conn $_  })]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidatePathUDL -path $_  })]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateConnStr -connStr $_ })]
        [string]$connStr
    )

    begin {
        Write-Verbose -Message ((Get-ResStr 'STARTING_FUNCTION') -f $myInvocation.Mycommand)
        Test-ValidateSingle -validParams (Get-SingleDeliveryKeys) @PSBoundParameters
        Test-ValidateSingle -validParams (Get-SingleConnection) @PSBoundParameters
        New-Variable -Name "sql" -Scope "Private" -Value ""
        New-Variable -Name "myConn" -Scope "Private" -Value $null
        $initialVariables = Get-CurrentVariables -Debug:$DebugPreference
    }

    process {
        $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
"@

        } else {
            $sql = @"
            SET NOCOUNT ON;
            DECLARE @DeliveryNo int
            SET @DeliveryNo = $deliveryNo
            EXEC dbo.cn_lfBuchen @lf_Nummer=@DeliveryNo
"@

        }

        $myConn.Execute($sql) | out-null

    }

    end {
        Get-CurrentVariables -InitialVariables $initialVariables -Debug:$DebugPreference
    }
    # Test: Close-Delivery -deliveryNo 68 -udl 'C:\temp\EULANDA_1 Truccamo.udl'
}

function Close-SalesOrder {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateNo -no $_ })]
        [int]$salesOrderNo
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateId -id $_ })]
        [int]$salesOrderId
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateNo -no $_ })]
        [int]$customerOrderNo
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateConn -conn $_  })]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidatePathUDL -path $_  })]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateConnStr -connStr $_ })]
        [string]$connStr
    )

    begin {
        Write-Verbose -Message ((Get-ResStr 'STARTING_FUNCTION') -f $myInvocation.Mycommand)
        Test-ValidateSingle -validParams (Get-SingleConnection) @PSBoundParameters
        Test-ValidateSingle -validParams (Get-SingleSalesOrderKeys) @PSBoundParameters
        New-Variable -Name 'myConn' -Scope 'Private' -Value ($null)
        New-Variable -Name 'paramsSalesOrder' -Scope 'Private' -Value ([System.Collections.Hashtable]@{})
        New-Variable -Name 'relevantError' -Scope 'Private' -Value ([string]'')
        New-Variable -Name 'sql' -Scope 'Private' -Value ([string]'')
        $initialVariables = Get-CurrentVariables -Debug:$DebugPreference
    }

    process {
        $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr
        $paramsSalesOrder = Get-UsedParameters -validParams (Get-SingleSalesOrderKeys) -boundParams $PSBoundParameters
        $salesOrderId = Get-SalesOrderId @paramsSalesOrder -conn $myConn

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

        } else {
            $sql = @"
            SET NOCOUNT ON;
            DECLARE @SalesOrderNo int
            SET @SalesOrderId = $salesOrderNo
            EXEC dbo.cn_afBuchen @af_Nummer=@SalesOrderNo
"@

        }

        try {
            $myConn.Execute($sql) | out-null
        }

        catch {
            $relevantError = Get-ErrorFromConn -conn $myConn
            Throw  "Error: $_! $relevantError"
        }

    }

    end {
        Get-CurrentVariables -InitialVariables $initialVariables -Debug:$DebugPreference
    }
    # Test: Close-SalesOrder -salesOrderNo 131 -udl 'C:\temp\EULANDA_1 Truccamo.udl'
}

function Confirm-System {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $false)]
        [switch]$all
        ,
        [Parameter(Mandatory = $false)]
        [switch]$administrator
        ,
        [Parameter(Mandatory = $false)]
        [switch]$controlledFolderAccess
        ,
        [Parameter(Mandatory = $false)]
        [switch]$memory
        ,
        [Parameter(Mandatory = $false)]
        [switch]$drives
        ,
        [Parameter(Mandatory = $false)]
        [switch]$network
    )

    begin {
        Write-Verbose -Message ((Get-ResStr 'STARTING_FUNCTION') -f $myInvocation.Mycommand)
        Test-SpecificParameters -BoundParameters $PSBoundParameters -CommandName $MyInvocation.MyCommand.Name
        New-Variable -Name 'item' -Scope 'Private' -Value ([PSCustomObject]@{})
        New-Variable -Name 'i' -Scope 'Private' -Value ([int]0)
        New-Variable -Name 'diskModel' -Scope 'Private' -Value ''
        New-Variable -Name 'lastSpaceIndex' -Scope 'Private' -Value ([int]0)
        New-Variable -Name 'lastWord' -Scope 'Private' -Value ''
        New-Variable -Name 'status' -Scope 'Private' -Value  ([object[]]$null)
        New-Variable -Name 'broadcastIp' -Scope 'Private' -Value  ([version]'0.0')
        New-Variable -Name 'cidr' -Scope 'Private' -Value ([int]0)
        New-Variable -Name 'firstIp' -Scope 'Private' -Value  ([version]'0.0')
        New-Variable -Name 'gatewayIp' -Scope 'Private' -Value  ([version]'0.0')
        New-Variable -Name 'gatewayName' -Scope 'Private' -Value ''
        New-Variable -Name 'lastIp' -Scope 'Private' -Value  ([version]'0.0')
        New-Variable -Name 'localIp' -Scope 'Private' -Value  ([version]'0.0')
        New-Variable -Name 'localSubnet' -Scope 'Private' -Value  ([version]'0.0')
        New-Variable -Name 'maxHosts' -Scope 'Private' -Value ([int]0)
        New-Variable -Name 'networkId' -Scope 'Private' -Value  ([version]'0.0')
        New-Variable -Name 'publicIp' -Scope 'Private' -Value  ([version]'0.0')
        New-Variable -Name 'result' -Scope 'Private' -Value  ([System.Collections.ArrayList]@())
        $initialVariables = Get-CurrentVariables -Debug:$DebugPreference
    }

    process {
        $result = New-Object System.Collections.ArrayList

        if ($all -or $administrator) {
            $item = [PSCustomObject]@{
                Description = (Get-ResStr 'CONFIRM_ADMINISTRATIVE_RIGHTS')
                Value = [string](Test-Administrator)
            }
            $result.Add($item) | Out-Null
        }

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

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

        if ($all -or $drives) {
            for ($i=0; $i -lt (Get-PsDrive -PsProvider FileSystem).count-1; $i++) {
                $item = [PSCustomObject]@{
                    Description = ((Get-ResStr 'CONFIRM_MEMORY') -f (Get-PsDrive -PsProvider FileSystem)[$i].Name)
                    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 = ((Get-ResStr 'CONFIRM_SMART') -f $diskModel)
                        Value = [string]$Lastword.trim()
                    }
                    $result.Add($item) | Out-Null
                }
            }
        }

        if ($all -or $network) {
            $gatewayIp = Get-GatewayIp
            $item = [PSCustomObject]@{
                Description = (Get-ResStr 'CONFIRM_GATEWAY_IP')
                Value = [string]$gatewayIp
            }
            $result.Add($item) | Out-Null

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

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

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

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

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

            $maxHosts= Get-MaxHosts -cidr $cidr
            $item = [PSCustomObject]@{
                Description = (Get-ResStr 'CONFIRM_MAX_HOSTS')
                Value = [string]$maxHosts
            }
            $result.Add($item) | Out-Null

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

            $firstIp= Get-FirstIp -networkId $networkId -cidr $cidr
            $item = [PSCustomObject]@{
                Description = (Get-ResStr 'CONFIRM_FIRST_IP')
                Value = [string]$firstIp
            }
            $result.Add($item) | Out-Null


            $lastIp= Get-LastIp -networkId $networkId -cidr $cidr
            $item = [PSCustomObject]@{
                Description =  (Get-ResStr 'CONFIRM_LAST_IP')
                Value = [string]$lastIp
            }
            $result.Add($item) | Out-Null


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

    end {
        Get-CurrentVariables -InitialVariables $initialVariables -Debug:$DebugPreference
        Return $result
    }
    # Test: Confirm-System -administrator -controlledFolderAccess -memory -drives -network
}

function Convert-Accent {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $false)]
        [string]$value = $(Throw ((Get-ResStr 'PARAM_MANDATORY_MISSED') -f 'value', $myInvocation.Mycommand))
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateStringCase $_ })]
        [string]$strCase = 'none'
    )

    begin {
        Write-Verbose -Message ((Get-ResStr 'STARTING_FUNCTION') -f $myInvocation.Mycommand)
        New-Variable -Name 'hash' -Scope 'Private' -Value ([System.Collections.Hashtable]@{})
        New-Variable -Name 'item' -Scope 'Private' -Value ($null)
        New-Variable -Name 'result' -Scope 'Private' -Value ([string]'')
        $initialVariables = Get-CurrentVariables -Debug:$DebugPreference
    }

    process {
        [string]$result = $value
        # case sensitive
        $hash = New-Object hashtable
        $hash['µ']='mikro'
        $hash['€']='eur'
        $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'

        foreach ($item in $hash.GetEnumerator()) {
            $result = $result -creplace  "$($item.key)", "$($item.value)"
        }
        $result = Convert-StringCase -value $result -strCase $strCase
    }

    end {
        Get-CurrentVariables -InitialVariables $initialVariables -Debug:$DebugPreference
        Return $result
    }
    # Test: Convert-Accent -value 'Der Caffè ist übergut in Österreich!' -strCase 'Upper'
}

function Convert-DatanormToXml {
    [CmdletBinding()]
    param(
        $datanorm
        ,
        [switch]$show
    )

    begin {
        Write-Verbose -Message ((Get-ResStr 'STARTING_FUNCTION') -f $myInvocation.Mycommand)
        $initialVariables = Get-CurrentVariables -Debug:$DebugPreference
    }

    process {
        # Create XmlWriterSettings
        $settings = New-Object System.Xml.XmlWriterSettings
        $settings.Indent = $true

        # Create a StringWriter
        $stringWriter = New-Object System.IO.StringWriter

        # Create XmlWriter that writes to the StringWriter
        $writer = [System.Xml.XmlWriter]::Create($stringWriter, $settings)

        if ($show) {
            $totalItems = $datanorm.a.values.count + $datanorm.p.values.count
            $currentItem = 0
            $item = [string]""
        }

        $writer.WriteStartDocument()

        # Start ARTIKELLISTE
        $writer.WriteStartElement('ARTIKELLISTE')

        foreach ($articleA in $datanorm.a.values) {
            if ($show) {
                $currentItem++
                $percentage = ($currentItem / $totalItems) * 100
                $item = $articleA.ArtikelNummer
                Write-Progress `
                    -Activity (Get-ResStr 'PROGBAR_DATANORM_PROMPT') `
                    -Status ((Get-ResStr 'PROGBAR_DATANORM_STATUS') -f $item, $currentItem, $totalItems) `
                    -PercentComplete $percentage
            }

            # Start ARTIKEL
            $writer.WriteStartElement('ARTIKEL')

            $articleB = $datanorm.b[$articleA.ArtikelNummer]

            $writer.WriteElementString('ARTNUMMER', $articleA.ArtikelNummer)
            $writer.WriteElementString('ARTMATCH', $articleB.Matchcode)
            $writer.WriteElementString('BARCODE', $articleB.EuroArtikelNummer)
            $writer.WriteElementString('ARTNUMMERERSATZ', $articleB.AlternativArtikelNummer)


            $price = (ConvertTo-USFloat -inputString $articleA.Preis)
            if ($articleA.PreisKennzeichen -eq '1') {
                $writer.WriteElementString('VKNETTO', $price)
            } else {
                $writer.WriteElementString('EKNETTO', $price)
            }

            $writer.WriteElementString('PREISEH', (Get-DatanormPriceUnit -priceUnitCode $articleA.PreisEinheit ))
            $writer.WriteElementString('MENGENEH', $articleA.MengenEinheit)
            if ($articleB.VerpackungsMenge) {
                $writer.WriteElementString('VERPACKEH', $articleB.VerpackungsMenge)
            } else {
                $writer.WriteElementString('VERPACKEH', 1)
            }
            $writer.WriteElementString('RABATTGR', $articleA.RabattGruppe)
            $writer.WriteElementString('WARENGR', $articleA.WarenhauptGruppe)

            $writer.WriteElementString('KURZTEXT1', $articleA.Kurztext1)
            $writer.WriteElementString('KURZTEXT2', $articleA.Kurztext2)
            $writer.WriteElementString('ULTRAKURZTEXT', $articleA.Kurztext1)
            $writer.WriteElementString('LANGTEXT', "$($articleA.Kurztext1)`r`n$($articleA.Kurztext2)"  )

            $writer.WriteElementString('USERN3',  (ConvertTo-USFloat -inputString $articleB.EUL_CuAufschlagProStueck))

            # End ARTIKEL
            $writer.WriteEndElement()
        }


        foreach ($price in $datanorm.p.values) {
            # Start ARTIKEL
            $writer.WriteStartElement('ARTIKEL')

            $artNoSet = $false
            if ($price['1']) {
                $writer.WriteElementString('ARTNUMMER', $price['1'].ArtikelNummer)
                $item = $price['1'].ArtikelNummer
                $artNoSet = $true
                $writer.WriteElementString('VKNETTO', (ConvertTo-USFloat -inputString $price['1'].Preis))
            }

            if ($price['2']) {
                if (! $artNoSet) {
                    $writer.WriteElementString('ARTNUMMER', $price['2'].ArtikelNummer)
                    $item = $price['2'].ArtikelNummer
                }
                $writer.WriteElementString('EKNETTO', (ConvertTo-USFloat -inputString $price['2'].Preis))
            }

            # End ARTIKEL
            $writer.WriteEndElement()

            if ($show) {
                $currentItem++
                $percentage = ($currentItem / $totalItems) * 100
                Write-Progress `
                    -Activity (Get-ResStr 'PROGBAR_DATANORM_PROMPT') `
                    -Status ((Get-ResStr 'PROGBAR_DATANORM_STATUS') -f $item, $currentItem, $totalItems) `
                    -PercentComplete $percentage
            }
        }

        # End ARTIKELLISTE
        $writer.WriteEndElement()
        $writer.WriteEndDocument()

        # Clean up the writer
        $writer.Flush()
        $writer.Close()

        # XML RAW for Root
        [xml]$xml = Get-XmlEulandaRoot

        # XML RAW for Metadata
        [xml]$xmlMetadata = Get-XmlEulandaMetadata

        # Now you have the XML in the StringWriter
        [xml]$xmlArticle = $stringWriter.ToString()

        $newNode = $xmlMetadata.SelectSingleNode("//METADATA")
        $node = $xml.ImportNode($newNode, $true)
        $xml.DocumentElement.AppendChild($node) | Out-Null

        if ($xmlArticle) {
            $newNode = $xmlArticle.SelectSingleNode("//ARTIKELLISTE")
            $node = $xml.ImportNode($newNode, $true)
            $xml.DocumentElement.AppendChild($node) | Out-Null
        }

        $result = [string](Format-Xml -xmlString $xml.OuterXml)

        if ($show) {
            Write-Progress -Activity (Get-ResStr 'PROGBAR_DATANORM_PROMPT') -Completed
        }
    }

    end {
        Get-CurrentVariables -InitialVariables $initialVariables -Debug:$DebugPreference
        Return $result
    }
    # Test: $xml = Convert-DatanormToXml -datanorm $datanorm
}

function Convert-DataToXml {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $false)]
        $data = $(Throw ((Get-ResStr 'PARAM_MANDATORY_MISSED') -f 'data', $myInvocation.Mycommand))
        ,
        [Parameter(Mandatory = $false)]
        [switch]$metadata
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateStringCase $_ })]
        [string]$strCase = 'none'
        ,
        [Parameter(Mandatory = $false)]
        [string]$root = 'Root'
        ,
        [Parameter(Mandatory = $false)]
        [string]$arrRoot = 'Records'
        ,
        [Parameter(Mandatory = $false)]
        [string]$arrSubRoot = 'Record'
    )

    function Convert-DataToXmlInner {
        [CmdletBinding()]
        param(
            [Parameter(Mandatory = $false)]
            $data = $(Throw ((Get-ResStr 'PARAM_MANDATORY_MISSED') -f 'data', $myInvocation.Mycommand))
            ,
            [Parameter(Mandatory = $false)]
            [switch]$metadata
            ,
            [Parameter(Mandatory = $false)]
            [ValidateScript({ Test-ValidateStringCase $_ })]
            [string]$strCase = 'none'
            ,
            [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
        )

        begin {
            Write-Verbose -Message ((Get-ResStr 'STARTING_FUNCTION') -f $myInvocation.Mycommand)
            New-Variable -Name 'encoding' -Scope 'Private' -Value ([System.Text.Encoding]::UTF8)
            New-Variable -Name 'i' -Scope 'Private' -Value (0)
            New-Variable -Name 'key' -Scope 'Private' -Value ('')
            New-Variable -Name 'nodeName' -Scope 'Private' -Value ('')
            New-Variable -Name 'on' -Scope 'Private' -Value ($false)
            New-Variable -Name 'reader' -Scope 'Private' -Value ($null)
            New-Variable -Name 'settings' -Scope 'Private' -Value ($null)
            New-Variable -Name 'spaces' -Scope 'Private' -Value ('')
            New-Variable -Name 'stream' -Scope 'Private' -Value ($null)
            New-Variable -Name 'strValue' -Scope 'Private' -Value ('')
            New-Variable -Name 'value' -Scope 'Private' -Value ($null)
            New-Variable -Name 'xmlString' -Scope 'Private' -Value ('')
            New-Variable -Name 'result' -Scope 'Private' -Value ($null)
            $initialVariables = Get-CurrentVariables -Debug:$DebugPreference
        }

        process {
            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-DataToXmlInner -data $data[$i] -root $nodeName -level ($level+1) -writer $writer -strCase $strCase -arrRoot $arrRoot -arrSubRoot $arrSubRoot
                    $writer.WriteEndElement()
                }
                $writer.WriteEndElement()
            } elseif ($data) {
                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-DataToXmlInner -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-DataToXmlInner -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-DataToXmlInner -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 }
                    }
                }
            } else {
                # No data found
                $writer.WriteStartElement($arrRoot)
                $writer.WriteEndElement()
            }

            # If the level is '0' when the function results 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]$result = $xmlString
            }
        }

        end {
            Get-CurrentVariables -InitialVariables $initialVariables -Debug:$DebugPreference
            if ($level -eq 0) { Return $result }
        }

    }

    $result = Convert-DataToXmlInner -data $data -metadata:$metadata -strCase $strCase -root $root  -arrRoot $arrRoot -arrSubRoot $arrSubRoot -Debug:$DebugPreference
    Return $result
    # Test: (Convert-DataToXml -data @([ordered]@{'Field1'='Value1';'Field2'='Value2'}, [ordered]@{'Field3'='Value3';'Field4'='Value4'})).OuterXml
}


function Convert-DateToIso {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $false)]
        [datetime]$value = $(Throw ((Get-ResStr 'PARAM_MANDATORY_MISSED') -f 'value', $myInvocation.Mycommand))
        ,
        [Parameter(Mandatory = $false)]
        [switch]$asUtc
        ,
        [Parameter(Mandatory = $false)]
        [switch]$noTime
        ,
        [Parameter(Mandatory = $false)]
        [switch]$noDate
        ,
        [Parameter(Mandatory = $false)]
        [switch]$noTimeZone
        ,
        [Parameter(Mandatory = $false)]
        [switch]$zeroTime
        ,
        [Parameter(Mandatory = $false)]
        [switch]$noonTime
    )

    begin {
        Write-Verbose -Message ((Get-ResStr 'STARTING_FUNCTION') -f $myInvocation.Mycommand)
        New-Variable -Name 'date' -Scope 'Private' -Value ($null)
        New-Variable -Name 'result' -Scope 'Private' -Value ('')
        $initialVariables = Get-CurrentVariables -Debug:$DebugPreference
    }

    process {
        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")
                }
            }
        }
    }

    end {
        Get-CurrentVariables -InitialVariables $initialVariables -Debug:$DebugPreference
        Return $result
    }
    # Test: Convert-DateToIso -value (get-date) -noontime -debug
}

function Convert-FromDatanorm {
    [CmdletBinding()]
    param(
        [string]$path
        ,
        [double]$vat = 19.0
        ,
        [double]$cuDel = 802.0 # LAPP Copper Price (LCP) per 100 kg, because DEL has stopped his notes
        ,
        [switch]$utf8
        ,
        [switch]$show
        ,
        [Alias('decimal')]
        [string]$decimalSeparator = [System.Globalization.CultureInfo]::CurrentCulture.NumberFormat.NumberDecimalSeparator
    )

    begin {
        Write-Verbose -Message ((Get-ResStr 'STARTING_FUNCTION') -f $myInvocation.Mycommand)
        $initialVariables = Get-CurrentVariables -Debug:$DebugPreference
    }

    process {
        if (Test-Path $path -PathType Container) {
            $filenames = @("DATANORM.RAB", "DATANORM.WRG") + (1..999 | ForEach-Object { "DATANORM.{0:D3}" -f $_ }) + (1..999 | ForEach-Object { "DATPREIS.{0:D3}" -f $_ })
            # Convert filenames to absolute paths
            $filepaths = $filenames | ForEach-Object { Join-Path -Path $path -ChildPath $_ }
            # Keep only paths that refer to existing files
            $filepaths = $filepaths | Where-Object { Test-Path $_ -PathType Leaf }
        } else {
            # Path is only a single file
            $filepaths = @($path)
        }


        # Create empty lists for each record type
        $a = @{}
        $b = @{}
        $v = @{}
        $p = @{}

        if ($show) {
            $totalFiles = $filepaths.Count
            $currentFile = 0
        }
        foreach ($filepath in $filepaths) {

            if ($show) {
                $currentFile++
                $percentage = ($currentFile / $totalFiles) * 100
                Write-Progress `
                    -Id 1 `
                    -Activity (Get-ResStr 'PROGBAR_FILES_PROMPT') `
                    -Status ((Get-ResStr 'PROGBAR_FILES_STATUS') -f $(Split-Path $filepath -Leaf), $currentFile, $filepaths.Count) `
                    -PercentComplete $percentage
            }


            # Test if it is an empty file
            if ((Get-Item $filepath).Length -eq 0) {
                Write-Error ((Get-ResStr 'DATANORM_FILE_EMPTY') -f $filepath, $myInvocation.Mycommand) -ErrorAction Continue
                Continue
            }

            if ($utf8) {
                # First test utf-8, this is uncommon and also not according to the standard, but a good way to support both encodings
                $encoding = [System.Text.Encoding]::GetEncoding("UTF-8")
                $allLines = [System.IO.File]::ReadAllLines($filepath, $encoding)

                $hasInvalidChars = $false
                foreach($line in $allLines) {
                    if ($line -match '�') {  # illegal char '�' found
                        $hasInvalidChars = $true
                        break
                    }
                }

                if ($hasInvalidChars) {
                    # This is the allowed method for datanorm
                    $encoding = [System.Text.Encoding]::GetEncoding("IBM850")  # IBM850 corresponds to CP850
                    $allLines = [System.IO.File]::ReadAllLines($filepath, $encoding)
                }
            } else {
                $encoding = [System.Text.Encoding]::GetEncoding("IBM850")  # IBM850 corresponds to CP850
                $allLines = [System.IO.File]::ReadAllLines($filepath, $encoding)
            }

            $totalLines = $AllLines.Count
            $currentLine = 0
            foreach ($line in $allLines) {
                $currentLine++

                # Check which record type is present and process accordingly
                if (! $line) {
                    continue
                }


                # ------------------------------
                # TYPE A
                # ------------------------------
                if ($line[0] -eq "A") {
                    # Separate individual fields
                    $fields = $line.Split(";")

                    # Create new PSobject and assign fields
                    $rec = New-Object PSObject -Property ([ordered]@{
                        SatzKennzeichen       = $fields[0]
                        VerarbeitungsKennzeichen = $fields[1]
                        ArtikelNummer         = $fields[2]
                        TextKennzeichen       = $fields[3]
                        Kurztext1             = $fields[4]
                        Kurztext2             = $fields[5]
                        PreisKennzeichen      = $fields[6]
                        PreisEinheit          = $fields[7]
                        MengenEinheit         = $fields[8]
                        Preis                 = Add-DecimalPoint -number $fields[9]
                        RabattGruppe          = $fields[10]
                        WarenhauptGruppe      = $fields[11]
                        LangtextSchluessel    = $fields[12]
                        EUL_PreisProStueck    = Get-DatanormPricePerUnit `
                                                -price (ConvertTo-USFloat(Add-DecimalPoint -number $fields[9])) `
                                                -priceUnitCode $fields[7]
                    })
                    $a[$rec.ArtikelNummer] = $rec
                    if ($show) {
                        $percentage = ($currentLine / $totalLines * 100)
                        Write-Progress `
                            -Id 2 `
                            -Activity ((Get-ResStr 'PROGBAR_FILE_PROMPT') -f $(Split-Path $filepath -Leaf)) `
                            -Status ((Get-ResStr 'PROGBAR_FILE_STATUS') -f $rec.ArtikelNummer, $currentLine, $totalLines) `
                            -PercentComplete $percentage
                    }
                }

                # ------------------------------
                # TYPE B
                # ------------------------------
                elseif ($line[0] -eq "B") {
                    # Separate individual fields
                    $fields = $line.Split(";")

                    # Create new PSobject and assign fields
                    $rec = New-Object PSObject -Property ([ordered]@{
                        SatzKennzeichen          = $fields[0]
                        VerarbeitungsKennzeichen = $fields[1]
                        ArtikelNummer            = $fields[2]
                        Matchcode                = $fields[3]
                        AlternativArtikelNummer  = $fields[4]
                        KatalogSeite             = $fields[5]
                        CUGewichtsMerker         = $fields[6]
                        CUKennzahl               = $fields[7]
                        Gewicht                  = Add-DecimalPoint -number $fields[8]
                        EuroArtikelNummer        = $fields[9]
                        AnbindungsNummer         = $fields[10]
                        WarenGruppe              = $fields[11]
                        KostenArt                = $fields[12]
                        VerpackungsMenge         = $fields[13]
                        ReverenzKuerzel          = $fields[14]
                        ReverenzNummer           = $fields[15]
                        EUL_CuGewichtProStueck   = Get-DatanormCuWeight `
                                                    -cuWeight (ConvertTo-USFloat(Add-DecimalPoint -number $fields[8])) `
                                                    -divisionCode $fields[6]
                        EUL_CuAufschlagProStueck = Get-DatanormCuSurcharge `
                                                    -cuWeight (ConvertTo-USFloat(Add-DecimalPoint -number $fields[8])) `
                                                    -cuDel $cuDel `
                                                    -cuIncluded $fields[7] `
                                                    -divisionCode $fields[6]
                        EUL_CuDelPro100Kg        = $CuDel.ToString()
                    })
                    $b[$rec.ArtikelNummer] = $rec
                }


                # ------------------------------
                # TYPE V
                # ------------------------------
                elseif ($line[0] -eq "V") {
                    $rec = New-Object PSObject -Property ([ordered]@{
                        SatzKennzeichen       = $line.Substring(0,1)
                        Frei                  = $line.Substring(1,1)
                        Datum                 = Convert-DatanormDateFormat $line.Substring(2,6)
                        InfoText1             = $line.Substring(8,40).Trim()
                        InfoText2             = $line.Substring(48,40).Trim()
                        InfoText3             = $line.Substring(88,35).Trim()
                        VersionsNummer        = $line.Substring(123,2)
                        WaehrungsKennzeichen  = $line.Substring(125,3)
                    })
                    $v['V'] = $rec
                }


                # ------------------------------
                # TYPE P
                # ------------------------------
                elseif ($line[0] -eq "P") {
                    $fields = $line.Split(";")

                    $rec = New-Object PSObject -Property ([ordered]@{
                        SatzKennzeichen          = $fields[0]
                        VerarbeitungsKennzeichen = $fields[1]

                        ArtikelNummer           = $fields[2]
                        PreisKennzeichen        = $fields[3]
                        Preis                   = Add-DecimalPoint -number $fields[4]
                        KonditonKennzeichen1    = $fields[5]
                        Kondition1              = Get-DatanormConditionDecimals -condition $fields[6] -indicator ([int]$fields[5])
                        KonditonKennzeichen2    = $fields[7]
                        Kondition2              = Get-DatanormConditionDecimals -condition $fields[8] -indicator ([int]$fields[7])
                        KonditonKennzeichen3    = $fields[9]
                        Kondition3              = Get-DatanormConditionDecimals -condition $fields[10] -indicator ([int]$fields[9])
                    })
                    if ($show) {
                        $percentage = ($currentLine / $totalLines * 100)
                        Write-Progress `
                            -Id 2 `
                            -Activity ((Get-ResStr 'PROGBAR_FILE_PROMPT') -f $(Split-Path $filepath -Leaf)) `
                            -Status ((Get-ResStr 'PROGBAR_FILE_STATUS') -f $rec.ArtikelNummer, $currentLine, $totalLines) `
                            -PercentComplete $percentage
                    }


                    if ($rec.ArtikelNummer -ne '') {
                        # Check if there's already an entry for the A article number
                        if (!$p.ContainsKey($rec.ArtikelNummer)) {
                            # If not, create a new inner hash table for this article number
                            $p[$rec.ArtikelNummer] = @{}
                        }
                        # Now add the record to the inner hash table, using the PreisKennzeichen as the key
                        $p[$rec.ArtikelNummer][$rec.PreisKennzeichen] = $rec
                    }


                    $rec = New-Object PSObject -Property ([ordered]@{
                        SatzKennzeichen          = $fields[0]
                        VerarbeitungsKennzeichen = $fields[1]

                        ArtikelNummer           = $fields[11]
                        PreisKennzeichen        = $fields[12]
                        Preis                   = Add-DecimalPoint -number $fields[13]
                        KonditonKennzeichen1    = $fields[14]
                        Kondition1              = Get-DatanormConditionDecimals -condition $fields[15] -indicator ([int]$fields[14])
                        KonditonKennzeichen2    = $fields[16]
                        Kondition2              = Get-DatanormConditionDecimals -condition $fields[17] -indicator ([int]$fields[16])
                        KonditonKennzeichen3    = $fields[18]
                        Kondition3              = Get-DatanormConditionDecimals -condition $fields[19] -indicator ([int]$fields[18])
                    })

                    if ($rec.ArtikelNummer -ne '') {
                        # Check if there's already an entry for the A article number
                        if (!$p.ContainsKey($rec.ArtikelNummer)) {
                            # If not, create a new inner hash table for this article number
                            $p[$rec.ArtikelNummer] = @{}
                        }
                        # Now add the record to the inner hash table, using the PreisKennzeichen as the key
                        $p[$rec.ArtikelNummer][$rec.PreisKennzeichen] = $rec
                    }

                    $rec = New-Object PSObject -Property ([ordered]@{
                        SatzKennzeichen          = $fields[0]
                        VerarbeitungsKennzeichen = $fields[1]

                        ArtikelNummer           = $fields[20]
                        PreisKennzeichen        = $fields[21]
                        Preis                   = Add-DecimalPoint -number $fields[22]
                        KonditonKennzeichen1    = $fields[23]
                        Kondition1              = Get-DatanormConditionDecimals -condition $fields[24] -indicator ([int]$fields[23])
                        KonditonKennzeichen2    = $fields[25]
                        Kondition2              = Get-DatanormConditionDecimals -condition $fields[26] -indicator ([int]$fields[25])
                        KonditonKennzeichen3    = $fields[27]
                        Kondition3              = Get-DatanormConditionDecimals -condition $fields[28] -indicator ([int]$fields[27])
                    })

                    if ($rec.ArtikelNummer -ne '') {
                        # Check if there's already an entry for the A article number
                        if (!$p.ContainsKey($rec.ArtikelNummer)) {
                            # If not, create a new inner hash table for this article number
                            $p[$rec.ArtikelNummer] = @{}
                        }
                        # Now add the record to the inner hash table, using the PreisKennzeichen as the key
                        $p[$rec.ArtikelNummer][$rec.PreisKennzeichen] = $rec
                    }
                }
            }

        }

        if ($show) {
            Write-Progress -Id 1 -Activity (Get-ResStr 'PROGBAR_FILES_PROMPT') -Completed
            Write-Progress -Id 2 -Activity (Get-ResStr 'PROGBAR_FILE_PROMPT') -Completed
        }

        # Create a new object that contains all supported record types of ol processed files
        if ($a -and $a.Count -gt 0 -or
            $b -and $b.Count -gt 0 -or
            $v -and $v.Count -gt 0 -or
            $p -and $p.Count -gt 0) {
            $datanorm = New-Object PSObject -Property @{
                a = $a
                b = $b
                v = $v
                p = $p
            }
        } else {
            $datanorm = $null
        }
    }

    end {
        Get-CurrentVariables -InitialVariables $initialVariables -Debug:$DebugPreference
        return $datanorm
    }
    # Test: $datanorm = Convert-FromDatanorm -path "$PSScriptRoot\.ignore\data\zander"
}

function Convert-ImageToBase64 {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateFileExists -Path $_ })]
        [string]$path = $(Throw ((Get-ResStr 'PARAM_MANDATORY_MISSED') -f 'path', $myInvocation.Mycommand))
    )

    begin {
        Write-Verbose -Message ((Get-ResStr 'STARTING_FUNCTION') -f $myInvocation.Mycommand)
        New-Variable -Name 'base64' -Scope 'Private' -Value ('')
        New-Variable -Name 'content' -Scope 'Private' -Value ($null)
        New-Variable -Name 'extension' -Scope 'Private' -Value ('')
        New-Variable -Name 'mimeType' -Scope 'Private' -Value ('')
        New-Variable -Name 'result' -Scope 'Private' -Value ('')
        $initialVariables = Get-CurrentVariables -Debug:$DebugPreference
    }

    process {
        $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"
    }

    end {
        Get-CurrentVariables -InitialVariables $initialVariables -Debug:$DebugPreference
        return $result
    }
    # Test: Convert-ImageToBase64 -path 'C:\temp\Eulanda.jpg'
}

function Convert-OemToUtf8 {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory=$true)]
        [string]$inputString
    )

    $convertedString = [System.Text.StringBuilder]::new()

    foreach ($char in $inputString.ToCharArray()) {
        switch ([int][char]$char) {
            129 {
                $convertedString = $convertedString.Append('ü')
            }
            132 { $convertedString = $convertedString.Append('ä') }
            142 { $convertedString = $convertedString.Append('Ä') }
            148 { $convertedString = $convertedString.Append('ö') }
            153 { $convertedString = $convertedString.Append('Ö') }
            154 { $convertedString = $convertedString.Append('Ü') }
            225 { $convertedString = $convertedString.Append('ß') }
            default { $convertedString = $convertedString.Append($char) }
        }
    }

    return $convertedString.ToString()

    # Test: $a = Convert-OemToUtf8 'Rckfahrt'
}

function Convert-Slugify {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $false)]
        [ValidateNotNullOrEmpty()]
        [string]$value = $(Throw ((Get-ResStr 'PARAM_MANDATORY_MISSED') -f 'value', $myInvocation.Mycommand))
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateStringCase $_ })]
        [string]$strCase = 'none'
        ,
        [Parameter(Mandatory = $false)]
        [ValidateSet('-', '_')]
        [string]$delimiter = '_'
    )

    begin {
        Write-Verbose -Message ((Get-ResStr 'STARTING_FUNCTION') -f $myInvocation.Mycommand)
        New-Variable -Name 'regex' -Scope 'Private' -Value ($null)
        New-Variable -Name 'result' -Scope 'Private' -Value ('')
        $initialVariables = Get-CurrentVariables -Debug:$DebugPreference
    }

    process {
        $regex = [System.Text.RegularExpressions.Regex]
        $result = convert-accent -value $value
        $result = [Regex]::Replace($result, [char]0x20AC, ' EUR ')  # For compatibility with powerShell 5.x
        $result = $result.replace('$',' USD ')
        $result = $result.replace('£',' GBP ')
        $result = $result.replace('²','2')
        $result = $result.replace('³','3')
        $result = $result.replace('-',' ')
        $result = $result.replace('_',' ')
        $result = $regex::Replace($result, "[^a-zA-Z0-9\s-]", "")
        $result = $regex::Replace($result, "\s+", " ").Trim()
        $result = $regex::Replace($result, "\s", $delimiter)
        $result = $result = Convert-StringCase -value $result -strCase $strCase
    }

    end {
        Get-CurrentVariables -InitialVariables $initialVariables -Debug:$DebugPreference
        Return $result
    }
    # Test: Convert-Slugify -value 'This is Österreich where you pan pay in € or $ but all in m³ and never in m²'
}

Function Convert-StringCase {
    [CmdletBinding()]
    Param (
        [Parameter(Mandatory = $false)]
        [ValidateNotNullOrEmpty()]
        [string]$value = $(Throw ((Get-ResStr 'PARAM_MANDATORY_MISSED') -f 'value', $myInvocation.Mycommand))
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateStringCase $_ })]
        [string]$strCase = 'none'
    )

    begin {
        Write-Verbose -Message ((Get-ResStr 'STARTING_FUNCTION') -f $myInvocation.Mycommand)
        New-Variable -Name 'culture' -Scope 'Private' -Value ($null)
        New-Variable -Name 'words' -Scope 'Private' -Value ([string[]]@())
        New-Variable -Name 'wordsCapitalized' -Scope 'Private' -Value ([System.Object[]]@())
        New-Variable -Name 'result' -Scope 'Private' -Value ('')
        $initialVariables = Get-CurrentVariables -Debug:$DebugPreference
    }

    process {
        $result = $value
        switch ( $strCase.ToLower() ) {
            'none' { $result = $result }
            'upper' { $result = $result.ToUpper() }
            'lower' { $result = $result.ToLower() }
            'capital' {
                $culture = [System.Globalization.CultureInfo]::CurrentCulture
                $words = $result.Split(' ')
                $wordsCapitalized = $words | ForEach-Object { $culture.TextInfo.ToTitleCase($_) }
                $result = $wordsCapitalized -join ' '
            }
        }
    }

    end {
        Get-CurrentVariables -InitialVariables $initialVariables -Debug:$DebugPreference
        Return $result
    }
    # Test: Convert-StringCase -value 'Der Caffè ist übergut in Österreich!' -strCase capital
}

function Convert-SubnetToBitmask {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $false)]
        [string]$subnet
        ,
        [Parameter(Mandatory = $false)]
        [int]$cidr
    )

    begin {
        Write-Verbose -Message ((Get-ResStr 'STARTING_FUNCTION') -f $myInvocation.Mycommand)
        Test-ValidateSingle -validParams ('subnet','cidr') @PSBoundParameters
        New-Variable -Name 'result' -Scope 'Private' -Value ('')
        $initialVariables = Get-CurrentVariables -Debug:$DebugPreference
    }

    process {
        try {
            <#
            if (($null -eq $subnet) -and ($null -eq $cidr)) {
                $subnet = Get-Subnet # get it from local ip
            }
#>

            if ($null -eq $cidr) {
                $cidr = Get-Cidr -subnet $subnet
            }

            if ($cidr -lt 0 -or $cidr -gt 32) {
                throw "CIDR value ($cidr) is out of the valid range (0-32)"
            }

            $result = ('1' * $cidr).PadRight(32, '0')
        }
        catch {
            throw $_  # Propagate the error
        }
    }


    end {
        Get-CurrentVariables -InitialVariables $initialVariables -Debug:$DebugPreference
        Return $result
    }
    # test: Convert-SubnetToBitmask -cidr 24
}

function Convert-ToDecimalDegrees {
    [CmdletBinding()]
    param(
        [parameter(Mandatory = $false)]
        [ValidateRange(0,180)]
        [int]$degrees
        ,
        [parameter(Mandatory = $false)]
        [ValidateRange(0,59)]
        [int]$minutes
        ,
        [parameter(Mandatory = $false)]
        [ValidateRange(0,59)]
        [int]$seconds
        ,
        [parameter(Mandatory = $false)]
        [ValidateSet('N', 'S', 'E', 'W')]
        [string]$direction
    )

    begin {
        Write-Verbose -Message ((Get-ResStr 'STARTING_FUNCTION') -f $myInvocation.Mycommand)
        $initialVariables = Get-CurrentVariables -Debug:$DebugPreference
    }

    process {
        $result = $degrees + $minutes / 60 + $seconds / 3600

        # Convert direction to upper case
        $direction = $direction.ToUpper()

        # If the direction is South or West, make the result negative
        if ($direction -eq 'S' -or $direction -eq 'W') {
            $result *= -1
        }
    }

    end {
        Get-CurrentVariables -InitialVariables $initialVariables -Debug:$DebugPreference
        Return $result
    }
    # Test: Convert-ToDecimalDegrees
}

function ConvertTo-USFloat {
    [CmdletBinding()]
    param (
        [string]$inputString
    )

    $commaPos = $inputString.LastIndexOf(",")
    $dotPos = $inputString.LastIndexOf(".")

    if ($dotPos -gt $commaPos) {
        $outputString = [string]$inputString.Replace(",", "")
    } else {
        $outputString = [string]$inputString.Replace(".", "")
        $outputString = [string]$outputString.Replace(",", ".")
    }

    try {
        $outputFloat = [float]$outputString
        if ($outputFloat) { $outputFloat = 0}  # Suppress vsc error markers
    } catch {
        Write-Error (Get-ResStr 'USFLOAT_ERROR') -f $inputString, $myInvocation.Mycommand
        return
    }

    return $outputString
}

function ConvertTo-WrappedLines {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $false)]
        [string]$text
        ,
        [Parameter(Mandatory = $false)]
        [int]$width= 80
        ,
        [Parameter(Mandatory = $false)]
        [switch]$asString
        ,
        [Parameter(Mandatory = $false)]
        [switch]$useCrLf
    )

    begin {
        Write-Verbose -Message ((Get-ResStr 'STARTING_FUNCTION') -f $myInvocation.Mycommand)
        New-Variable -Name 'column' -Scope 'Private' -Value (0)
        New-Variable -Name 'line' -Scope 'Private' -Value ('')
        New-Variable -Name 'paragraph' -Scope 'Private' -Value ('')
        New-Variable -Name 'paragraphs' -Scope 'Private' -Value ($null)
        New-Variable -Name 'rawWords' -Scope 'Private' -Value ($null)
        New-Variable -Name 'word' -Scope 'Private' -Value ('')
        New-Variable -Name 'words' -Scope 'Private' -Value ($null)
        New-Variable -Name 'result' -Scope 'Private' -Value ($null)
        $initialVariables = Get-CurrentVariables -Debug:$DebugPreference
    }

    process {
        # 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.trim() -join "`r`n"
            } else {
                [string]$result = $result.trim() -join "`n"
            }
        } else {
            $result = ,$result
            try {
                if ($result[0].Count -gt 1)  {
                    if ($result[0][0] -eq "") {
                        $result[0].RemoveAt(0)
                    }
                }
            }
            catch {
            }

        }
    }

    end {
        Get-CurrentVariables -InitialVariables $initialVariables -Debug:$DebugPreference
        Return $result
    }
    # Test: ConvertTo-WrappedLines -text 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed porttitor lacus sed augue commodo dapibus. Suspendisse potenti.' -width 40
}

function ConvertTo-WrappedLinesEdi {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $false)]
        [string]$text
        ,
        [Parameter(Mandatory = $false)]
        [int]$Width = 80
    )

    begin {
        Write-Verbose -Message ((Get-ResStr 'STARTING_FUNCTION') -f $myInvocation.Mycommand)
        New-Variable -Name 'a1' -Scope 'Private' -Value ('')
        New-Variable -Name 'al' -Scope 'Private' -Value ('')
        New-Variable -Name 'i' -Scope 'Private' -Value (0)
        New-Variable -Name 'maxPunctuation' -Scope 'Private' -Value (0)
        New-Variable -Name 'paragraph' -Scope 'Private' -Value ('')
        New-Variable -Name 'paragraphs' -Scope 'Private' -Value ($null)
        New-Variable -Name 'punctuation' -Scope 'Private' -Value (0)
        New-Variable -Name 'test' -Scope 'Private' -Value (0)
        New-Variable -Name 'result' -Scope 'Private' -Value ($null)
        $initialVariables = Get-CurrentVariables -Debug:$DebugPreference
    }

    process {
        # 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 (Get-ResStr 'VERBOSE_WRAPPED_TWO_LINES')
        } elseif (($paragraphs.count -eq 1) -and ($paragraphs[0].length -le $width) ) {
            Write-Verbose (Get-ResStr 'VERBOSE_WRAPPED_FIRST_LINE')
            $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()
    }

    end {
        Get-CurrentVariables -InitialVariables $initialVariables -Debug:$DebugPreference
        Return $result
    }
    # Test: ConvertTo-WrappedLinesEdi -text 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed porttitor lacus sed augue commodo dapibus. Suspendisse potenti.' -width 40
}

function ConvertTo-XmlString {
    [CmdletBinding()]
    param (
        [System.__ComObject]$adoField
        ,
        [switch]$includeEmpty
    )

    begin {
        Write-Verbose -Message ((Get-ResStr 'STARTING_FUNCTION') -f $myInvocation.Mycommand)
        New-Variable -Name 'adoType' -Scope 'Private' -Value ([int64]0)
        New-Variable -Name 'result' -Scope 'Private' -Value ([string]'')
        $initialVariables = Get-CurrentVariables -Debug:$DebugPreference
    }

    process {
        [long]$adoType = $adoField.Type
        [object]$result = $adoField.Value

        switch ($adoType) {
            {$_ -in @($adChar, $adVarChar, $adLongVarChar, $adWChar, $adVarWChar, $adLongVarWChar, $adBSTR)} {

                if ($result -eq [System.DBNull]::Value -or $null -eq $result) {
                    [string]$result = [string]''
                } else {
                    [string]$result = [string]$result.trim()
                }
            }
            {$_ -in @($adSmallInt, $adInteger, $adTinyInt)} {
                if ($result -eq [System.DBNull]::Value -or $null -eq $result) {
                    if ($includeEmpty) {
                        [string]$result = [string]''
                    } else {
                        [string]$result = [string]'0'
                    }
                } else {
                    [string]$result = [string]$result
                }
            }
            {$_ -in @($adBigInt, $adUnsignedBigInt)} {
                if ($result -eq [System.DBNull]::Value -or $null -eq $result) {
                    if ($includeEmpty) {
                        [string]$result = [string]''
                    } else {
                        [string]$result = [string]'0'
                    }
                } else {
                    [string]$result = [string]$result
                }
            }
            {$_ -in @($adSingle, $adDouble)} {
                if ($result -eq [System.DBNull]::Value -or $null -eq $result) {
                    if ($includeEmpty) {
                        [string]$result = [string]''
                    } else {
                        [string]$result = [string]'0.0'
                    }
                } else {
                    [string]$result = [string]$result
                }
            }
            {$_ -in @($adCurrency, $adDecimal, $adNumeric)} {
                if ($result -eq [System.DBNull]::Value -or $null -eq $result) {
                    if ($includeEmpty) {
                        [string]$result = [string]''
                    } else {
                        [string]$result = [string]'0.0'
                    }
                } else {
                    [string]$result = [string]$result
                }
            }
            {$_ -in @($adDate, $adDBDate, $adDBTimeStamp)} {
                if ($result -eq [System.DBNull]::Value -or $null -eq $result) {
                    if ($includeEmpty) {
                        [string]$result = [string]''
                     } else {
                        [string]$result = [string](Convert-DateToIso -value ([datetime]::MinValue))
                     }
                } else {
                    [string]$result =  [string](Convert-DateToIso -value $result)
                }
            }
            {$_ -in @($adBoolean)} {
                if ($result -eq [System.DBNull]::Value -or $null -eq $result) {
                    if ($includeEmpty) {
                        [string]$result = [string]''
                    } else {
                        [string]$result = [string]'False'
                    }
                } else {
                    [string]$result = [string]$result
                }
            }
            default {
                if ($result -eq [System.DBNull]::Value -or $null -eq $result) {
                    [string]$result = [string]''
                } else {
                    [string]$result = [string]$result
                }
            }
        }

    }

    end {
        Get-CurrentVariables -InitialVariables $initialVariables -Debug:$DebugPreference
        Return $result
    }
    <# Test:
        $rs = New-Object -ComObject ADODB.Recordset
        $rs.Fields.Append("MyDateField", $adDBTimeStamp)
        $rs.Open()
        $rs.AddNew()
        $rs.Fields("MyDateField").Value = Get-Date
        $adoField = $rs.Fields("MyDateField")
 
        ConvertTo-XmlString -adoField $adoField -debug
 
        $rs.Close()
        [System.Runtime.InteropServices.Marshal]::ReleaseComObject($rs) | Out-Null
    #>

}

function Deny-RemoteFingerprint {
    [CmdletBinding()]
    Param(
        [parameter(Mandatory = $false)]
        [string]$server = $(Throw ((Get-ResStr 'PARAM_MANDATORY_MISSED') -f 'server', $myInvocation.Mycommand))
        ,
        [Parameter(Mandatory = $false)]
        [ValidateSet("ftp", "ftps", "sftp")]
        [string]$protocol = 'sftp'
        ,
        [Parameter(Mandatory = $false)]
        [int]$port = $(if ($protocol -eq "ftp") {21} elseif ($protocol -eq "ftps") {21} elseif ($protocol -eq "sftp") {22})
    )

    begin {
        Write-Verbose -Message ((Get-ResStr 'STARTING_FUNCTION') -f $myInvocation.Mycommand)
        New-Variable -Name 'cachedFingerprint' -Scope 'Private' -Value ([string]'')
        New-Variable -Name 'remoteFingerprint' -Scope 'Private' -Value ([string]'')
        New-Variable -Name 'result' -Scope 'Private' -Value ([boolean]$false)
        $initialVariables = Get-CurrentVariables -Debug:$DebugPreference
    }

    process {
        if ($protocol -eq 'sftp') {
            if ([bool](Get-Module -ListAvailable -Name POSH-SSH)) {
                Import-Module -Name POSH-SSH -global
                $trustedInfo = Get-SSHTrustedHost -HostName $server
                if ($trustedInfo) {
                    $cachedFingerprint = [string]($trustedInfo).Fingerprint
                }
                try {
                    $serverInfo = Get-SSHHostKey -ComputerName $server -Port $port
                    if ($serverInfo) {
                        $remoteFingerprint = [string]($serverInfo).Fingerprint
                        if (($null -ne $cachedFingerprint -and $cachedFingerprint -ne "") -and $cachedFingerprint -ne $remoteFingerprint) {
                            $result = $true
                        }
                    }
                } catch {
                }
            } else {
                Write-Error ((Get-ResStr 'POSH_NOTFOUND') -f $myInvocation.Mycommand)
            }
        }
    }

    end {
        Get-CurrentVariables -InitialVariables $initialVariables -Debug:$DebugPreference
        Return $result
    }
    # Test: Deny-RemoteFingerprint -server '192.168.42.1'
}

function Export-ArticleToXml {
    [CmdletBinding()]
    param(
        [parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateNotEmpty -strParam $_ })]
        [string]$select = (Get-DefaultSelectArticle)
        ,
        [parameter(Mandatory = $false)]
        [string[]]$filter
        ,
        [parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateMapping -strValue $_ -mapping (Get-MappingArticleKeys) })]
        [string]$alias = 'articleNo'
        ,
        [parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateMapping -strValue $_ -mapping (Get-MappingArticleKeys) })]
        [string]$order = $alias
        ,
        [parameter(Mandatory = $false)]
        [switch]$reorder
        ,
        [parameter(Mandatory = $false)]
        [switch]$revers
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateCustomerGroups -CustomerGroups $_ })]
        [string]$customerGroups
        ,
        [Parameter(Mandatory = $false)]
        [string]$breadcrumbRoot = '\Shop'
        ,
        [Parameter(Mandatory = $false)]
        [switch]$noEmptyPropertyTree
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidatePathXML -path $_  })]
        [string]$path
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateConn -conn $_  })]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidatePathUDL -path $_  })]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateConnStr -connStr $_ })]
        [string]$connStr
    )

    # **************************************************************************
    # CREATE XML MESSAGE: ARTICLE
    # **************************************************************************

    Begin {
        Write-Verbose -Message ((Get-ResStr 'STARTING_FUNCTION') -f $myInvocation.Mycommand)
        Test-ValidateSingle -validParams (Get-SingleConnection) @PSBoundParameters
        $alias = Test-ValidateMapping -strValue $alias -mapping (Get-MappingArticleKeys)
        $order = Test-ValidateMapping -strValue $order -mapping (Get-MappingArticleKeys)
        New-Variable -Name 'myConn' -Scope 'Private' -Value ($null)
        New-Variable -Name 'newNode' -Scope 'Private' -Value ($null)
        New-Variable -Name 'node' -Scope 'Private' -Value ($null)
        New-Variable -Name 'xmlArticle' -Scope 'Private' -Value ($null)
        New-Variable -Name 'xmlMetadata' -Scope 'Private' -Value ($null)
        New-Variable -Name 'xmlPropertyTree' -Scope 'Private' -Value ($null)
        New-Variable -Name 'paramsArticle' -Scope 'Private' -Value (@{})
        New-Variable -Name 'Result' -Scope 'Private' -Value ('')
        $initialVariables = Get-CurrentVariables -Debug:$DebugPreference
    }

    Process {
        $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr
        $paramsArticle = Get-UsedParameters -validParams (Get-SetArticleWithGroupsFilter) -boundParams $PSBoundParameters

        # **************************************************************************
        # Create a raw version for each database section
        # **************************************************************************

        # XML RAW for Root
        [xml]$xml = Get-XmlEulandaRoot

        # XML RAW for Metadata
        [xml]$xmlMetadata = Get-XmlEulandaMetadata

        # XML RAW for PropertyTree
        if ($breadcrumbRoot) {
            [xml]$xmlPropertyTree = Get-XmlEulandaProperty -breadcrumbRoot $breadcrumbRoot -tablename 'Article' -conn $myConn
        } else {
            [xml]$xmlPropertyTree = '<MERKMALBAUM><ARTIKEL /></MERKMALBAUM>'
        }
        if ($noEmptyPropertyTree -and
            $xmlPropertyTree.Merkmalbaum.ChildNodes.Count -eq 1 -and
            $xmlPropertyTree.Merkmalbaum.ChildNodes[0].IsEmpty) {
                [xml]$xmlPropertyTree = $null
        }

        # XML RAW for all articles including nodes like SHOP, LAGER, PREISLISTE, MERKMALLISTE
        [xml]$xmlArticle = Get-XmlEulandaArticle @paramsArticle -conn $myConn


        # **************************************************************************
        # Assemble the individual XML nodes into an XML message.
        # **************************************************************************

        $newNode = $xmlMetadata.SelectSingleNode("//METADATA")
        $node = $xml.ImportNode($newNode, $true)
        $xml.DocumentElement.AppendChild($node) | Out-Null

        if ($xmlPropertyTree) {
            $newNode = $xmlPropertyTree.SelectSingleNode("//MERKMALBAUM")
            $node = $xml.ImportNode($newNode, $true)
            $xml.DocumentElement.AppendChild($node) | Out-Null
        }

        if (! $xmlArticle) {
            [xml]$xmlArticle = '<ARTIKELLISTE />'
        } elseif (! $xmlArticle.OuterXml) {
            [xml]$xmlArticle = '<ARTIKELLISTE />'
        }
        $newNode = $xmlArticle.SelectSingleNode("//ARTIKELLISTE")
        $node = $xml.ImportNode($newNode, $true)
        $xml.DocumentElement.AppendChild($node) | Out-Null


        if ($path) {
            Format-Xml -xmlString $xml.OuterXml -pathOut $path
        } else {
            [string]$result= (Format-Xml -xmlString $xml.OuterXml)
        }
    }

    End {
        Get-CurrentVariables -InitialVariables $initialVariables -Debug:$DebugPreference
        Return $result
    }
    # Test: Export-ArticleToXml -filter "ArtNummer>='1000' and ArtNummer<='1100'" -customerGroups 'HA,HB,HC' -udl 'C:\temp\Eulanda_1 Eulanda.udl' -path "$(Get-DesktopDir)\ARTICLE.xml"
    # Test: Export-ArticleToXml -filter "ArtNummer>='1000' and ArtNummer<='1100'" -customerGroups 'HA,HB,HC' -udl 'C:\temp\Eulanda_1 Eulanda.udl'
}

function Export-DeliveryToXml {
    [CmdletBinding()]
    param(
        [parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateId -id $_ })]
        [int]$deliveryId
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateNo -no $_ })]
        [int]$deliveryNo
        ,
        [Parameter(Mandatory = $false)]
        [switch]$includeEmpty
        ,
        [Parameter(Mandatory = $false)]
        [string[]]$sql = $(
            if ($deliveryId) {
                Get-DeliverySql -deliveryId $deliveryId
            } else {
                Get-DeliverySql -deliveryNo $deliveryNo
            }
        )
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidatePathXML -path $_  })]
        [string]$path
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateConn -conn $_  })]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidatePathUDL -path $_  })]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateConnStr -connStr $_ })]
        [string]$connStr
    )

    begin {
        Write-Verbose -Message ((Get-ResStr 'STARTING_FUNCTION') -f $myInvocation.Mycommand)
        Test-ValidateSingle -validParams (Get-SingleDeliveryKeys) @PSBoundParameters
        Test-ValidateSingle -validParams (Get-SingleConnection) @PSBoundParameters
        New-Variable -Name 'myConn' -Scope 'Private' -Value ($null)
        New-Variable -Name 'newNode' -Scope 'Private' -Value ($null)
        New-Variable -Name 'node' -Scope 'Private' -Value ($null)
        New-Variable -Name 'paramsDelivery' -Scope 'Private' -Value ([System.Collections.Hashtable]@{})
        New-Variable -Name 'xml' -Scope 'Private' -Value ($null)
        New-Variable -Name 'xmlDelivery' -Scope 'Private' -Value ($null)
        New-Variable -Name 'xmlMetadata' -Scope 'Private' -Value ($null)
        New-Variable -Name 'result' -Scope 'Private' -Value ([string]'')
        $initialVariables = Get-CurrentVariables -Debug:$DebugPreference
    }

    process {
        $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr
        $paramsDelivery = Get-UsedParameters -validParams (Get-SingleDeliveryKeys) -boundParams $PSBoundParameters

        # **************************************************************************
        # Create a raw version for each database section
        # **************************************************************************

        # XML RAW for Root
        [xml]$xml = Get-XmlEulandaRoot

        # XML RAW for Metadata
        [xml]$xmlMetadata = Get-XmlEulandaMetadata

        # XML RAW for Delivery
        [xml]$xmlDelivery = Get-XmlEulandaDelivery @paramsDelivery -sql $sql -includeEmpty:$includeEmpty -conn $myConn


        # **************************************************************************
        # Assemble the individual XML nodes into an XML message.
        # **************************************************************************

        $newNode = $xmlMetadata.SelectSingleNode("//METADATA")
        $node = $xml.ImportNode($newNode, $true)
        $xml.DocumentElement.AppendChild($node) | Out-Null

        if ($xmlDelivery) {
            $newNode = $xmlDelivery.SelectSingleNode("//LIEFERSCHEINLISTE")
            $node = $xml.ImportNode($newNode, $true)
            $xml.DocumentElement.AppendChild($node) | Out-Null
        }

        if ($path) {
            Format-Xml -xmlString $xml.OuterXml -pathOut $path
        } else {
            [string]$result= (Format-Xml -xmlString $xml.OuterXml)
        }
    }

    end {
        Get-CurrentVariables -InitialVariables $initialVariables -Debug:$DebugPreference
        Return $result
    }
    # Test: Export-DeliveryToXml -deliveryNo 430952 -includeEmpty -udl 'C:\temp\Eulanda_1 JohnDoe.udl' -debug
}
function Export-PropertyToXml {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $false)]
        [string]$breadcrumbRoot = $(Throw ((Get-ResStr 'PARAM_MANDATORY_MISSED') -f 'breadcrumbRoot', $myInvocation.Mycommand))
        ,
        [Parameter(Mandatory = $false)]
        [switch]$noEmptyPropertyTree
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateMapping -strValue $_ -mapping (Get-MappingTablename) })]
        [string]$tablename = $(Throw ((Get-ResStr 'PARAM_MANDATORY_MISSED') -f 'tablename', $myInvocation.Mycommand))
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidatePathXML -path $_  })]
        [string]$path
        ,
        [Parameter(Mandatory = $false)]
        [AllowNull()]
        [ValidateScript({ Test-ValidateConn -conn $_  })]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidatePathUDL -path $_  })]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateConnStr -connStr $_ })]
        [string]$connStr
    )

    begin {
        Write-Verbose -Message ((Get-ResStr 'STARTING_FUNCTION') -f $myInvocation.Mycommand)
        Test-ValidateSingle -validParams (Get-SingleConnection) @PSBoundParameters
        New-Variable -Name 'myConn' -Scope 'Private' -Value ($null)
        New-Variable -Name 'newNode' -Scope 'Private' -Value ($null)
        New-Variable -Name 'node' -Scope 'Private' -Value ($null)
        New-Variable -Name 'xmlMetadata' -Scope 'Private' -Value ($null)
        New-Variable -Name 'xmlPropertyTree' -Scope 'Private' -Value ($null)
        New-Variable -Name 'result' -Scope 'Private' -Value ('')
        $initialVariables = Get-CurrentVariables -Debug:$DebugPreference
    }

    process {
        $myConn = Get-Conn -conn $conn -udl $udl -connStr $connStr
        $tablename = Test-ValidateMapping -strValue $tablename -mapping (Get-MappingTablename)

        # XML RAW for Root
        [xml]$xml = Get-XmlEulandaRoot

        # XML RAW for Metadata
        [xml]$xmlMetadata = Get-XmlEulandaMetadata

        # XML RAW for PropertyTree
        if ($breadcrumbRoot) {
            [xml]$xmlPropertyTree = Get-XmlEulandaProperty -breadcrumbRoot $breadcrumbRoot -tablename $tablename -conn $myConn
        } else {
            [xml]$xmlPropertyTree = "<MERKMALBAUM><$($tablename.ToUpper()) /></MERKMALBAUM>"
        }

        if ($noEmptyPropertyTree -and
            $xmlPropertyTree.Merkmalbaum.ChildNodes.Count -eq 1 -and
            $xmlPropertyTree.Merkmalbaum.ChildNodes[0].IsEmpty) {
                [xml]$xmlPropertyTree = $null
        }

        $newNode = $xmlMetadata.SelectSingleNode("//METADATA")
        $node = $xml.ImportNode($newNode, $true)
        $xml.DocumentElement.AppendChild($node) | Out-Null

        if ($xmlPropertyTree) {
            $newNode = $xmlPropertyTree.SelectSingleNode("//MERKMALBAUM")
            $node = $xml.ImportNode($newNode, $true)
            $xml.DocumentElement.AppendChild($node) | Out-Null
        }

        if ($path) {
            Format-Xml -xmlString $xml.OuterXml -pathOut $path
        } else {
            [string]$result = (Format-Xml -xmlString $xml.OuterXml)
        }

    }

    end {
        Get-CurrentVariables -InitialVariables $initialVariables -Debug:$DebugPreference
        Return $result
    }
    # Test: Export-PropertyToXml -breadcrumbRoot '\Shop' -tablename 'Article' -udl 'C:\temp\Eulanda_1 Eulanda.udl' -path "$(Get-DesktopDir)\PROPERTYTREE.xml"
    # Test: Export-PropertyToXml -breadcrumbRoot '\Produkte' -tablename 'Address' -udl 'C:\temp\Eulanda_1 Eulanda.udl'
}

Function Export-StockToXml {
    [CmdletBinding()]
    param(
        [parameter(Mandatory = $false)]
        [string[]]$filter
        ,
        [parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateMapping -strValue $_ -mapping (Get-MappingArticleKeys) })]
        [string]$alias = 'articleNo'
        ,
        [parameter(Mandatory = $false)]
        [int]$qtyStatic
        ,
        [parameter(Mandatory = $false)]
        [string]$warehouse
        ,
        [parameter(Mandatory = $false)]
        [switch]$legacy
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidatePathXML -path $_  })]
        [string]$path
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateConn -conn $_  })]
        $conn
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidatePathUDL -path $_  })]
        [string]$udl
        ,
        [Parameter(Mandatory = $false)]
        [ValidateScript({ Test-ValidateConnStr -connStr $_ })]
        [string]$connStr
    )