private ParsingReturnStructure ProcessData2(List <List <String> > table)
{
NeumKey _nk = new NeumKey();
DataPointList dataPointList = new DataPointList();
List <String> footnotes = new List <string>();
List <String> columnHeaders = new List <string>();
List <DateTime> dates = new List <DateTime>();
List <String> values = new List <String>();
List <Tuple <String, String, DateTime, String, String> > dataPoint = new List <Tuple <string, string, DateTime, string, string> >();
List <Tuple <String, String> > nuemWithValue = new List <Tuple <string, string> >();
List <String> neums = new List <String>();
var rawNeum = table[0][0].ToString();
var formattedNeum = CreateNeum(rawNeum);
//var usedRange = ws.UsedRange;
foreach (List <String> Row in table)
{
int Rownum = table.IndexOf(Row);
foreach (String Item in Row)
{
int Colnum = Row.IndexOf(Item);
//Console.WriteLine(Item);
if ((Rownum == 0) && (Colnum >= 1))
{
columnHeaders.Add(Item.ToString());
}
else if ((Rownum > 0) && (Colnum == 0))
{
dates.Add(FormattedDate(Item.ToString()));
}
else if ((Rownum > 0) && (Colnum > 0))
{
try
{
Convert.ToDecimal(Item);
values.Add(Item);
}
catch
{
try
{
Convert.ToDecimal(Item.Split(')')[1].ToString().Trim());
//Console.WriteLine(Item.Split(')')[1].ToString().Trim());
values.Add(Item.Split(')')[1].ToString().Trim());
}
catch
{
values.Add("-999,999");
}
}
;
}
}
}
foreach (var col in columnHeaders)
{
nuemWithValue.Add(new Tuple <string, string>((formattedNeum + "_" + col), (rawNeum + "_" + col)));
}
var i = 0;
foreach (var date in dates)
//foreach (Tuple<string, string> nuemWithVal in nuemWithValue)
{
//foreach(var date in dates)
foreach (Tuple <string, string> nuemWithVal in nuemWithValue)
{
String[] neumAndCatsubstrings = nuemWithVal.Item1.Split('_');
var dp = new DataPoint2017(date, PeriodTypes2017.Annual, Convert.ToDecimal(values[i]));
dp.Neum = nuemWithVal.Item1;
Guid seriesId = _nk.GetValue(dp.Neum);
String seriesName = nuemWithVal.Item1;
_nk.AddSeriesName(seriesId, seriesName);
dp.ParentSeriesId = seriesId;
//dataPoint.Add(new Tuple<string, string, DateTime, string, string>(nuemWithVal.Item1, neumAndCatsubstrings[1], date, "Fiscal Year", values[i]));
dataPointList.AddPoint(dp);
i++;
}
}
ParsingReturnStructure toReturn = new ParsingReturnStructure();
BGTableInfo tableHere = new BGTableInfo();
tableHere.TableName = rawNeum;
int tableLineNum = 0;
toReturn.DataPoints.AddList(dataPointList);
for (int rowOn = 1; rowOn < columnHeaders.Count; rowOn++)
{
// int colOn = 1;
if (columnHeaders[rowOn] != null && columnHeaders[rowOn].Length > 0) //sometimes there are blank cells that don't need to be processed
{
tableLineNum++;
String seriesName = rawNeum + "_" + columnHeaders[rowOn];
String neum = "arlweb~" + seriesName;
neum = _nk.PrettifyNeum(neum);
Guid seriesID = _nk.GetValue(neum);
BGTableLineInformation tableLineInfo = new BGTableLineInformation();
tableLineInfo.linelabel = columnHeaders[rowOn];
tableLineInfo.tablelineindents = 0;
tableLineInfo.tablelinenum = tableLineNum;
tableLineInfo.objectID = seriesID;
tableHere.Add(tableLineInfo);
}
}
toReturn.NeumKey = _nk;
toReturn.TableInfos.Add(tableHere);
return(toReturn);
}
private ParsingReturnStructure ReadData(ParsedTable pt, string fileId)
{
int firstDateRow = 0;
PeriodTypes2017 frequency = PeriodTypes2017.Monthly;
string seriesName = "";
var increment = 1;
//get high level info from the first few rows
for (int row = 0; row <= pt.Vals.GetUpperBound(0); row++)
{
var cell = pt.Vals[row, 0].ToString();
if (cell.Contains(fileId))
{
seriesName = pt.Vals[row, 1].ToString().Replace(",", " "); //TODO - probably need to shorten this
}
if (cell.Contains("Frequency"))
{
if (cell.Contains("Monthly"))
{
frequency = PeriodTypes2017.Monthly;
}
else if (cell.Contains("Quarterly"))
{
frequency = PeriodTypes2017.Quarterly;
}
else if (cell.Contains("Weekly"))
{
frequency = GetDailyFrequency(pt.Vals[row + 2, 0]); //actual dates start two rows down ;
}
else if (cell.Contains("Daily"))
{
frequency = GetDailyFrequency(pt.Vals[row + 2, 0]); //actual dates start two rows down
increment = 5;
}
else
{
//TODO - handle more cases
Debug.Assert(false, "unknown frequency");
}
}
DateTime dateResult;
if (DateTime.TryParse(pt.Vals[row, 0], out dateResult))
{
firstDateRow = row;
break;
}
}
var neumonic = SeriesToNeumonic(seriesName);
seriesName = neumonic;
Guid seriesId = _nk.GetValue(neumonic);
_nk.AddSeriesName(seriesId, seriesName);
var dataPointList = new DataPointList();
for (int row = firstDateRow; row <= pt.Vals.GetUpperBound(0); row += increment)
{
var dp = new DataPoint2017(DateTime.Parse(pt.Vals[row, 0]), frequency, decimal.Parse(pt.Vals[row, 1]));
dp.Neum = neumonic;
dp.ParentSeriesId = seriesId;
dataPointList.AddPoint(dp);
}
var prs = new ParsingReturnStructure();
prs.DataPoints = dataPointList;
BGTableInfo bgTableInfo = new BGTableInfo(); ///
bgTableInfo.TableName = seriesName;
prs.TableInfos.Add(bgTableInfo);
BGTableLineInformation tableLineInfo = new BGTableLineInformation();
tableLineInfo.linelabel = seriesName;
tableLineInfo.tablelineindents = 0;
tableLineInfo.tablelinenum = 1;
tableLineInfo.objectID = seriesId;
bgTableInfo.Add(tableLineInfo);
return(prs);
}
private List <ParsingReturnStructure> ReadData(ParsedTable pt, string fileId)
{
PeriodTypes2017 frequency = PeriodTypes2017.Annual;
List <Regions> regions = new List <Regions>();
List <Crimes> crimes = new List <Crimes>();
List <Footnotes> footnotes = new List <Footnotes>();
List <string> metaData = new List <string>();
List <ParsingReturnStructure> prsList = new List <ParsingReturnStructure>(); //Since there is a table made per region, instead of retruning one parsing return structure we are returning a list of them
bool underArea = false;
int categoryRow = 0;
for (int row = 0; row <= 213; row++) //Loop through the all the rows on sheet
{
int num;
if (row <= 202 && pt.Vals[row, 0] != null && underArea == true) //If excel cell is not null and the range of rows occurs before footnotes
{
regions.Add(new Regions(pt.Vals[row, 0].ToString(), row));
}
if (pt.Vals[row, 0] != null && pt.Vals[row, 0].ToString() == "Area") //If row is under the Area topic, underArea is true and indicates the starting row where regions start
{
underArea = true;
categoryRow = row;
}
if (pt.Vals[row, 0] != null && Int32.TryParse(pt.Vals[row, 0].ToString().Substring(0, 1), out num)) //Puts footnote id value and footnote into footnote object
{
footnotes.Add(new Footnotes(num, pt.Vals[row, 0].ToString().Substring(1, pt.Vals[row, 0].ToString().Length - 1)));
}
if (pt.Vals[row, 0] != null && pt.Vals[row, 0].ToString().Substring(0, 4) == "NOTE")
{
metaData.Add(pt.Vals[row, 0].ToString());
}
}
for (int col = 2; col < 22; col++) //iterates through all of crime columns and if it is not an empty cell adds to the crime object
{
if (pt.Vals[categoryRow, col] != null)
{
crimes.Add(new Crimes(pt.Vals[categoryRow, col].ToString(), col));
}
}
foreach (var region in regions) //Iterates through all of the regions, with a nested loop going through all of the crimes for each region
{
var dataPointList = new DataPointList(); //The Datapoint List stores a list of datapoints
ParsingReturnStructure prs = new ParsingReturnStructure(); //A parsing return structure's datapoint value adds datapoint list
List <int> regionFootnotes = new List <int>();
if (region.RegionName.Any(c => char.IsDigit(c))) //Determines if region name contains a footnote, if so adds footnote id number
{
regionFootnotes = FormatFootnoteNumbers(region.RegionName);
}
foreach (var crime in crimes)
{
List <int> crimeFootnotes = new List <int>();
if (crime.CrimeName.Any(c => char.IsDigit(c))) //Determines if crime name contains a footnote, if so adds footnote id number
{
crimeFootnotes = FormatFootnoteNumbers(crime.CrimeName);
}
var neumonic = SeriesToNeumonic(FormatString(region.RegionName) + " - " + FormatString(crime.CrimeName)); //Formats the neumonic name for the datapoint appends the region name to the crime name
Guid seriesId = _nk.GetValue(neumonic);
_nk.AddSeriesName(seriesId, region.RegionName);
/* foreach (var regionNote in regionFootnotes) //this commented out section will add the footnotes into the into the prslist once other code that manipulates footnotes is changed
* {
* foreach (var note in footnotes)
* {
* if (regionNote == note.FootnoteId)
* {
* _nk.AddFootnote(note.Value, seriesId);
* }
* }
* }
* foreach (var crimeNote in crimeFootnotes)
* {
* foreach (var note in footnotes)
* {
* if (crimeNote == note.FootnoteId)
* {
* _nk.AddFootnote(note.Value, seriesId);
* }
* }
* }*/
int numLoops = 1;
if (crime.CrimeName.Contains("Population")) //Population category doesnt contain extra column pertaining to the rate of population committing a crime by 100k
{
numLoops = 0;
}
for (int col = crime.StartingCol; col <= crime.StartingCol + numLoops; col++)
{
string tempDate = "";
for (int row = region.StartingRow; row <= region.StartingRow + 1; row++)
{
if (row == region.StartingRow) //Dates only have year values must be modified to into a DateTime format
{
tempDate = "12/31/2015";
}
else if (row == region.StartingRow + 1)
{
tempDate = "12/31/2016";
}
var dp = new DataPoint2017(DateTime.Parse(tempDate), frequency, decimal.Parse(pt.Vals[row, col])); //Datapoint value takes to values a Datetime value and decimal number value
dp.Neum = neumonic; //Data point takes a neumonic value as well as the parent series id, same as neumonic value unless datapoint has a parent
dp.ParentSeriesId = seriesId;
dataPointList.AddPoint(dp); //Data point is added to the datapoint list
}
if (numLoops == 1) //if on second colummn of crime there is a given rate of crime per a 100k people
{
neumonic = SeriesToNeumonic(neumonic + "(RatePer100k)");
seriesId = _nk.GetValue(neumonic);
_nk.AddSeriesName(seriesId, region.RegionName);
}
}
BGTableInfo tableHere = new BGTableInfo(); //BGTableInfo contains relevant information for adding prs into the database at a later date
tableHere.TableName = region.RegionName;
prs.TableInfos.Add(tableHere);
BGTableLineInformation tableLineInfo = new BGTableLineInformation();
tableLineInfo.linelabel = region.RegionName;
tableLineInfo.tablelineindents = 0;
tableLineInfo.tablelinenum = 1;
tableLineInfo.objectID = seriesId;
tableHere.Add(tableLineInfo);
}
prs.DataPoints.AddList(dataPointList); //prs adds the data point list that has accumulated all of the data points for the per one region
prsList.Add(prs); // prs for one region is added prsList, prsList is what is returned
}
return(prsList);
}