Get SQL Procedure Results And Send CSV to SFTP

Get SQL Procedure Results And Send CSV to SFTP

# Variable Declaration Region

# WINSCP DLL Location
$WINCSPDLL = 'C:\Temp\Send-ReportToNYSFTP\WinSCPnet.dll'
# SFTP Credentials
$SFTPHostName = '***'
$SFTPHostLogin = '***'
# PPK Data
$PPKLocation = 'C:\Temp\Send-ReportToNYSFTP\Keys\***.ppk'
$PPKPassphrase = '***'

# Report Result Path
$Global:ReportResult = 'C:\Temp\Send-ReportToNYSFTP'

# Report Result CSV File
$CSV = [System.String]::Format('{0}\Data\{1}-SalesCreditAllNY.csv',$global:ReportResult,[System.DateTime]::Today.ToString('yyyyMMdd'))

# DataBase Engine Configuration
$ConnectionStrig = "Server=*hostname*,*port*;Database=dbname;uid=login;pwd=password;Integrated Security=False;"
#$QueryText = [System.String]::Format("exec dbname..sp_REPORT_*** '***', '', '', '', '', '{0}', '{0}', ''",[System.DateTime]::Today.ToString('yyyyMMdd'))

# Helper Function Region
function New-LogRecord{ 
    param(
        [System.String]$Event= [System.String]::Empty,
        [ValidateSet('TRACE','WARNING','ERROR')][System.String]$TypeOfEvent = 'TRACE',
        $OutputPath = [System.String]::Format('{0}\Logs\{1}.txt',$global:ReportResult,[System.DateTime]::Today.ToString('yyyyMMdd'))
    )
    [System.String]$Message = "[{0}][{1}][{2}][{3}.]" -f $(Get-Date -Format o),'sp_REPORT_SalesCredit_All_NY', $TypeOfEvent, $Event
    if (-not $(Test-Path $(Join-Path $Global:ReportResult 'Logs'))){
        New-Item $(Join-Path $Global:ReportResult 'Logs') -Type Directory -Force | Out-Null
    }
    Write-Output $Message | Out-File $OutputPath -Append -Encoding default -Force
}

# Report Generation Region
try {
        New-LogRecord -E $([System.String]::Format('Procedure Began'))
        [System.String]$Date = Get-Date -UFormat '%Y%m%d'
        [System.String]$Query = $QueryText
        New-LogRecord -E $([System.String]::Format('Using: "{0}"' -f $ConnectionStrig))
        [System.Data.SqlClient.SQLConnection]$Connection = New-Object System.Data.SqlClient.SqlConnection($ConnectionStrig)
        New-LogRecord -E $([System.String]::Format('Connecting'))
        [System.Data.SqlClient.SqlCommand]$Command = New-Object System.Data.SqlClient.SqlCommand($Query,$Connection)
        $Connection.Open()
        New-LogRecord -E $([System.String]::Format('Connection Open'))
        New-LogRecord -E $([System.String]::Format('Query: "{0}"' -f $Query))
        [System.Data.SqlClient.sqlDataAdapter]$Adapter = New-Object System.Data.SqlClient.SqlDataAdapter $Command
        [System.Data.DataSet]$Dataset = New-Object System.Data.DataSet
        New-LogRecord -E $([System.String]::Format('Query Executed'))
        [Void]$Adapter.Fill($DataSet)
        $Connection.Close()
        New-LogRecord -E $([System.String]::Format('Connection Closed'))
        if (Test-Path $global:ReportResult){
            New-LogRecord -E $([System.String]::Format('Directory Missing: "{0}"' -f $global:ReportResult))
        } else {
            New-LogRecord -E $([System.String]::Format('Creating Directory: "{0}"' -f $global:ReportResult))
            New-Item -Path $global:ReportResult -Type Directory -Force
            New-LogRecord -E $([System.String]::Format('Directory Created: "{0}"' -f $global:ReportResult))
        }
        if (Test-Path $CSV){
            Remove-Item $CSV -Force
            New-LogRecord -E $([System.String]::Format('Previois Results Deleted: "{0}"' -f $CSV))
        }
        $Dataset.Tables[6] | Select @{l='Name';e={$(Get-Date -Date $_.Name -UF %Y-%m-%d)}},
                        	        Account,
                        	        Cpty,
                        	        Operation,
                        	        Instrument,
                        	        Currency,
                        	        Quantity,
                        	        Price,
                        	        Price2,
                        	        Principal,
                        	        OrderType,
                        	        UserName,
                        	        Owner,
                        	        Beneficiary,
                        	        BackupSales,
                        	        Pickup,
                        	        Trader,
                        	        Comission,
                        	        Points,
                        	        Spread,
                        	        Total,
                        	        GrossTotalBeneficiary,
                        	        GrossTotalBackup,
                        	        GrossTotalPickup,
                        	        InstrumentClass,
                        	        Branch,
                        	        SalesBranch,
                        	        BackupSalesBranch,
                        	        BranchTotal,
                        	        Category,
                        	        SS,
                        	        Book,
                        	        RPS,
                        	        Intro,
                                    @{l='Company';e={$([System.String]$_.Company).Trim()}},
                        	        k1,
                        	        k2,
                        	        k3,
                        	        SalesTrader,
                        	        CDR,
                        	        Sector,
                        	        AllComission,
                        	        Street,
                        	        @{l='CaptureDate';e={$(Get-Date -Date $_.CaptureDate -UF %Y-%m-%d)}},
                        	        LegalEntity | 
                                        Export-Csv -NoTypeInformation -Path $CSV -Append -Force -Encoding Default
                New-LogRecord -E $([System.String]::Format('CSV File Generated: "{0}"' -f $CSV))
    } catch {
        New-LogRecord -T ERROR -E $([System.String]::Format($Error[0].Exception.Message))
        New-LogRecord -E $([System.String]::Format('Procedure Ended Up With Failure')); break
    }

# SFTP Delivery Region
try{
    New-LogRecord -E $([System.String]::Format('Loading WINSCP Library Init'))
    Add-Type -Path $WINCSPDLL
    New-LogRecord -E $([System.String]::Format("WINSCP Library Loaded: {0}",$WINCSPDLL))
    $session = New-Object WinSCP.Session
    $sessionOptions = New-Object WinSCP.SessionOptions
    $sessionOptions.Protocol = [WinSCP.Protocol]::Sftp
    $sessionOptions.HostName = $SFTPHostName
    $sessionOptions.UserName = $SFTPHostLogin
    #$sessionOptions.Password = $PPKPassphrase
    $SshHostKeyFingerprint = $session.ScanFingerprint($sessionOptions, "SHA-256")
    $sessionOptions.SshPrivateKeyPath = $PPKLocation
    $sessionOptions.SshPrivateKeyPassphrase = $PPKPassphrase
    $sessionOptions.SshHostKeyFingerprint = $SshHostKeyFingerprint
    # Connect and clear credentials from memory
    $session.Open($sessionOptions)
    $sessionOptions = $null

    # Define transfer options for upload process
    $transferOptions = New-Object WinSCP.TransferOptions
    $transferOptions.TransferMode = [WinSCP.TransferMode]::Binary

    # Upload file       
    $transferResult = $session.PutFiles(
                            $([System.String]::Format('{0}\Data\{1}-SalesCreditAllNY.csv',$Global:ReportResult,$([System.DateTime]::Today.ToString('yyyyMMdd')))),
                            $([System.String]::Format('/{0}-SalesCreditAllNY.csv',$([System.DateTime]::Today.ToString('yyyyMMdd')))),
                            $False,
                            $transferOptions)
        
    # Report on success
    foreach ($transfer in $transferResult.Transfers){
        New-LogRecord -E $([System.String]::Format('File Uplodad {0} Succeed',$transfer.FileName))
    }
    New-LogRecord -E $([System.String]::Format('Procedure Ended Up Successfully'))
}
catch{
    New-LogRecord -T ERROR -E $([System.String]::Format($Error[0].Exception.Message))
    New-LogRecord -E $([System.String]::Format('Procedure Ended Up With Failure')); break
}