<#
.SYNOPSIS
Create Excel workbooks for Managers with Direct Reports.

.DESCRIPTION
Used for auditing group membership for each Direct Report, and compiling the data into one excel document for each Manager.
All Direct Reports have an individual Sheet. Each manager will have their own workbook.

This command can be run for all managers in an organization, or a single manager.
By default, all AD Users with Direct Reports will be processed.

Use the -ManagerUsername parameter to specify an individual manager by username.
Use the -Path parameter to specify a destination folder for generated Excel workbooks.
Use the -ShowProgress parameter to display a progress bar during processing.

By default, the Excel workbooks will be saved in the current PowerShell Working Directory. ($pwd)
To specify a location, use the -Path parameter.

The script can also attach and email the Excel workbook to the manager using the -EmailToManager parameter.
IMPORTANT: Be sure to enter your Email Server information in the script.

Try 'Get-Help Get-ManagerReport -Examples' for more information.
Use the -Verbose flag for progress information.

.EXAMPLE
Get-ManagerReport

Queries Active Directory for all users with Direct Reports.
Gathers all Groups for each Direct Report, and creates a workbook for each manager, saving at the default location.

.EXAMPLE
Get-ManagerReport -ShowProgress

Queries Active Directory for all users with Direct Reports.
Gathers all Groups for each Direct Report, and creates a workbook for each manager, saving at the default location.
Displays a progress bar of current progress.

.EXAMPLE
Get-ManagerReport -ManagerUsername jsmith

Queries Active Directory for all Direct Reports of John Smith (username jsmith).
Gathers all Groups for each Direct Report, and creates a workbook for John Smith, saving at the default location.

.EXAMPLE
Get-ManagerReport -ManagerUsername jsmith -Path C:\Temp\Reports

Queries Active Directory for all Direct Reports of John Smith (username jsmith).
Gathers all Groups for each Direct Report, and creates a workbook for John Smith, saving in C:\Temp\Reports.

.EXAMPLE
Get-ManagerReport -path "\\Server\Manager Reports" -EmailToManager

Queries Active Directory for all users with Direct Reports.
Gathers all Groups for each Direct Report, and creates a workbook for each manager, saving in \\Server\Manager Reports.
Emails each workbook to it's respective manager.

.EXAMPLE
Get-ManagerReport -ManagerUsername jsmith -Path C:\Temp\Reports -EmailToManager

Queries Active Directory for all Direct Reports of John Smith (username jsmith).
Gathers all Groups for each Direct Report, and creates a workbook for John Smith, saving in C:\Temp\Reports.
Emails the created workbook to John Smith.

.NOTES
Author: Dom Ruggeri, 2019

#>

function Get-ManagerReport {
    [cmdletbinding()]

    Param (
        [Parameter(Position = 0)]
        [string]$ManagerUsername,

        [Parameter(Position = 1)]
        [validatePattern("(^\\\\|[A-Z]\:\\|\.\\).*")]
        [string]$Path = $PWD.Path,

        [Parameter()]
        [switch]$EmailToManager,

        [Parameter()]
        [switch]$ShowProgress
    )

    ############################################################################

    # Enter your Email Settings here:
    $smtpServer = "Relay.yourdomain.com"
    $From = "Direct Reports <DirectReports@yourdomain.com>"
    $Subject = "Direct Reports Group List - $(Get-date)"

    # Note: To CC an email address, you must also uncomment line 194
    # $CC = "youremail@yourdomain.com"

    # Enter your preferred Excel Table Style here:
    $TableStyle = "Medium2"

    ############################################################################

    If (!(Get-Module -ListAvailable -Name ImportExcel)){
        try {
            Write-Verbose "ImportExcel module not found. Attempting installation." -Verbose
            Install-Module -Name ImportExcel -Force -ErrorAction Stop
            Write-Verbose "ImportExcel successfully installed." -Verbose
        }
        catch {
            Write-Verbose "ImportExcel could not be installed for the following reason:" -Verbose
            Write-Error $_
            break
        }
    }

    If ($ManagerUsername){
        $ADUserParam = @{
            Properties = "DirectReports","emailaddress"
            Identity = $ManagerUsername
        }
    }
    else{
        $ADUserParam = @{
            Properties = "DirectReports","emailaddress"
            filter = {DirectReports -like '*'}
        }
    }

    $Path = $Path.TrimEnd("\")

    Write-Verbose "Gathering All Manager Information."
    $Managers = Get-ADUser @ADUserParam
    $ManagerCount = $managers.Count
    $ManagerCurrent = 0
    Write-Verbose "$ManagerCount Managers Found."

    foreach ($Manager in $Managers){
        Write-Verbose "Processing $($Manager.Name)."

        $ReportName = "DirectReports-$($Manager.samaccountname).xlsx"

        $ManagerPath = "$Path\$ReportName"
        $DRCount = ($Manager.DirectReports | measure).Count
        Write-Verbose "[$($manager.name)] $DRCount Direct Reports Found."

        if ($ShowProgress -and $ManagerCount -gt 1){
            $ManagerCurrent++
            $ManagerPercent = [math]::Round(($ManagerCurrent/$ManagerCount)*100)
            Write-Progress -Activity "Processing $($Manager.Name)..." -Status "$ManagerPercent% Complete - ($ManagerCurrent/$ManagerCount)" -Id 1 -PercentComplete $ManagerPercent
        }

        $DRCurrent = 0

        Foreach ($DirectReport in $Manager.DirectReports){
            $DR = Get-ADUser $DirectReport
            Write-Verbose "[$($manager.name)] Processing Direct Report $($DR.Name)."

            if ($ShowProgress -and $DRCount -gt 1){
                $DRCurrent++
                $DRPercent = [math]::Round(($DRCurrent/$DRCount)*100)
                Write-Progress -Activity "Processing $($DR.Name)..." -Status "$DRPercent% Complete - ($DRCurrent/$DRCount)" -ParentId 1 -PercentComplete $DRPercent
            }

            $DRGroups = Get-ADPrincipalGroupMembership $DR

            $SheetParams = @{
                Path = $ManagerPath
                WorksheetName = $DR.name
                TableName = $DR.Samaccountname
                TableStyle = $TableStyle
                FreezeTopRow = $true
                AutoSize = $true
                AutoFilter = $true
                WarningAction = "SilentlyContinue"
            }

            Write-Verbose "[$($manager.name)] Exporting Excel Sheet for Direct Report $($DR.Name)."
            $DRGroups | Select-Object Name,GroupCategory,DistinguishedName | Export-Excel @SheetParams
            Write-Verbose "[$($manager.name)] Direct Report $($DR.Name) completed."
        }

        Write-Verbose "[$($manager.name)] Report Completed."
        Write-Verbose "[$($manager.name)] Report Path: $ManagerPath."

        if ($EmailToManager){
            if ($Manager.emailaddress){
                Write-Verbose "[$($manager.name)] Emailing Report to $($Manager.emailaddress)"

                $EmailParams = @{
                    To = $Manager.emailaddress
                    From = $From
                    Subject = $Subject
                    smtpserver = $smtpServer
                    Attachments = $ManagerPath
                    #cc = $cc
                }
                Send-MailMessage @EmailParams
            }
            else {
                Write-Verbose "[$($manager.name)] Could not find email address for $($manager.name)"
            }
        }
    }
    Write-Verbose "Complete: All Manager Direct Reports Processed."
}