LQ##001

##CONFIGURE SMTP SETTINGS
$emailSmtpServer = "SERVER_PORT_HERE"
$emailSmtpServerPort = "587"
$emailSmtpUser = "USERNAME_HERE"
$emailSmtpPass = "PASSWORD_HERE"

##CONFIGURE EMAIL SETTINGS
$emailFrom = "person@person.com"
$emailTo = "person@person.com"
$body = "BODY_TEXT_HERE"
$subject = "SUBJECT_TEXT_HERE"

##CONFIGURE SQL CONNECTION SETTINGS
$sqlServer = "SERVER_NAME_HERE"
$sqlDatabase = "DATABASE_NAME_HERE"

##CONFIGURE SQL QUERY
$sqlQuery = @"
SQL_QUERY_HERE
"@

##CONFIGURE FOLDERPATH FOR SAVED FILES
$folderPath = "C:\reportresults\"

##CLEANUP FOLDER LOCATION
Remove-Item $folderPath -recurse

##CREATE FOLDER LOCATION
New-Item $folderPath -type directory

##CONNECT TO DATABASE AND EXECUTE COMMAND
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server="+$sqlServer+"; Database="+$sqlDatabase+"; Integrated Security=True;"

$sqlCommand = New-Object System.Data.SqlClient.SqlCommand
$sqlCommand.CommandText = $sqlQuery
$sqlCommand.Connection = $sqlConnection

$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$sqlAdapter.SelectCommand = $sqlCommand
$sqlDataSet = New-Object System.Data.DataSet
$sqlAdapter.Fill($sqlDataSet)
$sqlConnection.Close()

##SAVE RESULTS TO FILES
$filePath = $folderPath + "Report.csv"
$sqlDataSet.Tables[0] | Export-Csv -NoTypeInformation $filePath

foreach ($Row in $sqlDataSet.Tables[1].Rows)
{
	$val = $Row[1] | out-string
	$outPath = $folderPath + $row[0] + "_" + $row[2] + ".txt"
	$val | Out-File $outPath
}

##ZIP AND SAVE RESULTS TO FILE
set-alias sz "C:\Program Files (x86)\7-Zip\7z.exe"
sz a -t7z C:\reportresults\report.7z c:\reportresults\*
$emailAttachment = "C:\reportresults\report.7z"

##FINALIZE AND SEND EMAIL
$emailMessage = New-Object System.Net.Mail.MailMessage( $emailFrom , $emailTo )
$emailMessage.Subject = $subject
$emailMessage.IsBodyHtml = $true
$emailMessage.Body = $body
$emailMessage.Attachments
$attachment = New-Object System.Net.Mail.Attachment($emailattachment, 'application/x-7z-compressed')
$emailMessage.Attachments.Add($attachment)

$SMTPClient = New-Object System.Net.Mail.SmtpClient( $emailSmtpServer , $emailSmtpServerPort )
$SMTPClient.EnableSsl = $false
$SMTPClient.Credentials = New-Object System.Net.NetworkCredential( $emailSmtpUser , $emailSmtpPass );

$SMTPClient.Send( $emailMessage )

##CLEANUP FOLDER LOCATION
Remove-Item $folderPath -recurse