The OptionKey Blog: Using PowerShell to send scheduled emails based on a php generated xml file

Laptop Lending Locker

D-Tech International has some vey nice equipment, and it works pretty well. When these were purchased they were to send out the following email notices.

When a laptop is borrowed with the date and time that it is due back
an email notice 15 minutes before it is due
an email notice if it is overdue
an email notice when it is returned

Unfortunately not all the emails worked. The 15 minute notice and the overdue notice were not working properly. Email notices were sent when the laptop was taken out and when it was returned but not at the 15 minutes before it is due or when it is overdue. Since the system is using a SirsiDynix ILS system it has a server called Horizon Web Services; which is more/less an API connector for the ILS Database.

A php script generates a XML file which we will use to generate scheduled tasks for each laptop that is borrowed. Those tasks will run at specific times to send notices out to the user that borrowed it. The notice times obviously need to be flexible and only generate and send a notice for the laptop that is due within the operating hours of the location.

To do all this we are going to use powershell. Below is how the XML file is generated and structured.

XML File

<notices>
<laptopCirc>
<displayName>Name</displayName>
<barcode>2222200xxxxxxx</barcode>
<emailAddress>email@domain.com</emailAddress>
<checkOutTime>2023-04-24T10:21:00-06:00</checkOutTime>
<timeDue>2023-04-24T14:21:00-06:00</timeDue>
<laptopName>Laptop 2</laptopName>
</laptopCirc>
</notices>

The Process:

The emails for the borrowing and the returning of the laptops work great. However because this is something I can code and control the following notices will be generated.

15 minute pre-due notice
Notice that the laptop is due
15 minute overdue notice
15 minute pre-closing notice
Laptop not returned the next day notice

There will be 11 different files. A powershell script with a bat file for executing the powershell script as to not change the default powershell security model on the system. Below is a sample of the scripts for generating the task scheduler and email notices.

The Scripts:

Obviously because of the locked down security model we are going to use task scheduler to run bat files to execute our powershell scripts. Our bat file must contain the $Path that goes to our powershell file. Below is a sample file.

The Bat Files:

@ECHO OFF

Powershell.exe -executionpolicy remotesigned -File $PATH\LaptopsDue.ps1

Powershell Files:

Generate Notices (Sample) - This is the biggest and most complex file; as this is the one that makes and removes our scheduled tasks. Below is a sample of the script which will be provided at the end of the post.

#update the generated xml file from the php script (status.xml)

$WebResponse = Invoke-WebRequest "$URL/generate.php" -UseBasicParsing

$WebResponse

#URL to get the laptops data

$url = "$URL/status.xml"

#store the laptop xml file

$filename = "$PATH\status.xml"

$webClient = new-object System.Net.WebClient

$webClient.DownloadFile( $url, $filename )

#load the xml file we downloaded

[xml]$xml = get-content "$PATH\status.xml"

#check and see if there are any checked out laptops

#if there are laptops checked out laptops

if ($xml.notices.laptopCirc -ne $null){

Write-Output "there are checked out laptops"

}else{

#if there are no checked out laptops

Write-Output "There are NO check out laptops"

exit

}

#Write-Output $xml.notices.laptopCirc

#here is where we sort the xml file

$xml.notices.laptopCirc | ForEach-Object {

###

#Conversions for date and time for creating tasks

###

$DueTime = [datetime]::ParseExact($_.timeDue.substring(0, 19), 'yyyy-MM-ddTHH:mm:ss', $null)

$PreTime = [datetime]::ParseExact($_.timeDue.substring(0, 19), 'yyyy-MM-ddTHH:mm:ss', $null)

$OverTime = [datetime]::ParseExact($_.timeDue.substring(0, 19), 'yyyy-MM-ddTHH:mm:ss', $null)

#Create Timestamps

$PreTS = $PreTime.AddSeconds(-900).ToString('MM/dd/yyyy HH:mm:ss')

$OverTS = $OverTime.AddSeconds(+900).ToString('MM/dd/yyyy HH:mm:ss')

$DueTS = $DueTime.AddSeconds(+0).ToString('MM/dd/yyyy HH:mm:ss')

#get current Date & Time

$nowTime = (Get-Date).ToString('MM/dd/yyyy HH:mm:ss').substring(11,5)

$nowDate = (Get-Date).ToString('MM/dd/yyyy HH:mm:ss').substring(0,8)

$nowTS = Get-Date -Format yyyy-MM-ddTHH:mm:ss

###TIME CHECK###

$whatDay = (get-date).DayOfWeek

if ($whatDay -match 'Monday|Tuesday|Wednesday|Thursday' -and $nowTime -gt "10:00" -and $nowTime -lt "20:45"){

Write-Output "Today is "+ $DayOfTheWeek

###Notices for Monday - Thursday

###NOTICES###

#Pre-Over Due Notice

$taskName = $_.laptopName+"_PreOverDue"

if(Get-ScheduledTask $taskName -ErrorAction Ignore) {

Write-Output "Task Found"

} else {

$actions = (New-ScheduledTaskAction -Execute '$Path\LaptopPreDue.bat' -WorkingDirectory '$PATH')

$trigger = New-ScheduledTaskTrigger -Once -At $PreTime.substring(11, 5)

$principal = New-ScheduledTaskPrincipal -UserId '$user' -RunLevel Highest

$settings = New-ScheduledTaskSettingsSet -RunOnlyIfNetworkAvailable -WakeToRun

$task = New-ScheduledTask -Action $actions -Principal $principal -Trigger $trigger -Settings $settings

#taskname

$taskName = $_.laptopName+"_PreOverDue"

}

[PSCustomObject]@{

'displayName' = $_.displayName

'barcode' = $_.barcode

'emailAddress' = $_.emailAddress

'checkOutTime' = $_.checkOutTime

'timeDue' = $_.timeDue

'laptopName' = $_.laptopName

###

# 'id' = $_.id

# 'name' = $_.name

# 'status' = $_.status

# 'checkout' = $_.checkout

# 'due' = $_.due

# 'email' = $_.email

}

Item Due Email Notice (SAMPLE)

#URL to get the laptops data

$url = "$URL/status.xml"

#store the laptop xml file

$filename = "$PATH\status.xml"

$webClient = new-object System.Net.WebClient

$webClient.DownloadFile( $url, $filename )

#load the xml file we downloaded

[xml]$xml = get-content "status.xml"

#here is where we sort the xml file

$xml.notices.laptopCirc | ForEach-Object {

#Conversions for date and time for creating tasks

$TimeDueTS = [datetime]::ParseExact($_.timeDue.substring(0, 19), 'yyyy-MM-ddTHH:mm:ss', $null)

#Timestamps

#$CurrentTimeStamp

#add time to time due if required

$DueTime = $Due_Conversion.AddSeconds(+0).ToString('MM/dd/yyyy HH:mm:ss')

#get current time

$CurrentTime = (Get-Date).ToString('MM/dd/yyyy HH:mm:ss')

$CurrentTimeStamp = Get-Date -Format yyyy-MM-ddTHH:mm:ss

$removeTask = $_.laptopName+"_Due"

$isDue = $DueTime.substring(11, 5)

#Send an email if the laptop is due

if ($CurrentTime -match $DueTime){

#send an email if the current time matches the time due. Remove the task once the email is sent

#Write-output $_.laptopName $_.emailAddress

$emailBody = "Hello "+$_.displayName+",

Your laptop is now overdue and needs to be returned to the locker. If you need help with saving your document or the return process, please ask Staff.

Laptops are loaned for a maximum of 4 hours"

$emailSubject = "Please return "+$_.laptopName+" to the locker"

$emailSubject

Send-MailMessage -Encoding UTF8 -From '$FROM ADDRESS' -To $_.emailAddress -Subject $emailSubject -Body $emailBody -smtpserver '$SMTPSERVER'

Unregister-ScheduledTask -TaskName $removeTask -Confirm:$false

}