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