Public/Excel.ps1


# TM Excel File
Function Read-TMAssetFile {
    param(
        [Parameter(Mandatory = $true)][String]$filePath
    )
    if (Get-FileTypeFromPath($filePath) -eq "xlsx") {
        $tmAssets = @{ }
        $data = Import-Excel $filePath -WorksheetName "Applications"
        Add-Member -InputObject $tmAssets -MemberType "NoteProperty" -Name "Applications" -Value $data -Force

        $data = Import-Excel $filePath -WorksheetName "Devices"
        Add-Member -InputObject $tmAssets -MemberType "NoteProperty" -Name "Devices" -Value $data -Force

        $data = Import-Excel $filePath -WorksheetName "Databases"
        Add-Member -InputObject $tmAssets -MemberType "NoteProperty" -Name "Databases" -Value $data -Force

        $data = Import-Excel $filePath -WorksheetName "Storage"
        Add-Member -InputObject $tmAssets -MemberType "NoteProperty" -Name "Storage" -Value $data -Force

        $data = Import-Excel $filePath -WorksheetName "Dependencies"
        Add-Member -InputObject $tmAssets -MemberType "NoteProperty" -Name "Dependencies" -Value $data -Force

        $data = Import-Excel $filePath -WorksheetName "Room"
        Add-Member -InputObject $tmAssets -MemberType "NoteProperty" -Name "Room" -Value $data -Force

        $data = Import-Excel $filePath -WorksheetName "Rack"
        Add-Member -InputObject $tmAssets -MemberType "NoteProperty" -Name "Rack" -Value $data -Force

        $tmAssets

    }
}

function Export-TMExcel {
    <#
    .SYNOPSIS
    Exports TM Asset data to an Excel Spreadsheet
 
    .DESCRIPTION
    This function exports TM Asset data to an Excel Spreadsheet in the TMExcel format
 
    .EXAMPLE
    Export-TMExcel ## Saves to local directory
 
    .EXAMPLE
    Export-TMExcel -Path 'C:\Temp\'
 
    .EXAMPLE
    Export-TMExcel -Path 'C:\Temp\'
 
    .OUTPUTS
    Saves an Excel Export in the Path Specified
    #>


    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $false)][PSObject]$TMSession = 'Default',

        [Parameter(Mandatory = $true)][String]$Path

    )

    begin {

        ## Get Session Configuration
        $TMSession = Get-TMSession $TMSession

        #Honor SSL Settings
        $TMCertSettings = @{SkipCertificateCheck = $TMSession.AllowInsecureSSL }

        $Instance = $TMSession.TMServer.Replace('/tdstm', '')
        $instance = $instance.Replace('https://', '')
        $instance = $instance.Replace('http://', '')

        $uri = 'https://'
        $uri += $instance + '/tdstm/ws/asset/exportAssets'

    }

    process {

        $RequestBody = [PSCustomObject]@{
            "projectIdExport"   = $TMSession.UserContext.project.id
            "dataTransferSet"   = 1
            "application"       = "application"
            "asset"             = "asset"
            "exportFormat"      = "xlsx"
            "ImportApplication" = $true
            "ImportServer"      = $true
            "ImportDatabase"    = $true
            "ImportStorage"     = $true
            "ImportRoom"        = $true
            "ImportRack"        = $true
            "ImportDependency"  = $true
            "ImportCabling"     = $true
            "ImportComment"     = $true
            "bundle"            = "All"
        } | ConvertTo-Json -Compress

        ## Write the new Event that will be created
        Write-Verbose "Requesting Download: $RequestBody"

        ## Update the Content Type to JSON
        Set-TMHeaderContentType -ContentType 'JSON' -TMSession $TMSession

        ## Create a Request Splat
        $WebRequestSplat = @{
            Method     = 'POST'
            Uri        = $uri
            WebSession = $TMSession.TMWebSession
            Body       = $RequestBody
        }

        try {
            $Response = Invoke-WebRequest @WebRequestSplat @TMCertSettings
            if ($Response.StatusCode -eq 200 ) {
                $ResponseContent = $Response.content | ConvertFrom-Json
                if ($ResponseContent.status -ne 'success') {
                    Write-Error -Message $ResponseContent.errors[0]
                    return
                }
            } else {
                throw 'Unable to begin download'
            }
        } catch {
            throw $_
        }


        ## An Export job has started, get the Export Job ID
        $ExportJobID = $ResponseContent.data.key
        $Uri = "https://$instance/tdstm/ws/progress/$ExportJobId"

        ## Create a new WebRequest Splat
        $WebRequestSplat = @{
            Method     = 'Get'
            Uri        = $Uri
            WebSession = $TMSession.TMWebSession
        }

        try {

            $DownloadComplete = $False
            ## Poll for Status on the export job
            while (-Not $DownloadComplete) {

                ## Sleep an interval
                Start-Sleep -Seconds 1

                ## Check the Status from the server
                $Response = Invoke-WebRequest @WebRequestSplat @TMCertSettings
                $ResponseContent = $Response.content | ConvertFrom-Json
                <#{
                    "status": "success",
                    "data": {
                        "percentComp": 1,
                        "status": "In progress",
                        "detail": "",
                        "remainingTime": "Unknown",
                        "lastUpdated": 1638762169774,
                        "data": {}
                    }
                }#>


                ## Throw any Errors returned
                if ($ResponseContent.status -ne 'success') {
                    throw $ResponseContent.errors
                }

                ## Write progress
                $ProgressSplat = @{
                    Activity        = 'Downloading Asset Data'
                    PercentComplete = $ResponseContent.data.percentComp
                    Status          = $ResponseContent.data.status
                }
                Write-Progress @ProgressSplat

                if ($ResponseContent.data.status -eq 'Completed') {

                    $DownloadComplete = $True
                    $FileName = $ResponseContent.data.data.header -replace 'attachment; filename=', '' -replace '"', ''
                }
            }

            ##
            ## Download the File
            ##
            $Uri = "https://$instance/tdstm/assetEntity/downloadExport?key=$ExportJobId"

            ## Create a new WebRequest Splat
            $WebRequestSplat = @{
                Method     = 'Get'
                Uri        = $Uri
                WebSession = $TMSession.TMWebSession
            }
            $OutFilePath = Join-Path $Path $FileName
            $Response = Invoke-WebRequest @WebRequestSplat -OutFile $OutFilePath -PassThru
            if ($Response.StatusCode -ne 200 ) {
                throw 'Unable to Download Export file'
            }

            ## Write progress
            $ProgressSplat = @{
                Activity        = 'Downloaded Asset Data'
                PercentComplete = 100
                Status          = 'File Saved'
                Completed       = $true
            }
            Write-Progress @ProgressSplat

        } catch {
            throw $_
        }
    }

    end {
    }
}