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); }