There are some random bits in this script. Only the ones relating to the Backup are proven to work. The rest is a hash tag of code, captured for further use, when I have the time to make it work :).
==============================================================
Function DailyBackup {
#FTP Server Information – SET VARIABLES
$net = $(New-Object -ComObject WScript.Network)
$ftp = “FTPServer”
$user = ‘UserName’
$pass = ‘PassWord’
$folder = ‘/’
$target = “A:\DestinationFolder”
$RootFolder = “A:\RootFolder”
$DestinationFile = “$RootFolder\Daily_Backup_Report.xlsx”
# $yesterday = [DateTime]::Today.AddDays(-1).ToString(“ddMMyyyy”)
# $archive = [DateTime]::Today.AddDays(-2).ToString(“ddMMyyyy”)
$date = get-date -format d
$SourceFile = “$yesterday.csv”
$SourcePath = “$RootFolder\$SourceFile”
$WorkSheet1 = “$yesterday”
$Range1 = “A1:B1”
$Range2 = “A1”
$smtpServer = “OutBoundMailServer”
$fromadd = “FromEmailAddress”
$email1 = “ToEmailAddress”
#SET CREDENTIALS
$credentials = new-object System.Net.NetworkCredential($user, $pass)
function Get-FtpDir ($url,$credentials) {
$request = [Net.WebRequest]::Create($url)
$request.Method = [System.Net.WebRequestMethods+FTP]::ListDirectory
if ($credentials) { $request.Credentials = $credentials }
$response = $request.GetResponse()
$reader = New-Object IO.StreamReader $response.GetResponseStream()
$reader.ReadToEnd()
$reader.Close()
$response.Close()
}
$Net.MapNetworkDrive(“A:”, “\\ComputerName\Share”)
#Remove-Item $SourcePath
#SET FOLDER PATH
$folderPath= $ftp + “/” + $folder + “/”
$Allfiles=Get-FTPDir -url $folderPath -credentials $credentials
$files = ($Allfiles -split “`r`n”)
$files
$webclient = New-Object System.Net.WebClient
$webclient.Credentials = New-Object System.Net.NetworkCredential($user,$pass)
$counter = 0
foreach ($file in ($files | where {$_ -like “$yesterday.csv”})){
$source=$folderPath + $file
$destination = $target + $file
$webclient.DownloadFile($source, $destination)
}
#PRINT FILE NAME AND COUNTER
If (Test-Path “$target\$yesterday.csv”) {
# Merge CSV With Report
$Excel = New-Object -ComObject excel.application
$Excel.visible = $false
$Workbook = $excel.Workbooks.open($SourcePath)
$Worksheet = $Workbook.WorkSheets.item($WorkSheet1)
$worksheet.activate()
$Range = $WorkSheet.Range($Range1).EntireColumn
$Range.Copy()
$Workbook = $excel.Workbooks.open($DestinationFile)
$Worksheet = $Workbook.WorkSheets.item($WorkSheet1)
$worksheet.activate()
$NewRange = $Worksheet.Range($Range2)
$Worksheet.Paste()
$workbook.Save()
$Excel.Quit()
Remove-Variable -Name excel
[gc]::collect()
[gc]::WaitForPendingFinalizers()
# Write-host “File for Today found on FTP Site” -ForegroundColor green
Move-Item “$target\$archive.csv” “$target\Archive 2017\Core Files\”
}
else {
# write-host “vBackup file not uploaded Today, Pls Check” -ForegroundColor yellow
$msg = new-object Net.Mail.MailMessage
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
#Mail sender
$msg.From = $fromadd
#mail recipient
$msg.To.Add($email1)
$msg.Subject = “Alert !!! – Daily Backup File Could Not Be Located !!!”
$msg.Body = “Please check BackupServerName. Daily Backup CSV was not downloaded from FTP Server.”
$smtp.Send($msg)
}
$Net.RemoveNetworkDrive(“A:”)
}
Function DailyBackupUpdate {
#FTP Server Information – SET VARIABLES
$net = $(New-Object -ComObject WScript.Network)
$ftp = “ftp://FTPServer”
$user = ‘UserName’
$pass = ‘PassWord’
$folder = ‘/’
$target = “A:\TargetFolder”
$RootFolder = “A:\RootFolder”
$DestinationFile = “$RootFolder\Daily_Backup_Report.xlsx”
# $yesterday = [DateTime]::Today.AddDays(-1).ToString(“ddMMyyyy”)
# $archive = [DateTime]::Today.AddDays(-2).ToString(“ddMMyyyy”)
$date = [DateTime]::Today.ToString(“ddMMyyyy”)
$SourceFile = “$date.csv”
$SourcePath = “$RootFolder\$SourceFile”
$WorkSheet1 = “$date”
$WorkSheet2 = “$date”
$Range1 = “A1:B1”
$Range2 = “A1”
$smtpServer = “OutBoundMailServer”
$fromadd = “FromEmailAddress”
$email1 = “ToEmailAddress”
#SET CREDENTIALS
$credentials = new-object System.Net.NetworkCredential($user, $pass)
function Get-FtpDir ($url,$credentials) {
$request = [Net.WebRequest]::Create($url)
$request.Method = [System.Net.WebRequestMethods+FTP]::ListDirectory
if ($credentials) { $request.Credentials = $credentials }
$response = $request.GetResponse()
$reader = New-Object IO.StreamReader $response.GetResponseStream()
$reader.ReadToEnd()
$reader.Close()
$response.Close()
}
$Net.MapNetworkDrive(“A:”, “\\ComputerName\Share”)
Remove-Item $SourcePath
#SET FOLDER PATH
$folderPath= $ftp + “/” + $folder + “/”
$Allfiles=Get-FTPDir -url $folderPath -credentials $credentials
$files = ($Allfiles -split “`r`n”)
$files
$webclient = New-Object System.Net.WebClient
$webclient.Credentials = New-Object System.Net.NetworkCredential($user,$pass)
$counter = 0
foreach ($file in ($files | where {$_ -like “$date.csv”})){
$source=$folderPath + $file
$destination = $target + $file
$webclient.DownloadFile($source, $destination)
}
#PRINT FILE NAME AND COUNTER
#PRINT FILE NAME AND COUNTER
If (Test-Path “$target\$date.csv”) {
# Merge CSV With Report
$Excel = New-Object -ComObject excel.application
$Excel.visible = $false
$Workbook = $excel.Workbooks.open($SourcePath)
$Worksheet = $Workbook.WorkSheets.item($WorkSheet1)
$worksheet.activate()
$Range = $WorkSheet.Range($Range1).EntireColumn
$Range.Copy()
$Workbook = $excel.Workbooks.open($DestinationFile)
$Worksheet = $Workbook.WorkSheets.item($WorkSheet1)
$worksheet.activate()
$NewRange = $Worksheet.Range($Range2)
$Worksheet.Paste()
$workbook.Save()
$Excel.Quit()
Remove-Variable -Name excel
[gc]::collect()
[gc]::WaitForPendingFinalizers()
#Write-host “File for Today found on FTP Site” -ForegroundColor green
#Move-Item “$target\$archive.csv” “$target\Archive 2017\Core Files\”
}
else {
# write-host “vBackup file not uploaded Today, Pls Check” -ForegroundColor yellow
$msg = new-object Net.Mail.MailMessage
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
#Mail sender
$msg.From = $fromadd
#mail recipient
$msg.To.Add($email1)
$msg.Subject = “Alert !!! – Daily Backup File Could Not Be Located !!!”
$msg.Body = “Please check BackupServername. Daily Backup Update CSV was not downloaded from FTP Server.”B
$smtp.Send($msg)
}
$Net.RemoveNetworkDrive(“A:”)
}
Function CleanUpFiles-FTP {
#—– define parameters —–#
#—– get current date —-#
$Now = Get-Date
#—– define amount of days —-#
$Days = “7”
#—– define folder where files are located —-#
$TargetFolder = “TargetFolder”
#—– define extension —-#
$Extension = “*.csv”
#—– define LastWriteTime parameter based on $Days —#
$LastWrite = $Now.AddDays(-$Days)
#—– get files based on lastwrite filter and specified folder —#
$Files = Get-Childitem $TargetFolder -Include $Extension -Recurse | Where {$_.LastWriteTime -le “$LastWrite”}
foreach ($File in $Files) {
if ($File -ne $NULL) {
write-host “Deleting File $File” -ForegroundColor “DarkRed”
Remove-Item $File.FullName | out-null
}
else {
Write-Host “No more files to delete!” -foregroundcolor “Green”
}
}
}
Function CleanUpFiles-HTML {
#—– define parameters —–#
#—– get current date —-#
$Now = Get-Date
#—– define amount of days —-#
$Days = “21”
#—– define folder where files are located —-#
$TargetFolder = “C:\SourceFolder”
#—– define extension —-#
$Extension = “*.html”
#—– define LastWriteTime parameter based on $Days —#
$LastWrite = $Now.AddDays(-$Days)
#—– get files based on lastwrite filter and specified folder —#
$Files = Get-Childitem $TargetFolder -Include $Extension -Recurse | Where {$_.LastWriteTime -le “$LastWrite”}
foreach ($File in $Files) {
if ($File -ne $NULL) {
write-host “Deleting File $File” -ForegroundColor “DarkRed”
Remove-Item $File.FullName | out-null
}
else {
Write-Host “No more files to delete!” -foregroundcolor “Green”
}
}
}
Function CleanUpFiles-CSV {
#—– define parameters —–#
#—– get current date —-#
$Now = Get-Date
#—– define amount of days —-#
$Days = “14”
#—– define folder where files are located —-#
$TargetFolder = “C:\SourceFiles”
#—– define extension —-#
$Extension = “*.csv”
#—– define LastWriteTime parameter based on $Days —#
$LastWrite = $Now.AddDays(-$Days)
#—– get files based on lastwrite filter and specified folder —#
$Files = Get-Childitem $TargetFolder -Include $Extension -Recurse | Where {$_.LastWriteTime -le “$LastWrite”}
foreach ($File in $Files) {
if ($File -ne $NULL) {
write-host “Deleting File $File” -ForegroundColor “DarkRed”
Remove-Item $File.FullName | out-null
}
else {
Write-Host “No more files to delete!” -foregroundcolor “Green”
}
}
}
Function Get-ShuffledArray
{
param(
[Array]$gnArr
)
$len = $gnArr.Length;
while($len)
{
$i = Get-Random ($len –);
$tmp = $gnArr[$len];
$gnArr[$len] = $gnArr[$i];
$gnArr[$i] = $tmp;
}
return $gnArr
}
Function Get-RandCharacters() {
Param(
[int]$length=10,
[string[]]$sourcedata
)
For ($loop=1; $loop –le $length; $loop++) {
$TempPassword+=($sourcedata | GET-RANDOM)
}
return $TempPassword
}
Function Capitalize-nthLetter($word, $pos){
$arr=($word)[0..$word.Length]
$arr[$pos] = $($arr[$pos].ToString()).toupper()
$arr -join “”
}
#http://superuser.com/questions/611986/how-to-prevent-carriage-return-being-copied-to-clipboard-in-powershell
function Out-ClipboardText
{
param(
[Parameter(
Position=0,
Mandatory=$true,
ValueFromPipeline=$true)
]
[String]$text
)
process
{
powershell -sta -noprofile -command “add-type -an system.windows.forms; [System.Windows.Forms.Clipboard]::SetText(‘$text’)”
}
}
Function Invoke-Menu {
[cmdletbinding()]
Param(
[Parameter(Position=0,Mandatory=$True,HelpMessage=”Enter your menu text”)]
[ValidateNotNullOrEmpty()]
[string]$Menu,
[Parameter(Position=1)]
[ValidateNotNullOrEmpty()]
[string]$Title = “My Menu”,
[Alias(“cls”)]
[switch]$ClearScreen
)
#clear the screen if requested
if ($ClearScreen) {
Clear-Host
}
#build the menu prompt
$menuPrompt = $title
#add a return
$menuprompt+=”`n”
#add an underline
$menuprompt+=”=”*$title.Length
#add another return
$menuprompt+=”`n”
#add the menu
$menuPrompt+=$menu
Read-Host -Prompt $menuprompt
} #end function
Function Get-Holidays([int]$CalYear){
$import = “”
#Get a random filename ending in .CSV
$TempName = $env:temp+[System.IO.Path]::GetRandomFileName()
$TempName = $tempName.Substring(0, $TempName.LastIndexOf(‘.’))+”.csv”
#Capture existing Excel PIDs so you can slam it closed later. Three lines by
#Alex K. Angelopoulos, http://www.vistax64.com/powershell/241658-cant-get-excel-exe-close.html
$before = @(Get-Process [e]xcel | %{$_.Id})
$oXL = New-Object -comobject Excel.Application
$ExcelId = Get-Process excel | %{$_.Id} | ?{$before -notcontains $_}
$oXL.Visible = $False
$oWKS = $oXL.Workbooks.Add(1)
$oXL.ActiveSheet.name = “Table”
$sheet = $oXL.activesheet
#Go to the website at http://www.timeanddate.com/holidays/ to see additional country URLs
#Example, for UK it is www.timeanddate.com/holidays/uk/, Canada is www.timeanddate.com/holidays/Canada/
$QTable = $sheet.QueryTables.Add(“URL;http://www.timeanddate.com/holidays/us/$calYear”,$sheet.Application.Range(“A1”) )
$QTable.Name = $CalYear
$QTable.FieldNames = $True
$QTable.RowNumbers = $False
$QTable.FillAdjacentFormulas = $False
$QTable.PreserveFormatting = $True
$QTable.RefreshOnFileOpen = $False
$QTable.BackgroundQuery = $True
$QTable.RefreshStyle = 1
$QTable.SavePassword = $False
$QTable.SaveData = $True
$QTable.AdjustColumnWidth = $True
$QTable.RefreshPeriod = 0
$QTable.WebPreFormattedTextToColumns = $True
$QTable.WebConsecutiveDelimitersAsOne = $True
$QTable.WebSingleBlockTextImport = $False
$QTable.WebDisableDateRecognition = $False
$QTable.WebDisableRedirections = $False
#this and the out null stuff is to try to avoid “true from being echoed to the console
$QTable.Refresh() | Out-Null
Sleep -seconds 1
$bRefresh = $true
While ($bRefresh){
$bRefresh = $QTable.Refreshing
Sleep -Milliseconds 500
}
#Cleanup used parts of sheet
$range = $sheet.Cells.Item(2,1).entirerow
$range.delete() | Out-Null
#save as type 6 — CSV file using temp name
$oXL.ActiveWorkBook.Saveas($TempName,6)
$oxl.Quit()
#Polite release of oXL com objects
while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($oxl)){}
#Then, just in case, kill only the instance of Excel we identified above by PID
Stop-Process -Id $ExcelId -Force -ErrorAction SilentlyContinue
$import = Import-Csv $TempName
sleep -Seconds 1
Remove-Item $TempName -Force
#could quit here, but I like the date to have the year information, plus cleaner column names
$HDList = ForEach ($holiday in $import){
[PSCustomObject]@{
HolidayName = $holiday.’Holiday Name’
Date = Get-date ([string]$holiday.Date+$calYear)
Type = $holiday.’Holiday Type’
Observed = $holiday.’Where it is observed’
}
}
$HDList
}
Function Main (){
#Get a word at least 8 characters from randomword.setgetgo.com
do
{
[string]$RandWord = ($WebClient.DownloadString(“http://randomword.setgetgo.com/get.php”)).trim()
}
Until ($RandWord.Length -ge $minWordLen )
#Pick a random number between zero and the Maxlength -1
$maxLen = $RandWord.Length -1
$i = get-Random -Minimum 0 -Maximum ($maxLen -1)
#Capitalize a letter
$RandWord = Capitalize-nthLetter $RandWord $i
#Get a number
$Nums= GET-RandCharacters -length $numcount -sourcedata $ASCIINumbers
#Get a special character
$Spec = GET-RandCharacters -length $speccount -sourcedata $ASCIISpecial
#combine, ensuring no extra lines
$txt = “$Nums$Spec”.trim()
$Txt = [regex]::split($txt,””)
#randomize order of characters
$RandTxt =Get-ShuffledArray $Txt
$ToEnd = ($RandTxt -join(“”)).Trim()
return “$RandWord$ToEnd”
}
Function PopUpBox {
[void] [System.Reflection.Assembly]::LoadWithPartialName(“System.Drawing”)
[void] [System.Reflection.Assembly]::LoadWithPartialName(“System.Windows.Forms”)
$objForm = New-Object System.Windows.Forms.Form
$objForm.Text = “Holiday Days Adjustment”
$objForm.Size = New-Object System.Drawing.Size(300,200)
$objForm.StartPosition = “CenterScreen”
$objForm.KeyPreview = $True
$objForm.Add_KeyDown({if ($_.KeyCode -eq “Enter”) {
New-Variable -Scope global -Name result -Value $objTextBox.Text -Force
$objForm.Close()
}
})
$objForm.Add_KeyDown({if ($_.KeyCode -eq “Escape”)
{$objForm.Close()}})
$OKButton = New-Object System.Windows.Forms.Button
$OKButton.Location = New-Object System.Drawing.Size(75,120)
$OKButton.Size = New-Object System.Drawing.Size(75,23)
$OKButton.Text = “OK”
$OKButton.Add_Click({
New-Variable -Scope global -Name result -Value $objTextBox.Text -Force
$objForm.Close()
})
$objForm.Controls.Add($OKButton)
$CancelButton = New-Object System.Windows.Forms.Button
$CancelButton.Location = New-Object System.Drawing.Size(150,120)
$CancelButton.Size = New-Object System.Drawing.Size(75,23)
$CancelButton.Text = “Cancel”
$CancelButton.Add_Click({$objForm.Close()})
$objForm.Controls.Add($CancelButton)
$objLabel = New-Object System.Windows.Forms.Label
$objLabel.Location = New-Object System.Drawing.Size(10,20)
$objLabel.Size = New-Object System.Drawing.Size(280,20)
$objLabel.Text = “Please Enter The Number Of Days:”
$objForm.Controls.Add($objLabel)
$objTextBox = New-Object System.Windows.Forms.TextBox
$objTextBox.Location = New-Object System.Drawing.Size(10,40)
$objTextBox.Size = New-Object System.Drawing.Size(260,20)
$objForm.Controls.Add($objTextBox)
$objForm.Topmost = $True
$objForm.Add_Shown({$objForm.Activate()})
[void] $objForm.ShowDialog()
}