#cls;
#$st = Get-Date;
# 2019-Jan-16. 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: ExtractExcelData".PadRight(28);

if ($config.IndexOf("<ExcelSourceFile>") -eq -1) {Write-Host "*** Error. Please specify <ExcelSourceFile> parameter value in psConfig.txt file" -ForegroundColor red; exit(1);}
$excelSource=""; $excelSource = ($config | Select-Object -Index(($config.IndexOf("<ExcelSourceFile>"))+1)).Replace("</ExcelSourceFile>","");
if (!(Test-Path $excelSource)) {Write-Host "*** Error. <ExcelSourceFile> parameter value in psConfig.txt file is $excelSource, but such file does not exists" -ForegroundColor red; exit(1);}


# ###################################################################################################
$connection = New-Object System.Data.OleDb.OleDbConnection; $connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$excelSource;Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;'"; 
$cmdObject = New-Object System.Data.OleDb.OleDbCommand; $cmdObject.CommandType = "Text";

# ###################################################################################################
# Config
# ###################################################################################################
$outFile = $psDataFolder + "\Config.csv"; $cmdObject.CommandText = "Select * from [Config$]";
$connection.open(); $cmdObject.Connection = $connection; $dr = $cmdObject.ExecuteReader(); #datareader
$f="MinDate,TrackCash,RefreshTime".Replace(",",$colSep) + $eol; #file header
if (!$dr.Read()) {Write-Host "No config record found" -ForegroundColor Red; exit(1);}; # Just one line
$f += $dr.item("MinDate").ToString("yyyy-MM-dd").Replace($colSep," ") + $colSep + $dr.Item("TrackCash").ToString().Replace($colSep," ") + $colSep + (Get-Date -Format "MMM-dd, HH:mm");
$f | Out-File $outFile -Encoding OEM; #output file
$dr.Close(); $connection.close();
# ###################################################################################################

# ###################################################################################################
# ReportCurrency
# ###################################################################################################
$outFile = $psDataFolder + "\ReportCurrency.csv"; $cmdObject.CommandText = "Select * from [ReportCurrency$]";
$connection.open(); $cmdObject.Connection = $connection; $dr = $cmdObject.ExecuteReader(); #datareader
$f="ReportCurrency,CurrencyID".Replace(",",$colSep) + $eol; #file header
While ($dr.Read()) {
  $col1 = $dr.item("ReportCurrency").ToString().Replace($colSep," ").ToUpper(); if ($col1 -eq "") {continue;}
  if ($col1 -eq "*ORIGINAL*") {$col1 = $dr.item("ReportCurrency").ToString();}
  $f += $col1 + $colSep + $dr.Item("CurrencyID").ToString().Replace($colSep," ") `
  + $eol;
}
$f = $f.Substring(0, $f.Length-$eol.Length); # Removing last 2 characters that are end of line
$f | Out-File $outFile -Encoding OEM; #output file
$dr.Close(); $connection.close();
# ###################################################################################################

# ###################################################################################################
# Account
# ###################################################################################################
$accList = @(); $accCurrList = @(); # Creating Account/Currency arrays that will be used while building Transactions extract
$outFile = $psDataFolder + "\Account.csv"; $cmdObject.CommandText = "Select * from [Account$]"; 
$connection.open(); $cmdObject.Connection = $connection; $dr = $cmdObject.ExecuteReader(); #datareader
$f="Account,Portfolio,Tax,Currency,Active,AccountGroup1,AccountGroup2,AccountGroup3".Replace(",",$colSep) + $eol; #file header
While ($dr.Read()) {
  $col1 = $dr.item("Account").ToString().Replace($colSep," "); if ($col1 -eq "") {continue;}
  $accList += $col1.ToLower(); $accCurrList += $dr.Item("Currency").ToString().ToUpper().Replace($colSep," ");
  $f += $col1 + $colSep + $dr.Item("Portfolio").ToString().Replace($colSep," ") + $colSep + $dr.Item("Tax").ToString().Replace($colSep," ") + $colSep + $dr.Item("Currency").ToString().Replace($colSep," ") + $colSep + $dr.Item("Active").ToString().Replace($colSep," ") `
  + $colSep + $dr.Item("Account Group 1").ToString().Replace($colSep," ")  + $colSep + $dr.Item("Account Group 2").ToString().Replace($colSep," ") + $colSep + $dr.Item("Account Group 3").ToString().Replace($colSep," ") `
  + $eol;
}
$f = $f.Substring(0, $f.Length-$eol.Length); # Removing last 2 characters that are end of line
$f | Out-File $outFile -Encoding OEM; #output file
$dr.Close(); $connection.close();
# ###################################################################################################

# ###################################################################################################
# Allocation
# ###################################################################################################
$outFile = $psDataFolder + "\Allocation.csv"; $cmdObject.CommandText = "Select * from [Allocation$]"; 
$connection.open(); $cmdObject.Connection = $connection; $dr = $cmdObject.ExecuteReader(); #datareader
$f="Allocation,TargetPercent,Index".Replace(",",$colSep) + $eol; #file header
While ($dr.Read()) {
  $col1 = $dr.item("Allocation").ToString().Replace($colSep," "); if ($col1 -eq "") {continue;}
  $f += $col1 + $colSep + $dr.Item("TargetPercent").ToString().Replace($colSep," ") + $colSep + $dr.Item("Index").ToString().Replace($colSep," ") `
  + $eol;
}
$f = $f.Substring(0, $f.Length-$eol.Length); # Removing last 2 characters that are end of line
$f | Out-File $outFile -Encoding OEM; #output file
$dr.Close(); $connection.close();
# ###################################################################################################

# ###################################################################################################
# Symbol
# ###################################################################################################
$sArray=@(); $allocArray=@();
$outFile = $psDataFolder + "\Symbol.csv"; $cmdObject.CommandText = "Select * from [Symbol$]"; 
$connection.open(); $cmdObject.Connection = $connection; $dr = $cmdObject.ExecuteReader(); #datareader
$f="Symbol,SymbolName,Currency,MER,Allocation,SymbolGroup1,SymbolGroup2,SymbolGroup3,Region,CurrencyBase".Replace(",",$colSep) + $eol; #file header
While ($dr.Read()) {
  $col1 = $dr.item("Symbol").ToString().Replace($colSep," "); if ($col1 -eq "") {continue;}
  $f += $col1 + $colSep + $dr.Item("SymbolName").ToString().Replace($colSep," ") + $colSep + $dr.Item("Currency").ToString().Replace($colSep," ") `
  + $colSep + $dr.Item("MER").ToString() + $colSep + $dr.Item("Allocation").ToString().Replace($colSep," ") `
  + $colSep + $dr.Item("SymbolGroup1").ToString().Replace($colSep," ")  + $colSep + $dr.Item("SymbolGroup2").ToString().Replace($colSep," ") + $colSep + $dr.Item("SymbolGroup3").ToString().Replace($colSep," ") `
  + $colSep + $dr.Item("Region").ToString().Replace($colSep," ") + $colSep + $dr.Item("CurrencyBase").ToString().Replace($colSep," ")`
  + $eol;
  $sArray += $col1; $allocArray += $dr.Item("Allocation").ToString().Replace($colSep," ");
}
$f = $f.Substring(0, $f.Length-$eol.Length); # Removing last 2 characters that are end of line
$f | Out-File $outFile -Encoding OEM; #output file
$dr.Close(); $connection.close();
# ###################################################################################################

# ###################################################################################################
# SymbolSector
# ###################################################################################################
$outFile = $psDataFolder + "\SymbolSector.csv"; $cmdObject.CommandText = "Select * from [SymbolSector$]"; 
$connection.open(); $cmdObject.Connection = $connection; $dr = $cmdObject.ExecuteReader(); #datareader
$f="Symbol,Sector,Percent,Sensitivity".Replace(",",$colSep) + $eol; #file header
While ($dr.Read()) {
  $col1 = $dr.item("Symbol").ToString().Replace($colSep," "); if ($col1 -eq "") {continue;}
  $f += $col1 + $colSep + $dr.Item("Sector").ToString().Replace($colSep," ") + $colSep + $dr.Item("Percent").ToString().Replace($colSep," ") + $colSep + $dr.Item("Sensitivity").ToString().Replace($colSep," ") `
  + $eol;
}
$f = $f.Substring(0, $f.Length-$eol.Length); # Removing last 2 characters that are end of line
$f | Out-File $outFile -Encoding OEM; #output file
$dr.Close(); $connection.close();
# ###################################################################################################


# ###################################################################################################
# SymbolAllocation
# ###################################################################################################
$outFile = $psDataFolder + "\SymbolAllocation.csv"; $cmdObject.CommandText = "Select * from [SymbolAllocation$]"; $sAllocArray=@();
$connection.open(); $cmdObject.Connection = $connection; $dr = $cmdObject.ExecuteReader(); #datareader
$f="Symbol,Allocation,Percent".Replace(",",$colSep) + $eol; #file header
While ($dr.Read()) {
  $col1 = $dr.item("Symbol").ToString().Replace($colSep," "); if ($col1 -eq "") {continue;}
  $f += $col1 + $colSep + $dr.Item("Allocation").ToString().Replace($colSep," ") + $colSep + $dr.Item("Percent").ToString().Replace($colSep," ")  + $eol;
  if (!($sAllocArray -contains $col1)) {$sAllocArray+=$col1;}
}

for ($i=0; $i -lt $sArray.Length; $i++) {
    if (!($sAllocArray -contains $sArray[$i] )) {
          $f += $sArray[$i] + $colSep + $allocArray[$i] + $colSep + "1"  + $eol;
    }
 
}

$f = $f.Substring(0, $f.Length-$eol.Length); # Removing last 2 characters that are end of line
$f | Out-File $outFile -Encoding OEM; #output file
$dr.Close(); $connection.close();
# ###################################################################################################



# ###################################################################################################
# TransType
# ###################################################################################################
$transTypeList = @(); $transTypeBVList = @(); $transSortOrderList = @(); # Creating TransactionType + BookValue+TransasctionOrder arrays that will be used while building Transactions extract
$outFile = $psDataFolder + "\TransType.csv"; $cmdObject.CommandText = "Select * from [TransType$]"; 
$connection.open(); $cmdObject.Connection = $connection; $dr = $cmdObject.ExecuteReader(); #datareader
$f="TransType,IgnoreQtyFlag,TransFeeSign,CashAmntSign,BookValueSign,QtySign,DistribReturnOfCapitalFlag,DistribCapGainReinvstdFlag,DividendFlag,DepositTransSign,CashImpactSign,SellFlag,WithholdingTaxFlag,FeeFlag,ExternalImpactSymbolSign,ExternalImpactPortfolioSign,ExternalImpactPortfolioSign2,CashFlag,ExchRateFlag,ShowForSalesReport,TransTypeGroup".Replace(",",$colSep) + $eol; #file header
While ($dr.Read()) {
  $col1 = $dr.item("TransType").ToString().Replace($colSep," "); if ($col1 -eq "") {continue;}
  $transTypeList += $col1.ToLower(); $transTypeBVList += $dr.Item("BookValueSign").ToString(); $transSortOrderList += $dr.Item("TransSortOrder").ToString();
  $f += $col1 `
  + $colSep + $dr.Item("IgnoreQtyFlag").ToString()     + $colSep + $dr.Item("TransFeeSign").ToString()               + $colSep + $dr.Item("CashAmntSign").ToString()               + $colSep + $dr.Item("BookValueSign").ToString() `
  + $colSep + $dr.Item("QtySign").ToString()           + $colSep + $dr.Item("DistribReturnOfCapitalFlag").ToString() + $colSep + $dr.Item("DistribCapGainReinvstdFlag").ToString() + $colSep + $dr.Item("DividendFlag").ToString() `
  + $colSep + $dr.Item("DepositTransSign").ToString()  + $colSep + $dr.Item("CashImpactSign").ToString()             + $colSep + $dr.Item("SellFlag").ToString()                   + $colSep + $dr.Item("WithholdingTaxFlag").ToString() `
  + $colSep + $dr.Item("FeeFlag").ToString()           + $colSep + $dr.Item("ExternalImpactSymbolSign").ToString()   + $colSep + $dr.Item("ExternalImpactPortfolioSign").ToString()+ $colSep + $dr.Item("ExternalImpactPortfolioSign2").ToString() `
  + $colSep + $dr.Item("CashFlag").ToString()          + $colSep + $dr.Item("ExchRateFlag").ToString()               + $colSep + $dr.Item("ShowForSalesReport")                    + $colSep + $dr.Item("TransTypeGroup").ToString().Replace($colSep," ") `
  + $eol;
}
$f = $f.Substring(0, $f.Length-$eol.Length); # Removing last 2 characters that are end of line
$f | Out-File $outFile -Encoding OEM; #output file
$dr.Close(); $connection.close();
# ###################################################################################################


# ###################################################################################################
# Transactions
# ###################################################################################################
$outFile = $psDataFolder + "\TransactionsInExcel.csv"; $cmdObject.CommandText = "Select * from [Transactions$]"; 
$connection.open(); $cmdObject.Connection = $connection; $dr = $cmdObject.ExecuteReader(); #datareader
$f="Account,Date,TransType,TransSubType,Symbol,Qty,Price,Fee, ExchRate,Comment,CostBasisOverride,AccruedInterest,ExchRateRpt1Override,ExchRateRpt2Override,ExchRateRpt3Override,TotalAmnt,CashImpact,QtyChange,TransID,BookValueSign,TransSortOrder,CostBasisImpact".Replace(",",$colSep) + $eol; #file header
While ($dr.Read()) {
  $col1 = $dr.item("Account").ToString().Replace($colSep," "); if ($col1 -eq "") {continue;}
  # Calculation columns from previously built arrays
  $accountCurrency = $accCurrList[$accList.IndexOf($col1.ToLower())].ToUpper(); $bvs = $transTypeBVList[$transTypeList.IndexOf($dr.Item("TransType").ToString().ToLower())]; $tso = $transSortOrderList[$transTypeList.IndexOf($dr.Item("TransType").ToString().ToLower())];

  $f += $col1 `
  + $colSep + $dr.Item("Date").ToString("yyyy-MM-dd")             + $colSep + $dr.Item("TransType").ToString()                    + $colSep + $dr.Item("TransSubType").ToString()    + $colSep + $dr.Item("Symbol").ToString() `
  + $colSep + $dr.Item("Qty").ToString()                          + $colSep + $dr.Item("Price").ToString()                        + $colSep + $dr.Item("Fee").ToString()             + $colSep + $dr.Item("ExchRate").ToString() `
  + $colSep + $dr.Item("Comment").ToString().Replace($colSep," ") + $colSep + $dr.Item("CostBasisOverride").ToString()            + $colSep + $dr.Item("AccruedInterest").ToString() + $colSep + $dr.Item("ExchRateRpt1Override").ToString() `
  + $colSep + $dr.Item("ExchRateRpt2Override").ToString()         + $colSep + $dr.Item("ExchRateRpt3Override").ToString()         + $colSep + $dr.Item("TotalAmnt").ToString()       + $colSep + $dr.Item("CashImpact").ToString() `
  + $colSep + $dr.Item("QtyChange").ToString()                    + $colSep + $dr.Item("TransID").ToString().Replace($colSep," ") + $colSep + $bvs + $colSep + $tso + $colSep + "0" `
  + $eol;

  #+ $colSep + $accountCurrency  + $colSep + "0" + $colSep + $dr.Item("CBI").ToString() `

}
$f = $f.Substring(0, $f.Length-$eol.Length); # Removing last 2 characters that are end of line
$f | Out-File $outFile -Encoding OEM; #output file
$dr.Close(); $connection.close();
# ###################################################################################################

## ###################################################################################################
## Transaction Dates. We get them to lookup exchange rates faster
## ###################################################################################################
#$outFile = $psDataFolder + "\TransactionsDates.csv"; $cmdObject.CommandText = "Select DISTINCT AccCurrency, Date from [Transactions$] WHERE TransType NOT IN('div','divta','INT','WHTX','Deposit','Withdraw','bankfee') ORDER BY 1,2"; 
#$connection.open(); $cmdObject.Connection = $connection; $dr = $cmdObject.ExecuteReader(); #datareader
#$f="CurrencyFrom,Date".Replace(",",$colSep) + $eol; #file header
#While ($dr.Read()) {
#  $col1 = $dr.item("AccCurrency").ToString().Replace($colSep," "); if ($col1 -eq "") {continue;}
#  $f += $col1 + $colSep + $dr.item("Date").ToString("yyyy-MM-dd")`
#  + $eol;
#}
#$f = $f.Substring(0, $f.Length-$eol.Length); # Removing last 2 characters that are end of line
#$f | Out-File $outFile -Encoding OEM; #output file
#$dr.Close(); $connection.close();
## ###################################################################################################



# ###################################################################################################
# Report
# ###################################################################################################
$outFile = $psDataFolder + "\Report.csv";
$f="Report"+$eol+"Report";
$f | Out-File $outFile -Encoding OEM; #output file
# ###################################################################################################

# ###################################################################################################
# CompareTo
# ###################################################################################################
$outFile = $psDataFolder + "\CompareTo.csv"; $cmdObject.CommandText = "Select * from [CompareTo$]";
$connection.open(); $cmdObject.Connection = $connection; $dr = $cmdObject.ExecuteReader(); #datareader
$f="ID,CompareTo,Symbol,AnnlAdj%".Replace(",",$colSep) + $eol; #file header
While ($dr.Read()) {
  $col1 = $dr.item("ID").ToString().Replace($colSep," "); if ($col1 -eq "") {continue;}
  $f += $col1 + $colSep + $dr.Item("CompareTo").ToString().Replace($colSep," ") + $colSep + $dr.Item("Symbol").ToString().Replace($colSep," ") + $colSep + $dr.Item("AnnlAdj%").ToString().Replace($colSep," ")  + $eol;
}
$f = $f.Substring(0, $f.Length-$eol.Length); # Removing last 2 characters that are end of line
$f | Out-File $outFile -Encoding OEM; #output file
$dr.Close(); $connection.close();
# ###################################################################################################


$cmdObject.Dispose(); $connection.Dispose();
$duration = (NEW-TIMESPAN -Start $startTime -End (Get-Date)).TotalSeconds.ToString("#,##0") + " sec.";
(Get-Date).ToString("HH:mm:ss") + " --- Finished ExtractExcelData. Duration: $duration`r`n" | Out-File $logFile -Encoding OEM -append;
$logSummary + ". Duration: $duration";
#"Duration: " + (NEW-TIMESPAN -Start $st -End (Get-Date)).TotalSeconds.ToString("#,##0") + " sec.";
