private int SiteData(L1HarvestList series) { this.ws.Url = series.WebServiceURL; // save siteInfo SiteInfoResponseType responsesite = this.ws.GetSiteInfoObject(series.SiteCode, string.Empty); SiteInfoType site = responsesite.site[0].siteInfo; LatLonPointType latlon = (LatLonPointType)site.geoLocation.geogLocation; int localx = site.geoLocation.localSiteXY == null ? 0 : (int)site.geoLocation.localSiteXY[0].X; /*(int)DBNull.Value*/ int localy = site.geoLocation.localSiteXY == null ? 0 : (int)site.geoLocation.localSiteXY[0].Y; string state = string.Empty; string county = string.Empty; if (site.siteProperty != null) { foreach (PropertyType s in site.siteProperty) { if (s.name == "State") { state = s.Value; } else if (s.name == "County") { county = s.Value; } } } return(this.db.TooDB.SaveSite(site.siteCode[0].Value, site.siteName.ToString(), Convert.ToDouble(latlon.longitude), Convert.ToDouble(latlon.latitude), 0, Convert.ToDouble(site.elevation_m), site.verticalDatum, localx, localy, 0, 0, state, county /*"county"*/, string.Empty /*"comments"*/, series.SiteType)); }
public ClsDBAccessor(L1HarvestList series, ClsSummaryDB summDB) { this.summaryDB = summDB; if (series.DBInitialCatalog != null && series.DBInitialCatalog != string.Empty) { SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(); builder.DataSource = series.DBDataSource; // drought.usu.edu builder.InitialCatalog = series.DBInitialCatalog; // Summary builder.PersistSecurityInfo = true; builder.UserID = series.DBUsername; builder.Password = series.DBPassword; builder.MultipleActiveResultSets = true; //// builder.IntegratedSecurity = true; string providerString = builder.ToString(); EntityConnectionStringBuilder tooConnection = new EntityConnectionStringBuilder(); tooConnection.Metadata = "res://*/SummaryModel.csdl|res://*/SummaryModel.ssdl|res://*/SummaryModel.msl";//"res://*/ODMModel.csdl|res://*/ODMModel.ssdl|res://*/ODMModel.msl";//"res://*/SummaryModel.csdl|res://*/SummaryModel.ssdl|res://*/SummaryModel.msl"; tooConnection.Provider = "System.Data.SqlClient"; tooConnection.ProviderConnectionString = providerString; this.tooDB = new ClsTooDB(tooConnection.ConnectionString); } else { this.tooDB = new ClsTooDB(); } }
/// <summary> /// Gets the Data from a webservice for the Original Agency Data /// </summary> /// <param name="series">object from the database that contains all the details of the series we are trying to gather data for</param> /// <param name="start">start of interval to retrieve from webservice</param> /// <param name="end">end of the interval to retrieve from webservice</param> /// <returns> a class that containsall the data from the webservice call</returns> private TimeSeriesType GetData(L1HarvestList series, string start, string end) { this.ws = new WaterOneFlow(); this.ws.Url = series.WebServiceURL; TimeSeriesResponseType response = this.ws.GetValuesObject(series.SiteCode, series.VariableCode, start, end, string.Empty); TimeSeriesType tseries = response.timeSeries; return(tseries); }
/// <summary> /// gather and save Original Agency data from webservice to database /// </summary> /// <param name="variable">object from the database that contains all the details of the series we are trying to gather data for</param> /// <param name="start">date at the beginning of the interval</param> /// <param name="end">date at end of interval</param>1 public void AgencyDataFill(L1HarvestList variable, string start, string end, int count) { DBLogging.WriteLog(Properties.Settings.Default.projectName, "Log", "Level1Data1_0" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", String.Format("{0} {1} {2}-{3} {4}\t{5}-{6} {7}/{8}", "Level1", variable.SiteName, start.Replace('-', '/'), end.Replace('-', '/'), variable.SiteCode, variable.VariableCode, "Level1", count, db.SummaryDB.Count)); TimeSeriesType dataVals = this.GetData(variable, start, end); if (dataVals != null) { if (Convert.ToInt32(dataVals.values.count) >= 1) { int siteID = this.SiteData(variable); if (variable.SiteID == 0) { this.db.SummaryDB.UpdateHarvestSite(variable, siteID); } this.noDataValue = Convert.ToDouble(dataVals.variable.NoDataValue); TsValuesSingleVariableType vals = dataVals.values; DateTime startDate = (from n in vals.value select Convert.ToDateTime(n.dateTime)).Min(); DateTime endDate = (from n in vals.value select Convert.ToDateTime(n.dateTime)).Max(); var data = from n in vals.value select n; foreach (ValueSingleVariable member in data) { TimeSpan utcoffset = member.dateTime - member.dateTime.ToUniversalTime(); // site, Method, variable, Source this.values.Rows.Add(SqlInt32.Null, Convert.ToDouble(member.Value), (member.accuracyStdDev == 0 ? SqlDouble.Null : member.accuracyStdDev), member.dateTime, utcoffset.Hours /*utcoffset*/, member.dateTime.ToUniversalTime(), siteID, variable.VariableID, (member.offsetValue == 0 ? SqlDouble.Null : member.offsetValue), (Convert.ToInt32(member.offsetTypeID) == 0 ? SqlInt32.Null : Convert.ToInt32(member.offsetTypeID)), this.CensorCodeToString(member), SqlInt32.Null, variable.MethodID, variable.SourceID, (Convert.ToInt32(member.sampleID) == 0 ? SqlInt32.Null : Convert.ToInt32(member.sampleID)), SqlInt32.Null, variable.QualityControlLevelID); } this.db.TooDB.InsertBulk(this.values); DBLogging.WriteLog(Properties.Settings.Default.projectName, "Log", "Level1Data1_0" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", values.Rows.Count + " Rows Saved. " + "Level1" + " " + variable.SiteName + " " + variable.SiteCode + "\t" + variable.VariableCode + "-" + "Level1"); this.db.TooDB.SaveSeries(siteID, variable); } else { //DBLogging.WriteLog(Properties.Settings.Default.projectName, "Log", "Level1Data1_0" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", "No Values Found. " + variable.DataTimePeriod + " " + variable.SiteName + " " + variable.SiteCode + "\t" + variable.VariableCode + "-" + variable.DataTimePeriod); } } else { DBLogging.WriteLog(Properties.Settings.Default.projectName, "Log", "Level1Data1_0" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", 0 + " Rows Saved. " + "Level1" + " " + variable.SiteName + " " + variable.SiteCode + "\t" + variable.VariableCode + "-" + "Level1"); } }
/// <summary> /// saves or gathers site information SiteData /// </summary> /// <param name="series">object from the database that contains all the details of the series we are trying to gather data for</param> /// <returns>the SiteId from the database</returns> private int SiteData(L1HarvestList series) { SiteInfoType site; this.ws.Url = series.WebServiceURL; try { // save siteInfo SiteInfoResponseType responsesite = this.ws.GetSiteInfoObject(series.SiteCode, string.Empty); site = responsesite.site[0].siteInfo; } catch { TimeSeriesResponseType response = this.ws.GetValuesObject(series.SiteCode, series.VariableCode, "1900-01-01", "1900-01-01", string.Empty); site = (SiteInfoType)response.timeSeries.sourceInfo; // site[0].siteInfo; } LatLonPointType latlon = (LatLonPointType)site.geoLocation.geogLocation; int localx = site.geoLocation.localSiteXY == null ? 0 : (int)site.geoLocation.localSiteXY[0].X; /*(int)DBNull.Value*/ int localy = site.geoLocation.localSiteXY == null ? 0 : (int)site.geoLocation.localSiteXY[0].Y; // note 1 contains county information but not all sites contain a note 1. return(this.db.TooDB.SaveSite(site.siteCode[0].Value, site.siteName.ToString(), Convert.ToDouble(latlon.longitude), Convert.ToDouble(latlon.latitude), 0 /*lat long datum id*/, Convert.ToDouble(site.elevation_m), site.verticalDatum, localx, localy, 0 /*local projection id*/, 0 /*Position Accuracy*/, site.note[0].Value.Length == 2 ? site.note[0].Value : string.Empty /*"state"*/, string.Empty /*"county"*/, string.Empty /*"comments"*/, series.SiteType)); }
public void UpdateHarvestSite(L1HarvestList variable, int id) { variable.SiteID = id; this.SaveChanges(); }
//public int SaveSeries(int[] meta, int qclid) //{ // SeriesCatalog sc; // int siteID = meta[0]; // int methodID = meta[1]; // int varID = meta[2]; // int sourceID = meta[3]; // IQueryable<DataValue> dataList = from DV in this.DataValues where DV.Site.SiteID == siteID && DV.Variable.VariableID == varID && DV.Method.MethodID == methodID && DV.Source.SourceID == sourceID select DV; // try // { // sc = (from S in this.SeriesCatalogs where S.SiteID == siteID && S.VariableID == varID && S.MethodID == methodID && S.SourceID == sourceID select S).First(); // sc.ValueCount = dataList.Count(); // sc.EndDateTime = (from D in dataList select D.LocalDateTime).Max(); // sc.EndDateTimeUTC = (from D in dataList select D.DateTimeUTC).Max(); // this.SaveChanges(); // } // catch // { // sc = new SeriesCatalog(); // DataValue data = dataList.First(); // sc = new SeriesCatalog(); // sc.SiteID = data.Site.SiteID; // sc.SiteCode = data.Site.SiteCode; // sc.SiteName = data.Site.SiteName; // sc.VariableID = data.Variable.VariableID; // sc.VariableName = data.Variable.VariableName; // sc.VariableCode = data.Variable.VariableCode; // sc.Speciation = data.Variable.Speciation; // sc.VariableUnitsID = data.Variable.Unit.UnitsID; // sc.VariableUnitsName = this.GetUnits(data.Variable.Unit.UnitsID); // sc.SampleMedium = data.Variable.SampleMedium; // sc.ValueType = data.Variable.ValueType; // sc.TimeSupport = data.Variable.TimeSupport; // sc.TimeUnitsID = data.Variable.Unit1.UnitsID; // sc.TimeUnitsName = this.GetUnits(data.Variable.Unit1.UnitsID); // sc.DataType = data.Variable.DataType; // sc.GeneralCategory = data.Variable.GeneralCategory; // sc.MethodID = data.Method.MethodID; // sc.MethodDescription = data.Method.MethodDescription; // sc.SourceID = data.Source.SourceID; // sc.Organization = data.Source.Organization; // sc.SourceDescription = data.Source.SourceDescription; // sc.Citation = data.Source.Citation; // sc.QualityControlLevelID = qclid; // sc.QualityControlLevelCode = this.GetQCL(qclid); // sc.BeginDateTime = (from DL in dataList select DL.LocalDateTime).Min(); // sc.BeginDateTimeUTC = (from DL in dataList select DL.DateTimeUTC).Min(); // sc.EndDateTime = (from DL in dataList select DL.LocalDateTime).Max(); // sc.EndDateTimeUTC = (from DL in dataList select DL.DateTimeUTC).Max(); // sc.ValueCount = dataList.Count(); // this.AddToSeriesCatalogs(sc); // this.SaveChanges(); // } // return sc.SeriesID; //} public int SaveSeries(int siteID, L1HarvestList variable) { SeriesCatalog sc; int methodID = variable.MethodID; int varID = variable.VariableID; int sourceID = variable.SourceID; IQueryable <DataValue> dataList = from DV in this.DataValues where DV.Site.SiteID == siteID && DV.Variable.VariableID == varID && DV.Method.MethodID == methodID && DV.Source.SourceID == sourceID select DV; try { // site, Method, variable, Source sc = (from S in this.SeriesCatalogs where S.SiteID == siteID && S.VariableID == varID && S.MethodID == methodID && S.SourceID == sourceID select S).First(); sc.ValueCount = dataList.Count(); //// sc.BeginDateTime = StartDate; //// sc.BeginDateTimeUTC = StartDate.ToUniversalTime(); sc.EndDateTime = (from DL in dataList select DL.LocalDateTime).Max(); sc.EndDateTimeUTC = (from DL in dataList select DL.DateTimeUTC).Max(); this.SaveChanges(); } catch (Exception ex) { try { DataValue data = dataList.First(); //// DataValues D = (from DV in this.DataValues where DV.ValueID == DataValID select DV).First(); sc = new SeriesCatalog(); if (data.Site != null) { sc.SiteID = data.Site.SiteID; sc.SiteCode = data.Site.SiteCode; sc.SiteName = data.Site.SiteName; } else { Site s = (from S in this.Sites where S.SiteID == siteID select S).First(); sc.SiteID = s.SiteID; sc.SiteCode = s.SiteCode; sc.SiteName = s.SiteName; } if (data.Variable != null) { sc.VariableID = data.Variable.VariableID; sc.VariableName = data.Variable.VariableName; sc.VariableCode = data.Variable.VariableCode; sc.Speciation = data.Variable.Speciation; //if (data.Variable.Unit != null) //{ sc.VariableUnitsID = data.Variable.Unit.UnitsID; sc.VariableUnitsName = data.Variable.Unit.UnitsName; //} //else //{ //Units u = (from U in this.Units where U.UnitsID==1 select U).First(); //sc.VariableUnitsID = Convert.ToInt16(data.Variable UnitsReference.EntityKey.EntityKeyValues[0].Value); //sc.VariableUnitsName = this.GetUnits(sc.VariableUnitsID.Value); //} sc.SampleMedium = data.Variable.SampleMedium; sc.ValueType = data.Variable.ValueType; sc.TimeSupport = data.Variable.TimeSupport; //if (data.Variable.Unit != null) //{ sc.TimeUnitsID = data.Variable.Unit1.UnitsID; sc.TimeUnitsName = this.GetUnits(data.Variable.Unit1.UnitsID); //} //else //{ // Units u = (from U in this.Units where U.UnitsID==1 select U).First(); // sc.TimeUnitsID = Convert.ToInt16(data.Variable.Units1Reference.EntityKey.EntityKeyValues[0].Value); // sc.TimeUnitsName = this.GetUnits(sc.TimeUnitsID.Value); //} sc.DataType = data.Variable.DataType; sc.GeneralCategory = data.Variable.GeneralCategory; } else { Variable v = (from V in this.Variables where V.VariableID == varID select V).First(); sc.VariableID = v.VariableID; sc.VariableName = v.VariableName; sc.VariableCode = v.VariableCode; sc.Speciation = v.Speciation; if (v.Unit != null) { sc.VariableUnitsID = v.Unit.UnitsID; sc.VariableUnitsName = this.GetUnits(v.Unit.UnitsID); } else { sc.VariableUnitsID = Convert.ToInt16(v.UnitReference.EntityKey.EntityKeyValues[0].Value); sc.VariableUnitsName = this.GetUnits(sc.VariableUnitsID.Value); } sc.SampleMedium = v.SampleMedium; sc.ValueType = v.ValueType; sc.TimeSupport = v.TimeSupport; if (v.Unit1 != null) { sc.TimeUnitsID = v.Unit1.UnitsID; sc.TimeUnitsName = v.Unit1.UnitsName; } else { sc.TimeUnitsID = Convert.ToInt16(v.Unit1Reference.EntityKey.EntityKeyValues[0].Value); sc.TimeUnitsName = this.GetUnits(sc.TimeUnitsID.Value); } sc.DataType = v.DataType; sc.GeneralCategory = v.GeneralCategory; } if (data.Method != null) { sc.MethodID = data.Method.MethodID; sc.MethodDescription = data.Method.MethodDescription; } else { Method m = (from M in this.Methods where M.MethodID == methodID select M).First(); sc.MethodID = m.MethodID; sc.MethodDescription = m.MethodDescription; } if (data.Source != null) { sc.SourceID = data.Source.SourceID; sc.Organization = data.Source.Organization; sc.SourceDescription = data.Source.SourceDescription; sc.Citation = data.Source.Citation; } else { Source s = (from S in this.Sources where S.SourceID == sourceID select S).First(); sc.SourceID = s.SourceID; sc.Organization = s.Organization; sc.SourceDescription = s.SourceDescription; sc.Citation = s.Citation; } sc.QualityControlLevelID = variable.QualityControlLevelID; sc.QualityControlLevelCode = this.GetQCL(variable.QualityControlLevelID); sc.BeginDateTime = (from DL in dataList select DL.LocalDateTime).Min(); sc.BeginDateTimeUTC = (from DL in dataList select DL.DateTimeUTC).Min(); sc.EndDateTime = (from DL in dataList select DL.LocalDateTime).Max(); sc.EndDateTimeUTC = (from DL in dataList select DL.DateTimeUTC).Max(); sc.ValueCount = dataList.Count(); this.AddToSeriesCatalogs(sc); this.SaveChanges(); } catch (Exception exception) { throw new Exception("SaveSeries(int " + siteID + ", AggregateSeries " + variable.SiteCode + ")", exception); } } return(sc.SeriesID); }