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