Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
function Get-EmployeeData {
<#
.Synopsis
Function to get Employee data from SQL database.
#>
[CmdletBinding(
DefaultParameterSetName = 'filter'
)]
param (
# Employee first name.
[Parameter(
ParameterSetName = 'list'
)]
[SupportsWildcards()]
[string]$GivenName,
# Employee last name.
[Parameter(
ParameterSetName = 'list'
)]
[SupportsWildcards()]
[string]$Surname,
# Employee information.
[Parameter(
ParameterSetName = 'list'
)]
[SupportsWildcards()]
[string]$Description,
# Employee title.
[Parameter(
ParameterSetName = 'list'
)]
[SupportsWildcards()]
[string]$Title,
# Retrieves new-hire employees.
[Parameter(
ParameterSetName = 'list'
)]
[switch]$BeforeStartDate,
# Retrieves currently hired employees.
[Parameter(
ParameterSetName = 'list'
)]
[switch]$BeforeEndDate,
# Retrieves employees that were already hired.
[Parameter(
ParameterSetName = 'list'
)]
[switch]$AfterStartDate,
# Retrieves terminated employees.
[Parameter(
ParameterSetName = 'list'
)]
[switch]$AfterEndDate,
# Retrieves employees with Id greater than provided.
[Parameter(
ParameterSetName = 'list'
)]
[int]$IdGreaterThan,
# Retrieves employees with Id lower than provided.
[Parameter(
ParameterSetName = 'list'
)]
[int]$IdLowerThan,
# Filter required to get employees data. Requires T-SQL format.
[Parameter(
ParameterSetName = 'filter',
Mandatory = $true,
HelpMessage = 'Filter required to get employees data. Requires T-SQL format.'
)]
[string]$Filter
)
$parameterMap = @{
# Parameter = SQL Column Name
GivenName = 'First Name'
Surname = 'Last Name'
Description = 'User Information Detailed'
Title = 'Title'
SamAccountName = 'Login'
Id = 'Employee Id'
}
if (-not $Filter) {
$mappedParameters = '^({0})$' -f ($parameterMap.Keys -join '|')
$queryList = New-Object System.Collections.ArrayList
switch -Regex ($PSBoundParameters.Keys) {
$mappedParameters {
$wildcard = [System.Management.Automation.WildcardPattern]$PSBoundParameters[$_]
$wql = $wildcard.ToWql()
$item = "[{0}] LIKE '{1}'" -f $parameterMap[$_], $wql
$queryList.Add($item) | Out-Null
}
^Before {
$field = $_ -replace '^Before'
$item = "[{0}] > '{1}'" -f $field, (Get-Date)
$queryList.Add($item) | Out-Null
}
^After {
$field = $_ -replace '^After'
$item = "[{0}] < '{1}'" -f $field, (Get-Date)
$queryList.Add($item) | Out-Null
}
GreaterThan$ {
$fieldMappedTo = $_ -replace 'GreaterThan$'
$field = $parameterMap[$fieldMappedTo]
$item = '[{0}] > {1}' -f $field, $PSBoundParameters[$_]
$queryList.Add($item) | Out-Null
}
LowerThan$ {
$fieldMappedTo = $_ -replace 'LowerThan$'
$field = $parameterMap[$fieldMappedTo]
$item = '[{0}] < {1}' -f $field, $PSBoundParameters[$_]
$queryList.Add($item) | Out-Null
}
}
$Filter = $queryList -join ' AND '
}
$selectList = foreach ($key in $parameterMap.Keys) {
"[{0}] AS '{1}'" -f $parameterMap.$key, $key
}
$select = $selectList -join ', '
$query = @"
SELECT
Department, StartDate, EndDate,
$select
FROM
Employees
WHERE
$Filter
"@
Write-Verbose $query
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection @(
"Server=localhost;Database=HRDB;Integrated Security=True"
)
$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter @(
$query,
$sqlConnection
)
$dataSet = New-Object System.Data.DataSet
$sqlAdapter.Fill($dataSet) | Out-Null
$SqlConnection.Close()
$dataSet.Tables[0]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.