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();
                    }
                }
            }
        }
        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;
        }
Пример #3
0
        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;
        }
        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
        }