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 } |