public WaterQualityDeployment Get(CompoundIdentity id) { if (!id.IsNullOrEmpty() && this.CanGet()) { NpgsqlCommand cmd = Db.GetCmd(Db.ConnectionString); cmd.CommandText = Db.SelectDeployment + Db.Where + Db.WhereId; cmd.Parameters.AddWithValue("sid", id.DataStoreIdentity); cmd.Parameters.AddWithValue("id", id.Identity); NpgsqlDataReader rdr = Db.ExecuteReader(cmd); WaterQualityDeployment o = null; if (rdr != null) { try { rdr.Read(); o = this.builder.Build(rdr); if (cmd.Connection.State == System.Data.ConnectionState.Open) { cmd.Connection.Close(); } } catch { } finally { cmd.Dispose(); } } return(o); } return(null); }
public bool Delete(WaterQualityDeployment item) { if (item != null && this.CanDelete(item)) { try { NpgsqlCommand cmd = Db.GetCmd(Db.ConnectionString); cmd.CommandText = Db.DeleteDeployment; cmd.Parameters.AddWithValue("sid", item.Identity.DataStoreIdentity); cmd.Parameters.AddWithValue("id", item.Identity.Identity); Db.ExecuteNonQuery(cmd); cmd.CommandText = Db.DeleteMeasurement; //remove all measurements in this deployment cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("dsid", item.Identity.DataStoreIdentity); cmd.Parameters.AddWithValue("did", item.Identity.Identity); Db.ExecuteNonQuery(cmd); return(true); } catch { } } return(false); }
public WaterQualityMeasurement Create(WaterQualityDeployment deployment, WaterQualityMeasurementDTO item) { if (deployment != null && item != null) { return(Create(deployment.Identity, item)); } return(null); }
public WaterQualityMeasurement Create(WaterQualityDeployment item, DateTime sampleDate, double?surfaceElevation, double?temperature, double?ph, double?dissolvedOxygen, double?conductivity, double?salinity, double?velocity) { if (item != null) { return(this.Create(item.Identity, sampleDate, surfaceElevation, temperature, ph, dissolvedOxygen, conductivity, salinity, velocity)); } return(null); }
public bool Update(WaterQualityDeployment item) { if (item != null && this.CanUpdate(item)) { try { NpgsqlCommand cmd = Db.GetCmd(Db.ConnectionString); cmd.CommandText = Db.UpdateDeployment; cmd.Parameters.AddWithValue("sid", item.Identity.DataStoreIdentity); cmd.Parameters.AddWithValue("id", item.Identity.Identity); cmd.Parameters.AddWithValue("n", item.Name); cmd.Parameters.AddWithValue("esid", item.SampleEventId.DataStoreIdentity); cmd.Parameters.AddWithValue("eid", item.SampleEventId.Identity); cmd.Parameters.AddWithValue("sitesid", item.SiteId.DataStoreIdentity); cmd.Parameters.AddWithValue("siteid", item.SiteId.Identity); cmd.Parameters.AddWithValue("isid", item.SensorId.DataStoreIdentity); cmd.Parameters.AddWithValue("iid", item.SensorId.Identity); if (item.Range.StartDate.HasValue) { cmd.Parameters.AddWithValue("start", item.Range.StartDate.Value); } else { cmd.Parameters.Add(NpgSqlCommandUtils.GetNullInParam("start", NpgsqlTypes.NpgsqlDbType.TimestampTZ)); } if (item.Range.EndDate.HasValue) { cmd.Parameters.AddWithValue("end", item.Range.EndDate.Value); } else { cmd.Parameters.Add(NpgSqlCommandUtils.GetNullInParam("end", NpgsqlTypes.NpgsqlDbType.TimestampTZ)); } if (string.IsNullOrEmpty(item.Description)) { cmd.Parameters.Add(NpgSqlCommandUtils.GetNullInParam("d", NpgsqlTypes.NpgsqlDbType.Varchar)); } else { cmd.Parameters.AddWithValue("d", item.Description); } cmd.Parameters.AddWithValue("priv", item.IsPrivate); Db.ExecuteNonQuery(cmd); return(true); } catch { } } return(false); }
public bool CanDelete(WaterQualityDeployment item) { if (item != null) { if (this.CanDelete()) { UserProvider prov = UserAffilationSecurityManager.Instance.GetProvider(this.Context); if (prov != null) { return(prov.HasAffiliationForSampleEvent(item.SampleEventId, true)); } } } return(false); }
private static void CreateMeasurementsCsv(CsvDb db, Dictionary <CompoundIdentity, Tuple <int, Site> > siteDict, Dictionary <CompoundIdentity, Tuple <int, SamplingEvent> > eventDict, Dictionary <CompoundIdentity, Tuple <int, WaterQualityDeployment> > deploymentDict, List <Tuple <int, WaterQualityMeasurement> > measurementList) { string[] cols = new string[] { "DataKey", "Deployment", "DeployDesc", "SamplingEventKey", "SiteKey", "DeployStart", "DeployEnd", "SampleDate", "SurfaceElevation", "Temperature", "pH", "DissolvedOxygen", "Conductivity", "Salinity", "Velocity" }; ITable measurements = db.Create("WaterQualityMeasurements", cols); var orderedMeasurements = measurementList.OrderBy(x => x.Item1); foreach (var meas in orderedMeasurements) { WaterQualityDeployment deploy = deploymentDict[meas.Item2.DeploymentId].Item2; WaterQualityMeasurement measurement = meas.Item2; int eventIndex = eventDict[deploy.SampleEventId].Item1; //deploy.SiteId could be a dangling reference string siteFK = na; if (siteDict.ContainsKey(deploy.SiteId)) { siteFK = siteDict[deploy.SiteId].Item1.ToString(); } IRow r = measurements.CreateRow(); r[0] = meas.Item1.ToString(); r[1] = deploy.Name; r[2] = deploy.Description; r[3] = eventIndex.ToString(); r[4] = siteFK; r[5] = deploy.Range.StartDate.ToString(); r[6] = deploy.Range.EndDate.ToString(); r[7] = measurement.SampleDate.ToString(); r[8] = measurement.SurfaceElevation.ToString(); r[9] = measurement.Temperature.ToString(); r[10] = measurement.pH.ToString(); r[11] = measurement.DissolvedOxygen.ToString(); r[12] = measurement.Conductivity.ToString(); r[13] = measurement.Salinity.ToString(); r[14] = measurement.Velocity.ToString(); measurements.AddRow(r); } measurements.Flush(); return; }
public static JObject ToJson(WaterQualityDeployment deployment) { if (deployment != null) { JObject o = new JObject(); o.Add(JsonUtils.Id, JsonUtils.ToJson(deployment.Identity)); o.Add(JsonUtils.Name, deployment.Name); o.Add("sampleeventid", JsonUtils.ToJson(deployment.SampleEventId)); o.Add("siteid", JsonUtils.ToJson(deployment.SiteId)); o.Add("sensorid", JsonUtils.ToJson(deployment.SensorId)); o.Add("startdate", deployment.Range.StartDate); o.Add("enddate", deployment.Range.EndDate); if (!string.IsNullOrEmpty(deployment.Description)) { o.Add(JsonUtils.Description, deployment.Description); } o.Add("isprivate", deployment.IsPrivate); return(o); } return(null); }
public IEnumerable <WaterQualityDeployment> GetForSite(CompoundIdentity siteId) { if (!siteId.IsNullOrEmpty() && this.CanGet()) { NpgsqlCommand cmd = Db.GetCmd(Db.ConnectionString); cmd.CommandText = Db.SelectDeployment + Db.Where + Db.WhereSite; cmd.Parameters.AddWithValue("sitesid", siteId.DataStoreIdentity); cmd.Parameters.AddWithValue("siteid", siteId.Identity); NpgsqlDataReader rdr = Db.ExecuteReader(cmd); WaterQualityDeployment o = null; List <WaterQualityDeployment> permissions = new List <WaterQualityDeployment>(); if (rdr != null) { try { while (rdr.Read()) { o = this.builder.Build(rdr); if (o != null) { permissions.Add(o); } } if (cmd.Connection.State == System.Data.ConnectionState.Open) { cmd.Connection.Close(); } } catch { } finally { cmd.Dispose(); } } return(permissions); } return(null); }
public bool CanDelete(WaterQualityMeasurement item) { if (item != null && this.CanDelete()) { if (!item.DeploymentId.Equals(lastDeploymentId)) { if (this.helperProvider == null) { this.helperProvider = PgWaterQualityProviderFactory.Instance.GetDeploymentProvider(this.Context); } if (this.helperProvider != null) { WaterQualityDeployment depl = this.helperProvider.Get(item.DeploymentId); if (depl != null) { CompoundIdentity se = depl.SampleEventId; if (se != this.lastSampleEventId) { UserProvider prov = UserAffilationSecurityManager.Instance.GetProvider(this.Context); if (prov != null) { this.lastEditPermission = prov.HasAffiliationForSampleEvent(se, true); this.lastGetPermission = prov.HasAffiliationForSampleEvent(se, false); this.lastSampleEventId = se; this.lastDeploymentId = item.DeploymentId; } } else //same sample event, different deployment { this.lastDeploymentId = item.DeploymentId; //don't need to re-check the sampleevent controls permission } } } } } return(false); }
public WaterQualityMeasurement Build(DbDataReader reader) { WaterQualityMeasurement tmp = new WaterQualityMeasurement(new CompoundIdentity(DbReaderUtils.GetGuid(reader, 0), DbReaderUtils.GetGuid(reader, 1)), Db.FixDate(DbReaderUtils.GetDate(reader, 2)), DbReaderUtils.GetNullableDouble(reader, 3), DbReaderUtils.GetNullableDouble(reader, 4), DbReaderUtils.GetNullableDouble(reader, 5), DbReaderUtils.GetNullableDouble(reader, 6), DbReaderUtils.GetNullableDouble(reader, 7), DbReaderUtils.GetNullableDouble(reader, 8), DbReaderUtils.GetNullableDouble(reader, 9)); if (!seen.Contains(tmp.DeploymentId)) { seen.Add(tmp.DeploymentId); WaterQualityDeployment depl = this.helperBuilder.Get(tmp.DeploymentId); if (depl != null) { got.Add(tmp.DeploymentId); } } if (!got.Contains(tmp.DeploymentId)) { return(null); } return(tmp); }
private Guid CreateExcelFile(Dictionary <CompoundIdentity, Tuple <int, Site> > siteDict, Dictionary <CompoundIdentity, Tuple <int, SamplingEvent> > eventDict, Dictionary <CompoundIdentity, Tuple <int, WaterQualityDeployment> > deploymentDict, List <Tuple <int, WaterQualityMeasurement> > measurementList, Dictionary <CompoundIdentity, Organization> orgDict, Dictionary <CompoundIdentity, FieldTrip> fieldTripDict, Dictionary <CompoundIdentity, FieldActivity> fieldActivityDict, Dictionary <CompoundIdentity, Project> projectDict) { IFileStoreProvider provider = FileStoreManager.Instance.GetProvider(); //Setting up file and Excel Workbook FilestoreFile deployFile = provider.MakeTemp(DateTime.UtcNow.AddHours(4)); XlWorkbook book = new XlWorkbook(); XlWorksheets sheets = book.Worksheets; //Generating Sampling Event Sheet XlSchema eventSchema = GetSampleEventSchema(); XlWorksheet eventSheet = sheets.AddWorksheet("SamplingEvents", XlColor.White, eventSchema); XlRows eventRows = eventSheet.Rows; var orderedEvents = eventDict.OrderBy(x => x.Value.Item1); foreach (var evt in orderedEvents) { string orgName = na; if (orgDict.ContainsKey(evt.Value.Item2.PrincipalOrgId)) { orgName = orgDict[evt.Value.Item2.PrincipalOrgId].Name; } string ftripName = na; string factivityName = na; string projName = na; if (fieldTripDict.ContainsKey(evt.Value.Item2.FieldTripId)) { FieldTrip ftrip = fieldTripDict[evt.Value.Item2.FieldTripId]; ftripName = ftrip.Name; if (fieldActivityDict.ContainsKey(ftrip.FieldActivityId)) { FieldActivity factivity = fieldActivityDict[ftrip.FieldActivityId]; factivityName = factivity.Name; if (projectDict.ContainsKey(factivity.ProjectId)) { projName = projectDict[factivity.ProjectId].Name; } } } List <string> evtItems = new List <string>(); evtItems.Add(orgName); evtItems.Add(projName); evtItems.Add(factivityName); evtItems.Add(ftripName); evtItems.Add(evt.Value.Item2.Name); evtItems.Add(evt.Value.Item1.ToString()); evtItems.Add(evt.Value.Item2.Description); //evtItems.Add(evt.Value.Item2.DateRange.Min.ToString()); //evtItems.Add(evt.Value.Item2.DateRange.Max.ToString()); SchemaRowData row = new SchemaRowData(eventSchema, evtItems); eventRows.AddRow(row); } //Generating Deployment/Measurement Sheet XlSchema measSchema = GetDeployMeasurementSchema(); XlWorksheet measSheet = sheets.AddWorksheet("WaterQualityMeasurements", XlColor.White, measSchema); XlRows measRows = measSheet.Rows; var orderedMeasurements = measurementList.OrderBy(x => x.Item1); foreach (var meas in orderedMeasurements) { WaterQualityDeployment deploy = deploymentDict[meas.Item2.DeploymentId].Item2; WaterQualityMeasurement measurement = meas.Item2; int eventIndex = eventDict[deploy.SampleEventId].Item1; //deploy.SiteId could be a dangling reference string siteFK = na; if (siteDict.ContainsKey(deploy.SiteId)) { siteFK = siteDict[deploy.SiteId].Item1.ToString(); } List <string> measItems = new List <string>(); measItems.Add(meas.Item1.ToString()); measItems.Add(deploy.Name); measItems.Add(deploy.Description); measItems.Add(eventIndex.ToString()); measItems.Add(siteFK); measItems.Add(deploy.Range.StartDate.ToString()); measItems.Add(deploy.Range.EndDate.ToString()); measItems.Add(measurement.SampleDate.ToString()); measItems.Add(measurement.SurfaceElevation.ToString()); measItems.Add(measurement.Temperature.ToString()); measItems.Add(measurement.pH.ToString()); measItems.Add(measurement.DissolvedOxygen.ToString()); measItems.Add(measurement.Conductivity.ToString()); measItems.Add(measurement.Salinity.ToString()); measItems.Add(measurement.Velocity.ToString()); SchemaRowData row = new SchemaRowData(measSchema, measItems); measRows.AddRow(row); } //Generating Site Sheet XlSchema siteSchema = GetSiteSchema(); XlWorksheet siteSheet = sheets.AddWorksheet("Sites", XlColor.White, siteSchema); XlRows siteRows = siteSheet.Rows; var orderedSites = siteDict.OrderBy(x => x.Value.Item1); foreach (var site in orderedSites) { Site s = site.Value.Item2; List <string> siteItems = new List <string>(); siteItems.Add(site.Value.Item1.ToString()); siteItems.Add(s.Name); siteItems.Add(s.Description); IGeometry2 <double> geom = s.Location; if (geom != null) { if (geom is PolygonBag2 <double> ) { siteItems.Add(WktUtils.ToWkt(geom as PolygonBag2 <double>).ToString()); } else if (geom is Polygon2 <double> ) { siteItems.Add(WktUtils.ToWkt(geom as Polygon2 <double>).ToString()); } else if (geom is Polyline2 <double> ) { siteItems.Add(WktUtils.ToWkt(geom as Polyline2 <double>).ToString()); } else if (geom is PolylineBag2 <double> ) { siteItems.Add(WktUtils.ToWkt(geom as PolylineBag2 <double>).ToString()); } else if (geom is Point2 <double> ) { siteItems.Add(WktUtils.ToWkt(geom as Point2 <double>).ToString()); } } else { siteItems.Add(""); } Point2 <double> geom2 = s.LocationMark; if (geom2 != null) { siteItems.Add(WktUtils.ToWkt(geom2 as Point2 <double>).ToString()); } else { siteItems.Add(""); } SchemaRowData row = new SchemaRowData(siteSchema, siteItems); siteRows.AddRow(row); } book.Save(deployFile); deployFile.Flush(); deployFile.Close(); deployFile.Dispose(); return(deployFile.FileId); }
public IEnumerable <WaterQualityDeployment> GetForSite(CompoundIdentity siteId, DateTime start, DateTime end) { if (!siteId.IsNullOrEmpty() && this.CanGet()) { DateTime now = DateTime.UtcNow; string sql = null; if (start > WQUtils.GlobalMinDate) { sql = Db.Where + Db.WhereStart; } if (end < now) { if (sql == null) { sql = Db.Where + Db.WhereEnd; } else //already have start { sql = sql + " AND " + Db.WhereEnd; } } if (sql != null) //we need to have at least a start or end constraint that's limiting { NpgsqlCommand cmd = Db.GetCmd(Db.ConnectionString); cmd.CommandText = Db.SelectDeployment + sql + " AND " + Db.WhereSite; cmd.Parameters.AddWithValue("sitesid", siteId.DataStoreIdentity); cmd.Parameters.AddWithValue("siteid", siteId.Identity); if (start > WQUtils.GlobalMinDate) { cmd.Parameters.AddWithValue("start", start); } if (end < now) { cmd.Parameters.AddWithValue("end", end); } NpgsqlDataReader rdr = Db.ExecuteReader(cmd); WaterQualityDeployment o = null; List <WaterQualityDeployment> permissions = new List <WaterQualityDeployment>(); if (rdr != null) { try { while (rdr.Read()) { o = this.builder.Build(rdr); if (o != null) { permissions.Add(o); } } if (cmd.Connection.State == System.Data.ConnectionState.Open) { cmd.Connection.Close(); } } catch { } finally { cmd.Dispose(); } } return(permissions); } } return(null); }
public IEnumerable <WaterQualityDeployment> Get(DateTime start, DateTime end) { if (this.CanGet()) { DateTime now = DateTime.UtcNow; string sql = null; if (start > WQUtils.GlobalMinDate) { sql = Db.Where + Db.WhereStart; } if (end < now) { if (sql == null) { sql = Db.Where + Db.WhereEnd; } else //already have start { sql = sql + " AND " + Db.WhereEnd; } } if (sql != null) { NpgsqlCommand cmd = Db.GetCmd(Db.ConnectionString); cmd.CommandText = Db.SelectDeployment + sql; if (start > WQUtils.GlobalMinDate) { cmd.Parameters.AddWithValue("start", start); } if (end < now) { cmd.Parameters.AddWithValue("end", end); } NpgsqlDataReader rdr = Db.ExecuteReader(cmd); WaterQualityDeployment o = null; List <WaterQualityDeployment> permissions = new List <WaterQualityDeployment>(); if (rdr != null) { try { while (rdr.Read()) { o = this.builder.Build(rdr); if (o != null) { permissions.Add(o); } } if (cmd.Connection.State == System.Data.ConnectionState.Open) { cmd.Connection.Close(); } } catch { } finally { cmd.Dispose(); } } return(permissions); } } return(null); }
private UpdateStatus InitialLoad(CompoundIdentity seId, EntityBundle sites, EntityBundle instruments, WaterQualityDET curdet, IWQDeploymentProvider depl, IWQMeasurementProvider meas, bool isPrivate) { UpdateStatus stat = null; if (sites == null || instruments == null || seId == null || seId.IsEmpty) { stat = new UpdateStatus(UpdateIssue.DataIssue); stat.Add(new IssueNotice("BundlesOrEvent", "Null value")); } else { //TODO -- may need to do case insensitive ops or not -- verify how excel part is implemented Dictionary <string, CompoundIdentity> deployIds = new Dictionary <string, CompoundIdentity>(); BundleElement elem; foreach (DeploymentDTO cur in curdet.Deployments.Values) { if (cur != null && !deployIds.ContainsKey(cur.DeployCode)) { elem = sites.Get(cur.SiteId); if (elem != null) { CompoundIdentity siteId = elem.EntityId; elem = instruments.Get(cur.InstrumentId); if (elem != null) { CompoundIdentity sensorId = elem.EntityId; WaterQualityDeployment dep = depl.Create(cur.DeployCode, seId, siteId, sensorId, this.GetRange(cur.StartDate, cur.EndDate), cur.Comments, isPrivate); if (dep != null) { deployIds[dep.Name] = dep.Identity; } else { stat = Add("Create", "Unable to create deployment " + cur.DeployCode, UpdateIssue.DataIssue, stat); } } else { stat = Add("InstCode", "Empty or invalid Instrument Code on deployment " + cur.DeployCode, UpdateIssue.DataIssue, stat); } } else { stat = Add("SiteCode", "Empty or invalid Site Code on Deployment " + cur.DeployCode, UpdateIssue.DataIssue, stat); } } else { stat = Add("DeployCode", "A deployment is missing a deployment code", UpdateIssue.DataIssue, stat); } } Dictionary <CompoundIdentity, WaterQualityMeasurementsDTO> items = new Dictionary <CompoundIdentity, WaterQualityMeasurementsDTO>(); foreach (MeasurementDTO cur in curdet.Measurements.Values) { if (cur != null && deployIds.ContainsKey(cur.DeployCode) && cur.MeasureDateTime != null) { CompoundIdentity depId = deployIds[cur.DeployCode]; if (depId != null) { if (!items.ContainsKey(depId)) { items[depId] = WaterQualityMeasurementsDTO.Create(depId); } WaterQualityMeasurementsDTO elems = items[depId]; elems.Add(new WaterQualityMeasurementDTO(cur.MeasureDateTime.Value, cur.SurfaceElevation, cur.Temperature, cur.pH, cur.DO, cur.Conductivity, cur.Salinity, cur.Velocity)); } } else { stat = Add("Create", "Unable to create Measurement with key " + cur.DeployCode + ":" + cur.MeasureDateTime, UpdateIssue.DataIssue, stat); } } foreach (WaterQualityMeasurementsDTO cur in items.Values) { if (cur != null) { meas.Create(cur); } } if (stat == null) { stat = new UpdateStatus(UpdateIssue.AllOk); stat.Add(new IssueNotice("NoIssues", "No issues")); } } return(stat); }