SAP_currency_exchange_rate_update.ps1

# Taken from http://www.boi.org.il/he/Markets/Pages/explainxml.aspx
# the Key in hash is used in our table, and the value is for BOI
$currency_code = @{  "$"   = "01";   "לש"  = "02";  "Eur" = "27"; }

$API_URL = 'https://www.boi.org.il/currency.xml?'
$rdate = [DateTime]::Now
$script:recursive_limit = 7
$SQLServer = "localhost"
$DB_USER = "scripts_user"
$DB_PASS = "p@$$w0rd"
$SQLDBNAME = "test_db"

$proxy = [System.Net.WebRequest]::GetSystemWebProxy()
$proxy.Credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials
$web = New-Object System.Net.WebClient
$web.proxy = $proxy
$web.UseDefaultCredentials = $true

function notify_by_mail([string]$body=$null, [string]$subject = "SAP currency update script") 
{
    send-mailmessage `
        -from "PowerShell script <noreply@sap.localhost>" `
        -to "rabin <rabin@localhost>" `
        -subject $subject `
        -body $body `
        -priority High -dno onSuccess, onFailure -smtpServer smtp.localhost
}

function upsert([string]$rate="0.0", [string]$currency, [string]$date = [DateTime]::Now.ToString('yyyyMMdd'))
{

## The magic query, this will INSERT (if missing) / UPDATE (if exists) -- "UPSERT"
$query = "
MERGE dbo.ORTT AS target 
    USING (
        VALUES ( '$date',  N'$currency',  $rate,  'I',  9)
        ) AS source (RateDate,Currency,Rate,DataSource,UserSign) 
    ON target.RateDate = '$date' AND target.Currency = N'$currency' 
    WHEN MATCHED THEN 
        UPDATE SET 
             RateDate = source.RateDate,
       Currency = source.Currency,
       Rate = source.Rate,
       DataSource = source.DataSource,
       UserSign = source.UserSign 
  WHEN NOT MATCHED THEN 
    INSERT (RateDate,Currency,Rate,DataSource,UserSign) VALUES (source.RateDate,source.Currency,source.Rate,source.DataSource,source.UserSign);
";

    try {
        return Invoke-Sqlcmd -Database $SQLDBNAME -Username $DB_USER -Password $DB_PASS -Query $query -Debug
        #return Invoke-Sqlcmd -Database $SQLDBNAME -Username $DB_USER -Password $DB_PASS -Query "SELECT * FROM ORTT WITH (nolock) WHERE RateDate='$date' ORDER BY ratedate DESC"
    }
    catch {
            $ErrorMessage = $_.Exception.Message
            $FailedItem   = $_.Exception.ItemName
            notify_by_mail -body "$ErrorMessage" -subject "SAP Powershell script Error: $FailedItem"
            exit
    }

}


function fetch_rate([string]$currency, [string]$rdate) 
{
    $url = $API_URL + 'rdate=' + $rdate + '&curr=' + $currency_code.Item($currency_key);
    Write-Debug "Trying: $url";

    try 
    {
        [xml]$webpage = $web.DownloadString($url)
        $error_node = $webpage.SelectSingleNode("//CURRENCIES/ERROR1")

        if (-not $error_node) 
        {
            $rate = $webpage.CURRENCIES.CURRENCY.RATE
            return $rate
        }
        else 
        {       
            Write-Debug "No exchange rate published for this date[$rdate]"

            $new_rdate = [DateTime]::ParseExact($webpage.CURRENCIES.REQUESTED_DATE, "yyyyMMdd", $null).AddDays(-1)
            if ($script:recursive_limit -ge 0 ) 
            {
                $script:recursive_limit--
                $rate = fetch_rate -currency "$currency" -rdate $new_rdate.ToString('yyyyMMdd')
        
                return $rate
            }
            else {
                #return "ERROR: Recursive limit reached"
                notify_by_mail -body "ERROR: Recursive limit reached"
                exit
            }
        }   
    }
    catch {
        $ErrorMessage = $_.Exception.Message
        $FailedItem   = $_.Exception.ItemName
        notify_by_mail -body "$ErrorMessage" -subject "SAP Powershell script Error: $FailedItem"
    }

    return "Error"
}
foreach ($currency_key in $currency_code.keys) {
    $rate =  fetch_rate -currency "$currency_code" -rdate $rdate.ToString('yyyyMMdd')
    $format = @{Expression={$_.RateDate};Label=”Date”;width=22},@{Expression={$_.Currency};Label=”Currency”; width=8},@{Expression={$_.Rate};Label="Rate"}
    echo "rate=$rate , curency=$currency_key"
    upsert -rate $rate -currency $currency_key |  format-table $format   
}

Exit-PSSession