Example #1
0
        public ICollection <DroughtMonitorWeek> FindUS(DateTime?week = null, int weeksPrevious = 0)
        {
            week = DroughtMonitorWeek.ConvertDateToTuesday(week.Value);

            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                using (SqlCommand command = new SqlCommand(null, conn))
                {
                    if (week == null && weeksPrevious == 0)
                    {
                        command.CommandText = string.Format("select DroughtCategory, DroughtValue, PublishedDate from USDMValues where order by PublishedDate");
                    }
                    else if (week == null && weeksPrevious != 0)
                    {
                        command.CommandText = string.Format("select DroughtCategory, DroughtValue, PublishedDate from USDMValues where PublishedDate >= @rangestart order by PublishedDate");
                        command.Parameters.AddWithValue("@rangestart", DateTime.Now.AddDays(7 * (0 - weeksPrevious)).ToString("yyyy/MM/dd"));
                    }
                    else if (week != null)
                    {
                        command.CommandText = string.Format("select DroughtCategory, DroughtValue, PublishedDate from USDMValues where PublishedDate >= @rangestart and PublishedDate <= @rangeend order by PublishedDate");
                        command.Parameters.AddWithValue("@rangestart", week.Value.AddDays(7 * (0 - weeksPrevious)).ToString("yyyy/MM/dd"));
                        command.Parameters.AddWithValue("@rangeend", week.Value.ToString("yyyy/MM/dd"));
                    }

                    return(ProcessQuery(command, DMDataType.US));
                }
            }
        }
        } //End AlreadyImported

        private void WriteData(DMDataType type, List <string> rows, DateTime date)
        {
            DroughtMonitorWeek dmWeek = new DroughtMonitorWeek();

            dmWeek.Type         = type;
            dmWeek.Week         = date;
            dmWeek.County       = new USCounty();
            dmWeek.County.State = new USState();
            dmWeek.State        = new USState();

            bool wroteUS = false;

            foreach (string line in rows)
            {
                if (line.Equals("") || wroteUS)
                {
                    continue;
                }

                // remove the \" from the line
                string newLine = line.Replace("\"", "");

                // split out each column
                string[] cols = newLine.Split(',');

                int offset = 2; // Offset is 2 for the State and US data sets, but is 4 for the County data
                switch (type)
                {
                case DMDataType.COUNTY:
                    dmWeek.County.ID         = -1;
                    dmWeek.County.Name       = cols[2];
                    dmWeek.County.Fips       = int.Parse(cols[1]);
                    dmWeek.County.State.Name = cols[3];
                    dmWeek.State.Name        = cols[3];
                    offset = 4;
                    break;

                case DMDataType.STATE:
                    dmWeek.State.ID   = -1;
                    dmWeek.State.Name = cols[1];
                    break;

                case DMDataType.US:
                    //only write first line of data for US data
                    wroteUS = true;
                    break;
                }

                //Add DM values for all six columns
                for (int i = 0; i < 6; i++)
                {
                    // Set value for col[i+offset] with category i
                    dmWeek[i] = float.Parse(cols[i + offset]);
                }

                _repo.Add(dmWeek);
            } //End foreach line in rows
        }     //End WriteData
Example #3
0
        } //End Load DM Data

        private DroughtMonitorWeek LoadDMDataValues(DMDataType type, DateTime date, int CountyFIPS)
        {
            DroughtMonitorWeek week = null;

            switch (type)
            {
            case DMDataType.COUNTY:
                week = DmRepository.FindBy(DmRepository.GetCountyForFips(CountyFIPS), date).FirstOrDefault();
                break;

            case DMDataType.STATE:
                week = DmRepository.FindBy(DmRepository.GetCountyForFips(CountyFIPS).State, date).FirstOrDefault();
                break;

            case DMDataType.US:
                week = DmRepository.FindUS(date).FirstOrDefault();
                break;
            }

            if (week == null)
            {
                USCounty county = DmRepository.GetCountyForFips(CountyFIPS);
                week = new DroughtMonitorWeek()
                {
                    D0         = 0,
                    D1         = 0,
                    D2         = 0,
                    D3         = 0,
                    D4         = 0,
                    NonDrought = 0,
                    Week       = date,
                    Type       = type,
                    County     = county,
                    State      = county.State
                };
            }
            else
            {
                week.Type = type;
                // Normalize data to be out of 100%
                week.D0 = (float)Math.Round((week.D0 - week.D1), 2);
                week.D1 = (float)Math.Round((week.D1 - week.D2), 2);
                week.D2 = (float)Math.Round((week.D2 - week.D3), 2);
                week.D3 = (float)Math.Round((week.D3 - week.D4), 2);
            }

            return(week);
        } //End LoadDMDataValues
Example #4
0
        public void Add(DroughtMonitorWeek week)
        {
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                using (SqlCommand command = new SqlCommand(null, conn))
                {
                    switch (week.Type)
                    {
                    case DMDataType.COUNTY:
                        if (week.County.ID == -1)
                        {
                            week.County.ID = this.GetCountyID(conn, week.County.Name, week.County.Fips, week.State.Name);
                        }
                        command.CommandText = "insert into CountyDMValues (PublishedDate, County_ID, DroughtCategory, DroughtValue) values (@pdate, @county, @category, @dval)";
                        command.Parameters.AddWithValue("@county", week.County.ID);
                        break;

                    case DMDataType.STATE:
                        if (week.State.ID == -1)
                        {
                            week.State.ID = this.GetStateID(conn, week.State.Name);
                        }
                        command.CommandText = "insert into StateDMValues (PublishedDate, State_ID, DroughtCategory, DroughtValue) values (@pdate, @state, @category, @dval)";
                        command.Parameters.AddWithValue("@state", week.State.ID);
                        break;

                    case DMDataType.US:
                        command.CommandText = "insert into USDMValues (PublishedDate, DroughtCategory, DroughtValue) values (@pdate, @category, @dval)";
                        break;
                    }
                    command.Parameters.AddWithValue("@pdate", week.Week);
                    command.Parameters.Add("@category", SqlDbType.Int);
                    command.Parameters.Add("@dval", SqlDbType.Float);

                    for (int i = 0; i < 6; i++)
                    {
                        command.Parameters["@category"].Value = i;
                        command.Parameters["@dval"].Value     = week[i];
                        command.ExecuteNonQuery();
                    }
                }
            }
        }
        } //End RunDMImport (two dates)

        private void ImportDMData(DateTime startDate, DateTime endDate)
        {
            startDate = DroughtMonitorWeek.ConvertDateToTuesday(startDate);
            endDate   = DroughtMonitorWeek.ConvertDateToTuesday(endDate);

            DateTime        importWeek  = startDate;
            List <DateTime> importDates = new List <DateTime>();

            while (importWeek <= endDate)
            {
                importDates.Add(importWeek);
                importWeek = importWeek.AddDays(7);
            }

            foreach (DateTime week in importDates)
            {
                if (!AlreadyImported(week))
                {
                    foreach (DMDataType type in Enum.GetValues(typeof(DMDataType)))
                    {
                        if (type != DMDataType.ALL)
                        {
                            //Get information
                            string    url      = String.Format(@"http://droughtmonitor.unl.edu/USDMStatistics.ashx/?mode=table&aoi={0}&date={1}", type.ToString().ToLower(), week.ToString("yyyyMMdd"));
                            WebClient client   = new WebClient();
                            string    response = client.DownloadString(url);

                            // split the response into rows based on the new line character
                            List <string> rows = response.Split('\n').ToList <string>();
                            rows.RemoveAt(0); // remove the header row

                            this.WriteData(type, rows, week);
                        }
                    }
                }
            } //End foreach week in importDates
        }     //End ImportDMData (two dates)
Example #6
0
        public ICollection <DroughtMonitorWeek> FindBy(USCounty county, DateTime?week = null, int weeksPrevious = 0)
        {
            week = DroughtMonitorWeek.ConvertDateToTuesday(week.Value);

            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                using (SqlCommand command = new SqlCommand(null, conn))
                {
                    if (week == null && weeksPrevious == 0)
                    {
                        command.CommandText = string.Format("select DroughtCategory, DroughtValue, PublishedDate, County_ID from CountyDMValues where County_ID = @county order by PublishedDate, County_ID");
                        command.Parameters.AddWithValue("@county", county.ID);
                    }
                    else if (week == null && weeksPrevious != 0)
                    {
                        command.CommandText = string.Format("select DroughtCategory, DroughtValue, PublishedDate, County_ID from CountyDMValues where County_ID = @county and PublishedDate >= @rangestart order by PublishedDate, County_ID");
                        command.Parameters.AddWithValue("@county", county.ID);
                        command.Parameters.AddWithValue("@rangestart", DateTime.Now.AddDays(7 * (0 - weeksPrevious)).ToString("yyyy/MM/dd"));
                    }
                    else if (week != null)
                    {
                        command.CommandText = string.Format("select DroughtCategory, DroughtValue, PublishedDate, County_ID from CountyDMValues where County_ID = @county and PublishedDate >= @rangestart and PublishedDate <= @rangeend order by PublishedDate, County_ID");
                        command.Parameters.AddWithValue("@county", county.ID);
                        command.Parameters.AddWithValue("@rangestart", week.Value.AddDays(7 * (0 - weeksPrevious)).ToString("yyyy/MM/dd"));
                        command.Parameters.AddWithValue("@rangeend", week.Value.ToString("yyyy/MM/dd"));
                    }
                    else
                    {
                        throw new Exception("No command was specified");
                    }

                    return(ProcessQuery(command, DMDataType.COUNTY));
                }
            }
        }
Example #7
0
        private ICollection <DroughtMonitorWeek> ProcessQuery(SqlCommand command, DMDataType type)
        {
            List <DroughtMonitorWeek> weeks = new List <DroughtMonitorWeek>();

            using (SqlDataReader reader = command.ExecuteReader())
            {
                DroughtMonitorWeek currentWeek = null;
                while (reader.Read())
                {
                    switch (type)
                    {
                    case DMDataType.COUNTY:
                        if (currentWeek == null || (reader.GetDateTime(2) != currentWeek.Week || reader.GetInt64(3) != currentWeek.County.ID))
                        {
                            if (currentWeek != null)
                            {
                                weeks.Add(currentWeek);
                            }
                            currentWeek        = new DroughtMonitorWeek();
                            currentWeek.Week   = reader.GetDateTime(2);
                            currentWeek.County = GetCounty(reader.GetInt64(3));
                            currentWeek.State  = currentWeek.County.State;
                        }
                        break;

                    case DMDataType.STATE:
                        if (currentWeek == null || (reader.GetDateTime(2) != currentWeek.Week || reader.GetInt64(3) != currentWeek.State.ID))
                        {
                            if (currentWeek != null)
                            {
                                weeks.Add(currentWeek);
                            }
                            currentWeek       = new DroughtMonitorWeek();
                            currentWeek.Week  = reader.GetDateTime(2);
                            currentWeek.State = GetState(reader.GetInt64(3));
                        }
                        break;

                    case DMDataType.US:
                        if (currentWeek == null || reader.GetDateTime(2) != currentWeek.Week)
                        {
                            if (currentWeek != null)
                            {
                                weeks.Add(currentWeek);
                            }
                            currentWeek      = new DroughtMonitorWeek();
                            currentWeek.Week = reader.GetDateTime(2);
                        }
                        break;
                    }

                    currentWeek[reader.GetInt32(0)] = reader.GetDouble(1);
                }
                if (currentWeek != null)
                {
                    weeks.Add(currentWeek);
                }
                reader.Close();
            }

            return(weeks);
        }