Private/NavUtil.ps1

$NAVAdapterParameters = @{
    "Server" = "Server";
    "SOAPPort" = "Port";
    "AuthenticationMode" = "AuthenticationMode";
    "SSL" = "SSL";
    "RTCPort" = "ClientPort"
    "Instance" = "InstanceName";
    "ServiceAccountDomain" = "ServiceDomain";
    "ServiceAccountLogin" = "ServiceLogin";
    "ServiceAccountPassword" = "UnencryptedServicePassword";
    "SendBatchSize" = "SenderBatchSize";
    "SynchronizationTick" = "SynchronizationTimerStep";
    "MapOperationOnIgnoreResources" = "MapOperationOnIgnoreResourcesPS";
    "MapReservations" = "MapReservations";
    "KeepAlive" = "KeepAlive";
    "Tenant" = "TenantName";
    "WebServer" = "WebServerName";
    "WebPort" = "WebSitePort";
}

$NAVAdapterParametersType = @{
    "Server" = "string";
    "SOAPPort" = "int";
    "AuthenticationMode" = "string";
    "SSL" = "bool";
    "RTCPort" = "int"
    "Instance" = "string";
    "ServiceAccountDomain" = "string";
    "ServiceAccountLogin" = "string";
    "ServiceAccountPassword" = "string";
    "SendBatchSize" = "int";
    "SynchronizationTick" = "int";
    "MapOperationOnIgnoreResources" = "bool";
    "MapReservations" = "bool";
    "KeepAlive" = "bool";
    "Tenant" = "string";
    "WebServer" = "string";
    "WebPort" = "string";
}

function Set-NAVParameters(
    [string]$Tenant,
    [string]$Server,
    [int]$SOAPPort,
    [string]$AuthenticationMode,
    [bool]$SSL,
    [int]$ClientPort,
    [string]$InstanceName,
    [string]$Domain,
    [string]$Login,
    [string]$Password,
    [string]$TenantName) {
    
    if ($Server -eq "") {
        $Server = $Env:COMPUTERNAME
    }
    
    if ($SOAPPort -eq 0) {
        $SOAPPort = 7047
    }
    
    if ($AuthenticationMode -eq "") {
        $AuthenticationMode = "Negotiate"
        
        if ($Domain -eq "") {
            $Domain = $Env:COMPUTERNAME
        }
    }
    
    if ($ClientPort -eq 0) {
        $ClientPort = -1;
    }
    
    $configuration = @{
        UId = [System.Guid]::NewGuid();
        Version = 0;        
        Server = $Server;
        Port = $SOAPPort;
        Digest = $false;
        AuthenticationMode = $AuthenticationMode;
        SSL = $SSL;
        ClientPort = $ClientPort;
        InstanceName = $InstanceName;        
        ServiceDomain = $Domain;
        ServiceLogin = $Login;
        UnencryptedServicePassword = $Password;
        SenderBatchSize = 50;
        SynchronizerTimerStep = 3000;
        MapOperationsOnIgnoredResourcesPS = $true;
        MapReservationsPS = $true;
        KeepAlive = $true;
        RetryInitRatio = 5;
        TenantName = $TenantName;
        WebServerName = "";
        WebSitePort = 8080;
        MigratedV5 = $false;
        LastModificationDate = [System.DateTime]::UtcNow;
    }
    
    Write-NavParameters $Tenant $configuration
}

function Write-NavParameters($Tenant,$configuration) {
    $data = $configuration | ConvertTo-Json

    $storePath = Get-NAVConfigurationPath $Tenant
    [System.IO.File]::WriteAllText($storePath, $data)
    Write-Verbose "NAV configuration stored in $storePath"
}

function Get-NAVConfigurationPath($Tenant) {
    return Get-ConfigurationPath $Tenant "NAV.json"
}

function Get-NAVParametersFromConfig($InstanceName,[switch]$DefaultInstance,$NAVVersion) {
    $path = "$env:ProgramFiles\Microsoft Dynamics NAV"
    if ($NAVVersion -ne "") {
        $path += "\$NAVVersion\Service"    
    }
    
    Write-Verbose "Searching for psm1 modules in $path"
    $mod = (Get-ChildItem -Path $path -Filter '*.psm1' -Recurse).FullName
    if ($null -eq $mod) {
        Write-Warning "No Microsoft Dynamics NAV Powershell module found in $path"
        return    
    }
    
    Import-Module $mod

    if ($DefaultInstance) {
        $instance = Get-NAVServerInstance | Where-Object Default    
    } else {
        $instance = Get-NAVServerInstance -ServerInstance $InstanceName
    }
    
    if ($null -eq $instance) {
        Write-Warning "Microsoft Dynamics NAV instance not found with provided parameter."
        return
    }
    
    $xmlConfig = $instance | Get-NAVServerConfiguration -AsXml
    
    if ($null -eq $xmlConfig) {
        Write-Warning "Microsoft Dynamics NAV server configuration does not exist or cannot be read."
        return        
    }

    $values = $xmlConfig.configuration.appSettings.add

<#InstanceName needed by PlannerOne can differ from the function parameter one if full service instance name has been provided as parameter"#>
    $ServerInstance = ($values | Where-Object key -eq 'ServerInstance').value
    Write-Verbose "Server Instance: $ServerInstance" 
    $SOAPServicesPort = ($values | Where-Object key -eq 'SOAPServicesPort').value
    Write-Verbose "SOAP Port: $SOAPServicesPort"
    $ClientServicesPort = ($values | Where-Object key -eq 'ClientServicesPort').value
    Write-Verbose "Client Port: $ClientServicesPort"
    $SOAPServicesEnabled = ($values | Where-Object key -eq 'SOAPServicesEnabled').value
    Write-Verbose "SOAPServicesEnabled: $SOAPServicesEnabled"
    $ServicesDefaultTimeZone = ($values | Where-Object key -eq 'ServicesDefaultTimeZone').value
    Write-Verbose "ServicesDefaultTimeZone: $ServicesDefaultTimeZone"
    
    $instanceConfig = @{
        InstanceName = $ServerInstance;
        SOAPPort = $SOAPServicesPort;
        ClientPort = $ClientServicesPort;;
        SOAPServicesEnabled = $SOAPServicesEnabled;
        ServicesDefaultTimeZone = $ServicesDefaultTimeZone
    }
    
    return $instanceConfig
}

function Test-DBConfig($Role) {
    $dbConfig = Get-LegacyDatabaseConfig $Role
    if ($null -eq $dbConfig) {
        return $false
    }
    
    return $true
}

function Test-DBConnection($Role) {
    $dbConfig = Get-LegacyDatabaseConfig $Role
    if ($null -eq $dbConfig) {
        return $false
    }
    
    $connectionString = Get-ConnectionString -Server $dbConfig.Server -Database $dbConfig.Database -IntegratedSecurity "true"
    Write-Verbose "Connection string: $connectionString"
    
    try {
        Get-DatabaseData -connectionString $connectionString -query "select 1" -isSQLServer
    } catch {
        Write-Warning "Cannot connect to database with connection string $connectionString"
        return $false
    }
            
    Write-Verbose "Database connection OK"
    return $true
}

function Migrate-Configuration($Role) {
    $dbConfig = Get-LegacyDatabaseConfig $Role
    if ($null -eq $dbConfig) {
        return
    }
    
    $connectionString = Get-ConnectionString -Server $dbConfig.Server -Database $dbConfig.Database -IntegratedSecurity "true"
    Write-Verbose "Connection string: $connectionString"
    Migrate-Adapter $Role $connectionString
    Migrate-UIParameter $Role $connectionString
    Migrate-Criteria $Role $connectionString
}

function Migrate-UIParameter($Role,$connectionString) {
    try  {
        $data = Get-DatabaseData -connectionString $connectionString -query "select * from dbo.UIParameters" -isSQLServer
    } catch {
        Write-Verbose "No ui parameters to migrate."
        return
    }

    Write-Verbose "Migrating UI parameters..."
    if ($null -eq $data) {
        Write-Verbose "No ui parameters to migrate."
        return   
    }    

    $src = $data[1]    
    
    if ($null -eq $src.UId) {
        Write-Verbose "No ui parameters to migrate."
        return
    }
    
    $parameters = @{
        UId = $src.UId;
        Version = $src.Version;
        AdvanceDelayColorThresholdDuration = $src.AdvanceDelayColorThresholdDuration;
        ProjectLinksDisplayThreshold = $src.ProjectLinksDisplayThreshold;
        LastModificationDate = $src.LastModificationDate;
    }

    $storePath = Get-UIParamPathFromTenant $Role
    if (!(Test-Path $storePath)) {
        md $storePath | Out-Null
    }
    
    $storePath = $storePath + "parameters.json"
    $json = $parameters | ConvertTo-Json
    $json | Out-File $storePath
    Write-Verbose "UI parameters stored in $storePath"
}

function Migrate-Adapter($Role,$connectionString) {
    try {
    $data = Get-DatabaseData -connectionString $connectionString -query "select * from dbo.NavConfiguration" -isSQLServer
    } catch {
        Write-Verbose "No adapter configuration to migrate."
        return
    }
    Write-Verbose "Migrating adapter configuration..."
    if ($null -eq $data) {
        Write-Verbose "No adapter configuration to migrate."
        return   
    }
    
    $src = $data[1]
    
    if ($null -eq $src.UId) {
        Write-Verbose "No adapter configuration to migrate."
        return
    }

# Some PlannerOne version does not contains some boolean parameters
# Set default value to False instead of null if the data is not found
    $migratedV5 = Get-Bool $src.MigratedV5
    $ntlm = Get-Bool $src.NTLM
    $digest = Get-Bool $src.Digest
    $ssl = Get-Bool $src.SSL
    $ignoredResource = Get-Bool $src.MapOperationsOnIgnoredResourcesPS
    $mapReservation = Get-Bool $src.MapReservationsPS
    $keepAlive = Get-Bool $src.KeepAlive 
    $port = Get-Int $src.Port 7047 
    $clientPort = Get-Int $src.ClientPort -1
    $senderBatchSize = Get-Int $src.SenderBatchSize 50 
    $synchroStep = Get-Int $src.SynchronizerTimerStep 3000
    $retryInit = Get-Int $src.RetryInitRatio 5
    $webPort = Get-Int $src.WebSitePort 8080
    
    $navConfiguration = @{
        UId = $src.UId;
        Version = $src.Version;
        Server = $src.Server;
        Port = $port;
        NTLM = $ntlm;
        Digest = $digest;
        AuthenticationMode = $src.AuthenticationMode;
        SSL = $ssl;
        ClientPort = $clientPort;
        InstanceName = $src.WebRoot;
        ServiceDomain = $src.ServiceDomain;
        ServiceLogin = $src.ServiceLogin;
        ServicePassword = $src.ServicePassword;
        SenderBatchSize = $senderBatchSize;
        SynchronizerTimerStep = $synchroStep;
        MapOperationsOnIgnoredResourcesPS = $ignoredResource;
        MapReservationsPS = $mapReservation;
        KeepAlive = $keepAlive;
        RetryInitRatio = $retryInit;
        TenantName = $src.TenantName;
        WebServerName = $src.WebServerName;
        WebSitePort = $webPort;
        MigratedV5 = $migratedV5;
        LastModificationDate = $src.LastModificationDate;
    }
    
    Write-NavParameters $Role $navConfiguration
    Write-Verbose "Migration done"
}

function Get-Bool($value) {
        if ($value -eq $null) {
                return $false
        } else {
                return $value
        }
}
        
function Get-Int($value, $default) {
        if ($value -eq $null) {
                return $default
        } else {
                return $value
        }
}
        
function Migrate-Criteria($Role,$connectionString) {
    Migrate-TypedCriteria $Role $connectionString "Placement"
    Migrate-TypedCriteria $Role $connectionString "Task"
}

function Migrate-TypedCriteria($Role,$connectionString, $CriteriaType) {
    $query = "select * from dbo.CombinedCriterionConfiguration where CombinedType='$CriteriaType'"
    try {
    $data = Get-DatabaseData -connectionString $connectionString -query $query -isSQLServer
    } catch {
        Write-Verbose "No $CriteriaType criteria to migrate."
        return
    }
    Write-Verbose "Migrating criteria..."
    if ($null -eq $data) {
        Write-Verbose "No $CriteriaType criteria to migrate."
        return   
    }    

    $TypeInt = 0
    $ChoiceStrategy = "SC_FilteringChoiceStrategy"
    
    if ($CriteriaType -eq "Placement") {
        $TypeInt = 1
        $ChoiceStrategy = "PC_FilteringChoiceStrategy"
    }
    
    $src = $data[1]
    
    if ($null -eq $src.UId) {
        Write-Verbose "No $CriteriaType criteria to migrate."
        return
    }
    
    $Usages = Get-CriterionUsages $connectionString $src.UId
    
    $Strategy = @{
        UId = [System.Guid]::NewGuid();
        Name = $ChoiceStrategy;
    }
    
    $parameters = @{
        UId = $src.UId;
        Version = $src.Version;
        CombinedType = $TypeInt;
        Name = $src.Name;
        ChoiceStrategy = $Strategy;
        CriterionUsageConfiguration = $Usages;
    }

    $storePath = Get-CriteriaPathFromTenant $Role
    if (!(Test-Path $storePath)) {
        md $storePath | Out-Null
    }
    
    $storePath = $storePath + "CombinedCriterion$CriteriaType.json"
    $json = $parameters | ConvertTo-Json
    $json | Out-File $storePath
    Write-Verbose "Criteria stored in $storePath"
}

function Get-CriterionUsages($connectionString, $CriterionUId) {
    $query = "select * from dbo.CriterionUsageConfiguration where CombinedCriterionConfiguration_UId='$CriterionUId'"
    Write-Verbose $query
    $data = Get-DatabaseData -connectionString $connectionString -query $query -isSQLServer
    Write-Verbose "Migrating criterion usages..."
    if ($null -eq $data) {
        Write-Verbose "No criterion usages for $CriterionUId to migrate."
        return
    }
        
    $usages = @()
    
    $i = 0
    foreach ($usage in $data) {
        if ($i -gt 0) {
            $usage = @{
                UId = $usage.UId;
                Version = $usage.Version;
                Name = $usage.Name;
                Position = $usage.Position;
                Margin = $usage.Margin;
                Unit = $usage.Unit;
                LastModificationDate = $usage.LastModificationDate;
            }
            
            $usages += $usage    
        }
        
        $i++
    }
    
    return $usages
}

# http://social.technet.microsoft.com/wiki/contents/articles/890.export-sql-server-blob-data-with-powershell.aspx
function Export-DBEnvironments([string] $connectionString, [string] $datatable, [string] $outDir, [switch] $useUId) {
        $query = "select * from $datatable"
        Write-Verbose $query
        if (!(Test-Path $outDir)) {
            md $outDir | Out-Null
        }
    
        $con = New-Object Data.SqlClient.SqlConnection;
        $con.ConnectionString = $connectionString
        $con.Open()

        $cmd = New-Object Data.SqlClient.SqlCommand $query, $con
        $rd = $cmd.ExecuteReader()

        while ($rd.Read()) {
                if ($ColIndexFetched -eq $null) {
                    $UIdCol = $rd.GetOrdinal("UId")
                    $VersionCol = $rd.GetOrdinal("Version")
                    $AutoPublishCol = $rd.GetOrdinal("AutoPublish")
                    $WorkingRawCol = $rd.GetOrdinal("WorkingRaw")
                    $PublishedRawCol = $rd.GetOrdinal("PublishedRaw")
                    $LastModifCol = $rd.GetOrdinal("LastModificationDateTicks")
                    $NameCol = $rd.GetOrdinal("Name")
                    $GroupCol = $rd.GetOrdinal("PlanningGroupContainer")
                    $ColIndexFetched = $true
                }

                
                if ($useUId) {
                    $envDirName = $rd.GetGuid($UIdCol)
                } else {
                    $envName = Get-EscapedPath $rd.GetString($NameCol)
                    $envDirName =  $envName
                    if(!($rd.IsDBNull($GroupCol))) {
                        $envGroup = $rd.GetString($GroupCol)                    
                        $escapedGroup = Get-EscapedPath $envGroup
                        $envDirName = $envDirName + "\" + $escapedGroup                        
                    }
                }

                Write-Output "Exporting environment $envDirName"
                $envDir = $outDir + "\" + $envDirName
                if (!(Test-Path $envDir)) {
                     md $envDir | Out-Null
                }

                $environment = @{
                    UId = $rd.GetGuid($UIdCol);
                    Version = $rd.GetInt32($VersionCol);
                    ModelVersion = "5.1";
                    LastModificationDate= $null
                    AutoPublish = $rd.GetBoolean($AutoPublishCol);
                }
    
                $filePath = $envDir + "\environment.json"
                $json = $environment | ConvertTo-Json
                $json | Out-File $filePath                
                Write-Verbose "Environment informations $envDirName exported"

                Write-Verbose "Exporting working planning..."
                $workingPath = $envDir + "\working.protobuf"
                Write-Planning $rd $WorkingRawCol $workingPath
                Write-Verbose "Working planning exported"

                Write-Verbose "Exporting published planning..."
                if(!($rd.IsDBNull($PublishedRawCol))) {
                    $publishedPath = $envDir + "\published.protobuf"
                    Write-Planning $rd $PublishedRawCol $publishedPath
                    Write-Verbose "Published planning exported"
                } else {
                    Write-Verbose "No published planning"
                }



                Write-Verbose "Environment binaries $envDirName exported"
                Write-Output "Environment $envDirName exported"
            }

        if ($fs -ne $null) {
            $fs.Dispose()
        }

        if ($rd -ne $null) {
            $rd.Close()
        }

        if ($cmd -ne $null) {
            $cmd.Dispose()
        }

        if ($con -ne $null) {
            $con.Close()
        }

        Write-Output "Export finished"
}

function Get-EscapedPath([string] $toEscape) {
    $escaped = [System.Uri]::EscapeDataString($toEscape)
    $escaped = $escaped.Replace("*", "__STAR__")
    $escaped = $escaped.Replace(".", "__DOT__")
    return $escaped
}

function Write-Planning($rd, $PlanningCol, $path) {
        $fs = New-Object System.IO.FileStream $path, Create, Write
        $bw = New-Object System.IO.BinaryWriter $fs
        $bufferSize = 8192
        $out = [array]::CreateInstance('Byte', $bufferSize)


        $start = 0

        $received = $rd.GetBytes($PlanningCol, $start, $out, 0, $bufferSize -1)
        while ($received -gt 0) {
            $bw.Write($out, 0, $received)
            $bw.flush()
            $start += $received
            $received = $rd.GetBytes($WorkingRawCol, $start, $out, 0, $bufferSize - 1)
        }

        $bw.Close()
        $fs.Close()
}

function Test-NavVersionCompatibility{
    param ([Parameter(Mandatory=$True)] $year)

    foreach($yVersion in $global:NavVersion){
        if($yVersion -eq $year){
            return $true
        }
    }
    return $false
}

function Set-NAVAdapterParameter([string] $Tenant, [string] $Name, [string] $Value) {
    $storePath = Get-NAVConfigurationPath $Tenant
    $parameters = Get-AdapterParameters $Tenant $storePath
    if ($parameters -eq $null) {
        $parameters = New-NAVAdapterParameters $Tenant $storePath
    }

    if ($parameters -eq $null) {
        Write-Warning "Cannot create or get NAV parameters for tenant $Tenant"
        return
    }

    Set-AdapterParameter $Name $Value $parameters $NAVAdapterParameters $NAVAdapterParametersType "NAV"
    Write-NavParameters $Tenant $parameters
    Write-Verbose "Parameter set"
}

function New-NAVAdapterParameters([string] $Tenant, $StorePath) {
    if (!(Test-Path $StorePath)) {
        Set-NAVParameters
    }

    return Get-AdapterParameters $Tenant $StorePath
}

function Write-NAVValidParameters {
    Write-ValidParameters "NAV" $NAVAdapterParameters 
}