Azure SQL Mail sp_send_dbmail replacement with onprem - code to pull from emailqueue table, query databases, send emails with attachments++

azure_email_queue_from_onprem.ps1

#2022/10/17 mdb - the goal is to be able to run this on-prem and send emails from azure. 
#                  This needs to be replaced with a function at some point, but I need email functionality right now.
#                  Tries to support as many of the standard parameters as possible.
#2023/01/06 mdb - adding maxcharlength at 200k to handle larger emails
#2023/01/17 mdb - handling double quotes in the query  - can't use replace on a null, trying REPLACE in the initial query_to_run didn't work, wound up moving it after the null check for query block
#2023/01/19 mdb - what happens if the query is empty? No file created, so it fails. Fixed.

$ErrorActionPreference="Stop" 

#First, get what's in the table to perform/mail
$HOME_DATABASE = 'mydatabase' #need this so that we can query other databases if need be. Probably of limited usefulness.
$params = @{
    'Database' = $HOME_DATABASE
    'ServerInstance' = 'mydatabase.database.windows.net'
    'Username' = 'myuser'
    'Password' = 'mypassword'
    'Query' = 'select *, @@servername as servername from EmailQueue where senttime is null'
}
$mails_to_send = Invoke-Sqlcmd @params -MaxCharLength 20000 #otherwise fields lop off at 4k


#Second, for each email, we need to get the variables, run the query if there is one, format things, send email, mark as sent
$mails_to_send | %{

$From = if ([DBNull]::Value -eq $_.from_address){"do_not_reply <Do_not_reply_$($_.servername)@null.com>"} else {$_.from_address}
$To = $_.recipients #can't do this here, we need to split it to an array
$CC = $_.cc_recipients
$BCC = $_.bcc_recipients
$ID = $_.id #queue table mail id
$Subject =  $_.email_subject
$SMTPServer = "mysmtp.null.com" 
$SMTPPort = "25"
$Body = if ([DBNull]::Value -eq $_.email_body){"    "}else{$_.email_body} #$_.email_body
$query_to_run = $_.query
$email_body_format = $_.email_body_format
$query_attachment_filename = $_.query_attachment_filename
$query_result_separator = if ([DBNull]::Value -eq $_.query_result_separator){" "}else{$_.query_result_separator}
$attach_query_result_as_file = $_.attach_query_result_as_file
$execute_query_database = $_.execute_query_database
$priority = if ($_.importance -eq $null){"Normal"}else{$_.importance} #no, I don't know why that handles differently. Need to test more.

#how to overwrite part of the splat: $params.query = 'select 1'

#splitting comma/semicolon separated email addressess into an array so that it works with send-mailmessage - appears to work
$array_TO = @()
$to.split(";",[StringSplitOptions]::RemoveEmptyEntries) | Foreach {$array_TO += "$_"}

if ([DBNull]::Value -eq $CC){}else{
    $array_CC = @()
    $cc.split(";",[StringSplitOptions]::RemoveEmptyEntries) | Foreach {$array_CC += "$_"}
    }
if ([DBNull]::Value -eq $BCC){}else{
    $array_BCC = @()
    $bcc.split(";",[StringSplitOptions]::RemoveEmptyEntries) | Foreach {$array_BCC += "$_"}
    }

#blank line here- if you need to run the foreach to set variables, end here

$queryresults = @()
#RUN THE QUERY and send the results - about half the reason we're doing this from powershell instead of the linked server
#https://zeleskitech.com/2015/03/02/null-vs-dbnull-powershell/
if ([DBNull]::Value -eq $query_to_run) {} else {  #invoke-sqlcmd returns a DBNull, not a null, and -ne didn't work, so we do an if..else
#run the query

$params.query = $query_to_run.replace('"',"'")
#note that this changes the database context, so we need to put it back at the end when we update
if ([DBNull]::Value -eq $execute_query_database) {} else {$params.Database = $execute_query_database}
$queryresults = invoke-sqlcmd @params -MaxCharLength 100000 #-OutputAs DataRows #that may be a way to force it so that one field comes up as a field, but unsure of other effects so leaving for now
#now reset it for later.
$params.database = $HOME_DATABASE


<#
#I don't know if we need to actually set it to anything for purposes of attachment. The code I have will send empty on 
#$queryresults.length

#if it's empty, set it to something?
if($null -ne $queryresults) {} else {$queryresults = $null}

#different way to invoke it to try and get results - still doesn't work.
#https://stackoverflow.com/questions/23522309/must-specify-an-object-on-piping-variable-to-get-member
#if ((Get-Member -InputObject $queryresults -MemberType NoteProperty | measure).Count -eq 0) 
#{
##do something where we set it to an array or make a custom object or something
#}


#trying to handle if there's only one object returned, since convertto-html thinks it's a text string and uses *
#if (($queryresults| Get-Member -MemberType NoteProperty | measure).Count -eq 0) 
#{
#do something where we set it to an array or make a custom object or something
#}

#>

#handle attachments - if it's 1, then we need to create a file, write the file, attach the file, then destroy the file.
if ($attach_query_result_as_file -eq 1) {
#this converts it to a CSV with no extra quotes, and "`t" makes it a tab. 
#do we even want/need that? the files I see us making are tab-separated files.
#$queryresults  | ConvertTo-CSV -Delimiter "$query_result_separator" -NoTypeInformation `
#  | % {$_ -replace  `
#  '\G(?<start>^|,)(("(?<output>[^,"]*?)"(?=,|$))|(?<output>".*?(?<!")("")*?"(?=,|$)))' `
#  ,'${start}${output}'} `
#  | Out-File c:\temp\$($query_attachment_filename) -fo -en ascii ; 

#this is a basic one that works and sends it, but there's just a one-line header and the results, no dashed line like I see from sp_send_dbmail.
#$queryresults |ConvertTo-Csv -NoTypeInformation -delimiter "$query_result_separator" | `
# ForEach-Object {$_ -Replace('"','')} | `
# Out-file c:\temp\$($query_attachment_filename) -fo -en ascii

#we'll need to handle padding later, as well as how-to-skip-headers, which is a parameter (query_result_header)
#$queryresults | Format-Table -AutoSize -Wrap -HideTableHeaders

#This one includes headers and the line of hyphens/dashes. 
#https://stackoverflow.com/questions/74117598/how-to-invoke-sqlcmd-and-export-with-headers-and-dashes-hyphens-but-tab-delimite
$queryresults |
  ConvertTo-Csv -NoTypeInformation -Delimiter "`t" |
  ForEach-Object { 
    $_ -replace '"' # output with " chars. removed
    # If it was the first, i.e. the *header* row that was just output,
    # construct and output the desired separator row.
    if ($i++ -eq 0) {
      ($_ -replace '"' -split "`t").ForEach({ '-' * $_.Length }) -join "`t"
    }
  }  |set-content c:\temp\$($query_attachment_filename)

  $attachment_filepath = @() #needs to be an array for send-mailmessage
  $attachment_filepath += 'c:\temp\' + $query_attachment_filename
  
}
else {
#if it's already formatted, pass it along, otherwise format it
#only doing this if it's not a file!
if ($queryresults -like "*<body>*"  -or $queryresults -like "*<html>*") {
    $queryresults_for_sending = $queryresults|select * -ExcludeProperty rowerror,rowstate,table,itemarray,haserrors|out-string
    }
else
    {
    #bug if only one field returned, since it returns text. Need to create custom psobject, but unsure how to do that if field name returned is dynamic
    #https://dba.stackexchange.com/questions/266717/powershell-script-to-send-an-email-with-sql-results-in-html-format
    $queryresults_for_sending = $queryresults|select * -ExcludeProperty rowerror,rowstate,table,itemarray,haserrors|convertto-html|out-string
    $queryresults_for_sending = $queryresults_for_sending.Replace("</head>", "<style> TD {padding-left: 2mm}</style></head>") #add prettier spacing
    }


$body = $body + $queryresults_for_sending
}
}


#setting up code for attachment, since it's a flag that may or may not need to be sent.
#https://stackoverflow.com/questions/14560270/send-mailmessage-attachments-will-not-accept-null-for-any-variables
$attachments = @()
if ($attach_query_result_as_file -eq 1) {
  if ([DBNull]::Value -eq $queryresults -or $queryresults -eq "" -or $queryresults -eq $null){} else
  {$attachments += 'c:\temp\' + $query_attachment_filename}
}
# Repeat for each potential parameter
$mail_params = @{}
if ($attachments.Length -gt 0) {
  $mail_params['Attachments'] = $attachments
}
#Send-MailMessage @params -BodyAsHtml –From Monitoring@CorporateActions -Priority $Priority # Other parameters
# Repeat for each potential parameter
if ($array_cc.Length -gt 0) {
    $mail_params['CC'] = $array_cc
}
if ($array_bcc.Length -gt 0) {
    $mail_params['BCC'] = $array_bcc
}
#unsure why this doesn't seem to work
if ($priority.Length -gt 0) {
    $mail_params['Priority'] = $priority
}


#need code for email body - if it's html, use "-bodyashtml". Maybe figure it out when it's about to run, see if there are tags in the email?
#doing it the crap way for now and just duplicating this block. DEADLINES.
if ($email_body_format -eq 'html' -or $body -like "*<body>*"  -or $body -like "*<html>*" -or $body -like "*<table*") {
    Send-MailMessage @mail_params -From $From -to $array_TO -Subject $Subject -Body $Body -SmtpServer $SMTPServer -port $SMTPPort -BodyAsHtml
    #and now see if it succeeded
    if (! $?) 
        {
            $logmsg = "ERROR: Cannot send email"
            throw "ERROR: Cannot send email" 
            }     
    else 
    {
    #if it succeeds, mark as done
    #overwriting the existing query so we can keep that pretty @params block up at the top intact.
    $params.query = "update EmailQueue set senttime = getdate() where id = $ID"
    invoke-sqlcmd @params
    }
}
else { #aka we do need to send it as html
    Send-MailMessage @mail_params -From $From -to $array_TO -Subject $Subject -Body $Body -SmtpServer $SMTPServer -port $SMTPPort
    #and now see if it succeeded
    if (! $?) 
        {
            $logmsg = "ERROR: Cannot send email"
            throw "ERROR: Cannot send email" 
            } 
    
    else 
    {
    #if it succeeds, mark as done
    #overwriting the existing query so we can keep that pretty @params block up at the top intact.
    $params.query = "update EmailQueue set senttime = CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Central Standard Time' AS DATETIME) where id = $ID"
    invoke-sqlcmd @params
    }

    }

#delete email attachment at this point
if ($attach_query_result_as_file -eq 1) {
    if (Test-Path "c:\temp\$query_attachment_filename") {
    remove-item "c:\temp\$query_attachment_filename" -verbose}} #needs double quotes for the substitution to work!
}