/// <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 }
private int GetOrCreateSourceID(Source source, DbConnection conn) { const string sqlSource = "SELECT SourceID FROM Sources WHERE Organization = ?"; const string sqlISOMetadata = "SELECT MetadataID FROM ISOMetadata WHERE Title = ? AND MetadataLink = ?"; string sqlSaveSource = "INSERT INTO Sources(Organization, SourceDescription, SourceLink, ContactName, Phone, " + "Email, Address, City, State, ZipCode, Citation, MetadataID) " + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?)" + LastRowIDSelect; string sqlSaveISOMetadata = "INSERT INTO ISOMetadata(TopicCategory, Title, Abstract, ProfileVersion, MetadataLink) " + "VALUES(?,?,?,?,?)" + LastRowIDSelect; int sourceID = 0; int isoMetadataID = 0; if (source != null) { using (DbCommand cmd14 = conn.CreateCommand()) { cmd14.CommandText = sqlSource; cmd14.Parameters.Add(_db.CreateParameter(DbType.String, source.Organization)); var sourceIDResult = cmd14.ExecuteScalar(); if (sourceIDResult != null) { sourceID = Convert.ToInt32(sourceIDResult); } } if (sourceID == 0) { ISOMetadata isoMetadata = source.ISOMetadata; using (DbCommand cmd15 = conn.CreateCommand()) { cmd15.CommandText = sqlISOMetadata; cmd15.Parameters.Add(_db.CreateParameter(DbType.String, isoMetadata.Title)); cmd15.Parameters.Add(_db.CreateParameter(DbType.String, isoMetadata.MetadataLink)); var isoMetadataIDResult = cmd15.ExecuteScalar(); if (isoMetadataIDResult != null) { isoMetadataID = Convert.ToInt32(isoMetadataIDResult); } } if (isoMetadataID == 0) { using (DbCommand cmd16 = conn.CreateCommand()) { cmd16.CommandText = sqlSaveISOMetadata; cmd16.Parameters.Add(_db.CreateParameter(DbType.String, isoMetadata.TopicCategory)); cmd16.Parameters.Add(_db.CreateParameter(DbType.String, isoMetadata.Title)); cmd16.Parameters.Add(_db.CreateParameter(DbType.String, isoMetadata.Abstract)); cmd16.Parameters.Add(_db.CreateParameter(DbType.String, isoMetadata.ProfileVersion)); cmd16.Parameters.Add(_db.CreateParameter(DbType.String, isoMetadata.MetadataLink)); var isoMetadataIDResult = cmd16.ExecuteScalar(); isoMetadataID = Convert.ToInt32(isoMetadataIDResult); } } using (DbCommand cmd17 = conn.CreateCommand()) { cmd17.CommandText = sqlSaveSource; cmd17.Parameters.Add(_db.CreateParameter(DbType.String, source.Organization)); cmd17.Parameters.Add(_db.CreateParameter(DbType.String, source.Description)); cmd17.Parameters.Add(_db.CreateParameter(DbType.String, source.Link)); cmd17.Parameters.Add(_db.CreateParameter(DbType.String, source.ContactName)); cmd17.Parameters.Add(_db.CreateParameter(DbType.String, source.Phone)); cmd17.Parameters.Add(_db.CreateParameter(DbType.String, source.Email)); cmd17.Parameters.Add(_db.CreateParameter(DbType.String, source.Address)); cmd17.Parameters.Add(_db.CreateParameter(DbType.String, source.City)); cmd17.Parameters.Add(_db.CreateParameter(DbType.String, source.State)); cmd17.Parameters.Add(_db.CreateParameter(DbType.Int32, source.ZipCode)); cmd17.Parameters.Add(_db.CreateParameter(DbType.String, source.Citation)); cmd17.Parameters.Add(_db.CreateParameter(DbType.String, isoMetadataID)); var sourceIDResult = cmd17.ExecuteScalar(); sourceID = Convert.ToInt32(sourceIDResult); } } } return(sourceID); }