Author Posts

September 19, 2017 at 1:10 pm

I have a csv file that contains the following details
samaccountname,extensionattribute5
User01,22/09/2017
User02,22/09/2017
User03,22/09/2017
User04,23/09/2017
User05,23/09/2017
User06,24/09/2017
User07,24/09/2017
User08,24/09/2017
User09,25/09/2017
User10,25/09/2017
User11,
User12,
User13,

I'm looking for the ability for the script to assign an available date (no earlier than a week) to those that don't have Extensionattribute5 populated.
The day cannot have more than 3 users assigned

The script needs to:
Lists all the dates scheduled.
Group them
Checks if there are less than 3 users.
If yes, then get the number of free slots, assign that date to the number of users.
If no, find the next available date

Remove-Variable *
cls
Set-Location $PSScriptRoot

 $1WeekAfterToday = (Get-date ).AddDays(8)
        If  ($1WeekAfterToday.DayOfWeek -eq 'Saturday')
            {
            "Scheduled date is Saturday adding one more day" | Write-Host -ForegroundColor Cyan
            $1WeekAfterToday = $1WeekAfterToday.AddDays('1') 
            }
        ElseIf ($1WeekAfterToday.DayOfWeek -eq 'Friday')
            {
            "Scheduled date is Friday scheduling for Sunday" | Write-Host -ForegroundColor Cyan
            $1WeekAfterToday = $1WeekAfterToday.AddDays('2')
            }
$1WeekAfterToday = Get-Date $1WeekAfterToday -DisplayHint Date
"Potential date is $($1WeekAfterToday.ToLongDateString())" | Write-Host -ForegroundColor Cyan

#Get Users in Group
$sourceUsers = Import-Csv .\sourceUsers.csv -Verbose

Start-Sleep -Seconds 1 -Verbose
#Get Users that are scheduled
$PreScheduledUsers = Import-Csv .\sourceUsers.csv | Where-Object {$_.extensionattribute5 -ne ""} |
Select-Object *,@{
    'Name' = 'SchedDate'
    'Expression' = { ([datetime]::ParseExact($_.extensionattribute5,"dd/MM/yyyy",[System.Globalization.CultureInfo]::InvariantCulture)) }
    } | 
    Sort SchedDate

#Group Dates
$AllDates =  $PreScheduledUsers | Group SchedDate 

#Check if date is already in schedule

#DO {
Sleep 1
#$Avail = 
ForEach($datum in $AllDates)
    
    {    
        Write-Host "`r`n01. CHECKING $($1WeekAfterToday.ToShortDateString()) to $((Get-date $datum.Name).ToShortDateString())"
        If ($1WeekAfterToday.Date -eq (Get-date $datum.Name).Date ) 
            {
            Write-Host "`r`nFOUND A MATCH for $($1WeekAfterToday.Date)"
            IF ($datum.Count -lt 3)
                {
                Write-Host "LESS than 3"
                (Get-date $datum.Name)
                break
                }
            ELSE
                {
                Write-Host "`r`nDATE NOt available less than ..checking next day"
                $1WeekAfterToday = $1WeekAfterToday.AddDays(1)
                continue
                }#continue
           
            #Break
            }
           
        ELSE
            {
            Write-Host "`r`nNo MATCH for $($1WeekAfterToday.Date). Setting $1WeekAfterToday"
            $1WeekAfterToday
            #Break
            continue
            
            }
            #>
    }
    
  #  }
    #Until ($avail)
    $avail | Write-Host -ForegroundColor Yellow

September 19, 2017 at 2:10 pm

What's your question on this?

September 19, 2017 at 4:39 pm

#Requires -Version 5
#Requires -RunAsAdministrator
#Requires -Modules SB-Tools

#region Input
[CmdletBinding(ConfirmImpact='Low')] 
Param(
    [Parameter(Mandatory=$false)][ValidateScript({Test-Path $_})][String]$SourcePath = '.\sourceUsers.csv',
    [Parameter(Mandatory=$false)][String[]]$ExpectedFields = @('samaccountname','extensionattribute5'),
    [Parameter(Mandatory=$false)][Int32]$MaxUsersPerDate = 3,
    [Parameter(Mandatory=$false)][String]$LogFile = ".\AD-Bla-$(Get-Date -Format 'ddMMMMyyyy_hh-mm-ss_tt').xlsx"
)
#endregion

#region Initialize
Install-Module SB-Tools -Force
Import-Module  SB-Tools -DisableNameChecking
#region Validate input fields
$sourceUsers = Import-Csv $SourcePath 
if ($ExpectedFields) {
    $Missing = @()
    $csvFields = ($sourceUsers | Get-Member -MemberType NoteProperty).Name 
    $ExpectedFields | % { if ($_ -notin $csvFields) { $Missing += $_ } }
    if ($Missing) {
        Write-Log 'Error: Expected',($Missing -join ', '),'fields/columns are missing from input file',$SourcePath Magenta,Yellow,Magenta,Yellow $LogFile
        break
    }
} else {
    Write-Log 'Error: Missing ''Expected Fields'' input' Magenta $LogFile
    break
} 
#endregion
#endregion

#region Identify free date slots
$DateSlotsNotFull = $sourceUsers | Group extensionattribute5 | ? { $_.Name -and $_.Count -lt $MaxUsersPerDate } | select Name,Count
if ($DateSlotsNotFull) {
    Write-Log 'Identified the following ''DateSlotsNotFull''' Green $LogFile
    Write-Log ($DateSlotsNotFull | Out-String).Trim() Cyan $LogFile
} else {
    Write-Log 'No ''DateSlotsNotFull'' identified' Green $LogFile
}
#endregion

#region Identify users without dates
$UsersWithoutDates = $sourceUsers | ? { !$_.extensionattribute5 }
if ($UsersWithoutDates) {
    Write-Log 'Identified the following ''UsersWithoutDates''' Green $LogFile
    Write-Log ($UsersWithoutDates | Out-String).Trim() Cyan $LogFile
} else {
    Write-Log 'No ''UsersWithoutDates'' identified' Green $LogFile
}
#endregion

#region Assign dates from DateSlotsNotFull to UsersWithoutDates
if ($UsersWithoutDates) {
    $NextUser = 0
    if ($DateSlotsNotFull) { 
        $DateSlots = @()
        foreach ($Item in $DateSlotsNotFull) { 
            1..($MaxUsersPerDate-$Item.Count) | % { 
                # $DateSlots +=  [DateTime]$Item.Name # Simple type casting doesn't seem to work because of the date format
                $DateSlots += [DateTime]"$($Item.Name.Split('/')[1])/$($Item.Name.Split('/')[0])/$($Item.Name.Split('/')[2])"
            }
        } 
        Write-Log 'Assigining the following Date Slots',(($DateSlots | % {$_.ToString('dd/MM/yyyy')}) -join ', ') Green,Cyan $LogFile
        $DateSlots | % {
            if ($NextUser -lt $UsersWithoutDates.Count) {
                $UsersWithoutDates[$NextUser].extensionattribute5 = $_.ToString('dd/MM/yyyy')
                Write-Log 'Assigned',$UsersWithoutDates[$NextUser].extensionattribute5,'to',$UsersWithoutDates[$NextUser].samaccountname Green,Cyan,Green,Cyan $LogFile
                $NextUser++
            } # if $NextUser
        } # $DateSlots
    } # if $DateSlotsNotFull 
} # if $UsersWithoutDates
#endregion

#region Assign new dates to UsersWithoutDates
$NextDay = (($sourceUsers | select extensionattribute5 -Unique).extensionattribute5 | 
    % { if ($_) {[DateTime]"$($_.Split('/')[1])/$($_.Split('/')[0])/$($_.Split('/')[2])"}} | 
        sort | select -Last 1).AddDays(1)
while ($UsersWithoutDates = $sourceUsers | ? { !$_.extensionattribute5 }) {
    Write-Log 'Assigining Date Slot',$NextDay.ToString('dd/MM/yyyy') Green,Cyan $LogFile
    $NextUser = 0
    1..3 | % {
        if ($sourceUsers | ? { !$_.extensionattribute5 }) {
            $UsersWithoutDates[$NextUser].extensionattribute5 = $NextDay.ToString('dd/MM/yyyy')
            Write-Log 'Assigned',$UsersWithoutDates[$NextUser].extensionattribute5,'to',$UsersWithoutDates[$NextUser].samaccountname Green,Cyan,Green,Cyan $LogFile
            $NextUser++
        }
    }
    $NextDay = $NextDay.AddDays(1) 
} 
#endregion

September 22, 2017 at 5:01 am

The aim is to find the earliest available date after a week from today.

So for eg if today is the 22nd of Sept 2017
It will check 29th Sept if there is less than 3 allocations. If so 29th Sept becomes the allocated date for that user.
If 29th is NOT available, it will increment the date by a day and check 30th Sept if less than 3 allocations and so on.

@ Sam Boutros thanks however I'm hoping to achieve this comprehensively.

September 25, 2017 at 10:50 am

Restarting requirements:
– Assign days sequentially to users
– Maximum users/day = 3
– Minimum users/day = 3 (except last assigned day)
– Next assigned day = today + 7

Is this accurate?
Does the last requirement apply to currently assigned users as well to users without dates?

October 28, 2017 at 7:05 am

1) Next available date => today + 7
2) Max users/day = 3
3) Sequence not a requirement
4) Weekends to be excluded (I can put this at a later date)

The logic needs to be similar to say a receptionist making a booking for an appointment.