$TotalOpenTickets = invoke-RestMethod "http://helpdesk.internalwebhd.org/helpdesk/WebObjects/Helpdesk.woa/ra/Tickets/group?page=1&limit=250&apiKey=keygeneratedfromwhd"
[int]$TotalOpenTicketsInt = $TotalOpenTickets.length

# Tickets at HS
$HSTickets = invoke-RestMethod "http://helpdesk.internalwebhd.org/helpdesk/WebObjects/Helpdesk.woa/ra/Tickets/group?page=1&limit=250&qualifier=(location.locationName%3D%27High%20School%27)&apiKey=keygeneratedfromwhd"
[int]$HSTicketsInt = $HSTickets.length

# Tickets at MS
$MSTickets = invoke-RestMethod "http://helpdesk.internalwebhd.org/helpdesk/WebObjects/Helpdesk.woa/ra/Tickets/group?page=1&limit=250&qualifier=(location.locationName%3D%27Middle%20School%27)&apiKey=keygeneratedfromwhd"
[int]$MSTicketsInt = $MSTickets.length

#Tickets at ELE1
$Ele1Tickets = invoke-RestMethod "http://helpdesk.internalwebhd.org/helpdesk/WebObjects/Helpdesk.woa/ra/Tickets/group?page=1&limit=250&qualifier=(location.locationName%3D%27Ele1%27)&apiKey=keygeneratedfromwhd"
[int]$PGTicketsInt = $PGTickets.length

#Tickets at ELE2
$Ele2Tickets = invoke-RestMethod "http://helpdesk.internalwebhd.org/helpdesk/WebObjects/Helpdesk.woa/ra/Tickets/group?page=1&limit=250&qualifier=(location.locationName%3D%27Ele2%27)&apiKey=keygeneratedfromwhd"
[int]$LETicketsInt = $LETickets.length

#Tickets at ELE3
$Ele3Tickets = invoke-RestMethod "http://helpdesk.internalwebhd.org/helpdesk/WebObjects/Helpdesk.woa/ra/Tickets/group?page=1&limit=250&qualifier=(location.locationName%3D%27Ele3%27)&apiKey=keygeneratedfromwhd"
[int]$MFTicketsInt = $MFTickets.length

$date = Get-Date -format "yyyy-MM-ddT00:00:00Z"
$ClosedTickets = invoke-RestMethod "http://helpdesk.internalwebhd.org/helpdesk/WebObjects/Helpdesk.woa/ra/Tickets/?page=1&limit=250&qualifier=(statustype.statusTypeName%20%3D%20%27Closed%27%20AND%20(lastUpdated%20%3E%20%27$date%27))&apiKey=keygeneratedfromwhd"
[int]$ClosedTicketsInt = $ClosedTickets.length

##################
# Write Database #
##################

$PlainPassword = "passwordforinsertuser"
$SecurePassword = $PlainPassword | ConvertTo-SecureString -AsPlainText -Force
$UserName = "powershell-write"
$Credentials = New-Object System.Management.Automation.PSCredential -ArgumentList $UserName, $SecurePassword

Connect-MySqlServer -Credential $Credentials -Server 'localhost' -Database whdstats
Invoke-MySqlQuery -Query "INSERT INTO whdstats.tbl_whdstats (col_currentTicketCount,col_hsTicketCount,col_msTicketCount,col_ele1TicketCount,col_ele2TicketCount,col_ele3TicketCount,col_closedToday) VALUES ($TotalOpenTicketsInt,$HSTicketsInt,$MSTicketsInt,$Ele1TicketsInt,$Ele2TicketsInt,$Ele3TicketsInt,$ClosedTicketsInt);"
Disconnect-MySqlServer