# 2018-Sep-07. Created by Maxim T. 
$scriptPath = Split-Path -parent $MyInvocation.MyCommand.Definition; 
. ($scriptPath + "\psFunctions.ps1");     # Adding script with reusable functions
. ($scriptPath + "\psSetVariables.ps1");  # Adding script to assign values to common variables

$logFile        = $scriptPath + "\Log\" + $MyInvocation.MyCommand.Name.Replace(".ps1",".txt"); 
(Get-Date).ToString("HH:mm:ss") + " --- Starting script " + $MyInvocation.MyCommand.Name | Out-File $logFile -Encoding OEM; # starting logging to file.
$logSummary = (Get-Date).ToString("HH:mm:ss") + " Script: ExchRate BoC".PadRight(28);
$currExchIDFile = $currExchIDFolder + "GoogleCurrExchIntraday.txt"; if (Test-Path $currExchIDFile) { Remove-Item $currExchIDFile;} # Removing intraday file before each load
$currExchIDFile = $currExchIDFolder + "YahooCurrExchIntraday.txt"; if (Test-Path $currExchIDFile) { Remove-Item $currExchIDFile;} # Removing intraday file before each load
Get-ChildItem -Path $currExchIDFolder -Filter *Intraday.txt -File | foreach { $_.Delete()}; # Delete existing all files from intraday folder

$listStart = $config.IndexOf("<Currency>"); $listEnd = $config.IndexOf("</Currency>"); 
if ($listStart -eq -1 -or $listEnd -eq -1 -or $listStart+1 -ge $listEnd) {"<currency> Symbol list is empty. Exiting script." | Out-File $logFile -Encoding OEM -Append; exit(1);}
$currList = @($config | Select-Object -Index(($listStart+1)..($listEnd-1))); #list of symbols we will work on
$currCount= $currList.Count; "Currency count: $currCount. MinDate: $minDate" | Out-File $logFile -Encoding OEM -Append;
if ($currCount -le 1) {"Found just one currency, will not create currency exchange database"; exit(1);}

#http://www.bankofcanada.ca/valet/observations/FXUSDCAD/csv?start_date=2017-04-28&end_date=2017-05-03
$cur = @("AED","ARS","AUD","BRL","CAD","CHF","CLP","CNY","DKK","EUR","GBP","HKD","IDR","ILS","INR","JPY","KRW","MXN","MYR","NOK","NZD","SEK","SGD","TWD","USD","ZAR");
# If From or To currency is CAD then we can request conversion rate directly. Otherwise we should request rates: CurrFrom->CAD and CAD->CurrTo and then multiple these values
$urlBase1 = "http://www.bankofcanada.ca/valet/observations/FX@@CurrFrom@@@@CurrTo@@/csv?start_date=@@DateFrom@@&end_date=@@DateTo@@"
$urlBase2 = "http://www.bankofcanada.ca/valet/observations/FX@@CurrFrom@@CAD,FXCAD@@CurrTo@@/csv?start_date=@@DateFrom@@&end_date=@@DateTo@@"
For($i=0; $i -lt $currCount;$i++) {if ($cur.IndexOf($currList[$i]) -eq -1) {"*** Error. Currency " + $currList[$i] + " is not supported."; "*** Error. Currency " + $currList[$i] + " is not supported." | Out-File $logFile -Encoding OEM -Append; exit(1)}};

For($currFromIndex=0; $currFromIndex -lt $currCount; $currFromIndex++) {
    For($currToIndex=0; $currToIndex -lt $currCount; $currToIndex++) {
        if ($currList[$currFromIndex] -ne $currList[$currToIndex]) {
            $currFrom = $currList[$currFromIndex]; $currTo = $currList[$currToIndex]; 
            $ret = GetCurrExchInfo $currFrom $currTo $currExchFolder $minDate; $currExchFile = $ret[0]; $nextDate = $ret[1]; $lastQuote = $ret[2];
            "CurrExch. From: $currFrom -> To: $currTo. Next date: $nextDate. Quote file: $currExchFile" | Out-File $logFile -Encoding OEM -Append;
            
            if (($currFrom.ToLower() -eq "cad") -or ($currTo.ToLower() -eq "cad")) {$urlBase = $urlBase1; $useCad=$false;} else {$urlBase = $urlBase2; $useCad=$true;}; # Direct conversion or using CAD in Between
            $url = $urlBase.Replace("@@CurrFrom@@",$currFrom).Replace("@@CurrTo@@",$currTo).Replace("@@DateFrom@@", $nextDate).Replace("@@DateTo@@",$todayYMD);
            "  Url: " + $url | Out-File $logFile -Encoding OEM -Append;

            $wcD = new-object system.net.WebClient; $webpageD = ""; $reqCount++; $reqRows = 0; 
            try {$webpageD = $wcD.DownloadData($url);} # Get page from url
            catch { $reqFailed++; "  $currFrom->$currTo - Not Found (web err) `r`n" | Out-File $logFile -Encoding OEM -Append; continue; } # if attempt to get webpage failed go to next symbol
            $cfD = [System.Text.Encoding]::ASCII.GetString($webpageD); # This variable now contains downloaded currency rates
            if ($cfD.Contains("<html")) {$reqFailed++; "  No new data (returned html)" | Out-File $logFile -Encoding OEM -Append; continue; } # Result is html file, something went wrong, ignore result that was just returned
            
            $clD = $cfD.Split("`n"); $reqSucceed++;
            if ($clD.Contains("ERRORS")) {$clD = $clD[0..$clD.IndexOf("ERRORS")];} # Just lines before "ERRORS"
             # First 9 lines just quote header; Ignore rows that says "Holiday"; Ignore empty rows; Ignore rows with quote date before $DateFrom; Ignore quotes where rate is empty value.
            $clD1 = $clD | select -Skip 9| Where {$_ -notlike "*holiday*" -and $_ -like "`"2*"} | Where {$_ -ne ""} | Where {$_.split(",")[1].Trim() -ne ""} | Sort-Object;
            $clD1 | %{$a=$_.REPLACE("`"","").Split(","); if ($a[0] -ge $nextDate -and $a[0] -gt $lastQuote) {$reqRows++; if(!$useCad) {$exchRate = $a[1];} else {$exchRate = ([convert]::ToDouble($a[1],$culture) * [convert]::ToDouble($a[2],$culture)).ToString($culture);}; $a[0] + "," + $exchRate + "," + $CurrFrom + "," + $CurrTo; $lastQuote=$a[0]; }} | 
                Out-File $currExchFile -Encoding OEM -Append;

            if ($lastQuote -lt $nextDate) {"Done: $currFrom->$currTo. No new quotes found. `r`n" | Out-File $logFile -Encoding OEM -Append;} # No new records where loaded
            else {"Done: $currFrom->$currTo. (from: $nextDate). Record count: $reqRows. Last quote: $lastQuote `r`n" | Out-File $logFile -Encoding OEM -Append; $reqRowsT+=$reqRows;}
        }
    }
}

$duration = (NEW-TIMESPAN -Start $startTime -End (Get-Date)).TotalSeconds.ToString("#,##0") + " sec.";
(Get-Date).ToString("HH:mm:ss") + " --- Finished. CurrExch Requested/Succeed/Failed/Rows: $reqCount/$reqSucceed/$reqFailed/$reqRowsT. Duration: $duration`r`n" | Out-File $logFile -Encoding OEM -append;
$logSummary + ". CurrExch Requested/Succeed/Failed/Rows: $reqCount/$reqSucceed/$reqFailed/$reqRowsT. Duration: $duration";
