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 = PeriodTypes2017.WeeklyThurs; } 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; } } string freqValue = frequency.ToString(); var neumonic = SeriesToNeumonic(seriesName); 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); }