public KeyValuePair <FileHeaderInfo, ValuesDynamicDictionary> ReadSingleFileToFileCache(string filepath)
{
FileHeaderInfo fileheaderinfo = new FileHeaderInfo(filepath); //file meta data
fileheaderinfo.ColumnsUsed = ReadFirstLineOfColumnsToIntArray(filepath); //what particular columns are in that EQRMS extract
//2. Read the contents of the file into cache
// a) There are two invariants for the same loop
// i) The indexer to keep track of where we are in the string[] from reading a line in the file
// columnNumber) The current column we are upto as per the corresponding key(e.g. col #215) for the column's value we are populating
ValuesDynamicDictionary values = new ValuesDynamicDictionary();
using (var sr = new StreamReader(filepath))
{
for (int i = 0; i < numberOfRowsToSkip; i++)
{
sr.ReadLine();
} //skip the first x lines which contain column names and other useless info
while (!sr.EndOfStream)
{
string[] readLine = sr.ReadLine().Split(columnDelimeter); //get 1 row
numberofRowsInTotal++;
foreach (var columnsIndexes in fileheaderinfo.ColumnsUsed)
{
values.Add(columnsIndexes.Value, readLine[columnsIndexes.Key]);
}
}
}
KeyValuePair <FileHeaderInfo, ValuesDynamicDictionary> ret = new KeyValuePair <FileHeaderInfo, ValuesDynamicDictionary>(fileheaderinfo, values);
return(ret);
}
public void WriteToTxtFile(ValuesDynamicDictionary cache, FileHeaderInfo fileHeader, List <Dictionary <string, Value> > formulas)
{
string completePath = FilePath + Program.OutputTxtFileName;
string[] rowHeadline = new string[fileHeader.ColumnsUsed.Count + formulas[0].Keys.Count + 1];
//1. a) merge the headline from raw cache and formulas cache into a single array
int invariant = 0;
while (invariant < fileHeader.ColumnsUsed.Count + formulas[0].Keys.Count)
{
rowHeadline[invariant] = "AsOfDate";
invariant++;
for (int i = 0; i < fileHeader.ColumnsUsed.Count; i++)
{
rowHeadline[invariant] = EQRMSRawColumns.ProcessedColumnIntAsKey[fileHeader.ColumnsUsed[i]];
++invariant;
}
for (int i = 0; i < formulas[0].Count; i++)
{
var lol = formulas.First().Keys;
rowHeadline[invariant] = lol.ElementAt(i);
++invariant;
}
}
File.AppendAllText(completePath, string.Join("\t", rowHeadline) + "\n");
int rowsIndex = 0;
while (rowsIndex < cache.Count())
{
int columnIndex = 0;
string[] row = new string[cache.ValuesUnbound.Count + formulas[0].Count + 1];
row[columnIndex] = fileHeader.AsOfDate.ToString();
columnIndex++;
for (int i = 0; i < fileHeader.ColumnsUsed.Count; i++)
{
row[columnIndex] = cache.GetValue(fileHeader.ColumnsUsed[i], rowsIndex).ToString();
++columnIndex;
}
for (int i = 0; i < formulas[rowsIndex].Count; i++)
{
row[columnIndex] = formulas[rowsIndex].Values.ElementAt(i).ToString();
++columnIndex;
}
++rowsIndex;
File.AppendAllText(completePath, string.Join("\t", row) + "\n");
//File.AppendText(FilePath).WriteLine(string.Join("\t", row));
}
}
private Dictionary <string, Value> FormulasCalculateOneRow(int row, ValuesDynamicDictionary rawcache)
{
Dictionary <string, Value> ret = new Dictionary <string, Value>();
List <string> noneMatches = new List <string>();
EQRMSFormulaLayers formulasLayered = new EQRMSFormulaLayers();
foreach (var layer in formulasLayered.FormulasLayered)
{
foreach (var kvp in layer)
{
//0. Extract the formula into array.... Fair Economic (DTDS T-1) <USD> - Total PAA
string after = kvp.Value;
MatchCollection formulas = ExtractFormulaNames(kvp.Value);
//1. Replace column names with values... 150000 - 150000
foreach (Match match in formulas)
{
string str = match.ToString().Substring(1, match.Length - 2); //remove quotes
//1. a) Did we recalculate this formula already? If so use that
if (ret.ContainsKey(str))
{
string value = ret[str].ToString();
if (value == "")
{
value = "0.000000001337";
} //Empty cells confuse Antlr,
after = after.Replace(match.ToString(), value);
}
//1. b) If we didn't, then fetch the value from the raw cache instead (value in report extract)
else if (EQRMSRawColumns.ProcessedColumnNameAsKey.ContainsKey(str))
{
string value = rawcache.GetValue(EQRMSRawColumns.ProcessedColumnNameAsKey[str], row).ToString();
if (value == "")
{
value = "0.000000001337";
}
after = after.Replace(match.ToString(), value);
}
//1. c) Couldn't find as a column anywhere? Store this column, these could be "PFS" or "ExternalSecurity" etc. but cache them encase to aid debugging
else
{
noneMatches.Add(str);
}
}
//2. Now pass the valuefied formula to Antlr
Value result = CalculateFormula(after);
//3. Store result in a new cache, so that we can retain both the original + recalculated values for comparison
ret.Add(kvp.Key, result);
}
}
return(ret);
}
public List <Dictionary <string, Value> > FormulasCalculateSingleFile(ValuesDynamicDictionary cache)
{
List <Dictionary <string, Value> > ret = new List <Dictionary <string, Value> >();
//Parallel.ForEach(cache.ValuesUnbound, e =>
//{
// ret.Add(FormulasCalculateOneRow(i, cache));
//});
for (int i = 0; i < cache.Count(); i++)
{
ret.Add(FormulasCalculateOneRow(i, cache));
}
return(ret);
}
public void WriteToExcel(ValuesDynamicDictionary cache, FileHeaderInfo fileHeader, List <Dictionary <string, Value> > formulas)
{
//1. Column headline
Console.WriteLine(cache.Count());
string[] rowHeadline = new string[fileHeader.ColumnsUsed.Count + formulas[0].Keys.Count];
int maxLen = fileHeader.ColumnsUsed.Count + formulas[0].Keys.Count;
int tabOneRowCounter = NumberOfRows(FirstTab);
//1. a) merge the headline from raw cache and formulas cache into a single array
int invariant = 0;
while (invariant < maxLen)
{
for (int i = 0; i < fileHeader.ColumnsUsed.Count; i++)
{
rowHeadline[invariant] = EQRMSRawColumns.ProcessedColumnIntAsKey[fileHeader.ColumnsUsed[i]];
++invariant;
}
for (int i = 0; i < formulas[0].Count; i++)
{
var lol = formulas.First().Keys;
rowHeadline[invariant] = lol.ElementAt(i);
++invariant;
}
}
//1. b) write the headline
Excel.Range h1 = FirstTab.Cells[tabOneRowCounter, 1];
Excel.Range h2 = FirstTab.Cells[tabOneRowCounter, rowHeadline.Length];
Excel.Range headerRange = FirstTab.get_Range(h1, h2);
headerRange.Value2 = rowHeadline;
tabOneRowCounter++;
//2. a) merge two caches row at a time
int rowsIndex = 0;
while (rowsIndex < cache.Count())
{
int columnIndex = 0;
string[] row = new string[cache.ValuesUnbound.Count + formulas[0].Count];
//Raw extracted data
for (int i = 0; i < fileHeader.ColumnsUsed.Count; i++)
{
row[columnIndex] = cache.GetValue(fileHeader.ColumnsUsed[i], rowsIndex).ToString();
++columnIndex;
}
//Our re-calculated formulas using Antlr
for (int i = 0; i < formulas[rowsIndex].Count; i++)
{
row[columnIndex] = formulas[rowsIndex].Values.ElementAt(i).ToString();
++columnIndex;
}
++rowsIndex;
//2b) Write the row
Excel.Range c1 = FirstTab.Cells[tabOneRowCounter, 1];
Excel.Range c2 = FirstTab.Cells[tabOneRowCounter, row.Length];
Excel.Range firstTabRangeFormula = FirstTab.get_Range(c1, c2);
firstTabRangeFormula.Value2 = row;
++tabOneRowCounter;
}
}