Example #1
0
        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);
        }
Example #3
0
        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);
        }