
<#
.SYNOPSIS
    Script for importing a large number of report and input form files into Docusnap

.DESCRIPTION
    The script manages to prepare docusnap report files (.MRT) for usage in docusnap. It copies files to the user specified Settings-Directory and modifies the existing Repositry XML file with new information. Input forms are copied to the DataEdit folder. Existing files are copied to a backup directory.

.PARAMETER
    -Verbose    - Displays additional information on the individual steps of the script.
    -ImportEN   - Imports german reports for usage within EN UI. 
    -Update     - If a report or input form file already exists, both, meta information and file will be overwritten. Default behavior without this parameter is to one time ask the user to decide.
    -NoDetect   - Per default the script tries to automatically detect docusnap settings by searching and reading a configuration file. Set this parameter to manually specify parameters required.

.EXAMPLE
    Example of how to use the script:
    PS C:\> .\Import.ps1

.NOTES
    Company:           Docusnap GmbH
    Version:           3.1.5
    Created on         19.07.2024
    Last Change:       25.02.2026
    Contact:           support@docusnap.com
    Website:           https://docusnap.com
#>

Param([switch]$Verbose=$false, [switch]$ImportEN=$false, [switch]$Update, [switch]$NoDetect, [switch]$PrintSql)
Set-StrictMode -Version 2

#region functions
Function PromptForXmlFile([String]$mTitle = "Please select the xml file required", [String]$mInitialDirectory = "") {
    $null = Add-Type -AssemblyName System.Windows.Forms -ErrorAction:Stop
    $mDialog = New-Object System.Windows.Forms.OpenFileDialog
    $mDialog.Title = $mTitle
    $mDialog.InitialDirectory = $mInitialDirectory
    $mDialog.Filter = "XML Files (*.xml)|*.xml"
    $mDialog.Multiselect = $false

    if ($mDialog.ShowDialog() -eq [System.Windows.Forms.DialogResult]::OK) {
        return $mDialog.FileName
    } else {
        return $null
    }
}
Function PromptForFolder([String]$mDescription,[String]$mInitialDirectory){
    $null = Add-Type -AssemblyName System.Windows.Forms -ErrorAction:Stop
    $mAssembly = [System.Reflection.Assembly]::GetAssembly([System.Windows.Forms.OpenFileDialog])
    $mOpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
    $mOpenFileDialog.AddExtension = $false
    $mOpenFileDialog.CheckFileExists = $false
    $mOpenFileDialog.DereferenceLinks = $true
    $mOpenFileDialog.Filter = "Folders|`n"
    $mOpenFileDialog.Multiselect = $false
    $mOpenFileDialog.Title = $mDescription
    $mOpenFileDialog.InitialDirectory = $mInitialDirectory
    $mOpenFileDialogType = $mOpenFileDialog.GetType()
    $mFileDialogInterfaceType = $mAssembly.GetType('System.Windows.Forms.FileDialogNative+IFileDialog')
    $mIFileDialog = $mOpenFileDialogType.GetMethod('CreateVistaDialog',@('NonPublic','Public','Static','Instance')).Invoke($mOpenFileDialog,$null)
    $null = $mOpenFileDialogType.GetMethod('OnBeforeVistaDialog',@('NonPublic','Public','Static','Instance')).Invoke($mOpenFileDialog,$mIFileDialog)
    [uint32]$mPickFoldersOption = $mAssembly.GetType('System.Windows.Forms.FileDialogNative+FOS').GetField('FOS_PICKFOLDERS').GetValue($null)
    $mFolderOptions = $mOpenFileDialogType.GetMethod('get_Options',@('NonPublic','Public','Static','Instance')).Invoke($mOpenFileDialog,$null) -bor $mPickFoldersOption
    $null = $mFileDialogInterfaceType.GetMethod('SetOptions',@('NonPublic','Public','Static','Instance')).Invoke($mIFileDialog,$mFolderOptions)
    $mVistaDialogEvent = [System.Activator]::CreateInstance($mAssembly.FullName,'System.Windows.Forms.FileDialog+VistaDialogEvents',$false,0,$null,$mOpenFileDialog,$null,$null).Unwrap()
    [uint32]$mAdviceCookie = 0
    $mAdvisoryParameters = @($mVistaDialogEvent,$mAdviceCookie)
    $mAdviseResult = $mFileDialogInterfaceType.GetMethod('Advise',@('NonPublic','Public','Static','Instance')).Invoke($mIFileDialog,$mAdvisoryParameters)
    $mAdviceCookie = $mAdvisoryParameters[1]
    $mResult = $mFileDialogInterfaceType.GetMethod('Show',@('NonPublic','Public','Static','Instance')).Invoke($mIFileDialog,[System.IntPtr]::Zero)
    $null = $mFileDialogInterfaceType.GetMethod('Unadvise',@('NonPublic','Public','Static','Instance')).Invoke($mIFileDialog,$mAdviceCookie)
    if ($mResult -eq [System.Windows.Forms.DialogResult]::OK) {
        $mFileDialogInterfaceType.GetMethod('GetResult',@('NonPublic','Public','Static','Instance')).Invoke($mIFileDialog,$null)
    }
    return $mOpenFileDialog.FileName
}
Function GetScriptPath(){
    Write-Verbose -Message "Get the current script Path."
    
    Try{
        #for use in PS1
        $mInvocation = (Get-Variable MyInvocation -Scope 1).Value
        $mScriptPath = Split-Path $mInvocation.MyCommand.Path
        if($mScriptPath -eq $null){
            #For ISE debug mode
            $mScriptPath = Split-Path $psISE.CurrentFile.FullPath
        }
        Write-Verbose -Message "Current Script Path is $mScriptPath."
    }Catch{
        #for use in EXE
        $mScriptPath = Split-Path -Parent -Path ([Environment]::GetCommandLineArgs()[0])
        Write-Verbose -Message "Current Script Path is $mScriptPath."
    }finally{
        if($mScriptPath -eq $null){
            Write-Host -ForegroundColor DarkRed "Could not determine the current script path. Error: $($_.Exception.Message)."
        }
    }
    return $mScriptPath
}
Function WriteLog([String]$mFile,[String]$mLogText,[Int32]$mMaxSizeKB = [Int32]100,[Int32]$mMaxHistory = [Int32]5){

    Try
    {
        $mTimeStamp = Get-Date -Format G -ErrorAction:Stop
        $mHistoryTimeStamp = "$(Get-Date -Format "yyyyMMddHHmmss" -ErrorAction:Stop)$($(Get-Date -ErrorAction:Stop).Millisecond)"

        If(!(Test-Path $mFile -ErrorAction:Stop)){
            #Log file does not exist yet
            $mExistingFile = New-Item -ItemType File $mFile -Force -ErrorAction:Stop
            Write-Verbose -Message "Log file $mFile created for the first time."
        }else{
            #Log file already exists - check its size
            $mExistingFile = Get-Item -Path $mFile -ErrorAction:Stop
            if(($mExistingFile.Length / 1024) -gt $mMaxSizeKB){
                #file size exceeded. History management
                Write-Verbose -Message "Log file $mFile size exceeded."
                Try{
                    #generate the name of the respective history file
                    $mHistoryFileName = "$($mExistingFile.BaseName)_$mHistoryTimeStamp$($mExistingFile.Extension)"
                    $mHistoryFile = Rename-Item -Path $mExistingFile.FullName -NewName $mHistoryFileName -ErrorAction:SilentlyContinue
                    Write-Verbose -Message "Exceeded file converted to history file $mHistoryFileName."

                    #delete oldest history files
                    $mAllHistoryFiles = @(Get-ChildItem -Path $mExistingFile.Directory.FullName -ErrorAction:Stop | Where-Object{$_.name -match "$($mExistingFile.BaseName)_\d{14}"} | Sort-Object -Property LastWriteTime -ErrorAction:Stop)
                    if($mAllHistoryFiles.Count -gt $mMaxHistory){
                        #remove old history files
                        $mHistoryFilesToDelete = $mAllHistoryFiles.Count - $mMaxHistory
                        Write-Verbose -Message "$mHistoryFilesToDelete history files to delete..."
                        
                        for($mIndex = 0; $mIndex -le $mHistoryFilesToDelete - 1; $mIndex++){
                            #Write-Verbose -Message "Try to delete history file $($mAllHistoryFiles[$mIndex])"
                            $null = Remove-Item -Path $mAllHistoryFiles[$mIndex] -Force -Confirm:$false -ErrorAction:Stop
                        }
                        Write-Verbose -Message "$mHistoryFilesToDelete history files have been deleted. $mMaxHistory files remaining."
                    }

                    #Create a new, empty file
                    $null = New-Item -ItemType File $mFile -Force -ErrorAction:Stop
                }catch{
                    Write-Host -ForegroundColor DarkRed "Could not manage the file history. Error: $($_.Exception.Message)."
                }
            }
        }
        $null = Add-Content -Path $mFile -Value "$mTimeStamp ; $mLogText" -ErrorAction:Stop -WarningAction:SilentlyContinue
        Write-Verbose -Message "`"$mLogText`" logged to file $mFile."
    }
    Catch
    {
        Write-Host -ForegroundColor Red "Could not write to Log file: $($_.Exception.Message)."
    }
}
Function GetScriptName(){
    Write-Verbose -Message "Get the current script name."
    
    Try{
        #for use in PS1
        $mInvocation = (Get-Variable MyInvocation -Scope 1).Value
        $mScriptName = $mInvocation.MyCommand.Name
        if($mScriptName -eq $null -or $mScriptName.Length -eq 0){
            #For ISE debug mode
            $mScriptName = Split-Path $psISE.CurrentFile.FullPath -Leaf
        }
        Write-Verbose -Message "Current Script name is $mScriptName."
    }Catch{
        Write-Host -ForegroundColor DarkRed "Could not determine the current script name. Error: $($_.Exception.Message)."
        $mScriptName = $null
    }
    Write-Verbose -Message "Current script name is $mScriptName"
    return $mScriptName
}
Function DS_CreateEmptyRepository($mPath){
    Try{
        if(!(Test-Path -Path $mPath)){
            #Create new XML file
            $mContent = '<?xml version="1.0" encoding="utf-8"?>
<DcReportSerializer xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ReportCollection />
</DcReportSerializer>'
            
            $null = Set-Content -PassThru $mPath -Value $mContent -Force -ErrorAction:Stop
        }else{
            #File exists
            throw "The file $mPath already exists!"
        }
        return $true
    }catch{
        return $false
    }
}
Function AskYesOrNo($mTitle = 'Please choose...', $mText) {
    $null = Add-Type -AssemblyName System.Windows.Forms -ErrorAction:Stop

    # Helper function to measure title width in pixels
    $mGraphics = [System.Drawing.Graphics]::FromHwnd([IntPtr]::Zero)
    $mTitleSize = $mGraphics.MeasureString($mTitle, [System.Drawing.SystemFonts]::DefaultFont)
    $mTitleWidth = [math]::Ceiling($mTitleSize.Width) + 80  # some padding

    # Configure base form
    $mForm = New-Object System.Windows.Forms.Form
    $mForm.Text = $mTitle
    $mForm.StartPosition = "CenterScreen"
    $mForm.AutoSize = $true
    $mForm.AutoSizeMode = 'GrowAndShrink'
    $mForm.FormBorderStyle = 'FixedDialog'
    $mForm.MaximizeBox = $false
    $mForm.MinimizeBox = $false

    # TableLayoutPanel for structure
    $mLayout = New-Object System.Windows.Forms.TableLayoutPanel
    $mLayout.Dock = 'Fill'
    $mLayout.ColumnCount = 1
    $mLayout.RowCount = 2
    $mLayout.AutoSize = $true
    $mLayout.AutoSizeMode = 'GrowAndShrink'
    $mLayout.Padding = New-Object System.Windows.Forms.Padding(10)
    [void]$mLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::AutoSize)))
    [void]$mLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::AutoSize)))

    # Label
    $mLabel = New-Object System.Windows.Forms.Label
    $mLabel.Text = $mText
    $mLabel.Dock = 'Top'
    $mLabel.AutoSize = $true
    $mLabel.MaximumSize = New-Object System.Drawing.Size(600, 0)
    $mLabel.Margin = '0,0,0,15'
    [void]$mLayout.Controls.Add($mLabel, 0, 0)

    # Buttons centered in FlowLayoutPanel
    $mButtonPanel = New-Object System.Windows.Forms.FlowLayoutPanel
    $mButtonPanel.FlowDirection = 'LeftToRight'
    $mButtonPanel.Dock = 'Top'
    $mButtonPanel.AutoSize = $true
    $mButtonPanel.AutoSizeMode = 'GrowAndShrink'
    $mButtonPanel.Anchor = 'None'
    $mButtonPanel.WrapContents = $false
    $mButtonPanel.Margin = '0,0,0,5'
    $mButtonPanel.Padding = '0,0,0,0'

    # Yes button first
    $mYesButton = New-Object System.Windows.Forms.Button
    $mYesButton.Text = "Yes"
    $mYesButton.Width = 80
    $mYesButton.Add_Click({
        $global:result = $true
        $mForm.Close()
    })
    [void]$mButtonPanel.Controls.Add($mYesButton)

    # No button
    $mNoButton = New-Object System.Windows.Forms.Button
    $mNoButton.Text = "No"
    $mNoButton.Width = 80
    $mNoButton.Add_Click({
        $global:result = $false
        $mForm.Close()
    })
    [void]$mButtonPanel.Controls.Add($mNoButton)

    [void]$mLayout.Controls.Add($mButtonPanel, 0, 1)
    $mForm.Controls.Add($mLayout)

    $mForm.AcceptButton = $mYesButton
    $mForm.CancelButton = $mNoButton

    Register-ObjectEvent -InputObject $mForm -EventName Shown -Action {
        if ($mForm.Width -lt $mTitleWidth) {
            $mForm.Width = $mTitleWidth
        }
    } | Out-Null

    [void]$mForm.ShowDialog()
    return $global:result
}
Function DS_ImportReports($mSourceRepositoryFile, $mTargetRepositoryFile, $mImportEN=$false, $mUpdateReports, [string]$mReportsAuthor='Docusnap Consulting'){
    Try{
        $mSourceRepositoryFile = Get-Item -Path $mSourceRepositoryFile -Force -ErrorAction:Stop
        $mNewReportFiles = Get-ChildItem -Path $($mSourceRepositoryFile.Directory.FullName) -Recurse -Include '*.mrt'
        $mFileCounter = 0
        $mUpdateAskedOnce = $false
        $mTimeStamp = $(Get-Date -Format 'yyyyMMddHHmmss') #For backup folder

         #Check local repository file
        if(!(Test-Path -Path $mTargetRepositoryFile)){
            Write-Verbose -Message "Settings directory does not contain a repository file. Creating a default one..."
            if(&DS_CreateEmptyRepository $mTargetRepositoryFile){
                Write-Verbose "Report repository created: $mTargetRepositoryFile"
            }else{
                throw "Could not create new repository file."
            }
        }

        #Search for new MRT files
        foreach($mCurrentNewReportFile in $mNewReportFiles){
            $mFileCounter++
            $mStartMessage = "Preparing report file $mFileCounter / $($mNewReportFiles.count) '$($mCurrentNewReportFile.Name)' for import..."
            Write-Host $mStartMessage

            #region MRT file
            Try{
                #Prepare report properties
                if($mCurrentNewReportFile.Name -like 'DE_*' -and $mImportEN){
                    #Convert German reports for usage in EN UI
                    $mCurrentNewReportFileTargetName = $mCurrentNewReportFile.Name.Replace('DE_','EN_')
                    $mCurrentNewReportFileLanguageID = 1 #English 
                }else{
                    #do not modify 
                    $mCurrentNewReportFileTargetName = $mCurrentNewReportFile.Name
                    if($mCurrentNewReportFileTargetName -like 'DE_*'){
                        $mCurrentNewReportFileLanguageID = 0 #German
                    }else{
                        $mCurrentNewReportFileLanguageID = 1 #English
                    }
                }
                $mCurrentNewReportFileTargetPath = "$((Get-Item -Path $mTargetRepositoryFile).Directory.FullName)\$mCurrentNewReportFileTargetName"

                #Check if MRT file has changed
                if(!$(Test-Path -Path $mCurrentNewReportFileTargetPath)){
                    #target file does not exist
                    $null = Copy-Item -Path $mCurrentNewReportFile.FullName -Destination $mCurrentNewReportFileTargetPath -Force -ErrorAction:Stop
                    Write-Verbose "New report file $mCurrentNewReportFileTargetName has been copied."
                }elseif($(Get-FileHash -Path $mCurrentNewReportFile.FullName).Hash -eq $(Get-FileHash -Path $mCurrentNewReportFileTargetPath).Hash){
                    #target file already exists but hasn't changed - nothing to do
                    Write-Verbose "Report file $mCurrentNewReportFileTargetName is up-to-date. No changes made."
                }else{
                    #target file exists and differs
                    if(-not $mUpdateReports -and -not $mUpdateAskedOnce){
                        #Ask user once for update permission
                        if(&AskYesOrNo -mTitle 'Overwrite reports?' -mText 'One or more report files already exist and will be overwritten to ensure the customization functions correctly. To prevent data loss, all overwritten files will be backed up in a separate directory. Press Yes to backup and overwrite report files.'){
                            $mUpdateReports = $true
                        }
                        $mUpdateAskedOnce = $true
                    }

                    if($mUpdateReports){
                        #Create backup of existing report file
                        #Create new folder
                        $mBackupFolderPath = "$((Get-Item -Path $mTargetRepositoryFile -ErrorAction:Stop).Directory.FullName)\Backup\$mTimeStamp"
                        $null = New-Item -Path $mBackupFolderPath -ItemType Directory -ErrorAction:Stop -Force
                        Write-Verbose -Message "Creating backup of the existing report file."
                        $null = Move-Item -Path "$((Get-Item -Path $mTargetRepositoryFile).Directory.FullName)\$mCurrentNewReportFileTargetName" -Destination "$mBackupFolderPath\$mCurrentNewReportFileTargetName" 
                        #Copy MRT file to settings/reporting directory
                        $null = Copy-Item -Path $mCurrentNewReportFile.FullName -Destination $mCurrentNewReportFileTargetPath -Force -ErrorAction:Stop
                        Write-Verbose -Message "Existing report file $mCurrentNewReportFileTargetName has been updated."
                    }
                }
            }catch{
                throw "The report file $mCurrentNewReportFileTargetName could not be copied! Error: $($_.Exception.Message)."
            }
            #endregion
            #region Repository
                  
            Try{
                [xml]$mNewReportXML = Get-Content $mCurrentNewReportFile

                #Read Repository files
                [xml]$mTargetRepositoryXML = Get-Content -Path $mTargetRepositoryFile
                [xml]$mSourceRepositoryXML = Get-Content -Path $mSourceRepositoryFile

                #Modify source node 
                $mSourceDCDataNode = $mSourceRepositoryXML.SelectNodes('//DcReportData') | Where-Object{$_.FileName -eq $mCurrentNewReportFileTargetName}
                $mSourceDCDataNode.Language = $mCurrentNewReportFileLanguageID.ToString()
                $mSourceDCDataNode.FileName = $mCurrentNewReportFileTargetName.ToString()
                $mSourceDCDataNode.Author = $mReportsAuthor

                #Search for existing reports by MRT filename
                Write-Verbose "Checking report metadata..."
                $mTargetDCDataNode = $mTargetRepositoryXML.SelectNodes('//DcReportData') | Where-Object{$_ -ne $null -and -not $_.IsEmpty -and $_.FileName -eq $mCurrentNewReportFileTargetName}
                
                if($mTargetDCDataNode -ne $null){
                    #Compare
                    if($mTargetDCDataNode.InnerXml -eq $mSourceDCDataNode.InnerXml){
                        Write-Verbose "Report metadata for $mCurrentNewReportFileTargetName not changed."
                        continue #next Report file
                    }else{
                        Write-Verbose "Report metadata for $mCurrentNewReportFileTargetName has changed."
                        #Delete old node
                        Write-Verbose "Removing obsolete report metadata."
                        $null = $mTargetRepositoryXML.DcReportSerializer.ReportCollection.RemoveChild($mTargetDCDataNode)
                    }
                }
                
                #Copy new information into local repository
                Write-Verbose -Message "Adding new report metadata."
                $mNewDCDataNode    = $mTargetRepositoryXML.ImportNode($mSourceDCDataNode,$true)
                $null = ($mTargetRepositoryXML.DcReportSerializer.ChildNodes | Where-Object{$_.Name -eq 'ReportCollection'}).AppendChild($mNewDCDataNode)
                $mTargetRepositoryXML.Save($mTargetRepositoryFile)
                Write-Verbose -Message "Metadata updated."

            }catch{
                throw "The report $($mCurrentNewReportFile.Name) could not be imported. Error: $($_.Exception.Message)."
            }
            #endregion
    
        }
        return $true
    }catch{
        Write-Verbose -Message "Failed to import reports. Error: $($_.Exception.Message)."
        return $false
    }
   
} 
Function DS_ImportForms($mNewFormsRoot, $mSettingsFolder, $mUpdateForms = $false) {
    Try {
        $mFileCounter = 0
        $mUpdateAskedOnce = $false
        $mTimeStamp = $(Get-Date -Format 'yyyyMMddHHmmss') #For backup folder

        if (Test-Path -Path $mNewFormsRoot) {
            $mNewFormFiles = Get-ChildItem -Path $mNewFormsRoot -Filter '*.deu' -ErrorAction:Stop
            Foreach ($mCurrentNewFormFile in $mNewFormFiles) {
                $mFileCounter++
                $mStartMessage = "Preparing input form file $mFileCounter / $($mNewFormFiles.count) '$($mCurrentNewFormFile.Name)' for import..."
                Write-Host $mStartMessage
                $mTargetFormFilePath = "$mSettingsFolder\DataEdit\$($mCurrentNewFormFile.Name)"
                if (!(Test-Path -Path $mTargetFormFilePath)) {
                    # Target file does not exist
                    $null = Copy-Item -Path $mCurrentNewFormFile.FullName -Destination $mTargetFormFilePath -Force -ErrorAction:Stop
                } elseif ((Get-FileHash -Path $mTargetFormFilePath).Hash -eq (Get-FileHash -Path $mCurrentNewFormFile.FullName).Hash) {
                    # Target file already exists but hasn't changed - nothing to do
                } else {
                    # Target file exists and differs
                    if (-not $mUpdateForms -and -not $mUpdateAskedOnce) {
                        # Ask user once for update permission
                        if (&AskYesOrNo -mTitle 'Overwrite editor forms?' -mText 'Several editor forms were detected at the destination and will be overwritten to ensure the customization functions correctly. To prevent data loss, all overwritten files will be backed up in a separate directory. Press Yes to backup and overwrite editor forms.') {
                            $mUpdateForms = $true
                        }
                        $mUpdateAskedOnce = $true
                    }

                    if ($mUpdateForms -and $mUpdateAskedOnce) {
                        #Create backup of existing input form file
                        #Create new folder
                        $mBackupFolderPath = "$((Get-Item -Path $mTargetFormFilePath -ErrorAction:Stop).Directory.FullName)\Backup\$mTimeStamp"
                        $null = New-Item -Path $mBackupFolderPath -ItemType Directory -ErrorAction:Stop -Force
                        Write-Verbose -Message "Creating backup of the existing form file."
                        $null = Move-Item -Path $mTargetFormFilePath -Destination "$mBackupFolderPath\$($mCurrentNewFormFile.Name)"
                        #Copy form file to DataEdit directory
                        $null = Copy-Item -Path $mCurrentNewFormFile.FullName -Destination $mTargetFormFilePath -Force -ErrorAction:Stop
                        Write-Verbose -Message "Input form file $($mCurrentNewFormFile.Name) imported."
                    } else {
                        Write-Verbose -Message "Skipping file $($mCurrentNewFormFile.Name) as it already exists in $mSettingsFolder`\DataEdit and updating is not allowed."
                    }
                }
            }
            return $true
        } else {
            Write-Verbose "No forms found to import."
            return $true
        }
    } catch {
        Write-Error "Failed to import input forms. Error: $($_.Exception.Message)."
        return $false
    }
}
Function DS_GetDSInstallInfo(){
    #Find the latest Docusnap install directory based on DocusnapServer.exe (local installation)
    $mReturnObject = [PSCustomObject]@{
        InstallPath = $null
        Version = $null
        MajorVersion = $null
    }

    $mDSServices = Get-WmiObject -Class Win32_Service -Filter "Name LIKE 'Docusnap%Server'" -ErrorAction:Stop
    Foreach($mCurrentDSService in $mDSServices){
        $mCurrentDSServiceFilePath = $mCurrentDSService.PathName.Replace('"','')
        if(Test-Path -Path $mCurrentDSServiceFilePath){
            $mCurrentDSServiceFile = Get-Item -Path $mCurrentDSServiceFilePath -ErrorAction:Stop
            if(($mReturnObject.InstallPath -eq $null) -or ($mReturnObject.Version -le $mCurrentDSServiceFile.VersionInfo.ProductVersion)){
                #This is the latest version found yet
                $mReturnObject.InstallPath = $mCurrentDSServiceFile.Directory.FullName
                $mReturnObject.Version = $mCurrentDSServiceFile.VersionInfo.ProductVersion
            }
        }
    }

    if($mReturnObject.InstallPath -ne $null){
        Write-Verbose "Docusnap installation, version $($mReturnObject.Version) found in directory $($mReturnObject.InstallPath)"
    }else{
        #user defined paths
        $mReturnObject.InstallPath = &PromptForFolder 'Please select the Docusnap program installation directory' "$($env:ProgramFiles)\Docusnap 14"
        $mReturnObject.Version = $(Get-Item -Path "$($mReturnObject.InstallPath)\docusnap.exe").VersionInfo.ProductVersion
        Write-Verbose "User specified installation directory: $($mReturnObject.InstallPath), Version: $($mReturnObject.Version)"
    }
    if($mReturnObject.Version -match '\d+'){$mReturnObject.MajorVersion = $Matches[0]}
    return $mReturnObject
}
Function DS_GetConfigFile(){
    # Search configuration in DocusnapSettings.xml
    $mDSettingsFile = [xml](Get-Content 'c:\ProgramData\Docusnap\DocusnapSettings.xml' -ErrorAction:Stop)
    Try{
        $mConfigFilePath = $mDSettingsFile.SelectSingleNode('//AlternativeConfigFile').InnerText 
    }catch{
        $mConfigFilePath = $null
    }
    if($mConfigFilePath -eq $null -or $mConfigFilePath.length -eq 0 -or -not (Test-Path $mConfigFilePath)){
        #Search in user profile
        Write-Verbose "Configuration file not found via DocusnapSettings.xml"
        $mConfigFilePath = "$($env:APPDATA)\Docusnap\Configuration.xml"
        if(-not (Test-Path -Path $mConfigFilePath)){            
            #Ask for Docusnap Configuration file
            Write-Verbose "Configuration file not found in user profile. Prompt for user input."
            $null = Add-Type -AssemblyName System.Windows.Forms -ErrorAction:Stop
            $mOpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
            $mOpenFileDialog.Title = "Please select the Docusnap configuration file (e.g., Configuration.xml)."
            $mOpenFileDialog.Filter = "Docusnap Configuration (*.xml)|*.xml"
            if ($mOpenFileDialog.ShowDialog() -eq [System.Windows.Forms.DialogResult]::OK) {
                # Show the selected path
                $mConfigFilePath = $($mOpenFileDialog.FileName)
            }else{
                throw "The Docusnap configuration file containing the required parameters could not be found!"
            }

            if($mConfigFilePath -eq $null -or $mConfigFilePath.Length -eq 0 -or -not (Test-Path $mConfigFilePath)){
                Write-Verbose "Configuration file not found."
            }
        }
    }
    Write-Verbose "Return config file path: $mConfigFilePath"
    return $mConfigFilePath
}
Function DS_GetDSSSettingsPath($mConfigFilePath){
    try {
        if($mConfigFilePath -eq $null -or $mConfigFilePath.Length -eq 0 -or -not (Test-Path $mConfigFilePath)){
            $mConfigFilePath = &DS_GetConfigFile
        }

        #Extract information
        Write-Verbose "Try to extract information from configuration file $mConfigFilePath"
        $mConfiguration = [xml](Get-Content $mConfigFilePath)
        $mTeamsSettings = $mConfiguration.DcConfig._sNetworkSettingsPath
        $mLocalSettings = $mConfiguration.DcConfig._sLocalSettingsPath
        if($mTeamsSettings -ne $null -and $mTeamsSettings.length -gt 0 -and (Test-Path $mTeamsSettings)){
            Write-Verbose "Teams settings path $mTeamsSettings found in configuration file $mConfigFilePath"
            return $mTeamsSettings
        }elseif($mLocalSettings -ne $null -and $mLocalSettings.length -gt 0 -and (Test-Path $mLocalSettings)){
            Write-Verbose "Local settings path $mTeamsSettings found in configuration file $mConfigFilePath"
            return $mLocalSettings
        }else{
            throw "Neither team- nor local settings path was found in configuration file $mConfigFilePath."
        }

    }catch{
        Write-Verbose "Ask user for settings path due to: $($_.Exception.Message)."
        return &PromptForFolder 'Please enter the directory path for the "Teams Settings".' 'C:\Docusnap'
    }
}
Function DS_ConnectDB($mConfigFilePath) {
    try {
        if($mConfigFilePath -eq $null){
            $mConfigFilePath = &DS_GetConfigFile
        }

        Try {
            $mConfiguration = [xml](Get-Content $mConfigFilePath)
            $mServer = $mConfiguration.DcConfig._sSQLServer
            $mDatabase = $mConfiguration.DcConfig._sSQLDatabase
            #$mSqlAuthMode = $mConfiguration.DcConfig._lSQLAuthenticationMode # 1 = Windows, 2 = SQL
        } catch {
            # Ask user for database connection data
            Add-Type -AssemblyName System.Windows.Forms

            # Create form
            $mForm = New-Object System.Windows.Forms.Form
            $mForm.Text = "Docusnap Database Connection"
            $mForm.Size = New-Object System.Drawing.Size(400, 300)
            $mForm.StartPosition = "CenterScreen"

            # Tags for result values
            $mForm.Tag = @{
                Server = ""
                Database = ""
                AuthType = ""
            }

            # Labels and textboxes
            $mLabelInstance = New-Object System.Windows.Forms.Label
            $mLabelInstance.Text = "Server(\Instance):"
            $mLabelInstance.Location = New-Object System.Drawing.Point(10, 20)
            $mLabelInstance.Size = New-Object System.Drawing.Size(120, 20)
            $mForm.Controls.Add($mLabelInstance)

            $mTextInstance = New-Object System.Windows.Forms.TextBox
            $mTextInstance.Location = New-Object System.Drawing.Point(140, 20)
            $mTextInstance.Size = New-Object System.Drawing.Size(200, 20)
            $mForm.Controls.Add($mTextInstance)

            $mLabelDBName = New-Object System.Windows.Forms.Label
            $mLabelDBName.Text = "Database name:"
            $mLabelDBName.Location = New-Object System.Drawing.Point(10, 60)
            $mLabelDBName.Size = New-Object System.Drawing.Size(120, 20)
            $mForm.Controls.Add($mLabelDBName)

            $mTextDBName = New-Object System.Windows.Forms.TextBox
            $mTextDBName.Location = New-Object System.Drawing.Point(140, 60)
            $mTextDBName.Size = New-Object System.Drawing.Size(200, 20)
            $mForm.Controls.Add($mTextDBName)

            # Radio buttons for authentication
            $mLabelAuth = New-Object System.Windows.Forms.Label
            $mLabelAuth.Text = "Authentication:"
            $mLabelAuth.Location = New-Object System.Drawing.Point(10, 100)
            $mLabelAuth.Size = New-Object System.Drawing.Size(120, 20)
            $mForm.Controls.Add($mLabelAuth)

            $mRadioWindowsAuth = New-Object System.Windows.Forms.RadioButton
            $mRadioWindowsAuth.Text = "Windows Authentication"
            $mRadioWindowsAuth.Location = New-Object System.Drawing.Point(140, 100)
            $mRadioWindowsAuth.Size = New-Object System.Drawing.Size(200, 20)
            $mRadioWindowsAuth.Checked = $true
            $mForm.Controls.Add($mRadioWindowsAuth)

            $mRadioSQLAuth = New-Object System.Windows.Forms.RadioButton
            $mRadioSQLAuth.Text = "SQL Authentication"
            $mRadioSQLAuth.Location = New-Object System.Drawing.Point(140, 130)
            $mRadioSQLAuth.Size = New-Object System.Drawing.Size(200, 20)
            $mForm.Controls.Add($mRadioSQLAuth)

            # Buttons
            $mButtonOK = New-Object System.Windows.Forms.Button
            $mButtonOK.Text = "OK"
            $mButtonOK.Location = New-Object System.Drawing.Point(140, 180)
            $mButtonOK.Size = New-Object System.Drawing.Size(80, 30)
            $mForm.Controls.Add($mButtonOK)

            $mButtonCancel = New-Object System.Windows.Forms.Button
            $mButtonCancel.Text = "Cancel"
            $mButtonCancel.Location = New-Object System.Drawing.Point(260, 180)
            $mButtonCancel.Size = New-Object System.Drawing.Size(80, 30)
            $mForm.Controls.Add($mButtonCancel)

            # OK button event
            $mButtonOK.Add_Click({
                $mForm.Tag.AuthType = if ($mRadioWindowsAuth.Checked) { 1 } else { 2 }
                $mForm.Tag.Server = $mTextInstance.Text
                $mForm.Tag.Database = $mTextDBName.Text
                $mForm.DialogResult = [System.Windows.Forms.DialogResult]::OK
                $mForm.Close()
            })

            # Cancel button event
            $mButtonCancel.Add_Click({
                $mForm.DialogResult = [System.Windows.Forms.DialogResult]::Cancel
                $mForm.Close()
            })

            # Show form
            if ($mForm.ShowDialog() -eq [System.Windows.Forms.DialogResult]::OK) {
                $mServer = $mForm.Tag.Server
                $mDatabase = $mForm.Tag.Database
                $mSqlAuthMode = $mForm.Tag.AuthType
            } else {
                throw "No database connection data was provided."
            }
        }

        Try{
            # Windows Authentication
            $mConnectionString = "Server=$mServer;Database=$mDatabase;Integrated Security=True;"    

            # Test connection
            $mConnection = New-Object System.Data.SqlClient.SqlConnection($mConnectionString)
            $mConnection.Open()

            # check db_owner role            
            if($(&DS_SQL-Execute -mConnection $mConnection -mStatement "SELECT IS_ROLEMEMBER('db_owner') AS IsDbOwner;" -mReturnValues)[0].IsDBOwner -eq 1){
                # Windows user is db_owber
                return $mConnection
            }else{
                throw "Current windows user does not have db_owber access to database $mDatabase."
            }
        }catch{        
            # SQL Authentication - fallback method
            $mCredential = Get-Credential -Message "SQL Server authentication required. Please enter SQL user credentials."
            
            $mConnectionString = "Server=$mServer;Database=$mDatabase;User ID=$($mCredential.UserName);Password=""$($mCredential.GetNetworkCredential().Password.Replace('"','"""'))"";"
        
            # Test connection
            $mConnection = New-Object System.Data.SqlClient.SqlConnection($mConnectionString)
            $mConnection.Open()

            # check db_owner role
            if($(&DS_SQL-Execute -mConnection $mConnection -mStatement "SELECT IS_ROLEMEMBER('db_owner') AS IsDbOwner;" -mReturnValues)[0].IsDBOwner -eq 1){
                # swl user is db_owber
                return $mConnection
            }else{
                throw "Sql user does not have db_owber access to database $mDatabase."
            }
        }
    } catch {
        Write-Error "Failed to connect to the database. Error: $($_.Exception.Message)"
        return $null
    } 
}
Function DS_GetDocusnapAccounts($mConnection){
    $mStatement = "select *  from tAccounts where DSGUID !='11111111-2222-3333-4444-555555555555'"
    $mAccountInfos = @(&DS_SQL-Execute -mConnection $mConnection -mStatement $mStatement -mReturnValues)
    Write-Verbose "$($mAccountInfos.count) account(s) found."
    return $mAccountInfos
}
Function DS_SQL-Execute($mConnection, $mStatement, [Switch]$mReturnValues){
    Try{
        $mCommand = $mConnection.CreateCommand()
        $mCommand.CommandText = $mStatement
        Write-Verbose "Execute SQL statement '$mStatement'"
        $mReader = $mCommand.ExecuteReader()

        if($mReturnValues){
            $mColumns = @()
            for ($mColumnIndex = 0; $mColumnIndex -lt $mReader.FieldCount; $mColumnIndex++) {
                $mColumns += $mReader.GetName($mColumnIndex)
            }

            $mAllRows = @()
            while ($mReader.Read()) {
                $mResultRow = [PSCustomObject]@{}

                foreach ($mCurrentColumn in $mColumns) {
                    if($(($mResultRow | Get-Member) | Where-Object{$_.Name -eq $mCurrentColumn}) -ne $null){
                        #Property does already exist - new object
                        $mAllRows += $mResultRow
                        $mResultRow = [PSCustomObject]@{}
                    }
                    $mResultRow | Add-Member -MemberType NoteProperty -Name $mCurrentColumn -Value $mReader[$mCurrentColumn]
                }
                $mAllRows += $mResultRow
            }
            Write-Verbose "Return $($mAllRows.count) rows."
            return $mAllRows
        }else{
            return $true
        }
    }catch{
        Write-Host -ForegroundColor DarkRed "SQL Statement $mStatement failed. Error: $($_.Exception.Message)"
    }finally{
        If(-not $mReader.IsClosed){
            $mReader.Close() | Out-Null
        }
    }   
}
function UseCheckBoxSelection([System.String]$mTitle,[System.String]$mInfoText,[System.String[]]$mElements,[Switch]$mPreSelect=$true){
    Write-Host -ForegroundColor DarkGray "Function to create a list of checkboxes checkboxes for $($mElements.count) elements."
    
    Try{
        [void] [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
        [void] [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing") 
        
        # Build new Form object
        $mForm = New-Object System.Windows.Forms.Form 
        $mForm.Text = $mTitle
        $mForm.Size = New-Object System.Drawing.Size(500,350) 
        $mForm.StartPosition = "CenterScreen"
        
        # Behavior in case the ENTER Button gets pressed
        $mForm.KeyPreview = $True
        $mForm.Add_KeyDown(
            {
                if ($_.KeyCode -eq "Enter"){
                    $mForm.DialogResult = "OK";$mForm.Close()
                }
            }
        )
        
        # Behavior in case the ESCAPE Button gets pressed
        $mForm.Add_KeyDown(
            {
                if ($_.KeyCode -eq "Escape"){
                    $mForm.Close()
                }
            }
        )
        
        $mOkButton = New-Object System.Windows.Forms.Button
        $mOkButton.Size = New-Object System.Drawing.Size(73,23)
        $mOkButton.Text = "OK"
        $mOkButton.Add_Click({$mForm.DialogResult = "OK";$mForm.Close()})
        $mForm.Controls.Add($mOkButton)
        
        $mCancelButton = New-Object System.Windows.Forms.Button
        $mCancelButton.Size = New-Object System.Drawing.Size(73,23)
        $mCancelButton.Text = "Cancel"
        $mCancelButton.Add_Click({$mForm.Close()})
        $mForm.Controls.Add($mCancelButton)
        
        $mLabel = New-Object System.Windows.Forms.Label
        $mLabel.Location = New-Object System.Drawing.Size(10,20) 
        $mLabel.AutoSize = $true
        $mLabel.MaximumSize = New-Object System.Drawing.Size(($mForm.ClientSize.Width - 20), 0)        
        $mLabel.Text = $mInfoText
        $mForm.Controls.Add($mLabel) 
        
        # Panel to hold checkboxes
        $mPanel = New-Object System.Windows.Forms.Panel
        $mPanel.AutoScroll = $true
        $mForm.Controls.Add($mPanel)

        $mCheckboxes = @()
        $mHeigthPos = 10
        foreach($mCurrentElement in $mElements){
            $mCurrentCheckbox = New-Object System.Windows.Forms.CheckBox
            $mCurrentCheckbox.AutoSize = $true
            $mCurrentCheckbox.Text = $mCurrentElement
            $mCurrentCheckbox.Location = New-Object System.Drawing.Size(10, $mHeigthPos)
            if($mPreSelect){$mCurrentCheckbox.Checked=$true}
            $mPanel.Controls.Add($mCurrentCheckbox)
            $mCheckboxes += $mCurrentCheckbox
            $mHeigthPos += 25
        }
        
        function Update-Layout{
            #dynamic label width
            $mLabel.MaximumSize = New-Object System.Drawing.Size(($mForm.ClientSize.Width - 20), 0)
    
            #dynamic panel position
            $mPanel.Location = New-Object System.Drawing.Size(10, ($mLabel.Location.Y + $mLabel.Height + 10))
            $mPanel.Size = New-Object System.Drawing.Size(($mForm.ClientSize.Width - 20), ($mForm.ClientSize.Height - $mPanel.Location.Y - 60))

            #dynamic button position
            $mButtonY = $mForm.ClientSize.Height - $mOkButton.Height - 10

            $mCancelButton.Location = New-Object System.Drawing.Size(($mForm.ClientSize.Width - $mCancelButton.Width - 10), $mButtonY)
            $mOkButton.Location = New-Object System.Drawing.Size(($mCancelButton.Location.X - $mOkButton.Width - 10), $mButtonY)
        }

        $mForm.add_Resize({Update-Layout})
        Update-Layout

        $mForm.Topmost = $True
        $mForm.Add_Shown({$mForm.Activate()})
        
        if($mForm.ShowDialog() -eq "OK"){
            $mReturnValue = $mCheckboxes | Where-Object { $_.Checked } | ForEach-Object { $_.Text }
            Write-Host -ForegroundColor DarkGreen "Return selected values: $($mReturnValue -join ", ")."
            return $mReturnValue
        }else{
            Write-Host -ForegroundColor DarkYellow "Selection cancelled by user."
        }
    }catch{
        Write-Host -ForegroundColor DarkRed "Could not use Selection Box! Error: $($_.Exception.Message)"
        return $null
    }
}

#endregion

#region Variables
if($Verbose){$VerbosePreference = 'Continue'}else{$VerbosePreference = 'SilentlyContinue'}

$ScriptPath = &GetScriptPath
$ScriptName = &GetScriptName    
$LogFile = "$ScriptPath\$($ScriptName.replace('.ps1','.log'))"
$MinVersion = '14.0.1398.25189'

#endregion

Try{
    # Determine and check Docusnap installation directory
    $DSInstallInfo = &DS_GetDSInstallInfo

    if([version]$DSInstallInfo.Version -lt [version]$MinVersion){
        throw "At least version $MinVersion required. Current installed version of docusnap is $($DSInstallInfo.Version)"
    }   

    if($DSInstallInfo.InstallPath -eq $null -or $DSInstallInfo.InstallPath.Length -eq 0 -or !(Test-Path -Path $DSInstallInfo.InstallPath)){
        throw "Could not determine the docusnap installation directory."
    }


    # Determine and check Docusnap settings directory
    If(-not $NoDetect){
        $ConfigurationFilePath = &DS_GetConfigFile
    }else{
        $ConfigurationFilePath = &PromptForXmlFile 'Please select the docusnap configuration file' 
    }
    $SettingsPath = &DS_GetDSSSettingsPath -mConfigFilePath $ConfigurationFilePath

    if($SettingsPath -eq $null -or $SettingsPath.Length -eq 0 -or !(Test-Path -Path $SettingsPath)){
        throw "Could not determine the docusnap settings directory."    
    }

    #Check input xml for sql statements
    $SqlStatementsFilePath = "$ScriptPath\input.xml"
    if(Test-Path -Path $SqlStatementsFilePath){
        Try{
            [xml]$SqlStatementsFile = Get-Content -Path $SqlStatementsFilePath -ErrorAction:Stop
            
            if(-not $PrintSql){
                #Connect to Docusnap DB
                $Connection = &DS_ConnectDB -mConfigFilePath $ConfigurationFilePath
                if($Connection -ne $null){
                    #Get Accounts
                    $Accounts = @(DS_GetDocusnapAccounts($Connection))
                    if($Accounts.count -gt 0){
                        $SelectedAccountNames = @(&UseCheckBoxSelection 'Account selection' 'Please select the accounts whose meta-schema should be extended or updated and whose data should be imported.' $($Accounts.AccountName))
                        $Accounts = $Accounts | Where-Object{$_.AccountName -in $SelectedAccountNames}
                    }else{
                        Write-Verbose "No accounts selected for meta data import."
                    }

                }else{
                    throw "Connection to docusnap database could not be established."
                }
            }else{
                #no database connetion - create sql statement file
                #Dummy Account object
                $Accounts = @('')
            }

            foreach($CurrentAccount in $Accounts){
                $StatementCounter = 0    
                $AllStatementNodes = $SqlStatementsFile.SelectNodes("//Statement[@Action='Add']")
                foreach ($StatementNode in $AllStatementNodes) {
                    $StatementCounter++
                    if($StatementCounter % 25 -eq 0){
                        Write-Progress -Activity "Importing SQL data..." -Status "Executing SQL statement $StatementCounter / $($AllStatementNodes.Count)" -PercentComplete ($($StatementCounter / $AllStatementNodes.Count) * 100)
                    }

                    if(-not $PrintSql){
                        #translate sql-statement
                        $TranslatedStatement = $StatementNode.InnerText -replace "{AccountID}", $CurrentAccount.AccountID
                        #execute SQL statements
                        if(-not (&DS_SQL-Execute $Connection $TranslatedStatement)){
                            throw "Failed to execute SQL statement '$TranslatedStatement'. Error: $($_.Exception.Message)."
                        }
                        $MessageText = "Import meta data for Account $($CurrentAccount.AccountName): Success"
                    }else{
                        #print SQL statements into file
                        $null = Out-File -InputObject $StatementNode.InnerText -FilePath "$ScriptPath\SQL-Statements.sql" -Append -Force
                        $MessageText = "Write SQL statements to file '$ScriptPath\SQL-Statements.sql': Success"
                    }
                }
                Write-Progress -Activity "Importing SQL data..." -Status "Done" -Completed
                Write-Host -ForegroundColor Green $MessageText
            }#next Account
        }catch{
            throw "Import of additional meta data failed! Error: $($_.Exception.Message)"
        }
    }#else: There's not input xml

    # Set paths for the repository files
    $TargetReportRepositoryFilePath = "$SettingsPath\Reporting\ReportRepository.xml"
    Write-Verbose "Target repository file path: $TargetReportRepositoryFilePath"

    $SourceReportRepositoryFilePath = "$ScriptPath\Reporting\ReportRepository.xml"
    Write-Verbose "Source repository file path: $SourceReportRepositoryFilePath"
    
    # Import report files
    if(&DS_ImportReports -mSourceRepositoryFile $SourceReportRepositoryFilePath -mTargetRepositoryFile $TargetReportRepositoryFilePath -mImportEN $ImportEN -mUpdateReports $Update){
        $MessageText = 'Import reports: Success'
        &WriteLog $LogFile $MessageText
        Write-Host -ForegroundColor Green $MessageText

        # Import user interfaces
        if(&DS_ImportForms -mNewFormsRoot "$ScriptPath\DataEdit" -mSettingsFolder $SettingsPath -mUpdateForms $Update){
            $MessageText = 'Import input forms: Success'
            &WriteLog $LogFile $MessageText
            Write-Host -ForegroundColor Green $MessageText
        }else{
            throw "Failed to import input forms."
        }
    }else{
        throw "Failed to import reports."
    }
}catch{
    $MessageText = $($_.Exception.Message)
    Write-Host -ForegroundColor Red "$MessageText"
    &WriteLog $LogFile $MessageText
}
