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);
}
