How to send your data

<#
.SYNOPSIS
  Exports one Microsoft MDS model to CSV files for a Primentra demo.

.DESCRIPTION
  The script writes one CSV file per entity, named <Model>__<Entity>.csv.
  Each file has a header row: Code, Name, then the display name of each leaf
  attribute. A domain attribute holds the code of the member it points to.
  The script also exports every entity that a domain attribute points to.

  It exports active members (Status_ID = 1) of one model version only, and at
  most -MaxRows rows per entity.

  The script only reads. It sends SELECT statements and nothing else.
  It signs in with your Windows login, so you need read access to the MDS
  database. With -SqlLogin it signs in with a SQL Server login instead, and asks
  for the password without showing it. It shows file names and row counts on
  screen, never row values, and never the password.

.EXAMPLE
  .\export-mds-for-demo.ps1 -Server SQL01\MDS -Database MDS -Model Product

.EXAMPLE
  .\export-mds-for-demo.ps1 -Server SQL01 -Database MDS -Model Product -Entities Item,"Item Group" -MaxRows 500

.EXAMPLE
  .\export-mds-for-demo.ps1 -Server SQL01 -Database MDS -Model Product -SqlLogin mds_reader

.NOTES
  MDS tables and columns this script reads:
    mdm.tblModel         ID, Name
    mdm.tblModelVersion  ID, Model_ID, Name
    mdm.tblEntity        ID, Model_ID, Name, EntityTable
    mdm.tblAttribute     Entity_ID, MemberType_ID, DisplayName, TableColumn,
                         AttributeType_ID, DomainEntity_ID, IsSystem, SortOrder, ID
    mdm.<EntityTable>    Version_ID, ID, Status_ID, Code, Name, attribute columns

  Runs in Windows PowerShell 5.1. Needs no modules.
#>
[CmdletBinding(PositionalBinding = $false)]
param(
  [Parameter(Mandatory = $true)] [string] $Server,
  [Parameter(Mandatory = $true)] [string] $Database,
  [Parameter(Mandatory = $true)] [string] $Model,
  [string[]] $Entities,
  [string] $Version,
  [ValidateRange(1, 1000000)] [int] $MaxRows = 1000,
  [string] $OutFolder,
  [string] $SqlLogin
)

$ErrorActionPreference = 'Stop'
$invariant = [System.Globalization.CultureInfo]::InvariantCulture

# "powershell -File" passes -Entities "Customer","Region" as one text: Customer,Region.
# Split on commas so the list works from both a console and a command line.
$Entities = @($Entities | ForEach-Object { $_ -split ',' } | ForEach-Object { $_.Trim() } | Where-Object { $_ })

function Stop-Export([string] $Message) {
  [Console]::Error.WriteLine($Message)
  exit 1
}

# Table and column names come from MDS metadata. Bracket-quote them, like QUOTENAME.
function Get-QuotedName([string] $Name) {
  return '[' + $Name.Replace(']', ']]') + ']'
}

function Invoke-Select([string] $Sql, [hashtable] $Parameters) {
  $command = $script:connection.CreateCommand()
  $command.CommandText = $Sql
  $command.CommandTimeout = 300
  foreach ($key in $Parameters.Keys) {
    [void] $command.Parameters.AddWithValue($key, $Parameters[$key])
  }
  $table = New-Object System.Data.DataTable
  $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
  [void] $adapter.Fill($table)
  return ,$table
}

function Get-SafeFilePart([string] $Name) {
  $safe = $Name
  foreach ($char in [System.IO.Path]::GetInvalidFileNameChars()) {
    $safe = $safe.Replace([string] $char, '-')
  }
  # "__" separates model from entity, so it must not appear inside either name.
  $safe = [regex]::Replace($safe, '_{2,}', '_').Trim('_')
  if ($safe -eq '') { $safe = '-' }
  return $safe
}

function Format-CsvField($Value) {
  if ($null -eq $Value -or $Value -is [System.DBNull]) {
    $text = ''
  }
  elseif ($Value -is [datetime]) {
    if ($Value.TimeOfDay.Ticks -eq 0) { $text = $Value.ToString('yyyy-MM-dd', $invariant) }
    else { $text = $Value.ToString('yyyy-MM-dd HH:mm:ss', $invariant) }
  }
  elseif ($Value -is [System.DateTimeOffset]) {
    $text = $Value.ToString('yyyy-MM-dd HH:mm:ss', $invariant)
  }
  elseif ($Value -is [System.IFormattable]) {
    $text = $Value.ToString($null, $invariant)
  }
  else {
    $text = [string] $Value
  }
  return '"' + $text.Replace('"', '""') + '"'
}

$connection = New-Object System.Data.SqlClient.SqlConnection
$builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$builder['Data Source'] = $Server
$builder['Initial Catalog'] = $Database
$builder['Application Name'] = 'Primentra demo export'
if ($SqlLogin) {
  # The password never goes on the command line, where it would stay in the PowerShell history.
  # It is asked for with hidden typing and handed to SqlClient as a SecureString. The environment
  # variable exists for automated runs only.
  if ($env:PRIMENTRA_SQL_PASSWORD) {
    Write-Host 'Using the password from the PRIMENTRA_SQL_PASSWORD environment variable.'
    $password = ConvertTo-SecureString $env:PRIMENTRA_SQL_PASSWORD -AsPlainText -Force
  }
  else {
    $password = Read-Host -AsSecureString -Prompt "Password for SQL login '$SqlLogin' (you will not see it while you type)"
  }
  $password.MakeReadOnly()
  $builder['Integrated Security'] = $false
  $connection.ConnectionString = $builder.ConnectionString
  $connection.Credential = New-Object System.Data.SqlClient.SqlCredential($SqlLogin, $password)
}
else {
  $builder['Integrated Security'] = $true
  $connection.ConnectionString = $builder.ConnectionString
}

try {
  $connection.Open()
}
catch {
  Stop-Export "Cannot connect to database '$Database' on server '$Server': $($_.Exception.Message)"
}

try {
  # Say which login the server accepted, so a person can see the right account is in use.
  $whoCommand = $connection.CreateCommand()
  $whoCommand.CommandText = 'SELECT SUSER_SNAME()'
  Write-Host ("Signed in to '{0}' as {1}." -f $Server, $whoCommand.ExecuteScalar())

  # ---------------------------------------------------------------- model
  $models = Invoke-Select 'SELECT ID, Name FROM mdm.tblModel WHERE Name = @Model' @{ '@Model' = $Model }
  if ($models.Rows.Count -eq 0) {
    $all = Invoke-Select 'SELECT Name FROM mdm.tblModel ORDER BY Name' @{}
    $names = @($all.Rows | ForEach-Object { '  ' + $_.Name })
    Stop-Export ("Model '$Model' does not exist. No files written.`r`nModels in this database:`r`n" + ($names -join "`r`n"))
  }
  $modelId = [int] $models.Rows[0].ID
  $modelName = [string] $models.Rows[0].Name

  # -------------------------------------------------------------- version
  $versions = Invoke-Select 'SELECT ID, Name FROM mdm.tblModelVersion WHERE Model_ID = @ModelId ORDER BY ID DESC' @{ '@ModelId' = $modelId }
  if ($versions.Rows.Count -eq 0) {
    Stop-Export "Model '$modelName' has no versions. No files written."
  }
  if ($Version) {
    $versionRow = @($versions.Rows | Where-Object { $_.Name -eq $Version }) | Select-Object -First 1
    if ($null -eq $versionRow) {
      $names = @($versions.Rows | ForEach-Object { '  ' + $_.Name })
      Stop-Export ("Version '$Version' does not exist in model '$modelName'. No files written.`r`nVersions of this model:`r`n" + ($names -join "`r`n"))
    }
  }
  else {
    $versionRow = $versions.Rows[0]
  }
  $versionId = [int] $versionRow.ID
  $versionName = [string] $versionRow.Name

  # ------------------------------------------------------------- entities
  # An entity without an EntityTable has no leaf members to export.
  $entityTable = Invoke-Select 'SELECT ID, Name, EntityTable FROM mdm.tblEntity WHERE Model_ID = @ModelId AND EntityTable IS NOT NULL ORDER BY Name' @{ '@ModelId' = $modelId }
  $entityById = @{}
  foreach ($row in $entityTable.Rows) {
    $entityById[[int] $row.ID] = @{ Id = [int] $row.ID; Name = [string] $row.Name; Table = [string] $row.EntityTable }
  }

  # Leaf (MemberType_ID 1), non-system, free-form (1) or domain (2) attributes.
  $attributeTable = Invoke-Select @'
SELECT a.Entity_ID, a.DisplayName, a.TableColumn, a.AttributeType_ID, a.DomainEntity_ID
FROM mdm.tblAttribute a
JOIN mdm.tblEntity e ON e.ID = a.Entity_ID
WHERE e.Model_ID = @ModelId
  AND a.IsSystem = 0
  AND a.MemberType_ID = 1
  AND a.AttributeType_ID IN (1, 2)
ORDER BY a.Entity_ID, a.SortOrder, a.ID
'@ @{ '@ModelId' = $modelId }
  $attributesByEntity = @{}
  foreach ($row in $attributeTable.Rows) {
    $id = [int] $row.Entity_ID
    if (-not $attributesByEntity.ContainsKey($id)) { $attributesByEntity[$id] = New-Object System.Collections.ArrayList }
    $domainId = $null
    if ([int] $row.AttributeType_ID -eq 2 -and -not ($row.DomainEntity_ID -is [System.DBNull])) {
      $domainId = [int] $row.DomainEntity_ID
    }
    [void] $attributesByEntity[$id].Add(@{
      DisplayName = [string] $row.DisplayName
      Column      = [string] $row.TableColumn
      DomainId    = $domainId
    })
  }

  $queue = New-Object System.Collections.Queue
  if ($Entities -and $Entities.Count -gt 0) {
    foreach ($requested in $Entities) {
      $match = @($entityById.Values | Where-Object { $_.Name -eq $requested }) | Select-Object -First 1
      if ($null -eq $match) {
        $names = @($entityById.Values | Sort-Object { $_.Name } | ForEach-Object { '  ' + $_.Name })
        Stop-Export ("Entity '$requested' does not exist in model '$modelName'. No files written.`r`nEntities of this model:`r`n" + ($names -join "`r`n"))
      }
      $queue.Enqueue($match.Id)
    }
  }
  else {
    foreach ($id in $entityById.Keys) { $queue.Enqueue($id) }
  }

  # Add every entity a domain attribute points to, and the entities those point to.
  $selected = @{}
  while ($queue.Count -gt 0) {
    $id = $queue.Dequeue()
    if ($selected.ContainsKey($id)) { continue }
    $selected[$id] = $true
    if ($attributesByEntity.ContainsKey($id)) {
      foreach ($attribute in $attributesByEntity[$id]) {
        if ($null -ne $attribute.DomainId -and $entityById.ContainsKey($attribute.DomainId)) {
          $queue.Enqueue($attribute.DomainId)
        }
      }
    }
  }

  # --------------------------------------------------------------- export
  # Without -OutFolder, every run gets a folder of its own. Re-running with -Entities into the same
  # folder would leave the first run's files next to the new ones, and "select all" on the upload
  # form would pick up both.
  if (-not $OutFolder) {
    $OutFolder = Join-Path '.\primentra-demo-data' ((Get-SafeFilePart $modelName) + ' ' + (Get-Date -Format 'yyyy-MM-dd HHmmss'))
  }
  $outPath = $ExecutionContext.SessionState.Path.GetUnresolvedProviderPathFromPSPath($OutFolder)
  [void] [System.IO.Directory]::CreateDirectory($outPath)
  $utf8WithBom = New-Object System.Text.UTF8Encoding $true
  $usedFileNames = @{}

  Write-Host "Model '$modelName', version '$versionName'. Writing to $outPath"

  foreach ($entity in @($selected.Keys | ForEach-Object { $entityById[$_] } | Sort-Object { $_.Name })) {
    $headers = New-Object System.Collections.ArrayList
    $usedHeaders = @{ 'code' = $true; 'name' = $true }
    [void] $headers.Add('Code')
    [void] $headers.Add('Name')

    $selectList = New-Object System.Collections.ArrayList
    [void] $selectList.Add('e.[Code] AS [c0]')
    [void] $selectList.Add('e.[Name] AS [c1]')
    $joins = ''
    $index = 2

    $attributes = @()
    if ($attributesByEntity.ContainsKey($entity.Id)) { $attributes = $attributesByEntity[$entity.Id] }
    foreach ($attribute in $attributes) {
      # A display name can repeat Code, Name or another attribute. Make it unique.
      $header = $attribute.DisplayName
      $suffix = 2
      while ($usedHeaders.ContainsKey($header.ToLowerInvariant())) {
        $header = $attribute.DisplayName + ' ' + $suffix
        $suffix++
      }
      $usedHeaders[$header.ToLowerInvariant()] = $true
      [void] $headers.Add($header)

      $column = Get-QuotedName $attribute.Column
      if ($null -ne $attribute.DomainId -and $entityById.ContainsKey($attribute.DomainId)) {
        $alias = "d$index"
        $domainTable = Get-QuotedName $entityById[$attribute.DomainId].Table
        # A deactivated domain member is not in the domain's own file, so its code is left out here too.
        $joins += "`r`nLEFT JOIN mdm.$domainTable AS $alias ON $alias.[Version_ID] = e.[Version_ID] AND $alias.[ID] = e.$column AND $alias.[Status_ID] = 1"
        [void] $selectList.Add("$alias.[Code] AS [c$index]")
      }
      else {
        [void] $selectList.Add("e.$column AS [c$index]")
      }
      $index++
    }

    # One row more than MaxRows tells whether the cap cut rows off.
    $sql = "SELECT TOP (@Top) " + ($selectList -join ', ') +
      "`r`nFROM mdm." + (Get-QuotedName $entity.Table) + " AS e" + $joins +
      "`r`nWHERE e.[Status_ID] = 1 AND e.[Version_ID] = @VersionId" +
      "`r`nORDER BY e.[Code]"
    $rows = Invoke-Select $sql @{ '@Top' = [long] $MaxRows + 1; '@VersionId' = $versionId }

    $fileName = (Get-SafeFilePart $modelName) + '__' + (Get-SafeFilePart $entity.Name)
    $baseName = $fileName
    $suffix = 2
    while ($usedFileNames.ContainsKey($fileName.ToLowerInvariant())) {
      $fileName = "$baseName $suffix"
      $suffix++
    }
    $usedFileNames[$fileName.ToLowerInvariant()] = $true
    $fileName += '.csv'

    $writer = New-Object System.IO.StreamWriter ([System.IO.Path]::Combine($outPath, $fileName)), $false, $utf8WithBom
    try {
      $writer.NewLine = "`r`n"
      $writer.WriteLine((@($headers | ForEach-Object { Format-CsvField $_ }) -join ','))
      $written = [Math]::Min($rows.Rows.Count, $MaxRows)
      for ($r = 0; $r -lt $written; $r++) {
        $values = $rows.Rows[$r].ItemArray
        $writer.WriteLine((@($values | ForEach-Object { Format-CsvField $_ }) -join ','))
      }
    }
    finally {
      $writer.Dispose()
    }

    $rowWord = 'rows'
    if ($written -eq 1) { $rowWord = 'row' }
    if ($rows.Rows.Count -gt $MaxRows) {
      Write-Host ("  {0}  {1} {2} (capped by -MaxRows {3}; the entity has more)" -f $fileName, $written, $rowWord, $MaxRows)
    }
    else {
      Write-Host ("  {0}  {1} {2}" -f $fileName, $written, $rowWord)
    }
  }

  Write-Host ''
  Write-Host "The files are in: $outPath"
  if ($selected.Count -gt 12) {
    Write-Host ("That is {0} files. A demo takes at most 12 entities. Upload the 12 you want, or run the script again with -Entities and their names." -f $selected.Count)
  }
  Write-Host 'Upload the CSV files at https://primentra.com/demo-request'
  Write-Host 'You can select several files at once.'
}
catch {
  Stop-Export "Export failed: $($_.Exception.Message)"
}
finally {
  $connection.Dispose()
}