private int GetOrCreateQualityControlLevelID(QualityControlLevel qc, DbConnection conn)
{
const string sqlQuality = "SELECT QualityControlLevelID FROM QualityControlLevels WHERE Definition = ?";
string sqlSaveQualityControl = "INSERT INTO QualityControlLevels(QualityControlLevelCode, Definition, Explanation) " +
"VALUES(?,?,?)" + LastRowIDSelect;
int qualityControlLevelID = 0;
if (qc != null)
{
using (DbCommand cmd12 = conn.CreateCommand()) {
cmd12.CommandText = sqlQuality;
cmd12.Parameters.Add(_db.CreateParameter(DbType.String, qc.Definition));
var qualityControlLevelIDResult = cmd12.ExecuteScalar();
if (qualityControlLevelIDResult != null)
{
qualityControlLevelID = Convert.ToInt32(qualityControlLevelIDResult);
}
}
if (qualityControlLevelID == 0)
{
using (DbCommand cmd13 = conn.CreateCommand()) {
cmd13.CommandText = sqlSaveQualityControl;
cmd13.Parameters.Add(_db.CreateParameter(DbType.String, qc.Code));
cmd13.Parameters.Add(_db.CreateParameter(DbType.String, qc.Definition));
cmd13.Parameters.Add(_db.CreateParameter(DbType.String, qc.Explanation));
var qualityControlLevelIDResult = cmd13.ExecuteScalar();
qualityControlLevelID = Convert.ToInt32(qualityControlLevelIDResult);
}
}
}
return(qualityControlLevelID);
}
public static QualityControlLevel CreateRandomQualityControlLevel()
{
Random rnd = new Random();
QualityControlLevel qc = new QualityControlLevel();
int codeNumber = rnd.Next(2);
qc.Code = codeNumber.ToString();
if (codeNumber == 0)
{
qc.Definition = "Raw Data (DataManagerSQL)";
qc.Explanation = qc.Definition + "explanation..";
qc.OriginId = codeNumber;
}
else if (codeNumber == 1)
{
qc.Definition = "Quality controlled data (DataManagerSQL)";
qc.Explanation = qc.Definition + "explanation..";
qc.OriginId = codeNumber;
}
else
{
qc.Definition = "new unit test QualityControlLevel-" + rnd.Next(1000);
qc.Explanation = qc.Definition + " explanation.";
}
return(qc);
}
public void SaveSeries_Simple()
{
var manager = RepositoryFactory.Instance.Get <IRepositoryManager>(TestConfig.DbOperations);
Site mySite = CreateRandomSite();
Variable myVariable = CreateRandomVariable();
Method myMethod = CreateRandomMethod();
QualityControlLevel myQc = CreateRandomQualityControlLevel();
Source mySource = Source.Unknown; //TODO Change to a 'Random Source'
Series mySeries = CreateRandomSeries(mySite, myVariable, myMethod, myQc, mySource);
Theme myTheme = new Theme("DataManagerSQL-Theme");
manager.SaveSeries(mySeries, myTheme, OverwriteOptions.Copy);
}
/// <summary>
/// Reads the DataValues section
/// </summary>
protected override IList <Series> ReadDataValues(XmlReader r)
{
int valueCount;
var lst = new List <DataValueWrapper>(Int32.TryParse(r.GetAttribute("count"), out valueCount) ? valueCount : 4);
var qualifiers = new Dictionary <string, Qualifier>();
var methods = new Dictionary <string, Method>();
var sources = new Dictionary <string, Source>();
var qualityControlLevels = new Dictionary <string, QualityControlLevel>();
var samples = new Dictionary <string, Sample>();
var labMethods = new Dictionary <string, LabMethod>();
var offsets = new Dictionary <string, OffsetType>();
var seriesDictionary = new Dictionary <string, Series>();
while (r.Read())
{
if (r.NodeType == XmlNodeType.Element)
{
if (XmlContext.AdvanceReaderPastEmptyElement(r))
{
//Empty element - advance and continue...
continue;
}
if (r.Name == "value")
{
//create a new empty data value and add it to the list
var wrapper = new DataValueWrapper();
var val = new DataValue();
wrapper.DataValue = val;
lst.Add(wrapper);
if (r.HasAttributes)
{
var censorCode = r.GetAttribute("censorCode");
if (!string.IsNullOrEmpty(censorCode))
{
val.CensorCode = censorCode;
}
val.LocalDateTime = Convert.ToDateTime(r.GetAttribute("dateTime"), CultureInfo.InvariantCulture);
//utcOffset
var utcOffset = r.GetAttribute("timeOffset");
val.UTCOffset = !String.IsNullOrEmpty(utcOffset) ? ConvertUtcOffset(utcOffset) : 0.0;
//dateTimeUtc
var dateTimeUTC = r.GetAttribute("dateTimeUTC");
val.DateTimeUTC = !String.IsNullOrEmpty(dateTimeUTC) ? Convert.ToDateTime(dateTimeUTC, CultureInfo.InvariantCulture) : val.LocalDateTime;
//method
var methodID = r.GetAttribute("methodCode");
if (String.IsNullOrEmpty(methodID))
{
//try methodID instead of methodCode
methodID = r.GetAttribute("methodID");
if (String.IsNullOrEmpty(methodID))
{
methodID = "unknown"; //when a method is unspecified
}
}
if (!methods.ContainsKey(methodID))
{
var newMethod = Method.Unknown;
methods.Add(methodID, newMethod);
}
wrapper.MethodID = methodID;
//quality control level
var qualityCode = r.GetAttribute("qualityControlLevelCode");
if (String.IsNullOrEmpty(qualityCode))
{
qualityCode = r.GetAttribute("qualityControlLevelID");
if (string.IsNullOrEmpty(qualityCode))
{
qualityCode = "unknown"; //when the quality control level is unspecified
}
}
//BCC - 24-Aug-2016 - Check for a quality code of space-delimited terms...
string[] terms = qualityCode.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
foreach (var term in terms)
{
if (!qualityControlLevels.ContainsKey(term))
{
var qualControl = QualityControlLevel.Unknown;
qualControl.Code = term;
qualControl.Definition = term;
qualControl.Explanation = term;
qualityControlLevels.Add(term, qualControl);
}
}
wrapper.QualityID = qualityCode;
//source
string sourceID = r.GetAttribute("sourceCode");
if (string.IsNullOrEmpty(sourceID))
{
sourceID = r.GetAttribute("sourceID");
if (String.IsNullOrEmpty(sourceID))
{
sourceID = "unknown"; //when a source is unspecified
}
}
if (!sources.ContainsKey(sourceID))
{
sources.Add(sourceID, Source.Unknown);
}
wrapper.SourceID = sourceID;
wrapper.SeriesCode = SeriesCodeHelper.CreateSeriesCode(methodID, qualityCode, sourceID); //----method-source-qualityControl combination----
//sample
string sampleCode = r.GetAttribute("labSampleCode");
if (!String.IsNullOrEmpty(sampleCode))
{
if (!samples.ContainsKey(sampleCode))
{
samples.Add(sampleCode, Sample.Unknown);
}
}
wrapper.SampleID = sampleCode;
//qualifiers
string qualifierCodes = r.GetAttribute("qualifiers");
if (!String.IsNullOrEmpty(qualifierCodes))
{
if (!qualifiers.ContainsKey(qualifierCodes))
{
var newQualifier = new Qualifier {
Code = qualifierCodes
};
qualifiers.Add(qualifierCodes, newQualifier);
val.Qualifier = newQualifier;
}
else
{
val.Qualifier = qualifiers[qualifierCodes];
}
}
//vertical offset
string offsetCode = r.GetAttribute("offsetTypeCode");
if (string.IsNullOrEmpty(offsetCode))
{
offsetCode = r.GetAttribute("offsetTypeID");
}
if (!String.IsNullOrEmpty(offsetCode))
{
if (!offsets.ContainsKey(offsetCode))
{
offsets.Add(offsetCode, new OffsetType());
}
string offsetValue = r.GetAttribute("offsetValue");
if (!String.IsNullOrEmpty(offsetValue))
{
val.OffsetValue = Convert.ToDouble(offsetValue, CultureInfo.InvariantCulture);
}
}
wrapper.OffsetID = offsetCode;
}
//data value
r.Read();
val.Value = r.ReadContentAsDouble();
}
else if (r.Name == "method")
{
var method = ReadMethod(r);
var methodCodeKey = method.Code.ToString(CultureInfo.InvariantCulture);
if (methods.ContainsKey(methodCodeKey))
{
methods[methodCodeKey] = method;
}
}
else if (r.Name == "source")
{
var source = ReadSource(r);
var sourceCodeKey = source.OriginId.ToString(CultureInfo.InvariantCulture);
if (sources.ContainsKey(sourceCodeKey))
{
sources[sourceCodeKey] = source;
}
}
else if (r.Name == "qualityControlLevel")
{
var qcLevel = ReadQualityControlLevel(r);
var qcCodeKey = qcLevel.Code;
if (qualityControlLevels.ContainsKey(qcCodeKey))
{
qualityControlLevels[qcCodeKey] = qcLevel;
}
}
else if (r.Name == "qualifier")
{
ReadQualifier(r, qualifiers);
}
else if (r.Name == "sample")
{
ReadSample(r, samples, labMethods);
}
else if (r.Name == "offset")
{
ReadOffset(r, offsets);
}
}
}
//to assign special properties of each data value
foreach (var wrapper in lst)
{
var val = wrapper.DataValue;
//which series does the data value belong to?
var seriesCode = wrapper.SeriesCode;
if (!seriesDictionary.ContainsKey(seriesCode))
{
Series newSeries = new Series();
seriesDictionary.Add(seriesCode, newSeries);
//assign method, source and qual.control level
try
{
newSeries.Method = methods[SeriesCodeHelper.GetMethodCode(seriesCode)];
newSeries.Source = sources[SeriesCodeHelper.GetSourceCode(seriesCode)];
//BCC - 24-Aug-2016 - Add logic to handle space-delimited quality codes...
var qcCode = SeriesCodeHelper.GetQualityCode(seriesCode);
string[] terms = qcCode.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
if (1 == terms.Length)
{
//One quality code found...
newSeries.QualityControlLevel = qualityControlLevels[SeriesCodeHelper.GetQualityCode(seriesCode)];
}
else
{
//Multiple quality codes found...
var qcl = new QualityControlLevel();
string separator = " | ";
qcl.Code = qcCode;
qcl.Definition = String.Empty;
qcl.Explanation = String.Empty;
foreach (var term in terms)
{
var qclTemp = qualityControlLevels[term];
qcl.Definition += qclTemp.Definition + separator;
qcl.Explanation += qclTemp.Explanation + separator;
}
qcl.Definition = qcl.Definition.Substring(0, qcl.Definition.Length - separator.Length);
qcl.Explanation = qcl.Explanation.Substring(0, qcl.Explanation.Length - separator.Length);
//Set the OriginId to a minimum value to indicate a 'pseudo' instance...
int min = qualityControlLevels.Values.Min(value => value.OriginId);
qcl.OriginId = min - 1;
newSeries.QualityControlLevel = qcl;
//Add compound key to dictionary, if indicated...
if (!qualityControlLevels.ContainsKey(qcl.Code))
{
qualityControlLevels.Add(qcl.Code, qcl);
}
}
}
catch (Exception ex)
{
//Any exception happening here?
string msg = ex.Message;
int n = 5;
++n;
}
}
//add the data value to the correct series
var series = seriesDictionary[seriesCode];
series.DataValueList.Add(val);
val.Series = series;
Sample sample;
if (!string.IsNullOrEmpty(wrapper.SampleID) &&
samples.TryGetValue(wrapper.SampleID, out sample))
{
val.Sample = sample;
}
OffsetType offset;
if (!string.IsNullOrEmpty(wrapper.OffsetID) &&
offsets.TryGetValue(wrapper.OffsetID, out offset))
{
val.OffsetType = offset;
}
if (series.Method == null)
{
series.Method = methods[wrapper.MethodID];
}
if (series.Source == null)
{
series.Source = sources[wrapper.SourceID];
}
if (series.QualityControlLevel == null)
{
series.QualityControlLevel = qualityControlLevels[wrapper.QualityID];
}
}
//to check the qualifiers
CheckQualifiers(qualifiers);
return(seriesDictionary.Values.ToList());
}
/// <summary>
/// build HD data model to store time-series data
/// </summary>
/// <param name="link">the link that was added to the composition</param>
public void CreateSeries(ILink link)
{
#region Create DataModel Objects [HACK]
//---- create variable unit
HydroDesktop.Interfaces.ObjectModel.Unit VarUnit = new HydroDesktop.Interfaces.ObjectModel.Unit();
VarUnit.Name = link.SourceQuantity.Unit.Description; //defined by link
VarUnit.Abbreviation = link.SourceQuantity.Unit.ID; //defined by link
VarUnit.UnitsType = link.SourceQuantity.ID; //defined by link
//---- create time unit
HydroDesktop.Interfaces.ObjectModel.Unit TimeUnit = new HydroDesktop.Interfaces.ObjectModel.Unit();
TimeUnit.Name = "second"; //default value (cannot be changed)
TimeUnit.Abbreviation = "s"; //default value (cannot be changed)
TimeUnit.UnitsType = "Time"; //default value (cannot be changed)
//create unit
//HydroDesktop.Interfaces.ObjectModel.Unit unit = new HydroDesktop.Interfaces.ObjectModel.Unit();
//unit.Abbreviation = link.SourceQuantity.Unit.ID;
//---- create method
HydroDesktop.Interfaces.ObjectModel.Method method = new Method();
method.Link = link.SourceComponent.ModelID;
method.Link = "none"; //*default value
method.Description = link.SourceComponent.ModelDescription; //*default value
if (link.SourceComponent.ComponentDescription == null)
{
method.Description = "none";
}
//---- define data service info
DataServiceInfo dataservice = new DataServiceInfo();
dataservice.Abstract = "none"; //*default value
dataservice.Citation = "none"; //*default value
dataservice.ContactEmail = "none"; //*default value
dataservice.ContactName = "none"; //*default value
dataservice.EastLongitude = -999; //*default value
dataservice.HarveDateTime = DateTime.Now; //*default value
dataservice.Id = -999; //*default value
dataservice.NorthLatitude = -999; //*default value
dataservice.HISCentralID = -999; //*default value
dataservice.ServiceCode = "none"; //*default value
dataservice.DescriptionURL = "none"; //*default value
dataservice.EndpointURL = "none"; //*default value
dataservice.ServiceName = "none"; //*default value
dataservice.Protocol = "none"; //*default value
dataservice.ServiceTitle = "none"; //*default value
dataservice.ServiceType = "none"; //*default value
dataservice.Version = -999; //*default value
dataservice.SiteCount = link.SourceElementSet.ElementCount; //defined by link
//---- create metadata
ISOMetadata meta = new ISOMetadata();
meta.Abstract = "none"; //*default value
meta.Id = -999; //*default value
meta.ProfileVersion = "none"; //*default value
meta.Title = "none"; //*default value
meta.TopicCategory = "none"; //*default value
meta.MetadataLink = "none"; //*default value
//---- create source
Source source = new Source();
source.Organization = "University of South Carolina"; //*default value
source.Address = "300 Main St."; //*default value
source.Citation = "none"; //*default value
source.City = "Columbia"; //*default value
source.ContactName = "none"; //*default value
source.Description = "none"; //*default value
source.Email = "none"; //*default value
source.Id = -999; //*default value
source.Link = "none"; //*default value
source.OriginId = -999; //*default value
source.Phone = "none"; //*default value
source.State = "SC"; //*default value
source.ZipCode = 29206; //*default value
source.ISOMetadata = meta;
//---- create variable
Variable variable = new Variable();
variable.Code = link.SourceQuantity.Description; //defined by link
variable.Name = link.SourceQuantity.ID; //defined by link
variable.VariableUnit = VarUnit; //defined by link
variable.TimeUnit = TimeUnit; //defined by link
variable.Speciation = "H20"; //*default value
variable.GeneralCategory = "Hydrology"; //*default value
variable.NoDataValue = -999; //*default value
variable.SampleMedium = "Surface Water"; //*default value
variable.TimeSupport = 1; //TODO: determine in finish
variable.VocabularyPrefix = "none"; //*default value
variable.ValueType = "Model Simulation Result"; //*default value
variable.DataType = "Incremental"; //*default value
//---- create qualControl
QualityControlLevel qualControl = new QualityControlLevel();
qualControl.Code = "qual1"; //*default value
qualControl.Definition = "Quality control level 1"; //*default value
qualControl.Id = 1; //*default value
qualControl.Explanation = "unknown"; //*default value
qualControl.OriginId = -999; //*default value
#endregion
#region Build Sites
//RepositoryManagerSQL db = null;
//check to see if the database path is overridden
if (conn != null)
{
// db = new RepositoryManagerSQL(DatabaseTypes.SQLite, conn);
}
else
{
//db = new RepositoryManagerSQL(DatabaseTypes.SQLite, Settings.Instance.DataRepositoryConnectionString);
//conn = Settings.Instance.DataRepositoryConnectionString;
}
//---- override default db info with those provided by omi
//-- standard omi args
if (dbargs.ContainsKey("Method.Description"))
{
method.Description = dbargs["Method.Description"];
}
if (dbargs.ContainsKey("Source.Organization"))
{
source.Organization = dbargs["Source.Organization"];
}
if (dbargs.ContainsKey("Source.Address"))
{
source.Address = dbargs["Source.Address"];
}
if (dbargs.ContainsKey("Source.City"))
{
source.City = dbargs["Source.City"];
}
if (dbargs.ContainsKey("Source.State"))
{
source.State = dbargs["Source.State"];
}
if (dbargs.ContainsKey("Source.Zip"))
{
source.ZipCode = Convert.ToInt32(dbargs["Source.Zip"]);
}
if (dbargs.ContainsKey("Source.Contact"))
{
source.ContactName = dbargs["Source.Contact"];
}
if (dbargs.ContainsKey("Variable.Category"))
{
variable.GeneralCategory = dbargs["Variable.Category"];
}
if (dbargs.ContainsKey("Variable.SampleMedium"))
{
variable.SampleMedium = dbargs["Variable.SampleMedium"];
}
//-extra omi args
if (dbargs.ContainsKey("Method.Link"))
{
method.Link = dbargs["Method.Link"];
}
if (dbargs.ContainsKey("DataService.Abstract"))
{
dataservice.Abstract = dbargs["DataService.Abstract"];
}
if (dbargs.ContainsKey("DataService.Citation"))
{
dataservice.Citation = dbargs["DataService.Citation"];
}
if (dbargs.ContainsKey("DataService.ContactEmail"))
{
dataservice.ContactEmail = dbargs["DataService.ContactEmail"];
}
if (dbargs.ContainsKey("DataService.ContactName"))
{
dataservice.ContactName = dbargs["DataService.ContactName"];
}
if (dbargs.ContainsKey("DataService.EastLongitude"))
{
dataservice.EastLongitude = Convert.ToDouble(dbargs["DataService.EastLongitude"]);
}
if (dbargs.ContainsKey("DataService.HarveDateTime"))
{
dataservice.HarveDateTime = Convert.ToDateTime(dbargs["DataService.HarveDateTime"]);
}
if (dbargs.ContainsKey("DataService.ID"))
{
dataservice.Id = Convert.ToInt64(dbargs["DataService.ID"]);
}
if (dbargs.ContainsKey("DataService.NorthLatitude"))
{
dataservice.NorthLatitude = Convert.ToDouble(dbargs["DataService.NorthLatitude"]);
}
if (dbargs.ContainsKey("DataService.HISCentralID"))
{
dataservice.HISCentralID = Convert.ToInt32(dbargs["DataService.HISCentralID"]);
}
if (dbargs.ContainsKey("DataService.ServiceCode"))
{
dataservice.ServiceCode = dbargs["DataService.ServiceCode"];
}
if (dbargs.ContainsKey("DataService.DescriptionURL"))
{
dataservice.DescriptionURL = dbargs["DataService.DescriptionURL"];
}
if (dbargs.ContainsKey("DataService.EndpointURL"))
{
dataservice.EndpointURL = dbargs["DataService.EndpointURL"];
}
if (dbargs.ContainsKey("DataService.ServiceName"))
{
dataservice.ServiceName = dbargs["DataService.ServiceName"];
}
if (dbargs.ContainsKey("DataService.Protocol"))
{
dataservice.Protocol = dbargs["DataService.Protocol"];
}
if (dbargs.ContainsKey("DataService.ServiceTitle"))
{
dataservice.ServiceTitle = dbargs["DataService.ServiceTitle"];
}
if (dbargs.ContainsKey("DataService.ServiceType"))
{
dataservice.ServiceType = dbargs["DataService.ServiceType"];
}
if (dbargs.ContainsKey("DataService.Version"))
{
dataservice.Version = Convert.ToDouble(dbargs["DataService.Version"]);
}
if (dbargs.ContainsKey("ISOMetadata.Abstract"))
{
meta.Abstract = dbargs["ISOMetadata.Abstract"];
}
if (dbargs.ContainsKey("ISOMetadata.ID"))
{
meta.Id = Convert.ToInt64(dbargs["ISOMetadata.ID"]);
}
if (dbargs.ContainsKey("ISOMetadata.ProfileVersion"))
{
meta.ProfileVersion = dbargs["ISOMetadata.ProfileVersion"];
}
if (dbargs.ContainsKey("ISOMetadata.Title"))
{
meta.Title = dbargs["ISOMetadata.Title"];
}
if (dbargs.ContainsKey("ISOMetadata.TopicCategory"))
{
meta.TopicCategory = dbargs["ISOMetadata.TopicCategory"];
}
if (dbargs.ContainsKey("ISOMetadata.MetadataLink"))
{
meta.MetadataLink = dbargs["ISOMetadata.MetadataLink"];
}
if (dbargs.ContainsKey("Source.Citation"))
{
source.Citation = dbargs["Source.Citation"];
}
if (dbargs.ContainsKey("Source.Description"))
{
source.Description = dbargs["Source.Description"];
}
if (dbargs.ContainsKey("Source.Email"))
{
source.Email = dbargs["Source.Email"];
}
if (dbargs.ContainsKey("Source.ID"))
{
source.Id = Convert.ToInt64(dbargs["Source.ID"]);
}
if (dbargs.ContainsKey("Source.Link"))
{
source.Link = dbargs["Source.Link"];
}
if (dbargs.ContainsKey("Source.OrginID"))
{
source.OriginId = Convert.ToInt32(dbargs["Source.OrginID"]);
}
if (dbargs.ContainsKey("Source.Phone"))
{
source.Phone = dbargs["Source.Phone"];
}
if (dbargs.ContainsKey("Variable.Code"))
{
variable.Code = dbargs["Variable.Code"];
}
if (dbargs.ContainsKey("Variable.Name"))
{
variable.Name = dbargs["Variable.Name"];
}
if (dbargs.ContainsKey("Variable.Speciation"))
{
variable.Speciation = dbargs["Variable.Speciation"];
}
if (dbargs.ContainsKey("Variable.NoDataValue"))
{
variable.NoDataValue = Convert.ToDouble(dbargs["Variable.NoDataValue"]);
}
if (dbargs.ContainsKey("Variable.VocabPrefix"))
{
variable.VocabularyPrefix = dbargs["Variable.VocabPrefix"];
}
if (dbargs.ContainsKey("Variable.ValueType"))
{
variable.ValueType = dbargs["Variable.ValueType"];
}
if (dbargs.ContainsKey("Variable.DataValue"))
{
variable.DataType = dbargs["Variable.DataValue"];
}
if (dbargs.ContainsKey("QualityControl.Code"))
{
qualControl.Code = dbargs["QualityControl.Code"];
}
if (dbargs.ContainsKey("QualityControl.Definition"))
{
qualControl.Definition = dbargs["QualityControl.Definition"];
}
if (dbargs.ContainsKey("QualityControl.ID"))
{
qualControl.Id = Convert.ToInt64(dbargs["QualityControl.ID"]);
}
if (dbargs.ContainsKey("QualityControl.Explanation"))
{
qualControl.Explanation = dbargs["QualityControl.Explanation"];
}
if (dbargs.ContainsKey("QualityControl.OriginId"))
{
qualControl.OriginId = Convert.ToInt32(dbargs["QualityControl.OriginId"]);
}
#region create sites
Dictionary <long, Site> sites = new Dictionary <long, Site>();
//loop through all elements in the source components element set
for (int i = 0; i < link.SourceElementSet.ElementCount; ++i)
{
//TODO: Get spatial reference from elementset
//---- define spatial reference
HydroDesktop.Interfaces.ObjectModel.SpatialReference spatial = new HydroDesktop.Interfaces.ObjectModel.SpatialReference();
spatial.Id = 18; //*
spatial.Notes = "NAD27-UTM zone 17N projected coordinate"; //*
spatial.SRSID = 26717; //*
spatial.SRSName = "NAD27 / UTM zone 17N"; //*
//--- create site ---
Site site = new Site();
//create a unique site name [variable_model_location]
site.Name = (link.SourceElementSet.ID + "_" + link.SourceComponent.ModelID + "_loc" + i.ToString()).Replace(' ', '_');
//check if a sitename already exists in the repository
string sql = "SELECT s.SiteCode, s.SiteID " +
"FROM Sites s " +
"WHERE s.SiteName= '" + site.Name + "' ";
DbOperations _db = new DbOperations(conn, DatabaseTypes.SQLite);
System.Data.DataTable tbl = _db.LoadTable("values", sql);
if (tbl.Rows.Count > 0)
{
site.Code = tbl.Rows[0].ItemArray[0].ToString();
site.Id = Convert.ToInt32(tbl.Rows[0].ItemArray[1]);
}
else
{
//create a new site
sql = "SELECT s.SiteCode, s.SiteID FROM Sites s ";
_db = new DbOperations(conn, DatabaseTypes.SQLite);
tbl = _db.LoadTable("values", sql);
int last_row = tbl.Rows.Count - 1;
site.Code = site.Name;
//-- if the database is not blank
if (last_row >= 0)
{
site.Id = Convert.ToInt32(tbl.Rows[last_row].ItemArray[1]) + 2 + new_series_count;
}
else
{
site.Id = new_series_count++;
}
//add 1 to new series count so that the same site code isn't selected twice
new_series_count++;
}
site.SpatialReference = spatial;
site.Comments = "none";
site.County = "none";
site.Elevation_m = -999;
site.Latitude = -999;
site.LocalProjection = spatial;
site.LocalX = -999;
site.LocalY = -999;
site.Longitude = -999;
//--- Attempt to spatially define elements 7-15-2010 ---
try
{
//save site latitude and longitude
if (link.SourceElementSet.ElementType == ElementType.XYPoint)
{
site.Latitude = link.SourceElementSet.GetYCoordinate(i, 0);
site.Longitude = link.SourceElementSet.GetXCoordinate(i, 0);
site.LocalX = 0;
site.LocalY = 0;
}
else
{
////List<Point> points = new List<Point>();
////for(int p=0; p<= link.SourceElementSet.GetVertexCount(i)-1; p++)
////{
//// Point point = new Point(link.SourceElementSet.GetXCoordinate(i, p),
//// link.SourceElementSet.GetYCoordinate(i, p));
//// points.Add(point);
////}
//////create polyline
////LineString ls = new LineString(points);
////new SharpMap.CoordinateSystems.ProjectedCoordinateSystem()
////ls.SpatialReference =
site.Latitude = link.SourceElementSet.GetYCoordinate(i, 0);
site.Longitude = link.SourceElementSet.GetXCoordinate(i, 0);
//link.SourceElementSet.SpatialReference.ID;
site.LocalX = 0;
site.LocalY = 0;
}
}
catch (Exception) { }
site.NetworkPrefix = "none";
site.PosAccuracy_m = -999;
site.State = "SC"; //*
//site.TimeZone = new TimeZoneInfo.TransitionTime();
site.VerticalDatum = "unknown"; //*
if (!sites.ContainsKey(site.Id))
{
//add site and series to dictionary if they don't already exist
sites.Add(site.Id, site);
Series series = new Series(site, variable, method, qualControl, source);
series.Id = i;
if (!serieses.ContainsKey(site.Name))
{
serieses.Add(site.Name, series);
}
//else{serieses.Add(site.Name, series);}
}
}
#endregion
#endregion
}
//public void TestDeleteSeries1()
//{
// //1) create series1 at site1
// //2) create series2 at site1
// //3) delete series1 - site1 should remain
// //4) delete series2 - site2 should be deleted
// Random rnd = new Random();
// Series s1 = DataSeriesTest.CreateSeries(rnd.Next(10000));
// Site mySite = SitesTest.CreateSite(rnd.Next(10000));
// RepositoryManagerSQL manager = TestConfig.SQLRepositoryManager;
// DataTable fullSeriesTable = manager.GetSeriesListTable();
//}
private static Series CreateRandomSeries(Site site, Variable variable, Method method, QualityControlLevel qc, Source source)
{
Random rnd = new Random();
int numDays = rnd.Next(2000);
DateTime start = DateTime.Now.Date.AddDays(-numDays);
double utcOffset = -7;
Series newSeries = new Series(site, variable, method, qc, source);
newSeries.IsCategorical = false;
newSeries.LastCheckedDateTime = DateTime.Now;
newSeries.UpdateDateTime = DateTime.Now;
newSeries.BeginDateTime = start;
newSeries.BeginDateTimeUTC = start.AddHours(utcOffset);
newSeries.EndDateTime = DateTime.Now.Date;
newSeries.EndDateTimeUTC = newSeries.EndDateTime.AddHours(utcOffset);
for (int t = 0; t < numDays; t++)
{
DateTime obsTime = start.AddDays(t);
double val = 100.0 * rnd.NextDouble();
newSeries.DataValueList.Add(new DataValue(val, obsTime, utcOffset));
}
return(newSeries);
}
/// <summary>
/// Reads the DataValues section
/// </summary>
private IList <Series> ReadDataValues(XmlReader r)
{
IList <DataValue> lst = new List <DataValue>();
Dictionary <string, Qualifier> qualifiers = new Dictionary <string, Qualifier>();
Dictionary <string, Method> methods = new Dictionary <string, Method>();
Dictionary <string, Source> sources = new Dictionary <string, Source>();
Dictionary <string, QualityControlLevel> qualityControlLevels = new Dictionary <string, QualityControlLevel>();
Dictionary <string, Sample> samples = new Dictionary <string, Sample>();
Dictionary <string, OffsetType> offsets = new Dictionary <string, OffsetType>();
Dictionary <string, Series> seriesDictionary = new Dictionary <string, Series>();
//lookup for unique source, method, quality control level combination
Dictionary <DataValue, string> seriesCodeLookup = new Dictionary <DataValue, string>();
//lookup for samples, qualifiers and vertical offsets
var sampleLookup = new Dictionary <DataValue, string>();
var offsetLookup = new Dictionary <DataValue, string>();
var qualifierLookup = new Dictionary <DataValue, string>();
var methodLookup = new Dictionary <DataValue, string>();
var sourceLookup = new Dictionary <DataValue, string>();
while (r.Read())
{
if (r.NodeType == XmlNodeType.Element)
{
if (r.Name == "value")
{
//create a new empty data value and add it to the list
DataValue val = new DataValue();
lst.Add(val);
//the default value of censor code is 'nc'
val.CensorCode = "nc";
if (r.HasAttributes)
{
string censorCode = r.GetAttribute("censorCode");
if (!string.IsNullOrEmpty(censorCode))
{
val.CensorCode = r.GetAttribute("censorCode");
}
//fix by Jiri - sometimes the dateTime attribute is uppercase
string localDateTime = r.GetAttribute("dateTime");
if (string.IsNullOrEmpty(localDateTime))
{
localDateTime = r.GetAttribute("DateTime");
}
val.LocalDateTime = Convert.ToDateTime(localDateTime, CultureInfo.InvariantCulture);
val.DateTimeUTC = val.LocalDateTime;
val.UTCOffset = 0.0;
//method
string methodID = r.GetAttribute("methodID");
if (String.IsNullOrEmpty(methodID))
{
methodID = "0"; //when a method ID is unspecified
}
if (!methods.ContainsKey(methodID))
{
Method newMethod = Method.Unknown;
methods.Add(methodID, newMethod);
}
methodLookup.Add(val, methodID);
//quality control level
string qualityCode = r.GetAttribute("qualityControlLevel");
if (String.IsNullOrEmpty(qualityCode))
{
qualityCode = "unknown"; //when the quality control level is unspecified
}
if (!qualityControlLevels.ContainsKey(qualityCode))
{
QualityControlLevel qualControl = QualityControlLevel.Unknown;
qualControl.Code = qualityCode;
qualControl.Definition = qualityCode;
qualControl.Explanation = qualityCode;
qualityControlLevels.Add(qualityCode, qualControl);
}
//source
string sourceID = r.GetAttribute("sourceID");
if (String.IsNullOrEmpty(sourceID))
{
sourceID = "0"; //when a source ID is unspecified
}
if (!sources.ContainsKey(sourceID))
{
sources.Add(sourceID, Source.Unknown);
}
sourceLookup.Add(val, sourceID);
//----method-source-qualityControl combination----
string seriesCode = SeriesCode.CreateSeriesCode(methodID, qualityCode, sourceID);
seriesCodeLookup.Add(val, seriesCode);
//sample
string sampleID = r.GetAttribute("sampleID");
if (!String.IsNullOrEmpty(sampleID))
{
if (!samples.ContainsKey(sampleID))
{
samples.Add(sampleID, Sample.Unknown);
sampleLookup.Add(val, sampleID);
}
}
//qualifiers
string qualifierCodes = r.GetAttribute("qualifiers");
if (!String.IsNullOrEmpty(qualifierCodes))
{
if (!qualifiers.ContainsKey(qualifierCodes))
{
Qualifier newQualifier = new Qualifier();
newQualifier.Code = qualifierCodes;
qualifiers.Add(qualifierCodes, newQualifier);
val.Qualifier = newQualifier;
}
else
{
val.Qualifier = qualifiers[qualifierCodes];
}
}
//vertical offset
string offsetID = r.GetAttribute("offsetTypeID");
if (!String.IsNullOrEmpty(offsetID))
{
if (!offsets.ContainsKey(offsetID))
{
offsets.Add(offsetID, new OffsetType());
}
offsetLookup.Add(val, offsetID);
string offsetValue = r.GetAttribute("offsetValue");
if (!String.IsNullOrEmpty(offsetValue))
{
val.OffsetValue = Convert.ToDouble(offsetValue, CultureInfo.InvariantCulture);
}
}
//data value
string strVal = r.ReadString();
val.Value = Convert.ToDouble(strVal, CultureInfo.InvariantCulture);
//val.Value = Convert.ToDouble(r.ReadElementString());
//val.Value = Convert.ToDouble(r.ReadInnerXml());
//val.Value = Convert.ToDouble(r.Value); //r.ReadElementContentAsDouble();
}
}
else if (r.Name == "method")
{
ReadMethod(r, methods);
}
else if (r.Name == "source")
{
ReadSource(r, sources);
}
else if (r.Name == "qualityControlLevel")
{
//quality control level seems to be included with each value
}
else if (r.Name == "qualifier")
{
ReadQualifier(r, qualifiers);
}
else if (r.Name == "sample")
{
ReadSample(r, samples);
}
else if (r.Name == "offset")
{
ReadOffset(r, offsets);
}
}
}
//to assign special properties of each data value
foreach (DataValue val in lst)
{
//which series does the data value belong to?
string seriesCode = seriesCodeLookup[val];
if (!seriesDictionary.ContainsKey(seriesCode))
{
Series newSeries = new Series();
seriesDictionary.Add(seriesCode, newSeries);
//assign method, source and qual.control level
//assign method, source and qual.control level
try
{
newSeries.Method = methods[SeriesCode.GetMethodCode(seriesCode)];
//fix by Jiri: fixes the case when sourceID is unspecified sources
//has more than one source entry
string sourceCode = SeriesCode.GetSourceCode(seriesCode);
if (sourceCode == "0" && sources.Count > 0)
{
foreach (string sc in sources.Keys)
{
if (sc != "0")
{
sourceCode = sc;
break;
}
}
}
newSeries.Source = sources[sourceCode];
newSeries.QualityControlLevel = qualityControlLevels[SeriesCode.GetQualityCode(seriesCode)];
}
catch { }
}
//add the data value to the correct series
Series series = seriesDictionary[seriesCode];
series.DataValueList.Add(val);
val.Series = series;
if (sampleLookup.ContainsKey(val))
{
val.Sample = samples[sampleLookup[val]];
}
if (offsetLookup.ContainsKey(val))
{
val.OffsetType = offsets[offsetLookup[val]];
}
if (methodLookup.ContainsKey(val))
{
Method newMethod = methods[methodLookup[val]];
if (series.Method == null)
{
series.Method = newMethod;
}
}
if (sourceLookup.ContainsKey(val))
{
Source newSource = sources[sourceLookup[val]];
if (series.Method == null)
{
series.Source = newSource;
}
}
}
//to check the qualifiers
CheckQualifiers(qualifiers);
return(seriesDictionary.Values.ToList <Series>());
}
public Series GetSeriesByID(long seriesID)
{
var seriesTable = DbOperations.LoadTable("seriesTable", "select * from DataSeries where seriesID=" + seriesID);
if (seriesTable.Rows.Count == 0)
{
return(null);
}
var series = new Series();
DataRow seriesRow = seriesTable.Rows[0];
series.BeginDateTime = Convert.ToDateTime(seriesRow["BeginDateTime"]);
series.EndDateTime = Convert.ToDateTime(seriesRow["EndDateTime"]);
series.BeginDateTimeUTC = Convert.ToDateTime(seriesRow["BeginDateTimeUTC"]);
series.EndDateTimeUTC = Convert.ToDateTime(seriesRow["EndDatetimeUTC"]);
series.Id = seriesID;
series.IsCategorical = Convert.ToBoolean(seriesRow["IsCategorical"]);
series.LastCheckedDateTime = Convert.ToDateTime(seriesRow["LastCheckedDateTime"]);
series.UpdateDateTime = Convert.ToDateTime(seriesRow["UpdateDateTime"]);
series.Subscribed = Convert.ToBoolean(seriesRow["Subscribed"]);
series.ValueCount = Convert.ToInt32(seriesRow["ValueCount"]);
int siteID = Convert.ToInt32(seriesRow["SiteID"]);
string sqlSites = "SELECT SiteID, SiteCode, SiteName, Latitude, Longitude, Elevation_m, " +
"VerticalDatum, LocalX, LocalY, State, County, Comments FROM Sites where SiteID = " + siteID;
DataTable siteTable = DbOperations.LoadTable("siteTable", sqlSites);
if (siteTable.Rows.Count == 0)
{
return(null);
}
DataRow siteRow = siteTable.Rows[0];
var newSite = new Site();
newSite.Id = Convert.ToInt32(siteRow[0]);
newSite.Code = Convert.ToString(siteRow[1]);
newSite.Name = Convert.ToString(siteRow[2]);
newSite.Latitude = Convert.ToDouble(siteRow[3]);
newSite.Longitude = Convert.ToDouble(siteRow[4]);
newSite.Elevation_m = Convert.ToDouble(siteRow[5]);
newSite.VerticalDatum = Convert.ToString(siteRow[6]);
newSite.LocalX = Convert.ToDouble(siteRow["LocalX"]);
newSite.LocalY = Convert.ToDouble(siteRow["LocalY"]);
series.Site = newSite;
int variableID = Convert.ToInt32(seriesRow["VariableID"]);
series.Variable = RepositoryFactory.Instance.Get <IVariablesRepository>().GetByKey(variableID);
var newMethod = new Method {
Id = Convert.ToInt32(seriesRow["MethodID"])
};
series.Method = newMethod;
var newSource = new Source {
Id = Convert.ToInt32(seriesRow["SourceID"])
};
series.Source = newSource;
var newQC = new QualityControlLevel {
Id = Convert.ToInt32(seriesRow["QualityControlLevelID"])
};
series.QualityControlLevel = newQC;
return(series);
}
/// <summary>
/// Reads the DataValues section
/// </summary>
private Series ReadDataValues(XmlTextReader r)
{
Series series = new Series();
IList <DataValue> lst = series.DataValues;
Dictionary <string, Qualifier> qualifiers = new Dictionary <string, Qualifier>();
Dictionary <string, Method> methods = new Dictionary <string, Method>();
Dictionary <string, Source> sources = new Dictionary <string, Source>();
Dictionary <string, QualityControlLevel> qualityControlLevels = new Dictionary <string, QualityControlLevel>();
Dictionary <string, Sample> samples = new Dictionary <string, Sample>();
Dictionary <string, OffsetType> offsets = new Dictionary <string, OffsetType>();
//lookup for samples, qualifiers and vertical offsets
var sampleLookup = new Dictionary <DataValue, string>();
var offsetLookup = new Dictionary <DataValue, string>();
//var qualifierLookup = new Dictionary<DataValue, string>();
var methodLookup = new Dictionary <DataValue, string>();
var sourceLookup = new Dictionary <DataValue, string>();
while (r.Read())
{
if (r.NodeType == XmlNodeType.Element)
{
if (r.Name == "values" && r.HasAttributes)
{
series.ValueCount = Convert.ToInt32(r.GetAttribute("count"));
}
else if (r.Name == "value")
{
DataValue val = series.CreateDataValue();
//the default value of censor code is 'nc'
val.CensorCode = "nc";
if (r.HasAttributes)
{
string censorCode = r.GetAttribute("censorCode");
if (!string.IsNullOrEmpty(censorCode))
{
val.CensorCode = r.GetAttribute("censorCode");
}
val.LocalDateTime = Convert.ToDateTime(r.GetAttribute("dateTime"));
val.DateTimeUTC = val.LocalDateTime;
val.UTCOffset = 0.0;
//method
string methodID = r.GetAttribute("methodID");
if (String.IsNullOrEmpty(methodID))
{
methodID = "unknown"; //when a method is unspecified
}
if (!methods.ContainsKey(methodID))
{
Method newMethod = Method.Unknown;
methods.Add(methodID, newMethod);
}
methodLookup.Add(val, methodID);
//quality control level
string qualityCode = r.GetAttribute("qualityControlLevel");
if (String.IsNullOrEmpty(qualityCode))
{
qualityCode = "unknown"; //when the quality control level is unspecified
}
if (!qualityControlLevels.ContainsKey(qualityCode))
{
QualityControlLevel qualControl = QualityControlLevel.Unknown;
qualControl.Code = qualityCode;
qualControl.Definition = qualityCode;
qualControl.Explanation = qualityCode;
qualityControlLevels.Add(qualityCode, qualControl);
val.QualityControlLevel = qualControl;
series.QualityControlLevels.Add(qualControl);
}
else
{
val.QualityControlLevel = qualityControlLevels[qualityCode];
}
//source
string sourceID = r.GetAttribute("sourceID");
if (String.IsNullOrEmpty(sourceID))
{
sourceID = "unknown"; //when a source is unspecified
}
if (!sources.ContainsKey(sourceID))
{
sources.Add(sourceID, Source.Unknown);
}
sourceLookup.Add(val, sourceID);
//sample
string sampleID = r.GetAttribute("sampleID");
if (!String.IsNullOrEmpty(sampleID))
{
if (!samples.ContainsKey(sampleID))
{
samples.Add(sampleID, new Sample());
sampleLookup.Add(val, sampleID);
}
}
//qualifiers
string qualifierCodes = r.GetAttribute("qualifiers");
if (!String.IsNullOrEmpty(qualifierCodes))
{
if (!qualifiers.ContainsKey(qualifierCodes))
{
Qualifier newQualifier = new Qualifier();
newQualifier.Code = qualifierCodes;
qualifiers.Add(qualifierCodes, newQualifier);
val.Qualifier = newQualifier;
}
else
{
val.Qualifier = qualifiers[qualifierCodes];
}
}
//vertical offset
string offsetID = r.GetAttribute("offsetTypeID");
if (!String.IsNullOrEmpty(offsetID))
{
if (!offsets.ContainsKey(offsetID))
{
offsets.Add(offsetID, new OffsetType());
}
offsetLookup.Add(val, offsetID);
string offsetValue = r.GetAttribute("offsetValue");
if (!String.IsNullOrEmpty(offsetValue))
{
val.OffsetValue = Convert.ToDouble(offsetValue);
}
}
}
//data value
r.Read();
val.Value = r.ReadContentAsDouble();
}
else if (r.Name == "method")
{
ReadMethod(r, methods);
}
else if (r.Name == "source")
{
ReadSource(r, sources);
}
else if (r.Name == "qualityControlLevel")
{
//quality control level seems to be included with each value
}
else if (r.Name == "qualifier")
{
ReadQualifier(r, qualifiers);
}
else if (r.Name == "sample")
{
ReadSample(r, samples);
}
else if (r.Name == "offset")
{
ReadOffset(r, offsets);
}
}
}
//to assign relations of each variable
foreach (DataValue val in lst)
{
if (sampleLookup.ContainsKey(val))
{
val.Sample = samples[sampleLookup[val]];
}
if (offsetLookup.ContainsKey(val))
{
val.OffsetType = offsets[offsetLookup[val]];
}
if (methodLookup.ContainsKey(val))
{
Method newMethod = methods[methodLookup[val]];
val.Method = newMethod;
if (!series.Methods.Contains(newMethod))
{
series.Methods.Add(newMethod);
}
}
if (sourceLookup.ContainsKey(val))
{
Source newSource = sources[sourceLookup[val]];
val.Source = newSource;
if (!series.Sources.Contains(newSource))
{
series.Sources.Add(newSource);
}
}
}
//to check the qualifiers
CheckQualifiers(qualifiers);
return(series);
}
