Private/Get-AtwsData.ps1

<#
    .COPYRIGHT
    Copyright (c) Office Center H�nefoss AS. All rights reserved. Licensed under the MIT license.
    See https://github.com/officecenter/Autotask/blob/master/LICENSE.md for license information.
 
#>


Function Get-AtwsData 
{
  <#
      .SYNOPSIS
      This function queries the Autotask Web API for entities matching a specified type and filter.
      .DESCRIPTION
      This function queries the Autotask Web API for entities matching a specified type and filter.
      Valid operators:
      -and, -or
 
      Valid comparison operators:
      -eq, -ne, -lt, -le, -gt, -ge, -isnull, -isnotnull, -isthisday
 
      Valid text comparison operators:
      -contains, -like, -notlike, -beginswith, -endswith, -soundslike
          
      Special operators to nest conditions:
      -begin, -end
 
      .INPUTS
      Nothing.
      .OUTPUTS
      Autotask.Entity[]. One or more Autotask entities returned from Autotask Web API.
      .EXAMPLE
      Get-AtwsData -Entity Ticket -Filter {id -gt 0}
      Gets all tickets with an id greater than 0 from Autotask Web API
      .NOTES
      NAME: Get-AtwsData
      .LINK
      Set-AtwsData
      New-AtwsData
      Remove-AtwsData
  #>

  
  [cmdletbinding(
      SupportsShouldProcess = $True,
      ConfirmImpact = 'Low'
  )]
  [OutputType([PSObject[]])]
  param
  (
    [Parameter(
        Mandatory = $True,
        Position = 0
    )]
    [String]
    $Entity,
          
    [Parameter(
        Mandatory = $True,
        ValueFromRemainingArguments = $true,
        Position = 1
    )]
    [String[]]
    $Filter
  )
  Begin
  { 
    # Enable modern -Debug behavior
    If ($PSCmdlet.MyInvocation.BoundParameters['Debug'].IsPresent) {$DebugPreference = 'Continue'}
    
    Write-Debug ('{0}: Begin of function' -F $MyInvocation.MyCommand.Name)
       
    If (-not($Script:Atws.Url))
    {
      Throw [ApplicationException] 'Not connected to Autotask WebAPI. Re-import module with valid credentials.'
    }
    
    $Result = @()
    
    # Set up TimeZone offset handling
    If (-not($script:ESTzone)) {
      $script:ESTzone = [System.TimeZoneInfo]::FindSystemTimeZoneById("Eastern Standard Time")
    }
    
    If (-not($script:ESToffset)) {
      $Now = Get-Date
      $ESTtime = [System.TimeZoneInfo]::ConvertTimeFromUtc($Now.ToUniversalTime(), $ESTzone)

      $script:ESToffset = (New-TimeSpan -Start $ESTtime -End $Now).TotalHours
    }
  }
  
  Process
  {
  
    Write-Debug ('{0}: Mashing parameters into an array of strings.' -F $MyInvocation.MyCommand.Name)
    
    # $Filter should not be a flat string. If it is - fix it!
    If ($Filter.Count -eq 1 -and $Filter -match ' ' )
    { 
      # First, make sure it is a single string and replace parenthesis with our special operator
      $Filter = $Filter -join ' ' -replace '\(',' -begin ' -replace '\)', ' -end '
    
      # Removing double possible spaces we may have introduced
      Do {$Filter = $Filter -replace ' ',' '}
      While ($Filter -match ' ')

      # Split back in to array, respecting quotes
      $Words = $Filter.Trim().Split(' ')
      $Filter = @()
      $Temp = @()
      Foreach ($Word in $Words)
      {
        If ($Temp.Count -eq 0 -and $Word -match '^[\"\'']')
        {
          $Temp += $Word.TrimStart('"''')
        }
        ElseIf ($Temp.Count -gt 0 -and $Word -match "[\'\""]$")
        {
          $Temp += $Word.TrimEnd("'""")
          $Filter += $Temp -join ' '
          $Temp = @()
        }
        ElseIf ($Temp.Count -gt 0)
        {
          $Temp += $Word
        }
        Else
        {
          $Filter += $Word
        }
      }
    }
    Write-Debug ('{0}: Checking query for variables that have survived as string' -F $MyInvocation.MyCommand.Name)
    $NewFilter = @()
    Foreach ($Word in $Filter)
    {
      $Value = $Word
      # Is it a variable name?
      If ($Word -match '^\$\{?(\w+:)?(\w+)\}?(\.\w[\.\w]+)?$')
      {
        # If present, first group is SCOPE. In the context of this function, scope is always Global, i.e. 3
        # so any value her is just ignored

        
        # The variable name MUST be present
        $VariableName = $Matches[2]

        # A property tail CAN be present
        $PropertyTail = $Matches[3]
        
        # Check that the variable exists
        $Variable = Try
        { Get-Variable -Name $VariableName -Scope 3 -ValueOnly -ErrorAction Stop }
        Catch
        {
          Write-Error ('{0}: Could not find any variable called ${1}. Is it misspelled or has it not been set yet?' -f $MyInvocation.MyCommand.Name, $VariableName)
          # Force stop of calling script, because this will completely break the query
          Return
        }

        # Test if the variable "Variable" has been set
        If (Test-Path Variable:Variable) {
          Write-Debug ('{0}: Substituting {1} for its value' -F $MyInvocation.MyCommand.Name, $Word)
          If ($PropertyTail) {
            # Add properties back
            $Expression = '$Variable{0}' -F $PropertyTail
  
            # Invoke-Expression is considered risky from an SQL injection kind of perspective. But by only
            # permitting a .dot separated string of [a-zA-Z0-9_] we are PROBABLY safe...
            $Value = Invoke-Expression -Command $Expression
          }
          Else {
            $Value = $Variable
          }
          If ($Value -eq $Null) {
            Write-Error ('{0}: Could not find any variable called {1}. Is it misspelled or has it not been set yet?' -F $MyInvocation.MyCommand.Name, $Expression)
            # Force stop of calling script, because this will completely break the query
            Return
          }
          Else { 
            # Normalize dates. Important to avoid QueryXML problems
            If ($Value.GetType().Name -eq 'DateTime')
            {[String]$Value = ConvertTo-AtwsDate -ParameterName $NewFilter[-2] -DateTime $Value}
          }
        }
      }
      $NewFilter += $Value
    }
    
    # Squash into a flat array with entity first
    [Array]$Query = @($Entity) + $NewFilter
  
    Write-Debug ('{0}: Converting query string into QueryXml. String as array looks like: {1}' -F $MyInvocation.MyCommand.Name, $($Query -join ', '))
    [xml]$QueryXml = ConvertTo-QueryXML @Query

    Write-Debug ('{0}: QueryXml looks like: {1}' -F $MyInvocation.MyCommand.Name, $QueryXml.InnerXml.ToString())
    
    $Caption = 'Get-Atws{0}' -F $Entity
    $VerboseDescrition = '{0}: About to run a query for Autotask.{1} using Filter {{{2}}}' -F $Caption, $Entity, ($Filter -join ' ')
    $VerboseWarning = '{0}: About to run a query for Autotask.{1} using Filter {{{2}}}. Do you want to continue?' -F $Caption, $Entity, ($Filter -join ' ')
  

    If ($PSCmdlet.ShouldProcess($VerboseDescrition, $VerboseWarning, $Caption))
    { 
    
      Write-Debug ('{0}: Adding looping construct to query to handle more than 500 results.' -F $MyInvocation.MyCommand.Name)
    
      # Native XML is rather tedious...
      $field = $QueryXml.CreateElement('field')
      $expression = $QueryXml.CreateElement('expression')
      $expression.SetAttribute('op','greaterthan')
      $expression.InnerText = 0
      $field.InnerText = 'id'
      [void]$field.AppendChild($expression)
    
      $FirstPass = $True
    
      
      Do 
      {
        Write-Verbose ('{0}: Passing QueryXML to Autotask API' -F $MyInvocation.MyCommand.Name)
        $lastquery = $atws.query($QueryXml.InnerXml)

        If ($lastquery.Errors.Count -gt 0)
        {
          Foreach ($AtwsError in $lastquery.Errors)
          {
            Write-Error $AtwsError.Message
          }
          Return
        }
        $result += $lastquery.EntityResults
        $UpperBound = $lastquery.EntityResults[$lastquery.EntityResults.GetUpperBound(0)].id
        $expression.InnerText = $UpperBound
        If ($FirstPass)
        {
          # Insert looping construct into query
          [void]$QueryXml.queryxml.query.AppendChild($field)
          $FirstPass = $False        
        }
      }
      Until ($lastquery.EntityResults.Count -lt 500)
      
      
    }
    
    # Datetimeparameters
    $Fields = Get-AtwsFieldInfo -Entity $Entity
    $DateTimeParams = $Fields.Where({$_.Type -eq 'datetime'}).Name
    
    # Expand UDFs by default
    # Normalize dates (convert to local time). EVery datetime field ever returned
    # By the API is in CEST.
    Foreach ($Item in $Result)
    {
      # Any userdefined fields?
      If ($Item.UserDefinedFields.Count -gt 0)
      { 
        # Expand User defined fields for easy filtering of collections and readability
        Foreach ($UDF in $Item.UserDefinedFields)
        {
          # Make names you HAVE TO escape...
          $UDFName = '#{0}' -F $UDF.Name
          Add-Member -InputObject $Item -MemberType NoteProperty -Name $UDFName -Value $UDF.Value -Force
        }  
      }
      
      # Adjust TimeZone on all DateTime properties
      # Dates RETURNED by the API are always in CEST. Add timezone difference
      # to get local time
      Foreach ($DateTimeParam in $DateTimeParams) {
      
        # Get the datetime value
        $Value = $Item.$DateTimeParam
                
        # Skip if parameter is empty
        If (-not ($Value)) {
          Continue
        }
        # Yes, you really have to ADD the difference
        $Item.$DateTimeParam  = $Value.AddHours($script:ESToffset)
      }
    }
  }
  
  End
  { 
    Write-Debug ('{0}: End of function' -F $MyInvocation.MyCommand.Name)
    Return $result
  }
  
}